/// <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();
}
}
