Delimiter $$
/* 
 * Copyright (C) AMO & Soft - All Rights Reserved
 * Unauthorized copying of this file, via any medium is strictly prohibited.
 * Proprietary and confidential.
 */
/**
 * Update accounts without entries (ex: after entries deletion)
 * Author:  Benoit DD <b.danquechindorval@amo-soft.com>
 * Created: 2 mai 2016
 */

Drop Procedure If Exists BUSINESS_FI_ANA_UPDATE_EMPTY_ACCOUNTS $$
Create Procedure BUSINESS_FI_ANA_UPDATE_EMPTY_ACCOUNTS(In idaccounts Int Unsigned, In identities Int unsigned)
Begin

-- 	Set @accFilter = Ifnull(idaccounts, '%');

    if idaccounts Is null Then
        Set @bfuea_accMinVal = 0;
        Set @bfuea_accMaxVal = 4e7;
    Else
        Set @bfuea_accMinVal = idaccounts;
        Set @bfuea_accMaxVal = idaccounts;
    End if;

    if identities Is null Then
        Set @bfuea_enttMinVal = 0;
        Set @bfuea_enttMaxVal = 1000;
    Else
        Set @bfuea_enttMinVal = identities;
        Set @bfuea_enttMaxVal = identities;
    End if;

    fi_ana_update_empty_accounts: Begin

        -- Temporary table of empty accounts wich have fiana
		Drop Temporary Table If Exists TMP_FIANA_EMPTY_ACCOUNTS;
		Create Temporary Table TMP_FIANA_EMPTY_ACCOUNTS (
                    ACCOUNTS_ID_ACCOUNTS Int Unsigned 
                    , Index (ACCOUNTS_ID_ACCOUNTS )
                ) 
            ;
        Insert Into TMP_FIANA_EMPTY_ACCOUNTS(
                ACCOUNTS_ID_ACCOUNTS
            )
		Select 
			ACC.ID_ACCOUNTS
		From
			ACCOUNTS ACC
			Inner Join FI_ANA
				On ACC.ID_ACCOUNTS = FI_ANA.ACCOUNTS_ID_ACCOUNTS
--             Left Join ENTRIES_has_ACCOUNTS
--                 On ACC.ID_ACCOUNTS = ENTRIES_has_ACCOUNTS.ACCOUNTS_ID_ACCOUNTS
		Where 
            ACC.ID_ACCOUNTS Between @bfuea_accMinVal And @bfuea_accMaxVal
            And ACC.ENTITIES_ID_ENTITIES Between @bfuea_enttMinVal And @bfuea_enttMaxVal
--             ACC.ID_ACCOUNTS Like @accFilter
            And Not Exists (
                Select ENTRIES_has_ACCOUNTS.ACCOUNTS_ID_ACCOUNTS
                From ENTRIES_has_ACCOUNTS
                Where
                    ACC.ID_ACCOUNTS = ENTRIES_has_ACCOUNTS.ACCOUNTS_ID_ACCOUNTS
                Limit 1
            )
--             And ENTRIES_has_ACCOUNTS.ENTRIES_ID_ENTRIES Is Null
		;

		-- Update accounts
		Update
			FI_ANA
        Set
            FI_ANA_NOT_OVERDUE = 0
            , FI_ANA_AGE_PORTFOLIO_PERIOD1 = 0
            , FI_ANA_AGE_PORTFOLIO_PERIOD2 = 0
            , FI_ANA_AGE_PORTFOLIO_PERIOD3 = 0
            , FI_ANA_AGE_PORTFOLIO_PERIOD4 = 0
            , FI_ANA_AGE_PORTFOLIO_PERIOD5 = 0
            , FI_ANA_AGE_PORTFOLIO_PERIOD6 = 0
            , FI_ANA_AGE_PORTFOLIO_PERIOD7 = 0
            , FI_ANA_AGE_PORTFOLIO_PERIOD8 = 0
            , FI_ANA_OUTSTANDING = 0
            , FI_ANA_OVERDUE = 0
            , FI_ANA_FROZEN_AMOUNT = 0
            , FI_ANA_DUE = 0
            , FI_ANA_AGE_ACCOUNT_PERIOD1 = 0
            , FI_ANA_AGE_ACCOUNT_PERIOD2 = 0
            , FI_ANA_AGE_ACCOUNT_PERIOD3 = 0
            , FI_ANA_AGE_ACCOUNT_PERIOD4 = 0
            , FI_ANA_ACD = 0
            , FI_ANA_ACD_LASTUPDAT = null
            , FI_ANA_BPDSO = 0
            , FI_ANA_BPDSO_LASTUPDAT = null
            , FI_ANA_AGE_CUSTMMDE_PERIOD1 = 0
            , FI_ANA_AGE_CUSTMMDE_PERIOD2 = 0
            , FI_ANA_AGE_CUSTMMDE_PERIOD3 = 0
            , FI_ANA_AGE_CUSTMMDE_PERIOD4 = 0
            , FI_ANA_AGE_CUSTMMDE_DEBIT = 0
            , FI_ANA_AGE_CUSTMMDE_CREDIT = 0
        Where
#             Exists (
            FI_ANA.ACCOUNTS_ID_ACCOUNTS In (
                Select TMP_FIANA_EMPTY_ACCOUNTS.ACCOUNTS_ID_ACCOUNTS
                From TMP_FIANA_EMPTY_ACCOUNTS
                Where FI_ANA.ACCOUNTS_ID_ACCOUNTS  = TMP_FIANA_EMPTY_ACCOUNTS.ACCOUNTS_ID_ACCOUNTS
                -- limit 1
            )
		;

        -- Clean
		Drop Temporary table if exists TMP_FIANA_EMPTY_ACCOUNTS;

    End;

End $$
Delimiter ;