 SET NAMES utf8;

DELIMITER $$

/*
SET @app='M.WEB.PortaFide.www';
SET @app='WEBCRM';

CALL web_vars('EXMPLE.DEBUG', @app, @sess, 'varname','varvalue','vartype','a','b','c');

CALL web_vars('SET_VAR',@app, @sess,'varname','varvalue','vartype','delay','','');
CALL web_vars('SET_VAR',@app, @sess,'lista_obecnosci_wg','','list','','','');

CALL web_vars('SET_GLOBAL', @app, @sess, 'nrwewn_aktywny','nie','list','','','');
CALL web_vars('SET_GLOBAL', @app, @sess, 'varname','varvalue','vartype','','','');
CALL web_vars('SET_GLOBAL', @app, @sess, 'varname','varvalue','vartype','root','',''); -- readonly global

CALL web_vars('GET_VAR', @app, @sess, 'varname','','','','','');
CALL web_vars('GET_VARS', @app, @sess, '','','','','','');
CALL web_vars('GET_VAR_DICT', @app, @sess, 'catalog_mode','','','','','');
CALL web_vars('GET_GLOBAL', @app, @sess, 'varname','','','','','');
CALL web_vars('GET_GLOBALS', @app, @sess, '','','','','','');
CALL web_vars('GET_VAR_DICT', @app, @sess, 'lista_obecnosci_wg','','','','','DEBUG');
*/

-- dodać app, dodać app *

DROP PROCEDURE IF EXISTS web_vars$$
CREATE DEFINER=`root`@`localhost` PROCEDURE web_vars(
    IN _MODE VARCHAR(200),
    IN _APP VARCHAR(38),
    IN _SessionGUID VARCHAR(38),
    IN _VarName VARCHAR(255),
    IN _VarValue MEDIUMTEXT,
    IN _VarType VARCHAR(255),
    IN _ParamA VARCHAR(255),
    IN _ParamB MEDIUMTEXT,
    IN _ParamC MEDIUMTEXT
)

SPROC: BEGIN

DECLARE _DEBUG BOOLEAN;
DECLARE _ADMIN INT;
DECLARE _MIN1 INT;

DECLARE _USERLOGIN VARCHAR(38);
DECLARE _USERGUID VARCHAR(38);
DECLARE _KONTRGUID VARCHAR(38);
DECLARE _GUID VARCHAR(38);
DECLARE _USERTYPE INT;

SET _DEBUG=_MODE LIKE "%.DEBUG",@web_vars.mode=NULL,_ADMIN=0,_MIN1=0,@session.guid=_SessionGUID;
SET _MODE=REPLACE(_MODE,'.DEBUG','');

IF @session.globals.Owner IS NULL THEN
    SELECT value INTO @session.globals.Owner FROM _db_globals g WHERE g.key='Owner';
END IF;

SELECT log_user,log_USER_GUID,IFNULL(status,"")="ACTIVE" INTO _USERLOGIN,_USERGUID,@session.status FROM _sys_db_logons WHERE guid=_SessionGUID;

IF _SessionGUID IS NULL THEN
    SELECT idnuser,guid,1 INTO _USERLOGIN,_USERGUID,@session.status from osoby where idnuser='jmam';
END IF; 

SELECT user_type,administrator INTO _USERTYPE,_ADMIN FROM osoby WHERE guid=_USERGUID;

IF _DEBUG THEN
    SELECT _MODE as mode,_APP as app,_SessionGUID as sess,_ADMIN as admin,_VarName as varname,_VarValue as varvalue,_VarType as vartype,_ParamA as a,_ParamB as b,_ParamC as c;
END IF;

IF _MODE = 'GET_MIME' THEN
    SET @web_vars.mode=_MODE;
    SELECT * from typy_bloba;
END IF;


