#!/usr/bin/perl -w

#
# tasuke_variant_vcf.pl
# http://tasuke.dna.affrc.go.jp/
#
# Author: Jungsok Kim, Ryutaro Itoh
#
# Copyright (c) 2013 National Institute of Agrobiological Sciences   
#

use strict;
use warnings;
use DBI;
use Getopt::Long;
use File::Basename;
use Cwd;
use Scalar::Util qw(looks_like_number);
use FindBin qw($Bin);
use lib "$Bin";
use CommonLibTASUKE;
#use Data::Dumper;

my $NAME   = '';
my $CREATE = 0;
my $RESTORE= 0;
my $LIST   = 0;
my $REGGT00 = 0;
#my $SKIPGT00 = 0;
my $VCF    = '';
my $DB     = '';
my $HOST   = 'localhost';
my $USER   = '';
my $PASS   = '';
my $TYPE   = '';
my @NAMELIST;
my $NUMSAMPLE = 0;
my $RDBMS = '';
my $TABLESPACE = '';
my $SQLLDCOL = '';
my $FORCEFLG = 0;
my $APPENDFLG = 0;
#my $CHECKONLY = 0;
my $COMMONONLY = 0;

my $COMMIT_UNIT = 10000;

my $commonMysql = new CommonMysql();

################################################################
# Checking parameters and database connection
################################################################
GetOptions( 'n|name=s'      => \$NAME,
            'l|list'        => \$LIST,
            'r|restore'     => \$RESTORE,
            'z|reggt00'     => \$REGGT00,
            'f|file=s'      => \$VCF,
            'db|database=s' => \$DB,
            'h|host=s'      => \$HOST,
            'u|user=s'      => \$USER,
            't|type=s'      => \$TYPE,
            'b|backend=s'   => \$RDBMS,
            's|space=s'   => \$TABLESPACE,
            'p|password=s'  => \$PASS,
            'c|force'       => \$FORCEFLG,
            'a|append'      => \$APPENDFLG,
            'm|commononly'  => \$COMMONONLY
);

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

my $connect;
if($RDBMS eq "oracle"){
    if(!$TABLESPACE){
        _error_method();
        exit;
    }
    $connect = DBI->connect('DBI:Oracle:host='.$HOST.';SERVICE_NAME='.$DB.';',$USER,$PASS)
    or die "Can't connect to database: $DBI::errstr\n";
    if(!_check_exists_tablespace($TABLESPACE)){
        print "Tablespace: '$TABLESPACE' is not found.";
        _exit(1);
    }
}else{
    $connect= DBI->connect('DBI:mysql:database='.$DB.';host='.$HOST,$USER,$PASS,{AutoCommit => 0})
    or die "Can't connect to database: $DBI::errstr\n";
}

my $MODE_IS_MULTIVCF = $commonMysql->modeIsPlusMultiVcf($connect);
my $TABLE;

###########################################################
# Checking base table
###########################################################
if ( ! defined _check_exists_table("sys_accession") ) {
    print "\"sys_accession\" table does not exists.\n";
    print "Did you execute 'tasuke_init.pl' at the first?\n";
    _exit(1);
}
if($MODE_IS_MULTIVCF) {
	if($COMMONONLY) {
		if ( ! defined _check_exists_table("sys_variant") ) {
			print "\"sys_variant\" table does not exists.\n";
			print "Did you execute 'tasuke_init.pl' at the first?\n";
			_exit(1);
		} else {
			$TABLE = "sys_variant";
			if ( !$FORCEFLG && !$RESTORE && defined _check_var_table() ) {
				print "\"sys_variant\" data is already installed.\n";
				if($APPENDFLG) {
					print "Since it's in \"Append\" mode, data will be added to the existing table. This message will not be displayed in forced mode(\"-c\").\n";
					print "Do you want to continue? [y|n] ";
					confirmYN();
				} else {
					print "If you want to install again, \nplease run this tool again with \"-r\" option for deleting the variants.\n";
					_exit(1);
				}
			}
		}
		$NAME = get_dummy_acc();
		if(!$NAME) {
			print "Accession information is not registered.\n";
			print " please run \"tasuke_accession.pl\". \n";
			_exit(1);
		}
		if($FORCEFLG) {
			print "> Since force flag(-c) is attached, registration is forced.\n";
		} else {
			if($RESTORE) {
				print "> Are you sure deleting sys_variant(site-common variant information) table?[y|n] ";
			} else {
				print "> Are you sure registering sys_variant(site-common variant information) table?[y|n] ";
			}
			confirmYN();
		}
	}
	if($TYPE ne "gatkm") {
		print "Database \"$DB\" you specified is in multi-VCF mode (mvcf). Run in \"-t gatkm\".\n";
		$TYPE = "gatkm";
#		print "Database \"$DB\" you specified is in multi-VCF mode (mvcf). Please run with \"-t gatkm\".\n";
#		_exit(1);
	}
}

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

# for tasuke_variant_multivcf.pl. Wait for all parallel Threads to be generated successfully.
sleep 1;

###########################################################
# Restoring sys_variant table
###########################################################
if($RESTORE && $MODE_IS_MULTIVCF && $COMMONONLY) {
	_dbexe("TRUNCATE TABLE sys_variant");
	print "\"sys_variant\" table is restored.\n";
	_exit();
}

###########################################################
# Checking accession id
###########################################################
if ($RESTORE && $TYPE eq "gatkm") {
    $TYPE="";
}
$TABLE =_check_db();
my $CHKTABLE = $TABLE;
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(1);
} elsif ($TYPE eq "gatkm"){
    @NAMELIST = split(/,/,$NAME);
    for(my $i=0; $i<scalar(@NAMELIST); $i++){
        $NAME = $NAMELIST[$i];
        next if(!$NAME);
        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(1);
        }
    }
    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(1);
    }

} 
$TABLE .= "_variant";

###########################################################
# Restoring variant table
###########################################################
if ($RESTORE) {
#   if($MODE_IS_MULTIVCF && $COMMONONLY) {
#	_dbexe("TRUNCATE TABLE sys_variant");
#	print "\"sys_variant\" table is restored.\n";
#	_exit();
#    } else {
	_dbexe("TRUNCATE TABLE $TABLE");
	print "VARIANT $NAME table is restored.\n";
	_exit();

#    }
}

###########################################################
# Checking ref-seq already installed or not
###########################################################
if(scalar(@NAMELIST) == 0) {
  # Not gatkm
  if(defined _check_var_table()){
    if($APPENDFLG) {
      if(!$FORCEFLG) {
        print "Since it's in \"Append\" mode, data will be added to the existing table.\n>  [y|n] ";
        confirmYN();
      }
    } else {
      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(1);
    }
  }
} else {
  # Changed to skip registered tables.
  if(!$FORCEFLG) {
    foreach my $nm (@NAMELIST) {
      next if(!$nm);
      $NAME = $nm;
      $TABLE =_check_db();
      $TABLE .= "_variant";
      if(defined _check_var_table()){
        print "$nm: The variants are already installed.\n";
	if($APPENDFLG) {
		print "Since it's in \"Append\" mode, data will be added to the existing table.\n> [y|n] ";
		confirmYN();
	} else { 
		print "If you want to install again, \nplease run this tool again with \"-r\" option for deleting the variants.\n";
		_exit(1);
	}
      }
    }
  }
}

