/*
 KJ 20210603: Bug: Fix for "Conversion failed when converting the varchar value 'xxxxx' to data type int."
 kj  20210422: Ext: Added card type for Danshotel (DSB Plus)
 kj  20200701: Ext: Added card type for Danshotel (LO Plus)
 vd  20161115: Ext/CLN: Two further cards for DanHostel. A bit of housekeeping.
 vd  20160905: Ext: DanHostel reward card template added. DanHostel family card template updated.
 KJ  20150226: Ext: Added roger card type (Used by Danhost City). 
 vd  20140328: Ext: DanHostel family card template added in a way that makes room for a BestWestern Template
 KJ  20131210: Ext: Added bwr card template.
 LHA 20110531: Added Customer card. Current working as a Bonus card, but with no interest in bonus. We are only interested in adding card on customer.
 NB  20110120: Safety for duplicates, relation to the hotel, added Picasso Gift Certificate and Picasso Transfer/Guest Card
 TL  20071115: Added template for Tanum Bonus
 TL  20071029: Added VIA payment cards/RFID tags
 NB  20071017 requried by dbUpdate
 TL  20070522: Added CampingCard
*/

/*
Description of card iso standard (10/06/2011)
Spor 1: start % slut ?
Spor 2: start ; slut ?
Spor 3: start + slut ?
*/

/*New templates format: Separate fields with ^  */

SET NOCOUNT ON
DECLARE @HotelCustomerNumberLocal int

/* Card type primary keys */
DECLARE
  @PK_VingType INT,            /* ctVing = 1 in Delphi */
  @PK_BonusType INT,           /* ctBonus = 2 in Delphi */
  @PK_SocialSecurityType INT,  /* ctSocialSecurity = 3 in Delphi */
  @PK_LoginType INT,           /* ctLogin = 4 in Delphi */
  @PK_VIAAdultType INT,        /* ctVIApaymentAdult = 5 in Delphi */
  @PK_VIAChildType INT,        /* ctVIApaymentChild = 6 in Delphi */
  @PK_SCCType INT,             /* ctSCCCampingCard = 7 in Delphi */
  @PK_GCType INT,              /* ctGiftCertificate = 8 in Delphi */
  @PK_TCType INT,              /* ctTransferCard = 9 in Delphi */
  @PK_DCType INT,              /* ctTransferCard = 10 in Delphi */
  @PK_BWRType INT,             /* ctBestWesternReward = 11 in Delphi */
  @PK_DanHostelType INT,       /* ctDanHostel = 12 in Delphi */
  @PK_RogerType INT,           /* ctRoger = 13 in Delphi */
  @PK_DanHostelRewardType INT, /* ctDanHostelReward = 14 in Delphi */
  @PK_AeldreSagen INT,         /* ctAeldreSagen = 15 in Delphi */
  @PK_FDM INT,                 /* ctFDM = 16 in Delphi */
  @PK_Matas INT,               /* ctMatas = 17 in Delphi*/
  @PK_Coop INT,                /* ctCoop = 18 in Delphi*/
  @PK_DanhostelCorporate INT,  /* ctDanhostelCorporate = 19 in Delphi*/
  @PK_DanhostelLoyalty INT,    /* ctDanhostelLoyalty = 20 in Delphi*/
  @PK_PersonaleZonen INT,      /* ctPersonaleZonen = 21 in Delphi*/
  @PK_LOPlus INT,              /* ctLOPlus = 22 in Delphi*/
  @PK_DSBPlus INT              /* ctDSBPlus = 23 in Delphi*/

/* Card recognition template primary keys */
DECLARE
  @PK_TplPicassoBonus INT,
  @PK_TplGetMoreBonus INT,
  @PK_TplGPPBonus INT,
  @PK_TplEuroBonus INT,
  @PK_TplSocialSecurity INT,
  @PK_TplPicassoLogin INT,
  @PK_TplSCCCampingCard INT,
  @PK_TplViaPayment INT,
  @PK_TplTanumBonus INT,
  @PK_TplGiftCertificate INT,
  @PK_TplTransferCard INT,
  @PK_TplCustomerCard INT,
  @PK_TplBWRCard INT,
  @PK_TplDanhostelCard INT,
  @PK_TplRogerCard INT,
  @PK_TplDanhostelRewardCard INT,
  @PK_TplAeldreSagen INT,
  @PK_TplFDM INT,
  @PK_TplMatas INT,
  @PK_TplCoop INT,
  @PK_TplDanhostelCorporate INT,
  @PK_TplDanhostelLoyalty INT,
  @PK_TplPersonaleZonen INT,
  @PK_TplLOPlus INT,
  @PK_TplDSBPlus INT

/***** CREATE CARD TYPES *****/

/* VingCard */
IF NOT EXISTS(SELECT * FROM CardType WITH (NOLOCK) WHERE Type = 1 /* ctVing */)
  INSERT INTO CardType (Type, Name, Enabled, SortNumber) VALUES (1, 'Ving', 1, 1)
/* BonusCard */
IF NOT EXISTS(SELECT * FROM CardType WITH (NOLOCK) WHERE Type = 2 /* ctBonus */)
  INSERT INTO CardType (Type, Name, Enabled, SortNumber) VALUES (2, 'Bonus', 1, 2)
/* SocialSecurityCard */
IF NOT EXISTS(SELECT * FROM CardType WITH (NOLOCK) WHERE Type = 3 /* ctSocialSecurity */)
  INSERT INTO CardType (Type, Name, Enabled, SortNumber) VALUES (3, 'SocialSecurity', 1, 3)
/* LoginCard */
IF NOT EXISTS(SELECT * FROM CardType WITH (NOLOCK) WHERE Type = 4 /* ctLogin */)
  INSERT INTO CardType (Type, Name, Enabled, SortNumber) VALUES (4, 'Login', 1, 4)
/* VIAAdultCard */
IF NOT EXISTS(SELECT * FROM CardType WITH (NOLOCK) WHERE Type = 5 /* ctVIApaymentAdult */)
  INSERT INTO CardType (Type, Name, Enabled, SortNumber) VALUES (5, 'VIA Payment Adult', 1, 5)
/* VIAChildCard */
IF NOT EXISTS(SELECT * FROM CardType WITH (NOLOCK) WHERE Type = 6 /* ctVIApaymentChild */)
  INSERT INTO CardType (Type, Name, Enabled, SortNumber) VALUES (6, 'VIA Payment Child', 1, 6)
/* SCC Camping Card */
IF NOT EXISTS(SELECT * FROM CardType WITH (NOLOCK) WHERE Type = 7 /* ctSCCCampingCard */)
  INSERT INTO CardType (Type, Name, Enabled, SortNumber) VALUES (7, 'SCC Camping Card', 1, 7)
/* Gift Card Certificate */
IF NOT EXISTS(SELECT * FROM CardType WITH (NOLOCK) WHERE Type = 8 /* Gift Card Certificate  */)
  INSERT INTO CardType (Type, Name, Enabled, SortNumber) VALUES (8, 'Gift Certificate', 1, 8)
/* Transfer/Guest Card */
IF NOT EXISTS(SELECT * FROM CardType WITH (NOLOCK) WHERE Type = 9 /* Transfer/Guest Card */)
  INSERT INTO CardType (Type, Name, Enabled, SortNumber) VALUES (9, 'Transfer/Guest Card', 1, 9)
