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




$sql['KONTAKTY_view']="
CREATE or REPLACE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `KONTAKTY_view` AS select `u`.`ID` AS `ID`,`u`.`ADM_ACCOUNT` AS `ADM_ACCOUNT`,`u`.`ADM_NAME` AS `ADM_NAME`,`u`.`EMPLOYEE_TYPE` AS `EMPLOYEE_TYPE`,`u`.`ADM_PHONE` AS `ADM_PHONE`,`u`.`EMAIL` AS `EMAIL`,`u`.`A_STATUS` AS `A_STATUS`,`u`.`A_CLASSIFIED` AS `A_CLASSIFIED`,`u`.`A_ADM_COMPANY` AS `A_ADM_COMPANY`,group_concat(`ua`.`ID_ZASOB` separator ',') AS `ID_ZASOB_CSV_NUM` from (`ADMIN_USERS` `u` left join `CRM_AUTH_PROFILE` `ua` on(((`ua`.`REMOTE_ID` = `u`.`ID`) and (`ua`.`REMOTE_TABLE` = 'ADMIN_USERS')))) where (`u`.`A_STATUS` in ('WAITING','NORMAL','MONITOR','WARNING')) group by `u`.`ID`;
";


//! 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);
	  	update  CRM_PROCES set path=CRM_PROCES_hierarchy_sys_connect_by_path('-',ID);
     END

     ";
//! CRM_ZASOBY_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 path begin @2016-10



//! CRM_ZASOBY_BEGIN
$sql['drop_CRM_PROCES_hierarchy_sys_connect_by_path']="drop function if exists CRM_PROCES_hierarchy_sys_connect_by_path";
$sql['create_CRM_PROCES_hierarchy_sys_connect_by_path']="CREATE DEFINER=`root`@`localhost` FUNCTION `CRM_PROCES_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_PROCES
         WHERE   id = _id
                    AND COALESCE(id <> @start_with, TRUE);
               SET _path = CONCAT(_id_cur, delimiter, _path);
  END LOOP the_loop;
END";



//!_CRM_LISTA_ZASOBOW_tree_UPDATE
$sql['drop__CRM_PROCES_tree_UPDATE']="DROP TRIGGER IF EXISTS  `_CRM_PROCES_tree_UPDATE`";
$sql['create__CRM_PROCES_tree_UPDATE']="CREATE DEFINER=`root`@`localhost` TRIGGER `_CRM_PROCES_tree_UPDATE` BEFORE UPDATE ON `CRM_PROCES` FOR EACH ROW BEGIN
IF NEW.PARENT_ID<>OLD.PARENT_ID THEN
SET NEW.path = CRM_PROCES_hierarchy_sys_connect_by_path('/', NEW.PARENT_ID);
END IF;
END
";

//!_CRM_LISTA_ZASOBOW_tree_UPDATE_after
//$sql['drop__CRM_PROCES_tree_UPDATE_after']="DROP TRIGGER IF EXISTS  `_CRM_PROCES_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_PROCES_tree_INSERT']="DROP TRIGGER IF EXISTS  `_CRM_PROCES_tree_INSERT`";
$sql['create__CRM_PROCES_tree_INSERT']="CREATE DEFINER=`root`@`localhost` TRIGGER `_CRM_PROCES_tree_INSERT` BEFORE INSERT ON `CRM_PROCES` FOR EACH ROW BEGIN

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




//! CRM_PROCES path end @2016-10










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

//!_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['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

     ";

$sql['event_delete_CRM_PROCES_STATS_proc_view']="drop event if exists `_CRM_PROCES_STATS_proc_wiev_event`";
$sql['event_create_CRM_PROCES_STATS_proc_view']="CREATE DEFINER=root@localhost EVENT `_CRM_PROCES_STATS_proc_wiev_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

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

