#!/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 pr
+oject_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_use
+r_id, oligo_create_date,
                                oligo_modify_date, oligo_disabled_flag
+) values (?,?,?,?,?,?,?,?,?,?)";
    my $rv = $sth->execute($oligo_id, $project_id, $oligo_type_id, $se
+q, $oligo, '52', '52', $create_date, $create_date, '0');
}    
close $oligo_file;
[download]