#
#######################################################################
# 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: rr2_general.pm,v 1.20 2004/02/17 16:19:34 kva Exp $
#
# Author: KVA
###################################
# Request removal library (db info)
###################################

use strict;

package get_info;

sub check_teamleader {
    
    my ($pkg, $dbh, $id_person, $teamname) = @_;
    
    my $stat = "select 'yes' 
from team_person_role
where id_person = ?
and roletype = 'Finishing leader'
and teamname = ?
and iscurrent = 1";

    my $sth_1 = $dbh->prepare($stat);
    $sth_1->bind_param(1, $id_person);
    $sth_1->bind_param(2, $teamname);
    $sth_1->execute || die "ERROR:$DBI::errstr";
    my $answer = $sth_1->fetchrow();
    $sth_1->finish();
    return $answer;
    
}


package entity_get;

#############################################
# Displays the relevant ID's that relate to #
# each entity type                          #
#############################################
  
sub finish_batch_entities {

    my ($pkg, $dbh, $id_role, $teamname, $answer, $cb_value) = @_;
    
    my ($id_fbatch, $batchdate, $projectname, $fname, $description,$finisher);
    my @fbe;
    my @array_arrays;
    my $count;
    my ($statement_1,$statement_2,$statement_3,$statement_4);

    $statement_1 = "select distinct a.F_BATCH, RPAD(a.BATCHDATE,12), RPAD(a.PROJ,14), RPAD(a.FSNAME,30), a.PNAME
from (
select fb.id_fbatch F_BATCH, 
fb.batchdate BATCHDATE, 
fb.projectname PROJ, 
ff.fsname FSNAME,
p.email PNAME
from finish_batch fb, 
fin_team_finservice ftf,
fbatch_status fbs,
fin_finservice ff,
team_person_role tpr, 
person p
where fb.id_fbatch = fbs.id_fbatch
and fb.id_team_finserv= ftf.id_team_finserv
and ftf.id_team_finserv = ff.id_team_finserv
and fb.finisher = tpr.id_role 
and tpr.id_person = p.id_person ";
    
    $statement_2 = "and fb.finisher = ? ";
    $statement_3 = "and ftf.teamname = ? ";
    
    $statement_4 = "and fbs.status = 1
and fbs.iscurrent = 1)a
WHERE not exists (SELECT /*+ INDEX(fres) */finr.id_fbatch
from finish_request finr, frequest_status fres
where finr.id_fbatch = a.F_BATCH
and finr.id_frequest = fres.id_frequest
and fres.status = 3
and fres.iscurrent = 1
)
order by a.F_BATCH";
    
   
# select /*+ INDEX(fb) INDEX(f)*/ fb.id_fbatch, RPAD(fb.batchdate,12),RPAD(fb.projectname,14), RPAD(f.fsname,30)
# from finish_batch fb, fin_finservice f
# where fb.id_team_finserv=f.id_team_finserv
# and fb.id_fbatch in (select /*+ INDEX(a) INDEX(b) INDEX(e)*/ distinct a.id_fbatch
# from finish_batch a, fbatch_status b, fin_team_finservice e
# where a.id_fbatch=b.id_fbatch
# and a.id_team_finserv = e.id_team_finserv
# and b.status=1
# and b.iscurrent=1
# and b.id_fbatch not in (select id_fbatch from finish_request where id_frequest in (select id_frequest from frequest_status where status = 3 and iscurrent = 1))
# and a.finisher = ?
# and e.teamname = ?)
# order by fb.id_fbatch
#     
# my ($extstat_1,$extstat_2,$extstat_3);
#
# if (($answer eq 'yes') && ($cb_value == 1)){
#     $extstat_1 = ",p.email ";
#     $extstat_2 = ",team_person_role tpr, person p  ";
#     $extstat_3 = "and fb.finisher = tpr.id_role and tpr.id_person = p.id_person ";
# }
    
    my $statement;
    if (($answer eq 'yes') && ($cb_value == 1)){
        $statement =$statement_1.$statement_3.$statement_4;   
    } else {
        $statement =$statement_1.$statement_2.$statement_3.$statement_4;
    }
    
#    print "STATEMENT IN USE :- $statement\n";
    
    my $sth_1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    
    if (($answer eq 'yes') && ($cb_value == 1)){
        $sth_1->bind_param(1, $teamname);
    } else {
        $sth_1->bind_param(1, $id_role);
        $sth_1->bind_param(2, $teamname);
    }
    
    $sth_1->execute || die "ERROR:$DBI::errstr";
    while (($id_fbatch, $batchdate, $projectname, $fname, $finisher) = $sth_1->fetchrow()){
        if (($answer eq 'yes') && ($cb_value == 1)){
            my $fbe_entity = sprintf "%-12s %-12s %-16s %-30s %-12s" ,"$id_fbatch","$batchdate","$projectname","$fname","$finisher";
            push @fbe, $fbe_entity;
            $count++;
        } else {
            my $fbe_entity = sprintf "%-12s %-12s %-16s %-30s" ,"$id_fbatch","$batchdate","$projectname","$fname";
            push @fbe, $fbe_entity;
            $count++;
        }
        
    }
    $sth_1->finish();
    return ($count,\@fbe);
    
}


