1 package FS::part_export::a2billing;
4 use vars qw(@ISA @EXPORT_OK $DEBUG %info %options);
7 use FS::Record qw( qsearch qsearchs str2time_sql );
11 use Locale::Country qw(country_code2code);
12 use Date::Format qw(time2str);
16 @ISA = qw(FS::part_export);
20 tie %options, 'Tie::IxHash',
21 'datasrc' => { label=>'DBI data source ' },
22 'username' => { label=>'Database username' },
23 'password' => { label=>'Database password' },
24 'didgroup' => { label=>'DID group ID', default=>1 },
25 'credit' => { label=>'Default credit limit' },
26 'billtype' => {label=>'Billing type',
28 options => ['Dial Out Rate', 'Free']
30 'debug' => { label=>'Enable debugging', type=>'checkbox' }
34 <p>Real-time export to the backend database of an <a
35 href="http://www.asterisk2billing.org">Asterisk2Billing</a> billing server.
36 This is both a svc_acct and a svc_phone export, and needs to be attached
37 to both a svc_acct and svc_phone definition within the same package.</p>
39 <li>When you set up this export, it will create 'svcnum' fields in the
40 cc_card and cc_did tables in the A2Billing database to store the
41 service numbers of svc_acct and svc_phone records. The database username
42 must have ALTER TABLE privileges.</li>
43 <li><i>DBI data source</i> should look like<br>
44 <b>dbi:mysql:host=</b><i>hostname</i><b>;database=</b><i>dbname</i>
49 'svc' => ['svc_acct', 'svc_phone'],
50 'desc' => 'Export to Asterisk2Billing database',
51 'options' => \%options,
59 $self->{dbh} ||= FS::DBI->connect(
60 $self->option('datasrc'),
61 $self->option('username'),
62 $self->option('password')
63 ) or die $FS::DBI::errstr;
65 $self->{dbh}->trace(1, '%%%FREESIDE_LOG%%%/a2b_exportlog.'.$self->exportnum)
71 # hook insert/replace, because we need to make some changes to the
72 # database when the export is created
75 my $error = $self->SUPER::insert(@_);
76 return $error if $error;
77 if ( $self->option('datasrc') ) {
79 foreach (qw(cc_card cc_did)) {
80 $self->dbh->do("ALTER TABLE $_ ADD COLUMN svcnum int")
81 or $error = $self->dbh->errstr;
82 $error = '' if $error =~ /Duplicate column name/; # harmless
83 return "Error preparing a2billing database: $error\n" if $error;
91 my $old = shift || $new->replace_old;
92 my $old_datasrc = $old->option('datasrc');
93 my $error = $new->SUPER::replace($old, @_);
94 return $error if $error;
96 if ($new->option('datasrc') and $new->option('datasrc') ne $old_datasrc) {
97 my $dbh = $new->a2b_connect;
99 foreach (qw(cc_card cc_did)) {
100 $new->dbh->do("ALTER TABLE $_ ADD COLUMN svcnum int")
101 or $error = $new->dbh->errstr;
102 $error = '' if $error =~ /Duplicate column name/; # harmless
103 return "Error preparing a2billing database: $error\n" if $error;
112 my $cust_pkg = $svc->cust_svc->cust_pkg;
113 my $cust_main = $cust_pkg->cust_main;
114 my $location = $cust_pkg->cust_location;
115 my $part_pkg = $cust_pkg->part_pkg;
118 $DEBUG ||= $self->option('debug');
120 # 3-letter UN country code
121 my $country3 = uc(country_code2code($location->country, 'alpha2' => 'alpha3'));
123 my $dbh = $self->a2b_connect;
125 if ( $svc->isa('FS::svc_acct') ) {
126 # export to cc_card (customer identity) and cc_sip_buddies (SIP extension)
128 my $username = $svc->username;
131 svcnum => $svc->svcnum,
132 username => $username,
133 useralias => $username,
134 uipass => $svc->_password,
135 creditlimit => $cust_main->credit_limit || $self->option('credit') || 0,
136 tariff => $part_pkg->option('a2billing_tariff'),
138 lastname => $cust_main->last, # $svc->finger?
139 firstname => $cust_main->first,
140 address => $location->address1 .
141 ($location->address2 ? ', '.$location->address2 : ''),
142 city => $location->city,
143 state => $location->state,
144 country => $country3,
145 zipcode => $location->zip,
146 simultaccess => $part_pkg->option('a2billing_simultaccess'),
147 typepaid => $part_pkg->option('a2billing_type'),
148 email_notification => $cust_main->invoicing_list_emailonly_scalar,
149 notify_email => ($cust_main->invoicing_list_emailonly_scalar ? 1 : 0),
150 credit_notification => $cust_main->credit_limit || $self->option('credit') || 0,
152 company_name => $cust_main->company,
155 warn "creating A2B cc_card record for $username\n" if $DEBUG;
156 $error = $self->a2b_insert_or_replace('cc_card', 'svcnum', \%cc_card);
157 return "Error creating A2Billing customer identity: $error" if $error;
159 my $fullcontact = '';
160 if ( $svc->ip_addr ) {
161 $fullcontact = "sip:$username\@".$svc->ip_addr;
164 my $cc_card_id = $self->a2b_find('cc_card', 'svcnum', $svc->svcnum);
165 # these are the fields we know about; some of them might need to be
166 # export options eventually, and there are a lot more fields in the table
168 id_cc_card => $cc_card_id,
170 accountcode => $username,
171 regexten => $username,
172 amaflags => 'billing',
173 context => 'a2billing',
176 secret => $svc->_password,
177 username => $username,
178 allow => 'ulaw,alaw,gsm,g729',
179 ipaddr => ($svc->slipip || ''),
180 fullcontact => $fullcontact,
182 warn "creating A2B cc_sip_buddies record for $username\n" if $DEBUG;
183 $error = $self->a2b_insert_or_replace('cc_sip_buddies', 'id_cc_card',
185 return "Error creating A2Billing SIP extension: $error" if $error;
187 # then, if there are any DIDs on the package, set them up
188 foreach ( $self->_linked_svcs($svc, 'svc_phone') ) {
189 warn "triggering export of svc_phone #".$_->svcnum."\n" if $DEBUG;
190 $error = $self->export_insert($_->svc_x);
191 return $error if $error;
195 } elsif ( $svc->isa('FS::svc_phone') ) {
196 # find the linked svc_acct
198 foreach ($self->_linked_svcs($svc, 'svc_acct')) {
199 $svc_acct = $_->svc_x;
203 # it hasn't been created yet, so just exit.
204 # this service will be exported later.
205 warn "no linked svc_acct; deferring phone number export\n" if $DEBUG;
208 # find the card and sip_buddies records
209 my $cc_card_id = $self->a2b_find('cc_card', 'svcnum', $svc_acct->svcnum);
210 my $cc_sip_buddies_id = $self->a2b_find('cc_sip_buddies', 'id_cc_card', $cc_card_id);
211 if (!$cc_card_id or !$cc_sip_buddies_id) {
212 warn "When exporting svc_phone #".$svc->svcnum.", svc_acct #".$svc_acct->svcnum." was not found in A2Billing.\n";
213 if ( $FS::svc_Common::noexport_hack ) {
214 # recursion protection
215 return "During export of linked DID#".$svc->phonenum.", svc_acct #".$svc_acct->svcnum." was not found in A2Billing.";
217 return $svc_acct->export_insert; # which will call back to here when
222 my $cc_country_id = $self->a2b_find('cc_country', 'countrycode', $country3);
224 svcnum => $svc->svcnum,
225 id_cc_didgroup => $self->option('didgroup'),
226 id_cc_country => $cc_country_id,
227 iduser => $cc_card_id,
228 did => $svc->countrycode. $svc->phonenum,
229 billingtype => ($self->option('billtype') eq 'Dial Out Rate' ? 2 : 3),
231 aleg_carrier_cost_min_offp => $part_pkg->option('a2billing_carrier_cost_min'),
232 aleg_carrier_initblock_offp => $part_pkg->option('a2billing_carrier_initblock_offp'),
233 aleg_carrier_increment_offp => $part_pkg->option('a2billing_carrier_increment_offp'),
234 aleg_retail_cost_min_offp => $part_pkg->option('a2billing_retail_cost_min_offp'),
235 aleg_retail_initblock_offp => $part_pkg->option('a2billing_retail_initblock_offp'),
236 aleg_retail_increment_offp => $part_pkg->option('a2billing_retail_increment_offp'),
239 # use 'did' as the key here so that if the DID already exists, we
240 # link it to this customer.
241 $error = $self->a2b_insert_or_replace('cc_did', 'did', \%cc_did);
242 return "Error creating A2Billing DID record: $error" if $error;
244 my $cc_did_id = $self->a2b_find('cc_did', 'svcnum', $svc->svcnum);
246 my $destination = 'SIP/user-'. $svc_acct->username. '@'. $svc->sip_server. "!". $svc->countrycode. $svc->phonenum;
247 my %cc_did_destination = (
248 destination => $destination,
250 id_cc_card => $cc_card_id,
251 id_cc_did => $cc_did_id,
256 # and if there's already a destination, change it to point to
257 # this customer's SIP extension
258 $error = $self->a2b_insert_or_replace('cc_did_destination', 'id_cc_did',
259 \%cc_did_destination);
260 return "Error linking A2Billing DID record to customer: $error" if $error;
263 id_cc_card => $cc_card_id,
264 id_did => $cc_did_id,
266 month_payed => 1, # it's the default in the A2Billing code, I think
268 # and change the in-use record, too
269 my $id_use = $self->a2b_find('cc_did_use',
270 id_did => $cc_did_id,
274 $error = $self->a2b_insert_or_replace('cc_did_use', 'id',
276 releasedate => time2str('%Y-%m-%d %H:%M:%S', time),
280 return "Error closing existing A2Billing DID assignment record: $error"
283 # and do an update instead of an insert
284 $cc_did_use{id} = $id_use;
287 $error = $self->a2b_insert_or_replace('cc_did_use', 'id', \%cc_did_use);
288 return "Error creating A2Billing DID use record: $error" if $error;
290 } # if $svc->isa(...)
299 $DEBUG ||= $self->option('debug');
301 if ( $svc->isa('FS::svc_acct') ) {
303 # first remove the DID links
304 foreach ($self->_linked_svcs($svc, 'svc_phone')) {
305 warn "triggering export of svc_phone #".$_->svcnum."\n" if $DEBUG;
306 $error = $self->export_delete($_->svc_x);
307 return $error if $error;
310 # a2billing never deletes a card, just sets status = 0.
311 # though we also need to remove the svcnum, since that svcnum is no
313 my $cc_card_id = $self->a2b_find('cc_card', 'svcnum', $svc->svcnum);
315 warn "tried to remove svc_acct #".$svc->svcnum." from A2Billing, but couldn't find it.\n";
316 # which is not really a problem.
319 warn "deactivating A2B cc_card record #$cc_card_id\n" if $DEBUG;
320 $error = $self->a2b_insert_or_replace('cc_card', 'id', {
326 return $error if $error;
328 } elsif ( $svc->isa('FS::svc_phone') ) {
330 my $cc_did_id = $self->a2b_find('cc_did', 'svcnum', $svc->svcnum);
332 warn "deactivating DID ".$svc->phonenum."\n" if $DEBUG;
333 $error = $self->a2b_insert_or_replace('cc_did', 'id',
340 return $error if $error;
342 warn "tried to remove svc_phone #".$svc->svcnum." from A2Billing, but couldn't find it.\n";
346 my $cc_did_destination_id = $self->a2b_find('cc_did_destination',
347 'id_cc_did', $cc_did_id,
350 if ( $cc_did_destination_id ) {
351 warn "unlinking DID ".$svc->phonenum." from customer\n" if $DEBUG;
352 $error = $self->a2b_delete('cc_did_destination', $cc_did_destination_id);
353 return $error if $error;
355 warn "no cc_did_destination found for cc_did #$cc_did_id\n";
358 my $cc_did_use_id = $self->a2b_find('cc_did_use',
359 'id_did', $cc_did_id,
362 if ( $cc_did_use_id ) {
363 warn "closing DID assignment\n" if $DEBUG;
364 $error = $self->a2b_insert_or_replace('cc_did_use', 'id',
365 { id => $cc_did_use_id,
366 releasedate => time2str('%Y-%m-%d %H:%M:%S', time),
370 return "Error closing existing A2Billing DID assignment record: $error"
373 warn "no cc_did_use found for cc_did #$cc_did_id\n";
380 sub _export_replace {
383 my $old = shift || $self->replace_old;
386 $DEBUG ||= $self->option('debug');
388 if ( $new->isa('FS::svc_acct') ) {
390 my $cc_card_id = $self->a2b_find('cc_card', 'svcnum', $new->svcnum);
391 if ( $cc_card_id and $new->username ne $old->username ) {
392 # If the username is changing and any DIDs are provisioned, we need to
393 # change their destinations. To do this, we unlink them. This will
394 # close their did_use records, delete their cc_did_destinations, and
395 # set their cc_dids to inactive.
396 foreach ($self->_linked_svcs($new, 'svc_phone')) {
397 warn "triggering export of svc_phone #".$_->svcnum."\n" if $DEBUG;
398 $error = $self->export_delete($_->svc_x);
399 return $error if $error;
403 # export_insert will replace the record with the same svcnum, if there
404 # is one, and then re-export all existing DIDs (which is convenient since
405 # we just unlinked them).
406 $error = $self->export_insert($new);
407 return $error if $error;
409 } elsif ( $new->isa('FS::svc_phone') ) {
411 # if the phone number has changed, need to create a new DID.
412 if ( $new->phonenum ne $old->phonenum || $new->countrycode ne $old->countrycode ) {
413 # deactivate/unlink/close the old DID
414 # and create/link the new one
415 $error = $self->export_delete($old)
416 || $self->export_insert($new);
417 return $error if $error;
419 # otherwise we don't care
425 sub _export_suspend {
430 $DEBUG ||= $self->option('debug');
432 if ( $svc->isa('FS::svc_acct') ) {
433 $error = $self->a2b_insert_or_replace('cc_card', 'svcnum',
434 { svcnum => $svc->svcnum,
435 status => 6, # "SUSPENDED FOR UNDERPAYMENT"
436 activated => 0, # still used in some places, grrr
439 } elsif ( $svc->isa('FS::svc_phone') ) {
441 $error = $self->a2b_insert_or_replace('cc_did', 'svcnum',
442 { svcnum => $svc->svcnum,
450 sub _export_unsuspend {
455 $DEBUG ||= $self->option('debug');
457 if ( $svc->isa('FS::svc_acct') ) {
458 $error = $self->a2b_insert_or_replace('cc_card', 'svcnum',
459 { svcnum => $svc->svcnum,
460 status => 1, #"ACTIVE"
464 } elsif ( $svc->isa('FS::svc_phone') ) {
465 $error = $self->a2b_insert_or_replace('cc_did', 'svcnum',
466 { svcnum => $svc->svcnum,
474 =item a2b_insert_or_replace TABLE KEY HASHREF
476 Create a record in TABLE with the values in HASHREF. If there's already one
477 that matches on the KEY field, update the existing record instead of creating
478 a new one. Pass an empty KEY to just insert the record without checking.
482 sub a2b_insert_or_replace {
489 my $id = $self->a2b_find($table, $key, $hashref->{$key});
491 my $sql = "UPDATE $table SET " .
492 join(', ', map { "$_ = ?" } keys(%$hashref)) .
494 $self->dbh->do($sql, {}, values(%$hashref), $id)
495 or return $self->dbh->errstr;
499 # no key, or no existing record
500 my $sql = "INSERT INTO $table (". join(', ', keys(%$hashref)) . ")" .
501 " VALUES (" . join(', ', map { '?' } keys(%$hashref)) . ")";
502 $self->dbh->do($sql, {}, values(%$hashref))
503 or return $self->dbh->errstr;
507 =item a2b_delete TABLE ID
509 Remove the record with id ID from TABLE.
515 my ($table, $id) = @_;
516 my $sql = "DELETE FROM $table WHERE id = ?";
517 $self->dbh->do($sql, {}, $id)
518 or return $self->dbh->errstr;
522 =item a2b_find TABLE KEY VALUE [ KEY VALUE ... ]
524 Search TABLE for a row where KEY equals VALUE, and return its "id" field.
530 my ($table, %params) = @_;
531 my $sql = "SELECT id FROM $table WHERE " .
532 join(' AND ', map { "$_ = ?" } keys(%params));
533 my ($id) = $self->dbh->selectrow_array($sql, {}, values(%params));
534 die $self->dbh->errstr if $self->dbh->errstr;
538 # find services on the same package that are exportable with this export
539 # and are of a specified svcdb
541 # just to avoid repeating myself
543 my ($self, $svc, $svcdb) = @_;
544 # index the svcparts that belong to the a2billing export
545 my $export_svcparts = $self->{export_svcparts} ||=
546 { map { $_->svcpart => $_->part_svc->svcdb }
550 my $pkgnum = $svc->cust_svc->pkgnum;
551 my @svcs = qsearch('cust_svc', { pkgnum => $pkgnum });
552 grep { $export_svcparts->{$_->svcpart} eq $svcdb } @svcs;