package Orangutan::Query;

use strict;

use Data::Dumper;

use DBI;
use Orangutan::Date;

my %cache = ( );

sub new {
    my $class = shift;

    my $self = { };

    my %args = ( );
    while ($#_ >= 0) {
        $args{lc(pop)} = pop;
    }

    bless $self, $class;

    my ($hostname, $database, $timeout);
    foreach my $arg (keys %args) {
        if (($arg eq 'host') ||
            ($arg eq 'hostname')) {
            $hostname = $args{$arg};
        } elsif
           (($arg eq 'db') ||
            ($arg eq 'database')) {
            $database = $args{$arg};
        } elsif
           (($arg eq 'user') ||
            ($arg eq 'username')) {
            $self->{'username'} = $args{$arg};
        } elsif
           (($arg eq 'pass') ||
            ($arg eq 'password')) {
            $self->{'password'} = $args{$arg};
        } elsif ($arg eq 'timeout') {
            $timeout = $args{$arg};
        } else {
            $main::logger->Log("unsupported query property: %s", $arg);
        }
    }

    if (defined($database)) {
        $self->{'dsn'} = "DBI:mysql:database=$database";
        if (defined($hostname)) {
            $self->{'dsn'} .= ";host=$hostname";
        }
        if (defined($timeout)) {
            $self->{'dsn'} .= ";mysql_connect_timeout=$timeout";
        }
        $self->{'dsn'} .= ";mysql_enable_utf8=1";
        return $self;
    } else {
        return undef;
    }
}

sub Connect($) {
    my $self = shift;
    if ($self->{'dbh'}->{'Active'}) {
        $self->{'keep'}++;
        return $self->{'dbh'};
    }
    if (($self->{'dbh'} = DBI->connect($self->{'dsn'}, $self->{'username'}, $self->{'password'}))) {
        return $self->{'dbh'};
    } else {
        $main::logger->Log("could not connect to database");
        return undef;
    }
}

sub Escape($$) {
    my ($self, $string) = @_;
    if ($self->{'dbh'}) {
        return $self->{'dbh'}->quote($string);
    }
    return 'NULL';
}

sub Query($$) {
    my ($self, $sql) = @_;
    if ($self->{'dbh'}) {
        $self->{'sth'} = $self->{'dbh'}->prepare($sql);
        if ($self->{'sth'}) {
            my $result = $self->{'sth'}->execute;
            if ($result) {
                if (defined($self->{'sth'}->{'NUM_OF_FIELDS'}) &&
                    ($self->{'sth'}->{'NUM_OF_FIELDS'} > 0)) {
                    $self->{'results'} = [ ];
                    while (defined(my $hashref = $self->{'sth'}->fetchrow_hashref)) {
                        push(@{$self->{'results'}}, $hashref);
                    }
                    if (scalar @{$self->{'results'}} > 0) {
                        $self->{'index'} = 0;
                    }
                } else {
                    $self->{'results'} = $result;
                }
                return $self->{'results'};
            } else {
                $main::logger->Log("failed to execute sql statement: %s", $self->{'sth'}->errstr);
            }
        } else {
            $main::logger->Log("could not prepare sql statement: %s", $self->{'dbh'}->errstr);
        }
    }
}

sub Rows($) {
    my $self = shift;
    return scalar @{$self->{'results'}};
}

sub First($) {
    my $self = shift;
    $self->{'index'} = 0;
    return $self->Next;
}

sub Next($) {
    my $self = shift;
    return $self->{'results'}[$self->{'index'}++];
}

sub GetRow($$) {
    my ($self, $index) = @_;
    return $self->{'results'}[$index];
}

sub Last($) {
    my $self = shift;
    $self->{'index'} = $#{$self->{'results'}};
    return $self->Next;
}

sub Disconnect($) {
    my $self = shift;
    if ($self->{'dbh'}) {
        if ($self->{'keep'}) {
            $self->{'keep'}--;
        } else {
            $self->{'dbh'}->disconnect;
        }
    }
}

sub TableExists($$) {
    my ($self, $table) = @_;

    my $exists = 0;
    if (defined($cache{'tables'}) && defined($cache{'tables'}->{$table})) {
        $exists = $cache{'tables'}->{$table};
    } elsif ($self->Connect) {
        my $dbname = $self->Escape($main::config->Get('db', 'database'));
        my $tablename = $self->Escape($table);
        if ($self->Query(qq{
            SELECT COUNT(*) AS exist
            FROM information_schema.tables
            WHERE table_schema = $dbname AND table_name = $tablename
            })) {
            if ($self->Rows > 0) {
                $exists = $self->Next->{'exist'};
            }
            if (!defined($cache{'tables'})) {
                $cache{'tables'} = { };
            }
            $cache{'tables'}->{$table} = $exists;
        }
        $self->Disconnect;
    }
    return $exists;
}

sub ColumnExists($$$) {
    my ($self, $table, $column) = @_;

    my $exists = 0;
    if (defined($cache{'columns'}) && defined($cache{'columns'}->{$table}) &&
        defined($cache{'columns'}->{$table}->{$column})) {
        $exists = $cache{'columns'}->{$table}->{$column};
    } elsif ($self->Connect) {
        my $dbname = $self->Escape($main::config->Get('db', 'database'));
        my $tablename = $self->Escape($table);
        my $columnname = $self->Escape($column);
        if ($self->Query(qq{
            SELECT COUNT(*) AS exist
            FROM information_schema.columns
            WHERE table_schema = $dbname AND table_name = $tablename AND column_name = $columnname
            })) {
            if ($self->Rows > 0) {
                $exists = $self->Next->{'exist'};
            }
            if (!defined($cache{'columns'})) {
                $cache{'columns'} = { };
            }
            if (!defined($cache{'columns'}->{$table})) {
                $cache{'columns'}->{$table} = { };
            }
            $cache{'columns'}->{$table}->{$column} = $exists;
        }
        $self->Disconnect;
    }
    return $exists;
}

sub UserExists($$) {
    my ($self, $username) = @_;

    my @result = ( );
    if ($self->Connect) {
        my $where;
        if ($main::config->Get('redmine', 'same_login')) {
            if ($username =~ /^([^@]+)\@[^@]+$/) {
                $username = $1;
            }
            $username = $self->Escape($username);
            $where = qq{ login = $username };
        } else {
            if (index($username, '@') <= 0) {
                $username .= '@%';
                $username = $self->Escape($username);
                $where = qq{ jabber LIKE $username };
            } else {
                $username = $self->Escape($username);
                $where = qq{ jabber = $username };
            }
        }
        if ($self->Query(qq{
            SELECT id, login
            FROM users
            WHERE $where AND status = 1 AND type = 'User'
            })) {
            if ($self->Rows > 0) {
                my $user = $self->Next;
                @result = ($user->{'id'}, $user->{'login'});
            }
        }
        $self->Disconnect;
    }
    return @result;
}

sub UserDetails($) {
    my $self = shift;

    my $rows = undef;
    if ($self->Connect) {
        if ($self->Query(qq{
            SELECT login, firstname, lastname, mail, jabber
            FROM users
            })) {
            $rows = $self->Rows;
        }
        $self->Disconnect;
    }
    return $rows;
}

sub ProjectExists($$) {
    my ($self, $name) = @_;

    my $projects = undef;
    if ($self->Connect) {
        $projects = '';
        my $project = $self->Escape($name);
        if ($self->Query(qq{
            SELECT id, name
            FROM projects
            WHERE name = $project AND status = 1
            })) {
            if ($self->Rows == 1) {
                my $result = $self->Next;
                $projects = {
                    id   => $result->{'id'},
                    name => $result->{'name'}
                };
            } elsif ($self->Rows > 1) {
                $projects = [ ];
                while (defined(my $result = $self->Next)) {
                    push(@{$projects}, {
                        id   => $result->{'id'},
                        name => $result->{'name'}
                    });
                }
            } elsif ($name =~ m!^([^ ]+?)(?: v(?:er(?:sion|\.?)|\.?)| ?[-/,])? ?([^ ]+)$!) {
                my $project = $self->Escape($1);
                my $version = $self->Escape($2);
                if ($self->Query(qq{
                    SELECT projects.id, projects.name,
                    versions.id AS version_id, versions.name AS version
                    FROM projects
                    JOIN versions ON project_id = projects.id
                    WHERE projects.name = $project AND projects.status = 1
                    AND versions.name = $version
                    })) {
                    if ($self->Rows == 1) {
                        my $result = $self->Next;
                        $projects = {
                            id      => $result->{'id'},
                            name    => $result->{'name'},
                            ver_id  => $result->{'version_id'},
                            version => $result->{'version'}
                        };
                    } elsif ($self->Rows > 1) {
                        $projects = [ ];
                        while (defined(my $result = $self->Next)) {
                            push(@{$projects}, {
                                id      => $result->{'id'},
                                name    => $result->{'name'},
                                ver_id  => $result->{'version_id'},
                                version => $result->{'version'}
                            });
                        }
                    }
                }
            }
        }
        $self->Disconnect;
    }
    return $projects;
}

