#!/usr/bin/perl -w
use strict;
use warnings;
use DBI;
use Getopt::Long;

my $NAME   = '';
my $CREATE = 0;
my $RESTORE= 0;
my $LIST   = 0;
my $VCF    = '';
my $DB     = '';
my $HOST   = 'localhost';
my $USER   = '';
my $PASS   = '';
my $TYPE   = '';

my @NAMELIST;
my $NUMSAMPLE = 0;

################################################################
# Checking parameters and database connection
################################################################
GetOptions( 'n|name=s'      => \$NAME,
            'l|list'        => \$LIST,
            'c|create'      => \$CREATE,
            'r|restore'     => \$RESTORE,
            'f|file=s'      => \$VCF,
            'db|database=s' => \$DB,
            'h|host=s'      => \$HOST,
            'u|user=s'      => \$USER,
			't|type=s'      => \$TYPE,
            'p|password=s'  => \$PASS
);

if ($DB eq ''||($RESTORE&&!$NAME)||($CREATE&&(!$NAME||!-e$VCF || !$TYPE))) {
  _error_method();  
  exit;
}

my $connect = DBI->connect('DBI:mysql:database='.$DB.';host='.$HOST,$USER,$PASS)
  or die "Can't connect to database: $DBI::errstr\n";

###########################################################
# Checking base table
###########################################################
if ( ! defined _check_exists_table("sys_accession") ) {
  print "Some basic table does not exists.\n";
  print "Did you excute 'tasuke_init.pl' at the first?\n";
  exit;
}

###########################################################
# Listing track
###########################################################
if ($LIST) {
  print "[List of accessions] :\n";
  foreach my $l (_lists()) {
    print "$l\n";
  }
  exit;
}

###########################################################
# Checking accession id
###########################################################
if ($RESTORE && $TYPE eq "gatkm") {
	$TYPE="";
}
my $TABLE =_check_db();
if ( ! defined $TABLE && $TYPE ne "gatkm" ) {
  print "'$NAME' is not exists on list.\n\n";
  print "- Here is list you loaded\n";
  foreach my $l (_lists()) {
    print "$l\n";
  }
  print "*If you have not yet installed accession information in your database,";
  print " please run \"tasuke_accession.pl\". \n";
  exit;
} elsif ($TYPE eq "gatkm"){
	@NAMELIST = split(/,/,$NAME);
	for(my $i=0; $i<scalar(@NAMELIST); $i++){
		$NAME = $NAMELIST[$i];
		my $chktable=_check_db();
		if(!defined $chktable){
			print "'$NAMELIST[$i]' is not exists on list.\n\n";
			print "- Here is list you loaded\n";
			foreach my $l (_lists()) {
				print "$l\n";
			}
			exit;
		}
	}
	if(scalar(@NAMELIST) == 0){
		print "'$NAME' is not exists on list.\n\n";
		print "- Here is list you loaded\n";
		foreach my $l (_lists()) {
			print "$l\n";
		}
		exit;
	}

} 
$TABLE.="_variant";


###########################################################
# Restoring variant table
###########################################################
if ($RESTORE) {
  _dbexe("TRUNCATE TABLE $TABLE;");
  print "VARIANT $NAME table is restored.\n";
  exit;
}

