 /*!40101 SET NAMES cp1250 */;

DELIMITER $$

/*
call fk_salda('INIT',NULL,NULL,'','','');
call fk_salda('REFRESH','2025-01-01','2025-12-31','','','');
call fk_salda('REFRESH_ALL','2025-01-01','2025-12-31','','','');
call fk_salda('REFRESH_WGDAT','2025-01-01','2025-12-31','','','');
call fk_salda('REFRESH_WGDAT_ALL','2025-01-01','2025-12-31','','','');
-- bez odtwarzania tabeli:
call fk_salda('REFRESH_WGDAT_ALL','2025-01-01','2025-12-31','','','False');
-- bez uzupelniania zerowych dat bez obrotow
call fk_salda('REFRESH_WGDAT_ALL','2025-01-01','2025-12-31','','','False');

-- BO FK z rok 2025 na nastepny kolejne wywolania
call fk_salda('BO_CHECK',NULL,NULL,2025,'',''); result: status,jestjuzBO,info
-- standardowo - bez pozabilansowych i kont 4,5,7 i usunięciem istniejącego BO
call fk_salda('BO',NULL,NULL,2025,'','USUN');
-- z pozabilansowymi
call fk_salda('BO',NULL,NULL,2025,'','POZABILANS');
-- z pozabilansowymi i z kontami 4,7,9 i usunięciem istniejącego BO
call fk_salda('BO',NULL,NULL,2025,'','POZABILANS;Z47;Z5;Z9;USUN');

*/


DROP PROCEDURE IF EXISTS fk_salda$$
CREATE DEFINER='root'@'localhost' 
PROCEDURE fk_salda(
    IN _MODE VARCHAR(38),
    IN _DATAOD DATE,
    IN _DATADO DATE,
    IN _Param_A VARCHAR(38),
    IN _Param_B VARCHAR(38),
    IN _Param_C VARCHAR(38)
)
    CONTAINS SQL
    COMMENT ''
