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)
143 Creates a new CDR. To add the CDR to the database, see L<"insert">.
145 Note that this stores the hash reference, not a distinct copy of the hash it
146 points to. You can ask the object for a copy with the I<hash> method.
150 # the new method can be inherited from FS::Record, if a table method is defined
156 Adds this record to the database. If there is an error, returns the error,
157 otherwise returns false.
161 # the insert method can be inherited from FS::Record
165 Delete this record from the database.
169 # the delete method can be inherited from FS::Record
171 =item replace OLD_RECORD
173 Replaces the OLD_RECORD with this one in the database. If there is an error,
174 returns the error, otherwise returns false.
178 # the replace method can be inherited from FS::Record
182 Checks all fields to make sure this is a valid CDR. If there is
183 an error, returns the error, otherwise returns false. Called by the insert
186 Note: Unlike most types of records, we don't want to "reject" a CDR and we want
187 to process them as quickly as possible, so we allow the database to check most
195 # we don't want to "reject" a CDR like other sorts of input...
197 # $self->ut_numbern('acctid')
198 ## || $self->ut_('calldate')
199 # || $self->ut_text('clid')
200 # || $self->ut_text('src')
201 # || $self->ut_text('dst')
202 # || $self->ut_text('dcontext')
203 # || $self->ut_text('channel')
204 # || $self->ut_text('dstchannel')
205 # || $self->ut_text('lastapp')
206 # || $self->ut_text('lastdata')
207 # || $self->ut_numbern('startdate')
208 # || $self->ut_numbern('answerdate')
209 # || $self->ut_numbern('enddate')
210 # || $self->ut_number('duration')
211 # || $self->ut_number('billsec')
212 # || $self->ut_text('disposition')
213 # || $self->ut_number('amaflags')
214 # || $self->ut_text('accountcode')
215 # || $self->ut_text('uniqueid')
216 # || $self->ut_text('userfield')
217 # || $self->ut_numbern('cdrtypenum')
218 # || $self->ut_textn('charged_party')
219 ## || $self->ut_n('upstream_currency')
220 ## || $self->ut_n('upstream_price')
221 # || $self->ut_numbern('upstream_rateplanid')
222 ## || $self->ut_n('distance')
223 # || $self->ut_numbern('islocal')
224 # || $self->ut_numbern('calltypenum')
225 # || $self->ut_textn('description')
226 # || $self->ut_numbern('quantity')
227 # || $self->ut_numbern('carrierid')
228 # || $self->ut_numbern('upstream_rateid')
229 # || $self->ut_numbern('svcnum')
230 # || $self->ut_textn('freesidestatus')
232 # return $error if $error;
234 $self->calldate( $self->startdate_sql )
235 if !$self->calldate && $self->startdate;
237 #was just for $format eq 'taqua' but can't see the harm... add something to
238 #disable if it becomes a problem
239 if ( $self->duration eq '' && $self->enddate && $self->startdate ) {
240 $self->duration( $self->enddate - $self->startdate );
242 if ( $self->billsec eq '' && $self->enddate && $self->answerdate ) {
243 $self->billsec( $self->enddate - $self->answerdate );
246 my $conf = new FS::Conf;
248 unless ( $self->charged_party ) {
250 if ( $conf->exists('cdr-charged_party-accountcode') && $self->accountcode ){
252 $self->charged_party( $self->accountcode );
256 if ( $self->dst =~ /^(\+?1)?8[02-8]{2}/ ) {
257 $self->charged_party($self->dst);
259 $self->charged_party($self->src);
266 #check the foreign keys even?
267 #do we want to outright *reject* the CDR?
269 $self->ut_numbern('acctid')
271 #add a config option to turn these back on if someone needs 'em
273 # #Usage = 1, S&E = 7, OC&C = 8
274 # || $self->ut_foreign_keyn('cdrtypenum', 'cdr_type', 'cdrtypenum' )
276 # #the big list in appendix 2
277 # || $self->ut_foreign_keyn('calltypenum', 'cdr_calltype', 'calltypenum' )
279 # # Telstra =1, Optus = 2, RSL COM = 3
280 # || $self->ut_foreign_keyn('carrierid', 'cdr_carrier', 'carrierid' )
282 return $error if $error;
287 =item set_status_and_rated_price STATUS [ RATED_PRICE ]
289 Sets the status to the provided string. If there is an error, returns the
290 error, otherwise returns false.
294 sub set_status_and_rated_price {
295 my($self, $status, $rated_price) = @_;
296 $self->freesidestatus($status);
297 $self->rated_price($rated_price);
303 Parses the calldate in SQL string format and returns a UNIX timestamp.
308 str2time(shift->calldate);
313 Parses the startdate in UNIX timestamp format and returns a string in SQL
319 my($sec,$min,$hour,$mday,$mon,$year) = localtime(shift->startdate);
322 "$year-$mon-$mday $hour:$min:$sec";
327 Returns the FS::cdr_carrier object associated with this CDR, or false if no
328 carrierid is defined.
332 my %carrier_cache = ();
336 return '' unless $self->carrierid;
337 $carrier_cache{$self->carrierid} ||=
338 qsearchs('cdr_carrier', { 'carrierid' => $self->carrierid } );
343 Returns the carrier name (see L<FS::cdr_carrier>), or the empty string if
344 no FS::cdr_carrier object is assocated with this CDR.
350 my $cdr_carrier = $self->cdr_carrier;
351 $cdr_carrier ? $cdr_carrier->carriername : '';
356 Returns the FS::cdr_calltype object associated with this CDR, or false if no
357 calltypenum is defined.
361 my %calltype_cache = ();
365 return '' unless $self->calltypenum;
366 $calltype_cache{$self->calltypenum} ||=
367 qsearchs('cdr_calltype', { 'calltypenum' => $self->calltypenum } );
372 Returns the call type name (see L<FS::cdr_calltype>), or the empty string if
373 no FS::cdr_calltype object is assocated with this CDR.
379 my $cdr_calltype = $self->cdr_calltype;
380 $cdr_calltype ? $cdr_calltype->calltypename : '';
383 =item cdr_upstream_rate
385 Returns the upstream rate mapping (see L<FS::cdr_upstream_rate>), or the empty
386 string if no FS::cdr_upstream_rate object is associated with this CDR.
390 sub cdr_upstream_rate {
392 return '' unless $self->upstream_rateid;
393 qsearchs('cdr_upstream_rate', { 'upstream_rateid' => $self->upstream_rateid })
397 =item _convergent_format COLUMN [ COUNTRYCODE ]
399 Returns the number in COLUMN formatted as follows:
401 If the country code does not match COUNTRYCODE (default "61"), it is returned
404 If the country code does match COUNTRYCODE (default "61"), it is removed. In
405 addiiton, "0" is prepended unless the number starts with 13, 18 or 19. (???)
409 sub _convergent_format {
410 my( $self, $field ) = ( shift, shift );
411 my $countrycode = scalar(@_) ? shift : '61'; #+61 = australia
412 #my $number = $self->$field();
413 my $number = $self->get($field);
414 #if ( $number =~ s/^(\+|011)$countrycode// ) {
415 if ( $number =~ s/^\+$countrycode// ) {
417 unless $number =~ /^1[389]/; #???
422 =item downstream_csv [ OPTION => VALUE, ... ]
430 'invoice_header' => "Date,Time,Name,Destination,Duration,Price",
433 'name' => 'Simple with source',
434 'invoice_header' => "Date,Time,Called From,Destination,Duration,Price",
435 #"Date,Time,Name,Called From,Destination,Duration,Price",
439 'invoice_header' => 'Date,Time,Number,Destination,Duration,Price',
441 'source_default' => {
442 'name' => 'Default with source',
443 'invoice_header' => 'Caller,Date,Time,Number,Destination,Duration,Price',
447 my %export_formats = (
449 'carriername', #CARRIER
450 sub { shift->_convergent_format('src') }, #SERVICE_NUMBER
451 sub { shift->_convergent_format('charged_party') }, #CHARGED_NUMBER
452 sub { time2str('%Y-%m-%d', shift->calldate_unix ) }, #DATE
453 sub { time2str('%T', shift->calldate_unix ) }, #TIME
454 'billsec', #'duration', #DURATION
455 sub { shift->_convergent_format('dst') }, #NUMBER_DIALED
456 '', #XXX add (from prefixes in most recent email) #FROM_DESC
457 '', #XXX add (from prefixes in most recent email) #TO_DESC
458 'calltypename', #CLASS_CODE
459 'rated_price', #PRICE
460 sub { shift->rated_price ? 'Y' : 'N' }, #RATED
464 sub { time2str('%D', shift->calldate_unix ) }, #DATE
465 sub { time2str('%r', shift->calldate_unix ) }, #TIME
467 'dst', #NUMBER_DIALED
468 sub { sprintf('%.2fm', shift->billsec / 60 ) }, #DURATION
469 #sub { sprintf('%.3f', shift->upstream_price ) }, #PRICE
470 sub { my($cdr, %opt) = @_; $opt{money_char}. $opt{charge}; }, #PRICE
473 sub { time2str('%D', shift->calldate_unix ) }, #DATE
474 sub { time2str('%r', shift->calldate_unix ) }, #TIME
476 'dst', #NUMBER_DIALED
478 sub { sprintf('%.2fm', shift->billsec / 60 ) }, #DURATION
479 #sub { sprintf('%.3f', shift->upstream_price ) }, #PRICE
480 sub { my($cdr, %opt) = @_; $opt{money_char}. $opt{charge}; }, #PRICE
485 sub { time2str('%D', shift->calldate_unix ) },
486 # #time2str("%Y %b %d - %r", $cdr->calldate_unix ),
489 sub { time2str('%r', shift->calldate_unix ) },
490 # 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
493 sub { my($cdr, %opt) = @_; $opt{pretty_dst} || $cdr->dst; },
495 #REGIONNAME ("Destination")
496 sub { my($cdr, %opt) = @_; $opt{dst_regionname}; },
499 sub { my($cdr, %opt) = @_;
500 $opt{minutes}. ( $opt{granularity} ? 'm' : ' call' );
504 sub { my($cdr, %opt) = @_; $opt{money_char}. $opt{charge}; },
508 $export_formats{'source_default'} = [ 'src', @{ $export_formats{'default'} }, ];
511 my( $self, %opt ) = @_;
513 my $format = $opt{'format'}; # 'convergent';
514 return "Unknown format $format" unless exists $export_formats{$format};
516 #my $conf = new FS::Conf;
517 #$opt{'money_char'} ||= $conf->config('money_char') || '$';
518 $opt{'money_char'} ||= FS::Conf->new->config('money_char') || '$';
520 eval "use Text::CSV_XS;";
522 my $csv = new Text::CSV_XS;
526 ref($_) ? &{$_}($self, %opt) : $self->$_();
528 @{ $export_formats{$format} };
530 my $status = $csv->combine(@columns);
531 die "FS::CDR: error combining ". $csv->error_input(). "into downstream CSV"
544 =item invoice_formats
546 Returns an ordered list of key value pairs containing invoice format names
547 as keys (for use with part_pkg::voip_cdr) and "pretty" format names as values.
551 sub invoice_formats {
552 map { ($_ => $export_names{$_}->{'name'}) }
553 grep { $export_names{$_}->{'invoice_header'} }
557 =item invoice_header FORMAT
559 Returns a scalar containing the CSV column header for invoice format FORMAT.
565 $export_names{$format}->{'invoice_header'};
570 Returns an ordered list of key value pairs containing import format names
571 as keys (for use with batch_import) and "pretty" format names as values.
575 #false laziness w/part_pkg & part_export
578 foreach my $INC ( @INC ) {
579 warn "globbing $INC/FS/cdr/*.pm\n" if $DEBUG;
580 foreach my $file ( glob("$INC/FS/cdr/*.pm") ) {
581 warn "attempting to load CDR format info from $file\n" if $DEBUG;
582 $file =~ /\/(\w+)\.pm$/ or do {
583 warn "unrecognized file in $INC/FS/cdr/: $file\n";
587 my $info = eval "use FS::cdr::$mod; ".
588 "\\%FS::cdr::$mod\::info;";
590 die "error using FS::cdr::$mod (skipping): $@\n" if $@;
593 unless ( keys %$info ) {
594 warn "no %info hash found in FS::cdr::$mod, skipping\n";
597 warn "got CDR format info from FS::cdr::$mod: $info\n" if $DEBUG;
598 if ( exists($info->{'disabled'}) && $info->{'disabled'} ) {
599 warn "skipping disabled CDR format FS::cdr::$mod" if $DEBUG;
602 $cdr_info{$mod} = $info;
606 tie my %import_formats, 'Tie::IxHash',
607 map { $_ => $cdr_info{$_}->{'name'} }
608 sort { $cdr_info{$a}->{'weight'} <=> $cdr_info{$b}->{'weight'} }
609 grep { exists($cdr_info{$_}->{'import_fields'}) }
616 sub _cdr_min_parser_maker {
618 my @fields = ref($field) ? @$field : ($field);
619 @fields = qw( billsec duration ) unless scalar(@fields) && $fields[0];
621 my( $cdr, $min ) = @_;
622 my $sec = eval { _cdr_min_parse($min) };
623 die "error parsing seconds for @fields from $min minutes: $@\n" if $@;
624 $cdr->$_($sec) foreach @fields;
630 sprintf('%.0f', $min * 60 );
633 sub _cdr_date_parser_maker {
635 my @fields = ref($field) ? @$field : ($field);
637 my( $cdr, $datestring ) = @_;
638 my $unixdate = eval { _cdr_date_parse($datestring) };
639 die "error parsing date for @fields from $datestring: $@\n" if $@;
640 $cdr->$_($unixdate) foreach @fields;
644 sub _cdr_date_parse {
647 return '' unless length($date); #that's okay, it becomes NULL
649 my($year, $mon, $day, $hour, $min, $sec);
651 #$date =~ /^\s*(\d{4})[\-\/]\(\d{1,2})[\-\/](\d{1,2})\s+(\d{1,2}):(\d{1,2}):(\d{1,2})\s*$/
652 #taqua #2007-10-31 08:57:24.113000000
654 if ( $date =~ /^\s*(\d{4})\D(\d{1,2})\D(\d{1,2})\s+(\d{1,2})\D(\d{1,2})\D(\d{1,2})(\D|$)/ ) {
655 ($year, $mon, $day, $hour, $min, $sec) = ( $1, $2, $3, $4, $5, $6 );
656 } 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|$)/ ) {
657 ($mon, $day, $year, $hour, $min, $sec) = ( $1, $2, $3, $4, $5, $6 );
659 die "unparsable date: $date"; #maybe we shouldn't die...
662 return '' if $year == 1900 && $mon == 1 && $day == 1
663 && $hour == 0 && $min == 0 && $sec == 0;
665 timelocal($sec, $min, $hour, $day, $mon-1, $year);
668 =item batch_import HASHREF
670 Imports CDR records. Available options are:
682 Hash reference of preset fields, typically cdrbatch
686 Set true to prevent throwing an error on empty imports
692 my %import_options = (
695 'formats' => { map { $_ => $cdr_info{$_}->{'import_fields'}; }
699 #drop the || 'csv' to allow auto xls for csv types?
700 'format_types' => { map { $_ => ( lc($cdr_info{$_}->{'type'}) || 'csv' ); }
704 'format_headers' => { map { $_ => ( $cdr_info{$_}->{'header'} || 0 ); }
708 'format_sep_chars' => { map { $_ => $cdr_info{$_}->{'sep_char'}; }
712 'format_fixedlength_formats' =>
713 { map { $_ => $cdr_info{$_}->{'fixedlength_format'}; }
718 sub _import_options {
725 my $iopt = _import_options;
726 $opt->{$_} = $iopt->{$_} foreach keys %$iopt;
728 FS::Record::batch_import( $opt );
732 =item process_batch_import
736 sub process_batch_import {
739 my $opt = _import_options;
740 $opt->{'params'} = [ 'format', 'cdrbatch' ];
742 FS::Record::process_batch_import( $job, $opt, @_ );
745 # if ( $format eq 'simple' ) { #should be a callback or opt in FS::cdr::simple
746 # @columns = map { s/^ +//; $_; } @columns;
755 L<FS::Record>, schema.html from the base documentation.