//***** 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);
        }
示例#2
0
        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;
        }
示例#3
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;
        }
示例#4
0
        //***** 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);
        }
示例#5
0
        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);
        }