
/* 
    Export des Bons de réception vers Esker 
    - Spec 5094 - EAUXM

    Il faut envoyer les réceptions avec les mêmes conditions que les commandes exportées
*/
/*<DELIMITEUR>,</DELIMITEUR>*/
/*<HEADER>0</HEADER>*/

/* Drop tmp tables */
if exists (select * from sysobjects where name='xxTABLExx' and xtype='U') drop table xxTABLExx
if exists (select * from sysobjects where name='xxTABLE2xx' and xtype='U') drop table xxTABLE2xx



/*<SEPARATEUR>*/
/* REQ_1 : 
    BR_RECEP, 
    dans la table 1 (xxTABLExx) 
*/

declare @v_DateDuJour char(8) = convert(char(8), GETDATE(), 112)    -- YYYYMMDD

;WITH CTE_FL_AMOUNT AS    -- La somme du montant ainsi que la quantité facturée des lignes de réceptions
    (   select  fl.NUM_BR, 
                fl.NUM_LIBR,
                sum(fl.MONTANT) AS MONTANT,
                sum(fl.QUANTITE) AS QUANTITE
        from FAC_LIG fl with(nolock)
        group by fl.NUM_BR, fl.NUM_LIBR )
select

    replicate(' ',  2000)                                           as CHAMP,
    replace(bl.CODE_STE,'"','\"')                                   as AZ32_CompanyCode__,
    cast(bl.NUM_CDE as varchar)                                     as AZ32_OrderNumber__,
    cast(bl.NO_PO_CDE as varchar)                                   as AZ32_ItemNumber__,
    cast(bl.NUM_BR as varchar)                                      as AZ32_GoodsReceipt__,
    replace(br.BON_LIVR,'"','\"')                                   as AZ32_DeliveryNote__,
	substring(br.DATE_VAL,1,4) + '-' + substring(br.DATE_VAL,5,2) + '-' + substring(br.DATE_VAL,7,2)    as AZ32_DeliveryDate__,  -- YYYYMMDD devient YYYY-MM-DD
    case when isnull(bl.MNT_REC, 0) = 0 then cast(isnull(bl.QTE_ACCEP, 0) * cl.PU * (1 - cl.REMISE/100) as decimal(15,2)) else cast(bl.MNT_REC as decimal(15,2)) end        as AZ32_Amount__,
    cast(isnull(bl.QTE_ACCEP, 0) as decimal(15,3))                  as AZ32_Quantity__,
    cast(isnull(fla.MONTANT, 0) as decimal(15,2))                   as AZ32_InvoicedAmount__,
    cast(isnull(fla.QUANTITE, 0) as decimal(15,2))                  as AZ32_InvoicedQuantity__,
    case when cl.SOLD_PHYS = 0x54 then 'yes'else 'no' end           as AZ32_DeliveryCompleted__,

    cast(bl.NUM_BR as varchar) +'-' + cast(bl.NO_PO_BR as varchar)  as AZ32_ORDRE,
    'GoodsReceiptItems'                                             as AZ32_EXPORT_KEY
    
into xxTABLExx 
from BR_RECEP br with(nolock)
inner join BR_LIGNE bl with(nolock) on bl.NUM_BR = br.NUM_BR
inner join CA_ENTET ce with (nolock) on bl.NUM_CDE = ce.NO_CDE
inner join CA_LIGNE cl with (nolock) on bl.NUM_CDE = cl.NO_CDE and bl.NO_PO_CDE = cl.NO_PO
left outer join CTE_FL_AMOUNT fla on fla.NUM_BR = bl.NUM_BR and fla.NUM_LIBR = bl.NO_PO_BR
left outer join SOCIETE s with(nolock) on s.CODE_STE = br.CODE_STE
where ce.ETAT_CDE <> 'Z'
and ce.REFUSEE = 0x46
and ce.ETAT_FIN = ''
and ce.DTE_VALID <> '00000000'
and br.DATE_VAL <> '00000000'
and s.DTEVALMIN <= @v_DateDuJour 
and (s.DTEVALMAX >= @v_DateDuJour or s.DTEVALMAX = '00000000')




/*<SEPARATEUR>*/
/* REQ_2.1 : Liste des Lignes de commandes, dans table 1 (xxTABLExx) 
    Calcul du CHAMP exportable (concaténation) du Bloc CA_LIGNE
*/
declare @v_Separateur char(1) = ';'
declare @v_Delimiteur_Chaine char(1) = '"'


update xxTABLExx set CHAMP = 
    @v_Delimiteur_Chaine + trim(AZ32_CompanyCode__) + @v_Delimiteur_Chaine
    + @v_Separateur + @v_Delimiteur_Chaine + trim(AZ32_OrderNumber__) + @v_Delimiteur_Chaine
    + @v_Separateur + @v_Delimiteur_Chaine + trim(AZ32_ItemNumber__) + @v_Delimiteur_Chaine
    + @v_Separateur + @v_Delimiteur_Chaine + trim(AZ32_GoodsReceipt__) + @v_Delimiteur_Chaine
    + @v_Separateur + @v_Delimiteur_Chaine + trim(AZ32_DeliveryNote__) + @v_Delimiteur_Chaine
    + @v_Separateur + @v_Delimiteur_Chaine + cast(AZ32_DeliveryDate__ as varchar) + @v_Delimiteur_Chaine
    + @v_Separateur + cast(AZ32_Amount__ as varchar)
    + @v_Separateur + cast(AZ32_Quantity__ as varchar)
    + @v_Separateur + cast(AZ32_InvoicedAmount__ as varchar)
    + @v_Separateur + cast(AZ32_InvoicedQuantity__ as varchar)
    + @v_Separateur + @v_Delimiteur_Chaine + trim(AZ32_DeliveryCompleted__) + @v_Delimiteur_Chaine


where AZ32_EXPORT_KEY = 'GoodsReceiptItems'




/*<SEPARATEUR>*/
/* REQ_2.2 : Ajout de la ligne 1 en début de fichier, dans table 1 (xxTABLExx) */
insert into xxTABLExx 
(
    CHAMP, 

    /* Seulement car en "Not null" */
	AZ32_Amount__,
    AZ32_Quantity__,
    AZ32_InvoicedAmount__,
    AZ32_InvoicedQuantity__,
    AZ32_DeliveryCompleted__,

    AZ32_ORDRE,
    AZ32_EXPORT_KEY

) 
select

    '"CompanyCode__";"OrderNumber__";"ItemNumber__";"GoodsReceipt__";"DeliveryNote__";"DeliveryDate__";"Amount__";"Quantity__";"InvoicedAmount__";"InvoicedQuantity__";"DeliveryCompleted__"', 

    /* Seulement car en "Not null" */
    0, 
    0, 
    0, 
    0,
    '',


    '!', -- en position 1
    'GoodsReceiptItems'

-- TEST
-- select * from xxTABLExx t with(nolock) order by t.AZ32_ORDRE
