· 6 years ago · Nov 24, 2019, 05:30 PM
1use [QuarryTEST]
2GO
3
4--кеды
5
6select * from [InventoryStorage] GO
7
8CREATE TABLE [InventoryPrice](
9[InventoryID] int identity(1,1) PRIMARY KEY,
10[Name] varchar(20) UNIQUE NOT NULL,
11[Price] int NOT NULL
12);
13ALTER TABLE [Inventory]
14ADD [InventoryID] int NOT NULL REFERENCES [InventoryPrice] ([InventoryID])
15
16INSERT INTO [InventoryPrice] ([Name],[Price]) VALUES ('Jackhammer',57000)
17SELECT * FROM [InventoryPrice]
18INSERT INTO [Inventory] ([StorageID],[DateOfDelivery],[InventoryID]) VALUES (7,GETDATE(),2)
19
20SELECT * FROM [Inventory]
21
22CREATE PROCEDURE [AddInventory] --5.4
23(@name as varchar(20), @type as varchar(20), @amount as int)
24AS
25BEGIN
26 IF NOT EXISTS (SELECT * FROM [InventoryPrice] WHERE [Name] = @name)
27 BEGIN
28 RAISERROR ('Введенное название инвентаря отсутствует в таблице InventoryPrice', 1,1)
29 RETURN
30 END
31 ELSE
32 BEGIN
33 DECLARE @topID int
34 DECLARE @topst TABLE(
35 [StorageID] int,
36 [Capacity] int,
37 [Used] int
38 )
39 IF EXISTS (
40 SELECT SUM([Capacity]) AS [Cap]
41 FROM [InventoryStorage] GROUP BY [Type]
42 HAVING [Type] = @type --HAVING ([Type] = @type OR [Type] = 'General') --0,3
43 AND SUM([Capacity]) >= (SELECT @amount + COUNT(*)
44 FROM [Inventory] WHERE [StorageID] IN (SELECT [StorageID] FROM [InventoryStorage] WHERE [Type] = @type))--WHERE [Type] = @type OR [Type] = 'General')) --0.2
45 )
46 BEGIN
47 INSERT INTO @topst([StorageID],[Capacity],[Used]) --0.5
48 SELECT [InSt].[StorageID], [InSt].[Capacity], COUNT(*) AS [Used]
49 FROM [InventoryStorage] AS [InSt]
50 LEFT OUTER JOIN [InteractionWithInventoryStorage] AS [Int]
51 ON [Int].[StorageID] = [InSt].[StorageID]
52 GROUP BY [InSt].[StorageID], [InSt].[Capacity], [InSt].[Type]
53 HAVING [InSt].[Type] = @type --HAVING [InSt].[Type] = @type OR [InSt].[Type] = 'General'--0.3
54 AND [Capacity] > (SELECT COUNT(*) FROM [Inventory] WHERE [StorageID] = [InSt].[StorageID]) --0.2
55 ORDER BY COUNT(*) DESC
56 SET @topID = (SELECT TOP 1 [StorageID] FROM @topst)
57 WHILE (@amount > 0)
58 BEGIN
59 IF NOT EXISTS (
60 SELECT TOP 1 [StorageID] FROM @topst
61 WHERE [Capacity] < (SELECT 1 + COUNT(*) FROM [Inventory] WHERE [StorageID] = @topID)
62 )
63 BEGIN
64 DELETE @topst WHERE [StorageID] = @topID --0.5
65 SET @topID = (SELECT TOP 1 [StorageID] FROM @topst)
66 END
67 INSERT INTO [Inventory] ([InventoryID],[StorageID],[DateOfDelivery]) --0.5
68 VALUES ((SELECT [InventoryID] FROM [InventoryPrice] WHERE [Name] = @name),@topID, GETDATE())
69 SET @amount = @amount - 1
70 END
71 END
72 ELSE --Распределение инвентаря в общие склады
73 BEGIN
74 IF EXISTS (
75 SELECT SUM([Capacity]) AS [Cap]
76 FROM [InventoryStorage] GROUP BY [Type]
77 HAVING [Type] = 'General' --0,3
78 AND SUM([Capacity]) >= (SELECT @amount + COUNT(*)
79 FROM [Inventory] WHERE [StorageID] IN (SELECT [StorageID] FROM [InventoryStorage] WHERE [Type] = 'General')) --0.2
80 )
81 BEGIN
82 INSERT INTO @topst([StorageID],[Capacity],[Used]) --0.5
83 SELECT [St].[StorageID], [St].[Capacity], COUNT(*) AS [Used]
84 FROM [InventoryStorage] AS [St]
85 LEFT JOIN [InteractionWithInventoryStorage] AS [Int]
86 ON [Int].[StorageID] = [St].[StorageID]
87 GROUP BY [St].[StorageID], [St].[Capacity], [St].[Type]
88 HAVING [St].[Type] = 'General' --0.3
89 AND [Capacity] > (SELECT COUNT(*) FROM [Inventory] WHERE [StorageID] = [St].[StorageID]) --0.2
90 ORDER BY COUNT(*) DESC
91 SET @topID = (SELECT TOP 1 [StorageID] FROM @topst)
92 WHILE (@amount > 0)
93 BEGIN
94 IF NOT EXISTS (
95 SELECT TOP 1 [StorageID] FROM @topst
96 WHERE [Capacity] < (SELECT 1 + COUNT(*) FROM [Inventory] WHERE [StorageID] = @topID)
97 )
98 BEGIN
99 DELETE @topst WHERE [StorageID] = @topID --0.5
100 SET @topID = (SELECT TOP 1 [StorageID] FROM @topst)
101 END
102 INSERT INTO [Inventory] ([InventoryID],[StorageID],[DateOfDelivery]) --0.5
103 VALUES ((SELECT [InventoryID] FROM [InventoryPrice] WHERE [Name] = @name),@topID, GETDATE())
104 SET @amount = @amount - 1
105 END
106 END
107 ELSE
108 BEGIN
109 RAISERROR('Недостаточно свободного места на складах одного типа',1,1)
110 RETURN
111 END
112 END
113 SELECT [Inv].[ThingID], [IP].[Name], [Inv].[StorageID], [St].[Number] AS [StorageNumber], [St].[Type] AS [StorageType] --0.4
114 FROM [InventoryStorage] AS [St]
115 JOIN [Inventory] AS [Inv]
116 ON [Inv].[StorageID] = [St].[StorageID]
117 JOIN [InventoryPrice] AS [IP]
118 ON [IP].[InventoryID] = [Inv].[InventoryID]
119 WHERE [IP].[Name] = @name
120 AND [Inv].[DateOfDelivery] = CAST(GETDATE() AS date)
121 END
122END
123GO
124
125CREATE TABLE [Repair](
126[RepairID] int identity(1,1) PRIMARY KEY,
127[VehicleID] int NOT NULL REFERENCES [Vehicle] ([VehicleID]),
128[Date] date NOT NULL
129);
130GO
131
132CREATE TABLE [RequestVehicle](
133[RequestID] int identity(1,1) PRIMARY KEY,
134[ModelID] int NOT NULL REFERENCES [Model] ([ModelID]),
135[Date] date NOT NULL,
136[Done] bit NOT NULL
137);
138
139CREATE PROCEDURE [RepairVehicle] --9.5
140(@ID int)
141AS
142BEGIN
143 UPDATE [Vehicle] SET [Serviceableness] = 0 WHERE [VehicleID] = @ID --0.5
144 IF EXISTS (SELECT * FROM [Vehicle] AS [V] --0.2
145 JOIN [Model] AS [M]
146 ON [V].[ModelID] = [M].[ModelID]
147 WHERE [VehicleID] = @ID
148 AND GETDATE() <= DATEADD(year, [DepreciationPeriod], [DateOfIntoService])
149 )
150 BEGIN
151 INSERT INTO [Repair]([VehicleID],[Date]) --0.5
152 VALUES (@ID,GETDATE())
153 UPDATE [Vehicle] SET [Serviceableness] = 1 WHERE [VehicleID] = @ID --0.5
154 PRINT 'Транспорт будет отремонтирован'
155 END
156 ELSE
157 BEGIN
158 DECLARE @neededv TABLE(
159 [VehicleID] int,
160 [ModelID] int,
161 [VehicleGroupID] int,
162 [DateOfOutoService] date,
163 [Serviceableness] bit
164 );
165 INSERT INTO @neededv ([VehicleID],[ModelID],[VehicleGroupID],[DateOfOutoService],[Serviceableness]) --0.5
166 SELECT [V].[VehicleID], [M].[ModelID], [V].[VehicleGroupID], CAST(DATEADD(year,[M].[DepreciationPeriod],[V].[DateOfIntoService]) AS date), [V].Serviceableness --0.7
167 FROM [Vehicle] AS [V]
168 JOIN [Model] AS [M] ON [V].[ModelID] = [M].[ModelID]
169 JOIN [VehicleGroup] AS [VG] ON [V].[VehicleGroupID] = [VG].[VehicleGroupID]
170 LEFT JOIN [Worker] AS [W] ON [W].[VehicleID] = [V].[VehicleID]
171 WHERE [WorkerID] IS NULL
172 AND [VG].[Purpose] = (SELECT [Purpose] FROM [VehicleGroup] WHERE [VehicleGroupID] = (SELECT [VehicleGroupID] FROM [Vehicle] WHERE [VehicleID] = @ID))
173 IF EXISTS (SELECT * FROM @neededv)
174 BEGIN
175 IF EXISTS (SELECT * FROM @neededv --0.2
176 WHERE GETDATE() <= [DateOfOutoService]
177 AND [Serviceableness] = 1
178 )
179 BEGIN
180 UPDATE [Worker] SET [VehicleID] = (SELECT TOP 1 [VehicleID] FROM @neededv ORDER BY [DateOfOutoService] DESC) --0.5
181 WHERE [VehicleID] = @ID
182 PRINT 'Срок эксплуатации истек. Найдена замена из имеющейся техники'
183 END
184 ELSE
185 BEGIN
186 IF EXISTS (SELECT * FROM @neededv --0.2
187 WHERE GETDATE() <= [DateOfOutoService]
188 AND [Serviceableness] = 0
189 )
190 BEGIN
191 DECLARE @newID int
192 SET @newID = (SELECT TOP 1 [VehicleID] FROM @neededv ORDER BY [DateOfOutoService] DESC)
193 INSERT [Repair]([VehicleID],[Date]) --0.5
194 VALUES (@newID,GETDATE())
195 UPDATE [Vehicle] SET [Serviceableness] = 1 WHERE [VehicleID] = @newID --0.5
196 UPDATE [Worker] SET [VehicleID] = @newID WHERE [VehicleID] = @ID --0.5
197 PRINT 'Срок эксплуатации истек. Найдена замена из имеющейся техники, которая будет отремонтирована'
198 END
199 ELSE
200 BEGIN
201 IF EXISTS(SELECT * FROM @neededv --0.2
202 WHERE GETDATE() > [DateOfOutoService]
203 AND [Serviceableness] = 1
204 )
205 BEGIN
206 INSERT INTO [RequestVehicle] ([ModelID],[Date],[Done]) --0.5
207 VALUES ((SELECT [ModelID] FROM [Vehicle] WHERE [VehicleID] = @ID),GETDATE(),0)
208 UPDATE [Worker] SET [VehicleID] = (SELECT TOP 1 [VehicleID] FROM @neededv WHERE [Serviceableness] = 1)--0.5
209 PRINT 'Срок экплуатации истек. Найдена рабочая замена из имеющейся техники. В связи с ее так же истекшим сроком экплуатации заказана новая модель'
210 END
211 ELSE
212 BEGIN
213 INSERT INTO [Repair]([VehicleID],[Date]) --0.5
214 VALUES (@ID,GETDATE())
215 UPDATE [Vehicle] SET [Serviceableness] = 1 WHERE [VehicleID] = @ID --0.5
216 INSERT INTO [RequestVehicle] ([ModelID],[Date],[Done]) --0.5
217 VALUES ((SELECT [ModelID] FROM [Vehicle] WHERE [VehicleID] = @ID),GETDATE(),0)
218 PRINT 'Транспорт будет отремонтирован. В связи с истекшим сроком эксплуатации заказана новая модель'
219 END
220 END
221 END
222 END
223 ELSE
224 BEGIN
225 INSERT INTO [Repair]([VehicleID],[Date]) --0.5
226 VALUES (@ID,GETDATE())
227 UPDATE [Vehicle] SET [Serviceableness] = 1 WHERE [VehicleID] = @ID --0.5
228 INSERT INTO [RequestVehicle] ([ModelID],[Date],[Done]) --0.5
229 VALUES ((SELECT [ModelID] FROM [Vehicle] WHERE [VehicleID] = @ID),GETDATE(),0)
230 PRINT 'Транспорт будет отремонтирован. В связи с истекшим сроком эксплуатации заказана новая модель'
231 END
232 END
233END
234
235
236CREATE FUNCTION [MonthlyCosts] --1.2
237(@month int, @year int)
238RETURNS int
239AS
240BEGIN
241 DECLARE @res int, @date date
242 SET @date = DATEFROMPARTS(@year,@month,10)
243
244 SET @res = ISNULL((SELECT SUM([Salary]) --0.2
245 FROM [Contract]
246 WHERE (@date BETWEEN [ConclusionDate] AND ISNULL([TerminationDate],DATEADD(year,1,@date)))
247 AND DATEDIFF(DAY,[ConclusionDate],[TerminationDate]) >= 30)
248 ,0)
249
250 SET @res = @res + ISNULL((SELECT SUM([Salary]) --0.2
251 FROM [WorkerArchive]
252 WHERE (@date BETWEEN [StartWorkingDate] AND [EndWorkingDate])
253 AND DATEDIFF(DAY,[StartWorkingDate],[EndworkingDate]) >= 30)
254 ,0)
255
256 SET @res = @res + ISNULL((SELECT SUM([Price])*1000 --0.2
257 FROM [Model] AS [M]
258 JOIN [Vehicle] AS [V]
259 ON [M].[ModelID] = [V].[ModelID]
260 WHERE MONTH([V].[DateOfIntoService]) = @month
261 AND YEAR([V].[DateOfIntoService]) = @year)
262 ,0)
263
264 SET @res = @res + ISNULL((SELECT SUM([Price]) --0.2
265 FROM [InventoryPrice] AS [IP]
266 JOIN [Inventory] AS [Inv]
267 ON [IP].[InventoryID] = [Inv].[InventoryID]
268 WHERE MONTH([Inv].[DateOfDelivery]) = @month
269 AND YEAR([Inv].[DateOfDelivery]) = @year)
270 ,0)
271
272 SET @res = @res + ISNULL((SELECT SUM(DATEDIFF(minute,[LoadingTime],ISNULL([UnloadingTime],[LoadingTime])))*225 --0.2
273 FROM [ExportFromStock]
274 WHERE MONTH([Date]) = @month
275 AND YEAR([Date]) = @year)
276 ,0)
277
278 SET @res = @res + ISNULL((SELECT SUM(DATEDIFF(minute,[LoadingTime],ISNULL([UnloadingTime],[LoadingTime])))*225 --0.2
279 FROM [ImportToStock]
280 WHERE MONTH([Date]) = @month
281 AND YEAR([Date]) = @year)
282 ,0)
283
284 SET @res = @res + ISNULL((SELECT SUM(CAST(DATEDIFF(minute,[DepartureTime],[ArrivalTime])*[Power]*0.0324 as int))
285 FROM [Train] AS [T]
286 JOIN [TrainSchedule] AS [TS]
287 ON [T].[TrainID] = [TS].[TrainID])
288 ,0)
289
290 RETURN @res
291END