package MeetingRegistrationManager;

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;

my $meeting_registration = undef;

sub new {
    my ($class) = @_;    
    return $meeting_registration if(defined($meeting_registration));
    my %model = (ID => '');
    
    my $self = {
        _id => undef,
        _database => Database->new($CONFIG->{HOSTNAME}, $CONFIG->{PORT}, $CONFIG->{DATABASE}, $CONFIG->{USERNAME}, $CONFIG->{PASSWORD}),
        _model => \%model
    };
    
    $meeting_registration = bless $self, $class;
    return $meeting_registration;
}

sub get_new_model {
    my ($self) = @_;    
    my %model = (
########################### system        
	id => '',
	accounts_id =>'',
	single_room => '',
	vegetarian => '',
	handicapped => '',
	fee_waiver_support => '',
	travel_grant => '',
	comment => '',
	accompany => '',
	payment_type => '',
	poster => '',
	poster_autor =>'',
	poster_title => '',
    );
    return %model;
}
sub is_column {
    my ($self, $colunm_name) = @_;
    my $result = 0;
    if(	$colunm_name eq 'id' ||
	$colunm_name eq 'accounts_id' ||
	$colunm_name eq 'single_room' ||
	$colunm_name eq 'vegetarian' ||
	$colunm_name eq 'handicapped' ||
	$colunm_name eq 'fee_waiver_support' ||
	$colunm_name eq 'travel_grant' ||
	$colunm_name eq 'comment' ||
	$colunm_name eq 'accompany' ||
	$colunm_name eq 'payment_type' ||
	$colunm_name eq 'poster' ||
	$colunm_name eq 'poster_autor' ||
	$colunm_name eq 'poster_title'
	
      ) {
	$result = 1;
    }
    return $result;
}