/* Card type 10 (customer card for Dayz) not used any more */
IF NOT EXISTS(SELECT * FROM CardType WITH (NOLOCK) WHERE Type = 11 /* ctBestWesternReward */)
  INSERT INTO CardType (Type, Name, Enabled, SortNumber) VALUES (11, 'BWR Reward', 1, 11)
IF NOT EXISTS(SELECT * FROM CardType WITH (NOLOCK) WHERE Type = 12 /* ctDanHostel */)
  INSERT INTO CardType (Type, Name, Enabled, SortNumber) VALUES (12, 'Danhostel', 1, 12)
IF NOT EXISTS(SELECT * FROM CardType WITH (NOLOCK) WHERE Type = 13 /* ctRoger */)
  INSERT INTO CardType (Type, Name, Enabled, SortNumber) VALUES (13, 'Roger', 1, 13)
IF NOT EXISTS(SELECT * FROM CardType WITH (NOLOCK) WHERE Type = 14 /* ctDanHostelReward */)
  INSERT INTO CardType (Type, Name, Enabled, SortNumber) VALUES (14, 'DanHostelReward', 1, 14)
IF NOT EXISTS(SELECT * FROM CardType WITH (NOLOCK) WHERE Type = 15 /* ctAeldreSagen */)
  INSERT INTO CardType (Type, Name, Enabled, SortNumber) VALUES (15, 'ÆldreSagen', 1, 15)
IF NOT EXISTS(SELECT * FROM CardType WITH (NOLOCK) WHERE Type = 16 /* ctFDM */)
  INSERT INTO CardType (Type, Name, Enabled, SortNumber) VALUES (16, 'FDM', 1, 16)
IF NOT EXISTS(SELECT * FROM CardType WITH (NOLOCK) WHERE Type = 17 /* ctMatas */)
  INSERT INTO CardType (Type, Name, Enabled, SortNumber) VALUES (17, 'Matas', 1, 17)
IF NOT EXISTS(SELECT * FROM CardType WITH (NOLOCK) WHERE Type = 18 /* ctCoop */)
  INSERT INTO CardType (Type, Name, Enabled, SortNumber) VALUES (18, 'Coop', 1, 18)
IF NOT EXISTS(SELECT * FROM CardType WITH (NOLOCK) WHERE Type = 19 /* ctDanhostelCorporate */)
  INSERT INTO CardType (Type, Name, Enabled, SortNumber) VALUES (19, 'FirmaCard', 1, 19)
IF NOT EXISTS(SELECT * FROM CardType WITH (NOLOCK) WHERE Type = 20 /* ctDanhostelLoyalty */)
  INSERT INTO CardType (Type, Name, Enabled, SortNumber) VALUES (20, 'LoyalityCard', 1, 20)
IF NOT EXISTS(SELECT * FROM CardType WITH (NOLOCK) WHERE Type = 21 /* ctPersonaleZonen */)
  INSERT INTO CardType (Type, Name, Enabled, SortNumber) VALUES (21, 'PersonaleZonen', 0, 21)
ELSE
  UPDATE CardType SET Enabled = 0 WHERE Type = 21
IF NOT EXISTS(SELECT * FROM CardType WITH (NOLOCK) WHERE Type = 22 /* ctLOPlus */)
  INSERT INTO CardType (Type, Name, Enabled, SortNumber) VALUES (22, 'LOPlus', 1, 22)
IF NOT EXISTS(SELECT * FROM CardType WITH (NOLOCK) WHERE Type = 23 /* ctDSBPlus */)
  INSERT INTO CardType (Type, Name, Enabled, SortNumber) VALUES (23, 'DSBPlus', 0, 23)

DECLARE @NoDanhostel INT
DECLARE @NoDanhostel1 INT

SELECT @NoDanhostel = COUNT(*) 
FROM HotelSetup WITH (NOLOCK)
INNER JOIN HotelSetupValueXTab WITH (NOLOCK) ON HotelSetupValueXTab.HotelSetupNumber = HotelSetup.HotelSetUpNumber
INNER JOIN HotelSetupValue WITH (NOLOCK) ON HotelSetupValue.PK_ValueNumber = HotelSetupValueXTab.FK_ValueNumber
WHERE HotelSetup.SearchKey = 'IsDanHostel'
AND HotelSetupValue.KeyValue = '1'

SELECT @NoDanhostel1 = COUNT(*)
FROM Hotelsetup WITH (NOLOCK) 
WHERE SearchKey = 'IsDanHostel'
AND KeyValue = 1

IF ISNULL(@NoDanhostel, 0) > 0 OR ISNULL(@NoDanhostel1, 0) > 0
BEGIN
  UPDATE CardType SET Enabled = 1 WHERE Type = 23 AND Enabled = 0
END

SELECT TOP 1 @PK_VingType = PK_CardType FROM CardType WITH (NOLOCK) WHERE Type = 1
SELECT TOP 1 @PK_BonusType = PK_CardType FROM CardType WITH (NOLOCK) WHERE Type = 2
SELECT TOP 1 @PK_SocialSecurityType = PK_CardType FROM CardType WITH (NOLOCK) WHERE Type = 3
SELECT TOP 1 @PK_LoginType = PK_CardType FROM CardType WITH (NOLOCK) WHERE Type = 4
SELECT TOP 1 @PK_VIAAdultType = PK_CardType FROM CardType WITH (NOLOCK) WHERE Type = 5
SELECT TOP 1 @PK_VIAChildType = PK_CardType FROM CardType WITH (NOLOCK) WHERE Type = 6
SELECT TOP 1 @PK_SCCType = PK_CardType FROM CardType WITH (NOLOCK) WHERE Type = 7
SELECT TOP 1 @PK_GCType = PK_CardType FROM CardType WITH (NOLOCK) WHERE Type = 8
SELECT TOP 1 @PK_TCType = PK_CardType FROM CardType WITH (NOLOCK) WHERE Type = 9
SELECT TOP 1 @PK_BWRType = PK_CardType FROM CardType WITH (NOLOCK) WHERE Type = 11
SELECT TOP 1 @PK_DanHostelType = PK_CardType FROM CardType WITH (NOLOCK) WHERE Type = 12
SELECT TOP 1 @PK_RogerType = PK_CardType FROM CardType WITH (NOLOCK) WHERE Type = 13
SELECT TOP 1 @PK_DanHostelRewardType = PK_CardType FROM CardType WITH (NOLOCK) WHERE Type = 14 
SELECT TOP 1 @PK_AeldreSagen = PK_CardType FROM CardType WITH (NOLOCK) WHERE Type = 15
SELECT TOP 1 @PK_FDM = PK_CardType FROM CardType WITH (NOLOCK) WHERE Type = 16
SELECT TOP 1 @PK_Matas = PK_CardType FROM CardType WITH (NOLOCK) WHERE Type = 17
SELECT TOP 1 @PK_Coop = PK_CardType FROM CardType WITH (NOLOCK) WHERE Type = 18
SELECT TOP 1 @PK_DanhostelCorporate = PK_CardType FROM CardType WITH (NOLOCK) WHERE Type = 19
SELECT TOP 1 @PK_DanhostelLoyalty = PK_CardType FROM CardType WITH (NOLOCK) WHERE Type = 20
SELECT TOP 1 @PK_PersonaleZonen = PK_CardType FROM CardType WITH (NOLOCK) WHERE Type = 21
SELECT TOP 1 @PK_LOPlus = PK_CardType FROM CardType WITH (NOLOCK) WHERE Type = 22
SELECT TOP 1 @PK_DSBPlus = PK_CardType FROM CardType WITH (NOLOCK) WHERE Type = 23

