public static string ProcessWorkflow(Int32 workflow_id, Int32 userID)
{
qDbs_SQLcode sql = new qDbs_SQLcode();
qPtl_Workflow c_workflow = new qPtl_Workflow(workflow_id);
string returnMessage = string.Empty;
if (c_workflow.WorkflowActionType == "sql")
{
// get sql and then replace the parameters
string finalSql = c_workflow.SQL;
// replace values
finalSql = finalSql.Replace("{UserID}", Convert.ToString(userID));
if (c_workflow.ReferenceID > 0)
{
finalSql = finalSql.Replace("{ReferenceID}", Convert.ToString(c_workflow.ReferenceID));
}
if (c_workflow.WorkflowActionReferenceID > 0)
{
finalSql = finalSql.Replace("{WorkflowActionReferenceID}", Convert.ToString(c_workflow.WorkflowActionReferenceID));
}
returnMessage = sql.ExecuteSQL(finalSql);
}
return(returnMessage);
}
public static DataSet GetNearestHealthProvidersByPostalCode(string postal_code)
{
string sqlGIS = String.Empty;
string distance = "3500";
string sqlWHERE = String.Empty;
string criteria = String.Empty;
sqlGIS = "DECLARE @Zip nvarchar(5); ";
sqlGIS += "DECLARE @Distance int; ";
sqlGIS += "SET @Zip = '" + postal_code + "'; ";
sqlGIS += "SET @Distance = " + distance + "; ";
sqlGIS += "DECLARE @GeomZip geography ";
sqlGIS += "SET @GeomZip = (SELECT TOP(1) g.geom FROM qGis_ReferenceObjects g WHERE g.Name = @Zip ORDER BY AREA DESC) ";
sqlGIS += "SELECT h.*, (SELECT h.Geography.STDistance(@GeomZip)/1609.344) AS Distance ";
sqlGIS += "FROM qHtl_HealthProviders_Spatial_View h ";
sqlGIS += "WHERE (h.Geography.STIntersects(@GeomZip.STBuffer(@Distance * 1609.344))=1) ";
sqlGIS += "ORDER BY Distance ASC ";
criteria += " " + distance + " miles of " + postal_code;
qDbs_SQLcode sql = new qDbs_SQLcode();
DataSet dsHealthProviders = new DataSet();
dsHealthProviders = sql.GetDataSet(sqlGIS);
return(dsHealthProviders);
}
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
int emailID = Convert.ToInt32(Request.QueryString["emailID"]);
// create object
qCom_EmailTool email = new qCom_EmailTool(emailID);
qPtl_User user = new qPtl_User(Convert.ToInt32(Context.Items["UserID"]));
txtEmail.Text = user.Email;
lblSubject.Text = email.emailSubject;
// get headers and footers
string query = "SELECT TOP(1) * FROM qCom_EmailPreferences WHERE Available = 'Yes'";
qDbs_SQLcode sql = new qDbs_SQLcode();
SqlDataReader eReader = sql.GetDataReader(query);
eReader.Read();
string header = Convert.ToString(eReader["Header"]);
string footer = Convert.ToString(eReader["Footer"]);
string unsubscribe = Convert.ToString(eReader["Unsubscribe"]);
eReader.Close();
// if email type is bulk, then add footer
string completeMessage = header + email.emailDraft + footer;
if (email.emailType == "bulk")
{
completeMessage += unsubscribe;
}
lblBody.Text = completeMessage;
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
string sqlCode = string.Empty;
qDbs_SQLcode sql = new qDbs_SQLcode();
// get emails
sqlCode = "SELECT EmailID, URI FROM qCom_EmailItem WHERE Type <> 'Individual Email' AND ScopeID = " + Context.Items["ScopeID"] + " ORDER BY Subject ASC";
DataTable dtEmails;
dtEmails = sql.GetDataTable(sqlCode);
ddlEmails.DataSource = dtEmails;
ddlEmails.DataValueField = "EmailID";
ddlEmails.DataTextField = "URI";
ddlEmails.DataBind();
// get searches
sqlCode = "SELECT SearchID, SavedName FROM qDbs_Searches WHERE Saved = 'Yes' AND YesEmail = 'Yes' ORDER BY SavedName ASC";
DataTable dtSearches;
dtSearches = sql.GetDataTable(sqlCode);
ddlSearches.DataSource = dtSearches;
ddlSearches.DataValueField = "SearchID";
ddlSearches.DataTextField = "SavedName";
ddlSearches.DataBind();
// get prior data if edit mode
if (Request.QueryString["sendEventID"] != null && Request.QueryString["sendEventID"] != "")
{
sqlCode = "SELECT * FROM qCom_SendEvents WHERE SendEventID = " + Request.QueryString["sendEventID"];
using (SqlDataReader eReader = sql.GetDataReader(sqlCode))
{
eReader.Read();
ddlEmails.SelectedValue = Convert.ToString(eReader["EmailID"]);
ddlSearches.SelectedValue = Convert.ToString(eReader["SearchID"]);
DateTime startDate = new DateTime();
startDate = Convert.ToDateTime(eReader["StartDate"]);
datStart.SelectedDate = startDate;
rblRecurring.SelectedValue = Convert.ToString(eReader["Recurring"]);
rblRunning.SelectedValue = Convert.ToString(eReader["Running"]);
rblHeader.SelectedValue = Convert.ToString(eReader["IncludeHeader"]);
rblFooter.SelectedValue = Convert.ToString(eReader["IncludeFooter"]);
rblUnsubscribe.SelectedValue = Convert.ToString(eReader["IncludeUnsubscribe"]);
eReader.Close();
}
}
}
}
protected void btnSave_Click(object sender, EventArgs e)
{
Response.Write("just clicked");
string sqlQuery = string.Empty;
if (Request.QueryString["sendEventID"] != null && Request.QueryString["sendEventID"] != "")
{
sqlQuery = "UPDATE qCom_SendEvents SET [SearchID] = " + ddlSearches.SelectedValue + ", [EmailID] = " + ddlEmails.SelectedValue + ", [StartDate] = '" + datStart.SelectedDate + "',[Recurring] = '" + rblRecurring.SelectedItem + "',[Running] = '" + rblRunning.SelectedItem + "',[IncludeHeader] = '" + rblHeader.SelectedItem + "',[IncludeFooter] = '" + rblFooter.SelectedItem + "',[IncludeUnsubscribe] = '" + rblUnsubscribe.SelectedItem + "' WHERE SendEventID = " + Request.QueryString["sendEventID"];
}
else
{
sqlQuery = "INSERT qCom_SendEvents ([ScopeID],[SearchID],[EmailID],[StartDate],[Recurring],[Running],[IncludeHeader],[IncludeFooter],[IncludeUnsubscribe]) VALUES (" + Context.Items["ScopeID"] + "," + ddlSearches.SelectedValue + " , " + ddlEmails.SelectedValue + " ,'" + datStart.SelectedDate + "','" + rblRecurring.SelectedItem + "','" + rblRunning.SelectedItem + "','" + rblHeader.SelectedItem + "','" + rblFooter.SelectedItem + "','" + rblUnsubscribe.SelectedItem + "')";
}
//Response.Write("sql code = " + sqlQuery);
qDbs_SQLcode sql = new qDbs_SQLcode();
sql.ExecuteSQL(sqlQuery);
Response.Redirect("send-event-list.aspx");
}
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
// get all send events
string sqlCode = string.Empty;
sqlCode = "SELECT * FROM qCom_SendEvents_View WHERE (Available = 'Yes' AND Running = 'Yes' AND Recurring = 'Yes' AND GetDate() > StartDate) OR (Available = 'Yes' AND Running = 'Yes' AND Recurring = 'No' AND (GetDate() Between StartDate AND StartDate+1)) ORDER BY Priority ASC";
qDbs_SQLcode sql = new qDbs_SQLcode();
SqlDataReader eReader = sql.GetDataReader(sqlCode);
if (eReader.HasRows)
{
while (eReader.Read())
{
// see if there are custom email preferences if this send event is associated with a campaign
int campaign_id = 0;
string custom_email = string.Empty;
string custom_email_from = string.Empty;
bool get_custom_did = false;
if (!String.IsNullOrEmpty(Convert.ToString(eReader["CampaignID"])))
{
campaign_id = Convert.ToInt32(eReader["CampaignID"]);
}
if (campaign_id > 0)
{
var pref = qCom_CampaignPreference.GetCampaignPreferences(campaign_id);
if (pref != null)
{
if (pref.CampaignPreferenceID > 0)
{
if (!String.IsNullOrEmpty(pref.CampaignEmail))
{
custom_email = pref.CampaignEmail;
}
if (!String.IsNullOrEmpty(pref.CampaignEmailFrom))
{
custom_email_from = pref.CampaignEmailFrom;
}
// see if we need to get / integrated a custom DID
if (pref.IncludeCustomDID == true)
{
get_custom_did = true;
}
}
}
}
// get email preferences
string sqlCode2 = "SELECT TOP(1) * FROM qCom_EmailPreferences WHERE Available = 'Yes'";
SqlDataReader pReader = sql.GetDataReader(sqlCode2);
pReader.Read();
string header = Convert.ToString(pReader["Header"]);
string footer = Convert.ToString(pReader["Footer"]);
string unsubscribe = Convert.ToString(pReader["Unsubscribe"]);
string fromEmailAddress = string.Empty;
string fromName = string.Empty;
if (!String.IsNullOrEmpty(custom_email))
{
fromEmailAddress = custom_email;
}
else
{
fromEmailAddress = Convert.ToString(pReader["FromEmailAddress"]);
}
if (!String.IsNullOrEmpty(custom_email_from))
{
fromName = custom_email_from;
}
else
{
fromName = Convert.ToString(pReader["fromName"]);
}
pReader.Close();
//try
//{
lblMessage.Text += "<br>Sending...";
// create object
int emailID = Convert.ToInt32(eReader["emailID"]);
string contactQuery = "SELECT " + Convert.ToString(eReader["sqlSELECT"]) + " FROM " + Convert.ToString(eReader["sqlFROM"]) + " WHERE " + Convert.ToString(eReader["sqlWHERE"]);
bool is_contact_list = false;
if (Convert.ToString(eReader["sqlFROM"]).Contains("qCom_Contacts"))
{
is_contact_list = true;
}
qCom_EmailTool email = new qCom_EmailTool(emailID);
// get addresses
string[][] contacts = email.GetSendEventContacts(contactQuery, get_custom_did, campaign_id, Convert.ToInt32(Context.Items["ScopeID"]), is_contact_list);
string messageBody = string.Empty;
string includeHeader = Convert.ToString(eReader["IncludeHeader"]);
string includeFooter = Convert.ToString(eReader["IncludeFooter"]);
string includeUnsubscribe = Convert.ToString(eReader["IncludeUnsubscribe"]);
if (includeHeader == "Yes")
{
messageBody += header;
}
messageBody += email.emailDraft;
if (includeFooter == "Yes")
{
messageBody += footer;
}
if (includeUnsubscribe == "Yes")
{
messageBody += unsubscribe;
}
//(string[][] addresses, string strFrom, string strFromEmail, string requestURL, string strSubject, string rawEmailContent, string userID, string value1, string value2, bool sendCopyToAdmins, bool noDuplicatesToday, bool noDontSend, int emailID)
ArrayList[] output = email.SendMail(contacts, fromName, fromEmailAddress, "bulkemail", email.emailSubject, messageBody, "", "", "", "", "", false, true, true, emailID, get_custom_did, campaign_id);
lblMessage.Text += "finished sending email: " + email.emailSubject + "; send event id = " + Convert.ToString(eReader["SendEventID"]) + "; number of recipients=" + contacts.Length;
//}
//catch
//{
// lblMessage.Text += "A problem has occurred<br>";
//}
}
eReader.Close();
}
else
{
lblMessage.Text += "There are no scheduled send events.<br>";
}
}
else
{
lblMessage.Text += "Problem with key or page incorrectly loaded.<br>";
}
}
public static qPtl_User RegisterNewUser(RegistrationData data)
{
int existing_user_id = 0;
int new_space_id = 0;
string sqlCode = string.Empty;
// Redundancy check -- write Highest Level into qPtl_User table in case DB trigger not working
qPtl_Role role = new qPtl_Role(data.default_role_id);
// add user
qPtl_User new_user = new qPtl_User();
new_user.Available = "Yes";
new_user.OrgUnitID = data.scope_id;
new_user.ScopeID = data.scope_id;
new_user.Created = DateTime.Now;
new_user.CreatedBy = 0;
new_user.LastModified = DateTime.Now;
new_user.LastModifiedBy = 0;
new_user.MarkAsDelete = 0;
new_user.Status = ""; // used to include a default message for their status, now leave blank
new_user.FirstName = data.firstname;
new_user.LastName = data.lastname;
new_user.Email = data.email;
new_user.UserName = data.username;
string password_for_storing = FormsAuthentication.HashPasswordForStoringInConfigFile(data.password, "sha1");
new_user.Password = password_for_storing;
new_user.AccountStatus = "Active";
new_user.HighestRank = role.RoleRank;
new_user.HighestRole = role.RoleName;
new_user.Insert();
existing_user_id = new_user.UserID;
DateTime DOB;
try
{
DOB = Convert.ToDateTime(data.dob);
}
catch
{
// no valid date so use default value
DOB = Convert.ToDateTime("1/1/1900");
}
// add user profile
qPtl_UserProfile new_profile = new qPtl_UserProfile();
new_profile.UserID = existing_user_id;
new_profile.ScopeID = data.scope_id;
new_profile.Available = "Yes";
new_profile.Created = DateTime.Now;
new_profile.CreatedBy = existing_user_id;
new_profile.LastModified = DateTime.Now;
new_profile.LastModifiedBy = existing_user_id;
new_profile.MarkAsDelete = 0;
new_profile.Style = "default";
new_profile.Visibility = "all";
new_profile.Division = data.division;
new_profile.Agency = data.agency;
new_profile.Position = data.position;
new_profile.Degrees = data.degrees;
new_profile.Address1 = data.address;
new_profile.Address2 = data.address2;
new_profile.City = data.city;
new_profile.StateProvince = data.state;
new_profile.PostalCode = data.postal_code;
new_profile.Country = data.country;
new_profile.Gender = data.gender;
new_profile.DOB = DOB;
new_profile.Race = data.race;
new_profile.EmploymentLocation = data.employment_location;
new_profile.EmploymentSetting = data.employment_setting;
new_profile.WorkSites = data.employment_sites;
new_profile.Profession = data.profession;
new_profile.Phone1 = data.work_phone;
new_profile.Phone1Type = "work";
new_profile.Insert();
qPtl_User user = new qPtl_User(existing_user_id);
// add user communication preference
if (!String.IsNullOrEmpty(user.Email))
{
qCom_UserPreference connect = new qCom_UserPreference();
connect.UserID = user.UserID;
connect.Created = DateTime.Now;
connect.CreatedBy = user.UserID;
connect.LastModified = DateTime.Now;
connect.LastModifiedBy = user.UserID;
connect.Available = "Yes";
connect.ScopeID = 1;
connect.MarkAsDelete = 0;
connect.OkBulkEmail = "Yes";
connect.OkEmail = "Yes";
connect.OkSms = "Yes";
connect.LanguageID = 1;
connect.Insert();
}
// ****************************************************
// STEP 5: Add User Role & Supporting Role Structures
// Add role
/*
* qPtl_UserRole role = new qPtl_UserRole();
* role.UserID = user.UserID;
* role.RoleID = role_id;
* role.Insert();
*/
qDbs_SQLcode sql = new qDbs_SQLcode();
sqlCode = "INSERT INTO qPtl_UserRoles ([UserID],[RoleID]) VALUES(" + user.UserID + "," + data.default_role_id + ")";
sql.ExecuteSQL(sqlCode);
// Add possible role actions for the new user role
AddRoleAction(data.default_role_id, data.scope_id, user);
// add folder for user_data
string rootLocation = HttpContext.Current.Server.MapPath("~/") + "user_data\\";
if (!Directory.Exists(rootLocation + user.UserName))
{
Directory.CreateDirectory(rootLocation + user.UserName);
}
if (new_user.UserID > 0)
{
return(new_user);
}
else
{
return(null);
}
}
