package TargetsAccessManager; 

use strict;
use warnings;

use DBI;
use Digest::MD5 qw(md5 md5_hex md5_base64);
use DateTime;

use lib qw(Core);

use Database;
use PDBUtils;

use Configuration;
use LocalConfiguration;

use Cwd 'abs_path';


### METHODS (last update 07/04/2012 B. Monastyrskyy):
#   exist (returns 1 if the target with given id exists)
#   get_id_by_name (returns target id for given target name)
#   name (returns target name by its id)
#   force_delay_publishing (sets 1 for publish delay)
#   unforce_delay_publishing (sets 0 for publish delay)
#   generate_targets_file (creates file for target with given id)
#   is_target_files_exist (checks if the target file exist)
#   is_published (returns 1 if the target was published)
#   is_expired (returns 1 if the target is expired)
#   is_expired_QA($id, $stage) (return 1 if the target is expired for stage1 or stage2 
#   is_extended_expired (returns 1 if the target is expired even for extended groups)
#   is_canceled (returns 0 - if target is not cancelled 1 - if target is cancelled for all 
#                2 - if target is cancelled for humans only 
#                3 - if target is cancelled for servers only)
#   add (adds new target to the table)
#   targets (returns all target info with submitted sequences info)
#   public_targets (returns all target info for users view including submitted 
#                   sequences info and structures info)
#   update (changes target data)
#   delete (removes target with given id)
######################################################

my $targets_manager = undef;

sub new {
    my ($class) = @_;
    
    return $targets_manager if(defined($targets_manager));
    
    my $self = {
        _id => undef,
        _database => Database->new($CONFIG->{HOSTNAME}, $CONFIG->{PORT}, $CONFIG->{DATABASE}, $CONFIG->{USERNAME}, $CONFIG->{PASSWORD})
    };
    
    $targets_manager = bless $self, $class;
    return $targets_manager;
}

sub exist {
    my ($self, $target_name) = @_;
    my $result = 0;
    my $query = sprintf("SELECT target_code FROM casp13.targets_access WHERE (target_code = %d)", substr($target_name, 1, 4));
    my $sth = $self->{_database}->query($query);

    if(defined($sth) && ($sth->rows() > 0)) {
        my ($count) = $sth->fetchrow_array();

        $result = ($count == 1) ? 1 : 0;
    }

    return $result;

}

sub get_access_4target{
    my ($self, $target_name) = @_;
    
    my $result = 0;
    
    my $query = sprintf("SELECT t.name FROM casp13.targets WHERE (name = '%s')", $name);
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth) && ($sth->rows() > 0)) {
        ($result) = $sth->fetchrow_array();
    }
    
    return $result;
}

sub name {
    my ($self, $id) = @_;
    
    my $result = '';
    
    my $query = sprintf("SELECT name FROM casp13.targets WHERE (id = %d)", $id);
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth) && ($sth->rows() > 0)) {
        ($result) = $sth->fetchrow_array();
    }
    
    return $result;
}

sub force_delay_publishing {
    my ($self, $id) = @_;
    
    my $result = 0;
    
    return $result if(! $self->exist($id));
    
    my $query = sprintf("UPDATE casp13.targets SET force_delay_publishing = 1 WHERE (id = %d)", $id);
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth)) {
        # add logger
        $result = defined($sth) ? 1 : 0;
    }
    
    return $result;
}

sub unforce_delay_publishing {
    my ($self, $id) = @_;
    
    my $result = 0;
    
    return $result if(! $self->exist($id));
    
    my $query = sprintf("UPDATE casp13.targets SET force_delay_publishing = 0 WHERE (id = %d)", $id);
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth)) {
        # add logger
        $result = defined($sth) ? 1 : 0;
    }
    
    return $result;
}


sub generate_targets_file_multimer {
    my ($self, $id) = @_;

    my $result = 0;

    return $result if(! $self->exist($id));
    my %target_information = $self->info_rawSequence($id);
    my $subunit = $target_information{SUBUNIT};
    if ($target_information{IS_REFINEMENT} ne "1") {
	my @multimer_seqs = splitSequences($target_information{SEQUENCE});
	if (scalar(@multimer_seqs) == 1){
		$subunit = 1;
	} 
	if ($subunit =~ m/^[1-9]$/) {
		my $seq_file = sprintf("%s/%s.seq.txt", $LOCAL_CONFIG->{TARGET_TEMPLATES_DIR} , $target_information{NAME});
		my $pdb_file = sprintf("%s/%s.pdb.txt", $LOCAL_CONFIG->{TARGET_TEMPLATES_DIR} , $target_information{NAME});
		open SEQ, "> $seq_file";
		open PDB, "> $pdb_file";
		my $seq = $multimer_seqs[$subunit-1];
		$seq =~ s/>.*\n+//g;
		my $fasta_header = sprintf ">%s %s, %s, %s%s residues", $target_information{NAME}, $target_information{PROTEIN_NAME}, $target_information{ORGANISM_NAME}, (scalar(@multimer_seqs) == 1 ? '' : sprintf("subunit %d, ", $subunit)),  sequence_length($seq);
		$fasta_header =~ s/|//g; # remove all semicolons
		printf SEQ "%s|\n", $fasta_header;
		printf SEQ "%s%s", $seq, ($seq =~ m/\n$/ ? '' : "\n");
		printf PDB "REMARK Template for target %s\n", $target_information{NAME};
		printf PDB generate_target_template_from_sequence($seq, 'A');
		close SEQ;
		close PDB;
	} elsif ($subunit == 0 ) { # assembly
		#my @LETTERS = qw/A B C D E F G H I J K L M N O P Q R S T U V W X Y Z/;
		my $i_subunit = 1;
		my $seq_file = sprintf("%s/%s.seq.txt", $LOCAL_CONFIG->{TARGET_TEMPLATES_DIR} , $target_information{NAME});
                #my $pdb_file = sprintf("%s/%s.pdb.txt", $LOCAL_CONFIG->{TARGET_TEMPLATES_DIR} , $target_information{NAME});
                open SEQ, "> $seq_file";
                #open PDB, "> $pdb_file";
		#printf PDB "REMARK Template for target %s\n", $target_information{NAME};
		#my @no_chains_per_seq = split(/[A-Z]/, $target_information{STOICHIOMETRY}); # A2B1C1
		for my $seq (@multimer_seqs) {
		   $seq =~ s/>.*\n+//g;
		   my $fasta_header = sprintf  ">%s %s, %s, subunit %d, %s residues", $target_information{NAME}, $target_information{PROTEIN_NAME}, $target_information{ORGANISM_NAME}, $i_subunit,  sequence_length($seq);
		   $fasta_header =~ s/;//g; # remove all semicolons
                   printf SEQ "%s;\n", $fasta_header;		   
                   printf SEQ "%s%s", $seq, ($seq =~ m/\n$/ ? '' : "\n");
		#  if ($no_chains_per_seq[$i_subunit] !~ m/^\d+$/) {
		#	$no_chains_per_seq[$i_subunit] = 1;
		#   }
		#   for (my $i = 0; $i < $no_chains_per_seq[$i_subunit]; $i++ ){
		#	my $chain = shift @LETTERS;
		#	print PDB "PARENT N/A\n";
		#	printf PDB generate_target_template_from_sequence($seq, $chain);
		#	print PDB "TER\n";
		#   }
		   $i_subunit++;
		}
		close SEQ;
		# print pdb template
		my @LETTERS = qw/A B C D E F G H I J K L M N O P Q R S T U V W X Y Z/;
		my $pdb_file = sprintf("%s/%s.pdb.txt", $LOCAL_CONFIG->{TARGET_TEMPLATES_DIR} , $target_information{NAME});
		open PDB, "> $pdb_file";
		printf PDB "REMARK Template for target %s\n", $target_information{NAME};
		my @no_chains_per_seq = split(/[A-Z]/, $target_information{STOICHIOMETRY}); # A2B1C1 or AnBn
		# e.g. A2B1C2 will be A, B, C, D (the A sequence), E (the C sequence)
		my $max_iter = 1;
		for(my $i = 1; $i < scalar(@no_chains_per_seq); $i++){
			if ($no_chains_per_seq[$i]=~m/^\d+$/ && $max_iter < $no_chains_per_seq[$i]) {
				$max_iter = $no_chains_per_seq[$i];
			}
		}
		my $index_iter = 1;
		my $atom_index = 1;
		while ($index_iter <= $max_iter) {
			my $i_subunit = 1;
			foreach my $seq (@multimer_seqs) {
				if ($index_iter == 1 || ($no_chains_per_seq[$i_subunit]=~m/^\d+$/ && $no_chains_per_seq[$i_subunit]>=$index_iter)) {
					my $chain = shift @LETTERS;
					print PDB "PARENT N/A\n";
					my ($to_print, $last_atom_index) = generate_target_template_from_sequence_start_atom_index($seq, $chain, $atom_index);
					printf PDB $to_print;
					print PDB "TER\n";
					$atom_index = $last_atom_index + 1;
				}
				$i_subunit++;
			}
			$index_iter++;
		}
		print PDB "END\n";
		close PDB;
	}
    }
    return $result;
}


