/*
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 insertLeadRecord(String leadId, HubSpotLead leadRecord)
{
Dictionary<string, int> trimMap = getTrimMap(getMap("Lead"));
string sql = "INSERT INTO LEAD(" +
"LEADID, " +
"COMPANY, " +
"COMPANY_UC, " +
"FIRSTNAME, " +
"LASTNAME, " +
"LASTNAME_UC, " +
"IMPORTSOURCE, " +
"INDUSTRY, " +
"TITLE, " +
"LEADSOURCEID, " +
"STATUS, " +
"EMAIL, " +
"WORKPHONE, " +
"FAX, " +
"WEBADDRESS, " +
"ACCOUNTMANAGERID, " +
"SECCODEID, " +
"CREATEUSER, " +
"CREATEDATE, " +
"MODIFYUSER, " +
"MODIFYDATE " +
") VALUES(" +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"? " +
")";
OleDbCommand insertCmd = new OleDbCommand(sql, slxConnection);
OleDbParameter p1 = new OleDbParameter("@LEADID", OleDbType.VarChar, 12);
p1.Value = leadId;
OleDbParameter p2 = new OleDbParameter("@COMPANY", OleDbType.VarChar, getTrimMapValue(trimMap, "Company"));
p2.Value = leadRecord.getStringValue("company");
OleDbParameter p3 = new OleDbParameter("@COMPANY_UC", OleDbType.VarChar, getTrimMapValue(trimMap, "Company_UC"));
p3.Value = leadRecord.getStringValue("company").ToUpper();
OleDbParameter p4 = new OleDbParameter("@FIRSTNAME", OleDbType.VarChar, getTrimMapValue(trimMap, "FirstName"));
p4.Value = leadRecord.getStringValue("firstName");
OleDbParameter p5 = new OleDbParameter("@LASTNAME", OleDbType.VarChar, getTrimMapValue(trimMap, "LastName"));
p5.Value = leadRecord.getStringValue("lastName");
OleDbParameter p6 = new OleDbParameter("@LASTNAME_UC", OleDbType.VarChar, getTrimMapValue(trimMap, "LastName_UC"));
p6.Value = leadRecord.getStringValue("lastName").ToUpper();
OleDbParameter p7 = new OleDbParameter("@IMPORTSOURCE", OleDbType.VarChar, getTrimMapValue(trimMap, "ImportSource"));
p7.Value = "HubSpot Integration"; //May want to move to a config file at some point
OleDbParameter p8 = new OleDbParameter("@INDUSTRY", OleDbType.VarChar, getTrimMapValue(trimMap, "Industry"));
p8.Value = leadRecord.getStringValue("industry");
OleDbParameter p9 = new OleDbParameter("@TITLE", OleDbType.VarChar, getTrimMapValue(trimMap, "Title"));
p9.Value = leadRecord.getStringValue("jobTitle");
OleDbParameter p10 = new OleDbParameter("@LEADSOURCEID", OleDbType.VarChar, 12);
p10.Value = leadSourceId;
OleDbParameter p11 = new OleDbParameter("@STATUS", OleDbType.VarChar, getTrimMapValue(trimMap, "Status"));
p11.Value = ConfigurationManager.AppSettings.Get("lead_status");
OleDbParameter p12 = new OleDbParameter("@EMAIL", OleDbType.VarChar, getTrimMapValue(trimMap, "Email"));
p12.Value = leadRecord.getStringValue("email");
OleDbParameter p13 = new OleDbParameter("@PHONE", OleDbType.VarChar, getTrimMapValue(trimMap, "WorkPhone"));
p13.Value = UPhone.formatPhone(leadRecord.getStringValue("phone"), null, null);
OleDbParameter p14 = new OleDbParameter("@FAX", OleDbType.VarChar, getTrimMapValue(trimMap, "Fax"));
p14.Value = UPhone.formatPhone(leadRecord.getStringValue("fax"), null, null);
OleDbParameter p15 = new OleDbParameter("@WEBADDRESS", OleDbType.VarChar, getTrimMapValue(trimMap, "WebAddress"));
p15.Value = leadRecord.getStringValue("website");
OleDbParameter p16 = new OleDbParameter("@ACCOUNTMANAGERID", OleDbType.VarChar, 12);
p16.Value = ConfigurationManager.AppSettings.Get("userid");
OleDbParameter p17 = new OleDbParameter("@SECCODEID", OleDbType.VarChar, 12);
p17.Value = ConfigurationManager.AppSettings.Get("ownerid");
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);
addTimestampData(insertCmd, true);
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), insertCmd.CommandText);
insertCmd.ExecuteNonQuery();
}
//***** MHM Contacts *************//
private int pushLeadsToCRM()
{
login();
getConnectionStringValues();
string description = "" + ConfigurationManager.AppSettings.Get("lead_description");
string stage = "" + ConfigurationManager.AppSettings.Get("lead_stage");
string source = "" + ConfigurationManager.AppSettings.Get("lead_source");
ArrayList hubSpotGuids = new ArrayList();
string slxTable = null;
this.leadSourceId = SLX_Data.getDataValue("LEADSOURCE", "LEADSOURCEID", "ABBREVDESC", "HUBSPOT", slxConnection);
DataSet ds = getLeadsToProcess(); // select * from HubSpotLeadStaging where Processed_Timestamp is null
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "Number of Leads in SELECT: " + ds.Tables[0].Rows.Count);
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "Double Check debug: " + ds.Tables[0].Rows.Count);
//for (int i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
try
{
HubSpotLead leadRecord = new HubSpotLead(ds.Tables[0].Rows[i]["JSON_Data"].ToString());
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "leadRecord To Process: " + leadRecord.getStringValue("guid"));
ds.Tables[0].Rows[i]["Processed_Timestamp"] = SLX_Data.getNewTimestamp();
//this is where we check to see if the hubspot lead guid exists in crm lead table.
//if not then we add lead to crm
// 04252012 - if lead comes in with blank email address then do not try to match on email.
string hubSpotGuid = leadRecord.getStringValue("guid");
string emailAddress = leadRecord.getStringValue("email");
bool leadImported = UBoolean.parse(leadRecord.getStringValue("imported"));
string countQuery = "select count(*) from {0}_HUBSPOT where hubspot_guid = " + UString.addQuotes(hubSpotGuid) + "";
string searchQuery = "select * from {0}_HUBSPOT where hubspot_guid = " + UString.addQuotes(hubSpotGuid) + "";
bool alwaysCreateAsLead = UBoolean.parse(UConfig.getAppConfigValue("AlwaysCreateAsLead", false));
bool dedupeOnImport = UBoolean.parse(UConfig.getAppConfigValue("DedupeOnImport", false));
LeadRecordCheck leadRecordCheck = AppGlobal.getLeadRecordCheckValue(UConfig.getAppConfigValue("LeadRecordCheck", false));
ContactRecordCheck contactRecordCheck = AppGlobal.getContactRecordCheckValue(UConfig.getAppConfigValue("ContactRecordCheck", false));
int leadCount = 0;
int contactCount = 0;
string submissionGuid = leadRecord.LeadConversionEvents[0].getStringValue("guid");
bool submissionGuidFlag_Lead = false;
bool submissionGuidFlag_Contact = false;
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "SUBMISSION GUID: " + submissionGuid);
if (alwaysCreateAsLead == false || (dedupeOnImport == true && leadImported == true))
{
//Always check lead for a guid regardless of leadRecordCheck value
leadCount = UOleDB.countRecords(String.Format(countQuery, "LEAD"), slxConnection);
//Always check contact for a guid if we are anything but NONE
if (contactRecordCheck != ContactRecordCheck.NONE)
{
contactCount = UOleDB.countRecords(String.Format(countQuery, "CONTACT"), slxConnection);
}
}
else
{
//Always check lead for a guid regardless of leadRecordCheck value
leadCount = UOleDB.countRecords(String.Format(countQuery, "LEAD"), slxConnection);
}
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "Finished GUID Match Check - Lead Count: " + leadCount);
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "Finished GUID Match Check - Contact Count: " + contactCount);
// If we don't find a Lead or Contact match based on GUID, check based on submission GUID.
if (leadCount == 0 && contactCount == 0)
{
// Check for leadConversionGUID match to submissionGUID
countQuery = "select count(*) from lead where hs_submissionguid = '{0}'";
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "Submission GUID match check query for Lead: " + countQuery);
leadCount = UOleDB.countRecords(String.Format(countQuery, submissionGuid), slxConnection);
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "Finished Submission GUID Match Check - Lead Count: " + leadCount);
if (leadCount > 0)
{
submissionGuidFlag_Lead = true;
}
countQuery = "select count(*) from contact where hs_submissionguid = '{0}'";
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "Submission GUID match check query for Contact: " + countQuery);
contactCount = UOleDB.countRecords(String.Format(countQuery, submissionGuid), slxConnection);
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "Finished Submission GUID Match Check - Contact Count: " + contactCount);
if (contactCount > 0)
{
submissionGuidFlag_Contact = true;
}
}
// If we found a match based on submission guid then one or both submissionGuidFlag values are now set to true.
// leadCount and/or contactCount will no longer = 0. Therefore email match check will not occur.
//Check for the existence of an email address
// 042520102 - if HubSpot lead has blank email address then do not do this check.
if (emailAddress == "")
{
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "HubSpot Lead has no email address skip email address match: " + leadRecord.getStringValue("guid"));
}
// If we have not found a match on lead guid and/or contact guid and we have an email address then try and find lead or contact from email address.
if (leadCount == 0 && contactCount == 0 && emailAddress != "")
{
if (alwaysCreateAsLead == false)
{
if (leadRecordCheck == LeadRecordCheck.GUIDEMAIL)
{
countQuery = "select count(*) from lead where email = '{0}'";
leadCount = UOleDB.countRecords(String.Format(countQuery, emailAddress), slxConnection);
}
if (contactRecordCheck == ContactRecordCheck.GUIDEMAIL)
{
countQuery = "select count(*) from contact where (email = '{0}' or secondaryemail = '{0}' or email3 = '{0}')";
contactCount = UOleDB.countRecords(String.Format(countQuery, emailAddress), slxConnection);
}
}
else
{
if (leadRecordCheck == LeadRecordCheck.GUIDEMAIL)
{
countQuery = "select count(*) from lead where email = '{0}'";
leadCount = UOleDB.countRecords(String.Format(countQuery, emailAddress), slxConnection);
}
}
}
//If count == 0 then we are inserting else run as an update
if (leadCount == 0 && contactCount == 0) // && hasNewConversionEventRecords(leadRecord) == true)
{
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "BEGIN Insert New Lead: " + leadRecord.getStringValue("guid"));
slxTable = "LEAD";
//string slxLeadId = slxIds[i];
string slxLeadId = SLX_Data.newSLXID("Lead", slxConnection);
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "Insert Lead Record: " + leadRecord.getStringValue("guid"));
//Insert Lead Record
insertLeadRecord(slxLeadId, leadRecord);
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "Insert Lead Address Record: " + leadRecord.getStringValue("guid"));
//Insert Lead Address Record
insertLeadAddressRecord(slxLeadId, leadRecord);
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "Insert Lead HubSpot Record: " + leadRecord.getStringValue("guid"));
//Insert Lead HubSpot Record
insertHubSpotRecord("Lead", slxLeadId, leadRecord);
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "Insert Lead Conversion Events: " + leadRecord.getStringValue("guid"));
//Insert HubSpot Lead Conversion Events
insertHubspotConversionEventRecord("Lead", slxLeadId, leadRecord);
hubSpotGuids.Add(hubSpotGuid);
}
else
{
// We found the lead.
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "BEGIN Update Existing: " + leadRecord.getStringValue("guid"));
//Order below is important.
if (contactCount > 0)
{
slxTable = "CONTACT";
if (submissionGuidFlag_Contact)
{
searchQuery = "SELECT TOP 1 CONTACTID FROM CONTACT WHERE HS_SUBMISSIONGUID = '{0}'";
}
else
{
searchQuery = "SELECT TOP 1 CONTACTID FROM CONTACT WHERE (EMAIL = '{0}' OR SECONDARYEMAIL = '{0}' OR EMAIL3 = '{0}')";
}
}
else if (leadCount > 0)
{
slxTable = "LEAD";
if (submissionGuidFlag_Lead)
{
searchQuery = "SELECT TOP 1 LEADID FROM LEAD WHERE HS_SUBMISSIONGUID = '{0}'";
}
else
{
searchQuery = "SELECT TOP 1 LEADID FROM LEAD WHERE EMAIL = '{0}'";
}
}
// This line tries to search based on GUID. It will fail if a guid was not found because the searchQuery variable will not match for this OLEDB call.
string slxId = UOleDB.getDataValue(String.Format(searchQuery, slxTable), new OleDbConnection(slxConnectionString));
if (String.IsNullOrEmpty(slxId) == true)
{
if (submissionGuidFlag_Contact || submissionGuidFlag_Lead)
{
slxId = UOleDB.getDataValue(String.Format(searchQuery, submissionGuid), new OleDbConnection(slxConnectionString));
}
else
{
slxId = UOleDB.getDataValue(String.Format(searchQuery, emailAddress), new OleDbConnection(slxConnectionString));
}
}
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "updateHubSpotRecord: " + leadRecord.getStringValue("guid"));
updateHubSpotRecord(slxTable, slxId, leadRecord);
//Insert HubSpot Conversion Events
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "insertHubspotConversionEventRecord: " + leadRecord.getStringValue("guid"));
insertHubspotConversionEventRecord(slxTable, slxId, leadRecord);
if (contactCount > 0)
{
slxTable = "ACCOUNT";
searchQuery = "SELECT TOP 1 ACCOUNTID FROM CONTACT WHERE CONTACTID = '{0}'";
slxId = UOleDB.getDataValue(String.Format(searchQuery, slxId), new OleDbConnection(slxConnectionString));
updateHubSpotRecord(slxTable, slxId, leadRecord);
}
}
//saveProcessedRecords(ds, "hubspotleadstaging");
}
catch (Exception e)
{
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "pushHubSpotLeadsToCRM: " + e.Message);
ds.Tables[0].Rows[i]["Processed_Timestamp"] = SLX_Data.dateTimeToISODateString(DateTime.Now.AddYears(-100));
}
}
saveProcessedRecords(ds, "hubspotleadstaging");
logoff();
//ADD CODE TO UPDATE STAGING WITH PARENT ID
updateStagedLeadsWithParentId(hubSpotGuids, slxTable + "_HUBSPOT", slxTable + "ID", "HUBSPOT_GUID");
return 0;
}
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();
}
private void insertLeadAddressRecord(String leadId, HubSpotLead leadRecord)
{
Dictionary<string, int> trimMap = getTrimMap(getMap("Lead_Address"));
string sql = "INSERT INTO LEAD_ADDRESS(" +
"LEAD_ADDRESSID, " +
"LEADID, " +
"ADDRESS1, " +
"ADDRESS2, " +
"ADDRESS3, " +
"CITY, " +
"STATE, " +
"POSTALCODE, " +
"COUNTRY, " +
"ISMAILING, " +
"ISPRIMARY, " +
"DESCRIPTION, " +
"SALUTATION, " +
"TYPE, " +
"CREATEUSER, " +
"CREATEDATE, " +
"MODIFYUSER, " +
"MODIFYDATE " +
") VALUES(" +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"? " +
")";
string leadAddressId = SLX_Data.newSLXID("LEAD_ADDRESS", slxConnection);
OleDbCommand insertCmd = new OleDbCommand(sql, slxConnection);
OleDbParameter p1 = new OleDbParameter("@ADDRESSID", OleDbType.VarChar, 12);
p1.Value = leadAddressId;
OleDbParameter p2 = new OleDbParameter("@LEADID", OleDbType.VarChar, 12);
p2.Value = leadId;
OleDbParameter p3 = new OleDbParameter("@ADDRESS1", OleDbType.VarChar, getTrimMapValue(trimMap, "Address1"));
p3.Value = leadRecord.getStringValue("address"); //Steve made the executive decision to hack it off and store it in ADDRESS1
OleDbParameter p4 = new OleDbParameter("@ADDRESS2", OleDbType.VarChar, getTrimMapValue(trimMap, "Address2"));
//p4.Value = "";
OleDbParameter p5 = new OleDbParameter("@ADDRESS3", OleDbType.VarChar, getTrimMapValue(trimMap, "Address3"));
//p5.Value = "";
OleDbParameter p6 = new OleDbParameter("@CITY", OleDbType.VarChar, getTrimMapValue(trimMap, "City"));
p6.Value = leadRecord.getStringValue("city");
OleDbParameter p7 = new OleDbParameter("@STATE", OleDbType.VarChar, getTrimMapValue(trimMap, "State"));
p7.Value = leadRecord.getStringValue("state");
OleDbParameter p8 = new OleDbParameter("@POSTALCODE", OleDbType.VarChar, getTrimMapValue(trimMap, "PostalCode"));
p8.Value = leadRecord.getStringValue("zip");
OleDbParameter p9 = new OleDbParameter("@COUNTRY", OleDbType.VarChar, getTrimMapValue(trimMap, "Country"));
p9.Value = leadRecord.getStringValue("country");
OleDbParameter p10 = new OleDbParameter("@ISMAILING", OleDbType.VarChar, 1);
p10.Value = "T";
OleDbParameter p11 = new OleDbParameter("@ISPRIMARY", OleDbType.VarChar, 1);
p11.Value = "T";
OleDbParameter p12 = new OleDbParameter("@DESCRIPTION", OleDbType.VarChar, getTrimMapValue(trimMap, "Description"));
p12.Value = "Mailing";
OleDbParameter p13 = new OleDbParameter("@SALUTATION", OleDbType.VarChar, getTrimMapValue(trimMap, "Salutation"));
p13.Value = leadRecord.getStringValue("salutation");
OleDbParameter p14 = new OleDbParameter("@TYPE", OleDbType.VarChar, getTrimMapValue(trimMap, "Type"));
//p14.Value = "";
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);
addTimestampData(insertCmd, true);
insertCmd.ExecuteNonQuery();
//Update lead addressid so the lead and the address records are linked correctly
sql = "UPDATE LEAD SET " +
"LEAD_ADDRESSID = ? " +
"WHERE " +
"LEADID = ?";
OleDbCommand updateLeadCmd = new OleDbCommand(sql, slxConnection);
OleDbParameter p1001 = new OleDbParameter("@LEAD_ADDRESSID", OleDbType.VarChar, 12);
p1001.Value = leadAddressId;
OleDbParameter p1002 = new OleDbParameter("@LEADID", OleDbType.VarChar, 12);
p1002.Value = leadId;
updateLeadCmd.Parameters.Add(p1001);
updateLeadCmd.Parameters.Add(p1002);
updateLeadCmd.ExecuteNonQuery();
}
private void insertHubspotConversionEventRecord(string table, string parentId, HubSpotLead leadRecord)
{
if(isAllowedConversionEventTable(table) == false)
{
throw new ArgumentException(table + " is not a valid conversion event table");
}
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "BEGIN (inside) insertHubspotConversionEventRecord: " + leadRecord.getStringValue("guid"));
Dictionary<string, int> trimMap = getTrimMap(getMap(table + "_Conversion_Event"));
foreach (HubSpotLeadConversionEvent leadConversionEvent in leadRecord.LeadConversionEvents)
{
string guid = leadConversionEvent.getStringValue("guid");
string countSQL = "SELECT COUNT(*) FROM " + table + "_CONVERSION_EVENT WHERE GUID = " + UString.addQuotes(guid);
if (UOleDB.countRecords(countSQL, slxConnection) == 0)
{
//Only insert. No updates
string sql = "INSERT INTO " + table + "_CONVERSION_EVENT(" +
table + "_CONVERSION_EVENTID, " +
table + "ID, " +
"CONVERSION_DATE, " +
"FORM_GUID, " +
"FORM_ID, " +
"FORM_NAME, " +
"FORM_VALUES, " +
"GUID, " +
"LEAD_GUID, " +
"PAGE_NAME, " +
"PAGE_TYPE, " +
"PAGE_URL, " +
"PORTAL_ID, " +
"CREATEUSER, " +
"CREATEDATE, " +
"MODIFYUSER, " +
"MODIFYDATE " +
") VALUES(" +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"?, " +
"? " +
")";
OleDbCommand insertCmd = new OleDbCommand(sql, slxConnection);
OleDbParameter p1 = new OleDbParameter("@" + table + "_CONVERSION_EVENTID", OleDbType.VarChar, 12);
p1.Value = SLX_Data.newSLXID(table + "_CONVERSION_EVENT", slxConnection);
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "conversion eventid: " + SLX_Data.newSLXID(table + "_CONVERSION_EVENT", slxConnection));
OleDbParameter p2 = new OleDbParameter("@" + table + "ID", OleDbType.VarChar, 12);
p2.Value = parentId;
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "parentid: " + parentId);
OleDbParameter p3 = new OleDbParameter("@CONVERSION_DATE", OleDbType.DBTimeStamp);
p3.Value = SLX_Data.dateTimeToISODateString(leadConversionEvent.getDateTimeValueInLocalTime("convertDate"));
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "conversion date: " + SLX_Data.dateTimeToISODateString(leadConversionEvent.getDateTimeValueInLocalTime("convertDate")));
OleDbParameter p4 = new OleDbParameter("@FORM_ID", OleDbType.VarChar, getTrimMapValue(trimMap, "FORM_ID"));
p4.Value = leadConversionEvent.getStringValue("formId");
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "formid: " + leadConversionEvent.getStringValue("formId"));
OleDbParameter p5 = new OleDbParameter("@FORM_GUID", OleDbType.VarChar, getTrimMapValue(trimMap, "FORM_GUID"));
p5.Value = leadConversionEvent.getStringValue("formGuid");
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "form guid: " + leadConversionEvent.getStringValue("formGuid"));
OleDbParameter p6 = new OleDbParameter("@FORM_NAME", OleDbType.VarChar, getTrimMapValue(trimMap, "FORM_NAME"));
p6.Value = leadConversionEvent.getStringValue("formName");
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "form name: " + leadConversionEvent.getStringValue("formName"));
OleDbParameter p7 = new OleDbParameter("@FORM_VALUES", OleDbType.VarChar, getTrimMapValue(trimMap, "FORM_VALUES"));
p7.Value = leadConversionEvent.FormValuesString.Replace("/n/r", Environment.NewLine).Replace("/n", Environment.NewLine);
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "form values: " + leadConversionEvent.FormValuesString.Replace("/n/r", Environment.NewLine).Replace("/n", Environment.NewLine));
OleDbParameter p8 = new OleDbParameter("@GUID", OleDbType.VarChar, getTrimMapValue(trimMap, "GUID"));
p8.Value = guid;
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "guid: " + guid);
OleDbParameter p9 = new OleDbParameter("@LEAD_GUID", OleDbType.VarChar, getTrimMapValue(trimMap, "LEAD_GUID"));
p9.Value = leadConversionEvent.getStringValue("leadGuid");
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "leadguid: " + leadConversionEvent.getStringValue("leadGuid"));
OleDbParameter p10 = new OleDbParameter("@PAGE_NAME", OleDbType.VarChar, getTrimMapValue(trimMap, "PAGE_NAME"));
p10.Value = leadConversionEvent.getStringValue("pageName");
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "pagename: " + leadConversionEvent.getStringValue("pageName"));
OleDbParameter p11 = new OleDbParameter("@PAGE_TYPE", OleDbType.VarChar, getTrimMapValue(trimMap, "PAGE_TYPE"));
p11.Value = leadConversionEvent.getStringValue("pageType");
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "pagetype: " + leadConversionEvent.getStringValue("pageType"));
OleDbParameter p12 = new OleDbParameter("@PAGE_URL", OleDbType.VarChar, getTrimMapValue(trimMap, "PAGE_URL"));
p12.Value = leadConversionEvent.getStringValue("pageUrl");
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "pageUrl: " + leadConversionEvent.getStringValue("pageUrl"));
OleDbParameter p13 = new OleDbParameter("@PORTAL_ID", OleDbType.VarChar, getTrimMapValue(trimMap, "PORTAL_ID"));
p13.Value = leadConversionEvent.getStringValue("portalId");
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "portalId: " + leadConversionEvent.getStringValue("portalId"));
// NOTICE THAT p4 and p5 are reversed. They were mapping backwards. Had to reverse order of parameters to correct.
insertCmd.Parameters.Add(p1);
insertCmd.Parameters.Add(p2);
insertCmd.Parameters.Add(p3);
insertCmd.Parameters.Add(p5);
insertCmd.Parameters.Add(p4);
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);
addTimestampData(insertCmd, true);
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), insertCmd.CommandText);
//ULogging.writeToDebugLog(AppGlobal.getAppLogger(), insertCmd.ToString());
insertCmd.ExecuteNonQuery();
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "END insertHubspotConversionEventRecord: " + leadRecord.getStringValue("guid"));
}
}
}
//***** MHM Contacts *************//
private int pushLeadsToCRM()
{
login(); // performs web service (SOAP) login to Sage CRM
Dictionary<string, int> trimMap = getTrimMap(getMap("Lead"));
string description = "" + ConfigurationManager.AppSettings.Get("lead_description");
string stage = "" + ConfigurationManager.AppSettings.Get("lead_stage");
string source = "" + ConfigurationManager.AppSettings.Get("lead_source");
string channelId = "" + ConfigurationManager.AppSettings.Get("SageCRMChannelId");
ArrayList hubSpotGuids = new ArrayList();
DataSet ds = getLeadsToProcess(); // select * from HubSpotLeadStaging where Processed_Timestamp is null
for (int i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
{
HubSpotLead leadRecord = new HubSpotLead(ds.Tables[0].Rows[i]["JSON_Data"].ToString());
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;
ds.Tables[0].Rows[i]["Processed_Timestamp"] = SLX_Data.getNewTimestamp();
//this is where we check to see if the hubspot lead guid exists in crm lead table.
//if not then we add lead to crm
string hubSpotGuid = leadRecord.getStringValue("guid");
//ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "getLeadsToProcess = " + hubSpotGuid);
//this is where we check to see if the hubspot lead guid exists in crm lead table.
//if not then we add lead to crm
string sqlQuery = "lead_deleted is null and lead_hubspotguid = '" + hubSpotGuid + "'";
queryresult entityResult = binding.query(sqlQuery, "Lead");
ewarebase[] leadBase = entityResult.records;
if (leadBase == null)
{
// add lead to crm
ewarebase[] CRMBase = new ewarebase[1];
lead CRMLead = new lead();
// 201-06-10 NEED TO CLEANUP THE TRIM CALLS ON THE VARIOUS FIELDS
CRMLead.closed = leadRecord.getDateTimeValueInLocalTime("closedAt");
CRMLead.companyname = getTrimmedValue(trimMap, "COMPANYNAME", leadRecord.getStringValue("company"));
CRMLead.companycountry = getTrimmedValue(trimMap, "COMPANYCOUNTRY", leadRecord.getStringValue("country"));
//object country = getPicklistValue(typeof(lead_companycountry), leadRecord.getStringValue("country"));
//if (country != null)
//{
// CRMLead.companycountry = (lead_companycountry)country;
//}
CRMLead.personemail = getTrimmedValue(trimMap, "PERSONEMAIL", leadRecord.getStringValue("email"));
CRMLead.personfaxareacode = getTrimmedValue(trimMap, "PERSONFAXAREACODE", UPhone.getFormattedAreaCode(leadRecord.getStringValue("fax"), null));
CRMLead.personfaxnumber = getTrimmedValue(trimMap, "PERSONFAXNUMBER", UPhone.getFormattedPhoneNumberWithoutAreaCode(leadRecord.getStringValue("fax"), null));
CRMLead.personfirstname = getTrimmedValue(trimMap, "PERSONFIRSTNAME", leadRecord.getStringValue("firstName"));
CRMLead.hubspot1stconversiondate = leadRecord.getDateTimeValueInLocalTime("firstVisitSetAt");
CRMLead.hubspot1stconversiondateSpecified = true;
CRMLead.hubspotfoundsitevia = getTrimmedValue(trimMap, "HUBSPOTFOUNDSITEVIA", leadRecord.getStringValue("foundVia"));
CRMLead.hubspotguid = getTrimmedValue(trimMap, "HUBSPOTGUID", leadRecord.getStringValue("guid"));
CRMLead.companyindustry = getTrimmedValue(trimMap, "COMPANYINDUSTRY", leadRecord.getStringValue("industry"));
//object industry = getPicklistValue(typeof(lead_companyindustry), leadRecord.getStringValue("industry"));
//if (industry != null)
//{
// CRMLead.companyindustry = (lead_companyindustry)industry;
//}
CRMLead.hubspotipaddress = getTrimmedValue(trimMap, "HUBSPOTIPADDRESS", leadRecord.getStringValue("ipAddress"));
CRMLead.persontitle = getTrimmedValue(trimMap, "PERSONTITLE", leadRecord.getStringValue("jobTitle"));
CRMLead.hubspotrecentconvertdate = leadRecord.getDateTimeValueInLocalTime("lastConvertedAt");
CRMLead.hubspotrecentconvertdateSpecified = true;
CRMLead.personlastname = getTrimmedValue(trimMap, "PERSONLASTNAME", leadRecord.getStringValue("lastName"));
CRMLead.hubspotleaddetail = leadRecord.getStringValue("message");
CRMLead.personphoneareacode = getTrimmedValue(trimMap, "PERSONPHONEAREACODE", UPhone.getFormattedAreaCode(leadRecord.getStringValue("phone"), null));
CRMLead.personphonenumber = getTrimmedValue(trimMap, "PERSONPHONENUMBER", UPhone.getFormattedPhoneNumberWithoutAreaCode(leadRecord.getStringValue("phone"), null));
CRMLead.personsalutation = getTrimmedValue(trimMap, "PERSONSALUTATION", leadRecord.getStringValue("salutation"));
//object salutation = getPicklistValue(typeof(lead_personsalutation), leadRecord.getStringValue("salutation"));
//if (salutation != null)
//{
// CRMLead.personsalutation = (lead_personsalutation)salutation;
//}
CRMLead.hubspotleadgrade = getTrimmedValue(trimMap, "HUBSPOTLEADGRADE", leadRecord.getStringValue("score")); //May want to change to decimal like SLX. Don't forget to set Specified flag to true
CRMLead.companystate = getTrimmedValue(trimMap, "COMPANYSTATE", leadRecord.getStringValue("state"));
CRMLead.companywebsite = getTrimmedValue(trimMap, "COMPANYWEBSITE", leadRecord.getStringValue("website"));
CRMLead.companypostcode = getTrimmedValue(trimMap, "COMPANYPOSTCODE", leadRecord.getStringValue("zip"));
CRMLead.companycity = getTrimmedValue(trimMap, "COMPANYCITY", leadRecord.getStringValue("city"));
CRMLead.description = description;
CRMLead.source = source;
//object leadSource = getPicklistValue(typeof(lead_source), source);
//if (leadSource != null)
//{
// CRMLead.source = (lead_source)leadSource;
//}
CRMLead.stage = stage;
//object leadStage = getPicklistValue(typeof(lead_stage), stage);
//if(leadStage != null)
//{
// CRMLead.stage = (lead_stage)leadStage;
//}
CRMLead.opened = leadRecord.getDateTimeValueInLocalTime("insertedAt");
CRMLead.openedSpecified = true;
CRMLead.details = leadRecord.getStringValue("message");
CRMLead.hubspotinsertedat = leadRecord.getDateTimeValueInLocalTime("insertedAt");
CRMLead.hubspotinsertedatSpecified = true;
CRMLead.hubspotfirsturl = leadRecord.getStringValue("firstUrl");
CRMLead.hubspotfullfoundviastring = leadRecord.getStringValue("fullFoundViaString");
CRMLead.hubspottwitterhandle = getTrimmedValue(trimMap, "HUBSPOTTWITTERHANDLE", leadRecord.getStringValue("twitterHandle"));
CRMLead.hubspotformname = getTrimmedValue(trimMap, "HUBSPOTFORMNAME", sFormName);
CRMLead.hubspotconversionevents = leadRecord.getIntegerValue("numConversionEvents");
CRMLead.hubspotconversioneventsSpecified = true;
CRMLead.hubspotpublicleadlink = getTrimmedValue(trimMap, "HUBSPOTPUBLICLEADLINK", leadRecord.getStringValue("publicLeadLink"));
CRMLead.hubspotfirstvisit = leadRecord.getDateTimeValueInLocalTime("firstVisitSetAt");
CRMLead.hubspotfirstvisitSpecified = true;
//CRMLead.assigneduserid = System.Convert.ToInt32(UConfig.getAppConfigValue("lead_assigneduserid", false));
CRMLead.assigneduserid = System.Convert.ToInt32(UConfig.getAppConfigValue("userid", false));
CRMLead.assigneduseridSpecified = true;
if (String.IsNullOrEmpty(channelId) == false)
{
CRMLead.channelid = System.Convert.ToInt32(channelId);
CRMLead.channelidSpecified = true;
}
CRMLead.hubspotwebsitevisits = analyticalDetails.getIntegerValue("visitCount");
CRMLead.hubspotwebsitevisitsSpecified = true;
CRMLead.hubspottotalpageviews = analyticalDetails.getIntegerValue("pageViewCount");
CRMLead.hubspottotalpageviewsSpecified = true;
CRMLead.hubspotaveragepageviews = Convert.ToDouble(getAveragePagesViewed(analyticalDetails.getIntegerValue("visitCount"), analyticalDetails.getIntegerValue("pageViewCount")));
CRMLead.hubspotaveragepageviewsSpecified = true;
CRMBase[0] = CRMLead;
try
{
addresult CRMAddResult = binding.add("Lead", CRMBase); //new ewarebase[1]{CRMLead});
for (int ii = 0; ii < CRMAddResult.records.Length; ii++)
{
crmid id = (crmid)CRMAddResult.records[ii];
insertHubspotLeadConversionEventRecord(id.crmid1, leadRecord);
}
hubSpotGuids.Add(hubSpotGuid);
}
catch (SoapHeaderException e)
{
//string msg = e.Message;
ULogging.writeToErrorLog(AppGlobal.getAppLogger(), "SageCRM Logon Exception: hubSpotGuid = " +hubSpotGuid + " - " + e.Message);
}
}
else
{
// if lead already exists, update LeadConversionEvents field
lead CRMLead = (lead)leadBase[0];
CRMLead.hubspotformname = getTrimmedValue(trimMap, "HUBSPOTFORMNAME", sFormName);
CRMLead.hubspotleadconvertevents = leadRecord.getStringValue("leadConversionEvents");
CRMLead.hubspotrecentconvertdate = leadRecord.getDateTimeValueInLocalTime("lastConvertedAt");
CRMLead.hubspotrecentconvertdateSpecified = true;
CRMLead.hubspotconversionevents = leadRecord.getIntegerValue("numConversionEvents");
CRMLead.hubspotconversioneventsSpecified = true;
CRMLead.hubspotwebsitevisits = analyticalDetails.getIntegerValue("visitCount");
CRMLead.hubspotwebsitevisitsSpecified = true;
CRMLead.hubspottotalpageviews = analyticalDetails.getIntegerValue("pageViewCount");
CRMLead.hubspottotalpageviewsSpecified = true;
CRMLead.hubspotaveragepageviews = Convert.ToDouble(getAveragePagesViewed(analyticalDetails.getIntegerValue("visitCount"), analyticalDetails.getIntegerValue("pageViewCount")));
CRMLead.hubspotaveragepageviewsSpecified = true;
CRMLead.hubspotfoundsitevia = getTrimmedValue(trimMap, "HUBSPOTFOUNDSITEVIA", leadRecord.getStringValue("foundVia"));
CRMLead.hubspotfullfoundviastring = leadRecord.getStringValue("fullFoundViaString");
CRMLead.hubspotleadgrade = getTrimmedValue(trimMap, "HUBSPOTLEADGRADE", leadRecord.getStringValue("score")); //May want to change to decimal like SLX. Don't forget to set Specified flag to true
ewarebase[] leadList = new ewarebase[1];
leadList[0] = CRMLead;
try
{
updateresult CRMUpdateResult = binding.update("Lead", new ewarebase[]{CRMLead});
if (CRMUpdateResult.updatesuccess == true)
{
for (int ii = 0; ii < leadList.Length; ii++)
{
lead CRMLeadRecord = (lead)leadList[ii];
insertHubspotLeadConversionEventRecord(CRMLeadRecord.leadid, leadRecord);
}
}
}
catch (SoapHeaderException e)
{
//string msg = e.Message;
ULogging.writeToErrorLog(AppGlobal.getAppLogger(), "SageCRM Logon Exception: hubSpotGuid = " + hubSpotGuid + " - " + e.Message);
}
hubSpotGuids.Add(hubSpotGuid);
}
}
//saveProcessedLeads(ds);
saveProcessedRecords(ds, "hubspotleadstaging");
logoff();
//ADD CODE TO UPDATE STAGING WITH LEAD ID
updateStagedLeadsWithParentId(hubSpotGuids, "LEAD", "LEAD_LEADID", "LEAD_HUBSPOTGUID");
return 0;
}
public int getHubSpotLeads()
{
openSQLConnection();
DateTime lastPullDateTime = getLastHubSpotPullDateTime();
string hubspotLeadUrl = getHubSpotLeadUrl();
HttpWebResponse response = getHubSpotResponse(hubspotLeadUrl);
if(response != null)
{
bool hasRecords = false;
StreamReader reader = new StreamReader(response.GetResponseStream());
this.jsonData = reader.ReadToEnd();
JArray a = JArray.Parse(jsonData);
for (int i = 0; i < a.Count; i++)
{
hasRecords = true;
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), a[i].ToString()); // log each raw JSON record.
HubSpotLead leadRecord = new HubSpotLead(a[i].ToString()); // pass one JSON record to HubSpotLead class and return it as leadRecord
// leadRecord is only used to grab insertedAt date and guid value.
//DateTime insertedAt = leadRecord.getDateTimeValueInUTCTime("insertedAt");
DateTime hubSpotDate = leadRecord.getDateTimeValueInUTCTime(hubSpotTimePivot); // get date from leadRecord.
//ULogging.writeToDebugLog(AppGlobal.getAppLogger(), leadRecord.Record.ToString(Newtonsoft.Json.Formatting.Indented));
if (hubSpotDate > lastPullDateTime)
{
lastPullDateTime = hubSpotDate;
}
SqlCommand cmdInsert = sqlConnection.CreateCommand();
cmdInsert.CommandType = CommandType.StoredProcedure;
cmdInsert.CommandText = "spInsertHubSpotLeadToStaging";
SqlParameter guidParam = new SqlParameter();
guidParam.ParameterName = "@GUID";
guidParam.SqlDbType = SqlDbType.NVarChar;
guidParam.Value = leadRecord.getStringValue("guid"); // get guid from leadRecord
//***** MHM Contacts – Added *****//
SqlParameter Record_TypeParam = new SqlParameter();
Record_TypeParam.ParameterName = "@RECORD_TYPE";
Record_TypeParam.SqlDbType = SqlDbType.NVarChar;
Record_TypeParam.Value = DBNull.Value;
SqlParameter HubSpot_Record_DateParam = new SqlParameter();
HubSpot_Record_DateParam.ParameterName = "@HUBSPOT_RECORD_DATE";
HubSpot_Record_DateParam.SqlDbType = SqlDbType.DateTime;
HubSpot_Record_DateParam.Value = DBNull.Value;
cmdInsert.Parameters.Add(Record_TypeParam);
cmdInsert.Parameters.Add(HubSpot_Record_DateParam);
//***** MHM Contacts *************//
SqlParameter jsonParam = new SqlParameter();
jsonParam.ParameterName = "@JSON_DATA";
jsonParam.SqlDbType = SqlDbType.NText;
jsonParam.Value = a[i].ToString(); // we are pushing in original JSON
cmdInsert.Parameters.Add(guidParam);
cmdInsert.Parameters.Add(jsonParam);
try
{
cmdInsert.ExecuteNonQuery();
}
catch (SqlException e)
{
ULogging.writeToErrorLog(AppGlobal.getAppLogger(), "GET HUBSPOT LEADS EXCEPTION: " , e);
}
}
if (hasRecords == true)
{
setLastHubSpotPullDateTime(lastPullDateTime.ToUniversalTime());
}
}
closeSQLConnection();
cleanStagingTable();
return 0;
}
private void setALeadValue(HubSpotMapItem item, HubSpotLead aRecord)
{
if (item.CrmDataType.Equals("datetime", StringComparison.OrdinalIgnoreCase))
NativeMethods.GMW_NV_SetValue(pGMNV, item.CRMFieldName, SLX_Data.dateTimeToISODateString(aRecord.getDateTimeValueInLocalTime(item.HubSpotFieldName)));
else if (item.CrmDataType.Equals("int", StringComparison.OrdinalIgnoreCase) |
item.CrmDataType.Equals("smallint", StringComparison.OrdinalIgnoreCase))
return; //NativeMethods.GMW_NV_SetValue(pGMNV, item.CRMFieldName, 3);
else if (item.CrmDataType.Equals("float", StringComparison.OrdinalIgnoreCase))
return; //NativeMethods.GMW_NV_SetValue(pGMNV, item.CRMFieldName, Convert.ToString(leadRecord.getDecimalValue(item.HubSpotFieldName)));
else if (!string.IsNullOrEmpty(aRecord.getStringValue(item.HubSpotFieldName)))
{
NativeMethods.GMW_NV_SetValue(pGMNV, item.CRMFieldName, getTrimValue(aRecord.getStringValue(item.HubSpotFieldName), item.MaxLength, item.CrmDataType));
}
}
//***** MHM Contacts *************//
//private void setAContactValue(HubSpotMapItem item, string strValue)
//{
//}
private int pushLeadsToCRM()
{
login();
if (x == 1)
{
ArrayList hubSpotGuids = new ArrayList();
HubSpotMap mapContact1 = getMap("Contact1");
HubSpotMap mapContact2 = getMap("Contact2");
DataSet ds = getLeadsToProcess(); // select * from HubSpotLeadStaging where Processed_Timestamp is null
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "Number of Leads in SELECT: " + ds.Tables[0].Rows.Count);
for (int i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
{
HubSpotLead leadRecord = new HubSpotLead(ds.Tables[0].Rows[i]["JSON_Data"].ToString());
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;
ds.Tables[0].Rows[i]["Processed_Timestamp"] = SLX_Data.getNewTimestamp();
// Create a new NV container
pGMNV = NativeMethods.GMW_NV_Create();
if (pGMNV != null)
{
//this is where we check to see if the hubspot lead guid exists in crm lead table.
//if not then we add lead to crm
string hubSpotGuid = leadRecord.getStringValue("guid");
string emailAddress = leadRecord.getStringValue("email");
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "getLeadsToProcess = " + hubSpotGuid);
hubSpotGuids.Add(hubSpotGuid);
string strAccountNo = "";
string strRecId = getRecId("UHSGUID", hubSpotGuid);
if (!String.IsNullOrEmpty(strRecId))
NativeMethods.GMW_NV_SetValue(pGMNV, "RECID", strRecId.Trim());
NativeMethods.GMW_NV_SetValue(pGMNV, "SOURCE", "HubSpot");
foreach (DictionaryEntry entry in mapContact1.Items)
{
HubSpotMapItem item = (HubSpotMapItem)entry.Value;
if (!String.IsNullOrEmpty(item.HubSpotFieldName) &&
(String.IsNullOrEmpty(strRecId) || item.CrmUpdate))
{
if (item.CRMFieldName.Equals("contact", StringComparison.OrdinalIgnoreCase))
NativeMethods.GMW_NV_SetValue(pGMNV, item.CRMFieldName, getTrimValue(leadRecord.getStringValue("firstName") + " " + leadRecord.getStringValue("lastName"), item.MaxLength, "varchar"));
else
setALeadValue(item, leadRecord);
}
}
foreach (DictionaryEntry entry in mapContact2.Items)
{
HubSpotMapItem item = (HubSpotMapItem)entry.Value;
if (!String.IsNullOrEmpty(item.HubSpotFieldName) &&
(String.IsNullOrEmpty(strRecId) || item.CrmUpdate))
{
if (item.CRMFieldName.Equals("UHSFRMNAME", StringComparison.OrdinalIgnoreCase))
NativeMethods.GMW_NV_SetValue(pGMNV, item.CRMFieldName, getTrimValue(sFormName, item.MaxLength, "varchar"));
else if (item.CRMFieldName.Equals("UHSWSITEVS", StringComparison.OrdinalIgnoreCase) ||
item.CRMFieldName.Equals("UHSAVGPGV", StringComparison.OrdinalIgnoreCase))
NativeMethods.GMW_NV_SetValue(pGMNV, item.CRMFieldName, Convert.ToString(analyticalDetails.getIntegerValue("visitCount")));
else if (item.CRMFieldName.Equals("UHSTOTPGEV", StringComparison.OrdinalIgnoreCase))
NativeMethods.GMW_NV_SetValue(pGMNV, item.CRMFieldName, Convert.ToString(analyticalDetails.getIntegerValue("pageViewCount")));
else
setALeadValue(item, leadRecord);
}
}
String strValue = (getAveragePagesViewed(analyticalDetails.getIntegerValue("visitCount"), analyticalDetails.getIntegerValue("pageViewCount"))).ToString();
if (!String.IsNullOrEmpty(strValue))
{
NativeMethods.GMW_NV_SetValue(pGMNV, "UHSAVGPGV", getTrimValue(strValue, 10, "varchar"));
}
// Exec the WriteContact function
if (NativeMethods.GMW_Execute("WriteContact", pGMNV) > 0)
{
if (string.IsNullOrEmpty(strRecId))
strRecId = getRecId("UHSGUID", hubSpotGuid);
NativeMethods.GMW_NV_Delete(pGMNV);
if (string.IsNullOrEmpty(strRecId))
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "pushHubSpotLeadsToCRM: Cannot find RecId.");
else
{
strAccountNo = getAccountNo(strRecId);
insertHubspotLeadConversionEventRecord(strAccountNo, leadRecord);
}
}
else
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "pushHubSpotLeadsToCRM: WriteContact Failed.");
}
else
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "pushHubSpotLeadsToCRM: Could not create NV container.");
}
//saveProcessedLeads(ds);
saveProcessedRecords(ds, "hubspotleadstaging");
logoff();
//ADD CODE TO UPDATE STAGING WITH LEAD ID
updateStagedLeadsWithParentId(hubSpotGuids, "CONTACT2", "ACCOUNTNO", "UHSGUID");
}
return 0;
}
