From: Ivan Kohler Date: Tue, 3 Jul 2012 10:05:10 +0000 (-0700) Subject: add advertising source to sales/credits/receipts summary, RT#18349 X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=commitdiff_plain;h=64948ee373a03fa156ebc89cb39adfa6d4d2c3d5 add advertising source to sales/credits/receipts summary, RT#18349 --- diff --git a/FS/FS/Report/Table.pm b/FS/FS/Report/Table.pm index e1aec0592..73eed6e0c 100644 --- a/FS/FS/Report/Table.pm +++ b/FS/FS/Report/Table.pm @@ -72,8 +72,8 @@ sub invoiced { #invoiced SELECT SUM(charged) FROM cust_bill LEFT JOIN cust_main USING ( custnum ) - WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum) - . (%opt ? $self->for_custnum(%opt) : '') + WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum). + $self->for_opts(%opt) ); } @@ -85,8 +85,8 @@ sub invoiced { #invoiced sub netsales { #net sales my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_; - $self->invoiced($speriod,$eperiod,$agentnum,%opt) - - $self->netcredits($speriod,$eperiod,$agentnum,%opt); + $self->invoiced( $speriod, $eperiod, $agentnum, %opt) + - $self->netcredits($speriod, $eperiod, $agentnum, %opt); } =item cashflow: payments - refunds @@ -105,10 +105,10 @@ sub cashflow { =cut sub netcashflow { - my( $self, $speriod, $eperiod, $agentnum ) = @_; + my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_; - $self->receipts($speriod, $eperiod, $agentnum) - - $self->netrefunds( $speriod, $eperiod, $agentnum); + $self->receipts( $speriod, $eperiod, $agentnum, %opt) + - $self->netrefunds( $speriod, $eperiod, $agentnum, %opt); } =item payments: The sum of payments received in the period. @@ -121,8 +121,8 @@ sub payments { SELECT SUM(paid) FROM cust_pay LEFT JOIN cust_main USING ( custnum ) - WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum) - . (%opt ? $self->for_custnum(%opt) : '') + WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum). + $self->for_opts(%opt) ); } @@ -131,12 +131,13 @@ sub payments { =cut sub credits { - my( $self, $speriod, $eperiod, $agentnum ) = @_; + my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_; $self->scalar_sql(" SELECT SUM(amount) FROM cust_credit LEFT JOIN cust_main USING ( custnum ) - WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum) + WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum). + $self->for_opts(%opt) ); } @@ -150,8 +151,8 @@ sub refunds { SELECT SUM(refund) FROM cust_refund LEFT JOIN cust_main USING ( custnum ) - WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum) - . (%opt ? $self->for_custnum(%opt) : '') + WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum). + $self->for_opts(%opt) ); } @@ -170,8 +171,8 @@ sub netcredits { $eperiod, $agentnum, 'cust_bill._date' - ) - . (%opt ? $self->for_custnum(%opt) : '') + ). + $self->for_opts(%opt) ); } @@ -180,7 +181,7 @@ sub netcredits { =cut sub receipts { #net payments - my( $self, $speriod, $eperiod, $agentnum ) = @_; + my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_; $self->scalar_sql(" SELECT SUM(cust_bill_pay.amount) FROM cust_bill_pay @@ -190,7 +191,8 @@ sub receipts { #net payments $eperiod, $agentnum, 'cust_bill._date' - ) + ). + $self->for_opts(%opt) ); } @@ -199,7 +201,7 @@ sub receipts { #net payments =cut sub netrefunds { - my( $self, $speriod, $eperiod, $agentnum ) = @_; + my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_; $self->scalar_sql(" SELECT SUM(cust_credit_refund.amount) FROM cust_credit_refund @@ -209,7 +211,8 @@ sub netrefunds { $eperiod, $agentnum, 'cust_credit._date' - ) + ). + $self->for_opts(%opt) ); } @@ -625,10 +628,16 @@ sub in_time_period_and_agent { $sql; } -sub for_custnum { +sub for_opts { my ( $self, %opt ) = @_; - return '' unless $opt{'custnum'}; - $opt{'custnum'} =~ /^\d+$/ ? " and custnum = $opt{custnum} " : ''; + my $sql = ''; + if ( $opt{'custnum'} =~ /^(\d+)$/ ) { + $sql .= " and custnum = $1 "; + } + if ( $opt{'refnum'} =~ /^(\d+)$/ ) { + $sql .= " and refnum = $1 "; + } + $sql; } sub with_classnum { diff --git a/FS/FS/Report/Table/Monthly.pm b/FS/FS/Report/Table/Monthly.pm index 87c13a8ca..86ab19b74 100644 --- a/FS/FS/Report/Table/Monthly.pm +++ b/FS/FS/Report/Table/Monthly.pm @@ -24,6 +24,7 @@ FS::Report::Table::Monthly - Tables of report data, indexed monthly 'end_year' => 2020, #opt 'agentnum' => 54 + 'refnum' => 54 'params' => [ [ 'paramsfor', 'item_one' ], [ 'item', 'two' ] ], # ... 'remove_empty' => 1, #collapse empty rows, default 0 'item_labels' => [ ], #useful with remove_empty @@ -59,6 +60,7 @@ sub data { } my $agentnum = $self->{'agentnum'}; + my $refnum = $self->{'refnum'}; if ( $projecting ) { @@ -110,11 +112,13 @@ sub data { my $item = $items[$i]; my @param = $self->{'params'} ? @{ $self->{'params'}[$i] }: (); push @param, 'project', $projecting; + push @param, 'refnum' => $refnum if $refnum; my $value = $self->$item($speriod, $eperiod, $agentnum, @param); push @{$data{data}->[$col]}, $value; $item = $items[$i+1]; @param = $self->{'params'} ? @{ $self->{'params'}[++$i] }: (); push @param, 'project', $projecting; + push @param, 'refnum' => $refnum if $refnum; $value = $self->$item($speriod, $eperiod, $agentnum, @param); push @{$data{data}->[$col++]}, $value; } @@ -122,6 +126,7 @@ sub data { my $item = $items[$i]; my @param = $self->{'params'} ? @{ $self->{'params'}[$col] }: (); push @param, 'project', $projecting; + push @param, 'refnum' => $refnum if $refnum; my $value = $self->$item($speriod, $eperiod, $agentnum, @param); push @{$data{data}->[$col++]}, $value; } diff --git a/FS/FS/cust_bill.pm b/FS/FS/cust_bill.pm index 35ab9f388..498025f7b 100644 --- a/FS/FS/cust_bill.pm +++ b/FS/FS/cust_bill.pm @@ -5693,7 +5693,12 @@ sub search_sql_where { push @search, "cust_main.agentnum = $1"; } - #agentnum + #refnum + if ( $param->{'refnum'} =~ /^(\d+)$/ ) { + push @search, "cust_main.refnum = $1"; + } + + #custnum if ( $param->{'custnum'} =~ /^(\d+)$/ ) { push @search, "cust_bill.custnum = $1"; } diff --git a/httemplate/graph/money_time.cgi b/httemplate/graph/money_time.cgi index cde71be76..166735fc6 100644 --- a/httemplate/graph/money_time.cgi +++ b/httemplate/graph/money_time.cgi @@ -1,5 +1,5 @@ <% include('elements/monthly.html', - 'title' => $agentname. + 'title' => $agentname. $referralname. 'Sales, Credits and Receipts Summary', 'items' => \@items, 'labels' => \%label, @@ -7,6 +7,7 @@ 'colors' => \%color, 'links' => \%link, 'agentnum' => $agentnum, + 'refnum' => $refnum, 'nototal' => scalar($cgi->param('12mo')), ) %> @@ -22,9 +23,17 @@ if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { $agent = qsearchs('agent', { 'agentnum' => $agentnum } ); die "agentnum $agentnum not found!" unless $agent; } - my $agentname = $agent ? $agent->agent.' ' : ''; +my( $refnum, $part_referral ) = ('', ''); +if ( $cgi->param('refnum') =~ /^(\d+)$/ ) { + $refnum = $1; + $part_referral = qsearchs('part_referral', { 'refnum' => $refnum } ); + die "refnum $refnum not found!" unless $part_referral; +} +my $referralname = $part_referral ? $part_referral->referral.' ' : ''; + + my @items = qw( invoiced netsales credits netcredits payments receipts @@ -83,15 +92,17 @@ my %color = ( $color{$_.'_12mo'} = $color{$_} foreach keys %color; +my $ar = "agentnum=$agentnum;refnum=$refnum"; + my %link = ( - 'invoiced' => "${p}search/cust_bill.html?agentnum=$agentnum;", - 'netsales' => "${p}search/cust_bill.html?agentnum=$agentnum;net=1;", - 'credits' => "${p}search/cust_credit.html?agentnum=$agentnum;", - 'netcredits' => "${p}search/cust_credit_bill.html?agentnum=$agentnum;", - 'payments' => "${p}search/cust_pay.html?magic=_date;agentnum=$agentnum;", - 'receipts' => "${p}search/cust_bill_pay.html?agentnum=$agentnum;", - 'refunds' => "${p}search/cust_refund.html?magic=_date;agentnum=$agentnum;", - 'netrefunds' => "${p}search/cust_credit_refund.html?agentnum=$agentnum;", + 'invoiced' => "${p}search/cust_bill.html?$ar;", + 'netsales' => "${p}search/cust_bill.html?$ar;net=1;", + 'credits' => "${p}search/cust_credit.html?$ar;", + 'netcredits' => "${p}search/cust_credit_bill.html?$ar;", + 'payments' => "${p}search/cust_pay.html?magic=_date;$ar;", + 'receipts' => "${p}search/cust_bill_pay.html?$ar;", + 'refunds' => "${p}search/cust_refund.html?magic=_date;$ar;", + 'netrefunds' => "${p}search/cust_credit_refund.html?$ar;", ); # XXX link 12mo? diff --git a/httemplate/graph/report_money_time.html b/httemplate/graph/report_money_time.html index b85bb6552..97876c996 100644 --- a/httemplate/graph/report_money_time.html +++ b/httemplate/graph/report_money_time.html @@ -24,6 +24,13 @@ ) %> +<% include('/elements/tr-select-part_referral.html', + 'label' => 'Advertising source ', + 'disable_empty' => 0, + 'empty_label' => 'all', + ) +%> + Show 12 month totals instead of monthly values diff --git a/httemplate/search/cust_bill.html b/httemplate/search/cust_bill.html index 813f9b843..406486a85 100755 --- a/httemplate/search/cust_bill.html +++ b/httemplate/search/cust_bill.html @@ -93,6 +93,10 @@ if ( $cgi->param('invnum') =~ /^\s*(FS-)?(\d+)\s*$/ ) { $search{'agentnum'} = $1; } + if ( $cgi->param('refnum') =~ /^(\d+)$/ ) { + $search{'refnum'} = $1; + } + if ( $cgi->param('custnum') =~ /^(\d+)$/ ) { $search{'custnum'} = $1; } diff --git a/httemplate/search/cust_bill_pay.html b/httemplate/search/cust_bill_pay.html index 1fc8ffd78..22e9a6795 100644 --- a/httemplate/search/cust_bill_pay.html +++ b/httemplate/search/cust_bill_pay.html @@ -92,6 +92,13 @@ if ( $cgi->param('agentnum') && $cgi->param('agentnum') =~ /^(\d+)$/ ) { $title = $agent->agent. " $title"; } +if ( $cgi->param('refnum') && $cgi->param('refnum') =~ /^(\d+)$/ ) { + push @search, "refnum = $1"; + my $part_referral = qsearchs('part_referral', { 'refnum' => $1 } ); + die "unknown refnum $1" unless $part_referral; + $title = $part_referral->referral. " $title"; +} + my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); push @search, "cust_bill._date >= $beginning ", "cust_bill._date <= $ending"; diff --git a/httemplate/search/cust_credit.html b/httemplate/search/cust_credit.html index 917257034..38f03491d 100755 --- a/httemplate/search/cust_credit.html +++ b/httemplate/search/cust_credit.html @@ -96,6 +96,13 @@ if ( $cgi->param('agentnum') && $cgi->param('agentnum') =~ /^(\d+)$/ ) { $title = $agent->agent. " $title"; } +if ( $cgi->param('refnum') && $cgi->param('refnum') =~ /^(\d+)$/ ) { + push @search, "refnum = $1"; + my $part_referral = qsearchs('part_referral', { 'refnum' => $1 } ); + die "unknown refnum $1" unless $part_referral; + $title = $part_referral->referral. " $title"; +} + if ( $unapplied ) { push @search, FS::cust_credit->unapplied_sql . ' > 0'; } diff --git a/httemplate/search/cust_credit_bill.html b/httemplate/search/cust_credit_bill.html index 7f9eb7887..9fd6a987a 100644 --- a/httemplate/search/cust_credit_bill.html +++ b/httemplate/search/cust_credit_bill.html @@ -85,6 +85,13 @@ if ( $cgi->param('agentnum') && $cgi->param('agentnum') =~ /^(\d+)$/ ) { $title = $agent->agent. " $title"; } +if ( $cgi->param('refnum') && $cgi->param('refnum') =~ /^(\d+)$/ ) { + push @search, "refnum = $1"; + my $part_referral = qsearchs('part_referral', { 'refnum' => $1 } ); + die "unknown refnum $1" unless $part_referral; + $title = $part_referral->referral. " $title"; +} + my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); push @search, "cust_bill._date >= $beginning ", "cust_bill._date <= $ending"; diff --git a/httemplate/search/cust_credit_refund.html b/httemplate/search/cust_credit_refund.html index fd87aa575..361c8ad2f 100644 --- a/httemplate/search/cust_credit_refund.html +++ b/httemplate/search/cust_credit_refund.html @@ -78,6 +78,13 @@ if ( $cgi->param('agentnum') && $cgi->param('agentnum') =~ /^(\d+)$/ ) { $title = $agent->agent. " $title"; } +if ( $cgi->param('refnum') && $cgi->param('refnum') =~ /^(\d+)$/ ) { + push @search, "refnum = $1"; + my $part_referral = qsearchs('part_referral', { 'refnum' => $1 } ); + die "unknown refnum $1" unless $part_referral; + $title = $part_referral->referral. " $title"; +} + my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); push @search, "cust_credit._date >= $beginning ", "cust_credit._date <= $ending"; diff --git a/httemplate/search/elements/cust_pay_or_refund.html b/httemplate/search/elements/cust_pay_or_refund.html index 002b1a4c2..dc3cb2a99 100755 --- a/httemplate/search/elements/cust_pay_or_refund.html +++ b/httemplate/search/elements/cust_pay_or_refund.html @@ -232,6 +232,13 @@ if ( $cgi->param('magic') ) { $title = $agent->agent. " $title"; } + if ( $cgi->param('refnum') && $cgi->param('refnum') =~ /^(\d+)$/ ) { + push @search, "refnum = $1"; + my $part_referral = qsearchs('part_referral', { 'refnum' => $1 } ); + die "unknown refnum $1" unless $part_referral; + $title = $part_referral->referral. " $title"; + } + if ( $cgi->param('custnum') =~ /^(\d+)$/ ) { push @search, "custnum = $1"; }