CASE  WHEN DOC_FLG_TRAITE = 0 THEN 'A traiter'     
      WHEN DOC_FLG_TRAITE = 1 THEN 'OK'     
      WHEN DOC_FLG_TRAITE = 2 THEN 'KO!'  
 END FLG_LINE_COLOR,  CONCAT ('<img id=\'doc_image\' src=\'upload/FBOSS/DOCS/', DOC_FNAME, '\' />') as DOC_IMG   

SELECT DOC_CLT_ID, 
   SUM( FLG_NB_ASSOC ) DOC_KPI_NB_ASSOC, 
   SUM( FLG_MT_ASSOC ) DOC_KPI_MT_ASSOC, 
   SUM( FLG_COMMENT  ) DOC_KPI_NO_COMMENT,
   SUM( FLG_NB_LIG_KO ) DOC_KPI_NB_LIG_KO    
FROM
(
	SELECT 
	   DOC_ID, DOC_FNAME_ORIG, DOC_FNAME, DOC_LOCK_FLG, DOC_CLT_ID,  DOC_DATE_RECU, DOC_DATE_TRAITE, DOC_CATEG_ID, DOC_FLG_TRAITE, DOC_COMMENT, NB_PIECE_CPTA , MT_PIECE_CPTA, MOT_CLES,
	   CASE WHEN ( NB_PIECE_CPTA = 0 AND DOC_FLG_TRAITE = 1 ) THEN  1 ELSE 0 END FLG_NB_ASSOC,
	   CASE WHEN ( MT_PIECE_CPTA = 0 AND NB_PIECE_CPTA > 0 ) THEN  1 ELSE 0 END FLG_MT_ASSOC,
	   CASE WHEN ( DOC_FLG_TRAITE = 2 AND DOC_COMMENT='' ) THEN  1 ELSE 0 END FLG_COMMENT, 
	   CASE WHEN ( DOC_FLG_TRAITE = 2 ) THEN  1 ELSE 0 END FLG_NB_LIG_KO	   
	FROM ( 
		
		SELECT  DOC_ID, DOC_FNAME_ORIG, DOC_FNAME, DOC_LOCK_FLG, DOC_CLT_ID,  DOC_DATE_RECU, DOC_DATE_TRAITE, DOC_CATEG_ID, DOC_COMMENT, 
		    DOC_FLG_TRAITE,  
			SUM(CASE WHEN (FACT_GRP_PIECE IS NULL OR FACT_GRP_PIECE=0) THEN  0 ELSE 1 END) NB_PIECE_CPTA,  
			SUM(CASE WHEN (FACT_GRP_PIECE IS NULL OR FACT_GRP_PIECE=0) THEN  0 ELSE  FACT_MT_TTC END) MT_PIECE_CPTA,
			GROUP_CONCAT( 	FACT_FOURN )  AS MOT_CLES
		FROM MYSQL_T_DOC A  	
		LEFT OUTER JOIN MYSQL_T_CLT_NDF B ON ( A.DOC_ID = B.FACT_GRP_PIECE )
		GROUP BY  DOC_ID, DOC_FNAME_ORIG, DOC_FNAME, DOC_CLT_ID,  DOC_DATE_RECU, DOC_DATE_TRAITE, DOC_CATEG_ID, DOC_FLG_TRAITE, 
		DOC_COMMENT  

	) G 
) H
GROUP BY  DOC_CLT_ID

SET SQL_BIG_SELECTS=1;

SELECT @curRank := @curRank + 1 AS NUM_LIGNE,  
FACT_ROWID, EXCPTA_ID, FACT_CLT_ID, FACT_DT, FACT_FOURN, FACT_MT_HT, FACT_MT_TVA, FACT_MT_TTC, DEVISE_LBC,  FACT_FLG_LOCK, MODPAIE_LBC, 
FACT_REMARQUE, FACT_NUM_PIECE, CGTR_LBC, TRN_ID, NDF_CLT_OK_FLG, COALESCE(FACT_GRP_PIECE,0) as FACT_GRP_PIECE ,  
CASE     
	WHEN NDF_CLT_OK_FLG = 0 THEN 'A valider'     
	WHEN NDF_CLT_OK_FLG = 1 THEN 'OK'     
	WHEN NDF_CLT_OK_FLG = 2 THEN 'KO!'  
