#!/usr/bin/perl
# genenote_parser.pl
# usage: genenote_parser.pl GSE803.txt
# Perseus Missirlis - Jan 15, 2004

use strict;
use DBI;

####################
# Global Variables #
####################

# DBI

my ($dsn) = "DBI:mysql:schz_db:athena.bcgsc.ca";
my ($user_name) = "schz_rw";
my ($password) = "repeat";
my ($dbh, $sth);
my (@ary);

#######################
# Connect to Database #
#######################

$dbh = DBI->connect ($dsn, $user_name, $password, { RaiseError => 1 });

##############################################################################
# read infile specified on the cmd line                                      #
# read multiple coords of CAG/CTG repeat locations from STDIN one line at a time # ##############################################################################

my $tissue;
my $array;
my $number;
my $last_insert_id;

while (<>) {
  chomp;

if ($_ =~ /\!Sample_title\s+\=\s+Normal\s+((\S+\s+\S+)|\S+)\s+\S+\s+(\S+)\s+\S+\s+(\S+)/) {

	$tissue	= $1;
	$array	= $3;
	$number	= $4;

	} elsif ($_ =~ /(\S+)\s+(\d+\.\d+)\s+(\S)/) {

	my $id_ref = $1;
	my $value = $2;
	my $call = $3;

#	print  "id_ref $id_ref value $value call $call tissue $tissue array $array number $number\n";

	$sth = $dbh->prepare ("INSERT INTO GeneNote VALUES('NULL','$id_ref','$value','$call','$tissue','$array','$number')");
                        $sth->execute ();
			$last_insert_id = $sth-> {mysql_insertid};
			print "The id of the last record inserted into the db is $last_insert_id\n";
	}
print "outside of loop this is the last record $last_insert_id\n\n"; }

##############
# End script #
##############