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

//////////////////////////////////  Connecting with DB
require '../../conf/config.php';

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

//////////////////////////////////  Paramenters
(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_Mode']) && !empty($_GET['prm_Mode'])) ? $prm_Mode=$_GET['prm_Mode']:returnError("Error");
(isset($_GET['prm_Pos']) && !empty($_GET['prm_Pos'])) ? $prm_Pos=$_GET['prm_Pos']:returnError("Error");
(isset($_GET['prm_QUAL']) && !empty($_GET['prm_QUAL'])) ? $prm_QUAL=$_GET['prm_QUAL']:returnError("Error");
(isset($_GET['prm_DP']) && !empty($_GET['prm_DP'])) ? $prm_DP=$_GET['prm_DP']:returnError("Error");
(isset($_GET['prm_DP4']) && !empty($_GET['prm_DP4'])) ? $prm_DP4=$_GET['prm_DP4']:returnError("Error");
(isset($_GET['prm_Acc']) && !empty($_GET['prm_Acc'])) ? $prm_Acc=$_GET['prm_Acc'] : returnError("Error");

list($zoom_v,$zoom_r,$zoom_chr)=_cvtZoom($prm_Zoom, $prm_Chr);
if(!_validate_each_acc($prm_Acc)){
    error_log("Invalid string:".$prm_Acc);
    exit;
}
$query="";

if ( $prm_Mode == "dp+s" || $prm_Mode == "s" ) {
    $query = "SELECT pos,variant,REF,ALT,EFF,GT,QUAL FROM ".$prm_Acc."_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_adp and variant='s' ";
} else if ( $prm_Mode == "dp+i" || $prm_Mode == "i" ) {
    $query = "SELECT pos,variant,REF,ALT,EFF,GT,QUAL FROM ".$prm_Acc."_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_adp and variant!='s' ";
} else if ( $prm_Mode == "dp" ) {
    $query = "SELECT pos,variant,REF,ALT,EFF,GT,QUAL FROM ".$prm_Acc."_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_adp and variant='dp' ";
} else{
    $query = "SELECT pos,variant,REF,ALT,EFF,GT,QUAL FROM ".$prm_Acc."_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_adp ";
}
$query_eff="SELECT eff_grp FROM sys_snpEff WHERE eff_name like :bd_effect ";

////////////////////////////////////////////////// Range
$e=$zoom_v*$zoom_r*$prm_Pos;
$s=$e-$zoom_v*$zoom_r;

////////////////////////////////////////////////// SQL
$datas = array();
$stmteff = oci_parse($conn, $query_eff);
$stmt = oci_parse($conn, $query);
oci_bind_by_name($stmt, ':bd_chr', $prm_Chr, -1, SQLT_CHR);
oci_bind_by_name($stmt, ':bd_start', $s, -1, SQLT_INT);
oci_bind_by_name($stmt, ':bd_end', $e, -1, SQLT_INT);
oci_bind_by_name($stmt, ':bd_qual', $prm_QUAL, -1, SQLT_INT);
oci_bind_by_name($stmt, ':bd_dp', $prm_DP, -1, SQLT_INT);
oci_bind_by_name($stmt, ':bd_adp', $prm_DP4, -1, SQLT_INT);
oci_execute($stmt);
while (($row = oci_fetch_array($stmt, OCI_NUM + OCI_RETURN_NULLS))!= false) {
    if ( $row[1] == "s" ) $varType="SNP";
    if ( $row[1] == "i" ) $varType="INS";
    if ( $row[1] == "d" ) $varType="DEL";
    $each_effect=array();
    $eff_list=explode(",", $row[4]);
    $ret_eff=array();
    $t=count($eff_list);
    for($i=0; $i<$t; $i++){
        $effect=explode("(", $eff_list[$i]);
        $effect_splited=explode("+",$effect[0]);
        $effquery=$effect_splited[0];//primary effect
        oci_bind_by_name($stmteff, ':bd_effect', $effquery, -1, SQLT_CHR);
        oci_execute($stmteff);
        while (($row_2 = oci_fetch_array($stmteff, OCI_NUM + OCI_RETURN_NULLS))!= false) {
            $ret_eff[] = $row_2[0]."|".$eff_list[$i];
        }
    }
    $datas[]="{\"pos\":\"".$row[0]."\",\"variant\":\"".$varType."\",\"ref\":\"".$row[2]."\",".
              "\"alt\":\"".$row[3]."\",\"eff\":\"".implode(",", $ret_eff)."\",\"gt\":\"".$row[5]."\",\"qual\":\"".$row[6]."\"}";
}
oci_free_statement($stmteff);
oci_free_statement($stmt);
oci_close($conn);

$rtnString="[";
for ($i=0; $i<count($datas); $i++) {
    $rtnString=$rtnString.$datas[$i];
    if ( $i<count($datas)-1 ) $rtnString=$rtnString.",";
}
$rtnString=$rtnString."]";
echo $rtnString;
exit;


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

function _validate_each_acc($str){
    if (preg_match("/d\d+$/i", $str)) {
       return true;
    }else{
       return false;
    }
}

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);
}
?>
