+<%doc>
+
+Display the Discount Detail Report:
+
+Parameters:
+
+ - discount_classnum
+ - usernum
+ - agentnum
+ - beginning
+ - ending
+ - include_waived_setup
+
+</%doc>
<& elements/search.html,
- 'title' => 'Discounts',
- 'name' => 'discounts',
- 'query' => $query,
- 'count_query' => $count_query,
- 'count_addl' => [ $money_char. '%.2f total', ],
- 'header' => [
- #'#',
- 'Discount',
- 'Class',
- 'Amount',
- 'Months',
- 'Package',
- 'Invoice',
- 'Date',
- FS::UI::Web::cust_header(),
- ],
- 'fields' => [
- #'billpkgdiscountnum',
- sub { $_[0]->cust_pkg_discount->discount->description },
- sub { $_[0]->cust_pkg_discount->discount->classname },
- sub { sprintf($money_char.'%.2f', shift->amount ) },
- $months_sub,
- 'pkg',#sub { $_[0]->cust_bill_pkg->cust_pkg->part_pkg->pkg },
- 'invnum',
- sub { time2str('%b %d %Y', shift->_date ) },
- \&FS::UI::Web::cust_fields,
- ],
- 'sort_fields' => [
- '',
- '',
- 'amount',
- 'months',
- 'pkg',
- 'invnum',
- '_date',
- ],
- 'links' => [
- #'',
- '', #link to customer discount???
- '',
- '',
- '',
- '',
- $ilink,
- $ilink,
- ( map { $_ ne 'Cust. Status' ? $clink : '' }
- FS::UI::Web::cust_header()
- ),
- ],
- #'align' => 'rlrrrc'.FS::UI::Web::cust_aligns(),
- 'align' => 'lcrrlrr'.FS::UI::Web::cust_aligns(),
- 'color' => [
- #'',
- '',
- '',
- '',
- '',
- '',
- '',
- '',
- FS::UI::Web::cust_colors(),
- ],
- 'style' => [
- #'',
- '',
- '',
- '',
- '',
- '',
- '',
- '',
- FS::UI::Web::cust_styles(),
- ],
+ title => 'Discounts',
+ name => 'discounts',
+ query => $query,
+ count_query => $count_query,
+ count_addl => [ $money_char . '%.2f total' ],
+
+ header => [
+ qw(
+ Discount
+ Class
+ Amount
+ Months
+ Package
+ Invoice
+ Date
+ ),
+ FS::UI::Web::cust_header(),
+ ],
+
+ fields => [
+ # Discount
+ sub {
+ if ( $_[0]->pkgdiscountnum ) {
+ # Standard discount, not a waived setup fee
+ my $discount = qsearchs('discount',{
+ pkgdiscountnum => $_[0]->pkgdiscountnum
+ });
+ return $discount->description;
+ } else {
+ return 'Waive setup fee';
+ }
+ },
+
+ # Class
+ sub {
+ if ( $_[0]->discountnum ) {
+ # Standard discount, not a waived setup fee
+ my $discount = qsearchs('discount',{
+ discountnum => $_[0]->discountnum
+ });
+ return $discount->classname;
+ } else {
+ return 'n/a';
+ }
+ },
+
+ # Amount
+ sub {
+ sprintf(
+ $money_char.'%.2f',
+ $_[0]->amount ? $_[0]->amount : $_[0]->setup_fee
+ )
+ },
+
+ # Months
+ sub {
+ my $row = shift;
+ if ($row->pkgdiscountnum) {
+ $row->setuprecur eq 'setup'
+ ? 'Setup'
+ : sprintf( '%.2f', $row->months ),
+ } else {
+ return 'Setup';
+ }
+ },
+
+ # Package
+ 'pkg',
+
+ # Invoice
+ 'invnum',
+
+ # Date
+ sub { time2str('%b %d %Y', $_[0]->_date) },
+
+ # Customer
+ \&FS::UI::Web::cust_fields,
+ ],
+
+ sort_fields => [
+ '', # Discount
+ '', # Class
+ 'amount', # Amount
+ 'months', # Months
+ 'pkg', # Package
+ 'invnum', # Invoice
+ '_date', # Date
+ ],
+
+ links => [
+ '', # Discount
+ '', # Class
+ '', # Amount
+ '', # Months
+ '', # Package
+ [ "${p}view/cust_bill.cgi?", 'invnum' ], # Invoice
+ [ "${p}view/cust_bill.cgi?", 'invnum' ], # Date
+ ( # Customer
+ map {
+ $_ ne 'Cust. Status'
+ ? [ "${p}view/cust_main.cgi?", 'custnum' ]
+ : ''
+ } FS::UI::Web::cust_header()
+ ),
+ ],
+
+ align => 'lcrrlrr'.FS::UI::Web::cust_aligns(),
+ color => [ '','','','','','','',FS::UI::Web::cust_colors() ],
+ style => [ '','','','','','','',FS::UI::Web::cust_styles() ],
&>
<%init>
-#a little false laziness below w/cust_bill_pkg.cgi
-
die "access denied"
unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
my $conf = new FS::Conf;
-#here is the agent virtualization
-my $agentnums_sql =
- $FS::CurrentUser::CurrentUser->agentnums_sql( 'table' => 'cust_main' );
+# Query building blocks
+my @select = (qw(
+ cust_bill_pkg_discount.billpkgdiscountnum
+ cust_bill_pkg_discount.pkgdiscountnum
+ cust_bill_pkg_discount.amount
+ cust_bill_pkg_discount.months
+
+ cust_bill.invnum
+ cust_bill._date
+
+ part_pkg.pkg
+
+ cust_main.custnum
+
+ cust_pkg_discount.setuprecur
+ cust_pkg_discount.discountnum
+ cust_pkg.waive_setup),
+
+ "
+ ( SELECT optionvalue
+ FROM part_pkg_option
+ WHERE
+ part_pkg_option.pkgpart = cust_pkg.pkgpart
+ AND optionname = 'setup_fee'
+ ) as setup_fee ",
+
+ "
+ COALESCE(
+ CAST(cust_bill_pkg_discount.amount AS TEXT),
+ ( SELECT optionvalue
+ FROM part_pkg_option
+ WHERE
+ part_pkg_option.pkgpart = cust_pkg.pkgpart
+ AND optionname = 'setup_fee'
+ )
+ ) as discountorwaive
+ ",
+
+ FS::UI::Web::cust_sql_fields(),
+);
+
+my $join = "
+LEFT JOIN cust_bill_pkg_discount
+ USING (billpkgnum)
-my @where = ( $agentnums_sql );
+LEFT JOIN cust_pkg_discount
+ ON cust_bill_pkg_discount.pkgdiscountnum = cust_pkg_discount.pkgdiscountnum
+LEFT JOIN cust_pkg
+ ON cust_bill_pkg.pkgnum = cust_pkg.pkgnum
+
+LEFT JOIN part_pkg
+ USING (pkgpart)
+
+LEFT JOIN cust_bill USING (invnum)
+
+LEFT JOIN cust_main
+ ON cust_bill.custnum = cust_main.custnum
+
+";
+
+# Each entry in @where will be joined with AND
+my @where = (
+
+ # Agent Virtualization
+ $FS::CurrentUser::CurrentUser->agentnums_sql(table => 'cust_main'),
+
+ # Select only rows where there is a discount or waived setup fee
+ "(
+ cust_bill_pkg_discount.billpkgdiscountnum IS NOT NULL
+ OR (
+ cust_pkg.setup = cust_bill_pkg.sdate
+ AND cust_pkg.waive_setup = 'Y'
+ )
+ )",
+
+);
+
+
+# Filter: Date Range
my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
-push @where, "_date >= $beginning",
- "_date <= $ending";
+push @where, "_date >= $beginning", "_date <= $ending";
+
+# Filter: Agent
if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
push @where, "cust_main.agentnum = $1";
}
-#usernum
+
+# Filter: User
if ( $cgi->param('usernum') =~ /^(\d+)$/ ) {
- push @where, "cust_pkg_discount.usernum = $1";
+ push @where,
+ "( cust_pkg_discount.usernum = $1 OR cust_pkg_discount.usernum IS NULL )";
}
-# (discount) classnum
-my $join_discount = '';
-#false laziness w/cust_pkg_discount.html and cust_pkg.pm::search
+# Filter: Include waived setup fees
+if ( !$cgi->param('include_waived_setup') ) {
+ push @where, "cust_bill_pkg_discount.pkgdiscountnum IS NOT NULL";
+}
+
+# Filter: Discount Class
if ( grep { $_ eq 'discount_classnum' } $cgi->param ) {
# my @classnum = ();
if ( $cgi->param('discount_classnum') =~ /^(\d*)$/ && $1 ne '0' ) {
my @classnum = ( $1 );
- $join_discount = 'LEFT JOIN discount USING (discountnum)';
+ $join .= ' LEFT JOIN discount USING (discountnum) ';
my @c_where = ();
my @nums = grep $_, @classnum;
# }
# }
-my $count_query = "SELECT COUNT(*), SUM(cust_bill_pkg_discount.amount)";
-
-my $join_cust_pkg_discount =
- 'LEFT JOIN cust_pkg_discount USING (pkgdiscountnum)';
-
-my $join_cust =
- ' JOIN cust_bill USING ( invnum ) '.
- FS::UI::Web::join_cust_main('cust_bill', 'cust_pkg');
-
-my $join_pkg =
- ' JOIN cust_bill_pkg USING ( billpkgnum )
- LEFT JOIN cust_pkg ON ( cust_bill_pkg.pkgnum = cust_pkg.pkgnum )
- LEFT JOIN part_pkg USING ( pkgpart ) ';
- #LEFT JOIN part_pkg AS override
- # ON pkgpart_override = override.pkgpart ';
-
-my $join = "$join_cust_pkg_discount $join_discount $join_pkg $join_cust";
+my $count_query;
+if ( $cgi->param('include_waived_setup') ) {
+ $count_query = "
+ SELECT
+ COUNT(*),
+ SUM(
+ COALESCE(
+ cust_bill_pkg_discount.amount,
+ CAST(( SELECT optionvalue
+ FROM part_pkg_option
+ WHERE
+ part_pkg_option.pkgpart = cust_pkg.pkgpart
+ AND optionname = 'setup_fee'
+ ) AS NUMERIC )
+ )
+ ) ";
+} else {
+ $count_query = "SELECT COUNT(*), SUM(cust_bill_pkg_discount.amount)";
+}
my $where = ' WHERE '. join(' AND ', @where);
-$count_query .= " FROM cust_bill_pkg_discount $join $where";
-
-my @select = (
- 'cust_bill_pkg_discount.*',
- #'cust_bill_pkg.*',
- 'cust_bill.invnum',
- 'cust_bill._date',
- );
-push @select, 'part_pkg.pkg';
-push @select, 'cust_main.custnum',
- FS::UI::Web::cust_sql_fields();
+$count_query .= " FROM cust_bill_pkg $join $where";
my $query = {
- 'table' => 'cust_bill_pkg_discount',
+ 'table' => 'cust_bill_pkg',
'addl_from' => $join,
'hashref' => {},
'select' => join(', ', @select ),
'order_by' => 'ORDER BY _date, billpkgdiscountnum',
};
-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') || '$';
-my $months_sub = sub {
- my $cust_bill_pkg_discount = shift;
- return 'Setup'
- if $cust_bill_pkg_discount->cust_pkg_discount->setuprecur eq 'setup';
- sprintf('%.2f', $cust_bill_pkg_discount->months);
-};
-
</%init>
% {
% $cgi->delete('maxrecords');
% $cgi->param('_dummy', 1);
+% my $query = $m->scomp('/elements/create_uri_query');
- ( show <SELECT NAME="maxrecords" onChange="window.location = '<% "$self_url?". $cgi->query_string %>;maxrecords=' + this.options[this.selectedIndex].value;">
+ ( show <SELECT NAME="maxrecords" onChange="window.location = '<% "$self_url?$query" %>;maxrecords=' + this.options[this.selectedIndex].value;">
% foreach my $max ( map { $_ * $confmax } qw( 1 5 10 25 ) ) {
<OPTION VALUE="<% $max %>" <% ( $maxrecords == $max ) ? 'SELECTED' : '' %>><% $max %></OPTION>
<% $opt{'download_label'} || 'Download results:' %>
% $cgi->param('_type', "$xlsname.xls" );
- <A HREF="<% "$self_url?". $cgi->query_string %>">Spreadsheet</A> |
+% my $query = $m->scomp('/elements/create_uri_query');
+ <A HREF="<% "$self_url?$query" %>">Spreadsheet</A> |
% $cgi->param('_type', 'csv');
- <A HREF="<% "$self_url?". $cgi->query_string %>">CSV</A> |
+% my $query = $m->scomp('/elements/create_uri_query');
+ <A HREF="<% "$self_url?$query" %>">CSV</A> |
% if ( defined($opt{xml_elements}) ) {
% $cgi->param('_type', 'xml');
- <A HREF="<% "$self_url?". $cgi->query_string %>">XML</A> |
+% my $query = $m->scomp('/elements/create_uri_query');
+ <A HREF="<% "$self_url?$query" %>">XML</A> |
% }
% $cgi->param('_type', 'html-print');
- <A HREF="<% "$self_url?". $cgi->query_string %>">webpage</A>
+% my $query = $m->scomp('/elements/create_uri_query');
+ <A HREF="<% "$self_url?$query" %>">webpage</A>
%# "save search" -- for now, obey disable_download and the 'Download
%# report data' ACL, because saving a search allows the user to receive
% } else {
% $cgi->param('order_by', $field);
% }
-% $label = qq(<A HREF="$self_url?). $cgi->query_string.
-% qq(">$label</A>);
+% my $query = $m->scomp('/elements/create_uri_query');
+% $label = qq(<A HREF="$self_url?$query">$label</A>);
% }
%
% $colspan = ref($header) ? $header->{colspan} : 0;
projects / freeside.git / commitdiff