public VehicleTypeDto(VehicleType vehicleType)
: base(vehicleType)
{
_MinLegth = vehicleType.MinLegth;
_MaxLegth = vehicleType.MaxLegth;
_ByFootAmount = vehicleType.ByFootAmount;
}
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;
}
public static void DoInsert(VehicleType vehicleType)
{
vehicleType.Create();
}