/*

-- 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['reaplce_DEVICES_GROUP_view']="create or replace definer=`root`@`localhost` VIEW `DEVICES_GROUP` AS select max(`DEVICES`.`ID`) AS `ID`,`DEVICES`.`A_RECORD_CREATE_DATE` AS `A_RECORD_CREATE_DATE`,`DEVICES`.`A_RECORD_CREATE_AUTHOR` AS `A_RECORD_CREATE_AUTHOR`,`DEVICES`.`A_RECORD_UPDATE_DATE` AS `A_RECORD_UPDATE_DATE`,`DEVICES`.`A_RECORD_UPDATE_AUTHOR` AS `A_RECORD_UPDATE_AUTHOR`,`DEVICES`.`A_PROBLEM` AS `A_PROBLEM`,`DEVICES`.`A_PROBLEM_DESC` AS `A_PROBLEM_DESC`,`DEVICES`.`A_PROBLEM_DATE` AS `A_PROBLEM_DATE`,`DEVICES`.`A_STATUS` AS `A_STATUS`,`DEVICES`.`A_STATUS_INFO` AS `A_STATUS_INFO`,`DEVICES`.`A_STATUS_LASTCHANGE_DATE` AS `A_STATUS_LASTCHANGE_DATE`,`DEVICES`.`A_UPDATE_DATE` AS `A_UPDATE_DATE`,`DEVICES`.`A_ADM_COMPANY` AS `A_ADM_COMPANY`,`DEVICES`.`S_OTHER_INFO` AS `S_OTHER_INFO`,`DEVICES`.`S_IP` AS `S_IP`,`DEVICES`.`T_NETWORK_SERVER` AS `T_NETWORK_SERVER`,`DEVICES`.`T_DEVICE_SERIAL` AS `T_DEVICE_SERIAL`,group_concat(distinct `DEVICES`.`T_DEVICE_TYPE` order by `DEVICES`.`T_DEVICE_TYPE` ASC separator ',') AS `T_DEVICE_TYPE`,`DEVICES`.`T_DEVICE_PORTS_OK` AS `T_DEVICE_PORTS_OK`,`DEVICES`.`T_DEVICE_INFO` AS `T_DEVICE_INFO`,`DEVICES`.`T_DEVICE_POWER` AS `T_DEVICE_POWER`,`DEVICES`.`T_TELBOX_NEIGHBOUR_IN` AS `T_TELBOX_NEIGHBOUR_IN`,`DEVICES`.`T_TELBOX_NEIGHBOUR_IN_ID` AS `T_TELBOX_NEIGHBOUR_IN_ID`,`tb`.`T_TELBOX_NAME` AS `T_TELBOX_NAME`,`DEVICES`.`T_PRODUCER` AS `T_PRODUCER`,`DEVICES`.`T_PRODUCTION_DATE` AS `T_PRODUCTION_DATE`,`DEVICES`.`T_BUY_DATE` AS `T_BUY_DATE`,`DEVICES`.`T_SELLER` AS `T_SELLER`,`DEVICES`.`T_OWNER` AS `T_OWNER`,`DEVICES`.`T_GUARRANTY_EXPIRE` AS `T_GUARRANTY_EXPIRE`,`DEVICES`.`T_PAPERBOX_LOCATION` AS `T_PAPERBOX_LOCATION`,`DEVICES`.`F_BILL_DATE` AS `F_BILL_DATE`,`DEVICES`.`F_BILL_NUMBER` AS `F_BILL_NUMBER`,`DEVICES`.`F_BILL_POSITION` AS `F_BILL_POSITION`,`DEVICES`.`F_BILL_COST` AS `F_BILL_COST`,`DEVICES`.`S_HW` AS `S_HW`,`DEVICES`.`CRM_LISTA_ZASOBOW_ID` AS `CRM_LISTA_ZASOBOW_ID`,`cz`.`OPIS` AS `CRM_LISTA_ZASOBOW_OPIS`,`cz`.`DESC` AS `CRM_LISTA_ZASOBOW_DESC`,`DEVICES`.`ID_PROJECT` AS `ID_PROJECT`,`DEVICES`.`L_APPOITMENT_DATE` AS `L_APPOITMENT_DATE`,`DEVICES`.`L_APPOITMENT_USER` AS `L_APPOITMENT_USER`,`DEVICES`.`L_APPOITMENT_PERIOD` AS `L_APPOITMENT_PERIOD`,`DEVICES`.`L_APPOITMENT_INFO` AS `L_APPOITMENT_INFO`,`DEVICES`.`L_APPOITMENT_TYPE` AS `L_APPOITMENT_TYPE`,`DEVICES`.`L_CALENDAR` AS `L_CALENDAR`,`DEVICES`.`P_ACCOUNT` AS `P_ACCOUNT`,`DEVICES`.`ID_BILLING_USERS` AS `ID_BILLING_USERS`,`DEVICES`.`ID_SERVICES` AS `ID_SERVICES`,`DEVICES`.`A_CLASSIFIED` AS `A_CLASSIFIED`,`DEVICES`.`ADM_PASSWD` AS `ADM_PASSWD`,`DEVICES`.`S_PASSWD` AS `S_PASSWD`,count(`DEVICES`.`ID`) AS `ILE` from ((`DEVICES` left join `CRM_LISTA_ZASOBOW` `cz` on((`cz`.`ID` = `DEVICES`.`CRM_LISTA_ZASOBOW_ID`))) left join `TELBOXES` `tb` on((`tb`.`ID` = `DEVICES`.`T_TELBOX_NEIGHBOUR_IN_ID`))) group by `DEVICES`.`A_STATUS`,`DEVICES`.`CRM_LISTA_ZASOBOW_ID`,`DEVICES`.`T_TELBOX_NEIGHBOUR_IN_ID`";

$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['view__GRAFIK_PRACY']="CREATE or replace ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `GRAFIK_PRACY_view` AS select `GRAFIK_PRACY`.`ID` AS `ID`,year(`GRAFIK_PRACY`.`DATE`) AS `rok`,month(`GRAFIK_PRACY`.`DATE`) AS `miesiac`,`GRAFIK_PRACY`.`L_APPOITMENT_USER` AS `L_APPOITMENT_USER`,(sum(time_to_sec(timediff(`GRAFIK_PRACY`.`END_WORK_HOUR`,`GRAFIK_PRACY`.`START_WORK_HOUR`))) / 3600) AS `GODZIN_PLANOWO`,(sum(time_to_sec(timediff(if(`GRAFIK_PRACY`.`END_WORK_HOUR_CONFIRM`,`GRAFIK_PRACY`.`END_WORK_HOUR_CONFIRM`,`GRAFIK_PRACY`.`START_WORK_HOUR_CONFIRM`),`GRAFIK_PRACY`.`START_WORK_HOUR_CONFIRM`))) / 3600) AS `GODZIN_FAKTYCZNIE`,(sum(if((time_to_sec(timediff(`GRAFIK_PRACY`.`START_WORK_HOUR_CONFIRM`,`GRAFIK_PRACY`.`START_WORK_HOUR`)) > 0),time_to_sec(timediff(`GRAFIK_PRACY`.`START_WORK_HOUR_CONFIRM`,`GRAFIK_PRACY`.`START_WORK_HOUR`)),0)) / 3600) AS `GODZIN_SPOZNIEN`,(sum(if((`GRAFIK_PRACY`.`END_WORK_HOUR_CONFIRM` and (time_to_sec(timediff(`GRAFIK_PRACY`.`END_WORK_HOUR`,`GRAFIK_PRACY`.`END_WORK_HOUR_CONFIRM`)) > 0)),time_to_sec(timediff(`GRAFIK_PRACY`.`END_WORK_HOUR`,`GRAFIK_PRACY`.`END_WORK_HOUR_CONFIRM`)),0)) / 3600) AS `GODZIN_WYJSC_PRZED_CZASEM`,(sum((`GRAFIK_PRACY`.`BREAK1_MINUTES` + `GRAFIK_PRACY`.`BREAK2_MINUTES`)) / 60) AS `CZAS_PRZERW`,sum((if((`GRAFIK_PRACY`.`BREAK1_MINUTES` > 0),1,0) + if((`GRAFIK_PRACY`.`BREAK2_MINUTES` > 0),1,0))) AS `ILOSC_PRZERW`,sum(if((`GRAFIK_PRACY`.`START_WORK_HOUR_CONFIRM` and `GRAFIK_PRACY`.`END_WORK_HOUR_CONFIRM`),1,0)) AS `DNI_W_PRACY`,count(`GRAFIK_PRACY`.`ID`) AS `ILOSC_DNI`,sum((if((`GRAFIK_PRACY`.`START_WORK_HOUR_CONFIRM` and `GRAFIK_PRACY`.`END_WORK_HOUR_CONFIRM`),(time_to_sec(timediff(`GRAFIK_PRACY`.`END_WORK_HOUR_CONFIRM`,`GRAFIK_PRACY`.`START_WORK_HOUR_CONFIRM`)) - time_to_sec(timediff(`GRAFIK_PRACY`.`END_WORK_HOUR`,`GRAFIK_PRACY`.`START_WORK_HOUR`))),0) / 3600)) AS `NADGODZINY`,(sum((if((`GRAFIK_PRACY`.`START_WORK_HOUR_CONFIRM` and (time_to_sec(timediff('07:00:00',`GRAFIK_PRACY`.`START_WORK_HOUR_CONFIRM`)) > 0)),time_to_sec(timediff('07:00:00',`GRAFIK_PRACY`.`START_WORK_HOUR_CONFIRM`)),0) + if((`GRAFIK_PRACY`.`END_WORK_HOUR_CONFIRM` and (time_to_sec(timediff(`GRAFIK_PRACY`.`END_WORK_HOUR_CONFIRM`,'21:00:00')) > 0)),time_to_sec(timediff(`GRAFIK_PRACY`.`END_WORK_HOUR_CONFIRM`,'21:00:00')),0))) / 3600) AS `GODZIN_W_NOCY`,(sum(if((`GRAFIK_PRACY`.`START_WORK_HOUR_CONFIRM` and (time_to_sec(timediff(`GRAFIK_PRACY`.`START_WORK_HOUR`,`GRAFIK_PRACY`.`START_WORK_HOUR_CONFIRM`)) > 0)),time_to_sec(timediff(`GRAFIK_PRACY`.`START_WORK_HOUR`,`GRAFIK_PRACY`.`START_WORK_HOUR_CONFIRM`)),0)) / 3600) AS `GODZIN_PRZYJSC_PRZED_CZASEM`,(sum(if((time_to_sec(timediff(`GRAFIK_PRACY`.`END_WORK_HOUR_CONFIRM`,`GRAFIK_PRACY`.`END_WORK_HOUR`)) > 0),time_to_sec(timediff(`GRAFIK_PRACY`.`END_WORK_HOUR_CONFIRM`,`GRAFIK_PRACY`.`END_WORK_HOUR`)),0)) / 3600) AS `GODZIN_WYJSC_PO_CZASIE` , `GRAFIK_PRACY`.`A_ADM_COMPANY` as `A_ADM_COMPANY`, `GRAFIK_PRACY`.`A_CLASSIFIED` as `A_CLASSIFIED` from `GRAFIK_PRACY` group by `GRAFIK_PRACY`.`L_APPOITMENT_USER`,year(`GRAFIK_PRACY`.`DATE`),month(`GRAFIK_PRACY`.`DATE`)
";



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

PREPARE stmt FROM \"SELECT EMPLOYEE_TYPE into @EMPLOYEE_TYPE 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`,`EMPLOYEE_TYPE`) VALUES(@USER , crt_date,date_format(crt_date,'%v'),date_format(crt_date,'%W'), @ACL_GROUP,@ACL_GROUP, @USER_GROUP ,@EMPLOYEE_TYPE);
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__in7_dziennik_koresp_budget_view']="
CREATE or replace ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `in7_dziennik_koresp_budget_view` AS select `k`.`ID` AS `ID`,`k`.`path` AS `path`,`k`.`ID_PROJECT` AS `ID_PROJECT`,year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) AS `rok`,month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) AS `miesiac`,date_format(`k`.`K_DATA_OTRZYMANEJ_KORESP`,'%Y-%m') AS `data`,sum(`k`.`COST_VALUE`) AS `sum_k`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '0') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = year(now()))),`k`.`COST_VALUE`,0)) AS `sum_0`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '1') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = year(now()))),`k`.`COST_VALUE`,0)) AS `sum_1`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '2') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = year(now()))),`k`.`COST_VALUE`,0)) AS `sum_2`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '3') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = year(now()))),`k`.`COST_VALUE`,0)) AS `sum_3`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '4') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = year(now()))),`k`.`COST_VALUE`,0)) AS `sum_4`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '5') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = year(now()))),`k`.`COST_VALUE`,0)) AS `sum_5`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '6') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = year(now()))),`k`.`COST_VALUE`,0)) AS `sum_6`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '7') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = year(now()))),`k`.`COST_VALUE`,0)) AS `sum_7`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '8') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = year(now()))),`k`.`COST_VALUE`,0)) AS `sum_8`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '9') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = year(now()))),`k`.`COST_VALUE`,0)) AS `sum_9`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '10') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = year(now()))),`k`.`COST_VALUE`,0)) AS `sum_10`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '11') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = year(now()))),`k`.`COST_VALUE`,0)) AS `sum_11`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '12') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = year(now()))),`k`.`COST_VALUE`,0)) AS `sum_12`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '13') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = year(now()))),`k`.`COST_VALUE`,0)) AS `sum_13`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '0') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) - 1))),`k`.`COST_VALUE`,0)) AS `sum_ly_0`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '1') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) - 1))),`k`.`COST_VALUE`,0)) AS `sum_ly_1`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '2') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) - 1))),`k`.`COST_VALUE`,0)) AS `sum_ly_2`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '3') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) - 1))),`k`.`COST_VALUE`,0)) AS `sum_ly_3`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '4') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) - 1))),`k`.`COST_VALUE`,0)) AS `sum_ly_4`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '5') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) - 1))),`k`.`COST_VALUE`,0)) AS `sum_ly_5`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '6') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) - 1))),`k`.`COST_VALUE`,0)) AS `sum_ly_6`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '7') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) - 1))),`k`.`COST_VALUE`,0)) AS `sum_ly_7`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '8') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) - 1))),`k`.`COST_VALUE`,0)) AS `sum_ly_8`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '9') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) - 1))),`k`.`COST_VALUE`,0)) AS `sum_ly_9`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '10') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) - 1))),`k`.`COST_VALUE`,0)) AS `sum_ly_10`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '11') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) - 1))),`k`.`COST_VALUE`,0)) AS `sum_ly_11`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '12') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) - 1))),`k`.`COST_VALUE`,0)) AS `sum_ly_12`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '13') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) - 1))),`k`.`COST_VALUE`,0)) AS `sum_ly_13`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '0') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) + 1))),`k`.`COST_VALUE`,0)) AS `sum_ny_0`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '1') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) + 1))),`k`.`COST_VALUE`,0)) AS `sum_ny_1`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '2') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) + 1))),`k`.`COST_VALUE`,0)) AS `sum_ny_2`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '3') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) + 1))),`k`.`COST_VALUE`,0)) AS `sum_ny_3`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '4') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) + 1))),`k`.`COST_VALUE`,0)) AS `sum_ny_4`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '5') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) + 1))),`k`.`COST_VALUE`,0)) AS `sum_ny_5`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '6') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) + 1))),`k`.`COST_VALUE`,0)) AS `sum_ny_6`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '7') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) + 1))),`k`.`COST_VALUE`,0)) AS `sum_ny_7`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '8') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) + 1))),`k`.`COST_VALUE`,0)) AS `sum_ny_8`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '9') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) + 1))),`k`.`COST_VALUE`,0)) AS `sum_ny_9`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '10') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) + 1))),`k`.`COST_VALUE`,0)) AS `sum_ny_10`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '11') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) + 1))),`k`.`COST_VALUE`,0)) AS `sum_ny_11`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '12') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) + 1))),`k`.`COST_VALUE`,0)) AS `sum_ny_12`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '13') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) + 1))),`k`.`COST_VALUE`,0)) AS `sum_ny_13` from `IN7_DZIENNIK_KORESP` `k` where (`k`.`COST_VALUE` <> '') group by `k`.`ID_PROJECT`;

";





$sql['view__in7_dziennik_koresp_budget_main_view']="
CREATE or replace ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `in7_dziennik_koresp_budget_main_view` AS select cast(substring_index(substring_index(`k`.`path`,'-',2),'-',-(1)) as signed) AS `ID`,substring_index(`k`.`path`,'-',2) AS `path`,cast(concat(',',group_concat(distinct `k`.`ID_PROJECT` order by `k`.`ID_PROJECT` ASC separator ','),',') as char charset utf8) AS `ID_PROJECT`,year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) AS `rok`, sum(`k`.`COST_VALUE`) AS `sum_k`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '0') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = year(now()))),`k`.`COST_VALUE`,0)) AS `sum_0`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '1') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = year(now()))),`k`.`COST_VALUE`,0)) AS `sum_1`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '2') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = year(now()))),`k`.`COST_VALUE`,0)) AS `sum_2`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '3') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = year(now()))),`k`.`COST_VALUE`,0)) AS `sum_3`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '4') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = year(now()))),`k`.`COST_VALUE`,0)) AS `sum_4`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '5') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = year(now()))),`k`.`COST_VALUE`,0)) AS `sum_5`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '6') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = year(now()))),`k`.`COST_VALUE`,0)) AS `sum_6`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '7') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = year(now()))),`k`.`COST_VALUE`,0)) AS `sum_7`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '8') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = year(now()))),`k`.`COST_VALUE`,0)) AS `sum_8`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '9') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = year(now()))),`k`.`COST_VALUE`,0)) AS `sum_9`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '10') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = year(now()))),`k`.`COST_VALUE`,0)) AS `sum_10`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '11') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = year(now()))),`k`.`COST_VALUE`,0)) AS `sum_11`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '12') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = year(now()))),`k`.`COST_VALUE`,0)) AS `sum_12`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '13') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = year(now()))),`k`.`COST_VALUE`,0)) AS `sum_13`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '0') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) - 1))),`k`.`COST_VALUE`,0)) AS `sum_ly_0`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '1') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) - 1))),`k`.`COST_VALUE`,0)) AS `sum_ly_1`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '2') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) - 1))),`k`.`COST_VALUE`,0)) AS `sum_ly_2`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '3') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) - 1))),`k`.`COST_VALUE`,0)) AS `sum_ly_3`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '4') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) - 1))),`k`.`COST_VALUE`,0)) AS `sum_ly_4`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '5') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) - 1))),`k`.`COST_VALUE`,0)) AS `sum_ly_5`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '6') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) - 1))),`k`.`COST_VALUE`,0)) AS `sum_ly_6`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '7') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) - 1))),`k`.`COST_VALUE`,0)) AS `sum_ly_7`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '8') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) - 1))),`k`.`COST_VALUE`,0)) AS `sum_ly_8`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '9') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) - 1))),`k`.`COST_VALUE`,0)) AS `sum_ly_9`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '10') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) - 1))),`k`.`COST_VALUE`,0)) AS `sum_ly_10`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '11') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) - 1))),`k`.`COST_VALUE`,0)) AS `sum_ly_11`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '12') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) - 1))),`k`.`COST_VALUE`,0)) AS `sum_ly_12`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '13') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) - 1))),`k`.`COST_VALUE`,0)) AS `sum_ly_13`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '0') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) + 1))),`k`.`COST_VALUE`,0)) AS `sum_ny_0`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '1') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) + 1))),`k`.`COST_VALUE`,0)) AS `sum_ny_1`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '2') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) + 1))),`k`.`COST_VALUE`,0)) AS `sum_ny_2`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '3') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) + 1))),`k`.`COST_VALUE`,0)) AS `sum_ny_3`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '4') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) + 1))),`k`.`COST_VALUE`,0)) AS `sum_ny_4`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '5') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) + 1))),`k`.`COST_VALUE`,0)) AS `sum_ny_5`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '6') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) + 1))),`k`.`COST_VALUE`,0)) AS `sum_ny_6`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '7') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) + 1))),`k`.`COST_VALUE`,0)) AS `sum_ny_7`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '8') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) + 1))),`k`.`COST_VALUE`,0)) AS `sum_ny_8`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '9') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) + 1))),`k`.`COST_VALUE`,0)) AS `sum_ny_9`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '10') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) + 1))),`k`.`COST_VALUE`,0)) AS `sum_ny_10`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '11') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) + 1))),`k`.`COST_VALUE`,0)) AS `sum_ny_11`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '12') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) + 1))),`k`.`COST_VALUE`,0)) AS `sum_ny_12`,sum(if(((month(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = '13') and (year(`k`.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) + 1))),`k`.`COST_VALUE`,0)) AS `sum_ny_13` from `IN7_DZIENNIK_KORESP` `k` where (`k`.`COST_VALUE` <> '') group by substring_index(`k`.`path`,'-',2);

";


$sql['view__budget_project_to_realization_view']="
CREATE or replace ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `budget_project_to_realization_view` AS select `p`.`ID` AS `ID`,`p`.`ID_PROJECT` AS `ID_PROJECT`,`k`.`path` AS `path`,`p`.`YEAR` AS `YEAR`,`p`.`MONTH_1_VALUE` AS `MONTH_1_VALUE`,`k`.`sum_1` AS `sum_1`,`p`.`MONTH_2_VALUE` AS `MONTH_2_VALUE`,`k`.`sum_2` AS `sum_2`,`p`.`MONTH_3_VALUE` AS `MONTH_3_VALUE`,`k`.`sum_3` AS `sum_3`,`p`.`MONTH_4_VALUE` AS `MONTH_4_VALUE`,`k`.`sum_4` AS `sum_4`,`p`.`MONTH_5_VALUE` AS `MONTH_5_VALUE`,`k`.`sum_5` AS `sum_5`,`p`.`MONTH_6_VALUE` AS `MONTH_6_VALUE`,`k`.`sum_6` AS `sum_6`,`p`.`MONTH_7_VALUE` AS `MONTH_7_VALUE`,`k`.`sum_7` AS `sum_7`,`p`.`MONTH_8_VALUE` AS `MONTH_8_VALUE`,`k`.`sum_8` AS `sum_8`,`p`.`MONTH_9_VALUE` AS `MONTH_9_VALUE`,`k`.`sum_9` AS `sum_9`,`p`.`MONTH_10_VALUE` AS `MONTH_10_VALUE`,`k`.`sum_10` AS `sum_10`,`p`.`MONTH_11_VALUE` AS `MONTH_11_VALUE`,`k`.`sum_11` AS `sum_11`,`p`.`MONTH_12_VALUE` AS `MONTH_12_VALUE`,`k`.`sum_12` AS `sum_12` from (`projects_budget_year_month` `p` left join `in7_dziennik_koresp_budget_view` `k` on(((`k`.`ID_PROJECT` = `p`.`ID_PROJECT`) and (`p`.`YEAR` = `k`.`rok`))));
";


$sql['view__budget_project_to_realization_main_view']="
CREATE or replace ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `budget_project_to_realization_main_view` AS select `p`.`ID` AS `ID`,`p`.`ID_PROJECT` AS `ID_PROJECT`,`k`.`path` AS `path`,`p`.`YEAR` AS `YEAR`,`p`.`MONTH_1_VALUE` AS `MONTH_1_VALUE`,`k`.`sum_1` AS `sum_1`,`p`.`MONTH_2_VALUE` AS `MONTH_2_VALUE`,`k`.`sum_2` AS `sum_2`,`p`.`MONTH_3_VALUE` AS `MONTH_3_VALUE`,`k`.`sum_3` AS `sum_3`,`p`.`MONTH_4_VALUE` AS `MONTH_4_VALUE`,`k`.`sum_4` AS `sum_4`,`p`.`MONTH_5_VALUE` AS `MONTH_5_VALUE`,`k`.`sum_5` AS `sum_5`,`p`.`MONTH_6_VALUE` AS `MONTH_6_VALUE`,`k`.`sum_6` AS `sum_6`,`p`.`MONTH_7_VALUE` AS `MONTH_7_VALUE`,`k`.`sum_7` AS `sum_7`,`p`.`MONTH_8_VALUE` AS `MONTH_8_VALUE`,`k`.`sum_8` AS `sum_8`,`p`.`MONTH_9_VALUE` AS `MONTH_9_VALUE`,`k`.`sum_9` AS `sum_9`,`p`.`MONTH_10_VALUE` AS `MONTH_10_VALUE`,`k`.`sum_10` AS `sum_10`,`p`.`MONTH_11_VALUE` AS `MONTH_11_VALUE`,`k`.`sum_11` AS `sum_11`,`p`.`MONTH_12_VALUE` AS `MONTH_12_VALUE`,`k`.`sum_12` AS `sum_12` from (`projects_budget_year_month` `p` left join `in7_dziennik_koresp_budget_main_view` `k` on(((`k`.`ID_PROJECT` = `p`.`ID_PROJECT`) and (`p`.`YEAR` = `k`.`rok`))));
";


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




$sql['view__TASKS_INFO_trigger']="
CREATE or replace ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `_TASKS_INFO_trigger` AS
SELECT CONCAT(  'W:', SUM( IF( t.A_STATUS =  'WAITING', 1, 0 ) ) ,  '/N:', SUM( IF( t.A_STATUS =  'NORMAL', 1, 0 ) ) , '/Z:', SUM( IF( t.A_STATUS =  'OFF_HARD', 1, 0 ) ) ) AS TASKS_INFO_trigger,
SUM( IF( t.A_STATUS =  'WAITING', 1, 0 ) ) as WAITING,
SUM( IF( t.A_STATUS =  'NORMAL', 1, 0 ) ) as NORMAL,
SUM( IF( t.A_STATUS =  'OFF_SOFT', 1, 0 ) ) as OFF_SOFT,
SUM( IF( t.A_STATUS =  'OFF_HARD', 1, 0 ) ) as OFF_HARD,
 t.ID_PROJECT
FROM PROBLEMS AS t
GROUP BY ID_PROJECT";


$sql['drop__PROBLEMS_TASKS_INFO_trigger_INSERT']="DROP TRIGGER IF EXISTS  `_PROBLEMS_TASKS_INFO_trigger_INSERT`";
$sql['create__PROBLEMS_TASKS_INFO_trigger_INSERT']="CREATE DEFINER=`root`@`localhost` TRIGGER `_PROBLEMS_TASKS_INFO_trigger_INSERT`
AFTER INSERT ON `PROBLEMS` FOR EACH ROW BEGIN

	UPDATE IN7_MK_BAZA_DYSTRYBUCJI , _TASKS_INFO_trigger
	set IN7_MK_BAZA_DYSTRYBUCJI.TASKS_INFO_trigger = _TASKS_INFO_trigger.TASKS_INFO_trigger  where IN7_MK_BAZA_DYSTRYBUCJI.ID=NEW.ID_PROJECT and _TASKS_INFO_trigger.ID_PROJECT=NEW.ID_PROJECT;
END
";

$sql['drop__PROBLEMS_TASKS_INFO_trigger_UPDATE']="DROP TRIGGER IF EXISTS  `_PROBLEMS_TASKS_INFO_trigger_UPDATE`";
$sql['create__PROBLEMS_TASKS_INFO_trigger_UPDATE']="CREATE DEFINER=`root`@`localhost` TRIGGER `_PROBLEMS_TASKS_INFO_trigger_UPDATE` AFTER UPDATE ON `PROBLEMS` FOR EACH ROW BEGIN

	UPDATE IN7_MK_BAZA_DYSTRYBUCJI , _TASKS_INFO_trigger
	set IN7_MK_BAZA_DYSTRYBUCJI.TASKS_INFO_trigger = _TASKS_INFO_trigger.TASKS_INFO_trigger  where IN7_MK_BAZA_DYSTRYBUCJI.ID=NEW.ID_PROJECT and _TASKS_INFO_trigger.ID_PROJECT=NEW.ID_PROJECT;
END
";








$sql['view__IN7_DZIENNIK_KORESP_trigger']="
CREATE or replace ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `_IN7_DZIENNIK_KORESP_trigger` AS
SELECT CONCAT(  'W:', SUM( IF( t.A_STATUS =  'WAITING', 1, 0 ) ) ,  '/N:', SUM( IF( t.A_STATUS =  'NORMAL', 1, 0 ) ) , '/Z:', SUM( IF( t.A_STATUS =  'OFF_HARD', 1, 0 ) ) ) AS IN7_DZIENNIK_KORESP_INFO_trigger,
SUM( IF( t.A_STATUS =  'WAITING', 1, 0 ) ) as WAITING,
SUM( IF( t.A_STATUS =  'NORMAL', 1, 0 ) ) as NORMAL,
SUM( IF( t.A_STATUS =  'OFF_SOFT', 1, 0 ) ) as OFF_SOFT,
SUM( IF( t.A_STATUS =  'OFF_HARD', 1, 0 ) ) as OFF_HARD,
 t.ID_PROJECT
FROM IN7_DZIENNIK_KORESP AS t
GROUP BY ID_PROJECT";




/*  - nie dziala, bo jest trigger w bash_file_perms!!!
$sql['drop__IN7_DZIENNIK_KORESP_trigger_INSERT']="DROP TRIGGER IF EXISTS  `_IN7_DZIENNIK_KORESP_trigger_INSERT`";
$sql['create__PROBLEMS_TASKS_INFO_trigger_INSERT']="CREATE DEFINER=`root`@`localhost` TRIGGER `_IN7_DZIENNIK_KORESP_trigger_INSERT`
AFTER INSERT ON `IN7_DZIENNIK_KORESP` FOR EACH ROW BEGIN

	UPDATE IN7_MK_BAZA_DYSTRYBUCJI , _IN7_DZIENNIK_KORESP_trigger
	set IN7_MK_BAZA_DYSTRYBUCJI.IN7_DZIENNIK_KORESP_INFO_trigger = _IN7_DZIENNIK_KORESP_trigger.IN7_DZIENNIK_KORESP_INFO_trigger  where IN7_MK_BAZA_DYSTRYBUCJI.ID=NEW.ID_PROJECT and _TASKS_INFO_trigger.ID_PROJECT=NEW.ID_PROJECT;
END
";

$sql['drop__IN7_DZIENNIK_KORESP_trigger_UPDATE']="DROP TRIGGER IF EXISTS  `_IN7_DZIENNIK_KORESP_trigger_UPDATE`";
$sql['create__PROBLEMS_TASKS_INFO_trigger_UPDATE']="CREATE DEFINER=`root`@`localhost` TRIGGER `_IN7_DZIENNIK_KORESP_trigger_UPDATE` AFTER UPDATE ON `IN7_DZIENNIK_KORESP` FOR EACH ROW BEGIN

	UPDATE IN7_MK_BAZA_DYSTRYBUCJI , _IN7_DZIENNIK_KORESP_trigger
	set IN7_MK_BAZA_DYSTRYBUCJI.IN7_DZIENNIK_KORESP_INFO_trigger = _IN7_DZIENNIK_KORESP_trigger.IN7_DZIENNIK_KORESP_INFO_trigger  where IN7_MK_BAZA_DYSTRYBUCJI.ID=NEW.ID_PROJECT and _IN7_DZIENNIK_KORESP_trigger.ID_PROJECT=NEW.ID_PROJECT;
END
";

*/


