C# Pagination.PaginationQueryCondition类代码示例

 public static PaginationQueryCondition GetCurrentPaginationQueryCondition(HttpRequest request)
 {
     int page = GetCurrentPage(request);
     int size = GetPageSize(request);
     PaginationQueryCondition condition = new PaginationQueryCondition(page, size);
     return condition;
 }

 public PaginationQueryResult<User> GetLightUser(PaginationQueryCondition condition)
 {
     PaginationQueryResult<User> result = new PaginationQueryResult<User>();
     StringBuilder sb = new StringBuilder();
     sb.Append("SELECT id, username, real_name, sex, education, mobile, email, join_date, contract_date, commission, company_id  FROM (SELECT id, username, real_name, sex, education, mobile, email, join_date, contract_date, commission, company_id, row_number() over (ORDER BY Id DESC) AS RN FROM users WHERE is_delete = 0) AS Result WHERE RN BETWEEN ");
     sb.Append((condition.CurrentPage - 1) * condition.PageSize + 1);
     sb.Append(" AND ");
     sb.Append(condition.CurrentPage * condition.PageSize);
     sb.Append(";SELECT COUNT(*) FROM users where is_delete = 0");
     using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sb.ToString(), null))
     {
         while (dr.Read())
         {
             User user = new User();
             user.Id = dr.GetInt32(0);
             user.Username = dr.GetString(1);
             user.RealName = dr.GetString(2);
             user.Sex = dr.GetBoolean(3);
             user.Education = dr.GetString(4);
             user.Mobile = dr.GetString(5);
             user.Email = dr.GetString(6);
             user.JoinDate = dr.GetDateTime(7);
             user.ContractDate = dr.GetDateTime(8);
             user.Commission = dr.GetDecimal(9);
             user.CompanyId = dr.GetInt32(10);
             result.Results.Add(user);
         }
         dr.NextResult();
         while (dr.Read())
         {
             result.TotalCount = dr.GetInt32(0);
         }
     }
     return result;
 }

 public PaginationQueryResult<CarrierArea> GetCarrierArea(PaginationQueryCondition condition)
 {
     PaginationQueryResult<CarrierArea> result = new PaginationQueryResult<CarrierArea>();
     string sql = "SELECT TOP " + condition.PageSize + " id, carrier_id, name , encode FROM carrier_area ";
     if (condition.CurrentPage > 1)
     {
         sql += " WHERE id > (SELECT MAX(id) FROM (SELECT TOP " + condition.PageSize*(condition.CurrentPage-1) + " id FROM carrier_area ORDER BY encode ASC) AS C)";
     }
     sql += " ORDER BY encode ASC; SELECT COUNT(*) FROM carrier_area ";
     using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, null))
     {
         while (dr.Read())
         {
             CarrierArea ca = new CarrierArea();
             ca.Id = dr.GetInt32(0);
             Carrier carrier = new CarrierDAL().GetCarrierById(dr.GetInt32(1));
             ca.Carrier = carrier;
             ca.Name = dr.GetString(2);
             ca.Encode = dr.GetString(3);
             result.Results.Add(ca);
         }
         dr.NextResult();
         while (dr.Read())
         {
             result.TotalCount = dr.GetInt32(0);
         }
     }
     return result;
 }

        public PaginationQueryResult<News> GetNews(PaginationQueryCondition condition)
        {
            PaginationQueryResult<News> result = new PaginationQueryResult<News>();

            string sql = "SELECT TOP " + condition.PageSize + " news.id, title, content, create_time, news.category_id, news_categories.name FROM news INNER JOIN news_categories ON (news_categories.id = news.category_id) ";
            if (condition.CurrentPage > 1)
                sql += " WHERE news.id<(SELECT MIN(id) FROM (SELECT TOP " + condition.PageSize * (condition.CurrentPage - 1) + " news.id FROM news INNER JOIN news_categories ON (news_categories.id = news.category_id)  ORDER BY news.id DESC) AS D)";
            sql += " ORDER BY news.id DESC; SELECT COUNT(*) FROM news INNER JOIN news_categories ON (news_categories.id = news.category_id)";

            using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, null))
            {
                while (dr.Read())
                {
                    News news = new News();
                    news.Id = dr.GetInt32(0);
                    news.Title = dr.GetString(1);
                    news.Content = dr.GetString(2);
                    news.CreateTime = dr.GetDateTime(3);
                    news.Category = new NewsCategory();
                    news.Category.Id = dr.GetInt32(4);
                    news.Category.Name = dr.GetString(5);

                    result.Results.Add(news);
                }
                dr.NextResult();
                while (dr.Read())
                {
                    result.TotalCount = dr.GetInt32(0);
                }
            }
            return result;
        }

 public PaginationQueryResult<Country> GetCountry(PaginationQueryCondition condition)
 {
     PaginationQueryResult<Country> result = new PaginationQueryResult<Country>();
     string sql = "SELECT TOP " + condition.PageSize + " id, english_name, chinese_name, code, continent, is_front FROM countries";
     if (condition.CurrentPage > 1)
     {
         sql += " WHERE id< (SELECT MIN(id) FROM (SELECT TOP " + condition.PageSize * (condition.CurrentPage - 1) + " id FROM countries ORDER BY id DESC) AS C)";
     }
     sql += " ORDER BY id DESC; SELECT COUNT(*) FROM countries ";
     using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, null))
     {
         while (dr.Read())
         {
             Country country = new Country();
             country.Id = dr.GetInt32(0);
             country.EnglishName = dr.GetString(1);
             country.ChineseName = dr.GetString(2);
             country.Code = dr.GetString(3);
             country.Continent = dr.GetByte(4);
             country.IsFront = dr.GetBoolean(5);
             result.Results.Add(country);
         }
         dr.NextResult();
         while (dr.Read())
         {
             result.TotalCount = dr.GetInt32(0);
         }
     }
     return result;
 }

 public PaginationQueryResult<Insurance> GetInsurance(PaginationQueryCondition condition)
 {
     PaginationQueryResult<Insurance> result = new PaginationQueryResult<Insurance>();
     string sql = "SELECT TOP " + condition.PageSize +" I.id, I.create_time, O.encode, OD.bar_code, OD.carrier_encode, O.client_id,              I.insure_worth FROM insurance AS I JOIN orders AS O ON I.order_id = O.id                                                                             JOIN order_details AS OD ON OD.id = I.order_detail_id WHERE I.is_delete = 0";
     if (condition.CurrentPage > 1)
     {
         sql += " AND I.id < (SELECT MIN(id) FROM (SELECT TOP "+condition.PageSize*(condition.CurrentPage - 1)+" I.id FROM insurance AS I WHERE I.is_delete = 0 ORDER BY I.id DESC) AS E ) ";
     }
     sql += " ORDER BY I.id DESC; SELECT COUNT(*) FROM insurance AS I JOIN orders AS O ON I.order_id = O.id JOIN order_details AS OD ON OD.id = I.order_detail_id WHERE I.is_delete = 0 ";
     using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, null))
     {
         while (dr.Read())
         {
             Insurance insurance = new Insurance();
             insurance.Id = dr.GetInt32(0);
             insurance.CreateTime = dr.GetDateTime(1);
             insurance.OrderEncode = dr.GetString(2);
             insurance.OrderDetailBarCode = dr.GetString(3);
             insurance.CarrierName = new CarrierDAL().GetCarrierByEncode(dr.GetString(4)).Name;
             insurance.ClientName=new ClientDAL().GetClientById(dr.GetInt32(5)).RealName;
             insurance.InsureWorth = dr.GetDecimal(6);
             result.Results.Add(insurance);
         }
     }
     return result;
 }

        public PaginationQueryResult<Complaint> GetComplaint(PaginationQueryCondition condition)
        {
            PaginationQueryResult<Complaint> result = new PaginationQueryResult<Complaint>();

            string sql = "SELECT TOP " + condition.PageSize + " id, client_id, client_name, content, create_time, company_id FROM complaints ";
            if (condition.CurrentPage > 1)
            {
                sql += " WHERE id<(SELECT MIN(id) FROM (SELECT TOP " + condition.PageSize*(condition.CurrentPage-1) + " id FROM complaints ORDER BY id DESC) AS D) ";
            }
            sql += " ORDER BY id DESC; SELECT COUNT(*) FROM complaints ";
            using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, null))
            {
                while (dr.Read())
                {
                    Complaint comp = new Complaint();
                    comp.Id = dr.GetInt32(0);
                    comp.ClientId = dr.GetInt32(1);
                    comp.ClientName = dr.GetString(2);
                    comp.Content = dr.GetString(3);
                    comp.CreateTime = dr.GetDateTime(4);
                    comp.CompanyId = dr.GetInt32(5);
                    result.Results.Add(comp);
                }
                dr.NextResult();
                while (dr.Read())
                {
                    result.TotalCount = dr.GetInt32(0);
                }
            }
            return result;
        }

        public PaginationQueryResult<FetchArrange> GetFetchArrangeByCompanyIdAndDate(PaginationQueryCondition condition, int companyId, 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<FetchArrange> result = new PaginationQueryResult<FetchArrange>();
            SqlParameter[] param = new SqlParameter[] {
                SqlUtilities.GenerateInputIntParameter("@company_id", companyId),
                SqlUtilities.GenerateInputDateTimeParameter("@start_date", startDate),
                SqlUtilities.GenerateInputDateTimeParameter("@end_date", endDate)
            };
            string sql = "SELECT TOP " + condition.PageSize + " id, client_id, type, address, phone, fetch_time, create_time, company_id, remark, user_id FROM fetch_arranges WHERE company_id = @company_id AND is_delete = 0"+sqlTime;
            if (condition.CurrentPage > 1)
            {
                sql += " AND id< (SELECT MIN(id) FROM(SELECT TOP " + condition.PageSize * (condition.CurrentPage - 1) + " id FROM fetch_arranges WHERE company_id = @company_id AND is_delete = 0 "+sqlTime+" ORDER BY id DESC) AS F)";
            }
            sql += " ORDER BY id DESC; SELECT COUNT(*) FROM fetch_arranges WHERE company_id = @company_id AND is_delete = 0" + sqlTime;
            using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
            {
                while (dr.Read())
                {
                    FetchArrange fa = new FetchArrange();
                    fa.Id = dr.GetInt32(0);
                    fa.ClientId = dr.GetInt32(1);
                    fa.ClientName = new ClientDAL().GetClientById(fa.ClientId).RealName;
                    fa.Type = EnumConvertor.ConvertToOrderType(dr.GetByte(2));
                    fa.Address = dr.GetString(3);
                    fa.Phone = dr.GetString(4);
                    fa.FetchTime = dr.GetDateTime(5);
                    fa.CreateTime = dr.GetDateTime(6);
                    fa.CompanyId = dr.GetInt32(7);
                    fa.Remark = dr.GetString(8);
                    if (!dr.IsDBNull(9))
                    {
                        fa.UserId = dr.GetInt32(9);
                    }
                    result.Results.Add(fa);
                }
                dr.NextResult();
                while (dr.Read())
                {
                    result.TotalCount = dr.GetInt32(0);
                }

            }
            return result;
        }

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

 public PaginationQueryResult<DailyCost> GetDailyCostByCompanyId(PaginationQueryCondition condition, int compId)
 {
     SqlParameter[] param = new SqlParameter[] {
         SqlUtilities.GenerateInputIntParameter("@company_id", compId)
     };
     PaginationQueryResult<DailyCost> result = new PaginationQueryResult<DailyCost>();
     string sql = "SELECT TOP " + condition.PageSize + " id, user_id, company_id, audit_user_id, order_user_id, order_time, create_time, audit_time, encode, money, cost_type_id, remark FROM daily_costs WHERE is_delete = 0 AND company_id = @company_id";
     if (condition.CurrentPage > 1)
     {
         sql += " AND id< (SELECT MIN(id) FROM (SELECT TOP " + condition.PageSize * (condition.CurrentPage - 1) + " id FROM daily_costs WHERE is_delete = 0 AND company_id = @company_id ORDER BY id DESC) AS R )";
     }
     sql += " ORDER BY id DESC; SELECT COUNT(*) FROM daily_costs WHERE is_delete = 0 AND company_id = @company_id ";
     using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
     {
         while (dr.Read())
         {
             DailyCost dc = new DailyCost();
             dc.Id = dr.GetInt32(0);
             dc.UserId = dr.GetInt32(1);
             User user = UserOperation.GetUserById(dc.UserId);
             dc.Username = user.RealName;
             dc.DepartmentName = DepartmentOperation.GetDepartmentById(user.DepartmentId).Name;
             dc.CompanyId = dr.GetInt32(2);
             Company company = CompanyOperation.GetCompanyById(dc.CompanyId);
             dc.CompanyName = company.Name;
             if (!dr.IsDBNull(3))
             {
                 dc.AuditUserId = dr.GetInt32(3);
             }
             dc.OrderUserId = dr.GetInt32(4);
             user = UserOperation.GetUserById(dc.OrderUserId);
             dc.OrderUserName = user.RealName;
             dc.OrderTime = dr.GetDateTime(5);
             dc.CreateTime = dr.GetDateTime(6);
             if (!dr.IsDBNull(7))
             {
                 dc.AuditTime = dr.GetDateTime(7);
             }
             dc.Encode = dr.GetString(8);
             dc.Money = dr.GetDecimal(9);
             dc.CostTypeId = dr.GetInt32(10);
             CostType ct = CostTypeOperation.GetCostTypeById(dc.CostTypeId);
             dc.CostType = ct.Name;
             dc.Remark = dr.GetString(11);
             result.Results.Add(dc);
         }
         dr.NextResult();
         while (dr.Read())
         {
             result.TotalCount = dr.GetInt32(0);
         }
     }
     return result;
 }

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

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

        public PaginationQueryResult<WrongOrder> GetWrongOrderByClientId(PaginationQueryCondition condition, int clientId)
        {
            PaginationQueryResult<WrongOrder> result = new PaginationQueryResult<WrongOrder>();
            SqlParameter[] param = new SqlParameter[] {
                SqlUtilities.GenerateInputIntParameter("@client_id", clientId)
            };
            string sql = "SELECT TOP " + condition.PageSize + " WO.id, WO.order_id, WO.company_id, WO.company_name, WO.encode, WO.status, WO.reason, WO.[type], WO.create_time, WO.create_user_id, WO.last_update_time FROM wrong_orders AS WO INNER JOIN orders AS O ON WO.order_id = O.id WHERE            O.client_id = @client_id AND WO.is_delete = 0 AND O.is_delete = 0";
            if (condition.CurrentPage > 1)
            {
                sql += " AND WO.id< (SELECT MIN(id) FROM (SELECT TOP " + condition.PageSize * (condition.CurrentPage - 1) + " WO.id FROM wrong_orders AS WO INNER JOIN orders AS O ON WO.order_id = O.id WHERE O.client_id = @client_id AND WO.is_delete = 0 AND O.is_delete = 0 ORDER BY WO.id DESC) AS W)";
            }
            sql += " ORDER BY id DESC; SELECT COUNT(*) FROM wrong_orders AS WO INNER JOIN orders AS O ON WO.order_id = O.id WHERE O.client_id =         @client_id AND WO.is_delete = 0 AND O.is_delete = 0";

            using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
            {
                while (dr.Read())
                {
                    WrongOrder wo = new WrongOrder();
                    wo.Id = dr.GetInt32(0);
                    if (!dr.IsDBNull(1))
                    {
                        Order order = new OrderDAL().GetOrderById(dr.GetInt32(1));
                        wo.Order = order;
                    }
                    wo.CompanyId = dr.GetInt32(2);
                    wo.CompanyName = dr.GetString(3);
                    wo.Encode = dr.GetString(4);
                    wo.Status = EnumConvertor.ConvertToWrongOrderStatus(dr.GetByte(5));
                    wo.Reason = dr.GetString(6);
                    wo.Type = dr.GetString(7);
                    wo.CreateTime = dr.GetDateTime(8);
                    wo.CreateUserId = dr.GetInt32(9);
                    wo.LastUpdateCreateTime = dr.GetDateTime(10);
                    result.Results.Add(wo);
                }
                dr.NextResult();
                while (dr.Read())
                {
                    result.TotalCount = dr.GetInt32(0);
                }
            }
            return result;
        }

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

 public PaginationQueryResult<AlreadyPaid> GetAlreadyPaidByCompanyId(PaginationQueryCondition condition, int compId)
 {
     SqlParameter[] param = new SqlParameter[] {
         SqlUtilities.GenerateInputIntParameter("@company_id", compId)
     };
     PaginationQueryResult<AlreadyPaid> result = new PaginationQueryResult<AlreadyPaid>();
     string sql = "SELECT TOP " + condition.PageSize + " id, payment_method_id, invoice, carrier_id, user_id, company_id, create_time, paid_time, encode, money, remark, start_time, end_time FROM already_paid WHERE is_delete = 0 AND company_id = @company_id";
     if (condition.CurrentPage > 1)
     {
         sql += " AND id< (SELECT MIN(id) FROM (SELECT TOP " + condition.PageSize * (condition.CurrentPage - 1) + " id FROM already_paid WHERE is_delete = 0 AND company_id = @company_id ORDER BY id DESC) AS R )";
     }
     sql += " ORDER BY id DESC; SELECT COUNT(*) FROM already_paid WHERE is_delete = 0 AND company_id = @company_id ";
     using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
     {
         while (dr.Read())
         {
             AlreadyPaid ap = new AlreadyPaid();
             ap.Id = dr.GetInt32(0);
             ap.PaymentMethod=PaymentMethodOperation.GetPaymentMethodById(dr.GetInt32(1));
             ap.Invoice = dr.GetString(2);
             ap.Carrier = CarrierOperation.GetCarrierById(dr.GetInt32(3));
             ap.User = UserOperation.GetUserById(dr.GetInt32(4));
             ap.CompanyId = dr.GetInt32(5);
             ap.CreateTime = dr.GetDateTime(6);
             ap.PaidTime = dr.GetDateTime(7);
             ap.Encode = dr.GetString(8);
             ap.Money = dr.GetDecimal(9);
             ap.Remark = dr.GetString(10);
             ap.StartTime = dr.GetDateTime(11);
             ap.EndTime = dr.GetDateTime(12);
             result.Results.Add(ap);
         }
         dr.NextResult();
         while (dr.Read())
         {
             result.TotalCount = dr.GetInt32(0);
         }
     }
     return result;
 }