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