
CREATE PROCEDURE dbo.ChangeProduct as
SET NOCOUNT ON

-- Denne procedure ændere produkt på ale kunder hvis produkter udløber i dag.
-- SKAL kører fra Nightaudit
/*
   Last changed:
   20060301 JP: BUG: wrong table, plus simpler SQL. We do not have seperate dates for dayproducts. They must change on the same date as overnight products
*/

DECLARE
@SystemDate DATETIME

SELECT @SystemDate = CONVERT(DATETIME, SystemDate, 108) FROM System WITH (NOLOCK)

UPDATE CustomerFieldsPerHotel SET
OvernightProductNumber1 = NextOvernightProductNumber1, NextOvernightProductNumber1 = NULL,
DayProductNumber1 = NextDayProductNumber1, NextDayProductNumber1 = NULL,
ProductDate1 = NULL
WHERE ProductDate1 = @SystemDate

UPDATE CustomerFieldsPerHotel SET
OvernightProductNumber2 = NextOvernightProductNumber2, NextOvernightProductNumber2 = NULL,
DayProductNumber2 = NextDayProductNumber2, NextDayProductNumber2 = NULL,
ProductDate2 = NULL
WHERE ProductDate2 = @SystemDate

UPDATE CustomerFieldsPerHotel SET
OvernightProductNumber3 = NextOvernightProductNumber3, NextOvernightProductNumber3 = NULL,
DayProductNumber3 = NextDayProductNumber3, NextDayProductNumber3 = NULL,
ProductDate3 = NULL
WHERE ProductDate3 = @SystemDate

UPDATE CustomerFieldsPerHotel SET
OvernightProductNumber4 = NextOvernightProductNumber4, NextOvernightProductNumber4 = NULL,
DayProductNumber4 = NextDayProductNumber4, NextDayProductNumber4 = NULL,
ProductDate4 = NULL
WHERE ProductDate4 = @SystemDate

UPDATE CustomerFieldsPerHotel SET
OvernightProductNumber5 = NextOvernightProductNumber5, NextOvernightProductNumber5 = NULL,
DayProductNumber5 = NextDayProductNumber5, NextDayProductNumber5 = NULL,
ProductDate5 = NULL
WHERE ProductDate5 = @SystemDate

UPDATE CustomerFieldsPerHotel SET
OvernightProductNumber6 = NextOvernightProductNumber6, NextOvernightProductNumber6 = NULL,
DayProductNumber6 = NextDayProductNumber6, NextDayProductNumber6 = NULL,
ProductDate6 = NULL
WHERE ProductDate6 = @SystemDate

UPDATE CustomerFieldsPerHotel SET
OvernightProductNumber7 = NextOvernightProductNumber7, NextOvernightProductNumber7 = NULL,
DayProductNumber7 = NextDayProductNumber7, NextDayProductNumber7 = NULL,
ProductDate7 = NULL
WHERE ProductDate7 = @SystemDate

UPDATE CustomerFieldsPerHotel SET
OvernightProductNumber8 = NextOvernightProductNumber8, NextOvernightProductNumber8 = NULL,
DayProductNumber8 = NextDayProductNumber8, NextDayProductNumber8 = NULL,
ProductDate8 = NULL
WHERE ProductDate8 = @SystemDate

