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