private void GetDefaultUnitsAndReferenceRange()
{
string sql = "SELECT * FROM app_testtype WHERE testtypeid = CAST(@testtypeid AS INT);";
var paramList = new List <KeyValuePair <string, string> >()
{
new KeyValuePair <string, string>("testtypeid", this.ddltesttypeid.SelectedValue)
};
DataSet ds = DataServices.DataSetFromSQL(sql, paramList);
DataTable dt = ds.Tables[0];
try
{
this.txtunitstext.Text = dt.Rows[0]["unitstext"].ToString();
}
catch { }
try
{
this.txtlowerreferencerange.Text = dt.Rows[0]["lowerreferencerange"].ToString();
}
catch { }
try
{
this.txtupperreferencerange.Text = dt.Rows[0]["upperreferencerange"].ToString();
}
catch { }
}
private void GetClinicianDataFromString(string id)
{
string sql = "SELECT * FROM app_user WHERE userid = CAST(@userid AS INT);";
var paramList = new List <KeyValuePair <string, string> >()
{
new KeyValuePair <string, string>("userid", id)
};
DataSet ds = DataServices.DataSetFromSQL(sql, paramList);
DataTable dt = ds.Tables[0];
if (dt.Rows.Count > 0)
{
string userFullName = dt.Rows[0]["firstname"].ToString() + " " + dt.Rows[0]["lastname"].ToString();
try
{
this.lblClinianName.Text = userFullName.ToUpper();
}
catch { }
try
{
this.lblGMC.Text = dt.Rows[0]["gmccode"].ToString();
}
catch { }
}
}
private bool ValidateUser(out string emailvalidationstring)
{
emailvalidationstring = "";
string sql = "SELECT * FROM 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 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);
}
private void BindMyResults()
{
string sql = "SELECT * FROM app_test WHERE testtypeid = CAST(@testtypeid AS INT) AND patientid = CAST(@patientid AS INT);";
var paramList = new List <KeyValuePair <string, string> >()
{
new KeyValuePair <string, string>("testtypeid", this.ddltesttypeid.SelectedValue),
new KeyValuePair <string, string>("patientid", this.hdnPatientID.Value)
};
DataSet ds = DataServices.DataSetFromSQL(sql, paramList);
DataTable dt = ds.Tables[0];
this.dgAllMyResults.DataSource = dt;
this.dgAllMyResults.DataBind();
this.lblTestType.Text = this.ddltesttypeid.SelectedItem.Text;
this.lblResultCount.Text = dt.Rows.Count.ToString();
this.Chart1.DataSource = dt;
//Give two columns of data to Y-axle
Chart1.Series[0].YValueMembers = "testnumericresult";
Chart1.Series[0].XValueMember = "_createddate";
Chart1.ChartAreas[0].AxisX.LabelStyle.Enabled = false;
Chart1.DataBind();
}
private int CheckEmailAddress()
{
/*
* 0 Okay
* 1 Email exists
* 2 Email not correct extension
*/
string sql = "SELECT * from app_user where emailaddress = @email;";
var paramList = new List <KeyValuePair <string, string> >()
{
new KeyValuePair <string, string>("email", this.txtRegistrationEmail.Text),
};
DataSet ds = DataServices.DataSetFromSQL(sql, paramList);
DataTable dt = ds.Tables[0];
if (dt.Rows.Count > 0)
{
return(1);
}
else
{
return(0);
}
}
//RadioButtonList
private void BindRadioList(RadioButtonList rad, string sql, string valueField, string displayField)
{
DataSet ds = DataServices.DataSetFromSQL(sql);
rad.DataSource = ds;
rad.DataValueField = valueField;
rad.DataTextField = displayField;
rad.DataBind();
}
private void GetTnCs()
{
string sql = "SELECT * FROM systemsetup WHERE systemsetupid = 1;";
DataSet ds = DataServices.DataSetFromSQL(sql, null);
DataTable dt = ds.Tables[0];
if (dt.Rows.Count > 0)
{
try { this.lblTermsAndConditions.Text = dt.Rows[0]["patienttermsandconditions"].ToString(); } catch { }
}
}
private void GetAllUsers()
{
string sql = "SELECT * FROM app_user WHERE usertype='Clinician' AND organisationid = CAST(@organisationid AS INT) AND isauthorised = true ORDER BY firstname, lastname;";
var paramList = new List <KeyValuePair <string, string> >()
{
};
DataSet ds = DataServices.DataSetFromSQL(sql, paramList);
DataTable dt = ds.Tables[0];
this.dgMyPatients.DataSource = dt;
this.dgMyPatients.DataBind();
this.lblAllPatientCount.Text = dt.Rows.Count.ToString();
}
private string GetSiteURL()
{
string sql = "SELECT * FROM systemsetup WHERE systemsetupid = 1;";
string siteURL = "";
DataSet ds = DataServices.DataSetFromSQL(sql, null);
DataTable dt = ds.Tables[0];
if (dt.Rows.Count > 0)
{
try { siteURL = dt.Rows[0]["siteurl"].ToString(); } catch { }
}
return(siteURL);
}
private void GetMyPatients(string id)
{
string sql = "SELECT * FROM app_user WHERE matchedclinicianid = CAST(@userid AS INT) AND isauthorised = true ORDER BY firstname, lastname;";
var paramList = new List <KeyValuePair <string, string> >()
{
new KeyValuePair <string, string>("userid", id)
};
DataSet ds = DataServices.DataSetFromSQL(sql, paramList);
DataTable dt = ds.Tables[0];
this.dgMyPatients.DataSource = dt;
this.dgMyPatients.DataBind();
this.lblAllPatientCount.Text = dt.Rows.Count.ToString();
}
private void BindMyNewResults()
{
string sql = "SELECT a.testid, a.testnumericresult, b.testtypename, a._createddate FROM app_test a INNER JOIN app_testtype b ON a.testtypeid = b.testtypeid WHERE COALESCE(patienthasviewed,false) = false AND patientid = CAST(@patientid AS INT) ORDER BY a._createddate DESC;";
var paramList = new List <KeyValuePair <string, string> >()
{
new KeyValuePair <string, string>("testtypeid", this.ddltesttypeid.SelectedValue),
new KeyValuePair <string, string>("patientid", this.hdnPatientID.Value)
};
DataSet ds = DataServices.DataSetFromSQL(sql, paramList);
DataTable dt = ds.Tables[0];
this.dgMyNewResults.DataSource = dt;
this.dgMyNewResults.DataBind();
}
//Drop Down Lists
private void BindDropDownList(DropDownList ddl, string sql, string valueField, string displayField, int addPleaseSelect)
{
DataSet ds = DataServices.DataSetFromSQL(sql);
ddl.DataSource = ds;
ddl.DataValueField = valueField;
ddl.DataTextField = displayField;
ddl.DataBind();
if (addPleaseSelect == 1)
{
ListItem[] items = new ListItem[1];
items[0] = new ListItem("Please select ...", "0");
ddl.Items.Insert(0, items[0]);
}
}
private void GetMyRejectedUsers()
{
string sql = "SELECT * FROM app_user WHERE usertype='Clinician' AND organisationid = CAST(@organisationid AS INT) AND COALESCE(isrejected, false) = true ORDER BY firstname, lastname;";
var paramList = new List <KeyValuePair <string, string> >()
{
new KeyValuePair <string, string>("organisationid", this.ddlMatchedOrganisation.SelectedValue)
};
DataSet ds = DataServices.DataSetFromSQL(sql, paramList);
DataTable dt = ds.Tables[0];
this.dgMyRejectedRequests.DataSource = dt;
this.dgMyRejectedRequests.DataBind();
this.lbRejectedRequestCount.Text = dt.Rows.Count.ToString();
}
private void GetPatientData(string id)
{
string sql = "SELECT * FROM app_user WHERE userid = CAST(@userid AS INT);";
var paramList = new List <KeyValuePair <string, string> >()
{
new KeyValuePair <string, string>("userid", id)
};
DataSet ds = DataServices.DataSetFromSQL(sql, paramList);
DataTable dt = ds.Tables[0];
if (dt.Rows.Count > 0)
{
string userFullName = dt.Rows[0]["firstname"].ToString() + " " + dt.Rows[0]["lastname"].ToString();
try
{
this.lblPatientName.Text = userFullName.ToUpper();
}
catch { }
try
{
this.lblNHS.Text = dt.Rows[0]["nhsnumber"].ToString();
}
catch { }
try
{
this.lblDoB.Text = dt.Rows[0]["dateofbirth"].ToString().Substring(0, 10);;
}
catch { }
try
{
this.hdnMatchedclinicianid.Value = dt.Rows[0]["matchedclinicianid"].ToString();
}
catch { }
}
}
private void GetTestType(string id)
{
string sql = "SELECT * FROM app_testtype WHERE testtypeid = CAST(@testid AS INT);";
var paramList = new List <KeyValuePair <string, string> >()
{
new KeyValuePair <string, string>("testtypeid", id)
};
DataSet ds = DataServices.DataSetFromSQL(sql, paramList);
DataTable dt = ds.Tables[0];
if (dt.Rows.Count > 0)
{
try
{
this.lbltesttypename.Text = dt.Rows[0]["testtypename"].ToString();
}
catch { }
}
}
private void ValidateCode(string code)
{
string sql = "SELECT * FROM app_user WHERE emailresetstring = @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)
{
this.pnlSuccess.Visible = true;
return;
}
else
{
this.pnlFailed.Visible = true;
return;
}
}
private string GetUserValidationString()
{
string emailvalidationstring = "";
string sql = "SELECT * FROM app_user WHERE emailaddress = @email;";
var paramList = new List <KeyValuePair <string, string> >()
{
new KeyValuePair <string, string>("email", this.txtRegistrationEmail.Text)
};
DataSet ds = DataServices.DataSetFromSQL(sql, paramList);
DataTable dt = ds.Tables[0];
if (dt.Rows.Count > 0)
{
try { emailvalidationstring = dt.Rows[0]["emailvalidationstring"].ToString(); } catch { }
}
return(emailvalidationstring);
}
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 int SendMail(string messageBody, string messageSubject, string emailTo, out string msg)
{
string emailhost = "";
string emailuser = "";
string emailpassword = "";
Int16 emailport = 0;
bool emailusetls = false;
string emailfromaddress = "";
string emailfromname = "Physical Health App";
string sql = "SELECT * FROM systemsetup WHERE systemsetupid = 1;";
DataSet ds = DataServices.DataSetFromSQL(sql, null);
DataTable dt = ds.Tables[0];
if (dt.Rows.Count > 0)
{
try { emailhost = dt.Rows[0]["emailhost"].ToString(); } catch { }
try { emailuser = dt.Rows[0]["emailuser"].ToString(); } catch { }
try { emailpassword = dt.Rows[0]["emailpassword"].ToString(); } catch { }
try { emailport = System.Convert.ToInt16(dt.Rows[0]["emailport"].ToString()); } catch { }
try { emailusetls = System.Convert.ToBoolean(dt.Rows[0]["emailusetls"].ToString()); } catch { }
try { emailfromaddress = dt.Rows[0]["emailfromaddress"].ToString(); } catch { }
try { emailfromname = dt.Rows[0]["emailfromname"].ToString(); } catch { }
}
if (string.IsNullOrEmpty(emailhost))
{
msg = "Email not configured";
return(0);
}
MailMessage Message = new MailMessage();
Message.Subject = messageSubject;
Message.Body = messageBody;
Message.From = new System.Net.Mail.MailAddress(emailfromaddress, emailfromname);
Message.ReplyToList.Add(Message.From);
Message.IsBodyHtml = true;
Message.To.Add(new MailAddress(emailTo));
SmtpClient client = new SmtpClient();
client.Host = emailhost;
client.Port = emailport;
client.UseDefaultCredentials = true;
client.DeliveryMethod = SmtpDeliveryMethod.Network;
client.EnableSsl = emailusetls;
client.Credentials = new NetworkCredential(emailuser, emailpassword);
try
{
client.Send(Message);
msg = "Email sent successfully";
return(1);
}
catch (Exception ex)
{
msg = "Problem with email account: " + ex.ToString();
return(0);
}
}
private void GetUserData(string id)
{
string sql = "SELECT * FROM app_user WHERE userid = CAST(@userid AS INT);";
var paramList = new List <KeyValuePair <string, string> >()
{
new KeyValuePair <string, string>("userid", id)
};
DataSet ds = DataServices.DataSetFromSQL(sql, paramList);
DataTable dt = ds.Tables[0];
if (dt.Rows.Count > 0)
{
string userFullName = dt.Rows[0]["firstname"].ToString() + " " + dt.Rows[0]["lastname"].ToString();
try
{
this.lblUserFullName.Text = userFullName.ToUpper();
}
catch { }
switch (Session["userType"].ToString().ToLower())
{
case "patient":
break;
case "clinician":
try
{
this.lblUniqueID.Text = dt.Rows[0]["nhsnumber"].ToString();
}
catch { }
break;
case "super user":
try
{
this.lblUniqueID.Text = dt.Rows[0]["nhsnumber"].ToString();
}
catch { }
break;
}
try
{
this.lblDoB.Text = dt.Rows[0]["dateofbirth"].ToString().Substring(0, 10);;
}
catch { }
try
{
this.lblUserType.Text = dt.Rows[0]["usertype"].ToString();
}
catch { }
try
{
this.hdnEmail.Value = dt.Rows[0]["emailaddress"].ToString();
}
catch { }
try
{
this.hdnMatchedclinicianid.Value = dt.Rows[0]["matchedclinicianid"].ToString();
}
catch { }
string currentStatus = "Currently Unauthorised";
string currentStatusClass = "alert alert-info";
bool isAuthorised = false;
try
{
isAuthorised = System.Convert.ToBoolean(dt.Rows[0]["isauthorised"].ToString());
}
catch { }
bool isRejected = false;
try
{
isRejected = System.Convert.ToBoolean(dt.Rows[0]["isrejected"].ToString());
}
catch { }
if (isAuthorised)
{
currentStatus = "Currently Authorised";
currentStatusClass = "alert alert-success";
this.btnAuthorise.Visible = false;
//this.btnReject.Visible = false;
}
if (isRejected)
{
currentStatus = "Currently Rejected";
currentStatusClass = "alert alert-danger";
this.btnReject.Visible = false;
}
this.lblCurrentStatus.Text = currentStatus;
this.pnlCurrentStatus.CssClass = currentStatusClass;
}
}
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 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 hasnot been authorised yet";
this.lblError.Visible = true;
return;
}
sql = "INSERT INTO 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 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;
}
}
private void GetTestData(string id)
{
string sql = "SELECT * FROM app_test WHERE testid = CAST(@testid AS INT);";
var paramList = new List <KeyValuePair <string, string> >()
{
new KeyValuePair <string, string>("testid", id)
};
DataSet ds = DataServices.DataSetFromSQL(sql, paramList);
DataTable dt = ds.Tables[0];
if (dt.Rows.Count > 0)
{
string patientid = "";
try
{
patientid = dt.Rows[0]["patientid"].ToString();
this.hdnPatientID.Value = patientid;
GetPatientData(patientid);
}
catch { }
string testtypeid = "";
try
{
testtypeid = dt.Rows[0]["testtypeid"].ToString();
GetTestType(testtypeid);
}
catch { }
try
{
this.lbltestnumericresult.Text = dt.Rows[0]["testnumericresult"].ToString();
}
catch { }
try
{
this.lblunitstext.Text = dt.Rows[0]["unitstext"].ToString();
}
catch { }
try
{
this.lbllowerreferencerange.Text = dt.Rows[0]["lowerreferencerange"].ToString();
}
catch { }
try
{
this.lblupperreferencerange.Text = dt.Rows[0]["upperreferencerange"].ToString();
}
catch { }
try
{
this.lblclinicianmessage.Text = dt.Rows[0]["clinicianmessage"].ToString();
}
catch { }
try
{
this.lblnexttestdate.Text = dt.Rows[0]["nexttestdate"].ToString().Substring(0, 10);;
}
catch { }
}
}