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;
}
private void AssignBirthDateAndSex(IndexedTokens tokens)
{
BirthDate = CalculateBirthdate(tokens);
Year = new YearCode(BirthDate.Value).ToString();
Month = new MonthCode(BirthDate.Value).ToString();
Day = new DayCode(BirthDate.Value, Sex).ToString();
}
public AlertSessionInputs()
{
string initialDate = AppSettings.Get(typeof(AlertsPage), "InitialDate", false);
if (initialDate != null)
{
FromDate = DayCode.GenerateDateTime(initialDate);
}
}
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);
}
public override bool Equals(object o)
{
AdminFlightDay other = o as AdminFlightDay;
if (other == null)
{
return(false);
}
return(DayCode.Equals(other.DayCode) && DayMask.Equals(other.DayMask));
}
// 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.
}
internal ControlCode(
LastNameCode lastNameCode,
FirstNameCode firstNameCode,
YearCode yearCode,
MonthCode monthCode,
DayCode dayCode,
AreaCode areaCode)
{
partialCode = string.Concat(lastNameCode, firstNameCode, yearCode, monthCode, dayCode, areaCode);
}
public ControlCode(
LastNameCode lastNameCode,
FirstNameCode firstNameCode,
YearCode yearCode,
MonthCode monthCode,
DayCode dayCode,
AreaCode areaCode)
{
partialCode = string.Concat(lastNameCode, firstNameCode, yearCode, monthCode, dayCode, areaCode);
}
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);
}
// original ReadFile function
protected virtual void ReadFile(string xmlPath, string defaultErrorSubDirPath, bool hasBackOffice, ref bool xmlFileEmpty, FieldElementSection rawDataFields, FieldElementSection metaDataFields, SqlCommand insertCommand, Dictionary <string, string> gatewayNameFields)
{
bool firstRowInXml = true;
using (XmlTextReader xmlReader = new XmlTextReader(xmlPath))
{
xmlReader.WhitespaceHandling = WhitespaceHandling.None;
// Read root node
xmlReader.Read();
xmlFileEmpty = true;
// Loop on all the rows in the xml report file.
while (xmlReader.Read())
{
if (!GetToRowsSection(xmlReader))
{
continue;
}
// Initalize dayCode.
if (firstRowInXml)
{
xmlFileEmpty = false;
firstRowInXml = false;
// ASAF: raise log error if we have diffrent date in the file and in _requiredDay.
_requiredDay = DayCode.GenerateDateTime(GetDate(xmlReader));
if (_accountID == SystemAccountID)
{
GetAccountID(xmlReader.GetAttribute("acctname").ToString(), out _accountID, _accountFieldName);
_accountName = xmlReader.GetAttribute("acctname").ToString();
}
// Delete old data in the DB for the same data we fetch the new data.
DataIdentifier dataIdentifier = new DataIdentifier(_accountID, GetDayCode(_requiredDay), _channelID);
_dataIdentifiers.Add(dataIdentifier);
HandleDeleteDay();
InitalizeNullValues(insertCommand, rawDataFields, metaDataFields);
}
CheckRowData(xmlReader);
try
{
HandleRow(hasBackOffice, rawDataFields, metaDataFields, insertCommand, gatewayNameFields, xmlReader);
}
catch (Exception ex)
{
WriteErrorMesageToFile(xmlPath, xmlReader.ReadOuterXml(), ex, defaultErrorSubDirPath);
}
}
}
}
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);
}
}
protected virtual void InitializeTimes(ref SqlCommand command)
{
switch (_alertType)
{
case AlertType.Daily:
{
DateTime cur = DayCode.GenerateDateTime(ParentWorkflow.Parameters["CurrentDayCode"]);
DateTime comp = DayCode.GenerateDateTime(ParentWorkflow.Parameters["CompareDayCode"]);
int timeDiff = 0;
if (ParentWorkflow.Parameters.ContainsKey("TimeDifference"))
{
timeDiff = Convert.ToInt32(ParentWorkflow.Parameters["TimeDifference"]);
}
//format the current and compare dates.
string curDate = cur.ToString("yyyyMMdd");
string compDate = GetCompareDate(timeDiff, cur, comp);
command.Parameters["@CurrentDayCode"].Value = curDate;
if (_timeMeasurement == TimeMeasurement.Relative)
{
command.Parameters["@CompareDayCode"].Value = compDate;
}
else
{
command.Parameters["@CompareDayCode"].Value = curDate;
}
break;
}
case AlertType.Period:
{
DateTime curStart = DayCode.GenerateDateTime(ParentWorkflow.Parameters["CurrentStartDayCode"]);
DateTime curEnd = DayCode.GenerateDateTime(ParentWorkflow.Parameters["CurrentEndDayCode"]);
DateTime compareStart = DayCode.GenerateDateTime(ParentWorkflow.Parameters["CompareStartDayCode"]);
DateTime compareEnd = DayCode.GenerateDateTime(ParentWorkflow.Parameters["CompareEndDayCode"]);
command.Parameters["@CurrentStartDayCode"].Value = curStart.ToString("yyyyMMdd");
command.Parameters["@CurrentEndDayCode"].Value = curEnd.ToString("yyyyMMdd");
command.Parameters["@CompareStartDayCode"].Value = compareStart.ToString("yyyyMMdd");
command.Parameters["@CompareEndDayCode"].Value = compareEnd.ToString("yyyyMMdd");
break;
}
}
}
public FiscalCode Calculate(NaturalPerson naturalPerson, AreaCode areaCode)
{
var lastName = new LastNameCode(naturalPerson.LastName.Replace(" ", null));
var firstName = new FirstNameCode(naturalPerson.FirstName.Replace(" ", null));
var year = new YearCode(naturalPerson.BirthDate);
var month = new MonthCode(naturalPerson.BirthDate);
var day = new DayCode(naturalPerson.BirthDate, naturalPerson.Sex);
var control = new ControlCode(lastName, firstName, year, month, day, areaCode);
var fiscalCode = new FiscalCode(lastName, firstName, year, month, day, areaCode, control);
return(fiscalCode);
}
/*=========================*/
#endregion
#region Private Methods
/*=========================*/
private void RunProcess(string xmlPath, string defaultErrorSubDirPath)
{
Log.Write(string.Format("Run processor for service {2}, for account {0}, for date {1}.", _accountID.ToString(), _requiredDay.ToShortDateString(), Instance.ParentInstance.Configuration.Name), LogMessageType.Information);
ProccessData(xmlPath, defaultErrorSubDirPath);
foreach (DataIdentifier dataIdentifier in _dataIdentifiers)
{
_requiredDay = DayCode.GenerateDateTime(dataIdentifier.DayCode);
_accountID = dataIdentifier.AccountID;
HandledContentSP();
}
if (!String.IsNullOrEmpty(ErrorFilePath))
{
Log.Write(string.Format("Error file {0} was created for date {1}.", ErrorFilePath, _requiredDay.ToShortDateString()), LogMessageType.Warning);
}
}
internal FiscalCode(
LastNameCode lastNameCode,
FirstNameCode firstNameCode,
YearCode yearCode,
MonthCode monthCode,
DayCode dayCode,
AreaCode areaCode,
ControlCode controlCode)
: this(
lastNameCode.ToString(),
firstNameCode.ToString(),
yearCode.ToString(),
monthCode.ToString(),
dayCode.ToString(),
areaCode.ToString(),
controlCode.ToString())
{
}
public FiscalCode(
LastNameCode lastNameCode,
FirstNameCode firstNameCode,
YearCode yearCode,
MonthCode monthCode,
DayCode dayCode,
AreaCode areaCode,
ControlCode controlCode)
: this(
lastNameCode.ToString(),
firstNameCode.ToString(),
yearCode.ToString(),
monthCode.ToString(),
dayCode.ToString(),
areaCode.ToString(),
controlCode.ToString())
{
}
public FiscalCode Calculate(NaturalPerson naturalPerson, AreaCode areaCode)
{
Enforce.Argument(() => areaCode);
var lastName = new LastNameCode(naturalPerson.LastName.Replace(" ", null));
var firstName = new FirstNameCode(naturalPerson.FirstName.Replace(" ", null));
var year = new YearCode(naturalPerson.BirthDate);
var month = new MonthCode(naturalPerson.BirthDate);
var day = new DayCode(naturalPerson.BirthDate, naturalPerson.Sex);
var control = new ControlCode(lastName, firstName, year, month, day, areaCode);
var fiscalCode = new FiscalCode(lastName, firstName, year, month, day, areaCode, control);
return fiscalCode;
}
protected virtual void CheckRowData(XmlTextReader xmlReader)
{
int accountID;
// New date in the file.
if (DayCode.GenerateDateTime(GetDate(xmlReader)) != _requiredDay)
{
_requiredDay = DayCode.GenerateDateTime(GetDate(xmlReader));
DataIdentifier dataIdentifier = new DataIdentifier(_accountID, GetDayCode(_requiredDay), _channelID);
if (!_dataIdentifiers.Contains(dataIdentifier))
{
// Delete old data in the DB for the same data we fetch the new data.
HandleDeleteDay();
_dataIdentifiers.Add(dataIdentifier);
}
}
if (!string.IsNullOrEmpty(_accountName))
{
// New account in the file.
if (xmlReader.GetAttribute("acctname").ToString() != _accountName)
{
GetAccountID(xmlReader.GetAttribute("acctname").ToString(), out accountID, _accountFieldName);
_accountID = accountID;
_accountName = xmlReader.GetAttribute("acctname").ToString();
DataIdentifier dataIdentifier = new DataIdentifier(_accountID, GetDayCode(_requiredDay), _channelID);
if (!_dataIdentifiers.Contains(dataIdentifier))
{
// Delete old data in the DB for the same data we fetch the new data.
HandleDeleteDay();
_dataIdentifiers.Add(dataIdentifier);
}
}
}
}
protected virtual void CheckRowData(SourceDataRowReader <RetrieverDataRow> reader)
{
int accountID;
// New date in the file.
if (DayCode.GenerateDateTime(GetDate(reader)) != _requiredDay)
{
_requiredDay = DayCode.GenerateDateTime(GetDate(reader));
DataIdentifier dataIdentifier = new DataIdentifier(_accountID, GetDayCode(_requiredDay), _channelID);
if (!_dataIdentifiers.Contains(dataIdentifier))
{
// Delete old data in the DB for the same data we fetch the new data.
HandleDeleteDay();
_dataIdentifiers.Add(dataIdentifier);
}
}
if (!string.IsNullOrEmpty(_accountName))
{
// New account in the file.
if (reader.CurrentRow.Fields["acctname"] != _accountName)
{
GetAccountID(reader.CurrentRow.Fields["acctname"], out accountID, _accountFieldName);
_accountID = accountID;
_accountName = reader.CurrentRow.Fields["acctname"];
DataIdentifier dataIdentifier = new DataIdentifier(_accountID, GetDayCode(_requiredDay), _channelID);
if (!_dataIdentifiers.Contains(dataIdentifier))
{
// Delete old data in the DB for the same data we fetch the new data.
HandleDeleteDay();
_dataIdentifiers.Add(dataIdentifier);
}
}
}
}
public override int GetHashCode()
{
unchecked
{
var hash = 17;
hash = hash * 29 + RequestId != null?RequestId.GetHashCode() : 0;
hash = hash * 29 + Timestamp.GetHashCode();
hash = hash * 29 + Last.GetHashCode();
hash = hash * 29 + LastSize.GetHashCode();
hash = hash * 29 + TotalVolume.GetHashCode();
hash = hash * 29 + Bid.GetHashCode();
hash = hash * 29 + Ask.GetHashCode();
hash = hash * 29 + TickId.GetHashCode();
hash = hash * 29 + BasisForLast.GetHashCode();
hash = hash * 29 + TradeMarketCenter.GetHashCode();
hash = hash * 29 + TradeConditions.GetHashCode();
hash = hash * 29 + TradeAggressor.GetHashCode();
hash = hash * 29 + DayCode.GetHashCode();
return(hash);
}
}
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;
}
}
protected void ZedGraphWeb1_RenderGraph(ZedGraph.Web.ZedGraphWeb webObject, Graphics g, MasterPane pane)
{
//Color[] colors = new Color[16] {Color.Silver, Color.Yellow, Color.DarkGreen, Color.Red, Color.Gray, Color.Purple, Color.LightYellow, Color.LightGreen, Color.LightGray, Color.LimeGreen, Color.Magenta, Color.Turquoise, Color.Maroon, Color.Linen, Color.MediumBlue, Color.Olive};
Color[] colors = new Color[22] {
ColorTranslator.FromHtml("#149406"), ColorTranslator.FromHtml("#ee5624"),
ColorTranslator.FromHtml("#0600ff"), ColorTranslator.FromHtml("#eff14a"), ColorTranslator.FromHtml("#eeab36"),
ColorTranslator.FromHtml("#7f6300"), ColorTranslator.FromHtml("#a40070"), ColorTranslator.FromHtml("#4ecb2c"),
ColorTranslator.FromHtml("#41a8ff"), ColorTranslator.FromHtml("#f975fb"), Color.Black, Color.Gray, Color.BurlyWood,
Color.Chartreuse, Color.CornflowerBlue, Color.Cyan, Color.DarkSalmon, Color.DimGray, Color.Fuchsia, Color.Khaki,
Color.Ivory, Color.LawnGreen
};
double[] ranks = new double[_datesKwByTime.Count];
GraphPane myPane = pane[0];
myPane.Border.Color = System.Drawing.Color.White;
int firstPos = _searchEngine.IndexOf("-");
myPane.Title.Text = "Ranks for '" + _keyword + "' keyword - " + _searchEngine.Substring(0, firstPos) + "(" + _searchEngine.Substring(firstPos + 2, _searchEngine.Length - (firstPos + 2)) + ")";
myPane.XAxis.Title.Text = "Date";
myPane.YAxis.Title.Text = "Rank";
//creating dates list
List <string> dates = new List <string>();
int datesCounter;
for (datesCounter = _datesKwByTime.Count - 1; datesCounter > 0; datesCounter--)
{
//checking half a year limitation
if (_fromDate == 0 || _datesKwByTime[datesCounter] <= _fromDate)
{
_datesKwByTime.Remove(_datesKwByTime[datesCounter]);
}
}
//make the same dates list in dd/mm/yyyy format
for (int i = _datesKwByTime.Count - 1; i >= 0; i--)
{
dates.Add(DayCode.GenerateDateTime(_datesKwByTime[i]).ToString("dd/MM/yyyy"));
}
//foreach (int day in _datesKwByTime)
//{
// dates.Add(DayCode.GenerateDateTime(day).ToString("dd/MM/yyyy"));
//}
//creating all the curves data
List <LineItem> curvesList = new List <LineItem>();
List <string> urlsList = new List <string>();
List <PointPairList> curvesListData = new List <PointPairList>();
PointPairList pointsList;
int rowsCounter = _dtRank.Rows.Count - 1;
datesCounter = _datesKwByTime.Count - 1;
int daycode = 0;
int rank = 0;
int prevRank = (int)_dtRank.Rows[rowsCounter]["Rank"] * -1;
while (rowsCounter >= 0 && _datesKwByTime.Count > 0)
{
pointsList = new PointPairList();
if (rowsCounter <= 0)
{
break;
}
while (_dtRank.Rows[rowsCounter]["Url"].ToString().Equals(_dtRank.Rows[rowsCounter - 1]["Url"].ToString()) &&
datesCounter >= 0)
{
//multiply by -1 for proper direction of the graph curve
daycode = (int)_dtRank.Rows[rowsCounter]["day_code"];
rank = (int)_dtRank.Rows[rowsCounter]["Rank"] * -1;
if ((double)daycode == (double)_datesKwByTime[datesCounter])
{
pointsList.Add((double)daycode, (double)rank);
prevRank = rank;
rowsCounter--;
}
//no data on that date so we'll put the previous rank data
else
{
pointsList.Add(Double.Parse(_datesKwByTime[datesCounter].ToString()), (double)prevRank);
}
//in case we reached the last row from db - we need to fill the same rank data to the rest of dates
//if (rowsCounter == 0 && datesCounter > 0)
//{
// while (datesCounter > 0)
// {
// pointsList.Add(Double.Parse(_datesKwByTime[datesCounter].ToString()), (double)rank);
// datesCounter--;
// }
// break;
//}
//else if(rowsCounter == 0 || datesCounter == 0)
//{
// break;
//}
if (rowsCounter == 0)
{
break;
}
datesCounter--;
}
daycode = (int)_dtRank.Rows[rowsCounter]["day_code"];
rank = (int)_dtRank.Rows[rowsCounter]["Rank"] * -1;
//for first row in db
for (int i = datesCounter - 1; i >= 0; i--)
{
if ((double)daycode == (double)_datesKwByTime[i])
{
pointsList.Add((double)daycode, (double)rank);
break;
}
else
{
pointsList.Add(Double.Parse(_datesKwByTime[i].ToString()), (double)prevRank);
}
}
//save url pointsList for the legend
try
{
urlsList.Add(Server.UrlDecode(_dtRank.Rows[rowsCounter + 1]["Url"].ToString()));
}
catch
{
urlsList.Add(Server.UrlDecode(_dtRank.Rows[rowsCounter]["Url"].ToString()));
}
curvesListData.Add(pointsList);
LineItem myCurve = null;
curvesList.Add(myCurve);
rowsCounter--;
datesCounter = _datesKwByTime.Count - 1;
}
List <double> dataList = new List <double>();
List <double> z = new List <double>();
//for(int i = 0; i < curvesListData[0].Count; i ++)
//{
// dataList.Add(curvesListData[0][i].Y);
// z.Add(curvesListData[0][i].X);
//}
double[] curveDataArray = new double[dataList.Count];
//curveDataArray = dataList.ToArray();
for (int curvesCounter = 0; curvesCounter < curvesList.Count; curvesCounter++)
{
dataList.Clear();
for (int i = 0; i < curvesListData[curvesCounter].Count; i++)
{
dataList.Add(curvesListData[curvesCounter][i].Y);
}
curveDataArray = dataList.ToArray();
curvesList[curvesCounter] = myPane.AddCurve(urlsList[curvesCounter], null, curveDataArray, colors[curvesCounter]);
curvesList[curvesCounter].Line.Width = 6;
curvesList[curvesCounter].Line.IsSmooth = true;
curvesList[curvesCounter].Line.StepType = StepType.RearwardStep;
curvesList[curvesCounter].Line.IsAntiAlias = true;
curvesList[curvesCounter].Link = new Link("graph.aspx", "#", "_blank");
curvesList[curvesCounter].Symbol.Fill = new Fill(colors[curvesCounter]);
//curvesList[curvesCounter].Symbol.Fill.Type = FillType.GradientByZ;
//curvesList[curvesCounter].Symbol.Fill.RangeMin = 0;
//curvesList[curvesCounter].Symbol.Fill.RangeMax = 30;
curvesList[curvesCounter].Symbol.Type = SymbolType.Circle;
}
foreach (CurveItem curve in myPane.CurveList)
{
int i = _datesKwByTime.Count;
string name = curve.Label.Text;
foreach (PointPair pt in curve.Points as PointPairList)
{
//pt.Tag = (pt.Y * -1).ToString() + " (" + DayCode.GenerateDateTime(pt.X).ToString("dd/MM/yyyy") + ")";
//pt.Tag = string.Format("{0} ({1:dd/MM/yyyy})", pt.Y * -1, DayCode.GenerateDateTime(pt.Y));
i--;
pt.Tag = string.Format("{0} ({1:dd/MM/yyyy})", pt.Y * -1, DayCode.GenerateDateTime(_datesKwByTime[i]));
}
}
string[] datesArray = dates.ToArray();
myPane.Border.IsVisible = true;
myPane.Legend.IsVisible = true;
myPane.Legend.FontSpec.Size = 8;
float r = myPane.Legend.Location.TopLeft.X;
myPane.Legend.Position = LegendPos.BottomFlushLeft;
myPane.YAxis.IsVisible = true;
myPane.YAxis.Scale.FontSpec.IsAntiAlias = true;
myPane.XAxis.IsVisible = true;
// Set the XAxis labels
myPane.XAxis.Scale.TextLabels = datesArray;
myPane.XAxis.MajorTic.IsBetweenLabels = true;
// Set the XAxis to Text type
myPane.XAxis.Type = AxisType.Text;
myPane.XAxis.Scale.FontSpec.IsAntiAlias = true;
myPane.XAxis.Scale.FontSpec.Size = 10;
myPane.XAxis.Scale.FontSpec.Angle = 45;
myPane.X2Axis.IsVisible = false;
myPane.YAxis.Scale.FontSpec.Size = 10;
double maxRank = checkMaxRank(ref myPane);
if (maxRank < -20)
{
myPane.YAxis.Scale.Min = maxRank - 10;
}
else
{
myPane.YAxis.Scale.Min = -20;
}
myPane.YAxis.Scale.Max = 0;
myPane.YAxis.Scale.MajorStepAuto = true;
pane.AxisChange(g);
}
public override int GetHashCode()
{
return(DayCode.GetHashCode() ^ DayMask.GetHashCode());
}
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 override ActivityExecutionStatus Execute(ActivityExecutionContext executionContext)
{
DataManager.ConnectionString = ParentWorkflow.Parameters["ConnectionString"].ToString();
//Run the stored procedure, based on the params we have.
try
{
int accountID = Convert.ToInt32(ParentWorkflow.Parameters["AccountID"]);
DateTime cur = DayCode.GenerateDateTime(ParentWorkflow.Parameters["CurrentDayCode"]);
DateTime comp = DayCode.GenerateDateTime(ParentWorkflow.Parameters["CompareDayCode"]);
int channelID = Convert.ToInt32(ParentWorkflow.Parameters["ChannelID"]);
int campaignGK = Convert.ToInt32(ParentWorkflow.Parameters["CampaignGK"]);
//Create the command.
using (DataManager.Current.OpenConnection())
{
SqlCommand clicks = DataManager.CreateCommand("SP_Alerts_SumOfClicksPerAccount", System.Data.CommandType.StoredProcedure);
clicks.Parameters.Add("@Account_id", System.Data.SqlDbType.NVarChar);
clicks.Parameters.Add("@CurrentDayCode", System.Data.SqlDbType.NVarChar);
clicks.Parameters.Add("@CompareDayCode", System.Data.SqlDbType.NVarChar);
clicks.Parameters.Add("@channel_id", System.Data.SqlDbType.NVarChar);
clicks.Parameters.Add("@Campaign_GK", System.Data.SqlDbType.NVarChar);
clicks.Parameters.Add("@RC", System.Data.SqlDbType.Int);
clicks.Parameters["@RC"].Direction = System.Data.ParameterDirection.ReturnValue;
clicks.Parameters["@Account_id"].Value = accountID.ToString();
clicks.Parameters["@channel_id"].Value = channelID.ToString();
clicks.Parameters["@Campaign_GK"].Value = campaignGK.ToString();
//format the current and compare dates.
string curDate = cur.ToString("yyyyMMdd");
string compDate = comp.ToString("yyyyMMdd");
clicks.Parameters["@CurrentDayCode"].Value = curDate;
clicks.Parameters["@CompareDayCode"].Value = compDate;
clicks.ExecuteNonQuery();
int d = Convert.ToInt32(clicks.Parameters["@RC"].Value);
ClicksDelta = (float)d;
CampaignAllMeasures cam = new CampaignAllMeasures();
cam.CampaignGK = campaignGK;
cam.ClicksChangeRatio = ClicksDelta;
cam.ChannelID = channelID;
cam.AccountID = accountID;
if (ParentWorkflow.InternalParameters.ContainsKey("MeasuredParams"))
{
MeasuredParameters mps = (MeasuredParameters)ParentWorkflow.InternalParameters["MeasuredParams"];
if (!mps.ContainsKey(cam.CampaignGK))
{
mps.Add(cam.CampaignGK, cam);
}
ParentWorkflow.InternalParameters["MeasuredParams"] = mps;
}
else
{
MeasuredParameters mps = new MeasuredParameters();
mps.Add(cam.CampaignGK, cam);
ParentWorkflow.InternalParameters.Add("MeasuredParams", mps);
}
}
}
catch (Exception ex)
{
throw ex;
}
//Put the result in the clicks property.
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;
}
}
}
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;
}
}
