-
Notifications
You must be signed in to change notification settings - Fork 3
/
Copy pathrate-o-mat.pl
executable file
·3902 lines (3320 loc) · 145 KB
/
rate-o-mat.pl
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
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
#!/usr/bin/perl -w
use lib '/usr/share/ngcp-rate-o-mat';
use strict;
use warnings;
use DBI;
use POSIX qw(setsid mktime ceil);
use Fcntl qw(LOCK_EX LOCK_NB SEEK_SET);
use IO::Handle;
use IO::Socket::UNIX;
use NetAddr::IP;
use Data::Dumper;
use Time::HiRes qw(); #for debugging info only
use List::Util qw(shuffle);
use Storable qw(dclone);
use JSON::XS qw(encode_json decode_json);
# constants: ###########################################################
$0 = 'ngcp-rate-o-mat'; ## no critic (Variables::RequireLocalizedPunctuationVars)
my $fork = $ENV{RATEOMAT_DAEMONIZE} // 0;
my $pidfile = $ENV{RATEOMAT_PIDFILE} // '/run/ngcp-rate-o-mat.pid';
my $type = 'call';
my $loop_interval = ((defined $ENV{RATEOMAT_LOOP_INTERVAL} && $ENV{RATEOMAT_LOOP_INTERVAL}) ? int $ENV{RATEOMAT_LOOP_INTERVAL} : 10);
my $debug = ((defined $ENV{RATEOMAT_DEBUG} && $ENV{RATEOMAT_DEBUG}) ? int $ENV{RATEOMAT_DEBUG} : 0);
# number of unrated cdrs to fetch at once:
my $batch_size = ((defined $ENV{RATEOMAT_BATCH_SIZE} && $ENV{RATEOMAT_BATCH_SIZE} > 0) ? int $ENV{RATEOMAT_BATCH_SIZE} : 100);
# if rate-o-mat processes are working on the same accounting.cdr table:
# set to 1 to minimize collisions (and thus rollbacks)
my $shuffle_batch = ((defined $ENV{RATEOMAT_SHUFFLE_BATCH} && $ENV{RATEOMAT_SHUFFLE_BATCH}) ? int $ENV{RATEOMAT_SHUFFLE_BATCH} : 0);
# preload the whole prepaid_costs table, if number of records
# is below this limit:
my $prepaid_costs_cache_limit = ((defined $ENV{RATEOMAT_PREPAID_COSTS_CACHE} && $ENV{RATEOMAT_PREPAID_COSTS_CACHE} > 0) ? int $ENV{RATEOMAT_PREPAID_COSTS_CACHE} : 10000);
# if split_peak_parts is set to true, rate-o-mat will create a separate
# CDR every time a peak time border is crossed for either the customer,
# the reseller or the carrier billing profile.
my $split_peak_parts = ((defined $ENV{RATEOMAT_SPLIT_PEAK_PARTS} && $ENV{RATEOMAT_SPLIT_PEAK_PARTS}) ? int $ENV{RATEOMAT_SPLIT_PEAK_PARTS} : 0);
# set to 1 to write real call costs to CDRs for postpaid, even if balance was consumed:
my $use_customer_real_cost = 0;
my $use_provider_real_cost = 0;
# don't update balance of prepaid contracts, if no prepaid_costs record is found (re-rating):
my $prepaid_update_balance = ((defined $ENV{RATEOMAT_PREPAID_UPDATE_BALANCE} && $ENV{RATEOMAT_PREPAID_UPDATE_BALANCE}) ? int $ENV{RATEOMAT_PREPAID_UPDATE_BALANCE} : 0);
# control writing cdr relation data:
# disable it for now until this will be limited to prepaid contracts,
# as it produces massive amounts of zeroed or unneeded data.
my $write_cash_balance_before_after = $ENV{RATEOMAT_WRITE_CDR_RELATION_DATA} // 0;
my $write_free_time_balance_before_after = $ENV{RATEOMAT_WRITE_CDR_RELATION_DATA} // 0;
my $write_profile_package_id = $ENV{RATEOMAT_WRITE_CDR_RELATION_DATA} // 0;
my $write_contract_balance_id = $ENV{RATEOMAT_WRITE_CDR_RELATION_DATA} // 0;
# terminate if the same cdr fails $failed_cdr_max_retries + 1 times:
my $failed_cdr_max_retries = ((defined $ENV{RATEOMAT_MAX_RETRIES} && $ENV{RATEOMAT_MAX_RETRIES} >= 0) ? int $ENV{RATEOMAT_MAX_RETRIES} : 2);
my $failed_cdr_retry_delay = ((defined $ENV{RATEOMAT_RETRY_DELAY} && $ENV{RATEOMAT_RETRY_DELAY} >= 0) ? int $ENV{RATEOMAT_RETRY_DELAY} : 30);
# with 2 retries and 30sec delay, rato-o-mat tolerates a replication
# lag of around 60secs until it terminates.
# use source_user if number and source_cli =~ /anonymous/i:
my $offnet_anonymous_source_cli_fallback = 1;
# pause between db connect attempts:
my $connect_interval = 3;
my $maintenance_mode = $ENV{RATEOMAT_MAINTENANCE} // 'no';
my $lock_timeout = 5;
my $hostname_filepath = '/etc/ngcp_hostname';
$hostname_filepath = $ENV{RATEOMAT_HOSTNAME_FILEPATH} if exists $ENV{RATEOMAT_HOSTNAME_FILEPATH};
my $multi_master = ((defined $ENV{RATEOMAT_MUTLI_MASTER} && $ENV{RATEOMAT_MUTLI_MASTER}) ? int $ENV{RATEOMAT_MUTLI_MASTER} : 0);
my $multi_master_stall = 1; # idle if other node repl is stopped
#execute contract subscriber locks if fraud limits are exceeded after a call:
my $apply_fraud_lock = ((defined $ENV{RATEOMAT_FRAUD_LOCK} && $ENV{RATEOMAT_FRAUD_LOCK}) ? int $ENV{RATEOMAT_FRAUD_LOCK} : 0);
# test may execute rate-o-mat on another host with different
# timezone. the connection timezone can therefore be forced to
# eg. the UTC default on ngcp.
my $connection_timezone = $ENV{RATEOMAT_CONNECTION_TIMEZONE};
# $ENV{TZ} has to be adjusted in the root thread.
# option to transform onpeak/offpeak times to a subscriber contract's timezone:
my $subscriber_offpeak_tz = 0; #0;
# billing database
my $BillDB_Name = $ENV{RATEOMAT_BILLING_DB_NAME} || 'billing';
my $BillDB_Host = $ENV{RATEOMAT_BILLING_DB_HOST} || 'localhost';
my $BillDB_Port = $ENV{RATEOMAT_BILLING_DB_PORT} ? int $ENV{RATEOMAT_BILLING_DB_PORT} : 3306;
my $BillDB_User = $ENV{RATEOMAT_BILLING_DB_USER} || die "Missing billing DB user setting.";
my $BillDB_Pass = $ENV{RATEOMAT_BILLING_DB_PASS}; # || die "Missing billing DB password setting.";
# accounting database
my $AcctDB_Name = $ENV{RATEOMAT_ACCOUNTING_DB_NAME} || 'accounting';
my $AcctDB_Host = $ENV{RATEOMAT_ACCOUNTING_DB_HOST} || 'localhost';
my $AcctDB_Port = $ENV{RATEOMAT_ACCOUNTING_DB_PORT} ? int $ENV{RATEOMAT_ACCOUNTING_DB_PORT} : 3306;
my $AcctDB_User = $ENV{RATEOMAT_ACCOUNTING_DB_USER} || die "Missing accounting DB user setting.";
my $AcctDB_Pass = $ENV{RATEOMAT_ACCOUNTING_DB_PASS}; # || die "Missing accounting DB password setting.";
# provisioning database
my $ProvDB_Name = $ENV{RATEOMAT_PROVISIONING_DB_NAME} || 'provisioning';
my $ProvDB_Host = $ENV{RATEOMAT_PROVISIONING_DB_HOST} || 'localhost';
my $ProvDB_Port = $ENV{RATEOMAT_PROVISIONING_DB_PORT} ? int $ENV{RATEOMAT_PROVISIONING_DB_PORT} : 3306;
my $ProvDB_User = $ENV{RATEOMAT_PROVISIONING_DB_USER};
my $ProvDB_Pass = $ENV{RATEOMAT_PROVISIONING_DB_PASS};
# duplication database
my $DupDB_Name = $ENV{RATEOMAT_DUPLICATE_DB_NAME} || 'accounting';
my $DupDB_Host = $ENV{RATEOMAT_DUPLICATE_DB_HOST} || 'localhost';
my $DupDB_Port = $ENV{RATEOMAT_DUPLICATE_DB_PORT} ? int $ENV{RATEOMAT_DUPLICATE_DB_PORT} : 3306;
my $DupDB_User = $ENV{RATEOMAT_DUPLICATE_DB_USER};
my $DupDB_Pass = $ENV{RATEOMAT_DUPLICATE_DB_PASS};
my @cdr_fields = qw(source_user_id source_provider_id source_external_subscriber_id source_external_contract_id source_account_id source_user source_domain source_cli source_clir source_ip source_lnp_prefix source_user_out destination_user_id destination_provider_id destination_external_subscriber_id destination_external_contract_id destination_account_id destination_user destination_domain destination_user_dialed destination_user_in destination_domain_in destination_lnp_prefix destination_user_out peer_auth_user peer_auth_realm call_type call_status call_code init_time start_time duration call_id source_carrier_cost source_reseller_cost source_customer_cost source_carrier_free_time source_reseller_free_time source_customer_free_time source_carrier_billing_fee_id source_reseller_billing_fee_id source_customer_billing_fee_id source_carrier_billing_zone_id source_reseller_billing_zone_id source_customer_billing_zone_id destination_carrier_cost destination_reseller_cost destination_customer_cost destination_carrier_free_time destination_reseller_free_time destination_customer_free_time destination_carrier_billing_fee_id destination_reseller_billing_fee_id destination_customer_billing_fee_id destination_carrier_billing_zone_id destination_reseller_billing_zone_id destination_customer_billing_zone_id frag_carrier_onpeak frag_reseller_onpeak frag_customer_onpeak is_fragmented split rated_at rating_status exported_at export_status source_lnp_type destination_lnp_type);
foreach my $gpp_idx(0 .. 9) {
push @cdr_fields, ("source_gpp$gpp_idx", "destination_gpp$gpp_idx");
}
my @mos_data_fields = qw(mos_average mos_average_packetloss mos_average_jitter mos_average_roundtrip);
my $acc_cash_balance_col_model_key = 1;
my $acc_time_balance_col_model_key = 2;
my $acc_relation_col_model_key = 3;
my $acc_tag_col_model_key = 4;
my $dup_cash_balance_col_model_key = 5;
my $dup_time_balance_col_model_key = 6;
my $dup_relation_col_model_key = 7;
my $dup_tag_col_model_key = 8;
# globals: #############################################################
my $shutdown = 0;
my $prepaid_costs_cache;
my %cdr_col_models = ();
my $rollback;
my $log_fatal = 1;
# load equalization using first or second order low pass filter:
my $cps_info = {
rated => 0,
rated_old => 0,
d_rated => 0,
d_rated_old => 0,
dd_rated => 0,
t => 0.0,
t_old => 0.0,
dt => 0.0,
delay => $loop_interval,
cps => 0.0,
speedup => 0.02,
speeddown => 0.01,
};
# stmt handlers: #######################################################
my $billdbh;
my $acctdbh;
my $provdbh;
my $dupdbh;
my $sth_get_contract_info;
my $sth_get_subscriber_contract_id;
my $sth_billing_info_network;
my $sth_billing_info;
my $sth_profile_info;
my $sth_profile_fraud_info;
my $sth_contract_fraud_info;
my $sth_upsert_cdr_period_costs;
my $sth_get_cdr_period_costs;
my $sth_duplicate_upsert_cdr_period_costs;
my $sth_duplicate_get_cdr_period_costs;
my $sth_offpeak;
my $sth_offpeak_subscriber;
my $sth_unrated_cdrs;
my $sth_get_cdr;
my $sth_lock_cdr;
my $sth_update_cdr;
my $sth_create_cdr_fragment;
my $sth_mos_data;
my $sth_get_cbalances;
my $sth_update_cbalance_w_underrun_profiles_lock;
my $sth_update_cbalance_w_underrun_lock;
my $sth_update_cbalance_w_underrun_profiles;
my $sth_update_cbalance;
my $sth_new_cbalance;
my $sth_new_cbalance_infinite_future;
my $sth_get_last_cbalance;
my $sth_get_cbalance;
my $sth_get_first_cbalance;
my $sth_get_last_topup_cbalance,
my $sth_lnp_number;
my $sth_lnp_profile_info;
my $sth_prepaid_costs_cache;
my $sth_prepaid_costs_count;
my $sth_prepaid_cost;
my $sth_delete_prepaid_cost;
my $sth_delete_old_prepaid;
my $sth_get_billing_voip_subscribers;
my $sth_lock_billing_subscribers;
my $sth_unlock_billing_subscribers;
my $sth_get_provisioning_voip_subscribers;
my $sth_get_usr_preference_attribute;
my $sth_get_usr_preference_value;
my $sth_create_usr_preference_value;
my $sth_update_usr_preference_value;
my $sth_delete_usr_preference_value;
my $sth_duplicate_cdr;
my $sth_duplicate_mos_data;
# run the main loop: ##################################################
main();
exit 0;
# implementation: ######################################################
sub FATAL {
my $msg = shift;
chomp $msg;
die "FATAL: $msg\n";
}
sub DEBUG {
return unless $debug;
my $msg = shift;
$msg = &$msg() if 'CODE' eq ref $msg;
chomp $msg;
$msg =~ s/#012 +/ /g;
print "DEBUG: $msg\n";
}
sub INFO {
my $msg = shift;
chomp $msg;
print "INFO: $msg\n";
}
sub WARNING {
my $msg = shift;
chomp $msg;
warn "WARNING: $msg\n";
}
sub _sql_offpeak_convert_tz {
my ($date_col) = @_;
return 'unix_timestamp(convert_tz('.$date_col.
',@@session.time_zone,(select coalesce((select tz.name FROM billing.v_contract_timezone tz WHERE tz.contract_id = ? LIMIT 1),@@session.time_zone))))';
}
sub sql_time {
my ($time) = @_;
my ($y, $m, $d, $H, $M, $S) = (localtime($time))[5,4,3,2,1,0];
$y += 1900;
$m += 1;
return sprintf('%04d-%02d-%02d %02d:%02d:%02d', $y, $m, $d, $H, $M, $S);
}
sub set_start_strtime {
my $start = shift;
my $r_str = shift;
$$r_str = sql_time($start);
return 0;
}
sub connect_billdbh {
do {
INFO "Trying to connect to billing db...";
$billdbh = DBI->connect("dbi:mysql:database=$BillDB_Name;host=$BillDB_Host;port=$BillDB_Port", $BillDB_User, $BillDB_Pass, {AutoCommit => 1, mysql_auto_reconnect => 0, mysql_no_autocommit_cmd => 0, PrintError => 1, PrintWarn => 0});
} while(!defined $billdbh && ($DBI::err == 2002 || $DBI::err == 2003) && !$shutdown && sleep $connect_interval);
FATAL "Error connecting to db: ".$DBI::errstr
unless defined($billdbh);
if ($multi_master) {
$billdbh->do("SET SESSION binlog_format = 'STATEMENT'") or WARNING 'error setting session binlog_format';
}
$billdbh->do('SET time_zone = ?',undef,$connection_timezone) or FATAL 'error setting connection timezone' if $connection_timezone;
INFO "Successfully connected to billing db...";
}
sub connect_acctdbh {
do {
INFO "Trying to connect to accounting db...";
$acctdbh = DBI->connect("dbi:mysql:database=$AcctDB_Name;host=$AcctDB_Host;port=$AcctDB_Port", $AcctDB_User, $AcctDB_Pass, {AutoCommit => 1, mysql_auto_reconnect => 0, mysql_no_autocommit_cmd => 0, PrintError => 1, PrintWarn => 0});
} while(!defined $acctdbh && ($DBI::err == 2002 || $DBI::err == 2003) && !$shutdown && sleep $connect_interval);
FATAL "Error connecting to db: ".$DBI::errstr
unless defined($acctdbh);
if ($multi_master) {
$acctdbh->do("SET SESSION binlog_format = 'STATEMENT'") or WARNING 'error setting session binlog_format';
}
$acctdbh->do('SET time_zone = ?',undef,$connection_timezone) or FATAL 'error setting connection timezone' if $connection_timezone;
INFO "Successfully connected to accounting db...";
}
sub connect_provdbh {
unless ($ProvDB_User) {
undef $dupdbh;
WARNING "No provisioning db credentials, disabled.";
return;
}
do {
INFO "Trying to connect to provisioning db...";
$provdbh = DBI->connect("dbi:mysql:database=$ProvDB_Name;host=$ProvDB_Host;port=$ProvDB_Port", $ProvDB_User, $ProvDB_Pass, {AutoCommit => 1, mysql_auto_reconnect => 0, mysql_no_autocommit_cmd => 0, PrintError => 1, PrintWarn => 0});
} while(!defined $provdbh && ($DBI::err == 2002 || $DBI::err == 2003) && !$shutdown && sleep $connect_interval);
FATAL "Error connecting to db: ".$DBI::errstr
unless defined($provdbh);
$provdbh->do('SET time_zone = ?',undef,$connection_timezone) or FATAL 'error setting connection timezone' if $connection_timezone;
INFO "Successfully connected to provisioning db...";
}
sub connect_dupdbh {
unless ($DupDB_User) {
undef $dupdbh;
INFO "No duplication db credentials, disabled.";
return;
}
do {
INFO "Trying to connect to duplication db...";
$dupdbh = DBI->connect("dbi:mysql:database=$DupDB_Name;host=$DupDB_Host;port=$DupDB_Port", $DupDB_User, $DupDB_Pass, {AutoCommit => 1, mysql_auto_reconnect => 0, mysql_no_autocommit_cmd => 0, PrintError => 1, PrintWarn => 0});
} while(!defined $dupdbh && ($DBI::err == 2002 || $DBI::err == 2003) && !$shutdown && sleep $connect_interval);
FATAL "Error connecting to db: ".$DBI::errstr
unless defined($dupdbh);
$dupdbh->do("SET SESSION binlog_format = 'STATEMENT'") or WARNING 'error setting session binlog_format';
$dupdbh->do('SET time_zone = ?',undef,$connection_timezone) or FATAL 'error setting connection timezone' if $connection_timezone;
INFO "Successfully connected to duplication db...";
}
sub begin_transaction {
my ($dbh,$isolation_level) = @_;
if ($dbh) {
if ($isolation_level) {
$dbh->do('SET TRANSACTION ISOLATION LEVEL '.$isolation_level) or FATAL "Error setting transaction isolation level: ".$dbh->errstr;
}
$dbh->begin_work or FATAL "Error starting transaction: ".$dbh->errstr;
}
}
sub commit_transaction {
my $dbh = shift;
if ($dbh) {
#capture result to force list context and prevent legacy komodo perl5db.pl bug:
my @wa = $dbh->commit or FATAL "Error committing: ".$dbh->errstr;
}
}
sub rollback_transaction {
my $dbh = shift;
if ($dbh) {
my @wa = $dbh->rollback or FATAL "Error rolling back: ".$dbh->errstr;
}
}
sub rollback_all {
eval { rollback_transaction($billdbh); };
eval { rollback_transaction($provdbh); };
eval { rollback_transaction($acctdbh); };
eval { rollback_transaction($dupdbh); };
}
sub bigint_to_bytes {
my ($bigint,$size) = @_;
return pack('C' x $size, map { hex($_) } (sprintf('%0' . 2 * $size . 's',substr($bigint->as_hex(),2)) =~ /(..)/g));
}
sub is_infinite_unix {
my $unix_ts = shift;
return 1 unless defined $unix_ts; #internally, we use undef for infinite future
return $unix_ts == 0 ? 1 : 0; #If you pass an out-of-range date to UNIX_TIMESTAMP(), it returns 0
}
sub last_day_of_month {
my $t = shift;
my ($month,$year) = (localtime($t))[4,5];
$month++;
$year += 1900;
if (1 == $month || 3 == $month || 5 == $month || 7 == $month || 8 == $month || 10 == $month || 12 == $month) {
return 31;
} elsif (2 == $month) {
my $is_leap_year = 0;
if ($year % 4 == 0) {
$is_leap_year = 1;
}
if ($year % 100 == 0) {
$is_leap_year = 0;
}
if ($year % 400 == 0) {
$is_leap_year = 1;
}
if ($is_leap_year) {
return 29;
} else {
return 28;
}
} else {
return 30;
}
}
sub init_db {
connect_billdbh;
connect_provdbh;
connect_acctdbh;
connect_dupdbh;
$sth_get_contract_info = $billdbh->prepare(
"SELECT UNIX_TIMESTAMP(c.create_timestamp),".
" UNIX_TIMESTAMP(c.modify_timestamp),".
" co.reseller_id,".
" p.id,".
" p.balance_interval_unit,".
" p.balance_interval_value,".
" p.balance_interval_start_mode,".
" p.carry_over_mode,".
" p.notopup_discard_intervals,".
" p.underrun_profile_threshold,".
" p.underrun_lock_threshold,".
" p.underrun_lock_level, ".
" (SELECT COUNT(*) FROM billing.package_profile_sets WHERE package_id = p.id AND discriminator = 'underrun') as underrun_profiles_count, ".
" product.class ".
"FROM billing.contracts c ".
"JOIN billing.products product ON c.product_id = product.id ".
"LEFT JOIN billing.profile_packages p ON c.profile_package_id = p.id ".
"LEFT JOIN billing.contacts co ON c.contact_id = co.id ".
"WHERE c.id = ?"
) or FATAL "Error preparing subscriber contract info statement: ".$billdbh->errstr;
$sth_get_last_cbalance = $billdbh->prepare(
"SELECT id, UNIX_TIMESTAMP(start), UNIX_TIMESTAMP(end), cash_balance, cash_balance_interval, ".
"free_time_balance, free_time_balance_interval, topup_count, timely_topup_count ".
"FROM billing.contract_balances ".
"WHERE contract_id = ? ".
"ORDER BY end DESC LIMIT 1"
) or FATAL "Error preparing get last contract balance statement: ".$billdbh->errstr;
$sth_get_cbalance = $billdbh->prepare(
"SELECT id, UNIX_TIMESTAMP(start), UNIX_TIMESTAMP(end), cash_balance, cash_balance_interval, ".
"free_time_balance, free_time_balance_interval, topup_count, timely_topup_count ".
"FROM billing.contract_balances ".
"WHERE id = ?"
) or FATAL "Error preparing get last contract balance statement: ".$billdbh->errstr;
$sth_get_first_cbalance = $billdbh->prepare(
"SELECT UNIX_TIMESTAMP(start),UNIX_TIMESTAMP(end) ".
"FROM billing.contract_balances ".
"WHERE contract_id = ? ".
"ORDER BY start ASC LIMIT 1"
) or FATAL "Error preparing get first contract balance statement: ".$billdbh->errstr;
$sth_get_last_topup_cbalance = $billdbh->prepare(
"SELECT UNIX_TIMESTAMP(start),UNIX_TIMESTAMP(end) ".
"FROM billing.contract_balances ".
"WHERE contract_id = ? AND ".
"topup_count > 0 ".
"ORDER BY end DESC LIMIT 1"
) or FATAL "Error preparing get last topup contract balance statement: ".$billdbh->errstr;
$sth_get_subscriber_contract_id = $billdbh->prepare(
"SELECT contract_id FROM billing.voip_subscribers WHERE uuid = ?"
) or FATAL "Error preparing subscriber contract id statement: ".$billdbh->errstr;
$sth_billing_info_network = $billdbh->prepare(<<EOS
SELECT bp.id, bp.prepaid,
bp.interval_charge, bp.interval_free_time, bp.interval_free_cash,
bp.interval_unit, bp.interval_count, bp.ignore_domain
FROM billing.billing_profiles bp
WHERE bp.id = billing.get_billing_profile_by_contract_id_network(?,?,?)
EOS
) or FATAL "Error preparing network billing info statement: ".$billdbh->errstr;
$sth_billing_info = $billdbh->prepare(<<EOS
SELECT bp.id, bp.prepaid,
bp.interval_charge, bp.interval_free_time, bp.interval_free_cash,
bp.interval_unit, bp.interval_count, bp.ignore_domain
FROM billing.billing_profiles bp
WHERE bp.id = billing.get_billing_profile_by_contract_id(?,?)
EOS
) or FATAL "Error preparing billing info statement: ".$billdbh->errstr;
$sth_lnp_number = $billdbh->prepare(
"SELECT lnp_provider_id,type FROM billing.lnp_numbers WHERE id = billing.get_lnp_number_id(?,?)"
) or FATAL "Error preparing LNP number statement: ".$billdbh->errstr;
$sth_profile_info = $billdbh->prepare(
"SELECT id, source, destination, ".
"onpeak_init_rate, onpeak_init_interval, ".
"onpeak_follow_rate, onpeak_follow_interval, ".
"offpeak_init_rate, offpeak_init_interval, ".
"offpeak_follow_rate, offpeak_follow_interval, ".
"billing_zones_history_id, offpeak_use_free_time, onpeak_use_free_time, ".
"onpeak_extra_second, onpeak_extra_rate, ".
"offpeak_extra_second, offpeak_extra_rate ".
"FROM billing.billing_fees_history WHERE id = billing.get_billing_fee_id(?,?,?,?,?,null)"
) or FATAL "Error preparing profile info statement: ".$billdbh->errstr;
$sth_profile_fraud_info = $billdbh->prepare(
"SELECT bp.fraud_interval_limit, bp.fraud_daily_limit, " .
"bp.fraud_interval_lock, bp.fraud_daily_lock, bp.fraud_use_reseller_rates " .
"FROM billing.billing_profiles bp WHERE bp.id = ?"
) or FATAL "Error preparing profile fraud info statement: ".$billdbh->errstr;
$sth_contract_fraud_info = $billdbh->prepare(
"SELECT cfp.fraud_interval_limit, cfp.fraud_daily_limit, " .
"cfp.fraud_interval_lock, cfp.fraud_daily_lock " .
"FROM billing.contract_fraud_preferences cfp WHERE cfp.contract_id = ?"
) or FATAL "Error preparing contract fraud info statement: ".$billdbh->errstr;
$sth_lnp_profile_info = $billdbh->prepare(
"SELECT id, source, destination, ".
"onpeak_init_rate, onpeak_init_interval, ".
"onpeak_follow_rate, onpeak_follow_interval, ".
"offpeak_init_rate, offpeak_init_interval, ".
"offpeak_follow_rate, offpeak_follow_interval, ".
"billing_zones_history_id, offpeak_use_free_time, onpeak_use_free_time ".
"FROM billing.billing_fees_history WHERE id = billing.get_billing_fee_id(?,?,?,null,?,\"exact_destination\")"
) or FATAL "Error preparing LNP profile info statement: ".$billdbh->errstr;
$sth_offpeak = $billdbh->prepare("select ".
"unix_timestamp(concat(date_enum.d,' ',pw.start)),unix_timestamp(concat(date_enum.d,' ',pw.end))".
" from ngcp.date_range_helper as date_enum ".
"join billing.billing_peaktime_weekdays pw on pw.weekday=weekday(date_enum.d) ".
"where date_enum.d >= date(from_unixtime(?)) ".
"and date_enum.d <= date(from_unixtime(? + ?)) ".
"and pw.billing_profile_id = ?".
" union ".
"select ".
"unix_timestamp(ps.start),unix_timestamp(ps.end)" .
" from billing.billing_peaktime_special as ps ".
"where ps.billing_profile_id = ? ".
"and (ps.start <= from_unixtime(? + ?) and ps.end >= from_unixtime(?))"
) or FATAL "Error preparing offpeak statement: ".$billdbh->errstr;
$sth_offpeak_subscriber = $billdbh->prepare("select ".
_sql_offpeak_convert_tz("concat(date_enum.d,' ',pw.start)") .','. _sql_offpeak_convert_tz("concat(date_enum.d,' ',pw.end)") .
" from ngcp.date_range_helper as date_enum ".
"join billing.billing_peaktime_weekdays pw on pw.weekday=weekday(date_enum.d) ".
"where date_enum.d >= date(from_unixtime(?)) ".
"and date_enum.d <= date(from_unixtime(? + ?)) ".
"and pw.billing_profile_id = ?".
" union ".
"select ".
_sql_offpeak_convert_tz("ps.start") .','. _sql_offpeak_convert_tz("ps.end") .
" from billing.billing_peaktime_special as ps ".
"where ps.billing_profile_id = ? ".
"and (ps.start <= from_unixtime(? + ?) and ps.end >= from_unixtime(?))"
) or FATAL "Error preparing offpeak subscriber statement: ".$billdbh->errstr;
$sth_unrated_cdrs = $acctdbh->prepare(
"SELECT * ".
"FROM accounting.cdr WHERE rating_status = 'unrated' ".
"AND id > ? ORDER BY start_time ASC LIMIT " . $batch_size
) or FATAL "Error preparing unrated cdr statement: ".$acctdbh->errstr;
$sth_get_cdr = $acctdbh->prepare(
"SELECT * ".
"FROM accounting.cdr WHERE id = ?"
) or FATAL "Error preparing get cdr statement: ".$acctdbh->errstr;
$sth_lock_cdr = $acctdbh->prepare(
"SELECT id, rating_status ".
"FROM accounting.cdr WHERE id = ? FOR UPDATE"
) or FATAL "Error preparing lock cdr statement: ".$acctdbh->errstr;
$sth_update_cdr = $acctdbh->prepare(
"UPDATE LOW_PRIORITY accounting.cdr SET ".
"source_carrier_cost = ?, source_reseller_cost = ?, source_customer_cost = ?, ".
"source_carrier_free_time = ?, source_reseller_free_time = ?, source_customer_free_time = ?, ".
"rated_at = ?, rating_status = ?, ".
"source_carrier_billing_fee_id = ?, source_reseller_billing_fee_id = ?, source_customer_billing_fee_id = ?, ".
"source_carrier_billing_zone_id = ?, source_reseller_billing_zone_id = ?, source_customer_billing_zone_id = ?, ".
"destination_carrier_cost = ?, destination_reseller_cost = ?, destination_customer_cost = ?, ".
"destination_carrier_free_time = ?, destination_reseller_free_time = ?, destination_customer_free_time = ?, ".
"destination_carrier_billing_fee_id = ?, destination_reseller_billing_fee_id = ?, destination_customer_billing_fee_id = ?, ".
"destination_carrier_billing_zone_id = ?, destination_reseller_billing_zone_id = ?, destination_customer_billing_zone_id = ?, ".
"frag_carrier_onpeak = ?, frag_reseller_onpeak = ?, frag_customer_onpeak = ?, is_fragmented = ?, ".
"duration = ? ".
"WHERE id = ?"
) or FATAL "Error preparing update cdr statement: ".$acctdbh->errstr;
my $upsert_cdr_period_costs_stmt = "INSERT INTO accounting.cdr_period_costs (" .
" id," .
" contract_id," .
" period," .
" period_date," .
" direction," .
#billing_profile_id,
" customer_cost," .
" reseller_cost," .
" cdr_count," .
" fraud_limit_exceeded," .
" fraud_limit_type," .
" first_cdr_start_time," .
" first_cdr_id," .
" last_cdr_start_time," .
" last_cdr_id" .
") VALUES (" .
" NULL," .
" ?," . #_contract_id," .
" ?," . #'month'," .
" ?," . #_month_period_date," .
" ?," . #_direction," .
#_billing_profile_id,
" ?," . #_customer_cost," .
" ?," . #_reseller_cost," .
" 1," .
" if(? > 0," . #_fraud_use_reseller_rates
" if(coalesce(? + 0.0 >= ? + 0.0,0),1,0)," . #_reseller_cost _fraud_interval_limit
" if(coalesce(? + 0.0 >= ? + 0.0,0),1,0))," . #_customer_cost _fraud_interval_limit
" ?," . #_fraud_limit_type," .
" ?," . #_cdr_start_time," .
" ?," . #_cdr_id," .
" ?," . #_cdr_start_time" .
" ?" . #_cdr_id," .
") ON DUPLICATE KEY UPDATE " .
#billing_profile_id = _billing_profile_id,
" id = LAST_INSERT_ID(id)," . #_customer_cost," .
" fraud_limit_exceeded = if(? > 0," . #_fraud_use_reseller_rates
" if(coalesce(? + reseller_cost >= ? + 0.0,0),1,0)," . #_reseller_cost _fraud_interval_limit
" if(coalesce(? + customer_cost >= ? + 0.0,0),1,0))," . #_customer_cost _fraud_interval_limit
" customer_cost = ? + customer_cost," . #_customer_cost," .
" reseller_cost = ? + reseller_cost," . #_reseller_cost," .
" cdr_count = cdr_count + 1," .
" fraud_limit_type = ?," . #_fraud_limit_type
" first_cdr_start_time = if(? + 0.0 < first_cdr_start_time," . #_cdr_start_time
" ?," . #_cdr_start_time
" first_cdr_start_time)," .
" first_cdr_id = if(? + 0 < first_cdr_id," . #_cdr_id
" ?," . #_cdr_id
" first_cdr_id)," .
" last_cdr_start_time = if(? + 0.0 > last_cdr_start_time," . #_cdr_start_time
" ?," . #_cdr_start_time
" last_cdr_start_time)," .
" last_cdr_id = if(? + 0 > last_cdr_id," . #_cdr_id
" ?," . #_cdr_id
" last_cdr_id)";
my $get_cdr_period_costs_stmt = "SELECT " .
"cpc.fraud_limit_exceeded, cpc.customer_cost, cpc.reseller_cost, cpc.cdr_count " .
"FROM accounting.cdr_period_costs as cpc WHERE " .
"cpc.id = LAST_INSERT_ID()";
$sth_upsert_cdr_period_costs = $acctdbh->prepare(
$upsert_cdr_period_costs_stmt
) or FATAL "Error preparing upsert cdr period costs statement: ".$acctdbh->errstr;
$sth_get_cdr_period_costs = $acctdbh->prepare(
$get_cdr_period_costs_stmt
) or FATAL "Error preparing get cdr period costs statement: ".$acctdbh->errstr;
$sth_mos_data = $acctdbh->prepare(
"SELECT * ".
"FROM accounting.cdr_mos_data WHERE cdr_id = ?"
) or FATAL "Error preparing mos data statement: ".$acctdbh->errstr;
if ($split_peak_parts) {
my @exclude_fragment_fields = qw(start_time duration is_fragmented);
my %exclude_fragment_fields = map { $_ => 1 } @exclude_fragment_fields;
my @fragment_fields = grep {!$exclude_fragment_fields{$_}} @cdr_fields;
$sth_create_cdr_fragment = $acctdbh->prepare(
"INSERT INTO accounting.cdr (".
join(',', @fragment_fields, @exclude_fragment_fields).
") SELECT ".
join(',', @fragment_fields). ", " .
"start_time + ?,duration - ?,1 " .
"FROM accounting.cdr " .
"WHERE id = ? AND rating_status = 'unrated'"
) or FATAL "Error preparing create cdr fragment statement: ".$acctdbh->errstr;
}
$sth_get_cbalances = $billdbh->prepare(
"SELECT id, cash_balance, cash_balance_interval, ".
"free_time_balance, free_time_balance_interval, start, ".
"unix_timestamp(start) start_unix, ".
"unix_timestamp(end) end_unix ".
"FROM billing.contract_balances ".
"WHERE contract_id = ? AND ".
"end >= FROM_UNIXTIME(?) ORDER BY start ASC"
) or FATAL "Error preparing get contract balance statement: ".$billdbh->errstr;
$sth_new_cbalance = $billdbh->prepare(
"INSERT IGNORE INTO billing.contract_balances (".
" contract_id, cash_balance, initial_cash_balance, cash_balance_interval, free_time_balance, initial_free_time_balance, free_time_balance_interval, underrun_profiles, underrun_lock, start, end".
") VALUES (?, ?, ?, ?, ?, ?, ?, IF(? = 0, NULL, FROM_UNIXTIME(?)), IF(? = 0, NULL, FROM_UNIXTIME(?)), FROM_UNIXTIME(?), FROM_UNIXTIME(?))"
) or FATAL "Error preparing create contract balance statement: ".$billdbh->errstr;
$sth_new_cbalance_infinite_future = $billdbh->prepare(
"INSERT IGNORE INTO billing.contract_balances (".
" contract_id, cash_balance, initial_cash_balance, cash_balance_interval, free_time_balance, initial_free_time_balance, free_time_balance_interval, underrun_profiles, underrun_lock, start, end".
") VALUES (?, ?, ?, ?, ?, ?, ?, IF(? = 0, NULL, FROM_UNIXTIME(?)), IF(? = 0, NULL, FROM_UNIXTIME(?)), FROM_UNIXTIME(?), '9999-12-31 23:59:59')"
) or FATAL "Error preparing create contract balance statement: ".$billdbh->errstr;
$sth_update_cbalance_w_underrun_profiles_lock = $billdbh->prepare(
"UPDATE billing.contract_balances SET ".
"cash_balance = cash_balance + ?, cash_balance_interval = cash_balance_interval + ?, ".
"free_time_balance = free_time_balance + ?, free_time_balance_interval = free_time_balance_interval + ?, underrun_profiles = FROM_UNIXTIME(?), underrun_lock = FROM_UNIXTIME(?) ".
"WHERE id = ?"
) or FATAL "Error preparing update contract balance statement: ".$billdbh->errstr;
$sth_update_cbalance_w_underrun_lock = $billdbh->prepare(
"UPDATE billing.contract_balances SET ".
"cash_balance = cash_balance + ?, cash_balance_interval = cash_balance_interval + ?, ".
"free_time_balance = free_time_balance + ?, free_time_balance_interval = free_time_balance_interval + ?, underrun_lock = FROM_UNIXTIME(?) ".
"WHERE id = ?"
) or FATAL "Error preparing update contract balance statement: ".$billdbh->errstr;
$sth_update_cbalance_w_underrun_profiles = $billdbh->prepare(
"UPDATE billing.contract_balances SET ".
"cash_balance = cash_balance + ?, cash_balance_interval = cash_balance_interval + ?, ".
"free_time_balance = free_time_balance + ?, free_time_balance_interval = free_time_balance_interval + ?, underrun_profiles = FROM_UNIXTIME(?) ".
"WHERE id = ?"
) or FATAL "Error preparing update contract balance statement: ".$billdbh->errstr;
$sth_update_cbalance = $billdbh->prepare(
"UPDATE billing.contract_balances SET ".
"cash_balance = cash_balance + ?, cash_balance_interval = cash_balance_interval + ?, ".
"free_time_balance = free_time_balance + ?, free_time_balance_interval = free_time_balance_interval + ? ".
"WHERE id = ?"
) or FATAL "Error preparing update contract balance statement: ".$billdbh->errstr;
$sth_prepaid_costs_cache = $acctdbh->prepare(
"SELECT * FROM accounting.prepaid_costs order by timestamp asc" # newer entries overwrite older ones
) or FATAL "Error preparing prepaid costs cache statement: ".$acctdbh->errstr;
$sth_prepaid_costs_count = $acctdbh->prepare(
"SELECT count(cnt.id) FROM (SELECT id FROM accounting.prepaid_costs LIMIT " . ($prepaid_costs_cache_limit + 1) . ") AS cnt"
) or FATAL "Error preparing prepaid costs count statement: ".$acctdbh->errstr;
$sth_prepaid_cost = $acctdbh->prepare( #call_id index required
'SELECT * FROM accounting.prepaid_costs WHERE call_id = ? ' .
'AND source_user_id = ? AND destination_user_id = ?' .
'ORDER BY timestamp ASC' # newer entries overwrite older ones
) or FATAL "Error preparing prepaid cost statement: ".$acctdbh->errstr;
$sth_delete_prepaid_cost = $acctdbh->prepare( #call_id index required
'DELETE FROM accounting.prepaid_costs WHERE call_id = ? ' .
'AND source_user_id = ? AND destination_user_id = ?'
) or FATAL "Error preparing delete prepaid costs statement: ".$acctdbh->errstr;
$sth_delete_old_prepaid = $acctdbh->prepare(
"DELETE FROM accounting.prepaid_costs WHERE timestamp < DATE_SUB(NOW(), INTERVAL 7 DAY) LIMIT 10000"
) or FATAL "Error preparing delete old prepaid statement: ".$acctdbh->errstr;
$sth_get_billing_voip_subscribers = $billdbh->prepare(
"SELECT uuid FROM billing.voip_subscribers WHERE contract_id = ? AND status != 'terminated'"
) or FATAL "Error preparing get billing voip subscribers statement: ".$billdbh->errstr;
$sth_lock_billing_subscribers = $billdbh->prepare(
"UPDATE billing.voip_subscribers SET status = 'locked' WHERE contract_id = ? AND status = 'active'"
) or FATAL "Error preparing lock billing subscribers statement: ".$billdbh->errstr;
$sth_unlock_billing_subscribers = $billdbh->prepare(
"UPDATE billing.voip_subscribers SET status = 'active' WHERE contract_id = ? AND status = 'locked'"
) or FATAL "Error preparing lock billing subscribers statement: ".$billdbh->errstr;
if ($provdbh) {
$sth_get_provisioning_voip_subscribers = $provdbh->prepare(
"SELECT id FROM provisioning.voip_subscribers WHERE uuid = ?"
) or FATAL "Error preparing get provisioning voip subscribers statement: ".$provdbh->errstr;
$sth_get_usr_preference_attribute = $provdbh->prepare(
"SELECT id FROM provisioning.voip_preferences WHERE attribute = ? AND usr_pref = 1"
) or FATAL "Error preparing get usr preference attribute statement: ".$provdbh->errstr;
$sth_get_usr_preference_value = $provdbh->prepare(
"SELECT id,value FROM provisioning.voip_usr_preferences WHERE attribute_id = ? AND subscriber_id = ?"
) or FATAL "Error preparing get usr preference value statement: ".$provdbh->errstr;
$sth_create_usr_preference_value = $provdbh->prepare(
"INSERT INTO provisioning.voip_usr_preferences (subscriber_id, attribute_id, value) VALUES (?, ?, ?)"
) or FATAL "Error preparing create usr preference value statement: ".$provdbh->errstr;
$sth_update_usr_preference_value = $provdbh->prepare(
"UPDATE provisioning.voip_usr_preferences SET value = ? WHERE id = ?"
) or FATAL "Error preparing update usr preference value statement: ".$provdbh->errstr;
$sth_delete_usr_preference_value = $provdbh->prepare(
"DELETE FROM provisioning.voip_usr_preferences WHERE id = ?"
) or FATAL "Error preparing delete usr preference value statement: ".$provdbh->errstr;
}
prepare_cdr_col_models($acctdbh,
$acc_cash_balance_col_model_key,
$acc_time_balance_col_model_key,
$acc_relation_col_model_key,
$acc_tag_col_model_key,
'local');
if ($dupdbh) {
$sth_duplicate_cdr = $dupdbh->prepare(
'insert into cdr ('.
join(',', @cdr_fields).
') values ('.
join(',', (map {'?'} @cdr_fields)).
')'
) or FATAL "Error preparing duplicate_cdr statement: ".$dupdbh->errstr;
$sth_duplicate_mos_data = $dupdbh->prepare(
'insert into cdr_mos_data ('.
join(',', 'cdr_id',@mos_data_fields,'cdr_start_time').
') values (?,'.
join(',', (map {'?'} @mos_data_fields)).
',?) ON DUPLICATE KEY UPDATE ' . join(',',map { $_ . ' = ?'; } @mos_data_fields)
) or FATAL "Error preparing duplicate_mos_data statement: ".$dupdbh->errstr;
$sth_duplicate_upsert_cdr_period_costs = $dupdbh->prepare(
$upsert_cdr_period_costs_stmt
) or FATAL "Error preparing duplicate upsert cdr period costs statement: ".$dupdbh->errstr;
$sth_duplicate_get_cdr_period_costs = $dupdbh->prepare(
$get_cdr_period_costs_stmt
) or FATAL "Error preparing duplicate get cdr period costs statement: ".$dupdbh->errstr;
prepare_cdr_col_models($dupdbh,
$dup_cash_balance_col_model_key,
$dup_time_balance_col_model_key,
$dup_relation_col_model_key,
$dup_tag_col_model_key,
'duplication');
}
foreach (keys %cdr_col_models) {
init_cdr_col_model($_);
}
return 1;
}
sub prepare_cdr_col_models {
my $dbh = shift;
my $cash_balance_col_model_key = shift;
my $time_balance_col_model_key = shift;
my $relation_col_model_key = shift;
my $tag_col_model_key = shift;
my $description_prefix = shift;
prepare_cdr_col_model($dbh,$cash_balance_col_model_key,$description_prefix.' cdr cash balance column model',$description_prefix,
[ 'provider', 'direction', 'cash_balance' ], # avoid using Tie::IxHash
{
provider => {
sql => 'SELECT * FROM accounting.cdr_provider',
description => "get $description_prefix cdr provider cols",
},
direction => { # the name "direction" for "source" and "destination" is not ideal
sql => 'SELECT * FROM accounting.cdr_direction',
description => "get $description_prefix cdr direction cols",
},
cash_balance => {
sql => 'SELECT * FROM accounting.cdr_cash_balance',
description => "get $description_prefix cdr cash balance cols",
},
},{
sql => "INSERT INTO accounting.cdr_cash_balance_data".
" (cdr_id,cdr_start_time,provider_id,direction_id,cash_balance_id,val_before,val_after) VALUES".
" (?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE ".
"val_before = ?, val_after = ?",
description => "write $description_prefix cdr cash balance col data",
},{
sql => "SELECT val_before, val_after FROM accounting.cdr_cash_balance_data".
" WHERE cdr_id = ? AND provider_id = ? AND direction_id = ? AND cash_balance_id = ?",
description => "read $description_prefix cdr cash balance col data",
}
);
prepare_cdr_col_model($dbh,$time_balance_col_model_key,$description_prefix.' cdr time balance column model',$description_prefix,
[ 'provider', 'direction', 'time_balance' ],
{
provider => {
sql => 'SELECT * FROM accounting.cdr_provider',
description => "get $description_prefix cdr provider cols",
},
direction => {
sql => 'SELECT * FROM accounting.cdr_direction',
description => "get $description_prefix cdr direction cols",
},
time_balance => {
sql => 'SELECT * FROM accounting.cdr_time_balance',
description => "get $description_prefix cdr time balance cols",
},
},{
sql => "INSERT INTO accounting.cdr_time_balance_data".
" (cdr_id,cdr_start_time,provider_id,direction_id,time_balance_id,val_before,val_after) VALUES".
" (?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE ".
"val_before = ?, val_after = ?",
description => "write $description_prefix cdr time balance col data",
},{
sql => "SELECT val_before, val_after FROM accounting.cdr_time_balance_data".
" WHERE cdr_id = ? AND provider_id = ? AND direction_id = ? AND time_balance_id = ?",
description => "read $description_prefix cdr time balance col data",
}
);
prepare_cdr_col_model($dbh,$relation_col_model_key,$description_prefix.' cdr relation column model',$description_prefix,
[ 'provider', 'direction', 'relation' ],
{
provider => {
sql => 'SELECT * FROM accounting.cdr_provider',
description => "get $description_prefix cdr provider cols",
},
direction => {
sql => 'SELECT * FROM accounting.cdr_direction',
description => "get $description_prefix cdr direction cols",
},
relation => {
sql => 'SELECT * FROM accounting.cdr_relation',
description => "get $description_prefix relation cols",
},
},{
sql => "INSERT INTO accounting.cdr_relation_data".
" (cdr_id,cdr_start_time,provider_id,direction_id,relation_id,val) VALUES".
" (?,?,?,?,?,?) ON DUPLICATE KEY UPDATE ".
"val = ?",
description => "write $description_prefix cdr relation col data",
},{
sql => "SELECT val FROM accounting.cdr_relation_data".
" WHERE cdr_id = ? AND provider_id = ? AND direction_id = ? AND relation_id = ?",
description => "read $description_prefix cdr relation col data",
}
);
prepare_cdr_col_model($dbh,$tag_col_model_key,$description_prefix.' cdr tag column model',$description_prefix,
[ 'provider', 'direction', 'tag' ],
{
provider => {
sql => 'SELECT * FROM accounting.cdr_provider',
description => "get $description_prefix cdr provider cols",
},
direction => {
sql => 'SELECT * FROM accounting.cdr_direction',
description => "get $description_prefix cdr direction cols",
},
tag => {
sql => 'SELECT * FROM accounting.cdr_tag',
description => "get $description_prefix tag cols",
},
},{
sql => "INSERT INTO accounting.cdr_tag_data".
" (cdr_id,cdr_start_time,provider_id,direction_id,tag_id,val) VALUES".
" (?,?,?,?,?,?) ON DUPLICATE KEY UPDATE ".
"val = ?",
description => "write $description_prefix cdr tag col data",
},{
sql => "SELECT val FROM accounting.cdr_tag_data".
" WHERE cdr_id = ? AND provider_id = ? AND direction_id = ? AND tag_id = ?",
description => "read $description_prefix cdr tag col data",
}
);
}
sub lock_cdr {
my $cdr = shift;
my $sth = $sth_lock_cdr;
$sth->execute($cdr->{id})
or FATAL "Error executing cdr row lock selection statement: ".$sth->errstr;
my ($id,$rating_status) = $sth->fetchrow_array;
$sth->finish;