string script; // will hold the script to return
#endregion Fields
#region Methods
public bool Delete(int UIFSformid, long formid, ref UIFS.SQL SQL, bool test)
{
try
{
string query_delete = "";
// if bool test is set, we want to encap in a tran and roll it back.
if (test)
{
query_delete = "BEGIN TRAN UIFSTest \n";
}
//. build our query
query_delete += "DELETE FROM [UIFS.Form_" + UIFSformid.ToString() + "] WHERE [id]=" + formid.ToString();
// if test, rollback
if (test)
{
query_delete += "\n ROLLBACK TRAN UIFSTest";
}
SQL.Query = query_delete;
SQL.cmd = SQL.Command(SQL.Data);
SQL.cmd.ExecuteNonQuery();
return true;
}
catch (Exception ex)
{
SQL.WriteLog_Error(ex, "Failed to delete form: " + SQL.Query, "UIFS.FormInput.Delete");
return false;
}
}
// ---------------------------------------------------------------------------------------------------------------
/* -- 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;
}
}
// Returns the HTML formatted output for a Control, "div contained"
public void HTML_FormControl(UIFS.ControlType type, UIFS.FormControl Control, ref string HTML, ref string Javascript)
{
// Format strings for proper display/output
Control.prompt = HTML_Escape(Control.prompt);
Control.tip = HTML_Escape(Control.tip);
// Control div
HTML = HTML + "<div id='Control_" + Control.id + "_div' class='ControlType_" + type.ToString() + "' >";
switch (type)
{
case UIFS.ControlType.Textbox:
FormControl.Textbox ControlTextbox = (FormControl.Textbox)Control;
HTML = HTML + "<table><tr title='"+ControlTextbox.tip+"' class='Control_tip'>"+
"<td class='Control_prompt'>" + ControlTextbox.prompt + "</td>" +
"<td class='Control_input'>";
if (ControlTextbox.lines == 1)
{
HTML = HTML + "<input type='text' id='Control_" + ControlTextbox.id + "' size='" + ControlTextbox.width + "' /></td>";
}
else {
HTML = HTML + "<textarea id='Control_" + ControlTextbox.id + "' rows='" + ControlTextbox.lines + "' cols='" + ControlTextbox.width + "'></textarea></td>";
}
HTML = HTML + "</tr></table>";
// onblur watching...
Javascript = Javascript + "document.getElementById('Control_" + Control.id + "').onblur = function(){ FormControlsTouched.push('" + Control.id + "'); };";
break;
case UIFS.ControlType.List:
FormControl.List ControlList = (FormControl.List)Control;
HTML = HTML + "<table><tr title='" + ControlList.tip + "' class='Control_tip'>" +
"<td class='Control_prompt'>" + ControlList.prompt + "</td>";
if (ControlList.type == FormControl.List.listtype.slider) { HTML = HTML + "<td class='Control_input slider'>"; }
else { HTML = HTML + "<td class='Control_input'>"; }
switch (ControlList.type)
{
case FormControl.List.listtype.radio:
for (int t = 0; t < ControlList.Items.Length; t++)
{HTML = HTML + "<input type='radio' id='Control_" + ControlList.id + "' name='Control_" + ControlList.id +
"' value='" + ControlList.Items[t].value + "'>" + ControlList.Items[t].name + "<br/>";}
break;
case FormControl.List.listtype.dropdown:
case FormControl.List.listtype.slider:
HTML = HTML + "<select id='Control_" + ControlList.id + "' /><option value=''>-=Choose=-</option>";
for (int t = 0; t < ControlList.Items.Length; t++)
{ HTML = HTML + "<option value='" + ControlList.Items[t].value + "'>" + ControlList.Items[t].name + "</option>"; }
HTML = HTML + "</select>";
break;
}
HTML = HTML + "</td></tr></table>";
// --[ Javascript ]--
// Since the dropdown and the slider use the same html...separated this out
if (ControlList.type == FormControl.List.listtype.slider)
{
Javascript = Javascript + "$('#Control_" + ControlList.id + "').selectToUISlider();";
}
// onblur watching...
Javascript = Javascript + "document.getElementById('Control_" + Control.id + "').onblur = function(){ FormControlsTouched.push('" + Control.id + "'); };";
break;
case UIFS.ControlType.Checkbox:
FormControl.Checkbox ControlCheckbox = (FormControl.Checkbox)Control;
HTML = HTML + "<table><tr title='" + ControlCheckbox.tip + "' class='Control_tip'>" +
"<td class='Control_prompt'>" + ControlCheckbox.prompt + "</td>" +
"<td class='Control_input'>";
string initialState_checkbox="",initialState_select="",initialState_input="";
if (ControlCheckbox.initialstate)
{ // setup initial state if chosen
initialState_checkbox = " checked='1' ";
initialState_select = " selected='1' ";
initialState_input = "display: inline;";
}
else {
initialState_input = "display: none;";
}
switch (ControlCheckbox.type)
{
case FormControl.Checkbox.checkboxtype.standard:
HTML = HTML + "<input type='checkbox' id='Control_" + ControlCheckbox.id + "' "+initialState_checkbox+" onchange=\"Checkbox_Change('"+ControlCheckbox.id+"', 1) \" />";
break;
case FormControl.Checkbox.checkboxtype.YesNo:
HTML = HTML + "<select id='Control_" + ControlCheckbox.id + "' onchange=\"Checkbox_Change('" + ControlCheckbox.id + "', 2)\" /><option value='0'>No</option><option value='1' " + initialState_select + ">Yes</option></select>";
break;
case FormControl.Checkbox.checkboxtype.OnOff:
HTML = HTML + "<select id='Control_" + ControlCheckbox.id + "' onchange=\"Checkbox_Change('" + ControlCheckbox.id + "', 2)\" /><option value='0'>Off</option><option value='1' " + initialState_select + ">On</option></select>";
break;
}
if (ControlCheckbox.hasinput)
{
HTML = HTML + "<input type='text' id='Control_" + ControlCheckbox.id + "_input' size='30' style='" + initialState_input + "' />";
}
HTML = HTML + "</td></tr></table>";
// onblur watching...
Javascript = Javascript + "document.getElementById('Control_" + Control.id + "').onblur = function(){ FormControlsTouched.push('" + Control.id + "'); };";
break;
case UIFS.ControlType.DateTime:
FormControl.DateTime ControlDateTime = (FormControl.DateTime)Control;
string AnyTime_format = "", AnyTime_value="";
switch (ControlDateTime.type)
{
case FormControl.DateTime.datetimetype.datetime: // {format:'%m/%d/%Y %h:%i%p'}
AnyTime_format = "%m/%d/%Y %h:%i%p"; AnyTime_value= "1/1/1900 12:00AM";
break;
case FormControl.DateTime.datetimetype.date: // {format:'%m/%d/%Y'}
AnyTime_format = "%m/%d/%Y";AnyTime_value= "1/1/1900";
break;
case FormControl.DateTime.datetimetype.time: // {format:'%h:%i%p'}
AnyTime_format = "%h:%i%p";AnyTime_value= "12:00AM";
break;
}
HTML = HTML + "<table><tr title='" + ControlDateTime.tip + "' class='Control_tip'>" +
"<td class='Control_prompt'>" + ControlDateTime.prompt + "</td>" +
"<td class='Control_input'><input type='text' id='Control_" + ControlDateTime.id + "' size='23' class='Control_DateTime' value='"+AnyTime_value+"' onfocus=\"AnyTime.noPicker('Control_" + ControlDateTime.id + "'); $('#Control_" + ControlDateTime.id + "').AnyTime_picker({format:'" + AnyTime_format + "'}); this.onfocus=null; \" /></td>" +
"</tr></table>";
//Javascript = Javascript + "AnyTime.noPicker('Control_" + ControlDateTime.id + "');";
// Based on the type:
// Step #1: run the 'noPicker' function to remove any associated controls before creating a new AnyTime control
// Step #2: bind a function to create the picker to the *focus* event of the control (this is b/c of display issues with anytime run on hidden elements)
// onblur watching...
Javascript = Javascript + "document.getElementById('Control_" + Control.id + "').onblur = function(){ FormControlsTouched.push('" + Control.id + "'); };";
break;
case UIFS.ControlType.Number:
FormControl.Number ControlNumber = (FormControl.Number)Control;
// Standard text input field
HTML = HTML + "<table><tr title='" + ControlNumber.tip + "' class='Control_tip'>" +
"<td class='Control_prompt'>" + ControlNumber.prompt + "</td>" +
"<td class='Control_input'><input type='text' id='Control_" + ControlNumber.id + "' size='7' onchange=\"Number_Validate(this, '" + ControlNumber.min + "','" + ControlNumber.max + "','" + ControlNumber.interval + "') \" />";
if (ControlNumber.slider)
{ // Slider type control
HTML = HTML + "<span id='Control_" + ControlNumber.id + "_slider' class='Control_Number_Slider'></span>";
Javascript = Javascript + "$(function(){ $('#Control_" + ControlNumber.id + "_slider').slider({min:" + ControlNumber.min + ", max:" + ControlNumber.max + ", step:" + ControlNumber.interval +
", slide:function(event,ui){$('#Control_" + ControlNumber.id + "').val(ui.value);} }); }); ";
}
HTML = HTML + "</td></tr></table>";
// onblur watching...
Javascript = Javascript + "document.getElementById('Control_" + Control.id + "').onblur = function(){ FormControlsTouched.push('" + Control.id + "'); };";
break;
case ControlType.Percentage:
FormControl.Percentage ControlPercentage = (FormControl.Percentage)Control;
// Dropdown with values per interval
HTML = HTML + "<table><tr title='" + ControlPercentage.tip + "' class='Control_tip'>" +
"<td class='Control_prompt'>" + ControlPercentage.prompt + "</td>" +
"<td class='Control_input'><select id='Control_" + ControlPercentage.id + "'>";
for (int t = 0; t < 100 ; t+=ControlPercentage.interval)
{
HTML = HTML + "<option value='"+t.ToString()+"'>"+t.ToString()+"%</option>";
}
HTML = HTML + "<option value='100'>100%</option></select>";
// Slider control
//HTML = HTML + "<div id='Control_" + ControlPercentage.id + "_slider' class='Control_slider'></div>";
HTML = HTML + "</td></tr></table>";
//Javascript = Javascript + "$(function(){ $('#Control_" + ControlPercentage.id + "_slider').slider({min:0, max:100, step:" + ControlPercentage.interval +
// ", slide:function(event,ui){$('#Control_" + ControlPercentage.id + "').val(ui.value);} }); }); ";
Javascript = Javascript + "$('#Control_" + ControlPercentage.id + "').selectToUISlider();";
// onblur watching... (PARENT YO!)
Javascript = Javascript + "$('#Control_" + ControlPercentage.id + "').parent().find('.ui-slider').slider({ change: function(){FormControlsTouched.push('" + Control.id + "');} });";
break;
case ControlType.Range:
FormControl.Range ControlRange = (FormControl.Range)Control;
string AnyTime_datetimeformat = "", AnyTime_start = "", AnyTime_end = "", AnyTime_size="";
HTML = HTML + "<table><tr title='" + ControlRange.tip + "' class='Control_tip'>" +
"<td class='Control_prompt'>" + ControlRange.prompt + "</td>" +
"<td class='Control_input'><table>";
switch (ControlRange.type)
{
case FormControl.Range.Rangetype.TimeRange:
case FormControl.Range.Rangetype.DateRange:
case FormControl.Range.Rangetype.DateTimeRange:
switch (ControlRange.type)
{
case FormControl.Range.Rangetype.TimeRange:
AnyTime_datetimeformat = "%h:%i%p";AnyTime_start="12:00AM";AnyTime_end="12:00AM";AnyTime_size="10";
break;
case FormControl.Range.Rangetype.DateRange:
AnyTime_datetimeformat = "%m/%d/%Y";AnyTime_start="1/1/1900";AnyTime_end="1/1/1900";AnyTime_size="12";
break;
case FormControl.Range.Rangetype.DateTimeRange:
AnyTime_datetimeformat = "%m/%d/%Y %h:%i%p";AnyTime_start="1/1/1900 12:00AM";AnyTime_end="1/1/1900 12:00AM";AnyTime_size="20";
break;
}
HTML = HTML + "<tr><td class='label'>FROM</td><td class='value'><input type='text' id='Control_" + ControlRange.id + "_Start' size='" + AnyTime_size + "' class='Control_DateTime' value='" + AnyTime_start + "' " +
"onfocus=\"AnyTime.noPicker('Control_" + ControlRange.id + "_Start'); $('#Control_" + ControlRange.id + "_Start').AnyTime_picker({format:'" + AnyTime_datetimeformat + "'}); this.onfocus=null; \" /></td></tr>" +
"<tr><td class='label'>TO</td><td class='value'><input type='text' id='Control_" + ControlRange.id + "_End' size='" + AnyTime_size + "' class='Control_DateTime' value='" + AnyTime_end + "' " +
"onfocus=\"AnyTime.noPicker('Control_" + ControlRange.id + "_End'); $('#Control_" + ControlRange.id + "_End').AnyTime_picker({format:'" + AnyTime_datetimeformat + "'}); this.onfocus=null; \" /></td></tr>";
break;
// -----
// These control range types are combined...
case FormControl.Range.Rangetype.Currency:
case FormControl.Range.Rangetype.MinMax:
string Designator = "";
if (ControlRange.type == FormControl.Range.Rangetype.Currency) { Designator = "'$'+"; }
HTML = HTML + "<tr><td>From</td><td><input type='text' id='Control_" + ControlRange.id + "_Start' size='7' readonly='1' /></td>" +
"<td>To</td><td><input type='text' id='Control_" + ControlRange.id + "_End' size='7' readonly='1' /></td></tr>";
HTML = HTML + "<tr class='slider'><td colspan='4'><div id='Control_" + ControlRange.id + "_slider'></div></td></tr>";
Javascript = Javascript + "$(function(){ $('#Control_" + ControlRange.id + "_slider').slider({range: true, min:" + ControlRange.min + ", max:" + ControlRange.max + ", values: [" + ControlRange.min + "," + ControlRange.max + "]" +
", slide:function(event,ui){$('#Control_" + ControlRange.id + "_Start').val(" + Designator + "ui.values[0]); $('#Control_" + ControlRange.id + "_End').val(" + Designator + "ui.values[1]);} }); " +
"$('#Control_" + ControlRange.id + "_Start').val(" + Designator + ControlRange.min+ "); $('#Control_" + ControlRange.id + "_End').val(" + Designator + ControlRange.max+ "); }); ";
break;
}
HTML = HTML + "</table>"; // end input formatting...
HTML=HTML+"</td></tr></table>";
// onblur watching...
Javascript = Javascript + "document.getElementById('Control_" + Control.id + "_Start').onblur = function(){ FormControlsTouched.push('" + Control.id + "S'); };";
Javascript = Javascript + "document.getElementById('Control_" + Control.id + "_End').onblur = function(){ FormControlsTouched.push('" + Control.id + "E'); };";
break;
default:
break;
}
// End Control div
HTML = HTML + "</div>";
}
/* /-------[ 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;
}
}
/* /-------[ 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 InputValue[] FilterInput(System.Collections.Specialized.NameValueCollection querystring, UIFS.FormDataStruct FormData)
{
int cnt = 0;
InputValue[] IVs = new InputValue[FormData.ControlList.Length];
UIFS.FormControl Control;
foreach (UIFS.FormDataStruct.ControlListDetail CtrlDetail in FormData.ControlList)
{
Control = FormData.Get_Control(CtrlDetail.id);
IVs[cnt] = new InputValue();
IVs[cnt].Controlid = CtrlDetail.id; // set control id
// now set the correct values based on control type
switch (CtrlDetail.type)
{
case ControlType.Textbox:
case ControlType.DateTime:
case ControlType.List:
IVs[cnt].value = querystring["c_" + Control.id.ToString()];
break;
case ControlType.Percentage:
case ControlType.Number:
IVs[cnt].value = querystring["c_" + Control.id.ToString()];
if (IVs[cnt].value == "") {
IVs[cnt].value = "0"; // DEFAULT to 0 if nothing returned
}
break;
// 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);;
if (CB.hasinput)
{
IVs[cnt].value = SQLBOOL(querystring["c_" + Control.id.ToString()]);
IVs[cnt].input = querystring["c_" + Control.id.ToString()+"_I"];
}
else { IVs[cnt].value = SQLBOOL(querystring["c_" + Control.id.ToString()]);}
break;
// Ranges have a *Start and *End set of values
case ControlType.Range:
UIFS.FormControl.Range R = (UIFS.FormControl.Range)Control;
IVs[cnt].Start = querystring["c_" + Control.id.ToString() + "_S"];
IVs[cnt].End = querystring["c_" + Control.id.ToString() + "_E"];
switch (R.type) {
// numbers
case FormControl.Range.Rangetype.Currency:
case FormControl.Range.Rangetype.MinMax:
// DEFAULT to 0 if nothing returned
if (IVs[cnt].Start == ""){IVs[cnt].Start = "0"; }
if (IVs[cnt].End == ""){IVs[cnt].End = "0"; }
break;
case FormControl.Range.Rangetype.DateRange:
case FormControl.Range.Rangetype.DateTimeRange:
case FormControl.Range.Rangetype.TimeRange:
// DEFAULTs are typically set by Calendar widget..control creation..
break;
}
break;
}
cnt += 1; // increase our array counter
}
return IVs;
}
// ---------------------------------------------------------------------------------------------------------------
/* -- FormInput.EditSave --
// ---------------------------------------------------------------------------------------------------------------
| 1) updates the db record for the form with new field changes
| 2) returns a text value "LOG" entry to the user application for it to do what it wants (a basic table example is: UIFS_formid, formid, [changes text])
|
*/
public bool Update(UIFS.FormDataStruct FormData, long formid, InputValue[] FormValues_Old, InputValue[] FormValues_New, ref UIFS.SQL SQL, bool test, ref string Changes)
{
UIFS.FormControl Control;
string query_update = "UPDATE [UIFS.Form_" + FormData.id + "]";
string query_values = " SET ";
string query_where = " WHERE [id]="+formid.ToString();
int iOldCtrl, iNewCtrl, ctrl_currentversion;
string ctrl_dbver = "";
Changes = ""; // clear out first
// if bool test is set, we want to encap in a tran and roll it back.
if (test)
{
query_update = "BEGIN TRAN UIFSTest \n" + query_update;
}
// begin...
try
{
// We are going to build a querystring from all the values
// : making sure to parse values
// For each control for the form: find the value(s) for the control from the form data and check for changes
foreach (UIFS.FormDataStruct.ControlListDetail CtrlDetail in FormData.ControlList)
{
Control = FormData.Get_Control(CtrlDetail.id);
iNewCtrl = -1; iOldCtrl = -1;
//. 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 = ""; }
for (int t = 0; t < FormValues_Old.Length;t++)
{
if (FormValues_Old[t].Controlid == Control.id)
{ // Found the control data, now we can add to the query strings
iOldCtrl = t; break;
}
}
for (int t = 0; t < FormValues_New.Length;t++)
{
if (FormValues_New[t].Controlid == Control.id)
{ // Found the control data, now we can add to the query strings
iNewCtrl = t; break;
}
}
if (iNewCtrl == -1 || iOldCtrl == -1) {
// error, could not match control value arrays!
continue;
}
switch (CtrlDetail.type) {
// All of the following return a single string values
case ControlType.Textbox:
case ControlType.DateTime:
case ControlType.List:
if (FormValues_Old[iOldCtrl].value != FormValues_New[iNewCtrl].value)
{
query_values = query_values + "[" + Control.id + "]='" + SQL.ParseInput(FormValues_New[iNewCtrl].value) + "',";
Changes = Changes + Control.name + "(" + Control.id + ") was changed from: " + FormValues_Old[iOldCtrl].value + " to: " + FormValues_New[iNewCtrl].value + "\n";
}
break;
// The following return a single numeric values
case ControlType.Percentage:
case ControlType.Number:
if (FormValues_Old[iOldCtrl].value != FormValues_New[iNewCtrl].value)
{
query_values = query_values + "[" + Control.id + "]=" + FormValues_New[iNewCtrl].value + ",";
Changes = Changes + Control.name + "(" + Control.id + ") was changed from: " + FormValues_Old[iOldCtrl].value + " to: " + FormValues_New[iNewCtrl].value + "\n";
}
break;
// Checkbox controls are always true/false with an optional input field
case ControlType.Checkbox:
UIFS.FormControl.Checkbox CB = (UIFS.FormControl.Checkbox)Control;
if (CheckboxBOOL(FormValues_Old[iOldCtrl].value) != CheckboxBOOL(FormValues_New[iNewCtrl].value)) {
query_values = query_values + "[" + Control.id + ctrl_dbver + "]=" + FormValues_New[iNewCtrl].value + ",";
Changes = Changes + Control.name + "(" + Control.id + ") was changed from: " + CheckboxBOOL(FormValues_Old[iOldCtrl].value) + " to: " + CheckboxBOOL(FormValues_New[iNewCtrl].value) + "\n";
}
if (CB.hasinput)
{
if (FormValues_Old[iOldCtrl].input != FormValues_New[iNewCtrl].input)
{
query_values = query_values + "[" + Control.id + ctrl_dbver + "_text]='" + SQL.ParseInput(FormValues_New[iNewCtrl].input) + "',";
Changes = Changes + Control.name + "(" + Control.id + ")'s input was changed from: " + FormValues_Old[iOldCtrl].input + " to: " + FormValues_New[iNewCtrl].input + "\n";
}
}
break;
// Ranges have start/end values
case ControlType.Range:
if (FormValues_Old[iOldCtrl].Start != FormValues_New[iNewCtrl].Start)
{
query_values = query_values + "[" + Control.id + ctrl_dbver + "_Start]='" + SQL.ParseInput(FormValues_New[iNewCtrl].Start) + "',";
Changes = Changes + Control.name + "(" + Control.id + ")'s Start was changed from: " + FormValues_Old[iOldCtrl].Start + " to: " + FormValues_New[iNewCtrl].Start + "\n";
}
if (FormValues_Old[iOldCtrl].End != FormValues_New[iNewCtrl].End)
{
query_values = query_values + "[" + Control.id + ctrl_dbver + "_End]='" + SQL.ParseInput(FormValues_New[iNewCtrl].End) + "',";
Changes = Changes + Control.name + "(" + Control.id + ")'s End was changed from: " + FormValues_Old[iOldCtrl].Start + " to: " + FormValues_New[iNewCtrl].Start + "\n";
}
break;
}
}
// dbwrite
SQL.Query = query_update + query_values.Substring(0, query_values.Length - 1) + query_where;
// if test, rollback
if (test)
{
SQL.Query = SQL.Query + "\n ROLLBACK TRAN UIFSTest";
}
SQL.cmd = SQL.Command(SQL.Data);
SQL.cmd.ExecuteNonQuery();
return true;
}
catch (Exception ex)
{
SQL.WriteLog_Error(ex, "Failed to save form data: " + SQL.Query, "UIFS.FormInput.SaveForm");
return false;
}
}
public bool Save(UIFS.FormDataStruct FormData, InputValue[] FormValues, ref UIFS.SQL SQL, bool test, ref long newFormid)
{
UIFS.FormControl Control;
string query_insert = "INSERT INTO [UIFS.Form_" + FormData.id + "] (version,";
string query_values = "VALUES("+FormData.version+",";
// if bool test is set, we want to encap in a tran and roll it back.
if (test)
{
query_insert = "BEGIN TRAN UIFSTest \n" + query_insert;
}
// begin...
try
{
// We are going to build a querystring from all the values
// : making sure 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
foreach (UIFS.FormDataStruct.ControlListDetail CtrlDetail in FormData.ControlList)
{
Control = FormData.Get_Control(CtrlDetail.id);
foreach (InputValue IV in FormValues)
{
if (IV.Controlid == Control.id)
{ // Found the control data, now we can add to the query strings
switch (CtrlDetail.type)
{
// All of the following return a single string values
case ControlType.Textbox:
case ControlType.DateTime:
case ControlType.List:
query_insert = query_insert + "[" + Control.id + "],";
query_values = query_values + "'" + SQL.ParseInput(IV.value) + "',";
break;
// The following return a single numeric values
case ControlType.Percentage:
case ControlType.Number:
query_insert = query_insert + "[" + Control.id + "],";
query_values = query_values + "" + IV.value + ",";
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_insert = query_insert + "[" + Control.id + "], [" + Control.id + "_text],"; ;
query_values = query_values + IV.value + ",'" + SQL.ParseInput(IV.input) + "',";
}
else
{
query_insert = query_insert + "[" + Control.id + "],";
query_values = query_values + "" + IV.value + ",";
}
break;
// Ranges have start/end values
case ControlType.Range:
query_insert = query_insert + "[" + Control.id + "_Start], [" + Control.id + "_End],";
query_values = query_values + "'"+SQL.ParseInput(IV.Start)+ "','" + SQL.ParseInput(IV.End) + "',";
break;
}
break;
}
}
}
// dbwrite
// NOTE: we have to trim our trailing commas from created strings
SQL.Query = query_insert.Substring(0, query_insert.Length - 1) + ") " + query_values.Substring(0, query_values.Length - 1) + ") SELECT @@IDENTITY";
// if test, rollback
if (test) {
SQL.Query = SQL.Query + "\n ROLLBACK TRAN UIFSTest";
}
SQL.cmd = SQL.Command(SQL.Data);
newFormid = Convert.ToInt64(SQL.cmd.ExecuteScalar());
return true;
}
catch (Exception ex)
{
SQL.WriteLog_Error(ex, "Failed to save form data: "+SQL.Query, "UIFS.FormInput.SaveForm");
return false;
}
}