sub generate_targets_file {
    my ($self, $id) = @_;
    
    my $result = 0;
    
    return $result if(! $self->exist($id));
    my %target_information = $self->info($id,0);
    if($target_information{IS_REFINEMENT} ne "1") {
	my $sequence = sprintf(">%s %s, %s, %s residues\n%s\n\n\n", $target_information{NAME}, $target_information{PROTEIN_NAME}, $target_information{ORGANISM_NAME}, $target_information{NUMBER_OF_AA}, $target_information{SEQUENCE});
	
	my $out_path = sprintf(">%s/%s.seq.txt", $LOCAL_CONFIG->{TARGET_TEMPLATES_DIR} , $target_information{NAME});
	open(FILE_OUT, $out_path);
	print FILE_OUT sprintf("%s", $sequence);
	close(FILE_OUT);
	
	my $target_template = sprintf("REMARK Template for target %s\n", $target_information{NAME});
	$target_template .= generate_target_template_from_sequence($target_information{SEQUENCE}, $target_information{CHAIN});
	$out_path = sprintf(">%s/%s.pdb.txt", $LOCAL_CONFIG->{TARGET_TEMPLATES_DIR} , $target_information{NAME});
	open(FILE_OUT, $out_path);
	print FILE_OUT sprintf("%s", $target_template);
	close(FILE_OUT);    
    }
   
    # for Tc, Ts, Tx use templates Tp
    my $name = $target_information{NAME}; 
    if ($name =~ m/^([TR])([csx])([0-9]{3})$/){
        my $parent = $1.'p'.$3;
        my $flag = $2;
        foreach my $ext (qw/pdb.txt seq.txt/){
	        my $old_file = $LOCAL_CONFIG->{TARGET_TEMPLATES_DIR}."/".$parent.".".$ext;
        	$old_file = abs_path($old_file);
	        my $new_file = $LOCAL_CONFIG->{TARGET_TEMPLATES_DIR}."/".$name.".".$ext;
	        $new_file = abs_path($new_file);
	        if(-f $old_file){
        	        if(0 != system("sed \"s:Tp:T$flag:g\" < $old_file > $new_file")){
                	        $result = 0;
	                }else {
				system("chmod g+w $new_file");
                                system("chgrp casp $new_file");
	                        $result = 1;
        	        }
	        } else {
        	        $result = 1;
	        }
	}
    }

 
    return $result;
}

# copy pdb files for assissted targets ([TR][acspx]123)
sub copy_pdb_file {
    my ($self, $id) = @_;
    my $result = 0;
    return $result if (!$self->exist($id));
    my $name = $self->name($id);
    if($name =~ m/^[NSFAXLnsfaxl][0-9]{4}(s[1-9]){0,1}$/){
	my $parent = 'T'.substr($name, 1);
	my $old_file = $LOCAL_CONFIG->{DATA_TARGETS_DIR}."/".$parent.".pdb";
	$old_file = abs_path($old_file);
	my $new_file = $LOCAL_CONFIG->{DATA_TARGETS_DIR}."/".$name.".pdb";
        $new_file = abs_path($new_file);
	if(-f $old_file){
		if(0 != system("cp -p $old_file $new_file")){
			return  $result;
		}else {
			$result = 1;
		}
	} else {
		$result = 1;
	}
    } else {
	$result = 1;
    }
    return $result;
}

sub is_target_files_exist {
    my ($self, $name) = @_;
    my $result = 0;
    
    my $file_path = sprintf("%s/%s.seq.txt", $LOCAL_CONFIG->{TARGET_TEMPLATES_DIR}, $name);
    if (-e $file_path) {
        $file_path = sprintf("%s/%s.pdb.txt", $LOCAL_CONFIG->{TARGET_TEMPLATES_DIR}, $name);
        if (-e $file_path) {            
            $result = 1;
        }
    }
    return $result;
}

sub is_published {
    my ($self, $id) = @_;
    
    my $result = 0;
    
    return $result if(! $self->exist($id));
    
    my $query = sprintf("SELECT release_date FROM casp13.targets WHERE (id = %d) AND (force_delay_publishing = 0) LIMIT 1", $id);
    #printf($query);
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth) && ($sth->rows() > 0)) {
        my ($release_date) = $sth->fetchrow_array();
        my $year = 0;
        my $month = 0;
        my $day = 0;
        if($release_date =~ /(\d+)\-(\d+)\-(\d+)/) {
            $year = $1;
            $month = $2;
            $day = $3;
        }
    
        my $release = new DateTime (
            year => $year,
            month => $month,
            day => $day,
            hour   => $LOCAL_CONFIG->{RELESE_HOURS},
            minute => $LOCAL_CONFIG->{RELESE_MINUTES},
            time_zone => 'America/Los_Angeles'
        );
        my $current = DateTime->now( time_zone => 'America/Los_Angeles' );
        
        #printf("%d-%d-%d %d:%d<br>", $release->year, $release->month, $release->day, $release->hour, $release->minute);
        #printf("%d-%d-%d %d:%d", $current->year, $current->month, $current->day, $current->hour, $current->minute);
        
        if(DateTime->compare_ignore_floating($release, $current) <= 0) {
            return 1;
        }
    }
    
    return $result;
}

sub is_casprol_by_name{
    my ($self,$name) = @_;
    my $id = $self->get_id_by_name($name);
    if($id <= 0){
	return 0;
    }
    
    my $query = sprintf("SELECT is_casprol FROM casp13.targets WHERE (id = %d) AND (force_delay_publishing = 0) LIMIT 1", $id);
    my $sth = $self->{_database}->query($query);

    if(defined($sth) && ($sth->rows() > 0)) {
	my ($is_casprol) = $sth->fetchrow_array();
	return $is_casprol;
    }else{
	return 0;
    }
}

sub is_capri_by_name{
    my ($self,$name) = @_;
    my $id = $self->get_id_by_name($name);
    if($id <= 0){
        return 0;
    }

    my $query = sprintf("SELECT is_capri FROM casp13.targets WHERE (id = %d) AND (force_delay_publishing = 0) LIMIT 1", $id);
    my $sth = $self->{_database}->query($query);

    if(defined($sth) && ($sth->rows() > 0)) {
        my ($is_capri) = $sth->fetchrow_array();
        return $is_capri;
    }else{
        return 0;
    }
}