sub ActivityExists($$;$) {
    my ($self, $name, $projectid) = @_;

    my $activity = undef;
    if ($self->Connect) {
        my $sql = '';
        $activity = '';
        $name = $self->Escape($name);
        if (defined($projectid)) {
            $sql = qq{
                SELECT IFNULL(child.id, parent.id) AS id, parent.name
                FROM enumerations parent
                LEFT JOIN enumerations child ON child.parent_id = parent.id AND child.project_id = $projectid
                WHERE parent.name = $name AND parent.type = 'TimeEntryActivity' AND parent.parent_id IS NULL AND
                ((child.active IS NULL AND parent.active = 1) OR (child.active IS NOT NULL AND child.active = 1))
            };
        } else {
            $sql = qq{
                SELECT MIN(id) AS id, name
                FROM enumerations
                WHERE name = $name AND type = 'TimeEntryActivity' AND active = 1
                GROUP BY name
            };
        }
        if ($self->Query($sql)) {
            if ($self->Rows > 0) {
                $activity = $self->Next->{'id'};
            }
        }
        $self->Disconnect;
    }
    return $activity;
}

sub IsWatching($$$) {
    my ($self, $userid, $watchid) = @_;
    my $id = undef;
    if ($self->Connect) {
        $id = 0;
        my $watchable_type;
        my $watchable_id;
        if (ref $watchid eq 'HASH') {
            $watchable_id = $watchid->{'id'};
            $watchable_type = 'Project';
        } else {
            $watchable_id = $watchid;
            $watchable_type = 'Issue';
        }
        if ($self->Query(qq{
            SELECT id
            FROM watchers
            WHERE watchable_type = '$watchable_type' AND watchable_id = $watchable_id AND user_id = $userid
            })) {
            if ($self->Rows > 0) {
                $id = $self->Next->{'id'};
            }
        }
        $self->Disconnect;
    }
    return $id;
}

sub Can($$$$) {
    my ($self, $query, $user, $projectid) = @_;
    my $result = 0;
    if ($self->Connect) {
        my $employeeid;
        if (ref $user eq 'Orangutan::User') {
            $employeeid = $user->GetUserID;
        } else {
            $employeeid = $user;
        }
        if ($self->Query(qq{
            SELECT role_id
            FROM members
            JOIN member_roles ON member_id = members.id
            JOIN roles ON roles.id = role_id
            WHERE user_id = $employeeid AND project_id = $projectid AND permissions LIKE '%- :$query\n%';
            })) {
            $result = $self->Rows;
        }
        $self->Disconnect;
    }
    return $result;
}

sub GetProject($$) {
    my ($self, $projectid) = @_;
    my $project = undef;
    if ($self->Connect) {
        if ($self->Query(qq{
            SELECT id, name
            FROM projects
            WHERE id = $projectid
            })) {
            if ($self->Rows > 0) {
                $project = $self->Next;
            }
        }
        $self->Disconnect;
    }
    return $project;
}

sub GetProjects($;$) {
    my ($self, $projectid) = @_;
    my $rows = undef;
    if ($self->Connect) {
        my $and = '';
        if (defined($projectid)) {
            $and = qq{ AND project.parent_id = $projectid };
        } else {
            $and = qq{ AND project.parent_id IS NULL };
        }
        if ($self->Query(qq{
            SELECT project.name AS project, parent.name AS parent,
            (SELECT COUNT(*) FROM projects WHERE parent_id = project.id) AS subprojects,
            UNIX_TIMESTAMP(MAX(time_entries.spent_on)) AS end_time
            FROM projects project
            LEFT JOIN projects parent ON parent.id = project.parent_id
            LEFT JOIN time_entries ON time_entries.project_id = project.id
            WHERE project.status = 1
            $and
            GROUP BY project
            ORDER BY project
            })) {
            $rows = $self->Rows;
        }
        $self->Disconnect;
    }
    return $rows;
}

sub GetTrackers($) {
    my $self = shift;
    my $rows = undef;
    if ($self->Connect) {
        if ($self->Query(qq{
            SELECT id, name
            FROM trackers
            ORDER BY name
            })) {
            $rows = $self->Rows;
        }
        $self->Disconnect;
    }
    return $rows;
}

sub GetStatuses($) {
    my $self = shift;
    my $rows = undef;
    if ($self->Connect) {
        if ($self->Query(qq{
            SELECT id, name
            FROM issue_statuses
            ORDER BY name
            })) {
            $rows = $self->Rows;
        }
        $self->Disconnect;
    }
    return $rows;
}

sub GetPriorities($) {
    my $self = shift;
    my $rows = undef;
    if ($self->Connect) {
        if ($self->Query(qq{
            SELECT id, name
            FROM enumerations
            WHERE type = 'IssuePriority' AND parent_id IS NULL AND active = 1
            ORDER BY name
            })) {
            $rows = $self->Rows;
        }
        $self->Disconnect;
    }
    return $rows;
}

sub GetCustomFields($) {
    my $self = shift;
    my $rows = undef;
    if ($self->Connect) {
        if ($self->Query(qq{
            SELECT id, name
            FROM custom_fields
            WHERE type = 'IssueCustomField'
            ORDER BY name
            })) {
            $rows = $self->Rows;
        }
        $self->Disconnect;
    }
    return $rows;
}

sub GetUserPreferences($$) {
    my ($self, $userid) = @_;
    my $preferences = undef;
    if ($self->Connect) {
        if ($self->Query(qq{
            SELECT id, others
            FROM user_preferences
            WHERE user_id = $userid
            })) {
            $preferences = { };
            if ($self->Rows > 0) {
                my $row = $self->Next;
                my $others = $row->{'others'};
                my $option = undef;
                while ($others =~ /^(.*)$/mg) {
                    my $line = $1;
                    if ($line =~ /^:([a-z_0-9]+): (.*)$/i) {
                        $option = $1;
                        $preferences->{$option} = $2;
                    } elsif (defined($option)) {
                        $preferences->{$option} .= "\n";
                        $preferences->{$option} .= $line;
                    }
                }
                $preferences->{':id'} = $row->{'id'};
            }
        }
        $self->Disconnect;
    }
    return $preferences;
}

sub GetIssue($$) {
    my ($self, $issueid) = @_;
    my $issue = undef;
    if ($self->Connect) {
        if ($self->Query(qq{
            SELECT issues.id, tracker_id, issues.project_id, subject, issues.description,
            due_date, category_id, status_id, issues.assigned_to_id, priority_id, author_id,
            UNIX_TIMESTAMP(issues.created_on) AS created_on, UNIX_TIMESTAMP(issues.updated_on) AS updated_on,
            start_date, done_ratio, estimated_hours,
            trackers.name AS tracker_name, projects.name AS project_name,
            issue_categories.name AS category_name, issue_statuses.name AS status_name, is_closed,
            users.login AS assigned_to_name, enumerations.name AS priority_name,
            authors.login AS author_name
            FROM issues
            JOIN trackers ON trackers.id = tracker_id
            JOIN projects ON projects.id = project_id
            LEFT JOIN issue_categories ON issue_categories.id = category_id
            JOIN issue_statuses ON issue_statuses.id = status_id
            LEFT JOIN users users ON users.id = issues.assigned_to_id
            JOIN enumerations ON enumerations.id = priority_id
            JOIN users authors ON authors.id = author_id
            WHERE issues.id = $issueid
            })) {
            if ($self->Rows > 0) {
                $issue = $self->Next;
            }
        }
        $self->Disconnect;
    }
    return $issue;
}

sub GetIssueEx($$) {
    my ($self, $issueid) = @_;
    my $issue = undef;
    if ($self->Connect) {
        my $extras = '';
        if ($self->TableExists('votes')) {
            $extras .= qq{ ,
                (SELECT COUNT(*)
                FROM votes
                WHERE voteable_type = 'Issue' AND voteable_id = issues.id)
                AS votes
            };
        }
        if ($self->Query(qq{
            SELECT issues.id, issues.parent_id,
            (SELECT COUNT(*) FROM issues childs WHERE childs.parent_id = issues.id) AS childs,
            trackers.name AS tracker_name, issues.project_id, projects.name AS project_name,
            subject, issues.description, UNIX_TIMESTAMP(due_date) AS due_date, issue_categories.name AS category_name,
            issue_statuses.name AS status_name, is_closed, users.login AS assigned_to_name,
            enumerations.name AS priority_name, authors.login AS author_name,
            UNIX_TIMESTAMP(issues.created_on) AS created_on, UNIX_TIMESTAMP(issues.updated_on) AS updated_on,
            UNIX_TIMESTAMP(start_date) AS start_date, done_ratio, ROUND(estimated_hours * 3600) AS estimated_hours,
            (SELECT SUM(ROUND(hours * 3600)) FROM time_entries WHERE issue_id = issues.id) AS spent_time,
            (SELECT COUNT(*) FROM journals WHERE journalized_type = 'Issue' AND journalized_id = issues.id
            AND notes IS NOT NULL AND notes != '') AS notes
            $extras
            FROM issues
            JOIN trackers ON trackers.id = tracker_id
            JOIN projects ON projects.id = issues.project_id
            LEFT JOIN issue_categories ON issue_categories.id = category_id
            JOIN issue_statuses ON issue_statuses.id = status_id
            LEFT JOIN users users ON users.id = issues.assigned_to_id
            JOIN enumerations ON enumerations.id = priority_id
            LEFT JOIN versions ON versions.id = fixed_version_id
            JOIN users authors ON authors.id = author_id
            WHERE issues.id = $issueid
            })) {
            if ($self->Rows > 0) {
                $issue = $self->Next;
            }
        }
        $self->Disconnect;
    }
    return $issue;
}

