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

require '../conf/config.php';
require './common.php';
require './commonDB.php';

////////////////////////////////// Connecting with DB
if( !$conn = new mysqli($host, $user, $pswd, $db)) {
    error_log("Connection failed.\n");
    exit;
}

////////////////////////////////// Parameters
(isset($_POST['prm_csv_chr']) && !empty($_POST['prm_csv_chr'])) ? $prm_chr=$_POST['prm_csv_chr']:returnError("Error1");
(isset($_POST['prm_csv_qual']) && !empty($_POST['prm_csv_qual'])) ? $prm_qual=$_POST['prm_csv_qual']:returnError("Error5");
(isset($_POST['prm_csv_dp']) && !empty($_POST['prm_csv_dp'])) ? $prm_dp=$_POST['prm_csv_dp']:returnError("Error6");
(isset($_POST['prm_csv_list']) && !empty($_POST['prm_csv_list'])) ? $prm_list=$_POST['prm_csv_list'] : returnError("Error7");
(isset($_POST['choose_flag']) && !empty($_POST['choose_flag'])) ? $choose_flag=$_POST['choose_flag'] : returnError("Error8");
if( $choose_flag == "1" ){ //Position
        (isset($_POST['prm_csv_s']) && !empty($_POST['prm_csv_s'])) ? $prm_s=$_POST['prm_csv_s']:returnError("Error3");
        (isset($_POST['prm_csv_e']) && !empty($_POST['prm_csv_e'])) ? $prm_e=$_POST['prm_csv_e']:returnError("Error4");
} else if ($choose_flag == "2") { //ID
    (isset($_POST['prm_csv_keyword']) && !empty($_POST['prm_csv_keyword'])) ? $prm_csv_keyword=$_POST['prm_csv_keyword']:returnError("Error9");
   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;
    }
}
/*
$enabledEffImpacts = array("0" => True, "1" => True, "2" => True, "3" => True, "-1" => True);
if(isset($_POST['prm_csv_effgrps']) && !empty($_POST['prm_csv_effgrps'])) {
	try {
		$impacts = json_decode($_POST['prm_csv_effgrps']);
		$filterEffImpact = _setSnpEffImpactFilterMap($enabledEffImpacts, $impacts);	// Do not use return value(boolean)
	} catch ( Exception $ex ) {}
}
*/

///varidating prm///
_pregMatch($prm_chr);
_pregMatch($prm_csv_list);
 _pregMatch($prm_csv_keyword);
if (!(is_numeric($prm_qual)) || !(is_numeric($prm_dp)) || !(is_numeric($choose_flag))) {
                 $errmsg = "<font color=\"#FF0000\">Error: Input Parameter Invalid!</font>";
                  echo $errmsg;
                  exit;
}
if (!(is_numeric($prm_s)) || !(is_numeric($prm_e))) {
                 $errmsg = "<font color=\"#FF0000\">Error: Input Parameter Invalid!</font>";
                  echo $errmsg;
                  exit;
}

////////////////////////////////// Getting pos filtered on qual, dp
$list=explode(",",$prm_list);
$fltrPOS=_filterdLIST($list, $prm_qual, $prm_dp, $prm_chr, $prm_s, $prm_e, $conn);
//$fltrPOS=_filterdLIST($list, $prm_qual, $prm_dp, $prm_chr, $prm_s, $prm_e, $enabledEffImpacts, $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);

$fastaTable = getSerialTableByColumn($conn, $db, "sys_fasta", $prm_chr."_f");
$dn2table = array();	// Global variable

