public void DoUpdate(int ID, int fareTypeId, int rangeStart, int rangeEnd, decimal amount, decimal byFootAmount)
{
FareItem fareItem = new FareItem().GetById(ID, true);
fareItem.FareTypeId = fareTypeId;
fareItem.RangeStart = rangeStart;
fareItem.RangeEnd = rangeEnd;
fareItem.Amount = amount;
fareItem.ByFootAmount = byFootAmount;
fareItem.Update();
}
public FareItemDto(FareItem fareItem)
{
_FareItemId = fareItem.ID;
_FareTypeId = fareItem.FareTypeId;
_FareId = fareItem.FareId;
_Amount = fareItem.Amount;
_RangeStart = fareItem.RangeStart;
_RangeEnd = fareItem.RangeEnd;
_ByFootAmount = fareItem.ByFootAmount;
_RoundTripDiscount = fareItem.RoundTripDiscount;
}
public static FareItem GetFareItemByValues(int fareTypeId, int fareId, int rangeStart, int rangeEnd, decimal amount)
{
OQL oql = new OQL(typeof(FareItem));
oql.AddCondition(Condition.Eq(FareItem.Properties.FareTypeId, fareTypeId));
oql.AddCondition(Condition.Eq(FareItem.Properties.FareId, fareId));
oql.AddCondition(Condition.Eq(FareItem.Properties.RangeStart, rangeStart));
oql.AddCondition(Condition.Eq(FareItem.Properties.RangeEnd, rangeEnd));
oql.AddCondition(Condition.Eq(FareItem.Properties.Amount, amount));
FareItemList list = new FareItem().GetList(oql);
if (list.Count == 1)
return list[0];
else if (list.Count > 1)
throw new Exception("Duplicate FareItem Found");
else
return null;
}
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 UpdateFareItem(int id)
{
int fareId;
int fareTypeId;
int rangeStart;
int rangeEnd;
decimal amount;
decimal byFootAmount;
TextBox lblFare = (TextBox)this.FV_FareItem.FindControl("lblFare");
DropDownList ddlFareType = (DropDownList)this.FV_FareItem.FindControl("ddlFareType");
TextBox txtRangeStart = (TextBox)this.FV_FareItem.FindControl("txtRangeStart");
TextBox txtRangeEnd = (TextBox)this.FV_FareItem.FindControl("txtRangeEnd");
TextBox txtAmount = (TextBox)this.FV_FareItem.FindControl("txtAmount");
TextBox txtByFootAmount = (TextBox)this.FV_FareItem.FindControl("txtByFootAmount");
fareId = (lblFare == null) ? 0 : (string.IsNullOrEmpty(lblFare.Text) ? 0 : Convert.ToInt32(lblFare.Text));
fareTypeId = (ddlFareType == null) ? 0 : Convert.ToInt32(ddlFareType.SelectedValue);
rangeStart = (txtRangeStart == null) ? 0 : (string.IsNullOrEmpty(txtRangeStart.Text) ? 0 : Convert.ToInt32(txtRangeStart.Text));
rangeEnd = (txtRangeEnd == null) ? 0 : (string.IsNullOrEmpty(txtRangeEnd.Text) ? 0 : Convert.ToInt32(txtRangeEnd.Text));
amount = (txtAmount == null) ? 0m : (string.IsNullOrEmpty(txtAmount.Text) ? 0m : Convert.ToDecimal(txtAmount.Text));
byFootAmount = (txtByFootAmount == null) ? 0m : (string.IsNullOrEmpty(txtByFootAmount.Text) ? 0m : Convert.ToDecimal(txtByFootAmount.Text));
FareItem updateItem = new FareItem();
updateItem.DoUpdate(id, fareTypeId, rangeStart, rangeEnd, amount, byFootAmount);
BindItemList(Convert.ToInt32(this.GV_FareList.SelectedValue));
this.lblSubMessage.Text = "Update FareItem successfully";
this.lblSubMessage.ForeColor = Color.Green;
}
private void InsertFareItem()
{
int fareId;
int fareTypeId;
int rangeStart;
int rangeEnd;
decimal amount;
decimal byFootAmount;
TextBox lblFare = (TextBox)this.FV_FareItem.FindControl("lblFare");
DropDownList ddlFareType = (DropDownList)this.FV_FareItem.FindControl("ddlFareType");
TextBox txtRangeStart = (TextBox)this.FV_FareItem.FindControl("txtRangeStart");
TextBox txtRangeEnd = (TextBox)this.FV_FareItem.FindControl("txtRangeEnd");
TextBox txtAmount = (TextBox)this.FV_FareItem.FindControl("txtAmount");
TextBox txtByFootAmount = (TextBox)this.FV_FareItem.FindControl("txtByFootAmount");
fareId = (lblFare == null) ? 0 : (string.IsNullOrEmpty(lblFare.Text) ? 0 : Convert.ToInt32(lblFare.Text));
fareTypeId = (ddlFareType == null) ? 0 : Convert.ToInt32(ddlFareType.SelectedValue);
rangeStart = (txtRangeStart == null) ? 0 : (string.IsNullOrEmpty(txtRangeStart.Text) ? 0 : Convert.ToInt32(txtRangeStart.Text));
rangeEnd = (txtRangeEnd == null) ? 0 : (string.IsNullOrEmpty(txtRangeEnd.Text) ? 0 : Convert.ToInt32(txtRangeEnd.Text));
amount = (txtAmount == null) ? 0m : (string.IsNullOrEmpty(txtAmount.Text) ? 0m : Convert.ToDecimal(txtAmount.Text));
byFootAmount = (txtByFootAmount == null) ? 0m : (string.IsNullOrEmpty(txtByFootAmount.Text) ? 0m : Convert.ToDecimal(txtByFootAmount.Text));
FareItem item = new FareItem();
item.FareId = fareId;
item.FareTypeId = fareTypeId;
item.RangeStart = rangeStart;
item.RangeEnd = rangeEnd;
item.Amount = amount;
item.ByFootAmount = byFootAmount;
FareItem.DoInsert(item);
BindItemList(Convert.ToInt32(this.GV_FareList.SelectedValue));
if (txtRangeStart != null)
txtRangeStart.Text = string.Empty;
if (txtRangeEnd != null)
txtRangeEnd.Text = string.Empty;
if (ddlFareType != null)
ddlFareType.SelectedIndex = 0;
if (txtAmount != null)
txtAmount.Text = string.Empty;
if (txtByFootAmount != null)
txtByFootAmount.Text = string.Empty;
this.lblSubMessage.Text = "Insert FareItem successfully";
this.lblSubMessage.ForeColor = Color.Green;
}
private void BindItemList(int fareId)
{
FareItem fareItem = new FareItem();
int categoryId = 0;
if (this.ddlFareCategory.SelectedIndex != -1)
int.TryParse(this.ddlFareCategory.SelectedValue, out categoryId);
FareItemList list = fareItem.GetFareItemList(categoryId, fareId);
this.GV_FareItemList.DataSource = list;
this.GV_FareItemList.DataBind();
this.lblSubMessage.Text = "";
}
public void DoDelete(int ID)
{
FareItem fareItem = new FareItem().GetById(ID, false);
fareItem.Delete();
}
public static void DoInsert(FareItem fareItem)
{
fareItem.Create();
}