/***** CREATE TEMPLATES *****/

/* Picasso Bonus */
IF NOT EXISTS(SELECT * FROM CardTemplate WITH (NOLOCK) WHERE RegularExpression = '<<template>>^%TPBC\^<<HotelName>>(.*)\^<<CardNumber>>([0-9]+)\s*\?')
BEGIN
  INSERT INTO CardTemplate (RegularExpression, Note) VALUES ('<<template>>^%TPBC\^<<HotelName>>(.*)\^<<CardNumber>>([0-9]+)\s*\?', 'Picasso Bonus Card')
  SELECT @PK_TplPicassoBonus = PK_CardTemplate FROM CardTemplate WITH (NOLOCK) WHERE RegularExpression = '<<template>>^%TPBC\^<<HotelName>>(.*)\^<<CardNumber>>([0-9]+)\s*\?'
END
/* SAS GetMore */
IF NOT EXISTS(SELECT * FROM CardTemplate WITH (NOLOCK) WHERE RegularExpression = '<<template>>^%/07\s*601599<<CardNumber>>([0-9]+)\?')
BEGIN
  INSERT INTO CardTemplate (RegularExpression, Note) VALUES ('<<template>>^%/07\s*601599<<CardNumber>>([0-9]+)\?', 'SAS GetMore')
  SELECT @PK_TplGetMoreBonus = PK_CardTemplate FROM CardTemplate WITH (NOLOCK) WHERE RegularExpression = '<<template>>^%/07\s*601599<<CardNumber>>([0-9]+)\?'
END
/* SAS GoldPointsPlus */
IF NOT EXISTS(SELECT * FROM CardTemplate WITH (NOLOCK) WHERE RegularExpression = '<<template>>^%601599<<CardNumber>>(([0-9]{4})([0-9]{3})([0-9]{3}))\?')
BEGIN
  INSERT INTO CardTemplate (RegularExpression, Note) VALUES ('<<template>>^%601599<<CardNumber>>(([0-9]{4})([0-9]{3})([0-9]{3}))\?', 'SAS GoldPointsPlus')
  SELECT @PK_TplGPPBonus = PK_CardTemplate FROM CardTemplate WITH (NOLOCK) WHERE RegularExpression = '<<template>>^%601599<<CardNumber>>(([0-9]{4})([0-9]{3})([0-9]{3}))\?'
END
/* SAS EuroBonus */
IF NOT EXISTS(SELECT * FROM CardTemplate WITH (NOLOCK) WHERE RegularExpression = '<<template>>^%B2117<<CardNumber>>(([0-9]{3})([0-9]{3})([0-9]{3})).\^<<LastName>>([ A-Z]+)/<<FirstName>>([A-Z]) <<Title>>([A-Z]{2,3})\s*\^')
BEGIN
  INSERT INTO CardTemplate (RegularExpression, Note) VALUES ('<<template>>^%B2117<<CardNumber>>(([0-9]{3})([0-9]{3})([0-9]{3})).\^<<LastName>>([ A-Z]+)/<<FirstName>>([A-Z]) <<Title>>([A-Z]{2,3})\s*\^', 'SAS EuroBonus')
  SELECT @PK_TplEuroBonus = PK_CardTemplate FROM CardTemplate WITH (NOLOCK) WHERE RegularExpression = '<<template>>^%B2117<<CardNumber>>(([0-9]{3})([0-9]{3})([0-9]{3})).\^<<LastName>>([ A-Z]+)/<<FirstName>>([A-Z]) <<Title>>([A-Z]{2,3})\s*\^'
END
/*** SocialSecurityCard (DK) ***/
IF NOT EXISTS(SELECT * FROM CardTemplate WITH (NOLOCK) WHERE RegularExpression = '<<template>>^%<<LastName>>(.+)&<<FirstName>>(.+)\s<<Address>>(.{34})\d{3}<<ZipCode>>(\d{4}).{10}<<CardNumber>>(<<BirthDate>>(\d{6})<<CheckDigits>>(\d{4}))\d{13}<<ValidFrom>>(\d{6})')
BEGIN
  INSERT INTO CardTemplate (RegularExpression, Note) VALUES ('<<template>>^%<<LastName>>(.+)&<<FirstName>>(.+)\s<<Address>>(.{34})\d{3}<<ZipCode>>(\d{4}).{10}<<CardNumber>>(<<BirthDate>>(\d{6})<<CheckDigits>>(\d{4}))\d{13}<<ValidFrom>>(\d{6})', 'Danish Health Insurance')
  SELECT @PK_TplSocialSecurity = PK_CardTemplate FROM CardTemplate WITH (NOLOCK) WHERE RegularExpression = '<<template>>^%<<LastName>>(.+)&<<FirstName>>(.+)\s<<Address>>(.{34})\d{3}<<ZipCode>>(\d{4}).{10}<<CardNumber>>(<<BirthDate>>(\d{6})<<CheckDigits>>(\d{4}))\d{13}<<ValidFrom>>(\d{6})'
END
/*** LoginCard ***/
IF NOT EXISTS(SELECT * FROM CardTemplate WITH (NOLOCK) WHERE RegularExpression = '<<template>>^%TPLC\^<<HotelName>>(.*)\^<<CardNumber>>([0-9]+)\s*\?')
BEGIN
  INSERT INTO CardTemplate (RegularExpression, Note) VALUES ('<<template>>^%TPLC\^<<HotelName>>(.*)\^<<CardNumber>>([0-9]+)\s*\?', 'Picasso LoginCard')
  SELECT @PK_TplPicassoLogin = PK_CardTemplate FROM CardTemplate WITH (NOLOCK) WHERE RegularExpression LIKE '<<template>>^%TPLC\^<<HotelName>>(.*)\^<<CardNumber>>([0-9]+)\s*\?'
END
/*** VIA Payment RFID***/
IF NOT EXISTS(SELECT * FROM CardTemplate WITH (NOLOCK) WHERE RegularExpression = '<<template>>^%<<LastName>>([^\s]+)\s+<<FirstName>>([^\^]+)\s*\^<<Address>>([^\^]+)\s*\^<<CountryCode>>([A-Z]+)-<<ZipCode>>([0-9]+)\s+<<City>>([^0-9\s]+)\s+[0-9]+\s+\?;[0-9]{4}<<CardNumber>>([^=]+)=')
BEGIN
  INSERT INTO CardTemplate (RegularExpression, Note) VALUES ('<<template>>^%<<LastName>>([^\s]+)\s+<<FirstName>>([^\^]+)\s*\^<<Address>>([^\^]+)\s*\^<<CountryCode>>([A-Z]+)-<<ZipCode>>([0-9]+)\s+<<City>>([^0-9\s]+)\s+[0-9]+\s+\?;[0-9]{4}<<CardNumber>>([^=]+)=', 'SCC Camping Card')
  SELECT TOP 1 @PK_TplViaPayment = PK_CardTemplate FROM CardTemplate WITH (NOLOCK) WHERE Note LIKE '<<template>>^%<<LastName>>([^\s]+)\s+<<FirstName>>([^\^]+)\s*\^<<Address>>([^\^]+)\s*\^<<CountryCode>>([A-Z]+)-<<ZipCode>>([0-9]+)\s+<<City>>([^0-9\s]+)\s+[0-9]+\s+\?;[0-9]{4}<<CardNumber>>([^=]+)='