sub is_expired {
    my ($self, $id, $type) = @_;
    
    my $result = 0;
    #return $result;
    return $result if(! $self->exist($id));
    my $select_type ="";
    if($type eq 'human'){ $select_type = "human_expiration_date";} else { $select_type = "server_expiration_date";}
    
    my $query = sprintf("SELECT %s FROM casp13.targets WHERE (id = %d) AND (force_delay_publishing = 0) LIMIT 1", $select_type, $id);
    #printf($query);
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth) && ($sth->rows() > 0)) {
        my ($date) = $sth->fetchrow_array();
        my $year = 0;
        my $month = 0;
        my $day = 0;
        if($date =~ /(\d+)\-(\d+)\-(\d+)/) {
            $year = $1;
            $month = $2;
            $day = $3;
        }
    
        my $date_new = new DateTime (
            year => $year,
            month => $month,
            day => $day,
            hour   => $LOCAL_CONFIG->{EXPIRED_HOURS},
            minute => $LOCAL_CONFIG->{EXPIRED_MINUTES},
            time_zone => 'America/Los_Angeles'
        );
        my $current = DateTime->now( time_zone => 'America/Los_Angeles' );
        
        #printf("%d-%d-%d %d:%d<br>", $release->year, $release->month, $release->day, $release->hour, $release->minute);
        #printf("%d-%d-%d %d:%d", $current->year, $current->month, $current->day, $current->hour, $current->minute);
        # check if target is after the expiration date  of stage 2 for QA prediction
	# in contrary to sub is_expired_QA, which check separately for stage 1 and stage 2
        if($type eq 'QA') {
            if(DateTime->compare_ignore_floating($date_new, $current) > 0) {
                return 1;
            }
	    $date_new->add(days => $LOCAL_CONFIG->{QA_STAGE2_EXPIRED_DAYS});
	    if(DateTime->compare_ignore_floating($date_new, $current) <= 0) {
		return 1;
	    }
        } else {
		#$date_new->add(days => 20);
            if(DateTime->compare_ignore_floating($date_new, $current) <= 0) {
                return 1;
            }
        }
    }
    return $result;
}


sub are_expired_all_associated_targets {
    my ($self, $target_name, $type) = @_;
    my $id = $self->get_id_by_name($target_name);
    my $result = 0;
    #return $result;
    return $result if(! $self->exist($id));
    my $select_type ="";
    if($type eq 'human'){ $select_type = "human_expiration_date";} else { $select_type = "server_expiration_date";}
    my $first_letter;
    my $target_code;
    if ($target_name =~ m/^(T|R)([0-9Rrczx])([0-9]{3})/){
	$first_letter = $1;
	$target_code = sprintf("%03d",$3);
    }
    if (!defined($first_letter) || !defined($target_code)){
	return $result;
    } else {
	$result = -1;
    }
    my $query = sprintf("SELECT %s FROM casp13.targets WHERE (name SIMILAR TO \'%s_%s\') AND (force_delay_publishing = 0) ORDER BY %s DESC LIMIT 1", $select_type, $first_letter, $target_code, $select_type);
    #printf($query);
    my $sth = $self->{_database}->query($query);

    if(defined($sth) && ($sth->rows() > 0)) {
        my ($date) = $sth->fetchrow_array();
        my $year = 0;
        my $month = 0;
        my $day = 0;
        if($date =~ /(\d+)\-(\d+)\-(\d+)/) {
            $year = $1;
            $month = $2;
            $day = $3;
        }

        my $date_new = new DateTime (
            year => $year,
            month => $month,
            day => $day,
            hour   => $LOCAL_CONFIG->{EXPIRED_HOURS},
            minute => $LOCAL_CONFIG->{EXPIRED_MINUTES},
            time_zone => 'America/Los_Angeles'
        );
        my $current = DateTime->now( time_zone => 'America/Los_Angeles' );

        if(DateTime->compare_ignore_floating($date_new, $current) <= 0) {
	    return 1;
	} 
    }
    return $result;
}








# check expiration date for QA predictions for stage 1 and stage 2
sub is_expired_QA{
    my ($self, $id, $stage) = @_;
    my $result = 0;
    #return $result;
    return $result if(! $self->exist($id));
    my $select_type = "server_expiration_date";

    my $query = sprintf("SELECT %s FROM casp13.targets WHERE (id = %d) AND (force_delay_publishing = 0) LIMIT 1", $select_type, $id);
    #printf($query);
    my $sth = $self->{_database}->query($query);

    if(defined($sth) && ($sth->rows() > 0)) {
        my ($date) = $sth->fetchrow_array();
        my $year = 0;
        my $month = 0;
        my $day = 0;
        if($date =~ /(\d+)\-(\d+)\-(\d+)/) {
            $year = $1;
            $month = $2;
            $day = $3;
        }

        my $date_exp = new DateTime (
            year => $year,
            month => $month,
            day => $day,
            hour   => $LOCAL_CONFIG->{EXPIRED_HOURS},
            minute => $LOCAL_CONFIG->{EXPIRED_MINUTES},
            time_zone => 'America/Los_Angeles'
        );
	# IMPORTANT!!! : release time of QA predictions should be set at 12:15(+2min) in order to avoid overlapping periods of prediction stage1 and stage2
	my $date_release = new DateTime (
            year => $year,
            month => $month,
            day => $day,
            hour   => $LOCAL_CONFIG->{EXPIRED_HOURS},
            minute => $LOCAL_CONFIG->{EXPIRED_MINUTES},
            time_zone => 'America/Los_Angeles'	   
	);
        my $current = DateTime->now( time_zone => 'America/Los_Angeles' );
	
	# check between release 1st stage and expiration 1st stage
        if(defined($stage) && $stage == 1){
	    # release date 
	    $date_release->add(days =>$LOCAL_CONFIG->{QA_STAGE1_RELEASE_DAYS});
	    # if current is before release date
	    if(DateTime->compare_ignore_floating($date_release, $current) > 0) {
                return 1;
            }
            $date_exp->add(days => $LOCAL_CONFIG->{QA_STAGE1_EXPIRED_DAYS});
	    # if current is after expired date 
	    if(DateTime->compare_ignore_floating($date_exp, $current) <= 0) {
                return 1;
            }
	# check between release 2nd stage and expiration 2nd stage
   	}elsif(defined($stage) && $stage == 2){
            # release date 
            $date_release->add(days =>$LOCAL_CONFIG->{QA_STAGE2_RELEASE_DAYS});
            # if current is before release date
            if(DateTime->compare_ignore_floating($date_release, $current) > 0) {
                return 1;
            }
            $date_exp->add(days => $LOCAL_CONFIG->{QA_STAGE2_EXPIRED_DAYS});
            # if current is after expired date 
            if(DateTime->compare_ignore_floating($date_exp, $current) <= 0) {
                return 1;
            }
	# check between release 1st stage and expiration 2nd stage
	}else{ #
            # release date of 1st stage
            $date_release->add(days =>$LOCAL_CONFIG->{QA_STAGE1_RELEASE_DAYS});
            # if current is before release date
            if(DateTime->compare_ignore_floating($date_release, $current) > 0) {
                return 1;
            }
            $date_exp->add(days => $LOCAL_CONFIG->{QA_STAGE2_EXPIRED_DAYS});
            # if current is after expired date 
            if(DateTime->compare_ignore_floating($date_exp, $current) <= 0) {
                return 1;
            }
 
        }

    }
    return $result;
}



