);
}
+sub netcredits {
+ my( $self, $speriod, $eperiod, $agentnum ) = @_;
+ $self->scalar_sql("
+ SELECT SUM(cust_credit_bill.amount)
+ 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._date'
+ )
+ );
+}
+
#these should be auto-generated or $AUTOLOADed or something
sub invoiced_12mo {
my( $self, $speriod, $eperiod, $agentnum ) = @_;
use vars qw( @ISA $conf );
use FS::UID qw( getotaker );
use FS::Record qw( qsearch qsearchs );
+use FS::cust_main_Mixin;
use FS::cust_bill_ApplicationCommon;
use FS::cust_bill;
use FS::cust_credit;
-@ISA = qw( FS::cust_bill_ApplicationCommon );
+@ISA = qw( FS::cust_main_Mixin FS::cust_bill_ApplicationCommon );
#ask FS::UID to run this stuff for us later
FS::UID->install_callback( sub {
my $agentname = $agent ? $agent->agent.' ' : '';
-my @items = qw( invoiced netsales credits payments receipts );
+my @items = qw( invoiced netsales credits netcredits payments receipts );
if ( $cgi->param('12mo') == 1 ) {
@items = map $_.'_12mo', @items;
}
my %label = (
- 'invoiced' => 'Gross Sales',
- 'netsales' => 'Net Sales',
- 'credits' => 'Credits',
- 'payments' => 'Gross Receipts',
- 'receipts' => 'Net Receipts',
+ 'invoiced' => 'Gross Sales',
+ 'netsales' => 'Net Sales',
+ 'credits' => 'Gross Credits',
+ 'netcredits' => 'Net Credits',
+ 'payments' => 'Gross Receipts',
+ 'receipts' => 'Net Receipts',
);
my %graph_suffix = (
- 'invoiced' => ' (invoiced)',
- 'netsales' => ' (invoiced - applied credits)',
- 'credits' => '',
- 'payments' => ' (payments)',
- 'receipts' => '/Cashflow (payments - refunds)',
+ 'invoiced' => ' (invoiced)',
+ 'netsales' => ' (invoiced - applied credits)',
+ 'credits' => ' (credited)',
+ 'netcredits' => ' (applied credits)',
+ 'payments' => ' (payments)',
+ 'receipts' => '/Cashflow (payments - refunds)',
);
my %graph_label = map { $_ => $label{$_}.$graph_suffix{$_} } keys %label;
foreach keys %graph_label;
my %color = (
- 'invoiced' => '9999ff', #light blue
- 'netsales' => '0000cc', #blue
- 'credits' => 'cc0000', #red
- 'payments' => '99cc99', #light green
- 'receipts' => '00cc00', #green
+ 'invoiced' => '9999ff', #light blue
+ 'netsales' => '0000cc', #blue
+ 'credits' => 'ff9999', #light red
+ 'netcredits' => 'cc0000', #red
+ 'payments' => '99cc99', #light green
+ 'receipts' => '00cc00', #green
);
$color{$_.'_12mo'} = $color{$_}
foreach keys %color;
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;",
- 'payments' => "${p}search/cust_pay.cgi?magic=_date;agentnum=$agentnum;",
+ '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.cgi?magic=_date;agentnum=$agentnum;",
);
# XXX link 12mo?
'name' => 'credits',
'query' => $sql_query,
'count_query' => $count_query,
- 'count_addl' => [ '$%.2f total credited', ],
+ 'count_addl' => [ '$%.2f total credited (gross)', ],
#'redirect' => $link,
'header' => [ 'Amount',
'Date',
--- /dev/null
+<% include( 'elements/search.html',
+ 'title' => $title,
+ 'name' => 'net credits',
+ 'query' => $sql_query,
+ 'count_query' => $count_query,
+ 'count_addl' => [ '$%.2f total credited (net)', ],
+ 'header' => [ 'Net applied',
+ 'to Invoice',
+ 'Credit',
+ 'By',
+ 'Reason',
+ FS::UI::Web::cust_header(),
+ ],
+ 'fields' => [
+ sub { $money_char. sprintf('%.2f', shift->amount ) },
+ sub { my $ccb = shift;
+ '#'.$ccb->invnum. ' '.
+ time2str('%b %d %Y', $ccb->cust_bill_date ).
+ " ($money_char".
+ sprintf('%.2f', $ccb->cust_bill_amount).
+ ")"
+ },
+ sub { my $ccb = shift;
+ time2str('%b %d %Y', $ccb->_date ).
+ " ($money_char".
+ sprintf('%.2f', $ccb->cust_credit_amount ).
+ ")"
+ },
+ sub { shift->cust_credit->otaker },
+ sub { shift->cust_credit->reason },
+ \&FS::UI::Web::cust_fields,
+ ],
+ 'align' => 'rrrll'.FS::UI::Web::cust_aligns(),
+ 'links' => [
+ '',
+ $cust_bill_link,
+ '',
+ '',
+ '',
+ ( map { $_ ne 'Cust. Status' ? $cust_link : '' }
+ FS::UI::Web::cust_header()
+ ),
+ ],
+ 'color' => [
+ '',
+ '',
+ '',
+ '',
+ '',
+ FS::UI::Web::cust_colors(),
+ ],
+ 'style' => [
+ '',
+ '',
+ '',
+ '',
+ '',
+ FS::UI::Web::cust_styles(),
+ ],
+ )
+%>
+<%init>
+
+die "access denied"
+ unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
+
+my $conf = new FS::Conf;
+my $money_char = $conf->config('money_char') || '$';
+
+my $title = 'Net Credit Search Results';
+
+my @search = ();
+
+if ( $cgi->param('agentnum') && $cgi->param('agentnum') =~ /^(\d+)$/ ) {
+ push @search, "agentnum = $1";
+ my $agent = qsearchs('agent', { 'agentnum' => $1 } );
+ die "unknown agentnum $1" unless $agent;
+ $title = $agent->agent. " $title";
+}
+
+my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
+push @search, "cust_bill._date >= $beginning ",
+ "cust_bill._date <= $ending";
+
+#here is the agent virtualization
+push @search, $FS::CurrentUser::CurrentUser->agentnums_sql;
+
+my $where = 'WHERE '. join(' AND ', @search);
+#
+my $count_query = 'SELECT COUNT(*), SUM(amount)
+ FROM cust_credit_bill
+ LEFT JOIN cust_bill USING ( invnum )
+ LEFT JOIN cust_main USING ( custnum ) '.
+ $where;
+
+my $sql_query = {
+ 'table' => 'cust_credit_bill',
+ 'select' => join(', ',
+ 'cust_credit_bill.*',
+ 'cust_credit.amount AS cust_credit_amount',
+ 'cust_bill._date AS cust_bill_date',
+ 'cust_bill.charged AS cust_bill_charged',
+ 'cust_credit.custnum AS custnum',
+ 'cust_main.custnum AS cust_main_custnum',
+ FS::UI::Web::cust_sql_fields(),
+ ),
+ 'hashref' => {},
+ 'extra_sql' => $where,
+ 'addl_from' => 'LEFT JOIN cust_bill USING ( invnum )
+ LEFT JOIN cust_credit USING ( crednum )
+ LEFT JOIN cust_main ON ( cust_bill.custnum = cust_main.custnum )',
+};
+
+my $cust_bill_link = sub {
+ my $cust_credit_bill = shift;
+ $cust_credit_bill->invnum
+ ? [ "${p}view/cust_bill.cgi?", 'invnum' ]
+ : '';
+};
+
+#my $cust_credit_link = sub {
+# my $cust_credit_bill = shift;
+# $cust_credit_bill->crednum
+# ? [ "${p}view/cust_credit.cgi?", 'crednum' ]
+# : '';
+#};
+
+my $cust_link = sub {
+ my $cust_credit_bill = shift;
+ $cust_credit_bill->cust_main_custnum
+ ? [ "${p}view/cust_main.cgi?", 'cust_main_custnum' ]
+ : '';
+};
+
+</%init>
projects / freeside.git / commitdiff