END
/*** SCC Camping Card ***/
IF NOT EXISTS(SELECT * FROM CardTemplate WITH (NOLOCK) WHERE RegularExpression = '<<template>>^%<<CardID>>([0-9A-F]{10})')
BEGIN
  INSERT INTO CardTemplate (RegularExpression, Note) VALUES ('<<template>>^%<<CardID>>([0-9A-F]{10})', 'VIA Payment RFID')
  SELECT @PK_TplSCCCampingCard = PK_CardTemplate FROM CardTemplate WITH (NOLOCK) WHERE RegularExpression = '<<template>>^%<<CardID>>([0-9A-F]{10})'
END 
/*** Tanum Bonus***/
IF NOT EXISTS(SELECT * FROM CardTemplate WITH (NOLOCK) WHERE RegularExpression = '<<template flags=imsg-r>>^;151230*<<CardNumber>>([0-9]{0,6})\?')
BEGIN
  INSERT INTO CardTemplate (RegularExpression, Note) VALUES ('<<template flags=imsg-r>>^;151230*<<CardNumber>>([0-9]{0,6})\?', 'Tanum Bonus')
  SELECT TOP 1 @PK_TplTanumBonus = PK_CardTemplate FROM CardTemplate WITH (NOLOCK) WHERE RegularExpression = '<<template flags=imsg-r>>^;151230*<<CardNumber>>([0-9]{0,6})\?'
END
/*** Picasso Gift Certificate ***/
IF NOT EXISTS(SELECT * FROM CardTemplate WITH (NOLOCK) WHERE RegularExpression = '<<template>>^%TPBC\&<<HotelName>>(.*)\&<<CardNumber>>([0-9]+)\s*\?')
BEGIN
  INSERT INTO CardTemplate (RegularExpression, Note) VALUES ('<<template>>^%TPBC\&<<HotelName>>(.*)\&<<CardNumber>>([0-9]+)\s*\?', 'Picasso Gift Certificate')
  SELECT @PK_TplGiftCertificate = PK_CardTemplate FROM CardTemplate WITH (NOLOCK) WHERE RegularExpression = '<<template>>^%TPBC\&<<HotelName>>(.*)\&<<CardNumber>>([0-9]+)\s*\?'
END

/*** Transfer/Guest Card ***/
IF NOT EXISTS(SELECT * FROM CardTemplate WITH (NOLOCK) WHERE RegularExpression = '<<template>>^%TPTC\^<<HotelName>>(.*)\^<<CardNumber>>([0-9]+)\s*\?')
BEGIN
  INSERT INTO CardTemplate (RegularExpression, Note) VALUES ('<<template>>^%TPTC\^<<HotelName>>(.*)\^<<CardNumber>>([0-9]+)\s*\?', 'Picasso Transfer/Guest Card')
  SELECT @PK_TplTransferCard = PK_CardTemplate FROM CardTemplate WITH (NOLOCK) WHERE RegularExpression = '<<template>>^%TPTC\^<<HotelName>>(.*)\^<<CardNumber>>([0-9]+)\s*\?'
END

/*** Customer Card in Spor 2***/
IF NOT EXISTS(SELECT * FROM CardTemplate WITH (NOLOCK) WHERE RegularExpression = '<<template>>^;TPDC\^<<HotelName>>(.*)\^<<CardNumber>>([0-9]+)\s*\?')
BEGIN
  INSERT INTO CardTemplate (RegularExpression, Note) VALUES ('<<template>>^;TPDC\^<<HotelName>>(.*)\^<<CardNumber>>([0-9]+)\s*\?', 'Customer Card')
  SELECT @PK_TplCustomerCard = PK_CardTemplate FROM CardTemplate WITH (NOLOCK) WHERE RegularExpression = '<<template>>^;TPDC\^<<HotelName>>(.*)\^<<CardNumber>>([0-9]+)\s*\?'
END

-- Best Western Reward
IF NOT EXISTS(SELECT * FROM CardTemplate WITH (NOLOCK) WHERE RegularExpression = '<<template>>^;<<CardNumber>>([0-9]{16})\?')
BEGIN
  INSERT INTO CardTemplate (RegularExpression, Note) VALUES ('<<template>>^;<<CardNumber>>([0-9]{16})\?', 'BEST WESTERN REWARDS')
  SELECT @PK_TplBWRCard = PK_CardTemplate FROM CardTemplate WITH (NOLOCK) WHERE RegularExpression = '<<template>>^;<<CardNumber>>([0-9]{16})\?'
END

-- Danhostel family card
IF EXISTS(SELECT * FROM CardTemplate WITH (NOLOCK) WHERE Note = 'DanHostel')                                                                               
  UPDATE CardTemplate 
  SET RegularExpression = '<<template>>^;[0-9]{26}<<CardNumber>>(016[0-9]{8})\?'
  WHERE Note = 'DanHostel'
ELSE
BEGIN
  INSERT INTO CardTemplate (RegularExpression, Note) VALUES ('<<template>>^;[0-9]{26}<<CardNumber>>(016[0-9]{8})\?', 'Danhostel')
  SELECT @PK_TplDanhostelCard = PK_CardTemplate FROM CardTemplate WITH (NOLOCK) WHERE Note = 'Danhostel'
END

-- Roger
IF NOT EXISTS(SELECT * FROM CardTemplate WITH (NOLOCK) WHERE RegularExpression = '<<template>>^%<<CardNumber>>([0-9]+)')
BEGIN
  INSERT INTO CardTemplate (RegularExpression, Note) VALUES ('<<template>>^%<<CardNumber>>([0-9]+)', 'Roger')
  SELECT @PK_TplRogerCard = PK_CardTemplate FROM CardTemplate WITH (NOLOCK) WHERE RegularExpression = '<<template>>^%<<CardNumber>>([0-9]+)'
END

-- Danhostel reward card
IF EXISTS(SELECT * FROM CardTemplate WITH (NOLOCK) WHERE Note = 'DanhostelReward')                                                                               
  UPDATE CardTemplate 
  SET RegularExpression = '<<template>>^;[0-9]{26}<<CardNumber>>(910[0-9]{8})\?'
  WHERE Note = 'DanhostelReward'
ELSE
BEGIN
  INSERT INTO CardTemplate (RegularExpression, Note) VALUES ('<<template>>^;[0-9]{26}<<CardNumber>>(910[0-9]{8})\?', 'DanhostelReward')
  SELECT @PK_TplDanhostelRewardCard = PK_CardTemplate FROM CardTemplate WITH (NOLOCK) WHERE Note = 'DanhostelReward'
END

-- ÆldreSagen
IF NOT EXISTS(SELECT * FROM CardTemplate WITH (NOLOCK) WHERE Note = 'ÆldreSagen')     
BEGIN
  INSERT INTO CardTemplate (RegularExpression, Note) VALUES ('<<template>><<CardNumber>>(\d+)\?', 'ÆldreSagen')  
  SELECT @PK_TplAeldreSagen = PK_CardTemplate FROM CardTemplate WITH (NOLOCK) WHERE Note = 'ÆldreSagen'     
