Setting Brand based on related supplier:

[prcCustomHandleNewListing]

Begin

            update tblHouseListings

            set fkidBrand = 9

            where idHouseListing in 

            (select distinct hl.idHouseListing from tblSuppliers s

            join tblContractSupplierLinks csl on s.idSupplier = csl.fkidSupplier

            join tblcontracts c on csl.fkidContract = c.idContract

            join tblLands l on l.fkidContract = c.idContract

            join tblHouseListings hl on hl.fkidLand = l.idLand

            where s.sgSupplierName like 'White Square Properties'

            and hl.bStdActive = 1)

        End

Setting Brand based on NOT having a related supplier:

Begin

            update tblHouseListings

            set fkidBrand = 8

            where idHouseListing not in 

            (select distinct hl.idHouseListing from tblSuppliers s

            join tblContractSupplierLinks csl on s.idSupplier = csl.fkidSupplier

            join tblcontracts c on csl.fkidContract = c.idContract

            join tblLands l on l.fkidContract = c.idContract

            join tblHouseListings hl on hl.fkidLand = l.idLand

            where s.sgSupplierName like 'White Square Properties'

            and hl.bStdActive = 1)

            

        End


Changing carport qty to garage qty and making carport qty 0:


[prcCustomHandleNewListing]


Begin

        declare @inGarage INT,

                @inCarports INT

        select @inGarage = inGarage from tblHouseListings where idHouseListing = @idHouseListing

        select @inCarports = inCarports from tblHouseListings where idHouseListing = @idHouseListing


            If @inGarage <> @inCarports

            Begin

                Update tblHouseListings

                Set inGarage = inCarports

                where idHouseListing = @idHouseListing

            End

Begin

                Update tblHouseListings

                Set inCarports = 0

                where idHouseListing = @idHouseListing

            End


Copying Documents from House types or packages on tender and putting them in listing:


[prcCustomHandleNewListing]


