<?php
/*
* track_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;
}

////////////////////////////////// Parameters
$prm_Tracks=array();
(isset($_GET['prm_Zoom']) && !empty($_GET['prm_Zoom'])) ? $prm_Zoom=$_GET['prm_Zoom']:returnError("Error");
(isset($_GET['prm_Chr']) && !empty($_GET['prm_Chr'])) ? $prm_Chr=$_GET['prm_Chr']:returnError("Error");
(isset($_GET['prm_Pos']) && !empty($_GET['prm_Pos'])) ? $prm_Pos=$_GET['prm_Pos']:returnError("Error");
(isset($_GET['prm_Block']) && !empty($_GET['prm_Block'])) ? $prm_Block=$_GET['prm_Block'] : returnError("Error");
(isset($_GET['prm_Tracks']) && !empty($_GET['prm_Tracks'])) ? $prm_Tracks=$_GET['prm_Tracks'] : returnError("Error");

$db_range_start=$prm_Pos*_cvtZoom($prm_Zoom)-_cvtZoom($prm_Zoom);
$db_range_end=($prm_Pos+$prm_Block)*_cvtZoom($prm_Zoom);

///////////////////////////////////////////////////////////////////////////////
// Tracks
///////////////////////////////////////////////////////////////////////////////

$query = "SELECT id, name, pos, end+1, (end-pos+1), strand, others FROM sys_track WHERE track=:bd_track and chr=:bd_chr and parent=1 and end+1>:bd_start and pos<:bd_end ORDER BY pos";
$subquery = "SELECT pos, (end-pos+1), type from sys_track WHERE parent=0 and track=:bd_track and id=:bd_id ORDER BY pos";
$rtnString="";

$stmt = oci_parse($conn, $query);
$stmtSub = oci_parse($conn, $subquery);
if(!_validate_chrs($conn,$prm_Chr)){exit;}
oci_bind_by_name($stmt, ':bd_chr', $prm_Chr, -1, SQLT_CHR);
oci_bind_by_name($stmt, ':bd_start', $db_range_start, -1, SQLT_INT);
oci_bind_by_name($stmt, ':bd_end', $db_range_end, -1, SQLT_INT);

for($i=0;$i<count($prm_Tracks);$i++) {
    oci_bind_by_name($stmt, ':bd_track', $prm_Tracks[$i], -1, SQLT_CHR);
    oci_set_prefetch($stmt, 500); 
    oci_execute($stmt);
    $a1=0;
    $a2=0;
    $a3=0;
    $rtnArr=array();
    while (($rowN = oci_fetch_array($stmt, OCI_NUM + OCI_RETURN_NULLS))!= false) {
        $dest_url=$rowN[0];
        if ( $prm_Zoom == "1b" ) {
            $rs=((int)$rowN[2]/_cvtZoom($prm_Zoom))-$prm_Pos;
            $re=((int)$rowN[3]/_cvtZoom($prm_Zoom))-$prm_Pos;
            $p1=(int)((int)$rowN[2]/_cvtZoom($prm_Zoom))-$prm_Pos;
            if ((int)$rowN[3]%_cvtZoom($prm_Zoom)){
                $p=(int)((int)$rowN[3]/_cvtZoom($prm_Zoom))-$prm_Pos+1;
            } else {
                $p=(int)((int)$rowN[3]/_cvtZoom($prm_Zoom))-$prm_Pos;
            }
        }else{
            $rs=((int)$rowN[2]/_cvtZoom($prm_Zoom))-$prm_Pos+1;
            $re=((int)$rowN[3]/_cvtZoom($prm_Zoom))-$prm_Pos+1;
            $p1=(int)((int)$rowN[2]/_cvtZoom($prm_Zoom))-$prm_Pos+1;
            if ((int)$rowN[3]%_cvtZoom($prm_Zoom)){
                $p=(int)((int)$rowN[3]/_cvtZoom($prm_Zoom))-$prm_Pos+2;
            } else {
                $p=(int)((int)$rowN[3]/_cvtZoom($prm_Zoom))-$prm_Pos+1;
            }
        }
        if ( $p1 > $a1 || $a1 == null ) {
            $h=1;
            $a1=$p;
        } else if ( $p1 > $a2 || $a2 == null ) {
            $h=2;
            $a2=$p;
        } else if ( $p1 > $a3 || $a3 == null ) {
            $h=3;
            $a3=$p;
        } else {
            $h=4;
        }
        if($external_link_tag_gff != "ID" and $external_link_tag_gff != ""){
            $str_array = explode(";",$rowN[6]);
            for ($a=0; $a<count($str_array); $a++){
                $tag_val = explode("=",$str_array[$a]);
                if($tag_val[0]==$external_link_tag_gff){
                    $dest_url=$tag_val[1];
                    break;
                }else{
                    $dest_url="";
                }
            }
        }
        $arrStr="{\"id\":\"".$rowN[0]."\",\"name\":\"".$rowN[1]."\",\"pos\":\"".(int)$rowN[2]."\",\"length\":\"".(int)$rowN[4]."\",\"strand\":\"".$rowN[5]."\",\"s\":\"".$p1."\",\"e\":\"".$p."\",\"h\":\"".$h."\",\"rs\":\"".$rs."\",\"re\":\"".$re."\",\"ln\":\"".$dest_url."\"";
        $idsArr=array();
        if ( $rowN[0] != "" ) {
            oci_bind_by_name($stmtSub, ':bd_track', $prm_Tracks[$i], -1, SQLT_CHR);
            oci_bind_by_name($stmtSub, ':bd_id', $rowN[0], -1, SQLT_CHR);
            oci_execute($stmtSub);
            while (($rowS = oci_fetch_array($stmtSub, OCI_NUM + OCI_RETURN_NULLS))!= false) {
                $s=($rowS[0]-$rowN[2])/$rowN[4]*100;
                $l=$rowS[1]/$rowN[4]*100;
                if ( strstr(strtolower($rowS[2]), "utr") ) {$t="u";}
                else if ( strstr(strtolower($rowS[2]), "cds") ) {$t="c";}
                else {$t="a";}
                $idsArr[]=$t."-".$s."-".$l;
            }
        }
        $subStr="";
        if ( count($idsArr) ) {
            $subStr=",\"t\":\"";
            for($j=0;$j<count($idsArr);$j++){
                $subStr=$subStr.$idsArr[$j];
                if($j<count($idsArr)-1){$subStr=$subStr.";";}
            }
            $subStr=$subStr."\"";
        }
        $arrStr=$arrStr.$subStr."}";
        $rtnArr[]=$arrStr;
    }
    $rtnString=$rtnString."[";
    for($j=0;$j<count($rtnArr);$j++){
        $rtnString=$rtnString.$rtnArr[$j];
        if($j<count($rtnArr)-1){$rtnString=$rtnString.",";}
    }
    $rtnString=$rtnString."]";
    if($i<count($prm_Tracks)-1){$rtnString=$rtnString."|";}
}
oci_free_statement($stmt);
oci_free_statement($stmtSub);

///////////////////////////////////////////////////////////////////////////////
// Loading FASTA during zoom is '1b'
///////////////////////////////////////////////////////////////////////////////
if ( $prm_Zoom == '1b' ) {
    $paramFa1=($prm_Pos+$prm_Block);
    $queryFa = "SELECT ".$prm_Chr."_f FROM sys_fasta WHERE pos>=:bd_pos AND pos<:bd_fa ";
    $stmtFa = oci_parse($conn, $queryFa);
    oci_bind_by_name($stmtFa, ':bd_pos', $prm_Pos, -1, SQLT_INT);
    oci_bind_by_name($stmtFa, ':bd_fa', $paramFa1, -1, SQLT_INT);
    oci_execute($stmtFa);
    while (($rowF = oci_fetch_array($stmtFa, OCI_NUM + OCI_RETURN_NULLS))!= false) {
        $arrF[]="{\"f\":\"".$rowF[0]."\"}";
    }
    oci_free_statement($stmtFa);
    $rtnString=$rtnString."|[";
    for($i=0;$i<count($arrF);$i++){
        $rtnString=$rtnString.$arrF[$i];
        if($i<count($arrF)-1){$rtnString=$rtnString.",";}
    }
    $rtnString=$rtnString."]";
} else {
    $rtnString=$rtnString."|[]";
}
oci_close($conn);
echo $rtnString;

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

function _validate_chrs($conn, $prm_Chr){
    $flag = false;
    $query = "SELECT seq FROM sys_chromosome WHERE id=:bd_chr AND ROWNUM < 2 ";
    $stmt = oci_parse($conn, $query);
    oci_bind_by_name($stmt, ':bd_chr', $prm_Chr, -1, SQLT_CHR);
    oci_execute($stmt);
    while (($row = oci_fetch_array($stmt, OCI_NUM + OCI_RETURN_NULLS))!= false) {
        $flag = true ;
    }
    oci_free_statement($stmt);
    if($flag){
       return true;
    }else{
       return false;
    }
}

function _cvtZoom($str) {
    preg_match("/^[0-9]+/", $str, $r1);
    preg_match('/[b,k]$/', $str, $r2);
    $v=$r1[0];
    if($r2[0] == "b"){
        $r=1*(int)$v;
    } else {
        $r=1000*(int)$v;
    }
    return (int)$r;
}
?>

