public DataTable GetDriverPriceRate(ref String Message) { try { DBDataAdpterObject.SelectCommand.Parameters.Clear(); StringBuilder SQLSelect = new StringBuilder(); SQLSelect.Append(@"SELECT * FROM DriverPriceMst "); DBDataAdpterObject.SelectCommand.CommandText = SQLSelect.ToString(); DBDataAdpterObject.TableMappings.Clear(); DBDataAdpterObject.TableMappings.Add("Table", "DriverPriceMst"); DataSet ds = new DataSet(); DBDataAdpterObject.Fill(ds); if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { Message = "Driver Price detail data retrieved successfully "; return(ds.Tables[0]); } else { Message = "Driver Price detail data Not Found "; return(null); } } catch (Exception ex) { ServerLog.MgmtExceptionLog(ex.Message + Environment.NewLine + ex.StackTrace); Message = ex.Message; return(null); } }
public DataTable GetPost_Load_Inquiry(ref IDbDataAdapter adapter, String load_inquiry_no, ref String Message) { try { adapter.SelectCommand.Parameters.Clear(); StringBuilder SQLSelect = new StringBuilder(); SQLSelect.Append(@"SELECT * FROM post_load_inquiry WHERE load_inquiry_no=@load_inquiry_no "); adapter.SelectCommand.CommandText = SQLSelect.ToString(); adapter.SelectCommand.Parameters.Add(BLGeneralUtil.MakeParameter("load_inquiry_no", DbType.String, load_inquiry_no)); adapter.TableMappings.Clear(); adapter.TableMappings.Add("Table", "post_load_inquiry"); DataSet ds = new DataSet(); adapter.Fill(ds); if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { Message = "Post Load Inquiry data retrieved successfully "; return(ds.Tables[0]); } else { Message = "Post Load Inquiry data Not Found "; return(null); } } catch (Exception ex) { ServerLog.MgmtExceptionLog(ex.Message + Environment.NewLine + ex.StackTrace); Message = ex.Message; return(null); } }
public Decimal GetPackingRate(String Packing_type_code, DateTime OrderDate, ref Decimal RatePerSizeTypeCode, ref String Message) { try { DBDataAdpterObject.SelectCommand.Parameters.Clear(); StringBuilder SQLSelect = new StringBuilder(); SQLSelect.Append(@"SELECT * FROM PackingRateDetail WHERE Packing_type_code = @Packing_type_code AND (start_date <= @OrderDate) AND (end_date IS NULL OR end_date >= @OrderDate)"); DBDataAdpterObject.SelectCommand.CommandText = SQLSelect.ToString(); DBDataAdpterObject.SelectCommand.Parameters.Add(BLGeneralUtil.MakeParameter("@Packing_type_code", DbType.String, Packing_type_code)); DBDataAdpterObject.SelectCommand.Parameters.Add(BLGeneralUtil.MakeParameter("@OrderDate", DbType.DateTime, OrderDate)); DBDataAdpterObject.TableMappings.Clear(); DBDataAdpterObject.TableMappings.Add("Table", "PackingRateDetail"); DataSet ds = new DataSet(); DBDataAdpterObject.Fill(ds); if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { Message = "Packing Rate detail data retrieved successfully for Packing_type_code : " + Packing_type_code; RatePerSizeTypeCode = Convert.ToDecimal(ds.Tables[0].Rows[0]["rate"]); return(RatePerSizeTypeCode); } else { Message = "Packing Rate detail data is not found for Packing_type_code : " + Packing_type_code; return(-1); } } catch (Exception ex) { ServerLog.MgmtExceptionLog(ex.Message + Environment.NewLine + ex.StackTrace); Message = ex.Message; return(-1); } }
public Decimal GetConversionFactor(String FromUOM, String ToUOM, ref String Message) { try { DBDataAdpterObject.SelectCommand.Parameters.Clear(); StringBuilder SQLSelect = new StringBuilder(); SQLSelect.Append(@"SELECT * FROM DistanceConversion WHERE (FromUOM = @FromUOM) AND (ToUOM <= @ToUOM) "); DBDataAdpterObject.SelectCommand.CommandText = SQLSelect.ToString(); DBDataAdpterObject.SelectCommand.Parameters.Add(BLGeneralUtil.MakeParameter("@FromUOM", DbType.String, FromUOM)); DBDataAdpterObject.SelectCommand.Parameters.Add(BLGeneralUtil.MakeParameter("@ToUOM", DbType.String, ToUOM)); DBDataAdpterObject.TableMappings.Clear(); DBDataAdpterObject.TableMappings.Add("Table", "DistanceConversion"); DataSet ds = new DataSet(); DBDataAdpterObject.Fill(ds); if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { Message = "Conversion Factor retrieved successfully."; return(Convert.ToDecimal(ds.Tables[0].Rows[0]["ConversionFactor"]));; } else { Message = "Fail to get Conversion Factor."; return(-1); } } catch (Exception ex) { ServerLog.MgmtExceptionLog(ex.Message + Environment.NewLine + ex.StackTrace); Message = ex.Message; return(-1); } }
public DataTable GetSizeTypeDetail(String SizeTypeCode, Decimal TotalDistance, String TotalDistanceUOM, ref String Message) { try { DBDataAdpterObject.SelectCommand.Parameters.Clear(); StringBuilder SQLSelect = new StringBuilder(); SQLSelect.Append(@"SELECT SizeTypeCode, SizeTypeDesc, TruckTypeMst.truck_type_code, truck_type_desc, NoOfTruck, 0.0000 AS MinDistance, CAST(NULL AS varchar) AS MinDistanceUOM, DriverTypeMst.driver_type_code, driver_type_desc, NoOfDriver, 0.0000 AS RatePerDriver, 0.0000 AS TotalDriverRate, LabourTypeMst.labour_type_code, labour_type_desc, NoOfLabour, 0.0000 AS RatePerLabour, 0.0000 AS TotalLabourRate, HandimanTypeMst.Handiman_type_code, Handiman_type_desc, NoOfHandiman, 0.0000 AS RatePerHandiman, 0.0000 AS TotalHandimanRate, @TotalDistance AS TotalDistance, DistanceTypeMst.distance_type_code, distance_type_desc, 0.0000 AS RatePerDistanceUOM, 0.0000 AS TotalDistanceRate, TimeForLoadingInMinute, TimeForUnloadingInMinute, 0.0000 AS TimeToTravelInMinute, 0.0000 AS TotalTimeForPostOrder, 0.0000 AS NoOfDay, 0.0000 AS Total_cost ,SizeTypeMst.Packing_rate AS TotalPackingRate FROM SizeTypeMst LEFT OUTER JOIN TruckTypeMst ON SizeTypeMst.truck_type_code = TruckTypeMst.truck_type_code LEFT OUTER JOIN DriverTypeMst ON SizeTypeMst.driver_type_code = DriverTypeMst.driver_type_code LEFT OUTER JOIN LabourTypeMst ON SizeTypeMst.labour_type_code = LabourTypeMst.labour_type_code LEFT OUTER JOIN HandimanTypeMst ON SizeTypeMst.Handiman_type_code = HandimanTypeMst.Handiman_type_code CROSS JOIN DistanceTypeMst WHERE SizeTypeCode = @SizeTypeCode AND DistanceTypeMst.distance_type_code = @distance_type_code "); DBDataAdpterObject.SelectCommand.CommandText = SQLSelect.ToString(); DBDataAdpterObject.SelectCommand.Parameters.Add(BLGeneralUtil.MakeParameter("@SizeTypeCode", DbType.String, SizeTypeCode)); DBDataAdpterObject.SelectCommand.Parameters.Add(BLGeneralUtil.MakeParameter("@TotalDistance", DbType.Decimal, TotalDistance)); DBDataAdpterObject.SelectCommand.Parameters.Add(BLGeneralUtil.MakeParameter("@distance_type_code", DbType.String, TotalDistanceUOM)); DBDataAdpterObject.TableMappings.Clear(); DBDataAdpterObject.TableMappings.Add("Table", "SizeTypeMst"); DataSet ds = new DataSet(); DBDataAdpterObject.Fill(ds); if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { Message = "SizeType detail data retrieved successfully for SizeTypeCode : " + SizeTypeCode; return(ds.Tables[0]); } else { Message = "SizeType detail data is not found for SizeTypeCode : " + SizeTypeCode; return(null); } } catch (Exception ex) { ServerLog.MgmtExceptionLog(ex.Message + Environment.NewLine + ex.StackTrace); Message = ex.Message; return(null); } }
public DataTable GetParameter(String Parameter, String Code, ref String Message) { try { DBDataAdpterObject.SelectCommand.Parameters.Clear(); StringBuilder SQLSelect = new StringBuilder(); SQLSelect.Append("SELECT * FROM ParameterMst "); SQLSelect.Append("WHERE 1=1 "); if (Parameter != null && Parameter.Trim() != String.Empty) { SQLSelect.Append("AND (Parameter=@Parameter) "); DBDataAdpterObject.SelectCommand.Parameters.Add(BLGeneralUtil.MakeParameter("@Parameter", DbType.String, Parameter)); } if (Code != null && Code.Trim() != String.Empty) { SQLSelect.Append("AND (Code=@Code) "); DBDataAdpterObject.SelectCommand.Parameters.Add(BLGeneralUtil.MakeParameter("@Code", DbType.String, Code)); } DBDataAdpterObject.SelectCommand.CommandText = SQLSelect.ToString(); DBDataAdpterObject.TableMappings.Clear(); DBDataAdpterObject.TableMappings.Add("Table", "ParameterMst"); DataSet ds = new DataSet(); DBDataAdpterObject.Fill(ds); if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { Message = "ParameterMst retrieved successfully."; return(ds.Tables[0]); } else { Message = "ParameterMst not found."; return(null); } } catch (Exception ex) { Message = ex.Message; ServerLog.MgmtExceptionLog(ex.Message + Environment.NewLine + ex.StackTrace); return(null); } }
public Byte GetTruckRate(String truck_type_code, String rate_type_flag, int NoOfTruck, Decimal NoOfDay, DateTime OrderDate, ref Decimal MinDistance, ref String MinDistanceUOM, ref String Message) { try { DBDataAdpterObject.SelectCommand.Parameters.Clear(); StringBuilder SQLSelect = new StringBuilder(); SQLSelect.Append(@"SELECT * FROM TruckRateDetail WHERE truck_type_code = @truck_type_code AND rate_type_flag = @rate_type_flag AND (start_date <= @OrderDate) AND (end_date IS NULL OR end_date >= @OrderDate)"); DBDataAdpterObject.SelectCommand.CommandText = SQLSelect.ToString(); DBDataAdpterObject.SelectCommand.Parameters.Add(BLGeneralUtil.MakeParameter("@truck_type_code", DbType.String, truck_type_code)); DBDataAdpterObject.SelectCommand.Parameters.Add(BLGeneralUtil.MakeParameter("@rate_type_flag", DbType.String, rate_type_flag)); DBDataAdpterObject.SelectCommand.Parameters.Add(BLGeneralUtil.MakeParameter("@OrderDate", DbType.DateTime, OrderDate)); DBDataAdpterObject.TableMappings.Clear(); DBDataAdpterObject.TableMappings.Add("Table", "TruckRateDetail"); DataSet ds = new DataSet(); DBDataAdpterObject.Fill(ds); if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { Message = "Truck Rate detail data retrieved successfully for truck_type_code : " + truck_type_code; MinDistance = Convert.ToDecimal(ds.Tables[0].Rows[0]["MinDistance"]); MinDistanceUOM = ds.Tables[0].Rows[0]["MinDistanceUOM"].ToString(); return(1); } else { Message = "Truck Rate detail data is not found for truck_type_code : " + truck_type_code; return(2); } } catch (Exception ex) { ServerLog.MgmtExceptionLog(ex.Message + Environment.NewLine + ex.StackTrace); Message = ex.Message; return(2); } }
public DataTable CalculateRate(String SizeTypeCode, String rate_type_flag, Decimal TotalDistance, String TotalDistanceUOM, Decimal TimeToTravelInMinute, String IncludePackingCharge, int?NoOfTruck, int?NoOfDriver, int?NoOfLabour, int?NoOfHandiman, ref String Message) { try { Decimal NoOfDay = 1.0M; DateTime OrderDate = DateTime.Today; if (SizeTypeCode.Trim() == String.Empty) { Message = "Please supplied SizeTypeCode."; return(null); } else if (TotalDistance < 0) { Message = "Please supplied TotalDistance."; return(null); } else if (TotalDistanceUOM.Trim() == String.Empty) { Message = "Please supplied TotalDistance Unit."; return(null); } else if (TimeToTravelInMinute < 0) { Message = "Please supplied Time To Travel In Minute."; return(null); } ParameterMst objParameterMst = new ParameterMst(); DataTable dtParameter = objParameterMst.GetParameter("FullDayRule", null, ref Message); if (dtParameter == null || dtParameter.Rows.Count <= 0) { return(null); } //FullDayRule Decimal FullDayRule = 0.6M, WorkingHours = 8.0M; DataRow[] drArray = dtParameter.Select("Code='FullDayRule'"); if (drArray != null && drArray.Length > 0 && drArray[0]["Value"].ToString().Trim() != String.Empty) { FullDayRule = Convert.ToDecimal(drArray[0]["Value"]); } drArray = dtParameter.Select("Code='WorkingHours'"); if (drArray != null && drArray.Length > 0 && drArray[0]["Value"].ToString().Trim() != String.Empty) { WorkingHours = Convert.ToDecimal(drArray[0]["Value"]); } DataTable dtSizeTypeMst = GetSizeTypeDetail(SizeTypeCode, TotalDistance, TotalDistanceUOM, ref Message); if (dtSizeTypeMst == null || dtSizeTypeMst.Rows.Count <= 0) { return(null); } else { Decimal TimeForLoadingInMinute = Convert.ToDecimal(dtSizeTypeMst.Rows[0]["TimeForLoadingInMinute"]); Decimal TimeForUnloadingInMinute = Convert.ToDecimal(dtSizeTypeMst.Rows[0]["TimeForUnloadingInMinute"]); Decimal TotalTimeForPostOrder = TimeForLoadingInMinute + TimeToTravelInMinute + TimeForUnloadingInMinute; Decimal NoCompleteDay = TotalTimeForPostOrder / (60 * WorkingHours); Decimal IncompleteDay = NoCompleteDay - Math.Floor(NoCompleteDay); if (IncompleteDay < FullDayRule) { NoOfDay = Math.Floor(NoCompleteDay) + 0.5M; } else { NoOfDay = Math.Floor(NoCompleteDay) + 1.0M; } //String Packing_type_code = dtSizeTypeMst.Rows[0]["Packing_type_code"].ToString(); //Truck Rate String truck_type_code = dtSizeTypeMst.Rows[0]["truck_type_code"].ToString(); if (!NoOfTruck.HasValue) { NoOfTruck = Convert.ToInt32(dtSizeTypeMst.Rows[0]["NoOfTruck"]); } Decimal MinDistance = 0; String MinDistanceUOM = String.Empty; Byte result = GetTruckRate(truck_type_code, rate_type_flag, NoOfTruck.Value, NoOfDay, OrderDate, ref MinDistance, ref MinDistanceUOM, ref Message); if (result != 1) { return(null); } else { //Driver Rate String driver_type_code = dtSizeTypeMst.Rows[0]["driver_type_code"].ToString(); if (!NoOfDriver.HasValue) { NoOfDriver = Convert.ToInt32(dtSizeTypeMst.Rows[0]["NoOfDriver"]); } Decimal RatePerDriver = -1, TotalDriverRate = GetDriverRate(driver_type_code, rate_type_flag, NoOfDriver.Value, NoOfDay, OrderDate, ref RatePerDriver, ref Message); if (TotalDriverRate < 0) { return(null); } else { //Labour Rate String labour_type_code = dtSizeTypeMst.Rows[0]["labour_type_code"].ToString(); if (!NoOfLabour.HasValue) { NoOfLabour = Convert.ToInt32(dtSizeTypeMst.Rows[0]["NoOfLabour"]); } Decimal RatePerLabour = -1, TotalLabourRate = GetLabourRate(labour_type_code, rate_type_flag, NoOfLabour.Value, NoOfDay, OrderDate, ref RatePerLabour, ref Message); if (TotalLabourRate < 0) { return(null); } else { //Handiman Rate String Handiman_type_code = dtSizeTypeMst.Rows[0]["Handiman_type_code"].ToString(); if (!NoOfHandiman.HasValue) { NoOfHandiman = Convert.ToInt32(dtSizeTypeMst.Rows[0]["NoOfHandiman"]); } Decimal RatePerHandiman = -1, TotalHandimanRate = GetHandimanRate(Handiman_type_code, rate_type_flag, NoOfHandiman.Value, NoOfDay, OrderDate, ref RatePerHandiman, ref Message); if (TotalHandimanRate < 0) { return(null); } else { ////Helper Rate //String helper_type_code = dtSizeTypeMst.Rows[0]["helper_type_code"].ToString(); //if (!NoOfHelper.HasValue) // NoOfHelper = Convert.ToInt32(dtSizeTypeMst.Rows[0]["NoOfHelper"]); //Decimal RatePerHelper = -1, TotalHelperRate = GetHelperRate(helper_type_code, NoOfHelper.Value, NoOfDay, OrderDate, ref RatePerHelper, ref Message); //if (TotalHelperRate < 0) // return null; //else //{ if (MinDistanceUOM.ToUpper() != TotalDistanceUOM.ToUpper()) { Decimal ConversionFactor = GetConversionFactor(MinDistanceUOM, TotalDistanceUOM, ref Message); if (ConversionFactor < 0) { return(null); } else { MinDistance = MinDistance * ConversionFactor; MinDistanceUOM = TotalDistanceUOM; } } if (TotalDistance < MinDistance) { TotalDistance = MinDistance; } //Total Distance Rate Decimal RatePerDistanceUOM = -1, TotalDistanceRate = GetDistanceRate(TotalDistanceUOM, rate_type_flag, TotalDistance, NoOfTruck.Value, OrderDate, ref RatePerDistanceUOM, ref Message); if (TotalDistanceRate < 0) { return(null); } else { Decimal TotalPackingRate = 0; if (IncludePackingCharge == "N") { TotalPackingRate = 0; } else { TotalPackingRate = Convert.ToDecimal(dtSizeTypeMst.Rows[0]["TotalPackingRate"]); } //Decimal TotalPackingRate = 0; //if (dtPostOrderParameter.Rows[0]["IncludePackingCharge"].ToString() == "Y") //{ // Decimal RatePerSizeTypeCode = -1; // TotalPackingRate = objTruckerMaster.GetPackingRate(Packing_type_code, OrderDate, ref RatePerSizeTypeCode, ref Message); // if (TotalPackingRate < 0) // return null; //} Decimal BaseRate = TotalDistanceRate + TotalDriverRate; Decimal TotalRate = TotalDriverRate + TotalLabourRate + TotalHandimanRate + TotalDistanceRate + TotalPackingRate; dtSizeTypeMst.Rows[0]["MinDistance"] = MinDistance; dtSizeTypeMst.Rows[0]["MinDistanceUOM"] = MinDistanceUOM; dtSizeTypeMst.Rows[0]["RatePerDriver"] = RatePerDriver; dtSizeTypeMst.Rows[0]["TotalDriverRate"] = TotalDriverRate; dtSizeTypeMst.Rows[0]["RatePerLabour"] = RatePerLabour; dtSizeTypeMst.Rows[0]["TotalLabourRate"] = TotalLabourRate; dtSizeTypeMst.Rows[0]["RatePerHandiman"] = RatePerHandiman; dtSizeTypeMst.Rows[0]["TotalHandimanRate"] = TotalHandimanRate; //dtSizeTypeMst.Rows[0]["RatePerHelper"] = RatePerHelper; //dtSizeTypeMst.Rows[0]["TotalHelperRate"] = TotalHelperRate; dtSizeTypeMst.Rows[0]["RatePerDistanceUOM"] = RatePerDistanceUOM; dtSizeTypeMst.Rows[0]["TotalDistanceRate"] = TotalDistanceRate; dtSizeTypeMst.Rows[0]["TimeToTravelInMinute"] = TotalTimeForPostOrder; dtSizeTypeMst.Rows[0]["TotalTimeForPostOrder"] = TotalTimeForPostOrder; dtSizeTypeMst.Rows[0]["NoOfDay"] = NoOfDay; dtSizeTypeMst.Rows[0]["Total_cost"] = TotalRate; dtSizeTypeMst.Rows[0]["NoOfTruck"] = NoOfTruck.ToString() == dtSizeTypeMst.Rows[0]["NoOfTruck"].ToString() ? dtSizeTypeMst.Rows[0]["NoOfTruck"] : NoOfTruck; dtSizeTypeMst.Rows[0]["NoOfDriver"] = NoOfDriver.ToString() == dtSizeTypeMst.Rows[0]["NoOfDriver"].ToString() ? dtSizeTypeMst.Rows[0]["NoOfDriver"] : NoOfDriver; dtSizeTypeMst.Rows[0]["NoOfHandiman"] = NoOfHandiman.ToString() == dtSizeTypeMst.Rows[0]["NoOfHandiman"].ToString() ? dtSizeTypeMst.Rows[0]["NoOfHandiman"] : NoOfHandiman; //dtSizeTypeMst.Rows[0]["NoOfHelper"] = NoOfHelper.ToString() == dtSizeTypeMst.Rows[0]["NoOfHelper"].ToString() ? dtSizeTypeMst.Rows[0]["NoOfHelper"] : NoOfHelper; dtSizeTypeMst.Rows[0]["NoOfLabour"] = NoOfLabour.ToString() == dtSizeTypeMst.Rows[0]["NoOfLabour"].ToString() ? dtSizeTypeMst.Rows[0]["NoOfLabour"] : NoOfLabour; dtSizeTypeMst.Columns.Add("BaseRate", typeof(String)); dtSizeTypeMst.Rows[0]["BaseRate"] = BaseRate; dtSizeTypeMst.Columns.Add("rate_type_flag", typeof(String)); dtSizeTypeMst.Rows[0]["rate_type_flag"] = rate_type_flag; return(dtSizeTypeMst); } } } } } } } catch (Exception ex) { ServerLog.MgmtExceptionLog(ex.Message + Environment.NewLine + ex.StackTrace); Message = ex.Message; return(null); } }