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;
}