$uniqPOS_cnt = count($uniqPOS);
for ( $i=0; $i<$uniqPOS_cnt; $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;
    $colEff=array();

    $list_cnt = count($list);
    $effStr = "";
    for ( $j=0; $j<$list_cnt; $j++ ) {
        $chkDPStr=_CHKDP($list[$j], $prm_chr, $uniqPOS[$i], $conn);
        if ( empty($chkDPStr) ) {
            // DEPTH != NA
            list($strVar,$strRef,$strAlt,$strGt,$strEff) = _SELECT_VAR($list[$j],$prm_chr,$uniqPOS[$i],$conn);

            if( !empty($strEff) ) $colEff[] = $strEff;
            if ( empty($strVar) ) {
                $colS[$j] = strtoupper($REF)."/".strtoupper($REF);
                if(!isset($colI[$j])) $colI[$j] = "0";
                if(!isset($colD[$j])) $colD[$j] = "0";
            } else {
                if ( $strVar === "s" ) {
                    list($colS[$j],$snpATGC) = _WRKY($strRef, $strAlt, $strGt, $snpATGC);
                    if(!isset($colI[$j])) $colI[$j] = "0";
                    if(!isset($colD[$j])) $colD[$j] = "0";
                    $flagS=true;
                } else if ( $strVar === "i" ) {
                    list($insATGC,$colI[$j]) = _INS($insATGC, $strRef, $strAlt);
                    if(!isset($colS[$j])) $colS[$j] = strtoupper($REF);
                    if(!isset($colD[$j])) $colD[$j] = "0";
                    $flagI=true;
                } else if ( $strVar === "d" ) {
                    list($delATGC,$colD[$j]) = _DEL($delATGC, $strRef, $strAlt);
                    if(!isset($colS[$j])) $colS[$j] = strtoupper($REF);
                    if(!isset($colI[$j])) $colI[$j] = "0";
                    $flagD=true;
                }
          }
        } else {
          // DEPTH = NA
          $colS[$j]="-";
          $colI[$j]="-";
          $colD[$j]="-";
        }
    }

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

function _SELECT_VAR($a,$b,$c,$conn){
    $query = "SELECT variant,ref,alt,gt,eff FROM ".$a."_variant WHERE chr= ? and pos= ? ";
//    $query = "SELECT variant,ref,substring_index(alt, ',', 1),gt,eff FROM ".$a."_variant WHERE chr= ? and pos= ? ";
    $stmt = $conn->stmt_init();
    $stat = $stmt->prepare($query);
    if(!$stat) { return array(NULL, NULL, NULL, NULL, NULL); }

    $stmt->bind_param("si", $b, $c); 
    $stmt->execute();
    $stmt->store_result();
    $stmt->bind_result($row0,$row1,$row2,$row3,$row4);
    $stmt->fetch();
    $rtn_array = array($row0,$row1,$row2,$row3,$row4);
//    $rtn_array = array($row0,strtoupper($row1),strtoupper($row2),$row3,$row4);
    $stmt->free_result();
    $stmt->close();
    return $rtn_array;
}

function _GETPOSBYIDS($a,$conn){
    $query = "SELECT pos, end FROM sys_track WHERE id like ? and parent=1 order by id LIMIT 1";
    $param = "%".$a."%";
    $stmt = $conn->stmt_init();
    $stmt->prepare($query);
    $stmt->bind_param("s", $param); 
    $stmt->execute();
    $stmt->store_result();
    $stmt->bind_result($row0,$row1);
    $stmt->fetch();
    $rtn_array = array((int)$row0+100,(int)$row1+100);
    $stmt->free_result();
    $stmt->close();
    return $rtn_array;
}

function _GETREF($a,$b,$conn){
    global $fastaTable;
    $query = "SELECT `".$a."_f` FROM ". $fastaTable. " WHERE pos= ?";
//    $query = "SELECT `".$a."_f` FROM sys_fasta WHERE pos= ?";
    $stmt = $conn->stmt_init();
    $stmt->prepare($query);
    $stmt->bind_param("i", $b); 
    $stmt->execute();
    $stmt->store_result();
    $stmt->bind_result($row0);
    $stmt->fetch();
    $rtn = $row0;
    $stmt->free_result();
    $stmt->close();
    return $rtn;
}

function _HEADER($a,$b,$c,$conn){
    //$str=$b."~".$c."\n";
    $str="#Chr\tpos\ttype\tref\talt\tFiltered qual or dp\tGene\tAnnotation";
    $query="SELECT accession_id, sub_variety FROM sys_accession WHERE accession_db= ? ";
    $stmt = $conn->stmt_init();
    $stmt->prepare($query);
    for($i=0;$i<count($a);$i++){
        $stmt->bind_param("s", $a[$i]); 
        $stmt->execute();
        $stmt->store_result();
        $stmt->bind_result($row0,$row1);
        while ($stmt->fetch()) {
            //$str=$str."#".($i+1)." ".$row0." ".$row1."\n";
            $str=$str."\t".$row0." ".$row1;
        }
        $stmt->free_result();
    }
    //$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";
        }
    }