SPROC: BEGIN
    DECLARE _ROK int;
    DECLARE _DataBO DATE;
    DECLARE _DataBZ DATE;
    DECLARE _MAXTIME_S DATETIME;
    DECLARE _MAXTIME_Z DATETIME;
    SELECT idn,DataBO,DataBZ from lata_obrachunkowe WHERE _DATAOD BETWEEN DataBO AND DataBZ INTO _ROK,_DataBO,_DataBZ;
    SET @fk_salda_rok=_ROK; -- do filtracji planu kont
    SET @result='';
    SET @session.globals.Owner=IFNULL(@session.globals.Owner,(SELECT value FROM _db_globals g WHERE g.key='Owner'));

    IF _MODE='INIT' AND (_Param_C<>'False') THEN
        DROP TABLE IF EXISTS temp_salda_obroty_analityczne;
        CREATE TABLE temp_salda_obroty_analityczne (
        konto varchar(38) not null,
        bown double(16,4) not null default 0,
        boma double(16,4) not null default 0,
        wn double(16,4) not null default 0,
        ma double(16,4) not null default 0,
        rok int not null default 0,
        mc int not null default 0,
        maxtime DATETIME,
        INDEX maxtime(maxtime),
        INDEX konto(konto,maxtime),
        INDEX rmk(rok,mc,konto)
        );
        DROP TABLE IF EXISTS temp_salda_obroty_analityczne_wg_dat;
        CREATE TABLE temp_salda_obroty_analityczne_wg_dat (
        konto varchar(38) not null,
        bown double(16,4) not null default 0,
        boma double(16,4) not null default 0,
        wn double(16,4) not null default 0,
        ma double(16,4) not null default 0,
        rok int not null default 0,
        data_dowodu DATE,
        maxtime DATETIME,
        INDEX maxtime(maxtime),
        INDEX konto(konto,maxtime),
        UNIQUE INDEX rdk(rok,data_dowodu,konto)
        );
        DROP TABLE IF EXISTS temp_salda_daty;
        CREATE TABLE temp_salda_daty (
        dzien    date,
        UNIQUE INDEX dzien(dzien)
        );
        BEGIN
            DECLARE D INT;
            SET D=0;

            loop_daty: LOOP
                INSERT INTO temp_salda_daty SELECT DATE_ADD(_DataBO,interval D day);
                SET D = D + 1;
                IF D < 367 THEN
                    ITERATE loop_daty;
                END IF;
                LEAVE loop_daty;
            END LOOP loop_daty;
            DELETE FROM temp_salda_daty WHERE NOT (dzien BETWEEN _DataBO and _DataBZ);
            DELETE FROM temp_salda_daty WHERE NOT (dzien BETWEEN _DATAOD and _DATADO);
        END;
    END IF;

    IF _MODE='CLEAR' AND (_Param_C<>'False') THEN
        DROP TABLE IF EXISTS temp_salda_obroty_analityczne;
        DROP TABLE IF EXISTS temp_salda_obroty_analityczne_wg_dat;
        DROP TEMPORARY TABLE IF EXISTS temp_salda_obroty_wg_dat;
    END IF;

    IF (_MODE='REFRESH' OR _MODE='REFRESH_ALL') AND (_Param_C<>'False') THEN
        -- TODO: co z usunieciami zapisow??? TRIGGER?
        IF _MODE='REFRESH' THEN 
            SELECT max(maxtime) FROM temp_salda_obroty_analityczne WHERE rok=_ROK INTO _MAXTIME_S;
            SELECT max(z.localtime) FROM zapisy z WHERE rok=_ROK INTO _MAXTIME_Z;
        END IF;
        IF _MODE='REFRESH_ALL' OR (IFNULL(_MAXTIME_Z,'2000-01-01 00:00:01')>IFNULL(_MAXTIME_S,'2000-01-01 00:00:00')) THEN
            DELETE FROM temp_salda_obroty_analityczne WHERE rok=_ROK;
            INSERT INTO temp_salda_obroty_analityczne
            SELECT konto,
            sum(if(rodzaj_fk in (1,2) and stronawn,kwota,0)) as bown,
            sum(if(rodzaj_fk in (1,2) and not stronawn,kwota,0)) as boma,
            sum(if(stronawn,kwota,0)) as wn,sum(if(stronawn,0,kwota)) as ma,
            rok ,month(data_dowodu) as mc, max(z.localtime) as maxtime
            FROM zapisy z
            WHERE rok=_ROK
            GROUP BY konto,rok,mc;
        END IF;
--        SELECT ROWCOUNT() INTO @result;
        SELECT @result as aResult;
    END IF;

    IF (_MODE='REFRESH_WGDAT' OR _MODE='REFRESH_WGDAT_ALL') AND (_Param_C<>'False') THEN
        IF _MODE='REFRESH_WGDAT' THEN 
            SELECT max(maxtime) FROM temp_salda_obroty_analityczne_wg_dat WHERE rok=_ROK INTO _MAXTIME_S;
            SELECT max(z.localtime) FROM zapisy z WHERE rok=_ROK INTO _MAXTIME_Z;
        END IF;
        IF _MODE='REFRESH_WGDAT_ALL' OR (IFNULL(_MAXTIME_Z,'2000-01-01 00:00:01')>IFNULL(_MAXTIME_S,'2000-01-01 00:00:00')) THEN
            DELETE FROM temp_salda_obroty_analityczne_wg_dat WHERE rok=_ROK;
            INSERT INTO temp_salda_obroty_analityczne_wg_dat
            SELECT konto,
            sum(if(rodzaj_fk in (1,2) and stronawn,kwota,0)) as bown,
            sum(if(rodzaj_fk in (1,2) and not stronawn,kwota,0)) as boma,
            sum(if(stronawn,kwota,0)) as wn,sum(if(stronawn,0,kwota)) as ma,
            rok ,data_dowodu, max(z.localtime) as maxtime
            FROM zapisy z
            WHERE rok=_ROK
            GROUP BY konto,rok,data_dowodu;
            IF (_Param_C<>'TylkoNiezeroweDaty') THEN
                DROP TEMPORARY TABLE IF EXISTS temp_salda_konta;
                CREATE TEMPORARY TABLE temp_salda_konta SELECT DISTINCT konto FROM temp_salda_obroty_analityczne_wg_dat;
                INSERT IGNORE INTO temp_salda_obroty_analityczne_wg_dat (konto,rok,data_dowodu) 
                SELECT k.konto,_ROK,d.dzien FROM temp_salda_konta k,temp_salda_daty d;
                DROP TEMPORARY TABLE IF EXISTS temp_salda_konta;
            END IF;
        END IF;
