/// <summary> /// 分页查询 /// </summary> /// <param name="filter">查询参数</param> /// <returns>日志分页</returns> /// <remarks>2013-10-22 黄志勇 创建 /// 修改LiJia日志查询方法,仓库编号为多个编号时进行查询(余勇 2014-07-02) /// 拼接Sql解决无法使用索引的问题(null is null or 无法使用索引) 2014-09-03 杨浩 修改 /// </remarks> public override Pager <CBLiJiaSyncLog> GetList(ParaLiJiaSyncLogFilter filter) { #region 短路sql // string sql = // @"(select a.*,h.WarehouseName // from LiJiaSyncLog a // left join WhWarehouse h on h.sysno=a.warehousesysno // // where 1=1 // (:Status is null or a.Status=:Status) and --状态 // (:Name is null or charindex(a.Name,:Name) > 0) and --接口名称 // (:VoucherNo is null or charindex(a.VoucherNo,:VoucherNo) > 0) and --单据号 // (:Remarks is null or charindex(a.Remarks,:Remarks) > 0) and --备注 // (:BeginDate is null or a.CreatedDate>=:BeginDate) and --创建日期(起) // (:EndDate is null or a.CreatedDate<:EndDate) and --创建日期(止) // (:LastsyncBeginTime is null or a.LastsyncTime>=:LastsyncBeginTime) and --同步日期(起) // (:LastsyncEndDate is null or a.LastsyncTime<:LastsyncEndDate) and --同步日期(止) // (:FlowIdentify is null or a.FlowIdentify=:FlowIdentify) and --流程标识 // (:StatusCode is null or a.StatusCode=:StatusCode) --状态码 // --(:WarehouseSysNo is null or a.WarehouseSysNo=:WarehouseSysNo) // and (:WarehouseSysNo is null or exists (select 1 from table(splitstr(:WarehouseSysNo, ',')) tmp where tmp.column_value = a.WarehouseSysNo)) --仓库 // and exists (select 1 from table(splitstr(:Warehouses, ',')) tmp where tmp.column_value = a.warehousesysno) // ) tb"; // //查询日期上限+1 // filter.EndDate = filter.EndDate == null ? (DateTime?)null : filter.EndDate.Value.AddDays(1); // var paras = new object[] // { // filter.Status, filter.Status, // filter.Name, filter.Name, // filter.VoucherNo, filter.VoucherNo != null ? filter.VoucherNo.Trim() : null, // filter.Remarks, filter.Remarks != null ? filter.Remarks.Trim() : null, // filter.BeginDate, filter.BeginDate, // filter.EndDate, filter.EndDate, // filter.LastsyncBeginTime, filter.LastsyncBeginTime, // filter.LastsyncEndDate, filter.LastsyncEndDate, // filter.FlowIdentify, filter.FlowIdentify, // filter.StatusCode, filter.StatusCode, // filter.WarehouseSysNo,filter.WarehouseSysNo, // filter.Warehouses // }; #endregion #region 条件 string sql = @"(select a.*,h.WarehouseName from LiJiaSyncLog a left join WhWarehouse h on h.sysno=a.warehousesysno where 1=1 {0} ) tb "; string where = string.Empty; var paras = new ArrayList(); int i = 0; //拼接Sql解决无法使用索引的问题(null is null or 无法使用索引) 杨浩 修改 if (filter.Status != null) { where += " and a.Status=@p0p" + i; paras.Add(filter.Status); i++; } if (filter.Name != null) { where += " and CHARINDEX(a.Name,@p0p" + i + ") > 0 "; i++; paras.Add(filter.Name); } if (filter.Remarks != null) { where += " and CHARINDEX(a.Remarks,@p0p" + i + ") >0 "; i++; paras.Add(filter.Remarks); } if (filter.VoucherNo != null) { where += " and a.VoucherNo=@p0p" + i; i++; paras.Add(filter.VoucherNo); } if (filter.BeginDate != null) { where += " and a.CreatedDate>=@p0p" + i; i++; paras.Add(filter.BeginDate); } if (filter.EndDate != null) { where += " and a.CreatedDate<@p0p" + i; i++; paras.Add(filter.EndDate); } if (filter.LastsyncBeginTime != null) { where += " and a.LastsyncTime>=@p0p" + i; i++; paras.Add(filter.LastsyncBeginTime); } if (filter.LastsyncEndDate != null) { where += " and a.LastsyncTime<@p0p" + i; i++; paras.Add(filter.LastsyncEndDate); } if (filter.FlowIdentify != null) { where += " and a.FlowIdentify=@p0p" + i; i++; paras.Add(filter.FlowIdentify); } if (filter.StatusCode != null) { where += " and a.StatusCode=@p0p" + i; i++; paras.Add(filter.StatusCode); } if (filter.WarehouseSysNo != null) { where += @" and exists (select 1 from splitstr(@p0p" + i + ", ',') tmp where tmp.col = a.WarehouseSysNo)"; i++; where += @" and exists (select 1 from splitstr(@p0p" + i + ", ',') tmp where tmp.col = a.warehousesysno)"; i++; paras.Add(filter.WarehouseSysNo); paras.Add(filter.Warehouses); } sql = string.Format(sql, where); #endregion var dataList = Context.Select <CBLiJiaSyncLog>("tb.*").From(sql); var dataCount = Context.Select <int>("count(0)").From(sql); dataList.Parameters(paras); dataCount.Parameters(paras); var pager = new Pager <CBLiJiaSyncLog> { PageSize = filter.PageSize, CurrentPage = filter.Id }; pager.TotalRows = dataCount.QuerySingle(); pager.Rows = dataList.OrderBy(" tb.FLOWIDENTIFY desc, tb.interfacetype desc,tb.sysno asc") .Paging(pager.CurrentPage, filter.PageSize) .QueryMany(); return(pager); }
/// <summary> /// 分页查询 /// </summary> /// <param name="filter">查询参数</param> /// <returns>日志分页</returns> /// <remarks>2013-10-22 黄志勇 创建</remarks> public abstract Pager <CBLiJiaSyncLog> GetList(ParaLiJiaSyncLogFilter filter);
/// <summary> /// LiJia同步日志查询 /// </summary> /// <param name="filter">查询条件</param> /// <returns>分页查询</returns> /// <remarks>2013-10-22 黄志勇 创建</remarks> public Pager <CBLiJiaSyncLog> GetList(ParaLiJiaSyncLogFilter filter) { return(ILiJiaDao.Instance.GetList(filter)); }