Delimiter //
/**
 * Updates the FI_ANA table from views
 *
 * @bcsap_author Armen
 * @bcsap_ticket #5082
 */ 
Drop Procedure if Exists BUSINESS_CALCULATE_SALES_AND_PAYEMENT//
 
Create Procedure BUSINESS_CALCULATE_SALES_AND_PAYEMENT(In reftdt DATE, In toHisto Int, In idaccounts Int unsigned, In identities Int unsigned)
Begin
	slsnpmt_main: Begin
-- 		Declare dtSP_refDT, dtSP_bdt, dtSP_edt Date;
-- 		Declare iSp_tpeEntrInvoice, iSp_tpeEntrCreditNote, iSp_tpeEntrPayment, iSp_tpeEntrCancel Int Unsigned;

        if idaccounts Is null Then
            Set @bcsap_accMinVal = 0;
            Set @bcsap_accMaxVal = 4e7;
        Else
            Set @bcsap_accMinVal = idaccounts;
            Set @bcsap_accMaxVal = idaccounts;
        End if;


        if identities Is null Then
            Set @bcsap_enttMinVal = 0;
            Set @bcsap_enttMaxVal = 1000;
        Else
            Set @bcsap_enttMinVal = identities;
            Set @bcsap_enttMaxVal = identities;
        End if;



	-- #5082
		-- FIANA CA & encaissement
		-- date de ref
		set @bcsap_dtSP_refDT = Coalesce(reftdt, curdate()); -- @bcsap_dtSP_refDT = date de référence
-- 		set @bcsap_dtSP_refDT = curdate(); -- debug
		set @bcsap_dtSP_bdt = date_add(@bcsap_dtSP_refDT, interval 1-day(@bcsap_dtSP_refDT) day); -- @bcsap_dtSP_bdt = 1er jour du mois de référence
		set @bcsap_dtSP_edt = last_day(@bcsap_dtSP_refDT); -- @bcsap_dtSP_edt dernier jour du mois de référence

        -- select @bcsap_dtSP_refDT , @bcsap_dtSP_bdt,  @bcsap_dtSP_edt ;
-- leave slsnpmt_main;
-- 		if @bcsap_dtSP_edt < @bcsap_dtSP_bdt Then
-- 			set @bcsap_dtSP_bdt = date_add(@bcsap_dtSP_edt, interval 1-day(@bcsap_dtSP_edt) day);
-- 		End if;

		set @bcsap_iSP_tpeEntrInvoice = (Select ID_DF_ENTRIES_TYPE From DF_ENTRIES_TYPE Where DF_ENTRIES_TYPE_CODE = 'INV' And DF_ENTRIES_TYPE_ACCOUNTANT = 1);
		set @bcsap_iSP_tpeEntrCreditNote = (Select ID_DF_ENTRIES_TYPE From DF_ENTRIES_TYPE Where DF_ENTRIES_TYPE_CODE = 'CRN' And DF_ENTRIES_TYPE_ACCOUNTANT = 1);
		set @bcsap_iSP_tpeEntrPayment = (Select ID_DF_ENTRIES_TYPE From DF_ENTRIES_TYPE Where DF_ENTRIES_TYPE_CODE = 'PAY' And DF_ENTRIES_TYPE_ACCOUNTANT = 1);
		set @bcsap_iSP_tpeEntrCancel = (Select ID_DF_ENTRIES_TYPE From DF_ENTRIES_TYPE Where DF_ENTRIES_TYPE_CODE = 'CAN' And DF_ENTRIES_TYPE_ACCOUNTANT = 1);

		Drop temporary table if exists TMP_FI_ANA_SALES;

		Create Temporary Table TMP_FI_ANA_SALES (
			ACCOUNTS_ID_ACCOUNTS Int Unsigned Not NULL 
			, TMP_FI_ANA_SALES_SALES Decimal(19,4)
			, TMP_FI_ANA_SALES_UNBOUND_SALES Decimal(19,4)
			, TMP_FI_ANA_SALES_PAYMENT Decimal(19,4)
			, TMP_FI_ANA_SALES_UNBOUND_PAYMENT Decimal(19,4)
			, Primary Key(ACCOUNTS_ID_ACCOUNTS)
		);

		Insert Into TMP_FI_ANA_SALES(
            ACCOUNTS_ID_ACCOUNTS
            , TMP_FI_ANA_SALES_SALES, TMP_FI_ANA_SALES_UNBOUND_SALES
            , TMP_FI_ANA_SALES_PAYMENT, TMP_FI_ANA_SALES_UNBOUND_PAYMENT
        )
		Select 
