 DELIMITER $$

DROP PROCEDURE IF EXISTS `ht_kontrah_info_tools`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `ht_kontrah_info_tools`(
	IN _MODE VARCHAR(150),
	IN _GUID VARCHAR(38),
	IN _Param_A VARCHAR(50),	
	IN _Param_B VARCHAR(50),	
	IN _Param_C VARCHAR(50),	
	IN _Param_D VARCHAR(50)	
)
SPROC: BEGIN
	/*
	_MODE

	*/
	
	DECLARE _TerminRealizacji DATETIME;
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'get.eInovice.emails'
		Pobieranie adresow do wysylki faktur mailem (zadanie  nr 46048)
		Parametry
			_GUID	  kontrahent 

		CALL ht_kontrah_info_tools('get.eInovice.emails', @KontrGUID, '', '', '', '');				
		
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE = 'get.eInovice.emails' THEN
	
	SELECT  
		GROUP_CONCAT(REPLACE(K.adres,"'","")) AS emails -- , IF (osobaGUID<>'', 1, 0) AS typ
	FROM 
		kontakty K   
	LEFT JOIN osoby O ON O.GUID=K.osobaGUID  
	LEFT JOIN kontrah KO ON KO.GUID=K.kontrGUID  
	WHERE 
		K.kontrGUID = _GUID AND K.typ_medium=3 AND IFNULL(O.nieaktywny, 0) = 0  AND K.opis LIKE '%e%faktur%';
		
	END IF;	
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'GetMailCDS'
		Funkcja zastepujaca GUI_Motlawa.GetMailCDS_
		Parametry
			_GUID	  kontrahent / osoba
			_ParamA   'kontrah' lub ''
			_ParamB   'limit' lub ''
			_ParamC   okreslenie wielkosci limitu (tylko przy _ParamB  = 'limit')
		CALL ht_kontrah_info_tools('GetMailCDS', @KontrGUID, 'kontrah', '', '', '');		
		CALL ht_kontrah_info_tools('GetMailCDS', @KontrGUID, 'kontrah', 'limit', '1', '');		
		CALL ht_kontrah_info_tools('GetMailCDS', @osobaGUID, '', '', '', '');		
		
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE = 'GetMailCDS' THEN
	
	/*
	
	*/
	SELECT 1;
		
	END IF;
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'refresh.zam.kontrah'
		Odświeża zam_interval i zam_prev_date
		Parametry
			_GUID	Kontrahent
		CALL ht_kontrah_info_tools('refresh.zam.kontrah', @KontrGUID, '', '', '', '');		
		CALL ht_kontrah_info_tools('refresh.zam.kontrah.zam_interval', @KontrGUID, '', '', '', '');		
		CALL ht_kontrah_info_tools('refresh.zam.kontrah.zam_prev_date', @KontrGUID, '', '', '', '');		
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE LIKE 'refresh.zam.kontrah%' THEN
		
		IF _MODE = 'refresh.zam.kontrah' THEN
			CALL _modify_ht_kontrah_info('refresh.zam.kontrah', _GUID, '', '', '', '');		
		END IF;	
		
		IF _MODE = 'refresh.zam.kontrah.zam_interval' THEN
			CALL _modify_ht_kontrah_info('refresh.zam.kontrah.zam_interval', _GUID, '', '', '', '');		
		END IF;	
		
		IF _MODE = 'refresh.zam.kontrah.zam_prev_date' THEN
			CALL _modify_ht_kontrah_info('refresh.zam.kontrah.zam_prev_date', _GUID, '', '', '', '');		
		END IF;	
		
	END IF;	--	IF _MODE LIKE 'refresh.zam.kontrah%'


/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'update.kontrah.all'
		Odświeża wszystkich
		Parametry
		CALL ht_kontrah_info_tools('update.kontrah.all', '', '', '', '', '');		
		CALL ht_kontrah_info_tools('update.kontrah.all', 'clear', '', '', '', '');		
		CALL ht_kontrah_info_tools('update.kontrah.all.z.temp_srednie3m', 'clear', '', '', '', '');		
		
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE LIKE 'update.kontrah.all%' THEN
	
		IF _Param_A = 'clear' THEN
			TRUNCATE `ht_kontrah_info`;
		END IF;	
		
		IF _MODE = 'update.kontrah.all' THEN
			INSERT IGNORE INTO `ht_kontrah_info`
				(KontrGUID)
				SELECT DISTINCT KontrGUID FROM `zmng` WHERE rodzaj = 27
			;	
		END IF;
		
		IF _MODE = 'update.kontrah.all.z.temp_srednie3m' THEN
			INSERT IGNORE INTO `ht_kontrah_info`
				(KontrGUID)
				SELECT DISTINCT KontrGUID FROM `temp_srednie3m`
			;	
		END IF;
		
		UPDATE `ht_kontrah_info` ki
		SET
			ki.zam_interval = 
			(
				SELECT dlmagazynu FROM `kontrah` WHERE GUID = ki.KontrGUID
			)
			,
			ki.zam_prev_date = 
			IFNULL(
			(
				SELECT MAX(n.terminrealizacji_plan) 
				FROM `zmng` n INNER JOIN `zmpo` p WHERE n.GUID = p.IdnNG AND n.KontrGUID = ki.KontrGUID AND n.Rodzaj IN (27) AND p.ilosc > 0 and p.zrealizowano > 0 
			),
				ki.zam_prev_date
			)
		;



	--	ponizsze wykonuje sie w triggerze
		UPDATE `ht_kontrah_info` ki
		SET
			ki.zam_next_date = DATE_ADD(ki.zam_prev_date, INTERVAL ki.zam_interval DAY)
		;	
	END IF;	--	IF _MODE = 'update.kontrah.all'
	
	