###########################################################
# Regisering EFF Level
###########################################################

my $EFF_HASH_REF = _reg_eff();

###########################################################
# Variants
###########################################################
my $fh = _getFileHandle();
#if($VCF =~ m/\.gz$/ || $VCF =~ m/\.bz2$/) {
#    print "File checking. Maybe you get a \"stdout: Broken pipe\" error, but that's OK.\n";
#}
if($TYPE eq "gatkm"){
    while(<$fh>) {
        chomp $_;
        if($_ =~ /^##/){
            next;
        }elsif ($_ =~ /^#CHROM/ && !$COMMONONLY){
            my $count=1;
            my @header = split(/\t/, $_);
            print "   +------------------------------------+\n";
            print "   | Accession       <- Sample NAME     |\n";
            print "   +------------------------------------+\n";
            my $samplecocunt = 0;
            foreach my $column (@header){
                if ($count >=10 && defined $NAMELIST[$count-10]){
                    my $x = $NAMELIST[$count-10];
                    printf("   \| %-15s <- %-15s \| \n",$x,$column);
                    $samplecocunt++;
                }
                $count++;
            }
            print "   +------------------------------------+\n\n";
            if($samplecocunt < scalar(@NAMELIST)) {
                printf "The number of IDs(-n) exceeds the number of VCF samples. %d/%d\n", scalar(@NAMELIST), $samplecocunt;
                _exit(1);
            }
            if($FORCEFLG) {
                print "> Since force flag(-c) is attached, registration is forced.\n";
            } else {
                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(1);
            } 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(1);
                }
                last;
            }
        }
    }
}elsif($TYPE eq "samtools"){
    while(<$fh>) {
        chomp $_;
        if($_ =~ /^#/){

            next;
        }elsif ($_ !~ /DP4/ ){
            print "This VCF file is probably GATK-type VCF file.\n";
            print "Please check the \"-t\" option.\n";
            _exit(1);
        }else{
            last;
        }
    }
}elsif($TYPE eq "gatk"){
    while(<$fh>) {
        chomp $_;
        if($_ =~ /^#/){
            next;
        }elsif ($_ =~ /DP4/ ){
            print "This VCF file is probably SAMtools-type VCF file.\n";
            print "Please check the \"-t\" option.\n";
            _exit(1);
        }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(1);
            }
            last;
        }
    }
}elsif($TYPE eq "freebayes"){
    while(<$fh>) {
        chomp $_;
        if($_ =~ /^#/){
            next;
        }elsif ($_ =~ /DP4/){
            print "This VCF file is probably SAMtools-type VCF file.\n";
            print "Please check the \"-t\" option.\n";
            _exit(1);
        }else{
            last;
        }
    }
}
close($fh);

###########################################################
# Creating variants table
###########################################################
my (@chrs) = _chrs();
my %chrKeys =_chrsH();
my $TEMPDIR = $VCF.".temp";
my $finalCode = 0;
if($TYPE eq "samtools"){
    _print_time("*[".$NAME." variant]    begin");
    if($RDBMS eq "oracle"){
        _mkTmpDir($TEMPDIR);
        $finalCode = _insertVar(\%chrKeys);
        _sqlloder();
    }else{
        _dbexe("LOCK TABLES $TABLE WRITE, sys_snpEff READ;");
        $finalCode = _insertVar(\%chrKeys);
        _dbexe("UNLOCK TABLES;");
    }
    _print_time(" [".$NAME." variant] complete");
} elsif($TYPE eq "gatk"){
    _print_time("*[".$NAME." variant]    begin");
    if($RDBMS eq "oracle"){
        _mkTmpDir($TEMPDIR);
        $finalCode = _insertVarGATK(\%chrKeys);
        _sqlloder();
    }else{
        _dbexe("LOCK TABLES $TABLE WRITE, sys_snpEff READ;");
        $finalCode = _insertVarGATK(\%chrKeys);
        _dbexe("UNLOCK TABLES;");
    }
    _print_time(" [".$NAME." variant] complete");
} elsif($TYPE eq "gatkm"){
    @NAMELIST = ($NAMELIST[0]) if($COMMONONLY);

    foreach my $i (@NAMELIST){
      if($i) {
	$NAME=$i;
	if($COMMONONLY) {
		_print_time("*[sys_variant] begin");
		$TABLE = 'sys_variant';
	} else {
		_print_time("*[".$NAME." variant] begin");
		$TABLE = _check_db();
		$TABLE .= "_variant";
	}

	my $execRegist = 0;
	if(checkAlreadyRegisted($TABLE)) {
		print "The variant is already installed.\n";
		if($APPENDFLG) {
			print "Since it's in \"Append\" mode, data will be added.\n";
			$execRegist = 1;
		} elsif($FORCEFLG) {
			print "Registration will be skipped. To update, restore(-r) the table first.\n";
		}
	} else {
		$execRegist = 1;
	}
	if($execRegist) {
		_dbexe("LOCK TABLES $TABLE WRITE;");
		if($MODE_IS_MULTIVCF) {
			$finalCode = _insertVarGATKm_m(\%chrKeys);
		} else {
			$finalCode = _insertVarGATKm_s(\%chrKeys);
		}
		_dbexe("UNLOCK TABLES;");
	}
	_print_time(" [".$NAME." variant] complete");
      }
      $NUMSAMPLE++;
    }
} elsif($TYPE eq "freebayes") {
    _print_time("*[".$NAME." variant]    begin");
    if($RDBMS eq "oracle"){
        _mkTmpDir($TEMPDIR);
        $finalCode = _insertVarFreebayes(\%chrKeys);
        _sqlloder();
    }else{
        _dbexe("LOCK TABLES $TABLE WRITE, sys_snpEff READ;");
        $finalCode = _insertVarFreebayes(\%chrKeys);
        _dbexe("UNLOCK TABLES;");
    }
    _print_time(" [".$NAME." variant] complete");
} else {
    _error_method();  
    _exit(1);
}
_exit($finalCode);

