public List <DicReader> GetPlistVisitingLogTrs(string key, DateTime?leftVisitOn, DateTime?rightVisitOn, PagingInput paging)
{
paging.Valid();
if (key != null)
{
key = key.Trim();
}
Func <SqlFilter> filter = () =>
{
var parameters = new List <DbParameter>();
var filterString = @" where a.LoginName like @key ";
parameters.Add(CreateParameter("key", "%" + key + "%", DbType.String));
if (leftVisitOn.HasValue)
{
parameters.Add(CreateParameter("leftVisitOn", leftVisitOn.Value, DbType.DateTime));
filterString += " and a.VisitOn>=@leftVisitOn";
}
if (rightVisitOn.HasValue)
{
parameters.Add(CreateParameter("rightVisitOn", rightVisitOn.Value, DbType.DateTime));
filterString += " and a.VisitOn<@rightVisitOn";
}
return(new SqlFilter(filterString, parameters.ToArray()));
};
return(base.GetPlist("VisitingLog", filter, paging));
}
public List <DicReader> GetPlistVisitingLogTrs(Guid accountId, string loginName, DateTime?leftVisitOn, DateTime?rightVisitOn, PagingInput paging)
{
paging.Valid();
loginName = (loginName ?? string.Empty).ToLower();
Func <SqlFilter> filter = () =>
{
var parameters = new List <DbParameter>();
var filterString = @" where (a.AccountId=@AccountId or Lower(a.LoginName)=@LoginName) ";
parameters.Add(CreateParameter("LoginName", loginName, DbType.String));
parameters.Add(CreateParameter("AccountId", accountId, DbType.Guid));
if (leftVisitOn.HasValue)
{
parameters.Add(CreateParameter("leftVisitOn", leftVisitOn.Value, DbType.DateTime));
filterString += " and a.VisitOn>=@leftVisitOn";
}
if (rightVisitOn.HasValue)
{
parameters.Add(CreateParameter("rightVisitOn", rightVisitOn.Value, DbType.DateTime));
filterString += " and a.VisitOn<@rightVisitOn";
}
return(new SqlFilter(filterString, parameters.ToArray()));
};
return(base.GetPlist("VisitingLog", filter, paging));
}
public List <DicReader> GetPlistCatalogAccountTrs(string key, string catalogCode
, bool includeDescendants, PagingInput paging)
{
paging.Valid();
if (string.IsNullOrEmpty(catalogCode))
{
throw new ArgumentNullException("catalogCode");
}
Func <SqlFilter> filter = () =>
{
var parameters = new List <DbParameter>();
var filterString = " where (a.Name like @key or a.Code like @key or a.LoginName like @key)";
parameters.Add(CreateParameter("key", "%" + key + "%", DbType.String));
if (!includeDescendants)
{
parameters.Add(CreateParameter("CatalogCode", catalogCode, DbType.String));
filterString += " and a.CatalogCode=@CatalogCode";
}
else
{
parameters.Add(CreateParameter("CatalogCode", catalogCode + "%", DbType.String));
filterString += " and a.CatalogCode like @CatalogCode";
}
return(new SqlFilter(filterString, parameters.ToArray()));
};
return(base.GetPlist("CatalogAccountTr", filter, paging));
}
public IList <ExceptionLog> GetPlistExceptionLogs(List <FilterData> filters, PagingInput paging)
{
paging.Valid();
var filterStringBuilder = _acDomain.RetrieveRequiredService <ISqlFilterStringBuilder>();
RdbDescriptor db = GetExceptionLogDb();
List <DbParameter> prams;
var filterString = filterStringBuilder.FilterString(db, filters, "t", out prams);
if (!string.IsNullOrEmpty(filterString))
{
filterString = " where " + filterString;
}
var sql =
@"select top({0}) *
from (SELECT ROW_NUMBER() OVER(ORDER BY {1} {2}) AS RowNumber,* FROM {3} as t"
+ filterString + ") a WHERE a.RowNumber > {4}";
var countSql = "select count(*) from ExceptionLog as t" + filterString;
var exceptionLogs = new List <ExceptionLog>();
var reader = db.ExecuteReader(
string.Format(sql, paging.PageSize, paging.SortField, paging.SortOrder, "ExceptionLog", paging.PageSize * paging.PageIndex), prams.ToArray());
while (reader.Read())
{
exceptionLogs.Add(ExceptionLog.Create(reader));
}
paging.Total = (int)db.ExecuteScalar(countSql, prams.Select(p => ((ICloneable)p).Clone()).Cast <DbParameter>().ToArray());
reader.Close();
return(exceptionLogs);
}
/// <summary>
///
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="filterCallback"></param>
/// <param name="paging"></param>
/// <returns></returns>
protected IList <T> GetPlist <T>(Func <ObjectFilter> filterCallback, PagingInput paging) where T : class, IEntity <Guid>
{
paging.Valid();
string setName = typeof(T).Name + "s";
ObjectFilter filter = ObjectFilter.Empty;
if (filterCallback != null)
{
filter = filterCallback();
}
var queryString =
@"select value a from " + setName + " as a " + filter.FilterString + " order by a." + paging.SortField + " " + paging.SortOrder;
var countQs =
@"select value a from " + setName + " as a " + filter.FilterString;
IQueryable <T> countQuery;
IQueryable <T> query;
if (filter.Parameters != null)
{
countQuery = this.DbContext.CreateQuery <T>(countQs, filter.Parameters);
query = this.DbContext.CreateQuery <T>(queryString, filter.Parameters)
.Skip(paging.SkipCount).Take(paging.PageSize);
}
else
{
countQuery = this.DbContext.CreateQuery <T>(countQs);
query = this.DbContext.CreateQuery <T>(queryString)
.Skip(paging.SkipCount).Take(paging.PageSize);
}
paging.Total = countQuery.Count();
return(query.ToList <T>());
}
public List<DicReader> GetPlistVisitingLogTrs(string key, DateTime? leftVisitOn, DateTime? rightVisitOn, PagingInput paging)
{
paging.Valid();
if (key != null)
{
key = key.Trim();
}
Func<SqlFilter> filter = () =>
{
var parameters = new List<DbParameter>();
var filterString = @" where a.LoginName like @key ";
parameters.Add(CreateParameter("key", "%" + key + "%", DbType.String));
if (leftVisitOn.HasValue)
{
parameters.Add(CreateParameter("leftVisitOn", leftVisitOn.Value, DbType.DateTime));
filterString += " and a.VisitOn>=@leftVisitOn";
}
if (rightVisitOn.HasValue)
{
parameters.Add(CreateParameter("rightVisitOn", rightVisitOn.Value, DbType.DateTime));
filterString += " and a.VisitOn<@rightVisitOn";
}
return new SqlFilter(filterString, parameters.ToArray());
};
return base.GetPlist("VisitingLog", filter, paging);
}
public List<DicReader> GetPlistCatalogAccountTrs(string key, string catalogCode
, bool includeDescendants, PagingInput paging)
{
paging.Valid();
if (string.IsNullOrEmpty(catalogCode))
{
throw new ArgumentNullException("catalogCode");
}
Func<SqlFilter> filter = () =>
{
var parameters = new List<DbParameter>();
var filterString = " where (a.Name like @key or a.Code like @key or a.LoginName like @key)";
parameters.Add(CreateParameter("key", "%" + key + "%", DbType.String));
if (!includeDescendants)
{
parameters.Add(CreateParameter("CatalogCode", catalogCode, DbType.String));
filterString += " and a.CatalogCode=@CatalogCode";
}
else
{
parameters.Add(CreateParameter("CatalogCode", catalogCode + "%", DbType.String));
filterString += " and a.CatalogCode like @CatalogCode";
}
return new SqlFilter(filterString, parameters.ToArray());
};
return base.GetPlist("CatalogAccountTr", filter, paging);
}
public List<DicReader> GetPlistGroupAccountTrs(string key, Guid groupId, PagingInput paging)
{
paging.Valid();
Func<SqlFilter> filter = () =>
{
var parameters = new List<DbParameter>();
const string filterString = @" where (a.Name like @key
or a.Code like @key
or a.LoginName like @key) and a.GroupId=@GroupId";
parameters.Add(CreateParameter("key", "%" + key + "%", DbType.String));
parameters.Add(CreateParameter("GroupId", groupId, DbType.Guid));
return new SqlFilter(filterString, parameters.ToArray());
};
return base.GetPlist("GroupAccountTr", filter, paging);
}
public List <DicReader> GetPlistGroupAccountTrs(string key, Guid groupId, PagingInput paging)
{
paging.Valid();
Func <SqlFilter> filter = () =>
{
var parameters = new List <DbParameter>();
const string filterString = @" where (a.Name like @key
or a.Code like @key
or a.LoginName like @key) and a.GroupId=@GroupId" ;
parameters.Add(CreateParameter("key", "%" + key + "%", DbType.String));
parameters.Add(CreateParameter("GroupId", groupId, DbType.Guid));
return(new SqlFilter(filterString, parameters.ToArray()));
};
return(base.GetPlist("GroupAccountTr", filter, paging));
}
public List <DicReader> GetPlistAccountTrs(List <FilterData> filters, string catalogCode, bool includeDescendants, PagingInput paging)
{
paging.Valid();
bool byOrgCode = !string.IsNullOrEmpty(catalogCode);
Func <SqlFilter> filter = () =>
{
List <DbParameter> parameters;
var filterString = new SqlFilterStringBuilder().FilterString(base.DbContext.Rdb, filters, "a", out parameters);
if (!string.IsNullOrEmpty(filterString))
{
filterString = " where 1=1 and " + filterString;
}
else
{
filterString = " where 1=1 ";
}
if (!includeDescendants)
{
if (byOrgCode)
{
if (!string.IsNullOrEmpty(catalogCode))
{
parameters.Add(this.CreateParameter("CatalogCode", catalogCode, DbType.String));
filterString += "and a.CatalogCode=@CatalogCode ";
}
}
}
else
{
if (byOrgCode)
{
if (!string.IsNullOrEmpty(catalogCode))
{
parameters.Add(CreateParameter("CatalogCode", catalogCode + "%", DbType.String));
filterString += "and a.CatalogCode like @CatalogCode ";
}
}
}
return(new SqlFilter(filterString, parameters.ToArray()));
};
return(base.GetPlist("AccountTr", filter, paging));
}
public List<DicReader> GetPlistAccountTrs(List<FilterData> filters, string catalogCode, bool includeDescendants, PagingInput paging)
{
paging.Valid();
bool byOrgCode = !string.IsNullOrEmpty(catalogCode);
Func<SqlFilter> filter = () =>
{
List<DbParameter> parameters;
var filterString = new SqlFilterStringBuilder().FilterString(base.DbContext.Rdb, filters, "a", out parameters);
if (!string.IsNullOrEmpty(filterString))
{
filterString = " where 1=1 and " + filterString;
}
else
{
filterString = " where 1=1 ";
}
if (!includeDescendants)
{
if (byOrgCode)
{
if (!string.IsNullOrEmpty(catalogCode))
{
parameters.Add(this.CreateParameter("CatalogCode", catalogCode, DbType.String));
filterString += "and a.CatalogCode=@CatalogCode ";
}
}
}
else
{
if (byOrgCode)
{
if (!string.IsNullOrEmpty(catalogCode))
{
parameters.Add(CreateParameter("CatalogCode", catalogCode + "%", DbType.String));
filterString += "and a.CatalogCode like @CatalogCode ";
}
}
}
return new SqlFilter(filterString, parameters.ToArray());
};
return base.GetPlist("AccountTr", filter, paging);
}
public List<DicReader> GetPlistVisitingLogTrs(Guid accountId, string loginName, DateTime? leftVisitOn, DateTime? rightVisitOn, PagingInput paging)
{
paging.Valid();
loginName = (loginName ?? string.Empty).ToLower();
Func<SqlFilter> filter = () =>
{
var parameters = new List<DbParameter>();
var filterString = @" where (a.AccountId=@AccountId or Lower(a.LoginName)=@LoginName) ";
parameters.Add(CreateParameter("LoginName", loginName, DbType.String));
parameters.Add(CreateParameter("AccountId", accountId, DbType.Guid));
if (leftVisitOn.HasValue)
{
parameters.Add(CreateParameter("leftVisitOn", leftVisitOn.Value, DbType.DateTime));
filterString += " and a.VisitOn>=@leftVisitOn";
}
if (rightVisitOn.HasValue)
{
parameters.Add(CreateParameter("rightVisitOn", rightVisitOn.Value, DbType.DateTime));
filterString += " and a.VisitOn<@rightVisitOn";
}
return new SqlFilter(filterString, parameters.ToArray());
};
return base.GetPlist("VisitingLog", filter, paging);
}
public IList <OperationLog> GetPlistOperationLogs(Guid?targetId,
DateTime?leftCreateOn, DateTime?rightCreateOn
, List <FilterData> filters, PagingInput paging)
{
paging.Valid();
var filterStringBuilder = _acDomain.RetrieveRequiredService <ISqlFilterStringBuilder>();
RdbDescriptor db = GetOperationLogDb();
List <DbParameter> prams;
var filterString = filterStringBuilder.FilterString(db, filters, "t", out prams);
if (!string.IsNullOrEmpty(filterString))
{
filterString = " where " + filterString + "{0}";
}
else
{
filterString = " where 1=1 {0}";
}
if (targetId.HasValue)
{
filterString = string.Format(filterString, " and t.TargetID=@TargetId {0}");
}
if (leftCreateOn.HasValue)
{
filterString = string.Format(filterString, " and t.CreateOn >= @leftCreateOn");
}
if (rightCreateOn.HasValue)
{
filterString = string.Format(filterString, " and t.CreateOn < @rightCreateOn");
}
else
{
filterString = string.Format(filterString, string.Empty);
}
var sql =
@"select top({0}) *
from (SELECT ROW_NUMBER() OVER(ORDER BY {1} {2}) AS RowNumber,* FROM {3} as t"
+ filterString + ") a WHERE a.RowNumber > {4}";
var countSql = "select count(*) from OperationLog as t" + filterString;
var operationLogs = new List <OperationLog>();
if (prams == null)
{
prams = new List <DbParameter>();
}
if (targetId.HasValue)
{
prams.Add(CreateParameter(db, "TargetId", targetId.Value, DbType.Guid));
}
if (leftCreateOn.HasValue)
{
prams.Add(CreateParameter(db, "leftCreateOn", leftCreateOn.Value, DbType.DateTime));
}
if (rightCreateOn.HasValue)
{
prams.Add(CreateParameter(db, "rightCreateOn", rightCreateOn.Value, DbType.DateTime));
}
var reader = db.ExecuteReader(
string.Format(sql, paging.PageSize, paging.SortField, paging.SortOrder, "OperationLog", paging.PageSize * paging.PageIndex), prams.ToArray());
while (reader.Read())
{
operationLogs.Add(OperationLog.Create(reader));
}
paging.Total = (int)db.ExecuteScalar(countSql, prams.Select(p => ((ICloneable)p).Clone()).Cast <DbParameter>().ToArray());
reader.Close();
return(operationLogs);
}
public IList<OperationLog> GetPlistOperationLogs(Guid? targetId,
DateTime? leftCreateOn, DateTime? rightCreateOn
, List<FilterData> filters, PagingInput paging)
{
paging.Valid();
var filterStringBuilder = _acDomain.RetrieveRequiredService<ISqlFilterStringBuilder>();
RdbDescriptor db = GetOperationLogDb();
List<DbParameter> prams;
var filterString = filterStringBuilder.FilterString(db, filters, "t", out prams);
if (!string.IsNullOrEmpty(filterString))
{
filterString = " where " + filterString + "{0}";
}
else
{
filterString = " where 1=1 {0}";
}
if (targetId.HasValue)
{
filterString = string.Format(filterString, " and t.TargetID=@TargetId {0}");
}
if (leftCreateOn.HasValue)
{
filterString = string.Format(filterString, " and t.CreateOn >= @leftCreateOn");
}
if (rightCreateOn.HasValue)
{
filterString = string.Format(filterString, " and t.CreateOn < @rightCreateOn");
}
else
{
filterString = string.Format(filterString, string.Empty);
}
var sql =
@"select top({0}) *
from (SELECT ROW_NUMBER() OVER(ORDER BY {1} {2}) AS RowNumber,* FROM {3} as t"
+ filterString + ") a WHERE a.RowNumber > {4}";
var countSql = "select count(*) from OperationLog as t" + filterString;
var operationLogs = new List<OperationLog>();
if (prams == null)
{
prams = new List<DbParameter>();
}
if (targetId.HasValue)
{
prams.Add(CreateParameter(db, "TargetId", targetId.Value, DbType.Guid));
}
if (leftCreateOn.HasValue)
{
prams.Add(CreateParameter(db, "leftCreateOn", leftCreateOn.Value, DbType.DateTime));
}
if (rightCreateOn.HasValue)
{
prams.Add(CreateParameter(db, "rightCreateOn", rightCreateOn.Value, DbType.DateTime));
}
var reader = db.ExecuteReader(
string.Format(sql, paging.PageSize, paging.SortField, paging.SortOrder, "OperationLog", paging.PageSize * paging.PageIndex), prams.ToArray());
while (reader.Read())
{
operationLogs.Add(OperationLog.Create(reader));
}
paging.Total = (int)db.ExecuteScalar(countSql, prams.Select(p => ((ICloneable)p).Clone()).Cast<DbParameter>().ToArray());
reader.Close();
return operationLogs;
}
public IList<ExceptionLog> GetPlistExceptionLogs(List<FilterData> filters, PagingInput paging)
{
paging.Valid();
var filterStringBuilder = _acDomain.RetrieveRequiredService<ISqlFilterStringBuilder>();
RdbDescriptor db = GetExceptionLogDb();
List<DbParameter> prams;
var filterString = filterStringBuilder.FilterString(db, filters, "t", out prams);
if (!string.IsNullOrEmpty(filterString))
{
filterString = " where " + filterString;
}
var sql =
@"select top({0}) *
from (SELECT ROW_NUMBER() OVER(ORDER BY {1} {2}) AS RowNumber,* FROM {3} as t"
+ filterString + ") a WHERE a.RowNumber > {4}";
var countSql = "select count(*) from ExceptionLog as t" + filterString;
var exceptionLogs = new List<ExceptionLog>();
var reader = db.ExecuteReader(
string.Format(sql, paging.PageSize, paging.SortField, paging.SortOrder, "ExceptionLog", paging.PageSize * paging.PageIndex), prams.ToArray());
while (reader.Read())
{
exceptionLogs.Add(ExceptionLog.Create(reader));
}
paging.Total = (int)db.ExecuteScalar(countSql, prams.Select(p => ((ICloneable)p).Clone()).Cast<DbParameter>().ToArray());
reader.Close();
return exceptionLogs;
}
