public void addItem(HubSpotMapItem item)
{
if (item == null)
{
//Throw exception or return. One of the two
}
if (items == null)
{
this.items = new Hashtable();
}
items.Add(UCollection.getCollectionKey(item.CRMFieldName), item);
}
//***** MHM Contacts – Added *****//
private DataRow getHubSpotMappingRow(string table, HubSpotMapItem item)
{
DataRow dRow = null;
SqlConnection connection = new SqlConnection(ConnectionString);
DataSet ds = new DataSet();
USQLServer.openConnection(connection);
if (table.ToUpper().Contains("HUBSPOT"))
table = "HUBSPOT";
string query = "";
switch (AppGlobal.getDatabaseVersion(UConfig.getAppConfigValue("DatabaseVersion", false)))
{
case DatabaseVersion.SQL2000:
query += "SELECT * FROM HUBSPOT_MAPPING WHERE CRM_TABLENAME='" + table + "' and CRM_FIELDNAME='" + item.CRMFieldName + "'";
break;
default:
query += "SELECT * FROM HUBSPOT_MAPPING WHERE CRM_TABLENAME='" + table + "' and CRM_FIELDNAME='" + item.CRMFieldName + "'";
break;
}
SqlCommand cmd = new SqlCommand(query, connection);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(ds);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
dRow = ds.Tables[0].Rows[i];
}
adapter.Dispose();
USQLServer.closeConnection(connection);
connection.Dispose();
return dRow;
}
//***** 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);
}
//***** 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);
}
private string getTrimValue(HubSpotMapItem item, string strValue)
{
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;
}
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;
}
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));
}
}
