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<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<Quote> GetQuoteByParameters(PaginationQueryCondition condition, int compId, DateTime startDate, DateTime endDate, string strStatus, string keyword)
{
DateTime minTime = new DateTime(1999, 1, 1);
string sqlParam = "";
bool status = true;
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 (strStatus != "0")
{
status = bool.Parse(strStatus);
if (status)
{
sqlParam += " AND status = 1";
}
else
{
sqlParam += " AND status = 0";
}
}
if (!string.IsNullOrEmpty(keyword))
{
sqlParam += " AND client_id IN(SELECT id FROM clients WHERE real_name LIKE '%" + keyword + "%')";
}
PaginationQueryResult<Quote> result = new PaginationQueryResult<Quote>();
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputIntParameter("@company_id", compId),
SqlUtilities.GenerateInputDateTimeParameter("@start_date", startDate),
SqlUtilities.GenerateInputDateTimeParameter("@end_date", endDate)
};
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 " + sqlParam;
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 " + sqlParam + " ORDER BY ID DESC)AS Q) ";
}
sql += " ORDER BY ID DESC; SELECT COUNT(*) FROM quote WHERE company_id = @company_id AND is_delete = 0 " + sqlParam;
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 Quote GetQuoteById(int id)
{
Quote quote = null;
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputIntParameter("@id", id)
};
string sql = "SELECT id, encode, client_id, company_id, company_name, status, quote_time, user_id, create_time, remark, audit_user_id, audit_time FROM quote WHERE id = @id";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
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);
}
}
}
return quote;
}
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 List<Recharge> GetRechargeStatistic(DateTime startDate, DateTime endDate, int clientId, string pmIds)
{
List<Recharge> result = new List<Recharge>();
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";
}
if (!string.IsNullOrEmpty(pmIds))
{
sqlParam += " AND payment_method_id IN(" + pmIds + ")";
}
string sql = "SELECT 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 "+sqlParam;
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.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.Add(recharge);
}
}
return result;
}
public Recharge GetRechargeById(int id)
{
Recharge recharge = null;
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputIntParameter("@id", id)
};
string sql = "SELECT id, client_id, encode, money, account, receive_time, create_time, user_id, currency_type, remark, paid, exchange_rate, invoice FROM recharges WHERE id = @id";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
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);
}
}
return recharge;
}
public PaginationQueryResult<Recharge> GetRechargeByDate(PaginationQueryCondition condition, 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";
}
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputDateTimeParameter("@start_date", startDate),
SqlUtilities.GenerateInputDateTimeParameter("@end_date", endDate)
};
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" + sqlTime;
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 " + sqlTime + " ORDER BY id DESC) AS R )";
}
sql += " ORDER BY id DESC; SELECT COUNT(*) FROM recharges WHERE is_delete = 0 " + sqlTime;
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.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 List<UserSales> GetUserSalesStatistic(DateTime startDate, DateTime endDate, int companyId, int
userId)
{
List<UserSales> result = new List<UserSales>();
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputDateTimeParameter("@start_date", startDate),
SqlUtilities.GenerateInputDateTimeParameter("@end_date", endDate),
SqlUtilities.GenerateInputIntParameter("@company_id", companyId)
};
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 (companyId > 0)
{
sqlParam += " AND company_id = @company_id";
}
if (userId > 0)
{
sqlParam += " AND user_id = @user_id";
}
string sql = "SELECT user_id, SUM(costs) AS money, SUM(costs-self_costs) AS profit FROM orders WHERE is_delete = 0 AND status IN(4,5) " + sqlParam + " GROUP BY user_id ORDER BY money DESC";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
UserSales us = new UserSales();
User user = new UserDAL().GetUserById(dr.GetInt32(0));
us.User = user;
us.Money = dr.GetDecimal(1);
us.Profit = dr.GetDecimal(2);
result.Add(us);
}
}
return result;
}
public List<UserSales> GetUserAssessStatistic(DateTime startDate, DateTime endDate, int companyId, int clientId, string carrierEncode, int userId)
{
List<UserSales> result = new List<UserSales>();
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputDateTimeParameter("@start_date", startDate),
SqlUtilities.GenerateInputDateTimeParameter("@end_date", endDate),
SqlUtilities.GenerateInputIntParameter("@client_id", clientId),
SqlUtilities.GenerateInputIntParameter("@company_id", companyId),
SqlUtilities.GenerateInputNVarcharParameter("@carrier_encode", 50, carrierEncode),
SqlUtilities.GenerateInputIntParameter("@user_id", userId)
};
string sqlParam = "";
DateTime minTime = new DateTime(1999, 1, 1);
if (startDate > minTime && endDate > minTime)
{
sqlParam += " AND O.create_time BETWEEN @start_date AND @end_date";
}
else if (startDate > minTime && endDate <= minTime)
{
sqlParam += " AND O.create_time >= @start_date ";
}
else if (startDate <= minTime && endDate > minTime)
{
sqlParam += " AND O.create_time <= @end_date";
}
if (companyId > 0)
{
sqlParam += " AND O.company_id = @company_id";
}
if (clientId >= 0)
{
sqlParam += " AND O.client_id = @client_id";
}
if (userId > 0)
{
sqlParam += " AND O.user_id = @user_id";
}
if (!string.IsNullOrEmpty(carrierEncode))
{
sqlParam += " AND OD.carrier_encode = @carrier_encode";
}
string sql = "SELECT O.user_id, SUM(total_costs) AS costs FROM orders AS O INNER JOIN order_details AS OD ON O.id = OD.order_id WHERE O.is_delete = 0 AND OD.is_delete = 0 AND O.status IN(4,5) " + sqlParam + " GROUP BY O.user_id";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
UserSales us = new UserSales();
User user = new UserDAL().GetUserById(dr.GetInt32(0));
us.User = user;
us.Money = dr.GetDecimal(1);
result.Add(us);
}
}
return result;
}
public PaginationQueryResult<Order> GetOrderByClientIdStatusAndDate(PaginationQueryCondition condition, int clientId, OrderStatus status,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<Order> result = new PaginationQueryResult<Order>();
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputDateTimeParameter("@start_date", startDate),
SqlUtilities.GenerateInputDateTimeParameter("@end_date", endDate),
SqlUtilities.GenerateInputIntParameter("@client_id", clientId),
SqlUtilities.GenerateInputParameter("@status", SqlDbType.TinyInt, (byte)status)
};
string sql = "SELECT TOP " + condition.PageSize + " id, client_id, company_id, company_name, user_id, encode, status, costs, receive_date, type, create_time, calculate_type, receive_type, create_user_id, receive_user_id, remark, is_mail_send, audit_user_id, audit_time, check_user_id, check_time FROM orders WHERE is_delete = 0 AND client_id = @client_id AND status = @status "+sqlTime;
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 client_id = @client_id AND status = @status "+sqlTime+" ORDER BY id DESC) AS O) ";
}
sql += " ORDER BY id DESC; SELECT COUNT(*) FROM orders WHERE is_delete = 0 AND client_id = @client_id AND status = @status "+sqlTime;
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.CompanyId = dr.GetInt32(2);
order.CompanyName = dr.GetString(3);
if (!dr.IsDBNull(4))
{
order.UserId = dr.GetInt32(4);
}
order.Encode = dr.GetString(5);
order.Status = EnumConvertor.ConvertToOrderStatus(dr.GetByte(6));
order.Costs = dr.GetDecimal(7);
if (!dr.IsDBNull(8))
{
order.ReceiveDate = dr.GetDateTime(8);
}
order.Type = EnumConvertor.ConvertToOrderType(dr.GetByte(9));
order.CreateTime = dr.GetDateTime(10);
order.CalculateType = dr.GetInt32(11);
order.ReceiveType = dr.GetString(12);
if (!dr.IsDBNull(13))
{
User user = new UserDAL().GetUserById(dr.GetInt32(13));
order.CreateUser = user;
}
if (!dr.IsDBNull(14))
{
order.ReceiveUserId = dr.GetInt32(14);
}
order.Remark = dr.GetString(15);
order.IsMailSend = dr.GetBoolean(16);
if (!dr.IsDBNull(17))
{
order.AuditUserId = dr.GetInt32(17);
}
if (!dr.IsDBNull(18))
{
order.AuditTime = dr.GetDateTime(18);
}
if (!dr.IsDBNull(19))
{
order.CheckUserId = dr.GetInt32(19);
}
if (!dr.IsDBNull(20))
{
order.CheckTime = dr.GetDateTime(20);
}
result.Results.Add(order);
}
dr.NextResult();
while (dr.Read())
{
result.TotalCount = dr.GetInt32(0);
}
}
return result;
}
public Order GetOrderByClientIdEncodeAndStatus(int clientId, string encode, OrderStatus status)
{
Order order = null;
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputIntParameter("@client_id", clientId),
SqlUtilities.GenerateInputParameter("@status", SqlDbType.TinyInt, (byte)status),
SqlUtilities.GenerateInputNVarcharParameter("@encode", 50, encode)
};
string sql = "SELECT id, client_id, company_id, company_name, user_id, encode, status, costs, receive_date, type, create_time, calculate_type, receive_type, create_user_id, receive_user_id, remark, is_mail_send, audit_user_id, audit_time, check_user_id, check_time, reason, to_username, to_phone, to_email, to_city, to_country, to_address, to_postcode, self_costs FROM orders WHERE is_delete = 0 AND client_id = @client_id AND encode = @encode AND status = @status";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
order = new Order();
order.Id = dr.GetInt32(0);
Client client = new ClientDAL().GetClientById(dr.GetInt32(1));
order.Client = client;
order.CompanyId = dr.GetInt32(2);
order.CompanyName = dr.GetString(3);
if (!dr.IsDBNull(4))
{
order.UserId = dr.GetInt32(4);
}
order.Encode = dr.GetString(5);
order.Status = EnumConvertor.ConvertToOrderStatus(dr.GetByte(6));
order.Costs = dr.GetDecimal(7);
if (!dr.IsDBNull(8))
{
order.ReceiveDate = dr.GetDateTime(8);
}
order.Type = EnumConvertor.ConvertToOrderType(dr.GetByte(9));
order.CreateTime = dr.GetDateTime(10);
order.CalculateType = dr.GetInt32(11);
order.ReceiveType = dr.GetString(12);
if (!dr.IsDBNull(13))
{
User user = new UserDAL().GetUserById(dr.GetInt32(13));
order.CreateUser = user;
}
if (!dr.IsDBNull(14))
{
order.ReceiveUserId = dr.GetInt32(14);
}
order.Remark = dr.GetString(15);
order.IsMailSend = dr.GetBoolean(16);
if (!dr.IsDBNull(17))
{
order.AuditUserId = dr.GetInt32(17);
}
if (!dr.IsDBNull(18))
{
order.AuditTime = dr.GetDateTime(18);
}
if (!dr.IsDBNull(19))
{
order.CheckUserId = dr.GetInt32(19);
}
if (!dr.IsDBNull(20))
{
order.CheckTime = dr.GetDateTime(20);
}
if (!dr.IsDBNull(21))
{
order.Reason = dr.GetString(21);
}
order.ToUsername = dr.GetString(22);
order.ToPhone = dr.GetString(23);
order.ToEmail = dr.GetString(24);
order.ToCity = dr.GetString(25);
order.ToCountry = dr.GetString(26);
order.ToAddress = dr.GetString(27);
order.ToPostcode = dr.GetString(28);
order.SelfCosts = dr.GetDecimal(29);
}
}
return order;
}
public PaginationQueryResult<Order> GetAuditOrderByCompanyIdAndConsignType(PaginationQueryCondition condition, int compId, int consignType)
{
PaginationQueryResult<Order> result = new PaginationQueryResult<Order>();
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputIntParameter("@company_id", compId),
SqlUtilities.GenerateInputParameter("@status", SqlDbType.TinyInt, (byte)OrderStatus.WAIT_AUDIT)
};
string sql = "";
switch (consignType)
{
case 1:
sql = "SELECT TOP " + condition.PageSize + " id, client_id, company_id, company_name, user_id, encode, status, costs, receive_date, type, create_time, calculate_type, receive_type, create_user_id, receive_user_id, remark FROM orders WHERE is_delete = 0 AND company_id = @company_id 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 company_id = @company_id AND status = @status ORDER BY id DESC) AS O) ";
}
sql += " ORDER BY id DESC; SELECT COUNT(*) FROM orders WHERE company_id = @company_id AND status = @status";
break;
case 2:
sql = "SELECT TOP " + condition.PageSize + " OS.id, OS.client_id, OS.company_id, OS.company_name, OS.user_id, OS.encode, OS.status, OS.costs, OS.receive_date, OS.type, OS.create_time, OS.calculate_type, OS.receive_type, OS.create_user_id, OS.receive_user_id, 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.company_id = @company_id 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.company_id = @company_id 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.company_id = @company_id 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.company_id, OS.company_name, OS.user_id, OS.encode, OS.status, OS.costs, OS.receive_date, OS.type, OS.create_time, OS.calculate_type, OS.receive_type, OS.create_user_id, OS.receive_user_id, 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.company_id = @company_id 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.company_id = @company_id 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.company_id = @company_id 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.CompanyId = dr.GetInt32(2);
order.CompanyName = dr.GetString(3);
if (!dr.IsDBNull(4))
{
order.UserId = dr.GetInt32(4);
}
order.Encode = dr.GetString(5);
order.Status = EnumConvertor.ConvertToOrderStatus(dr.GetByte(6));
order.Costs = dr.GetDecimal(7);
if (!dr.IsDBNull(8))
{
order.ReceiveDate = dr.GetDateTime(8);
}
order.Type = EnumConvertor.ConvertToOrderType(dr.GetByte(9));
order.CreateTime = dr.GetDateTime(10);
order.CalculateType = dr.GetInt32(11);
order.ReceiveType = dr.GetString(12);
if (!dr.IsDBNull(13))
{
User user = new UserDAL().GetUserById(dr.GetInt32(13));
order.CreateUser = user;
}
if (!dr.IsDBNull(14))
{
order.ReceiveUserId = dr.GetInt32(14);
}
order.Remark = dr.GetString(15);
result.Results.Add(order);
}
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<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;
}