public List<CarrierCharge> GetCarrierCharge(int countryId, decimal weight, byte type, int count, int clientId) { List<CarrierCharge> result = new List<CarrierCharge>(); SqlParameter[] param = new SqlParameter[] { SqlUtilities.GenerateInputIntParameter("@country_id", countryId), SqlUtilities.GenerateInputParameter("@weight", SqlDbType.Decimal, weight), SqlUtilities.GenerateInputParameter("@type", SqlDbType.TinyInt, type), SqlUtilities.GenerateInputIntParameter("@count", count) }; string sql = "SELECT CA.carrier_id, CA.id, CS.id FROM carrier_area AS CA INNER JOIN charge_standards AS CS ON CA.id = CS.carrier_area_id WHERE carrier_area_id IN(SELECT carrier_area_id FROM area_countries WHERE country_id = @country_id) AND start_weight <= @weight AND end_weight >= @weight AND goods_type = @type AND CS.carrier_id IN(SELECT id FROM carriers WHERE is_client_show=1 AND is_delete = 0 AND min_weight<= @weight AND (max_weight >= @weight OR max_weight = 0))"; using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param)) { while (dr.Read()) { CarrierCharge cc = new CarrierCharge(); Carrier carrier = new CarrierDAL().GetCarrierById(dr.GetInt32(0)); cc.Carrier = carrier; cc = GetClientCarrierChargeByParameter(countryId, weight, type, count, carrier.Id, clientId); result.Add(cc); } } result.Sort(); return result; }
public CarrierCharge GetSelfCarrierChargeByParameter(int countryId, decimal weight, byte type, int count, int carrierId, int clientId) { CarrierCharge cc = null; SqlParameter[] param = new SqlParameter[] { SqlUtilities.GenerateInputIntParameter("@country_id", countryId), SqlUtilities.GenerateInputParameter("@weight", SqlDbType.Decimal, weight), SqlUtilities.GenerateInputParameter("@type", SqlDbType.TinyInt, type), SqlUtilities.GenerateInputIntParameter("@count", count), SqlUtilities.GenerateInputIntParameter("@carrier_id", carrierId) }; string sql = "SELECT CA.carrier_id, CA.id, CS.id FROM carrier_area AS CA INNER JOIN charge_standards AS CS ON CA.id = CS.carrier_area_id WHERE carrier_area_id IN(SELECT carrier_area_id FROM area_countries WHERE country_id = @country_id) AND start_weight <= @weight AND end_weight >= @weight AND goods_type = @type AND CS.carrier_id = @carrier_id"; using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param)) { while (dr.Read()) { cc = new CarrierCharge(); Carrier carrier = new CarrierDAL().GetCarrierById(dr.GetInt32(0)); cc.Carrier = carrier; CarrierArea ca = new CarrierAreaDAL().GetCarrierAreaById(dr.GetInt32(1)); cc.CarrierArea = ca; ChargeStandard cs = GetChargeStandardById(dr.GetInt32(2)); cc.ChargeStandard = cs; if (cs.PreferentialGram > 0) { if (weight > (cs.PreferentialGram / 1000)) { weight = weight - cs.PreferentialGram / 1000; cs = GetActualWeightChargeStandardByParameters(countryId, weight, type, carrierId); if (cs != null) { cc.ChargeStandard = cs; } } } if(cs.SelfKgPrice>0) { cc.SelfPostCost = cs.SelfKgPrice * (Math.Ceiling(weight /1))*count; cc.SelfTotalCost = Math.Round(cc.SelfPostCost + (cs.SelfDisposalCost + cs.SelfRegisterCost + cc.ClientPostCost * carrier.FuelSgRate) * count, 5); } else { decimal newWeight = weight - cs.BaseWeight; if (newWeight < 0) { newWeight = 0; } cc.SelfPostCost = (cs.SelfBasePrice + cs.SelfContinuePrice * (Math.Ceiling(newWeight / cs.IncreaseWeight))) * count; cc.SelfTotalCost = Math.Round(cc.SelfPostCost + (cs.SelfDisposalCost + cs.SelfRegisterCost + cc.SelfPostCost * carrier.FuelSgRate) * count, 5); } } } return cc; }