3 my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
6 JOIN cust_bill USING ( invnum )
7 JOIN cust_main USING ( custnum )
11 LEFT JOIN cust_pkg USING ( pkgnum )
12 LEFT JOIN part_pkg USING ( pkgpart )
16 WHERE _date >= $beginning AND _date <= $ending
20 if ( $cgi->param('out') ) {
24 SELECT COUNT(*) FROM cust_main_county
25 WHERE ( cust_main_county.county = cust_main.county
26 OR ( cust_main_county.county IS NULL AND cust_main.county = '' )
27 OR ( cust_main_county.county = '' AND cust_main.county IS NULL)
28 OR ( cust_main_county.county IS NULL AND cust_main.county IS NULL)
30 AND ( cust_main_county.state = cust_main.state
31 OR ( cust_main_county.state IS NULL AND cust_main.state = '' )
32 OR ( cust_main_county.state = '' AND cust_main.state IS NULL )
33 OR ( cust_main_county.state IS NULL AND cust_main.state IS NULL )
35 AND cust_main_county.country = cust_main.country
36 AND cust_main_county.tax > 0
40 } elsif ( $cgi->param('country' ) ) {
42 my $county = dbh->quote( $cgi->param('county') );
43 my $state = dbh->quote( $cgi->param('state') );
44 my $country = dbh->quote( $cgi->param('country') );
46 AND ( county = $county OR $county = '' )
47 AND ( state = $state OR $state = '' )
48 AND country = $country
50 $where .= ' AND taxclass = '. dbh->quote( $cgi->param('taxclass') )
51 if $cgi->param('taxclass');
55 $where .= ' AND pkgnum != 0' if $cgi->param('nottax');
57 $where .= ' AND pkgnum = 0' if $cgi->param('istax');
59 $where .= " AND tax = 'Y'" if $cgi->param('cust_tax');
62 if ( $cgi->param('pkg_tax') ) {
65 "SELECT COUNT(*), SUM( ( CASE WHEN part_pkg.setuptax = 'Y'
66 THEN cust_bill_pkg.setup
69 ( CASE WHEN part_pkg.recurtax = 'Y'
70 THEN cust_bill_pkg.recur
75 ( part_pkg.setuptax = 'Y' AND cust_bill_pkg.setup > 0 )
76 OR ( part_pkg.recurtax = 'Y' AND cust_bill_pkg.recur > 0 )
82 "SELECT COUNT(*), SUM(cust_bill_pkg.setup + cust_bill_pkg.recur)";
85 $count_query .= " FROM cust_bill_pkg $join_cust $join_pkg $where";
88 'table' => 'cust_bill_pkg',
89 'addl_from' => "$join_cust $join_pkg",
91 'select' => join(', ',
96 FS::UI::Web::cust_sql_fields(),
98 'extra_sql' => $where,
101 my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ];
102 my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ];
104 my $conf = new FS::Conf;
105 my $money_char = $conf->config('money_char') || '$';
107 %><%= include( 'elements/search.html',
108 'title' => 'Line items',
109 'name' => 'line items',
111 'count_query' => $count_query,
112 'count_addl' => [ $money_char. '%.2f total', ],
120 FS::UI::Web::cust_header(),
124 sub { $_[0]->pkgnum > 0
126 : $_[0]->get('itemdesc')
128 #strikethrough or "N/A ($amount)" or something these when
129 # they're not applicable to pkg_tax search
130 sub { sprintf($money_char.'%.2f', shift->setup ) },
131 sub { sprintf($money_char.'%.2f', shift->recur ) },
133 sub { time2str('%b %d %Y', shift->_date ) },
134 \&FS::UI::Web::cust_fields,
143 ( map { $clink } FS::UI::Web::cust_header() ),