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
|
<% include( 'elements/search.html',
'title' => 'Legacy tax exemptions',
'name' => 'legacy tax exemptions',
'query' => $query,
'count_query' => $count_query,
'count_addl' => [ $money_char. '%.2f total', ],
'header' => [
'#',
'Month',
'Inserted',
'Amount',
FS::UI::Web::cust_header(),
],
'fields' => [
'exemptnum',
sub { $_[0]->month. '/'. $_[0]->year; },
sub { my $h = $_[0]->h_search('insert');
$h ? time2str('%L/%d/%Y', $h->history_date ) : ''
},
sub { $money_char. $_[0]->amount; },
\&FS::UI::Web::cust_fields,
],
'links' => [
'',
'',
'',
'',
( map { $_ ne 'Cust. Status' ? $clink : '' }
FS::UI::Web::cust_header()
),
],
'align' => 'rrrr'.FS::UI::Web::cust_aligns(),
'color' => [
'',
'',
'',
'',
FS::UI::Web::cust_colors(),
],
'style' => [
'',
'',
'',
'',
FS::UI::Web::cust_styles(),
],
)
%>
<%init>
my $join_cust = "
LEFT JOIN cust_main USING ( custnum )
";
die "access denied"
unless $FS::CurrentUser::CurrentUser->access_right('View customer tax exemptions');
my @where = ();
#my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
#if ( $beginning || $ending ) {
# push @where, "_date >= $beginning",
# "_date <= $ending";
# #"payby != 'COMP';
#}
if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
push @where, "agentnum = $1";
}
if ( $cgi->param('custnum') =~ /^(\d+)$/ ) {
push @where, "cust_main.custnum = $1";
}
#prospect active inactive suspended cancelled
if ( grep { $cgi->param('status') eq $_ } FS::cust_main->statuses() ) {
my $method = $cgi->param('status'). '_sql';
#push @where, $class->$method();
push @where, FS::cust_main->$method();
}
if ( $cgi->param('out') ) {
push @where, "
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') );
push @where, "( county = $county OR $county = '' )",
"( state = $state OR $state = '' )",
" country = $country";
push @where, 'taxclass = '. dbh->quote( $cgi->param('taxclass') )
if $cgi->param('taxclass');
}
my $where = scalar(@where) ? 'WHERE '.join(' AND ', @where) : '';
my $count_query = "SELECT COUNT(*), SUM(amount)".
" FROM cust_tax_exempt $join_cust $where";
my $query = {
'table' => 'cust_tax_exempt',
'addl_from' => $join_cust,
'hashref' => {},
'select' => join(', ',
'cust_tax_exempt.*',
'cust_main.custnum',
FS::UI::Web::cust_sql_fields(),
),
'extra_sql' => $where,
};
my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ];
my $conf = new FS::Conf;
my $money_char = $conf->config('money_char') || '$';
</%init>
|
