/// <summary> /// 获取采购分页库存 /// </summary> /// <param name="pager"></param> public override void GetPmWareGoodsListByPager(ref Model.Pager <Model.Procurement.CBPmWareGoods> pager) { #region sql条件 string sqlWhere = @""; if (pager.PageFilter.Type == 0) { sqlWhere += "( WareNum <= 0 ) "; } else if (pager.PageFilter.Type > 0) { sqlWhere += "( WareNum > 0 ) "; } #endregion using (var context = Context.UseSharedConnection(true)) { pager.Rows = context.Select <CBPmWareGoods>(" PmWareGoods.*, PdProduct.ProductName as ProName,'' as Spec ") .From(" PmWareGoods inner join PdProduct on PmWareGoods.ProSysNo=PdProduct.SysNo ") .Where(sqlWhere) .Paging(pager.CurrentPage, pager.PageSize) .OrderBy("SysNo desc") .QueryMany(); pager.TotalRows = context.Select <int>("count(1)") .From(" PmWareGoods inner join PdProduct on PmWareGoods.ProSysNo=PdProduct.SysNo ") .Where(sqlWhere) .QuerySingle(); } }
/// <summary> /// 盘点单列表分页 /// </summary> /// <param name="pager"></param> public override void GetTakeStockOrderListPagerByDsSysNo(ref Model.Pager <Model.Pos.CBDsTakeStockOrder> pager) { #region sql条件 string sqlWhere = @"(DsTakeStockOrder.DsSysNo=@DsSysNo or " + pager.PageFilter.DsSysNo + "=0 ) "; if (pager.PageFilter.DateTime != null) { sqlWhere += " and (CheckTime>='" + pager.PageFilter.DateTime.Value.ToString("yyyy-MM-dd") + " 00:00:00' and CheckTime<='" + pager.PageFilter.DateTime.Value.ToString("yyyy-MM-dd") + " 23:59:59') "; } #endregion using (var context = Context.UseSharedConnection(true)) { pager.Rows = context .Select <CBDsTakeStockOrder>("DsTakeStockOrder.*,DsDealer.DealerName , TotalAmount=(select sum(PdPrice.Price*(DsTakeStockItem.ProNowNum-DsTakeStockItem.ProOldNum)) from DsTakeStockItem inner join PdPrice on PdPrice.ProductSysNo=DsTakeStockItem.ProductSysNo and PdPrice.SourceSysNo=0 where DsTakeStockItem.PSysNo=DsTakeStockOrder.SysNo ) ") .From(" DsTakeStockOrder inner join DsDealer on DsTakeStockOrder.DsSysNo=DsDealer.SysNo ") .Where(sqlWhere) .Parameter("DsSysNo", pager.PageFilter.DsSysNo) .Paging(pager.CurrentPage, pager.PageSize) .OrderBy("DsTakeStockOrder.SysNo desc") .QueryMany(); pager.TotalRows = context.Select <int>("count(1)") .From(" DsTakeStockOrder ") .Where(sqlWhere) .Parameter("DsSysNo", pager.PageFilter.DsSysNo) .QuerySingle(); } }
public override void GetPosOrderListPagerByDsSysNo(ref Model.Pager <Model.Pos.DBDsPosOrder> pager) { #region sql条件 string sqlWhere = @"(DsSysNo=@DsSysNo or " + pager.PageFilter.DsSysNo + " = 0 ) "; if (pager.PageFilter.BeginDate != null) { sqlWhere += " and SaleTime >='" + pager.PageFilter.BeginDate.Value.ToString("yyyy-MM-dd") + " 00:00:00' "; } if (pager.PageFilter.EndDate != null) { sqlWhere += " and SaleTime <='" + pager.PageFilter.EndDate.Value.ToString("yyyy-MM-dd") + " 23:59:59' "; } if (pager.PageFilter.DsPosSysNo > 0) { sqlWhere += " and DsPosOrder.DsPosSysNo = " + pager.PageFilter.DsPosSysNo + " "; } #endregion using (var context = Context.UseSharedConnection(true)) { pager.Rows = context.Select <DBDsPosOrder>("DsPosOrder.*,DsDealer.DealerName as StoreName ") .From(" DsPosOrder inner join DsDealer on DsPosOrder.DsSysNo=DsDealer.SysNo ") .Where(sqlWhere) .Parameter("DsSysNo", pager.PageFilter.DsSysNo) .Paging(pager.CurrentPage, pager.PageSize) .OrderBy("DsPosOrder.SysNo desc") .QueryMany(); pager.TotalRows = context.Select <int>("count(1)") .From(" DsPosOrder inner join DsDealer on DsPosOrder.DsSysNo=DsDealer.SysNo ") .Where(sqlWhere) .Parameter("DsSysNo", pager.PageFilter.DsSysNo) .QuerySingle(); } }
/// <summary> /// 分销返利分页 /// </summary> /// <param name="pager"></param> /// <remarks>2016-07-15 周 创建</remarks> public override void GeDirectOrdersList(ref Model.Pager <CBDsDealerRebatesRecord> pager) { #region sql条件 string sqlWhere = @"a.RecommendSysNo=@RecommendSysNo "; if (pager.PageFilter.Genre != "0") { sqlWhere += "and a.Genre=@Genre"; } #endregion using (var context = Context.UseSharedConnection(true)) { pager.Rows = context.Select <CBDsDealerRebatesRecord>("a.*, b.Account as RecommendAccount,b.Name as RecommendName,c.Account as ComplyAccount,c.Name as ComplyName,rd.DealerName as RDealerName ") .From("CrCustomerRebatesRecord a left join CrCustomer b on a.RecommendSysNo = b.SysNo left join CrCustomer c on a.ComplySysNo = c.SysNo left join DsDealer rd on b.DealerSysNo = rd.SysNo") .Where(sqlWhere) .Parameter("RecommendSysNo", pager.PageFilter.RecommendSysNo) .Parameter("Genre", pager.PageFilter.Genre) .Paging(pager.CurrentPage, pager.PageSize) .OrderBy("a.RebatesTime desc") .QueryMany(); pager.TotalRows = context.Select <int>("count(1)") .From("CrCustomerRebatesRecord a left join CrCustomer b on a.RecommendSysNo = b.SysNo left join CrCustomer c on a.ComplySysNo = c.SysNo left join DsDealer rd on b.DealerSysNo = rd.SysNo") .Where(sqlWhere) .Parameter("RecommendSysNo", pager.PageFilter.RecommendSysNo) .Parameter("Genre", pager.PageFilter.Genre) .QuerySingle(); } }
public override void GetDsWhRecipienterList(ref Model.Pager <DsWhRecipienter> pageCusList) { #region sql条件 string sqlWhere = @" 1=1 "; if (!string.IsNullOrEmpty(pageCusList.PageFilter.Name)) { sqlWhere += " and Name = '" + pageCusList.PageFilter.Name.Trim() + "' "; } #endregion using (var context = Context.UseSharedConnection(true)) { pageCusList.Rows = context.Select <DsWhRecipienter>(" * ") .From(@" DsWhRecipienter ") .Where(sqlWhere) .Paging(pageCusList.CurrentPage, pageCusList.PageSize) .OrderBy("DsWhRecipienter.SysNo desc") .QueryMany(); pageCusList.TotalRows = context.Select <int>("count(1)") .From(@" DsWhRecipienter ") .Where(sqlWhere) .QuerySingle(); } }
public override void GetPosReturnOrderListPagerByDsSysNo(ref Model.Pager <CBDsPosReturnOrder> pager) { #region sql条件 string sqlWhere = @"(DsPosOrder.DsSysNo=@DsSysNo or " + pager.PageFilter.DsSysNo + "=0) "; if (!string.IsNullOrEmpty(pager.PageFilter.PosName)) { sqlWhere += " and DsPosOrder.DsPosSysNo = '" + pager.PageFilter.PosName + "' "; } #endregion using (var context = Context.UseSharedConnection(true)) { pager.Rows = context .Select <CBDsPosReturnOrder>("DsPosReturnOrder.*,DsPosOrder.SerialNumber as SellOrderNumber,DsPosOrder.DsSysNo ,DsDealer.DealerName as StoreName") .From(" DsPosReturnOrder inner join DsPosOrder on DsPosReturnOrder.OrderSysNo=DsPosOrder.SysNo inner join DsDealer on DsPosOrder.DsSysNo=DsDealer.SysNo ") .Where(sqlWhere) .Parameter("DsSysNo", pager.PageFilter.DsSysNo) .Paging(pager.CurrentPage, pager.PageSize) .OrderBy("DsPosReturnOrder.SysNo desc") .QueryMany(); pager.TotalRows = context.Select <int>("count(1)") .From(" DsPosReturnOrder inner join DsPosOrder on DsPosReturnOrder.OrderSysNo=DsPosOrder.SysNo ") .Where(sqlWhere) .Parameter("DsSysNo", pager.PageFilter.DsSysNo) .QuerySingle(); } }
/// <summary> /// 获取用户积分日志 /// </summary> /// <param name="pager">分页查询条件</param> /// <returns>经验积分日志</returns> /// <remarks>2013-07-10 黄波 创建</remarks> /// <remarks>2013-07-10 苟治国 修改</remarks> public override void GetCrAvailablePointLog(ref Model.Pager <CrAvailablePointLog> pager) { #region sql条件 string sqlWhere = @"(@customersysno=-1 or cp.customersysno=@customersysno) and (@pointType=-1 or cp.pointType=@pointType)"; #endregion using (var context = Context.UseSharedConnection(true)) { pager.Rows = context.Select <CrAvailablePointLog>("cp.*,su.UserName") .From("CrAvailablePointLog cp left join SyUser su on cp.CreatedBy=su.sysno") .Where(sqlWhere) .Parameter("customersysno", pager.PageFilter.CustomerSysNo) .Parameter("pointType", pager.PageFilter.PointType) .Paging(pager.CurrentPage, pager.PageSize) .OrderBy("changedate desc") .QueryMany(); pager.TotalRows = context.Select <int>("count(1)") .From("CrAvailablePointLog cp left join SyUser su on cp.CreatedBy=su.sysno") .Where(sqlWhere) .Parameter("customersysno", pager.PageFilter.CustomerSysNo) .Parameter("pointType", pager.PageFilter.PointType) .QuerySingle(); } }
/// <summary> /// 获取等级积分日志 /// </summary> /// <param name="pager">分页查询条件</param> /// <returns>等级积分日志</returns> /// <remarks>2013-07-10 黄波 创建</remarks> /// <remarks>2013-07-15 苟治国 修改</remarks> public override void GetLevelPointLog(ref Model.Pager <CBCrLevelPointLog> pager) { #region 原sql //select cp.*,su.UserName from CrLevelPointLog cp left join SyUser su on cp.customersysno=su.sysno #endregion #region sql条件 string sqlWhere = @"(@customersysno=-1 or cp.customersysno=@customersysno) and (@changetype=-1 or cp.changetype=@changetype)"; #endregion using (var context = Context.UseSharedConnection(true)) { pager.Rows = context.Select <CBCrLevelPointLog>("cp.*,su.UserName") .From("CrLevelPointLog cp left join SyUser su on cp.LastUpdateBy=su.sysno") .Where(sqlWhere) .Parameter("customersysno", pager.PageFilter.CustomerSysNo) .Parameter("ChangeType", pager.PageFilter.ChangeType) .Paging(pager.CurrentPage, pager.PageSize) .OrderBy("cp.SysNo desc") .QueryMany(); pager.TotalRows = context.Select <int>("count(1)") .From("CrLevelPointLog cp left join SyUser su on cp.LastUpdateBy=su.sysno") .Where(sqlWhere) .Parameter("customersysno", pager.PageFilter.CustomerSysNo) .Parameter("ChangeType", pager.PageFilter.ChangeType) .QuerySingle(); } }
/// <summary> /// 查询等级变更日志详情 /// </summary> /// <param name="pager">查询条件</param> /// <returns>void</returns> /// <remarks>2013-07-10 黄波 添加</remarks> public override void GetCrLevelLogItems(ref Model.Pager <Model.CrLevelLog> pager) { #region sql条件 string sqlWhere = @"(@customersysno=0 or customersysno=@customersysno) and (@ChangeType=0 or ChangeType=@ChangeType) and (@createdby=0 or CREATEDBY=@createdby)"; #endregion using (var context = Context.UseSharedConnection(true)) { pager.Rows = context.Select <Model.CrLevelLog>("cr.*") .From("CrLevelLog cr") .Where(sqlWhere) .Parameter("customersysno", pager.PageFilter.CustomerSysNo) .Parameter("ChangeType", pager.PageFilter.ChangeType) .Parameter("createdby", pager.PageFilter.CreatedBy) .Paging(pager.CurrentPage, pager.PageSize) .OrderBy("changedate desc") .QueryMany(); pager.TotalRows = context.Select <int>("count(1)") .From("CrLevelLog") .Where(sqlWhere) .Parameter("customersysno", pager.PageFilter.CustomerSysNo) .Parameter("ChangeType", pager.PageFilter.ChangeType) .Parameter("createdby", pager.PageFilter.CreatedBy) .QuerySingle(); } }
/// <summary> /// 分页搜索商品数据 /// </summary> /// <param name="pageProList"></param> public override void DoDsWhProductQuery(ref Model.Pager <CBDsWhProduct> pageProList) { #region sql条件 string sqlWhere = @" "; if (pageProList.PageFilter.IsAllDealer) { sqlWhere = " 1=1 "; } else if (pageProList.PageFilter.IsDealer) { sqlWhere = " DsSysNo = '" + pageProList.PageFilter.DsSysNo + "' "; } else if (pageProList.PageFilter.IsCustomer) { sqlWhere = " CustomerCode = '" + pageProList.PageFilter.CustomerCode + "' "; } #endregion using (var context = Context.UseSharedConnection(true)) { pageProList.Rows = context.Select <CBDsWhProduct>(" DsWhProduct.* ") .From(@" DsWhProduct ") .Where(sqlWhere) .Paging(pageProList.CurrentPage, pageProList.PageSize) .OrderBy(" DsWhProduct.SysNo desc ") .QueryMany(); pageProList.TotalRows = context.Select <int>("count(1)") .From(@" DsWhProduct ") .Where(sqlWhere) .QuerySingle(); } }
/// <summary> /// 充值记录 /// </summary> /// <param name="pager"></param> public override void GetCrRechargeLog(ref Model.Pager <CrRecharge> pager) { #region sql条件 string sqlWhere = @"(@CustomerSysNo=-1 or cp.CustomerSysNo=@CustomerSysNo) and (@State=-1 or cp.State=@State) and IsDelete=0"; #endregion using (var context = Context.UseSharedConnection(true)) { pager.Rows = context.Select <CrRecharge>("cp.*") .From("CrRecharge cp") .Where(sqlWhere) .Parameter("CustomerSysNo", pager.PageFilter.CustomerSysNo) .Parameter("State", pager.PageFilter.State) .Paging(pager.CurrentPage, pager.PageSize) .OrderBy("cp.ReAddTime desc") .QueryMany(); pager.TotalRows = context.Select <int>("count(1)") .From("CrRecharge cp") .Where(sqlWhere) .Parameter("CustomerSysNo", pager.PageFilter.CustomerSysNo) .Parameter("State", pager.PageFilter.State) .QuerySingle(); } }
public void GoodsManagePager(ref Model.Pager <CBDsWhGoodsManagement> pageCusList, bool IsBindAllDealer, bool IsBindDealer, bool IsCustomer, int DsSysNo, string CusCode, string OrderByKey, string OrderbyType) { IDsWhGoodsManagementDao.Instance.GoodsManagePager(ref pageCusList, IsBindAllDealer, IsBindDealer, IsCustomer, DsSysNo, CusCode, OrderByKey, OrderbyType); }
public override void OrderManagerGroupPager(Model.Pager <WhGoodsManagementGroup> pageCusList) { #region sql条件 string sqlWhere = @" 1=1 "; #endregion string typeList = pageCusList.PageFilter.CusCode; switch (typeList.Split('_')[0]) { case "ALL": sqlWhere = " 1=1 "; break; case "Dealer": sqlWhere = "( DsSysNo = '" + typeList.Split('_')[2] + "' )"; break; case "Customer": sqlWhere = " ( CustomerCode = '" + typeList.Split('_')[1] + "' ) "; break; } using (var context = Context.UseSharedConnection(true)) { pageCusList.Rows = context.Select <WhGoodsManagementGroup>(" tab.* ") .From(@" (select distinct CONVERT(varchar(10),CreateTime,120) as CreateTime, 'E'+ CONVERT(varchar(10),CreateTime,112) as OrderBatchNum , DsWhCustomer.DsSysNo, DsWhGoodsManagement.CustomerCode, OrderCount=(select count(1) from DsWhGoodsManagement a where CONVERT(varchar(10),a.CreateTime,120)=CONVERT(varchar(10),DsWhGoodsManagement.CreateTime,120) and DsWhGoodsManagement.CustomerCode=a.CustomerCode ) from DsWhGoodsManagement inner join DsWhCustomer on DsWhGoodsManagement.CustomerCode=DsWhCustomer.CusCode ) tab ") .Where(sqlWhere) .Paging(pageCusList.CurrentPage, pageCusList.PageSize) .OrderBy(" tab.CreateTime ASC ") .QueryMany(); pageCusList.TotalRows = context.Select <int>("count(1)") .From(@" (select distinct CONVERT(varchar(10),CreateTime,120) as CreateTime, 'E'+ CONVERT(varchar(10),CreateTime,112) as OrderBatchNum , DsWhCustomer.DsSysNo, DsWhGoodsManagement.CustomerCode, OrderCount=(select count(1) from DsWhGoodsManagement a where CONVERT(varchar(10),a.CreateTime,120)=CONVERT(varchar(10),DsWhGoodsManagement.CreateTime,120) and DsWhGoodsManagement.CustomerCode=a.CustomerCode ) from DsWhGoodsManagement inner join DsWhCustomer on DsWhGoodsManagement.CustomerCode=DsWhCustomer.CusCode ) tab ") .Where(sqlWhere) .QuerySingle(); } }
public override void GetChatBindDataPager(ref Model.Pager <Model.Generated.BCCrWeChatBind> pager) { pager.TotalRows = Context.Sql(@"select count(1) from CrWeChatBind a ") .QuerySingle <int>(); pager.Rows = Context.Select <BCCrWeChatBind>(" CrWeChatBind.*,CrCustomer.Name as AccountName, CrCustomer.Account as AccountCode ") .From(@" CrWeChatBind inner join CrCustomer on CrWeChatBind.AccountSysNo=CrCustomer.SysNo ") .OrderBy("CrWeChatBind.SysNo") .Paging(pager.CurrentPage, pager.PageSize) .QueryMany(); }
/// <summary> /// 网站类型 /// </summary> /// <param name="pager"></param> public override void GetProcurmentWebTypePager(Model.Pager <PmProcurementWebType> pager) { string sqlTable = ""; string sqlSelect = ""; string sqlWhere = ""; var dataList = Context.Select <PmProcurementWebType>(sqlSelect).From(sqlTable).Where(sqlWhere); var dataCount = Context.Select <int>("count(0)").From(sqlTable).Where(sqlWhere); var rows = dataList.OrderBy("SysNo desc").Paging(pager.CurrentPage, pager.PageSize).QueryMany(); var totalRows = dataCount.QuerySingle(); pager.TotalRows = totalRows; pager.Rows = rows; }
public override void GetPmPointsOrderPager(ref Model.Pager <CBPmPointsOrder> pager) { string sqlTable = " PmPointsOrder left join SyUser a on PmPointsOrder.Po_CreateSysNo=a.SysNo left join SyUser b on PmPointsOrder.Po_UpdateSysNo=b.SysNo"; string sqlSelect = " PmPointsOrder.* ,a.UserName as CreateName,b.UserName as UpdateName "; string sqlWhere = ""; var dataList = Context.Select <CBPmPointsOrder>(sqlSelect).From(sqlTable); var dataCount = Context.Select <int>("count(0)").From(sqlTable); var rows = dataList.OrderBy("SysNo desc").Paging(pager.CurrentPage, pager.PageSize).QueryMany(); var totalRows = dataCount.QuerySingle(); pager.TotalRows = totalRows; pager.Rows = rows; }
public override void GetPaymentListDataPager( ref Model.Pager <CBFnReceiptVoucher> pager, int?PaymentTypeSysNo, int?WarehouseSysNo, DateTime?startTime, DateTime?endTime, string sysNoList) { string sqlTable = @" FnReceiptVoucher inner join FnReceiptVoucherItem on FnReceiptVoucher.SysNo=FnReceiptVoucherItem.ReceiptVoucherSysNo inner join SoOrder on SoOrder.SysNo=FnReceiptVoucher.SourceSysNo inner join WhWarehouse on SoOrder.DefaultWarehouseSysNo = WhWarehouse.SysNo inner join BsPaymentType on SoOrder.PayTypeSysNo=BsPaymentType.SysNo left join SyUser on SyUser.SysNo=FnReceiptVoucher.CreatedBy left join SyUser as tabUser2 on tabUser2.SysNo=SoOrder.OrderCreatorSysNo left join CrCustomer as Customer1 on Customer1.SysNo=SoOrder.CustomerSysNo left join SyUser as tabUser on tabUser.SysNo=FnReceiptVoucher.ConfirmedBy "; string sqlSelect = @" SoOrder.SysNo, BsPaymentType.PaymentName ,SoOrder.CreateDate,tabUser2.UserName as CreatorName,Customer1.Name as CustomerName,tabUser.UserName as ConfirmeName, FnReceiptVoucher.IncomeAmount,FnReceiptVoucher.ReceivedAmount,FnReceiptVoucherItem.CreditCardNumber,FnReceiptVoucherItem.VoucherNo, WhWarehouse.WarehouseName "; string sqlWhere = " FnReceiptVoucher.Source=10 and FnReceiptVoucher.status=20 "; if (PaymentTypeSysNo != null && PaymentTypeSysNo.Value > 0) { sqlWhere += " and SoOrder.PayTypeSysNo=" + PaymentTypeSysNo.Value; } if (WarehouseSysNo != null && WarehouseSysNo.Value > 0) { sqlWhere += " and SoOrder.DefaultWarehouseSysNo=" + WarehouseSysNo.Value; } if (startTime != null) { sqlWhere += " and SoOrder.CreateDate>='" + startTime.Value.ToString("yyyy-MM-dd") + " 00:00:00' "; } if (endTime != null) { sqlWhere += " and SoOrder.CreateDate<='" + endTime.Value.ToString("yyyy-MM-dd") + " 23:59:59' "; } if (!string.IsNullOrEmpty(sysNoList)) { sqlWhere += " and SoOrder.SysNo in (" + sysNoList + ") "; } var dataList = Context.Select <CBFnReceiptVoucher>(sqlSelect).From(sqlTable).Where(sqlWhere); var dataCount = Context.Select <int>("count(0)").From(sqlTable).Where(sqlWhere); var rows = dataList.OrderBy(" SourceSysNo desc").Paging(pager.CurrentPage, pager.PageSize).QueryMany(); var totalRows = dataCount.QuerySingle(); pager.TotalRows = totalRows; pager.Rows = rows; }
public override void DoDsWhPackageQuery(Model.Pager <DsWhPackage> pageCusList) { string table = " DsWhPackage "; #region sql条件 string sqlWhere = @" 1=-1 "; #endregion string typeList = pageCusList.PageFilter.CusCode; switch (typeList.Split('_')[0]) { case "ALL": sqlWhere = " 1=1 "; break; case "Dealer": sqlWhere = " DsWhPackage.CusCode = '" + typeList.Split('_')[1] + "' or DsWhCustomer.DsSysNo = '" + typeList.Split('_')[2] + "' "; table = " DsWhPackage left join DsWhCustomer on DsWhPackage.CusCode = DsWhCustomer.CusCode "; break; case "Customer": sqlWhere = " DsWhPackage.CusCode = '" + typeList.Split('_')[1] + "' "; break; } if (!string.IsNullOrEmpty(pageCusList.PageFilter.StatusCode)) { if (!string.IsNullOrEmpty(sqlWhere)) { sqlWhere += " and "; } sqlWhere += " DsWhPackage.StatusCode= '" + pageCusList.PageFilter.StatusCode + "' "; } using (var context = Context.UseSharedConnection(true)) { pageCusList.Rows = context.Select <DsWhPackage>(" * ") .From(table) .Where(sqlWhere) .Paging(pageCusList.CurrentPage, pageCusList.PageSize) .OrderBy("DsWhPackage.SysNo desc") .QueryMany(); pageCusList.TotalRows = context.Select <int>("count(1)") .From(table) .Where(sqlWhere) .QuerySingle(); } }
public override void DsWhTotalWaybillPager(ref Model.Pager <CBDsWhTotalWaybill> pageCusList) { string table = @" DsWhTotalWaybill inner join LgDeliveryType on DsWhTotalWaybill.ServiceType=LgDeliveryType.OverseaCarrier "; string sqlWhere = " 1=1 "; string typeList = pageCusList.PageFilter.CusCode; switch (typeList.Split('_')[0]) { case "ALL": sqlWhere = " 1=1 "; break; case "Dealer": sqlWhere = " ( DsWhTotalWaybill.CusCode = '" + typeList.Split('_')[1] + "' or DsWhCustomer.DsSysNo = '" + typeList.Split('_')[2] + "') "; table = " DsWhTotalWaybill left join DsWhCustomer on DsWhTotalWaybill.CusCode = DsWhCustomer.CusCode inner join LgDeliveryType on DsWhTotalWaybill.ServiceType=LgDeliveryType.OverseaCarrier "; break; case "Customer": sqlWhere = " (DsWhTotalWaybill.CusCode = '" + typeList.Split('_')[1] + "') "; break; } if (!string.IsNullOrEmpty(pageCusList.PageFilter.StatusCode)) { if (sqlWhere != "") { sqlWhere += " and "; } sqlWhere += " DsWhTotalWaybill.StatusCode='" + pageCusList.PageFilter.StatusCode + "' "; } using (var context = Context.UseSharedConnection(true)) { pageCusList.Rows = context.Select <CBDsWhTotalWaybill>(" DsWhTotalWaybill.*,LgDeliveryType.DeliveryTypeName ") .From(table) .Where(sqlWhere) .Paging(pageCusList.CurrentPage, pageCusList.PageSize) .OrderBy(" DsWhTotalWaybill.SysNo desc ") .QueryMany(); pageCusList.TotalRows = context.Select <int>("count(1)") .From(table) .Where(sqlWhere) .QuerySingle(); } }
public override void GetSpSpikeListPager(ref Model.Pager <Model.Promotion.SpSpike> SpSpikePager) { #region sql条件 string sqlWhere = @" "; #endregion using (var context = Context.UseSharedConnection(true)) { SpSpikePager.Rows = context.Select <SpSpike>(" * ") .From(@" SpSpike ") .Paging(SpSpikePager.CurrentPage, SpSpikePager.PageSize) .OrderBy(" SpSpike.CreatedDate ASC ") .QueryMany(); SpSpikePager.TotalRows = context.Select <int>("count(1)") .From(@" SpSpike ") .QuerySingle(); } }
/// <summary> /// 分销团队 /// </summary> /// <param name="type"></param> /// <param name="pager"></param> /// <remarks>2016-07-15 周 创建</remarks> public override void GetMyDistTemsList(int?typeid, ref Model.Pager <CBCCrCustomerList> pager) { string sqlWhere = " 1=1 "; //" c.SysNo<>" + pager.PageFilter.SysNo + " "; switch (typeid) { case 1: sqlWhere += " and c.PSysNo=" + pager.PageFilter.SysNo + ""; break; case 2: sqlWhere += " and c.PSysNo in(select sysno from [CrCustomer] where PSysNo=" + pager.PageFilter.SysNo + ")"; break; case 3: sqlWhere += " and c.PSysNo in(select sysno from [CrCustomer] where PSysNo in(select sysno from [CrCustomer] where PSysNo=" + pager.PageFilter.SysNo + "))"; break; default: sqlWhere += " and c.CustomerSysNos like '%," + pager.PageFilter.SysNo + ",%'"; break; } using (var context = Context.UseSharedConnection(true)) { pager.Rows = context.Select <CBCCrCustomerList>(" c.*,[dbo].[func_GetCrCustomerOrderCount](c.sysno) as OrderNums,[dbo].[func_GetRebagesOrderCount](c.sysno) as RebagesOrderCount ") .From("CrCustomer c") .Where(sqlWhere) //.Parameter("SysNo", pager.PageFilter.SysNo) .Paging(pager.CurrentPage, pager.PageSize) .OrderBy("c.RegisterDate desc") .QueryMany(); pager.TotalRows = context.Select <int>("count(1)") .From("CrCustomer c") .Where(sqlWhere) //.Parameter("SysNo", pager.PageFilter.SysNo) .QuerySingle(); } }
/// <summary> /// 分页数据 /// </summary> /// <param name="pager"></param> public override void GetPmPointsOrderPager(ref Model.Pager <Model.Procurement.CBPurchasePaymentOrder> pager) { string sqlTable = " FnPurchasePaymentOrder "; string sqlSelect = @" *, BankPaymentInfo=stuff(( SELECT distinct '|' + CompanyName+','+PayBankName+','+PayBankIDCard+','+ Convert(varchar(50), PaymentAmount) FROM FnPurchasePaymentOrderItem WHERE FnPurchasePaymentOrderItem.PSysNo = [FnPurchasePaymentOrder].SysNo FOR xml path('') ) , 1, 1, '') "; string sqlWhere = ""; var dataList = Context.Select <CBPurchasePaymentOrder>(sqlSelect).From(sqlTable); var dataCount = Context.Select <int>("count(0)").From(sqlTable); var rows = dataList.OrderBy("SysNo desc").Paging(pager.CurrentPage, pager.PageSize).QueryMany(); var totalRows = dataCount.QuerySingle(); pager.TotalRows = totalRows; pager.Rows = rows; }
public override void GetDsPosMoneyBoxListPagerByDsSysNo(ref Model.Pager <CBDsPosMoneyBox> pager) { #region sql条件 string sqlWhere = @"(DsSysNo=@DsSysNo or " + pager.PageFilter.DsSysNo + " = 0 ) "; #endregion using (var context = Context.UseSharedConnection(true)) { pager.Rows = context.Select <CBDsPosMoneyBox>("DsPosMoneyBox.*,DsPosManage.pos_posName as PosSYName ,DsDealer.DealerName ") .From(" DsPosMoneyBox inner join DsPosManage on DsPosMoneyBox.DsPosSysNo=DsPosManage.SysNo inner join DsDealer on DsPosMoneyBox.DsSysNo=DsDealer.SysNo ") .Where(sqlWhere) .Parameter("DsSysNo", pager.PageFilter.DsSysNo) .Paging(pager.CurrentPage, pager.PageSize) .OrderBy("DsPosMoneyBox.SysNo desc") .QueryMany(); pager.TotalRows = context.Select <int>("count(1)") .From(" DsPosMoneyBox inner join DsPosManage on DsPosMoneyBox.DsPosSysNo=DsPosManage.SysNo inner join DsDealer on DsPosMoneyBox.DsSysNo=DsDealer.SysNo ") .Where(sqlWhere) .Parameter("DsSysNo", pager.PageFilter.DsSysNo) .QuerySingle(); } }
public override void DsPosBarcodePayLogPager(ref Model.Pager <Model.Pos.DsPosBarcodePayLog> pager) { #region sql条件 string sqlWhere = @"(DsSysNo=@DsSysNo or " + pager.PageFilter.DsSysNo + " = 0 ) "; #endregion using (var context = Context.UseSharedConnection(true)) { pager.Rows = context.Select <DsPosBarcodePayLog>("DsPosBarcodePayLog.*") .From(" DsPosBarcodePayLog ") .Where(sqlWhere) .Parameter("DsSysNo", pager.PageFilter.DsSysNo) .Paging(pager.CurrentPage, pager.PageSize) .OrderBy("DsPosBarcodePayLog.SysNo desc") .QueryMany(); pager.TotalRows = context.Select <int>("count(1)") .From(" DsPosBarcodePayLog ") .Where(sqlWhere) .Parameter("DsSysNo", pager.PageFilter.DsSysNo) .QuerySingle(); } }
public override void DoDsWhCustomerQuery(ref Model.Pager <CBDsWhCustomer> pageCusList) { #region sql条件 string sqlWhere = @" "; #endregion if (pageCusList.PageFilter.IsAllDealer) { sqlWhere = " 1=1 "; } else if (pageCusList.PageFilter.IsDealer) { sqlWhere = " DsWhCustomer.DsSysNo = '" + pageCusList.PageFilter.DsSysNo + "' "; } else if (pageCusList.PageFilter.IsCustomer) { sqlWhere = " DsWhCustomer.SysNo = '" + pageCusList.PageFilter.SysNo + "' "; } using (var context = Context.UseSharedConnection(true)) { pageCusList.Rows = context.Select <CBDsWhCustomer>(" DsWhCustomer.*, DsDealer.DealerName as DsName , DsDealer.ErpCode as DsCode , (a.AreaName+' '+b.AreaName+' '+c.AreaName) as CountryName ") .From(@" DsWhCustomer inner join DsDealer on DsWhCustomer.DsSysNo=DsDealer.SysNo inner join BsArea c on DsWhCustomer.CusCountryCode=c.SysNo inner join BsArea b on c.ParentSysNo=b.SysNo inner join BsArea a on b.ParentSysNo=a.SysNo ") .Where(sqlWhere) .Paging(pageCusList.CurrentPage, pageCusList.PageSize) .OrderBy("DsWhCustomer.SysNo desc") .QueryMany(); pageCusList.TotalRows = context.Select <int>("count(1)") .From(@" DsWhCustomer inner join DsDealer on DsWhCustomer.DsSysNo=DsDealer.SysNo inner join BsArea c on DsWhCustomer.CusCountryCode=c.SysNo inner join BsArea b on c.ParentSysNo=b.SysNo inner join BsArea a on b.ParentSysNo=a.SysNo ") .Where(sqlWhere) .QuerySingle(); } }
/// <summary> /// 获取订单日志分页数据 /// </summary> /// <param name="transactionSysNo">事物编号</param> /// <param name="pager">分页设置</param> /// <returns>订单日志分页数据</returns> /// <remarks>2013-06-20 朱成果 创建</remarks> public override void GetPageData(string transactionSysNo, ref Model.Pager <Model.SoTransactionLog> pager) { #region Sql 获取订单日志总数 const string sql = @" select count(1) from SOTRANSACTIONLOG where TransactionSysNo=@0 "; #endregion pager.TotalRows = Context.Sql(sql, transactionSysNo) .QuerySingle <int>(); var list = Context.Select <Model.SoTransactionLog>("p.*") .From("SOTRANSACTIONLOG p") .Where("TransactionSysNo=@TransactionSysNo") .OrderBy("OperateDate asc,p.sysno asc") .Paging(pager.CurrentPage, pager.PageSize) .Parameter("TransactionSysNo", transactionSysNo) .QueryMany(); pager.Rows = list; }
/// <summary> /// 分页 /// </summary> /// <param name="pager"></param> public override void GetMembershipCardListByPager(ref Model.Pager <CBDsMembershipCard> pager) { #region sql条件 string sqlWhere = @"( DsMembershipCard.DsSysNo=@DsSysNo or " + pager.PageFilter.DsSysNo + " = 0 ) "; #endregion using (var context = Context.UseSharedConnection(true)) { pager.Rows = context .Select <CBDsMembershipCard>("DsMembershipCard.*,DsDealer.DealerName as DealerName ") .From(" DsMembershipCard inner join DsDealer on DsDealer.SysNo=DsMembershipCard.DsSysNo ") .Where(sqlWhere) .Parameter("DsSysNo", pager.PageFilter.DsSysNo) .Paging(pager.CurrentPage, pager.PageSize) .OrderBy("DsMembershipCard.SysNo desc") .QueryMany(); pager.TotalRows = context.Select <int>("count(1)") .From(" DsMembershipCard inner join DsDealer on DsDealer.SysNo=DsMembershipCard.DsSysNo ") .Where(sqlWhere) .Parameter("DsSysNo", pager.PageFilter.DsSysNo) .QuerySingle(); } }
/// <summary> /// 分页显示 /// </summary> /// <param name="pager"></param> public override void GetWhWarehouseMonthStockHistoryPager(ref Model.Pager <Model.Procurement.CBWhWarehouseMonthStockHistory> pager) { string sqlTable = "( select distinct ProductSysNo,WarehouseSysNo from WhWarehouseMonthStockHistory where WarehouseSysNo = '" + pager.PageFilter.WarehouseSysNo + "' and WhYear='" + pager.PageFilter.WhYear + "' ) as tab"; string sqlSelect = @" ProductSysNo "; string sqlWhere = ""; var dataList = Context.Select <CBWhWarehouseMonthStockHistory>(sqlSelect).From(sqlTable).Where(sqlWhere); var dataCount = Context.Select <int>("count(0)").From(sqlTable).Where(sqlWhere); var rows = dataList.OrderBy("ProductSysNo desc").Paging(pager.CurrentPage, pager.PageSize).QueryMany(); var totalRows = dataCount.QuerySingle(); pager.TotalRows = totalRows; pager.Rows = rows; if (rows.Count > 0) { string strList = ""; foreach (CBWhWarehouseMonthStockHistory mod in rows) { if (!string.IsNullOrEmpty(strList)) { strList += ","; } strList += mod.ProductSysNo; } string sql = " select WhWarehouseMonthStockHistory.*," + " PdProduct.ErpCode,PdProduct.EasName,'' as Spec,PdProduct.SpecUnit " + " from WhWarehouseMonthStockHistory inner join PdProduct on "; sql += " WhWarehouseMonthStockHistory.ProductSysNo=PdProduct.SysNo "; sql += " where ProductSysNo in (" + strList + ") and WhYear='" + pager.PageFilter.WhYear + "' and WarehouseSysNo='" + pager.PageFilter.WarehouseSysNo + "' order by ProductSysNo "; pager.Rows = Context.Sql(sql).QueryMany <CBWhWarehouseMonthStockHistory>(); } }
/// <summary> /// 获取等级日志 /// </summary> /// <param name="pager">分页查询条件</param> /// <returns>等级日志</returns> /// <remarks>2013-07-10 黄波 创建</remarks> /// <remarks>2013-07-15 苟治国 修改</remarks> public override void GetLevelLog(ref Model.Pager <CBCrLevelLog> pager) { #region 原Sql /* * select * cll.*, * (select LevelName from CrCustomerLevel ccl where ccl.sysno=cll.oldlevelsysno) as oldLevelName, * (select LevelName from CrCustomerLevel ccl where ccl.sysno=cll.newlevelsysno) as newLevelName, * su.UserName * from CrLevelLog cll left join SyUser su on cll.CreatedBy=su.sysno * */ #endregion #region sql条件 string sqlWhere = @"(@customersysno is null or cll.customersysno=@customersysno) and (@changetype=-1 or cll.changetype=@changetype)"; #endregion using (var context = Context.UseSharedConnection(true)) { pager.Rows = context.Select <CBCrLevelLog>("cll.*,(select LevelName from CrCustomerLevel ccl where ccl.sysno=cll.oldlevelsysno) as oldLevelName,(select LevelName from CrCustomerLevel ccl where ccl.sysno=cll.newlevelsysno) as newLevelName,su.UserName") .From("CrLevelLog cll left join SyUser su on cll.CreatedBy=su.sysno") .Where(sqlWhere) .Parameter("customersysno", pager.PageFilter.CustomerSysNo) .Parameter("ChangeType", pager.PageFilter.ChangeType) .Paging(pager.CurrentPage, pager.PageSize) .OrderBy("cll.SysNo desc") .QueryMany(); pager.TotalRows = context.Select <int>("count(1)") .From("CrLevelLog cll left join SyUser su on cll.customersysno=su.sysno") .Where(sqlWhere) .Parameter("customersysno", pager.PageFilter.CustomerSysNo) .Parameter("ChangeType", pager.PageFilter.ChangeType) .QuerySingle(); } }
public override void GetPmGoodsDeliveryPager(ref Model.Pager <CBPmGoodsDelivery> pager) { #region sql条件 string sqlWhere = @""; if (pager.PageFilter.gd_Type == 2) { sqlWhere = " gd_PSysNo='0' "; } #endregion using (var context = Context.UseSharedConnection(true)) { pager.Rows = context.Select <CBPmGoodsDelivery>(" PmGoodsDelivery.*,SyUser.UserName as CurrentName ") .From(" PmGoodsDelivery left join SyUser on PmGoodsDelivery.gd_DeliveryUserSys=SyUser.SysNo ") .Where(sqlWhere) .Paging(pager.CurrentPage, pager.PageSize) .OrderBy("SysNo desc") .QueryMany(); pager.TotalRows = context.Select <int>("count(1)") .From(" PmGoodsDelivery left join SyUser on PmGoodsDelivery.gd_DeliveryUserSys=SyUser.SysNo ") .Where(sqlWhere) .QuerySingle(); } }