se/SE/se-lib/Route/Mufy.php

<?php

Lib::loadClass('RouteBase');

class Route_Mufy extends RouteBase {

  public function defaultAction() {
    UI::gora();
    UI::menu();
    UI::startTag('div', ['class'=>"container"]);
    try {
      echo UI::h('h3', [], "Mufy - raport dla uke (TODO)");
      DB::getPDO()->execSql("
        update opt_przelacznice_mufy m
          join TELBOXES t on (t.ID = m.ID_TELBOXES)
        set m.TELBOXES_X =
            IF( t.the_geom is NULL, NULL,
              SUBSTRING(
                AsWKT( ST_Centroid(t.the_geom) ),
                7, -- 'POINT('
                INSTR(AsWKT( ST_Centroid(t.the_geom) ), ' ') - 7
              )
            )
          , m.TELBOXES_Y =
            IF( t.the_geom is NULL, NULL,
              REPLACE( SUBSTRING(
                AsWKT( ST_Centroid(t.the_geom) ),
                INSTR( AsWKT( ST_Centroid(t.the_geom) ), ' ')
              ), ')', '')
            )
        where m.ID_TELBOXES > 0
      ");
      DB::getPDO()->execSql("
        update opt_przelacznice_mufy m
        set m.TELBOXES_X = NULL
          , m.TELBOXES_Y = NULL
        where m.ID_TELBOXES = 0
      ");
      UI::alert('info', "Poprawiono dane w polach TELBOXES_X, TELBOXES_Y w tabeli `opt_przelacznice_mufy` na podstawie powiązanej studni (tabela TELBOXES, pole ID_TELBOXES)");

      $todo = DB::getPDO()->fetchAll("
        select m.ID, m.Nazwa, m.ID_TELBOXES, m.TELBOXES_X, m.TELBOXES_Y
            , AsWKT( m.the_geom ) as mufa_the_geom
            , AsWKT( t.the_geom ) as studnia_the_geom
            , AsWKT( ST_Centroid(t.the_geom) ) as centerXY
            , NULL as centerX
            , NULL as centerY
            , IF(t.the_geom is NULL, NULL,
              SUBSTRING(
                AsWKT( ST_Centroid(t.the_geom) ),
                7, -- 'POINT('
                INSTR(AsWKT( ST_Centroid(t.the_geom) ), ' ') - 7
              )
            ) as testX
            , IF(t.the_geom is NULL, NULL,
              REPLACE(SUBSTRING(
                AsWKT( ST_Centroid(t.the_geom) ),
                INSTR(AsWKT( ST_Centroid(t.the_geom) ), ' ')
              ), ')', '')
            ) as testY
        from `opt_przelacznice_mufy` m
          left join `TELBOXES` t on(t.ID = m.ID_TELBOXES)
        where m.ID_TELBOXES > 0
          or m.ID < 10
      ");
      UI::table([
        'rows' => array_map(function ($row) {
          list($row['centerX'], $row['centerY']) = ('POINT(' == substr($row['centerXY'], 0, strlen('POINT(')))
            ? explode(' ', substr($row['centerXY'], strlen('POINT('), -1))
            : ['NULL', 'NULL'];
          $row['notes'] = '';
          if ($row['ID_TELBOXES'] > 0) {
            if ($row['centerX'] != $row['TELBOXES_X'] ||  $row['centerY'] != $row['TELBOXES_Y']) {
              $row['notes'] = "TODO udpate...";
              // $affected = DB::getPDO()->update('opt_przelacznice_mufy', 'ID', $row['ID'], [
              //   'TELBOXES_X' => ($row['centerX'] > 0) ? $row['centerX'] : 'NULL',
              //   'TELBOXES_Y' => ($row['centerY'] > 0) ? $row['centerY'] : 'NULL',
              // ]);
              // $row['notes'] = ($affected) ? "Zaktualizowano TELBOXES X, Y" : "Bez zmian";
            }
          } else {

          }
          $row['testX'] = (null === $row['testX']) ? 'NULL' : $row['testX'];
          $row['testY'] = (null === $row['testY']) ? 'NULL' : $row['testY'];
          return $row;
        }, $todo)
      ]);
    } catch (Exception $e) {
      UI::alert('danger', "Error #" . $e->getCode() .  "|" . $e->getLine() .  ": " . $e->getMessage());
      DBG::log($e);
    }
    UI::endTag('div');
    UI::dol();
  }

}