'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',
]
},
'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'
]
},
'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',
}
-##### 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:
@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);
=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
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
--- /dev/null
+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;
# 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;
}
'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
);
projects / freeside.git / commitdiff