/// <summary>
/// This method will get row(s) from the database using the value of the field specified
/// along with the details of the child table.
/// </summary>
///
/// <param name="pk" type="CampaignMasterPrimaryKey">Primary Key information based on which data is to be fetched.</param>
///
/// <returns>object of class CampaignJobs</returns>
///
/// <remarks>
///
/// <RevisionHistory>
/// Author Date Description
/// DLGenerator 2/3/2010 4:14:00 PM Created function
///
/// </RevisionHistory>
///
/// </remarks>
///
public static CampaignJobs SelectAllByForeignKeyFromCampaignMaster(CampaignMasterPrimaryKey pk, String ConnectionString)
{
DatabaseHelper oDatabaseHelper = new DatabaseHelper(ConnectionString);
bool ExecutionState = false;
CampaignJobs obj = null;
// 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_SelectAllByForeignKeyCampaignMaster", ref ExecutionState);
obj = new CampaignJobs();
obj = CampaignJob.PopulateObjectsFromReaderWithCheckingReader(dr, oDatabaseHelper,ConnectionString);
dr.Close();
oDatabaseHelper.Dispose();
return obj;
}
internal static CampaignJobs PopulateObjectsFromReaderNew(IDataReader rdr, string ConnectionString)
{
CampaignJobs list = new CampaignJobs();
while (rdr.Read())
{
CampaignJob obj = new CampaignJob(ConnectionString);
PopulateObjectFromReaderNew(obj, rdr);
list.Add(obj);
}
return list;
}
/// <summary>
/// Populates the fields for multiple objects from the columns found in an open reader.
/// </summary>
///
/// <param name="rdr" type="IDataReader">An object that implements the IDataReader interface</param>
///
/// <returns>Object of CampaignJobs</returns>
///
/// <remarks>
///
/// <RevisionHistory>
/// Author Date Description
/// DLGenerator 2/3/2010 4:14:00 PM Created function
///
/// </RevisionHistory>
///
/// </remarks>
///
internal static CampaignJobs PopulateObjectsFromReaderWithCheckingReader(IDataReader rdr, DatabaseHelper oDatabaseHelper, string ConnectionString)
{
CampaignJobs list = new CampaignJobs();
if (rdr.Read())
{
CampaignJob obj = new CampaignJob(ConnectionString);
PopulateObjectFromReader(obj, rdr);
list.Add(obj);
while (rdr.Read())
{
obj = new CampaignJob(ConnectionString);
PopulateObjectFromReader(obj, rdr);
list.Add(obj);
}
oDatabaseHelper.Dispose();
return list;
}
else
{
oDatabaseHelper.Dispose();
return null;
}
}
protected void SingleMailingCounts(string campaignID, string mailingId)
{
if (ddlMailingNames.SelectedItem.Text != defaultMailingItem)
lblCampaignTitle.Text = ddlMailingNames.SelectedItem.Text.ToString();
else if (ddlCampaignsNames.SelectedItem.Text != defaultCampItem)
lblCampaignTitle.Text = ddlCampaignsNames.SelectedItem.Text.ToString();
else
{
lblCampaignTitle.Text = string.Empty;
}
Session["TitleName"] = lblCampaignTitle.Text;
string selectedList = string.Empty;
if (mailingId != string.Empty || campaignID != string.Empty)
{
long ActiveCount = 0;
lblContactsTargeted.Text = "0";
lblSuccessfulDeliveries.Text = "0";
lblUnsubscribe.Text = "0";
lblTotalOpens.Text = "0";
lblUniqueopens.Text = "0";
lblBounces.Text = "0";
lblTotalClicks.Text = "0";
lblUniqueClicks.Text = "0";
lblForwrds.Text = "0";
List<string> listsSelected = new List<string>();
List<string> xValues = new List<string>();
List<double> yValues = new List<double>();
string startMailingId = string.Empty;
string endingMailingId = string.Empty;
if (ddlCampaignsNames.SelectedItem.Text != defaultCampItem || ddlMailingNames.SelectedItem.Text!=defaultMailingItem)
{
if (ddlMailingNames.SelectedItem.Text == defaultMailingItem)
{
//Get the Latest Sent Mailing.
startMailingId = ddlMailingNames.Items[1].Value.ToString();
CampaignMasterPrimaryKey objCampaignMasterPrimaryKey = new CampaignMasterPrimaryKey(Convert.ToInt64(startMailingId));
objCampaignMaster = CampaignMaster.SelectOneWithCampaignJobUsingCampaignMasterID(objCampaignMasterPrimaryKey, ConnectionString);
objCampaignjob = CampaignJob.SelectByField("CampaignMasterID", Convert.ToInt64(startMailingId), ConnectionString);
lblSendCompletionDate.Text = objCampaignjob[0].CompletionTime.ToString();
//Get the First Sent Mailing.
endingMailingId = ddlMailingNames.Items[ddlMailingNames.Items.Count - 1].Value.ToString();
if (ddlCampaignsNames.SelectedItem.Text != defaultCampItem)
{
objEndCampaignjob = CampaignJob.SelectByField("CampaignMasterID", Convert.ToInt64(endingMailingId), ConnectionString);
lblSendStartDate.Text = objEndCampaignjob[0].StartTime.ToString();
}
else
{
lblSendStartDate.Text = objCampaignjob[0].StartTime.ToString();
}
}
else
{
CampaignMasterPrimaryKey objCampaignMasterPrimaryKey = new CampaignMasterPrimaryKey(Convert.ToInt64(mailingId));
objCampaignMaster = CampaignMaster.SelectOneWithCampaignJobUsingCampaignMasterID(objCampaignMasterPrimaryKey, ConnectionString);
objCampaignjob = CampaignJob.SelectByField("CampaignMasterID", Convert.ToInt64(mailingId), ConnectionString);
lblSendStartDate.Text = objCampaignjob[0].StartTime.ToString();
lblSendCompletionDate.Text = objCampaignjob[0].CompletionTime.ToString();
}
}
if (mailingId.Length != 0)
{
if (objCampaignMaster.Subject.Length > 30)
lblSubject.Text = objCampaignMaster.Subject.Substring(0, 30) + "..";
else
lblSubject.Text = objCampaignMaster.Subject.ToString();
lblSubject.ToolTip = objCampaignMaster.Subject.ToString();
lblsentDateTime.Text = "Sent: " + objCampaignjob[0].CompletionTime.ToString();
Session["SentDateTime"] = lblsentDateTime.Text;
string SelectCondition = objCampaignMaster.SelectCondition;
string[] ListIds = SelectCondition.Split(',');
StringBuilder selectedListExport = new StringBuilder();
//Commented by Swaroop no need to touch the db iam take the count form indivitual tables code written bellow
ListContactMaster objListContactMaster = new ListContactMaster(ConnectionString);
DataTable dtcontacts = objListContactMaster.getContactsWithCampaignID(objCampaignMaster.SelectCondition, mailingId.ToString());
Session["ContactsTargeted"] = dtcontacts;
foreach (var Id in ListIds)
{
if (Id != "")
{
ListMasterPrimaryKey pk = new ListMasterPrimaryKey(Convert.ToInt64(Id));
ListMaster list = ListMaster.SelectOne(pk, ConnectionString);
if (list != null)
{
bool IsListRemoved = true;
if (list.IsDeleted == null || list.IsDeleted == false)
IsListRemoved = false;
if (!IsListRemoved)
selectedList += list.ListName.ToString() + ",";
else
{
selectedList += list.ListName.ToString() + "(List has been removed),";
}
listsSelected.Add(list.ListName);
}
}
}
if (selectedList != "")
selectedList = selectedList.TrimEnd(',');
if (selectedList.Length > 0)
{
lblSelectedList.Style.Add("color", "#717171");
selectedList.Remove(selectedList.Length - 2, 1);
lblSelectedList.Text = selectedList.ToString();
Session["ListNames"] = selectedList.ToString();
}
else
{
selectedList = "The List has been moved or removed";
Session["ListNames"] = selectedList.ToString();
lblSelectedList.Text = string.Empty;
lblSelectedList.Style.Add("color", "red");
lblSelectedList.Text = "The List has been moved or removed";
}
if (selectedList.Length >= 30)
{
lblSelectedList.Text = selectedList.Substring(0, 30) + "...";
lblSelectedList.ToolTip = selectedList;
}
}
lblResultsAsOf.Text = DateTime.Now.ToString();
//Bellow mailingId become to empty bcz. when iam selecting only Campaign not Mailing at that time only we want to disply latest sent Mailing Info of particular Campaign.
if (hdfMailingID.Value == string.Empty)
mailingId = string.Empty;
#region Swaroop
//Get the values form Individual Tables Link EmailOpenDatils Developed By Swaroop etc...
DataTable ListSuccessfulDelivered, emailsOpened, emailsClicked, BounceRecipients, forwardRecipients, ListUnsubScribeRecipients = null;
if (mailingId != string.Empty)
{
ListSuccessfulDelivered = ContactMaster.SelectSuccessfulDeliveredByCampId(ConnectionString, Convert.ToInt32(mailingId));
sentcount = ListSuccessfulDelivered.Rows.Count;
}
else
{
ListSuccessfulDelivered = ContactMaster.SelectSuccessfulDeliveredByAssignCampId(ConnectionString, Convert.ToInt32(campaignID));
sentcount = ListSuccessfulDelivered.Rows.Count;
}
if (mailingId != string.Empty)
{
emailsOpened = ContactMaster.EmailOpendRecByCampId_PageWise(ConnectionString, mailingId, "ContactId", "desc", 0, 10, out Count, out TotalOpenCounts, out UniqueOpenCounts);
opencount = UniqueOpenCounts;
}
else
{
emailsOpened = ContactMaster.EmailOpendRecByAssignCampId_PageWise(ConnectionString, campaignID, "ContactId", "desc", 0, 10, out Count, out TotalOpenCounts, out UniqueOpenCounts);
opencount = UniqueOpenCounts;
}
if (mailingId != string.Empty)
{
emailsClicked = ContactMaster.EmailClickedRecByCampId_PageWise(ConnectionString, mailingId, "ContactId", "desc", 0, 10, out Count, out TotalClickCounts, out UniqueClickCounts);
}
else
{
emailsClicked = ContactMaster.EmailClickedRecByAssignCampId_PageWise(ConnectionString, campaignID, "ContactId", "desc", 0, 10, out Count, out TotalClickCounts, out UniqueClickCounts);
}
if (mailingId != string.Empty)
{
BounceRecipients = ContactMaster.SelectRecipientsBounceRecByCampId(ConnectionString, Convert.ToInt32(mailingId), out TotalBounceCount, out SoftBounceCount, out HardBounceCount);
bouncecount = BounceRecipients.Rows.Count;
}
else
{
BounceRecipients = ContactMaster.SelectRecipientsBounceRecByAssignCampId(ConnectionString, Convert.ToInt32(campaignID), out TotalBounceCount, out SoftBounceCount, out HardBounceCount);
bouncecount = TotalBounceCount;
}
unopencount = sentcount - opencount;
if (mailingId != string.Empty)
{
forwardRecipients = ContactMaster.SelectForwardRecipients(ConnectionString, Convert.ToInt32(mailingId));
Fcount = forwardRecipients.Rows.Count;
}
else
{
forwardRecipients = ContactMaster.SelectForwardRecipientsByAssignId(ConnectionString, Convert.ToInt32(campaignID), out TotalForwardCount);
Fcount = TotalForwardCount;
}
if (mailingId != string.Empty)
{
ListUnsubScribeRecipients = ContactMaster.SelectUnsubscribeRecipients(ConnectionString, Convert.ToInt32(mailingId), out NeverOptedCount, out CommCount, out NotInterestedCount, out OtherCount);
UnSubscribeCount = ListUnsubScribeRecipients.Rows.Count;
}
else
{
ListUnsubScribeRecipients = ContactMaster.SelectUnsubscribeRecipientsByAssignedCampId(ConnectionString, Convert.ToInt32(campaignID), out NeverOptedCount, out CommCount, out NotInterestedCount, out OtherCount, out TotalUnsubCount);
UnSubscribeCount = TotalUnsubCount;
}
yValues.Clear();
if (sentcount != 0)
{
yValues.Add(Convert.ToDouble(opencount));
yValues.Add(Convert.ToDouble(bouncecount));
yValues.Add(Convert.ToDouble(unopencount));
}
#endregion
lblRecipients.Text = (sentcount + bouncecount).ToString();
ActiveCount = sentcount + bouncecount;
double TotalCount = sentcount + bouncecount;
double plotY = 0;
ShowCampaignCountChart.Series["Series1"].Points.Clear();
if (yValues.Count != 0)
{
for (int pointIndex = 0; pointIndex < yValues.Count; pointIndex++)
{
plotY = yValues[pointIndex];
ShowCampaignCountChart.Series["Series1"].Points.AddY(plotY);
ShowCampaignCountChart.Series["Series1"].Points[pointIndex].LabelForeColor = System.Drawing.Color.White;
}
ShowCampaignCountChart.Series["Series1"].Points[0].LegendText = "Open" + " " + ((Convert.ToDouble(yValues[0].ToString()) / TotalCount) * 100).ToString("###########0.00") + "%" + " (" + yValues[0].ToString() + ")";
ShowCampaignCountChart.Series["Series1"].Points[1].LegendText = "Bounce" + " " + ((Convert.ToDouble(yValues[1].ToString()) / TotalCount) * 100).ToString("###########0.00") + "%" + " (" + yValues[1].ToString() + ")";
ShowCampaignCountChart.Series["Series1"].Points[2].LegendText = "Unopen" + " " + ((Convert.ToDouble(yValues[2].ToString()) / TotalCount) * 100).ToString("###########0.00") + "%" + " (" + yValues[2].ToString() + ")";
//Display tooltip for the chart
ShowCampaignCountChart.Series["Series1"].Points[0].ToolTip = "Open Count:" + yValues[0].ToString() + " (" + Math.Round((double.Parse(yValues[0].ToString()) * 100 / TotalCount)).ToString() + "%" + ")";
ShowCampaignCountChart.Series["Series1"].Points[1].ToolTip = "Bounce Count:" + yValues[1].ToString() + " (" + Math.Round((double.Parse(yValues[1].ToString()) * 100 / TotalCount)).ToString() + "%" + ")";
ShowCampaignCountChart.Series["Series1"].Points[2].ToolTip = "Unopen Count:" + yValues[2].ToString() + " (" + Math.Round((double.Parse(yValues[2].ToString()) * 100 / TotalCount)).ToString() + "%" + ")";
//Applying colors
ShowCampaignCountChart.Series["Series1"].Points[0].Color = System.Drawing.Color.Green;
ShowCampaignCountChart.Series["Series1"].Points[1].Color = System.Drawing.Color.Red;
ShowCampaignCountChart.Series["Series1"].Points[2].Color = System.Drawing.Color.DarkBlue;
//show Chart
ShowCampaignCountChart.Series["Series1"].ChartType = SeriesChartType.Pie;
ShowCampaignCountChart.Series["Series1"]["PieStartAngle"] = "30";
//get sum of sentcount for selected campaign .
double sendcount = Convert.ToDouble(sentcount);
double targetcount = sendcount + yValues[1];
//showing value in table
lblContactsTargeted.Text = lblContactsTargeted.Text != null ? ActiveCount.ToString() : "0";
lblSuccessfulDeliveries.Text = sendcount.ToString() != "" ? sendcount + "(" + Math.Round((sendcount) * 100 / ActiveCount).ToString() + "%" + ")" : "0";
lblBounces.Text = yValues[1].ToString() != null ? yValues[1].ToString() + "(" + Math.Round((double.Parse(yValues[1].ToString()) * 100 / ActiveCount)).ToString() + "%" + ")" : "0";
lblUnsubscribe.Text = UnSubscribeCount.ToString();
lblForwrds.Text = Fcount.ToString() != null ? Fcount.ToString() : "0";
lblTotalOpens.Text = TotalOpenCounts.ToString();
lblUniqueopens.Text = UniqueOpenCounts.ToString() != null ? UniqueOpenCounts.ToString() + "(" + Math.Round((double.Parse(UniqueOpenCounts.ToString()) * 100 / sendcount)).ToString() + "%" + ")" : "0";
lblUniqueClicks.Text = UniqueClickCounts.ToString();
lblTotalClicks.Text = TotalClickCounts.ToString();
}
else
Session["allCounts"] = null;
# region DataExportToExcel
string str = selectedList.ToString(); //.Replace(",", ";");
DataTable dtExportSummary = new DataTable();
DataColumn s1;
if (ddlMailingNames.SelectedItem.ToString() != defaultMailingItem)
s1 = new DataColumn("Dashboard: Mailing Report Summary");
else
s1 = new DataColumn("Dashboard: Campaign Report Summary");
DataColumn s2 = new DataColumn(" ");
DataColumn s3 = new DataColumn(" ");
dtExportSummary.Columns.Add(s1);
dtExportSummary.Columns.Add(s2);
dtExportSummary.Columns.Add(s3);
DataRow row1 = dtExportSummary.NewRow();
if (ddlMailingNames.SelectedItem.ToString() != defaultMailingItem)
row1[s1] = "Mailing Name:";
else
row1[s1] = "Campaign Name:";
if (ddlMailingNames.SelectedItem.ToString() != defaultMailingItem)
row1[s2] = ddlMailingNames.SelectedItem.ToString();
else
row1[s2] = ddlCampaignsNames.SelectedItem.ToString();
row1[s3] = "";
dtExportSummary.Rows.Add(row1);
DataRow row2 = dtExportSummary.NewRow();
row2[s1] = "Subject Line:";
row2[s2] = lblSubject.ToolTip.ToString();
row2[s3] = "";
dtExportSummary.Rows.Add(row2);
DataRow row3 = dtExportSummary.NewRow();
row3[s1] = "Results as of :";
row3[s2] = lblResultsAsOf.Text.ToString();
row3[s3] = "";
dtExportSummary.Rows.Add(row3);
DataRow row4 = dtExportSummary.NewRow();
row4[s1] = "Send Start Date : ";
row4[s2] = lblSendStartDate.Text.ToString();
row4[s3] = "";
dtExportSummary.Rows.Add(row4);
DataRow row5 = dtExportSummary.NewRow();
row5[s1] = "Send Completion Date : ";
row5[s2] = lblSendCompletionDate.Text.ToString();
row5[s3] = "";
dtExportSummary.Rows.Add(row5);
DataRow row6 = dtExportSummary.NewRow();
row6[s1] = "List(s) Selected : ";
row6[s2] = str.ToString();
row6[s3] = "";
dtExportSummary.Rows.Add(row6);
DataRow row7 = dtExportSummary.NewRow();
row7[s1] = "Overall Stats";
row7[s2] = "";
row7[s3] = "";
dtExportSummary.Rows.Add(row7);
DataRow row8 = dtExportSummary.NewRow();
row8[s1] = "Contacts Targeted:";
row8[s2] = lblContactsTargeted.Text;
row8[s3] = "";
dtExportSummary.Rows.Add(row8);
DataRow row9 = dtExportSummary.NewRow();
row9[s1] = "Successful Deliveries:";
row9[s2] = lblSuccessfulDeliveries.Text;
row9[s3] = "";
dtExportSummary.Rows.Add(row9);
DataRow row10 = dtExportSummary.NewRow();
row10[s1] = "Bounced:";
row10[s2] = lblBounces.Text;
row10[s3] = "";
dtExportSummary.Rows.Add(row10);
DataRow row11 = dtExportSummary.NewRow();
row11[s1] = "Unsubscribes:";
row11[s2] = lblUnsubscribe.Text;
row11[s3] = "";
dtExportSummary.Rows.Add(row11);
DataRow row12 = dtExportSummary.NewRow();
row12[s1] = "Unique Opens:";
row12[s2] = lblUniqueopens.Text;
row12[s3] = "";
dtExportSummary.Rows.Add(row12);
DataRow row13 = dtExportSummary.NewRow();
row13[s1] = "Total Opens:";
row13[s2] = lblTotalOpens.Text;
row13[s3] = "";
dtExportSummary.Rows.Add(row13);
DataRow row14 = dtExportSummary.NewRow();
row14[s1] = "Unique Clicks:";
row14[s2] = lblUniqueClicks.Text;
row14[s3] = "";
dtExportSummary.Rows.Add(row14);
DataRow row15 = dtExportSummary.NewRow();
row15[s1] = "Total Clicks:";
row15[s2] = lblTotalClicks.Text;
row15[s3] = "";
dtExportSummary.Rows.Add(row15);
DataRow row16 = dtExportSummary.NewRow();
row16[s1] = "Forwards:";
row16[s2] = lblForwrds.Text;
row16[s3] = "";
dtExportSummary.Rows.Add(row16);
ViewState["CampaignExportSummary"] = dtExportSummary;
#endregion
}
else
{
lblContactsTargeted.Text = "0";
lblSuccessfulDeliveries.Text = "0";
lblTotalOpens.Text = "0";
lblUnsubscribe.Text = "0";
lblUniqueopens.Text = "0";
lblBounces.Text = "0";
lblUniqueClicks.Text = "0";
lblForwrds.Text = "0";
lblTotalClicks.Text = "0";
lblCampaignTitle.Text = string.Empty;
lblSubject.Text = string.Empty;
lblResultsAsOf.Text = string.Empty;
lblSendStartDate.Text = string.Empty;
lblSendCompletionDate.Text = string.Empty;
lblsentDateTime.Text = string.Empty;
lblRecipients.Text = string.Empty;
lblSelectedList.Text = string.Empty;
ShowCampaignCountChart.Series["Series1"].Points.Clear();
}
}
