 DELIMITER $$

DROP PROCEDURE IF EXISTS `zest_alerty`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `zest_alerty`(
	IN _MODE VARCHAR(38),
  IN _GUID VARCHAR(38),
  IN _rok VARCHAR(38),
  IN _miesiac VARCHAR(38),
  IN _NaDzien VARCHAR(20),
  IN _OdDnia VARCHAR(10),
  IN _DoDnia VARCHAR(10),
  IN _WHERE_SQL TEXT,
  IN _Param_A VARCHAR(50),
  IN _Param_B VARCHAR(50),
  IN _Param_C VARCHAR(50),
  IN _Param_D VARCHAR(50)
  )


SPROC: BEGIN
    DECLARE _S TEXT DEFAULT '';
    DECLARE _WHERE TEXT DEFAULT '';

		DECLARE _okres VARCHAR(7);
		DECLARE _okres_m01 VARCHAR(7); -- minus 1
		DECLARE _okres_m02 VARCHAR(7); -- minus 2
		DECLARE _okres_m03 VARCHAR(7);
		DECLARE _okres_m04 VARCHAR(7);
		DECLARE _okres_m05 VARCHAR(7);
		DECLARE _okres_m06 VARCHAR(7);
		DECLARE _okres_m07 VARCHAR(7);
		DECLARE _okres_m08 VARCHAR(7);
		DECLARE _okres_m09 VARCHAR(7);
		DECLARE _okres_m10 VARCHAR(7);
		DECLARE _okres_m11 VARCHAR(7);
		DECLARE _okres_m12 VARCHAR(7);
		DECLARE _wstecz INT;
    
		SET _WHERE_SQL = IFNULL(_WHERE_SQL, '');
		SET _GUID = IFNULL(_GUID, '');

/*
Tworca: TK.
Procedura zbierajaca zestawienia dla alertow mailowych.
Bo pisanie samych sqlek w skryptach jest hmm, klopotliwe, no i problem ze zmiennymi.

IZOHAN_01 - zestawienie sprzedazy wg grup odbiorcow, narastajaco
call zest_alerty("IZOHAN_01","","","","","","","","","","","");

ASTE_01 - zestawienie nowych i zmienionych zamowien wg regionow
call zest_alerty("ASTE_01","","","","","","","","","","","");

CRM_01 - zestawienie z crm dla INT - czasy poswiecone dla firm, miesiecznie
CRM_02 - nowsza wersja zestawienia CRM_01

konsekwentnie, zestawienia crm dalej, choć już jako zrodko dla sqd

konkol - alert emalowy z danymi pakowacza, tutaj bo sama procedura daje za duzo danych i select do tego wynikowy, a szkoda tam (czyli w fr_ht_ZM.sql) grzebac
*/

IF (_MODE = 'ASTE_01') THEN

SET @czas = '1 hour';

CREATE TEMPORARY TABLE t_zam
SELECT z.katcol0 as region, MAX(l.idn) AS idn, l.guidlink
FROM `log` l
INNER JOIN zmng z ON z.guid=l.guidlink
WHERE z.deleted=0 AND z.rodzaj=27
AND IF(l.compressed=0,l.info LIKE '%<ROW RowState="8"%',UNCOMPRESS(l.info) LIKE '%<ROW RowState="8"%')
AND z.ilepoz>z.ilepozzreal
AND DATE_ADD(z.localtime,INTERVAL 1 day ) > NOW()
GROUP BY l.guidlink
ORDER BY kontrsymbol;

CREATE TEMPORARY TABLE t_zam2
SELECT region, czas, useridn,l.guidlink AS guid,
SUBSTRING_INDEX(SUBSTRING_INDEX(IF(l.compressed=0,l.info,UNCOMPRESS(l.info)),'<ROW RowState="1"',-1),'<ROW RowState="8"',1) AS bylo,
SUBSTRING_INDEX(IF(l.compressed=0,l.info,UNCOMPRESS(l.info)),'<ROW RowState="8"',-1) AS jest
FROM `log` l
INNER JOIN t_zam t ON t.idn=l.idn;

CREATE TEMPORARY TABLE t_zam2a
SELECT * FROM t_zam2;

CREATE TEMPORARY TABLE t_zam3
SELECT region, 'było' AS stan, z.kontrsymbol,z.nrzamw,z.idnuser,w.symbol AS waluta,
-- REPLACE(CONCAT('#R',IF(LOCATE('nettowal="',bylo)=0,'',_Rnd2(SUBSTRING_INDEX(SUBSTRING_INDEX(bylo,'nettowal="',-1),'" ',1)))),',','.') AS wart_netto,

REPLACE(IF(LOCATE('nettowal="',bylo)=0,'',_Rnd2(SUBSTRING_INDEX(SUBSTRING_INDEX(bylo,'nettowal="',-1),'" ',1))),',','.') AS wart_netto,

IF(LOCATE('terminrealizacji="',bylo)=0,'',SUBSTRING_INDEX(SUBSTRING_INDEX(bylo,'terminrealizacji="',-1),'" ',1)) AS terminreal,
IF(LOCATE('rabat="',bylo)=0,'',SUBSTRING_INDEX(SUBSTRING_INDEX(bylo,'rabat="',-1),'" ',1)) AS rabat,
IF(LOCATE('uwagi="',bylo)=0,'',SUBSTRING_INDEX(SUBSTRING_INDEX(bylo,'uwagi="',-1),'" ',1)) AS uwagi
FROM t_zam2 t
INNER JOIN zmng z ON z.guid=t.guid
INNER JOIN waluty w ON w.idn=z.waluta
UNION
SELECT region, 'jest' AS stan, z.kontrsymbol,z.nrzamw,z.idnuser,w.symbol AS waluta,
ROUND(z.nettowal,2) AS wart_netto,
z.terminrealizacji AS terminreal,
z.rabat AS rabat,
IFNULL(z.uwagi,'') AS uwagi
FROM t_zam2a t
INNER JOIN zmng z ON z.guid=t.guid
INNER JOIN waluty w ON w.idn=z.waluta;

CREATE TABLE t_zam3a
SELECT region, stan, kontrsymbol, nrzamw, idnuser AS wystawil, waluta,
wart_netto,
DATE(terminreal) AS terminreal,
ROUND(rabat,4) AS rabat,
uwagi
FROM t_zam3 ORDER BY kontrsymbol, nrzamw, stan;


CREATE TABLE t_zam4
SELECT nrzamw FROM t_zam3a WHERE wart_netto=0;

DELETE t3 FROM t_zam3a t3, t_zam4 t4 WHERE t3.nrzamw=t4.nrzamw;



END IF;



IF (_MODE = 'IZOHAN_01') THEN

	drop table if exists tz01;

	SET @data=current_date;
	SET @rok=YEAR(@data);
	SET @mc=MONTH(@data);
	SET @yw=LEFT(CONCAT(@rok,"_",RIGHT(YEARWEEK(@data),2)),7);
	SET @yw2=LEFT(CONCAT(@rok-1,"_",RIGHT(YEARWEEK(@data),2)),7);

	create table tz01
	SELECT REPLACE(kontrtree2,'ODBIORCY|','') AS odbiorcy, "narastajaco mc" AS okres,
	_dfmt(SUM(IF(treenode1="IZOHAN" AND rok=@rok AND mc=@mc AND yw<=@yw,wartnet,0))) AS `izohan br`,
	_dfmt(SUM(IF(treenode1="IZOHAN" AND rok=@rok-1 AND mc=@mc AND yw<=@yw2,wartnet,0))) AS `izohan pr`,
	_dfmt(SUM(IF(treenode1="IZOLMAT" AND rok=@rok AND mc=@mc AND yw<=@yw,wartnet,0))) AS `izolmat br`,
	_dfmt(SUM(IF(treenode1="IZOLMAT" AND rok=@rok-1 AND mc=@mc AND yw<=@yw2,wartnet,0))) AS `izolmat pr`,
	_dfmt(SUM(IF(treenode1="NEXLER" AND rok=@rok AND mc=@mc AND yw<=@yw,wartnet,0))) AS `nexler br`,
	_dfmt(SUM(IF(treenode1="NEXLER" AND rok=@rok-1 AND mc=@mc AND yw<=@yw2,wartnet,0))) AS `nexler pr`,
	_dfmt(SUM(IF(treenode1 IN ("NEXLER","IZOLMAT","IZOHAN") AND rok=@rok AND mc=@mc AND yw<=@yw,wartnet,0))) AS `razem br`,
	_dfmt(SUM(IF(treenode1 IN ("NEXLER","IZOLMAT","IZOHAN") AND rok=@rok-1 AND mc=@mc AND yw<=@yw2,wartnet,0))) AS `razem pr`
	FROM temp_olap
	WHERE kontrtree2 LIKE 'ODBIORCY%'