### Update variants ##############################
sub _insertVar {
    my $INDEL = "INDEL";
    my $chrKeysRef = shift(@_);
    my %chrKeys = %$chrKeysRef;
    my $stmt_count = 0;
    my $primary_key=0;
    my $temp_base_name = basename($VCF);
    my $fhOUT;
    my $fh = _getFileHandle();

    if($RDBMS eq "oracle"){
        open $fhOUT, '>', $TEMPDIR."/".$temp_base_name.".temp" or die "file open error: $!";
    }

    my $total = 0;
    while(<$fh>) {
        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
            if(!defined $line[8]){
                print STDERR "Format-field is not defined. Skip: $CHR:$POS\n";
                next;
            }
            setSampleByFormat(\%arr, $line[8], $line[9], {'GT'=>1, 'PL'=>1});

            next if($arr{'GT'}[0] eq './.');
            next if(($arr{'GT'}[0] eq '0/0' || $arr{'GT'}[0] eq '0|0') && !$REGGT00);
#            next if($SKIPGT00 && $arr{'GT'}[0] eq '0/0');

            #############################################################
            # 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;
            $primary_key++;
            if($RDBMS eq "oracle"){
                $cQuery="seq,chr,pos,variant,ref CHAR(1000),alt CHAR(1000),qual";
                $vQuery="$primary_key\t$CHR\t$POS\t$variant\t$REF\t$ALT\t$QUAL";
            }else{
                $cQuery="chr,pos,variant,ref,alt,qual";
                $vQuery="'$CHR',$POS,'$variant','$REF','$ALT',$QUAL";
            }
=pod
            if(exists $arr{"EFF"}){
                if(length(defined $arr{"EFF"}) > 4000){
                    print STDERR "Too many SNP-Effects are detected. Skip: $CHR:$POS\n";
                    next;
                }
            }
            if(exists $arr{"ANN"}){
                if(length(defined $arr{"ANN"}) > 4000){
                    print STDERR "Too many SNP-Effects are detected. Skip: $CHR:$POS\n";
                    next;
                }
            }
=cut
            #if(!defined $arr{"VDB"}){
            #    $arr{"VDB"}[0]=0;
            #}
            for my $key (sort keys %arr) {
		#print $key."\t".$arr{$key}[0]."\n";
                if($key eq "ANN" || $key eq "EFF") {
			if($arr{$key}[0]){
				$arr{$key}[0] =~ s/['"]/ /g;
			} else {
				delete $arr{$key};
			}
		}
		
                ## 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" ) {
                    next if(!_isNumber($arr{$key}[0]));
                    if($RDBMS eq "oracle"){
                        if($key ne "VDB"){
                            $cQuery.=",$key";
                            $vQuery.="\t$arr{$key}[0]";
                        }
                    }else{
                        $cQuery.=",$key";
                        $vQuery.=",$arr{$key}[0]";
                    }
                }
                ## for string
                if ($key eq "EFF" || $key eq "ANN" || $key eq "GT" || $key eq "PL" || $key eq "PV4") {
                    if($key eq "EFF" || $key eq "ANN") {
                        my $subsqlArray = _get_eff_sql($key, $arr{$key}[0]);
                        $cQuery .= $subsqlArray->[0];
                        $vQuery .= $subsqlArray->[1];
                    } else {
                        if($RDBMS eq "oracle"){
                            $cQuery.=",$key";
                            $vQuery.="\t$arr{$key}[0]";
                        }else{
                            $cQuery.=",$key";
                            $vQuery.=",'$arr{$key}[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);
                        if($RDBMS eq "oracle"){
                            $vQuery.="\t$dp4Arr[$i]";
                        }else{
                            $vQuery.=",$dp4Arr[$i]";
                        }
                    }
                }
            }
            if($RDBMS eq "oracle"){
                $SQLLDCOL = $cQuery;
                print $fhOUT $vQuery."\n";
            }else{
		my $sql = "INSERT into $TABLE($cQuery) values($vQuery)";
		my $sth = $connect->prepare($sql);
		my $stat = $sth->execute();
		$total++;
		$connect->commit() if($total % $COMMIT_UNIT == 0);
#               	_dbexe("INSERT into $TABLE($cQuery) values($vQuery)");
            }
        }
    }
    close($fh);
    if($RDBMS eq "oracle"){
        close($fhOUT);
    } else {
#	$connect->rollback();
	$connect->commit();
    }
    return 0;
}

