public List<AreaCountry> GetAreaCountryByCarrierAreaId(int id)
{
List<AreaCountry> result = new List<AreaCountry>();
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputIntParameter("@carrier_area_id", id)
};
string sql = "SELECT id, carrier_area_id, country_id FROM area_countries WHERE carrier_area_id = @carrier_area_id";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
AreaCountry ac = new AreaCountry();
ac.Id = dr.GetInt32(0);
CarrierArea ca = new CarrierAreaDAL().GetCarrierAreaById(dr.GetInt32(1));
ac.CarrierArea = ca;
Country country = new CountryDAL().GetCountryById(dr.GetInt32(2));
ac.Country = country;
result.Add(ac);
}
}
return result;
}
public List<QuoteDetail> GetQuoteDetailByQuoteId(int id)
{
List<QuoteDetail> result = new List<QuoteDetail>();
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputIntParameter("@quote_id", id)
};
string sql = "SELECT id, quote_id, carrier_id, carrier_area_id, discount, preferential_gram, is_register_abate, register_costs FROM quote_details WHERE quote_id = @quote_id AND is_delete = 0";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
QuoteDetail qd = new QuoteDetail();
qd.Id = dr.GetInt32(0);
qd.QuoteId = dr.GetInt32(1);
Carrier carrier = new CarrierDAL().GetCarrierById(dr.GetInt32(2));
qd.Carrier = carrier;
CarrierArea ca = new CarrierAreaDAL().GetCarrierAreaById(dr.GetInt32(3));
qd.CarrierArea = ca;
qd.Discount = dr.GetDecimal(4);
qd.PreferentialGram = dr.GetDecimal(5);
qd.IsRegisterAbate = dr.GetBoolean(6);
qd.RegisterCosts = dr.GetDecimal(7);
result.Add(qd);
}
}
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;
}