-- IN ("ODBIORCY|HURTOWNIE","ODBIORCY|MARKETY","ODBIORCY|WYKONAWCY","ODBIORCY|DETALIŚCI")
	and rok in (@rok, @rok-1)
	-- AND rok in (2015, 2016)
	GROUP BY kontrtree2
	UNION
	SELECT REPLACE(kontrtree2,'ODBIORCY|','') AS odbiorcy, "narastajaco rok" AS okres,
	_dfmt(SUM(IF(treenode1="IZOHAN" AND rok=@rok AND yw<=@yw,wartnet,0))) AS `izohan br`,
	_dfmt(SUM(IF(treenode1="IZOHAN" AND rok=@rok-1 AND yw<=@yw2,wartnet,0))) AS `izohan pr`,
	_dfmt(SUM(IF(treenode1="IZOLMAT" AND rok=@rok AND yw<=@yw,wartnet,0))) AS `izolmat br`,
	_dfmt(SUM(IF(treenode1="IZOLMAT" AND rok=@rok-1 AND yw<=@yw2,wartnet,0))) AS `izolmat pr`,
	_dfmt(SUM(IF(treenode1="NEXLER" AND rok=@rok AND yw<=@yw,wartnet,0))) AS `nexler br`,
	_dfmt(SUM(IF(treenode1="NEXLER" AND rok=@rok-1 AND yw<=@yw2,wartnet,0))) AS `nexler pr`,
	_dfmt(SUM(IF(treenode1 IN ("NEXLER","IZOLMAT","IZOHAN") AND rok=@rok AND yw<=@yw,wartnet,0))) AS `razem br`,
	_dfmt(SUM(IF(treenode1 IN ("NEXLER","IZOLMAT","IZOHAN") AND rok=@rok-1 AND yw<=@yw2,wartnet,0))) AS `razem pr`
	FROM temp_olap
	WHERE kontrtree2 LIKE 'ODBIORCY%'
	AND rok IN (@rok, @rok-1)
	GROUP BY kontrtree2
	ORDER BY odbiorcy,okres
	;

    
END IF;


-- call zest_alerty("CRM_01","","","","_NaDzien","","","","","","","");
-- CALL zest_alerty("CRM_01","","","",NOW(),"","","","","","","");
IF (_MODE = 'CRM_01') THEN

	drop table if exists tc01;
	drop table if exists tc02;
	drop table if exists tc03;
	drop table if exists tc04;

	CREATE TABLE `tc02` (
  `kontrahent` varchar(50) DEFAULT NULL,
  `rodzaj` varchar(7) DEFAULT NULL,
  `rodzaj_lp` int(1) NOT NULL DEFAULT '0',
  `m12` varchar(10) DEFAULT NULL,
  `m11` varchar(10) DEFAULT NULL,
  `m10` varchar(10) DEFAULT NULL,
  `m09` varchar(10) DEFAULT NULL,
  `m08` varchar(10) DEFAULT NULL,
  `m07` varchar(10) DEFAULT NULL,
  `m06` varchar(10) DEFAULT NULL,
  `m05` varchar(10) DEFAULT NULL,
  `m04` varchar(10) DEFAULT NULL,
  `m03` varchar(10) DEFAULT NULL,
  `m02` varchar(10) DEFAULT NULL,
  `m01` varchar(10) DEFAULT NULL,
  `m00` varchar(10) DEFAULT NULL,
  `Razem` varchar(10) DEFAULT NULL
	) DEFAULT CHARSET=cp1250;
	
	SET @data = current_date;
	SET @data_m01 = DATE_ADD(@data, INTERVAL -1 MONTH);
	SET @data_m02 = DATE_ADD(@data, INTERVAL -2 MONTH);
	SET @data_m03 = DATE_ADD(@data, INTERVAL -3 MONTH);
	SET @data_m04 = DATE_ADD(@data, INTERVAL -4 MONTH);
	SET @data_m05 = DATE_ADD(@data, INTERVAL -5 MONTH);
	SET @data_m06 = DATE_ADD(@data, INTERVAL -6 MONTH);
	SET @data_m07 = DATE_ADD(@data, INTERVAL -7 MONTH);
	SET @data_m08 = DATE_ADD(@data, INTERVAL -8 MONTH);
	SET @data_m09 = DATE_ADD(@data, INTERVAL -9 MONTH);
	SET @data_m10 = DATE_ADD(@data, INTERVAL -10 MONTH);
	SET @data_m11 = DATE_ADD(@data, INTERVAL -11 MONTH);
	SET @data_m12 = DATE_ADD(@data, INTERVAL -12 MONTH);

	SET @rok = YEAR(@data);
	SET @mc = MONTH(@data);
	SET @OdDnia = CONCAT(YEAR(_NaDzien)-1,'-01-01');

	SET _okres = CONCAT(YEAR(@data),'_',LPAD(MONTH(@data),2,'0'));
	SET _okres_m01 = CONCAT(YEAR(@data_m01),'_',LPAD(MONTH(@data_m01),2,'0'));
	SET _okres_m02 = CONCAT(YEAR(@data_m02),'_',LPAD(MONTH(@data_m02),2,'0'));
	SET _okres_m03 = CONCAT(YEAR(@data_m03),'_',LPAD(MONTH(@data_m03),2,'0'));
	SET _okres_m04 = CONCAT(YEAR(@data_m04),'_',LPAD(MONTH(@data_m04),2,'0'));
	SET _okres_m05 = CONCAT(YEAR(@data_m05),'_',LPAD(MONTH(@data_m05),2,'0'));
	SET _okres_m06 = CONCAT(YEAR(@data_m06),'_',LPAD(MONTH(@data_m06),2,'0'));
	SET _okres_m07 = CONCAT(YEAR(@data_m07),'_',LPAD(MONTH(@data_m07),2,'0'));
	SET _okres_m08 = CONCAT(YEAR(@data_m08),'_',LPAD(MONTH(@data_m08),2,'0'));
	SET _okres_m09 = CONCAT(YEAR(@data_m09),'_',LPAD(MONTH(@data_m09),2,'0'));
	SET _okres_m10 = CONCAT(YEAR(@data_m10),'_',LPAD(MONTH(@data_m10),2,'0'));
	SET _okres_m11 = CONCAT(YEAR(@data_m11),'_',LPAD(MONTH(@data_m11),2,'0'));
	SET _okres_m12 = CONCAT(YEAR(@data_m12),'_',LPAD(MONTH(@data_m12),2,'0'));


	create table tc01
		SELECT
		L.idn as log_idn,
		YEAR(L.modify_date) AS rok,
		LPAD(MONTH(L.modify_date),2,'0') AS mc,
		CONCAT(YEAR(L.modify_date),'_',LPAD(MONTH(L.modify_date),2,'0')) AS rok_mc,
		o.idnuser,
		C.idn,
		CONCAT('Id:',C.idn,' ',IF(C.nrwewn LIKE 'PL.%',MID(C.nrwewn,4,50),C.nrwewn),',',C.temat) AS info,
		IF(LOCATE(':',C.temat)>0,SUBSTRING_INDEX(C.temat,':',1),'?') AS aplikacja,
		CONCAT(o.Nazwisko, ' ', o.Imie1) AS osoba,
		SUM(L.time_sheet) AS time_sheet,
		IF(C.nrwewn LIKE "%.wiz%" OR C.nrwewn LIKE "%.spot%","WI",
		IF(C.nrwewn LIKE "%reklam%" OR L.keywords LIKE "%[R]%","RE",
		IF(C.nrwewn LIKE "%podsta%","PD",
		IF(C.nrwewn LIKE "%dodatkow%.$%FV%","$f",
		IF(C.nrwewn LIKE "%dodatkow%.$%ZAM%","$z",
		IF(C.nrwewn LIKE "%dodatkow%.$%","$?",
		IF(C.nrwewn LIKE "%dodatkow%","DD","?"))))))) AS rodzaj,
		IFNULL((SELECT k.symbol FROM kontrah k WHERE k.GUID = L.kontrGUID),
		SUBSTRING_INDEX(
		IF(C.nrwewn LIKE 'PL.%',MID(C.nrwewn,4,50),
		IF(C.nrwewn LIKE 'ST.%',MID(C.nrwewn,4,50),
		IF(C.nrwewn LIKE 'TR.%',MID(C.nrwewn,4,50),
		C.nrwewn)))
		,'.',1)) AS kontrahent
		FROM crm_log L
		INNER JOIN crm_commisions C ON L.commissionguid=C.GUID
		LEFT JOIN osoby o ON o.guid = L.osobaguid
		WHERE
		/*   g.osobaguid = '{BFA09420-3393-11D7-B676-00304F0902E2}' and */
			C.template_osoba =''
			AND C.Rodzaj IN (8,5) AND L.modify_date>'2008-01-01'
		AND L.modify_date> @OdDnia  
		AND L.modify_date <= _NaDzien
		GROUP BY  idnuser,rok,mc,aplikacja,rodzaj,kontrahent,info
		-- HAVING kontrahent IN ('AXIS','KONKOL')
		;

	create table tc04
		SELECT kontrahent, rok_mc AS okres, log_idn, rodzaj, idn AS zadanie, osoba, info, aplikacja, 
		_DisplayDurationFormat(time_sheet) as czas
		FROM tc01 
		WHERE rok_mc IN (_okres, _okres_m01) AND time_sheet <> 0
		ORDER BY kontrahent, rodzaj, idn, log_idn  DESC;
	

	-- dodac rnd2 i formatowanie do prawej!
	
	INSERT INTO tc02
		SELECT
		kontrahent, rodzaj,
		0 AS rodzaj_lp,
/*		_DisplayDurationFormat(SUM(IF(rok_mc = _okres_m12,time_sheet,0))) AS m12,
		_DisplayDurationFormat(SUM(IF(rok_mc = _okres_m11,time_sheet,0))) AS m11,
		_DisplayDurationFormat(SUM(IF(rok_mc = _okres_m10,time_sheet,0))) AS m10,
		_DisplayDurationFormat(SUM(IF(rok_mc = _okres_m09,time_sheet,0))) AS m09,
		_DisplayDurationFormat(SUM(IF(rok_mc = _okres_m08,time_sheet,0))) AS m08,
		_DisplayDurationFormat(SUM(IF(rok_mc = _okres_m07,time_sheet,0))) AS m07,
		_DisplayDurationFormat(SUM(IF(rok_mc = _okres_m06,time_sheet,0))) AS m06,
		_DisplayDurationFormat(SUM(IF(rok_mc = _okres_m05,time_sheet,0))) AS m05,
		_DisplayDurationFormat(SUM(IF(rok_mc = _okres_m04,time_sheet,0))) AS m04,
		_DisplayDurationFormat(SUM(IF(rok_mc = _okres_m03,time_sheet,0))) AS m03,
		_DisplayDurationFormat(SUM(IF(rok_mc = _okres_m02,time_sheet,0))) AS m02,
		_DisplayDurationFormat(SUM(IF(rok_mc = _okres_m01,time_sheet,0))) AS m01,
		_DisplayDurationFormat(SUM(IF(rok_mc = _okres,time_sheet,0))) AS m00,
		_DisplayDurationFormat(SUM(time_sheet)) AS Razem
*/	
		SUM(IF(rok_mc = _okres_m12,time_sheet,0)) AS m12,
		SUM(IF(rok_mc = _okres_m11,time_sheet,0)) AS m11,
		SUM(IF(rok_mc = _okres_m10,time_sheet,0)) AS m10,
		SUM(IF(rok_mc = _okres_m09,time_sheet,0)) AS m09,
		SUM(IF(rok_mc = _okres_m08,time_sheet,0)) AS m08,
		SUM(IF(rok_mc = _okres_m07,time_sheet,0)) AS m07,
		SUM(IF(rok_mc = _okres_m06,time_sheet,0)) AS m06,
		SUM(IF(rok_mc = _okres_m05,time_sheet,0)) AS m05,
		SUM(IF(rok_mc = _okres_m04,time_sheet,0)) AS m04,
		SUM(IF(rok_mc = _okres_m03,time_sheet,0)) AS m03,
		SUM(IF(rok_mc = _okres_m02,time_sheet,0)) AS m02,
		SUM(IF(rok_mc = _okres_m01,time_sheet,0)) AS m01,
		SUM(IF(rok_mc = _okres,time_sheet,0)) AS m00,
		SUM(time_sheet) AS Razem
		FROM tc01
		GROUP BY kontrahent, rodzaj
		ORDER BY kontrahent, rodzaj
		;

		ALTER TABLE tc02 MODIFY rodzaj VARCHAR(7);

		INSERT INTO tc02
		SELECT
		kontrahent, 'Razem' AS rodzaj,
		5 AS rodzaj_lp,
		SUM(IF(rok_mc = _okres_m12,time_sheet,0)) AS m12,
		SUM(IF(rok_mc = _okres_m11,time_sheet,0)) AS m11,
		SUM(IF(rok_mc = _okres_m10,time_sheet,0)) AS m10,
		SUM(IF(rok_mc = _okres_m09,time_sheet,0)) AS m09,
		SUM(IF(rok_mc = _okres_m08,time_sheet,0)) AS m08,
		SUM(IF(rok_mc = _okres_m07,time_sheet,0)) AS m07,
		SUM(IF(rok_mc = _okres_m06,time_sheet,0)) AS m06,
		SUM(IF(rok_mc = _okres_m05,time_sheet,0)) AS m05,
		SUM(IF(rok_mc = _okres_m04,time_sheet,0)) AS m04,
		SUM(IF(rok_mc = _okres_m03,time_sheet,0)) AS m03,
		SUM(IF(rok_mc = _okres_m02,time_sheet,0)) AS m02,
		SUM(IF(rok_mc = _okres_m01,time_sheet,0)) AS m01,
		SUM(IF(rok_mc = _okres,time_sheet,0)) AS m00,
		SUM(time_sheet) AS Razem
		FROM tc01
		GROUP BY kontrahent
		ORDER BY kontrahent
		;

		UPDATE tc02 SET m12 = '' WHERE m12 = '0';
		UPDATE tc02 SET m11 = '' WHERE m11 = '0';
		UPDATE tc02 SET m10 = '' WHERE m10 = '0';
		UPDATE tc02 SET m09 = '' WHERE m09 = '0';
		UPDATE tc02 SET m08 = '' WHERE m08 = '0';
		UPDATE tc02 SET m07 = '' WHERE m07 = '0';
		UPDATE tc02 SET m06 = '' WHERE m06 = '0';
		UPDATE tc02 SET m05 = '' WHERE m05 = '0';
		UPDATE tc02 SET m04 = '' WHERE m04 = '0';
		UPDATE tc02 SET m03 = '' WHERE m03 = '0';
		UPDATE tc02 SET m02 = '' WHERE m02 = '0';
		UPDATE tc02 SET m01 = '' WHERE m01 = '0';
		UPDATE tc02 SET m00 = '' WHERE m00 = '0';
		
		SET _S =
		'create table tc03
		SELECT 
		kontrahent, rodzaj, 
		m12 AS #om12#,
		m11 AS #om11#,
		m10 AS #om10#,
		m09 AS #om09#,
		m08 AS #om08#,
		m07 AS #om07#,
		m06 AS #om06#,
		m05 AS #om05#,
		m04 AS #om04#,
		m03 AS #om03#,
		m02 AS #om02#,
		m01 AS #om01#,
		m00 AS #om00#,
		Razem
		FROM tc02 order by kontrahent, rodzaj_lp
		;'
		;

		SET _S = REPLACE(_S,'#om12#',_okres_m12);
		SET _S = REPLACE(_S,'#om11#',_okres_m11);
		SET _S = REPLACE(_S,'#om10#',_okres_m10);
		SET _S = REPLACE(_S,'#om09#',_okres_m09);
		SET _S = REPLACE(_S,'#om08#',_okres_m08);
		SET _S = REPLACE(_S,'#om07#',_okres_m07);
		SET _S = REPLACE(_S,'#om06#',_okres_m06);
		SET _S = REPLACE(_S,'#om05#',_okres_m05);
		SET _S = REPLACE(_S,'#om04#',_okres_m04);
		SET _S = REPLACE(_S,'#om03#',_okres_m03);
		SET _S = REPLACE(_S,'#om02#',_okres_m02);
		SET _S = REPLACE(_S,'#om01#',_okres_m01);
		SET _S = REPLACE(_S,'#om00#',_okres);


		SET @SQLText = _S;
		CALL _ExecuteCmd(_S, @Error); 


END IF;

/*
CALL zest_alerty("CRM_02","","","",NOW(),"","","","","","","");
SELECT * FROM tc01;
SELECT * FROM tc02;
SELECT * FROM tc04;
SELECT @_okres, @_okres_m01;


rozbić dolna tabelke na poszczegolne wpisy - wtedy nie ma sensu praca z grupowaniem wpisow
*/
IF (_MODE = 'CRM_02') THEN
	-- SET SESSION group_concat_max_len = 1024*1024*1024;

	SET _wstecz = IF(_miesiac='', 1, _miesiac);
	-- SET _wstecz = IF(_miesiac='', 17, _miesiac); -- tez jednorazowo

	SET @data = current_date;
	-- SET @data_m01 = DATE_ADD(@data, INTERVAL -1 MONTH);
	SET @data_m01 = DATE_ADD(@data, INTERVAL -_wstecz MONTH);
		
	SET @rok = YEAR(@data);
	SET @mc = MONTH(@data);
	-- SET @OdDnia = CONCAT(YEAR(_NaDzien)-1,'-',LPAD(@mc,2,'0'),'-01');
	SET @OdDnia = '2025-01-01'; -- dla Pliszki jednorazowo, ale zotaje na stale dla wszystkich, tylko rok zmieniamy.
	
	SET @_okres = CONCAT(YEAR(@data),'_',LPAD(MONTH(@data),2,'0'));
	SET @_okres_m01 = CONCAT(YEAR(@data_m01),'_',LPAD(MONTH(@data_m01),2,'0'));

	drop table if exists tc01a;
	drop table if exists tc01;
	drop table if exists tc02;
	drop table if exists tc03;
	drop table if exists tc04;
	drop table if exists tc05;
	drop table if exists tcadresy;

	CREATE TABLE `tc02` (
  kontrahent VARCHAR(50) DEFAULT NULL,
  rok_mc VARCHAR(10) DEFAULT NULL,
  WI VARCHAR(7) DEFAULT NULL,
  RE VARCHAR(7) DEFAULT NULL,
  PD VARCHAR(7) DEFAULT NULL,
  DD VARCHAR(7) DEFAULT NULL,
  PLN VARCHAR(7) DEFAULT NULL,
  -- S2 VARCHAR(7) DEFAULT NULL,
  -- Sz VARCHAR(7) DEFAULT NULL,
  S22 VARCHAR(7) DEFAULT NULL,
  PR VARCHAR(8) DEFAULT NULL,
  Razem VARCHAR(10) DEFAULT NULL,
  Godziny VARCHAR(10) DEFAULT NULL,
  Saldo VARCHAR(10) DEFAULT NULL
	) DEFAULT CHARSET=cp1250;

		create table tc01a
		SELECT
		L.idn as log_idn,
		YEAR(L.modify_date) AS rok,
		LPAD(MONTH(L.modify_date),2,'0') AS mc,
		CONCAT(YEAR(L.modify_date),'_',LPAD(MONTH(L.modify_date),2,'0')) AS rok_mc,
		o.idnuser,
		C.idn,
		CONCAT('Id:',C.idn,' ',IF(C.nrwewn LIKE 'PL.%',MID(C.nrwewn,4,50),C.nrwewn),',',C.temat) AS info,
		C.temat AS temat,
		group_concat(L.jest) as info_log,
		IF(LOCATE(':',C.temat)>0,SUBSTRING_INDEX(C.temat,':',1),'?') AS aplikacja,
		CONCAT(o.Nazwisko, ' ', o.Imie1) AS osoba,
		_Rnd2(SUM(L.time_sheet)) AS time_sheet,
		IF(C.nrwewn LIKE "%.wiz%" OR C.nrwewn LIKE "%.spot%","WI",
		IF(C.nrwewn LIKE "%reklam%" OR L.keywords LIKE "%[R]%","RE",
		IF(C.nrwewn LIKE "%podsta%","PD",
		IF(C.nrwewn LIKE "%dodatkow%.$%FV%" OR C.nrwewn LIKE "%dodatkow%.$%ZAM%" OR C.nrwewn LIKE "%dodatkow%.$%","PLN",
		-- IF(C.nrwewn LIKE "%dodatkow%.$%ZAM%","$z",
		-- IF(C.nrwewn LIKE "%dodatkow%.$%","$?",
		IF(C.nrwewn LIKE "%przekroczenie%","PR",
		IF(C.nrwewn LIKE "%dodatkow%","DD","?")))))) AS rodzaj,

		IFNULL((SELECT k.symbol FROM kontrah k WHERE k.GUID = L.kontrGUID),
		SUBSTRING_INDEX(
		IF(C.nrwewn LIKE 'PL.%',MID(C.nrwewn,4,50),
		IF(C.nrwewn LIKE 'ST.%',MID(C.nrwewn,4,50),
		IF(C.nrwewn LIKE 'TR.%',MID(C.nrwewn,4,50),
		C.nrwewn)))
		,'.',1)) AS kontrahent

		-- IFNULL((SELECT symbol FROM kontrah WHERE GUID = L.kontrGUID), k.symbol) AS kontrahent

		FROM crm_log L
		INNER JOIN crm_commisions C ON L.commissionguid=C.GUID
		LEFT JOIN osoby o ON o.guid = L.osobaguid
		-- LEFT JOIN crm_usr_links cul ON cul.commisionGUID = C.GUID AND cul.poziom=4
        -- LEFT JOIN kontrah k ON k.GUID = cul.kontrGUID
		WHERE
		/*   g.osobaguid = '{BFA09420-3393-11D7-B676-00304F0902E2}' and */
			C.template_osoba =''
			AND C.Rodzaj IN (8,5) AND L.modify_date>'2008-01-01'
			AND L.modify_date> @OdDnia  
			AND L.modify_date <= DATE(_NaDzien)
		GROUP BY  idnuser,rok,mc,aplikacja,rodzaj,kontrahent,info, L.idn
		-- HAVING kontrahent IN ('MAXFORM')
		;
		
		create table tc01
		SELECT
		L.idn as log_idn,
		YEAR(L.modify_date) AS rok,
		LPAD(MONTH(L.modify_date),2,'0') AS mc,
		CONCAT(YEAR(L.modify_date),'_',LPAD(MONTH(L.modify_date),2,'0')) AS rok_mc,
		o.idnuser,
		C.idn,
		CONCAT('Id:',C.idn,' ',IF(C.nrwewn LIKE 'PL.%',MID(C.nrwewn,4,50),C.nrwewn),',',C.temat) AS info,
		C.temat AS temat,
		group_concat(L.jest) as info_log,
		IF(LOCATE(':',C.temat)>0,SUBSTRING_INDEX(C.temat,':',1),'?') AS aplikacja,
		CONCAT(o.Nazwisko, ' ', o.Imie1) AS osoba,
		_Rnd2(SUM(L.time_sheet)) AS time_sheet,
		IF(C.nrwewn LIKE "%.wiz%" OR C.nrwewn LIKE "%.spot%","WI",
		IF(C.nrwewn LIKE "%reklam%" OR L.keywords LIKE "%[R]%","RE",
		IF(C.nrwewn LIKE "%podsta%","PD",
		IF(C.nrwewn LIKE "%dodatkow%.$%FV%" OR C.nrwewn LIKE "%dodatkow%.$%ZAM%" OR C.nrwewn LIKE "%dodatkow%.$%","PLN",
		-- IF(C.nrwewn LIKE "%dodatkow%.$%ZAM%","$z",
		-- IF(C.nrwewn LIKE "%dodatkow%.$%","$?",
		IF(C.nrwewn LIKE "%przekroczenie%","PR",
		IF(C.nrwewn LIKE "%dodatkow%","DD","?")))))) AS rodzaj,

		IFNULL((SELECT k.symbol FROM kontrah k WHERE k.GUID = L.kontrGUID),
		SUBSTRING_INDEX(
		IF(C.nrwewn LIKE 'PL.%',MID(C.nrwewn,4,50),
		IF(C.nrwewn LIKE 'ST.%',MID(C.nrwewn,4,50),
		IF(C.nrwewn LIKE 'TR.%',MID(C.nrwewn,4,50),
		C.nrwewn)))
		,'.',1)) AS kontrahent

		-- IFNULL((SELECT symbol FROM kontrah WHERE GUID = L.kontrGUID), k.symbol) AS kontrahent

		FROM crm_log L
		INNER JOIN crm_commisions C ON L.commissionguid=C.GUID
		LEFT JOIN osoby o ON o.guid = L.osobaguid
		-- LEFT JOIN crm_usr_links cul ON cul.commisionGUID = C.GUID AND cul.poziom=4
        -- LEFT JOIN kontrah k ON k.GUID = cul.kontrGUID
		WHERE
		/*   g.osobaguid = '{BFA09420-3393-11D7-B676-00304F0902E2}' and */
			C.template_osoba =''
			AND C.Rodzaj IN (8,5) AND L.modify_date>'2008-01-01'
			AND L.modify_date> @OdDnia  
			AND L.modify_date <= DATE(_NaDzien)
		GROUP BY  idnuser,rok,mc,aplikacja,rodzaj,kontrahent,info
		-- HAVING kontrahent IN ('MAXFORM')
		;
		
		
		-- bylo 117 znakow
		UPDATE tc01 SET 
		info_log = IF(LENGTH(info_log)>800,CONCAT(LEFT(info_log,800),'...'),info_log)
		;
		
		UPDATE tc01a SET 
		info_log = IF(LENGTH(info_log)>800,CONCAT(LEFT(info_log,800),'...'),info_log)
		;
		
		
		INSERT INTO tc02
		SELECT
		kontrahent,
		rok_mc AS rok_mc,
		SUM(IF(rodzaj='WI',time_sheet,0)),
		SUM(IF(rodzaj='RE',time_sheet,0)),
		SUM(IF(rodzaj='PD',time_sheet,0)),
		SUM(IF(rodzaj='DD',time_sheet,0)),
		SUM(IF(rodzaj='PLN',time_sheet,0)),
		-- SUM(IF(rodzaj='$?',time_sheet,0)),
		-- SUM(IF(rodzaj='$z',time_sheet,0)),
		SUM(IF(rodzaj='?',time_sheet,0)),
		_Rnd2(SUM(IF(rodzaj='PR',time_sheet,0))*(-1)),
		SUM(IF(rodzaj='PR',-time_sheet,time_sheet)) AS Razem,
		'' AS godziny,
		'' AS Saldo
		FROM tc01
		GROUP BY kontrahent, rok_mc
		ORDER BY kontrahent, rok_mc
		;
		
		UPDATE tc02 SET 
		godziny = (SELECT _Rnd2(godz_nadzor_zdalny + godz_serwis) FROM kontrah WHERE symbol = kontrahent AND deleted=0 AND symbol<>'');
		
		UPDATE tc02 SET 
		razem = _Rnd2(WI + PD + DD + PR);
		
		UPDATE tc02 SET 
		saldo = _Rnd2(godziny - razem);
		
		INSERT INTO tc02 
		SELECT kontrahent, 'Razem',
		_Rnd2(sum(WI)),_Rnd2(sum(RE)),_Rnd2(sum(PD)),_Rnd2(sum(DD)),_Rnd2(sum(PLN)),
		_Rnd2(sum(S22)),_Rnd2(sum(PR)),_Rnd2(sum(Razem)),_Rnd2(0),_Rnd2(sum(Saldo))
		FROM tc02 
		GROUP BY kontrahent;
		
		
		UPDATE tc02 SET 
			WI = CONCAT('#',IF(WI='0.00','', WI)),
			RE = CONCAT('#',IF(RE='0.00','', RE)),
			PD = CONCAT('#',IF(PD='0.00','', PD)),
			DD = CONCAT('#',IF(DD='0.00','', DD)),
			PLN = CONCAT('#',IF(PLN='0.00','', PLN)),
			-- S2 = CONCAT('#',REPLACE(S2,'0.00','')),
			-- Sz = CONCAT('#',REPLACE(Sz,'0.00','')),
			S22 = CONCAT('#',IF(S22='0.00','', S22)),
			PR = CONCAT('#',IF(PR='0.00','', PR)),
			Godziny = CONCAT('#',IF(Godziny='0.00','', Godziny)),
			Razem = CONCAT('#',IF(Razem='0.00','', Razem)),
			Saldo = CONCAT('#',IF(Saldo='0.00','', Saldo));
			
	
		create table tc03
		SELECT kontrahent, rok_mc AS okres, log_idn, rodzaj, idn AS zadanie, osoba, temat, aplikacja, 
		_DisplayDurationFormat(time_sheet) as czas
		FROM tc01 
		-- WHERE rok_mc IN (@_okres, @_okres_m01) AND
		WHERE rok_mc >= @_okres_m01 AND
		time_sheet <> 0
		AND info NOT LIKE '%.obsluga%'
		ORDER BY kontrahent, rodzaj, idn, log_idn  DESC;
	
		create table tc04
		SELECT kontrahent, rok_mc AS okres, log_idn, rodzaj, idn AS zadanie, osoba, temat, info_log, aplikacja, 
		_DisplayDurationFormat(time_sheet) as czas
		FROM tc01a 
		-- WHERE rok_mc IN (@_okres, @_okres_m01) AND
		WHERE rok_mc >= @_okres_m01 AND
		time_sheet <> 0
		AND info LIKE '%.obsluga%'
		ORDER BY kontrahent, rodzaj, idn, log_idn  DESC;
	
		
		create table tc05 (
		Legenda VARCHAR(150) DEFAULT NULL
		) DEFAULT CHARSET=cp1250;
		
		insert into tc05 VALUES 
		 (_utf8'LIMIT:  godzinowy zakres wsparcia zgodnie z umową ryczałtową'),
	     (_utf8'WI: wizyty'),
	     (_utf8'RE: obsługa reklamacji'),
	     (_utf8'PD: podstawowa obsługa'),
	     (_utf8'DD: dodatkowa obsługa w ryczałcie'),
	     (_utf8'PLN:obsługa dodatkowa odpłatna'),
	     (_utf8'PR: rozliczone przekroczenia limitu'),
	     (_utf8'RAZEM = WI + PD + DD + PR'),
	     (_utf8'SALDO = LIMIT – RAZEM');
	
	
	create table tcadresy
	SELECT kontrahent,
	(SELECT IFNULL(CONCAT('',GROUP_CONCAT(CONCAT(kontrahent,' <',a.adres),'>'),''),'brak_adresu <t_lendzion@int.com.pl>') FROM kontakty a, kontrah k WHERE a.typ_medium=3 AND a.kontrguid=k.guid AND k.symbol=kontrahent AND a.opis LIKE '%Raporty%') AS adresy
	FROM tc02
	GROUP BY kontrahent;
	
	
END IF;


/*
CALL zest_alerty("CRM_03","","","",NOW(),"","","","","","","");
zestawienie obciazen zadan dla AS
*/
IF (_MODE = 'CRM_03') THEN

		drop table if exists td01;
		CREATE TEMPORARY TABLE `td01` (
  `guid` varchar(38) NOT NULL DEFAULT '' ,
  `kontrahent` varchar(100) DEFAULT NULL,
  `numer` int NOT NULL DEFAULT '0',
  `zadanie` varchar(100) DEFAULT NULL,
  `nrwewn` varchar(100) DEFAULT NULL,
  `data_zgl` date DEFAULT NULL,
  `data_po` date DEFAULT NULL,
  `odpow` varchar(200) DEFAULT NULL,
  `wykonaw` varchar(200) DEFAULT NULL,
  `zaloz_h` double(16,2) DEFAULT NULL,
  `akt_h` double(16,2) DEFAULT NULL,
  `roznica_h` double(16,2) DEFAULT NULL,
  `proc_h` double(16,2) DEFAULT NULL,
  `proc_prac` double(16,2) DEFAULT NULL,
  `roznica_proc` double(16,2) DEFAULT NULL,
  rodzaj varchar(10) DEFAULT NULL,
  platne varchar(5) DEFAULT NULL
) DEFAULT CHARSET=cp1250
;

		drop table if exists td02;
		CREATE TEMPORARY TABLE `td02` (
   `kontrahent` varchar(100) DEFAULT NULL
		) DEFAULT CHARSET=cp1250
		;

		insert into td01
		select
		c.guid,

		SUBSTRING_INDEX(
		IF(c.nrwewn LIKE 'PL.%',MID(c.nrwewn,4,50),
		IF(c.nrwewn LIKE 'ST.%',MID(c.nrwewn,4,50),
		IF(c.nrwewn LIKE 'TR.%',MID(c.nrwewn,4,50),
		c.nrwewn))),'.',1) as kontrahent,

		c.idn as numer,
		c.temat as zadanie,
		c.nrwewn as nrwewn,
		date(c.data) as data_zgl,
		(select date(modify_date) from crm_log where commissionguid = c.GUID order by modify_date desc limit 1) as data_po,

		(select group_concat(distinct concat(nazwisko, ' ',imie1) order by nazwisko, imie1) from osoby o, crm_usr_links cul where cul.poziom=1 and o.guid = cul.osoba and cul.commisionGUID = c.GUID)
    as odpow,

		(select group_concat(distinct concat(nazwisko, ' ',imie1) order by nazwisko, imie1) from osoby o, crm_usr_links cul where cul.poziom=2 and o.guid = cul.osoba and cul.commisionGUID = c.GUID)
    as wykonaw,

		-- c.work_time_def as zaloz_h,
		c.Duration as zaloz_h,
		c.Work_Duration as akt_h,

		c.Work_Duration - c.Duration as roznica_h,

		ifnull(_Rnd2(c.Work_Duration/c.Duration * 100),0) as proc_h,

		c.Progress as proc_prac,

		ifnull(_Rnd2(c.Work_Duration/c.Duration * 100) - c.Progress, 0) as roznica_proc,

		IF(c.nrwewn LIKE "%.wiz%" OR c.nrwewn LIKE "%.spot%","WI",
		-- IF(c.nrwewn LIKE "%reklam%" OR L.keywords LIKE "%[R]%","RE",
		IF(c.nrwewn LIKE "%reklam%","RE",
		IF(c.nrwewn LIKE "%podsta%","PD",
		IF(c.nrwewn LIKE "%dodatkow%.$%FV%" OR c.nrwewn LIKE "%dodatkow%.$%ZAM%" OR c.nrwewn LIKE "%dodatkow%.$%","PLN",
		-- IF(C.nrwewn LIKE "%dodatkow%.$%ZAM%","$z",
		-- IF(C.nrwewn LIKE "%dodatkow%.$%","$?",
		IF(c.nrwewn LIKE "%przekroczenie%","PR",
		IF(c.nrwewn LIKE "%dodatkow%","DD","?")))))) AS rodzaj,

		IF(c.nrwewn LIKE "%dodatkow%.$%FV%" OR c.nrwewn LIKE "%dodatkow%.$%ZAM%" OR c.nrwewn LIKE "%dodatkow%.$%", 'T', 'N') as platne

		-- c.*

		from crm_commisions c
		where c.rodzaj=8 and c.data>='2008-01-01' and c.deleted = 0
		order by c.data, c.idn

		-- limit 10
		;
		-- tabela pomocnicza porzebna do listy kontrahentow w filtrze
		insert into td02
		select distinct kontrahent from td01 order by kontrahent
		;

END IF; -- CRM_03

/*
obciazenia rentownosci, zestawienie sqd, podajemy dzien startowy zestawienia
dane na miesiac aktualny, miesiac poprzedni, 3 mc wstecz, 6 mcy wstecz, rok wstecz
call zest_alerty("CRM_04","","","","_NaDzien","","","","","","","");
CALL zest_alerty("CRM_04","","","",NOW(),"","","","","","","");
*/

IF (_MODE = 'CRM_04') THEN

		-- poczatek miesiaca z dnia podanego do zestawienia
		SET @data = concat(left(_NaDzien,7),'-01');
		-- daty potrzebne do ograniczenia zakresu badanych wpisow
		SET @OdDnia = DATE_ADD(@data, INTERVAL - 12 MONTH);
		SET @DoDnia = LAST_DAY(@data);

		SET @_okres = CONCAT(YEAR(@data),'_',LPAD(MONTH(@data),2,'0'));
		SET @_okres_01 = CONCAT(YEAR(DATE_ADD(@data, INTERVAL - 1 MONTH)),'_',LPAD(MONTH(DATE_ADD(@data, INTERVAL - 1 MONTH)),2,'0'));
		SET @_okres_02 = CONCAT(YEAR(DATE_ADD(@data, INTERVAL - 2 MONTH)),'_',LPAD(MONTH(DATE_ADD(@data, INTERVAL - 2 MONTH)),2,'0'));
		SET @_okres_03 = CONCAT(YEAR(DATE_ADD(@data, INTERVAL - 3 MONTH)),'_',LPAD(MONTH(DATE_ADD(@data, INTERVAL - 3 MONTH)),2,'0'));
		SET @_okres_04 = CONCAT(YEAR(DATE_ADD(@data, INTERVAL - 4 MONTH)),'_',LPAD(MONTH(DATE_ADD(@data, INTERVAL - 4 MONTH)),2,'0'));
		SET @_okres_05 = CONCAT(YEAR(DATE_ADD(@data, INTERVAL - 5 MONTH)),'_',LPAD(MONTH(DATE_ADD(@data, INTERVAL - 5 MONTH)),2,'0'));
		SET @_okres_06 = CONCAT(YEAR(DATE_ADD(@data, INTERVAL - 6 MONTH)),'_',LPAD(MONTH(DATE_ADD(@data, INTERVAL - 6 MONTH)),2,'0'));
		SET @_okres_07 = CONCAT(YEAR(DATE_ADD(@data, INTERVAL - 7 MONTH)),'_',LPAD(MONTH(DATE_ADD(@data, INTERVAL - 7 MONTH)),2,'0'));
		SET @_okres_08 = CONCAT(YEAR(DATE_ADD(@data, INTERVAL - 8 MONTH)),'_',LPAD(MONTH(DATE_ADD(@data, INTERVAL - 8 MONTH)),2,'0'));
		SET @_okres_09 = CONCAT(YEAR(DATE_ADD(@data, INTERVAL - 9 MONTH)),'_',LPAD(MONTH(DATE_ADD(@data, INTERVAL - 9 MONTH)),2,'0'));
		SET @_okres_10 = CONCAT(YEAR(DATE_ADD(@data, INTERVAL - 10 MONTH)),'_',LPAD(MONTH(DATE_ADD(@data, INTERVAL - 10 MONTH)),2,'0'));
		SET @_okres_11 = CONCAT(YEAR(DATE_ADD(@data, INTERVAL - 11 MONTH)),'_',LPAD(MONTH(DATE_ADD(@data, INTERVAL - 11 MONTH)),2,'0'));
		SET @_okres_12 = CONCAT(YEAR(DATE_ADD(@data, INTERVAL - 12 MONTH)),'_',LPAD(MONTH(DATE_ADD(@data, INTERVAL - 12 MONTH)),2,'0'));

		drop table if exists te01;
		drop table if exists te02;

		create temporary table te01
		SELECT
		YEAR(L.modify_date) AS rok,
		LPAD(MONTH(L.modify_date),2,'0') AS mc,
		CONCAT(YEAR(L.modify_date),'_',LPAD(MONTH(L.modify_date),2,'0')) AS rok_mc,
		_Rnd2(SUM(L.time_sheet)) AS time_sheet,
		IF(C.nrwewn LIKE "%.wiz%" OR C.nrwewn LIKE "%.spot%","WI",
		IF(C.nrwewn LIKE "%reklam%" OR L.keywords LIKE "%[R]%","RE",
		IF(C.nrwewn LIKE "%podsta%","PD",
		IF(C.nrwewn LIKE "%dodatkow%.$%FV%" OR C.nrwewn LIKE "%dodatkow%.$%ZAM%" OR C.nrwewn LIKE "%dodatkow%.$%","PLN",
		-- IF(C.nrwewn LIKE "%dodatkow%.$%ZAM%","$z",
		-- IF(C.nrwewn LIKE "%dodatkow%.$%","$?",
		IF(C.nrwewn LIKE "%przekroczenie%","PR",
		IF(C.nrwewn LIKE "%dodatkow%","DD","?")))))) AS rodzaj_w,

		IFNULL((SELECT k.symbol FROM kontrah k WHERE k.GUID = L.kontrGUID),
		SUBSTRING_INDEX(
		IF(C.nrwewn LIKE 'PL.%',MID(C.nrwewn,4,50),
		IF(C.nrwewn LIKE 'ST.%',MID(C.nrwewn,4,50),
		IF(C.nrwewn LIKE 'TR.%',MID(C.nrwewn,4,50),
		C.nrwewn)))
		,'.',1)) AS kontrahent
		-- IFNULL((SELECT symbol FROM kontrah WHERE GUID = L.kontrGUID), k.symbol) AS kontrahent
		FROM crm_log L
		INNER JOIN crm_commisions C ON L.commissionguid=C.GUID
		WHERE C.Rodzaj IN (8) AND L.modify_date>'2008-01-01' and L.time_sheet <> 0
			AND L.modify_date between @OdDnia and @DoDnia
		GROUP BY rok, mc, kontrahent, rodzaj_w
		-- HAVING kontrahent IN ('WEGA')
		;

		alter table te01 add column okres varchar(10) default null after rok_mc;

		update te01 set okres =
		case when rok_mc = @_okres then 'okres'
		when rok_mc = @_okres_01 then 'okres01'
		when rok_mc = @_okres_02 then 'okres02'
		when rok_mc = @_okres_03 then 'okres03'
		when rok_mc = @_okres_06 then 'okres06'
		when rok_mc = @_okres_12 then 'okres12'
		else '' end
		;


		CREATE temporary TABLE te02 (
  kontrahent VARCHAR(50) DEFAULT NULL,
  godziny_ryczaltu double(16,2) default null,
  kwota_ryczaltu double(16,2) default null,
  akt_RE double(16,2) DEFAULT NULL,
  akt_PLN double(16,2) DEFAULT NULL,
  akt_RYCZ double(16,2) DEFAULT NULL,
  akt_PRZEKR double(16,2) DEFAULT NULL,
  akt_stawka double(16,2) DEFAULT NULL,

  p01_RE double(16,2) DEFAULT NULL,
  p01_PLN double(16,2) DEFAULT NULL,
  p01_RYCZ double(16,2) DEFAULT NULL,
  p01_PRZEKR double(16,2) DEFAULT NULL,
  p01_stawka double(16,2) DEFAULT NULL,

  p03_RE double(16,2) DEFAULT NULL,
  p03_PLN double(16,2) DEFAULT NULL,
  p03_RYCZ double(16,2) DEFAULT NULL,
  p03_PRZEKR double(16,2) DEFAULT NULL,
  p03_stawka double(16,2) DEFAULT NULL,

  p06_RE double(16,2) DEFAULT NULL,
  p06_PLN double(16,2) DEFAULT NULL,
  p06_RYCZ double(16,2) DEFAULT NULL,
  p06_PRZEKR double(16,2) DEFAULT NULL,
  p06_stawka double(16,2) DEFAULT NULL,

  p12_RE double(16,2) DEFAULT NULL,
  p12_PLN double(16,2) DEFAULT NULL,
  p12_RYCZ double(16,2) DEFAULT NULL,
  p12_PRZEKR double(16,2) DEFAULT NULL,
  p12_stawka double(16,2) DEFAULT NULL,

  KEY kontrahent (kontrahent)
	) DEFAULT CHARSET=cp1250;

		insert into te02
		select kontrahent, null, null,
		SUM(IF(okres='okres' and rodzaj_w ='RE',time_sheet,0)),
		SUM(IF(okres='okres' and rodzaj_w ='PLN',time_sheet,0)),
		SUM(IF(okres='okres' and rodzaj_w not in ('RE','PLN'),time_sheet,0)),
		null, null,
		SUM(IF(okres='okres01' and rodzaj_w ='RE',time_sheet,0)),
		SUM(IF(okres='okres01' and rodzaj_w ='PLN',time_sheet,0)),
		SUM(IF(okres='okres01' and rodzaj_w not in ('RE','PLN'),time_sheet,0)),
		null, null,
		SUM(IF(okres='okres03' and rodzaj_w ='RE',time_sheet,0)),
		SUM(IF(okres='okres03' and rodzaj_w ='PLN',time_sheet,0)),
		SUM(IF(okres='okres03' and rodzaj_w not in ('RE','PLN'),time_sheet,0)),
		null, null,
		SUM(IF(okres='okres06' and rodzaj_w ='RE',time_sheet,0)),
		SUM(IF(okres='okres06' and rodzaj_w ='PLN',time_sheet,0)),
		SUM(IF(okres='okres06' and rodzaj_w not in ('RE','PLN'),time_sheet,0)),
		null, null,
		SUM(IF(okres='okres12' and rodzaj_w ='RE',time_sheet,0)),
		SUM(IF(okres='okres12' and rodzaj_w ='PLN',time_sheet,0)),
		SUM(IF(okres='okres12' and rodzaj_w not in ('RE','PLN'),time_sheet,0)),
		null, null
		from te01
		group by kontrahent
		order by kontrahent;

		update te02 t, kontrah k set
		t.godziny_ryczaltu = ifnull(k.godz_nadzor_zdalny,0) + ifnull(k.godz_serwis,0) + ifnull(k.godz_modyfikacje,0),
		t.kwota_ryczaltu = k.kwota_ryczaltu
		where t.kontrahent = k.symbol
		;

		update te02 set
		akt_PRZEKR = akt_RYCZ - godziny_ryczaltu,
		akt_stawka = _Rnd2(kwota_ryczaltu/akt_RYCZ),
		p01_PRZEKR = p01_RYCZ - godziny_ryczaltu,
		p01_stawka = _Rnd2(kwota_ryczaltu/p01_RYCZ),
		p03_PRZEKR = p03_RYCZ - godziny_ryczaltu,
		p03_stawka = _Rnd2(kwota_ryczaltu/p03_RYCZ),
		p06_PRZEKR = p06_RYCZ - godziny_ryczaltu,
		p06_stawka = _Rnd2(kwota_ryczaltu/p06_RYCZ),
		p12_PRZEKR = p12_RYCZ - godziny_ryczaltu,
		p12_stawka = _Rnd2(kwota_ryczaltu/p12_RYCZ)
		;


		drop table if exists te03;
		CREATE TEMPORARY TABLE `te03` (
   `kontrahent` varchar(100) DEFAULT NULL
		) DEFAULT CHARSET=cp1250
		;

		-- tabela pomocnicza porzebna do listy kontrahentow w filtrze
		insert into te03
		select distinct kontrahent from te02 order by kontrahent
		;



		-- select * from  limit 1;


END IF;


-- jest to mode tworzoacy dane do alertu emailowego z zestawieniem dla pakowaczy
-- call zest_alerty("KONKOL_PAKOWACZ","","","","","","","","","","","");
IF (_MODE = 'KONKOL_PAKOWACZ') THEN
	-- tabele wstpene wszystkie
	-- CALL fr_ht_ZM_Tool(0, '', '', 0, '2025-10-30', '2025-10-30', '', '', '', '');
 CALL fr_ht_ZM_Tool(0, '', '', 0, CURRENT_DATE(), CURRENT_DATE(), '', '', '', '');

 -- sekcja zamiast tego, co jest w ZM_PAKOWACZ w procedurze fr_ht_ZM
 DROP TEMPORARY TABLE IF EXISTS t_Kpakowacz;

			CREATE TEMPORARY TABLE t_Kpakowacz
			SELECT
			/*

			    _VarINT(COUNT(DISTINCT P.IDN)) AS Zamowien, _varDOUBLE(0.0) as inwnbo,
				-- FULL bylo _VarStr(CONCAT(COUNT(DISTINCT P.IDN), ' poz. na ', COUNT(DISTINCT N.IDN), ' zam.', '(Nr: ', GROUP_CONCAT(DISTINCT N.nrzamw SEPARATOR ' , '), ' )')) AS Zam,
				_VarStr(CONCAT(COUNT(DISTINCT P.IDN), ' poz. na ', COUNT(DISTINCT N.IDN), ' zam.')) AS Zam,
				*/
				N.transza_zamow,
				CONCAT(N.AkwizytorGUID,'--',N.transza_zamow) as grupa,

				IFNULL(A.Symbol, 'n/n') AS Kierowca,
				_VarStr(spier.DisplayValue) as TranszaStr1,

				-- IFNULL(A.Nazwa, 'n/n') AS KierowcaNazwa,
				IFNULL(CONCAT(ok.Nazwisko, ' ', ok.imiona), 'n/n') AS KierowcaNazwa,
				ean.GateNo AS brama,
				c.temat as WyjazdNazwa,

				IFNULL(Mag.Opis, 'n/n') AS MagazynWysStr,
    /*
     J.Nazwa as Jedn,
*/
			JJ.Nazwa as Jedn_pom,
				ht_opakowania_L_M_GetText(0, SUM(P.ilosc), T.przelicznik_l, T.przelicznik_m, J.Nazwa, JJ.Nazwa, 0) AS IloscOpakowan,
				ht_opakowania_L_M_GetText('CEIL', SUM(P.ilosc), T.przelicznik_l, T.przelicznik_m, J.Nazwa, JJ.Nazwa, (JJ.idn !=23)) AS Opakowan_Max,
				ht_opakowania_L_M_GetText('CEIL_STR', SUM(P.ilosc), T.przelicznik_l, T.przelicznik_m, J.Nazwa, JJ.Nazwa, (JJ.idn !=23)) AS Opakowan_Max_Str,
				/*
				-- _ConcatStr(P.towrsymb, IFNULL(R.Nazwa, ''), '/') AS towrsymb,
				*/
				P.towrsymb AS towrsymb,
				IF(T.nazwa = P.towrnazwa, IFNULL(( SELECT tostr FROM lang_translations WHERE to_lang='pl' AND object_name='TowrNg' and object_key=P.IdnTowr ), P.towrnazwa), P.towrnazwa) AS towrnazwa,

				_VarStr(ht_przeliczniki_GetText(3, L.Opis, J.Nazwa, SUM(P.ilosc_L_do_przeliczen), P.ilosc_M_do_przeliczen, SUM(P.Ilosc), COUNT(DISTINCT P.ilosc_M_do_przeliczen))) AS Ilosc_Przelicznik,

				/*
				SUM(P.Ilosc) AS Ilosc,
				IF((J.Nazwa = '' or isnull(J.Nazwa)), J.Nazwa, J.Nazwa) AS Jednostka,  J.precyzja as JednostkaPrecyzja,

				'.' AS nrdokmag_zwrotu, P.idn, P.GUID, "." AS tabela_kursow,
				T.min_ord_quant,
				_VarDouble(P.ilosc*T.objetosc) AS objetosc,
				T.objetosc AS objetosc_jedn, T.lokalizacja1, T.lokalizacja2, T.lokalizacja3, T.lokalizacja4, P.info, P.info2, P.info3, P.info4,

				-- slon add
				(select symbol_edi from oferty  WHERE  N.kontrGUID=oferty.kontrguid and P.idntowr=oferty.idntowr ) as oferty_symbol_edi,

				_VarINT(P.magTyp) AS magTyp,
				*/
				L.uwagi as PrzelicznikUwagi,
				/*
				_VarDOUBLE(0) AS CenaNetWalzRab,
				_ConcatStr(IF(_VarInt(P.ilosc div T.opakowanie) = 0, '', CONCAT(_VarStr(P.ilosc div T.opakowanie), ' op.')),
					IF(_VarInt(P.ilosc mod T.opakowanie) = 0, '', CONCAT(_VarStr(P.ilosc mod T.opakowanie), ' ', J.Nazwa)), ' + ') AS opakowan,
				-- CONCAT(P.ilosc div T.opakowanie,"+",P.ilosc mod T.opakowanie) AS opakowan,
				'.' AS nrdokmag,  _VarINT(N.waluta) as waluta,  '.' AS nrseryjny,  P.uwagi AS uwagi2,  '.' AS towrnazwa2,
				*/
				P.terminrealizacji ,
				-- P.osobaGUID AS osobaGUID,  N.datawystaw AS datawystaw_pozycji,
				/*
				CONCAT(O.Imiona," ",O.Nazwisko) AS osobaNazwiskoImie, CAST(N.datawystaw  AS char) AS datazamowienia,
				W.Symbol AS WalutaStr,
				IFNULL(CONCAT(P.lp," "),"1") AS zplp,  P.Lp,
				P.cenanet AS cenamag,  P.wartnet AS wartmag,
				P.wartnet,  P.wartvat, P.wartvat/P.kurs AS wartvatwal,
				if(P.wartvat=0,P.wartnetwal,P.wartbrt/P.kurs) AS wartbrtwal,
				if(P.wartvat=0,P.wartnetwal,P.wartbrt/P.kurs) AS wartbrt,
				(P.wartbrt/P.ilosc) AS cenabrt,
				(P.wartnet/P.kurs) AS wartnetpln,  P.usluga,  P.kurs,
				T.NazwaUProducenta,  T.kod_kreskowy,  T.kod_kreskowy1, T.kod_kreskowy2, T.Opis,  T.waga AS wagatowaru,
				(P.Ilosc/P.ilewopakowaniu) AS ileopakowan,
				_VarDouble(((P.ilosc-1) div T.opakowanie_zbiorcze)+1) AS ilosc_palet,
				_VarDouble(P.waga+(((P.ilosc-1) div T.opakowanie_zbiorcze)+1) * 20) AS wagabrt,
				P.oplata_produktowa,  P.ilewopakowaniu,  J.EDI_index,
				T.akcyza, T.pcn,  T.podlega_akcyzie,  T.symboludost, T.SymbolProducenta, T.dataprodukcji, T.nazwa_segment3,
				T.wyroznik,
				P.cenanet, P.idntowr,  P.wspolplatnosc,  P.rabat,  P.uwagi,  P.waga, P.waga AS waga_pozycji,
				P.ilosc AS Ilosc2, P.iloscdo AS IloscDo,
				P.idnvat, P.Rodzaj_przeliczenia, N.nrzamw, N.nrzamwkontr,  P.Lp AS grupuj,
				LEFT('.',38) AS GUIDKorPO,  P.wartnetWal AS wartnetWal,
				CONCAT(os.Imiona," ",os.Nazwisko) AS Osoba_ImieNazwisko, os.info1 AS Osoba_Info1, os.info2 AS Osoba_Info2,
				T.treenode, T.symbol, T.pkwiu, T.sww,
*/
				kc.Valuex AS Kategoria_Towaru
			FROM `t_prn_zmpo` P
			LEFT JOIN t_prn_zmng N ON N.GUID = P.idnNG
			LEFT JOIN `t_prn_zmng_akwizytor` A ON A.GUID = N.AkwizytorGUID

			LEFT JOIN crm_commisions c ON c.GUID = N.wyjazdGUID
			LEFT JOIN erp_area_nodes ean ON c.areaGUID = ean.GUID
			left join osoby ok ON ok.guid = c.template_osoba

			LEFT JOIN `TowrNg` T ON T.GUID = P.idntowr
			LEFT JOIN `Jednostki` J ON J.IDN = P.Jednostka
			LEFT JOIN `Jednostki` JJ ON JJ.Idn = T.jednostka_pom
			LEFT JOIN `przeliczniki` L ON L.GUID = P.ID_Przelicznika
			LEFT JOIN `katcols` kc ON kc.Klucz = P.kat5 AND kc.colnr = 1 AND kc.typ = 1
			LEFT JOIN slowniki_pierwotne spier ON spier.Slownik_GUID='ZM_TRANSZE' AND spier.Value=N.transza_zamow
			-- LEFT JOIN rcng R ON R.GUID = P.recepturaGUID
			LEFT JOIN osoby O ON O.GUID = P.osobaGUID
			LEFT JOIN waluty W ON W.idn = N.waluta
			LEFT JOIN osoby os ON os.GUID = P.osobaGUID
			LEFT JOIN Magazyny Mag ON Mag.IdnMagazynu= N.MagazynWys
			WHERE
				N.Checked <> 0
				AND P.Deleted = 0 		--	patrz CALL fr_ht_ZM_Tool(52, ...)
				AND T.opakowanie_zwrotne = 0
			-- GROUP BY N.AkwizytorGUID, P.IdnTowr,
			GROUP BY N.wyjazdGUID, P.IdnTowr,
			-- P.recepturaGUID, zad 49287
			IFNULL(P.ID_Przelicznika,'') , IF(P.ilosc_M_do_przeliczen = 1, P.ilosc_M_do_przeliczen, CONCAT(P.ilosc_L_do_przeliczen,P.ilosc_M_do_przeliczen)), P.Jednostka
			ORDER BY KierowcaNazwa, Kategoria_Towaru, P.towrsymb, P.towrnazwa
			;


			DROP TEMPORARY TABLE IF EXISTS t_KpakowaczSum;

			CREATE TEMPORARY TABLE t_KpakowaczSum
			SELECT
			    KierowcaNazwa,
				SUM(Opakowan_Max) as sumka,
				Jedn_pom
			from t_Kpakowacz
			where IFNULL(KierowcaNazwa,'')<>''
			GROUP BY KierowcaNazwa,Jedn_pom;


			-- tabela stala, uzywana w skrypcie /automaty/pakowacz_kopia.sh
			drop table if exists `ta_pakowacz`;
			create table `ta_pakowacz`
			select t.*, concat('',(select group_concat(concat(sumka,' ',Jedn_pom) separator '\n') from t_KpakowaczSum where KierowcaNazwa = t.KierowcaNazwa),'') as sumka from t_Kpakowacz t
			;

/*
			select t.*, concat('',(select group_concat(concat(sumka,' ',Jedn_pom) separator '\n') from t_KpakowaczSum where KierowcaNazwa = t.KierowcaNazwa),'') as sumka from t_Kpakowacz t;
*/
			-- select t.*, "xx" as sumka from t_Kpakowacz t;



END IF;

END$$

DELIMITER ;

