# DN1
################################
# General owl library (db info)
################################

use strict;

package get;

sub id_person_from_email {    
    my ($pkg, $dbh, $who) = @_;
    my $statement = "select id_person from person where email = '$who' and iscurrent = 1";
    #warn "$statement\n";
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    my $id_person = $sth1->fetchrow();
    $sth1->finish();
    return ($id_person);
}

sub finishing_id_role_from_id_person {
    my ($pkg, $dbh, $id_person) = @_;
    my $statement = "
select id_role 
from team_person_role 
where id_person = \'$id_person\'
and roletype = 'Finishing'
and iscurrent = 1
";
    #warn "$statement\n";
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    my $id_role = $sth1->fetchrow();
    $sth1->finish();
    return ($id_role);
}

sub teamname_from_id_person {
    my ($pkg, $dbh, $id_person) = @_;
    my $statement = "
select teamname from team_person where id_person = $id_person
";
    #warn "$statement\n";
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    my $teamname = $sth1->fetchrow();
    $sth1->finish();
    return ($teamname);
}

sub options_from_team {
    my ($pkg, $dbh, $teamname) = @_;
    my @possibles;
    my $statement = "
select fsname, id_team_finserv
from team_finservice 
where teamname = $teamname
";
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    my ($curr_finservice, $id_team_finserve);
    while (($curr_finservice, $id_team_finserve) = $sth1->fetchrow()) {
	
    
	unless ( ($curr_finservice eq 'private finisher') || ($curr_finservice eq 'robotic finishing') ){
	    my $fix_state = "
select ID_TEAM_FINSERV 
from team_finservice 
where FSNAME = \'$curr_finservice\' 
order by ID_TEAM_FINSERV asc
";
	    my $sthfix = $dbh->prepare($fix_state) || die "ERROR:$DBI::errstr";
	    $sthfix->execute || die "ERROR:$DBI::errstr";
	    my $NEW_id_team_finserve = $sthfix->fetchrow();
	    $sthfix->finish();
	    $id_team_finserve = $NEW_id_team_finserve;
	}

	my $possible = "$curr_finservice" . "_id_" . "$id_team_finserve";
	push @possibles, $possible;
    }
    $sth1->finish;

    return (@possibles);
}

sub new_session_value {
    my ($pkg, $dbh) = @_;
    my $statement_id = "select seq_session.nextval from dual";
    #warn "$statement_id\n";
    my $sth_id = $dbh->prepare($statement_id) || die "ERROR:$DBI::errstr";
    $sth_id->execute || die "ERROR:$DBI::errstr";
    my $session_id = $sth_id->fetchrow();
    $sth_id->finish;
    return($session_id);
}

