/*
 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

 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 */
  @PK_BonusType int,    /* ctBonus = 2 */
  @PK_SocialSecurityType int, /* ctSocialSecurity = 3 */
  @PK_LoginType int,    /* ctLogin = 4 */
  @PK_VIAAdultType int,   /* ctVIApaymentAdult = 5 */
  @PK_VIAChildType int,   /* ctVIApaymentChild = 6 */
  @PK_SCCType int,     /* ctSCCCampingCard = 7 */
  @PK_GCType int, /*ctGiftCertificate = 8 */
  @PK_TCType int, /*ctTransferCard = 9 */
  @PK_DCType int, /*ctTransferCard = 10 */
  --@PK_BWRType int, /*ctBestWesternReward = 11 */
  @PK_DanHostelType int, /*ctDanHostel = 12 */
  @PK_RogerType int, /*ctRoger = 13 */
  @PK_DanHostelRewardType int /*ctDanHostelReward = 14 */


/* 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

/***** 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)
/* Customer card (Dayz request)  do not exist yet dayzs card is type: bonus*/
/*IF NOT EXISTS(SELECT * FROM CardType WITH (NOLOCK) WHERE Type = 10 /* Customer Card  */)
  INSERT INTO CardType (Type, Name, Enabled, SortNumber) VALUES (10, 'Customer Card ', 1, 10)*/
--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)

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 

/***** 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 WHERE RegularExpression = '<<template>>^;<<CardNumber>>([0-9]{16})\?'
--END

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

  SELECT @PK_TplDanhostelCard = PK_CardTemplate FROM CardTemplate WITH (NOLOCK) WHERE RegularExpression = '<<template>>^;[0-9]{26}016<<CardNumber>>([0-9]{8})\?'
END
*/
/*
IF NOT EXISTS(SELECT * FROM CardTemplate WITH (NOLOCK) WHERE RegularExpression = '<<template>>^;[0-9]{26}<<CardNumber>>(016[0-9]{8})\?')                                                                               
BEGIN
  IF EXISTS (SELECT * FROM CardTemplate WITH (NOLOCK) WHERE RegularExpression = '<<template>>^;<<CardNumber>>([0-9]{37})\?')
    UPDATE CardTemplate 
    SET RegularExpression = '<<template>>^;[0-9]{26}<<CardNumber>>(016[0-9]{8})\?'
    WHERE RegularExpression = '<<template>>^;<<CardNumber>>([0-9]{37})\?'
  ELSE IF EXISTS (SELECT * FROM CardTemplate WITH (NOLOCK) WHERE RegularExpression = '<<template>>^;[0-9]{26}016<<CardNumber>>([0-9]{8})\?')
    UPDATE CardTemplate 
    SET RegularExpression = '<<template>>^;[0-9]{26}<<CardNumber>>(016[0-9]{8})\?'
    WHERE RegularExpression = '<<template>>^;[0-9]{26}016<<CardNumber>>([0-9]{8})\?'
  ELSE
    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 RegularExpression = '<<template>>^;[0-9]{26}<<CardNumber>>(016[0-9]{8})\?'
END
*/

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

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 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)