sub GetBestIssues($$;$$) {
    my ($self, $user, $projectid, $date) = @_;
    my $rows = undef;
    if ($self->Connect) {
        my $and = '';
        my $employeeid = $user->GetUserID;
        if (defined($projectid)) {
            $and = qq{ AND issues.project_id = $projectid };
        }
        if (!defined($date)) {
            $date = new Orangutan::Date;
        }
        my $currentdate = $self->Escape($date->DateTime('date'));
        if ($self->Query(qq{
            SELECT issues.id, trackers.name AS tracker_name, projects.name AS project_name,
            subject, issue_statuses.name AS status_name, enumerations.name AS priority_name
            FROM issues
            JOIN trackers ON trackers.id = tracker_id
            JOIN projects ON projects.id = project_id
            JOIN issue_statuses ON issue_statuses.id = status_id
            JOIN enumerations ON enumerations.id = priority_id
            JOIN members ON members.project_id = issues.project_id
            JOIN member_roles ON member_id = members.id
            JOIN roles ON roles.id = role_id
            WHERE user_id = $employeeid AND permissions LIKE '%- :edit_time_entries\n%'
            AND (assigned_to_id IS NULL OR assigned_to_id = $employeeid)
            AND done_ratio < 100 AND is_closed = 0
            AND (start_date IS NULL OR start_date <= $currentdate)
            $and
            GROUP BY issues.id
            ORDER BY assigned_to_id DESC,
            DATEDIFF($currentdate, IFNULL(due_date, $currentdate)) DESC,
            enumerations.position DESC, done_ratio, issue_statuses.position, issues.id
            })) {
            $rows = $self->Rows;
        }
        $self->Disconnect;
    }
    return $rows;
}

sub GetIssuesEx($$$$$$$$$;$) {
    my ($self, $user, $userid, $projectid, $versionid, $issueid, $trackerid, $statusid, $priorityid, %options) = @_;
    my $rows = undef;
    if ($self->Connect) {
        my @clause = ( );
        if (!defined($userid)) {
            if (!$user->IsCreator && !$user->IsManager) {
                $userid = $user->GetUserID;
                push(@clause, qq{ user_id = $userid });
                push(@clause, qq{ permissions LIKE '%- :view_issues\n%' });
            }
            push(@clause, qq{ issues.assigned_to_id IS NULL });
        } elsif ($userid eq '') {
            $userid = $user->GetUserID;
            if (!$user->IsCreator && !$user->IsManager) {
                push(@clause, qq{ user_id = $userid });
                push(@clause, qq{ permissions LIKE '%- :view_issues\n%' });
            }
            if (!defined($options{'all'})) {
                push(@clause, qq{ (issues.assigned_to_id = $userid OR issues.assigned_to_id IS NULL) });
            }
        } else {
            push(@clause, qq{ user_id = $userid });
            push(@clause, qq{ permissions LIKE '%- :view_issues\n%' });
            push(@clause, qq{ issues.assigned_to_id = $userid });
        }
        if (defined($projectid)) {
            push(@clause, qq{ projects.lft BETWEEN project.lft AND project.rgt });
            if (defined($versionid)) {
                push(@clause, qq{ issues.fixed_version_id = $versionid });
            }
        }
        if (defined($issueid)) {
            push(@clause, qq{ issues.lft BETWEEN (parent.lft + 1) AND parent.rgt });
        }
        if (defined($trackerid)) {
            push(@clause, qq{ issues.tracker_id = $trackerid });
        }
        if (!defined($statusid)) {
            push(@clause, qq{ is_closed = 0 });
        } elsif ($statusid eq '') {
            push(@clause, qq{ is_closed = 1 });
        } else {
            push(@clause, qq{ issues.status_id = $statusid });
        }
        if (defined($priorityid)) {
            push(@clause, qq{ issues.priority_id = $priorityid });
        }
        if (defined($options{'actual'}) || defined($options{'overdue'})) {
            my $date = new Orangutan::Date;
            my $currentdate = $self->Escape($date->DateTime('date'));
            if (defined($options{'actual'})) {
                push(@clause, qq{ (issues.start_date IS NULL OR issues.start_date <= $currentdate) });
                push(@clause, qq{ (issues.due_date IS NULL OR issues.due_date >= $currentdate) });
            } else {
                push(@clause, qq{ issues.due_date IS NOT NULL });
                push(@clause, qq{ issues.due_date < $currentdate });
            }
        }
        if (defined($options{'overtimed'})) {
            push(@clause, qq{ issues.estimated_hours IS NOT NULL });
            push(@clause, qq{ (SELECT SUM(hours) FROM time_entries WHERE issue_id = issues.id) > issues.estimated_hours });
        }
        my $join = '';
        if (defined($userid)) {
            $join .= qq{ JOIN members ON members.project_id = issues.project_id };
            $join .= qq{ JOIN member_roles ON member_id = members.id };
            $join .= qq{ JOIN roles ON roles.id = role_id };
        }
        if (defined($projectid)) {
            $join .= qq{ JOIN projects project ON project.id = $projectid };
        }
        if (defined($issueid)) {
            $join .= qq{ JOIN issues parent ON parent.id = $issueid };
        }
        my $where = '';
        if (scalar @clause > 0) {
            $where = 'WHERE ';
            $where .= join(' AND ', @clause);
        }
        if ($self->Query(qq{
            SELECT issues.id, issues.parent_id, issues.lft, issues.rgt, trackers.name AS tracker_name,
            issues.project_id, projects.name AS project_name,
            issues.subject, issue_statuses.name AS status_name, enumerations.name AS priority_name
            FROM issues
            JOIN trackers ON trackers.id = issues.tracker_id
            JOIN projects ON projects.id = issues.project_id
            JOIN issue_statuses ON issue_statuses.id = issues.status_id
            JOIN enumerations ON enumerations.id = issues.priority_id
            $join
            $where
            GROUP BY issues.id
            ORDER BY enumerations.position DESC, issues.id
            })) {
            $rows = $self->Rows;
        }
        $self->Disconnect;
    }
    return $rows;
}

sub GetRelatedIssues($$) {
    my ($self, $issueid) = @_;
    my $rows = undef;
    if ($self->Connect) {
        if ($self->Query(qq{
            SELECT relation_type,
            (CASE issue_from_id WHEN $issueid THEN issue_from_id ELSE issue_to_id END) AS issue_from_id,
            (CASE issue_from_id WHEN $issueid THEN issue_to_id ELSE issue_from_id END) AS issue_to_id,
            name AS tracker_name, subject, delay
            FROM issue_relations
            JOIN issues ON issues.id = (CASE issue_from_id WHEN $issueid THEN issue_to_id ELSE issue_from_id END)
            JOIN trackers ON trackers.id = tracker_id
            WHERE issue_from_id = $issueid OR issue_to_id = $issueid
            })) {
            $rows = $self->Rows;
        }
        $self->Disconnect;
    }
    return $rows;
}

sub GetSubissues($$) {
    my ($self, $issueid) = @_;
    my $rows = undef;
    if ($self->Connect) {
        if ($self->Query(qq{
            SELECT issues.id, trackers.name AS tracker_name, projects.name AS project_name,
            subject, issue_statuses.name AS status_name, enumerations.name AS priority_name
            FROM issues
            JOIN trackers ON trackers.id = tracker_id
            JOIN projects ON projects.id = project_id
            JOIN issue_statuses ON issue_statuses.id = status_id
            JOIN enumerations ON enumerations.id = priority_id
            WHERE issues.parent_id = $issueid
            ORDER BY issues.id
            })) {
            $rows = $self->Rows;
        }
        $self->Disconnect;
    }
    return $rows;
}

sub GetChangedIssues($$;$) {
    my ($self, $userid, $propkey) = @_;
    my $rows = undef;
    if ($self->Connect) {
        my $prop = '';
        if (defined($propkey)) {
            $prop = qq{ AND prop_key = 'done_ratio' };
        }
        my $status;
        my $join = '';
        my $statuses = $main::config->Get('statuses', 'progress');
        if (defined($statuses)) {
            if (ref $statuses eq 'ARRAY') {
                my $list = join(', ', @{$statuses});
                $status = qq{ AND status_id IN ($list) };
            } else {
                $status = qq{ AND status_id = $statuses };
            }
        } else {
            $join = qq{ JOIN issue_statuses ON issue_statuses.id = status_id };
            $status = qq{ AND is_closed = 0 };
        }
        my $date = new Orangutan::Date;
        my $currentdate = $self->Escape($date->DateTime('date'));
        if ($self->Query(qq{
            SELECT journalized_id AS issue_id
            FROM journals
            JOIN journal_details ON journal_id = journals.id
            JOIN issues ON issues.id = journalized_id
            $join
            WHERE journalized_type = 'Issue' AND user_id = $userid
            AND property = 'attr' $prop
            AND (assigned_to_id IS NULL OR assigned_to_id = $userid)
            AND (start_date IS NULL OR start_date <= $currentdate)
            AND done_ratio < 100 $status
            GROUP BY issue_id
            })) {
            $rows = $self->Rows;
        }
        $self->Disconnect;
    }
    return $rows;
}

