/*
private bool hasNewConversionEventRecords(HubSpotLead leadRecord)
{
if(leadRecord == null)
{
throw new NullReferenceException("hasNewConversionEventRecords requires a valid lead record to be passed in");
}
bool returnValue = true;
int newConversionEventCount = 0;
foreach (HubSpotLeadConversionEvent leadConversionEvent in leadRecord.LeadConversionEvents)
{
string guid = leadConversionEvent.getStringValue("guid");
string countSQL = "";
int leadConversionEventCount = 0;
int contactConversionEventCount = 0;
countSQL = String.Format("SELECT COUNT(*) FROM {0}_CONVERSION_EVENT WHERE GUID = '{1}'", new string[] { "LEAD", guid });
leadConversionEventCount += UOleDB.countRecords(countSQL, slxConnection);
countSQL = String.Format("SELECT COUNT(*) FROM {0}_CONVERSION_EVENT WHERE GUID = '{1}'", new string[] { "CONTACT", guid });
contactConversionEventCount += UOleDB.countRecords(countSQL, slxConnection);
if (leadConversionEventCount == 0 && contactConversionEventCount == 0)
{
newConversionEventCount++;
}
}
if (newConversionEventCount == 0)
{
returnValue = false;
}
return returnValue;
} */
private void updateHubSpotRecord(string table, string parentId, HubSpotLead leadRecord)
{
if (isAllowedConversionEventTable(table) == false)
{
throw new ArgumentException(table + " is not a valid conversion event table");
}
//Open the connection just in case
UOleDB.openConnection(slxConnection);
string countQuery = "select count(*) from " + table + "_hubspot where " + table + "id = '{0}'";
int recordCount = UOleDB.countRecords(String.Format(countQuery, parentId), slxConnection);
if (recordCount == 0)
{
insertHubSpotRecord(table, parentId, leadRecord);
return;
}
//Order of parameters is critical due to issues with SLX provider
Dictionary<string, int> trimMap = getTrimMap(getMap(table + "_HubSpot"));
int recordIndex = 0;
HubSpotLeadConversionEvent leadConversionEvent = (HubSpotLeadConversionEvent)leadRecord.LeadConversionEvents[recordIndex];
string sql = "UPDATE " + table + "_HUBSPOT SET " +
"RECENT_CONVERT_DATE = ?, " +
"WEBSITE_VISITS = ?, " +
"UNIQUE_PAGES_VIEWED = ?, " +
"TOTAL_PAGES_VIEWED = ?, " +
"AVERAGE_PAGES_VIEWED = ?, " +
"FORM_NAME = ?, " +
"FOUND_SITE_VIA = ?, " +
"FULL_FOUND_VIA_STRING = ?, " +
"LEAD_GRADE = ?, " +
"JSON_DATA = ?, " +
"MODIFYUSER = ?, " +
"MODIFYDATE = ? " +
"WHERE " +
table + "ID = ?";
OleDbCommand updateCmd = new OleDbCommand(sql, slxConnection);
OleDbParameter p2 = new OleDbParameter("@RECENT_CONVERT_DATE", OleDbType.DBTimeStamp);
p2.Value = SLX_Data.dateTimeToISODateString(leadRecord.getDateTimeValueInLocalTime("lastConvertedAt"));
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "recent convert date: " + SLX_Data.dateTimeToISODateString(leadRecord.getDateTimeValueInLocalTime("lastConvertedAt")));
OleDbParameter p3 = new OleDbParameter("@WEBSITE_VISITS", OleDbType.Integer);
p3.Value = leadRecord.AnalyticDetails.getStringValue("visitCount");
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "website visits: " + leadRecord.AnalyticDetails.getStringValue("visitCount"));
OleDbParameter p4 = new OleDbParameter("@UNIQUE_PAGES_VIEWED", OleDbType.Integer);
//p4.Value = leadRecord.AnalyticDetails.getStringValue("pageViewCount");
OleDbParameter p5 = new OleDbParameter("@TOTAL_PAGES_VIEWED", OleDbType.Integer);
p5.Value = leadRecord.AnalyticDetails.getStringValue("pageViewCount");
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "total pages viewed: " + leadRecord.AnalyticDetails.getStringValue("pageViewCount"));
OleDbParameter p6 = new OleDbParameter("@AVERAGE_PAGE_VIEWS", OleDbType.Double);
p6.Value = getAveragePagesViewed(leadRecord.AnalyticDetails.getIntegerValue("visitCount"), leadRecord.AnalyticDetails.getIntegerValue("pageViewCount"));
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "average page views: " + getAveragePagesViewed(leadRecord.AnalyticDetails.getIntegerValue("visitCount"), leadRecord.AnalyticDetails.getIntegerValue("pageViewCount")));
OleDbParameter p7 = new OleDbParameter("@FORM_NAME", OleDbType.VarChar, getTrimMapValue(trimMap, "Form_Name"));
p7.Value = leadConversionEvent.getStringValue("formName");
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "form name: " + leadConversionEvent.getStringValue("formName"));
OleDbParameter p8 = new OleDbParameter("@FOUND_SITE_VIA", OleDbType.VarChar, getTrimMapValue(trimMap, "Found_Site_Via"));
p8.Value = leadRecord.getStringValue("foundVia");
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "found site via: " + leadRecord.getStringValue("foundVia"));
OleDbParameter p9 = new OleDbParameter("@FULL_FOUND_VIA_STRING", OleDbType.VarChar, getTrimMapValue(trimMap, "Full_Found_Via_String"));
p9.Value = leadRecord.getStringValue("fullFoundViaString");
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "full found via string: " + leadRecord.getStringValue("fullFoundViaString"));
OleDbParameter p10 = new OleDbParameter("@LEAD_GRADE", OleDbType.Decimal);
p10.Value = leadRecord.getDecimalValue("score");
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "lead grade: " + leadRecord.getDecimalValue("score"));
OleDbParameter p11 = new OleDbParameter("@JSON_DATA", OleDbType.VarChar);
p11.Value = leadRecord.JSONData;
updateCmd.Parameters.Add(p2);
updateCmd.Parameters.Add(p3);
updateCmd.Parameters.Add(p4);
updateCmd.Parameters.Add(p5);
updateCmd.Parameters.Add(p6);
updateCmd.Parameters.Add(p7);
updateCmd.Parameters.Add(p8);
updateCmd.Parameters.Add(p9);
updateCmd.Parameters.Add(p10);
updateCmd.Parameters.Add(p11);
addTimestampData(updateCmd, false);
OleDbParameter p1 = new OleDbParameter("@ID", OleDbType.VarChar, 12);
p1.Value = parentId;
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "parentId: " + parentId);
updateCmd.Parameters.Add(p1);
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), updateCmd.CommandText);
updateCmd.ExecuteNonQuery();
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "END Record Update: " + parentId);
}
private void insertHubSpotRecord(string table, string recordId, HubSpotLead leadRecord)
{
//Open the connection just in case
UOleDB.openConnection(slxConnection);
Dictionary<string, int> trimMap = getTrimMap(getMap(table + "_HubSpot"));
HubSpotLeadConversionEvent leadConversionEvent;
string sFormName = "";
int recordIndex = 0;
if (leadRecord.LeadConversionEvents.Count > 0)
{
leadConversionEvent = (HubSpotLeadConversionEvent)leadRecord.LeadConversionEvents[recordIndex];
sFormName = leadConversionEvent.getStringValue("formName");
}
HubSpotLeadAnalyticDetails analyticalDetails = leadRecord.AnalyticDetails;
string sql = "INSERT INTO " + table + "_HUBSPOT(" +
table + "ID, " +
"SECCODEID, " +
"CLOSED_AT, " +
"FIRST_CONVERSION_DATE, " +
"FOUND_SITE_VIA, " +
"HUBSPOT_GUID, " +
"IP_ADDRESS, " +
"RECENT_CONVERT_DATE, " +
"LEAD_DETAIL, " +
"LEAD_GRADE, " +
"FIRST_URL, " +
"TWITTER_HANDLE, " +
"FORM_NAME, " +
"PUBLIC_LEAD_LINK, " +
"FIRST_VISIT, " +
"CONVERSION_COUNT, " +
"FULL_FOUND_VIA_STRING, " +
"INSERTED_AT, " +
"WEBSITE_VISITS, " +
"UNIQUE_PAGES_VIEWED, " +
"TOTAL_PAGES_VIEWED, " +
"AVERAGE_PAGES_VIEWED, " +
"RECENT_VISIT, " +
"PORTAL_ID, " +
"JSON_DATA, " +
"CREATEUSER, " +
"CREATEDATE, " +
"MODIFYUSER, " +
"MODIFYDATE " +
") VALUES(" +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"? " +
")";
OleDbCommand insertCmd = new OleDbCommand(sql, slxConnection);
OleDbParameter p1 = new OleDbParameter("@" + table + "ID", OleDbType.VarChar, 12);
p1.Value = recordId;
OleDbParameter p2 = new OleDbParameter("@SECCODEID", OleDbType.VarChar, 12);
p2.Value = ConfigurationManager.AppSettings.Get("ownerid");
OleDbParameter p3 = new OleDbParameter("@CLOSED_AT", OleDbType.DBTimeStamp);
p3.Value = SLX_Data.dateTimeToISODateString(leadRecord.getDateTimeValueInLocalTime("closedAt"));
OleDbParameter p4 = new OleDbParameter("@FIRST_CONVERSION_DATE", OleDbType.DBTimeStamp);
p4.Value = SLX_Data.dateTimeToISODateString(leadRecord.getDateTimeValueInLocalTime("firstVisitSetAt"));
OleDbParameter p5 = new OleDbParameter("@FOUND_SITE_VIA", OleDbType.VarChar, getTrimMapValue(trimMap, "Found_Site_Via"));
p5.Value = leadRecord.getStringValue("foundVia");
OleDbParameter p6 = new OleDbParameter("@HUBSPOT_GUID", OleDbType.VarChar, getTrimMapValue(trimMap, "HubSpot_GUID"));
p6.Value = leadRecord.getStringValue("guid");
OleDbParameter p7 = new OleDbParameter("@IP_ADDRESS", OleDbType.VarChar, getTrimMapValue(trimMap, "IP_Address"));
p7.Value = leadRecord.getStringValue("ipAddress");
OleDbParameter p8 = new OleDbParameter("@RECENT_CONVERT_DATE", OleDbType.DBTimeStamp);
p8.Value = SLX_Data.dateTimeToISODateString(leadRecord.getDateTimeValueInLocalTime("lastConvertedAt"));
OleDbParameter p9 = new OleDbParameter("@LEAD_DETAIL", OleDbType.LongVarBinary); //MEMO
//p9.Value = leadRecord.getStringValue("message");
OleDbParameter p10 = new OleDbParameter("@LEAD_GRADE", OleDbType.Decimal);
p10.Value = leadRecord.getDecimalValue("score");
OleDbParameter p11 = new OleDbParameter("@FIRST_URL", OleDbType.VarChar, getTrimMapValue(trimMap, "First_URL"));
p11.Value = leadRecord.getStringValue("firstURL");
OleDbParameter p12 = new OleDbParameter("@TWITTER_HANDLE", OleDbType.VarChar, getTrimMapValue(trimMap, "Twitter_Handle"));
p12.Value = leadRecord.getStringValue("twitterHandle");
OleDbParameter p13 = new OleDbParameter("@FORM_NAME", OleDbType.VarChar, getTrimMapValue(trimMap, "Form_Name"));
p13.Value = sFormName;
OleDbParameter p14 = new OleDbParameter("@PUBLIC_LEAD_LINK", OleDbType.VarChar, getTrimMapValue(trimMap, "Public_Lead_Link"));
p14.Value = leadRecord.getStringValue("publicLeadLink").Replace("https://", "").Replace("http://", "").Replace("%3D","") ;
OleDbParameter p15 = new OleDbParameter("@FIRST_VISIT", OleDbType.DBTimeStamp);
p15.Value = SLX_Data.dateTimeToISODateString(leadRecord.getDateTimeValueInLocalTime("firstVisitSetAt"));
OleDbParameter p16 = new OleDbParameter("@CONVERSION_COUNT", OleDbType.Integer);
p16.Value = leadRecord.getIntegerValue("numConversionEvents");
OleDbParameter p17 = new OleDbParameter("@FULL_FOUND_VIA_STRING", OleDbType.VarChar, getTrimMapValue(trimMap, "Full_Found_Via_String"));
p17.Value = leadRecord.getStringValue("fullFoundViaString");
OleDbParameter p18 = new OleDbParameter("@INSERTED_AT", OleDbType.DBTimeStamp);
p18.Value = SLX_Data.dateTimeToISODateString(leadRecord.getDateTimeValueInLocalTime("insertedAt"));
OleDbParameter p19 = new OleDbParameter("@WEBSITE_VISITS", OleDbType.Integer);
p19.Value = analyticalDetails.getStringValue("visitCount");
OleDbParameter p20 = new OleDbParameter("@UNIQUE_PAGES_VIEWED", OleDbType.Integer);
//p20.Value = analyticalDetails.getStringValue("pageViewCount");
OleDbParameter p21 = new OleDbParameter("@TOTAL_PAGES_VIEWED", OleDbType.Integer);
p21.Value = analyticalDetails.getStringValue("pageViewCount");
OleDbParameter p22 = new OleDbParameter("@AVERAGE_PAGES_VIEWED", OleDbType.Double);
p22.Value = getAveragePagesViewed(analyticalDetails.getIntegerValue("visitCount"), analyticalDetails.getIntegerValue("pageViewCount"));
OleDbParameter p23 = new OleDbParameter("@RECENT_VISIT", OleDbType.DBTimeStamp);
//p23.Value = SLX_Data.dateTimeToISODateString(leadRecord.getDateTimeValueInLocalTime("lastConvertedAt"));
p23.Value = SLX_Data.dateTimeToISODateString(analyticalDetails.getDateTimeValueInLocalTime("lastVisitAt"));
OleDbParameter p24 = new OleDbParameter("@PORTAL_ID", OleDbType.VarChar, getTrimMapValue(trimMap, "Portal_Id"));
p24.Value = leadRecord.getStringValue("portalId");
OleDbParameter p25 = new OleDbParameter("@JSON_DATA", OleDbType.VarChar); //NOT TRIMMED BECAUSE IT IS A MEMO FIELD
p25.Value = leadRecord.JSONData;
insertCmd.Parameters.Add(p1);
insertCmd.Parameters.Add(p2);
insertCmd.Parameters.Add(p3);
insertCmd.Parameters.Add(p4);
insertCmd.Parameters.Add(p5);
insertCmd.Parameters.Add(p6);
insertCmd.Parameters.Add(p7);
insertCmd.Parameters.Add(p8);
insertCmd.Parameters.Add(p9);
insertCmd.Parameters.Add(p10);
insertCmd.Parameters.Add(p11);
insertCmd.Parameters.Add(p12);
insertCmd.Parameters.Add(p13);
insertCmd.Parameters.Add(p14);
insertCmd.Parameters.Add(p15);
insertCmd.Parameters.Add(p16);
insertCmd.Parameters.Add(p17);
insertCmd.Parameters.Add(p18);
insertCmd.Parameters.Add(p19);
insertCmd.Parameters.Add(p20);
insertCmd.Parameters.Add(p21);
insertCmd.Parameters.Add(p22);
insertCmd.Parameters.Add(p23);
insertCmd.Parameters.Add(p24);
insertCmd.Parameters.Add(p25);
addTimestampData(insertCmd, true);
insertCmd.ExecuteNonQuery();
}
