<?php
/*
* csv_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
(isset($_POST['prm_csv_chr']) && !empty($_POST['prm_csv_chr'])) ? $prm_chr=$_POST['prm_csv_chr']:returnError("Error");
(isset($_POST['prm_pos_id']) && !empty($_POST['prm_pos_id'])) ? $prm_pos_id=$_POST['prm_pos_id']:returnError("Error");
(isset($_POST['prm_csv_s']) && !empty($_POST['prm_csv_s'])) ? $prm_s=$_POST['prm_csv_s']:returnError("Error");
(isset($_POST['prm_csv_e']) && !empty($_POST['prm_csv_e'])) ? $prm_e=$_POST['prm_csv_e']:returnError("Error");
(isset($_POST['prm_csv_qual']) && !empty($_POST['prm_csv_qual'])) ? $prm_qual=$_POST['prm_csv_qual']:returnError("Error");
(isset($_POST['prm_csv_dp']) && !empty($_POST['prm_csv_dp'])) ? $prm_dp=$_POST['prm_csv_dp']:returnError("Error");
(isset($_POST['prm_csv_list']) && !empty($_POST['prm_csv_list'])) ? $prm_list=$_POST['prm_csv_list'] : returnError("Error");

if( $prm_pos_id == "id" ){
    (isset($_POST['prm_csv_keyword']) && !empty($_POST['prm_csv_keyword'])) ? $prm_csv_keyword=$_POST['prm_csv_keyword']:returnError("Error");
}
if ( $prm_pos_id == "id" ) {
    list($prm_s,$prm_e)=_GETPOSBYIDS($prm_csv_keyword,$conn);
} else {
    if($prm_e-$prm_s>199999){  // Maximum size is 200K
        $prm_e=$prm_s+199999;
    }
}

////////////////////////////////// Getting pos filtered on qual, dp
$list=explode(",",$prm_list);
$fltrPOS=_filterdLIST($list, $prm_qual, $prm_dp, $prm_chr, $prm_s, $prm_e, $conn);

////////////////////////////////// Generation accession lists
$uniqPOS=_allLIST($list,$prm_chr,$prm_s,$prm_e,$conn);

//////////////////////////////////////////////////////////////////////
// Writing csv file
//////////////////////////////////////////////////////////////////////
echo _HEADER($list,$prm_s,$prm_e,$conn);

for ( $i=0; $i<count($uniqPOS); $i++ ) {
    $REF=_GETREF($prm_chr,$uniqPOS[$i],$conn);
    $colS=array();
    $colI=array();
    $colD=array();
    $snpATGC=array();
    $insATGC=array();
    $delATGC=array();
    $flagS=false;
    $flagI=false;
    $flagD=false;

    for ( $j=0; $j<count($list); $j++ ) {
        $chkDPStr=_CHKDP($list[$j],$prm_chr,$uniqPOS[$i],$conn);
        if ( empty($chkDPStr) ) {
            list($strVar,$strRef,$strAlt,$strGt)=_SELECT_VAR($list[$j],$prm_chr,$uniqPOS[$i],$conn);
            if ( empty($strVar) ) {
                $colS[$j]=strtoupper($REF);
                $colI[$j]="0";
                $colD[$j]="0";
            } else {
                if ( $strVar == "s" ) {
                    list($colS[$j],$snpATGC)=_WRKY($strRef,$strAlt, $strGt,$snpATGC);
                    $colI[$j]="0";
                    $colD[$j]="0";
                    $flagS=true;
                }
                if ( $strVar == "i" ) {
                    list($insATGC,$colI[$j])=_INS($insATGC,$strRef,$strAlt);
                    $colS[$j]=strtoupper($REF);
                    $colD[$j]="0";
                    $flagI=true;
                }
                if ( $strVar == "d" ) {
                    list($delATGC,$colD[$j])=_DEL($delATGC,$strRef,$strAlt);
                    $colS[$j]=strtoupper($REF);
                    $colI[$j]="0";
                    $flagD=true;
                }
          }
        } else {
          $colS[$j]="-";
          $colI[$j]="-";
          $colD[$j]="-";
        }
    }

    // Track, FILTER
    $genesStr=_GENE($uniqPOS[$i],$prm_chr,$conn);
    $fltrStr=_FILTER($uniqPOS[$i],$fltrPOS);
    if ( $flagS ) {
        $snpStr=_ARRTOSTR($snpATGC,",");
        $colStr=_COL($colS);
        echo $prm_chr."\t".$uniqPOS[$i]."\t"."SNP"."\t".$REF."\t".$snpStr."\t".$fltrStr."\t".$genesStr."\t".$colStr."\n";
    }
    if ( $flagI ) {
        $insStr=_ARRTOSTR($insATGC,",");
        $colStr=_COL($colI);
        echo $prm_chr."\t".$uniqPOS[$i]."\t"."INS"."\t".$REF."\t".$insStr."\t".$fltrStr."\t".$genesStr."\t".$colStr."\n";
    }
    if ( $flagD ) {
        $delStr=_ARRTOSTR($delATGC,",");
        $colStr=_COL($colD);
        echo $prm_chr."\t".$uniqPOS[$i]."\t"."DEL"."\t".$REF."\t".$delStr."\t".$fltrStr."\t".$genesStr."\t".$colStr."\n";
    }
}

function _SELECT_VAR($a,$b,$c,$conn){
    $query = "SELECT variant,ref,alt,gt FROM ".$a."_variant WHERE chr= :bd_chr and pos= :bd_pos ";
    $stmt = oci_parse($conn, $query);
    oci_bind_by_name($stmt, ':bd_chr', $b, -1, SQLT_CHR);
    oci_bind_by_name($stmt, ':bd_pos', $c, -1, SQLT_INT);
    oci_execute($stmt);
    while (($row = oci_fetch_array($stmt, OCI_NUM + OCI_RETURN_NULLS))!= false) {
        $alts = explode(",", $row[2]);
        $rtn_array = array($row[0],strtoupper($row[1]),strtoupper($alts[0]),$row[3]);
        oci_free_statement($stmt);
        return $rtn_array;
    }
}

function _GETPOSBYIDS($a,$conn){
    $start = 0;
    $end = 0;
    $query = "SELECT pos, end FROM sys_track WHERE id like :bd_text and parent=1 and ROWNUM < 2 order by id";
    $stmt = oci_parse($conn, $query);
    $param = "%".$a."%";
    oci_bind_by_name($stmt, ':bd_text', $param, -1, SQLT_CHR);
    oci_execute($stmt);
    while (($row = oci_fetch_array($stmt, OCI_NUM + OCI_RETURN_NULLS))!= false) {
        $start = $row[0];
        $end = $row[1];
    }
    $rtn_array = array((int)$start+100,(int)$end+100);
    oci_free_statement($stmt);
    return $rtn_array;
}

function _GETREF($a,$b,$conn){
    $rtn = "";
    $query = "SELECT ".$a."_f FROM sys_fasta WHERE pos=:bd_pos";
    $stmt = oci_parse($conn, $query);
    oci_bind_by_name($stmt, ':bd_pos', $b, -1, SQLT_INT);
    oci_execute($stmt);
    while (($row = oci_fetch_array($stmt, OCI_NUM + OCI_RETURN_NULLS))!= false) {
        $rtn = $row[0];
    }
    oci_free_statement($stmt);
    return $rtn;
}

function _HEADER($a,$b,$c,$conn){
    $str = $b."~".$c."\n";
    $query="SELECT accession_id, sub_variety FROM sys_accession WHERE accession_db= :bd_acc ";
    $stmt = oci_parse($conn, $query);
    for($i=0;$i<count($a);$i++){
        oci_bind_by_name($stmt, ':bd_acc', $a[$i], -1, SQLT_CHR);
        oci_execute($stmt);
        while (($row = oci_fetch_array($stmt, OCI_NUM + OCI_RETURN_NULLS))!= false) {
            $str=$str."#".($i+1)." ".$row[0]." ".$row[1]."\n";
        }
    }
    $str=$str."\n";
    $str=$str."#Chr\tpos\ttype\tref\talt\tqual&dp\tAnnotation\t";
    for($i=0;$i<count($a);$i++){
        $str=$str.($i+1);
        if($i<count($a)-1){
            $str=$str."\t";
        }
    }
    oci_free_statement($stmt);
    $str=$str."\n";
    return $str;
}

function _allLIST($a, $b, $c, $d,$conn){
    $all=array();
    for($i=0;$i<count($a);$i++){
        $query="SELECT pos FROM ".$a[$i]."_variant WHERE chr= :bd_chr and pos>= :bd_start and pos<= :bd_end";
        $stmt = oci_parse($conn, $query);
        oci_bind_by_name($stmt, ':bd_chr', $b, -1, SQLT_CHR);
        oci_bind_by_name($stmt, ':bd_start', $c, -1, SQLT_INT);
        oci_bind_by_name($stmt, ':bd_end', $d, -1, SQLT_INT);
        oci_execute($stmt);
        while (($row = oci_fetch_array($stmt, OCI_NUM + OCI_RETURN_NULLS))!= false) {
            $all[]=$row[0];
        }
    }
    oci_free_statement($stmt);
    $returnArr = array_values(array_unique($all));
    sort($returnArr);
    return $returnArr;
}

function _filterdLIST($a, $b, $c, $d, $e, $f,$conn) {
    $filtered=array();
    for($i=0;$i<count($a);$i++){
        $query="SELECT pos FROM ".$a[$i]."_variant WHERE (qual< :bd_qual or dp< :bd_dp) and chr= :bd_chr and pos>= :bd_start and pos<= :bd_end";
        $stmt = oci_parse($conn, $query);
        oci_bind_by_name($stmt, ':bd_qual', $b, -1, SQLT_INT);
        oci_bind_by_name($stmt, ':bd_dp', $c, -1, SQLT_INT);
        oci_bind_by_name($stmt, ':bd_chr', $d, -1, SQLT_CHR);
        oci_bind_by_name($stmt, ':bd_start', $e, -1, SQLT_INT);
        oci_bind_by_name($stmt, ':bd_end', $f, -1, SQLT_INT);
        oci_execute($stmt);
        while (($row = oci_fetch_array($stmt, OCI_NUM + OCI_RETURN_NULLS))!= false) {
            $filtered[]=$row[0];
        }
    }
    oci_free_statement($stmt);
    return array_values(array_unique($filtered));
}

function _WRKY($a, $b, $c, $d) {
    if ( $c != "1/1" ) {
        if ( ($a == "A" and $b == "T") or ($a == "T" and $b == "A") ) {
        $rStr="W";
        } else if ( ($a == "A" and $b == "G") or ($a == "G" and $b == "A") ) {
        $rStr="R";
        } else if ( ($a == "A" and $b == "C") or ($a == "C" and $b == "A") ) {
        $rStr="M";
        } else if ( ($a == "T" and $b == "G") or ($a == "G" and $b == "T") ) {
        $rStr="K";
        } else if ( ($a == "T" and $b == "C") or ($a == "C" and $b == "T") ) {
        $rStr="Y";
        } else if ( ($a == "G" and $b == "C") or ($a == "C" and $b == "G") ) {
        $rStr="S";
        }
    } else {
        $rStr=$b;
    }
    $k = array_search($b, $d);
    if ( $k !== false ) {
        //deleted?
    } else {
      $d[]=$b;
    }
    return array($rStr,$d);
}

function _FILTER($a, $b){
    $k = array_search($a, $b);
    if ( $k !== false ) {
        return "0"; //filtered
    } else {
        return "1"; //not filtered
    }
}

function _INS($a, $b, $c) {
    $s=substr($c,strlen($b)-strlen($c));
    $k=array_search($s, $a);
    if ( $k !== false ) {
        $r=$k+1;
    } else {
        $a[]=$s;
        $r=count($a);
    }
    return array($a, $r);
}

function _DEL($a, $b, $c) {
    $s=substr($b,1,strlen($b)-strlen($c));
    $k=array_search($s, $a);
    if ( $k !== false ) {
        $r=$k+1;
    } else {
        $a[]=$s;
        $r=count($a);
    }
    return array($a, $r);
}

function _COL($a) {
    $str="";
    for($i=0;$i<count($a);$i++){
        $str=$str.$a[$i];
        if($i<count($a)-1){
            $str=$str."\t";
        }
    }
    return $str;
}

function _GENE($a, $b, $conn) {
    // Retrieving tracks
    $cnt=0;
    $query="SELECT tmp.id, tmp.type FROM (SELECT id,CASE parent WHEN 0 THEN type ELSE '' END type FROM sys_track WHERE chr=:bd_chr and :bd_start>=pos AND :bd_end<=end ORDER BY type) tmp where ROWNUM<2 GROUP BY id,type";
    $stmt = oci_parse($conn, $query);
    oci_bind_by_name($stmt, ':bd_chr', $b, -1, SQLT_CHR);
    oci_bind_by_name($stmt, ':bd_start', $a, -1, SQLT_INT);
    oci_bind_by_name($stmt, ':bd_end', $a, -1, SQLT_INT);
    oci_execute($stmt);
    while (($row = oci_fetch_array($stmt, OCI_NUM + OCI_RETURN_NULLS))!= false) {
        if ( $row[0] != "" ) {
            $g[$cnt][0]=$row[0];
            $g[$cnt][1]=$row[1];
            $cnt++;
        }
    }
    oci_free_statement($stmt);
    $str="";
    for($i=0;$i<$cnt;$i++){
        $str=$str.$g[$i][0];
        if ( $g[$i][1] != "" ) $str=$str.":".$g[$i][1];
        if ( $i < $cnt-1 ) $str=$str.",";
    }
    return $str;
}

function _CHKDP($a, $b, $c, $conn){
    $rtn = "";
    $query = "SELECT pos FROM ".$a."_accession WHERE ".$b."_b is null and pos= :bd_pos";
    $stmt = oci_parse($conn, $query);
    oci_bind_by_name($stmt, ':bd_pos', $c, -1, SQLT_INT);
    oci_execute($stmt);
    while (($row = oci_fetch_array($stmt, OCI_NUM + OCI_RETURN_NULLS))!= false) {
        $rtn = $row[0];
    }
    oci_free_statement($stmt);
    return $rtn;
}

function _ARRTOSTR($a,$b){
    $str="";
    for($i=0;$i<count($a);$i++){
        $str=$str.$a[$i];
        if($i<count($a)-1){
            $str=$str.$b;
        }
    }
    return $str;
}

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

?>