###########################################################
# 
###########################################################
 if($TYPE eq "gatkm"){
	open CHIN, '<', $VCF or die "file open error: $!";
	while (<CHIN>) {
		chomp $_;
		if($_ =~ /^##/){
			next;
		}elsif ($_ =~ /^#CHROM/ ){
			my $count=1;
			my @header = split(/\t/, $_);
			  print "   +------------------------------------+\n";
			  print "   | Accession       <- Sample NAME     |\n";
			  print "   +------------------------------------+\n";
			foreach my $column (@header){
				if ($count >=10 && defined $NAMELIST[$count-10]){
					my $x = $NAMELIST[$count-10];
					printf("   \| %-15s <- %-15s \| \n",$x,$column);
				}
				$count++;
			}
			print "   +------------------------------------+\n\n";
			print "> Please check your accession and sample name for installation.\n";
			print "> Are you sure creating database?[y|n] ";
			while (defined(my $line = <STDIN>)){
				chomp($line);
				if ( $line eq "y" || $line eq "Y" ) {
					last;
				} elsif ( $line eq "n" || $line eq "N" ) {
					print "finished.\n";
					exit;
				} else {
					print "> [y|n] ";
				}
		   }
		   #last;
		} else{
			if($_ =~ /DP4/){
				print "This VCF file is probably SAMtools-type VCF file.\n";
				print "Please check the \"-t\" option.\n";
				exit;
			} else{
				my @line = split(/\t/, $_);
				if(!defined $line[10]){
					print "This VCF file is probably single GATK-type VCF file.\n";
					print "Please check the \"-t\" option.\n";
					exit;
				}
				last;
			}
		}
	}
	close(CHIN);
} elsif($TYPE eq "samtools"){
	open CHIN, '<', $VCF or die "file open error: $!";
	while (<CHIN>) {
		chomp $_;
		if($_ =~ /^#/){
			next;
		}elsif ($_ !~ /DP4/ ){
			print "This VCF file is probably GATK-type VCF file.\n";
			print "Please check the \"-t\" option.\n";
			exit;
		}else{
			last;
		}
	}
	close(CHIN);
} elsif($TYPE eq "gatk"){
	open CHIN, '<', $VCF or die "file open error: $!";
	while (<CHIN>) {
		chomp $_;
		if($_ =~ /^#/){
			next;
		}elsif ($_ =~ /DP4/ ){
			print "This VCF file is probably SAMtools-type VCF file.\n";
			print "Please check the \"-t\" option.\n";
			exit;
		}else{
			my @line = split(/\t/, $_);
			if(defined $line[11]){
				print "This VCF file is probably multi sample GATK-type VCF file.\n";
				print "Please check the \"-t\" option.\n";
				exit;
			}
			last;
		}
	}
	close(CHIN);
}
###########################################################
# Creating variants table
###########################################################

  # Set chromosomes 
  my (@chrs) = _chrs();

  #_dbexe("LOCK TABLES $TABLE WRITE, sys_snpEff READ;");
  if($TYPE eq "samtools"){
	_print_time("*[".$NAME." variant]    begin");
    _dbexe("LOCK TABLES $TABLE WRITE, sys_snpEff READ;");
	_insertVar(@chrs);
	_dbexe("UNLOCK TABLES;");
	_print_time(" [".$NAME." variant] complete");
  } elsif($TYPE eq "gatk"){
	 _print_time("*[".$NAME." variant]    begin");
	_dbexe("LOCK TABLES $TABLE WRITE, sys_snpEff READ;");
	_insertVarGATK(@chrs);
	_dbexe("UNLOCK TABLES;");
	_print_time(" [".$NAME." variant] complete");
  } elsif($TYPE eq "gatkm"){
	foreach my $i (@NAMELIST){
		$NAME=$i;
		print $NAME."\n";
		$TABLE =_check_db();
		$TABLE.="_variant";
		 _print_time("*[".$NAME." variant]    begin");
		_dbexe("LOCK TABLES $TABLE WRITE, sys_snpEff READ;");
		_insertVarGATKm(@chrs);
		_dbexe("UNLOCK TABLES;");
		_print_time(" [".$NAME." variant] complete");
		$NUMSAMPLE++;
		print $i."\n";
	}
  } else {
	_error_method();  
	exit;
  }
  #_dbexe("UNLOCK TABLES;");

$connect->disconnect or warn "Disconnection error: $DBI::errstr\n";
exit;


