/// <summary>
/// Initalize and calculate all relevent parmaeters for adwords creative including gk fields.
/// </summary>
/// <param name="insertCommand"></param>
/// <param name="xmlReader"></param>
/// <param name="retriveredDate">Required date.</param>
/// <param name="hasBackOffice">Indicate if the account have backoffice.</param>
/// <param name="gatewayNameFields"></param>
protected override void InitalizeMetaDataParameters(SqlCommand insertCommand,
SourceDataRowReader <RetrieverDataRow> reader, bool hasBackOffice, Dictionary <string, string> gatewayNameFields)
{
base.InitalizeMetaDataParameters(insertCommand, reader, hasBackOffice, gatewayNameFields);
InitalizeAdwordsParameters(insertCommand, reader, hasBackOffice, gatewayNameFields);
// If headline value is empty we initalize it with imgCreativeName value.
if (reader.CurrentRow.Fields.ContainsKey("headline") && string.IsNullOrEmpty(reader.CurrentRow.Fields["headline"]))
{
insertCommand.Parameters["@headline"].Value = ResolveString(reader.CurrentRow.Fields["imgCreativeName"]);
}
//insertCommand.Parameters["@maxCpc"].Value =
// ResolveDouble(reader.CurrentRow.Fields["maxCpc"]) / DivideFieldsValue;
if (reader.CurrentRow.Fields.ContainsKey("creativeType"))
{
InitalizeAdVariation(insertCommand, reader.CurrentRow.Fields["creativeType"]);
}
// Initalize Gateway ID
if (hasBackOffice)
{
InitalizeGatewayID(insertCommand, _channelID, gatewayNameFields);
}
InitalizeCreativeGKFields(insertCommand, _channelID, hasBackOffice, _accountID);
}
protected void HandleMatchType(SqlCommand insertCommand, SourceDataRowReader <RetrieverDataRow> reader)
{
// Initalize match type
string matchType;
if (reader.CurrentRow.Fields.ContainsKey("kwType"))
{
matchType = ResolveString(reader.CurrentRow.Fields["kwType"].ToString());
insertCommand.Parameters["@kwSite"].Value = ResolveString(reader.CurrentRow.Fields["keyword"]);
insertCommand.Parameters["@siteKwStatus"].Value = ResolveString(reader.CurrentRow.Fields["kwStatus"]);
}
else if (reader.CurrentRow.Fields.ContainsKey("kwSiteType"))
{
matchType = ResolveString(reader.CurrentRow.Fields["kwSiteType"]);
insertCommand.Parameters["@kwSite"].Value = ResolveString(reader.CurrentRow.Fields["kwSite"]);
insertCommand.Parameters["@siteKwStatus"].Value = ResolveString(reader.CurrentRow.Fields["siteKwStatus"]);
}
else if (insertCommand.Parameters["@MatchType"].Value.ToString() != DBNullValue.ToString()) // data from ppc importer
{
matchType = insertCommand.Parameters["@MatchType"].Value.ToString();
}
else
{
matchType = string.Empty;
}
InitalizeMatchType(insertCommand, matchType);
}
/// <summary>
/// Initalize and calculate all relevent parmaeters for adwords content including gk fields.
/// </summary>
/// <param name="insertCommand"></param>
/// <param name="xmlReader"></param>
/// <param name="hasBackOffice">Indicate if the account have backoffice.</param>
/// <param name="gatewayNameFields"></param>
protected override void InitalizeMetaDataParameters(SqlCommand insertCommand,
SourceDataRowReader <RetrieverDataRow> reader, bool hasBackOffice, Dictionary <string, string> gatewayNameFields)
{
base.InitalizeMetaDataParameters(insertCommand, reader, hasBackOffice, gatewayNameFields);
InitalizeAdwordsParameters(insertCommand, reader, hasBackOffice, gatewayNameFields);
InitalizeContentGKFields(insertCommand, _channelID, hasBackOffice, _accountID);
}
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 void InitalizeGatewayGK(SqlCommand insertCommand, SourceDataRowReader <RetrieverDataRow> reader, FieldElement fe)
{
if (fe.DBFieldName.ToLower() == "gateway_id")
{
insertCommand.Parameters["@Gateway_GK"].Value =
GkManager.GetGatewayGK(_accountID, Convert.ToInt64(reader.CurrentRow.Fields[fe.Value]));
}
}
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);
}
}
}
}
protected virtual void InitalizeAdwordsParameters(SqlCommand insertCommand,
SourceDataRowReader <RetrieverDataRow> reader, bool hasBackOffice, Dictionary <string, string> gatewayNameFields)
{
#region Obsolete - delete data by AggregationType.
//if (_aggregationType == AggregationType.Month)
// insertCommand.Parameters["@Day_Code"].Value = DayCodeSummary();
//else
// insertCommand.Parameters["@Day_Code"].Value = retriveredDate;
#endregion
insertCommand.Parameters["@Channel_ID"].Value = _channelID;
// Divide by 1000000 relevent Parameters.
insertCommand.Parameters["@ctr"].Value =
ResolveDouble(reader.CurrentRow.Fields["ctr"]) / DivideFieldsValue;
insertCommand.Parameters["@cpc"].Value =
ResolveDouble(reader.CurrentRow.Fields["cpc"]) / DivideFieldsValue;
insertCommand.Parameters["@cost"].Value =
ResolveDouble(reader.CurrentRow.Fields["cost"]) / DivideFieldsValue;
insertCommand.Parameters["@budget"].Value =
ResolveDouble(reader.CurrentRow.Fields["budget"]) / DivideFieldsValue;
//insertCommand.Parameters["@maxCpm"].Value =
// ResolveDouble(xmlReader.GetAttribute("maxCpm")) / DivideFieldsValue;
//insertCommand.Parameters["@maxContentCpc"].Value =
// ResolveDouble(xmlReader.GetAttribute("maxContentCpc")); // Yaniv : maybe need to divide this as well in 1000000
//insertCommand.Parameters["@cpm"].Value =
// ResolveDouble(xmlReader.GetAttribute("cpm")) / DivideFieldsValue;
// Fields to remove in the future.
insertCommand.Parameters["@customerid"].Value =
ResolveLong(reader.CurrentRow.Fields["campaignid"]);
insertCommand.Parameters["@Account_ID_SRC"].Value = _accountID;
insertCommand.Parameters["@date"].Value = DateTime.Now;
HandleMatchType(insertCommand, reader);
}
protected virtual void InitalizeGatewayGK(SqlCommand insertCommand, SourceDataRowReader <RetrieverDataRow> reader, FieldElement fe)
{
}
/// <summary>
/// Initalize the row raw data fields with values from the xml.
/// And initalize Meta Data fields with null values.
/// </summary>
/// <param name="rawDataFields">Dictionary that contain all the required raw data fields from the DB.</param>
/// <param name="metaDataFields">Dictionary that contain all the required Meta data fields from the DB.</param>
/// <param name="insertCommand"></param>
/// <param name="xmlReader"></param>
protected virtual void InitalizeRawDataFromXml(FieldElementSection rawDataFields, FieldElementSection metaDataFields, SqlCommand insertCommand, SourceDataRowReader <RetrieverDataRow> reader)
{
// Initalize the insertCommand with the values from the xmlReader.
foreach (FieldElement fe in rawDataFields.Fields)
{
if (!fe.InsertToDB || !fe.Enabled)
{
continue;
}
if (!String.IsNullOrEmpty(reader.CurrentRow.Fields[fe.Value]))
{
insertCommand.Parameters["@" + fe.DBFieldName].Value =
Convert.ChangeType(reader.CurrentRow.Fields[fe.Value], Type.GetType(_tableTypeMappings[fe.DBFieldType.ToLower()]));
InitalizeGatewayGK(insertCommand, reader, fe);
}
else
{
InsertNullValue(insertCommand, fe);
}
}
// Initalize Meta Data fields with null values.
foreach (FieldElement fe in metaDataFields.Fields)
{
if (!fe.InsertToDB || !fe.Enabled)
{
continue;
}
InsertNullValue(insertCommand, fe);
}
}
/// <summary>
/// Get the date of the row from the xml and return it in day code format.
/// </summary>
/// <param name="xmlReader"></param>
/// <returns>Date of the row day code format.</returns>
protected virtual int GetDate(SourceDataRowReader <RetrieverDataRow> reader)
{
int retriveredDate = ResolveInt(reader.CurrentRow.Fields["date"].Replace("-", string.Empty));
return(retriveredDate);
}
/// <summary>
/// Initalize row with raw data from the xml and meta
/// data (like gk fields) and insert the row to the DB.
/// </summary>
/// <param name="hasBackOffice">Indicate if the account have backoffice.</param>
/// <param name="rawDataFields">Dictionary that contain all the required raw data fields from the DB.</param>
/// <param name="metaDataFields">Dictionary that contain all the required Meta data fields from the DB.</param>
/// <param name="insertCommand"></param>
/// <param name="gatewayNameFields"></param>
/// <param name="xmlReader"></param>
/// <param name="retriveredDate">Required date.</param>
protected virtual void HandleRow(bool hasBackOffice, FieldElementSection rawDataFields, FieldElementSection metaDataFields, SqlCommand insertCommand, Dictionary <string, string> gatewayNameFields, SourceDataRowReader <RetrieverDataRow> reader)
{
InitalizeRawDataFromXml(rawDataFields, metaDataFields, insertCommand, reader);
InitalizeMetaDataParameters(insertCommand, reader, hasBackOffice, gatewayNameFields);
InsertRowToDB(insertCommand);
}
protected override void CheckRowData(SourceDataRowReader <RetrieverDataRow> reader)
{
}
protected override ServiceOutcome DoWork()
{
// go over each file
// get a list of domains to search for
// for every OrganicRankingsRow, check if matches domain
// insert to Rankings_Data
// The reader type is required for processing; if it is not available to GetType then an exception will
// trigger a service failure
Type organicReaderType = Type.GetType(Instance.Configuration.Options["ReaderType"], true);
// ----------------------------------------
// TODO: Delete old data if necessary
// ----------------------------------------
// Get the files list
List <RetrievedFile> files = new List <RetrievedFile>();
SqlCommand retrievedFileCmd = DataManager.CreateCommand(
@"
select RetrieveDate, Path, Parameters
from RetrievedFiles
where ParentInstanceID = @parentInstanceID:Int
"
);
retrievedFileCmd.Parameters["@parentInstanceID"].Value = this.TargetParentInstanceID;
using (SqlConnection sourceCn = new SqlConnection(AppSettings.Get(this, "SourceConnectionString")))
{
sourceCn.Open();
retrievedFileCmd.Connection = sourceCn;
using (SqlDataReader reader = retrievedFileCmd.ExecuteReader())
{
while (reader.Read())
{
files.Add(new RetrievedFile(reader["Path"] as string, (DateTime)reader["RetrieveDate"], reader["Parameters"] as string));
}
}
}
// ----------------------------------------
// Get "search engines" - gl/hl combinations
Dictionary <string, SearchEngine> searchEngines = new Dictionary <string, SearchEngine>();
using (DataManager.Current.OpenConnection())
{
SqlCommand searchEngineCmd = DataManager.CreateCommand(
@"
select Search_Engine_ID, Channel_ID, GeoLocation, HostLanguage
from Constant_SearchEngine
where ServiceName = @serviceName:NvarChar
"
);
searchEngineCmd.Parameters["@serviceName"].Value = this.Instance.ParentInstance.Configuration.Name;
using (SqlDataReader reader = searchEngineCmd.ExecuteReader())
{
while (reader.Read())
{
// Key is gl-hl, e.g. us-en
searchEngines.Add
(
String.Format("{0}-{1}", reader["GeoLocation"], reader["HostLanguage"]),
new SearchEngine((int)reader["Search_Engine_ID"], (int)reader["Channel_ID"])
);
}
}
}
// ----------------------------------------
// Get profile data
// Check if this is a profile-specific run.
// The profileID is added to the query to ensure it is active
int profileID = -1;
if (Instance.ParentInstance.Configuration.Options.ContainsKey("ProfileID"))
{
if (!Int32.TryParse(Instance.ParentInstance.Configuration.Options["ProfileID"], out profileID))
{
// Invalid profile ID, report failure
throw new ArgumentException(String.Format("'{0}' is an invalid profile ID.", Instance.ParentInstance.Configuration.Options["ProfileID"]));
}
}
List <ProfileDomain> domains = new List <ProfileDomain>();
Dictionary <long, List <int> > profilesPerKeyword;
Dictionary <int, List <int> > profilesPerSearchEngine;
// Get keywords & search engines per profile
using (DataManager.Current.OpenConnection())
{
profilesPerKeyword = GetProfilesPerItem <long>(profileID, -1,
@"
select
pk.Keyword_GK as ItemID, pk.Profile_ID as ProfileID
from
User_GUI_SerpProfileKeyword pk
inner join User_GUI_SerpProfile p on p.Profile_ID = pk.Profile_ID {0}
where
pk.Account_ID = @accountID:Int
group by
pk.Keyword_GK, pk.Profile_ID
order by
pk.Keyword_GK, pk.Profile_ID
"
);
profilesPerSearchEngine = GetProfilesPerItem <int>(profileID, -1,
@"
select
pse.Search_Engine_ID as ItemID, pse.Profile_ID as ProfileID
from
User_GUI_SerpProfileSearchEngine pse
inner join User_GUI_SerpProfile p on p.Profile_ID = pse.Profile_ID {0}
where
pse.Account_ID = @accountID:Int
group by
pse.Search_Engine_ID, pse.Profile_ID
order by
pse.Search_Engine_ID, pse.Profile_ID
"
);
// Get the domain list
SqlCommand domainCmd = DataManager.CreateCommand(String.Format
(
@"
select
pd.Domain_ID, pd.Profile_ID, pd.Group_ID, pd.Domain
from
User_GUI_SerpProfileDomain pd
inner join User_GUI_SerpProfile p on p.Profile_ID = pd.Profile_ID {0}
where
pd.Account_ID = @accountID:Int
" ,
profileID > -1 ? String.Format("and p.Profile_ID = {0}", profileID) : "and p.IsActive = 1"
));
domainCmd.Parameters["@accountID"].Value = Instance.AccountID;
using (SqlDataReader reader = domainCmd.ExecuteReader())
{
while (reader.Read())
{
try
{
domains.Add(new ProfileDomain((int)reader["Domain_ID"], (int)reader["Profile_ID"], (int)reader["Group_ID"], (string)reader["Domain"]));
}
catch (Exception ex)
{
Log.Write(String.Format("Invalid domain {0}.", reader["Domain"]), ex);
}
}
}
}
// Create objects for bulk insert
DataTable insertBuffer = new DataTable();
insertBuffer.Columns.Add("Account_ID");
insertBuffer.Columns.Add("Channel_ID");
insertBuffer.Columns.Add("Day_Code");
insertBuffer.Columns.Add("ProcessorInstanceID");
insertBuffer.Columns.Add("DateTime");
insertBuffer.Columns.Add("ProfileID");
insertBuffer.Columns.Add("SearchEngineID");
insertBuffer.Columns.Add("KeywordGK");
insertBuffer.Columns.Add("DomainGroupID");
insertBuffer.Columns.Add("DomainID");
insertBuffer.Columns.Add("DomainFilter");
insertBuffer.Columns.Add("Rank");
insertBuffer.Columns.Add("Url");
insertBuffer.Columns.Add("Title");
insertBuffer.Columns.Add("Description");
using (DataManager.Current.OpenConnection())
{
using (SqlBulkCopy bulk = DataManager.CreateBulkCopy(SqlBulkCopyOptions.Default))
{
bulk.DestinationTableName = "Rankings_Data";
// ----------------------------------------
// Go over each file
foreach (RetrievedFile file in files)
{
// Keyword
long keywordGK;
if (!long.TryParse(file.Parameters["KeywordGK"], out keywordGK) || !profilesPerKeyword.ContainsKey(keywordGK))
{
Log.Write
(
String.Format("Keyword GK {0} is not defined in any profile; skipping file {1}", keywordGK, file.Path),
LogMessageType.Warning
);
}
// Check which search engine we're working with
SearchEngine searchEngine = null;
if (!searchEngines.TryGetValue(String.Format("{0}-{1}", file.Parameters["GL"], file.Parameters["HL"]), out searchEngine))
{
Log.Write
(
String.Format("There is no search engine definition with GL = {0} and HL = {1} for the current service; skipping file {2}.", file.Parameters["GL"], file.Parameters["HL"], file.Path),
LogMessageType.Warning
);
continue;
}
// Check which result we're starting with
int currentRank = 1;
if (file.Parameters.ContainsKey("Results"))
{
if (!Int32.TryParse(file.Parameters["Results"].Split('-')[0], out currentRank))
{
Log.Write(String.Format("Invalid rankings range \"{0}\" (Results parameter in Parameters field) so skipping file {1}.", file.Parameters["Results"], file.Path), LogMessageType.Warning);
continue;
}
}
using (SourceDataRowReader <OrganicRankingsRow> rankingsReader = (SourceDataRowReader <OrganicRankingsRow>)Activator.CreateInstance(organicReaderType, file.Path))
{
while (rankingsReader.Read())
{
// Check each domain to see if it fits
foreach (ProfileDomain domain in domains)
{
if (!profilesPerKeyword[keywordGK].Contains(domain.ProfileID))
{
continue;
}
if (!profilesPerSearchEngine[searchEngine.ID].Contains(domain.ProfileID))
{
continue;
}
// Escape the value but unescape the wild card characters
string regexFilter = Regex.Escape(domain.DomainFilter);
regexFilter = "[(//)\\.]" + regexFilter + "[(//)\\.]";
regexFilter = regexFilter.Replace(@"\*", ".*");
// Check if we have a domain match
//AP 12/7/09 - for some reason, there appears a situation where the URL is null - fix this
if (rankingsReader.CurrentRow.Url != null)
{
if (!Regex.IsMatch(rankingsReader.CurrentRow.Url, regexFilter, RegexOptions.IgnoreCase))
{
continue;
}
}
DataRow row = insertBuffer.NewRow();
row["Account_ID"] = Instance.AccountID;
row["Channel_ID"] = searchEngine.ChannelID;
row["Day_Code"] = GetDayCode(file.DateTime);
row["ProcessorInstanceID"] = Instance.InstanceID;
row["DateTime"] = file.DateTime;
row["ProfileID"] = domain.ProfileID;
row["SearchEngineID"] = searchEngine.ID;
row["KeywordGK"] = keywordGK;
row["DomainGroupID"] = domain.GroupID;
row["DomainID"] = domain.ID;
row["DomainFilter"] = domain.DomainFilter;
row["Rank"] = currentRank;
row["Url"] = rankingsReader.CurrentRow.Url;
row["Title"] = rankingsReader.CurrentRow.Title;
row["Description"] = rankingsReader.CurrentRow.Description;
insertBuffer.Rows.Add(row);
// Look for profiles matching this domain
//using (DataManager.Current.OpenConnection())
//{
// DataManager.Current.AssociateCommands(_insertCommand);
// _insertCommand.Parameters["@channelID"].Value = searchEngine.ChannelID;
// _insertCommand.Parameters["@dayCode"].Value = DayCode(file.DateTime);
// _insertCommand.Parameters["@processorInstanceID"].Value = Instance.InstanceID;
// _insertCommand.Parameters["@dateTime"].Value = file.DateTime;
// _insertCommand.Parameters["@profileID"].Value = domain.ProfileID;
// _insertCommand.Parameters["@searchEngineID"].Value = searchEngine.ID;
// _insertCommand.Parameters["@keywordGK"].Value = keywordGK;
// _insertCommand.Parameters["@domainGroupID"].Value = domain.GroupID;
// _insertCommand.Parameters["@domainID"].Value = domain.ID;
// _insertCommand.Parameters["@domainFilter"].Value = domain.DomainFilter;
// _insertCommand.Parameters["@rank"].Value = currentRank;
// _insertCommand.Parameters["@url"].Value = rankingsReader.CurrentRow.Url;
// _insertCommand.Parameters["@title"].Value = rankingsReader.CurrentRow.Title;
// _insertCommand.Parameters["@description"].Value = rankingsReader.CurrentRow.Description;
// //try
// //{
// _insertCommand.ExecuteNonQuery();
// //}
// //catch(Exception ex)
// //{
// // Log.Write(String.Format("Failed to add rank for keyword GK {0} in profile {1}", keywordGK, domain.ProfileID), ex);
// //}
//}
}
// Increate rank counter
currentRank++;
}
}
}
try
{
bulk.WriteToServer(insertBuffer);
}
catch (Exception ex)
{
Log.Write("Failed to insert rankings to database.", ex);
}
}
}
return(ServiceOutcome.Success);
}
protected override int GetDate(SourceDataRowReader <RetrieverDataRow> reader)
{
return(GetDayCode(_requiredDay));
}