From: Ivan Kohler Date: Thu, 13 Nov 2014 22:28:36 +0000 (-0800) Subject: Merge branch 'master' of git.freeside.biz:/home/git/freeside X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=commitdiff_plain;h=52a824f9b11ca266be10c76894eaf6607344c8d0;hp=4af695fe8521e17dd67ba34df929f35fdad4c505 Merge branch 'master' of git.freeside.biz:/home/git/freeside --- diff --git a/FS/FS/Conf.pm b/FS/FS/Conf.pm index ca6588e06..cb4274f39 100644 --- a/FS/FS/Conf.pm +++ b/FS/FS/Conf.pm @@ -3729,7 +3729,7 @@ and customer address. Include units.', 'type' => 'select', 'select_enum' => [ 'NACHA', 'csv-td_canada_trust-merchant_pc_batch', 'csv-chase_canada-E-xactBatch', 'BoM', 'PAP', - 'paymentech', 'ach-spiritone', 'RBC' + 'paymentech', 'ach-spiritone', 'RBC', 'CIBC', ] }, @@ -3791,7 +3791,7 @@ and customer address. Include units.', 'type' => 'select', 'select_enum' => [ 'NACHA', 'csv-td_canada_trust-merchant_pc_batch', 'BoM', 'PAP', 'paymentech', 'ach-spiritone', 'RBC', - 'td_eft1464', 'eft_canada' + 'td_eft1464', 'eft_canada', 'CIBC' ] }, @@ -3809,6 +3809,13 @@ and customer address. Include units.', 'type' => 'textarea', }, +{ + 'key' => 'batchconfig-CIBC', + 'section' => 'billing', + 'description' => 'Configuration for Canadian Imperial Bank of Commerce, six lines: 1. Origin ID, 2. Datacenter, 3. Typecode, 4. Short name, 5. Bank, 6. Bank account', + 'type' => 'textarea', + }, + { 'key' => 'batchconfig-PAP', 'section' => 'billing', diff --git a/FS/FS/Report/Table.pm b/FS/FS/Report/Table.pm index 3a4a1695d..934287a15 100644 --- a/FS/FS/Report/Table.pm +++ b/FS/FS/Report/Table.pm @@ -664,7 +664,7 @@ sub cust_bill_pkg_discount { } -##### churn report ##### +##### package churn report ##### =item active_pkg: The number of packages that were active at the start of the period. The end date of the period is ignored. Options: @@ -756,7 +756,91 @@ sub pkg_where { @where; } -##### end of churn report stuff ##### +##### end of package churn report stuff ##### + +##### customer churn report ##### + +=item active_cust: The number of customers who had any active recurring +packages at the start of the period. The end date is ignored, agentnum is +mandatory, and no other parameters are accepted. + +=item started_cust: The number of customers who had no active packages at +the start of the period, but had active packages at the end. Like +active_cust, agentnum is mandatory and no other parameters are accepted. + +=item suspended_cust: The number of customers who had active packages at +the start of the period, and at the end had no active packages but some +suspended packages. Note that this does not necessarily mean that their +packages were suspended during the period. + +=item resumed_cust: The inverse of suspended_cust: the number of customers +who had suspended packages and no active packages at the start of the +period, and active packages at the end. + +=item cancelled_cust: The number of customers who had active packages +at the start of the period, and only cancelled packages at the end. + +=cut + +sub active_cust { + my $self = shift; + $self->churn_cust(@_)->{active}; +} +sub started_cust { + my $self = shift; + $self->churn_cust(@_)->{started}; +} +sub suspended_cust { + my $self = shift; + $self->churn_cust(@_)->{suspended}; +} +sub resumed_cust { + my $self = shift; + $self->churn_cust(@_)->{resumed}; +} +sub cancelled_cust { + my $self = shift; + $self->churn_cust(@_)->{cancelled}; +} + +sub churn_cust { + my $self = shift; + my ( $speriod ) = @_; + + # run one query for each interval + return $self->{_interval}{$speriod} ||= $self->calculate_churn_cust(@_); +} + +sub calculate_churn_cust { + my $self = shift; + my ($speriod, $eperiod, $agentnum, %opt) = @_; + + my $churn_sql = FS::cust_main::Status->churn_sql($speriod, $eperiod); + my $where = ''; + $where = " WHERE cust_main.agentnum = $agentnum " if $agentnum; + my $cust_sql = + "SELECT churn.* ". + "FROM cust_main JOIN ($churn_sql) AS churn USING (custnum)". + $where; + + # query to count the ones with certain status combinations + my $total_sql = " + SELECT SUM((s_active > 0)::int) as active, + SUM((s_active = 0 and e_active > 0)::int) as started, + SUM((s_active > 0 and e_active = 0 and e_suspended > 0)::int) + as suspended, + SUM((s_active = 0 and s_suspended > 0 and e_active > 0)::int) + as resumed, + SUM((s_active > 0 and e_active = 0 and e_suspended = 0)::int) + as cancelled + FROM ($cust_sql) AS x + "; + + my $sth = dbh->prepare($total_sql); + $sth->execute or die "failed to execute churn query: " . $sth->errstr; + + $self->{_interval}{$speriod} = $sth->fetchrow_hashref; +} sub in_time_period_and_agent { my( $self, $speriod, $eperiod, $agentnum ) = splice(@_, 0, 4); diff --git a/FS/FS/cust_main/Status.pm b/FS/FS/cust_main/Status.pm index c17bb5711..9a3fe1bbe 100644 --- a/FS/FS/cust_main/Status.pm +++ b/FS/FS/cust_main/Status.pm @@ -118,6 +118,42 @@ sub cancelled_sql { =back +=head1 CLASS METHODS + +=over 4 + +=item churn_sql START, END + +Returns an SQL statement for the customer churn status query. The columns +returned are the custnum and the number of active, suspended, and cancelled +packages (excluding one-time packages) at the start date ("s_active", +"s_suspended", and "s_cancelled") and the end date ("e_active", etc.). + +=cut + +# not sure this belongs here...FS::cust_main::Packages? + +sub churn_sql { + my $self = shift; + my ($speriod, $eperiod) = @_; + + my $s_sql = FS::h_cust_pkg->status_as_of_sql($speriod); + my $e_sql = FS::h_cust_pkg->status_as_of_sql($eperiod); + + my @select = ( + 'custnum', + 'COALESCE(SUM(s.is_active::int),0) as s_active', + 'COALESCE(SUM(s.is_suspended::int),0) as s_suspended', + 'COALESCE(SUM(s.is_cancelled::int),0) as s_cancelled', + 'COALESCE(SUM(e.is_active::int),0) as e_active', + 'COALESCE(SUM(e.is_suspended::int),0) as e_suspended', + 'COALESCE(SUM(e.is_cancelled::int),0) as e_cancelled', + ); + my $from = "($s_sql) AS s FULL JOIN ($e_sql) AS e USING (custnum)"; + + return "SELECT ".join(',', @select)." FROM $from GROUP BY custnum"; +} + =head1 BUGS =head1 SEE ALSO diff --git a/FS/FS/h_cust_pkg.pm b/FS/FS/h_cust_pkg.pm index 0c3db107f..423b44250 100644 --- a/FS/FS/h_cust_pkg.pm +++ b/FS/FS/h_cust_pkg.pm @@ -177,9 +177,57 @@ sub churn_fromwhere_sql { return ($from, @where); } +=head1 as_of_sql DATE + +Returns a qsearch hash for the instantaneous state of the cust_pkg table +on DATE. + +Currently accepts no restrictions; use it in a subquery if you want to +limit or sort the output. (Restricting within the query is problematic.) + +=cut + +sub as_of_sql { + my $class = shift; + my $date = shift; + "SELECT DISTINCT ON (pkgnum) * + FROM h_cust_pkg + WHERE history_date < $date + AND history_action IN('insert', 'replace_new') + ORDER BY pkgnum ASC, history_date DESC" +} + +=item status_query DATE + +Returns a statement for determining the status of packages on a particular +past date. + +=cut + +sub status_as_of_sql { + my $class = shift; + my $date = shift; + + my @select = ( + 'h_cust_pkg.*', + FS::cust_pkg->active_sql() . ' AS is_active', + FS::cust_pkg->suspended_sql() . ' AS is_suspended', + FS::cust_pkg->cancelled_sql() . ' AS is_cancelled', + ); + # foo_sql queries reference 'cust_pkg' in field names + foreach(@select) { + s/\bcust_pkg\b/h_cust_pkg/g; + } + + return "SELECT DISTINCT ON(pkgnum) ".join(',', @select). + " FROM h_cust_pkg". + " WHERE history_date < $date AND history_action IN('insert','replace_new')". + " ORDER BY pkgnum ASC, history_date DESC"; +} + =head1 BUGS -churn_fromwhere_sql fails on MySQL. +churn_fromwhere_sql and as_of_sql fail on MySQL. =head1 SEE ALSO diff --git a/FS/FS/pay_batch/CIBC.pm b/FS/FS/pay_batch/CIBC.pm new file mode 100644 index 000000000..2c72bef4e --- /dev/null +++ b/FS/FS/pay_batch/CIBC.pm @@ -0,0 +1,87 @@ +package FS::pay_batch::CIBC; + +use strict; +use vars qw(@ISA %import_info %export_info $name); +use Date::Format 'time2str'; +use FS::Conf; + +my $conf; +my ($origid, $datacenter, $transcode, $shortname, $mybank, $myacct); + +$name = 'CIBC'; + +%import_info = ( + 'filetype' => 'CSV', + 'fields' => [], + 'hook' => sub { die "Can't import CIBC" }, + 'approved' => sub { 1 }, + 'declined' => sub { 0 }, +); + +%export_info = ( + init => sub { + $conf = shift; + ($origid, + $datacenter, + $transcode, + $shortname, + $mybank, + $myacct) = $conf->config("batchconfig-CIBC"); + }, + header => sub { + my $pay_batch = shift; + sprintf( "1%2s%05u%-5s%010u%6s%04u%1s%04u%5u%-12u%2s%-15s%1s%3s%4s \n", #80 + '', + substr(0,5, $origid), + '', + $origid, + time2str('%y%m%d', $pay_batch->download), + $pay_batch->batchnum, + ' ', + '0010', + $mybank, + $myacct, + '', + $shortname, + ' ', + 'CAD', + '', ) . + sprintf( "5%46s%03u%-10s%6s%14s", #80 + '', + $transcode, + ' ', + time2str('%y%m%d', $pay_batch->download), + ' '); + }, + row => sub { + my ($cust_pay_batch, $pay_batch) = @_; + my ($account, $aba) = split('@', $cust_pay_batch->payinfo); + my($bankno, $branch); + if ( $aba =~ /^0(\d{3})(\d{5})$/ ) { # standard format for Canadian bank ID + ($bankno, $branch) = ( $1, $2 ); + } elsif ( $aba =~ /^(\d{5})\.(\d{3})$/ ) { #how we store branches + ($branch, $bankno) = ( $1, $2 ); + } else { + die "invalid branch/routing number '$aba'\n"; + } + sprintf( "6%1s%1s%04u%05u%-12u%5u%10s%-13s%-22s%6s ", #80 + 'D', + '', + $bankno, + $branch, + $account, + '', + $cust_pay_batch->amount * 100, + $cust_pay_batch->paybatchnum, + $cust_pay_batch->payname, + ' ', + ); + }, + footer => sub { + my ($pay_batch, $batchcount, $batchtotal) = @_; + sprintf( "7%03u%06f%010s%20s%012s%28s \n", $transcode, $batchcount,'0','',$batchtotal*100,''). #80 + sprintf( "9%06s%06s%67s", 1, $batchcount,''); #80 + }, +); + +1; diff --git a/FS/FS/pay_batch/eft_canada.pm b/FS/FS/pay_batch/eft_canada.pm index 310c400b1..0c093214a 100644 --- a/FS/FS/pay_batch/eft_canada.pm +++ b/FS/FS/pay_batch/eft_canada.pm @@ -81,9 +81,11 @@ my %holiday = ( # company + empty or first + last my $company = sprintf('%.64s', $cust_pay_batch->cust_main->company); if ( $company ) { + push @fields, 'Business'; push @fields, $company, '' } else { + push @fields, 'Personal'; push @fields, map { sprintf('%.64s', $_) } $cust_pay_batch->first, $cust_pay_batch->last; } diff --git a/httemplate/search/elements/cust_pay_batch_top.html b/httemplate/search/elements/cust_pay_batch_top.html index a773dd009..2dbf62019 100644 --- a/httemplate/search/elements/cust_pay_batch_top.html +++ b/httemplate/search/elements/cust_pay_batch_top.html @@ -149,6 +149,7 @@ tie my %download_formats, 'Tie::IxHash', ( 'RBC' => 'Royal Bank of Canada PDS batch', 'td_eft1464' => '1464 byte file for TD Commercial Banking EFT', 'eft_canada' => 'EFT Canada CSV batch', + 'CIBC' => '80 byte file for Canadian Imperial Bank of Commerce', # insert new batch formats here );