 DELIMITER $$

DROP PROCEDURE IF EXISTS `ht_pr_tool`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `ht_pr_tool`(
    IN _MODE VARCHAR(150),
    IN _Param_A VARCHAR(50),    
    IN _Param_B VARCHAR(50),    
    IN _Param_C VARCHAR(50),    
    IN _Param_D VARCHAR(50),    
    IN _Param_E VARCHAR(50)    
)
BEGIN
    /*
    _MODE

    ------------------------------------------------------------------------------------------------------------------------------------------------------------
        
    ------------------------------------------------------------------------------------------------------------------------------------------------------------
    */
    DECLARE CZM_Status_WYSLANE INTEGER DEFAULT 16;
    DECLARE _ParentGUID VARCHAR(38) DEFAULT '';
    DECLARE _Monoznik_Dodawac_Zrealizowano INTEGER DEFAULT 1;
    DECLARE _DEBUG BOOLEAN;
    
    SET _DEBUG=_Param_E LIKE "%DEBUG%",_Param_E=REPLACE(_Param_E,"DEBUG","");

/*------------------------------------------------------------------------------------------------------------------------------------------------------------------    
    'BUILD_ZLEC_INIT_ZESTAW'
        Zaklada konta w planie kont
        Parametry
            _Param_A    'T' - towar, 'Z' - zamowienie, 'P' - planer, 'O' Operacje
            _Param_B    GUID towaru, zamowienia, etc
            _Param_C    <> '' - oznacza kazdy towar (produkt) osobno na zleceniu 
            _Param_D    <> '' - oznacza towary grupujemy 
            _Param_E    ''/
        CALL ht_pr_tool('BUILD_ZLEC_INIT_ZESTAW', 'Z', 'GUID', '', '', '');        
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/    
    IF _MODE = 'BUILD_ZLEC_INIT_ZESTAW' THEN
        DROP TEMPORARY TABLE IF EXISTS `t_shell_zlec_pwrw`;
        CREATE TEMPORARY TABLE `t_shell_zlec_pwrw` (
            `class_id` VARCHAR(50) DEFAULT 'ALL',
            `class_descr` VARCHAR(250) DEFAULT 'Wszystkie',
            `Build_From` VARCHAR(20),
            `GUID` VARCHAR(38),
            `ParentGUID` VARCHAR(38),
            `RecepturaGUID` VARCHAR(38),
            `IdnTowr` VARCHAR(38),
            `TowrNazwa` VARCHAR(250),
            `Jednostka` INT(11),
            `Ilosc` DOUBLE(18,6),
            `Uwagi` TEXT,
            `osobaGUID` VARCHAR(38),
            `nr_seryjny_do_instancji` VARCHAR(250),
            `KontrGUID` VARCHAR(38),
            `numer_src` VARCHAR(38),
            `UwagiNg` TEXT,
            `terminrealizacjiNg` DATE,
            `idnmagazyn` INT(11),
            `ZamowienieGUID` VARCHAR(38),    
            -- dla Maxform:
            nrzamwkontr VARCHAR(30),
            zamawiajacy    VARCHAR(50),
            KEY GUID(GUID)
        )
        ;
        IF _Param_A = 'Z' THEN    --    zamowienia
            SET _Monoznik_Dodawac_Zrealizowano = IF(IFNULL(_Param_E, '') != '', _Param_E, 1);
            INSERT INTO `t_shell_zlec_pwrw`
            (class_id, class_descr, Build_From, GUID, ParentGUID, RecepturaGUID, IdnTowr, TowrNazwa, Jednostka, Ilosc, Uwagi, osobaGUID, 
             nr_seryjny_do_instancji, KontrGUID, numer_src, UwagiNg, terminrealizacjiNg, idnmagazyn, ZamowienieGUID,
             nrzamwkontr, zamawiajacy)
            SELECT
                IF(_Param_C = '', 'ALL', p.GUID) AS class_id,
                IF(_Param_C = '', 'Wszystkie', p.towrnazwa) AS class_descr,
                _Param_A,
                p.GUID, _Param_B, p.RecepturaGUID, p.idntowr, p.towrnazwa, p.Jednostka, 
                _varDOUBLE_NOTNULL(p.ilosc-(_Monoznik_Dodawac_Zrealizowano * p.zrealizowano)-ifnull(p.zlecono,0)) AS Ilosc, 
                p.Uwagi, p.osobaGUID, p.nr_seryjny_do_instancji,
                n.kontrguid, n.nrzamw, n.Uwagi as UwagiNg, n.terminrealizacji, n.idnmagazyn, p.GUID,
                n.nrzamwkontr, n.zamawiajacy -- dla Maxform
            FROM `zmpo` p, `zmng` n            
            WHERE
                n.guid = _Param_B and 
                p.idnNg = n.guid
                and    if(n.rodzaj= 73, ifnull((select count(*) FROM rcpo PP LEFT JOIN rcng NN ON NN.GUID = PP.idnNG WHERE PP.idntowr = p.idntowr and PP.przychod <> 0 and  NN.nieaktywna = 0 and NN.widoczna_w_zam > 0),0)  > 0 and p.MagTyp = 2, true) 
            ;
        END IF;
        
        IF _Param_A = 'O' THEN    --    Operacje
            IF _Param_D != '' THEN    --    z grupowaniem
                INSERT INTO `t_shell_zlec_pwrw`
                (class_id, class_descr, Build_From, GUID, ParentGUID, RecepturaGUID, IdnTowr, TowrNazwa, Jednostka, Ilosc, osobaGUID)
                SELECT
                    IF(_Param_C = '', 'ALL', p.GUID) AS class_id,
                    IF(_Param_C = '', 'Wszystkie', p.towrnazwa) AS class_descr,
                    _Param_A,
                    p.GUID, _Param_B, p.RecepturaGUID, p.idntowr, p.towrnazwa, p.Jednostka, 
                    _varDOUBLE_NOTNULL(SUM(ilosc)) AS Ilosc, 
                    p.osobaGUID
                FROM `oppo` p
                WHERE
                    p.idnOper = _Param_B
                GROUP BY p.idntowr, p.osobaGUID    
                ;
            ELSE
                INSERT INTO `t_shell_zlec_pwrw`
                (class_id, class_descr, Build_From, GUID, RecepturaGUID, IdnTowr, TowrNazwa, Jednostka, Ilosc, osobaGUID)
                SELECT
                    IF(_Param_C = '', 'ALL', p.GUID) AS class_id,
                    IF(_Param_C = '', 'Wszystkie', p.towrnazwa) AS class_descr,
                    _Param_A,
                    p.GUID, p.RecepturaGUID, p.idntowr, p.towrnazwa, p.Jednostka, 
                    _varDOUBLE_NOTNULL(ilosc) AS Ilosc, 
                    p.osobaGUID
                FROM `oppo` p
                WHERE
                    p.idnOper = _Param_B
                ;
            END IF;
        END IF;

        IF _Param_A = 'T' THEN    --    Towar/Produkt
            INSERT INTO `t_shell_zlec_pwrw`
            (class_id, Build_From, GUID, RecepturaGUID, IdnTowr, TowrNazwa, Jednostka, Ilosc)
            SELECT
                'ALL' AS class_id,
                _Param_A,
                t.GUID, '', t.GUID AS idntowr, t.nazwa, t.Jednostka, 
                _VarDouble(0) AS Ilosc
            FROM `TowrNg` t
            WHERE
                t.GUID = _Param_B
            ;
        END IF;
        
        DELETE FROM `t_shell_zlec_pwrw`
        WHERE
            Ilosc <= 0
        ;
        DROP TEMPORARY TABLE IF EXISTS `t_shell_zlec_pwrw_group`;
        CREATE TEMPORARY TABLE `t_shell_zlec_pwrw_group` 
            SELECT
                class_id,
                _VarInt(COUNT(*)) AS Ile                
            FROM `t_shell_zlec_pwrw`    
            GROUP BY class_id    
        ;
        
    END IF;    --    IF _MODE = 'BUILD_ZLEC_INIT_ZESTAW'
    
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------    
    'BUILD_ZLEC_INIT_GET'
        Zwraca zestaw danych
        Parametry
            _Param_A    '1' - `t_shell_zlec_pwrw_group`, '' - `t_shell_zlec_pwrw`
            _Param_B    class_id (moze byc 'ALL' moze byc '' wszystkie)
        CALL ht_pr_tool('BUILD_ZLEC_INIT_GET', 'Z', '1', '', '', '');        
        CALL ht_pr_tool('BUILD_ZLEC_INIT_GET', 'Z', '', '', '', '');        
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/    
    IF _MODE = 'BUILD_ZLEC_INIT_GET' THEN
        IF _Param_A != '' THEN
            SELECT * from `t_shell_zlec_pwrw_group`;            
        ELSE
            SELECT * from `t_shell_zlec_pwrw` WHERE IF(_Param_B = '', TRUE, class_id = _Param_B);
        END IF;
    END IF;    --    IF _MODE = 'BUILD_ZLEC_INIT_GET'
    
    
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------    
    'BUILD_ZLEC_AFTER'
        Po budowaniu
        Parametry
            _Param_A    'T' - towar, 'Z' - zamowienie, 'P' - planer, 'O' Operacje
        CALL ht_pr_tool('BUILD_ZLEC_AFTER', 'Z', '', '', '', '');        
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/    
    IF _MODE = 'BUILD_ZLEC_AFTER' THEN
        IF _Param_A = 'Z' THEN    --    zamowienia
            UPDATE `t_shell_zlec_pwrw` zp
            SET
                zp.Ilosc = _varDOUBLE_NOTNULL((select sum(pp.Ilosc) from zmpo pp where pp.zamowienieGUID = zp.GUID))
            WHERE
                zp.Build_From = 'Z' 
            ;
            
            UPDATE `t_shell_zlec_pwrw` zp, `zmpo` p
            SET
                p.zlecono = ifnull(p.zlecono,0) + zp.Ilosc
            WHERE
                zp.Build_From = 'Z' AND 
                p.GUID = zp.GUID
            ;
			IF @session.globals.Owner <> 'COMEX' THEN	
            UPDATE `t_shell_zlec_pwrw` zp, `zmng` n
            SET 
                n.Status = 0
            WHERE 
                zp.Build_From = 'Z' AND 
                zp.ParentGUID IS NOT NULL AND
                n.GUID = zp.ParentGUID
            ;
            END IF;
            
            DROP TEMPORARY TABLE IF EXISTS `t_zmpo_update_status`;
            CREATE TEMPORARY TABLE `t_zmpo_update_status` 
                SELECT zp.GUID, n.GUID AS IdnNg
                FROM `t_shell_zlec_pwrw` zp, `zmng` n
                WHERE 
                    zp.Build_From = 'Z' AND 
                    zp.ParentGUID IS NOT NULL AND
                    n.GUID = zp.ParentGUID
                GROUP BY IdnNg
            ;
            CALL _zmng_Update_zrealizowane('for.`t_zmpo_update_status`', '', '', '');
            
        END IF;
    END IF;    --    IF _MODE = 'BUILD_ZLEC_AFTER'
--  CALL ht_pr_tool('AGG_ZLEC', @guidzp, '', '', '', '');
    IF _MODE = 'AGG_ZLEC' AND (SELECT rodzaj FROM zmng n WHERE n.guid=_Param_A)=73 THEN
        
        UPDATE zmpo p, rcpo r set p.pr_tpz=r.tpz WHERE p.idnng=_PARAM_A AND p.rcpoguid=r.guid AND p.pr_tpz=0 and p.pr_tpz<>r.tpz AND p.usluga=6;
        UPDATE zmpo p, rcpo r set p.info=lpad(r.lp,2,'0') WHERE p.idnng=_PARAM_A AND p.rcpoguid=r.guid AND p.info<>lpad(r.lp,2,'0');
        DROP TEMPORARY TABLE IF EXISTS t_zmpo_agg;
        CREATE TEMPORARY TABLE t_zmpo_agg
        SELECT max(p.idn) as idn,p.rodzaj,p.ilosc,p.towrsymb,p.idntowr,SUM(p.ilosc) as ilosc_r,SUM(p.ilosc-p.pr_tpz) as ilosc_bez_tpz,
                p.info,p.idnng,p.rcpoguid,p.guid,
                max(p.idnmagazyn) as idnmagazyn,MAX(p.pr_tpz) as pr_tpz,ifnull(GROUP_CONCAT(DISTINCT p.uwagi),'') as uwagi,count(*) as ile 
        FROM zmpo p WHERE p.idnng=_Param_A and p.rodzaj=73 AND p.MagTyp in (0,2) and p.zrealizowano=0 -- AND p.usluga=6
        -- GROUP BY idntowr,p.ilosc,p.pr_tpz,info,idnmagazyn,md5(ifnull(uwagi,'')),rcpoguid
        GROUP BY idntowr,rcpoguid
        HAVING ile>1
        ;
        IF _DEBUG THEN 
            SELECT * FROM t_zmpo_agg ORDER BY info;
        END IF;

        ALTER TABLE t_zmpo_agg ADD index (idn);
        UPDATE zmpo p, t_zmpo_agg t SET p.ilosc=t.ilosc_bez_tpz+t.pr_tpz WHERE p.idn=t.idn;
        UPDATE zmpo p, t_zmpo_agg t SET p.ilosc=0, p.status=-1, p.info=concat(p.info,'A')
        WHERE p.idnng=_PARAM_A AND p.idn<>t.idn and p.rcpoguid=t.rcpoguid
--            AND p.idntowr=t.idntowr and p.info=t.info 
--            and p.ilosc=t.ilosc and p.pr_tpz=t.pr_tpz and p.idnmagazyn=t.idnmagazyn and p.uwagi=t.uwagi
            ;
        DROP TEMPORARY TABLE IF EXISTS t_zmpo_agg;
        
    END IF;
--  CALL ht_pr_tool('AGG_ZM', @guidzp, 'DELETE', '', '', ''); -- na razie dla ZM ZA (108)
    IF _MODE IN ('AGG_ZM','AGG_ZLEC') AND (SELECT rodzaj FROM zmng n WHERE n.guid=_Param_A)<>73  THEN
        DROP TEMPORARY TABLE IF EXISTS t_zmpo_agg;
        CREATE TEMPORARY TABLE t_zmpo_agg
        SELECT max(p.idn) as idn,p.rodzaj,p.ilosc,p.towrsymb,p.idntowr,SUM(p.ilosc) as ilosc_r,p.idnmagazyn,count(*) as ile 
        FROM zmpo p WHERE p.idnng=_Param_A and p.rodzaj<>73 AND p.zrealizowano=0 AND p.usluga=0
        GROUP BY idntowr,idnmagazyn
        HAVING ile>1
        ;
        IF _DEBUG THEN 
            SELECT * FROM t_zmpo_agg ORDER BY towrsymb;
        END IF;

        ALTER TABLE t_zmpo_agg ADD index (idn);
        UPDATE zmpo p, t_zmpo_agg t SET p.ilosc=t.ilosc_r WHERE p.idn=t.idn;
        UPDATE zmpo p, t_zmpo_agg t SET p.ilosc=0, p.status=-1
        WHERE p.idnng=_PARAM_A AND p.idn<>t.idn and p.idntowr=t.idntowr;
        IF _Param_B LIKE "%DELETE%" THEN
            DELETE FROM zmpo WHERE idnng=_Param_A AND ilosc=0 AND zrealizowano=0 AND status=-1;
        END IF;
        DROP TEMPORARY TABLE IF EXISTS t_zmpo_agg;
    END IF;
    
END$$

DELIMITER ;

/*
$Log$
*/

/*
====================================================================================================================================================================================
    NOTATNIK
====================================================================================================================================================================================

CALL ht_pr_tool('BUILD_ZLEC_INIT_ZESTAW', 'Z', '{12E3DCB2-2DD5-48D8-84B8-FE16C58381BD}', '', '', '');        
CALL ht_pr_tool('BUILD_ZLEC_INIT_GET', 'Z', '1', '', '', '');        
CALL ht_pr_tool('BUILD_ZLEC_INIT_GET', 'Z', '', '', '', '');        

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
*/

