<?php

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

  ////////////////////////////////// Connecting with DB
  $conn = mysql_connect($host, $user, $pswd);
  $db = mysql_select_db($db);

  ////////////////////////////////// Parameters
  (isset($_GET['prm_csv_chr']) && !empty($_GET['prm_csv_chr'])) ? $prm_chr=$_GET['prm_csv_chr']:returnError("Error");
  (isset($_GET['prm_pos_id']) && !empty($_GET['prm_pos_id'])) ? $prm_pos_id=$_GET['prm_pos_id']:returnError("Error");
  (isset($_GET['prm_csv_s']) && !empty($_GET['prm_csv_s'])) ? $prm_s=$_GET['prm_csv_s']:returnError("Error");
  (isset($_GET['prm_csv_e']) && !empty($_GET['prm_csv_e'])) ? $prm_e=$_GET['prm_csv_e']:returnError("Error");
  if ( $prm_pos_id == "id" ) {
  (isset($_GET['prm_csv_keyword']) && !empty($_GET['prm_csv_keyword'])) ? $prm_csv_keyword=$_GET['prm_csv_keyword']:returnError("Error");
  }
  (isset($_GET['prm_csv_qual']) && !empty($_GET['prm_csv_qual'])) ? $prm_qual=$_GET['prm_csv_qual']:returnError("Error");
  (isset($_GET['prm_csv_dp']) && !empty($_GET['prm_csv_dp'])) ? $prm_dp=$_GET['prm_csv_dp']:returnError("Error");
  (isset($_GET['prm_csv_list']) && !empty($_GET['prm_csv_list'])) ? $prm_list=$_GET['prm_csv_list'] : returnError("Error");
  
	if (!is_numeric($prm_s)) {
		$prm_s=mysql_real_escape_string($prm_s);
	}
	if (!is_numeric($prm_e)) {
		$prm_e=mysql_real_escape_string($prm_e);
	}
	$prm_csv_keyword=mysql_real_escape_string($prm_csv_keyword);

  if ( $prm_pos_id == "id" ) {
    list($prm_s,$prm_e)=_GETPOSBYIDS($prm_csv_keyword);
  } 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);

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

  //////////////////////////////////////////////////////////////////////
  // Writing csv file
  //////////////////////////////////////////////////////////////////////
  $file="CSV_".date("Y-m-d").".csv";
  header("Content-Disposition: attachment; filename=\"".$file."\"");
  header("Content-type: application/octet-stream");
  echo _HEADER($list,$prm_s,$prm_e);

  for ( $i=0; $i<count($uniqPOS); $i++ ) {

      $REF=_GETREF($prm_chr,$uniqPOS[$i]);
      $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]);
        if ( empty($chkDPStr) ) {
          list($strVar,$strRef,$strAlt,$strGt)=_SELECT_VAR($list[$j],$prm_chr,$uniqPOS[$i]);
          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);
      $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 returnError($str){
    echo $str;
  }

  function _SELECT_VAR($a,$b,$c){
    $sql = "SELECT variant,ref,alt,gt FROM ".$a."_variant WHERE chr='".$b."' and pos=".$c;
    $result = mysql_query ( $sql ) or ( "Error " . mysql_error () );
    $row = mysql_fetch_row($result, MYSQL_NUM);
    return array($row[0],strtoupper($row[1]),strtoupper($row[2]),$row[3]);
  }

  function _GETPOSBYIDS($a){
    $sql = "SELECT pos, end FROM sys_track WHERE id like '%".$a."%' and parent=1 order by id LIMIT 1";
    $result = mysql_query ( $sql ) or ( "Error " . mysql_error () );
    $row = mysql_fetch_row($result, MYSQL_NUM);
    return array((int)$row[0]+100,(int)$row[1]+100);
  }

  function _GETREF($a,$b){
    $sql = "SELECT ".$a."_f FROM sys_fasta WHERE pos=".$b;
    $result = mysql_query ( $sql ) or ( "Error " . mysql_error () );
    $row = mysql_fetch_row($result, MYSQL_NUM);
    return $row[0];
  }

  function _HEADER($a,$b,$c){
    $str=$b."~".$c."\n";
    for($i=0;$i<count($a);$i++){
      $sql="SELECT accession_id, sub_variety FROM sys_accession WHERE accession_db='".$a[$i]."'";
      $result = mysql_query ( $sql ) or ( "Error " . mysql_error () );
      while ($row = mysql_fetch_array($result, MYSQL_NUM)){
        $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";
      }
    }
    $str=$str."\n";
    return $str;
  }

  function _allLIST($a, $b, $c, $d){
    $all=array();
    for($i=0;$i<count($a);$i++){
      $sql="SELECT pos FROM ".$a[$i]."_variant WHERE chr='".$b."' and pos>=".$c." and pos<=".$d;
      $result = mysql_query ( $sql ) or ( "Error " . mysql_error () );
      while ($row = mysql_fetch_array($result, MYSQL_NUM)){
        $all[]=$row[0];
      }
    }
    $returnArr = array_values(array_unique($all));
    sort($returnArr);
    return $returnArr;
  }

  function _filterdLIST($a, $b, $c, $d, $e, $f) {
    $filtered=array();
    for($i=0;$i<count($a);$i++){
      $sql="SELECT pos FROM ".$a[$i]."_variant WHERE (qual<".$b." or dp<".$c.") and chr='".$d."' and pos>=".$e." and pos<=".$f;
      $result = mysql_query ( $sql ) or ( "Error " . mysql_error () );
      while ($row = mysql_fetch_array($result, MYSQL_NUM)){
        $filtered[]=$row[0];
      }
    }
    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 ) {
    } 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) {
    // Retrieving tracks
    $sql="SELECT * FROM (SELECT id,IF(parent=0,type,'') FROM sys_track WHERE chr='".$b."' and ".$a.">=pos AND ".$a."<=end ORDER BY type) as a GROUP BY id";
    $result = mysql_query ( $sql ) or ( "Error " . mysql_error () );
    $cnt=0;
    while($row = mysql_fetch_array($result, MYSQL_NUM)){
      if ( $row[0] != "" ) {
        $g[$cnt][0]=$row[0];
        $g[$cnt][1]=$row[1];
        $cnt++;
      }
    }
    $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){
    //$sql="SELECT pos FROM ".$list[$i]."_accession WHERE ".$prm_chr."_b is null and pos=".$uniqPOS[$j];
    $sql="SELECT pos FROM ".$a."_accession WHERE ".$b."_b is null and pos=".$c;
    $result = mysql_query ( $sql ) or ( "Error " . mysql_error () );
    $row = mysql_fetch_row($result, MYSQL_NUM);
    return $row[0];
  }

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

?>