sub finish_request_entities {
    
    my ($pkg, $dbh, $id_role, $teamname, $answer, $cb_value) = @_;
    
    my ($id_frequest, $id_fbatch, $platename, $fsname, $finisher);
    my @fre;
    my @array_arrays;
    my $count;   
    
    my ($statement_1,$statement_2,$statement_3,$statement_4,$statement_5,$statement_6,$statement_7);
    
    $statement_1 = "select /*+ INDEX(a) INDEX(b) INDEX(c) */ distinct b.id_frequest, b.id_fbatch, b.platename, f.fsname ";
    
    $statement_2 = "from finish_batch a, finish_request b, frequest_status c, fin_finservice f, fin_team_finservice g ";
    
    $statement_3 = "where a.id_fbatch=b.id_fbatch 
and b.id_frequest=c.id_frequest 
and a.id_team_finserv= f.id_team_finserv 
and f.id_team_finserv= g.id_team_finserv
and c.iscurrent = 1 
and c.status = 1 ";
    
    $statement_4 = "and a.finisher = ? ";
    $statement_5 = "and g.teamname = ? ";
    $statement_6 = "order by b.id_frequest,b.id_fbatch";
    
    my($extstat_1,$extstat_2,$extstat_3);
    
    if (($answer eq 'yes') && ($cb_value == 1)){
	$extstat_1 = ",i.email ";
	$extstat_2 = ",team_person_role h, person i ";
	$extstat_3 = "and a.finisher = h.id_role and h.id_person = i.id_person ";
    }
    
    my $statement;
    if (($answer eq 'yes') && ($cb_value == 1)){
        $statement =$statement_1.$extstat_1.$statement_2.$extstat_2.$statement_3.$extstat_3.$statement_5.$statement_6;   
    } else {
        $statement =$statement_1.$statement_2.$statement_3.$statement_4.$statement_5.$statement_6;
    }
    
    
    my $sth_1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    if (($answer eq 'yes') && ($cb_value == 1)){
        $sth_1->bind_param(1, $teamname);
        $sth_1->execute || die "ERROR:$DBI::errstr";
        while (($id_frequest, $id_fbatch, $platename, $fsname, $finisher) = $sth_1->fetchrow()){
            my $fre_entity = sprintf "%-12s %-12s %-16s %-30s %-12s" ,"$id_frequest","$id_fbatch","$platename","$fsname","$finisher";
	    push @fre, $fre_entity;
            $count++;
        }
	
    } else {
	
	$sth_1->bind_param(1, $id_role);
	$sth_1->bind_param(2, $teamname);
	$sth_1->execute || die "ERROR:$DBI::errstr";
	while (($id_frequest, $id_fbatch, $platename, $fsname) = $sth_1->fetchrow()){
	    my $fre_entity = sprintf "%-12s %-12s %-16s %-30s" ,"$id_frequest","$id_fbatch","$platename","$fsname";
	    push @fre, $fre_entity;
	    $count++;
	}
    }
    $sth_1->finish();
    return ($count,\@fre);
    
}