# Update variants at pos
sub _insertVar {

  my $INDEL = "INDEL";
  my (@chrs)= @_;

  open IN, '<', $VCF or die "file open error: $!";
  my $seek = tell IN;

  for (my $i=0; $i<=$#chrs; $i++ ) {
    seek IN, $seek, 0;
    while (<IN>) {
      if ($_ !~ /^#/i){

      my @line=split(/\t/, $_);
      my $CHR=$line[0];
      if ( $CHR ne $chrs[$i] ) {
        $seek = tell IN;
        $seek = $seek - length($_);
        last;
      }

      my $POS=int($line[1]);
      my $REF=$line[3];
      my @salt=split(/,/,$line[4]);
      my $ALT=$salt[0];
	  my $raw_ALT=$line[4];
      my $QUAL=int($line[5]);
      my %arr=();

      my @spltinfo=split(/;/, $line[7]);
      my $indelPos=0;
      if ( $spltinfo[0] eq $INDEL ) { $indelPos=1; }
      for (my $i=$indelPos; $i < @spltinfo; $i++) {
        my @info=split(/=/, $spltinfo[$i]);
        $arr{$info[0]}[0] = $info[1] if $info[0] ne "";
      }

      # GT:PL:DP:SP:GQ
      my @sgt=split(/:/, $line[8]);
      my @sgtv=split(/:/, $line[9]);
      if ( $sgt[0] eq 'GT' ) {
		chomp($sgtv[1]);
		$sgtv[0] =~ s/\|/\//g;
        $arr{'GT'}[0] = $sgtv[0];
        $arr{'PL'}[0] = $sgtv[1];
      }

      #############################################################
      # Varation
      #############################################################
      my $variant;
      ## SNP
      if ( $spltinfo[0] ne $INDEL && $ALT ne "." ) {
        $variant="s";
      }
      ## Insertion
      if ( $spltinfo[0] eq $INDEL && length($REF) < length($ALT) ) {
        $variant="i";
      }
      ## Deletion (POS+1)
      if ( $spltinfo[0] eq $INDEL && ( $ALT eq "." || length($REF) > length($ALT)) ) {
        $variant="d";
        $POS=$POS+1;
      }
	  
	  $ALT=$raw_ALT;
	  
      #############################################################
      # Combining query
      #############################################################
      my $cQuery;
      my $vQuery;
      $cQuery="chr,pos,variant,ref,alt,qual";
      $vQuery="'$CHR',$POS,'$variant','$REF','$ALT',$QUAL";
      for my $key (sort keys %arr) {

        ## for numbers
        if ( $key eq "DP" || $key eq "VDB" || $key eq "AF1" || $key eq "AC1" || $key eq "MQ" || $key eq "FQ" || $key eq "SP" || $key eq "GQ" ) {
            $cQuery.=",$key";
            $vQuery.=",$arr{$key}[0]";
        }

        ## for string
        if ( $key eq "PV4" || $key eq "EFF" || $key eq "GT" || $key eq "PL" ) {
            $cQuery.=",$key";
            $vQuery.=",'$arr{$key}[0]'";
        }

        ## for EFF_GRP
        if ($key eq "EFF") {
          my @effArr = split(",", $arr{$key}[0]);
          my $arraylength = @effArr;
          if ($arraylength) {
            my $eQry="select max(eff_grp) from sys_snpEff where";
            for (my $i=0; $i<$arraylength; $i++){
              if ($effArr[$i] =~ /^(.+?)\(/) {
                $eQry.=" eff_name='$1'";
              }
              $eQry.=" or" if $i < $#effArr;
            }

            my $sth = $connect->prepare($eQry) or die "Cannot prepare: " . $connect->errstr();
            $sth->execute() or die "Cannot execute: " . $sth->errstr();
            my $field = $sth->fetchrow_array();
            $sth->finish();

            $cQuery.=",EFF_GRP";
			if(defined $field){
				$vQuery.=",$field";
			} else {
				print "[Warning]: \'EFF\'tag is not correct.\n".$_;
				$vQuery.=",0";
			}
          }
        }

        ## for DP4
        if ( $key eq "DP4" ) { 
          my @dp4Arr = split(",", $arr{$key}[0]);
          my $arraylength = @dp4Arr;
          for (my $i=0; $i<$arraylength; $i++){
            $cQuery.=",DP".($i+1);
            $vQuery.=",$dp4Arr[$i]";
          }
        }
      }
      _dbexe("INSERT into $TABLE($cQuery) values($vQuery);");

      }
    }
  }
  close (IN);
}

# Update variants at pos (GATK)
sub _insertVarGATK {

  my $INDEL = "Dels=";
  my (@chrs)= @_;
  
  open IN, '<', $VCF or die "file open error: $!";
  my $seek = tell IN;

  for (my $i=0; $i<=$#chrs; $i++ ) {
    seek IN, $seek, 0;
    while (<IN>) {
      if ($_ !~ /^#/i){
      my @line=split(/\t/, $_);
      my $CHR=$line[0];
      if ( $CHR ne $chrs[$i] ) {
        $seek = tell IN;
        $seek = $seek - length($_);
        last;
      }

      my $POS=int($line[1]);
      my $REF=$line[3];
      my @salt=split(/,/,$line[4]);
      my $ALT=$salt[0];
	  my $raw_ALT=$line[4];
      my $QUAL=int($line[5]);
      my %arr=();

      my @spltinfo=split(/;/, $line[7]);
      my $indelPos=0;
      if ( $line[7] =~ /Dels/ ) { $indelPos=0; }
      for (my $i=$indelPos; $i < @spltinfo; $i++) {
        my @info=split(/=/, $spltinfo[$i]);
        $arr{$info[0]}[0] = $info[1] if $info[0] ne "";
      }

      # GT:AD:DP:GQ:PL
      my @sgt=split(/:/, $line[8]);
      my @sgtv=split(/:/, $line[9]);
      if ( $sgt[0] eq 'GT' ) {
        chomp($sgtv[4]);
		$sgtv[0] =~ s/\|/\//g;
		$arr{'GT'}[0] = $sgtv[0];
        $arr{'AD'}[0] = $sgtv[1];
		$arr{'DP'}[0] = $sgtv[2];
		$arr{'PL'}[0] = $sgtv[4];
      }

      #############################################################
      # Varation
      #############################################################
      my $variant="s";
      ## SNP
      #if ( $line[7] !~ /$INDEL/ && $ALT ne "." ) {
      #  $variant="s";
      #}
      ## Insertion
      if ( $line[7] !~ /$INDEL/ && length($REF) < length($ALT) ) {
        $variant="i";
      }
      ## Deletion (POS+1)
      if ( $line[7] !~ /$INDEL/ && ( $ALT eq "." || length($REF) > length($ALT)) ) {
        $variant="d";
        $POS=$POS+1;
      }
	  
	  $ALT=$raw_ALT;

      #############################################################
      # Combining query
      #############################################################
      my $cQuery;
      my $vQuery;
      $cQuery="chr,pos,variant,ref,alt,qual";
      $vQuery="'$CHR',$POS,'$variant','$REF','$ALT',$QUAL";
      for my $key (sort keys %arr) {

        ## for numbers
        if ( $key eq "VDB" || $key eq "AF" || $key eq "AC" || $key eq "MQ" || $key eq "FQ" || $key eq "SP" || $key eq "GQ" || $key eq "DP") {
			if($key eq "AF"){
				$cQuery.=",AF1";
				my @tmparray = split(",", $arr{$key}[0]);
				$vQuery.=",$tmparray[0]";
			} elsif($key eq "AC"){
				$cQuery.=",AC1";
				my @tmparray = split(",", $arr{$key}[0]);
				$vQuery.=",$tmparray[0]";
			} else {
				$cQuery.=",$key";
				$vQuery.=",$arr{$key}[0]";
			}
        }

        ## for string
        if ( $key eq "PV4" || $key eq "EFF" || $key eq "GT" || $key eq "PL" ) {
            $cQuery.=",$key";
            $vQuery.=",'$arr{$key}[0]'";
        }
		
        ## for EFF_GRP
        if ($key eq "EFF") {
          my @effArr = split(",", $arr{$key}[0]);
          my $arraylength = @effArr;
          if ($arraylength) {
            my $eQry="select max(eff_grp) from sys_snpEff where";
            for (my $i=0; $i<$arraylength; $i++){
              if ($effArr[$i] =~ /^(.+?)\(/) {
                $eQry.=" eff_name='$1'";
              }
              $eQry.=" or" if $i < $#effArr;
            }

            my $sth = $connect->prepare($eQry) or die "Cannot prepare: " . $connect->errstr();
            $sth->execute() or die "Cannot execute: " . $sth->errstr();
            my $field = $sth->fetchrow_array();
            $sth->finish();

            $cQuery.=",EFF_GRP";
			if(defined $field){
				$vQuery.=",$field";
			} else {
				print "[Warning]: \'EFF\'tag is not correct.\n".$_;
				$vQuery.=",0";
			}
          }
        }

        ## for DP4
        if ( $key eq "AD" ) { 
          my @Predp4Arr = split(",", $arr{$key}[0]);
		  my $dp3=0;
		  if(defined $Predp4Arr[2]){
			$dp3 = $Predp4Arr[1]+$Predp4Arr[2];
		  }else{
			$dp3 = $Predp4Arr[1];
		  }
		  my $dp1 = $Predp4Arr[0];
		  my @dp4Arr = ($dp1,"0",$dp3,"0");
          my $arraylength = @dp4Arr;
          for (my $i=0; $i<$arraylength; $i++){
            $cQuery.=",DP".($i+1);
            $vQuery.=",$dp4Arr[$i]";
          }
        }
      }
      _dbexe("INSERT into $TABLE($cQuery) values($vQuery);");

      }
    }
  }
  close (IN);
}


sub _insertVarGATKm {

  my $INDEL = "Dels=";
  my (@chrs)= @_;

  open IN, '<', $VCF or die "file open error: $!";
  my $seek = tell IN;

  for (my $i=0; $i<=$#chrs; $i++ ) {
    seek IN, $seek, 0;
    while (<IN>) {
      if ($_ !~ /^#/i){

		  my @line=split(/\t/, $_);
		  my $CHR=$line[0];
		  if ( $CHR ne $chrs[$i] ) {
			$seek = tell IN;
			$seek = $seek - length($_);
			last;
		  }
		  chomp $_;
		  @line=split(/\t/, $_);
		  my $POS=int($line[1]);
		  my $REF=$line[3];
		  my @salt=split(/,/,$line[4]);
		  my $ALT=$salt[0];
		  my $raw_ALT=$line[4];
		  my $QUAL=int($line[5]);
		  my %arr=();

		  my @spltinfo=split(/;/, $line[7]);
		  my $indelPos=0;
		  if ( $line[7] =~ /Dels/ ) { $indelPos=0; }
		  for (my $i=$indelPos; $i < @spltinfo; $i++) {
			my @info=split(/=/, $spltinfo[$i]);
			$arr{$info[0]}[0] = $info[1] if $info[0] ne "";
		  }

		  # GT:AD:DP:GQ:PL
		  my @sgt=split(/:/, $line[8]);
		  my @sgtv=split(/:/, $line[(9+$NUMSAMPLE)]);
		  
		  if($sgtv[0] eq ".\/." || $sgtv[0] eq "0\/0"){
			next;
		  }
		  if(scalar(@sgt) != scalar(@sgtv) && $sgtv[0] ne ".\/."){
			print "[Warning]: FORMAT FIELD or SAMPLE FIELD is probably truncated.\n".$_."\n";
			next;
		  }
		  if ( $sgt[0] eq 'GT' ) {
		    chomp($sgtv[4]);
			$sgtv[0]=~s/\|/\//g;
			$arr{'GT'}[0] = $sgtv[0];
			$arr{'AD'}[0] = $sgtv[1];
			$arr{'DP'}[0] = $sgtv[2];
			$arr{'PL'}[0] = $sgtv[4];
		  }
		  my $cALTQuery;
		  my $vALTQuery;
		  my @Predp4Arr = split(",", $arr{'AD'}[0]);
		  my @gtarray = split(/\//, $arr{'GT'}[0]);
		  my $dp1=$Predp4Arr[0];#for DP4
		  my $dp3=0;
		  my $rawGT1=$gtarray[0];#for snpeff
		  my $rawGT2=$gtarray[1];
		  
		  if($gtarray[0] != $gtarray[1] && $gtarray[0] != 0){ # for hetero 1/2 1/3 2/3
			if ($Predp4Arr[$gtarray[0]] < $Predp4Arr[$gtarray[1]]){
				$raw_ALT=$salt[$gtarray[1]-1].",".$salt[$gtarray[0]-1];
				$ALT=$salt[$gtarray[1]-1];
				$dp3 = $Predp4Arr[$gtarray[1]]+$Predp4Arr[$gtarray[1]];
			} else{
				$raw_ALT=$salt[$gtarray[0]-1].",".$salt[$gtarray[1]-1];
				$ALT=$salt[$gtarray[0]-1];
				$dp3 = $Predp4Arr[$gtarray[1]]+$Predp4Arr[$gtarray[0]];
			}
		  }elsif($gtarray[0] != $gtarray[1] && $gtarray[0] == 0){# for hetero 0/1 0/2 0/3 
			$raw_ALT=$salt[$gtarray[1]-1];
				$ALT=$salt[$gtarray[1]-1];
				$dp3 = $Predp4Arr[$gtarray[1]];
				$gtarray[0]=0;
				$gtarray[1]=1;
		  }else{#for homo 1/1 2/2 3/3 
			$dp3 = $Predp4Arr[$gtarray[1]];
			$ALT=$salt[$gtarray[1]-1];
			$raw_ALT=$salt[$gtarray[1]-1];
			$gtarray[0]=1;
			$gtarray[1]=1;
		  } 
		  my @dp4Arr = ($dp1,"0",$dp3,"0");
		  my $arraylength = @dp4Arr;
		  for (my $i=0; $i<$arraylength; $i++){
			$cALTQuery.=",DP".($i+1);
			$vALTQuery.=",$dp4Arr[$i]";
		  }
		  if($gtarray[0] > 1){
			$gtarray[0]=1;
		  }
		  if($gtarray[1] > 2){
			$gtarray[1]=2;
		  }
		  $arr{'GT'}[0]=$gtarray[0]."/".$gtarray[1];
		  
		  #############################################################
		  # Varation
		  #############################################################
		  my $variant="s";
		  ## SNP
		  #if ( $line[7] !~ /$INDEL/ && $ALT ne "." ) {
		  #  $variant="s";
		  #}
		  ## Insertion
		  if ( $line[7] !~ /$INDEL/ && length($REF) < length($ALT) ) {
			$variant="i";
		  }
		  ## Deletion (POS+1)
		  if ( $line[7] !~ /$INDEL/ && ( $ALT eq "." || length($REF) > length($ALT)) ) {
			$variant="d";
			$POS=$POS+1;
		  }
		   $ALT=$raw_ALT;

		  #############################################################
		  # Combining query
		  #############################################################
		  my $cQuery;
		  my $vQuery;
		  $cQuery="chr,pos,variant,ref,alt,qual";
		  $vQuery="'$CHR',$POS,'$variant','$REF','$ALT',$QUAL";
		  for my $key (sort keys %arr) {

			## for numbers
			if ($key eq "VDB" || $key eq "AF" || $key eq "AC" || $key eq "MQ" || $key eq "FQ" || $key eq "SP" || $key eq "GQ" || $key eq "DP") {
				if($key eq "AF"){
					$cQuery.=",AF1";
					if($gtarray[0] == $gtarray[1]){
						$vQuery.=",1";
					}else{
						$vQuery.=",0.5";
					}
					
				} elsif($key eq "AC"){
					$cQuery.=",AC1";
					if($gtarray[0] == $gtarray[1]){
						$vQuery.=",2";
					}else{
						$vQuery.=",1";
					}
				} else {
					$cQuery.=",$key";
					$vQuery.=",$arr{$key}[0]";
				}
			}

			## for string
			if ( $key eq "PV4" || $key eq "GT" || $key eq "PL" ) {
				$cQuery.=",$key";
				$vQuery.=",'$arr{$key}[0]'";
			}
			
			#split EFF (for each genotype)
			if ( $key eq "EFF" ) {
				my @spEff = split(/,/, $arr{$key}[0]);
				my $effQuery = "";
				foreach my $eachEff (@spEff){
					if($eachEff =~ /\|$rawGT1\)/ or $eachEff =~ /\|$rawGT2\)/ or $eachEff =~ /\|\)/ or $eachEff =~ /\|$rawGT1\-$rawGT2\)/){
						$effQuery=$effQuery.$eachEff.",";
					}
				}
				chop($effQuery);
				$arr{$key}[0]=$effQuery;
				$cQuery.=",$key";
				$vQuery.=",'$arr{$key}[0]'";
			}
			
			## for EFF_GRP
			if ($key eq "EFF") {
			  my @effArr = split(",", $arr{$key}[0]);
			  my $arraylength = @effArr;
			  if ($arraylength) {
				my $eQry="select max(eff_grp) from sys_snpEff where";
				for (my $i=0; $i<$arraylength; $i++){
				  if ($effArr[$i] =~ /^(.+?)\(/) {
					$eQry.=" eff_name='$1'";
				  }
				  $eQry.=" or" if $i < $#effArr;
				}

				my $sth = $connect->prepare($eQry) or die "Cannot prepare: " . $connect->errstr();
				$sth->execute() or die "Cannot execute: " . $sth->errstr();
				my $field = $sth->fetchrow_array();
				$sth->finish();

				$cQuery.=",EFF_GRP";
				if(defined $field){
					$vQuery.=",$field";
				} else {
					print "[Warning]: \'EFF\'tag is not correct.\n".$_;
					$vQuery.=",0";
				}
			  }
			}
		  }
		  $cQuery.=$cALTQuery;
		  $vQuery.=$vALTQuery;
		  _dbexe("INSERT into $TABLE($cQuery) values($vQuery);");

		} 
	}
  }
  close (IN);
}

