<?php
// procedury do optymalnegi przyjemnego i szybkiego widoku danych procesow 
// bazowe konstruktory twardych struktur danych systemowych
//version @2014-01-22 bindera
//@2014-02-05 ustalono, ze ten plik zostanie rozrzucony po pojedynczych skryptach php, ktore beda wyzwalane przez klase zaprojektowana przez Piotra, ktora
//na podobnej zasadzie co WordPress bedzie czytac tabele z dostepnymi pluginami, gdzie te skryupty beda pozglaszane/lub beda sie zglaszac


if(isset($_SERVER["argv"][1])) {
	//wymuszenie argumentu z polecenia (ta zmienna oznaczac ma domene (np. biuro.biall-net.pl ) 
	//php -r"include('/Library/Server/Web/Data/Sites/Default/SE/superedit-SQIX_STRUCTURE_DB_SYNC.php') ;" biuro.biall-net.pl 
		$_SERVER['SERVER_NAME']=$_SERVER["argv"][1];
		$_REQUEST['SYSTEM_PROFILE_STRING_IMPORT_EXPORT']=$_SERVER["argv"][2]; //filtr do importu
		$_REQUEST['submit']=true;
		define('DS', DIRECTORY_SEPARATOR);
		define('APP_PATH_ROOT', dirname(__FILE__));
		define('APP_PATH_WWW', dirname(__FILE__));
		define('APP_PATH_CONFIG', APP_PATH_ROOT . DS . 'config');


		require_once APP_PATH_ROOT . DS . 'se-lib' . DS . 'Lib.php';
		Lib::loadClass('V');
		Lib::loadClass('Config');
		Lib::loadClass('DB');
		Lib::loadClass('User');
	    session_start();
                include_once(APP_PATH_CONFIG.'/.config-'.$_SERVER['SERVER_NAME'].'.php');

		include('superedit-DEBUG_S.php');

//	$PROJ_mount_point=Config::getConfFile('import_db', '') ;
//	print_r($PROJ_mount_point);

} else {

	if($_SESSION['ADM_ADMIN_LEVEL']<>0) die('niewlasciwy admin level != 0');
	if(!isset($_SESSION['ADM_ADMIN_LEVEL'])) die('brak admin level - zle wyzwolenie funkcji');

}