sub GetVote($$$) {
    my ($self, $issueid, $userid) = @_;
    my $vote = undef;
    if ($self->Connect) {
        if ($self->Query(qq{
            SELECT id, IF(vote = 0, -1, 1) AS vote
            FROM votes
            WHERE voteable_type = 'Issue' AND voteable_id = $issueid AND user_id = $userid
            })) {
            if ($self->Rows > 0) {
                $vote = $self->Next;
            }
        }
        $self->Disconnect;
    }
    return $vote;
}

sub GetVotes($$) {
    my ($self, $issueid, $userid) = @_;
    my $vote = undef;
    if ($self->Connect) {
        my $you = '';
        if ($userid) {
            $you = qq{ ,
                (SELECT IF(vote = 0, -1, 1)
                FROM votes
                WHERE voteable_type = 'Issue' AND voteable_id = $issueid AND user_id = $userid) AS you
            };
        }
        if ($self->Query(qq{
            SELECT SUM(IF(vote = 0, -1, 1)) AS vote, SUM(vote) AS yes, SUM(IF(vote = 0, 1, 0)) AS no
            $you
            FROM votes
            WHERE voteable_type = 'Issue' AND voteable_id = $issueid
            })) {
            if ($self->Rows > 0) {
                $vote = $self->Next;
            }
        }
        $self->Disconnect;
    }
    return $vote;
}

sub GetAttachments($$) {
    my ($self, $issueid) = @_;
    my $rows = undef;
    if ($self->Connect) {
        if ($self->Query(qq{
            SELECT id, filename, filesize, description
            FROM attachments
            WHERE container_type = 'Issue' AND container_id = $issueid
            })) {
            $rows = $self->Rows;
        }
        $self->Disconnect;
    }
    return $rows;
}

sub GetWatcher($$) {
    my ($self, $watcherid) = @_;
    my $watcher = undef;
    if ($self->Connect) {
        if ($self->Query(qq{
            SELECT watchable_type, watchable_id, user_id, login
            FROM watchers
            LEFT JOIN users ON users.id = user_id
            WHERE watchers.id = $watcherid
            })) {
            if ($self->Rows > 0) {
                $watcher = $self->Next;
            }
        }
        $self->Disconnect;
    }
    return $watcher;
}

sub GetWatchers($@) {
    my ($self, @ids) = @_;
    my $rows = undef;
    if ($self->Connect) {
        my $join = '';
        my $where = '';
        for (my $i = 0; $i < scalar @ids; $i++) {
            if (scalar @ids > 1) {
                $where .= qq{ ( };
            }
            if (ref $ids[$i] eq 'HASH') {
                my $id = $ids[$i]->{'id'};
                $where .= qq{
                    watchable_type = 'Project' AND
                    (SELECT lft FROM projects WHERE id = $id)
                    BETWEEN lft AND rgt
                };
                if (!$join) {
                    $join = qq{ JOIN projects ON projects.id = watchable_id };
                }
            } else {
                $where .= qq{ watchable_type = 'Issue' AND watchable_id = };
                $where .= $ids[$i];
            }
            if (scalar @ids > 1) {
                $where .= qq{ ) };
            }
            if ($i < $#ids) {
                $where .= qq{ OR };
            }
        }
        if ($self->Query(qq{
            SELECT login
            FROM watchers
            JOIN users ON users.id = user_id
            $join
            WHERE $where
            ORDER BY login
            })) {
            $rows = $self->Rows;
        }
        $self->Disconnect;
    }
    return $rows;
}

sub GetNotes($$) {
    my ($self, $issueid) = @_;
    my $rows = undef;
    if ($self->Connect) {
        if ($self->Query(qq{
            SELECT user_id, users.login AS user_name,
            notes, UNIX_TIMESTAMP(journals.created_on) AS created_on
            FROM journals
            JOIN users ON users.id = user_id
            WHERE  notes IS NOT NULL AND notes != '' AND
            journalized_type = 'Issue' AND journalized_id = $issueid
            ORDER BY created_on
            })) {
            $rows = $self->Rows;
        }
        $self->Disconnect;
    }
    return $rows;
}

sub GetWatched($$;$) {
    my ($self, $userid, $type) = @_;
    my $rows = undef;
    if ($self->Connect) {
        my $where;
        if (defined($type)) {
            if (lc($type) eq 'issues') {
                $where = qq{ watchable_type = 'Issue' };
            } else {
                $where = qq{ watchable_type = 'Project' };
            }
        } else {
            $where = qq{ (watchable_type = 'Issue' OR watchable_type = 'Project') };
        }
        if ($self->Query(qq{
            SELECT watchable_type, watchable_id,
            CASE watchable_type
            WHEN 'Project' THEN (SELECT name FROM projects WHERE id = watchable_id)
            WHEN 'Issue' THEN (SELECT subject FROM issues WHERE id = watchable_id)
            ELSE NULL END AS title
            FROM watchers
            WHERE $where AND user_id = $userid
            ORDER BY CASE watchable_type WHEN 'Project' THEN title ELSE watchable_id END
            })) {
            $rows = $self->Rows;
        }
        $self->Disconnect;
    }
    return $rows;
}

sub GetDefaultActivity($$) {
    my ($self, $projectid) = @_;
    my $activity = undef;
    if ($self->Connect) {
        if ($self->Query(qq{
            SELECT IFNULL(child.id, parent.id) AS id, parent.name
            FROM enumerations parent
            LEFT JOIN enumerations child ON child.parent_id = parent.id AND child.project_id = $projectid
            WHERE parent.type = 'TimeEntryActivity' AND parent.parent_id IS NULL AND
            ((child.active IS NULL AND parent.active = 1) OR (child.active IS NOT NULL AND child.active = 1))
            ORDER BY parent.is_default DESC, parent.position
            LIMIT 1
            })) {
            if ($self->Rows > 0) {
                $activity = $self->Next;
            }
        }
        $self->Disconnect;
    }
    return $activity;
}

sub GetActivities($$) {
    my ($self, $projectid) = @_;
    my $rows = undef;
    if ($self->Connect) {
        if ($self->Query(qq{
            SELECT IFNULL(child.id, parent.id) AS id, parent.name
            FROM enumerations parent
            LEFT JOIN enumerations child ON child.parent_id = parent.id AND child.project_id = $projectid
            WHERE parent.type = 'TimeEntryActivity' AND parent.parent_id IS NULL AND
            ((child.active IS NULL AND parent.active = 1) OR (child.active IS NOT NULL AND child.active = 1))
            ORDER BY parent.position
            })) {
            $rows = $self->Rows;
        }
        $self->Disconnect;
    }
    return $rows;
}

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

    my $event = undef;
    if ($self->Connect) {
        $event = '';
        if ($self->Query(qq{
            SELECT time_entries.id, time_entries.project_id, user_id, issue_id, comments, activity_id,
            UNIX_TIMESTAMP(start_time) AS start_time, UNIX_TIMESTAMP(end_time) AS end_time,
            UNIX_TIMESTAMP(spent_on) AS spent_on, ROUND(hours * 3600) AS hours,
            projects.name AS project, enumerations.name AS activity,
            UNIX_TIMESTAMP(time_entries.created_on) AS created_on
            FROM time_entries
            LEFT JOIN projects ON projects.id = time_entries.project_id
            LEFT JOIN enumerations ON enumerations.id = activity_id
            WHERE time_entries.id = $id
            })) {
            if ($self->Rows > 0) {
                $event = $self->Next;
            }
        }
        $self->Disconnect;
    }
    return $event;
}

sub GetLastEntry($$;$) {
    my ($self, $user, $date) = @_;

    my $event = undef;
    if ($self->Connect) {
        $event = '';
        my $and = '';
        my $employeeid = $user->GetUserID;
        if (defined($date)) {
            my $start_time = $self->Escape($date->DateTime);
            $and = qq{ AND start_time < $start_time };
        }
        if ($self->Query(qq{
            SELECT time_entries.id, time_entries.project_id, user_id, issue_id, comments, activity_id,
            UNIX_TIMESTAMP(start_time) AS start_time, UNIX_TIMESTAMP(end_time) AS end_time,
            projects.name AS project, enumerations.name AS activity
            FROM time_entries
            LEFT JOIN projects ON projects.id = time_entries.project_id
            LEFT JOIN enumerations ON enumerations.id = activity_id
            WHERE user_id = $employeeid AND start_time IS NOT NULL AND end_time IS NOT NULL $and
            ORDER BY end_time DESC
            LIMIT 1
            })) {
            if ($self->Rows > 0) {
                $event = $self->Next;
            }
        }
        $self->Disconnect;
    }
    return $event;
}

