public List <Budget_CarTypesPeriod> SelectAll()
{
List <Budget_CarTypesPeriod> PeriodList = null;
try
{
string sSQL = "SELECT * FROM CarTypesPeriod";
DataSet ds = SqlHelper.ExecuteDataset(ConnectionString, CommandType.Text, sSQL);
if (ds.Tables[0].Rows.Count > 0)
{
PeriodList = new List <Budget_CarTypesPeriod>();
Budget_CarTypesPeriod period = null;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
period = new Budget_CarTypesPeriod();
period.RateOID = new Guid(ds.Tables[0].Rows[i]["RateOID"].ToString());
period.CarTypeOID = new Guid(ds.Tables[0].Rows[i]["CarTypeOID"].ToString());
period.StartPeriod = Convert.ToDateTime(ds.Tables[0].Rows[i]["StartPeriod"].ToString());
period.FinishPeriod = Convert.ToDateTime(ds.Tables[0].Rows[i]["FinishPeriod"].ToString());
period.RatePerDay = ds.Tables[0].Rows[i]["RatePerDay"].Equals(DBNull.Value) == false?Convert.ToDouble(ds.Tables[0].Rows[i]["RatePerDay"].ToString()) : 0;
PeriodList.Add(period);
}
}
}
catch (Exception ex) { }
return(PeriodList);
}
public List <Budget_CarTypesPeriod> SelectAllByCarTypeOID(Guid CarTypeOID)
{
List <Budget_CarTypesPeriod> PeriodList = null;
try
{
string sSQL = "SELECT * FROM CarTypesPeriod WHERE CarTypeOID=@CarTypeOID";
SqlParameter[] sqlParam = new SqlParameter[1];
sqlParam[0] = new SqlParameter("@CarTypeOID", SqlDbType.UniqueIdentifier);
sqlParam[0].Value = CarTypeOID;
DataSet ds = SqlHelper.ExecuteDataset(ConnectionString, CommandType.Text, sSQL, sqlParam);
if (ds.Tables[0].Rows.Count > 0)
{
PeriodList = new List <Budget_CarTypesPeriod>();
Budget_CarTypesPeriod period = null;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
period = new Budget_CarTypesPeriod();
period.RateOID = new Guid(ds.Tables[0].Rows[i]["RateOID"].ToString());
period.CarTypeOID = new Guid(ds.Tables[0].Rows[i]["CarTypeOID"].ToString());
period.StartPeriod = Convert.ToDateTime(ds.Tables[0].Rows[i]["StartPeriod"].ToString());
period.FinishPeriod = Convert.ToDateTime(ds.Tables[0].Rows[i]["FinishPeriod"].ToString());
period.RatePerDay = ds.Tables[0].Rows[i]["RatePerDay"].Equals(DBNull.Value) == false?Convert.ToDouble(ds.Tables[0].Rows[i]["RatePerDay"].ToString()) : 0;
PeriodList.Add(period);
}
}
}
catch (Exception ex) { }
return(PeriodList);
}
public Budget_CarTypesPeriod SelectByOID(Guid guidRateOID)
{
Budget_CarTypesPeriod period = null;
try
{
string sSQL = "SELECT * FROM CarTypesPeriod WHERE RateOID=@RateOID";
SqlParameter[] sqlParam = new SqlParameter[1];
sqlParam[0] = new SqlParameter("@RateOID", SqlDbType.UniqueIdentifier);
sqlParam[0].Value = guidRateOID;
DataSet ds = SqlHelper.ExecuteDataset(ConnectionString, CommandType.Text, sSQL, sqlParam);
if (ds.Tables[0].Rows.Count > 0)
{
period = new Budget_CarTypesPeriod();
period.RateOID = new Guid(ds.Tables[0].Rows[0]["RateOID"].ToString());
period.CarTypeOID = new Guid(ds.Tables[0].Rows[0]["CarTypeOID"].ToString());
period.StartPeriod = Convert.ToDateTime(ds.Tables[0].Rows[0]["StartPeriod"].ToString());
period.FinishPeriod = Convert.ToDateTime(ds.Tables[0].Rows[0]["FinishPeriod"].ToString());
period.RatePerDay = ds.Tables[0].Rows[0]["RatePerDay"].Equals(DBNull.Value) == false?Convert.ToDouble(ds.Tables[0].Rows[0]["RatePerDay"].ToString()) : 0;
}
}
catch (Exception ex) { }
return(period);
}
public List <Budget_CarTypes> SelectAllByPeriod(DateTime PickupDateTime)
{
List <Budget_CarTypes> CarTypeList = null;
try
{
string sSQL = @"SELECT CarTypes.*, CarTypesPeriod.RateOID,CarTypesPeriod.StartPeriod,CarTypesPeriod.FinishPeriod,CarTypesPeriod.RatePerDay
FROM CarTypes LEFT JOIN CarTypesPeriod ON CarTypes.CarTypeOID=CarTypesPeriod.CarTypeOID
WHERE CONVERT(nvarchar,CarTypesPeriod.StartPeriod,102) <= @PickupDateTime AND CONVERT(nvarchar,CarTypesPeriod.FinishPeriod,102) >= @PickupDateTime";
SqlParameter[] sqlParam = new SqlParameter[1];
sqlParam[0] = new SqlParameter("@PickupDateTime", SqlDbType.NVarChar);
sqlParam[0].Value = PickupDateTime.ToString("yyyy.MM.dd");
DataSet ds = SqlHelper.ExecuteDataset(ConnectionString, CommandType.Text, sSQL, sqlParam);
if (ds.Tables[0].Rows.Count > 0)
{
CarTypeList = new List <Budget_CarTypes>();
Budget_CarTypes carType = null;
Budget_CarTypesPeriod period = null;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
if (ds.Tables[0].Rows[i]["RatePerDay"].Equals(DBNull.Value) == false)
{
carType = new Budget_CarTypes();
carType.CarTypeOID = new Guid(ds.Tables[0].Rows[i]["CarTypeOID"].ToString());
carType.VehiclesClassCode = ds.Tables[0].Rows[i]["VehiclesClassCode"].ToString();
carType.VehiclesCategoryCode = ds.Tables[0].Rows[i]["VehiclesCategoryCode"].ToString();
carType.GroupCode = ds.Tables[0].Rows[i]["GroupCode"].ToString();
carType.SIPPCode = ds.Tables[0].Rows[i]["SIPPCode"].ToString();
carType.Type = ds.Tables[0].Rows[i]["Type"].ToString();
carType.Name = ds.Tables[0].Rows[i]["Name"].ToString();
carType.FileName = ds.Tables[0].Rows[i]["FileName"].ToString();
carType.NoOfPax = int.Parse(ds.Tables[0].Rows[i]["NoOfPax"].ToString());
carType.NoOfLargeBag = int.Parse(ds.Tables[0].Rows[i]["NoOfLargeBag"].ToString());
carType.NoOfMediumBag = int.Parse(ds.Tables[0].Rows[i]["NoOfMediumBag"].ToString());
carType.NoOfSmallBag = int.Parse(ds.Tables[0].Rows[i]["NoOfSmallBag"].ToString());
carType.Doors = int.Parse(ds.Tables[0].Rows[i]["Doors"].ToString());
carType.isSedan = Convert.ToBoolean(ds.Tables[0].Rows[i]["isSedan"].ToString());
carType.Litre = float.Parse(ds.Tables[0].Rows[i]["Litre"].ToString());
carType.Cylindar = int.Parse(ds.Tables[0].Rows[i]["Cylindar"].ToString());
carType.AirBag = int.Parse(ds.Tables[0].Rows[i]["AirBag"].ToString());
carType.isAutomatic = Convert.ToBoolean(ds.Tables[0].Rows[i]["isAutomatic"].ToString());
carType.isAirCondition = Convert.ToBoolean(ds.Tables[0].Rows[i]["isAirCondition"].ToString());
carType.isPowerSteering = Convert.ToBoolean(ds.Tables[0].Rows[i]["isPowerSteering"].ToString());
carType.isABS = Convert.ToBoolean(ds.Tables[0].Rows[i]["isABS"].ToString());
carType.isCDPlayer = Convert.ToBoolean(ds.Tables[0].Rows[i]["isCDPlayer"].ToString());
carType.isReversingCamera = Convert.ToBoolean(ds.Tables[0].Rows[i]["isReversingCamera"].ToString());
carType.isBluetoothAUXandUSB = Convert.ToBoolean(ds.Tables[0].Rows[i]["isBluetoothAUXandUSB"].ToString());
carType.Others = ds.Tables[0].Rows[i]["Others"].ToString();
period = new Budget_CarTypesPeriod();
period.RateOID = new Guid(ds.Tables[0].Rows[i]["RateOID"].ToString());
period.CarTypeOID = new Guid(ds.Tables[0].Rows[i]["CarTypeOID"].ToString());
period.StartPeriod = Convert.ToDateTime(ds.Tables[0].Rows[i]["StartPeriod"].ToString());
period.FinishPeriod = Convert.ToDateTime(ds.Tables[0].Rows[i]["FinishPeriod"].ToString());
period.RatePerDay = ds.Tables[0].Rows[i]["RatePerDay"].Equals(DBNull.Value) == false?Convert.ToDouble(ds.Tables[0].Rows[i]["RatePerDay"].ToString()) : 0;
carType.period = period;
CarTypeList.Add(carType);
}
}
}
}
catch (Exception ex) { }
return(CarTypeList);
}