END FLG_LINE_COLOR, 
CONCAT ('<img id=\'ndf_image\' src=\'upload/FBOSS/', FACT_NUM_PIECE, '.jpg\' />') as NDF_IMG

SET SQL_BIG_SELECTS=1;
SELECT FACT_CLT_ID, 
   SUM( FLG_NOM_PIECE ) FACT_KPI_NOM_PIECE, 
   SUM( FLG_MT_TOT )    FACT_KPI_MT_TOT, 
   SUM( FLG_NO_CATEG  ) FACT_KPI_NO_CATEG,
   SUM( FLG_NO_COMMENT  ) FACT_KPI_NO_COMMENT,  
   SUM( FLG_NB_LIG_KO ) FACT_KPI_NB_LIG_KO 
 
FROM
(
	SELECT
	FACT_ROWID, A.EXCPTA_ID, FACT_CLT_ID, FACT_DT, FACT_FOURN, FACT_MT_HT, FACT_MT_TVA, FACT_MT_TTC, DEVISE_LBC,  FACT_FLG_LOCK, MODPAIE_LBC, 
	FACT_REMARQUE, FACT_NUM_PIECE, CGTR_LBC, TRN_ID, NDF_CLT_OK_FLG, COALESCE(FACT_GRP_PIECE,0) as FACT_GRP_PIECE,
	   CASE WHEN (( SUBSTR( FACT_NUM_PIECE, LENGTH(CLT_CODE)+2, 10) <> FACT_DT ) OR ( SUBSTR( FACT_NUM_PIECE,1, LENGTH(CLT_CODE)) <> LTRIM(RTRIM(CLT_CODE)) ) )   THEN  1 ELSE 0 END FLG_PB_NOM_PIECE,
	   CASE WHEN ( (FACT_MT_HT + FACT_MT_TVA <> FACT_MT_TTC) OR (FACT_MT_TTC=0) ) THEN  1 ELSE 0 END FLG_MT_TOT,
	   CASE WHEN ( CGTR_ID IS NULL ) THEN  1 ELSE 0 END FLG_NO_CATEG,
	   EXCPTA_DT_START,	EXCPTA_DT_END, 
	   CASE WHEN (( FACT_DT NOT BETWEEN EXCPTA_DT_START AND EXCPTA_DT_END ) AND FACT_FLG_LOCK=0)THEN  1 ELSE 0 END FLG_HORS_EXER,
	   CASE WHEN ( NDF_CLT_OK_FLG = 2 AND FACT_REMARQUE='' ) THEN  1 ELSE 0 END FLG_NO_COMMENT, 
	   CASE WHEN ( NDF_CLT_OK_FLG = 2 ) THEN  1 ELSE 0 END FLG_NB_LIG_KO		   
	FROM MYSQL_T_CLT_NDF A  	
		LEFT OUTER JOIN MYSQL_T_DEVISE ON (TRIM(DEVISE_ID) = TRIM(FACT_DEVISE_ID))  
		LEFT OUTER JOIN MYSQL_T_MODE_PAIE ON (TRIM(MODPAIE_ID)= TRIM(FACT_MODPAIE_ID))   
		LEFT OUTER JOIN MYSQL_T_CATEGO_TRANSAC ON (TRIM(CGTR_ID)= TRIM(FACT_CATEG_ID)) 
		INNER JOIN MYSQL_T_CLIENT ON ( CLT_ID = FACT_CLT_ID)
		INNER JOIN MYSQL_T_CLT_EXER_CPTA E ON ( ( FACT_CLT_ID = EXCPTA_CLT_ID ) AND ( EXCPTA_FLG_THIS=1 ) )
	WHERE FACT_CLT_ID=8
) H
GROUP BY  FACT_CLT_ID