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 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;
}
