Exemple #1
0
        //public void UpdateRecharge(Recharge recharge)
        //{
        //    SqlParameter[] param = new SqlParameter[] {
        //        SqlUtilities.GenerateInputIntParameter("@id", recharge.Id),
        //        SqlUtilities.GenerateInputNVarcharParameter("@english_name", 50, recharge.EnglishName),
        //        SqlUtilities.GenerateInputNVarcharParameter("@chinese_name", 50, recharge.ChineseName),
        //        SqlUtilities.GenerateInputNVarcharParameter("@code", 50, recharge.Code),
        //        SqlUtilities.GenerateInputParameter("@state",SqlDbType.TinyInt, recharge.State)
        //    };
        //    string sql = "UPDATE recharges SET english_name = @english_name, chinese_name = @chinese_name, code = @code, state = @state WHERE id =      @id";
        //    SqlHelper.ExecuteNonQuery(CommandType.Text, sql, param);
        //}
        //public void DeleteRechargeById(int id)
        //{
        //    SqlParameter[] param = new SqlParameter[] {
        //        SqlUtilities.GenerateInputIntParameter("@id", id)
        //    };
        //    string sql = "DELETE FROM recharges WHERE id = @id";
        //    SqlHelper.ExecuteNonQuery(CommandType.Text, sql, param);
        //}
        public void DeleteRechargeById(int id)
        {
            SqlParameter[] param = new SqlParameter[] {
                SqlUtilities.GenerateInputIntParameter("@id", id)
            };
            string sql = "UPDATE recharges SET is_delete = 1 WHERE id = @id";
            SqlHelper.ExecuteNonQuery(CommandType.Text, sql, param);

            Recharge recharge = new RechargeDAL().GetRechargeById(id);
            Client client = new ClientDAL().GetClientById(recharge.ClientId);
            decimal balance = client.Balance - recharge.Money;
            client.Balance = balance;
            new ClientDAL().UpdateClientBalance(client);
        }
Exemple #2
0
 public Order GetOrderById(int id)
 {
     Order order = null;
     SqlParameter[] param = new SqlParameter[] {
         SqlUtilities.GenerateInputIntParameter("@id", id)
     };
     string sql = "SELECT id, client_id, encode, status, costs, receive_date, create_time, 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 id = @id";
     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.Encode = dr.GetString(2);
             order.Status = EnumConvertor.ConvertToOrderStatus(dr.GetByte(3));
             order.Costs = dr.GetDecimal(4);
             if (!dr.IsDBNull(5))
             {
                 order.ReceiveDate = dr.GetDateTime(5);
             }
             order.CreateTime = dr.GetDateTime(6);
             order.Remark = dr.GetString(7);
             order.IsMailSend = dr.GetBoolean(8);
             if (!dr.IsDBNull(9))
             {
                 order.AuditUserId = dr.GetInt32(9);
             }
             if (!dr.IsDBNull(10))
             {
                 order.AuditTime = dr.GetDateTime(10);
             }
             if (!dr.IsDBNull(11))
             {
                 order.CheckUserId = dr.GetInt32(11);
             }
             if (!dr.IsDBNull(12))
             {
                 order.CheckTime = dr.GetDateTime(12);
             }
             if (!dr.IsDBNull(13))
             {
                 order.Reason = dr.GetString(13);
             }
             order.ToUsername = dr.GetString(14);
             order.ToPhone = dr.GetString(14);
             order.ToEmail = dr.GetString(16);
             order.ToCity = dr.GetString(17);
             order.ToCountry = dr.GetString(18);
             order.ToAddress = dr.GetString(19);
             order.ToPostcode = dr.GetString(20);
             order.SelfCosts = dr.GetDecimal(21);
         }
     }
     return order;
 }
Exemple #3
0
 public PaginationQueryResult<Order> GetAuditOrderByCompIdConsignTypeAndEncode(PaginationQueryCondition condition, int compId, int consignType, string encode)
 {
     PaginationQueryResult<Order> result = new PaginationQueryResult<Order>();
     SqlParameter[] param = new SqlParameter[] {
         SqlUtilities.GenerateInputIntParameter("@company_id", compId),
         SqlUtilities.GenerateInputNVarcharParameter("@encode", 50, encode),
         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 AND encode = @encode";
             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 AND encode = @encode ORDER BY id DESC) AS O) ";
             }
             sql += " ORDER BY id DESC; SELECT COUNT(*) FROM orders WHERE company_id = @company_id AND status = @status AND encode = @encode";
             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 OS.encode = @encode 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 OS.encode = @encode 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 OS.encode = @encode 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 OS.encode = @encode 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 OS.encode = @encode 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 OS.encode = @encode 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;
 }