// W zastepstwie poki nie bedzie triggera na KORESPONDENCJI
$sql['_IN7_DZIENNIK_KORESP_trigger_event_drop']="DROP EVENT if exists _IN7_DZIENNIK_KORESP_trigger_event";


$sql['_IN7_DZIENNIK_KORESP_trigger_event']="CREATE EVENT `_IN7_DZIENNIK_KORESP_trigger_event` ON SCHEDULE EVERY 1 DAY STARTS '2015-05-12 00:04:30' ON COMPLETION NOT PRESERVE ENABLE DO

 UPDATE IN7_MK_BAZA_DYSTRYBUCJI , _IN7_DZIENNIK_KORESP_trigger
	set IN7_MK_BAZA_DYSTRYBUCJI.IN7_DZIENNIK_KORESP_INFO_trigger = _IN7_DZIENNIK_KORESP_trigger.IN7_DZIENNIK_KORESP_INFO_trigger  where IN7_MK_BAZA_DYSTRYBUCJI.ID=_IN7_DZIENNIK_KORESP_trigger.ID_PROJECT ;";







$sql['view__budget_project_to_realization_main_event_drop']="DROP EVENT if exists view__budget_project_to_realization_main_event";


$sql['view__budget_project_to_realization_main_event']="CREATE EVENT `view__budget_project_to_realization_main_event` ON SCHEDULE EVERY 1 DAY STARTS '2015-05-12 00:04:30' ON COMPLETION NOT PRESERVE ENABLE DO INSERT IGNORE INTO `projects_budget_year_month` ( `ID_PROJECT` , `M_DIST_DESC` , `YEAR` ,`A_RECORD_CREATE_AUTHOR` , `A_RECORD_CREATE_DATE` )
SELECT t1.`ID` , t2.M_DIST_DESC, t1.`rok` , 'import z widoku budzetu glownych spraw (view__budget_project_to_realization_main_event)', NOW( )
FROM `in7_dziennik_koresp_budget_main_view` AS t1
LEFT JOIN IN7_MK_BAZA_DYSTRYBUCJI AS t2 ON t1.ID = t2.ID;";




