Voir la requête d'export - Télécharger un fichier de test
SELECT
R.REQ_ID as IdARC,
R.SC_ID as IdSC,
R.TOKEN_NFL as Token_NFL,
DATE_FORMAT(R.REQ_DATETIME,"%d/%m/%Y %H:%i") as DateArrivee,
DATE_FORMAT(SH.STH_DATE,"%d/%m/%Y %H:%i") as DateDernierStatut,
count(DISTINCT(rc.RQC_ID)) as credits_conso,
-- (select count(*) from REQ_CONS rc where rc.REQ_ID = R.REQ_ID and rc.RQC_RACHAT_YN = 1) as credits_conso,
IFNULL(sum(DISTINCT rc.RQC_MONTHLY), 0) as mensualites_cco,
IFNULL(sum(DISTINCT rc.RQC_CAPITAL), 0) as montants_totaux_cco,
count(DISTINCT(ri.RQI_ID)) as credits_immo,
IFNULL(sum(DISTINCT ri.RQI_MONTHLY), 0) as mensualites_cio,
IFNULL(sum(DISTINCT ri.RQI_CAPITAL), 0) as montants_totaux_cio,
R.REQ_ADD_MONEY as besoin_treso,
R.EMP_ID_STATUT_PRO as emp_id_statut_pro,
R.EMP_ID_CONTRAT_TRAVAIL as emp_id_contrat_travail,
R.REQ_SALARY_MONTHLY_1 as emp_revenus,
if(R.REQ_CHILD_1 >= 0, 1, 0) +
if(R.REQ_CHILD_2 >= 0, 1, 0) +
if(R.REQ_CHILD_3 >= 0, 1, 0) +
if(R.REQ_CHILD_4 >= 0, 1, 0) +
if(R.REQ_CHILD_5 >= 0, 1, 0) +
if(R.REQ_CHILD_6 >= 0, 1, 0) +
if(R.REQ_CHILD_7 >= 0, 1, 0) +
if(R.REQ_CHILD_8 >= 0, 1, 0) +
if(R.REQ_CHILD_9 >= 0, 1, 0) +
if(R.REQ_CHILD_10 >= 0, 1, 0) as emp_enfants,
R.REQ_ID_SITU_IMMO as situation,
R.REQ_RENT as loyer,
R.REQ_EXTRA as valeur_bien_im,
R.REQ_FICP as fich_bank_ficp,
R.EMP_ID_CIVILITE as emp_id_civilite,
IF(CONVERT(CAST(CONVERT(R.REQ_SURNAME_1 USING latin1) AS BINARY) USING utf8) is null, REQ_SURNAME_1, UPPER(CONVERT(CAST(CONVERT(R.REQ_SURNAME_1 USING latin1) AS BINARY) USING utf8))) as emp_nom,
IF(CONVERT(CAST(CONVERT(R.REQ_NAME_1 USING latin1) AS BINARY) USING utf8) is null, REQ_NAME_1, CONVERT(CAST(CONVERT(R.REQ_NAME_1 USING latin1) AS BINARY) USING utf8)) as emp_prenom,
CONCAT(R.REQ_NAIS_DAY_1,"/",R.REQ_NAIS_MONTH_1,"/",R.REQ_NAIS_YEAR_1) as emp_naissance,
ANA.EMP_NAISSANCE_ID_PAYS as emp_naissance_etranger,
R.EMP_ID_SITUATION_FAMILIALE as emp_id_situ_familiale,
IF(CONCAT(R.REQ_SURNAME_2, R.REQ_NAME_2) <> '', "true","false") as coemprunteur,
IF(CONVERT(CAST(CONVERT(R.REQ_SURNAME_2 USING latin1) AS BINARY) USING utf8) is null, REQ_SURNAME_2, CONVERT(CAST(CONVERT(R.REQ_SURNAME_2 USING latin1) AS BINARY) USING utf8)) as co_nom,
IF(CONVERT(CAST(CONVERT(R.REQ_NAME_2 USING latin1) AS BINARY) USING utf8) is null, REQ_NAME_2, CONVERT(CAST(CONVERT(R.REQ_NAME_2 USING latin1) AS BINARY) USING utf8)) as co_prenom,
CONCAT(R.REQ_NAIS_DAY_2,"/",R.REQ_NAIS_MONTH_2,"/",R.REQ_NAIS_YEAR_2) as co_naissance,
ANA.CO_NAISSANCE_ID_PAYS as co_naissance_etranger,
R.CO_ID_STATUT_PRO as co_id_statut_pro,
R.CO_ID_CONTRAT_TRAVAIL as co_id_contrat_travail,
R.REQ_SALARY_MONTHLY_2 as co_revenus,
CONVERT(CAST(CONVERT(CONCAT(R.REQ_ADD_STREET,R.REQ_ADD_STREET_COMPLEMENT) USING latin1) AS BINARY) USING utf8) as adresse,
R.REQ_ADD_ZIP as code_postal,
R.REQ_ADD_CITY as ville,
IF(
R.REQ_PHONE_MOBILE_1 = '',
IF(R.REQ_PHONE_MOBILE_2 = '',
IF(R.REQ_PHONE_HOME = '',
IF(R.REQ_PHONE_OFFICE_1 = '',
R.REQ_PHONE_OFFICE_2,
R.REQ_PHONE_OFFICE_1),
R.REQ_PHONE_HOME),
R.REQ_PHONE_MOBILE_2),
R.REQ_PHONE_MOBILE_1) as tel_port,
R.REQ_EMAIL as email,
S.STA_NAME as Statut,
SH.STH_TEXT as Motif,
SM.STA_TINY_NAME as TypeMotif,
IF(SH2.STH_ID IS NOT NULL,"avec","sans") as avec_DR,
R.PROFIL_NR as Profil_NR,
P.PRT_NAME_NOT_HIDDEN as Partenaire,
P.PRT_NAME as CodePartenaire,
IF (SL.XTOR IS NOT NULL,SL.XTOR,R.XTOR) as Xtor,
SOC.LIBELLE AS Marque,
1 as Categorie,
'XX' as prio
FROM STATUS_HISTORY AS SH
LEFT JOIN REQUEST R ON R.REQ_ID = SH.REQ_ID AND R.REQ_LAST_STATUS = SH.STA_ID
LEFT JOIN REQ_ANALYSE ANA ON ANA.REQ_ID = R.REQ_ID
LEFT JOIN AGENCE A ON A.AG_ID = R.AG_ID
LEFT JOIN PARTY P ON P.PRT_ID = R.PRT_ID
LEFT JOIN REL_SOURCE_LEAD_PARTY SL ON SL.FK_PARTY=R.PRT_ID AND SL.FK_SOURCE_LEAD = 1
LEFT JOIN STATUS S ON S.STA_ID = SH.STA_ID
LEFT JOIN REF_SOCIETE SOC ON SOC.SOC_ID = A.SOC_ID
LEFT JOIN REF_SANS_SUITE SS ON SS.SS_TEXT = SH.STH_TEXT
LEFT JOIN STATUS SM ON SM.STA_ID = SH.STA_2_ID
LEFT JOIN STATUS_HISTORY SH2 ON R.REQ_ID = SH2.REQ_ID AND SH2.STA_ID = 3
LEFT JOIN REQUEST R2 ON R2.REQ_EMAIL = R.REQ_EMAIL AND R2.DAM >= "2019-05-31" AND R2.REQ_DATETIME >= "2018-12-01"
LEFT JOIN REQ_IMMO ri on SH.REQ_ID = ri.REQ_ID and ri.RQI_RACHAT_YN = 1
LEFT JOIN REQ_CONS rc on SH.REQ_ID = rc.REQ_ID and rc.RQC_RACHAT_YN = 1
WHERE
SH.STA_ID IN (10)
AND SH.STH_DATE >= "2018-12-01"
AND SH.STH_DATE <= "2019-08-31"
AND R.REQ_DATETIME BETWEEN "2018-12-01" AND "2019-05-31"
AND SS.SS_ID IN (3, 7, 13, 31, 33, 37, 38, 39, 40, 45)
AND R.REQ_EMAIL IS NOT NULL
AND R.REQ_EMAIL <> ""
AND A.FK_DISTRIBUTION = 1
AND A.SOC_ID IN (1,5,7)
GROUP BY R.REQ_EMAIL, SOC.SOC_ID
UNION
SELECT
R.REQ_ID as IdARC,
R.SC_ID as IdSC,
R.TOKEN_NFL as Token_NFL,
DATE_FORMAT(R.REQ_DATETIME,"%d/%m/%Y %H:%i") as DateArrivee,
DATE_FORMAT(SH.STH_DATE,"%d/%m/%Y %H:%i") as DateDernierStatut,
count(DISTINCT(rc.RQC_ID)) as credits_conso,
-- (select count(*) from REQ_CONS rc where rc.REQ_ID = R.REQ_ID and rc.RQC_RACHAT_YN = 1) as credits_conso,
IFNULL(sum(DISTINCT rc.RQC_MONTHLY), 0) as mensualites_cco,
IFNULL(sum(DISTINCT rc.RQC_CAPITAL), 0) as montants_totaux_cco,
count(DISTINCT(ri.RQI_ID)) as credits_immo,
IFNULL(sum(DISTINCT ri.RQI_MONTHLY), 0) as mensualites_cio,
IFNULL(sum(DISTINCT ri.RQI_CAPITAL), 0) as montants_totaux_cio,
R.REQ_ADD_MONEY as besoin_treso,
R.EMP_ID_STATUT_PRO as emp_id_statut_pro,
R.EMP_ID_CONTRAT_TRAVAIL as emp_id_contrat_travail,
R.REQ_SALARY_MONTHLY_1 as emp_revenus,
if(R.REQ_CHILD_1 >= 0, 1, 0) +
if(R.REQ_CHILD_2 >= 0, 1, 0) +
if(R.REQ_CHILD_3 >= 0, 1, 0) +
if(R.REQ_CHILD_4 >= 0, 1, 0) +
if(R.REQ_CHILD_5 >= 0, 1, 0) +
if(R.REQ_CHILD_6 >= 0, 1, 0) +
if(R.REQ_CHILD_7 >= 0, 1, 0) +
if(R.REQ_CHILD_8 >= 0, 1, 0) +
if(R.REQ_CHILD_9 >= 0, 1, 0) +
if(R.REQ_CHILD_10 >= 0, 1, 0) as emp_enfants,
R.REQ_ID_SITU_IMMO as situation,
R.REQ_RENT as loyer,
R.REQ_EXTRA as valeur_bien_im,
R.REQ_FICP as fich_bank_ficp,
R.EMP_ID_CIVILITE as emp_id_civilite,
IF(CONVERT(CAST(CONVERT(R.REQ_SURNAME_1 USING latin1) AS BINARY) USING utf8) is null, REQ_SURNAME_1, UPPER(CONVERT(CAST(CONVERT(R.REQ_SURNAME_1 USING latin1) AS BINARY) USING utf8))) as emp_nom,
IF(CONVERT(CAST(CONVERT(R.REQ_NAME_1 USING latin1) AS BINARY) USING utf8) is null, REQ_NAME_1, CONVERT(CAST(CONVERT(R.REQ_NAME_1 USING latin1) AS BINARY) USING utf8)) as emp_prenom,
CONCAT(R.REQ_NAIS_DAY_1,"/",R.REQ_NAIS_MONTH_1,"/",R.REQ_NAIS_YEAR_1) as emp_naissance,
ANA.EMP_NAISSANCE_ID_PAYS as emp_naissance_etranger,
R.EMP_ID_SITUATION_FAMILIALE as emp_id_situ_familiale,
IF(CONCAT(R.REQ_SURNAME_2, R.REQ_NAME_2) <> '', "true","false") as coemprunteur,
IF(CONVERT(CAST(CONVERT(R.REQ_SURNAME_2 USING latin1) AS BINARY) USING utf8) is null, REQ_SURNAME_2, CONVERT(CAST(CONVERT(R.REQ_SURNAME_2 USING latin1) AS BINARY) USING utf8)) as co_nom,
IF(CONVERT(CAST(CONVERT(R.REQ_NAME_2 USING latin1) AS BINARY) USING utf8) is null, REQ_NAME_2, CONVERT(CAST(CONVERT(R.REQ_NAME_2 USING latin1) AS BINARY) USING utf8)) as co_prenom,
CONCAT(R.REQ_NAIS_DAY_2,"/",R.REQ_NAIS_MONTH_2,"/",R.REQ_NAIS_YEAR_2) as co_naissance,
ANA.CO_NAISSANCE_ID_PAYS as co_naissance_etranger,
R.CO_ID_STATUT_PRO as co_id_statut_pro,
R.CO_ID_CONTRAT_TRAVAIL as co_id_contrat_travail,
R.REQ_SALARY_MONTHLY_2 as co_revenus,
CONVERT(CAST(CONVERT(CONCAT(R.REQ_ADD_STREET,R.REQ_ADD_STREET_COMPLEMENT) USING latin1) AS BINARY) USING utf8) as adresse,
R.REQ_ADD_ZIP as code_postal,
R.REQ_ADD_CITY as ville,
IF(
R.REQ_PHONE_MOBILE_1 = '',
IF(R.REQ_PHONE_MOBILE_2 = '',
IF(R.REQ_PHONE_HOME = '',
IF(R.REQ_PHONE_OFFICE_1 = '',
R.REQ_PHONE_OFFICE_2,
R.REQ_PHONE_OFFICE_1),
R.REQ_PHONE_HOME),
R.REQ_PHONE_MOBILE_2),
R.REQ_PHONE_MOBILE_1) as tel_port,
R.REQ_EMAIL as email,
S.STA_NAME as Statut,
SH.STH_TEXT as Motif,
SM.STA_TINY_NAME as TypeMotif,
IF(SH2.STH_ID IS NOT NULL,"avec","sans") as avec_DR,
R.PROFIL_NR as Profil_NR,
P.PRT_NAME_NOT_HIDDEN as Partenaire,
P.PRT_NAME as CodePartenaire,
IF (SL.XTOR IS NOT NULL,SL.XTOR,R.XTOR) as Xtor,
SOC.LIBELLE AS Marque,
2 as Categorie,
'XX' as prio
FROM STATUS_HISTORY AS SH
LEFT JOIN REQUEST R ON R.REQ_ID = SH.REQ_ID AND R.REQ_LAST_STATUS = SH.STA_ID
LEFT JOIN REQ_ANALYSE ANA ON ANA.REQ_ID = R.REQ_ID
LEFT JOIN PARTY P ON P.PRT_ID = R.PRT_ID
LEFT JOIN REL_SOURCE_LEAD_PARTY SL ON SL.FK_PARTY=R.PRT_ID AND SL.FK_SOURCE_LEAD = 1
LEFT JOIN STATUS S ON S.STA_ID = SH.STA_ID
LEFT JOIN AGENCE A ON A.AG_ID = R.AG_ID
LEFT JOIN REF_SOCIETE SOC ON SOC.SOC_ID = A.SOC_ID
LEFT JOIN REF_SANS_SUITE SS ON SS.SS_TEXT = SH.STH_TEXT
LEFT JOIN STATUS SM ON SM.STA_ID = SH.STA_2_ID
LEFT JOIN STATUS_HISTORY SH2 ON R.REQ_ID = SH2.REQ_ID AND SH2.STA_ID = 3
LEFT JOIN REQUEST R2 ON R2.REQ_EMAIL = R.REQ_EMAIL AND R2.DAM >= "2019-05-31" AND R2.REQ_DATETIME >= "2018-12-01"
LEFT JOIN REQ_IMMO ri on SH.REQ_ID = ri.REQ_ID and ri.RQI_RACHAT_YN = 1
LEFT JOIN REQ_CONS rc on SH.REQ_ID = rc.REQ_ID and rc.RQC_RACHAT_YN = 1
WHERE
SH.STA_ID = 10
AND SH.STH_DATE >= "2018-06-01"
AND SH.STH_DATE <= "2019-08-31"
AND R.REQ_DATETIME BETWEEN "2018-06-01" AND "2018-11-18"
AND SS.SS_ID IN (6, 8, 11, 29)
AND R.REQ_EMAIL IS NOT NULL
AND R.REQ_EMAIL <> ""
AND A.FK_DISTRIBUTION = 1
AND A.SOC_ID IN (1,5,7)
GROUP BY R.REQ_EMAIL, SOC.SOC_ID
;