DELIMITER $$


DROP PROCEDURE IF EXISTS `ht_magdroid`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `ht_magdroid`(
	IN _MODE VARCHAR(150),
	IN _SESSION VARCHAR(150),
	IN _USER VARCHAR(38),
	IN _BARCODE VARCHAR(38),
	IN _PARAM_A VARCHAR(38),
	IN _PARAM_B VARCHAR(38),
	IN _PARAM_C VARCHAR(38)
)

BEGIN

/*
[{ 'component':'WebView' , 'componentName':'WebView1' ,'label':'".json_encode($output)."', 'visible':'1', 'action':'', 'value':'', 'update':'0' },                                                                   
{ 'component':'TextView' ,'componentName':'TextView1' , 'label':'Symbol', 'visible':'0', 'action':'', 'value':'', 'update':'0' },                                                                                         
{ 'component':'EditText' ,'componentName':'EditText1' , 'label':'".$e['symbol']."', 'visible':'0', 'action':'', 'value':'', 'update':'1' },                                                                               
{ 'component':'TextView' ,'componentName':'TextView2' , 'label':'Nazwa', 'visible':'0', 'action':'', 'value':'', 'update':'0' },                                                                                          
{ 'component':'EditText' ,'componentName':'EditText2' , 'label':'".$e['nazwa']."', 'visible':'0', 'action':'', 'value':'', 'update':'1' },                                                                                
{ 'component':'TextView' ,'componentName':'TextView3' , 'label':'Cena', 'visible':'0', 'action':'', 'value':'', 'update':'0' },                                                                                           
{ 'component':'EditText' ,'componentName':'EditText3' , 'label':'0.00', 'visible':'0', 'action':'', 'value':'', 'update':'1' },                                                                                           
{ 'component':'Button' , 'componentName':'Button1' , 'label':'Zapisz', 'visible':'0', 'action':'POST', 'value':'post.php', 'update':'0' }]

call ht_magdroid('INFO','test','jmam',97737,'','','');
*/

  SET @ht_magdroid_result := -1;
	DROP TEMPORARY TABLE IF EXISTS t_view;
	CREATE TEMPORARY TABLE t_view (
		`component` varchar(100),
		`componentName` varchar(100),
		`label` varchar(1024),
		`visible` int not null,
		`action` varchar(100),
		`value` varchar(100),
		`update` int not null
	);

 
	IF _MODE IN ('INFO','DODAJ') THEN

		SET @opis:="";
		SELECT guid FROM oppo WHERE idn=_BARCODE INTO @guid;

		SET @wgdostaw:=0;
		IF NOT isnull(@guid) THEN
			IF @wgdostaw<>0 THEN
				SET @wydano:=IFNULL(greatest((SELECT sum(ilosc) FROM oppo WHERE guid_dostawy=@guid),0),0);
				UPDATE oppo SET wydano=least(@wydano,ilosc) WHERE idn=_BARCODE;

				SELECT concat("Kod:",p.idn,"<BR><B>",p.towrsymb,"</B><BR>",p.towrnazwa,"<BR><B>Ilosc pozost. z dostawy:",round(greatest(p.ilosc-p.wydano,0),2),
				"<BR><B>Ilosc tow. na magazynie:",(select round(tp.ilosc,2) from TowrPo tp where tp.magazyn=p.domagazynu and tp.idntowr=p.idntowr ),
				"</B><BR>Dostawa:",n.datawystaw,"<BR>",n.kontrnazwa), greatest(p.ilosc-p.wydano,0) as pozost
				FROM oppo p, opng n WHERE p.idnoper=n.guid AND p.idn=_BARCODE INTO @opis,@pozost;
			ELSE
				SELECT concat("Kod:",p.idn,"<BR><B>",p.towrsymb,"</B><BR>",p.towrnazwa,
				"<BR><B>Ilosc na magazynie:",ifnull((select round(tp.ilosc,2) from TowrPo tp where tp.magazyn=p.domagazynu and tp.idntowr=p.idntowr ),0),
				"</B><BR>Dostawa:",n.datawystaw,"<BR>",n.kontrnazwa), 
				ifnull((select round(tp.ilosc,2) from TowrPo tp where tp.magazyn=p.domagazynu and tp.idntowr=p.idntowr ),0) as pozost
				FROM oppo p, opng n WHERE p.idnoper=n.guid AND p.idn=_BARCODE INTO @opis,@pozost;
			END IF;
		END IF;

		IF @opis="" THEN
			SET @opis=concat("<H2> Nie znaleziono kodu:",_BARCODE,"</H2>");
		END IF;

		INSERT INTO t_view 
		SELECT "WebView","Info1",@opis,1,"","",0;
		IF _MODE IN ('DODAJ') and @pozost>0 THEN
			INSERT INTO t_view VALUES 
			("TextView" ,"TVilosc" , "Ilosc",1,"", "value", 0),
			("EditText" ,"KodKreskowy" , _BARCODE,8,"", "value", 1),
			("EditText" ,"EDilosc" ,@pozost ,1,"", "value", 1),
			("Button","ButtonOK","OK",1,"POST","md_dodaj_post.php",0);
		ELSE
			INSERT INTO t_view VALUES -- zamiast POST ma byl BACK
			("Button","ButtonOK","OK",1,"BACK","",0);
		END IF;	




		SELECT * from t_view;
		SET @ht_gasdroid_result := 0;
	END IF;	

	IF _MODE ='SPRAWDZ' THEN
		INSERT INTO t_view 
		SELECT "WebView","Info1",
		concat("<B>Dokument:</B><BR>",IFNULL(
				(select group_concat(concat(d.barcode," : <B>",o.towrsymb,"</B> : ",d.p1) SEPARATOR "<BR>") from droid_scan d,oppo o where d.barcode=o.idn and d.idnuser=_USER and d.GUIDng=''),
				"Brak zeskanowanych pozycji")
		),1,"","",0;

		INSERT INTO t_view VALUES ("Button","ButtonOK","OK",1,"BACK","",0);

		SELECT * from t_view;
		SET @ht_gasdroid_result := 0;
	END IF;	


	IF _MODE = 'INFO' THEN
		SET @ht_gasdroid_result := 0;
	END IF;	
	
	
	IF _MODE = 'POST'  THEN
		DROP TEMPORARY TABLE IF EXISTS t_opng;
		DROP TEMPORARY TABLE IF EXISTS t_oppo;

		CREATE TEMPORARY TABLE t_opng like opng;
		ALTER TABLE t_opng DROP COLUMN idn;
		ALTER TABLE t_opng add column idn int not null default 0 first;

		CREATE TEMPORARY TABLE t_oppo like oppo;
		ALTER TABLE t_oppo DROP COLUMN idn;
		ALTER TABLE t_oppo add column idn int not null default 0 first;

		-- CREATE TEMPORARY TABLE t_scan;
		SET @GUID:=replace(uuid(),'-','');

		INSERT IGNORE INTO t_oppo 
		SELECT o.* FROM oppo o,droid_scan d WHERE d.barcode=o.idn and d.GUIDng='' and d.idnuser=_USER;