//! POPC_UPDATE eventy stats

$sql['POPC_UPDATE_event_drop']="DROP EVENT if exists POPC_UPDATE";


$sql['POPC_UPDATE_event']="CREATE EVENT `POPC_UPDATE` ON SCHEDULE EVERY 1 DAY STARTS '2015-05-12 00:04:30' ON COMPLETION NOT PRESERVE ENABLE DO
BEGIN
create temporary table ST_POPC_AKT as
select  t1.ID, count(t2.ID) as cache_count_POPC , 
sum(if(t2.Rodzaj_obiektu like '%Plac%owa',t2.Liczba_placowek_publicznych,0)) as cache_count_POPC_plac ,
sum(if(t2.Rodzaj_obiektu like '%Plac%owa%A',t2.Liczba_placowek_publicznych,0)) as cache_count_POPC_plac_os_A ,
sum(t2.Liczba_lokali) as cache_count_POPC_lokali 
from MK_Rewiry as t1 
join MK_BIALE_PLAMY as t2 on ST_Contains(t1.the_geom, t2.the_geom)
-- where t2.NAZWA_OBSZARU like 'GDA%KI I TR%KI' 
group by t1.ID ;
update MK_Rewiry as t1, ST_POPC_AKT as t2 set t1.cache_count_POPC=t2.cache_count_POPC ,
t1.cache_count_POPC_plac=t2.cache_count_POPC_plac , 
t1.cache_count_POPC_plac_os_A=t2.cache_count_POPC_plac_os_A , 
t1.cache_count_POPC_lokali=t2.cache_count_POPC_lokali
where t1.ID=t2.ID;
END
";

