private string GetDatetimeFromFile(string fileName)
{
//e-4-3-09 6
//-4-12-09 7
//12-12-09 8
DateTime date;
fileName = fileName.Substring(fileName.Length - 12, 8);
if (fileName[0].Equals('-'))
{
fileName = fileName.Remove(0, 1);
string[] str = fileName.Split("-".ToCharArray());
if (str[0].Length == 1)
{
date = DateTime.ParseExact(fileName, "M-dd-yy", null);
}
else
{
date = DateTime.ParseExact(fileName, "MM-d-yy", null);
}
return(DayCode.ToDayCode(date).ToString());
}
else
if (fileName[1].Equals('-'))
{
fileName = fileName.Remove(0, 1);
fileName = fileName.Remove(0, 1);
date = DateTime.ParseExact(fileName, "M-d-yy", null);
return(DayCode.ToDayCode(date).ToString());
}
date = DateTime.ParseExact(fileName, "MM-dd-yy", null);
return(DayCode.ToDayCode(date).ToString());
}
protected virtual void InitalizeMetaDataParameters(SqlCommand insertCommand,
SourceDataRowReader <RetrieverDataRow> reader, bool hasBackOffice, Dictionary <string, string> gatewayNameFields)
{
insertCommand.Parameters["@Day_Code"].Value = DayCode.ToDayCode(_requiredDay);
insertCommand.Parameters["@Downloaded_Date"].Value = DateTime.Now;
insertCommand.Parameters["@account_ID"].Value = _accountID;
}
protected override ActivityExecutionStatus Execute(ActivityExecutionContext executionContext)
{
if (Convert.ToBoolean(ParentWorkflow.InternalParameters["Monthly"]))
{
return(ActivityExecutionStatus.Closed);
}
DateTime reportDate = DateTime.Now.AddDays(-1);
if (ParentWorkflow.InternalParameters.ContainsKey("ReportDate"))
{
reportDate = Convert.ToDateTime(ParentWorkflow.InternalParameters["ReportDate"]);
}
//Execute the query to get the measured parameters for the OLTP for all accounts.
string sql = @"SELECT BO.Account_ID, Account_Name AccountName, sum(BOClicks) AS SumOfClicks,
SUM(new_leads) AS SumOfLeads, SUM(new_users) AS SumOfNewUsers,
SUM(new_active_users) AS SumOfNewActiveUsers, SUM(active_users) AS SumOfActiveUsers,
SUM(new_net_deposits_in_dollars) SumOfNewNetDeposits, SUM(total_net_deposits_in_dollars) SumOfTotalNetDeposits,
SUM(clientspecific1) AS SumOfClientSpecific1, SUM(clientspecific2) AS SumOfClientSpecific2,
SUM(clientspecific3) AS SumOfClientSpecific3, SUM(clientspecific4) AS SumOfClientSpecific4,
SUM(clientspecific5) AS SumOfClientSpecific5
FROM easynet_oltp.dbo.BackOffice_Client_Gateway BO,easynet_oltp.dbo.User_GUI_Account ";
sql += "WHERE day_code = '" + DayCode.ToDayCode(reportDate) + "' ";
sql += "AND BO.account_id = User_GUI_Account.Account_ID GROUP BY Account_Name,BO.Account_id,day_code ORDER BY 2";
DataManager.ConnectionString = ParentWorkflow.Parameters["AdminConnectionString"].ToString();
DataManager.CommandTimeout = 0;
using (DataManager.Current.OpenConnection())
{
SqlCommand cmd = DataManager.CreateCommand(sql);
SqlDataReader dr = cmd.ExecuteReader();
//Loop on the results, and build a hash-table per account. We assume that each
//account only appears ONCE!.
Hashtable ht = new Hashtable();
while (dr.Read())
{
AccountAllMeasures aam = new AccountAllMeasures(dr, true);
ht.Add(aam.AccountID, aam);
}
dr.Close();
dr.Dispose();
if (!ParentWorkflow.InternalParameters.ContainsKey("BOResults"))
{
ParentWorkflow.InternalParameters.Add("BOResults", ht);
}
}
return(ActivityExecutionStatus.Closed);
}
// Required for working with SqlBulkCopy
private DataRow ConvertToDataRow(DeliveryFile file, PpcExampleRow row)
{
DataRow data = table.NewRow();
data["Day_Code"] = DayCode.ToDayCode(file.TargetDateTime);
data["AccountID"] = _delivery.AccountID;
data["Campaign_GK"] = GKManager.GetCampaignGK(_delivery.AccountID, row.CampaignName);
data["Adgroup_GK"] = GKManager.GetAdgroupGK(_delivery.AccountID, row.AdgroupName);
//etc.
}
protected override ActivityExecutionStatus Execute(ActivityExecutionContext executionContext)
{
DateTime reportDate = DateTime.Now.AddDays(-1);
if (ParentWorkflow.InternalParameters.ContainsKey("ReportDate"))
{
reportDate = Convert.ToDateTime(ParentWorkflow.InternalParameters["ReportDate"]);
}
//Execute the query to get the measured parameters for the OLTP for all accounts.
string sql = @"SELECT Account_Name AccountName,User_GUI_Account.account_id,
SUM(impressions) as SumOfImps,SUM(clicks) as SumOfClicks,
SUM(cost) SumOfCost,SUM(conv) SumOfConv,SUM(purchases) SumOfPurchase,
SUM(leads) SumOfleads,SUM(signups) SumOfSignups
FROM easynet_dwh.dbo.Dwh_Fact_PPC_Campaigns, easynet_OLTP.dbo.User_GUI_Account ";
if (Convert.ToBoolean(ParentWorkflow.InternalParameters["Monthly"]))
{
string time = reportDate.Year.ToString();
if (reportDate.Month.ToString().Length < 2)
{
time += "0" + reportDate.Month.ToString();
}
else
{
time += reportDate.Month.ToString();
}
sql += "WHERE left(day_code,6) = '" + time + "' ";
}
else
{
sql += "WHERE day_code = '" + DayCode.ToDayCode(reportDate) + "' ";
}
sql += "AND Dwh_Fact_PPC_Campaigns.account_id = User_GUI_Account.account_id " +
"AND channel_id = 1 " +
"GROUP BY Account_Name,User_GUI_Account.account_id,channel_id " +
"ORDER BY 1,2";
DataManager.ConnectionString = ParentWorkflow.Parameters["AdminConnectionString"].ToString();
DataManager.CommandTimeout = 0;
using (DataManager.Current.OpenConnection())
{
SqlCommand cmd = DataManager.CreateCommand(sql);
SqlDataReader dr = cmd.ExecuteReader();
dr.Close();
dr.Dispose();
}
return(ActivityExecutionStatus.Closed);
}
private void makeRankTable(ref DataTable dtRank)
{
String se = Request.QueryString["se"];
String accntID = Request.QueryString["accntID"];
String kw = Server.UrlDecode(Request.QueryString["kw"]);
String profID = Request.QueryString["profID"];
_keyword = kw;
_searchEngine = se;
using (DataManager.Current.OpenConnection())
{
SqlCommand dateCmd = DataManager.CreateCommand("select distinct Day_Code from Rankings_Data where Account_ID = @accountID:Int and ProfileID = @profileID:Int order by day_code desc");
dateCmd.Parameters["@accountID"].Value = Int32.Parse(accntID);
dateCmd.Parameters["@profileID"].Value = Int32.Parse(profID);
using (SqlDataReader reader = dateCmd.ExecuteReader())
{
while (reader.Read())
{
_datesKwByTime.Add(Int32.Parse(reader[0].ToString()));
}
}
}
SqlCommand resultsCmd = DataManager.CreateCommand(@"RankingSingleGraphData(@profileID:Int, @fromDate:Int, @toDate:Int, @Se:nvarchar, @Kw:nvarchar)", CommandType.StoredProcedure);
resultsCmd.Parameters["@profileID"].Value = Int32.Parse(profID);
resultsCmd.Parameters["@toDate"].Value = _datesKwByTime[0];
if (_datesKwByTime[0] - 600 > _datesKwByTime[_datesKwByTime.Count - 1])
{
//past a half a year
resultsCmd.Parameters["@fromDate"].Value = DayCode.ToDayCode(DayCode.GenerateDateTime(_datesKwByTime[0]).AddMonths(-6));
_fromDate = DayCode.ToDayCode(DayCode.GenerateDateTime(_datesKwByTime[0]).AddMonths(-6));
}
else
{
resultsCmd.Parameters["@fromDate"].Value = _datesKwByTime[_datesKwByTime.Count - 1];
}
resultsCmd.Parameters["@Se"].Value = se;
resultsCmd.Parameters["@Kw"].Value = kw;
SqlDataAdapter adpater = new SqlDataAdapter(resultsCmd);
using (DataManager.Current.OpenConnection())
{
DataManager.Current.AssociateCommands(resultsCmd);
adpater.Fill(dtRank);
}
}
void RetrieveData(string sortingExpression, bool useSession, AlertType alertType)
{
DataView totalsView = useSession ? Session["TotalsView"] as DataView : null;
DataView summaryView = useSession ? Session["SummaryView"] as DataView : null;
//=========================
// RUN THE COMMANDS
if (totalsView == null)
{
DataSet dataSet = new DataSet();
DataTable totals = new DataTable();
DataTable summaryTable = new DataTable();
summaryTable.Columns.Add("Summary");
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Easynet.Edge.UI.Data.Properties.Settings.Edge2AlertsConnectionString"].ConnectionString))
{
// ............................
// SP_Alerts_GetAlertData
SqlCommand command = DataManager.CreateCommand("SP_Alerts_GetAlertData(@AlertDate:Char, @accountID:Char, @WFType:Char)", CommandType.StoredProcedure);
command.Parameters["@AlertDate"].Value = DayCode.ToDayCode(SessionInputs.FromDate);
command.Parameters["@accountID"].Value = this.AccountID;
command.Parameters["@WFType"].Value = SessionInputs.FlowID;
command.Connection = connection;
SqlDataAdapter adapter = new SqlDataAdapter(command);
connection.Open();
DataSet tempDataSet = new DataSet();
adapter.Fill(tempDataSet);
if (tempDataSet.Tables.Count > 0)
{
totals = tempDataSet.Tables[0];
if (totals.Rows.Count > 0 && _alertTypeSelector.Items[0].Selected == true)
{
FillSummaryDataGrid(totals, ref summaryTable, AlertType.Campaign);
if (summaryTable.Rows.Count > 0)
{
DataRow newDataRow = summaryTable.NewRow();
newDataRow[0] = "Campaigns:";
summaryTable.Rows.InsertAt(newDataRow, 0);
}
}
}
// ............................
// SP_Alerts_GetAlertDataAdGroups
command.CommandText = "SP_Alerts_GetAlertDataAdGroups";
adapter.Fill(dataSet);
}
DataTable adGroupsResult = dataSet.Tables.Count > 0 ? dataSet.Tables[0] : new DataTable();
DataTable gatewaysResult = dataSet.Tables.Count > 1 ? dataSet.Tables[1] : new DataTable();
DataTable keywordResult = dataSet.Tables.Count > 3 ? dataSet.Tables[3] : new DataTable();
DataTable adtextsResult = dataSet.Tables.Count > 2 ? dataSet.Tables[2] : new DataTable();
FillSummaryOrSomethingLikeThat(adGroupsResult, summaryTable, 1, AlertType.Adgroup, "Ad Groups:");
FillSummaryOrSomethingLikeThat(keywordResult, summaryTable, 2, AlertType.Keyword, "Keywords:");
FillSummaryOrSomethingLikeThat(adtextsResult, summaryTable, 3, AlertType.Text, "AdText:");
FillSummaryOrSomethingLikeThat(gatewaysResult, summaryTable, 4, AlertType.Gateway, "Gateways:");
//Rename headers for campaigns - [sorta-refactored with guesswork by Doron]
RenameColumnHeaders(totals, 1, 5);
RenameColumnHeaders(adGroupsResult, 2, 7);
RenameColumnHeaders(keywordResult, 5, 9);
RenameColumnHeaders(adtextsResult, 5, 9);
RenameColumnHeaders(gatewaysResult, 5, 9);
RemoveCols(totals, new string[] { "MeasurecolumnNames", "measure_id" });
RemoveCols(adGroupsResult, new string[] { "MeasurecolumnNames", "measure_id", "group_gk" });
RemoveCols(keywordResult, new string[] { "MeasurecolumnNames", "measure_id", "group_gk", "Keyword_gk" });
RemoveCols(adtextsResult, new string[] { "MeasurecolumnNames", "measure_id", "group_gk", "Adtext_gk" });
RemoveCols(gatewaysResult, new string[] { "MeasurecolumnNames", "measure_id", "group_gk", "Gateway_id" });
totalsView = new DataView(totals);
Session["TotalsView"] = totalsView;
summaryView = new DataView(summaryTable);
Session["SummaryView"] = summaryTable;
}
//=========================
// apply sorting
if (sortingExpression != null)
{
totalsView.Sort = sortingExpression + " desc";
}
// Bind view
_dataGrid.DataSource = totalsView;
_dataGrid.DataBind();
_summaryGrid.DataSource = summaryView;
_summaryGrid.DataBind();
}
protected override ActivityExecutionStatus Execute(ActivityExecutionContext executionContext)
{
DateTime reportDate = DateTime.Now.AddDays(-1);
if (ParentWorkflow.InternalParameters.ContainsKey("ReportDate"))
{
reportDate = Convert.ToDateTime(ParentWorkflow.InternalParameters["ReportDate"]);
}
//Execute the query to get the measured parameters for the OLTP for all accounts.
string sql = @"SELECT Account_Name AccountName,Paid_API_AllColumns.account_id,
SUM(imps) as SumOfImps,SUM(clicks) as SumOfClicks,Avg(cpc) AVGCPC,
SUM(cost) SumOfCost,Avg(pos) AvgPos,SUM(conv) SumOfConv,SUM(purchases) SumOfPurchase,
SUM(leads) SumOfleads,SUM(signups) SumOfSignups
FROM easynet_OLTP.dbo.Paid_API_AllColumns,easynet_OLTP.dbo.User_GUI_Account ";
if (Convert.ToBoolean(ParentWorkflow.InternalParameters["Monthly"]))
{
string time = reportDate.Year.ToString();
if (reportDate.Month.ToString().Length < 2)
{
time += "0" + reportDate.Month.ToString();
}
else
{
time += reportDate.Month.ToString();
}
sql += "WHERE left(day_code,6) = '" + time + "' ";
}
else
{
sql += "WHERE day_code = '" + DayCode.ToDayCode(reportDate) + "' ";
}
sql += "AND Paid_API_AllColumns.account_id= User_GUI_Account.Account_ID " +
"AND channel_id = 1 " +
"GROUP BY Account_Name,Paid_API_AllColumns.account_id " +
"ORDER BY 1";
DataManager.ConnectionString = ParentWorkflow.Parameters["AdminConnectionString"].ToString();
DataManager.CommandTimeout = 0;
using (DataManager.Current.OpenConnection())
{
SqlCommand cmd = DataManager.CreateCommand(sql);
SqlDataReader dr = cmd.ExecuteReader();
//Loop on the results, and build a hash-table per account. We assume that each
//account only appears ONCE!.
Hashtable ht = new Hashtable();
while (dr.Read())
{
AccountAllMeasures aam = new AccountAllMeasures(dr);
ht.Add(aam.AccountID, aam);
}
dr.Close();
dr.Dispose();
if (!ParentWorkflow.InternalParameters.ContainsKey("OLTPResults"))
{
ParentWorkflow.InternalParameters.Add("OLTPResults", ht);
}
}
return(ActivityExecutionStatus.Closed);
}
protected virtual void WriteResult(MeasuredParameter mp, AlertMeasure am)
{
DataManager.ConnectionString = AppSettings.GetAbsolute("Easynet.Edge.Core.Workflow.AlertConnectionString");
//Create the command.
using (DataManager.Current.OpenConnection())
{
AlertMeasures measures = (AlertMeasures)ParentWorkflow.InternalParameters["AlertMeasures"];
string sql = "INSERT INTO AlertResults ";
string columns = "(wf_id,wf_date,wf_parameters,wf_conditionValues,entity_type,Account_id,Channel_id,Account_name,Current_Day,Compare_Day,measure_id,measure_current_value,measure_compare_value,measure_change_ratio";
columns += GetColumns(mp);
sql += columns + " VALUES(";
int wfID = ParentWorkflow.WorkflowID;
if (wfID <= 0)
{
if (ParentWorkflow.Parameters.ContainsKey("WorkflowID"))
{
wfID = Convert.ToInt32(ParentWorkflow.Parameters["WorkflowID"]);
}
}
string values = String.Empty;
values = wfID.ToString() + ",'" + DateTime.Now.ToString("yyyyMMdd HH:mm:ss") + "','" + ParentWorkflow.GetParametersAsString() + "','" + ParentWorkflow.GetConditionValuesAsString() + "'," + Convert.ToInt32(_entityType).ToString() + ",";
values += mp.AccountID.ToString() + "," + mp.ChannelID.ToString() + ",'" + mp.AccountName + "'," + DayCode.ToDayCode(mp.CurrentDay).ToString() + "," + DayCode.ToDayCode(mp.CompareDate).ToString() + ",";
values += mp.GetMeasureParameterSQL(am, measures) + ")";
sql += values;
SqlCommand alertResults = DataManager.CreateCommand(sql);
try
{
alertResults.ExecuteNonQuery();
}
catch (Exception ex)
{
Log.Write("Failed to write alert result to database.", ex);
throw ex;
}
}
}
public void Generate(string templateFileName, DateTime date)
{
if (templateFileName == String.Empty ||
templateFileName == null)
{
throw new ArgumentException("Invalid template file name. Cannot be null or empty");
}
if (!File.Exists(templateFileName))
{
throw new FileNotFoundException("Could not find templat efile: " + templateFileName);
}
if (_source == null ||
_compare == null)
{
throw new Exception("Invalid source or compare data. Cannot be null.");
}
string reportFilePath = Path.GetDirectoryName(templateFileName);
if (!reportFilePath.EndsWith("\\"))
{
reportFilePath += "\\";
}
string reportFileName = "AdminAlert_" + DayCode.ToDayCode(date).ToString() + ".xlsx";
reportFileName = reportFilePath + reportFileName;
FileInfo newFile = new FileInfo(reportFileName);
FileInfo template = new FileInfo(templateFileName);
using (ExcelPackage ep = new ExcelPackage(newFile, template))
{
ExcelWorkbook ew = ep.Workbook;
ExcelWorksheet worksheet = ew.Worksheets["AdWords"];
//Start inserting rows from the begining.
int row = ADMIN_START_ROW;
IDictionaryEnumerator ide = _source.GetEnumerator();
while (ide.MoveNext())
{
AccountAllMeasures csv = (AccountAllMeasures)ide.Value;
//First the CSV Row.
worksheet.Cell(row, 1).Value = "Adwords - " + csv.AccountName;
worksheet.Cell(row, 2).Value = csv.ImpsCurrent.ToString();
worksheet.Cell(row, 3).Value = csv.ClicksCurrent.ToString();
worksheet.Cell(row, 4).Value = csv.CPCCurrent.ToString();
worksheet.Cell(row, 5).Value = csv.CostCurrent.ToString();
worksheet.Cell(row, 6).Value = csv.AveragePosition.ToString();
worksheet.Cell(row, 7).Value = csv.ConversionsCurrent.ToString();
worksheet.Cell(row, 8).Value = csv.PurchasesCurrent.ToString();
worksheet.Cell(row, 9).Value = csv.LeadsCurrent.ToString();
worksheet.Cell(row, 10).Value = csv.SignupsCurrent.ToString();
//Now - OLTP
int id = -1;
try
{
id = AccountAllMeasures.FromAccountName(csv.AccountName);
}
catch (Exception ex)
{
ex.ToString();
}
if (_compare.ContainsKey(id))
{
AccountAllMeasures oltp = (AccountAllMeasures)_compare[id];
worksheet.Cell(row + 1, 1).Value = "OLTP - " + oltp.AccountName;
worksheet.Cell(row + 1, 2).Value = oltp.ImpsCurrent.ToString();
worksheet.Cell(row + 1, 3).Value = oltp.ClicksCurrent.ToString();
worksheet.Cell(row + 1, 4).Value = oltp.CPCCurrent.ToString();
worksheet.Cell(row + 1, 5).Value = oltp.CostCurrent.ToString();
worksheet.Cell(row + 1, 6).Value = oltp.AveragePosition.ToString();
worksheet.Cell(row + 1, 7).Value = oltp.ConversionsCurrent.ToString();
worksheet.Cell(row + 1, 8).Value = oltp.PurchasesCurrent.ToString();
worksheet.Cell(row + 1, 9).Value = oltp.LeadsCurrent.ToString();
worksheet.Cell(row + 1, 10).Value = oltp.SignupsCurrent.ToString();
if (csv.ImpsCurrent != oltp.ImpsCurrent)
{
worksheet.Cell(row + 1, 2).Style = "Bad";
}
if (csv.ClicksCurrent != oltp.ClicksCurrent)
{
worksheet.Cell(row + 1, 3).Style = "Bad";
}
if (csv.CPCCurrent != oltp.CPCCurrent)
{
worksheet.Cell(row + 1, 4).Style = "Bad";
}
if (csv.CostCurrent != oltp.CostCurrent)
{
worksheet.Cell(row + 1, 5).Style = "Bad";
}
if (csv.AveragePosition != oltp.AveragePosition)
{
worksheet.Cell(row + 1, 6).Style = "Bad";
}
if (csv.ConversionsCurrent != oltp.ConversionsCurrent)
{
worksheet.Cell(row + 1, 7).Style = "Bad";
}
if (csv.PurchasesCurrent != oltp.PurchasesCurrent)
{
worksheet.Cell(row + 1, 8).Style = "Bad";
}
if (csv.LeadsCurrent != oltp.LeadsCurrent)
{
worksheet.Cell(row + 1, 9).Style = "Bad";
}
if (csv.SignupsCurrent != oltp.SignupsCurrent)
{
worksheet.Cell(row + 1, 10).Style = "Bad";
}
}
else
{
//Couldn't find it. Put 0 in everyone, and mark them all as "Bad".
worksheet.Cell(row + 1, 1).Value = "OLTP - " + csv.AccountName;
worksheet.Cell(row + 1, 2).Value = "0";
worksheet.Cell(row + 1, 2).Style = "Bad";
worksheet.Cell(row + 1, 3).Value = "0";
worksheet.Cell(row + 1, 3).Style = "Bad";
worksheet.Cell(row + 1, 4).Value = "0";
worksheet.Cell(row + 1, 4).Style = "Bad";
worksheet.Cell(row + 1, 5).Value = "0";
worksheet.Cell(row + 1, 5).Style = "Bad";
worksheet.Cell(row + 1, 6).Value = "0";
worksheet.Cell(row + 1, 6).Style = "Bad";
worksheet.Cell(row + 1, 7).Value = "0";
worksheet.Cell(row + 1, 7).Style = "Bad";
worksheet.Cell(row + 1, 8).Value = "0";
worksheet.Cell(row + 1, 8).Style = "Bad";
worksheet.Cell(row + 1, 9).Value = "0";
worksheet.Cell(row + 1, 9).Style = "Bad";
worksheet.Cell(row + 1, 10).Value = "0";
worksheet.Cell(row + 1, 10).Style = "Bad";
}
//Now panorama
string accountName = AccountAllMeasures.FromGoogleAccountName(csv.AccountName);
if (_bi != null && _bi.ContainsKey(accountName))
{
AccountAllMeasures panorama = (AccountAllMeasures)_bi[accountName];
worksheet.Cell(row + 2, 1).Value = "PANORAMA - " + panorama.AccountName;
worksheet.Cell(row + 2, 2).Value = panorama.ImpsCurrent.ToString();
worksheet.Cell(row + 2, 3).Value = panorama.ClicksCurrent.ToString();
worksheet.Cell(row + 2, 4).Value = panorama.CPCCurrent.ToString();
worksheet.Cell(row + 2, 5).Value = panorama.CostCurrent.ToString();
worksheet.Cell(row + 2, 6).Value = panorama.AveragePosition.ToString();
worksheet.Cell(row + 2, 7).Value = panorama.ConversionsCurrent.ToString();
worksheet.Cell(row + 2, 8).Value = panorama.PurchasesCurrent.ToString();
worksheet.Cell(row + 2, 9).Value = panorama.LeadsCurrent.ToString();
worksheet.Cell(row + 2, 10).Value = panorama.SignupsCurrent.ToString();
if (csv.ImpsCurrent != panorama.ImpsCurrent)
{
worksheet.Cell(row + 1, 2).Style = "Bad";
}
if (csv.ClicksCurrent != panorama.ClicksCurrent)
{
worksheet.Cell(row + 1, 3).Style = "Bad";
}
if (csv.CPCCurrent != panorama.CPCCurrent)
{
worksheet.Cell(row + 1, 4).Style = "Bad";
}
if (csv.CostCurrent != panorama.CostCurrent)
{
worksheet.Cell(row + 1, 5).Style = "Bad";
}
if (csv.AveragePosition != panorama.AveragePosition)
{
worksheet.Cell(row + 1, 6).Style = "Bad";
}
if (csv.ConversionsCurrent != panorama.ConversionsCurrent)
{
worksheet.Cell(row + 1, 7).Style = "Bad";
}
if (csv.PurchasesCurrent != panorama.PurchasesCurrent)
{
worksheet.Cell(row + 1, 8).Style = "Bad";
}
if (csv.LeadsCurrent != panorama.LeadsCurrent)
{
worksheet.Cell(row + 1, 9).Style = "Bad";
}
if (csv.SignupsCurrent != panorama.SignupsCurrent)
{
worksheet.Cell(row + 1, 10).Style = "Bad";
}
}
else
{
//Couldn't find it. Put 0 in everyone, and mark them all as "Bad".
worksheet.Cell(row + 2, 1).Value = "PANORAMA - " + csv.AccountName;
worksheet.Cell(row + 2, 2).Value = "0";
worksheet.Cell(row + 2, 2).Style = "Bad";
worksheet.Cell(row + 2, 3).Value = "0";
worksheet.Cell(row + 2, 3).Style = "Bad";
worksheet.Cell(row + 2, 4).Value = "0";
worksheet.Cell(row + 2, 4).Style = "Bad";
worksheet.Cell(row + 2, 5).Value = "0";
worksheet.Cell(row + 2, 5).Style = "Bad";
worksheet.Cell(row + 2, 6).Value = "0";
worksheet.Cell(row + 2, 6).Style = "Bad";
worksheet.Cell(row + 2, 7).Value = "0";
worksheet.Cell(row + 2, 7).Style = "Bad";
worksheet.Cell(row + 2, 8).Value = "0";
worksheet.Cell(row + 2, 8).Style = "Bad";
worksheet.Cell(row + 2, 9).Value = "0";
worksheet.Cell(row + 2, 9).Style = "Bad";
worksheet.Cell(row + 2, 10).Value = "0";
worksheet.Cell(row + 2, 10).Style = "Bad";
}
//Advance 3 rows.
row += 3;
}
worksheet.Cell(ADMIN_START_ROW + 1, 16).Value = DateTime.Now.ToString("dd/MM/yyyy HH:mm:ss");
worksheet.Cell(ADMIN_START_ROW + 2, 16).Value = date.ToString("dd/MM/yyyy HH:mm:ss");
ExcelWorksheet boHistory = ew.Worksheets["BOHistory"];
ExcelWorksheet bo_ef = ew.Worksheets["BO_EF"];
if (_bo != null)
{
//Add the BO sheet.
int boRow = ADMIN_START_ROW;
IDictionaryEnumerator boe = _bo.GetEnumerator();
if (boHistory != null)
{
while (boe.MoveNext())
{
AccountAllMeasures boData = (AccountAllMeasures)boe.Value;
boHistory.Cell(boRow, 1).Value = boData.AccountName;
boHistory.Cell(boRow, 2).Value = boData.ClicksCurrent.ToString();
boHistory.Cell(boRow, 3).Value = boData.LeadsCurrent.ToString();
boHistory.Cell(boRow, 4).Value = boData.BONewUsersCurrent.ToString();
boHistory.Cell(boRow, 5).Value = boData.BONewActivationsCurrent.ToString();
boHistory.Cell(boRow, 6).Value = boData.SumOfActiveUsers.ToString();
boHistory.Cell(boRow, 7).Value = boData.SumOfNewNetDeposits.ToString();
boHistory.Cell(boRow, 8).Value = boData.SumOfTotalNetDeposits.ToString();
boHistory.Cell(boRow, 9).Value = boData.SumOfClientSpecific1.ToString();
boHistory.Cell(boRow, 10).Value = boData.SumOfClientSpecific2.ToString();
boHistory.Cell(boRow, 11).Value = boData.SumOfClientSpecific3.ToString();
boHistory.Cell(boRow, 12).Value = boData.SumOfClientSpecific4.ToString();
boHistory.Cell(boRow, 13).Value = boData.SumOfClientSpecific5.ToString();
boRow++;
}
}
boe.Reset();
boRow = ADMIN_START_ROW;
if (bo_ef != null)
{
while (boe.MoveNext())
{
AccountAllMeasures boef = (AccountAllMeasures)boe.Value;
//Hack for easy forex only!
if (boef.AccountID == 7)
{
bo_ef.Cell(boRow, 2).Value = boef.LeadsCurrent.ToString();
bo_ef.Cell(boRow, 3).Value = boef.BONewUsersCurrent.ToString();
bo_ef.Cell(boRow, 4).Value = boef.BONewActivationsCurrent.ToString();
bo_ef.Cell(boRow, 5).Value = boef.SumOfActiveUsers.ToString();
bo_ef.Cell(boRow, 6).Value = boef.SumOfNewNetDeposits.ToString();
bo_ef.Cell(boRow, 7).Value = boef.SumOfTotalNetDeposits.ToString();
}
}
}
}
else
{
boHistory.Cell(100, 1).Value = "1";
bo_ef.Cell(100, 1).Value = "1";
}
ep.Save();
_generatedFileName = reportFileName;
}
}
void _submit2_Click(object sender, EventArgs e)
{
if (fileUpload.PostedFile.FileName == "")// empty filename
{
}
else
{
try
{
//if 'BO' or 'Bing' and one file already selected
if ((_sourceSelector.Text.Equals("BO") || _sourceSelector.Text.Equals("Bing")) &&
listboxFiles.Items.Count > 0)
{
//replace the current file
EraseFiles();
}
string newFileName = string.Empty;
System.Xml.XmlNode myXML = (System.Xml.XmlNode)ConfigurationManager.GetSection("convertAccounts");
System.Xml.XmlNode SaveFilePathNode = myXML.SelectSingleNode(@"SaveFilePath");
string path = SaveFilePathNode.InnerText;
if (listboxFiles.Items.Count == 0)
{
listOfFilesToUpload = new Dictionary <string, string>();
Session["listOfFilesToUpload"] = listOfFilesToUpload;
}
{
newFileName = fileUpload.FileName;
ListItem item = listboxFiles.Items.FindByText(newFileName);
if (item != null)//this file already exist in listbox
{
//log or something
}
else
{
if (Path.GetExtension(fileUpload.PostedFile.FileName).ToLower().Equals(".tsv") && !_sourceSelector.Text.ToLower().Equals("yahoo"))//copy to '.csv'
{
newFileName = Path.ChangeExtension(path + newFileName, "csv");
}
else
{
newFileName = path + newFileName;
}
string originalFileName = fileUpload.PostedFile.FileName;
listOfFilesToUpload.Add(originalFileName, newFileName);
try
{
fileUpload.PostedFile.SaveAs(path + fileUpload.FileName);
}
catch (Exception ex)
{
saveFilePathTextBox.Text = "FGGG :" + ex.Message;
}
listboxFiles.Items.Add(originalFileName);//fileUpload.PostedFile.FileName);
try
{
if (Path.GetExtension(originalFileName) != Path.GetExtension(newFileName))//copy to '.csv'
{
System.IO.File.Copy(Path.ChangeExtension(newFileName, "tsv"), newFileName, true);
}
}
catch (Exception ex)
{
saveFilePathTextBox.Text = "x :" + ex.Message + ". " + Path.ChangeExtension(newFileName, "tsv") + " ," + newFileName;
}
int count = listboxFiles.Items.Count;
if (count > 0)
{
string outputFileName = listboxFiles.Items[count - 1].Text;
string name = Path.GetFileName(outputFileName);
string exten = Path.GetExtension(name);
//saveFilePathTextBox.Text = name.Remove(name.Length - exten.Length, exten.Length);
saveFilePathTextBox.Text = Request.QueryString["accountID"] + "_" + name.Remove(name.Length - exten.Length, exten.Length) + DayCode.ToDayCode(DateTime.Today) + "_" + DateTime.Now.TimeOfDay.Hours + "_" + DateTime.Now.TimeOfDay.Minutes + "_" + DateTime.Now.TimeOfDay.Seconds;
}
}
}
}
catch (Exception ex)
{
saveFilePathTextBox.Text = "EX :" + ex.Message;
}
_submit.Enabled = true;
}
}