# 			eha.ACCOUNTS_ID_ACCOUNTS
			acc.ID_ACCOUNTS
			, Sum(
                Case When (ent.DF_ENTR_TYPE_ID_DF_ENTR_TYPE In (@bcsap_iSP_tpeEntrInvoice, @bcsap_iSP_tpeEntrCreditNote)) 
                    Then 
                        -- /* Correction calcul des paiements/ventes #7913 */
--                         If(ent.ENTRIES_DEBIT = 0 And ent.ENTRIES_CREDIT = 0
--                             ,  ent.ENTRIES_ENT_DEBIT - ent.ENTRIES_ENT_CREDIT
--                             ,  ent.ENTRIES_DEBIT - ent.ENTRIES_CREDIT
--                         )
--                         If(ent.ENTRIES_ENT_DEBIT = 0 And ent.ENTRIES_ENT_CREDIT = 0
--                             ,  ent.ENTRIES_DEBIT - ent.ENTRIES_CREDIT
--                             ,  ent.ENTRIES_DEBIT - ent.ENTRIES_CREDIT
--                         )
                        ent.ENTRIES_ENT_DEBIT - ent.ENTRIES_ENT_CREDIT

--                     Else Null 
                    Else 0 
                End
            ) sales
			, Sum( 
                Case 
                    When eha.ENTRIES_PERMANENT_ATTACHEMENT = 0 Then 0
                    When (ent.DF_ENTR_TYPE_ID_DF_ENTR_TYPE In (@bcsap_iSP_tpeEntrInvoice, @bcsap_iSP_tpeEntrCreditNote)) Then 
--                         If(ent.ENTRIES_ENT_DEBIT = 0 And ent.ENTRIES_ENT_CREDIT = 0
--                             ,  ent.ENTRIES_DEBIT - ent.ENTRIES_CREDIT
--                             ,  ent.ENTRIES_ENT_DEBIT - ent.ENTRIES_ENT_CREDIT
--                         )
                        ent.ENTRIES_ENT_DEBIT - ent.ENTRIES_ENT_CREDIT
--                     Else Null 
                    Else 0 
                End
            ) unbound_sales

			, Sum( 
                Case When (ent.DF_ENTR_TYPE_ID_DF_ENTR_TYPE In (@bcsap_iSP_tpeEntrPayment, @bcsap_iSP_tpeEntrCancel)) 
                    Then
                        -- /* Correction calcul des paiements/ventes #7913 */
--                         If(ent.ENTRIES_CREDIT = 0 And ent.ENTRIES_DEBIT = 0
--                             ,  ent.ENTRIES_ENT_CREDIT - ent.ENTRIES_ENT_DEBIT
--                             ,  ent.ENTRIES_CREDIT - ent.ENTRIES_DEBIT
--                         )
-- 						If(ent.ENTRIES_ENT_CREDIT = 0 And ent.ENTRIES_ENT_DEBIT = 0
--                             ,  ent.ENTRIES_CREDIT - ent.ENTRIES_DEBIT
--                             ,  ent.ENTRIES_ENT_CREDIT - ent.ENTRIES_ENT_DEBIT
--                         )
                        ent.ENTRIES_ENT_CREDIT - ent.ENTRIES_ENT_DEBIT
--                     Else Null 
                    Else 0 
                End
            ) payement
			, Sum( 
                Case When eha.ENTRIES_PERMANENT_ATTACHEMENT = 0 Then 0 
                    When (ent.DF_ENTR_TYPE_ID_DF_ENTR_TYPE In (@bcsap_iSP_tpeEntrPayment, @bcsap_iSP_tpeEntrCancel)) Then
-- 						If(ent.ENTRIES_ENT_CREDIT = 0 And ent.ENTRIES_ENT_DEBIT = 0
--                             ,  ent.ENTRIES_CREDIT - ent.ENTRIES_DEBIT
--                             ,  ent.ENTRIES_ENT_CREDIT - ent.ENTRIES_ENT_DEBIT
--                         )
                        ent.ENTRIES_ENT_CREDIT - ent.ENTRIES_ENT_DEBIT
--                     Else Null 
                    Else 0 
                End
            ) unbound_payement
		From ENTRIES ent
			Inner Join ENTRIES_has_ACCOUNTS eha
				ON ent.ID_ENTRIES = eha.ENTRIES_ID_ENTRIES
		    Inner Join ACCOUNTS acc
		        On eha.ACCOUNTS_ID_ACCOUNTS = acc.ID_ACCOUNTS
		Where 
            ent.DF_ENTR_TYPE_ID_DF_ENTR_TYPE In (
                    @bcsap_iSP_tpeEntrInvoice, @bcsap_iSP_tpeEntrCreditNote
                    , @bcsap_iSP_tpeEntrPayment, @bcsap_iSP_tpeEntrCancel
                )
		-- vue oralement avec olivier le 24/07/2015
		-- il faut utiliser la date de l'ecriture et non la date d'échéance
			And ent.ENTRIES_DATE >= @bcsap_dtSP_bdt 
            And ent.ENTRIES_DATE <= @bcsap_dtSP_edt
