WITH ttl AS (SELECT sum(apago) as pagos,
              sum(movs) as movimientos,
              rut_med,
              periodo,
              cod_suc
       FROM (
              SELECT sum(foto.apago) as apago,
                     count(foto.apago) as movs,
                     foto.rut_med,
                     foto.periodo,
                     foto.cod_suc
              FROM h_foto foto
              WHERE 1 = 1  AND substr(periodo,1,4)  =  '2019'
              GROUP BY rut_med,
                       periodo,
                       cod_suc
              UNION
              SELECT sum(valor_dcto) * - 1 as valor_dcto,
                     count(valor_dcto) as movs,
                     rut_med,
                     periodo,
                     cod_suc
              FROM h_foto_dcto_desevo
              WHERE 1 = 1  AND substr(periodo,1,4)  =  '2019'
              GROUP BY rut_med,
                       periodo,
                       cod_suc
            ) aux
       GROUP BY rut_med,
                periodo,
                cod_suc),
diag AS (SELECT
                     mod.id_concepto_fijo,
                     mod.valor,
                     mod.cantidad,
                    (mod.valor * mod.cantidad) as total_concepto,
                     mod.rut_med,
                     mod.periodo,
                     mod.cod_suc
              from mod_diag_foto mod
              where substr(periodo,1,4)  =  '2019'
              GROUP BY rut_med,
                       periodo,
                       cod_suc,
                       mod.id_concepto_fijo,
                       valor,
                       cantidad)
SELECT
       M.nom_med || ' ' || M.apepat_med || ' ' || M.apemat_med as name,
       usu.des_tip_usu,
       CASE M.est_med
            WHEN 1 THEN 'ACTIVO'
            ELSE 'INACTIVO'
       END as estado_medico,
       bancos.rut_sociedad || ' ' || bancos.rut_sociedad_dig as rut_sociedad,
       cs.nom_suc,
       pagostot.periodo,
       CASE
            WHEN esp.cod_esp = 22 THEN (pagostot.pagos - (case when dx.id_concepto_fijo = 1 then dx.total_concepto else 0 END))
            ELSE pagostot.pagos
        END as honorario,
       case when dx.id_concepto_fijo = 0 then dx.cantidad else 0 END as cantidad_turnos,
       case when dx.id_concepto_fijo = 0 then dx.total_concepto else 0 END as dx_fijo,
       case when dx.id_concepto_fijo = 1 then dx.total_concepto else 0 END as rayos,
       case when dx.id_concepto_fijo = 2 then dx.total_concepto else 0 END as orto,
       case when dx.id_concepto_fijo = 3 then dx.total_concepto else 0 END as implan,
       case when dx.id_concepto_fijo = 4 then dx.total_concepto else 0 END as sobremeta,

       coalesce(sum(dx.total_concepto), 0) as pago_diag,
       CASE
            WHEN esp.cod_esp = 22 THEN COALESCE((pagostot.pagos - (case when dx.id_concepto_fijo = 1 then dx.total_concepto else 0 END)) + coalesce(sum(dx.total_concepto), 0) ,0)
            ELSE pagostot.pagos
        END as total_emision
FROM h_cab_liq H
     INNER JOIN medico M ON M.rut_med = H.rut_med
     LEFT JOIN medico_especialidad esp ON esp.rut_med = m.rut_med AND esp.cod_esp = 22
     INNER JOIN tip_usu_rut tip ON tip.rut = M.rut_med
     INNER JOIN tipo_usuario usu ON usu.cod_tip_usu = tip.cod_tip_usu
     LEFT JOIN mod_datos_bancarios bancos ON bancos.rut_med = M.rut_med
     LEFT JOIN ttl pagostot ON pagostot.rut_med = H.rut_med AND to_date(pagostot.periodo,
       'YYYYMM') =(to_timestamp(H.fecha_ini) + interval '1h')::date

     LEFT JOIN diag dx ON dx.rut_med = H.rut_med AND to_date(dx.periodo,
       'YYYYMM') =(to_timestamp(H.fecha_ini) + interval '1h')::date

     INNER JOIN cliente_sucursal CS ON CS.cod_suc = pagostot.cod_suc
WHERE H.estado = 'ING' AND
      pagostot.movimientos > 0 AND EXTRACT(YEAR FROM TIMESTAMP WITH TIME ZONE 'epoch' + fecha_ini * INTERVAL '1 second')=2019
GROUP BY m.rut_med,
        M.nom_med,
         M.apepat_med,
          M.apemat_med,
          usu.des_tip_usu,M.est_med,bancos.rut_sociedad,bancos.rut_sociedad_dig,cs.nom_suc,pagostot.periodo,esp.cod_esp,pagostot.pagos,dx.id_concepto_fijo,dx.total_concepto,
          dx.cantidad