httemplate/search/cust_pkg_discount.html

   1 <% include( 'elements/search.html',
   2                   'title'       => 'Package discounts',
   3                   'name'        => 'discounts',
   4                   'query'       => $query,
   5                   'count_query' => $count_query,
   6                   #'redirect'    => $link,
   7                   'header'      => [ 'Status',
   8                                      'Discount',
   9                                      'Months used',
  10                                      'Employee',
  11                                      'Package',
  12                                      FS::UI::Web::cust_header(
  13                                      #  $cgi->param('cust_fields')
  14                                      ),
  15                                    ],
  16                   'fields'      => [
  17                                      sub { ucfirst( shift->status ) },
  18                                      sub { shift->discount->description },
  19                                      sub { my $m = shift->months_used;
  20                                            $m =~ /\./ ? sprintf('%.2f',$m) : $m;
  21                                          },
  22                                      'otaker',
  23                                      'pkg',
  24                                      \&FS::UI::Web::cust_fields,
  25                                    ],
  26                   'links'       => [
  27                                      '',
  28                                      '',
  29                                      '',
  30                                      '',
  31                                      '',
  32                                      ( map { $_ ne 'Cust. Status' ? $clink : ''}
  33                                            FS::UI::Web::cust_header()
  34                                      ),
  35                                    ],
  36                   'align'       => 'clrll'. FS::UI::Web::cust_aligns(),
  37                   'color'       => [
  38                                      '',
  39                                      '',
  40                                      '',
  41                                      '',
  42                                      '',
  43                                      FS::UI::Web::cust_colors(),
  44                                    ],
  45                  'style'        => [
  46                                      '',
  47                                      '',
  48                                      '',
  49                                      '',
  50                                      '',
  51                                      FS::UI::Web::cust_styles(),
  52                                    ],
  53            )
  54 %>
  55 <%init>
  56
  57 die "access denied"
  58   unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
  59
  60 #my $conf = new FS::Conf;
  61
  62 #here is the agent virtualization
  63 my $agentnums_sql =
  64   $FS::CurrentUser::CurrentUser->agentnums_sql( 'table' => 'cust_main' );
  65
  66 my @where = ( $agentnums_sql );
  67
  68 #status
  69 if ( $cgi->param('status') eq 'active' ) {
  70   push @where, "     (    cust_pkg_discount.disabled IS NULL
  71                        OR cust_pkg_discount.disabled != 'Y'  )
  72                  AND ( months IS NULL OR months_used < months ) ";
  73                        #XXX also end date
  74 } elsif ( $cgi->param('status') eq 'expired' ) {
  75   push @where, "    (      cust_pkg_discount.disabled IS NOT NULL
  76                       AND  cust_pkg_discount.disabled = 'Y'       )
  77                  OR ( months IS NOT NULL AND months_used >= months )
  78                ";     #XXX also end date
  79 }
  80
  81 #usernum
  82 if ( $cgi->param('usernum') =~ /^(\d+)$/ ) {
  83   push @where, "cust_pkg_discount.usernum = $1";
  84 }
  85
  86 #agent
  87 if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
  88   push @where, "cust_main.agentnum = $1";
  89 }
  90
  91 my $count_query = "SELECT COUNT(*), SUM(amount)";
  92
  93 my $join = ' LEFT JOIN discount  USING ( discountnum )
  94              LEFT JOIN cust_pkg  USING ( pkgnum )
  95              LEFT JOIN part_pkg  USING ( pkgpart )
  96              LEFT JOIN cust_main USING ( custnum ) ';
  97
  98 my $where = ' WHERE '. join(' AND ', @where);
  99
 100 $count_query .= " FROM cust_pkg_discount $join $where";
 101
 102 my @select = (
 103                'cust_pkg_discount.*',
 104                'part_pkg.pkg',
 105              );
 106 push @select, 'cust_main.custnum',
 107               FS::UI::Web::cust_sql_fields();
 108
 109 my $query = {
 110   'table'     => 'cust_pkg_discount',
 111   'addl_from' => $join,
 112   'hashref'   => {},
 113   'select'    => join(', ', @select ),
 114   'extra_sql' => $where,
 115   'order_by'  => 'ORDER BY pkgdiscountnum',
 116 };
 117
 118 my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ];
 119
 120 my $conf = new FS::Conf;
 121
 122 </%init>