END
ELSE
BEGIN
  UPDATE CardTemplate 
  SET RegularExpression = '<<template>><<CardNumber>>(\d+)\?'
  WHERE Note = 'ÆldreSagen' AND RegularExpression <> '<<template>><<CardNumber>>(\d+)\?'
END

-- FDM
IF NOT EXISTS(SELECT * FROM CardTemplate WITH (NOLOCK) WHERE Note = 'FDM')     
BEGIN
  INSERT INTO CardTemplate (RegularExpression, Note) VALUES ('<<template>><<CardNumber>>(\d+)\?', 'FDM')
  SELECT @PK_TplFDM = PK_CardTemplate FROM CardTemplate WITH (NOLOCK) WHERE Note = 'FDM'     
END
ELSE
BEGIN
  UPDATE CardTemplate 
  SET RegularExpression = '<<template>><<CardNumber>>(\d+)\?'
  WHERE Note = 'FDM' AND RegularExpression <> '<<template>><<CardNumber>>(\d+)\?'
END

IF NOT EXISTS(SELECT * FROM CardTemplate WITH (NOLOCK) WHERE Note = 'Matas')     
BEGIN
  INSERT INTO CardTemplate (RegularExpression, Note) VALUES ('<<template>><<CardNumber>>(\d+)\?', 'Matas')
  SELECT @PK_TplMatas = PK_CardTemplate FROM CardTemplate WITH (NOLOCK) WHERE Note = 'Matas'
END

IF NOT EXISTS(SELECT * FROM CardTemplate WITH (NOLOCK) WHERE Note = 'Coop')     
BEGIN
  INSERT INTO CardTemplate (RegularExpression, Note) VALUES ('<<template>><<CardNumber>>(\d+)\?', 'Coop')
  SELECT @PK_TplCoop = PK_CardTemplate FROM CardTemplate WITH (NOLOCK) WHERE Note = 'Coop'
END

IF NOT EXISTS(SELECT * FROM CardTemplate WITH (NOLOCK) WHERE Note = 'FirmaCard')
BEGIN
  INSERT INTO CardTemplate (RegularExpression, Note) VALUES ('<<template>><<CardNumber>>(\d+)\?', 'FirmaCard')
  SELECT @PK_TplDanhostelCorporate = PK_CardTemplate FROM CardTemplate WITH (NOLOCK) WHERE Note = 'FirmaCard'
END

IF NOT EXISTS(SELECT * FROM CardTemplate WITH (NOLOCK) WHERE Note = 'LoyaltyCard')
BEGIN
  INSERT INTO CardTemplate (RegularExpression, Note) VALUES ('<<template>><<CardNumber>>(\d+)\?', 'LoyalityCard')
  SELECT @PK_TplDanhostelLoyalty = PK_CardTemplate FROM CardTemplate WITH (NOLOCK) WHERE Note = 'LoyalityCard'
END

IF NOT EXISTS(SELECT * FROM CardTemplate WITH (NOLOCK) WHERE Note = 'PersonaleZonen')
BEGIN
  INSERT INTO CardTemplate (RegularExpression, Note) VALUES ('<<template>><<CardNumber>>(\d+)\?', 'PersonaleZonen')
  SELECT @PK_TplPersonaleZonen = PK_CardTemplate FROM CardTemplate WITH (NOLOCK) WHERE Note = 'PersonaleZonen'
END

SELECT @HotelCustomerNumberLocal = CustomerNumber FROM Customer WITH (NOLOCK) WHERE LocalHotel = 1
SET @HotelCustomerNumberLocal = ISNULL(@HotelCustomerNumberLocal, -1)

IF NOT EXISTS(SELECT * FROM CardTemplate WITH (NOLOCK) WHERE Note = 'LOPlus')
BEGIN
  INSERT INTO CardTemplate (RegularExpression, Note) VALUES ('<<template>><<CardNumber>>(\d+)\?', 'LOPlus')
  SELECT @PK_TplLOPlus = PK_CardTemplate FROM CardTemplate WITH (NOLOCK) WHERE Note = 'LOPlus'
END

IF NOT EXISTS(SELECT * FROM CardTemplate WITH (NOLOCK) WHERE Note = 'DSBPlus')
BEGIN
  INSERT INTO CardTemplate (RegularExpression, Note) VALUES ('<<template>><<CardNumber>>(\d+)\?', 'DSBPlus')
  SELECT @PK_TplDSBPlus = PK_CardTemplate FROM CardTemplate WITH (NOLOCK) WHERE Note = 'DSBPlus'
END

SELECT @HotelCustomerNumberLocal = CustomerNumber FROM Customer WITH (NOLOCK) WHERE LocalHotel = 1
SET @HotelCustomerNumberLocal = ISNULL(@HotelCustomerNumberLocal, -1)

/***** CREATE TEMPLATE TO CARDTYPE MAPPINGS *****/

IF NOT ISNULL(@PK_BonusType, 0) = 0 AND NOT ISNULL(@PK_TplPicassoBonus, 0) = 0 AND
  NOT EXISTS(SELECT * FROM CardTypeTemplateXTab WITH (NOLOCK) WHERE FK_CardType = @PK_BonusType AND FK_CardTemplate = @PK_TplPicassoBonus)
  INSERT INTO CardTypeTemplateXTab (FK_CardType, FK_CardTemplate, HotelCustomerNumber) VALUES (@PK_BonusType, @PK_TplPicassoBonus, @HotelCustomerNumberLocal)

IF NOT ISNULL(@PK_BonusType, 0) = 0 AND NOT ISNULL(@PK_TplGetMoreBonus, 0) = 0 AND
  NOT EXISTS(SELECT * FROM CardTypeTemplateXTab WITH (NOLOCK) WHERE FK_CardType = @PK_BonusType AND FK_CardTemplate = @PK_TplGetMoreBonus)
  INSERT INTO CardTypeTemplateXTab (FK_CardType, FK_CardTemplate, HotelCustomerNumber) VALUES (@PK_BonusType, @PK_TplGetMoreBonus, @HotelCustomerNumberLocal)
  
IF NOT ISNULL(@PK_BonusType, 0) = 0 AND NOT ISNULL(@PK_TplGPPBonus, 0) = 0 AND
  NOT EXISTS(SELECT * FROM CardTypeTemplateXTab WITH (NOLOCK) WHERE FK_CardType = @PK_BonusType AND FK_CardTemplate = @PK_TplGPPBonus)
  INSERT INTO CardTypeTemplateXTab (FK_CardType, FK_CardTemplate, HotelCustomerNumber) VALUES (@PK_BonusType, @PK_TplGPPBonus, @HotelCustomerNumberLocal)
  
IF NOT ISNULL(@PK_BonusType, 0) = 0 AND NOT ISNULL(@PK_TplEuroBonus, 0) = 0 AND
  NOT EXISTS(SELECT * FROM CardTypeTemplateXTab WITH (NOLOCK) WHERE FK_CardType = @PK_BonusType AND FK_CardTemplate = @PK_TplEuroBonus)
  INSERT INTO CardTypeTemplateXTab (FK_CardType, FK_CardTemplate, HotelCustomerNumber) VALUES (@PK_BonusType, @PK_TplEuroBonus, @HotelCustomerNumberLocal)
  
