예제 #1
0
 /// <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);
예제 #2
0
        /// <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);
        }