· 6 years ago · Jan 17, 2020, 08:12 PM
1IF (NOT EXISTS (SELECT *
2 FROM INFORMATION_SCHEMA.TABLES
3 WHERE TABLE_SCHEMA = 'dbo'
4 AND TABLE_NAME = 'DeliveryDatesDim'))
5BEGIN
6CREATE TABLE [dbo].[DeliveryDatesDim](
7 [TpDate] [nvarchar](10) NOT NULL,
8 [Doctype] [varchar](4) NOT NULL,
9 [OrderId] [int] NOT NULL,
10 [OrderRow] [int] NOT NULL,
11 [Date] [datetime] NOT NULL,
12 [ColorId] [nvarchar](25) NOT NULL,
13 [GridId] [nvarchar](15) NOT NULL,
14 [SizeId] [nvarchar](10) NOT NULL,
15 [VariationCountry] [nvarchar](50) NOT NULL,
16 [Qtd] [decimal](18, 5) NULL,
17 CONSTRAINT [PK_DeliveryDatesDims] PRIMARY KEY CLUSTERED
18(
19 [TpDate] ASC,
20 [Doctype] ASC,
21 [OrderId] ASC,
22 [OrderRow] ASC,
23 [Date] ASC,
24 [ColorId] ASC,
25 [GridId] ASC,
26 [SizeId] ASC,
27 [VariationCountry] ASC
28)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
29) ON [PRIMARY]
30END
31go
32If (select count(*) from DeliveryDatesDim) = 0
33Begin
34 INSERT INTO [dbo].[DeliveryDatesDim]
35 ([TpDate]
36 ,[Doctype]
37 ,[OrderId]
38 ,[OrderRow]
39 ,[Date]
40 ,[ColorId]
41 ,[GridId]
42 ,[SizeId]
43 ,[VariationCountry]
44 ,[Qtd])
45 select TpDate, DocType, OrderId, OrderRow, Date, '','','','', Qtd from DeliveryDates
46End
47Go
48begin try
49 DROP TYPE [dbo].[TBL_Plan_DelivDatesDims]
50end try
51begin catch
52end catch
53GO
54CREATE TYPE [dbo].[TBL_Plan_DelivDatesDims] AS TABLE(
55 [TpDate] [nvarchar](10) NULL,
56 [DocType] [varchar](4) NULL,
57 [OrderId] [int] NULL,
58 [ColorID] [nvarchar](25) NULL,
59 [GridID] [nvarchar](15) NULL,
60 [SizeId] [nvarchar](10) NULL,
61 [VariationCountry] [nvarchar](50) NULL,
62 [Date] [datetime] NULL,
63 [Qtd] [decimal](18, 5) NULL,
64 [obs] [nvarchar](500) NULL
65)
66GO
67
68IF COL_LENGTH('workereventdate', 'GrupoDescr') IS NULL
69BEGIN
70 ALTER TABLE workereventdate ADD GrupoDescr nvarchar(200) DEFAULT('')
71END
72GO
73
74IF COL_LENGTH('RollsDetails', 'Locked') IS NULL
75BEGIN
76 ALTER TABLE RollsDetails ADD Locked bit DEFAULT(0)
77END
78GO
79IF COL_LENGTH('RollsDetails', 'Obs') IS NULL
80BEGIN
81 ALTER TABLE RollsDetails ADD Obs nvarchar(100) DEFAULT('')
82END
83GO
84
85
86ALTER TABLE workereventdate ALTER column RegistString nvarchar(600)
87GO
88ALTER TABLE workereventdate ALTER column GrupoDescr nvarchar(300)
89GO
90
91
92IF COL_LENGTH('WorkerEventDateTemp', 'GrupoDescr') IS NULL
93BEGIN
94 ALTER TABLE WorkerEventDateTemp ADD GrupoDescr nvarchar(50) DEFAULT('')
95END
96GO
97IF COL_LENGTH('WorkerEventDateTemp', 'inMenuTime') IS NULL
98BEGIN
99 ALTER TABLE WorkerEventDateTemp ADD inMenuTime decimal(18,5) not null DEFAULT(0)
100END
101GO
102IF COL_LENGTH('workereventdate', 'inMenuTime') IS NULL
103BEGIN
104 ALTER TABLE workereventdate ADD inMenuTime decimal(18,5) not null DEFAULT(0)
105END
106GO
107IF COL_LENGTH('operations', 'deleted') IS NULL
108BEGIN
109 ALTER TABLE operations ADD deleted bit not null DEFAULT(0)
110END
111GO
112
113
114IF (NOT EXISTS (SELECT *
115 FROM INFORMATION_SCHEMA.TABLES
116 WHERE TABLE_SCHEMA = 'dbo'
117 AND TABLE_NAME = 'ListOfNumbers'))
118BEGIN
119CREATE TABLE [dbo].[ListOfNumbers](
120 [Number] [int] NOT NULL,
121 CONSTRAINT [PK_ABCTESTE] PRIMARY KEY CLUSTERED
122(
123 [Number] ASC
124)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
125) ON [PRIMARY]
126END
127Go
128If (select count(*) from ListOfNumbers )= 0
129Begin
130
131;WITH Missing
132AS
133(
134 SELECT 1 AS missnum
135 UNION ALL
136 SELECT missnum + 1 FROM Missing
137 WHERE missnum < 999999
138)
139insert into ListOfNumbers
140select * from Missing
141OPTION (MAXRECURSION 0);
142End
143
144GO
145
146IF COL_LENGTH('ItemMasterDetailsVersion', 'Locked') IS NULL
147BEGIN
148 ALTER TABLE ItemMasterDetailsVersion ADD Locked bit not null constraint DF_ItemMasterVersionsNames DEFAULT 0
149END
150GO
151
152IF COL_LENGTH('ClientOrders', 'ResponsibleUserID') IS NULL
153BEGIN
154 ALTER TABLE ClientOrders ADD ResponsibleUserID nvarchar(50) not null constraint DF_ClientOrders_ResponsibleUserID DEFAULT ''
155END
156GO
157IF COL_LENGTH('ConfectionLinesOpergroupAutoamatic', 'DiffHours') IS NULL
158BEGIN
159 ALTER TABLE ConfectionLinesOpergroupAutoamatic ADD DiffHours decimal(18,5) not null constraint DF_ConfectionLinesOpergroupAutoamatic_DiffHours DEFAULT 0
160END
161GO
162IF COL_LENGTH('ConfectionLinesOpergroupAutoamatic', 'Capacity') IS NULL
163BEGIN
164 ALTER TABLE ConfectionLinesOpergroupAutoamatic ADD Capacity decimal(18,5) not null constraint DF_ConfectionLinesOpergroupAutoamatic_Capacity DEFAULT 100
165END
166GO
167
168
169
170INSERT INTO [dbo].[ItemMasterDetailsVersion]([ItemId],[Version],[Obs],[Composition],[ComposAuto],[Price],[PriceAuto],[PriceApproved])
171select im.ItemID, x.Versao, '','',0,0,0,0 from(
172SELECT distinct ic.itemid,ic.versao FROM ItemComp ic
173UNION
174SELECT distinct ic.itemid,ic.[Version] FROM ItemOperations ic
175UNION
176SELECT distinct ic.itemid,ic.[Version] FROM ItemMasterAddictionalCosts ic
177UNION
178SELECT distinct ic.itemid,ic.[Version] FROM ItemMasterAssemblyDetails ic
179UNION
180SELECT distinct ic.itemid,ic.[Version] FROM ItemMasterDetailsVersion ic
181)x
182join ItemMaster im on im.ItemID = x.ItemID
183left join ItemMasterDetailsVersion imdv on imdv.Version = x.Versao and imdv.ItemId = x.ItemID
184where imdv.ItemId is null
185
186Go
187
188IF (NOT EXISTS (SELECT *
189 FROM INFORMATION_SCHEMA.TABLES
190 WHERE TABLE_SCHEMA = 'dbo'
191 AND TABLE_NAME = 'ClientOrdersCutPartsAbast'))
192BEGIN
193 CREATE TABLE [dbo].[ClientOrdersCutPartsAbast](
194 [Doctype] [varchar](4) NOT NULL,
195 [OrderId] [int] NOT NULL,
196 [OrderRow] [int] NOT NULL,
197 [PartNum] [int] NOT NULL,
198 [SequenceAbast] [int] NOT NULL,
199 [SequenceCutPart] [int] NOT NULL,
200 [CutPartId] [varchar](30) NOT NULL,
201 [DateAbast] [datetime] NULL,
202 [Movid] [int] NULL,
203 [Type] [nvarchar](50) NULL,
204 [QtyAbast] [decimal](18, 5) NULL,
205 [CreationUser] [varchar](50) NULL,
206 [CreationDateTime] [datetime] NULL,
207 [ModifUser] [varchar](50) NULL,
208 [ModifDateTime] [datetime] NULL,
209 [Canceled] [bit] NULL,
210 [Locked] [bit] NULL,
211 [DoctypeGenerated] [varchar](4) NULL,
212 [OrderIdGenerated] [int] NULL,
213 [OrderRowGenerated] [int] NULL,
214 [DoctypeAbasted] [varchar](4) NULL,
215 [OrderidAbasted] [int] NULL,
216 [OrderrowAbasted] [int] NULL,
217 [SizeID] [nvarchar](10) NULL,
218 CONSTRAINT [PK_ClientOrdersCutPartsAbast] PRIMARY KEY CLUSTERED
219 (
220 [Doctype] ASC,
221 [OrderId] ASC,
222 [OrderRow] ASC,
223 [PartNum] ASC,
224 [SequenceAbast] ASC,
225 [SequenceCutPart] ASC,
226 [CutPartId] ASC
227 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
228 ) ON [PRIMARY]
229 END
230GO
231
232
233if (select count(*) from Menus where menuId = '0304') = 0
234begin
235 update m set menuid = replace(m.menuId,'0303','0304') from Menus m where menuId like '0303%'
236 update menus set parentId = '0304' where parentId = '0303'
237 insert into Menus(menuId, descricao, nivel, parentId, imagemId, menuInicial, tipoAcesso, Comando, available)
238 select '0303', 'Guias de transporte', '2', '03', '34', '2', '2', 'TRANPORTGUIDE', 1
239end
240
241
242GO
243
244delete from Menus where Comando = 'CNFLINES'
245delete from Favourites where Comando = 'CNFLINES'
246delete from Access where comando = 'CNFLINES'
247
248GO
249
250if (select count(*) from Menus where menuId = '0206') = 0
251begin
252 update m set menuid = replace(m.menuId,'0205','0206') from Menus m where menuId like '0205%'
253 update menus set parentId = '0206' where parentId = '0205'
254
255 update m set menuid = replace(m.menuId,'0204','0205') from Menus m where menuId like '0204%'
256 update menus set parentId = '0205' where parentId = '0204'
257
258 update m set menuid = replace(m.menuId,'0203','0204') from Menus m where menuId like '0203%'
259 update menus set parentId = '0204' where parentId = '0204'
260
261 insert into Menus(menuId, descricao, nivel, parentId, imagemId, menuInicial, tipoAcesso, Comando, available)
262 select '0203', 'Alteração de consumos', '2', '02', '41', '1', '2', 'CONSUMPTIONS', 1
263End
264Go
265
266Delete from Menus where menuid = '040103'
267insert into Menus(menuId, descricao, nivel, parentId, imagemId, menuInicial, tipoAcesso, Comando, available)
268select '040103', 'Planeamento Corte', '3', '0401', '69', '3', '2', 'PLANINGCUT', 1
269go
270
271
272IF (NOT EXISTS (SELECT *
273 FROM INFORMATION_SCHEMA.TABLES
274 WHERE TABLE_SCHEMA = 'dbo'
275 AND TABLE_NAME = 'OperationsGroupsServices'))
276BEGIN
277
278CREATE TABLE [dbo].[OperationsGroupsServices](
279 [OperGrupo] [nvarchar](50) NOT NULL,
280 [ServiceType] [nvarchar](50) NOT NULL,
281 [ItemID] [varchar](100) NOT NULL,
282 [Description] [nvarchar](50) NULL,
283 [OrderIndex] [int] NULL,
284 [Active] [bit] NULL,
285 CONSTRAINT [PK_OperationsGroupsServices] PRIMARY KEY CLUSTERED
286(
287 [OperGrupo] ASC,
288 [ServiceType] ASC,
289 [ItemID] ASC
290)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
291) ON [PRIMARY]
292end
293GO
294
295IF NOT EXISTS (SELECT * FROM dbo.sysobjects where name = 'OperationCodeStructure')
296BEGIN
297CREATE TABLE [dbo].[OperationCodeStructure](
298 [OperCode] [nchar](15) NOT NULL,
299 [No] [nvarchar](6) NOT NULL,
300 [ValorID] [nvarchar](4000) NULL,
301 [IndexID] [int] NULL,
302 [VisibleID] [bit] NULL,
303 [TAG] [nvarchar](100) NULL,
304 [ValDsc] [nvarchar](4000) NULL,
305 [IndexDsc] [int] NULL,
306 [VisibleDesc] [bit] NULL,
307 [Desc2] [nvarchar](4000) NULL,
308 [Desc3] [nvarchar](4000) NULL,
309 [Desc4] [nvarchar](4000) NULL,
310 [desc1] [nvarchar](4000) NULL,
311 [col1] [nvarchar](4000) NULL,
312 CONSTRAINT [PK_OperationCodeStructure] PRIMARY KEY CLUSTERED
313(
314 [OperCode] ASC,
315 [No] ASC
316)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
317) ON [PRIMARY]
318END
319GO
320
321
322IF NOT EXISTS (SELECT * FROM dbo.sysobjects where name = 'ItemOperationsHistory')
323BEGIN
324
325CREATE TABLE [dbo].[ItemOperationsHistory](
326 [ID] [int] IDENTITY(1,1) NOT NULL,
327 [ItemID] [nvarchar](100) NOT NULL,
328 [Version] [int] NOT NULL,
329 [Sequence] [int] NOT NULL,
330 [Operation] [nvarchar](15) NOT NULL,
331 [UnitTime] [decimal](18, 5) NOT NULL,
332 [CreationUser] [nvarchar](50) NULL,
333 [CreationDateTime] [datetime] NULL,
334 [ModifUser] [nvarchar](50) NULL,
335 [ModifDateTime] [datetime] NULL,
336 CONSTRAINT [PK_ItemOperationsHistory] PRIMARY KEY CLUSTERED
337(
338 [ID] ASC
339)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
340) ON [PRIMARY]
341
342ALTER TABLE [dbo].[ItemOperationsHistory] ADD CONSTRAINT [DF_ItemOperationsHistory_UnitTime] DEFAULT ((0)) FOR [UnitTime]
343END
344GO