public static PaginationQueryCondition GetCurrentPaginationQueryCondition(HttpRequest request)
{
int page = GetCurrentPage(request);
int size = GetPageSize(request);
PaginationQueryCondition condition = new PaginationQueryCondition(page, size);
return condition;
}
public PaginationQueryResult<User> GetLightUser(PaginationQueryCondition condition)
{
PaginationQueryResult<User> result = new PaginationQueryResult<User>();
StringBuilder sb = new StringBuilder();
sb.Append("SELECT id, username, real_name, sex, education, mobile, email, join_date, contract_date, commission, company_id FROM (SELECT id, username, real_name, sex, education, mobile, email, join_date, contract_date, commission, company_id, row_number() over (ORDER BY Id DESC) AS RN FROM users WHERE is_delete = 0) AS Result WHERE RN BETWEEN ");
sb.Append((condition.CurrentPage - 1) * condition.PageSize + 1);
sb.Append(" AND ");
sb.Append(condition.CurrentPage * condition.PageSize);
sb.Append(";SELECT COUNT(*) FROM users where is_delete = 0");
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sb.ToString(), null))
{
while (dr.Read())
{
User user = new User();
user.Id = dr.GetInt32(0);
user.Username = dr.GetString(1);
user.RealName = dr.GetString(2);
user.Sex = dr.GetBoolean(3);
user.Education = dr.GetString(4);
user.Mobile = dr.GetString(5);
user.Email = dr.GetString(6);
user.JoinDate = dr.GetDateTime(7);
user.ContractDate = dr.GetDateTime(8);
user.Commission = dr.GetDecimal(9);
user.CompanyId = dr.GetInt32(10);
result.Results.Add(user);
}
dr.NextResult();
while (dr.Read())
{
result.TotalCount = dr.GetInt32(0);
}
}
return result;
}
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 PaginationQueryResult<News> GetNews(PaginationQueryCondition condition)
{
PaginationQueryResult<News> result = new PaginationQueryResult<News>();
string sql = "SELECT TOP " + condition.PageSize + " news.id, title, content, create_time, news.category_id, news_categories.name FROM news INNER JOIN news_categories ON (news_categories.id = news.category_id) ";
if (condition.CurrentPage > 1)
sql += " WHERE news.id<(SELECT MIN(id) FROM (SELECT TOP " + condition.PageSize * (condition.CurrentPage - 1) + " news.id FROM news INNER JOIN news_categories ON (news_categories.id = news.category_id) ORDER BY news.id DESC) AS D)";
sql += " ORDER BY news.id DESC; SELECT COUNT(*) FROM news INNER JOIN news_categories ON (news_categories.id = news.category_id)";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, null))
{
while (dr.Read())
{
News news = new News();
news.Id = dr.GetInt32(0);
news.Title = dr.GetString(1);
news.Content = dr.GetString(2);
news.CreateTime = dr.GetDateTime(3);
news.Category = new NewsCategory();
news.Category.Id = dr.GetInt32(4);
news.Category.Name = dr.GetString(5);
result.Results.Add(news);
}
dr.NextResult();
while (dr.Read())
{
result.TotalCount = dr.GetInt32(0);
}
}
return result;
}
public PaginationQueryResult<Country> GetCountry(PaginationQueryCondition condition)
{
PaginationQueryResult<Country> result = new PaginationQueryResult<Country>();
string sql = "SELECT TOP " + condition.PageSize + " id, english_name, chinese_name, code, continent, is_front FROM countries";
if (condition.CurrentPage > 1)
{
sql += " WHERE id< (SELECT MIN(id) FROM (SELECT TOP " + condition.PageSize * (condition.CurrentPage - 1) + " id FROM countries ORDER BY id DESC) AS C)";
}
sql += " ORDER BY id DESC; SELECT COUNT(*) FROM countries ";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, null))
{
while (dr.Read())
{
Country country = new Country();
country.Id = dr.GetInt32(0);
country.EnglishName = dr.GetString(1);
country.ChineseName = dr.GetString(2);
country.Code = dr.GetString(3);
country.Continent = dr.GetByte(4);
country.IsFront = dr.GetBoolean(5);
result.Results.Add(country);
}
dr.NextResult();
while (dr.Read())
{
result.TotalCount = dr.GetInt32(0);
}
}
return result;
}
public PaginationQueryResult<Insurance> GetInsurance(PaginationQueryCondition condition)
{
PaginationQueryResult<Insurance> result = new PaginationQueryResult<Insurance>();
string sql = "SELECT TOP " + condition.PageSize +" I.id, I.create_time, O.encode, OD.bar_code, OD.carrier_encode, O.client_id, I.insure_worth FROM insurance AS I JOIN orders AS O ON I.order_id = O.id JOIN order_details AS OD ON OD.id = I.order_detail_id WHERE I.is_delete = 0";
if (condition.CurrentPage > 1)
{
sql += " AND I.id < (SELECT MIN(id) FROM (SELECT TOP "+condition.PageSize*(condition.CurrentPage - 1)+" I.id FROM insurance AS I WHERE I.is_delete = 0 ORDER BY I.id DESC) AS E ) ";
}
sql += " ORDER BY I.id DESC; SELECT COUNT(*) FROM insurance AS I JOIN orders AS O ON I.order_id = O.id JOIN order_details AS OD ON OD.id = I.order_detail_id WHERE I.is_delete = 0 ";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, null))
{
while (dr.Read())
{
Insurance insurance = new Insurance();
insurance.Id = dr.GetInt32(0);
insurance.CreateTime = dr.GetDateTime(1);
insurance.OrderEncode = dr.GetString(2);
insurance.OrderDetailBarCode = dr.GetString(3);
insurance.CarrierName = new CarrierDAL().GetCarrierByEncode(dr.GetString(4)).Name;
insurance.ClientName=new ClientDAL().GetClientById(dr.GetInt32(5)).RealName;
insurance.InsureWorth = dr.GetDecimal(6);
result.Results.Add(insurance);
}
}
return result;
}
public PaginationQueryResult<Complaint> GetComplaint(PaginationQueryCondition condition)
{
PaginationQueryResult<Complaint> result = new PaginationQueryResult<Complaint>();
string sql = "SELECT TOP " + condition.PageSize + " id, client_id, client_name, content, create_time, company_id FROM complaints ";
if (condition.CurrentPage > 1)
{
sql += " WHERE id<(SELECT MIN(id) FROM (SELECT TOP " + condition.PageSize*(condition.CurrentPage-1) + " id FROM complaints ORDER BY id DESC) AS D) ";
}
sql += " ORDER BY id DESC; SELECT COUNT(*) FROM complaints ";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, null))
{
while (dr.Read())
{
Complaint comp = new Complaint();
comp.Id = dr.GetInt32(0);
comp.ClientId = dr.GetInt32(1);
comp.ClientName = dr.GetString(2);
comp.Content = dr.GetString(3);
comp.CreateTime = dr.GetDateTime(4);
comp.CompanyId = dr.GetInt32(5);
result.Results.Add(comp);
}
dr.NextResult();
while (dr.Read())
{
result.TotalCount = dr.GetInt32(0);
}
}
return result;
}
public PaginationQueryResult<FetchArrange> GetFetchArrangeByCompanyIdAndDate(PaginationQueryCondition condition, int companyId, DateTime startDate, DateTime endDate)
{
DateTime minTime = new DateTime(1999, 1, 1);
string sqlTime = "";
if (startDate > minTime && endDate > minTime)
{
sqlTime = " AND create_time BETWEEN @start_date AND @end_date";
}
else if (startDate > minTime && endDate <= minTime)
{
sqlTime = " AND create_time >= @start_date ";
}
else
{
sqlTime = " AND create_time <= @end_date";
}
PaginationQueryResult<FetchArrange> result = new PaginationQueryResult<FetchArrange>();
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputIntParameter("@company_id", companyId),
SqlUtilities.GenerateInputDateTimeParameter("@start_date", startDate),
SqlUtilities.GenerateInputDateTimeParameter("@end_date", endDate)
};
string sql = "SELECT TOP " + condition.PageSize + " id, client_id, type, address, phone, fetch_time, create_time, company_id, remark, user_id FROM fetch_arranges WHERE company_id = @company_id AND is_delete = 0"+sqlTime;
if (condition.CurrentPage > 1)
{
sql += " AND id< (SELECT MIN(id) FROM(SELECT TOP " + condition.PageSize * (condition.CurrentPage - 1) + " id FROM fetch_arranges WHERE company_id = @company_id AND is_delete = 0 "+sqlTime+" ORDER BY id DESC) AS F)";
}
sql += " ORDER BY id DESC; SELECT COUNT(*) FROM fetch_arranges WHERE company_id = @company_id AND is_delete = 0" + sqlTime;
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
FetchArrange fa = new FetchArrange();
fa.Id = dr.GetInt32(0);
fa.ClientId = dr.GetInt32(1);
fa.ClientName = new ClientDAL().GetClientById(fa.ClientId).RealName;
fa.Type = EnumConvertor.ConvertToOrderType(dr.GetByte(2));
fa.Address = dr.GetString(3);
fa.Phone = dr.GetString(4);
fa.FetchTime = dr.GetDateTime(5);
fa.CreateTime = dr.GetDateTime(6);
fa.CompanyId = dr.GetInt32(7);
fa.Remark = dr.GetString(8);
if (!dr.IsDBNull(9))
{
fa.UserId = dr.GetInt32(9);
}
result.Results.Add(fa);
}
dr.NextResult();
while (dr.Read())
{
result.TotalCount = dr.GetInt32(0);
}
}
return result;
}
public PaginationQueryResult<Order> GetAuditOrderByConsignType(PaginationQueryCondition condition, int consignType)
{
PaginationQueryResult<Order> result = new PaginationQueryResult<Order>();
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputParameter("@status", SqlDbType.TinyInt, (byte)OrderStatus.WAIT_AUDIT)
};
string sql = "";
switch (consignType)
{
case 1:
sql = "SELECT TOP " + condition.PageSize + " id, client_id, encode, status, costs, create_time, remark FROM orders WHERE is_delete = 0 AND status = @status";
if (condition.CurrentPage > 1)
{
sql += " AND id< (SELECT MIN(id) FROM (SELECT TOP " + condition.PageSize * (condition.CurrentPage - 1) + " id FROM orders WHERE is_delete = 0 AND status = @status ORDER BY id DESC) AS O) ";
}
sql += " ORDER BY id DESC; SELECT COUNT(*) FROM orders WHERE status = @status";
break;
case 2:
sql = "SELECT TOP " + condition.PageSize + " OS.id, OS.client_id, OS.encode, OS.status, OS.costs, OS.create_time, OS.remark, CS.balance FROM orders AS OS INNER JOIN clients AS CS ON OS.client_id = CS.id WHERE OS.is_delete = 0 AND OS.status = @status AND (CS.balance + CS.credit) >= OS.costs";
if (condition.CurrentPage > 1)
{
sql += " AND OS.id< (SELECT MIN(id) FROM (SELECT TOP " + condition.PageSize * (condition.CurrentPage - 1) + " OS.id FROM orders WHERE OS.is_delete = 0 AND OS.status = @status AND (CS.balance + CS.credit) >= OS.costs ORDER BY OS.id DESC) AS O) ";
}
sql += " ORDER BY OS.id DESC; SELECT COUNT(*) FROM orders AS OS INNER JOIN clients AS CS ON OS.client_id = CS.id WHERE OS.is_delete = 0 AND OS.status = @status AND (CS.balance + CS.credit) >= OS.costs";
break;
case 3:
sql = "SELECT TOP " + condition.PageSize + " OS.id, OS.client_id, OS.encode, OS.status, OS.costs, OS.create_time, OS.remark, CS.balance FROM orders AS OS INNER JOIN clients AS CS ON OS.client_id = CS.id WHERE OS.is_delete = 0 AND OS.status = @status AND (CS.balance + CS.credit) < OS.costs";
if (condition.CurrentPage > 1)
{
sql += " AND OS.id< (SELECT MIN(id) FROM (SELECT TOP " + condition.PageSize * (condition.CurrentPage - 1) + " OS.id FROM orders WHERE OS.is_delete = 0 AND OS.status = @status AND (CS.balance + CS.credit) < OS.costs ORDER BY OS.id DESC) AS O) ";
}
sql += " ORDER BY OS.id DESC; SELECT COUNT(*) FROM orders AS OS INNER JOIN clients AS CS ON OS.client_id = CS.id WHERE OS.is_delete = 0 AND OS.status = @status AND (CS.balance + CS.credit) < OS.costs";
break;
}
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
Order order = new Order();
order.Id = dr.GetInt32(0);
Client client = new ClientDAL().GetClientById(dr.GetInt32(1));
order.Client = client;
order.Encode = dr.GetString(2);
order.Status = EnumConvertor.ConvertToOrderStatus(dr.GetByte(3));
order.Costs = dr.GetDecimal(4);
order.CreateTime = dr.GetDateTime(5);
order.Remark = dr.GetString(6);
result.Results.Add(order);
}
dr.NextResult();
while (dr.Read())
{
result.TotalCount = dr.GetInt32(0);
}
}
return result;
}
public PaginationQueryResult<DailyCost> GetDailyCostByCompanyId(PaginationQueryCondition condition, int compId)
{
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputIntParameter("@company_id", compId)
};
PaginationQueryResult<DailyCost> result = new PaginationQueryResult<DailyCost>();
string sql = "SELECT TOP " + condition.PageSize + " id, user_id, company_id, audit_user_id, order_user_id, order_time, create_time, audit_time, encode, money, cost_type_id, remark FROM daily_costs WHERE is_delete = 0 AND company_id = @company_id";
if (condition.CurrentPage > 1)
{
sql += " AND id< (SELECT MIN(id) FROM (SELECT TOP " + condition.PageSize * (condition.CurrentPage - 1) + " id FROM daily_costs WHERE is_delete = 0 AND company_id = @company_id ORDER BY id DESC) AS R )";
}
sql += " ORDER BY id DESC; SELECT COUNT(*) FROM daily_costs WHERE is_delete = 0 AND company_id = @company_id ";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
DailyCost dc = new DailyCost();
dc.Id = dr.GetInt32(0);
dc.UserId = dr.GetInt32(1);
User user = UserOperation.GetUserById(dc.UserId);
dc.Username = user.RealName;
dc.DepartmentName = DepartmentOperation.GetDepartmentById(user.DepartmentId).Name;
dc.CompanyId = dr.GetInt32(2);
Company company = CompanyOperation.GetCompanyById(dc.CompanyId);
dc.CompanyName = company.Name;
if (!dr.IsDBNull(3))
{
dc.AuditUserId = dr.GetInt32(3);
}
dc.OrderUserId = dr.GetInt32(4);
user = UserOperation.GetUserById(dc.OrderUserId);
dc.OrderUserName = user.RealName;
dc.OrderTime = dr.GetDateTime(5);
dc.CreateTime = dr.GetDateTime(6);
if (!dr.IsDBNull(7))
{
dc.AuditTime = dr.GetDateTime(7);
}
dc.Encode = dr.GetString(8);
dc.Money = dr.GetDecimal(9);
dc.CostTypeId = dr.GetInt32(10);
CostType ct = CostTypeOperation.GetCostTypeById(dc.CostTypeId);
dc.CostType = ct.Name;
dc.Remark = dr.GetString(11);
result.Results.Add(dc);
}
dr.NextResult();
while (dr.Read())
{
result.TotalCount = dr.GetInt32(0);
}
}
return result;
}
public PaginationQueryResult<Recharge> GetRechargeByClientId(PaginationQueryCondition condition, int clientId)
{
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputIntParameter("@client_id", clientId)
};
PaginationQueryResult<Recharge> result = new PaginationQueryResult<Recharge>();
string sql = "SELECT TOP " + condition.PageSize + " id, client_id, company_id, encode, money, account, receive_time, create_time, user_id, payment_method_id, payment_type, currency_type, remark, paid, exchange_rate, invoice FROM recharges WHERE is_delete = 0 AND client_id = @client_id";
if (condition.CurrentPage > 1)
{
sql += " AND id< (SELECT MIN(id) FROM (SELECT TOP " + condition.PageSize * (condition.CurrentPage - 1) + " id FROM recharges WHERE is_delete = 0 AND client_id = @client_id ORDER BY id DESC) AS R )";
}
sql += " ORDER BY id DESC; SELECT COUNT(*) FROM recharges WHERE is_delete = 0 AND client_id = @client_id ";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
Recharge recharge = new Recharge();
recharge.Id = dr.GetInt32(0);
recharge.ClientId = dr.GetInt32(1);
Client client = new ClientDAL().GetClientById(recharge.ClientId);
recharge.ClientName = client.RealName;
recharge.CompanyId = dr.GetInt32(2);
recharge.Encode = dr.GetString(3);
recharge.Money = dr.GetDecimal(4);
recharge.Account = dr.GetString(5);
recharge.ReceiveTime = dr.GetDateTime(6);
recharge.CreateTime = dr.GetDateTime(7);
recharge.UserId = dr.GetInt32(8);
User user = new UserDAL().GetUserById(recharge.UserId);
recharge.UserName = user.RealName;
recharge.PaymentMethodId = dr.GetInt32(9);
PaymentMethod pm = new PaymentMethodDAL().GetPaymentMethodById(recharge.PaymentMethodId);
recharge.PaymentMethodName = pm.Name;
recharge.PaymentType = EnumConvertor.ConvertToPaymentType(dr.GetByte(10));
recharge.CurrencyType = EnumConvertor.ConvertToCurrencyType(dr.GetByte(11));
recharge.Remark = dr.GetString(12);
recharge.Paid = dr.GetDecimal(13);
recharge.ExchangeRate = dr.GetDecimal(14);
recharge.Invoice = dr.GetString(15);
result.Results.Add(recharge);
}
dr.NextResult();
while (dr.Read())
{
result.TotalCount = dr.GetInt32(0);
}
}
return result;
}
public PaginationQueryResult<Quote> GetQuoteByCompanyId(PaginationQueryCondition condition, int compId)
{
PaginationQueryResult<Quote> result = new PaginationQueryResult<Quote>();
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputIntParameter("@company_id", compId)
};
string sql = "SELECT TOP " + condition.PageSize + " id, encode, client_id, company_id, company_name, status, quote_time, user_id, create_time, remark, audit_user_id, audit_time FROM quote 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 quote WHERE company_id = @company_id AND is_delete = 0 ORDER BY ID DESC)AS Q) ";
}
sql += " ORDER BY ID DESC; SELECT COUNT(*) FROM quote WHERE company_id = @company_id AND is_delete = 0 ";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
Quote quote = new Quote();
quote.Id = dr.GetInt32(0);
quote.Encode = dr.GetString(1);
Client client = new ClientDAL().GetClientById(dr.GetInt32(2));
quote.Client = client;
quote.CompanyId = dr.GetInt32(3);
quote.CompanyName = dr.GetString(4);
quote.Status = dr.GetBoolean(5);
quote.QuoteTime = dr.GetDateTime(6);
User user = new UserDAL().GetUserById(dr.GetInt32(7));
quote.User = user;
quote.CreateTime = dr.GetDateTime(8);
quote.Remark = dr.GetString(9);
if (!dr.IsDBNull(10))
{
quote.AuditUserId = dr.GetInt32(10);
}
if (!dr.IsDBNull(11))
{
quote.AuditTime = dr.GetDateTime(11);
}
result.Results.Add(quote);
}
dr.NextResult();
while (dr.Read())
{
result.TotalCount = dr.GetInt32(0);
}
}
return result;
}
public PaginationQueryResult<WrongOrder> GetWrongOrderByClientId(PaginationQueryCondition condition, int clientId)
{
PaginationQueryResult<WrongOrder> result = new PaginationQueryResult<WrongOrder>();
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputIntParameter("@client_id", clientId)
};
string sql = "SELECT TOP " + condition.PageSize + " WO.id, WO.order_id, WO.company_id, WO.company_name, WO.encode, WO.status, WO.reason, WO.[type], WO.create_time, WO.create_user_id, WO.last_update_time FROM wrong_orders AS WO INNER JOIN orders AS O ON WO.order_id = O.id WHERE O.client_id = @client_id AND WO.is_delete = 0 AND O.is_delete = 0";
if (condition.CurrentPage > 1)
{
sql += " AND WO.id< (SELECT MIN(id) FROM (SELECT TOP " + condition.PageSize * (condition.CurrentPage - 1) + " WO.id FROM wrong_orders AS WO INNER JOIN orders AS O ON WO.order_id = O.id WHERE O.client_id = @client_id AND WO.is_delete = 0 AND O.is_delete = 0 ORDER BY WO.id DESC) AS W)";
}
sql += " ORDER BY id DESC; SELECT COUNT(*) FROM wrong_orders AS WO INNER JOIN orders AS O ON WO.order_id = O.id WHERE O.client_id = @client_id AND WO.is_delete = 0 AND O.is_delete = 0";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
WrongOrder wo = new WrongOrder();
wo.Id = dr.GetInt32(0);
if (!dr.IsDBNull(1))
{
Order order = new OrderDAL().GetOrderById(dr.GetInt32(1));
wo.Order = order;
}
wo.CompanyId = dr.GetInt32(2);
wo.CompanyName = dr.GetString(3);
wo.Encode = dr.GetString(4);
wo.Status = EnumConvertor.ConvertToWrongOrderStatus(dr.GetByte(5));
wo.Reason = dr.GetString(6);
wo.Type = dr.GetString(7);
wo.CreateTime = dr.GetDateTime(8);
wo.CreateUserId = dr.GetInt32(9);
wo.LastUpdateCreateTime = dr.GetDateTime(10);
result.Results.Add(wo);
}
dr.NextResult();
while (dr.Read())
{
result.TotalCount = dr.GetInt32(0);
}
}
return result;
}
public PaginationQueryResult<Recharge> GetRecharge(PaginationQueryCondition condition)
{
PaginationQueryResult<Recharge> result = new PaginationQueryResult<Recharge>();
string sql = "SELECT TOP " + condition.PageSize + " id, client_id, encode, money, account, receive_time, create_time, user_id, currency_type, remark, paid, exchange_rate, invoice FROM recharges WHERE is_delete = 0";
if (condition.CurrentPage > 1)
{
sql += " AND id< (SELECT MIN(id) FROM (SELECT TOP " + condition.PageSize * (condition.CurrentPage - 1) + " id FROM recharges WHERE is_delete = 0 ORDER BY id DESC) AS R )";
}
sql += " ORDER BY id DESC; SELECT COUNT(*) FROM recharges WHERE is_delete = 0 ";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, null))
{
while (dr.Read())
{
Recharge recharge = new Recharge();
recharge.Id = dr.GetInt32(0);
recharge.ClientId = dr.GetInt32(1);
Client client = new ClientDAL().GetClientById(recharge.ClientId);
recharge.ClientName = client.RealName;
recharge.Encode = dr.GetString(2);
recharge.Money = dr.GetDecimal(3);
recharge.Account = dr.GetString(4);
recharge.ReceiveTime = dr.GetDateTime(5);
recharge.CreateTime = dr.GetDateTime(6);
recharge.UserId = dr.GetInt32(7);
User user = new UserDAL().GetUserById(recharge.UserId);
recharge.UserName = user.RealName;
recharge.CurrencyType = EnumConvertor.ConvertToCurrencyType(dr.GetByte(8));
recharge.Remark = dr.GetString(9);
recharge.Paid = dr.GetDecimal(10);
recharge.ExchangeRate = dr.GetDecimal(11);
recharge.Invoice = dr.GetString(12);
result.Results.Add(recharge);
}
dr.NextResult();
while (dr.Read())
{
result.TotalCount = dr.GetInt32(0);
}
}
return result;
}
public PaginationQueryResult<AlreadyPaid> GetAlreadyPaidByCompanyId(PaginationQueryCondition condition, int compId)
{
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputIntParameter("@company_id", compId)
};
PaginationQueryResult<AlreadyPaid> result = new PaginationQueryResult<AlreadyPaid>();
string sql = "SELECT TOP " + condition.PageSize + " id, payment_method_id, invoice, carrier_id, user_id, company_id, create_time, paid_time, encode, money, remark, start_time, end_time FROM already_paid WHERE is_delete = 0 AND company_id = @company_id";
if (condition.CurrentPage > 1)
{
sql += " AND id< (SELECT MIN(id) FROM (SELECT TOP " + condition.PageSize * (condition.CurrentPage - 1) + " id FROM already_paid WHERE is_delete = 0 AND company_id = @company_id ORDER BY id DESC) AS R )";
}
sql += " ORDER BY id DESC; SELECT COUNT(*) FROM already_paid WHERE is_delete = 0 AND company_id = @company_id ";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
AlreadyPaid ap = new AlreadyPaid();
ap.Id = dr.GetInt32(0);
ap.PaymentMethod=PaymentMethodOperation.GetPaymentMethodById(dr.GetInt32(1));
ap.Invoice = dr.GetString(2);
ap.Carrier = CarrierOperation.GetCarrierById(dr.GetInt32(3));
ap.User = UserOperation.GetUserById(dr.GetInt32(4));
ap.CompanyId = dr.GetInt32(5);
ap.CreateTime = dr.GetDateTime(6);
ap.PaidTime = dr.GetDateTime(7);
ap.Encode = dr.GetString(8);
ap.Money = dr.GetDecimal(9);
ap.Remark = dr.GetString(10);
ap.StartTime = dr.GetDateTime(11);
ap.EndTime = dr.GetDateTime(12);
result.Results.Add(ap);
}
dr.NextResult();
while (dr.Read())
{
result.TotalCount = dr.GetInt32(0);
}
}
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 PaginationQueryResult<Company> GetCompany(PaginationQueryCondition condition)
{
PaginationQueryResult<Company> result=new PaginationQueryResult<Company>();
string sql = "SELECT TOP " + condition.PageSize + " id, name, area_code, address, contact_person, phone, email, smtp, commission, email_password, qq, msn FROM companies WHERE id_delete = 0";
if (condition.CurrentPage > 1)
{
sql += " AND id<(SELECT MIN(id) FROM (SELECT TOP " + condition.PageSize * (condition.CurrentPage - 1) + " id FROM companies WHERE is_delete = 0 ORDER BY id DESC) AS D)";
}
sql += " ORDER BY id DESC; SELECT COUNT(*) FROM companies WHERE id_delete = 0";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, null))
{
while (dr.Read())
{
Company company = new Company();
company.Id = dr.GetInt32(0);
company.Name = dr.GetString(1);
company.AreaCode = EnumConvertor.ConvertToAreaCode(dr.GetByte(2));
company.Address = dr.GetString(3);
company.ContactPerson = dr.GetString(4);
company.Phone = dr.GetString(5);
company.Email = dr.GetString(6);
company.Smtp = dr.GetString(7);
company.Commission = dr.GetDecimal(8);
company.EmailPassword = dr.GetString(9);
company.QQ = dr.GetString(10);
company.MSN = dr.GetString(11);
result.Results.Add(company);
}
dr.NextResult();
while (dr.Read())
{
result.TotalCount = dr.GetInt32(0);
}
}
return result;
}
public PaginationQueryResult<Client> GetClient(PaginationQueryCondition condition)
{
PaginationQueryResult<Client> result = new PaginationQueryResult<Client>();
string sql = "SELECT TOP " + condition.PageSize + " id, username, password, real_name, id_card, phone, mobile, email, address, credit, is_message, create_date, balance FROM clients WHERE is_delete = 0 ";
if (condition.CurrentPage > 1)
{
sql += " AND id<(SELECT MIN(id) FROM(SELECT TOP " + condition.PageSize * (condition.CurrentPage - 1) + " id FROM clients WHERE is_delete = 0 ORDER BY id DESC) AS D)";
}
sql += " ORDER BY id DESC; SELECT COUNT(*) FROM clients WHERE is_delete = 0 ";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, null))
{
while (dr.Read())
{
Client client = new Client();
client.Id = dr.GetInt32(0);
client.Username = dr.GetString(1);
client.Password = dr.GetString(2);
client.RealName = dr.GetString(3);
client.IdCard = dr.GetString(4);
client.Phone = dr.GetString(5);
client.Mobile = dr.GetString(6);
client.Email = dr.GetString(7);
client.Address = dr.GetString(8);
client.Credit = dr.GetDecimal(9);
client.IsMessage = dr.GetBoolean(10);
client.CreateDate = dr.GetDateTime(11);
client.Balance = dr.GetDecimal(12);
result.Results.Add(client);
}
dr.NextResult();
while (dr.Read())
{
result.TotalCount = dr.GetInt32(0);
}
}
return result;
}
public PaginationQueryResult<Complaint> GetComplaintByClientId(PaginationQueryCondition condition, int clientId)
{
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputIntParameter("@client_id", clientId)
};
PaginationQueryResult<Complaint> result = new PaginationQueryResult<Complaint>();
string sql = "SELECT TOP " + condition.PageSize + " id, client_id, client_name, content, create_time, company_id, is_reply, title FROM complaints WHERE client_id = @client_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 complaints WHERE client_id = @client_id AND is_delete = 0 ORDER BY id DESC) AS D) ";
}
sql += " ORDER BY id DESC; SELECT COUNT(*) FROM complaints WHERE client_id = @client_id AND is_delete = 0 ";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
Complaint comp = new Complaint();
comp.Id = dr.GetInt32(0);
comp.ClientId = dr.GetInt32(1);
comp.ClientName = dr.GetString(2);
comp.Content = dr.GetString(3);
comp.CreateTime = dr.GetDateTime(4);
comp.CompanyId = dr.GetInt32(5);
comp.IsReply = dr.GetBoolean(6);
comp.Title = dr.GetString(7);
result.Results.Add(comp);
}
dr.NextResult();
while (dr.Read())
{
result.TotalCount = dr.GetInt32(0);
}
}
return result;
}
public PaginationQueryResult<ShouldPay> GetShouldPayByCompanyId(PaginationQueryCondition condition, int compId)
{
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputIntParameter("@company_id", compId)
};
PaginationQueryResult<ShouldPay> result = new PaginationQueryResult<ShouldPay>();
string sql = "SELECT TOP " + condition.PageSize + " id, order_encode, order_detail_id, carrier_id, type, user_id, company_id, create_time, encode FROM should_pay WHERE is_delete = 0 AND is_paid = 0 AND company_id = @company_id";
if (condition.CurrentPage > 1)
{
sql += " AND id< (SELECT MIN(id) FROM (SELECT TOP " + condition.PageSize * (condition.CurrentPage - 1) + " id FROM should_pay ORDER BY id DESC) AS R )";
}
sql += " ORDER BY id DESC; SELECT COUNT(*) FROM should_pay WHERE is_delete = 0 AND company_id = @company_id ";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
ShouldPay sp = new ShouldPay();
sp.Id = dr.GetInt32(0);
sp.OrderEncode = dr.GetString(1);
sp.OrderDetail = new OrderDetailDAL().GetOrderDetailById(dr.GetInt32(2));
sp.Carrier = new CarrierDAL().GetCarrierById(dr.GetInt32(3));
sp.Type = dr.GetString(4);
sp.UserId = dr.GetInt32(5);
sp.CompanyId = dr.GetInt32(6);
sp.CreateTime = dr.GetDateTime(7);
sp.Encode = dr.GetString(8);
result.Results.Add(sp);
}
dr.NextResult();
while (dr.Read())
{
result.TotalCount = dr.GetInt32(0);
}
}
return result;
}
public PaginationQueryResult<ClientOrder> GetClientOrderByParameters(PaginationQueryCondition condition, int clientId, DateTime startDate, DateTime endDate)
{
PaginationQueryResult<ClientOrder> result = new PaginationQueryResult<ClientOrder>();
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputDateTimeParameter("@start_date", startDate),
SqlUtilities.GenerateInputDateTimeParameter("@end_date", endDate),
SqlUtilities.GenerateInputIntParameter("@client_id", clientId)
};
string sqlParam = "";
DateTime minTime = new DateTime(1999, 1, 1);
if (startDate > minTime && endDate > minTime)
{
sqlParam += " AND create_time BETWEEN @start_date AND @end_date";
}
else if (startDate > minTime && endDate <= minTime)
{
sqlParam += " AND create_time >= @start_date ";
}
else if (startDate <= minTime && endDate > minTime)
{
sqlParam += " AND create_time <= @end_date";
}
if (clientId >= 0)
{
sqlParam += " AND client_id = @client_id";
}
string sql = "SELECT TOP " + condition.PageSize + " id, client_id, client_address_id, real_name, phone, email, postcode, address, remark, create_time, country, city, encode FROM client_orders WHERE 1=1 " + sqlParam;
if (condition.CurrentPage > 1)
{
sql += " AND id <(SELECT MIN(id) FROM (SELECT TOP " + condition.PageSize * (condition.CurrentPage - 1) + " id FROM client_orders WHERE 1=1" + sqlParam + " ORDER BY id DESC) AS O)";
}
sql += " ORDER BY id DESC; SELECT COUNT(1) FROM client_orders WHERE 1=1" + sqlParam;
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
ClientOrder co = new ClientOrder();
co.Id = dr.GetInt32(0);
co.ClientId = dr.GetInt32(1);
co.ClientAddressId = dr.GetInt32(2);
co.RealName = dr.GetString(3);
co.Phone = dr.GetString(4);
co.Email = dr.GetString(5);
co.Postcode = dr.GetString(6);
co.Address = dr.GetString(7);
co.Remark = dr.GetString(8);
co.CreateTime = dr.GetDateTime(9);
co.Country = dr.GetString(10);
co.City = dr.GetString(11);
co.Encode = dr.GetString(12);
result.Results.Add(co);
}
dr.NextResult();
while (dr.Read())
{
result.TotalCount = dr.GetInt32(0);
}
}
return result;
}
public PaginationQueryResult<Insurance> GetInsuranceByParameters(PaginationQueryCondition condition, DateTime startDate, DateTime endDate, decimal insureWorth, string searchKey)
{
PaginationQueryResult<Insurance> result = new PaginationQueryResult<Insurance>();
DateTime minTime = new DateTime(1999, 1, 1);
string sqlParam = "";
if (startDate > minTime && endDate > minTime)
{
sqlParam += " AND I.create_time BETWEEN @start_date AND @end_date";
}
else if (startDate > minTime && endDate <= minTime)
{
sqlParam += " AND I.create_time >= @start_date ";
}
else if(startDate<=minTime && endDate >minTime)
{
sqlParam += " AND I.create_time <= @end_date";
}
if (insureWorth > 0)
{
sqlParam += " AND I.insure_worth > @insure_worth";
}
if (!string.IsNullOrEmpty(searchKey))
{
sqlParam += " AND O.encode LIKE '%" + searchKey + "%' OR OD.bar_code LIKE '%" + searchKey + "%' OR I.carrier_name LIKE '%" + searchKey + "%' OR I.client_name LIKE '%" + searchKey + "%'";
}
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputDateTimeParameter("@start_date", startDate),
SqlUtilities.GenerateInputDateTimeParameter("@end_date", endDate),
SqlUtilities.GenerateInputParameter("@insure_worth", SqlDbType.Decimal, insureWorth)
};
string sql = "SELECT TOP " + condition.PageSize + " I.id, I.create_time, O.encode, OD.bar_code, OD.carrier_encode, O.client_id, I.insure_worth FROM insurance AS I JOIN orders AS O ON I.order_id = O.id JOIN order_details AS OD ON OD.id = I.order_detail_id WHERE I.is_delete = 0" + sqlParam;
if (condition.CurrentPage > 1)
{
sql += " AND I.id < (SELECT MIN(id) FROM (SELECT TOP " + condition.PageSize * (condition.CurrentPage - 1) + " I.id FROM insurance AS I WHERE I.is_delete = 0 " + sqlParam + " ORDER BY I.id DESC) AS E ) ";
}
sql += " ORDER BY I.id DESC; SELECT COUNT(*) FROM insurance AS I JOIN orders AS O ON I.order_id = O.id JOIN order_details AS OD ON OD.id = I.order_detail_id WHERE I.is_delete = 0" + sqlParam;
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
Insurance insurance = new Insurance();
insurance.Id = dr.GetInt32(0);
insurance.CreateTime = dr.GetDateTime(1);
insurance.OrderEncode = dr.GetString(2);
insurance.OrderDetailBarCode = dr.GetString(3);
insurance.CarrierName = new CarrierDAL().GetCarrierByEncode(dr.GetString(4)).Name;
insurance.ClientName = new ClientDAL().GetClientById(dr.GetInt32(5)).RealName;
insurance.InsureWorth = dr.GetDecimal(6);
result.Results.Add(insurance);
}
}
return result;
}
public static PaginationQueryResult<FetchArrange> GetFetchArrangeByCompanyIdAndDate(PaginationQueryCondition condition, int companyId, DateTime startDate, DateTime endDate)
{
return dal.GetFetchArrangeByCompanyIdAndDate(condition, companyId, startDate, endDate);
}
public static PaginationQueryResult<FetchArrange> GetFetchArrangeByCompanyId(PaginationQueryCondition condition, int companyId)
{
return dal.GetFetchArrangeByCompanyId(condition, companyId);
}
public static PaginationQueryResult<AlreadyPaid> GetAlreadyPaidByCompanyIdAndDate(PaginationQueryCondition condition, int compId, DateTime startDate, DateTime endDate)
{
return dal.GetAlreadyPaidByCompanyIdAndDate(condition, compId, startDate, endDate);
}
public static PaginationQueryResult<AlreadyPaid> GetAlreadyPaidByCompanyId(PaginationQueryCondition condition, int compId)
{
return dal.GetAlreadyPaidByCompanyId(condition, compId);
}
public static PaginationQueryResult<News> GetNewsByCategoryId(PaginationQueryCondition condition, int catId)
{
return dal.GetNewsByCategoryId(condition, catId);
}
public static PaginationQueryResult<News> GetNews(PaginationQueryCondition condition)
{
return dal.GetNews(condition);
}
public static PaginationQueryResult<Quote> GetQuoteByParameters(PaginationQueryCondition condition, int compId, DateTime startDate, DateTime endDate, string strStatus, string keyword)
{
return dal.GetQuoteByParameters(condition, compId, startDate, endDate, strStatus, keyword);
}
public static PaginationQueryResult<Quote> GetQuoteByCompanyId(PaginationQueryCondition condition, int compId)
{
return dal.GetQuoteByCompanyId(condition, compId);
}
