DELIMITER $$

DROP PROCEDURE IF EXISTS `fr_ht_zest`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `fr_ht_zest`(
IN _MODE VARCHAR(38),
    IN _GUID VARCHAR(38),
    IN _rok VARCHAR(38),
    IN _miesiac VARCHAR(38),
    IN _NaDzien VARCHAR(10),
    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 '';
    
    SET _WHERE_SQL = IFNULL(_WHERE_SQL, '');
	 SET _GUID = IFNULL(_GUID, '');

/*
Procedura zbierajaca rozne zestawienia wykonywane w programie ht (analogicznie jak w placach i produkcji)

_MODE = 'SERWIS.ROZLICZENIE' - zestawienie zerwisowe dla Wegi, przygotowanie danych, tabeli pomocniczej, pod wydruk
				podawane parametry to tylko zakres dat, tabela jest naliczana dla wszystkich kontrahentow od zera
				istotne jest wylapanie instancji (ciuchow) serwisowanych w danym okresie czasu, czyli po wydaniu i bez zwrotow i depozytow

 CALL fr_ht_zest('SERWIS.ROZLICZENIE','',2013,5,'','','','','','','','');
 CALL fr_ht_zest('SERWIS.ROZLICZENIE','{250A3617-7AF8-462F-A3A9-7367BE058F9D}',2013,5,'','','','','','','','');
 CALL fr_ht_zest('SERWIS.ROZLICZENIE','5890006529',2013,6,'','','','','','','','');  - kasacja

 CALL fr_ht_zest('SERWIS.ROZLICZENIE','{ED51A4A7-A66B-499A-BCD0-4CFD4E68627C}',2013,8,'','','','','','','','');
 CALL fr_ht_zest('SERWIS.ROZLICZENIE','{8EAE6B9B-D4DE-4C0E-8622-FA2B3BE36312}',2013,10,'','','','','','','','');

*/

    IF (_MODE = 'SERWIS.ROZLICZENIE') THEN
	
		-- kontrahenci, dla ktorych naliczono juz fakture
		CREATE TEMPORARY TABLE t_rozliczone AS 
		SELECT DISTINCT GUID_kontrah AS GUID FROM temp_serwis_rozliczenie WHERE rok = _Rok AND miesiac = _miesiac AND idnoper <> '';

        -- tabela z guidami kontrahentow, dla ktorych robione jest zestawienie 
		-- uzupelniona danymi wczesniejszych kontrahentow (jezeli byly przejscia)
		DROP TABLE IF EXISTS tt_kontrah;
		CREATE TEMPORARY TABLE tt_kontrah (GUID VARCHAR(38) NOT NULL DEFAULT '', KEY `GUID` (`GUID`));

		INSERT INTO tt_kontrah
		SELECT GUID 
		FROM kontrah
		WHERE 
		  IF(_GUID='', TRUE, GUID = _GUID)
		  AND deleted = 0 
		  AND serwis_rozlicz = 1 and GUID NOT IN (SELECT GUID FROM t_rozliczone) ;

 		INSERT INTO tt_kontrah
		SELECT kp.guid_kontr_pop 
		FROM kontrah_przejscie kp 
		INNER JOIN kontrah k ON k.GUID=kp.guid_kontr_nast
		WHERE k.GUID=_GUID AND k.deleted = 0 AND k.serwis_rozlicz = 1 and GUID NOT IN (SELECT GUID FROM t_rozliczone) ;

/*		
		SET _OdDnia = CONCAT(_rok,'-',_miesiac,'-15');
		SET _DoDnia = DATE_ADD(LAST_DAY(_OdDnia), INTERVAL 14 DAY);
*/
		SET _DoDnia = CONCAT(_rok,'-',_miesiac,'-15');
		SET _OdDnia = DATE_ADD(DATE_ADD(CONCAT(_rok,'-',_miesiac,'-01'), INTERVAL -1 MONTH), INTERVAL 15 DAY);  

		set @a1 = _DoDnia;
		set @a2 = _OdDnia;

# przygotowanie zawezonej tabeli opng, dodatkowo z przekodowanymi kontrahentami
		DROP TABLE IF EXISTS tt_opng;
		CREATE TEMPORARY TABLE tt_opng LIKE opng;
		INSERT INTO tt_opng
		SELECT n.*
		FROM opng n		
		INNER JOIN tt_kontrah tk ON tk.GUID=n.kontrGUID
		WHERE n.datawystaw<=_DoDnia 
		AND ((n.rodzaj=47 AND n.rodzaj_typ IN (0,1,2))  OR (n.rodzaj=9 AND n.rodzaj_typ=3)  
		OR (n.rodzaj=46 AND n.rodzaj_typ=3) OR (n.rodzaj=9 AND n.rodzaj_typ=1 AND n.domagazynu=15) OR (n.rodzaj=46 AND n.rodzaj_typ=1 AND n.domagazynu=15))
		;

# kasacje wszystkie, bo bez kontrahentow
		INSERT INTO tt_opng
		SELECT n.*
		FROM opng n		
		WHERE n.datawystaw<=_DoDnia 
		AND (n.rodzaj=11 AND n.rodzaj_typ=1) 
		;



		UPDATE tt_opng tt, kontrah_przejscie kp
		SET tt.kontrGUID = kp.guid_kontr_nast
		WHERE tt.kontrGUID = kp.guid_kontr_pop;


# zawezona tabela oppo_instancje
		DROP TABLE IF EXISTS tt_oppo_instancje;
		CREATE TEMPORARY TABLE tt_oppo_instancje LIKE oppo_instancje;
		INSERT INTO tt_oppo_instancje
		SELECT oi.*
		FROM oppo_instancje oi
		INNER JOIN oppo o ON o.guid=oi.guid_oppo
		INNER JOIN tt_opng n ON n.GUID=o.idnoper
		INNER JOIN tt_kontrah tk ON tk.GUID=n.kontrGUID
		WHERE n.datawystaw<=_DoDnia AND oi.deleted=0 AND oi.guid_instancji<>'' AND oi.ilosc <> 0
		AND ((n.rodzaj=47 AND n.rodzaj_typ IN (0,1,2)) OR (n.rodzaj=11 AND n.rodzaj_typ=1) OR (n.rodzaj=9 AND n.rodzaj_typ=3)  
		OR (n.rodzaj=46 AND n.rodzaj_typ=3) OR (n.rodzaj=9 AND n.rodzaj_typ=1 AND n.domagazynu=15) OR (n.rodzaj=46 AND n.rodzaj_typ=1 AND n.domagazynu=15))
		;

		DROP TABLE IF EXISTS tt_instancje;
		CREATE TEMPORARY TABLE tt_instancje
		SELECT oi.guid_instancji AS GUID_instancji, 
		MAX(IF(n.rodzaj=47 AND n.rodzaj_typ IN (1,2),
		   CONCAT(LPAD(n.datasprzed,10,' '),':',LPAD(n.lpkpr+100,3,'0'),':',LPAD(n.idn,10,' ')),
		  '1901-01-01:00:0000000000')) AS max_data_lpkpr_idn, 
		REPEAT(' ',10) AS data_wydania, _varint(0) AS lpkpr, _varint(0) AS idn,
		MAX(IF((n.rodzaj=11 AND n.rodzaj_typ=1) OR (n.rodzaj=9 AND n.rodzaj_typ=3 ),n.datasprzed,'')) AS data_kasacji,
		REPEAT(' ',38) AS GUID_kontrah,
		REPEAT(' ',38) AS GUID_oppo_instancji,
		REPEAT(' ',38) AS osobaguid,
		'2100-01-01' AS data_zwrotu
		FROM tt_oppo_instancje oi
		INNER JOIN oppo o ON o.guid=oi.guid_oppo
		INNER JOIN tt_opng n ON n.GUID=o.idnoper
		INNER JOIN tt_kontrah tk ON tk.GUID=n.kontrGUID
		WHERE n.datawystaw<=_DoDnia AND oi.deleted=0 AND oi.guid_instancji<>''  AND oi.ilosc <> 0
		AND ((n.rodzaj=47 AND n.rodzaj_typ IN (1,2)) OR (n.rodzaj=11 AND n.rodzaj_typ=1) OR (n.rodzaj=9 AND n.rodzaj_typ=3))
		GROUP BY guid_instancji
		;

		ALTER TABLE tt_instancje ADD INDEX GUID_instancji(GUID_instancji),
		ADD INDEX GUID_kontrah(GUID_kontrah),
		ADD INDEX data_wydania(data_wydania),
		ADD INDEX max_data_lpkpr_idn(max_data_lpkpr_idn);

		UPDATE tt_instancje 
		SET data_wydania = MID(max_data_lpkpr_idn, 1,10), lpkpr = MID(max_data_lpkpr_idn, 12,3)-100, idn = MID(max_data_lpkpr_idn, 17,10);


# kasacje bez kontrahenta (a takich jest wiekszosc)

		DROP TABLE IF EXISTS tt_instancje_kas;
		CREATE TEMPORARY TABLE tt_instancje_kas
		SELECT oi.guid_instancji AS GUID_instancji, 	

		MAX(IF((n.rodzaj=11 AND n.rodzaj_typ=1) OR (n.rodzaj=9 AND n.rodzaj_typ=3 ),n.datasprzed,'')) AS data_kasacji

		FROM oppo_instancje oi
		INNER JOIN oppo o ON o.guid=oi.guid_oppo
		INNER JOIN tt_opng n ON n.GUID=o.idnoper
		WHERE n.datawystaw<=_DoDnia AND oi.deleted=0 AND oi.guid_instancji<>''  AND oi.ilosc <> 0
		AND ((n.rodzaj=11 AND n.rodzaj_typ=1) OR (n.rodzaj=9 AND n.rodzaj_typ=3))
		GROUP BY guid_instancji
		;

		ALTER TABLE tt_instancje_kas ADD INDEX GUID_instancji(GUID_instancji);

		UPDATE tt_instancje tt, tt_instancje_kas tt2
		SET tt.data_kasacji = tt2.data_kasacji
		WHERE tt.guid_instancji = tt2.guid_instancji
		;

		DELETE FROM tt_instancje WHERE (data_kasacji < _OdDnia AND data_kasacji<>'') OR data_wydania='1901-01-01';

# wydania
	 UPDATE tt_instancje t, tt_oppo_instancje oi, oppo o, tt_opng n
	 SET t.GUID_kontrah = n.kontrGUID,
		  t.GUID_oppo_instancji = oi.guid
	 WHERE n.datawystaw=t.data_wydania AND t.GUID_instancji=oi.guid_instancji
	 AND oi.deleted=0  AND oi.ilosc <> 0 AND n.rodzaj=47 AND n.rodzaj_typ IN (1,2)
	 AND n.GUID=o.idnoper AND o.guid=oi.guid_oppo;

#zwroty 
	 DROP TABLE IF EXISTS tt2_instancje;
	 CREATE TEMPORARY TABLE tt2_instancje -- tabela ze zwrotami
	 SELECT oi.guid_instancji AS GUID_instancji, 	 
	 MAX(CONCAT(LPAD(n.datasprzed,10,' '),':',LPAD(n.lpkpr+100,3,'0'),':',LPAD(n.idn,10,' '))) AS max_data_lpkpr_idn, 
	 REPEAT(' ',10) AS data_zwrotu, _varint(0) AS lpkpr, _varint(0) AS idn, 
	 tt.GUID_kontrah AS GUID_kontrah
	 FROM tt_oppo_instancje oi
	 INNER JOIN oppo o ON o.guid=oi.guid_oppo
	 INNER JOIN tt_opng n ON n.GUID=o.idnoper
	 INNER JOIN tt_kontrah tk ON tk.GUID=n.kontrGUID
	 INNER JOIN tt_instancje tt ON tt.guid_instancji=oi.guid_instancji AND tt.GUID_kontrah=n.kontrGUID
	 WHERE n.datawystaw>=tt.data_wydania AND 
	 oi.deleted=0  AND oi.ilosc <> 0 AND oi.guid_instancji<>''
	 AND ((n.rodzaj=46 AND n.rodzaj_typ=3) OR (n.rodzaj=9 AND n.rodzaj_typ=1 AND n.domagazynu=15) OR (n.rodzaj=46 AND n.rodzaj_typ=1 AND n.domagazynu=15))
	 GROUP BY guid_instancji
	 ;

	 ALTER TABLE tt2_instancje 
	 ADD INDEX GUID_instancji(GUID_instancji),
	 ADD INDEX GUID_kontrah(GUID_kontrah),
	 ADD INDEX max_data_lpkpr_idn(max_data_lpkpr_idn);

# wyszukanie ostatniej operacji w zakresie dat, potrzebnej do przypisania wlasciwej osoby, dla danego kontrahenta
	 DROP TABLE IF EXISTS tt3_instancje;
	 CREATE TEMPORARY TABLE tt3_instancje
	 SELECT oi.guid_instancji AS GUID_instancji, 	 
	 MAX(CONCAT(LPAD(n.datasprzed,10,' '),':',LPAD(n.lpkpr+100,3,'0'),':',LPAD(n.idn,10,' '),':',oi.osobaguid)) AS max_data_lpkpr_idn, 
	 -- REPEAT(' ',10) AS data_zwrotu, _varint(0) AS lpkpr, _varint(0) AS idn, 
	 repeat(' ',38) as osobaguid,
	 tt.GUID_kontrah AS GUID_kontrah
	 FROM tt_oppo_instancje oi
	 INNER JOIN oppo o ON o.guid=oi.guid_oppo
	 INNER JOIN tt_opng n ON n.GUID=o.idnoper
	 INNER JOIN tt_kontrah tk ON tk.GUID=n.kontrGUID
	 INNER JOIN tt_instancje tt ON tt.guid_instancji=oi.guid_instancji AND tt.GUID_kontrah=n.kontrGUID
	 WHERE n.datawystaw>=tt.data_wydania AND 
	 oi.deleted=0  AND oi.ilosc <> 0 AND oi.guid_instancji<>''
	 GROUP BY guid_instancji
	 ;

	 ALTER TABLE tt3_instancje 
	 ADD INDEX GUID_instancji(GUID_instancji),
	 ADD INDEX GUID_kontrah(GUID_kontrah),
	 ADD INDEX max_data_lpkpr_idn(max_data_lpkpr_idn);

	 UPDATE tt3_instancje 
	 SET -- data_zwrotu = MID(max_data_lpkpr_idn, 1,10), 
		  -- lpkpr = MID(max_data_lpkpr_idn, 12,2), 
		  -- idn = MID(max_data_lpkpr_idn, 16,10),
		  osobaguid = MID(max_data_lpkpr_idn, 27,38);

/*
# podmiana guidow kontrahentow
	 UPDATE tt_instancje tt, kontrah_przejscie kp
	 SET tt.GUID_kontrah = kp.guid_kontr_nast
	 WHERE tt.GUID_kontrah = kp.guid_kontr_pop;
		
	 UPDATE tt2_instancje tt, kontrah_przejscie kp
	 SET tt.GUID_kontrah = kp.guid_kontr_nast
	 WHERE tt.GUID_kontrah = kp.guid_kontr_pop;

	 UPDATE tt3_instancje tt, kontrah_przejscie kp
	 SET tt.GUID_kontrah = kp.guid_kontr_nast
	 WHERE tt.GUID_kontrah = kp.guid_kontr_pop;
# koniec podmianki
*/

	 UPDATE tt2_instancje 
	 SET data_zwrotu = MID(max_data_lpkpr_idn, 1,10), 
		  lpkpr = MID(max_data_lpkpr_idn, 12,3)-100, 
		  idn = MID(max_data_lpkpr_idn, 17,10);

	 UPDATE tt_instancje t, tt2_instancje t2
	 SET t.data_zwrotu = t2.data_zwrotu
	 WHERE t.GUID_instancji = t2.GUID_instancji AND t.GUID_kontrah = t2.GUID_kontrah

	 -- AND t2.data_zwrotu>t.data_wydania; -- nowy dopisek;
	 -- jeszcze nowszy dopisek, bo jednak sa dokumenty z jednego dnia
	 AND (t2.data_zwrotu>t.data_wydania 
		  OR (t2.data_zwrotu=t.data_wydania AND t2.lpkpr>t.lpkpr) 
		  OR (t2.data_zwrotu=t.data_wydania AND t2.lpkpr=t.lpkpr AND t2.idn>t.idn))  -- sortowanie wg daty, lpkpr, idnu operacji
	 ; 


-- obsluga samej tabeli wynikowej
	 DELETE FROM temp_serwis_rozliczenie 
	 WHERE rok = _rok AND miesiac = _miesiac 
			 AND IF(_GUID='',TRUE,GUID_kontrah = _GUID)
			 AND idnoper='';

	 INSERT INTO temp_serwis_rozliczenie (rok,miesiac,GUID_kontrah,GUID_instancji,GUID_osoby,oddzial,cena,wart_mc)
	 SELECT _rok, _miesiac, tt.GUID_kontrah, tt.GUID_instancji, oi.osobaguid, 
	 '' AS oddzial, IFNULL(of.cena,0) AS cena, IFNULL(of.cena,0)*4.33 AS wart_mc
	 FROM tt_instancje tt
	 INNER JOIN tt3_instancje oi ON oi.GUID_instancji=tt.GUID_instancji and oi.GUID_kontrah = tt.GUID_kontrah
	 INNER JOIN towrng_instancje ti ON ti.guid=tt.guid_instancji
	 LEFT JOIN oferty of ON of.idntowr=ti.idntowr AND tt.GUID_kontrah=of.kontrGUID
	 WHERE tt.data_wydania <= _DoDnia AND tt.data_zwrotu > _DoDnia;
	 

/*
	 DROP TABLE IF EXISTS tt2_instancje;
	 DROP TABLE IF EXISTS tt_instancje;
	 DROP TABLE IF EXISTS tt_instancje_kas;
	 DROP TABLE IF EXISTS tt_kontrah;
	 DROP TABLE IF EXISTS tt_oppo_instancje;
	 DROP TABLE IF EXISTS tt_opng;
*/

    END IF;    --    IF _MODE = 'SERWIS.ROZLICZENIE'


END$$

/*

CREATE TABLE temp_serwis_rozliczenie (
idn INT(11) NOT NULL AUTO_INCREMENT,
alocaltime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
idnoper VARCHAR(38) NOT NULL DEFAULT '',
rok INT(11) NOT NULL DEFAULT 0,
miesiac INT(11) NOT NULL DEFAULT 0,
GUID_kontrah VARCHAR(38) NOT NULL DEFAULT '',
GUID_instancji VARCHAR(38) NOT NULL DEFAULT '',
GUID_osoby VARCHAR(38) NOT NULL DEFAULT '',
oddzial VARCHAR(100) NOT NULL DEFAULT '',
cena DOUBLE(16,4) NOT NULL DEFAULT '0.0000',
wart_mc DOUBLE(16,4) NOT NULL DEFAULT '0.0000',
PRIMARY KEY (idn),
KEY GUID_kontrah (GUID_kontrah),
KEY GUID_instancji (GUID_instancji),
KEY GUID_osoby (GUID_osoby),
KEY rok (rok),
KEY miesiac (miesiac)
) ENGINE=MYISAM AUTO_INCREMENT=0 DEFAULT CHARSET=cp1250;


ALTER TABLE temp_serwis_rozliczenie ADD COLUMN wart_mc DOUBLE(16,4) DEFAULT 0;


SELECT ti.kod_kreskowy, o.nazwisko, o.imiona, t.* 
FROM temp_serwis_rozliczenie t
INNER JOIN osoby o ON o.guid=t.guid_osoby
INNER JOIN towrng_instancje ti ON ti.guid = t.guid_instancji
WHERE rok=2013 AND miesiac=8 AND guid_kontrah = '{ED51A4A7-A66B-499A-BCD0-4CFD4E68627C}'
AND o.nazwisko = 'bartkowski' AND o.imiona = 'witold'
;

SELECT * FROM tt_kontrah;

SELECT n.idn, ot.nazwa, ot.opis, n.kontrguid, n.datawystaw, n.lpkpr, n.rodzaj, n.rodzaj_typ, oi.*
FROM oppo_instancje oi
INNER JOIN oppo o ON oi.guid_oppo=o.guid
INNER JOIN opng n ON n.guid=o.idnoper
INNER JOIN operdoc_typy ot ON ot.rodzaj=n.rodzaj AND ot.typ=n.rodzaj_typ
WHERE guid_instancji='{2353D026-F485-43F6-B759-6B775067BA3D}'
ORDER BY n.datawystaw, n.idn; 

*/

/*
pomocnicze

SELECT * FROM towrng_instancje WHERE kod_kreskowy='1001586190';


SELECT n.idn, ot.nazwa, ot.opis, n.kontrguid, n.datawystaw, n.rodzaj, n.rodzaj_typ, oi.* 
FROM oppo_instancje oi 
INNER JOIN oppo o ON oi.guid_oppo=o.guid
INNER JOIN opng n ON n.guid=o.idnoper
INNER JOIN operdoc_typy ot ON ot.rodzaj=n.rodzaj AND ot.typ=n.rodzaj_typ
WHERE guid_instancji='{30AD5222-A6A0-407E-8BB5-FBB187FC8B33}'
ORDER BY n.datawystaw, n.idn;

*/


#### zestaw sledczy
/*
CALL fr_ht_zest('SERWIS.ROZLICZENIE','{FD9D643E-769A-48B2-BCE7-5F3FE030162F}',2013,10,'','','','','','','','');

SELECT * FROM kontrah WHERE nazwa LIKE 'al piekarnia%';
SELECT * FROM towrng_instancje WHERE kod_kreskowy='1002194990';

SELECT @a1, @a2;

SELECT * FROM tt_kontrah;

SELECT * FROM tt_opng WHERE guid = '{A875239D-55B4-4885-ADC4-46C5B7625095}';

SELECT * FROM tt_instancje WHERE guid_instancji='{0DABDCE3-B1F4-4F15-B23B-9DE68115D637}';
SELECT * FROM tt2_instancje WHERE guid_instancji='{0DABDCE3-B1F4-4F15-B23B-9DE68115D637}';


SELECT n.idn, ot.nazwa, ot.opis, n.kontrguid, n.datawystaw, n.lpkpr, n.rodzaj, n.rodzaj_typ, oi.*
FROM oppo_instancje oi
INNER JOIN oppo o ON oi.guid_oppo=o.guid
INNER JOIN opng n ON n.guid=o.idnoper
INNER JOIN operdoc_typy ot ON ot.rodzaj=n.rodzaj AND ot.typ=n.rodzaj_typ
WHERE guid_instancji='{0DABDCE3-B1F4-4F15-B23B-9DE68115D637}'
ORDER BY n.datawystaw, n.idn; 

SELECT * FROM temp_serwis_rozliczenie
WHERE rok=2013 AND miesiac=10 AND guid_kontrah='{FD9D643E-769A-48B2-BCE7-5F3FE030162F}'
AND guid_instancji='1002194990';



*/




DELIMITER ;