sub is_extended_expired {
    my ($self, $id) = @_;
    my $result = 0;
    #return $result;
    return $result if(! $self->exist($id));
    my $query = sprintf("SELECT release_date FROM casp13.targets WHERE (id = %d) AND (force_delay_publishing = 0) LIMIT 1", $id);
    #printf($query);
    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)) {
        my ($date) = $sth->fetchrow_array();
        my $year = 0;
        my $month = 0;
        my $day = 0;
        if($date =~ /(\d+)\-(\d+)\-(\d+)/) {
            $year = $1;
            $month = $2;
            $day = $3;
        }
        my $date_new = new DateTime (
            year => $year,
            month => $month,
            day => $day,
            hour   => $LOCAL_CONFIG->{EXPIRED_HOURS},
            minute => $LOCAL_CONFIG->{EXPIRED_MINUTES},
            time_zone => 'America/Los_Angeles'
        );
        my $current = DateTime->now( time_zone => 'America/Los_Angeles' );
        #printf("%d-%d-%d %d:%d<br>", $release->year, $release->month, $release->day, $release->hour, $release->minute);
        #printf("%d-%d-%d %d:%d", $current->year, $current->month, $current->day, $current->hour, $current->minute);
        $date_new->add(days => 42);
        if(DateTime->compare_ignore_floating($date_new, $current) <= 0) {
        	return 1;
        }
    }
    return $result;
}

sub is_canceled {
    my ($self, $id) = @_;
    
    my $result = 0;
    #return $result;
    return $result if(! $self->exist($id));
    
    my $query = sprintf("SELECT cancelation_date, cancellation_status FROM casp13.targets WHERE (id = %d) LIMIT 1", $id);
    #printf($query);
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth) && ($sth->rows() > 0)) {
        my ($date, $status) = $sth->fetchrow_array();
        my $year = 0;
        my $month = 0;
        my $day = 0;
        if($date =~ /(\d+)\-(\d+)\-(\d+)/) {
            $year = $1;
            $month = $2;
            $day = $3;
        }
    
        my $date_new = new DateTime (
            year => $year,
            month => $month,
            day => $day,
            hour   => $LOCAL_CONFIG->{EXPIRED_HOURS},
            minute => $LOCAL_CONFIG->{EXPIRED_MINUTES},
            time_zone => 'America/Los_Angeles'
        );
        my $current = DateTime->now( time_zone => 'America/Los_Angeles' );
        
        #if(DateTime->compare_ignore_floating($date_new, $current) <= 0) {
        	return $status;
        #}

	# 0 - not cancelled; 1 - cancelled for all; 2 - cancelled for humans only; 3 - cancelled for servers only; 4 - cancelled for humansoft+
        
    }
    return $result;
}


# check if target is cancelled for QA predictions
# $type = 'server'/'human' - type of groups the cancelation is valid for
# $stage = '1'/'2' - stage of QA prediction
sub is_canceled_QA {
    my ($self, $id, $stage, $type) = @_;
    # default values for $stage = 1
    if(!defined($stage) || $stage != 2){
	$stage = 1;
    }
    # default value for $type = 'server'
    if(!defined($type) or $type ne 'human'){
	$type = 'server';
    }
    my $result = 0;
 
    return $result if(! $self->exist($id));

    my $query = sprintf("SELECT server_expiration_date, cancelation_date, cancellation_status FROM casp13.targets WHERE (id = %d) LIMIT 1", $id);
    
    my $sth = $self->{_database}->query($query);

    if(defined($sth) && ($sth->rows() > 0)) {
        my ($date_s, $date_c, $status) = $sth->fetchrow_array();

	if($status == 0){
	   return 0;
	}

	if($status == 1){
	   return 1;
	}

        my $year = 0;
        my $month = 0;
        my $day = 0;
        if($date_c =~ /(\d+)\-(\d+)\-(\d+)/) {
            $year = $1;
            $month = $2;
            $day = $3;
        }

	my $date_cnsl = new DateTime(
            year => $year,
            month => $month,
            day => $day,
            hour   => $LOCAL_CONFIG->{EXPIRED_HOURS},
            minute => $LOCAL_CONFIG->{EXPIRED_MINUTES},
            time_zone => 'America/Los_Angeles'
	);

	if($date_s =~ /(\d+)\-(\d+)\-(\d+)/){
            $year = $1;
            $month = $2;
	    $day = $3;
	}
        my $date_exp = new DateTime (
            year => $year,
            month => $month,
            day => $day,
            hour   => $LOCAL_CONFIG->{EXPIRED_HOURS},
            minute => $LOCAL_CONFIG->{EXPIRED_MINUTES},
            time_zone => 'America/Los_Angeles'
        );
	
	

	if($stage == 1){
		$date_exp->add(days=>$LOCAL_CONFIG->{QA_STAGE1_EXPIRED_DAYS});

		if(DateTime->compare_ignore_floating($date_exp, $date_cnsl) >= 0){
			return 1;
		} else {
			return 0;
		}

	}
	
	if ($stage == 2){
                $date_exp->add(days=>$LOCAL_CONFIG->{QA_STAGE2_EXPIRED_DAYS});
                if(DateTime->compare_ignore_floating($date_exp, $date_cnsl) >= 0){
                        return 1;
                }else{
			return 0;
		}
        }
	


        # 0 - not cancelled; 1 - cancelled for all; 2 - cancelled for humans only; 3 - cancelled for servers only; 4 - cancelled for humansoft+

    }
    return $result;

}


sub is_date_expired {
    my ($self, $date, $type) = @_;    
    my $result = 0;    
    my $year = 0;
    my $month = 0;
    my $day = 0;
    if($date =~ /(\d+)\-(\d+)\-(\d+)/) {
        $year = $1;
        $month = $2;
        $day = $3;
    }
    my $date_new = new DateTime (
        year => $year,
        month => $month,
        day => $day,
        hour   => $LOCAL_CONFIG->{EXPIRED_HOURS},
        minute => $LOCAL_CONFIG->{EXPIRED_MINUTES},
        time_zone => 'America/Los_Angeles'
    );
    my $current = DateTime->now( time_zone => 'America/Los_Angeles' );
        
    if(DateTime->compare_ignore_floating($date_new, $current) <= 0) {
        return 1;
    }
    return $result;
}


sub info_rawSequence{
    my ($self, $id) = @_;
    my %result = (
	SUBMITTED_SEQUENCES_ID => '',
	IS_REFINEMENT => '',
	NAME => '',
	PROTEIN_NAME => '',
	ORGANISM_NAME => '',
	SEQUENCE => '',
	STOICHIOMETRY => '',
	SUBUNIT => ''
    );
    return %result if(! $self->exist($id));

    my $query = sprintf("SELECT t.submitted_sequences_id, t.is_refinement, t.name, ss.protein_name, ss.organism_name, ss.sequence, ss.stoichiometry, t.subunit FROM casp13.targets t JOIN casp13.submitted_sequences ss ON (ss.id = t.submitted_sequences_id) WHERE (t.id = %d) ",  $id);
    my $sth = $self->{_database}->query($query);

    if(defined($sth) && ($sth->rows() > 0)) {
        ($result{SUBMITTED_SEQUENCES_ID}, $result{IS_REFINEMENT}, $result{NAME}, $result{PROTEIN_NAME}, $result{ORGANISM_NAME}, $result{SEQUENCE}, $result{STOICHIOMETRY}, $result{SUBUNIT} ) = $sth->fetchrow_array();
    }

    return %result;
}

