/* 
    Export des Commandes (lignes) vers GFC
    - Spec 5288 - UNIVBO

*/
/*<DELIMITEUR>;</DELIMITEUR>*/
/*<HEADER>1</HEADER>*/
/*<LAUNCH.wl>*/ 


/* 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 entêtes de commandes, dans la table 1 (xxTABLExx) 
*/

declare 
    @v_NCS_CA_ENTET_NUM_COMMANDE    int = ISNULL((select cast(LIB01DICO as int) from DILISPEC with(nolock) where TYPEDICO = 'PARNCS0198' and CODEDICO = 'NCS_CA_ENTET_NUM_COMMANDE'), 0),
    @v_NCS_CA_ENTET_ETAPE           int = ISNULL((select cast(LIB01DICO as int) from DILISPEC with(nolock) where TYPEDICO = 'PARNCS0198' and CODEDICO = 'NCS_CA_ENTET_ETAPE'), 0),
    @v_NCS_HASTUTIL_PERS_ID         int = ISNULL((select cast(LIB01DICO as int) from DILISPEC with(nolock) where TYPEDICO = 'PARNCS0198' and CODEDICO = 'NCS_HASTUTIL_PERS_ID'), 0)
    
    -- ,@v_DateJour char(10) = CONVERT(char(10), GetDate(),126) -- format aaaa-mm-dd

select 

    /* entête */
    ce.DEVISE                           as Devise,
    ce.CODE_FOU                         as CodeFournisseur,
    ''                                  as CodeServiceCommande,
    isnull(m.CONTMEMO,'')               as CommentaireCommande,
    substring(ce.DTE_CREAT,1,4)         as Exercice,
    replicate(' ', 3)                   as IdAttribution,
    -- (il est unique normalement (nouvelle régle))
    'false'                             as IsEjAttribution,
    'false'                             as IsEjReprise,
    ce.OBJET_CA                         as LibelleCommande,
    ce.NO_CDE                           as ReferenceCommande,
    'SYSTEMEEXTERNE'                    as TypeApplication,
    -- @v_DateJour                         as DateEJ,

    cast(ce.NO_CDE as varchar)          as AZ32_ORDRE,
    ''                                  as AZ32_OS_COMMAND_TO_EXECUTE,
    cast(ce.NO_CDE as varchar)          as AZ32_KEY_FOR_LAUNCH_LOOP, 
    ce.NO_CDE                           as AZ32_NUM_COMMANDE_OXALYS,
    isnull(cs_num_commande.VALEUR,'')   as AZ32_NUM_COMMANDE_COCKTAIL,
    @v_NCS_CA_ENTET_NUM_COMMANDE        as AZ32_NUM_COMMANDE_COCKTAIL_NUM_CS,
    isnull(try_cast(isnull(cs_etape.VALEUR, 0) as int),0)               as AZ32_ETAPE,
    @v_NCS_CA_ENTET_ETAPE               as AZ32_ETAPE_NUM_CS,
    isnull(cs_ach_pers_id.VALEUR,'')    as AZ32_ACHETEUR_PERS_ID,
    ce.ACH                              as AZ32_ACHETEUR_CODE,
    isnull(cs_sig_pers_id.VALEUR,'')    as AZ32_SIGNATAIRE_PERS_ID,
    isnull(eve_sig.SIGNAT,'')           as AZ32_SIGNATAIRE_CODE, 
    ''                                  as AZ32_EXPORT_KEY

