<?php
/*
* data_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_Ref']) && !empty($_POST['prm_Ref'])) ? $prm_Ref=$_POST['prm_Ref']:returnError("Error");
(isset($_POST['prm_Zoom']) && !empty($_POST['prm_Zoom'])) ? $prm_Zoom=$_POST['prm_Zoom']:returnError("Error");
(isset($_POST['prm_Chr']) && !empty($_POST['prm_Chr'])) ? $prm_Chr=$_POST['prm_Chr']:returnError("Error");
(isset($_POST['prm_Pos']) && !empty($_POST['prm_Pos'])) ? $prm_Pos=$_POST['prm_Pos']:returnError("Error");
(isset($_POST['prm_QUAL']) && !empty($_POST['prm_QUAL'])) ? $prm_QUAL=$_POST['prm_QUAL']:returnError("Error");
(isset($_POST['prm_DP']) && !empty($_POST['prm_DP'])) ? $prm_DP=$_POST['prm_DP']:returnError("Error");
(isset($_POST['prm_DP4']) && !empty($_POST['prm_DP4'])) ? $prm_DP4=$_POST['prm_DP4']:returnError("Error");
(isset($_POST['prm_Block']) && !empty($_POST['prm_Block'])) ? $prm_Block=$_POST['prm_Block'] : returnError("Error");
(isset($_POST['prm_Lists']) && !empty($_POST['prm_Lists'])) ? $prm_Lists=$_POST['prm_Lists'] : returnError("Error");
(isset($_POST['prm_width']) && !empty($_POST['prm_width'])) ? $prm_width=$_POST['prm_width'] : returnError("Error");

$prm_width=(int)$prm_width;
$db_range_end=$prm_Pos+$prm_Block;
list($zoom_v,$zoom_r,$zoom_chr)=_cvtZoom($prm_Zoom, $prm_Chr);
if(!_validate_each_acc($prm_Lists)){exit;}
if(!_validate_ref_acc($prm_Ref)){exit;}
if(!_validate_chrs($conn,$prm_Chr)){exit;}
$paramVref1 = $prm_Chr."_b";
$row_cntRef=0;
$row_cntAc=0;
if($customTrack=="enabled"){
    $customTrack=true;
}else{
    $customTrack=false;
}

if ( $prm_Ref != "Reference" ) {
    $query = "SELECT pos FROM ".$prm_Ref."_accession WHERE ROWNUM<2";
    $stmt = oci_parse($conn, $query);
    oci_execute($stmt);
    while (($row = oci_fetch_array($stmt, OCI_NUM + OCI_RETURN_NULLS))!= false) {
        $row_cntRef++;
    }
    oci_free_statement($stmt);
}
$rtnString="";
$minNumReads=1;
$tofix=1;
$normReads=1;
$doCUSTOM=false;
$dostmtRDP=false;
$cstm_tables = _get_condtable($conn);
if($customTrack and $customTrackNormByRead == "enabled"){
    $query = "SELECT min(num_reads) FROM sys_accession WHERE num_reads > 1";
    $stmt = oci_parse($conn, $query);
    oci_execute($stmt);
    while (($row = oci_fetch_array($stmt, OCI_NUM + OCI_RETURN_NULLS))!= false) {
        $minNumReads=$row[0];
    }
    $tofix=$minNumReads;
    oci_free_statement($stmt);
}
    
for($j=0;$j<count($prm_Lists);$j++){
    $stmt;
    $stmtHap;
    $stmtDP;
    $stmtRDP;
    $stmtV;
    $stmtVr;
    $accid=$prm_Lists[$j];
    $row_cntAc=0;
    $row_cntAcCstm=array();
    $numAllReads=1;
    
    $query = "SELECT pos FROM ".$accid."_accession WHERE ROWNUM<2";
    $stmt = oci_parse($conn, $query);
    oci_execute($stmt);
    while (($row = oci_fetch_array($stmt, OCI_NUM + OCI_RETURN_NULLS))!= false) {
        $row_cntAc++;
    }
    oci_free_statement($stmt);
    if($customTrack){
        for($z=0; $z<count($cstm_tables); $z++){
            $val = 0;
            $query = "select pos from ".$accid."_accession_cstm".$cstm_tables[$z]." ROWNUM<2";
            $stmt = oci_parse($conn, $query);
            oci_execute($stmt);
            while (($row = oci_fetch_array($stmt, OCI_NUM + OCI_RETURN_NULLS))!= false) {
                $val = 1;
            }
            $row_cntAcCstm[] = $val;
            oci_free_statement($stmt);
        }
        
        $query = "SELECT num_reads FROM sys_accession WHERE accession_db=:bd_acc";
        oci_bind_by_name($stmt, ':bd_acc', $accid, -1, SQLT_CHR);
        $stmt = oci_parse($conn, $query);
        oci_execute($stmt);
        while (($row = oci_fetch_array($stmt, OCI_NUM + OCI_RETURN_NULLS))!= false) {
            $numAllReads=$row[0];
        }
        oci_free_statement($stmt);
        
        $query = "SELECT table_name FROM all_tables WHERE table_name like Upper('".$accid."_accession_cstm')";
        $stmt = oci_parse($conn, $query);
        oci_execute($stmt);
        while (($row = oci_fetch_array($stmt, OCI_NUM + OCI_RETURN_NULLS))!= false) {
            $doCUSTOM=true;
        }
        oci_free_statement($stmt);
        if($customTrackNormByRead == "enabled"){
            if($numAllReads==0){
                $numAllReads=1;
                $tofix=1;
            }
            if((int)$tofix==0){$tofix==1;}
            $normReads=$tofix/$numAllReads;
        }
    }

    //query
    $queryDP = "SELECT sum(".$zoom_chr.")/".$zoom_v." sum FROM ".$accid."_accession WHERE pos > :bd_start AND pos <= :bd_end ";
    //$queryCstm = "SELECT sum(".$zoom_chr.")/".$zoom_v."*".$normReads." sum FROM ".$accid."_accession_cstm WHERE pos > :bd_start AND pos <= :bd_end ";
    $queryVblk = "SELECT variant, count(variant), max(eff_grp) FROM ".$accid."_variant WHERE chr= :bd_chr AND pos > :bd_start AND pos <= :bd_end AND QUAL >= :bd_qual AND DP >= :bd_dp AND (DP3+DP4) >= :bd_dp4 GROUP BY variant";//////
    $queryV1b = "SELECT variant, count(variant), max(eff_grp), REF, ALT, GT FROM ".$accid."_variant 
                    WHERE chr= :bd_chr AND pos > :bd_start AND pos <= :bd_end AND QUAL >= :bd_qual AND DP >= :bd_dp AND (DP3+DP4) >= :bd_dp4 GROUP BY variant,ref,alt,gt";
    $queryVref = "SELECT var.variant, count(depth.pos) FROM ".$prm_Ref."_accession depth, 
                        (SELECT pos, alt, gt, variant FROM 
                        (SELECT pos, alt, gt, variant FROM ".$accid."_variant 
                        WHERE QUAL>= :bd_qual_1 AND DP >= :bd_dp_1 AND (DP3+DP4) >= :bd_dp4_1 AND pos > :bd_start_1 AND pos <= :bd_end_1 AND chr= :bd_chr_1 
                    UNION ALL 
                        SELECT pos, alt, gt, variant FROM ".$prm_Ref."_variant 
                        WHERE QUAL >= :bd_qual_2 AND DP >= :bd_dp_2 AND (DP3+DP4) >= :bd_dp4_2 AND pos > :bd_start_2 AND pos <= :bd_end_2 AND chr= :bd_chr_2 ) a 
                        group by a.pos, a.alt, a.gt,a.variant having count(*)<2 AND a.variant is not NULL) var 
                        where depth.pos=var.pos 
                        AND chr01_b >= 1 group by var.variant";//////
    $queryVrefNull = "SELECT var.variant, count(depth.pos) FROM ".$prm_Ref."_accession depth, 
                        (SELECT pos, alt, gt, variant FROM 
                        (SELECT pos, alt, gt, variant FROM ".$accid."_variant 
                        WHERE QUAL>= :bd_qual_1 AND DP >= :bd_dp_1 AND (DP3+DP4) >= :bd_dp4_1 AND pos > :bd_start_1 AND pos <= :bd_end_1 AND chr= :bd_chr_1 
                    UNION ALL 
                        SELECT pos, alt, gt, variant FROM ".$prm_Ref."_variant 
                        WHERE QUAL >= :bd_qual_2 AND DP >= :bd_dp_2 AND (DP3+DP4) >= :bd_dp4_2 AND pos > :bd_start_2 AND pos <= :bd_end_2 AND chr= :bd_chr_2 ) a 
                        group by a.pos, a.alt, a.gt,a.variant having count(*)<2 AND a.variant is not NULL) var 
                        where depth.pos=var.pos (+)
                        AND chr01_b >= 1 group by var.variant";//////
    $queryV="";
    $queryhap="SELECT round(((pos-:bd_start_1)/:bd_p1)*:bd_p2), ALT, GT, EFF_GRP, REF, variant FROM ".$accid."_variant WHERE chr= :bd_chr AND pos >= :bd_start_2 AND pos <= :bd_end AND QUAL >= :bd_qual AND DP >= :bd_dp AND (DP3+DP4) >= :bd_dp4";
    $end=($zoom_v*$zoom_r*$db_range_end)-($zoom_v*$zoom_r);
    $start=$end-($zoom_v*$zoom_r*($prm_Block-1))+1-($zoom_v*$zoom_r);
    $p1=($zoom_v*$zoom_r)*($prm_Block);
    $p2=($prm_width)*($prm_Block);
    $array=array();
    $gtarray=array();
    $igtarray=array();
    $iarray=array();
    $darray=array();
    
    if($zoom_v*$zoom_r <5000){
        $stmtHap = oci_parse($conn, $queryhap);
        oci_bind_by_name($stmtHap, ':bd_start_1', $start, -1, SQLT_INT);
        oci_bind_by_name($stmtHap, ':bd_p1', $p1, -1, SQLT_INT);
        oci_bind_by_name($stmtHap, ':bd_p2', $p2, -1, SQLT_INT);
        oci_bind_by_name($stmtHap, ':bd_chr', $prm_Chr, -1, SQLT_CHR);
        oci_bind_by_name($stmtHap, ':bd_start_2', $start, -1, SQLT_INT);
        oci_bind_by_name($stmtHap, ':bd_end', $end, -1, SQLT_INT);
        oci_bind_by_name($stmtHap, ':bd_qual', $prm_QUAL, -1, SQLT_INT);
        oci_bind_by_name($stmtHap, ':bd_dp', $prm_DP, -1, SQLT_INT);
        oci_bind_by_name($stmtHap, ':bd_dp4', $prm_DP4, -1, SQLT_INT);
        oci_set_prefetch($stmtHap, 500); 
        oci_execute($stmtHap);
        $blknum=0;
        $nextflag=0;
        while (($row = oci_fetch_array($stmtHap, OCI_NUM + OCI_RETURN_NULLS))!= false) {
            $blknum=(int)($row[0]/$prm_width);
            if($row[0] % $prm_width ==0){$blknum--;}
            if(($row[0]-$blknum*$prm_width)==$prm_width){$blknum++;}
            $alts = explode(",", $row[1]);
            if($row[5]==="s"){
                if(array_key_exists($blknum,$array)){//inner block
                    if(!array_key_exists($row[0],$gtarray)){
                        $array[$blknum]=$array[$blknum]."%".($row[0]-$blknum*$prm_width)."-".$alts[0]."-".$row[2]."-".$row[3];
                    }else{
                        if((int)substr($array[$blknum],-1) < $row[3]){
                            $array[$blknum]=substr($array[$blknum],0,-1).$row[3];
                        }
                    }
                    $gtarray[$row[0]]=$row[3];
                }else{
                    $array[$blknum]=($row[0]-$blknum*$prm_width)."-".$alts[0]."-".$row[2]."-".$row[3];
                    $gtarray[$row[0]]=$row[3];
                }
            }else if($row[5]==="i"){
                $reflen = strlen($row[4]);
                $altlen = strlen($alts[0]);
                $len_i = $altlen - $reflen;
                if(array_key_exists($blknum,$iarray)){//inner block
                    if(!array_key_exists($row[0],$igtarray)){
                        $iarray[$blknum]=$iarray[$blknum]."%".($row[0]-$blknum*$prm_width)."-".substr($alts[0], 1, $len_i)."-".$row[2]."-".$row[3];
                    }else{
                        if((int)substr($iarray[$blknum],-1) < $row[3]){
                            $iarray[$blknum]=substr($iarray[$blknum],0,-1).$row[3];
                        }
                    }
                    $igtarray[$row[0]]=$row[3];
                }else{
                    $iarray[$blknum]=($row[0]-$blknum*$prm_width)."-".substr($alts[0], 1, $len_i)."-".$row[2]."-".$row[3];
                    $igtarray[$row[0]]=$row[3];
                }
            }else if ($row[5]==="d"){
                $reflen = strlen($row[4]);
                $altlen = strlen($alts[0]);
                $len_d = $reflen - $altlen;
                if(array_key_exists($blknum,$darray)){//inner block
                    if(!array_key_exists($row[0],$igtarray)){
                        $darray[$blknum]=$darray[$blknum]."%".($row[0]-$blknum*$prm_width)."-".substr($row[4], 1, $len_d)."-".$row[2]."-".$row[3];
                    }else{
                        if((int)substr($darray[$blknum],-1) < $row[3]){
                            $darray[$blknum]=substr($darray[$blknum],0,-1).$row[3];
                        }
                    }
                    $igtarray[$row[0]]=$row[3];
                }else{
                    $darray[$blknum]=($row[0]-$blknum*$prm_width)."-".substr($row[4], 1, $len_d)."-".$row[2]."-".$row[3];
                    $igtarray[$row[0]]=$row[3];
                }
            }
        }
    }
    if($row_cntAc!=0){
        $stmtDP = oci_parse($conn, $queryDP);
    }
    /*if($row_cntAcCstm!=0){
        $stmtRDP = oci_parse($conn, $queryCstm);
    }*/
    // Over 1b
    if($zoom_r*$zoom_v > 1){
        $queryV=$queryVblk;
     // 1b mode: + ALT, REF
    } else {
        $queryV=$queryV1b;
    }
    if ( $prm_Ref == "Reference" ) {
        $stmtV = oci_parse($conn, $queryV);
    }else{
        if($row_cntRef==0){
            $stmtVr = oci_parse($conn, $queryVrefNull);
        }else{
            $stmtVr = oci_parse($conn, $queryVref);
        }
    }
    $rtnString=$rtnString.$accid."|[";
    for($i=$prm_Pos;$i<$db_range_end;$i++){
        $rtnString=$rtnString."{";
        ////////////////////////////////////////////////// SELECT TABLE DEPTH
        $e=$zoom_v*$i;
        $s=$e-$zoom_v;
        if($row_cntAc!=0){
            oci_bind_by_name($stmtDP, ':bd_start', $s, -1, SQLT_INT);
            oci_bind_by_name($stmtDP, ':bd_end', $e, -1, SQLT_INT);
            oci_execute($stmtDP);
            while (($row = oci_fetch_array($stmtDP, OCI_NUM + OCI_RETURN_NULLS))!= false) {
                if ( $row[0] != "" ) {
                    $rtnString=$rtnString."\"dp\":\"".(int)$row[0]."\"";
                } else {
                    $rtnString=$rtnString."\"dp\":\"\"";
                }
            }
        }else{
            $rtnString=$rtnString."\"dp\":\"n\"";
        }
        if($customTrack and $doCUSTOM){
            $string_buffer = "";
            for($z=0; $z<count($cstm_tables); $z++){
                if($row_cntAcCstm[$z] == 0){
                    $string_buffer .= "n%";
                }else{
                    $dostmtRDP = true;
                    $isEmptyTable = 1;
                    $queryCstm = "SELECT sum(".$zoom_chr.")/".$zoom_v."*".$normReads." as sum FROM ".$accid."_accession_cstm".$cstm_tables[$z]." WHERE pos > :bd_start and pos <= :bd_end ";
                    $stmtRDP = oci_parse($conn, $queryCstm);
                    oci_bind_by_name($stmtRDP, ':bd_start', $s, -1, SQLT_INT);
                    oci_bind_by_name($stmtRDP, ':bd_end', $e, -1, SQLT_INT);
                    oci_execute($stmtRDP);
                    while (($row = oci_fetch_array($stmtRDP, OCI_NUM + OCI_RETURN_NULLS))!= false) {
                        $isEmptyTable = 0;
                        if ( $row[0] != "" ) {
                            $string_buffer .= (int)$row[0]."%";
                        } else {
                            $string_buffer .= "%";
                        }
                    }
                    if($isEmptyTable != 0){ $string_buffer .= "%";}
                    oci_free_statement($stmtRDP);
                }
            }
            $string_buffer = substr($string_buffer, 0, -1);
            $rtnString=$rtnString.",\"rdp\":\"".$string_buffer."\"";
        }
        ////////////////////////////////////////////////// SELECT TABLE VARIANT
        $e=$zoom_v*$zoom_r*$i;
        $s=$e-$zoom_v*$zoom_r;
        if ( $prm_Ref == "Reference" ) {
            oci_bind_by_name($stmtV, ':bd_chr', $prm_Chr, -1, SQLT_CHR);
            oci_bind_by_name($stmtV, ':bd_start', $s, -1, SQLT_INT);
            oci_bind_by_name($stmtV, ':bd_end', $e, -1, SQLT_INT);
            oci_bind_by_name($stmtV, ':bd_qual', $prm_QUAL, -1, SQLT_INT);
            oci_bind_by_name($stmtV, ':bd_dp', $prm_DP, -1, SQLT_INT);
            oci_bind_by_name($stmtV, ':bd_dp4', $prm_DP4, -1, SQLT_INT);
            oci_set_prefetch($stmtV, 500); 
            oci_execute($stmtV);
            while (($row = oci_fetch_array($stmtV, OCI_NUM + OCI_RETURN_NULLS))!= false) {
                if($zoom_r*$zoom_v > 1){
                    $rtnString=$rtnString.",\"".$row[0]."\":\"".$row[1]."-".$row[2]."\"";
                } else {
                    $alts = explode(",", $row[4]);
                    $rtnString=$rtnString.",\"".$row[0]."\":\"".$row[1]."-".$row[2]."-".$row[3]."-".$alts[0]."-".$row[5]."\"";
                }
            }
        } else {
            oci_bind_by_name($stmtVr, ':bd_qual_1', $prm_QUAL, -1, SQLT_INT);
            oci_bind_by_name($stmtVr, ':bd_dp_1', $prm_DP, -1, SQLT_INT);
            oci_bind_by_name($stmtVr, ':bd_dp4_1', $prm_DP4, -1, SQLT_INT);
            oci_bind_by_name($stmtVr, ':bd_start_1', $s, -1, SQLT_INT);
            oci_bind_by_name($stmtVr, ':bd_end_1', $e, -1, SQLT_INT);
            oci_bind_by_name($stmtVr, ':bd_chr_1', $prm_Chr, -1, SQLT_CHR);
            oci_bind_by_name($stmtVr, ':bd_qual_2', $prm_QUAL, -1, SQLT_INT);
            oci_bind_by_name($stmtVr, ':bd_dp_2', $prm_DP, -1, SQLT_INT);
            oci_bind_by_name($stmtVr, ':bd_dp4_2', $prm_DP4, -1, SQLT_INT);
            oci_bind_by_name($stmtVr, ':bd_start_2', $s, -1, SQLT_INT);
            oci_bind_by_name($stmtVr, ':bd_end_2', $e, -1, SQLT_INT);
            oci_bind_by_name($stmtVr, ':bd_chr_2', $prm_Chr, -1, SQLT_CHR);
            oci_set_prefetch($stmtVr, 500); 
            oci_execute($stmtVr);
            while (($row = oci_fetch_array($stmtVr, OCI_NUM + OCI_RETURN_NULLS))!= false) {
                if($zoom_r*$zoom_v > 1){
                    $rtnString=$rtnString.",\"".$row[0]."\":\"".$row[1]."-\"";
                } else {
                    $rtnString=$rtnString.",\"".$row[0]."\":\"".$row[1]."---\"";
                }
            }
        }
        if(array_key_exists($i-$prm_Pos,$array)){
            $rtnString=$rtnString.",\"hs\":\"".$array[$i-$prm_Pos]."\"";
        }
        if(array_key_exists($i-$prm_Pos,$iarray)){
            $rtnString=$rtnString.",\"hi\":\"".$iarray[$i-$prm_Pos]."\"";
        }
        if(array_key_exists($i-$prm_Pos,$darray)){
            $rtnString=$rtnString.",\"hd\":\"".$darray[$i-$prm_Pos]."\"";
        }
        $rtnString=$rtnString."}";
        if ($i<($db_range_end-1)) {
            $rtnString=$rtnString.",";
        }
    }
    $rtnString=$rtnString."]";
    if($j<count($prm_Lists)-1){
        $rtnString=$rtnString.";";
    }
    unset($array);
    unset($gtarray);
}
if($row_cntAc!=0){
    oci_free_statement($stmtDP);
}
if($dostmtRDP == true and $customTrack){
    oci_free_statement($stmtRDP);
}
if ( $prm_Ref != "Reference" ) {
    oci_free_statement($stmtVr);
} else{
    oci_free_statement($stmtV);
}
echo $rtnString;

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

