diff options
author | ivan <ivan> | 2007-08-14 21:46:18 +0000 |
---|---|---|
committer | ivan <ivan> | 2007-08-14 21:46:18 +0000 |
commit | 6b74e2e7a98a11b8b4f62a10676ba8b37ee2ef4f (patch) | |
tree | 1bfb827ae9ab09efd0faec58a8b559402ae68baa /FS/FS/Report/Table/Monthly.pm | |
parent | 0cd9e13c265f6acc6b2cccf58af54c445100e4d0 (diff) |
add package churn report/graph
Diffstat (limited to 'FS/FS/Report/Table/Monthly.pm')
-rw-r--r-- | FS/FS/Report/Table/Monthly.pm | 84 |
1 files changed, 40 insertions, 44 deletions
diff --git a/FS/FS/Report/Table/Monthly.pm b/FS/FS/Report/Table/Monthly.pm index 145f2a85c..a44cafe3e 100644 --- a/FS/FS/Report/Table/Monthly.pm +++ b/FS/FS/Report/Table/Monthly.pm @@ -147,7 +147,11 @@ sub netsales { #net sales 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') + WHERE ". $self->in_time_period_and_agent( $speriod, + $eperiod, + $agentnum, + 'cust_bill._date' + ) ); #horrible local kludge @@ -158,7 +162,11 @@ sub netsales { #net sales 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'). " + WHERE ". $self->in_time_period_and_agent( $speriod, + $eperiod, + $agentnum, + 'cust_bill._date' + ). " AND LOWER(part_pkg.pkg) LIKE 'expense _%' "); @@ -183,7 +191,11 @@ sub receipts { #cashflow 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'). " + WHERE ". $self->in_time_period_and_agent( $speriod, + $eperiod, + $agentnum, + 'cust_bill_pay._date' + ). " 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 @@ -285,54 +297,38 @@ sub cust_bill_pkg { } -# NEEDS TO BE AGENTNUM-capable -sub canceled { #active - my( $self, $speriod, $eperiod, $agentnum ) = @_; - $self->scalar_sql(" - 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, $agentnum ) = @_; - $self->scalar_sql(" - 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.susp IS NULL OR cust_pkg.susp = 0 ) - AND cust_pkg.setup > $speriod AND cust_pkg.setup < $eperiod - "); -} +sub setup_pkg { shift->pkg_field( @_, 'setup' ); } +sub susp_pkg { shift->pkg_field( @_, 'susp' ); } +sub cancel_pkg { shift->pkg_field( @_, 'cancel'); } -# NEEDS TO BE AGENTNUM-capable -sub suspended { #suspended - my( $self, $speriod, $eperiod, $agentnum ) = @_; +sub pkg_field { + my( $self, $speriod, $eperiod, $agentnum, $field ) = @_; $self->scalar_sql(" - SELECT COUNT(*) FROM cust_pkg - WHERE cust_pkg.custnum = cust_main.custnum - AND ( cust_pkg.cancel IS NULL OR cust_pkg.cancel = 0 ) - AND 0 = ( SELECT COUNT(*) FROM cust_pkg - WHERE cust_pkg.custnum = cust_main.custnum - AND ( cust_pkg.susp IS NULL OR cust_pkg.susp = 0 ) - ) - AND cust_pkg.susp > $speriod AND cust_pkg.susp < $eperiod - "); + SELECT COUNT(*) FROM cust_pkg + LEFT JOIN cust_main USING ( custnum ) + WHERE ". $self->in_time_period_and_agent( $speriod, + $eperiod, + $agentnum, + "cust_pkg.$field", + ) + ); + } +#this is going to be harder.. +#sub unsusp_pkg { +# my( $self, $speriod, $eperiod, $agentnum ) = @_; +# $self->scalar_sql(" +# SELECT COUNT(*) FROM h_cust_pkg +# WHERE +# +#} + sub in_time_period_and_agent { my( $self, $speriod, $eperiod, $agentnum ) = splice(@_, 0, 4); - my $table = @_ ? shift().'.' : ''; + my $col = @_ ? shift() : '_date'; - my $sql = "${table}_date >= $speriod AND ${table}_date < $eperiod"; + my $sql = "$col >= $speriod AND $col < $eperiod"; #agent selection $sql .= " AND agentnum = $agentnum" |