diff options
Diffstat (limited to 'FS')
-rw-r--r-- | FS/FS/Report.pm | 46 | ||||
-rw-r--r-- | FS/FS/Report/Table.pm | 27 | ||||
-rw-r--r-- | FS/FS/Report/Table/Monthly.pm | 166 | ||||
-rw-r--r-- | FS/MANIFEST | 6 | ||||
-rw-r--r-- | FS/t/Report-Table-Monthly.t | 5 | ||||
-rw-r--r-- | FS/t/Report-Table.t | 5 | ||||
-rw-r--r-- | FS/t/Report.t | 5 |
7 files changed, 260 insertions, 0 deletions
diff --git a/FS/FS/Report.pm b/FS/FS/Report.pm new file mode 100644 index 000000000..181fea2f6 --- /dev/null +++ b/FS/FS/Report.pm @@ -0,0 +1,46 @@ +package FS::Report; + +use strict; + +=head1 NAME + +FS::Report - Report data objects + +=head1 SYNOPSIS + + #see the more speicific report objects, currently only FS::Report::Table + +=head1 DESCRIPTION + +See the more specific report objects, currently only FS::Report::Table + +=head1 METHODS + +=over 4 + +=item new [ OPTION => VALUE ... ] + +Constructor. Takes a list of options and their values. + +=cut + +sub new { + my $proto = shift; + my $class = ref($proto) || $proto; + my $self = @_ ? ( ref($_[0]) ? shift : { @_ } ) : {}; + bless( $self, $class ); +} + +=back + +=head1 BUGS + +Documentation. + +=head1 SEE ALSO + +L<FS::Report::Table>, reports in the web interface. + +=cut + +1; diff --git a/FS/FS/Report/Table.pm b/FS/FS/Report/Table.pm new file mode 100644 index 000000000..9f636fa43 --- /dev/null +++ b/FS/FS/Report/Table.pm @@ -0,0 +1,27 @@ +package FS::Report::Table; + +use strict; +use vars qw( @ISA ); +use FS::Report; + +@ISA = qw( FS::Report ); + +=head1 NAME + +FS::Report::Table - Tables of report data + +=head1 SYNOPSIS + +See the more specific report objects, currently only FS::Report::Table::Monthly + +=head1 BUGS + +Documentation. + +=head1 SEE ALSO + +L<FS::Report::Table::Monthly>, reports in the web interface. + +=cut + +1; diff --git a/FS/FS/Report/Table/Monthly.pm b/FS/FS/Report/Table/Monthly.pm new file mode 100644 index 000000000..9aebeee3d --- /dev/null +++ b/FS/FS/Report/Table/Monthly.pm @@ -0,0 +1,166 @@ +package FS::Report::Table::Monthly; + +use strict; +use vars qw( @ISA ); +use Time::Local; +use FS::UID qw( dbh ); +use FS::Report::Table; + +@ISA = qw( FS::Report::Table ); + +=head1 NAME + +FS::Report::Table::Monthly - Tables of report data, indexed monthly + +=head1 SYNOPSIS + + use FS::Report::Table::Monthly; + + my $report = new FS::Report::Table ( + 'items' => [ 'invoiced', 'netsales', 'credits', 'receipts', ], + 'start_month' => 4, + 'start_year' => 2000, + 'end_month' => 4, + 'end_year' => 2020, + ); + + my $data = $report->data; + +=head1 METHODS + +=over 4 + +=item data + +Returns a hashref of data (!! describe) + +=cut + +sub data { + my $self = shift; + + my $smonth = $self->{'start_month'}; + my $syear = $self->{'start_year'}; + my $emonth = $self->{'end_month'}; + my $eyear = $self->{'end_year'}; + + my %data; + + while ( $syear < $eyear || ( $syear == $eyear && $smonth < $emonth+1 ) ) { + + push @{$data{label}}, "$smonth/$syear"; + + my $speriod = timelocal(0,0,0,1,$smonth-1,$syear); + if ( ++$smonth == 13 ) { $syear++; $smonth=1; } + my $eperiod = timelocal(0,0,0,1,$smonth-1,$syear); + + foreach my $item ( @{$self->{'items'}} ) { + push @{$data{$item}}, $self->$item($speriod, $eperiod); + } + + } + + \%data; + +} + +sub invoiced { #invoiced + my( $self, $speriod, $eperiod ) = ( shift, shift, shift ); + $self->scalar_sql(" + SELECT SUM(charged) FROM cust_bill + WHERE ". $self->in_time_period($speriod, $eperiod) + ); +} + +sub netsales { #net sales + my( $self, $speriod, $eperiod ) = ( shift, shift, shift ); + + 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') + ); + + #horrible local kludge + my $expenses = $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 _%' + "); + + $self->invoiced($speriod,$eperiod)-$credited-$expenses; +} + +#deferred revenue + +sub receipts { #cashflow + my( $self, $speriod, $eperiod ) = ( shift, shift, shift ); + + #cashflow + my $paid = $self->scalar_sql(" + SELECT SUM(paid) FROM cust_pay + WHERE ". $self->in_time_period($speriod, $eperiod) + ); + + my $refunded = $self->scalar_sql(" + SELECT SUM(refund) FROM cust_refund + WHERE ". $self->in_time_period($speriod, $eperiod) + ); + + #horrible local kludge that doesn't even really work right + my $expenses = $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'). " + 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 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 _%'"; + + $paid-$refunded-$expenses; +} + +sub credits { + my( $self, $speriod, $eperiod ) = ( shift, shift, shift ); + $self->scalar_sql(" + SELECT SUM(amount) FROM cust_credit + WHERE ". $self->in_time_period($speriod, $eperiod) + ); +} + +sub in_time_period { + my( $self, $speriod, $eperiod ) = ( shift, shift, shift ); + my $table = @_ ? shift().'.' : ''; + "${table}_date >= $speriod AND ${table}_date < $eperiod"; +} + +sub scalar_sql { + my( $self, $sql ) = ( shift, shift ); + my $sth = dbh->prepare($sql) or die dbh->errstr; + $sth->execute + or die "Unexpected error executing statement $sql: ". $sth->errstr; + $sth->fetchrow_arrayref->[0] || 0; +} + +=back + +=head1 BUGS + +Documentation. + +=head1 SEE ALSO + +=cut + +1; + diff --git a/FS/MANIFEST b/FS/MANIFEST index 3cbf0e91f..555509a41 100644 --- a/FS/MANIFEST +++ b/FS/MANIFEST @@ -37,6 +37,9 @@ FS/Conf.pm FS/ConfItem.pm FS/Misc.pm FS/Record.pm +FS/Report.pm +FS/Report/Table.pm +FS/Report/Table/Monthly.pm FS/SearchCache.pm FS/UI/Base.pm FS/UI/CGI.pm @@ -128,6 +131,9 @@ t/Conf.t t/ConfItem.t t/Misc.t t/Record.t +t/Report.pm +t/Report-Table.pm +t/Report-Table-Monthly.pm t/UID.t t/Msgcat.t t/SearchCache.t diff --git a/FS/t/Report-Table-Monthly.t b/FS/t/Report-Table-Monthly.t new file mode 100644 index 000000000..6ff365d1c --- /dev/null +++ b/FS/t/Report-Table-Monthly.t @@ -0,0 +1,5 @@ +BEGIN { $| = 1; print "1..1\n" } +END {print "not ok 1\n" unless $loaded;} +use FS::Report::Table::Monthly; +$loaded=1; +print "ok 1\n"; diff --git a/FS/t/Report-Table.t b/FS/t/Report-Table.t new file mode 100644 index 000000000..866d4981e --- /dev/null +++ b/FS/t/Report-Table.t @@ -0,0 +1,5 @@ +BEGIN { $| = 1; print "1..1\n" } +END {print "not ok 1\n" unless $loaded;} +use FS::Report::Table; +$loaded=1; +print "ok 1\n"; diff --git a/FS/t/Report.t b/FS/t/Report.t new file mode 100644 index 000000000..76d6ea489 --- /dev/null +++ b/FS/t/Report.t @@ -0,0 +1,5 @@ +BEGIN { $| = 1; print "1..1\n" } +END {print "not ok 1\n" unless $loaded;} +use FS::Report; +$loaded=1; +print "ok 1\n"; |