--        SELECT ROWCOUNT() INTO @result;
        SELECT @result as aResult;
    END IF;
    IF _MODE='SALDA_WGDAT'  AND (_Param_C<>'False') THEN
        SET @wnbo:=0,@mabo:=0,@wnnar:=0,@manar:=0,@wnokr:=0,@maokr:=0,@wnsaldo:=0,@masaldo:=0,@konto:='';

        DROP TEMPORARY TABLE IF EXISTS temp_salda_obroty_wg_dat;
        CREATE TEMPORARY TABLE temp_salda_obroty_wg_dat (
        rok int not null default 0,
        wnbo double(18,2) not null default 0,
        mabo double(18,2) not null default 0,
        wn double(18,2) not null default 0,
        ma double(18,2) not null default 0,
        wnnar double(18,2) not null default 0,
        manar double(18,2) not null default 0,
        wnsaldo double(18,2) not null default 0,
        masaldo double(18,2) not null default 0,
        konto varchar(38) not null,
        data_dowodu DATE,
        maxtime DATETIME,
--        INDEX maxtime(maxtime),
        INDEX konto(konto,rok,maxtime)
--        INDEX rdk(rok,data_dowodu,konto)
        );
        INSERT INTO temp_salda_obroty_wg_dat
        SELECT 
        s.rok,
        round(_VarDouble(@wnbo:=if(ifnull(@konto,'')<>s.konto,s.bown,@wnbo+s.bown)),2) as wnbo,
        round(_VarDouble(@mabo:=if(ifnull(@konto,'')<>s.konto,s.boma,@mabo+s.boma)),2) as mabo,
        s.wn,
        s.ma,
        round(_VarDouble(@wnnar:=if(ifnull(@konto,'')<>s.konto,s.wn,@wnnar+s.wn)),2) as wnnar,
        round(_VarDouble(@manar:=if(ifnull(@konto,'')<>s.konto,s.ma,@manar+s.ma)),2) as manar,
        round(0) as wnsaldo,
        round(0) as masaldo,
        @konto:=s.konto as konto,
        s.data_dowodu,
        s.maxtime
        FROM temp_salda_obroty_analityczne_wg_dat s 
        ORDER BY konto,data_dowodu;
        UPDATE temp_salda_obroty_wg_dat SET wnsaldo=if(ABS(wnnar)>=ABS(manar),wnnar-manar,0),masaldo=if(ABS(manar)>ABS(wnnar),manar-wnnar,0);
    END IF;

    IF _MODE='SALDA'  AND (_Param_C<>'False') THEN
        DROP TEMPORARY TABLE IF EXISTS temp_salda_obroty;
        CREATE TEMPORARY TABLE temp_salda_obroty (
        konto varchar(38) not null,
        bown double(16,4) not null default 0,
        boma double(16,4) not null default 0,
        wn_okr double(16,4) not null default 0,
        ma_okr double(16,4) not null default 0,
        wn double(16,4) not null default 0,
        ma double(16,4) not null default 0,
        INDEX konto(konto)
        );
        INSERT INTO temp_salda_obroty
        SELECT konto,
        sum(bown) as bown,sum(boma) as boma,
        sum(wn) as wn,sum(ma) as ma
        FROM temp_salda_obroty_analityczne
        WHERE rok=_ROK and mc<=month(_DATADO)