sub GetEntries($$$$) {
    my ($self, $user, $start, $end) = @_;
    my $rows = undef;
    if ($self->Connect) {
        my $employeeid = $user->GetUserID;
        my $startdate = $self->Escape($start->DateTime);
        my $enddate = $self->Escape($end->DateTime);
        if ($self->Query(qq{
            SELECT time_entries.id, time_entries.project_id, user_id, issue_id, comments, activity_id,
            UNIX_TIMESTAMP(start_time) AS start_time, UNIX_TIMESTAMP(end_time) AS end_time,
            projects.name AS project, enumerations.name AS activity
            FROM time_entries
            LEFT JOIN projects ON projects.id = time_entries.project_id
            LEFT JOIN enumerations ON enumerations.id = activity_id
            WHERE user_id = $employeeid AND start_time IS NOT NULL AND end_time IS NOT NULL
            AND start_time < $enddate AND end_time > $startdate
            ORDER BY start_time
            })) {
            $rows = $self->Rows;
        }
        $self->Disconnect;
    }
    return $rows;
}

sub GetEntriesEx($$$$$$$) {
    my ($self, $userid, $start, $end, $projectid, $issue, $activityid) = @_;
    my $rows = undef;
    if ($self->Connect) {
        my $and = '';
        my $employeeid = $userid;
        my $startdate = $self->Escape($start->DateTime);
        my $enddate = $self->Escape($end->DateTime);
        if (defined($projectid)) {
            $and .= qq{ AND time_entries.project_id = $projectid };
        }
        if (defined($issue)) {
            $and .= qq{ AND issue_id = $issue };
        }
        if (defined($activityid)) {
            if (defined($projectid)) {
                $and .= qq{ AND activity_id = $activityid };
            } else {
                $and .= qq{ AND (activity_id = $activityid OR enumerations.parent_id = $activityid ) };
            }
        }
        if ($self->Query(qq{
            SELECT time_entries.id, time_entries.project_id, user_id, issue_id, comments, activity_id,
            UNIX_TIMESTAMP(start_time) AS start_time, UNIX_TIMESTAMP(end_time) AS end_time,
            CASE WHEN start_time IS NULL AND end_time IS NULL THEN ROUND(hours * 3600) ELSE TIMESTAMPDIFF(SECOND,
            CASE WHEN start_time < $startdate THEN $startdate ELSE start_time END,
            CASE WHEN end_time > $enddate THEN $enddate ELSE end_time END) END AS duration,
            projects.name AS project, enumerations.name AS activity,
            CASE WHEN start_time IS NOT NULL THEN UNIX_TIMESTAMP(start_time)
            ELSE UNIX_TIMESTAMP(spent_on) END AS order_time
            FROM time_entries
            LEFT JOIN projects ON projects.id = time_entries.project_id
            LEFT JOIN enumerations ON enumerations.id = activity_id
            WHERE user_id = $employeeid
            AND ((start_time IS NOT NULL AND end_time IS NOT NULL
            AND start_time < $enddate AND end_time > $startdate)
            OR (start_time IS NULL AND end_time IS NULL
            AND DATE_ADD(spent_on, INTERVAL 6 HOUR) >= $startdate
            AND DATE_ADD(spent_on, INTERVAL 6 HOUR) < $enddate))
            $and
            ORDER BY order_time
            })) {
            $rows = $self->Rows;
        }
        $self->Disconnect;
    }
    return $rows;
}

sub GetWorkHours($$$$) {
    my ($self, $user, $start, $end) = @_;
    my $hours = undef;
    if ($self->Connect) {
        my $employeeid = $user->GetUserID;
        my $starttime = $self->Escape($start->DateTime);
        my $startdate = $self->Escape($start->DateTime('date'));
        my $endtime = $self->Escape($end->DateTime);
        my $enddate = $self->Escape($end->DateTime('date'));
        if ($self->Query(qq{
            SELECT SUM(
            CASE WHEN start_time IS NULL AND end_time IS NULL THEN ROUND(hours * 3600) ELSE TIMESTAMPDIFF(SECOND,
            CASE WHEN start_time < $startdate THEN $startdate ELSE start_time END,
            CASE WHEN end_time > $enddate THEN $enddate ELSE end_time END) END) AS duration
            FROM time_entries
            WHERE user_id = $employeeid
            AND ((start_time IS NOT NULL AND end_time IS NOT NULL
            AND start_time < $enddate AND end_time > $startdate)
            OR (start_time IS NULL AND end_time IS NULL
            AND DATE_ADD(spent_on, INTERVAL 6 HOUR) >= $startdate
            AND DATE_ADD(spent_on, INTERVAL 6 HOUR) < $enddate))
            })) {
            if ($self->Rows > 0) {
                $hours = $self->Next->{'duration'};
            }
        }
        $self->Disconnect;
    }
    return $hours;
}

sub GetWorkHoursEx($$$$$$) {
    my ($self, $userid, $start, $end, $projectid, $activityid) = @_;
    my $rows = undef;
    if ($self->Connect) {
        my $and = '';
        my $employeeid = $userid;
        my $startdate = $self->Escape($start->DateTime);
        my $enddate = $self->Escape($end->DateTime);
        if (defined($projectid)) {
            $and .= qq{ AND time_entries.project_id = $projectid };
        }
        if (defined($activityid)) {
            $and .= qq{ AND activity_id = $activityid };
        }
        if ($self->Query(qq{
            SELECT time_entries.project_id, activity_id,
            projects.name AS project, enumerations.name AS activity,
            SUM(CASE WHEN start_time IS NULL AND end_time IS NULL THEN ROUND(hours * 3600) ELSE TIMESTAMPDIFF(SECOND,
            CASE WHEN start_time < $startdate THEN $startdate ELSE start_time END,
            CASE WHEN end_time > $enddate THEN $enddate ELSE end_time END) END) AS duration
            FROM time_entries
            LEFT JOIN projects ON projects.id = time_entries.project_id
            LEFT JOIN enumerations ON enumerations.id = activity_id
            WHERE user_id = $employeeid
            AND ((start_time IS NOT NULL AND end_time IS NOT NULL
            AND start_time < $enddate AND end_time > $startdate)
            OR (start_time IS NULL AND end_time IS NULL
            AND DATE_ADD(spent_on, INTERVAL 6 HOUR) >= $startdate
            AND DATE_ADD(spent_on, INTERVAL 6 HOUR) < $enddate))
            $and
            GROUP BY time_entries.project_id, activity_id
            ORDER BY time_entries.project_id, activity_id
            })) {
            $rows = $self->Rows;
        }
        $self->Disconnect;
    }
    return $rows;
}

sub GetWorkHoursPerDay($$$$$) {
    my ($self, $userid, $start, $end, $projectid) = @_;
    my $rows = undef;
    if ($self->Connect) {
        my $and = '';
        my $employeeid = $userid;
        my $startdate = $self->Escape($start->DateTime);
        my $enddate = $self->Escape($end->DateTime);
        if (defined($projectid)) {
            $and .= qq{ AND project_id = $projectid };
        }
        if ($self->Query(qq{
            SELECT UNIX_TIMESTAMP(spent_on) AS spent_on, SUM(ROUND(hours * 3600)) AS duration
            FROM time_entries
            WHERE user_id = $employeeid
            AND spent_on >= $startdate AND spent_on <= $enddate
            $and
            GROUP BY spent_on
            ORDER BY spent_on
            })) {
            $rows = $self->Rows;
        }
        $self->Disconnect;
    }
    return $rows;
}

sub GetTasksReport($$$$$) {
    my ($self, $userid, $start, $end, $projectid) = @_;
    my $rows = undef;
    if ($self->Connect) {
        my $and = '';
        my $employeeid = $userid;
        my $startdate = $self->Escape($start->DateTime);
        my $enddate = $self->Escape($end->DateTime);
        if (defined($projectid)) {
            $and .= qq{ AND time_entries.project_id = $projectid };
        }
        if ($self->Query(qq{
            SELECT UNIX_TIMESTAMP(MIN(spent_on)) AS spent_on,
            time_entries.project_id, projects.name AS project_name,
            trackers.name AS tracker, issue_id, subject,
            comments, SUM(ROUND(hours * 3600)) AS hours
            FROM time_entries
            JOIN projects ON projects.id = time_entries.project_id
            LEFT JOIN issues ON issues.id = issue_id
            LEFT JOIN trackers ON trackers.id = tracker_id
            WHERE user_id = $employeeid
            AND spent_on >= $startdate AND spent_on <= $enddate
            $and
            GROUP BY time_entries.project_id, issue_id, comments
            ORDER BY spent_on
            })) {
            $rows = $self->Rows;
        }
        $self->Disconnect;
    }
    return $rows;
}

