/* 
    Export des Fournisseurs vers BC
    - Spec 5083 - TSG
    - Modifs : Spec 5279
*/
/*<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


/*
-- Uniquement en DEV
declare 
	@v_TYPETRAITEMENT	char(1)		= 'E', 
	@v_CODEOBJET		varchar(30) = 'FOURNISS', 
	@v_CODEINTERFACE	varchar(30) = 'BC', 
	@v_NUM_JOUR			int			= 1
*/


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

/* CS sur les fourniss
    - Date d'agrément
    - Date de fin d'assurance RC
    - Company : Id de la société dans BC (Spec 5279) (c'est un CS de type Mémo)
 */ 
declare 
    @v_NCS_FOURNISS_DATE_AGREMENT   int = ISNULL((select cast(LIB01DICO as int) from DILISPEC with(nolock) where TYPEDICO = 'PARNCS0180' and CODEDICO = 'NCS_FOURNISS_DATE_AGREMENT'), 0),
    @v_NCS_FOURNISS_DATE_FIN_RC     int = ISNULL((select cast(LIB01DICO as int) from DILISPEC with(nolock) where TYPEDICO = 'PARNCS0180' and CODEDICO = 'NCS_FOURNISS_DATE_FIN_RC'), 0), 
    @v_NCS_FOURNISS_ID_COMPANY      int = ISNULL((select cast(LIB01DICO as int) from DILISPEC with(nolock) where TYPEDICO = 'PARNCS0180' and CODEDICO = 'NCS_FOURNISS_ID_COMPANY'), 0)

select
    
    case when cs_date_agrement.VALEUR is null or replace(replace(trim(cs_date_agrement.VALEUR),char(13),''),char(10),'') = '' then '0001-01-01' else substring(cs_date_agrement.VALEUR,7,4) + '-' + substring(cs_date_agrement.VALEUR,4,2) + '-' + substring(cs_date_agrement.VALEUR,1,2) end as DATE_AGREMENT_HSE, -- DD/MM/YYYY devient YYYY-MM-DD
    case when cs_date_fin_rc.VALEUR is null or replace(replace(trim(cs_date_fin_rc.VALEUR),char(13),''),char(10),'') = '' then '0001-01-01' else substring(cs_date_fin_rc.VALEUR,7,4) + '-' + substring(cs_date_fin_rc.VALEUR,4,2) + '-' + substring(cs_date_fin_rc.VALEUR,1,2) end as DATE_FIN_RC,               -- DD/MM/YYYY devient YYYY-MM-DD
    cast('' as varchar(50))     as COMPANY_ID, -- CS de type mémo, voir REQ_3 
    
    f.CODE                      as AZ32_CODFOUR,
    isnull(try_cast(isnull(cs_memo_company_id.VALEUR, 0) as int),0) as AZ32_NUM_MEMO_COMPANY_ID, -- CS de type mémo : on stocke d'abord le N° de mémo (voir REQ_3)

    f.CODE                      as AZ32_ORDRE,
    f.CODE                      as AZ32_KEY_FOR_LAUNCH_LOOP, 
    ''                          as AZ32_EXPORT_KEY
    
into xxTABLExx 
from FOURNISS f with(nolock)
/* Lecture du CS "Date Agrement" sur les fournisseurs */ 
left outer join CS_DON cs_date_agrement with(nolock) on cs_date_agrement.COD_TAB = 'FOURNISS' and cs_date_agrement.COD_ENR = f.CODE and cs_date_agrement.NUM_FIX = @v_NCS_FOURNISS_DATE_AGREMENT
/* Lecture du CS "Date de fin assurance RC" sur les fournisseurs */ 
left outer join CS_DON cs_date_fin_rc with(nolock) on cs_date_fin_rc.COD_TAB = 'FOURNISS' and cs_date_fin_rc.COD_ENR = f.CODE and cs_date_fin_rc.NUM_FIX = @v_NCS_FOURNISS_DATE_FIN_RC
/* Lecture du CS "Id Company" (de type Mémo) sur les fournisseurs. */ 
left outer join CS_DON cs_memo_company_id with(nolock) on cs_memo_company_id.COD_TAB = 'FOURNISS' and cs_memo_company_id.COD_ENR = f.CODE and cs_memo_company_id.NUM_FIX = @v_NCS_FOURNISS_ID_COMPANY 
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=@v_TYPETRAITEMENT and TYPEOBJ=@v_CODEOBJET and CODE_INT=@v_CODEINTERFACE and NUM_JOUR=@v_NUM_JOUR/*<NUMJOUR>*/)


/*<SEPARATEUR>*/
/* REQ_2 : correction dates à 0 */
update t set 
    DATE_AGREMENT_HSE = case when DATE_AGREMENT_HSE = '0000-00-00' then '0001-01-01' else DATE_AGREMENT_HSE end,
    DATE_FIN_RC = case when DATE_FIN_RC = '0000-00-00' then '0001-01-01' else DATE_FIN_RC end
from xxTABLExx t

/*<SEPARATEUR>*/
/* REQ_3 : Lecture de la valeur du CS mémo "Company Id" (Spec 5279) */
update t set  
    COMPANY_ID = left(trim(m.CONTMEMO),50)   
from xxTABLExx t 
    inner join MEMOS m with(nolock) on m.NODEMEMO = t.AZ32_NUM_MEMO_COMPANY_ID
where t.AZ32_NUM_MEMO_COMPANY_ID <> 0

/*<SEPARATEUR>*/
/* REQ_4 : On supprime les RC */
update xxTABLExx set 
    COMPANY_ID = replace(COMPANY_ID,char(13)+char(10),' ')

update xxTABLExx set 
    COMPANY_ID = replace(COMPANY_ID,char(10),' ')

update xxTABLExx set 
    COMPANY_ID = replace(COMPANY_ID,char(13),' ')

/* REQ_5 : Company Id par défaut */
update xxTABLExx set 
    COMPANY_ID = '382c5de3-1ddd-ed11-a7c9-002248388e78' 
    where trim(COMPANY_ID) = ''


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