/* 
    Export des Commandes (lignes) vers SAGE
    - Spec 5263 - LASPA

*/
/*<DELIMITEUR>TAB</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 : 
    Liste des lignes de commandes, dans la table 1 (xxTABLExx) 
*/

/* CS sur la Commande (entête et ligne) */ 
declare 
    @v_NCS_CA_LIGNE_CODE_AFFAIRE int = ISNULL((select cast(LIB01DICO as int) from DILISPEC with(nolock) where TYPEDICO = 'PARNCS0201' and CODEDICO = 'NCS_CA_LIGNE_CODE_AFFAIRE'), 0) 

;WITH CTE_BR AS 
(
    select 
        bl.NUM_CDE as NUM_CDE,
        bl.NO_PO_CDE as NO_PO_CDE,
        -- STRING_AGG(cast(isnull(trim(m.CONTMEMO), '') as varchar(max)), ';') as COM_RECEPT,
        STRING_AGG(case when isnull(trim(m.CONTMEMO), '') <> '' then cast(replace(replace(trim(m.CONTMEMO),char(10),' '),char(13),'') as varchar(max)) end, ';') as COM_RECEPT,
        STRING_AGG(cast(br.NUM_BR as varchar(max)), ';') as NUMS_BR,
        STRING_AGG(cast(br.BON_LIVR as varchar(max)), ';') as NUMS_BL,
        sum(bl.QTE_ACCEP) as SUM_RECEPT,
        max(br.DATE_BL) as DATE_BR
    from BR_RECEP br
    inner join BR_LIGNE bl with(nolock) on bl.NUM_BR = br.NUM_BR
    /* Lecture du memo de la ligne de Bon de Reception */
    left outer join MEMOS m with(nolock) on m.NODEMEMO = bl.NM_COMMENT
    where bl.NUM_CDE in (select cast(ID_1 as int) from XT_LIG with(nolock) where TYPETR='<TYPETRAITEMENT>' and TYPEOBJ='<CODEOBJET>'  and NUM_JOUR=1/*<NUMJOUR>*/) 
    -- where bl.NUM_CDE in (select cast(ID_1 as int) from XT_LIG with(nolock) where TYPETR=@v_TYPETRAITEMENT and TYPEOBJ=@v_CODEOBJET  and NUM_JOUR=@v_NUM_JOUR/*<NUMJOUR>*/)
    group by bl.NUM_CDE, bl.NO_PO_CDE
)
select 

    'CREATE'                                                                            as CREATION,
    ce.CODE_FOU                                                                         as CODE_FOU,
    ''                                                                                  as VIDE1,
    ce.NO_CDE                                                                           as NO_CDE,
    isnull(ev.DTE, ce.DTE_VALID)                                                        as DTE_ENV, -- normalement jamais null sauf erreur d'insertion de données
    ce.TOT_TTC                                                                          as TOT_TTC,
    ce.TOT_CDEHT                                                                        as TOT_CDEHT,
    ce.DEVISE                                                                           as DEVISE,
    ce.ACH                                                                              as ACH,
    ''                                                                                  as VIDE2,
    ''                                                                                  as VIDE3,
    cl.NO_PO                                                                            as NO_PO,
    cl.CODE_ART                                                                         as CODE_ART,
    case when isnull(cl.REF_FOUR, '') <> '' then cl.REF_FOUR + ';' else '' end
    + case when isnull(cl.DES_SOMM, '') <> '' then cl.DES_SOMM + ';' else '' end 
    + isnull(replace(replace(trim(m.CONTMEMO),char(10),' '),char(13),''),'')            as DESIGN,
    cl.PU                                                                               as PU,
    cl.QTE_COMM                                                                         as QTE_COMM,
    isnull(cte_br.SUM_RECEPT, 0)                                                        as QTE_SUM_RECEPT,
    ''                                                                                  as VIDE4,
    cast(cl.QTE_COMM * cl.PU * (1 - cl.REMISE / 100) as decimal(15,2))                  as MTLIGHT,
    ''                                                                                  as VIDE5,
    ''                                                                                  as VIDE6,
    cast(cl.QTE_COMM * cl.PU * (1 - cl.REMISE / 100) * (cl.TVA / 100) as decimal(15,2)) as MTN_TVA_CL,
    cl.SSCPTEANA                                                                        as SSCPTEANA,
    ''                                                                                  as VIDE7,
    isnull(cs_code_affaire.VALEUR, '')                                                  as CODE_AFFAIRE,
    ''                                                                                  as VIDE8,
    cl.REF_FOUR                                                                         as REF_ART_FOURN,
    cl.SECTIMP                                                                          as SECT_IMP,
    ''                                                                                  as VIDE10,
    isnull(cte_br.COM_RECEPT, '')                                                       as COM_RECEPT,
    isnull(cte_br.DATE_BR, '')                                                          as DATE_BR,
    cl.DTELIVPRE                                                                        as DTELIVPRE,
    0                                                                                   as CONST_0,
    'STD'                                                                               as CONST_STD,
    ''                                                                                  as VIDE11,
    ''                                                                                  as VIDE12,
    ''                                                                                  as VIDE13,
    ''                                                                                  as VIDE14,
    ''                                                                                  as VIDE15,
    ''                                                                                  as VIDE16,
    ''                                                                                  as VIDE17,
    case when cl.RECEP_AUTO = 0x54 then cl.DATEDEBCONT else '' end                      as DTE_START,
    case when cl.RECEP_AUTO = 0x54 then cl.DATEFINCONT else '' end                      as DTE_END,
    case when cl.SOLD_PHYS = 0x54 then 'SP' else '' end                                 as SOLDE_PHYS,
    ''                                                                                  as VIDE18,
    cl.UNITE                                                                            as UNITE,
    ''                                                                                  as VIDE19,
    ''                                                                                  as VIDE20,
    ''                                                                                  as VIDE21,
    ''                                                                                  as VIDE22,
    isnull(cte_br.NUMS_BL, '')                                                          as REF_BL,
    isnull(cte_br.NUMS_BR, '')                                                          as NUM_BR,
    ''                                                                                  as VIDE23,


    cast(ce.NO_CDE as varchar(30)) + '-' + cast (cl.NO_PO as varchar(30))               as AZ32_ORDRE,
    cl.NO_CDE                                                                           as AZ32_NO_CDE,
    cl.NO_PO                                                                            as AZ32_NO_LIG_CDE,
    trim(cl.SECTIMP)                                                                    as AZ32_EXPORT_KEY

    -- changer params exports
    
