se/SE/superedit-DB_PROCEDURES_CREATE.php

<?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($DB=null) {


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 ;";

//! CRM_ZASOBY_BEGIN
$sql['drop_CRM_LISTA_ZASOBOW_hierarchy_sys_connect_by_path']="drop function if exists CRM_LISTA_ZASOBOW_hierarchy_sys_connect_by_path";
$sql['create_CRM_LISTA_ZASOBOW_hierarchy_sys_connect_by_path']="CREATE DEFINER=`root`@`localhost` FUNCTION `CRM_LISTA_ZASOBOW_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 = '';
      SET _lvl=1;
    the_loop: LOOP
    
     SET _lvl =_lvl+1;
    
       IF _lvl>100 THEN
       	 RETURN concat('ERROR',_path);
	     LEAVE the_loop;
         
       END IF; 
    
                SELECT  PARENT_ID,`DESC`,ID
              INTO    _id,_type,_id_cur
         FROM    CRM_LISTA_ZASOBOW
         WHERE   id = _id
                    AND COALESCE(id <> @start_with, TRUE);
               SET _path = CONCAT(_type, delimiter, _path);
              
              
              
              
  END LOOP the_loop;
END";



//!_CRM_LISTA_ZASOBOW_tree_UPDATE
$sql['drop__CRM_LISTA_ZASOBOW_tree_UPDATE']="DROP TRIGGER IF EXISTS  `_CRM_LISTA_ZASOBOW_tree_UPDATE`";
$sql['create__CRM_LISTA_ZASOBOW_tree_UPDATE']="CREATE DEFINER=`root`@`localhost` TRIGGER `_CRM_LISTA_ZASOBOW_tree_UPDATE` BEFORE UPDATE ON `CRM_LISTA_ZASOBOW` FOR EACH ROW BEGIN
IF NEW.PARENT_ID<>OLD.PARENT_ID or NEW.`DESC`<>OLD.`DESC` THEN 
SET NEW.path = CRM_LISTA_ZASOBOW_hierarchy_sys_connect_by_path('/', NEW.PARENT_ID);	
update CRM_WSKAZNIK ik set ik.path_CRM_LISTA_ZASOBOW=CRM_LISTA_ZASOBOW_hierarchy_sys_connect_by_path('/', NEW.ID) where path_CRM_LISTA_ZASOBOW like concat('%',NEW.ID,'%');
-- update CRM_LISTA_ZASOBOW ik set ik.path=CRM_LISTA_ZASOBOW_hierarchy_sys_connect_by_path('/', ik.ID) where path like concat('%',OLD.`DESC`,'%') and ik.ID<>OLD.ID;
END IF;
END
";

//!_CRM_LISTA_ZASOBOW_tree_UPDATE_after
$sql['drop__CRM_LISTA_ZASOBOW_tree_UPDATE_after']="DROP TRIGGER IF EXISTS  `_CRM_LISTA_ZASOBOW_tree_UPDATE_after`";
/*$sql['create__CRM_LISTA_ZASOBOW_tree_UPDATE_after']="CREATE DEFINER=`root`@`localhost` TRIGGER `_CRM_LISTA_ZASOBOW_tree_UPDATE_after` AFTER UPDATE ON `CRM_LISTA_ZASOBOW` FOR EACH ROW BEGIN
IF NEW.PARENT_ID<>OLD.PARENT_ID or NEW.`DESC`<>OLD.`DESC` THEN
-- update CRM_LISTA_ZASOBOW ik set ik.path=CRM_LISTA_ZASOBOW_hierarchy_sys_connect_by_path('/', ik.ID) where path like concat('%',OLD.`DESC`,'%');
update CRM_WSKAZNIK ik set ik.path_CRM_LISTA_ZASOBOW=CRM_LISTA_ZASOBOW_hierarchy_sys_connect_by_path('/', ik.ID_ZASOB) where path_CRM_LISTA_ZASOBOW like concat('%',OLD.`DESC`,'%');
END IF;

END
";
*/


//!_CRM_LISTA_ZASOBOW_tree_INSERT
$sql['drop__CRM_LISTA_ZASOBOW_tree_INSERT']="DROP TRIGGER IF EXISTS  `_CRM_LISTA_ZASOBOW_tree_INSERT`";
$sql['create__CRM_LISTA_ZASOBOW_tree_INSERT']="CREATE DEFINER=`root`@`localhost` TRIGGER `_CRM_LISTA_ZASOBOW_tree_INSERT` BEFORE INSERT ON `CRM_LISTA_ZASOBOW` FOR EACH ROW BEGIN

	SET NEW.path = CRM_LISTA_ZASOBOW_hierarchy_sys_connect_by_path('/', NEW.PARENT_ID);
END
";




//!_CRM_WSKAZNIK_tree_INSERT
$sql['drop__CRM_WSKAZNIK_tree_INSERT']="DROP TRIGGER IF EXISTS  `_CRM_WSKAZNIK_tree_INSERT`";
$sql['create__CRM_WSKAZNIK_tree_INSERT']="CREATE DEFINER=`root`@`localhost` TRIGGER `_CRM_WSKAZNIK_tree_INSERT` BEFORE INSERT ON `CRM_WSKAZNIK` FOR EACH ROW BEGIN

	SET NEW.path_CRM_LISTA_ZASOBOW = CRM_LISTA_ZASOBOW_hierarchy_sys_connect_by_path('/', NEW.ID_ZASOB);
END
";



//!_CRM_WSKAZNIK_tree_UPDATE
$sql['drop__CRM_WSKAZNIK_tree_UPDATE']="DROP TRIGGER IF EXISTS  `_CRM_WSKAZNIK_tree_UPDATE`";
$sql['create__CRM_WSKAZNIK_tree_UPDATE']="CREATE DEFINER=`root`@`localhost` TRIGGER `_CRM_WSKAZNIK_tree_UPDATE` BEFORE UPDATE ON `CRM_WSKAZNIK` FOR EACH ROW BEGIN

IF NEW.ID_ZASOB<>OLD.ID_ZASOB THEN 
	SET NEW.path_CRM_LISTA_ZASOBOW = CRM_LISTA_ZASOBOW_hierarchy_sys_connect_by_path('/', NEW.ID_ZASOB);

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_CRM_LISTA_ZASOBOW__CRM_WSKAZNIK_tree']="drop event if exists _CRM_LISTA_ZASOBOW__CRM_WSKAZNIK_tree_event";
$sql['event_create_CRM_LISTA_ZASOBOW__CRM_WSKAZNIK_tree']="CREATE DEFINER=root@localhost EVENT _CRM_LISTA_ZASOBOW__CRM_WSKAZNIK_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  CRM_LISTA_ZASOBOW set path=CRM_LISTA_ZASOBOW_hierarchy_sys_connect_by_path('/',ID);
     	update  CRM_WSKAZNIK set path_CRM_LISTA_ZASOBOW=CRM_LISTA_ZASOBOW_hierarchy_sys_connect_by_path('/',ID_ZASOB);

     END 
    
     ";





//! CRM_ZASOBY_END

//!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

IF NEW.ID_PROJECT IS NOT NULL THEN
	SET NEW.path = IN7_MK_BAZA_DYSTRYBUCJI_hierarchy_sys_connect_by_path('-', NEW.ID_PROJECT);
END IF;

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 is null THEN
	SET NEW.path = '';
ELSEIF OLD.ID_PROJECT IS NULL or 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_GOTO_CACHE']="drop table if exists _CRM_PROCES_GOTO_CACHE;";
$sql_tmp['select_CRM_PROCES_GOTO_CACHE']="select t1.ID,t1.ID_PROCES_INIT,t2.IF_TRUE_GOTO , t2.IF_TRUE_GOTO_FLAG, now() as TIMESTAMP
	 from _CRM_PROCES_STATS_proc_wiev t1 
	inner join CRM_PROCES as t2 on ( t1.ID=t2.ID and t2.IF_TRUE_GOTO>0 and t2.IF_TRUE_GOTO_FLAG='GOTO_AND_RETURN' ) 
	-- inner join CRM_PROCES as t3 on ( t2.IF_TRUE_GOTO=t3.ID and t2.IF_TRUE_GOTO>0 and t2.IF_TRUE_GOTO_FLAG='GOTO_AND_RETURN' )  TODO@2014-09-04 trzeba rekursywnie podlapac wzajemne powiazania

	where t1.ID_PROCES_INIT>0
	";
$sql['create__CRM_PROCES_GOTO_CACHE']="create table _CRM_PROCES_GOTO_CACHE ".$sql_tmp['select_CRM_PROCES_GOTO_CACHE'] ;

$sql['alter__CRM_PROCES_GOTO_CACHE_ID']="alter table _CRM_PROCES_GOTO_CACHE add index(ID)" ;
$sql['alter__CRM_PROCES_GOTO_CACHE_ID_PROCES_INIT_type']="alter table _CRM_PROCES_GOTO_CACHE modify ID_PROCES_INIT int(11)" ;
$sql['alter__CRM_PROCES_GOTO_CACHE_ID_PROCES_INIT']="alter table _CRM_PROCES_GOTO_CACHE add index(ID_PROCES_INIT)" ;
$sql['alter__CRM_PROCES_GOTO_CACHE_IF_TRUE_GOTO']="alter table _CRM_PROCES_GOTO_CACHE add index(IF_TRUE_GOTO)" ;



$sql['event_delete_CRM_PROCES_GOTO_CACHE']="drop event if exists _CRM_PROCES_GOTO_CACHE_event";
$sql['event_create_CRM_PROCES_GOTO_CACHE']="CREATE DEFINER=root@localhost EVENT _CRM_PROCES_GOTO_CACHE_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 ;
       truncate table _CRM_PROCES_INIT_STATS;
       replace into _CRM_PROCES_INIT_STATS ".$sql_tmp['select_CRM_PROCES_GOTO_CACHE']." ;
     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_to_group']="create or replace definer=`root`@`localhost` view _CRM_PROCES_USER_STATS_wiev_to_group as
select  concat(au.ID,cw.ID,0) 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
, au.EMPLOYEE_TYPE , au.A_RECORD_CREATE_DATE, au.A_RECORD_UPDATE_DATE , au.L_APPOITMENT_DATE, au.L_APPOITMENT_INFO, au.L_APPOITMENT_USER
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

-- dodatkowe GOTO_AND_RETURN

union 
select  concat(au.ID,cw.ID,1) 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
, au.EMPLOYEE_TYPE , au.A_RECORD_CREATE_DATE, au.A_RECORD_UPDATE_DATE , au.L_APPOITMENT_DATE, au.L_APPOITMENT_INFO, au.L_APPOITMENT_USER
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_PROCES_GOTO_CACHE as cpgc on cpgc.ID_PROCES_INIT=cw.ID_PROCES
  
  left join CRM_TESTY as ct on ct.ID_PROCES_INIT=cpgc.IF_TRUE_GOTO and ct.ID_TESTER=au.ID and ct.TEST_TYPE='TEORETYCZNY' 
  left join _CRM_PROCES_INIT_STATS as cps on cps.ID=cpgc.IF_TRUE_GOTO

where au.A_STATUS='NORMAL' 

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


union 
select  concat(au.ID,cw.ID,2) 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
, au.EMPLOYEE_TYPE , au.A_RECORD_CREATE_DATE, au.A_RECORD_UPDATE_DATE , au.L_APPOITMENT_DATE, au.L_APPOITMENT_INFO, au.L_APPOITMENT_USER
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
 
-- dodatkowe GOTO_AND_RETURN

union 
select  concat(au.ID,cw.ID,3) 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
, au.EMPLOYEE_TYPE, au.A_RECORD_CREATE_DATE, au.A_RECORD_UPDATE_DATE , au.L_APPOITMENT_DATE, au.L_APPOITMENT_INFO, au.L_APPOITMENT_USER
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_PROCES_GOTO_CACHE as cpgc on cpgc.ID_PROCES_INIT=cw.ID_PROCES

  left join CRM_TESTY as ct on ct.ID_PROCES_INIT=cpgc.IF_TRUE_GOTO and ct.ID_TESTER=au.ID and ct.TEST_TYPE='PRAKTYCZNY' 
  left join _CRM_PROCES_INIT_STATS as cps on cps.ID=cpgc.IF_TRUE_GOTO

where au.A_STATUS='NORMAL' 

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

$sql['replace__CRM_PROCES_USER_STATS_wiev']="create or replace definer=`root`@`localhost` view _CRM_PROCES_USER_STATS_wiev as
	select ID,ID_PROCES, `DESC`, ADM_ACCOUNT, EMPLOYEE_TYPE,A_RECORD_CREATE_DATE, A_RECORD_UPDATE_DATE ,L_APPOITMENT_DATE,L_APPOITMENT_INFO, L_APPOITMENT_USER, path,TEST_TYPE,SCQuest,OCENA,TEST_END
	from _CRM_PROCES_USER_STATS_wiev_to_group
	group by ADM_ACCOUNT,ID_PROCES,TEST_TYPE ";


$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['procedure_GRAFIK_PRACY_FILL_DATE_drop']="drop procedure if exists GRAFIK_PRACY_FILL_DATE";
$sql['procedure_GRAFIK_PRACY_FILL_DATE_create']="CREATE PROCEDURE GRAFIK_PRACY_FILL_DATE(start_date DATE, end_date DATE)
BEGIN
  DECLARE crt_date DATE;
  DECLARE USER  TEXT;
  DECLARE COUNT_USERS integer;
  DECLARE USERS_counter int;
  DECLARE ACL_GROUP TEXT;
  DECLARE USER_GROUP TEXT;

 
  SET @USERS_counter = 0;
  SET USERS_counter = 0;
  SET @ACL_GROUP = '';
  SET @USER_GROUP = '';
  SELECT COUNT(`ID`) into COUNT_USERS FROM ADMIN_USERS WHERE EMPLOYEE_TYPE='Pracownik' and A_STATUS='NORMAL';

WHILE USERS_counter < COUNT_USERS DO
  SET crt_date=start_date;

 PREPARE stmt FROM \"SELECT ADM_ACCOUNT into @USER from ADMIN_USERS WHERE EMPLOYEE_TYPE='Pracownik' and A_STATUS='NORMAL' limit ?,1;\";
 EXECUTE stmt USING @USERS_counter ;
DEALLOCATE PREPARE stmt;
      
 PREPARE stmt FROM \"SELECT DEFAULT_ACL_GROUP into @ACL_GROUP from ADMIN_USERS WHERE EMPLOYEE_TYPE='Pracownik' and A_STATUS='NORMAL' limit ?,1;\";
 EXECUTE stmt USING @USERS_counter ;
DEALLOCATE PREPARE stmt;


SELECT GROUP_CONCAT(concat('[',cap.ID_ZASOB,']')) into @USER_GROUP from ADMIN_USERS 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')
   WHERE au.ADM_ACCOUNT=@USER ;


  WHILE crt_date < end_date DO
    



INSERT ignore INTO GRAFIK_PRACY (`L_APPOITMENT_USER`,`DATE`,`WEEK`,`DAY_OF_WEEK`,`A_CLASSIFIED`,`A_ADM_COMPANY`,`USER_GROUPS`) VALUES(@USER , crt_date,date_format(crt_date,'%v'),date_format(crt_date,'%W'), @ACL_GROUP,@ACL_GROUP, @USER_GROUP );
SET crt_date = ADDDATE(crt_date, INTERVAL 1 DAY);
      
       END WHILE;
      SET USERS_counter = USERS_counter +1;
      SET @USERS_counter = @USERS_counter +1;

  END WHILE;
END";



$sql['event_delete_GRAFIK_PRACY_FILL_DATE']="drop event if exists GRAFIK_PRACY_FILL_DATE_event";
$sql['event_create_GRAFIK_PRACY_FILL_DATE']="CREATE DEFINER=root@localhost EVENT GRAFIK_PRACY_FILL_DATE_event
    ON SCHEDULE EVERY 1 DAY 
    STARTS str_to_date( date_format(now(), '%Y%m%d 0400'), '%Y%m%d %H%i' ) + INTERVAL 1 DAY
    DO 
     BEGIN 
     	call GRAFIK_PRACY_FILL_DATE(date_format(now(),'%Y-%m-01'),date_format(DATE_ADD(now(),INTERVAL + 2 MONTH ),'%Y-%m-01'));

     END 
    
     ";



$sql['view__GRAFIK_PRACY']="create or replace definer=`root`@`localhost` view GRAFIK_PRACY_view as 

SELECT  ID,YEAR( DATE ) as rok , MONTH( DATE ) as miesiac,
`L_APPOITMENT_USER` , SUM( TIME_TO_SEC( TIMEDIFF(  `END_WORK_HOUR` ,  `START_WORK_HOUR`  ) ) )/3600  AS GODZIN_PLANOWO,

 SUM( TIME_TO_SEC( TIMEDIFF(  `END_WORK_HOUR_CONFIRM` ,  `START_WORK_HOUR_CONFIRM`  ) ) )/3600  AS GODZIN_FAKTYCZNIE,

SUM(
if(
TIME_TO_SEC( TIMEDIFF(  `START_WORK_HOUR_CONFIRM`, `START_WORK_HOUR`  ) ) >0,
TIME_TO_SEC( TIMEDIFF(  `START_WORK_HOUR_CONFIRM`, `START_WORK_HOUR`  ) ),
0
)
 )/3600 as GODZIN_SPOZNIEN,

SUM(
if(
TIME_TO_SEC( TIMEDIFF(  `END_WORK_HOUR`, `END_WORK_HOUR_CONFIRM`  ) ) >0,
TIME_TO_SEC( TIMEDIFF(  `END_WORK_HOUR`, `END_WORK_HOUR_CONFIRM`  ) ),
0
)
 )/3600 as GODZIN_WYJSC_PRZED_CZASEM,

 sum(`BREAK1_MINUTES`+`BREAK2_MINUTES`)/60 as CZAS_PRZERW ,

sum(
 if(BREAK1_MINUTES>0,1,0)+
 if(BREAK2_MINUTES>0,1,0)
) as ILOSC_PRZERW ,


sum(
if( TIME_TO_SEC( TIMEDIFF(  `START_WORK_HOUR_CONFIRM`, `START_WORK_HOUR`  ) ) >0,1,0)
) as DNI_W_PRACY  , 

count(ID) as ILOSC_DNI

FROM  `GRAFIK_PRACY` 
WHERE 1 
 GROUP  BY L_APPOITMENT_USER, YEAR( DATE ) , MONTH( DATE ) 

";



$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__);
	if(!empty($DB)) {
		$DB->query($sql_) or die('Problem z zapytaniem '.mysql_error());
	} else {
		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
 ?>