Delimiter //
/**
    In batchnum Int : num du lot, si 0 ou null traitement de l'ensembles des lignes
    
*/

Drop Procedure if exists LOADER_CONTACTS_ADDRESSES_INSERT//

Create Procedure LOADER_CONTACTS_ADDRESSES_INSERT (
    In batchnum Int, 
    In lastId Int
)
Begin
    -- insertion
	Insert Into CONTACTS_ADDRESSES (
		CONTACTS_ID_CONTACTS,
		DF_COUNTRY_ID_DF_COUNTRY, CONTACTS_ADDRESSES_LAST_UPDATE,
		CONTACTS_ADDRESSES_LINE_1, CONTACTS_ADDRESSES_LINE_2, CONTACTS_ADDRESSES_LINE_3, CONTACTS_ADDRESSES_LINE_4,
		CONTACTS_ADDRESSES_ZIP_CODE, CONTACTS_ADDRESSES_CITY, CONTACTS_ADDRESSES_TRASHED,
		DF_CHECK_STAT_ID_DF_CHECK_STAT
	)  
	Select
src.ID_CONTACTS_NEW + lastId
         , src.DF_COUNTRY_ID_DF_COUNTRY, CONTACTS_LAST_UPDATE
         , Substring(CONTACTS_ADDRESSES_LINE_1, 1, 45)
         , Substring(CONTACTS_ADDRESSES_LINE_2, 1, 45)
         , Substring(CONTACTS_ADDRESSES_LINE_3, 1, 45)
         , Substring(CONTACTS_ADDRESSES_LINE_4, 1, 45)
         , Substring(CONTACTS_ADDRESSES_ZIP_CODE, 1, 10)
         , Substring(CONTACTS_ADDRESSES_CITY, 1, 45)
         , CONTACTS_TRASHED
         , DF_CHECK_STAT_ID_DF_CHECK_STAT
	From  
		LOADER_CONTACTS src  
		Inner Join LOADER_STATUS lst  
			On src.LOADER_STATUS_ID_LOADER_STATUS = lst.ID_LOADER_STATUS  

	Where 1 = 1  
		And lst.LOADER_STATUS_ERRCOUNT = 0  
		And lst.LOADER_STATUS_BATCH_NUM =  batchnum
		And Coalesce(src.ID_CONTACTS_NEW, 0) <> 0 

	;

	-- il se peut que des contacts soient exitants mais pas les adresses de ces contacts
	-- creation de nouvelles adresses pour des contacts existant
	Insert Into CONTACTS_ADDRESSES (
		CONTACTS_ID_CONTACTS,
		DF_COUNTRY_ID_DF_COUNTRY, CONTACTS_ADDRESSES_LAST_UPDATE,
		CONTACTS_ADDRESSES_LINE_1, CONTACTS_ADDRESSES_LINE_2, CONTACTS_ADDRESSES_LINE_3, CONTACTS_ADDRESSES_LINE_4,
		CONTACTS_ADDRESSES_ZIP_CODE, CONTACTS_ADDRESSES_CITY, CONTACTS_ADDRESSES_TRASHED,
		DF_CHECK_STAT_ID_DF_CHECK_STAT
	)  
	Select  
		src.ID_CONTACTS, 
		src.DF_COUNTRY_ID_DF_COUNTRY, src.CONTACTS_LAST_UPDATE,
		src.CONTACTS_ADDRESSES_LINE_1, src.CONTACTS_ADDRESSES_LINE_2, src.CONTACTS_ADDRESSES_LINE_3, src.CONTACTS_ADDRESSES_LINE_4,
		src.CONTACTS_ADDRESSES_ZIP_CODE, src.CONTACTS_ADDRESSES_CITY, src.CONTACTS_TRASHED,
		src.DF_CHECK_STAT_ID_DF_CHECK_STAT
	From  
		LOADER_CONTACTS src  
		Inner Join LOADER_STATUS lst  
			On src.LOADER_STATUS_ID_LOADER_STATUS = lst.ID_LOADER_STATUS  
		Left Join CONTACTS_ADDRESSES ctcchk
			On src.ID_CONTACTS = ctcchk.CONTACTS_ID_CONTACTS
	Where 1 = 1  
		And lst.LOADER_STATUS_ERRCOUNT = 0  
		And lst.LOADER_STATUS_BATCH_NUM =  batchnum
		And ctcchk.CONTACTS_ID_CONTACTS Is Null
		And src.ID_CONTACTS Is Not Null

	;


End //

Delimiter ;