sub GetActivitiesReport($$$) {
    my ($self, $userid, $issueid) = @_;
    my $rows = undef;
    if ($self->Connect) {
        my $employeeid = $userid;
        if ($self->Query(qq{
            (SELECT name, SUM(ROUND(hours * 3600)) AS hours,
            UNIX_TIMESTAMP(MIN(spent_on)) AS start_date, UNIX_TIMESTAMP(MAX(spent_on)) AS end_date
            FROM time_entries
            LEFT JOIN enumerations ON enumerations.id = activity_id
            WHERE issue_id = $issueid AND user_id = $employeeid
            GROUP BY name
            ORDER BY position)
            UNION ALL
            (SELECT NULL, SUM(ROUND(hours * 3600)) AS hours,
            UNIX_TIMESTAMP(MIN(spent_on)) AS start_date, UNIX_TIMESTAMP(MAX(spent_on)) AS end_date
            FROM time_entries
            WHERE issue_id = $issueid AND user_id = $employeeid)
            })) {
            $rows = $self->Rows;
        }
        $self->Disconnect;
    }
    return $rows;
}

sub GetLeaveDays($$$$) {
    my ($self, $user, $start, $end) = @_;
    my $rows = undef;
    if ($self->Connect) {
        my $projectid = $main::config->Get('redmine', 'dayoffs_project_id');
        my $employeeid = $user->GetUserID;
        my $startdate = $self->Escape($start->DateTime('date'));
        my $enddate = $self->Escape($end->DateTime('date'));
        if ($self->Query(qq{
            SELECT UNIX_TIMESTAMP(start_date) AS start_date, UNIX_TIMESTAMP(due_date) AS end_date
            FROM issues
            WHERE project_id = $projectid AND assigned_to_id = $employeeid
            AND start_date <= $enddate AND due_date >= $startdate
            ORDER BY start_date
            })) {
            $rows = $self->Rows;
        }
        $self->Disconnect;
    }
    return $rows;
}

sub GetAvgWorkTime {
    my ($self, $user) = @_;
    my @worktime = ( );
    if ($self->Connect) {
        my $employeeid = $user->GetUserID;
        my $weeksago = new Orangutan::Date(time - 1209600);
        my $start_time = $self->Escape($weeksago->WorkDayStart->DateTime);
        if ($self->Query(qq{
            SELECT SEC_TO_TIME(AVG(start_time)) AS start_time, SEC_TO_TIME(AVG(end_time)) AS end_time
            FROM (SELECT MIN(TIME_TO_SEC(TIME(start_time))) AS start_time, MAX(TIME_TO_SEC(TIME(end_time))) AS end_time
            FROM time_entries
            WHERE user_id = $employeeid AND start_time >= $start_time
            AND DATE(start_time) = DATE(end_time) AND TIME(start_time) > '06:00:00'
            GROUP BY DATE(start_time)) AS worktime
            })) {
            if ($self->Rows > 0) {
                my $time = $self->Next;
                if (defined($time->{'start_time'})) {
                    push(@worktime, $time->{'start_time'});
                } else {
                    push(@worktime, undef);
                }
                if (defined($time->{'end_time'})) {
                    push(@worktime, $time->{'end_time'});
                } else {
                    push(@worktime, undef);
                }
            }
        }
        $self->Disconnect;
    }
    return @worktime;
}

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

    my $result = undef;
    if ($self->Connect) {
        if ($self->Query(qq{
            SELECT journalized_id AS issue_id, user_id, login,
            notes, UNIX_TIMESTAMP(journals.created_on) AS created_on,
            (SELECT COUNT(*) FROM journal_details WHERE journal_id = journals.id) AS count
            FROM journals
            JOIN users ON users.id = user_id
            WHERE journals.id = $id AND journalized_type = 'Issue'
            })) {
            if ($self->Rows > 0) {
                $result = $self->Next;
            }
        }
        $self->Disconnect;
    }
    return $result;
}

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

    my $rows = 0;
    if ($self->Connect) {
        if ($self->Query(qq{
            SELECT property, prop_key, old_value, value,
            CASE property
            WHEN 'attr' THEN
                CASE prop_key
                WHEN 'assigned_to_id' THEN (SELECT login FROM users WHERE id = value)
                WHEN 'category_id' THEN (SELECT name FROM issue_categories WHERE id = value)
                WHEN 'fixed_version_id' THEN (SELECT name FROM versions WHERE id = value)
                WHEN 'priority_id' THEN (SELECT name FROM enumerations WHERE id = value)
                WHEN 'project_id' THEN (SELECT name FROM projects WHERE id = value)
                WHEN 'status_id' THEN (SELECT name FROM issue_statuses WHERE id = value)
                WHEN 'tracker_id' THEN (SELECT name FROM trackers WHERE id = value)
                WHEN 'due_date' THEN UNIX_TIMESTAMP(value)
                WHEN 'start_date' THEN UNIX_TIMESTAMP(value)
                ELSE NULL END
            WHEN 'cf' THEN (SELECT name FROM custom_fields WHERE id = prop_key)
            ELSE NULL END AS value_name
            FROM journal_details
            WHERE journal_id = $id
            })) {
            $rows = $self->Rows;
        }
        $self->Disconnect;
    }
    return $rows;
}

sub GetCustomFieldName($$) {
    my ($self, $field) = @_;

    my $result = undef;
    if ($self->Connect) {
        my $name = $self->Escape($field);
        if ($self->Query(qq{
            SELECT id, name, field_format, custom_fields.regexp, min_length, max_length
            FROM custom_fields
            WHERE type = 'IssueCustomField' AND name = $name
            })) {
            if ($self->Rows > 0) {
                $result = $self->Next;
            }
        }
        $self->Disconnect;
    }
    return $result;
}

sub GetCustomFieldValues($$$;@) {
    my ($self, $fieldid, @values) = @_;

    my $rows = 0;
    if ($self->Connect) {
        my $value = '';
        for (my $i = 0; $i < scalar @values; $i++) {
            if ($i > 0) {
                $value .= ', ';
            }
            $value .= $self->Escape($values[$i]);
        }
        if ($self->Query(qq{
            SELECT value, customized_id
            FROM custom_values
            WHERE customized_type = 'Issue' AND custom_field_id = $fieldid AND value IN ($value)
            })) {
            $rows = $self->Rows;
        }
        $self->Disconnect;
    }
    return $rows;
}

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

    my @list = ( );
    if ($self->Connect) {
        if ($self->Query(qq{
            SELECT possible_values
            FROM custom_fields
            WHERE type = 'IssueCustomField' AND field_format = 'list' AND id = $id
            })) {
            if ($self->Rows > 0) {
                my $values = $self->Next->{'possible_values'};
                while ($values =~ /^- (.*)$/mg) {
                    push(@list, $1);
                }
            }
        }
        $self->Disconnect;
    }
    return \@list;
}

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

    my $ratio = 0;
    if ($self->Connect) {
        if ($self->Query(qq{
            SELECT ((SUM(IF(count = 1, 1, 0)) * 100) / COUNT(*)) AS ratio
            FROM (
            SELECT COUNT(*) AS count
            FROM custom_values
            WHERE customized_type = 'Issue' AND value IS NOT NULL AND value != '' AND custom_field_id = $id
            GROUP BY value) AS count;
            })) {
            if ($self->Rows > 0) {
                $ratio = $self->Next->{'ratio'};
            }
        }
        $self->Disconnect;
    }
    return $ratio;
}

sub GetIssueCustomFields($$) {
    my ($self, $issueid) = @_;

    my $rows = 0;
    if ($self->Connect) {
        if ($self->Query(qq{
            SELECT name, value
            FROM custom_values
            JOIN custom_fields ON custom_fields.id = custom_field_id
            WHERE customized_type = 'Issue' AND customized_id = $issueid AND value IS NOT NULL AND value != ''
            ORDER BY position
            })) {
            $rows = $self->Rows;
        }
        $self->Disconnect;
    }
    return $rows;
}

sub UpdateUserPreferences($$$) {
    my ($self, $userid, $preferences) = @_;
    my $result = undef;
    if ($self->Connect) {
        my $others = '--- ';
        if (defined($preferences) && (scalar keys %{$preferences} > 0) &&
            ((scalar keys %{$preferences} > 1) || !defined($preferences->{':id'}))) {
            foreach my $option (sort keys %{$preferences}) {
                if ($option ne ':id') {
                    $others .= "\n";
                    $others .= ":$option: ";
                    $others .= $preferences->{$option};
                }
            }
        } else {
            $others .= "{}\n\n";
        }
        $others = $self->Escape($others);
        if (defined($preferences->{':id'})) {
            my $id = $preferences->{':id'};
            if ($self->Query(qq{
                UPDATE user_preferences
                SET others = $others
                WHERE id = $id
                })) {
                $result = $id;
            }
        } else {
            if ($self->Query(qq{
                INSERT INTO user_preferences (
                    user_id,
                    others
                )
                VALUES (
                    $userid,
                    $others
                )
                })) {
                if ($self->Query(qq{
                    SELECT LAST_INSERT_ID() AS last_id
                    })) {
                    if ($self->Rows > 0) {
                        $result = $self->Next->{'last_id'};
                    }
                }
            }
        }
        $self->Disconnect;
    }
    return $result;
}