sub blocks {
    my ($pkg, $dbh, $request_view, $id_role, $ID_TEAM_FINSERV, $primersort) = @_;
    #warn "request_view $request_view id_role $id_role ID_TEAM_FINSERV $ID_TEAM_FINSERV primersort $primersort\n";

    my @datacollector;
    my ($statement1, $statement2, $statement3, $order_by);

    $statement1 = "
select b.projectname, e.email, c.protocol, decode(a.id_runtype, NULL,'NULL',a.id_runtype), a.id_frequest, a.id_primer
from finish_request a, finish_batch b, seqchem c, team_person_role d, person e
where 
a.id_fbatch = b.id_fbatch
and a.id_seqchem  = c.id_seqchem
and d.id_person = e.id_person
and d.id_role = b.finisher
and a.id_frequest >= 680523
";

    if ($request_view eq 'private finisher'){
	$statement2 = "and b.finisher = \'$id_role\'";
    } else { 
	$statement2 = "";
    }
    
    $statement3 = "
and b.id_team_finserv = \'$ID_TEAM_FINSERV\' 
and exists (
	select 1
	from frequest_status f
	where f.iscurrent = 1
	and f.status = 1		
	and a.id_frequest = f.id_frequest)
and 
     (a.id_primer is NULL or exists (
                                     select 1
                                     from primer_status g
                                     where g.iscurrent = 1
                                     and g.status = 4
                                     and a.id_primer = g.id_primer))
";

    if ($primersort eq 'yes'){
	$order_by = "order by 2, 1, 4, 3, a.id_primer, a.platename, a.rowletter, a.colnum, a.iteration";
    } else {
	$order_by = "order by 2, 1, 4, 3, a.platename, a.rowletter, a.colnum, a.iteration";
    }

    my $statement = "$statement1"."$statement2"."$statement3"." $order_by";
    #warn "$statement\n";

    my ($l_projectname, $l_email, $l_protocol, $l_id_runtype, $l_id_frequest, $l_id_primer) = 'first';
    my $count = 0;
    my $iter;
    my $title;
    my @requests;
    my $sth_id = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth_id->execute || die "ERROR:$DBI::errstr";
    my ($projectname, $email, $protocol, $id_runtype, $id_frequest, $id_primer);
    while (($projectname, $email, $protocol, $id_runtype, $id_frequest, $id_primer)= $sth_id->fetchrow()){
	#warn "$projectname, $email, $protocol, $id_runtype, $id_frequest, $id_primer\n";

	my @info;

	if ($count == 0){
	    $l_projectname= $projectname;
	    $l_email = $email;
	    $l_protocol = $protocol;
	    $l_id_runtype = $id_runtype;
	    $l_id_frequest = $id_frequest;
	    $l_id_primer = $id_primer;
	    
	    @info = ("$id_frequest");
	    $requests[$count] = [@info];
	    $count++;

	} elsif  (($id_runtype ne $l_id_runtype) || ($protocol ne $l_protocol) || ($email ne $l_email) || ($projectname ne $l_projectname) ){
	    $title = "$l_projectname"."__"."$l_email"."__"."$count"."__"."$l_protocol"."$l_id_runtype";
	    $iter = {
		"block" => "$title",
		"contents" => [@requests]
		};
	    push @datacollector, $iter;
	    @requests = ();
	    $count = 1;

	    @info = ("$id_frequest");
	    $requests[$count-1] = [@info];

	    $l_projectname= $projectname;
	    $l_email = $email;
	    $l_protocol = $protocol;
	    $l_id_runtype = $id_runtype;
	    $l_id_frequest = $id_frequest;
	    $l_id_primer = $id_primer;
	    
	} else {

	    @info = ("$id_frequest");
	    $requests[$count] = [@info];
	    $count++;

	}
    }
    $sth_id->finish;
    
    $title = "$l_projectname"."__"."$l_email"."__"."$count"."__"."$l_protocol"."$l_id_runtype";
    $iter = {
	    "block" => "$title",
	    "contents" => [@requests]
	    };
    push @datacollector, $iter;

    #my $capture = Data::Dumper::Dumper(@datacollector);
    #warn $capture;

    return ($count, \@datacollector);
}