-- przechowanie zlecenia
		UPDATE t_oppo o, opng n SET o.guidzm=n.zlecenieguid WHERE o.idnoper=n.guid;

		UPDATE t_oppo o, droid_scan d SET 
			o.idn=0,o.GUID_dostawy=o.guid,o.guid=concat('MD',d.idn),o.rodzaj=11,o.kurs=1,
			o.ilosc=least(d.p1,if(o.ilosc>o.wydano,o.ilosc-o.wydano,0)),o.iloscdo=d.p1,o.idnwz='',
			
			o.idnoper=concat(@GUID,o.domagazynu),o.idnoperfin=concat(@GUID,o.domagazynu),
			-- czy nie robic tego na koncu?
			d.GUIDng=concat(@GUID,o.domagazynu),d.processed=now(),
			o.datawystaw=CURRENT_DATE,o.datasprzed=CURRENT_DATE,o.datawplywu=CURRENT_DATE,
			o.zmagazynu=o.domagazynu,o.tymczasowa=1,o.info4=d.idnuser
		WHERE d.barcode=o.idn and d.GUIDng='' and d.idnuser=_USER;
--		czyszczenie blednych kodow
		UPDATE droid_scan d SET GUIDng='BRAK_KODU',processed=now() where GUIDng='' and idnuser=_USER;

		INSERT INTO t_opng (rodzaj,zmagazynu,domagazynu,guid,nrdokmag,datawystaw,datasprzed,datawplywu,
								tymczasowa,idnuser,waluta,waluta_rozliczen,zlecenieguid,status_obieg)
		SELECT DISTINCT
			rodzaj,
			zmagazynu,
			domagazynu,
			idnoper as guid,
			'' as nrdokmag,datawystaw,datasprzed,datawplywu,
			tymczasowa,
			_USER,
			0 as waluta,0 as waluta_rozliczen,
			guidzm as zlecenieguid,0 as status_obieg

		FROM t_oppo;

		UPDATE t_opng o, operdoc d SET
			o.rv_id=d.rv_id,o.grupaoper=d.grupa,o.domagazynu=0,
			o.kurs=1,o.kurs_rozliczen=1,o.ugrpguid='ALL'
		WHERE d.rodzaj=o.rodzaj;

		SET @lp:=0;
		UPDATE t_oppo o SET	
			o.lp=@lp:=@lp+1,o.guidzm='',o.domagazynu=0,o.wydano=0,
			o.wartnet=o.cenanet*o.ilosc,o.wartnetwal=o.cenanet*o.ilosc,o.wartvat=0,o.wartbrt=0
		ORDER BY o.lp;

		INSERT INTO opng select * from t_opng;DROP TEMPORARY TABLE IF EXISTS t_opng;
		INSERT INTO oppo select * from t_oppo WHERE ilosc>0;
		
		DROP TEMPORARY TABLE IF EXISTS t_dost;
		CREATE TEMPORARY TABLE t_dost 
		SELECT o.GUID_dostawy,sum(o.ilosc) as ilosc 
		FROM oppo o, t_oppo t WHERE t.GUID_dostawy=o.GUID_dostawy 
		GROUP BY GUID_dostawy;
		UPDATE oppo o, t_dost d SET o.wydano=least(d.ilosc,o.ilosc),o.info3=if(d.ilosc>o.ilosc,'B��D WYDANIA',o.info3) WHERE o.guid=d.GUID_dostawy;

		DROP TEMPORARY TABLE IF EXISTS t_dost;
		DROP TEMPORARY TABLE IF EXISTS t_oppo;

		SELECT "OK" AS STATUS,_MODE AS `mode`;
		
		SET @ht_gasdroid_result := 0;
	END IF;	
	
	
	IF @ht_gasdroid_result = -1 THEN
		SELECT "MODE NIE ZDEFINIOWANY" AS STATUS,_MODE AS `mode`;
	END IF;
	DROP TEMPORARY TABLE IF EXISTS t_view;
