public PaginationQueryResult<CarrierArea> GetCarrierArea(PaginationQueryCondition condition)
{
PaginationQueryResult<CarrierArea> result = new PaginationQueryResult<CarrierArea>();
string sql = "SELECT TOP " + condition.PageSize + " id, carrier_id, name , encode FROM carrier_area ";
if (condition.CurrentPage > 1)
{
sql += " WHERE id > (SELECT MAX(id) FROM (SELECT TOP " + condition.PageSize*(condition.CurrentPage-1) + " id FROM carrier_area ORDER BY encode ASC) AS C)";
}
sql += " ORDER BY encode ASC; SELECT COUNT(*) FROM carrier_area ";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, null))
{
while (dr.Read())
{
CarrierArea ca = new CarrierArea();
ca.Id = dr.GetInt32(0);
Carrier carrier = new CarrierDAL().GetCarrierById(dr.GetInt32(1));
ca.Carrier = carrier;
ca.Name = dr.GetString(2);
ca.Encode = dr.GetString(3);
result.Results.Add(ca);
}
dr.NextResult();
while (dr.Read())
{
result.TotalCount = dr.GetInt32(0);
}
}
return result;
}
public List<PostPlan> GetPostPlan()
{
List<PostPlan> result = new List<PostPlan>();
string sql = "SELECT id, carrier_id, company_id, package_count, weight, depot_id, user_id, create_time FROM post_plans WHERE is_delete = 0";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, null))
{
while (dr.Read())
{
PostPlan pp = new PostPlan();
pp.Id = dr.GetInt32(0);
Carrier carrier = new CarrierDAL().GetCarrierById(dr.GetInt32(1));
pp.Carrier = carrier;
pp.CompanyId = dr.GetInt32(2);
pp.PackageCount = dr.GetInt32(3);
pp.Weight = dr.GetDecimal(4);
Depot depot = new DepotDAL().GetDepotById(dr.GetInt32(5));
pp.Depot = depot;
User user = new UserDAL().GetUserById(dr.GetInt32(6));
pp.User = user;
pp.CreateTime = dr.GetDateTime(7);
result.Add(pp);
}
}
return result;
}
public List<CarrierArea> GetCarrierAreaByCarrierId(int id)
{
List<CarrierArea> result = new List<CarrierArea>();
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputIntParameter("@carrier_id", id)
};
string sql = "SELECT id, carrier_id, name, encode FROM carrier_area WHERE carrier_id = @carrier_id";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
CarrierArea ca = new CarrierArea();
ca.Id = dr.GetInt32(0);
Carrier carrier = new CarrierDAL().GetCarrierById(dr.GetInt32(1));
ca.Carrier = carrier;
ca.Name = dr.GetString(2);
ca.Encode = dr.GetString(3);
result.Add(ca);
}
}
return result;
}
public PaginationQueryResult<PostPlan> GetPostPlanByCompanyId(PaginationQueryCondition condition, int compId)
{
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputIntParameter("@company_id", compId)
};
PaginationQueryResult<PostPlan> result = new PaginationQueryResult<PostPlan>();
string sql = "SELECT TOP " + condition.PageSize + " id, carrier_id, company_id, package_count, weight, depot_id, user_id, create_time FROM post_plans WHERE company_id = @company_id AND is_delete = 0";
if (condition.CurrentPage > 1)
{
sql += " AND id < (SELECT MIN(id) FROM (SELECT TOP " + condition.PageSize*(condition.CurrentPage - 1) + " id FROM post_plans WHERE company_id = @company_id AND is_delete = 0 ORDER BY id DESC) AS P)";
}
sql += " ORDER BY id DESC; SELECT COUNT(*) FROM post_plans WHERE company_id = @company_id AND is_delete = 0";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
PostPlan pp = new PostPlan();
pp.Id = dr.GetInt32(0);
Carrier carrier = new CarrierDAL().GetCarrierById(dr.GetInt32(1));
pp.Carrier = carrier;
pp.CompanyId = dr.GetInt32(2);
pp.PackageCount = dr.GetInt32(3);
pp.Weight = dr.GetDecimal(4);
Depot depot = new DepotDAL().GetDepotById(dr.GetInt32(5));
pp.Depot = depot;
User user = new UserDAL().GetUserById(dr.GetInt32(6));
pp.User = user;
pp.CreateTime = dr.GetDateTime(7);
result.Results.Add(pp);
}
dr.NextResult();
while (dr.Read())
{
result.TotalCount = dr.GetInt32(0);
}
}
return result;
}
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 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;
}
public PostPlan GetPostPlanById(int id)
{
PostPlan pp = null;
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputIntParameter("@id", id)
};
string sql = "SELECT id, carrier_id, company_id, package_count, weight, depot_id, user_id, create_time FROM post_plans WHERE id = @id";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
pp = new PostPlan();
pp.Id = dr.GetInt32(0);
Carrier carrier = new CarrierDAL().GetCarrierById(dr.GetInt32(1));
pp.Carrier = carrier;
pp.CompanyId = dr.GetInt32(2);
pp.PackageCount = dr.GetInt32(3);
pp.Weight = dr.GetDecimal(4);
Depot depot = new DepotDAL().GetDepotById(dr.GetInt32(5));
pp.Depot = depot;
User user = new UserDAL().GetUserById(dr.GetInt32(6));
pp.User = user;
pp.CreateTime = dr.GetDateTime(7);
}
}
return pp;
}