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
|
<% include( 'elements/browse.html',
'title' => 'Rating Regions and Prefixes',
'name_singular' => 'region', #'rate region',
'menubar' => [ 'Rate plans' => $p.'browse/rate.cgi' ],
'html_init' => $html_init,
'html_posttotal' => $html_posttotal,
'query' => {
'select' => $select,
'table' => 'rate_region',
'addl_from' => $join,
'extra_sql' => $extra_sql,
'order_by' => 'ORDER BY LOWER(regionname)',
},
'count_query' => $count_query,
'header' => \@header,
'fields' => \@fields,
'links' => \@links,
'align' => \@align,
'xls_format' => \@xls_format,
)
%>
<%once>
my $edit_url = $p.'edit/rate_region.cgi';
my $link = [ "$edit_url?regionnum=", 'regionnum' ];
my $html_init =
'Regions and prefixes for VoIP and call billing.<BR><BR>'.
qq(<A HREF="$edit_url"><I>Add a new region</I></A><BR><BR>);
#not quite right for the shouldn't-happen multiple countrycode per region case
my $select = 'rate_region.*, ';
my $join = '';
my $group_sql = '';
if ( driver_name =~ /^Pg/ ) {
my $fromwhere = 'FROM rate_prefix'.
' WHERE rate_prefix.regionnum = rate_region.regionnum';
my $prefix_sql = " CASE WHEN nxx IS NULL OR nxx = '' ".
" THEN npa ".
" ELSE npa || '-' || nxx ".
" END";
my $prefixes_sql = "SELECT $prefix_sql $fromwhere AND npa IS NOT NULL ORDER BY npa";
$select .= "( SELECT '+'||countrycode $fromwhere LIMIT 1 ) AS ccode,
ARRAY_TO_STRING( ARRAY($prefixes_sql), ', ' ) AS prefixes";
} elsif ( driver_name =~ /^mysql/i ) {
$join = 'LEFT JOIN rate_prefix USING ( regionnum )';
$select .= "'+'||GROUP_CONCAT( DISTINCT countrycode ) AS ccode,
GROUP_CONCAT( npa ORDER BY npa SEPARATOR ', ' ) AS prefixes ";
$group_sql = 'GROUP BY regionnum, regionname';
} else {
die 'unknown database '. driver_name;
}
my $base_count_sql = 'SELECT COUNT(*) FROM rate_region';
tie my %granularity, 'Tie::IxHash', FS::rate_detail::granularities();
</%once>
<%init>
die "access denied"
unless $FS::CurrentUser::CurrentUser->access_right('Configuration');
my $sub_prefixes = sub {
my $region = shift;
$region->prefixes .
($region->exact_match ? ' <I>(exact match only)</I>' : '');
};
my @header = ();
my @fields = ();
my @links = ();
my @align = ();
my @xls_format = ();
if ( FS::Record->scalar_sql('SELECT 1 FROM rate_region WHERE agent_regionid IS NOT NULL LIMIT 1') ) {
push @header, 'Legacy #';
push @fields, 'agent_regionid';
push @links, $link;
push @align, 'right';
push @xls_format, { locked=>1, bg_color=>22};
}
push @header, 'Region', 'Country code', 'Prefixes';
push @fields, 'regionname', 'ccode', $sub_prefixes;
push @links, ($link) x 3;
push @align, 'left', 'right', 'left';
push @xls_format, ({ locked=>1, bg_color=>22 }) x 3;
my $countrycode_filter_change =
"window.location = '".
$cgi->url. "?countrycode=' + this.options[this.selectedIndex].value;";
my $countrycode = '';
my $extra_sql = $group_sql;
my $count_query = $base_count_sql;
if ( $cgi->param('countrycode') =~ /^(\d+)$/ ) {
$countrycode = $1;
my $ccode_sql = '( SELECT countrycode FROM rate_prefix
WHERE rate_prefix.regionnum = rate_region.regionnum
LIMIT 1
)';
$extra_sql = " WHERE $ccode_sql = '$1' $extra_sql";
$count_query .= " WHERE $ccode_sql = '$1'";
}
sub _rate_detail_factory {
my( $rate, $field ) = @_;
return sub {
my $rate_detail = $rate->dest_detail(shift)
|| new FS::rate_detail { 'min_included' => 0,
'min_charge' => 0,
'min_cost' => 0,
'sec_granularity' => 0,
};
my $value = $rate_detail->$field();
$field eq 'sec_granularity' ? $granularity{$value} : $value;
};
}
if ( $cgi->param('show_rates') ) {
foreach my $rate ( qsearch('rate', {}) ) {
my $label = $rate->ratenum.': '. $rate->ratename;
push @header, "$label: Included minutes/calls",
"$label: Charge per minute/call",
"$label: Granularity",
"$label: Usage class";
#closure me harder
push @fields, _rate_detail_factory($rate, 'min_included'),
_rate_detail_factory($rate, 'min_charge'),
_rate_detail_factory($rate, 'sec_granularity'),
_rate_detail_factory($rate, 'classnum');
push @links, ( ('') x 4 );
push @xls_format, ( ({}) x 4 );
}
}
sub countrycode2country {
my $cc = shift;
#exceptions to the below
return 'North America (NANPA)' if $cc eq '1';
#return 'Algeria' if $cc eq '214';
return 'Ascension Island' if $cc eq '247';
return 'Satellite / Maritime / Universal Personal' if $cc eq '87'; #? or 870..
return 'Inmarsat' if $cc eq '870';
return 'Global Mobile Satellite System' if $cc eq '881';
return 'International Networks' if $cc eq '882';
return 'International Networks' if $cc eq '883';
code2country(
Number::Phone::Country::phone2country("+$cc")
);
}
my $html_posttotal =
'(show country code: '.
qq(<SELECT NAME="countrycode" onChange="$countrycode_filter_change">).
qq(<OPTION VALUE="">(all)).
join("\n", map { qq(<OPTION VALUE="$_").
($_ eq $countrycode ? ' SELECTED' : '' ).
">+$_ - ". encode_entities( countrycode2country($_) ),
}
FS::rate_prefix->all_countrycodes
).
'</SELECT>)';
</%init>
|
