/// <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 **************"); }