/*FACTURADAS*/
SELECT a.codasig_cita, fecha_cita, a.codpacientes, nomsucursales, IF(a.codesta_cita=11,'Pendiente por confirmar',IF(a.codesta_cita=1,IF(codtipo_pacientes IS NOT NULL,'Realizada','Activa Sin Admitir'),'Cancelada')) AS edo, GROUP_CONCAT(DISTINCT nomproc_espec) AS espec, IF(a.codesta_cita<>1 AND a.codesta_cita<>11,REPLACE(nom_esta_cita,'cancelada ',''),'') AS motivo_cancel, GROUP_CONCAT(DISTINCT e.nrodoc) AS dr_cc, LCASE(GROUP_CONCAT(DISTINCT r.nombre1,' ',r.nombre2,' ',r.apellido1)) AS dr_nom, GROUP_CONCAT(DISTINCT p.codiss,': ',LEFT(p.nomprocedimientos,100)) AS proc, 'SI' AS fact, nomempresas, fecha_expe, IFNULL((SELECT DISTINCT 'SI' FROM citas_hospit h WHERE h.codasig_cita=a.codasig_cita),'NO') AS RedExterna, SUM(ROUND(((f.valor * 100) / (100 - d.porcentaje)),0) + f.valor_dcto) AS vlr_t, 0 AS ignorar

FROM empresas m, sucursales s, citas_proc_empl cpe, proc_empl pe, procedimientos p, proc_espec c, esta_cita i, empleados e, personas r, citas_facturas f, doc_fuente d, asig_cita a

WHERE fecha_cita BETWEEN '2016-01-01' AND '2016-01-01' AND a.codsucursales=s.codsucursales AND a.codasig_cita=cpe.codasig_cita AND cpe.codproc_empl=pe.codproc_empl AND cpe.codasig_cita=f.codasig_cita AND cpe.codproc_empl=f.codproc_empl AND f.coddoc_fuente=d.coddoc_fuente AND d.codestado_doc_fuente=1 AND pe.codiss=p.codiss AND pe.codsoat=p.codsoat AND p.codproc_espec=c.codproc_espec AND i.codesta_cita=a.codesta_cita AND pe.codempleados=e.codempleados AND r.nrodoc=e.nrodoc AND r.codtipo_doc=e.codtipo_doc AND a.codempresas=m.codempresas
GROUP BY a.codasig_cita

UNION ALL