into xxTABLExx 
from CA_ENTET ce with(nolock) 
/* Lecture du memo Titre de commande */
left outer join MEMOS m with(nolock) on m.NODEMEMO = ce.NM_TITRE
/* Lecture des CS "Num Commande dans GFC Cocktail", "N° Etape" sur la commande */ 
left outer join CS_DON cs_num_commande with(nolock) on cs_num_commande.COD_TAB = 'CA_ENTET' and cs_num_commande.COD_ENR = ce.NO_CDE and cs_num_commande.NUM_FIX = @v_NCS_CA_ENTET_NUM_COMMANDE
left outer join CS_DON cs_etape with(nolock) on cs_etape.COD_TAB = 'CA_ENTET' and cs_etape.COD_ENR = ce.NO_CDE and cs_etape.NUM_FIX = @v_NCS_CA_ENTET_ETAPE
/* Lecture du CS "Id User dans GFC Cocktail" sur l'acheteur */ 
left outer join CS_DON cs_ach_pers_id with(nolock) on cs_ach_pers_id.COD_TAB = 'HASTUTIL' and cs_ach_pers_id.COD_ENR = ce.ACH and cs_ach_pers_id.NUM_FIX = @v_NCS_HASTUTIL_PERS_ID
/* Lecture du dernier signataire sur la commande */ 
left outer join CA_EVENT eve_sig with(nolock) on eve_sig.NO_CDE = ce.NO_CDE and eve_sig.NUM_ORD = ce.EVE_SIG
/* Lecture du CS "Id User dans GFC Cocktail" sur le signataire */ 
left outer join CS_DON cs_sig_pers_id with(nolock) on cs_sig_pers_id.COD_TAB = 'HASTUTIL' and cs_sig_pers_id.COD_ENR = isnull(eve_sig.SIGNAT,'') and cs_sig_pers_id.NUM_FIX = @v_NCS_HASTUTIL_PERS_ID
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>*/
/* REQ_1.5 : 
    On update l'id, dans la table 1 (xxTABLExx) 
*/
declare
    @v_NCS_ACD_ID_ATTRIBUTION         int = ISNULL((select cast(LIB01DICO as int) from DILISPEC with(nolock) where TYPEDICO = 'PARNCS0198' and CODEDICO = 'NCS_ACD_ID_ATTRIBUTION'), 0)
    
update t
    set IdAttribution = isnull((
        select TOP 1 isnull(trim(cs_id_att.VALEUR), '')
        from CA_LIGNE cl 
        /* Lecture des CS "Num Commande dans GFC Cocktail", "N° Etape" sur la commande */ 
        left outer join CS_DON cs_id_att with(nolock) on cs_id_att.COD_TAB = 'ACD' and cs_id_att.COD_ENR = cl.ACCORCA and cs_id_att.NUM_FIX = @v_NCS_ACD_ID_ATTRIBUTION
        where isnull(cl.ACCORCA, '') <> ''
        and cl.NO_CDE = t.AZ32_NUM_COMMANDE_OXALYS
    ), '')
from xxTABLExx t


/*<SEPARATEUR>*/
/* REQ_2 : 
    Liste des lignes de commandes, dans la table 2 (xxTABLE2xx) 
*/

declare 
    -- @v_NCS_CA_ENTET_ETAPE_2         int = ISNULL((select cast(LIB01DICO as int) from DILISPEC with(nolock) where TYPEDICO = 'PARNCS0198' and CODEDICO = 'NCS_CA_LIGNE_ETAPE'), 0),
    @v_NCS_CA_LIGNE_IMPUT_COMPT     int = ISNULL((select cast(LIB01DICO as int) from DILISPEC with(nolock) where TYPEDICO = 'PARNCS0198' and CODEDICO = 'NCS_CA_LIGNE_IMPUT_COMPT'), 0), 
    @v_NCS_CA_LIGNE_TAUX_PRORATA    int = ISNULL((select cast(LIB01DICO as int) from DILISPEC with(nolock) where TYPEDICO = 'PARNCS0198' and CODEDICO = 'NCS_CA_LIGNE_TAUX_PRORATA'), 0)  
    --@v_NCS_CODECOMP_NATURE_DEPENSE  int = ISNULL((select cast(LIB01DICO as int) from DILISPEC with(nolock) where TYPEDICO = 'PARNCS0198' and CODEDICO = 'NCS_CODECOMP_NATURE_DEPENSE'), 0) 
    
declare 
    /* Paramétrages standards */
    @v_SI_SECTORISE char(1) = ISNULL((select LIB01PARV from PARAVRAC with(nolock) where TYPEPARV = 'PARAMSTE' and CODEPARV = 'SISECTOR'), 'N') -- La SI est sectorisée par société ?
    --@v_CDC_SECTORISE  char(1) = ISNULL((select LIB01PARV from PARAVRAC with(nolock) where TYPEPARV = 'PARAMSTE' and CODEPARV = 'CDCSECTO'), 'N') -- Le CDC est sectorisé par société ?
    

