Delimiter //
/**
 * Author:  armen
 * Created: 8 mars 2018
 */

Drop Procedure if exists LOADER_EDMINDEX_NORMALIZE_CHECK_METADATA_EMPTY//

Create Procedure LOADER_EDMINDEX_NORMALIZE_CHECK_METADATA_EMPTY (
    In batchnum int
)
Begin
        -- test test clé meta data fourni (DOCUMENTS_METADATA_KEY  <> ) mais avec des donnée vide
        Update
            LOADER_STATUS sts
        Set LOADER_STATUS_WARNCOUNT = LOADER_STATUS_WARNCOUNT + 1
            , sts.LOADER_STATUS_WARNMSGS =  LOADER_CONCAT_ERR(sts.LOADER_STATUS_WARNMSGS, 'la Meta data ne contient aucune donnée')
        Where
            sts.LOADER_STATUS_ERRCOUNT=0
--             And sts.ID_LOADER_STATUS In (
            And Exists (
                Select
                    mdata.LOADER_STATUS_ID_LOADER_STATUS
                From
                    LOADER_EDMINDEX_DOCUMENTS_METADATA mdata
                Where
                    Trim(Coalesce(mdata.DOCUMENTS_METADATA_KEY, '')) <> ''
                    And (
                        mdata.DOCUMENTS_METADATA_INT is Null
                        And mdata.DOCUMENTS_METADATA_EXTID Is null
                        And mdata.DOCUMENTS_METADATA_DATE Is null
                        And mdata.DOCUMENTS_METADATA_DECIMAL Is null
                        And trim(Coalesce(mdata.DOCUMENTS_METADATA_REF, '')) = ''
                        -- le content peut contenir juste des espaces
                        And Coalesce(mdata.DOCUMENTS_METADATA_CONTENT, '') = ''

                    )
                    And mdata.LOADER_STATUS_ID_LOADER_STATUS = sts.ID_LOADER_STATUS
                Limit 1
            )
            And sts.LOADER_STATUS_BATCH_NUM = batchnum
        ;
    -- test test clé meta data fourni sans clé (DOCUMENTS_METADATA_KEY vide  mais avec des donnée non vide dans les autre champs)
        Update
            LOADER_STATUS sts
        Set LOADER_STATUS_WARNCOUNT = LOADER_STATUS_WARNCOUNT + 1
            , sts.LOADER_STATUS_WARNMSGS = LOADER_CONCAT_ERR(sts.LOADER_STATUS_WARNMSGS, 'Meta data ignorée car la clé est vide')
        Where
            sts.LOADER_STATUS_ERRCOUNT=0
--             And sts.ID_LOADER_STATUS In (
            And Exists (
                Select
                    mdata.LOADER_STATUS_ID_LOADER_STATUS
                From
                    LOADER_EDMINDEX_DOCUMENTS_METADATA mdata
                Where
                    Trim(Coalesce(mdata.DOCUMENTS_METADATA_KEY, '')) = ''
                    And (
                        mdata.DOCUMENTS_METADATA_INT is Not Null
                        Or mdata.DOCUMENTS_METADATA_EXTID Is Not null
                        Or mdata.DOCUMENTS_METADATA_DATE Is Not null
                        Or mdata.DOCUMENTS_METADATA_DECIMAL Is Not null
                        Or trim(Coalesce(mdata.DOCUMENTS_METADATA_REF , '')) <> ''
                        Or trim(Coalesce(mdata.DOCUMENTS_METADATA_CONTENT, '')) <> ''

                    )
                    And mdata.LOADER_STATUS_ID_LOADER_STATUS = sts.ID_LOADER_STATUS
                Limit 1
            )
            And sts.LOADER_STATUS_BATCH_NUM = batchnum
        ;

        -- purge des meta data non importable
        Delete
            mdata
        From
            LOADER_STATUS sts
            Inner Join LOADER_EDMINDEX_DOCUMENTS_METADATA mdata
                On mdata.LOADER_STATUS_ID_LOADER_STATUS = sts.ID_LOADER_STATUS
        Where
            Trim(Coalesce(mdata.DOCUMENTS_METADATA_KEY, '')) = ''
                    
            And sts.LOADER_STATUS_BATCH_NUM = batchnum
        ;

End //

Delimiter ;