IF _MODE = 'GET_VAR_DICT' THEN
    SET @web_vars.mode=_MODE;

    CREATE TEMPORARY TABLE dict (val varchar(50), descr varchar(50));
    SELECT varType INTO @varType FROM web_vars WHERE app=_APP AND varName=_VarName LIMIT 1;
    IF @varType='list' THEN
    -- TODO: zrobić z tego słownik
        CASE _VarName
            WHEN 'catalog_mode' THEN
                INSERT INTO dict VALUES ('list',''),('grid',''),('shortlist','');
            WHEN 'lista_obecnosci_wg' THEN
                INSERT INTO dict VALUES ('bez filtracji',''),('wg dzialu',''),('wg kategorii','');
            WHEN 'PP_Wnioski_DEL_KtoKieruje' THEN
                INSERT INTO dict VALUES ('Dowolny kierownik',''),('Kierownik pracownika',''),('Grupa DEL_KIEROWNICY','');
            WHEN 'delegacje_wybor_przelew_gotowka' THEN
                INSERT INTO dict VALUES ('przelew',''),('gotówka',''),('gotówka/przelew','');
            ELSE
                INSERT INTO dict VALUES ('tak',''),('nie','');
        END CASE;
    END IF;
    SELECT * FROM dict;
    DROP TEMPORARY TABLE dict;
END IF;


IF _MODE IN ('SET_VAR','SET_VAR.USER') THEN
    SET @web_vars.mode=_MODE,_MIN1=IF(IFNULL(_ParamA,'')='',0,_VarINT(_ParamA)),@web_vars.idn=NULL; 
    -- _MIN1 = delay - jesli nie zero to ustawiaj tylko starsze niż delay sekund
    SET @varType=IF(_VarType='','string',_VarType);
    IF _MODE='SET_VAR.USER' THEN
        SELECT idn INTO @web_vars.idn FROM web_vars WHERE app=_APP AND userGUID=_USERGUID AND varName=_VarName ORDER BY alocaltime DESC LIMIT 1;
        IF @web_vars.idn IS NOT NULL THEN
            UPDATE web_vars SET varValue=IFNULL(_VarValue,''),sessionGUID=_SessionGUID
            WHERE idn=@web_vars.idn; 
            DELETE FROM web_vars WHERE app=_APP AND userGUID=_USERGUID AND varName=_VarName AND idn<>@web_vars.idn;
        END IF;
    END IF;
    IF  @web_vars.idn IS NULL THEN
        INSERT IGNORE INTO web_vars (app,userGUID,sessionGUID,varName,varValue,varType) 
        VALUES (_APP,_USERGUID,_SessionGUID,_VarName,IFNULL(_VarValue,''),@varType);
        IF ROW_COUNT()=0 THEN
            UPDATE web_vars SET varValue=IFNULL(_VarValue,''),varType=@varType 
            WHERE app=_APP AND userGUID=_USERGUID AND sessionGUID=_SessionGUID AND varName=_VarName AND 
                (_MIN1=0 OR alocaltime<now() - interval _MIN1 second);
        END IF;
    END IF;
END IF;

IF _MODE ='SET_GLOBAL' AND _VarName<>'' THEN
    SET @web_vars.mode=_MODE;
    SET @user=IF(_ParamA='','*',_ParamA);
    SET @varType=IF(_VarType='','string',_VarType);
    IF _ADMIN THEN
        INSERT INTO web_vars (app,userGUID,sessionGUID,varName,varValue,varType) VALUES 
        (_APP,@user,'*',_VarName,IFNULL(_VarValue,''),@varType) ON DUPLICATE KEY UPDATE varValue=_VarValue;
    ELSE 
        SELECT "Tylko ADMIN!";
    END IF;
END IF;


IF _MODE IN ('GET_VAR','GET_VAR_SELECT') THEN
    SET @web_vars.mode=_MODE;
    SELECT varValue INTO @web_vars.varValue FROM web_vars WHERE app=_APP AND sessionGUID=_SessionGUID AND varName=_VarName;
    SET @web_vars.varValue=IFNULL(@web_vars.varValue,'');
    IF _MODE='GET_VAR_SELECT' THEN
        SELECT @web_vars.varValue as varValue;
    END IF;