sub mapplate_entities {

    my ($pkg, $dbh, $id_role) = @_;
    
    my ($id_mapplate, $plate_format, $id_team_finserv, $date, $fsname, $description, $operator);
    my @map;
    my @array_arrays;
    my $count;

my $statement_1 = "
select distinct a.id_mapplate, a.plate_format, a.id_team_finserv, b.statusdate, d.fsname, c.description, b.operator 
from mapplate a, mapplate_status b, mapplatestatusdict c,  fin_finservice d, fin_team_finservice ftf, team_person_role e
where a.id_mapplate = b.id_mapplate and
b.status =  c.id_dict and
a.id_team_finserv = d.id_team_finserv and
d.id_team_finserv = ftf.id_team_finserv and
ftf.teamname = e.teamname and
b.iscurrent = 1 and
b.status in (1,2,5,6,7,9,10,11) and
e.id_role = ?
order by a.id_team_finserv, a.id_mapplate
";

    
    my $sth_1 = $dbh->prepare($statement_1) || die "ERROR:$DBI::errstr";
    $sth_1->bind_param(1, $id_role);
    $sth_1->execute || die "ERROR:$DBI::errstr";
    while (($id_mapplate, $plate_format, $id_team_finserv, $date, $fsname, $description, $operator) = $sth_1->fetchrow()){
        my $map_entity = sprintf "%-10s %-1s %-4s %-1s %-9s %-1s %-4s %-1s %-15s %-1s %-4s" ,"$id_mapplate"," ","$plate_format"," ","$date"," ","$fsname"," ","$description"," ","$operator";
        push @map, $map_entity;
        $count++;
    }
    $sth_1->finish();
    return ($count,\@map);
}


sub id_gel_entities {

    my ($pkg, $dbh, $id_role) = @_;
    
    my ($id_gel ,$description ,$statusdate, $fsname);
    my @id_gel;
    my @array_arrays;
    my $count;

my $statement_1 = "
select /*+ INDEX(b) INDEX(d) INDEX(e) INDEX(i) */ distinct a.id_gel, b.description, a.statusdate, h.fsname
from gel_status a, gelstatusdict b, gel_sample c, request_map d, mapplate e, fin_team_finservice g, fin_finservice h, team_person_role i
where a.status = b.id_dict 
and a.id_gel = c.id_gel
and c.id_loadingsample = d.id_loadingsample 
and d.id_mapplate = e.id_mapplate
and e.id_team_finserv = g.id_team_finserv 
and g.id_team_finserv = h.id_team_finserv
and g.teamname = i.teamname
and i.id_role = ?
and (a.id_gel, a.statusdate,b.description) in (select /*+ INDEX(gs) */ gs.id_gel, gs.statusdate, gsd.description
from gel_status gs, gelstatusdict gsd 
where gs.status = gsd.id_dict
and gs.status = 10 
and gs.iscurrent = 1)
order by a.id_gel
";

    my $sth_1 = $dbh->prepare($statement_1) || die "ERROR:$DBI::errstr";
    $sth_1->bind_param(1, $id_role);
    $sth_1->execute || die "ERROR:$DBI::errstr";
    while (($id_gel ,$description ,$statusdate, $fsname) = $sth_1->fetchrow()){
        my $id_gel_entity = sprintf "%-10s %-3s %-15s %-3s %-9s %-3s %-15s" ,"$id_gel"," ","$description"," ","$statusdate"," ","$fsname";
        push @id_gel, $id_gel_entity;
        $count++;
    }
    $sth_1->finish();
    return ($count,\@id_gel);
}


package entity_id_check;

#######################################
# Checks to see if the id's submitted #
# for cancellation are valid id's     #
#######################################