/*
declare @SystemDate varchar(8)  
select top 1 @SystemDate = SystemDate from system (nolock) 

print 'Start OvernightProduct 1'

update Customer set 
  OvernightProductNumber1 = NextOvernightProductNumber1, ProductDate1 = null, NextOvernightProductNumber1 = null
where CustomerNumber in 
(
  select CustomerNumber 
  from Customer (nolock) 
  where ProductDate1 = @SystemDate
)

print 'Start OvernightProduct 2'

update Customer set 
  OvernightProductNumber2 = NextOvernightProductNumber2, ProductDate2 = null, NextOvernightProductNumber2 = null
where CustomerNumber in 
(
  select CustomerNumber 
  from Customer (nolock) 
  where ProductDate2 = @SystemDate
)

print 'Start OvernightProduct 3'

update Customer set 
  OvernightProductNumber3 = NextOvernightProductNumber3, ProductDate3 = null, NextOvernightProductNumber3 = null
where CustomerNumber in 
(
  select CustomerNumber 
  from Customer (nolock) 
  where ProductDate3 = @SystemDate
)

print 'Start OvernightProduct 4'

update Customer set 
  OvernightProductNumber4 = NextOvernightProductNumber4, ProductDate4 = null, NextOvernightProductNumber4 = null
where CustomerNumber in 
(
  select CustomerNumber 
  from Customer (nolock) 
  where ProductDate4 = @SystemDate
)


print 'Start OvernightProduct 5'

update Customer set 
  OvernightProductNumber5 = NextOvernightProductNumber5, ProductDate5 = null, NextOvernightProductNumber5 = null
where CustomerNumber in 
(
  select CustomerNumber 
  from Customer (nolock) 
  where ProductDate5 = @SystemDate
)


print 'Start OvernightProduct 6'

update Customer set 
  OvernightProductNumber6 = NextOvernightProductNumber6, ProductDate6 = null, NextOvernightProductNumber6 = null
where CustomerNumber in 
(
  select CustomerNumber 
  from Customer (nolock) 
  where ProductDate6 = @SystemDate
)


print 'Start OvernightProduct 7'

update Customer set 
  OvernightProductNumber7 = NextOvernightProductNumber7, ProductDate7 = null, NextOvernightProductNumber7 = null
where CustomerNumber in 
(
  select CustomerNumber 
  from Customer (nolock) 
  where ProductDate7 = @SystemDate
)

print 'Start OvernightProduct 8'

update Customer set 
  OvernightProductNumber8 = NextOvernightProductNumber8, ProductDate8 = null, NextOvernightProductNumber8 = null
where CustomerNumber in 
(
  select CustomerNumber 
  from Customer (nolock) 
  where ProductDate8 = @SystemDate
)


print 'Start ProductDate1'
update Customer set 
  DayProductNumber1 = NextDayProductNumber1, ProductDate1 = null, NextDayProductNumber1 = null
where CustomerNumber in 
(
  select CustomerNumber 
  from Customer (nolock) 
  where ProductDate1 = @SystemDate
)

print 'Start ProductDate2'
update Customer set 
  DayProductNumber2 = NextDayProductNumber2, ProductDate2 = null, NextDayProductNumber2 = null
where CustomerNumber in 
(
  select CustomerNumber 
  from Customer (nolock) 
  where ProductDate2 = @SystemDate
)

print 'Start ProductDate3'
update Customer set 
  DayProductNumber3 = NextDayProductNumber3, ProductDate3 = null, NextDayProductNumber3 = null
where CustomerNumber in 
(
  select CustomerNumber 
  from Customer (nolock) 
  where ProductDate3 = @SystemDate
)

print 'Start ProductDate4'
update Customer set 
  DayProductNumber4 = NextDayProductNumber4, ProductDate4 = null, NextDayProductNumber4 = null
where CustomerNumber in 
(
  select CustomerNumber 
  from Customer (nolock) 
  where ProductDate4 = @SystemDate
)

print 'Start ProductDate5'
update Customer set 
  DayProductNumber5 = NextDayProductNumber5, ProductDate5 = null, NextDayProductNumber5 = null
where CustomerNumber in 
(
  select CustomerNumber 
  from Customer (nolock) 
  where ProductDate5 = @SystemDate
)

print 'Start ProductDate6'
update Customer set 
  DayProductNumber6 = NextDayProductNumber6, ProductDate6 = null, NextDayProductNumber6 = null
where CustomerNumber in 
(
  select CustomerNumber 
  from Customer (nolock) 
  where ProductDate6 = @SystemDate
)

print 'Start ProductDate7'
update Customer set 
  DayProductNumber7 = NextDayProductNumber7, ProductDate7 = null, NextDayProductNumber7 = null
where CustomerNumber in 
(
  select CustomerNumber 
  from Customer (nolock) 
  where ProductDate7 = @SystemDate
)

print 'Start ProductDate8'
update Customer set 
  DayProductNumber8 = NextDayProductNumber8, ProductDate8 = null, NextDayProductNumber8 = null
where CustomerNumber in 
(
  select CustomerNumber 
  from Customer (nolock) 
  where ProductDate8 = @SystemDate
)
*/

