Delimiter $$
/**
 * Author:  armen
 * Created: 21 juin 2018
 */

--  Accounts
-- Drop Procedure if Exists BUSINESS_COMPUTE_PUBLICVIEWS_PAYEMENT_BEHAVIOR $$
Drop Procedure if Exists BUSINESS_COMPUTE_PUBLICVIEWS_PAYMENT_BEHAVIOR $$
Create Procedure BUSINESS_COMPUTE_PUBLICVIEWS_PAYMENT_BEHAVIOR ()
Begin

        Set @bcpvpb_dfEntType_INV = (Select ID_DF_ENTRIES_TYPE from DF_ENTRIES_TYPE where DF_ENTRIES_TYPE_CODE = 'INV' Limit 1 );
        Set @bcpvpb_dfEntType_CRN = (Select ID_DF_ENTRIES_TYPE from DF_ENTRIES_TYPE where DF_ENTRIES_TYPE_CODE = 'CRN' Limit 1 );
        Set @bcpvpb_dfMthcSts_OPEN = (Select ID_DF_MATCHINGCODE_STATE from DF_MATCHINGCODE_STATE where DF_MATCHINGCODE_STATE_CODE = 'OPEN ENTRY' Limit 1);
        Set @bcpvpb_dfMthcSts_PARTIAL = (Select ID_DF_MATCHINGCODE_STATE from DF_MATCHINGCODE_STATE where DF_MATCHINGCODE_STATE_CODE = 'PARTIAL MATCHING CODE' Limit 1);
        Set @bcpvpb_dfMthcSts_CLOSED = (Select ID_DF_MATCHINGCODE_STATE from DF_MATCHINGCODE_STATE where DF_MATCHINGCODE_STATE_CODE = 'CLOSED ENTRY' Limit 1);
        set @bcpvpb_refDate = curdate();

        -- obtention des paramètres
        Set @bcpvpb_miscParamTextVal = Coalesce(
                        (
                        Select MISCELLANEOUS_PARAMS_TEXT_VAL1
                        from MISCELLANEOUS_PARAMS
                        where MISCELLANEOUS_PARAMS_KEY = 'PV.PAYMENT_BEHAVIOR'
                        Limit 1
                    )
                    , ''
                )
            ;

        Set @bcpvpb_maxDelay = extractValue(@bcpvpb_miscParamTextVal,  '/MiscParamsRoot/filters/max_delay') ;
        Set @bcpvpb_maxDelay = Case When Coalesce(CASH_ISINTEGER(@bcpvpb_maxDelay), 0)=0 then 0 else Cast(@bcpvpb_maxDelay as SIGNED) End;
        if @bcpvpb_maxDelay < 1 Then
            Set @bcpvpb_maxDelay = 15;
        end If;

        set @bcpvpb_minUpdtDate = date_sub(@bcpvpb_refDate, interval @bcpvpb_maxDelay DAY);
        Set @bcpvpb_forcdMatchKey = 'FORCEDMATCHSTS';

        Truncate Table TMP_V_PUBLIC_PAYMENT_BEHAVIOR_VIEW;

        Insert Into TMP_V_PUBLIC_PAYMENT_BEHAVIOR_VIEW (
            `ENTRIES_ID_ENTRIES` 
            , `ACCOUNTS_ID_ACCOUNTS` 
            , `ENTITIES_ID_ENTITIES` 
            , `V_PUBLIC_PAYMENT_BEHAVIOR_ZIP_CODE` 
            , `V_PUBLIC_PAYMENT_BEHAVIOR_COUNTY_CODE`
            , `V_PUBLIC_PAYMENT_BEHAVIOR_BUSINESS_ID`
            , `V_PUBLIC_PAYMENT_BEHAVIOR_ENTRIES_REF`
            , `V_PUBLIC_PAYMENT_BEHAVIOR_COMPANY_NAME` 
            , `V_PUBLIC_PAYMENT_BEHAVIOR_ENTRIES_DATE` 
            , `V_PUBLIC_PAYMENT_BEHAVIOR_ENTRIES_DUE_DATE`
            , `V_PUBLIC_PAYMENT_BEHAVIOR_CLOSED_MATCH_DATE` 
            , `V_PUBLIC_PAYMENT_BEHAVIOR_AMOUNT` 
            , `V_PUBLIC_PAYMENT_BEHAVIOR_CURRENCIES_CODE` 
            , `V_PUBLIC_PAYMENT_BEHAVIOR_MATCHING_STATUS` 

        )

        Select
            ent.ID_ENTRIES, acc.ID_ACCOUNTS, entt.ID_ENTITIES
            , ctadr_acc.CONTACTS_ADDRESSES_ZIP_CODE, dfcntr_acc.DF_COUNTRY_LONG_CODE
            -- , cb_acc.CONTACTS_BUSINESS_SIRET
        --     , Length(cb_acc.CONTACTS_BUSINESS_SIRET)
            , Case 
                When dfcntr_acc.DF_COUNTRY_LONG_CODE = 'FRA' 
                    And Length(cb_acc.CONTACTS_BUSINESS_SIRET)=14
                Then
                    substring(cb_acc.CONTACTS_BUSINESS_SIRET, 1, 9)
                Else
                    cb_acc.CONTACTS_BUSINESS_SIRET
            END BUSINESS_ID
