3 %my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
6 % JOIN cust_bill USING ( invnum )
7 % LEFT JOIN cust_main USING ( custnum )
11 % LEFT JOIN cust_pkg USING ( pkgnum )
12 % LEFT JOIN part_pkg USING ( pkgpart )
15 %my $where = " WHERE _date >= $beginning AND _date <= $ending ";
17 %$where .= " AND payby != 'COMP' "
18 % unless $cgi->param('include_comp_cust');
20 %if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
21 % $where .= " AND agentnum = $1 ";
24 %if ( $cgi->param('classnum') =~ /^(\d+)$/ ) {
26 % $where .= " AND classnum IS NULL ";
28 % $where .= " AND classnum = $1 ";
32 %if ( $cgi->param('out') ) {
36 % SELECT COUNT(*) FROM cust_main_county
37 % WHERE ( cust_main_county.county = cust_main.county
38 % OR ( cust_main_county.county IS NULL AND cust_main.county = '' )
39 % OR ( cust_main_county.county = '' AND cust_main.county IS NULL)
40 % OR ( cust_main_county.county IS NULL AND cust_main.county IS NULL)
42 % AND ( cust_main_county.state = cust_main.state
43 % OR ( cust_main_county.state IS NULL AND cust_main.state = '' )
44 % OR ( cust_main_county.state = '' AND cust_main.state IS NULL )
45 % OR ( cust_main_county.state IS NULL AND cust_main.state IS NULL )
47 % AND cust_main_county.country = cust_main.country
48 % AND cust_main_county.tax > 0
52 %} elsif ( $cgi->param('country' ) ) {
54 % my $county = dbh->quote( $cgi->param('county') );
55 % my $state = dbh->quote( $cgi->param('state') );
56 % my $country = dbh->quote( $cgi->param('country') );
58 % AND ( county = $county OR $county = '' )
59 % AND ( state = $state OR $state = '' )
60 % AND country = $country
62 % $where .= ' AND taxclass = '. dbh->quote( $cgi->param('taxclass') )
63 % if $cgi->param('taxclass');
67 %$where .= ' AND pkgnum != 0' if $cgi->param('nottax');
69 %$where .= ' AND pkgnum = 0' if $cgi->param('istax');
71 %$where .= " AND tax = 'Y'" if $cgi->param('cust_tax');
74 %if ( $cgi->param('pkg_tax') ) {
77 % "SELECT COUNT(*), SUM(
78 % ( CASE WHEN part_pkg.setuptax = 'Y'
79 % THEN cust_bill_pkg.setup
84 % ( CASE WHEN part_pkg.recurtax = 'Y'
85 % THEN cust_bill_pkg.recur
93 % ( part_pkg.setuptax = 'Y' AND cust_bill_pkg.setup > 0 )
94 % OR ( part_pkg.recurtax = 'Y' AND cust_bill_pkg.recur > 0 )
96 % AND ( tax != 'Y' OR tax IS NULL )
102 % "SELECT COUNT(*), SUM(cust_bill_pkg.setup + cust_bill_pkg.recur)";
105 %$count_query .= " FROM cust_bill_pkg $join_cust $join_pkg $where";
108 % 'table' => 'cust_bill_pkg',
109 % 'addl_from' => "$join_cust $join_pkg",
111 % 'select' => join(', ',
115 % 'cust_main.custnum',
116 % FS::UI::Web::cust_sql_fields(),
118 % 'extra_sql' => $where,
121 %my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ];
122 %my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ];
124 %my $conf = new FS::Conf;
125 %my $money_char = $conf->config('money_char') || '$';
128 <% include( 'elements/search.html',
129 'title' => 'Line items',
130 'name' => 'line items',
132 'count_query' => $count_query,
133 'count_addl' => [ $money_char. '%.2f total', ],
141 FS::UI::Web::cust_header(),
145 sub { $_[0]->pkgnum > 0
147 : $_[0]->get('itemdesc')
149 #strikethrough or "N/A ($amount)" or something these when
150 # they're not applicable to pkg_tax search
151 sub { sprintf($money_char.'%.2f', shift->setup ) },
152 sub { sprintf($money_char.'%.2f', shift->recur ) },
154 sub { time2str('%b %d %Y', shift->_date ) },
155 \&FS::UI::Web::cust_fields,
164 ( map { $clink } FS::UI::Web::cust_header() ),