### Checking id on the list ####################################
sub _check_db {
  my $val = $connect->selectrow_array("select accession_db from sys_accession where accession_id='$NAME'");
  return $val;
}

# Accession list
sub _lists {
  my $val;
  my @lists;
  my $query = "SELECT accession_id FROM sys_accession";
  my $query_handle = $connect->prepare($query);
  $query_handle->execute();
  $query_handle->bind_columns(undef, \$val);
  while($query_handle->fetch()) {
    push @lists, $val;
  }
  return @lists;
}

# chromosomes
sub _chrs {
  my $val;
  my @c;
  my $query = "SELECT id FROM sys_chromosome";
  my $query_handle = $connect->prepare($query);
  $query_handle->execute();
  $query_handle->bind_columns(undef, \$val);
  while($query_handle->fetch()) {
    push @c, $val;
  }
  return @c;
}

sub _print_time {
  my ($str) = @_;
  my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime;
  $year += 1900;
  $mon += 1;
  my $datetime = sprintf "%04d-%02d-%02d %02d:%02d:%02d", $year, $mon, $mday, $hour, $min, $sec;
  print "$str : $datetime\n";
}

### Execute DB ################################################
sub _dbexe {
    my ($query) = @_;
    my $query_handle = $connect->prepare($query);
    $query_handle->execute();
}

sub _error_method {
  print "\nCreating variant db\n";
  print "tasuke_variant_vcf.pl -n [id] -f [variant_file(.vcf)] -t [samtools or gatk or gatkm] -db [database] -u [user] -p [password]\n\n";
  print "Restoring table\n";
  print "tasuke_variant_vcf.pl -r -n [name] -db [database] -u [user] -p [password]\n\n";
  print "Listing accession\n";
  print "tasuke_variant_vcf.pl -l -db [database] -u [user] -p [password]\n\n";
}

### Checking table ####################################
sub _check_exists_table {
  my ($t) = @_;
  my $val = $connect->selectrow_array("show tables like '$t'");
  return $val;
}

