CFO-CaseAssessmentTrackingSystem/src/DatabaseSeeding/Scripts/0008_TenantLocations.sql at 4e4030b9a35c6d8c115e5c124cae80e86620c462 · ministryofjustice/CFO-CaseAssessmentTrackingSystem · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140


IF NOT EXISTS (SELECT TOP(1) LocationId FROM [Configuration].[Tenantlocation])
BEGIN

    insert into [Configuration].Tenantlocation
    (
        Locationid,
        Tenantid
    )
    select Id as [LocationId],
           '1.1.2.3.1.' as [TenantId]
    from [Configuration].[Location]
    where [Name] in ( 'HMP Foston Hall', 'HMP Stocken', 'HMP Lincoln', 'HMP Onley', 'HMP North Sea Camp', 'HMP Whatton',
                      'HMP Ranby', 'HMP Nottingham', 'HMP Five Wells', 'Nottingham', 'Leicester', 'Derby',
                      'East Midlands Community','HMP Leicester'
        )
    union
    select Id as [LocationId],
           '1.1.2.3.2.' as [TenantId]
    from [Configuration].[Location]
    where [Name] in ( 'HMP Foston Hall', 'Nottingham', 'Leicester', 'Derby', 'North East Community' )
    union
    select Id as [LocationId],
           '1.1.2.3.3.' as [TenantId]
    from [Configuration].[Location]
    where [Name] in ( 'HMP Stocken', 'HMP Lincoln', 'HMP North Sea Camp', 'HMP Whatton', 'North East Community' )
    union
    select Id as [LocationId],
           '1.1.2.2.1.' as [TenantId]
    from [Configuration].[Location]
    where [Name] in ( 'HMP Drake Hall', 'HMP Featherstone', 'HMP Birmingham', 'North West Community', 'HMP Oakwood',
                      'HMP Dovegate', 'HMP Stoke Heath', 'HMP Brinsford', 'HMP Hewell', 'HMP Swinfen Hall', 'Birmingham',
                      'Wolverhampton', 'Stoke', 'West Midlands Community'
        )
    union
    select Id as [LocationId],
           '1.1.2.2.2.' as [TenantId]
    from [Configuration].[Location]
    where [Name] in ( 'HMP Drake Hall' )
    union
    select Id as [LocationId],
           '1.1.2.2.3.' as [TenantId]
    from [Configuration].[Location]
    where [Name] in ( 'HMP Drake Hall', 'Birmingham', 'Wolverhampton', 'Stoke', 'West Midlands Community' )
    union
    select Id as [LocationId],
           '1.1.2.1.1.' as [TenantId]
    from [Configuration].[Location]
    where [Name] in ( 'HMP Holme House', 'HMP Low Newton', 'HMP Northumberland', 'HMP Durham', 'HMP Kirklevington Grange',
                      'Durham', 'Sunderland', 'Middlesbrough', 'Darlington', 'Newcastle', 'North East Community'
        )
    union
    select Id as [LocationId],
           '1.1.2.1.2.' as [TenantId]
    from [Configuration].[Location]
    where [Name] in ( 'HMP Low Newton', 'Durham', 'Sunderland', 'Middlesbrough', 'Darlington', 'Newcastle',
                      'North East Community'
        )
    union
    select Id as [LocationId],
           '1.1.2.1.3.' as [TenantId]
    from [Configuration].[Location]
    where [Name] in ( 'HMP Holme House' )
    union
    select Id as [LocationId],
           '1.1.6.1.1.' as [TenantId]
    from [Configuration].[Location]
    where [Name] in ( 'HMP Portland', 'HMP Exeter', 'HMP Bristol', 'HMP Dartmoor', 'HMP Channings Wood', 'HMP Leyhill',
                      'HMP Guys Marsh', 'HMP The Verne', 'HMP Eastwood Park', 'HMP Erlestoke', 'Bristol', 'Plymouth', 'South West Community'
        )
    union
    select Id as [LocationId],
           '1.1.5.1.1.' as [TenantId]
    from [Configuration].[Location]
    where [Name] in ( 'HMP High Down', 'HMP Wandsworth', 'HMP Thameside', 'HMP Pentonville', 'HMP Brixton', 'HMP Feltham',
                      'HMP Isis', 'HMP Bronzefield', 'HMP Downview', 'HMP Wormwood Scrubs', 'Croydon', 'Lambeth', 'Lewisham', 'London Community'
        )
    union
    select Id as [LocationId],
           '1.1.5.1.2.' as [TenantId]
    from [Configuration].[Location]
    where [Name] in ( 'HMP High Down', 'HMP Wandsworth', 'HMP Thameside', 'HMP Pentonville', 'HMP Brixton', 'HMP Feltham',
                      'HMP Isis', 'HMP Bronzefield', 'HMP Downview', 'London Community'
        )
    union
    select Id as [LocationId],
           '1.1.3.1.1.' as [TenantId]
    from [Configuration].[Location]
    where [Name] in ( 'HMP Leeds', 'HMP Askham Grange', 'HMP Full Sutton', 'HMP Wealstun', 'HMP Moorland', 'HMP Humber',
                      'HMP Doncaster', 'HMP Hull', 'HMP Hatfield', 'HMP Lindholme', 'HMP New Hall', 'Leeds', 'Doncaster',
                      'Hull', 'Bradford', 'Huddersfield', 'Sheffield', 'Yorkshire and Humberside Community'
        )
    union
    select Id as [LocationId],
           '1.1.3.1.2.' as [TenantId]
    from [Configuration].[Location]
    where [Name] in ( 'Leeds', 'Huddersfield', 'Yorkshire and Humberside Community' )
    union
    select Id as [LocationId],
           '1.1.3.1.3.' as [TenantId]
    from [Configuration].[Location]
    where [Name] in ( 'HMP Leeds', 'HMP Askham Grange', 'HMP Full Sutton', 'HMP Wealstun', 'HMP New Hall',
                      'Yorkshire and Humberside Community'
        )
    union
    select Id as [LocationId],
           '1.1.1.1.1.' as [TenantId]
    from [Configuration].[Location]
    where [Name] in ( 'HMP Risley', 'HMP Lancaster Farms', 'HMP Forest Bank', 'HMP Altcourse', 'HMP Preston',
                      'HMP Kirkham', 'HMP Liverpool', 'HMP Wymott', 'HMP Buckley Hall', 'HMP Styal', 'HMP Haverigg',
                      'HMP Hindley', 'HMP Manchester', 'HMP Thorn Cross', 'Manchester', 'Liverpool', 'Blackpool',
                      'Warrington', 'Blackburn', 'North West Community'
        )
    union
    select Id as [LocationId],
           '1.1.1.1.2.' as [TenantId]
    from [Configuration].[Location]
    where [Name] in ( 'Manchester', 'Liverpool', 'Blackpool', 'Warrington', 'Blackburn', 'Preston', 'North West Community' )
    union
    select Id as [LocationId],
           '1.1.4.2.1.' as [TenantId]
    from [Configuration].[Location]
    where [Name] in ( 'HMP Rochester', 'HMP Elmley', 'HMP Lewes', 'HMP Winchester', 'HMP Bullingdon', 'HMP Swaleside',
                      'HMP Aylesbury', 'HMP Ford', 'HMP Spring Hill', 'HMP Stanford Hill', 'HMP Woodhill',
                      'HMP East Sutton Park', 'HMP Send', 'Chatham', 'Southampton', 'Portsmouth', 'South East Community'
        )
    union
    select Id as [LocationId],
           '1.1.4.1.1.' as [TenantId]
    from [Configuration].[Location]
    where [Name] in ( 'HMP The Mount', 'HMP Peterborough (M)', 'HMP Peterborough (F)', 'HMP Bedford', 'HMP Highpoint',
                      'HMP Littlehey', 'HMP Chelmsford', 'HMP Hollesley Bay', 'HMP Norwich', 'HMP Wayland', 'Peterborough',
                      'Luton', 'East Of England Community'
        )

END