// EOF POPC_UPDATE eventy stats


//! ST__MK_Rewiry_to_BUILDINGS
$sql['ST__MK_Rewiry_to_BUILDINGS_event_drop']="DROP EVENT if exists ST__MK_Rewiry_to_BUILDINGS";
$sql['ST__MK_Rewiry_to_BUILDINGS_event']="CREATE EVENT `ST__MK_Rewiry_to_BUILDINGS` ON SCHEDULE EVERY 1 DAY STARTS '2015-05-12 00:04:30' ON COMPLETION NOT PRESERVE ENABLE DO
update BUILDINGS as t1, MK_Rewiry as t2 , USERS2_MARKETING t3 set t1.M_REWIR=t2.NAZWA_REWI , t3.M_REWIR=t2.NAZWA_REWI
where  ST_Contains(t2.the_geom, t1.the_geom) and t3.T_TELBOX_BUILDING_IN=t1.S_ADDRESS_STREET
";
// EOF ST__MK_Rewiry_to_BUILDINGS


//! bzyka funkcje @2015-07 do tree zasobow aliasow
$sql['drop_function_ALIAS_CHILDS_FROM_CRM_LISTA_ZASOBOW']="drop function if exists ALIAS_CHILDS_FROM_CRM_LISTA_ZASOBOW";
$sql['create_function_ALIAS_CHILDS_FROM_CRM_LISTA_ZASOBOW']="CREATE DEFINER=`root`@`localhost` FUNCTION `ALIAS_CHILDS_FROM_CRM_LISTA_ZASOBOW`(`CRM_LISTA_ZASOBOW_ID` INT) RETURNS text CHARSET latin2
   READS SQL DATA
