<?php
  $db = pg_connect("host=10.50.114.201 port=5432 dbname=sube user=subeweb password=WGCO6sfygF");

  $linea = addslashes($_GET['linea']);
  $fecha = addslashes($_GET['fecha']);
  if ($linea != 'mitre') {
    $sql = "
        SELECT
          e.estacion as id_est,
          t2.hora,
          e.est_desc as descripcion,
          coalesce(t2.cant_uso_checkin+t2.cant_checkout_sin_checkin,0) checkin,
          coalesce(t2.cant_checkout,0) checkout
        FROM
          ".$linea.".estaciones e
        LEFT JOIN
        (
          SELECT
            case when codigo_operacion in (114,124,125) then check_out else check_in end as estacion,
            extract(HOUR from fecha) hora,
            sum(case when (codigo_operacion=124) then 1 else 0 end) as cant_checkout_sin_checkin,
            sum(case when (codigo_operacion=113) then 1 else 0 end) as cant_uso_checkin,
            sum(case when (codigo_operacion in (114,125)) then 1 else 0 end) as cant_checkout
          FROM
            ".$linea.".mov_monetario M
          WHERE
            M .fecha >= '".$fecha." 00:00:00' :: TIMESTAMP
            AND M .fecha < '".$fecha." 23:59:59' :: TIMESTAMP
            AND codigo_operacion <> 123
          GROUP BY
            estacion, extract(HOUR from fecha)
        ) t2 ON e.estacion = t2.estacion
        WHERE e.estacion>0
        ORDER BY
          e.estacion,
          t2.hora";
  }
  else {
    $sql = "
        SELECT
          e.estacion as id_est,
          t2.hora,
          e.est_desc as descripcion,
          coalesce(t2.cant_uso_checkin+t2.cant_checkout_sin_checkin,0) checkin,
          coalesce(t2.cant_checkout,0) checkout
        FROM
          ".$linea.".estaciones e
        LEFT JOIN
        (
          SELECT
            mol.estacion,
            extract(HOUR from fecha) hora,
            sum(case when (codigo_operacion=124) then 1 else 0 end) as cant_checkout_sin_checkin,
            sum(case when (codigo_operacion=113) then 1 else 0 end) as cant_uso_checkin,
            sum(case when (codigo_operacion in (114,125)) then 1 else 0 end) as cant_checkout
          FROM
            ".$linea.".mov_monetario M
          INNER JOIN
            ".$linea.".reporte rep ON rep.id_reporte = M.id_reporte
          INNER JOIN
            ".$linea.".molinetes mol ON mol.id_molinete = rep.nro_interno
          WHERE
            M .fecha >= '".$fecha." 00:00:00' :: TIMESTAMP
            AND M .fecha < '".$fecha." 23:59:59' :: TIMESTAMP
            AND codigo_operacion <> 123
          GROUP BY
            mol.estacion, extract(HOUR from fecha)
        ) t2 ON e.estacion = t2.estacion
        WHERE e.estacion>0
        ORDER BY
          e.estacion,
          t2.hora";
  }

  $serie1 = [];
  $serie2 = [];
  $nombres = [];
  $result = pg_query($sql);
  while ($rs = pg_fetch_array($result)) {
    if (!array_key_exists($rs['id_est'], $serie1)) {
      $serie1[$rs['id_est']] = [];
      $serie2[$rs['id_est']] = [];
    }
    $nombres[$rs['id_est']] = $rs['descripcion'];
    $h = str_pad($rs['hora'],2,'0', STR_PAD_LEFT);
    $serie1[$rs['id_est']][$h] = $rs['checkin'];
    $serie2[$rs['id_est']][$h] = $rs['checkout'];
  }
  foreach ($serie1 as $idest => $d1) {
    for ($i=0; $i < 24; $i++) {
      $h = str_pad($i,2,'0', STR_PAD_LEFT);
      if (!array_key_exists($h, $serie1[$idest])) {
        $serie1[$idest][$h] = 0;
        $serie2[$idest][$h] = 0;
      }
    }
    ksort($serie1[$idest]);
    ksort($serie2[$idest]);
  }
  echo json_encode(['transacciones_pagas' => $serie1, 'check_out' => $serie2, 'estaciones' => $nombres]);