Exemple #4
0
 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;
 }
Exemple #5
0
 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;
 }
Exemple #6
0
 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;
 }
Exemple #7
0
        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;
        }
 public PaginationQueryResult<ReceivedDeducted> GetReceivedDeductedByParameter(PaginationQueryCondition condition, int compId, DateTime startDate, DateTime endDate)
 {
     SqlParameter[] param = new SqlParameter[] {
         SqlUtilities.GenerateInputIntParameter("@company_id", compId),
         SqlUtilities.GenerateInputDateTimeParameter("@start_date", startDate),
         SqlUtilities.GenerateInputDateTimeParameter("@end_date", endDate)
     };
     DateTime minTime = new DateTime(1999, 1, 1);
     string sqlParam = " ";
     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";
     }
     PaginationQueryResult<ReceivedDeducted> result = new PaginationQueryResult<ReceivedDeducted>();
     string sql = "SELECT TOP " + condition.PageSize + " id, company_id, client_id, sr_encode, ar_encode, money, create_time, ar_account, ar_user_id FROM received_deducted 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 received_deducted  WHERE company_id = @company_id AND is_delete = 0" + sqlParam + " ORDER BY id DESC) AS R )";
     }
     sql += " ORDER BY id DESC; SELECT COUNT(*) FROM received_deducted WHERE company_id = @company_id AND is_delete = 0" + sqlParam;
     using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
     {
         while (dr.Read())
         {
             ReceivedDeducted rd = new ReceivedDeducted();
             rd.Id = dr.GetInt32(0);
             rd.CompanyId = dr.GetInt32(1);
             Client client = new Client();
             client = new ClientDAL().GetClientById(dr.GetInt32(2));
             rd.Client = client;
             rd.SrEncode = dr.GetString(3);
             rd.ArEncode = dr.GetString(4);
             rd.Money = dr.GetDecimal(5);
             rd.CreateTime = dr.GetDateTime(6);
             rd.ArAccount = dr.GetString(7);
             rd.ArUserId = dr.GetInt32(8);
             result.Results.Add(rd);
         }
         dr.NextResult();
         while (dr.Read())
         {
             result.TotalCount = dr.GetInt32(0);
         }
     }
     return result;
 }
Exemple #9
0
        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;
        }
Exemple #10
0
        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;
        }
Exemple #11
0
        public List<ClientRecharge> GetRechargeDetailStatistic(DateTime startDate, DateTime endDate, int companyId, int clientId, int userId, int receiveUserId, string pmIds)
        {
            List<ClientRecharge> result = new List<ClientRecharge>();

            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.GenerateInputIntParameter("@user_id", userId),
                SqlUtilities.GenerateInputIntParameter("@receive_user_id", receiveUserId),
            };

            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 (clientId >= 0)
            {
                sqlParam += " AND client_id = @client_id";
            }
            if (userId > 0)
            {
                sqlParam += " AND user_id = @user_id";
            }
            if (receiveUserId > 0)
            {
                sqlParam += " AND user_id = @receive_user_id";
            }
            if (!string.IsNullOrEmpty(pmIds))
            {
                sqlParam += " AND payment_method_id IN(" + pmIds + ")";
            }

            string sql = "SELECT client_id FROM recharges WHERE is_delete = 0 " + sqlParam + " GROUP BY client_id";
            using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
            {
                while (dr.Read())
                {
                    ClientRecharge cr = new ClientRecharge();
                    Client client = new ClientDAL().GetClientById(dr.GetInt32(0));
                    cr.Client = client;
                    List<Recharge> rechargeResult = new RechargeDAL().GetRechargeStatistic(startDate, endDate, companyId, client.Id, userId, receiveUserId, pmIds);
                    cr.RechargeList = rechargeResult;
                    result.Add(cr);
                }
            }
            return result;
        }
Exemple #12
0
 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;
 }
Exemple #13
0
 public PaginationQueryResult<Order> GetOrderByClientId(PaginationQueryCondition condition, int cId)
 {
     PaginationQueryResult<Order> result = new PaginationQueryResult<Order>();
     SqlParameter[] param = new SqlParameter[] {
         SqlUtilities.GenerateInputIntParameter("@client_id", cId)
     };
     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";
     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 ORDER BY id DESC) AS O) ";
     }
     sql += " ORDER BY id DESC; SELECT COUNT(*) FROM orders WHERE is_delete = 0 AND client_id = @client_id ";
     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;
 }
