insert.sql

-- CREATE TABLE Language (languageID int IDENTITY NOT NULL, code char(3) NOT NULL UNIQUE, name varchar(255) NOT NULL UNIQUE, PRIMARY KEY (languageID));
SET IDENTITY_INSERT Language ON;
INSERT INTO Language (languageID, code, name)
VALUES
    (1, 'deu', 'Deutsch'),
    (2, 'eng', 'English'),
    (3, 'nor', 'Norsk');
SET IDENTITY_INSERT Language OFF;

-- CREATE TABLE Countries (id int IDENTITY NOT NULL, code char(3) NOT NULL UNIQUE, PRIMARY KEY (id));
SET IDENTITY_INSERT Countries ON;
INSERT INTO Countries (id, code)
VALUES
    (616, 'POL'),
    (250, 'FRA'),
    (100, 'BUL'),
    (826, 'GBR'),
    (372, 'IRL'),
    (620, 'PRT'),
    (040, 'AUT'),
    (528, 'NLD'),
    (276, 'DEU'),
    (380, 'ITA'),
    (724, 'ESP'),
    (300, 'GRC'),
    (056, 'BEL'),
    (428, 'LVA'),
    (440, 'LTU'),
    (233, 'EST'),
    (246, 'FIN'),
    (703, 'SVK'),
    (196, 'CYP'),
    (705, 'SVN'),
    (578, 'NOR'),
    (752, 'SWE'),
    (208, 'DNK'),
    (442, 'LUX'),
    (203, 'CZE'),
    (470, 'MLT');
SET IDENTITY_INSERT Countries OFF;

-- CREATE TABLE LCountries (countryID int NOT NULL, languageID int NOT NULL, name varchar(255) NOT NULL, PRIMARY KEY (countryID, languageID));
INSERT INTO LCountries (countryID, languageID, name)
VALUES
    (616,1,'Polen'),
    (616,2,'Poland'),
    (616,3,'Polen'),
    (250,1,'Frankreich'),
    (250,2,'France'),
    (250,3,'Frankrike'),
    (100,1,'Bulgarien'),
    (100,2,'Bulgaria'),
    (100,3,'Bulgaria'),
    (826,1,'Vereinigtes Königreich'),
    (826,2,'United Kingdom'),
    (826,3,'Storbritannia'),
    (372,1,'Irland'),
    (372,2,'Ireland'),
    (372,3,'Irland'),
    (620,1,'Portugal'),
    (620,2,'Portugal'),
    (620,3,'Portugal'),
    (040,1,'Österreich'),
    (040,2,'Austria'),
    (040,3,'Østerrike'),
    (528,1,'Niederlande'),
    (528,2,'Netherlands'),
    (528,3,'Nederland'),
    (276,1,'Deutschland'),
    (276,2,'Germany'),
    (276,3,'Tyskland'),
    (380,1,'Italien'),
    (380,2,'Italy'),
    (380,3,'Italia'),
    (724,1,'Spanien'),
    (724,2,'Spain'),
    (724,3,'Spania'),
    (300,1,'Griechenland'),
    (300,2,'Greece'),
    (300,3,'Hellas'),
    (056,1,'Belgien'),
    (056,2,'Belgium'),
    (056,3,'Belgia'),
    (428,1,'Lettland'),
    (428,2,'Latvia'),
    (428,3,'Latvia'),
    (440,1,'Litauen'),
    (440,2,'Lithuania'),
    (440,3,'Litauen'),
    (233,1,'Estland'),
    (233,2,'Estonia'),
    (233,3,'Estland'),
    (246,1,'Finnland'),
    (246,2,'Finland'),
    (246,3,'Finland'),
    (703,1,'Slowakei'),
    (703,2,'Slovakia'),
    (703,3,'Slovakia'),
    (196,1,'Zypern'),
    (196,2,'Cyprus'),
    (196,3,'Kypros'),
    (705,1,'Slowenien'),
    (705,2,'Slovenia'),
    (705,3,'Slovenia'),
    (578,1,'Norwegen'),
    (578,2,'Norway'),
    (578,3,'Norge'),
    (752,1,'Schweden'),
    (752,2,'Sweden'),
    (752,3,'Sverige'),
    (208,1,'Dänemark'),
    (208,2,'Denmark'),
    (208,3,'Danmark'),
    (442,1,'Luxemburg'),
    (442,2,'Luxembourg'),
    (442,3,'Luxembourg'),
    (203,1,'Tschechien'),
    (203,2,'Czech Republic'),
    (203,3,'Tsjekkia'),
    (470,1,'Malta'),
    (470,2,'Malta'),
    (470,3,'Malta');

