From 9f73c5d4c0954b3a03bfcb5e010fc288a7071209 Mon Sep 17 00:00:00 2001 From: ivan Date: Mon, 4 May 2009 00:22:44 +0000 Subject: [PATCH] add menu item and page for d/ling and edit rates with excel. RT#5108 --- httemplate/browse/rate_region.html | 13 +++++--- httemplate/elements/menu.html | 16 ++++++---- httemplate/misc/rate_edit_excel.html | 51 ++++++++++++++++++++++++++++++ httemplate/search/elements/search-xls.html | 42 ++++++++++++------------ httemplate/search/elements/search.html | 8 +++-- 5 files changed, 97 insertions(+), 33 deletions(-) create mode 100644 httemplate/misc/rate_edit_excel.html diff --git a/httemplate/browse/rate_region.html b/httemplate/browse/rate_region.html index 456ba3452..4e4986987 100644 --- a/httemplate/browse/rate_region.html +++ b/httemplate/browse/rate_region.html @@ -15,6 +15,7 @@ 'header' => \@header, 'fields' => \@fields, 'links' => \@links, + 'xls_format' => \@xls_format, ) %> <%once> @@ -40,7 +41,7 @@ if ( driver_name =~ /^Pg/ ) { " 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, @@ -60,9 +61,10 @@ tie my %granularity, 'Tie::IxHash', FS::rate_detail::granularities(); 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 = @@ -110,7 +112,8 @@ if ( $cgi->param('show_rates') ) { _rate_detail_factory($rate, 'sec_granularity'), _rate_detail_factory($rate, 'classnum'); - push @links, '', '', '', ''; + push @links, ( ('') x 4 ); + push @xls_format, ( ({}) x 4 ); } diff --git a/httemplate/elements/menu.html b/httemplate/elements/menu.html index 409a1525a..487373710 100644 --- a/httemplate/elements/menu.html +++ b/httemplate/elements/menu.html @@ -227,13 +227,16 @@ tie my %tools_importing, 'Tie::IxHash', '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', @@ -292,6 +295,7 @@ tie my %config_billing_rates, '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'; diff --git a/httemplate/misc/rate_edit_excel.html b/httemplate/misc/rate_edit_excel.html new file mode 100644 index 000000000..7d3255301 --- /dev/null +++ b/httemplate/misc/rate_edit_excel.html @@ -0,0 +1,51 @@ +<% 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) %> + + + 1. Download current rates: + + Download rate spreadsheet + + + + + 2. Edit rates with Excel (or other .XLS-compatible application) + + + <% include( '/elements/file-upload.html', + 'field' => 'file', + 'label' => '3. Upload edited rate file: '. ( ' 'x4 ), #yuck + ) + %> + + + + + + + + + + +<% include('/elements/footer.html') %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Configuration'); + + diff --git a/httemplate/search/elements/search-xls.html b/httemplate/search/elements/search-xls.html index 8a6ad5cac..8a05e477c 100644 --- a/httemplate/search/elements/search-xls.html +++ b/httemplate/search/elements/search-xls.html @@ -25,9 +25,18 @@ my $workbook = Spreadsheet::WriteExcel->new($XLS) 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++; @@ -37,39 +46,32 @@ foreach my $row ( @$rows ) { #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(); - # } - #} + + 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; } } diff --git a/httemplate/search/elements/search.html b/httemplate/search/elements/search.html index 59139327f..a66176d00 100644 --- a/httemplate/search/elements/search.html +++ b/httemplate/search/elements/search.html @@ -148,11 +148,15 @@ Example: 'align' => 'lrc.', #listrefs of ( scalars or coderefs ) - #currently only HTML, maybe eventually Excel too + # currently only HTML, maybe eventually Excel too 'color' => [], 'size' => [], 'style' => [], # or , 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' => => [], ); @@ -227,7 +231,7 @@ if ( $opt{'agent_virt'} ) { #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'} } ]; } -- 2.11.0