END IF;

IF _MODE IN ('GET_GLOBAL','GET_GLOBAL_SELECT') THEN
    SET @web_vars.mode=_MODE;
    SET @lang=IF(_ParamA='','',_ParamA),@web_vars.varValue=NULL;
    IF @lang not in ('','pl') THEN
        SELECT tostr INTO @web_vars.varValue FROM lang_translations WHERE /* guid like "SERW%" and */ to_lang=@lang AND fromstr=_VarName and context='globals';
    END IF;
    IF @web_vars.varValue IS NULL THEN
        SELECT varValue INTO @web_vars.varValue FROM web_vars WHERE app=_APP AND sessionGUID='*' AND varName=_VarName;
    END IF;
    SET @web_vars.varValue=IFNULL(@web_vars.varValue,'');
    IF _MODE='GET_GLOBAL_SELECT' THEN
        SELECT @web_vars.varValue as varValue;
    END IF;
END IF;

IF _MODE='GET_VARS' THEN
    SET @web_vars.mode=_MODE;
    SELECT varName,varValue,varType FROM web_vars WHERE app=_APP AND sessionGUID=_SessionGUID;
END IF;

IF _MODE='GET_GLOBALS' THEN
    SET @web_vars.mode=_MODE;
    -- SET @lang=IF(_ParamC='','',_ParamC);
    DROP TEMPORARY TABLE IF EXISTS web_globals;
    CREATE TEMPORARY TABLE web_globals
    SELECT userguid='root' as readonly,varName,varValue,varType FROM web_vars WHERE app=_APP AND sessionGUID='*' and varType<>'html';
    -- UPDATE web_globals g, lang_translations l SET g.varValue=l.tostr WHERE l.guid like "SERW%" and l.to_lang=@lang AND l.fromstr=g.varName and l.context='globals'; 
    SELECT * FROM web_globals;
    DROP TEMPORARY TABLE IF EXISTS web_globals;
END IF;

/*
CALL web_vars('EXMPLE.DEBUG', @app, @sess, 'varname','varvalue','vartype','a','b','c');
CALL web_vars('LANG_LIST',@app, @sess,'','','','','','pl');
set @lang='en';
CALL web_vars('LANG_LIST',@app, @sess,'','','','','',@lang);

*/


IF _MODE='LANG_LIST' THEN
    SET @web_vars.mode=_MODE;
    SET @lang=_ParamC;
    SELECT lang_id as lang,web_translate(name,'side_bar',@lang) as nazwa FROM languages 
    WHERE lang_id in (SELECT DISTINCT to_lang as lang FROM lang_translations WHERE context='admin' AND to_lang<>'') or lang_id='pl';

END IF;

/*
pobiera listę tłumaczeń 
set @lang='en';
CALL web_vars('LANG',@app, @sess,'','','','','',@lang);
testy:
CALL web_vars('LANG',@app, @sess,'','Zapis','','','',@lang);

*/

IF _MODE = 'LANG' THEN
    SET @web_vars.mode=_MODE;
    SET @lang=_ParamC;
    SELECT context,fromstr,if(_ParamC='pl',fromstr,tostr) as tostr,_ParamC as lang 
    FROM lang_translations 
    WHERE to_lang=if(_ParamC='pl','en',_ParamC) -- AND guid like "B2B%"
        AND fromstr LIKE CONCAT('%',_VarValue,'%')
    ORDER BY context,fromstr;
    
    CALL web_vars('SET_VAR.USER',_APP,_SessionGUID,'lang',@lang,'string','','','');
END IF;

/*
SET @tolang='en',@varname='powitanie',@tostr='Welcome';
CALL web_vars('SET_GLOBAL_TRANSL',@app, @sess,@varname,@tostr,'','','',@tolang);
*/

