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
159 #XXX fill in some (more) nice names
161 'calldate' => 'Call date',
162 'clid' => 'Caller ID',
164 'dst' => 'Destination',
165 'dcontext' => 'Dest. context',
166 'channel' => 'Channel',
167 'dstchannel' => 'Destination channel',
170 'startdate' => 'Start date',
171 'answerdate' => 'Answer date',
172 'enddate' => 'End date',
173 'duration' => 'Duration',
174 'billsec' => 'Billable seconds',
175 'disposition' => 'Disposition',
176 'amaflags' => 'AMA flags',
177 'accountcode' => 'Account code',
179 'userfield' => 'User field',
181 'charged_party' => 'Charged party',
182 #'upstream_currency' => '',
183 'upstream_price' => 'Upstream price',
184 #'upstream_rateplanid' => '',
185 #'ratedetailnum' => '',
186 'rated_price' => 'Rated price',
189 #'calltypenum' => '',
190 #'description' => '',
192 'carrierid' => 'Carrier ID',
193 #'upstream_rateid' => '',
194 'svcnum' => 'Freeside service',
195 'freesidestatus' => 'Freeside status',
196 'freesiderewritestatus' => 'Freeside rewrite status',
197 'cdrbatch' => 'Batch',
206 Adds this record to the database. If there is an error, returns the error,
207 otherwise returns false.
211 # the insert method can be inherited from FS::Record
215 Delete this record from the database.
219 # the delete method can be inherited from FS::Record
221 =item replace OLD_RECORD
223 Replaces the OLD_RECORD with this one in the database. If there is an error,
224 returns the error, otherwise returns false.
228 # the replace method can be inherited from FS::Record
232 Checks all fields to make sure this is a valid CDR. If there is
233 an error, returns the error, otherwise returns false. Called by the insert
236 Note: Unlike most types of records, we don't want to "reject" a CDR and we want
237 to process them as quickly as possible, so we allow the database to check most
245 # we don't want to "reject" a CDR like other sorts of input...
247 # $self->ut_numbern('acctid')
248 ## || $self->ut_('calldate')
249 # || $self->ut_text('clid')
250 # || $self->ut_text('src')
251 # || $self->ut_text('dst')
252 # || $self->ut_text('dcontext')
253 # || $self->ut_text('channel')
254 # || $self->ut_text('dstchannel')
255 # || $self->ut_text('lastapp')
256 # || $self->ut_text('lastdata')
257 # || $self->ut_numbern('startdate')
258 # || $self->ut_numbern('answerdate')
259 # || $self->ut_numbern('enddate')
260 # || $self->ut_number('duration')
261 # || $self->ut_number('billsec')
262 # || $self->ut_text('disposition')
263 # || $self->ut_number('amaflags')
264 # || $self->ut_text('accountcode')
265 # || $self->ut_text('uniqueid')
266 # || $self->ut_text('userfield')
267 # || $self->ut_numbern('cdrtypenum')
268 # || $self->ut_textn('charged_party')
269 ## || $self->ut_n('upstream_currency')
270 ## || $self->ut_n('upstream_price')
271 # || $self->ut_numbern('upstream_rateplanid')
272 ## || $self->ut_n('distance')
273 # || $self->ut_numbern('islocal')
274 # || $self->ut_numbern('calltypenum')
275 # || $self->ut_textn('description')
276 # || $self->ut_numbern('quantity')
277 # || $self->ut_numbern('carrierid')
278 # || $self->ut_numbern('upstream_rateid')
279 # || $self->ut_numbern('svcnum')
280 # || $self->ut_textn('freesidestatus')
281 # || $self->ut_textn('freesiderewritestatus')
283 # return $error if $error;
285 $self->calldate( $self->startdate_sql )
286 if !$self->calldate && $self->startdate;
288 #was just for $format eq 'taqua' but can't see the harm... add something to
289 #disable if it becomes a problem
290 if ( $self->duration eq '' && $self->enddate && $self->startdate ) {
291 $self->duration( $self->enddate - $self->startdate );
293 if ( $self->billsec eq '' && $self->enddate && $self->answerdate ) {
294 $self->billsec( $self->enddate - $self->answerdate );
297 $self->set_charged_party;
299 #check the foreign keys even?
300 #do we want to outright *reject* the CDR?
302 $self->ut_numbern('acctid')
304 #add a config option to turn these back on if someone needs 'em
306 # #Usage = 1, S&E = 7, OC&C = 8
307 # || $self->ut_foreign_keyn('cdrtypenum', 'cdr_type', 'cdrtypenum' )
309 # #the big list in appendix 2
310 # || $self->ut_foreign_keyn('calltypenum', 'cdr_calltype', 'calltypenum' )
312 # # Telstra =1, Optus = 2, RSL COM = 3
313 # || $self->ut_foreign_keyn('carrierid', 'cdr_carrier', 'carrierid' )
315 return $error if $error;
322 Returns true when the cdr represents a toll free number and false otherwise.
328 ( $self->dst =~ /^(\+?1)?8(8|([02-7])\3)/ ) ? 1 : 0;
331 =item set_charged_party
333 If the charged_party field is already set, does nothing. Otherwise:
335 If the cdr-charged_party-accountcode config option is enabled, sets the
336 charged_party to the accountcode.
338 Otherwise sets the charged_party normally: to the src field in most cases,
339 or to the dst field if it is a toll free number.
343 sub set_charged_party {
346 my $conf = new FS::Conf;
348 unless ( $self->charged_party ) {
350 if ( $conf->exists('cdr-charged_party-accountcode') && $self->accountcode ){
352 $self->charged_party( $self->accountcode );
356 if ( $self->is_tollfree ) {
357 $self->charged_party($self->dst);
359 $self->charged_party($self->src);
366 # my $prefix = $conf->config('cdr-charged_party-truncate_prefix');
367 # my $prefix_len = length($prefix);
368 # my $trunc_len = $conf->config('cdr-charged_party-truncate_length');
370 # $self->charged_party( substr($self->charged_party, 0, $trunc_len) )
371 # if $prefix_len && $trunc_len
372 # && substr($self->charged_party, 0, $prefix_len) eq $prefix;
376 =item set_status_and_rated_price STATUS [ RATED_PRICE ]
378 Sets the status to the provided string. If there is an error, returns the
379 error, otherwise returns false.
383 sub set_status_and_rated_price {
384 my($self, $status, $rated_price) = @_;
385 $self->freesidestatus($status);
386 $self->rated_price($rated_price);
392 Parses the calldate in SQL string format and returns a UNIX timestamp.
397 str2time(shift->calldate);
402 Parses the startdate in UNIX timestamp format and returns a string in SQL
408 my($sec,$min,$hour,$mday,$mon,$year) = localtime(shift->startdate);
411 "$year-$mon-$mday $hour:$min:$sec";
416 Returns the FS::cdr_carrier object associated with this CDR, or false if no
417 carrierid is defined.
421 my %carrier_cache = ();
425 return '' unless $self->carrierid;
426 $carrier_cache{$self->carrierid} ||=
427 qsearchs('cdr_carrier', { 'carrierid' => $self->carrierid } );
432 Returns the carrier name (see L<FS::cdr_carrier>), or the empty string if
433 no FS::cdr_carrier object is assocated with this CDR.
439 my $cdr_carrier = $self->cdr_carrier;
440 $cdr_carrier ? $cdr_carrier->carriername : '';
445 Returns the FS::cdr_calltype object associated with this CDR, or false if no
446 calltypenum is defined.
450 my %calltype_cache = ();
454 return '' unless $self->calltypenum;
455 $calltype_cache{$self->calltypenum} ||=
456 qsearchs('cdr_calltype', { 'calltypenum' => $self->calltypenum } );
461 Returns the call type name (see L<FS::cdr_calltype>), or the empty string if
462 no FS::cdr_calltype object is assocated with this CDR.
468 my $cdr_calltype = $self->cdr_calltype;
469 $cdr_calltype ? $cdr_calltype->calltypename : '';
472 =item cdr_upstream_rate
474 Returns the upstream rate mapping (see L<FS::cdr_upstream_rate>), or the empty
475 string if no FS::cdr_upstream_rate object is associated with this CDR.
479 sub cdr_upstream_rate {
481 return '' unless $self->upstream_rateid;
482 qsearchs('cdr_upstream_rate', { 'upstream_rateid' => $self->upstream_rateid })
486 =item _convergent_format COLUMN [ COUNTRYCODE ]
488 Returns the number in COLUMN formatted as follows:
490 If the country code does not match COUNTRYCODE (default "61"), it is returned
493 If the country code does match COUNTRYCODE (default "61"), it is removed. In
494 addiiton, "0" is prepended unless the number starts with 13, 18 or 19. (???)
498 sub _convergent_format {
499 my( $self, $field ) = ( shift, shift );
500 my $countrycode = scalar(@_) ? shift : '61'; #+61 = australia
501 #my $number = $self->$field();
502 my $number = $self->get($field);
503 #if ( $number =~ s/^(\+|011)$countrycode// ) {
504 if ( $number =~ s/^\+$countrycode// ) {
506 unless $number =~ /^1[389]/; #???
511 =item downstream_csv [ OPTION => VALUE, ... ]
519 'invoice_header' => "Date,Time,Name,Destination,Duration,Price",
522 'name' => 'Simple with source',
523 'invoice_header' => "Date,Time,Called From,Destination,Duration,Price",
524 #"Date,Time,Name,Called From,Destination,Duration,Price",
528 'invoice_header' => 'Date,Time,Number,Destination,Duration,Price',
530 'source_default' => {
531 'name' => 'Default with source',
532 'invoice_header' => 'Caller,Date,Time,Number,Destination,Duration,Price',
534 'accountcode_default' => {
535 'name' => 'Default plus accountcode',
536 'invoice_header' => 'Date,Time,Account,Number,Destination,Duration,Price',
540 my $duration_sub = sub {
542 if ( $opt{minutes} ) {
543 $opt{minutes}. ( $opt{granularity} ? 'm' : ' call' );
545 sprintf('%.2fm', $cdr->billsec / 60 );
549 my %export_formats = (
551 'carriername', #CARRIER
552 sub { shift->_convergent_format('src') }, #SERVICE_NUMBER
553 sub { shift->_convergent_format('charged_party') }, #CHARGED_NUMBER
554 sub { time2str('%Y-%m-%d', shift->calldate_unix ) }, #DATE
555 sub { time2str('%T', shift->calldate_unix ) }, #TIME
556 'billsec', #'duration', #DURATION
557 sub { shift->_convergent_format('dst') }, #NUMBER_DIALED
558 '', #XXX add (from prefixes in most recent email) #FROM_DESC
559 '', #XXX add (from prefixes in most recent email) #TO_DESC
560 'calltypename', #CLASS_CODE
561 'rated_price', #PRICE
562 sub { shift->rated_price ? 'Y' : 'N' }, #RATED
566 sub { time2str('%D', shift->calldate_unix ) }, #DATE
567 sub { time2str('%r', shift->calldate_unix ) }, #TIME
569 'dst', #NUMBER_DIALED
570 $duration_sub, #DURATION
571 #sub { sprintf('%.3f', shift->upstream_price ) }, #PRICE
572 sub { my($cdr, %opt) = @_; $opt{money_char}. $opt{charge}; }, #PRICE
575 sub { time2str('%D', shift->calldate_unix ) }, #DATE
576 sub { time2str('%r', shift->calldate_unix ) }, #TIME
579 'dst', #NUMBER_DIALED
580 $duration_sub, #DURATION
581 #sub { sprintf('%.3f', shift->upstream_price ) }, #PRICE
582 sub { my($cdr, %opt) = @_; $opt{money_char}. $opt{charge}; }, #PRICE
587 sub { time2str('%D', shift->calldate_unix ) },
588 # #time2str("%Y %b %d - %r", $cdr->calldate_unix ),
591 sub { time2str('%r', shift->calldate_unix ) },
592 # 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
595 sub { my($cdr, %opt) = @_; $opt{pretty_dst} || $cdr->dst; },
597 #REGIONNAME ("Destination")
598 sub { my($cdr, %opt) = @_; $opt{dst_regionname}; },
604 sub { my($cdr, %opt) = @_; $opt{money_char}. $opt{charge}; },
608 $export_formats{'source_default'} = [ 'src', @{ $export_formats{'default'} }, ];
609 $export_formats{'accountcode_default'} =
610 [ @{ $export_formats{'default'} }[0,1],
612 @{ $export_formats{'default'} }[2..5],
616 my( $self, %opt ) = @_;
618 my $format = $opt{'format'}; # 'convergent';
619 return "Unknown format $format" unless exists $export_formats{$format};
621 #my $conf = new FS::Conf;
622 #$opt{'money_char'} ||= $conf->config('money_char') || '$';
623 $opt{'money_char'} ||= FS::Conf->new->config('money_char') || '$';
625 eval "use Text::CSV_XS;";
627 my $csv = new Text::CSV_XS;
631 ref($_) ? &{$_}($self, %opt) : $self->$_();
633 @{ $export_formats{$format} };
635 my $status = $csv->combine(@columns);
636 die "FS::CDR: error combining ". $csv->error_input(). "into downstream CSV"
649 =item invoice_formats
651 Returns an ordered list of key value pairs containing invoice format names
652 as keys (for use with part_pkg::voip_cdr) and "pretty" format names as values.
656 sub invoice_formats {
657 map { ($_ => $export_names{$_}->{'name'}) }
658 grep { $export_names{$_}->{'invoice_header'} }
662 =item invoice_header FORMAT
664 Returns a scalar containing the CSV column header for invoice format FORMAT.
670 $export_names{$format}->{'invoice_header'};
675 Returns an ordered list of key value pairs containing import format names
676 as keys (for use with batch_import) and "pretty" format names as values.
680 #false laziness w/part_pkg & part_export
683 foreach my $INC ( @INC ) {
684 warn "globbing $INC/FS/cdr/*.pm\n" if $DEBUG;
685 foreach my $file ( glob("$INC/FS/cdr/*.pm") ) {
686 warn "attempting to load CDR format info from $file\n" if $DEBUG;
687 $file =~ /\/(\w+)\.pm$/ or do {
688 warn "unrecognized file in $INC/FS/cdr/: $file\n";
692 my $info = eval "use FS::cdr::$mod; ".
693 "\\%FS::cdr::$mod\::info;";
695 die "error using FS::cdr::$mod (skipping): $@\n" if $@;
698 unless ( keys %$info ) {
699 warn "no %info hash found in FS::cdr::$mod, skipping\n";
702 warn "got CDR format info from FS::cdr::$mod: $info\n" if $DEBUG;
703 if ( exists($info->{'disabled'}) && $info->{'disabled'} ) {
704 warn "skipping disabled CDR format FS::cdr::$mod" if $DEBUG;
707 $cdr_info{$mod} = $info;
711 tie my %import_formats, 'Tie::IxHash',
712 map { $_ => $cdr_info{$_}->{'name'} }
713 sort { $cdr_info{$a}->{'weight'} <=> $cdr_info{$b}->{'weight'} }
714 grep { exists($cdr_info{$_}->{'import_fields'}) }
721 sub _cdr_min_parser_maker {
723 my @fields = ref($field) ? @$field : ($field);
724 @fields = qw( billsec duration ) unless scalar(@fields) && $fields[0];
726 my( $cdr, $min ) = @_;
727 my $sec = eval { _cdr_min_parse($min) };
728 die "error parsing seconds for @fields from $min minutes: $@\n" if $@;
729 $cdr->$_($sec) foreach @fields;
735 sprintf('%.0f', $min * 60 );
738 sub _cdr_date_parser_maker {
741 my @fields = ref($field) ? @$field : ($field);
743 my( $cdr, $datestring ) = @_;
744 my $unixdate = eval { _cdr_date_parse($datestring, %options) };
745 die "error parsing date for @fields from $datestring: $@\n" if $@;
746 $cdr->$_($unixdate) foreach @fields;
750 sub _cdr_date_parse {
754 return '' unless length($date); #that's okay, it becomes NULL
755 return '' if $date eq 'NA'; #sansay
757 if ( $date =~ /^([a-z]{3})\s+([a-z]{3})\s+(\d{1,2})\s+(\d{1,2}):(\d{1,2}):(\d{1,2})\s+(\d{4})$/i && $7 > 1970 ) {
758 my $time = str2time($date);
759 return $time if $time > 100000; #just in case
762 my($year, $mon, $day, $hour, $min, $sec);
764 #$date =~ /^\s*(\d{4})[\-\/]\(\d{1,2})[\-\/](\d{1,2})\s+(\d{1,2}):(\d{1,2}):(\d{1,2})\s*$/
765 #taqua #2007-10-31 08:57:24.113000000
767 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|$)/ ) {
768 ($year, $mon, $day, $hour, $min, $sec) = ( $1, $2, $3, $4, $5, $6 );
769 } 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|$)/ ) {
770 ($mon, $day, $year, $hour, $min, $sec) = ( $1, $2, $3, $4, $5, $6 );
772 die "unparsable date: $date"; #maybe we shouldn't die...
775 return '' if ( $year == 1900 || $year == 1970 ) && $mon == 1 && $day == 1
776 && $hour == 0 && $min == 0 && $sec == 0;
779 timegm($sec, $min, $hour, $day, $mon-1, $year);
781 timelocal($sec, $min, $hour, $day, $mon-1, $year);
785 =item batch_import HASHREF
787 Imports CDR records. Available options are:
799 Hash reference of preset fields, typically cdrbatch
803 Set true to prevent throwing an error on empty imports
809 my %import_options = (
812 'formats' => { map { $_ => $cdr_info{$_}->{'import_fields'}; }
816 #drop the || 'csv' to allow auto xls for csv types?
817 'format_types' => { map { $_ => ( lc($cdr_info{$_}->{'type'}) || 'csv' ); }
821 'format_headers' => { map { $_ => ( $cdr_info{$_}->{'header'} || 0 ); }
825 'format_sep_chars' => { map { $_ => $cdr_info{$_}->{'sep_char'}; }
829 'format_fixedlength_formats' =>
830 { map { $_ => $cdr_info{$_}->{'fixedlength_format'}; }
835 sub _import_options {
842 my $iopt = _import_options;
843 $opt->{$_} = $iopt->{$_} foreach keys %$iopt;
845 FS::Record::batch_import( $opt );
849 =item process_batch_import
853 sub process_batch_import {
856 my $opt = _import_options;
857 $opt->{'params'} = [ 'format', 'cdrbatch' ];
859 FS::Record::process_batch_import( $job, $opt, @_ );
862 # if ( $format eq 'simple' ) { #should be a callback or opt in FS::cdr::simple
863 # @columns = map { s/^ +//; $_; } @columns;
872 L<FS::Record>, schema.html from the base documentation.