/// <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));
}