select 

    /* ligne */
    'false'                                                             as AjoutCatalogue,
    cl.SSFAM                                                            as CodeNomenclatureAchat,
    ''                                                                  as IdCatalogueArticle,
    cl.DES_SOMM                                                         as LibelleArticle,
    cast(cl.PU * (1 - cl.REMISE / 100) as decimal(15,2))                as PrixHT,
    cl.QTE_COMM                                                         as Quantite,
    cl.REF_FOUR                                                         as Reference,
    cl.TVA                                                              as TauxTVA,
    -- (cl.TVA / 100)                                                      as TauxTVA,
    case when cl.ACCORCA <> '' then 'MARCHE' else 'HORS_MARCHE' end     as TypeAchat,                   -- 'MARCHE' ou 'HORS MARCHE'
    /* imputations */
    cl.SSCPTEANA                                                        as DestinationDepense,
    cl.SECTIMP                                                          as EntiteBudgetaire,
    isnull(cs_imputation_comptable.VALEUR, '')                          as ImputationComptable,
    cast(cl.QTE_COMM * cl.PU * (1 - cl.REMISE / 100) as decimal(15,2))  as Montant,
    isnull(si.CODE_DIR, '')                                             as NatureDeDepense,             -- Code direction (Retour modif du 01/07) - Avant : isnull(cs_nature_depense.VALEUR, '')
    case when cl.ID_CG <> '' then cl.ID_CG else 0 end                   as Operation,                   -- pas d'exemple
    isnull(cs_taux_prorata.VALEUR, '')                                  as TauxDeProrata,

    cast(ce.NO_CDE as varchar) + '-' +  cast(cl.NO_PO as varchar)       as AZ32_ORDRE,
    ''                                                                  as AZ32_OS_COMMAND_TO_EXECUTE,
    cast(ce.NO_CDE as varchar)                                          as AZ32_KEY_FOR_LAUNCH_LOOP, 
    ce.NO_CDE                                                           as AZ32_NUM_COMMANDE_OXALYS,
    -- isnull(cs.VALEUR,'')                                                as AZ32_NUM_COMMANDE_COCKTAIL,
    -- isnull(cs_etape.VALEUR, '')                                         as AZ32_ETAPE,
    ''                                                                  as AZ32_EXPORT_KEY


into xxTABLE2xx 
from CA_ENTET ce with(nolock) 
inner join CA_LIGNE cl with(nolock) on cl.NO_CDE = ce.NO_CDE
/* Lecture du memo de la ligne de commande */
--left outer join MEMOS m with(nolock) on m.NODEMEMO = cl.NM_DCOMP 
/* Lecture de la SI de la ligne de commande */
left outer join SECTIMPU si with(nolock) on cl.SECTIMP = si.CODE_SI and (ce.CODE_STE = si.CODE_STE or @v_SI_SECTORISE = 'N')
/* Lecture des CS "Imputation Comptable", "Taux de Prorata" sur la ligne de commande */ 
left outer join CS_DON cs_imputation_comptable with(nolock) on cs_imputation_comptable.COD_TAB = 'CA_LIGNE' and cs_imputation_comptable.COD_ENR = right('000000000'+cast(cl.NO_CDE as varchar(9)),9) + right('000'+cast(cl.NO_PO as varchar(3)),3) and cs_imputation_comptable.NUM_FIX = @v_NCS_CA_LIGNE_IMPUT_COMPT
left outer join CS_DON cs_taux_prorata with(nolock) on cs_taux_prorata.COD_TAB = 'CA_LIGNE' and cs_taux_prorata.COD_ENR = right('000000000'+cast(cl.NO_CDE as varchar(9)),9) + right('000'+cast(cl.NO_PO as varchar(3)),3) and cs_taux_prorata.NUM_FIX = @v_NCS_CA_LIGNE_TAUX_PRORATA
/* Lecture du CS "Nature de dépense" sur le CODECOMP */ 
--left outer join CS_DON cs_nature_depense with(nolock) on cs_nature_depense.COD_TAB = 'CODECOMP' and cs_nature_depense.COD_ENR = (case when @v_CDC_SECTORISE = 'O' then cast(ce.CODE_STE as char(7)) else replicate(' ',7) end) + cl.CODE_COMP and cs_nature_depense.NUM_FIX = @v_NCS_CODECOMP_NATURE_DEPENSE
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>*/)


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