public bool Save_ReportingDefinition(ref ReportDefinition RD, string user)
{
try
{
if (RD.id == 0) // New Report
{
SQL.Query = string.Format(SQL.SQLQuery.Reporting_SaveReportingDefinition, SQL.ParseInput(RD.title), SQL.ParseInput(user), JsonConvert.SerializeObject(RD.Description), SQL.ParseInput(RD.query), SQL.ParseInput(RD.language), SQL.ParseInput(RD.columns));
SQL.cmd = SQL.Command(SQL.Data);
RD.id = Convert.ToInt32(SQL.cmd.ExecuteScalar());
}
else
{ // Update Report
SQL.Query = string.Format(SQL.SQLQuery.Reporting_UpdateReportingDefinition, RD.id, SQL.ParseInput(RD.title), JsonConvert.SerializeObject(RD.Description), SQL.ParseInput(RD.query), SQL.ParseInput(RD.language), SQL.ParseInput(RD.columns), SQL.ParseInput(user));
SQL.cmd = SQL.Command(SQL.Data);
SQL.cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
SQL.WriteLog_Error(ex, "Save/Update failed on id:"+RD.id+" :(", "UIFS.Reporting.Save_ReportingDefinition()");
return false;
}
return true;
}
// -------------------------------------------------------------------
// --[ Begin Report Data Outputs
/* /-------[ function: Output_DataTables ]-------\
| Creates HTML and Javascript for the "DataTables" jquery plugin
| * we can specify column settings: width,
|
\-------------------------------------------------------------------/
*/
public string Output_DataTables(ReportDefinition RD)
{
string html ="",js="",data="";
//. build table
html = "<table cellpadding='0' cellspacing='0' border='0' class='display' id='Reporting_DataTables'>" +
"<thead><tr>";
string[] columns = RD.columns.Split(new char[] { ',' });
for (int t = 0; t < columns.Length; t++)
{
html = html + "<th>" + columns[t] + "</th>";
}
html = html + "</tr></thead>"+
"<tbody></tbody></table>";
//. get data!
SQL.Query=RD.query;
SQL.cmd=SQL.Command(SQL.Data);
SQL.sdr = SQL.cmd.ExecuteReader();
if (SQL.sdr.HasRows)
{
while (SQL.sdr.Read())
{
data = data + "[";
for (int t = 0; t < columns.Length; t++)
{
if (!SQL.sdr.IsDBNull(t))
{
data = data + JsonConvert.SerializeObject(SQL.sdr[t].ToString()) + ",";
}
else { data += "\"\","; }
}
data = data.Remove(data.Length - 1, 1) + "],";
}
data = data.Remove(data.Length - 1, 1);
}
SQL.sdr.Close();
js = "<script type='text/javascript'>" +
"$('#Reporting_DataTables').dataTable( {" +
"'aoColumnDefs': [ { 'bSearchable': false, 'bVisible': false, 'aTargets': [0,1] }]," + // 0 would be the id field...
"'bJQueryUI':true," + // use jquery ui theme
"'sPaginationType': 'full_numbers'," + // for the paging navigation (either full or 2 arrows)
"'aLengthMenu': [[25, 50, 100, 200, -1], [25, 50, 100, 200, 'All']]," +
"'sScrollY':'250px'," + // MUST set our height to keep this thing under control!
"'sScrollX':'100%'," + // Set width to container size...add scrollbar in table
"'sHeightMatch': 'none'," + // do not let calculate row height...for faster display
//"'sAjaxSource': 'ajax.aspx?cmd=300.1'," +
// The next line adds a row click function (allows selection multiple)
"'fnInitComplete': function () {$('tr').click(function () {if ($(this).hasClass('row_selected')) $(this).removeClass('row_selected'); else $(this).addClass('row_selected'); }); }," +
"'aaData':["+data+"]"+
"});" +
"SubjectTable = $('#DataTables_FormSelect').dataTable();" +
"</script>";
return html+js;
}
/* /-------[ function: Output_Aggregation ]-------\
| Can output HTML with javascript at the end.
| We use the plugin: jqplot
|
\-------------------------------------------------------------------/
*/
public string Output_Aggregation(ReportDefinition RD)
{
string html = "",js="";
AggregationDisplay ADisplay = new AggregationDisplay();
if (RD.Description.Aggregates != null)
{
html = "<table cellpadding='0' cellspacing='0' border='0' class='display'>" +
"<thead></thead>";
for (int t = 0; t < RD.Description.Aggregates.Length; t++)
{
ReportDefinition.Aggregate RDA = RD.Description.Aggregates[t];
DateTime DT;
SQL.Query = RDA.query;
SQL.cmd = SQL.Command(SQL.Data);
//. query may return different data depending on the datatype
try
{
switch (RDA.manipulation)
{
case "CNT":
switch (RDA.datatype)
{
case "bit":
// we expect false (0) first
SQL.sdr = SQL.cmd.ExecuteReader(); SQL.sdr.Read();
if (SQL.sdr.HasRows)
{
ADisplay.CNT_Bool_False = SQL.sdr.GetInt32(0);
ADisplay.CNT_Bool_True = SQL.sdr.GetInt32(1);
}
// TODO: depending on Yes/No, On/Off...
//html = html + "<tr><td>HOW MANY: <span class='title'>" + RDA.title + "</span></td><td>YES=" + ADisplay.CNT_Bool_True + "; NO=" + ADisplay.CNT_Bool_False + "</td></tr>";
html = html + "<tr><td colspan='2'>"+
"<div id='piechart_" + t.ToString() + "' style='width:300px; height:180px; margin-left:auto;margin-right:auto; '></div>" +
"</td></tr>";
//. build jqplot data and script
js = js + "jqdata = [['YES ("+ADisplay.CNT_Bool_True+")',"+ADisplay.CNT_Bool_True+"],['NO ("+ADisplay.CNT_Bool_False+")',"+ADisplay.CNT_Bool_False+"]];";
js = js + "$.jqplot('piechart_"+t.ToString()+"', [jqdata], {seriesDefaults: {renderer:$.jqplot.PieRenderer,"+
"rendererOptions:{padding:10,sliceMargin:4, startAngle:-90, showDataLabels:true}},"+
"legend: {show:true},"+
"title: {text:'HOW MANY: " + RDA.title + "',textColor:'#FFDEAD'}" +
" });";
SQL.sdr.Close();
break;
default:
ADisplay.AggrValue = Convert.ToInt32(SQL.cmd.ExecuteScalar());
html = html + "<tr><td>A COUNT of: " + RDA.title + "</td><td>" + ADisplay.AggrValue + "</td></tr>";
break;
}
break;
case "SUM":
ADisplay.AggrValue = Convert.ToInt32(SQL.cmd.ExecuteScalar());
html = html + "<tr><td>A SUM of: " + RDA.title + "</td><td>" + ADisplay.AggrValue + "</td></tr>";
break;
case "AVG":
ADisplay.AggrValue = Convert.ToInt32(SQL.cmd.ExecuteScalar());
html = html + "<tr><td>AVERAGE of: " + RDA.title + "</td><td>" + ADisplay.AggrValue + "</td></tr>";
break;
// -- DATETIME RANGES
// : Time Ranges should only display Hours, Minutes (Days are invalid here)
// : Date & Time Ranges can display Days, Hours, Minutes...
case "SUM_DTRANGE":
DT = Convert.ToDateTime(SQL.cmd.ExecuteScalar());
html = html + "<tr><td>SUM of: " + RDA.title + "</td><td>Days: " + DT.Day +", Hours: "+DT.Hour+", Minutes: "+DT.Minute+ "</td></tr>";
break;
case "SUM_DRANGE":
DT = Convert.ToDateTime(SQL.cmd.ExecuteScalar());
html = html + "<tr><td>SUM of: " + RDA.title + "</td><td>Days: " + DT.Day + "</td></tr>";
break;
case "SUM_TRANGE":
DT = Convert.ToDateTime(SQL.cmd.ExecuteScalar());
html = html + "<tr><td>SUM of: " + RDA.title + "</td><td>Hours: " + DT.Hour + ", Minutes: " + DT.Minute + "</td></tr>";
break;
case "AVG_DTRANGE":
DT = Convert.ToDateTime(SQL.cmd.ExecuteScalar());
html = html + "<tr><td>AVERAGE of: " + RDA.title + "</td><td>Days: " + DT.Day + ", Hours: " + DT.Hour + ", Minutes: " + DT.Minute + "</td></tr>";
break;
case "AVG_DRANGE":
DT = Convert.ToDateTime(SQL.cmd.ExecuteScalar());
html = html + "<tr><td>AVERAGE of: " + RDA.title + "</td><td>Days: " + DT.Day + "</td></tr>";
break;
case "AVG_TRANGE":
DT = Convert.ToDateTime(SQL.cmd.ExecuteScalar());
html = html + "<tr><td>AVERAGE of: " + RDA.title + "</td><td>Hours: " + DT.Hour + ", Minutes: " + DT.Minute + "</td></tr>";
break;
}
}
catch (Exception ex)
{
SQL.WriteLog_Error(ex, "Could not read data with: " + SQL.Query, "UIFS.Reporting.Output_Aggregation()");
if (!SQL.sdr.IsClosed) { SQL.sdr.Close(); }
html = html + "<tr><td>ERROR:" + ex.Message + "<br/>" + ex.StackTrace + "</td></tr>";
}
}
html = html + "</table>";
}
return html +"<script type='text/javascript'>" + js + "</script>";
}
public ReportDefinition Load_ReportingDefinition(long id)
{
ReportDefinition RD = new ReportDefinition();
try
{
SQL.Query = string.Format(SQL.SQLQuery.Reporting_LoadReportingDefinition,id);
SQL.cmd = SQL.Command(SQL.Data);
SQL.sdr = SQL.cmd.ExecuteReader();SQL.sdr.Read();
if (SQL.sdr.HasRows) {
RD.id = id;
RD.title = SQL.sdr.GetString(0);
RD.Description = (ReportDefinition.Subject)JsonConvert.DeserializeObject(SQL.sdr.GetString(1),typeof(ReportDefinition.Subject));
}
else { RD=null; // no data
}
SQL.sdr.Close();
}
catch (Exception ex)
{
SQL.WriteLog_Error(ex, "load failed", "UIFS.Reporting.Load_ReportingDefinition()");
return null;
}
return RD;
}
/* /-------[ class: FormControl_formatqueryandlanguage ]-------\
| based on UIFS.ControlType, we will have different ways to build
| a query. This being the standardized routine
\-------------------------------------------------------------------/
*/
public void FormControl_formatqueryandlanguage(UIFS.ControlType ctrltype, UIFS.FormControl Control, ReportDefinition.Detail detail, ref string Query, ref string Language)
{
string[] selections;
ReportingSubject.Detail RSDetail = new ReportingSubject.Detail(); // used to pass data for building queries
switch (ctrltype)
{
// All of the following return a single string values
case ControlType.Textbox:
case ControlType.DateTime:
case ControlType.List:
RSDetail.db="[" + Control.id.ToString() + "]";
RSDetail.lang=detail.lang;
RSDetail.name = Control.name;
RSDetail.type="text";
Query = Query_FormatDetail(RSDetail, detail.selection, ref Language);
break;
// The following return a single numeric values
case ControlType.Percentage:
case ControlType.Number:
RSDetail.db="[" + Control.id.ToString() + "]";
RSDetail.lang=detail.lang;
RSDetail.name = Control.name;
RSDetail.type="number";
Query = Query_FormatDetail(RSDetail, detail.selection, ref Language);
break;
// Checkbox controls are always true/false with an optional input field
case ControlType.Checkbox:
RSDetail.db="[" + Control.id.ToString() + "]";
RSDetail.lang=detail.lang;
RSDetail.name = Control.name;
RSDetail.type="bit";
Query = Query_FormatDetail(RSDetail, detail.selection, ref Language);
break;
// Ranges have start/end values
//TODO: this type
case ControlType.Range:
switch (Language)
{
case "IS BETWEEN":
selections = detail.selection.Split(new char[] { ',' });
//Query = "[" + Control.id + "_Start]>=" + selections[0] + " AND [" + Control.id + "_End]<='" + selections[1] + "'";
break;
}
break;
}
}
public ReportShow[] ReportShowing; // a list of all possible data field manipulations for report output
#endregion Fields
#region Constructors
//. Need to "build the GUI" by creating all the possible ReportConditions
// then, we can save to Mem and access the dynamic controls, individually, without reloading unnecessary data
// this happens in Subject_Set();
public GraphicalUserInterface(ReportDefinition RD)
{
this.RD = RD; // save our ReportDefinition
}