-- CREATE TABLE Cities (id int IDENTITY NOT NULL, code char(6) NOT NULL UNIQUE, PRIMARY KEY (id));
SET IDENTITY_INSERT Cities ON;
INSERT INTO Cities (id, code)
VALUES
    (1, 'PL-WAW'),
    (2, 'FR-CHA'),
    (3, 'BG-SOF'),
    (4, 'GB-LOU'),
    (5, 'GB-GAT'),
    (6, 'PT-CAR'),
    (7, 'AT-VIE'),
    (8, 'NL-HAA'),
    (9, 'DE-BER'),
    (10, 'IT-ROM'),
    (11, 'IE-DUB'),
    (12, 'FR-CHE'),
    (13, 'ES-MAD'),
    (14, 'DE-LEI'),
    (15, 'DE-MUN'),
    (16, 'GR-ATH'),
    (17, 'BE-BRU'),
    (18, 'LV-RIG'),
    (19, 'LT-VIL'),
    (20, 'EE-TAL'),
    (21, 'FI-HEI'),
    (22, 'SK-BRA'),
    (23, 'CY-NIC'),
    (24, 'SI-LJU'),
    (25, 'NO-OSL'),
    (26, 'SE-STO'),
    (27, 'DK-COP'),
    (28, 'LU-LUX'),
    (29, 'CZ-PRA'),
    (30, 'MT-VAL'),
    (31, 'SE-TUM'),
    (32, 'FI-VAN'),
    (33, 'NL-HAR');
SET IDENTITY_INSERT Cities OFF;

