using framework_business;
using framework_library;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Telerik.Web.UI;
namespace scheduler
{
class Campaigns
{
string _dbName = "", _webPath = "";
oSetup _setup;
string connectionString;
static string staticConnectionString;
public Campaigns(string dbName, oSetup setup, string webPath)
{
_dbName = dbName;
_setup = setup;
_webPath = webPath;
string conn = handler.ReturnConfigAppSetting("conn");
connectionString = conn.Replace("{catalog}", dbName);
staticConnectionString = connectionString;
}
public void Process()
{
DataTable campaignTable = GetCampaigns(_dbName);
//enumerate each campaign
foreach (oCampaign campaign in utils.ConvertDataTableToList(campaignTable, typeof(oCampaign)))
{
int creditsUsed = 0;
bool useCampaign = false;
//verify next send
if (campaign.nextSend.Date < DateTime.Now.AddDays(1).Date)
{
if (campaign.isRecurring && campaign.lastSent.Date < DateTime.Now.Date)
{
//Check if time has lapsed yet
if (DateTime.Now.TimeOfDay >= campaign.nextSend.TimeOfDay)
{
useCampaign = true;
}
}
else if (campaign.lastSent.Year < 2016)//not recurrign but never sent before so lets send it
{
//Check if time has lapsed yet
if (DateTime.Now.TimeOfDay >= campaign.nextSend.TimeOfDay)
{
useCampaign = true;
}
}
}
if (useCampaign)
{
//LogStatus("processing campaign " + campaign.campaignTitle + ".. ");
switch (campaign.campaignType)
{
#region subscriber communications
case 1://subscriber communication
switch (campaign.messageType)
{
case 1://email
foreach (oSubscriber sub in utils.ConvertDataTableToList(GetSubcribers(_dbName), typeof(oSubscriber)))
{
//LogStatus("Sending email to " + sub.name + "...");
oEmail email = new oEmail();
email.fromAddress = _setup.communicationEmail;
email.toAddress = sub.email;
email.Body = campaign.campaignBody;
email.Subject = campaign.campaignTitle;
email.Body = email.Body.Replace("{name}", sub.name);
email.Body = email.Body.Replace("src=\"/images", "src=\"" + _webPath + "/images");
email.Body = email.Body.Replace("{WebAddress}", _webPath);
oCampaignMessage msg = new oCampaignMessage();
msg.campaignId = campaign.recId;
msg.message = email.Body;
msg.messageType = 1;
msg.mobile = "";
//CVH 2018-01-11 Save email address
msg.email = email.toAddress;
msg.dateSent = DateTime.Now;
if (communication.SendAnEmail(email))
{
msg.status = 1;
msg.statusCode = 0;
msg.statusDisplay = "email sent OK";
}
else
{
msg.status = -1;
msg.statusCode = -1;
msg.statusDisplay = "failed to send the email";
}
//LogStatus(msg.statusDisplay);
SaveCampaignMessage(_dbName, msg);
}
break;
case 2://sms
foreach (oSubscriberSMS sub in utils.ConvertDataTableToList(GetSubcribersSMS(_dbName), typeof(oSubscriberSMS)))
{
//LogStatus("Sending sms to " + sub.name + "...");
string SMSMessage = campaign.campaignBody;
SMSMessage = SMSMessage.Replace("{name}", sub.name);
oCampaignMessage msg = new oCampaignMessage();
msg.campaignId = campaign.recId;
msg.message = SMSMessage;
msg.messageType = 2;
msg.mobile = sub.mobile;
msg.dateSent = DateTime.Now;
msg.status = 1;
//CVH 2018-04-09 If SMS body is more than 1 sms (more than 160 char), increase credits used proportionately
int smsCount = 0;
decimal calc = (SMSMessage.Length - 160m) / (160m - 7m) + 1m;
int.TryParse((Math.Ceiling(calc)).ToString(), out smsCount);
if (smsCount <= 0)
smsCount = 1;
long smsResult = xSendEasy.SendSMS(handler.ReturnConfigAppSetting("smsU"), handler.ReturnConfigAppSetting("smsP"), sub.mobile, SMSMessage);
if (smsResult >= 0)
{
msg.statusCode = 0;
msg.statusDisplay = "sms sent OK";
//creditsUsed++;
creditsUsed += smsCount;
}
else
{
msg.statusCode = Convert.ToInt32(smsResult);
msg.statusDisplay = xSendEasy.ReturnErrorDetail(msg.statusCode);
}
//LogStatus(msg.statusDisplay);
SaveCampaignMessage(_dbName, msg);
}
break;
}
break;
#endregion
#region appointment reminders
case 2://appointemnt reminder
//CVH 2018-07-04 Need to cater for recurring appointments
ArrayList events = utils.ConvertDataTableToList(GetCalendarEvents(_dbName), typeof(oCalendarEvent));
foreach (oCalendarEvent calRecurr in utils.ConvertDataTableToList(GetCalendarEventsRecurring(_dbName), typeof(oCalendarEvent)))
{
//if this is the master event of a recurring event,
if (calRecurr.recurrenceRule != null && calRecurr.recurrenceRule != "")
{
RecurrenceRule rule;
RecurrenceRule.TryParse(calRecurr.recurrenceRule, out rule);
foreach (DateTime dtOcc in rule.Occurrences)
{
if (dtOcc.Date == System.DateTime.Now.AddDays(1).Date)
{
oCalendarEvent eventOcc = new oCalendarEvent();
eventOcc.calendarEventTypeId = calRecurr.calendarEventTypeId;
eventOcc.calendarId = calRecurr.calendarId;
eventOcc.calendarRoomId = calRecurr.calendarRoomId;
eventOcc.calendarRoomOther = calRecurr.calendarRoomOther;
eventOcc.customerId = calRecurr.customerId;
eventOcc.dateSaved = calRecurr.dateSaved;
eventOcc.dateUpdated = calRecurr.dateUpdated;
eventOcc.emailReminderMin = calRecurr.emailReminderMin;
eventOcc.filterField = calRecurr.filterField;
eventOcc.filterValue = calRecurr.filterValue;
eventOcc.isBilled = calRecurr.isBilled;
eventOcc.isEmailReminder = calRecurr.isEmailReminder;
eventOcc.isEmailReminderSent = calRecurr.isEmailReminderSent;
eventOcc.isNewContact = calRecurr.isNewContact;
eventOcc.newContactEmail = calRecurr.newContactEmail;
eventOcc.newContactName = calRecurr.newContactName;
eventOcc.newContactSurname = calRecurr.newContactSurname;
eventOcc.newContactTel = calRecurr.newContactTel;
eventOcc.start = dtOcc;
eventOcc.statusId = calRecurr.statusId;
eventOcc.subject = calRecurr.subject;
eventOcc.userIdSaved = calRecurr.userIdSaved;
eventOcc.userIdUpdated = calRecurr.userIdUpdated;
eventOcc.users = calRecurr.users;
events.Add(eventOcc);
}
}
}
}
//foreach (oCalendarEvent calEvnt in utils.ConvertDataTableToList(GetCalendarEvents(_dbName), typeof(oCalendarEvent)))
foreach (oCalendarEvent calEvnt in events)
{
//get attendees for this event
foreach (oUser usr in utils.ConvertDataTableToList(GetUsersForCalendar(calEvnt.calendarId, calEvnt, _dbName, _setup), typeof(oUser)))
{
oCampaignMessage msg = new oCampaignMessage();
//LogStatus("Sending event reminder to " + usr.name + "...");
switch (campaign.messageType)
{
case 1://email
oEmail email = new oEmail();
email.fromAddress = _setup.communicationEmail;
email.toAddress = usr.email;
email.Body = campaign.campaignBody;
email.Subject = campaign.campaignTitle;
email.Body = email.Body.Replace("{name}", usr.name);
email.Body = email.Body.Replace("src=\"/images", "src=\"" + _webPath + "/images");
email.Body = email.Body.Replace("{WebAddress}", _webPath);
msg.campaignId = campaign.recId;
msg.message = email.Body;
msg.messageType = 1;
msg.mobile = "";
//CVH 2018-01-11 Save email address
msg.email = email.toAddress;
msg.dateSent = DateTime.Now;
if (communication.SendAnEmail(email))
{
msg.status = 1;
msg.statusCode = 0;
msg.statusDisplay = "email sent OK";
}
else
{
msg.status = -1;
msg.statusCode = -1;
msg.statusDisplay = "failed to send the email";
}
//LogStatus(msg.statusDisplay);
SaveCampaignMessage(_dbName, msg);
break;
case 2://sms
string SMSMessage = campaign.campaignBody;
SMSMessage = SMSMessage.Replace("{name}", usr.name);
SMSMessage = SMSMessage.Replace("{startDate}", calEvnt.start.ToString("dd/MM/yyyy"));
SMSMessage = SMSMessage.Replace("{startTime}", calEvnt.start.ToString("HH:mmtt"));
SMSMessage = SMSMessage.Replace("{customer}", _setup.customer);
msg.campaignId = campaign.recId;
msg.message = SMSMessage;
msg.messageType = 2;
msg.mobile = usr.tel;
msg.dateSent = DateTime.Now;
msg.status = 1;
//CVH 2018-04-09 If SMS body is more than 1 sms (more than 160 char), increase credits used proportionately
int smsCount = 0;
decimal calc = (SMSMessage.Length - 160m) / (160m - 7m) + 1m;
int.TryParse((Math.Ceiling(calc)).ToString(), out smsCount);
if (smsCount <= 0)
smsCount = 1;
if (!CheckDuplicateMessage(_dbName, msg))
{
long smsResult = xSendEasy.SendSMS(handler.ReturnConfigAppSetting("smsU"), handler.ReturnConfigAppSetting("smsP"), usr.tel, SMSMessage);
if (smsResult >= 0)
{
msg.statusCode = 0;
msg.statusDisplay = "sms sent OK";
//creditsUsed++;
creditsUsed += smsCount;
}
else
{
msg.statusCode = Convert.ToInt32(smsResult);
msg.statusDisplay = xSendEasy.ReturnErrorDetail(msg.statusCode);
}
//LogStatus(msg.statusDisplay);
SaveCampaignMessage(_dbName, msg);
}
break;
}
}
}
break;
#endregion
#region general
case 3:
switch (campaign.messageType)
{
case 1://email
foreach (oCampaignRecipient recipient in xData.GetTypedByCriteriaSpecific("recId", typeof(oCampaignRecipient), "campaignId,isIncluded", campaign.recId.ToString() + ",1", "", "pal_", false, connectionString))
{
string emailMessage = campaign.campaignBody;
oEmail email = new oEmail();
email.fromAddress = _setup.communicationEmail;
email.toAddress = recipient.recipientNumber;
email.Body = campaign.campaignBody;
email.Subject = campaign.campaignTitle;
email.Body = email.Body.Replace("{name}", recipient.recipientName);
oCampaignMessage msg = new oCampaignMessage();
msg.campaignId = campaign.recId;
msg.message = email.Body;
msg.messageType = 1;
msg.mobile = "";
//CVH 2018-01-11 Save email address
msg.email = email.toAddress;
msg.dateSent = DateTime.Now;
if (communication.SendAnEmail(email))
{
msg.status = 1;
msg.statusCode = 0;
msg.statusDisplay = "email sent OK";
}
else
{
msg.status = -1;
msg.statusCode = -1;
msg.statusDisplay = "failed to send the email";
}
//long smsResult = xSendEasy.SendSMS(handler.ReturnConfigAppSetting("smsU"), handler.ReturnConfigAppSetting("smsP"), recipient.recipientNumber, emailMessage);
//if (smsResult >= 0)
//{
// msg.statusCode = 0;
// msg.statusDisplay = "sms sent OK";
// creditsUsed++;
//}
//else
//{
// msg.statusCode = Convert.ToInt32(smsResult);
// msg.statusDisplay = xSendEasy.ReturnErrorDetail(msg.statusCode);
//}
//LogStatus(msg.statusDisplay);
SaveCampaignMessage(_dbName, msg);
//xData.UpdateTyped("recId", msg.recId.ToString(), typeof(oCampaignMessage), msg, "pal_", false, connectionString);
}
break;
case 2://sms
foreach (oCampaignRecipient recipient in xData.GetTypedByCriteriaSpecific("recId", typeof(oCampaignRecipient), "campaignId,isIncluded", campaign.recId.ToString() + ",1", "", "pal_", false, connectionString))
{
string SMSMessage = campaign.campaignBody.Replace("{contactName}", recipient.recipientName);
oCampaignMessage msg = new oCampaignMessage();
msg.campaignId = campaign.recId;
msg.message = SMSMessage;
msg.messageType = 2;
msg.mobile = recipient.recipientNumber;
msg.dateSent = DateTime.Now;
msg.status = 1;
//CVH 2018-04-09 If SMS body is more than 1 sms (more than 160 char), increase credits used proportionately
int smsCount = 0;
decimal calc = (SMSMessage.Length - 160m) / (160m - 7m) + 1m;
int.TryParse((Math.Ceiling(calc)).ToString(), out smsCount);
if (smsCount <= 0)
smsCount = 1;
long smsResult = xSendEasy.SendSMS(handler.ReturnConfigAppSetting("smsU"), handler.ReturnConfigAppSetting("smsP"), recipient.recipientNumber, SMSMessage);
if (smsResult >= 0)
{
msg.statusCode = 0;
msg.statusDisplay = "sms sent OK";
//creditsUsed++;
creditsUsed += smsCount;
}
else
{
msg.statusCode = Convert.ToInt32(smsResult);
msg.statusDisplay = xSendEasy.ReturnErrorDetail(msg.statusCode);
}
//LogStatus(msg.statusDisplay);
//xData.UpdateTyped("recId", msg.recId.ToString(), typeof(oCampaignMessage), msg, "pal_", false, connectionString);
//TODO update credits - consider global counter and update afterwards
SaveCampaignMessage(_dbName, msg);
}
break;
}
break;
#endregion
#region ad hoc
case 4:
switch (campaign.messageType)
{
case 1://email
foreach (oCampaignRecipient recipient in xData.GetTypedByCriteriaSpecific("recId", typeof(oCampaignRecipient), "campaignId,isIncluded", campaign.recId.ToString() + ",1", "", "pal_", false, connectionString))
{
string emailMessage = campaign.campaignBody;
oEmail email = new oEmail();
email.fromAddress = _setup.communicationEmail;
email.toAddress = recipient.recipientNumber;
email.Body = campaign.campaignBody;
email.Subject = campaign.campaignTitle;
email.Body = email.Body.Replace("{name}", recipient.recipientName);
oCampaignMessage msg = new oCampaignMessage();
msg.campaignId = campaign.recId;
msg.message = email.Body;
msg.messageType = 1;
msg.mobile = "";
//CVH 2018-01-11 Save email address
msg.email = email.toAddress;
msg.dateSent = DateTime.Now;
if (communication.SendAnEmail(email))
{
msg.status = 1;
msg.statusCode = 0;
msg.statusDisplay = "email sent OK";
}
else
{
msg.status = -1;
msg.statusCode = -1;
msg.statusDisplay = "failed to send the email";
}
SaveCampaignMessage(_dbName, msg);
//xData.UpdateTyped("recId", msg.recId.ToString(), typeof(oCampaignMessage), msg, "pal_", false, connectionString);
}
break;
case 2://sms
foreach (oCampaignRecipient recipient in xData.GetTypedByCriteriaSpecific("recId", typeof(oCampaignRecipient), "campaignId,isIncluded", campaign.recId.ToString() + ",1", "", "pal_", false, connectionString))
{
string SMSMessage = campaign.campaignBody.Replace("{contactName}", recipient.recipientName);
oCampaignMessage msg = new oCampaignMessage();
msg.campaignId = campaign.recId;
msg.message = SMSMessage;
msg.messageType = 2;
msg.mobile = recipient.recipientNumber;
msg.dateSent = DateTime.Now;
msg.status = 1;
//CVH 2018-04-09 If SMS body is more than 1 sms (more than 160 char), increase credits used proportionately
int smsCount = 0;
decimal calc = (SMSMessage.Length - 160m) / (160m - 7m) + 1m;
int.TryParse((Math.Ceiling(calc)).ToString(), out smsCount);
if (smsCount <= 0)
smsCount = 1;
long smsResult = xSendEasy.SendSMS(handler.ReturnConfigAppSetting("smsU"), handler.ReturnConfigAppSetting("smsP"), recipient.recipientNumber, SMSMessage);
if (smsResult >= 0)
{
msg.statusCode = 0;
msg.statusDisplay = "sms sent OK";
//creditsUsed++;
creditsUsed += smsCount;
}
else
{
msg.statusCode = Convert.ToInt32(smsResult);
msg.statusDisplay = xSendEasy.ReturnErrorDetail(msg.statusCode);
}
//LogStatus(msg.statusDisplay);
//xData.UpdateTyped("recId", msg.recId.ToString(), typeof(oCampaignMessage), msg, "pal_", false, connectionString);
//TODO update credits - consider global counter and update afterwards
SaveCampaignMessage(_dbName, msg);
}
break;
}
break;
#endregion
}
//update campaign after send to all recipients
campaign.dateUpdated = DateTime.Now;
campaign.lastSent = DateTime.Now;
if (campaign.isRecurring)
{
switch (campaign.interval)
{
case 2://daily
campaign.nextSend = DateTime.Now.AddDays(1);
break;
case 3://monthly
campaign.nextSend = DateTime.Now.AddMonths(1);
break;
}
}
//Update Campaign
xData.UpdateTyped("recId", campaign.recId.ToString(), typeof(oCampaign), campaign, "pal_", false, connectionString);
//if (UpdateCampaign(_dbName, campaign))
//{
// //LogStatus("campaign " + campaign.campaignTitle + " has been successfully processed.");
//}
//update global credits
oSMSCreditLog creditLog = new oSMSCreditLog();
creditLog.customerCode = _setup.code;
creditLog.transactionType = (int)pNums.SMSCreditTransactionType.Used;
creditLog.transactionDate = DateTime.Now;
creditLog.reference = campaign.recId.ToString();
creditLog.qty = creditsUsed * -1;
xData.SaveTyped("recId", typeof(oSMSCreditLog), creditLog, "pal_", true);
xData.UpdateAllocatedSMS(_setup.code);
}
}
}
///
/// Datatable of Campaigns for a database
///
///
public static DataTable GetCampaigns(string dbName)
{
DataTable result = new DataTable();
SqlCommand command = new SqlCommand();
try
{
string query = "SELECT * FROM pal_Campaign WHERE isActive = '1' AND (isRecurring = '1' OR YEAR(lastSent) < 2015) ";
command = CreateCommand(dbName, query);
DataSet ds = dataTier.ReturnMethods.ReturnDataSet(ref command);
if (ds.Tables.Count > 0)
result = ds.Tables[0];
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
dataTier.CommandMethods.CompleteSQLCommand(ref command);
}
return result;
}
///
/// Datatable of Subscribers for Email
///
///
public static DataTable GetSubcribers(string dbName)
{
DataTable result = new DataTable();
SqlCommand command = new SqlCommand();
try
{
string query = "SELECT * FROM pal_Subscriber WHERE isActive = '1' ";
command = CreateCommand(dbName, query);
DataSet ds = dataTier.ReturnMethods.ReturnDataSet(ref command);
if (ds.Tables.Count > 0)
result = ds.Tables[0];
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
dataTier.CommandMethods.CompleteSQLCommand(ref command);
}
return result;
}
///
/// Datatable of Subscribers for SMS
///
///
public static DataTable GetSubcribersSMS(string dbName)
{
DataTable result = new DataTable();
SqlCommand command = new SqlCommand();
try
{
string query = "SELECT * FROm pal_SubscriberSMS WHERE isActive = '1' ";
command = CreateCommand(dbName, query);
DataSet ds = dataTier.ReturnMethods.ReturnDataSet(ref command);
if (ds.Tables.Count > 0)
result = ds.Tables[0];
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
dataTier.CommandMethods.CompleteSQLCommand(ref command);
}
return result;
}
///
/// Datatable of Calendar Events
///
///
public static DataTable GetCalendarEvents(string dbName)
{
DataTable result = new DataTable();
SqlCommand command = new SqlCommand();
try
{
//int eventTypeID = 0;
string eventTypeIDs = String.Empty;
string q = "Select recId FROM pal_CalendarEventType Where name LIKE '%Appointment%' ";
command = CreateCommand(dbName, q);
DataSet dsEventTypeIDs = dataTier.ReturnMethods.ReturnDataSet(ref command);
if (dsEventTypeIDs != null && dsEventTypeIDs.Tables.Count > 0)
{
foreach (DataRow row in dsEventTypeIDs.Tables[0].Rows)
{
if (eventTypeIDs == String.Empty)
eventTypeIDs = row["recId"].ToString();
else
eventTypeIDs += "," + row["recId"].ToString(); ;
}
}
if (eventTypeIDs != String.Empty)
{
dataTier.CommandMethods.CompleteSQLCommand(ref command);
//CVH 2018-08-01 Only select appointments that are not recurring here. Selecting all recurring appointments in another method to process occurrences
//string query = "SELECT * FROm pal_CalendarEvent WHERE calendarEventTypeId = " + eventTypeID + " AND (start > '" + utils.fixDate(DateTime.Now.AddDays(1).Date) + "' AND start < '" + utils.fixDate(DateTime.Now.AddDays(2).Date) + "') ";
string query = "SELECT * FROM pal_CalendarEvent WHERE calendarEventTypeId IN (" + eventTypeIDs + ") AND (start > '" + utils.fixDate(DateTime.Now.AddDays(1).Date) + "' AND start < '" + utils.fixDate(DateTime.Now.AddDays(2).Date) + "') AND recurrenceRule = '' ";
command = CreateCommand(dbName, query);
DataSet ds = dataTier.ReturnMethods.ReturnDataSet(ref command);
if (ds.Tables.Count > 0)
result = ds.Tables[0];
}
else
result = new DataTable();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
dataTier.CommandMethods.CompleteSQLCommand(ref command);
}
return result;
}
///
/// Datatable of all recurring Calendar Events
///
///
public static DataTable GetCalendarEventsRecurring(string dbName)
{
DataTable result = new DataTable();
SqlCommand command = new SqlCommand();
try
{
//int eventTypeID = 0;
string eventTypeIDs = String.Empty;
string q = "Select recId FROM pal_CalendarEventType Where name LIKE '%Appointment%' ";
command = CreateCommand(dbName, q);
DataSet dsEventTypeIDs = dataTier.ReturnMethods.ReturnDataSet(ref command);
if (dsEventTypeIDs != null && dsEventTypeIDs.Tables.Count > 0)
{
foreach (DataRow row in dsEventTypeIDs.Tables[0].Rows)
{
if (eventTypeIDs == String.Empty)
eventTypeIDs = row["recId"].ToString();
else
eventTypeIDs += "," + row["recId"].ToString(); ;
}
}
if (eventTypeIDs != String.Empty)
{
dataTier.CommandMethods.CompleteSQLCommand(ref command);
string query = "SELECT * FROM pal_CalendarEvent WHERE calendarEventTypeId IN (" + eventTypeIDs + ") AND recurrenceRule <> '' ";
command = CreateCommand(dbName, query);
DataSet ds = dataTier.ReturnMethods.ReturnDataSet(ref command);
if (ds.Tables.Count > 0)
result = ds.Tables[0];
}
else
result = new DataTable();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
dataTier.CommandMethods.CompleteSQLCommand(ref command);
}
return result;
}
///
/// Datatable of Users
///
///
public static DataTable GetUsers(string users)
{
DataTable result = new DataTable();
SqlCommand command = new SqlCommand();
try
{
string query = "SELECT * from pal_User where recId In (" + users + ") ";
command = CreateCommand("Framework_EVOL-1", query);
DataSet ds = dataTier.ReturnMethods.ReturnDataSet(ref command);
if (ds.Tables.Count > 0)
result = ds.Tables[0];
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
dataTier.CommandMethods.CompleteSQLCommand(ref command);
}
return result;
}
///
/// Datatable of Users
///
///
public static DataTable GetUsersForCalendar(int calendarId, oCalendarEvent calEvent, string dbName, oSetup _setup)
{
DataTable result = new DataTable();
SqlCommand command = new SqlCommand();
try
{
string userIds = calEvent.users;
if (userIds != String.Empty)
{
//get module for this calendar
string modQ = "Select * FROM pal_CalendarEventModule WHERE calendarId = " + calendarId + " ";
command = CreateCommand(dbName, modQ);
DataSet ds = dataTier.ReturnMethods.ReturnDataSet(ref command);
dataTier.CommandMethods.CompleteSQLCommand(ref command);
foreach (DataRow row in ds.Tables[0].Rows)
{
if (row["moduleId"].ToString() == "3")
{
result = GetUsers(userIds);
}
else if (row["moduleId"].ToString() == "10")
{
command = CreateCommand(dbName, "");
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "sp_GetSurfaceCalendarUsersByIDs";
command.Parameters.Add(new SqlParameter("@SurfaceID", row["entityId"].ToString()));
command.Parameters.Add(new SqlParameter("@NameField", row["entityNameField"].ToString()));
command.Parameters.Add(new SqlParameter("@SurnameField", row["entitySurnameField"].ToString()));
command.Parameters.Add(new SqlParameter("@EmailField", row["entityEmailField"].ToString()));
command.Parameters.Add(new SqlParameter("@MobileField", row["entityMobileField"].ToString()));
command.Parameters.Add(new SqlParameter("@ItemIDs", userIds));
DataSet data = dataTier.ReturnMethods.ReturnDataSet(ref command);
command.Parameters.Clear();
ArrayList usrList = new ArrayList();
if (data.Tables.Count > 0)
{
foreach (DataRow uRow in data.Tables[0].Rows)
{
oUser usr = new oUser();
usr.customerCode = _setup.code;
usr.recId = int.Parse(uRow[0].ToString());
usr.name = uRow[3].ToString();
usr.surname = uRow[4].ToString();
usr.email = uRow[5].ToString();
usr.tel = uRow[6].ToString();
usrList.Add(usr);
}
}
result = utils.ConvertListToDataTable(usrList);
}
}
}
else
{
DataTable resultTable = new DataTable();
if (calEvent.isNewContact && calEvent.newContactTel != String.Empty)
{
ArrayList usrList = new ArrayList();
oUser usr = new oUser();
usr.customerCode = _setup.code;
usr.name = calEvent.newContactName;
usr.surname = calEvent.newContactSurname;
usr.email = calEvent.newContactEmail;
usr.tel = calEvent.newContactTel;
usrList.Add(usr);
resultTable = utils.ConvertListToDataTable(usrList);
}
result = resultTable;
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
dataTier.CommandMethods.CompleteSQLCommand(ref command);
}
return result;
}
///
/// Check if this is a duplicate message
///
///
///
///
public static bool CheckDuplicateMessage(string dbName, oCampaignMessage _msg)
{
bool result = false;
string query = String.Empty;
SqlCommand command = new SqlCommand();
try
{
query = "SELECT * FROM [dbo].[pal_CampaignMessage] " +
"WHERE [campaignId] = " + _msg.campaignId +
" AND [mobile] = '" + _msg.mobile + "'" +
" AND [dateSent] > '" + utils.fixDate(_msg.dateSent.AddHours(-12)) + "'";
//create command
command = CreateCommand(dbName, query);
//select record
DataSet data = dataTier.ReturnMethods.ReturnDataSet(ref command);
if (data.Tables.Count > 0)
{
result = data.Tables[0].Rows.Count > 0;
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
dataTier.CommandMethods.CompleteSQLCommand(ref command);
}
return result;
}
///
/// Save Campaign Message to database
///
///
///
public static bool SaveCampaignMessage(string dbName, oCampaignMessage _msg)
{
bool result = false;
string query = String.Empty;
SqlCommand command = new SqlCommand();
try
{
if (_msg.recId > 0)
{
query = "UPDATE [dbo].[pal_CampaignMessage] " +
"SET [campaignId] = @campaignId " +
",[message] = @message " +
",[messageType] = @messageType " +
",[email] = @email " +
",[mobile] = @mobile " +
",[dateSent] = @dateSent " +
",[status] = @status " +
",[statusCode] = @statusCode " +
",[statusDisplay] = @statusDisplay " +
"WHERE [recId] = " + _msg.recId.ToString();
}
else
{
query = "INSERT INTO [dbo].[pal_CampaignMessage] " +
"([campaignId] " +
",[message] " +
",[messageType] " +
",[email] " +
",[mobile] " +
",[dateSent] " +
",[status] " +
",[statusCode] " +
",[statusDisplay]) " +
"VALUES " +
"(@campaignId " +
",@message " +
",@messageType " +
",@email " +
",@mobile " +
",@dateSent " +
",@status " +
",@statusCode " +
",@statusDisplay) ";
}
//create command
command = CreateCommand(dbName, query);
command.Parameters.Add(new SqlParameter("@campaignId", _msg.campaignId));
command.Parameters.Add(new SqlParameter("@message", utils.formatSqlString(_msg.message)));
command.Parameters.Add(new SqlParameter("@messageType", _msg.messageType));
command.Parameters.Add(new SqlParameter("@email", _msg.email));
command.Parameters.Add(new SqlParameter("@mobile", _msg.mobile));
command.Parameters.Add(new SqlParameter("@dateSent", utils.fixDate(_msg.dateSent)));
command.Parameters.Add(new SqlParameter("@status", _msg.status));
command.Parameters.Add(new SqlParameter("@statusCode", _msg.statusCode));
command.Parameters.Add(new SqlParameter("@statusDisplay", utils.formatSqlString(_msg.statusDisplay)));
//save campaign message
result = dataTier.SavingMethods.UpdateRecord(ref command);
command.Parameters.Clear();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
dataTier.CommandMethods.CompleteSQLCommand(ref command);
}
return result;
}
///
/// Update a Campaign
///
///
///
///
public static bool UpdateCampaign(string dbName, oCampaign _campaign)
{
bool result = false;
string query = String.Empty;
SqlCommand command = new SqlCommand();
try
{
if (_campaign.recId > 0)
{
query = "UPDATE [dbo].[pal_Campaign] " +
"SET [campaignTitle] = @campaignTitle " +
",[campaignBody] = @campaignBody " +
",[campaignType] = @campaignType " +
",[isRecurring] = @isRecurring " +
",[interval] = @interval " +
",[messageType] = @messageType " +
",[lastSent] = @lastSent " +
",[nextSend] = @nextSend " +
",[dateUpdated] = @dateUpdated " +
"WHERE [recId] = " + _campaign.recId.ToString();
//create command
command = CreateCommand(dbName, query);
command.Parameters.Add(new SqlParameter("@campaignTitle", _campaign.campaignTitle));
command.Parameters.Add(new SqlParameter("@campaignBody", utils.formatSqlString(_campaign.campaignBody)));
command.Parameters.Add(new SqlParameter("@campaignType", _campaign.campaignType));
command.Parameters.Add(new SqlParameter("@isRecurring", _campaign.isRecurring));
command.Parameters.Add(new SqlParameter("@interval", _campaign.interval));
command.Parameters.Add(new SqlParameter("@messageType", _campaign.messageType));
command.Parameters.Add(new SqlParameter("@lastSent", utils.fixDate(_campaign.lastSent)));
command.Parameters.Add(new SqlParameter("@nextSend", utils.fixDate(_campaign.nextSend)));
command.Parameters.Add(new SqlParameter("@dateUpdated", _campaign.dateUpdated));
//save campaign
result = dataTier.SavingMethods.UpdateRecord(ref command);
command.Parameters.Clear();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
dataTier.CommandMethods.CompleteSQLCommand(ref command);
}
return result;
}
///
/// Create a SQL Commane for a database name
///
///
///
public static SqlCommand CreateCommand(string dbName, string query)
{
string conn = handler.ReturnConfigAppSetting("conn");
conn = conn.Replace("{catalog}", dbName);
SqlConnection con = new SqlConnection(conn);
con.Open();
SqlCommand result = new SqlCommand(query, con);
return result;
}
}
}