summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--FS/FS/Report/Table/Monthly.pm102
-rwxr-xr-xhttemplate/graph/money_time-graph.cgi7
-rw-r--r--httemplate/graph/money_time.cgi29
3 files changed, 89 insertions, 49 deletions
diff --git a/FS/FS/Report/Table/Monthly.pm b/FS/FS/Report/Table/Monthly.pm
index 89d44f92f..9ef108c44 100644
--- a/FS/FS/Report/Table/Monthly.pm
+++ b/FS/FS/Report/Table/Monthly.pm
@@ -45,6 +45,7 @@ sub data {
my $syear = $self->{'start_year'};
my $emonth = $self->{'end_month'};
my $eyear = $self->{'end_year'};
+ my $agentnum = $self->{'agentnum'};
my %data;
@@ -59,7 +60,7 @@ sub data {
push @{$data{eperiod}}, $eperiod;
foreach my $item ( @{$self->{'items'}} ) {
- push @{$data{$item}}, $self->$item($speriod, $eperiod);
+ push @{$data{$item}}, $self->$item($speriod, $eperiod, $agentnum);
}
}
@@ -69,53 +70,60 @@ sub data {
}
sub invoiced { #invoiced
- my( $self, $speriod, $eperiod ) = ( shift, shift, shift );
+ my( $self, $speriod, $eperiod, $agentnum ) = @_;
$self->scalar_sql("
- SELECT SUM(charged) FROM cust_bill
- WHERE ". $self->in_time_period($speriod, $eperiod)
+ SELECT SUM(charged)
+ FROM cust_bill
+ LEFT JOIN cust_main USING ( custnum )
+ WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum)
);
}
sub netsales { #net sales
- my( $self, $speriod, $eperiod ) = ( shift, shift, shift );
+ my( $self, $speriod, $eperiod, $agentnum ) = @_;
my $credited = $self->scalar_sql("
SELECT SUM(cust_credit_bill.amount)
- FROM cust_credit_bill, cust_bill
- WHERE cust_bill.invnum = cust_credit_bill.invnum
- AND ". $self->in_time_period($speriod, $eperiod, 'cust_bill')
+ FROM cust_credit_bill
+ LEFT JOIN cust_bill USING ( invnum )
+ LEFT JOIN cust_main USING ( custnum )
+ WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum, 'cust_bill')
);
#horrible local kludge
my $expenses = !$expenses_kludge ? 0 : $self->scalar_sql("
SELECT SUM(cust_bill_pkg.setup)
- FROM cust_bill_pkg, cust_bill, cust_pkg, part_pkg
- WHERE cust_bill.invnum = cust_bill_pkg.invnum
- AND ". $self->in_time_period($speriod, $eperiod, 'cust_bill'). "
- AND cust_pkg.pkgnum = cust_bill_pkg.pkgnum
- AND cust_pkg.pkgpart = part_pkg.pkgpart
- AND LOWER(part_pkg.pkg) LIKE 'expense _%'
+ FROM cust_bill_pkg
+ LEFT JOIN cust_bill USING ( invnum )
+ LEFT JOIN cust_main USING ( custnum )
+ LEFT JOIN cust_pkg USING ( pkgnum )
+ LEFT JOIN part_pkg USING ( pkgpart )
+ WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum, 'cust_bill'). "
+ AND LOWER(part_pkg.pkg) LIKE 'expense _%'
");
- $self->invoiced($speriod,$eperiod) - $credited - $expenses;
+ $self->invoiced($speriod,$eperiod,$agentnum) - $credited - $expenses;
}
#deferred revenue
sub receipts { #cashflow
- my( $self, $speriod, $eperiod ) = ( shift, shift, shift );
+ my( $self, $speriod, $eperiod, $agentnum ) = @_;
my $refunded = $self->scalar_sql("
- SELECT SUM(refund) FROM cust_refund
- WHERE ". $self->in_time_period($speriod, $eperiod)
+ SELECT SUM(refund)
+ FROM cust_refund
+ LEFT JOIN cust_main USING ( custnum )
+ WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum)
);
#horrible local kludge that doesn't even really work right
my $expenses = !$expenses_kludge ? 0 : $self->scalar_sql("
SELECT SUM(cust_bill_pay.amount)
- FROM cust_bill_pay, cust_bill
- WHERE cust_bill_pay.invnum = cust_bill.invnum
- AND ". $self->in_time_period($speriod, $eperiod, 'cust_bill_pay'). "
+ FROM cust_bill_pay
+ LEFT JOIN cust_bill USING ( invnum )
+ LEFT JOIN cust_main USING ( custnum )
+ WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum, 'cust_bill_pay'). "
AND 0 < ( SELECT COUNT(*) from cust_bill_pkg, cust_pkg, part_pkg
WHERE cust_bill.invnum = cust_bill_pkg.invnum
AND cust_pkg.pkgnum = cust_bill_pkg.pkgnum
@@ -125,40 +133,48 @@ sub receipts { #cashflow
");
# my $expenses_sql2 = "SELECT SUM(cust_bill_pay.amount) FROM cust_bill_pay, cust_bill_pkg, cust_bill, cust_pkg, part_pkg WHERE cust_bill_pay.invnum = cust_bill.invnum AND cust_bill.invnum = cust_bill_pkg.invnum AND cust_bill_pay._date >= $speriod AND cust_bill_pay._date < $eperiod AND cust_pkg.pkgnum = cust_bill_pkg.pkgnum AND cust_pkg.pkgpart = part_pkg.pkgpart AND LOWER(part_pkg.pkg) LIKE 'expense _%'";
- $self->payments($speriod, $eperiod) - $refunded - $expenses;
+ $self->payments($speriod, $eperiod, $agentnum) - $refunded - $expenses;
}
sub payments {
- my( $self, $speriod, $eperiod ) = ( shift, shift, shift );
+ my( $self, $speriod, $eperiod, $agentnum ) = @_;
$self->scalar_sql("
- SELECT SUM(paid) FROM cust_pay
- WHERE ". $self->in_time_period($speriod, $eperiod)
+ SELECT SUM(paid)
+ FROM cust_pay
+ LEFT JOIN cust_main USING ( custnum )
+ WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum)
);
}
sub credits {
- my( $self, $speriod, $eperiod ) = ( shift, shift, shift );
+ my( $self, $speriod, $eperiod, $agentnum ) = @_;
$self->scalar_sql("
- SELECT SUM(amount) FROM cust_credit
- WHERE ". $self->in_time_period($speriod, $eperiod)
+ SELECT SUM(amount)
+ FROM cust_credit
+ LEFT JOIN cust_main USING ( custnum )
+ WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum)
);
}
+# NEEDS TO BE AGENTNUM-capable
sub canceled { #active
- my( $self, $speriod, $eperiod ) = ( shift, shift, shift );
+ my( $self, $speriod, $eperiod, $agentnum ) = @_;
$self->scalar_sql("
- SELECT COUNT(*) FROM cust_pkg
- WHERE cust_pkg.custnum = cust_main.custnum
- AND 0 = ( SELECT COUNT(*) FROM cust_pkg
- WHERE cust_pkg.custnum = cust_main.custnum
- AND ( cust_pkg.cancel IS NULL OR cust_pkg.cancel = 0 )
- )
- AND cust_pkg.cancel > $speriod AND cust_pkg.cancel < $eperiod
+ SELECT COUNT(*)
+ FROM cust_pkg
+ LEFT JOIN cust_main USING ( custnum )
+ WHERE 0 = ( SELECT COUNT(*)
+ FROM cust_pkg
+ WHERE cust_pkg.custnum = cust_main.custnum
+ AND ( cust_pkg.cancel IS NULL OR cust_pkg.cancel = 0 )
+ )
+ AND cust_pkg.cancel > $speriod AND cust_pkg.cancel < $eperiod
");
}
+# NEEDS TO BE AGENTNUM-capable
sub newaccount { #newaccount
- my( $self, $speriod, $eperiod ) = ( shift, shift, shift );
+ my( $self, $speriod, $eperiod, $agentnum ) = @_;
$self->scalar_sql("
SELECT COUNT(*) FROM cust_pkg
WHERE cust_pkg.custnum = cust_main.custnum
@@ -168,8 +184,9 @@ sub newaccount { #newaccount
");
}
+# NEEDS TO BE AGENTNUM-capable
sub suspended { #suspended
- my( $self, $speriod, $eperiod ) = ( shift, shift, shift );
+ my( $self, $speriod, $eperiod, $agentnum ) = @_;
$self->scalar_sql("
SELECT COUNT(*) FROM cust_pkg
WHERE cust_pkg.custnum = cust_main.custnum
@@ -182,10 +199,13 @@ sub suspended { #suspended
");
}
-sub in_time_period {
- my( $self, $speriod, $eperiod ) = ( shift, shift, shift );
+sub in_time_period_and_agent {
+ my( $self, $speriod, $eperiod, $agentnum ) = splice(@_, 0, 4);
my $table = @_ ? shift().'.' : '';
- "${table}_date >= $speriod AND ${table}_date < $eperiod";
+ my $sql = "${table}_date >= $speriod AND ${table}_date < $eperiod";
+ $sql .= " AND agentnum = $agentnum"
+ if $agentnum;
+ $sql;
}
sub scalar_sql {
diff --git a/httemplate/graph/money_time-graph.cgi b/httemplate/graph/money_time-graph.cgi
index bb3d23aae..fc8207a81 100755
--- a/httemplate/graph/money_time-graph.cgi
+++ b/httemplate/graph/money_time-graph.cgi
@@ -12,6 +12,12 @@ my $eyear = $cgi->param('eyear') || 1900+$curyear;
my $emonth = $cgi->param('emonth') || $curmon+1;
#if ( $emonth++>12 ) { $emonth-=12; $eyear++; }
+# XXX or virtual
+my $agentnum = '';
+if ( $cgi->param('agentnum') =~ /^(\d*)$/ ) {
+ $agentnum = $1;
+}
+
#my @labels;
#my %data;
@@ -37,6 +43,7 @@ my $report = new FS::Report::Table::Monthly (
'start_year' => $syear,
'end_month' => $emonth,
'end_year' => $eyear,
+ 'agentnum' => $agentnum,
);
my %data = %{$report->data};
diff --git a/httemplate/graph/money_time.cgi b/httemplate/graph/money_time.cgi
index 1c7d54266..874f58b09 100644
--- a/httemplate/graph/money_time.cgi
+++ b/httemplate/graph/money_time.cgi
@@ -12,13 +12,23 @@ my $smonth = $cgi->param('smonth') || $curmon+1;
my $eyear = $cgi->param('eyear') || 1900+$curyear;
my $emonth = $cgi->param('emonth') || $curmon+1;
+#XXX or virtual
+my( $agentnum, $agent ) = ('', '');
+if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
+ $agentnum = $1;
+ $agent = qsearchs('agent', { 'agentnum' => $agentnum } );
+ die "agentnum $agentnum not found!" unless $agent;
+}
+my $agentname = $agent ? $agent->agent.' ' : '';
+warn $agentname;
+
+%>
+
+<%= include('/elements/header.html',
+ $agentname. 'Sales, Credits and Receipts Summary'
+ )
%>
-<HTML>
- <HEAD>
- <TITLE>Sales, Credits and Receipts Summary</TITLE>
- </HEAD>
-<BODY BGCOLOR="#e8e8e8">
<IMG SRC="money_time-graph.cgi?<%= $cgi->query_string %>" WIDTH="976" HEIGHT="384">
<BR>
@@ -41,9 +51,9 @@ my %color = (
'receipts' => '00cc00', #green
);
my %link = (
- 'invoiced' => "${p}search/cust_bill.html?",
- 'credits' => "${p}search/cust_credit.html?",
- 'payments' => "${p}search/cust_pay.cgi?magic=_date;",
+ 'invoiced' => "${p}search/cust_bill.html?agentnum=$agentnum;",
+ 'credits' => "${p}search/cust_credit.html?agentnum=$agentnum;",
+ 'payments' => "${p}search/cust_pay.cgi?magic=_date;agentnum=$agentnum;",
);
my $report = new FS::Report::Table::Monthly (
@@ -52,6 +62,7 @@ my $report = new FS::Report::Table::Monthly (
'start_year' => $syear,
'end_month' => $emonth,
'end_year' => $eyear,
+ 'agentnum' => $agentnum,
);
my $data = $report->data;
@@ -119,6 +130,8 @@ From <SELECT NAME="smonth">
<% } %>
</SELECT>
+for agent: <%= include('/elements/select-agent.html', $agentnum) %>
+
<INPUT TYPE="submit" VALUE="Redisplay">
</FORM>
</BODY>
generated by cgit v1.2.1 (git 2.18.0) at 2025-06-14 12:21:55 +0000