added on local at 2026-07-01 12:34:37
| 1 | #!/usr/bin/perl | |
| 2 | ||
| 3 | ################################################################################################################################## | |
| 4 | # ______ ____ ____ ______ ______ ____ ___ __ ___ ______ _ __ ____ ____ __ __ _ __ _____ ____ | |
| 5 | # / ____// __ \ / __ \ / ____/ / ____// __ \ / | / |/ // ____/| | / // __ \ / __ \ / //_/ | | / /|__ / / __ \ | |
| 6 | # / / / / / // / / // __/ / /_ / /_/ // /| | / /|_/ // __/ | | /| / // / / // /_/ // ,< | | / / /_ < / / / / | |
| 7 | # / /___ / /_/ // /_/ // /___ / __/ / _, _// ___ | / / / // /___ | |/ |/ // /_/ // _, _// /| | | |/ / ___/ /_ / /_/ / | |
| 8 | # \____/ \____//_____//_____/ /_/ /_/ |_|/_/ |_|/_/ /_//_____/ |__/|__/ \____//_/ |_|/_/ |_| |___/ /____/(_)\____/ | |
| 9 | # | |
| 10 | # - Written by: Shawn Pickering | |
| 11 | # - shawn@shawnpickering.com | |
| 12 | ################################################################################################################################## | |
| 13 | use strict; | |
| 14 | use lib '/var/www/vhosts/3dshawn.com/ptmatrix.3dshawn.com'; | |
| 15 | ||
| 16 | ||
| 17 | ############################################################# | |
| 18 | ## Variables | |
| 19 | ############################################################# | |
| 20 | ||
| 21 | ||
| 22 | ||
| 23 | ############################################################ | |
| 24 | # Modules | |
| 25 | ############################################################ | |
| 26 | use CGI; my $query = new CGI; my $form = $query->Vars; | |
| 27 | use MODS::Login; my $login = new MODS::Login; | |
| 28 | use MODS::PTMatrix::Urls; my $getlist = new MODS::PTMatrix::Urls; my $url = $getlist->urls(); | |
| 29 | use MODS::Template; my $tfile = new MODS::Template; | |
| 30 | use MODS::PTMatrix::Wrapper; my $load = new MODS::PTMatrix::Wrapper; | |
| 31 | use MODS::DBConnect; my $db = new MODS::DBConnect; | |
| 32 | use MODS::PTMatrix::Config; my $config = new MODS::PTMatrix::Config; my $database_name = $config->settings('database_name'); | |
| 33 | use MODS::PTMatrix::PM; | |
| 34 | use Time::Local; | |
| 35 | ||
| 36 | ||
| 37 | ||
| 38 | ############################################################# | |
| 39 | ## Get form data | |
| 40 | ############################################################# | |
| 41 | foreach my $line(keys %$form){ | |
| 42 | $form->{$line} =~ s/[^!-~\s]//g; | |
| 43 | $form->{$line} = quotemeta("$form->{$line}"); | |
| 44 | } | |
| 45 | ||
| 46 | ||
| 47 | ||
| 48 | ############################################################ | |
| 49 | # Program Controls | |
| 50 | ############################################################ | |
| 51 | $|=1; | |
| 52 | my $userinfo = $login->login_verify(); | |
| 53 | print qq~Content-type:text/html; Cache-Control:no-cache;\n\n~; | |
| 54 | if(!$userinfo){print qq~<script>window.location="$url->{login}";</script>~;} | |
| 55 | else{&reports_view;} | |
| 56 | ||
| 57 | ||
| 58 | ############################################################ | |
| 59 | # Subroutines | |
| 60 | ############################################################ | |
| 61 | sub iso_to_epoch{ | |
| 62 | my($iso) = @_; | |
| 63 | return 0 unless $iso && $iso =~ /^(\d{4})-(\d{2})-(\d{2})/; | |
| 64 | return Time::Local::timelocal(0,0,12, $3, $2-1, $1-1900); | |
| 65 | } | |
| 66 | ||
| 67 | sub epoch_to_iso{ | |
| 68 | my @t = localtime($_[0]); | |
| 69 | return sprintf("%04d-%02d-%02d", $t[5]+1900, $t[4]+1, $t[3]); | |
| 70 | } | |
| 71 | ||
| 72 | sub url_enc{ | |
| 73 | my $s = shift // ''; | |
| 74 | $s =~ s/([^A-Za-z0-9\-])/sprintf("%%%02X", ord($1))/eg; | |
| 75 | return $s; | |
| 76 | } | |
| 77 | ||
| 78 | sub reports_view{ | |
| 79 | my $cid = MODS::PTMatrix::PM::get_company_id($userinfo); | |
| 80 | ||
| 81 | #Date range defaults to current month (1st -> today). End is inclusive of day. | |
| 82 | my @lt = localtime; | |
| 83 | my $today_iso = sprintf("%04d-%02d-%02d", $lt[5]+1900, $lt[4]+1, $lt[3]); | |
| 84 | my $month_start = sprintf("%04d-%02d-01", $lt[5]+1900, $lt[4]+1); | |
| 85 | ||
| 86 | my $range_start = $form->{start} || $month_start; | |
| 87 | my $range_end = $form->{end} || $today_iso; | |
| 88 | $range_start = $month_start unless $range_start =~ /^\d{4}-\d{2}-\d{2}$/; | |
| 89 | $range_end = $today_iso unless $range_end =~ /^\d{4}-\d{2}-\d{2}$/; | |
| 90 | my $range_end_eod = "$range_end 23:59:59"; | |
| 91 | ||
| 92 | my $dbh = $db->db_connect(); | |
| 93 | ||
| 94 | #------------------------------------------------------------------------------------------------------ | |
| 95 | #KPI tiles - one query with 6 scalar subqueries | |
| 96 | my $sql_k = qq~SELECT (SELECT COUNT(*) FROM `${database_name}`.projects WHERE company_id='$cid' AND status='active' AND deleted_at IS NULL) AS active_projects, (SELECT COUNT(*) FROM `${database_name}`.tasks WHERE company_id='$cid' AND status IN ('open','in_progress','review','blocked')) AS open_tasks, (SELECT COUNT(*) FROM `${database_name}`.tasks WHERE company_id='$cid' AND status='done' AND completed_at BETWEEN '$range_start' AND '$range_end_eod') AS done_range, (SELECT COUNT(*) FROM `${database_name}`.tasks WHERE company_id='$cid' AND status IN ('open','in_progress','review','blocked') AND due_date < CURDATE()) AS overdue, (SELECT COALESCE(SUM(minutes),0)/60 FROM `${database_name}`.time_entries WHERE company_id='$cid' AND occurred_on BETWEEN '$range_start' AND '$range_end') AS hours_range, (SELECT COUNT(*) FROM `${database_name}`.users WHERE company_id='$cid' AND account_status='active') AS active_users FROM dual~; | |
| 97 | my $kpi = $db->db_readwrite($dbh, $sql_k, $ENV{SCRIPT_NAME}, __LINE__); | |
| 98 | foreach my $k(qw(active_projects open_tasks done_range overdue active_users)){ | |
| 99 | $kpi->{$k} = ($kpi->{$k} || 0) + 0; | |
| 100 | } | |
| 101 | $kpi->{hours_range} = sprintf("%.1f", $kpi->{hours_range} || 0); | |
| 102 | #------------------------------------------------------------------------------------------------------ | |
| 103 | ||
| 104 | #------------------------------------------------------------------------------------------------------ | |
| 105 | #Throughput - daily completion counts for the bar chart | |
| 106 | my $sql_t = qq~SELECT DATE(completed_at) AS d, COUNT(*) AS n FROM `${database_name}`.tasks WHERE company_id='$cid' AND completed_at BETWEEN '$range_start' AND '$range_end_eod' GROUP BY DATE(completed_at) ORDER BY d~; | |
| 107 | my @throughput = $db->db_readwrite_multiple($dbh, $sql_t, $ENV{SCRIPT_NAME}, __LINE__); | |
| 108 | my %th_by_d; | |
| 109 | foreach my $r(@throughput){$th_by_d{$r->{d}} = $r->{n};} | |
| 110 | my $max_n = 1; | |
| 111 | foreach my $r(@throughput){$max_n = $r->{n} if $r->{n} > $max_n;} | |
| 112 | ||
| 113 | #Build day-by-day bars across the chosen window. Long ranges thin the labels. | |
| 114 | my $start_ep = &iso_to_epoch($range_start); | |
| 115 | my $end_ep = &iso_to_epoch($range_end); | |
| 116 | my $days = int(($end_ep - $start_ep) / 86400) + 1; | |
| 117 | $days = 1 if $days < 1; | |
| 118 | my $show_every = ($days > 30) ? int($days / 14) : 1; | |
| 119 | ||
| 120 | my @bars; | |
| 121 | for(my $i = 0; $i < $days; $i++){ | |
| 122 | my $d_iso = &epoch_to_iso($start_ep + $i * 86400); | |
| 123 | my $n = $th_by_d{$d_iso} || 0; | |
| 124 | my $h = $max_n > 0 ? int($n * 100 / $max_n) : 0; | |
| 125 | push @bars, { | |
| 126 | day => substr($d_iso, 5), | |
| 127 | d_iso => $d_iso, | |
| 128 | count => $n, | |
| 129 | height => $h, | |
| 130 | height_css => ($h + 2) . 'px', | |
| 131 | show_label => (($i % $show_every) == 0) ? 1 : 0, | |
| 132 | has_tasks => ($n > 0) ? 1 : 0, | |
| 133 | }; | |
| 134 | } | |
| 135 | #------------------------------------------------------------------------------------------------------ | |
| 136 | ||
| 137 | #------------------------------------------------------------------------------------------------------ | |
| 138 | #Top 10 projects by total tasks | |
| 139 | my $sql_p = qq~SELECT p.id, p.name, p.color, p.key_prefix, COUNT(t.id) AS total, SUM(IF(t.status='done',1,0)) AS done_n, SUM(IF(t.status IN ('open','in_progress','review','blocked'),1,0)) AS open_n, SUM(IF(t.due_date < CURDATE() AND t.status NOT IN ('done','cancelled'),1,0)) AS overdue_n FROM `${database_name}`.projects p LEFT JOIN `${database_name}`.tasks t ON t.project_id=p.id WHERE p.company_id='$cid' AND p.status IN ('active','planning') GROUP BY p.id ORDER BY total DESC LIMIT 10~; | |
| 140 | my @projects = $db->db_readwrite_multiple($dbh, $sql_p, $ENV{SCRIPT_NAME}, __LINE__); | |
| 141 | foreach my $p(@projects){ | |
| 142 | $p->{name} = MODS::PTMatrix::PM::h($p->{name}); | |
| 143 | $p->{color} ||= '#5aa9ff'; | |
| 144 | my $tot = ($p->{total} || 0) + 0; | |
| 145 | my $don = ($p->{done_n} || 0) + 0; | |
| 146 | $p->{progress_pct} = $tot > 0 ? int($don * 100 / $tot) : 0; | |
| 147 | } | |
| 148 | #------------------------------------------------------------------------------------------------------ | |
| 149 | ||
| 150 | #------------------------------------------------------------------------------------------------------ | |
| 151 | #Top 10 people by tasks completed in range | |
| 152 | my $sql_pp = qq~SELECT u.id, u.display_name, u.avatar_color, COALESCE((SELECT COUNT(*) FROM `${database_name}`.tasks t WHERE t.assigned_user_id=u.id AND t.status IN ('open','in_progress','review','blocked')),0) AS open_n, COALESCE((SELECT COUNT(*) FROM `${database_name}`.tasks t WHERE t.assigned_user_id=u.id AND t.completed_at BETWEEN '$range_start' AND '$range_end_eod'),0) AS done_range, COALESCE((SELECT SUM(minutes)/60 FROM `${database_name}`.time_entries te WHERE te.user_id=u.id AND te.occurred_on BETWEEN '$range_start' AND '$range_end'),0) AS hours_range FROM `${database_name}`.users u WHERE u.company_id='$cid' AND u.account_status='active' ORDER BY done_range DESC LIMIT 10~; | |
| 153 | my @ppl = $db->db_readwrite_multiple($dbh, $sql_pp, $ENV{SCRIPT_NAME}, __LINE__); | |
| 154 | foreach my $p(@ppl){ | |
| 155 | $p->{initials} = MODS::PTMatrix::PM::initials($p->{display_name}); | |
| 156 | $p->{display_name} = MODS::PTMatrix::PM::h($p->{display_name}); | |
| 157 | $p->{hours_range} = sprintf("%.1f", $p->{hours_range} || 0); | |
| 158 | } | |
| 159 | #------------------------------------------------------------------------------------------------------ | |
| 160 | ||
| 161 | $db->db_disconnect($dbh); | |
| 162 | ||
| 163 | #KPI tile drill-in URLs - tickets.cgi accepts wit=all + status filters | |
| 164 | my $start_e = &url_enc($range_start); | |
| 165 | my $end_e = &url_enc($range_end); | |
| 166 | ||
| 167 | #Create all template variables | |
| 168 | my $tvars; | |
| 169 | $tvars->{kpi} = $kpi; | |
| 170 | $tvars->{bars} = \@bars; | |
| 171 | $tvars->{has_bars} = scalar(@bars) ? 1 : 0; | |
| 172 | $tvars->{projects} = \@projects; | |
| 173 | $tvars->{has_projects} = scalar(@projects) ? 1 : 0; | |
| 174 | $tvars->{ppl} = \@ppl; | |
| 175 | $tvars->{has_ppl} = scalar(@ppl) ? 1 : 0; | |
| 176 | $tvars->{range_start} = $range_start; | |
| 177 | $tvars->{range_end} = $range_end; | |
| 178 | $tvars->{range_label} = ($range_start eq $month_start && $range_end eq $today_iso) ? 'This month' : "$range_start → $range_end"; | |
| 179 | $tvars->{url_active} = "/projects.cgi?status=active"; | |
| 180 | $tvars->{url_open} = "/tickets.cgi?wit=all&status=open,in_progress,review,blocked"; | |
| 181 | $tvars->{url_done} = "/tickets.cgi?wit=all&status=done&completed_start=$start_e&completed_end=$end_e"; | |
| 182 | $tvars->{url_overdue} = "/tickets.cgi?wit=all&overdue=1"; | |
| 183 | $tvars->{url_hours} = "/time.cgi?start=$start_e&end=$end_e"; | |
| 184 | $tvars->{url_members} = "/team.cgi?status=active"; | |
| 185 | $tvars->{url_drill_day} = "/tickets.cgi?wit=all&status=done"; | |
| 186 | ||
| 187 | my $body = join('', $tfile->template('tf_reports.html', $tvars, $userinfo)); | |
| 188 | ||
| 189 | $load->render({ | |
| 190 | userinfo => $userinfo, | |
| 191 | page_key => 'reports', | |
| 192 | title => 'Reports', | |
| 193 | body => $body, | |
| 194 | }); | |
| 195 | } |