diff options
author | Mark Wells <mark@freeside.biz> | 2013-07-31 12:09:30 -0700 |
---|---|---|
committer | Mark Wells <mark@freeside.biz> | 2013-07-31 12:09:30 -0700 |
commit | af12e29224dd69ef1d577c5e9943644feafe5525 (patch) | |
tree | 25783ee5d33597d05b76f480c30f29461bc2dace /FS | |
parent | 60f1de20bfedefd3fdc85c4af2b20c3c7ecb35fb (diff) |
a2billing export, #23281
Diffstat (limited to 'FS')
-rw-r--r-- | FS/FS/part_export/a2billing.pm | 542 |
1 files changed, 542 insertions, 0 deletions
diff --git a/FS/FS/part_export/a2billing.pm b/FS/FS/part_export/a2billing.pm new file mode 100644 index 000000000..52680dc17 --- /dev/null +++ b/FS/FS/part_export/a2billing.pm @@ -0,0 +1,542 @@ +package FS::part_export::a2billing; + +use strict; +use vars qw(@ISA @EXPORT_OK $DEBUG %info %options); +use Exporter; +use Tie::IxHash; +use FS::Record qw( qsearch qsearchs str2time_sql ); +use FS::part_export; +use FS::svc_acct; +use FS::svc_phone; +use Locale::Country qw(country_code2code); +use Date::Format qw(time2str); +use Carp qw( cluck ); + +@ISA = qw(FS::part_export); + +$DEBUG = 0; + +tie %options, 'Tie::IxHash', + 'datasrc' => { label=>'DBI data source ' }, + 'username' => { label=>'Database username' }, + 'password' => { label=>'Database password' }, + 'didgroup' => { label=>'DID group ID', default=>1 }, + 'tariffgroup' => { label=>'Tariff group ID', default=>1 }, + 'credit' => { label=>'Default credit limit' }, + 'billtype' => {label=>'Billing type', + type => 'select', + options => ['monthly', 'weekly'] + }, + 'debug' => { label=>'Enable debugging', type=>'checkbox' } +; + +my $notes = <<'END'; +<p>Real-time export to the backend database of an <a +href="http://www.asterisk2billing.org">Asterisk2Billing</a> billing server. +This is both a svc_acct and a svc_phone export, and needs to be attached +to both a svc_acct and svc_phone definition within the same package.</p> +<ul> +<li>When you set up this export, it will create 'svcnum' fields in the +cc_card and cc_did tables in the A2Billing database to store the +service numbers of svc_acct and svc_phone records. The database username +must have ALTER TABLE privileges.</li> +<li><i>DBI data source</i> should look like<br> +<b>dbi:mysql:host=</b><i>hostname</i><b>;database=</b><i>dbname</i> +</li> +END + +%info = ( + 'svc' => ['svc_acct', 'svc_phone'], + 'desc' => 'Export to Asterisk2Billing database', + 'options' => \%options, + 'nodomain' => 'Y', + 'no_machine' => 1, + 'notes' => $notes +); + +sub dbh { + my $self = shift; + $self->{dbh} ||= DBI->connect( + $self->option('datasrc'), + $self->option('username'), + $self->option('password') + ) or die $DBI::errstr; + + $self->{dbh}->trace(1, '%%%FREESIDE_LOG%%%/a2b_exportlog.'.$self->exportnum) + if $DEBUG; + + $self->{dbh}; +} + +# hook insert/replace, because we need to make some changes to the +# database when the export is created +sub insert { + my $self = shift; + my $error = $self->SUPER::insert(@_); + return $error if $error; + if ( $self->option('datasrc') ) { + my $error; + foreach (qw(cc_card cc_did)) { + $self->dbh->do("ALTER TABLE $_ ADD COLUMN svcnum int") + or $error = $self->dbh->errstr; + $error = '' if $error =~ /Duplicate column name/; # harmless + return "Error preparing a2billing database: $error\n" if $error; + } + } + ''; +} + +sub replace { + my $new = shift; + my $old = shift || $new->replace_old; + my $old_datasrc = $old->option('datasrc'); + my $error = $new->SUPER::replace($old, @_); + return $error if $error; + + if ($new->option('datasrc') and $new->option('datasrc') ne $old_datasrc) { + my $dbh = $new->a2b_connect; + my $error; + foreach (qw(cc_card cc_did)) { + $new->dbh->do("ALTER TABLE $_ ADD COLUMN svcnum int") + or $error = $new->dbh->errstr; + $error = '' if $error =~ /Duplicate column name/; # harmless + return "Error preparing a2billing database: $error\n" if $error; + } + } + ''; +} + +sub export_insert { + my $self = shift; + my $svc = shift; + my $cust_pkg = $svc->cust_svc->cust_pkg; + my $cust_main = $cust_pkg->cust_main; + my $location = $cust_pkg->cust_location; + + my $error; + $DEBUG ||= $self->option('debug'); + + # 3-letter UN country code + my $country3 = uc(country_code2code($location->country, 'alpha2' => 'alpha3')); + + my $dbh = $self->a2b_connect; + + if ( $svc->isa('FS::svc_acct') ) { + # export to cc_card (customer identity) and cc_sip_buddies (SIP extension) + + my $username = $svc->username; + + my %cc_card = ( + svcnum => $svc->svcnum, + username => $username, + useralias => $username, + uipass => $svc->_password, + # XXX these options may move to a part_pkg at some point + credit => $self->option('credit') || 0, + tariff => $self->option('tariffgroup'), + status => 1, + lastname => $cust_main->last, # $svc->finger? + firstname => $cust_main->first, + address => $location->address1 . + ($location->address2 ? ', '.$location->address2 : ''), + city => $location->city, + state => $location->state, + country => $country3, + zipcode => $location->zip, + typepaid => 1, + sip_buddy => 1, + company_name => $cust_main->company, + ); + warn "creating A2B cc_card record for $username\n" if $DEBUG; + $error = $self->a2b_insert_or_replace('cc_card', 'svcnum', \%cc_card); + return "Error creating A2Billing customer identity: $error" if $error; + + my $fullcontact = ''; + if ( $svc->ip_addr ) { + $fullcontact = "sip:$username\@".$svc->ip_addr; + } + + my $cc_card_id = $self->a2b_find('cc_card', 'svcnum', $svc->svcnum); + # these are the fields we know about; some of them might need to be + # export options eventually, and there are a lot more fields in the table + my %cc_sip_buddy = ( + id_cc_card => $cc_card_id, + name => $username, + accountcode => $username, + regexten => $username, + amaflags => 'billing', + context => 'a2billing', + host => 'dynamic', + port => 5060, + secret => $svc->_password, + username => $username, + allow => 'ulaw,alaw,gsm,g729', + ipaddr => ($svc->slipip || ''), + fullcontact => $fullcontact, + ); + warn "creating A2B cc_sip_buddies record for $username\n" if $DEBUG; + $error = $self->a2b_insert_or_replace('cc_sip_buddies', 'id_cc_card', + \%cc_sip_buddy); + return "Error creating A2Billing SIP extension: $error" if $error; + + # then, if there are any DIDs on the package, set them up + foreach ( $self->_linked_svcs($svc, 'svc_phone') ) { + warn "triggering export of svc_phone #".$_->svcnum."\n" if $DEBUG; + $error = $self->export_insert($_->svc_x); + return $error if $error; + } + return ''; + + } elsif ( $svc->isa('FS::svc_phone') ) { + # find the linked svc_acct + my $svc_acct; + foreach ($self->_linked_svcs($svc, 'svc_acct')) { + $svc_acct = $_->svc_x; + last; + } + if ( !$svc_acct ) { + # it hasn't been created yet, so just exit. + # this service will be exported later. + warn "no linked svc_acct; deferring phone number export\n" if $DEBUG; + return ''; + } + # find the card and sip_buddies records + my $cc_card_id = $self->a2b_find('cc_card', 'svcnum', $svc_acct->svcnum); + my $cc_sip_buddies_id = $self->a2b_find('cc_sip_buddies', 'id_cc_card', $cc_card_id); + if (!$cc_card_id or !$cc_sip_buddies_id) { + warn "When exporting svc_phone #".$svc->svcnum.", svc_acct #".$svc_acct->svcnum." was not found in A2Billing.\n"; + if ( $FS::svc_Common::noexport_hack ) { + # recursion protection + return "During export of linked DID#".$svc->phonenum.", svc_acct #".$svc_acct->svcnum." was not found in A2Billing."; + } + return $svc_acct->export_insert; # which will call back to here when + # it's done + } + + # Create the DID. + my $cc_country_id = $self->a2b_find('cc_country', 'countrycode', $country3); + my %cc_did = ( + svcnum => $svc->svcnum, + id_cc_didgroup => $self->option('didgroup'), + id_cc_country => $cc_country_id, + iduser => $cc_card_id, + did => $svc->phonenum, + billingtype => ($self->option('billtype') eq 'weekly' ? 1 : 0), + activated => 1, + ); + + # use 'did' as the key here so that if the DID already exists, we + # link it to this customer. + $error = $self->a2b_insert_or_replace('cc_did', 'did', \%cc_did); + return "Error creating A2Billing DID record: $error" if $error; + + my $cc_did_id = $self->a2b_find('cc_did', 'svcnum', $svc->svcnum); + + my $destination = 'SIP/' . $svc->phonenum . '@' . $svc_acct->username; + my %cc_did_destination = ( + destination => $destination, + priority => 1, + id_cc_card => $cc_card_id, + id_cc_did => $cc_did_id, + ); + + # and if there's already a destination, change it to point to + # this customer's SIP extension + $error = $self->a2b_insert_or_replace('cc_did_destination', 'id_cc_did', + \%cc_did_destination); + return "Error linking A2Billing DID record to customer: $error" if $error; + + my %cc_did_use = ( + id_cc_card => $cc_card_id, + id_did => $cc_did_id, + activated => 1, + month_payed => 1, # it's the default in the A2Billing code, I think + ); + # and change the in-use record, too + my $id_use = $self->a2b_find('cc_did_use', + id_did => $cc_did_id, + activated => 1, + ); + if ( $id_use ) { + $error = $self->a2b_insert_or_replace('cc_did_use', 'id', + { id => $id_use, + releasedate => time2str('%Y-%m-%d %H:%M:%S', time), + activated => 0 + } + ); + return "Error closing existing A2Billing DID assignment record: $error" + if $error; + + # and do an update instead of an insert + $cc_did_use{id} = $id_use; + } + + $error = $self->a2b_insert_or_replace('cc_did_use', 'id', \%cc_did_use); + return "Error creating A2Billing DID use record: $error" if $error; + + } # if $svc->isa(...) + ''; +} + +sub export_delete { + my $self = shift; + my $svc = shift; + + my $error; + $DEBUG ||= $self->option('debug'); + + if ( $svc->isa('FS::svc_acct') ) { + + # first remove the DID links + foreach ($self->_linked_svcs($svc, 'svc_phone')) { + warn "triggering export of svc_phone #".$_->svcnum."\n" if $DEBUG; + $error = $self->export_delete($_->svc_x); + return $error if $error; + } + + # a2billing never deletes a card, just sets status = 0. + # though we also need to remove the svcnum, since that svcnum is no + # longer valid. + my $cc_card_id = $self->a2b_find('cc_card', 'svcnum', $svc->svcnum); + if (!$cc_card_id) { + warn "tried to remove svc_acct #".$svc->svcnum." from A2Billing, but couldn't find it.\n"; + # which is not really a problem. + return ''; + } + warn "deactivating A2B cc_card record #$cc_card_id\n" if $DEBUG; + $error = $self->a2b_insert_or_replace('cc_card', 'id', { + id => $cc_card_id, + status => 0, + activated => 0, + svcnum => 0, + }); + return $error if $error; + + } elsif ( $svc->isa('FS::svc_phone') ) { + + my $cc_did_id = $self->a2b_find('cc_did', 'svcnum', $svc->svcnum); + if ( $cc_did_id ) { + warn "deactivating DID ".$svc->phonenum."\n" if $DEBUG; + $error = $self->a2b_insert_or_replace('cc_did', 'id', + { id => $cc_did_id, + activated => 0, + iduser => 0, + svcnum => 0, + } + ); + return $error if $error; + } else { + warn "tried to remove svc_phone #".$svc->svcnum." from A2Billing, but couldn't find it.\n"; + return ''; + } + + my $cc_did_destination_id = $self->a2b_find('cc_did_destination', + 'id_cc_did', $cc_did_id, + 'activated', 1 + ); + if ( $cc_did_destination_id ) { + warn "unlinking DID ".$svc->phonenum." from customer\n" if $DEBUG; + $error = $self->a2b_delete('cc_did_destination', $cc_did_destination_id); + return $error if $error; + } else { + warn "no cc_did_destination found for cc_did #$cc_did_id\n"; + } + + my $cc_did_use_id = $self->a2b_find('cc_did_use', + 'id_did', $cc_did_id, + 'activated', 1 + ); + if ( $cc_did_use_id ) { + warn "closing DID assignment\n" if $DEBUG; + $error = $self->a2b_insert_or_replace('cc_did_use', 'id', + { id => $cc_did_use_id, + releasedate => time2str('%Y-%m-%d %H:%M:%S', time), + activated => 0 + } + ); + return "Error closing existing A2Billing DID assignment record: $error" + if $error; + } else { + warn "no cc_did_use found for cc_did #$cc_did_id\n"; + } + + } + ''; +} + +sub export_replace { + my $self = shift; + my $new = shift; + my $old = shift || $self->replace_old; + + my $error; + $DEBUG ||= $self->option('debug'); + + if ( $new->isa('FS::svc_acct') ) { + + my $cc_card_id = $self->a2b_find('cc_card', 'svcnum', $new->svcnum); + if ( $cc_card_id and $new->username ne $old->username ) { + # If the username is changing and any DIDs are provisioned, we need to + # change their destinations. To do this, we unlink them. This will + # close their did_use records, delete their cc_did_destinations, and + # set their cc_dids to inactive. + foreach ($self->_linked_svcs($new, 'svc_phone')) { + warn "triggering export of svc_phone #".$_->svcnum."\n" if $DEBUG; + $error = $self->export_delete($_->svc_x); + return $error if $error; + } + } + + # export_insert will replace the record with the same svcnum, if there + # is one, and then re-export all existing DIDs (which is convenient since + # we just unlinked them). + $error = $self->export_insert($new); + return $error if $error; + + } elsif ( $new->isa('FS::svc_phone') ) { + + # if the phone number has changed, need to create a new DID. + if ( $new->phonenum ne $old->phonenum ) { + # deactivate/unlink/close the old DID + # and create/link the new one + $error = $self->export_delete($old) + || $self->export_insert($new); + return $error if $error; + } + # otherwise we don't care + } + + ''; +} + +sub export_suspend { + my $self = shift; + my $svc = shift; + + my $error; + $DEBUG ||= $self->option('debug'); + + if ( $svc->isa('FS::svc_acct') ) { + $error = $self->a2b_insert_or_replace('cc_card', 'svcnum', + { svcnum => $svc->svcnum, + status => 6, # "SUSPENDED FOR UNDERPAYMENT" + activated => 0, # still used in some places, grrr + } + ); + } elsif ( $svc->isa('FS::svc_phone') ) { + # deactivate the DID + $error = $self->a2b_insert_or_replace('cc_did', 'svcnum', + { svcnum => $svc->svcnum, + activated => 0, + } + ); + } + $error || ''; +} + +sub export_unsuspend { + my $self = shift; + my $svc = shift; + + my $error; + $DEBUG ||= $self->option('debug'); + + if ( $svc->isa('FS::svc_acct') ) { + $error = $self->a2b_insert_or_replace('cc_card', 'svcnum', + { svcnum => $svc->svcnum, + status => 0, #"ACTIVE" + activated => 1, + } + ); + } elsif ( $svc->isa('FS::svc_phone') ) { + $error = $self->a2b_insert_or_replace('cc_did', 'svcnum', + { svcnum => $svc->svcnum, + activated => 1, + } + ); + } + $error || ''; +} + +=item a2b_insert_or_replace TABLE KEY HASHREF + +Create a record in TABLE with the values in HASHREF. If there's already one +that matches on the KEY field, update the existing record instead of creating +a new one. Pass an empty KEY to just insert the record without checking. + +=cut + +sub a2b_insert_or_replace { + my $self = shift; + my $table = shift; + my $key = shift; + my $hashref = shift; + + if ( $key ) { + my $id = $self->a2b_find($table, $key, $hashref->{$key}); + if ( $id ) { + my $sql = "UPDATE $table SET " . + join(', ', map { "$_ = ?" } keys(%$hashref)) . + " WHERE id = ?"; + $self->dbh->do($sql, {}, values(%$hashref), $id) + or return $self->dbh->errstr; + return ''; + } + } + # no key, or no existing record + my $sql = "INSERT INTO $table (". join(', ', keys(%$hashref)) . ")" . + " VALUES (" . join(', ', map { '?' } keys(%$hashref)) . ")"; + $self->dbh->do($sql, {}, values(%$hashref)) + or return $self->dbh->errstr; + return ''; +} + +=item a2b_delete TABLE ID + +Remove the record with id ID from TABLE. + +=cut + +sub a2b_delete { + my $self = shift; + my ($table, $id) = @_; + my $sql = "DELETE FROM $table WHERE id = ?"; + $self->dbh->do($sql, {}, $id) + or return $self->dbh->errstr; + return ''; +} + +=item a2b_find TABLE KEY VALUE [ KEY VALUE ... ] + +Search TABLE for a row where KEY equals VALUE, and return its "id" field. + +=cut + +sub a2b_find { + my $self = shift; + my ($table, %params) = @_; + my $sql = "SELECT id FROM $table WHERE " . + join(' AND ', map { "$_ = ?" } keys(%params)); + my ($id) = $self->dbh->selectrow_array($sql, {}, values(%params)); + die $self->dbh->errstr if $self->dbh->errstr; + $id || ''; +} + +# find services on the same package that are exportable with this export +# and are of a specified svcdb +# +# just to avoid repeating myself +sub _linked_svcs { + my ($self, $svc, $svcdb) = @_; + # index the svcparts that belong to the a2billing export + my $export_svcparts = $self->{export_svcparts} ||= + { map { $_->svcpart => $_->part_svc->svcdb } + $self->export_svc + }; + + my $pkgnum = $svc->cust_svc->pkgnum; + my @svcs = qsearch('cust_svc', { pkgnum => $pkgnum }); + grep { $export_svcparts->{$_->svcpart} eq $svcdb } @svcs; +} + +1; |