3 # */5 * * * /home/ivan/freeside/bin/cdr-netsapiens.import ivan exportnum
9 use FS::UID qw(adminsuidsetup dbh);
10 use FS::Record qw(qsearchs);
16 my $user = shift or die &usage;
19 my $exportnum = shift or die &usage;
20 my $part_export = qsearchs('part_export', { 'exportnum' => $exportnum } )
21 or die "unknown exportnum $exportnum\n";
23 my $cdrbatch = 'NetSapiens import '. time2str('%Y-%m-%d %x', $^T);
29 #find max time_release
30 my $sth = dbh->prepare('SELECT MAX(enddate) FROM cdr') #XXX and imported from this netsapens switch
33 $sth->execute or die $sth->errstr;
34 my $time_release = time2str('%Y-%m-%d %x', $sth->fetchrow_arrayref->[0]);
36 #retreive CDRs >= this time
38 my $ns = $part_export->ns_command( 'GET', '/cdr/',
39 'time_release' => "$time_release,",
40 '_sort' => '+time_release',
43 #loop over them, double check duplicates, insert the rest
45 my $content = $ns->responseContent;
49 #<a href="/tac2/cdr/20090219201719000016%40SkyNet360.Com">20090219201719000016@SkyNet360.Com</a>
50 # <form method="post" action="/tac2/cdr/20090219201719000016%40SkyNet360.Com">
52 s/^.*?<form class="" method="post" action="\/tac2\/cdr\/(\d+)\%40[^"]*">//is )
56 warn "$accountcode\n" if $DEBUG;
58 $content =~ s/(.*?)<\/form>//is;
62 while ( $cdr_content =~
63 s/.*?<input name="(\w+)" type="\w+" value="([^"]+)" \/>//is )
65 warn " $1 => $2\n" if $DEBUG > 1;
71 my $cdr = new FS::cdr {
72 'src' => $cdr{'orig_sub'}, #orig_from_user?
73 'dst' => $cdr{'term_sub'},
74 'startdate' => FS::cdr::_cdr_date_parse($cdr{'time_start'}),
75 'enddate' => FS::cdr::_cdr_date_parse($cdr{'time_release'}),
76 'duration' => $cdr{'duration'},
77 'billsec' => $cdr{'time_talking'},
79 'accountcode' => $accountcode,
81 'cdrbatch' => $cdrbatch,
84 my $error = $cdr->insert;
92 "Usage: \n cdr-netsapiens.import user exportnum\n";
98 orig_req_host => residential.skynet360.com
100 orig_from_host => 63.251.149.5
101 batch_tim_beg => 2009-02-19 20:17:19
102 term_match => sip:7865457300@residential.skynet360.com
103 term_domain => residential.skynet360.com
104 term_sub => 7865457300
105 orig_req_user => 7865457300
106 orig_callid => 5D1164E6-44E011D6-8C84C368-EA5A0BC4@63.251.149.5
107 term_ip => 63.251.148.137:1453
108 term_to_uri => sip:7865457300@residential.skynet360.com
110 time_start => 2009-02-19 20:17:19.0
112 orig_from_user => 9046384544
114 term_logi_uri => sip:7865457300@residential.skynet360.com
116 orig_from_uri => sip:9046384544@63.251.149.5
118 orig_logi_uri => sip:9046384544@63.251.149.5
120 time_insert => 2009-02-19 15:17:38.0
121 orig_to_user => 7865457300
122 rly_prt_a => 63.251.149.18:21972
124 orig_to_host => 63.251.149.18
125 orig_match => sip:*@63.251.149.5
126 time_release => 2009-02-19 20:17:37
128 orig_req_uri => sip:7865457300@residential.skynet360.com
129 orig_to_uri => sip:7865457300@63.251.149.18
131 orig_ip => 63.251.149.5:57326
132 release_text => Orig: Cancel
134 time_ringing => 2009-02-19 20:17:19
137 orig_req_host => residential.skynet360.com
139 orig_from_host => 63.251.149.5
140 batch_tim_beg => 2009-02-19 20:17:19
141 term_match => sip:7865457300@residential.skynet360.com
142 term_domain => residential.skynet360.com
143 time_start => 2009-02-19 20:17:19.0
144 term_sub => 7865457300
145 orig_req_user => 7865457300
146 orig_callid => 5D1164E6-44E011D6-8C84C368-EA5A0BC4@63.251.149.5
147 term_ip => 63.251.148.137:1453
148 term_to_uri => sip:7865457300@residential.skynet360.com
150 time_start => 2009-02-19 20:17:19.0
152 orig_from_user => 9046384544
154 term_logi_uri => sip:7865457300@residential.skynet360.com
156 orig_from_uri => sip:9046384544@63.251.149.5
158 orig_logi_uri => sip:9046384544@63.251.149.5
160 time_insert => 2009-02-19 15:17:38.0
161 orig_to_user => 7865457300
162 rly_prt_a => 63.251.149.18:21972
164 orig_to_host => 63.251.149.18
165 orig_match => sip:*@63.251.149.5
166 time_release => 2009-02-19 20:17:37
168 orig_req_uri => sip:7865457300@residential.skynet360.com
169 orig_to_uri => sip:7865457300@63.251.149.18
171 orig_ip => 63.251.149.5:57326
172 release_text => Orig: Cancel
174 time_ringing => 2009-02-19 20:17:19
177 list of freeside CDR fields, useful ones marked with *
180 *[1] calldate - Call timestamp (SQL timestamp)
181 clid - Caller*ID with text
182 * src - Caller*ID number / Source number
183 * dst - Destination extension
184 dcontext - Destination context
185 channel - Channel used
186 dstchannel - Destination channel if appropriate
187 lastapp - Last application if appropriate
188 lastdata - Last application data
189 * startdate - Start of call (UNIX-style integer timestamp)
190 answerdate - Answer time of call (UNIX-style integer timestamp)
191 * enddate - End time of call (UNIX-style integer timestamp)
192 * duration - Total time in system, in seconds
193 * billsec - Total time call is up, in seconds
194 *[2] disposition - What happened to the call: ANSWERED, NO ANSWER, BUSY
195 amaflags - What flags to use: BILL, IGNORE etc, specified on a per
196 channel basis like accountcode.
197 *[3] accountcode - CDR account number to use: account
198 uniqueid - Unique channel identifier (Unitel/RSLCOM Event ID)
199 userfield - CDR user-defined field
200 cdr_type - CDR type - see FS::cdr_type (Usage = 1, S&E = 7, OC&C = 8)
201 *[4] charged_party - Service number to be billed
202 upstream_currency - Wholesale currency from upstream
203 *[5] upstream_price - Wholesale price from upstream
204 upstream_rateplanid - Upstream rate plan ID
205 rated_price - Rated (or re-rated) price
206 distance - km (need units field?)
207 islocal - Local - 1, Non Local = 0
208 *[6] calltypenum - Type of call - see FS::cdr_calltype
209 description - Description (cdr_type 7&8 only) (used for
210 cust_bill_pkg.itemdesc)
211 quantity - Number of items (cdr_type 7&8 only)
212 carrierid - Upstream Carrier ID (see FS::cdr_carrier)
213 upstream_rateid - Upstream Rate ID
214 svcnum - Link to customer service (see FS::cust_svc)
215 freesidestatus - NULL, done (or something)
217 [1] Auto-populated from startdate if not present
218 [2] Package options available to ignore calls without a specific disposition
219 [3] When using 'cdr-charged_party-accountcode' config
220 [4] Auto-populated from src (normal calls) or dst (toll free calls) if not present
221 [5] When using 'upstream_simple' rating method.
222 [6] Set to usage class classnum when using pre-rated CDRs and usage class-based
223 taxation (local/intrastate/interstate/international)