/// <summary>
/// 查分销商绑定库存商品列表的分页数据列表
/// </summary>
/// <param name="filter"></param>
/// <returns></returns>
///<remarks>
/// 2016-03-14 杨云奕 添加
/// </remarks>
public override Pager <PdProductStockList> DoDealerPdProductStockDetailQuery(ParaProductStockFilter filter)
{
string sql = @"(select
PdProductStock.* , PdProduct.ErpCode , PdProduct.EasName ,DsDealer.DealerName as BackWarehouseName
from PdProduct
inner join PdProductStock on PdProduct.SysNo=PdProductStock.PdProductSysNo
inner join DsDealerWharehouse on DsDealerWharehouse.WarehouseSysNo=PdProductStock.WarehouseSysNo
inner join DsDealer on DsDealer.SysNo=DsDealerWharehouse.DealerSysNo ";
if (filter.DsDealerSysNo > 0)
{
sql += " where DsDealer.SysNo = " + filter.DsDealerSysNo;
}
sql += " ) tb ";
var dataList = Context.Select <PdProductStockList>("tb.*").From(sql);
var dataCount = Context.Select <int>("count(1)").From(sql);
var pager = new Pager <PdProductStockList>
{
CurrentPage = filter.Id,
PageSize = filter.PageSize
};
var totalRows = dataCount.QuerySingle();
var rows = dataList.OrderBy("tb.LastUpdateDate desc").Paging(pager.CurrentPage, pager.PageSize).QueryMany();
pager.TotalRows = totalRows;
pager.Rows = rows;
return(pager);
}
/// <summary> /// 获取运费模板列表 /// </summary> /// <param name="sysNo">运费模板系统编号</param> /// <returns>运费模板列表</returns> /// <remarks>2015-08-06 王耀发 创建</remarks> public abstract Pager <CBPdProductStockList> GetPdProductStockList(ParaProductStockFilter filter);
/// <summary> /// 查分销商绑定库存商品列表的分页数据列表 /// </summary> /// <param name="filter"></param> /// <returns></returns> ///<remarks> /// 2016-03-14 杨云奕 添加 /// </remarks> public abstract Pager <PdProductStockList> DoDealerPdProductStockDetailQuery(ParaProductStockFilter filter);
/// <summary>
/// 库存信息
/// </summary>
/// <param name="filter">库存信息</param>
/// <returns>返回库存信息</returns>
/// <remarks>2015-08-27 王耀发 创建</remarks>
public override Pager <CBPdProductStockList> GetPdProductStockList(ParaProductStockFilter filter)
{
Decimal typeFrom_StockQuantity = 0;
Decimal typeTo_StockQuantity = 0;
if (!string.IsNullOrWhiteSpace(filter.From_StockQuantity) && !Decimal.TryParse(filter.From_StockQuantity, out typeFrom_StockQuantity))
{
filter.From_StockQuantity = "0";
}
if (!string.IsNullOrWhiteSpace(filter.To_StockQuantity) && !Decimal.TryParse(filter.To_StockQuantity, out typeTo_StockQuantity))
{
filter.To_StockQuantity = "0";
}
string where = "WHERE 1=1";
if (filter.Status != "全部")
{
where += " AND Status = '" + filter.Status + "'";
}
if (!string.IsNullOrWhiteSpace(filter.ProductSysNos))
{
where += " AND ProductSysNo in(" + filter.ProductSysNos + ")";
}
// string sql = @"(select p.SysNo as ProductSysNo,p.ErpCode,p.EasName,s.*,w.BackWarehouseName as BackWarehouseName,w.StreetAddress,case when s.sysno is null then '未入库' else '已入库' end as Status, PdPrice.Price
//
// " + (filter.ProductCategory > 0?",PdCategory.SysNos as PdCategorySysNos ":" ")+@"
// from PdProduct p
// "+(filter.ProductCategory > 0?@"
//
// inner join PdCategoryAssociation on PdCategoryAssociation.ProductSysNo=p.SysNo and PdCategoryAssociation.IsMaster = 1
// inner join PdCategory on PdCategoryAssociation.CategorySysNo=PdCategory.SysNo ":"")+ @"
// left join (select * from PdProductStock where WarehouseSysNo = @WarehouseSysNo ) as s on p.SysNo = s.PdProductSysNo
// left join WhWarehouse w on s.WarehouseSysNo = w.SysNo left join PdPrice on PdPrice.ProductSysNo=p.SysNo and PriceSource=10 and SourceSysNo=1 ";
string sql = @"(select p.SysNo as ProductSysNo,p.ErpCode,p.EasName,s.*,w.BackWarehouseName as BackWarehouseName,w.StreetAddress,case when s.sysno is null then '未入库' else '已入库' end as Status, 0 as Price
,PdCategory.SysNos as PdCategorySysNos
from PdProduct p
inner join PdCategoryAssociation on PdCategoryAssociation.ProductSysNo=p.SysNo and PdCategoryAssociation.IsMaster = 1
inner join PdCategory on PdCategoryAssociation.CategorySysNo=PdCategory.SysNo
left join (select * from PdProductStock where WarehouseSysNo = @WarehouseSysNo ) as s on p.SysNo = s.PdProductSysNo
left join WhWarehouse w on s.WarehouseSysNo = w.SysNo ";
if (filter.WhPositionSysNo > 0)
{
sql += @" INNER JOIN WhProductWarehousePositionAssociation WPA ON s.SysNo=WPA.ProductStockSysNo
INNER JOIN WhWarehousePosition WP ON WPA.WarehousePositionSysNo=WP.SysNo ";
}
sql += @" where p.[Status] <> 2 and
(@ErpCode is null or p.ErpCode like @ErpCode) and
(@Barcode is null or p.Barcode like @Barcode) and
(@EasName is null or p.EasName like @EasName) and
(@From_StockQuantity is null or s.StockQuantity >= @From_StockQuantity) and
(@To_StockQuantity is null or s.StockQuantity <= @To_StockQuantity) ";
if (!string.IsNullOrWhiteSpace(filter.KeyWord))
{
sql += "and (p.ErpCode like @KeyWord or p.Barcode like @KeyWord or p.EasName like @KeyWord or p.ProductName like @KeyWord)";
}
if (filter.WhPositionSysNo > 0)
{
sql += " AND WP.SysNo=" + filter.WhPositionSysNo;
}
if (filter.ProductCategory > 0)
{
sql += " AND PdCategory.SysNos like '%," + filter.ProductCategory + ",%'";
}
if (filter.DsDealerSysNo > 0)
{
sql += " AND p.DealerSysNo=" + filter.DsDealerSysNo;
}
sql += " ) tb " + where;
var dataList = Context.Select <CBPdProductStockList>("tb.*").From(sql)
.Parameter("WarehouseSysNo", filter.WarehouseSysNo)
.Parameter("ErpCode", "%" + filter.ErpCode + "%")
.Parameter("Barcode", "%" + filter.Barcode + "%")
.Parameter("EasName", "%" + filter.EasName + "%")
.Parameter("From_StockQuantity", filter.From_StockQuantity)
.Parameter("To_StockQuantity", filter.To_StockQuantity)
.Parameter("KeyWord", "%" + filter.KeyWord + "%");
var dataCount = Context.Select <int>("count(1)").From(sql)
.Parameter("WarehouseSysNo", filter.WarehouseSysNo)
.Parameter("ErpCode", "%" + filter.ErpCode + "%")
.Parameter("Barcode", "%" + filter.Barcode + "%")
.Parameter("EasName", "%" + filter.EasName + "%")
.Parameter("From_StockQuantity", filter.From_StockQuantity)
.Parameter("To_StockQuantity", filter.To_StockQuantity)
.Parameter("KeyWord", "%" + filter.KeyWord + "%");
var pager = new Pager <CBPdProductStockList>
{
CurrentPage = filter.Id,
PageSize = filter.PageSize
};
var totalRows = dataCount.QuerySingle();
var rows = dataList.OrderBy("tb.LastUpdateDate desc").Paging(pager.CurrentPage, pager.PageSize).QueryMany();
pager.TotalRows = totalRows;
pager.Rows = rows;
return(pager);
}
