httemplate/search/cust_timespan.html

   1 <& elements/search.html,
   2                   'title'       => emt('Customer Timespan Report'),
   3                   'name'        => emt('customers'),
   4                   'query'       => {
   5                      select     => join(', ', @select),
   6                      table      => $table,
   7                      addl_from  => $addl_from,
   8                      extra_sql  => $extra_sql,
   9                    },
  10                   'count_query' => $count_query,
  11                   'header'      => \@header,
  12                   'fields'      => \@fields,
  13                   'links'       => \@links,
  14                   'disable_maxselect' => '1',
  15
  16 &>
  17 <%init>
  18
  19 die "access denied"
  20   unless $FS::CurrentUser::CurrentUser->access_right('Advanced customer search');
  21
  22 my $table = 'cust_main';
  23 my $customer_link = [ "${p}view/cust_main.cgi?", 'custnum' ];
  24 my $agent_sql;
  25
  26 ## get agent numbers
  27 if (length($cgi->param('agentnum'))) {
  28   $cgi->param('agentnum') =~ /^(\d+)$/ or errorpage("Illegal agentnum");
  29   $agent_sql = ' and cust_main.agentnum = ' . $1;
  30 }
  31
  32
  33 ## get selected requested customers
  34 my $cust_status = $cgi->param('cust_status');
  35
  36 my %type_sql_map = (
  37         'cancelled' => 'cancel_sql',
  38         'suspended' => 'susp_sql',
  39 );
  40
  41 my $type_sql = $type_sql_map{$cust_status};
  42 $type_sql = 'cancel_sql' unless $type_sql;
  43
  44 my @custs = qsearch({
  45         table     => 'cust_main',
  46         extra_sql => ' where ' . FS::cust_main->$type_sql,
  47 });
  48 my @customers = ('0');
  49 foreach my $cust (@custs) { push @customers, $cust->custnum; }
  50
  51 ## get locations
  52 my $location_sub = sub {
  53   my $customer = shift;
  54   my @cust_location = qsearch({
  55         table => 'cust_location',
  56         select => 'cust_location.*',
  57         addl_from => ' LEFT JOIN cust_main ON (cust_location.locationnum = cust_main.bill_locationnum) ',
  58         extra_sql => ' WHERE cust_main.custnum = ' . $customer->custnum ,
  59   } );
  60
  61   my $location;
  62   foreach my $loc (@cust_location) {
  63         $location .= $loc->address1 unless !$loc->address1;
  64         $location .= "<br>" . $loc->address2 unless !$loc->address2;
  65         $location .= "<br>" . $loc->city . ", " . $loc->state . ' ' . $loc->zip unless !$loc->city;
  66   }
  67   $location;
  68 };
  69
  70 ## get contact emails for customer
  71 my $email_sub = sub {
  72   my $customer = shift;
  73   #can't because contactnum is in the wrong field #my @contact_email = $contact->contact_email;
  74   my @contact_email = qsearch({
  75         table => 'contact_email',
  76         addl_from => ' LEFT JOIN cust_contact ON (contact_email.contactnum = cust_contact.contactnum) LEFT JOIN cust_main ON (cust_contact.custnum = cust_main.custnum) ',
  77         extra_sql => ' WHERE cust_main.custnum = ' . $customer->custnum ,
  78   } );
  79   join('<br>', map $_->emailaddress, @contact_email);
  80 };
  81
  82 ## sql to get only canceled customers
  83 my @status = ('active', 'on hold', 'suspended', 'not yet billed', 'one-time charge');
  84 my $active_pkg_sql = 'select pkgnum from cust_pkg where cust_pkg.custnum = cust_main.custnum and ' . FS::cust_pkg->status_sql . " in ('".join( "', '", @status )."') limit 1";
  85
  86 ## sql to get the first active date, last cancel date, and last reason.
  87 my $active_date = 'select min(setup) from cust_pkg left join part_pkg using (pkgpart) where cust_pkg.custnum = cust_main.custnum and part_pkg.freq > \'0\'';
  88
  89 ## set cancel date range here
  90 my($beginning_date, $ending_date) = FS::UI::Web::parse_beginning_ending($cgi, '');
  91 my $max_cancel_sql = "select max(cancel) from cust_pkg left join part_pkg using (pkgpart) where cust_pkg.custnum = cust_main.custnum and part_pkg.freq > \'0\'";
  92 my $cancel_date = $max_cancel_sql.' and (('.$max_cancel_sql.') >= '.$beginning_date.' and ('.$max_cancel_sql.') <= '.$ending_date.')';
  93
  94 my $cancel_reason = 'select reason.reason from cust_pkg
  95         left join cust_pkg_reason on (cust_pkg.pkgnum = cust_pkg_reason.pkgnum)
  96         left join reason on (cust_pkg_reason.reasonnum = reason.reasonnum)
  97         where cust_pkg.custnum = cust_main.custnum and cust_pkg_reason.date = ('.$cancel_date.') limit 1
  98 ';
  99
 100 my @header = ( '#', 'Name', 'Address', 'Phone', 'Email', 'Active Date', 'Cancelled Date', 'Reason', 'Active Days' );
 101 my @fields = ( 'custnum', 'custname', $location_sub, 'daytime', $email_sub, 'active_date', 'cancel_date', 'cancel_reason', 'active_days' );
 102 my @links = ( $customer_link, $customer_link, '', '', '', '', '', '', '' );
 103 my @select = (
 104         'cust_main.*',
 105         "(select to_char((select to_timestamp((".$active_date."))), 'Mon DD YYYY')) AS active_date",
 106         "(select to_char((select to_timestamp((".$cancel_date."))), 'Mon DD YYYY')) AS cancel_date",
 107         "($cancel_reason) AS cancel_reason",
 108         "(select date_part('day', (select to_timestamp((".$cancel_date."))) - (select to_timestamp((".$active_date."))) )) AS active_days",
 109         "CONCAT_WS(', ', last, first) AS custname",
 110 );
 111 my $addl_from = '
 112   LEFT JOIN cust_location ON (cust_main.bill_locationnum = cust_location.locationnum)
 113   LEFT JOIN cust_pkg ON (cust_main.custnum = cust_pkg.custnum)
 114   LEFT JOIN part_pkg ON (cust_pkg.pkgpart = part_pkg.pkgpart)
 115 ';
 116 my $extra_sql = " WHERE (".$active_date.") IS NOT NULL AND (".$cancel_date.") IS NOT NULL AND cust_main.custnum IN ('" . join( "', '", @customers ). "') $agent_sql ";
 117
 118 ## sql to get record count
 119 my $count_query = 'select COUNT(*) from ' . $table . ' ' . $extra_sql;
 120
 121 </%init>