Delimiter //
/**
 * If a entry with the combination of Entity Id, Account Ref and Entry Ref 
 * already exists in application, update it with the imported one. 
 * Else, create a new entry.
 * must be call AFTER LOADER_ENTRIES_NORMALIZE_MAP_ACCOUNT_REF_PROVIDED
 * @param int batchnum
 * @author Benoit
 * @ticket #3758
 */ 
Drop Procedure if Exists LOADER_ENTRIES_NORMALIZE_MAP_ENTRY_CREATE_OR_UPDATE//
 
Create Procedure LOADER_ENTRIES_NORMALIZE_MAP_ENTRY_CREATE_OR_UPDATE (
    In batchnum int
)
Begin

    -- Entry doesn't exist

    -- -- Stores new ids in a temporary table
    DROP TEMPORARY TABLE IF EXISTS temp_ENTRIES;
    CREATE TEMPORARY TABLE temp_ENTRIES (
        ID_ENTRIES Int Unsigned Not Null Auto_increment
        , LOADER_STATUS_ID_LOADER_STATUS Int Unsigned Not Null
		, LOADER_ENTRIES_ID_LOADER_ENTRIES Int Unsigned Not Null
        , Primary Key (ID_ENTRIES)
        , Index (LOADER_STATUS_ID_LOADER_STATUS)
		, Index (LOADER_ENTRIES_ID_LOADER_ENTRIES)
    );

    INSERT INTO temp_ENTRIES (
            LOADER_STATUS_ID_LOADER_STATUS, LOADER_ENTRIES_ID_LOADER_ENTRIES
        )

        SELECT
            S.ID_LOADER_STATUS, LE.ID_LOADER_ENTRIES
        FROM
            LOADER_ENTRIES LE
            INNER JOIN LOADER_STATUS S
                ON S.ID_LOADER_STATUS = LE.LOADER_STATUS_ID_LOADER_STATUS
            LEFT JOIN ENTRIES_has_ACCOUNTS EHA
                On LE.ACCOUNTS_ID_ACCOUNTS = EHA.ACCOUNTS_ID_ACCOUNTS
                And LE.ENTRIES_ACC_EXT_REF = EHA.ENTRIES_ACC_EXT_REF

        WHERE S.LOADER_STATUS_BATCH_NUM = batchnum
            And EHA.ENTRIES_ACC_EXT_REF Is Null
			And LE.ID_ENTRIES_NEW Is Null
    ;
    -- -- Update new entries with their new id
    UPDATE 
        LOADER_ENTRIES LE
        INNER JOIN temp_ENTRIES TMP
--             ON TMP.LOADER_STATUS_ID_LOADER_STATUS = LE.LOADER_STATUS_ID_LOADER_STATUS
            ON TMP.LOADER_ENTRIES_ID_LOADER_ENTRIES = LE.ID_LOADER_ENTRIES
    SET
        LE.ID_ENTRIES_NEW = TMP.ID_ENTRIES
    ;

    -- -- Insert new entry/account relations
    INSERT INTO 
        LOADER_ENTRIES_ENTRIES_has_ACCOUNTS (
            LOADER_STATUS_ID_LOADER_STATUS
			, LOADER_ENTRIES_ID_LOADER_ENTRIES
            , ACCOUNTS_ID_ACCOUNTS
            , ENTRIES_ACC_EXT_REF
        )
        SELECT 
            LE.LOADER_STATUS_ID_LOADER_STATUS
			, LE.ID_LOADER_ENTRIES
            , LE.ACCOUNTS_ID_ACCOUNTS
            , LE.ENTRIES_ACC_EXT_REF
        FROM
            LOADER_ENTRIES LE
            INNER JOIN LOADER_STATUS S
                ON S.ID_LOADER_STATUS = LE.LOADER_STATUS_ID_LOADER_STATUS
        WHERE S.LOADER_STATUS_BATCH_NUM = batchnum
            And S.LOADER_STATUS_ERRCOUNT = 0
            And LE.ACCOUNTS_ID_ACCOUNTS is not null
            And LE.ID_ENTRIES_NEW IS NOT NULL
    ;
    
    -- Entry exists
    UPDATE
        LOADER_ENTRIES LE
        INNER JOIN LOADER_STATUS S
            ON S.ID_LOADER_STATUS = LE.LOADER_STATUS_ID_LOADER_STATUS
        INNER JOIN ENTRIES_has_ACCOUNTS EHA
            ON LE.ACCOUNTS_ID_ACCOUNTS = EHA.ACCOUNTS_ID_ACCOUNTS
            And LE.ENTRIES_ACC_EXT_REF = EHA.ENTRIES_ACC_EXT_REF
    SET
        LE.ID_ENTRIES = EHA.ENTRIES_ID_ENTRIES
    WHERE 
         S.LOADER_STATUS_BATCH_NUM =  batchnum
    ;

End //
Delimiter ;