sub exist {
    my ($self, $accounts_id) = @_;
    my $result = 0;
    my $query = sprintf("SELECT COUNT(accounts_id) FROM casp13.meeting_registration WHERE (accounts_id = %s)", $accounts_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 exist_by_parameters {
    my ($self, %model) = @_;
    my $result = 0;
    my $query = sprintf("SELECT mr.id FROM casp13.meeting_registration mr JOIN public.accounts ac ON (mr.accounts_id = ac.id) WHERE mr.accounts_id=\'%s\' limit 1", $model{accounts_id});
    my $sth = $self->{_database}->query($query);
    #print "Content-Type: text/html; charset=ISO-8859-1\n\n";
    #print $query ."\n";
    if(defined($sth) && ($sth->rows() > 0)) {
        my ($id) = $sth->fetchrow_array();
	$result = $id;
    }
    return $result;
}

sub update {
    my ($self, %model) = @_;
    #print "Content-Type: text/html; charset=ISO-8859-1\n\n";
    my $result = 0;
    my $set_query = '';

    while ( my ($key, $value) = each(%model) ) {
	if($key eq 'id' || $value eq '' || !defined($value)) {
	    #print "NE---$key---------->$value<----------\n";
	}else{ 
	    #list of columns which not updated
	    if($self->is_column($key) == 1) {
		$set_query .= sprintf(" %s = \'%s\', ", $key, $value);
	    }
	}
    }
    return $result if(! $self->exist( $model{accounts_id}));
    my $query = sprintf("UPDATE casp13.meeting_registration SET %s id=id WHERE (accounts_id = %d)", $set_query,  $model{accounts_id});
#	print "------------------------UPDATE: $model{id} \n";

#print  $query .  "<br>";
    my $sth = $self->{_database}->query($query);    
    if(defined($sth)) {
        $result = 1;
    }
    
    return $result;
}

sub add {
    my ($self, %model) = @_;
    my $result = 0;
    my $column_names = '';
    my $column_values = '';
    my $values_count = 0;

    while ( my ($key, $value) = each(%model) ) {	
	if($key eq 'id' || $key eq 'date' || $value eq '' || !defined($value)) {
		#print "NE---$key---------->$value<----------\n";
	}else{ 
		if($self->is_column($key)==1) {
			$column_names .= sprintf(" %s%s ",($values_count == 0)?'':',', $key);
			$column_values .= sprintf(" %s\'%s\' ",($values_count == 0)?'':',', $value);;
			$values_count++;
		}
	}
    }

    my $query = sprintf("INSERT INTO casp13.meeting_registration ( %s ) VALUES ( %s )", $column_names, $column_values);
#print "Content-Type: text/html; charset=ISO-8859-1\n\n";
#print  $query;

    my $tmp = $query;
#	print $query . "\n";

    my $sth = $self->{_database}->query($query);
#print "execute query \n";
    if(defined($sth)) {
        my $query = sprintf("SELECT MAX(id) FROM casp13.meeting_registration");
        my $sth = $self->{_database}->query($query);
        # add logger
        ($result) = $sth->fetchrow_array();
    } 
#	print "---------------------ADD: $result ";
    if ($result == 0) {
	#	print "\n" . $tmp . "\n";
    }

#print "add end \n";

    return $result;
}



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

sub delete_by_accounts_id {
    my ($self, $accounts_id) = @_;
    my $result = 0;    
    my $query = sprintf("DELETE FROM casp13.meeting_registration WHERE (accounts_id = %d)", $accounts_id);
#print "Content-Type: text/html; charset=ISO-8859-1\n\n";
#print $query;
    my $sth = $self->{_database}->query($query);
    if(defined($sth)) {
        $result = defined($sth) ? 1 : 0;
    }
    return $result;
}

sub get_meeting_registrations {
    my ($self, $params) = @_;
#print "Content-Type: text/html; charset=ISO-8859-1\n\n";
#print "<br>" . $params->{field} . "<br>";

    my $field = $params->{field};
    my $order = $params->{order};	
    	
    $field = (!defined($field) || ($field eq '')) ? 'mr.date' : $field;
    $order = (!defined($order) || ($order eq '')) ? 'DESC' : $order;

    #### DO MULTIPLE SORTING
    my $count_to_replace = 0;
	while ($field =~ s/, / $order,/ || $count_to_replace >20){
	$count_to_replace ++;
    }
    ####

    my $sub_query = "";
    my $domain = 0;
    my $target_name = ""; 


    if(defined($params->{id}) && ($params->{id} ne '')) {
 	$sub_query .= sprintf(" and mr.id=\'%s\' ", $params->{id});	
    }
    
    if(defined($params->{type}) && ($params->{type} ne '')) {
	if(($params->{type} eq 'registrations_list')) {
	    $sub_query .= sprintf(" and mr.id<>0 ");
	}
    }        

    my $right_join = "";
    
    if(defined($params->{accounts_id}) && ($params->{accounts_id} ne '')) {
 	$sub_query .= sprintf(" and ac.id=\'%s\' ", $params->{accounts_id});
	$right_join = sprintf(" RIGHT ");	
    }

    my @results = ();

    my $query = sprintf("SELECT mr.id, ac.id, ac.email, ac.firstname, ac.lastname, ac.initial, ac.gender, ac.institution, ac.street, ac.city, ac.state, ac.zip, ac.country_id, ac.phone, ac.fax, ac.url, ac.status, ac.description,
		mr.single_room, mr.vegetarian, mr.handicapped, mr.fee_waiver_support, mr.travel_grant, co.name, mr.comment, mr.accompany, mr.payment_type, mr.date, mr.poster, mr.poster_autor, mr.poster_title		
		FROM casp13.meeting_registration mr %s JOIN public.accounts ac on (mr.accounts_id = ac.id)
		LEFT JOIN public.countries co on (co.id = ac.country_id)
		WHERE 1=1 %s ORDER BY %s %s ", $right_join, $sub_query, $field, $order);


#print $query;

#FROM public.accounts ac LEFT JOIN casp13.meeting_registration mr on (mr.accounts_id = ac.id)
#		JOIN public.countries co on (co.id = ac.country_id)		
#		JOIN casp13.predictors pr on (ac.id = pr.accounts_id)
#		JOIN casp13.groups_predictors_relations gpr on (pr.id = gpr.predictors_id)
#		JOIN casp13.groups gr on (gr.id = gpr.groups_id)
#
    my $sth = $self->{_database}->query($query);

    #print $query;
    if(defined($sth) && ($sth->rows() > 0)) {
        my $index = 1;
#	my $index_a = 1;
#	my $stored_id = 0;
        while(my($id, $accounts_id, $email, $firstname, $lastname, $initial, $gender, $institution, $street, $city, $state, $zip, $country_id, $phone, $fax, $url, $status, $description, $single_room, $vegetarian, $handicapped, $fee_waiver_support, $travel_grant, $country, $comment, $accompany, $payment_type, $date, $poster, $poster_autor, $poster_title) = $sth->fetchrow_array()) {
	    #if($stored_id == $id && scalar(@results) > 0) {
	#	$results[scalar(@results) - 1]->{GROURS} = $results[scalar(@results) - 1]->{GROURS} . " !!! " . $group_name;
	#	print "<br>$index_a check stored<br>";
#		$index_a ++;
#	    } else {
		push(@results, {
		    INDEX => $index,
		    id => $id,
		    accounts_id => $accounts_id,
		    email => $email,
		    firstname => $firstname,
		    lastname => $lastname,
		    initial => $initial,
		    gender => $gender,
		    institution => $institution,
		    street => $street,
		    city => $city,
		    state => $state,
		    zip => $zip,
		    country => $country_id,
		    phone => $phone,
		    fax => $fax,
		    url => $url,
		    status => $status,
		    description => $description,				    
		    single_room => $single_room,
		    vegetarian => $vegetarian,
		    handicapped => $handicapped,
		    fee_waiver_support => $fee_waiver_support,
		    travel_grant => $travel_grant,
		    country => $country =~ s/,/ /,
		    comment => $comment,
		    accompany => $accompany,
		    payment_type => $payment_type,
		    poster => $poster,
		    poster_autor => $poster_autor,
		    poster_title => $poster_title,		    
		    GROUPS => $self->get_group_names ($accounts_id),
		    date => (defined($date) ? ((substr($date, 0, 10) . "<br>" . substr($date, 11, 8))) : '-')
		    
		    }
		);
		$index++;
#	    }	    
#	    $stored_id = $id;
        }
    }
    return @results;
}

sub get_group_names {
    my ($self, $accounts_id) = @_;
    
    my $group_names = "";
    
    my @results = ();
    my $query = sprintf("select pr.id, pr.accounts_id, gr.name
    FROM casp13.predictors pr
    JOIN casp13.groups_predictors_relations gpr on (pr.id = gpr.predictors_id)
    JOIN casp13.groups gr on (gr.id = gpr.groups_id)
    WHERE pr.accounts_id=%s ORDER BY pr.id ASC ,gr.name ASC", $accounts_id);

    #print $query;

    my $sth = $self->{_database}->query($query);

    if(defined($sth) && ($sth->rows() > 0)) {
        my $index = 1;
        while(my($pr_id, $accounts_id, $group) = $sth->fetchrow_array()) {
	    $group_names = sprintf("%s %s", $group_names, $group);
	}
    }
    
    return $group_names;
}


sub get_models {
    my ($self, $params) = @_;
    my $field = $params->{field};
    my $order = $params->{order};	
    #my $view_targets = $params->{view_targets};
    #my $view = $params->{view};
    #print "Content-Type: text/html; charset=ISO-8859-1\n\n";
    #foreach my $parameter (keys %{$params}) {
    #	print $parameter . " => " . $params->{$parameter}. "<br>";
    #}	
    $field = (!defined($field) || ($field eq '')) ? 'mr.id' : $field;
    $order = (!defined($order) || ($order eq '')) ? 'DESC' : $order;

    #### DO MULTIPLE SORTING
    my $count_to_replace = 0;
	while ($field =~ s/, / $order,/ || $count_to_replace >20){
	$count_to_replace ++;
    }
    ####
    
    my $domain = 0;
    my $sub_query = "";
    
    
    if(defined($params->{id}) && ($params->{id} ne '')) {
 	$sub_query .= sprintf(" and mr.id=\'%s\' ", $params->{id});	
    }
    
    my @results = ();
    #need to inmproved using get_meeting_registrations method using ID as parameter
#####    my $query = sprintf("SELECT gr.id, pr.target, pr.pfrmat, gr.code, pr.model, re.id, re.parent, re.domain, re.gdt_ts_4, re.rmsd_5, re.lga_s_5, re.alignment_4 FROM casp13.results re
#####		JOIN casp13.predictions pr ON  (re.predictions_id = pr.id) JOIN casp13.targets tr ON  (pr.target = tr.name) JOIN casp13.groups gr ON (pr.groups_id = gr.id) 
#####		WHERE 1=1 %s ORDER BY %s %s LIMIT 2500", $sub_query, $field, $order);
#####    my $sth = $self->{_database}->query($query);
#####
#####    #print $query;
#####
#####    if(defined($sth) && ($sth->rows() > 0)) {
#####        my $index = 1;
#####        while(my($group_id, $target, $pfrmat, $code, $model, $id, $parent, $domain, $gdt_ts_4, $rmsd_5, $lga_s_5, $alignment_4) = $sth->fetchrow_array()) {
#####	    push(@results, {
#####		INDEX => $index	
#####		}
#####	    );	
#####	    $index++;
#####        }
#####    }
    return @results;
}

sub ToString {
    my ($self, $PS, %model) = @_;
    # $PS -   print separator could be "\n" for text or "<br>" for HTML    
    while ( my ($key, $value) = each(%model) ) {
        print sprintf("%s => %s  %s ", $key, $value, $PS);
    }    
    return 0; 
}

1;