package TargetsManager;

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;

### METHODS (last update 05/25/2010 M. Wojciechowski):
#   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_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, $id) = @_;
    
    my $result = 0;
    
    my $query = sprintf("SELECT COUNT(id) FROM casp9.targets WHERE (id = %d)", $id);
    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_id_by_name {
    my ($self, $name) = @_;
    
    my $result = 0;
    
    my $query = sprintf("SELECT id FROM casp9.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 casp9.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 casp9.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 casp9.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 {
    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);    
    }
    
    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 casp9.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   => 9,
            minute => 00,
            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_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 casp9.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   => 12,
            minute => 15,
            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($type eq 'QA') {
            if(DateTime->compare_ignore_floating($date_new, $current) > 0) {
                return 1;
            }
	    $date_new->add(days => 4);
	    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 is_extended_expired {
    my ($self, $id) = @_;
    my $result = 0;
    #return $result;
    return $result if(! $self->exist($id));
    my $query = sprintf("SELECT release_date FROM casp9.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   => 12,
            minute => 15,
            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 casp9.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   => 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) {
        	return $status;
        #}

	# 0 - not cancelled 1 - cancelled for all 2 - cancelled for humans only 3 - cancelled for servers only
        
    }
    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   => 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) {
        return 1;
    }
    return $result;
}


sub info {
    my ($self, $id, $published_only) = @_;
    
    my %result = (
        SUBMITTED_SEQUENCES_ID => '',
        IS_SERVER_ONLY => '',
        IS_REFINEMENT => '',
        NAME => '',
        COMMENTS => '',
        RELEASE_DATE => '',
        HUMAN_EXPIRATION_DATE => '',
        SERVER_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 => ''
    );
    
    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.name, t.comments, t.release_date, t.human_expiration_date, t.server_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 FROM casp9.targets t JOIN casp9.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{NAME}, $result{COMMENTS}, $result{RELEASE_DATE}, $result{HUMAN_EXPIRATION_DATE}, $result{SERVER_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}) = $sth->fetchrow_array();
        $result{SEQUENCE} = reformat_sequence($result{SEQUENCE});
        $result{NUMBER_OF_AA} = sequence_length($result{SEQUENCE});
    }
    
    return %result;
}

sub update {
    my ($self, $id, $submitted_sequences_id, $is_server_only, $is_refinement, $target, $comments, $release_date, $server_expiration_date, $human_expiration_date, $cancelation_date, $target_list_comments, $pdb_code, $multimeric_status, $cancellation_status) = @_;
    
    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   => 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;
        }
    }
    
    my $query = sprintf("UPDATE casp9.targets SET submitted_sequences_id = %d, is_server_only = %d, is_refinement = %d, name = '%s', comments = '%s', release_date = '%s', server_expiration_date = '%s', human_expiration_date = '%s', cancelation_date = '%s', target_list_comments = '%s', pdb_code= '%s', multimeric_status = '%d', cancellation_status = '%d' WHERE (id = %d)", quotemeta($submitted_sequences_id), $is_server_only, $is_refinement, quotemeta($target), quotemeta($comments), $release_date, $server_expiration_date, $human_expiration_date, $cancelation_date, $target_list_comments, $pdb_code, $multimeric_status, $cancellation_status, $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, $comments, $release_date, $multimeric_status, $server_expiration_date, $human_expiration_date, $cancelation_date, $target_list_comments, $pdb_code, $cancellation_status) = @_;
    
    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 $human_date = new DateTime (
        year => $year,
        month => $month,
        day => $day,
        time_zone => 'America/Los_Angeles'
    );

    $server_date->add(days => 3);
    $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;
        }
    }

    my $query = sprintf("INSERT INTO casp9.targets (submitted_sequences_id, name, is_server_only, is_refinement, comments, release_date, human_expiration_date, server_expiration_date, cancelation_date, multimeric_status, target_list_comments, pdb_code, cancellation_status) VALUES (%d, '%s', %d, %d, '%s', '%s', '%s', '%s', '%s', '%d', '%s', '%s', %d)",
                        $submitted_sequences_id, $target, $is_server_only, $is_refinement, $comments, $release_date, $human_expiration_date, $server_expiration_date, $cancelation_date, $multimeric_status, $target_list_comments, $pdb_code, $cancellation_status);
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth)) {
        my $query = sprintf("SELECT MAX(id) FROM casp9.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 casp9.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.comments, t.force_delay_publishing, t.release_date, t.human_expiration_date, t.server_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 FROM casp9.targets t JOIN casp9.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, $comments, $force_delay_publishing, $release_date, $human_expiration_date, $server_expiration_date, $cancelation_date, $protein_name, $organism_name, $date, $target_list_comments, $pdb_code, $multimeric_status, $cancellation_status) = $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,
                    COMMENTS => $comments,
                    FORCE_DELAY_PUBLISHING => $force_delay_publishing,
                    RELEASE_DATE => $release_date,
                    HUMAN_EXPIRATION_DATE => $human_expiration_date,
                    SERVER_EXPIRATION_DATE => $server_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;
    
    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 $query = sprintf("SELECT t.id, t.submitted_sequences_id, t.name, t.is_server_only, t.release_date, t.server_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.is_refinement, t.target_list_comments, t.pdb_code, t.multimeric_status, s.date, s.is_published, sc.id FROM casp9.targets t JOIN casp9.submitted_sequences ss ON (ss.id = t.submitted_sequences_id) LEFT JOIN casp9.structures s ON (s.targets_id = t.id) LEFT JOIN casp9.sgi_center sc ON (sc.id = ss.sgi_center_id) WHERE (t.force_delay_publishing = 0) %s %s ORDER BY %s %s", $view_targets_subquery, $view_refinement_query, $field, $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, $release_date, $server_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, $is_refinement, $target_list_comments, $pdb_code, $multimeric_status, $structure_publish_date, $is_published_structure) = $sth->fetchrow_array()) {
            
            # skip not released targets
            if(!$self->is_published($id)) { next; }
            
            push(@targets, {
                    ID => $id,
                    SUBMITTED_SEQUENCES_ID => $submitted_sequences_id,
                    INDEX => $index,
                    NAME => $name,
                    IS_SERVER_ONLY => $is_server_only,
                    RELEASE_DATE => $release_date,
                    SERVER_EXPIRATION_DATE => $server_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),
                    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,
                    IS_REFINEMENT => $is_refinement,
                    TARGET_LIST_COMMENTS => $target_list_comments,
                    PDB_CODE => $pdb_code,
		    MULTIMERIC_STATUS => $multimeric_status,
                    IS_CANCELED => $self->is_canceled($id),
                    STRUCTURE_PUBLISH_DATE => $structure_publish_date,
                    IS_PUBLISHED_STRUCTURE => $is_published_structure
            	}
            );
            $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};
}
1;