SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO


ALTER   PROCEDURE [dbo].[fcalc_perenos]
@data INT, @pr SMALLINT
AS
BEGIN
    /*
    * признак для переноса фактической калькуляции
    * на приход @pr = 1
    * на расход @pr = 2
    * причем для прихода в таблицу приходов и нетовара программы добавляется просто "хвост" из готовой калькуляции
    * а для расхода высчитывается новая таблица. в программе она не редактируется а нужна для построения отчета текстовой печати
    * */
    IF @pr = 1
    begin
        update fcalc_prihod_netovar
        set fcalc_prihod_netovar.hoz = fcalc_kalk.hoz_rashod, fcalc_prihod_netovar.prz = fcalc_kalk.proiz_rashod,
        fcalc_prihod_netovar.brak = fcalc_kalk.brak, fcalc_prihod_netovar.in_fond = fcalc_kalk.in_fond,
        fcalc_prihod_netovar.kom_rash = fcalc_kalk.kom_rash,  fcalc_prihod_netovar.proch_rash = fcalc_kalk.proch,
        fcalc_prihod_netovar.priznak = 1, fcalc_prihod_netovar.seb =
        ( isnull(fcalc_prihod_netovar.mat, 0) + isnull(fcalc_prihod_netovar.tzr_mat,0)  + isnull(fcalc_prihod_netovar.pki,0) + isnull(fcalc_prihod_netovar.tzr_pki,0) + isnull(fcalc_prihod_netovar.zp,0) + isnull(fcalc_prihod_netovar.dop_zp,0) + isnull(fcalc_prihod_netovar.otch,0)
        + isnull(fcalc_kalk.hoz_rashod,0) + isnull(fcalc_kalk.proiz_rashod,0) + isnull(fcalc_kalk.brak,0) + isnull(fcalc_kalk.in_fond,0) /*+ isnull(fcalc_kalk.kom_rash,0) */+ isnull(fcalc_kalk.proch,0))

        from fcalc_prihod_netovar
        inner join fcalc_kalk  /*шпз 2040 нет в фактической калькуляции но он учитывается в НЗП*/
        on ((fcalc_prihod_netovar.dt = @data) AND (fcalc_prihod_netovar.dt = fcalc_kalk.dt) AND (fcalc_prihod_netovar.shpz = fcalc_kalk.shpz)  AND (fcalc_prihod_netovar.priznak = 1))
    END
    IF @pr = 2
    BEGIN
        DELETE FROM fcalc_rashod

        INSERT fcalc_rashod(dt, shpz, mat, tzr_mat, pki, tzr_pki, zp, dop_zp, otchisl, prz_rashod, hoz_rashod, in_fond, proch, brak, seb, kom)
        SELECT a.dt, a.shpz, (a.mat+ a.otkl_mat+ b.mat), (a.tzr_mat+ b.tzr_mat),
        (a.pki+ a.otkl_pki+ b.pki), (a.tzr_pki+  b.tzr_pki), (a.zp+ a.otkl_zp+ b.zp), (a.dop_zp+  b.dop_zp), (a.otchisl+  b.otch),
        a.proiz_rashod, a.hoz_rashod, a.in_fond, a.proch, a.brak,
        (CAST((a.mat+ a.otkl_mat+ b.mat)AS BIGINT)+(a.tzr_mat+ b.tzr_mat)+(a.pki+ a.otkl_pki+ b.pki)+(a.tzr_pki+  b.tzr_pki)+
        (a.zp+ a.otkl_zp+ b.zp)+ (a.dop_zp+  b.dop_zp)+(a.otchisl+  b.otch)+ a.proiz_rashod +a.hoz_rashod +a.in_fond+a.proch+a.brak),
        a.kom_rash
         FROM fcalc_kalk a, fcalc_prihod_netovar b
         WHERE
        (a.dt = @data) AND (a.dt = b.dt)  AND b.priznak = 2 AND( a.shpz = b.shpz)
        /**/
        INSERT fcalc_rashod(dt, shpz, mat, tzr_mat, pki, tzr_pki, zp, dop_zp, otchisl, prz_rashod, hoz_rashod, in_fond, proch, brak, seb, kom)
        SELECT a.dt, a.shpz, (a.mat+ a.otkl_mat), a.tzr_mat,
        (a.pki+ a.otkl_pki), a.tzr_pki, (a.zp+ a.otkl_zp) , a.dop_zp , a.otchisl,
        a.proiz_rashod, a.hoz_rashod, a.in_fond, a.proch, a.brak,
        (CAST((a.mat+ a.otkl_mat) AS bigint)+a.tzr_mat+(a.pki+ a.otkl_pki)+a.tzr_pki+
        (a.zp+ a.otkl_zp)+ a.dop_zp + a.otchisl+ a.proiz_rashod +a.hoz_rashod +a.in_fond+a.proch+a.brak),
        a.kom_rash
        FROM fcalc_kalk a WHERE shpz NOT IN (
            SELECT a.shpz  FROM fcalc_kalk a, fcalc_prihod_netovar b
         WHERE
        (a.dt = @data) AND (a.dt = b.dt)  AND b.priznak = 2 AND( a.shpz = b.shpz)
        ) AND dt = @data

        /*FROM fcalc_kalk a
        LEFT OUTER JOIN fcalc_prihod_netovar b ON
        (a.dt = 201007) AND (a.dt = b.dt)  AND (b.priznak = 2) AND( a.shpz = b.shpz)
        select * from fcalc_rashod  WHERE
        (dt = 201007)
        VALUES(@data, 2001, )(a.shpz = b.shpz)*/

    END
    IF @pr = 3
        BEGIN
            update fcalc_prihod_netovar
        set  seb = ISNULL(mat,0) +  ISNULL(tzr_mat,0) +  ISNULL(pki,0) +  ISNULL(tzr_pki,0)+  ISNULL(zp,0)+  ISNULL(dop_zp,0)+  ISNULL(otch,0)
    WHERE dt = @data AND priznak = 2
        END

end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO