/*****************************************************
REWIZJE
******************************************************
$Log: ht_mhdroid.sql,v $
Revision 1.26  2015/06/26 06:27:41  apro
zadania - kontrah_todo

Revision 1.25  2015/06/16 09:39:41  apro
drobne porządki

Revision 1.24  2015/06/09 07:07:55  apro
poprawka w wizytach

Revision 1.23  2015/06/02 10:32:03  apro
*** empty log message ***

Revision 1.22  2015/05/19 05:05:33  apro
wizyty z CRM

Revision 1.21  2015/04/21 09:53:17  waliant
Poprawka błędu kolejności wpisów na pocz. procedury.

Revision 1.20  2015/04/21 09:47:14  waliant
Dodanie płatności "-"

Revision 1.19  2015/01/22 11:07:46  apro
oferty

Revision 1.18  2015/01/22 07:45:46  apro
*** empty log message ***

*/


DELIMITER $$

DROP PROCEDURE IF EXISTS `ht_mhdroid`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `ht_mhdroid`(
	IN _MODE VARCHAR(150),
	IN _LOGIN VARCHAR(150),
	IN _WYJAZD VARCHAR(38),
	IN _ALOCALTIME VARCHAR(20),
	IN _LATITUDE VARCHAR(38),
	IN _LONGITUDE VARCHAR(38),
	IN _PARAM_A VARCHAR(100),
	IN _PARAM_B VARCHAR(100),
	IN _PARAM_C VARCHAR(100)
)
-- call ht_mhdroid('DEF_NG','jmam','','','','','','','');
-- call ht_mhdroid('DEF_PO','jmam','','','','','','','');
-- call ht_mhdroid('DICT','jmam','','','','','','','');
BEGIN
	SET @ht_mhdroid_result := -1;

  SET _ALOCALTIME=IF(IFNULL(_ALOCALTIME,'')='','1900-01-01 00:00:00',_ALOCALTIME);
	
	IF _MODE = 'DEF_NG' THEN
		SET @ht_mhdroid_result := 0;
		SELECT * FROM droid_ankiety_def_ng WHERE status=0; -- TODO ugrpguid
	END IF;	

	IF _MODE = 'DEF_PO' THEN
		SET @ht_mhdroid_result := 0;
		SELECT p.* FROM droid_ankiety_def_po p,droid_ankiety_def_ng n WHERE n.GUID=p.GUIDng AND n.status=0 AND p.status=0; -- TODO ugrpguid
	END IF;	

	IF _MODE = 'DICT' THEN
		SET @ht_mhdroid_result := 0;
-- katcol handlowca
		SET @handlowiec := 0;

		SELECT klucz from katcols WHERE typ=0 and colnr=5 and param2=_LOGIN INTO @handlowiec;
		IF _LOGIN in ('jmam','mosakowskip') THEN SET @handlowiec := 3; END IF; -- wlodarczyk
		IF _LOGIN in ('denysm') THEN SET @handlowiec := 9; END IF; -- janas
		IF _LOGIN in ('drewniakl') THEN SET @handlowiec := 2; END IF; -- bielawski
		IF _LOGIN in ('kampat') THEN SET @handlowiec := 15; END IF; -- grygier
		IF _LOGIN in ('chudelp') THEN SET @handlowiec := 7; END IF; -- osinski
		IF _LOGIN in ('chudyl') THEN SET @handlowiec := 6; END IF; -- cybak
		IF _LOGIN in ('rydlewskiad') THEN SET @handlowiec := 4; END IF; -- przybyl
		IF _LOGIN in ('pokorskip','przybylr') THEN	SET @handlowiec := -1; END IF;

		DROP TEMPORARY TABLE IF EXISTS ankiety_slowniki;

		CREATE TEMPORARY TABLE `ankiety_slowniki` (
			`datasource` varchar(38) NOT NULL DEFAULT '', -- guid/nazwa slownika - np kontrah, TowrNg
			`ValueKey` varchar(38) NOT NULL DEFAULT '', -- guid lub idn pozycji slownika - np. kontrah.guid  lub oferty.guid dla cennik�w klienta
			`FilterKey1` varchar(38) NOT NULL DEFAULT '', -- pierszy guid w slownika do filtracji - np. TowrNg.guid w s�owniku cen klient�w
			`FilterKey2` varchar(38) NOT NULL DEFAULT '', -- drugi guid w slownika do filtracji  - np. kontrah.guid w s�owniku cen klient�w
			`FilterKey3` varchar(38) NOT NULL DEFAULT '', -- trzeci guid w slownika do filtracji - zapas
			`ValueDescr` varchar(255) NOT NULL DEFAULT '', -- opis pozycji slownika - np. kontrah.symbol
			`ValueDescrExt` varchar(255) NOT NULL DEFAULT '',  -- opis pozycji slownika rozszerzony - np. kontah.nazwa + adres + cokolwiek
			`alocaltime` timestamp, -- czas stworzenia zapisu na serwerze

		KEY `datasource` (`datasource`),
		UNIQUE KEY `ds_vk` (`datasource`,`ValueKey`)
		);

		INSERT INTO ankiety_slowniki (datasource,valuekey,valuedescr) VALUES ("inwentaryzacja","",""),
		("inwentaryzacja","NW","nie wykonana"),("inwentaryzacja","WZ","wykonana - zgodna"),("inwentaryzacja","WN","wykonana - niezgodna");

		INSERT INTO ankiety_slowniki (datasource,valuekey,valuedescr) VALUES 
		("typceny","netto","netto"),("typceny","brutto","brutto");

		INSERT INTO ankiety_slowniki (datasource,valuekey,valuedescr) VALUES 
		("czestosc","7","7 dni"),("czestosc","14","14 dni"),("czestosc","21","21 dni");

		INSERT INTO ankiety_slowniki (datasource,valuekey,valuedescr) VALUES 
--		("decyzjaklienta","ok","ok"),("decyzjaklienta","czekac","czeka� - dowiadywa� si�"),("decyzjaklienta","wycofuje","wycofuje si�");
		("decyzjaklienta","-","-"),
		("decyzjaklienta","odezwiesie","odezwie się"),
		("decyzjaklienta","pozytywna","pozytywna"),
		("decyzjaklienta","zrobicinna","złożyć inną ofertę"),
		("decyzjaklienta","niechce","nie chce"),
		("decyzjaklienta","naprobe","dać na próbę"),
		("decyzjaklienta","czekac","czekać - dowiadywać się");

		INSERT INTO ankiety_slowniki (datasource,valuekey,valuedescr) VALUES 
		("stanowiska","-","-"),
		("stanowiska","kierownik","kierownik"),
		("stanowiska","zaopatrzeniowiec","zaopatrzeniowiec"),
		("stanowiska","wlasciciel","(współwłaściciel"),
		("stanowiska","prezes","prezes/dyrektor"),
		("stanowiska","inne","inne");

		INSERT INTO ankiety_slowniki SELECT "ceny_lista",'-','','','','-','',now();
		SET @i:=45;
		REPEAT
			INSERT INTO ankiety_slowniki (datasource,valuekey,valuedescr) VALUES ("ceny_lista",@i,concat(@i," zł"));SET @i:=@i+1;
		UNTIL @i > 65 
		END REPEAT;

		INSERT INTO ankiety_slowniki SELECT "prowizje",'-','','','','-','',now();
		SET @i:=2;
		REPEAT
			INSERT INTO ankiety_slowniki (datasource,valuekey,valuedescr) VALUES ("prowizje",@i,concat(@i," zł"));SET @i:=@i+1;
		UNTIL @i > 5 
		END REPEAT;

		INSERT INTO ankiety_slowniki SELECT "klatka_ilebutli",'-','','','','-','',now();
		SET @i:=1;
		REPEAT
			INSERT INTO ankiety_slowniki (datasource,valuekey,valuedescr) VALUES ("klatka_ilebutli",lpad(@i,2,'0'),concat(@i," szt"));SET @i:=@i+1;
		UNTIL @i > 20 
		END REPEAT;

--		FROM slowniki_pierwotne WHERE slownikGUID='MHDroid_inwentaryzacja';

		INSERT INTO ankiety_slowniki SELECT "kontrah",k.guid,'','','',concat(k.symbol,' (',k.nazwamiasta,')'),concat(k.nazwa,' ',k.ulica,' ',k.nazwamiasta),k.localtime
		from kontrah k
		WHERE k.treenode not like '*|HURT%' and 
		k.deleted=0 and k.areaguid<>'' and k.localtime>=_ALOCALTIME AND (k.katcol4=@handlowiec or (@handlowiec=-1 and k.katcol4 in (4,6,7,9,15,19)))
		AND k.katcol3 in (1,2,4,6,8,9,10,12,14,15,16,17,18,19,20,21,22,23,24,47,48,49) and ifnull(k.serwis_koment,'')<>''
		ORDER BY k.symbol; 
/*
		INSERT INTO ankiety_slowniki SELECT "kontrah_info",k.guid,'','','',concat('<B>',k.symbol,'</B>:',if(k.serwis_koment='','<brak info>',k.serwis_koment)),'',k.localtime
		from kontrah k
		WHERE k.deleted=0 and k.areaguid<>'' and k.localtime>=_ALOCALTIME AND (k.katcol4=@handlowiec or (@handlowiec=-1 and k.katcol4 in (4,6,7,8)))
		AND k.katcol3 in (1,2,4,6,8,9,10,12,14,15,16,17,18,19,20,21,22,23,24,47,48,49) and ifnull(k.serwis_koment,'')<>''
		ORDER BY k.symbol; 
*/
		INSERT INTO ankiety_slowniki SELECT "kontrah_info",k.guid,'','','',concat('<B>',k.symbol,'</B>:',
		(select group_concat(t.symbol,':',round(c.cena,2)) from ht_kontrah_ceny c,TowrNg t where t.wyroznik in ('G','GP') and t.guid=c.idntowr and c.kontrguid=k.guid),'<BR>',
		if(k.serwis_koment='','<brak info>',k.serwis_koment)),'',k.localtime
		from kontrah k
		WHERE k.deleted=0 and k.areaguid<>'' and k.localtime>=_ALOCALTIME AND (k.katcol4=@handlowiec or (@handlowiec=-1 and k.katcol4 in (4,6,7,8,15,19)))
		AND k.katcol3 in (1,2,4,6,8,9,10,12,14,15,16,17,18,19,20,21,22,23,24,47,48,49)
		ORDER BY k.symbol; 

-- TODO <BR> info o cenach z ofert<BR>

		INSERT INTO ankiety_slowniki SELECT "konkurencja",'-','','','','-','',now();
		INSERT INTO ankiety_slowniki SELECT "konkurencja",k.guid,'','','',k.symbol,concat(k.nazwa,' ',k.ulica,' ',k.nazwamiasta),k.localtime
		from kontrah k
		WHERE k.treenode like '*|KONKURENCJA%' and k.deleted=0 and k.localtime>=_ALOCALTIME;

		INSERT INTO ankiety_slowniki SELECT "kontrah_hurt",k.guid,'','','',k.symbol,concat(k.nazwa,' ',k.ulica,' ',k.nazwamiasta),k.localtime
		from kontrah k
		WHERE k.treenode like '*|HURT%' and 
		k.deleted=0 and k.areaguid<>'' and k.localtime>=_ALOCALTIME AND (k.katcol4=@handlowiec or (@handlowiec=-1 and k.katcol4 in (4,6,7,8,15,19)))
		AND k.katcol3 in (1,2,4,6,8,9,10,12,14,15,16,17,18,19,20,21,22,23,24,47,48,49) and ifnull(k.serwis_koment,'')<>''
		ORDER BY k.symbol;

		INSERT INTO ankiety_slowniki SELECT "kontrah_hurt_info",k.guid,'','','',concat(k.symbol,':',if(k.serwis_koment='','<brak info>',k.serwis_koment)),'',k.localtime
		from kontrah k
		WHERE k.treenode like '*|HURT%' and 
		k.deleted=0 and k.areaguid<>'' and k.localtime>=_ALOCALTIME AND (k.katcol4=@handlowiec or (@handlowiec=-1 and k.katcol4 in (4,6,7,8,15,19)))
		AND k.katcol3 in (1,2,4,6,8,9,10,12,14,15,16,17,18,19,20,21,22,23,24,47,48,49) and ifnull(k.serwis_koment,'')<>''
		ORDER BY k.symbol;

-- TODO - przelaczyc sie na crm_commisions - niezrealizowane wizyty danego handlowca
		DROP TEMPORARY TABLE IF EXISTS temp_wizyty;
		CREATE TEMPORARY TABLE temp_wizyty
		SELECT DISTINCT c.rodzaj,
		max(c.starttime) as starttime,max(c.temat) as temat,k.guid as kontrguid
		FROM crm_commisions c, kontrah k,crm_usr_links lk,osoby ok
		WHERE c.rodzaj=5 AND c.status IN (1,2) AND
		k.guid=ok.kontrguid AND ok.guid=lk.osoba AND lk.commisionguid=c.guid AND lk.poziom=4 AND 
		(k.katcol4=@handlowiec or (@handlowiec=-1 and k.katcol4 in (4,6,7,8,15,19)))
		GROUP BY kontrguid;
		
		ALTER TABLE temp_wizyty ADD INDEX (kontrguid);


		INSERT INTO ankiety_slowniki 
		SELECT DISTINCT if(t.temat LIKE "%r2r%","kontrah_r2r","kontrah_todo"),k.guid,'','','',k.symbol,
		concat(if(t.temat LIKE "%r2r%","r2r: ","zad: "),k.nazwa,' ',k.ulica,' ',k.nazwamiasta,' ',left(t.temat,50)),k.localtime
		from kontrah k, temp_wizyty t
		WHERE k.guid=t.kontrguid;
		
		IF _Param_C<>'debug' THEN
			DROP TEMPORARY TABLE temp_wizyty;
		END IF;

		INSERT INTO ankiety_slowniki SELECT "TowrNg",'-','','','','-','',now();
		INSERT INTO ankiety_slowniki SELECT "TowrNg",t.guid,'','','',t.symbol,t.nazwa,t.localtime
		from TowrNg t
		WHERE t.promocja_status<>2 and t.status_www=1 and wyroznik in ('G','GT') and t.localtime>=_ALOCALTIME;

		INSERT INTO ankiety_slowniki SELECT "sprzet",'-','','','','-','',now();
		INSERT INTO ankiety_slowniki SELECT "sprzet",t.guid,'','','',t.symbol,t.nazwa,t.localtime
		from TowrNg t
		WHERE t.promocja_status<>2 and 
		(symbol like "K-%") and wyroznik in ('T') and t.localtime>=_ALOCALTIME;

		INSERT INTO ankiety_slowniki SELECT "oferty",'-','','','','-','',now();
		INSERT INTO ankiety_slowniki SELECT "oferty",o.idn,o.idntowr,o.kontrguid,'',t.symbol,concat('akt. cena:',round(o.cena,2),' data:',date(o.dataoferty)),o.alocaltime
		from oferty o
		INNER JOIN TowrNg t on t.guid=o.idntowr
		INNER JOIN kontrah k on k.guid=o.kontrguid
		WHERE t.promocja_status<>2 and t.status_www=1 and t.wyroznik in ('G','GT') and o.alocaltime>=_ALOCALTIME
		AND k.deleted=0 and k.areaguid<>'' and k.localtime>=_ALOCALTIME AND (k.katcol4=@handlowiec or (@handlowiec=-1 and k.katcol4 in (4,6,7,8,15,19)))
		AND k.katcol3 in (1,2,4,6,8,9,10,12,14,15,16,17,18,19,20,21,22,23,24,47,48,49) and ifnull(k.serwis_koment,'')<>''
		;
		
		INSERT INTO ankiety_slowniki SELECT "platnosc",'-','','','','-','',now();
		INSERT INTO ankiety_slowniki SELECT "platnosc",p.idn,'','','',concat(p.nazwa,' ',p.dni),'',now()
		FROM platnosc p
		WHERE p.idn in (1,4,5,8,9,10,11);

		
		INSERT INTO ankiety_slowniki SELECT "klasyfikacja",'-','','','','-','',now();
		INSERT INTO ankiety_slowniki SELECT "klasyfikacja",'1 SYFON','','','','1 SYFON','',now();
		INSERT INTO ankiety_slowniki SELECT "klasyfikacja",'2 PROPAN','','','','2 PROPAN','',now();
		INSERT INTO ankiety_slowniki SELECT "klasyfikacja",'3 MIX','','','','3 MIX','',now();
		INSERT INTO ankiety_slowniki SELECT "klasyfikacja",'4 B33','','','','4 B33','',now();
		INSERT INTO ankiety_slowniki SELECT "klasyfikacja",'5 L10','','','','5 L10','',now();

		INSERT INTO ankiety_slowniki SELECT "typkontaktu",'-','','','','-','',now();
		INSERT INTO ankiety_slowniki SELECT "typkontaktu",'wizyta','','','','wizyta','',now();
		INSERT INTO ankiety_slowniki SELECT "typkontaktu",'telefon','','','','telefon','',now();
		INSERT INTO ankiety_slowniki SELECT "typkontaktu",'mail','','','','mail','',now();


		IF _Param_C='debug' THEN
			SELECT * FROM ankiety_slowniki WHERE datasource=_Param_B or _Param_B="";
		ELSE
			SELECT * FROM ankiety_slowniki;
			DROP TEMPORARY TABLE ankiety_slowniki;
		END IF;	

	END IF;	
	
	
	IF @ht_mhdroid_result = -1 THEN
		SELECT "MODE NIE ZDEFINIOWANY" AS STATUS,_MODE AS `mode`;
	END IF;
	
END $$


DROP PROCEDURE IF EXISTS `ht_mhdroid_ng`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `ht_mhdroid_ng`(
	IN guid VARCHAR(38),
	IN ankietadefguid VARCHAR(38), 
	IN login VARCHAR(38),
	IN idDevice  VARCHAR(38),
	IN name VARCHAR(150),
	IN droidtime DATETIME,
	IN status INT,
	IN _p1   VARCHAR(250),
	IN _p2   VARCHAR(250),
	IN _p3   VARCHAR(250)
)

BEGIN
	REPLACE `droid_ankiety_dane_ng` (guid, ankietadefguid,login, idDevice, name, droidtime,status)
	VALUES (guid, ankietadefguid,login, idDevice, name, droidtime,status);
END $$

DROP PROCEDURE IF EXISTS `ht_mhdroid_po`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `ht_mhdroid_po`(
	IN guid VARCHAR(38),
	IN guidng VARCHAR(38),
	IN ankietadefpoguid VARCHAR(38), 
	IN valuekey VARCHAR(38),
	IN valuedescr VARCHAR(255),
	IN latitude VARCHAR(38),
	IN longitude VARCHAR(38),
	IN droidtime DATETIME,
	IN _p1   VARCHAR(250),
	IN _p2   VARCHAR(250),
	IN _p3   VARCHAR(250)
)
--   $sql = "CALL ht_mhdroid_po('".$e->{'guid'}."','".$e->{'guidng'}."','".$e->{'ankietadefpoguid'}."','".
-- $e->{'valuekey'}."','".$e->{'valuedescr'}."','".$e->{'latitude'}."','".$e->{'longitude'}."','".$e->{'droidtime'}."','','','')";     
 
BEGIN
	REPLACE `droid_ankiety_dane_po` (guid, guidng, ankietadefpoguid, valuekey, valuedescr, latitude, longitude, droidtime)
	VALUES (guid, guidng, ankietadefpoguid, valuekey, valuedescr, latitude, longitude, droidtime);
END $$

DROP PROCEDURE IF EXISTS `ht_mhdroid_ceny`$$

/*
call ht_mhdroid_ceny('ALL','','','','VERBOSE');
call ht_mhdroid_ceny('AnkietaGUID','5d31f6087851a5786a502ff3f5cb4d73','','','VERBOSE,DEBUG');
-- mosakowski {9607BB15-84C9-448C-AA0E-0DA8469A229A}
select * from oferty where kontrguid='{9607BB15-84C9-448C-AA0E-0DA8469A229A}';
*/ 




CREATE DEFINER=`root`@`localhost` PROCEDURE `ht_mhdroid_ceny`(
	IN _MODE VARCHAR(250),
	IN _GUID VARCHAR(38),
	IN _p1   VARCHAR(250),
	IN _p2   VARCHAR(250),
	IN _p3   VARCHAR(250)
)

BEGIN
	SET @ht_mhdroid_result = -1;

	IF _MODE = 'AnkietaGUID' THEN
		SET @ht_mhdroid_result = 0;
		SET @kontrah:='';
		SET @towar:='';
		SET @cena:=0;
		DROP TEMPORARY TABLE IF EXISTS temp_zmiana_ceny;
		CREATE TEMPORARY TABLE temp_zmiana_ceny
		SELECT
			p.idn,n.login,p.AnkietaDefPoGUID,p.status,
			@kontrah:=if(p.AnkietaDefPoGUID='CDG_MHv190','',if(p.AnkietaDefPoGUID in ('CDG_MHv12200','CDG_MHv1310'),p.ValueKey,@kontrah)) as kontrGUID,
			@towar:=if(p.AnkietaDefPoGUID in ('CDG_MHv12200'),'',if(p.AnkietaDefPoGUID in ('CDG_MHv12310','CDG_MHv12410'),p.ValueKey,@towar)) as idntowr,
			@cena:=if(p.AnkietaDefPoGUID in ('CDG_MHv12310','CDG_MHv12410','CDG_MHv12200'),0,if(p.AnkietaDefPoGUID in ('CDG_MHv12320','CDG_MHv12420'),p.ValueDescr,@cena)) as cena,
			p.ValueKey,p.ValueDescr,
			p.droidtime
		FROM 	droid_ankiety_dane_po p, droid_ankiety_dane_ng n
		WHERE 	n.guid=p.guidng AND p.guidng=_GUID AND p.status=0 
			AND p.AnkietaDefPoGUID in ('CDG_MHv190','CDG_MHv1310','CDG_MHv12200','CDG_MHv12210','CDG_MHv12220','CDG_MHv12300','CDG_MHv12310','CDG_MHv12320','CDG_MHv12330','CDG_MHv12400','CDG_MHv12410','CDG_MHv12420','CDG_MHv12430')
		ORDER BY p.droidtime,p.AnkietaDefPoGUID;

		BEGIN
			DECLARE done BOOL DEFAULT FALSE;
			DECLARE _Cena DOUBLE(16,2) DEFAULT 0;
			DECLARE _KontrGUID VARCHAR(38) DEFAULT '';
			DECLARE _IdnTowr VARCHAR(38) DEFAULT '';
			DECLARE _Login VARCHAR(38) DEFAULT '';
			DECLARE _Idn INT DEFAULT 0;
			DECLARE _DroidTime DATETIME;

			DECLARE c_proc CURSOR FOR
			SELECT kontrguid,idntowr,cena,idn,droidtime,login FROM temp_zmiana_ceny WHERE AnkietaDefPoGUID in ('CDG_MHv12330','CDG_MHv12430');
			DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
			
			OPEN c_proc;
			  loop1: LOOP
				SET done = FALSE;
				FETCH c_proc INTO _Kontrguid,_IdnTowr,_Cena,_Idn,_DroidTime,_Login;
				IF done THEN
					LEAVE loop1;
				ELSE
					IF _p3 like '%VERBOSE%' THEN
						SELECT now(),_GUID as AnkietaGUID,_Kontrguid,
							IFNULL((select k.symbol from kontrah k WHERE k.guid=_KontrGuid and _KontrGuid<>''),'brak kontrahenta!') as kontrahent,
							_IdnTowr,_Cena,_Idn,_DroidTime,_Login;
					END IF;
					IF _KontrGuid<>'' AND _IdnTowr<>'' and _Cena<>0  THEN
					
						UPDATE oferty SET jezyk=-1 WHERE kontrguid=_Kontrguid AND idntowr=_IdnTowr;
						INSERT INTO oferty SET 
							kontrguid=_Kontrguid,idntowr=_IdnTowr,cena=_Cena,symbol=_IdnTowr,
							dataoferty=_DroidTime,czy_ceny_brt=1,info=concat(_Login,' ',_VarStr(_DroidTime),' ',_VarStr(_Idn));
						DELETE FROM oferty WHERE jezyk=-1 AND kontrguid=_Kontrguid AND idntowr=_IdnTowr;
					END IF;
-- status -1 dla blednie wybranych kontrahentow, 1 dla poprawnie przetworzonej ankiety
					UPDATE droid_ankiety_dane_po p SET alocaltime=alocaltime,status=IF(_KontrGuid<>'',1,-1) WHERE idn=_Idn;
				END IF;
			END LOOP;
			CLOSE c_proc;
			IF _p3 not like '%DEBUG%' THEN
				DROP TEMPORARY TABLE IF EXISTS temp_zmiana_ceny;
			END IF;
		END;
	
	END IF;

	IF _MODE = 'ALL' THEN
		SET @ht_mhdroid_result = 0;
		BEGIN
			DECLARE done BOOL DEFAULT FALSE;
			DECLARE _GUID VARCHAR(38) DEFAULT '';

			DECLARE c_proc CURSOR FOR
			SELECT DISTINCT guidng FROM droid_ankiety_dane_po WHERE AnkietaDefPoGUID in ('CDG_MHv12330','CDG_MHv12430') and status=0;
			DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
			
			OPEN c_proc;
				loop2: LOOP
				SET done = FALSE;
				FETCH c_proc INTO _GUID;
				IF done THEN
					LEAVE loop2;
				ELSE
					CALL ht_mhdroid_ceny('AnkietaGUID',_GUID,_p1,_p2,_p3);
				END IF;
			END LOOP;
			CLOSE c_proc;
		END;
	END IF;

	IF @ht_mhdroid_result = -1 THEN
		SELECT "MODE NIE ZDEFINIOWANY" AS STATUS,_MODE AS `mode`;
	END IF;

END $$


DROP PROCEDURE IF EXISTS `ht_mhdroid_kontrah`$$

/*
call ht_mhdroid_kontrah('ALL','','','','VERBOSE');
call ht_mhdroid_kontrah('AnkietaGUID','5d31f6087851a5786a502ff3f5cb4d73','','','VERBOSE,DEBUG');
-- mosakowski {9607BB15-84C9-448C-AA0E-0DA8469A229A}
select * from kontrah where guid='{9607BB15-84C9-448C-AA0E-0DA8469A229A}';
*/ 


CREATE DEFINER=`root`@`localhost` PROCEDURE `ht_mhdroid_kontrah`(
	IN _MODE VARCHAR(250),
	IN _GUID VARCHAR(38),
	IN _p1   VARCHAR(250),
	IN _p2   VARCHAR(250),
	IN _p3   VARCHAR(250)
)

BEGIN
	SET @ht_mhdroid_result = -1;

	IF _MODE = 'AnkietaGUID' THEN
		SET @ht_mhdroid_result = 0;
		SET @kontrah:='';
		SET @multibrand:='';
		SET @brakmultibrand:=0;
		SET @inwentaryzacja:='';
		SET @inwentaryzacja_opis:='';

		TRUNCATE temp_zmiany_kntr;
		TRUNCATE temp_zmiany_kntr2;
		TRUNCATE temp_zmiany_kntr3;

		INSERT INTO temp_zmiany_kntr
		SELECT
			p.idn,n.login,p.AnkietaDefPoGUID,p.status,
			@kontrah:=if(p.AnkietaDefPoGUID='CDG_MHv190','',if(p.AnkietaDefPoGUID in ('CDG_MHv1300','CDG_MHv1310','CDG_MHv1320'),p.ValueKey,@kontrah)) as kontrGUID,
			@brakmultibrand:=LEFT(if(p.AnkietaDefPoGUID in ('CDG_MHv1425','CDG_MHv1472','CDG_MHv1300','CDG_MHv1310','CDG_MHv1320'),'',if(p.AnkietaDefPoGUID='CDG_MHv1395',p.ValueDescr,@brakmultibrand)),100) as brakmultibrand,
			@multibrand:=LEFT(if(p.AnkietaDefPoGUID in ('CDG_MHv1425','CDG_MHv1472','CDG_MHv1395','CDG_MHv1300','CDG_MHv1310','CDG_MHv1320') or @brakmultibrand like '%nie ma%','',if(p.AnkietaDefPoGUID='CDG_MHv1410',p.ValueDescr,@multibrand)),100) as multibrand,
			@inwentaryzacja:=LEFT(if(p.AnkietaDefPoGUID in ('CDG_MHv1425','CDG_MHv1472','CDG_MHv1395','CDG_MHv1300','CDG_MHv1310','CDG_MHv1320'),'',
			  if(p.AnkietaDefPoGUID in ('CDG_MHv11175','CDG_MHv1520'),p.ValueKey,@inwentaryzacja)),100) as inwentaryzacja, -- NW WZ WN
			@inwentaryzacja_opis:=LEFT(if(p.AnkietaDefPoGUID in ('CDG_MHv1425','CDG_MHv1472','CDG_MHv1395','CDG_MHv1300','CDG_MHv1310','CDG_MHv1320'),'',
			  if(p.AnkietaDefPoGUID in ('CDG_MHv11180','CDG_MHv1530'),p.ValueDescr,@inwentaryzacja_opis)),100) as inwentaryzacja_opis,
-- inwentaryzacja zgodna (kontrah.data_inwent i opis_niezgodn) CDG_MHv1520,CDG_MHv11175 status inw i CDG_MHv1530,CDG_MHv11180 - opis 

--			(select concat(d.form,':',d.label) from droid_ankiety_def_po d where d.guid=p.AnkietaDefPoGUID) as pole,p.ValueKey,p.ValueDescr,
			p.droidtime
		FROM 	droid_ankiety_dane_po p, droid_ankiety_dane_ng n
		WHERE 	n.guid=p.guidng AND p.guidng=_GUID AND p.status=0 
			AND p.AnkietaDefPoGUID in ('CDG_MHv190','CDG_MHv12200','CDG_MHv12210','CDG_MHv1425','CDG_MHv1395','CDG_MHv1472','CDG_MHv1410','CDG_MHv1300','CDG_MHv1310','CDG_MHv1320',
			'CDG_MHv11175','CDG_MHv11180','CDG_MHv1520','CDG_MHv1530' -- inwentaryzacje
			'CDG_MHv11220','CDG_MHv1590' -- koniec wizyty
			)
		ORDER BY p.droidtime,p.AnkietaDefPoGUID;
		IF _p3 like '%VERBOSE%' THEN
			SELECT k.symbol,t.* FROM temp_zmiany_kntr t,kontrah k WHERE k.guid=t.kontrguid;
		END IF;
			
		BEGIN
			DECLARE done BOOL DEFAULT FALSE;
			DECLARE _KontrGUID VARCHAR(38) DEFAULT '';
			DECLARE _AnkietaDefPoGUID VARCHAR(38) DEFAULT '';
			DECLARE _KontrSymbol VARCHAR(38) DEFAULT '';
			DECLARE _MultiBrand VARCHAR(100) DEFAULT '';
			DECLARE _MultiBrand_Old VARCHAR(100) DEFAULT '';
			DECLARE _Inwentaryzacja VARCHAR(200) DEFAULT '';
			DECLARE _Inwentaryzacja_opis VARCHAR(200) DEFAULT '';
			DECLARE _Login VARCHAR(38) DEFAULT '';
			DECLARE _Idn INT DEFAULT 0;
			DECLARE _DroidTime DATETIME;
			DECLARE c_proc CURSOR FOR
			SELECT t.kontrguid,t.AnkietaDefPoGUID,k.kategoria,t.multibrand,t.idn,t.droidtime,t.login,t.inwentaryzacja,t.inwentaryzacja_opis,k.symbol
			FROM temp_zmiany_kntr t, kontrah k 
			WHERE k.guid=t.kontrguid and t.AnkietaDefPoGUID in ('CDG_MHv1410','CDG_MHv1395','CDG_MHv11175','CDG_MHv11180','CDG_MHv1520','CDG_MHv1530') 
			  and IF(t.AnkietaDefPoGUID in ('CDG_MHv11175','CDG_MHv1520'),t.inwentaryzacja<>'NW',1) 
			  and IF(t.AnkietaDefPoGUID in ('CDG_MHv11180','CDG_MHv1530'),t.inwentaryzacja_opis<>'',1)  
			ORDER BY droidtime;

			DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
			
			OPEN c_proc;
			  loop1: LOOP
				SET done = FALSE;
				FETCH c_proc INTO _Kontrguid,_AnkietaDefPoGUID,_MultiBrand_Old,_MultiBrand,_Idn,_DroidTime,_Login,_Inwentaryzacja,_Inwentaryzacja_opis,_KontrSymbol;
				IF done THEN
					LEAVE loop1;
				ELSE
					IF _p3 like '%VERBOSE%' THEN
						SELECT now(),_GUID as AnkietaGUID,_KontrSymbol,_Kontrguid,_MultiBrand,_Inwentaryzacja,_Inwentaryzacja_opis,_Idn,_DroidTime,_Login;
					END IF;
					INSERT INTO temp_zmiany_kntr2 
					SET guid=_Kontrguid,AnkietaDefPoGUID=_AnkietaDefPoGUID,data=date(_DroidTime),idn=_Idn,
					kategoria=_MultiBrand_Old,kategoria_new=_MultiBrand,
					inwentaryzacja=_Inwentaryzacja,inwentaryzacja_opis=_Inwentaryzacja_opis
					ON DUPLICATE KEY UPDATE 
					  kategoria=_MultiBrand_Old,kategoria_new=_MultiBrand,
					  inwentaryzacja=_Inwentaryzacja,inwentaryzacja_opis=_Inwentaryzacja_opis,
					  idn=_Idn
					;
					UPDATE droid_ankiety_dane_po p SET alocaltime=alocaltime,status=1 WHERE idn=_Idn;
				END IF;
			END LOOP;
			CLOSE c_proc;
		END;
		IF _p3 like '%VERBOSE%' THEN
			SELECT k.symbol,t.* FROM temp_zmiany_kntr2 t,kontrah k WHERE k.guid=t.guid;
		END IF;

		INSERT INTO temp_zmiany_kntr3
		SELECT guid,kategoria,concat(group_concat(distinct nullif(kategoria_new,'') separator ';'),';') as kategoria_new,
			group_concat(distinct nullif(inwentaryzacja,'')) as inwentaryzacja,
			group_concat(distinct nullif(inwentaryzacja_opis,'')) as inwentaryzacja_opis,
			max(data) as data 
		FROM temp_zmiany_kntr2 
		GROUP BY guid;
		IF _p3 LIKE '%VERBOSE%' THEN
			SELECT k.symbol,t.* FROM temp_zmiany_kntr3 t,kontrah k WHERE k.guid=t.guid;
		END IF;
		IF _p3 NOT LIKE '%BEZMB%' THEN
		  UPDATE kontrah k, temp_zmiany_kntr3 t SET k.kategoria=ifnull(t.kategoria_new,'') WHERE k.guid=t.guid;
		END IF;
-- data_inwent,opis_niezgodn - jeśli ok, to wpisujemy "OK"
		IF _p3 NOT LIKE '%BEZINW%' THEN
		  UPDATE kontrah k, temp_zmiany_kntr3 t SET k.data_inwent=t.data,k.opis_niezgodn='OK' WHERE k.guid=t.guid and inwentaryzacja='WZ';
		  UPDATE kontrah k, temp_zmiany_kntr3 t SET k.data_inwent=t.data,k.opis_niezgodn=ifnull(t.inwentaryzacja_opis,'brak info o charakterze niezgodnosci') WHERE k.guid=t.guid and inwentaryzacja='WN';
		END IF;
-- zamykam ankiete rowniez dla zignorowanych pozycji - status -2
		UPDATE droid_ankiety_dane_po p SET alocaltime=alocaltime,status=-2 WHERE guidng=_GUID AND status=0;

	END IF;

	IF _MODE = 'ALL' THEN
		SET @ht_mhdroid_result = 0;
		CALL ht_mhdroid_kontrah('INIT','','','','');
		BEGIN
			DECLARE done BOOL DEFAULT FALSE;
			DECLARE _GUID VARCHAR(38) DEFAULT '';

			DECLARE c_proc CURSOR FOR
			SELECT DISTINCT guidng FROM droid_ankiety_dane_po WHERE AnkietaDefPoGUID in ('CDG_MHv1410','CDG_MHv1395','CDG_MHv11175','CDG_MHv11180','CDG_MHv1520') and status=0 and ValueDescr<>'' ORDER BY alocaltime;
-- UPDATE droid_ankiety_dane_po set status=0 WHERE p.AnkietaDefPoGUID in ('CDG_MHv190','CDG_MHv12200','CDG_MHv11175','CDG_MHv11180','CDG_MHv12210','CDG_MHv1425','CDG_MHv1395','CDG_MHv1472','CDG_MHv1410','CDG_MHv1300','CDG_MHv1310','CDG_MHv1320');
			DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
			
			OPEN c_proc;
				loop2: LOOP
				SET done = FALSE;
				FETCH c_proc INTO _GUID;
				IF done THEN
					LEAVE loop2;
				ELSE
					IF _p3 like '%DEBUG%' THEN
						SELECT _GUID as ankietaGUID;
					END IF;
					CALL ht_mhdroid_kontrah('AnkietaGUID',_GUID,_p1,_p2,_p3);
				END IF;
			END LOOP;
			CLOSE c_proc;
		END;
		IF _p3 not like '%DEBUG%' THEN
			CALL ht_mhdroid_kontrah('DONE','','','','');
		END IF;


	END IF;

	IF _MODE = 'INIT' THEN
		SET @ht_mhdroid_result = 0;
		DROP TEMPORARY TABLE IF EXISTS temp_zmiany_kntr3;
		CREATE TEMPORARY TABLE temp_zmiany_kntr3 (
			guid VARCHAR(38) DEFAULT '',
			kategoria VARCHAR(200) DEFAULT '',
			kategoria_new VARCHAR(200) DEFAULT '',
			inwentaryzacja varchar(200) DEFAULT NULL,
			inwentaryzacja_opis varchar(200) DEFAULT NULL,
			data DATE DEFAULT NULL,
			INDEX (GUID)
		);
		DROP TEMPORARY TABLE IF EXISTS temp_zmiany_kntr2;
		CREATE TEMPORARY TABLE temp_zmiany_kntr2 (
			guid VARCHAR(38) DEFAULT '',
			AnkietaDefPoGUID varchar(38) NOT NULL DEFAULT '',
			kategoria VARCHAR(200) DEFAULT '',
			kategoria_new VARCHAR(200) DEFAULT '',
			inwentaryzacja varchar(200) DEFAULT NULL,
			inwentaryzacja_opis varchar(200) DEFAULT NULL,
			idn int(11) NOT NULL DEFAULT '0',
			data DATE DEFAULT NULL,
			INDEX (GUID),
			UNIQUE INDEX (guid,AnkietaDefPoGUID,data)
		);
		DROP TEMPORARY TABLE IF EXISTS temp_zmiany_kntr;
		CREATE TEMPORARY TABLE temp_zmiany_kntr (
			idn int(11) NOT NULL DEFAULT '0',
			login varchar(38) NOT NULL DEFAULT '',
			AnkietaDefPoGUID varchar(38) NOT NULL DEFAULT '',
			status int(11) NOT NULL DEFAULT '0',
			kontrGUID varchar(38) NOT NULL DEFAULT '',
			brakmultibrand varchar(200) DEFAULT NULL,
			multibrand varchar(200) DEFAULT NULL,
			inwentaryzacja varchar(200) DEFAULT NULL,
			inwentaryzacja_opis varchar(200) DEFAULT NULL,
			droidtime datetime DEFAULT NULL,
			INDEX kontrguid(kontrguid)
			) -- ENGINE=MyISAM DEFAULT CHARSET=cp1250
		;
	END IF;

	IF _MODE = 'DONE' THEN
		SET @ht_mhdroid_result = 0;
		DROP TEMPORARY TABLE IF EXISTS temp_zmiany_kntr3;
		DROP TEMPORARY TABLE IF EXISTS temp_zmiany_kntr2;
		DROP TEMPORARY TABLE IF EXISTS temp_zmiany_kntr;
	END IF;

	IF @ht_mhdroid_result = -1 THEN
		SELECT "MODE NIE ZDEFINIOWANY" AS STATUS,_MODE AS `mode`;
	END IF;

END $$

DELIMITER ;