into xxTABLExx 
from CA_ENTET ce with(nolock) 
inner join CA_LIGNE cl with(nolock) on cl.NO_CDE = ce.NO_CDE
left outer join CA_EVENT ev with(nolock) on ev.NO_CDE = ce.NO_CDE and ev.NUM_ORD = ce.EVE_IMP
/* Lecture du memo de la ligne de commande */
left outer join MEMOS m with(nolock) on m.NODEMEMO = cl.NM_DCOMP
/* Lecture du CTE basé sur les lignes de Bon de reception de la ligne de commande */
left outer join CTE_BR cte_br on cte_br.NUM_CDE = cl.NO_CDE and cte_br.NO_PO_CDE = cl.NO_PO 
/* Lecture du CS "Montant acompte" sur la commande */ 
left outer join CS_DON cs_code_affaire with(nolock) on cs_code_affaire.COD_TAB = 'CA_LIGNE' and cs_code_affaire.COD_ENR = right('000000000'+cast(cl.NO_CDE as varchar(9)),9) + right('000'+cast(cl.NO_PO as varchar(3)),3) and cs_code_affaire.NUM_FIX = @v_NCS_CA_LIGNE_CODE_AFFAIRE
where ce.NO_CDE in (select cast(ID_1 as int) from XT_LIG with(nolock) where TYPETR='<TYPETRAITEMENT>' and TYPEOBJ='<CODEOBJET>'  and NUM_JOUR=1/*<NUMJOUR>*/) 
-- where ce.NO_CDE in (select cast(ID_1 as int) from XT_LIG with(nolock) where TYPETR=@v_TYPETRAITEMENT and TYPEOBJ=@v_CODEOBJET  and NUM_JOUR=@v_NUM_JOUR/*<NUMJOUR>*/)


/*<SEPARATEUR>*/
update t set 
    -- COM_RECEPT  = case when substring(COM_RECEPT,len(COM_RECEPT),1) = ';' then left(COM_RECEPT, len(COM_RECEPT) - 1) else COM_RECEPT end,
    DTE_ENV     = case when DTE_ENV <> '00000000' then DTE_ENV else '' end,
    DATE_BR     = case when DATE_BR <> '00000000' then DATE_BR else '' end,
    DTELIVPRE   = case when DTELIVPRE <> '00000000' then DTELIVPRE else '' end,
    DTE_START   = case when DTE_START <> '00000000' then DTE_START else '' end,
    DTE_END     = case when DTE_END <> '00000000' then DTE_END else '' end,
    DESIGN      = case when substring(DESIGN,len(DESIGN),1) = ';' then left(DESIGN, len(DESIGN) - 1) else DESIGN end
from xxTABLExx t


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