$last_addr = ''; $addr_array = array(); $result = pg_query($sql); if (pg_num_rows($result)) { $content .= '<table>'; $content .= '<tr class="text-center" style="background-color: silver; font-weight: bold;"> <td class="span1 left bt">Порт</td> <td class="span4 bt">Описание</td> <td class="span4 bt">Подключение</td> <td class="span3 bt">Адрес</td> <td class="span2 bt">Кросс</td> <td class="span1 bt">Порт</td> <td class="span4 br bt">Описание</td> </tr>'; while ($row = pg_fetch_assoc($result)) { $to_port = find_end_port($node_id, 0, $row['fiber_id'], true); $bb = $last_addr != $to_port['curr_node_addr'] ? 'bt' : ''; $bg_key = array_search($to_port['curr_node_addr'], $addr_array); if (!is_numeric($bg_key)) { array_push($addr_array, $to_port['curr_node_addr']); } $bg = isset($to_port['curr_node_addr']) ? array_search($to_port['curr_node_addr'], $addr_array) % 2 == 0 ? ' bgw' : ' bgs' : ' bgw'; $content .= '<tr>'; $content .= ' <td class="' . $bb . $bg . '">' . $row['port'] . '</td>'; $content .= ' <td class="' . $bb . $bg . '">' . ($row['descrip'] ? $row['descrip'] : ' ') . '</td>'; $content .= ' <td class="' . $bb . $bg . '">' . ($row['to_port'] ? $row['to_pq_name'] . ' №' . ($row['to_pq_num'] ? $row['to_pq_num'] : '1') . ' порт ' . $row['to_port'] : ' ') . '</td>'; $content .= ' <td class="' . $bb . $bg . '">' . ($to_port['curr_node_addr'] ? $to_port['curr_node_addr'] : ' ') . '</td>'; $content .= ' <td class="' . $bb . $bg . '">' . ($to_port['pq_name'] ? $to_port['pq_name'] . ($to_port['pq_type'] == 0 ? ' №' . ($to_port['pq_num'] ? $to_port['pq_num'] : '1') : '') : ' ') . '</td>'; $content .= ' <td class="' . $bb . $bg . '">' . ($to_port['to_port'] && $to_port['pq_type'] == 0 ? $to_port['to_port'] : ' ') . '</td>'; $content .= ' <td class="br ' . $bb . $bg . '">' . ($to_port['port_desc'] ? $to_port['port_desc'] : ' ') . '</td>'; $content .= '</tr>';
function find_end_port($to_node_id, $last_id, $id, $first = false) { global $table_cruz_conn; global $table_fiber_conn; global $table_fiber; global $table_cable; global $table_pq; global $table_pq_type; global $table_node; global $table_color; //$result_array = array(); $sql = ' SELECT f1.id AS id, f1.num AS num, n1.id AS from_node_id, CASE WHEN p1.node = n1.id THEN p1.id ELSE p2.id END AS from_pq_id, c1.id AS from_cable_id, f2.id AS to_id, f2.num AS to_num, n2.id AS to_node_id, CASE WHEN p3.node = n2.id THEN p3.id ELSE p4.id END AS to_pq_id, c2.id AS to_cable_id, c_n.id AS curr_node_id, c_n.address AS curr_node_addr, c_n.descrip AS curr_node_descrip, c_n.incorrect, CASE WHEN p1.node = c_n.id THEN p1.id ELSE CASE WHEN p2.node = c_n.id THEN p2.id ELSE NULL END END AS curr_pq_id, f1.mod_color AS mod_color_1, f1.fib_color AS fib_color_1, f2.mod_color AS mod_color_2, f2.fib_color AS fib_color_2, cc1.port AS to_port FROM ' . $table_fiber_conn . ' AS fc1, ' . $table_cable . ' AS c1, ' . $table_pq . ' AS p1, ' . $table_pq . ' AS p2, ' . $table_fiber . ' AS f2, ' . $table_cable . ' AS c2, ' . $table_pq . ' AS p3, ' . $table_pq . ' AS p4, ' . $table_node . ' AS n1, ' . $table_node . ' AS c_n, ' . $table_node . ' AS n2, ' . $table_fiber . ' AS f1 LEFT JOIN ' . $table_cruz_conn . ' AS cc1 JOIN ' . $table_pq . ' AS pp1 ON pp1.node !=' . $to_node_id . ' ON cc1.fiber_id = f1.id AND cc1.pq_id = pp1.id WHERE ( ( fc1.fiber_id_1 = ' . $id . ' AND fc1.fiber_id_2 != ' . $last_id . ' ) OR ( fc1.fiber_id_2 = ' . $id . ' AND fc1.fiber_id_1 != ' . $last_id . ' ) ) AND f1.id = ' . $id . ' AND c1.id = f1.cable_id AND p1.id = c1.pq_1 AND p2.id = c1.pq_2 AND n1.id = CASE WHEN ( p1.node = p3.node OR p1.node = p4.node ) THEN p2.node ELSE p2.node END AND p3.id = c2.pq_1 AND p4.id = c2.pq_2 AND n2.id = CASE WHEN ( p1.node = p3.node OR p2.node = p3.node ) THEN p4.node ELSE p3.node END AND ( (c_n.id = CASE WHEN ( p1.node = p3.node OR p1.node = p4.node ) THEN p1.node ELSE NULL END ) OR ( c_n.id = CASE WHEN ( p2.node = p3.node OR p2.node = p4.node ) THEN p2.node ELSE NULL END ) ) AND f2.id = CASE WHEN fc1.fiber_id_1 = ' . $id . ' THEN fc1.fiber_id_2 ELSE fc1.fiber_id_1 END AND c2.id = f2.cable_id AND c_n.id ' . ($first ? '!= ' : '= ') . $to_node_id . ' LIMIT 1'; //$text.=$sql; //echo '<br>'; $result = @pg_fetch_assoc(pg_query($sql)); //print_r($result); /*echo '<pre>'; print_r($sql); echo '</pre>';*/ if ($result && empty($result['to_port'])) { //$text.=' to_fiber: '.$result['to_port']; //echo 'node: '.$to_node_id.' fiber_id: '.$last_id.' to_fiber_id: '.$id; /*echo '<pre>'; print_r($sql); echo '</pre>';*/ return find_end_port($result['to_node_id'], $result['id'], $result['to_id'], false); $first = true; } else { //$text.='<hr>'; //$text.=$result['to_node'].' '.$result['fiber_id'].' '.$result['to_fiber_id']; //echo '<br>node: '.$to_node_id.' fiber: '.$last_id.' to_fiber: '.$id.'<hr>'; //echo '<hr>'; //return $text; $sql2 = ' SELECT f1.id AS id, f1.num AS num, /*f1.descrip AS port_desc,*/ n1.id AS curr_node_id, n1.address AS curr_node_addr, n1.incorrect, n1.descrip AS curr_node_descrip, p1.id AS curr_pq_id, pt1.name AS pq_name, pt1.type AS pq_type, p1.num AS pq_num, f1.mod_color AS mod_color, f1.fib_color AS fib_color, cc1.port AS to_port, cc1.used AS port_used, cc1.descrip AS port_desc FROM ' . $table_cable . ' AS c1, ' . $table_pq . ' AS p1, ' . $table_pq_type . ' AS pt1, ' . $table_node . ' AS n1, ' . $table_fiber . ' AS f1 LEFT JOIN ' . $table_cruz_conn . ' AS cc1 JOIN ' . $table_pq . ' AS pp1 ON pp1.node ' . ($first ? '!' : '') . '=' . $to_node_id . ' ON cc1.fiber_id = f1.id AND cc1.pq_id = pp1.id WHERE f1.id = ' . $id . ' AND c1.id = f1.cable_id AND ( c1.pq_1 = p1.id OR c1.pq_2 = p1.id) AND p1.node ' . ($first ? '!= ' : '= ') . $to_node_id . ' AND p1.pq_type_id = pt1.id AND n1.id = p1.node'; /*echo '<pre>'; print_r($sql2); echo '</pre>';*/ return @pg_fetch_assoc(pg_query($sql2)); //$result2=@pg_fetch_assoc(pg_query($sql2)); //return $result2; } //echo '<pre>'.$text.'</pre>'; }