Delimiter //
/**
 * Author:  armen
 * Created: 24 janv. 2017
 * @see #7961
 */
Drop Procedure if Exists BUSINESS_FI_ANA_UPDATE_DELIVRED_AND_ORDER//
 
Create Procedure BUSINESS_FI_ANA_UPDATE_DELIVRED_AND_ORDER(In reftdt DATE, In idaccounts Int unsigned, In identities Int unsigned)
Begin

	bizfidelorder_main: Begin
		bizfidelorder_init: begin
			set @bizfidelorder_refDT = Coalesce(reftdt, AMO_BAL_REFDATE()); 
# 			set @bizfidelorder_refDT = curdate(); -- debug
			set @bizfidelorder_today = curdate();


			set @bizfidelorder_bdt = date_add(@bizfidelorder_refDT, interval 1-day(@bizfidelorder_refDT) day); -- @bcsap_dtSP_bdt = 1er jour du mois de référence
			if  @bizfidelorder_refDT < @bizfidelorder_today Then
			    -- retro calcul
			    Set @bizfidelorder_useDebitAndCredit = 1;
                set @bizfidelorder_edt = last_day(@bizfidelorder_refDT); -- @bcsap_dtSP_edt dernier jour du mois de référence
            else
                -- calcul quotidien
			    Set @bizfidelorder_useDebitAndCredit = 0;
                set @bizfidelorder_edt = @bizfidelorder_refDT;
            end if ;

# 			Set @bizfidelorder_useDebitAndCredit = (Case When @bizfidelorder_refDT < curdate() Then 1 Else 0 End);

-- 			set @bizfidelorder_accFilter = ifnull(idaccounts, '%');
            if idaccounts Is null Then
                Set @bizfidelorder_accMinVal = 0;
                Set @bizfidelorder_accMaxVal = 4e7;
            Else
                Set @bizfidelorder_accMinVal = idaccounts;
                Set @bizfidelorder_accMaxVal = idaccounts;
            End if;

            if identities Is null Then
                Set @bizfidelorder_enttMinVal = 0;
                Set @bizfidelorder_enttMaxVal = 1000;
            Else
                Set @bizfidelorder_enttMinVal = identities;
                Set @bizfidelorder_enttMaxVal = identities;
            End if;

			set @bizfidelorder_MATCH_OPEN = (Select ID_DF_MATCHINGCODE_STATE From DF_MATCHINGCODE_STATE where DF_MATCHINGCODE_STATE_CODE = 'OPEN ENTRY' Limit 1);
			set @bizfidelorder_MATCH_PARTIAL = (Select ID_DF_MATCHINGCODE_STATE From DF_MATCHINGCODE_STATE where DF_MATCHINGCODE_STATE_CODE = 'PARTIAL MATCHING CODE' Limit 1);
			set @bizfidelorder_MATCH_CLOSED = (Select ID_DF_MATCHINGCODE_STATE From DF_MATCHINGCODE_STATE where DF_MATCHINGCODE_STATE_CODE = 'CLOSED ENTRY' Limit 1);


			set @bizfidelorder_SNDBXSTATE_DOVALDTE = (Select ID_DF_SANDBOX_STATE from DF_SANDBOX_STATE Where DF_SANDBOX_STATE_CODE = 'DOVALIDATE' Limit 1) ;
            set @bizfidelorder_SNDBXSTATE_ACTIVE = (Select ID_DF_SANDBOX_STATE from DF_SANDBOX_STATE Where DF_SANDBOX_STATE_CODE = 'ACTIVE' Limit 1) ;



			set @bizfidelorder_ENTR_TYPE_ORD = (Select ID_DF_ENTRIES_TYPE From DF_ENTRIES_TYPE where DF_ENTRIES_TYPE_CODE = 'ORD' Limit 1);
			set @bizfidelorder_ENTR_TYPE_DLV = (Select ID_DF_ENTRIES_TYPE From DF_ENTRIES_TYPE where DF_ENTRIES_TYPE_CODE = 'DLV' Limit 1);
			set @bizfidelorder_ENTR_TYPE_BKD = (Select ID_DF_ENTRIES_TYPE From DF_ENTRIES_TYPE where DF_ENTRIES_TYPE_CODE = 'BKD' Limit 1);

		end ;
		
		bizfidelorder_table: Begin
			Drop Temporary Table if exists tmp_delivred_an_order;
			Create Temporary Table tmp_delivred_an_order (
                        ACCOUNTS_ID_ACCOUNTS Int UNSIGNED Not Null
                        , PENDING_ORDER Decimal(19, 4)
                        , UNBOUND_PENDING_ORDER Decimal(19, 4)
                        , DELIVERY_TOBEINVOICED Decimal(19, 4)
                        , UNBOUND_DELIVERY_TOBEINVOICED Decimal(19, 4)
                        , BANK_DRAFT Decimal(19, 4)
                        , UNBOUND_BANK_DRAFT Decimal(19, 4)
                        , BLOCKED_ORDER Decimal(19, 4)
                        , UNBOUND_BLOCKED_ORDER Decimal(19, 4)
                        , BLOCKED_DELIVERY_TOBEINVOICED Decimal(19, 4)
                        , UNBOUND_BLOCKED_DELIVERY_TOBEINVOICED Decimal(19, 4)

                        , Index (ACCOUNTS_ID_ACCOUNTS)
			        )
                ;

		    Drop Temporary Table if exists tmp_delivred_an_order_sel_tent;
            Create Temporary Table tmp_delivred_an_order_sel_tent (
                        ENTRIES_ID_ENTRIES Int UNSIGNED Not Null
                        , IS_BLOQUED SmallInt Not Null Default 0
                        , IS_CLOSED SmallInt Not Null Default 0
                        , DATE_CLOSE Date
                        , ENTRIES_AMOUNT Decimal(19, 4)
                        , Index (ENTRIES_ID_ENTRIES)
                    )
                ;

		end;
		bizfidelorder_compute: Begin
			Insert Into tmp_delivred_an_order_sel_tent (
                      ENTRIES_ID_ENTRIES, IS_BLOQUED
                      , IS_CLOSED, DATE_CLOSE
                      , ENTRIES_AMOUNT
			    )
           -- explain
            Select ent.ID_ENTRIES
                , Case
                    When Coalesce(rclm.RECLAIMS_NB_BLOCKED, 0) >0 Then 1
                    When Coalesce(dfes.DF_ENTRIES_STATUT_INDICATOR, 0) = 8 Then 1
                    Else 0
                 End IS_BLOQUED
                , Case
                    When ent.DF_MATCH_STAT_ID_DF_MATCH_STAT = @bizfidelorder_MATCH_CLOSED Then 1
                        Else 0
                    End IS_CLOSED