# Update variants at pos (GATK)
sub _insertVarGATK {
    my $INDEL = "Dels=";
    my $chrKeysRef=shift(@_);
    my %chrKeys = %$chrKeysRef;
    my %EFF_HASH = %$EFF_HASH_REF;
    my $stmt_count = 0;
    my $primary_key=0;
    my $temp_base_name = basename($VCF);
    my $fhOUT;
    my $fh = _getFileHandle();

    if($RDBMS eq "oracle"){
        open $fhOUT, '>', $TEMPDIR."/".$temp_base_name.".temp" or die "file open error: $!";
    }

    my $total = 0;
    while(<$fh>) {
        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
            if(!defined $line[8]){
                print STDERR "Format-field is not defined. Skip: $CHR:$POS\n";
                next;
            }
            setSampleByFormat(\%arr, $line[8], $line[9], {'GT'=>1, 'AD'=>1, 'DP'=>1, 'PL'=>1});

            next if($arr{'GT'}[0] eq './.');
            next if(($arr{'GT'}[0] eq '0/0' || $arr{'GT'}[0] eq '0|0') && !$REGGT00);
#            next if($SKIPGT00 && $arr{'GT'}[0] eq '0/0');

            #############################################################
            # 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;
            $primary_key++;
            if($RDBMS eq "oracle"){
                $cQuery="seq,chr,pos,variant,ref CHAR(1000),alt CHAR(1000),qual";
                $vQuery="$primary_key\t$CHR\t$POS\t$variant\t$REF\t$ALT\t$QUAL";
            }else{
                $cQuery="chr,pos,variant,ref,alt,qual";
                $vQuery="'$CHR',$POS,'$variant','$REF','$ALT',$QUAL";
            }

            for my $key (sort keys %arr) {
                if($key eq "EFF"){$arr{$key}[0] =~ s/['"]/ /g;}
                if($key eq "ANN"){$arr{$key}[0] =~ s/['"]/ /g;}
                ## 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]);
                        if($RDBMS eq "oracle"){
                            $vQuery.="\t$tmparray[0]";
                        }else{
                            $vQuery.=",$tmparray[0]";
                        }
                        #$vQuery.=",$tmparray[0]";
                    } elsif($key eq "AC"){
                        $cQuery.=",AC1";
                        my @tmparray = split(",", $arr{$key}[0]);
                        if($RDBMS eq "oracle"){
                            $vQuery.="\t$tmparray[0]";
                        }else{
                            $vQuery.=",$tmparray[0]";
                        }
                        #$vQuery.=",$tmparray[0]";
                    } else {
                        next if(!_isNumber($arr{$key}[0]));
                        $cQuery.=",$key";
                        if($RDBMS eq "oracle"){
                            $vQuery.="\t$arr{$key}[0]";
                        }else{
                            $vQuery.=",$arr{$key}[0]";
                        }
                        #$vQuery.=",$arr{$key}[0]";
                    }
                }
                ## for string
                if ($key eq "EFF" || $key eq "ANN" || $key eq "GT" || $key eq "PL" || $key eq "PV4") {
                    if($key eq "EFF" || $key eq "ANN") {
                        my $subsqlArray = _get_eff_sql($key, $arr{$key}[0]);
                        $cQuery .= $subsqlArray->[0];
                        $vQuery .= $subsqlArray->[1];
                    } else {
                        if($RDBMS eq "oracle"){
                            $cQuery.=",$key";
                            $vQuery.="\t$arr{$key}[0]";
                        }else{
                            $cQuery.=",$key";
                            $vQuery.=",'$arr{$key}[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($RDBMS eq "oracle"){
                            $vQuery.="\t$dp4Arr[$i]";
                        }else{
                            $vQuery.=",$dp4Arr[$i]";
                        }
                        #$cQuery.=",DP".($i+1);
                        #if(defined $dp4Arr[$i]){
                        #    $vQuery.=",$dp4Arr[$i]";
                        #}else{
                        #    $vQuery.=",'0'";
                        #}
                    }
                }
            }
            if($RDBMS eq "oracle"){
                $SQLLDCOL = $cQuery;
                print $fhOUT $vQuery."\n";
            }else{
		my $sql = "INSERT into $TABLE($cQuery) values($vQuery)";
		my $sth = $connect->prepare($sql);
		my $stat = $sth->execute();
		$total++;
		$connect->commit() if($total % $COMMIT_UNIT == 0);
#                _dbexe("INSERT into $TABLE($cQuery) values($vQuery);");
            }
        }
    }
    close($fh);
#    close (IN);
    if($RDBMS eq "oracle"){
        close($fhOUT);
    } else {
#        $connect->rollback();
        $connect->commit();
    }

    return 0;
}

sub _insertVarGATKm_m {
	my ($chrKeysRef) = @_;
	my $code = 0;

	my $INDEL = "Dels=";

	my $mainunit = 100; 	# How many subunits to commit.
	my $subunit = 100;	# How many rows to insert in one SQL.
	my @valsMatrix;

	my $currentMainunit = 0;
	my $currentSubunit = 0;

	my $colsStr;
	my $sqlHeadTemplate = "";
	my $sqlValsPat = "";
	my @valsTemplate;
	if($COMMONONLY) {
		$colsStr = "chr,pos_vcf,REF,ALTS,VDB,MQ,FQ,SP,EFF,AF1,AC1,QUAL";
		$sqlHeadTemplate = "insert into  $TABLE ($colsStr) values ";
		$sqlValsPat = "('%s',%s,'%s','%s',%s,%s,%s,%s,'%s','%s','%s',%s)";
		@valsTemplate = ('','NULL','','','NULL','NULL','NULL','NULL','','','','NULL')
	} else {
		$mainunit = 10;
		$subunit = 1000;
		$colsStr = "chr,pos_vcf,pos,variant,ALT,DP,DP1,DP2,DP3,DP4,GT,GTROW,PL,GQ, EFF_GRP";
		$sqlHeadTemplate = "insert into  $TABLE ($colsStr) values ";
		$sqlValsPat = "('%s',%s,%s,'%s','%s',%s,%s,%s,%s,%s,'%s','%s','%s',%s,%s)";
		@valsTemplate = ('','NULL','NULL','','','NULL','NULL','NULL','NULL','NULL','','','','NULL','NULL');
	}
	my %col2Idx;	# To save memory, use arrays instead of hash to store data.
	my $idx = 0;
	foreach my $col (split /\s*,\s*/, $colsStr) {
		$col2Idx{$col} = $idx;
		$idx++;
	}

	$| = 1;	# AutoFlush:On

	my $fh = _getFileHandle();
	my $total = 0;
	while(<$fh>) {
		chomp;
		my $datarow = $_;
		next if($datarow !~ m/\S/ || $datarow =~ m/^#/);

		my @line = split(/\t/, $datarow);
		my $CHR = $line[0];
		next if(!(exists $chrKeysRef->{$CHR}));
		my $POS = int($line[1]);
		my $REF = $line[3];
		my $ALTS = $line[4];
		my $QUAL = int($line[5]);
		my @infoArr = split(/;/, $line[7]);
		my %valsHash;
		foreach my $info (@infoArr) {
			my @keyVal = split(/\=/, $info);
			$valsHash{$keyVal[0]}[0] = $keyVal[1] if($keyVal[0]);
		}
		my $FORMAT = $line[8];
		my $SAMPLEVAL = $line[(9+$NUMSAMPLE)];

		my $refLen = length($REF);

		my @vals = @valsTemplate;	# Deep copy
		$vals[$col2Idx{'chr'}] = $CHR;
		$vals[$col2Idx{'pos_vcf'}] = $POS;

		if($COMMONONLY) {
			$vals[$col2Idx{'REF'}] = $REF;
			$vals[$col2Idx{'ALTS'}] = $ALTS;

			for my $key (sort keys %valsHash) {
				## for numbers
				if ($key eq "VDB" || $key eq "MQ" || $key eq "FQ" || $key eq "SP") {
					next if(!_isNumber($valsHash{$key}[0]));
					$vals[$col2Idx{$key}] = $valsHash{$key}[0];
				}

				## for string
				if ($key eq "EFF" || $key eq "ANN" || $key eq "AF" || $key eq "AC") {
					if($key eq "EFF" || $key eq "ANN") {
						my $eff = $valsHash{$key}[0];
						$eff =~ s/['"]/ /g;
						$vals[$col2Idx{'EFF'}] = $eff;
					} elsif($key eq "AF") {
						$vals[$col2Idx{'AF1'}] = $valsHash{$key}[0];
					} elsif($key eq "AC") {
						$vals[$col2Idx{'AC1'}] = $valsHash{$key}[0];
					} else {
						$vals[$col2Idx{$key}] = $valsHash{$key}[0];
					}
				}
			}
			$vals[$col2Idx{'QUAL'}] = $QUAL;
		} else {
			setSampleByFormat(\%valsHash, $FORMAT, $SAMPLEVAL, {'GT'=>1, 'AD'=>1, 'DP'=>1, 'GQ'=>1, 'PL'=>1});

			if($REGGT00) {
				# Always register "./."
			} else {
				next if($valsHash{'GT'}[0] !~ m/[^0\|\/]/);
#				next if($valsHash{'GT'}[0] eq '0/0' || $valsHash{'GT'}[0] eq '0|0');
			}

			if($valsHash{'GT'}[0] =~ m/^\.\/\./ || $valsHash{'GT'}[0] =~ m/^\.\|\./) {
#			if($valsHash{'GT'}[0] eq './.' || $valsHash{'GT'}[0] eq '.|.') {
				$vals[$col2Idx{'GTROW'}] = $valsHash{'GT'}[0];
				$vals[$col2Idx{'GT'}] = './.';
#				$vals[$col2Idx{'ALT'}] = 'N';
				$vals[$col2Idx{'EFF_GRP'}] = 0;
				$vals[$col2Idx{'pos'}] = $POS;
				$vals[$col2Idx{'variant'}] = 's';
			} else {
				my $isCorrectAd = 1;
				if(!exists($valsHash{'AD'}) || $valsHash{'AD'}[0] eq '.') {
					$isCorrectAd = 0;
					printf("Incorrect depth information(AD): %s %d %s = %s\n", $CHR, $POS, $FORMAT, $SAMPLEVAL);
				}

				my @Predp4Arr;
					@Predp4Arr = split(",", $valsHash{'AD'}[0])  if($isCorrectAd);
				my $dp1 = $Predp4Arr[0] if($isCorrectAd);
				my $dp3 = 0;
				my @gtarray = split(/[\/\|]/, $valsHash{'GT'}[0]);

				#TODO Test!!!
				# If ploidy > 2, Top two GTs with the highest frequency of occurrence (smallest GT) are used.
				if(@gtarray > 2) {
					my %tmphash;
					@tmphash{@gtarray} = undef;	# Slice: ArrayVals => UniqKeys
					@gtarray = sort {$a <=> $b} keys(%tmphash);
					push(@gtarray, $gtarray[0]) if(@gtarray == 1);	# 1/1/1/1 => 1 =(push)=> 1/1
				}
				my $gtSingle;
				my @altArrAll = split /,/, $ALTS;
				my $altsStr = "";
				my $repAlt = "";
				if($gtarray[0] == 0 && $gtarray[1] == 0) {	# 0/0
					$gtSingle = '0/0';
					$altsStr = $REF;
					$repAlt = $altsStr;
					$dp3 = $Predp4Arr[0] if($isCorrectAd);
				}elsif($gtarray[0] != $gtarray[1] && $gtarray[0] != 0){	# for hetero 1/2 1/3 2/3
					$gtSingle = '1/2';
					$altsStr = $altArrAll[$gtarray[0] - 1] . ",". $altArrAll[$gtarray[1] - 1];
					$repAlt = $altArrAll[$gtarray[0] - 1];
					$dp3 = $Predp4Arr[$gtarray[0]] + $Predp4Arr[$gtarray[1]] if($isCorrectAd);
				}elsif($gtarray[0] != $gtarray[1] && $gtarray[0] == 0){	# for hetero 0/1 0/2 0/3
					$gtSingle = '0/1';
					$altsStr = $altArrAll[$gtarray[1] - 1];
					$repAlt = $altsStr;
					$dp3 = $Predp4Arr[$gtarray[1]] if($isCorrectAd);
				}else{	#for homo ./. 1/1 2/2 3/3
					$gtSingle = '1/1';
					$altsStr = $altArrAll[$gtarray[0] - 1];
					$repAlt = $altsStr;
					$dp3 = $Predp4Arr[$gtarray[1]] if($isCorrectAd);
				}

				# GTROW,GT
				$vals[$col2Idx{'GTROW'}] = $valsHash{'GT'}[0];
				$vals[$col2Idx{'GT'}] = $gtSingle;

				# ALT
				$vals[$col2Idx{'ALT'}] = $altsStr;

				# DP1,DP2,DP3,DP4
				if(!$isCorrectAd) {
					$dp1 = "NULL";
					$dp3 = "NULL";
				}
				$vals[$col2Idx{'DP1'}] = $dp1;
				$vals[$col2Idx{'DP2'}] = 0;
				$vals[$col2Idx{'DP3'}] = $dp3;
				$vals[$col2Idx{'DP4'}] = 0;

				# variant type
				my $variant = "s";
				my $pos2 = $POS;
				if ( $line[7] !~ /$INDEL/ && $refLen < length($repAlt) ) {
					$variant = "i";
				}
				if ( $line[7] !~ /$INDEL/ && ( $repAlt eq "." || $refLen > length($repAlt)) ) {
					$variant="d";
					$pos2 = $POS + 1;
				}
				$vals[$col2Idx{'variant'}] = $variant;
				$vals[$col2Idx{'pos'}] = $pos2;

				# EFF_GRP
				if($valsHash{'GT'}[0] ne '0/0' && $valsHash{'GT'}[0] ne '0|0' &&
					$valsHash{'GT'}[0] ne './.' && $valsHash{'GT'}[0] ne '.|.') {
					my $level = 0;
					if(exists($valsHash{'EFF'})) {
						$level = $commonMysql->getMaxEffGrpEff($valsHash{'EFF'}[0], $altsStr);
#						$level = _getMaxLebelEff($valsHash{'EFF'}[0], $altsStr);
					} elsif(exists($valsHash{'ANN'})) {
						$level = $commonMysql->getMaxEffGrpAnn($valsHash{'ANN'}[0], $altsStr);
#						$level = _getMaxLebelAnn($valsHash{'ANN'}[0], $altsStr);
					}
					$vals[$col2Idx{'EFF_GRP'}] = $level;
				}

				# others
				for my $key (sort keys %valsHash) {
					## for numbers
					if ($key eq "GQ" || $key eq "DP") {
						next if(!_isNumber($valsHash{$key}[0]));
						my $val = $valsHash{$key}[0];
						$val = 0 if($val eq '.');
						$vals[$col2Idx{$key}] = $val;
					}

					## for string
					if ($key eq "PL") {
						$vals[$col2Idx{$key}] = $valsHash{$key}[0];
					}
				}
			}
		}
		push @valsMatrix, \@vals;
		$total++;
		$currentSubunit++;
		if($currentSubunit >= $subunit) {
			my $sth = $connect->prepare(createSqlVarGATKm($sqlHeadTemplate, $sqlValsPat, \@valsMatrix));
			my $stat = $sth->execute();
			$code = 1 if(!(defined $stat));
			$sth->finish();
			$currentMainunit++;
			if($currentMainunit >= $mainunit) {
				printf("Progress: %s\t%d\t RegisteredVariants:%d\n", $CHR, $POS, $total);
				$connect->commit();
				$currentMainunit = 0;
			}
			$currentSubunit = 0;
			@valsMatrix = ();			
		}
	}
	close($fh);

	if($currentSubunit > 0) {
		my $sth = $connect->prepare(createSqlVarGATKm($sqlHeadTemplate, $sqlValsPat, \@valsMatrix));
		my $stat = $sth->execute();
		$code = 1 if(!(defined $stat));
		$sth->finish();
		$connect->commit();
	}

	$| = 0;	# AutoFlush:Off

	return $code;
}
sub createSqlVarGATKm {
	my ($sqlHeadTemplate, $sqlValsPat, $valsMatrix) = @_;
	my $sql = $sqlHeadTemplate;
	foreach my $valsRef (@{$valsMatrix}) {
		$sql .= sprintf($sqlValsPat, @{$valsRef}). ',';
	}
	$sql =~ s/,$//;
	return $sql;
}

sub _insertVarGATKm_s {
    my $code = 0;

    my $INDEL = "Dels=";
    my $chrKeysRef=shift(@_);
    my %chrKeys = %$chrKeysRef;
    my %EFF_HASH = %$EFF_HASH_REF;
    my $stmt_count = 0;
    my $primary_key=0;
    my $temp_base_name = basename($VCF);
    my $fhOUT;
    my $fh = _getFileHandle();

    if($RDBMS eq "oracle"){
        open $fhOUT, '>', $TEMPDIR."/".$temp_base_name.".temp" or die "file open error: $!";
    }

    $| = 1;     # AutoFlush:On
    my $total = 0;
    while(<$fh>) {
        if ($_ !~ /^#/i){
            my @line=split(/\t/, $_);
            my $CHR=$line[0];
            if(!defined $chrKeys{$CHR}){
                next;
            }
            chomp $_;
            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
            if(!defined $line[8]){
                print STDERR "Format-field is not defined. Skip: $CHR:$POS\n";
                next;
            }
            my @sgt=split(/:/, $line[8]);
            my @sgtv=split(/:/, $line[(9+$NUMSAMPLE)]);
            setSampleByFormat(\%arr, $line[8], $line[(9+$NUMSAMPLE)], {'GT'=>1, 'AD'=>1, 'DP'=>1, 'GQ'=>1, 'PL'=>1});

#            next if($arr{'GT'}[0] eq './.');
            next if(($arr{'GT'}[0] eq '0/0' || $arr{'GT'}[0] eq '0|0') && !$REGGT00);

	my $cQuery;
	my $vQuery;
	if($arr{'GT'}[0] eq './.') {
                $cQuery = "chr,pos,variant,ref,GT,EFF_GRP";
                $vQuery = "'$CHR',$POS,'s','$REF','./.',0";
	} else {
            my $isCorrectAd = 1;
            if(!exists($arr{'AD'}) || $arr{'AD'}[0] eq '.') {
                $isCorrectAd = 0;
                printf("Incorrect depth information(AD): %s %d %s = %s\n", $CHR, $POS, $line[8], $line[(9+$NUMSAMPLE)]);
#                next;
            }

            my $cALTQuery;
            my $vALTQuery;
            my @Predp4Arr;
                @Predp4Arr = split(",", $arr{'AD'}[0])  if($isCorrectAd);
            my @gtarray = split(/[\/\|]/, $arr{'GT'}[0]);
            my $dp1=$Predp4Arr[0] if($isCorrectAd);     #for DP4
            my $dp3=0;
            my $rawGT1=$gtarray[0];     #for snpeff
            my $rawGT2=$gtarray[1];
            if($gtarray[0] != $gtarray[1] && $gtarray[0] != 0 && $gtarray[1] != 0){ # for hetero 1/2 1/3 2/3
                # Prioritize the allele with the larger depth (place it on the left)
#                my ($mainGtIdx, $slaveGtIdx) = (0, 1);
#                ($mainGtIdx, $slaveGtIdx) = (1, 0) if($isCorrectAd && $Predp4Arr[$gtarray[0]] < $Predp4Arr[$gtarray[1]]);
#                $raw_ALT = $salt[$gtarray[$mainGtIdx]-1]. ",". $salt[$gtarray[$slaveGtIdx]-1];
#                $ALT = $salt[$gtarray[$mainGtIdx] - 1];
		my ($mainGtIdx, $slaveGtIdx) = ($gtarray[0], $gtarray[1]);
		($mainGtIdx, $slaveGtIdx) = ($gtarray[1], $gtarray[0]) if( $gtarray[1] < $gtarray[0]);
		$raw_ALT = $salt[$mainGtIdx-1]. ",". $salt[$slaveGtIdx-1];
		$ALT = $salt[$mainGtIdx-1];
		$dp3 = $Predp4Arr[$gtarray[0]] + $Predp4Arr[$gtarray[1]] if($isCorrectAd);
		$gtarray[0]=1;
		$gtarray[1]=2;
            }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]] if($isCorrectAd);
                $gtarray[0]=0;
                $gtarray[1]=1;
            }elsif($arr{'GT'}[0] eq '0/0' || $arr{'GT'}[0] eq '0|0') {# for 0/0
                $dp3 = $Predp4Arr[0] if($isCorrectAd);
                $ALT = $REF;
                $raw_ALT = $REF;
                $gtarray[0]=0;
                $gtarray[1]=0;
            }else{#for homo 1/1 2/2 3/3
                $dp3 = $Predp4Arr[$gtarray[1]] if($isCorrectAd);
                $ALT=$salt[$gtarray[1]-1];
                $raw_ALT=$salt[$gtarray[1]-1];
                $gtarray[0]=1;
                $gtarray[1]=1;
            }

            if(!$isCorrectAd) {
                $dp1 = "NULL";
                $dp3 = "NULL";
            }
            my @dp4Arr = ($dp1,"0",$dp3,"0");
            my $arraylength = @dp4Arr;
            for (my $i=0; $i<$arraylength; $i++){
                $cALTQuery.=",DP".($i+1);
                $vALTQuery.=",$dp4Arr[$i]";
            }
=head
            if($gtarray[0] > 1){
                $gtarray[0]=1;
            }
            if($gtarray[1] > 2){
                $gtarray[1]=2;
            }
=cut
            $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;
            $primary_key++;
            if($RDBMS eq "oracle"){
                $cQuery="seq,chr,pos,variant,ref CHAR(1000),alt CHAR(1000)";
                $vQuery="$primary_key\t$CHR\t$POS\t$variant\t$REF\t$ALT";
            }else{
                $cQuery="chr,pos,variant,ref,alt";
                $vQuery="'$CHR',$POS,'$variant','$REF','$ALT'";
            }
            my $qualm = $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]){
                            if($RDBMS eq "oracle"){
                                $vQuery.="\t1";
                            }else{
                                $vQuery.=",1";
                            }
                            #$vQuery.=",1";
                        }else{
                            if($RDBMS eq "oracle"){
                                $vQuery.="\t0.5";
                            }else{
                                $vQuery.=",0.5";
                            }
                            #$vQuery.=",0.5";
                        }
                    } elsif($key eq "AC"){
                        $cQuery.=",AC1";
                        if($gtarray[0] == $gtarray[1]){
                            if($RDBMS eq "oracle"){
                                $vQuery.="\t2";
                            }else{
                                $vQuery.=",2";
                            }
                            #$vQuery.=",2";
                        }else{
                            if($RDBMS eq "oracle"){
                                $vQuery.="\t1";
                            }else{
                                $vQuery.=",1";
                            }
                            #$vQuery.=",1";
                        }
                    } else {
                        next if(!_isNumber($arr{$key}[0]));
                        $cQuery.=",$key";
                        if($RDBMS eq "oracle"){
                            $vQuery.="\t$arr{$key}[0]";
                        }else{
                            my $val = $arr{$key}[0];
                            $val = 0 if($val eq '.');
                            $vQuery.=",$val";
#                            $vQuery.=",$arr{$key}[0]";

                            $qualm = $val if($key eq 'GQ');
                        }
                    }
                }
                ## for string
                if ($key eq "EFF" || $key eq "ANN" || $key eq "GT" || $key eq "PL" || $key eq "PV4") {
                    if($key eq "EFF" || $key eq "ANN") {
                        if($arr{'GT'}[0] eq '0/0' || $arr{'GT'}[0] eq '0|0') {
                            # In case of GT:0/0, SnpEff information is not registered.
                        } else {
                            my $subsqlArray = _get_eff_sql($key, $arr{$key}[0], $ALT);
                            $cQuery .= $subsqlArray->[0];
                            $vQuery .= $subsqlArray->[1];
                        }
                    } else {
                        if($RDBMS eq "oracle"){
                            $cQuery.=",$key";
                            $vQuery.="\t$arr{$key}[0]";
                        }else{
                            $cQuery.=",$key";
                            $vQuery.=",'$arr{$key}[0]'";
                        }
                    }
                }
            }
            if($RDBMS eq "oracle"){
                $cQuery .= ",QUAL";
                $vQuery .= "\t$qualm";
            }else{
                $cQuery .= ",QUAL";
                $vQuery .= ",$qualm";
            }

            $cQuery.=$cALTQuery;
            $vQuery.=$vALTQuery;
	}
            if($RDBMS eq "oracle"){
                $SQLLDCOL = $cQuery;
                print $fhOUT $vQuery."\n";
            }else{
                my $sql = "INSERT into $TABLE($cQuery) values($vQuery)";
                my $sth = $connect->prepare($sql);
                my $stat = $sth->execute();
                $code = 1 if(!(defined $stat));
                $total++;
                if($total % $COMMIT_UNIT == 0) {
                        printf("Progress: %s\t%d\t RegisteredVariants:%d\n", $CHR, $POS, $total);
                        $connect->commit();
                }
            }
        }
    }
    $| = 0;     # AutoFlush:Off
