|
SELECT
#01 (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 isnull(SEType.setype) 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 AS razonsocial,
vtiger_cuentascobrogrouprel.groupname AS groupname,
vtiger_cuentascobro.cuentascobroid AS cuentascobroid,
vtiger_cuentascobro.cuentacobro AS cuentacobro,
vtiger_cuentascobro.empresa AS empresa,
vtiger_cuentascobro.estadocuenta AS estadocuenta,
vtiger_cuentascobro.fechaestado AS fechaestado,
vtiger_cuentascobro.valor AS valor,
vtiger_cuentascobro.valorpagado AS valorpagado,
#02 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.
vtiger_cuentascobro.agnocobro AS agnocobro,
vtiger_cuentascobro.mescobro AS mescobro,
vtiger_cuentascobro.tipodeuda AS tipodeuda,
vtiger_cuentascobro.tipogeneracion AS tipogeneracion,
vtiger_cuentascobro.estadoauditoria AS estadoauditoria,
vtiger_cuentascobro.creacion AS creacion,
vtiger_cuentascobro.periodocobro AS periodocobro,
vtiger_cuentascobro.paciente AS ID_Relacionado,
coalesce(SEType.setype, 'Usuario') AS Modulo_Relacionado,
#03 (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 isnull(SEType.setype) 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 AS docid,
vtiger_pacientesii.tipoafiliacion AS tipoafiliacion,
vtiger_pacientesii.tipo_regimen AS tipo_regimen,
vtiger_pacientesiicf.cf_1480 AS NivelIBC,
vtiger_pacientesii.entidadpadrename AS entidadpadrename,
vtiger_account.epsconsolidada AS epsconsolidada,
vtiger_pacientesii.categoria_paciente AS categoria_paciente,
vtiger_pacientesii.departamento AS departamento,
vtiger_pacientesii.ciudad AS ciudad,
vtiger_pacientesii.telefono_incharge AS telefono_incharge,
vtiger_pacientesiicf.cf_1619 AS EstadoPaciente,
vtiger_pacientesii.tel_whatsapp AS tel_whatsapp,
vtiger_pacientesii.email_incharge AS email_incharge,
vtiger_pacientesii.auditcoordenadas AS auditcoordenadas,
vtiger_crmentity.createdtime AS Created_DateTime,
cast(vtiger_crmentity.createdtime AS date) AS Created_Date,
vtiger_crmentity.modifiedtime AS modifiedtime,
#04 (SELECT vtiger_users.user_name
FROM vtiger_users
WHERE (vtiger_users.id = vtiger_crmentity.smcreatorid)) AS Creado_Por_Usuario,
vtiger_crmentity.description AS Observaciones
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)
| 
