· 7 years ago · Dec 18, 2018, 07:54 AM
1-- Таблица оÑтатков
2create table DocStrOst (
3 KodSkl smallint not null,
4 PNT int not null,
5 KolOv decimal(18,5),
6 KolM int,
7 KolZ decimal (18,5),
8 KolF decimal (18,5),
9 KolKzp decimal (18,5),
10 primary key(PNT, KodSkl)
11);
12GO
13------------------------------------
14-- Триггера Ð²ÐµÐ´ÐµÐ½Ð¸Ñ Ñ‚Ð°Ð±Ð»Ð¸Ñ†Ñ‹ оÑтатков Ð´Ð»Ñ DocCaption
15CREATE Trigger DocCaption_ost_tr_d
16ON DocCaption
17FOR delete
18AS
19 update D
20 set KolOv=D.KolOv-N.KolOv,KolM=D.KolM-N.KolM,KolZ=D.KolZ-N.KolZ,KolF=D.KolF-N.KolF,KolKzp=D.KolKzp-N.KolKzp
21 from DocStrOst D, (
22 select D.KodSkl,I.PNT,
23 IsNull(sum(IsNull(KolOv,0)*z),0) KolOv,IsNull(sum(IsNull(KolM,0)*z),0) KolM,
24 IsNull(sum(IsNull(KolZ,0)*z),0) KolZ,IsNull(sum(IsNull(KolF,0)*z),0) KolF,
25 IsNull(sum(IsNull(KolKzp,0)*z),0) KolKzp
26 from deleted D, DocString I,
27 ( select 1 vid, 1 z union all select 5, 1 union all select 2, -1 union all select 3, -1 ) Z
28 where D.IdDoc=I.IdDoc and Z.vid=D.VidDoc
29 group by D.KodSkl,I.PNT
30 ) N
31 where N.KodSkl=D.KodSkl and N.PNT=D.PNT;
32GO
33----
34CREATE Trigger DocCaption_ost_tr_i
35ON DocCaption
36FOR insert
37AS
38 update D
39 set KolOv=D.KolOv+N.KolOv,KolM=D.KolM+N.KolM,KolZ=D.KolZ+N.KolZ,KolF=D.KolF+N.KolF,KolKzp=D.KolKzp+N.KolKzp
40 from DocStrOst D, (
41 select D.KodSkl,I.PNT,
42 IsNull(sum(IsNull(KolOv,0)*z),0) KolOv,IsNull(sum(IsNull(KolM,0)*z),0) KolM,
43 IsNull(sum(IsNull(KolZ,0)*z),0) KolZ,IsNull(sum(IsNull(KolF,0)*z),0) KolF,
44 IsNull(sum(IsNull(KolKzp,0)*z),0) KolKzp
45 from inserted D, DocString I,
46 ( select 1 vid, 1 z union all select 5, 1 union all select 2, -1 union all select 3, -1 ) Z
47 where D.IdDoc=I.IdDoc and Z.vid=D.VidDoc
48 group by D.KodSkl,I.PNT
49 ) N
50 where N.KodSkl=D.KodSkl and N.PNT=D.PNT;
51 insert into DocStrOst(KodSkl,PNT,KolOv,KolM,KolZ,KolF,KolKzp)
52 select D.KodSkl,I.PNT,
53 IsNull(sum(IsNull(I.KolOv,0)*z),0) KolOv,IsNull(sum(IsNull(I.KolM,0)*z),0) KolM,
54 IsNull(sum(IsNull(I.KolZ,0)*z),0) KolZ,IsNull(sum(IsNull(I.KolF,0)*z),0) KolF,
55 IsNull(sum(IsNull(I.KolKzp,0)*z),0) KolKzp
56 from inserted D
57 join DocString I on D.IdDoc=I.IdDoc
58 join ( select 1 vid, 1 z union all select 5, 1 union all select 2, -1 union all select 3, -1 ) Z
59 on Z.vid=D.VidDoc
60 left join DocStrOst O on O.KodSkl=D.KodSkl and O.PNT=I.PNT
61 where O.PNT is null
62 group by D.KodSkl,I.PNT;
63GO
64------------------------------------
65-- Триггера Ð²ÐµÐ´ÐµÐ½Ð¸Ñ Ñ‚Ð°Ð±Ð»Ð¸Ñ†Ñ‹ оÑтатков Ð´Ð»Ñ DocString
66CREATE Trigger DocString_ost_tr_i
67ON DocString
68FOR insert
69AS
70 update D
71 set KolOv=D.KolOv+N.KolOv,KolM=D.KolM+N.KolM,KolZ=D.KolZ+N.KolZ,KolF=D.KolF+N.KolF,KolKzp=D.KolKzp+N.KolKzp
72 from DocStrOst D, (
73 select D.KodSkl,I.PNT,
74 IsNull(sum(IsNull(KolOv,0)*z*OP),0) KolOv,IsNull(sum(IsNull(KolM,0)*z*OP),0) KolM,
75 IsNull(sum(IsNull(KolZ,0)*z*OP),0) KolZ,IsNull(sum(IsNull(KolF,0)*z*OP),0) KolF,
76 IsNull(sum(IsNull(KolKzp,0)*z*OP),0) KolKzp
77 from DocCaption D,
78 (select 1 as OP, IdDoc, PNT, KolOv,KolM,KolZ,KolF,KolKzp from inserted) I,
79 ( select 1 vid, 1 z union all select 5, 1 union all select 2, -1 union all select 3, -1 ) Z
80 where D.IdDoc=I.IdDoc and Z.vid=D.VidDoc
81 group by D.KodSkl,I.PNT
82 ) N
83 where N.KodSkl=D.KodSkl and N.PNT=D.PNT;
84 insert into DocStrOst(KodSkl,PNT,KolOv,KolM,KolZ,KolF,KolKzp)
85 select * from (
86 select D.KodSkl,I.PNT,
87 IsNull(sum(IsNull(KolOv,0)*z*OP),0) KolOv,IsNull(sum(IsNull(KolM,0)*z*OP),0) KolM,
88 IsNull(sum(IsNull(KolZ,0)*z*OP),0) KolZ,IsNull(sum(IsNull(KolF,0)*z*OP),0) KolF,
89 IsNull(sum(IsNull(KolKzp,0)*z*OP),0) KolKzp
90 from DocCaption D,
91 (select 1 as OP, IdDoc, PNT, KolOv,KolM,KolZ,KolF,KolKzp from inserted) I,
92 ( select 1 vid, 1 z union all select 5, 1 union all select 2, -1 union all select 3, -1 ) Z
93 where D.IdDoc=I.IdDoc and Z.vid=D.VidDoc
94 group by D.KodSkl,I.PNT
95 ) N
96 where not exists(select 1 from DocStrOst D where N.KodSkl=D.KodSkl and N.PNT=D.PNT);
97GO
98----
99CREATE Trigger DocString_ost_tr_u
100ON DocString
101FOR update
102AS
103 IF( UPDATE(KolOv) OR UPDATE(KolM) OR UPDATE(KolZ) OR UPDATE(KolF) OR UPDATE(KolKzp) )
104 BEGIN
105 update D
106 set KolOv=D.KolOv+N.KolOv,KolM=D.KolM+N.KolM,KolZ=D.KolZ+N.KolZ,KolF=D.KolF+N.KolF,KolKzp=D.KolKzp+N.KolKzp
107 from DocStrOst D, (
108 select D.KodSkl,I.PNT,
109 IsNull(sum(IsNull(KolOv,0)*z*OP),0) KolOv,IsNull(sum(IsNull(KolM,0)*z*OP),0) KolM,
110 IsNull(sum(IsNull(KolZ,0)*z*OP),0) KolZ,IsNull(sum(IsNull(KolF,0)*z*OP),0) KolF,
111 IsNull(sum(IsNull(KolKzp,0)*z*OP),0) KolKzp
112 from DocCaption D,
113 (select 1 as OP, IdDoc, PNT, KolOv,KolM,KolZ,KolF,KolKzp from inserted
114 union all
115 select -1 , IdDoc, PNT, KolOv,KolM,KolZ,KolF,KolKzp from deleted
116 ) I,
117 ( select 1 vid, 1 z union all select 5, 1 union all select 2, -1 union all select 3, -1 ) Z
118 where D.IdDoc=I.IdDoc and Z.vid=D.VidDoc
119 group by D.KodSkl,I.PNT
120 ) N
121 where N.KodSkl=D.KodSkl and N.PNT=D.PNT;
122 END
123GO
124----
125CREATE Trigger DocString_ost_tr_d
126ON DocString
127FOR delete
128AS
129 update D
130 set KolOv=D.KolOv+N.KolOv,KolM=D.KolM+N.KolM,KolZ=D.KolZ+N.KolZ,KolF=D.KolF+N.KolF,KolKzp=D.KolKzp+N.KolKzp
131 from DocStrOst D, (
132 select D.KodSkl,I.PNT,
133 IsNull(sum(IsNull(KolOv,0)*z*OP),0) KolOv,IsNull(sum(IsNull(KolM,0)*z*OP),0) KolM,
134 IsNull(sum(IsNull(KolZ,0)*z*OP),0) KolZ,IsNull(sum(IsNull(KolF,0)*z*OP),0) KolF,
135 IsNull(sum(IsNull(KolKzp,0)*z*OP),0) KolKzp
136 from DocCaption D,
137 (select -1 OP, IdDoc, PNT, KolOv,KolM,KolZ,KolF,KolKzp from deleted) I,
138 ( select 1 vid, 1 z union all select 5, 1 union all select 2, -1 union all select 3, -1 ) Z
139 where D.IdDoc=I.IdDoc and Z.vid=D.VidDoc
140 group by D.KodSkl,I.PNT
141 ) N
142 where N.KodSkl=D.KodSkl and N.PNT=D.PNT;
143GO
144-----------------------------
145--- Первичное заполнение таблицы оÑтатков:
146delete DocStrOst; -- Очищаем Ñтарые оÑтатки
147insert into DocStrOst(KodSkl,PNT,KolOv,KolM,KolZ,KolF,KolKzp) -- И переÑчитываем по новой
148 select * from (
149 select D.KodSkl,I.PNT,
150 IsNull(sum(KolOv*z),0) KolOv,IsNull(sum(KolM*z),0) KolM,
151 IsNull(sum(KolZ*z),0) KolZ,IsNull(sum(KolF*z),0) KolF,IsNull(sum(KolKzp*z),0) KolKzp
152 from DocCaption D, DocString I,
153 ( select 1 vid, 1 z union all select 5, 1 union all select 2, -1 union all select 3, -1 ) Z
154 where D.IdDoc=I.IdDoc and Z.vid=D.VidDoc
155 group by D.KodSkl,I.PNT
156 ) N
157 where not exists(select 1 from DocStrOst D where N.KodSkl=D.KodSkl and N.PNT=D.PNT)
158--------------------------------
159-- ÐÐ¾Ð²Ð°Ñ Ð¿Ñ€Ð¾Ñ†ÐµÐ´ÑƒÑ€Ð° Ð¿Ð¾Ð»ÑƒÑ‡ÐµÐ½Ð¸Ñ Ð¾Ñтатков
160CREATE PROCEDURE dbo.CalcOst
161 @KodSkl smallint, @PNT integer, @Ostatok decimal(18,5) OUTPUT
162AS
163 declare @prod decimal(18,5), @KodPredpr int
164 set @Prod=0
165 set @Ostatok=IsNull(
166 (select max(KolF) from DocStrOst where KodSkl=@KodSkl and PNT=@PNT)
167 ,0)
168if exists (select * from master.dbo.sysdatabases
169 where name ='Ditron')
170begin
171if ((select count(*) from tbPredpr where idPredpr =@KodSkl and idGrPredpr =
172 ( select idGrPredpr from tbGrPredpr where nameGrPredpr = 'Ñклад-магазин'))=1) and
173 ((select count(*) from tbNastrSchet where IdPredpr=@KodSkl)=1)
174begin
175 set @kodPredpr=(select kodpredpr from tbpredpr where idpredpr=@kodskl)
176 set @prod=IsNull(( Select Sum(Ditron.dbo.tbProdaja.Kol)
177 From Ditron.dbo.tbProdaja
178 Where Ditron.dbo.tbProdaja.KodPredpr=@KodPredpr and
179 Ditron.dbo.tbProdaja.PNT=@PNT ),0)
180 set @Ostatok=@Ostatok-@prod
181end
182end
183GO
184---------------------------------------
185--- Сверка оÑтатков
186 select * from (
187 select D.KodSkl,I.PNT,
188 IsNull(sum(KolOv*z),0) KolOv,IsNull(sum(KolM*z),0) KolM,
189 IsNull(sum(KolZ*z),0) KolZ,IsNull(sum(KolF*z),0) KolF,IsNull(sum(KolKzp*z),0) KolKzp
190 from DocCaption D, DocString I,
191 ( select 1 vid, 1 z union all select 5, 1 union all select 2, -1 union all select 3, -1 ) Z
192 where D.IdDoc=I.IdDoc and Z.vid=D.VidDoc
193 group by D.KodSkl,I.PNT
194 ) N
195 full join DocStrOst O on O.KodSkl=N.KodSkl and O.PNT=N.PNT
196 where O.PNT is null or (N.PNT is null and O.KolF+O.KolKzp+O.KolM+O.KolOv+O.KolZ>0)
197 or N.KolZ!=O.KolZ or N.KolOv!=O.KolOv or N.KolF!=O.KolF or N.KolKzp!=O.KolKzp or N.KolM!=O.KolM