/*
* A command used to update the verified field. Update should be followed by an select.
*
* Has parameters that match PrimaryKeyField and a parameter called '@verified'
*
* Example:
*
* UPDATE EDS_CatC
* SET verified = @verified
* WHERE (CatCkey = @catckey);
* SELECT *
* FROM eds_catc
* WHERE catckey = @catckey;
*
*/
protected SqlCommand GetUpdateVerifiedSqlCommand(DataEntryController dec)
{
if (UpdateVerifiedSqlCommandText != string.Empty)
{
return(new SqlCommand(UpdateVerifiedSqlCommandText, DBConnection));
}
else
{
//throw error if any required inputs are missing
if (dec.PrimaryKeyField == string.Empty || dec.VerifiedField == string.Empty ||
dec.DatabaseTable == string.Empty || dec.LookupField1 == string.Empty)
{
throw new Exception("PrimaryKeyField, VerifiedField, DatabaseTable and at least LookupField1 controller properties are required.");
}
else
{
string _sqlUpdateVerified = "UPDATE " + dec.DatabaseTable + " SET " + dec.VerifiedField + " = @" +
dec.VerifiedField + " WHERE (" + dec.PrimaryKeyField + " = @" + dec.PrimaryKeyField + "); ";
_sqlUpdateVerified += "SELECT t.*, st.studyname + ': ' + sm.studymeasname as studymeasname FROM " + dec.DatabaseTable + " t join uwautism_research_backend..tblstudymeas sm ";
_sqlUpdateVerified += " on t.studymeasid=sm.studymeasid ";
_sqlUpdateVerified += " join uwautism_research_backend..tblstudy st on st.studyid=sm.studyid ";
_sqlUpdateVerified += " WHERE " + dec.PrimaryKeyField + " = @" + dec.PrimaryKeyField;
return(new SqlCommand(_sqlUpdateVerified, DBConnection));
}
}
}
public virtual void FormatSingleEntered(DataEntryController dec)
{
/*
* Show Insert, Entry, and Read-only fields. Only Entry fields should
* be enabled.
*/
foreach (DataFieldControl c in dec.AllDataFields.Values)
{
c.Visible = false;
c.Enabled = false;
if (c.IsReadOnly == true || c.IsInsertField == true || c.IsEntryField == true)
{
c.Visible = true;
}
if (c.IsEntryField == true)
{
c.Enabled = true;
}
}
// show mainPanel, if it exists
System.Web.UI.WebControls.Panel mainPanel = dec.Parent.FindControl("mainPanel") as System.Web.UI.WebControls.Panel;
if (mainPanel != null)
{
mainPanel.Visible = true;
}
}
public virtual void FormatCorrectingDoubleEntering(DataEntryController dec)
{
/*
* Show Insert, Entry, and Read-only fields. Only Double-Entry fields with
* verification errors are enabled.
* If there were validation errors (FormError == true) then don't show
* override check boxes.
*/
foreach (DataFieldControl c in dec.AllDataFields.Values)
{
c.Visible = false;
c.Enabled = false;
c.OverrideCheckBoxVisible = false;
if (c.IsReadOnly == true || c.IsInsertField == true || c.IsEntryField == true)
{
c.Visible = true;
}
if (c.IsDoubleEntryField == true && c.VerifyError == true)
{
c.Enabled = true;
}
if (c.IsDoubleEntryField == true && c.VerifyError == true && dec.FormError == false)
{
c.OverrideCheckBoxVisible = true;
}
}
// show mainPanel, if it exists
System.Web.UI.WebControls.Panel mainPanel = dec.Parent.FindControl("mainPanel") as System.Web.UI.WebControls.Panel;
if (mainPanel != null)
{
mainPanel.Visible = true;
}
}
/*
* A select statement using PrimaryKeyField:
*
* SELECT EDS_CatC.*
* FROM EDS_CatC
* WHERE (CatCkey = @catckey)
*
*/
protected SqlCommand GetSelectSqlCommand(DataEntryController dec)
{
if (SelectSqlCommandText != string.Empty)
{
return(new SqlCommand(SelectSqlCommandText, DBConnection));
}
else
{
//throw error if any required inputs are missing
if (dec.PrimaryKeyField == string.Empty || dec.VerifiedField == string.Empty ||
dec.DatabaseTable == string.Empty || dec.LookupField1 == string.Empty)
{
throw new Exception("PrimaryKeyField, VerifiedField, DatabaseTable and at least LookupField1 controller properties are required.");
}
else
{
string _sqlSelect = "SELECT t.*, st.studyname + ': ' + sm.studymeasname as studymeasname ";
_sqlSelect += " , uwautism_research_backend.dbo.fnGetSubjIDFromIDAndStudymeasID(ID, sm.studymeasID) as subjID ";
_sqlSelect += " FROM " + dec.DatabaseTable + " t join uwautism_research_backend..tblstudymeas sm ";
_sqlSelect += " on t.studymeasid=sm.studymeasid ";
_sqlSelect += " join uwautism_research_backend..tblstudy st on st.studyid=sm.studyid ";
_sqlSelect += " WHERE ID in (select ID from uwautism_research_backend.dbo.fnSEC_Allowed_TRACKING_IDs_by_User()) AND " + dec.PrimaryKeyField + " = @" + dec.PrimaryKeyField;
return(new SqlCommand(_sqlSelect, DBConnection));
}
}
}
/*
* Execute sqlCommand.
*
* Read values returned by command back into fields.
*
*/
protected void DoCommand(DataEntryController dec,
SqlCommand sqlCommand)
{
// execute the query
if (DBConnection.State == ConnectionState.Closed)
{
DBConnection.Open();
}
SqlDataReader r = sqlCommand.ExecuteReader();
r.Read();
//loop over fields and fetch values from columns of resultset
foreach (DataFieldControl dfc in dec.AllDataFields.Values)
{
dfc.FieldTextBoxText = r[dfc.DatabaseField].ToString();
}
// save value of primary key
dec.PrimaryKeyVal = (int)r[dec.PrimaryKeyField];
// save studymeasname
dec.StudyMeasName = r["studymeasname"].ToString();
// save studymeasid
dec.StudyMeasID = (int)r["studymeasid"];
//JM
dec.SubjID = (int)r["subjID"];
// save value of study subject ID string. It's the value
// in the field whose name is stored in the LookupField1 controller property.
dec.StudySubjectIDString = r[dec.LookupField1].ToString();
// save the value of the indexnum. If used indexnum is the
// value in the field whose name is stored in LookupField2
if (dec.LookupTextBox2Visible)
{
dec.StudyMeasureIndexnum = (int)r[dec.LookupField2];
}
//if last updated and last scored fields are specified get their values.
//If the value for either in the table is NULL set to DateTime.MinValue
//if (dec.LastScoredDateField != string.Empty && dec.LastUpdatedDateField != string.Empty)
//{
// if (r[dec.LastUpdatedDateField] != DBNull.Value)
// dec.LastUpdatedDate = (DateTime)r[dec.LastUpdatedDateField];
// else
// dec.LastUpdatedDate = DateTime.MinValue;
// if (r[dec.LastScoredDateField] != DBNull.Value)
// dec.LastScoredDate = (DateTime)r[dec.LastScoredDateField];
// else
// dec.LastScoredDate = DateTime.MinValue;
//}
r.Close();
DBConnection.Close();
}
protected void LoadLayout(string measID)
{
//GridSections1.Visible = true;
//LoadSections(measID);
panelDEMainBody.Visible = true;
LoadFldtype_legend();
AutoLayout.LoadFields_for_layout(measID, panelDEMainBody, "Layout", panelDEInsert); //, popupPanel);
DataEntryController dec = (DataEntryController)this.FindControlRecursive("DataEntryController1");
if (dec != null)
{
dec.FormState = FormStates.EDITING_LAYOUT;
dec.StudyMeasName = ddlMeas.SelectedItem.Text;
foreach (Control ctl in dec.Controls)
{
Debug.Write(ctl.ID + " " + ctl.GetType().ToString());
}
}
//Grid1.Visible = false;
}
private SqlConnection GetSqlConnection(DataEntryController dec, string componentName)
{
IDesignerHost idh = null;
idh = (IDesignerHost)dec.Page.Site.GetService(typeof(IDesignerHost));
SqlConnection sc = null;
if (idh != null)
{
foreach (Object c in idh.Container.Components)
{
sc = c as SqlConnection;
if (sc != null)
{
if (sc.Site.Name == componentName)
{
break;
}
else
{
sc = null;
}
}
}
}
return(sc);
}
public virtual void FormatDoubleEntered(DataEntryController dec)
{
/*
* Show Insert, Entry, and Read-only fields. Only Entry fields should
* be enabled.
*/
foreach (DataFieldControl c in dec.AllDataFields)
{
// clear any verification errors left
// over from verification state
c.VerifyError = false;
c.Visible = false;
c.Enabled = false;
if (c.IsReadOnly == true || c.IsInsertField == true || c.IsEntryField == true)
{
c.Visible = true;
}
if (c.IsEntryField == true)
{
c.Enabled = true;
}
}
// show mainPanel, if it exists
System.Web.UI.WebControls.Panel mainPanel = dec.Parent.FindControl("mainPanel") as System.Web.UI.WebControls.Panel;
if (mainPanel != null)
{
mainPanel.Visible = true;
}
}
public virtual void DoInsert(DataEntryController dec)
{
//trap any exceptions
try
{
//user must select a studymeasure
if (dec.SelectedNewStudyMeasID == -1)
{
dec.Notifications.Add("Select a study measure.");
dec.FormError = true;
}
// do basic field validation first
else if (dec.Validator.ValidateForInsert(dec.InsertDataFields, dec.Notifications) == false)
{
dec.FormError = true;
}
else
{
dec.FormError = false;
// do insert
SqlCommand insertSqlCommand = GetInsertSqlCommand(dec);
DoCommand(dec, insertSqlCommand, dec.InsertDataFields, dec.SelectedNewStudyMeasID, Verified.CREATED);
}
}
catch (SqlException sqlEx)
{
string sErrMsg = string.Empty;
if (sqlEx.Number == 2601)
{
// Duplicate key violation
sErrMsg = "A measure with the same ID already exists.";
}
else if (sqlEx.Number == 2627)
{
// violation of unique index
sErrMsg = "A measure with the same ID and indexnum already exists.";
}
else
{
sErrMsg = sqlEx.Message;
}
//add error to notifications and let controller know
//an error occured
dec.Notifications.Add(string.Format("An error occurred: {0}", sErrMsg));
dec.FormError = true;
}
catch (Exception ex)
{
//add error to notifications and let controller know
//an error occured
dec.Notifications.Add(string.Format("An error occurred: {0}", ex.Message));
dec.FormError = true;
}
}
/*
* Execute sqlCommand after setting primary key as parameter.
*
* Read values returned by command back into form fields.
*
*/
protected void DoCommand(DataEntryController dec,
SqlCommand sqlCommand,
int primaryKeyVal)
{
// set primary key parameter
sqlCommand.Parameters.AddWithValue("@" + dec.PrimaryKeyField, primaryKeyVal);
// execute
DoCommand(dec, sqlCommand);
}
/*
* The InsertSqlCommand must contain one parameter for each data entry control
* with IsInsertField == true. It also should have a parameter for @verified.
*
* It must also follow the insert command with a select command using
* @@IDENTITY to return the newly inserted row.
*
* For Example:
* INSERT INTO EDS_CatC
* (edsid, indexnum, catcdate, catcclin, timepoint, verified)
* VALUES (@edsid, @indexnum, @catcdate, @catcclin, @timepoint, @verified);
* SELECT *
* FROM eds_catc
* WHERE catckey = @@IDENTITY;
*
*/
protected SqlCommand GetInsertSqlCommand(DataEntryController dec)
{
if (InsertSqlCommandText != string.Empty)
{
return(new SqlCommand(InsertSqlCommandText, DBConnection));
}
else
{
//throw error if any required inputs are missing
if (dec.PrimaryKeyField == string.Empty || dec.VerifiedField == string.Empty ||
dec.DatabaseTable == string.Empty || dec.LookupField1 == string.Empty ||
dec.SelectedNewStudyMeasID == -1)
{
throw new Exception("PrimaryKeyField, VerifiedField, DatabaseTable, Study Measure and at least LookupField1 controller properties are required.");
}
else
{
// build and return sqlInsert SqlCommand object
string _sqlInsert = "INSERT INTO " + dec.DatabaseTable + " (";
bool first = true;
foreach (DataFieldControl d in dec.InsertDataFields.Values)
{
_sqlInsert += ((!first)?",":"") + d.DatabaseField;
first = false;
}
_sqlInsert += "," + dec.VerifiedField + ", studymeasid";
_sqlInsert += ") VALUES (";
first = true;
foreach (DataFieldControl d in dec.InsertDataFields.Values)
{
_sqlInsert += ((!first)?",":"") + "@" + d.DatabaseField;
first = false;
}
_sqlInsert += ", @" + dec.VerifiedField + ",@studymeasid";
_sqlInsert += ");";
_sqlInsert += " SELECT t.*, st.studyname + ': ' + sm.studymeasname as studymeasname , uwautism_research_backend.dbo.fnGetSubjIDFromIDAndStudymeasID(ID, sm.studymeasID) as subjID ";
_sqlInsert += " FROM " + dec.DatabaseTable + " t join uwautism_research_backend..tblstudymeas sm ";
_sqlInsert += " on t.studymeasid=sm.studymeasid ";
_sqlInsert += " join uwautism_research_backend..tblstudy st on st.studyid=sm.studyid ";
//_sqlInsert += " WHERE " + dec.PrimaryKeyField + " = @@IDENTITY;";
_sqlInsert += " WHERE ID in (select ID from uwautism_research_backend.dbo.fnSEC_Allowed_TRACKING_IDs_by_User()) AND " + dec.PrimaryKeyField + " = scope_identity();";
return(new SqlCommand(_sqlInsert, DBConnection));
}
}
}
/*
* Execute sqlCommand setting values passed in ArrayList and
* verified as parameters.
*
* Read values returned by command back into fields.
*
*/
protected void DoCommand(DataEntryController dec,
SqlCommand sqlCommand,
ArrayList paramFields,
Verified verified)
{
// add field parameters
AddParameters(sqlCommand.Parameters, paramFields);
// set verified parameter
sqlCommand.Parameters.AddWithValue("@verified", verified);
// execute
DoCommand(dec, sqlCommand);
}
/*
* Execute sqlCommand setting values passed in ArrayList and
* verified as parameters.
*
* Read values returned by command back into fields.
*
*/
protected void DoCommand(DataEntryController dec,
SqlCommand sqlCommand,
Dictionary <string, DataFieldControl> paramFields,
Verified verified)
{
// add field parameters
AddParameters(sqlCommand.Parameters, paramFields);
// set verified parameter
sqlCommand.Parameters.AddWithValue("@verified", verified);
// execute
DoCommand(dec, sqlCommand);
}
/*
* The UpdateSqlCommand must contain one parameter for every data entry field
* with IsEntryField == true. It also should have a parameter for @verified.
*
* AND must have an additional parameter that matches
* the user supplied PrimaryKeyField controller setting.
*
* The update SQL must be followed by a select statement that returns the contents of the updated row.
*
* In the following example PrimaryKeyField = catckey:
*
* UPDATE EDS_CatC
* SET timepoint = @timepoint, catcdate = @catcdate, catcclin = @catcclin, catc01cu = @catc01cu, catc01ev = @catc01ev, catc02cu = @catc02cu,
* catc02ev = @catc02ev, catc03cu = @catc03cu, catc03ev = @catc03ev, catc04cu = @catc04cu, catc04ev = @catc04ev, catc05cu = @catc05cu,
* catc05ev = @catc05ev, catc06cu = @catc06cu, catc06ev = @catc06ev, catc07cu = @catc07cu, catc07ev = @catc07ev, catc08cu = @catc08cu,
* catc08ev = @catc08ev, catc10 = @catc10, catc11 = @catc11, catc12 = @catc12, catc13 = @catc13, catccmt = @catccmt, verified = @verified
* WHERE (CatCkey = @CatCkey);
* SELECT *
* FROM eds_catc
* WHERE (CatCkey = @CatCkey);
*
*
*/
protected SqlCommand GetUpdateSqlCommand(DataEntryController dec)
{
if (UpdateSqlCommandText != string.Empty)
{
return(new SqlCommand(UpdateSqlCommandText, DBConnection));
}
else
{
//throw error if any required inputs are missing
if (dec.PrimaryKeyField == string.Empty || dec.VerifiedField == string.Empty ||
dec.DatabaseTable == string.Empty || dec.LookupField1 == string.Empty)
{
throw new Exception("PrimaryKeyField, VerifiedField, DatabaseTable and at least LookupField1 controller properties are required.");
}
else
{
// make sqlUpdate SqlCommand
string _sqlUpdate = "UPDATE " + dec.DatabaseTable + " SET ";
bool first = true;
foreach (DataFieldControl d in dec.EntryDataFields.Values)
{
_sqlUpdate += ((!first)?",":"") + d.DatabaseField + "=@" + d.DatabaseField;
first = false;
}
_sqlUpdate += "," + dec.VerifiedField + "=@" + dec.VerifiedField + " ";
_sqlUpdate += " WHERE " + dec.PrimaryKeyField + "=@" + dec.PrimaryKeyField + "; ";
//Add sql to call scoring sp after saving, if requested
if (dec.ScoreAfterSave)
{
_sqlUpdate += " exec spDEF_ScoreAfterSave '" + dec.DatabaseTable + "', @" + dec.PrimaryKeyField + "; ";
}
//Update the reliability data if studymeasID is a reliability measure
_sqlUpdate += " exec spSummarizeReliability_if_Rel_studymeas " + dec.StudyMeasID;
_sqlUpdate += " SELECT t.*, st.studyname + ': ' + sm.studymeasname as studymeasname , uwautism_research_backend.dbo.fnGetSubjIDFromIDAndStudymeasID(ID, sm.studymeasID) as subjID FROM " + dec.DatabaseTable + " t join uwautism_research_backend..tblstudymeas sm ";
_sqlUpdate += " on t.studymeasid=sm.studymeasid ";
_sqlUpdate += " join uwautism_research_backend..tblstudy st on st.studyid=sm.studyid ";
_sqlUpdate += " WHERE ID in (select ID from uwautism_research_backend.dbo.fnSEC_Allowed_TRACKING_IDs_by_User()) AND " + dec.PrimaryKeyField + " = @" + dec.PrimaryKeyField;
return(new SqlCommand(_sqlUpdate, DBConnection));
}
}
}
public virtual void FormatInserting(DataEntryController dec)
{
/*
* Show only controls that are insert fields or are read-only. Insert fields
* should be enabled. Read-only fields should be disabled. Clear all fields.
* Set all fields to default value, if any.
*/
foreach (DataFieldControl c in dec.AllDataFields.Values)
{
/*
* clear field controls if not re-entering
* state because of an error. This is needed
* to reset all form fields when arriving in the INSERTING
* state after a lookup or create new.
*/
if (dec.FormError == false)
{
c.OverrideCheckBoxChecked = false;
c.OverrideCheckBoxVisible = false;
c.VerifyError = false;
// initialize insert/read-only fields
if (c.IsInsertField == true || c.IsReadOnly == true)
{
c.FieldTextBoxText = c.FieldTextBoxTextDefault;
}
}
c.Visible = false;
c.Enabled = false;
if (c.IsReadOnly == true || c.IsInsertField == true)
{
c.Visible = true;
}
if (c.IsInsertField == true)
{
c.Enabled = true;
}
}
// hide mainPanel, if it exists
System.Web.UI.WebControls.Panel mainPanel = dec.Parent.FindControl("mainPanel") as System.Web.UI.WebControls.Panel;
if (mainPanel != null)
{
mainPanel.Visible = false;
}
}
/*
* A select command with parameters corresponding to
* LookupField1 and optionally LookupField2
*
* In this Example LookupField1 = edsid and LookupField2 = indexnum (indexnum is optional):
*
* SELECT EDS_CatC.*
* FROM EDS_CatC
* WHERE (edsid = @edsid) AND (indexnum = @indexnum OR
* @indexnum IS NULL)
*
*
*/
protected SqlCommand GetLookupSqlCommand(DataEntryController dec)
{
if (LookupSqlCommandText != string.Empty)
{
return(new SqlCommand(LookupSqlCommandText, DBConnection));
}
else
{
//throw error if any required inputs are missing
if (dec.PrimaryKeyField == string.Empty || dec.VerifiedField == string.Empty ||
dec.DatabaseTable == string.Empty || dec.LookupField1 == string.Empty)
{
throw new Exception("PrimaryKeyField, VerifiedField, DatabaseTable and at least LookupField1 controller properties are required.");
}
else
{
/*
* string _sqlLookup = "SELECT t.*, st.studyname + ': ' + sm.studymeasname as studymeasname FROM " + dec.DatabaseTable + " t join uwautism_research_backend..tblstudymeas sm ";
* _sqlLookup += " on t.studymeasid=sm.studymeasid ";
* _sqlLookup += " join uwautism_research_backend..tblstudy st on st.studyid=sm.studyid ";
* _sqlLookup += " WHERE (" + dec.LookupField1 + " = @" + dec.LookupField1 + ") " +
* ((dec.LookupField2 != string.Empty)? " AND (" + dec.LookupField2 + "= @" + dec.LookupField2 +
* " OR @" + dec.LookupField2 + " IS NULL) ":" ");
* _sqlLookup += " AND t.studymeasid=@studymeasid ";
* _sqlLookup += " AND (" + dec.PrimaryKeyField + " = @pk OR @pk IS NULL) ";
*/
string _sqlLookup = "SELECT t.*, st.studyname + ': ' + sm.studymeasname as studymeasname ";
_sqlLookup += " , uwautism_research_backend.dbo.fnGetSubjIDFromIDAndStudymeasID(ID, sm.studymeasID) as subjID ";
_sqlLookup += " FROM " + dec.DatabaseTable + " t join uwautism_research_backend..tblstudymeas sm ";
_sqlLookup += " on t.studymeasid=sm.studymeasid ";
_sqlLookup += " join uwautism_research_backend..tblstudy st on st.studyid=sm.studyid ";
_sqlLookup += " WHERE ID in (select ID from uwautism_research_backend.dbo.fnSEC_Allowed_TRACKING_IDs_by_User()) AND (" + dec.LookupField1 + " = @" + dec.LookupField1 + " OR @" + dec.LookupField1 + " IS NULL) ";
_sqlLookup += ((dec.LookupField2 != string.Empty)? " AND (" + dec.LookupField2 + "= @" + dec.LookupField2 +
" OR @" + dec.LookupField2 + " IS NULL) ":" ");
_sqlLookup += " AND (t.studymeasid=@studymeasid OR @studymeasid IS NULL) ";
_sqlLookup += " AND (" + dec.PrimaryKeyField + " = @pk OR @pk IS NULL) ";
//JM to see the SQL string: throw new Exception(_sqlLookup);
return(new SqlCommand(_sqlLookup, DBConnection));
}
}
}
protected void btnDuplicate_click(Object sender, EventArgs e)
{
SQL_utils sql = new SQL_utils("data");
DataEntryController dec = (DataEntryController)this.DataEntryController1;
string tblname = dec.DatabaseTable;
var smID = dec.StudyMeasID;
var pkfld = dec.PrimaryKeyField;
int pkval = dec.PrimaryKeyVal;
string id = dec.StudySubjectIDString;
int indexnum = dec.StudyMeasureIndexnum;
DataTable dtflds = sql.DataTable_from_SQLstring("select fldname from def.fld where tblpk = " + Environment.NewLine +
" (select tblpk from def.tbl where tblname='" + tblname + "') and fldname not in ('" + pkfld + "')");
int maxindexnum = sql.IntScalar_from_SQLstring("select max(indexnum) from " + tblname + Environment.NewLine +
" where id = '" + id + "' and studymeasID=" + smID.ToString());
int nextindexnum = maxindexnum + 1;
List <string> flds = dtflds.AsEnumerable().Select(f => f.Field <string>("fldname")).ToList();
string flds_csv = String.Join(",", flds).ToLower();
string newflds_csv = flds_csv.Replace(",studymeasid,", ",4984 as studymeasid,");
newflds_csv = newflds_csv.Replace(",indexnum,", "," + nextindexnum.ToString() + " as indexnum,");
string sqlcode = String.Format("insert into all_currchecklist({0}) " + Environment.NewLine +
" select {1} " + Environment.NewLine +
" from all_currchecklist where ccl_pk={2}"
, flds_csv, newflds_csv, pkval);
sql.NonQuery_from_SQLstring(sqlcode);
int maxindexnum2 = sql.IntScalar_from_SQLstring("select max(indexnum) from " + tblname + Environment.NewLine +
" where id = '" + id + "' and studymeasID=" + smID.ToString());
if (maxindexnum2 > maxindexnum)
{
lblNewrec.Text = String.Format("indexnum={0} inserted", maxindexnum2);
}
sql.Close();
}
/*
* A select command with parameters corresponding to
* LookupField1 and optionally LookupField2
*
* In this Example LookupField1 = edsid and LookupField2 = indexnum (indexnum is optional):
*
* SELECT EDS_CatC.*
* FROM EDS_CatC
* WHERE (edsid = @edsid) AND (indexnum = @indexnum OR
* @indexnum IS NULL)
*
*
*/
protected SqlCommand GetLookupSqlCommand(DataEntryController dec)
{
if (LookupSqlCommandText != string.Empty)
{
return(new SqlCommand(LookupSqlCommandText, DBConnection));
}
else
{
//throw error if any required inputs are missing
if (dec.PrimaryKeyField == string.Empty || dec.VerifiedField == string.Empty ||
dec.DatabaseTable == string.Empty || dec.LookupField1 == string.Empty)
{
throw new Exception("PrimaryKeyField, VerifiedField, DatabaseTable and at least LookupField1 controller properties are required.");
}
else
{
/*
* string _sqlLookup = "SELECT t.*, st.studyname + ': ' + sm.studymeasname as studymeasname FROM " + dec.DatabaseTable + " t join uwautism_research_backend..tblstudymeas sm ";
* _sqlLookup += " on t.studymeasid=sm.studymeasid ";
* _sqlLookup += " join uwautism_research_backend..tblstudy st on st.studyid=sm.studyid ";
* _sqlLookup += " WHERE (" + dec.LookupField1 + " = @" + dec.LookupField1 + ") " +
* ((dec.LookupField2 != string.Empty)? " AND (" + dec.LookupField2 + "= @" + dec.LookupField2 +
* " OR @" + dec.LookupField2 + " IS NULL) ":" ");
* _sqlLookup += " AND t.studymeasid=@studymeasid ";
* _sqlLookup += " AND (" + dec.PrimaryKeyField + " = @pk OR @pk IS NULL) ";
*/
string _sqlLookup = "SELECT t.*, st.studyname + ': ' + sm.studymeasname as studymeasname FROM " + dec.DatabaseTable + " t join uwautism_research_backend..tblstudymeas sm ";
_sqlLookup += " on t.studymeasid=sm.studymeasid ";
_sqlLookup += " join uwautism_research_backend..tblstudy st on st.studyid=sm.studyid ";
_sqlLookup += " WHERE (" + dec.LookupField1 + " = @" + dec.LookupField1 + " OR @" + dec.LookupField1 + " IS NULL) ";
_sqlLookup += ((dec.LookupField2 != string.Empty)? " AND (" + dec.LookupField2 + "= @" + dec.LookupField2 +
" OR @" + dec.LookupField2 + " IS NULL) ":" ");
_sqlLookup += " AND (t.studymeasid=@studymeasid OR @studymeasid IS NULL) ";
_sqlLookup += " AND (" + dec.PrimaryKeyField + " = @pk OR @pk IS NULL) ";
return(new SqlCommand(_sqlLookup, DBConnection));
}
}
}
protected void Page_PreRender(object sender, EventArgs e)
{
DataEntryController dec = (DataEntryController)this.DataEntryController1;
int smID = dec.StudyMeasID;
btnDuplicate.Visible = (smID == 4984) ? true : false;
if (Request.QueryString["pk"] != null)
{
SQL_utils sql = new SQL_utils("data");
int db_smID = sql.IntScalar_from_SQLstring("select studymeasID from all_currchecklist where ccl_pk = " + Request.QueryString["pk"]);
if (db_smID == 4984)
{
btnDuplicate.Visible = true;
}
sql.Close();
}
}
private void GetFields(DataEntryController dec)
{
IDesignerHost idh = null;
idh = (IDesignerHost)dec.Page.Site.GetService(typeof(IDesignerHost));
if (idh != null)
{
DataFieldControl dfc = null;
foreach (Object c in idh.Container.Components)
{
dfc = c as DataFieldControl;
if (dfc != null)
{
_fields.Add(new DataFieldColumn(dfc));
}
}
}
}
public override bool EditComponent(ITypeDescriptorContext context,
object component,
IWin32Window owner)
{
DataEntryController dec = component as DataEntryController;
if (dec == null)
{
throw new ArgumentException("Component must be a DataEntryController object", "component");
}
bool changed = false;
//throw an exception if all required designer properties aren't set before showing designer
// HACK: really should have a way to set these in the designer, but I'll do that later
//throw an error if any required controller property ismissing
if (dec.PrimaryKeyField == string.Empty || dec.VerifiedField == string.Empty ||
dec.DatabaseTable == string.Empty || dec.LookupField1 == string.Empty)
{
throw new Exception("The controller is missing some required properties: PrimaryKeyField, VerifiedField, DatabaseTable, and LookupField1 are required.");
}
SqlConnection conn = GetSqlConnection(dec, Settings.SQL_CONNECTION_COMPONENTNAME);
if (conn == null)
{
throw new Exception(string.Format("Add a SqlConnection component to the form called {0}.", Settings.SQL_CONNECTION_COMPONENTNAME));
}
//DataEntryControllerComponentEditorForm form = new DataEntryControllerComponentEditorForm(dec, _cd);
DataEntryControllerComponentEditorForm form = new DataEntryControllerComponentEditorForm(dec);
if (form.ShowDialog(owner) == DialogResult.OK)
{
changed = true;
}
return(changed);
}
public DataEntryControllerFieldEditorForm(DataEntryController dec)
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
// wire up tab index reorder button handlers
this.btnFirst.Click += new System.EventHandler(this.btnReorder_Click);
this.btnUp.Click += new System.EventHandler(this.btnReorder_Click);
this.btnDown.Click += new System.EventHandler(this.btnReorder_Click);
this.btnLast.Click += new System.EventHandler(this.btnReorder_Click);
_dec = dec;
GetFields(dec);
FillListView();
}
public virtual void FormatEntering(DataEntryController dec)
{
/*
* Show Insert, Entry, and Read-only fields. Only Entry fields should
* be enabled.
*
* Initialize fields with default values if not entering as result of error.
* Only initialize fields that are entry fields.
*/
foreach (DataFieldControl c in dec.AllDataFields.Values)
{
c.Visible = false;
c.Enabled = false;
if (c.IsReadOnly == true || c.IsInsertField == true || c.IsEntryField == true)
{
c.Visible = true;
}
if (c.IsEntryField == true)
{
c.Enabled = true;
}
if (dec.FormError == false)
{
// initialize entry fields, leave fields that are also insert fields alone
if (c.IsEntryField == true && c.IsInsertField == false)
{
c.FieldTextBoxText = c.FieldTextBoxTextDefault;
}
}
}
// show mainPanel, if it exists
System.Web.UI.WebControls.Panel mainPanel = dec.Parent.FindControl("mainPanel") as System.Web.UI.WebControls.Panel;
if (mainPanel != null)
{
mainPanel.Visible = true;
}
}
public virtual void DoNoData(DataEntryController dec)
{
try
{
if (dec.Validator.ValidateForNoData(dec.AllDataFields, dec.Notifications) == false)
{
dec.FormError = true;
}
else
{
dec.FormError = false;
SqlCommand updateVerifiedSqlCommand = GetUpdateVerifiedSqlCommand(dec);
DoCommand(dec, updateVerifiedSqlCommand, Verified.NO_DATA, dec.PrimaryKeyVal);
}
}
catch (Exception ex)
{
dec.Notifications.Add(ex.Message);
dec.FormError = true;
}
}
public virtual void FormatNoData(DataEntryController dec)
{
/*
* Show only controls that are insert fields or are read-only. All fields
* are disabled.
*/
foreach (DataFieldControl c in dec.AllDataFields.Values)
{
c.Visible = false;
c.Enabled = false;
if (c.IsReadOnly == true || c.IsInsertField == true)
{
c.Visible = true;
}
}
// show mainPanel, if it exists
System.Web.UI.WebControls.Panel mainPanel = dec.Parent.FindControl("mainPanel") as System.Web.UI.WebControls.Panel;
if (mainPanel != null)
{
mainPanel.Visible = true;
}
}
/*
* The UpdateSqlCommand must contain one parameter for every data entry field
* with IsEntryField == true. It also should have a parameter for @verified.
*
* AND must have an additional parameter that matches
* the user supplied PrimaryKeyField controller setting.
*
* The update SQL must be followed by a select statement that returns the contents of the updated row.
*
* In the following example PrimaryKeyField = catckey:
*
* UPDATE EDS_CatC
* SET timepoint = @timepoint, catcdate = @catcdate, catcclin = @catcclin, catc01cu = @catc01cu, catc01ev = @catc01ev, catc02cu = @catc02cu,
* catc02ev = @catc02ev, catc03cu = @catc03cu, catc03ev = @catc03ev, catc04cu = @catc04cu, catc04ev = @catc04ev, catc05cu = @catc05cu,
* catc05ev = @catc05ev, catc06cu = @catc06cu, catc06ev = @catc06ev, catc07cu = @catc07cu, catc07ev = @catc07ev, catc08cu = @catc08cu,
* catc08ev = @catc08ev, catc10 = @catc10, catc11 = @catc11, catc12 = @catc12, catc13 = @catc13, catccmt = @catccmt, verified = @verified
* WHERE (CatCkey = @CatCkey);
* SELECT *
* FROM eds_catc
* WHERE (CatCkey = @CatCkey);
*
*
*/
protected SqlCommand GetUpdateSqlCommand(DataEntryController dec)
{
if (UpdateSqlCommandText != string.Empty)
{
return(new SqlCommand(UpdateSqlCommandText, DBConnection));
}
else
{
//throw error if any required inputs are missing
if (dec.PrimaryKeyField == string.Empty || dec.VerifiedField == string.Empty ||
dec.DatabaseTable == string.Empty || dec.LookupField1 == string.Empty)
{
throw new Exception("PrimaryKeyField, VerifiedField, DatabaseTable and at least LookupField1 controller properties are required.");
}
else
{
// make sqlUpdate SqlCommand
string _sqlUpdate = "UPDATE " + dec.DatabaseTable + " SET ";
bool first = true;
foreach (DataFieldControl d in dec.EntryDataFields)
{
_sqlUpdate += ((!first)?",":"") + d.DatabaseField + "=@" + d.DatabaseField;
first = false;
}
_sqlUpdate += "," + dec.VerifiedField + "=@" + dec.VerifiedField + " ";
_sqlUpdate += " WHERE " + dec.PrimaryKeyField + "=@" + dec.PrimaryKeyField + "; ";
_sqlUpdate += " SELECT t.*, st.studyname + ': ' + sm.studymeasname as studymeasname FROM " + dec.DatabaseTable + " t join uwautism_research_backend..tblstudymeas sm ";
_sqlUpdate += " on t.studymeasid=sm.studymeasid ";
_sqlUpdate += " join uwautism_research_backend..tblstudy st on st.studyid=sm.studyid ";
_sqlUpdate += " WHERE " + dec.PrimaryKeyField + " = @" + dec.PrimaryKeyField;
return(new SqlCommand(_sqlUpdate, DBConnection));
}
}
}
public virtual void DoVerify(DataEntryController dec)
{
try
{
if (dec.Validator.ValidateForVerify(dec.DoubleEntryDataFields, dec.Notifications) == false)
{
dec.FormError = true;
}
else
{
dec.FormError = false;
// setup sqlcommand for call to Validator.Verify
SqlCommand selectSqlCommand = GetSelectSqlCommand(dec);
selectSqlCommand.Parameters.AddWithValue("@" + dec.PrimaryKeyField, dec.PrimaryKeyVal);
if (dec.Validator.Verify(selectSqlCommand, dec.DoubleEntryDataFields, dec.Notifications) == false)
{ // There were verification errors
dec.VerifyError = true;
}
else
{
// No verification errors
dec.VerifyError = false;
SqlCommand updateVerifiedSqlCommand = GetUpdateVerifiedSqlCommand(dec);
DoCommand(dec, updateVerifiedSqlCommand, Verified.DOUBLE_ENTERED, dec.PrimaryKeyVal);
}
}
}
catch (Exception ex)
{
dec.FormError = true;
dec.Notifications.Add(string.Format("Unexpected error in DoVerify() event handler: {0}", ex.Message));
}
}
public virtual void DoSaveDoubleEntered(DataEntryController dec)
{
// trap any exceptions
try
{
if (dec.Validator.ValidateForSaveDoubleEntered(dec.EntryDataFields, dec.Notifications) == false)
{
dec.FormError = true;
}
else
{
dec.FormError = false;
// do update
SqlCommand updateSqlCommand = GetUpdateSqlCommand(dec);
DoCommand(dec, updateSqlCommand, dec.EntryDataFields, Verified.DOUBLE_ENTERED, dec.PrimaryKeyVal);
}
}
catch (Exception ex)
{
dec.Notifications.Add(string.Format("Unexpected error in DoSaveDoubleEntered() event handler: {0}", ex.Message));
dec.FormError = true;
}
}
public DataEntryControllerComponentEditorFormX(DataEntryController component, System.Web.UI.Design.ControlDesigner designer)
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
// _dec = component;
// _designer = designer;
//
// label1.Text = "Editing: " + _dec.ID.ToString();
// textBox1.Text = _dec.PrimaryKeyField;
txtConnectString.Text = "packet size=4096;integrated security=SSPI;data source=\"autism-SQL\";persist security info=False;initial catalog=uwautism_research_data";
txtSqlTable.Text = "edst3_ppvt";
//wireup check box event handler
chkIsInsertField.CheckedChanged += new EventHandler(this.FieldTypeCheckedHandler);
chkIsInsertValueRequired.CheckedChanged += new EventHandler(this.FieldTypeCheckedHandler);
chkIsEntryField.CheckedChanged += new EventHandler(this.FieldTypeCheckedHandler);
chkIsEntryValueRequired.CheckedChanged += new EventHandler(this.FieldTypeCheckedHandler);
chkIsReadOnly.CheckedChanged += new EventHandler(this.FieldTypeCheckedHandler);
chkIsDoubleEntryField.CheckedChanged += new EventHandler(this.FieldTypeCheckedHandler);
}
public virtual void FormatDoubleEntering(DataEntryController dec)
{
/*
* Show Insert, Entry, and Read-only fields. Only Double-Entry fields should
* be enabled.
*
* Double-Entry fields must be a subset of entry fields. Clear any
* existing contents of double entry fields (or set them to default if any)
* unless re-entering state because of an error.
*/
foreach (DataFieldControl c in dec.AllDataFields.Values)
{
c.Visible = false;
c.Enabled = false;
if (c.IsReadOnly == true || c.IsInsertField == true || c.IsEntryField == true)
{
c.Visible = true;
}
if (c.IsDoubleEntryField == true)
{
c.Enabled = true;
if (dec.FormError == false)
{
c.FieldTextBoxText = string.Empty;
c.FieldTextBoxText = c.FieldTextBoxTextDefault;
}
}
}
// show mainPanel, if it exists
System.Web.UI.WebControls.Panel mainPanel = dec.Parent.FindControl("mainPanel") as System.Web.UI.WebControls.Panel;
if (mainPanel != null)
{
mainPanel.Visible = true;
}
}
