public OrderModel Get(
Sqls.TableTypes tableType = Sqls.TableTypes.Normal,
SqlColumnCollection column = null,
SqlJoinCollection join = null,
SqlWhereCollection where = null,
SqlOrderByCollection orderBy = null,
SqlParamCollection param = null,
bool distinct = false,
int top = 0)
{
Set(Rds.ExecuteTable(statements: Rds.SelectOrders(
tableType: tableType,
column: column ?? Rds.OrdersDefaultColumns(),
join: join ?? Rds.OrdersJoinDefault(),
where : where ?? Rds.OrdersWhereDefault(this),
orderBy: orderBy,
param: param,
distinct: distinct,
top: top)));
return(this);
}
public SqlOrderByCollection OrderBy(
Context context,
SiteSettings ss,
SqlOrderByCollection orderBy = null,
int pageSize = 0)
{
orderBy = orderBy ?? new SqlOrderByCollection();
if (ColumnSorterHash?.Any() == true)
{
ColumnSorterHash?.ForEach(data =>
orderBy.Add(
column: ss.GetColumn(context: context, columnName: data.Key),
orderType: data.Value));
}
return(pageSize > 0 && orderBy?.Any() != true
? new SqlOrderByCollection().Add(
tableName: ss.ReferenceType,
columnBracket: "[UpdatedTime]",
orderType: SqlOrderBy.Types.desc)
: orderBy);
}
private EnumerableRowCollection <DataRow> Get(
Context context,
SiteSettings ss,
SqlColumnCollection column = null,
SqlJoinCollection join = null,
SqlWhereCollection where = null,
SqlOrderByCollection orderBy = null,
SqlParamCollection param = null,
Sqls.TableTypes tableType = Sqls.TableTypes.Normal,
bool distinct = false,
int top = 0,
int offset = 0,
int pageSize = 0,
bool history = false,
bool countRecord = false)
{
var statements = new List <SqlStatement>
{
Rds.SelectResults(
dataTableName: "Main",
column: column ?? Rds.ResultsDefaultColumns(),
join: join ?? Rds.ResultsJoinDefault(),
where : where,
orderBy: orderBy,
param: param,
tableType: tableType,
distinct: distinct,
top: top,
offset: offset,
pageSize: pageSize,
countRecord: countRecord)
};
var dataSet = Rds.ExecuteDataSet(
context: context,
transactional: false,
statements: statements.ToArray());
TotalCount = Rds.Count(dataSet);
return(dataSet.Tables["Main"].AsEnumerable());
}
private static Dictionary <long, long> ResultData(
SiteSettings ss,
Dictionary <long, Dictionary <long, Dictionary <int, string> > > data,
SqlWhereCollection where,
SqlOrderByCollection orderBy,
Export export,
Dictionary <long, long> keys,
Dictionary <long, string> keyColumns)
{
ss.SetColumnAccessControls();
ss.SetChoiceHash();
var keyColumn = keyColumns.Get(ss.SiteId);
var resultHash = new ResultCollection(
ss: ss,
column: ResultsColumn(ss, export, ss.GetColumn(keyColumn)),
where : where,
orderBy: orderBy)
.ToDictionary(o => o.ResultId, o => o);
resultHash.Values.ToList().SetLinks(ss);
if (keys == null)
{
data.Add(ss.SiteId, resultHash.ToDictionary(
o => o.Value.ResultId,
o => ResultData(ss, export, o.Value)));
return(resultHash.ToDictionary(
o => o.Key,
o => o.Value.PropertyValue(keyColumn).ToLong()));
}
else
{
data.Add(ss.SiteId, keys.ToDictionary(
o => o.Key,
o => ResultData(ss, export, resultHash.Get(o.Value))));
return(keys.ToDictionary(
o => o.Key,
o => resultHash.Get(o.Value)?.PropertyValue(keyColumn).ToLong() ?? 0));
}
}
public WikiModel Get(
SiteSettings ss,
Sqls.TableTypes tableType = Sqls.TableTypes.Normal,
SqlColumnCollection column = null,
SqlJoinCollection join = null,
SqlWhereCollection where = null,
SqlOrderByCollection orderBy = null,
SqlParamCollection param = null,
bool distinct = false,
int top = 0)
{
Set(ss, Rds.ExecuteTable(statements: Rds.SelectWikis(
tableType: tableType,
column: column ?? Rds.WikisEditorColumns(ss),
join: join ?? Rds.WikisJoinDefault(),
where : where ?? Rds.WikisWhereDefault(this),
orderBy: orderBy ?? null,
param: param ?? null,
distinct: distinct,
top: top)));
return(this);
}
private static Dictionary <long, long> IssueData(
SiteSettings ss,
Dictionary <long, Dictionary <long, Dictionary <int, string> > > data,
SqlWhereCollection where,
SqlOrderByCollection orderBy,
Export export,
Dictionary <long, long> keys,
Dictionary <long, string> keyColumns)
{
ss.SetColumnAccessControls();
var keyColumn = keyColumns.Get(ss.SiteId);
var issueHash = new IssueCollection(
ss: ss,
column: IssuesColumn(ss, export, ss.GetColumn(keyColumn)),
join: ss.Join(withColumn: true),
where : where,
orderBy: orderBy)
.ToDictionary(o => o.IssueId, o => o);
if (keys == null)
{
data.Add(ss.SiteId, issueHash.ToDictionary(
o => o.Value.IssueId,
o => IssueData(ss, export, o.Value)));
return(issueHash.ToDictionary(
o => o.Key,
o => o.Value.PropertyValue(keyColumn).ToLong()));
}
else
{
data.Add(ss.SiteId, keys.ToDictionary(
o => o.Key,
o => IssueData(ss, export, issueHash.Get(o.Value))));
return(keys.ToDictionary(
o => o.Key,
o => issueHash.Get(o.Value)?.PropertyValue(keyColumn).ToLong() ?? 0));
}
}
public IssueCollection(
Context context,
SiteSettings ss,
SqlColumnCollection column = null,
SqlJoinCollection join = null,
SqlWhereCollection where = null,
SqlOrderByCollection orderBy = null,
SqlParamCollection param = null,
Sqls.TableTypes tableType = Sqls.TableTypes.Normal,
bool distinct = false,
int top = 0,
int offset = 0,
int pageSize = 0,
bool get = true,
List <FormData> formDataSet = null)
{
if (get)
{
Set(
context: context,
ss: ss,
dataRows: Get(
context: context,
ss: ss,
column: column,
join: join,
where : where,
orderBy: orderBy,
param: param,
tableType: tableType,
distinct: distinct,
top: top,
offset: offset,
pageSize: pageSize),
formDataSet: formDataSet);
}
}
public ReminderScheduleModel Get(
Context context,
Sqls.TableTypes tableType = Sqls.TableTypes.Normal,
SqlColumnCollection column = null,
SqlJoinCollection join = null,
SqlWhereCollection where = null,
SqlOrderByCollection orderBy = null,
SqlParamCollection param = null,
bool distinct = false,
int top = 0)
{
Set(context, Repository.ExecuteTable(
context: context,
statements: Rds.SelectReminderSchedules(
tableType: tableType,
column: column ?? Rds.ReminderSchedulesDefaultColumns(),
join: join ?? Rds.ReminderSchedulesJoinDefault(),
where : where ?? Rds.ReminderSchedulesWhereDefault(this),
orderBy: orderBy,
param: param,
distinct: distinct,
top: top)));
return(this);
}
private DataSet GetDataSet(Context context, SiteSettings ss, List <Column> toColumns)
{
var orderByColumn = ss.GetColumn(
context: context,
columnName: Column);
var column = new SqlColumnCollection()
.Add(column: ss.GetColumn(
context: context,
columnName: Rds.IdColumn(ss.ReferenceType)))
.Add(column: orderByColumn)
.ItemTitle(ss.ReferenceType);
toColumns.ForEach(toColumn =>
column.Add(column: toColumn));
var columns = ss.IncludedColumns(Line).ToList();
columns.ForEach(o => column.Add(column: o));
if (columns.Any(o => o.ColumnName == "Status"))
{
columns.Add(ss.GetColumn(context: context, columnName: "Status"));
}
var view = ss.Views?.Get(Condition) ?? new View();
var where = view.Where(
context: context,
ss: ss,
checkPermission: false)
.Add(
tableName: ss.ReferenceType,
columnBrackets: new string[] { $"\"{orderByColumn.ColumnName}\"" },
_operator: "<'{0}'".Params(
DateTime.Now.ToLocal(context: context).Date.AddDays(Range)))
.Add(or: new SqlWhereCollection()
.Add(
tableName: ss.ReferenceType,
columnBrackets: new string[] { "\"Status\"" },
_operator: " is null")
.Add(
tableName: ss.ReferenceType,
columnBrackets: new string[] { "\"Status\"" },
_operator: "<{0}".Params(Parameters.General.CompletionCode))
.Add(
tableName: ss.ReferenceType,
columnBrackets: new string[]
{
"\"" + orderByColumn.ColumnName + "\""
},
_operator: "<'{0}'".Params(
DateTime.Now.ToLocal(context: context).Date),
_using: SendCompletedInPast == true));
var orderBy = new SqlOrderByCollection()
.Add(
column: orderByColumn,
orderType: SqlOrderBy.Types.desc);
var dataSet = Repository.ExecuteDataSet(
context: context,
statements: new SqlStatement[]
{
Rds.Select(
tableName: ss.ReferenceType,
dataTableName: "Main",
column: column,
join: new SqlJoinCollection().ItemJoin(
tableType: Sqls.TableTypes.Normal,
tableName: ss.ReferenceType),
where : where,
orderBy: orderBy),
Rds.SelectCount(
tableName: ss.ReferenceType,
join: new SqlJoinCollection().ItemJoin(
tableType: Sqls.TableTypes.Normal,
tableName: ss.ReferenceType),
where : where)
});
return(dataSet);
}
private DataTable GetDataSet(
Context context,
SiteSettings ss,
List <Column> toColumns,
List <Column> subjectColumns,
List <Column> bodyColumns)
{
var orderByColumn = ss.GetColumn(
context: context,
columnName: Column);
var column = new SqlColumnCollection()
.Add(column: ss.GetColumn(
context: context,
columnName: Rds.IdColumn(ss.ReferenceType)))
.Add(column: orderByColumn)
.ItemTitle(ss.ReferenceType);
toColumns.ForEach(toColumn =>
column.Add(column: toColumn));
subjectColumns.ForEach(subjectColumn =>
column.Add(column: subjectColumn));
bodyColumns.ForEach(bodyColumn =>
column.Add(column: bodyColumn));
var columns = ss.IncludedColumns(Line).ToList();
columns.ForEach(o => column.Add(column: o));
if (columns.Any(o => o.ColumnName == "Status"))
{
columns.Add(ss.GetColumn(context: context, columnName: "Status"));
}
var view = ss.Views?.Get(Condition) ?? new View();
var where = view.Where(
context: context,
ss: ss,
checkPermission: false)
.Add(
tableName: ss.ReferenceType,
columnBrackets: new string[] { orderByColumn.ColumnName },
_operator: "<'{0}'".Params(
DateTime.Now.ToLocal(context: context).Date.AddDays(Range)))
.Add(
tableName: ss.ReferenceType,
columnBrackets: "[CompletionTime]".ToSingleArray(),
_operator: ">getdate()",
_using: ExcludeOverdue == true)
.Add(or: new SqlWhereCollection()
.Add(
tableName: ss.ReferenceType,
columnBrackets: new string[] { "[Status]" },
_operator: " is null")
.Add(
tableName: ss.ReferenceType,
columnBrackets: new string[] { "[Status]" },
_operator: "<{0}".Params(Parameters.General.CompletionCode))
.Add(
tableName: ss.ReferenceType,
columnBrackets: new string[]
{
"[" + orderByColumn.ColumnName + "]"
},
_operator: "<'{0}'".Params(
DateTime.Now.ToLocal(context: context).Date),
_using: SendCompletedInPast == true));
var orderBy = new SqlOrderByCollection()
.Add(
column: orderByColumn,
orderType: SqlOrderBy.Types.desc);
var dataTable = Rds.ExecuteTable(
context: context,
statements: Rds.Select(
tableName: ss.ReferenceType,
column: column,
join: new SqlJoinCollection().ItemJoin(
tableType: Sqls.TableTypes.Normal,
tableName: ss.ReferenceType),
where : where,
orderBy: orderBy,
top: Parameters.Reminder.Limit));
return(dataTable);
}