

package dis_plate;
use strict;

sub dis_plate{

    ##### this is in use by web form for team to disregard plates of dodgy results


    ##  platename  on/off  value type
    my ($plate, $bin, $qual, $type) = @_;
    
    my $count = 0;
    my $dbi_user = "snp";
    
    my $dbh;
    
    eval {
	
	$dbh = WrapDBI->connect($dbi_user, {RaiseError => 1});
	$dbh->{autocommit} = 0;
	
	my $dis_up_sth = $dbh->prepare(qq[update WELL_RESULT
					  set DISREGARD = ?
					  where ID_RESULT = ?
					  ]);

	my $geno_up_sth = $dbh->prepare(qq[update GENOTYPE 
					    set DISREGARD =?
					    where ID_RESULT=?
					    ]);
	
	my $well_ext_sth;
	
	if(defined $qual && $type eq "exp_num"){

	    $well_ext_sth = $dbh->prepare(qq[select 
					     WELL_RESULT.ID_RESULT
					     from 
					     SPLATE_WELL, 
					     SEQUENOM_PLATE, 
					     WELL_RESULT
					     where  
					     WELL_RESULT.EXP_NUM = ?
					     and WELL_RESULT.ID_SPW = SPLATE_WELL.ID_SPW
					     and SPLATE_WELL.ID_SPLATE = SEQUENOM_PLATE.ID_SPLATE
					     and SEQUENOM_PLATE.PLATE_NAME = ?
					     ]);
	    
	    
	    $well_ext_sth->execute($qual, $plate ) || die "ERROR:$DBI::errstr";



	}
	elsif(defined $qual && $type eq "dna"){

	    $well_ext_sth = $dbh->prepare(qq[select 
					     WELL_RESULT.ID_RESULT
					     from 
					     SPLATE_WELL, 
					     SEQUENOM_PLATE, 
					     WELL_RESULT,
					     INDIVIDUAL
					     where  
					     SEQUENOM_PLATE.PLATE_NAME = ?
					     and SPLATE_WELL.ID_SPLATE = SEQUENOM_PLATE.ID_SPLATE
					     and WELL_RESULT.ID_SPW = SPLATE_WELL.ID_SPW
					     and SPLATE_WELL.ID_IND = INDIVIDUAL.ID_IND 
					     and INDIVIDUAL.CLONENAME = ?
					     ]);
	    
	    
	    $well_ext_sth->execute($plate, $qual ) || die "ERROR:$DBI::errstr";



	}

	elsif(defined $qual && $type eq "quad"){
	    ############ 20031002 new option
	    $well_ext_sth = $dbh->prepare(qq[select 
					     WELL_RESULT.ID_RESULT
					     from 
					     SPLATE_WELL, 
					     SEQUENOM_PLATE, 
					     WELL_RESULT
					     where  
					     SPLATE_WELL.ID_WELL = ?
					     and WELL_RESULT.ID_SPW = SPLATE_WELL.ID_SPW
					     and SPLATE_WELL.ID_SPLATE = SEQUENOM_PLATE.ID_SPLATE
					     and SEQUENOM_PLATE.PLATE_NAME = ?
					     ]);
	    
	    
	    $well_ext_sth->execute($qual, $plate ) || die "ERROR:$DBI::errstr";



	}
	elsif(defined $qual && $type eq "snp"){
	    ################# new option 20040810
	    
	    $well_ext_sth = $dbh->prepare(qq[select 
					     WELL_RESULT.ID_RESULT
					     from
					     SEQUENOM_PLATE,
					     SPLATE_WELL,
					     WELL_RESULT,
					     WELL_ASSAY, 
					     SNPASSAY_SNP,
					     SNP_NAME
					     where  
					     SEQUENOM_PLATE.PLATE_NAME = ?
					     and SPLATE_WELL.ID_SPLATE = SEQUENOM_PLATE.ID_SPLATE
					     and WELL_RESULT.ID_SPW = SPLATE_WELL.ID_SPW
					     and SPLATE_WELL.ID_WELL = WELL_ASSAY.ID_WELL
					     and WELL_ASSAY.ID_ASSAY = SNPASSAY_SNP.ID_ASSAY
					     and SNPASSAY_SNP.ID_SNP = SNP_NAME.ID_SNP
					     and SNP_NAME.SNP_NAME =?
					     ]);
	    
	    
	    $well_ext_sth->execute($plate, $qual ) || die "ERROR:$DBI::errstr";



	}

	elsif($qual){
	    ### delete only for set date
	    $well_ext_sth = $dbh->prepare(qq[select 
					     WELL_RESULT.ID_RESULT
					     from 
					     SPLATE_WELL, 
					     SEQUENOM_PLATE, 
					     WELL_RESULT
					     where  
					     WELL_RESULT.CALL_DATE = ?
					     and WELL_RESULT.ID_SPW = SPLATE_WELL.ID_SPW
					     and SPLATE_WELL.ID_SPLATE = SEQUENOM_PLATE.ID_SPLATE
					     and SEQUENOM_PLATE.PLATE_NAME = ?
					     ]);
	    
	    
	    $well_ext_sth->execute($qual, $plate ) || die "ERROR:$DBI::errstr";
	}
	elsif($plate ){
	    
	    ### delete all plate
	    $well_ext_sth = $dbh->prepare(qq[select 
					     WELL_RESULT.ID_RESULT
					     from 
					     SPLATE_WELL, 
					     SEQUENOM_PLATE, 
					     WELL_RESULT
					     where  
					     SEQUENOM_PLATE.PLATE_NAME = ?
					     and SPLATE_WELL.ID_SPLATE = SEQUENOM_PLATE.ID_SPLATE
					     and WELL_RESULT.ID_SPW = SPLATE_WELL.ID_SPW
					     ]);
	    
	    
	    $well_ext_sth->execute($plate ) || die "ERROR:$DBI::errstr";
	}
	else{
	    return("ERROR : no plate specified");
	}
	my $id_well = $well_ext_sth->fetchall_arrayref();
	
	foreach my $l(@$id_well){
	    $count++;
	    $dis_up_sth->execute($bin, $l->[0]) || die "ERROR:$DBI::errstr";
	    $geno_up_sth->execute($bin, $l->[0]) || die "ERROR:$DBI::errstr";
	}
	
        
	
    };
    
    if ($@) {  
	warn "ERROR : $@\n";
	$dbh->rollback;
	
	if (defined $dbh) {
	    $dbh->disconnect;
	} 
	return($@);

    } 
    else{
	$dbh->commit;
	if (defined $dbh) {
	    $dbh->disconnect;
	} 
	return($count);
    }
    
    

 
}



