MyTestRepository/- SQLcode.txt at master · alexeypato/MyTestRepository · 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
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
SA T6hjm*UI

http://wladm.narod.ru/C_Sharp/comexcel.html

SELECT	ISNULL(round(sum(dbo.getTrueSumm(1, dokttn.tip_preskgp, dokttn.datattn, ttnMain.summa, ttn.id_ttn)), 2), 0)
		as 'Сумма за I период',
		YEAR(dokttn.datattn),
		MONTH(dokttn.datattn)

FROM    dbo.ttn as ttnMain INNER JOIN
        dbo.dokttn ON ttnMain.id_dokttn = dbo.dokttn.id_dokttn INNER JOIN
        dbo.products ON ttnMain.id_product = dbo.products.id_product INNER JOIN
        dbo.organization ON dbo.dokttn.id_orgpol = dbo.organization.id_organization INNER JOIN
        dbo.country ON dbo.organization.id_country = dbo.country.id_country

WHERE   (dokttn.status='prov')
        and (country.id_country != '1')
		and (dokttn.id_orgpol != '1')
		and (dokttn.fiosotr != '1')
		and (dokttn.tipttn = 'Реализация по комиссии')
		and (products.tip = 'Продукция технического назначения')
        and (ttnMain.id_product != '1')
		and (dokttn.datattn BETWEEN '01/01/2015' AND '30/06/2016 23:59:59')

GROUP BY   YEAR(dokttn.datattn), MONTH(dokttn.datattn)
ORDER BY   YEAR(dokttn.datattn), MONTH(dokttn.datattn)


SELECT	ttnMain.id_product as ID,
		(SELECT dbo.products.nomer_product
		FROM dbo.products
		WHERE dbo.products.id_product = ttnMain.id_product) as 'Номер изделия',

		(SELECT dbo.products.name_kratkoe
		FROM dbo.products
		WHERE dbo.products.id_product = ttnMain.id_product) as 'Краткое название изделия',

		(SELECT dbo.products.name_product
		FROM dbo.products
		WHERE dbo.products.id_product = ttnMain.id_product) as 'Название изделия',

		ISNULL((SELECT sum(kolvo)
				FROM ttn INNER JOIN
					dokttn ON ttn.id_dokttn = dokttn.id_dokttn INNER JOIN
					products ON ttn.id_product = products.id_product INNER JOIN
					organization ON dokttn.id_orgpol = organization.id_organization INNER JOIN
					country ON organization.id_country = country.id_country
				WHERE     (dokttn.status='prov')
					and (country.id_country != '1')
					and (dokttn.id_orgpol != '1')
					and (dokttn.fiosotr != '1')
					and (dokttn.tipttn = 'Простая')
					and (products.tip = 'Продукция технического назначения')
					and (ttn.id_product = ttnMain.id_product)
					and (dokttn.datattn BETWEEN '01/06/2016' AND '30/06/2016 23:59:59')), 0)
		as 'Кол-во за I период, шт.',

		ISNULL((SELECT sum(kolvo)
				FROM ttn INNER JOIN
					dokttn ON ttn.id_dokttn = dokttn.id_dokttn INNER JOIN
					products ON ttn.id_product = products.id_product INNER JOIN
					organization ON dokttn.id_orgpol = organization.id_organization INNER JOIN
					country ON organization.id_country = country.id_country
				WHERE     (dokttn.status='prov')
					and (country.id_country != '1')
					and (dokttn.id_orgpol != '1')
					and (dokttn.fiosotr != '1')
					and (dokttn.tipttn = 'Простая')
					and (products.tip = 'Продукция технического назначения')
					and (ttn.id_product = ttnMain.id_product)
					and (dokttn.datattn BETWEEN '01/07/2016' AND '31/07/2016 23:59:59')), 0)
		as 'Кол-во за II период, шт.',

		ISNULL((SELECT round(sum(dbo.getTrueSumm(4, dokttn.tip_preskgp, dokttn.datattn, ttn.summa, ttn.id_ttn)), 2)
				FROM ttn INNER JOIN
					dokttn ON ttn.id_dokttn = dokttn.id_dokttn INNER JOIN
					products ON ttn.id_product = products.id_product INNER JOIN
					organization ON dokttn.id_orgpol = organization.id_organization INNER JOIN
					country ON organization.id_country = country.id_country
				WHERE     (dokttn.status='prov')
					and (country.id_country != '1')
					and (dokttn.id_orgpol != '1')
					and (dokttn.fiosotr != '1')
					and (dokttn.tipttn = 'Простая')
					and (products.tip = 'Продукция технического назначения')
					and (ttn.id_product = ttnMain.id_product)
					and (dokttn.datattn BETWEEN '01/06/2016' AND '30/06/2016 23:59:59')), 0)
		as 'Сумма за I период',

		ISNULL((SELECT round(sum(dbo.getTrueSumm(4, dokttn.tip_preskgp, dokttn.datattn, ttn.summa, ttn.id_ttn)), 2)
				FROM ttn INNER JOIN
					dokttn ON ttn.id_dokttn = dokttn.id_dokttn INNER JOIN
					products ON ttn.id_product = products.id_product INNER JOIN
					organization ON dokttn.id_orgpol = organization.id_organization INNER JOIN
					country ON organization.id_country = country.id_country
				WHERE     (dokttn.status='prov')
					and (country.id_country != '1')
					and (dokttn.id_orgpol != '1')
					and (dokttn.fiosotr != '1')
					and (dokttn.tipttn = 'Простая')
					and (products.tip = 'Продукция технического назначения')
					and (ttn.id_product = ttnMain.id_product)
					and (dokttn.datattn BETWEEN '01/07/2016' AND '31/07/2016 23:59:59')), 0)
		as 'Сумма за II период'

