/* 
    Export des fournisseurs vers Talentia 
    - Spec 4935 - SOGEDO
    - Dossier 305381
    - RA : journal 2 (base test 199)
*/
/*<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
if exists (select * from sysobjects where name='xxTABLExx_LISTE_FOU' and xtype='U') drop table xxTABLExx_LISTE_FOU

/*<SEPARATEUR>*/
/* REQ_1 : Liste des fournisseurs, dans une table intermédiaire (xxTABLExx_LISTE_FOU) */

declare @v_DateDuJour char(10) = CONVERT (date, GETDATE()) -- YYYY-MM-DD

select  
    f.CODE                      as uk_numero_compte, 
    ''                          as date_debut,
    ''                          as date_fin,
    left(f.RS,50)               as lib_compte, 
    left(f.RS,15)               as lib_court_compte, 
    ''                          as fk_type_compte, 
    ''                          as fk_code_tva, 
    ''                          as fk_base_tva, 
    ''                          as fk_nature_tva, 
    left(f.TYP_TVA,1)           as fk_regime_tva,           --10

    ''                          as fk_rubrique,
    ''                          as fk_axe0,
    ''                          as fk_axe1,
    ''                          as fk_axe2,
    ''                          as fk_axe3,
    ''                          as fk_axe4,
    ''                          as fk_axe5,
    ''                          as fk_axe6,
    ''                          as fk_axe7,
    ''                          as fk_axe8,
    ''                          as fk_axe9,                 --21

    f.DEVISE                    as fk_devise, 
    ''                          as fk_monnaie_acquisitionperiode1,
    ''                          as fk_monnaie_acquisitionperiode2, 
    ''                          as date_modif_monnaie_acquisition, 
    ''                          as fk_monnaie_gestion_periode1, 
    ''                          as fk_monnaie_gestion_periode2,
    ''                          as date_modif_monnaie_gestion, 
    ''                          as is_oblige_date_valeur, 
    ''                          as fk_eap,                  -- 30
    
    ''                          as fk_ear, 
    ''                          as fk_code_reciprocite, 
    ''                          as fk_type_lettrage, 
    ''                          as is_lettre_par_montant, 
    ''                          as is_resultat_comptable, 
    ''                          as fk_compte_reporting, 
    ''                          as fk_famille_securite, 
    ''                          as is_compression, 
    ''                          as date_ouverture, 
    ''                          as date_fermeture, 
    ''                          as fk_societe, 
    ''                          as fk_etablissement_rattachement, 
    replicate(' ', 5)           as ug_integration,          -- 43 -- Voir REQ_5 : le CS "Unité de gestion" sur la société

    @v_DateDuJour               as date_integration,        -- 44 - date du jour YYYY-MM-DD

    ''                          as is_compte_bancaire, 
    ''                          as zppcg101, 
    ''                          as zppcg151,                -- 47

    replicate(' ', 5)           as fk_type_tiers,           -- 48 -- Voir REQ_3 : le CS Type de Tiers sur le fournisseur
    ''                          as fk_axe_tiers0, 
    ''                          as fk_axe_tiers1, 
    ''                          as fk_axe_tiers2, 
    ''                          as fk_axe_tiers3, 
    ''                          as fk_axe_tiers4,           -- 53

    ''                          as numero_lettrage, 
    ''                          as zppct151, 
    ''                          as zppct101, 
    ''                          as is_verouillage_tresorerie, 
    ''                          as is_num_cheques_decroissant, 
    ''                          as code_emetteur,           -- 59 

    replicate(' ', 36)          as adresse1, -- 60 -- Voir REQ_2
    replicate(' ', 36)          as adresse2, 
    replicate(' ', 36)          as adresse3, 
    /*
    left( isnull([dbo].[OXA_DecoupeChaineV2](f.ADR, 1, char(10)),''), 36) as adresse1, -- 60
    left( isnull([dbo].[OXA_DecoupeChaineV2](f.ADR, 2, char(10)),''), 36) as adresse2, 
    left( isnull([dbo].[OXA_DecoupeChaineV2](f.ADR, 3, char(10)),''), 36) as adresse3, 
    */

    ''                          as adresse_banque, 
    ''                          as adresse_banque_etrangere, 
    ''                          as is_bloque_raf, 
    replicate(' ', 256)         as chemin_reseau,           -- 66 -- Voir REQ_4 : le CS "Email de la compta" sur le fournisseur
    ''                          as fk_civilite, 

    left(case when f.IBANPAYS = 'FR' then f.IBANPAYS + right('0'+ cast(f.IBANCLE as varchar),2) + f.IBANCPT else '' end, 34) as code_iban, -- 68
    --left(f.IBANPAYS + f.IBANCPT + cast(f.IBANCLE as varchar), 34) as code_iban, -- 68 

    case when f.IBANPAYS = 'FR' then left(f.CODEBIC, 11) else '' end                as code_bic, 
    ''                          as cle_rib,                     -- 70
    ''                          as code_banque, 
    ''                          as fk_code_blocage, 
    ''                          as fk_code_bon_a_payer, 
    ''                          as fk_code_calcul_provision,    -- 74
    ''                          as fk_gestionnaire, 
    ''                          as code_guichet, 
    ''                          as fk_code_langue, 
    ''                          as fk_lettre_relance, 

    case when f.IBANPAYS = 'FR' then ''                 else left(f.IBANPAYS,5) end as fk_code_pays_banque, -- 79
    case when f.IBANPAYS = 'FR' then left(f.CPOSTAL, 10) else '' end                as code_postal,         -- 80
    ''                          as code_postal_banque_etrangere, 
    ''                          as fk_type_relance, 
    ''                          as compte_bancaire, 

    --case when f.IBANPAYS <> 'FR' then left(f.DOM_BK,15)   else '' end               as fk_compte_banque,              -- 84 -- Avant modif du 07/08/2024
    ''                                                                              as fk_compte_banque,                -- 84 -- Depuis modif du 07/08/2024
    case when f.IBANPAYS = 'FR' then left(f.DOM_BK,34)  else left(f.IBANPAYS + right('0'+ cast(f.IBANCLE as varchar),2) + f.IBANCPT, 34)  end    as compte_bancaire_etranger, 

    ''                          as date_peremption,             -- 86
    '1'                         as fk_depart_echeance, 
    '00'                        as fk_cadre_depart_echeance, 
    ''                          as is_fiche_tiers_comptable,    -- 89 
    left(f.CODE, 15)            as uk_compte_tiers,             -- 90
    ''                          as is_principale, 
    left(f.NO_TVA_IC, 20)       as identifiant_cee, 
    ''                          as interlocuteur, 
    case when f.IBANPAYS = 'FR' then left(f.DOM_BK,30)   else '' end                as lib_banque, 
    ''                          as limite_credit_monnaie1,      -- 95
    ''                          as limite_credit_monnaie2, 
    left(f.MODE_PAIE, 5)        as fk_mode_reglement,           -- 97

    ''                          as fk_fiche_tiers_reference, 
    ''                          as uk_fiche_tiers, 
    ''                          as fk_fiche_tiers_reglementclient, -- 100
    ''                          as fk_fiche_tiers_reglement_frn, 
    case when f.IBANPAYS <> 'FR' then left(f.DOM_BK,30)   else '' end               as nom_banque_etrangere, 
    c.DELAI_PAI                 as nombre_jours, 
    ''                          as norme_edi, 
    ''                          as numero_fax, 
    ''                          as numero_telex,                -- 106
    left(f.NO_SIRET,9)          as numero_siren, 
    substring(f.NO_SIRET,10,5)  as numero_nicc, 
    case when f.IBANPAYS <> 'FR' then left(f.CODEBIC, 11)   else '' end             as numero_swift,                -- 109 -- Swift = code bic
    ''                          as lib_pays, 
    left(f.CODE_PAYS, 5)        as fk_code_pays, 
    ''                          as profession,
    left(f.RS, 36)              as raison_sociale, 

    ''                          as reference_externe, 
    ''                          as scenario_pointage,           -- 115
    left(f.TEL, 15)             as numero_telephone, 
    
    'FM'                          as fk_terme_echeance, 
    left(f.CODE, 15)            as fk_tiers_reglement_client,   -- 118
    left(f.CODE, 15)            as fk_tiers_reglement_fournisseur, -- 119 -- Voir REQ_4b : le CS Factor sur le fournisseur (Dossier 306184) 
    left(f.VILLE1, 36)          as ville, 
    replicate(' ', 5)           as fk_type_tiers_reglement_client, -- 121 -- Voir REQ_3 : le CS Type de Tiers sur le fournisseur 
    replicate(' ', 5)           as fk_type_tiers_reglement_frn,    -- 122 -- Voir REQ_3 : le CS Type de Tiers sur le fournisseur

    f.CODE                      as AZ32_CODE, 
    f.ADR                       as AZ32_ADR, 
    f.CODE                      as AZ32_ORDRE, 
    replicate(' ', 5)           as AZ32_EXPORT_KEY 