IF NOT ISNULL(@PK_SocialSecurityType, 0) = 0 AND NOT ISNULL(@PK_TplSocialSecurity, 0) = 0 AND
  NOT EXISTS(SELECT * FROM CardTypeTemplateXTab WITH (NOLOCK) WHERE FK_CardType = @PK_SocialSecurityType AND FK_CardTemplate = @PK_TplSocialSecurity)
  INSERT INTO CardTypeTemplateXTab (FK_CardType, FK_CardTemplate, HotelCustomerNumber) VALUES (@PK_SocialSecurityType, @PK_TplSocialSecurity, @HotelCustomerNumberLocal)
  
IF NOT ISNULL(@PK_LoginType, 0) = 0 AND NOT ISNULL(@PK_TplPicassoLogin, 0) = 0 AND
  NOT EXISTS(SELECT * FROM CardTypeTemplateXTab WITH (NOLOCK) WHERE FK_CardType = @PK_LoginType AND FK_CardTemplate = @PK_TplPicassoLogin)
  INSERT INTO CardTypeTemplateXTab (FK_CardType, FK_CardTemplate, HotelCustomerNumber) VALUES (@PK_LoginType, @PK_TplPicassoLogin, @HotelCustomerNumberLocal)
  
IF NOT ISNULL(@PK_SCCType, 0) = 0 AND NOT ISNULL(@PK_TplSCCCampingCard, 0) = 0 AND
  NOT EXISTS(SELECT * FROM CardTypeTemplateXTab WITH (NOLOCK) WHERE FK_CardType = @PK_SCCType AND FK_CardTemplate = @PK_TplSCCCampingCard)
  INSERT INTO CardTypeTemplateXTab (FK_CardType, FK_CardTemplate, HotelCustomerNumber) VALUES (@PK_SCCType, @PK_TplSCCCampingCard, @HotelCustomerNumberLocal)
  
IF NOT ISNULL(@PK_VIAAdultType, 0) = 0 AND NOT ISNULL(@PK_TplViaPayment, 0) = 0 AND
  NOT EXISTS(SELECT * FROM CardTypeTemplateXTab WITH (NOLOCK) WHERE FK_CardType = @PK_VIAAdultType AND FK_CardTemplate = @PK_TplViaPayment)
  INSERT INTO CardTypeTemplateXTab (FK_CardType, FK_CardTemplate, HotelCustomerNumber) VALUES (@PK_VIAAdultType, @PK_TplViaPayment, @HotelCustomerNumberLocal)
  
IF NOT ISNULL(@PK_VIAChildType, 0) = 0 AND NOT ISNULL(@PK_TplViaPayment, 0) = 0 AND
  NOT EXISTS(SELECT * FROM CardTypeTemplateXTab WITH (NOLOCK) WHERE FK_CardType = @PK_VIAChildType AND FK_CardTemplate = @PK_TplViaPayment)
  INSERT INTO CardTypeTemplateXTab (FK_CardType, FK_CardTemplate, HotelCustomerNumber) VALUES (@PK_VIAChildType, @PK_TplViaPayment, @HotelCustomerNumberLocal)
  
IF NOT ISNULL(@PK_BonusType, 0) = 0 AND NOT ISNULL(@PK_TplTanumBonus, 0) = 0 AND
  NOT EXISTS(SELECT * FROM CardTypeTemplateXTab WITH (NOLOCK) WHERE FK_CardType = @PK_BonusType AND FK_CardTemplate = @PK_TplTanumBonus)
  INSERT INTO CardTypeTemplateXTab (FK_CardType, FK_CardTemplate, HotelCustomerNumber) VALUES (@PK_BonusType, @PK_TplTanumBonus, @HotelCustomerNumberLocal)

IF NOT ISNULL(@PK_GCType, 0) = 0 AND NOT ISNULL(@PK_TplGiftCertificate, 0) = 0 AND
  NOT EXISTS(SELECT * FROM CardTypeTemplateXTab WITH (NOLOCK) WHERE FK_CardType = @PK_GCType AND FK_CardTemplate = @PK_TplGiftCertificate)
  INSERT INTO CardTypeTemplateXTab (FK_CardType, FK_CardTemplate, HotelCustomerNumber) VALUES (@PK_GCType, @PK_TplGiftCertificate, @HotelCustomerNumberLocal)

IF NOT ISNULL(@PK_TCType, 0) = 0 AND NOT ISNULL(@PK_TplTransferCard, 0) = 0 AND
  NOT EXISTS(SELECT * FROM CardTypeTemplateXTab WITH (NOLOCK) WHERE FK_CardType = @PK_TCType AND FK_CardTemplate = @PK_TplTransferCard)
  INSERT INTO CardTypeTemplateXTab (FK_CardType, FK_CardTemplate, HotelCustomerNumber) VALUES (@PK_TCType, @PK_TplTransferCard, @HotelCustomerNumberLocal)

/*customer card is the same as bonus card therefore the type is @PK_BonusType*/
IF NOT ISNULL(@PK_BonusType, 0) = 0 AND NOT ISNULL(@PK_TplCustomerCard, 0) = 0 AND
  NOT EXISTS(SELECT * FROM CardTypeTemplateXTab WITH (NOLOCK) WHERE FK_CardType = @PK_BonusType AND FK_CardTemplate = @PK_TplCustomerCard)
  INSERT INTO CardTypeTemplateXTab (FK_CardType, FK_CardTemplate, HotelCustomerNumber) VALUES (@PK_BonusType, @PK_TplCustomerCard, @HotelCustomerNumberLocal)

IF NOT ISNULL(@PK_BWRType, 0) = 0 AND NOT ISNULL(@PK_TplBWRCard, 0) = 0 AND
  NOT EXISTS(SELECT * FROM CardTypeTemplateXTab WITH (NOLOCK) WHERE FK_CardType = @PK_BWRType AND FK_CardTemplate = @PK_TplBWRCard)
  INSERT INTO CardTypeTemplateXTab (FK_CardType, FK_CardTemplate, HotelCustomerNumber) VALUES (@PK_BWRType, @PK_TplBWRCard, @HotelCustomerNumberLocal)

IF NOT ISNULL(@PK_DanHostelType, 0) = 0 AND NOT ISNULL(@PK_TplDanhostelCard, 0) = 0 AND
  NOT EXISTS(SELECT * FROM CardTypeTemplateXTab WITH (NOLOCK) WHERE FK_CardType = @PK_DanHostelType AND FK_CardTemplate = @PK_TplDanhostelCard)
  INSERT INTO CardTypeTemplateXTab (FK_CardType, FK_CardTemplate, HotelCustomerNumber) VALUES (@PK_DanHostelType, @PK_TplDanhostelCard, @HotelCustomerNumberLocal)

IF NOT ISNULL(@PK_RogerType, 0) = 0 AND NOT ISNULL(@PK_TplRogerCard, 0) = 0 AND
  NOT EXISTS(SELECT * FROM CardTypeTemplateXTab WITH (NOLOCK) WHERE FK_CardType = @PK_RogerType AND FK_CardTemplate = @PK_TplRogerCard)
  INSERT INTO CardTypeTemplateXTab (FK_CardType, FK_CardTemplate, HotelCustomerNumber) VALUES (@PK_RogerType, @PK_TplRogerCard, @HotelCustomerNumberLocal)