sub dis_assay{

    ##### this is in use by web form for team to disregard assays of dodgy results


    ##  well  on/off   snp      alias
    my ($well, $bin, $snp, $dbh) = @_;
    
    my $count = 0;
       
    
    eval {

	my $dis_up_sth = $dbh->prepare(qq[update WELL_RESULT
					  set DISREGARD = ?
					  where ID_RESULT = ?
					  ]);
	
	my $geno_up_sth = $dbh->prepare(qq[update GENOTYPE 
					   set DISREGARD =?
					   where ID_RESULT=?
					   ]);
	
	my $well_ext_sth = $dbh->prepare(qq[select 
					    WELL_RESULT.ID_RESULT
					    from 
					    SPLATE_WELL, 
					    WELL_RESULT,
					    RESULT_VAR,
					    SNP_VARIATION,
					     SNP_NAME
					    where  
					    SPLATE_WELL.ID_WELL = ?
					    and WELL_RESULT.ID_SPW   = SPLATE_WELL.ID_SPW
					    and RESULT_VAR.ID_RESULT = WELL_RESULT.ID_RESULT
					    and SNP_VARIATION.ID_VAR = RESULT_VAR.ID_VAR
					    and SNP_VARIATION.ID_SNP = SNP_NAME.ID_SNP
					     and SNP_NAME.SNP_NAME = ?
					    ]);
	
	if( defined $well && defined $snp ){
	    
	    
	    
	    
	    $well_ext_sth->execute($well, $snp ) || die "ERROR:$DBI::errstr";
	    
	    my $id_well = $well_ext_sth->fetchall_arrayref();
	    
	    foreach my $l(@$id_well){
		$count++;
		warn "removing $l->[0]\n";
		$dis_up_sth->execute($bin, $l->[0])  || die "ERROR:$DBI::errstr";
		$geno_up_sth->execute($bin, $l->[0]) || die "ERROR:$DBI::errstr";
	    }
	    
	}
	else{
	    return("ERROR : specify SNP and Well combination");
	} 
	
    };
    
    if ($@) {  
	warn "ERROR : $@\n";
	$dbh->rollback;
	
	
	return($@);

    } 
    else{
	$dbh->commit;
	print "$count assays deleted\n";
	
	return($count);
    }
    
    

 
}





1;