#    close (IN);
    close($fh);

    if($RDBMS eq "oracle"){
         close($fhOUT);
    } else {
#       $connect->rollback();
        $connect->commit();
    }

    return $code;
}

sub _insertVarFreebayes {
    my $chrKeysRef = shift(@_);
    my %chrKeys = %$chrKeysRef;
    my %EFF_HASH = %$EFF_HASH_REF;
    my $stmt_count = 0;
    my $primary_key=0;
    my $temp_base_name = basename($VCF);
    my %key2colMap = (
        'GL' => 'PL',
        'AF' => 'AF1',
        'AC' => 'AC1',
        'RO' => 'DP1',
        'AO' => 'DP3'
    );
    my $fhOUT;
    if($RDBMS eq "oracle"){
        open $fhOUT, '>', $TEMPDIR."/".$temp_base_name.".temp" or die "file open error: $!";
    }

    my $fh = _getFileHandle();
    my $total = 0;
    while(<$fh>) {
        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]);
            for (my $i=0; $i < @spltinfo; $i++) {
                my @info=split(/=/, $spltinfo[$i]);
                $arr{$info[0]}[0] = $info[1] if($info[0] ne "");
            }
            # GT:DP:RO:QR:AO:QA:GL
            if(!defined $line[8]){
                print STDERR "Format-field is not defined. Skip: $CHR:$POS\n";
                next;
            }
            setSampleByFormat(\%arr, $line[8], $line[9]);

            next if($arr{'GT'}[0] eq './.');
            next if(($arr{'GT'}[0] eq '0/0' || $arr{'GT'}[0] eq '0|0') && !$REGGT00);