#                 , Coalesce(MTC.MATCHINGCODES_DATE, ENMTC.ENTRIES_COMPUTED_DATA_DATE, '2080-01-01') DATE_CLOSE
			    , Case
			        When ent.DF_MATCH_STAT_ID_DF_MATCH_STAT In(@bizfidelorder_MATCH_OPEN, @bizfidelorder_MATCH_PARTIAL)
			            -- non lettré  on affecte une date dans le futur
			            Then '2080-01-01'
			        When MTC.MATCHINGCODES_DATE Is Not Null
			            -- date de lettrage non vide
                        Then MTC.MATCHINGCODES_DATE
			        When ENMTC.ENTRIES_COMPUTED_DATA_DATE Is Not Null
			            -- date de lettrage forcé non null
			            Then ENMTC.ENTRIES_COMPUTED_DATA_DATE
			        Else
			            -- lettré sans lettrage si date de lettrage forcé
			            -- on utilise la date d échéance comme date de lettrage par defaut
			            ent.ENTRIES_DUE_DATE
			    End DATE_CLOSE
			    , Case
			        -- calcul au quotidien : on prend le montant restant du
			        When (
			                @bizfidelorder_useDebitAndCredit=0
                            And ent.ENTRIES_ENT_BALANCE <> 0
                        ) Then ent.ENTRIES_ENT_BALANCE
			        -- retor calcul on prend les initiaux
			        When (
			                @bizfidelorder_useDebitAndCredit=1
                            And  (ent.ENTRIES_ENT_DEBIT - ent.ENTRIES_ENT_CREDIT)
                        ) Then ent.ENTRIES_ENT_DEBIT - ent.ENTRIES_ENT_CREDIT

                    -- certain clients on des donnés initial vide, gestion de ces cas
			        When  ent.ENTRIES_ENT_BALANCE <> 0
                        Then ent.ENTRIES_ENT_BALANCE
                    Else
                        ent.ENTRIES_ENT_DEBIT - ent.ENTRIES_ENT_CREDIT
			    End ENTRIES_AMOUNT

            From ENTRIES ent
			    Left Join DF_ENTRIES_STATUT dfes
                    On ent.DF_ENTR_STT_ID_DF_ENTR_STT = dfes.ID_DF_ENTRIES_STATUT
			    Left Join ENTRIES_NB_RECLAIMS_VIEW_FOR_VIEW rclm
                    ON ent.ID_ENTRIES = rclm.ENTRIES_ID_ENTRIES
			    Left Join ENTRIES_MATCHCOD emc
                    On ent.ID_ENTRIES = emc.ENTRIES_ID_ENTRIES
                    And emc.ENTRIES_MATCHCOD_CURRENT = 1
                Left Join MATCHINGCODES MTC
                    On emc.MATCHCOD_ID_MATCHCOD = MTC.ID_MATCHINGCODES
