<?php
/*
* list.php
* http://tasuke.dna.affrc.go.jp/
*
* Author: Ryutaro Itoh
*
* Copyright (c) 2013 National Institute of Agrobiological Sciences   
*
*/

require '../conf/config.php';
require './common.php';
//////////////////////////////////  Connecting with DB
if( !$conn = new mysqli($host, $user, $pswd, $db)) {
    error_log("Connection failed.\n");
    exit;
}

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

////////////////////////////////// prepare 
$ids = array();
$dbs = array();
$grp = array();
$key_regist = array();	// order.conf && db exist accs(uniq)
$str;
$uniq_array;
$x=0;	// order.conf acc count.
$y=0;	// order.conf && db exist acc count.

//$startOrder = microtime(true);
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= ? ";
    $stmt = $conn->stmt_init();
    $stmt->prepare($query);
    for($i=0;$i<$x;$i++){
        $id_test = $uniq_array[$i];
        $stmt->bind_param("s", $id_test); 
        $stmt->execute();
        $stmt->store_result();
        $stmt->bind_result($row0);
        while ($stmt->fetch()) {
            $chk[]=$row0;
            $key_regist[$row0]=1;
            $flag=1;
        }
    }
    if($flag == 1){
        $y=count($chk);
    }else{
        $y=0;
    }
    $stmt->free_result();
    $stmt->close();
}
//error_log("Order:". (microtime(true) - $startOrder));

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)){
            $chk[] = $ext_uniq_array[$i];
//            array_push($chk,$ext_uniq_array[$i]);
            $flag=1;
        }else{
            if(array_key_exists($ext_uniq_array[$i], $key_regist)){
                $chk[] = $ext_uniq_array[$i];
//                array_push($chk,$ext_uniq_array[$i]);
                $flag=1;
            }
        }
    }
    if($flag == 1){
        $y=count($chk);
    }else{
        $y=0;
    }
    $uniq_array = $ext_uniq_array;
}

//$startAcc = microtime(true);
if ($x>0 and $y>0 ){
    $query = "SELECT accession_id, accession_db, accession_name, variety, sub_variety, origin, origin_2, type, other_1, other_2 FROM sys_accession where accession_id= ? ";
    $stmt = $conn->stmt_init();
    $stmt->prepare($query);
    for($i=0;$i<$x;$i++){
        $ac_id = $uniq_array[$i];
        $stmt->bind_param("s", $ac_id); 
        $stmt->execute();
        $stmt->store_result();
        $stmt->bind_result($row0,$row1,$row2,$row3,$row4,$row5,$row6,$row7,$row8,$row9);
        while ($stmt->fetch()) {
            $ids[]=$row0;
            $dbs[]=$row1;
            $names[]=$row2;
            $variety[]=$row3;
            $sub_variety[]=$row4;
            $origins[]=$row5;
            $origins_2[]=$row6;
            $type[]=$row7;
            $other_1[]=$row8;
            $other_2[]=$row9;
        }
    }
    $stmt->free_result();
    $stmt->close();
    $str="[";

    $ids_cnt = count($ids);
    for ($i=0; $i<$ids_cnt; $i++) {
	$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]."\",\"other_1\":\"".$other_1[$i]."\",\"other_2\":\"".$other_2[$i]."\"}";
	if ( $i<$ids_cnt-1 ) {
	    $str .= ",";
	}
    }
    $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]."\",\"other_1\":\"".$other_1[$i]."\",\"other_2\":\"".$other_2[$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, other_1, other_2 FROM sys_accession order by cast(substring(accession_db, 2) as unsigned) asc";
    $stmt = $conn->stmt_init();
    $stmt->prepare($query);
    $stmt->execute();
    $stmt->store_result();
    $stmt->bind_result($row0,$row1,$row2,$row3,$row4,$row5,$row6,$row7,$row8,$row9);
    while ($stmt->fetch()) {
        $ids[]=$row0;
        $dbs[]=$row1;
        $names[]=$row2;
        $variety[]=$row3;
        $sub_variety[]=$row4;
        $origins[]=$row5;
        $origins_2[]=$row6;
        $type[]=$row7;
        $other_1[]=$row8;
        $other_2[]=$row9;
    }
    $stmt->free_result();
    $stmt->close();
    $str="[";

    $ids_cnt = count($ids);
    for ($i=0; $i<count($ids); $i++) {
	$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].
                  "\",\"type\":\"".$type[$i]."\",\"other_1\":\"".$other_1[$i]."\",\"other_2\":\"".$other_2[$i]."\"}";
	if ( $i<$ids_cnt-1 ) {
	    $str .= ",";
	}
    }
    $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].
                  "\",\"type\":\"".$type[$i]."\",\"other_1\":\"".$other_1[$i]."\",\"other_2\":\"".$other_2[$i]."\"}";
        if ( $i<count($ids)-1 ) {
            $str=$str.",";
        }
    }
    $str=$str."]|[";
