Untitled

-- Таблица остатков
create table DocStrOst (
  KodSkl smallint not null,
  PNT int not null,
  KolOv decimal(18,5),
  KolM  int,
  KolZ  decimal (18,5),
  KolF  decimal (18,5),
  KolKzp decimal (18,5),
  primary key(PNT, KodSkl)
);
GO
------------------------------------
-- Триггера ведения таблицы остатков для DocCaption
CREATE Trigger DocCaption_ost_tr_d
ON DocCaption
FOR delete
AS
   update D
      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
     from DocStrOst D, (
     select D.KodSkl,I.PNT,
            IsNull(sum(IsNull(KolOv,0)*z),0) KolOv,IsNull(sum(IsNull(KolM,0)*z),0) KolM,
            IsNull(sum(IsNull(KolZ,0)*z),0) KolZ,IsNull(sum(IsNull(KolF,0)*z),0) KolF,
            IsNull(sum(IsNull(KolKzp,0)*z),0) KolKzp
       from deleted D, DocString I,
            ( select 1 vid, 1 z union all select 5, 1 union all select 2, -1 union all select 3, -1 ) Z
       where D.IdDoc=I.IdDoc and Z.vid=D.VidDoc
       group by D.KodSkl,I.PNT
  ) N
  where N.KodSkl=D.KodSkl and N.PNT=D.PNT;
GO
----
CREATE Trigger DocCaption_ost_tr_i
ON DocCaption
FOR insert
AS
   update D
      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
     from DocStrOst D, (
     select D.KodSkl,I.PNT,
            IsNull(sum(IsNull(KolOv,0)*z),0) KolOv,IsNull(sum(IsNull(KolM,0)*z),0) KolM,
            IsNull(sum(IsNull(KolZ,0)*z),0) KolZ,IsNull(sum(IsNull(KolF,0)*z),0) KolF,
            IsNull(sum(IsNull(KolKzp,0)*z),0) KolKzp
       from inserted D, DocString I,
            ( select 1 vid, 1 z union all select 5, 1 union all select 2, -1 union all select 3, -1 ) Z
       where D.IdDoc=I.IdDoc and Z.vid=D.VidDoc
       group by D.KodSkl,I.PNT
  ) N
  where N.KodSkl=D.KodSkl and N.PNT=D.PNT;
  insert into DocStrOst(KodSkl,PNT,KolOv,KolM,KolZ,KolF,KolKzp)
     select D.KodSkl,I.PNT,
            IsNull(sum(IsNull(I.KolOv,0)*z),0) KolOv,IsNull(sum(IsNull(I.KolM,0)*z),0) KolM,
            IsNull(sum(IsNull(I.KolZ,0)*z),0) KolZ,IsNull(sum(IsNull(I.KolF,0)*z),0) KolF,
            IsNull(sum(IsNull(I.KolKzp,0)*z),0) KolKzp
       from inserted D
       join DocString I on D.IdDoc=I.IdDoc
       join ( select 1 vid, 1 z union all select 5, 1 union all select 2, -1 union all select 3, -1 ) Z
         on Z.vid=D.VidDoc
       left join DocStrOst O on O.KodSkl=D.KodSkl and O.PNT=I.PNT
       where O.PNT is null
       group by D.KodSkl,I.PNT;
GO
------------------------------------
-- Триггера ведения таблицы остатков для DocString
CREATE Trigger DocString_ost_tr_i
ON DocString
FOR insert
AS
  update D
     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
    from DocStrOst D, (
     select D.KodSkl,I.PNT,
            IsNull(sum(IsNull(KolOv,0)*z*OP),0) KolOv,IsNull(sum(IsNull(KolM,0)*z*OP),0) KolM,
        IsNull(sum(IsNull(KolZ,0)*z*OP),0) KolZ,IsNull(sum(IsNull(KolF,0)*z*OP),0) KolF,
                IsNull(sum(IsNull(KolKzp,0)*z*OP),0) KolKzp
       from DocCaption D,
            (select 1 as OP, IdDoc, PNT, KolOv,KolM,KolZ,KolF,KolKzp  from inserted) I,
                ( select 1 vid, 1 z union all select 5, 1 union all select 2, -1 union all select 3, -1 ) Z
       where D.IdDoc=I.IdDoc and Z.vid=D.VidDoc
       group by D.KodSkl,I.PNT
   ) N
  where N.KodSkl=D.KodSkl and N.PNT=D.PNT;
 insert into DocStrOst(KodSkl,PNT,KolOv,KolM,KolZ,KolF,KolKzp)
  select * from (
     select D.KodSkl,I.PNT,
            IsNull(sum(IsNull(KolOv,0)*z*OP),0) KolOv,IsNull(sum(IsNull(KolM,0)*z*OP),0) KolM,
        IsNull(sum(IsNull(KolZ,0)*z*OP),0) KolZ,IsNull(sum(IsNull(KolF,0)*z*OP),0) KolF,
                IsNull(sum(IsNull(KolKzp,0)*z*OP),0) KolKzp
       from DocCaption D,
            (select 1 as OP, IdDoc, PNT, KolOv,KolM,KolZ,KolF,KolKzp  from inserted) I,
                ( select 1 vid, 1 z union all select 5, 1 union all select 2, -1 union all select 3, -1 ) Z
       where D.IdDoc=I.IdDoc and Z.vid=D.VidDoc
       group by D.KodSkl,I.PNT
  ) N
 where not exists(select 1 from DocStrOst D where N.KodSkl=D.KodSkl and N.PNT=D.PNT);
