public List<SearchOrderDetail> GetNotOnlineOrderDetail(DateTime startDate, int judgeDays, int clientId, string carrierEncode)
{
List<SearchOrderDetail> result = new List<SearchOrderDetail>();
DateTime judgeDate=startDate.AddDays(-judgeDays);
DateTime judgeTime=new DateTime(judgeDate.Year, judgeDate.Month, judgeDate.Day, 23, 59, 59);
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputDateTimeParameter("@judge_time", judgeTime),
SqlUtilities.GenerateInputIntParameter("@client_id", clientId),
SqlUtilities.GenerateInputNVarcharParameter("@carrier_encode", 50, carrierEncode)
};
string sqlParam = "";
sqlParam += " AND O.audit_time <= @judge_time";
if (clientId > 0)
{
sqlParam += " AND O.client_id = @client_id";
}
if (!string.IsNullOrEmpty(carrierEncode))
{
sqlParam += " AND OD.carrier_encode = @carrier_encode";
}
string sql = "SELECT O.encode, O.create_time, O.client_id, O.receive_date, OD.carrier_encode, OD.weight AS weight, OD.[count] AS conunt, OD.total_costs AS costs, OD.self_total_costs, OD.create_time, OD.bar_code, OD.to_country, OD.to_username, OD.remark 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 OD.bar_code LIKE 'RA%' AND OD.is_tracking = 0 " + sqlParam;
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
SearchOrderDetail sod = new SearchOrderDetail();
sod.OrderEncode = dr.GetString(0);
sod.CreateTime = dr.GetDateTime(1);
Client client = new ClientDAL().GetClientById(dr.GetInt32(2));
sod.Client = client;
sod.OrderReceiveDate = dr.GetDateTime(3);
sod.CarrierEncode = dr.GetString(4);
sod.Weight = dr.GetDecimal(5);
sod.Count = dr.GetInt32(6);
sod.TotalCosts = dr.GetDecimal(7);
sod.SelfTotalCosts = dr.GetDecimal(8);
sod.CreateTime = dr.GetDateTime(9);
sod.BarCode = dr.GetString(10);
sod.ToCountry = dr.GetString(11);
sod.ToUsername = dr.GetString(12);
sod.Remark = dr.GetString(13);
result.Add(sod);
}
}
return result;
}
public List<SearchOrderDetail> GetFetchCostsStatistic(DateTime startDate, DateTime endDate, int clientId, string carrierEncode, int userId)
{
List<SearchOrderDetail> result = new List<SearchOrderDetail>();
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputDateTimeParameter("@start_date", startDate),
SqlUtilities.GenerateInputDateTimeParameter("@end_date", endDate),
SqlUtilities.GenerateInputIntParameter("@client_id", clientId),
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 (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.client_id, SUM(OD.fetch_costs), SUM(OD.disposal_costs), SUM(OD.material_costs), SUM(OD.other_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) AND OD.fetch_costs > 0 OR OD.disposal_costs > 0 OR OD.material_costs > 0 OR OD.other_costs > 0 " + sqlParam + " GROUP BY O.client_id";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
SearchOrderDetail sod = new SearchOrderDetail();
Client client = new ClientDAL().GetClientById(dr.GetInt32(0));
sod.Client = client;
sod.TotalFetchCosts = dr.GetDecimal(1);
sod.TotalDisposalCosts = dr.GetDecimal(2);
sod.TotalMaterialCosts = dr.GetDecimal(3);
sod.TotalOtherCosts = dr.GetDecimal(4);
result.Add(sod);
}
}
return result;
}
public PaginationQueryResult<SearchOrderDetail> GetOrderCostsPostInfoDetailsByParameters(PaginationQueryCondition condition, int clientId, DateTime startDate, DateTime endDate, string barCode, string remark)
{
PaginationQueryResult<SearchOrderDetail> result = new PaginationQueryResult<SearchOrderDetail>();
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputDateTimeParameter("@start_date", startDate),
SqlUtilities.GenerateInputDateTimeParameter("@end_date", endDate),
SqlUtilities.GenerateInputIntParameter("@client_id", clientId),
SqlUtilities.GenerateInputNVarcharParameter("@bar_code", 50, barCode),
SqlUtilities.GenerateInputNVarcharParameter("@remark", 50, remark)
};
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 (clientId >= 0)
{
sqlParam += " AND O.client_id = @client_id";
}
if (!string.IsNullOrEmpty(barCode))
{
sqlParam += " AND OD.bar_code = @bar_code";
}
if (!string.IsNullOrEmpty(remark))
{
sqlParam += " AND OD.remark = @remark";
}
string sql = "SELECT TOP " + condition.PageSize + " O.encode, O.create_time, O.client_id, O.receive_date, OD.carrier_encode, OD.weight AS weight, OD.[count] AS conunt, OD.total_costs AS costs, OD.self_total_costs, OD.create_time, OD.bar_code, OD.to_country, OD.remark, OD.to_username, OD.last_disposal_time, OD.post_status, OD.is_tracking 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;
if (condition.CurrentPage > 1)
{
sql += " AND OD.id <(SELECT MIN(id) FROM (SELECT TOP " + condition.PageSize * (condition.CurrentPage - 1) + " OD.id 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 + " ORDER BY OD.id DESC) AS O)";
}
sql += " ORDER BY OD.id DESC; SELECT COUNT(*) 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;
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
SearchOrderDetail sod = new SearchOrderDetail();
sod.OrderEncode = dr.GetString(0);
sod.CreateTime = dr.GetDateTime(1);
Client client = new ClientDAL().GetClientById(dr.GetInt32(2));
sod.Client = client;
sod.OrderReceiveDate = dr.GetDateTime(3);
sod.CarrierEncode = dr.GetString(4);
sod.Weight = dr.GetDecimal(5);
sod.Count = dr.GetInt32(6);
sod.TotalCosts = dr.GetDecimal(7);
sod.SelfTotalCosts = dr.GetDecimal(8);
sod.CreateTime = dr.GetDateTime(9);
sod.BarCode = dr.GetString(10);
sod.ToCountry = dr.GetString(11);
sod.Remark = dr.GetString(12);
sod.ToUsername = dr.GetString(13);
if (!dr.IsDBNull(14))
{
sod.LastDisposalTime = dr.GetDateTime(14);
}
if (!dr.IsDBNull(15))
{
sod.PostStatus = dr.GetString(15);
}
sod.IsTracking = dr.GetBoolean(16);
result.Results.Add(sod);
}
dr.NextResult();
while (dr.Read())
{
result.TotalCount = dr.GetInt32(0);
}
}
return result;
}
public List<SearchOrderDetail> GetEaduOrderDetailStatistic(DateTime startDate, DateTime endDate, int clientId, string carrierEncode)
{
List<SearchOrderDetail> result = new List<SearchOrderDetail>();
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputDateTimeParameter("@start_date", startDate),
SqlUtilities.GenerateInputDateTimeParameter("@end_date", endDate),
SqlUtilities.GenerateInputIntParameter("@client_id", clientId),
SqlUtilities.GenerateInputNVarcharParameter("@carrier_encode", 50, carrierEncode)
};
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 (clientId >= 0)
{
sqlParam += " AND O.client_id = @client_id";
}
if (!string.IsNullOrEmpty(carrierEncode))
{
sqlParam += " AND OD.carrier_encode = @carrier_encode";
}
sqlParam += " ORDER BY OD.carrier_encode DESC,OD.post_status ASC";
string sql = "SELECT O.encode, O.create_time, O.client_id, O.receive_date, OD.carrier_encode, OD.weight AS weight, OD.[count] AS conunt, OD.total_costs AS costs, OD.self_total_costs, OD.create_time, OD.bar_code, OD.to_country, OD.to_username, OD.remark, OD.last_disposal_time, OD.post_status, OD.tracking_time 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) AND OD.carrier_encode IN('CNBJ','CNAM','ZJEMS','BRAM','SHCN') AND OD.bar_code NOT LIKE '%D%CN' AND LEN(OD.bar_code)=13" + sqlParam;
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
SearchOrderDetail sod = new SearchOrderDetail();
sod.OrderEncode = dr.GetString(0);
sod.CreateTime = dr.GetDateTime(1);
Client client = new ClientDAL().GetClientById(dr.GetInt32(2));
sod.Client = client;
sod.OrderReceiveDate = dr.GetDateTime(3);
sod.CarrierEncode = dr.GetString(4);
sod.Weight = dr.GetDecimal(5);
sod.Count = dr.GetInt32(6);
sod.TotalCosts = dr.GetDecimal(7);
sod.SelfTotalCosts = dr.GetDecimal(8);
sod.CreateTime = dr.GetDateTime(9);
sod.BarCode = dr.GetString(10);
sod.ToCountry = dr.GetString(11);
sod.ToUsername = dr.GetString(12);
sod.Remark = dr.GetString(13);
if (!dr.IsDBNull(14))
{
sod.LastDisposalTime = dr.GetDateTime(14);
}
else
{
sod.LastDisposalTime = minTime;
}
if (!dr.IsDBNull(15))
{
sod.PostStatus = dr.GetString(15);
}
else
{
sod.PostStatus = "";
}
if (!dr.IsDBNull(16))
{
sod.TrackingTime = dr.GetDateTime(16);
}
else
{
sod.TrackingTime = minTime;
}
result.Add(sod);
}
}
return result;
}
public List<SearchOrderDetail> GetOrderCostsDetailByParameters(int clientId, DateTime startDate, DateTime endDate, string barCode, string remark)
{
List<SearchOrderDetail> result = new List<SearchOrderDetail>();
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputDateTimeParameter("@start_date", startDate),
SqlUtilities.GenerateInputDateTimeParameter("@end_date", endDate),
SqlUtilities.GenerateInputIntParameter("@client_id", clientId),
SqlUtilities.GenerateInputNVarcharParameter("@bar_code", 50, barCode),
SqlUtilities.GenerateInputNVarcharParameter("@remark", 50, remark)
};
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 (clientId >= 0)
{
sqlParam += " AND O.client_id = @client_id";
}
if (!string.IsNullOrEmpty(barCode))
{
sqlParam += " AND OD.bar_code = @bar_code";
}
if (!string.IsNullOrEmpty(remark))
{
sqlParam += " AND OD.remark = @remark";
}
string sql = "SELECT O.encode, O.create_time, O.client_id, O.receive_date, OD.carrier_encode, OD.weight AS weight, OD.[count] AS conunt, OD.total_costs AS costs, OD.self_total_costs, OD.create_time, OD.bar_code, OD.to_country, OD.remark, OD.to_username 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 + " ORDER BY O.id DESC";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
SearchOrderDetail sod = new SearchOrderDetail();
sod.OrderEncode = dr.GetString(0);
sod.CreateTime = dr.GetDateTime(1);
Client client = new ClientDAL().GetClientById(dr.GetInt32(2));
sod.Client = client;
sod.OrderReceiveDate = dr.GetDateTime(3);
sod.CarrierEncode = dr.GetString(4);
sod.Weight = dr.GetDecimal(5);
sod.Count = dr.GetInt32(6);
sod.TotalCosts = dr.GetDecimal(7);
sod.SelfTotalCosts = dr.GetDecimal(8);
sod.CreateTime = dr.GetDateTime(9);
sod.BarCode = dr.GetString(10);
sod.ToCountry = dr.GetString(11);
sod.Remark = dr.GetString(12);
sod.ToUsername = dr.GetString(13);
result.Add(sod);
}
}
return result;
}
