'header' => \@header,
'fields' => \@fields,
'links' => \@links,
+ 'xls_format' => \@xls_format,
)
%>
<%once>
" END";
my $prefixes_sql = "SELECT $prefix_sql $fromwhere AND npa IS NOT NULL";
$select .= "( SELECT countrycode $fromwhere LIMIT 1 ) AS ccode,
- ARRAY_TO_STRING( ARRAY($prefixes_sql), ', ' ) AS prefixes";
+ 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,
die "access denied"
unless $FS::CurrentUser::CurrentUser->access_right('Configuration');
-my @header = ( '#', 'Region', 'Country code', 'Prefixes' );
-my @fields = ( 'regionnum', 'regionname', 'ccode', 'prefixes' );
-my @links = ( $link, $link, $link, $link );
+my @header = ( '#', 'Region', 'Country code', 'Prefixes' );
+my @fields = ( 'regionnum', 'regionname', 'ccode', 'prefixes' );
+my @links = ( ($link) x 4 );
+my @xls_format = ( ({ locked=>1, bg_color=>22 }) x 4 );
$cgi->param('dummy', 1);
my $countrycode_filter_change =
_rate_detail_factory($rate, 'sec_granularity'),
_rate_detail_factory($rate, 'classnum');
- push @links, '', '', '', '';
+ push @links, ( ('') x 4 );
+ push @xls_format, ( ({}) x 4 );
}
'Import payments from CSV file' => [ $fsurl.'misc/cust_pay-import.cgi', '' ],
'Import phone numbers (DIDs)' => [ $fsurl.'misc/phone_avail-import.html', '' ],
'Import Call Detail Records (CDRs) from CSV file' => [ $fsurl.'misc/cdr-import.html', '' ],
+# 'Import call rates and regions' => [ $fsurl.'misc/rate-import.html', '' ],
;
-if ( $conf->exists('taxdatadirectdownload') ) {
- $tools_importing{'Import tax rates from vendor site'} =
- [ $fsurl.'misc/tax-fetch_and_import.cgi', '' ];
-} else {
- $tools_importing{'Import tax rates from CSV files'} =
- [ $fsurl.'misc/tax-import.cgi', '' ];
+if ( $conf->exists('enable_taxproducts') ) {
+ if ( $conf->exists('taxdatadirectdownload') ) {
+ $tools_importing{'Import tax rates from vendor site'} =
+ [ $fsurl.'misc/tax-fetch_and_import.cgi', '' ];
+ } else {
+ $tools_importing{'Import tax rates from CSV files'} =
+ [ $fsurl.'misc/tax-import.cgi', '' ];
+ }
}
tie my %tools_exporting, 'Tie::IxHash',
'View/Edit rate plans' => [ $fsurl.'browse/rate.cgi', 'Manage rate plans' ],
'View/Edit regions and prefixes' => [ $fsurl.'browse/rate_region.html', 'Manage regions and prefixes' ],
'View/Edit usage classes' => [ $fsurl.'browse/usage_class.html', 'Usage classes define groups of usage for taxation purposes.' ],
+ 'Edit rates with Excel' => [ $fsurl.'misc/rate_edit_excel.html', 'Download and edit rates with Excel, then upload changes.' ],
;
tie my %config_billing, 'Tie::IxHash';
--- /dev/null
+<% include('/elements/header.html', 'Edit rates with Excel' ) %>
+
+<% include( '/elements/form-file_upload.html',
+ 'name' => 'RateImportForm',
+ 'action' => 'process/rate_edit_excel.html',
+ 'num_files' => 1,
+ 'fields' => [ 'format' ], #?
+ 'message' => 'Rate edit successful',
+ 'url' => $p."browse/rate_region.html",
+ )
+%>
+
+<% &ntable("#cccccc", 2) %>
+
+ <TR>
+ <TH ALIGN="left">1. Download current rates:</TH>
+ <TD>
+ <A HREF="<%$p%>/browse/rate_region.html?show_rates=1;_type=regions.xls">Download rate spreadsheet</A>
+ </TD>
+ </TR>
+
+ <TR>
+ <TH ALIGN="left" COLSPAN=2>2. Edit rates with Excel (or other .XLS-compatible application)</TH>
+ </TR>
+
+ <% include( '/elements/file-upload.html',
+ 'field' => 'file',
+ 'label' => '3. Upload edited rate file: '. ( ' 'x4 ), #yuck
+ )
+ %>
+
+ <TR>
+ <TD COLSPAN=2 ALIGN="center" STYLE="padding-top:6px">
+ <INPUT TYPE = "submit"
+ ID = "submit"
+ VALUE = "Upload"
+ onClick = "document.RateImportForm.submit.disabled=true;"
+ >
+ </TD>
+ </TR>
+
+
+</TABLE>
+
+<% include('/elements/footer.html') %>
+<%init>
+
+die "access denied"
+ unless $FS::CurrentUser::CurrentUser->access_right('Configuration');
+
+</%init>
my $worksheet = $workbook->add_worksheet(substr($opt{'title'},0,31));
+$worksheet->protect();
+
my($r,$c) = (0,0);
-$worksheet->write($r, $c++, $_) foreach @$header;
+my $header_format = $workbook->add_format(
+ bold => 1,
+ locked => 1,
+ bg_color => 55, #22,
+ bottom => 3,
+);
+
+$worksheet->write($r, $c++, $_, $header_format ) foreach @$header;
foreach my $row ( @$rows ) {
$r++;
#my $links = $opt{'links'} ? [ @{$opt{'links'}} ] : '';
#my $aligns = $opt{'align'} ? [ @{$opt{'align'}} ] : '';
+ #could also translate color, size, style into xls equivalents?
+ my $formats = $opt{'xls_format'} ? [ @{$opt{'xls_format'}} ] : [];
foreach my $field ( @{$opt{'fields'}} ) {
- #my $align = $aligns ? shift @$aligns : '';
- #$align = " ALIGN=$align" if $align;
- #my $a = '';
- #if ( $links ) {
- # my $link = shift @$links;
- # $link = &{$link}($row) if ref($link) eq 'CODE';
- # if ( $link ) {
- # my( $url, $method ) = @{$link};
- # if ( ref($method) eq 'CODE' ) {
- # $a = $url. &{$method}($row);
- # } else {
- # $a = $url. $row->$method();
- # }
- # $a = qq(<A HREF="$a">);
- # }
- #}
+
+ my $format = shift @$formats;
+ $format = &{$format}($row) if ref($format) eq 'CODE';
+ $format ||= {};
+ my $xls_format = $workbook->add_format(locked=>0, %$format);
+
if ( ref($field) eq 'CODE' ) {
foreach my $value ( &{$field}($row) ) {
if ( ref($value) eq 'ARRAY' ) {
$worksheet->write($r, $c++, '(N/A)' ); #unimplemented
} else {
- $worksheet->write($r, $c++, $value );
+ $worksheet->write($r, $c++, $value, $xls_format );
}
}
} else {
- $worksheet->write($r, $c++, $row->$field() );
+ $worksheet->write($r, $c++, $row->$field(), $xls_format );
}
}
} else {
- $worksheet->write($r, $c++, $_) foreach @$row;
+ my $xls_format = $workbook->add_format(locked=>0);
+ $worksheet->write($r, $c++, $_, $xls_format ) foreach @$row;
}
}
'align' => 'lrc.',
#listrefs of ( scalars or coderefs )
- #currently only HTML, maybe eventually Excel too
+ # currently only HTML, maybe eventually Excel too
'color' => [],
'size' => [],
'style' => [], #<B> or <I>, etc.
'cell_style' => [], #STYLE= attribute of TR, very HTML-specific...
+
+ # Excel-specific listref of ( hashrefs or coderefs )
+ # each hashref: http://search.cpan.org/dist/Spreadsheet-WriteExcel/lib/Spreadsheet/WriteExcel.pm#Format_methods_and_Format_properties
+ 'xls_format' => => [],
);
#false laziness w/statuspos above
my $pos = $opt{'agent_pos'};
- foreach my $att (qw( align style color size )) {
+ foreach my $att (qw( align color size style cell_style xls_format )) {
$opt{$att} ||= [ map '', @{ $opt{'fields'} } ];
}