*/
}
//error_log("GetAccs:". (microtime(true) - $startAcc));

////////////////////////////////////////////////////////// List of tracks
//$startTrack = microtime(true);
$queryT = "select min(seq) as minseq,track from sys_track group by track order by minseq";
//$queryT = "SELECT DISTINCT track FROM sys_track order by seq";
//$queryT = "SELECT DISTINCT track FROM sys_track";
$stmtT = $conn->stmt_init();
$stmtT->prepare($queryT);
$stmtT->execute();
$stmtT->store_result();
$stmtT->bind_result($rowT0, $rowT1);
$tracks = array();

while ($stmtT->fetch()) {
    $tracks[]=$rowT1;
}
$stmtT->free_result();
$stmtT->close();
//error_log("GetTrackToTrack:". (microtime(true) - $startTrack));

$queryC = "SELECT count(1) as cnt FROM DUAL WHERE exists(SELECT seq FROM sys_track WHERE parent=0 AND track = ?)";
$stmtC = $conn->stmt_init();
$stmtC->prepare($queryC);

$queryTY = "SELECT type FROM sys_track WHERE track= ? limit 1";
$stmtTY = $conn->stmt_init();
$stmtTY->prepare($queryTY);

$queryIsGene = "SELECT count(1) as cnt FROM DUAL where exists(SELECT 1 FROM sys_track where type in('gene','transcript','CDS','exon','mRNA') and track=?)";
$stmtIsGene = $conn->stmt_init();
$stmtIsGene->prepare($queryIsGene);

$tracks_cnt = count($tracks);
for ($i=0; $i<$tracks_cnt; $i++) {
    $objectType = "";
    $viewType = "general";
    $stmtTY->bind_param("s", $tracks[$i]); 
    $stmtTY->execute();
    $stmtTY->store_result();
    $stmtTY->bind_result($rowTY);
    while ($stmtTY->fetch()) {
        $objectType = $rowTY;
	break;
    }
//error_log("GetTrackType(". $tracks[$i]. "=". $rowTY. "):". (microtime(true) - $startTrack));

    $isGene = 0;
    $stmtIsGene->bind_param("s", $tracks[$i]);
    $stmtIsGene->execute();
    $stmtIsGene->store_result();
    $stmtIsGene->bind_result($rowIsGene);
    if($stmtIsGene->fetch()) {
      $isGene = $rowIsGene;
    }

    $stmtC->bind_param("s", $tracks[$i]);
    $stmtC->execute();
    $stmtC->store_result();
    $stmtC->bind_result($rowC);
    if($stmtC->fetch()) {
        $color = "#000000";
        if ( $rowC > 0 ) {
          $color = "#000080";
        }
        if ($objectType == "DE") {
            $color = "#FF0080";
            $viewType = "DE";
        }
//	$str .= "{\"track\":\"".$tracks[$i]."\",\"p\":\"1\",\"h\":\"4\",\"c\":\"".$color."\",\"s\":\"".$i."\",\"t\":\"".$viewType."\"}";
        $str .= "{\"track\":\"".$tracks[$i]."\",\"p\":\"1\",\"h\":\"4\",\"c\":\"".$color."\",\"s\":\"".$i."\",\"t\":\"".$viewType."\",\"g\":\"". $isGene. "\"}";
	if ( $i<$tracks_cnt-1 ) {
	    $str .= ",";
	}
    }
//    error_log("GetTrackCount(". $tracks[$i]. "=". $rowC. "):". (microtime(true) - $startTrack));
}
$stmtC->free_result();
$stmtC->close();
$stmtTY->free_result();
$stmtTY->close();
$stmtIsGene->free_result();
$stmtIsGene->close();

$str .= "]|[";
//$str=$str."]|[";
//error_log("GetTrack:". (microtime(true) - $startTrack));

////////////////////////////////////////////////////////// List of Cond
//$startPE = microtime(true); 

