|
#01 SELECT (CASE
WHEN SEType.setype = 'PacientesII' THEN (SELECT vtiger_accountgrouprelation.groupname
FROM vtiger_accountgrouprelation
WHERE vtiger_accountgrouprelation.accountid = vtiger_pacientesii.accountid)
WHEN SEType.setype = 'Empleados' THEN (CASE
WHEN (SELECT vtiger_empleadosgrouprel.groupname FROM vtiger_empleadosgrouprel WHERE vtiger_empleadosgrouprel.empleadosid = vtiger_cuentascobro.paciente) LIKE "CUN%" THEN 'CUNDINAMARCA'
WHEN (SELECT vtiger_empleadosgrouprel.groupname FROM vtiger_empleadosgrouprel WHERE vtiger_empleadosgrouprel.empleadosid = vtiger_cuentascobro.paciente) LIKE "EJE%" THEN 'EJE CAFETERO'
ELSE vtiger_cuentascobrogrouprel.groupname
END)
WHEN SEType.setype IS NULL THEN (CASE
WHEN (SELECT vtiger_groups.groupname
FROM vtiger_groups
LEFT JOIN vtiger_users2group ON (vtiger_users2group.groupid = vtiger_groups.groupid)
LEFT JOIN vtiger_users ON (vtiger_users.id = vtiger_users2group.userid)
WHERE vtiger_users.id = vtiger_cuentascobro.paciente LIMIT 1) LIKE "CUN%" THEN 'CUNDINAMARCA'
WHEN (SELECT vtiger_groups.groupname
FROM vtiger_groups
LEFT JOIN vtiger_users2group ON (vtiger_users2group.groupid = vtiger_groups.groupid)
LEFT JOIN vtiger_users ON (vtiger_users.id = vtiger_users2group.userid)
WHERE vtiger_users.id = vtiger_cuentascobro.paciente LIMIT 1) LIKE "EJE%" THEN 'EJE CAFETERO'
ELSE vtiger_cuentascobrogrouprel.groupname
END)
END) AS 'Regional',
vtiger_empresas.razonsocial,
vtiger_cuentascobrogrouprel.groupname,
vtiger_cuentascobro.cuentascobroid,
vtiger_cuentascobro.cuentacobro,
vtiger_cuentascobro.empresa,
vtiger_cuentascobro.estadocuenta,
vtiger_cuentascobro.fechaestado,
vtiger_cuentascobro.valor,
vtiger_cuentascobro.valorpagado,
vtiger_cuentascobro.agnocobro,
vtiger_cuentascobro.mescobro,
vtiger_cuentascobro.tipodeuda,
vtiger_cuentascobro.tipogeneracion,
vtiger_cuentascobro.estadoauditoria,
vtiger_cuentascobro.creacion,
vtiger_cuentascobro.periodocobro,
vtiger_cuentascobro.paciente AS 'ID_Relacionado',
COALESCE(SEType.setype, 'Usuario') AS 'Modulo_Relacionado',
#02 (CASE
WHEN SEType.setype = 'PacientesII' THEN vtiger_pacientesii.identificador_paciente
WHEN SEType.setype = 'Empleados' THEN (SELECT vtiger_empleados.identificador_empleado FROM vtiger_empleados WHERE vtiger_empleados.empleadosid = vtiger_cuentascobro.paciente)
WHEN SEType.setype IS NULL THEN (SELECT vtiger_users.user_name FROM vtiger_users WHERE vtiger_users.id = vtiger_cuentascobro.paciente)
END) AS 'Relacionado_Con',
LEFT(vtiger_pacientesii.doc_type, 2) AS 'Tipo_Documento',
vtiger_pacientesii.docid,
vtiger_pacientesii.tipoafiliacion,
vtiger_pacientesii.tipo_regimen,
vtiger_pacientesiicf.cf_1480 AS 'NivelIBC',
vtiger_pacientesii.entidadpadrename,
vtiger_account.epsconsolidada,
vtiger_pacientesii.categoria_paciente,
vtiger_pacientesii.departamento,
vtiger_pacientesii.ciudad,
vtiger_pacientesii.telefono_incharge,
vtiger_pacientesiicf.cf_1619 AS 'EstadoPaciente',
vtiger_pacientesii.tel_whatsapp,
vtiger_pacientesii.email_incharge,
vtiger_pacientesii.auditcoordenadas,
vtiger_crmentity.createdtime AS 'Created_DateTime',
DATE(vtiger_crmentity.createdtime) AS 'Created_Date',
vtiger_crmentity.modifiedtime,
#03 (SELECT vtiger_users.user_name FROM vtiger_users WHERE vtiger_users.id = vtiger_crmentity.smcreatorid) AS 'Creado_Por_Usuario',
vtiger_crmentity.description AS 'Observaciones'
from
(
left join
on((`vtiger_cuentascobrogrouprel`.`cuentascobroid` = `vtiger_cuentascobro`.`cuentascobroid`)))
left join
on((`vtiger_empresas`.`empresasid` = `vtiger_cuentascobro`.`empresa`)))
left join
on((`vtiger_pacientesiigrouprel`.`pacientesiiid` = `vtiger_cuentascobro`.`paciente`)))
left join
on((`vtiger_pacientesii`.`pacientesiiid` = `vtiger_cuentascobro`.`paciente`)))
left join
on((`vtiger_pacientesiicf`.`pacientesiiid` = `vtiger_pacientesii`.`pacientesiiid`)))
left join
on((`vtiger_account`.`accountid` = `vtiger_pacientesii`.`accountid`)))
left join
on((`vtiger_crmentity`.`crmid` = `vtiger_cuentascobro`.`cuentascobroid`)))
left join
`vtiger_crmentity` `SEType`
on((`SEType`.`crmid` = `vtiger_cuentascobro`.`paciente`))
)
where
(
(`vtiger_crmentity`.`deleted` <> 1)
and
(
`vtiger_crmentity`.`createdtime` >= date_format((now() - interval 90 day), '%Y-%m-%d 00:00:00')
)
)
| 
