//***** MHM Contacts *************// private void loadLeadDataMaps() { //Could modify to accommodate SQL 2000 if needed but for now 2005 and above Hashtable tempItems = new Hashtable(); SqlConnection connection = new SqlConnection(ConnectionString); DataSet ds = new DataSet(); USQLServer.openConnection(connection); string query = ""; switch (AppGlobal.getDatabaseVersion(UConfig.getAppConfigValue("DatabaseVersion", false))) { case DatabaseVersion.SQL2000: query += "SELECT " + "T.NAME AS TABLE_NAME, " + "C.NAME AS COLUMN_NAME, " + "TYPES.NAME AS FIELD_TYPE, " + "C.LENGTH AS MAX_LENGTH, " + "C.ISNULLABLE AS IS_NULLABLE " + "FROM " + "SYSOBJECTS T INNER JOIN SYSCOLUMNS C ON T.ID = C.ID INNER JOIN " + "SYSTYPES TYPES ON C.XTYPE = TYPES.XUSERTYPE " + "WHERE " + "T.NAME IN ('LEAD','LEAD_ADDRESS','LEAD_HUBSPOT', 'LEAD_CONVERSION_EVENT', 'ACCOUNT_HUBSPOT', 'ACCOUNT_CONVERSION_EVENT','CONTACT_HUBSPOT', 'CONTACT_CONVERSION_EVENT') " + "ORDER BY " + "T.NAME, " + "C.NAME"; break; default: query += "SELECT " + "T.NAME AS TABLE_NAME, " + "C.NAME AS COLUMN_NAME, " + "TYPES.NAME AS FIELD_TYPE, " + "C.MAX_LENGTH AS MAX_LENGTH, " + "C.IS_NULLABLE AS IS_NULLABLE " + "FROM " + "SYS.TABLES T INNER JOIN SYS.COLUMNS C ON T.OBJECT_ID = C.OBJECT_ID INNER JOIN " + "SYS.TYPES TYPES ON C.USER_TYPE_ID = TYPES.USER_TYPE_ID " + "WHERE " + "T.NAME IN ('LEAD','LEAD_ADDRESS','LEAD_HUBSPOT', 'LEAD_CONVERSION_EVENT', 'ACCOUNT_HUBSPOT', 'ACCOUNT_CONVERSION_EVENT','CONTACT_HUBSPOT', 'CONTACT_CONVERSION_EVENT') " + "ORDER BY " + "T.NAME, " + "C.NAME"; break; } SqlCommand cmd = new SqlCommand(query, connection); SqlDataAdapter adapter = new SqlDataAdapter(cmd); ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "Data Map Items: " + cmd.CommandText); adapter.Fill(ds); for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { DataRow row = ds.Tables[0].Rows[i]; string tableName = row["TABLE_NAME"].ToString(); string crmFieldName = row["COLUMN_NAME"].ToString(); int maxWidth = int.Parse(row["MAX_LENGTH"].ToString()); HubSpotMapItem item = new HubSpotMapItem(null, crmFieldName, maxWidth); //***** MHM Contacts – Added *****// item.CrmDataType = row["FIELD_TYPE"].ToString(); //***** MHM Contacts *************// tempItems.Add(UCollection.getCollectionKey(tableName + "|" + crmFieldName), item); } adapter.Dispose(); USQLServer.closeConnection(connection); connection.Dispose(); //***** MHM Contacts – Added *****// if (UConfig.getAppConfigValue("HubSpotContact", false) != "true") { //***** MHM Contacts *************// //Load HubSpot map for Saleslogix and add matching hubspot field XmlTextReader reader = new XmlTextReader("SalesLogixHubSpotFieldMap.xml"); while (reader.Read()) { string key = UCollection.getCollectionKey(reader.GetAttribute("crmtablename") + "|" + reader.GetAttribute("crmfieldname")); HubSpotMapItem item = (HubSpotMapItem)tempItems[key]; if (item != null) { item.HubSpotFieldName = reader.GetAttribute("hubspotfieldname"); } } //***** MHM Contacts – Added *****// } //***** MHM Contacts *************// //Put temp map items in the correct map HubSpotMap leadMap = new HubSpotMap(UCollection.getCollectionKey("Lead")); HubSpotMap leadAddressMap = new HubSpotMap(UCollection.getCollectionKey("Lead_Address")); HubSpotMap leadHubSpotMap = new HubSpotMap(UCollection.getCollectionKey("Lead_HubSpot")); HubSpotMap leadConversionEventMap = new HubSpotMap(UCollection.getCollectionKey("Lead_Conversion_Event")); HubSpotMap accountHubSpotMap = new HubSpotMap(UCollection.getCollectionKey("Account_HubSpot")); HubSpotMap accountConversionEventMap = new HubSpotMap(UCollection.getCollectionKey("Account_Conversion_Event")); HubSpotMap contactHubSpotMap = new HubSpotMap(UCollection.getCollectionKey("Contact_HubSpot")); HubSpotMap contactConversionEventMap = new HubSpotMap(UCollection.getCollectionKey("Contact_Conversion_Event")); foreach (string key in tempItems.Keys) { string[] values = key.Split(new string[]{"|"}, StringSplitOptions.None); string table = values[0]; HubSpotMapItem item = (HubSpotMapItem) tempItems[key]; //***** MHM Contacts – Added *****// if (UConfig.getAppConfigValue("HubSpotContact", false) == "true") { DataRow dRow = getHubSpotMappingRow(table, item); if (dRow != null) { item.HubSpotFieldName = dRow["HUBSPOT_NAME"].ToString(); item.HubSpotDateType = dRow["HUBSPOT_TYPE"].ToString(); if (string.IsNullOrEmpty(dRow["CRM_UPDATE"].ToString())) item.CrmUpdate = false; else item.CrmUpdate = Convert.ToBoolean(dRow["CRM_UPDATE"].ToString()); } } //***** MHM Contacts *************// if (table.Equals("Lead", StringComparison.OrdinalIgnoreCase) == true) { leadMap.addItem(item); } else if (table.Equals("Lead_Address", StringComparison.OrdinalIgnoreCase) == true) { leadAddressMap.addItem(item); } else if (table.Equals("Lead_Hubspot", StringComparison.OrdinalIgnoreCase) == true) { leadHubSpotMap.addItem(item); } else if (table.Equals("Lead_Conversion_Event", StringComparison.OrdinalIgnoreCase) == true) { leadConversionEventMap.addItem(item); } else if (table.Equals("Account_Hubspot", StringComparison.OrdinalIgnoreCase) == true) { accountHubSpotMap.addItem(item); } else if (table.Equals("Account_Conversion_Event", StringComparison.OrdinalIgnoreCase) == true) { accountConversionEventMap.addItem(item); } else if (table.Equals("Contact_Hubspot", StringComparison.OrdinalIgnoreCase) == true) { contactHubSpotMap.addItem(item); } else if (table.Equals("Contact_Conversion_Event", StringComparison.OrdinalIgnoreCase) == true) { contactConversionEventMap.addItem(item); } } addMap(leadMap); addMap(leadAddressMap); addMap(leadHubSpotMap); addMap(leadConversionEventMap); addMap(accountHubSpotMap); addMap(accountConversionEventMap); addMap(contactHubSpotMap); addMap(contactConversionEventMap); }
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; }
protected Dictionary<string, int> getTrimMap(HubSpotMap map) { Dictionary<string, int> dictionary = new Dictionary<string, int>(); if (map == null) { //Throw an exception ArgumentNullException e = new ArgumentNullException(UException.getExceptionMessage("AConnectorBase", "getTrimMap", "A HubSpot Map is required")); throw e; } foreach(string key in map.Items.Keys) { HubSpotMapItem item = (HubSpotMapItem)map.Items[key]; if (String.IsNullOrEmpty(item.CRMFieldName) == false && item.MaxLength > 0) { dictionary.Add(item.CRMFieldName.Trim().ToUpper(), item.MaxLength); } } return dictionary; }
//***** MHM Contacts *************// private void loadLeadDataMaps() { login(); Hashtable tempItems = new Hashtable(); Dictionary<string, crmfield[]> fieldsDictionary = new Dictionary<string, crmfield[]>(); fieldsDictionary.Add("Lead", leadResult.records); fieldsDictionary.Add("LeadConversionEvent", leadConversionEventResult.records); foreach (string key in fieldsDictionary.Keys) { crmfield[] fields = fieldsDictionary[key]; foreach (crmfield field in fields) { //ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "Data Map Items: " + cmd.CommandText); //string tableName = row["TABLE_NAME"].ToString(); //string crmFieldName = row["COLUMN_NAME"].ToString(); //int maxWidth = int.Parse(row["MAX_LENGTH"].ToString()); string tableName = key; string crmFieldName = field.name; int maxWidth = field.length; HubSpotMapItem item = new HubSpotMapItem(null, crmFieldName, maxWidth); //***** MHM Contacts – Added *****// item.CrmDataType = field.type; //***** MHM Contacts *************// tempItems.Add(UCollection.getCollectionKey(tableName + "|" + crmFieldName), item); } } //***** MHM Contacts – Added *****// if (UConfig.getAppConfigValue("HubSpotContact", false) != "true") { //***** MHM Contacts *************// //Load HubSpot map for Saleslogix and add matching hubspot field XmlTextReader reader = new XmlTextReader("SageCRMHubSpotFieldMap.xml"); while (reader.Read()) { string key = UCollection.getCollectionKey(reader.GetAttribute("crmtablename") + "|" + reader.GetAttribute("crmfieldname")); HubSpotMapItem item = (HubSpotMapItem)tempItems[key]; if (item != null) { item.HubSpotFieldName = reader.GetAttribute("hubspotfieldname"); } } //***** MHM Contacts – Added *****// } //***** MHM Contacts *************// //Put temp map items in the correct map HubSpotMap leadMap = new HubSpotMap(UCollection.getCollectionKey("Lead")); HubSpotMap leadConversionEventMap = new HubSpotMap(UCollection.getCollectionKey("LeadConversionEvent")); foreach (string key in tempItems.Keys) { string[] values = key.Split(new string[] { "|" }, StringSplitOptions.None); string table = values[0]; HubSpotMapItem item = (HubSpotMapItem)tempItems[key]; //***** MHM Contacts – Added *****// if (UConfig.getAppConfigValue("HubSpotContact", false) == "true" && table.Equals("Lead", StringComparison.OrdinalIgnoreCase)) { DataRow dRow = getHubSpotMappingRow(table, item); if (dRow != null) { item.HubSpotFieldName = dRow["HUBSPOT_NAME"].ToString(); item.HubSpotDateType = dRow["HUBSPOT_TYPE"].ToString(); if (string.IsNullOrEmpty(dRow["CRM_UPDATE"].ToString())) item.CrmUpdate = false; else item.CrmUpdate = Convert.ToBoolean(dRow["CRM_UPDATE"].ToString()); } } //***** MHM Contacts *************// if (table.Equals("Lead", StringComparison.OrdinalIgnoreCase) == true) { leadMap.addItem(item); } else if (table.Equals("LeadConversionEvent", StringComparison.OrdinalIgnoreCase) == true) { leadConversionEventMap.addItem(item); } } addMap(leadMap); addMap(leadConversionEventMap); }
protected void addMap(HubSpotMap map) { if (map == null) { //Probably want to change to throw an exception return; } string mapName = UCollection.getCollectionKey(map.Name); this.hubSpotMaps.Add(mapName, map); }