sub info {
    my ($self, $id, $published_only) = @_;
    
    my %result = (
        SUBMITTED_SEQUENCES_ID => '',
        IS_SERVER_ONLY => '',
        IS_REFINEMENT => '',
	IS_CASPROL => '',
	IS_CAPRI => '',
        NAME => '',
        COMMENTS => '',
        RELEASE_DATE => '',
        HUMAN_EXPIRATION_DATE => '',
        SERVER_EXPIRATION_DATE => '',
	SOFT_EXPIRATION_DATE => '',
        CANCELATION_DATE => '',
        PROTEIN_NAME => '',
        ACCESSION_NUMBER => '',
        ORGANISM_NAME => '',
        SEQUENCE => '',
        NUMBER_OF_AA => '',
        SEQUENCE_DATABASE => '',
        GO_MOLECULAR_FUNCTION => '',
        EC_NUMBER => '',
        BINDING_SITE => '',
        IS_NMR => '',
        HAS_MAP_OR_SPECTRA => '',
        CURRENT_STATE_DESCRIPTION => '',
        SEQUENCE_FAMILY => '',
        SEQUENCE_RELEASE_DATE => '',
        CHAIN_TRACING_DATE => '',
	TARGET_LIST_COMMENTS => '',
	PDB_CODE => '',
	MULTIMERIC_STATUS => '',
	CANCELLATION_STATUS => '',
	CHAIN => '',
	SUBUNIT => '',
	STOICHIOMETRY => '',
	IS_ASSISTED => '',
    );
    
    return %result if(! $self->exist($id));
    
    if(!defined($published_only) || ($published_only == 1)) {
        if(! $self->is_published($id)) {
            return %result;
        }
    }
    
    my $query = sprintf("SELECT t.submitted_sequences_id, t.is_server_only, t.is_refinement, t.is_casprol, t.name, t.comments, t.release_date, t.human_expiration_date, t.server_expiration_date, t.soft_expiration_date, t.cancelation_date, ss.protein_name, ss.organism_name, ss.sequence, ss.accession_number, ss.number_of_aa, ss.sequence_database, ss.go_molecular_function, ss.ec_number, ss.binding_site, ss.is_nmr, ss.has_map_or_spectra, ss.current_state_description, ss.sequence_family, ss.release_date, ss.chain_tracing_date, t.target_list_comments, t.pdb_code, t.multimeric_status, t.cancellation_status, t.is_capri, t.chain, t.subunit, ss.stoichiometry  FROM casp13.targets t JOIN casp13.submitted_sequences ss ON (ss.id = t.submitted_sequences_id) WHERE (t.id = %d)", $id);
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth) && ($sth->rows() > 0)) {
        ($result{SUBMITTED_SEQUENCES_ID}, $result{IS_SERVER_ONLY}, $result{IS_REFINEMENT}, $result{IS_CASPROL}, $result{NAME}, $result{COMMENTS}, $result{RELEASE_DATE}, $result{HUMAN_EXPIRATION_DATE}, $result{SERVER_EXPIRATION_DATE}, $result{SOFT_EXPIRATION_DATE}, $result{CANCELATION_DATE}, $result{PROTEIN_NAME}, $result{ORGANISM_NAME}, $result{SEQUENCE}, $result{ACCESSION_NUMBER}, $result{NUMBER_OF_AA}, $result{SEQUENCE_DATABASE}, $result{GO_MOLECULAR_FUNCTION}, $result{EC_NUMBER}, $result{BINDING_SITE}, $result{IS_NMR}, $result{HAS_MAP_OR_SPECTRA}, $result{CURRENT_STATE_DESCRIPTION}, $result{SEQUENCE_FAMILY}, $result{SEQUENCE_RELEASE_DATE}, $result{CHAIN_TRACING_DATE}, $result{TARGET_LIST_COMMENTS}, $result{PDB_CODE}, $result{MULTIMERIC_STATUS}, $result{CANCELLATION_STATUS}, $result{IS_CAPRI}, $result{CHAIN}, $result{SUBUNIT}, $result{STOICHIOMETRY}) = $sth->fetchrow_array();
        #$result{SEQUENCE} = reformat_sequence($result{SEQUENCE}); # in CASP13 seqeunce should be formatted properly
        $result{NUMBER_OF_AA} = sequence_length($result{SEQUENCE}, $result{SUBUNIT});
	$result{SEQUENCE} = reformat_sequence_multimer($result{SEQUENCE}, $result{SUBUNIT});
	if ($result{NAME} =~ m/^[TNSFAXLnsfaxl].*s\d/) {
                $result{STOICHIOMETRY} = 'A1';
        } elsif ($result{NAME} =~ m/^T/ && $result{STOICHIOMETRY} !~ m/^A[n1-9]$/ ) {
                $result{STOICHIOMETRY} = 'A1';
        } elsif ($result{NAME} =~ m/^N0980/ ) {
                $result{STOICHIOMETRY} = 'A1B1';
        } elsif ($result{NAME} =~ m/^[Nn]/ ) {
                $result{STOICHIOMETRY} = 'A1';
        }

	#$result{STOICHIOMETRY} = ($result{NAME} =~ m/^[TNSFAXL]/ ? ($result{STOICHIOMETRY} =~ m/^A[n1-9]$/ ? $result{STOICHIOMETRY} : 'A1') : $result{STOICHIOMETRY});
	$result{IS_ASSISTED} = ($result{NAME} =~ m/^[NSFAXLnsfaxl]/ ? 1 : 0);
    }
    
    return %result;
}


sub update {
    my ($self, $id, $submitted_sequences_id, $is_server_only, $is_refinement, $is_casprol, $target, $comments, $release_date, $server_expiration_date, $soft_expiration_date, $human_expiration_date, $cancelation_date, $target_list_comments, $pdb_code, $multimeric_status, $cancellation_status, $is_capri, $chain, $subunit) = @_;
    
    my $result = 0;
    
    return $result if(! $self->exist($id));

    if ($cancellation_status > 0) {
	my $year_c = 0;
        my $month_c = 0;
        my $day_c = 0;
        if($cancelation_date =~ /(\d+)\-(\d+)\-(\d+)/) {
            $year_c = $1;
            $month_c = $2;
            $day_c = $3;
        }
    
        my $date_new = new DateTime (
            year => $year_c,
            month => $month_c,
            day => $day_c,
            hour   => $LOCAL_CONFIG->{EXPIRED_HOURS},
            minute => $LOCAL_CONFIG->{EXPIRED_MINUTES},
            time_zone => 'America/Los_Angeles'
        );
        my $current = DateTime->now( time_zone => 'America/Los_Angeles' );

        if(DateTime->compare_ignore_floating($date_new, $current) > 0) {
        	$cancelation_date = $date_new;
        }
    }
    
    if (!defined($is_capri) || $is_capri == 0){
	$is_capri = 0;
    } else {
	$is_capri = 1;
    }
    if (!defined($chain) || $chain !~ m/^[A-Za-z]$/){
        $chain = undef;
    }
    my $query;
    if (!(defined($subunit) && ($subunit eq 'assembly' || $subunit =~ m/^\d+$/))){
	$subunit = 1; # default value
    }
    if (defined($chain)){
	$query = sprintf("UPDATE casp13.targets SET submitted_sequences_id = %d, is_server_only = %d, is_refinement = %d, is_casprol = %d, name = '%s', comments = '%s', release_date = '%s', server_expiration_date = '%s', soft_expiration_date = '%s', human_expiration_date = '%s', cancelation_date = '%s', target_list_comments = '%s', pdb_code= '%s', multimeric_status = '%d', cancellation_status = '%d', is_capri='%d', chain='%s', subunit='%s' WHERE (id = %d)", quotemeta($submitted_sequences_id), $is_server_only, $is_refinement,$is_casprol, quotemeta($target), quotemeta($comments), $release_date, $server_expiration_date, $soft_expiration_date, $human_expiration_date, $cancelation_date, $target_list_comments, $pdb_code, $multimeric_status, $cancellation_status, $is_capri, $chain, ($subunit eq 'assembly' ? 0 : $subunit), $id);
    } else {
	$query = sprintf("UPDATE casp13.targets SET submitted_sequences_id = %d, is_server_only = %d, is_refinement = %d, is_casprol = %d, name = '%s', comments = '%s', release_date = '%s', server_expiration_date = '%s', soft_expiration_date = '%s', human_expiration_date = '%s', cancelation_date = '%s', target_list_comments = '%s', pdb_code= '%s', multimeric_status = '%d', cancellation_status = '%d', is_capri='%d', subunit='%d' WHERE (id = %d)", quotemeta($submitted_sequences_id), $is_server_only, $is_refinement,$is_casprol, quotemeta($target), quotemeta($comments), $release_date, $server_expiration_date, $soft_expiration_date, $human_expiration_date, $cancelation_date, $target_list_comments, $pdb_code, $multimeric_status, $cancellation_status, $is_capri, ($subunit eq 'assembly' ? 0 : $subunit), $id);
    }
    my $sth = $self->{_database}->query($query);

    if(defined($sth)) {
        # add logger
        $result = 1;
    }
    
    return $result;
}