-- CREATE TABLE LCities (cityID int NOT NULL, languageID int NOT NULL, name varchar(255) NOT NULL, PRIMARY KEY (cityID, languageID));
INSERT INTO LCities (cityID, languageID, name)
VALUES
    (1,1, 'Warschau'),
    (1,2, 'Warsaw'),
    (1,3, 'Warszawa'),
    (2,1, 'Chantepie'),
    (2,2, 'Chantepie'),
    (2,3, 'Chantepie'),
    (3,1, 'Sofia'),
    (3,2, 'Sofia'),
    (3,3, 'Sofia'),
    (4,1, 'Loughton'),
    (4,2, 'Loughton'),
    (4,3, 'Loughton'),
    (5,1, 'Gateshead'),
    (5,2, 'Gateshead'),
    (5,3, 'Gateshead'),
    (6,1, 'Carregado'),
    (6,2, 'Carregado'),
    (6,3, 'Carregado'),
    (7,1, 'Wien'),
    (7,2, 'Vienna'),
    (7,3, 'Wien'),
    (8,1, 'Den Haag'),
    (8,2, 'The Hague'),
    (8,3, 'Den Haag'),
    (9,1, 'Berlin'),
    (9,2, 'Berlin'),
    (9,3, 'Berlin'),
    (10,1, 'Rom'),
    (10,2, 'Rome'),
    (10,3, 'Roma'),
    (11,1, 'Dublin'),
    (11,2, 'Dublin'),
    (11,3, 'Dublin'),
    (12,1, 'Chamalières'),
    (12,2, 'Chamalières'),
    (12,3, 'Chamalières'),
    (13,1, 'Madrid'),
    (13,2, 'Madrid'),
    (13,3, 'Madrid'),
    (14,1, 'Leipzig'),
    (14,2, 'Leipzig'),
    (14,3, 'Leipzig'),
    (15,1, 'München'),
    (15,2, 'Munich'),
    (15,3, 'München'),
    (16,1, 'Athen'),
    (16,2, 'Athens'),
    (16,3, 'Athen'),
    (17,1, 'Brüssel'),
    (17,2, 'Brussels'),
    (17,3, 'Brussel'),
    (18,1, 'Riga'),
    (18,2, 'Riga'),
    (18,3, 'Riga'),
    (19,1, 'Vilnius'),
    (19,2, 'Vilnius'),
    (19,3, 'Vilnius'),
    (20,1, 'Tallinn'),
    (20,2, 'Tallinn'),
    (20,3, 'Tallinn'),
    (21,1, 'Helsinki'),
    (21,2, 'Helsinki'),
    (21,3, 'Helsingfors'),
    (22,1, 'Bratislava'),
    (22,2, 'Bratislava'),
    (22,3, 'Bratislava'),
    (23,1, 'Nikosia'),
    (23,2, 'Nicosia'),
    (23,3, 'Nicosia'),
    (24,1, 'Ljubljana'),
    (24,2, 'Ljubljana'),
    (24,3, 'Ljubljana'),
    (25,1, 'Oslo'),
    (25,2, 'Oslo'),
    (25,3, 'Oslo'),
    (26,1, 'Stockholm'),
    (26,2, 'Stockholm'),
    (26,3, 'Stockholm'),
    (27,1, 'Kopenhagen'),
    (27,2, 'Copenhagen'),
    (27,3, 'Copenhagen'),
    (28,1, 'Luxemburg'),
    (28,2, 'Luxembourg'),
    (28,3, 'Luxemburg'),
    (29,1, 'Prag'),
    (29,2, 'Prague'),
    (29,3, 'Praha'),
    (30,1, 'Valletta'),
    (30,2, 'Valletta'),
    (30,3, 'Valletta'),
    (31,1, 'Tumba'),
    (31,2, 'Tumba'),
    (31,3, 'Tumba'),
    (32,1, 'Vantaa'),
    (32,2, 'Vantaa'),
    (32,3, 'Vantaa'),
    (33,1, 'Haarlem'),
    (33,2, 'Haarlem'),
    (33,3, 'Haarlem');



-- CREATE TABLE EuropeSeries (id int IDENTITY NOT NULL, code char(1) NOT NULL, printery varchar(255) NOT NULL, countryID int NOT NULL, cityID int NOT NULL, circulation int NOT NULL, PRIMARY KEY (id));

INSERT INTO EuropeSeries (code, printery, countryID, cityID, circulation)
    VALUES
        ('D', 'Polska Wytwórnia Papierów Wartościowych', 616, 1, 0),
        ('E', 'Oberthur Fiduciaire', 250, 2, 1),
        ('F', 'Oberthur Fiduciaire AD Bulgaria', 100, 3, 1),
        ('H', 'De La Rue Currency', 826, 4, 0),
        ('J', 'De La Rue Currency', 826, 5, 0),
        ('M', 'Valora SA', 620, 6, 1),
        ('N', 'Österreichische Banknoten- und Sicherheitsdruck GmbH', 40, 7, 1),
        ('P', 'Joh. Enschede Security Printing BV', 528, 8, 1),
        ('R', 'Bundesdruckerei GmbH', 276, 9, 1),
        ('S', 'Banca d’Italia', 380, 10, 1),
        ('T', 'Central Bank of Ireland', 372, 11, 1),
        ('U', 'Banque de France', 250, 2, 1),
        ('V', 'IMBISA', 724, 13, 1),
        ('W', 'Giesecke+Devrient GmbH', 276, 14, 1),
        ('X', 'Giesecke+Devrient GmbH', 276, 15, 1),
        ('Y', 'Bank of Greece', 300, 16, 1),
        ('Z', 'Nationale Bank van België', 56, 17, 1);

