DELIMITER $$

/*
call fk_salda('INIT',NULL,NULL,'','','');
call fk_salda('REFRESH','2013-01-01','2013-12-31','','','');
call fk_salda('REFRESH_ALL','2013-01-01','2013-12-31','','','');
call fk_salda('REFRESH_WGDAT','2013-01-01','2013-12-31','','','');
call fk_salda('REFRESH_WGDAT_ALL','2013-01-01','2013-12-31','','','');

*/


DROP PROCEDURE IF EXISTS `fk_salda`$$
CREATE DEFINER=`root`@`localhost` 
PROCEDURE `fk_salda`(	IN MODE VARCHAR(38), IN _DATAOD DATE, IN _DATADO DATE, 
			IN _Param_A VARCHAR(38), IN _Param_B VARCHAR(38), IN _Param_C VARCHAR(38))
	CONTAINS SQL
	COMMENT ''
SPROC: BEGIN
	DECLARE _ROK int;
	DECLARE _MAXTIME_S DATETIME;
	DECLARE _MAXTIME_Z DATETIME;
	SELECT idn from lata_obrachunkowe WHERE _DATAOD BETWEEN DataBO AND DataBZ INTO _ROK;
	SET @result='';
	IF MODE='INIT' AND (_Param_C<>'False') THEN
		DROP TABLE IF EXISTS temp_salda_obroty_analityczne;
		CREATE TABLE temp_salda_obroty_analityczne (
		konto varchar(38) not null,
		bown double(16,4) not null default 0,
		boma double(16,4) not null default 0,
		wn double(16,4) not null default 0,
		ma double(16,4) not null default 0,
		rok int not null default 0,
		mc int not null default 0,
		maxtime DATETIME,
		INDEX maxtime(maxtime),
		INDEX konto(konto,maxtime),
		INDEX rmk(rok,mc,konto)
		);
		DROP TABLE IF EXISTS temp_salda_obroty_analityczne_wg_dat;
		CREATE TABLE temp_salda_obroty_analityczne_wg_dat (
		konto varchar(38) not null,
		bown double(16,4) not null default 0,
		boma double(16,4) not null default 0,
		wn double(16,4) not null default 0,
		ma double(16,4) not null default 0,
		rok int not null default 0,
		data_dowodu DATE,
		maxtime DATETIME,
		INDEX maxtime(maxtime),
		INDEX konto(konto,maxtime),
		INDEX rdk(rok,data_dowodu,konto)
		);
	END IF;

	IF MODE='CLEAR' AND (_Param_C<>'False') THEN
		DROP TABLE IF EXISTS temp_salda_obroty_analityczne;
		DROP TABLE IF EXISTS temp_salda_obroty_analityczne_wg_dat;
		DROP TEMPORARY TABLE IF EXISTS temp_salda_obroty_wg_dat;
	END IF;

	IF (MODE='REFRESH' OR MODE='REFRESH_ALL') AND (_Param_C<>'False') THEN
		-- TODO: co z usunieciami zapisow??? TRIGGER?
		IF MODE='REFRESH' THEN 
			SELECT max(maxtime) FROM temp_salda_obroty_analityczne WHERE rok=_ROK INTO _MAXTIME_S;
			SELECT max(z.localtime) FROM zapisy z WHERE rok=_ROK INTO _MAXTIME_Z;
		END IF;
		IF MODE='REFRESH_ALL' OR (IFNULL(_MAXTIME_Z,'2000-01-01 00:00:01')>IFNULL(_MAXTIME_S,'2000-01-01 00:00:00')) THEN
			DELETE FROM temp_salda_obroty_analityczne WHERE rok=_ROK;
			INSERT INTO temp_salda_obroty_analityczne
			SELECT konto,
			sum(if(rodzaj_fk in (1,2) and stronawn,kwota,0)) as bown,
			sum(if(rodzaj_fk in (1,2) and not stronawn,kwota,0)) as boma,
			sum(if(stronawn,kwota,0)) as wn,sum(if(stronawn,0,kwota)) as ma,
			rok ,month(data_dowodu) as mc, max(z.localtime) as maxtime
			FROM zapisy z
			WHERE rok=_ROK
			GROUP BY konto,rok,mc;
		END IF;
