· 10 years ago · Sep 06, 2016, 09:18 AM
1
2
3
4
5---------------------------------------------------------------------------------------------------------------------------------------------------
6---- MARIA SILVA: 2016.08.25 START
7---------------------------------------------------------------------------------------------------------------------------------------------------
8
9
10/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
11BEGIN TRANSACTION
12SET QUOTED_IDENTIFIER ON
13SET ARITHABORT ON
14SET NUMERIC_ROUNDABORT OFF
15SET CONCAT_NULL_YIELDS_NULL ON
16SET ANSI_NULLS ON
17SET ANSI_PADDING ON
18SET ANSI_WARNINGS ON
19COMMIT
20BEGIN TRANSACTION
21GO
22ALTER TABLE dbo.PublicContests ADD
23 CreatedBackOffice int NULL
24GO
25ALTER TABLE dbo.PublicContests ADD CONSTRAINT
26 DF_PublicContests_CreatedBackOffice DEFAULT 0 FOR CreatedBackOffice
27GO
28ALTER TABLE dbo.PublicContests SET (LOCK_ESCALATION = TABLE)
29GO
30COMMIT
31select Has_Perms_By_Name(N'dbo.PublicContests', 'Object', 'ALTER') as ALT_Per, Has_Perms_By_Name(N'dbo.PublicContests', 'Object', 'VIEW DEFINITION') as View_def_Per, Has_Perms_By_Name(N'dbo.PublicContests', 'Object', 'CONTROL') as Contr_Per
32
33
34UPDATE PublicContests
35 SET CreatedBackOffice = 0
36---------------------------------------------------------------------------------------------------------------------------------------------------
37---- MARIA SILVA: 2016.08.25 END
38---------------------------------------------------------------------------------------------------------------------------------------------------
39
40
41---------------------------------------------------------------------------------------------------------------------------------------------------
42---- MARIA SILVA: 2016.09.01 START
43---------------------------------------------------------------------------------------------------------------------------------------------------
44
45-- to create a file type public to be used on Aiccopn's Platform
46
47DECLARE @AdminRoleId uniqueidentifier
48DECLARE @user_Admin_id uniqueidentifier
49
50SET @AdminRoleId = (SELECT TOP (1)
51 aspRol.RoleId
52FROM aspnet_Roles aspRol
53WHERE aspRol.RoleName LIKE '%Administrator%')
54
55IF @AdminRoleId IS NOT NULL
56BEGIN
57
58 SET @user_Admin_id = (SELECT TOP (1)
59 aspUR.UserId
60 FROM aspnet_UsersInRoles aspUR
61 JOIN UserDetails userDet
62 ON aspUR.UserId = userDet.UserId
63 WHERE aspUR.RoleId = @AdminRoleId
64 AND aspUR.RecordState = 1
65 AND userDet.RecordState = 1)
66END
67
68
69-- insert the new filetype if it doesn't exists
70IF NOT EXISTS (SELECT TOP (1)
71 *
72 FROM FileTypes
73 WHERE FileType_descrition LIKE '%Documento Público Diretório%')
74BEGIN
75
76 INSERT INTO FileTypes (CompanyID, FileType_descrition, FileType, RecordState, DateCreated, UserCreated,
77 DateModified, UserModified, imageURL, URLHelpFile, ProductFileType, default_country, ValidatorCompanyID, DefaultPlatform)
78 VALUES (NULL, 'Documento Público Diretório', NULL, NULL, GETDATE(), @user_Admin_id, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2)
79END
80
81
82-- gets the filetypeid of the new FileType
83DECLARE @newFileTypeID int;
84SET @newFileTypeID = (SELECT TOP (1)
85 FileTypeID
86FROM FileTypes
87WHERE FileType_descrition LIKE '%Documento Público Diretório%'
88ORDER BY 1 DESC)
89
90
91-- Gets the id of the aiccopn group
92DECLARE @aiccopn_Group_ID int;
93SET @aiccopn_Group_ID = (SELECT
94 AiccopnGroupID
95FROM Administration)
96
97-- gets the aiccopn's generic BusinessCategory
98DECLARE @catId int;
99
100SET @catId = (SELECT
101 bc.BusinessCategoryID
102FROM BusinessCategories bc
103WHERE LOWER(REPLACE(bc.Name, ' ', '')) LIKE '%'
104+ LOWER(REPLACE('Ficheiros genéricos da Aiccopn', ' ', '')) + '%'
105AND bc.BusinessGroupID = @aiccopn_Group_ID)
106
107
108-- creates the relation newfiletype ---> BusinessCategory
109IF NOT EXISTS (SELECT
110 *
111 FROM Categories_FileTypes
112 WHERE FileTypeID = @newFileTypeID)
113BEGIN
114
115 INSERT Categories_FileTypes (BusinessCategoryID, FileTypeID, RecordState, DateCreated, UserCreated,
116 DateModified, UserModified, default_country, DefaultPlatform)
117 VALUES (@catId, @newFileTypeID, 1, GETDATE(), @user_Admin_id, NULL, NULL, NULL, 2);
118
119END
120
121---------------------------------------------------------------------------------------------------------------------------------------------------
122---- MARIA SILVA: 2016.09.01 START
123---------------------------------------------------------------------------------------------------------------------------------------------------
124
125---------------------------------------------------------------------------------------------------------------------------------------------------
126---- MARIA SILVA: 2016.09.05 START
127---------------------------------------------------------------------------------------------------------------------------------------------------
128
129-- para invalidar pedidos de acreditação que têm ficheiros expirados
130-- e que não foram invalidados durante a execução dos alarmes
131
132DECLARE @catSeloAiccpn int,
133 @ReqID int,
134 @CompanyIDU int;
135
136SET @catSeloAiccpn = (SELECT -- busca o id da categoria do selo Aiccopn
137 AiccopnActivityCategoryID
138FROM Administration)
139
140
141
142DECLARE Requeststovalidate CURSOR FOR -- busca todos os pedidos de acreditação ativos
143SELECT
144 Request_ID,
145 CompanyID_user
146FROM Request_Qualification
147WHERE BusinessCategoryID = @catSeloAiccpn
148AND RecordState = 1
149AND QualificationTypeID = 3
150
151
152OPEN Requeststovalidate
153FETCH NEXT FROM Requeststovalidate INTO @ReqID, @CompanyIDU
154
155
156WHILE @@FETCH_STATUS = 0
157BEGIN
158
159 IF (EXISTS ( -- se existe um pedido que tem um ficheiro expirado e cujo tipo é um
160 -- dos tipos de ficheiros da acreditação, invalida o pedido
161 SELECT
162 f.FileID
163 FROM Files f
164 JOIN FileTypes ft
165 ON f.FileTypeID = ft.FileTypeID
166 JOIN Categories_FileTypes catft
167 ON catft.FileTypeID = ft.FileTypeID
168
169 WHERE
170 f.CompanyID = @CompanyIDU -- ficheiro da empresa que fez o pedido
171
172 AND f.DateValidity < GETDATE() -- ficheiro expirado
173 AND f.RecordState = 3
174
175 AND f.DefaultPlatform = 2
176 AND ft.DefaultPlatform = 2
177 AND catft.BusinessCategoryID = @catSeloAiccpn
178 ))
179 BEGIN
180
181 UPDATE Request_Qualification
182 SET RecordState = 2,
183 DateModified = GETDATE()
184 WHERE Request_ID = @ReqID
185
186 END
187 FETCH NEXT FROM Requeststovalidate INTO @ReqID, @CompanyIDU
188END
189
190CLOSE Requeststovalidate
191DEALLOCATE Requeststovalidate
192
193---------------------------------------------------------------------------------------------------------------------------------------------------
194---- MARIA SILVA: 2016.09.05 END
195---------------------------------------------------------------------------------------------------------------------------------------------------