sub check_id {
    my ($pkg, $dbh, $entity, $nb)=@_;
    
    my @all; 
    my @id;
    my %HASH=();
    my $statement_1;
 
    #splits each selected request on spaces and then proceeds to 
    #extract the request id from each one and depending on the 
    #entity type, checks to see if the valid is valid.  
 
    if (defined @{$nb}){
        foreach (@{$nb}){
            @all= split(" ", $_);
            my $id=$all[0];
            push @id, $id;
        }

        foreach (@id){

	    # we are only checking whether the entity has been deleted here.
	    # the entity in question could have moved onto a new (and now invalid
	    # for cancelling) status.

            if ($entity=~ /Finish_Batch/){
		$statement_1 = "select distinct a.id_fbatch 
                  from finish_batch a, fbatch_status b, finish_request c, frequest_status d
		  where a.id_fbatch = b.id_fbatch
                  and a.id_fbatch = c.id_fbatch
                  and c.id_frequest = d.id_frequest
                  and d.status = 1
                  and d.iscurrent = 1
		  and a.id_fbatch = ?
		  and b.status = 1
		  and b.iscurrent = 1";

            }  
            
            elsif ($entity=~ /Finish_Request/){
                $statement_1 = "select a.id_frequest 
		 from finish_request a, frequest_status b
		 where a.id_frequest = b.id_frequest
		 and a.id_frequest = ?
		 and b.status = 1
		 and b.iscurrent = 1";
            }
            
            elsif ($entity=~ /Mapplate/){
                $statement_1 = "select a.id_mapplate 
		from mapplate a, mapplate_status b 
		where a.id_mapplate = b.id_mapplate
		and a.id_mapplate = ?
		and b.status in (1,2,5,6,7,9,10,11)
		and b.iscurrent = 1";
            }
            
            elsif ($entity=~ /ID_Gel/){
                $statement_1 = "select a.id_gel 
		 from gel a, gel_status b
		 where a.id_gel = b.id_gel
		 and a.id_gel = ?
		 and b.status = 10
		 and b.iscurrent = 1";
            }
            
            else{
                print "There is no provision for this at the moment\n";
            }
            
            my $sth_1 = $dbh->prepare($statement_1) || die "ERROR:$DBI::errstr";
            $sth_1->bind_param(1, $_);
            $sth_1->execute || die "ERROR:$DBI::errstr";
            my $id= $sth_1->fetchrow();    
            $sth_1->finish();
            if (defined $id){
                $HASH{$id}="1";
            } else {
                $HASH{$_}="0";
            }
            
        }
        return (\%HASH);       
        
    } else {
        return (\%HASH);
    }
    
} 

package show;

###########################################
# Displays the id's for the entity choice #
# That were found to be invalid id's in   #
# the database.                           #
###########################################

sub not_deleted {

my ($pkg, $mw, $label_f, $button_f, $entity, $nve)=@_;

my $win = $mw->Toplevel;
$win->focusForce;
$win->grab;
$win->configure (-title => "ID's from $entity that couldn't be removed");
my $frame = $win->Frame();
my $scrllist = $frame->ScrlListbox (
                                    -bg => 'lemonchiffon1',
                                    -scrollbars => 'e',
                                    -font => $label_f,
                                    -height => 5,
                                    -width => 70
                                    );
$scrllist->pack(-side => 'left', -expand => 1, -fill => 'both');
$scrllist->insert('end', @$nve);
my $but=$win->Button(-font => $button_f, -text => 'Exit',-command => sub { $win->destroy(); });
$but->pack(-side => 'bottom');
$frame->pack;

return;

}


package remove;

#########################################
# Update and inserts new status entries #
# for the valid id's.                   #
#########################################