function DB_PROCEDURES_CREATE() {


DEBUG_S(-3,"Deklaruje zmienna SQL do wykonania instalacji procedur",null,__FILE__,__FUNCTION__,__LINE__);


$sql['turn_off_database_locking']="SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;";


//!IN7_MK_BAZA_DYSTRYBUCJI_hierarchy_sys_connect_by_path
$sql['drop_IN7_MK_BAZA_DYSTRYBUCJI_hierarchy_sys_connect_by_path']="drop function if exists IN7_MK_BAZA_DYSTRYBUCJI_hierarchy_sys_connect_by_path";
$sql['create_IN7_MK_BAZA_DYSTRYBUCJI_hierarchy_sys_connect_by_path']="CREATE DEFINER=`root`@`localhost` FUNCTION `IN7_MK_BAZA_DYSTRYBUCJI_hierarchy_sys_connect_by_path`(`delimiter` TEXT, `node` INT) RETURNS text CHARSET latin2
    READS SQL DATA
BEGIN
     DECLARE _path TEXT;
     DECLARE _type CHAR(255);
     DECLARE _lvl INT;
     
 DECLARE _cpath TEXT;
        DECLARE _id INT;
        DECLARE _id_cur INT;
    DECLARE EXIT HANDLER FOR NOT FOUND RETURN _path;
    SET _id = COALESCE(node, @id);
      SET _path = _id;
      SET _lvl=1;
    the_loop: LOOP
    
     SET _lvl =_lvl+1;
    
       IF _lvl>100 THEN
       	 RETURN concat('ERROR',_path);
	     LEAVE the_loop;
         
       END IF; 
    
                SELECT  P_ID,M_DIST_TYPE,ID
              INTO    _id,_type,_id_cur
         FROM    IN7_MK_BAZA_DYSTRYBUCJI
         WHERE   id = _id
                    AND COALESCE(id <> @start_with, TRUE);
               SET _path = CONCAT(_id, delimiter, _path);
              
              
  END LOOP the_loop;
END";


//!_IN7_MK_BAZA_DYSTRYBUCJI_tree_UPDATE
$sql['drop__IN7_MK_BAZA_DYSTRYBUCJI_tree_UPDATE']="DROP TRIGGER IF EXISTS  `_IN7_MK_BAZA_DYSTRYBUCJI_tree_UPDATE`";
$sql['create__IN7_MK_BAZA_DYSTRYBUCJI_tree_UPDATE']="CREATE DEFINER=`root`@`localhost` TRIGGER `_IN7_MK_BAZA_DYSTRYBUCJI_tree_UPDATE` BEFORE UPDATE ON `IN7_MK_BAZA_DYSTRYBUCJI` FOR EACH ROW BEGIN

IF NEW.P_ID<>OLD.P_ID THEN 
	SET NEW.path = IN7_MK_BAZA_DYSTRYBUCJI_hierarchy_sys_connect_by_path('-', NEW.P_ID);
	update IN7_DZIENNIK_KORESP ik set ik.path=IN7_MK_BAZA_DYSTRYBUCJI_hierarchy_sys_connect_by_path('-', NEW.ID) where path like concat('%',NEW.ID,'%');

END IF;
END
";


//!_IN7_MK_BAZA_DYSTRYBUCJI_tree_INSERT
$sql['drop__IN7_MK_BAZA_DYSTRYBUCJI_tree_INSERT']="DROP TRIGGER IF EXISTS  `_IN7_MK_BAZA_DYSTRYBUCJI_tree_INSERT`";
$sql['create__IN7_MK_BAZA_DYSTRYBUCJI_tree_INSERT']="CREATE DEFINER=`root`@`localhost` TRIGGER `_IN7_MK_BAZA_DYSTRYBUCJI_tree_INSERT` BEFORE INSERT ON `IN7_MK_BAZA_DYSTRYBUCJI` FOR EACH ROW BEGIN

	SET NEW.path = IN7_MK_BAZA_DYSTRYBUCJI_hierarchy_sys_connect_by_path('-', NEW.P_ID);
END
";

//!_IN7_DZIENNIK_KORESP_tree_INSERT
$sql['drop__IN7_DZIENNIK_KORESP_tree_INSERT']="DROP TRIGGER IF EXISTS  `_IN7_DZIENNIK_KORESP_tree_INSERT`";
$sql['create__IN7_DZIENNIK_KORESP_tree_INSERT']="CREATE DEFINER=`root`@`localhost` TRIGGER `_IN7_DZIENNIK_KORESP_tree_INSERT` BEFORE INSERT ON `IN7_DZIENNIK_KORESP` FOR EACH ROW BEGIN

	SET NEW.path = IN7_MK_BAZA_DYSTRYBUCJI_hierarchy_sys_connect_by_path('-', NEW.ID_PROJECT);
END
";


//!_IN7_DZIENNIK_KORESP_tree_UPDATE
$sql['drop__IN7_DZIENNIK_KORESP_tree_UPDATE']="DROP TRIGGER IF EXISTS  `_IN7_DZIENNIK_KORESP_tree_UPDATE`";
$sql['create__IN7_DZIENNIK_KORESP_tree_UPDATE']="CREATE DEFINER=`root`@`localhost` TRIGGER `_IN7_DZIENNIK_KORESP_tree_UPDATE` BEFORE UPDATE ON `IN7_DZIENNIK_KORESP` FOR EACH ROW BEGIN

IF NEW.ID_PROJECT<>OLD.ID_PROJECT THEN 
	SET NEW.path = IN7_MK_BAZA_DYSTRYBUCJI_hierarchy_sys_connect_by_path('-', NEW.ID_PROJECT);

END IF;
END
";


//! event do aktualizacji PROJEKTOW - raz dziennie wszystkie przemapowac TODO trigger powinien aktualizowac pozostale powiazane rekordy, ktore zmienily strukture po zmianie struktury projektow


$sql['event_delete_IN7_MK_BAZA_DYSTRYBUCJI__IN7_DZIENNIK_KORESP_tree']="drop event if exists _IN7_MK_BAZA_DYSTRYBUCJI__IN7_DZIENNIK_KORESP_tree_event";
$sql['event_create_IN7_MK_BAZA_DYSTRYBUCJI__IN7_DZIENNIK_KORESP_tree']="CREATE DEFINER=root@localhost EVENT _IN7_MK_BAZA_DYSTRYBUCJI__IN7_DZIENNIK_KORESP_tree_event
    ON SCHEDULE EVERY 1 DAY 
    STARTS str_to_date( date_format(now(), '%Y%m%d 0500'), '%Y%m%d %H%i' ) + INTERVAL 1 DAY
    DO 
     BEGIN 
     	update  IN7_MK_BAZA_DYSTRYBUCJI set path=IN7_MK_BAZA_DYSTRYBUCJI_hierarchy_sys_connect_by_path('-',ID);
     	update  IN7_DZIENNIK_KORESP set path=IN7_MK_BAZA_DYSTRYBUCJI_hierarchy_sys_connect_by_path('-',ID_PROJECT);

     END 
    
     ";


//!hierarchy_connect_by_parent_eq_prior_id_with_level_and_loop
$sql['drop_hierarchy_connect_by_parent_eq_prior_id_with_level_and_loop']="drop function if exists hierarchy_connect_by_parent_eq_prior_id_with_level_and_loop";
$sql['create_hierarchy_connect_by_parent_eq_prior_id_with_level_and_loop']="CREATE DEFINER=`root`@`localhost` FUNCTION `hierarchy_connect_by_parent_eq_prior_id_with_level_and_loop`(`value` INT, `maxlevel` INT) RETURNS int(11)
    READS SQL DATA
BEGIN
        DECLARE _id INT;
        DECLARE _parent INT;
        DECLARE _next INT;
        DECLARE _i INT;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET @id = NULL;

        SET _parent = @id;
        SET _id = -1;
        SET _i = 0;

        IF @id IS NULL THEN
                RETURN NULL;
        END IF;

        LOOP
                SELECT  MIN(id)
                INTO    @id
                FROM    CRM_PROCES
                WHERE   PARENT_ID = _parent
                        AND id > _id
                        
                        AND id <> @start_with
                        AND COALESCE(@level < maxlevel, TRUE);
                IF @id IS NOT NULL OR _parent = @start_with THEN
                        SET @level = @level + 1;
                        RETURN @id;
                END IF;
                SET @level := @level - 1;
                SELECT  id, PARENT_ID
                INTO    _id, _parent
                FROM    CRM_PROCES
                WHERE   id = _parent;
                SET _i = _i + 1;
        END LOOP;
        RETURN NULL;
END
";
$sql['drop_hierarchy_connect_by_iscycle']="drop function if exists hierarchy_connect_by_iscycle";

$sql['create_hierarchy_connect_by_iscycle']="CREATE DEFINER=`root`@`localhost` FUNCTION `hierarchy_connect_by_iscycle`(`node` INT) RETURNS int(11)
    READS SQL DATA
BEGIN
        DECLARE _id INT;
        DECLARE _loop INT;
        DECLARE _node INT;
        DECLARE EXIT HANDLER FOR NOT FOUND RETURN 0;
        SET _id = COALESCE(node, @id);
        SET _loop = 0;
        SET _node = 0;
        LOOP
                SELECT  PARENT_ID
                INTO    _id
                FROM    CRM_PROCES
                WHERE   id = _id;
                IF _id = @start_with THEN
                        SET _loop := _loop + 1;
                END IF;
                IF _id = COALESCE(node, @id) THEN
                        SET _node = _node + 1;
                END IF;
                IF _loop >= 2 THEN
                        RETURN _node;
                END IF;
        END LOOP;
END";


$sql['drop_hierarchy_sys_connect_by_path']="drop function if exists hierarchy_sys_connect_by_path";
$sql['create_hierarchy_sys_connect_by_path']="CREATE DEFINER=`root`@`localhost` FUNCTION `hierarchy_sys_connect_by_path`(`delimiter` TEXT, `node` INT) RETURNS text CHARSET latin2
    READS SQL DATA
BEGIN
     DECLARE _path TEXT;
     DECLARE _type CHAR(255);
 DECLARE _cpath TEXT;
        DECLARE _id INT;
        DECLARE _id_cur INT;
    DECLARE EXIT HANDLER FOR NOT FOUND RETURN _path;
    SET _id = COALESCE(node, @id);
      SET _path = _id;
    LOOP
                SELECT  PARENT_ID,TYPE,ID
              INTO    _id,_type,_id_cur
         FROM    CRM_PROCES
         WHERE   id = _id
                    AND COALESCE(id <> @start_with, TRUE);
               SET _path = CONCAT(_id, delimiter, _path);
              
              
  END LOOP;
END";


$sql['drop_CRM_PROCES_tree_detect_init']="drop function if exists CRM_PROCES_tree_detect_init";
$sql['create_CRM_PROCES_tree_detect_init']="CREATE DEFINER=`root`@`localhost` FUNCTION `CRM_PROCES_tree_detect_init`(`node` INT) RETURNS text CHARSET latin2
    READS SQL DATA
BEGIN
     DECLARE _path INT;
     DECLARE _type CHAR(255);
 DECLARE _cpath TEXT;
        DECLARE _id INT;
        DECLARE _id_cur INT;
    DECLARE EXIT HANDLER FOR NOT FOUND RETURN _path;
    SET _id = COALESCE(node, @id);
    
    LOOP
                SELECT  PARENT_ID,TYPE,ID
              INTO    _id,_type,_id_cur
         FROM    CRM_PROCES
         WHERE   id = _id
                    AND COALESCE(id <> @start_with, TRUE);
             
              
               if _type = 'PROCES_INIT' THEN
                SET _path = _id_cur;  
               END IF;
  END LOOP;
END";


$sql['drop__CRM_PROCES_STATS_proc_wiev']="drop table if exists _CRM_PROCES_STATS_proc_wiev;";
$sql['create__CRM_PROCES_STATS_proc_wiev']="create table _CRM_PROCES_STATS_proc_wiev
SELECT  hi.ID,CRM_PROCES_tree_detect_init(hi.ID) as ID_PROCES_INIT,CONCAT(REPEAT('    ', lvl - 1), hi.id) AS treeitem,
        hierarchy_sys_connect_by_path('/', hi.id) AS path,
        PARENT_ID, lvl,
        CASE
            WHEN lvl >= @maxlevel THEN 1
            ELSE COALESCE(
            (
            SELECT  0
            FROM    CRM_PROCES hl
            WHERE   hl.PARENT_ID = ho.id
                    AND hl.id <> @start_with
            LIMIT 1
            ), 1)
        END AS is_leaf,
        hierarchy_connect_by_iscycle(hi.id) AS is_cycle
FROM    (
        SELECT  hierarchy_connect_by_parent_eq_prior_id_with_level_and_loop(id, @maxlevel) AS id,
                CAST(@level AS SIGNED) AS lvl
        FROM    (
                SELECT  @start_with := 0,
                        @id := @start_with,
                        @level := 0,
                        @maxlevel := NULL
                ) vars, CRM_PROCES
        WHERE   @id IS NOT NULL
        ) ho
JOIN    CRM_PROCES hi
ON      hi.id = ho.id ;";

$sql['alter__CRM_PROCES_STATS_proc_wiev_unique_id']="alter table _CRM_PROCES_STATS_proc_wiev add unique(ID)";
$sql['alter__CRM_PROCES_STATS_proc_wiev_modify_path']="alter table _CRM_PROCES_STATS_proc_wiev modify path varchar(255)";
$sql['alter__CRM_PROCES_STATS_proc_wiev_modify_treeitem']="alter table _CRM_PROCES_STATS_proc_wiev modify treeitem varchar(255)";


//!_CRM_PROCES_STATS_proc_wiev_UPDATE

$sql['drop__CRM_PROCES_STATS_proc_wiev_UPDATE']="DROP TRIGGER IF EXISTS  `_CRM_PROCES_STATS_proc_wiev_UPDATE`";

$sql['create__CRM_PROCES_STATS_proc_wiev_UPDATE']="CREATE DEFINER=`root`@`localhost` TRIGGER `_CRM_PROCES_STATS_proc_wiev_UPDATE` AFTER UPDATE ON `CRM_PROCES` FOR EACH ROW BEGIN

IF NEW.PARENT_ID<>OLD.PARENT_ID THEN 
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
replace into  _CRM_PROCES_STATS_proc_wiev
SELECT  hi.ID,CRM_PROCES_tree_detect_init(hi.ID) as ID_PROCES_INIT,CONCAT(REPEAT('    ', lvl - 1), hi.id) AS treeitem,
        hierarchy_sys_connect_by_path('/', hi.id) AS path,
        PARENT_ID, lvl,
        CASE
            WHEN lvl >= @maxlevel THEN 1
            ELSE COALESCE(
            (
            SELECT  0
            FROM    CRM_PROCES hl
            WHERE   hl.PARENT_ID = ho.id
                    AND hl.id <> @start_with
            LIMIT 1
            ), 1)
        END AS is_leaf,
        hierarchy_connect_by_iscycle(hi.id) AS is_cycle
FROM    (
        SELECT  hierarchy_connect_by_parent_eq_prior_id_with_level_and_loop(id, @maxlevel) AS id,
                CAST(@level AS SIGNED) AS lvl
        FROM    (
                SELECT  @start_with := 0,
                        @id := @start_with,
                        @level := 0,
                        @maxlevel := 1000
                ) vars, CRM_PROCES
        WHERE   @id IS NOT NULL
        ) ho
JOIN    CRM_PROCES hi
ON      hi.id = ho.id ;


END IF;
END
";

//!_CRM_PROCES_STATS_proc_wiev_INSERT
$sql['_drop__CRM_PROCES_STATS_proc_wiev_INSERT']="DROP TRIGGER IF EXISTS  `_CRM_PROCES_STATS_proc_wiev_INSERT`;";
$sql['create__CRM_PROCES_STATS_proc_wiev_INSERT']="CREATE DEFINER=`root`@`localhost` TRIGGER `_CRM_PROCES_STATS_proc_wiev_INSERT` AFTER INSERT ON `CRM_PROCES` FOR EACH ROW BEGIN

IF NEW.PARENT_ID>0 THEN 
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
replace into  _CRM_PROCES_STATS_proc_wiev
SELECT  hi.ID,CRM_PROCES_tree_detect_init(hi.ID) as ID_PROCES_INIT,CONCAT(REPEAT('    ', lvl - 1), hi.id) AS treeitem,
        hierarchy_sys_connect_by_path('/', hi.id) AS path,
        PARENT_ID, lvl,
        CASE
            WHEN lvl >= @maxlevel THEN 1
            ELSE COALESCE(
            (
            SELECT  0
            FROM    CRM_PROCES hl
            WHERE   hl.PARENT_ID = ho.id
                    AND hl.id <> @start_with
            LIMIT 1
            ), 1)
        END AS is_leaf,
        hierarchy_connect_by_iscycle(hi.id) AS is_cycle
FROM    (
        SELECT  hierarchy_connect_by_parent_eq_prior_id_with_level_and_loop(id, @maxlevel) AS id,
                CAST(@level AS SIGNED) AS lvl
        FROM    (
                SELECT  @start_with := 0,
                        @id := @start_with,
                        @level := 0,
                        @maxlevel := 1000
                ) vars, CRM_PROCES
        WHERE   @id IS NOT NULL
        ) ho
JOIN    CRM_PROCES hi
ON      hi.id = ho.id ;


END IF;
END";


$sql['drop__CRM_PROCES_STATS']="drop table if exists _CRM_PROCES_STATS ;";
$sql_tmp['select__CRM_PROCES_STATS']="select t1.ID , t2.ID_PROCES_INIT , count(t1.ID) as CSteps 
 , (select count(ID) from CRM_IMAGE where REMOTE_TABLE='CRM_PROCES' and REMOTE_ID=t1.ID and A_STATUS!='DELETED' ) as CPics 
 , (select count(ID) from CRM_PROCES_HIST where ID_USERS2=t1.ID  ) as CProcEdits
 , (select count(ID) from CRM_WSKAZNIK where ID_PROCES=t1.ID and A_STATUS!='DELETED') as CRes
 , (select count(cwh.ID) from CRM_WSKAZNIK_HIST cwh left join CRM_WSKAZNIK as cw on cwh.ID_USERS2=cw.ID where cw.ID_PROCES=t1.ID    ) as CResorEdits
 , (select count(ctph.ID) from CRM_TESTY_PYTANIA_HIST as ctph left join CRM_TESTY_PYTANIA as ctp on ctp.ID=ctph.ID_USERS2  where ctp.ID_PROCES=t1.ID    ) as CQuestEdits

 , (select count(ci.ID) from CRM_IMAGE ci left join CRM_WSKAZNIK as cw on ci.REMOTE_ID=cw.ID and ci.REMOTE_TABLE='CRM_WSKAZNIK' where cw.ID_PROCES=t1.ID    ) as CResPics
 , (select count(ci.ID) from CRM_IMAGE ci left join CRM_WSKAZNIK as cw on ci.REMOTE_ID=cw.ID_ZASOB and ci.REMOTE_TABLE='CRM_LISTA_ZASOBOW' where cw.ID_PROCES=t1.ID    ) as CResPicsS

 , (select count(ID) from CRM_TESTY_PYTANIA  where ID_PROCES=t1.ID    ) as CQuest
 , (select count(ID) from CRM_TESTY_PYTANIA  where ID_PROCES=t1.ID and TEST_TYPE='TEORETYCZNY'     ) as CQuestTeor

 , (select count(ID) from CRM_TESTY_PYTANIA  where ID_PROCES=t1.ID  and TEST_TYPE='PRAKTYCZNY'  ) as CQuestPract -- pytania praktyczne
 , (select count(cto.ID) from CRM_TESTY_ODPOWIEDZI as cto left join CRM_TESTY_PYTANIA as ctp on cto.ID_PYTANIE=ctp.ID  where ctp.ID_PROCES=t1.ID  ) as CQRes -- udzielono odpowiedzi
 , (select count(cto.ID) from CRM_TESTY_ODPOWIEDZI as cto left join CRM_TESTY_PYTANIA as ctp on cto.ID_PYTANIE=ctp.ID  where ctp.ID_PROCES=t1.ID and ctp.TEST_TYPE='PRAKTYCZNY'  ) as CQPRes -- udzielono odpowiedzi praktycznych

 , (select count(cto.ID) from CRM_TESTY_ODPOWIEDZI as cto left join CRM_TESTY_PYTANIA as ctp on cto.ID_PYTANIE=ctp.ID  where ctp.ID_PROCES=t1.ID and cto.OCENA<1  ) as CQErrRes -- udzielono slabych odpowiedzi
 , (select count(distinct(au.ID)) from ADMIN_USERS as au left join CRM_AUTH_PROFILE as cap on au.ID=cap.REMOTE_ID and
    cap.REMOTE_TABLE='ADMIN_USERS' and cap.A_STATUS!='DELETED' left join CRM_WSKAZNIK as cw on cap.ID_ZASOB=cw.ID_ZASOB and cw.A_STATUS!='DELETED' where cw.ID_PROCES=t1.ID and au.A_STATUS='NORMAL'  ) as CWorkers -- pracownikow aktualnie wykonujacych proces - blad w strukturach ADMIN_USERS statusy WAITING
-- , (select count(distinct(au.ID)) from ADMIN_USERS as au 
--     left join CRM_AUTH_PROFILE as cap on au.ID=cap.REMOTE_ID and cap.REMOTE_TABLE='ADMIN_USERS' and cap.A_STATUS!='DELETED' 
--     left join CRM_WSKAZNIK as cw on cap.ID_ZASOB=cw.ID_ZASOB and cw.A_STATUS!='DELETED' 
--     left join CRM_TESTY_ODPOWIEDZI as cto on  cto.A_RECORD_CREATE_AUTHOR=au.ADM_ACCOUNT 
--     left join CRM_TESTY_PYTANIA as ctp on ctp.ID=cto.ID_PYTANIE
--     left join _CRM_PROCES_STATS_proc_wiev as cps on cps.ID=cw.ID_PROCES
--     where cw.ID_PROCES=cps.ID and au.A_STATUS='NORMAL' and ctp.ID_PROCES=t1.ID and cps.ID_PROCES_INIT=t1.ID   ) as CQAWorkers -- pracownikow ktorzy maja aktualny proces 
 , (select count(distinct(cto.ID)) from CRM_TESTY_ODPOWIEDZI as cto left join CRM_TESTY_PYTANIA as ctp on cto.ID_PYTANIE=ctp.ID
    left join CRM_WSKAZNIK as cw on cw.ID_PROCES=ctp.ID_PROCES
     left join _CRM_PROCES_STATS_proc_wiev as cps on cps.ID=cw.ID_PROCES
      left join CRM_WSKAZNIK as cw1 on cw1.ID_PROCES=cps.ID_PROCES_INIT
       left join CRM_AUTH_PROFILE as cap on cap.ID_ZASOB=cw1.ID_ZASOB
        left join ADMIN_USERS as au on au.ID=cap.REMOTE_ID and cap.REMOTE_TABLE='ADMIN_USERS' and au.A_STATUS='NORMAL'
    where cw.ID_PROCES = t1.ID and au.ADM_ACCOUNT=cto.A_RECORD_CREATE_AUTHOR and cto.OCENA>1 and ctp.TEST_TYPE='TEORETYCZNY') as CQTAnsOk -- pracownicy ktorzy sa przyporzadkowani do procesow ile udzielili odpowiedzi 
, (select count(distinct(cto.ID)) from CRM_TESTY_ODPOWIEDZI as cto left join CRM_TESTY_PYTANIA as ctp on cto.ID_PYTANIE=ctp.ID
    left join CRM_WSKAZNIK as cw on cw.ID_PROCES=ctp.ID_PROCES
     left join _CRM_PROCES_STATS_proc_wiev as cps on cps.ID=cw.ID_PROCES
      left join CRM_WSKAZNIK as cw1 on cw1.ID_PROCES=cps.ID_PROCES_INIT
       left join CRM_AUTH_PROFILE as cap on cap.ID_ZASOB=cw1.ID_ZASOB
        left join ADMIN_USERS as au on au.ID=cap.REMOTE_ID and cap.REMOTE_TABLE='ADMIN_USERS' and au.A_STATUS='NORMAL'
    where cw.ID_PROCES = t1.ID and au.ADM_ACCOUNT=cto.A_RECORD_CREATE_AUTHOR and cto.OCENA>1 and ctp.TEST_TYPE='PRAKTYCZNY') as CQPWAnsOk -- pracownicy ktorzy sa przyporzadkowani do procesow ile udzielili odpowiedzi 
, (select count(distinct(cto.ID)) from CRM_TESTY_ODPOWIEDZI as cto left join CRM_TESTY_PYTANIA as ctp on cto.ID_PYTANIE=ctp.ID
    left join CRM_WSKAZNIK as cw on cw.ID_PROCES=ctp.ID_PROCES
     left join _CRM_PROCES_STATS_proc_wiev as cps on cps.ID=cw.ID_PROCES
      left join CRM_WSKAZNIK as cw1 on cw1.ID_PROCES=cps.ID_PROCES_INIT
       left join CRM_AUTH_PROFILE as cap on cap.ID_ZASOB=cw1.ID_ZASOB
        left join ADMIN_USERS as au on au.ID=cap.REMOTE_ID and cap.REMOTE_TABLE='ADMIN_USERS'  
    where cw.ID_PROCES = t1.ID and au.ADM_ACCOUNT=cto.A_RECORD_CREATE_AUTHOR and cto.OCENA>1 and ctp.TEST_TYPE='PRAKTYCZNY') as CQPAnsOk -- pracownicy ktorzy sa przyporzadkowani do procesow ile udzielili odpowiedzi praktycznych


 , (select min(A_RECORD_CREATE_DATE) from CRM_WSKAZNIK where ID_PROCES=t1.ID and A_STATUS!='DELETED') as MinResCreDate
 , (select max(A_RECORD_UPDATE_DATE) from CRM_WSKAZNIK where ID_PROCES=t1.ID and A_STATUS!='DELETED') as MaxResUpdDate
 , (select min(A_RECORD_CREATE_DATE) from CRM_PROCES where ID=t1.ID and A_STATUS!='DELETED') as MinProcCreDate
 , (select max(A_RECORD_UPDATE_DATE) from CRM_PROCES where ID=t1.ID and A_STATUS!='DELETED') as MaxProcUpdDate


 from
  _CRM_PROCES_STATS_proc_wiev as t2 join CRM_PROCES as t1 on t1.ID=t2.ID 
   --   where t2.ID_PROCES_INIT=994

--  group by t2.ID_PROCES_INIT
    group by t1.ID
  order by t1.A_RECORD_UPDATE_DATE desc";
$sql['create__CRM_PROCES_STATS']="create table _CRM_PROCES_STATS 
    ".$sql_tmp['select__CRM_PROCES_STATS'] ;

$sql['alter__CRM_PROCES_STATS_unique_id']="alter table _CRM_PROCES_STATS add unique(ID)";


$sql['drop__CRM_PROCES_INIT_STATS']="drop table if exists _CRM_PROCES_INIT_STATS";

$sql_tmp['select_CRM_PROCES_INIT_STATS']="select t1.ID_PROCES_INIT as ID,left(t2.`DESC`,100) as `DESC`,t3.path, t3.lvl,sum(CSteps) as SCSteps,sum(CPics) as SCPics ,sum(CProcEdits+CResorEdits) as SCPREdits, 
 sum(CQuestEdits) as SCQuestEdits,sum(CResPics) as SCResPics , sum(CResPicsS) as SCResPicsS,sum(CQuest) as SCQuest,sum(CQuestPract) as SCQuestPract,
 sum(CQRes) as SCQRes,sum(CQPRes) as SCQPRes,sum(CQErrRes) as CQErrRes,sum(CWorkers) as SCWorkers,sum(CQTAnsOk) as SCQTAnsOk,sum(CQPWAnsOk) as CQPWAnsOk, sum(CQPAnsOk) as SCQPAnsOk
 ,(select count(ID) from CRM_TESTY where ID_PROCES_INIT=t1.ID_PROCES_INIT and TEST_TYPE='TEORETYCZNY') as STestTeor
 ,(select count(ID) from CRM_TESTY where ID_PROCES_INIT=t1.ID_PROCES_INIT and TEST_TYPE='PRAKTYCNZY') as STestPrakt
 ,(select avg(OCENA) from CRM_TESTY where ID_PROCES_INIT=t1.ID_PROCES_INIT) as AvgMark
 ,(select avg(DISCOUNT)  from CRM_TESTY  where ID_PROCES_INIT=t1.ID_PROCES_INIT) as AvgDiscount 
  ,min(MinProcCreDate) as MinProcCreDate , max(MaxProcUpdDate) as MaxProcUpdDate
  -- todo lista osob ktora powinna zdac test teoretyczny i taka sama praktyczny wyliczana w oparciu o to czy sa testy i ile jest stanowisk
 , (
	 select  if ((select sum(CQuestTeor) from _CRM_PROCES_STATS where ID_PROCES_INIT=t1.ID_PROCES_INIT)>0, 
	 concat('brak:',sum(if(ct.ID<1 or ct.ID is null ,1,0)),'/popraw:' , 
	 sum(if(ct.OCENA > 0 and ct.OCENA<2.5,1,0))  )   ,0) from ADMIN_USERS as au 
	  left join CRM_AUTH_PROFILE as cap on cap.REMOTE_ID=au.ID and cap.REMOTE_TABLE='ADMIN_USERS' and cap.A_STATUS in ('NORMAL','WAITING')
	  left join CRM_WSKAZNIK as cw on cw.ID_ZASOB=cap.ID_ZASOB  
	  left join CRM_TESTY as ct on ct.ID_PROCES_INIT=cw.ID_PROCES and ct.ID_TESTER=au.ID and ct.TEST_TYPE='TEORETYCZNY' 
	  where au.A_STATUS='NORMAL' and cw.ID_PROCES=t1.ID_PROCES_INIT  
	  ) as CTTWinfo
, (
	 select  if ((select sum(CQuestTeor) from _CRM_PROCES_STATS where ID_PROCES_INIT=t1.ID_PROCES_INIT)>0, 
	 concat('brak:',sum(if(ct.ID<1 or ct.ID is null ,1,0)),'/popraw:' , 
	 sum(if(ct.OCENA > 0 and ct.OCENA<2.5,1,0))  )   ,0) from ADMIN_USERS as au 
	  left join CRM_AUTH_PROFILE as cap on cap.REMOTE_ID=au.ID and cap.REMOTE_TABLE='ADMIN_USERS' and cap.A_STATUS in ('NORMAL','WAITING')
	  left join CRM_WSKAZNIK as cw on cw.ID_ZASOB=cap.ID_ZASOB  
	  left join CRM_TESTY as ct on ct.ID_PROCES_INIT=cw.ID_PROCES and ct.ID_TESTER=au.ID and ct.TEST_TYPE='PRAKTYCZNY' 
	  where au.A_STATUS='NORMAL' and cw.ID_PROCES=t1.ID_PROCES_INIT  
	  ) as CTPWinfo

	 
  , '' as A_ADM_COMPANY, '' as A_CLASSIFIED
  
 from _CRM_PROCES_STATS as t1 
  left join CRM_PROCES as t2 on t1.ID_PROCES_INIT=t2.ID
  left join _CRM_PROCES_STATS_proc_wiev as t3 on t3.ID=t1.ID_PROCES_INIT
 group by t1.ID_PROCES_INIT
";
$sql['create__CRM_PROCES_INIT_STATS']="create table _CRM_PROCES_INIT_STATS ".$sql_tmp['select_CRM_PROCES_INIT_STATS'];

$sql['alter__CRM_PROCES_INIT_STATS_mod_id']="alter table _CRM_PROCES_INIT_STATS modify ID int(11)";
$sql['alter__CRM_PROCES_INIT_STATS_add_uniq']="alter table _CRM_PROCES_INIT_STATS add unique(ID)";
$sql['alter__CRM_PROCES_INIT_STATS_SCPREdits']="alter table `_CRM_PROCES_INIT_STATS` CHANGE `SCPREdits` `SCPREdits` decimal(42,0)";


$sql['event_delete_CRM_PROCES_INIT_STATS']="drop event if exists _CRM_PROCES_INIT_STATS_event";
$sql['event_create_CRM_PROCES_INIT_STATS']="CREATE DEFINER=root@localhost EVENT _CRM_PROCES_INIT_STATS_event
    ON SCHEDULE EVERY 1 DAY 
    STARTS str_to_date( date_format(now(), '%Y%m%d 0500'), '%Y%m%d %H%i' ) + INTERVAL 1 DAY
    DO 
     BEGIN 
      SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
       replace into _CRM_PROCES_INIT_STATS ".$sql_tmp['select_CRM_PROCES_INIT_STATS']." ;
     END 
    
     ";


$sql['event_delete_CRM_PROCES_STATS']="drop event if exists _CRM_PROCES_STATS_event";
$sql['event_create_CRM_PROCES_STATS']="CREATE DEFINER=root@localhost EVENT _CRM_PROCES_STATS_event
    ON SCHEDULE EVERY 1 DAY 
    STARTS str_to_date( date_format(now(), '%Y%m%d 0500'), '%Y%m%d %H%i' ) + INTERVAL 1 DAY
    DO 
     BEGIN 
      SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
       replace into _CRM_PROCES_STATS ".$sql_tmp['select__CRM_PROCES_STATS']." ;
     END 
    
     ";


/*

-- stworzenie tabeli do planowania harmonogramow testow i szkolen

-- proponuje widok!
-- drop table if exists _CRM_PROCES_USER_STATS;
-- create table _CRM_PROCES_USER_STATS
-- DROP VIEW  _CRM_PROCES_USER_STATS_wiev;

*/


$sql['drop_CRM_LISTA_ZASOBOW_tree_detect_kierownik']="drop function if exists CRM_LISTA_ZASOBOW_tree_detect_kierownik";
$sql['create_CRM_LISTA_ZASOBOW_tree_detect_kierownik']="CREATE DEFINER=`root`@`localhost` FUNCTION `CRM_LISTA_ZASOBOW_tree_detect_kierownik`(`node` INT) RETURNS text CHARSET latin2
    READS SQL DATA
BEGIN
     DECLARE _path INT;
     DECLARE _type CHAR(255);
 DECLARE _cpath TEXT;
        DECLARE _id INT;
        DECLARE _id_cur INT;
        DECLARE _group_name CHAR(255);
    DECLARE EXIT HANDLER FOR NOT FOUND RETURN _group_name;
    SET _id = COALESCE(node, @id);
    
    SELECT NAME from LDAP_GROUPS where ID=node
                  INTO _group_name;
    
    
   label1:  LOOP
                SELECT t1.PARENT_ID,t1.TYPE,t1.ID
              INTO    _id,_type,_id_cur
         FROM    CRM_LISTA_ZASOBOW t1 
         WHERE   t1.id = _id
                  --  AND COALESCE(id <> @start_with, TRUE);
             ;
              
               if _type = 'STANOWISKO' and node!=_id_cur THEN
                SET _path = _id_cur;  
                  SELECT NAME from LDAP_GROUPS where ID=_id_cur
                  INTO _group_name;
                RETURN _group_name;
                LEAVE label1;
               END IF;
  END LOOP label1;
END";


$sql['replace__CRM_PROCES_USER_STATS_wiev']="create or replace definer=`root`@`localhost` view _CRM_PROCES_USER_STATS_wiev as
select  concat(au.ID,cw.ID) as ID,cps.ID as ID_PROCES , left(cps.`DESC`,50) as `DESC`
,au.ADM_ACCOUNT  
,cps.path
, 'TEORETYCZNY' as TEST_TYPE,cps.SCQuest , max(ct.OCENA) as OCENA , max(ct.TEST_END) as TEST_END
-- , max(if(ct.TEST_TYPE='TEORETYCZNY',ct.OCENA,NULL)) as OCENA_T
-- , max(if(ct.TEST_TYPE='PRAKTYCZNY',ct.OCENA,NULL)) as OCENA_P
from ADMIN_USERS as au 
  left join CRM_AUTH_PROFILE as cap on cap.REMOTE_ID=au.ID and cap.REMOTE_TABLE='ADMIN_USERS' and cap.A_STATUS in ('NORMAL','WAITING')
  left join CRM_WSKAZNIK as cw on cw.ID_ZASOB=cap.ID_ZASOB and cw.A_STATUS in ('NORMAL','WAITING') 
  left join CRM_TESTY as ct on ct.ID_PROCES_INIT=cw.ID_PROCES and ct.ID_TESTER=au.ID and ct.TEST_TYPE='TEORETYCZNY' 
  left join _CRM_PROCES_INIT_STATS as cps on cps.ID=cw.ID_PROCES

where au.A_STATUS='NORMAL' 

and cps.SCQuest>0
 group by au.ID, cps.ID

-- order by ct.OCENA desc
-- limit 10
union 
select  concat(au.ID,cw.ID) as ID, cps.ID as ID_PROCES , left(cps.`DESC`,50) as `DESC`
,au.ADM_ACCOUNT  
,cps.path
, 'PRAKTYCZNY' as TEST_TYPE, cps.SCQuestPract , max(ct.OCENA) as OCENA, max(ct.TEST_END) as TEST_END
from ADMIN_USERS as au 
  left join CRM_AUTH_PROFILE as cap on cap.REMOTE_ID=au.ID and cap.REMOTE_TABLE='ADMIN_USERS' and cap.A_STATUS in ('NORMAL','WAITING')
  left join CRM_WSKAZNIK as cw on cw.ID_ZASOB=cap.ID_ZASOB  and cw.A_STATUS in ('NORMAL','WAITING')
  left join CRM_TESTY as ct on ct.ID_PROCES_INIT=cw.ID_PROCES and ct.ID_TESTER=au.ID and ct.TEST_TYPE='PRAKTYCZNY' 
  left join _CRM_PROCES_INIT_STATS as cps on cps.ID=cw.ID_PROCES

where au.A_STATUS='NORMAL' 

and cps.SCQuestPract>0
 group by au.ID,cps.ID

order by ADM_ACCOUNT , path,OCENA desc";

$sql['event_drop_CRM_PROCES_USER_OCENA_OKRES']="drop event if exists CRM_PROCES_USER_OCENA_OKRES_event ";
$sql['event_create_CRM_PROCES_USER_OCENA_OKRES']=" CREATE DEFINER=root@localhost EVENT CRM_PROCES_USER_OCENA_OKRES_event 
ON SCHEDULE EVERY 1 DAY 
STARTS str_to_date( date_format(now(), '%Y%m%d 0600'), '%Y%m%d %H%i' ) + INTERVAL 1 DAY
 DO 
  BEGIN 
  SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
   insert ignore into CRM_PROCES_USER_OCENA_OKRES (ID,CW_ID,DATE,ID_PROCES,`DESC`,OPIS_ZASOB,L_APPOITMENT_USER,A_CLASSIFIED,A_ADM_COMPANY,FUNCTION) 
   select  '', group_concat(cw.ID) as CW_ID , now() ,cps.ID as ID_PROCES , left(cps.`DESC`,200) as `DESC` , group_concat(cw.`OPIS_ZASOB`) as OPIS_ZASOB
   ,au.ADM_ACCOUNT  ,CRM_LISTA_ZASOBOW_tree_detect_kierownik(cw.ID_ZASOB),CRM_LISTA_ZASOBOW_tree_detect_kierownik(cw.ID_ZASOB),concat('[',cz.ID,'] ',cz.`DESC`)
  from ADMIN_USERS as au 
  left join CRM_AUTH_PROFILE as cap on cap.REMOTE_ID=au.ID and cap.REMOTE_TABLE='ADMIN_USERS' and cap.A_STATUS in ('NORMAL','WAITING') and cap.SHOW_IN_PERIOD_MARK!='NO'
  left join CRM_WSKAZNIK as cw on cw.ID_ZASOB=cap.ID_ZASOB  and cw.A_STATUS in ('NORMAL','WAITING')
  left join CRM_LISTA_ZASOBOW as cz on cz.ID=cw.ID_ZASOB
--  left join CRM_TESTY as ct on ct.ID_PROCES_INIT=cw.ID_PROCES and ct.ID_TESTER=au.ID and ct.TEST_TYPE='TEORETYCZNY' 
  left join _CRM_PROCES_INIT_STATS as cps on cps.ID=cw.ID_PROCES

where au.A_STATUS='NORMAL' and cps.ID is not null

-- and cps.SCQuest>0
 group by au.ID, cps.ID , cw.ID;

 END
";


$sql['event_shedule_init']="SET GLOBAL event_scheduler = ON";


foreach($sql as $ind=>$sql_) {
	DEBUG_S(-3,'wykonuje zapytanie sql dla '.$ind,$sql_,__FILE__,__FUNCTION__,__LINE__);
	DB::query($sql_) or die('Problem z zapytaniem '.mysql_error());
	
}


//-- alter table _CRM_PROCES_USER_STATS add unique(ID,TEST_TYPE,ADM_ACCOUNT);

} //eof func
 ?>