agent-specific sales/credit/receipts summary

diff --git a/FS/FS/Report/Table/Monthly.pm b/FS/FS/Report/Table/Monthly.pm
index 89d44f9..9ef108c 100644 (file)
--- 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 $syear = $self->{'start_year'};
   my $emonth = $self->{'end_month'};
   my $eyear = $self->{'end_year'};

+  my $agentnum = $self->{'agentnum'};

 
   my %data;
 
 
   my %data;
 


@@ -59,7 +60,7 @@ sub data {
     push @{$data{eperiod}}, $eperiod;
   
     foreach my $item ( @{$self->{'items'}} ) {
     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
 }
 
 sub invoiced { #invoiced

-  my( $self, $speriod, $eperiod ) = ( shift, shift, shift );
+  my( $self, $speriod, $eperiod, $agentnum ) = @_;

   $self->scalar_sql("
   $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
   );
 }
 
 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)
 
   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)
   );
 
   #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
 }
 
 #deferred revenue
 
 sub receipts { #cashflow

-  my( $self, $speriod, $eperiod ) = ( shift, shift, shift );
+  my( $self, $speriod, $eperiod, $agentnum ) = @_;

 
   my $refunded = $self->scalar_sql("
 
   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)
   );
 
   #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
     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 _%'";
   
   ");
   #    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 {
 }
 
 sub payments {

-  my( $self, $speriod, $eperiod ) = ( shift, shift, shift );
+  my( $self, $speriod, $eperiod, $agentnum ) = @_;

   $self->scalar_sql("
   $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 {
   );
 }
 
 sub credits {

-  my( $self, $speriod, $eperiod ) = ( shift, shift, shift );
+  my( $self, $speriod, $eperiod, $agentnum ) = @_;

   $self->scalar_sql("
   $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
 sub canceled { #active

-  my( $self, $speriod, $eperiod ) = ( shift, shift, shift );
+  my( $self, $speriod, $eperiod, $agentnum ) = @_;

   $self->scalar_sql("
   $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
 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
   $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
 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
   $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().'.' : '';
   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 {
 }
 
 sub scalar_sql {

diff --git a/httemplate/graph/money_time-graph.cgi b/httemplate/graph/money_time-graph.cgi
index bb3d23a..fc8207a 100755 (executable)
--- 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++; }
 
 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;
 
 #my @labels;
 #my %data;
 


@@ -37,6 +43,7 @@ my $report = new FS::Report::Table::Monthly (
   'start_year'  => $syear,
   'end_month'   => $emonth,
   'end_year'    => $eyear,
   'start_year'  => $syear,
   'end_month'   => $emonth,
   'end_year'    => $eyear,

+  'agentnum'    => $agentnum,

 );
 my %data = %{$report->data};
 
 );
 my %data = %{$report->data};
 

diff --git a/httemplate/graph/money_time.cgi b/httemplate/graph/money_time.cgi
index 1c7d542..874f58b 100644 (file)
--- 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;
 
 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>
 
 <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 = (
   '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 (
 );
 
 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,
   'start_year'  => $syear,
   'end_month'   => $emonth,
   'end_year'    => $eyear,

+  'agentnum'    => $agentnum,

 );
 my $data = $report->data;
 
 );
 my $data = $report->data;
 


@@ -119,6 +130,8 @@ From <SELECT NAME="smonth">
 <% } %>
 </SELECT>
 
 <% } %>
 </SELECT>
 

+for agent: <%= include('/elements/select-agent.html', $agentnum) %>
+

 <INPUT TYPE="submit" VALUE="Redisplay">
 </FORM>
 </BODY>
 <INPUT TYPE="submit" VALUE="Redisplay">
 </FORM>
 </BODY>