/// <summary> /// 查找/高级查找 /// </summary> /// <returns>结算单主表实体</returns> /// <param name="currPageIndex">当前pageIndex</param> /// <param name="warehouses">当前登录人员有权限的仓库</param> /// <param name="searchParas">高级查询参数集合</param> /// <remarks>2013-06-15 黄伟 创建</remarks> public abstract Pager <CBLgSettlement> Search(int?currPageIndex, ParaLogisticsLgsettlement searchParas, List <int> warehouses);
/// <summary>
/// 查找/高级查找
/// </summary>
/// <param name="currPageIndex">当前pageIndex</param>
/// <param name="searchParas">高级查询参数集合</param>
/// <param name="warehouses">当前登录人员有权限的仓库</param>
/// <returns>结算单主表实体</returns>
/// <remarks>2013-06-15 黄伟 创建</remarks>
public override Pager <CBLgSettlement> Search(int?currPageIndex, ParaLogisticsLgsettlement searchParas, List <int> warehouses)
{
var userSysNos = searchParas.CreatedByAdv == null ? null : ISyUserDao.Instance.GetSyUsersByName(searchParas.CreatedByAdv).Select(p => p.SysNo).ToList();
var strUsers = "";
if (userSysNos != null && userSysNos.Any())
{
strUsers = "and M.CreatedBy in(" + string.Join(",", userSysNos) + ")";
}
else
{
if (searchParas.CreatedByAdv != null)
{
strUsers = "and M.CreatedBy in(-1)";
}
}
#region 没有仓库权限
if (warehouses == null || !warehouses.Any())
{
return(new Pager <CBLgSettlement>
{
Rows = null,
TotalRows = 0
});
}
#endregion
var sqlFrom = "lgsettlement m";
var strWhere =
@" 1=1
and M.WAREHOUSESYSNO in({0}) --in(@param) in list not supported in context.select.where--huangwei
and (@STOCKOUTSYSNO is null or exists(select * from LgSettlementItem d where d.settlementsysno=m.sysno and stockoutsysno=@STOCKOUTSYSNO) )
--and (@WAREHOUSESYSNO is null or exists(select * from LgSettlementItem d where d.settlementsysno=m.sysno and exists(select * from whstockout where Sysno=d.stockoutsysno and --WAREHOUSESYSNO=@WAREHOUSESYSNO)))
and (@WAREHOUSESYSNO is null or WAREHOUSESYSNO=@WAREHOUSESYSNO)--the col has been added into table lgsettlement,so use warehousesysno in lgsettlement not from others as before
and (@settleSysNo is null or M.sysNo=@settleSysNo)
and (@Deliveryusersysno is null or M.Deliveryusersysno=@Deliveryusersysno)
{1}
and (@Status is null or M.Status=@Status)
and ((@beginDate is null or M.CreatedDate >= @beginDate)
and (@endDate is null or M.CreatedDate < @endDate))";
strWhere = string.Format(strWhere, string.Join(",", warehouses), strUsers);
var pagerResult = new Pager <CBLgSettlement> {
PageSize = PageSize, CurrentPage = currPageIndex ?? 1
};
using (var context = Context.UseSharedConnection(true))
{
pagerResult.TotalRows = context.Select <int>(
@"count(0)")
.From(sqlFrom)
.AndWhere(strWhere)
.Parameter("STOCKOUTSYSNO", searchParas.DoSysNoAdv) //出库单系统编号
.Parameter("WAREHOUSESYSNO", searchParas.TxtWareHouse) //仓库系统编号
.Parameter("settleSysNo", searchParas.SettleSysNoAdv) //结算单系统编号
.Parameter("Deliveryusersysno", searchParas.SelDelManAdv) //用户系统编号
//.Parameter("Createdby", searchParas.CreatedByAdv) //创建人
//.Parameter("Createdby", searchParas.CreatedByAdv) //创建人
.Parameter("Status", searchParas.SelStatusAdv) //状态
.Parameter("beginDate", searchParas.BeginDateAdv) //开始日期
.Parameter("endDate", searchParas.EndDateAdv) //结束日期
.QuerySingle();
pagerResult.Rows = context.Select <CBLgSettlement>(
@"m.SysNo,m.Deliveryusersysno,m.WAREHOUSESYSNO,
m.Createdby,m.Totalamount,
--(select sum(payamount) from LgSettlementItem where settlementsysno=m.sysno) as PaidAmount,
m.PaidAmount,
m.status,m.createddate")
.From(sqlFrom)
.AndWhere(strWhere)
.Parameter("STOCKOUTSYSNO", searchParas.DoSysNoAdv) //出库单系统编号
.Parameter("WAREHOUSESYSNO", searchParas.TxtWareHouse) //仓库系统编号
.Parameter("settleSysNo", searchParas.SettleSysNoAdv) //结算单系统编号
.Parameter("Deliveryusersysno", searchParas.SelDelManAdv) //用户系统编号
//.Parameter("Createdby", searchParas.CreatedByAdv) //创建人
//.Parameter("Createdby", searchParas.CreatedByAdv) //创建人
.Parameter("Status", searchParas.SelStatusAdv) //状态
.Parameter("beginDate", searchParas.BeginDateAdv) //开始日期
.Parameter("endDate", searchParas.EndDateAdv) //结束日期
.OrderBy("m.SysNo desc")
.Paging(currPageIndex ?? 1, PageSize)
.QueryMany();
}
return(pagerResult);
}