# 			And eha.ACCOUNTS_ID_ACCOUNTS Between @bcsap_accMinVal And @bcsap_accMaxVal
			And acc.ID_ACCOUNTS Between @bcsap_accMinVal And @bcsap_accMaxVal
			And acc.ENTITIES_ID_ENTITIES Between @bcsap_enttMinVal And @bcsap_enttMaxVal
		
		Group By
            acc.ID_ACCOUNTS
# 		            eha.ACCOUNTS_ID_ACCOUNTS
		;

		If Coalesce(toHisto, 0) <> 0 Then
			-- maj de la table fiana_histo
			Update
				FI_ANA_HISTO
                Inner Join ACCOUNTS acc
                    On FI_ANA_HISTO.ACCOUNTS_ID_ACCOUNTS = acc.ID_ACCOUNTS
				Left Join TMP_FI_ANA_SALES  TSL
					On FI_ANA_HISTO.ACCOUNTS_ID_ACCOUNTS = TSL.ACCOUNTS_ID_ACCOUNTS
			Set 
				FI_ANA_HISTO.FI_ANA_SALES = Coalesce(TSL.TMP_FI_ANA_SALES_SALES, 0)
				, FI_ANA_HISTO.FI_ANA_UNBOUND_SALES = Coalesce(TSL.TMP_FI_ANA_UNBOUND_SALES_SALES, 0)
				, FI_ANA_HISTO.FI_ANA_PAYMENT = Coalesce(TSL.TMP_FI_ANA_SALES_PAYMENT, 0)
				, FI_ANA_HISTO.FI_ANA_UNBOUND_PAYMENT = Coalesce(TSL.TMP_FI_ANA_UNBOUND_SALES_PAYMENT, 0)
			Where
				FI_ANA_HISTO.FI_ANA_HISTO_DATE = @bcsap_dtSP_refDT
                And acc.ID_ACCOUNTS Between @bcsap_accMinVal And @bcsap_accMaxVal
                And acc.ENTITIES_ID_ENTITIES Between @bcsap_enttMinVal And @bcsap_enttMaxVal
			;
		Else
	--         maj de fiana
			Update
				FI_ANA
                Inner Join ACCOUNTS acc
                    On FI_ANA.ACCOUNTS_ID_ACCOUNTS = acc.ID_ACCOUNTS
				Left Join TMP_FI_ANA_SALES  TSL
					On FI_ANA.ACCOUNTS_ID_ACCOUNTS = TSL.ACCOUNTS_ID_ACCOUNTS
			Set 
				FI_ANA.FI_ANA_SALES = Coalesce(TSL.TMP_FI_ANA_SALES_SALES, 0)
				, FI_ANA.FI_ANA_UNBOUND_SALES = Coalesce(TSL.TMP_FI_ANA_SALES_UNBOUND_SALES, 0)
				, FI_ANA.FI_ANA_PAYMENT = Coalesce(TSL.TMP_FI_ANA_SALES_PAYMENT, 0)
				, FI_ANA.FI_ANA_UNBOUND_PAYMENT = Coalesce(TSL.TMP_FI_ANA_SALES_UNBOUND_PAYMENT, 0)
			Where
                acc.ID_ACCOUNTS Between @bcsap_accMinVal And @bcsap_accMaxVal
                And acc.ENTITIES_ID_ENTITIES Between @bcsap_enttMinVal And @bcsap_enttMaxVal
			;
		End if;
		if @bcsap_debugMode <> 1 Then
			Drop temporary table if exists TMP_FI_ANA_SALES;
		End if;
	end;
End //
Delimiter ;