#            next if($SKIPGT00 && $arr{'GT'}[0] eq '0/0');

            #############################################################
            # Varation (snp/complex/mnp/ins/del)
            #############################################################
            my $variant = 's';
            if($line[7] =~ m/TYPE=(\S+)/i) {
                my $vtype = $1;
                if($vtype eq 'ins') {
                    $variant = 'i';
                } elsif($vtype eq 'del') {
                    $variant="d";
                    $POS = $POS + 1;
                }
            }
            $ALT=$raw_ALT;
            #############################################################
            # Combining query
            #############################################################
            my $cQuery;
            my $vQuery;
            $primary_key++;
            if($RDBMS eq "oracle"){
                $cQuery="seq,chr,pos,variant,ref CHAR(1000),alt CHAR(1000),qual";
                $vQuery="$primary_key\t$CHR\t$POS\t$variant\t$REF\t$ALT\t$QUAL";
            }else{
                $cQuery="chr,pos,variant,ref,alt,qual";
                $vQuery="'$CHR',$POS,'$variant','$REF','$ALT',$QUAL";
            }

            for my $key (sort keys %arr) {
                my $col = $key;
                $col = $key2colMap{$key} if(exists($key2colMap{$key}));

                ## for numbers
                if ( $key eq "DP" || $key eq "AF" || $key eq "AC" || $key eq "RO" || $key eq "AO" || $key eq "GQ" ) {
                    next if(!_isNumber($arr{$key}[0]));
                    if($RDBMS eq "oracle"){
                        if($key ne "VDB"){
                            $cQuery.=",$col";
                            $vQuery.="\t$arr{$key}[0]";
                        }
                    }else{
                        $cQuery.=",$col";
                        $vQuery.=",$arr{$key}[0]";
                    }
                }

                ## for string
                if ($key eq "EFF" || $key eq "ANN" || $key eq "GT" || $key eq "GL" ) {
                    if($key eq "EFF" || $key eq "ANN") {
                        my $subsqlArray = _get_eff_sql($key, $arr{$key}[0]);
                        $cQuery .= $subsqlArray->[0];
                        $vQuery .= $subsqlArray->[1];
                    } else {
                        if($RDBMS eq "oracle"){
                            $cQuery.=",$col";
                            $vQuery.="\t$arr{$key}[0]";
                        }else{
                            $cQuery.=",$col";
                            $vQuery.=",'$arr{$key}[0]'";
                        }
                    }
                }
            }
            if(exists($arr{"RO"}) || exists($arr{"AO"})) {
                $cQuery.=",DP2,DP4";
                if($RDBMS eq "oracle"){
                    $vQuery.="\t0\t0";
                } else {
                    $vQuery.=",0,0";
                }
            }

            if($RDBMS eq "oracle"){
                $SQLLDCOL = $cQuery;
                print $fhOUT $vQuery."\n";
            }else{
		my $sql = "INSERT into $TABLE($cQuery) values($vQuery)";
		my $sth = $connect->prepare($sql);
		my $stat = $sth->execute();
		$total++;
		$connect->commit() if($total % $COMMIT_UNIT == 0);
#                print STDERR "$sql\n";
#                _dbexe($sql);
            }
        }
    }
    close($fh);

    if($RDBMS eq "oracle"){
	close($fhOUT);
    } else {
#	$connect->rollback();
	$connect->commit();
    }
    return 0;
}
sub setSampleByFormat {
    my($arrRef, $formatStr, $valsStr, $keysHash) = @_;

    my @sgt=split(/:/, $formatStr);
    my @sgtv=split(/:/, $valsStr);
    my $sgtLen = @sgt;
    for (my $i=0; $i < $sgtLen; $i++) {
	my $key = $sgt[$i];
        my $val = $sgtv[$i];
        next if(!$key || !$val);

        $val =~ s/^\s+//;
        $val =~ s/\s+$//;
        $val =~ s/\|/\//g if($key eq 'GT');
	if($key eq 'PL' && length($val) > 65534) {
		$val = substr($val, 0, 65534);
	}
        $arrRef->{$key}[0] = $val if($val ne "" && ( !$keysHash || ( ref($keysHash) eq 'HASH' && %{$keysHash} && exists($keysHash->{$key}) )));
    }
}
### SnpEff Impact parser from vcf###
=head
sub _getMaxLebelEff {
        my ($str, $altsStr) = @_;

	my %EFF_HASH = %$EFF_HASH_REF;

	my @effArr = split(",", $str);
	my $arraylength = @effArr;
	my $level = 0;
	for (my $i=0; $i<$arraylength; $i++){
		my @eff = split(/\+/, $effArr[$i]);
		my $efflength = @eff;
		for (my $a=0; $a<$efflength; $a++){
			if ($eff[$a] =~ /^(.+?)\((.+)\)/) {
				my $effname=$1;
				my $eff_body = $2;
				if($altsStr) {
					my @eff_body_array = split(/\|/, $eff_body);
					my $eff_allele = $eff_body_array[$#eff_body_array];
					next if(! _hitAlt($altsStr, $eff_allele));
				}
				if (exists $EFF_HASH{$effname} && $level < $EFF_HASH{$effname}) {
					$level = $EFF_HASH{$effname};
				}
			} elsif($efflength>1 and $a<$efflength) {
				if (exists $EFF_HASH{$eff[$a]}) {
					$level = $EFF_HASH{$eff[$a]};
				}
			}
		}
	}
	return $level;
}
sub _getMaxLebelAnn {
	my ($str, $altsStr) = @_;

	my %effgrp = ('MODIFIER' => 0, 'LOW' => 1, 'MODERATE' => 2, 'HIGH' => 3);

	my @effArr = split(",", $str);
	my $level = 0;
	my $arraylength = @effArr;
	for (my $i=0; $i<$arraylength; $i++){
		my @eff = split(/\|/, $effArr[$i]);
		if($altsStr) {
			next if(! _hitAlt($altsStr, $eff[0]));
		}
		my $eff_level = $eff[2];
		if (exists $effgrp{$eff_level} && $level < $effgrp{$eff_level}) {
			$level = $effgrp{$eff_level};
		}
		
	}
	return $level;
}
sub _hitAlt {
	my($altsStr, $eff_allele) = @_;

	my @alts = split /,/, $altsStr;		# if GT is "1/2", for example, ALT is "G,T".
	my $hit = 0;
	foreach my $alt (@alts) {
		if($alt eq $eff_allele) {
			$hit = 1;
			next;
		}
	}
	return $hit;
}
=cut
sub _get_eff_sql {
    my($key, $val, $altsStr) = @_;

    my $cQuery = "";
    my $vQuery = "";
    return [$cQuery, $vQuery] if(($key ne "EFF" && $key ne "ANN") || !$val);

    $val =~ s/['"]/ /g;

    if($RDBMS eq "oracle"){
        $cQuery.=",EFF CHAR(4000)";
        $vQuery.="\t$val";
    } else {
        $cQuery.=",EFF";
        $vQuery.=",'$val'";
    }

    my $level=0;
    if($key eq "EFF") {
         $level = $commonMysql->getMaxEffGrpEff($val, $altsStr);
#        $level = _getMaxLebelEff($val, $altsStr);
    } elsif($key eq "ANN") {
         $level = $commonMysql->getMaxEffGrpAnn($val, $altsStr);
#        $level = _getMaxLebelAnn($val, $altsStr);
    }
    $cQuery.=",EFF_GRP";
    if($RDBMS eq "oracle"){
        $vQuery.="\t$level";
    } else {
        $vQuery.=",$level";
    }

    return [$cQuery, $vQuery];
}


### 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";
}
### read input file ##########################################
sub _getFileHandle {
    my $fh;
    if ($VCF =~ m/\.tar\.gz$/) {
        open $fh, "tar zxf $VCF -O 2> /dev/null|" or die "file open error: $!";
    } elsif($VCF =~ m/\.tar\.bz2$/) {
        open $fh, "tar jxf $VCF -O 2> /dev/null|" or die "file open error: $!";
    } elsif($VCF =~ m/\.gz$/) {
        open $fh, "gzip -dc $VCF 2> /dev/null|" or die "file open error: $!";
    } elsif($VCF =~ m/\.bz2$/) {
        open $fh, "bzip2 -dc $VCF 2> /dev/null|" or die "file open error: $!";
    } else {
        open $fh, '<', $VCF or die "file open error: $!";
    }
    return $fh;
}

### Getting effect level from a database #########################
sub _reg_eff{
    my %hash;
    my $key;
    my $val; 
    my $query = "SELECT eff_name, eff_grp FROM sys_snpEff";
    my $query_handle = $connect->prepare($query);
    $query_handle->execute();
    $query_handle->bind_columns(undef, \$key, \$val);
    while($query_handle->fetch()) {
        $hash{$key} = $val;
    }
    return \%hash;
}

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

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( To register GT:0/0 variant: -z) \n\n";
    print "Append variant table\n";
    print "tasuke_variant_vcf.pl -a -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 "Restoring site-common table(sys_variant) for \"mvcf\" DB mode\n";
    print "tasuke_variant_vcf.pl -r -m -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;
    if($RDBMS eq "oracle"){
        $t = uc($t);
        $val = $connect->selectrow_array("select table_name from user_tables where tablespace_name = '$TABLESPACE' and table_name = '$t'");
    }else{
        $val = $connect->selectrow_array("show tables like '$t'");
    }
    return $val;
}

