コード例 #1
0
        /*
        private bool hasNewConversionEventRecords(HubSpotLead leadRecord)
        {
            if(leadRecord == null)
            {
                throw new NullReferenceException("hasNewConversionEventRecords requires a valid lead record to be passed in");
            }

            bool returnValue = true;
            int newConversionEventCount = 0;

            foreach (HubSpotLeadConversionEvent leadConversionEvent in leadRecord.LeadConversionEvents)
            {
                string guid = leadConversionEvent.getStringValue("guid");
                string countSQL = "";
                int leadConversionEventCount = 0;
                int contactConversionEventCount = 0;

                countSQL = String.Format("SELECT COUNT(*) FROM {0}_CONVERSION_EVENT WHERE GUID = '{1}'", new string[] { "LEAD", guid });

                leadConversionEventCount += UOleDB.countRecords(countSQL, slxConnection);

                countSQL = String.Format("SELECT COUNT(*) FROM {0}_CONVERSION_EVENT WHERE GUID = '{1}'", new string[] { "CONTACT", guid });

                contactConversionEventCount += UOleDB.countRecords(countSQL, slxConnection);

                if (leadConversionEventCount == 0 && contactConversionEventCount == 0)
                {
                    newConversionEventCount++;
                }
            }

            if (newConversionEventCount == 0)
            {
                returnValue = false;
            }

            return returnValue;
        } */
        private void updateHubSpotRecord(string table, string parentId, HubSpotLead leadRecord)
        {
            if (isAllowedConversionEventTable(table) == false)
            {
                throw new ArgumentException(table + " is not a valid conversion event table");
            }

            //Open the connection just in case
            UOleDB.openConnection(slxConnection);

            string countQuery = "select count(*) from " + table + "_hubspot where " + table + "id = '{0}'";

            int recordCount = UOleDB.countRecords(String.Format(countQuery, parentId), slxConnection);

            if (recordCount == 0)
            {
                insertHubSpotRecord(table, parentId, leadRecord);

                return;
            }

            //Order of parameters is critical due to issues with SLX provider
            Dictionary<string, int> trimMap = getTrimMap(getMap(table + "_HubSpot"));
            int recordIndex = 0;
            HubSpotLeadConversionEvent leadConversionEvent = (HubSpotLeadConversionEvent)leadRecord.LeadConversionEvents[recordIndex];
            string sql = "UPDATE " + table + "_HUBSPOT SET " +
                                "RECENT_CONVERT_DATE = ?, " +
                                "WEBSITE_VISITS = ?, " +
                                "UNIQUE_PAGES_VIEWED = ?, " +
                                "TOTAL_PAGES_VIEWED = ?, " +
                                "AVERAGE_PAGES_VIEWED = ?, " +
                                "FORM_NAME = ?, " +
                                "FOUND_SITE_VIA = ?, " +
                                "FULL_FOUND_VIA_STRING = ?, " +
                                "LEAD_GRADE = ?, " +
                                "JSON_DATA = ?, " +
                                "MODIFYUSER = ?, " +
                                "MODIFYDATE = ? " +
                            "WHERE " +
                                table + "ID = ?";

            OleDbCommand updateCmd = new OleDbCommand(sql, slxConnection);

            OleDbParameter p2 = new OleDbParameter("@RECENT_CONVERT_DATE", OleDbType.DBTimeStamp);
            p2.Value = SLX_Data.dateTimeToISODateString(leadRecord.getDateTimeValueInLocalTime("lastConvertedAt"));
            ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "recent convert date: " + SLX_Data.dateTimeToISODateString(leadRecord.getDateTimeValueInLocalTime("lastConvertedAt")));

            OleDbParameter p3 = new OleDbParameter("@WEBSITE_VISITS", OleDbType.Integer);
            p3.Value = leadRecord.AnalyticDetails.getStringValue("visitCount");
            ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "website visits: " + leadRecord.AnalyticDetails.getStringValue("visitCount"));

            OleDbParameter p4 = new OleDbParameter("@UNIQUE_PAGES_VIEWED", OleDbType.Integer);
            //p4.Value = leadRecord.AnalyticDetails.getStringValue("pageViewCount");

            OleDbParameter p5 = new OleDbParameter("@TOTAL_PAGES_VIEWED", OleDbType.Integer);
            p5.Value = leadRecord.AnalyticDetails.getStringValue("pageViewCount");
            ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "total pages viewed: " + leadRecord.AnalyticDetails.getStringValue("pageViewCount"));

            OleDbParameter p6 = new OleDbParameter("@AVERAGE_PAGE_VIEWS", OleDbType.Double);
            p6.Value = getAveragePagesViewed(leadRecord.AnalyticDetails.getIntegerValue("visitCount"), leadRecord.AnalyticDetails.getIntegerValue("pageViewCount"));
            ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "average page views: " + getAveragePagesViewed(leadRecord.AnalyticDetails.getIntegerValue("visitCount"), leadRecord.AnalyticDetails.getIntegerValue("pageViewCount")));

            OleDbParameter p7 = new OleDbParameter("@FORM_NAME", OleDbType.VarChar, getTrimMapValue(trimMap, "Form_Name"));
            p7.Value = leadConversionEvent.getStringValue("formName");
            ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "form name: " + leadConversionEvent.getStringValue("formName"));

            OleDbParameter p8 = new OleDbParameter("@FOUND_SITE_VIA", OleDbType.VarChar, getTrimMapValue(trimMap, "Found_Site_Via"));
            p8.Value = leadRecord.getStringValue("foundVia");
            ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "found site via: " + leadRecord.getStringValue("foundVia"));

            OleDbParameter p9 = new OleDbParameter("@FULL_FOUND_VIA_STRING", OleDbType.VarChar, getTrimMapValue(trimMap, "Full_Found_Via_String"));
            p9.Value = leadRecord.getStringValue("fullFoundViaString");
            ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "full found via string: " + leadRecord.getStringValue("fullFoundViaString"));

            OleDbParameter p10 = new OleDbParameter("@LEAD_GRADE", OleDbType.Decimal);
            p10.Value = leadRecord.getDecimalValue("score");
            ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "lead grade: " + leadRecord.getDecimalValue("score"));

            OleDbParameter p11 = new OleDbParameter("@JSON_DATA", OleDbType.VarChar);
            p11.Value = leadRecord.JSONData;

            updateCmd.Parameters.Add(p2);
            updateCmd.Parameters.Add(p3);
            updateCmd.Parameters.Add(p4);
            updateCmd.Parameters.Add(p5);
            updateCmd.Parameters.Add(p6);
            updateCmd.Parameters.Add(p7);
            updateCmd.Parameters.Add(p8);
            updateCmd.Parameters.Add(p9);
            updateCmd.Parameters.Add(p10);
            updateCmd.Parameters.Add(p11);

            addTimestampData(updateCmd, false);

            OleDbParameter p1 = new OleDbParameter("@ID", OleDbType.VarChar, 12);
            p1.Value = parentId;
            ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "parentId: " + parentId);

            updateCmd.Parameters.Add(p1);

            ULogging.writeToDebugLog(AppGlobal.getAppLogger(), updateCmd.CommandText);

            updateCmd.ExecuteNonQuery();

            ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "END Record Update: " + parentId);
        }
