-- 1 -- RECHERCHE PRESTATAIRE

SELECT  
codePrestataire  AS lbc_centre, l.libelle AS lbc_commune,fn_libelle_prestataire(prt.codePrestataire) AS lb_centre
,telephoneFixe AS tel_centre,telephonePortable AS tel_centre1,faxPrestataire AS  fax_centre,'' AS obs_centre,
'C'	AS mod_paiem_centre,'' AS num_banq_centre,'' AS num_guich_centre,	
'' AS num_compt_centre,	'' AS cle_rib_centre,prt.id AS num_cen,emailGestionPrestataire AS	emai_cen,	
nomResponsable AS Respond, '0'	privilege, '1' AS activer

FROM prestataire prt 
INNER JOIN `typeprestataire` b ON (b.codeTypePrestataire=prt.codeTypePrestataire)
INNER JOIN localite l ON (l.codePays=prt.codePays AND l.codeVille=prt.codeVille AND l.codeLocalite=prt.codeLocalite)
ORDER BY prt.codeVille,l.libelle   ;

-- 2 -- RECHERCHE LOCALITE
SELECT  id AS  num_comune ,codeLocalite AS cod_comune,codeVille AS  lbc_ville,
libelle AS lib_comune,'LASME' AS cod_util,'06/05/2022' tm_stamp,'' AS COD_GEST	,'' ASCOD_SUP
FROM localite_ci

-- 3 -- tablle des règlement

SELECT 	A.id AS num_regl,A.codePrestataire AS lbc_centre, SUM(B.fraisReel) AS mtt_tot_recl_regl	,
SUM(B.montantArembourser) AS tot_base_regl,(SUM(B.montantArembourser)-SUM(B.montantRedresse))	mtt_tot_regl,
'C' mod_regl,fn_libelle_prestataire(A.codePrestataire) AS lb_ordre_regl,'' AS ct_libre_regl,'' AS st_regl,'06/05/2022' AS dt_regl,
'' AS num_lcheq_regl,'LASME' AS cod_util,'06/05/2022' AS tm_stamp,'BRIDGE' AS code_bqe, '' AS	COD_GEST, '' AS	COD_SUP	
, '' AS lbc_assur, '' AS st_cp_regl, '' AS cpta_centre, '' AS	cpta_prestat, '' AS cpta_assur, '' AS cum_mnt_paient
, '' AS  cod_comptabilisation , '' AS dat_comptabilisation, '' AS mnt_escompte, CONCAT (A.codeExercice,'-',A.codeMois,'-',
CASE WHEN A.codeMois="01" THEN "31" WHEN A.codeMois="02" THEN "28" WHEN A.codeMois="03" THEN "31" WHEN A.codeMois="04" THEN "30"
WHEN A.codeMois="05" THEN "31" WHEN A.codeMois="06" THEN "30" WHEN A.codeMois="07" THEN "31" WHEN A.codeMois="08" THEN "31"
WHEN A.codeMois="09" THEN "30" WHEN A.codeMois="10" THEN "31" WHEN A.codeMois="11" THEN "30" WHEN A.codeMois="12" THEN "31" END) AS	dt_paiem_prest
FROM p_reglement A
INNER JOIN  p_facture B ON (B.numeroDecompte = A.numeroDecompte)
WHERE (LEFT(B.dateSysteme,10)  BETWEEN '2020-01-01' AND '2022-03-31')
AND (A.fraisRetenu>"0")  --  and A.codePrestataire!='NC0001'
 AND A.codePrestataire='CE0400'
GROUP BY A.id,A.codePrestataire, A.codeMois,A.codeExercice
ORDER BY dt_paiem_prest,fn_libelle_prestataire(A.codePrestataire)


-- select substring(LAST_DAY(concat('2016-02','01'),9,10)

-- SELECT SUBSTRING(LAST_DAY('2016-02-01'))


SELECT * FROM prestataire WHERE libelle LIKE "%OLIVIERS%"