END$$


DELIMITER ;

/*
$Log: ht_kontrah_info_tools.sql,v $
Revision 1.16  2025/03/31 06:49:12  jmam
$send$ Zaległe poprawki

*/

/*
====================================================================================================================================================================================
	NOTATNIK
====================================================================================================================================================================================
	 
DROP TABLE `ht_kontrah_info`;
CREATE TABLE `ht_kontrah_info` (
  `KontrGUID` VARCHAR(38) NOT NULL DEFAULT '' COMMENT 'GUID kontrahenta',
  `zam_prev_date` DATE DEFAULT '0000-00-00' NULL COMMENT 'data ostatniego zamowienia',
  `zam_interval` INT(11) DEFAULT 0 COMMENT 'zamowienie co ile dni jako dl. magazynu',
  `zam_next_date` DATE DEFAULT NULL COMMENT 'data nastepnego zamowienia',
  PRIMARY KEY (`KontrGUID`),
  KEY `zam_next_date` (`zam_next_date`)
) ENGINE=MYISAM DEFAULT CHARSET=cp1250
;


-- TRUNCATE `ht_kontrah_info`;

INSERT IGNORE INTO `ht_kontrah_info`
	(KontrGUID)
	SELECT DISTINCT KontrGUID FROM `zmng` WHERE rodzaj = 27
;	
UPDATE `ht_kontrah_info` ki
SET
	ki.zam_interval = 
	(
		SELECT dlmagazynu FROM `kontrah` WHERE GUID = ki.KontrGUID
	)
	,
	ki.zam_prev_date = 
	(
		SELECT MAX(n.terminrealizacji_plan) 
		FROM `zmng` n WHERE n.KontrGUID = ki.KontrGUID AND n.Rodzaj IN (27) AND n.ilepozzreal<>0
	)
;



--	ponizsze wykonuje sie w triggerze
		UPDATE `ht_kontrah_info` ki
		SET
			ki.zam_next_date = DATE_ADD(ki.zam_prev_date, INTERVAL ki.zam_interval DAY)
		;	

--	ponizsze wolac w HT po zmianach w kontrahencie lub realizacji zamowienia
CALL ht_kontrah_info_tools('refresh.zam.kontrah', 'IM_57d9bd3b4a9c0d4fe50500b41395550b', '', '', '', '');		



				UPDATE `zmng` n
				SET
					terminrealizacji_plan = terminrealizacji
				WHERE
					terminrealizacji_plan IS NULL
					AND terminrealizacji IS NOT NULL 
					AND terminrealizacji > '2011-04-01'
					AND Rodzaj IN (27) 
				;	

				SELECT
					-- MIN(datawystaw), MAX(datawystaw) 
					*
				FROM `zmng` n	
				WHERE
					terminrealizacji_plan IS NULL
					AND terminrealizacji IS NOT NULL 
					AND terminrealizacji > '2011-04-01'
					AND Rodzaj IN (27) 
				ORDER BY n.IDN DESC	
				LIMIT 500
				;	


	
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

		UPDATE `zmng` n
		SET
			terminrealizacji_plan = terminrealizacji
		WHERE
			terminrealizacji_plan IS NULL
			AND terminrealizacji IS NOT NULL 
			AND terminrealizacji > '2011-04-01'
			AND Rodzaj IN (27) 
		;	
	
		UPDATE `ht_kontrah_info` ki
		SET
			ki.zam_prev_date = 
			(
				SELECT MAX(n.terminrealizacji_plan) 
				FROM `zmng` n WHERE n.KontrGUID = ki.KontrGUID AND n.Rodzaj IN (27) AND n.ilepozzreal<>0
			)
		;



	--	ponizsze wykonuje sie w triggerze
		UPDATE `ht_kontrah_info` ki
		SET
			ki.zam_next_date = DATE_ADD(ki.zam_prev_date, INTERVAL ki.zam_interval DAY)
		;	
	
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
*/

		