FROM    dbo.ttn as ttnMain INNER JOIN
        dbo.dokttn ON ttnMain.id_dokttn = dbo.dokttn.id_dokttn INNER JOIN
        dbo.products ON ttnMain.id_product = dbo.products.id_product INNER JOIN
        dbo.organization ON dbo.dokttn.id_orgpol = dbo.organization.id_organization INNER JOIN
        dbo.country ON dbo.organization.id_country = dbo.country.id_country

WHERE   (dokttn.status='prov')
        and (country.id_country != '1')
		and (dokttn.id_orgpol != '1')
		and (dokttn.fiosotr != '1')
		and (dokttn.tipttn = 'Простая')
		and (products.tip = 'Продукция технического назначения')
        and (ttnMain.id_product != '1')
		and (dokttn.datattn BETWEEN '01/06/2016' AND '31/07/2016 23:59:59')

GROUP BY   ttnMain.id_product
ORDER BY   'Название изделия', 'Номер изделия'


ALTER FUNCTION [dbo].[getTrueSumm](@requiredCurrency bigint, @currentCurrency varchar(MAX), @date date, @summa float, @currentTTNID varchar(MAX))
RETURNS float
	BEGIN
		DECLARE @tempSumm float = @summa

		/****** Переводим в Белорусские рубли ******/
		IF (@requiredCurrency = 1)
		BEGIN

			IF ((@currentCurrency = 'Отпускные белорусский руб.'
				or @currentCurrency = 'Розничные белорусский руб.'
				or @currentCurrency = 'Цены для МАЗа') and @date < '01/07/2016')
			RETURN @summa/10000

			IF (@currentCurrency = 'Отпускные белорусский руб.'
				or @currentCurrency = 'Розничные белорусский руб.'
				or @currentCurrency = 'Цены для МАЗа')
			RETURN @summa

			IF (@currentCurrency = 'Отпускные российский руб.')
			BEGIN
				SELECT @tempSumm = @summa * BAAZKIS.dbo.kyrsval.value_kyrsval
				FROM BAAZKIS.dbo.kyrsval
				WHERE (BAAZKIS.dbo.kyrsval.date_kyrsval = @date and BAAZKIS.dbo.kyrsval.id_valuta = 4)

				IF (@date < '01/07/2016')
				    RETURN @tempSumm/10000
				ELSE
				    RETURN @tempSumm
			END

			IF (@currentCurrency = 'Отпускные доллар США')
			BEGIN
				SELECT @tempSumm = @summa * BAAZKIS.dbo.kyrsval.value_kyrsval
				FROM BAAZKIS.dbo.kyrsval
				WHERE (BAAZKIS.dbo.kyrsval.date_kyrsval = @date and BAAZKIS.dbo.kyrsval.id_valuta = 2)

				IF (@date < '01/07/2016')
				    RETURN @tempSumm/10000
				ELSE
				    RETURN @tempSumm
			END

			IF (@currentCurrency = 'Валюта')
			BEGIN
				SELECT @tempSumm = @summa * BAAZKIS.dbo.kyrsval.value_kyrsval
				FROM BAAZKIS.dbo.kyrsval
				WHERE (BAAZKIS.dbo.kyrsval.date_kyrsval = @date and BAAZKIS.dbo.kyrsval.id_valuta = (SELECT dbo.docpreskyrantvaluta.id_valuta
																									 FROM dbo.valuta INNER JOIN
																										  dbo.docpreskyrantvaluta ON dbo.valuta.id_valuta = dbo.docpreskyrantvaluta.id_valuta INNER JOIN
																										  dbo.ttn ON dbo.docpreskyrantvaluta.id_docpreskyrantvaluta = dbo.ttn.id_docpreskyrant INNER JOIN
																									      dbo.dokttn ON dbo.ttn.id_dokttn = dbo.dokttn.id_dokttn
																								     WHERE        (dbo.ttn.id_ttn = @currentTTNID)))

				IF (@date < '01/07/2016')
				    RETURN @tempSumm/10000
				ELSE
				    RETURN @tempSumm
			END

		END

		/****** Переводим в Доллары США ******/
		IF (@requiredCurrency = 2)
		BEGIN

			IF (@currentCurrency = 'Отпускные белорусский руб.'
				or @currentCurrency = 'Розничные белорусский руб.'
				or @currentCurrency = 'Цены для МАЗа')
			BEGIN
				SELECT @tempSumm = @summa / BAAZKIS.dbo.kyrsval.value_kyrsval
				FROM BAAZKIS.dbo.kyrsval
				WHERE (BAAZKIS.dbo.kyrsval.date_kyrsval = @date and BAAZKIS.dbo.kyrsval.id_valuta = 2)
				RETURN @tempSumm
			END

			IF (@currentCurrency = 'Отпускные российский руб.')
			BEGIN
				SELECT @tempSumm = @summa * BAAZKIS.dbo.kyrsval.value_kyrsval
				FROM BAAZKIS.dbo.kyrsval
				WHERE (BAAZKIS.dbo.kyrsval.date_kyrsval = @date and BAAZKIS.dbo.kyrsval.id_valuta = 4)

				SELECT @tempSumm = @tempSumm / BAAZKIS.dbo.kyrsval.value_kyrsval
				FROM BAAZKIS.dbo.kyrsval
				WHERE (BAAZKIS.dbo.kyrsval.date_kyrsval = @date and BAAZKIS.dbo.kyrsval.id_valuta = 2)

				RETURN @tempSumm
			END

			IF (@currentCurrency = 'Валюта')
			BEGIN
				SELECT @tempSumm = @summa * BAAZKIS.dbo.kyrsval.value_kyrsval
				FROM BAAZKIS.dbo.kyrsval
				WHERE (BAAZKIS.dbo.kyrsval.date_kyrsval = @date and BAAZKIS.dbo.kyrsval.id_valuta = (SELECT dbo.docpreskyrantvaluta.id_valuta
																									 FROM dbo.valuta INNER JOIN
																										  dbo.docpreskyrantvaluta ON dbo.valuta.id_valuta = dbo.docpreskyrantvaluta.id_valuta INNER JOIN
																										  dbo.ttn ON dbo.docpreskyrantvaluta.id_docpreskyrantvaluta = dbo.ttn.id_docpreskyrant INNER JOIN
																									      dbo.dokttn ON dbo.ttn.id_dokttn = dbo.dokttn.id_dokttn
																								     WHERE        (dbo.ttn.id_ttn = @currentTTNID)))

				SELECT @tempSumm = @tempSumm / BAAZKIS.dbo.kyrsval.value_kyrsval
				FROM BAAZKIS.dbo.kyrsval
				WHERE (BAAZKIS.dbo.kyrsval.date_kyrsval = @date and BAAZKIS.dbo.kyrsval.id_valuta = 2)

				RETURN @tempSumm
			END

			IF (@currentCurrency = 'Отпускные доллар США')
			RETURN @summa

		END

		/****** Переводим в EURO ******/
		IF (@requiredCurrency = 3)
		BEGIN

			IF (@currentCurrency = 'Отпускные белорусский руб.'
				or @currentCurrency = 'Розничные белорусский руб.'
				or @currentCurrency = 'Цены для МАЗа')
			BEGIN
				SELECT @tempSumm = @summa / BAAZKIS.dbo.kyrsval.value_kyrsval
				FROM BAAZKIS.dbo.kyrsval
				WHERE (BAAZKIS.dbo.kyrsval.date_kyrsval = @date and BAAZKIS.dbo.kyrsval.id_valuta = @requiredCurrency)
				RETURN @tempSumm
			END

			IF (@currentCurrency = 'Отпускные российский руб.')
			BEGIN
				SELECT @tempSumm = @summa * BAAZKIS.dbo.kyrsval.value_kyrsval
				FROM BAAZKIS.dbo.kyrsval
				WHERE (BAAZKIS.dbo.kyrsval.date_kyrsval = @date and BAAZKIS.dbo.kyrsval.id_valuta = 4)

				SELECT @tempSumm = @tempSumm / BAAZKIS.dbo.kyrsval.value_kyrsval
				FROM BAAZKIS.dbo.kyrsval
				WHERE (BAAZKIS.dbo.kyrsval.date_kyrsval = @date and BAAZKIS.dbo.kyrsval.id_valuta = @requiredCurrency)

				RETURN @tempSumm
			END

			IF (@currentCurrency = 'Отпускные доллар США')
			BEGIN
				SELECT @tempSumm = @summa * BAAZKIS.dbo.kyrsval.value_kyrsval
				FROM BAAZKIS.dbo.kyrsval
				WHERE (BAAZKIS.dbo.kyrsval.date_kyrsval = @date and BAAZKIS.dbo.kyrsval.id_valuta = 2)

				SELECT @tempSumm = @tempSumm / BAAZKIS.dbo.kyrsval.value_kyrsval
				FROM BAAZKIS.dbo.kyrsval
				WHERE (BAAZKIS.dbo.kyrsval.date_kyrsval = @date and BAAZKIS.dbo.kyrsval.id_valuta = @requiredCurrency)

				RETURN @tempSumm
			END

			IF (@currentCurrency = 'Валюта')
			BEGIN

			    IF (@requiredCurrency <> (SELECT dbo.docpreskyrantvaluta.id_valuta
									   FROM dbo.valuta INNER JOIN
											dbo.docpreskyrantvaluta ON dbo.valuta.id_valuta = dbo.docpreskyrantvaluta.id_valuta INNER JOIN
											dbo.ttn ON dbo.docpreskyrantvaluta.id_docpreskyrantvaluta = dbo.ttn.id_docpreskyrant INNER JOIN
											dbo.dokttn ON dbo.ttn.id_dokttn = dbo.dokttn.id_dokttn
									   WHERE        (dbo.ttn.id_ttn = @currentTTNID)))
			    BEGIN
					SELECT @tempSumm = @summa * BAAZKIS.dbo.kyrsval.value_kyrsval
					FROM BAAZKIS.dbo.kyrsval
					WHERE (BAAZKIS.dbo.kyrsval.date_kyrsval = @date and BAAZKIS.dbo.kyrsval.id_valuta = (SELECT dbo.docpreskyrantvaluta.id_valuta
																									 FROM dbo.valuta INNER JOIN
																										  dbo.docpreskyrantvaluta ON dbo.valuta.id_valuta = dbo.docpreskyrantvaluta.id_valuta INNER JOIN
																										  dbo.ttn ON dbo.docpreskyrantvaluta.id_docpreskyrantvaluta = dbo.ttn.id_docpreskyrant INNER JOIN
																									      dbo.dokttn ON dbo.ttn.id_dokttn = dbo.dokttn.id_dokttn
																								     WHERE        (dbo.ttn.id_ttn = @currentTTNID)))

					SELECT @tempSumm = @tempSumm / BAAZKIS.dbo.kyrsval.value_kyrsval
					FROM BAAZKIS.dbo.kyrsval
					WHERE (BAAZKIS.dbo.kyrsval.date_kyrsval = @date and BAAZKIS.dbo.kyrsval.id_valuta = @requiredCurrency)

					RETURN @tempSumm
			    END

			    ELSE
					RETURN @summa
			END

		END

		/****** Переводим в Российские рубли ******/
		IF (@requiredCurrency = 4)
		BEGIN

			IF (@currentCurrency = 'Отпускные белорусский руб.'
				or @currentCurrency = 'Розничные белорусский руб.'
				or @currentCurrency = 'Цены для МАЗа')
			BEGIN
				SELECT @tempSumm = @summa / BAAZKIS.dbo.kyrsval.value_kyrsval
				FROM BAAZKIS.dbo.kyrsval
				WHERE (BAAZKIS.dbo.kyrsval.date_kyrsval = @date and BAAZKIS.dbo.kyrsval.id_valuta = 4)
				RETURN @tempSumm
			END

			IF (@currentCurrency = 'Отпускные российский руб.')
			RETURN @summa

			IF (@currentCurrency = 'Отпускные доллар США')
			BEGIN
				SELECT @tempSumm = @summa * BAAZKIS.dbo.kyrsval.value_kyrsval
				FROM BAAZKIS.dbo.kyrsval
				WHERE (BAAZKIS.dbo.kyrsval.date_kyrsval = @date and BAAZKIS.dbo.kyrsval.id_valuta = 2)

				SELECT @tempSumm = @tempSumm / BAAZKIS.dbo.kyrsval.value_kyrsval
				FROM BAAZKIS.dbo.kyrsval
				WHERE (BAAZKIS.dbo.kyrsval.date_kyrsval = @date and BAAZKIS.dbo.kyrsval.id_valuta = 4)
				RETURN @tempSumm
			END

			IF (@currentCurrency = 'Валюта')
			BEGIN
				SELECT @tempSumm = @summa * BAAZKIS.dbo.kyrsval.value_kyrsval
				FROM BAAZKIS.dbo.kyrsval
				WHERE (BAAZKIS.dbo.kyrsval.date_kyrsval = @date and BAAZKIS.dbo.kyrsval.id_valuta = (SELECT dbo.docpreskyrantvaluta.id_valuta
																									 FROM dbo.valuta INNER JOIN
																										  dbo.docpreskyrantvaluta ON dbo.valuta.id_valuta = dbo.docpreskyrantvaluta.id_valuta INNER JOIN
																										  dbo.ttn ON dbo.docpreskyrantvaluta.id_docpreskyrantvaluta = dbo.ttn.id_docpreskyrant INNER JOIN
																									      dbo.dokttn ON dbo.ttn.id_dokttn = dbo.dokttn.id_dokttn
																								     WHERE        (dbo.ttn.id_ttn = @currentTTNID)))

				SELECT @tempSumm = @tempSumm / BAAZKIS.dbo.kyrsval.value_kyrsval
				FROM BAAZKIS.dbo.kyrsval
				WHERE (BAAZKIS.dbo.kyrsval.date_kyrsval = @date and BAAZKIS.dbo.kyrsval.id_valuta = 4)
				RETURN @tempSumm
			END

		END

		RETURN @tempSumm
	END