#                         And MTC.MATCHINGCODES_DATE <= @bizfidelorder_refDT
                Left Join ENTRIES_COMPUTED_DATA ENMTC
                    On MTC.ID_MATCHINGCODES = ENMTC.ENTRIES_COMPUTED_DATA_EXTID
                    And ENMTC.ENTRIES_COMPUTED_DATA_KEY = 'FORCEDMATCHSTS'
			Where
                ent.DF_ENTR_TYPE_ID_DF_ENTR_TYPE In (@bizfidelorder_ENTR_TYPE_ORD, @bizfidelorder_ENTR_TYPE_DLV, @bizfidelorder_ENTR_TYPE_BKD)
#                 And ent.DF_MATCH_STAT_ID_DF_MATCH_STAT In (@bizfidelorder_MATCH_OPEN,  @bizfidelorder_MATCH_PARTIAL )
#                -- vu avec OB, les écritures antidatées ne doivent pas être prise en compte
#                 And ent.ENTRIES_DATE <= @bizfidelorder_refDT
                And ent.ENTRIES_DATE BETWEEN @bizfidelorder_bdt And @bizfidelorder_edt
			    And ent.ID_ENTRIES In (
                    Select eha.ENTRIES_ID_ENTRIES
			        From ENTRIES_has_ACCOUNTS eha
                         Inner Join ACCOUNTS acc
                                    On eha.ACCOUNTS_ID_ACCOUNTS = acc.ID_ACCOUNTS
			        Where
			            ent.ID_ENTRIES = eha.ENTRIES_ID_ENTRIES
			            And acc.ID_ACCOUNTS Between @bizfidelorder_accMinVal And @bizfidelorder_accMaxVal
# 			            And eha.ACCOUNTS_ID_ACCOUNTS Between @bizfidelorder_accMinVal And @bizfidelorder_accMaxVal
			            And acc.ENTITIES_ID_ENTITIES Between @bizfidelorder_enttMinVal And @bizfidelorder_enttMaxVal
			        
			    )
            ;


