httemplate/search/cust_credit_source_bill_pkg.html

   1 <& elements/search.html,
   2               'title'         => 'Credit package source detail', #from line item
   3               'name_singular' => 'credit source',
   4               'query'         => $query,
   5               'count_query'   => $count_query,
   6                'count_addl'   => [ $money_char. '%.2f total', ],
   7                'header'       => [
   8                    #'#',
   9
  10                    'Amount',
  11
  12                    #credit
  13                    'Date',
  14                    'By',
  15                    'Reason',
  16
  17                    # line item
  18                    'Line item',
  19
  20                    #invoice
  21                    'Invoice',
  22                    'Date',
  23                    FS::UI::Web::cust_header(),
  24                ],
  25                'fields'       => [
  26                    #'creditsourcebillpkgnum',
  27                    sub { sprintf($money_char.'%.2f', shift->amount ) },
  28
  29                    sub { time2str('%b %d %Y', shift->get('cust_credit_date') ) },
  30                    sub { shift->cust_credit->otaker },
  31                    sub { shift->cust_credit->reason }, # split into reason_only/addlinfo if addlinfo ever gets used here
  32
  33                    sub { $_[0]->pkgnum > 0
  34                            ? $_[0]->get('pkg')      # possibly use override.pkg
  35                            : $_[0]->get('itemdesc') # but i think this correct
  36                        },
  37                    'invnum',
  38                    sub { time2str('%b %d %Y', shift->_date ) },
  39                    \&FS::UI::Web::cust_fields,
  40                ],
  41                'sort_fields'  => [
  42                    'amount',
  43                    'cust_credit_date',
  44                    '', #'otaker',
  45                    '', #reason
  46                    '', #line item description
  47                    'invnum',
  48                    '_date',
  49                    FS::UI::Web::cust_sort_fields(),
  50                ],
  51                'links' => [
  52                    '',
  53                    '',
  54                    '',
  55                    '',
  56                    '',
  57                    $ilink,
  58                    $ilink,
  59                    ( map { $_ ne 'Cust. Status' ? $clink : '' }
  60                          FS::UI::Web::cust_header()
  61                    ),
  62                ],
  63                'align' => 'rrlll'.
  64                           'rr'.
  65                           FS::UI::Web::cust_aligns(),
  66                'color' => [
  67                               '',
  68                               '',
  69                               '',
  70                               '',
  71                               '',
  72                               '',
  73                               '',
  74                               FS::UI::Web::cust_colors(),
  75                             ],
  76                'style' => [
  77                               '',
  78                               '',
  79                               '',
  80                               '',
  81                               '',
  82                               '',
  83                               '',
  84                               FS::UI::Web::cust_styles(),
  85                           ],
  86
  87 &>
  88 <%init>
  89
  90 #copied from cust_credit_source_bill.html, which itself was cribbed from
  91 # cust_bill_pkg.cgi and cust_credit.html, but then i took out a bunch of the
  92 # tax reports stuff... maybe look for parts of all three that can be made into
  93 # common subs?
  94
  95 die "access denied"
  96   unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
  97
  98 my $conf = new FS::Conf;
  99
 100 #here is the agent virtualization
 101 my $agentnums_sql =
 102   $FS::CurrentUser::CurrentUser->agentnums_sql( 'table' => 'cust_main' );
 103
 104 my @where = ( $agentnums_sql );
 105
 106 if ( $cgi->param('usernum') =~ /^(\d+)$/ ) {
 107   push @where, "cust_credit.usernum = $1";
 108 }
 109
 110 #source invoice date (not yet in UI)
 111 my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
 112 push @where, "cust_bill._date >= $beginning",
 113              "cust_bill._date <= $ending";
 114
 115 #credit date
 116 my($cr_begin, $cr_end) = FS::UI::Web::parse_beginning_ending($cgi, 'credit');
 117 push @where, "cust_credit._date >= $cr_begin",
 118              "cust_credit._date <= $cr_end";
 119
 120 #credit amount?  seems more what is expected than the applied amount (not in UI)
 121 my @lt_gt = FS::UI::Web::parse_lt_gt($cgi, 'amount' );
 122 s/amount/cust_credit.amount/g foreach (@lt_gt);
 123 push @where, @lt_gt;
 124
 125 if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
 126   push @where, "cust_main.agentnum = $1";
 127 }
 128
 129 #(not in UI)
 130 if ( $cgi->param('billpkgnum') =~ /^(\d+)$/ ) {
 131   push @where, "billpkgnum = $1";
 132 }
 133
 134 #(not in UI)
 135 #classnum
 136 # not specified: all classes
 137 # 0: empty class
 138 # N: classnum
 139 my $use_override = $cgi->param('use_override');
 140 if ( $cgi->param('classnum') =~ /^(\d+)$/ ) {
 141   my $comparison = '';
 142   if ( $1 == 0 ) {
 143     $comparison = "IS NULL";
 144   } else {
 145     $comparison = "= $1";
 146   }
 147
 148   if ( $use_override ) {
 149     push @where, "(
 150       part_pkg.classnum $comparison AND pkgpart_override IS NULL OR
 151       override.classnum $comparison AND pkgpart_override IS NOT NULL
 152     )";
 153   } else {
 154     push @where, "part_pkg.classnum $comparison";
 155   }
 156 }
 157
 158 my $count_query = "SELECT COUNT(DISTINCT creditsourcebillpkgnum),
 159                           SUM(cust_credit_source_bill_pkg.amount)";
 160
 161 my $join_cust =
 162   '      JOIN cust_bill ON ( cust_bill_pkg.invnum = cust_bill.invnum )'.
 163   FS::UI::Web::join_cust_main('cust_bill', 'cust_pkg');
 164
 165
 166 my $join_cust_bill_pkg = 'LEFT JOIN cust_bill_pkg USING ( billpkgnum )';
 167
 168 my $join_pkg =  ' LEFT JOIN cust_pkg USING ( pkgnum )
 169                   LEFT JOIN part_pkg USING ( pkgpart ) ';
 170
 171 my $where = ' WHERE '. join(' AND ', @where);
 172
 173 my $join_credit = ' LEFT JOIN cust_credit      USING ( crednum ) ';
 174
 175 $count_query .= " FROM cust_credit_source_bill_pkg
 176                          $join_cust_bill_pkg
 177                          $join_pkg
 178                          $join_credit
 179                          $join_cust
 180                        $where";
 181
 182 my @select = ( 'cust_credit_source_bill_pkg.*',
 183                'cust_bill_pkg.*',
 184                'cust_credit.otaker',
 185                'cust_credit._date AS cust_credit_date',
 186                'cust_bill._date',
 187              );
 188 push @select, 'part_pkg.pkg' unless $cgi->param('istax');
 189 push @select, 'cust_main.custnum',
 190               FS::UI::Web::cust_sql_fields();
 191
 192 my $query = {
 193   'table'     => 'cust_credit_source_bill_pkg',
 194   'addl_from' => "$join_cust_bill_pkg
 195                   $join_pkg
 196                   $join_credit
 197                   $join_cust",
 198   'hashref'   => {},
 199   'select'    => join(', ', @select ),
 200   'extra_sql' => $where,
 201   'order_by'  => 'ORDER BY creditsourcebillpkgnum', #cust_bill. or cust_credit._date?
 202 };
 203
 204 my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ];
 205 my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ];
 206
 207 my $conf = new FS::Conf;
 208 my $money_char = $conf->config('money_char') || '$'; #well, no guarantee we're totaling these up in the multi-currency world
 209
 210 </%init>