<?php

$db = new SQLite3('/home/nicolas/Documents/tk106/tk106.sqlite');
$db->loadExtension('libspatialite.so.3');


$traces = array();
$vias = array();
$noeuds = array();

$nlayers = 4;

for ($layer=1; $layer<=$nlayers; $layer++) {
  $traces[$layer] = array();
}

for ($layer=1; $layer<=$nlayers; $layer++) {

  if ($layer == 1 || $layer == $nlayers) {

    $sql  = 'SELECT p.pkuid as pin, nomcomp, nompin, t.pkuid as trace ';
    $sql .= "FROM `pins{$layer}` as p, `traces{$layer}` as t ";
    $sql .= 'WHERE ST_Within(p.geometry, t.geometry) = 1';

    $results = $db->query($sql);
    while ($r = $results->fetchArray()) {

      $tid = $r['trace'];

      if (array_key_exists($tid, $traces[$layer])) {
    $noeud = $traces[$layer][$tid];
      } else {
    $noeud = count($noeuds);
    $traces[$layer][$tid] = $noeud;

    $tracesToAnalyse = array(array('layer'=>$layer, 'id'=>$tid));

    while (count($tracesToAnalyse) != 0) {
      $t = array_pop($tracesToAnalyse);

      foreach (getVias($t['layer'], $t['id']) as $newVia) {
        if (array_key_exists($newVia, $vias)) continue;
        for ($i=1; $i<=$nlayers; $i++) {
          foreach(getTraces($i, $newVia) as $newTrace) {
        if (array_key_exists($newTrace, $traces[$i])) continue;
        $tracesToAnalyse[] = array('layer'=>$i, 'id'=>$newTrace);
        $traces[$i][$newTrace] = $noeud;
          }
        }
        $vias[$newVia] = $noeud;
      }

    }

      } // end if traces
      $noeuds[$noeud][$r['nomcomp']][$r['pin']] = $r['nompin'];
    } // end while fetch comp
  } // end if layers 1 & last
} // end for layers

$txt = '';
foreach ($noeuds as $ni=>$n) {
  $npins = 0;
  $txtnoeud = "noeud #$ni -------------------------\n";
  foreach ($n as $nomcomp=>$pins) {
    $nom = strtoupper($nomcomp);
    foreach ($pins as $pi=>$nompin) {
      $txtnoeud .= "  $nom [$pi] $nompin\n";
      $npins++;
    }
  }
  $txtnoeud .= "\n";
  if ($npins > 1) $txt .=  $txtnoeud;
}

file_put_contents('parts.txt', $txt);

function getVias($layer, $trace) {
  global $db;
  $vias = array();

  $sql  = "SELECT v.pkuid as via FROM traces{$layer} as t, vias as v ";
  $sql .= "WHERE t.pkuid={$trace} AND ST_Within (v.geometry, t.geometry)";
  $results = $db->query($sql);

  while ($r = $results->fetchArray()) {
    $vias[] = $r['via'];
  }

  return $vias;
}

function getTraces($layer, $via) {
  global $db;
  $traces = array();

  $sql  = "SELECT t.pkuid as trace FROM traces{$layer} as t, vias as v ";
  $sql .= "WHERE v.pkuid={$via} AND ST_Within (v.geometry, t.geometry)";
  $results = $db->query($sql);

  while ($r = $results->fetchArray()) {
    $traces[] = $r['trace'];
  }

  return $traces;
}