IF NOT ISNULL(@PK_DanHostelRewardType, 0) = 0 AND NOT ISNULL(@PK_TplDanhostelRewardCard, 0) = 0 AND
  NOT EXISTS(SELECT * FROM CardTypeTemplateXTab WITH (NOLOCK) WHERE FK_CardType = @PK_DanHostelRewardType AND FK_CardTemplate = @PK_TplDanhostelRewardCard)
  INSERT INTO CardTypeTemplateXTab (FK_CardType, FK_CardTemplate, HotelCustomerNumber) VALUES (@PK_DanHostelRewardType, @PK_TplDanhostelRewardCard, @HotelCustomerNumberLocal)

IF NOT ISNULL(@PK_AeldreSagen, 0) = 0 AND NOT ISNULL(@PK_TplAeldreSagen, 0) = 0 AND
  NOT EXISTS(SELECT * FROM CardTypeTemplateXTab WITH (NOLOCK) WHERE FK_CardType = @PK_AeldreSagen AND FK_CardTemplate = @PK_TplAeldreSagen)
  INSERT INTO CardTypeTemplateXTab (FK_CardType, FK_CardTemplate, HotelCustomerNumber) VALUES (@PK_AeldreSagen, @PK_TplAeldreSagen, @HotelCustomerNumberLocal)

IF NOT ISNULL(@PK_FDM, 0) = 0 AND NOT ISNULL(@PK_TplFDM, 0) = 0 AND
  NOT EXISTS(SELECT * FROM CardTypeTemplateXTab WITH (NOLOCK) WHERE FK_CardType = @PK_FDM AND FK_CardTemplate = @PK_TplFDM)
  INSERT INTO CardTypeTemplateXTab (FK_CardType, FK_CardTemplate, HotelCustomerNumber) VALUES (@PK_FDM, @PK_TplFDM, @HotelCustomerNumberLocal)

IF NOT ISNULL(@PK_Matas, 0) = 0 AND NOT ISNULL(@PK_TplMatas, 0) = 0 AND
  NOT EXISTS(SELECT * FROM CardTypeTemplateXTab WITH (NOLOCK) WHERE FK_CardType = @PK_Matas AND FK_CardTemplate = @PK_TplMatas)
  INSERT INTO CardTypeTemplateXTab (FK_CardType, FK_CardTemplate, HotelCustomerNumber) VALUES (@PK_Matas, @PK_TplMatas, @HotelCustomerNumberLocal)

IF NOT ISNULL(@PK_Coop, 0) = 0 AND NOT ISNULL(@PK_TplCoop, 0) = 0 AND
  NOT EXISTS(SELECT * FROM CardTypeTemplateXTab WITH (NOLOCK) WHERE FK_CardType = @PK_Coop AND FK_CardTemplate = @PK_TplCoop)
  INSERT INTO CardTypeTemplateXTab (FK_CardType, FK_CardTemplate, HotelCustomerNumber) VALUES (@PK_Coop, @PK_TplCoop, @HotelCustomerNumberLocal)

IF NOT ISNULL(@PK_DanhostelCorporate, 0) = 0 AND NOT ISNULL(@PK_TplDanhostelCorporate, 0) = 0 AND
  NOT EXISTS(SELECT * FROM CardTypeTemplateXTab WITH (NOLOCK) WHERE FK_CardType = @PK_DanhostelCorporate AND FK_CardTemplate = @PK_TplDanhostelCorporate)
  INSERT INTO CardTypeTemplateXTab (FK_CardType, FK_CardTemplate, HotelCustomerNumber) VALUES (@PK_DanhostelCorporate, @PK_TplDanhostelCorporate, @HotelCustomerNumberLocal)

IF NOT ISNULL(@PK_DanhostelLoyalty, 0) = 0 AND NOT ISNULL(@PK_TplDanhostelLoyalty, 0) = 0 AND
  NOT EXISTS(SELECT * FROM CardTypeTemplateXTab WITH (NOLOCK) WHERE FK_CardType = @PK_DanhostelLoyalty AND FK_CardTemplate = @PK_TplDanhostelLoyalty)
  INSERT INTO CardTypeTemplateXTab (FK_CardType, FK_CardTemplate, HotelCustomerNumber) VALUES (@PK_DanhostelLoyalty, @PK_TplDanhostelLoyalty, @HotelCustomerNumberLocal)

IF NOT ISNULL(@PK_PersonaleZonen, 0) = 0 AND NOT ISNULL(@PK_TplPersonaleZonen, 0) = 0 AND
  NOT EXISTS(SELECT * FROM CardTypeTemplateXTab WITH (NOLOCK) WHERE FK_CardType = @PK_PersonaleZonen AND FK_CardTemplate = @PK_TplPersonaleZonen)
  INSERT INTO CardTypeTemplateXTab (FK_CardType, FK_CardTemplate, HotelCustomerNumber) VALUES (@PK_PersonaleZonen, @PK_TplPersonaleZonen, @HotelCustomerNumberLocal)

IF NOT ISNULL(@PK_LOPlus, 0) = 0 AND NOT ISNULL(@PK_TplLOPlus, 0) = 0 AND
  NOT EXISTS(SELECT * FROM CardTypeTemplateXTab WITH (NOLOCK) WHERE FK_CardType = @PK_LOPlus AND FK_CardTemplate = @PK_TplLOPlus)
  INSERT INTO CardTypeTemplateXTab (FK_CardType, FK_CardTemplate, HotelCustomerNumber) VALUES (@PK_LOPlus, @PK_TplLOPlus, @HotelCustomerNumberLocal)

IF NOT ISNULL(@PK_DSBPlus, 0) = 0 AND NOT ISNULL(@PK_TplDSBPlus, 0) = 0 AND
  NOT EXISTS(SELECT * FROM CardTypeTemplateXTab WITH (NOLOCK) WHERE FK_CardType = @PK_DSBPlus AND FK_CardTemplate = @PK_TplDSBPlus)
  INSERT INTO CardTypeTemplateXTab (FK_CardType, FK_CardTemplate, HotelCustomerNumber) VALUES (@PK_DSBPlus, @PK_TplDSBPlus, @HotelCustomerNumberLocal)

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[CardTypeExt]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
  CREATE TABLE [dbo].[CardTypeExt](
    [FK_CardTypeId] [int] NOT NULL,
    [Description] [varchar](150) NULL,
    [DescriptionShort] [varchar](50) NULL,
    [Tooltip] [varchar](50) NULL,
    [IconURL] [varchar](150) NULL,
    [ProvideDiscount] [bit] NULL,
    [DiscountPercent] [smallmoney] NULL,
    [Discount] [smallmoney] NULL,
    [ProvideCashBack] [bit] NULL,
    [ExternalCardId] [int] NULL,
    [ProviderName] [varchar](50) NULL,
    [ClientType] [int] NULL,
    [ZipRequired] [bit] NULL,
    [ProvideBonus] [bit] NULL,
    CONSTRAINT [PK_CardTypeExt] PRIMARY KEY CLUSTERED 
    (
      [FK_CardTypeId] ASC
    )
  ) ON [PRIMARY]

  ALTER TABLE [dbo].[CardTypeExt]  WITH CHECK ADD  CONSTRAINT [FK_CardTypeExt_CardType] FOREIGN KEY([FK_CardTypeId])
  REFERENCES [dbo].[CardType] ([PK_CardType])

  ALTER TABLE [dbo].[CardTypeExt] CHECK CONSTRAINT [FK_CardTypeExt_CardType]
END