### Get existing accid(for dummy) ####################################
sub get_dummy_acc {
	my $accid = $connect->selectrow_array("select accession_id from sys_accession limit 1");	# Return top one-row only.
	return $accid;
}

### Checking tablespace ####################################
sub _check_exists_tablespace {
    my ($t) = @_;
    $t = uc($t);
    my $val = $connect->selectrow_array("select tablespace_name from user_tablespaces where tablespace_name = '$t'");
    return $val;
}

sub _check_var_table {
    my $res;
    if($RDBMS eq "oracle"){
        $res = $connect->selectrow_array("select * from $TABLE where  ROWNUM <= 1");
    }else{
        $res = $connect->selectrow_array("select * from $TABLE limit 1");
    }
    return $res;
}
sub checkAlreadyRegisted {
        my($table) = @_;
        my $sql = sprintf 'select * from %s limit 1', $table;
        my $sth = $connect->prepare($sql);
        $sth->execute();
        my $res = $sth->fetchrow_arrayref();
        return $res;
}


### disable indexes ###################################
sub _disable_indexes{
    _dbexe("alter index ".uc($CHKTABLE)."_VAR_IDX unusable");
}

### rebuild indexes ###################################
sub _rebuild_indexes{
    _dbexe("alter index ".uc($CHKTABLE)."_VAR_IDX rebuild");
}

