<?php
/*
* data.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;
}

(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");	// _Block: blocks per page 
(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");	// _BlockW: 1 block width(px)
$enabledEffImpacts = array("0" => True, "1" => True, "2" => True, "3" => True, "-1" => True);
$filterEffImpact = False;
if(isset($_POST['prm_EffGrps']) && !empty($_POST['prm_EffGrps'])) {
	try {
		$impacts = json_decode($_POST['prm_EffGrps']);
		$filterEffImpact = _setSnpEffImpactFilterMap($enabledEffImpacts, $impacts);
	} catch ( Exception $ex ) {}
}
$prm_ExcludeUpDownVar = False;
if(isset($_POST['prm_Gene']) && !empty($_POST['prm_Gene'])) {
	$prm_Gene=$_POST['prm_Gene'];
	if(isset($_POST['prm_ExcludeUpDownVar']) && !empty($_POST['prm_ExcludeUpDownVar'])) $prm_ExcludeUpDownVar = True; 
}
///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;
}

$bpPerBlock = $zoom_r*$zoom_v;
$blockOver1b = $bpPerBlock > 1;	// boolean
$EFFMAP = array('MODIFIER' => 0, 'LOW' => 1, 'MODERATE' => 2, 'HIGH' => 3);
// key is lowercase
$UP_DOWN_STREAMEFFS = array('upstream' => True, 'downstream' => True, 'downstream_gene_variant' => True, 'upstream_gene_variant' => True);

$refaccTable = $prm_Ref. "_accession";

$stmtDP = $conn->stmt_init();
$stmtRDP = $conn->stmt_init();
if ( $prm_Ref == "Reference" ) {
    $stmtV = $conn->stmt_init();
}else{
    $refaccTable = getSerialTableByColumn($conn, $db, $refaccTable, $zoom_chr);
    $stmtVr = $conn->stmt_init();
    //get num_row from dx_accession
    if ($result = $conn->query("select pos from ". $refaccTable. " limit 1")) {    
//    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);
$cstm_tables_cnt = count($cstm_tables);
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;
    }
}

// make Handler

$timeDP = 0;
$timeRDP = 0;
$timeV = 0;
$timeVr = 0;
$timeHap = 0;
$pid = getmypid();

$dlm__ = "^";
class BlockInfo {
//	public $variant = "";
	public $effgrp = 0;
	public $count = 0;
        public $ref = "";
	public $alt = "";
	public $gt = "";
	public $genehit = 0;
}

$lists_cnt = count($prm_Lists); 
for($j=0; $j<$lists_cnt; $j++){
    $accid=$prm_Lists[$j];
    $row_cntAc=0;
    $row_cntAcCstm = array();
    $numAllReads=1;

    $accTable = getSerialTableByColumn($conn, $db, $accid."_accession", $zoom_chr);
    $cstmTable = $accid."_accession_cstm";
    if($customTrack) $cstmTable = getSerialTableByColumn($conn, $db, $cstmTable, $zoom_chr);

//    if ($result = $conn->query("select pos from ".$accid."_accession limit 1")) {
    if ($result = $conn->query("select pos from ". $accTable. " limit 1")) {
        $row_cntAc = $result->num_rows;
        $result->close();
    }
    if($customTrack){
	for($z=0; $z<$cstm_tables_cnt; $z++){
//            if ($result = $conn->query("select pos from ".$accid."_accession_cstm".$cstm_tables[$z]." limit 1")) {
	    if ($result = $conn->query("select pos from ". $cstmTable. $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'")) {
	    if ($result = $conn->query("show tables like '". $cstmTable. "'")) {
                $doCUSTOM=true;
            }
        }
        if($customTrackNormByRead == "enabled"){
            if($numAllReads==0){
                $numAllReads=1;
                $tofix=1;
            }
            if((int)$tofix==0){$tofix==1;}
            $normReads=$tofix/$numAllReads;
        }
    }

    //query
    $queryDP = "SELECT sum(`". $zoom_chr. "`)/". $zoom_v. " as sum FROM ". $accTable. " WHERE pos > ? and pos <= ? "; 

    $queryVref = "";
    $queryVrefNull = "";
    $queryhap = "";
    if(modeIsPlus($conn)) {
	$queryVref = "SELECT var.variant, count(pos) FROM ". $refaccTable. "
                    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)";
	$queryVrefNull = "SELECT var.variant, count(pos) FROM ". $refaccTable. "
                    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)";
	$queryhap="SELECT 1,alt,GT,EFF_GRP,REF,variant,EFF,pos FROM ".$accid."_variant ".
                   "WHERE chr= ? and pos >= ? and pos <= ? and (GT='./.' or (QUAL >= ? and DP >= ? and (DP3+DP4) >= ?))";
    } else {
	$queryVref = "SELECT var.variant,count(pos) FROM ". $refaccTable. " JOIN (SELECT pos,alt,gt,variant FROM (".
		"SELECT v1.pos,alt,gt,variant FROM sys_variant s1,". $accid. "_variant v1 ".
			"WHERE GQ>=? and s1.chr=v1.chr and s1.pos_vcf=v1.pos_vcf and DP>=? and (DP3+DP4)>=? and v1.pos>? and v1.pos<=? and v1.chr=? ".
		"UNION ALL ".
		"SELECT v2.pos,alt,gt,variant from sys_variant s2,". $prm_Ref. "_variant v2 ".
			"WHERE GQ>=? and s2.chr=v2.chr and s2.pos_vcf=v2.pos_vcf and DP>=? and (DP3+DP4)>=? and v2.pos>? and v2.pos<=? and v2.chr=?".
		") AS a group by pos,alt,gt having count(*)<2) AS var using (pos) group by (variant)";
	$queryVrefNull = "SELECT var.variant,count(pos) FROM ". $refaccTable. " RIGHT JOIN (SELECT pos,alt,gt,variant FROM (".
		"SELECT v1.pos,alt,gt,variant FROM sys_variant s1,". $accid. "_variant v1 ".
			"WHERE GQ>=? and s1.chr=v1.chr and s1.pos_vcf=v1.pos_vcf and DP>=? and (DP3+DP4)>=? and v1.pos>? and v1.pos<=? and v1.chr=? ".
		"UNION ALL ".
		"SELECT v2.pos,alt,gt,variant FROM sys_variant s2,". $prm_Ref. "_variant v2 ".
			"WHERE GQ>=? and s2.chr=v2.chr and s2.pos_vcf=v2.pos_vcf and DP>=? and (DP3+DP4)>=? and v2.pos>? and v2.pos<=? and v2.chr=? ".
		") AS a group by pos,alt,gt having count(*)<2) AS var using (pos) group by (variant)";
	$queryhap = "SELECT 1,ALT,GT,EFF_GRP,REF,variant,EFF,v.pos FROM sys_variant s ".
		"INNER JOIN ". $accid. "_variant v ON s.chr=v.chr and s.pos_vcf=v.pos_vcf ".
		"WHERE v.chr=? and v.pos>=? and v.pos<=? and (GT='./.' or (GQ>=? and DP>=? and (DP3+DP4)>=?))";
    }
    $queryV="";

    $end=($bpPerBlock * $db_range_end) - $bpPerBlock;
    $start=$end-($bpPerBlock * ($prm_Block - 1)) + 1 - $bpPerBlock;
    $p1= $bpPerBlock * $prm_Block;
    $p2=($prm_width)*($prm_Block);

    $array=array();
    $gtarray=array();
    $igtarray=array();
    $iarray=array();
    $darray=array();

    $geneHitBlock2maxEff = NULL;	// [s/i/d]->[blknum] = maxEffGrp
    $blockInfoMap = array("s" => array(), "i" => array(), "d" => array());	// variant => blockIndex => BlockInfo 

    if($bpPerBlock < 100001) {    
//    if($zoom_v*$zoom_r <100001){
//$startHap =  microtime(true);
        $stmtHap->prepare($queryhap);
        $stmtHap->bind_param("siiiii", $prm_Chr, $start, $end, $prm_QUAL, $prm_DP, $prm_DP4); 
        $stmtHap->execute();
        $stmtHap->store_result();
        $stmtHap->bind_result($rowDummy,$row1,$row2,$row3,$row4,$row5,$rowEff,$rowPos);
        $blknum=0;
        $nextflag=0;

        while ($stmtHap->fetch()) {
            if($row5 !== "s" && $row5 !== "i" && $row5 !== "d") continue;
	    $row0 = ($rowPos - $start)/$p1*$p2;
            $pxFromLeft = (int)round($row0);	// Variant Pos(px) from the left end, The return value of round is bouble. If you do not cast to int, calculations will be double. (ex. $pxInBlock)

            $blknum = (int)($row0/$prm_width);
            $pxInBlock = $pxFromLeft - $blknum * $prm_width;
//            if($pxFromLeft % $prm_width === 0){$blknum--;}
//            if($pxInBlock === $prm_width){$blknum++;}

            if(!array_key_exists($blknum, $blockInfoMap[$row5])) $blockInfoMap[$row5][$blknum] = new BlockInfo();

            // Below, it is difficult to understand, but to eliminate redundancy. 
            $dispOK = False;	// If False, do not display variant
            $geneHitFlg = 0;	// If 0, variant is displayed semi-transparently
	    $maxEffAlt = $row1;
            $maxEffGrp = 0;
            $maxEffByGene = array();	// MaxEff for each Gene at this Pos 
            $maxEffs = array();		// MaxEff for each Alleles at this Pos(If there is prm_Gene, it is reflected)
            if( !empty($prm_Gene) || $filterEffImpact || $row2 === "1/2" ) {
	      $effGrp = 0;
	      if(empty($prm_Gene)) {
                  $geneHitFlg = 1;
              } else {
                  if($geneHitBlock2maxEff === NULL) $geneHitBlock2maxEff = array("s" => array(), "i" => array(), "d" => array());
              }
              if(!$filterEffImpact) $dispOK = True;

	      if($rowEff && $rowEff !== "NULL") {
		$eff_list = explode(",", $rowEff);
		$t=count($eff_list);
		for($i=0; $i<$t; $i++){
			$effname = ""; $alt = ""; $geneid = ""; $genename = ""; $impact = -1;
			if(preg_match("/^([^\|\(]+)\((.+)$/", $eff_list[$i], $matches)) {
				// EFF
				$effname = strtolower($matches[1]);
				$EffInfo = explode("|", $matches[2]);
				if(count($EffInfo) > 10) {
					// new EFF
					$geneid = $EffInfo[5];
					$genename = $EffInfo[$EFFcol];
//					$genename = $EffInfo[8];
//					$alt = str_replace(")", "", array_pop($EffInfo));
				} else {
					// old EFF
					$geneid = $EffInfo[4];
					$genename = $EffInfo[7];
//					$alt = $row1;
				}
                                $alt = $row1;
//                                $alt = str_replace(")", "", array_pop($EffInfo));
				$impact = $EffInfo[0];
			} else {
				// ANN
				$effect = explode("|", $eff_list[$i]);
				$effname = strtolower($effect[1]);
				$geneid = $effect[3];
				$genename = $effect[$ANNcol];
//				$genename = $effect[6];
                                $alt = $effect[0];
				$impact = $effect[2];
			}
			if($row2 === "1/2" || $row2 === "1|2") {
				if(preg_match("/^([^,]+),([^,]+)$/", $row1, $matches)) {
					if($matches[1] !== $alt && $matches[2] !== $alt) continue;
				}
			} else {
				if($row1 !== $alt) continue;
			}

			$impactCode = $EFFMAP[$impact];
			if($filterEffImpact && !$enabledEffImpacts[(string)$impactCode]) continue;
			$dispOK = True;

			// Since "Gene highlight" is not a filter, MaxEffgrp also includes Variants that do not include "Gene".
			if($maxEffGrp < $impactCode) {
				$maxEffGrp = $impactCode;
				$maxEffAlt = $alt;
			}

			// Show only SNP of specified Gene
			if(!empty($prm_Gene)) {
				if($prm_Gene === $genename || (empty($genename) && $prm_Gene === $geneid)) {
//				if($prm_Gene === $geneid || $prm_Gene === $genename) {
					if($prm_ExcludeUpDownVar && array_key_exists($effname, $UP_DOWN_STREAMEFFS)) {
						// Do not highlight
					} else {
						$geneHitFlg = 1;
					}
                                        if($geneHitFlg===1 && (!array_key_exists($blknum, $geneHitBlock2maxEff[$row5]) || $geneHitBlock2maxEff[$row5][$blknum] < $impactCode)) {
//error_log("Test0:". $row5. ", ". $blknum. ", ". $geneHitBlock2maxEff[$row5][$blknum]. "=>". $impactCode);
                                            $geneHitBlock2maxEff[$row5][$blknum] = $impactCode;
                                        }
				}
				if(empty($genename)) {
					if(!array_key_exists($geneid, $maxEffByGene) || $maxEffByGene[$geneid] < $impactCode) $maxEffByGene[$geneid] = $impactCode;
				}
				if(!array_key_exists($genename, $maxEffByGene) || $maxEffByGene[$genename] < $impactCode) $maxEffByGene[$genename] = $impactCode;
			}
//			else { if($maxEffGrp < $impactCode) $maxEffGrp = $impactCode; $maxEffAlt = $alt; }

			// for GT:1/2 EFF Impact
			if( (empty($prm_Gene) || ($prm_Gene === $geneid || $prm_Gene === $genename)) &&
				(!array_key_exists($alt, $maxEffs) || $maxEffs[$alt] < $impactCode)) $maxEffs[$alt] = $impactCode;
		}
	      } else {
		   $maxEffGrp = -1;
		  if($filterEffImpact && $enabledEffImpacts["-1"]) $dispOK = True;
	      }
            } else {
		$maxEffGrp = $row3;
		if($maxEffGrp !== 0 && empty($maxEffGrp)) $maxEffGrp = -1;
		$geneHitFlg = 1;	// Always show.
		$dispOK = True;
            }
            if(!$dispOK) continue;

            if($blockInfoMap[$row5][$blknum]->effgrp < $maxEffGrp) $blockInfoMap[$row5][$blknum]->effgrp = $maxEffGrp;
	    if($blockInfoMap[$row5][$blknum]->genehit === 0) $blockInfoMap[$row5][$blknum]->genehit = $geneHitFlg;
            if(!_isGtZero($row2)) $blockInfoMap[$row5][$blknum]->count++;
            if(!$blockOver1b) {
                $blockInfoMap[$row5][$blknum]->ref = $row4;
                $blockInfoMap[$row5][$blknum]->alt = $maxEffAlt;
                $blockInfoMap[$row5][$blknum]->gt = $row2;
            }

            $effsStr = $row3;
            if($row2 !== "1/2" && $row2 !== "1|2") {
//            if($row2 === "0/1" || $row2 === "0|1" || $row2 === "1/1" || $row2 === "1|1") {
		if(!empty($prm_Gene) && array_key_exists($prm_Gene, $maxEffByGene)) {
			$effsStr = $maxEffByGene[$prm_Gene]; 
		} else {
			$effsStr = $maxEffGrp;
		}
            } else {
                  // for example, "1/2", hetero and Different from REF.
                  if(preg_match("/^([^,]+),([^,]+)$/", $row1, $matches)) {
//                  	$maxEffs = getMaxEffs($rowEff);
                        $eff1 = 0; $eff2 = 0;
                        if(array_key_exists($matches[1], $maxEffs)) $eff1 = $maxEffs[$matches[1]];
                        if(array_key_exists($matches[2], $maxEffs)) $eff2 = $maxEffs[$matches[2]];
                        $effsStr = $eff1. ",". $eff2;
                  }
            }

             $baseData = $pxInBlock.  $dlm__.  $row4."/".$row1.  $dlm__.  $row2.  $dlm__. $geneHitFlg. $dlm__.  $effsStr;
            if($row5==="s"){
                if(array_key_exists($blknum,$array)){//inner block
                    if(!array_key_exists($pxFromLeft,$gtarray)){
                        $array[$blknum][] = $baseData;
                    }else{
                        _replaceTailVariant($array[$blknum], $row3, $baseData);
                    }
                }else{
                    $array[$blknum] = array($baseData);
                }
                $gtarray[$pxFromLeft]=$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($pxFromLeft,$igtarray)){
			$iarray[$blknum][] = $baseData;
                    }else{
                        _replaceTailVariant($iarray[$blknum], $row3, $baseData);
                    }
                }else{
                    $iarray[$blknum] = array($baseData);
                }
                $igtarray[$pxFromLeft]=$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($pxFromLeft,$igtarray)){
                        $darray[$blknum][] = $baseData;
                    }else{
                        _replaceTailVariant($darray[$blknum], $row3, $baseData);
                    }
                }else{
                    $darray[$blknum] = array($baseData);
                }
                $igtarray[$pxFromLeft]=$row3;
            }
        }
        $stmtHap->free_result();
//$endHap = microtime(true);
//$partTimeHap = $endHap - $startHap;
//$timeHap += $partTimeHap;
    }
    if($row_cntAc!=0){
        $stmtDP->prepare($queryDP);
    }
    /*if($row_cntAcCstm!=0){
        $stmtRDP->prepare($queryCstm);
    }*/
    // Over 1b
