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; }
public PaginationQueryResult<PostPlan> GetPostPlanByCompanyId(PaginationQueryCondition condition, int compId) { SqlParameter[] param = new SqlParameter[] { SqlUtilities.GenerateInputIntParameter("@company_id", compId) }; PaginationQueryResult<PostPlan> result = new PaginationQueryResult<PostPlan>(); string sql = "SELECT TOP " + condition.PageSize + " id, carrier_id, company_id, package_count, weight, depot_id, user_id, create_time FROM post_plans 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 post_plans WHERE company_id = @company_id AND is_delete = 0 ORDER BY id DESC) AS P)"; } sql += " ORDER BY id DESC; SELECT COUNT(*) FROM post_plans WHERE company_id = @company_id AND is_delete = 0"; using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param)) { while (dr.Read()) { PostPlan pp = new PostPlan(); pp.Id = dr.GetInt32(0); Carrier carrier = new CarrierDAL().GetCarrierById(dr.GetInt32(1)); pp.Carrier = carrier; pp.CompanyId = dr.GetInt32(2); pp.PackageCount = dr.GetInt32(3); pp.Weight = dr.GetDecimal(4); Depot depot = new DepotDAL().GetDepotById(dr.GetInt32(5)); pp.Depot = depot; User user = new UserDAL().GetUserById(dr.GetInt32(6)); pp.User = user; pp.CreateTime = dr.GetDateTime(7); result.Results.Add(pp); } dr.NextResult(); while (dr.Read()) { result.TotalCount = dr.GetInt32(0); } } return result; }
public PaginationQueryResult<Company> GetCompany(PaginationQueryCondition condition) { PaginationQueryResult<Company> result=new PaginationQueryResult<Company>(); string sql = "SELECT TOP " + condition.PageSize + " id, name, area_code, address, contact_person, phone, email, smtp, commission, email_password, qq, msn FROM companies WHERE id_delete = 0"; if (condition.CurrentPage > 1) { sql += " AND id<(SELECT MIN(id) FROM (SELECT TOP " + condition.PageSize * (condition.CurrentPage - 1) + " id FROM companies WHERE is_delete = 0 ORDER BY id DESC) AS D)"; } sql += " ORDER BY id DESC; SELECT COUNT(*) FROM companies WHERE id_delete = 0"; using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, null)) { while (dr.Read()) { Company company = new Company(); company.Id = dr.GetInt32(0); company.Name = dr.GetString(1); company.AreaCode = EnumConvertor.ConvertToAreaCode(dr.GetByte(2)); company.Address = dr.GetString(3); company.ContactPerson = dr.GetString(4); company.Phone = dr.GetString(5); company.Email = dr.GetString(6); company.Smtp = dr.GetString(7); company.Commission = dr.GetDecimal(8); company.EmailPassword = dr.GetString(9); company.QQ = dr.GetString(10); company.MSN = dr.GetString(11); result.Results.Add(company); } dr.NextResult(); while (dr.Read()) { result.TotalCount = dr.GetInt32(0); } } return result; }
public PaginationQueryResult<Client> GetClient(PaginationQueryCondition condition) { PaginationQueryResult<Client> result = new PaginationQueryResult<Client>(); string sql = "SELECT TOP " + condition.PageSize + " id, username, password, real_name, id_card, phone, mobile, email, address, credit, is_message, create_date, balance FROM clients WHERE is_delete = 0 "; if (condition.CurrentPage > 1) { sql += " AND id<(SELECT MIN(id) FROM(SELECT TOP " + condition.PageSize * (condition.CurrentPage - 1) + " id FROM clients WHERE is_delete = 0 ORDER BY id DESC) AS D)"; } sql += " ORDER BY id DESC; SELECT COUNT(*) FROM clients WHERE is_delete = 0 "; using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, null)) { while (dr.Read()) { Client client = new Client(); client.Id = dr.GetInt32(0); client.Username = dr.GetString(1); client.Password = dr.GetString(2); client.RealName = dr.GetString(3); client.IdCard = dr.GetString(4); client.Phone = dr.GetString(5); client.Mobile = dr.GetString(6); client.Email = dr.GetString(7); client.Address = dr.GetString(8); client.Credit = dr.GetDecimal(9); client.IsMessage = dr.GetBoolean(10); client.CreateDate = dr.GetDateTime(11); client.Balance = dr.GetDecimal(12); result.Results.Add(client); } dr.NextResult(); while (dr.Read()) { result.TotalCount = dr.GetInt32(0); } } return result; }
public PaginationQueryResult<Complaint> GetComplaintByClientId(PaginationQueryCondition condition, int clientId) { SqlParameter[] param = new SqlParameter[] { SqlUtilities.GenerateInputIntParameter("@client_id", clientId) }; PaginationQueryResult<Complaint> result = new PaginationQueryResult<Complaint>(); string sql = "SELECT TOP " + condition.PageSize + " id, client_id, client_name, content, create_time, company_id, is_reply, title FROM complaints WHERE client_id = @client_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 complaints WHERE client_id = @client_id AND is_delete = 0 ORDER BY id DESC) AS D) "; } sql += " ORDER BY id DESC; SELECT COUNT(*) FROM complaints WHERE client_id = @client_id AND is_delete = 0 "; using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param)) { 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); comp.IsReply = dr.GetBoolean(6); comp.Title = dr.GetString(7); result.Results.Add(comp); } dr.NextResult(); while (dr.Read()) { result.TotalCount = dr.GetInt32(0); } } return result; }
public PaginationQueryResult<ShouldPay> GetShouldPayByCompanyId(PaginationQueryCondition condition, int compId) { SqlParameter[] param = new SqlParameter[] { SqlUtilities.GenerateInputIntParameter("@company_id", compId) }; PaginationQueryResult<ShouldPay> result = new PaginationQueryResult<ShouldPay>(); string sql = "SELECT TOP " + condition.PageSize + " id, order_encode, order_detail_id, carrier_id, type, user_id, company_id, create_time, encode FROM should_pay WHERE is_delete = 0 AND is_paid = 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 should_pay ORDER BY id DESC) AS R )"; } sql += " ORDER BY id DESC; SELECT COUNT(*) FROM should_pay WHERE is_delete = 0 AND company_id = @company_id "; using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param)) { while (dr.Read()) { ShouldPay sp = new ShouldPay(); sp.Id = dr.GetInt32(0); sp.OrderEncode = dr.GetString(1); sp.OrderDetail = new OrderDetailDAL().GetOrderDetailById(dr.GetInt32(2)); sp.Carrier = new CarrierDAL().GetCarrierById(dr.GetInt32(3)); sp.Type = dr.GetString(4); sp.UserId = dr.GetInt32(5); sp.CompanyId = dr.GetInt32(6); sp.CreateTime = dr.GetDateTime(7); sp.Encode = dr.GetString(8); result.Results.Add(sp); } dr.NextResult(); while (dr.Read()) { result.TotalCount = dr.GetInt32(0); } } return result; }
public PaginationQueryResult<ClientOrder> GetClientOrderByParameters(PaginationQueryCondition condition, int clientId, DateTime startDate, DateTime endDate) { PaginationQueryResult<ClientOrder> result = new PaginationQueryResult<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 TOP " + condition.PageSize + " 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; if (condition.CurrentPage > 1) { sql += " AND id <(SELECT MIN(id) FROM (SELECT TOP " + condition.PageSize * (condition.CurrentPage - 1) + " id FROM client_orders WHERE 1=1" + sqlParam + " ORDER BY id DESC) AS O)"; } sql += " ORDER BY id DESC; SELECT COUNT(1) FROM client_orders WHERE 1=1" + sqlParam; 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); result.Results.Add(co); } dr.NextResult(); while (dr.Read()) { result.TotalCount = dr.GetInt32(0); } } return result; }
public PaginationQueryResult<Insurance> GetInsuranceByParameters(PaginationQueryCondition condition, DateTime startDate, DateTime endDate, decimal insureWorth, string searchKey) { PaginationQueryResult<Insurance> result = new PaginationQueryResult<Insurance>(); DateTime minTime = new DateTime(1999, 1, 1); string sqlParam = ""; if (startDate > minTime && endDate > minTime) { sqlParam += " AND I.create_time BETWEEN @start_date AND @end_date"; } else if (startDate > minTime && endDate <= minTime) { sqlParam += " AND I.create_time >= @start_date "; } else if(startDate<=minTime && endDate >minTime) { sqlParam += " AND I.create_time <= @end_date"; } if (insureWorth > 0) { sqlParam += " AND I.insure_worth > @insure_worth"; } if (!string.IsNullOrEmpty(searchKey)) { sqlParam += " AND O.encode LIKE '%" + searchKey + "%' OR OD.bar_code LIKE '%" + searchKey + "%' OR I.carrier_name LIKE '%" + searchKey + "%' OR I.client_name LIKE '%" + searchKey + "%'"; } SqlParameter[] param = new SqlParameter[] { SqlUtilities.GenerateInputDateTimeParameter("@start_date", startDate), SqlUtilities.GenerateInputDateTimeParameter("@end_date", endDate), SqlUtilities.GenerateInputParameter("@insure_worth", SqlDbType.Decimal, insureWorth) }; 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" + sqlParam; 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 " + sqlParam + " 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" + sqlParam; using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param)) { 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 static PaginationQueryResult<FetchArrange> GetFetchArrangeByCompanyIdAndDate(PaginationQueryCondition condition, int companyId, DateTime startDate, DateTime endDate) { return dal.GetFetchArrangeByCompanyIdAndDate(condition, companyId, startDate, endDate); }
public static PaginationQueryResult<FetchArrange> GetFetchArrangeByCompanyId(PaginationQueryCondition condition, int companyId) { return dal.GetFetchArrangeByCompanyId(condition, companyId); }
public static PaginationQueryResult<AlreadyPaid> GetAlreadyPaidByCompanyIdAndDate(PaginationQueryCondition condition, int compId, DateTime startDate, DateTime endDate) { return dal.GetAlreadyPaidByCompanyIdAndDate(condition, compId, startDate, endDate); }
public static PaginationQueryResult<AlreadyPaid> GetAlreadyPaidByCompanyId(PaginationQueryCondition condition, int compId) { return dal.GetAlreadyPaidByCompanyId(condition, compId); }
public static PaginationQueryResult<News> GetNewsByCategoryId(PaginationQueryCondition condition, int catId) { return dal.GetNewsByCategoryId(condition, catId); }
public static PaginationQueryResult<News> GetNews(PaginationQueryCondition condition) { return dal.GetNews(condition); }
public static PaginationQueryResult<Quote> GetQuoteByParameters(PaginationQueryCondition condition, int compId, DateTime startDate, DateTime endDate, string strStatus, string keyword) { return dal.GetQuoteByParameters(condition, compId, startDate, endDate, strStatus, keyword); }
public static PaginationQueryResult<Quote> GetQuoteByCompanyId(PaginationQueryCondition condition, int compId) { return dal.GetQuoteByCompanyId(condition, compId); }