New Ford Inventory

Connection OpenAsync()

Connection Close()

  
                                                         Declare @PageTypeId INT;

                                                         SELECT   @PageTypeId = pagetypeid FROM OffersitePage  WHERE id = 66588
    
                                                        ;WITH mainSeo AS (
                                                        
                                                        SELECT Top 1 *, 1 AS priority
                                                        FROM OfferSiteSEO
                                                        WHERE 66588 <> 0 And PageId = 66588
                                                            AND ((@PageTypeId in (1, 58, 60, 67) AND [CampaignID] in (0, 610)) OR (@PageTypeId not in (1, 58, 60, 67) AND [CampaignID] = 610))
                                                            AND ISNULL(PageURL, '') = '' and ISNULL(RootURL, '') = ''

                                                        UNION ALL

                                                        SELECT TOP 1 *, 2 AS priority
                                                        FROM OfferSiteSEO
                                                        WHERE PageId IN (
                                                            SELECT TOP 1 ID
                                                            FROM OfferSitePage
                                                            WHERE PageTypeId = @PageTypeId AND (CampaignID = 610) and @PageTypeId not in (1, 58, 60, 67)
                                                            ORDER BY
			                                                CASE WHEN ModifiedDate IS NOT NULL THEN 0 ELSE 1 END,
			                                                ModifiedDate DESC,
			                                                ID DESC 
                                                        )
                                                        AND ISNULL(PageURL, '') = '' and ISNULL(RootURL, '') = ''

                                                        UNION ALL

                                                        SELECT TOP 1 *, 3 AS priority
                                                        FROM OfferSiteSEO
                                                        WHERE PageId = 66588 and @PageTypeId not in (1, 58, 60, 67)
                                                            AND CampaignID = 0
                                                            AND ISNULL(PageURL, '') = '' and ISNULL(RootURL, '') = '' order by id desc
                                                         
                                                        UNION ALL

	                                                    SELECT TOP 1 *, 4 AS priority
	                                                    FROM OfferSiteSEO
	                                                    WHERE PageId = 0
                                                        AND CampaignID = 610
                                                        AND ISNULL(PageURL, '') = '' and ISNULL(RootURL, '') = '' order by id desc
                                                    ),

                                                    finalPageSeo AS (
                                                        SELECT TOP 1 *
                                                        FROM mainSeo
                                                        ORDER BY priority
                                                    ),


                                                    customSrpseo AS (
                                                        SELECT *, 0 as priority
                                                        FROM OfferSiteSEO
                                                        WHERE @PageTypeId = 18
                                                            AND CampaignID = 610
                                                            AND (ISNULL(PageURL, '') <> '' OR ISNULL(RootURL, '') <> '')
                                                    )

                                                    select      ID
                                                                , CampaignID
                                                                , PageId
                                                                , Description
                                                                , Keywords
                                                                , RobotParameters
                                                                , CanonicalUrl
                                                                , OgLocale
                                                                , OgSiteName
                                                                , OgTitle
                                                                , OgDescription
                                                                , PageTitle
                                                                , SEOText
                                                                , CorePageH1TagText
                                                                , UsedSEOText
                                                                , CPOSEOText
                                                                , UsedCorePageH1TagText
                                                                , CpoCorePageH1TagText
                                                                , UsedPageTitle
                                                                , CpoPageTitle
                                                                , UsedOgSiteName
                                                                , CpoOgSiteName
                                                                , UsedOgTitle
                                                                , CpoOgTitle
                                                                , UsedOgDescription
                                                                , CpoOgDescription
                                                                , UsedDescription
                                                                , CpoDescription
                                                                , UsedKeywords
                                                                , CpoKeywords
                                                                , UsedOgLocale
                                                                , CpoOgLocale
                                                                , UsedRobotParameters
                                                                , CpoRobotParameters
                                                                , UsedCanonicalUrl
                                                                , CpoCanonicalUrl
                                                                , AddNewSeoTextToTop
                                                                , AddUsedSeoTextToTop
                                                                , AddCpoSeoTextToTop
                                                                , NewSEOCustomStructuredData
                                                                , UsedSEOCustomStructuredData
                                                                , CpoSEOCustomStructuredData
                                                                , HidePageH1Tag
                                                                , PageURL
                                                                , RootURL
                                                                , [BottomSeoText]
                                                                , [BottomUsedSeoText]
                                                                , [BottomCpoSeoText]
                                                                , [HideSrpFilters]
                                                                , BreadcrumbText
		                                                        From (
                                                                        SELECT * FROM finalPageSeo
                                                                        UNION ALL
                                                                        SELECT * FROM customSrpseo
                                                                     ) as SeoResults
                                                                                     

