From ac0bdc5e7860c6ab43d467f075505b0b4ec0245f Mon Sep 17 00:00:00 2001
From: Mark Wells <mark@freeside.biz>
Date: Thu, 18 Aug 2016 12:42:52 -0700
Subject: [PATCH] report on contract end dates, #71964

---
 httemplate/elements/menu.html        |  1 +
 httemplate/search/cust_pkg-date.html | 90 ++++++++++++++++++++++++++++++++++++
 2 files changed, 91 insertions(+)
 create mode 100644 httemplate/search/cust_pkg-date.html

diff --git a/httemplate/elements/menu.html b/httemplate/elements/menu.html
index 88c1df3c8..51ec26329 100644
--- a/httemplate/elements/menu.html
+++ b/httemplate/elements/menu.html
@@ -275,6 +275,7 @@ $report_packages{'Suspension summary'} = [ $fsurl.'search/cust_pkg_susp.html', '
 $report_packages{'Customer packages with unconfigured services'} =  [ $fsurl.'search/cust_pkg.cgi?APKG_pkgnum', 'List packages which have provisionable services' ];
 $report_packages{'FCC Form 477'} =  [ $fsurl.'search/report_477.html' ]
   if $conf->exists('part_pkg-show_fcc_options');
+$report_packages{'Contract end dates'} = [ $fsurl.'search/cust_pkg-date.html?date=contract_end', 'Show packages by contract end date' ];
 $report_packages{'Advanced package reports'} =  [ $fsurl.'search/report_cust_pkg.html', 'by agent, date range, status, package definition' ];
 
 tie my %report_inventory, 'Tie::IxHash',
diff --git a/httemplate/search/cust_pkg-date.html b/httemplate/search/cust_pkg-date.html
new file mode 100644
index 000000000..1b9377546
--- /dev/null
+++ b/httemplate/search/cust_pkg-date.html
@@ -0,0 +1,90 @@
+<%init>
+my $curuser = $FS::CurrentUser::CurrentUser;
+die 'access denied' unless $curuser->access_right('List packages');
+
+my %cols = (
+  'contract_end' => 'Contract end',
+  # We could put any of the date fields in cust_pkg in here, but keep in
+  # mind:
+  # - for start_date, setup, and bill, make sure to include rows where
+  #   the field is null, as that's effectively "right now".
+  # - for cancel and susp, and maybe expire, adjourn, and resume, add a
+  #   column for the cancel or suspend reason.
+  # - for expire, also figure out if there's a future change scheduled.
+  # - for change_date, should probably show what it was changed from.
+);
+
+my $col = $cgi->param('date');
+die "invalid date column" unless $cols{$col};
+
+my $title = 'Packages by ' . lc($cols{$col}) . ' date';
+# second option on the cust_fields_avail list, plus email
+my $cust_fields = 'Cust# | Customer | Day phone | Night phone | Mobile phone | Invoicing email(s)';
+my @header = ( $cols{$col},
+               emt('#'),
+               emt('Quan.'),
+               emt('Package'),
+               # anything else? package status, maybe?
+             );
+my @fields = ( sub { time2str('%b %d %Y', $_[0]->$col) },
+               'pkgnum',
+               'quantity',
+               'pkg_label',
+             );
+my @sort_fields = ( map '', @fields ); # should only ever sort by $col
+
+push @header, FS::UI::Web::cust_header($cust_fields);
+push @fields, \&FS::UI::Web::cust_fields;
+
+my $query = {
+  'table'     => 'cust_pkg',
+  'addl_from' => FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg'),
+  'hashref'   => {
+    $col => { op => '!=', value => '' },
+    'cancel' => '',
+  },
+  'order_by' => "ORDER BY $col",
+};
+
+my $count_query =
+  "SELECT COUNT(*) FROM cust_pkg WHERE $col IS NOT NULL AND cancel IS NULL";
+
+my $pkg_link = sub {
+  my $self = shift;
+  my $frag = 'cust_pkg'. $self->pkgnum;
+  [ "${p}view/cust_main.cgi?custnum=".$self->custnum.
+                           ";show=packages;fragment=$frag#cust_pkg",
+    'pkgnum'
+  ];
+};
+
+my @links = ( '', ($pkg_link) x 3,
+  FS::UI::Web::cust_links() );
+
+my $date_color_sub = sub {
+  my $self = shift;
+  my $color;
+  my $interval = ($self->$col - time) / 86400;
+  if ( $interval > 30 ) {
+    $color = 'palegreen';
+  } elsif ( $interval > 0 ) {
+    $color = 'yellow';
+  } else {
+    $color = 'tomato';
+  }
+  "background-color: $color";
+};
+
+</%init>
+<& elements/search.html,
+  'title'       => $title,
+  'name'        => 'packages',
+  'query'       => $query,
+  'count_query' => $count_query,
+  'header'      => \@header,
+  'fields'      => \@fields,
+  'align'       => 'rrrl'. FS::UI::Web::cust_aligns(),
+  'links'       => \@links,
+  'cell_style'  => [ $date_color_sub ],
+&>
+
-- 
2.11.0