using System;
using System.Data;
using System.Data.SqlClient;
namespace Stalberg.TMS
{
//*******************************************************
//
// LocationDetails Class
//
// A simple data class that encapsulates details about a particular loc
//
//*******************************************************
public partial class LocationDetails
{
public Int32 SZIntNo;
public Int32 RdTIntNo;
public Int32 ACIntNo;
public string LocCameraCode;
public string LocCode;
public string LocStreetCode;
public string LocStreetName;
public string LocTravelDirection;
public string LocDescr;
public int LocOffenceSpeedStart;
public string Province;
public string City;
public string Suburb;
public string StreetA;
public string StreetB;
public string Route;
public string From;
public string To;
public string GpsX;
public string GpsY;
public string LocBranchCode;
public int LoSuIntNo;
public string LocType;
public string LocRegion;
//2013-12-12 Heidi added IsRailwayCrossing for check Railway Crossing on Location Maintenance page(5149)
public bool IsRailwayCrossing;
}
//*******************************************************
//
// LocationDB Class
//
// Business/Data Logic Class that encapsulates all data
// logic necessary to add/login/query Locs within
// the Commerce Starter Kit Customer database.
//
//*******************************************************
public partial class LocationDB
{
string mConstr = "";
public LocationDB(string vConstr)
{
mConstr = vConstr;
}
//*******************************************************
//
// LocDB.GetLocationDetails() Method <a name="GetLocationDetails"></a>
//
// The GetLocationDetails method returns a LocationDetails
// struct that contains information about a specific
// customer (name, password, etc).
//
//*******************************************************
public LocationDetails GetLocationDetails(int locIntNo)
{
// Create Instance of Connection and Command Object
SqlConnection con = new SqlConnection(mConstr);
SqlCommand cmd = new SqlCommand("LocationDetail", con);
// Mark the Command as a SPROC
cmd.CommandType = CommandType.StoredProcedure;
// Add Parameters to SPROC
SqlParameter parameterLocIntNo = new SqlParameter("@LocIntNo", SqlDbType.Int, 4);
parameterLocIntNo.Value = locIntNo;
cmd.Parameters.Add(parameterLocIntNo);
con.Open();
SqlDataReader result = cmd.ExecuteReader(CommandBehavior.CloseConnection);
// Create CustomerDetails Struct
LocationDetails details = new LocationDetails();
while (result.Read())
{
// Populate Struct using Output Params from SPROC
details.ACIntNo = Convert.ToInt32(result["ACIntNo"]);
details.RdTIntNo = Convert.ToInt32(result["RdTIntNo"]);
details.SZIntNo = Convert.ToInt32(result["SZIntNo"]);
details.LocOffenceSpeedStart = Convert.ToInt32(result["LocOffenceSpeedStart"]);
details.LocCameraCode = result["LocCameraCode"].ToString();
details.LocCode = result["LocCode"].ToString();
details.LocDescr = result["LocDescr"].ToString();
details.LocStreetCode = result["LocStreetCode"].ToString();
details.LocStreetName = result["LocStreetName"].ToString();
details.LocTravelDirection = result["LocTravelDirection"].ToString();
details.Province = result["Province"].ToString();
details.City = result["City"].ToString();
details.Suburb = result["Suburb"].ToString();
details.StreetA = result["StreetA"].ToString();
details.StreetB = result["StreetB"].ToString();
details.Route = result["Route"].ToString();
details.From = result["From"].ToString();
details.To = result["To"].ToString();
details.GpsX = result["GpsX"].ToString();
details.GpsY = result["GpsY"].ToString();
details.LocBranchCode = result["LocBranchCode"].ToString();
details.LoSuIntNo = Convert.ToInt32(result["LoSuIntNo"]);
details.LocType = result["LocType"].ToString();
details.LocRegion = result["LocRegion"].ToString();
//2013-12-12 Heidi added for check Railway Crossing on Location Maintenance page(5149)
details.IsRailwayCrossing = string.IsNullOrEmpty(result["IsRailwayCrossing"].ToString()) ? false : Convert.ToBoolean(result["IsRailwayCrossing"]);
}
result.Close();
return details;
}
//*******************************************************
//
// LocDB.AddLoc() Method <a name="AddLoc"></a>
//
// The AddLoc method inserts a new loc record
// into the locs database. A unique "LocId"
// key is then returned from the method.
//
//*******************************************************
public int AddLocation(int acIntNo, int szIntNo, int rdtIntNo,
string locCameraCode, string locCode, string locDescr,
string locStreetCode, string locStreetName, string locTravelDirection,
int locOffenceSpeedStart, string province, string city, string suburb,
string streetA, string streetB, string route, string from, string to, string gpsX, string gpsY,
string locBranchCode, string lastUser, int LoSuIntNo, string locType, string locRegion, bool isRailwayCrossing)
{
// Create Instance of Connection and Command Object
SqlConnection con = new SqlConnection(mConstr);
SqlCommand cmd = new SqlCommand("LocationAdd", con);
// Mark the Command as a SPROC
cmd.CommandType = CommandType.StoredProcedure;
// Add Parameters to SPROC
SqlParameter parameterACIntNo = new SqlParameter("@ACIntNo", SqlDbType.Int, 4);
parameterACIntNo.Value = acIntNo;
cmd.Parameters.Add(parameterACIntNo);
SqlParameter parameterRdTIntNo = new SqlParameter("@RdTIntNo", SqlDbType.Int, 4);
parameterRdTIntNo.Value = rdtIntNo;
cmd.Parameters.Add(parameterRdTIntNo);
SqlParameter parameterSZIntNo = new SqlParameter("@SZIntNo", SqlDbType.Int, 4);
parameterSZIntNo.Value = szIntNo;
cmd.Parameters.Add(parameterSZIntNo);
SqlParameter parameterLocCameraCode = new SqlParameter("@LocCameraCode", SqlDbType.VarChar, 12);
parameterLocCameraCode.Value = locCameraCode;
cmd.Parameters.Add(parameterLocCameraCode);
SqlParameter parameterLocCode = new SqlParameter("@LocCode", SqlDbType.VarChar, 21);
parameterLocCode.Value = locCode;
cmd.Parameters.Add(parameterLocCode);
SqlParameter parameterLocDescr = new SqlParameter("@LocDescr", SqlDbType.VarChar, 150);
parameterLocDescr.Value = locDescr;
cmd.Parameters.Add(parameterLocDescr);
SqlParameter parameterLocStreetCode = new SqlParameter("@LocStreetCode", SqlDbType.VarChar, 5);
parameterLocStreetCode.Value = locStreetCode;
cmd.Parameters.Add(parameterLocStreetCode);
SqlParameter parameterLocStreetName = new SqlParameter("@LocStreetName", SqlDbType.VarChar, 30);
parameterLocStreetName.Value = locStreetName;
cmd.Parameters.Add(parameterLocStreetName);
SqlParameter parameterLocOffenceSpeedStart = new SqlParameter("@LocOffenceSpeedStart", SqlDbType.Int);
parameterLocOffenceSpeedStart.Value = locOffenceSpeedStart;
cmd.Parameters.Add(parameterLocOffenceSpeedStart);
SqlParameter parameterLocTravelDirection = new SqlParameter("@LocTravelDirection", SqlDbType.Char, 1);
parameterLocTravelDirection.Value = locTravelDirection;
cmd.Parameters.Add(parameterLocTravelDirection);
SqlParameter parameterLoSuIntNo = new SqlParameter("@LoSuIntNo", SqlDbType.Int, 4);
parameterLoSuIntNo.Value = LoSuIntNo;
cmd.Parameters.Add(parameterLoSuIntNo);
cmd.Parameters.Add("Province", SqlDbType.VarChar, 30).Value = province;
cmd.Parameters.Add("City", SqlDbType.VarChar, 30).Value = city;
cmd.Parameters.Add("Suburb", SqlDbType.VarChar, 30).Value = suburb;
cmd.Parameters.Add("StreetA", SqlDbType.VarChar, 50).Value = streetA;
cmd.Parameters.Add("StreetB", SqlDbType.VarChar, 50).Value = streetB;
cmd.Parameters.Add("Route", SqlDbType.VarChar, 30).Value = route;
cmd.Parameters.Add("From", SqlDbType.VarChar, 50).Value = from;
cmd.Parameters.Add("To", SqlDbType.VarChar, 50).Value = to;
cmd.Parameters.Add("GpsX", SqlDbType.VarChar, 30).Value = gpsX;
cmd.Parameters.Add("GpsY", SqlDbType.VarChar, 30).Value = gpsY;
cmd.Parameters.Add("LocBranchCode", SqlDbType.Char, 2).Value = locBranchCode;
cmd.Parameters.Add("LastUser", SqlDbType.VarChar, 50).Value = lastUser;
cmd.Parameters.Add("LocType", SqlDbType.Char, 1).Value = locType;
cmd.Parameters.Add("LocRegion", SqlDbType.NVarChar, 100).Value = locRegion;
//2013-12-12 Heidi added for check Railway Crossing on Location Maintenance page(5149)
cmd.Parameters.Add("IsRailwayCrossing", SqlDbType.Bit).Value = isRailwayCrossing;
SqlParameter parameterLocIntNo = new SqlParameter("@LocIntNo", SqlDbType.Int, 4);
parameterLocIntNo.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parameterLocIntNo);
try
{
con.Open();
cmd.ExecuteNonQuery();
// Calculate the CustomerID using Output Param from SPROC
int locIntNo = Convert.ToInt32(parameterLocIntNo.Value);
return locIntNo;
}
catch (Exception e)
{
string msg = e.Message;
return 0;
}
finally
{
con.Dispose();
}
}
public SqlDataReader GetLocationList(int autIntNo, string search, string orderBy)
{
// Create Instance of Connection and Command Object
SqlConnection myConnection = new SqlConnection(mConstr);
SqlCommand myCommand = new SqlCommand("LocationList", myConnection);
// Mark the Command as a SPROC
myCommand.CommandType = CommandType.StoredProcedure;
SqlParameter parameterAutIntNo = new SqlParameter("@AutIntNo", SqlDbType.Int, 4);
parameterAutIntNo.Value = autIntNo;
myCommand.Parameters.Add(parameterAutIntNo);
SqlParameter parameterSearch = new SqlParameter("@Search", SqlDbType.VarChar, 20);
parameterSearch.Value = search;
myCommand.Parameters.Add(parameterSearch);
SqlParameter parameterOrderBy = new SqlParameter("@OrderBy", SqlDbType.VarChar, 20);
parameterOrderBy.Value = orderBy;
myCommand.Parameters.Add(parameterOrderBy);
// Execute the command
myConnection.Open();
SqlDataReader result = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
// Return the datareader result
return result;
}
public DataSet GetLocationListDS(int autIntNo, string search, string orderBy)
{
int totalCount;
return GetLocationListDS(autIntNo, search, orderBy, out totalCount);
}
public DataSet GetLocationListDS(int autIntNo, string search, string orderBy, out int totalCount, int pageSize = 0, int pageIndex = 0)
{
SqlDataAdapter sqlDALocs = new SqlDataAdapter();
DataSet dsLocs = new DataSet();
// Create Instance of Connection and Command Object
sqlDALocs.SelectCommand = new SqlCommand();
sqlDALocs.SelectCommand.Connection = new SqlConnection(mConstr);
sqlDALocs.SelectCommand.CommandText = "LocationList";
// Mark the Command as a SPROC
sqlDALocs.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter parameterAutIntNo = new SqlParameter("@AutIntNo", SqlDbType.Int, 4);
parameterAutIntNo.Value = autIntNo;
sqlDALocs.SelectCommand.Parameters.Add(parameterAutIntNo);
SqlParameter parameterSearch = new SqlParameter("@Search", SqlDbType.VarChar, 20);
parameterSearch.Value = search;
sqlDALocs.SelectCommand.Parameters.Add(parameterSearch);
SqlParameter parameterOrderBy = new SqlParameter("@OrderBy", SqlDbType.VarChar, 20);
parameterOrderBy.Value = orderBy;
sqlDALocs.SelectCommand.Parameters.Add(parameterOrderBy);
SqlParameter parameterPageSize = new SqlParameter("@PageSize", SqlDbType.Int);
parameterPageSize.Value = pageSize;
sqlDALocs.SelectCommand.Parameters.Add(parameterPageSize);
SqlParameter parameterPageIndex = new SqlParameter("@PageIndex", SqlDbType.Int);
parameterPageIndex.Value = pageIndex;
sqlDALocs.SelectCommand.Parameters.Add(parameterPageIndex);
SqlParameter parameterTotalCount = new SqlParameter("@TotalCount", SqlDbType.Int);
parameterTotalCount.Direction = ParameterDirection.Output;
sqlDALocs.SelectCommand.Parameters.Add(parameterTotalCount);
// Execute the command and close the connection
sqlDALocs.Fill(dsLocs);
sqlDALocs.SelectCommand.Connection.Dispose();
totalCount = (int)(parameterTotalCount.Value == DBNull.Value ? 0 : parameterTotalCount.Value);
// Return the dataset result
return dsLocs;
}
public int UpdateLocation(int locIntNo, int acIntNo, int szIntNo, int rdtIntNo,
string locCameraCode, string locCode, string locDescr,
string locStreetCode, string locStreetName, string locTravelDirection,
int locOffenceSpeedStart, string province, string city, string suburb,
string streetA, string streetB, string route, string from, string to, string gpsX, string gpsY,
string locBranchCode, string lastUser, int LoSuIntNo, string locType, string locRegion, bool isRailwayCrossing)
{
// Create Instance of Connection and Command Object
SqlConnection con = new SqlConnection(mConstr);
SqlCommand cmd = new SqlCommand("LocationUpdate", con);
// Mark the Command as a SPROC
cmd.CommandType = CommandType.StoredProcedure;
// Add Parameters to SPROC
SqlParameter parameterACIntNo = new SqlParameter("@ACIntNo", SqlDbType.Int, 4);
parameterACIntNo.Value = acIntNo;
cmd.Parameters.Add(parameterACIntNo);
SqlParameter parameterRdTIntNo = new SqlParameter("@RdTIntNo", SqlDbType.Int, 4);
parameterRdTIntNo.Value = rdtIntNo;
cmd.Parameters.Add(parameterRdTIntNo);
SqlParameter parameterSZIntNo = new SqlParameter("@SZIntNo", SqlDbType.Int, 4);
parameterSZIntNo.Value = szIntNo;
cmd.Parameters.Add(parameterSZIntNo);
SqlParameter parameterLocCameraCode = new SqlParameter("@LocCameraCode", SqlDbType.VarChar, 12);
parameterLocCameraCode.Value = locCameraCode;
cmd.Parameters.Add(parameterLocCameraCode);
SqlParameter parameterLocCode = new SqlParameter("@LocCode", SqlDbType.VarChar, 21);
parameterLocCode.Value = locCode;
cmd.Parameters.Add(parameterLocCode);
SqlParameter parameterLocDescr = new SqlParameter("@LocDescr", SqlDbType.VarChar, 150);
parameterLocDescr.Value = locDescr;
cmd.Parameters.Add(parameterLocDescr);
SqlParameter parameterLocStreetCode = new SqlParameter("@LocStreetCode", SqlDbType.VarChar, 5);
parameterLocStreetCode.Value = locStreetCode;
cmd.Parameters.Add(parameterLocStreetCode);
SqlParameter parameterLocStreetName = new SqlParameter("@LocStreetName", SqlDbType.VarChar, 30);
parameterLocStreetName.Value = locStreetName;
cmd.Parameters.Add(parameterLocStreetName);
SqlParameter parameterLocTravelDirection = new SqlParameter("@LocTravelDirection", SqlDbType.Char, 1);
parameterLocTravelDirection.Value = locTravelDirection;
cmd.Parameters.Add(parameterLocTravelDirection);
SqlParameter parameterLocOffenceSpeedStart = new SqlParameter("@LocOffenceSpeedStart", SqlDbType.Int);
parameterLocOffenceSpeedStart.Value = locOffenceSpeedStart;
cmd.Parameters.Add(parameterLocOffenceSpeedStart);
SqlParameter parameterLoSuIntNo = new SqlParameter("@LoSuIntNo", SqlDbType.Int, 4);
parameterLoSuIntNo.Value = LoSuIntNo;
cmd.Parameters.Add(parameterLoSuIntNo);
cmd.Parameters.Add("Province", SqlDbType.VarChar, 30).Value = province;
cmd.Parameters.Add("City", SqlDbType.VarChar, 30).Value = city;
cmd.Parameters.Add("Suburb", SqlDbType.VarChar, 30).Value = suburb;
cmd.Parameters.Add("StreetA", SqlDbType.VarChar, 50).Value = streetA;
cmd.Parameters.Add("StreetB", SqlDbType.VarChar, 50).Value = streetB;
cmd.Parameters.Add("Route", SqlDbType.VarChar, 30).Value = route;
cmd.Parameters.Add("From", SqlDbType.VarChar, 50).Value = from;
cmd.Parameters.Add("To", SqlDbType.VarChar, 50).Value = to;
cmd.Parameters.Add("GpsX", SqlDbType.VarChar, 30).Value = gpsX;
cmd.Parameters.Add("GpsY", SqlDbType.VarChar, 30).Value = gpsY;
cmd.Parameters.Add("LocBranchCode", SqlDbType.Char, 2).Value = locBranchCode;
cmd.Parameters.Add("LastUser", SqlDbType.VarChar, 50).Value = lastUser;
cmd.Parameters.Add("LocType", SqlDbType.Char, 1).Value = locType;
cmd.Parameters.Add("LocRegion", SqlDbType.NVarChar, 100).Value = locRegion;
//2013-12-12 Heidi added for check Railway Crossing on Location Maintenance page(5149)
cmd.Parameters.Add("IsRailwayCrossing", SqlDbType.Bit).Value = isRailwayCrossing;
SqlParameter parameterLocIntNo = new SqlParameter("@LocIntNo", SqlDbType.Int);
parameterLocIntNo.Direction = ParameterDirection.InputOutput;
parameterLocIntNo.Value = locIntNo;
cmd.Parameters.Add(parameterLocIntNo);
try
{
con.Open();
cmd.ExecuteNonQuery();
con.Dispose();
// Calculate the CustomerID using Output Param from SPROC
int LocIntNo = (int)cmd.Parameters["@LocIntNo"].Value;
//int locId = (int)parameterLocIntNo.Value;
return LocIntNo;
}
catch (Exception e)
{
con.Dispose();
string msg = e.Message;
return 0;
}
}
public int DeleteLocation(int locIntNo, ref string errMessage)
{
// Create Instance of Connection and Command Object
SqlConnection myConnection = new SqlConnection(mConstr);
SqlCommand myCommand = new SqlCommand("LocationDelete", myConnection);
// Mark the Command as a SPROC
myCommand.CommandType = CommandType.StoredProcedure;
// Add Parameters to SPROC
SqlParameter parameterLocIntNo = new SqlParameter("@LocIntNo", SqlDbType.Int, 4);
parameterLocIntNo.Value = locIntNo;
parameterLocIntNo.Direction = ParameterDirection.InputOutput;
myCommand.Parameters.Add(parameterLocIntNo);
try
{
myConnection.Open();
myCommand.ExecuteNonQuery();
myConnection.Dispose();
// Calculate the CustomerID using Output Param from SPROC
int LocIntNo = (int)parameterLocIntNo.Value;
return LocIntNo;
}
catch (Exception ex)
{
errMessage = ex.Message;
return 0;
}
finally
{
myConnection.Dispose();
}
}
/// <summary>
/// Gets the auth_Location list by auth DS.
/// </summary>
/// <param name="autIntNo">The aut int no.</param>
/// <returns></returns>
public DataSet GetValueTextListByLocationTable(int autIntNo)
{
SqlDataAdapter sqlDALocations = new SqlDataAdapter();
DataSet dsLocations = new DataSet();
// Create Instance of Connection and Command Object
sqlDALocations.SelectCommand = new SqlCommand();
sqlDALocations.SelectCommand.Connection = new SqlConnection(mConstr);
sqlDALocations.SelectCommand.CommandText = "GetValueTextListByLocationTable";
// Mark the Command as a SPROC
sqlDALocations.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter parameterAutIntNo = new SqlParameter("@AutIntNo", SqlDbType.Int, 4);
parameterAutIntNo.Value = autIntNo;
sqlDALocations.SelectCommand.Parameters.Add(parameterAutIntNo);
// Execute the command and close the connection
sqlDALocations.Fill(dsLocations);
sqlDALocations.SelectCommand.Connection.Dispose();
// Return the dataset result
return dsLocations;
}
/// <summary>
/// Heidi 2013-12-12 Get Railway Locations DDL by AutIntNo
/// </summary>
/// <param name="autIntNo">The aut int no.</param>
/// <returns></returns>
public DataSet GetRailwayLocationsByAutIntNo(int autIntNo,int locIntNo,bool IsAll)
{
SqlDataAdapter sqlDALocations = new SqlDataAdapter();
DataSet dsLocations = new DataSet();
// Create Instance of Connection and Command Object
sqlDALocations.SelectCommand = new SqlCommand();
sqlDALocations.SelectCommand.Connection = new SqlConnection(mConstr);
sqlDALocations.SelectCommand.CommandText = "GetRailwayLocationsByAutIntNo";
// Mark the Command as a SPROC
sqlDALocations.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter parameterAutIntNo = new SqlParameter("@AutIntNo", SqlDbType.Int, 4);
parameterAutIntNo.Value = autIntNo;
sqlDALocations.SelectCommand.Parameters.Add(parameterAutIntNo);
SqlParameter parameterLocIntNo = new SqlParameter("@LocIntNo", SqlDbType.Int, 4);
parameterLocIntNo.Value = locIntNo;
sqlDALocations.SelectCommand.Parameters.Add(parameterLocIntNo);
SqlParameter parameterISALL = new SqlParameter("@ISALL", SqlDbType.Bit);
parameterISALL.Value = IsAll;
sqlDALocations.SelectCommand.Parameters.Add(parameterISALL);
// Execute the command and close the connection
sqlDALocations.Fill(dsLocations);
sqlDALocations.SelectCommand.Connection.Dispose();
// Return the dataset result
return dsLocations;
}
/// <summary>
/// Heidi 2013-12-16 Get Railway Locations by AutIntNo and LocIntNo
/// </summary>
/// <param name="autIntNo">The aut int no.</param>
/// <returns></returns>
public DataSet GetLocationByLocIntNoAndIsRailwayCrossing(int autIntNo, int locIntNo)
{
SqlDataAdapter sqlDALocations = new SqlDataAdapter();
DataSet dsLocations = new DataSet();
// Create Instance of Connection and Command Object
sqlDALocations.SelectCommand = new SqlCommand();
sqlDALocations.SelectCommand.Connection = new SqlConnection(mConstr);
sqlDALocations.SelectCommand.CommandText = "GetLocationByLocIntNoAndIsRailwayCrossing";
// Mark the Command as a SPROC
sqlDALocations.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter parameterAutIntNo = new SqlParameter("@AutIntNo", SqlDbType.Int, 4);
parameterAutIntNo.Value = autIntNo;
sqlDALocations.SelectCommand.Parameters.Add(parameterAutIntNo);
SqlParameter parameterLocIntNo= new SqlParameter("@LocIntNo", SqlDbType.Int, 4);
parameterLocIntNo.Value = locIntNo;
sqlDALocations.SelectCommand.Parameters.Add(parameterLocIntNo);
// Execute the command and close the connection
sqlDALocations.Fill(dsLocations);
sqlDALocations.SelectCommand.Connection.Dispose();
// Return the dataset result
return dsLocations;
}
}
}