-- TODO: odwzorowac reszte kolumn, dodac katcole
        GROUP BY konto,rok,mc;

    END IF;
    
    IF _MODE IN ('BO_CHECK','BO') THEN
        SET _Param_C=CONCAT(';',_Param_C,';');
        SET _ROK=_VarInt(_Param_A);
        SELECT DataBO,DataBZ from lata_obrachunkowe WHERE idn=_ROK INTO _DataBO,_DataBZ;
        SET @NoweBO=_DataBZ + INTERVAL 1 day, @NowyRok=NULL;
        SELECT idn from lata_obrachunkowe WHERE DataBO=@NoweBO INTO @NowyRok;
        IF @NowyRok IS NULL THEN
              SELECT 1 as status,CONCAT('BRAK OKRESU OBRACHUNKOWEGO DLA ',_VarStr(@NoweBO)) as info;
              LEAVE SPROC;
        END IF;
        -- param_a - stary rok
-- call fk_salda('BO_CHECK',NULL,NULL,2025,'',''); 
-- call fk_salda('BO',NULL,NULL,2025,'','POZABILANS,Z47,Z5');

        IF _MODE='BO_CHECK' THEN
            call fk_salda('BO_CLEAN',NULL,NULL,'','','');    
            SET @group_kat0=0,@group_kat1=0,@group_kat2=0,@group_kat3=0,@group_kat4=0,@group_kat5=0,@group_kat6=0,@group_kat7=0,@group_kat8=0,@group_kat9=0,@group_konta='';
            -- wybor grupowania po katcolu i ograniczenie zespołów kont do wybranych
            IF @session.globals.Owner IN ('POLAM') THEN SET @group_kat3=1,@group_konta="3568"; END IF;
            IF @session.globals.Owner IN ('SEVENET') THEN SET @group_kat4=1,@group_konta="3568"; END IF;

            CREATE TEMPORARY TABLE temp_FKBO
            SELECT konto,pozabilans, sum(if(stronawn,kwota,0)) as wn, sum(if(stronawn,0,kwota)) as ma, sum(kwota) as saldo,
                0 as stronawn, 0 as lp,
                IF(@group_kat0 and @group_konta like CONCAT('%',MID(z.konto,3,1),'%'),IFNULL(IF(katcol0=-1,0,katcol0),0),0) AS kat0,
                IF(@group_kat1 and @group_konta like CONCAT('%',MID(z.konto,3,1),'%'),IFNULL(IF(katcol1=-1,0,katcol1),0),0) AS kat1,
                IF(@group_kat2 and @group_konta like CONCAT('%',MID(z.konto,3,1),'%'),IFNULL(IF(katcol2=-1,0,katcol2),0),0) AS kat2,
                IF(@group_kat3 and @group_konta like CONCAT('%',MID(z.konto,3,1),'%'),IFNULL(IF(katcol3=-1,0,katcol3),0),0) AS kat3,
                IF(@group_kat4 and @group_konta like CONCAT('%',MID(z.konto,3,1),'%'),IFNULL(IF(katcol4=-1,0,katcol4),0),0) AS kat4,
                IF(@group_kat5 and @group_konta like CONCAT('%',MID(z.konto,3,1),'%'),IFNULL(IF(katcol5=-1,0,katcol5),0),0) AS kat5,
                IF(@group_kat6 and @group_konta like CONCAT('%',MID(z.konto,3,1),'%'),IFNULL(IF(katcol6=-1,0,katcol6),0),0) AS kat6,
                IF(@group_kat7 and @group_konta like CONCAT('%',MID(z.konto,3,1),'%'),IFNULL(IF(katcol7=-1,0,katcol7),0),0) AS kat7,
                IF(@group_kat8 and @group_konta like CONCAT('%',MID(z.konto,3,1),'%'),IFNULL(IF(katcol8=-1,0,katcol8),0),0) AS kat8,
                IF(@group_kat9 and @group_konta like CONCAT('%',MID(z.konto,3,1),'%'),IFNULL(IF(katcol9=-1,0,katcol9),0),0) AS kat9

            FROM zapisy z
            WHERE rok=_ROK
            GROUP BY konto,pozabilans,kat0,kat1,kat2,kat3,kat4,kat5,kat6,kat7,kat8,kat9;
                      
            UPDATE temp_FKBO SET stronawn=abs(wn)>abs(ma) ORDER BY konto;
            UPDATE temp_FKBO SET saldo=if(stronawn,wn-ma,ma-wn);
            DELETE FROM temp_FKBO WHERE saldo=0;
    
            ALTER TABLE temp_FKBO ADD UNIQUE konto(konto,pozabilans,kat0,kat1,kat2,kat3,kat4,kat5,kat6,kat7,kat8,kat9);
          
            SELECT round(sum(wn),2) as wn, round(sum(ma),2) as ma FROM temp_FKBO WHERE pozabilans=0 INTO @saldown,@saldoma;
            IF @saldown=@saldoma THEN
                SELECT 0 as status,COUNT(*) as jestjuzBO, CONCAT('Tworzenie bilansu otwarcia z okresu: \n', _varSTR(_DataBO), ' do ', _varSTR(_DataBZ), ' na dzien: ', _varSTR(@NoweBO), '.') as info 
                FROM zapisy WHERE rok=@NowyRok AND rodzaj_fk in (1,2);
            ELSE
                SELECT 1 as status,0 as jestjuzBO,'KONTA BILANSOWE NIE BILANSUJĄ SIĘ' as info;
            END IF;
        END IF;
               
        IF _MODE='BO' THEN
          IF _Param_C LIKE '%;USUN;%' THEN
              DELETE FROM zapisy WHERE rok=@NowyRok AND rodzaj_fk IN (1,2) AND data_dowodu=@NoweBO;
          END IF;
          IF _Param_C NOT LIKE '%;Z47;%' THEN
              DELETE FROM temp_FKBO WHERE LEFT(konto,3) IN ('*|4','*|7');
          END IF;
          IF _Param_C NOT LIKE '%;Z5;%' THEN
              DELETE FROM temp_FKBO WHERE LEFT(konto,3) IN ('*|5');
          END IF;
          IF _Param_C NOT LIKE '%;Z9;%' THEN
              DELETE FROM temp_FKBO WHERE LEFT(konto,3) IN ('*|9');
          END IF;
          IF _Param_C NOT LIKE '%;POZABILANS;%' THEN
            DELETE FROM temp_FKBO WHERE pozabilans;
          END IF;
          
          SET @lp:=0;
          UPDATE temp_FKBO SET lp=@lp:=@lp+1 ORDER BY konto;
          SELECT uuid(),ifnull(@session.Registration.IdnUser,'root') INTO @idnoper,@uid;
            
          INSERT INTO zapisy (idnoper,data_dowodu,konto,opis,kwota,stronawn,rodzaj_fk,numer_dok,data_oper,uid,pozabilans,guid,lp,rok,
                              katcol0,katcol1,katcol2,katcol3,katcol4,katcol5,katcol6,katcol7,katcol8,katcol9)
          SELECT @idnoper,@NoweBO,konto,"Bilans otwarcia",saldo,stronawn,if(saldo<0,2,1),"BO",@NoweBO,@uid,pozabilans,uuid(),lp,@NowyRok,
                    kat0,kat1,kat2,kat3,kat4,kat5,kat6,kat7,kat8,kat9
          FROM temp_FKBO;
            -- uzupelnienie brakujacych kont z poprzedniego roku
          CREATE TEMPORARY TABLE brak_kont SELECT t.konto FROM temp_FKBO t LEFT JOIN plankont p ON p.symbol=t.konto AND p.rok=@NowyRok;
          CREATE TEMPORARY TABLE pk SELECT p.* FROM plankont p, brak_kont b WHERE b.konto=p.symbol AND p.rok=_ROK;
          UPDATE pk SET idn=0,guid=uuid(),rok=@NowyROK,pk.localtime=now();
          INSERT IGNORE INTO plankont SELECT * FROM pk;
         
          IF _Param_C NOT LIKE '%DEBUG%' THEN
              call fk_salda('BO_CLEAN',NULL,NULL,'','','');    
          END IF;
        END IF;
    END IF;
    
    IF _MODE='BO_CLEAN' THEN
          DROP TEMPORARY TABLE IF EXISTS pk;
          DROP TEMPORARY TABLE IF EXISTS brak_kont;
          DROP TEMPORARY TABLE IF EXISTS temp_FKBO;
    END IF;  

