X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=FS%2FFS%2Fcust_bill.pm;h=c59948808347f1e3ec9d883b94a2ad69f18a4c77;hb=45c73e0d548b950dd29c21d863c239f6114a2083;hp=ede9a0e19380d952775496130df6dee0bb2fb4ad;hpb=cd907554f4c08175c379e204e28e26483acf957a;p=freeside.git diff --git a/FS/FS/cust_bill.pm b/FS/FS/cust_bill.pm index ede9a0e19..c59948808 100644 --- a/FS/FS/cust_bill.pm +++ b/FS/FS/cust_bill.pm @@ -24,6 +24,7 @@ use FS::cust_credit_bill; use FS::pay_batch; use FS::cust_pay_batch; use FS::cust_bill_event; +use FS::cust_event; use FS::part_pkg; use FS::cust_bill_pay; use FS::cust_bill_pay_batch; @@ -271,8 +272,7 @@ sub open_cust_bill_pkg { =item cust_bill_event -Returns the completed invoice events (see L) for this -invoice. +Returns the completed invoice events (deprecated, old-style events - see L) for this invoice. =cut @@ -281,6 +281,54 @@ sub cust_bill_event { qsearch( 'cust_bill_event', { 'invnum' => $self->invnum } ); } +=item num_cust_bill_event + +Returns the number of completed invoice events (deprecated, old-style events - see L) for this invoice. + +=cut + +sub num_cust_bill_event { + my $self = shift; + my $sql = + "SELECT COUNT(*) FROM cust_bill_event WHERE invnum = ?"; + my $sth = dbh->prepare($sql) or die dbh->errstr. " preparing $sql"; + $sth->execute($self->invnum) or die $sth->errstr. " executing $sql"; + $sth->fetchrow_arrayref->[0]; +} + +=item cust_event + +Returns the new-style customer billing events (see L) for this invoice. + +=cut + +#false laziness w/cust_pkg.pm +sub cust_event { + my $self = shift; + qsearch({ + 'table' => 'cust_event', + 'addl_from' => 'JOIN part_event USING ( eventpart )', + 'hashref' => { 'tablenum' => $self->invnum }, + 'extra_sql' => " AND eventtable = 'cust_bill' ", + }); +} + +=item num_cust_event + +Returns the number of new-style customer billing events (see L) for this invoice. + +=cut + +#false laziness w/cust_pkg.pm +sub num_cust_event { + my $self = shift; + my $sql = + "SELECT COUNT(*) FROM cust_event JOIN part_event USING ( eventpart ) ". + " WHERE tablenum = ? AND eventtable = 'cust_bill'"; + my $sth = dbh->prepare($sql) or die dbh->errstr. " preparing $sql"; + $sth->execute($self->invnum) or die $sth->errstr. " executing $sql"; + $sth->fetchrow_arrayref->[0]; +} =item cust_main @@ -418,6 +466,19 @@ sub owed { sub apply_payments_and_credits { my $self = shift; + local $SIG{HUP} = 'IGNORE'; + local $SIG{INT} = 'IGNORE'; + local $SIG{QUIT} = 'IGNORE'; + local $SIG{TERM} = 'IGNORE'; + local $SIG{TSTP} = 'IGNORE'; + local $SIG{PIPE} = 'IGNORE'; + + my $oldAutoCommit = $FS::UID::AutoCommit; + local $FS::UID::AutoCommit = 0; + my $dbh = dbh; + + $self->select_for_update; #mutex + my @payments = grep { $_->unapplied > 0 } $self->cust_main->cust_pay; my @credits = grep { $_->credited > 0 } $self->cust_main->cust_credit; @@ -483,10 +544,17 @@ sub apply_payments_and_credits { $app->invnum( $self->invnum ); my $error = $app->insert; + if ( $error ) { + $dbh->rollback if $oldAutoCommit; + return "Error inserting ". $app->table. " record: $error"; + } die $error if $error; } + $dbh->commit or die $dbh->errstr if $oldAutoCommit; + ''; #no error + } =item generate_email PARAMHASH @@ -1613,8 +1681,10 @@ sub print_text { #setup template variables package FS::cust_bill::_template; #! - use vars qw( $custnum $invnum $date $agent @address $overdue - $page $total_pages @buf ); + use vars qw( $company_name $company_address + $custnum $invnum $date $agent @address $overdue + $page $total_pages @buf + ); $custnum = $self->custnum; $invnum = $self->invnum; @@ -1662,6 +1732,10 @@ sub print_text { # && $self->printed > 0 # ); + $FS::cust_bill::_template::company_name = $conf->config('company_name'); + $FS::cust_bill::_template::company_address = + join("\n", $conf->config('company_address') ). "\n"; + #and subroutine for the template sub FS::cust_bill::_template::invoice_lines { my $lines = shift || scalar(@buf); @@ -1746,34 +1820,49 @@ sub print_latex { my $returnaddress; if ( length($conf->config_orbase('invoice_latexreturnaddress', $template)) ) { + $returnaddress = join("\n", $conf->config_orbase('invoice_latexreturnaddress', $template) ); + + } elsif ( grep /\S/, $conf->config('company_address') ) { + + $returnaddress = + join( '\\*'."\n", map s/( {2,})/'~' x length($1)/eg, + $conf->config('company_address') + ); + } else { + + my $warning = "Couldn't find a return address; ". + "do you need to set the company_address configuration value?"; + warn "$warning\n"; $returnaddress = '~'; + #$returnaddress = $warning; + } my %invoice_data = ( - 'custnum' => $self->custnum, - 'invnum' => $self->invnum, - 'date' => time2str('%b %o, %Y', $self->_date), - 'today' => time2str('%b %o, %Y', $today), - 'agent' => _latex_escape($cust_main->agent->agent), - 'payname' => _latex_escape($cust_main->payname), - 'company' => _latex_escape($cust_main->company), - 'address1' => _latex_escape($cust_main->address1), - 'address2' => _latex_escape($cust_main->address2), - 'city' => _latex_escape($cust_main->city), - 'state' => _latex_escape($cust_main->state), - 'zip' => _latex_escape($cust_main->zip), - 'footer' => join("\n", $conf->config_orbase('invoice_latexfooter', $template) ), - 'smallfooter' => join("\n", $conf->config_orbase('invoice_latexsmallfooter', $template) ), - 'returnaddress' => $returnaddress, - 'quantity' => 1, - 'terms' => $self->terms, - #'notes' => join("\n", $conf->config('invoice_latexnotes') ), + 'company_name' => $conf->config('company_name'), + 'company_address' => join("\n", $conf->config('company_address') ). "\n", + 'custnum' => $self->custnum, + 'invnum' => $self->invnum, + 'date' => time2str('%b %o, %Y', $self->_date), + 'today' => time2str('%b %o, %Y', $today), + 'agent' => _latex_escape($cust_main->agent->agent), + 'payname' => _latex_escape($cust_main->payname), + 'company' => _latex_escape($cust_main->company), + 'address1' => _latex_escape($cust_main->address1), + 'address2' => _latex_escape($cust_main->address2), + 'city' => _latex_escape($cust_main->city), + 'state' => _latex_escape($cust_main->state), + 'zip' => _latex_escape($cust_main->zip), + 'returnaddress' => $returnaddress, + 'quantity' => 1, + 'terms' => $self->terms, + #'notes' => join("\n", $conf->config('invoice_latexnotes') ), # better hang on to conf_dir for a while - 'conf_dir' => "$FS::UID::conf_dir/conf.$FS::UID::datasrc", + 'conf_dir' => "$FS::UID::conf_dir/conf.$FS::UID::datasrc", ); my $countrydefault = $conf->config('countrydefault') || 'US'; @@ -1783,18 +1872,24 @@ sub print_latex { $invoice_data{'country'} = _latex_escape(code2country($cust_main->country)); } - $invoice_data{'notes'} = - join("\n", -# #do variable substitutions in notes -# map { my $b=$_; $b =~ s/\$(\w+)/$invoice_data{$1}/eg; $b } - $conf->config_orbase('invoice_latexnotes', $template) - ); - warn "invoice notes: ". $invoice_data{'notes'}. "\n" - if $DEBUG; + #do variable substitution in notes, footer, smallfooter + foreach my $include (qw( notes footer smallfooter )) { - $invoice_data{'footer'} =~ s/\n+$//; - $invoice_data{'smallfooter'} =~ s/\n+$//; - $invoice_data{'notes'} =~ s/\n+$//; + my $inc_tt = new Text::Template ( + TYPE => 'ARRAY', + SOURCE => [ map "$_\n", + $conf->config_orbase("invoice_latex$include", $template ) + ], + DELIMITERS => [ '[@--', '--@]' ], + ) or die "can't create new Text::Template object: $Text::Template::ERROR"; + + $inc_tt->compile() + or die "can't compile template: $Text::Template::ERROR"; + + $invoice_data{$include} = $inc_tt->fill_in( HASH => \%invoice_data ); + + $invoice_data{$include} =~ s/\n+$//; + } $invoice_data{'po_line'} = ( $cust_main->payby eq 'BILL' && $cust_main->payinfo ) @@ -2149,31 +2244,37 @@ sub print_html { or die 'While compiling ' . $templatefile . ': ' . $Text::Template::ERROR; my %invoice_data = ( - 'custnum' => $self->custnum, - 'invnum' => $self->invnum, - 'date' => time2str('%b %o, %Y', $self->_date), - 'today' => time2str('%b %o, %Y', $today), - 'agent' => encode_entities($cust_main->agent->agent), - 'payname' => encode_entities($cust_main->payname), - 'company' => encode_entities($cust_main->company), - 'address1' => encode_entities($cust_main->address1), - 'address2' => encode_entities($cust_main->address2), - 'city' => encode_entities($cust_main->city), - 'state' => encode_entities($cust_main->state), - 'zip' => encode_entities($cust_main->zip), - 'terms' => $self->terms, - 'cid' => $cid, - 'template' => $template, -# 'conf_dir' => "$FS::UID::conf_dir/conf.$FS::UID::datasrc", + 'company_name' => $conf->config('company_name'), + 'company_address' => join("\n", $conf->config('company_address') ). "\n", + 'custnum' => $self->custnum, + 'invnum' => $self->invnum, + 'date' => time2str('%b %o, %Y', $self->_date), + 'today' => time2str('%b %o, %Y', $today), + 'agent' => encode_entities($cust_main->agent->agent), + 'payname' => encode_entities($cust_main->payname), + 'company' => encode_entities($cust_main->company), + 'address1' => encode_entities($cust_main->address1), + 'address2' => encode_entities($cust_main->address2), + 'city' => encode_entities($cust_main->city), + 'state' => encode_entities($cust_main->state), + 'zip' => encode_entities($cust_main->zip), + 'terms' => $self->terms, + 'cid' => $cid, + 'template' => $template, +# 'conf_dir' => "$FS::UID::conf_dir/conf.$FS::UID::datasrc", ); if ( defined( $conf->config_orbase('invoice_htmlreturnaddress', $template) ) && length( $conf->config_orbase('invoice_htmlreturnaddress', $template) ) ) { + $invoice_data{'returnaddress'} = - join("\n", $conf->config('invoice_htmlreturnaddress', $template) ); - } else { + join("\n", $conf->config_orbase('invoice_htmlreturnaddress', $template) ); + + } elsif ( grep /\S/, + $conf->config_orbase( 'invoice_latexreturnaddress', $template ) ) { + $invoice_data{'returnaddress'} = join("\n", map { s/~/ /g; @@ -2185,6 +2286,19 @@ sub print_html { $template ) ); + + } elsif ( grep /\S/, $conf->config('company_address') ) { + + $invoice_data{'returnaddress'} = + join("\n", $conf->config('company_address') ); + + } else { + + my $warning = "Couldn't find a return address; ". + "do you need to set the company_address configuration value?"; + warn "$warning\n"; + #$invoice_data{'returnaddress'} = $warning; + } my $countrydefault = $conf->config('countrydefault') || 'US'; @@ -2204,13 +2318,14 @@ sub print_html { } else { $invoice_data{'notes'} = join("\n", map { - s/%%(.*)$//; - s/\\section\*\{\\textsc\{(.)(.*)\}\}/

$1<\/font>\U$2<\/b>/; - s/\\begin\{enumerate\}/

    /; - s/\\item /
  1. /; - s/\\end\{enumerate\}/<\/ol>/; - s/\\textbf\{(.*)\}/$1<\/b>/; + s/%%(.*)$//g; + s/\\section\*\{\\textsc\{(.)(.*)\}\}/

    $1<\/font>\U$2<\/b>/g; + s/\\begin\{enumerate\}/

      /g; + s/\\item /
    1. /g; + s/\\end\{enumerate\}/<\/ol>/g; + s/\\textbf\{(.*)\}/$1<\/b>/g; s/\\\\\*/ /; + s/\\dollar ?/\$/g; $_; } $conf->config_orbase('invoice_latexnotes', $template) @@ -2605,7 +2720,6 @@ sub process_re_X { sub re_X { my($method, $job, %param ) = @_; -# [ 'begin', 'end', 'agentnum', 'open', 'days', 'newest_percust' ], if ( $DEBUG ) { warn "re_X $method for job $job with param:\n". join( '', map { " $_ => ". $param{$_}. "\n" } keys %param ); @@ -2615,43 +2729,14 @@ sub re_X { my $distinct = ''; my $orderby = 'ORDER BY cust_bill._date'; - my @where; - - if ( $param{'begin'} =~ /^(\d+)$/ ) { - push @where, "cust_bill._date >= $1"; - } - if ( $param{'end'} =~ /^(\d+)$/ ) { - push @where, "cust_bill._date < $1"; - } - if ( $param{'agentnum'} =~ /^(\d+)$/ ) { - push @where, "cust_main.agentnum = $1"; - } - - my $owed = - "charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay - WHERE cust_bill_pay.invnum = cust_bill.invnum ) - - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill - WHERE cust_credit_bill.invnum = cust_bill.invnum )"; - - push @where, "0 != $owed" - if $param{'open'}; - - push @where, "cust_bill._date < ". (time-86400*$param{'days'}) - if $param{'days'}; - - my $extra_sql = scalar(@where) ? 'WHERE '. join(' AND ', @where) : ''; + my $extra_sql = ' WHERE '. FS::cust_bill->search_sql(\%param); my $addl_from = 'left join cust_main using ( custnum )'; - - if ( $param{'newest_percust'} ) { - $distinct = 'DISTINCT ON ( cust_bill.custnum )'; - $orderby = 'ORDER BY cust_bill.custnum ASC, cust_bill._date DESC'; - #$count_query = "SELECT COUNT(DISTINCT cust_bill.custnum), 'N/A', 'N/A'"; - } my @cust_bill = qsearch( 'cust_bill', {}, - "$distinct cust_bill.*", + #"$distinct cust_bill.*", + "cust_bill.*", $extra_sql, '', $addl_from @@ -2678,13 +2763,151 @@ sub re_X { =back +=head1 CLASS METHODS + +=over 4 + +=item owed_sql + +Returns an SQL fragment to retreive the amount owed (charged minus credited and paid). + +=cut + +sub owed_sql { + my $class = shift; + 'charged - '. $class->paid_sql. ' - '. $class->credited_sql; +} + +=item net_sql + +Returns an SQL fragment to retreive the net amount (charged minus credited). + +=cut + +sub net_sql { + my $class = shift; + 'charged - '. $class->credited_sql; +} + +=item paid_sql + +Returns an SQL fragment to retreive the amount paid against this invoice. + +=cut + +sub paid_sql { + #my $class = shift; + "( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay + WHERE cust_bill.invnum = cust_bill_pay.invnum )"; +} + +=item credited_sql + +Returns an SQL fragment to retreive the amount credited against this invoice. + +=cut + +sub credited_sql { + #my $class = shift; + "( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill + WHERE cust_bill.invnum = cust_credit_bill.invnum )"; +} + +=item search_sql HASHREF + +Class method which returns an SQL WHERE fragment to search for parameters +specified in HASHREF. Valid parameters are + +=over 4 + +=item begin - epoch date (UNIX timestamp) setting a lower bound for _date values + +=item end - epoch date (UNIX timestamp) setting an upper bound for _date values + +=item invnum_min + +=item invnum_max + +=item agentnum + +=item owed + +=item net + +=item days + +=item newest_percust + +=back + +Note: validates all passed-in data; i.e. safe to use with unchecked CGI params. + +=cut + +sub search_sql { + my($class, $param) = @_; + my @search = (); + + if ( $param->{'begin'} =~ /^(\d+)$/ ) { + push @search, "cust_bill._date >= $1"; + } + if ( $param->{'end'} =~ /^(\d+)$/ ) { + push @search, "cust_bill._date < $1"; + } + if ( $param->{'invnum_min'} =~ /^(\d+)$/ ) { + push @search, "cust_bill.invnum >= $1"; + } + if ( $param->{'invnum_max'} =~ /^(\d+)$/ ) { + push @search, "cust_bill.invnum <= $1"; + } + if ( $param->{'agentnum'} =~ /^(\d+)$/ ) { + push @search, "cust_main.agentnum = $1"; + } + + push @search, '0 != '. FS::cust_bill->owed_sql + if $param->{'open'}; + + push @search, '0 != '. FS::cust_bill->net_sql + if $param->{'net'}; + + push @search, "cust_bill._date < ". (time-86400*$param->{'days'}) + if $param->{'days'}; + + if ( $param->{'newest_percust'} ) { + + #$distinct = 'DISTINCT ON ( cust_bill.custnum )'; + #$orderby = 'ORDER BY cust_bill.custnum ASC, cust_bill._date DESC'; + + my @newest_where = map { my $x = $_; + $x =~ s/\bcust_bill\./newest_cust_bill./g; + $x; + } + grep ! /^cust_main./, @search; + my $newest_where = scalar(@newest_where) + ? ' AND '. join(' AND ', @newest_where) + : ''; + + + push @search, "cust_bill._date = ( + SELECT(MAX(newest_cust_bill._date)) FROM cust_bill AS newest_cust_bill + WHERE newest_cust_bill.custnum = cust_bill.custnum + $newest_where + )"; + + } + + push @search, $FS::CurrentUser::CurrentUser->agentnums_sql; + + join(' AND ', @search ); + +} + +=back + =head1 BUGS The delete method. -print_text formatting (and some logic :/) is in source, but needs to be -slurped in from a file. Also number of lines ($=). - =head1 SEE ALSO L, L, L, L,