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 my $conf = new FS::Conf;
239 unless ( $self->charged_party ) {
241 if ( $conf->exists('cdr-charged_party-accountcode') && $self->accountcode ){
243 $self->charged_party( $self->accountcode );
247 if ( $self->dst =~ /^(\+?1)?8[02-8]{2}/ ) {
248 $self->charged_party($self->dst);
250 $self->charged_party($self->src);
257 #check the foreign keys even?
258 #do we want to outright *reject* the CDR?
260 $self->ut_numbern('acctid')
262 #add a config option to turn these back on if someone needs 'em
264 # #Usage = 1, S&E = 7, OC&C = 8
265 # || $self->ut_foreign_keyn('cdrtypenum', 'cdr_type', 'cdrtypenum' )
267 # #the big list in appendix 2
268 # || $self->ut_foreign_keyn('calltypenum', 'cdr_calltype', 'calltypenum' )
270 # # Telstra =1, Optus = 2, RSL COM = 3
271 # || $self->ut_foreign_keyn('carrierid', 'cdr_carrier', 'carrierid' )
273 return $error if $error;
278 =item set_status_and_rated_price STATUS [ RATED_PRICE ]
280 Sets the status to the provided string. If there is an error, returns the
281 error, otherwise returns false.
285 sub set_status_and_rated_price {
286 my($self, $status, $rated_price) = @_;
287 $self->freesidestatus($status);
288 $self->rated_price($rated_price);
294 Parses the calldate in SQL string format and returns a UNIX timestamp.
299 str2time(shift->calldate);
304 Parses the startdate in UNIX timestamp format and returns a string in SQL
310 my($sec,$min,$hour,$mday,$mon,$year) = localtime(shift->startdate);
313 "$year-$mon-$mday $hour:$min:$sec";
318 Returns the FS::cdr_carrier object associated with this CDR, or false if no
319 carrierid is defined.
323 my %carrier_cache = ();
327 return '' unless $self->carrierid;
328 $carrier_cache{$self->carrierid} ||=
329 qsearchs('cdr_carrier', { 'carrierid' => $self->carrierid } );
334 Returns the carrier name (see L<FS::cdr_carrier>), or the empty string if
335 no FS::cdr_carrier object is assocated with this CDR.
341 my $cdr_carrier = $self->cdr_carrier;
342 $cdr_carrier ? $cdr_carrier->carriername : '';
347 Returns the FS::cdr_calltype object associated with this CDR, or false if no
348 calltypenum is defined.
352 my %calltype_cache = ();
356 return '' unless $self->calltypenum;
357 $calltype_cache{$self->calltypenum} ||=
358 qsearchs('cdr_calltype', { 'calltypenum' => $self->calltypenum } );
363 Returns the call type name (see L<FS::cdr_calltype>), or the empty string if
364 no FS::cdr_calltype object is assocated with this CDR.
370 my $cdr_calltype = $self->cdr_calltype;
371 $cdr_calltype ? $cdr_calltype->calltypename : '';
374 =item cdr_upstream_rate
376 Returns the upstream rate mapping (see L<FS::cdr_upstream_rate>), or the empty
377 string if no FS::cdr_upstream_rate object is associated with this CDR.
381 sub cdr_upstream_rate {
383 return '' unless $self->upstream_rateid;
384 qsearchs('cdr_upstream_rate', { 'upstream_rateid' => $self->upstream_rateid })
388 =item _convergent_format COLUMN [ COUNTRYCODE ]
390 Returns the number in COLUMN formatted as follows:
392 If the country code does not match COUNTRYCODE (default "61"), it is returned
395 If the country code does match COUNTRYCODE (default "61"), it is removed. In
396 addiiton, "0" is prepended unless the number starts with 13, 18 or 19. (???)
400 sub _convergent_format {
401 my( $self, $field ) = ( shift, shift );
402 my $countrycode = scalar(@_) ? shift : '61'; #+61 = australia
403 #my $number = $self->$field();
404 my $number = $self->get($field);
405 #if ( $number =~ s/^(\+|011)$countrycode// ) {
406 if ( $number =~ s/^\+$countrycode// ) {
408 unless $number =~ /^1[389]/; #???
413 =item downstream_csv [ OPTION => VALUE, ... ]
421 'invoice_header' => "Date,Time,Name,Destination,Duration,Price",
424 'name' => 'Simple with source',
425 'invoice_header' => "Date,Time,Called From,Destination,Duration,Price",
426 #"Date,Time,Name,Called From,Destination,Duration,Price",
430 'invoice_header' => 'Date,Time,Duration,Price,Number,Destination',
432 'source_default' => {
433 'name' => 'Default with source',
434 'invoice_header' => 'Caller,Date,Time,Duration,Number,Destination,Price',
438 my %export_formats = (
440 'carriername', #CARRIER
441 sub { shift->_convergent_format('src') }, #SERVICE_NUMBER
442 sub { shift->_convergent_format('charged_party') }, #CHARGED_NUMBER
443 sub { time2str('%Y-%m-%d', shift->calldate_unix ) }, #DATE
444 sub { time2str('%T', shift->calldate_unix ) }, #TIME
445 'billsec', #'duration', #DURATION
446 sub { shift->_convergent_format('dst') }, #NUMBER_DIALED
447 '', #XXX add (from prefixes in most recent email) #FROM_DESC
448 '', #XXX add (from prefixes in most recent email) #TO_DESC
449 'calltypename', #CLASS_CODE
450 'rated_price', #PRICE
451 sub { shift->rated_price ? 'Y' : 'N' }, #RATED
455 sub { time2str('%D', shift->calldate_unix ) }, #DATE
456 sub { time2str('%r', shift->calldate_unix ) }, #TIME
458 'dst', #NUMBER_DIALED
459 sub { sprintf('%.2fm', shift->billsec / 60 ) }, #DURATION
460 sub { sprintf('%.3f', shift->upstream_price ) }, #PRICE
463 sub { time2str('%D', shift->calldate_unix ) }, #DATE
464 sub { time2str('%r', shift->calldate_unix ) }, #TIME
466 'dst', #NUMBER_DIALED
468 sub { sprintf('%.2fm', shift->billsec / 60 ) }, #DURATION
469 sub { sprintf('%.3f', shift->upstream_price ) }, #PRICE
474 sub { time2str('%D', shift->calldate_unix ) },
475 # #time2str("%Y %b %d - %r", $cdr->calldate_unix ),
478 sub { time2str('%r', shift->calldate_unix ) },
479 # 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
482 sub { my($cdr, %opt) = @_;
483 $opt{minutes}. ( $opt{granularity} ? 'm' : ' call' );
487 sub { my($cdr, %opt) = @_; $opt{money_char}. $opt{charge}; },
490 sub { my($cdr, %opt) = @_; $opt{pretty_dst} || $cdr->dst; },
492 #REGIONNAME ("Destination")
493 sub { my($cdr, %opt) = @_; $opt{dst_regionname}; },
497 $export_formats{'source_default'} = [ 'src',
498 @{ $export_formats{'default'} }[0..2],
499 @{ $export_formats{'default'} }[4..5],
500 @{ $export_formats{'default'} }[3],
504 my( $self, %opt ) = @_;
506 my $format = $opt{'format'}; # 'convergent';
507 return "Unknown format $format" unless exists $export_formats{$format};
509 #my $conf = new FS::Conf;
510 #$opt{'money_char'} ||= $conf->config('money_char') || '$';
511 $opt{'money_char'} ||= FS::Conf->new->config('money_char') || '$';
513 eval "use Text::CSV_XS;";
515 my $csv = new Text::CSV_XS;
519 ref($_) ? &{$_}($self, %opt) : $self->$_();
521 @{ $export_formats{$format} };
523 my $status = $csv->combine(@columns);
524 die "FS::CDR: error combining ". $csv->error_input(). "into downstream CSV"
537 =item invoice_formats
539 Returns an ordered list of key value pairs containing invoice format names
540 as keys (for use with part_pkg::voip_cdr) and "pretty" format names as values.
544 sub invoice_formats {
545 map { ($_ => $export_names{$_}->{'name'}) }
546 grep { $export_names{$_}->{'invoice_header'} }
550 =item invoice_header FORMAT
552 Returns a scalar containing the CSV column header for invoice format FORMAT.
558 $export_names{$format}->{'invoice_header'};
563 Returns an ordered list of key value pairs containing import format names
564 as keys (for use with batch_import) and "pretty" format names as values.
568 #false laziness w/part_pkg & part_export
571 foreach my $INC ( @INC ) {
572 warn "globbing $INC/FS/cdr/*.pm\n" if $DEBUG;
573 foreach my $file ( glob("$INC/FS/cdr/*.pm") ) {
574 warn "attempting to load CDR format info from $file\n" if $DEBUG;
575 $file =~ /\/(\w+)\.pm$/ or do {
576 warn "unrecognized file in $INC/FS/cdr/: $file\n";
580 my $info = eval "use FS::cdr::$mod; ".
581 "\\%FS::cdr::$mod\::info;";
583 die "error using FS::cdr::$mod (skipping): $@\n" if $@;
586 unless ( keys %$info ) {
587 warn "no %info hash found in FS::cdr::$mod, skipping\n";
590 warn "got CDR format info from FS::cdr::$mod: $info\n" if $DEBUG;
591 if ( exists($info->{'disabled'}) && $info->{'disabled'} ) {
592 warn "skipping disabled CDR format FS::cdr::$mod" if $DEBUG;
595 $cdr_info{$mod} = $info;
599 tie my %import_formats, 'Tie::IxHash',
600 map { $_ => $cdr_info{$_}->{'name'} }
601 sort { $cdr_info{$a}->{'weight'} <=> $cdr_info{$b}->{'weight'} }
602 grep { exists($cdr_info{$_}->{'import_fields'}) }
609 sub _cdr_min_parser_maker {
611 my @fields = ref($field) ? @$field : ($field);
612 @fields = qw( billsec duration ) unless scalar(@fields) && $fields[0];
614 my( $cdr, $min ) = @_;
615 my $sec = eval { _cdr_min_parse($min) };
616 die "error parsing seconds for @fields from $min minutes: $@\n" if $@;
617 $cdr->$_($sec) foreach @fields;
623 sprintf('%.0f', $min * 60 );
626 sub _cdr_date_parser_maker {
629 my( $cdr, $date ) = @_;
630 #$cdr->$field( _cdr_date_parse($date) );
631 eval { $cdr->$field( _cdr_date_parse($date) ); };
632 die "error parsing date for $field from $date: $@\n" if $@;
636 sub _cdr_date_parse {
639 return '' unless length($date); #that's okay, it becomes NULL
641 my($year, $mon, $day, $hour, $min, $sec);
643 #$date =~ /^\s*(\d{4})[\-\/]\(\d{1,2})[\-\/](\d{1,2})\s+(\d{1,2}):(\d{1,2}):(\d{1,2})\s*$/
644 #taqua #2007-10-31 08:57:24.113000000
646 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|$)/ ) {
647 ($year, $mon, $day, $hour, $min, $sec) = ( $1, $2, $3, $4, $5, $6 );
648 } 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|$)/ ) {
649 ($mon, $day, $year, $hour, $min, $sec) = ( $1, $2, $3, $4, $5, $6 );
651 die "unparsable date: $date"; #maybe we shouldn't die...
654 return '' if $year == 1900 && $mon == 1 && $day == 1
655 && $hour == 0 && $min == 0 && $sec == 0;
657 timelocal($sec, $min, $hour, $day, $mon-1, $year);
660 =item batch_import HASHREF
662 Imports CDR records. Available options are:
677 my $fh = $param->{filehandle};
678 my $format = $param->{format};
679 my $cdrbatch = $param->{cdrbatch};
681 return "Unknown format $format"
682 unless exists( $cdr_info{$format} )
683 && exists( $cdr_info{$format}->{'import_fields'} );
685 my $info = $cdr_info{$format};
687 my $type = exists($info->{'type'}) ? lc($info->{'type'}) : 'csv';
690 if ( $type eq 'csv' ) {
691 eval "use Text::CSV_XS;";
694 foreach ( grep exists($info->{$_}), qw( sep_char ) ) {
695 $attr{$_} = $info->{$_};
697 $parser = new Text::CSV_XS \%attr;
698 } elsif ( $type eq 'fixedlength' ) {
699 eval "use Parse::FixedLength;";
701 $parser = new Parse::FixedLength $info->{'fixedlength_format'};
703 die "Unknown CDR format type $type for format $format\n";
709 local $SIG{HUP} = 'IGNORE';
710 local $SIG{INT} = 'IGNORE';
711 local $SIG{QUIT} = 'IGNORE';
712 local $SIG{TERM} = 'IGNORE';
713 local $SIG{TSTP} = 'IGNORE';
714 local $SIG{PIPE} = 'IGNORE';
716 my $oldAutoCommit = $FS::UID::AutoCommit;
717 local $FS::UID::AutoCommit = 0;
720 my $header_lines = exists($info->{'header'}) ? $info->{'header'} : 0;
723 while ( defined($line=<$fh>) ) {
725 next if $header_lines-- > 0; #&& $line =~ /^[\w, "]+$/
728 if ( $type eq 'csv' ) {
730 $parser->parse($line) or do {
731 $dbh->rollback if $oldAutoCommit;
732 return "can't parse: ". $parser->error_input();
735 @columns = $parser->fields();
737 } elsif ( $type eq 'fixedlength' ) {
739 @columns = $parser->parse($line);
742 die "Unknown CDR format type $type for format $format\n";
745 #warn join('-',@columns);
747 if ( $format eq 'simple' ) { #should be a callback or opt in FS::cdr::simple
748 @columns = map { s/^ +//; $_; } @columns;
755 my $field_or_sub = $_;
756 if ( ref($field_or_sub) ) {
757 push @later, $field_or_sub, shift(@columns);
760 ( $field_or_sub => shift @columns );
764 @{ $info->{'import_fields'} }
767 $cdr{cdrbatch} = $cdrbatch;
769 my $cdr = new FS::cdr ( \%cdr );
771 while ( scalar(@later) ) {
772 my $sub = shift @later;
773 my $data = shift @later;
774 &{$sub}($cdr, $data); # $cdr->&{$sub}($data);
777 if ( $format eq 'taqua' ) { #should be a callback or opt in FS::cdr::taqua
778 if ( $cdr->enddate && $cdr->startdate ) { #a bit more?
779 $cdr->duration( $cdr->enddate - $cdr->startdate );
781 if ( $cdr->enddate && $cdr->answerdate ) { #a bit more?
782 $cdr->billsec( $cdr->enddate - $cdr->answerdate );
786 my $error = $cdr->insert;
788 $dbh->rollback if $oldAutoCommit;
798 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
800 #might want to disable this if we skip records for any reason...
801 return "Empty file!" unless $imported || $param->{empty_ok};
813 L<FS::Record>, schema.html from the base documentation.