<?php
/*
* list_oracle.php
* http://tasuke.dna.affrc.go.jp/
*
* Author: Ryutaro Itoh
*
* Copyright (c) 2013 National Institute of Agrobiological Sciences   
*
* This software is released under the MIT License.
*
* http://opensource.org/licenses/mit-license.php
*/

require '../../conf/config.php';

//////////////////////////////////  Connecting with DB
$conn = oci_connect($user, $pswd, $host."/".$db, "AL32UTF8");
if(!$conn) {
    $e = oci_error();
    error_log($e["message"]."\nConnection failed.\n");
    exit;
}

(isset($_POST['prm_ext_acc'])) ? $prm_ext_acc=$_POST['prm_ext_acc'] : returnError("Error");

////////////////////////////////// prepare 
$ids = array();
$dbs = array();
$grp = array();
$key_regist = array();
$str;
$uniq_array;
$x=0;
$y=0;

if ( file_exists( "../conf/order.conf" )) {
    $flag=0;
    $line = file_get_contents("../conf/order.conf");
    $array = explode("\n",$line);
    $uniq_array = array_unique($array);
    $x = count($uniq_array);
    $query = "SELECT accession_id FROM sys_accession where accession_id=:bd_acc ";
    $stmt = oci_parse($conn, $query);
    for($i=0;$i<$x;$i++){
        $id_test = $uniq_array[$i];
        oci_bind_by_name($stmt, ':bd_acc', $id_test, -1, SQLT_CHR);
        oci_execute($stmt);
        while (($row = oci_fetch_array($stmt, OCI_NUM + OCI_RETURN_NULLS))!= false) {
            $chk[]=$row[0];
            $key_regist[$row[0]]=1;
            $flag=1;
        }
    }
    if($flag == 1){
        $y=count($chk);
    }else{
        $y=0;
    }
    oci_free_statement($stmt);
}

if(!empty($prm_ext_acc)){
    $flag = 0;
    $chk = array();
    $ext_array = explode(",",$prm_ext_acc);
    $ext_uniq_array = array_unique($ext_array);
    $x = count($ext_uniq_array);
    for($i=0;$i<$x;$i++){
        if(empty($key_regist)){
            array_push($chk,$ext_uniq_array[$i]);
            $flag=1;
        }else{
            if(array_key_exists($ext_uniq_array[$i], $key_regist)){
                array_push($chk,$ext_uniq_array[$i]);
                $flag=1;
            }
        }
    }
    if($flag == 1){
        $y=count($chk);
    }else{
        $y=0;
    }
    $uniq_array = $ext_uniq_array;
}

if ($x>0 and $y>0 ){
    $query = "SELECT accession_id, accession_db, accession_name, variety, sub_variety, origin, origin_2, type FROM sys_accession where accession_id=:bd_acc ";
    $stmt = oci_parse($conn, $query);
    for($i=0;$i<$x;$i++){
        $ac_id = $uniq_array[$i];
        oci_bind_by_name($stmt, ':bd_acc', $ac_id, -1, SQLT_CHR);
        oci_execute($stmt);
        while (($row = oci_fetch_array($stmt, OCI_NUM + OCI_RETURN_NULLS))!= false) {
            $ids[]=$row[0];
            $dbs[]=$row[1];
            $names[]=$row[2];
            $variety[]=$row[3];
            $sub_variety[]=$row[4];
            $origins[]=$row[5];
            $origins_2[]=$row[6];
            $type[]=$row[7];
        }
    }
    oci_free_statement($stmt);
    $str="[";
    for ($i=0; $i<count($ids); $i++) {
        $str=$str."{\"id\":\"".$ids[$i]."\",\"db\":\"".$dbs[$i]."\",\"name\":\"".$names[$i].
                  "\",\"variety\":\"".$variety[$i]."\",\"grp\":\"".$sub_variety[$i].
                  "\",\"origin\":\"".$origins[$i]."\",\"origin_2\":\"".$origins_2[$i]."\",\"type\":\"".$type[$i]."\"}"; 
        if ( $i<count($ids)-1 ) {
            $str=$str.",";
        }
    }
    $str=$str."]|[";
}else{
    $query = "SELECT accession_id, accession_db, accession_name, variety, sub_variety, origin, origin_2, type FROM sys_accession order by cast(substr(accession_db,2) as number) asc";
    $stmt = oci_parse($conn, $query);
    oci_execute($stmt);
    while (($row = oci_fetch_array($stmt, OCI_NUM + OCI_RETURN_NULLS))!= false) {
        $ids[]=$row[0];
        $dbs[]=$row[1];
        $names[]=$row[2];
        $variety[]=$row[3];
        $sub_variety[]=$row[4];
        $origins[]=$row[5];
        $origins_2[]=$row[6];
        $type[]=$row[7];
    }
    oci_free_statement($stmt);
    $str="[";
    for ($i=0; $i<count($ids); $i++) {
        $str=$str."{\"id\":\"".$ids[$i]."\",\"db\":\"".$dbs[$i]."\",\"name\":\"".$names[$i].
                  "\",\"variety\":\"".$variety[$i]."\",\"grp\":\"".$sub_variety[$i].
                  "\",\"origin\":\"".$origins[$i]."\",\"origin_2\":\"".$origins_2[$i]."\",\"type\":\"".$type[$i]."\"}"; 
        if ( $i<count($ids)-1 ) {
            $str=$str.",";
        }
    }
    $str=$str."]|[";
}

