/// <summary>
/// Execute the spOpts to get the list of SearchPaneOptions to return to the client-
/// side
/// </summary>
/// <param name="field">Field instance</param>
/// <param name="editor">Editor instance</param>
/// <param name="leftJoin">List of LeftJoins instance</param>
/// <param name="fields">Field[] instance</param>
/// <param name="http">DtRequest instance</param>
/// <returns>List of SearchPaneOptions</returns>
internal List <Dictionary <string, object> > SearchPaneOptionsExec(Field field, Editor editor, List <LeftJoin> leftJoin, Field[] fields, DtRequest http)
{
if (_spOptsFn != null)
{
return(_spOptsFn(editor.Db(), editor));
}
if (_spOpts != null)
{
return(_spOpts.Exec(field, editor, leftJoin, http, fields));
}
return(null);
}
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* Internal methods
*/
/// <summary>
/// Execute the configuration, getting the SearchPaneOptions from the database and formatting
/// for output.
/// </summary>
/// <param name="fieldIn">Field that the SearchPane options are to be found for</param>
/// <param name="editor">Editor Instance</param>
/// <param name="leftJoinIn">List of LeftJoins to be performed</param>
/// <param name="http">DTRequest Instance for where conditions</param>
/// <param name="fields">Array of all of the other fields</param>
/// <returns>List of SearchPaneOptions</returns>
internal List <Dictionary <string, object> > Exec(Field fieldIn, Editor editor, List <LeftJoin> leftJoinIn, DtRequest http, Field[] fields)
{
var db = editor.Db();
string _label;
if (this._value == null)
{
this._value = fieldIn.DbField();
}
if (this._table == null)
{
var readTable = editor.ReadTable();
if (readTable.Count() == 0)
{
this._table = editor.Table()[0].ToString();
}
else
{
this._table = readTable[0];
}
}
if (this._label == null)
{
_label = this._value;
}
else
{
_label = this._label.First();
}
// Just return the label if no default renderer
var formatter = _renderer ?? (str =>
{
return(str);
});
if (leftJoinIn.Count() > 0)
{
this._leftJoin = leftJoinIn;
}
var query = db.Query("select")
.Table(this._table);
if (fieldIn.Apply("get") && fieldIn.GetValue() == null)
{
query.Get(this._value + " as value")
.Get("COUNT(*) as count")
.GroupBy(this._value);
}
// Loop over fields
for (int i = 0; i < fields.Count(); i++)
{
if (http.searchPanes.ContainsKey(fields[i].Name()))
{
// Apply Or where based upon searchPanes selections
query.Where(qu =>
{
foreach (var opt in http.searchPanes[fields[i].Name()])
{
qu.OrWhere(fields[i].Name(), opt, "=");
}
});
}
}
_PerformLeftJoin(query);
var res = query.Exec()
.FetchAll();
var q = db.Query("select")
.Distinct(true)
.Table(this._table)
.Get(_label + " as label")
.Get(this._value + " as value")
.Get("COUNT(*) as total")
.GroupBy(this._value)
.Where(this._where);
_PerformLeftJoin(q);
var rows = q
.Exec()
.FetchAll();
// Create output object with all of the SearchPaneOptions
List <Dictionary <string, object> > output = new List <Dictionary <string, object> >();
for (int i = 0, ien = rows.Count(); i < ien; i++)
{
bool set = false;
for (int j = 0; j < res.Count(); j++)
{
if (res[j]["value"].ToString() == rows[i]["value"].ToString())
{
output.Add(new Dictionary <string, object> {
{ "label", formatter(rows[i]["label"].ToString()) },
{ "total", rows[i]["total"] },
{ "value", rows[i]["value"].ToString() },
{ "count", res[j]["count"] }
});
set = true;
}
}
// If it has not been set then there aren't any so set count to 0
if (!set)
{
output.Add(new Dictionary <string, object> {
{ "label", formatter(rows[i]["label"].ToString()) },
{ "total", rows[i]["total"] },
{ "value", rows[i]["value"].ToString() },
{ "count", 0 }
});
}
}
if (_order == null)
{
output.Sort((a, b) => a["label"].ToString().CompareTo(b["label"].ToString()));
}
return(output.ToList());
}
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* Internal methods
*/
/// <summary>
/// Execute the configuration, getting the SearchPaneOptions from the database and formatting
/// for output.
/// </summary>
/// <param name="fieldIn">Field that the SearchPane options are to be found for</param>
/// <param name="editor">Editor Instance</param>
/// <param name="leftJoinIn">List of LeftJoins to be performed</param>
/// <param name="http">DTRequest Instance for where conditions</param>
/// <param name="fields">Array of all of the other fields</param>
/// <returns>List of SearchPaneOptions</returns>
internal List <Dictionary <string, object> > Exec(Field fieldIn, Editor editor, List <LeftJoin> leftJoinIn, DtRequest http, Field[] fields)
{
var db = editor.Db();
string _label;
if (this._value == null)
{
this._value = fieldIn.DbField();
}
if (this._table == null)
{
var readTable = editor.ReadTable();
if (readTable.Count() == 0)
{
this._table = editor.Table()[0].ToString();
}
else
{
this._table = readTable[0];
}
}
if (this._label == null)
{
_label = this._value;
}
else
{
_label = this._label.First();
}
// Just return the label if no default renderer
var formatter = _renderer ?? (str =>
{
return(str);
});
if (leftJoinIn.Count() > 0)
{
this._leftJoin = leftJoinIn;
}
var query = db.Query("select")
.Table(this._table);
// The last pane to have a selection runs a slightly different query
var queryLast = db.Query("select")
.Table(this._table);
if (fieldIn.Apply("get") && fieldIn.GetValue() == null)
{
query.Get(this._value + " as value")
.Get("COUNT(*) as count")
.GroupBy(this._value);
queryLast.Get(this._value + " as value")
.Get("COUNT(*) as count")
.GroupBy(this._value);
}
// Loop over fields - for cascade
for (int i = 0; i < fields.Count(); i++)
{
if (http.searchPanes.ContainsKey(fields[i].Name()))
{
// Apply Or where based upon searchPanes selections
query.Where(qu => {
for (int j = 0; j < http.searchPanes[fields[i].Name()].Count(); j++)
{
qu.OrWhere(
fields[i].Name(),
http.searchPanes_null.ContainsKey(fields[i].Name()) && http.searchPanes_null[fields[i].Name()][j] ?
null :
http.searchPanes[fields[i].Name()][j],
"="
);
}
});
}
}
// If there is a last value set then a slightly different set of results is required for cascade
// That panes results are based off of the results when only considering the selections of all of the others
if (http.searchPanesLast != null)
{
// Loop over fields - for cascade
for (int i = 0; i < fields.Count(); i++)
{
if (http.searchPanes.ContainsKey(fields[i].Name()) && fields[i].Name() != http.searchPanesLast)
{
// Apply Or where based upon searchPanes selections
queryLast.Where(qu => {
for (int j = 0; j < http.searchPanes[fields[i].Name()].Count(); j++)
{
qu.OrWhere(
fields[i].Name(),
http.searchPanes_null.ContainsKey(fields[i].Name()) && http.searchPanes_null[fields[i].Name()][j] ?
null :
http.searchPanes[fields[i].Name()][j],
"="
);
}
});
}
}
}
_PerformLeftJoin(query);
_PerformLeftJoin(queryLast);
var res = query.Exec()
.FetchAll();
var resLast = queryLast.Exec()
.FetchAll();
var q = db.Query("select")
.Distinct(true)
.Table(this._table)
.Get(_label + " as label")
.Get(this._value + " as value")
.Get("COUNT(*) as total")
.GroupBy(this._value)
.Where(this._where);
_PerformLeftJoin(q);
var rows = q
.Exec()
.FetchAll();
// Create output object with all of the SearchPaneOptions
List <Dictionary <string, object> > output = new List <Dictionary <string, object> >();
for (int i = 0, ien = rows.Count(); i < ien; i++)
{
bool set = false;
// Send slightly different results if this is the last pane
if (http.searchPanesLast != null && fieldIn.Name() == http.searchPanesLast)
{
for (int j = 0; j < resLast.Count(); j++)
{
if (resLast[j]["value"].ToString() == rows[i]["value"].ToString())
{
output.Add(new Dictionary <string, object> {
{ "label", formatter(
(rows[i]["label"] is DBNull) ? null : rows[i]["label"].ToString()
) },
{ "total", rows[i]["total"] },
{ "value", rows[i]["value"] is DBNull ? null : rows[i]["value"].ToString() },
{ "count", resLast[j]["count"] }
});
set = true;
}
}
}
else
{
for (int j = 0; j < res.Count(); j++)
{
if (res[j]["value"].ToString() == rows[i]["value"].ToString())
{
output.Add(new Dictionary <string, object> {
{ "label", formatter(
(rows[i]["label"] is DBNull) ? null : rows[i]["label"].ToString()
) },
{ "total", rows[i]["total"] },
{ "value", rows[i]["value"] is DBNull ? null : rows[i]["value"].ToString() },
{ "count", res[j]["count"] }
});
set = true;
}
}
}
// If it has not been set then there aren't any so set count to 0
if (!set)
{
output.Add(new Dictionary <string, object> {
{ "label", formatter(
(rows[i]["label"] is DBNull) ? null : rows[i]["label"].ToString()) },
{ "total", rows[i]["total"] },
{ "value", rows[i]["value"] is DBNull ? null : rows[i]["value"].ToString() },
{ "count", 0 }
});
}
}
if (_order == null)
{
string emptyStringa = "";
string emptyStringb = "";
output.Sort((a, b) => (a["label"] == null && b["label"] == null) ?
emptyStringa.CompareTo(emptyStringb) :
(a["label"] == null) ?
emptyStringa.CompareTo(b["label"].ToString()) :
(b["label"] == null) ?
a["label"].ToString().CompareTo(emptyStringb) :
a["label"].ToString().CompareTo(b["label"].ToString())
);
}
return(output.ToList());
}