sub SubmitEntry($$$) {
    my ($self, $user, $task) = @_;

    my $result = undef;
    if ($self->Connect) {
        if ($task->GetID) {
            my $now = new Orangutan::Date;
            my $eventid = $task->GetID;
            my $projectid = $task->GetProject('id') || 0;
            my $activityid = $task->GetActivity('id') || 0;
            my $issueid = $task->GetIssue || 'NULL';
            my $hours = sprintf("%.2f", $task->GetDuration / 3600);
            my $spenton = $self->Escape($task->GetDate->DateTime('date'));
            my ($month, $year) = ($task->GetDate->GetDate)[1..2];
            my $week = $task->GetDate->GetWeek;
            my $reportdate = $self->Escape($now->DateTime);
            my $starttime = $task->GetStart ? $self->Escape($task->GetStart->DateTime) : 'NULL';
            my $endtime = $task->GetEnd ? $self->Escape($task->GetEnd->DateTime) : 'NULL';
            if ($self->Query(qq{
                UPDATE time_entries
                SET
                    project_id  = $projectid,
                    issue_id    = $issueid,
                    hours       = $hours,
                    activity_id = $activityid,
                    spent_on    = $spenton,
                    tyear       = $year,
                    tmonth      = $month,
                    tweek       = $week,
                    updated_on  = $reportdate,
                    start_time  = $starttime,
                    end_time    = $endtime
                WHERE id = $eventid
                })) {
                $result = $eventid;
            }
        } else {
            my $now = new Orangutan::Date;
            my $projectid = $task->GetProject('id') || 0;
            my $activityid = $task->GetActivity('id') || 0;
            my $issueid = $task->GetIssue || 'NULL';
            my $employeeid = $user->GetUserID;
            my $starttime = $task->GetStart ? $self->Escape($task->GetStart->DateTime) : 'NULL';
            my $endtime = $task->GetEnd ? $self->Escape($task->GetEnd->DateTime) : 'NULL';
            my $spenton = $self->Escape($task->GetDate->DateTime('date'));
            my ($month, $year) = ($task->GetDate->GetDate)[1..2];
            my $week = $task->GetDate->GetWeek;
            my $reportdate = $self->Escape($now->DateTime);
            my $hours = sprintf("%.2f", $task->GetDuration / 3600);
            my $description = $self->Escape($task->GetName);
            if ($self->Query(qq{
                INSERT INTO time_entries (
                    project_id,
                    user_id,
                    issue_id,
                    hours,
                    comments,
                    activity_id,
                    spent_on,
                    tyear,
                    tmonth,
                    tweek,
                    created_on,
                    updated_on,
                    start_time,
                    end_time
                )
                VALUES (
                    $projectid,
                    $employeeid,
                    $issueid,
                    $hours,
                    $description,
                    $activityid,
                    $spenton,
                    $year,
                    $month,
                    $week,
                    $reportdate,
                    $reportdate,
                    $starttime,
                    $endtime
                )})) {
                if ($self->Query(qq{
                    SELECT LAST_INSERT_ID() AS last_id
                    })) {
                    if ($self->Rows > 0) {
                        $result = $self->Next->{'last_id'};
                    }
                }
            }
        }
        if ($result && $self->TableExists('chart_time_entries')) {
            my $chart = $self->GetChartEntries($result);
            if ($chart) {
                $self->UpdateChartEntries($chart);
            }
        }
        $self->Disconnect;
    }
    return $result;
}

sub RemoveEntry($$) {
    my ($self, $eventid) = @_;
    my $deleted = 0;
    if ($self->Connect) {
        if ($self->Query(qq{
            DELETE
            FROM time_entries
            WHERE id = $eventid
            })) {
            if ($self->TableExists('chart_time_entries')) {
                my $chart = $self->GetChartEntries($eventid);
                if ($chart) {
                    $self->UpdateChartEntries($chart);
                }
            }
            $deleted = 1;
        }
        $self->Disconnect;
    }
    return $deleted;
}

sub GetChartEntries($$) {
    my ($self, $eventid) = @_;

    my $result = undef;
    if ($self->Connect) {
        if ($self->Query(qq{
            SELECT chart.id, total.id AS total, spent_on,
            entries.user_id, entries.issue_id, entries.activity_id, entries.project_id
            FROM time_entries entries
            LEFT JOIN chart_time_entries chart
            ON chart.day = (YEAR(spent_on) * 1000 + DAYOFYEAR(spent_on))
            AND chart.user_id = entries.user_id
            AND chart.issue_id = entries.issue_id
            AND chart.activity_id = entries.activity_id
            AND chart.project_id = entries.project_id
            LEFT JOIN chart_time_entries total
            ON total.day = 0
            AND total.user_id = entries.user_id
            AND total.issue_id = entries.issue_id
            AND total.activity_id = entries.activity_id
            AND total.project_id = entries.project_id
            WHERE entries.id = $eventid
            })) {
            if ($self->Rows > 0) {
                $result = $self->Next;
            }
        }
        $self->Disconnect;
    }
    return $result;
}

sub UpdateChartEntries($$) {
    my ($self, $chart) = @_;

    my $updated = 0;
    if ($self->Connect) {

        if ($chart->{'id'}) {
            my $chartid = $chart->{'id'};
            my $spenton = $self->Escape($chart->{'spent_on'});
            my $employeeid = $chart->{'user_id'};
            my $issueid = $chart->{'issue_id'} || 'NULL';
            my $activityid = $chart->{'activity_id'} || 'NULL';
            my $projectid = $chart->{'project_id'};
            if ($self->Query(qq{
                SELECT
                    ROUND(SUM(hours), 2) AS logged_hours,
                    COUNT(id) AS entries
                FROM time_entries
                WHERE
                    spent_on = $spenton AND
                    user_id = $employeeid AND
                    issue_id = $issueid AND
                    activity_id = $activityid AND
                    project_id = $projectid
                GROUP BY spent_on, user_id, issue_id, activity_id, project_id
                })) {
                if ($self->Rows > 0) {
                    my $data = $self->Next;
                    my $loggedhours = $data->{'logged_hours'};
                    my $entries = $data->{'entries'};
                    if ($self->Query(qq{
                        UPDATE chart_time_entries
                        SET
                            logged_hours = $loggedhours,
                            entries = $entries
                        WHERE id = $chartid
                        })) {
                        $updated++;
                    }
                } else {
                    if ($self->Query(qq{
                        DELETE
                        FROM chart_time_entries
                        WHERE id = $chartid
                        })) {
                        $updated++;
                    }
                }
            }
        } else {
            my $spenton = $self->Escape($chart->{'spent_on'});
            my $employeeid = $chart->{'user_id'};
            my $issueid = $chart->{'issue_id'} || 'NULL';
            my $activityid = $chart->{'activity_id'} || 'NULL';
            my $projectid = $chart->{'project_id'};
            if ($self->Query(qq{
                INSERT INTO chart_time_entries (
                    day,
                    week,
                    month,
                    logged_hours,
                    entries,
                    user_id,
                    issue_id,
                    activity_id,
                    project_id
                )
                SELECT
                    (YEAR(spent_on) * 1000 + DAYOFYEAR(spent_on)) AS day,
                    (YEAR(spent_on) * 1000 + WEEKOFYEAR(spent_on)) AS week,
                    (YEAR(spent_on) * 1000 + MONTH(spent_on)) AS month,
                    ROUND(SUM(hours), 2) AS logged_hours,
                    COUNT(id) AS entries,
                    user_id,
                    issue_id,
                    activity_id,
                    project_id
                FROM time_entries
                WHERE
                    spent_on = $spenton AND
                    user_id = $employeeid AND
                    issue_id = $issueid AND
                    activity_id = $activityid AND
                    project_id = $projectid
                GROUP BY spent_on, user_id, issue_id, activity_id, project_id
                })) {
                $updated++;
            }
        }

        if ($chart->{'total'}) {
            my $chartid = $chart->{'total'};
            my $employeeid = $chart->{'user_id'};
            my $issueid = $chart->{'issue_id'} || 'NULL';
            my $activityid = $chart->{'activity_id'} || 'NULL';
            my $projectid = $chart->{'project_id'};
            if ($self->Query(qq{
                SELECT
                    ROUND(SUM(hours), 2) AS logged_hours,
                    COUNT(id) AS entries
                FROM time_entries
                WHERE
                    user_id = $employeeid AND
                    issue_id = $issueid AND
                    activity_id = $activityid AND
                    project_id = $projectid
                GROUP BY user_id, issue_id, activity_id, project_id
                })) {
                if ($self->Rows > 0) {
                    my $data = $self->Next;
                    my $loggedhours = $data->{'logged_hours'};
                    my $entries = $data->{'entries'};
                    if ($self->Query(qq{
                        UPDATE chart_time_entries
                        SET
                            logged_hours = $loggedhours,
                            entries = $entries
                        WHERE id = $chartid
                        })) {
                        $updated++;
                    }
                } else {
                    if ($self->Query(qq{
                        DELETE
                        FROM chart_time_entries
                        WHERE id = $chartid
                        })) {
                        $updated++;
                    }
                }
            }
        } else {
            my $employeeid = $chart->{'user_id'};
            my $issueid = $chart->{'issue_id'} || 'NULL';
            my $activityid = $chart->{'activity_id'} || 'NULL';
            my $projectid = $chart->{'project_id'};
            if ($self->Query(qq{
                INSERT INTO chart_time_entries (
                    day,
                    week,
                    month,
                    logged_hours,
                    entries,
                    user_id,
                    issue_id,
                    activity_id,
                    project_id
                )
                SELECT
                    0,
                    0,
                    0,
                    ROUND(SUM(hours), 2) AS logged_hours,
                    COUNT(id) AS entries,
                    user_id,
                    issue_id,
                    activity_id,
                    project_id
                FROM time_entries
                WHERE
                    user_id = $employeeid AND
                    issue_id = $issueid AND
                    activity_id = $activityid AND
                    project_id = $projectid
                GROUP BY user_id, issue_id, activity_id, project_id
                })) {
                $updated++;
            }
        }

        $self->Disconnect;
    }
    return $updated;
}

