|
SELECT vtiger_reciboscaja.ordenauth AS 'ordenauth',
vtiger_reciboscaja.fecha_recibo AS 'fecha_recibo',
vtiger_reciboscaja.numerorecibo AS 'numerorecibo',
vtiger_reciboscaja.concepto AS 'concepto',
vtiger_reciboscaja.pagadoq AS 'pagadoq',
vtiger_reciboscaja.estado_recibo AS 'estado_recibo',
vtiger_reciboscaja.obsinternas AS 'obsinternas',
vtiger_reciboscaja.valorprogramado AS 'valorprogramado',
vtiger_reciboscaja.valorfinal AS 'valorfinal',
vtiger_reciboscaja.obsfinal AS 'obsfinal',
vtiger_reciboscaja.sedesel AS 'sedesel',
vtiger_reciboscaja.fechapago AS 'fechapago',
vtiger_reciboscaja.tiporecibo AS 'tiporecibo',
vtiger_reciboscaja.creadodesde AS 'creadodesde',
#01 (SELECT vtiger_empresas.razonsocial
FROM vtiger_empresas
WHERE vtiger_empresas.empresasid = vtiger_reciboscaja.empresa) AS 'nom_empresa',
vtiger_reciboscaja.consecutivoempresa AS 'consecutivoempresa',
vtiger_reciboscaja.visita AS 'visita',
#02 (SELECT vtiger_visitas.visita
FROM vtiger_visitas
WHERE vtiger_visitas.visitasid = vtiger_reciboscaja.visita) AS 'nom_visitas',
vtiger_reciboscaja.msgprocesamiento AS 'msgprocesamiento',
vtiger_reciboscaja.completo AS 'completo',
vtiger_reciboscaja.cierrerecibo AS 'cierrerecibo',
vtiger_reciboscaja.asunto_recibo AS 'Asunto_recibo',
vtiger_reciboscaja.reciboscajaid AS 'RecibosCajaId',
vtiger_reciboscaja.precio AS 'Precio',
vtiger_reciboscaja.razonnopago AS 'RazonNoPago',
vtiger_reciboscaja.condonacion AS 'Condonacion',
vtiger_reciboscaja.cmpagohasta AS 'CMPagoHasta',
vtiger_reciboscaja.obsprogramacion AS 'ObsProgramacion',
vtiger_reciboscaja.tramitadopor AS 'TramitadoPor',
#03 (SELECT vtiger_users.user_name
FROM vtiger_users
WHERE vtiger_users.id = vtiger_crmentity.smcreatorid) AS 'CreadoPorUsuario',
vtiger_crmentity.createdtime AS 'CreatedTime',
vtiger_crmentity.modifiedtime AS 'Modified_Time',
vtiger_reciboscajacf.cf_1508 AS 'NumFisico',
vtiger_reciboscajacf.cf_1579 AS 'FormadePago',
vtiger_reciboscajacf.cf_2004 AS 'cf_2004',
vtiger_reciboscaja.paciente AS 'ID_Relacionado',
COALESCE(SEType.setype, 'Usuario') AS 'Modulo_Relacionado',
#04 (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_reciboscaja.paciente)
WHEN SEType.setype IS NULL THEN (SELECT vtiger_users.user_name FROM vtiger_users WHERE vtiger_users.id = vtiger_reciboscaja.paciente)
END) AS 'Relacionado_Con',
DATE_FORMAT(vtiger_reciboscaja.fecha_recibo, '%Y%m') AS `agno_mes`,
DATE_FORMAT(vtiger_reciboscaja.fecha_recibo, '%Y%m%d') AS `agno_mes_dia`,
yearweek(vtiger_reciboscaja.fecha_recibo, 0) AS `agno_semana`,
month(vtiger_reciboscaja.fecha_recibo) AS 'Mes',
concat(vtiger_users.first_name, ' ', vtiger_users.last_name) AS 'Tramitador',
vtiger_reciboscaja.automatico AS 'automatico',
vtiger_reciboscaja.recibomanual AS 'recibomanual',
vtiger_pacientesii.entidadpadrename AS 'entidadpadrename',
LEFT(vtiger_pacientesii.doc_type, 2) AS 'Tipo_Documento',
vtiger_pacientesii.docid AS 'DocId',
concat(vtiger_users_1.first_name, ' ', vtiger_users_1.last_name) AS 'Auditor',
vtiger_users_1.user_name AS 'Auditor_usuario',
vtiger_reciboscaja.corregidoauditor AS 'CorregidoAuditor',
#05 (SELECT vtiger_users.user_name
FROM vtiger_users
WHERE vtiger_users.id = vtiger_reciboscaja.revisor) AS 'Revisor_usuario',
vtiger_reciboscaja.corregidorevisor AS 'CorregidoRevisor',
vtiger_rutascf.cf_2797 AS 'Operador_Logistico',
vtiger_rutascf.rutasid AS 'RutasID',
#06 (CASE
WHEN SEType.setype = 'PacientesII' THEN (COALESCE((vtiger_rutascf.cf_2961),
(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_reciboscaja.paciente) LIKE "CUN%" THEN 'CUNDINAMARCA'
WHEN (SELECT vtiger_empleadosgrouprel.groupname FROM vtiger_empleadosgrouprel WHERE vtiger_empleadosgrouprel.empleadosid = vtiger_reciboscaja.paciente) LIKE "EJE%" THEN 'EJE CAFETERO'
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_reciboscaja.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_reciboscaja.paciente LIMIT 1) LIKE "EJE%" THEN 'EJE CAFETERO'
END)
END) AS 'Regional',
vtiger_rutas.nombre_ruta AS 'NombreRuta',
vtiger_rutascf.cf_3487 AS 'UnidadOperacionLogistica',
vtiger_empleados.identificador_empleado AS 'Conductor',
vtiger_empleados_1.identificador_empleado AS 'Auxiliar',
vtiger_ordenesservicio.numero_orden AS 'Numero_Orden',
vtiger_account.epsconsolidada AS 'EPSConsolidada',
vtiger_planillastrabajo.planillastrabajoid AS 'ID_planilla',
vtiger_planillastrabajo.identificador AS 'Identificador_planilla'
LEFT JOIN vtiger_reciboscajacf ON (vtiger_reciboscaja.reciboscajaid = vtiger_reciboscajacf.reciboscajaid) LEFT JOIN vtiger_pacientesii ON (vtiger_pacientesii.pacientesiiid = vtiger_reciboscaja.paciente) LEFT JOIN vtiger_account ON (vtiger_account.accountid = vtiger_pacientesii.accountid) LEFT JOIN vtiger_visitas ON (vtiger_ordenesservicio.visita = vtiger_visitas.visitasid) LEFT JOIN vtiger_planillastrabajo ON (vtiger_visitas.planilla_trabajo = vtiger_planillastrabajo.planillastrabajoid) LEFT JOIN vtiger_empleados ON (vtiger_planillastrabajo.placa_camion = vtiger_empleados.empleadosid) LEFT JOIN vtiger_empleados vtiger_empleados_1 ON (vtiger_planillastrabajo.auxiliarcamion = vtiger_empleados_1.empleadosid)
LEFT JOIN vtiger_users ON (vtiger_reciboscaja.tramitadopor = vtiger_users.id) LEFT JOIN vtiger_users vtiger_users_1 ON (vtiger_reciboscaja.auditor = vtiger_users_1.id) LEFT JOIN vtiger_rutas ON (vtiger_planillastrabajo.ruta = vtiger_rutas.rutasid) LEFT JOIN vtiger_rutascf ON (vtiger_rutascf.rutasid = vtiger_rutas.rutasid) LEFT JOIN vtiger_crmentity ON (vtiger_crmentity.crmid = vtiger_reciboscaja.reciboscajaid) LEFT JOIN vtiger_crmentity SEType ON (SEType.crmid = vtiger_reciboscaja.paciente)
ORDER BY vtiger_reciboscaja.fecha_recibo DESC;
| 