into xxTABLExx_LISTE_FOU
from FOURNISS f with (nolock)
left outer join CONDPAIE c with (nolock) on c.CODE_CDP = f. COND_PAIE
where f.CODE in (select ID_1 from XT_LIG with (nolock) where TYPETR='<TYPETRAITEMENT>' and TYPEOBJ='<CODEOBJET>' and CODE_INT='<CODEINTERFACE>' and NUM_JOUR=1/*<NUMJOUR>*/)
--where f.CODE in (select ID_1 from XT_LIG with (nolock) where TYPETR='E' and TYPEOBJ='FOURNISS' and CODE_INT='FOURNISSEURS' and NUM_JOUR=2)



/*<SEPARATEUR>*/
/* REQ_2 : les RC */
update xxTABLExx_LISTE_FOU set 
    AZ32_ADR=replace(replace(replace(AZ32_ADR,char(13)+char(10), ' ') ,char(10), ' ') ,char(13), ' ')  

/*<SEPARATEUR>*/
/* REQ_2b : les RC. 3 lignes de 36 car */
update xxTABLExx_LISTE_FOU set 
    adresse1=left(AZ32_ADR, 36),  
    adresse2=substring(AZ32_ADR, 37, 36),  
    adresse3=substring(AZ32_ADR, 73, 36)  