--		SELECT ROWCOUNT() INTO @result;
		SELECT @result as aResult;
	END IF;

	IF (MODE='REFRESH_WGDAT' OR MODE='REFRESH_WGDAT_ALL') AND (_Param_C<>'False') THEN
		IF MODE='REFRESH_WGDAT' THEN 
			SELECT max(maxtime) FROM temp_salda_obroty_analityczne_wg_dat WHERE rok=_ROK INTO _MAXTIME_S;
			SELECT max(z.localtime) FROM zapisy z WHERE rok=_ROK INTO _MAXTIME_Z;
		END IF;
		IF MODE='REFRESH_WGDAT_ALL' OR (IFNULL(_MAXTIME_Z,'2000-01-01 00:00:01')>IFNULL(_MAXTIME_S,'2000-01-01 00:00:00')) THEN
			DELETE FROM temp_salda_obroty_analityczne_wg_dat WHERE rok=_ROK;
			INSERT INTO temp_salda_obroty_analityczne_wg_dat
			SELECT konto,
			sum(if(rodzaj_fk in (1,2) and stronawn,kwota,0)) as bown,
			sum(if(rodzaj_fk in (1,2) and not stronawn,kwota,0)) as boma,
			sum(if(stronawn,kwota,0)) as wn,sum(if(stronawn,0,kwota)) as ma,
			rok ,data_dowodu, max(z.localtime) as maxtime
			FROM zapisy z
			WHERE rok=_ROK
			GROUP BY konto,rok,data_dowodu;
		END IF;
--		SELECT ROWCOUNT() INTO @result;
		SELECT @result as aResult;
	END IF;
	IF MODE='SALDA_WGDAT'  AND (_Param_C<>'False') THEN
		SET @wnbo:=0,@mabo:=0,@wnnar:=0,@manar:=0,@wnokr:=0,@maokr:=0,@wnsaldo:=0,@masaldo:=0,@konto:='';

		DROP TEMPORARY TABLE IF EXISTS temp_salda_obroty_wg_dat;
		CREATE TEMPORARY TABLE temp_salda_obroty_wg_dat (
		rok int not null default 0,
		wnbo double(18,2) not null default 0,
		mabo double(18,2) not null default 0,
		wn double(18,2) not null default 0,
		ma double(18,2) not null default 0,
		wnnar double(18,2) not null default 0,
		manar double(18,2) not null default 0,
		wnsaldo double(18,2) not null default 0,
		masaldo double(18,2) not null default 0,
		konto varchar(38) not null,
		data_dowodu DATE,
		maxtime DATETIME,
--		INDEX maxtime(maxtime),
		INDEX konto(konto,rok,maxtime)
--		INDEX rdk(rok,data_dowodu,konto)
		);
		INSERT INTO temp_salda_obroty_wg_dat
		SELECT 
		s.rok,
		_Rnd2(_VarDouble(@wnbo:=if(ifnull(@konto,'')<>s.konto,s.bown,@wnbo+s.bown))) as wnbo,
		_Rnd2(_VarDouble(@mabo:=if(ifnull(@konto,'')<>s.konto,s.boma,@mabo+s.boma))) as mabo,
		s.wn,
		s.ma,
		_Rnd2(_VarDouble(@wnnar:=if(ifnull(@konto,'')<>s.konto,s.wn,@wnnar+s.wn))) as wnnar,
		_Rnd2(_VarDouble(@manar:=if(ifnull(@konto,'')<>s.konto,s.ma,@manar+s.ma))) as manar,
		_Rnd2(0) as wnsaldo,
		_Rnd2(0) as masaldo,
		@konto:=s.konto as konto,
		s.data_dowodu,
		s.maxtime
		FROM temp_salda_obroty_analityczne_wg_dat s 
		ORDER BY konto,data_dowodu;
		UPDATE temp_salda_obroty_wg_dat SET wnsaldo=if(ABS(wnnar)>=ABS(manar),wnnar-manar,0),masaldo=if(ABS(manar)>ABS(wnnar),manar-wnnar,0);
	END IF;

	IF MODE='SALDA'  AND (_Param_C<>'False') THEN
		DROP TEMPORARY TABLE IF EXISTS temp_salda_obroty;
		CREATE TEMPORARY TABLE temp_salda_obroty (
		konto varchar(38) not null,
		bown double(16,4) not null default 0,
		boma double(16,4) not null default 0,
		wn_okr double(16,4) not null default 0,
		ma_okr double(16,4) not null default 0,
		wn double(16,4) not null default 0,
		ma double(16,4) not null default 0,
		INDEX konto(konto)
		);
		INSERT INTO temp_salda_obroty
		SELECT konto,
		sum(bown) as bown,sum(boma) as boma,
		sum(wn) as wn,sum(ma) as ma
		FROM temp_salda_obroty_analityczne
		WHERE rok=_ROK and mc<=month(_DATADO)
-- TODO: odwzorowac reszte kolumn, dodac katcole
		GROUP BY konto,rok,mc;

	END IF;

	
END$$

DROP FUNCTION IF EXISTS `fk_saldo`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `fk_saldo`(konto VARCHAR(255),strona CHAR(2)) RETURNS DOUBLE(16,4)
    NO SQL
    DETERMINISTIC
BEGIN
	RETURN _Value;
END$$


-- TODO: porzadki z indeksami w zapisach

DELIMITER ;