IF _MODE='SET_GLOBAL_TRANSL' THEN
    SET @web_b2b.mode=_MODE;
    SET @lang=_ParamC;
    SET _GUID=CONCAT('VAR',MD5(CONCAT(_VarName,@lang)));

    DELETE FROM lang_translations WHERE guid=_GUID;
    INSERT INTO lang_translations SET tostr=_VarValue,tostr_u=_VarValue, context='globals', guid=_GUID,from_lang='pl',to_lang=@lang,fromstr=_VarName;
    SELECT 'OK' as result;
END IF;


IF _MODE = 'CREATE_TABLES' THEN
    SET @web_vars.mode=_MODE;
    
    CREATE TABLE IF NOT EXISTS web_vars (
        idn int(11) NOT NULL AUTO_INCREMENT,
        app varchar(38) NOT NULL DEFAULT '',
        userGUID varchar(38) NOT NULL DEFAULT '',
        sessionGUID varchar(38) NOT NULL DEFAULT '',
        varName varchar(38) NOT NULL DEFAULT '',
        varValue varchar(1024) NOT NULL DEFAULT '',
        varType varchar(20) NOT NULL DEFAULT '',
        alocaltime timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
        PRIMARY KEY (idn),
        UNIQUE KEY user_sess_var(app,userguid,sessionGUID,varname),
        KEY alocaltime (alocaltime)
    );
    
    SET @sess=_SessionGUID;
END IF;

END $$

/*
global:
SELECT web_vars_get('*','WEBCRM',_SessionGUID,'ceny_widoczne')
sesja
SELECT web_vars_get('VAR','WEBCRM',_SessionGUID,'lang');
user (na podst. sesji)
SELECT web_vars_get('VAR.USER','WEBCRM',_SessionGUID,'miejscedostawy');
SELECT web_vars_get('VAR.USER','WEBCRM',_SessionGUID,'lang');
*/

DROP FUNCTION IF EXISTS web_vars_get $$
CREATE DEFINER=`root`@`localhost` FUNCTION web_vars_get(
    _MODE VARCHAR(200),
    _APP VARCHAR(38),
    _SessionGUID VARCHAR(38),
    _VarName VARCHAR(38)
) RETURNS TEXT DETERMINISTIC
-- dla pustego _MODE zwraca globalne

BEGIN
    SET @web_get.varValue=NULL,@web_get.userguid=NULL;
    IF _MODE='VAR.USER' THEN
        SELECT log_USER_GUID INTO @web_get.userguid FROM _sys_db_logons WHERE guid=_SessionGUID;
        SELECT varValue INTO @web_get.varValue FROM web_vars WHERE _APP in (app,'*') AND UserGUID=@web_get.userguid AND varName=_VarName 
        ORDER BY app=_APP DESC,alocaltime DESC
        LIMIT 1;
    ELSE 
        SELECT varValue INTO @web_get.varValue FROM web_vars WHERE _APP in (app,'*') AND sessionGUID=IF(_MODE='VAR',_SessionGUID,'*') AND varName=_VarName 
        ORDER BY (app=_APP) DESC,alocaltime DESC
        LIMIT 1;
    END IF;
    RETURN @web_get.varValue;
END $$

-- SELECT web_translate('Angielski','side_bar','en') as tostr;


DROP FUNCTION IF EXISTS web_translate $$
CREATE DEFINER=`root`@`localhost` FUNCTION web_translate(
    _FROM TEXT,
    _CONTEXT VARCHAR(255),
    _LANG VARCHAR(38)
) RETURNS TEXT CHARSET UTF8 READS SQL DATA

BEGIN
    SET @web_translate=_FROM;
    IF _LANG not in ('','pl') THEN
        SELECT IFNULL(tostr_u,tostr)
        INTO @web_translate
        FROM lang_translations
        WHERE /*guid like "B2B%" and */ to_lang=_LANG AND fromstr=_FROM AND context=_CONTEXT 
        ;
    END IF;
    RETURN @web_translate;
END $$


-- select web_translate_obj('brak tlumaczenia',@guid,'TowrNg','','en');