sub add {
    my ($self, $submitted_sequences_id, $target, $is_server_only, $is_refinement, $is_casprol, $comments, $release_date, $multimeric_status, $server_expiration_date, $soft_expiration_date, $human_expiration_date, $cancelation_date, $target_list_comments, $pdb_code, $cancellation_status, $is_capri, $chain, $subunit) = @_;
    
    my $result = 0;
    
    my $year = 0;
    my $month = 0;
    my $day = 0;
    if($release_date =~ /(\d+)\-(\d+)\-(\d+)/) {
        $year = $1;
        $month = $2;
        $day = $3;
    }

    my $server_date = new DateTime (
        year => $year,
        month => $month,
        day => $day,
        time_zone => 'America/Los_Angeles'
    );

    my $soft_date = new DateTime (
        year => $year,
        month => $month,
        day => $day,
        time_zone => 'America/Los_Angeles'
    );

    my $human_date = new DateTime (
        year => $year,
        month => $month,
        day => $day,
        time_zone => 'America/Los_Angeles'
    );

    $server_date->add(days => 3);
    $soft_date->add(days => 21);
    $human_date->add(days => 21);

    if ($cancellation_status > 0) {
	my $year_c = 0;
        my $month_c = 0;
        my $day_c = 0;
        if($cancelation_date =~ /(\d+)\-(\d+)\-(\d+)/) {
            $year_c = $1;
            $month_c = $2;
            $day_c = $3;
        }
    
        my $date_new = new DateTime (
            year => $year_c,
            month => $month_c,
            day => $day_c,
            hour   => 12,
            minute => 15,
            time_zone => 'America/Los_Angeles'
        );
        my $current = DateTime->now( time_zone => 'America/Los_Angeles' );

        if(DateTime->compare_ignore_floating($date_new, $current) > 0) {
        	$cancelation_date = $date_new;
        }
    }
  
    if (!defined ($is_capri) || $is_capri == 0){
	$is_capri = 0;
    } else {
	$is_capri = 1;
    }
    if (!defined($chain) || $chain !~ m/^[A-Za-z]$/){
	$chain = undef;
    }
    if (!(defined($subunit) && ($subunit eq 'assembly' || $subunit =~ m/^\d+$/))){
        $subunit = 1; # default value
    }

    my $query;
    if (defined($chain)){
	$query = sprintf("INSERT INTO casp13.targets (submitted_sequences_id, name, is_server_only, is_refinement, is_casprol, comments, release_date, human_expiration_date, server_expiration_date, soft_expiration_date, cancelation_date, multimeric_status, target_list_comments, pdb_code, cancellation_status, is_capri, chain, subunit) VALUES (%d, '%s', %d, %d, %d, '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%s', '%s', %d, '%s', '%s', %d) RETURNING id",
                        $submitted_sequences_id, $target, $is_server_only, $is_refinement, $is_casprol, quotemeta($comments), $release_date, $human_expiration_date, $server_expiration_date, $soft_expiration_date, $cancelation_date, $multimeric_status, $target_list_comments, $pdb_code, $cancellation_status, $is_capri, $chain, ($subunit eq 'assembly' ? 0 : $subunit));

    } else { 
	$query = sprintf("INSERT INTO casp13.targets (submitted_sequences_id, name, is_server_only, is_refinement, is_casprol, comments, release_date, human_expiration_date, server_expiration_date, soft_expiration_date, cancelation_date, multimeric_status, target_list_comments, pdb_code, cancellation_status, is_capri, subunit) VALUES (%d, '%s', %d, %d, %d, '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%s', '%s', %d, '%s', %d) RETURNING id",
                        $submitted_sequences_id, $target, $is_server_only, $is_refinement, $is_casprol, quotemeta($comments), $release_date, $human_expiration_date, $server_expiration_date, $soft_expiration_date, $cancelation_date, $multimeric_status, $target_list_comments, $pdb_code, $cancellation_status, $is_capri, ($subunit eq 'assembly' ? 0 : $subunit));
    }
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth)) {
        #my $query = sprintf("SELECT MAX(id) FROM casp13.targets");
        #my $sth = $self->{_database}->query($query);
        # add logger
        ($result) = $sth->fetchrow_array();
    }
    
    return $result;
}

sub delete {
    my ($self, $id) = @_;
    
    my $result = 0;
    
    return $result if(! $self->exist($id));
    
    my $query = sprintf("DELETE FROM casp13.targets WHERE (id = %d)", $id);
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth)) {
        # add logger
        $result = defined($sth) ? 1 : 0;
    }
    
    return $result;
}

sub targets {
    my ($self, $field, $order) = @_;
    
    my @targets = ();
    
    $field = (!defined($field) || ($field eq '')) ? 't.date' : $field;
    $order = (!defined($order) || ($order eq '')) ? 'ASC' : $order;
    
    my $query = sprintf("SELECT t.id, t.submitted_sequences_id, t.name, t.is_server_only, t.is_refinement, t.is_casprol, t.comments, t.force_delay_publishing, t.release_date, t.human_expiration_date, t.server_expiration_date, t.soft_expiration_date, t.cancelation_date, ss.protein_name, ss.organism_name, t.date, t.target_list_comments, t.pdb_code, t.multimeric_status, t.cancellation_status, t.is_capri FROM casp13.targets t JOIN casp13.submitted_sequences ss ON (ss.id = t.submitted_sequences_id) ORDER BY %s %s", $field, $order);
    my $sth = $self->{_database}->query($query);
    
#print "Content-Type: text/html; charset=ISO-8859-1\n\n";
#print $query;
    if(defined($sth) && ($sth->rows() > 0)) {
        my $index = 1;
        while(my($id, $submitted_sequences_id, $name, $is_server_only, $is_refinement, $is_casprol, $comments, $force_delay_publishing, $release_date, $human_expiration_date, $server_expiration_date, $soft_expiration_date, $cancelation_date, $protein_name, $organism_name, $date, $target_list_comments, $pdb_code, $multimeric_status, $cancellation_status, $is_capri) = $sth->fetchrow_array()) {
            if($date =~ /(\S+)\s+/) { $date = $1; }
            
            push(@targets, {
                    ID => $id,
                    SUBMITTED_SEQUENCES_ID => $submitted_sequences_id,
                    INDEX => $index,
                    NAME => $name,
                    IS_SERVER_ONLY => $is_server_only,
                    IS_REFINEMENT => $is_refinement,
		    IS_CASPROL => $is_casprol,
		    IS_CAPRI => $is_capri,
                    COMMENTS => $comments,
                    FORCE_DELAY_PUBLISHING => $force_delay_publishing,
                    RELEASE_DATE => $release_date,
                    HUMAN_EXPIRATION_DATE => $human_expiration_date,
                    SERVER_EXPIRATION_DATE => $server_expiration_date,
                    SOFT_EXPIRATION_DATE => $soft_expiration_date,
                    CANCELATION_DATE => $cancelation_date,
                    PROTEIN_NAME => $protein_name,
                    ORGANISM_NAME => $organism_name,
                    DATE => $date,
                    IS_PUBLISHED => $self->is_published($id),
                    IS_FILES_EXIST => $self->is_target_files_exist($name),
                    TARGET_LIST_COMMENTS => $target_list_comments,
		    MULTIMERIC_STATUS=> $multimeric_status,
		    CANCELLATION_STATUS=> $cancellation_status
            	}
            );
            $index++;
        }
    }
    
    return @targets;
}

