protected void btnAdd_Click(object sender, EventArgs e)
{
bool success = true;
string sql = "insert into \"UserRoles_ExternalProviders\" (\"ExternalSubjectId\",\"RoleId\",\"Idp\",\"Id\") values (@userid,@roleid, @idp,@id) ";
var paramList = new List <KeyValuePair <string, string> >()
{
new KeyValuePair <string, string>("userid", txtExternalUserId.Text),
new KeyValuePair <string, string>("roleid", Session["SM_roleid"].ToString()),
new KeyValuePair <string, string>("idp", ddlIdp.SelectedValue),
new KeyValuePair <string, string>("id", Guid.NewGuid().ToString())
};
try
{
DataServices.executeSQLStatement(sql, paramList, dbConnection: SynapseHelpers.DBConnections.PGSQLConnectionSIS);
}
catch (Exception ex)
{
ShowError(ex.Message);
success = false;
}
if (success)
{
ShowSuccess("User Added"); LoadUsersInRole();
}
}
protected void gvLocalLogins_RowCommand(object sender, GridViewCommandEventArgs e)
{
bool success = true;
string sql = "insert into \"AspNetUserRoles\" (\"UserId\",\"RoleId\") values (@userid,@roleid) ";
var paramList = new List <KeyValuePair <string, string> >()
{
new KeyValuePair <string, string>("userid", e.CommandArgument.ToString()),
new KeyValuePair <string, string>("roleid", Session["SM_roleid"].ToString())
};
try
{
DataServices.executeSQLStatement(sql, paramList, dbConnection: SynapseHelpers.DBConnections.PGSQLConnectionSIS);
}
catch (Exception ex)
{
ShowError(ex.Message);
success = false;
}
if (success)
{
ShowSuccess("User Added"); LoadUsersInRole();
}
}
protected void btnAdd_Click(object sender, EventArgs e)
{
if (validateInput())
{
bool success = true;
string sql = "insert into \"ApiScopePermissions\" (\"ApiScopeId\",\"RoleId\",\"Id\") values (cast(@userid as int),@roleid ,@id) ";
var paramList = new List <KeyValuePair <string, string> >()
{
new KeyValuePair <string, string>("userid", ddlApiScopes.SelectedValue),
new KeyValuePair <string, string>("roleid", Session["SM_roleid"].ToString()),
new KeyValuePair <string, string>("id", Guid.NewGuid().ToString())
};
try
{
DataServices.executeSQLStatement(sql, paramList, dbConnection: SynapseHelpers.DBConnections.PGSQLConnectionSIS);
}
catch (Exception ex)
{
ShowError(ex.Message);
success = false;
}
if (success)
{
ShowSuccess("Scope Added"); LoadScopesInRole();
}
}
}
protected void dgUsersInRole_ItemCommand(object source, DataGridCommandEventArgs e)
{
bool success = true;
string sql = "delete from \"AspNetUserRoles\" where \"UserId\" = @userid and \"RoleId\" = @roleid;";
var paramList = new List <KeyValuePair <string, string> >()
{
new KeyValuePair <string, string>("userid", e.CommandArgument.ToString()),
new KeyValuePair <string, string>("roleid", Session["SM_roleid"].ToString()),
};
try
{
DataServices.executeSQLStatement(sql, paramList, dbConnection: SynapseHelpers.DBConnections.PGSQLConnectionSIS);
}
catch (Exception ex)
{
ShowError(ex.Message);
success = false;
}
if (success)
{
ShowSuccess("User removed"); LoadUsersInRole();
}
}
protected void btnValidateAndCreate_Click(object sender, EventArgs e)
{
if (validateInput())
{
bool success = true;
string sql = "INSERT INTO \"ApiScopes\" (\"Name\",\"DisplayName\",\"Description\",\"Required\",\"Emphasize\",\"ShowInDiscoveryDocument\",\"ApiResourceId\") VALUES ( @name,@displayname,@description,false,false,true,cast(@apiid as int));";
var paramList = new List <KeyValuePair <string, string> >()
{
new KeyValuePair <string, string>("name", txtResourceName.Text),
new KeyValuePair <string, string>("displayname", txtDisplayName.Text),
new KeyValuePair <string, string>("description", txtDescription.Text),
new KeyValuePair <string, string>("apiid", Session["SM_apiid"].ToString())
};
try
{
DataServices.executeSQLStatement(sql, paramList, dbConnection: SynapseHelpers.DBConnections.PGSQLConnectionSIS);
}
catch (Exception ex)
{
ShowError(ex.Message);
success = false;
}
if (success)
{
ShowSuccess("Scope " + txtResourceName.Text + " Added"); LoadScopesInAPI();
}
}
else
{
}
}
protected void btnValidateAndCreate_Click(object sender, EventArgs e)
{
if (validateInput())
{
bool success = true;
string sql = "INSERT INTO \"IdentityClaims\" (\"Type\", \"IdentityResourceId\") VALUES ( @type, cast(@resourceid as int));";
var paramList = new List <KeyValuePair <string, string> >()
{
new KeyValuePair <string, string>("type", txtClaimName.Text),
new KeyValuePair <string, string>("resourceid", ddlResource.SelectedValue),
};
try
{
DataServices.executeSQLStatement(sql, paramList, dbConnection: SynapseHelpers.DBConnections.PGSQLConnectionSIS);
}
catch (Exception ex)
{
ShowError(ex.Message);
success = false;
}
if (success)
{
ShowSuccess("Claim " + txtClaimName.Text + " Added"); LoadIdentityClaims();
}
}
else
{
}
}
protected void btnValidateAndCreate_Click(object sender, EventArgs e)
{
if (validateInput())
{
bool success = true;
string sql = "INSERT INTO \"ApiResources\" (\"Name\",\"DisplayName\",\"Enabled\",\"Description\") VALUES ( @name,@displayname,cast(@enabled as bool),@description);";
var paramList = new List <KeyValuePair <string, string> >()
{
new KeyValuePair <string, string>("name", txtResourceName.Text),
new KeyValuePair <string, string>("displayname", txtDisplayName.Text),
new KeyValuePair <string, string>("enabled", chkEnabled.Checked.ToString()),
new KeyValuePair <string, string>("description", txtDescription.Text)
};
try
{
DataServices.executeSQLStatement(sql, paramList, dbConnection: SynapseHelpers.DBConnections.PGSQLConnectionSIS);
}
catch (Exception ex)
{
ShowError(ex.Message);
success = false;
}
if (success)
{
ShowSuccess("API " + txtResourceName.Text + " Added"); LoadAPIResources();
}
}
else
{
}
}
protected void dgIdentityClaims_ItemCommand(object source, DataGridCommandEventArgs e)
{
bool success = true;
string sql = "delete from \"IdentityClaims\" where \"Id\" = cast(@id as int);";
var paramList = new List <KeyValuePair <string, string> >()
{
new KeyValuePair <string, string>("id", e.CommandArgument.ToString()),
};
try
{
DataServices.executeSQLStatement(sql, paramList, dbConnection: SynapseHelpers.DBConnections.PGSQLConnectionSIS);
}
catch (Exception ex)
{
ShowError(ex.Message);
success = false;
}
if (success)
{
ShowSuccess("Claim removed"); LoadIdentityClaims();
}
}
public static String UpdateOrdinalPosition(string listId, string listattribute_id, int ordinalposition)
{
string sql = "UPDATE listsettings.listattribute SET ordinalposition = CAST(@ordinalposition AS int) WHERE list_id= @list_id AND listattribute_id = @listattribute_id;";
var paramList = new List <KeyValuePair <string, string> >()
{
new KeyValuePair <string, string>("list_id", listId),
new KeyValuePair <string, string>("listattribute_id", listattribute_id),
new KeyValuePair <string, string>("ordinalposition", ordinalposition.ToString())
};
try
{
DataServices.executeSQLStatement(sql, paramList);
}
catch (Exception ex)
{
var httpErr = new SynapseHTTPError();
httpErr.ErrorCode = "HTTP.400";
httpErr.ErrorType = "Client Error";
httpErr.ErrorDescription = ex.ToString();
return(JsonConvert.SerializeObject(httpErr, Formatting.Indented, new JsonSerializerSettings {
NullValueHandling = NullValueHandling.Ignore
}));
}
return("Ordinal Position Updated");
}
protected void btnCreateNewEntity_Click(object sender, EventArgs e)
{
string tmpViewName = Guid.NewGuid().ToString("N");
//Check if we can create a temporary view using the supplied SQL Statement
string sqlCreate = "CREATE VIEW baseviewcore." + this.lblNamespaceName.Text + "_" + this.txtEntityName.Text + " AS " + this.txtSQL.Text + ";";
var paramListCreate = new List <KeyValuePair <string, string> >()
{
};
try
{
DataServices.executeSQLStatement(sqlCreate, paramListCreate);
}
catch (Exception ex)
{
this.lblError.Text = "Error creating view: " + ex.ToString();
this.lblError.Visible = true;
return;
}
string sql = "SELECT listsettings.createbaseview(@p_baseviewnamespaceid, @p_baseviewnamespace, @p_baseviewname, @p_baseviewdescription, @p_baseviewsqlstatement, @p_username);";
var paramList = new List <KeyValuePair <string, string> >()
{
new KeyValuePair <string, string>("p_baseviewnamespaceid", this.hdnNamespaceID.Value),
new KeyValuePair <string, string>("p_baseviewnamespace", SynapseHelpers.GetBaseviewNamespaceNameFromID(this.hdnNamespaceID.Value)),
new KeyValuePair <string, string>("p_baseviewname", this.txtEntityName.Text),
new KeyValuePair <string, string>("p_baseviewdescription", this.txtEntityComments.Text),
new KeyValuePair <string, string>("p_baseviewsqlstatement", this.txtSQL.Text),
new KeyValuePair <string, string>("p_username", this.hdnUserName.Value)
};
DataSet ds = new DataSet();
try
{
ds = DataServices.DataSetFromSQL(sql, paramList);
}
catch (Exception ex)
{
this.lblError.Text = "Error creating view: " + System.Environment.NewLine + ex.ToString();
this.lblError.Visible = true;
return;
}
DataTable dt = ds.Tables[0];
string newGuid = "";
try
{
newGuid = dt.Rows[0][0].ToString();
}
catch { }
Response.Redirect("BaseviewManagerView.aspx?id=" + newGuid);
}
private bool ValidateUser(out string emailvalidationstring)
{
emailvalidationstring = "";
string sql = "SELECT * FROM systemsettings.app_user WHERE emailaddress = @email;";
var paramList = new List <KeyValuePair <string, string> >()
{
new KeyValuePair <string, string>("email", this.txtEmail.Text)
};
DataSet ds = DataServices.DataSetFromSQL(sql, paramList);
DataTable dt = ds.Tables[0];
if (dt.Rows.Count > 0)
{
string newguid = System.Convert.ToString(System.Guid.NewGuid());
string sqlUpdate = "UPDATE systemsettings.app_user SET emailresetstring = @newguid WHERE emailaddress = @email;";
var paramListUpdate = new List <KeyValuePair <string, string> >()
{
new KeyValuePair <string, string>("email", this.txtEmail.Text),
new KeyValuePair <string, string>("newguid", newguid)
};
DataServices.executeSQLStatement(sqlUpdate, paramListUpdate);
emailvalidationstring = newguid;
return(true);
}
return(false);
}
protected void dgIdentityResource_ItemCommand(object source, DataGridCommandEventArgs e)
{
string id = e.CommandArgument.ToString().Split(':')[0];
string status = e.CommandArgument.ToString().Split(':')[1];
bool success = true;
string sql = "update \"IdentityResources\" set \"Enabled\" = cast(@status as bool) where \"Id\" = cast(@id as int);";
var paramList = new List <KeyValuePair <string, string> >()
{
new KeyValuePair <string, string>("status", status.ToLower() == "true"? "false":"true"),
new KeyValuePair <string, string>("id", id),
};
try
{
DataServices.executeSQLStatement(sql, paramList, dbConnection: SynapseHelpers.DBConnections.PGSQLConnectionSIS);
}
catch (Exception ex)
{
ShowError(ex.Message);
success = false;
}
if (success)
{
ShowSuccess("Resource " + (status.ToLower() == "true" ? "Disabled" : "Enabled")); LoadIdentityResources();
}
}
protected void btnSave_Click(object sender, EventArgs e)
{
string haserr = "form-group has-error";
string noerr = "form-group";
this.lblError.Text = string.Empty;
this.lblError.Visible = false;
this.lblSuccess.Visible = false;
this.fgOptionValueText.CssClass = noerr;
this.fgOptionDisplayText.CssClass = noerr;
if (string.IsNullOrEmpty(this.txtOptionValueText.Text.ToString()))
{
this.lblError.Text = "Please enter value associated with the option";
this.txtOptionValueText.Focus();
this.lblError.Visible = true;
this.fgOptionValueText.CssClass = haserr;
return;
}
if (string.IsNullOrEmpty(this.txtOptionDisplayText.Text.ToString()))
{
this.lblError.Text = "Please enter what you would like to display to the end user for this option";
this.txtOptionDisplayText.Focus();
this.lblError.Visible = true;
this.fgOptionDisplayText.CssClass = haserr;
return;
}
string sql = "UPDATE listsettings.questionoption SET optionvaluetext = @optionvaluetext, optiondisplaytext = @optiondisplaytext, optionflag = @optionflag, optionflagalt = @optionflagalt WHERE questionoption_id = @questionoption_id;";
string id = this.hdnOptionID.Value;
var paramList = new List <KeyValuePair <string, string> >()
{
new KeyValuePair <string, string>("questionoption_id", id),
new KeyValuePair <string, string>("optionvaluetext", this.txtOptionValueText.Text),
new KeyValuePair <string, string>("optiondisplaytext", this.txtOptionDisplayText.Text),
new KeyValuePair <string, string>("optionflag", this.txtOptionFlag.Text),
new KeyValuePair <string, string>("optionflagalt", this.txtOptionFlagAlt.Text),
};
try
{
DataServices.executeSQLStatement(sql, paramList);
}
catch (Exception ex)
{
this.lblError.Text = "Error updating question option: " + System.Environment.NewLine + ex.ToString();
this.lblError.Visible = true;
return;
}
Response.Redirect("ListOptionCollectionView.aspx?id=" + this.hdnOptionCollectionID.Value);
}
protected void btnResetPassword_Click(object sender, EventArgs e)
{
string haserr = "form-group has-error";
string noerr = "form-group";
this.lblError.Text = string.Empty;
this.lblError.Visible = false;
this.fgPassword.CssClass = noerr;
this.fgPassword.CssClass = noerr;
this.fgConfirmPassword.CssClass = noerr;
if (string.IsNullOrEmpty(this.txtRegistrationPassword.Text.ToString()))
{
this.lblError.Text = "Please enter a password";
this.lblError.Visible = true;
this.fgPassword.CssClass = haserr;
return;
}
if (string.IsNullOrEmpty(this.txtConfirmPassword.Text.ToString()))
{
this.lblError.Text = "Please confirm your password";
this.lblError.Visible = true;
this.fgConfirmPassword.CssClass = haserr;
return;
}
if (this.txtRegistrationPassword.Text != this.txtConfirmPassword.Text)
{
this.lblError.Text = "Passwords do not match";
this.lblError.Visible = true;
this.fgConfirmPassword.CssClass = haserr;
this.fgPassword.CssClass = haserr;
return;
}
string newguid = System.Convert.ToString(System.Guid.NewGuid());
string sqlUpdate = "UPDATE systemsettings.app_user SET userpassword = crypt(@userpassword, gen_salt('bf', 8)), emailresetstring = @newguid WHERE emailresetstring = @code;";
var paramListUpdate = new List <KeyValuePair <string, string> >()
{
new KeyValuePair <string, string>("code", this.hdnCode.Value),
new KeyValuePair <string, string>("userpassword", this.txtRegistrationPassword.Text),
new KeyValuePair <string, string>("newguid", newguid)
};
DataServices.executeSQLStatement(sqlUpdate, paramListUpdate);
this.pnlSuccess.Visible = false;
this.pnlResetWorked.Visible = true;
}
protected void btnCreateNewNamespace_Click(object sender, EventArgs e)
{
string sql = "INSERT INTO listsettings.baseviewnamespace(_createdsource, baseviewnamespace, baseviewnamespacedescription, _createdby) VALUES ('Synapse Studio', @baseviewnamespace, @baseviewnamespacedescription, @p_username);";
var paramList = new List <KeyValuePair <string, string> >()
{
new KeyValuePair <string, string>("baseviewnamespace", this.txtLocalNamespaceName.Text),
new KeyValuePair <string, string>("baseviewnamespacedescription", this.txtLocalNamespaceDescription.Text),
new KeyValuePair <string, string>("p_username", this.hdnUserName.Value)
};
DataServices.executeSQLStatement(sql, paramList);
BindGrid();
ResetForm();
}
protected void btnSave_Click(object sender, EventArgs e)
{
string haserr = "form-group has-error";
string noerr = "form-group";
this.lblError.Text = string.Empty;
this.lblError.Visible = false;
this.lblSuccess.Visible = false;
this.fgCollectionName.CssClass = noerr;
if (string.IsNullOrEmpty(this.txtCollectionName.Text.ToString()))
{
this.lblError.Text = "Please enter a new name for the collection";
this.txtCollectionName.Focus();
this.lblError.Visible = true;
this.fgCollectionName.CssClass = haserr;
return;
}
string sql = "INSERT INTO listsettings.questionoptioncollection(questionoptioncollection_id, questionoptioncollectionname, questionoptioncollectiondescription) VALUES (@questionoptioncollection_id, @questionoptioncollectionname, @questionoptioncollectiondescription);";
string id = System.Guid.NewGuid().ToString();
var paramList = new List <KeyValuePair <string, string> >()
{
new KeyValuePair <string, string>("questionoptioncollection_id", id),
new KeyValuePair <string, string>("questionoptioncollectionname", this.txtCollectionName.Text),
new KeyValuePair <string, string>("questionoptioncollectiondescription", this.txtCollectionDescription.Text)
};
try
{
DataServices.executeSQLStatement(sql, paramList);
}
catch (Exception ex)
{
this.lblError.Text = "Error creating question collection: " + System.Environment.NewLine + ex.ToString();
this.lblError.Visible = true;
return;
}
Response.Redirect("ListOptionCollectionView.aspx?id=" + id);
}
protected void btnSave_Click(object sender, EventArgs e)
{
string haserr = "form-group has-error";
string noerr = "form-group";
this.lblError.Text = string.Empty;
this.lblError.Visible = false;
this.lblSuccess.Visible = false;
this.fgRoleName.CssClass = noerr;
if (string.IsNullOrEmpty(this.txtRoleName.Text.ToString()))
{
this.lblError.Text = "Please enter a new name for the Role";
this.txtRoleName.Focus();
this.lblError.Visible = true;
this.fgRoleName.CssClass = haserr;
return;
}
string sql = "INSERT INTO \"AspNetRoles\"(\"Id\", \"Name\", \"NormalizedName\") VALUES (@id, @name, @nname);";
string id = System.Guid.NewGuid().ToString();
var paramList = new List <KeyValuePair <string, string> >()
{
new KeyValuePair <string, string>("id", id),
new KeyValuePair <string, string>("name", this.txtRoleName.Text),
new KeyValuePair <string, string>("nname", this.txtRoleName.Text.ToUpper())
};
try
{
DataServices.executeSQLStatement(sql, paramList, dbConnection: SynapseHelpers.DBConnections.PGSQLConnectionSIS);
}
catch (Exception ex)
{
this.lblError.Text = "Error creating Role: " + System.Environment.NewLine + ex.ToString();
this.lblError.Visible = true;
return;
}
Response.Redirect("ManageRole.aspx?id=" + id);
}
protected void btnSave_Click(object sender, EventArgs e)
{
string haserr = "form-group has-error";
string noerr = "form-group";
this.lblError.Text = string.Empty;
this.lblError.Visible = false;
this.lblSuccess.Visible = false;
this.fgCollectionName.CssClass = noerr;
if (string.IsNullOrEmpty(this.txtCollectionName.Text.ToString()))
{
this.lblError.Text = "Please enter a new name for the collection";
this.txtCollectionName.Focus();
this.lblError.Visible = true;
this.fgCollectionName.CssClass = haserr;
return;
}
string sql = "UPDATE listsettings.questionoptioncollection SET questionoptioncollectionname = @questionoptioncollectionname, questionoptioncollectiondescription = @questionoptioncollectiondescription WHERE questionoptioncollection_id = @questionoptioncollection_id;";
string id = this.hdnOptionCollectionID.Value;
var paramList = new List <KeyValuePair <string, string> >()
{
new KeyValuePair <string, string>("questionoptioncollection_id", id),
new KeyValuePair <string, string>("questionoptioncollectionname", this.txtCollectionName.Text),
new KeyValuePair <string, string>("questionoptioncollectiondescription", this.txtCollectionDescription.Text)
};
try
{
DataServices.executeSQLStatement(sql, paramList);
}
catch (Exception ex)
{
this.lblError.Text = "Error saving question collection: " + System.Environment.NewLine + ex.ToString();
this.lblError.Visible = true;
return;
}
Response.Redirect("ListManagerList.aspx");
}
protected void btnDelete_Click(object sender, EventArgs e)
{
string sql = "DELETE FROM listsettings.questionoption WHERE questionoption_id = @questionoption_id;";
string id = this.hdnOptionID.Value;
var paramList = new List <KeyValuePair <string, string> >()
{
new KeyValuePair <string, string>("questionoption_id", id)
};
try
{
DataServices.executeSQLStatement(sql, paramList);
}
catch (Exception ex)
{
this.lblError.Text = "Error updating question option: " + System.Environment.NewLine + ex.ToString();
this.lblError.Visible = true;
return;
}
Response.Redirect("ListOptionCollectionView.aspx?id=" + this.hdnOptionCollectionID.Value);
}
private void ValidateCode(string code)
{
bool emailconfirmed = false;
string sql = "SELECT * FROM app_user WHERE emailvalidationstring = @code;";
var paramList = new List <KeyValuePair <string, string> >()
{
new KeyValuePair <string, string>("code", code)
};
DataSet ds = DataServices.DataSetFromSQL(sql, paramList);
DataTable dt = ds.Tables[0];
if (dt.Rows.Count > 0)
{
try { emailconfirmed = System.Convert.ToBoolean(dt.Rows[0]["emailconfirmed"].ToString()); } catch { }
if (emailconfirmed)
{
this.pnlAlreadyConfirmed.Visible = true;
return;
}
else
{
this.pnlSuccess.Visible = true;
string sqlConfirm = "UPDATE app_user SET emailconfirmed = true, emailconfirmedtimestamp = now() WHERE emailvalidationstring = @code;";
DataServices.executeSQLStatement(sqlConfirm, paramList);
return;
}
}
else
{
this.pnlFailed.Visible = true;
return;
}
}
public static String RemoveAttributeFromList(string listId, string attributename)
{
string sqlCheck = "SELECT ordinalposition FROM listsettings.listattribute WHERE list_id= @list_id AND attributename = @attributename;";
var paramListCheck = new List <KeyValuePair <string, string> >()
{
new KeyValuePair <string, string>("list_id", listId),
new KeyValuePair <string, string>("attributename", attributename),
};
DataSet dsCheck = DataServices.DataSetFromSQL(sqlCheck, paramListCheck);
DataTable dtCheck = new DataTable();
dtCheck = dsCheck.Tables[0];
int ordinalposition = 0;
try
{
ordinalposition = System.Convert.ToInt16(dtCheck.Rows[0]["ordinalposition"].ToString());
}
catch (Exception ex)
{
var a = ex;
}
string sql = "";
sql = @"UPDATE listsettings.listattribute
SET isselected = false,
ordinalposition = null
WHERE list_id = @list_id
AND attributename = @attributename;
UPDATE listsettings.listattribute
SET ordinalposition = ordinalposition - 1
WHERE list_id = @list_id
AND ordinalposition > CAST(@ordinalposition AS INT);
";
var paramList = new List <KeyValuePair <string, string> >()
{
new KeyValuePair <string, string>("list_id", listId),
new KeyValuePair <string, string>("attributename", attributename),
new KeyValuePair <string, string>("ordinalposition", ordinalposition.ToString()),
};
try
{
DataServices.executeSQLStatement(sql, paramList);
}
catch (Exception ex)
{
var httpErr = new SynapseHTTPError();
httpErr.ErrorCode = "HTTP.400";
httpErr.ErrorType = "Client Error";
httpErr.ErrorDescription = ex.ToString();
return(JsonConvert.SerializeObject(httpErr, Formatting.Indented, new JsonSerializerSettings {
NullValueHandling = NullValueHandling.Ignore
}));
}
return("Attribute removed");
}
public static String AddAttributeToList(string listId, string attributename, int ordinalposition)
{
string sqlCheck = "SELECT COUNT(*) AS recCount FROM listsettings.listattribute WHERE list_id= @list_id AND attributename = @attributename;";
var paramListCheck = new List <KeyValuePair <string, string> >()
{
new KeyValuePair <string, string>("list_id", listId),
new KeyValuePair <string, string>("attributename", attributename),
};
DataSet dsCheck = DataServices.DataSetFromSQL(sqlCheck, paramListCheck);
DataTable dtCheck = new DataTable();
dtCheck = dsCheck.Tables[0];
int recs = 0;
try
{
recs = System.Convert.ToInt16(dtCheck.Rows[0]["recCount"].ToString());
}
catch { }
string sql = "";
if (recs == 0) //Do insert
{
sql = @"INSERT INTO listsettings.listattribute(listattribute_id, list_id, baseviewattribute_id, attributename, datatype, ordinalposition)
SELECT uuid_generate_v4() AS listattribute_id, @list_id, baseviewattribute_id, @attributename, datatype, CAST(@ordinalposition AS INT) AS ordinalposition
FROM listsettings.baseviewattribute WHERE attributename = @attributename
AND baseview_id IN (SELECT baseview_id FROM listsettings.listmanager WHERE list_id = @list_id);";
}
else //Do update
{
sql = @"UPDATE listsettings.listattribute
SET isselected = true,
ordinalposition = CAST(@ordinalposition AS INT)
WHERE list_id = @list_id
AND attributename = @attributename;";
}
var paramList = new List <KeyValuePair <string, string> >()
{
new KeyValuePair <string, string>("list_id", listId),
new KeyValuePair <string, string>("attributename", attributename),
new KeyValuePair <string, string>("ordinalposition", ordinalposition.ToString()),
};
try
{
DataServices.executeSQLStatement(sql, paramList);
}
catch (Exception ex)
{
var httpErr = new SynapseHTTPError();
httpErr.ErrorCode = "HTTP.400";
httpErr.ErrorType = "Client Error";
httpErr.ErrorDescription = ex.ToString();
return(JsonConvert.SerializeObject(httpErr, Formatting.Indented, new JsonSerializerSettings {
NullValueHandling = NullValueHandling.Ignore
}));
}
return("Attribute added");
}
protected void btnLogin_Click(object sender, EventArgs e)
{
this.lblError.Text = string.Empty;
this.lblError.Visible = false;
this.btnResendValidationEmail.Visible = false;
this.txtEmail.CssClass = this.txtEmail.CssClass.Replace("has-error", "");
this.txtPassword.CssClass = this.txtPassword.CssClass.Replace("has-error", "");
this.fgtxtEmail.CssClass = this.fgtxtEmail.CssClass.Replace("has-error", "");
this.fgtxtPassword.CssClass = this.fgtxtPassword.CssClass.Replace("has-error", "");
if (string.IsNullOrEmpty(this.txtEmail.Text.ToString()))
{
this.lblError.Text = "Please enter your email address";
this.lblError.Visible = true;
this.fgtxtEmail.CssClass = this.fgtxtEmail.CssClass.Replace("form-group", "form-group has-error");
return;
}
if (string.IsNullOrEmpty(this.txtPassword.Text.ToString()))
{
this.lblError.Text = "Please enter your password";
this.lblError.Visible = true;
this.fgtxtPassword.CssClass = this.fgtxtPassword.CssClass.Replace("form-group", "form-group has-error");
return;
}
string IPAddress = "";
try
{
IPAddress = GetIPAddress();
}
catch { }
string sql = "SELECT * FROM systemsettings.app_user WHERE emailaddress = @email AND userpassword = crypt(@password, userpassword);";
var paramList = new List <KeyValuePair <string, string> >()
{
new KeyValuePair <string, string>("email", this.txtEmail.Text),
new KeyValuePair <string, string>("password", this.txtPassword.Text)
};
DataSet ds = DataServices.DataSetFromSQL(sql, paramList);
DataTable dt = ds.Tables[0];
if (dt.Rows.Count > 0)
{
//Valid User
Session["UserDetailsSxn"] = dt;
//Record Login
string userid = "0";
try
{
userid = dt.Rows[0]["userid"].ToString();
}
catch { }
Session["userID"] = userid;
string emailconfirmed = "False";
try
{
emailconfirmed = dt.Rows[0]["emailconfirmed"].ToString();
}
catch { }
string userFullName = "";
try
{
userFullName = dt.Rows[0]["firstname"].ToString() + " " + dt.Rows[0]["lastname"].ToString();
}
catch { }
Session["userFullName"] = userFullName;
string userType = "";
try
{
userType = dt.Rows[0]["usertype"].ToString();
}
catch
{
//Response.Redirect("Login.aspx");
}
Session["userType"] = userType;
string matchedclinicianid = "";
try
{
matchedclinicianid = dt.Rows[0]["matchedclinicianid"].ToString();
}
catch
{
//Response.Redirect("Login.aspx");
}
Session["matchedclinicianid"] = matchedclinicianid;
this.hdnEmail.Value = this.txtEmail.Text;
if (emailconfirmed == "False")
{
this.lblError.Text = "Your account has been created but you have not confirmed your email address yet.<br /><br />Please check your spam folder for the email containing the link to confirm your account";
this.btnResendValidationEmail.Visible = true;
this.lblError.Visible = true;
return;
}
string isauthorised = "False";
try
{
isauthorised = dt.Rows[0]["isauthorised"].ToString();
}
catch { }
if (isauthorised == "False")
{
this.lblError.Text = "Your account has not been authorised yet";
this.lblError.Visible = true;
return;
}
sql = "INSERT INTO systemsettings.loginhistory (userid, emailaddress, ipaddress) VALUES (CAST(@userid AS INT), @emailaddress, @ipaddress);";
var paramListHistory = new List <KeyValuePair <string, string> >()
{
new KeyValuePair <string, string>("userid", userid),
new KeyValuePair <string, string>("emailaddress", this.txtEmail.Text),
new KeyValuePair <string, string>("ipaddress", IPAddress)
};
DataServices.executeSQLStatement(sql, paramListHistory);
Response.Redirect(this.lblRedirect.Text);
}
else
{
//Invalid User
sql = "INSERT INTO systemsettings.failedlogin(emailaddress, ipaddress) VALUES ( @emailaddress, @ipaddress); ";
var paramListFail = new List <KeyValuePair <string, string> >()
{
new KeyValuePair <string, string>("emailaddress", this.txtEmail.Text),
new KeyValuePair <string, string>("ipaddress", IPAddress)
};
DataServices.executeSQLStatement(sql, paramListFail);
this.lblError.Text = "Invalid Username or Password";
this.lblError.Visible = true;
}
}
protected void btnValidateEntity_Click(object sender, EventArgs e)
{
string haserr = "form-group has-error";
string noerr = "form-group";
this.lblError.Text = string.Empty;
this.lblError.Visible = false;
this.lblSuccess.Visible = false;
this.fgSQL.CssClass = noerr;
////Check if name already exists
//string sql = "SELECT * FROM listsettings.baseviewmanager WHERE baseviewnamespaceid = @baseviewnamespaceid and baseviewname = @baseviewname;";
//var paramList = new List<KeyValuePair<string, string>>() {
// new KeyValuePair<string, string>("baseviewnamespaceid", this.hdnNamespaceID.Value),
// new KeyValuePair<string, string>("baseviewname", this.lblBaseViewName.Text)
//};
//DataSet ds = DataServices.DataSetFromSQL(sql, paramList);
//DataTable dt = ds.Tables[0];
//if (dt.Rows.Count > 0)
//{
// this.lblError.Text = "The name of the baseview that you you have entered already exists";
// this.txtSQL.Focus();
// this.lblError.Visible = true;
// this.fgSQL.CssClass = haserr;
// return;
//}
string tmpViewName = Guid.NewGuid().ToString("N");
//Check if we can create a temporary view using the supplied SQL Statement
string sqlCreate = "CREATE VIEW baseviewtemp." + this.lblNamespaceName.Text + "_" + tmpViewName + " AS " + this.txtSQL.Text + ";";
var paramListCreate = new List <KeyValuePair <string, string> >()
{
};
try
{
DataServices.executeSQLStatement(sqlCreate, paramListCreate);
}
catch (Exception ex)
{
this.lblError.Text = "Error creating view: " + ex.ToString();
this.txtSQL.Focus();
this.lblError.Visible = true;
this.fgSQL.CssClass = haserr;
return;
}
this.lblSuccess.Visible = true;
this.lblSuccess.Text = "Validation succeeded";
this.btnCreateNewEntity.Visible = true;
this.btnCancel.Visible = true;
this.btnCreateNewEntity.Focus();
this.btnValidateEntity.Visible = false;
this.lblBaseViewName.Enabled = false;
this.txtSQL.Enabled = false;
}
protected void btnCreateNewEntity_Click(object sender, EventArgs e)
{
//Drop the baseeview
string sqlDrop = @"SELECT listsettings.dropbaseview(
@p_baseview_id,
@p_baseviewname
)";
var paramListDrop = new List <KeyValuePair <string, string> >()
{
new KeyValuePair <string, string>("p_baseview_id", this.hdnBaseViewID.Value),
new KeyValuePair <string, string>("p_baseviewname", this.lblSummaryType.Text)
};
DataServices.ExcecuteNonQueryFromSQL(sqlDrop, paramListDrop);
//Recreate the baseview
//string tmpViewName = Guid.NewGuid().ToString("N");
//Check if we can create a temporary view using the supplied SQL Statement
string sqlCreate = "CREATE VIEW baseviewcore." + this.lblNamespaceName.Text + "_" + this.lblBaseViewName.Text + " AS " + this.txtSQL.Text + ";";
var paramListCreate = new List <KeyValuePair <string, string> >()
{
};
try
{
DataServices.executeSQLStatement(sqlCreate, paramListCreate);
}
catch (Exception ex)
{
this.lblError.Text = "Error creating view: " + ex.ToString();
this.lblError.Visible = true;
return;
}
string baseviewid = this.hdnBaseViewID.Value;
string sql = "SELECT listsettings.recreatebaseview(@p_baseviewnamespaceid, @p_baseviewnamespace, @p_baseviewname, @p_baseviewdescription, @p_baseviewsqlstatement, @p_username, @baseviewid);";
var paramList = new List <KeyValuePair <string, string> >()
{
new KeyValuePair <string, string>("p_baseviewnamespaceid", this.hdnNamespaceID.Value),
new KeyValuePair <string, string>("p_baseviewnamespace", this.lblNamespaceName.Text),
new KeyValuePair <string, string>("p_baseviewname", this.lblBaseViewName.Text),
new KeyValuePair <string, string>("p_baseviewdescription", this.lblBaseViewComments.Text),
new KeyValuePair <string, string>("p_baseviewsqlstatement", this.txtSQL.Text),
new KeyValuePair <string, string>("p_username", this.hdnUserName.Value),
new KeyValuePair <string, string>("baseviewid", this.hdnBaseViewID.Value),
};
DataSet ds = new DataSet();
try
{
ds = DataServices.DataSetFromSQL(sql, paramList);
}
catch (Exception ex)
{
this.lblError.Text = "Error creating view: " + ex.ToString();
this.lblError.Visible = true;
return;
}
DataTable dt = ds.Tables[0];
string newGuid = "";
try
{
newGuid = dt.Rows[0][0].ToString();
}
catch { }
Response.Redirect("BaseViewManageSQL.aspx?id=" + newGuid);
}
protected void btnSave_Click(object sender, EventArgs e)
{
string sql = "UPDATE listsettings.question SET defaultcontext = @defaultcontext, defaultcontextfieldname = @defaultcontextfieldname, questiontype_id = @questiontype_id, questiontypetext = @questiontypetext, labeltext = @labeltext, defaultvaluetext = @defaultvaluetext, defaultvaluedatetime = null, questionquickname = @questionquickname, questionview_id = @questionview_id, questionviewname = @questionviewname, questionviewsql = @questionviewsql, optiontype = @optiontype, questionoptioncollection_id = @questionoptioncollection_id, questionoptionsqlstatement = @questionoptionsqlstatement, questioncustomhtml = @questioncustomhtml, questioncustomhtmlalt = @questioncustomhtmlalt WHERE question_id= @question_id;";
//, , , , , , , , , , , , , ,
string question_id = this.hdnQuestionID.Value;
string defaultcontext = this.ddlDefaultContext.SelectedValue;
string defaultcontextfieldname = this.lblDefaultContextField.Text;
string questiontype_id = this.ddlQuestionType.SelectedValue;
string questiontypetext = this.ddlQuestionType.SelectedItem.Text;
string labeltext = "";
string defaultvaluetext = "";
string questioncustomhtml = "";
string questioncustomhtmlalt = "";
if (this.ddlQuestionType.SelectedValue != "3aa99ab6-9df6-4c3a-a966-6cc51ce1a3bf") //"Not HTML Tag (Label, Custom HTML)" 3
{
defaultvaluetext = this.txtDefaultValueText.Text;
labeltext = this.txtLabelText.Text;
}
if (this.ddlQuestionType.SelectedValue == "3aa99ab6-9df6-4c3a-a966-6cc51ce1a3bf") //"HTML Tag (Label, Custom HTML)" 3
{
questioncustomhtml = this.txtCustomHTML.Text;
}
if (this.ddlQuestionType.SelectedValue == "164c31d5-d32e-4c97-91d6-a0d01822b9b6" || this.ddlQuestionType.SelectedValue == "221ca4a0-3a39-42ff-a0f4-885ffde0f0bd") //"Single Checkbox (Binary)" or "Checkbox Image (Binary)"
{
questioncustomhtml = this.txtCustomHTML.Text;
questioncustomhtmlalt = this.txtCustomHTMLAlt.Text;
}
string questionquickname = this.txtQuickName.Text;
string questionview_id = "";
string questionviewname = "";
string questionviewsql = "";
string optiontype = "";
string questionoptioncollection_id = "";
string questionoptionsqlstatement = "";
if (this.ddlQuestionType.SelectedValue == "fc1f2643-b491-4889-8d1a-910619b65722" ||
this.ddlQuestionType.SelectedValue == "3d236e17-e40e-472d-95a5-5e45c5e02faf" ||
this.ddlQuestionType.SelectedValue == "4f31c02d-fa36-4033-8977-8f25bef33d52" ||
this.ddlQuestionType.SelectedValue == "ca1f1b24-b490-4e57-8921-9f680819e47c" ||
this.ddlQuestionType.SelectedValue == "71490eff-a54b-455a-86b1-a4d5ab676f32"
)
// "Drop Down List" 4
// "Check Box List" 5
// "Auto-complete Selection List" 8
// "Radio Button List"
// "Radio Button Image List"
{
optiontype = this.ddlOptionType.SelectedValue;
if (this.ddlOptionType.SelectedValue == "e9e6feda-f02d-4388-8c5b-9fc97558c684")//Internal Option Collection
{
questionoptioncollection_id = this.ddlOptionCollection.SelectedValue;
}
if (this.ddlOptionType.SelectedValue == "638dadd6-fca7-4f9b-b25f-692c45172524") //Custom SQL Statement
{
questionoptionsqlstatement = this.txtCustomHTML.Text;
}
}
var paramList = new List <KeyValuePair <string, string> >()
{
new KeyValuePair <string, string>("question_id", question_id),
new KeyValuePair <string, string>("defaultcontext", defaultcontext),
new KeyValuePair <string, string>("defaultcontextfieldname", defaultcontextfieldname),
new KeyValuePair <string, string>("questiontype_id", questiontype_id),
new KeyValuePair <string, string>("questiontypetext", questiontypetext),
new KeyValuePair <string, string>("labeltext", labeltext),
new KeyValuePair <string, string>("questioncustomhtml", questioncustomhtml),
new KeyValuePair <string, string>("questionquickname", questionquickname),
new KeyValuePair <string, string>("defaultvaluetext", defaultvaluetext),
new KeyValuePair <string, string>("questionview_id", questionview_id),
new KeyValuePair <string, string>("questionviewname", questionviewname),
new KeyValuePair <string, string>("questionviewsql", questionviewsql),
new KeyValuePair <string, string>("optiontype", optiontype),
new KeyValuePair <string, string>("questionoptioncollection_id", questionoptioncollection_id),
new KeyValuePair <string, string>("questionoptionsqlstatement", questionoptionsqlstatement),
new KeyValuePair <string, string>("questioncustomhtmlalt", questioncustomhtmlalt)
};
try
{
DataServices.executeSQLStatement(sql, paramList);
}
catch (Exception ex)
{
this.lblError.Text = "Error creating question: " + System.Environment.NewLine + ex.ToString();
this.lblError.Visible = true;
return;
}
Response.Redirect("ListManagerList.aspx?id=" + question_id);
}
protected void btnRegister_Click(object sender, EventArgs e)
{
string haserr = "form-group has-error";
string noerr = "form-group";
this.lblError.Text = string.Empty;
this.lblError.Visible = false;
this.fgEmail.CssClass = noerr;
this.fgPassword.CssClass = noerr;
this.fgEmail.CssClass = noerr;
this.fgPassword.CssClass = noerr;
this.fgConfirmPassword.CssClass = noerr;
this.fgMatchedOrganisation.CssClass = noerr;
this.fgFirstName.CssClass = noerr;
this.fgLastName.CssClass = noerr;
this.fgGMCCode.CssClass = noerr;
//if (this.ddlMatchedOrganisation.SelectedIndex == 0)
//{
// this.lblError.Text = "Please select an organisation";
// this.lblError.Visible = true;
// this.fgMatchedOrganisation.CssClass = haserr;
// return;
//}
if (string.IsNullOrEmpty(this.txtFirstName.Text.ToString()))
{
this.lblError.Text = "Please enter your first name";
this.lblError.Visible = true;
this.fgFirstName.CssClass = haserr;
return;
}
if (string.IsNullOrEmpty(this.txtLastName.Text.ToString()))
{
this.lblError.Text = "Please enter your last name";
this.lblError.Visible = true;
this.fgLastName.CssClass = haserr;
return;
}
//if (string.IsNullOrEmpty(this.txtGMCCode.Text.ToString()))
//{
// this.lblError.Text = "Please enter your GMC Number";
// this.lblError.Visible = true;
// this.fgGMCCode.CssClass = haserr;
// return;
//}
if (string.IsNullOrEmpty(this.txtRegistrationEmail.Text.ToString()))
{
this.lblError.Text = "Please enter your email address";
this.lblError.Visible = true;
this.fgEmail.CssClass = haserr;
return;
}
if (CheckEmailAddress() == 1)
{
this.lblError.Text = "This email address has already been registered";
this.lblError.Visible = true;
this.fgEmail.CssClass = haserr;
return;
}
if (string.IsNullOrEmpty(this.txtRegistrationPassword.Text.ToString()))
{
this.lblError.Text = "Please enter a password";
this.lblError.Visible = true;
this.fgPassword.CssClass = haserr;
return;
}
if (string.IsNullOrEmpty(this.txtConfirmPassword.Text.ToString()))
{
this.lblError.Text = "Please confirm your password";
this.lblError.Visible = true;
this.fgConfirmPassword.CssClass = haserr;
return;
}
if (this.txtRegistrationPassword.Text != this.txtConfirmPassword.Text)
{
this.lblError.Text = "Passwords do not match";
this.lblError.Visible = true;
this.fgConfirmPassword.CssClass = haserr;
this.fgPassword.CssClass = haserr;
return;
}
string sql = "INSERT INTO systemsettings.app_user(usertype, userpassword, emailaddress, firstname, lastname, isactive, emailconfirmed, issysadmin, isauthorised)";
sql += " VALUES (@usertype, crypt(@userpassword, gen_salt('bf', 8)), @emailaddress, @firstname, @lastname, true, true, true, true)";
var paramListSave = new List <KeyValuePair <string, string> >()
{
new KeyValuePair <string, string>("usertype", this.ddlUserType.SelectedValue),
new KeyValuePair <string, string>("userpassword", this.txtRegistrationPassword.Text),
new KeyValuePair <string, string>("emailaddress", this.txtRegistrationEmail.Text),
new KeyValuePair <string, string>("firstname", this.txtFirstName.Text),
new KeyValuePair <string, string>("lastname", this.txtLastName.Text)
};
DataServices.executeSQLStatement(sql, paramListSave);
Response.Redirect("RegistrationThankYou.aspx?id=patient");
}