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 insertContactLeadRecord(String leadId, HubSpotLead aContactRecord)
{
HubSpotMap mapLead = getMap("Lead");
HubSpotMapItem item;
string strFields;
string strParms;
// Default fields
strFields = "( LEADID, IMPORTSOURCE, STATUS, ACCOUNTMANAGERID, LEADSOURCEID, ";
strParms = "( ?, ?, ?, ?, ?, ";
// Hubspot mapping fields
foreach (DictionaryEntry entry in mapLead.Items)
{
item = (HubSpotMapItem)entry.Value;
if (!String.IsNullOrEmpty(item.HubSpotFieldName))
{
strFields += item.CRMFieldName + ", ";
strParms += "?, ";
if (item.CRMFieldName.Equals("company", StringComparison.OrdinalIgnoreCase))
{
strFields += "company_uc, ";
strParms += "?, ";
}
else if (item.CRMFieldName.Equals("LastName", StringComparison.OrdinalIgnoreCase))
{
strFields += "LASTNAME_UC, ";
strParms += "?, ";
}
}
}
// Last field
strFields += "SECCODEID, CREATEUSER, CREATEDATE, MODIFYUSER, MODIFYDATE )";
strParms += "?, ?, ?, ?, ? )";
string sql = "INSERT INTO LEAD " + strFields + " values " + strParms;
OleDbCommand insertCmd = new OleDbCommand(sql, slxConnection);
OleDbParameter parm;
// Default Parameters
item = mapLead.getItem("LEADID");
parm = new OleDbParameter("@LEADID", OleDbType.VarChar, item.MaxLength);
parm.Value = leadId;
insertCmd.Parameters.Add(parm);
item = mapLead.getItem("IMPORTSOURCE");
parm = new OleDbParameter("@IMPORTSOURCE", OleDbType.VarChar, item.MaxLength);
parm.Value = "HubSpot Integration";
insertCmd.Parameters.Add(parm);
item = mapLead.getItem("STATUS");
parm = new OleDbParameter("@STATUS", OleDbType.VarChar, item.MaxLength);
parm.Value = ConfigurationManager.AppSettings.Get("lead_status");
insertCmd.Parameters.Add(parm);
item = mapLead.getItem("ACCOUNTMANAGERID");
parm = new OleDbParameter("@ACCOUNTMANAGERID", OleDbType.VarChar, item.MaxLength);
parm.Value = ConfigurationManager.AppSettings.Get("userid");
insertCmd.Parameters.Add(parm);
item = mapLead.getItem("LEADSOURCEID");
parm = new OleDbParameter("@LEADSOURCEID", OleDbType.VarChar, item.MaxLength);
parm.Value = leadSourceId;
insertCmd.Parameters.Add(parm);
// Hubspot mapping parameters
foreach (DictionaryEntry entry in mapLead.Items)
{
item = (HubSpotMapItem)entry.Value;
if (!String.IsNullOrEmpty(item.HubSpotFieldName))
{
string strValue = aContactRecord.ContactProperties.getContactPropertyStringValue(item.HubSpotFieldName);
int intLen = item.MaxLength;
if (!String.IsNullOrEmpty(strValue) && item.CrmDataType.Equals("datetime", StringComparison.OrdinalIgnoreCase))
{
strValue = SLX_Data.dateTimeToISODateString(getDateTimeValue(strValue));
intLen = 30;
}
parm = new OleDbParameter("@" + item.CRMFieldName, OleDbType.VarChar, intLen);
parm.Value = getTrimValue(strValue, intLen, item.CrmDataType);
insertCmd.Parameters.Add(parm);
if (item.CRMFieldName.Equals("company", StringComparison.OrdinalIgnoreCase))
{
parm = new OleDbParameter("@company_uc", OleDbType.VarChar, item.MaxLength);
parm.Value = getTrimValue(strValue, item.MaxLength, item.CrmDataType);
insertCmd.Parameters.Add(parm);
}
else if (item.CRMFieldName.Equals("LastName", StringComparison.OrdinalIgnoreCase))
{
parm = new OleDbParameter("@LASTNAME_UC", OleDbType.VarChar, item.MaxLength);
parm.Value = getTrimValue(strValue, item.MaxLength, item.CrmDataType);
insertCmd.Parameters.Add(parm);
}
}
}
item = mapLead.getItem("SECCODEID");
parm = new OleDbParameter("@SECCODEID", OleDbType.VarChar, item.MaxLength);
parm.Value = ConfigurationManager.AppSettings.Get("ownerid");
insertCmd.Parameters.Add(parm);
addTimestampData(insertCmd, true);
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), insertCmd.CommandText);
insertCmd.ExecuteNonQuery();
}
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 insertContactHubSpotRecord(string table, string recordId, HubSpotLead aContactRecord)
{
//string hubSpotGuid = aContactRecord.ContactDetailRecord.getStringValue("vid");
string hubSpotLeadGuid = aContactRecord.ContactIdentity_Profiles[0].current_Lead_Guid;
string hubSpotVid = aContactRecord.ContactDetailRecord.getStringValue("vid");
string sPublicLink = aContactRecord.ContactDetailRecord.getStringValue("profile-url");
DateTime dInsertDate = getDateTimeValue(aContactRecord.ContactHeaderRecord.getStringValue("addedAt"));
string strNumVisits = "";
string strNumPageView = "";
string sFormName = "";
string sConversionEvents = "";
DateTime sFirst_Conversion_Date = DateTime.MinValue.AddYears(1969);
DateTime sLast_Conversion_Date = DateTime.MinValue.AddYears(1969);
if (aContactRecord.ContactForm_Submissions.Count > 0)
{
sConversionEvents = aContactRecord.ContactForm_Submissions.Count.ToString();
foreach (HubSpotContactForm_Submission ContactForm_Submission in aContactRecord.ContactForm_Submissions)
{
DateTime sTimeStamp = ContactForm_Submission.TimeStamp;
if (sTimeStamp > sLast_Conversion_Date)
{
sFormName = ContactForm_Submission.Title;
sLast_Conversion_Date = ContactForm_Submission.TimeStamp;
}
if (sFirst_Conversion_Date == DateTime.MinValue || sTimeStamp < sFirst_Conversion_Date)
sFirst_Conversion_Date = ContactForm_Submission.TimeStamp;
}
}
//Open the connection just in case
UOleDB.openConnection(slxConnection);
HubSpotMap mapHubspot = getMap(table + "_HubSpot");
HubSpotMapItem item;
string strFields;
string strParms;
// Default fields
strFields = "( " + table + "ID, " + "HUBSPOT_GUID, VID, RECENT_UPDATE, FORM_NAME, PUBLIC_LEAD_LINK, CONVERSION_COUNT, FIRST_CONVERSION_DATE, RECENT_CONVERT_DATE, PORTAL_ID, SECCODEID, JSON_DATA, ";
strParms = "( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ";
// Hubspot mapping fields
foreach (DictionaryEntry entry in mapHubspot.Items)
{
item = (HubSpotMapItem)entry.Value;
if (!String.IsNullOrEmpty(item.HubSpotFieldName))
{
strFields += item.CRMFieldName + ", ";
strParms += "?, ";
}
}
// Last field
strFields += "AVERAGE_PAGES_VIEWED, CREATEUSER, CREATEDATE, MODIFYUSER, MODIFYDATE )";
strParms += "?, ?, ?, ?, ? )";
string sql = "INSERT INTO " + table + "_HUBSPOT " + strFields + " values " + strParms;
OleDbCommand insertCmd = new OleDbCommand(sql, slxConnection);
OleDbParameter parm;
// Default Parameters
item = mapHubspot.getItem(table + "ID");
parm = new OleDbParameter("@" + table + "ID", OleDbType.VarChar, item.MaxLength);
parm.Value = recordId;
insertCmd.Parameters.Add(parm);
//ULogging.writeToDebugLog(AppGlobal.getAppLogger(), parm.ParameterName + " - " + parm.Value);
item = mapHubspot.getItem("HUBSPOT_GUID");
parm = new OleDbParameter("@HUBSPOT_GUID", OleDbType.VarChar, item.MaxLength);
parm.Value = hubSpotLeadGuid;
insertCmd.Parameters.Add(parm);
//ULogging.writeToDebugLog(AppGlobal.getAppLogger(), parm.ParameterName + " - " + parm.Value);
item = mapHubspot.getItem("VID");
parm = new OleDbParameter("@VID", OleDbType.VarChar, item.MaxLength);
parm.Value = hubSpotVid;
insertCmd.Parameters.Add(parm);
//ULogging.writeToDebugLog(AppGlobal.getAppLogger(), parm.ParameterName + " - " + parm.Value);
item = mapHubspot.getItem("RECENT_UPDATE");
parm = new OleDbParameter("@RECENT_UPDATE", OleDbType.VarChar, 30);
parm.Value = dInsertDate;
insertCmd.Parameters.Add(parm);
//ULogging.writeToDebugLog(AppGlobal.getAppLogger(), parm.ParameterName + " - " + parm.Value);
item = mapHubspot.getItem("FORM_NAME");
parm = new OleDbParameter("@FORM_NAME", OleDbType.VarChar, item.MaxLength);
parm.Value = sFormName;
insertCmd.Parameters.Add(parm);
//ULogging.writeToDebugLog(AppGlobal.getAppLogger(), parm.ParameterName + " - " + parm.Value);
item = mapHubspot.getItem("PUBLIC_LEAD_LINK");
parm = new OleDbParameter("@PUBLIC_LEAD_LINK", OleDbType.VarChar, item.MaxLength);
parm.Value = sPublicLink;
insertCmd.Parameters.Add(parm);
//ULogging.writeToDebugLog(AppGlobal.getAppLogger(), parm.ParameterName + " - " + parm.Value);
item = mapHubspot.getItem("CONVERSION_COUNT");
parm = new OleDbParameter("@CONVERSION_COUNT", OleDbType.VarChar, item.MaxLength);
parm.Value = sConversionEvents;
insertCmd.Parameters.Add(parm);
//ULogging.writeToDebugLog(AppGlobal.getAppLogger(), parm.ParameterName + " - " + parm.Value);
item = mapHubspot.getItem("FIRST_CONVERSION_DATE");
parm = new OleDbParameter("@FIRST_CONVERSION_DATE", OleDbType.VarChar, 30);
parm.Value = sFirst_Conversion_Date;
insertCmd.Parameters.Add(parm);
//ULogging.writeToDebugLog(AppGlobal.getAppLogger(), parm.ParameterName + " - " + parm.Value);
item = mapHubspot.getItem("RECENT_CONVERT_DATE");
parm = new OleDbParameter("@RECENT_CONVERT_DATE", OleDbType.VarChar, 30);
parm.Value = sLast_Conversion_Date;
insertCmd.Parameters.Add(parm);
//ULogging.writeToDebugLog(AppGlobal.getAppLogger(), parm.ParameterName + " - " + parm.Value);
item = mapHubspot.getItem("PORTAL_ID");
parm = new OleDbParameter("@PORTAL_ID", OleDbType.VarChar, item.MaxLength);
parm.Value = aContactRecord.ContactDetailRecord.getStringValue("portal-id");
insertCmd.Parameters.Add(parm);
//ULogging.writeToDebugLog(AppGlobal.getAppLogger(), parm.ParameterName + " - " + parm.Value);
item = mapHubspot.getItem("SECCODEID");
parm = new OleDbParameter("@SECCODEID", OleDbType.VarChar, item.MaxLength);
parm.Value = ConfigurationManager.AppSettings.Get("ownerid");
insertCmd.Parameters.Add(parm);
//ULogging.writeToDebugLog(AppGlobal.getAppLogger(), parm.ParameterName + " - " + parm.Value);
item = mapHubspot.getItem("JSON_DATA");
parm = new OleDbParameter("@JSON_DATA", OleDbType.VarChar, item.MaxLength);
parm.Value = aContactRecord.JSONData;
insertCmd.Parameters.Add(parm);
//ULogging.writeToDebugLog(AppGlobal.getAppLogger(), parm.ParameterName + " - " + parm.Value);
// Hubspot mapping parameters
foreach (DictionaryEntry entry in mapHubspot.Items)
{
item = (HubSpotMapItem)entry.Value;
if (!String.IsNullOrEmpty(item.HubSpotFieldName))
{
string strValue = aContactRecord.ContactProperties.getContactPropertyStringValue(item.HubSpotFieldName);
int intLen = item.MaxLength;
if (!String.IsNullOrEmpty(strValue) && item.CrmDataType.Equals("datetime", StringComparison.OrdinalIgnoreCase))
{
strValue = SLX_Data.dateTimeToISODateString(getDateTimeValue(strValue));
intLen = 30;
}
/*if (String.IsNullOrEmpty(strValue) && item.CrmDataType.Equals("datetime", StringComparison.OrdinalIgnoreCase))
{
strValue = DateTime.MinValue.AddYears(1969).ToString();
intLen = 30;
}*/
parm = new OleDbParameter("@" + item.CRMFieldName, OleDbType.VarChar, intLen);
parm.Value = getTrimValue(strValue, intLen, item.CrmDataType);
insertCmd.Parameters.Add(parm);
//ULogging.writeToDebugLog(AppGlobal.getAppLogger(), parm.ParameterName + " - " + parm.Value);
if (item.CRMFieldName.Equals("WEBSITE_VISITS", StringComparison.OrdinalIgnoreCase))
strNumVisits = strValue;
if (item.CRMFieldName.Equals("TOTAL_PAGES_VIEWED", StringComparison.OrdinalIgnoreCase))
strNumPageView = strValue;
}
}
item = mapHubspot.getItem("AVERAGE_PAGES_VIEWED");
parm = new OleDbParameter("@AVERAGE_PAGES_VIEWED", OleDbType.VarChar, item.MaxLength);
string strAverage = "";
if (!string.IsNullOrEmpty(strNumPageView) && !string.IsNullOrEmpty(strNumVisits))
{
strAverage = (getAveragePagesViewed(Convert.ToInt16(strNumVisits), Convert.ToInt16(strNumPageView))).ToString();
}
parm.Value = strAverage;
insertCmd.Parameters.Add(parm);
addTimestampData(insertCmd, true);
insertCmd.ExecuteNonQuery();
//OleDbParameter p5 = new OleDbParameter("@FOUND_SITE_VIA", OleDbType.VarChar, getTrimMapValue(trimMap, "Found_Site_Via"));
//p5.Value = leadRecord.getStringValue("foundVia");
//OleDbParameter p17 = new OleDbParameter("@FULL_FOUND_VIA_STRING", OleDbType.VarChar, getTrimMapValue(trimMap, "Full_Found_Via_String"));
//p17.Value = leadRecord.getStringValue("fullFoundViaString");
//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"));
}
//***** MHM Contacts *************//
private void insertHubspotLeadConversionEventRecord(string strAccountNo, HubSpotLead leadRecord)
{
ArrayList guids = getLeadConversionGuids(strAccountNo);
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "leadRecord.LeadConversionEvents.Count: " + strAccountNo + " - " + leadRecord.LeadConversionEvents.Count);
foreach (HubSpotLeadConversionEvent leadConversionEvent in leadRecord.LeadConversionEvents)
{
if (guids.Contains(leadConversionEvent.getStringValue("guid")) == false)
{
// Create a new NV container
pGMNV = NativeMethods.GMW_NV_Create();
if (pGMNV != null)
{
NativeMethods.GMW_NV_SetValue(pGMNV, "AccountNo", strAccountNo.Trim());
NativeMethods.GMW_NV_SetValue(pGMNV, "detail", "HubSpot");
NativeMethods.GMW_NV_SetValue(pGMNV, "notes", "Form Guid: " + leadConversionEvent.getStringValue("formGuid") + Environment.NewLine + Environment.NewLine +
"Page URL: " + leadConversionEvent.getStringValue("pageUrl") + Environment.NewLine + Environment.NewLine +
leadConversionEvent.FormValuesString.Replace("/n/r", Environment.NewLine).Replace("/n", Environment.NewLine));
NativeMethods.GMW_NV_SetValue(pGMNV, "UField11", getTrimValue(leadConversionEvent.getStringValue("guid"), 40, "varchar"));
//CRMLeadConversionEvent.formguid = getTrimmedValue(trimMap, "FORMGUID", leadConversionEvent.getStringValue("formGuid"));
//CRMLeadConversionEvent.formvalues = leadConversionEvent.FormValuesString.Replace("/n/r", Environment.NewLine).Replace("/n", Environment.NewLine);
// Exec the WriteContact function
if (NativeMethods.GMW_Execute("WriteDetail", pGMNV) > 0)
{
NativeMethods.GMW_NV_Delete(pGMNV);
updLeadEvent(leadConversionEvent.getStringValue("guid"), leadConversionEvent);
}
else
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "insertHubspotLeadConversionEventRecord: WriteDetail Failed.");
}
else
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "insertHubspotLeadConversionEventRecord: Could not create NV container.");
}
}
}
private void updateHubSpotContactRecord(string table, string parentId, HubSpotLead aContactRecord)
{
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)
{
insertContactHubSpotRecord(table, parentId, aContactRecord);
return;
}
//Order of parameters is critical due to issues with SLX provider
Dictionary<string, int> trimMap = getTrimMap(getMap(table + "_HubSpot"));
string strNumVisits = "";
string strNumPageView = "";
string sFormName = "";
string sConversionEvents = "";
DateTime sFirst_Conversion_Date = DateTime.MinValue;
DateTime sLast_Conversion_Date = DateTime.MinValue;
if (aContactRecord.ContactForm_Submissions.Count > 0)
{
sConversionEvents = aContactRecord.ContactForm_Submissions.Count.ToString();
foreach (HubSpotContactForm_Submission ContactForm_Submission in aContactRecord.ContactForm_Submissions)
{
DateTime sTimeStamp = ContactForm_Submission.TimeStamp;
if (sTimeStamp > sLast_Conversion_Date)
{
sFormName = ContactForm_Submission.Title;
sLast_Conversion_Date = ContactForm_Submission.TimeStamp;
}
if (sFirst_Conversion_Date == DateTime.MinValue || sTimeStamp < sFirst_Conversion_Date)
sFirst_Conversion_Date = ContactForm_Submission.TimeStamp;
}
}
string sql = "UPDATE " + table + "_HUBSPOT SET " +
"VID = ?, " +
"RECENT_UPDATE = ?, " +
"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);
strNumVisits = aContactRecord.ContactProperties.getContactPropertyStringValue("hs_analytics_num_visits");
strNumPageView = aContactRecord.ContactProperties.getContactPropertyStringValue("hs_analytics_num_page_views");
OleDbParameter parm;
parm = new OleDbParameter("@VID", OleDbType.VarChar, getTrimMapValue(trimMap, "VID"));
parm.Value = aContactRecord.ContactDetailRecord.getStringValue("vid");
updateCmd.Parameters.Add(parm);
parm = new OleDbParameter("@RECENT_UPDATE", OleDbType.DBTimeStamp);
parm.Value = getDateTimeValue(aContactRecord.ContactHeaderRecord.getStringValue("addedAt")); ;
updateCmd.Parameters.Add(parm);
OleDbParameter p2 = new OleDbParameter("@RECENT_CONVERT_DATE", OleDbType.DBTimeStamp);
p2.Value = sLast_Conversion_Date;
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "recent convert date: " + sLast_Conversion_Date);
OleDbParameter p3 = new OleDbParameter("@WEBSITE_VISITS", OleDbType.Integer);
p3.Value = strNumVisits;
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "website visits: " + strNumVisits);
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 = strNumPageView;
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "total pages viewed: " + strNumPageView);
OleDbParameter p6 = new OleDbParameter("@AVERAGE_PAGE_VIEWS", OleDbType.Double);
string strAverage = "";
if (!string.IsNullOrEmpty(strNumPageView) && !string.IsNullOrEmpty(strNumVisits))
{
strAverage = (getAveragePagesViewed(Convert.ToInt16(strNumVisits), Convert.ToInt16(strNumPageView))).ToString();
}
p6.Value = strAverage;
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "average page views: " + strAverage);
OleDbParameter p7 = new OleDbParameter("@FORM_NAME", OleDbType.VarChar, getTrimMapValue(trimMap, "Form_Name"));
p7.Value = sFormName;
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "form name: " + sFormName);
OleDbParameter p8 = new OleDbParameter("@FOUND_SITE_VIA", OleDbType.VarChar, getTrimMapValue(trimMap, "Found_Site_Via"));
p8.Value = aContactRecord.ContactProperties.getContactPropertyStringValue("hs_analytics_first_referrer");
//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 = aContactRecord.getDecimalFromValue(aContactRecord.ContactProperties.getContactPropertyStringValue("hubspotscore"));
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "lead grade: " + aContactRecord.ContactProperties.getContactPropertyStringValue("hubspotscore"));
OleDbParameter p11 = new OleDbParameter("@JSON_DATA", OleDbType.VarChar);
p11.Value = aContactRecord.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);
}
/// <summary>
/// This method goes through each conversion event and checks the lead and contact conversion event tables to see if the conversion event guid exists
/// If both lead and contact conversion event count return a value of zero, the newConversionEvent count is incremented by one since the conversion event
/// guid did not exist in either table. If the count is greater than zero, then true is returned else false is returned
/// </summary>
/// <param name="leadRecord"></param>
/// <returns></returns>
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;
int leadConversionEventCount = 0;
int contactConversionEventCount = 0;
bool firstConversionEvent = true;
StringBuilder sb = new StringBuilder();
//Build the IN clause string of values
foreach (HubSpotLeadConversionEvent leadConversionEvent in leadRecord.LeadConversionEvents)
{
string guid = leadConversionEvent.getStringValue("guid");
if(firstConversionEvent == true)
{
sb.Append(UString.addQuotes(guid));
firstConversionEvent = false;
}
else
{
sb.Append("," + UString.addQuotes(guid));
}
}
string countSQL = "";
countSQL = String.Format("SELECT COUNT(*) FROM {0}_CONVERSION_EVENT WHERE GUID IN ({1})", new string[] { "LEAD", sb.ToString() });
leadConversionEventCount += UOleDB.countRecords(countSQL, slxConnection);
countSQL = String.Format("SELECT COUNT(*) FROM {0}_CONVERSION_EVENT WHERE GUID IN ({1})", new string[] { "CONTACT", sb.ToString() });
contactConversionEventCount += UOleDB.countRecords(countSQL, slxConnection);
newConversionEventCount = leadRecord.LeadConversionEvents.Count - (leadConversionEventCount + contactConversionEventCount);
if (newConversionEventCount == 0)
{
returnValue = false;
}
return returnValue;
}
private lead setLeadFields(lead CRMLead, HubSpotLead aContactRecord, bool addRecord)
{
string strValue;
HubSpotMapItem item;
// When adding Lead
if (addRecord)
{
//CRMLead.hubspotguid = aContactRecord.ContactDetailRecord.getStringValue("vid");
CRMLead.description = strDescription;
CRMLead.source = strSource;
CRMLead.stage = strStage;
CRMLead.assigneduserid = System.Convert.ToInt32(UConfig.getAppConfigValue("userid", false));
CRMLead.assigneduseridSpecified = true;
CRMLead.hubspotpublicleadlink = getTrimValue(mapLead.getItem("hubspotpublicleadlink"), strPublicLink);
if (String.IsNullOrEmpty(strChannelId) == false)
{
CRMLead.channelid = System.Convert.ToInt32(strChannelId);
CRMLead.channelidSpecified = true;
}
}
// Always update fields
CRMLead.hubspotguid = aContactRecord.ContactIdentity_Profiles[0].current_Lead_Guid; ;
CRMLead.hubspotvid = aContactRecord.ContactDetailRecord.getStringValue("vid");
CRMLead.hubspotrecentupdate = getDateTimeValue(aContactRecord.ContactHeaderRecord.getStringValue("addedAt"));
CRMLead.hubspotrecentupdateSpecified = true;
CRMLead.hubspotformname = getTrimValue(mapLead.getItem("HUBSPOTFORMNAME"), strFormName);
CRMLead.hubspotconversionevents = getIntegerValue(strConversionEvents);
CRMLead.hubspotconversioneventsSpecified = true;
CRMLead.hubspotwebsitevisits = getIntegerValue(getTrimValue(mapLead.getItem("hubspotwebsitevisits"), aContactRecord));
CRMLead.hubspotwebsitevisitsSpecified = true;
CRMLead.hubspottotalpageviews = getIntegerValue(getTrimValue(mapLead.getItem("hubspottotalpageviews"), aContactRecord));
CRMLead.hubspottotalpageviewsSpecified = true;
CRMLead.hubspotaveragepageviews = Convert.ToDouble(getAveragePagesViewed(CRMLead.hubspotwebsitevisits, CRMLead.hubspottotalpageviews));
CRMLead.hubspotaveragepageviewsSpecified = true;
CRMLead.hubspotleadgrade = getTrimValue(mapLead.getItem("hubspotleadgrade"), aContactRecord); //May want to change to decimal like SLX. Don't forget to set Specified flag to true
if (!string.IsNullOrEmpty(strLast_Conversion_Date))
{
CRMLead.hubspotrecentconvertdate = getDateTimeValue(strLast_Conversion_Date);
CRMLead.hubspotrecentconvertdateSpecified = true;
}
if (!string.IsNullOrEmpty(strFirst_Conversion_Date))
{
CRMLead.hubspot1stconversiondate = getDateTimeValue(strFirst_Conversion_Date);
CRMLead.hubspot1stconversiondateSpecified = true;
}
//CRMLead.hubspotfullfoundviastring = leadRecord.getStringValue("fullFoundViaString");
//CRMLead.hubspotleadconvertevents = leadRecord.getStringValue("leadConversionEvents");
// Standard mapping fields
item = mapLead.getItem("Closed");
if (!string.IsNullOrEmpty(item.HubSpotFieldName) && !string.IsNullOrEmpty(getTrimValue(item, aContactRecord)) && (addRecord || item.CrmUpdate))
CRMLead.closed = getDateTimeValue(getTrimValue(item, aContactRecord));
item = mapLead.getItem("companyname");
if (!string.IsNullOrEmpty(item.HubSpotFieldName) && (addRecord || item.CrmUpdate))
CRMLead.companyname = getTrimValue(item, aContactRecord);
item = mapLead.getItem("companycountry");
if (!string.IsNullOrEmpty(item.HubSpotFieldName) && (addRecord || item.CrmUpdate))
CRMLead.companycountry = getTrimValue(item, aContactRecord);
item = mapLead.getItem("PERSONEMAIL");
if (!string.IsNullOrEmpty(item.HubSpotFieldName) && (addRecord || item.CrmUpdate))
CRMLead.personemail = getTrimValue(item, aContactRecord);
item = mapLead.getItem("PERSONFAXNUMBER");
if (!string.IsNullOrEmpty(item.HubSpotFieldName) && (addRecord || item.CrmUpdate))
{
strValue = aContactRecord.ContactProperties.getContactPropertyStringValue(item.HubSpotFieldName);
CRMLead.personfaxareacode = UPhone.getFormattedAreaCode(strValue, null);
CRMLead.personfaxnumber = UPhone.getFormattedPhoneNumberWithoutAreaCode(strValue, null);
}
item = mapLead.getItem("PERSONFIRSTNAME");
if (!string.IsNullOrEmpty(item.HubSpotFieldName) && (addRecord || item.CrmUpdate))
CRMLead.personfirstname = getTrimValue(item, aContactRecord);
item = mapLead.getItem("COMPANYINDUSTRY");
if (!string.IsNullOrEmpty(item.HubSpotFieldName) && (addRecord || item.CrmUpdate))
CRMLead.companyindustry = getTrimValue(item, aContactRecord);
item = mapLead.getItem("HUBSPOTIPADDRESS");
if (!string.IsNullOrEmpty(item.HubSpotFieldName) && (addRecord || item.CrmUpdate))
CRMLead.hubspotipaddress = getTrimValue(item, aContactRecord);
item = mapLead.getItem("PERSONTITLE");
if (!string.IsNullOrEmpty(item.HubSpotFieldName) && (addRecord || item.CrmUpdate))
CRMLead.persontitle = getTrimValue(item, aContactRecord);
item = mapLead.getItem("PERSONLASTNAME");
if (!string.IsNullOrEmpty(item.HubSpotFieldName) && (addRecord || item.CrmUpdate))
CRMLead.personlastname = getTrimValue(item, aContactRecord);
item = mapLead.getItem("hubspotleaddetail");
if (!string.IsNullOrEmpty(item.HubSpotFieldName) && (addRecord || item.CrmUpdate))
{
CRMLead.hubspotleaddetail = getTrimValue(item, aContactRecord);
CRMLead.details = getTrimValue(item, aContactRecord);
}
item = mapLead.getItem("PERSONPHONENUMBER");
if (!string.IsNullOrEmpty(item.HubSpotFieldName) && (addRecord || item.CrmUpdate))
{
strValue = aContactRecord.ContactProperties.getContactPropertyStringValue(item.HubSpotFieldName);
CRMLead.personphoneareacode = UPhone.getFormattedAreaCode(strValue, null);
CRMLead.personphonenumber = UPhone.getFormattedPhoneNumberWithoutAreaCode(strValue, null);
}
item = mapLead.getItem("PERSONSALUTATION");
if (!string.IsNullOrEmpty(item.HubSpotFieldName) && (addRecord || item.CrmUpdate))
CRMLead.personsalutation = getTrimValue(item, aContactRecord);
item = mapLead.getItem("COMPANYSTATE");
if (!string.IsNullOrEmpty(item.HubSpotFieldName) && (addRecord || item.CrmUpdate))
CRMLead.companystate = getTrimValue(item, aContactRecord);
item = mapLead.getItem("COMPANYWEBSITE");
if (!string.IsNullOrEmpty(item.HubSpotFieldName) && (addRecord || item.CrmUpdate))
CRMLead.companywebsite = getTrimValue(item, aContactRecord);
item = mapLead.getItem("COMPANYPOSTCODE");
if (!string.IsNullOrEmpty(item.HubSpotFieldName) && (addRecord || item.CrmUpdate))
CRMLead.companypostcode = getTrimValue(item, aContactRecord);
item = mapLead.getItem("COMPANYCITY");
if (!string.IsNullOrEmpty(item.HubSpotFieldName) && (addRecord || item.CrmUpdate))
CRMLead.companycity = getTrimValue(item, aContactRecord);
item = mapLead.getItem("HUBSPOTTWITTERHANDLE");
if (!string.IsNullOrEmpty(item.HubSpotFieldName) && (addRecord || item.CrmUpdate))
CRMLead.hubspottwitterhandle = getTrimValue(item, aContactRecord);
item = mapLead.getItem("hubspotinsertedat");
if (!string.IsNullOrEmpty(item.HubSpotFieldName) && !string.IsNullOrEmpty(getTrimValue(item, aContactRecord)) && (addRecord || item.CrmUpdate))
{
CRMLead.hubspotinsertedat = getDateTimeValue(getTrimValue(item, aContactRecord));
CRMLead.hubspotinsertedatSpecified = true;
CRMLead.opened = getDateTimeValue(getTrimValue(item, aContactRecord));
CRMLead.openedSpecified = true;
}
item = mapLead.getItem("hubspotfirsturl");
if (!string.IsNullOrEmpty(item.HubSpotFieldName) && (addRecord || item.CrmUpdate))
CRMLead.hubspotfirsturl = getTrimValue(item, aContactRecord);
item = mapLead.getItem("hubspotfirstvisit");
if (!string.IsNullOrEmpty(item.HubSpotFieldName) && !string.IsNullOrEmpty(getTrimValue(item, aContactRecord)) && (addRecord || item.CrmUpdate))
{
CRMLead.hubspotfirstvisit = getDateTimeValue(getTrimValue(item, aContactRecord));
CRMLead.hubspotfirstvisitSpecified = true;
}
//CRMLead.hubspotfoundsitevia = getTrimmedValue(trimMap, "HUBSPOTFOUNDSITEVIA", leadRecord.getStringValue("foundVia"));
item = mapLead.getItem("hubspotfoundsitevia");
if (!string.IsNullOrEmpty(item.HubSpotFieldName) && (addRecord || item.CrmUpdate))
CRMLead.hubspotfoundsitevia = getTrimValue(item, aContactRecord);
return CRMLead;
}
private string getTrimValue(HubSpotMapItem item, HubSpotLead aContactRecord)
{
string strValue = "";
if (!string.IsNullOrEmpty(item.HubSpotFieldName))
strValue = aContactRecord.ContactProperties.getContactPropertyStringValue(item.HubSpotFieldName);
if (!string.IsNullOrEmpty(strValue) &&
(item.CrmDataType.ToUpper().Contains("STRING") ||
item.CrmDataType.ToUpper().Contains("NCHAR") ||
item.CrmDataType.ToUpper().Contains("NVARCHAR")) &&
item.MaxLength > 0 &&
strValue.Length > item.MaxLength)
return strValue.Substring(0, item.MaxLength);
else
return strValue;
}
public string buildFormValues(string guid, HubSpotLead aContactRecord)
{
string strFormValues = "";
string strValue;
string strHubSpotName;
string strHubSpotLabel;
for (int i = 0; i < dsContactProperties.Tables[0].Rows.Count; i++)
{
strHubSpotName = dsContactProperties.Tables[0].Rows[i]["HUBSPOT_NAME"].ToString();
strHubSpotLabel = dsContactProperties.Tables[0].Rows[i]["HUBSPOT_LABEL"].ToString();
if (!string.IsNullOrEmpty(strHubSpotName))
{
strValue = aContactRecord.ContactProperties.getFormValue(guid, strHubSpotName, strHubSpotLabel);
if (!string.IsNullOrEmpty(strValue))
strFormValues += strValue + Environment.NewLine;
}
}
return strFormValues;
}
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;
}
private int pushContactsToCRM()
{
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 Contacts in SELECT: " + ds.Tables[0].Rows.Count);
for (int i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
{
HubSpotLead aContactRecord = new HubSpotLead(ds.Tables[0].Rows[i]["JSON_Data"].ToString());
string hubSpotLeadGuid = aContactRecord.ContactIdentity_Profiles[0].current_Lead_Guid;
string hubSpotVid = aContactRecord.ContactDetailRecord.getStringValue("vid");
string hubSpotEmail = aContactRecord.ContactIdentity_Profiles[0].Email;
string sPublicLink = aContactRecord.ContactDetailRecord.getStringValue("profile-url");
DateTime dInsertDate = getDateTimeValue(aContactRecord.ContactHeaderRecord.getStringValue("addedAt"));
ds.Tables[0].Rows[i]["Processed_Timestamp"] = SLX_Data.getNewTimestamp();
string strRecId = getRecId("UHSVID", hubSpotVid);
if (String.IsNullOrEmpty(strRecId) && !string.IsNullOrEmpty(hubSpotLeadGuid))
strRecId = getRecId("UHSGUID", hubSpotLeadGuid);
if (String.IsNullOrEmpty(strRecId) && !string.IsNullOrEmpty(hubSpotLeadGuid))
strRecId = getRecIdFromEmail(hubSpotEmail);
if (dInsertDate > dtRecentUpdate)
{
string sFormName = "";
string sConversionEvents = "";
string sFirst_Conversion_Date = "";
string sLast_Conversion_Date = "0";
if (aContactRecord.ContactForm_Submissions.Count > 0)
{
sConversionEvents = aContactRecord.ContactForm_Submissions.Count.ToString();
foreach (HubSpotContactForm_Submission ContactForm_Submission in aContactRecord.ContactForm_Submissions)
{
string sTimeStamp = aContactRecord.getStringValueFromValue(ContactForm_Submission.Json["timestamp"].ToString());
if (Convert.ToInt64(sTimeStamp) > Convert.ToInt64(sLast_Conversion_Date))
{
sFormName = ContactForm_Submission.Title;
sLast_Conversion_Date = aContactRecord.getStringValueFromValue(ContactForm_Submission.Json["timestamp"].ToString());
}
if (sFirst_Conversion_Date == "" || Convert.ToInt64(sTimeStamp) < Convert.ToInt64(sFirst_Conversion_Date))
sFirst_Conversion_Date = aContactRecord.getStringValueFromValue(ContactForm_Submission.Json["timestamp"].ToString());
}
}
//HubSpotLeadAnalyticDetails analyticalDetails = leadRecord.AnalyticDetails;
// 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
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "getLeadsToProcess = " + hubSpotVid);
hubSpotGuids.Add(hubSpotVid);
string strAccountNo = "";
if (!String.IsNullOrEmpty(strRecId))
NativeMethods.GMW_NV_SetValue(pGMNV, "RECID", strRecId.Trim());
NativeMethods.GMW_NV_SetValue(pGMNV, "SOURCE", "HubSpot");
NativeMethods.GMW_NV_SetValue(pGMNV, "UHSGUID", hubSpotLeadGuid);
NativeMethods.GMW_NV_SetValue(pGMNV, "UHSVID", hubSpotVid);
NativeMethods.GMW_NV_SetValue(pGMNV, "UHSRCNTUPD", dInsertDate.ToString());
string strValue;
string strNumVisits = "";
string strNumPageView = "";
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))
strValue = getTrimValue(aContactRecord.ContactProperties.getContactPropertyStringValue("firstname") + " " + aContactRecord.ContactProperties.getContactPropertyStringValue("lastname"),
item.MaxLength, "varchar");
else
strValue = aContactRecord.ContactProperties.getContactPropertyStringValue(item.HubSpotFieldName);
if (!String.IsNullOrEmpty(strValue))
{
if (item.CrmDataType.Equals("datetime", StringComparison.OrdinalIgnoreCase))
{
strValue = SLX_Data.dateTimeToISODateString(getDateTimeValue(strValue));
NativeMethods.GMW_NV_SetValue(pGMNV, item.CRMFieldName, strValue);
}
else
NativeMethods.GMW_NV_SetValue(pGMNV, item.CRMFieldName, getTrimValue(strValue, item.MaxLength, item.CrmDataType));
}
}
}
foreach (DictionaryEntry entry in mapContact2.Items)
{
HubSpotMapItem item = (HubSpotMapItem)entry.Value;
strValue = string.Empty;
if (item.CRMFieldName.Equals("UHSFRMNAME", StringComparison.OrdinalIgnoreCase))
strValue = sFormName;
else if (item.CRMFieldName.Equals("UHS1STCDTE", StringComparison.OrdinalIgnoreCase))
strValue = sFirst_Conversion_Date;
else if (item.CRMFieldName.Equals("UHSRCCNVDT", StringComparison.OrdinalIgnoreCase))
strValue = sLast_Conversion_Date;
else if (item.CRMFieldName.Equals("UHSCONVEVT", StringComparison.OrdinalIgnoreCase))
strValue = sConversionEvents;
else if (item.CRMFieldName.Equals("UHSLEADLNK", StringComparison.OrdinalIgnoreCase))
strValue = sPublicLink;
else if (!String.IsNullOrEmpty(item.HubSpotFieldName) &&
(String.IsNullOrEmpty(strRecId) || item.CrmUpdate))
strValue = aContactRecord.ContactProperties.getContactPropertyStringValue(item.HubSpotFieldName);
if (!String.IsNullOrEmpty(strValue))
{
if (item.CrmDataType.Equals("datetime", StringComparison.OrdinalIgnoreCase))
{
strValue = SLX_Data.dateTimeToISODateString(getDateTimeValue(strValue));
NativeMethods.GMW_NV_SetValue(pGMNV, item.CRMFieldName, strValue);
}
else
NativeMethods.GMW_NV_SetValue(pGMNV, item.CRMFieldName, getTrimValue(strValue, item.MaxLength, item.CrmDataType));
if (item.CRMFieldName.Equals("UHSWSITEVS", StringComparison.OrdinalIgnoreCase))
strNumVisits = strValue;
if (item.CRMFieldName.Equals("UHSTOTPGEV", StringComparison.OrdinalIgnoreCase))
strNumPageView = strValue;
}
}
if (!string.IsNullOrEmpty(strNumPageView) && !string.IsNullOrEmpty(strNumVisits))
{
strValue = (getAveragePagesViewed(Convert.ToInt16(strNumVisits), Convert.ToInt16(strNumPageView))).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("UHSVID", hubSpotVid);
NativeMethods.GMW_NV_Delete(pGMNV);
if (string.IsNullOrEmpty(strRecId))
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "pushContactsToCRM: Cannot find RecId.");
else
{
strAccountNo = getAccountNo(strRecId);
updRecentUpdate(strAccountNo, dInsertDate);
insertContactHubspotFormSubmissionRecord(strAccountNo, aContactRecord);
}
}
else
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "pushContactsToCRM: WriteContact Failed.");
}
else
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "pushContactsToCRM: 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;
}
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();
}
private void insertContactHubspotForm_SubmissionRecord(int leadId, HubSpotLead aContactRecord)
{
HubSpotMap LeadConversionEvent = getMap("LeadConversionEvent");
ArrayList guids = new ArrayList();
string sqlQuery = "conv_leadid = " + leadId + ""; //Didn't include criteria to check for records that haven't been deleted on purpose. We want all guids added to the list regardless of whether they have been deleted.
queryresult entityResult = binding.query(sqlQuery, "LeadConversionEvent");
ewarebase[] leadConversionEventBase = entityResult.records;
if (leadConversionEventBase != null)
{
//ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "leadConversionEventBase.Length: " + leadId + " - " + leadConversionEventBase.Length);
for (int i = 0; i < leadConversionEventBase.Length; i++)
{
leadconversionevent leadConversionEvent = (leadconversionevent)leadConversionEventBase[i];
if (guids.Contains(leadConversionEvent.guid) == false)
{
guids.Add(leadConversionEvent.guid);
}
}
}
int counter = 0;
List<ewarebase> CRMBase = new List<ewarebase>();
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "contactRecord.ContactForm_Submission.Count: " + leadId + " - " + aContactRecord.ContactForm_Submissions.Count);
foreach (HubSpotContactForm_Submission contactForm_Submission in aContactRecord.ContactForm_Submissions)
{
if (guids.Contains(contactForm_Submission.Conversion_Id) == false)
{
leadconversionevent CRMLeadConversionEvent = new leadconversionevent();
CRMLeadConversionEvent.leadid = leadId;
CRMLeadConversionEvent.leadidSpecified = true;
CRMLeadConversionEvent.conversiondate = contactForm_Submission.TimeStamp;
CRMLeadConversionEvent.conversiondateSpecified = true;
CRMLeadConversionEvent.formid = getTrimValue(LeadConversionEvent.getItem("formid"), contactForm_Submission.Form_Id);
CRMLeadConversionEvent.formguid = getTrimValue(LeadConversionEvent.getItem("formguid"), contactForm_Submission.Form_Id);
CRMLeadConversionEvent.formname = getTrimValue(LeadConversionEvent.getItem("formname"), contactForm_Submission.Page_Title);
CRMLeadConversionEvent.formvalues = buildFormValues(contactForm_Submission.Conversion_Id, aContactRecord); //leadConversionEvent.FormValuesString.Replace("/n/r", Environment.NewLine).Replace("/n", Environment.NewLine);
CRMLeadConversionEvent.guid = getTrimValue(LeadConversionEvent.getItem("guid"), contactForm_Submission.Conversion_Id);
CRMLeadConversionEvent.leadguid = getTrimValue(LeadConversionEvent.getItem("LEADGUID"), aContactRecord.ContactDetailRecord.getStringValue("vid"));
CRMLeadConversionEvent.pagename = getTrimValue(LeadConversionEvent.getItem("pagename"), contactForm_Submission.Page_Title);
CRMLeadConversionEvent.pagetype = getTrimValue(LeadConversionEvent.getItem("pagetype"), contactForm_Submission.Page_Id);
CRMLeadConversionEvent.pageurl = getTrimValue(LeadConversionEvent.getItem("pageurl"), contactForm_Submission.Page_Url);
CRMLeadConversionEvent.portalid = getTrimValue(LeadConversionEvent.getItem("portalid"), contactForm_Submission.Portal_Id);
CRMBase.Add(CRMLeadConversionEvent);
counter++;
}
}
if (CRMBase.Count > 0)
{
try
{
addresult CRMAddResult = binding.add("LeadConversionEvent", CRMBase.ToArray());
}
catch (Exception e)
{
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "EXCEPTION IN insertHubspotContactForm_SubmissionRecord", e);
}
}
}
//***** 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 insertHubspotLeadConversionEventRecord(int leadId, HubSpotLead leadRecord)
{
Dictionary<string, int> trimMap = getTrimMap(getMap("LeadConversionEvent"));
ArrayList guids = new ArrayList();
string sqlQuery = "conv_leadid = " + leadId + ""; //Didn't include criteria to check for records that haven't been deleted on purpose. We want all guids added to the list regardless of whether they have been deleted.
queryresult entityResult = binding.query(sqlQuery, "LeadConversionEvent");
ewarebase[] leadConversionEventBase = entityResult.records;
if (leadConversionEventBase != null)
{
//ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "leadConversionEventBase.Length: " + leadId + " - " + leadConversionEventBase.Length);
for(int i = 0; i < leadConversionEventBase.Length; i++)
{
leadconversionevent leadConversionEvent = (leadconversionevent)leadConversionEventBase[i];
if(guids.Contains(leadConversionEvent.guid) == false)
{
guids.Add(leadConversionEvent.guid);
}
}
}
int counter = 0;
List<ewarebase> CRMBase = new List<ewarebase>();
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "leadRecord.LeadConversionEvents.Count: " + leadId + " - " + leadRecord.LeadConversionEvents.Count);
foreach (HubSpotLeadConversionEvent leadConversionEvent in leadRecord.LeadConversionEvents)
{
if (guids.Contains(leadConversionEvent.getStringValue("guid")) == false)
{
leadconversionevent CRMLeadConversionEvent = new leadconversionevent();
CRMLeadConversionEvent.leadid = leadId;
CRMLeadConversionEvent.leadidSpecified = true;
CRMLeadConversionEvent.conversiondate = leadConversionEvent.getDateTimeValueInLocalTime("convertDate");
CRMLeadConversionEvent.conversiondateSpecified = true;
CRMLeadConversionEvent.formid = getTrimmedValue(trimMap, "FORMID", leadConversionEvent.getStringValue("formId"));
CRMLeadConversionEvent.formguid = getTrimmedValue(trimMap, "FORMGUID", leadConversionEvent.getStringValue("formGuid"));
CRMLeadConversionEvent.formname = getTrimmedValue(trimMap, "FORMNAME", leadConversionEvent.getStringValue("formName"));
CRMLeadConversionEvent.formvalues = leadConversionEvent.FormValuesString.Replace("/n/r", Environment.NewLine).Replace("/n", Environment.NewLine);
CRMLeadConversionEvent.guid = getTrimmedValue(trimMap, "GUID", leadConversionEvent.getStringValue("guid"));
CRMLeadConversionEvent.leadguid = getTrimmedValue(trimMap, "LEADGUID", leadConversionEvent.getStringValue("leadGuid"));
CRMLeadConversionEvent.pagename = getTrimmedValue(trimMap, "PAGENAME", leadConversionEvent.getStringValue("pageName"));
CRMLeadConversionEvent.pagetype = getTrimmedValue(trimMap, "PAGETYPE", leadConversionEvent.getStringValue("pageType"));
CRMLeadConversionEvent.pageurl = getTrimmedValue(trimMap, "PAGEURL", leadConversionEvent.getStringValue("pageUrl"));
CRMLeadConversionEvent.portalid = getTrimmedValue(trimMap, "PORTALID", leadConversionEvent.getStringValue("portalId"));
CRMBase.Add(CRMLeadConversionEvent);
counter++;
}
}
if (CRMBase.Count > 0)
{
try
{
addresult CRMAddResult = binding.add("LeadConversionEvent", CRMBase.ToArray());
}
catch (Exception e)
{
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "EXCEPTION IN insertHubspotLeadConversionEventRecord", e);
}
}
}
/*
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 int pushContactsToCRM()
{
login(); // performs web service (SOAP) login to Sage CRM
ArrayList hubSpotGuids = new ArrayList();
mapLead = getMap("Lead");
DataSet ds = getLeadsToProcess(); // select * from HubSpotLeadStaging where Processed_Timestamp is null
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "Number of Contacts in SELECT: " + ds.Tables[0].Rows.Count);
for (int i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
{
HubSpotLead aContactRecord = new HubSpotLead(ds.Tables[0].Rows[i]["JSON_Data"].ToString());
string hubSpotLeadGuid = aContactRecord.ContactIdentity_Profiles[0].current_Lead_Guid;
string hubSpotVid = aContactRecord.ContactDetailRecord.getStringValue("vid");
// KVW added 05132013
string hubSpotEmail = aContactRecord.ContactIdentity_Profiles[0].Email;
// KVW added 05132013
strPublicLink = aContactRecord.ContactDetailRecord.getStringValue("profile-url");
DateTime dInsertDate = getDateTimeValue(aContactRecord.ContactHeaderRecord.getStringValue("addedAt"));
dInsertDate = dInsertDate.AddMilliseconds(- dInsertDate.Millisecond);
ds.Tables[0].Rows[i]["Processed_Timestamp"] = SLX_Data.getNewTimestamp();
string sqlQuery = "lead_deleted is null and lead_hubspotvid = '" + hubSpotVid + "'";
queryresult entityResult = binding.query(sqlQuery, "Lead");
ewarebase[] leadBase = entityResult.records;
if (leadBase == null && !string.IsNullOrEmpty(hubSpotLeadGuid))
{
sqlQuery = "lead_deleted is null and lead_hubspotguid = '" + hubSpotLeadGuid + "'";
entityResult = binding.query(sqlQuery, "Lead");
leadBase = entityResult.records;
}
// KVW added 05132013
if (leadBase == null && !string.IsNullOrEmpty(hubSpotLeadGuid))
{
sqlQuery = "lead_deleted is null and lead_personemail = '" + hubSpotEmail + "'";
entityResult = binding.query(sqlQuery, "Lead");
leadBase = entityResult.records;
}
// KVW added 05132013
this.dtRecentUpdate = DateTime.MinValue;
if (leadBase != null)
{
lead CRMLead = (lead)leadBase[0];
this.dtRecentUpdate = CRMLead.hubspotrecentupdate;
}
if (dInsertDate > dtRecentUpdate)
{
strFormName = "";
strConversionEvents = "";
strFirst_Conversion_Date = "";
strLast_Conversion_Date = "0";
if (aContactRecord.ContactForm_Submissions.Count > 0)
{
strConversionEvents = aContactRecord.ContactForm_Submissions.Count.ToString();
foreach (HubSpotContactForm_Submission ContactForm_Submission in aContactRecord.ContactForm_Submissions)
{
string sTimeStamp = aContactRecord.getStringValueFromValue(ContactForm_Submission.Json["timestamp"].ToString());
if (Convert.ToInt64(sTimeStamp) > Convert.ToInt64(strLast_Conversion_Date))
{
strFormName = ContactForm_Submission.Title;
strLast_Conversion_Date = aContactRecord.getStringValueFromValue(ContactForm_Submission.Json["timestamp"].ToString());
}
if (strFirst_Conversion_Date == "" || Convert.ToInt64(sTimeStamp) < Convert.ToInt64(strFirst_Conversion_Date))
strFirst_Conversion_Date = aContactRecord.getStringValueFromValue(ContactForm_Submission.Json["timestamp"].ToString());
}
}
if (leadBase == null)
{
// add lead to crm
ewarebase[] CRMBase = new ewarebase[1];
lead CRMLead = new lead();
CRMBase[0] = setLeadFields(CRMLead, aContactRecord, true);
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];
insertContactHubspotForm_SubmissionRecord(id.crmid1, aContactRecord);
}
hubSpotGuids.Add(hubSpotVid);
}
catch (SoapHeaderException e)
{
//string msg = e.Message;
ULogging.writeToErrorLog(AppGlobal.getAppLogger(), "SageCRM Logon Exception: hubSpotVid = " + hubSpotVid + " - " + e.Message);
}
}
else
{
// if lead already exists, update LeadConversionEvents field
lead CRMLead = (lead)leadBase[0];
ewarebase[] leadList = new ewarebase[1];
leadList[0] = setLeadFields(CRMLead, aContactRecord, false);
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];
insertContactHubspotForm_SubmissionRecord(CRMLeadRecord.leadid, aContactRecord);
}
}
}
catch (SoapHeaderException e)
{
//string msg = e.Message;
ULogging.writeToErrorLog(AppGlobal.getAppLogger(), "SageCRM Logon Exception: hubSpotVid = " + hubSpotVid + " - " + e.Message);
}
hubSpotGuids.Add(hubSpotVid);
}
}
}
//saveProcessedLeads(ds);
saveProcessedRecords(ds, "hubspotleadstaging");
logoff();
//ADD CODE TO UPDATE STAGING WITH LEAD ID
updateStagedLeadsWithParentId(hubSpotGuids, "LEAD", "LEAD_LEADID", "LEAD_HUBSPOTGUID");
return 0;
}
private void insertContactHubspotForm_SubmissionRecord(string table, string parentId, HubSpotLead aContactRecord)
{
if (isAllowedConversionEventTable(table) == false)
{
throw new ArgumentException(table + " is not a valid conversion event table");
}
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "BEGIN (inside) ContactForm_Submission: " + aContactRecord.ContactDetailRecord.getStringValue("vid"));
Dictionary<string, int> trimMap = getTrimMap(getMap(table + "_Conversion_Event"));
foreach (HubSpotContactForm_Submission contactForm_Submission in aContactRecord.ContactForm_Submissions)
{
string guid = contactForm_Submission.Conversion_Id;
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(contactForm_Submission.TimeStamp);
//ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "conversion date: " + SLX_Data.dateTimeToISODateString(contactForm_Submission.TimeStamp));
OleDbParameter p4 = new OleDbParameter("@FORM_ID", OleDbType.VarChar, getTrimMapValue(trimMap, "FORM_ID"));
p4.Value = contactForm_Submission.Form_Id;
//ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "formid: " + contactForm_Submission.Form_Id);
OleDbParameter p5 = new OleDbParameter("@FORM_GUID", OleDbType.VarChar, getTrimMapValue(trimMap, "FORM_GUID"));
p5.Value = contactForm_Submission.Page_Id;
//ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "form guid: " + contactForm_Submission.Page_Id);
OleDbParameter p6 = new OleDbParameter("@FORM_NAME", OleDbType.VarChar, getTrimMapValue(trimMap, "FORM_NAME"));
p6.Value = contactForm_Submission.Page_Title;
//ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "form name: " + contactForm_Submission.Page_Title);
OleDbParameter p7 = new OleDbParameter("@FORM_VALUES", OleDbType.VarChar, getTrimMapValue(trimMap, "FORM_VALUES"));
p7.Value = buildFormValues(guid, aContactRecord);
//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 = contactForm_Submission.Page_Title;
//ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "pagename: " + contactForm_Submission.Page_Title);
OleDbParameter p11 = new OleDbParameter("@PAGE_TYPE", OleDbType.VarChar, getTrimMapValue(trimMap, "PAGE_TYPE"));
p11.Value = contactForm_Submission.Page_Id;
//ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "pagetype: " + contactForm_Submission.Page_Id);
OleDbParameter p12 = new OleDbParameter("@PAGE_URL", OleDbType.VarChar, getTrimMapValue(trimMap, "PAGE_URL"));
p12.Value = contactForm_Submission.Page_Url;
//ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "pageUrl: " + contactForm_Submission.Page_Url);
OleDbParameter p13 = new OleDbParameter("@PORTAL_ID", OleDbType.VarChar, getTrimMapValue(trimMap, "PORTAL_ID"));
p13.Value = contactForm_Submission.Portal_Id;
//ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "portalId: " + contactForm_Submission.Portal_Id);
// 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 insertContactHubspotForm_SubmissionRecord: " + contactForm_Submission.Conversion_Id);
}
}
}
//***** 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;
}
private void insertContactLeadAddressRecord(String leadId, HubSpotLead aContactRecord)
{
HubSpotMap mapLeadAddress = getMap("Lead_Address");
HubSpotMapItem item;
string leadAddressId = SLX_Data.newSLXID("LEAD_ADDRESS", slxConnection);
string strFields;
string strParms;
// Default fields
strFields = "( LEADID, LEAD_ADDRESSID, ISMAILING, ISPRIMARY, ";
strParms = "( ?, ?, ?, ?, ";
// Hubspot mapping fields
foreach (DictionaryEntry entry in mapLeadAddress.Items)
{
item = (HubSpotMapItem)entry.Value;
if (!String.IsNullOrEmpty(item.HubSpotFieldName))
{
strFields += item.CRMFieldName + ", ";
strParms += "?, ";
}
}
// Last field
strFields += "DESCRIPTION, CREATEUSER, CREATEDATE, MODIFYUSER, MODIFYDATE )";
strParms += "?, ?, ?, ?, ? )";
string sql = "INSERT INTO LEAD_ADDRESS " + strFields + " values " + strParms;
OleDbCommand insertCmd = new OleDbCommand(sql, slxConnection);
OleDbParameter parm;
// Default Parameters
item = mapLeadAddress.getItem("LEADID");
parm = new OleDbParameter("@LEADID", OleDbType.VarChar, item.MaxLength);
parm.Value = leadId;
insertCmd.Parameters.Add(parm);
item = mapLeadAddress.getItem("LEAD_ADDRESSID");
parm = new OleDbParameter("@LEAD_ADDRESSID", OleDbType.VarChar, item.MaxLength);
parm.Value = leadAddressId;
insertCmd.Parameters.Add(parm);
item = mapLeadAddress.getItem("ISMAILING");
parm = new OleDbParameter("@ISMAILING", OleDbType.VarChar, item.MaxLength);
parm.Value = "T";
insertCmd.Parameters.Add(parm);
item = mapLeadAddress.getItem("ISPRIMARY");
parm = new OleDbParameter("@ISPRIMARY", OleDbType.VarChar, item.MaxLength);
parm.Value = "T";
insertCmd.Parameters.Add(parm);
// Hubspot mapping parameters
foreach (DictionaryEntry entry in mapLeadAddress.Items)
{
item = (HubSpotMapItem)entry.Value;
if (!String.IsNullOrEmpty(item.HubSpotFieldName))
{
string strValue = aContactRecord.ContactProperties.getContactPropertyStringValue(item.HubSpotFieldName);
int intLen = item.MaxLength;
if (!String.IsNullOrEmpty(strValue) && item.CrmDataType.Equals("datetime", StringComparison.OrdinalIgnoreCase))
{
strValue = SLX_Data.dateTimeToISODateString(getDateTimeValue(strValue));
intLen = 30;
}
parm = new OleDbParameter("@" + item.CRMFieldName, OleDbType.VarChar, intLen);
parm.Value = getTrimValue(strValue, intLen, item.CrmDataType);
insertCmd.Parameters.Add(parm);
}
}
item = mapLeadAddress.getItem("DESCRIPTION");
parm = new OleDbParameter("@DESCRIPTION", OleDbType.VarChar, item.MaxLength);
parm.Value = "Mailing";
insertCmd.Parameters.Add(parm);
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();
}
//***** MHM Contacts – Added *****//
private void insertContactHubspotFormSubmissionRecord(string strAccountNo, HubSpotLead aContactRecord)
{
ArrayList guids = getLeadConversionGuids(strAccountNo);
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "contactRecord.LeadConversionEvents.Count: " + strAccountNo + " - " + aContactRecord.ContactForm_Submissions.Count);
foreach (HubSpotContactForm_Submission contactForm_Submission in aContactRecord.ContactForm_Submissions)
{
if (guids.Contains(contactForm_Submission.Conversion_Id) == false)
{
// Create a new NV container
pGMNV = NativeMethods.GMW_NV_Create();
if (pGMNV != null)
{
NativeMethods.GMW_NV_SetValue(pGMNV, "AccountNo", strAccountNo.Trim());
NativeMethods.GMW_NV_SetValue(pGMNV, "detail", "HubSpot");
//NativeMethods.GMW_NV_SetValue(pGMNV, "zip", SLX_Data.dateTimeToISODateString(contactForm_Submission.TimeStamp));
//NativeMethods.GMW_NV_SetValue(pGMNV, "ext", contactForm_Submission.Form_Id);
//NativeMethods.GMW_NV_SetValue(pGMNV, "contsupref", contactForm_Submission.Title);
//NativeMethods.GMW_NV_SetValue(pGMNV, "ADDRESS1", contactForm_Submission.Page_Title);
//NativeMethods.GMW_NV_SetValue(pGMNV, "COUNTRY", contactForm_Submission.Portal_Id);
NativeMethods.GMW_NV_SetValue(pGMNV, "notes", "Form Id: " + contactForm_Submission.Form_Id + Environment.NewLine + Environment.NewLine +
"Page Id: " + contactForm_Submission.Page_Id + Environment.NewLine + Environment.NewLine +
"Page URL: " + contactForm_Submission.Page_Url + Environment.NewLine + Environment.NewLine +
buildFormValues(contactForm_Submission.Conversion_Id, aContactRecord));
NativeMethods.GMW_NV_SetValue(pGMNV, "UField11", contactForm_Submission.Conversion_Id);
// Exec the WriteContact function
if (NativeMethods.GMW_Execute("WriteDetail", pGMNV) > 0)
{
NativeMethods.GMW_NV_Delete(pGMNV);
updLeadContactEvent(contactForm_Submission.Conversion_Id, contactForm_Submission);
}
else
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "insertHubspotLeadConversionEventRecord: WriteDetail Failed.");
}
else
ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "insertHubspotLeadConversionEventRecord: Could not create NV container.");
}
}
}