public FareDto(Fare fare)
{
_FareId = fare.ID;
_RoutesID = fare.RoutesID;
_StartDate = fare.StartDate;
_EndDate = fare.EndDate;
}
public static Fare GetFareForSchedule(int routeId, DateTime depDateTime)
{
OQL oql = new OQL(typeof(Fare));
oql.AddCondition(Condition.Eq(Fare.Properties.RoutesID, routeId));
oql.AddCondition(Condition.Le(Fare.Properties.StartDate, depDateTime));
oql.AddCondition(Condition.Ge(Fare.Properties.EndDate, depDateTime));
FareList list = new Fare().GetList(oql);
if (list.Count >= 1)
return list[0];
else
return null;
}
public static Fare GetFareByRouteAndDateRange(int routeId, DateTime startDate, DateTime endDate)
{
OQL oql = new OQL(typeof(Fare));
oql.AddCondition(Condition.Eq(Fare.Properties.RoutesID, routeId));
oql.AddCondition(Condition.Eq(Fare.Properties.StartDate, startDate));
oql.AddCondition(Condition.Eq(Fare.Properties.EndDate, endDate));
FareList list = new Fare().GetList(oql);
if (list.Count == 1)
return list[0];
else if (list.Count > 1)
throw new Exception("Duplicate fare record found.");
else
return null;
}
private void BindControls()
{
Vessel v = new Vessel();
VesselList vlist = v.GetAllList();
this.ddlVessel.DataSource = vlist;
this.ddlVessel.DataTextField = "FullName";
this.ddlVessel.DataValueField = "ID";
this.ddlVessel.DataBind();
this.ddlVessel.Items.Insert(0, new ListItem("-- Show All --", "0"));
Fare f = new Fare();
FareList flist = f.GetAllList();
this.ddlFare.DataSource = flist;
this.ddlFare.DataTextField = "FullName";
this.ddlFare.DataValueField = "ID";
this.ddlFare.DataBind();
this.ddlFare.Items.Insert(0, new ListItem("-- Show All --", "0"));
}
public DataTable ImportFareDumpFile(string fileName)
{
DataTable dt = null;
DataTable dtErrInfo = new DataTable();
dtErrInfo.Columns.Add("RowNumber", typeof(int));
dtErrInfo.Columns.Add("ErrColumnName", typeof(string));
dtErrInfo.Columns.Add("ErrColumnData", typeof(string));
dtErrInfo.Columns.Add("ErrDescription", typeof(string));
dt = this.GetDataTableFromExcel(fileName);
//Check Column in imported Excel file match the template, no missing column
string columnNames;
columnNames = ",Dep,Arr,Dreg,Areg,Category,Facility,Minlength,Maxlength,Amount,Byfootamt,Startdate,Enddate,Recid,Description,";
int rowNumber = 0;
foreach (DataColumn column in dt.Columns)
{
if (columnNames.IndexOf("," + column.ColumnName + ",") < 0)
dtErrInfo.Rows.Add(new object[] { rowNumber, column.ColumnName, "Template Error", "The column in XLS file does not match orinigal template" });
}
if (dtErrInfo.Rows.Count > 0)
return dtErrInfo;
//Check if column “Dep” and “Arr”(Port Code) exists in Port table.
//If not then create port
foreach (DataRow row in dt.Rows)
{
string depPortId = row["Dep"].ToString();
string arrPortId = row["Arr"].ToString();
if (string.IsNullOrEmpty(depPortId) || string.IsNullOrEmpty(arrPortId))
continue;
Port dport = new Port().GetById(depPortId, false);
if (dport == null)
{
Port dp = new Port();
dp.DoInsert(depPortId, depPortId);
}
Port aport = new Port().GetById(arrPortId, false);
if (aport == null)
{
Port ap = new Port();
ap.DoInsert(arrPortId, arrPortId);
}
//Select distinct column “Dep” and “Arr” from Route table, if not exist, insert into Route table
Company c = Company.GetCompanyByShortName("AMHS");
int operatorId = c.ID;
Route r = Route.GetRouteByPortId(depPortId, arrPortId, operatorId);
int routeId;
if (r == null)
{
Route newRoute = new Route();
newRoute.OperatorId = operatorId;
newRoute.DeparturePortId = depPortId;
newRoute.ArriavlPortId = arrPortId;
newRoute.IsActive = true;
Route.DoInsert(newRoute);
routeId = newRoute.ID;
}
else
{
routeId = r.ID;
}
//Create a record in Fare table (RouteID, StartDate, EndDate)
string strStartDate = row["Startdate"].ToString();
string strEndDate = row["Enddate"].ToString();
DateTime startDate = DateTime.MaxValue;
DateTime endDate = DateTime.MaxValue;
if (string.IsNullOrEmpty(strStartDate) || string.IsNullOrEmpty(strEndDate))
{
dtErrInfo.Rows.Add(new object[] { rowNumber, "StartDate/EndDate", "Null", " StartDate/EndDate is worng format or value" });
continue;
}
if (DateTime.TryParse(strStartDate, out startDate) && DateTime.TryParse(strEndDate, out endDate))
{
int fareId;
Fare existingFare = Fare.GetFareByRouteAndDateRange(routeId, startDate, endDate);
if (existingFare == null)
{
Fare newFare = new Fare();
newFare.RoutesID = routeId;
newFare.StartDate = startDate;
newFare.EndDate = endDate;
Fare.DoInsert(newFare);
fareId = newFare.ID;
}
else
{
fareId = existingFare.ID;
}
string strCategory = row["Category"].ToString();
FareCategory fareCategory = FareCategory.GetCategoryByName(strCategory);
if (fareCategory == null)
{
dtErrInfo.Rows.Add(new object[] { rowNumber, "Fare Category", "Null", " Fare Category not found" });
continue;
}
int categoryId = fareCategory.ID;
string strFacility = row["Facility"].ToString();
string strDescription = row["Description"].ToString();
if (!string.IsNullOrEmpty(strFacility) && !string.IsNullOrEmpty(strDescription))
{
int fareTypeId = 0;
int minLength = 0;
int maxLength = 0;
decimal amount = 0.0m;
decimal byFootAmount = 0.0m;
string strMinLength = row["Minlength"].ToString();
string strMaxLength = row["Maxlength"].ToString();
string strAmount = row["Amount"].ToString();
string strByFootAmount = row["Byfootamt"].ToString();
decimal.TryParse(strByFootAmount, out byFootAmount);
FareType existingFareType = FareType.GetFareTypeByValue(operatorId, categoryId, strFacility, strDescription);
if (existingFareType == null)
{
if (decimal.TryParse(strAmount, out amount))
{
int.TryParse(strMinLength, out minLength);
int.TryParse(strMaxLength, out maxLength);
if (strCategory == EnumFareCategory.CARDECK.ToString())
{
VehicleType newVehicleType = new VehicleType();
newVehicleType.OperatorId = operatorId;
newVehicleType.CategoryId = categoryId;
newVehicleType.FareTypeName = strFacility;
newVehicleType.FareTypeDescription = strDescription;
newVehicleType.MinLegth = minLength;
newVehicleType.MaxLegth = maxLength;
newVehicleType.ByFootAmount = byFootAmount;
VehicleType.DoInsert(newVehicleType);
fareTypeId = newVehicleType.ID;
}
else if (strCategory == EnumFareCategory.PASSAGE.ToString())
{
int defaultMinAge = 0;
int defaultMaxAge = 0;
switch (strFacility.ToUpper())
{
case "ADT":
defaultMinAge = 12;
defaultMaxAge = 65;
break;
case "CHD":
defaultMinAge = 6;
defaultMaxAge = 12;
break;
case "SRC":
defaultMinAge = 65;
defaultMaxAge = 99;
break;
case "UND":
defaultMinAge = 0;
defaultMaxAge = 6;
break;
default:
break;
}
PassengerType newPassegerType = new PassengerType();
newPassegerType.OperatorId = operatorId;
newPassegerType.CategoryId = categoryId;
newPassegerType.FareTypeName = strFacility;
newPassegerType.FareTypeDescription = strDescription;
if (minLength == 0)
{
minLength = defaultMinAge;
newPassegerType.MinAge = defaultMinAge;
}
else
newPassegerType.MinAge = minLength;
if (maxLength == 0)
{
maxLength = defaultMaxAge;
newPassegerType.MaxAge = defaultMaxAge;
}
else
newPassegerType.MaxAge = maxLength;
PassengerType.DoInsert(newPassegerType);
fareTypeId = newPassegerType.ID;
}
else
{
FareType newFareType = new FareType();
newFareType.OperatorId = operatorId;
newFareType.CategoryId = categoryId;
newFareType.FareTypeName = strFacility;
newFareType.FareTypeDescription = strDescription;
FareType.DoInsert(newFareType);
fareTypeId = newFareType.ID;
}
}
else
{
dtErrInfo.Rows.Add(new object[] { rowNumber, "Minlength/Maxlength/Amount", "Null", "Minlength/Maxlength/Amount is worng format or value" });
continue;
}
}
else
{
int.TryParse(strMinLength, out minLength);
int.TryParse(strMaxLength, out maxLength);
decimal.TryParse(strAmount, out amount);
fareTypeId = existingFareType.ID;
}
if (FareItem.GetFareItemByValues(fareTypeId, fareId, minLength, maxLength, amount) == null)
{
FareItem newFareItem = new FareItem();
newFareItem.FareTypeId = fareTypeId;
newFareItem.FareId = fareId;
newFareItem.RangeStart = minLength;
newFareItem.RangeEnd = maxLength;
newFareItem.Amount = amount;
newFareItem.ByFootAmount = byFootAmount;
FareItem.DoInsert(newFareItem);
}
}
else
{
dtErrInfo.Rows.Add(new object[] { rowNumber, "Facility/Description", "Null", " Facility/Description is worng format or value" });
continue;
}
}
else
{
dtErrInfo.Rows.Add(new object[] { rowNumber, "StartDate/EndDate", "Null", " StartDate/EndDate is worng format or value" });
continue;
}
}
return dtErrInfo;
}
private void UpdateFare(int id)
{
int routeId;
DateTime startDate;
DateTime endDate;
DropDownList ddlRoute = (DropDownList)this.FV_Fare.FindControl("ddlRoute");
TextBox txtStartDate = (TextBox)this.FV_Fare.FindControl("txtStartDate");
TextBox txtEndDate = (TextBox)this.FV_Fare.FindControl("txtEndDate");
routeId = (ddlRoute == null) ? 0 : Convert.ToInt32(ddlRoute.SelectedValue);
if (DateTime.TryParse(txtStartDate.Text.Trim(), out startDate)
&& DateTime.TryParse(txtEndDate.Text.Trim(), out endDate)
&& routeId != 0)
{
Fare fare = new Fare();
fare.DoUpdate(id, routeId, startDate, endDate);
BindList();
if (ddlRoute != null)
ddlRoute.SelectedIndex = 0;
this.lblMessage.Text = "Update successfully";
this.lblMessage.ForeColor = Color.Green;
}
else
{
this.lblMessage.Text = "Update failed";
this.lblMessage.ForeColor = Color.Red;
}
}
private void InsertFare()
{
int routeId;
DateTime startDate;
DateTime endDate;
DropDownList ddlRoute = (DropDownList)this.FV_Fare.FindControl("ddlRoute");
TextBox txtStartDate = (TextBox)this.FV_Fare.FindControl("txtStartDate");
TextBox txtEndDate = (TextBox)this.FV_Fare.FindControl("txtEndDate");
routeId = (ddlRoute == null) ? 0 : Convert.ToInt32(ddlRoute.SelectedValue);
if (DateTime.TryParse(txtStartDate.Text.Trim(), out startDate)
&& DateTime.TryParse(txtEndDate.Text.Trim(), out endDate)
&& routeId != 0 )
{
_fare = new Fare();
_fare.RoutesID = routeId;
_fare.StartDate = startDate;
_fare.EndDate = endDate;
Fare.DoInsert(_fare);
BindList();
if (ddlRoute != null)
ddlRoute.SelectedIndex = 0;
this.lblMessage.Text = "Insert successfully";
this.lblMessage.ForeColor = Color.Green;
}
else
{
this.lblMessage.Text = "Insert failed";
this.lblMessage.ForeColor = Color.Red;
}
}
private void BindList()
{
Fare fare = new Fare();
int operatorId = 0;
int routeId = 0; ;
if (this.ddlOperator.SelectedIndex != -1)
int.TryParse(this.ddlOperator.SelectedValue, out operatorId);
if (this.ddlRoute.SelectedIndex != -1)
int.TryParse(this.ddlRoute.SelectedValue, out routeId);
DateTime startTime = new DateTime(1900, 1, 1);
DateTime endTime = DateTime.MaxValue;
DateTime.TryParse(this.txtStartDate.Text, out startTime);
DateTime.TryParse(this.txtEndDate.Text, out endTime);
if (startTime == DateTime.MinValue)
startTime = new DateTime(1900, 1, 1);
if (endTime == DateTime.MinValue)
endTime = DateTime.MaxValue;
if (endTime != DateTime.MaxValue)
endTime = endTime.AddDays(1.0);
FareList list = fare.GetFareList(operatorId,routeId, startTime, endTime);
this.GV_FareList.DataSource = list;
this.GV_FareList.DataBind();
this.lblMessage.Text = "";
}
private void BindFareType()
{
DropDownList ddlFareType = (DropDownList)this.FV_FareItem.FindControl("ddlFareType");
if (ddlFareType != null)
{
//if (_fareId == 0)
// _fareId = Convert.ToInt32(this.GV_FareItemList.SelectedValue);
Fare fare = new Fare().GetById(Convert.ToInt32(this.GV_FareList.SelectedValue), false);
if (fare != null)
{
int operatorId = fare.Routes.OperatorId.GetValueOrDefault(0);
FareType ft = new FareType();
FareTypeList list = ft.GetFareTypeList(operatorId, 0);
ddlFareType.DataSource = list;
ddlFareType.DataTextField = "FullName";
ddlFareType.DataValueField = "ID";
ddlFareType.DataBind();
}
}
}
protected void GV_FareList_SelectedIndexChanged(object sender, EventArgs e)
{
//_fareId = Convert.ToInt32(this.GV_FareList.SelectedValue);
Fare currentFare = new Fare().GetById(Convert.ToInt32(this.GV_FareList.SelectedValue), false);
BindFareCategory();
BindItemList(Convert.ToInt32(this.GV_FareList.SelectedValue));
this.pnlFareItems.Visible = true;
this.FV_Fare.Visible = false;
}
public void DoUpdate(int ID, int routeId, DateTime startDate, DateTime endDate)
{
Fare fare = new Fare().GetById(ID, true);
fare.RoutesID = routeId;
fare.StartDate = startDate;
fare.EndDate = endDate;
fare.Update();
}
public void DoUpdate(Fare fare)
{
fare.Update();
}
public void DoDelete(int ID)
{
Fare fare = new Fare().GetById(ID, false);
fare.Delete();
}
public static void DoInsert(Fare fare)
{
fare.Create();
}
public Route GetRouteBySchedule(int scheduleId)
{
Schedule schedule = new Schedule().GetById(scheduleId, false);
Fare fare = new Fare().GetById(schedule.FareId.Value, false);
Route route = new Route().GetById(fare.RoutesID.Value, false);
return route;
}