BEGIN
   SELECT GROUP_CONCAT(`ID`)
       INTO @RESULT
       FROM `CRM_LISTA_ZASOBOW`
       WHERE `ALIAS_ID`=CRM_LISTA_ZASOBOW_ID;

   RETURN COALESCE(@RESULT,'');
END";

$sql['drop_function_ALIAS_PATH_FROM_CRM_LISTA_ZASOBOW']="drop function if exists ALIAS_PATH_FROM_CRM_LISTA_ZASOBOW";
$sql['create_function_ALIAS_PATH_FROM_CRM_LISTA_ZASOBOW']="CREATE DEFINER=`root`@`localhost` FUNCTION `ALIAS_PATH_FROM_CRM_LISTA_ZASOBOW`(`CRM_LISTA_ZASOBOW_ID` INT) RETURNS text CHARSET latin2
   READS SQL DATA
BEGIN
   SET @PARAM_ALIAS_ID=CRM_LISTA_ZASOBOW_ID;
   SET @PATH='';
   REPEAT
       SELECT `ALIAS_ID`
           INTO @ALIAS_ID
           FROM `CRM_LISTA_ZASOBOW`
           WHERE `ID`=@PARAM_ALIAS_ID;

       IF @ALIAS_ID>0 AND @PATH!='' THEN
           SET @PATH=CONCAT(',',@PATH);
       END IF;

       IF @ALIAS_ID=@PARAM_ALIAS_ID OR FIND_IN_SET(@ALIAS_ID,@PATH) THEN
           SET @PATH=CONCAT('LOOP_ERROR,',@ALIAS_ID,@PATH);
           SET @ALIAS_ID=0;
       END IF;

       IF @ALIAS_ID IS NULL THEN
           SET @PATH=CONCAT('BRAK_ZASOBU',@PATH);
       ELSEIF @ALIAS_ID>0 THEN
           SET @PATH=CONCAT(@ALIAS_ID,@PATH);
       END IF;

       SET @PARAM_ALIAS_ID=@ALIAS_ID;
   UNTIL COALESCE(@ALIAS_ID,0)=0 END REPEAT;
   RETURN @PATH;
