protected void GrdStaffOfferings_RowDataBound(object sender, GridViewRowEventArgs e)
{
DataTable dt = Session["staff_offerings_data"] as DataTable;
bool tblEmpty = (dt.Rows.Count == 1 && dt.Rows[0][0] == DBNull.Value);
if (!tblEmpty && e.Row.RowType == DataControlRowType.DataRow)
{
Label lblId = (Label)e.Row.FindControl("lblId");
DataRow[] foundRows = dt.Select("so_staff_offering_id=" + lblId.Text);
DataRow thisRow = foundRows[0];
Utilities.AddConfirmationBox(e);
if ((e.Row.RowState & DataControlRowState.Edit) > 0)
{
Utilities.SetEditRowBackColour(e, System.Drawing.Color.LightGoldenrodYellow);
}
}
if (e.Row.RowType == DataControlRowType.Footer)
{
DropDownList ddlStaff = (DropDownList)e.Row.FindControl("ddlNewStaff");
DataTable staff = StaffDB.GetDataTable();
DataView dataView = new DataView(staff);
dataView.Sort = "person_firstname, person_surname";
staff = dataView.ToTable();
for (int i = 0; i < staff.Rows.Count; i++)
{
Staff s = StaffDB.LoadAll(staff.Rows[i]);
ddlStaff.Items.Add(new ListItem(s.Person.FullnameWithoutMiddlename, s.StaffID.ToString()));
}
DropDownList ddlOffering = (DropDownList)e.Row.FindControl("ddlNewOffering");
string offering_invoice_type_id = null;
if (UserView.GetInstance().IsAgedCareView)
{
offering_invoice_type_id = "3,4"; // 4 = AC
}
else // if (!UserView.GetInstance().IsAgedCareView)
{
offering_invoice_type_id = "1,3"; // 4 = Clinic
}
DataTable offerings = OfferingDB.GetDataTable(false, offering_invoice_type_id);
ddlOffering.DataSource = offerings;
ddlOffering.DataTextField = "o_name";
ddlOffering.DataValueField = "o_offering_id";
ddlOffering.DataBind();
}
}
public static UserLogin LoadAll(DataRow row)
{
UserLogin userlogin = Load(row, "userlogin_");
if (row["userlogin_site_id"] != DBNull.Value)
{
userlogin.Site = SiteDB.Load(row, "site_");
}
if (row["userlogin_staff_id"] != DBNull.Value)
{
userlogin.Staff = StaffDB.LoadAll(row);
}
if (row["userlogin_patient_id"] != DBNull.Value)
{
userlogin.Patient = PatientDB.Load(row, "patient_");
userlogin.Patient.Person = PersonDB.Load(row, "pperson_");
userlogin.Patient.Person.Title = IDandDescrDB.Load(row, "ttitle_title_id", "ttitle_descr");
}
return(userlogin);
}
protected void EmailAllUsers(bool previewOnly, string DB = null)
{
bool IsDebug = Utilities.IsDev();
string curDbName = Session["DB"].ToString();
try
{
txtSubject.Text = txtSubject.Text.Trim();
if (txtSubject.Text.Length == 0)
{
lblEmailErrorMessage.Text = "<br />No Subject Entered<br /><br />";
return;
}
if (FreeTextBox1.Text.Trim().Length == 0)
{
lblEmailErrorMessage.Text = "<br />No Email Text Entered<br /><br />";
return;
}
string fromEmail = IsDebug ? "*****@*****.**" : ((SystemVariables)System.Web.HttpContext.Current.Session["SystemVariables"])["Email_FromEmail"].Value;
string fromName = ((SystemVariables)Session["SystemVariables"])["Email_FromName"].Value;
DataTable tblAllStaff = null;
ArrayList emailInfoList = new ArrayList(); // list of Tuple<site, person, email>
System.Data.DataTable tbl = DBBase.ExecuteQuery("EXEC sp_databases;", "master").Tables[0];
for (int i = 0; i < tbl.Rows.Count; i++)
{
string databaseName = tbl.Rows[i][0].ToString();
if (!Regex.IsMatch(databaseName, @"Mediclinic_\d{4}"))
{
continue;
}
if (chkIgnore0001.Checked && databaseName == @"Mediclinic_0001")
{
continue;
}
if (DB != null && databaseName != DB)
{
continue;
}
Session["DB"] = databaseName;
Session["SystemVariables"] = SystemVariableDB.GetAll();
string clientSiteName = ((SystemVariables)Session["SystemVariables"])["Site"].Value;
DataTable tblStaff = StaffDB.GetDataTable();
// get entity ID's so we can get all emails into a hashtable in one db query
int[] entityIDs = new int[tblStaff.Rows.Count];
for (int j = 0; j < tblStaff.Rows.Count; j++)
{
entityIDs[j] = Convert.ToInt32(tblStaff.Rows[j]["person_entity_id"]);
}
Hashtable emailHash = PatientsContactCacheDB.GetBullkEmail(entityIDs, -1);
// add the emails to the datatable - as comma-seperated string
tblStaff.Columns.Add("database_name", typeof(String));
tblStaff.Columns.Add("emails", typeof(String));
tblStaff.Columns.Add("site", typeof(String));
for (int j = 0; j < tblStaff.Rows.Count; j++)
{
Staff s = StaffDB.LoadAll(tblStaff.Rows[j]);
if (chkMasterAdminOnly.Checked && !s.IsMasterAdmin)
{
continue;
}
if (emailHash[s.Person.EntityID] != null)
{
if (Utilities.GetAddressType().ToString() == "Contact")
{
if (emailHash[s.Person.EntityID] != null)
{
foreach (Contact c in (Contact[])emailHash[s.Person.EntityID])
{
if (Utilities.IsValidEmailAddress(c.AddrLine1.Trim()))
{
emailInfoList.Add(new Tuple <string, string, string>(clientSiteName, s.Person.FullnameWithoutMiddlename, c.AddrLine1.Trim()));
}
}
}
}
else if (Utilities.GetAddressType().ToString() == "ContactAus")
{
if (emailHash[s.Person.EntityID] != null)
{
foreach (ContactAus c in (ContactAus[])emailHash[s.Person.EntityID])
{
if (Utilities.IsValidEmailAddress(c.AddrLine1.Trim()))
{
emailInfoList.Add(new Tuple <string, string, string>(clientSiteName, s.Person.FullnameWithoutMiddlename, c.AddrLine1.Trim()));
}
}
}
}
else
{
throw new Exception("Unknown AddressType in config: " + Utilities.GetAddressType().ToString().ToString());
}
}
}
// if first table, set alldb's to this, else add this to alldb's list
if (tblAllStaff == null)
{
tblAllStaff = tblStaff;
}
else
{
tblAllStaff.Merge(tblStaff);
}
Session.Remove("DB");
Session.Remove("SystemVariables");
}
// send the email
string output = "<h4>" + (previewOnly ? "Message Preview" : "Message Sent") + "</h4>" + Environment.NewLine +
"<table style=\"min-width:400px;border:1px solid black;text-align:center;background-color:#FFFFFF;border-spacing:2px;border-collapse:separate;\"><tr><td><b>" + txtSubject.Text + "</b></td></tr></table><div style=\"height:10px;\"></div>" + Environment.NewLine +
"<table style=\"min-width:500px;border:1px solid black;text-align:left;background-color:#FFFFFF;border-spacing:10px;border-collapse:separate;\"><tr style=\"min-height:200px;\"><td>" + (FreeTextBox1.Text.Length == 0 ? " " : FreeTextBox1.Text) + "</td></tr></table><br />";
output += "<h4>" + (previewOnly ? "Will Be Sent To" : "Sent To") + "</h4><table border=\"1\" class=\"table table-bordered table-striped table-grid table-grid-top-bottum-padding-normal auto_width block_center\" >";
string emails = string.Empty;
foreach (Tuple <string, string, string> emailInfo in emailInfoList)
{
output += "<tr><td style=\"padding-left:4px;padding-right:4px;text-align:left !important;\">" + emailInfo.Item1 + "</td><td style=\"padding-left:4px;padding-right:4px;text-align:left !important;\">" + emailInfo.Item2 + "</td><td style=\"padding-left:4px;padding-right:4px;text-align:left !important;\">" + emailInfo.Item3 + "</td></tr>";
emails = (emails.Length == 0 ? "" : ",") + emailInfo.Item3;
}
if (emailInfoList.Count == 0)
{
output += "<tr><td style=\"padding-left:4px;padding-right:4px;text-align:left !important;\"> No Staff Have Emails In The Selected Site(s) </td></tr>";
}
output += "</table>";
if (!previewOnly && emails.Length > 0)
{
// email: put to addresss as from address
// email: put all emails in BCC
EmailerNew.SimpleEmail(
fromEmail,
fromName,
fromEmail,
txtSubject.Text.Trim(),
FreeTextBox1.Text,
true,
null,
false,
null,
IsDebug ? "*****@*****.**" : emails
);
}
lblEmailOutput.Text = output;
}
finally
{
Session["DB"] = curDbName;
Session["SystemVariables"] = SystemVariableDB.GetAll();
Page.ClientScript.RegisterStartupScript(this.GetType(), "download", @"<script language=javascript>addLoadEvent(function () { window.location.hash = ""emailing_tag""; });</script>");
}
}
protected void ExportAllUsers(string DB = null)
{
string curDbName = Session["DB"].ToString();
try
{
DataTable tblAllStaff = null;
System.Data.DataTable tbl = DBBase.ExecuteQuery("EXEC sp_databases;", "master").Tables[0];
for (int i = 0; i < tbl.Rows.Count; i++)
{
string databaseName = tbl.Rows[i][0].ToString();
if (!Regex.IsMatch(databaseName, @"Mediclinic_\d{4}"))
{
continue;
}
if (DB != null && databaseName != DB)
{
continue;
}
Session["DB"] = databaseName;
Session["SystemVariables"] = SystemVariableDB.GetAll();
DataTable tblStaff = StaffDB.GetDataTable();
// get entity ID's so we can get all emails into a hashtable in one db query
int[] entityIDs = new int[tblStaff.Rows.Count];
for (int j = 0; j < tblStaff.Rows.Count; j++)
{
entityIDs[j] = Convert.ToInt32(tblStaff.Rows[j]["person_entity_id"]);
}
Hashtable emailHash = PatientsContactCacheDB.GetBullkEmail(entityIDs, -1);
// add the emails to the datatable - as comma-seperated string
tblStaff.Columns.Add("database_name", typeof(String));
tblStaff.Columns.Add("emails", typeof(String));
tblStaff.Columns.Add("site", typeof(String));
for (int j = 0; j < tblStaff.Rows.Count; j++)
{
Staff s = StaffDB.LoadAll(tblStaff.Rows[j]);
string emails = string.Empty;
if (emailHash[s.Person.EntityID] != null)
{
if (Utilities.GetAddressType().ToString() == "Contact")
{
if (emailHash[s.Person.EntityID] != null)
{
foreach (Contact c in (Contact[])emailHash[s.Person.EntityID])
{
if (c.AddrLine1.Trim().Length > 0 && Utilities.IsValidEmailAddress(c.AddrLine1.Trim()))
{
emails += (emails.Length == 0 ? "" : ",") + c.AddrLine1.Trim();
}
}
}
}
else if (Utilities.GetAddressType().ToString() == "ContactAus")
{
if (emailHash[s.Person.EntityID] != null)
{
foreach (ContactAus c in (ContactAus[])emailHash[s.Person.EntityID])
{
if (c.AddrLine1.Trim().Length > 0 && Utilities.IsValidEmailAddress(c.AddrLine1.Trim()))
{
emails += (emails.Length == 0 ? "" : ",") + c.AddrLine1.Trim();
}
}
}
}
else
{
throw new Exception("Unknown AddressType in config: " + Utilities.GetAddressType().ToString().ToString());
}
}
tblStaff.Rows[j]["database_name"] = databaseName;
tblStaff.Rows[j]["emails"] = emails;
tblStaff.Rows[j]["site"] = ((SystemVariables)Session["SystemVariables"])["Site"].Value;
}
// if first table, set alldb's to this, else add this to alldb's list
if (tblAllStaff == null)
{
tblAllStaff = tblStaff;
}
else
{
tblAllStaff.Merge(tblStaff);
}
Session.Remove("DB");
Session.Remove("SystemVariables");
}
// create output
System.Text.StringBuilder htmlOoutput = new System.Text.StringBuilder();
System.Text.StringBuilder exportOoutput = new System.Text.StringBuilder();
htmlOoutput.Append("<table border=\"1\">");
htmlOoutput.Append("<tr>");
htmlOoutput.Append("<th>Database</td>");
htmlOoutput.Append("<th>Site Name</td>");
htmlOoutput.Append("<th>Firstname</td>");
htmlOoutput.Append("<th>Surname</td>");
htmlOoutput.Append("<th>Fullname</td>");
htmlOoutput.Append("<th>Stakeholder</td>");
htmlOoutput.Append("<th>Master Admin</td>");
htmlOoutput.Append("<th>Admin</td>");
htmlOoutput.Append("<th>Principal</td>");
htmlOoutput.Append("<th>Provider</td>");
htmlOoutput.Append("<th>Email(s)</td>");
htmlOoutput.Append("</tr>");
exportOoutput.Append("Database").Append(",");
exportOoutput.Append("Site Name").Append(",");
exportOoutput.Append("Firstname").Append(",");
exportOoutput.Append("Surname").Append(",");
exportOoutput.Append("Fullname").Append(",");
exportOoutput.Append("Stakeholder").Append(",");
exportOoutput.Append("Master Admin").Append(",");
exportOoutput.Append("Admin").Append(",");
exportOoutput.Append("Principal").Append(",");
exportOoutput.Append("Provider").Append(",");
exportOoutput.Append("Email(s)").Append(",");
exportOoutput.AppendLine();
if (tblAllStaff != null)
{
for (int i = 0; i < tblAllStaff.Rows.Count; i++)
{
Staff s = StaffDB.LoadAll(tblAllStaff.Rows[i]);
htmlOoutput.Append("<tr>");
htmlOoutput.Append("<td>" + tblAllStaff.Rows[i]["database_name"] + "</td>");
htmlOoutput.Append("<td>" + tblAllStaff.Rows[i]["site"] + "</td>");
htmlOoutput.Append("<td>" + s.Person.Firstname + "</td>");
htmlOoutput.Append("<td>" + s.Person.Surname + "</td>");
htmlOoutput.Append("<td>" + s.Person.Fullname + "</td>");
htmlOoutput.Append("<td>" + (s.IsStakeholder ? "Yes" : "No") + "</td>");
htmlOoutput.Append("<td>" + (s.IsMasterAdmin ? "Yes" : "No") + "</td>");
htmlOoutput.Append("<td>" + (s.IsAdmin ? "Yes" : "No") + "</td>");
htmlOoutput.Append("<td>" + (s.IsPrincipal ? "Yes" : "No") + "</td>");
htmlOoutput.Append("<td>" + (s.IsProvider ? "Yes" : "No") + "</td>");
htmlOoutput.Append("<td>" + tblAllStaff.Rows[i]["emails"] + "</td>");
htmlOoutput.Append("</tr>");
exportOoutput.Append(tblAllStaff.Rows[i]["database_name"]).Append(",");
exportOoutput.Append(tblAllStaff.Rows[i]["site"]).Append(",");
exportOoutput.Append(s.Person.Firstname).Append(",");
exportOoutput.Append(s.Person.Surname).Append(",");
exportOoutput.Append(s.Person.Fullname).Append(",");
exportOoutput.Append(s.IsStakeholder ? "Yes" : "No").Append(",");
exportOoutput.Append(s.IsMasterAdmin ? "Yes" : "No").Append(",");
exportOoutput.Append(s.IsAdmin ? "Yes" : "No").Append(",");
exportOoutput.Append(s.IsPrincipal ? "Yes" : "No").Append(",");
exportOoutput.Append(s.IsProvider ? "Yes" : "No").Append(",");
exportOoutput.Append(tblAllStaff.Rows[i]["emails"]).Append(",");
exportOoutput.AppendLine();
}
}
htmlOoutput.Append("</table>");
// send the output
//lblResultMessage2.Text = htmlOoutput.ToString();
ExportCSV(Response, exportOoutput.ToString(), "All Users All Sites.csv");
}
finally
{
Session["DB"] = curDbName;
Session["SystemVariables"] = SystemVariableDB.GetAll();
}
}