4 use vars qw( @ISA @EXPORT_OK $DEBUG );
10 use FS::UID qw( dbh );
12 use FS::Record qw( qsearch qsearchs );
16 use FS::cdr_upstream_rate;
18 @ISA = qw(FS::Record);
19 @EXPORT_OK = qw( _cdr_date_parser_maker _cdr_min_parser_maker );
25 FS::cdr - Object methods for cdr records
31 $record = new FS::cdr \%hash;
32 $record = new FS::cdr { 'column' => 'value' };
34 $error = $record->insert;
36 $error = $new_record->replace($old_record);
38 $error = $record->delete;
40 $error = $record->check;
44 An FS::cdr object represents an Call Data Record, typically from a telephony
45 system or provider of some sort. FS::cdr inherits from FS::Record. The
46 following fields are currently supported:
50 =item acctid - primary key
52 =item calldate - Call timestamp (SQL timestamp)
54 =item clid - Caller*ID with text
56 =item src - Caller*ID number / Source number
58 =item dst - Destination extension
60 =item dcontext - Destination context
62 =item channel - Channel used
64 =item dstchannel - Destination channel if appropriate
66 =item lastapp - Last application if appropriate
68 =item lastdata - Last application data
70 =item startdate - Start of call (UNIX-style integer timestamp)
72 =item answerdate - Answer time of call (UNIX-style integer timestamp)
74 =item enddate - End time of call (UNIX-style integer timestamp)
76 =item duration - Total time in system, in seconds
78 =item billsec - Total time call is up, in seconds
80 =item disposition - What happened to the call: ANSWERED, NO ANSWER, BUSY
82 =item amaflags - What flags to use: BILL, IGNORE etc, specified on a per channel basis like accountcode.
86 #ignore the "omit" and "documentation" AMAs??
87 #AMA = Automated Message Accounting.
88 #default: Sets the system default.
89 #omit: Do not record calls.
90 #billing: Mark the entry for billing
91 #documentation: Mark the entry for documentation.
93 =item accountcode - CDR account number to use: account
95 =item uniqueid - Unique channel identifier (Unitel/RSLCOM Event ID)
97 =item userfield - CDR user-defined field
99 =item cdr_type - CDR type - see L<FS::cdr_type> (Usage = 1, S&E = 7, OC&C = 8)
101 =item charged_party - Service number to be billed
103 =item upstream_currency - Wholesale currency from upstream
105 =item upstream_price - Wholesale price from upstream
107 =item upstream_rateplanid - Upstream rate plan ID
109 =item rated_price - Rated (or re-rated) price
111 =item distance - km (need units field?)
113 =item islocal - Local - 1, Non Local = 0
115 =item calltypenum - Type of call - see L<FS::cdr_calltype>
117 =item description - Description (cdr_type 7&8 only) (used for cust_bill_pkg.itemdesc)
119 =item quantity - Number of items (cdr_type 7&8 only)
121 =item carrierid - Upstream Carrier ID (see L<FS::cdr_carrier>)
125 #Telstra =1, Optus = 2, RSL COM = 3
127 =item upstream_rateid - Upstream Rate ID
129 =item svcnum - Link to customer service (see L<FS::cust_svc>)
131 =item freesidestatus - NULL, done (or something)
133 =item freesiderewritestatus - NULL, done (or something)
145 Creates a new CDR. To add the CDR to the database, see L<"insert">.
147 Note that this stores the hash reference, not a distinct copy of the hash it
148 points to. You can ask the object for a copy with the I<hash> method.
152 # the new method can be inherited from FS::Record, if a table method is defined
158 Adds this record to the database. If there is an error, returns the error,
159 otherwise returns false.
163 # the insert method can be inherited from FS::Record
167 Delete this record from the database.
171 # the delete method can be inherited from FS::Record
173 =item replace OLD_RECORD
175 Replaces the OLD_RECORD with this one in the database. If there is an error,
176 returns the error, otherwise returns false.
180 # the replace method can be inherited from FS::Record
184 Checks all fields to make sure this is a valid CDR. If there is
185 an error, returns the error, otherwise returns false. Called by the insert
188 Note: Unlike most types of records, we don't want to "reject" a CDR and we want
189 to process them as quickly as possible, so we allow the database to check most
197 # we don't want to "reject" a CDR like other sorts of input...
199 # $self->ut_numbern('acctid')
200 ## || $self->ut_('calldate')
201 # || $self->ut_text('clid')
202 # || $self->ut_text('src')
203 # || $self->ut_text('dst')
204 # || $self->ut_text('dcontext')
205 # || $self->ut_text('channel')
206 # || $self->ut_text('dstchannel')
207 # || $self->ut_text('lastapp')
208 # || $self->ut_text('lastdata')
209 # || $self->ut_numbern('startdate')
210 # || $self->ut_numbern('answerdate')
211 # || $self->ut_numbern('enddate')
212 # || $self->ut_number('duration')
213 # || $self->ut_number('billsec')
214 # || $self->ut_text('disposition')
215 # || $self->ut_number('amaflags')
216 # || $self->ut_text('accountcode')
217 # || $self->ut_text('uniqueid')
218 # || $self->ut_text('userfield')
219 # || $self->ut_numbern('cdrtypenum')
220 # || $self->ut_textn('charged_party')
221 ## || $self->ut_n('upstream_currency')
222 ## || $self->ut_n('upstream_price')
223 # || $self->ut_numbern('upstream_rateplanid')
224 ## || $self->ut_n('distance')
225 # || $self->ut_numbern('islocal')
226 # || $self->ut_numbern('calltypenum')
227 # || $self->ut_textn('description')
228 # || $self->ut_numbern('quantity')
229 # || $self->ut_numbern('carrierid')
230 # || $self->ut_numbern('upstream_rateid')
231 # || $self->ut_numbern('svcnum')
232 # || $self->ut_textn('freesidestatus')
233 # || $self->ut_textn('freesiderewritestatus')
235 # return $error if $error;
237 $self->calldate( $self->startdate_sql )
238 if !$self->calldate && $self->startdate;
240 #was just for $format eq 'taqua' but can't see the harm... add something to
241 #disable if it becomes a problem
242 if ( $self->duration eq '' && $self->enddate && $self->startdate ) {
243 $self->duration( $self->enddate - $self->startdate );
245 if ( $self->billsec eq '' && $self->enddate && $self->answerdate ) {
246 $self->billsec( $self->enddate - $self->answerdate );
249 $self->set_charged_party;
251 #check the foreign keys even?
252 #do we want to outright *reject* the CDR?
254 $self->ut_numbern('acctid')
256 #add a config option to turn these back on if someone needs 'em
258 # #Usage = 1, S&E = 7, OC&C = 8
259 # || $self->ut_foreign_keyn('cdrtypenum', 'cdr_type', 'cdrtypenum' )
261 # #the big list in appendix 2
262 # || $self->ut_foreign_keyn('calltypenum', 'cdr_calltype', 'calltypenum' )
264 # # Telstra =1, Optus = 2, RSL COM = 3
265 # || $self->ut_foreign_keyn('carrierid', 'cdr_carrier', 'carrierid' )
267 return $error if $error;
274 Returns true when the cdr represents a toll free number and false otherwise.
280 ( $self->dst =~ /^(\+?1)?8(8|([02-7])\3)/ ) ? 1 : 0;
283 =item set_charged_party
285 If the charged_party field is already set, does nothing. Otherwise:
287 If the cdr-charged_party-accountcode config option is enabled, sets the
288 charged_party to the accountcode.
290 Otherwise sets the charged_party normally: to the src field in most cases,
291 or to the dst field if it is a toll free number.
295 sub set_charged_party {
298 my $conf = new FS::Conf;
300 unless ( $self->charged_party ) {
302 if ( $conf->exists('cdr-charged_party-accountcode') && $self->accountcode ){
304 $self->charged_party( $self->accountcode );
308 if ( $self->is_tollfree ) {
309 $self->charged_party($self->dst);
311 $self->charged_party($self->src);
318 # my $prefix = $conf->config('cdr-charged_party-truncate_prefix');
319 # my $prefix_len = length($prefix);
320 # my $trunc_len = $conf->config('cdr-charged_party-truncate_length');
322 # $self->charged_party( substr($self->charged_party, 0, $trunc_len) )
323 # if $prefix_len && $trunc_len
324 # && substr($self->charged_party, 0, $prefix_len) eq $prefix;
328 =item set_status_and_rated_price STATUS [ RATED_PRICE ]
330 Sets the status to the provided string. If there is an error, returns the
331 error, otherwise returns false.
335 sub set_status_and_rated_price {
336 my($self, $status, $rated_price) = @_;
337 $self->freesidestatus($status);
338 $self->rated_price($rated_price);
344 Parses the calldate in SQL string format and returns a UNIX timestamp.
349 str2time(shift->calldate);
354 Parses the startdate in UNIX timestamp format and returns a string in SQL
360 my($sec,$min,$hour,$mday,$mon,$year) = localtime(shift->startdate);
363 "$year-$mon-$mday $hour:$min:$sec";
368 Returns the FS::cdr_carrier object associated with this CDR, or false if no
369 carrierid is defined.
373 my %carrier_cache = ();
377 return '' unless $self->carrierid;
378 $carrier_cache{$self->carrierid} ||=
379 qsearchs('cdr_carrier', { 'carrierid' => $self->carrierid } );
384 Returns the carrier name (see L<FS::cdr_carrier>), or the empty string if
385 no FS::cdr_carrier object is assocated with this CDR.
391 my $cdr_carrier = $self->cdr_carrier;
392 $cdr_carrier ? $cdr_carrier->carriername : '';
397 Returns the FS::cdr_calltype object associated with this CDR, or false if no
398 calltypenum is defined.
402 my %calltype_cache = ();
406 return '' unless $self->calltypenum;
407 $calltype_cache{$self->calltypenum} ||=
408 qsearchs('cdr_calltype', { 'calltypenum' => $self->calltypenum } );
413 Returns the call type name (see L<FS::cdr_calltype>), or the empty string if
414 no FS::cdr_calltype object is assocated with this CDR.
420 my $cdr_calltype = $self->cdr_calltype;
421 $cdr_calltype ? $cdr_calltype->calltypename : '';
424 =item cdr_upstream_rate
426 Returns the upstream rate mapping (see L<FS::cdr_upstream_rate>), or the empty
427 string if no FS::cdr_upstream_rate object is associated with this CDR.
431 sub cdr_upstream_rate {
433 return '' unless $self->upstream_rateid;
434 qsearchs('cdr_upstream_rate', { 'upstream_rateid' => $self->upstream_rateid })
438 =item _convergent_format COLUMN [ COUNTRYCODE ]
440 Returns the number in COLUMN formatted as follows:
442 If the country code does not match COUNTRYCODE (default "61"), it is returned
445 If the country code does match COUNTRYCODE (default "61"), it is removed. In
446 addiiton, "0" is prepended unless the number starts with 13, 18 or 19. (???)
450 sub _convergent_format {
451 my( $self, $field ) = ( shift, shift );
452 my $countrycode = scalar(@_) ? shift : '61'; #+61 = australia
453 #my $number = $self->$field();
454 my $number = $self->get($field);
455 #if ( $number =~ s/^(\+|011)$countrycode// ) {
456 if ( $number =~ s/^\+$countrycode// ) {
458 unless $number =~ /^1[389]/; #???
463 =item downstream_csv [ OPTION => VALUE, ... ]
471 'invoice_header' => "Date,Time,Name,Destination,Duration,Price",
474 'name' => 'Simple with source',
475 'invoice_header' => "Date,Time,Called From,Destination,Duration,Price",
476 #"Date,Time,Name,Called From,Destination,Duration,Price",
480 'invoice_header' => 'Date,Time,Number,Destination,Duration,Price',
482 'source_default' => {
483 'name' => 'Default with source',
484 'invoice_header' => 'Caller,Date,Time,Number,Destination,Duration,Price',
486 'accountcode_default' => {
487 'name' => 'Default plus accountcode',
488 'invoice_header' => 'Date,Time,Account,Number,Destination,Duration,Price',
492 my $duration_sub = sub {
494 if ( $opt{minutes} ) {
495 $opt{minutes}. ( $opt{granularity} ? 'm' : ' call' );
497 sprintf('%.2fm', $cdr->billsec / 60 );
501 my %export_formats = (
503 'carriername', #CARRIER
504 sub { shift->_convergent_format('src') }, #SERVICE_NUMBER
505 sub { shift->_convergent_format('charged_party') }, #CHARGED_NUMBER
506 sub { time2str('%Y-%m-%d', shift->calldate_unix ) }, #DATE
507 sub { time2str('%T', shift->calldate_unix ) }, #TIME
508 'billsec', #'duration', #DURATION
509 sub { shift->_convergent_format('dst') }, #NUMBER_DIALED
510 '', #XXX add (from prefixes in most recent email) #FROM_DESC
511 '', #XXX add (from prefixes in most recent email) #TO_DESC
512 'calltypename', #CLASS_CODE
513 'rated_price', #PRICE
514 sub { shift->rated_price ? 'Y' : 'N' }, #RATED
518 sub { time2str('%D', shift->calldate_unix ) }, #DATE
519 sub { time2str('%r', shift->calldate_unix ) }, #TIME
521 'dst', #NUMBER_DIALED
522 $duration_sub, #DURATION
523 #sub { sprintf('%.3f', shift->upstream_price ) }, #PRICE
524 sub { my($cdr, %opt) = @_; $opt{money_char}. $opt{charge}; }, #PRICE
527 sub { time2str('%D', shift->calldate_unix ) }, #DATE
528 sub { time2str('%r', shift->calldate_unix ) }, #TIME
530 'dst', #NUMBER_DIALED
532 $duration_sub, #DURATION
533 #sub { sprintf('%.3f', shift->upstream_price ) }, #PRICE
534 sub { my($cdr, %opt) = @_; $opt{money_char}. $opt{charge}; }, #PRICE
539 sub { time2str('%D', shift->calldate_unix ) },
540 # #time2str("%Y %b %d - %r", $cdr->calldate_unix ),
543 sub { time2str('%r', shift->calldate_unix ) },
544 # time2str("%c", $cdr->calldate_unix), #XXX this should probably be a config option dropdown so they can select US vs- rest of world dates or whatnot
547 sub { my($cdr, %opt) = @_; $opt{pretty_dst} || $cdr->dst; },
549 #REGIONNAME ("Destination")
550 sub { my($cdr, %opt) = @_; $opt{dst_regionname}; },
556 sub { my($cdr, %opt) = @_; $opt{money_char}. $opt{charge}; },
560 $export_formats{'source_default'} = [ 'src', @{ $export_formats{'default'} }, ];
561 $export_formats{'accountcode_default'} =
562 [ @{ $export_formats{'default'} }[0,1],
564 @{ $export_formats{'default'} }[2..5],
568 my( $self, %opt ) = @_;
570 my $format = $opt{'format'}; # 'convergent';
571 return "Unknown format $format" unless exists $export_formats{$format};
573 #my $conf = new FS::Conf;
574 #$opt{'money_char'} ||= $conf->config('money_char') || '$';
575 $opt{'money_char'} ||= FS::Conf->new->config('money_char') || '$';
577 eval "use Text::CSV_XS;";
579 my $csv = new Text::CSV_XS;
583 ref($_) ? &{$_}($self, %opt) : $self->$_();
585 @{ $export_formats{$format} };
587 my $status = $csv->combine(@columns);
588 die "FS::CDR: error combining ". $csv->error_input(). "into downstream CSV"
601 =item invoice_formats
603 Returns an ordered list of key value pairs containing invoice format names
604 as keys (for use with part_pkg::voip_cdr) and "pretty" format names as values.
608 sub invoice_formats {
609 map { ($_ => $export_names{$_}->{'name'}) }
610 grep { $export_names{$_}->{'invoice_header'} }
614 =item invoice_header FORMAT
616 Returns a scalar containing the CSV column header for invoice format FORMAT.
622 $export_names{$format}->{'invoice_header'};
627 Returns an ordered list of key value pairs containing import format names
628 as keys (for use with batch_import) and "pretty" format names as values.
632 #false laziness w/part_pkg & part_export
635 foreach my $INC ( @INC ) {
636 warn "globbing $INC/FS/cdr/*.pm\n" if $DEBUG;
637 foreach my $file ( glob("$INC/FS/cdr/*.pm") ) {
638 warn "attempting to load CDR format info from $file\n" if $DEBUG;
639 $file =~ /\/(\w+)\.pm$/ or do {
640 warn "unrecognized file in $INC/FS/cdr/: $file\n";
644 my $info = eval "use FS::cdr::$mod; ".
645 "\\%FS::cdr::$mod\::info;";
647 die "error using FS::cdr::$mod (skipping): $@\n" if $@;
650 unless ( keys %$info ) {
651 warn "no %info hash found in FS::cdr::$mod, skipping\n";
654 warn "got CDR format info from FS::cdr::$mod: $info\n" if $DEBUG;
655 if ( exists($info->{'disabled'}) && $info->{'disabled'} ) {
656 warn "skipping disabled CDR format FS::cdr::$mod" if $DEBUG;
659 $cdr_info{$mod} = $info;
663 tie my %import_formats, 'Tie::IxHash',
664 map { $_ => $cdr_info{$_}->{'name'} }
665 sort { $cdr_info{$a}->{'weight'} <=> $cdr_info{$b}->{'weight'} }
666 grep { exists($cdr_info{$_}->{'import_fields'}) }
673 sub _cdr_min_parser_maker {
675 my @fields = ref($field) ? @$field : ($field);
676 @fields = qw( billsec duration ) unless scalar(@fields) && $fields[0];
678 my( $cdr, $min ) = @_;
679 my $sec = eval { _cdr_min_parse($min) };
680 die "error parsing seconds for @fields from $min minutes: $@\n" if $@;
681 $cdr->$_($sec) foreach @fields;
687 sprintf('%.0f', $min * 60 );
690 sub _cdr_date_parser_maker {
693 my @fields = ref($field) ? @$field : ($field);
695 my( $cdr, $datestring ) = @_;
696 my $unixdate = eval { _cdr_date_parse($datestring, %options) };
697 die "error parsing date for @fields from $datestring: $@\n" if $@;
698 $cdr->$_($unixdate) foreach @fields;
702 sub _cdr_date_parse {
706 return '' unless length($date); #that's okay, it becomes NULL
708 my($year, $mon, $day, $hour, $min, $sec);
710 #$date =~ /^\s*(\d{4})[\-\/]\(\d{1,2})[\-\/](\d{1,2})\s+(\d{1,2}):(\d{1,2}):(\d{1,2})\s*$/
711 #taqua #2007-10-31 08:57:24.113000000
713 if ( $date =~ /^\s*(\d{4})\D(\d{1,2})\D(\d{1,2})\D+(\d{1,2})\D(\d{1,2})\D(\d{1,2})(\D|$)/ ) {
714 ($year, $mon, $day, $hour, $min, $sec) = ( $1, $2, $3, $4, $5, $6 );
715 } elsif ( $date =~ /^\s*(\d{1,2})\D(\d{1,2})\D(\d{4})\s+(\d{1,2})\D(\d{1,2})\D(\d{1,2})(\D|$)/ ) {
716 ($mon, $day, $year, $hour, $min, $sec) = ( $1, $2, $3, $4, $5, $6 );
718 die "unparsable date: $date"; #maybe we shouldn't die...
721 return '' if $year == 1900 && $mon == 1 && $day == 1
722 && $hour == 0 && $min == 0 && $sec == 0;
725 timegm($sec, $min, $hour, $day, $mon-1, $year);
727 timelocal($sec, $min, $hour, $day, $mon-1, $year);
731 =item batch_import HASHREF
733 Imports CDR records. Available options are:
745 Hash reference of preset fields, typically cdrbatch
749 Set true to prevent throwing an error on empty imports
755 my %import_options = (
758 'formats' => { map { $_ => $cdr_info{$_}->{'import_fields'}; }
762 #drop the || 'csv' to allow auto xls for csv types?
763 'format_types' => { map { $_ => ( lc($cdr_info{$_}->{'type'}) || 'csv' ); }
767 'format_headers' => { map { $_ => ( $cdr_info{$_}->{'header'} || 0 ); }
771 'format_sep_chars' => { map { $_ => $cdr_info{$_}->{'sep_char'}; }
775 'format_fixedlength_formats' =>
776 { map { $_ => $cdr_info{$_}->{'fixedlength_format'}; }
781 sub _import_options {
788 my $iopt = _import_options;
789 $opt->{$_} = $iopt->{$_} foreach keys %$iopt;
791 FS::Record::batch_import( $opt );
795 =item process_batch_import
799 sub process_batch_import {
802 my $opt = _import_options;
803 $opt->{'params'} = [ 'format', 'cdrbatch' ];
805 FS::Record::process_batch_import( $job, $opt, @_ );
808 # if ( $format eq 'simple' ) { #should be a callback or opt in FS::cdr::simple
809 # @columns = map { s/^ +//; $_; } @columns;
818 L<FS::Record>, schema.html from the base documentation.