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