function _cvtZoom($str1, $str2) {
    preg_match("/^[0-9]+/", $str1, $r1);
    preg_match('/[b,k]$/', $str1, $r2);
    $v=$r1[0];
    if($r2[0] == "b"){
        $r=1;
        $c=$str2."_b";
    } else {
        $r=1000;
        $c=$str2."_k";
    }
    return array((int)$v,(int)$r, $c);
}

function _get_condtable($conn){
    $res = array();
    $res[] = "";
    $query = "select table_id from sys_condition order by table_id asc";
    $stmt = oci_parse($conn, $query);
    oci_execute($stmt);
    while (($row = oci_fetch_array($stmt, OCI_NUM + OCI_RETURN_NULLS))!= false) {
        $res[] = "_".$row[0];
    }
    oci_free_statement($stmt);
    return $res;
}

function _validate_each_acc($list){
    $num = count($list);
    for($a=0;$a<$num;$a++){
        if (preg_match("/d\d+$/i", $list[$a])) {
            //pass
        }else{
           error_log("Invalid accession ids");
           return false;
        }
    }
    return true;
}

function _validate_ref_acc($str){
    if($str=="Reference"){return true;}
    if (preg_match("/d\d+$/i", $str)) {
       return true;
    }else{
       error_log("Invalid accession id");
       return false;
    }
}

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{
        error_log("Invalid sequence name");
       return false;
    }
}

?>
