1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
|
<%
my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
my $join_cust = "
JOIN cust_bill USING ( invnum )
LEFT JOIN cust_main USING ( custnum )
";
my $join_pkg = "
LEFT JOIN cust_pkg USING ( pkgnum )
LEFT JOIN part_pkg USING ( pkgpart )
";
my $join = "
JOIN cust_bill_pkg USING ( billpkgnum )
$join_cust
$join_pkg
";
my $where = "
WHERE _date >= $beginning AND _date <= $ending
";
# AND payby != 'COMP'
if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
$where .= " AND agentnum = $1 ";
}
if ( $cgi->param('out') ) {
$where .= "
AND 0 = (
SELECT COUNT(*) FROM cust_main_county AS county_out
WHERE ( county_out.county = cust_main.county
OR ( county_out.county IS NULL AND cust_main.county = '' )
OR ( county_out.county = '' AND cust_main.county IS NULL)
OR ( county_out.county IS NULL AND cust_main.county IS NULL)
)
AND ( county_out.state = cust_main.state
OR ( county_out.state IS NULL AND cust_main.state = '' )
OR ( county_out.state = '' AND cust_main.state IS NULL )
OR ( county_out.state IS NULL AND cust_main.state IS NULL )
)
AND county_out.country = cust_main.country
AND county_out.tax > 0
)
";
} elsif ( $cgi->param('country' ) ) {
my $county = dbh->quote( $cgi->param('county') );
my $state = dbh->quote( $cgi->param('state') );
my $country = dbh->quote( $cgi->param('country') );
$where .= "
AND ( county = $county OR $county = '' )
AND ( state = $state OR $state = '' )
AND country = $country
";
$where .= ' AND taxclass = '. dbh->quote( $cgi->param('taxclass') )
if $cgi->param('taxclass');
}
my $count_query = "SELECT COUNT(*), SUM(amount)".
" FROM cust_tax_exempt_pkg $join $where";
my $query = {
'table' => 'cust_tax_exempt_pkg',
'addl_from' => $join,
'hashref' => {},
'select' => join(', ',
'cust_tax_exempt_pkg.*',
'cust_bill_pkg.*',
'cust_bill.*',
'part_pkg.pkg',
'cust_main.custnum',
FS::UI::Web::cust_sql_fields(),
),
'extra_sql' => $where,
};
my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ];
my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ];
my $conf = new FS::Conf;
my $money_char = $conf->config('money_char') || '$';
%><%= include( 'elements/search.html',
'title' => 'Tax exemptions',
'name' => 'tax exemptions',
'query' => $query,
'count_query' => $count_query,
'count_addl' => [ $money_char. '%.2f total', ],
'header' => [
'#',
'Month',
'Amount',
'Line item',
'Invoice',
'Date',
FS::UI::Web::cust_header(),
],
'fields' => [
'exemptpkgnum',
sub { $_[0]->month. '/'. $_[0]->year; },
sub { $money_char. $_[0]->amount; },
sub {
$_[0]->billpkgnum. ': '.
( $_[0]->pkgnum > 0
? $_[0]->get('pkg')
: $_[0]->get('itemdesc')
).
' ('.
( $_[0]->setup > 0
? $money_char. $_[0]->setup. ' setup'
: ''
).
( $_[0]->setup > 0 && $_[0]->recur > 0
? ' / '
: ''
).
( $_[0]->recur > 0
? $money_char. $_[0]->recur. ' recur'
: ''
).
')';
},
'invnum',
sub { time2str('%b %d %Y', shift->_date ) },
\&FS::UI::Web::cust_fields,
],
'links' => [
'',
'',
'',
'',
$ilink,
$ilink,
( map { $clink } FS::UI::Web::cust_header() ),
],
'align' => 'rrrlrc', # 'rlrrrc',
)
%>
|