Begin

                Create Table #TempHouseTypeDocs (NewidDocument INT, sgKeyWords varchar (200)) -- Create temp table


                Insert into tblDocuments -- Copy image

                (sgTitle, bCategoryActiveDoc, fkidContract, fkidBusinessUnit, fkidDocCategory, fkidSubmittedBy, sgDocumentName, sgDocumentExt, sgKeyWords

                ,dtLoaded, bHasThumbnail, sgFullPath, bMyHomeOverride, bSupplierAccessOverride, inStorageType, dtAvailableFrom, bHidden, inStdCustomOrder, bStdActive)


                output inserted.idDocument, inserted.sgKeyWords into #TempHouseTypeDocs  -- Insert new idDocument, new sgKeyWords and new fkidHouseType into temp table


                Select 

                concat(d.sgTitle,'_copy'), d.bCategoryActiveDoc, d.fkidContract, d.fkidBusinessUnit, d.fkidDocCategory, 0, d.sgDocumentName, d.sgDocumentExt, sgKeyWords

                ,d.dtLoaded, d.bHasThumbnail, d.sgFullPath, d.bMyHomeOverride, d.bSupplierAccessOverride, d.inStorageType, d.dtAvailableFrom, d.bHidden, d.inStdCustomOrder, d.bStdActive

                from tbldocuments as D

                left join tblTenderHouseTypeDocs as HTD on htd.fkidDocument = d.idDocument

                where idDocument in 

                (

                select HTD.fkidDocument

                from tblTenderHouseTypeDocs as HTD

                inner join tblHouseTypes as HT on HT.idHouseType = HTD.fkidHouseType

                inner join tblHouseListings as HL on HTD.fkidHouseType = HL.fkidHouseType 

                inner join tblDocuments as D on HTD.fkidDocument = d.idDocument

                where d.bStdActive = 1 and hl.bStdActive = 1 and ht.bStdActive = 1 and hl.fkidHouseType <> 585

                and hl.idHouseListing = @idHouselisting

                )

            


                Insert into tblHouseListingDocs (fkidDocument, fkidHouseListing, bIsFloorPlan, inStdCustomOrder, bStdActive) --Insert new documents from temp table

                select NewidDocument, @idHouseListing

                ,case when sgKeyWords like '%floor%' then 1 else 0 end

                ,2

                ,1

                From #TempHouseTypeDocs 

                

                

                Drop table #TempHouseTypeDocs  -- Remove temp table

            End

            

            


            --Insert image from facade & facade option packages (packagedocs)

            Begin


                Create Table #TempTenderPackageDocs (NewidDocument INT, sgKeyWords varchar (200), fkidHouselisting INT, fkidPackageCategory INT)  -- Create temp table


                Insert into tblDocuments -- Copy image

                (sgTitle, bCategoryActiveDoc, fkidContract, fkidBusinessUnit, fkidDocCategory, fkidSubmittedBy, sgDocumentName, sgDocumentExt, sgKeyWords,sgStdExtRef,sgDescription

                ,dtLoaded, bHasThumbnail, sgFullPath, bMyHomeOverride, bSupplierAccessOverride, inStorageType, dtAvailableFrom, bHidden, inStdCustomOrder, bStdActive)

                

                output inserted.idDocument, inserted.sgKeyWords, cast(inserted.sgStdExtRef as INT), cast(inserted.sgDescription as INT) into #TempTenderPackageDocs


                select 

                concat(d.sgTitle,'_copy'), d.bCategoryActiveDoc, d.fkidContract, d.fkidBusinessUnit, d.fkidDocCategory, 0, d.sgDocumentName, d.sgDocumentExt, sgKeyWords, @idhouselisting, tp.fkidPackageCategory

                ,d.dtLoaded, d.bHasThumbnail, d.sgFullPath, d.bMyHomeOverride, d.bSupplierAccessOverride, d.inStorageType, d.dtAvailableFrom, d.bHidden, d.inStdCustomOrder, d.bStdActive

                from tbldocuments as D

                inner join tblTenderPackageDocs as TPD on tpd.fkidDocument = d.idDocument

                left join tblTenderPackageSelections as TPS on TPD.fkidTenderPackage = TPS.fkidPackage

                left join tblTenderPackages TP on TP.idTenderPackage = TPS.fkidPackage

                left join tblHouseListings as HL on HL.fkidTender = TPS.fkidTender and hl.idHouseListing = @idHouseListing

                where d.idDocument in 

                (

                select 

                TPD.fkidDocument

                from tblTenderPackageDocs as TPD

                left join tblTenderPackageSelections as TPS on TPD.fkidTenderPackage = TPS.fkidPackage

                left join tblTenderPackages TP on TP.idTenderPackage = TPS.fkidPackage

                left join tblHouseListings as HL on HL.fkidTender = TPS.fkidTender

                inner join tblDocuments as D on TPD.fkidDocument = d.idDocument

                where d.bStdActive = 1 and hl.bStdActive = 1 and tp.fkidPackageCategory in (1,45) and tps.dcQuantity > 0 and hl.fkidHouseType <> 585

                and hl.idHouseListing = @idHouselisting

                )


                Insert into tblHouseListingDocs (fkidDocument, fkidHouseListing, bIsFloorPlan, inStdCustomOrder, bStdActive)

                select 

                t.NewidDocument

                ,t.fkidHouselisting

                ,case when t.sgKeyWords like '%floor%' then 1 else 0 end

                ,case when t.fkidPackageCategory = 1 then 1 else 2 end

                ,1

                from #TempTenderPackageDocs as t



                Drop table #TempTenderPackageDocs

            End


            --Insert image from facade & facade option packages (packagehousedocs)

            Begin

                Create Table #TempTenderHousePackageDocs (NewidDocument INT, sgKeyWords varchar (200), fkidHouselisting INT, fkidPackageCategory INT) -- Create temp table


                Insert into tblDocuments -- Copy image

                (sgTitle, bCategoryActiveDoc, fkidContract, fkidBusinessUnit, fkidDocCategory, fkidSubmittedBy, sgDocumentName, sgDocumentExt, sgKeyWords,sgStdExtRef,sgDescription

                ,dtLoaded, bHasThumbnail, sgFullPath, bMyHomeOverride, bSupplierAccessOverride, inStorageType, dtAvailableFrom, bHidden, inStdCustomOrder, bStdActive)

                

                output inserted.idDocument, inserted.sgKeyWords, cast(inserted.sgStdExtRef as INT), cast(inserted.sgDescription as INT) into #TempTenderHousePackageDocs 


                select 

                concat(d.sgTitle,'_copy'), d.bCategoryActiveDoc, d.fkidContract, d.fkidBusinessUnit, d.fkidDocCategory, 0, d.sgDocumentName, d.sgDocumentExt, sgKeyWords, @idhouselisting, tp.fkidPackageCategory

                ,d.dtLoaded, d.bHasThumbnail, d.sgFullPath, d.bMyHomeOverride, d.bSupplierAccessOverride, d.inStorageType, d.dtAvailableFrom, d.bHidden, d.inStdCustomOrder, d.bStdActive

                from tbldocuments as D

                inner join tblTenderPackageHouseDocs as TPHD on d.idDocument = tphd.fkidDocument

                inner join tblTenderPackageHouseLinks as TPHL on TPHD.fkidTenderPackageHouseLink = TPHL.idTenderPackageHouseLink

                left join tblTenderPackageSelections as TPS on TPHL.fkidTenderPackage = TPS.fkidPackage

                left join tblTenderPackages TP on TP.idTenderPackage = TPS.fkidPackage

                inner join tblHouseListings as HL on HL.fkidTender = TPS.fkidTender and hl.idHouseListing = @idHouseListing

                where d.idDocument in 

                (

                select TPHD.fkidDocument

                from tblTenderPackageHouseDocs as TPHD 

                inner join tblTenderPackageHouseLinks as TPHL on TPHD.fkidTenderPackageHouseLink = TPHL.idTenderPackageHouseLink

                left join tblTenderPackageSelections as TPS on TPHL.fkidTenderPackage = TPS.fkidPackage

                left join tblTenderPackages TP on TP.idTenderPackage = TPS.fkidPackage

                inner join tblHouseListings as HL on HL.fkidTender = TPS.fkidTender

                inner join tblDocuments as D on TPHD.fkidDocument = d.idDocument

                where d.bStdActive = 1 and hl.bStdActive = 1 and tp.fkidPackageCategory in (1,45) and tps.dcQuantity > 0 and hl.fkidHouseType <> 585 and tphl.fkidHouseType = hl.fkidHouseType

                and hl.idHouseListing = @idHouselisting

                )


                Insert into tblHouseListingDocs (fkidDocument, fkidHouseListing, bIsFloorPlan, inStdCustomOrder, bStdActive)

                select 

                t.NewidDocument

                ,t.fkidHouselisting

                ,case when t.sgKeyWords like '%floor%' then 1 else 0 end

                ,case when t.fkidPackageCategory = 1 then 1 else 2 end

                ,1

                from #TempTenderHousePackageDocs  as t


                Drop table #TempTenderHousePackageDocs 

            End