SELECT
date_format(vtiger_crmentity.createdtime, '%Y%m%d') AS agno_mes_dia,
DATE_FORMAT(vtiger_crmentity.createdtime, '%Y%m') AS agno_mes,
YEAR(CURDATE()) as agno,
#01 (SELECT vtiger_accountgrouprelation.groupname
FROM vtiger_accountgrouprelation
WHERE (vtiger_accountgrouprelation.accountid = vtiger_pacientesii.accountid)) AS Regional,
count(*) as Total_cuentas,
SUM(IF( vtiger_cuentascobro.estadocuenta = 'Anulada', 1,0 )) as Anulada,
SUM(IF( vtiger_cuentascobro.estadocuenta = 'Condonada', 1,0 )) as Condonada,
SUM(IF( vtiger_cuentascobro.estadocuenta = 'Pagada', 1,0 )) as Pagada,
SUM(IF( vtiger_cuentascobro.estadocuenta = 'Parcial', 1,0 )) as Parcial,
SUM(IF( vtiger_cuentascobro.estadocuenta = 'Pendiente', 1,0 )) as Pendiente,
SUM(IF( vtiger_cuentascobro.estadocuenta = 'Programado', 1,0 )) as Programado,
SUM(vtiger_cuentascobro.valor) as Valor_cuentas,
SUM(IF( vtiger_cuentascobro.estadocuenta = 'Anulada', vtiger_cuentascobro.valor,0 )) as Valor_Anulada,
SUM(IF( vtiger_cuentascobro.estadocuenta = 'Condonada', vtiger_cuentascobro.valor,0 )) as Valor_Condonada,
SUM(vtiger_cuentascobro.valorpagado) as Valor_Pagado,
#02 SUM(IF(vtiger_cuentascobro.estadocuenta = 'Pendiente' OR vtiger_cuentascobro.estadocuenta = 'Parcial' OR vtiger_cuentascobro.estadocuenta = 'Programado' OR vtiger_cuentascobro.estadocuenta = 'Pagada', vtiger_cuentascobro.valor - vtiger_cuentascobro.valorpagado, 0 )) as Valor_Pendiente
LEFT JOIN vtiger_empresas on vtiger_empresas.empresasid = vtiger_cuentascobro.empresa
LEFT JOIN vtiger_pacientesii on vtiger_pacientesii.pacientesiiid = vtiger_cuentascobro.paciente
LEFT JOIN vtiger_pacientesiicf on vtiger_pacientesiicf.pacientesiiid = vtiger_pacientesii.pacientesiiid
LEFT JOIN vtiger_account on vtiger_account.accountid = vtiger_pacientesii.accountid
LEFT JOIN vtiger_crmentity on vtiger_crmentity.crmid = vtiger_cuentascobro.cuentascobroid
LEFT JOIN vtiger_crmentity SEType on SEType.crmid = vtiger_cuentascobro.paciente
WHERE (vtiger_crmentity.deleted <> 1) and SEType.setype = 'PacientesII' and cast(vtiger_crmentity.createdtime AS date) BETWEEN DATE_FORMAT(NOW(), '%Y-%m-01 00:00:00') AND DATE_FORMAT(LAST_DAY(NOW()), '%Y-%m-%d 23:59:59')
AND identificador_paciente NOT LIKE '%PRUEBA%' and vtiger_pacientesii.empresa <> 3130039 and vtiger_pacientesii.empresa <> 3589077
group by (SELECT vtiger_accountgrouprelation.groupname
WHERE (vtiger_accountgrouprelation.accountid = vtiger_pacientesii.accountid))
|