*/
    $stmt->close();
    $str=$str."\n";
    return $str;
}

function _allLIST($a, $b, $c, $d,$conn){
    $all=array();
    $stmt = $conn->stmt_init();
    for($i=0;$i<count($a);$i++){
        $query="SELECT pos FROM ".$a[$i]."_variant WHERE chr= ? and pos>= ? and pos<= ?";
        $stmt->prepare($query);
        $stmt->bind_param("sii", $b, $c, $d); 
        $stmt->execute();
        $stmt->store_result();
        $stmt->bind_result($row0);
        while ($stmt->fetch()) {
            $all[]=$row0;
        }
        $stmt->free_result();
    }
    $stmt->close();
    $returnArr = array_values(array_unique($all));
    sort($returnArr);
    return $returnArr;
}
function _filterdLIST($a, $b, $c, $d, $e, $f,$conn) {
    $filtered=array();
    $stmt = $conn->stmt_init();
    for($i=0;$i<count($a);$i++){
        $query="SELECT pos FROM ".$a[$i]."_variant WHERE (qual< ? or dp< ?) and chr= ? and pos>= ? and pos<= ?";
        $stmt->prepare($query);
        $stmt->bind_param("iisii", $b, $c, $d, $e, $f);
        $stmt->execute();
        $stmt->store_result();
        $stmt->bind_result($row0);
        while ($stmt->fetch()) {
            $filtered[]=$row0;
        }
        $stmt->free_result();
    }
    $stmt->close();
    return array_values(array_unique($filtered));
}
/*
function _filterdLIST($a, $b, $c, $d, $e, $f,$enabledEffImpacts, $conn) {
    $filtered=array();
    $stmt = $conn->stmt_init();
    for($i=0;$i<count($a);$i++){
        $query="SELECT pos,EFF,QUAL,DP FROM ".$a[$i]."_variant WHERE chr= ? and pos>= ? and pos<= ?";
        $stmt->prepare($query);
	$stmt->bind_param("sii", $d, $e, $f);
        $stmt->execute();
        $stmt->store_result();
        $stmt->bind_result($row0, $rowEff, $rowQual, $rowDp);
        while ($stmt->fetch()) {
            $doFilter = False;

            // SnpEff Filter
            if(empty($rowEff)) {
                if(!$enabledEffImpacts{"-1"}) $doFilter = True;
            } else { 
                $eff_list=explode(",", $rowEff);
                $t=count($eff_list);
                $hitImpact = False;
                for($k=0; $k<$t; $k++){
                    $effInfo = _parseSnpEff($eff_list[$k]);
                    $impact = $effInfo["impactc"];
                    if(!empty($impact) && $enabledEffImpacts{(string)$impact}) $hitImpact = True;
                }
                if(!$hitImpact) $doFilter = True;
            }

            // QUAL
            if(!$doFilter && $rowQual < $b) $doFilter = True;

            // DP
            if(!$doFilter && $rowDp < $c) $doFilter = True; 

            if(!$doFilter) continue; 

            $filtered[]=$row0;
        }
        $stmt->free_result();
    }
    $stmt->close();
    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;
    }
*/
    if ( $c === "1/1" || $c === "1|1") {
	$rStr = $b."/".$b;
    } else if ( $c === "0/0" || $c === "0|0") {
	$rStr = $a."/".$a;
    } else if ($c === "0/1" || $c === "0|1") {
	$rStr = $a."/".$b;
    } else if ($c === "1/0" || $c === "1|0") {
	$rStr = $b."/".$a;
    } else {
	// $c === "1/2"
	$rStr = str_replace(",", "/", $b);
    }
    $rStr = strtoupper($rStr);

    $alleles = explode(",", $b);
    $allelesLen = count($alleles);
    for($i=0; $i<$allelesLen; $i++) {
        $allele = $alleles[$i];
        $k = array_search($allele, $d);
        if ( $k !== false ) {
            //deleted?
        } else {
          $d[] = $allele;
        }
    }
