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