END";




$sql['view__CRM_LISTA_ZASOBOW_ALIASY_view']="CREATE or replace ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `CRM_LISTA_ZASOBOW_ALIASY_view` AS select `CRM_LISTA_ZASOBOW`.`ID` AS `ID`,`CRM_LISTA_ZASOBOW`.`ID` AS `CRM_LISTA_ZASOBOW_ID`,`ALIAS_CHILDS_FROM_CRM_LISTA_ZASOBOW`(`CRM_LISTA_ZASOBOW`.`ID`) AS `CHILDS`,`ALIAS_PATH_FROM_CRM_LISTA_ZASOBOW`(`CRM_LISTA_ZASOBOW`.`ID`) AS `PATH` from `CRM_LISTA_ZASOBOW`";






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

$sql['drop__CRM_TESTY_PYTANIA__AFTER__INSERT'] = "DROP TRIGGER IF EXISTS  `_CRM_TESTY_PYTANIA__AFTER__INSERT`";
$sql['create__CRM_TESTY_PYTANIA__AFTER__INSERT'] = "CREATE DEFINER=`root`@`localhost` TRIGGER `_CRM_TESTY_PYTANIA__AFTER__INSERT`
	AFTER INSERT ON `CRM_TESTY_PYTANIA` FOR EACH ROW BEGIN
		update `CRM_PROCES` as p
			set p.`TEST_PYTANIE`=(
				select count(1)
				from `CRM_TESTY_PYTANIA` as pyt
				where
					pyt.`ID_PROCES`=p.ID
			);
