#!/usr/bin/perl -w

#
# tasuke_variant_vcf.pl
# http://tasuke.dna.affrc.go.jp/
#
# Copyright (c) 2013 National Institute of Agrobiological Sciences   
#
# This software is released under the MIT License.
#
# http://opensource.org/licenses/mit-license.php
#

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

###########################################################
# Checking ref-seq already installed or not
###########################################################
if(defined _check_var_table()){
    print "The variants are already installed.\n";
    print "If you want to install again. \nPlease run this tool again with \"-r\" option for deleting the variants.\n";
    _exit();
}

###########################################################
# Variants
###########################################################
 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] ";
                }
            }
        } 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
###########################################################
my (@chrs) = _chrs();
my %chrKeys =_chrsH();
if($TYPE eq "samtools"){
    _print_time("*[".$NAME." variant]    begin");
    _dbexe("LOCK TABLES $TABLE WRITE, sys_snpEff READ;");
    _insertVar(\%chrKeys);
    _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(\%chrKeys);
    _dbexe("UNLOCK TABLES;");
    _print_time(" [".$NAME." variant] complete");
} elsif($TYPE eq "gatkm"){
    foreach my $i (@NAMELIST){
        $NAME=$i;
        $TABLE =_check_db();
        $TABLE.="_variant";
         _print_time("*[".$NAME." variant]    begin");
        _dbexe("LOCK TABLES $TABLE WRITE, sys_snpEff READ;");
        _insertVarGATKm(\%chrKeys);
        _dbexe("UNLOCK TABLES;");
        _print_time(" [".$NAME." variant] complete");
        $NUMSAMPLE++;
    }
} else {
    _error_method();  
    _exit();
}
_exit();

### Update variants ##############################
sub _insertVar {
    my $INDEL = "INDEL";
    my $chrKeysRef=shift(@_);
    my %chrKeys = %$chrKeysRef;

    open IN, '<', $VCF or die "file open error: $!";
    while (<IN>) {
        if ($_ !~ /^#/i){
            my @line=split(/\t/, $_);
            my $CHR=$line[0];
            if(!defined $chrKeys{$CHR}){
                next;
            }
            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]: This EFF-tag is defined.\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 $chrKeysRef=shift(@_);
    my %chrKeys = %$chrKeysRef;
    open IN, '<', $VCF or die "file open error: $!";
    while (<IN>) {
        if ($_ !~ /^#/i){
            my @line=split(/\t/, $_);
            my $CHR=$line[0];
            if(!defined $chrKeys{$CHR}){
                next;
            }
            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]);
            my $sgtvlen = @sgtv;
            $sgtv[0] =~ s/\|/\//g;
            if(defined $sgtv[4]){
                chomp($sgtv[4]);
            }else{
                chomp($sgtv[2]);
            }
            for (my $x=0; $x<$sgtvlen; $x++){
                if ( $sgt[$x] eq 'GT' ) {
                    $arr{'GT'}[0] = $sgtv[$x];
                }elsif( $sgt[$x] eq 'AD' ){
                    $arr{'AD'}[0] = $sgtv[$x];
                }elsif( $sgt[$x] eq 'DP' ){
                    $arr{'DP'}[0] = $sgtv[$x];
                }elsif( $sgt[$x] eq 'PL' ){
                    $arr{'PL'}[0] = $sgtv[$x];
                }
            }
            
            #############################################################
            # Varation
            #############################################################
            my $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";
                    if(defined $arr{$key}[0]){
                        $vQuery.=",'$arr{$key}[0]'";
                    }else{
                        $vQuery.=",'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);
                        if(defined $dp4Arr[$i]){
                            $vQuery.=",$dp4Arr[$i]";
                        }else{
                            $vQuery.=",'0'";
                        }
                    }
                }
            }
            _dbexe("INSERT into $TABLE($cQuery) values($vQuery);");
        }
    }
    close (IN);
}

sub _insertVarGATKm {
    my $INDEL = "Dels=";
    my $chrKeysRef=shift(@_);
    my %chrKeys = %$chrKeysRef;
  
  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(!defined $chrKeys{$CHR}){
          #if ( $CHR ne $chrs[$i] ) {
            #$seek = tell IN;
            #$seek = $seek - length($_);
            #last;
            next;
          }
          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]: This EFF-tag is defined.\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 _chrsH {
    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()) {
        $c{$val}=1;
    }
    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 table\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;
}

sub _check_var_table {
    my $res = $connect->selectrow_array("select * from $TABLE limit 1");
    return $res;
}

#### Disconnection and exit #######################################
sub _exit {
    $connect->disconnect or warn "Disconnection error: $DBI::errstr\n";
    exit;
}