/*
update xxTABLExx_LISTE_FOU set 
    adresse1=replace(adresse1,char(10),''),  
    adresse2=replace(adresse2,char(10),''),  
    adresse3=replace(adresse3,char(10),'')  

update xxTABLExx_LISTE_FOU set 
    adresse1=replace(adresse1,char(13),''),  
    adresse2=replace(adresse2,char(13),''),  
    adresse3=replace(adresse3,char(13),'')  
*/



/*<SEPARATEUR>*/
/* REQ_3 : le CS Type de Tiers sur le fournisseur */

declare @v_NCS_TypeTiers int = ISNULL((select cast(LIB01DICO as int) from DILISPEC with(nolock) where TYPEDICO = 'PARNCS0166' and CODEDICO = 'NCS_FOURNISS_TYPE_TIERS'), 0)

update t set 
    fk_type_tiers                   = left(cs.VALEUR, 5),  -- col. 48
    fk_type_tiers_reglement_client  = left(cs.VALEUR, 5),  -- col. 121
    fk_type_tiers_reglement_frn     = left(cs.VALEUR, 5)   -- col. 122
from xxTABLExx_LISTE_FOU t, CS_DON cs 
where t.AZ32_CODE=cs.COD_ENR and cs.COD_TAB='FOURNISS' and cs.NUM_FIX=@v_NCS_TypeTiers 

/*<SEPARATEUR>*/
/* REQ_4 : le CS "Email de la compta" sur le fournisseur */

declare @v_NCS_EmailCompta int = ISNULL((select cast(LIB01DICO as int) from DILISPEC with(nolock) where TYPEDICO = 'PARNCS0166' and CODEDICO = 'NCS_FOURNISS_EMAIL_COMPTA'), 0)

