#!/usr/bin/env perl
################################################################################
#677968693279877869823280736982826932696779678132535748555051505432666947666976#
#some modules required (if not installed: cpan install <module>)
use Modern::Perl '2011'; # replace the usual warnings / strict
use Getopt::Euclid;# used to document perl code
use File::Copy 'copy'; # used to copy the code dedicated MS-Excel Template
use Cwd; # used to catch code folder (working dir)
use Win32::OLE;# used to populate MS-Excel Workbooks
use Win32::GUI;# used to create Explorer Windows; will fail end of install as designed for 32 bit
#next is required to upgrade for 64 bit windows (from command line)
#ppm upgrade -install Win32::GUI
#no warnings 'File::Find';
################################################################################
#677968693279877869823280736982826932696779678132535748555051505432666947666976#
my $code = $0;#Perl code
while($code  =~ s/\//\\/){}
while($code  =~ s/\'//) {}
while($code  =~ s/\"//) {}
my $codebasename;#used to identifiy the MS-Excel template to be used
if ($code =~ m/([^\\]+)$/) {
	$codebasename = $1;
	if ($codebasename =~ m/(SamplesTracker.*_v\d+i\d+)\.pl$/i) {
		$codebasename = $1;}
	else {
		die "\n\n1 Unexpected code name $code (expected: SamplesTracker..._vxiy.pl with x and y being version and issue numbers)\n\n";}}
else {
	die "\n\n2 Unexpected code name $code (expected: SamplesTracker..._vxiy.pl with x and y being version and issue numbers)\n\n";}
my $codedir = cwd($0);# code location; that's where everything should be located
my $runtime = getdatetime();#current date and time used also for name of MS-Excel output file
print "\n$runtime Welcome to $code.\n\nIf you do not know how to execute this perl code just type in\n";
print "from Command Line Windows in code folder \"perl <code> --man\"\nAnd to stop running just have Ctrl-C at any time\n";
my $maxsnps = 48;#maximum number of SNPs evaluated (limit in MS-Excel output file) in the KASP file
my %cellhash = loadcells();#for referencing cells in MS-Excel
my $protectpassword = "chuulggenetics";#to lock/protect MS-Excel output
################################################################################
#677968693279877869823280736982826932696779678132535748555051505432666947666976#
#selecting the output folder (should exist)
my $outputdir;
OUTPUTDIR:
while (1 > 0) {
	#creating a Explorer Window to select for the MS excel Template
	$outputdir = Win32::GUI::BrowseForFolder(
        -folderonly => 1,
        -title => "Select an existing Output folder",);
    #next 3 lines to match windows OS reqs = slash to backslash and removing simple/double quotes
    while($outputdir  =~ s/\//\\/){}
	while($outputdir  =~ s/\'//) {}
	while($outputdir  =~ s/\"//) {}
    if ((-e $outputdir) && (-d $outputdir)) {# exists and is a folder so out of the loop
		last OUTPUTDIR;}
	else {
		print "\nCannot find selected Output folder ($outputdir).\nHave Ctrl-C to quit, anything else to try again.\n";
		my $answer = <STDIN>;}}
print "\nOutput folder selected as $outputdir\n";
################################################################################
#677968693279877869823280736982826932696779678132535748555051505432666947666976#
#selecting the MS-Excel Template
my $summarytemplate;
EXCELFILE:
while (1 > 0) {
	#creating a Explorer Window to select for the MS excel Template
	$summarytemplate = Win32::GUI::BrowseForFolder(
        -includefiles => 1,
        -title => "Select the MS-Excel Template (file name matching perl code name)",);
    #next 3 lines to match windows OS reqs = slash to backslash and removing simple/double quotes
    while($summarytemplate  =~ s/\//\\/){}
	while($summarytemplate  =~ s/\'//) {}
	while($summarytemplate  =~ s/\"//) {}
    if (-e $summarytemplate) {# exists
		if ($summarytemplate =~ m/\\?$codebasename.xlsx?$/i) {# expected filename format
			last EXCELFILE;}
		else {
			print "\nThe MS-Excel Template selected $summarytemplate is not named as expected (should be as perl code = $codebasename).\nHave Ctrl-C to quit, anything else to try again.\n";
			my $answer = <STDIN>;}}
	else {
		print "\nCannot find required summary template $summarytemplate.\nHave Ctrl-C to quit, anything else to try again.\n";
		my $answer = <STDIN>;}}
print "\nMS-Excel template identified as $summarytemplate\n";
################################################################################
#677968693279877869823280736982826932696779678132535748555051505432666947666976#
#selecting a user file (list of users; csv tab delimited with required fields)
my $userfilename;#user list file name
my @userreqfields;#required fields (from header)
my @userarr;
my $maxusers = 1000;
my $pointeruserarr = 0;
$userreqfields[1][1] = "Name,Surname,Title";
$userreqfields[2][1] = "UserID";
$userreqfields[3][1] = "Entity,Unit";
$userreqfields[4][1] = "Function";
USERFILE:
while (1 > 0) {
	CHECKUSERFILE:
	#creating a Explorer Window to select for the User List file
	$userfilename = Win32::GUI::BrowseForFolder(
        -includefiles => 1,
        -title => "Select the User List (file named as <UserList_xxxYYYYMMDD.txt> where xxx is info string)",);
    #next 3 lines to match windows OS reqs = slash to backslash and removing simple/double quotes
    while($userfilename  =~ s/\//\\/){}
	while($userfilename  =~ s/\'//) {}
	while($userfilename  =~ s/\"//) {}
    if (-e $userfilename) {# exists
		if ($userfilename =~ m/\\UserList_.*\d{8}\.txt$/i) {# expected filename format
			if (open(FILE,"<$userfilename")) {
				my $header = <FILE>;#first line expected to be header with fields names 
				chomp $header;#removing carriage return / linefeed
				$header = $header . "\t";#adding tab to catch last field name
				while($header  =~ s/\'//) {}# removing simple/double quotes
				while($header  =~ s/\"//) {}
				my @linearr;
				while ($header =~ m/([^\t]+)\t/igc) {#fetch "en passant" all fields names
					$linearr[++$linearr[0]] = $1;}
				my $kf1 = 1;
				while (defined($userreqfields[$kf1][1])) {#for all required fields
					my $kf2 = 1;
					FETCHUSERREQFIELD:
					while ($kf2 <= $linearr[0]) {#for all header fields
						if ($linearr[$kf2] =~ m/^$userreqfields[$kf1][1]$/i) {
							$userreqfields[$kf1][2] = $kf2;
							last FETCHUSERREQFIELD;}
						++$kf2;}
					if (not defined($userreqfields[$kf1][2])) {#so field is missing
						close FILE;
						print "\nThe User File selected misses the req field: $userreqfields[$kf1][1].\nHave Ctrl-C to quit, anything else to try again.\n";
						my $answer = <STDIN>;
						goto CHECKUSERFILE;}
					++$kf1;}
				#so if here everything is OK so starting to collect entries
				$pointeruserarr =0;
				undef @userarr;
				while (defined(my $linein = <FILE>)) {
					undef @linearr;
					chomp $linein;
					$linein = $linein . "\t";
					while ($linein =~ m/([^\t]*)\t/igc) {#fetch "en passant" all fields values
						$linearr[++$linearr[0]] = $1;}
					my $username = $linearr[$userreqfields[1][2]];
					my $userid = $linearr[$userreqfields[2][2]];
					my $userfunction = $linearr[$userreqfields[4][2]];
					my $userservice = $linearr[$userreqfields[3][2]];
					$userarr[$pointeruserarr] = "$username\t$userid\t$userservice\t$userfunction\t";
					while ($username =~ s/\s//) {}#to avoid counting blanks
					if (length($username) < 6) {#minimum 6 characters for userID
						close FILE;
						print "\n\nBad entry (user Name) in UserList file $linein.\nHave Ctrl-C to quit, anything else to try again.\n";
						my $answer = <STDIN>;
						goto CHECKUSERFILE;}
					while ($userid =~ s/\s//) {}#to avoid counting blanks
					if (length($userid) < 6) {#minimum 6 characters for userID
						close FILE;
						print "\n\nBad entry (user ID) in UserList file $linein.\nHave Ctrl-C to quit, anything else to try again.\n";
						my $answer = <STDIN>;
						goto CHECKUSERFILE;}
					while ($userfunction =~ s/\s//) {}
					if (length($userfunction) < 3) {
						close FILE;
						print "\n\nBad entry (user Function) in UserList file $linein.\nHave Ctrl-C to quit, anything else to try again.\n";
						my $answer = <STDIN>;
						goto CHECKUSERFILE;}
					while ($userservice =~ s/\s//) {}
					if (length($userservice) < 3) {
						close FILE;
						print "\n\nBad entry (user Entity-Unit) in UserList file $linein.\nHave Ctrl-C to quit, anything else to try again.\n";
						my $answer = <STDIN>;
						goto CHECKUSERFILE;}
					++$pointeruserarr;}
					close FILE;
					if ($pointeruserarr == 0) {
						print "\n\nUnable to identify entries from user list file  $userfilename.\nHave Ctrl-C to quit, anything else to try again.\n";
						my $answer = <STDIN>;
						goto CHECKUSERFILE;}
					if ($pointeruserarr >= $maxusers) {
						print "\n\nFound too many entries (system alloas max $maxusers).Have Ctrl-C to quit, anything else to try again.\n";
						my $answer = <STDIN>;
						goto CHECKUSERFILE;}
					last USERFILE;}
			else {
				print "\n\nUnable to open user list file $userfilename for reading.\nHave Ctrl-C to quit, anything else to try again.\n";
				my $answer = <STDIN>;
				last CHECKUSERFILE;}}
		else {
			print "\n\nBad name format for user list file $userfilename.\nHave Ctrl-C to quit, anything else to try again.\n";
			my $answer = <STDIN>;
			last CHECKUSERFILE;}}
	else {
		print "\n\nCannot find user list file $userfilename.\nHave Ctrl-C to quit, anything else to try again.\n";
		my $answer = <STDIN>;
		last CHECKUSERFILE;}}	
#ordering by Username, UserID for easy finding in MS-Excel output file
my $field1 = 1;
my $field2 = 2;
my @rankarr = map {$_->[0]} 
	sort {
		my @a_fields = @$a[1..$#$a];
		my @b_fields = @$b[1..$#$b];
		$a_fields[$field1-1] cmp $b_fields[$field1-1]
			||
		$a_fields[$field2-1] cmp $b_fields[$field2-1]}
map {[$_, split /\t/]} @userarr;
undef @userarr;
@userarr = @rankarr;
undef @rankarr;
print"\nIdentified $pointeruserarr users from user list $userfilename\n";
################################################################################
#677968693279877869823280736982826932696779678132535748555051505432666947666976#
#selecting a SNPs table (named as SNPsTable_...YYYYMMDD.txt)
#selecting a user file (list of users; csv tab delimited with required fields)
my $snpfilename;#user list file name
my @snpreqfields;#required fields (from header)
my @snparr;#
my %allelehash;
my %afhash;
my %genehash;
my %pointergenehash;							
$snpreqfields[1][1] = "GeneName";
$snpreqfields[2][1] = "GeneRef";
$snpreqfields[3][1] = "Xkasp";
$snpreqfields[4][1] = "Ykasp";
$snpreqfields[5][1] = "Xngs";
$snpreqfields[6][1] = "Yngs";
$snpreqfields[7][1] = "XAFkasp";
$snpreqfields[8][1] = "YAFkasp";
SNPFILE:
while (1 > 0) {
	CHECKSNPFILE:
	#creating a Explorer Window to select for the SMPs Table
	$snpfilename = Win32::GUI::BrowseForFolder(
        -includefiles => 1,
        -title => "Select the SNPs Table (file named as <SNPsTable_xxxYYYYMMDD.txt> where xxx is info string)",);
    #next 3 lines to match windows OS reqs = slash to backslash and removing simple/double quotes
    while($snpfilename  =~ s/\//\\/){}
	while($snpfilename  =~ s/\'//) {}
	while($snpfilename  =~ s/\"//) {}
    if (-e $snpfilename) {# exists
		if ($snpfilename =~ m/\\SNPsTable_.*\d{8}\.txt$/i) {# expected filename format
			if (open(FILE,"<$snpfilename")) {
				my $header = <FILE>;#first line expected to be header with fields names 
				chomp $header;#removing carriage return / linefeed
				$header = $header . "\t";#adding tab to catch last field name
				while($header  =~ s/\'//) {}# removing simple/double quotes
				while($header  =~ s/\"//) {}
				my @linearr;
				while ($header =~ m/([^\t]+)\t/igc) {#fetch "en passant" all fields names
					$linearr[++$linearr[0]] = $1;}
				my $kf1 = 1;
				while (defined($snpreqfields[$kf1][1])) {#for all required fields
					my $kf2 = 1;
					FETCHSNPREQFIELD:
					while ($kf2 <= $linearr[0]) {#for all header fields
						if ($linearr[$kf2] =~ m/^$snpreqfields[$kf1][1]$/i) {
							$snpreqfields[$kf1][2] = $kf2;
							last FETCHSNPREQFIELD;}
						++$kf2;}
					if (not defined($snpreqfields[$kf1][2])) {#so field is missing
						close FILE;
						print "\nThe SNPs Table selected misses the req field: $snpreqfields[$kf1][1].\nHave Ctrl-C to quit, anything else to try again.\n";
						my $answer = <STDIN>;
						goto CHECKSNPFILE;}
					++$kf1;}
				#so if here everything is OK so starting to collect entries
				undef @snparr;
				undef %allelehash;
				undef %afhash;
				undef %genehash;
				undef %pointergenehash;
				while (defined(my $linein = <FILE>)) {#reading entries
					undef @linearr;
					chomp $linein;
					$linein = uc $linein . "\t";
					while ($linein =~ m/([^\t]*)\t/igc) {#fetch "en passant" all fields values
						$linearr[++$linearr[0]] = $1;}
					my $genename = $linearr[$snpreqfields[1][2]];
					my $generef = $linearr[$snpreqfields[2][2]];
					my $nucxkasp = $linearr[$snpreqfields[3][2]];
					my $nucykasp = $linearr[$snpreqfields[4][2]];
					my $nucxngs = $linearr[$snpreqfields[5][2]];
					my $nucyngs = $linearr[$snpreqfields[6][2]];
					my $majoraf = $linearr[$snpreqfields[7][2]];
					my $minoraf = $linearr[$snpreqfields[8][2]];
					#for english version switch decimal separator
					while ($majoraf =~ s/,/\./) {}
					while ($minoraf =~ s/,/\./) {}
					#recording gene name and gene reference (and checking they do not exist already)
					my $k2 = 1;
					while (defined($snparr[$k2][2])) {
						if ($generef eq $snparr[$k2][2]) {#so duplicate in genename (may be modified later on if KASP file comme with GeneRef
							close FILE;
							print "\nDuplicated entry (gene) in SNP table $snpfilename for  $snparr[$k2][1] $snparr[$k2][2]\nHave Ctrl-C to quit, anything else to try again.\n";
							my $answer = <STDIN>;
							goto CHECKSNPFILE;}
						++$k2;}
					$pointergenehash{$genename} = $k2;
					$snparr[++$snparr[0][0]][1] = $genename;
					$snparr[$snparr[0][0]][2] = $generef;
					$snparr[$snparr[0][0]][6] = $nucxkasp;
					$snparr[$snparr[0][0]][7] = $nucykasp;
					$snparr[$snparr[0][0]][8] = $nucxngs;
					$snparr[$snparr[0][0]][9] = $nucyngs;
					#loading a hash to catch call KASP (and NGS) and another hash to catch frequencies plus a hash genename genref
					$genehash{$genename} = $generef;
					$genehash{$generef} = $genename;
					my $hashval = $genename . '_XX';
					my $hashvar = $nucxkasp . "\/$nucxkasp";
					$allelehash{$hashval} = $hashvar;
					$afhash{$hashval} = $majoraf ** 2;
					$snparr[$snparr[0][0]][10] = $majoraf ** 2;
					$hashval = $genename . '_XY';
					$hashvar = $nucxkasp . "\/$nucykasp";
					$allelehash{$hashval} = $hashvar;
					$afhash{$hashval} = $majoraf * $minoraf * 2;
					$snparr[$snparr[0][0]][12] = $majoraf * $minoraf * 2;
					$hashval = $genename . '_YY';
					$hashvar = $nucykasp . "\/$nucykasp";
					$allelehash{$hashval} = $hashvar;
					$afhash{$hashval} = $minoraf ** 2;
					$snparr[$snparr[0][0]][11] = $minoraf ** 2;
					#new version. position 3 of array = call when ngs call is matching HOMO, 4 when matching HET and 5 when absent 
					#but there are 2 versions as KASP fluo can consider an inverted variant prevalence
					if (($nucxkasp eq $nucxngs) && ($nucykasp eq $nucyngs)) {# so same way to consider the variants
						$snparr[$snparr[0][0]][3] = 'YY';
						$snparr[$snparr[0][0]][4] = 'XY';
						$snparr[$snparr[0][0]][5] = 'XX';}
					elsif (($nucxkasp eq $nucyngs) && ($nucykasp eq $nucxngs)) {# inverted way to consider variants
						$snparr[$snparr[0][0]][3] = 'XX';
						$snparr[$snparr[0][0]][4] = 'XY';
						$snparr[$snparr[0][0]][5] = 'YY';}
					else {#should not happen in the table
						close FILE;
						print "\nUnmatched correlation in SNP table entry: $linein.\nHave Ctrl-C to quit, anything else to try again.\n";
						my $answer = <STDIN>;
						goto CHECKSNPFILE;}}
					close FILE;
					if (not defined($snparr[0][0])) {
						print "\nUnable to capture any valid entry from SNP table $snpfilename.\nHave Ctrl-C to quit, anything else to try again.\n";
						my $answer = <STDIN>;
						goto CHECKSNPFILE;}
					last SNPFILE;}
			else {
				print "\n\nUnable to open SNPs Table $snpfilename for reading.\nHave Ctrl-C to quit, anything else to try again.\n";
				my $answer = <STDIN>;
				goto CHECKSNPFILE;}}
		else {
			print "\n\nBad name format for SNPs table $snpfilename.\nHave Ctrl-C to quit, anything else to try again.\n";
			my $answer = <STDIN>;
			goto CHECKSNPFILE;}}
	else {
		print "\n\nCannot find SNPs Table $snpfilename.\nHave Ctrl-C to quit, anything else to try again.\n";
		my $answer = <STDIN>;
		goto CHECKSNPFILE;}}		
print "\nFound a unique valid SNP table $snpfilename and loaded $snparr[0][0] unique SNPs\n";
################################################################################
#677968693279877869823280736982826932696779678132535748555051505432666947666976#
#OK let's go for the KASP file (unique!) Attention long section as has to manage blanc values
#required fields from header
my $kaspfilename;
my @kasparr;
my $pointerkasparr = 0;
my @tempkasparr;
my @blancval;#array to store blanc values
my @reqkaspfield;
my @kaspglimsarr;
$reqkaspfield[1][1] = 'Sample Name';
$reqkaspfield[2][1] = 'X-Fluor';
$reqkaspfield[3][1] = 'Y-Fluor';
$reqkaspfield[4][1] = 'ManualCall';
$reqkaspfield[5][1] = 'Call';
$reqkaspfield[6][1] = 'Score';
KASPFILE:
while (1 > 0) {
	CHECKKASPFILE:
	#initialization
	undef @kaspglimsarr;
	undef $kaspfilename;
	undef @kasparr;
	$pointerkasparr = 0;
	undef @tempkasparr;
	undef @blancval;#array to store blanc values
	#creating a Explorer Window to select for the KASP File (only one)
	$kaspfilename = Win32::GUI::BrowseForFolder(
        -includefiles => 1,
        -title => "Select the KASP input file ( named as <KASPxxx.txt> where xxx is info string)",);
    #next 3 lines to match windows OS reqs = slash to backslash and removing simple/double quotes
    while($kaspfilename  =~ s/\//\\/){}
	while($kaspfilename  =~ s/\'//) {}
	while($kaspfilename  =~ s/\"//) {}	
    if (-e $kaspfilename) {# exists
		if ($kaspfilename =~ m/\\KASP.*\.txt$/i) {# expected filename format
			if (open(FILE,"<$kaspfilename")) {
				my $header = <FILE>;#first line expected to be header with fields names 
				chomp $header;#removing carriage return / linefeed
				$header = $header . "\t";#adding tab to catch last field name
				while($header  =~ s/\'//) {}# removing simple/double quotes
				while($header  =~ s/\"//) {}
				my @linearr;
				while ($header =~ m/([^\t]+)\t/igc) {#fetch "en passant" all fields names
					$linearr[++$linearr[0]] = $1;}
				my $kf1 = 1;
				while (defined($reqkaspfield[$kf1][1])) {#for all required fields
					my $kf2 = 1;
					FETCHKASPREQFIELD:
					while ($kf2 <= $linearr[0]) {#for all header fields
						if ($linearr[$kf2] =~ m/^$reqkaspfield[$kf1][1]$/i) {
							$reqkaspfield[$kf1][2] = $kf2;
							last FETCHKASPREQFIELD;}
						++$kf2;}
					if (not defined($reqkaspfield[$kf1][2])) {#so field is missing
						close FILE;
						print "\nThe KASP file selected misses the req field: $reqkaspfield[$kf1][1].\nHave Ctrl-C to quit, anything else to try again.\n";
						my $answer = <STDIN>;
						goto CHECKKASPFILE;}
					++$kf1;}
				#so if here everything is OK so starting to collect entries
				undef @kasparr;
				$pointerkasparr = 0;
				undef @tempkasparr;
				undef @tempkasparr;#array to store values of this KASP file prior evaluating blancs
				undef @blancval;#array to store blanc values
				while (defined(my $linein = <FILE>)) {#reading entries
					chomp $linein;#remove linefeed carriage return
					while ($linein =~ s/\"//) {}#removal of putative text delimiters
					$linein = $linein . "\t";#adding tab for regexp matching
					my @linearr;#array to store values
					while ($linein =~ m/([^\t]*)\t/igc) {#fetching on the fly all field values
						$linearr[++$linearr[0]] = $1;}
					#now focusing on required ones and first sample name
					my $glimsplusgene = uc $linearr[$reqkaspfield[1][2]];#still uc to avoid typos glims plus gene
					if ($glimsplusgene =~ m/^(\d*).?(\d{6}).(\d{4}).?(\d*)[\s\-\_]+(.+)$/) {#correct entry format extrapollated for full glims
						my $genein = uc $5;
						my $fullglims = "$1\-$2\-$3\-$4";
						my $baseglims = "$2\-$3";
						if (not defined($genehash{$genein})) {
							#here nothing else to do then restarting from beginning
							close FILE;
							die "\nSNPs table does not contain info about KASP gene entry: $linein gene $genein. Correct and rerun this code.\n";}	
						if ($genein =~ m/^RS\d+$/i) {
							$genein = uc $genehash{$genein};}		
						if ($fullglims =~ m/^\-(.+)$/) {
							$fullglims = $1;}
						if ($fullglims =~ m/^(.+)\-$/) {
							$fullglims = $1;}
						my $xfluo = $linearr[$reqkaspfield[2][2]];
						my $yfluo = $linearr[$reqkaspfield[3][2]];
						my $manu = $linearr[$reqkaspfield[4][2]];
						my $call = $linearr[$reqkaspfield[5][2]];
						my $score = $linearr[$reqkaspfield[6][2]];
						if (($xfluo !~ m/^\d+\.\d+$/) || ($yfluo !~ m/^\d+\.\d+$/) || ($manu !~ m/^[01]$/) || ($score !~ m/^\d+\.\d+$/) || ($score > 1)) {#checking xfluo, yfluo and manual values
							close FILE;
							print "\n\nCorrupted (bad format xfluo or yfluo or score or manual) KASP file $kaspfilename entry $linein.\nHave Ctrl-C to quit, anything else to try again.\n";
							my $answer = <STDIN>;
							goto CHECKKASPFILE;}
						#dealing with KASP calls
						if ($call =~ m/\sX$/i) {
							$call = 'XX';}
						elsif ($call =~ m/\sY$/i) {
							$call = 'YY';}
						elsif ($call =~ m/^BOTH/i) {
							$call = 'XY';}
						else {
							close FILE;
							print "\n\nCorrupted (bad format for call $call) in KASP file $kaspfilename entry $linein.\nHave Ctrl-C to quit, anything else to try again.\n";
							my $answer = <STDIN>;
							goto CHECKKASPFILE;}
						my $fetchalleles = $genein . "_$call";
						my $alleles = $allelehash{$fetchalleles};
						my $kchk = 1;
						CHECKKASPGLIMS:
						while(defined($kaspglimsarr[$kchk][1])) {
							if ($kaspglimsarr[$kchk][1] eq $baseglims) {
								++$kaspglimsarr[$kchk][2];
								last CHECKKASPGLIMS;}
							++$kchk;}
						if (not defined($kaspglimsarr[$kchk][1])) {
							$kaspglimsarr[0][0] = $kchk;
							$kaspglimsarr[$kchk][1] = $baseglims;
							++$kaspglimsarr[$kchk][2];}
						$tempkasparr[++$tempkasparr[0][0]][1] = $kaspfilename;
						$tempkasparr[$tempkasparr[0][0]][2] = $fullglims;
						$tempkasparr[$tempkasparr[0][0]][3]  = $baseglims;
						$tempkasparr[$tempkasparr[0][0]][4]  = $genein;
						$tempkasparr[$tempkasparr[0][0]][5] = $genehash{$genein};#generef
						$tempkasparr[$tempkasparr[0][0]][6] = $call;
						$tempkasparr[$tempkasparr[0][0]][7] = $alleles;
						$tempkasparr[$tempkasparr[0][0]][8] = $manu;
						$tempkasparr[$tempkasparr[0][0]][9] = $xfluo;
						$tempkasparr[$tempkasparr[0][0]][10] = $yfluo;
						$tempkasparr[$tempkasparr[0][0]][11] = $score;}
					elsif ($glimsplusgene =~ m/^BLANC[\s\-\_]+(.+)$/i) {#special case of blanc that we need to summarize by gene (if no blank KASP file is devalidated)
						my $genein = uc $1;
						if (not defined($genehash{$genein})) {
							#here nothing else to do then restarting from beginning
							close FILE;
							die "\nSNPs table does not contain info about KASP gene entry: $linein gene $genein. Correct and rerun this code.\n";}
						if ($genein =~ m/^RS\d+$/i) {
							$genein = uc $genehash{$genein};}	
						my $xfluo = $linearr[$reqkaspfield[2][2]];
						my $yfluo = $linearr[$reqkaspfield[3][2]];
						if (($xfluo !~ m/^\d+\.\d+$/) || ($yfluo !~ m/^\d+\.\d+$/)) {
							close FILE;
							print "\n\nCorrupted (bad format xfluo or yfluo or score or manual) KASP file $kaspfilename entry $linein.\nHave Ctrl-C to quit, anything else to try again.\n";
							my $answer = <STDIN>;
							goto CHECKKASPFILE;}
						++$blancval[$pointergenehash{$genein}][1];#count of blanc values X fluo
						++$blancval[$pointergenehash{$genein}][4];#count of blanc values Y fluo
						if (not defined($blancval[$pointergenehash{$genein}][2])) {#so to initialize
							$blancval[$pointergenehash{$genein}][2] = $xfluo;# sum of blanc values X fluo
							$blancval[$pointergenehash{$genein}][3] = $xfluo ** 2; #some of squared X fluo
							$blancval[$pointergenehash{$genein}][5] = $yfluo;# sum of blanc values Y fluo
							$blancval[$pointergenehash{$genein}][6] = $yfluo ** 2;} #some of squared Y fluo
						else {#in case that more then one blanc per gene
							$blancval[$pointergenehash{$genein}][2] =$blancval[$pointergenehash{$genein}][2] + $xfluo;# sum of blanc values X fluo
							$blancval[$pointergenehash{$genein}][3] = $blancval[$pointergenehash{$genein}][3] + $xfluo ** 2; #some of squared X fluo
							$blancval[$pointergenehash{$genein}][5] = $blancval[$pointergenehash{$genein}][5] + $yfluo;# sum of blanc values Y fluo
							$blancval[$pointergenehash{$genein}][6] = $blancval[$pointergenehash{$genein}][6] + $yfluo ** 2;}} #some of squared Y fluo
					else {#unexpected gene + glims entry
						close FILE;
						print "\n\nCorrupted (bad format glims + gene) KASP file $kaspfilename entry $linein.\nHave Ctrl-C to quit, anything else to try again.\n";
						my $answer = <STDIN>;
						goto CHECKKASPFILE;}}
				close FILE;
				if (not defined($tempkasparr[0][0])) { #not a single valid entry
					close FILE;
					print "\n\nUnable to identify a single valid entry from KASP file $kaspfilename.\nHave Ctrl-C to quit, anything else to try again.\n";
					my $answer = <STDIN>;
					goto CHECKKASPFILE;}
				#if there are blanc values, then added to entries
				my $k2 = 1;
				while ($k2 <= $tempkasparr[0][0]) {#for all entries
					my $kaspinvalue;
					$kasparr[++$kasparr[0][0]][1] = $tempkasparr[$k2][1];
					$kasparr[$kasparr[0][0]][2] = $tempkasparr[$k2][2];
					$kasparr[$kasparr[0][0]][3] = $tempkasparr[$k2][3];
					$kasparr[$kasparr[0][0]][4] = $tempkasparr[$k2][4];
					$kasparr[$kasparr[0][0]][5] = $tempkasparr[$k2][5];
					$kasparr[$kasparr[0][0]][6] = $tempkasparr[$k2][6];
					$kasparr[$kasparr[0][0]][7] = $tempkasparr[$k2][7];
					$kasparr[$kasparr[0][0]][8] = $tempkasparr[$k2][8];
					$kasparr[$kasparr[0][0]][9] = $tempkasparr[$k2][9];
					$kasparr[$kasparr[0][0]][10] = $tempkasparr[$k2][10];
					$kasparr[$kasparr[0][0]][11] = $tempkasparr[$k2][11];
					if (not defined($blancval[$pointergenehash{$tempkasparr[$k2][4]}][1])) { #so not a single blanc; we add 8 empty fields directly into the final entry array
						$kasparr[$kasparr[0][0]][12] = "NA\tNA\tNA\tNA\tNA\tNA\tNA\tNA";}#string without blanc values (8 NA values)
					else {#so we have some blanc values 
						my $meanofx = $blancval[$pointergenehash{$tempkasparr[$k2][4]}][2]/$blancval[$pointergenehash{$tempkasparr[$k2][4]}][1];
						my $meanofy = $blancval[$pointergenehash{$tempkasparr[$k2][4]}][5]/$blancval[$pointergenehash{$tempkasparr[$k2][4]}][4];
						$kasparr[$kasparr[0][0]][12] = "$meanofx\t$blancval[$pointergenehash{$tempkasparr[$k2][4]}][1]\t$blancval[$pointergenehash{$tempkasparr[$k2][4]}][2]\t$blancval[$pointergenehash{$tempkasparr[$k2][4]}][3]\t$meanofy\t$blancval[$pointergenehash{$tempkasparr[$k2][4]}][4]\t$blancval[$pointergenehash{$tempkasparr[$k2][4]}][5]\t$blancval[$pointergenehash{$tempkasparr[$k2][4]}][6]";}
					++$k2;}
				#here to help user fetching the NGS files
				print "\nSamples (Glims + Genes) in KASP file:\n\n";
				my $kchk = 1;
				while(defined($kaspglimsarr[$kchk][1])) {
					print "\t$kaspglimsarr[$kchk][1]\t$kaspglimsarr[$kchk][2]\n";
					++$kchk;}
				last KASPFILE;}
			else {
				print "\n\nUnable to open KASP file $kaspfilename for reading.\nHave Ctrl-C to quit, anything else to try again.\n";
				my $answer = <STDIN>;
				goto CHECKKASPFILE;}}
		else {
			print "\n\nBad name format for KASP file $kaspfilename.\nHave Ctrl-C to quit, anything else to try again.\n";
			my $answer = <STDIN>;
			goto CHECKKASPFILE;}}
	else {
		print "\n\nCannot find KASP file $kaspfilename.\nHave Ctrl-C to quit, anything else to try again.\n";
		my $answer = <STDIN>;
		goto CHECKKASPFILE;}}		
print "\nFound $kasparr[0][0] KASP entries from KASP file $kaspfilename ($kaspglimsarr[0][0] samples).\n";
################################################################################
#677968693279877869823280736982826932696779678132535748555051505432666947666976#
#now NGS file(s)
my @listofngsglims;
my @listofngsfiles;
my @ngsarr;
my @ngsglimsarr;
my $countngsfiles = 0;
my @reqngsfield;
my $baseglims;
my $fullglims;
$reqngsfield[1][1] = 'web Ref.';
$reqngsfield[2][1] = 'Nuc Change';
my $root;
my $ngsfilename;
NGSFILE:
while (1 > 0) {
	CHECKNGSFILE:
	#creating a Explorer Window to select for the NGS file(s)...More then one usually
	undef $ngsfilename;
	#modification to speed selecting ngs files if from same folder
	#if (not defined($root)) {
		$ngsfilename = Win32::GUI::BrowseForFolder(
			-includefiles => 1,
			-title => "Select the NGS input file (name should contain the Glims 6 digits _ or - 4 digits)",);
			#}
	#else {
#		$ngsfilename = Win32::GUI::BrowseForFolder(
#			-root => "$root",
#			-includefiles => 1,
#			-title => "Select the NGS input file (name should contain the Glims 6 digits _ or - 4 digits)",);}
    if (not defined($ngsfilename)) {
		if ($countngsfiles != 0) {
			last NGSFILE;}
		else {
			print "You need to select at least one NGS file.\nHave Ctrl-C to quit, anything else to try again.\n";
			my $answer = <STDIN>;
			goto CHECKNGSFILE;}}
	if ($ngsfilename =~ m/^(.+)\\[^\\]+$/) {
		$root = $1;}
	#die "$ngsfilename at root $root";
    #next 3 lines to match windows OS reqs = slash to backslash and removing simple/double quotes
    while($ngsfilename  =~ s/\//\\/){}
	while($ngsfilename  =~ s/\'//) {}
	while($ngsfilename  =~ s/\"//) {}
    if (-e $ngsfilename) {# exists
		if ($ngsfilename =~ m/\\([^\\]+)$/) {
			my $checkngsname = $1;
			if ($checkngsname =~ m/(\d*)[\-_]?(\d{6})[\-_](\d{4})[\-_]?(\d*)/i)  {# expected filename format
				$fullglims = "$1\-$2\-$3\-$4";
				$baseglims = "$2\-$3";
				if ($fullglims =~ m/^\-(.+)$/) {
					$fullglims = $1;}
				if ($fullglims =~ m/^(.+)\-$/) {
					$fullglims = $1;}}
			else {
				print "\n\nUnexpected name format for NGS file ($ngsfilename). It should contain Glims.\nHave Ctrl-C to quit, anything else to try again.\n";
				my $answer = <STDIN>;
				goto CHECKNGSFILE;}
			#checking this sample / glims relates to KASP file sample	
			my $k2x = 1;#checking for duplicates (not allowed) and recording files/glims  (if all major alleles...) 
			my $foundglimsx = 0;
			CHECKNGSKASPGLIMS:
			while (defined($kaspglimsarr[$k2x][1])) {
				if ($kaspglimsarr[$k2x][1] eq $baseglims) {
					$foundglimsx = 1;
					last CHECKNGSKASPGLIMS;}
				++$k2x;}
			if ($foundglimsx == 0) {
				print "\nNGS file $ngsfilename does not relates to a KASP sample.\nHave Ctrl-C to quit, anything else to try again.\n";
				my $answer = <STDIN>;
				goto CHECKNGSFILE;}
			my $k2 = 1;#checking for duplicates (not allowed) and recording files/glims  (if all major alleles...) 
			my $foundglims = 0;
			while (defined($ngsglimsarr[$k2])) {
				if (uc $ngsglimsarr[$k2] eq uc $baseglims) {
					print "\n\nThis NGS file ($ngsfilename) relates to a previously identified Glims ($baseglims).\nHave Ctrl-C to quit, anything else to try again.\n";
					my $answer = <STDIN>;
					goto CHECKNGSFILE;}
				++$k2;}
			if ($foundglims == 0) {
				$ngsglimsarr[$k2] = $baseglims;}
			if (open(NGS,"<$ngsfilename")) {
				#so here 2 types of NGS SeqNext: Version 4.1 Header firts line, version 4.x First line file name, second header
				my $header = uc <NGS>;
				if ($header =~ m/^DNA/i) {#simply skip it
				$header = uc <NGS>;}
				chomp $header;
				while ($header =~ s/\"//) {}
				$header = $header . "\t";#this is to keep in the last field name
				my @fieldin;#array to store header fields
				while ($header =~ m/([^\t]*)\t/igc) {#fetching on the fly all field names
					$fieldin[++$fieldin[0]] = $1;}
				#ok lets check that required fields are present and collect where they are
				my @checkreqfields;
				$k2 = 1;
				while (defined($reqngsfield[$k2][1])) {#for all required fields
					my $k3 = 1;
					FINDNGSFIELD:
					while ($fieldin[$k3]) {#for all fields identified
						if ($fieldin[$k3] eq (uc ($reqngsfield[$k2][1]))) {
							$checkreqfields[$k2][1] = $k3;#recording field position in file
							#print "$catcharr[$k1] $fieldin[$k3] in $k3\n";
						last FINDNGSFIELD;}
						++$k3;}
					if (not defined($checkreqfields[$k2][1])) {#a required field has not been identified from header so this file is not used
						close NGS;
						print "\n\nMissing required NGS field ($reqngsfield[$k2][1]) in file $ngsfilename.\nHave Ctrl-C to quit, anything else to try again.\n";
						my $answer = <STDIN>;
						goto CHECKNGSFILE;}
					++$k2;}
				#so all fields are ok (and position recorded) and so let's now collect ngs entries
				my @tempngsarr;#array to store temporarily values of this NGS file
				while (defined(my $linein = <NGS>)) {#for all entries in NGS file
					$linein = uc $linein;
					chomp $linein;#remove linefeed carriage return
					while ($linein =~ s/\"//) {}#removal of putative text delimiters
					$linein = $linein . "\t";#adding tab for regexp matching
					my @linearr;#array to store values
					while ($linein =~ m/([^\t]*)\t/igc) {#fetching on the fly all field values
						$linearr[++$linearr[0]] = $1;}
					#now focusing on required ones and first sample name
					my $ngsgeneref = $linearr[$checkreqfields[1][1]]; # gene reference
					my $ngscall = $linearr[$checkreqfields[2][1]];#call still uc to avoid typos glims plus gene
					if ($ngsgeneref =~ m/^(RS\d+)[^\d]?/) {
						$ngsgeneref = $1;}
					my $xngscall;
					if (defined($genehash{$ngsgeneref})) {#only if in SNP table
						#recording info in global NGS array	
						my $ngsgenename = $genehash{$ngsgeneref};
						$xngscall = $snparr[$pointergenehash{$ngsgenename}][5];
						if ($ngscall =~ m/HOMO/i) {
							$xngscall = $snparr[$pointergenehash{$ngsgenename}][3];}
						if ($ngscall =~ m/HET/i) {
							$xngscall = $snparr[$pointergenehash{$ngsgenename}][4];}
						$ngsarr[++$ngsarr[0][0]][1] = $ngsfilename; # ngs file name
						$ngsarr[$ngsarr[0][0]][2] = $fullglims; # full Glims
						$ngsarr[$ngsarr[0][0]][3] = $baseglims; # base Glims
						$ngsarr[$ngsarr[0][0]][4] = $ngsgenename; # Gene name
						$ngsarr[$ngsarr[0][0]][5] = $ngsgeneref; # Gene ref
						$ngsarr[$ngsarr[0][0]][6] = $xngscall; # NGS call
						my $fetchngsalleles = $ngsgenename . "_$xngscall";
						$ngsarr[$ngsarr[0][0]][7] = $allelehash{$fetchngsalleles};
						######Important still need to be updated...Fetching reads of the alleles
						$ngsarr[$ngsarr[0][0]][8] = "NA";
						$ngsarr[$ngsarr[0][0]][9] = "NA";}} 
				close NGS;
				++$countngsfiles;
				$listofngsglims[$countngsfiles] = $baseglims;
				$listofngsfiles[$countngsfiles] = $ngsfilename;}
			else {
				print "\n\nUnable to open NGS file $ngsfilename for reading.\nHave Ctrl-C to quit, anything else to try again.\n";
				my $answer = <STDIN>;
				goto CHECKNGSFILE;}}
		else {
			print "\n\nUnable to capture file name for NGS file $ngsfilename.\nHave Ctrl-C to quit, anything else to try again.\n";
			my $answer = <STDIN>;
			goto CHECKNGSFILE;}}
	else {
		print "\n\nCannot find NGS file $ngsfilename.\nHave Ctrl-C to quit, anything else to try again.\n";
		my $answer = <STDIN>;
		goto CHECKNGSFILE;}}						
print "\nIdentified $countngsfiles NGS file(s) and captured $ngsarr[0][0] entries\n";
################################################################################
#677968693279877869823280736982826932696779678132535748555051505432666947666976#
#now matching NGS to KASP Glims+Gene (not the reverse)
#first creating the MS-Excel summary output file
my $msexcelsummary = "$outputdir\/SamplesTrackerSummary_$runtime.xlsx";
my $forhyperlink = "SamplesTrackerSummary_$runtime.xlsx";
if (-e $msexcelsummary) {
	unlink $msexcelsummary ||
		die "\nCannot erase MS-Excel file $msexcelsummary\n";}
copy ("$summarytemplate","$msexcelsummary") ||
	die "\nCannot create MS-Excel file $msexcelsummary from $summarytemplate\n";
my $Excel = Win32::OLE->new("Excel.Application", 'Quit');
$Excel->{'Visible'} = 0;    
$Excel->{DisplayAlerts}=0;       
my $Book = $Excel->Workbooks->Open("$msexcelsummary");
#activating Userdata sheet and loading users data
my $Sheet = $Book->Worksheets("Userdata");
$Sheet->Activate();
my $k1 = 0;
my $rownum = 2;
while(defined($userarr[$k1])) {
	my $colnum = 0;
	while($userarr[$k1] =~ m/([^\t]+)\t/igc) {
		++$colnum;
		my $cellval = $1;
		my $cellref = $cellhash{$colnum} . $rownum;	
		$Sheet->Range("$cellref")->{Value} = $cellval;}
	++$k1;
	++$rownum;}
$Sheet->Protect($protectpassword);
$Sheet = $Book->Worksheets("StartHere");
$Sheet->Activate();
$Sheet->Range("B16")->{Value} = $runtime;
$Sheet->Range("B18")->{Value} = $code;
$Sheet->Protect($protectpassword);
################################################################################
#677968693279877869823280736982826932696779678132535748555051505432666947666976#
#activating KASP data sheet
$Sheet = $Book->Worksheets("KASPdata");
$Sheet->Activate();
#ranking KASP data per KASP filename, FullGlims, BaseGlims and Genename and ordering fields as wanted
#loading data (single string tab delimited)
my @torankkasp;
$k1 = 1;
my $pointertorankkasp = 0;
$k1 = 1;
while($k1 <= $kasparr[0][0]) {
	$torankkasp[$pointertorankkasp] = $kasparr[$k1][1];
	my $k2 = 2;
	while ($k2 <= 12) {
		$torankkasp[$pointertorankkasp] = $torankkasp[$pointertorankkasp] . "\t$kasparr[$k1][$k2]";
		++$k2;}
	$torankkasp[$pointertorankkasp] = $torankkasp[$pointertorankkasp] ."\t";
	++$pointertorankkasp;
	++$k1;}
#now ranking  KASP data
$field1 = 1;#kasp file name
$field2 = 2;#kasp full glims
my $field3 = 3;#kasp base glims
my $field4 = 4;#kasp gene name
my $field5 = 5;#kasp gene ref
my @kasprankedarr = map {$_->[0]} 
	sort {
		my @a_fields = @$a[1..$#$a];
		my @b_fields = @$b[1..$#$b];
		$a_fields[$field1-1] cmp $b_fields[$field1-1]
			||
		$a_fields[$field2-1] cmp $b_fields[$field2-1]
			||
		$a_fields[$field3-1] cmp $b_fields[$field3-1]
			||
		$a_fields[$field4-1] cmp $b_fields[$field4-1]
			||
		$a_fields[$field5-1] cmp $b_fields[$field5-1]}
map {[$_, split /\t/]} @torankkasp;
#before printing we need to check that none of glims/kasp has more then 32 SNPs
$k1 = 0;
my $countsnp = 0;
my $countglims = 0;
my $preyglims;
my $yglims;
my $maxvalctsnp =0;
while (defined($kasprankedarr[$k1])) {
	if ($kasprankedarr[$k1] =~ m/[^\t]*\t([^\t]*)\t/) {
		$yglims = $1;}
	if ( not defined($preyglims)) {
		$countsnp = 1;
		++$countglims;}
	elsif ($preyglims eq $yglims) {
		++$countsnp;}
	else {
		if ($countsnp > $maxvalctsnp) {
			$maxvalctsnp = $countsnp;}
		$countsnp = 1;
		++$countglims;}
	++$k1;
	$preyglims = $yglims;}
if ($countsnp > $maxvalctsnp) {
	$maxvalctsnp = $countsnp;}
if ($maxvalctsnp > $maxsnps) {
	die "Sorry but this code can only handle up to $maxsnps samples. Check KASP file, correct and rerun this code.";}
my @kasprk;#array of array to store ranked data
$kasprk[0][0] = $kasparr[0][0];
$k1 = 0;
$rownum = 2;
while (defined($kasprankedarr[$k1])) {
	my $colnum = 1;
	while ($kasprankedarr[$k1] =~ m/([^\t]*)\t/igc) {
		my $cellref = $cellhash{$colnum} . $rownum;
		my $cellval = $1;
		$kasprk[$k1+1][$colnum] = $1;
		$Sheet->Range("$cellref")->{Value} = $cellval;
		++$colnum;}
	++$rownum;
	++$k1;}
$Sheet->Protect($protectpassword);	
################################################################################
#677968693279877869823280736982826932696779678132535748555051505432666947666976#
#lets now record NGS data in 
$Sheet = $Book->Worksheets("NGSdata");
$Sheet->Activate();  
#loading and ranking (NGS file, Full Glims, Base glims, Genename, $generef) NGS data
my @torankngs;
my $pointertorankngs = 0;
$k1 = 1;
while ($k1 <= $ngsarr[0][0]) {
	my $k2 = 2;
	$torankngs[$pointertorankngs] = $ngsarr[$k1][1];
	while ($k2 <= 9) {
		$torankngs[$pointertorankngs] =$torankngs[$pointertorankngs] . "\t$ngsarr[$k1][$k2]";
		++$k2;}
	$torankngs[$pointertorankngs] =$torankngs[$pointertorankngs] . "\t";
	++$pointertorankngs;
	++$k1;}
#so ranking of NGS data
$field1 = 1;#ngs file name
$field2 = 2;#ngs full glims
$field3 = 3;#ngs base glims
$field4 = 4;#ngs gene name
$field5 = 5;#ngs gene ref
my @ngsrankedarr = map {$_->[0]} 
	sort {
		my @a_fields = @$a[1..$#$a];
		my @b_fields = @$b[1..$#$b];
		$a_fields[$field1-1] cmp $b_fields[$field1-1]
			||
		$a_fields[$field2-1] cmp $b_fields[$field2-1]
			||
		$a_fields[$field3-1] cmp $b_fields[$field3-1]
			||
		$a_fields[$field4-1] cmp $b_fields[$field4-1]
			||
		$a_fields[$field5-1] cmp $b_fields[$field5-1]}
map {[$_, split /\t/]} @torankngs;

my @ngsrk; #array of array to store ranked NGS data
$ngsrk[0][0] = $ngsarr[0][0];
$k1 = 0;
$rownum = 2;
while (defined($ngsrankedarr[$k1])) {
	my $colnum = 1;
	while ($ngsrankedarr[$k1] =~ m/([^\t]*)\t/igc) {
		my $cellref = $cellhash{$colnum} . $rownum;
		my $cellval = $1;
		$ngsrk[$k1+1][$colnum] = $1;
		$Sheet->Range("$cellref")->{Value} = $cellval;
		++$colnum;}
	++$rownum;
	++$k1;}
$Sheet->Protect($protectpassword);
################################################################################
#677968693279877869823280736982826932696779678132535748555051505432666947666976#
#now for validation purpose loading the SNP table
$Sheet = $Book->Worksheets("SNPsTable");
$Sheet->Activate();
my @toranksnptable;
my $pointertoranksnptable = 0;
$k1 = 1;
while ($k1 <= $snparr[0][0]) {
	$toranksnptable[$pointertoranksnptable] = "$snparr[$k1][1]\t$snparr[$k1][2]\t$snparr[$k1][6]\t$snparr[$k1][7]\t$snparr[$k1][8]\t$snparr[$k1][9]\t$snparr[$k1][10]\t$snparr[$k1][11]\t$snparr[$k1][12]\t";
	++$pointertoranksnptable;
	++$k1;}
#so ranking of NGS data
$field1 = 1;#gene name
$field2 = 2;#gene ref
my @snptablerankedarr = map {$_->[0]} 
	sort {
		my @a_fields = @$a[1..$#$a];
		my @b_fields = @$b[1..$#$b];
		$a_fields[$field1-1] cmp $b_fields[$field1-1]
			||
		$a_fields[$field2-1] cmp $b_fields[$field2-1]}
map {[$_, split /\t/]} @toranksnptable;

$k1 = 0;
$rownum = 2;
while (defined($snptablerankedarr[$k1])) {
	my $colnum = 1;
	while ($snptablerankedarr[$k1] =~ m/([^\t]*)\t/igc) {
		my $cellref = $cellhash{$colnum} . $rownum;
		my $cellval = $1;
		$Sheet->Range("$cellref")->{Value} = $cellval;
		++$colnum;}
	++$rownum;
	++$k1;}
$Sheet->Protect($protectpassword);
################################################################################
#677968693279877869823280736982826932696779678132535748555051505432666947666976#
#also for validation purposes: This code is entirely included in MS-Excel
$Sheet = $Book->Worksheets("PerlCode");
$Sheet->Activate();
open(CODE,"$0") ||
	die "Cannot read from Perl code $0";
my $kline = 0;
while(defined(my $linein = <CODE>)) {
	chomp $linein;
	while ($linein =~ s/\t/ /) {}
	$linein = '#' . $linein;
	++$kline;
	my $cellref = "A$kline";
	$Sheet->Range("$cellref")->{Value} = $linein;}
$Sheet->Protect($protectpassword);
################################################################################
#677968693279877869823280736982826932696779678132535748555051505432666947666976#
#ok lets go for  matching NGS vs KASP (real amazing part) So the match is done by glims
#and we start with KASP glims (distinction between full and base glims)
my @collectsummary;
my @batcharr;
my @linearr;
my $prekaspglims ="XYZ";
my $ctsamples = 0;
my $found;
my @sheetinfo;
my @recorddone;
$k1 =1;
while($k1 <=$kasprk[0][0]) {
	my $kaspglims = $kasprk[$k1][3];
	my $kaspgene = $kasprk[$k1][5];
	if ($kaspglims ne $prekaspglims) {
		++$ctsamples;
		#next are for summary sheet
		$collectsummary[$ctsamples][0] = $kaspglims; #the glims also sheet name
		$collectsummary[$ctsamples][1] = 0;# count of matching
		$collectsummary[$ctsamples][2] = 0;# count of different
		$collectsummary[$ctsamples][3] = 0;# count of NGS Major
		$collectsummary[$ctsamples][4] = 1;# KASP AF
		$collectsummary[$ctsamples][5] = 1;# NGS AF
		$collectsummary[$ctsamples][6] = 1;# Differential AF
		#creating a sheet
		$Book->Save();
		my $Template = $Book->Worksheets("SampleTemplate");
		$Template->Copy({Before=>$Book->Worksheets("SampleTemplate")});
		$Book->Save();
		$sheetinfo[$ctsamples][1] = $kaspglims;
		my $refnum = $ctsamples + 1;
		$sheetinfo[$ctsamples][2] = "SampleTemplate ($refnum)";
		$Sheet = $Book->Worksheets($sheetinfo[$ctsamples][2]);
		$Sheet->Activate();
		$Sheet->Range("V7")->{Value} = $kaspglims;
		$recorddone[$ctsamples] = 0;}
	my $kz2 =1;
	$found = 0;
	FOUNDGLIMS:
	while (defined($listofngsfiles[$kz2])) {
		if ($listofngsglims[$kz2] eq $kaspglims) {
			$found = $kz2;
			last FOUNDGLIMS;}
		++$kz2;}
	if ($found != 0) {#so existing correlated glims and so looking for corresponding NGS gene (info already recorded
		my $ky2 =1;
		my $foundval = 0;
		FOUNDVAL:
		while ($ky2 <= $ngsrk[0][0]) {
			if (($ngsrk[$ky2][3] eq $kaspglims) && ($ngsrk[$ky2][5] eq $kaspgene)) {
				$foundval = $ky2;
				last FOUNDVAL;}
			++$ky2;}
		++$recorddone[$ctsamples];#this for position to write in sheet
		loadsampledata($ctsamples,$k1,$foundval,$recorddone[$ctsamples]);}
	$prekaspglims = $kaspglims;	
	++$k1;}
$Book->Save();
#now renaming all samples sheets and protecting
$k1 = 1;
while($k1 <= $ctsamples) {
	$Sheet = $Book->Worksheets("$sheetinfo[$k1][2]");
	$Sheet->Activate();
	my $nameofsheet = $sheetinfo[$k1][1];
	while ($nameofsheet =~ s/\-/_/) {}
	$Sheet -> {Name} = "$nameofsheet";
	$collectsummary[$k1][7] = $nameofsheet;
	$Sheet->Protect($protectpassword);
	$Book->Save(); 
	++$k1;}
$Book->Save(); 
$Sheet = $Book->Worksheets("SampleTemplate");
$Sheet->Activate();
$Sheet->Protect($protectpassword);
#here loading summary data
$Sheet = $Book->Worksheets("Summary");
$Sheet->Activate();
$Sheet->Range("J1")->{Value} = "SamplesTrackerSummary_$runtime.xlsx";
$k1 = 1;
my $refcolx;
my $refrowx = 1;
my $refcellx;
while($k1 <= $ctsamples) {
	++$refrowx;
	$refcellx =  "A". $refrowx;
	$Sheet->Range("$refcellx")->{Value} = $collectsummary[$k1][7];
	$refcellx =  "H". $refrowx;
	$Sheet->Range("$refcellx")->{Value} = $collectsummary[$k1][3];
	++$k1;}
$Sheet->Protect($protectpassword);
$Book->Save();
################################################################################
#677968693279877869823280736982826932696779678132535748555051505432666947666976#
#activating RunInfo and closing run
$Sheet = $Book->Worksheets("RunInfo");
$Sheet->Activate();
$Sheet->Range("A1")->{Value} = "RunDateTime";
$Sheet->Range("B1")->{Value} = $runtime;
$Sheet->Range("A2")->{Value} = "PerlCode";
$Sheet->Range("B2")->{Value} = $0;
$Sheet->Range("A3")->{Value} = "CodeDir";
$Sheet->Range("B3")->{Value} = $codedir;
$Sheet->Range("A4")->{Value} = "MSExcelTemplate";
$Sheet->Range("B4")->{Value} = $summarytemplate;
$Sheet->Range("A5")->{Value} = "OutputDir";
$Sheet->Range("B5")->{Value} = $outputdir;
$Sheet->Range("A6")->{Value} = "UserList";
$Sheet->Range("B6")->{Value} = $userfilename;
$Sheet->Range("A7")->{Value} = "SNPsTable";
$Sheet->Range("B7")->{Value} = $snpfilename;
$Sheet->Range("A8")->{Value} = "KASPfile";
$Sheet->Range("B8")->{Value} = $kaspfilename;
$Sheet->Range("A9")->{Value} = "KASPSamples";
$Sheet->Range("B9")->{Value} = $kaspglimsarr[0][0];
$Sheet->Range("A10")->{Value} = "KASPSampleGlims";
$Sheet->Range("B10")->{Value} = "KASPSampleGenes";
my $kpr1 = 1;
my $numcell = 11;
while ($kpr1 <= $kaspglimsarr[0][0]) {
	my $refcell1 = "A$numcell";
	my $refcell2 = "B$numcell";
	$Sheet->Range("$refcell1")->{Value} = $kaspglimsarr[$kpr1][1];
	$Sheet->Range("$refcell2")->{Value} = $kaspglimsarr[$kpr1][2];
	++$numcell;
	++$kpr1;}
my $refcell1 = "A$numcell";
my $refcell2 = "B$numcell";
$Sheet->Range("$refcell1")->{Value} = "NGSglims";
$Sheet->Range("$refcell2")->{Value} = "NGSfile";
++$numcell;
$kpr1 = 1;
while ($kpr1 <= $countngsfiles) {
	my $refcell1 = "A$numcell";
	my $refcell2 = "B$numcell";
	$Sheet->Range("$refcell1")->{Value} = $listofngsglims[$kpr1];
	$Sheet->Range("$refcell2")->{Value} = $listofngsfiles[$kpr1];
	++$numcell;
	++$kpr1;}
$Sheet->Protect($protectpassword);
$Sheet = $Book->Worksheets("StartHere");
$Sheet->Activate();
$Book->Save(); 
$Book->Close();
$Excel->Quit();
die "\nProcess Completed. $0\n";
#677968693279877869823280736982826932696779678132535748555051505432666947666976#
################################################################################
sub loadsampledata {
#to load data in individual sample sheets
	my $samplingnum = shift;
	my $kaspnum = shift;
	my $ngsnum = shift;
	my $posinsheet = shift;
	$posinsheet = $posinsheet + 14;#relates to first row to write in
	#my $sheetname = "Sample_$samplingnum";
	#$Sheet = $Book->Worksheets("$sheetname");
	#$Sheet->Activate();
	my $viewkaspafx = $kasprk[$kaspnum][4] ."_$kasprk[$kaspnum][6]";
	my $viewkaspaf = $afhash{$viewkaspafx};
	my $viewkaspalleles = $allelehash{viewkaspafx};
	my $viewngscall;
	my $viewngsaf;
	my $viewngsalleles;
	my $modengsview=1;
	if ($ngsnum == 0) {#so not reported and we need to go to SNP table
		++$collectsummary[$samplingnum][3]; 
		my $kx2 = 1;
		$modengsview = 0;
		while(defined($snparr[$kx2][1])) {
			if ($snparr[$kx2][2] eq $kasprk[$kaspnum][5]) {
				$viewngscall = $snparr[$kx2][5];
				my $catchit = $kasprk[$kaspnum][4] ."_$viewngscall";
				$viewngsalleles = $allelehash{$catchit};
				$viewngsaf = $afhash{$catchit};}
			++$kx2;}}
	else {
		$viewngscall = $ngsrk[$ngsnum][6];
		my $catchit = $kasprk[$kaspnum][4] ."_$viewngscall";
		$viewngsaf = $afhash{$catchit};
		$viewngsalleles = $allelehash{$catchit};}
	$Sheet->Range("B$posinsheet")->{Value} = "$kasprk[$kaspnum][4]";#GeneName
	$Sheet->Range("D$posinsheet")->{Value} = "$kasprk[$kaspnum][5]";#GeneRef
	$Sheet->Range("F$posinsheet")->{Value} = "$kasprk[$kaspnum][6]";#KASPcall
	$Sheet->Range("H$posinsheet")->{Value} = "$viewngscall";#NGScall
	$Sheet->Range("J$posinsheet")->{Value} = "$kasprk[$kaspnum][7]";#KASPalleles
	$Sheet->Range("L$posinsheet")->{Value} = "$viewngsalleles";#NGSalleles
	$Sheet->Range("N$posinsheet")->{Value} = "$viewkaspaf";#KASPaf
	$Sheet->Range("P$posinsheet")->{Value} = "$viewngsaf";#NGSaf
	#$Sheet->Range("K$posinsheet")->{Value} = "$kasprk[$kaspnum][9]";#KASPxFluo
	#$Sheet->Range("L$posinsheet")->{Value} = "$kasprk[$kaspnum][10]";#KASPyFluo
	#$Sheet->Range("M$posinsheet")->{Value} = "$kasprk[$kaspnum][11]";#KASPscore
	$Sheet->Range("R$posinsheet")->{Value} = "$kasprk[$kaspnum][8]";#KASPmode
	#$Sheet->Range("O$posinsheet")->{Value} = "$kasprk[$kaspnum][12]";#KASPxBlanc
	#$Sheet->Range("P$posinsheet")->{Value} = "$kasprk[$kaspnum][16]";#KASPyBlanc
	#$Sheet->Range("Q$posinsheet")->{Value} = "NA";#NGS xReads
	#$Sheet->Range("R$posinsheet")->{Value} = "NA";#NGS yReads	
	$Sheet->Range("T$posinsheet")->{Value} = "$modengsview";}#NGSmode
#	$collectsummary[$samplingnum][4] = $collectsummary[$samplingnum][4] *  $viewkaspaf;
#	$collectsummary[$samplingnum][5] = $collectsummary[$samplingnum][5] *  $viewngsaf;
#	if ($kasprk[$kaspnum][6] eq $viewngscall) {
#		++$collectsummary[$samplingnum][1];
#		$collectsummary[$samplingnum][6] = $collectsummary[$samplingnum][6] *  $viewngsaf;}
#	else {
#		++$collectsummary[$samplingnum][2];}}
#677968693279877869823280736982826932696779678132535748555051505432666947666976#
################################################################################
#################
sub getdatetime {
    #sub to capture GMT date and time
    my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = gmtime;#catch date starting process
    $year = $year + 1900;#$year was number of years since 1900
    $mon = $mon + 1;#first day is zero!!!
    if ($mon < 10) {
	$mon = "0" . $mon;}
    if ($mday < 10) {
	$mday = "0" . $mday;}
    if ($hour < 10) {
	$hour = "0" . $hour;}
    if ($min < 10) {
	$min = "0" . $min;}
    if ($sec < 10) {
	$sec = "0" . $sec;}
    my $date = "$year$mon$mday";
    my $time = "$hour$min$sec";
    my $xdatetime = $date ."_$time";
    return $xdatetime;}
###############
sub loadcells {
#for loading in msexcel columns
	my %xcellhash = (1 => 'A',2 => 'B',3 => 'C',4 => 'D',5 => 'E',6 => 'F',7 => 'G',8 => 'H',9 => 'I',10 => 'J',11 => 'K',12 => 'L',13 => 'M',14 => 'N',15 => 'O',16 => 'P',17 => 'Q',18 => 'R',19 => 'S',20 => 'T',21 => 'U',22 => 'V',23 => 'W',24 => 'X',25 => 'Y',26 => 'Z',);
	return (%xcellhash);}
################################################################################
#677968693279877869823280736982826932696779678132535748555051505432666947666976#
#test for duplicating a worksheet
sub pending {
$k1 = 1;
while ($k1 <= 3) {
	my $Template = $Book->Worksheets("SampleTemplate");
	$Template->Copy({Before=>$Book->Worksheets("SampleTemplate")});;
	++$k1;}
	$Book->Save();
$k1 = 1;
my $num = 2;
while ($k1 <= 3) {
	my $sheetname = "SampleTemplate ($num)";
	my $Sheetx = $Book->Worksheets("$sheetname");
	$Sheetx->Activate();
	$Sheetx->Range("A2")->{Value} = $num;
	$Sheetx -> {Name} = "Sample_$k1";
	++$num;
	++$k1;}
$Book->Save();
$Book->Close();
$Excel->Quit();
die "\nProcess Completed. $0\n";}
#677968693279877869823280736982826932696779678132535748555051505432666947666976#
################################################################################

=head1 NAME

SamplesTrackerEN_v1i1.pl

=head1 HELP

This code has been developped for Windows 7 or higher OS/platforms.

It requires MS-Excel 2007 (.xlsx) or higher to be installed as well as  
Perl (developped with 5.22.1) and some Perl Modules.

It is part of a software package that includes also one Perl code dedicated
MS-Excel Template (that should be named as perl code except extension).

Objective: Insure Samples Traceability/Identification via SNPs identified
primarily via KASP Fluo (Kompetitive Allele Specific PCR;exhaustive list of SNPs 
by sample/Glims in a KASP output file) then confirmed via NGS data collected 
from SeqNext output files (one file per sample/Glims)

Remark: As MS-Excel under Windows is used depending of OS / MS-Excel settings on
the platform, 2 versions of the Perl code exist:

	FR: the "French" version using "comma" as decimal delimiter
	EN: the "English" version using "dot" as decimal delimiter

Required Input Files (apart from Perl code + MS Excel Template).

The following files/folders will be used:

	0. An existing output folder to record the MS-Excel output file.
	
	1. One SNPs table named as SNPsTable_...YYYYMMDD.txt.
	   Fields required:
			GeneName (should be unique as KASP Fluo; string)
			GeneRef ("rs + number"; should be unique)	
			Xkasp (KASP Major Allele)
			Ykasp (KASP Minor Allele)
			Xngs (NGS Major Allele)
			Yngs (NGS Minor Allele)
			XAFkasp (KASP Major Allele Frequency)
			YAFkasp (KASP Minor Allele Frequency)
			
	2. One KASP file (named starting with "KASP").
	   Fields required:
			Sample Name (minimally contains a Basic Glims 6 digits -or_ 4 digits)
			X-Fluor (measured X allele Fluo; positive numeric)
			Y-Fluor (measured X allele Fluo; positive numeric)
			ManualCall (0 or 1; 1 means manually modified call)
			Call (Allele X, Allele Y or Both Alleles)
			Score (numeric value 0 to 1 reflecting confidence level)
			
	3. NGS data files (SeqNext output). In theory related to KASP fluo entries 
	   via Glims (this Glims is present in the file name).
	   Fields required:
			web Ref. (rs + number)
			Nuc Change (will match "Het" if both allele present, "Homo" if only
			minor allele detected. Absent if only major allele detected)
			
	4. A User file. This one list Users allowed to review/validate/approve the
	   outputs. Should also be unique and named <UserList_...YYYYMMDD.txt>.
	   Fields required:
			UserName (<Lastname,Firstname_initials,>; min. 6 chars)
			UserID (min. 6 chars)
			UserService (min. 3 chars)
			UserFunction (min. 3 chars)
			
Outputs: Protected MS-Excel workbooks. One Summary (Summary.xlsx and one for each
Glims in KASP fluo file (Sample_1.xlsx, Sample_2.xlsx, etc). These will be
write protected.

This version is the English Version.

For additionnal info. refer to SOP for SamplesTracker System or contact the code/SOP 
Owner/Author.

=head1 VERSION

This documentation refers to SamplesTrackerEN_v1i1.pl (version 1, issue 1)

=head1  USAGE

	SamplesTrackerEN_v1i1.pl
	
=head1 REQUIRED ARGUMENTS

=over

They are no required arguments.

=back

=head1 OPTIONS

=over

They are no optional arguments.

=back
	
=item --version

=item --usage

=item --help

=item --man

=back

=head1 AUTHOR

LECOCQ Pierre 59072327136 (Pierre.Lecocq@NGYX.eu)

=head1 BUGS

If bug is identified please contact code Owner/Author.

=head1 COPYRIGHT

Copyright (c) 2016MAR01, code Owner: LECOCQ Pierre 59072327136.
All Rights Reserved.

This program is not free software. It may not be used , redistributed
and/or modified without code Owner/Author authorisation. Exception to 
the previous is made for active members of the "Service de Génétique du 
Centre Hospitalier Universitaire de l'Université de Liège BE/BEL".

677968693279877869823280736982826932696779678132535748555051505432666947666976
