|
select
`vtiger_contratosdecomodato`.`contratosdecomodatoid` AS `contratosdecomodatoid`,
`vtiger_contratosdecomodato`.`consecutivocrm` AS `consecutivocrm`,
(
select
`vtiger_accountgrouprelation`.`groupname`
from
`vtiger_accountgrouprelation`
where
(
`vtiger_accountgrouprelation`.`accountid` = `vtiger_pacientesii`.`accountid`
)
)
AS `regional`,
`vtiger_contratosdecomodato`.`paciente` AS `paciente`,
`vtiger_contratosdecomodato`.`empresa` AS `empresa`,
`vtiger_contratosdecomodato`.`numcontrato` AS `numcontrato`,
`vtiger_contratosdecomodato`.`estadocomodato` AS `estadocomodato`,
`vtiger_contratosdecomodato`.`subestadocomodato` AS `subestadocomodato`,
`vtiger_contratosdecomodato`.`entregainicial` AS `entregainicial`,
`vtiger_contratosdecomodato`.`fechaentrega` AS `fechaentrega`,
`vtiger_contratosdecomodato`.`recogidafinal` AS `OSRT`,
`vtiger_contratosdecomodato`.`ordenterminacion` AS `ordenterminacion`,
`vtiger_contratosdecomodato`.`fechaterminacion` AS `fechaterminacion`,
`vtiger_contratosdecomodato`.`recogidafinal` AS `recogidafinal`,
`vtiger_contratosdecomodato`.`fecharecogida` AS `fecharecogida`,
`vtiger_contratosdecomodato`.`razon_recogida` AS `razon_recogida`,
NULL AS `PeriodoRecogida`,
NULL AS `PeriodoEntrega`,
(
right(`vtiger_contratosdecomodato`.`fechaentrega`, 4) & left(right(`vtiger_contratosdecomodato`.`fechaentrega`, 7), 2)
)
AS `PeriodoEI`,
(
right(`vtiger_contratosdecomodato`.`fecharecogida`, 4) & left(right(`vtiger_contratosdecomodato`.`fecharecogida`, 7), 2)
)
AS `PeriodoRT`,
`vtiger_contratosdecomodato`.`obsterminacion` AS `obsterminacion`,
`vtiger_pacientesii`.`identificador_paciente` AS `identificador_paciente`,
`vtiger_pacientesii`.`firstname` AS `firstname`,
`vtiger_pacientesii`.`lastname` AS `lastname`,
(
(`vtiger_pacientesii`.`firstname` & ' ') & `vtiger_pacientesii`.`lastname`
)
AS `Nombre`,
`vtiger_pacientesii`.`docid` AS `docid`,
`vtiger_account`.`accountname` AS `EPS`,
`vtiger_account`.`epsconsolidada` AS `epsconsolidada`,
`vtiger_pacientesiicf`.`cf_1619` AS `EstadoPaciente`,
`vtiger_pacientesiicf`.`cf_1679` AS `Perdidos_Recuperación`,
`vtiger_pacientesii`.`ultimadesechables` AS `ultimadesechables`,
`vtiger_pacientesii`.`fechaultimavisita` AS `fechaultimavisita`,
`vtiger_pacientesii`.`ultimavisita` AS `ultimavisita`,
`vtiger_empresas`.`razonsocial` AS `razonsocial`,
`vtiger_contratosdecomodato`.`medionotificacion` AS `medionotificacion`,
`vtiger_contratosdecomodato`.`fecha_solicitud_terminacion` AS `Fecha_solicitud_terminacion`,
`vtiger_contratosdecomodato`.`asunto` AS `Asunto`,
`vtiger_pacientesii`.`pacientesiiid` AS `Paciente_ID`,
`vtiger_contratosdecomodato`.`tiempomeses` AS `Timepo_Meses`,
`vtiger_contratosdecomodato`.`linea_servicio` AS `Linea_servicio`,
`vtiger_contratosdecomodato`.`recargasrespaldo` AS `Recarga_respaldo`,
`vtiger_contratosdecomodato`.`recargasportatil` AS `Recargas_portatil`,
`vtiger_contratosdecomodato`.`cantidadvisitas` AS `Cantidad_visitas`,
`vtiger_crmentity`.`smcreatorid` AS `smcreatorid`,
`vtiger_users`.`user_name` AS `CreadoPorUsuario`,
date_format(`vtiger_contratosdecomodato`.`fechaentrega`, '%Y%m') AS `agno_mes`,
date_format(`vtiger_contratosdecomodato`.`fechaentrega`, '%Y%m%d') AS `agno_mes_dia`,
yearweek(`vtiger_contratosdecomodato`.`fechaentrega`, 0) AS `agno_semana`,
contratocf.cf_3646 AS EstadoCierreAdministrativo,
contratocf.cf_3648 AS FechaCierreAdministrativo,
contratocf.cf_3650 AS Cartera,
contratocf.cf_3652 AS ValorCartera,
contratocf.cf_3654 AS EquiposPendientes,
contratocf.cf_3656 AS CantidadEquiposPendientes,
contratocf.cf_3658 AS DetalleEquiposPendientes,
contratocf.cf_3660 AS NovedadesGraves,
contratocf.cf_3662 AS DetalleNovedadesGraves,
contratocf.cf_3664 AS ObservacionesCierre
JOIN ` vtiger_pacientesii` on((`vtiger_contratosdecomodato`.`paciente` = `vtiger_pacientesii`.`pacientesiiid`))) on((`vtiger_pacientesiicf`.`pacientesiiid` = `vtiger_contratosdecomodato`.`paciente`))) JOIN ` vtiger_account` on((`vtiger_pacientesii`.`accountid` = `vtiger_account`.`accountid`))) on((`vtiger_empresas`.`empresasid` = `vtiger_contratosdecomodato`.`empresa`))) LEFT JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_contratosdecomodato.contratosdecomodatoid LEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smcreatorid limit 100
| 