/*
CALL fk_salda('KONTROLA_BILANSOWANIA.ALL','2026-01-01','2026-03-31','','','');
CALL fk_salda('KONTROLA_BILANSOWANIA.ZAKSIEGOWANE','2026-01-01','2026-03-31','','','');
*/

    IF _MODE LIKE 'KONTROLA_BILANSOWANIA%' THEN
        IF _MODE='KONTROLA_BILANSOWANIA.ZAKSIEGOWANE' THEN
            SET @StatusFilter=1;
        ELSE
            SET @StatusFilter=-1;
        END IF;
        SELECT 
            COUNT(*),
            _Rnd2(IFNULL(SUM(IF(stronawn, -kwota, kwota)),0)),
            _Rnd2(IFNULL(SUM(IF(rodzaj_fk IN (1,2),IF(stronawn, -kwota, kwota),0)),0))
        INTO 
            @IloscZapisow, @saldo, @saldoBO
        FROM zapisy 
        WHERE (@StatusFilter=-1 OR Status = @StatusFilter) 
          AND Data_Dowodu BETWEEN _DATAOD AND _DATADO 
          AND pozabilans = 0;

        IF @IloscZapisow > 0 THEN
            IF NOT (ABS(@saldo) < 0.0001) THEN
                 SELECT CONCAT(_utf8'Podany okres się nie bilansuje! Ilość zapisów: ', _VarStr(@IloscZapisow),
                 ' Okres: ' ,_VarStr(_DATAOD), ' - ', _VarStr(_DATADO) ,
                 ' Saldo = ', _VarStr(@saldo), 
                 ' Saldo BO = ', _VarStr(@saldoBO)
                 ) as info;
            ELSE 
                SELECT _utf8'OK' as info;
            END IF;
        ELSE
            SELECT CONCAT(_utf8'Brak zapisów w podanym okresie: ' ,_VarStr(_DATAOD), ' - ', _VarStr(_DATADO)) as info;
        END IF;
            
    END IF;