DROP FUNCTION IF EXISTS web_b2b_translate_obj $$
CREATE DEFINER=`root`@`localhost` FUNCTION web_b2b_translate_obj(
    _FROM TEXT,
    _OBJ VARCHAR(38),
    _OBJ_TABLE VARCHAR(38),
    _CONTEXT VARCHAR(255),
    _LANG VARCHAR(38)
) RETURNS TEXT CHARSET UTF8 READS SQL DATA

BEGIN
    SET @web_b2b_translate=_FROM;
    SET _CONTEXT=IF(_CONTEXT='',_OBJ,_CONTEXT);

    IF _LANG not in ('','pl') THEN
        SELECT tostr_u INTO @web_b2b_translate FROM lang_translations 
        WHERE object_key=_OBJ and object_name=_OBJ_TABLE and to_lang=_LANG AND context=_CONTEXT; -- czy badanie kontekstu jest tu sensowne?
    END IF;
    RETURN @web_b2b_translate;
END $$

DELIMITER ;

/*
SET @app='M.WEB.PortaFide.www';
alter table lang_translations add column tostr_u varchar(250) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL;

SELECT web_vars_get('',@app, @sess,'debug'),web_vars_get('VAR',@app, @sess,'kolor');
SELECT guid FROM _sys_db_logons WHERE log_user='jmam' ORDER BY idn desc limit 1 INTO @sess;UPDATE _sys_db_logons SET status='ACTIVE' WHERE guid=@sess;

CALL web_vars('SET_VAR',@app, @sess,'test','testowy wpis usera jmam','','','','');

CALL web_vars('SET_GLOBAL',@app, @sess,'appname','Portal Pracowniczy 2.0','','','','');
CALL web_vars('SET_GLOBAL',@app, @sess,'debug','1','boolean','root','','','');
CALL web_vars('SET_GLOBAL',@app, @sess,'mail','biuro@pliszka.pl','','','','','');

CALL web_vars('SET_GLOBAL',@app, @sess,'debugmail','apro68@gmail.com','','','');
CALL web_vars('SET_GLOBAL',@app, @sess,'usluga_wezwanie','USŁ.INNE.WEZWANIE','','','','','');

CALL web_vars('SET_GLOBAL',@app, @sess,'mailfrom','biuro@pliszka.pl','','','','','');
CALL web_vars('SET_GLOBAL',@app, @sess,'mailcc','biuro@pliszka.pl','','','','','');
CALL web_vars('SET_GLOBAL',@app, @sess,'mailbcc','biuro@pliszka.pl','','','','','');
CALL web_vars('SET_GLOBAL',@app, @sess,'mailbrak','Brak maila<biuro@pliszka.pl>','','','','','');
CALL web_vars('SET_GLOBAL',@app, @sess,'www','https://pliszka.pl/','','','','','');
CALL web_vars('SET_GLOBAL',@app, @sess,'logo','https://pliszka.pl/logo.png','','','','','');
CALL web_vars('SET_GLOBAL',@app, @sess,'catalog_mode','list','list','','','','');
CALL web_vars('SET_GLOBAL',@app, @sess,'catalog_mode_details','tak','list','','','','');
CALL web_vars('SET_GLOBAL',@app, @sess,'powitanie','Witaj w systemie Serwis','html','','','','');
CALL web_vars('SET_GLOBAL',@app, @sess,'pomoc_html','<H1>Tu będzie pomoc</H1><BR><BR>pomooooc!','html','','','','');
            
*/


/*
$Log: web_vars.sql,v $
Revision 1.8  2025/09/11 07:21:35  apro
poprawka VAR.USER

Revision 1.7  2025/09/05 12:58:19  apro
..

Revision 1.6  2025/09/03 09:53:17  apro
$SEND$
kosmetyka

Revision 1.5  2025/09/03 09:52:34  apro
lang

Revision 1.4  2025/08/21 12:31:58  apro
porządki, kontekst

*/