IF NOT EXISTS(SELECT * FROM CardTypeExt WITH (NOLOCK) WHERE FK_CardTypeId = @PK_AeldreSagen)
  INSERT INTO CardTypeExt (FK_CardTypeId, Description, DescriptionShort, Tooltip, IconURL, ProvideDiscount, DiscountPercent, Discount, ProvideCashBack, ExternalCardId, ProviderName, ClientType, ZipRequired)
  VALUES (@PK_AeldreSagen, 'Eldresagen discount cards', 'Ældresagen medlemskort', 'Ældresagen medlemskort', 'https://crm.danhostel.dk/cardicons/eldresagen.png', 1, 10, 0, 0, 3, 'Eldresagen', 1, 1)
ELSE
  UPDATE CardTypeExt SET ExternalCardId = 3 WHERE FK_CardTypeId = @PK_AeldreSagen
IF NOT EXISTS(SELECT * FROM CardTypeExt WITH (NOLOCK) WHERE FK_CardTypeId = @PK_FDM)
  INSERT INTO CardTypeExt (FK_CardTypeId, Description, DescriptionShort, Tooltip, IconURL, ProvideDiscount, DiscountPercent, Discount, ProvideCashBack, ExternalCardId, ProviderName, ClientType, ZipRequired)
  VALUES (@PK_FDM, 'Fdm discount cards', 'FDM fordelskort', 'FDM fordelskort', 'https://crm.danhostel.dk/cardicons/fdm.png', 1, 10, 0, 0, 4, 'FDM', 0, 0)
ELSE
  UPDATE CardTypeExt SET ExternalCardId = 4 WHERE FK_CardTypeId = @PK_FDM
IF NOT EXISTS(SELECT * FROM CardTypeExt WITH (NOLOCK) WHERE FK_CardTypeId = @PK_Matas)
  INSERT INTO CardTypeExt (FK_CardTypeId, Description, DescriptionShort, Tooltip, IconURL, ProvideDiscount, DiscountPercent, Discount, ProvideCashBack, ExternalCardId, ProviderName, ClientType, ZipRequired)
  VALUES (@PK_Matas, 'Matas discount cards', 'Club Matas kort', 'Club Matas kort', 'https://crm.danhostel.dk/cardicons/matas.png', 0, 10, 0, 1, 5, 'Matas', 2, 0)
ELSE
  UPDATE CardTypeExt SET ExternalCardId = 5 WHERE FK_CardTypeId = @PK_Matas
IF NOT EXISTS(SELECT * FROM CardTypeExt WITH (NOLOCK) WHERE FK_CardTypeId = @PK_Coop)
  INSERT INTO CardTypeExt (FK_CardTypeId, Description, DescriptionShort, Tooltip, IconURL, ProvideDiscount, DiscountPercent, Discount, ProvideCashBack, ExternalCardId, ProviderName, ClientType, ZipRequired)
  VALUES (@PK_Coop, 'Coop discount cards', 'Coop medlemskort', 'Coop medlemskort', 'https://crm.danhostel.dk/cardicons/coop.png', 0, 10, 0, 1, 6, 'Coop', 3, 0)
ELSE
  UPDATE CardTypeExt SET ExternalCardId = 6 WHERE FK_CardTypeId = @PK_Coop
IF NOT EXISTS(SELECT * FROM CardTypeExt WITH (NOLOCK) WHERE FK_CardTypeId = @PK_DanhostelCorporate)
  INSERT INTO CardTypeExt (FK_CardTypeId, Description, DescriptionShort, Tooltip, IconURL, ProvideDiscount, DiscountPercent, Discount, ProvideCashBack, ExternalCardId, ProviderName, ClientType, ZipRequired)
  VALUES (@PK_DanhostelCorporate, 'Firma Danhostel cards', 'Danhostel Firmakort', 'Danhostel Firmakort', 'https://crm.danhostel.dk/cardicons/firma_card.png', 1, 10, 0, 0, 2, 'FirmaCard', 4, 0)
ELSE
  UPDATE CardTypeExt SET ExternalCardId = 2 WHERE FK_CardTypeId = @PK_DanhostelCorporate
IF NOT EXISTS(SELECT * FROM CardTypeExt WITH (NOLOCK) WHERE FK_CardTypeId = @PK_DanhostelLoyalty)
  INSERT INTO CardTypeExt (FK_CardTypeId, Description, DescriptionShort, Tooltip, IconURL, ProvideDiscount, DiscountPercent, Discount, ProvideCashBack, ExternalCardId, ProviderName, ClientType, ZipRequired, ProvideBonus)
  VALUES (@PK_DanhostelLoyalty, 'Loyality Danhostel cards', 'Danhostel Fordelskort', 'Danhostel Fordelskort', 'https://crm.danhostel.dk/cardicons/loyality_card.png', 1, 10, 0, 0, 1, 'LoyaltyCard', 5, 0, 1)
ELSE
  UPDATE CardTypeExt SET ExternalCardId = 1, ProvideBonus = 1 WHERE FK_CardTypeId = @PK_DanhostelLoyalty
IF NOT EXISTS(SELECT * FROM CardTypeExt WITH (NOLOCK) WHERE FK_CardTypeId = @PK_PersonaleZonen)
  INSERT INTO CardTypeExt (FK_CardTypeId, Description, DescriptionShort, Tooltip, IconURL, ProvideDiscount, DiscountPercent, Discount, ProvideCashBack, ExternalCardId, ProviderName, ClientType, ZipRequired)
  VALUES (@PK_PersonaleZonen, 'PersonaleZonen medlemskort', 'PersonaleZonen medlemskort', 'PersonaleZonen medlemskort', 'https://crm.danhostel.dk/cardicons/personalezonen.png', 1, 10, 0, 0, 7, 'PersonaleZonen', 6, 0)
ELSE
  UPDATE CardTypeExt SET ExternalCardId = 6 WHERE FK_CardTypeId = @PK_PersonaleZonen
IF NOT EXISTS(SELECT * FROM CardTypeExt WITH (NOLOCK) WHERE FK_CardTypeId = @PK_LOPlus)
  INSERT INTO CardTypeExt (FK_CardTypeId, Description, DescriptionShort, Tooltip, IconURL, ProvideDiscount, DiscountPercent, Discount, ProvideCashBack, ExternalCardId, ProviderName, ClientType, ZipRequired)
  VALUES (@PK_LOPlus, 'LO Pluskort', 'LO Pluskort', 'LO Pluskort', 'https://crm.danhostel.dk/cardicons/loplus.png', 1, 10, 0, 0, 8, 'LoPlus', 7, 0)
ELSE
  UPDATE CardTypeExt SET ExternalCardId = 8 WHERE FK_CardTypeId = @PK_LOPlus
IF NOT EXISTS(SELECT * FROM CardTypeExt WITH (NOLOCK) WHERE FK_CardTypeId = @PK_DSBPlus)
  INSERT INTO CardTypeExt (FK_CardTypeId, Description, DescriptionShort, Tooltip, IconURL, ProvideDiscount, DiscountPercent, Discount, ProvideCashBack, ExternalCardId, ProviderName, ClientType, ZipRequired)
  VALUES (@PK_DSBPlus, 'DSBPlus', 'DSB Plus', 'DSB Plus', 'https://crm.danhostel.dk/cardicons/dsb.png', 1, 10, 0, 0, 9, 'DSBPlus', 8, 0)
ELSE
  UPDATE CardTypeExt SET ExternalCardId = 9 WHERE FK_CardTypeId = @PK_DSBPlus