END$$



DROP PROCEDURE IF EXISTS `ht_magdroid_post`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `ht_magdroid_post`(
	IN _MODE VARCHAR(150),
	IN _SESSION VARCHAR(150),
	IN _USER VARCHAR(38),
	IN _BARCODE VARCHAR(38),
	IN _p1   VARCHAR(250),
	IN _p2   VARCHAR(250),
	IN _p3   VARCHAR(250)
)

BEGIN
	REPLACE `droid_scan` (sessionid,barcode,idnuser,p1,p2,p3)
	VALUES (_SESSION, _BARCODE,_USER,_p1,_p2,_p3);

END$$



DELIMITER ;


/*
CREATE TABLE `droid_scan` (
  `idn` int(11) NOT NULL AUTO_INCREMENT,
  `alocaltime`  timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `sessionid` varchar(38) DEFAULT '',
  `barcode` varchar(150) NOT NULL DEFAULT '',
  `idnuser` varchar(20) NOT NULL DEFAULT '',
  `p1` varchar(150) NOT NULL DEFAULT '',
  `p2` varchar(150) NOT NULL DEFAULT '',
  `p3` varchar(150) NOT NULL DEFAULT '',
  `GUIDng` varchar(38) DEFAULT '',
  `processed` timestamp default '0000-00-00 00:00:00',
  PRIMARY KEY (`idn`)
);

*/
