/// <summary>
/// This method will return an object representing the record matching the primary key information specified.
/// </summary>
///
/// <param name="pk" type="CampaignJobPrimaryKey">Primary Key information based on which data is to be fetched.</param>
///
/// <returns>object of class CampaignJob</returns>
///
/// <remarks>
///
/// <RevisionHistory>
/// Author Date Description
/// DLGenerator 2/3/2010 4:14:00 PM Created function
///
/// </RevisionHistory>
///
/// </remarks>
///
public static CampaignJob SelectOne(CampaignJobPrimaryKey pk, String ConnectionString)
{
DatabaseHelper oDatabaseHelper = new DatabaseHelper(ConnectionString);
bool ExecutionState = false;
// Pass the values of all key parameters to the stored procedure.
System.Collections.Specialized.NameValueCollection nvc = pk.GetKeysAndValues();
foreach (string key in nvc.Keys)
{
oDatabaseHelper.AddParameter("@" + key,nvc[key] );
}
// The parameter '@ErrorCode' will contain the status after execution of the stored procedure.
oDatabaseHelper.AddParameter("@ErrorCode", -1, System.Data.ParameterDirection.Output);
IDataReader dr=oDatabaseHelper.ExecuteReader("sp_CampaignJob_SelectbyPrimaryKey", ref ExecutionState);
if (dr.Read())
{
CampaignJob obj=new CampaignJob(ConnectionString);
PopulateObjectFromReader(obj,dr);
dr.Close();
oDatabaseHelper.Dispose();
return obj;
}
else
{
dr.Close();
oDatabaseHelper.Dispose();
return null;
}
}
public XmlDocument SingleCampaignReports(string UserID, string Password, int CampaignID)
{
XmlDocument doc = new XmlDocument();
StringBuilder str = new StringBuilder();
str.Append("<ReportInfo>");
string ConnectionString = GetConnectionString(UserID, Password);
if (ConnectionString.Trim().Length != 0 && CampaignID>0)
{
CampaignMasterPrimaryKey objCampaignMasterPrimaryKey = new CampaignMasterPrimaryKey(CampaignID);
CampaignMaster objCampaignMaster = CampaignMaster.SelectOneWithCampaignJobUsingCampaignMasterID(objCampaignMasterPrimaryKey, ConnectionString);
CampaignJobPrimaryKey pk=new CampaignJobPrimaryKey(CampaignID);
CampaignJobs objCampaignjob = CampaignJob.SelectByField("CampaignMasterID", CampaignID, ConnectionString);
//CampaignJob job = CampaignJob.SelectOne(pk, ConnectionString);
str.Append("<CampaignMasterID>" + objCampaignMaster.CampaignId.ToString() + "</CampaignMasterID> ");
str.Append("<CampaignMasterName>" + objCampaignMaster.CampaignName.ToString() + "</CampaignMasterName> ");
str.Append("<ResultsAsOf>" + DateTime.Now.ToString() + "</ResultsAsOf> ");
str.Append("<Subject>" + objCampaignMaster.Subject.ToString() + "</Subject> ");
ListContactMaster objListContactMaster = new ListContactMaster(ConnectionString);
DataTable dtcontacts = objListContactMaster.getContactsWithCampaignID(objCampaignMaster.SelectCondition, CampaignID.ToString());
int ForwardCount = -1;
int Fcount = LinkDetailBase.RecipientsForwardCount(ConnectionString, CampaignID, out ForwardCount);
long ActiveCount = Convert.ToInt64(dtcontacts.Rows.Count);
str.Append("<TotalRecipientsCount>" + ActiveCount.ToString() + "</TotalRecipientsCount> ");
str.Append("<ContactsTargetd>" + ActiveCount.ToString() + "</ContactsTargetd> ");
foreach (CampaignJob job in objCampaignjob)
{
str.Append("<SucessfulDeliverdCount>" + job.DeliveredCount.ToString() + "</SucessfulDeliverdCount> ");
str.Append("<BounceCount>" + job.BounceCount.ToString() + "</BounceCount> ");
str.Append("<OpenCount>" + job.OpenCount.ToString() + "</OpenCount> ");
str.Append("<ClickCount>" + job.ClickCount.ToString() + "</ClickCount> ");
str.Append("<UnsubscribeCount>" + job.UnSubscribeCount.ToString() + "</UnsubscribeCount> ");
str.Append("<ForwardCount>" + Fcount .ToString()+ "</ForwardCount>");
}
str.Append("<SucessfulDeliverdCount>");
DataTable ListSuccessfulDelivered = ContactMaster.SelectSuccessfulDeliveredByCampId(ConnectionString, CampaignID);
if (ListSuccessfulDelivered != null)
{
for (int i = 0; i < ListSuccessfulDelivered.Rows.Count; i++)
{
str.Append("<Row>" + i.ToString() + "</Row>");
str.Append("<EmailAddress>" + ListSuccessfulDelivered.Rows[i]["EmailAddress"].ToString() + "</EmailAddress> ");
}
}
str.Append("</SucessfulDeliverdCount>");
str.Append("<BounceCount>");
int TotalBounceCount, SoftBounceCount, HardBounceCount = -1;
DataTable BounceRecipients = ContactMaster.SelectRecipientsBounceRecByCampId(ConnectionString, CampaignID, out TotalBounceCount, out SoftBounceCount, out HardBounceCount);
if (BounceRecipients != null)
{
for (int i = 0; i < BounceRecipients.Rows.Count; i++)
{
str.Append("<Row>" + i.ToString() + "</Row>");
str.Append("<EmailAddress>" + BounceRecipients.Rows[i]["EmailAddress"].ToString() + "</EmailAddress> ");
}
}
str.Append("</BounceCount>");
str.Append("<OpenCount>");
DataTable emailsOpened = ContactMaster.EmailOpendByCampId(ConnectionString, CampaignID);
if (emailsOpened != null)
{
for (int i = 0; i < emailsOpened.Rows.Count; i++)
{
str.Append("<Row>" + i.ToString() + "</Row>");
str.Append("<EmailAddress>" + emailsOpened.Rows[i]["EmailAddress"].ToString() + "</EmailAddress> ");
}
}
str.Append("</OpenCount>");
str.Append("<ClickCount>");
DataTable emailsClicked = ContactMaster.EmailClickedRecByCampId(ConnectionString, CampaignID);
if (emailsClicked != null)
{
for (int i = 0; i < emailsClicked.Rows.Count; i++)
{
str.Append("<Row>" + i.ToString() + "</Row>");
str.Append("<EmailAddress>" + emailsClicked.Rows[i]["EmailAddress"].ToString() + "</EmailAddress> ");
}
}
str.Append("</ClickCount>");
str.Append("<UnsubscribeCount>");
int NeverOptedCount, CommCount, NotInterestedCount, OtherCount = -1;
DataTable ListUnsubScribeRecipients = ContactMaster.SelectUnsubscribeRecipients(ConnectionString, CampaignID, out NeverOptedCount, out CommCount, out NotInterestedCount, out OtherCount);
if (ListUnsubScribeRecipients != null)
{
for (int i = 0; i < ListUnsubScribeRecipients.Rows.Count; i++)
{
str.Append("<Row>" + i.ToString() + "</Row>");
str.Append("<EmailAddress>" + ListUnsubScribeRecipients.Rows[i]["EmailAddress"].ToString() + "</EmailAddress> ");
}
}
str.Append("</UnsubscribeCount>");
str.Append("<ForwardCount>");
DataTable forwardRecipients = ContactMaster.SelectForwardRecipients(ConnectionString, CampaignID);
if (forwardRecipients != null)
{
for (int i = 0; i < forwardRecipients.Rows.Count; i++)
{
str.Append("<Row>" + i.ToString() + "</Row>");
str.Append("<EmailAddress>" + forwardRecipients.Rows[i]["EmailAddress"].ToString() + "</EmailAddress> ");
}
}
str.Append("</ForwardCount>");
string ss = str.ToString();
}
str.Append("</ReportInfo>");
doc.LoadXml(str.ToString());
return doc;
}
/// <summary>
/// This method will Delete one row from the database using the primary key information
/// </summary>
///
/// <param name="pk" type="CampaignJobPrimaryKey">Primary Key information based on which data is to be fetched.</param>
///
/// <returns>True if succeeded</returns>
///
/// <remarks>
///
/// <RevisionHistory>
/// Author Date Description
/// DLGenerator 2/3/2010 4:14:00 PM Created function
///
/// </RevisionHistory>
///
/// </remarks>
///
public static bool Delete(CampaignJobPrimaryKey pk, String ConnectionString)
{
DatabaseHelper oDatabaseHelper = new DatabaseHelper(ConnectionString);
bool ExecutionState = false;
// Pass the values of all key parameters to the stored procedure.
System.Collections.Specialized.NameValueCollection nvc = pk.GetKeysAndValues();
foreach (string key in nvc.Keys)
{
oDatabaseHelper.AddParameter("@" + key,nvc[key] );
}
// The parameter '@ErrorCode' will contain the status after execution of the stored procedure.
oDatabaseHelper.AddParameter("@ErrorCode", -1, System.Data.ParameterDirection.Output);
oDatabaseHelper.ExecuteScalar("sp_CampaignJob_Delete", ref ExecutionState);
oDatabaseHelper.Dispose();
return ExecutionState;
}
public XmlDocument GetMailingStausByMailingID(string UserID, string Password, int MailingID)
{
XmlDocument doc = new XmlDocument();
StringBuilder str = new StringBuilder();
str.Append("<MailingStatusInfo>");
string ConnectionString = GetConnectionString(UserID, Password);
try
{
if (ConnectionString.Trim().Length != 0 && MailingID > 0)
{
CampaignMasterPrimaryKey objCampaignMasterPrimaryKey = new CampaignMasterPrimaryKey(MailingID);
CampaignMaster objCampaignMaster = CampaignMaster.SelectOneWithCampaignJobUsingCampaignMasterID(objCampaignMasterPrimaryKey, ConnectionString);
if (objCampaignMaster != null)
{
CampaignJobPrimaryKey pk = new CampaignJobPrimaryKey(MailingID);
CampaignJobs objCampaignjob = CampaignJob.SelectByField("CampaignMasterID", MailingID, ConnectionString);
ListMasterPrimaryKey pkList;
ListMaster listMaster = new ListMaster(ConnectionString);
ListContactMaster objListContactMaster;
string ListNames = string.Empty;
string ListIds = string.Empty;
if (objCampaignMaster.SelectCondition.Trim().Length > 0)
{
if (objCampaignMaster.SelectCondition.Trim().Contains(","))
{
string[] Lists = objCampaignMaster.SelectCondition.Split(',');
foreach (string list in Lists)
{
pkList = new ListMasterPrimaryKey(Convert.ToInt32(list));
listMaster = ListMaster.SelectOne(pkList, ConnectionString);
if (listMaster != null)
{
ListNames = ListNames + listMaster.ListName + ",";
ListIds = ListIds + listMaster.ListMasterID.ToString() + ",";
}
}
ListNames = ListNames.Substring(0, ListNames.Length - 1);
ListIds = ListIds.Substring(0, ListIds.Length - 1);
}
else
{
pkList = new ListMasterPrimaryKey(Convert.ToInt32(objCampaignMaster.SelectCondition));
listMaster = ListMaster.SelectOne(pkList, ConnectionString);
objListContactMaster = new ListContactMaster(ConnectionString);
ListNames = listMaster.ListName;
ListIds = listMaster.ListMasterID.ToString();
}
str.Append("<CurrentStatusForTheMailing>");
if (objCampaignMaster.SelectCondition.Length > 0)
str.Append("<ListCreated>Yes</ListCreated>");
else
str.Append("<ListCreated>Error</ListCreated>");
if (objCampaignjob[0] != null && objCampaignjob[0].ScheduledDatetime.ToString().Length > 0)
str.Append("<ScheduleDefined>Yes</ScheduleDefined>");
else
str.Append("<ScheduleDefined>No</ScheduleDefined>");
MailApprovals objMailApprovals = MailApproval.SelectByField("MailingID", MailingID, ConnectionString);
if (objMailApprovals.Count > 0)
{
if (objMailApprovals[0].ApprovalStatus == "Approved")
str.Append("<CreativeIsApproved>Yes</CreativeIsApproved>");
else
str.Append("<CreativeIsApproved>Waiting For Approval</CreativeIsApproved>");
}
if (objCampaignjob.Count > 0 && objCampaignjob[0].CompletionTime != null)
{
str.Append("<MailingIsInSendingProcess>No</MailingIsInSendingProcess>");
str.Append("<MailingIsComplete>Yes</MailingIsComplete>");
}
else
{
str.Append("<MailingIsInSendingProcess>Yes</MailingIsInSendingProcess>");
str.Append("<MailingIsComplete>No</MailingIsComplete>");
}
str.Append("</CurrentStatusForTheMailing>");
str.Append("<MailingSummary>");
str.Append("<Client>" + UserID + "</Client>");
str.Append("<MailingName>" + objCampaignMaster.CampaignName.ToString() + "</MailingName> ");
str.Append("<MailingID>" + objCampaignMaster.CampaignId.ToString() + "</MailingID> ");
str.Append("<FromName>" + objCampaignMaster.FromName + "</FromName> ");
str.Append("<FromMail>" + objCampaignMaster.FromAddress + "</FromMail> ");
str.Append("<Subject>" + objCampaignMaster.Subject + "</Subject> ");
str.Append("<ReplyToMail>" + objCampaignMaster.ReplytoAddress + "</ReplyToMail> ");
str.Append("<MailingListFileQuantity>" + objCampaignjob[0].TotalRecipients + "</MailingListFileQuantity > ");
if (objCampaignMaster.SuppressSelection != null && objCampaignMaster.SuppressSelection != "0" && objCampaignMaster.SuppressSelection != "")
{
ListContactMasters objListContacts = ListContactMaster.SelectByField("ListMasterID", objCampaignMaster.SuppressSelection, ConnectionString);
str.Append("<SuppressListFileQuantity>" + objListContacts.Count.ToString() + "</SuppressListFileQuantity > ");
}
if (objCampaignjob.Count > 0)
str.Append("<ScheduleDateTime>" + objCampaignjob[0].ScheduledDatetime + "</ScheduleDateTime> ");
str.Append("<MailingListName>" + ListNames + "</MailingListName> ");
str.Append("<MailingListID>" + ListIds + "</MailingListID> ");
str.Append("</MailingSummary>");
}
else
str.Append("<Message>List Not Found For The Mailing With ID: " + MailingID + "</Message>");
}
else
str.Append("<Message>Mailing with id: " + MailingID + " not exist</Message>");
}
else
str.Append("<Message>Invalid UserName/Password</Message>");
}
catch (Exception ex)
{
str.Append("<Error>" + ex.Message + "</Error>");
}
str.Append("</MailingStatusInfo>");
doc.LoadXml(str.ToString());
return doc;
}
public XmlDocument GetMailingReportByMailingID(string UserID, string Password, int MailingID)
{
XmlDocument doc = new XmlDocument();
StringBuilder str = new StringBuilder();
str.Append("<MailingReportInfo>");
string ConnectionString = GetConnectionString(UserID, Password);
if (ConnectionString.Trim().Length != 0 && MailingID > 0)
{
CampaignMasterPrimaryKey objCampaignMasterPrimaryKey = new CampaignMasterPrimaryKey(MailingID);
CampaignMaster objCampaignMaster = CampaignMaster.SelectOneWithCampaignJobUsingCampaignMasterID(objCampaignMasterPrimaryKey, ConnectionString);
if (objCampaignMaster != null)
{
CampaignJobPrimaryKey pk = new CampaignJobPrimaryKey(MailingID);
CampaignJobs objCampaignjob = CampaignJob.SelectByField("CampaignMasterID", MailingID, ConnectionString);
str.Append("<MailingID>" + objCampaignMaster.CampaignId.ToString() + "</MailingID> ");
str.Append("<MailingName>" + objCampaignMaster.CampaignName.ToString() + "</MailingName> ");
str.Append("<MailingCreatedDate>" + objCampaignMaster.CreatedDate + "</MailingCreatedDate> ");
str.Append("<ResultsAsOf>" + DateTime.Now.ToString() + "</ResultsAsOf> ");
str.Append("<Subject>" + objCampaignMaster.Subject.ToString() + "</Subject> ");
ListMasterPrimaryKey pkList = new ListMasterPrimaryKey(Convert.ToInt32(objCampaignMaster.SelectCondition));
ListMaster listMaster = ListMaster.SelectOne(pkList, ConnectionString);
str.Append("<ListName>" + listMaster.ListName + "</ListName> ");
ListContactMaster objListContactMaster = new ListContactMaster(ConnectionString);
DataTable dtcontacts = objListContactMaster.getContactsWithCampaignID(objCampaignMaster.SelectCondition, MailingID.ToString());
int ForwardCount = -1;
int Fcount = LinkDetailBase.RecipientsForwardCount(ConnectionString, MailingID, out ForwardCount);
long ActiveCount = Convert.ToInt64(dtcontacts.Rows.Count);
str.Append("<TotalRecipientsCount>" + ActiveCount.ToString() + "</TotalRecipientsCount> ");
str.Append("<ContactsTargetd>" + ActiveCount.ToString() + "</ContactsTargetd> ");
foreach (CampaignJob job in objCampaignjob)
{
str.Append("<ScheduledTime>" + job.ScheduledDatetime.ToString() + "</ScheduledTime> ");
str.Append("<CompletionTime>" + job.CompletionTime.ToString() + "</CompletionTime> ");
long SentCount = (string.IsNullOrEmpty(job.DeliveredCount.ToString()) == true) ? 0 : Convert.ToInt64(job.DeliveredCount.ToString());
str.Append("<SucessfulDeliverdCount>" + SentCount + "</SucessfulDeliverdCount> ");
long BCount = (string.IsNullOrEmpty(job.BounceCount.ToString()) == true) ? 0 : Convert.ToInt64(job.BounceCount.ToString());
str.Append("<BounceCount>" + BCount + "</BounceCount> ");
long OCount = (string.IsNullOrEmpty(job.OpenCount.ToString()) == true) ? 0 : Convert.ToInt64(job.OpenCount.ToString());
str.Append("<OpenCount>" + OCount + "</OpenCount> ");
long ClickCount = (string.IsNullOrEmpty(job.ClickCount.ToString()) == true) ? 0 : Convert.ToInt64(job.ClickCount.ToString());
str.Append("<ClickCount>" + ClickCount + "</ClickCount> ");
long UCount = (string.IsNullOrEmpty(job.UnSubscribeCount.ToString()) == true) ? 0 : Convert.ToInt64(job.UnSubscribeCount.ToString());
str.Append("<UnsubscribeCount>" + UCount + "</UnsubscribeCount> ");
long FCount = (string.IsNullOrEmpty(Fcount.ToString()) == true) ? 0 : Convert.ToInt64(Fcount.ToString());
str.Append("<ForwardCount>" + FCount + "</ForwardCount>");
}
str.Append("<SucessfulDeliverd>");
DataTable ListSuccessfulDelivered = ContactMaster.SelectSuccessfulDeliveredByCampId(ConnectionString, MailingID);
if (ListSuccessfulDelivered != null)
{
for (int i = 0; i < ListSuccessfulDelivered.Rows.Count; i++)
{
str.Append("<EmailAddress>" + ListSuccessfulDelivered.Rows[i]["EmailAddress"].ToString() + "</EmailAddress> ");
}
}
str.Append("</SucessfulDeliverd>");
str.Append("<BounceContacts>");
int TotalBounceCount, SoftBounceCount, HardBounceCount = -1;
DataTable BounceRecipients = ContactMaster.SelectRecipientsBounceRecByCampId(ConnectionString, MailingID, out TotalBounceCount, out SoftBounceCount, out HardBounceCount);
if (BounceRecipients != null)
{
for (int i = 0; i < BounceRecipients.Rows.Count; i++)
{
str.Append("<EmailAddress>" + BounceRecipients.Rows[i]["EmailAddress"].ToString() + "</EmailAddress> ");
}
}
str.Append("</BounceContacts>");
str.Append("<OpenedContacts>");
DataTable emailsOpened = ContactMaster.EmailOpendByCampId(ConnectionString, MailingID);
if (emailsOpened != null)
{
for (int i = 0; i < emailsOpened.Rows.Count; i++)
{
str.Append("<EmailAddress>" + emailsOpened.Rows[i]["EmailAddress"].ToString() + "</EmailAddress> ");
}
}
str.Append("</OpenedContacts>");
str.Append("<ClickedContacts>");
DataTable emailsClicked = ContactMaster.EmailClickedRecByCampId(ConnectionString, MailingID);
if (emailsClicked != null)
{
for (int i = 0; i < emailsClicked.Rows.Count; i++)
{
str.Append("<EmailAddress>" + emailsClicked.Rows[i]["EmailAddress"].ToString() + "</EmailAddress> ");
}
}
str.Append("</ClickedContacts>");
str.Append("<UnsubscribedContacts>");
int NeverOptedCount, CommCount, NotInterestedCount, OtherCount = -1;
DataTable ListUnsubScribeRecipients = ContactMaster.SelectUnsubscribeRecipients(ConnectionString, MailingID, out NeverOptedCount, out CommCount, out NotInterestedCount, out OtherCount);
if (ListUnsubScribeRecipients != null)
{
for (int i = 0; i < ListUnsubScribeRecipients.Rows.Count; i++)
{
str.Append("<EmailAddress>" + ListUnsubScribeRecipients.Rows[i]["EmailAddress"].ToString() + "</EmailAddress> ");
}
}
str.Append("</UnsubscribedContacts>");
str.Append("<ForwardedContacts>");
DataTable forwardRecipients = ContactMaster.SelectForwardRecipients(ConnectionString, MailingID);
if (forwardRecipients != null)
{
for (int i = 0; i < forwardRecipients.Rows.Count; i++)
{
str.Append("<EmailAddress>" + forwardRecipients.Rows[i]["EmailAddress"].ToString() + "</EmailAddress> ");
}
}
str.Append("</ForwardedContacts>");
string ss = str.ToString();
}
else
str.Append("<Message>Mailing with id: " + MailingID + " not exist</Message>");
}
else
str.Append("<Message>Invalid UserName/Password</Message>");
str.Append("</MailingReportInfo>");
doc.LoadXml(str.ToString());
return doc;
}
public void getGraphByCampaignId()
{
try
{
lblSubtitle1.Text = ddlcampaigns.SelectedItem.Text + " Campaign Report";
List<string> xValues = new List<string>();
List<decimal> yValuesSentCount = new List<decimal>();
List<decimal> yValuesOpenCount = new List<decimal>();
List<decimal> yValuesOpenCountChart = new List<decimal>();
List<decimal> yValuesClickCountChart = new List<decimal>();
CampaignMasterPrimaryKey pk = new CampaignMasterPrimaryKey(Convert.ToInt64(ddlcampaigns.SelectedValue));
CampaignMaster objCampaignMaster = CampaignMaster.SelectOne(pk, ConnectionString);
CampaignJobPrimaryKey Camppk = new CampaignJobPrimaryKey(Convert.ToInt64(ddlcampaigns.SelectedValue));
//CampaignJob objCampaignJob = CampaignJob.SelectOne(Camppk, ConnectionString);
CampaignJobs objCampaignJob = CampaignJob.SelectByField("CampaignMasterID", ddlcampaigns.SelectedValue.ToString(), ConnectionString);
int opencount = 0; int sentcount = 0; int clickcount = 0;
foreach (CampaignJob job in objCampaignJob)
{
//Calculate LastTopTenCampaignsSent
//get sum of opencount of all jobs in this month.
opencount = (job.OpenCount != null) ? Convert.ToInt32(job.OpenCount.ToString()) : 0;
//opencount = Convert.ToInt32(job.OpenCount.ToString());
//get sum of sentcount of all jobs in this month.
sentcount = (job.DeliveredCount != null) ? Convert.ToInt32(job.OpenCount.ToString()) : 0;//Convert.ToInt32(job.DeliveredCount.ToString());
//get sum of sentcount of all jobs in this month.
clickcount = (job.ClickCount != null) ? Convert.ToInt32(job.OpenCount.ToString()) : 0;//Convert.ToInt32(job.ClickCount);
}
xValues.Add(objCampaignMaster.CampaignName);
yValuesSentCount.Add(Convert.ToDecimal(sentcount));
yValuesOpenCount.Add(Convert.ToDecimal(opencount));
//this is to bind the open count chart
if (sentcount > 0)
{
yValuesOpenCountChart.Add(Convert.ToDecimal(opencount) / Convert.ToDecimal(sentcount) * 100);
}
else
{
yValuesOpenCountChart.Add(0);
}
//Calculate click for that particular month.
if (opencount != 0)
{
yValuesClickCountChart.Add(Convert.ToDecimal(clickcount) / Convert.ToDecimal(opencount) * 100);
}
else
{
yValuesClickCountChart.Add(0);
}
//Show chart for sent and open count
showChartLastTopTenCampaignsSent(xValues.ToArray(), yValuesSentCount.ToArray(), yValuesOpenCount.ToArray());
//Show chart for open count
showChartTopTenCampaignsByOpenRate(xValues.ToArray(), yValuesOpenCountChart.ToArray());
//Show chart for click count
showChartTopTenCampaignsByClickRate(xValues.ToArray(), yValuesClickCountChart.ToArray());
}
catch (Exception ex)
{
}
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
string ConnectionString = string.Empty;
string username = txtusername.Text;
ProfileBase objProfileBase = ProfileBase.Create(username, true);
string dbName = objProfileBase.GetPropertyValue("DBName").ToString();
string dbUserID = objProfileBase.GetPropertyValue("DBUserID").ToString();
string dbPassword = objProfileBase.GetPropertyValue("DBPassword").ToString();
string dbServerName = objProfileBase.GetPropertyValue("DBServerName").ToString();
ConnectionString =
string.Format("Data Source={0};Initial Catalog={1};User ID={2};Password={3};Pooling=True",
dbServerName, dbName, dbUserID, dbPassword);
Int64 MailingID = Convert.ToInt64(txtmailingid.Text);
if (ConnectionString.Trim().Length != 0 && MailingID > 0)
{
CampaignMasterPrimaryKey objCampaignMasterPrimaryKey = new CampaignMasterPrimaryKey(MailingID);
CampaignMaster objCampaignMaster = CampaignMaster.SelectOneWithCampaignJobUsingCampaignMasterID(objCampaignMasterPrimaryKey, ConnectionString);
if (objCampaignMaster != null)
{
CampaignJobPrimaryKey pk = new CampaignJobPrimaryKey(MailingID);
CampaignJobs objCampaignjob = CampaignJob.SelectByField("CampaignMasterID", MailingID, ConnectionString);
string[] ListId = objCampaignMaster.SelectCondition.ToString().Split(',');
ListMaster listMaster;
ListMasterPrimaryKey pkList;
if (ListId.Length > 0 && ListId[0].Trim().Length>0)
{
pkList = new ListMasterPrimaryKey(Convert.ToInt32(ListId[0]));
listMaster = ListMaster.SelectOne(pkList, ConnectionString);
ListContactMaster objListContactMaster = new ListContactMaster(ConnectionString);
lblMailingListName.Text = listMaster.ListName;
lblMailingListID.Text = listMaster.ListMasterID.ToString();
//DataTable dtcontacts = objListContactMaster.getContactsWithCampaignID(objCampaignMaster.SelectCondition, MailingID.ToString());
}
//long ActiveCount = Convert.ToInt64(dtcontacts.Rows.Count);
long ActiveCount = Convert.ToInt64(objCampaignjob[0].TotalRecipients);
if (objCampaignMaster.SelectCondition.Length > 0)
lblListCreatedStatus.Text = "Yes";
else
lblListCreatedStatus.Text = "No";
if (objCampaignjob[0] != null && objCampaignjob[0].ScheduledDatetime.ToString().Length > 0)
lblScheduleStatus.Text = "Yes";
else
lblScheduleStatus.Text = "No";
MailApprovals objMailApprovals = MailApproval.SelectByField("MailingID", MailingID, ConnectionString);
if (objMailApprovals.Count > 0)
{
if (objMailApprovals[0].ApprovalStatus == "Approved")
lblApprovedStatus.Text = "Yes";
else
lblApprovedStatus.Text = "Waiting For Approval";
}
if (objCampaignjob.Count > 0 && objCampaignjob[0].CompletionTime != null)
{
lblMailSendingProcessStatus.Text = "No";
lblMailCompleteStatus.Text = "Yes";
}
else
{
lblMailSendingProcessStatus.Text = "Yes";
lblMailCompleteStatus.Text = "No";
}
lblClient.Text = txtusername.Text;
lblMailingName.Text = objCampaignMaster.CampaignName.ToString();
lblMailingID.Text = objCampaignMaster.CampaignId.ToString();
lblFromName.Text = objCampaignMaster.FromName;
lblFromMail.Text = objCampaignMaster.FromAddress;
lblSubject.Text = objCampaignMaster.Subject;
lblReplyToMail.Text = objCampaignMaster.ReplytoAddress;
lblMailingListFileQuantity.Text = ActiveCount.ToString();
if (objCampaignMaster.SuppressSelection != "0" && objCampaignMaster.SuppressSelection != "")
{
ListContactMasters objListContacts = ListContactMaster.SelectByField("ListMasterID", objCampaignMaster.SuppressSelection, ConnectionString);
lblSuppressListFileQuantity.Text = objListContacts.Count.ToString();
}
if (objCampaignjob.Count > 0)
lblScheduleDate.Text = objCampaignjob[0].ScheduledDatetime.ToString();
}
else
lblmsg.Text = "Mailing with id: " + MailingID + " not exist";
}
else
lblmsg.Text = "Invalid UserName/Password";
}
public void getMailingRpt(DataRow dr, int RptSenthr)
{
Logger.logdata(logforemailbuilder, "************** in getMailingRpt(..) method **************");
Logger.logdata(logforemailbuilder,
string.Format("getMailingRpt(..) has been started at {0}", DateTime.Now.ToString()));
string message = string.Empty;
int MailingId = Convert.ToInt32(dr["MailingID"].ToString());
Logger.logdata(logforemailbuilder, string.Format("Mailing Id:{0}", MailingId.ToString()));
//string[] rptMailIds= dr["ReportMailTo"].ToString().Split(',');
string rptMailTo = dr["ReportMailTo"].ToString();
CampaignMasterPrimaryKey objCampaignMasterPrimaryKey = new CampaignMasterPrimaryKey(MailingId);
CampaignMaster objCampaignMaster = CampaignMaster.SelectOneWithCampaignJobUsingCampaignMasterID(objCampaignMasterPrimaryKey, clientConnectionString);
CampaignJobPrimaryKey pk = new CampaignJobPrimaryKey(MailingId);
CampaignJobs objCampaignjob = CampaignJob.SelectByField("CampaignMasterID", MailingId, clientConnectionString);
string MailinName = objCampaignMaster.CampaignName.ToString();
Logger.logdata(logforemailbuilder, string.Format("MailingName:{0}", MailinName.ToString()));
string Subject = objCampaignMaster.Subject.ToString();
string sentTime = objCampaignjob[0].ScheduledDatetime.ToString();
string completionTime = objCampaignjob[0].CompletionTime.ToString();
string DeliveredCount = objCampaignjob[0].DeliveredCount.ToString();
string BounceCount = objCampaignjob[0].BounceCount.ToString();
string OpenCount = objCampaignjob[0].OpenCount.ToString();
string ClickCount = objCampaignjob[0].ClickCount.ToString();
string UnsubscribeCount = objCampaignjob[0].UnSubscribeCount.ToString();
ListMasterPrimaryKey pkList = new ListMasterPrimaryKey(Convert.ToInt32(objCampaignMaster.SelectCondition));
ListMaster listMaster = ListMaster.SelectOne(pkList, connectionString);
string listName = listMaster.ListName;
ListContactMaster objListContactMaster = new ListContactMaster(clientConnectionString);
DataTable dtcontacts = objListContactMaster.getContactsWithCampaignID(objCampaignMaster.SelectCondition, MailingId.ToString());
string TargetedCount = dtcontacts.Rows.Count.ToString();
int ForwardCount = -1;
int Fcount = LinkDetailBase.RecipientsForwardCount(clientConnectionString, MailingId, out ForwardCount);
string forwardCount = Fcount.ToString();
#region MailBody
StringBuilder mailContent = new StringBuilder();
mailContent.Append("<table width=\"600px\">");
mailContent.Append("<tr><td style=\"font-weight: bold\">Mailing Report Summary</td></tr>");
mailContent.Append("<tr><td width=\"250px\">Mailing Name:</td><td>" + MailinName + "</td></tr>");
mailContent.Append("<tr><td>Subject Line:</td><td>" + Subject + "</td></tr>");
mailContent.Append("<tr><td>Send Start Date:</td><td>" + sentTime + "</td></tr>");
mailContent.Append("<tr><td>Send Completion Date :</td><td>" + completionTime + "</td></tr>");
mailContent.Append("<tr><td>List Name:</td><td>" + listName + "</td></tr>");
mailContent.Append("<tr><td style=\"height:20px\"></td><td></td></tr>");
mailContent.Append("<tr><td style=\"font-weight:bold\">Overall Stats</td></tr>");
mailContent.Append("<tr><td>Contacts Targeted</td><td>" + TargetedCount + "</td></tr>");
mailContent.Append("<tr><td>Successful Delivered</td> <td>" + DeliveredCount + "</td></tr>");
mailContent.Append("<tr><td>BounceCount</td> <td>" + BounceCount + "</td></tr>");
mailContent.Append("<tr><td>OpenCount</td> <td>" + OpenCount + "</td></tr>");
mailContent.Append("<tr><td>ClickCount</td> <td>" + ClickCount + "</td></tr>");
mailContent.Append("<tr><td>UnsubscribeCount</td><td>" + UnsubscribeCount + "</td></tr>");
mailContent.Append("<tr><td>ForwardCount</td><td>" + forwardCount + "</td></tr>");
mailContent.Append("</table>");
Logger.logdata(logforemailbuilder, string.Format("MailingBody :{0}", mailContent.ToString()));
#endregion
try
{
#region Build Mail Body DataTable
DataTable mailRptDt = new DataTable();
DataColumn s1 = new DataColumn("Mailing Report Summary");
DataColumn s2 = new DataColumn(" ");
mailRptDt.Columns.Add(s1);
mailRptDt.Columns.Add(s2);
DataRow row1 = mailRptDt.NewRow();
row1[s1] = "Mailing Name:";
row1[s2] = MailinName;
mailRptDt.Rows.Add(row1);
DataRow row2 = mailRptDt.NewRow();
row2[s1] = "Subject Line:";
row2[s2] = Subject;
mailRptDt.Rows.Add(row2);
DataRow row3 = mailRptDt.NewRow();
row3[s1] = "Send Start Date:";
row3[s2] = sentTime;
mailRptDt.Rows.Add(row3);
DataRow row4 = mailRptDt.NewRow();
row4[s1] = "Send Completion Date :";
row4[s2] = completionTime;
mailRptDt.Rows.Add(row4);
DataRow row5 = mailRptDt.NewRow();
row5[s1] = "List Name:";
row5[s2] = listName;
mailRptDt.Rows.Add(row5);
DataRow row6 = mailRptDt.NewRow();
row6[s1] = "";
row6[s2] = "";
mailRptDt.Rows.Add(row6);
DataRow row7 = mailRptDt.NewRow();
row7[s1] = "Overall Stats";
row7[s2] = "";
mailRptDt.Rows.Add(row7);
DataRow row8 = mailRptDt.NewRow();
row8[s1] = "Contacts Targeted:";
row8[s2] = TargetedCount;
mailRptDt.Rows.Add(row8);
DataRow row9 = mailRptDt.NewRow();
row9[s1] = "Successful Delivered:";
row9[s2] = DeliveredCount;
mailRptDt.Rows.Add(row9);
DataRow row10 = mailRptDt.NewRow();
row10[s1] = "Bounce Count:";
row10[s2] = BounceCount;
mailRptDt.Rows.Add(row10);
DataRow row11 = mailRptDt.NewRow();
row11[s1] = "Open Count:";
row11[s2] = OpenCount;
mailRptDt.Rows.Add(row11);
DataRow row12 = mailRptDt.NewRow();
row12[s1] = "Click Count:";
row12[s2] = ClickCount;
mailRptDt.Rows.Add(row12);
DataRow row13 = mailRptDt.NewRow();
row13[s1] = "Unsubscribe Count:";
row13[s2] = UnsubscribeCount;
mailRptDt.Rows.Add(row13);
DataRow row14 = mailRptDt.NewRow();
row14[s1] = "Forward Count:";
row14[s2] = forwardCount;
mailRptDt.Rows.Add(row14);
#endregion
string dat = DateTime.Now.ToString("ddMMMyyyyhhmmssTT");
string pPath = ConfigurationSettings.AppSettings["AttachFilePath"].ToString();
string apiFromMail = ConfigurationSettings.AppSettings["ApiFromMail"].ToString();
DirectoryInfo dirInfo = new DirectoryInfo(pPath);
if (!dirInfo.Exists)
dirInfo.Create();
pPath = Path.Combine(pPath, "MailingReport" + dat + ".csv");
Logger.logdata(logforemailbuilder, string.Format("Attachment Path: {0}", pPath.ToString()));
CreateCSVFile(mailRptDt, pPath);
Logger.logdata(logforemailbuilder, string.Format("sendEmail(..) has been started at {0}", DateTime.Now.ToString()));
bool status = sendEmail(apiFromMail, rptMailTo.ToString().Trim(), string.Empty, "Report", false, mailContent.ToString(), pPath, out message);
if (status)
{
Logger.logdata(logforemailbuilder, "Report Mail Sent Successfully to:'" + rptMailTo + "'");
#region Update Hours status in MailApproval Table
MailApproval objMailApproval = new MailApproval(clientConnectionString);
bool updateStatus = objMailApproval.UpdateHoursStatus(MailingId, RptSenthr);
if (updateStatus)
Logger.logdata(logforemailbuilder, string.Format("{0} Hours Status Updated Successfully at {1}", RptSenthr.ToString(), DateTime.Now.ToString()));
else
Logger.logdata(logforemailbuilder, string.Format("{0} Hours Status Updated Failed at {1}", RptSenthr.ToString(), DateTime.Now.ToString()));
#endregion
}
else
{
Logger.logdata(logforemailbuilder, "Report Mail Sending Failed");
}
Logger.logdata(logforemailbuilder, string.Format("sendEmail(..) method has been ended at {0}", DateTime.Now.ToString()));
}
catch (Exception ex)
{
}
Logger.logdata(logforemailbuilder, string.Format("getMailingRpt(..) method has been ended at {0}", DateTime.Now.ToString()));
Logger.logdata(logforemailbuilder, "************** Exit getMailingRpt(..) method **************");
}
