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();
}
//***** 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;
}