Delimiter //
/**
 * Author:  armen
 * Created: 12 oct. 2016
doit etre executé apres LOADER_MEMBERS_NORMALIZE_CHECK_NOT_ACCOUNT_CONTACT
 */

Drop Procedure If Exists LOADER_MEMBERS_NORMALIZE_MAP_CREATE_OR_UPDATE//
Create Procedure LOADER_MEMBERS_NORMALIZE_MAP_CREATE_OR_UPDATE(
    In batchnum Int
)
Begin

	Update LOADER_MEMBERS ltar
	Inner Join LOADER_STATUS lst
		On ltar.LOADER_STATUS_ID_LOADER_STATUS = lst.ID_LOADER_STATUS
	Inner Join CONTACTS chkctc
		On ltar.CONTACTS_REF = chkctc.CONTACTS_REF
	Left Join CONTACTS_INDIVIDUAL chkindiv
		On chkctc.ID_CONTACTS = chkindiv.CONTACTS_ID_CONTACTS
	Left Join MEMBERS chkmbr
		on chkindiv.ID_CONTACTS_INDIVIDUAL = chkmbr.CONTACT_INDIV_ID_CONTACT_INDIV
	Set 
		ltar.ID_CONTACTS = chkctc.ID_CONTACTS
		, ltar.CONTACT_INDIV_ID_CONTACT_INDIV = chkindiv.ID_CONTACTS_INDIVIDUAL
		, ltar.ID_MEMBERS = chkmbr.ID_MEMBERS
	Where
		lst.LOADER_STATUS_BATCH_NUM = batchnum
		And lst.LOADER_STATUS_ERRCOUNT = 0
	;
	-- maj des erreur
	Update LOADER_MEMBERS ltar
	Inner Join LOADER_STATUS lst
		On ltar.LOADER_STATUS_ID_LOADER_STATUS = lst.ID_LOADER_STATUS
	Set 
		lst.LOADER_STATUS_ERRCOUNT = lst.LOADER_STATUS_ERRCOUNT + 1
        , lst.LOADER_STATUS_ERRMSGS = LOADER_CONCAT_ERR(lst.LOADER_STATUS_ERRMSGS, Concat('Le membre ne peut être créé / mise à jour car le contact individuel est inexistant ' ) )
	Where
		lst.LOADER_STATUS_BATCH_NUM = batchnum
		And ltar.CONTACT_INDIV_ID_CONTACT_INDIV Is Null
		And lst.LOADER_STATUS_ERRCOUNT = 0
	;

	Drop Temporary Table If Exists TMP_LOADER_MEMBERS;
	Create Temporary Table TMP_LOADER_MEMBERS (
		ID_TMP_LOADER_MEMBERS Int Unsigned Not Null Primary Key Auto_increment
		, LOADER_STATUS_ID_LOADER_STATUS Int Unsigned Not Null Unique Key 
	);

	Insert Into TMP_LOADER_MEMBERS(LOADER_STATUS_ID_LOADER_STATUS)
	Select 
		lst.ID_LOADER_STATUS
	From
		LOADER_STATUS lst
		Inner Join LOADER_MEMBERS ltar
			On lst.ID_LOADER_STATUS = ltar.LOADER_STATUS_ID_LOADER_STATUS
	Where  lst.LOADER_STATUS_BATCH_NUM = batchnum
		And lst.LOADER_STATUS_ERRCOUNT = 0
		And ltar.ID_MEMBERS Is Null
	;
	Update LOADER_MEMBERS ltar
	Inner Join TMP_LOADER_MEMBERS 
		On ltar.LOADER_STATUS_ID_LOADER_STATUS = TMP_LOADER_MEMBERS.LOADER_STATUS_ID_LOADER_STATUS
	Set ltar.ID_MEMBERS_NEW = TMP_LOADER_MEMBERS.ID_TMP_LOADER_MEMBERS
	;
	Drop Temporary Table If Exists TMP_LOADER_MEMBERS;
End//
Delimiter ;