sub UpdateIssue($$$;@) {
    my $self = shift;
    my $user = shift;
    my $issueid = shift;

    my $notes = undef;
    my %properties = ( );
    while ($#_ >= 0) {
        my ($key, $value) = splice(@_, -2);
        if ($key eq 'notes') {
            $notes = $value;
        } elsif
           (($key eq 'assigned_to_id') ||
            ($key eq 'done_ratio')) {
            $properties{$key} = $value;
        } else {
            $main::logger->Log("ignoring unsupported issue property: %s", $key);
        }
    }

    my $journal = undef;
    my @details = ( );
    if ($self->Connect) {
        my %changes = ( );
        my $now = new Orangutan::Date;
        my $updated_on = $self->Escape($now->DateTime);
        if (scalar keys %properties > 0) {
            my $fields = join(', ', keys %properties);
            if ($self->Query(qq{
                SELECT $fields
                FROM issues
                WHERE id = $issueid
                })) {
                if ($self->Rows > 0) {
                    my $oldvalues = $self->Next;
                    my @pairs = ( );
                    foreach my $key (keys %properties) {
                        if (defined($properties{$key})) {
                            my $value = $self->Escape($properties{$key});
                            push(@pairs, qq{ $key = $value });
                        } else {
                            push(@pairs, qq{ $key = NULL });
                        }
                    }
                    $fields = join(', ', @pairs);
                    if ($self->Query(qq{
                        UPDATE issues
                        SET
                            $fields,
                            updated_on = $updated_on
                        WHERE id = $issueid
                        })) {
                        foreach my $key (keys %properties) {
                            $changes{$key} = [
                                $oldvalues->{$key},
                                $properties{$key}
                            ];
                        }
                    }
                }
            }
        } else {
            $self->Query(qq{
                UPDATE issues
                SET updated_on = $updated_on
                WHERE id = $issueid
            });
        }
        ($journal, @details) = $self->AddJournal($user, $issueid, $notes, %changes);
        $self->Disconnect;
    }
    return ($journal, @details);
}

sub AddJournal($$$$;@) {
    my $self = shift;
    my $user = shift;
    my $issueid = shift;
    my $comment = shift;

    my %properties = ( );
    while ($#_ >= 0) {
        $properties{lc(pop)} = pop;
    }

    my $journal = undef;
    my @details = ( );
    if ($self->Connect) {
        my $now = new Orangutan::Date;
        my $userid = $user->GetUserID;
        my $notes = $comment ? $self->Escape($comment) : 'NULL';
        my $created_on = $self->Escape($now->DateTime('full'));
        if ($self->Query(qq{
            INSERT INTO journals (
                journalized_id,
                journalized_type,
                user_id,
                notes,
                created_on,
                source
            ) VALUES (
                $issueid,
                'Issue',
                $userid,
                $notes,
                $created_on,
                'Orangutan'
            )})) {
            if ($self->Query(qq{
                SELECT LAST_INSERT_ID() AS last_id
                })) {
                if ($self->Rows > 0) {
                    my $journalid = $self->Next->{'last_id'};
                    $journal = {
                        id         => $journalid,
                        issue_id   => $issueid,
                        user_id    => $userid,
                        login      => $user->GetLogin,
                        notes      => $comment,
                        created_on => $now->Date
                    };
                    foreach my $key (keys %properties) {
                        my $detail = $self->AddJournalDetails($journalid,
                            'attr', $key, $properties{$key}[0], $properties{$key}[1]);
                        push(@details, $detail);
                    }
                    $journal->{'count'} = scalar @details;
                }
            }
        }
        $self->Disconnect;
    }
    return ($journal, @details);
}

sub AddJournalDetails($$$$$$) {
    my ($self, $journalid, $property, $propkey, $old, $new) = @_;

    my $details = undef;
    if ($self->Connect) {
        my $oldvalue = defined($old) ? $self->Escape($old) : 'NULL';
        my $value = defined($new) ? $self->Escape($new) : 'NULL';
        if ($self->Query(qq{
            INSERT INTO journal_details (
                journal_id,
                property,
                prop_key,
                old_value,
                value
            ) VALUES (
                $journalid,
                '$property',
                '$propkey',
                $oldvalue,
                $value
            )})) {
            if ($self->Query(qq{
                SELECT LAST_INSERT_ID() AS last_id
                })) {
                if ($self->Rows > 0) {
                    $details = {
                        property   => $property,
                        prop_key   => $propkey,
                        old_value  => $old,
                        value      => $new
                    };
                }
            }
        }
        $self->Disconnect;
    }
    return $details;
}

sub Vote($$$$;$) {
    my ($self, $userid, $issueid, $vote, $id) = @_;

    my $result = undef;
    if ($self->Connect) {
        my $value = ($vote == 1) ? 1 : 0;
        if ($id) {
            if ($self->Query(qq{
                UPDATE votes
                SET vote = $value
                WHERE id = $id
                })) {
                $result = $id;
            }
        } else {
            if ($self->Query(qq{
                INSERT INTO votes (
                    vote,
                    created_at,
                    voteable_type,
                    voteable_id,
                    user_id
                ) VALUES (
                    $value,
                    NOW(),
                    'Issue',
                    $issueid,
                    $userid
                )})) {
                if ($self->Query(qq{
                    SELECT LAST_INSERT_ID() AS last_id
                    })) {
                    if ($self->Rows > 0) {
                        $result = $self->Next->{'last_id'};
                    }
                }
            }
        }
        if ($result && $self->ColumnExists('issues', 'votes_value')) {
            my $now = new Orangutan::Date;
            my $updated_on = $self->Escape($now->DateTime);
            $self->Query(qq{
                UPDATE issues
                SET
                    votes_value = (
                        SELECT SUM(IF(vote = 0, -1, 1))
                        FROM votes
                        WHERE voteable_type = 'Issue' AND voteable_id = $issueid
                    ),
                    updated_on = $updated_on
                WHERE id = $issueid
            });
        }
        $self->Disconnect;
    }
    return $result;
}

sub AddWatcher($$$) {
    my ($self, $userid, $watchid) = @_;

    my $result = undef;
    if ($self->Connect) {
        my $watchable_type;
        my $watchable_id;
        if (ref $watchid eq 'HASH') {
            $watchable_id = $watchid->{'id'};
            $watchable_type = 'Project';
        } else {
            $watchable_id = $watchid;
            $watchable_type = 'Issue';
        }
        if ($self->Query(qq{
            INSERT INTO watchers (
                watchable_type,
                watchable_id,
                user_id
            ) VALUES (
                '$watchable_type',
                $watchable_id,
                $userid
            )})) {
            if ($self->Query(qq{
                SELECT LAST_INSERT_ID() AS last_id
                })) {
                if ($self->Rows > 0) {
                    $result = $self->Next->{'last_id'};
                }
            }
        }
        $self->Disconnect;
    }
    return $result;
}

sub RemoveWatcher($$) {
    my ($self, $watchid) = @_;

    my $deleted = 0;
    if ($self->Connect) {
        if ($self->Query(qq{
            DELETE
            FROM watchers
            WHERE id = $watchid
            })) {
            $deleted = 1;
        }
        $self->Disconnect;
    }
    return $deleted;
}

sub RemoveNotes($$) {
    my ($self, $journalid) = @_;

    my $deleted = undef;
    if ($self->Connect) {
        if ($self->Query(qq{
            SELECT COUNT(*) AS count
            FROM journal_details
            WHERE journal_id = $journalid
            })) {
            if ($self->Rows > 0) {
                my $count = $self->Next->{'count'};
                if ($count > 0) {
                    if ($self->Query(qq{
                        UPDATE journals
                        SET notes = NULL
                        WHERE id = $journalid
                        })) {
                        $deleted = 0;
                    }
                } else {
                    if ($self->Query(qq{
                        DELETE
                        FROM journals
                        WHERE id = $journalid
                        })) {
                        $deleted = 1;
                    }
                }
            }
        }
        $self->Disconnect;
    }
    return $deleted;
}

1;
