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
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
|
<& elements/search.html,
'title' => $title,
'name_singular' => $name,
'header' => \@header,
'query' => { 'select' => $select,
'table' => 'part_pkg',
'addl_from' => $addl_from,
'hashref' => {},
'extra_sql' => $extra_sql,
'order_by' => "ORDER BY $order_by",
},
'count_query' => $count_query,
'fields' => \@fields,
'links' => \@links,
'align' => $align,
'sort_fields' => [],
&>
<%init>
#this is about reports about packages definitions (starting w/commission ones)
# while browse/part_pkg.cgi is config->package definitions
my $curuser = $FS::CurrentUser::CurrentUser;
die "access denied"
unless $curuser->access_right('Employees: Commission Report'); #that's all this does so far
my $conf = new FS::Conf;
my $money_char = $conf->config('money_char') || '$';
my $title = 'Package definition report';
my $name = 'package definition';
my $select = '';
my $addl_from = '';
my @where = ();
my @top_where = ();
my @order_by = ();
my @header = ();
my @fields = ();
my @links = ();
my $align = '';
my $group_by = '';
my $match = '';
if (1) { #commission reports
if (1) { #employee commission reports
$group_by = join(', ',
( map "access_user.$_", qw( usernum username ) ),
( map "part_pkg.$_", qw( pkgpart pkg comment plan ) ),
);
$select = $group_by;
$addl_from .= ' CROSS JOIN access_user ';
$match = 'cust_pkg.usernum = access_user.usernum';
if ( $cgi->param('usernum') =~ /^(\d+)$/ ) {
#XXX in this context, agent virt for employees, not package defs
my $access_user = qsearchs('access_user', { 'usernum' => $1, })
or die "unknown usernum";
$title = $access_user->name;
push @top_where, 'access_user.usernum = '. $access_user->usernum;
} else {
push @header, 'Employee';
push @fields, sub { shift->get('username'); }; #access_user->name
push @links, ''; #link to employee edit w/ACL?
$align .= 'c';
push @order_by, 'usernum'; #join to username? we're mostly interested in grouping rather than order
$title = 'Employee';
}
} elsif (0) { #agent commission reports
$match = 'cust_main.agentnum = agent.agentnum'; #XXX need to join to this
if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
#agent virt
my $agent = qsearchs('agent', { 'agentnum' => $1 })
or die "unknown agentnum";
$title = $agent->agent;
push @header, 'Agent';
push @fields, sub { 'XXXagent' };
push @links, ''; #link to agent edit w/ACL?
$align .= 'c';
push @order_by, 'agentnum'; #join to agent? we're mostly interested in grouping rather than order
} else {
$title = 'Agent';
}
}
$addl_from .= "
LEFT JOIN cust_pkg ON ( part_pkg.pkgpart = cust_pkg.pkgpart
AND $match )
LEFT JOIN cust_bill_pkg USING ( pkgnum )
LEFT JOIN cust_bill USING ( invnum )
";
$title .= ' commission report';
$name = "commissionable $name";
}
push @header, 'Package definition';
push @fields, 'pkg_comment';
push @links, ''; #link to pkg definition edit w/ACL?
$align .= 'l';
if (1) { #commission reports
my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
push @where, " cust_bill_pkg.pkgnum > 0 ",
" cust_bill._date >= $beginning ",
" cust_bill._date <= $ending ";
push @header, '#'; # of sales';
push @links, ''; #link to detail report
$align .= 'r';
push @fields, 'num_cust_pkg';
$select .= ", COUNT(DISTINCT cust_pkg.pkgnum) AS num_cust_pkg";
push @header, 'Sales';
push @links, ''; #link to detail report
$align .= 'r';
# push @fields, sub { $money_char. sprintf('%.2f', shift->get('pkg_sales')); };
# $select .=
# ", SUM( SELECT setup+recur $from_cust_bill_pkg_where ) AS pkg_sales";
push @fields, sub {
my $part_pkg = shift;
my $sql = "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur)".
# " FROM cust_bill_pkg $addl_from $where AND pkgpart = ? AND ";
" FROM cust_bill_pkg
LEFT JOIN cust_pkg USING ( pkgnum )
LEFT JOIN cust_bill USING ( invnum )
WHERE ". join(' AND ', @where).
" AND pkgpart = ? AND ";
my @arg = ($part_pkg->pkgpart);
if (1) { #employee commission reports
$sql .= 'usernum = ?';
push @arg, $part_pkg->get('usernum');
} elsif (0) { #agent commission reports
$match = 'cust_main.agentnum = agent.agentnum';
}
my $sth = dbh->prepare($sql) or die dbh->errstr;
$sth->execute(@arg) or die $sth->errstr;
$money_char. sprintf('%.2f', $sth->fetchrow_arrayref->[0] );
};
push @header, 'Commission';
push @links, ''; #link to detail report
$align .= 'r';
#push @fields, sub { $money_char. sprintf('%.2f', shift->get('pkg_commission')); };
push @fields, sub {
my $part_pkg = shift;
my $sql = "SELECT SUM(amount) FROM cust_credit
LEFT JOIN cust_event USING ( eventnum )
LEFT JOIN part_event USING ( eventpart )
LEFT JOIN cust_pkg ON ( cust_event.tablenum = cust_pkg.pkgnum )
WHERE eventnum IS NOT NULL
AND action IN ( 'pkg_employee_credit',
'pkg_employee_credit_pkg'
)
AND cust_credit._date >= $beginning
AND cust_credit._date <= $ending
AND pkgpart = ?
AND cust_credit.custnum = ?
";
my @arg = ($part_pkg->pkgpart);
if (1) { #employee commission reports
#XXX in this context, agent virt for employees, not package defs
my $access_user = qsearchs('access_user', { 'usernum' => $part_pkg->get('usernum'), })
or die "unknown usernum";
return $money_char.'0.00' unless $access_user->user_custnum;
push @arg, $access_user->user_custnum;
} elsif (0) { #agent commission reports
push @arg, 'XXXagent_custnum'; #$agent->agent_custnum
}
#warn $sql;
#warn join(',', @arg);
my $sth = dbh->prepare($sql) or die dbh->errstr;
$sth->execute(@arg) or die $sth->errstr;
$money_char. sprintf('%.2f', $sth->fetchrow_arrayref->[0] );
};
}
push @order_by, 'pkgpart'; #pkg?
$select ||= 'part_pkg.*';
push @top_where, @where;
my $extra_sql = scalar(@top_where) ? 'WHERE ' . join(' AND ', @top_where) : '';
$extra_sql .= "GROUP BY $group_by" if $group_by;
my $order_by = join(', ', @order_by);
#XXX agent?
#my $count_query = "SELECT COUNT(*) FROM ( SELECT 1 FROM part_pkg $addl_from $extra_sql ) AS num";
#mysql?
my $count_query = "SELECT COUNT(*) FROM ( SELECT DISTINCT part_pkg.pkgpart FROM part_pkg $addl_from $extra_sql ) AS num";
#warn $count_query;
</%init>
|
