/// <summary>
/// 业务员库存查询
/// </summary>
/// <param name="filter">查询参数</param>
/// <returns>业务员库存</returns>
/// <remarks>2013-12-11 周唐炬 创建</remarks>
public PagedList <WhProductLendItem> GetInventoryProductList(ParaProductLendFilter filter)
{
PagedList <WhProductLendItem> pageList = null;
if (null != filter)
{
if (!filter.WarehouseSysNo.HasValue)
{
throw new HytException("必须指定仓库!");
}
if (!filter.DeliveryUserSysNo.HasValue)
{
throw new HytException("必须指定业务员!");
}
filter.Status = WarehouseStatus.借货单状态.已出库.GetHashCode();
pageList = new PagedList <WhProductLendItem>();
filter.PageSize = pageList.PageSize;
var pager = IProductLendDao.Instance.GetInventoryProductList(filter);
if (pager != null)
{
pageList.TData = pager.Rows;
pageList.TotalItemCount = pager.TotalRows;
pageList.CurrentPageIndex = filter.CurrentPage;
}
}
return(pageList);
}
/// <summary>
/// 业务员库存查询
/// </summary>
/// <param name="filter">查询参数</param>
/// <returns>业务员库存</returns>
/// <remarks>2013-12-11 周唐炬 创建</remarks>
public override Pager <WhProductLendItem> GetInventoryProductList(ParaProductLendFilter filter)
{
var pager = new Pager <WhProductLendItem>();
const string sql = @"(select b.productsysno,
b.productname,
sum(b.lendquantity) as lendquantity,
sum(b.salequantity) as salequantity,
sum(b.returnquantity) as returnquantity
from whproductlend a
inner join whproductlenditem b
on b.productlendsysno = a.sysno
where (@0 is null or a.warehousesysno = @0)
and a.deliveryusersysno = @1
and a.status = @2
and b.lendquantity > (b.salequantity + b.returnquantity + b.forcecompletequantity)
group by b.productsysno, b.productname) tb";
var dataList = Context.Select <WhProductLendItem>("tb.*").From(sql);
var dataCount = Context.Select <int>("COUNT(1)").From(sql);
var paras = new object[]
{
filter.WarehouseSysNo,
filter.DeliveryUserSysNo,
filter.Status
};
dataList.Parameters(paras);
dataCount.Parameters(paras);
pager.Rows = dataList.OrderBy("tb.productsysno").Paging(filter.CurrentPage, filter.PageSize).QueryMany();
pager.TotalRows = dataCount.QuerySingle();
return(pager);
}
/// <summary>
/// 获取借货单列表
/// </summary>
/// <param name="filter">过滤条件</param>
/// <returns>借货单列表</returns>
/// <remarks>2013-07-09 周唐炬 创建</remarks>
public override Pager <WhProductLend> GetWhProductLendList(ParaProductLendFilter filter)
{
var pager = new Pager <WhProductLend>();
const string sql = @"(SELECT W.*
FROM WHPRODUCTLEND W
WHERE (@0 IS NULL OR EXISTS
(SELECT 1
FROM splitstr(@0, ',') tmp
WHERE tmp.col = W.Warehousesysno))
AND (@1 IS NULL OR W.Status=@1)
AND (@2 IS NULL OR W.DeliveryUserSysNo=@2)
AND (@3 IS NULL OR W.CreatedBy=@3)
AND (@4 IS NULL OR CONVERT(varchar(10),W.CreatedDate,120) = CONVERT(varchar(10),@4,120))
AND (@5 IS NULL OR W.StockOutBy=@5)
AND (@4 IS NULL OR CONVERT(varchar(10),W.CreatedDate,120) = CONVERT(varchar(10),@4,120))
AND (@7 IS NULL OR W.LastUpdateBy=@7)
AND (@4 IS NULL OR CONVERT(varchar(10),W.CreatedDate,120) = CONVERT(varchar(10),@4,120))
) tb";
var dataList = Context.Select <WhProductLend>("tb.*").From(sql);
var dataCount = Context.Select <int>("COUNT(1)").From(sql);
var warehouseSysNoList = string.Empty;
if (filter.WarehouseSysNoList != null && filter.WarehouseSysNoList.Any())
{
warehouseSysNoList = string.Join(",", filter.WarehouseSysNoList);
}
var paras = new object[]
{
warehouseSysNoList,
filter.Status,
filter.DeliveryUserSysNo,
filter.CreatedBy,
filter.CreatedDate,
filter.StockOutBy,
filter.StockOutDate,
filter.LastUpdateBy,
filter.LastUpdateDate
};
dataList.Parameters(paras);
dataCount.Parameters(paras);
pager.Rows = dataList.OrderBy("tb.SysNO desc").Paging(filter.CurrentPage, filter.PageSize).QueryMany();
pager.TotalRows = dataCount.QuerySingle();
return(pager);
}
/// <summary>
/// 借货单列表导出Excel
/// </summary>
/// <param name="filter">过滤条件</param>
/// <returns>借货单列表</returns>
/// <remarks>2013-07-09 周唐炬 创建</remarks>
public override List <CBWhProductLend> WhProductLendExportExcel(ParaProductLendFilter filter)
{
const string sql = @"(SELECT W.SysNo,W.IsEnforceAllow,W.Amount,W.Status,W.Remarks,W.CreatedDate,W.StockOutDate,W.LastUpdateDate,
A.WAREHOUSENAME AS WarehouseName,
B.USERNAME AS CreatedByName,
C.USERNAME AS LastUpdatedByName,
D.USERNAME AS StockOutByName,
E.USERNAME AS DeliveryUserName
FROM WHPRODUCTLEND W
INNER JOIN WhWarehouse A ON A.SysNo=W.WarehouseSysNo
LEFT JOIN SYUSER B ON B.SYSNO=W.CREATEDBY
LEFT JOIN SYUSER C ON C.SYSNO=W.LASTUPDATEBY
LEFT JOIN SYUSER D ON D.SYSNO=W.StockOutBy
LEFT JOIN SYUSER E ON E.SYSNO=W.DeliveryUserSysNo
WHERE (@0 IS NULL OR EXISTS
(SELECT 1
FROM splitstr(@0, ',') tmp
WHERE tmp.col = W.Warehousesysno))
AND (@1 IS NULL OR W.Status=@1)
AND (@2 IS NULL OR W.DeliveryUserSysNo=@2)
AND (@3 IS NULL OR W.CreatedBy=@3)
AND (@4 IS NULL OR Convert(nvarchar(10),W.CreatedDate,120) = Convert(nvarchar(10),@4,120))
AND (@5 IS NULL OR W.StockOutBy=@5)
AND (@6 IS NULL OR Convert(nvarchar(10),W.CreatedDate,120) = Convert(nvarchar(10),@6,120))
AND (@7 IS NULL OR W.LastUpdateBy=@7)
AND (@8 IS NULL OR Convert(nvarchar(10),W.CreatedDate,120) = Convert(nvarchar(10),@8,120))
) tb";
var dataList = Context.Select <CBWhProductLend>("tb.*").From(sql);
var warehouseSysNoList = string.Join(",", filter.WarehouseSysNoList);
var paras = new object[]
{
warehouseSysNoList,
filter.Status,
filter.DeliveryUserSysNo,
filter.CreatedBy,
filter.CreatedDate,
filter.StockOutBy,
filter.StockOutDate,
filter.LastUpdateBy,
filter.LastUpdateDate
};
dataList.Parameters(paras);
var list = dataList.OrderBy("tb.SysNO desc").Paging(filter.CurrentPage, filter.PageSize).QueryMany();
return(list);
}
/// <summary>
/// 获取借货单列表
/// </summary>
/// <param name="filter">过滤条件</param>
/// <returns>借货单列表</returns>
/// <remarks>2013-07-09 周唐炬 创建</remarks>
public PagedList <WhProductLend> GetProductLendList(ParaProductLendFilter filter)
{
PagedList <WhProductLend> pageList = null;
if (null != filter)
{
pageList = new PagedList <WhProductLend>();
var pager = IProductLendDao.Instance.GetWhProductLendList(filter);
if (pager != null)
{
pageList.TData = pager.Rows;
pageList.TotalItemCount = pager.TotalRows;
pageList.CurrentPageIndex = filter.CurrentPage;
pageList.PageSize = filter.PageSize;
}
}
return(pageList);
}
/// <summary>
/// 获取借货单列表
/// </summary>
/// <param name="filter">过滤条件</param>
/// <returns>借货单列表</returns>
/// <remarks>2013-07-09 周唐炬 创建</remarks>
public List <CBWhProductLend> WhProductLendExportExcel(ParaProductLendFilter filter)
{
return(IProductLendDao.Instance.WhProductLendExportExcel(filter));
}
/// <summary> /// 借货单列表导出Excel /// </summary> /// <param name="filter">过滤条件</param> /// <returns>借货单列表</returns> /// <remarks>2013-07-09 周唐炬 创建</remarks> public abstract List <CBWhProductLend> WhProductLendExportExcel(ParaProductLendFilter filter);
/// <summary> /// 获取借货单列表 /// </summary> /// <param name="filter">过滤条件</param> /// <returns>借货单列表</returns> /// <remarks>2013-07-09 周唐炬 创建</remarks> public abstract Pager <WhProductLend> GetWhProductLendList(ParaProductLendFilter filter);
/// <summary> /// 获取业务员库存 /// </summary> /// <param name="filter">过滤条件</param> /// <returns>业务员库存</returns> /// <remarks>2013-12-11 周唐炬 创建</remarks> public abstract Pager <WhProductLendItem> GetInventoryProductList(ParaProductLendFilter filter);
