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