### Inserting for oracle ################################################
sub _sqlloder{
    my $crtdir = Cwd::getcwd();
    my @chrs;
    my $temp_base_name = basename($VCF);
    my $control_file = $TEMPDIR."/".$temp_base_name.".ctl";
    my $temp_vcf = $TEMPDIR."/".$temp_base_name.".temp";
    my $logfile = $TEMPDIR."/log.log";
    my $badfile = $TEMPDIR."/bad.bad";
    my $num_chrs = @chrs;
    my $ctlstr = "";
    $ctlstr .= "OPTIONS( DIRECT = TRUE, MULTITHREADING = TRUE) ";
    $ctlstr .= "LOAD DATA INFILE '$temp_vcf' BADFILE '$TEMPDIR\/bad' DISCARDFILE '$TEMPDIR\/disc' ";
    $ctlstr .= "APPEND INTO TABLE $TABLE FIELDS TERMINATED BY X'09' (";
    $ctlstr .= $SQLLDCOL;
    $ctlstr .= ")";
    
    open OUTCTL, '>', $control_file or die "file open error: $!";
    print OUTCTL $ctlstr;
    close (OUTCTL);
    my $cmd = "sqlldr userid=\"$USER/$PASS\@$DB\" control=\'$control_file\' log='$TEMPDIR\/log' errors=0";
    push(@chrs,$logfile);
    push(@chrs,$badfile);
    push(@chrs,$control_file);
    push(@chrs,$temp_vcf);
    if (system($cmd)){
        print  "Error: Loading the reference sequence is failed.\n";
        _exit(1);
    }
    clean_dir($crtdir, \@chrs, $crtdir);
    rmdir($TEMPDIR);
}

### temp directory ################################################
sub _mkTmpDir{
    my $OUTDIR = shift;
    if(!-e "$OUTDIR"){
        mkdir("$OUTDIR") or die "$!: $OUTDIR";
    } else{
        print "$OUTDIR already exists.\n";
        exit(0);
    }
}

### Removing temp files ###############################
sub clean_dir{
    my $work_dir = shift;
    my $chr_sc = shift;
    my $CD = shift;
    my @files= @$chr_sc;
    chdir("$work_dir") or die "$! : $work_dir";
    foreach my $file(@files){    
        if(-e $file ){
            unlink $file;
        }
    }
    chdir($CD) or die "$! : $CD";
}

#### Disconnection and exit #######################################
sub _exit {
    my ($arg) = @_;
    my $code = 0;
    if(defined $arg){$code = $arg;}
    $connect->disconnect or warn "Disconnection error: $DBI::errstr\n";
    exit($code);
}

sub _isNumber {
	my($str) = @_;
	if($str =~ m/[^\+\-\d\.e]/i) {
		return 0;
	} else {
		return 1;
	}
}
sub confirmYN {
                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] ";
                    }
                }
}
