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
|
<% 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 = ( '#', 'Region', 'Country code', 'Prefixes' );
my @fields = ( 'regionnum', 'regionname', 'ccode', $sub_prefixes );
my @links = ( ($link) x 4 );
my @align = ( 'right', 'left', 'right', 'left' );
my @xls_format = ( ({ locked=>1, bg_color=>22 }) x 4 );
$cgi->param('dummy', 1);
my $countrycode_filter_change =
"window.location = '".
$cgi->self_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 );
}
}
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' : '' ).
">$_",
}
FS::rate_prefix->all_countrycodes
).
'</SELECT>)';
</%init>
|