/*NO FACTURADAS CON ESTADO DE CUENTA*/
 SELECT t.codasi, t.fec, t.codpac, t.nomsuc, t.edo, t.espec, t.motivo_cancel, t.dr_cc, t.dr_nom, t.proc, 'NO' AS fact, t.emp, t.expec, IFNULL((SELECT DISTINCT 'SI' FROM citas_hospit h WHERE h.codasig_cita=t.codasi),'NO') AS RedExterna, IF(t.estado = 1,(IFNULL((SELECT SUM(hv.cantidad * IFNULL(valor_original,precio)) AS cita FROM hg_hoja h, hg_vendida hv LEFT JOIN hg_vendida_dcto dt ON (dt.codhoja_vendida=hv.codhoja_vendida) WHERE h.codasig_cita = t.codasi AND hv.coddoc_fuente IS NULL AND hv.codhoja = h.codhoja AND cobrado = 1 GROUP BY h.codasig_cita),0) + IFNULL((SELECT SUM(valor) AS cita FROM hospitalizacion hos WHERE hos.codasig_cita = t.codasi AND hos.coddoc_fuente IS NULL GROUP BY codasig_cita),0) + IFNULL((SELECT SUM(cantidad * (valor + valor_dcto)) AS cita FROM labor_fact lab WHERE  cobrado_l = 1 AND lab.codasig_cita = t.codasi AND lab.coddoc_fuente IS NULL GROUP BY codasig_cita),0) + IFNULL((SELECT SUM(cantidad * (valor + valor_dcto)) AS cita FROM bsangre_fact bf WHERE cobrado_b = 1 AND bf.codasig_cita = t.codasi AND bf.coddoc_fuente IS NULL GROUP BY codasig_cita),0) + IFNULL((SELECT SUM(ROUND(valor_unit + ((valor_unit * iva) / 100),0) * f.cantidad) AS cita FROM fact_concepto_evento_fact f, fact_concepto_evento e WHERE cobrado_o = 1 AND codasig_cita = t.codasi AND coddoc_fuente IS NULL AND f.cod_concepto_evento = e.cod_concepto_evento GROUP BY codasig_cita),0) + IFNULL((SELECT SUM(valor + valor_dcto) AS cita FROM citas_honorarios ch WHERE ch.codasig_cita = t.codasi AND ch.codcitas_facturas IS NULL GROUP BY codasig_cita),0) + IFNULL((SELECT SUM(valor + valor_dcto) AS cita FROM citas_otros_items co WHERE co.codasig_cita = t.codasi AND co.codcitas_facturas IS NULL GROUP BY codasig_cita),0)),0) AS vlr_t, 0 AS ignorar FROM (

SELECT a.codasig_cita AS codasi, fecha_cita AS fec, a.codpacientes AS codpac, nomsucursales AS nomsuc, IF(a.codesta_cita=11,'Pendiente por confirmar',IF(a.codesta_cita=1,IF(codtipo_pacientes IS NOT NULL,'Realizada','Activa Sin Admitir'),'Cancelada')) AS edo, GROUP_CONCAT(DISTINCT nomproc_espec) AS espec, IF(a.codesta_cita<>1 AND a.codesta_cita<>11,REPLACE(nom_esta_cita,'cancelada ',''),'') AS motivo_cancel, GROUP_CONCAT(DISTINCT e.nrodoc) AS dr_cc, LCASE(GROUP_CONCAT(DISTINCT r.nombre1,' ',r.nombre2,' ',r.apellido1)) AS dr_nom, GROUP_CONCAT(DISTINCT p.codiss,': ',LEFT(p.nomprocedimientos,100)) AS proc, nomempresas AS emp, fecha_expe AS expec, a.codesta_cita AS estado, (SELECT DISTINCT codestado_doc_fuente FROM citas_facturas cf, doc_fuente docs WHERE cf.codasig_cita = cpe.codasig_cita AND cf.codproc_empl = cpe.codproc_empl AND cf.coddoc_fuente = docs.coddoc_fuente AND codestado_doc_fuente = 1) AS fv

FROM empresas m, sucursales s, citas_proc_empl cpe, proc_empl pe, proc_espec c, esta_cita i, empleados e, personas r, asig_cita a, procedimientos p LEFT JOIN proc_est_cuenta es ON (p.codiss = es.codiss AND p.codsoat = es.codsoat)

WHERE es.codiss IS NOT NULL AND es.codsoat IS NOT NULL AND fecha_cita BETWEEN '2016-01-01' AND '2016-01-01' AND a.codsucursales=s.codsucursales AND a.codasig_cita=cpe.codasig_cita AND cpe.codproc_empl=pe.codproc_empl AND pe.codiss=p.codiss AND pe.codsoat=p.codsoat AND p.codproc_espec=c.codproc_espec AND i.codesta_cita=a.codesta_cita AND pe.codempleados=e.codempleados AND r.nrodoc=e.nrodoc AND r.codtipo_doc=e.codtipo_doc AND a.codempresas=m.codempresas
GROUP BY a.codasig_cita HAVING fv IS NULL ) AS t GROUP BY t.codasi

UNION ALL

/*NO FACTURADAS SIN ESTADO DE CUENTA CON PLAN*/
SELECT a.codasig_cita AS codasi, fecha_cita AS fec, a.codpacientes AS codpac, nomsucursales AS nomsuc, IF(a.codesta_cita=11,'Pendiente por confirmar',IF(a.codesta_cita=1,IF(codtipo_pacientes IS NOT NULL,'Realizada','Activa Sin Admitir'),'Cancelada')) AS edo, GROUP_CONCAT(DISTINCT nomproc_espec) AS espec, IF(a.codesta_cita<>1 AND a.codesta_cita<>11,REPLACE(nom_esta_cita,'cancelada ',''),'') AS motivo_cancel, GROUP_CONCAT(DISTINCT e.nrodoc) AS dr_cc, LCASE(GROUP_CONCAT(DISTINCT r.nombre1,' ',r.nombre2,' ',r.apellido1)) AS dr_nom, GROUP_CONCAT(DISTINCT p.codiss,': ',LEFT(p.nomprocedimientos,100)) AS proc, 'NO' AS fact, nomempresas AS emp, fecha_expe AS expec, IFNULL((SELECT DISTINCT 'SI' FROM citas_hospit h WHERE h.codasig_cita=a.codasig_cita),'NO') AS RedExterna, IF(a.codesta_cita = 1,SUM(valorproc),0) AS vlr_t, (SELECT DISTINCT codestado_doc_fuente FROM citas_facturas cf, doc_fuente docs WHERE cf.codasig_cita = cpe.codasig_cita AND cf.codproc_empl = cpe.codproc_empl AND cf.coddoc_fuente = docs.coddoc_fuente AND codestado_doc_fuente = 1) AS ignorar

