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."); } } }