/*
    if($blockOver1b) {  
//    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 .= $accid. "|[";
    for($i=$prm_Pos;$i<$db_range_end;$i++){
	$rtnString .= "{";
        ////////////////////////////////////////////////// SELECT TABLE DEPTH
        $blknum = $i-$prm_Pos;

        $e=$zoom_v*$i;
        $s=$e-$zoom_v;
        if($row_cntAc!=0){
//$startDP = microtime(true);
            $stmtDP->bind_param("ii", $s, $e);  
            $stmtDP->execute();
            $stmtDP->store_result();
            $stmtDP->bind_result($row0);
//$endDP = microtime(true);
//$partTimeDP = $endDP - $startDP;
//$timeDP += $partTimeDP;
            while ($stmtDP->fetch()) {
		if ( $row0 !== null ) {
//                if ( $row0 != "" ) {
		    $rtnString .= "\"dp\":\"". (int)$row0. "\"";
                } else {
		    $rtnString .= "\"dp\":\"\"";
                }
            }
            $stmtDP->free_result();
        }else{
	    $rtnString .= "\"dp\":\"n\"";
        }
        if($customTrack and $doCUSTOM){
            $string_buffer = "";
	    for($z=0; $z<$cstm_tables_cnt; $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 ". $cstmTable. $cstm_tables[$z]. " WHERE pos > ? and pos <= ? ";
//                    $queryCstm = "SELECT sum(`". $zoom_chr. "`)/". $zoom_v. "*". $normReads. " as sum FROM ". $accid. "_accession_cstm". $cstm_tables[$z]. " WHERE pos > ? and pos <= ? ";
//$startRDP = microtime(true);
                    $stmtRDP->prepare($queryCstm);
                    $stmtRDP->bind_param("ii", $s, $e);  
                    $stmtRDP->execute();
                    $stmtRDP->store_result();
                    $stmtRDP->bind_result($row0);
//$endRDP = microtime(true);
//$partTimeRDP = $endRDP - $startRDP;
//$timeRDP += $partTimeRDP;
                    while ($stmtRDP->fetch()) {
                        $isEmptyTable = 0;
                        if ( $row0 !== null ) {
//                        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 .= ",\"rdp\":\"". $string_buffer. "\"";
        }
        ////////////////////////////////////////////////// SELECT TABLE VARIANT
        $e = $bpPerBlock * $i;
        $s = $e - $bpPerBlock;
        if ( $prm_Ref == "Reference" ) {
//$startV = microtime(true);

            $sid = array("s", "i", "d");
            $sidNum = count($sid);
            foreach($sid as $variant) {
                $blockInfo = null;
                if(array_key_exists($blknum, $blockInfoMap[$variant]) && $blockInfoMap[$variant][$blknum]->count > 0) {
                    $blockInfo = $blockInfoMap[$variant][$blknum];
                } else {
                    continue;
                }

                $effgrp = $blockInfo->effgrp;
                if($geneHitBlock2maxEff !== NULL && array_key_exists($blknum, $geneHitBlock2maxEff[$variant])) $effgrp = $geneHitBlock2maxEff[$variant][$blknum];
                if($blockOver1b) {
                    $rtnString .= ",\"". $variant. "\":\"". $blockInfo->count. $dlm__. 
                        $effgrp. $dlm__.
                        $blockInfo->genehit. "\"";
                } else {
                    $rtnString .= ",\"". $variant. "\":\"". $blockInfo->count. $dlm__. 
                        $effgrp. $dlm__. 
                        $blockInfo->ref. $dlm__. 
                        $blockInfo->alt. $dlm__. 
                        $blockInfo->gt. $dlm__. 
                        $blockInfo->genehit. "\"";
                }
            }
//$endV = microtime(true);
//$partTimeV = $endV - $startV;
//$timeV += $partTimeV;
//            $stmtV->free_result();
        } else {
//$startVr = microtime(true);
            $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);
//$endVr = microtime(true);
//$partTimeVr = $endVr - $startVr;
//$timeV += $partTimeVr;
            while ($stmtVr->fetch()) {
		$isGeneSnpMap = 1;
		if(array_key_exists($blknum, $blockInfoMap[$row0])) {
			$isGeneSnpMap = $blockInfoMap[$row0][$blknum]->genehit;
		}
                if($blockOver1b) {
			$rtnString .= ",\"". $row0. "\":\"". $row1. $dlm__. $dlm__. $isGeneSnpMap. "\"";
//                    $rtnString .= ",\"". $row0. "\":\"". $row1. $dlm__. "\"";
                } else {
			$rtnString .= ",\"". $row0. "\":\"". $row1. str_repeat($dlm__, 4). $isGeneSnpMap. "\"";
//		    $rtnString .= ",\"". $row0. "\":\"". $row1. str_repeat($dlm__, 3). "\"";
                }
            }
            $stmtVr->free_result();
        }
        if(array_key_exists($blknum, $array)){
	    $rtnString .= ",\"hs\":\"". implode("%", $array[$blknum]). "\"";
        }
        if(array_key_exists($blknum, $iarray)){
	    $rtnString .= ",\"hi\":\"". implode("%", $iarray[$blknum]). "\"";
        }
        if(array_key_exists($blknum, $darray)){
	    $rtnString .= ",\"hd\":\"". implode("%", $darray[$blknum]). "\"";
        }
        $rtnString=$rtnString."}";
        if ($i<($db_range_end-1)) {
	    $rtnString .= ",";
        }
    }
    $rtnString=$rtnString."]";
    if($j<count($prm_Lists)-1){
	$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();
}
//mysqli_refresh($conn, MYSQLI_REFRESH_TABLES);
$conn->close();

//file_put_contents("/tmp/data_php_new_". $prm_Lists[0], $rtnString);
echo $rtnString;

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

function getMapVariant2BlockIncludeGeneSnp($stPos, $enPos, $variantPos2Bool) {
	$map = array("s"=>False, "i"=>False, "d"=>False);
	for($i = $stPos; $i <= $enPos; $i++) {
		if(isset($variantPos2Bool["s". $i])) $map["s"] = True;
		if(isset($variantPos2Bool["i". $i])) $map["i"] = True;
		if(isset($variantPos2Bool["d". $i])) $map["d"] = True;
	}
	return $map;
}
/*
function isGeneHaveVariant($eff, $gene) {
	global $EFFcol, $ANNcol;

	$eff_list = explode(",", $eff);
	$t=count($eff_list);
	$flag = False;
	for($i=0; $i<$t; $i++){
		$geneid = ""; $genename = "";
		if(preg_match("/^[^\|]+\((.+)$/", $eff_list[$i], $matches)) {
			// EFF
			$EffInfo = explode("|", $matches[1]);
			if(count($EffInfo) > 10) {
				// new EFF
				$geneid = $EffInfo[5];
				$genename = $EffInfo[$EFFcol];
//				$genename = $EffInfo[8];
			} else {
				// old EFF
				$geneid = $EffInfo[4];
				$genename = $EffInfo[7];
			}
		} else {
			// ANN
			$effect = explode("|", $eff_list[$i]);
			$geneid = $effect[3];
			$genename = $effect[$ANNcol];
//			$genename = $effect[6];
		}

		if($gene === $geneid || $gene === $genename) {
			$flag = True;
			break;
		}
	}
	return $flag;
}
function getMaxEffs($eff){
    global $EFFMAP;

    $ret_eff = array();
    $eff_list = explode(",", $eff);
    $t=count($eff_list);
    for($i=0; $i<$t; $i++){
	$base=""; $impact=0;
        if(preg_match("/^[^\|]+\((.+)$/", $eff_list[$i], $matches)) {
                // EFF
                $EffInfo = explode("|", $matches[1]);
                $base = str_replace(")", "", array_pop($EffInfo));
		$impact = $EffInfo[0];
        } else {
                // ANN
                $effect=explode("|", $eff_list[$i]);
                $base = $effect[0];
                $impact = $effect[2];
        }
	$impactCode = $EFFMAP[$impact];
//error_log("$base => $impact => ". $impactCode);
	if(!array_key_exists($base, $ret_eff) || $ret_eff[$base] < $impactCode) {
		$ret_eff[$base] = $impactCode;
	}
    }
    return $ret_eff;
}
*/
function _replaceEffImpact($oriStr, $dbEffGrp, $newEffGrps) {
//	global $dlm__;

        $oriImpact = (int)substr($oriStr, -1);
	if($oriImpact < $dbEffGrp) {
		//TODO use $dlm__
		$convStr = preg_replace('/\^[^\^]+$/', "^".$newEffGrps, $oriStr);
		return $convStr;
	} else {
		return $oriStr;
	}
/* very slow
	$tmpArr = explode($dlm__, $oriStr);
	$maxIndex = count($tmpArr)-1;
	if(((int)$tmpArr[$maxIndex]) < $dbEffGrp){
		$tmpArr[$maxIndex] = $dbEffGrp;
		return implode($dlm__, $tmpArr);
	} else {
		return $oriStr;
	}
*/
}
function _replaceTailVariant(&$oriVariantsArray, $dbEffGrp, $newVarintStr) {
	//Notice: Array parameter for PHP functions are passed by "value". Not "Reference"
//	$lastVariantImpact = (int)str_replace(",", "", substr($variantListStr, -2));	// if GT=="0/0", Impact="-1"

	$lastVal = end($oriVariantsArray);	//end () is essentially a function that sets an array pointer. Be careful when using next() etc.
	$lastVariantImpact = (int)preg_replace("/[^\d\-]/", "", substr($lastVal, -2));	// if GT=="0/0", Impact="-1"
	if($lastVariantImpact < $dbEffGrp) {
		array_pop($oriVariantsArray);
		$oriVariantsArray[] = $newVarintStr;
	}
}
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;
}

?>