sub maps {
    my ($pkg, $dbh, $teams, $mode) = @_;
    my @local_teams = @$teams;

    my $num = scalar(@local_teams);
    my $c = 1;
    foreach my $n (@local_teams){
	unless ($c == $num){
	    $n = "$n" . ", ";
	}
	$c++;
    }

    my @possible_maps;
    my ($statement, $statement1, $statement2, $statement3);
    $statement1 = "
select distinct a.teamname, a.id_team_finserv, b.id_mapplate, b.protocol, decode(b.id_runtype, NULL,'NULL',b.id_runtype), b.plate_format, count(d.id_frequest)
from team_finservice a, mapplate b, mapplate_status c, request_map d
where a.id_team_finserv = b.id_team_finserv
and b.id_mapplate = c.id_mapplate
and b.id_mapplate = d.id_mapplate
and b.id_mapplate > 89307
and a.teamname in (@local_teams)
";

    if ($mode eq 'team_loading'){
	$statement2 = "
and (c.status = 9 or c.status = 3)
";
    } else {
	$statement2 = "
and (c.status = 2 or c.status = 6)
";
    }

    $statement3 = "
and c.iscurrent = 1
and d.id_loadingsample is NULL
group by a.teamname, a.id_team_finserv, b.id_mapplate, b.protocol, decode(b.id_runtype, NULL,'NULL',b.id_runtype), b.plate_format
order by a.teamname, a.id_team_finserv, b.id_mapplate desc
";
    
    $statement = "$statement1" . "$statement2" . "$statement3";

    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my ($TEAM, $ID_TEAM_FINSERV, $ID_MAPPLATE, $PROTOCOL, $ID_RUNTYPE, $PLATE_FORMAT, $COUNT);
    while ( ($TEAM, $ID_TEAM_FINSERV, $ID_MAPPLATE, $PROTOCOL, $ID_RUNTYPE, $PLATE_FORMAT, $COUNT) = $sth->fetchrow() ){
	my $return = "$ID_MAPPLATE"."_"."$PROTOCOL"."$ID_RUNTYPE"."_"."$COUNT";
	push @possible_maps, $return;
    }
    $sth->finish();
    return (@possible_maps);
}

sub map_info {
    my ($pkg, $dbh, $plate, $samples, $sample_data, $gel_lane) = @_;
    my %sample_data;
    my @samples;
    my $statement = "
select a.id_mapplate, b.id_map, b.maprow, b.mapcol, a.id_frequest, c.platename, c.rowletter, c.colnum, d.suffix, c.iteration, e.primername
from request_map a, rowcol_map b, finish_request c, seqchem d, primer e
where a.id_map = b.id_map
and a.id_frequest = c.id_frequest
and c.id_seqchem = d.id_seqchem
and c.id_primer = e.id_primer(+)
and a.id_mapplate = $plate
and a.id_loadingsample is NULL
order by a.id_map
";
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my ($id_mapplate, $id_map, $maprow, $mapcol, $id_frequest, $platename, $rowletter, $colnum, $suffix, $iteration, $primername);
    while (($id_mapplate, $id_map, $maprow, $mapcol, $id_frequest, $platename, $rowletter, $colnum, $suffix, $iteration, $primername) = $sth->fetchrow()){
	my $entry1 = "lane " . "$$gel_lane     " . "$platename" . "$rowletter" . "$colnum" . "$suffix" . "$iteration" . "$primername";
	push @$samples, $entry1;

	my $entry2 = "$maprow"."$mapcol"."#$id_map#"."_plate"."$plate"."#$id_frequest#";
	$sample_data->{$$gel_lane} = $entry2;

	$$gel_lane++;

    }
    $sth->finish();
    
    return($samples, $sample_data);
}

sub runs {
    my ($pkg, $dbh, $seqservice, $machines) = @_;
    my @data;
    my %hash;
    my @machines = @$machines;

    my $statement = "
select a.id_seqmachine, b.id_gel
from seqmachine a, gel_queue b, gel c, gel_status d
where a.id_seqmachine = b.id_seqmachine
and b.id_gel = c.id_gel
and c.id_gel = d.id_gel
and a.isactive = 1
and a.id_seqservice = $seqservice
and d.status = 10
and d.iscurrent = 1
order by a.id_seqmachine asc, b.id_gel asc
";
    
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my ($id_seqmachine, $id_gel);
    while ( ($id_seqmachine, $id_gel) = $sth->fetchrow() ){
	unless (exists $hash{$id_seqmachine}){
	    $hash{$id_seqmachine} = ();
	}
	push @{$hash{$id_seqmachine}}, $id_gel;
    }
    $sth->finish();

    foreach my $next (@machines){
	unless (exists $hash{$next}){
	    $hash{$next} = ();
	}
    }

    foreach my $next2 (@machines){
	my @array = $hash{$next2};
	push @data, @array ;
    }

    return(@data);

}

1;