GO
----
CREATE Trigger DocString_ost_tr_u
ON DocString
FOR update
AS
 IF( UPDATE(KolOv) OR UPDATE(KolM) OR UPDATE(KolZ) OR UPDATE(KolF) OR UPDATE(KolKzp) )
 BEGIN
  update D
     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
    from DocStrOst D, (
     select D.KodSkl,I.PNT,
            IsNull(sum(IsNull(KolOv,0)*z*OP),0) KolOv,IsNull(sum(IsNull(KolM,0)*z*OP),0) KolM,
        IsNull(sum(IsNull(KolZ,0)*z*OP),0) KolZ,IsNull(sum(IsNull(KolF,0)*z*OP),0) KolF,
                IsNull(sum(IsNull(KolKzp,0)*z*OP),0) KolKzp
       from DocCaption D,
            (select 1 as OP, IdDoc, PNT, KolOv,KolM,KolZ,KolF,KolKzp  from inserted
             union all
             select -1     , IdDoc, PNT, KolOv,KolM,KolZ,KolF,KolKzp  from deleted
                ) I,
                ( select 1 vid, 1 z union all select 5, 1 union all select 2, -1 union all select 3, -1 ) Z
       where D.IdDoc=I.IdDoc and Z.vid=D.VidDoc
       group by D.KodSkl,I.PNT
   ) N
  where N.KodSkl=D.KodSkl and N.PNT=D.PNT;
 END
GO
----
CREATE Trigger DocString_ost_tr_d
ON DocString
FOR delete
AS
  update D
     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
    from DocStrOst D, (
     select D.KodSkl,I.PNT,
            IsNull(sum(IsNull(KolOv,0)*z*OP),0) KolOv,IsNull(sum(IsNull(KolM,0)*z*OP),0) KolM,
        IsNull(sum(IsNull(KolZ,0)*z*OP),0) KolZ,IsNull(sum(IsNull(KolF,0)*z*OP),0) KolF,
                IsNull(sum(IsNull(KolKzp,0)*z*OP),0) KolKzp
       from DocCaption D,
            (select -1 OP, IdDoc, PNT, KolOv,KolM,KolZ,KolF,KolKzp  from deleted) I,
            ( select 1 vid, 1 z union all select 5, 1 union all select 2, -1 union all select 3, -1 ) Z
       where D.IdDoc=I.IdDoc and Z.vid=D.VidDoc
       group by D.KodSkl,I.PNT
   ) N
  where N.KodSkl=D.KodSkl and N.PNT=D.PNT;
GO
-----------------------------
--- Первичное заполнение таблицы остатков:
delete DocStrOst;  -- Очищаем старые остатки
insert into DocStrOst(KodSkl,PNT,KolOv,KolM,KolZ,KolF,KolKzp)  -- И пересчитываем по новой
  select * from (
     select D.KodSkl,I.PNT,
            IsNull(sum(KolOv*z),0) KolOv,IsNull(sum(KolM*z),0) KolM,
            IsNull(sum(KolZ*z),0) KolZ,IsNull(sum(KolF*z),0) KolF,IsNull(sum(KolKzp*z),0) KolKzp
       from DocCaption D, DocString I,
            ( select 1 vid, 1 z union all select 5, 1 union all select 2, -1 union all select 3, -1 ) Z
       where D.IdDoc=I.IdDoc and Z.vid=D.VidDoc
       group by D.KodSkl,I.PNT
  ) N
 where not exists(select 1 from DocStrOst D where N.KodSkl=D.KodSkl and N.PNT=D.PNT)
--------------------------------
-- Новая процедура получения остатков
CREATE PROCEDURE dbo.CalcOst
  @KodSkl smallint, @PNT integer, @Ostatok decimal(18,5) OUTPUT
AS
  declare @prod decimal(18,5), @KodPredpr int
    set @Prod=0
    set @Ostatok=IsNull(
         (select max(KolF) from DocStrOst where KodSkl=@KodSkl and PNT=@PNT)
        ,0)
if exists (select * from master.dbo.sysdatabases
           where  name ='Ditron')
begin
if ((select count(*) from tbPredpr where idPredpr =@KodSkl and idGrPredpr =
    ( select idGrPredpr from tbGrPredpr where nameGrPredpr = 'склад-магазин'))=1) and
    ((select count(*) from tbNastrSchet where IdPredpr=@KodSkl)=1)
begin
 set @kodPredpr=(select kodpredpr from tbpredpr where idpredpr=@kodskl)
 set @prod=IsNull(( Select Sum(Ditron.dbo.tbProdaja.Kol)
 From Ditron.dbo.tbProdaja
   Where Ditron.dbo.tbProdaja.KodPredpr=@KodPredpr and
 Ditron.dbo.tbProdaja.PNT=@PNT ),0)
 set @Ostatok=@Ostatok-@prod
end
end
GO
---------------------------------------
--- Сверка остатков
 select * from (
     select D.KodSkl,I.PNT,
            IsNull(sum(KolOv*z),0) KolOv,IsNull(sum(KolM*z),0) KolM,
            IsNull(sum(KolZ*z),0) KolZ,IsNull(sum(KolF*z),0) KolF,IsNull(sum(KolKzp*z),0) KolKzp
       from DocCaption D, DocString I,
            ( select 1 vid, 1 z union all select 5, 1 union all select 2, -1 union all select 3, -1 ) Z
       where D.IdDoc=I.IdDoc and Z.vid=D.VidDoc
       group by D.KodSkl,I.PNT
  ) N
   full join DocStrOst O on O.KodSkl=N.KodSkl and O.PNT=N.PNT
   where O.PNT is null or (N.PNT is null and O.KolF+O.KolKzp+O.KolM+O.KolOv+O.KolZ>0)
      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