#!/usr/bin/perl -w
use strict;
use warnings;
use DBI;
use Time::localtime;
use DateTime;
########################################################
#	This script creates new records in the oracle		#
#	 database for Taqman oligos. These are 	#
#	revised versions of oligos that previously failed	#
#	and now the sequence and oligo_name are ready		#
# 	to be inserted into the oligos table.			#
########################################################

my $now = DateTime->now(time_zone => 'floating');
my $yesterday = $now->subtract(days=>1);
my $year = $yesterday->year;
my $month = $yesterday->month;
my $month_abbr = uc($yesterday->month_abbr);# uppercase string chars
my $day = $yesterday->day;
my $year_abbr = substr "$year",2,2;
#creation date for loading into the database date fields
my $create_date =  "$day-$month_abbr-$year_abbr\n";
my $dbh = DBI->connect("DBI:Oracle:jweb", "genes", "biggenes")
             or die "Couldn't connect to database: " . DBI->errstr;

open(my $oligo_file, "irFRP_Oligos.txt") || die "can't open file";

while(<$oligo_file>){
	chomp;
	my @fields = split /\t/;
	my ($oligo, $seq) = ($fields[0], $fields[1]);
	# get project name from substring of $oligo
	
	
	# Create records for irregular oligos. Example, 12453TUR2.
	my $project_name = substr ($oligo,0,5);
	my $sth1 = $dbh->prepare("select project_id from projects where project_name = ?");
	$sth1->execute($project_name);
	my @result1 = $sth1->fetchrow();
	my $project_id = $result1[0];
	# this is where I want to m
	my $oligos_type_name = substr ($oligo, -4,-1);
	my $sth2 = $dbh->prepare("select oligos_type_id from oligos_types where oligos_type_name = ?");
	$sth2->execute($oligos_type_name);
	my @result2 = $sth2->fetchrow();
	my $oligos_type_id = $result2[0];
	
	
	my $sth3 = $dbh->prepare("oligos_sequence.nextval from dual");
	my @result3 = $sth3->fetchrow();
	my $oligo_id = $result3[0];

	my $sth = $dbh->prepare("insert into oligos (oligo_id, project_id, oligo_type_id, oligo_sequence, oligo_name,
								oligo_create_user_id, oligo_modify_user_id, oligo_create_date,
								oligo_modify_date, oligo_disabled_flag) values (?,?,?,?,?,?,?,?,?,?)";
	my $rv = $sth->execute($oligo_id, $project_id, $oligo_type_id, $seq, $oligo, '52', '52', $create_date, $create_date, '0');
}	
close $oligo_file;