private void GetQueryGridData()
{
AppState appState = AppState.FromJson(Request.Form["state"]);
Configuration.ApplicationRow application = Configuration.Application.First(o => o.ApplicationID == appState.Application);
Configuration.QueryRow query = Configuration.Query.First(o => o.QueryID == appState.Query);
List <String> zones = new List <String>();
List <String> levels = new List <String>();
if (!application.IsZoneLevelIDNull())
{
zones = application.ZoneLevelRow.GetZoneRows().Select(o => o.ZoneID).ToList();
levels = application.ZoneLevelRow.GetLevelRows().Select(o => o.LevelID).ToList();
}
Dictionary <String, Object> result = new Dictionary <String, Object>();
using (OleDbCommand command = query.GetDatabaseCommand())
{
command.Parameters[0].Value = appState.TargetIds.Join(",");
if (command.Parameters.Count > 1)
{
command.Parameters[1].Value = AppUser.GetRole();
}
using (OleDbDataReader reader = command.ExecuteReader())
{
// get the indexes of the ID columns
int mapIdColumn = reader.GetColumnIndex("MapID");
int dataIdColumn = reader.GetColumnIndex("DataID");
int zoneIdColumn = zones.Count > 0 ? reader.GetColumnIndex("ZoneID") : -1;
int levelIdColumn = levels.Count > 0 ? reader.GetColumnIndex("LevelID") : -1;
// write the column headers
List <String> headers = new List <String>();
for (int i = 0; i < reader.FieldCount; ++i)
{
if (i != mapIdColumn && i != dataIdColumn && i != zoneIdColumn && i != levelIdColumn)
{
headers.Add(reader.GetName(i));
}
}
result.Add("headers", headers);
// write the data
List <Dictionary <String, Object> > rows = new List <Dictionary <String, Object> >();
while (reader.Read())
{
if (!reader.IsDBNull(mapIdColumn) && !reader.IsDBNull(dataIdColumn))
{
Dictionary <String, String> id = new Dictionary <String, String>();
id.Add("m", reader.GetValue(mapIdColumn).ToString());
if (dataIdColumn > -1 && !reader.IsDBNull(dataIdColumn))
{
id.Add("d", reader.GetValue(dataIdColumn).ToString());
}
if (zoneIdColumn > -1 && !reader.IsDBNull(zoneIdColumn))
{
string zoneId = reader.GetValue(zoneIdColumn).ToString();
if (zones.Contains(zoneId))
{
id.Add("z", zoneId);
}
}
if (levelIdColumn > -1 && !reader.IsDBNull(levelIdColumn))
{
string levelId = reader.GetValue(levelIdColumn).ToString();
if (levels.Contains(levelId))
{
id.Add("l", levelId);
}
}
List <Object> values = new List <Object>();
for (int i = 0; i < reader.FieldCount; ++i)
{
if (i != mapIdColumn && i != dataIdColumn && i != zoneIdColumn && i != levelIdColumn)
{
values.Add(reader.IsDBNull(i) ? null : reader.GetValue(i));
}
}
Dictionary <String, Object> row = new Dictionary <String, Object>();
row.Add("id", id);
row.Add("v", values);
rows.Add(row);
}
}
result.Add("rows", rows);
}
command.Connection.Dispose();
}
ReturnJson(result);
}
public void AddFromReader(OleDbDataReader reader, bool addSpace)
{
do
{
if (reader.HasRows)
{
HtmlGenericControl resultSetDiv = CreateDiv("ResultSet");
_container.Controls.Add(resultSetDiv);
int headerColumn = reader.GetColumnIndex("Header");
string lastHeader = null;
int subheaderColumn = reader.GetColumnIndex("Subheader");
string lastSubHeader = null;
int lastColumn = reader.FieldCount - 1;
for (int i = 0; i < 2; ++i)
{
if (lastColumn == headerColumn || lastColumn == subheaderColumn)
{
lastColumn -= 1;
}
}
while (reader.Read())
{
HtmlGenericControl rowSetDiv = CreateDiv("RowSet");
resultSetDiv.Controls.Add(rowSetDiv);
bool headerAdded = false;
bool subheaderAdded = false;
if (headerColumn >= 0 && !reader.IsDBNull(headerColumn))
{
string header = reader.GetValue(headerColumn).ToString();
headerAdded = header != lastHeader;
if (headerAdded)
{
HtmlGenericControl headerDiv = CreateDiv("RowSetHeader", header);
rowSetDiv.Controls.Add(headerDiv);
lastHeader = header;
}
}
if (subheaderColumn >= 0 && !reader.IsDBNull(subheaderColumn))
{
string subheader = reader.GetValue(subheaderColumn).ToString();
subheaderAdded = subheader != lastSubHeader;
if (subheaderAdded)
{
if (headerAdded && addSpace)
{
AddSpace(rowSetDiv, "Space2");
}
HtmlGenericControl subheaderDiv = CreateDiv("RowSetSubheader", subheader);
rowSetDiv.Controls.Add(subheaderDiv);
lastSubHeader = subheader;
}
}
if (lastColumn < 0)
{
if (addSpace)
{
AddSpace(rowSetDiv, "Space2");
}
}
else
{
if ((headerAdded || subheaderAdded) && addSpace)
{
AddSpace(rowSetDiv, "Space2");
}
for (int i = 0; i < reader.FieldCount; ++i)
{
if (i != headerColumn && i != subheaderColumn)
{
HtmlGenericControl valueSetDiv = CreateDiv("ValueSet");
rowSetDiv.Controls.Add(valueSetDiv);
HtmlGenericControl labelDiv = CreateDiv("Label", reader.GetName(i));
valueSetDiv.Controls.Add(labelDiv);
if (addSpace)
{
HtmlGenericControl separatorDiv = CreateDiv("Separator", "");
valueSetDiv.Controls.Add(separatorDiv);
}
HtmlGenericControl valueDiv = CreateValueDiv(!reader.IsDBNull(i) ? reader.GetValue(i) : null);
valueSetDiv.Controls.Add(valueDiv);
if (i < lastColumn && addSpace)
{
AddSpace(rowSetDiv, "Space1");
}
}
}
if (addSpace)
{
AddSpace(rowSetDiv, "Space4");
}
}
}
}
}while (reader.NextResult());
}
private void DefaultMethod()
{
Configuration config = AppContext.GetConfiguration();
Configuration.ApplicationRow applicationRow = config.Application.First(o => o.ApplicationID == Request.Form["app"]);
Configuration.SearchRow searchRow = config.Search.First(o => o.SearchID == Request.Form["search"]);
JavaScriptSerializer serializer = new JavaScriptSerializer();
Dictionary <String, Object> criteria = serializer.Deserialize <Dictionary <String, Object> >(Request.Form["criteria"]);
List <String> levels = new List <String>();
if (!applicationRow.IsZoneLevelIDNull())
{
levels = applicationRow.ZoneLevelRow.GetLevelRows().Select(o => o.LevelID).ToList();
}
List <String> where = new List <String>();
List <Object> parameters = new List <Object>();
foreach (string criteriaID in criteria.Keys)
{
Configuration.SearchInputFieldRow searchInputFieldRow = config.SearchInputField.First(o => o.FieldID == criteriaID);
switch (searchInputFieldRow.FieldType)
{
case "autocomplete":
case "date":
case "list":
case "number":
case "text":
where.Add(searchInputFieldRow.ColumnName + " = ?");
parameters.Add(criteria[criteriaID]);
break;
case "daterange":
case "numberrange":
ArrayList values = (ArrayList)criteria[criteriaID];
if (values[0] != null)
{
where.Add(searchInputFieldRow.ColumnName + " >= ?");
parameters.Add(values[0]);
}
if (values[1] != null)
{
where.Add(searchInputFieldRow.ColumnName + " <= ?");
parameters.Add(values[1]);
}
break;
}
}
Dictionary <String, Object> result = new Dictionary <String, Object>();
using (OleDbCommand command = searchRow.GetSelectCommand())
{
command.CommandText = String.Format(command.CommandText, String.Join(" and ", where.ToArray()));
for (int i = 0; i < parameters.Count; ++i)
{
command.Parameters.AddWithValue(i.ToString(), parameters[i]);
}
using (OleDbDataReader reader = command.ExecuteReader())
{
// get the indexes of the ID columns
int mapIdColumn = reader.GetColumnIndex("MapID");
int dataIdColumn = reader.GetColumnIndex("DataID");
int levelIdColumn = levels.Count > 0 ? reader.GetColumnIndex("LevelID") : -1;
// write the column headers
List <String> headers = new List <String>();
for (int i = 0; i < reader.FieldCount; ++i)
{
if (i != mapIdColumn && i != dataIdColumn && i != levelIdColumn)
{
headers.Add(reader.GetName(i));
}
}
result.Add("headers", headers);
// write the data
List <Dictionary <String, Object> > rows = new List <Dictionary <String, Object> >();
while (reader.Read())
{
if (!reader.IsDBNull(mapIdColumn) && !reader.IsDBNull(dataIdColumn))
{
Dictionary <String, String> id = new Dictionary <String, String>();
id.Add("m", reader.GetValue(mapIdColumn).ToString());
if (dataIdColumn > -1 && !reader.IsDBNull(dataIdColumn))
{
id.Add("d", reader.GetValue(dataIdColumn).ToString());
}
if (levelIdColumn > -1 && !reader.IsDBNull(levelIdColumn))
{
string levelId = reader.GetValue(levelIdColumn).ToString();
if (levels.Contains(levelId))
{
id.Add("l", levelId);
}
}
List <Object> values = new List <Object>();
for (int i = 0; i < reader.FieldCount; ++i)
{
if (i != mapIdColumn && i != dataIdColumn && i != levelIdColumn)
{
values.Add(reader.IsDBNull(i) ? null : reader.GetValue(i));
}
}
Dictionary <String, Object> row = new Dictionary <String, Object>();
row.Add("id", id);
row.Add("v", values);
rows.Add(row);
}
}
result.Add("rows", rows);
}
}
ReturnJson(result);
}