Connection OpenAsync()

Connection Close()

select distinct(d.AccountID), d.OemCode from Dealerships d 
                                                           inner join dealercampaign dc on d.AccountID = dc.AccountID
                                                           inner join dealercampaignsettings dcs on dc.campaignid=dcs.campaignid
                                                           where ', '+59271,54926+', ' LIKE '%, '+CAST(d.AccountID AS varchar(20))+', %' 
                                                           AND d.FranchiseString In ('Ford', 'FordFord') and d.EnableOEMProgram = 1 and dc.active=1 and dc.isdeleted=0
                                                           AND ((2 = 0 and dcs.EnableModelEData = 0) OR (2 = 1) OR (2 = 2))

Connection OpenAsync()

Connection Close()

SELECT [d].[ModelName]
FROM [DealershipCustomPriceTypeSettings] AS [d]
WHERE [d].[CategoryId] = 3

Connection OpenAsync()

Connection Close()

GetStyleIdFromChromeByModelNumber

Connection OpenAsync()

Connection Close()

SELECT TOP(1) [p].[Id], [p].[CampaignId], [p].[EnablePopupVideo], [p].[PopupTypeId], [p].[VideoCTALink], [p].[VideoCTAName], [p].[VideoURL]
FROM [PopupVideo] AS [p]
WHERE [p].[CampaignId] = 610 AND [p].[PopupTypeId] = 5

Connection OpenAsync()

Connection CloseAsync()

SELECT TOP(1) [h].[DealerID], [h].[HomenetVehicleID], [h].[BMW_BEV], [h].[BMW_BEV_Disclaimer], [h].[BMW_M], [h].[BMW_PHEV], [h].[BMW_PHEV_Disclaimer], [h].[Best Price], [h].[Body], [h].[BookValue], [h].[Bottom Price], [h].[CarFaxHistory], [h].[CarfaxIconURL], [h].[Categorized Options], [h].[Certified], [h].[CertifiedType], [h].[CFModelName], [h].[ChromeDivisionID], [h].[Chrome_Style_info], [h].[CityMPG], [h].[DateInStock], [h].[Dealer Address], [h].[Dealer City], [h].[Dealer Discounted Price], [h].[Dealer Fax], [h].[Dealer Phone], [h].[Dealer State], [h].[Dealer Zip], [h].[Demo], [h].[Description], [h].[Doors], [h].[Drivetrain], [h].[ETA], [h].[Engine_Aspiration_Type], [h].[Engine_Block_Type], [h].[EngineCylinders], [h].[Engine_Description], [h].[EngineDisplacement], [h].[EngineDisplacementCubicInches], [h].[EPAClassification], [h].[Ext_Color_Code], [h].[Ext_Color_Generic], [h].[ExtColorHexCode], [h].[ExteriorColor], [h].[Factory_Codes], [h].[FirstPhotoUrl], [h].[FordDirect_AZPlan], [h].[FordDirect_FeatureLabel], [h].[FordDirect_LOBTypeId], [h].[FordDirect_VLPrice], [h].[FordDirect_XPlan], [h].[Fuel_Type], [h].[HighMSRP], [h].[HighwayMPG], [h].[HotListingExpiration], [h].[InProduction], [h].[InTransit], [h].[Int_Color_Code], [h].[Int_Color_Generic], [h].[IntColorHexCode], [h].[Int_Upholstery], [h].[InteriorColor], [h].[Internet_Price], [h].[Invoice], [h].[IsElectric], [h].[FordDirect_FCTP], [h].[IsHybrid], [h].[Loaner], [h].[LowestPayment], [h].[Make], [h].[MarketClass], [h].[Miles], [h].[Misc_Price1], [h].[Misc_Price2], [h].[Misc_Price3], [h].[Model], [h].[ModelNumber], [h].[MSRP], [h].[OEM_InstalledPackages], [h].[OneOwner], [h].[Options], [h].[Other Price], [h].[PNO12], [h].[PNO34], [h].[PassengerCapacity], [h].[PhotoURLs], [h].[Reserved], [h].[SellingPrice], [h].[Special Field 1], [h].[Special Field 2], [h].[Special Field 3], [h].[Special Field 4], [h].[Stock], [h].[Style_Description], [h].[Subaru_Eyesight], [h].[Subaru_OnOrder], [h].[Subaru_Starlink], [h].[Subaru_VON], [h].[Toyota_Disclaimer], [h].[Transmission], [h].[Transmission_Description], [h].[Transmission_Speed], [h].[Trim], [h].[Type], [h].[VdvEligible], [h].[VehicleTypeID], [h].[Vehicle Video URL], [h].[VideoId], [h].[VIN], [h].[Wheelbase_Code], [h].[Windowstickerurl], [h].[Year], [h].[dio_SellingPrice]
FROM [HomenetVehicles] AS [h]
WHERE [h].[DealerID] = 26198 AND [h].[FordDirect_FCTP] = CAST(1 AS bit) AND LOWER([h].[Type]) = new

