示例#1
0
文件: OrderDAL.cs 项目: feidu/XSEMS
        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;
        }
示例#2
0
文件: OrderDAL.cs 项目: feidu/XSEMS
        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;
        }
示例#3
0
文件: OrderDAL.cs 项目: feidu/XSEMS
        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;
        }
示例#4
0
文件: OrderDAL.cs 项目: feidu/XSEMS
        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;
        }
示例#5
0
文件: OrderDAL.cs 项目: feidu/XSEMS
        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;
        }