Exemple #14
0
 public PaginationQueryResult<Order> GetOrderByStatus(PaginationQueryCondition condition, OrderStatus status)
 {
     PaginationQueryResult<Order> result = new PaginationQueryResult<Order>();
     SqlParameter[] param = new SqlParameter[] {
         SqlUtilities.GenerateInputParameter("@status", SqlDbType.TinyInt, (byte)status)
     };
     string sql = "SELECT TOP " + condition.PageSize + " id, client_id, encode, status, costs, create_time, remark, is_mail_send, audit_user_id, audit_time, check_user_id, check_time, create_time FROM orders WHERE is_delete = 0 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 status = @status ORDER BY id DESC) AS O) ";
     }
     sql += " ORDER BY id DESC; SELECT COUNT(*) FROM orders WHERE is_delete = 0 AND status = @status";
     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.Encode = dr.GetString(2);
             order.Status = EnumConvertor.ConvertToOrderStatus(dr.GetByte(3));
             order.Costs = dr.GetDecimal(4);
             order.CreateTime = dr.GetDateTime(5);
             order.Remark = dr.GetString(6);
             order.IsMailSend = dr.GetBoolean(7);
             if (!dr.IsDBNull(8))
             {
                 order.AuditUserId = dr.GetInt32(8);
             }
             if (!dr.IsDBNull(9))
             {
                 order.AuditTime = dr.GetDateTime(9);
             }
             if (!dr.IsDBNull(10))
             {
                 order.CheckUserId = dr.GetInt32(10);
             }
             if (!dr.IsDBNull(11))
             {
                 order.CheckTime = dr.GetDateTime(11);
             }
             order.CreateTime = dr.GetDateTime(12);
             result.Results.Add(order);
         }
         dr.NextResult();
         while (dr.Read())
         {
             result.TotalCount = dr.GetInt32(0);
         }
     }
     return result;
 }
Exemple #15
0
        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 ReceivedDeducted GetReceivedDeductedBySrEncode(string srEncode)
 {
     ReceivedDeducted rd = null;
     SqlParameter[] param = new SqlParameter[] {
         SqlUtilities.GenerateInputNVarcharParameter("@sr_encode", 50, srEncode)
     };
     string sql = "SELECT id, company_id, client_id, sr_encode, ar_encode, money, create_time, ar_account, ar_user_id FROM received_deducted WHERE sr_encode = @sr_encode";
     using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
     {
         while (dr.Read())
         {
             rd = new ReceivedDeducted();
             rd.Id = dr.GetInt32(0);
             rd.CompanyId = dr.GetInt32(1);
             Client client = new Client();
             client = new ClientDAL().GetClientById(dr.GetInt32(2));
             rd.Client = client;
             rd.SrEncode = dr.GetString(3);
             rd.ArEncode = dr.GetString(4);
             rd.Money = dr.GetDecimal(5);
             rd.CreateTime = dr.GetDateTime(6);
             rd.ArAccount = dr.GetString(7);
             rd.ArUserId = dr.GetInt32(8);
         }
     }
     return rd;
 }
Exemple #17
0
        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;
        }
Exemple #18
0
 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;
 }
Exemple #19
0
        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;
        }
Exemple #20
0
        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;
        }
Exemple #21
0
        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;
        }
Exemple #22
0
 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;
 }
Exemple #23
0
 public PaginationQueryResult<Order> GetOrderByClientIdAndDate(PaginationQueryCondition condition, int clientId, 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)
     };
     string sql = "SELECT TOP " + condition.PageSize + " id, client_id, encode, status, costs, create_time, 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 "+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 "+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 "+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.Encode = dr.GetString(2);
             order.Status = EnumConvertor.ConvertToOrderStatus(dr.GetByte(3));
             order.Costs = dr.GetDecimal(4);
             order.CreateTime = dr.GetDateTime(5);
             order.Remark = dr.GetString(6);
             order.IsMailSend = dr.GetBoolean(7);
             if (!dr.IsDBNull(8))
             {
                 order.AuditUserId = dr.GetInt32(8);
             }
             if (!dr.IsDBNull(9))
             {
                 order.AuditTime = dr.GetDateTime(9);
             }
             if (!dr.IsDBNull(10))
             {
                 order.CheckUserId = dr.GetInt32(10);
             }
             if (!dr.IsDBNull(11))
             {
                 order.CheckTime = dr.GetDateTime(11);
             }
             result.Results.Add(order);
         }
         dr.NextResult();
         while (dr.Read())
         {
             result.TotalCount = dr.GetInt32(0);
         }
     }
     return result;
 }