Connection OpenAsync()

Connection Close()

SELECT * FROM [TrackingPixels] TP WHERE TP.CampaignID = 610 AND TP.Location IN ('InventorySearch', 'InventorySearchNEW') AND TrackingDISABLE = 0;

Connection OpenAsync()

Connection CloseAsync()

SELECT TOP(1) [r].[Id], [r].[AltText], [r].[AltTextReserveYourVehicleCardSRPStep1], [r].[AltTextReserveYourVehicleCardSRPStep2], [r].[AltTextReserveYourVehicleCardSRPUsed], [r].[CampaignID], [r].[CreatedDate], [r].[Disclaimer], [r].[EnableCallforPrice], [r].[EnableReserveYourCarInSRPUsed], [r].[EnableReserveYourCarPermanentlyActiveSrpUsed], [r].[EnableReserveYourCarPermanentlyActiveinstep1Srp], [r].[EnableReserveYourCarPermanentlyActiveinstep2Srp], [r].[ExcludeModels], [r].[ExcludeTrims], [r].[ExcludeYears], [r].[FormHeader], [r].[HeaderText], [r].[HideEstimatedMPG], [r].[HideMSRP], [r].[LogoImageId], [r].[ModifiedDate], [r].[RadioLabel1], [r].[RadioLabel2], [r].[ReserveYourVehicleCardSRPStep1Redirection], [r].[ReserveYourVehicleCardSRPStep1URL], [r].[ReserveYourVehicleCardSRPStep2Redirection], [r].[ReserveYourVehicleCardSRPStep2URL], [r].[ReserveYourVehicleCardSRPUsedRedirection], [r].[ReserveYourVehicleCardSRPUsedURL], [r].[ReserveYourVehicleCardSrpStep1FromDate], [r].[ReserveYourVehicleCardSrpStep1ToDate], [r].[ReserveYourVehicleCardSrpStep2FromDate], [r].[ReserveYourVehicleCardSrpStep2ToDate], [r].[ReserveYourVehicleCardSrpUsedFromDate], [r].[ReserveYourVehicleCardSrpUsedToDate], [r].[SRPReserveYourVehicleCardTitle], [r].[SRPReserveYourVehicleLink], [r].[SRPReserveYourVehicleLinkRedirection], [r].[SrpButtonBorderColor], [r].[SrpButtonColor], [r].[SrpButtonFontColor], [r].[SrpButtonHoverColor], [r].[SrpButtonText], [r].[SrpUsedButtonText], [r].[Step1Header], [r].[Step2Header], [r].[Step3Header], [r].[ThankYouMessage], [r].[UsedReserveYourVehicleCardTitle], [r].[UsedSRPReserveYourVehicleLinkRedirection], [r].[UsedSrpReserveYourVehicleLink], [r].[VehicleCardImageId], [r].[VehicleCardSrpStep2ImageId], [r].[VehicleCardSrpUsedImageId]
FROM [ReserveYourCarSettings] AS [r]
WHERE [r].[CampaignID] = 610