-- CREATE TABLE OldSeries (id int IDENTITY NOT NULL, code char(1) NOT NULL, countryID int NOT NULL, circulation bit NOT NULL, PRIMARY KEY (id));

INSERT INTO OldSeries (code, countryID, circulation)
    VALUES
        ('B', 440, 0),
        ('C', 428, 0),
        ('D', 233, 1),
        ('E', 703, 1),
        ('F', 470, 1),
        ('G', 196, 1),
        ('H', 705, 1),
        ('J', 826, 0),
        ('K', 752, 0),
        ('L', 250, 1),
        ('M', 620, 1),
        ('N', 40, 1),
        ('P', 528, 1),
        ('R', 442, 0),
        ('S', 380, 1),
        ('T', 372, 1),
        ('U', 250, 1),
        ('V', 724, 1),
        ('W', 208, 0),
        ('X', 276, 1),
        ('Y', 300, 1),
        ('Z', 56, 1);


-- CREATE TABLE Printery (id int IDENTITY NOT NULL, code char(1) NOT NULL, name varchar(255) NOT NULL, countryID int NOT NULL, cityID int NOT NULL, circulation int NOT NULL, PRIMARY KEY (id));
INSERT INTO Printery (code, name, countryID, cityID, circulation)
    VALUES
        ('A', 'Bank of England Printing Works', 826, 4, 0),
        ('C', 'AB Tumba Bruk', 752, 31, 0),
        ('D', 'Setec Oy', 246, 32, 1),
        ('E', 'Oberthur Technologies', 250, 2, 1),
        ('F', 'Österreichische Banknoten- und Sicherheitsdruck GmbH', 40, 7, 1),
        ('G', 'Koninklijke Joh. Enschedé', 528, 33, 1),
        ('H', 'De La Rue plc.', 826, 5, 1),
        ('J', 'Istituto Poligrafico e Zecca dello Stato', 380, 10, 1),
        ('K', 'Central Bank of Ireland', 372, 11, 1),
        ('L', 'Banque de France', 250, 12, 1),
        ('M', 'Fábrica Nacional de Moneda y Timbre', 724, 13, 1),
        ('N', 'Bank von Griechenland', 300, 16, 1),
        ('P', 'Giesecke+Devrient GmbH', 276, 15, 1),
        ('R', 'Thunderstruck', 276, 9, 1),
        ('S', 'Danmarks Nationalbank', 208, 27, 0),
        ('T', 'Nationale Bank van België', 56, 17, 1),
        ('U', 'Valora SA', 620, 12, 1);


-- CREATE TABLE Texts (textID int IDENTITY NOT NULL, [desc] varchar(255) NOT NULL UNIQUE, PRIMARY KEY (textID));
SET IDENTITY_INSERT Texts ON;
INSERT INTO Texts (textID, [desc])
    VALUES
        (0, 'title'),
        (1, 'info'),

        (10, 'oldTitle'),
        (11, 'newTitle'),
        (12, 'oldDescription'),
        (13, 'newDescription'),
        (14, 'changeLanguage'),

        (20, 'serialNo'),
        (21, 'printeryCode'),
        (22, 'checkButton'),
        (23, 'oldCountryResult'),
        (24, 'oldPrinteryResult'),
        (25, 'newResult'),
        (26, 'validSerial'),
        (27, 'invalidSerial'),

        (30, 'error'),
        (31, 'serialFormatError'),
        (32, 'printeryFormatError'),
        (33, 'serialInvalidError'),
        (34, 'printeryInvalidError'),
        (35, 'codeMatchError'),
        (36, 'noDataError'),

        (40, 'warning'),
        (41, 'circulationWarning');
SET IDENTITY_INSERT Texts OFF;