$str .= "{\"id\":\"".$defaultCond."\"},";
if($result = $conn->query("select condition_id from sys_condition order by table_id asc")) {
    while ($rowCD0 = $result->fetch_row()) {
	$str .= "{\"id\":\"".$rowCD0[0]."\"},";
    }
    $result->close();
}
$str = substr($str, 0, -1);
$str .= "]|[";
/*
$str=$str."{\"id\":\"".$defaultCond."\"},";
if($result = $conn->query("select condition_id from sys_condition order by table_id asc")) {
    while ($rowCD0 = $result->fetch_row()) {
        $str=$str."{\"id\":\"".$rowCD0[0]."\"},";
    }
    $result->close();
}
$str = substr($str, 0, -1);
$str=$str."]|[";
*/

$queryPE = "SELECT a.accession_id, phenotype, expression FROM sys_phenotype_expression a, sys_accession b, sys_phenotype c WHERE a.accession_id = b.accession_id and a.phenotype_id = c.phenotype_id order by cast(substring(accession_db, 2) as unsigned) asc";
$stmtPE = $conn->stmt_init();
$stmtPE->prepare($queryPE);
$stmtPE->execute();
$stmtPE->store_result();
$stmtPE->bind_result($row0,$row1,$row2);

$ids = array();
$phenotypes = array();
$expressions = array();
while ($stmtPE->fetch()) {
	$ids[]=$row0;
	$phenotypes[]=$row1;
	$expressions[]=$row2;
}
$stmtPE->free_result();
$stmtPE->close();

$ids_cnt = count($ids);
for ($i=0; $i<$ids_cnt; $i++) {
	$str .= "{\"id\":\"".$ids[$i]."\",\"phenotype\":\"".$phenotypes[$i]."\",\"expression\":\"".$expressions[$i]."\"}";
	if ( $i<$ids_cnt-1 ) {
		$str .= ",";
	}
}
$str .= "]|[";
/*
for ($i=0; $i<count($ids); $i++) {
	$str=$str."{\"id\":\"".$ids[$i]."\",\"phenotype\":\"".$phenotypes[$i]."\",\"expression\":\"".$expressions[$i]."\"}";
	if ( $i<count($ids)-1 ) {
		$str=$str.",";
	}
}
$str=$str."]|[";
*/
//error_log("GetPE:". (microtime(true) - $startPE));

/*
//$startP = microtime(true);
$queryP = "SELECT a.accession_id, c.phenotype, PMID, title, author, abstract FROM sys_phenotype_paper a, sys_accession b, sys_phenotype c WHERE a.accession_id = b.accession_id and a.phenotype_id = c.phenotype_id order by cast(substring(accession_db, 2) as unsigned) asc";
$stmtP = $conn->stmt_init();
$stmtP->prepare($queryP);
$stmtP->execute();
$stmtP->store_result();
$stmtP->bind_result($row0, $row1, $row2, $row3, $row4, $row5);
$phenotype_paper_array_array = array();
while ($stmtP->fetch()) {
	if(!isset($phenotype_paper_array_array[$row0])){
		$phenotype_paper_array_array[$row0][$row1] = array();
	}
	array_push($phenotype_paper_array_array[$row0][$row1], array($row2, $row3, $row4, $row5));
}
$stmtP->free_result();
$stmtP->close();

$ids_cnt = count($ids);
for ($i=0; $i<$ids_cnt; $i++) {
	$str .= "{\"id\":\"".$ids[$i]."\",\"phenotype\":\"".$phenotypes[$i]."\",\"paper\":[";
//	$str=$str."{\"id\":\"".$ids[$i]."\",\"phenotype\":\"".$phenotypes[$i]."\",\"paper\":[";
	if(!array_key_exists($ids[$i], $phenotype_paper_array_array) || 
		!array_key_exists($phenotypes[$i], $phenotype_paper_array_array[$ids[$i]])) { continue; }
	foreach($phenotype_paper_array_array[$ids[$i]][$phenotypes[$i]] as $phenotype_paper_array){
		$str .= '{"PMID":"' . $phenotype_paper_array[0] . '",';
		$str .= '"title":"' . $phenotype_paper_array[1] . '",';
		$str .= '"author":"' . $phenotype_paper_array[2] . '",';
		$str .= '"abst":"' . $phenotype_paper_array[3] . '"},';
	}
	$str = rtrim($str, ',');
	$str .= "]},";
}
$str = rtrim($str, ',');
*/
$str .= "]";
//$str=$str."]";
//error_log("GetP:". (microtime(true) - $startP));

//file_put_contents("/tmp/list_php_new", $str);

echo $str;

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

?>