/*
    $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 "yes"; //filtered
    } else {
        return "no"; //not filtered
    }
}

function _INS($a, $b, $c) {
    $c = _getFirstAllele($c);
    if(_isTransposon($c)) {
        $s = $c;
    } else {
//        $s = strtoupper( substr($c,strlen($b)-strlen($c)) );
        $s = strtoupper( substr($c, 1, strlen($c)-strlen($b)) );
    }
    $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) {
    $c = _getFirstAllele($c);
    $s = strtoupper( 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 _getFirstAllele($str) {
	$alleles = explode(",", $str);
	return $alleles[0];
}

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

function _GENE($a, $b, $conn) {
    // Retrieving tracks
    $cnt=0;
    $query="SELECT * FROM (SELECT id,IF(parent=0,type,'') FROM sys_track WHERE chr=? and ?>=pos AND ?<=end ORDER BY type) as a GROUP BY id";
    $stmt = $conn->stmt_init();
    $stmt->prepare($query);
    $stmt->bind_param("sii", $b, $a, $a); 
    $stmt->execute();
    $stmt->store_result();
    $stmt->bind_result($row0,$row1);
    while ($stmt->fetch()) {
        if ( $row0 != "" ) {
            $g[$cnt][0]=$row0;
            $g[$cnt][1]=$row1;
            $cnt++;
        }
    }
    $stmt->free_result();
    $stmt->close();
    $str="";
    for($i=0;$i<$cnt;$i++){
        $str .= $g[$i][0];
        if ( $g[$i][1] != "" ) $str .= ":".$g[$i][1];
        if ( $i < $cnt-1 ) $str .= ",";
/*
        $str=$str.$g[$i][0];
        if ( $g[$i][1] != "" ) $str=$str.":".$g[$i][1];
        if ( $i < $cnt-1 ) $str=$str.",";
*/
    }
    return $str;
}

function _EFF($colEff) {
    $str = "";
    $uniqEffMap = array();
    $uniqEffArray = array();
    $icount = count($colEff);
    for($i=0; $i<$icount; $i++) {
        $effsAcc = preg_split("/\s*,\s*/", $colEff[$i]);
	$jcount = count($effsAcc);
        for($j=0; $j<$jcount; $j++) {
            $eff = $effsAcc[$j];
            $key = preg_replace("/\s/", "", strtolower($eff));
            if(!array_key_exists($key, $uniqEffMap)) {
                $str .= $eff. ",";
            }
            $uniqEffMap[$key] = True;
        }
    }
    $str = preg_replace("/,$/", "", $str);
    return $str;
}

function _CHKDP($a, $b, $c, $conn){
    global $db, $dn2table;

    $table = "";
    if(array_key_exists($a, $dn2table)) {  
        $table = $dn2table[$a];
    } else {
        $table = getSerialTableByColumn($conn, $db, $a."_accession", $b."_b");
        $dn2table[$a] = $table;
    }
    $query="SELECT pos FROM ". $table. " WHERE `".$b."_b` is null and pos= ? ";
//    $query="SELECT pos FROM ".$a."_accession WHERE `".$b."_b` is null and pos= ? ";
    $stmt = $conn->stmt_init();
    $stat = $stmt->prepare($query);
    if(!$stat) { return NULL; }
    $stmt->bind_param("i", $c); 
    $stmt->execute();
    $stmt->store_result();
    $stmt->bind_result($row0);
    $stmt->fetch();
    $rtn = $row0;
    $stmt->free_result();
    $stmt->close();
    return $rtn;
}

function _ARRTOSTR($a,$b){
    $str="";
    $a_cnt = count($a);
    for($i=0;$i<$a_cnt;$i++){
        $str .= $a[$i];
        if($i<$a_cnt-1){ $str .= $b; }
    }
/*
    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;
}

function _stopError($str){
    echo $str;
    exit;
}
?>
