/// <summary>
/// 查询取件单
/// </summary>
/// <param name="filter">查询条件实体</param>
/// <returns>返回取件单列表</returns>
/// <remarks>2013-08-12 周唐炬 创建</remarks>
public override Pager <LgPickUp> GetPickUpList(ParaPickUpFilter filter)
{
#region 原调试SQL
//(SELECT A.*,
// B.WAREHOUSENAME AS WarehouseName,
// C.Streetaddress as StreetAddress,
// D.PICKUPTYPENAME AS PiceupTypeName
// FROM LGPICKUP A
// LEFT JOIN WhWarehouse B
// ON B.SysNo = A.WarehouseSysNo
// LEFT JOIN SORECEIVEADDRESS C
// ON C.SYSNO = A.PICKUPADDRESSSYSNO
// LEFT JOIN LgPickupType D
// ON D.SYSNO=A.PICKUPTYPESYSNO
// WHERE 1=1
// AND (:WarehouseSysNoList IS NULL OR EXISTS
// (SELECT 1
// FROM table(splitstr(:WarehouseSysNoList, ',')) tmp
// WHERE tmp.column_value = A.WarehouseSysNo))
// AND (:Status IS NULL OR A.Status=:Status)
// AND (:PickupTypeSysNo IS NULL OR A.PickupTypeSysNo=:PickupTypeSysNo)
// ) tb
#endregion
const string sql = @"(SELECT A.*
FROM LGPICKUP A
WHERE 1=1
AND (@0 IS NULL OR EXISTS
(SELECT 1
FROM splitstr(@0, ',') tmp
WHERE tmp.col = A.WarehouseSysNo))
AND (@1 IS NULL OR A.Status=@1)
AND (@2 IS NULL OR A.PickupTypeSysNo=@2)
AND (@3 IS NULL OR A.SysNo=@3 OR A.StockInSysNo=@3)
) tb";
var dataList = Context.Select <LgPickUp>("tb.*").From(sql);
var dataCount = Context.Select <int>("count(1)").From(sql);
var warehouseSysNoList = string.Empty;
if (null != filter.WarehouseSysNoList)
{
warehouseSysNoList = string.Join(",", filter.WarehouseSysNoList);
}
var paras = new object[]
{
warehouseSysNoList,
filter.Status,
filter.PickupTypeSysNo,
filter.SysNo
};
dataList.Parameters(paras);
dataCount.Parameters(paras);
var pager = new Pager <LgPickUp>
{
Rows = dataList.OrderBy(@"tb.SysNo desc").Paging(filter.CurrentPage, filter.PageSize).QueryMany(),
TotalRows = dataCount.QuerySingle()
};
return(pager);
}
/// <summary>
/// 查询取件单
/// </summary>
/// <param name="filter">查询条件实体</param>
/// <returns>返回取件单列表</returns>
/// <remarks>2013-08-12 周唐炬 创建</remarks>
public PagedList <LgPickUp> GetPickUpList(ParaPickUpFilter filter)
{
PagedList <LgPickUp> model = null;
if (filter != null)
{
model = new PagedList <LgPickUp>();
var pager = ILgPickUpDao.Instance.GetPickUpList(filter);
if (null != pager)
{
model.TData = pager.Rows;
model.TotalItemCount = pager.TotalRows;
model.CurrentPageIndex = filter.CurrentPage;
}
}
return(model);
}
/// <summary>
/// 查询取件单
/// </summary>
/// <param name="pager">分页对象</param>
/// <param name="filter">查询条件实体</param>
/// <remarks>
/// 2013-07-05 郑荣华 创建
/// </remarks>
public override void GetPickUpList(ref Pager <CBLgPickUp> pager, ParaPickUpFilter filter)
{
using (var context = Context.UseSharedConnection(true))
{
#region sqlcount
const string sqlCount = @"
select count(1) from lgpickup t
where (@warehousesysno is null or warehousesysno=@warehousesysno)
and (@status is null or status=@status)
and (@SysNoFilter is null or not exists(select 1 from splitstr(@SysNoFilter,',') z where z.col = t.sysno))
";
#endregion
pager.TotalRows = context.Sql(sqlCount)
.Parameter("warehousesysno", filter.WarehouseSysNo)
.Parameter("status", filter.Status)
.Parameter("SysNoFilter", filter.SysNoFilter)
.QuerySingle <int>();
const string sqlFrom = @"
lgpickup t left join Soreceiveaddress b
on t.pickupAddressSysNo=b.sysno
";
const string sqlWhere = @"
(@warehousesysno is null or t.warehousesysno=@warehousesysno)
and (@status is null or t.status=@status)
and (@SysNoFilter is null or not exists(select 1 from splitstr(@SysNoFilter,',') z where z.col = t.sysno))
";
pager.Rows = context.Select <CBLgPickUp>("t.*,b.mobilephonenumber,b.streetaddress,b.phoneNumber")
.From(sqlFrom)
.Where(sqlWhere)
.Parameter("warehousesysno", filter.WarehouseSysNo)
.Parameter("status", filter.Status)
.Parameter("SysNoFilter", filter.SysNoFilter)
.OrderBy("t.sysno desc")
.Paging(pager.CurrentPage, pager.PageSize)
.QueryMany();
}
}
/// <summary> /// 查询取件单 /// </summary> /// <param name="pager">分页对象</param> /// <param name="filter">查询条件实体</param> /// <returns></returns> /// <remarks> /// 2013-07-04 郑荣华 创建 /// </remarks> public abstract void GetPickUpList(ref Pager <CBLgPickUp> pager, ParaPickUpFilter filter);
/// <summary> /// 查询取件单 /// </summary> /// <param name="filter">查询条件实体</param> /// <returns>返回取件单列表</returns> /// <remarks>2013-08-12 周唐炬 创建</remarks> public abstract Pager <LgPickUp> GetPickUpList(ParaPickUpFilter filter);
/// <summary>
/// 查询取件单
/// </summary>
/// <param name="pager">分页对象</param>
/// <param name="filter">查询条件实体</param>
/// <returns></returns>
/// <remarks>
/// 2013-07-04 郑荣华 创建
/// </remarks>
public void GetPickUpList(ref Pager <CBLgPickUp> pager, ParaPickUpFilter filter)
{
ILgPickUpDao.Instance.GetPickUpList(ref pager, filter);
}
