IBExpert,
Не работают такие:
1)
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.
create or alter procedure update_dispdoc (
id integer,
vid_move integer = 0)
AS
declare variable vid int;
declare variable id_doc int;
declare variable date_nk date;
declare variable id_doctype int;
declare variable d0 date;
declare variable d2 date;
declare variable id_mand int;
declare variable i4 int;
begin
vid = 10000 + dtdispdoc();
select id_doctype, date_nk, id_mandfrom from doc where id = :id into id_doctype, d2, id_mand;
if (id_doctype = dtdispdoc()) then
begin
-- Проверяем совпадают ли остатки
for select first 1 id_doc, date_nk from period
where id_mand = :id_mand and date_nk > :d2 and vid = :vid
order by date_nk
into id_doc, d0 do
if (d0 <> d2 + 1 or
exists(select 1
from (select id_item, coalesce(items2, n13, 0) items from docdet where id_doc = :id and id_docdettype = 1
union all
select id_item, -n1 from docdet where id_doc = :id_doc and id_docdettype = 1) t
group by id_item
having sum(items) <> 0)) then
begin
update doc set i4 = 1 where id = :id_doc and i4 is distinct from 1;
insert into docdet (id_doc, id_docdettype, id_item, i1, i2, d1, i3, i4, i5)
values (:id_doc, 3, 0, :id, :id_doctype, :d2, :id_mand, 0, 1);
end
end else
for select distinct dd.id, dd.i4 from doc d
join mand_folder f on f.id_mand in (d.id_mandfrom, d.id_mandto)
join mand_category c on c.id = f.id_category and c.vid = sktSkladHlebAll() and c.category_type = 0
join period p on p.id_mand = f.id_mand and p.date_nk = d.date_nk and p.vid = :vid
join doc dd on dd.id = p.id_doc
where d.id = :id
into id_doc, i4 do
begin
if (i4 is distinct from 1) then
update doc set i4 = 1 where id = :id_doc;
insert into docdet (id_doc, id_docdettype, id_item, i1, i2, d1, v1, i3, i4, i5)
select :id_doc, 3, 0, id, id_doctype, date_nk, num_nk, id_mandfrom, id_mandto, enabled
from doc
where id = :id;
end
if (vid_move <> 0) then
-- Изменение остатков при перепроведении накладных
for select d.id_mand, min(d.date_nk), min(d0.id) id
from (select dd.id_mandfrom id_mand, min(dd.date_nk) date_nk, list(dd.id) ids
from doc d
join mand_folder f on f.id_mand in (d.id_mandfrom, d.id_mandto)
join mand_category c on c.id = f.id_category and c.vid = sktSkladHlebAll() and c.category_type = 0
join period p on p.id_mand = f.id_mand and p.date_nk >= d.date_nk and p.vid = :vid
join doc dd on dd.id = p.id_doc and dd.enabled <> 0 and dd.id <> d.id
where d.id = :id
group by 1) d
left join strtoid(d.ids) c on 1=1
left join doc d0 on d0.id = c.code
where d.date_nk = d0.date_nk
group by 1
into i4, date_nk, id_doc do
begin
if (vid_move = -1) then
update move set
id_doc = :id_doc,
date_nk = :date_nk,
id_doctype = dtdispdoc(),
i1 = :id,
sysregion = base_id()
where id_doc = :id and id_mand = :i4 and ost = 0;
if (vid_move = 1) then
insert into move (id_doc, id_doctype, date_nk, sign, id_mand, id_item, items, summa, i1)
select :id_doc, dtdispdoc(), :date_nk, -sign, id_mand, id_item, items, summa, :id
from move
where id_doc = :id and id_mand = :i4 and ost = 0;
-- Очистка повторов
merge into move v
using (select code id
from (select list(id) ids from move
where id_doc+0 = :id_doc and id_mand = :i4 and i1 = :id and ost = 0
group by id_item+0
having sum(items * sign) = 0 and sum(summa * sign) = 0)
left join strtoid(ids) on 1=1) c on c.id = v.id
when matched then
delete;
insert into docdet (id_doc, id_item, id_docdettype)
select distinct :id_doc, v.id_item, 1
from move v
left join docdet t on t.id_doc = :id_doc and t.id_docdettype = 1 and t.id_item = v.id_item
where v.id_doc = :id_doc and t.id is null;
if (row_count <> 0) then
update doc set i6 = 1 where id = :id_doc and i6 is distinct from 1;
end
end
2)
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.
create or alter procedure calc_vipusksumma (
id_doc integer,
date_nk date = null)
AS
declare variable id_session int;
declare variable id int;
declare variable id_item int;
declare variable id_item1 int;
declare variable items numeric(12,3);
declare variable items0 numeric(12,3);
declare variable summa numeric(12,2);
declare variable summa0 numeric(12,2);
declare variable summa1 numeric(12,2);
declare variable id_from int = 4; -- Изделия
declare variable id_from0 int = 1; -- Изделия
declare variable id_to int = 2; -- Расход сырья
declare variable id_from2 int = 6; -- Мочка (замена)
declare variable id_vid_calc int = 8; -- Теоритический расход
declare variable id_vid_calc_m int = 9; -- Расчет по расходу мочки
declare variable vid11 int = 11;
begin
id_session = gen_id(gen_session, 1);
if (date_nk is null) then
select date_nk from doc where id = :id_doc into date_nk;
select count(*), sum(summa) from docdet where id_doc = :id_doc and id_docdettype in (:id_vid_calc, :id_vid_calc_m)
into id, summa;
if (id = 0 or summa is null) then
execute procedure calc_vipuskrecept(:id_doc, :date_nk);
insert into session_edit (id_item, i1, i2, n1, n2, id_session, i5, n4, n5)
select id_item, i1, id_docdettype, sum(items), coalesce(sum(summa), 0), :id_session, coalesce(min(i5), 0), 0, sum(items2) from docdet
where id_doc = :id_doc and id_docdettype in (:id_to, :id_from0, :id_from, :id_from2, :id_vid_calc, :id_vid_calc_m)
group by id_doc, id_item, id_docdettype, i1
order by 3, 5, 4;
delete from session_edit where id_session = :id_session and i2 = :id_vid_calc and i5 = 1;
delete from docdet where id_doc = :id_doc and id_docdettype = :id_vid_calc and i5 = 1;
update session_edit set n2 = 0 where id_session = :id_session and i2 in(:id_vid_calc, :id_from0, :id_from, :id_from2);
-- Распределение стоимости по изделиям и п/ф
for select id_item, sum(n1) from session_edit
where id_session = :id_session and i2 = :id_vid_calc
group by 1
having sum(n1) <> 0
order by 2
into id_item, items do
begin
summa0 = 0; items0 = 0;
select sum(n1-n5), sum(n2) from session_edit
where id_session = :id_session and i2 = :id_to and id_item = :id_item
into items0, summa0;
update session_edit set
n2 = n2 + cast(1e0 * n1*:summa0/:items as numeric(12,2)),
n4 = cast(1e0 * n1*:items0/:items as numeric(12,3)) - n1
where id_session = :id_session and i2 = :id_vid_calc and id_item = :id_item;
select sum(n1+n4), sum(n2) from session_edit
where id_session = :id_session and i2 = :id_vid_calc and id_item = :id_item
into items, summa;
if (summa <> summa0 or items <> items0) then
update session_edit set
n2 = n2 - :summa + :summa0,
n4 = n4 - :items + :items0
where id_session = :id_session and i2 = :id_vid_calc and id_item = :id_item
order by n1+n4 desc
rows 1;
update session_edit set
i4 = 1
where id_session = :id_session and i2 = :id_to and id_item = :id_item;
end
select sum(n2) from session_edit where id_session = :id_session and i2 = :id_vid_calc and i5 = 0
into summa1;
for select id_item, sum(n1+n4), sum(n2) from session_edit
where id_session = :id_session and i2 = :id_to and i4 is null
group by 1
having sum(n2) <> 0
into id_item, items0, summa0 do
begin
for select i1, sum(n2) from session_edit
where id_session = :id_session and i2 = :id_vid_calc and i5 = 0
group by 1
having sum(n2) <> 0
order by 2
into id_item1, summa do
begin
items = 1e0 * items0 * summa / summa1;
summa = 1e0 * summa0 * summa / summa1;
insert into session_edit(id_session, i2, id_item, i1, n1, n2, n4, i5)
values(:id_session, :id_vid_calc, :id_item, :id_item1, 0, :summa, :items, 1)
returning id into id;
end
select sum(n1+n4), sum(n2) from session_edit
where id_session = :id_session and i2 = :id_vid_calc and id_item = :id_item and i5 = 1
into items, summa;
if (items0 <> items or summa0 <> summa) then
update session_edit set
n4 = n4 - :items + :items0,
n2 = n2 - :summa + :summa0
where id = :id;
update session_edit set
i4 = 1
where id_session = :id_session and i2 = :id_to and id_item = :id_item;
end
insert into session_edit (id_item, n1, n2, n3, i2, id_session)
select id_item, sum(items), sum(summa), sum(items_in), :vid11, :id_session
from (select id_item,
sum(n1+n4) items,
sum(n2) summa,
cast(null as numeric(12,3)) items_in
from session_edit
where id_session = :id_session and i2 = :id_vid_calc
group by 1
union all
select id_item, 0 items, 0 summa, n1 items_in
from session_edit
where id_session = :id_session and i2 = :id_from0)
group by 1;
-- Расчет себестоимости изделий и п/ф
for with recursive
items0 as (
select id_item, i1, cast(sum(n1) as numeric(12,3)) items
from session_edit
where id_session = :id_session and i2 = :id_vid_calc
group by 1, 2),
prices0 as (
select id_item,
cast(n1 as numeric(12,3)) items,
cast(n2 as numeric(12,2)) summa,
cast(n3 as numeric(12,3)) items_in
from session_edit
where id_session = :id_session and i2 = :vid11),
prices as (
select id_item, items, summa, items_in from prices0
union all
select p0.id_item,
p0.items_in,
1e0*minvalue(coalesce(p.items_in, s.items), s.items) * p.summa / nullif(coalesce(p.items_in, p.items), 0) summa,
p0.items_in
from prices0 p0
join items0 s on s.i1 = p0.id_item
join prices p on p.id_item = s.id_item)
select p.id_item, sum(p.summa) summa from prices0 p0
join prices p on p.id_item = p0.id_item
group by 1
having min(p0.items_in) <> 0
into id_item, summa do
update session_edit set
n2 = :summa
where id_session = :id_session and i2 = :id_from0 and id_item = :id_item;
merge into docdet t
using (select id_item, i1, sum(n2) summa, sum(n1) items, sum(n4) items2, sum(n2)/nullif(sum(n1+n4), 0) price
from session_edit
where id_session = :id_session and i2 = :id_vid_calc
group by 1, 2) c on id_doc = :id_doc and id_docdettype = :id_vid_calc and t.id_item = c.id_item and t.i1 = c.i1
when matched then
update set price = c.price,
summa = c.summa,
items = c.items,
items2 = c.items2
when not matched then
insert (id_item, i1, items, items2, price, summa, id_doc, id_docdettype, i5)
values (c.id_item, c.i1, c.items, c.items2, c.price, c.summa, :id_doc, :id_vid_calc, 1);
merge into docdet t
using (select id_item, sum(n2) summa, sum(n1) items, sum(n2)/nullif(sum(n1), 0) price from session_edit
where id_session = :id_session and i2 = :id_from0
group by 1) c on id_doc = :id_doc and id_docdettype = :id_to and t.id_item = c.id_item and (t.n5 is distinct from c.price or t.n6 is distinct from c.summa)
when matched then
update set n6 = iif(c.items = t.items, c.summa, c.items * c.price),
n5 = iif(c.items = t.items, c.summa / nullif(c.items, 0), c.price);
merge into docdet t
using (select id_item, sum(n2) n2 from session_edit
where id_session = :id_session and i2 = :id_from0
group by 1) c on id_doc = :id_doc and id_docdettype = :id_from and t.id_item = c.id_item and summa is distinct from c.n2
when matched then
update set summa = c.n2, price = coalesce(1e0 * c.n2/nullif(items, 0), 0);
delete from docdet where id_doc = :id_doc and id_docdettype = :id_vid_calc and i5 = 1 and items = 0 and items2 = 0 and summa = 0;
end
3)
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.
363.
364.
365.
366.
367.
368.
369.
370.
371.
372.
373.
374.
375.
376.
377.
378.
379.
380.
381.
382.
383.
384.
385.
386.
387.
388.
389.
390.
391.
392.
393.
394.
395.
396.
397.
398.
399.
400.
401.
402.
403.
404.
405.
406.
407.
408.
409.
410.
411.
412.
413.
414.
415.
416.
417.
418.
419.
420.
421.
422.
423.
424.
425.
426.
427.
428.
429.
430.
431.
432.
433.
434.
435.
436.
437.
438.
439.
440.
441.
442.
443.
444.
445.
446.
447.
448.
449.
450.
451.
452.
453.
454.
455.
456.
457.
458.
459.
460.
461.
462.
463.
464.
465.
466.
467.
468.
469.
470.
471.
472.
473.
474.
475.
476.
477.
478.
479.
480.
481.
482.
483.
484.
485.
486.
487.
488.
489.
490.
491.
492.
493.
494.
495.
496.
497.
498.
499.
500.
501.
502.
503.
504.
505.
506.
507.
508.
509.
510.
511.
512.
513.
514.
515.
516.
517.
518.
519.
520.
521.
522.
523.
524.
525.
526.
527.
528.
529.
530.
531.
532.
533.
534.
535.
536.
537.
538.
539.
540.
541.
542.
543.
544.
545.
546.
547.
548.
549.
550.
551.
552.
553.
554.
555.
556.
557.
558.
559.
560.
561.
562.
563.
564.
565.
566.
567.
568.
569.
570.
571.
572.
573.
574.
575.
576.
577.
578.
579.
580.
581.
582.
583.
584.
585.
586.
587.
588.
589.
590.
591.
592.
593.
594.
595.
596.
597.
598.
599.
600.
601.
602.
603.
604.
605.
606.
607.
608.
609.
610.
611.
612.
613.
614.
615.
616.
617.
618.
619.
620.
621.
622.
623.
624.
625.
626.
627.
628.
629.
630.
631.
632.
633.
634.
635.
636.
637.
638.
639.
640.
641.
642.
643.
644.
645.
646.
647.
648.
649.
650.
651.
652.
653.
654.
655.
656.
657.
658.
659.
660.
661.
662.
663.
664.
665.
666.
667.
668.
669.
670.
671.
672.
673.
674.
675.
676.
677.
678.
679.
680.
681.
682.
683.
684.
685.
686.
687.
688.
689.
690.
691.
692.
693.
694.
695.
696.
697.
698.
699.
700.
701.
702.
703.
704.
705.
706.
707.
708.
709.
710.
711.
712.
713.
714.
715.
716.
717.
718.
719.
720.
721.
722.
723.
724.
725.
726.
727.
728.
729.
730.
731.
732.
733.
734.
735.
736.
737.
738.
739.
740.
741.
742.
743.
744.
745.
746.
747.
748.
749.
750.
751.
752.
753.
754.
755.
756.
757.
758.
759.
760.
761.
762.
763.
764.
765.
create or alter procedure calc_item_summa0 (
id_firma integer,
d1 date,
d2 date,
mand_cat integer,
bl_cat integer,
id_nprice integer = null,
ost integer = 1,
mand_cat2 integer = null)
returns (
id_session integer)
AS
declare variable vid1 int = 1; -- Выпуск
declare variable vid2 int = 2; -- Расход сырья по изделиям (рецепт)
declare variable vid3 int = 3; -- Закупка ГИ
declare variable vid4 int = 4; -- Переработка в сырье
declare variable vid5 int = 5; -- Расход по документам
declare variable vid6 int = 6; -- Переработка приход
declare variable vid7 int = 7; -- Переработка расход
declare variable vid8 int = 8; -- Остаток на начало
declare variable vid9 int = 9; -- Свернутые Выпуск + остатки + расход по изделию
declare variable vid10 int = 10; -- Реализация
declare variable vid11 int = 11; -- Н/р по реализации
declare variable vid12 int = 12; -- Остаток на конец
declare variable vid99 int = 99; -- Накладные расходы
declare variable vid20 int = 20; -- Готовые расчеты
declare variable vid22 int = 22; -- Прочие движения по ГИ (сведенный)
declare variable vid23 int = 23; -- Промежуточные расчеты (расчет расхода сырья)
declare variable vid24 int = 24; -- Промежуточные расчеты (расчет переработки)
declare variable vid25 int = 25; -- Промежуточные расчеты (перераспределение переработки)
declare variable id_item int;
declare variable items numeric(12,3);
declare variable summa numeric(12,2);
declare variable items0 numeric(12,3);
declare variable summa0 numeric(12,2);
declare variable summa1 numeric(12,2);
declare variable items1 numeric(12,3);
declare variable items2 numeric(12,3);
declare variable summa2 numeric(12,2);
declare variable items3 numeric(12,3);
declare variable items4 numeric(12,3);
declare variable items5 numeric(12,3);
declare variable id0 int;
declare variable id1 int;
declare variable k int;
declare variable n int;
declare variable type_v int;
begin
-- Получаем данные из документов
select id_session from calc_item_summa_save(:id_firma, :d1, :d2, :mand_cat, :bl_cat, :id_nprice, :ost, :mand_cat2)
into id_session;
-- распределение прочего расхода сырья
for select id_item, n1, n2, id from session_edit
where id_session = :id_session and vid = :vid5
into id_item, items0, summa0, id0 do
begin
items1 = 0; summa1 = 0;
select sum(n1+n4), sum(n2) from session_edit
where id_session = :id_session and vid = :vid2 and id_item = :id_item and i5 = 0
into items1, summa1;
if (items1 <> 0) then
begin
for select n1+n4, n2, id from session_edit
where id_session = :id_session and vid = :vid2 and id_item = :id_item and i5 = 0
order by 2, 1
into items, summa, id1 do
begin
items2 = 1e0 * items0 * items / items1;
if (items0 <> 0) then
summa2 = 1e0 * items2 * summa0 / items0;
else
summa2 = coalesce(1e0 * summa0 * summa / nullif(summa1, 0), 0);
update session_edit set
n5 = :items2,
n6 = :summa2
where id = :id1;
end
items2 = 0; summa2 = 0;
select sum(n5), sum(n6) from session_edit
where id_session = :id_session and vid = :vid2 and id_item = :id_item and i5 = 0
into items2, summa2;
if (items2 <> items0 or summa2 <> summa0) then
update session_edit set
n5 = n5 + :items0 - :items2,
n6 = n6 + :summa0 - :summa2
where id = :id1;
update session_edit set
i4 = 1
where id = :id0;
end
end
-- распределение стоимости расхода сырья вне рецептов
for select sum(n2) from session_edit
where id_session = :id_session and vid = :vid5 and i4 is null
having sum(n2) <> 0
into summa0 do
begin
summa1 = 0;
select sum(n2+n6) from session_edit
where id_session = :id_session and vid = :vid2
into summa1;
if (summa1 <> 0) then
update session_edit set
n7 = cast(1e0*(n2+n6)*:summa0/:summa1 as numeric(12,2))
where id_session = :id_session and vid = :vid2;
summa2 = 0;
select sum(n7) from session_edit where id_session = :id_session and vid = :vid2
into summa2;
if (summa2 <> summa0) then
update session_edit set
n7 = n7 + :summa0 - :summa2
where id_session = :id_session and vid = :vid2
order by n2 desc
rows 1;
update session_edit set
i4 = 1
where id_session = :id_session and i2 = :vid5;
end
-- Списываем остаток из расхода изделий
for select id, id_item, n1, n2 from session_edit s
where id_session = :id_session and vid = :vid8 and n1 > 0
into id0, id_item, items0, summa0 do
begin
items2 = 0;
select sum(n1) from session_edit
where id_session = :id_session and vid = :vid2 and id_item = :id_item
into items2;
-- Убираем из выпуска
items1 = minvalue(items0, items2);
if (items1 > 0) then
begin
summa1 = iif(items1 = items0, summa0, 1e0*items1*summa0/items0);
for select id, n1 from session_edit
where id_session = :id_session and vid = :vid2 and id_item = :id_item
order by 2
into id1, items do
begin
items = 1e0*items1*items/items2;
summa = 1e0*items*summa0/items0;
update session_edit set
n11 = :items,
n12 = :summa,
n1 = n1 - :items
where id = :id1;
end
select sum(n11), sum(n12) from session_edit
where id_session = :id_session and vid = :vid2 and id_item = :id_item
into items, summa;
if (items <> items1 or summa <> summa1) then
update session_edit set
n11 = n11 + :items1 - :items,
n12 = n12 + :summa1 - :summa,
n1 = n1 - :items1 + :items
where id = :id1;
update session_edit set
n3 = :items1,
n4 = :summa1
where id = :id0;
items0 = items0 - items1;
summa0 = summa0 - summa1;
end
if (items0 <> 0) then
begin
items2 = 0;
select sum(n1) from session_edit
where id_session = :id_session and vid = :vid7 and id_item = :id_item
into items2;
-- Убираем из переработки
items1 = minvalue(items0, items2);
if (items1 > 0) then
begin
summa1 = iif(items1 = items0, summa0, 1e0*items1*summa0/items0);
for select id, n1 from session_edit
where id_session = :id_session and vid = :vid7 and id_item = :id_item
order by 2
into id1, items do
begin
items = 1e0*items1*items/items2;
summa = 1e0*items*summa0/items0;
update session_edit set
n11 = :items,
n12 = :summa,
n1 = n1 - :items
where id = :id1;
end
select sum(n11), sum(n12) from session_edit
where id_session = :id_session and vid = :vid7 and id_item = :id_item
into items, summa;
if (items <> items1 or summa <> summa1) then
update session_edit set
n11 = n11 + :items1 - :items,
n12 = n12 + :summa1 - :summa,
n1 = n1 - :items1 + :items
where id = :id1;
update session_edit set
n5 = :items1,
n6 = :summa1
where id = :id0;
items0 = items0 - items1;
summa0 = summa0 - summa1;
end
end
end
-- Списываем закупку из расхода изделий
for select id, id_item, n1, n2 from session_edit s
where id_session = :id_session and vid = :vid3 and n1 > 0
into id0, id_item, items0, summa0 do
begin
items2 = 0;
select sum(n1) from session_edit
where id_session = :id_session and vid = :vid2 and id_item = :id_item
into items2;
-- Убираем из выпуска
items1 = minvalue(items0, items2);
if (items1 > 0) then
begin
summa1 = iif(items1 = items0, summa0, 1e0*items1*summa0/items0);
for select id, n1 from session_edit
where id_session = :id_session and vid = :vid2 and id_item = :id_item
order by 2
into id1, items do
begin
items = 1e0*items1*items/items2;
summa = 1e0*items*summa0/items0;
update session_edit set
n13 = :items,
n14 = :summa,
n1 = n1 - :items
where id = :id1;
end
select sum(n13), sum(n14) from session_edit
where id_session = :id_session and vid = :vid2 and id_item = :id_item
into items, summa;
if (items <> items1 or summa <> summa1) then
update session_edit set
n13 = n13 + :items1 - :items,
n14 = n14 + :summa1 - :summa,
n1 = n1 - :items1 + :items
where id = :id1;
update session_edit set
n3 = :items1,
n4 = :summa1
where id = :id0;
items0 = items0 - items1;
summa0 = summa0 - summa1;
end
if (items0 <> 0) then
begin
items2 = 0;
select sum(n1) from session_edit
where id_session = :id_session and vid = :vid7 and id_item = :id_item
into items2;
-- Убираем из переработки
items1 = minvalue(items0, items2);
if (items1 > 0) then
begin
summa1 = iif(items1 = items0, summa0, 1e0*items1*summa0/items0);
for select id, n1 from session_edit
where id_session = :id_session and vid = :vid7 and id_item = :id_item
order by 2
into id1, items do
begin
items = 1e0*items1*items/items2;
summa = 1e0*items*summa0/items0;
update session_edit set
n13 = :items,
n14 = :summa,
n1 = n1 - :items
where id = :id1;
end
select sum(n13), sum(n14) from session_edit
where id_session = :id_session and vid = :vid7 and id_item = :id_item
into items, summa;
if (items <> items1 or summa <> summa1) then
update session_edit set
n13 = n13 + :items1 - :items,
n14 = n14 + :summa1 - :summa,
n1 = n1 - :items1 + :items
where id = :id1;
update session_edit set
n5 = :items1,
n6 = :summa1
where id = :id0;
items0 = items0 - items1;
summa0 = summa0 - summa1;
end
end
end
insert into session_edit(id_session, vid, id_item, n1, n2, n3, n4, n5, n6, n7, n8, n9, n10, n16, n17, n18, n19)
select :id_session, :vid9, id_item, sum(items), sum(summa), sum(summa_inv), sum(summa_in), sum(items_in), sum(items_b),
sum(summa_b), sum(items_prih), sum(summa_prih), sum(items_p_in), sum(items_t), sum(summa_t), max(price_b), sum(items_end)
from (select id_item,
sum(n1+n4+n5) items,
sum(n2+n6+n7) summa,
sum(n6+n7) summa_inv,
null ITEMS_in,
null summa_in,
0 items_b,
0 summa_b,
0 items_prih,
0 summa_prih,
0 items_p_in,
0 items_t,
0 summa_t,
0 price_b,
0 items_end
from session_edit
where id_session = :id_session and vid = :vid2
group by 1
union all
select id_item, 0, 0, 0, n1, n2, 0, 0, 0, 0, 0, 0, 0, 0, 0 from session_edit where id_session = :id_session and vid = :vid1 -- приход ГИ
union all
select id_item, 0, 0, 0, 0, 0, 0, 0, n1, n2, 0, 0, 0, 0, 0 from session_edit where id_session = :id_session and vid = :vid3 -- закупка ГИ
union all
select id_item, 0, 0, 0, 0, 0, n1, n2, 0, 0, 0, 0, 0, n3, 0 from session_edit where id_session = :id_session and vid = :vid8 -- остаток ГИ
union all
select id_item, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, n1 from session_edit where id_session = :id_session and vid = :vid12 -- остаток ГИ (на конец)
union all
select id_item, 0, 0, 0, 0, 0, 0, 0, 0, 0, n1, 0, 0, 0, 0 from session_edit where id_session = :id_session and vid = :vid6 -- переработка приход ГИ
union all
select id_item, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, n1, n2, 0, 0 from session_edit where id_session = :id_session and vid = :vid4
union all
select distinct id_item, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 from session_edit where id_session = :id_session and vid in (:vid7, :vid10, :vid22))
group by id_item;
insert into session_edit(id_session, vid, id_item, n1, n2, n3, n5)
with recursive
-- Расход по поличеству сырья на изделие
rashod as (
select id_item,
i1,
sum(n1+n4+n5) items
from session_edit
where id_session = :id_session and vid = :vid2
group by 1, 2),
-- Расход сырья по изделиям + остатки
prices0 as (
select id_item,
n1 items,
n2 summa,
n3 summa_inv,
n5 items_in
from session_edit
where id_session = :id_session and vid = :vid9),
prices as (
select id_item, items, summa, summa_inv, items_in from prices0
union all
select p0.id_item,
p0.items_in,
1e0 * r.items * p.summa / nullif(p.items, 0),
1e0 * r.items * p.summa_inv / nullif(p.items, 0),
p0.items_in
from prices0 p0
join rashod r on r.i1 = p0.id_item
join prices p on p.id_item = r.id_item)
select :id_session, :vid23, id_item,
min(items_in) items_in,
sum(summa) summa,
sum(summa_inv) summa_inv,
min(items) items
from prices
group by id_item
having min(items_in) is not null;
insert into session_edit(id_session, vid, id_item, n1, n2, n3, n5, i1, i2)
with recursive
-- Расход по поличеству сырья на изделие
rashod as (
select id_item,
i1,
sum(n1+n4+n5) items,
sum(n11) items_b,
sum(n13) items_prih
from session_edit
where id_session = :id_session and vid = :vid2
group by 1, 2),
-- Расход сырья по изделиям + остатки
prices0 as (
select s.id_item,
s.n1 items_in,
s.n2 summa,
s.n3 summa_inv,
s.n5 items,
s9.n6 items_b,
s9.n7 summa_b,
s9.n8 items_prih,
s9.n9 summa_prih,
s9.n10 items_p_in,
s9.n18 price_b
from session_edit s
join session_edit s9 on s9.id_session = s.id_session and s.id_item = s9.id_item and s9.vid = :vid9
where s.id_session = :id_session and s.vid = :vid23),
prices as (
select id_item, cast(null as int) i1, 0.0000 summa, 0.0000 summa_inv, items_b, summa_b, items_in, summa summa_in, summa_inv summa_inv_in, items_prih, summa_prih, 0.0000 items_r, 0.0000 summa_r, price_b from prices0
union all
select p0.id_item,
p.id_item,
1e0*r.items*p.summa_in/nullif(p.items_in, 0),
1e0*r.items*p.summa_inv_in/nullif(p.items_in, 0),
p0.items_b,
p0.summa_b,
0,
0,
0,
p0.items_prih,
p0.summa_prih,
r.items_b + r.items_prih + r.items,
coalesce(1e0*r.items_b * p.summa_b / nullif(p.items_b, 0), 0)+
coalesce(1e0*r.items_prih * p.summa_prih / nullif(p.items_prih, 0), 0)+
coalesce(1e0*r.items * p.summa_r / nullif(p.items_in, 0), 0),
p0.price_b
from prices0 p0
join rashod r on r.i1 = p0.id_item
join prices p on p.id_item = r.id_item),
prices2 as (
select id_item, items_in items, summa_in summa, summa_inv_in summa_inv, cast(summa_r as numeric(12,2)) summa_r, items_prih, summa_prih, items_b, summa_b, price_b, 1 vid from prices
union all
select i1, -items_r, -summa, -summa_inv, -cast(summa_r as numeric(12,2)), null, null, null, null, null, -1 from prices where i1 is not null),
vipusk as (
select id_item,
sum(items) items,
sum(summa) summa,
sum(summa_inv) summa_inv,
sum(summa_r) summa_r,
coalesce(nullif(coalesce(sum(summa+summa_r)/nullif(sum(items), 0), 0), 0), max(price_b)) price_ceh,
min(items_b) items_b,
min(summa_b) summa_b,
min(items_prih) items_prih,
min(summa_prih) summa_prih
from prices2 p
group by 1),
-- Расход по переработке
rashod2 as (
select id_item,
i1,
n1 items,
n2 koef,
n11 items_b,
n13 items_prih
from session_edit
where id_session = :id_session and vid = :vid7),
-- Увеличение себестоимости по переработке
pererabotka as (
select r.i1 id_item,
r.id_item id_item2,
min(p0.items_p_in) items,
sum(1e0 * r.items * r.koef * v.summa /nullif(v.items, 0)) summa,
sum(1e0 * r.items * r.koef * v.summa_inv / nullif(v.items, 0)) summa_inv,
sum(r.items +r.items_b + r.items_prih) items_r,
cast(coalesce(sum(1e0 * r.items * r.koef * v.summa_r /nullif(v.items, 0)), 0)+
coalesce(sum(1e0 * r.items_b * r.koef * v.summa_b /nullif(v.items_b, 0)), 0)+
coalesce(sum(1e0 * r.items_prih * r.koef * v.summa_prih /nullif(v.items_prih, 0)), 0) +
coalesce(sum(iif(v.items = 0, 1e0 * r.items * r.koef * v.price_ceh, 0)), 0) as numeric(12,2)) summa_r
from rashod2 r
join vipusk v on r.id_item = v.id_item
left join prices0 p0 on p0.id_item = r.i1
group by 1, 2),
total as (
select id_item, items, summa, summa_inv, summa_r, cast(null as int) id_item2, 0 vid
from vipusk
union all
select id_item, items, summa, summa_inv, summa_r, id_item2, 1 vid
from pererabotka
union all
select id_item2, -items_r, -summa, -summa_inv, -summa_r, id_item, 2 vid
from pererabotka)
select :id_session, :vid24,
id_item,
items n1,
summa n2,
summa_inv n3,
summa_r n5,
id_item2 i1,
vid i2
from total;
insert into session_edit(id_session, vid, id_item, n1, n2, n3, n5, i1, i2)
with
total as (
select id_item,
max(iif(i2 = 1, n1, 0)) + sum(iif(i2 = 1, 0, n1)) items,
sum(n2) summa,
sum(n3) summa_inv,
sum(n5) summa_r,
'~'||cast(substring(list(distinct i1, '~') from 1 for 20000) as varchar(20000)) sid_item2
from session_edit
where id_session = :id_session and vid = :vid24
group by 1),
total1 as (
select id_item, summa, summa_inv, summa_r,
(select first 1 t.id_item from total t where t1.sid_item2 containing '~'||t.id_item||'~' and t.items <> 0 order by t.summa + t.summa_r desc) id_item2
from total t1
where items = 0 and (summa <> 0 or summa_inv <> 0 or summa_r <> 0)),
total2 as (
select id_item2 id_item, summa, summa_inv, summa_r from total1
where id_item2 is not null
union all
select id_item, -summa, -summa_inv, -summa_r from total1
where id_item2 is not null)
select :id_session, :vid25,
id_item,
cast(null as numeric(12, 3)) items,
summa,
summa_inv n3,
summa_r n5,
cast(null as int) id_item2,
3 i2
from total2;
insert into session_edit(id_session, vid, id_item, n1, n2, n3, n6, n7, n8, n9, n10, n11, n12, n13, n14)
with
total as (
select s.id_item,
max(iif(s.i2 = 1, s.n1, 0)) + sum(iif(s.i2 = 1, 0, s.n1)) items,
sum(s.n2) summa,
sum(s.n3) summa_inv,
sum(s.n5) summa_r,
min(s9.n6) items_b,
min(s9.n7) summa_b,
min(s9.n8) items_prih,
min(s9.n9) summa_prih,
min(s9.n10) items_p_in,
min(s9.n16) items_t,
min(s9.n17) summa_t,
min(s9.n18) price_b,
min(s9.n19) items_end
from session_edit s
join session_edit s9 on s9.id_session = s.id_session and s.id_item = s9.id_item and s9.vid = :vid9
where s.id_session = :id_session and s.vid in (:vid24, :vid25)
group by 1)
select :id_session, :vid20,
id_item,
cast(items as numeric(12,3)) n1,
cast(summa as numeric(12,2)) n2,
cast(summa_inv as numeric(12,2)) n3,
cast(summa_prih as numeric(12,2)) n6,
cast(items_prih as numeric(12,3)) n7,
cast(summa_b as numeric(12,2)) n8,
cast(items_b as numeric(12,3)) n9,
cast(summa_r as numeric(12,2)) n10,
cast(items_t as numeric(12,3)) n11,
cast(summa_t as numeric(12,2)) n12,
cast(price_b as numeric(12,4)) n13,
cast(items_end as numeric(12,3)) n14
from total;
merge into session_edit s
using (select s.id, i.group_id i2 from session_edit s
left join item i on i.id = s.id_item
where s.id_session = :id_session and s.vid in (:vid20, :vid23)) c on c.id = s.id
when matched then
update set i2 = c.i2, n4 = 0;
-- Выравниваем сырье
for select sum(n2+n6+n7) summa,
sum(n6+n7) summa_inv
from session_edit
where id_session = :id_session and vid = :vid2
into summa, summa1 do
for select sum(n2) summa, sum(n3) summa_inv
from session_edit
where id_session = :id_session and vid = :vid20
into items, items1 do
update session_edit set
n2 = n2 + :summa - :items,
n3 = n3 + :summa1 - :items1
where id_session = :id_session and vid = :vid20
order by n2 desc
rows 1;
-- распределение переменных Н/Р (пропорционально сумме сырья)
for select sum(n1) from session_edit
where id_session = :id_session and vid = :vid99 and i1 = 2
having sum(n1) <> 0
into summa0 do
begin
summa1 = 0;
select sum(n2) from session_edit where id_session = :id_session and vid = :vid20 and i2 = 2
into summa1;
summa1 = nullif(summa1, 0);
update session_edit set
n4 = cast(coalesce(1e0 * :summa0 * n2 / :summa1, 0) as numeric(12,2))
where id_session = :id_session and vid in (:vid20, :vid23) and i2 = 2;
summa2 = 0;
select sum(n4) from session_edit where id_session = :id_session and vid = :vid20 and i2 = 2
into summa2;
if (summa2 <> summa0) then
update session_edit set
n4 = n4 + :summa0 - :summa2
where id_session = :id_session and vid = :vid20 and i2 = 2
order by n4 desc
rows 1;
end
update session_edit set
n4 = 0
where id_session = :id_session and vid in (:vid20, :vid23) and i2 = 5;
-- распределение Н/Р по изделиям (пропорционально выбранному виду)
for select iif(i1 = 3, 2, 1) vid, sum(n1) from session_edit
where id_session = :id_session and vid = :vid99 and n1 <> 0 and i1 in (0,1,3)
group by 1
having sum(n1) <> 0
into k, summa0 do
for select num from get_rows(3)
into n do
begin
summa1 = 0;
select sum(decode(:n, 1, n2, 2, n5, 3, n6)) from session_edit
where id_session = :id_session and vid = :vid10
into summa1;
type_v = (n-1)*2+k;
if (summa1 <> 0) then
insert into session_edit(id_session, vid, id_item, i1, n1)
select id_session, :vid11, id_item, :type_v, cast(1e0*decode(:n, 1, n2, 2, n5, 3, n6)*:summa0/:summa1 as numeric(12,2))
from session_edit
where id_session = :id_session and vid = :vid10;
summa2 = 0;
select sum(n1) from session_edit where id_session = :id_session and vid = :vid11 and i1 = :type_v
into summa2;
if (summa2 <> summa0) then
update session_edit set
n1 = n1 + :summa0 - :summa2
where id_session = :id_session and vid = :vid11 and i1 = :type_v
order by n1 desc
rows 1;
end
-- Распределение Н/р по ГИ
merge into session_edit s
using (select id_item,
sum(iif(i1 = 1, n1, 0)) n7,
sum(iif(i1 = 2, n1, 0)) n8,
sum(iif(i1 = 3, n1, 0)) n9,
sum(iif(i1 = 4, n1, 0)) n10,
sum(iif(i1 = 5, n1, 0)) n11,
sum(iif(i1 = 6, n1, 0)) n12
from session_edit
where id_session = :id_session and vid = :vid11
group by 1) c on s.id_session = :id_session and s.id_item = c.id_item and s.vid = :vid10
when matched then
update set n7 = c.n7,
n8 = c.n8,
n9 = c.n9,
n10 = c.n10,
n11 = c.n11,
n12 = c.n12;
-- Расчет остатка на конец
if (ost = 1) then
begin
for select s.id_item,
s23.n1 items,
s.n1-s23.n1 items_r,
s.n1,
s.n7 items_prih,
s.n9 items_b,
s.n14 items_end,
s12.n2 summa_end,
s.id
from session_edit s
left join session_edit s23 on s23.id_session = s.id_session and s23.id_item = s.id_item and s23.vid = :vid23
left join session_edit s12 on s12.id_session = s.id_session and s12.id_item = s.id_item and s12.vid = :vid12
where s.id_session = :id_session and s.vid = :vid20
into id_item, items, items4, items5, items1, items0, items2, summa, id0 do
begin
items3 = maxvalue(items2, 0);
if (items3 = 0) then
begin
items = 0;
items4 = 0;
end else
begin
items5 = minvalue(items3, items5);
items3 = items3 - items5;
if (items5 <> items + items4) then
begin
items = maxvalue(minvalue(items5, items), 0);
items4 = items5 - items;
end
end
items1 = maxvalue(minvalue(items3, items1), 0);
items3 = items3 - items1;
items0 = maxvalue(minvalue(items3, items0), 0);
items3 = items3 - items0;
merge into session_edit s
using (select s.id, :items2 items_end,
cast(coalesce(:summa,
coalesce(1e0 * :items * (s23.n2 + s23.n4) / nullif(s23.n1, 0), 0) +
coalesce(1e0 * :items4 * (s23.n2 + s23.n4 - s.n2 - s.n10 - s.n4) / nullif(s23.n1 - s.n1, 0), 0) +
coalesce(1e0 * :items1 * s.n6 / nullif(s.n7, 0), 0) +
coalesce(1e0 * :items0 * s.n8 / nullif(s.n9, 0), 0) +
coalesce(1e0 * :items3 * s.n13, 0)) as numeric(12,2)) summa_end
from session_edit s
left join session_edit s23 on s23.id_session = s.id_session and s23.id_item = s.id_item and s23.vid = :vid23
where s.id = :id0) c on c.id = s.id
when matched then
update set
n14 = items_end,
n15 = summa_end;
end
end else
update session_edit set
n14 = 0,
n15 = 0
where id_session = :id_session and vid = :vid20;
suspend;
end
|
