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 my $charged_party = $self->accountcode;
353 $charged_party =~ s/^0+//
354 if $conf->exists('cdr-charged_party-accountcode-trim_leading_0s');
355 $self->charged_party( $charged_party );
359 if ( $self->is_tollfree ) {
360 $self->charged_party($self->dst);
362 $self->charged_party($self->src);
369 # my $prefix = $conf->config('cdr-charged_party-truncate_prefix');
370 # my $prefix_len = length($prefix);
371 # my $trunc_len = $conf->config('cdr-charged_party-truncate_length');
373 # $self->charged_party( substr($self->charged_party, 0, $trunc_len) )
374 # if $prefix_len && $trunc_len
375 # && substr($self->charged_party, 0, $prefix_len) eq $prefix;
379 =item set_status_and_rated_price STATUS [ RATED_PRICE ]
381 Sets the status to the provided string. If there is an error, returns the
382 error, otherwise returns false.
386 sub set_status_and_rated_price {
387 my($self, $status, $rated_price) = @_;
388 $self->freesidestatus($status);
389 $self->rated_price($rated_price);
395 Parses the calldate in SQL string format and returns a UNIX timestamp.
400 str2time(shift->calldate);
405 Parses the startdate in UNIX timestamp format and returns a string in SQL
411 my($sec,$min,$hour,$mday,$mon,$year) = localtime(shift->startdate);
414 "$year-$mon-$mday $hour:$min:$sec";
419 Returns the FS::cdr_carrier object associated with this CDR, or false if no
420 carrierid is defined.
424 my %carrier_cache = ();
428 return '' unless $self->carrierid;
429 $carrier_cache{$self->carrierid} ||=
430 qsearchs('cdr_carrier', { 'carrierid' => $self->carrierid } );
435 Returns the carrier name (see L<FS::cdr_carrier>), or the empty string if
436 no FS::cdr_carrier object is assocated with this CDR.
442 my $cdr_carrier = $self->cdr_carrier;
443 $cdr_carrier ? $cdr_carrier->carriername : '';
448 Returns the FS::cdr_calltype object associated with this CDR, or false if no
449 calltypenum is defined.
453 my %calltype_cache = ();
457 return '' unless $self->calltypenum;
458 $calltype_cache{$self->calltypenum} ||=
459 qsearchs('cdr_calltype', { 'calltypenum' => $self->calltypenum } );
464 Returns the call type name (see L<FS::cdr_calltype>), or the empty string if
465 no FS::cdr_calltype object is assocated with this CDR.
471 my $cdr_calltype = $self->cdr_calltype;
472 $cdr_calltype ? $cdr_calltype->calltypename : '';
475 =item cdr_upstream_rate
477 Returns the upstream rate mapping (see L<FS::cdr_upstream_rate>), or the empty
478 string if no FS::cdr_upstream_rate object is associated with this CDR.
482 sub cdr_upstream_rate {
484 return '' unless $self->upstream_rateid;
485 qsearchs('cdr_upstream_rate', { 'upstream_rateid' => $self->upstream_rateid })
489 =item _convergent_format COLUMN [ COUNTRYCODE ]
491 Returns the number in COLUMN formatted as follows:
493 If the country code does not match COUNTRYCODE (default "61"), it is returned
496 If the country code does match COUNTRYCODE (default "61"), it is removed. In
497 addiiton, "0" is prepended unless the number starts with 13, 18 or 19. (???)
501 sub _convergent_format {
502 my( $self, $field ) = ( shift, shift );
503 my $countrycode = scalar(@_) ? shift : '61'; #+61 = australia
504 #my $number = $self->$field();
505 my $number = $self->get($field);
506 #if ( $number =~ s/^(\+|011)$countrycode// ) {
507 if ( $number =~ s/^\+$countrycode// ) {
509 unless $number =~ /^1[389]/; #???
514 =item downstream_csv [ OPTION => VALUE, ... ]
522 'invoice_header' => "Date,Time,Name,Destination,Duration,Price",
525 'name' => 'Simple with source',
526 'invoice_header' => "Date,Time,Called From,Destination,Duration,Price",
527 #"Date,Time,Name,Called From,Destination,Duration,Price",
531 'invoice_header' => 'Date,Time,Number,Destination,Duration,Price',
533 'source_default' => {
534 'name' => 'Default with source',
535 'invoice_header' => 'Caller,Date,Time,Number,Destination,Duration,Price',
537 'accountcode_default' => {
538 'name' => 'Default plus accountcode',
539 'invoice_header' => 'Date,Time,Account,Number,Destination,Duration,Price',
543 my $duration_sub = sub {
545 if ( $opt{minutes} ) {
546 $opt{minutes}. ( $opt{granularity} ? 'm' : ' call' );
548 sprintf('%.2fm', $cdr->billsec / 60 );
552 my %export_formats = (
554 'carriername', #CARRIER
555 sub { shift->_convergent_format('src') }, #SERVICE_NUMBER
556 sub { shift->_convergent_format('charged_party') }, #CHARGED_NUMBER
557 sub { time2str('%Y-%m-%d', shift->calldate_unix ) }, #DATE
558 sub { time2str('%T', shift->calldate_unix ) }, #TIME
559 'billsec', #'duration', #DURATION
560 sub { shift->_convergent_format('dst') }, #NUMBER_DIALED
561 '', #XXX add (from prefixes in most recent email) #FROM_DESC
562 '', #XXX add (from prefixes in most recent email) #TO_DESC
563 'calltypename', #CLASS_CODE
564 'rated_price', #PRICE
565 sub { shift->rated_price ? 'Y' : 'N' }, #RATED
569 sub { time2str('%D', shift->calldate_unix ) }, #DATE
570 sub { time2str('%r', shift->calldate_unix ) }, #TIME
572 'dst', #NUMBER_DIALED
573 $duration_sub, #DURATION
574 #sub { sprintf('%.3f', shift->upstream_price ) }, #PRICE
575 sub { my($cdr, %opt) = @_; $opt{money_char}. $opt{charge}; }, #PRICE
578 sub { time2str('%D', shift->calldate_unix ) }, #DATE
579 sub { time2str('%r', shift->calldate_unix ) }, #TIME
582 'dst', #NUMBER_DIALED
583 $duration_sub, #DURATION
584 #sub { sprintf('%.3f', shift->upstream_price ) }, #PRICE
585 sub { my($cdr, %opt) = @_; $opt{money_char}. $opt{charge}; }, #PRICE
590 sub { time2str('%D', shift->calldate_unix ) },
591 # #time2str("%Y %b %d - %r", $cdr->calldate_unix ),
594 sub { time2str('%r', shift->calldate_unix ) },
595 # 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
598 sub { my($cdr, %opt) = @_; $opt{pretty_dst} || $cdr->dst; },
600 #REGIONNAME ("Destination")
601 sub { my($cdr, %opt) = @_; $opt{dst_regionname}; },
607 sub { my($cdr, %opt) = @_; $opt{money_char}. $opt{charge}; },
611 $export_formats{'source_default'} = [ 'src', @{ $export_formats{'default'} }, ];
612 $export_formats{'accountcode_default'} =
613 [ @{ $export_formats{'default'} }[0,1],
615 @{ $export_formats{'default'} }[2..5],
619 my( $self, %opt ) = @_;
621 my $format = $opt{'format'}; # 'convergent';
622 return "Unknown format $format" unless exists $export_formats{$format};
624 #my $conf = new FS::Conf;
625 #$opt{'money_char'} ||= $conf->config('money_char') || '$';
626 $opt{'money_char'} ||= FS::Conf->new->config('money_char') || '$';
628 eval "use Text::CSV_XS;";
630 my $csv = new Text::CSV_XS;
634 ref($_) ? &{$_}($self, %opt) : $self->$_();
636 @{ $export_formats{$format} };
638 my $status = $csv->combine(@columns);
639 die "FS::CDR: error combining ". $csv->error_input(). "into downstream CSV"
652 =item invoice_formats
654 Returns an ordered list of key value pairs containing invoice format names
655 as keys (for use with part_pkg::voip_cdr) and "pretty" format names as values.
659 sub invoice_formats {
660 map { ($_ => $export_names{$_}->{'name'}) }
661 grep { $export_names{$_}->{'invoice_header'} }
665 =item invoice_header FORMAT
667 Returns a scalar containing the CSV column header for invoice format FORMAT.
673 $export_names{$format}->{'invoice_header'};
678 Returns an ordered list of key value pairs containing import format names
679 as keys (for use with batch_import) and "pretty" format names as values.
683 #false laziness w/part_pkg & part_export
686 foreach my $INC ( @INC ) {
687 warn "globbing $INC/FS/cdr/*.pm\n" if $DEBUG;
688 foreach my $file ( glob("$INC/FS/cdr/*.pm") ) {
689 warn "attempting to load CDR format info from $file\n" if $DEBUG;
690 $file =~ /\/(\w+)\.pm$/ or do {
691 warn "unrecognized file in $INC/FS/cdr/: $file\n";
695 my $info = eval "use FS::cdr::$mod; ".
696 "\\%FS::cdr::$mod\::info;";
698 die "error using FS::cdr::$mod (skipping): $@\n" if $@;
701 unless ( keys %$info ) {
702 warn "no %info hash found in FS::cdr::$mod, skipping\n";
705 warn "got CDR format info from FS::cdr::$mod: $info\n" if $DEBUG;
706 if ( exists($info->{'disabled'}) && $info->{'disabled'} ) {
707 warn "skipping disabled CDR format FS::cdr::$mod" if $DEBUG;
710 $cdr_info{$mod} = $info;
714 tie my %import_formats, 'Tie::IxHash',
715 map { $_ => $cdr_info{$_}->{'name'} }
716 sort { $cdr_info{$a}->{'weight'} <=> $cdr_info{$b}->{'weight'} }
717 grep { exists($cdr_info{$_}->{'import_fields'}) }
724 sub _cdr_min_parser_maker {
726 my @fields = ref($field) ? @$field : ($field);
727 @fields = qw( billsec duration ) unless scalar(@fields) && $fields[0];
729 my( $cdr, $min ) = @_;
730 my $sec = eval { _cdr_min_parse($min) };
731 die "error parsing seconds for @fields from $min minutes: $@\n" if $@;
732 $cdr->$_($sec) foreach @fields;
738 sprintf('%.0f', $min * 60 );
741 sub _cdr_date_parser_maker {
744 my @fields = ref($field) ? @$field : ($field);
746 my( $cdr, $datestring ) = @_;
747 my $unixdate = eval { _cdr_date_parse($datestring, %options) };
748 die "error parsing date for @fields from $datestring: $@\n" if $@;
749 $cdr->$_($unixdate) foreach @fields;
753 sub _cdr_date_parse {
757 return '' unless length($date); #that's okay, it becomes NULL
758 return '' if $date eq 'NA'; #sansay
760 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 ) {
761 my $time = str2time($date);
762 return $time if $time > 100000; #just in case
765 my($year, $mon, $day, $hour, $min, $sec);
767 #$date =~ /^\s*(\d{4})[\-\/]\(\d{1,2})[\-\/](\d{1,2})\s+(\d{1,2}):(\d{1,2}):(\d{1,2})\s*$/
768 #taqua #2007-10-31 08:57:24.113000000
770 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|$)/ ) {
771 ($year, $mon, $day, $hour, $min, $sec) = ( $1, $2, $3, $4, $5, $6 );
772 } 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|$)/ ) {
773 ($mon, $day, $year, $hour, $min, $sec) = ( $1, $2, $3, $4, $5, $6 );
775 die "unparsable date: $date"; #maybe we shouldn't die...
778 return '' if ( $year == 1900 || $year == 1970 ) && $mon == 1 && $day == 1
779 && $hour == 0 && $min == 0 && $sec == 0;
782 timegm($sec, $min, $hour, $day, $mon-1, $year);
784 timelocal($sec, $min, $hour, $day, $mon-1, $year);
788 =item batch_import HASHREF
790 Imports CDR records. Available options are:
802 Hash reference of preset fields, typically cdrbatch
806 Set true to prevent throwing an error on empty imports
812 my %import_options = (
815 'formats' => { map { $_ => $cdr_info{$_}->{'import_fields'}; }
819 #drop the || 'csv' to allow auto xls for csv types?
820 'format_types' => { map { $_ => ( lc($cdr_info{$_}->{'type'}) || 'csv' ); }
824 'format_headers' => { map { $_ => ( $cdr_info{$_}->{'header'} || 0 ); }
828 'format_sep_chars' => { map { $_ => $cdr_info{$_}->{'sep_char'}; }
832 'format_fixedlength_formats' =>
833 { map { $_ => $cdr_info{$_}->{'fixedlength_format'}; }
838 sub _import_options {
845 my $iopt = _import_options;
846 $opt->{$_} = $iopt->{$_} foreach keys %$iopt;
848 FS::Record::batch_import( $opt );
852 =item process_batch_import
856 sub process_batch_import {
859 my $opt = _import_options;
860 $opt->{'params'} = [ 'format', 'cdrbatch' ];
862 FS::Record::process_batch_import( $job, $opt, @_ );
865 # if ( $format eq 'simple' ) { #should be a callback or opt in FS::cdr::simple
866 # @columns = map { s/^ +//; $_; } @columns;
875 L<FS::Record>, schema.html from the base documentation.