public static FareType GetFareTypeByValue(int operatorId, int categoryId, string fareTypeName, string fareTypeDesc)
{
OQL oql = new OQL(typeof(FareType));
if (string.IsNullOrEmpty(fareTypeName) || string.IsNullOrEmpty(fareTypeDesc) || operatorId <= 0 || categoryId <= 0)
return null;
oql.AddCondition(Condition.Eq(FareType.Properties.OperatorId, operatorId));
oql.AddCondition(Condition.Eq(FareType.Properties.CategoryId, categoryId));
oql.AddCondition(Condition.Eq(FareType.Properties.FareTypeName, fareTypeName));
oql.AddCondition(Condition.Eq(FareType.Properties.FareTypeDescription, fareTypeDesc));
FareTypeList list = new FareType().GetList(oql);
if (list.Count == 1)
return list[0];
else if (list.Count > 1)
throw new Exception("Duplicate Fare Type Found");
else
return null;
}
private void UpdateVessel(int id)
{
string vesselCode;
string vesselName;
int operatorId;
TextBox txtVesselCode = (TextBox)this.FV_Vessel.FindControl("txtVesselCode");
TextBox txtVesselName = (TextBox)this.FV_Vessel.FindControl("txtVesselName");
DropDownList ddlOperator = (DropDownList)this.FV_Vessel.FindControl("ddlOperator");
CheckBoxList chklstAccommodation = (CheckBoxList)this.FV_Vessel.FindControl("chklstAccommodation");
vesselCode = (txtVesselCode == null) ? "" : (string.IsNullOrEmpty(txtVesselCode.Text) ? "" : txtVesselCode.Text);
vesselName = (txtVesselName == null) ? "" : (string.IsNullOrEmpty(txtVesselName.Text) ? "" : txtVesselName.Text);
operatorId = (ddlOperator == null) ? 0 : Convert.ToInt32(ddlOperator.SelectedValue);
Vessel vessel = new Vessel();
IList<FareType> ftList = new List<FareType>();
for (int i = 0; i < chklstAccommodation.Items.Count; i++)
{
if (chklstAccommodation.Items[i].Selected)
{
int ftId = Convert.ToInt32(chklstAccommodation.Items[i].Value);
FareType ft = new FareType().GetById(ftId, false);
ftList.Add(ft);
}
}
vessel.DoUpdate(id, vesselCode, vesselName, operatorId, ftList);
BindList();
this.lblMessage.Text = "Update successfully";
this.lblMessage.ForeColor = Color.Green;
}
private void InsertVessel()
{
string vesselCode;
string vesselName;
int operatorId;
TextBox txtVesselCode = (TextBox)this.FV_Vessel.FindControl("txtVesselCode");
TextBox txtVesselName = (TextBox)this.FV_Vessel.FindControl("txtVesselName");
DropDownList ddlOperator = (DropDownList)this.FV_Vessel.FindControl("ddlOperator");
CheckBoxList chklstAccommodation = (CheckBoxList)this.FV_Vessel.FindControl("chklstAccommodation");
vesselCode = (txtVesselCode == null) ? "" : (string.IsNullOrEmpty(txtVesselCode.Text) ? "" : txtVesselCode.Text);
vesselName = (txtVesselName == null) ? "" : (string.IsNullOrEmpty(txtVesselName.Text) ? "" : txtVesselName.Text);
operatorId = (ddlOperator == null) ? 0 : Convert.ToInt32(ddlOperator.SelectedValue);
_vessel = new Vessel();
_vessel.VesselCode = vesselCode;
_vessel.VesselName = vesselName;
_vessel.OperatorId = operatorId;
for (int i = 0; i < chklstAccommodation.Items.Count; i++)
{
if (chklstAccommodation.Items[i].Selected)
{
int ftId = Convert.ToInt32(chklstAccommodation.Items[i].Value);
FareType ft = new FareType().GetById(ftId, false);
_vessel.FareTypes.Add(ft);
}
}
Vessel.DoInsert(_vessel);
BindList();
if (txtVesselCode != null)
txtVesselCode.Text = string.Empty;
if (txtVesselName != null)
txtVesselName.Text = string.Empty;
if (ddlOperator != null)
ddlOperator.SelectedIndex = 0;
this.lblMessage.Text = "Insert successfully";
this.lblMessage.ForeColor = Color.Green;
}
private void BindVesselCabinet(int vesselId)
{
int operatorId;
CheckBoxList chklstAccommodation = (CheckBoxList)this.FV_Vessel.FindControl("chklstAccommodation");
DropDownList ddlOperator = (DropDownList)this.FV_Vessel.FindControl("ddlOperator");
if (ddlOperator == null)
return;
operatorId = Convert.ToInt32(ddlOperator.SelectedValue);
FareType fareType = new FareType();
FareTypeList list = fareType.GetFareTypeList(operatorId, 1);
chklstAccommodation.DataSource = list;
chklstAccommodation.DataTextField = "FullName";
chklstAccommodation.DataValueField = "ID";
chklstAccommodation.DataBind();
if (vesselId != 0)
{
Vessel v = new Vessel().GetById(vesselId, false);
foreach (FareType ft in v.FareTypes)
{
for (int i = 0; i < chklstAccommodation.Items.Count; i++)
{
if (chklstAccommodation.Items[i].Value == ft.ID.ToString())
{
chklstAccommodation.Items[i].Selected = true;
break;
}
}
}
}
}
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 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 lvAccommodation_ItemDataBound(object sender, ListViewItemEventArgs e)
{
if (e.Item.ItemType == ListViewItemType.DataItem)
{
ListViewDataItem dataItem = (ListViewDataItem)e.Item;
RouteOrder r = (RouteOrder)(dataItem.DataItem);
Label lblRoute = (Label)(dataItem.FindControl("lblRoute"));
Route route = new Route().GetRouteBySchedule(r.ScheduleId.Value);
lblRoute.Text = route.DeparturePort.PortName+ " - " + route.ArriavlPort.PortName;
GridView gvAccommodation = (GridView)(dataItem.FindControl("gvAccommodation"));
if (gvAccommodation != null)
{
IList<FareType> ftList = new FareType().GetAccommodationTypeBySchedule(r.ScheduleId.Value);
gvAccommodation.DataSource = ftList;
gvAccommodation.DataBind();
}
}
}
public static void DoInsert(FareType fareType)
{
fareType.Create();
}