-- CREATE TABLE LTexts (textID int NOT NULL, languageID int NOT NULL, text varchar(255) NOT NULL UNIQUE, PRIMARY KEY (textID, languageID));
INSERT INTO LTexts (textID, languageID, text)
    VALUES
        (0, 1, 'Euro - Prüfziffernkontrolle'),
        (0, 2, 'Euro - Serial Number Lookup'),
        (0, 3, 'Euro - Serienummer Søk'),

        (1, 1, 'Ergebnisse werden hier gezeigt'),
        (1, 2, 'Results will be displayed here'),
        (1, 3, 'Resultater vises her'),

        (10, 1, 'Alte Geldscheine prüfen'),
        (10, 2, 'Check old banknotes'),
        (10, 3, 'Sjekk gamle sedler'),
        (11, 1, 'Neue Geldscheine prüfen'),
        (11, 2, 'Check new banknotes'),
        (11, 3, 'Sjekk nye sedler'),
        (12, 1, 'z.B. Z60162200226 / T003G1'),
        (12, 2, 'e.g. Z60162200226 / T003G1'),
        (12, 3, 'f.eks. Z60162200226 / T003G1'),
        (13, 1, 'z.B. ND6617057128 / N022F6'),
        (13, 2, 'e.g. ND6617057128 / N022F6'),
        (13, 3, 'f.eks. ND6617057128 / N022F6'),
        (14, 1, 'Sprache auswählen'),
        (14, 2, 'Select Language'),
        (14, 3, 'Velg språk'),

        (20, 1, 'Seriennummer'),
        (20, 2, 'Serial number'),
        (20, 3, 'Serienummer'),
        (21, 1, 'Plattencode'),
        (21, 2, 'Print office code'),
        (21, 3, 'Trykkerikode'),
        (22, 1, 'Geldschein prüfen'),
        (22, 2, 'Check banknote'),
        (22, 3, 'Sjekk seddel'),
        (23, 1, 'Austellungsland'),
        (23, 2, 'Issuing country'),
        (23, 3, 'Utgivelsesland'),
        (24, 1, 'Druckerei'),
        (24, 2, 'Print office'),
        (24, 3, 'Trykkeri'),
        (25, 1, 'Druckerei & Austellungsland'),
        (25, 2, 'Print office & Issuing country'),
        (25, 3, 'Trykkeri & Utgivelsesland'),
        (26, 1, 'Geldschein ist GÜLTIG'),
        (26, 2, 'Banknote is VALID'),
        (26, 3, 'Seddel er GYLDIG'),
        (27, 1, 'Geldschein ist UNGÜLTIG'),
        (27, 2, 'Banknote is INVALID'),
        (27, 3, 'Seddel er UGYLDIG'),

        (30, 1, 'Fehler'),
        (30, 2, 'Error'),
        (30, 3, 'Feil'),
        (31, 1, 'Seriennummer hat falsches Format'),
        (31, 2, 'Serial number has wrong format'),
        (31, 3, 'Serienummer har feil format'),
        (32, 1, 'Plattencode hat falsches Format'),
        (32, 2, 'Print office code has wrong format'),
        (32, 3, 'Trykkerikode har feil format'),
        (33, 1, 'Seriennummer ist Fehlerhaft'),
        (33, 2, 'Serial number is faulty'),
        (33, 3, 'Serienummer er feil'),
        (34, 1, 'Plattencode ist Fehlerhaft'),
        (34, 2, 'Print office code is faulty'),
        (34, 3, 'Trykkerikode er feil'),
        (35, 1, 'Seriennummer und Plattencode passen nicht zusammen'),
        (35, 2, 'Serial number and print office code do not match'),
        (35, 3, 'Serienummer og trykkerikode passer ikke sammen'),
        (36, 1, 'Seriennummer / Druckereikennung nicht gefunden'),
        (36, 2, 'Serial number / print office code not found'),
        (36, 3, 'Serienummer / trykkerikode ikke funnet'),

        (40, 1, 'Warnung'),
        (40, 2, 'Warning'),
        (40, 3, 'Advarsel'),
        (41, 1, 'Derzeit sind keine Geldscheine durch dieses Land / diese Druckerei im Umlauf'),
        (41, 2, 'Currently there are no banknotes in circulation by this country / print office'),
        (41, 3, 'Det er for tiden ingen sedler i omløp av dette landet / trykkeriet');