Exemple #24
0
 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;
 }
Exemple #25
0
 public PaginationQueryResult<Order> GetAuditOrderByConsignType(PaginationQueryCondition condition, int consignType)
 {
     PaginationQueryResult<Order> result = new PaginationQueryResult<Order>();
     SqlParameter[] param = new SqlParameter[] {
         SqlUtilities.GenerateInputParameter("@status", SqlDbType.TinyInt, (byte)OrderStatus.WAIT_AUDIT)
     };
     string sql = "";
     switch (consignType)
     {
         case 1:
             sql = "SELECT TOP " + condition.PageSize + " id, client_id, encode, status, costs, create_time, remark FROM orders WHERE is_delete = 0 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 status = @status ORDER BY id DESC) AS O) ";
             }
             sql += " ORDER BY id DESC; SELECT COUNT(*) FROM orders WHERE status = @status";
             break;
         case 2:
             sql = "SELECT TOP " + condition.PageSize + " OS.id, OS.client_id, OS.encode, OS.status, OS.costs, OS.create_time, 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.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.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.status = @status AND (CS.balance + CS.credit) >= OS.costs";
             break;
         case 3:
             sql = "SELECT TOP " + condition.PageSize + " OS.id, OS.client_id, OS.encode, OS.status, OS.costs, OS.create_time, 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.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.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.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.Encode = dr.GetString(2);
             order.Status = EnumConvertor.ConvertToOrderStatus(dr.GetByte(3));
             order.Costs = dr.GetDecimal(4);
             order.CreateTime = dr.GetDateTime(5);
             order.Remark = dr.GetString(6);
             result.Results.Add(order);
         }
         dr.NextResult();
         while (dr.Read())
         {
             result.TotalCount = dr.GetInt32(0);
         }
     }
     return result;
 }
Exemple #26
0
        public List<ClientOrder> GetClientOrderListByParameters(int clientId, DateTime startDate, DateTime endDate)
        {
            List<ClientOrder> result = new List<ClientOrder>();

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

            string sql = "SELECT id, client_id, client_address_id, real_name, phone, email, postcode, address, remark, create_time, country, city, encode FROM client_orders WHERE 1=1" + sqlParam + " ORDER BY create_time ASC";

            using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
            {
                while (dr.Read())
                {
                    ClientOrder co = new ClientOrder();
                    co.Id = dr.GetInt32(0);
                    co.ClientId = dr.GetInt32(1);
                    co.ClientAddressId = dr.GetInt32(2);
                    co.RealName = dr.GetString(3);
                    co.Phone = dr.GetString(4);
                    co.Email = dr.GetString(5);
                    co.Postcode = dr.GetString(6);
                    co.Address = dr.GetString(7);
                    co.Remark = dr.GetString(8);
                    co.CreateTime = dr.GetDateTime(9);
                    co.Country = dr.GetString(10);
                    co.City = dr.GetString(11);
                    co.Encode = dr.GetString(12);
                    //ClientAddress ca = new ClientAddress();
                    //ca = new ClientAddressDAL().GetClientAddressById(co.ClientAddressId);
                    //if (ca != null)
                    //{
                    //    co.PostAddress = ca.Address + ", " + ca.Province;
                    //}
                    //else
                    //{
                    //    co.PostAddress = "";
                    //}
                    Client client = new ClientDAL().GetClientById(co.ClientId);
                    if (client != null)
                    {
                        Company comp = new CompanyDAL().GetCompanyById(client.CompanyId);
                        if (comp != null)
                        {
                            switch (comp.Name)
                            {
                                case "亿度物流宁波总公司":
                                    co.PostAddress = "站前路238 (NB)";
                                    break;
                                case "亿度物流义乌分公司":
                                    co.PostAddress = "站前路238 (YW)";
                                    break;
                                case "亿度物流杭州分公司":
                                    co.PostAddress = "站前路238 (HZ)";
                                    break;
                                case "亿度物流上海分公司":
                                    co.PostAddress = "站前路238 (SH)";
                                    break;
                                case "亿度物流深圳分公司":
                                    co.PostAddress = "站前路238 (SZ)";
                                    break;
                                default:
                                    co.PostAddress = "站前路238";
                                    break;
                            }
                        }
                    }
                    result.Add(co);
                }
            }
            return result;
        }
Exemple #27
0
 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;
 }