////////////////////////////////////////////////////////// List of tracks
$tracks = array();
$queryT = "SELECT distinct track FROM (SELECT track, seq from sys_track order by seq) tmp";
$stmtT = oci_parse($conn, $queryT);
oci_execute($stmtT);
while (($rowT = oci_fetch_array($stmtT, OCI_NUM + OCI_RETURN_NULLS))!= false) {
    $tracks[]=$rowT[0];
}
oci_free_statement($stmtT);

$queryC = "SELECT count(seq) FROM sys_track WHERE parent=0 AND track=:bd_track ";
$stmtC = oci_parse($conn, $queryC);
$queryTY = "SELECT type FROM sys_track WHERE track=:bd_track ";
$stmtTY = oci_parse($conn, $queryTY);
for ($i=0; $i<count($tracks); $i++) {
    $objectType = "";
    $viewType = "general";
    oci_bind_by_name($stmtC, ':bd_track', $tracks[$i], -1, SQLT_CHR);
    oci_execute($stmtC);
    oci_bind_by_name($stmtTY, ':bd_track', $tracks[$i], -1, SQLT_CHR);
    oci_execute($stmtTY);
    while (($rowTY = oci_fetch_array($stmtTY, OCI_NUM + OCI_RETURN_NULLS))!= false) {
        $objectType = $rowTY[0];
    }
    while (($rowC = oci_fetch_array($stmtC, OCI_NUM + OCI_RETURN_NULLS))!= false) {
        $color = "#000000";
        if ( $rowC[0] > 0 ) {
          $color = "#000080";
        }
        if ($objectType == "DE") {
            $color = "#FF0080";
            $viewType = "DE";
        }
        $str=$str."{\"track\":\"".$tracks[$i]."\",\"p\":\"1\",\"h\":\"4\",\"c\":\"".$color."\",\"s\":\"".$i."\",\"t\":\"".$viewType."\"}";
        if ( $i<count($tracks)-1 ) {
            $str=$str.",";
        }
    }
}
oci_free_statement($stmtC);
oci_free_statement($stmtTY);
$str=$str."]|[";

////////////////////////////////////////////////////////// List of Cond
$str=$str."{\"id\":\"".$defaultCond."\"},";
$queryL = "select condition_id from sys_condition order by table_id asc";
$stmtL = oci_parse($conn, $queryT);
oci_execute($stmtL);
while (($rowCD0 = oci_fetch_array($stmtL, OCI_NUM + OCI_RETURN_NULLS))!= false) {
    $str=$str."{\"id\":\"".$rowCD0[0]."\"},";
}
oci_free_statement($stmtL);
$str = substr($str, 0, -1);

$str=$str."]";
echo $str;

////////////////////////////////////////////////////////// Function
function returnError($str){
    echo $str;
}

?>
