public void aReportOn_UIFSForm(int formid)
{
UIFS.Form Form = new Form(ref SQL);
UIFS.FormDataStruct FormData = new FormDataStruct();
SQL.OpenDatabase();
// TODO: diff form ver?
if (!Form.Load(formid,-1, ref FormData))
{ // failed to load, end
SQL.WriteLog_Error(Form.ErrorEx, "failed to load specified formdata:"+formid.ToString(), "UIFS.Reporting.aReportOn_UIFSForm()");
}
SQL.CloseDatabase();
aReportOn = "Form: "+FormData.name;
}
/* /-------[ function: BuildReport ]-------\
| Currently, this will build Query and Language strings
| These could be saved at the time the report is created since they will not change
| Then used to get data, etc.
\-------------------------------------------------------------------/
*/
// So, a report is based on a "Subject" that has its specific "Details" (we have our special "Form" subject and direct form info from the using app)
// Optionally, we can link in other "Subjects" with their specific "Details" (those subjects that ARE related/linked)
public bool BuildReport(ref UIFS.ReportDefinition ReportDefinition)
{
string Query = "",Q_FieldSelection="SELECT ", Q_Subject="", Q_Details=" WHERE ", Q_Joins="";
string columnnames = "";
string Language = "";
string[] selections;
string Temp_Query="",Temp_Language="";
int iFormLinkdetail, iReportingSubject; // index holders
ReportingSubject.Detail RSDetail = new ReportingSubject.Detail(); // used to pass data for building queries
string FormDbName = ""; // SINGULAR forms will set this. Used in Aggregate query building
try
{
// --] LOAD Needed Data [--
Load_ReportingSubjects();
Q_Subject = " FROM [" + FormLinks.TableName + "]"; // Our Subject clause always starts from the main table
//. Add required id fields
Q_FieldSelection = Q_FieldSelection + "[" + FormLinks.TableName + "].[UIFS_formid],[" + FormLinks.TableName + "].[formid],";
columnnames = columnnames + "_UIFS_formid,_formid,";
//. Need to add in all linked form fields, but they will (can) be hidden from the output (used for easy linking)
foreach (FormLink.Detail FLdetail in FormLinks.Details)
{
Q_FieldSelection += "["+ FormLinks.TableName+"].["+ FLdetail.field+ "],";
columnnames += FLdetail.name.Replace(",", "") + ","; // make sure no commas in field name
}
//. Start with the Main Subject
switch (ReportDefinition.Description.lang)
{
case "ALL": // all forms
break;
case "singular": // single form
//. load the form details
UIFS.Form Form = new Form(ref SQL);
UIFS.FormDataStruct FormData = new FormDataStruct(); // hold our data
// TODO: diff form version loading here...
if (!Form.Load(Convert.ToInt32(ReportDefinition.Description.selection),-1, ref FormData))
{ // failed to load, end
SQL.WriteLog_Error(Form.ErrorEx, "failed to load specified formdata", "UIFS.Reporting.BuildReport()");
return false;
}
FormDbName = "[UIFS.Form_" + FormData.id.ToString() + "]";
//. Construct a field selection clause based on all fields in this form
// TODO: if report is on a different version other than the current form version...need to specify that.
// Can get the form version by querying the chosen form....
//. build list of fields to extract, default is all
/* NOTES:
* - not sure if we want to use the user selected fields, or just have everything in the data output
*/
// WARNING: make sure to remove comma's from fieldnames as we use this as our list separator.
// we do this with: .Replace(",", "");
string fieldid = "", fieldname="";
foreach (FormDataStruct.ControlListDetail formctrl in FormData.ControlList)
{
switch (formctrl.type)
{
case ControlType.Range: // TWO FIELDS/VALUES/COLUMNS
fieldid = FormDbName + ".[" + formctrl.id.ToString() + "_Start]," + FormDbName + ".[" + formctrl.id.ToString() + "_End],";
fieldname = FormData.Get_Control(formctrl.id).name.Replace(",", "") + "[FROM]," + FormData.Get_Control(formctrl.id).name.Replace(",", "")+"[TO]";
break;
case ControlType.Checkbox: // checkbox can have an INPUT field...
fieldid = FormDbName + ".[" + formctrl.id.ToString() + "],";
fieldname = FormData.Get_Control(formctrl.id).name.Replace(",", "");
break;
case ControlType.Textbox:
case ControlType.Number:
case ControlType.List:
case ControlType.DateTime:
case ControlType.Percentage:
fieldid = FormDbName + ".[" + formctrl.id.ToString() + "],";
fieldname = FormData.Get_Control(formctrl.id).name.Replace(",", "");
break;
}
Q_FieldSelection += fieldid;
columnnames += fieldname + ",";
}
Q_FieldSelection = Q_FieldSelection.Remove(Q_FieldSelection.Length - 1); // take out last comma
//. link form table by formid from formlinks main table (standard practice)
Q_Joins = Q_Joins + " INNER JOIN " + FormDbName + " ON " + FormDbName + ".[id] = [" + FormLinks.TableName+"].[formid] AND ";
Language = "A report that shows for this '" + ReportDefinition.Description.name + "': <span class='detail'>" + FormData.name + "</span><br/>";
if (ReportDefinition.Description.Details != null)
{
//. walk through the non-[global] details that are form specific
foreach (ReportDefinition.Detail detail in ReportDefinition.Description.Details)
{
if (detail.name.StartsWith("[global]")) { continue; } // skip globals (dealt with later)
// GET the assigned name/id for this Detail
// NOTE: since we are working with [Form] Subject data, this is actually an identifier(int)
UIFS.FormControl detail_ctrl = FormData.Get_Control(Convert.ToInt32(detail.name)); // so, get Form->Control name
//. specific query building for UIFS.FormControl
FormControl_formatqueryandlanguage(FormData.ControlList[FormData.Find_ControlListEntry_byControlID(Convert.ToInt32(detail.name))].type, detail_ctrl, detail, ref Temp_Query, ref Temp_Language);
//. from returned vars, build query and language
Q_Joins = Q_Joins + FormDbName + "." + Temp_Query; // select using [form].[detail]
Language = Language + Temp_Language;
}
}
if (Q_Joins.EndsWith("ON ")) { Q_Joins = Q_Joins.Remove(Q_Joins.Length - 3); } // this would occur if no detailed selection was made..."no detail filter"
else { if (Q_Joins.EndsWith("AND ")) { Q_Joins = Q_Joins.Remove(Q_Joins.Length - 5); } }
Q_Details = Q_Details + " [" + FormLinks.TableName + "].UIFS_formid=" + FormData.id.ToString() + " AND ";
// the query detail string always adds an "AND " after each addition
break;
case "plural": // multiple forms
Q_Details = Q_Details + " [UIFS_formid] IS IN (";
selections = ReportDefinition.Description.selection.Split(new char[] { ',' });
foreach (string specificdetail in selections)
{
Q_Details = Q_Details + specificdetail + ",";
}
Q_Details = Q_Details.Remove(Q_Details.Length - 1); // remove last comma
Q_Details = Q_Details + ") AND ";
break;
}
// On Forms we have these [global] fields that the user/application has added that are linked to a specific form
// these are contained in the same user table that has the main form linking data
//. Now deal with [global] details
if (ReportDefinition.Description.Details != null)
{
foreach (ReportDefinition.Detail detail in ReportDefinition.Description.Details)
{
if (detail.name.StartsWith("[global]"))
{
//. Get
iFormLinkdetail = FormLinks.Find_detail(detail.name.Remove(0, 8)); // index of our global detail info
switch (FormLinks.Details[iFormLinkdetail].type)
{
case "Subject": // need to join data from another collection
//. find Subject info
iReportingSubject = Find_ReportingSubject(FormLinks.Details[iFormLinkdetail].name);
//. restrict our query to a selection of this subject
Q_Joins = Q_Joins + " INNER JOIN [" + ReportingSubjects[iReportingSubject].db + "]" +
" ON [" + ReportingSubjects[iReportingSubject].db + "].[" + ReportingSubjects[iReportingSubject].db_id + "]=[" + FormLinks.TableName + "]." + FormLinks.Details[iFormLinkdetail].field;
RSDetail.db = "[" + FormLinks.TableName + "].[" + FormLinks.Details[iFormLinkdetail].field + "]";
RSDetail.lang = detail.lang;
RSDetail.name = ReportingSubjects[iReportingSubject].name;
RSDetail.type = "id"; // currently fixed
Q_Details = Q_Details + Query_FormatDetail(RSDetail, detail.selection, ref Temp_Language);
Language = Language + Temp_Language;
break;
case "Detail": // this specific field data exists in the user 'FormLink' table
RSDetail.db = "[" + FormLinks.TableName + "].[" + FormLinks.Details[iFormLinkdetail].field + "]";
RSDetail.lang = detail.lang;
RSDetail.name = detail.name.Remove(0, 8);
RSDetail.type = FormLinks.Details[iFormLinkdetail].datatype;
Q_Details = Q_Details + Query_FormatDetail(RSDetail, detail.selection, ref Temp_Language);
Language = Language + Temp_Language;
break;
}
}
}
}
//. Details cleanup
if (Q_Details == "WHERE ") { Q_Details = ""; }
else
{ // remove last " AND " from query (if exists)
if (Q_Details.EndsWith("AND ")) { Q_Details = Q_Details.Remove(Q_Details.Length - 5); }
}
//. put the query together
Query = Q_FieldSelection + Q_Subject + Q_Joins + Q_Details;
//. Assign/Push
ReportDefinition.query = Query;
ReportDefinition.language = Language;
ReportDefinition.columns = columnnames.Remove(columnnames.Length-1); // Q_FieldSelection.Substring(7);
// --[ AGGREGATION ]--
//
//. build a query for each type of aggregation the user wants
string AggrTable = "", AggrQuery = "",AggrGroup="";
ReportDefinition.Aggregate RDA;
if (ReportDefinition.Description.Aggregates != null)
{
for (int t = 0; t < ReportDefinition.Description.Aggregates.Length; t++)
{
RDA = ReportDefinition.Description.Aggregates[t];
//. find our field
AggrTable = ""; AggrQuery = ""; AggrGroup = "";
if (RDA.title.StartsWith("[global]"))
{
AggrTable = FormLinks.TableName;
}
else
{
AggrTable = FormDbName;
}
switch (RDA.manipulation)
{
case "CNT":
switch (RDA.datatype)
{
case "bit": // boolean sum(iF(flushot,0,1)),sum(flushot)
AggrQuery = "SELECT COUNT(CASE " + AggrTable + ".[" + RDA.db + "] WHEN 0 THEN 1 ELSE null END), COUNT(CASE " + AggrTable + ".[" + RDA.db + "] WHEN 1 THEN 1 ELSE null END) ";
break;
default:
AggrQuery = "SELECT COUNT(" + AggrTable + ".[" + RDA.db + "]) ";
break;
}
break;
case "SUM":
AggrQuery = "SELECT SUM(" + AggrTable + ".[" + RDA.db + "]) ";
break;
case "AVG":
AggrQuery = "SELECT AVG(" + AggrTable + ".[" + RDA.db + "]) ";
break;
case "SUM_DTRANGE":
case "SUM_DRANGE":
case "SUM_TRANGE":
AggrQuery = "SELECT DATEADD(ms, SUM(DATEDIFF(ms,[" + RDA.db + "_Start],[" + RDA.db + "_End])),0)";
//AggrQuery = "SELECT SUM([" + RDA.db + "_End]-[" + RDA.db + "_Start])";
break;
case "AVG_DTRANGE":
case "AVG_DRANGE":
case "AVG_TRANGE":
AggrQuery = "SELECT DATEADD(ms, AVG(DATEDIFF(ms,[" + RDA.db + "_Start],[" + RDA.db + "_End])),0)";
break;
}
if (AggrQuery != "")
{
AggrQuery = AggrQuery + Q_Subject + Q_Joins + Q_Details + AggrGroup; // rest of query is same...except for grouping
RDA.query = AggrQuery;
}
}
}
// END BuildReport
return true;
}
catch (Exception ex)
{
SQL.WriteLog_Error(ex, "failed", "UIFS.Reporting.BuildReport()");
return false;
}
}
/* /---[ Subject_Set ]--------------------------------------------------------------------\
* | Loads all the ReportConditions based on Subject (does not matter if a new report or existing one)
* |
* \-------------------------------------------------------------------/
* isnew = false if loading/editing an existing RD (otherwise if a new Subject is chosen, TRUE to rebuild)
*
*/
public bool Subject_Set(bool isnew, ref FormLink Formlinks, ReportingSubject RS, ref SQL SQL, ref ReportingSubject[] ReportingSubjects)
{
bool exists = false;
ReportingSubject.Detail RSdetail;
ReportDefinition.Detail existingRDdetail = new ReportDefinition.Detail();
FormControl UIFSFormControl;
if (isnew || this.ReportConditions == null)
{
this.ReportConditions = new ReportCondition[0]; // starting over fresh...should this be possible?
}
//. setup [globals]
foreach (FormLink.Detail FLdetail in Formlinks.Details) {
int iRDDd=-1; // holds the index of the RD.Desc.detail for linking the ReportCondition to the actual RD.Description...
switch (FLdetail.type)
{
// -[ Global Subject ]-
// this behaves as a "pointer"/collection of id(s) to reference a Subject or set of Subjects to filter by...
// this is a UIFS standard, but for UX simplification (This "subject" has a corresponding id field that is unique to each form created)
case "Subject":
if (!isnew) { // check to see if exists in current RD
for (int t=0;t<RD.Description.Details.Length;t++) {
if ("[global]"+FLdetail.name == RD.Description.Details[t].name)
{
existingRDdetail = RD.Description.Details[t]; iRDDd = t;
exists = true; break;
}
}
}
RSdetail = new ReportingSubject.Detail();
RSdetail.db = FLdetail.field;
RSdetail.name = "[global]" + FLdetail.name;
RSdetail.type = "id"; // this basically tells the application that this is a linked *Subject
Array.Resize(ref ReportConditions, ReportConditions.Length + 1);
ReportConditions[ReportConditions.Length - 1] = new ReportCondition(RSdetail, ReportConditions.Length - 1);
if (exists)
{
ReportConditions[ReportConditions.Length - 1].RDdetail = existingRDdetail;
ReportConditions[ReportConditions.Length - 1].iRDDdetail = iRDDd;
exists = false; // reset
}
//. Load subject selection data
int pscount=0; int iRS;
//. Find reporting subject
for (iRS = 0; iRS < ReportingSubjects.Length; iRS++)
{
if (ReportingSubjects[iRS].name == FLdetail.name) { break; }
}
SQL.Query = ReportingSubjects[iRS].BuildQuery_IDList(); // dynamic build...
SQL.cmd = SQL.Command(SQL.Data);
SQL.sdr = SQL.cmd.ExecuteReader();
while (SQL.sdr.Read())
{
Array.Resize(ref ReportConditions[ReportConditions.Length - 1].PossibleValues, pscount+1);
ReportConditions[ReportConditions.Length - 1].PossibleValues[pscount] = new FormControl.List.Item();
ReportConditions[ReportConditions.Length - 1].PossibleValues[pscount].value = SQL.sdr[0].ToString();
ReportConditions[ReportConditions.Length - 1].PossibleValues[pscount].name = SQL.sdr[1].ToString();
pscount += 1;
}
SQL.sdr.Close();
// Report Show possibility
ReportShow_Add(RSdetail.db, RSdetail.name, RSdetail.type);
break;
case "Detail":
if (!isnew)
{ // check to see if exists in current RD
for (int t = 0; t < RD.Description.Details.Length; t++)
{
if ("[global]" + FLdetail.name == RD.Description.Details[t].name)
{
existingRDdetail = RD.Description.Details[t]; iRDDd = t;
exists = true; break;
}
}
}
RSdetail = new ReportingSubject.Detail();
RSdetail.db = FLdetail.field;
RSdetail.name = "[global]" + FLdetail.name;
RSdetail.type = FLdetail.datatype;
Array.Resize(ref ReportConditions, ReportConditions.Length + 1);
ReportConditions[ReportConditions.Length - 1] = new ReportCondition(RSdetail, ReportConditions.Length - 1);
if (exists)
{
ReportConditions[ReportConditions.Length - 1].RDdetail = existingRDdetail;
ReportConditions[ReportConditions.Length - 1].iRDDdetail = iRDDd;
exists = false; // reset
}
// Report Show possibility
ReportShow_Add(RSdetail.db, RSdetail.name, RSdetail.type);
break;
}
} // end globals
// NOTE: Right now, reporting is based off of having a UIFS.Form selection as the MAIN SUBJECT
// This means we are not setup to handle any generic *Subject as the main...here and in BuildReport()
// ONLY when a SINGLE *Form* is chosen can we use its details
if (RD.Description.lang == "singular")
{
exists = false;
//. Now we walk through our chosen "Subject"'s Details
// this is a UIFS.Form
if (RD.Description.name == "Form")
{
//. need to get data from UIFS.Form for advanced functionality
UIFS.Form UIFSForm = new Form(ref SQL);
UIFS.FormDataStruct UIFSFormData = new FormDataStruct();
DataType UIFSConvertDT = new DataType();
// TODO: diff form ver
if (!UIFSForm.Load(Convert.ToInt32(RD.Description.selection),-1, ref UIFSFormData))
{
SQL.WriteLog_Error(UIFSForm.ErrorEx, "Error loading form:" + RD.Description.selection, "UIFS.Reporting.GraphicalUserInterface.Subject_Set()");
return false;
}
//. walk through all form controls...
foreach (FormDataStruct.ControlListDetail CLD in UIFSFormData.ControlList)
{
int iRDDd = -1;
UIFSFormControl = UIFSFormData.Get_Control(CLD.id);
switch (CLD.type) {
case ControlType.Range: // Range Controls have 2 values
FormControl.Range Ctrl_Range = (FormControl.Range)UIFSFormControl;
//. create Start option
RSdetail = new ReportingSubject.Detail();
RSdetail.db = CLD.id.ToString()+"_Start";
RSdetail.name = UIFSFormControl.name+" START";
RSdetail.type = UIFSConvertDT.convertUIFS(Ctrl_Range.type.ToString());
if (!isnew) { // check to see if exists in current RD
for (int t = 0; t < RD.Description.Details.Length; t++) {
if (RSdetail.db == RD.Description.Details[t].name){
existingRDdetail = RD.Description.Details[t]; iRDDd = t;
exists = true; break;
}
}
}
Array.Resize(ref ReportConditions, ReportConditions.Length + 1);
ReportConditions[ReportConditions.Length - 1] = new ReportCondition(RSdetail, ReportConditions.Length - 1);
if (exists)
{
ReportConditions[ReportConditions.Length - 1].RDdetail = existingRDdetail;
ReportConditions[ReportConditions.Length - 1].iRDDdetail = iRDDd;
exists = false; // reset
}
//. create End option
RSdetail = new ReportingSubject.Detail();
RSdetail.db = CLD.id.ToString()+"_End";
RSdetail.name = UIFSFormControl.name +" END";
RSdetail.type = UIFSConvertDT.convertUIFS(Ctrl_Range.type.ToString());
if (!isnew) { // check to see if exists in current RD
for (int t = 0; t < RD.Description.Details.Length; t++) {
if (RSdetail.db == RD.Description.Details[t].name){
existingRDdetail = RD.Description.Details[t]; iRDDd = t;
exists = true; break;
}
}
}
Array.Resize(ref ReportConditions, ReportConditions.Length + 1);
ReportConditions[ReportConditions.Length - 1] = new ReportCondition(RSdetail, ReportConditions.Length - 1);
if (exists)
{
ReportConditions[ReportConditions.Length - 1].RDdetail = existingRDdetail;
ReportConditions[ReportConditions.Length - 1].iRDDdetail = iRDDd;
exists = false; // reset
}
// Report Show possibility
switch (Ctrl_Range.type)
{
case FormControl.Range.Rangetype.DateRange:
case FormControl.Range.Rangetype.TimeRange:
case FormControl.Range.Rangetype.DateTimeRange:
ReportShow_Add(CLD.id.ToString(), UIFSFormControl.name, "Range_DateTime");
break;
case FormControl.Range.Rangetype.MinMax:
case FormControl.Range.Rangetype.Currency:
ReportShow_Add(CLD.id.ToString(),UIFSFormControl.name,"Range_Number");//UIFSFormControl.name
break;
}
break;
case ControlType.DateTime:
FormControl.DateTime Ctrl_DateTime = (FormControl.DateTime)UIFSFormControl;
RSdetail = new ReportingSubject.Detail();
RSdetail.db = CLD.id.ToString();
RSdetail.name = UIFSFormControl.name;
RSdetail.type = UIFSConvertDT.convertUIFS(Ctrl_DateTime.type.ToString().ToLower()); // UIFS.ControlType..
if (!isnew) { // check to see if exists in current RD
for (int t = 0; t < RD.Description.Details.Length; t++) {
if (RSdetail.db == RD.Description.Details[t].name){
existingRDdetail = RD.Description.Details[t]; iRDDd = t;
exists = true; break;
}
}
}
Array.Resize(ref ReportConditions, ReportConditions.Length + 1);
ReportConditions[ReportConditions.Length - 1] = new ReportCondition(RSdetail, ReportConditions.Length - 1);
ReportConditions[ReportConditions.Length - 1].UIFSFormControl = true;
ReportConditions[ReportConditions.Length - 1].UIFSControl = UIFSFormControl;
ReportConditions[ReportConditions.Length - 1].UIFSFormControl_type = CLD.type;
if (exists)
{
ReportConditions[ReportConditions.Length - 1].RDdetail = existingRDdetail;
ReportConditions[ReportConditions.Length - 1].iRDDdetail = iRDDd;
exists = false; // reset
}
// Report Show possibility
ReportShow_Add(RSdetail.db, RSdetail.name, RSdetail.type);
break;
default:
RSdetail = new ReportingSubject.Detail();
RSdetail.db = CLD.id.ToString();
RSdetail.name = UIFSFormControl.name;
RSdetail.type = UIFSConvertDT.convertUIFS(CLD.type.ToString().ToLower()); // UIFS.ControlType..
if (!isnew) { // check to see if exists in current RD
for (int t = 0; t < RD.Description.Details.Length; t++) {
if (RSdetail.db == RD.Description.Details[t].name){
existingRDdetail = RD.Description.Details[t]; iRDDd = t;
exists = true; break;
}
}
}
Array.Resize(ref ReportConditions, ReportConditions.Length + 1);
ReportConditions[ReportConditions.Length - 1] = new ReportCondition(RSdetail, ReportConditions.Length - 1);
ReportConditions[ReportConditions.Length - 1].UIFSFormControl = true;
ReportConditions[ReportConditions.Length - 1].UIFSControl = UIFSFormControl;
ReportConditions[ReportConditions.Length - 1].UIFSFormControl_type = CLD.type;
if (exists)
{
ReportConditions[ReportConditions.Length - 1].RDdetail = existingRDdetail;
ReportConditions[ReportConditions.Length - 1].iRDDdetail = iRDDd;
exists = false; // reset
}
// Report Show possibility
ReportShow_Add(RSdetail.db,RSdetail.name,RSdetail.type);
break;
}
}
}
// NOT IMPLEMENTED
else
{ // generic Subject, use the Reporting DB
foreach (ReportingSubject.Detail RSd in RS.Details)
{
Array.Resize(ref ReportConditions, ReportConditions.Length + 1);
ReportConditions[ReportConditions.Length - 1] = new ReportCondition(RSd, ReportConditions.Length - 1);
if (!isnew)
{ // check to see if exists in current RD
foreach (ReportDefinition.Detail RDdetail in RD.Description.Details)
{
if (RSd.name == RDdetail.name)
{ // if an RD is loaded, this gives us the selection details
ReportConditions[ReportConditions.Length - 1].RDdetail = RDdetail;
break;
}
}
}
}
}
}
// END of Subject_Set()
return true;
}