FROM empresas m, sucursales s, citas_proc_empl cpe, proc_empl pe, proc_espec c, esta_cita i, empleados e, personas r, asig_cita a, planes pl, proc_cont pc, procedimientos p LEFT JOIN proc_est_cuenta es ON (p.codiss = es.codiss AND p.codsoat = es.codsoat)

WHERE es.codiss IS NULL AND es.codsoat IS NULL AND fecha_cita BETWEEN '2016-01-01' AND '2016-01-01' AND a.codsucursales=s.codsucursales AND a.codasig_cita=cpe.codasig_cita AND cpe.codproc_empl=pe.codproc_empl AND pe.codiss=p.codiss AND pe.codsoat=p.codsoat AND p.codproc_espec=c.codproc_espec AND i.codesta_cita=a.codesta_cita AND pe.codempleados=e.codempleados AND r.nrodoc=e.nrodoc AND r.codtipo_doc=e.codtipo_doc AND a.codempresas=m.codempresas AND a.codplanes = pl.codplanes AND pl.codplanes = pc.codplanes AND pc.codiss = p.codiss AND pc.codsoat = p.codsoat
GROUP BY a.codasig_cita HAVING ignorar IS NULL

UNION ALL

/*NO FACTURADAS SIN ESTADO DE CUENTA SIN PLAN*/
SELECT a.codasig_cita AS codasi, fecha_cita AS fec, a.codpacientes AS codpac, nomsucursales AS nomsuc, IF(a.codesta_cita=11,'Pendiente por confirmar',IF(a.codesta_cita=1,IF(codtipo_pacientes IS NOT NULL,'Realizada','Activa Sin Admitir'),'Cancelada')) AS edo, GROUP_CONCAT(DISTINCT nomproc_espec) AS espec, IF(a.codesta_cita<>1 AND a.codesta_cita<>11,REPLACE(nom_esta_cita,'cancelada ',''),'') AS motivo_cancel, GROUP_CONCAT(DISTINCT e.nrodoc) AS dr_cc, LCASE(GROUP_CONCAT(DISTINCT r.nombre1,' ',r.nombre2,' ',r.apellido1)) AS dr_nom, GROUP_CONCAT(DISTINCT p.codiss,': ',LEFT(p.nomprocedimientos,100)) AS proc, 'NO' AS fact, nomempresas AS emp, fecha_expe AS expec, IFNULL((SELECT DISTINCT 'SI' FROM citas_hospit h WHERE h.codasig_cita=a.codasig_cita),'NO') AS RedExterna, 0 AS vlr_t, (SELECT DISTINCT codestado_doc_fuente FROM citas_facturas cf, doc_fuente docs WHERE cf.codasig_cita = cpe.codasig_cita AND cf.codproc_empl = cpe.codproc_empl AND cf.coddoc_fuente = docs.coddoc_fuente AND codestado_doc_fuente = 1) AS ignorar

FROM empresas m, sucursales s, citas_proc_empl cpe, proc_empl pe, proc_espec c, esta_cita i, empleados e, personas r, asig_cita a, procedimientos p LEFT JOIN proc_est_cuenta es ON (p.codiss = es.codiss AND p.codsoat = es.codsoat)

WHERE es.codiss IS NULL AND es.codsoat IS NULL AND fecha_cita BETWEEN '2016-01-01' AND '2017-06-30' AND a.codsucursales=s.codsucursales AND a.codasig_cita=cpe.codasig_cita AND cpe.codproc_empl=pe.codproc_empl AND pe.codiss=p.codiss AND pe.codsoat=p.codsoat AND p.codproc_espec=c.codproc_espec AND i.codesta_cita=a.codesta_cita AND pe.codempleados=e.codempleados AND r.nrodoc=e.nrodoc AND r.codtipo_doc=e.codtipo_doc AND a.codempresas=m.codempresas AND a.codplanes IS NULL
GROUP BY a.codasig_cita HAVING ignorar IS NULL