public PaginationQueryResult<SearchOrder> GetSearchOrderByParameters(PaginationQueryCondition condition, string carrierEncode, int clientId, string encode, string ydEncode, string barCode, DateTime startDate, DateTime endDate, byte status)
{
PaginationQueryResult<SearchOrder> result = new PaginationQueryResult<SearchOrder>();
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.GenerateInputNVarcharParameter("@encode", 50, encode),
SqlUtilities.GenerateInputNVarcharParameter("@yd_encode", 50, ydEncode),
SqlUtilities.GenerateInputNVarcharParameter("@bar_code", 50, barCode),
SqlUtilities.GenerateInputParameter("@status", SqlDbType.TinyInt, status)
};
string sqlParam = "";
if (!string.IsNullOrEmpty(carrierEncode))
{
sqlParam += " AND OD.carrier_encode = @carrier_encode";
}
if (clientId >= 0)
{
sqlParam += " AND O.client_id = @client_id";
}
if (!string.IsNullOrEmpty(encode))
{
sqlParam += " AND O.encode = @encode";
}
if(!string.IsNullOrEmpty(ydEncode))
{
sqlParam += " AND OD.encode = @yd_encode";
}
if (!string.IsNullOrEmpty(barCode))
{
sqlParam += " AND OD.bar_code = @bar_code";
}
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 (status != 0)
{
sqlParam += " AND O.status = @status";
}
string sql = "SELECT TOP " + condition.PageSize + " O.id, O.create_time, O.encode, O.client_id, OD.carrier_encode, OD.to_country, OD.to_username, OD.bar_code, OD.id, O.status 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 " + 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 " + sqlParam + " ORDER BY OD.id DESC) AS R) ";
}
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 " + sqlParam;
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
SearchOrder so = new SearchOrder();
so.Id = dr.GetInt32(0);
so.CreateTime = dr.GetDateTime(1);
so.Encode = dr.GetString(2);
Client client = new ClientDAL().GetClientById(dr.GetInt32(3));
so.Client = client;
if(!dr.IsDBNull(4))
{
so.CarrierEncode = dr.GetString(4);
}
if (!dr.IsDBNull(5))
{
so.ToCountry = dr.GetString(5);
}
if (!dr.IsDBNull(6))
{
so.ToUsername = dr.GetString(6);
}
if (!dr.IsDBNull(7))
{
so.BarCode = dr.GetString(7);
}
so.OrderDetailId = dr.GetInt32(8);
so.Status = EnumConvertor.ConvertToOrderStatus(dr.GetByte(9));
result.Results.Add(so);
}
dr.NextResult();
while (dr.Read())
{
result.TotalCount = dr.GetInt32(0);
}
}
return result;
}
public List<SearchOrder> GetReceiveOrderStatistic(DateTime startDate, DateTime endDate, int clientId, string carrierEncode)
{
List<SearchOrder> result = new List<SearchOrder>();
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";
}
string sql = "SELECT O.client_id, SUM(OD.weight) AS weight, SUM(count) AS conunt, 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.client_id";
using(SqlDataReader dr=SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while(dr.Read())
{
SearchOrder so=new SearchOrder();
Client client = new ClientDAL().GetClientById(dr.GetInt32(0));
so.Client = client;
so.TotalWeight=dr.GetDecimal(1);
so.TotalCount=dr.GetInt32(2);
so.TotalCost=dr.GetDecimal(3);
result.Add(so);
}
}
return result;
}