sub public_targets {
    my ($self, $params) = @_;
    
    my $field = $params->{field};
    my $order = $params->{order};
    my $view_targets = $params->{view_targets};
    my $view = $params->{view};
 
    my @targets = ();
    
    $field = (!defined($field) || ($field eq '')) ? 't.name' : $field;
    $order = (!defined($order) || ($order eq '')) ? 'ASC' : $order;
    
    $view_targets = (!defined($view_targets) ? '' : $view_targets);
    $view = (!defined($view) ? '' : $view);

    my $view_targets_subquery = "";
    
    if($view_targets eq 'human') {
        $view_targets_subquery = sprintf(" AND (t.is_server_only = 0)")
    } elsif($view_targets eq 'server') {
        $view_targets_subquery = sprintf(" AND (t.is_server_only = 1)")
    }
    
    my $view_refinement_query = '';
    if($view eq 'refinement') {
        $view_refinement_query = sprintf(" AND (t.is_refinement = 1)");
    } else {
    #    $view_refinement_query = sprintf(" AND (t.is_refinement = 0)");
    }
    
    my $view_other_exper_targets_query = '';
    if($view eq 'others') {
        $view_other_exper_targets_query = sprintf(" AND (t.name similar to \'[NSFAXLnsfaxl]%s\') ", '%');
    }

    my $view_regular_targets_query = '';
    if($view eq 'regular') {
        $view_regular_targets_query = sprintf(" AND (t.name similar to \'T[0-9]%s\') ", '%');
    }
    my $view_multimer_targets_query = '';
    if($view eq 'multimer') {
        $view_multimer_targets_query = sprintf(" AND (t.name similar to \'[HO][0-9]%s\') ", '%');
    }



    my $query = sprintf("SELECT t.id, t.submitted_sequences_id, t.name, t.is_server_only, t.is_refinement, t.is_casprol, t.release_date, t.server_expiration_date, t.soft_expiration_date, t.human_expiration_date, t.cancelation_date, t.comments, ss.protein_name, ss.organism_name, length(trim(ss.sequence)), ss.sequence_database, ss.sequence, ss.go_molecular_function, ss.ec_number, ss.binding_site, ss.is_nmr, ss.has_map_or_spectra, sc.full_name, t.target_list_comments, t.pdb_code, t.multimeric_status, s.date, s.is_published, t.cancellation_status, t.is_capri, ss.oligomeric_state, ss.stoichiometry FROM casp13.targets t JOIN casp13.submitted_sequences ss ON (ss.id = t.submitted_sequences_id) LEFT JOIN casp13.structures s ON (s.targets_id = t.id) LEFT JOIN casp13.sgi_center sc ON (sc.id = ss.sgi_center_id) WHERE (t.force_delay_publishing = 0) %s %s %s %s %s ORDER BY %s %s, t.name %s", $view_targets_subquery, $view_refinement_query, $view_other_exper_targets_query, $view_regular_targets_query, $view_multimer_targets_query, $field, $order, $order);
    

# print "Content-Type: text/html; charset=ISO-8859-1\n\n";
# print $query;

    my $sth = $self->{_database}->query($query);
    
    if(defined($sth) && ($sth->rows() > 0)) {
        my $index = 1;
        while(my($id, $submitted_sequences_id, $name, $is_server_only, $is_refinement, $is_casprol, $release_date, $server_expiration_date, $soft_expiration_date, $human_expiration_date, $cancelation_date, $comments, $protein_name, $organism_name, $number_of_aa, $sequence_database, $sequence, $go_molecular_function, $ec_number, $binding_site, $is_nmr, $has_map_or_spectra, $sgi_center, $target_list_comments, $pdb_code, $multimeric_status, $structure_publish_date, $is_published_structure, $cancellation_status, $is_capri, $oligomeric_state, $stoichiometry) = $sth->fetchrow_array()) {
            
            # skip not released targets
            if(!$self->is_published($id)) { next; }

	    # set expiration date for QA stage 1 and QA stage 2
	    my $qa_expiration_date1 = '';
	    my $qa_expiration_date2 = '';
	    if($server_expiration_date =~ m/^\s*(\d+)-(\d+)-(\d+)/){
		my $year = $1;
		my $month = $2;
		my $day = $3;
		my $date_qa1 = new DateTime (
	            year => $year,
	            month => $month,
	            day => $day,
	            hour   => $LOCAL_CONFIG->{EXPIRED_HOURS},
	            minute => $LOCAL_CONFIG->{EXPIRED_MINUTES},
	            time_zone => 'America/Los_Angeles'
        	);
		$date_qa1->add(days => $LOCAL_CONFIG->{QA_STAGE1_EXPIRED_DAYS});
		$qa_expiration_date1 = $date_qa1->date();
		my $date_qa2 = new DateTime (
                    year => $year,
                    month => $month,
                    day => $day,
                    hour   => $LOCAL_CONFIG->{EXPIRED_HOURS},
                    minute => $LOCAL_CONFIG->{EXPIRED_MINUTES},
                    time_zone => 'America/Los_Angeles'
                );
                $date_qa2->add(days => $LOCAL_CONFIG->{QA_STAGE2_EXPIRED_DAYS});
		$qa_expiration_date2 = $date_qa2->date();
	    }
	    my $subunit_index = undef;
  	    if ($name =~ m/s(\d)/) { # subunit index
		$subunit_index = $1;
	    }
	    if ($name =~ m/^[TNSFAXLnsfaxl].*s\d/) {
		$stoichiometry = 'A1';
	    } elsif ($name =~ m/^T/ && $stoichiometry !~ m/^A[n1-9]$/ ) {
		$stoichiometry = 'A1';
	    } elsif ($name =~ m/^N0980/ ) {
                $stoichiometry = 'A1B1';
            } elsif ($name =~ m/^[Nn]/) {
                $stoichiometry = 'A1';
            }

            push(@targets, {
                    ID => $id,
                    SUBMITTED_SEQUENCES_ID => $submitted_sequences_id,
                    INDEX => $index,
                    NAME => $name,
                    IS_SERVER_ONLY => $is_server_only,
		    IS_REFINEMENT => $is_refinement,
                    IS_CASPROL => $is_casprol,
		    IS_CAPRI => $is_capri,
                    RELEASE_DATE => $release_date,
                    SERVER_EXPIRATION_DATE => $server_expiration_date,
                    SOFT_EXPIRATION_DATE => $soft_expiration_date,
                    HUMAN_EXPIRATION_DATE => $human_expiration_date,
                    CANCELATION_DATE => $cancelation_date,
                    SGI_CENTER => $sgi_center,
                    COMMENTS => $comments,
                    
                    PROTEIN_NAME => $protein_name,
                    ORGANISM_NAME => $organism_name,
                    NUMBER_OF_AA => sequence_length($sequence, $subunit_index),
                    SEQUENCE_DATABASE => $sequence_database,
                    GO_MOLECULAR_FUNCTION => $go_molecular_function,
                    EC_NUMBER => $ec_number,
                    BINDING_SITE => $binding_site,
                    IS_NMR => $is_nmr,
                    HAS_MAP_OR_SPECTRA => $has_map_or_spectra,
                    TARGET_LIST_COMMENTS => $target_list_comments,
                    PDB_CODE => $pdb_code,
		    MULTIMERIC_STATUS => $multimeric_status,
                    IS_CANCELED => $self->is_canceled($id),
		    CANCELATION_STATUS => $cancellation_status,
                    STRUCTURE_PUBLISH_DATE => $structure_publish_date,
                    IS_PUBLISHED_STRUCTURE => $is_published_structure,
		    QA_EXPIRATION_DATE1 => $qa_expiration_date1,
		    QA_EXPIRATION_DATE2 => $qa_expiration_date2,
		    OLIGOMERIC_STATE => $oligomeric_state,
		    STOICHIOMETRY => $stoichiometry,
		    IS_ASSISTED => ($name =~ m/^[NSFAXLnsfaxl]/ ? 1 : 0),
            	}
            );
            $index++;
        }
    } 
    my @sorted = ();
    if ($field eq "length(ss.sequence)") {
	if ($order eq "ASC") {
	    @sorted = sort custom_sort @targets;    

	} else {
	    @sorted = reverse sort custom_sort @targets;
	}
	return @sorted;
    }
        
    return @targets;
}