# 			Create Temporary Table tmp_delivred_an_order
# 				AS
            Insert Into tmp_delivred_an_order (
                ACCOUNTS_ID_ACCOUNTS
                , PENDING_ORDER, UNBOUND_PENDING_ORDER
                , DELIVERY_TOBEINVOICED, UNBOUND_DELIVERY_TOBEINVOICED
                , BANK_DRAFT, UNBOUND_BANK_DRAFT
                , BLOCKED_ORDER, UNBOUND_BLOCKED_ORDER
                , BLOCKED_DELIVERY_TOBEINVOICED , UNBOUND_BLOCKED_DELIVERY_TOBEINVOICED
            )
            -- explain
			Select
				 acc.ID_ACCOUNTS ACCOUNTS_ID_ACCOUNTS
				 , Sum(
                    -- somme des "commandes en cours"
				     Case
                        -- exclusion des "EAR" et "livrées à facturer"
				        When (ent.DF_ENTR_TYPE_ID_DF_ENTR_TYPE In (@bizfidelorder_ENTR_TYPE_DLV, @bizfidelorder_ENTR_TYPE_BKD) )
				            Then 0

				        -- exclusion des écritures lettrées à la date de référence
				        When (
                                tmp_sel_ent.IS_CLOSED = 1
				                And tmp_sel_ent.DATE_CLOSE > @bizfidelorder_refDT
				            ) Then  0

                        Else
                        -- il reste les "commandes en cours" ouvertes ou non lettrées à la date de ref
                            tmp_sel_ent.ENTRIES_AMOUNT
				     End
				 ) PENDING_ORDER

				 , Sum(
                    -- somme des "commandes en cours" NON consolidées
				     Case
				         -- exclusion des écritures consolidées
                        When eha.ENTRIES_PERMANENT_ATTACHEMENT = 0 Then 0
                        -- exclusion des "EAR" et "livrées à facturer"
				        When (ent.DF_ENTR_TYPE_ID_DF_ENTR_TYPE In (@bizfidelorder_ENTR_TYPE_DLV, @bizfidelorder_ENTR_TYPE_BKD) )
				            Then 0

				        -- exclusion des écritures lettrées à la date de références
				        When (
                                tmp_sel_ent.IS_CLOSED = 1
				                And tmp_sel_ent.DATE_CLOSE > @bizfidelorder_refDT
				            ) Then  0

				        Else
                        -- Il reste les "commande en cours" non consolidées, ouvertes ou non lettrées à la date de ref
                            tmp_sel_ent.ENTRIES_AMOUNT
				     End
				 ) UNBOUND_PENDING_ORDER
				, Sum(
                    -- somme des "Livrées à facturer"
				    Case
                        -- exclusion des "EAR" et "Commandes en cours"
                        When (ent.DF_ENTR_TYPE_ID_DF_ENTR_TYPE In (@bizfidelorder_ENTR_TYPE_ORD, @bizfidelorder_ENTR_TYPE_BKD) )
                            Then 0

				        -- exclusion des écritures lettrées à la date de référence
				        When (
                                tmp_sel_ent.IS_CLOSED = 1
                                And tmp_sel_ent.DATE_CLOSE > @bizfidelorder_refDT
                            ) Then  0

                        Else
                        -- Il reste les "Livrées à facturer" non consolidées, ouvertes ou non lettrées à la date de ref
                            tmp_sel_ent.ENTRIES_AMOUNT
                    End
				) As DELIVERY_TOBEINVOICED
				, Sum(
				    -- somme des "Livrées à facturer" NON consolidées
				    Case
				        -- exclusion des écritures consolidées
                        When eha.ENTRIES_PERMANENT_ATTACHEMENT = 0 Then 0

				        -- exclusion des "EAR" et "Commandes en cours"
                        When (ent.DF_ENTR_TYPE_ID_DF_ENTR_TYPE In (@bizfidelorder_ENTR_TYPE_ORD, @bizfidelorder_ENTR_TYPE_BKD) )
                            Then 0
				        
				        -- exclusion des écritures lettrées à la date de référence
                        When (
                                tmp_sel_ent.IS_CLOSED = 1
                                And tmp_sel_ent.DATE_CLOSE > @bizfidelorder_refDT
                            ) Then  0

                        Else
                        -- Il reste les "Livrées à facturer" non consolidées, ouvertes ou non lettrées à la date de ref
                            tmp_sel_ent.ENTRIES_AMOUNT
                    End
				) As UNBOUND_DELIVERY_TOBEINVOICED
				, Sum(
				    -- somme des "effets à recevoir" (EAR)
                        Case
                        -- exclusion des "commandes en cours" et "livrées à facturer"
				        When (ent.DF_ENTR_TYPE_ID_DF_ENTR_TYPE In (@bizfidelorder_ENTR_TYPE_ORD, @bizfidelorder_ENTR_TYPE_DLV) )
				            Then 0

				        -- exclusion des écritures lettrées à la date de référence
				        When (
                                tmp_sel_ent.IS_CLOSED = 1
				                And tmp_sel_ent.DATE_CLOSE > @bizfidelorder_refDT
				            ) Then  0

                        Else
                        -- il reste les "EAR" ouverts ou non lettrés à la date de ref
                            tmp_sel_ent.ENTRIES_AMOUNT
				     End

				) As BANK_DRAFT
				, Sum(
				    -- somme des "effets à recevoir" (EAR) NON consolidées
                    Case
                        -- exclusion des écritures consolidées
                        When eha.ENTRIES_PERMANENT_ATTACHEMENT = 0 Then 0
                        -- exclusion des "commandes en cours" et "livrées à facturer"
                        When (ent.DF_ENTR_TYPE_ID_DF_ENTR_TYPE In (@bizfidelorder_ENTR_TYPE_ORD, @bizfidelorder_ENTR_TYPE_DLV) )
                            Then 0

                        -- exclusion des écritures lettrées à la date de référence
                        When (
                                tmp_sel_ent.IS_CLOSED = 1
                                And tmp_sel_ent.DATE_CLOSE > @bizfidelorder_refDT
                            ) Then  0

                        Else
                        -- il reste les "EAR" NON consolidés, ouverts ou non lettrés à la date de ref
                            tmp_sel_ent.ENTRIES_AMOUNT
                     End
				) As UNBOUND_BANK_DRAFT
				, Sum(
                    -- somme des "commandes en cours" bloquées
                    Case
                        -- exclusion des "EAR" et "livrées à facturer"
				        When (ent.DF_ENTR_TYPE_ID_DF_ENTR_TYPE In (@bizfidelorder_ENTR_TYPE_DLV, @bizfidelorder_ENTR_TYPE_BKD) )
				            Then 0
				        -- exclusion des écritures lettrées à la date de référence
				        When (
                                tmp_sel_ent.IS_CLOSED = 1
				                And tmp_sel_ent.DATE_CLOSE > @bizfidelorder_refDT
				            ) Then  0

                        -- exclusion des écritures non bloquées
                        When tmp_sel_ent.IS_BLOQUED = 0 Then 0
                        Else
                        -- il reste les "commandes en cours" bloquées, ouvertes ou non lettrées à la date de ref
                            tmp_sel_ent.ENTRIES_AMOUNT
				     End
				) As BLOCKED_ORDER
				, Sum(
				    -- somme des "commandes en cours" bloquées NON consolidées
                    Case
                        -- exclusion des écritures consolidées
                        When eha.ENTRIES_PERMANENT_ATTACHEMENT = 0 Then 0
                        -- exclusion des "EAR" et "livrées à facturer"
                        When (ent.DF_ENTR_TYPE_ID_DF_ENTR_TYPE In (@bizfidelorder_ENTR_TYPE_DLV, @bizfidelorder_ENTR_TYPE_BKD) )
                            Then 0
                        -- exclusion des écritures lettrées à la date de référence
                        When (
                                tmp_sel_ent.IS_CLOSED = 1
                                And tmp_sel_ent.DATE_CLOSE > @bizfidelorder_refDT
                            ) Then  0

                        -- exclusion des écritures non bloquées
                        When tmp_sel_ent.IS_BLOQUED = 0 Then 0
                        Else
                        -- il reste les "commandes en cours" non consolidées, non bloquées, ouvertes ou non lettrées à la date de ref
                            tmp_sel_ent.ENTRIES_AMOUNT
                    End
				) As UNBOUND_BLOCKED_ORDER
				, Sum(
				    -- somme des "Livrées à facturer" bloquées
                    Case
                        -- exclusion des "EAR" et "Commandes en cours"
                        When (ent.DF_ENTR_TYPE_ID_DF_ENTR_TYPE In (@bizfidelorder_ENTR_TYPE_ORD, @bizfidelorder_ENTR_TYPE_BKD) )
                            Then 0
                        -- exclusion des écritures lettrées à la date de référence
                        When (
                                tmp_sel_ent.IS_CLOSED = 1
                                And tmp_sel_ent.DATE_CLOSE > @bizfidelorder_refDT
                            ) Then  0
                        -- exclusion des écritures non bloquées
                        When tmp_sel_ent.IS_BLOQUED = 0 Then 0
                        Else
                        -- Il reste les "Livrées à facturer" bloqués, ouvertes ou non lettrées à la date de ref
                            tmp_sel_ent.ENTRIES_AMOUNT
                    End
				) As BLOCKED_DELIVERY_TOBEINVOICED
				, Sum(
				    -- somme des "Livrées à facturer" bloquées NON Consolidées
                    Case
                        -- exclusion des écritures consolidées
                        When eha.ENTRIES_PERMANENT_ATTACHEMENT = 0 Then 0
                        -- exclusion des "EAR" et "Commandes en cours"
                        When (ent.DF_ENTR_TYPE_ID_DF_ENTR_TYPE In (@bizfidelorder_ENTR_TYPE_ORD, @bizfidelorder_ENTR_TYPE_BKD) )
                            Then 0
                        -- exclusion des écritures lettrées à la date de référence
                        When (
                                tmp_sel_ent.IS_CLOSED = 1
                                And tmp_sel_ent.DATE_CLOSE > @bizfidelorder_refDT
                            ) Then  0
                        -- exclusion des écritures non bloquées
                        When tmp_sel_ent.IS_BLOQUED = 0 Then 0
                        Else
                        -- Il reste les "Livrées à facturer" bloquées, ouvertes ou non lettrées à la date de ref
                            tmp_sel_ent.ENTRIES_AMOUNT
                    End
				) As UNBOUND_BLOCKED_DELIVERY_TOBEINVOICED
            -- explain select * --  select count(*)
			From ACCOUNTS acc
			    Left Join (
			        ENTRIES_has_ACCOUNTS eha
			        Inner Join tmp_delivred_an_order_sel_tent tmp_sel_ent
			            On eha.ENTRIES_ID_ENTRIES = tmp_sel_ent.ENTRIES_ID_ENTRIES
			        Inner Join ENTRIES ent
			            On tmp_sel_ent.ENTRIES_ID_ENTRIES = ent.ID_ENTRIES
                )  On acc.ID_ACCOUNTS = eha.ACCOUNTS_ID_ACCOUNTS
			Where
                 acc.ID_ACCOUNTS Between @bizfidelorder_accMinVal And @bizfidelorder_accMaxVal
                And acc.ENTITIES_ID_ENTITIES Between @bizfidelorder_enttMinVal And @bizfidelorder_enttMaxVal
			Group By
					acc.ID_ACCOUNTS
			;
		end;


		bizfidelorder_update_fiana: Begin	
