#!/usr/local/bin/perl5.6.1
# 	$Id: zebrafishFix.pl,v 1.2 2003/04/08 07:40:22 ja1 Exp $	
#
# This is a temporary fix for zebrafish digest requests which need to use EcoRV instead
# of BamHI. Meant to be invoked from an early morning cronjob, in which case it will 
# fix requests made the previous day, but can be given an optional command line parameter
# indicating the number of days to subtract from today to get the day in which to operate.
# The latter mode would be useful if the cronjob failed and the command had to be run 
# manually at a later date.

use strict;
use warnings;

use lib '/usr/local/badger/bin';
use WrapDBI;

my $days = $ARGV[0] || 1;


my $dbh = WrapDBI->connect('image', {RaiseError => 1}) or die "Can't connect to Oracle: $!\n";

my ($then) = $dbh->selectrow_array(qq[select trunc(sysdate) - 1 
				      from dual
				      ]);

my $pref = $dbh->selectall_arrayref(qq[select r.id_rdrequest, r.clonename, enzymename, requested_by
				       from rdrequest r, rdrequest_enzyme e, rdrequest_status rs, clone c
				       where r.id_rdrequest = e.id_rdrequest
				       and r.id_rdrequest = rs.id_rdrequest
				       and r.clonename = c.clonename
				       and status = 1
				       and iscurrent = 1
				       and trunc(request_date) = trunc(sysdate) - $days
				       and c.speciesname = 'Zebrafish'
				       and enzymename = 'BamHI'
				       and not exists (select 'x' from rdrequest r1, rdrequest_enzyme e1
						       where r1.id_rdrequest = e1.id_rdrequest
						       and r1.clonename = r.clonename
						       and e1.enzymename = 'EcoRV'
						       and r1.sample_ref = r.sample_ref)
				       order by clonename
				       ]);

my @lines = ();


foreach (@{$pref}) {
    my ($id, $clone, $enz, $person) = @{$_};
    push @lines, sprintf "%10d %-20s %-10s %s\n", $id, $clone, $enz;
    
}


if (@lines) {
    unshift @lines, sprintf "%10s %-20s %-10s %s\n", '--------', '-----', '------'; 
    unshift @lines, sprintf "%10s %-20s %-10s %s\n", 'Id_RdReq', 'Clone', 'Enzyme'; 
    push @lines, sprintf "\n\n%d pending zebrafish BamHI digests found\n", scalar @{$pref};
    my $rc = $dbh->do
	(qq[update rdrequest_enzyme
	    set enzymename = 'EcoRV'
	    where id_rdrequest in (select r.id_rdrequest
				   from rdrequest r, rdrequest_enzyme e, rdrequest_status rs, clone c
				   where r.id_rdrequest = e.id_rdrequest
				   and r.id_rdrequest = rs.id_rdrequest
				   and r.clonename = c.clonename
				   and status = 1
				   and iscurrent = 1
				   and trunc(request_date) = trunc(sysdate) - $days
				   and c.speciesname = 'Zebrafish'
				   and enzymename = 'BamHI'
				   and not exists (select 'x' from rdrequest r1, rdrequest_enzyme e1
						   where r1.id_rdrequest = e1.id_rdrequest
						   and r1.clonename = r.clonename
						   and e1.enzymename = 'EcoRV'
						   and r1.sample_ref = r.sample_ref)
				   )
	    ]);
    push @lines, sprintf "\n%d requests changed to EcoRV\n", $rc;
    open (SENDMAIL, "| /usr/lib/sendmail -t -n") || die "Can't open /usr/lib/sendmail: $!\n";
    my $list = join "", @lines;
    print SENDMAIL <<EOFile;
To: bug
Cc: rs2
Reply-To: bug
Subject: Zebrafish digest requests for $then

Pending zebrafish BamHI digest requests entered on $then and for which no 
EcoRV digest request has been entered:-

$list

End of report
EOFile
    close(SENDMAIL);
}


END {
    $dbh->disconnect() if $dbh;
}