END
";
$sql['drop__CRM_TESTY_PYTANIA__AFTER__DELETE'] = "DROP TRIGGER IF EXISTS  `_CRM_TESTY_PYTANIA__AFTER__DELETE`";
$sql['create__CRM_TESTY_PYTANIA__AFTER__DELETE'] = "CREATE DEFINER=`root`@`localhost` TRIGGER `_CRM_TESTY_PYTANIA__AFTER__DELETE`
	AFTER DELETE ON `CRM_TESTY_PYTANIA` FOR EACH ROW BEGIN
		update `CRM_PROCES` as p
			set p.`TEST_PYTANIE`=(
				select count(1)
				from `CRM_TESTY_PYTANIA` as pyt
				where
					pyt.`ID_PROCES`=p.ID
			);
END
";






$sql['replace__RozdzielczaObiekty_wiev']="create or replace definer=`root`@`localhost` view RozdzielczaObiekty_wiev as
select concat('rp5mm',ID) as ID, 'Rozdzielcza_Pakiet5MikrorurekMagistrala_WSG84' as TABLE_name, ID as TABLE_ID,  A_STATUS, A_STATUS_INFO, L_APPOITMENT_USER, L_APPOITMENT_INFO, A_ADM_COMPANY, A_CLASSIFIED, the_geom 
, A_RECORD_UPDATE_DATE, A_RECORD_UPDATE_AUTHOR, A_RECORD_CREATE_DATE, A_RECORD_CREATE_AUTHOR
from Rozdzielcza_Pakiet5MikrorurekMagistrala_WSG84
union 
select concat('rmk',ID) as ID, 'Rozdzielcza_Mikrokanalizacja_do_klienta' as TABLE_name, ID as TABLE_ID, A_STATUS, A_STATUS_INFO, L_APPOITMENT_USER, L_APPOITMENT_INFO, A_ADM_COMPANY, A_CLASSIFIED, the_geom
, A_RECORD_UPDATE_DATE, A_RECORD_UPDATE_AUTHOR, A_RECORD_CREATE_DATE, A_RECORD_CREATE_AUTHOR from Rozdzielcza_Mikrokanalizacja_do_klienta

union
select concat('rsw',ID) as ID, 'Rozdzielcza_struktura_wewnetrzna_wsg84' as TABLE_name, ID as TABLE_ID, A_STATUS, A_STATUS_INFO, L_APPOITMENT_USER, L_APPOITMENT_INFO, A_ADM_COMPANY, A_CLASSIFIED, the_geom 
, A_RECORD_UPDATE_DATE, A_RECORD_UPDATE_AUTHOR, A_RECORD_CREATE_DATE, A_RECORD_CREATE_AUTHOR
from Rozdzielcza_struktura_wewnetrzna_wsg84

union
select concat('rwk',ID) as ID, 'Rozdzielcza_wewn_kabel_ethernet' as TABLE_name, ID as TABLE_ID, A_STATUS, A_STATUS_INFO, L_APPOITMENT_USER, L_APPOITMENT_INFO, A_ADM_COMPANY, A_CLASSIFIED, the_geom 
, A_RECORD_UPDATE_DATE, A_RECORD_UPDATE_AUTHOR, A_RECORD_CREATE_DATE, A_RECORD_CREATE_AUTHOR
from Rozdzielcza_wewn_kabel_ethernet 


union
select concat('rr',ID) as ID,  'Rozdzielcza_rurociag_wsg84' as TABLE_name, ID as TABLE_ID,A_STATUS, A_STATUS_INFO, L_APPOITMENT_USER, L_APPOITMENT_INFO, A_ADM_COMPANY, A_CLASSIFIED, the_geom 
, A_RECORD_UPDATE_DATE, A_RECORD_UPDATE_AUTHOR, A_RECORD_CREATE_DATE, A_RECORD_CREATE_AUTHOR
from Rozdzielcza_rurociag_wsg84 


union
select concat('rwp',ID) as ID, 'Rozdzielcza_Wykop_przedmiar_na_mikrorurki' as TABLE_name, ID as TABLE_ID, A_STATUS, A_STATUS_INFO, L_APPOITMENT_USER, L_APPOITMENT_INFO, A_ADM_COMPANY, A_CLASSIFIED, the_geom 
, A_RECORD_UPDATE_DATE, A_RECORD_UPDATE_AUTHOR, A_RECORD_CREATE_DATE, A_RECORD_CREATE_AUTHOR
from Rozdzielcza_Wykop_przedmiar_na_mikrorurki 


union
select concat('rks',ID) as ID, 'Rozdzielcza_Kabel_Swiatlowodowy_wsg84' as TABLE_name, ID as TABLE_ID, A_STATUS, A_STATUS_INFO, L_APPOITMENT_USER, L_APPOITMENT_INFO, A_ADM_COMPANY, A_CLASSIFIED, the_geom
, A_RECORD_UPDATE_DATE, A_RECORD_UPDATE_AUTHOR, A_RECORD_CREATE_DATE, A_RECORD_CREATE_AUTHOR
 from Rozdzielcza_Kabel_Swiatlowodowy_wsg84 


";






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

	Lib::loadClass('Router');

	$routeConfig = Router::getRoute('Config');
	$routeConfig->reinstall();

}

?>