<?php
/*
* data.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';
require './common.php';
//////////////////////////////////  Connecting with DB
if( !$conn = new mysqli($host, $user, $pswd, $db)) {
    error_log("Connection 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");
///Validating///
$prm_width=(int)$prm_width;
$prm_Pos=(int)$prm_Pos;
$prm_QUAL=(int)$prm_QUAL;
$prm_DP=(int)$prm_DP;
$prm_DP4=(int)$prm_DP4;
$prm_Block=(int)$prm_Block;
$db_range_end=$prm_Pos+$prm_Block;
if(preg_match("/\;|\||\&|\`|\(|\)|\<|\>|\*|\?|\{|\}|\[|\]|\!/",$prm_Zoom)) {
        $prm_Zoom = "1k";
}
list($zoom_v,$zoom_r,$zoom_chr)=_cvtZoom($prm_Zoom, $prm_Chr);
if(!_validate_each_acc($prm_Lists)){exit;}
//$prm_Ref = $conn->real_escape_string($prm_Ref);
if(!_validate_ref_acc($prm_Ref)){exit;}
//$prm_Chr = $conn->real_escape_string($prm_Chr);
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;
}

$stmtDP = $conn->stmt_init();
$stmtRDP = $conn->stmt_init();
if ( $prm_Ref == "Reference" ) {
    $stmtV = $conn->stmt_init();
}else{
    $stmtVr = $conn->stmt_init();
    //get num_row from dx_accession
    if ($result = $conn->query("select pos from ".$prm_Ref."_accession limit 1")) {
        $row_cntRef = $result->num_rows;
        $result->close();
    }
}
$stmtHap = $conn->stmt_init();
$rtnString="";
$minNumReads=1;
$tofix=1;
$normReads=1;
$doCUSTOM=false;
$dostmtRDP=false;
$cstm_tables = _get_condtable($conn);
if($customTrack and $customTrackNormByRead == "enabled"){
    if ($result = $conn->query("SELECT min(num_reads) FROM sys_accession where num_reads > 1")) {
        while ($row = $result->fetch_row()) {
            $minNumReads=$row[0];
        }
        $tofix=$minNumReads;
    }
}
    
for($j=0;$j<count($prm_Lists);$j++){
    $accid=$prm_Lists[$j];
    $row_cntAc=0;
    $row_cntAcCstm = array();
    $numAllReads=1;

    if ($result = $conn->query("select pos from ".$accid."_accession limit 1")) {
        $row_cntAc = $result->num_rows;
        $result->close();
    }
    if($customTrack){
        for($z=0; $z<count($cstm_tables); $z++){
            if ($result = $conn->query("select pos from ".$accid."_accession_cstm".$cstm_tables[$z]." limit 1")) {
                $row_cntAcCstm[] = $result->num_rows;
                $result->close();
            }
        }
        if ($result = $conn->query("SELECT num_reads FROM sys_accession WHERE accession_db='".$accid."'")) {
            while ($row = $result->fetch_row()) {
                $numAllReads=$row[0];
            }
            if ($result = $conn->query("show tables like '".$accid."_accession_cstm'")) {
                $doCUSTOM=true;
            }
        }
        if($customTrackNormByRead == "enabled"){
            if($numAllReads==0){
                $numAllReads=1;
                $tofix=1;
            }
            if((int)$tofix==0){$tofix==1;}
            $normReads=$tofix/$numAllReads;
        }
    }

    //query
    //$accid = $conn->real_escape_string($accid);
    $queryDP = "SELECT sum(".$zoom_chr.")/".$zoom_v." as sum FROM ".$accid."_accession WHERE pos > ? and pos <= ? ";
    //$queryCstm = "SELECT sum(".$zoom_chr.")/".$zoom_v."*".$normReads." as sum FROM ".$accid."_accession_cstm WHERE pos > ? and pos <= ? ";
    $queryVblk = "SELECT variant, count(variant), max(eff_grp) FROM ".$accid."_variant WHERE chr= ? and pos > ? and pos <= ? and QUAL >= ? and DP >= ? and (DP3+DP4) >= ? GROUP BY variant";
    $queryV1b = "SELECT variant, count(variant), max(eff_grp), REF, substring_index(ALT, ',', 1), GT FROM ".$accid."_variant 
                    WHERE chr= ? and pos > ? and pos <= ? and QUAL >= ? and DP >= ? and (DP3+DP4) >= ? GROUP BY variant";
    $queryVref = "SELECT var.variant, count(pos) FROM ".$prm_Ref."_accession 
                    JOIN (SELECT pos, alt, gt, variant FROM 
                        (SELECT pos, alt, gt, variant FROM ".$accid."_variant 
                        WHERE QUAL>= ? and DP >= ? and (DP3+DP4) >= ? and pos > ? and pos <= ? and chr= ? 
                    UNION ALL 
                        SELECT pos, alt, gt, variant from ".$prm_Ref."_variant 
                        WHERE QUAL >= ? and DP >= ? and (DP3+DP4) >= ? and pos > ? and pos <= ? and chr= ? ) AS a 
                        group by pos, alt, gt having count(*)<2) AS var	using (pos) where ".$paramVref1." >= 1 group by (variant)";
    $queryVrefNull = "SELECT var.variant, count(pos) FROM ".$prm_Ref."_accession 
                    RIGHT JOIN (SELECT pos, alt, gt, variant FROM 
                        (SELECT pos, alt, gt, variant FROM ".$accid."_variant 
                        WHERE QUAL>= ? and DP >= ? and (DP3+DP4) >= ? and pos > ? and pos <= ? and chr= ? 
                    UNION ALL 
                        SELECT pos, alt, gt, variant from ".$prm_Ref."_variant 
                        WHERE QUAL >= ? and DP >= ? and (DP3+DP4) >= ? and pos > ? and pos <= ? and chr= ? ) AS a 
                        group by pos, alt, gt having count(*)<2) AS var	using (pos) group by (variant)";
    $queryV="";
    $queryhap="SELECT round(((pos-?)/?)*?), substring_index(alt,',',1), GT, EFF_GRP, REF, variant FROM ".$accid."_variant WHERE chr= ? and pos >= ? and pos <= ? and QUAL >= ? and DP >= ? and (DP3+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){
    if($zoom_v*$zoom_r <100001){
        $stmtHap->prepare($queryhap);
        $stmtHap->bind_param("iiisiiiii", $start, $p1, $p2, $prm_Chr, $start, $end, $prm_QUAL, $prm_DP, $prm_DP4); 
        $stmtHap->execute();
        $stmtHap->store_result();
        $stmtHap->bind_result($row0,$row1,$row2,$row3,$row4,$row5);
        $blknum=0;
        $nextflag=0;

        while ($stmtHap->fetch()) {
            $blknum=(int)($row0/$prm_width);
            if($row0 % $prm_width ==0){$blknum--;}
            if(($row0-$blknum*$prm_width)==$prm_width){$blknum++;}
            if($row5==="s"){
                if(array_key_exists($blknum,$array)){//inner block
                    if(!array_key_exists($row0,$gtarray)){
                        $array[$blknum]=$array[$blknum]."%".($row0-$blknum*$prm_width)."-".$row1."-".$row2."-".$row3;
                    }else{
                        if((int)substr($array[$blknum],-1) < $row3){
                            $array[$blknum]=substr($array[$blknum],0,-1).$row3;
                        }
                    }
                    $gtarray[$row0]=$row3;
                }else{
                    $array[$blknum]=($row0-$blknum*$prm_width)."-".$row1."-".$row2."-".$row3;
                    $gtarray[$row0]=$row3;
                }
            }else if($row5==="i"){
                $reflen = strlen($row4);
                $altlen = strlen($row1);
                $len_i = $altlen - $reflen;
                if(array_key_exists($blknum,$iarray)){//inner block
                    if(!array_key_exists($row0,$igtarray)){
                        $iarray[$blknum]=$iarray[$blknum]."%".($row0-$blknum*$prm_width)."-".substr($row1, 1, $len_i)."-".$row2."-".$row3;
                    }else{
                        if((int)substr($iarray[$blknum],-1) < $row3){
                            $iarray[$blknum]=substr($iarray[$blknum],0,-1).$row3;
                        }
                    }
                    $igtarray[$row0]=$row3;
                }else{
                    $iarray[$blknum]=($row0-$blknum*$prm_width)."-".substr($row1, 1, $len_i)."-".$row2."-".$row3;
                    $igtarray[$row0]=$row3;
                }
            }else if ($row5==="d"){
                $reflen = strlen($row4);
                $altlen = strlen($row1);
                $len_d = $reflen - $altlen;
                if(array_key_exists($blknum,$darray)){//inner block
                    if(!array_key_exists($row0,$igtarray)){
                        $darray[$blknum]=$darray[$blknum]."%".($row0-$blknum*$prm_width)."-".substr($row4, 1, $len_d)."-".$row2."-".$row3;
                    }else{
                        if((int)substr($darray[$blknum],-1) < $row3){
                            $darray[$blknum]=substr($darray[$blknum],0,-1).$row3;
                        }
                    }
                    $igtarray[$row0]=$row3;
                }else{
                    $darray[$blknum]=($row0-$blknum*$prm_width)."-".substr($row4, 1, $len_d)."-".$row2."-".$row3;
                    $igtarray[$row0]=$row3;
                }
            }
        }
        $stmtHap->free_result();
    }

    if($row_cntAc!=0){
        $stmtDP->prepare($queryDP);
    }
    /*if($row_cntAcCstm!=0){
        $stmtRDP->prepare($queryCstm);
    }*/
    // Over 1b
    if($zoom_r*$zoom_v > 1){
        $queryV=$queryVblk;
     // 1b mode: + ALT, REF
    } else {
        $queryV=$queryV1b;
    }
    if ( $prm_Ref == "Reference" ) {
        $stmtV->prepare($queryV);
    }else{
        if($row_cntRef==0){
            $stmtVr->prepare($queryVrefNull);
        }else{
            $stmtVr->prepare($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){
            $stmtDP->bind_param("ii", $s, $e);  
            $stmtDP->execute();
            $stmtDP->store_result();
            $stmtDP->bind_result($row0);
            while ($stmtDP->fetch()) {
                if ( $row0 != "" ) {
                    $rtnString=$rtnString."\"dp\":\"".(int)$row0."\"";
                } else {
                    $rtnString=$rtnString."\"dp\":\"\"";
                }
            }
            $stmtDP->free_result();
        }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 > ? and pos <= ? ";
                    $stmtRDP->prepare($queryCstm);
                    $stmtRDP->bind_param("ii", $s, $e);  
                    $stmtRDP->execute();
                    $stmtRDP->store_result();
                    $stmtRDP->bind_result($row0);
                    while ($stmtRDP->fetch()) {
                        $isEmptyTable = 0;
                        if ( $row0 != "" ) {
                            $string_buffer .= (int)$row0."%";
                        } else {
                            $string_buffer .= "%";
                        }
                    }
                    if($isEmptyTable != 0){ $string_buffer .= "%";}
                    $stmtRDP->free_result();
                }
            }
            $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" ) {
            $stmtV->bind_param("siiiii", $prm_Chr, $s, $e, $prm_QUAL, $prm_DP, $prm_DP4); 
            $stmtV->execute();
            $stmtV->store_result();
            if($zoom_r*$zoom_v > 1){
                $stmtV->bind_result($row0,$row1,$row2);
            }else{
                $stmtV->bind_result($row0,$row1,$row2,$row3,$row4,$row5);
            }
            while ($stmtV->fetch()) {
                if($zoom_r*$zoom_v > 1){
                    $rtnString=$rtnString.",\"".$row0."\":\"".$row1."-".$row2."\"";
                } else {
                    $rtnString=$rtnString.",\"".$row0."\":\"".$row1."-".$row2."-".$row3."-".$row4."-".$row5."\"";
                }
            }
            $stmtV->free_result();
        } else {
            $stmtVr->bind_param("iiiiisiiiiis",$prm_QUAL, $prm_DP, $prm_DP4, $s, $e, $prm_Chr, $prm_QUAL, $prm_DP, $prm_DP4, $s, $e, $prm_Chr);
            $stmtVr->execute();
            $stmtVr->store_result();
            $stmtVr->bind_result($row0,$row1);
            while ($stmtVr->fetch()) {
                if($zoom_r*$zoom_v > 1){
                    $rtnString=$rtnString.",\"".$row0."\":\"".$row1."-\"";
                } else {
                    $rtnString=$rtnString.",\"".$row0."\":\"".$row1."---\"";
                }
            }
            $stmtVr->free_result();
        }
        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){
    $stmtDP->close();
}
if($dostmtRDP == true and $customTrack){
    $stmtRDP->close();
}
if ( $prm_Ref != "Reference" ) {
    $stmtVr->close();
} else{
    $stmtV->close();
}
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[] = "";
    $q = "select table_id from sys_condition order by table_id asc";
    $stmt = $conn->stmt_init();
    $stmt->prepare($q);
    $stmt->execute();
    $stmt->store_result();
    $stmt->bind_result($row0);
    while ($stmt->fetch()) {
        $res[] = "_".$row0;
    }
    $stmt->free_result();
    $stmt->close();
    return $res;
}

function _validate_chrs($conn, $prm_Chr){
    $flag = false;
    $query = "select seq from sys_chromosome where id=? limit 1";
    $stmt = $conn->stmt_init();
    $stmt->prepare($query);
    $stmt->bind_param("s", $prm_Chr); 
    $stmt->execute();
    $stmt->store_result();
    $stmt->bind_result($row0);
    while ($stmt->fetch()) {
        $flag = true;
    }
    $stmt->free_result();
    $stmt->close();
    if($flag){
       return true;
    }else{
       return false;
    }
}

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_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;
}

?>
