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 InsertPort() { string portCode; string portName; TextBox txtPortCode = (TextBox)this.FV_Port.FindControl("txtPortCode"); TextBox txtPortName = (TextBox)this.FV_Port.FindControl("txtPortName"); portCode = (txtPortCode == null) ? "" : (string.IsNullOrEmpty(txtPortCode.Text) ? "" : txtPortCode.Text.Trim()); portName = (txtPortName == null) ? "" : (string.IsNullOrEmpty(txtPortName.Text) ? "" : txtPortName.Text.Trim()); Port existingPort = new Port().GetById(portCode, false); if (existingPort == null) { Port port = new Port(); port.DoInsert(portCode, portName); BindList(); this.lblMessage.Text = "Insert successfully"; this.lblMessage.ForeColor = Color.Green; } else { this.lblMessage.Text = "Deplicate Port Code found, please try another one."; this.lblMessage.ForeColor = Color.Red; } if (txtPortCode != null) txtPortCode.Text = string.Empty; if (txtPortName != null) txtPortName.Text = string.Empty; }