コード例 #2
0
        //***** MHM Contacts *************//
        private int pushLeadsToCRM()
        {
            login(); // performs web service (SOAP) login to Sage CRM

            Dictionary<string, int> trimMap = getTrimMap(getMap("Lead"));

            string description = "" + ConfigurationManager.AppSettings.Get("lead_description");
            string stage = "" + ConfigurationManager.AppSettings.Get("lead_stage");
            string source = "" + ConfigurationManager.AppSettings.Get("lead_source");
            string channelId = "" + ConfigurationManager.AppSettings.Get("SageCRMChannelId");
            ArrayList hubSpotGuids = new ArrayList();

            DataSet ds = getLeadsToProcess(); // select * from HubSpotLeadStaging where Processed_Timestamp is null

            for (int i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
            {
                HubSpotLead leadRecord = new HubSpotLead(ds.Tables[0].Rows[i]["JSON_Data"].ToString());
                HubSpotLeadConversionEvent leadConversionEvent;
                string sFormName = "";
                int recordIndex = 0;
                if (leadRecord.LeadConversionEvents.Count > 0)
                {
                    leadConversionEvent = (HubSpotLeadConversionEvent)leadRecord.LeadConversionEvents[recordIndex];
                    sFormName = leadConversionEvent.getStringValue("formName");
                }

                HubSpotLeadAnalyticDetails analyticalDetails = leadRecord.AnalyticDetails;

                ds.Tables[0].Rows[i]["Processed_Timestamp"] = SLX_Data.getNewTimestamp();

                //this is where we check to see if the hubspot lead guid exists in crm lead table.
                //if not then we add lead to crm
                string hubSpotGuid = leadRecord.getStringValue("guid");

                //ULogging.writeToDebugLog(AppGlobal.getAppLogger(), "getLeadsToProcess = " + hubSpotGuid);

                //this is where we check to see if the hubspot lead guid exists in crm lead table.
                //if not then we add lead to crm

                string sqlQuery = "lead_deleted is null and lead_hubspotguid = '" + hubSpotGuid + "'";
                queryresult entityResult = binding.query(sqlQuery, "Lead");
                ewarebase[] leadBase = entityResult.records;

                if (leadBase == null)
                {
                    // add lead to crm
                    ewarebase[] CRMBase = new ewarebase[1];
                    lead CRMLead = new lead();

                    // 201-06-10 NEED TO CLEANUP THE TRIM CALLS ON THE VARIOUS FIELDS
                    CRMLead.closed = leadRecord.getDateTimeValueInLocalTime("closedAt");
                    CRMLead.companyname = getTrimmedValue(trimMap, "COMPANYNAME", leadRecord.getStringValue("company"));
                    CRMLead.companycountry = getTrimmedValue(trimMap, "COMPANYCOUNTRY", leadRecord.getStringValue("country"));
                    //object country = getPicklistValue(typeof(lead_companycountry), leadRecord.getStringValue("country"));

                    //if (country != null)
                    //{
                    //    CRMLead.companycountry = (lead_companycountry)country;
                    //}

                    CRMLead.personemail = getTrimmedValue(trimMap, "PERSONEMAIL", leadRecord.getStringValue("email"));
                    CRMLead.personfaxareacode = getTrimmedValue(trimMap, "PERSONFAXAREACODE", UPhone.getFormattedAreaCode(leadRecord.getStringValue("fax"), null));
                    CRMLead.personfaxnumber = getTrimmedValue(trimMap, "PERSONFAXNUMBER", UPhone.getFormattedPhoneNumberWithoutAreaCode(leadRecord.getStringValue("fax"), null));
                    CRMLead.personfirstname = getTrimmedValue(trimMap, "PERSONFIRSTNAME", leadRecord.getStringValue("firstName"));
                    CRMLead.hubspot1stconversiondate = leadRecord.getDateTimeValueInLocalTime("firstVisitSetAt");
                    CRMLead.hubspot1stconversiondateSpecified = true;
                    CRMLead.hubspotfoundsitevia = getTrimmedValue(trimMap, "HUBSPOTFOUNDSITEVIA", leadRecord.getStringValue("foundVia"));
                    CRMLead.hubspotguid = getTrimmedValue(trimMap, "HUBSPOTGUID", leadRecord.getStringValue("guid"));
                    CRMLead.companyindustry = getTrimmedValue(trimMap, "COMPANYINDUSTRY", leadRecord.getStringValue("industry"));
                    //object industry = getPicklistValue(typeof(lead_companyindustry), leadRecord.getStringValue("industry"));

                    //if (industry != null)
                    //{
                    //    CRMLead.companyindustry = (lead_companyindustry)industry;
                    //}

                    CRMLead.hubspotipaddress = getTrimmedValue(trimMap, "HUBSPOTIPADDRESS", leadRecord.getStringValue("ipAddress"));
                    CRMLead.persontitle = getTrimmedValue(trimMap, "PERSONTITLE", leadRecord.getStringValue("jobTitle"));
                    CRMLead.hubspotrecentconvertdate = leadRecord.getDateTimeValueInLocalTime("lastConvertedAt");
                    CRMLead.hubspotrecentconvertdateSpecified = true;
                    CRMLead.personlastname = getTrimmedValue(trimMap, "PERSONLASTNAME", leadRecord.getStringValue("lastName"));
                    CRMLead.hubspotleaddetail = leadRecord.getStringValue("message");
                    CRMLead.personphoneareacode = getTrimmedValue(trimMap, "PERSONPHONEAREACODE", UPhone.getFormattedAreaCode(leadRecord.getStringValue("phone"), null));
                    CRMLead.personphonenumber = getTrimmedValue(trimMap, "PERSONPHONENUMBER", UPhone.getFormattedPhoneNumberWithoutAreaCode(leadRecord.getStringValue("phone"), null));
                    CRMLead.personsalutation = getTrimmedValue(trimMap, "PERSONSALUTATION", leadRecord.getStringValue("salutation"));
                    //object salutation = getPicklistValue(typeof(lead_personsalutation), leadRecord.getStringValue("salutation"));

                    //if (salutation != null)
                    //{
                    //    CRMLead.personsalutation = (lead_personsalutation)salutation;
                    //}

                    CRMLead.hubspotleadgrade = getTrimmedValue(trimMap, "HUBSPOTLEADGRADE", leadRecord.getStringValue("score")); //May want to change to decimal like SLX. Don't forget to set Specified flag to true
                    CRMLead.companystate = getTrimmedValue(trimMap, "COMPANYSTATE", leadRecord.getStringValue("state"));
                    CRMLead.companywebsite = getTrimmedValue(trimMap, "COMPANYWEBSITE", leadRecord.getStringValue("website"));
                    CRMLead.companypostcode = getTrimmedValue(trimMap, "COMPANYPOSTCODE", leadRecord.getStringValue("zip"));
                    CRMLead.companycity = getTrimmedValue(trimMap, "COMPANYCITY", leadRecord.getStringValue("city"));
                    CRMLead.description = description;
                    CRMLead.source = source;
                    //object leadSource = getPicklistValue(typeof(lead_source), source);

                    //if (leadSource != null)
                    //{
                    //    CRMLead.source = (lead_source)leadSource;
                    //}

                    CRMLead.stage = stage;
                    //object leadStage = getPicklistValue(typeof(lead_stage), stage);

                    //if(leadStage != null)
                    //{
                    //    CRMLead.stage = (lead_stage)leadStage;
                    //}

                    CRMLead.opened = leadRecord.getDateTimeValueInLocalTime("insertedAt");
                    CRMLead.openedSpecified = true;
                    CRMLead.details = leadRecord.getStringValue("message");
                    CRMLead.hubspotinsertedat = leadRecord.getDateTimeValueInLocalTime("insertedAt");
                    CRMLead.hubspotinsertedatSpecified = true;
                    CRMLead.hubspotfirsturl = leadRecord.getStringValue("firstUrl");
                    CRMLead.hubspotfullfoundviastring = leadRecord.getStringValue("fullFoundViaString");
                    CRMLead.hubspottwitterhandle = getTrimmedValue(trimMap, "HUBSPOTTWITTERHANDLE", leadRecord.getStringValue("twitterHandle"));
                    CRMLead.hubspotformname = getTrimmedValue(trimMap, "HUBSPOTFORMNAME", sFormName);
                    CRMLead.hubspotconversionevents = leadRecord.getIntegerValue("numConversionEvents");
                    CRMLead.hubspotconversioneventsSpecified = true;
                    CRMLead.hubspotpublicleadlink = getTrimmedValue(trimMap, "HUBSPOTPUBLICLEADLINK", leadRecord.getStringValue("publicLeadLink"));
                    CRMLead.hubspotfirstvisit = leadRecord.getDateTimeValueInLocalTime("firstVisitSetAt");
                    CRMLead.hubspotfirstvisitSpecified = true;
                    //CRMLead.assigneduserid = System.Convert.ToInt32(UConfig.getAppConfigValue("lead_assigneduserid", false));
                    CRMLead.assigneduserid = System.Convert.ToInt32(UConfig.getAppConfigValue("userid", false));
                    CRMLead.assigneduseridSpecified = true;

                    if (String.IsNullOrEmpty(channelId) == false)
                    {
                        CRMLead.channelid = System.Convert.ToInt32(channelId);
                        CRMLead.channelidSpecified = true;
                    }

                    CRMLead.hubspotwebsitevisits = analyticalDetails.getIntegerValue("visitCount");
                    CRMLead.hubspotwebsitevisitsSpecified = true;
                    CRMLead.hubspottotalpageviews = analyticalDetails.getIntegerValue("pageViewCount");
                    CRMLead.hubspottotalpageviewsSpecified = true;
                    CRMLead.hubspotaveragepageviews = Convert.ToDouble(getAveragePagesViewed(analyticalDetails.getIntegerValue("visitCount"), analyticalDetails.getIntegerValue("pageViewCount")));
                    CRMLead.hubspotaveragepageviewsSpecified = true;

                    CRMBase[0] = CRMLead;

                    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];

                            insertHubspotLeadConversionEventRecord(id.crmid1, leadRecord);
                        }

                        hubSpotGuids.Add(hubSpotGuid);
                    }
                    catch (SoapHeaderException e)
                    {
                        //string msg = e.Message;
                        ULogging.writeToErrorLog(AppGlobal.getAppLogger(), "SageCRM Logon Exception: hubSpotGuid = " +hubSpotGuid + " - " + e.Message);
                    }
                }
                else
                {
                    // if lead already exists, update LeadConversionEvents field
                    lead CRMLead = (lead)leadBase[0];

                    CRMLead.hubspotformname = getTrimmedValue(trimMap, "HUBSPOTFORMNAME", sFormName);
                    CRMLead.hubspotleadconvertevents = leadRecord.getStringValue("leadConversionEvents");
                    CRMLead.hubspotrecentconvertdate = leadRecord.getDateTimeValueInLocalTime("lastConvertedAt");
                    CRMLead.hubspotrecentconvertdateSpecified = true;
                    CRMLead.hubspotconversionevents = leadRecord.getIntegerValue("numConversionEvents");
                    CRMLead.hubspotconversioneventsSpecified = true;
                    CRMLead.hubspotwebsitevisits = analyticalDetails.getIntegerValue("visitCount");
                    CRMLead.hubspotwebsitevisitsSpecified = true;
                    CRMLead.hubspottotalpageviews = analyticalDetails.getIntegerValue("pageViewCount");
                    CRMLead.hubspottotalpageviewsSpecified = true;
                    CRMLead.hubspotaveragepageviews = Convert.ToDouble(getAveragePagesViewed(analyticalDetails.getIntegerValue("visitCount"), analyticalDetails.getIntegerValue("pageViewCount")));
                    CRMLead.hubspotaveragepageviewsSpecified = true;
                    CRMLead.hubspotfoundsitevia = getTrimmedValue(trimMap, "HUBSPOTFOUNDSITEVIA", leadRecord.getStringValue("foundVia"));
                    CRMLead.hubspotfullfoundviastring = leadRecord.getStringValue("fullFoundViaString");
                    CRMLead.hubspotleadgrade = getTrimmedValue(trimMap, "HUBSPOTLEADGRADE", leadRecord.getStringValue("score")); //May want to change to decimal like SLX. Don't forget to set Specified flag to true

                    ewarebase[] leadList = new ewarebase[1];

                    leadList[0] = CRMLead;

                    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];

                                insertHubspotLeadConversionEventRecord(CRMLeadRecord.leadid, leadRecord);
                            }
                        }
                    }
                    catch (SoapHeaderException e)
                    {
                        //string msg = e.Message;
                        ULogging.writeToErrorLog(AppGlobal.getAppLogger(), "SageCRM Logon Exception: hubSpotGuid = " + hubSpotGuid + " - " + e.Message);
                    }

                    hubSpotGuids.Add(hubSpotGuid);
                }
            }

            //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
        private void insertHubSpotRecord(string table, string recordId, HubSpotLead leadRecord)
        {
            //Open the connection just in case
            UOleDB.openConnection(slxConnection);

            Dictionary<string, int> trimMap = getTrimMap(getMap(table + "_HubSpot"));
            HubSpotLeadConversionEvent leadConversionEvent;
            string sFormName = "";
            int recordIndex = 0;
            if (leadRecord.LeadConversionEvents.Count > 0)
            {
                leadConversionEvent = (HubSpotLeadConversionEvent)leadRecord.LeadConversionEvents[recordIndex];
                sFormName = leadConversionEvent.getStringValue("formName");
            }
            HubSpotLeadAnalyticDetails analyticalDetails = leadRecord.AnalyticDetails;
            string sql = "INSERT INTO " + table + "_HUBSPOT(" +
                                        table + "ID, " +
                                        "SECCODEID, " +
                                        "CLOSED_AT, " +
                                        "FIRST_CONVERSION_DATE, " +
                                        "FOUND_SITE_VIA, " +
                                        "HUBSPOT_GUID, " +
                                        "IP_ADDRESS, " +
                                        "RECENT_CONVERT_DATE, " +
                                        "LEAD_DETAIL, " +
                                        "LEAD_GRADE, " +
                                        "FIRST_URL, " +
                                        "TWITTER_HANDLE, " +
                                        "FORM_NAME, " +
                                        "PUBLIC_LEAD_LINK, " +
                                        "FIRST_VISIT, " +
                                        "CONVERSION_COUNT, " +
                                        "FULL_FOUND_VIA_STRING, " +
                                        "INSERTED_AT, " +
                                        "WEBSITE_VISITS, " +
                                        "UNIQUE_PAGES_VIEWED, " +
                                        "TOTAL_PAGES_VIEWED, " +
                                        "AVERAGE_PAGES_VIEWED, " +
                                        "RECENT_VISIT, " +
                                        "PORTAL_ID, " +
                                        "JSON_DATA, " +
                                        "CREATEUSER, " +
                                        "CREATEDATE, " +
                                        "MODIFYUSER, " +
                                        "MODIFYDATE " +
                                    ") VALUES(" +
                                        "?, " +
                                        "?, " +
                                        "?, " +
                                        "?, " +
                                        "?, " +
                                        "?, " +
                                        "?, " +
                                        "?, " +
                                        "?, " +
                                        "?, " +
                                        "?, " +
                                        "?, " +
                                        "?, " +
                                        "?, " +
                                        "?, " +
                                        "?, " +
                                        "?, " +
                                        "?, " +
                                        "?, " +
                                        "?, " +
                                        "?, " +
                                        "?, " +
                                        "?, " +
                                        "?, " +
                                        "?, " +
                                        "?, " +
                                        "?, " +
                                        "?, " +
                                        "? " +
                                    ")";

            OleDbCommand insertCmd = new OleDbCommand(sql, slxConnection);

            OleDbParameter p1 = new OleDbParameter("@" + table + "ID", OleDbType.VarChar, 12);
            p1.Value = recordId;

            OleDbParameter p2 = new OleDbParameter("@SECCODEID", OleDbType.VarChar, 12);
            p2.Value = ConfigurationManager.AppSettings.Get("ownerid");

            OleDbParameter p3 = new OleDbParameter("@CLOSED_AT", OleDbType.DBTimeStamp);
            p3.Value = SLX_Data.dateTimeToISODateString(leadRecord.getDateTimeValueInLocalTime("closedAt"));

            OleDbParameter p4 = new OleDbParameter("@FIRST_CONVERSION_DATE", OleDbType.DBTimeStamp);
            p4.Value = SLX_Data.dateTimeToISODateString(leadRecord.getDateTimeValueInLocalTime("firstVisitSetAt"));

            OleDbParameter p5 = new OleDbParameter("@FOUND_SITE_VIA", OleDbType.VarChar, getTrimMapValue(trimMap, "Found_Site_Via"));
            p5.Value = leadRecord.getStringValue("foundVia");

            OleDbParameter p6 = new OleDbParameter("@HUBSPOT_GUID", OleDbType.VarChar, getTrimMapValue(trimMap, "HubSpot_GUID"));
            p6.Value = leadRecord.getStringValue("guid");

            OleDbParameter p7 = new OleDbParameter("@IP_ADDRESS", OleDbType.VarChar, getTrimMapValue(trimMap, "IP_Address"));
            p7.Value = leadRecord.getStringValue("ipAddress");

            OleDbParameter p8 = new OleDbParameter("@RECENT_CONVERT_DATE", OleDbType.DBTimeStamp);
            p8.Value = SLX_Data.dateTimeToISODateString(leadRecord.getDateTimeValueInLocalTime("lastConvertedAt"));

            OleDbParameter p9 = new OleDbParameter("@LEAD_DETAIL", OleDbType.LongVarBinary); //MEMO
            //p9.Value = leadRecord.getStringValue("message");

            OleDbParameter p10 = new OleDbParameter("@LEAD_GRADE", OleDbType.Decimal);
            p10.Value = leadRecord.getDecimalValue("score");

            OleDbParameter p11 = new OleDbParameter("@FIRST_URL", OleDbType.VarChar, getTrimMapValue(trimMap, "First_URL"));
            p11.Value = leadRecord.getStringValue("firstURL");

            OleDbParameter p12 = new OleDbParameter("@TWITTER_HANDLE", OleDbType.VarChar, getTrimMapValue(trimMap, "Twitter_Handle"));
            p12.Value = leadRecord.getStringValue("twitterHandle");

            OleDbParameter p13 = new OleDbParameter("@FORM_NAME", OleDbType.VarChar, getTrimMapValue(trimMap, "Form_Name"));
            p13.Value = sFormName;

            OleDbParameter p14 = new OleDbParameter("@PUBLIC_LEAD_LINK", OleDbType.VarChar, getTrimMapValue(trimMap, "Public_Lead_Link"));
            p14.Value = leadRecord.getStringValue("publicLeadLink").Replace("https://", "").Replace("http://", "").Replace("%3D","") ;

            OleDbParameter p15 = new OleDbParameter("@FIRST_VISIT", OleDbType.DBTimeStamp);
            p15.Value = SLX_Data.dateTimeToISODateString(leadRecord.getDateTimeValueInLocalTime("firstVisitSetAt"));

            OleDbParameter p16 = new OleDbParameter("@CONVERSION_COUNT", OleDbType.Integer);
            p16.Value = leadRecord.getIntegerValue("numConversionEvents");

            OleDbParameter p17 = new OleDbParameter("@FULL_FOUND_VIA_STRING", OleDbType.VarChar, getTrimMapValue(trimMap, "Full_Found_Via_String"));
            p17.Value = leadRecord.getStringValue("fullFoundViaString");

            OleDbParameter p18 = new OleDbParameter("@INSERTED_AT", OleDbType.DBTimeStamp);
            p18.Value = SLX_Data.dateTimeToISODateString(leadRecord.getDateTimeValueInLocalTime("insertedAt"));

            OleDbParameter p19 = new OleDbParameter("@WEBSITE_VISITS", OleDbType.Integer);
            p19.Value = analyticalDetails.getStringValue("visitCount");

            OleDbParameter p20 = new OleDbParameter("@UNIQUE_PAGES_VIEWED", OleDbType.Integer);
            //p20.Value = analyticalDetails.getStringValue("pageViewCount");

            OleDbParameter p21 = new OleDbParameter("@TOTAL_PAGES_VIEWED", OleDbType.Integer);
            p21.Value = analyticalDetails.getStringValue("pageViewCount");

            OleDbParameter p22 = new OleDbParameter("@AVERAGE_PAGES_VIEWED", OleDbType.Double);
            p22.Value = getAveragePagesViewed(analyticalDetails.getIntegerValue("visitCount"), analyticalDetails.getIntegerValue("pageViewCount"));

            OleDbParameter p23 = new OleDbParameter("@RECENT_VISIT", OleDbType.DBTimeStamp);
            //p23.Value = SLX_Data.dateTimeToISODateString(leadRecord.getDateTimeValueInLocalTime("lastConvertedAt"));
            p23.Value = SLX_Data.dateTimeToISODateString(analyticalDetails.getDateTimeValueInLocalTime("lastVisitAt"));

            OleDbParameter p24 = new OleDbParameter("@PORTAL_ID", OleDbType.VarChar, getTrimMapValue(trimMap, "Portal_Id"));
            p24.Value = leadRecord.getStringValue("portalId");

            OleDbParameter p25 = new OleDbParameter("@JSON_DATA", OleDbType.VarChar); //NOT TRIMMED BECAUSE IT IS A MEMO FIELD
            p25.Value = leadRecord.JSONData;

            insertCmd.Parameters.Add(p1);
            insertCmd.Parameters.Add(p2);
            insertCmd.Parameters.Add(p3);
            insertCmd.Parameters.Add(p4);
            insertCmd.Parameters.Add(p5);
            insertCmd.Parameters.Add(p6);
            insertCmd.Parameters.Add(p7);
            insertCmd.Parameters.Add(p8);
            insertCmd.Parameters.Add(p9);
            insertCmd.Parameters.Add(p10);
            insertCmd.Parameters.Add(p11);
            insertCmd.Parameters.Add(p12);
            insertCmd.Parameters.Add(p13);
            insertCmd.Parameters.Add(p14);
            insertCmd.Parameters.Add(p15);
            insertCmd.Parameters.Add(p16);
            insertCmd.Parameters.Add(p17);
            insertCmd.Parameters.Add(p18);
            insertCmd.Parameters.Add(p19);
            insertCmd.Parameters.Add(p20);
            insertCmd.Parameters.Add(p21);
            insertCmd.Parameters.Add(p22);
            insertCmd.Parameters.Add(p23);
            insertCmd.Parameters.Add(p24);
            insertCmd.Parameters.Add(p25);

            addTimestampData(insertCmd, true);

            insertCmd.ExecuteNonQuery();
        }
コード例 #4
0
ファイル: GMConnector.cs プロジェクト: kevinvw/HubSpot2014
 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));
     }
 }