sub to_be_removed {
    
    my ($pkg, $dbh, $who, $entity, $session_id, $program, $update_screen, $to_be_deleted) = @_;
    my $sth;
    my ($sth_ifb, $sth_ufb, $sth_ifr, $sth_ufr, $sth_ump, $sth_imp, $sth_ug, $sth_ig);
    

    if ($entity=~ /Finish_Batch/){
#statement1
        my $stat_ufb = "update fbatch_status set iscurrent = 0 where iscurrent = 1 and ID_FBATCH = ?";
        $sth_ufb = $dbh->prepare($stat_ufb) || die "ERROR:$DBI::errstr";

       
#statement2
        my $stat_ifb = "insert into fbatch_status (ID_FBATCH, STATUS, STATUSDATE,ISCURRENT, SESSIONID, PROGRAM, OPERATOR) values (?, 2, sysdate, 1, ?, ?, ?)";
        $sth_ifb = $dbh->prepare($stat_ifb) || die "ERROR:$DBI::errstr";
        
#statement3
        my $stat_ufr = "update frequest_status set iscurrent = 0 where iscurrent = 1 and ID_FREQUEST in (select a.id_frequest from finish_request a, frequest_status b where a.id_frequest=b.id_frequest and a.ID_FBATCH = ? and b.status=1 and b.iscurrent=1)";
        $sth_ufr = $dbh->prepare($stat_ufr) || die "ERROR:$DBI::errstr";
        
#statement4
        my $stat_ifr = "insert into frequest_status (ID_FREQUEST, STATUS, STATUSDATE, ISCURRENT, SESSIONID, PROGRAM, OPERATOR) values (?, 2, sysdate, 1, ?, ?, ?)";
        $sth_ifr = $dbh->prepare($stat_ifr) || die "ERROR:$DBI::errstr";
         
        my($count1, $count2);    
   
        foreach my $tbd (@{$to_be_deleted}){
            
            $sth_ufb->bind_param(1, $tbd);
            $sth_ufb->execute || die "ERROR:$DBI::errstr";
            
            $sth_ifb->bind_param(1, $tbd);
            $sth_ifb->bind_param(2, $session_id);
            $sth_ifb->bind_param(3, $program);
            $sth_ifb->bind_param(4, $who);
            $sth_ifb->execute || die "ERROR:$DBI::errstr";
            
            $count2++;
            &update($update_screen, "#$count2 - Updating changes for id_fbatch $tbd to the database for $entity.");
            $$update_screen->update('idletasks');
            
            my $statement = "select a.id_frequest from finish_request a, frequest_status b where a.id_frequest=b.id_frequest and a.ID_FBATCH = ? and b.status = 1 and b.iscurrent=1";
            my $all_requestsref = $dbh->selectall_arrayref($statement, {}, $tbd) || die "ERROR:$DBI::errstr";

            $count1=0;

            $sth_ufr->bind_param(1, $tbd);
            $sth_ufr->execute || die "ERROR:$DBI::errstr";

            foreach my $arref (@$all_requestsref){
                $sth_ifr->bind_param(1, @$arref);
                $sth_ifr->bind_param(2, $session_id);
                $sth_ifr->bind_param(3, $program);
                $sth_ifr->bind_param(4, $who);
                $sth_ifr->execute || die "ERROR:$DBI::errstr";
                
                $count1++;
                &update($update_screen, "#$count1 - Updating changes for finish_request @$arref to the database for $entity $tbd");
                $$update_screen->update('idletasks');
            }
   
        }
        
        $sth_ufb->finish();
        $sth_ifb->finish();
        $sth_ufr->finish();
        $sth_ifr->finish();
    }   
    



    elsif ($entity=~ /Finish_Request/){

        my $count3;
#statement1
        my $stat_ufr = "update frequest_status set iscurrent = 0 where iscurrent = 1 and ID_FREQUEST = ?";
        $sth_ufr = $dbh->prepare($stat_ufr) || die "ERROR:$DBI::errstr";
        
#statement2
        my $stat_ifr = "insert into frequest_status (ID_FREQUEST, STATUS, STATUSDATE, ISCURRENT, SESSIONID, PROGRAM, OPERATOR) values (?, 2, sysdate, 1, ?, ?, ?) ";
        $sth_ifr = $dbh->prepare($stat_ifr) || die "ERROR:$DBI::errstr";
        
        foreach my $tbd (@{$to_be_deleted}){
            
            $sth_ufr->bind_param(1, $tbd);
            $sth_ufr->execute || die "ERROR:$DBI::errstr";
            
            $sth_ifr->bind_param(1, $tbd);
            $sth_ifr->bind_param(2, $session_id);
            $sth_ifr->bind_param(3, $program);
            $sth_ifr->bind_param(4, $who);
            $sth_ifr->execute || die "ERROR:$DBI::errstr";

            $count3++;
            &update($update_screen, "#$count3 - Updating changes for finish_request $tbd to the database for $entity.");
            $$update_screen->update('idletasks');
            
        }
        
        $sth_ufr->finish();
        $sth_ifr->finish();
    }       
    


    elsif ($entity=~ /Mapplate/){

        my $count4;        
#statement1
        my $stat_ump = "update mapplate_status set iscurrent = 0 where iscurrent = 1 and ID_MAPPLATE = ?";
        $sth_ump = $dbh->prepare($stat_ump) || die "ERROR:$DBI::errstr";
        
#statement2
        my $stat_imp = "insert into mapplate_status (ID_MAPPLATE, STATUS, STATUSDATE, ISCURRENT, SESSIONID, PROGRAM, OPERATOR) values (?, 8, sysdate, 1, ?, ?, ?) ";
        $sth_imp = $dbh->prepare($stat_imp) || die "ERROR:$DBI::errstr";
        
        foreach my $tbd (@{$to_be_deleted}){
            
            $sth_ump->bind_param(1, $tbd);
            $sth_ump->execute || die "ERROR:$DBI::errstr";
            
            $sth_imp->bind_param(1, $tbd);
            $sth_imp->bind_param(2, $session_id);
            $sth_imp->bind_param(3, $program);
            $sth_imp->bind_param(4, $who);
            $sth_imp->execute || die "ERROR:$DBI::errstr";

            $count4++;
            &update($update_screen, "#$count4 - Updating changes for id_mapplate $tbd to the database for $entity.");
            $$update_screen->update('idletasks');  
        }
        
        $sth_ump->finish();
        $sth_imp->finish();
    } 
    


    elsif ($entity=~ /ID_Gel/){

        my $count5;
#statement1
        my $stat_ug = "update gel_status set iscurrent = 0 where iscurrent = 1 and ID_GEL = ?";
        $sth_ug = $dbh->prepare($stat_ug) || die "ERROR:$DBI::errstr";
        
#statement2
        my $stat_ig = "insert into gel_status (ID_GEL, STATUS, ISCURRENT, STATUSDATE, SESSIONID, PROGRAM, OPERATOR) values (?, 101, 1, sysdate, ?, ?, ?) ";
        $sth_ig = $dbh->prepare($stat_ig) || die "ERROR:$DBI::errstr";
        
        foreach my $tbd (@{$to_be_deleted}){
            
            $sth_ug->bind_param(1, $tbd);
            $sth_ug->execute || die "ERROR:$DBI::errstr";
            
            $sth_ig->bind_param(1, $tbd);
            $sth_ig->bind_param(2, $session_id);
            $sth_ig->bind_param(3, $program);
            $sth_ig->bind_param(4, $who);
            $sth_ig->execute || die "ERROR:$DBI::errstr";

            $count5++;
            &update($update_screen, "#$count5 - Updating changes for id_gel $tbd to the database for $entity.");
            $$update_screen->update('idletasks');    
        } 
       
        $sth_ug->finish();
        $sth_ig->finish();
    }   
    
    else{
        
        print "NO SQL PROVISIONS.\n";
        die;
    }    
    
}

sub update {
    my ($update_screen, @update_contents)=@_;
    
#Fill the list in the main window with update-information
    $$update_screen->insert('end', @update_contents);
    $$update_screen->see('end'); 
    $$update_screen->pack(-side => 'left', -expand => 1, -fill => 'both');
}



1;
    
    