sub custom_sort { 
    # perl function 'stat' returns array of info on a file
    # 10th element of the stat array is last modified date,
    # returned as number of seconds since 1/1/1970.
#    my %seq_length1 = $a; # get last modified date
#    my %seq_length2 = $b; # get last modified date
#    my $myA = $seq_length1{NUMBER_OF_AA};
#    my $myB = $seq_length2{NUMBER_OF_AA};
#    return $myA cmp $myB;
    return $$a{NUMBER_OF_AA} <=> $$b{NUMBER_OF_AA};
}


sub get_is_final_domain_def {
    my ($self, $id) = @_;

    my $result = 0;

    my $query = sprintf("SELECT is_final_domain_def FROM casp13.targets WHERE (id = %d)", $id);
    my $sth = $self->{_database}->query($query);

    if(defined($sth) && ($sth->rows() > 0)) {
        ($result) = $sth->fetchrow_array();
    }

    return $result;
}

sub set_is_final_domain_def {
    my ($self, $id, $final_dom_def) = @_;

    if(!defined($final_dom_def)){
	$final_dom_def = 0;
    }elsif($final_dom_def =~ m/^[1-9]/){
	$final_dom_def = 1;
    }else{
	$final_dom_def = 0;
    }

    my $result = 0;

    return $result if(! $self->exist($id));

    my $query = sprintf("UPDATE casp13.targets SET is_final_domain_def = %d WHERE (id = %d)", $final_dom_def ,$id);
    my $sth = $self->{_database}->query($query);

    if(defined($sth)) {
        # add logger
        $result = defined($sth) ? 1 : 0;
    }

    return $result;
}

sub get_is_update_domain_def {
    my ($self, $id) = @_;

    my $result = 0;

    my $query = sprintf("SELECT is_update_domain_def FROM casp13.targets WHERE (id = %d)", $id);
    my $sth = $self->{_database}->query($query);

    if(defined($sth) && ($sth->rows() > 0)) {
        ($result) = $sth->fetchrow_array();
    }

    return $result;
}

sub set_is_update_domain_def {
    my ($self, $id, $update_dom_def) = @_;

    if(!defined($update_dom_def)){
        $update_dom_def = 0;
    }elsif($update_dom_def =~ m/^[1-9]/){
        $update_dom_def = 1;
    }else{
        $update_dom_def = 0;
    }

    my $result = 0;

    return $result if(! $self->exist($id));

    my $query = sprintf("UPDATE casp13.targets SET is_update_domain_def = %d WHERE (id = %d)", $update_dom_def ,$id);
    my $sth = $self->{_database}->query($query);

    if(defined($sth)) {
        # add logger
        $result = defined($sth) ? 1 : 0;
    }

    return $result;
}


sub get_is_final_eval {
    my ($self, $id) = @_;

    my $result = 0;

    my $query = sprintf("SELECT is_final_eval FROM casp13.targets WHERE (id = %d)", $id);
    my $sth = $self->{_database}->query($query);

    if(defined($sth) && ($sth->rows() > 0)) {
        ($result) = $sth->fetchrow_array();
    }

    return $result;
}

sub set_is_final_eval {
    my ($self, $id, $is_final_eval) = @_;

    if(!defined($is_final_eval)){
        $is_final_eval = 0;
    }elsif($is_final_eval =~ m/^[1-9]/){
        $is_final_eval = 1;
    }else{
        $is_final_eval = 0;
    }

    my $result = 0;

    return $result if(! $self->exist($id));

    my $query = sprintf("UPDATE casp13.targets SET is_final_eval = %d WHERE (id = %d)", $is_final_eval ,$id);
    my $sth = $self->{_database}->query($query);

    if(defined($sth)) {
        # add logger
        $result = defined($sth) ? 1 : 0;
    }

    return $result;
}

sub targetsCAPRIToReleaseToday {
    my ($self) = @_;
    my $current = DateTime->now(time_zone=>'America/Los_Angeles');

    my $today = sprintf("%4d-%02d-%02d", $current->year(), $current->month(), $current->day());

    my @targets = ();

    my $query = sprintf("SELECT t.id, t.name, ss.sequence FROM casp13.targets t JOIN casp13.submitted_sequences ss ON t.submitted_sequences_id=ss.id WHERE t.release_date='%s' AND t.is_capri=1 ", $today);
    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)) {
        while(my($id, $name, $seq) = $sth->fetchrow_array()){
                $seq =~ s/\s+//g;
                $seq = uc($seq);
                push(@targets, {
                        ID=>$id,
                        NAME=>$name,
                        SEQUENCE=>$seq,
                    }
                );
        }
    }
    return @targets;
}

sub targetsToReleaseToday {
    my ($self) = @_;
    my $current = DateTime->now(time_zone=>'America/Los_Angeles');

    my $today = sprintf("%4d-%02d-%02d", $current->year(), $current->month(), $current->day());

    my @targets = ();

    my $query = sprintf("SELECT t.id, t.name, ss.sequence, ss.stoichiometry FROM casp13.targets t JOIN casp13.submitted_sequences ss ON t.submitted_sequences_id=ss.id WHERE t.release_date='%s'", $today);
    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)) {
        while(my($id, $name, $seq, $stoich) = $sth->fetchrow_array()){
                $seq =~ s/\s+//g;
                $seq = uc($seq);
                push(@targets, {
                        ID=>$id,
                        NAME=>$name,
                        SEQUENCE=>$seq,
			STOICHIOMETRY=>$stoich
                    }
                );
        }
    }
    return @targets;
}

sub targetsExpiredForServersToday {
    my ($self) = @_;
    my $current = DateTime->now(time_zone=>'America/Los_Angeles');

    my $today = sprintf("%4d-%02d-%02d", $current->year(), $current->month(), $current->day());

    my @targets = ();

    my $query = sprintf("SELECT t.id, t.name FROM casp13.targets t  WHERE t.server_expiration_date='%s' and t.name similar to 'T[0-9]%%' ", $today);
    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)) {
        while(my($id, $name) = $sth->fetchrow_array()){
                push(@targets, {
                        ID=>$id,
                        NAME=>$name
                    }
                );
        }
    }
    return @targets;
}


1;