// ---------------------------------------------------------------------------------------------------------------
/* -- Form.DB_CreateDataTable --
// ---------------------------------------------------------------------------------------------------------------
This routine requires that the FormData is loaded from the db pointing to an existing form and its controls.
* */
public bool DB_CreateDataTable(FormDataStruct FormData)
{
string CreateTableQuery = "", TableColumns = "";
/* Columns ARE created/named by their id#.
The current version of the form will always have its controls represented by a column name of the control id#.
Controls may need more than one column to store their data. These will be named as so: [id#]_[controldataname]
VERSIONING: Any controls that get changed and force a new version will:
1) Rename the current column(s) with the [id#]_[version#]
2) Create a new column with the id#
NOTES:
This way no data is ever lost and can be accessed by version# for reporting, etc. This method of storing dynamic
form data is the most efficient since it uses a single table per form. It is not very complicated compared to
some other possible methods.
REASONS:
- Columns are always nullable because of expiring versions. This means all data verification needs to happen
outside of the db scope.
*/
try
{
// Now we iterate through the controls to create the columns
// Now we will walk through all the controls that exist, in order
for (int i = 0; i < FormData.ControlList.Length; i++)
{
// TEMPLATE: TableColumns=TableColumns+", ["+column_name+"] "+column_type+" NULL";
// Get the query language for the creation of each column, building the string...
TableColumns = TableColumns + ", " + DB_FormControl_ColumnCreation(FormData.ControlList[i].type, FormData.Get_Control(FormData.ControlList[i].id)) + "\n";
}
CreateTableQuery = "CREATE TABLE dbo.[UIFS.Form_" + FormData.id + "]( [id] [bigint] IDENTITY(1,1) NOT NULL \n"
+ ", [created] [datetime] NOT NULL CONSTRAINT [DF_Form_" + FormData.id + "created] DEFAULT (getdate()) \n"
+ ", [version] [smallint] NOT NULL \n"
+ TableColumns
+ ",CONSTRAINT [PK_Form_" + FormData.id + "Entry] PRIMARY KEY CLUSTERED ([id] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]"
;
// DB work
SQL.Query = CreateTableQuery; // Create the new table for the form
SQL.cmd = SQL.Command(SQL.Data);
SQL.cmd.ExecuteNonQuery();
return true;
}
catch (Exception ex)
{
SQL.WriteLog_Error(ex, "Could not create the Form's data table", "UIFS.Form.DB_CreateDataTable");
return false;
}
}
/** --| PopulateForm_js
* ---------- (based from: FormInput.GetInput_js
* = outputs javascript to populate a form based on data input
* | using Form_Input.InputValue[] same as Save routines..staying consistent here for now
* | this Form_Input.InputValue[] is populated from the db via Load()
*/
public string PopulateForm_js(FormDataStruct FormData, Form_Input.InputValue[] FormValues)
{
UIFS.FormControl Control;
string script = "";
int iFormInputValue;
// build a query string for each control's value
foreach (FormDataStruct.ControlListDetail CtrlDetail in FormData.ControlList)
{
Control = FormData.Get_Control(CtrlDetail.id);
iFormInputValue = -1;
iFormInputValue = FormInputValue_findbyID(ref FormValues, Control.id);
// IF we do not find a value, ignore control
if (iFormInputValue != -1)
{
switch (CtrlDetail.type)
{
// All of the following controls we can get their value directly from the Control object
case ControlType.Textbox:
case ControlType.Percentage:
case ControlType.Number:
case ControlType.DateTime:
script = script + "$('#Control_" + Control.id + "').val('" + FormValues[iFormInputValue].value + "');";
break;
case ControlType.List:
UIFS.FormControl.List L = (UIFS.FormControl.List)Control;
switch (L.type)
{
case FormControl.List.listtype.radio:
//script = script + "$('#Control_" + Control.id + "').filter('[value=" + FormValues[iFormInputValue].value + "]').prop('checked',true);";
script = script + "$('input[name=Control_" + Control.id + "]').filter('[value=" + FormValues[iFormInputValue].value + "]').prop('checked',true);";
break;
case FormControl.List.listtype.dropdown:
case FormControl.List.listtype.slider:
script = script + "$('#Control_" + Control.id + "').val('" + FormValues[iFormInputValue].value + "');";
break;
}
break;
// This checkbox control is single (not grouped) and can have its state checked without having to filter through an array
// This checkbox control may have an attached text input field
case ControlType.Checkbox:
UIFS.FormControl.Checkbox CB = (UIFS.FormControl.Checkbox)Control; //FormData.Get_Control(CtrlDetail.id);;
switch (CB.type)
{
case FormControl.Checkbox.checkboxtype.standard:
script = script + "$('#Control_" + Control.id + "').prop('checked'," + FormValues[iFormInputValue].value.ToLower() + ");";
break;
case FormControl.Checkbox.checkboxtype.OnOff:
case FormControl.Checkbox.checkboxtype.YesNo:
script = script + "$('#Control_" + Control.id + "').val('" + FormValues[iFormInputValue].value + "');";
break;
}
if (CB.hasinput)
{
script = script + "$('#Control_" + Control.id + "_input').val('" + FormValues[iFormInputValue].input + "');";
}
break;
// Ranges have a *Start and *End set of values
case ControlType.Range:
UIFS.FormControl.Range Range = (UIFS.FormControl.Range)Control;
switch (Range.type)
{
case FormControl.Range.Rangetype.DateRange:
case FormControl.Range.Rangetype.DateTimeRange:
case FormControl.Range.Rangetype.TimeRange:
script = script + "$('#Control_" + Control.id + "_Start').val('" + FormValues[iFormInputValue].Start + "');";
script = script + "$('#Control_" + Control.id + "_End').val('" + FormValues[iFormInputValue].End + "');";
break;
case FormControl.Range.Rangetype.Currency:
case FormControl.Range.Rangetype.MinMax:
script = script + "$('#Control_" + Control.id + "_Start').val('" + FormValues[iFormInputValue].Start + "');";
script = script + "$('#Control_" + Control.id + "_End').val('" + FormValues[iFormInputValue].End + "');";
break;
}
break;
}
}
}
return script;
}
// ---------------------------------------------------------------------------------------------------------------
/* -- FormOutput.LoadData --
// ---------------------------------------------------------------------------------------------------------------
| returns saved data on a specific form/version
| NOTE: expects data retrieved to match loaded form version controls via UIFS.Form.Load()
| -- this means that the user application should "know" and have loaded the right version of the Form..
*/
public Form_Input.InputValue[] LoadData(FormDataStruct FormData, ref UIFS.SQL SQL, long formid)
{
Form_Input.InputValue[] FormValues = new Form_Input.InputValue[FormData.ControlList.Length];
UIFS.FormControl Control;
string query_from = "FROM [UIFS.Form_" + FormData.id + "]";
string query_select = "SELECT ";
string query_where = "WHERE [id]="+formid.ToString();
string ctrl_dbver = "";
int ctrl_currentversion;
try
{
//. build a select statement from controls loaded
// ?: do we need to parse values?
// 1) For each control for the form: build insert statement with correct column names: id_ver
// 2) and find the value(s) for the control from the form data and add to VALUES part of query
for (int t = 0; t < FormData.ControlList.Length; t++)
{
Control = FormData.Get_Control(FormData.ControlList[t].id);
//. determine if this control is the latest version or older..
SQL.Query = string.Format(SQL.SQLQuery.Form_ControlcurrentVersion, FormData.id, Control.id);
SQL.cmd = SQL.Command(SQL.Data);
ctrl_currentversion = Convert.ToInt32(SQL.cmd.ExecuteScalar());
if (Control.version != ctrl_currentversion)
{ // NOT CURRENT version, specific column name required
ctrl_dbver = "_"+Control.version.ToString();
}
else { ctrl_dbver = ""; }
switch (FormData.ControlList[t].type)
{
// All of the following return a single string values
case ControlType.Textbox:
case ControlType.DateTime:
case ControlType.List:
query_select = query_select + "[" + Control.id + ctrl_dbver + "],";
break;
// The following return a single numeric values
case ControlType.Percentage:
case ControlType.Number:
query_select = query_select + "[" + Control.id + ctrl_dbver + "],";
break;
// Checkbox controls are always true/false with an optional input field
case ControlType.Checkbox:
UIFS.FormControl.Checkbox CB = (UIFS.FormControl.Checkbox)Control;
if (CB.hasinput)
{
query_select = query_select + "[" + Control.id + ctrl_dbver + "], [" + Control.id + ctrl_dbver + "_text],"; ;
}
else
{
query_select = query_select + "[" + Control.id + ctrl_dbver + "],";
}
break;
// Ranges have start/end values
case ControlType.Range:
query_select = query_select + "[" + Control.id + ctrl_dbver + "_Start], [" + Control.id + ctrl_dbver + "_End],";
break;
}
}
// dbread
SQL.Query = query_select.Substring(0, query_select.Length - 1) + " " + query_from+" " +query_where;
SQL.cmd = SQL.Command(SQL.Data);
SQL.sdr = SQL.cmd.ExecuteReader();
SQL.sdr.Read();
if (SQL.sdr.HasRows)
{
int fieldcount = 0;
// Get data in the SAME ORDER as query
for (int t = 0; t < FormData.ControlList.Length; t++)
{
Control = FormData.Get_Control(FormData.ControlList[t].id);
FormValues[t] = new Form_Input.InputValue();
FormValues[t].Controlid = Control.id;
switch (FormData.ControlList[t].type)
{
case ControlType.DateTime:
UIFS.FormControl.DateTime DT = (FormControl.DateTime)Control;
switch (DT.type)
{
case FormControl.DateTime.datetimetype.date:
FormValues[t].value = SQL.sdr.GetDateTime(fieldcount).ToString("MM/dd/yyyy");
break;
case FormControl.DateTime.datetimetype.datetime:
FormValues[t].value = SQL.sdr.GetDateTime(fieldcount).ToString("MM/dd/yyyy hh:mmtt");
break;
case FormControl.DateTime.datetimetype.time:
FormValues[t].value = SQL.sdr.GetDateTime(fieldcount).ToString("hh:mmtt");
break;
}
break;
// All of the following return a single string values
case ControlType.Textbox:
case ControlType.List:
//. FILTER text...
FormValues[t].value = LoadData_parsetextinput(SQL.sdr.GetString(fieldcount));
break;
// The following return numeric values
case ControlType.Percentage:
FormValues[t].value = SQL.sdr.GetByte(fieldcount).ToString();
break;
case ControlType.Number:
FormValues[t].value = SQL.sdr.GetDecimal(fieldcount).ToString();
break;
// Checkbox controls are always true/false with an optional input field
case ControlType.Checkbox:
UIFS.FormControl.Checkbox CB = (UIFS.FormControl.Checkbox)Control;
if (CB.hasinput)
{
FormValues[t].value = SQL.sdr.GetBoolean(fieldcount).ToString();
fieldcount += 1; // advance to next value
FormValues[t].input = LoadData_parsetextinput(SQL.sdr.GetString(fieldcount));
}
else
{
FormValues[t].value = SQL.sdr.GetBoolean(fieldcount).ToString();
}
break;
// Ranges have start/end values
case ControlType.Range:
UIFS.FormControl.Range Range = (UIFS.FormControl.Range)Control;
switch (Range.type)
{
case FormControl.Range.Rangetype.Currency:
case FormControl.Range.Rangetype.MinMax:
FormValues[t].Start = SQL.sdr.GetDecimal(fieldcount).ToString();
fieldcount += 1; // advance to next value
FormValues[t].End = SQL.sdr.GetDecimal(fieldcount).ToString();
break;
case FormControl.Range.Rangetype.DateRange:
FormValues[t].Start = SQL.sdr.GetDateTime(fieldcount).ToString("MM/dd/yyyy");
fieldcount += 1; // advance to next value
FormValues[t].End = SQL.sdr.GetDateTime(fieldcount).ToString("MM/dd/yyyy");
break;
case FormControl.Range.Rangetype.DateTimeRange:
FormValues[t].Start = SQL.sdr.GetDateTime(fieldcount).ToString("MM/dd/yyyy hh:mmtt");
fieldcount += 1; // advance to next value
FormValues[t].End = SQL.sdr.GetDateTime(fieldcount).ToString("MM/dd/yyyy hh:mmtt");
break;
case FormControl.Range.Rangetype.TimeRange:
// our Any+Time plugin requires the date in a SPECIFIC format
FormValues[t].Start = SQL.sdr.GetDateTime(fieldcount).ToString("hh:mmtt");
fieldcount += 1; // advance to next value
FormValues[t].End = SQL.sdr.GetDateTime(fieldcount).ToString("hh:mmtt");
break;
}
break;
}
fieldcount += 1; // advance to next value
}
}
SQL.sdr.Close();
return FormValues;
}
catch (Exception ex)
{
SQL.WriteLog_Error(ex, "Error retrieving saved form data: " + SQL.Query, "UIFS.FormOutput.LoadData");
return null;
}
}
/** --| HTML
* ----------
* = This is the default system designed HTML output.
* It uses standards for naming all the elements so that the elements can be manipulated via javascript (after the fact) if desired.
* A js function UIFS_ValidateForm() is dynamically created based on the form fields
* | Each control is placed inside its own div:
*
*/
/* Format into HTML for user input/display */
public void HTML(FormDataStruct FormData, ref string HTML, ref string Javascript)
{
HTML = ""; Javascript = ""; // MAKE SURE our return vars are clear as we are going to work with them directly
string jsUIFS_ValidateForm = "this.UIFS_ValidateForm = function() {var valid=true;";
string jsForm_CtrlMonitoring = ""; // for applying events to the controls so we can track which ones have been "touched"
int currentColumn = 1;
bool CtrlRequired = true;
// Setup HTML
HTML = HTML + "<div id='ControlColumn_1'>"; // start with column #1
// Walk through all controls in display order
for (int cnt = 1; cnt <= FormData.controls; cnt++)
{
int iControl = FormData.Find_ControlListEntry_byOrdernum(cnt); // This finds the control with the order number of [cnt]
if (FormData.ControlList[iControl].removed) { continue; } // skip deleted controls (only live form editing)
// All controls are in order starting with column 1 then 2 then 3...
// so we can just walk through them all
switch (FormData.Layout.OutputFormat)
{
case Layout.Style.DIVs:
// NO EXTRA layout design
break;
case Layout.Style.SingleColumn:
if (cnt > 1) { HTML = HTML + "</td></tr>"; } // Close out previous
HTML = HTML + "<tr><td>";
break;
case Layout.Style.DoubleColumn:
if (cnt > 1) { HTML = HTML + "</td></tr>"; } // Close out previous
if (FormData.ControlList[iControl].Layout.column > currentColumn)
{ // start a new column!
HTML = HTML + "</div><div id='ControlColumn_" + FormData.ControlList[iControl].Layout.column+"'>"; }
else
{ // continue with elements
HTML = HTML + "<tr><td>";
}
currentColumn = FormData.ControlList[iControl].Layout.column;
break;
}
//. Create Control div
switch (FormData.ControlList[iControl].type) {
case ControlType.Checkbox:
HTML_FormControl(FormData.ControlList[iControl].type, FormData.Checkbox[FormData.ControlList[iControl].index], ref HTML, ref Javascript);
CtrlRequired = FormData.Checkbox[FormData.ControlList[iControl].index].required;
break;
case ControlType.DateTime:
HTML_FormControl(FormData.ControlList[iControl].type, FormData.DateTime[FormData.ControlList[iControl].index], ref HTML, ref Javascript);
CtrlRequired = FormData.DateTime[FormData.ControlList[iControl].index].required;
break;
case ControlType.Number:
HTML_FormControl(FormData.ControlList[iControl].type, FormData.Number[FormData.ControlList[iControl].index], ref HTML, ref Javascript);
CtrlRequired = FormData.Number[FormData.ControlList[iControl].index].required;
break;
case ControlType.Percentage:
HTML_FormControl(FormData.ControlList[iControl].type, FormData.Percentage[FormData.ControlList[iControl].index], ref HTML, ref Javascript);
CtrlRequired = FormData.Percentage[FormData.ControlList[iControl].index].required;
break;
case ControlType.List:
HTML_FormControl(FormData.ControlList[iControl].type, FormData.List[FormData.ControlList[iControl].index], ref HTML, ref Javascript);
CtrlRequired = FormData.List[FormData.ControlList[iControl].index].required;
break;
case ControlType.Range:
HTML_FormControl(FormData.ControlList[iControl].type, FormData.Range[FormData.ControlList[iControl].index], ref HTML, ref Javascript);
CtrlRequired = FormData.Range[FormData.ControlList[iControl].index].required;
break;
case UIFS.ControlType.Textbox:
HTML_FormControl(FormData.ControlList[iControl].type, FormData.Textbox[FormData.ControlList[iControl].index], ref HTML, ref Javascript);
CtrlRequired = FormData.Textbox[FormData.ControlList[iControl].index].required;
break;
}
//. Add this control to the validate function
jsUIFS_ValidateForm = jsUIFS_ValidateForm + "if (! UIFS_ValidateControl('Control_" + FormData.ControlList[iControl].id + "','" + FormData.ControlList[iControl].type.ToString() + "'," + CtrlRequired.ToString().ToLower() + ") ) { valid=false; }\n";
}
// End div
HTML = HTML + "</div>";
jsUIFS_ValidateForm = jsUIFS_ValidateForm +
"if (!valid){alert('You have incomplete items, please review the items in red'); return false;}"+
"return true; };\n";
Javascript = Javascript + jsUIFS_ValidateForm + jsForm_CtrlMonitoring; // add to our returned js
}
/* /-------[ 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;
}
}
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;
}
/* /---[ 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;
}
public string GetInput_js(FormDataStruct FormData)
{
UIFS.FormControl Control;
script = "var query = ''";
// build a query string for each control's value
foreach (FormDataStruct.ControlListDetail CtrlDetail in FormData.ControlList) {
Control = FormData.Get_Control(CtrlDetail.id);
switch (CtrlDetail.type)
{
// SECURITY WARNING: be sure to 'escape' those values that are passed in
// All of the following controls we can get their value directly from the Control object
case ControlType.Textbox:
case ControlType.Percentage:
case ControlType.Number:
case ControlType.DateTime:
script = script + "+'&c_" + Control.id + "='+escape($('#Control_" + Control.id + "').val())";
break;
case ControlType.List:
UIFS.FormControl.List L = (UIFS.FormControl.List)Control;
switch (L.type)
{
case FormControl.List.listtype.radio:
script = script + "+'&c_" + Control.id + "='+escape($('input[name=Control_" + Control.id + "]:checked').val())";
break;
case FormControl.List.listtype.dropdown:
case FormControl.List.listtype.slider:
script = script + "+'&c_" + Control.id + "='+escape($('#Control_" + Control.id + "').val())";
break;
}
break;
// This checkbox control is single (not grouped) and can have its state checked without having to filter through an array
// This checkbox control may also have an attached text input field
case ControlType.Checkbox:
UIFS.FormControl.Checkbox CB = (UIFS.FormControl.Checkbox)Control; //FormData.Get_Control(CtrlDetail.id);;
switch (CB.type)
{
case FormControl.Checkbox.checkboxtype.standard:
script = script + "+'&c_" + Control.id + "='+$('#Control_" + Control.id + "').prop('checked')";
break;
case FormControl.Checkbox.checkboxtype.OnOff:
case FormControl.Checkbox.checkboxtype.YesNo:
script = script + "+'&c_" + Control.id + "='+escape($('#Control_" + Control.id + "').val())";
break;
}
if (CB.hasinput)
{
script = script + "+'&c_" + Control.id + "_I='+escape($('#Control_" + Control.id + "_input').val())";
}
break;
// Ranges have a *Start and *End set of values
case ControlType.Range:
script = script + "+'&c_" + Control.id + "_S='+escape($('#Control_" + Control.id + "_Start').val())";
script = script + "+'&c_" + Control.id + "_E='+escape($('#Control_" + Control.id + "_End').val())";
break;
}
}
script = script + ";";
return script;
}
// ---------------------------------------------------------------------------------------------------------------
/* -- Form.DB_CreateForm --
// ---------------------------------------------------------------------------------------------------------------
Creates the new form adding appropriate db entries
*/
public bool DB_CreateForm(ref FormDataStruct FormData)
{
FormControl Control = new FormControl();
try
{
// Create new Form entry
SQL.Query = string.Format(SQL.SQLQuery.Form_Create, 1, SQL.ParseInput(FormData.name), SQL.ParseInput(FormData.description), WindowsIdentity.GetCurrent().Name);
SQL.cmd = SQL.Command(SQL.Data);
// Assign the new form its id
FormData.id = Convert.ToInt32(SQL.cmd.ExecuteScalar());
// create all the controls
for (int t = 0; t < FormData.ControlList.Length; t++)
{
switch (FormData.ControlList[t].type)
{
case ControlType.Textbox:
Control = FormData.Textbox[FormData.ControlList[t].index];
break;
case ControlType.List:
Control = FormData.List[FormData.ControlList[t].index];
break;
case ControlType.Checkbox:
Control = FormData.Checkbox[FormData.ControlList[t].index];
break;
case ControlType.DateTime:
Control = FormData.DateTime[FormData.ControlList[t].index];
break;
case ControlType.Number:
Control = FormData.Number[FormData.ControlList[t].index];
break;
case ControlType.Percentage:
Control = FormData.Percentage[FormData.ControlList[t].index];
break;
case ControlType.Range:
Control = FormData.Range[FormData.ControlList[t].index];
break;
}
// Get query and execute
SQL.Query = DB_FormControl_AddQuery(Control, FormData.ControlList[t].type, FormData.id);
SQL.cmd = SQL.Command(SQL.Data);
SQL.cmd.ExecuteNonQuery();
}
return true;
}
catch (Exception ex)
{
SQL.WriteLog_Error(ex, "Could not create a new form: " + SQL.Query, "UIFS.Form.DB_CreateForm");
return false;
}
}
// ---------------------------------------------------------------------------------------------------------------
/* -- Form.Validate --
// ---------------------------------------------------------------------------------------------------------------
checks the form controls for any possible issues (use before saving)
*/
public bool Validate(ref FormDataStruct FormData, ref string Message)
{
for (int t = 0; t < FormData.ControlList.Length; t++)
{
switch (FormData.ControlList[t].type)
{
case ControlType.List:
UIFS.FormControl.List LControl;
LControl=(UIFS.FormControl.List)FormData.Get_Control(FormData.ControlList[t].id);
if (LControl.Items.Length == 0) { Message = "You need to add some items to this *List* Control: "+LControl.name; return false; }
break;
}
}
return true;
}
// ---------------------------------------------------------------------------------------------------------------
/* -- Form.Save --
// ---------------------------------------------------------------------------------------------------------------
/* -- This routine is run after form controls have been changed via the designer and the user chooses the 'Save' option
* -- It can be an initial save or an update
*
* -- Designed to build a Query, test it in a controlled transaction first, then committ
*/
public bool Save(ref FormDataStruct FormData)
{
FormControl Control = new FormControl();
bool increaseVersion = false;
string DBSaveQuery = "";
// 1) Check to see if form has been created: if zero, this is a new form
if (FormData.id != 0)
{ // form exists
// 1a) Cycle through all controls and see which ones have changed
for (int t = 0; t < FormData.ControlList.Length; t++)
{
// 1a.1) Update changed controls (not new/added controls)
if (FormData.ControlList[t].controlchanged && !FormData.ControlList[t].added && !FormData.ControlList[t].removed)
{
// DB_UpdateControl will perform the functions needed
Control = FormData.Get_Control(FormData.ControlList[t].id);
// Add Control update routine to query
DBSaveQuery = DBSaveQuery + DB_UpdateControl(FormData.ControlList[t].type, Control, FormData.ControlList[t].newversionneeded, FormData.id) + "\n";
if (FormData.ControlList[t].newversionneeded) { increaseVersion = true; }
}
}
// 2a) Cycle through all controls...find additions...find deletions
for (int t = 0; t < FormData.ControlList.Length; t++)
{
Control = FormData.Get_Control(FormData.ControlList[t].id);
// 2a.1) New Controls!
if (FormData.ControlList[t].added)
{
increaseVersion = true;
// Query to add FormControl data
DBSaveQuery = DBSaveQuery + DB_FormControl_AddQuery(Control, FormData.ControlList[t].type, FormData.id) + "\n";
// Query to update form table by adding a new column for this control
DBSaveQuery = DBSaveQuery + string.Format(SQL.SQLQuery.Form_UpdateControl_ColumnAdd, FormData.id, DB_FormControl_ColumnCreation(FormData.ControlList[t].type, Control)) + "\n";
}
else
{
// 2a.2) Removed Controls
if (FormData.ControlList[t].removed)
{
increaseVersion = true;
// Build query and execute
DBSaveQuery = DBSaveQuery + string.Format(SQL.SQLQuery.Form_UpdateControl_Deactivate,
FormData.id, FormData.ControlList[t].id, Control.version)+"\n";
}
}
}
// TEST SAVE
try
{
//BEGIN TRAN UIFS_Update \n BEGIN TRY " + Query_NewControlVersion + "\n" + Query_OldControlDeactivate + "\n" + Query_DataTableUpdate + "\n COMMIT TRAN UIFS_Update \n END TRY \n BEGIN CATCH\nROLLBACK TRAN UIFS_Update\nDECLARE @ErrMsg NVARCHAR(4000);DECLARE @ErrSev INT;DECLARE @ErrState INT; SELECT @ErrMsg = ERROR_MESSAGE(),@ErrSev = ERROR_SEVERITY(),@ErrState = ERROR_STATE(); RAISERROR (@ErrMsg,@ErrSev,@ErrState); \n END CATCH ";
SQL.Query = "BEGIN TRAN UIFS_Update \n BEGIN TRY " +DBSaveQuery+
"\n COMMIT TRAN UIFS_Update \n END TRY \n BEGIN CATCH\nROLLBACK TRAN UIFS_Update\nDECLARE @ErrMsg NVARCHAR(4000);DECLARE @ErrSev INT;DECLARE @ErrState INT; SELECT @ErrMsg = ERROR_MESSAGE(),@ErrSev = ERROR_SEVERITY(),@ErrState = ERROR_STATE(); RAISERROR (@ErrMsg,@ErrSev,@ErrState); \n END CATCH ";
SQL.cmd = SQL.Command(SQL.Data);
SQL.cmd.ExecuteNonQuery();
//-- SUCCESS! --
try
{
// if a version change is required:
if (increaseVersion) { FormData.version += 1; }
// Update the main form details: name, version, lastmodifiedby
SQL.Query = string.Format(SQL.SQLQuery.Form_Update, FormData.id, SQL.ParseInput(FormData.name), SQL.ParseInput(FormData.description), FormData.version, WindowsIdentity.GetCurrent().Name);
SQL.cmd = SQL.Command(SQL.Data);
SQL.cmd.ExecuteNonQuery();
// This runs last, if version changed
if (increaseVersion)
{
// Create version history..(This is created immediately after form changes to preserve a record of form and form control's states)
SQL.Query = string.Format(SQL.SQLQuery.Form_VersionHistory_Create, FormData.id);
SQL.cmd = SQL.Command(SQL.Data);
SQL.cmd.ExecuteNonQuery();
}
SQL.WriteLog("Saved changes to form: "+FormData.id.ToString());
}
catch (Exception ex)
{
SQL.WriteLog_Error(ex, "Failed to update version and/or version history...", "UIFS.Form.Save()");
}
}
catch (Exception ex) { // -- SAVE FAILED :( boo
SQL.WriteLog_Error(ex,"SAVING FORM FAILED: "+DBSaveQuery,"UIFS.Form.Save()");
return false;
}
}
else
{ // 1b.1) New Form, create db entries for the form and its controls, create a table for the data
try
{
if (!DB_CreateForm(ref FormData)) { return false; }
if (!DB_CreateDataTable(FormData)) { return false; }
// Create version history..
SQL.Query = string.Format(SQL.SQLQuery.Form_VersionHistory_Create, FormData.id);
SQL.cmd = SQL.Command(SQL.Data);
SQL.cmd.ExecuteNonQuery();
SQL.WriteLog("Created new form!");
}
catch (Exception ex)
{
SQL.WriteLog_Error(ex,"Failed to create a new form! ","UIFS.Form.Save()");
}
}
// update any other components, layout,etc.
return true;
}
// -- This routine will load the form data from the db and put it in the referenced FormData
// formversion :: set to -1 to get LATEST version
public bool Load(int formid, int formversion, ref FormDataStruct FormData)
{
try
{
FormData = null; // Clear out just in case
FormData = new FormDataStruct();
int iControl = 0;
bool reOrderControls = false; // set to true to perform a complete reordering of control order#s
//: 1) Load main form data
SQL.Query = string.Format(SQL.SQLQuery.Form_Load, formid);
SQL.cmd = SQL.Command(SQL.Data);
SQL.sdr = SQL.cmd.ExecuteReader();
SQL.sdr.Read();
FormData.id = formid;
FormData.version = SQL.sdr.GetInt16(0);
FormData.name = SQL.sdr.GetString(1);
FormData.description = SQL.sdr.GetString(2);
FormData.created = SQL.sdr.GetDateTime(3);
SQL.sdr.Close();
//: 2) Load controls list
if (formversion != -1)
{ // retrieves the controls for a specific version of the form
// required: reordering!
reOrderControls = true;
SQL.Query = string.Format(SQL.SQLQuery.Form_LoadControlList_byversion, formid, formversion);
}
else { // retrieves the latest version of this form
SQL.Query = string.Format(SQL.SQLQuery.Form_LoadControlList, formid);
}
// This query actually only returns the common control data which we will use to create the control list.
// With the list we will then walk through all control types with specific control type queries to load the control data.
SQL.cmd = SQL.Command(SQL.Data);
SQL.sdr = SQL.cmd.ExecuteReader();
while (SQL.sdr.Read())
{
FormData.controls += 1;
Array.Resize(ref FormData.ControlList, FormData.controls);
FormData.ControlList[FormData.controls - 1] = new FormDataStruct.ControlListDetail();
FormData.ControlList[FormData.controls - 1].id = SQL.sdr.GetInt16(0);
FormData.ControlList[FormData.controls - 1].type = (ControlType)SQL.sdr.GetInt32(1);
FormData.ControlList[FormData.controls - 1].ordernum = SQL.sdr.GetInt16(2);
FormData.ControlList[FormData.controls - 1].version = SQL.sdr.GetInt16(3);
}
SQL.sdr.Close();
//: 3) Load Controls
for (int i = 0; i < FormData.ControlList.Length; i++)
{
switch (FormData.ControlList[i].type)
{
case ControlType.Textbox:
FormControl.Textbox newTextBox = new FormControl.Textbox();
newTextBox.id = FormData.ControlList[i].id; // copy id
newTextBox.version = FormData.ControlList[i].version;
// Load rest of data from db
SQL.Query = string.Format(SQL.SQLQuery.Form_LoadControl_Textbox, formid, FormData.ControlList[i].id, FormData.ControlList[i].version);
SQL.cmd = SQL.Command(SQL.Data);
SQL.sdr = SQL.cmd.ExecuteReader(); SQL.sdr.Read();
newTextBox.name = SQL.sdr.GetString(0);
newTextBox.prompt = SQL.sdr.GetString(1);
if (!SQL.sdr.IsDBNull(2)) { newTextBox.tip = SQL.sdr.GetString(2); }
newTextBox.ordernum = SQL.sdr.GetInt16(3);
newTextBox.required = SQL.sdr.GetBoolean(4);
newTextBox.lines = SQL.sdr.GetInt32(5);
newTextBox.width = SQL.sdr.GetInt32(6);
newTextBox.FullText = SQL.sdr.GetBoolean(7);
SQL.sdr.Close();
// Add new control to control array and get index
iControl = FormData.AddControl(ControlType.Textbox, newTextBox, false);
FormData.ControlList[i].index = iControl; // record index
break;
case ControlType.List:
string ListOptions = "";
string[] ListOption;
int iItem; // index of current item...
FormControl.List newList = new FormControl.List();
newList.id = FormData.ControlList[i].id; // copy id
newList.version = FormData.ControlList[i].version;
SQL.Query = string.Format(SQL.SQLQuery.Form_LoadControl_List, formid, FormData.ControlList[i].id, FormData.ControlList[i].version);
SQL.cmd = SQL.Command(SQL.Data);
SQL.sdr = SQL.cmd.ExecuteReader(); SQL.sdr.Read();
newList.name = SQL.sdr.GetString(0);
newList.prompt = SQL.sdr.GetString(1);
if (!SQL.sdr.IsDBNull(2)) { newList.tip = SQL.sdr.GetString(2); }
newList.ordernum = SQL.sdr.GetInt16(3);
newList.required = SQL.sdr.GetBoolean(4);
ListOptions = SQL.sdr.GetString(5);
newList.type = (FormControl.List.listtype)SQL.sdr.GetByte(6);
SQL.sdr.Close();
// Load Option names/values
ListOption = ListOptions.Split(new char[] { ',' });
Array.Resize(ref newList.Items, ListOption.Length);
for (int t = 0; t < ListOption.Length; t++)
{
newList.Items[t] = new FormControl.List.Item();
iItem = ListOption[t].IndexOf(":");
newList.Items[t].name = ListOption[t].Substring(0, iItem);
newList.Items[t].value = ListOption[t].Substring(iItem + 1);
}
iControl = FormData.AddControl(ControlType.List, newList, false);
FormData.ControlList[i].index = iControl; // Set index value in control list for faster search/display
break;
case ControlType.Checkbox:
FormControl.Checkbox newCheckbox = new FormControl.Checkbox();
newCheckbox.id = FormData.ControlList[i].id; // copy id
newCheckbox.version = FormData.ControlList[i].version;
// Load rest of data from db
SQL.Query = string.Format(SQL.SQLQuery.Form_LoadControl_Checkbox, formid, FormData.ControlList[i].id, FormData.ControlList[i].version);
SQL.cmd = SQL.Command(SQL.Data);
SQL.sdr = SQL.cmd.ExecuteReader(); SQL.sdr.Read();
newCheckbox.name = SQL.sdr.GetString(0);
newCheckbox.prompt = SQL.sdr.GetString(1);
if (!SQL.sdr.IsDBNull(2)) { newCheckbox.tip = SQL.sdr.GetString(2); }
newCheckbox.ordernum = SQL.sdr.GetInt16(3);
newCheckbox.required = SQL.sdr.GetBoolean(4);
newCheckbox.type = (FormControl.Checkbox.checkboxtype)SQL.sdr.GetByte(5);
newCheckbox.initialstate = SQL.sdr.GetBoolean(6);
newCheckbox.hasinput = SQL.sdr.GetBoolean(7);
SQL.sdr.Close();
// Add new control to control array and get index
iControl = FormData.AddControl(ControlType.Checkbox, newCheckbox, false);
FormData.ControlList[i].index = iControl; // record index
break;
case ControlType.DateTime:
FormControl.DateTime newDateTime = new FormControl.DateTime();
newDateTime.id = FormData.ControlList[i].id; // copy id
newDateTime.version = FormData.ControlList[i].version;
// Load rest of data from db
SQL.Query = string.Format(SQL.SQLQuery.Form_LoadControl_DateTime, formid, FormData.ControlList[i].id, FormData.ControlList[i].version);
SQL.cmd = SQL.Command(SQL.Data);
SQL.sdr = SQL.cmd.ExecuteReader(); SQL.sdr.Read();
newDateTime.name = SQL.sdr.GetString(0);
newDateTime.prompt = SQL.sdr.GetString(1);
if (!SQL.sdr.IsDBNull(2)) { newDateTime.tip = SQL.sdr.GetString(2); }
newDateTime.ordernum = SQL.sdr.GetInt16(3);
newDateTime.required = SQL.sdr.GetBoolean(4);
newDateTime.type = (FormControl.DateTime.datetimetype)SQL.sdr.GetByte(5);
SQL.sdr.Close();
// Add new control to control array and get index
iControl = FormData.AddControl(ControlType.DateTime, newDateTime, false);
FormData.ControlList[i].index = iControl; // record index
break;
case ControlType.Number:
FormControl.Number newNumber = new FormControl.Number();
newNumber.id = FormData.ControlList[i].id; // copy id
newNumber.version = FormData.ControlList[i].version;
// Load rest of data from db
SQL.Query = string.Format(SQL.SQLQuery.Form_LoadControl_Number, formid, FormData.ControlList[i].id, FormData.ControlList[i].version);
SQL.cmd = SQL.Command(SQL.Data);
SQL.sdr = SQL.cmd.ExecuteReader(); SQL.sdr.Read();
newNumber.name = SQL.sdr.GetString(0);
newNumber.prompt = SQL.sdr.GetString(1);
if (!SQL.sdr.IsDBNull(2)) { newNumber.tip = SQL.sdr.GetString(2); }
newNumber.ordernum = SQL.sdr.GetInt16(3);
newNumber.required = SQL.sdr.GetBoolean(4);
newNumber.min = (decimal)SQL.sdr[5];
newNumber.max = (decimal)SQL.sdr[6];
newNumber.interval = (decimal)SQL.sdr[7];
newNumber.slider = SQL.sdr.GetBoolean(8);
SQL.sdr.Close();
// Add new control to control array and get index
iControl = FormData.AddControl(ControlType.Number, newNumber, false);
FormData.ControlList[i].index = iControl; // record index
break;
case ControlType.Percentage:
FormControl.Percentage newPercentage = new FormControl.Percentage();
newPercentage.id = FormData.ControlList[i].id; // copy id
newPercentage.version = FormData.ControlList[i].version;
// Load rest of data from db
SQL.Query = string.Format(SQL.SQLQuery.Form_LoadControl_Percentage, formid, FormData.ControlList[i].id, FormData.ControlList[i].version);
SQL.cmd = SQL.Command(SQL.Data);
SQL.sdr = SQL.cmd.ExecuteReader(); SQL.sdr.Read();
newPercentage.name = SQL.sdr.GetString(0);
newPercentage.prompt = SQL.sdr.GetString(1);
if (!SQL.sdr.IsDBNull(2)) { newPercentage.tip = SQL.sdr.GetString(2); }
newPercentage.ordernum = SQL.sdr.GetInt16(3);
newPercentage.required = SQL.sdr.GetBoolean(4);
newPercentage.interval = SQL.sdr.GetInt32(5);
SQL.sdr.Close();
// Add new control to control array and get index
iControl = FormData.AddControl(ControlType.Percentage, newPercentage, false);
FormData.ControlList[i].index = iControl; // record index
break;
case ControlType.Range:
FormControl.Range newRange = new FormControl.Range();
newRange.id = FormData.ControlList[i].id; // copy id
newRange.version = FormData.ControlList[i].version;
// Load rest of data from db
SQL.Query = string.Format(SQL.SQLQuery.Form_LoadControl_Range, formid, FormData.ControlList[i].id, FormData.ControlList[i].version);
SQL.cmd = SQL.Command(SQL.Data);
SQL.sdr = SQL.cmd.ExecuteReader(); SQL.sdr.Read();
newRange.name = SQL.sdr.GetString(0);
newRange.prompt = SQL.sdr.GetString(1);
if (!SQL.sdr.IsDBNull(2)) { newRange.tip = SQL.sdr.GetString(2); }
newRange.ordernum = SQL.sdr.GetInt16(3);
newRange.required = SQL.sdr.GetBoolean(4);
newRange.type = (FormControl.Range.Rangetype)SQL.sdr.GetByte(5);
newRange.min = (decimal)SQL.sdr[6];
newRange.max = (decimal)SQL.sdr[7];
SQL.sdr.Close();
// Add new control to control array and get index
iControl = FormData.AddControl(ControlType.Range, newRange, false);
FormData.ControlList[i].index = iControl; // record index
break;
}
}
//: 4) Get next available control id
SQL.Query = string.Format(SQL.SQLQuery.Form_GetNextAvailableControlID, formid);
SQL.cmd = SQL.Command(SQL.Data);
FormData.nextcontrolid = Convert.ToInt32(SQL.cmd.ExecuteScalar());
//: 5) reorder Control list if needed
if (reOrderControls)
{
FormData.ReOrder_ControlList();
}
//: ?) what is next?
FormData.newform = false; // This is not a new form.
}
catch (Exception ex)
{ // Failed to load form
ErrorEx = ex;
return false;
}
return true;
}