Connection OpenAsync()

Connection CloseAsync()

SELECT * FROM TrackingPixels WHERE CampaignId = 610 AND TrackingDISABLE = 0;

Connection OpenAsync()

Connection CloseAsync()

SELECT TOP(1) [e].[Id], [e].[CampaignId], [e].[CreatedDate], [e].[FeaturedSpecial], [e].[ModifiedDate], [e].[SRPMouseOver], [e].[SpecialRotator]
FROM [EnhancedMediaAssetSettings] AS [e]
WHERE [e].[CampaignId] = 610

Connection OpenAsync()

Connection OpenAsync()

Connection CloseAsync()

SELECT TOP(1) [d].[Id], [d].[ActivEngageButtounLabelDesktop], [d].[ActivEngageButtounLabelMobile], [d].[ApiKey], [d].[CTXIdNew], [d].[CTXIdUsed], [d].[CampaignId], [d].[ClientId], [d].[DiscountUsedDetails], [d].[EnableDiscountUsedDetails], [d].[EnableFinalPriceUsedDetails], [d].[EnableListPriceUsedDetails], [d].[EnableTagRailIntegration], [d].[EnableTextium], [d].[FinalPriceUsedDetails], [d].[IPacketAPIKey], [d].[ListPriceUsedDetails], [d].[OpenIPacketUsingModalPopupVersion], [d].[ShowMileageNewVehicles], [d].[SourceId], [d].[TagRailDealerId], [d].[TagRailPurchaseOnlineLabel], [d].[TagRailValueYourTradeLabel], [d].[TransactButtonBorderColor], [d].[TransactCPOButtonColor], [d].[TransactCPOFontColor], [d].[TransactCpoButtonBorderColor], [d].[TransactUsedButtonBorderColor], [d].[TransactUsedButtonColor], [d].[TransactUsedFontColor], [d].[WhispTextPidNew], [d].[WhispTextPidUsed], [d].[WhispTextPidVdpNew], [d].[WhispTextPidVdpUsed], [d].[WhispTextSrp], [d].[WhispTextSrpButtonLabel], [d].[WhispTextVdp], [d].[WhispTextVdpButtonLabel]
FROM [DealerCampaignSrpVdpSettings] AS [d]
WHERE [d].[CampaignId] = 610

Connection OpenAsync()

Connection CloseAsync()

SELECT TOP(1) [o].[Id], [o].[ButtonImgUrl], [o].[CampaignId], [o].[ColorPrimary], [o].[ColorSecondary], [o].[EnableOfferLogixBuyerCall], [o].[InfoSectionBackground], [o].[OpenHours], [o].[Phonenumber], [o].[Source], [o].[SubTitle], [o].[Title]
FROM [OfferLogixBuyerCallSettings] AS [o]
WHERE [o].[CampaignId] = 610

Connection OpenAsync()

Connection Close()

                                SELECT 
                                [Id]
                                , [AccessibilitySettingId]
                                , [StatementLink]
                                , [FooterHtml]
                                , [HideMobile]
                                , [HideTrigger]
                                , [Language]
                                , [Position]
                                , [LeadColor]
                                , [TriggerColor]
                                , [TriggerRadius]
                                , [TriggerPositionX]
                                , [TriggerPositionY]
                                , [TriggerIcon]
                                , [TriggerSize]
                                , [TriggerOffsetX]
                                , [TriggerOffsetY]
                                , [Mobile_TriggerSize] AS MobileTriggerSize
                                , [Mobile_TriggerPositionX] AS MobileTriggerPositionX
                                , [Mobile_TriggerPositionY] AS MobileTriggerPositionY
                                , [Mobile_TriggerOffsetX] AS MobileTriggerOffsetX
                                , [Mobile_TriggerOffsetY] AS MobileTriggerOffsetY
                                , [Mobile_TriggerRadius] AS MobileTriggerRadius
                                FROM 
                                dbo.AccessiBeSettings
                                WHERE AccessibilitySettingId = 27