5 use Date::Parse 'str2time';
6 use Date::Format 'time2str';
7 use FS::UID qw(adminsuidsetup dbh);
13 getopts('H:U:P:D:T:s:e:c:', \%opt);
14 my $user = shift or die &usage;
16 my $dsn = 'dbi:mysql';
17 $dsn .= ":database=$opt{D}" if $opt{D};
18 $dsn .= ":host=$opt{H}" if $opt{H};
20 my $mysql = DBI->connect($dsn, $opt{U}, $opt{P})
23 my ($start, $end) = ('', '');
25 $start = str2time($opt{s}) or die "can't parse start date $opt{s}\n";
26 $start = time2str('%Y-%m-%d', $start);
29 $end = str2time($opt{e}) or die "can't parse end date $opt{e}\n";
30 $end = time2str('%Y-%m-%d', $end);
35 my $fsdbh = FS::UID::dbh;
37 # check for existence of freesidestatus
38 my $table = $opt{T} || 'cc_call';
39 my $status = $mysql->selectall_arrayref("SHOW COLUMNS FROM $table WHERE Field = 'freesidestatus'");
41 print "Adding freesidestatus column...\n";
42 $mysql->do("ALTER TABLE $table ADD COLUMN freesidestatus varchar(32)")
43 or die $mysql->errstr;
46 print "freesidestatus column present\n";
50 # id - primary key, sequential
51 # session_id - Local/<digits>-<digits> or SIP/<digits>-<digits>
52 # uniqueid - a decimal number, seems to be close to the unix timestamp
53 # card_id - probably the equipment port, 1 - 10
54 # nasipaddress - we don't care
55 # starttime, stoptime - timestamps
56 # sessiontime - duration, seconds
58 # sessionbill - upstream_price
59 # id_tariffgroup - null, 0, 1
60 # id_tariffplan - null, 0, 3, 4, 5, 6, 7, 8, 9
61 # id_ratecard - larger numbers
62 # (all of the id_* fields are foreign keys: cc_tariffgroup, cc_ratecard, etc.)
63 # id_trunk - we don't care
64 # sipiax - probably don't care
65 # src - src. Usually a phone number, but not always.
66 # id_did - always null
67 # buycost - wholesale price? correlated with sessionbill
68 # id_card_package_offer - no idea
69 # real_sessiontime - close to sessiontime, except when it's null
70 # (When sessiontime = 0, real_sessiontime is either 0 or null, and
71 # sessionbill is 0. When sessiontime > 0, but real_sessiontime is null,
72 # sessionbill is 0. So real_sessiontime seems to be the billable time, and
73 # is null when the call is non-billable.)
74 # dnid - sometimes equals calledstation, or calledstation without the leading
75 # "1". But not always.
76 # terminatecauseid - integer, 0 - 7
77 # destination - seems to be the NPA or NPA+NXX sometimes, or "0".
79 # terminatecauseid values:
82 1 => 'ANSWER', #the only one that's billable
95 starttime stoptime sessiontime real_sessiontime
98 id_tariffplan id_ratecard sessionbill
101 my $sql = 'SELECT '.join(',', @cols). " FROM $table".
102 ' WHERE freesidestatus IS NULL' .
103 ($start && " AND starttime >= '$start'") .
104 ($end && " AND starttime < '$end'") ;
105 my $sth = $mysql->prepare($sql);
107 print "Importing ".$sth->rows." records...\n";
109 my $cdr_batch = new FS::cdr_batch({
110 'cdrbatch' => 'mysql-import-'. time2str('%Y/%m/%d-%T',time),
112 my $error = $cdr_batch->insert;
113 die $error if $error;
114 my $cdrbatchnum = $cdr_batch->cdrbatchnum;
119 while ( $row = $sth->fetchrow_hashref ) {
120 $row->{calledstation} =~ s/^1//;
121 $row->{src} =~ s/^1//;
122 my $cdr = FS::cdr->new ({
123 uniqueid => $row->{sessionid},
124 cdrbatchnum => $cdrbatchnum,
125 startdate => time2str($row->{starttime}),
126 enddate => time2str($row->{stoptime}),
127 duration => $row->{sessiontime},
128 billsec => $row->{real_sessiontime},
129 dst => $row->{calledstation},
131 upstream_rateplanid => $row->{id_tariffplan},
132 upstream_rateid => $row->{id_ratecard}, # I think?
133 upstream_price => $row->{sessionbill},
135 $cdr->cdrtypenum($opt{c}) if $opt{c};
137 my $error = $cdr->insert;
139 print "failed import: $error\n";
142 my $updated = $mysql->do(
143 "UPDATE $table SET freesidestatus = 'done' WHERE id = ?",
147 $updates += $updated;
148 print "failed to set status: ".$mysql->errstr."\n" unless $updated;
151 print "Done.\nImported $imports CDRs, marked $updates as done in source database.\n";
161 [ -s start ] [ -e end ] [ -c cdrtypenum ]