END $$

DROP FUNCTION IF EXISTS fk_saldo$$
CREATE DEFINER='root'@'localhost' FUNCTION fk_saldo(konto VARCHAR(255),strona CHAR(2)) RETURNS DOUBLE(16,4)
    NO SQL
    DETERMINISTIC
BEGIN
    RETURN 0;
END $$


-- TODO: porzadki z indeksami w zapisach

DELIMITER ;


/*
$Log: fk_salda.sql,v $
Revision 1.20  2025/08/26 05:09:08  apro
.

Revision 1.19  2024/09/05 06:54:27  apro
$SEND$
usuwanie tylko dla dataBO

Revision 1.18  2024/02/15 11:34:07  apro
$SEND$
niezerowe

Revision 1.17  2023/09/27 12:23:48  apro
niezerowe

Revision 1.16  2023/08/03 09:24:44  apro
$SEND$
ograniczenie kont przy grupowaniu na BO

Revision 1.15  2023/08/02 13:18:58  apro
$SEND$
BO wg katcoli

Revision 1.14  2022/07/11 06:02:03  apro
debug

Revision 1.13  2022/02/14 13:01:43  rafalg
rozbicie BO na katcole

Revision 1.12  2019/08/27 12:35:00  apro
$SEND$

Revision 1.11  2016/04/26 12:02:46  apro
poprawka tworzenia klucza unikalnego - zabezpiecznie przed sytuacją gdy są zapisy pozabilansowe i bilansowe na tym samym koncie

Revision 1.10  2016/01/11 09:26:33  apro
poprawka w tworzeniu BO dla pozabilansowych
$SEND$

Revision 1.9  2025/04/02 12:21:58  max
[crm:38619]

Revision 1.8  2025/03/31 13:42:13  max
BO_CLEAN

Revision 1.7  2025/03/31 12:20:26  apro
*** empty log message ***

Revision 1.6  2025/03/31 11:24:51  apro
dropy zabezpieczające

Revision 1.5  2025/03/31 11:06:57  apro
poprawka w lp (BO)

Revision 1.4  2025/03/31 10:59:23  apro
dodanie funkcji tworzenia BO

*/

