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;
}
}
// ---------------------------------------------------------------------------------------------------------------
/* -- 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;
}
}