--             , eha.ENTRIES_ACC_EXT_REF
            , ent.ENTRIES_REF
            , cb_acc.CONTACTS_BUSINESS_COMPANY_NAME
            , ent.ENTRIES_DATE, ent.ENTRIES_DUE_DATE
            , Coalesce(mc.MATCHINGCODES_DATE, ecdmtch.ENTRIES_COMPUTED_DATA_DATE) CLOSED_MATCH_DATE
            , (ent.ENTRIES_ENT_DEBIT - ent.ENTRIES_ENT_CREDIT) ENTRIES_ENT_AMOUNT
            , dfcurnc_ennt.DF_CURRENCIES_CODE
            , Case
                    When ent.ENTRIES_RECLAIMS > 0 then 
                        'DISPUTED'
                    When ent.DF_MATCH_STAT_ID_DF_MATCH_STAT In (@bcpvpb_dfMthcSts_OPEN, @bcpvpb_dfMthcSts_PARTIAL) And ent.ENTRIES_DUE_DATE <= @bcpvpb_refDate  then 
                        'OVERDUE'
                    When ent.DF_MATCH_STAT_ID_DF_MATCH_STAT In (@bcpvpb_dfMthcSts_OPEN, @bcpvpb_dfMthcSts_PARTIAL) then 
                        'NOT LETTERED' -- not overdue
                    When ent.ENTRIES_DUE_DATE > Coalesce(mc.MATCHINGCODES_DATE, ecdmtch.ENTRIES_COMPUTED_DATA_DATE, '2000-01-01') then
                        'LETTERED'
                    Else 
                        'LETTERED_LATE'

                End COMPUTED_LET_STATUS

        From ENTRIES ent
            Inner Join ENTRIES_has_ACCOUNTS eha
                On ent.ID_ENTRIES = eha.ENTRIES_ID_ENTRIES
                and eha.ENTRIES_PERMANENT_ATTACHEMENT = 1
            Inner Join ACCOUNTS acc
                On eha.ACCOUNTS_ID_ACCOUNTS = acc.ID_ACCOUNTS
            Inner join CONTACTS_BUSINESS cb_acc
                On acc.CONTACTS_ID_CONTACTS = cb_acc.CONTACTS_ID_CONTACTS
            Left join (
                CONTACTS_ADDRESSES ctadr_acc
                Inner Join DF_COUNTRY dfcntr_acc
                    On ctadr_acc.DF_COUNTRY_ID_DF_COUNTRY = dfcntr_acc.ID_DF_COUNTRY
            ) on acc.CONTACTS_ID_CONTACTS = ctadr_acc.CONTACTS_ID_CONTACTS
            Inner Join ENTITIES entt
                On acc.ENTITIES_ID_ENTITIES = entt.ID_ENTITIES
            Inner join ADMIN_PARAMS admp
                On entt.ADMIN_ID_ADMIN = admp.ID_ADMIN_PARAMS
            Inner join DF_CURRENCIES dfcurnc_ennt
                On admp.DF_CURRENCIES_ID_DF_CURRENCIES = dfcurnc_ennt.ID_DF_CURRENCIES
--             Left Join CONTACTS_BUSINESS cb_entt
--                 On entt.CONT_BUSINESS_ID_CONT_BUSINESS = cb_entt.ID_CONTACTS_BUSINESS
            Left Join ENTRIES_COMPUTED_DATA ecdmtch
                On ent.ID_ENTRIES = ecdmtch.ENTRIES_ID_ENTRIES
                and ecdmtch.ENTRIES_COMPUTED_DATA_KEY = @bcpvpb_forcdMatchKey
                And ecdmtch.ENTRIES_COMPUTED_DATA_EXTID = @bcpvpb_dfMthcSts_CLOSED 
            Left join (
                ENTRIES_MATCHCOD emc
                Inner Join MATCHINGCODES mc
                    On emc.MATCHCOD_ID_MATCHCOD = mc.ID_MATCHINGCODES
            )
                On ent.ID_ENTRIES = emc.ENTRIES_ID_ENTRIES
                and emc.ENTRIES_MATCHCOD_CURRENT = 1

        Where 1 = 1 
            And ent.DF_ENTR_TYPE_ID_DF_ENTR_TYPE in (@bcpvpb_dfEntType_INV,  @bcpvpb_dfEntType_CRN)
            And ent.ENTRIES_ACCOUTING_ACCOUNTS like '411%'
            And (
                    ent.DF_MATCH_STAT_ID_DF_MATCH_STAT in (@bcpvpb_dfMthcSts_OPEN, @bcpvpb_dfMthcSts_PARTIAL)
                    Or ent.ENTRIES_LAST_UPDATE > @bcpvpb_minUpdtDate
                )
            And Coalesce(cb_acc.CONTACTS_BUSINESS_SIRET, '') <> ''
        ;

End $$
Delimiter ;

