/*****************************************************
REWIZJE
******************************************************
$Id: export_to_bank.sql,v 1.3 2017/11/22 14:12:05 max Exp $

*/



DELIMITER $$
/*
    CALL ht_ng_tools('OPNG.after.apply', @GUID, '', '', '', '', '');
    CALL ht_ng_tools('ZMNG.after.apply', @GUID, '', '', '', '', '');
*/

DROP PROCEDURE IF EXISTS export_to_bank$$
CREATE DEFINER=`root`@`localhost` PROCEDURE export_to_bank(
	IN _MODE VARCHAR(150),
	IN _GUID VARCHAR(38),	
	IN _Param_A VARCHAR(50),
	IN _BankID  VARCHAR(50)
)
SPROC: BEGIN
  DECLARE _KontrGUID VARCHAR(38);
  DECLARE _done BOOL DEFAULT FALSE;	
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done = TRUE;			
  
  IF _MODE = 'create.tab' THEN
  
	DROP TEMPORARY TABLE IF EXISTS t_base_rec;
	
	-- analogia do D_Export.T_INTExportRec:		 	
	CREATE TEMPORARY TABLE t_base_rec(
	  GUID 	       			VARCHAR(38) NOT NULL DEFAULT '',	-- cdsRozrachunkiGUID.asString;	  
	  NrDokFin 				VARCHAR(30) NOT NULL DEFAULT '',    -- opng.NrDokFin
	  datawaluty  			DATE,								-- opng.datawaluty
	  --
	  NaszeKonto			VARCHAR(40) NOT NULL DEFAULT '',
	  SWIFT					VARCHAR(11) NOT NULL DEFAULT '',
	  -- dane kontrahenta
      -- KontrGUID    			VARCHAR(38) NOT NULL DEFAULT '',   
	  NIP					VARCHAR(20) NOT NULL DEFAULT '',   -- NIP kontrahenta bez formatowania (podzialu)
	  KontrahNIP			VARCHAR(20) NOT NULL DEFAULT '',   -- NIP kontrahenta z formatowaniem po tym grupowanie	
	  podzialnip   			INTEGER,
	  kontrSymbol			VARCHAR(100) NOT NULL DEFAULT '',
	  kontrahnazwa			VARCHAR(100) NOT NULL DEFAULT '',
	  KontrahZipcode 		VARCHAR(10) NOT NULL DEFAULT '',
      kontrahNazwaMiasta	VARCHAR(60) NOT NULL DEFAULT '',
      kontrahulica			VARCHAR(100) NOT NULL DEFAULT '',	  	  
	  KontrahBankNazwa      VARCHAR(100) NOT NULL DEFAULT '',	
	  DefNrRachunku 		VARCHAR(40) NOT NULL DEFAULT '',	  
	  KontrahBankKonto      VARCHAR(34) NOT NULL DEFAULT '',	-- po tym grupowanie	
      KontrahNrKonta 		VARCHAR(40) NOT NULL DEFAULT '',
	  SWIFTklienta 			VARCHAR(11) NOT NULL DEFAULT '',
	  ISOKraju				VARCHAR(2) NOT NULL DEFAULT '',		-- ISO kraju kontrahenta
	  --
	  ISOKrajBanku			VARCHAR(2) NOT NULL DEFAULT '',	  
	  --      	  
	  WalutaStr				VARCHAR(3) NOT NULL DEFAULT '',	  
	  -- OplatyKosztow			VARCHAR(2) NOT NULL DEFAULT '',	  -- Instrukcje dot. opłat = IReg.'BANK_OplatyBankowe' 
      --
	  DomyslnaWartosc		DECIMAL(16,2) NOT NULL DEFAULT 0.00,
      CPozostalo 			DECIMAL(16,2) NOT NULL DEFAULT 0.00,
      CRozliczone 			DECIMAL(16,2) NOT NULL DEFAULT 0.00
	);
  
  END IF;
  
  IF _MODE = 'iPKO.export.data' THEN
	CALL _IReg_Get('Przelewy', 'BANK_OplatyBankowe', 0, 'STRING', @InstrOplaty, 'SHA');  -- BANK_OplatyBankowe
	
	update t_base_rec t, opng o
	set t.datawaluty = o.datawaluty  
	where t.guid = o.guid;

	DROP TEMPORARY TABLE IF EXISTS t_export;
	
	IF _Param_A = 'GRP' THEN
	
		CREATE TEMPORARY TABLE t_export AS
		SELECT 
		datawaluty AS DataWaluty, SUM(cpozostalo) AS Kwota, WalutaStr AS Waluta, NaszeKonto AS NrRachZlecd,  
		SWIFTklienta AS KodBankuBenef, ISOKrajBanku AS KrajBankuBenef, KontrahBankKonto AS NrRachBenef,
		LEFT(CONCAT(kontrahnazwa, ' ', kontrahzipcode, ' ', kontrahnazwamiasta, ' ', kontrahulica), 105) AS DaneBenef,
		' ' AS RefZlecd, ISOKraju AS KrajBenef, NaszeKonto AS RachOplat, -- gdy oplaty = "OUR"
		GROUP_CONCAT(nrdokfin) AS details
		FROM t_base_rec 
		GROUP BY nip, KontrahBankKonto; 
	
	ELSE
	
		CREATE TEMPORARY TABLE t_export AS
		SELECT 
		datawaluty AS DataWaluty, cpozostalo AS Kwota, WalutaStr AS Waluta, NaszeKonto AS NrRachZlecd,  
		SWIFTklienta AS KodBankuBenef, ISOKrajBanku AS KrajBankuBenef, KontrahBankKonto AS NrRachBenef,
		LEFT(CONCAT(kontrahnazwa, ' ', kontrahzipcode, ' ', kontrahnazwamiasta, ' ', kontrahulica), 105) AS DaneBenef,
		' ' AS RefZlecd, ISOKraju AS KrajBenef, NaszeKonto AS RachOplat, -- gdy oplaty = "OUR"
		nrdokfin AS details
		FROM t_base_rec ;
	
	END IF;	
	
	-- generujemy kolejne linie pliku wynikowego:
	BEGIN		
	/*	DECLARE _DataWaluty DATE;
		DECLARE _Kwota double(21,2);
		DECLARE _Waluta varchar(3);
		DECLARE _NrRachZlecd varchar(40);
		DECLARE _KodBankuBenef varchar(11);
		DECLARE _KrajBankuBenef varchar(2);
		DECLARE _NrRachBenef varchar(34);
		DECLARE _DaneBenef varchar(105);
		DECLARE _KrajBenef varchar(2);
		DECLARE _RachOplat varchar(40);
		DECLARE _details varchar(140);  */
		DECLARE _export_row TEXT;
		
		DECLARE _c_export CURSOR FOR			
		SELECT  
			/* DataWaluty, Kwota, Waluta, NrRachZlecd,  	
			KodBankuBenef, KrajBankuBenef, NrRachBenef,	DaneBenef, KrajBenef, 
			RachOplat, LEFT(details, 140) AS details */
			CONCAT_WS(';', DataWaluty, Kwota, Waluta, NrRachZlecd,  	
			KodBankuBenef, KrajBankuBenef, NrRachBenef,	DaneBenef, RefZlecd, KrajBenef, 
			RachOplat, @InstrOplaty, LEFT(details, 140)) AS export_row
		FROM 
			t_export
		;
				
		SET @export.content = '';		
		OPEN _c_export;
		loop_export: LOOP -- petla loop po zalacznikach
			FETCH 
				_c_export 
			INTO 
				/*_DataWaluty, _Kwota, _Waluta, _NrRachZlecd,  	
				_KodBankuBenef, _KrajBankuBenef, _NrRachBenef, _DaneBenef, _KrajBenef, 
				_RachOplat, _details;*/
				_export_row;
				
			IF _done THEN
				LEAVE loop_export;
			ELSE
				-- SET @tmp.pos = _details;
				SET _export_row = CONCAT(_export_row, ';');
				SET @export.content = CONCAT_WS(IF(@export.content='', '', '\n'), @export.content, _export_row);
			END IF;
			
		END LOOP loop_export; 
		CLOSE _c_export;	
				
	END;
	
	IF _GUID != 'DEBUG' THEN
	
		SET @tmp.pos = 'update opng o, t_base_rec t set o.status_przelewu=2 where t.guid = o.guid';
		UPDATE 	opng o, t_base_rec t SET o.wyslano_przelew = 1, o.status_przelewu = 2 	WHERE t.guid = o.guid;
		
		CALL _IReg_Get('OPNGINFOPATH', 'Przenos_przelewy_do_banku_docelowego', 0, 'BOOLEAN', @pp, 'TRUE');
		IF @pp = 'TRUE' THEN
			UPDATE 	opng o, t_base_rec t 
			SET nrkasy = if(rodzaj=37 and zaksiegowana=0 and datawystaw>=date_sub(CURRENT_DATE, interval 2 day), _varINT(_BankID), nrkasy) 	
			WHERE t.guid = o.guid;
		-- else   aSQL := Format('update '+UpdateTable+' set '+UpdateField+'='''+UpdateFieldValue+''',rozrachunek=%d where GUID=''%s'' ',[FGUIDySL.strings[i], aRozrach]);
		-- 	
		END IF;
		
		-- wpis do loga
		INSERT INTO `log` (useridn, ip, miejsce, czynnosc, GUIDlink, info)   -- IdRozrachunki = 4000; CLOG_M_Insert         = 2;
		SELECT @session.CurrLogin, @session.CurrMyIPOnly, 4000, 2, GUID, 'Wykonanie przelewu do banku'	    -- analogicznie do MotlawaLog.AddToLogS(IdRozrachunki, CLOG_M_Insert, FGUIDySL.strings[i] ,'Wykonanie przelewu do banku');
		FROM t_base_rec;
		
	/* na koniec: 
	   update opng o, t_base_rec t set o.status_przelewu=2 where t.guid = o.guid;
	   
	    if RegistryInt.GetRegIntValueBoolean('Przenos_przelewy_do_banku_docelowego', true, 'OPNGINFOPATH') then  <- wlaczony dla Hydromegi
              // aSQL: set wyslano_przelew = 1,
              aSQL := Format('update '+UpdateTable+' set '+UpdateField+'='''+UpdateFieldValue+''',nrkasy=if(rodzaj=37 and zaksiegowana=0 and datawystaw>=date_sub(CURRENT_DATE, interval 2 day),%d,nrkasy) where GUID=''%s'' ',[aIdnBanku,FGUIDySL.strings[i]])
            else
              aSQL := Format('update '+UpdateTable+' set '+UpdateField+'='''+UpdateFieldValue+''',rozrachunek=%d where GUID=''%s'' ',[FGUIDySL.strings[i], aRozrach]);
            ConnectSupport.execSQL(aSQL);
            MotlawaLog.AddToLogS(IdRozrachunki, CLOG_M_Insert, FGUIDySL.strings[i] ,'Wykonanie przelewu do banku');
	

	*/	
	END IF;
		
	SELECT @export.content AS content;
  
  END IF;
  
  
  /* 
  IF _MODE = 'ZMNG.after.apply' THEN
    SELECT kontrguid FROM zmng WHERE guid=_GUID INTO _KontrGUID;
    CALL erp_kontrah_tools('UPDATE_LAST_CONTACT', _KontrGUID, '', '', '', '');
    SET @result=0;
  END IF;  */
  
END $$

DELIMITER ;
