SELECT
	id,id2,
	CONCAT('mailto:',de) de2,
	CASE WHEN para!='' THEN CONCAT('mailto:',para)
	ELSE
		(SELECT
			CASE WHEN email_name='' THEN CONCAT('mailto:',email_from) ELSE
			CONCAT('mailto:',email_name,' <',email_from,'>') END
		FROM tbl_usuarios_c WHERE id=id_cuenta)
	END para2,
	CONCAT('link:openapp(\'correo\',',-id,'):',subject) subject,
	datetime,
	size,
	CASE
		WHEN size>=1073741824 THEN CONCAT(ROUND(size/1073741824.0,2),' Gbytes')
		WHEN size>=1048576 THEN CONCAT(ROUND(size/1048576.0,2),' Mbytes')
		WHEN size>=1024 THEN CONCAT(ROUND(size/1024.0,2),' Kbytes')
		ELSE CONCAT(size,' bytes')
	END size2,
	files,
	estado,
	id action_id,
	CONCAT(id2,' - ',de,' - ',subject,' (',estado,')') action_title,
	action_style,
	CASE (1=1) WHEN 1 THEN 'true' ELSE 'false' END action_view,
	CASE (1=1) WHEN 1 THEN 'true' ELSE 'false' END action_pdf2,
	CASE (1=1) WHEN 1 THEN 'true' ELSE 'false' END action_view2,
	CASE (1 AND (1=1)) WHEN 1 THEN CASE is_outbox WHEN '0' THEN 'true' ELSE 'false' END ELSE 'false' END action_reply,
	CASE (1 AND (1=1)) WHEN 1 THEN CASE is_outbox WHEN '0' THEN CASE WHEN (SELECT COUNT(*) FROM tbl_correo_a WHERE id_correo=z.id AND id_tipo IN (2,3))>1 THEN 'true' ELSE 'false' END ELSE 'false' END ELSE 'false' END action_replyall,
	CASE (1 AND (1=1)) WHEN 1 THEN 'true' ELSE 'false' END action_forward,
	CASE (1=1) WHEN 1 THEN 'true' ELSE 'false' END action_wait,
	CASE (1=1) WHEN 1 THEN CASE is_outbox WHEN '0' THEN 'true' ELSE 'false' END ELSE 'false' END action_spam,
	CASE (1=1) WHEN 1 THEN 'true' ELSE 'false' END action_delete
FROM (
	SELECT a.id id,LPAD(a.id,6,0) id2,
		de,para,cc,bcc,
		a.datetime datetime,a.size size,
		files,body,
		e.id_usuario id_usuario,d.id_grupo id_grupo,a.id_cuenta id_cuenta,
		state_new,state_wait,state_spam,is_outbox,
		CASE WHEN subject='' THEN '(sense assumpte)' ELSE subject END subject,
		CONCAT(id_cuenta,'/',uidl) msgid,
		TRIM(CONCAT(
		CASE state_sent WHEN 1 THEN 'Enviat ' ELSE '' END,
		CASE state_error WHEN '' THEN '' ELSE 'Error ' END,
		CASE WHEN is_outbox=1 AND state_sent=0 AND state_error='' THEN ' No-enviat' ELSE '' END,
		CASE state_new WHEN 1 THEN 'Nou ' ELSE '' END,
		CASE state_reply WHEN 1 THEN 'Respost ' ELSE '' END,
		CASE state_forward WHEN 1 THEN 'Reenviat ' ELSE '' END,
		CASE state_wait WHEN 1 THEN 'Pendent ' ELSE '' END,
		CASE state_spam WHEN 1 THEN 'SPAM ' ELSE '' END,
		CASE priority WHEN -1 THEN 'Baixa ' WHEN 1 THEN 'Alta ' ELSE '' END,
		CASE sensitivity WHEN 1 THEN 'Personal ' WHEN 2 THEN 'Privat ' WHEN 3 THEN 'Confidencial ' ELSE '' END,
		CASE is_outbox+state_new+state_reply+state_forward+state_wait+state_spam+priority*10+sensitivity WHEN 0 THEN 'Llegit ' ELSE '' END
		)) estado,
		CASE WHEN state_error!='' THEN 'state_error' WHEN state_spam=1 THEN 'state_spam' WHEN state_new=1 THEN 'state_new' WHEN state_wait=1 THEN 'state_wait' ELSE '' END action_style
	FROM tbl_correo a
		LEFT JOIN tbl_registros_i e ON e.id_aplicacion='27' AND e.id_registro=a.id
		LEFT JOIN tbl_usuarios d ON e.id_usuario=d.id
	WHERE
		a.id IN (SELECT id_registro FROM tbl_indexing WHERE id_aplicacion='27' AND 1=1)
) z
WHERE 1=1
AND id_usuario='38'
AND (1=1)
AND 1=1
AND (1=1)
AND (1=1)
AND (1=1)
AND (1=1)
AND (1=1)
AND id_cuenta NOT IN (SELECT id FROM tbl_usuarios_c WHERE email_privated='1' AND id_usuario!='38')
AND (1=1)
AND (1=1)
AND 1=1
AND (1=1) ORDER BY id desc LIMIT 0,50;