update t set 
    chemin_reseau                   = left(cs.VALEUR, 256)  -- col. 66
from xxTABLExx_LISTE_FOU t, CS_DON cs 
where t.AZ32_CODE=cs.COD_ENR and cs.COD_TAB='FOURNISS' and cs.NUM_FIX=@v_NCS_EmailCompta 

/*<SEPARATEUR>*/
/* REQ_4b : le CS Factor sur le fournisseur (Dossier 306184)  */

declare @v_NCS_Factor int = ISNULL((select cast(LIB01DICO as int) from DILISPEC with(nolock) where TYPEDICO = 'PARNCS0166' and CODEDICO = 'NCS_FOURNISS_FACTOR'), 0)

update t set 
    fk_tiers_reglement_fournisseur  = trim(isnull(cs.VALEUR,''))
from xxTABLExx_LISTE_FOU t 
    inner join CS_DON cs on cs.COD_ENR = t.AZ32_CODE and cs.COD_TAB='FOURNISS' and cs.NUM_FIX=@v_NCS_Factor
where  trim(isnull(cs.VALEUR,'')) <> ''



/*<SEPARATEUR>*/
/* REQ_5 : On duplique la liste des fournisseurs, dans la table finale xxTABLExx, 
    pour chaque société 
*/
declare 
    @v_CODE_STE     varchar(30) = '', 
    @v_Date         char(8)     = convert(varchar(8), GETDATE(), 112), -- YYYYMMDD
    @v_Compteur     int         = 0 

declare CursorSocietes cursor 
for 
select CODE_STE from SOCIETE with(nolock) 
where DTEVALMIN <= @v_Date 
	and (DTEVALMAX >= @v_Date or DTEVALMAX = '00000000')
order by CODE_STE 

open CursorSocietes
fetch next from CursorSocietes into @v_CODE_STE
while (@@FETCH_STATUS = 0)
	begin

        set @v_Compteur += 1

        /* On précise le code société dans AZ32_EXPORT_KEY pour définir le fichier exporté */
        declare 
            @v_NCS_UniteGestion int = ISNULL((select cast(LIB01DICO as int) from DILISPEC with(nolock) where TYPEDICO = 'PARNCS0166' and CODEDICO = 'NCS_SOCIETE_UNITE_GESTION'), 0),
            @v_NCS_CodeSociete int = ISNULL((select cast(LIB01DICO as int) from DILISPEC with(nolock) where TYPEDICO = 'PARNCS0166' and CODEDICO = 'NCS_SOCIETE_CODE_STE'), 0)

        update t set 
            ug_integration      = isnull(left(cs_unite_gestion.VALEUR, 5),''),    -- col. 43
            AZ32_EXPORT_KEY     = isnull(left(cs_code_societe.VALEUR, 5), '')
        from xxTABLExx_LISTE_FOU t
            -- ,CS_DON cs with(nolock)
            left outer join CS_DON cs_unite_gestion with(nolock) on cs_unite_gestion.COD_TAB = 'SOCIETE' and cs_unite_gestion.COD_ENR = @v_CODE_STE and cs_unite_gestion.NUM_FIX = @v_NCS_UniteGestion
            left outer join CS_DON cs_code_societe with(nolock) on cs_code_societe.COD_TAB = 'SOCIETE' and cs_code_societe.COD_ENR = @v_CODE_STE and cs_code_societe.NUM_FIX = @v_NCS_CodeSociete
        -- where cs.COD_ENR=@v_CODE_STE and cs.COD_TAB='SOCIETE' and cs.NUM_FIX=@v_NCS_UniteGestion 

        /* On ajoute la liste des fournisseurs dans la table finale, pour ce code société */
        if @v_Compteur = 1 
            begin 
                select * into xxTABLExx 
                    from xxTABLExx_LISTE_FOU
            end 
        else 
            begin 
                insert into xxTABLExx 
                    select * from xxTABLExx_LISTE_FOU 
            end

        /* Ligne suivante */
		fetch next from CursorSocietes into @v_CODE_STE
	end
close CursorSocietes 
deallocate CursorSocietes



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