# 			Alter Table tmp_delivred_an_order
# 					add index ix_tmp_delivred_an_order(ACCOUNTS_ID_ACCOUNTS)
# 				;
#
			Update
				FI_ANA
					Inner Join tmp_delivred_an_order src
						On FI_ANA.ACCOUNTS_ID_ACCOUNTS = src.ACCOUNTS_ID_ACCOUNTS
				Set
					FI_ANA.FI_ANA_CURRENT_ORDER_AMOUNT = Coalesce(src.PENDING_ORDER, 0)
					, FI_ANA.FI_ANA_UNBOUND_CURRENT_ORDER_AMOUNT = Coalesce(src.UNBOUND_PENDING_ORDER, 0)
					, FI_ANA.FI_ANA_BLOCKED_ORDER_AMOUNT = Coalesce(src.BLOCKED_ORDER, 0)
					, FI_ANA.FI_ANA_UNBOUND_BLOCKED_ORDER_AMOUNT = Coalesce(src.UNBOUND_BLOCKED_ORDER, 0)
					, FI_ANA.FI_ANA_DELIVERY_TOBEINVOICED = Coalesce(src.DELIVERY_TOBEINVOICED, 0)
					, FI_ANA.FI_ANA_UNBOUND_DELIVERY_TOBEINVOICED = Coalesce(src.UNBOUND_DELIVERY_TOBEINVOICED, 0)
					, FI_ANA.FI_ANA_BLOCKED_DELIVERY_TOBEINVOICED = Coalesce(src.BLOCKED_DELIVERY_TOBEINVOICED, 0)
					, FI_ANA.FI_ANA_UNBOUND_BLOCKED_DELIVERY_TOBEINVOICED = Coalesce(src.UNBOUND_BLOCKED_DELIVERY_TOBEINVOICED, 0)
					, FI_ANA.FI_ANA_BANK_DRAFT = Coalesce(src.BANK_DRAFT, 0)
					, FI_ANA.FI_ANA_UNBOUND_BANK_DRAFT = Coalesce(src.UNBOUND_BANK_DRAFT, 0)
				;

		end;

		bizfidelorder_clear: Begin

			Drop Temporary Table if exists tmp_delivred_an_order_sel_tent;
			Drop Temporary Table if exists tmp_delivred_an_order;

		end;

	end;

End //
Delimiter ;