3 #false laziness w/other cdr-*.import scripts, especially the other MySQL ones
10 use Date::Parse 'str2time';
11 use Date::Format 'time2str';
12 use FS::UID qw(adminsuidsetup dbh);
18 getopts('H:U:P:D:T:s:e:c:', \%opt);
19 my $user = shift or die &usage;
21 my $dsn = 'dbi:mysql';
22 $dsn .= ":database=$opt{D}" if $opt{D};
23 $dsn .= ":host=$opt{H}" if $opt{H};
25 my $mysql = DBI->connect($dsn, $opt{U}, $opt{P})
28 my ($start, $end) = ('', '');
30 $start = str2time($opt{s}) or die "can't parse start date $opt{s}\n";
31 $start = time2str('%Y-%m-%d', $start);
34 $end = str2time($opt{e}) or die "can't parse end date $opt{e}\n";
35 $end = time2str('%Y-%m-%d', $end);
40 # check for existence of freesidestatus
41 my $table = 'calls'; # $opt{T} || 'calls';
42 my $status = $mysql->selectall_arrayref("SHOW COLUMNS FROM $table WHERE Field = 'freesidestatus'");
44 print "Adding freesidestatus column...\n";
45 $mysql->do("ALTER TABLE $table ADD COLUMN freesidestatus varchar(32)")
46 or die $mysql->errstr;
49 print "freesidestatus column present\n";
53 id_call id_client ip_number caller_id called_number call-start call_end
54 route_type id_tariff cost duration tariff_prefix client_type id_route pdd
55 costR1 costR2 costR3 costD id_reseller tariffdesc id_cc ratio client_pdd
56 orig_call_id term_call_id id_callback_call id_cn dialing_plan_prefix
57 call_rate effective_duration dtmf call-data tariff_data id_dial_plan
60 #effective_duration/billsec appears from the documentation to be post-minimum time and granularity, calculated on the switch (see p4-5). would probably have weired effects if we then tried to do the same in freeside.
62 #so, probably either set the switch minimal_time and resolution to 1 sec and do the minimum and granularity calculation in freeside, OR, the other way around, if client prefers to set minimal_time and resolution in the switch, then the freeside rating should be no minimum, no (1 second) granularity
64 #(if you're rating and not just passing through cost->upstream_price)
67 #id_client - (w/client_type) charged_party
68 #ip_number - src_ip_addr
69 #caller_id - src (or clid?)
71 #call_start - startdate
74 #id_tariff - upstream_rateplanid
75 #cost - upstream_price
78 #client_type - (w/id_client) charged_party
86 #tariffdesc - upstream_dst_regionname
90 #orig_call_id - clid or is this src?
91 #term_call_id - need this?
95 #call_rate - upstream_rate?
96 #effective_duration - billsec
98 #call_data - disposition
100 # (these last two appear to be undocumented)
104 my $sql = 'SELECT '.join(',', @cols). " FROM $table ".
105 ' WHERE freesidestatus IS NULL' .
106 ($start && " AND call_start >= '$start'") .
107 ($end && " AND call_start < '$end'") ;
109 my $sth = $mysql->prepare($sql);
111 print "Importing ".$sth->rows." records...\n";
113 my $cdr_batch = new FS::cdr_batch({
114 'cdrbatch' => 'mysql-import-'. time2str('%Y/%m/%d-%T',time),
116 my $error = $cdr_batch->insert;
117 die $error if $error;
118 my $cdrbatchnum = $cdr_batch->cdrbatchnum;
122 while ( $row = $sth->fetchrow_hashref ) {
124 my $cdr = FS::cdr->new({
125 cdrid => $row->{id_call},
126 charged_party => sprintf('%.2d', $row->{client_type}).
128 src_ip_addr => $row->{ip_number},
129 src => $row->{caller_id},
130 dst => $row->{called_number},
131 startdate => str2time($row->{call_start}),
132 enddate => str2time($row->{call_end}),
133 channel => $row->{route_type},
134 upstream_rateplanid => $row->{id_tariff},
135 upstream_price => $row->{cost},
136 duration => $row->{duration},
137 upstream_dst_regionname => $row->{tariffdesc},
138 uniqueid => $row->{id_cc},
139 orig_call_id => $row->{clid},
140 billsec => $row->{effective_duration},
141 lastdata => $row->{dtmf},
142 disposition => $row->{call_data},
144 cdrbatchnum => $cdrbatchnum,
147 $cdr->cdrtypenum($opt{c}) if $opt{c};
149 #print $row->{id_call},"\n" if $opt{v};
150 my $error = $cdr->insert;
152 #die $row->{id_call} . ": failed import: $error\n";
153 print $row->{id_call} . ": failed import: $error\n";
157 my $updated = $mysql->do(
158 "UPDATE $table SET freesidestatus = 'done' WHERE id_call = ?",
162 #$updates += $updated;
163 die "failed to set status: ".$mysql->errstr."\n" unless $updated;
168 print "Imported $imported CDRs.\n" if $imported;
172 "Usage: \n cdr-voipswitch.import\n\t[ -H host ]\n\t-D database\n\t-U user\n\t-P password\n\t[ -s start ] [ -e end ] [ -c cdrtypenum ] \n\tfreesideuser\n";