1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
|
#!/usr/bin/perl
#
# */5 * * * /home/ivan/freeside/bin/cdr-netsapiens.import ivan exportnum
use strict;
use vars qw( $DEBUG );
use Date::Format;
use REST::Client;
use FS::UID qw(adminsuidsetup dbh);
use FS::Record qw(qsearchs);
use FS::part_export;
use FS::cdr;
$DEBUG = 1;
my $user = shift or die &usage;
adminsuidsetup $user;
my $exportnum = shift or die &usage;
my $part_export = qsearchs('part_export', { 'exportnum' => $exportnum } )
or die "unknown exportnum $exportnum\n";
my $cdrbatch = 'NetSapiens import '. time2str('%Y-%m-%d %x', $^T);
my $cdrs = 0;
do {
#find max time_release
my $sth = dbh->prepare('SELECT MAX(enddate) FROM cdr') #XXX and imported from this netsapens switch
or die dbh->errstr;
$sth->execute or die $sth->errstr;
my $time_release = time2str('%Y-%m-%d %X', $sth->fetchrow_arrayref->[0]);
#retreive CDRs >= this time
my $ns = $part_export->ns_command( 'GET', '/cdr/',
'time_release' => "$time_release,",
'_sort' => '+time_release',
'_limit' => '500',
);
#loop over them, double check duplicates, insert the rest
my $content = $ns->responseContent;
$cdrs = 0;
#<a href="/tac2/cdr/20090219201719000016%40SkyNet360.Com">20090219201719000016@SkyNet360.Com</a>
# <form method="post" action="/tac2/cdr/20090219201719000016%40SkyNet360.Com">
while ( $content =~
s/^.*?<form class="" method="post" action="\/tac2\/cdr\/(\d{4})(\d+)\%40[^"]*">//is )
{
my $cdrid = ($1-1900). $2; #2009 -> 109 so we fit in a bigint
unless ( $cdrs ) { #inefficient
my $dsth = dbh->prepare(
'SELECT cdrid FROM cdr WHERE cdrid IS NOT NULL AND cdrid = ?'
) or die dbh->errstr;
$dsth->execute($cdrid) or die $dsth->errstr;
my $row = $dsth->fetchrow_arrayref;
if ( $row && $row->[0] eq $cdrid ) { # == w/ 8 byte int?
warn "$cdrid (dup)\n" if $DEBUG > 1;
next;
}
}
warn "$cdrid\n" if $DEBUG > 1;
$content =~ s/(.*?)<\/form>//is;
my $cdr_content = $1;
my %cdr = ();
while ( $cdr_content =~
s/.*?<input name="(\w+)" type="\w+" value="([^"]+)" \/>//is )
{
warn " $1 => $2\n" if $DEBUG > 2;
$cdr{$1} = $2;
}
$cdrs++;
my $cdr = new FS::cdr {
'src' => $cdr{'orig_from_user'}, #orig_sub
'dst' => $cdr{'orig_to_user'}, #term_sub?
'startdate' => FS::cdr::_cdr_date_parse($cdr{'time_start'}),
'enddate' => FS::cdr::_cdr_date_parse($cdr{'time_release'}),
'duration' => $cdr{'duration'},
'billsec' => $cdr{'time_talking'},
#'disposition' =>
#'accountcode' =>
#'charged_party'
'cdrid' => $cdrid,
'cdrbatch' => $cdrbatch,
};
my $error = $cdr->insert;
die $error if $error;
}
} while $cdrs;
sub usage {
"Usage: \n cdr-netsapiens.import user exportnum\n";
}
__END__
rly_prt_0 => 23946
orig_req_host => residential.skynet360.com
batch_dura => 0
orig_from_host => 63.251.149.5
batch_tim_beg => 2009-02-19 20:17:19
term_match => sip:7865457300@residential.skynet360.com
term_domain => residential.skynet360.com
term_sub => 7865457300
orig_req_user => 7865457300
orig_callid => 5D1164E6-44E011D6-8C84C368-EA5A0BC4@63.251.149.5
term_ip => 63.251.148.137:1453
term_to_uri => sip:7865457300@residential.skynet360.com
release_code => end
time_start => 2009-02-19 20:17:19.0
batch_hold => 0
orig_from_user => 9046384544
time_holding => 0
term_logi_uri => sip:7865457300@residential.skynet360.com
time_talking => 0
orig_from_uri => sip:9046384544@63.251.149.5
duration => 0
orig_logi_uri => sip:9046384544@63.251.149.5
rly_cnt_b => 0
time_insert => 2009-02-19 15:17:38.0
orig_to_user => 7865457300
rly_prt_a => 63.251.149.18:21972
cdr_index => 0
orig_to_host => 63.251.149.18
orig_match => sip:*@63.251.149.5
time_release => 2009-02-19 20:17:37
codec => G.711 u-law
orig_req_uri => sip:7865457300@residential.skynet360.com
orig_to_uri => sip:7865457300@63.251.149.18
rly_cnt_a => 13
orig_ip => 63.251.149.5:57326
release_text => Orig: Cancel
time_disp => 0
time_ringing => 2009-02-19 20:17:19
_method => put
prt_0 => 23946
orig_req_host => residential.skynet360.com
batch_dura => 0
orig_from_host => 63.251.149.5
batch_tim_beg => 2009-02-19 20:17:19
term_match => sip:7865457300@residential.skynet360.com
term_domain => residential.skynet360.com
time_start => 2009-02-19 20:17:19.0
term_sub => 7865457300
orig_req_user => 7865457300
orig_callid => 5D1164E6-44E011D6-8C84C368-EA5A0BC4@63.251.149.5
term_ip => 63.251.148.137:1453
term_to_uri => sip:7865457300@residential.skynet360.com
release_code => end
time_start => 2009-02-19 20:17:19.0
batch_hold => 0
orig_from_user => 9046384544
time_holding => 0
term_logi_uri => sip:7865457300@residential.skynet360.com
time_talking => 0
orig_from_uri => sip:9046384544@63.251.149.5
duration => 0
orig_logi_uri => sip:9046384544@63.251.149.5
rly_cnt_b => 0
time_insert => 2009-02-19 15:17:38.0
orig_to_user => 7865457300
rly_prt_a => 63.251.149.18:21972
cdr_index => 0
orig_to_host => 63.251.149.18
orig_match => sip:*@63.251.149.5
time_release => 2009-02-19 20:17:37
codec => G.711 u-law
orig_req_uri => sip:7865457300@residential.skynet360.com
orig_to_uri => sip:7865457300@63.251.149.18
rly_cnt_a => 13
orig_ip => 63.251.149.5:57326
release_text => Orig: Cancel
time_disp => 0
time_ringing => 2009-02-19 20:17:19
_method => put
list of freeside CDR fields, useful ones marked with *
acctid - primary key
*[1] calldate - Call timestamp (SQL timestamp)
clid - Caller*ID with text
* src - Caller*ID number / Source number
* dst - Destination extension
dcontext - Destination context
channel - Channel used
dstchannel - Destination channel if appropriate
lastapp - Last application if appropriate
lastdata - Last application data
* startdate - Start of call (UNIX-style integer timestamp)
answerdate - Answer time of call (UNIX-style integer timestamp)
* enddate - End time of call (UNIX-style integer timestamp)
* duration - Total time in system, in seconds
* billsec - Total time call is up, in seconds
*[2] disposition - What happened to the call: ANSWERED, NO ANSWER, BUSY
amaflags - What flags to use: BILL, IGNORE etc, specified on a per
channel basis like accountcode.
*[3] accountcode - CDR account number to use: account
uniqueid - Unique channel identifier (Unitel/RSLCOM Event ID)
userfield - CDR user-defined field
cdr_type - CDR type - see FS::cdr_type (Usage = 1, S&E = 7, OC&C = 8)
*[4] charged_party - Service number to be billed
upstream_currency - Wholesale currency from upstream
*[5] upstream_price - Wholesale price from upstream
upstream_rateplanid - Upstream rate plan ID
rated_price - Rated (or re-rated) price
distance - km (need units field?)
islocal - Local - 1, Non Local = 0
*[6] calltypenum - Type of call - see FS::cdr_calltype
description - Description (cdr_type 7&8 only) (used for
cust_bill_pkg.itemdesc)
quantity - Number of items (cdr_type 7&8 only)
carrierid - Upstream Carrier ID (see FS::cdr_carrier)
upstream_rateid - Upstream Rate ID
svcnum - Link to customer service (see FS::cust_svc)
freesidestatus - NULL, done (or something)
[1] Auto-populated from startdate if not present
[2] Package options available to ignore calls without a specific disposition
[3] When using 'cdr-charged_party-accountcode' config
[4] Auto-populated from src (normal calls) or dst (toll free calls) if not present
[5] When using 'upstream_simple' rating method.
[6] Set to usage class classnum when using pre-rated CDRs and usage class-based
taxation (local/intrastate/interstate/international)
|
