#!/usr/local/bin/perl5.004 -w
#######################################################################
# this software has been created by genome research limited (grl).    # 
# grl hereby grants permission to use, copy, modify and distribute    # 
# this software and its documentation for non-commercial purposes     # 
# without fee at the user's own risk on the basis set out below.      #
# grl neither undertakes nor accepts any duty whether contractual or  # 
# otherwise in connection with the software, its use or the use of    # 
# any derivative, and makes no representations or warranties, express #
# or implied, concerning the software, its suitability, fitness for   #
# a particular purpose or non-infringement.                           #
# In no event shall the authors of the software or GRL be responsible # 
# or liable for any loss or damage whatsoever arising in any way      # 
# directly or indirectly out of the use of this software or its       # 
# derivatives, even if advised of the possibility of such damage.     #
# Our software can be freely distributed under the conditions set out # 
# above, and must contain this copyright notice.                      #
#######################################################################
# 	$Id: wpThruput.pl,v 1.5 2003/03/06 08:45:08 ja1 Exp $	
use strict;
#
# Set the badger environment here cause of the cron!
#
BEGIN {
    unshift(@INC, "/usr/local/badger/bin");
}

use WrapDBI;

my $month = shift;
my $year = shift;
my $date;
my @temp = split /\//, $0;
my $prog = pop @temp;
#
# Discourage unauthorised users - ja1(9738), cmc(9373), gej (9856) and badger (9034) only
#
unless ($< == 9738 or $< == 9373 or $< == 9856 or $< == 9034) {
    die "Sorry, you are not unauthorised to use this script\n";
}

my $usage = "Usage: $prog [MON YYYY]\n  eg: $prog JAN 2000, or $prog jan 00 (the same).\n  Omitting both parameters is the same as specifying the current month/year.\n  All dates are taken to be in the current century even if given in full.\n";

if (defined $month and defined $year) {
    $month = uc($month);
    die "$usage\n" unless index('JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC', $month) >= 0; 
    $date = sprintf "to_date('01-%s-%02d', 'DD-MON-YY')", $month, $year % 100;
} else {
    die "$usage\n" if defined $month or defined $year;
    $date = "sysdate";
}

my $between = "trunc($date, 'MM') and add_months(trunc($date, 'MM'), 1)";

# print "between $between\n";

print "JIT plates scanned by WP capillary sequencers\n\n";


printf "%-9s%-9s%-18s%-18s%-18s\n","Date", "   POP6  ", "       POP37      ", "        POP5       ", "      Totals      ";
printf "%9s%9s%9s%9s%9s%9s%9s%9s\n", "", "96-well", "96-well", "384-well", "96-well", "384-well", "Plates", "Lanes";


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

my $pref = 
    $dbh->fetch_all(qq[select /*+ index(gq) index(lp) */ trunc(statusdate), smd.description, 
		       lp.format, count(lp.platename)
		       from gel_status gs, gel_queue gq, seqmachine sm, sequencertype st, gel_sample gsm, 
		       loadingsample ls, loadingplate lp, seqmatrixtypedict smd
		       where statusdate between $between
		       and status = 1
		       and gs.id_gel = gq.id_gel
		       and gq.id_seqmachine = sm.id_seqmachine
		       and sm.id_seqtype = st.id_seqtype
		       and gs.id_gel = gsm.id_gel
		       and gsm.id_loadingsample = ls.id_loadingsample
		       and ls.id_loadingplate = lp.id_loadingplate
		       and st.id_matrix = smd.id_dict
		       and gsm.lane = 1
		       and sm.id_seqservice in (1,3)
		       and st.id_model in (3,5)
		       group by trunc(statusdate), smd.description, lp.format
		       order by trunc(statusdate), smd.description, lp.format
		       ]);


my %data;
foreach (@{$pref}) {
    my ($day, $type, $format, $count) = @{$_};
    $data{$day}{$type}{$format} = $count;
}

foreach my $day (sort keys %data) {
    print $day;
    my $total = 0;
    my $wells = 0;
#    foreach my $type ('Standard MegaBace (96 lanes)','POP5 3700 (96 lanes)', 'ET Modified 3700(96 lanes)') {
#    foreach my $type ('Standard MegaBace (96 lanes)','POP5 3700, v1 (96 lanes)', 'ET mod POP5 3700, v1 (96 lanes)') {
    foreach my $type ('POP6', 'POP37', 'POP5') {
	foreach my $format (96, 384) {
	    next if $format == 384 and $type eq 'POP6';
	    my $count = $data{$day}{$type}{$format} || 0;
	    printf "%9d", $count;
	    $data{TOTAL}{$type}{$format} += $count;
	    $total += $count;
#	    $wells += $count * $format;
	    $wells += $count * 96;
	}
    }
    printf "%9d%9d\n", $total, $wells;
}
print "\nTOTALS   ";
my $plates = 0;
my $wells = 0;
#foreach my $type ('Standard MegaBace (96 lanes)','POP5 3700 (96 lanes)', 'ET Modified 3700(96 lanes)') {
#foreach my $type ('Standard MegaBace (96 lanes)','POP5 3700, v1 (96 lanes)', 'ET mod POP5 3700, v1 (96 lanes)') {    
foreach my $type ('POP6', 'POP37', 'POP5') {
    foreach my $format (96, 384) {
	next if $format == 384 and $type eq 'POP6';
	printf "%9d", $data{TOTAL}{$type}{$format};
	$plates += $data{TOTAL}{$type}{$format};
	$wells += $data{TOTAL}{$type}{$format} * 96;
    }
}
printf "%9d%9d\n\n", $plates, $wells;




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

__END__

select /*+ index(gq) index(lp) */ trunc(statusdate), st.description, lp.format, count(unique lp.platename)
		       from gel_status gs, gel_queue gq, seqmachine sm, sequencertype st, gel_sample gsm, loadingsample ls, loadingplate lp
		       where statusdate >= '01-OCT-00'
		       and status = 1
		       and gs.id_gel = gq.id_gel
		       and gq.id_seqmachine = sm.id_seqmachine
		       and sm.id_seqtype = st.id_seqtype
		       and gs.id_gel = gsm.id_gel
		       and gsm.id_loadingsample = ls.id_loadingsample
		       and ls.id_loadingplate = lp.id_loadingplate
		       group by trunc(statusdate), st.description, lp.format
		       order by trunc(statusdate), st.description, lp.format


#select /*+ index(gq) index(lp) */ trunc(statusdate), st.description, lp.format, count(lp.platename)
#		       from gel_status gs, gel_queue gq, seqmachine sm, sequencertype st, gel_sample gsm, 
#                             loadingsample ls, loadingplate lp
#		       where statusdate between $between
#		       and status = 1
#		       and gs.id_gel = gq.id_gel
#		       and gq.id_seqmachine = sm.id_seqmachine
#		       and sm.id_seqtype = st.id_seqtype
#		       and gs.id_gel = gsm.id_gel
#		       and gsm.id_loadingsample = ls.id_loadingsample
#		       and ls.id_loadingplate = lp.id_loadingplate
#                       and gsm.lane = 1
#		       group by trunc(statusdate), st.description, lp.format
#		       order by trunc(statusdate), st.description, lp.format
#		       ]);
