From 9bafdefcce8d3586429d2878f148bb4fd211b4e2 Mon Sep 17 00:00:00 2001 From: Ivan Kohler Date: Fri, 17 Aug 2012 18:53:49 -0700 Subject: [PATCH] quotation reports, RT#16996 --- FS/FS/AccessRight.pm | 1 + FS/FS/access_right.pm | 1 + FS/FS/quotation.pm | 153 +++++++++++++++++- httemplate/elements/menu.html | 7 + httemplate/search/quotation.html | 268 ++++++++++++++++++++++++++++++++ httemplate/search/report_quotation.html | 75 +++++++++ 6 files changed, 504 insertions(+), 1 deletion(-) create mode 100755 httemplate/search/quotation.html create mode 100644 httemplate/search/report_quotation.html diff --git a/FS/FS/AccessRight.pm b/FS/FS/AccessRight.pm index d6e0adb79..26867c318 100644 --- a/FS/FS/AccessRight.pm +++ b/FS/FS/AccessRight.pm @@ -264,6 +264,7 @@ tie my %rights, 'Tie::IxHash', 'List all customers', 'Advanced customer search', 'List zip codes', #NEW + 'List quotations', 'List invoices', 'List packages', 'Summarize packages', diff --git a/FS/FS/access_right.pm b/FS/FS/access_right.pm index 22fe94da8..397b456ce 100644 --- a/FS/FS/access_right.pm +++ b/FS/FS/access_right.pm @@ -197,6 +197,7 @@ sub _upgrade_data { # class method 'Unsuspend customer package' => 'Unsuspend customer', 'New prospect' => 'Generate quotation', 'Delete invoices' => 'Void invoices', + 'List invoices' => 'List quotations', 'List services' => [ 'Services: Accounts', 'Services: Domains', diff --git a/FS/FS/quotation.pm b/FS/FS/quotation.pm index 9e7723c77..bf2711b0a 100644 --- a/FS/FS/quotation.pm +++ b/FS/FS/quotation.pm @@ -147,7 +147,34 @@ sub cust_bill_pkg { #actually quotation_pkg objects qsearch('quotation_pkg', { quotationnum=>$self->quotationnum }); } -=back +=item total_setup + +=cut + +sub total_setup { + my $self = shift; + $self->_total('setup'); +} + +=item total_recur [ FREQ ] + +=cut + +sub total_recur { + my $self = shift; +#=item total_recur [ FREQ ] + #my $freq = @_ ? shift : ''; + $self->_total('recur'); +} + +sub _total { + my( $self, $method ) = @_; + + my $total = 0; + $total += $_->$method() for $self->cust_bill_pkg; + sprintf('%.2f', $total); + +} =item enable_previous @@ -155,6 +182,130 @@ sub cust_bill_pkg { #actually quotation_pkg objects sub enable_previous { 0 } +=back + +=head1 CLASS METHODS + +=over 4 + + +=item search_sql_where HASHREF + +Class method which returns an SQL WHERE fragment to search for parameters +specified in HASHREF. Valid parameters are + +=over 4 + +=item _date + +List reference of start date, end date, as UNIX timestamps. + +=item invnum_min + +=item invnum_max + +=item agentnum + +=item charged + +List reference of charged limits (exclusive). + +=item owed + +List reference of charged limits (exclusive). + +=item open + +flag, return open invoices only + +=item net + +flag, return net invoices only + +=item days + +=item newest_percust + +=back + +Note: validates all passed-in data; i.e. safe to use with unchecked CGI params. + +=cut + +sub search_sql_where { + my($class, $param) = @_; + #if ( $DEBUG ) { + # warn "$me search_sql_where called with params: \n". + # join("\n", map { " $_: ". $param->{$_} } keys %$param ). "\n"; + #} + + my @search = (); + + #agentnum + if ( $param->{'agentnum'} =~ /^(\d+)$/ ) { + push @search, "( prospect_main.agentnum = $1 OR cust_main.agentnum = $1 )"; + } + +# #refnum +# if ( $param->{'refnum'} =~ /^(\d+)$/ ) { +# push @search, "cust_main.refnum = $1"; +# } + + #prospectnum + if ( $param->{'prospectnum'} =~ /^(\d+)$/ ) { + push @search, "quotation.prospectnum = $1"; + } + + #custnum + if ( $param->{'custnum'} =~ /^(\d+)$/ ) { + push @search, "cust_bill.custnum = $1"; + } + + #_date + if ( $param->{_date} ) { + my($beginning, $ending) = @{$param->{_date}}; + + push @search, "quotation._date >= $beginning", + "quotation._date < $ending"; + } + + #quotationnum + if ( $param->{'quotationnum_min'} =~ /^(\d+)$/ ) { + push @search, "quotation.quotationnum >= $1"; + } + if ( $param->{'quotationnum_max'} =~ /^(\d+)$/ ) { + push @search, "quotation.quotationnum <= $1"; + } + +# #charged +# if ( $param->{charged} ) { +# my @charged = ref($param->{charged}) +# ? @{ $param->{charged} } +# : ($param->{charged}); +# +# push @search, map { s/^charged/cust_bill.charged/; $_; } +# @charged; +# } + + my $owed_sql = FS::cust_bill->owed_sql; + + #days + push @search, "quotation._date < ". (time-86400*$param->{'days'}) + if $param->{'days'}; + + #agent virtualization + my $curuser = $FS::CurrentUser::CurrentUser; + #false laziness w/search/quotation.html + push @search,' ( '. $curuser->agentnums_sql( table=>'prospect_main' ). + ' OR '. $curuser->agentnums_sql( table=>'cust_main' ). + ' ) '; + + join(' AND ', @search ); + +} + +=back + =head1 BUGS =head1 SEE ALSO diff --git a/httemplate/elements/menu.html b/httemplate/elements/menu.html index c656cb9eb..b2141e991 100644 --- a/httemplate/elements/menu.html +++ b/httemplate/elements/menu.html @@ -94,6 +94,11 @@ tie my %report_prospects, 'Tie::IxHash', 'Advanced prospect reports' => [ $fsurl. 'search/report_prospect_main.html', '' ], ; +tie my %report_quotations, 'Tie::IxHash', + 'List quotations' => [ $fsurl. 'search/quotation.html', '' ], + 'Advanced quotation reports' => [ $fsurl. 'search/report_quotation.html', '' ], +; + tie my %report_customers_lists, 'Tie::IxHash', 'by customer number' => [ $fsurl. 'search/cust_main.cgi?browse=custnum', '' ], 'by last name' => [ $fsurl. 'search/cust_main.cgi?browse=last', '' ], @@ -344,6 +349,8 @@ if($curuser->access_right('Financial reports')) { tie my %report_menu, 'Tie::IxHash'; $report_menu{'Prospects'} = [ \%report_prospects, 'Prospect reports' ] if $curuser->access_right('List prospects'); +$report_menu{'Quotations'} = [ \%report_quotations, 'Quotation reports' ] + if $curuser->access_right('List quotations'); $report_menu{'Customers'} = [ \%report_customers, 'Customer reports' ] if $curuser->access_right('List customers'); $report_menu{'Invoices'} = [ \%report_invoices, 'Invoice reports' ] diff --git a/httemplate/search/quotation.html b/httemplate/search/quotation.html new file mode 100755 index 000000000..259c85c22 --- /dev/null +++ b/httemplate/search/quotation.html @@ -0,0 +1,268 @@ +<& elements/search.html, + 'title' => emt('Quotation Search Results'), + 'html_init' => $html_init, + 'menubar' => $menubar, + 'name' => 'quotations', + 'query' => $sql_query, + 'count_query' => $count_query, + 'count_addl' => $count_addl, + 'redirect' => $link, + 'header' => [ emt('Quotation #'), + emt('Setup'), + emt('Recurring'), + emt('Date'), + emt('Prospect'), + emt('Customer'), + ], + 'fields' => [ + 'quotationnum', + sub { $money_char. shift->total_setup }, + sub { $money_char. shift->total_recur }, + sub { time2str('%b %d %Y', shift->_date ) }, + sub { my $prospect_main = shift->prospect_main; + $prospect_main ? $prospect_main->name : ''; + }, + sub { my $cust_main = shift->cust_main; + $cust_main ? $cust_main->name : ''; + }, + #\&FS::UI::Web::cust_fields, + ], + 'sort_fields' => [ + 'quotationnum', + '', #FS::quotation->total_setup_sql, + '', #FS::quotation->total_recur_sql, + '_date', + '', + '', + ], + 'align' => 'rrrrll', #.FS::UI::Web::cust_aligns(), + 'links' => [ + $link, + $link, + $link, + $link, + $prospect_link, + $cust_link, + #( map { $_ ne 'Cust. Status' ? $clink : '' } + # FS::UI::Web::cust_header() + #), + ], +# 'color' => [ +# '', +# '', +# '', +# '', +# '', +# FS::UI::Web::cust_colors(), +# ], +# 'style' => [ +# '', +# '', +# '', +# '', +# '', +# FS::UI::Web::cust_styles(), +# ], +&> +<%init> + +my $curuser = $FS::CurrentUser::CurrentUser; + +die "access denied" + unless $curuser->access_right('List quotations'); + +my $join_prospect_main = 'LEFT JOIN prospect_main USING ( prospectnum )'; +my $join_cust_main = 'LEFT JOIN cust_main ON ( quotation.custnum = cust_main.custnum )'; + +#here is the agent virtualization +my $agentnums_sql = ' ( '. $curuser->agentnums_sql( table=>'prospect_main' ). + ' OR '. $curuser->agentnums_sql( table=>'cust_main' ). + ' ) '; + +my( $count_query, $sql_query ); +my $count_addl = ''; +my %search; + +#if ( $cgi->param('quotationnum') =~ /^\s*(FS-)?(\d+)\s*$/ ) { +# +# my $where = "WHERE quotationnum = $2 AND $agentnums_sql"; +# +# $count_query = "SELECT COUNT(*) FROM quotation $join_prospect_main $join_cust_main $where"; +# +# $sql_query = { +# 'table' => 'quotation', +# 'addl_from' => "$join_prospect_main $join_cust_main", +# 'hashref' => {}, +# 'extra_sql' => $where, +# }; +# +#} else { + + #some false laziness w/cust_bill::re_X + my $orderby = 'ORDER BY quotation._date'; + + if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { + $search{'agentnum'} = $1; + } + +# if ( $cgi->param('refnum') =~ /^(\d+)$/ ) { +# $search{'refnum'} = $1; +# } + + if ( $cgi->param('prospectnum') =~ /^(\d+)$/ ) { + $search{'prospectnum'} = $1; + } + + if ( $cgi->param('custnum') =~ /^(\d+)$/ ) { + $search{'custnum'} = $1; + } + + # begin/end/beginning/ending + my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi, ''); + $search{'_date'} = [ $beginning, $ending ] + unless $beginning == 0 && $ending == 4294967295; + + if ( $cgi->param('quotationnum_min') =~ /^\s*(\d+)\s*$/ ) { + $search{'quotationnum_min'} = $1; + } + if ( $cgi->param('quotationnum_max') =~ /^\s*(\d+)\s*$/ ) { + $search{'quotationnum_max'} = $1; + } + + #amounts + $search{$_} = [ FS::UI::Web::parse_lt_gt($cgi, $_) ] + foreach qw( total_setup total_recur ); + +# my($query) = $cgi->keywords; +# if ( $query =~ /^(OPEN(\d*)_)?(invnum|date|custnum)$/ ) { +# $search{'open'} = 1 if $1; +# ($search{'days'}, my $field) = ($2, $3); +# $field = "_date" if $field eq 'date'; +# $orderby = "ORDER BY cust_bill.$field"; +# } + +# if ( $cgi->param('newest_percust') ) { +# $search{'newest_percust'} = 1; +# $count_query = "SELECT COUNT(DISTINCT cust_bill.custnum), 'N/A', 'N/A'"; +# } + + my $extra_sql = ' WHERE '. FS::quotation->search_sql_where( \%search ); + + unless ( $count_query ) { + $count_query = 'SELECT COUNT(*)'; + } + $count_query .= " FROM quotation $join_prospect_main $join_cust_main $extra_sql"; + + $sql_query = { + 'table' => 'quotation', + 'addl_from' => "$join_prospect_main $join_cust_main", + 'hashref' => {}, + 'select' => join(', ', + 'quotation.*', + #( map "cust_main.$_", qw(custnum last first company) ), + 'prospect_main.prospectnum as prospect_main_prospectnum', + 'cust_main.custnum as cust_main_custnum', + #FS::UI::Web::cust_sql_fields(), + ), + 'extra_sql' => $extra_sql, + 'order_by' => $orderby, + }; + +#} + +my $link = [ "${p}view/quotation.html?", 'quotationnum', ]; +my $prospect_link = sub { + my $quotation = shift; + $quotation->prospect_main_prospectnum + ? [ "${p}view/prospect_main.html?", 'prospectnum' ] + : ''; +}; + +my $cust_link = sub { + my $quotation = shift; + $quotation->cust_main_custnum + ? [ "${p}view/cust_main.cgi?", 'custnum' ] + : ''; +}; + +my $conf = new FS::Conf; +my $money_char = $conf->config('money_char') || '$'; + +my $html_init = join("\n", map { + ( my $action = $_ ) =~ s/_$//; + include('/elements/progress-init.html', + $_.'form', + [ keys %search ], + "../misc/${_}invoices.cgi", + { 'message' => "Invoices re-${action}ed" }, #would be nice to show the number of them, but... + $_, #key + ), + qq!
!, + ( map { my $f = $_; + my @values = ref($search{$f}) ? @{ $search{$f} } : $search{$f}; + map qq!!, @values; + } + keys %search + ), + qq!
! +} qw( print_ email_ fax_ ftp_ spool_ ) ). + +''; + +my $menubar = []; + +#if ( $curuser->access_right('Resend quotations') ) { +# +# push @$menubar, emt('Print these invoices') => +# "javascript:confirm_print_process()", +# emt('Email these invoices') => +# "javascript:confirm_email_process()"; +# +# push @$menubar, emt('Fax these invoices') => +# "javascript:confirm_fax_process()" +# if $conf->exists('hylafax'); +# +# push @$menubar, emt('FTP these invoices') => +# "javascript:confirm_ftp_process()" +# if $conf->exists('cust_bill-ftpformat'); +# +# push @$menubar, emt('Spool these invoices') => +# "javascript:confirm_spool_process()" +# if $conf->exists('cust_bill-spoolformat'); +# +#} + + diff --git a/httemplate/search/report_quotation.html b/httemplate/search/report_quotation.html new file mode 100644 index 000000000..1be904dc3 --- /dev/null +++ b/httemplate/search/report_quotation.html @@ -0,0 +1,75 @@ +<& /elements/header.html, mt($title, @title_arg) &> + +
+ + + + + scalar( $cgi->param('agentnum') ), + 'label' => emt('Quotations for agent: '), + 'disable_empty' => 0, + &> +% } + + <& /elements/tr-input-beginning_ending.html &> + + <& /elements/tr-input-lessthan_greaterthan.html, + label => emt('Setup'), + field => 'total_setup', + &> + + <& /elements/tr-input-lessthan_greaterthan.html, + label => emt('Recurring'), + field => 'total_recur', + &> + +
+ +
+ + +
+ +<& /elements/footer.html &> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('List quotations'); + +my $conf = new FS::Conf; + +my $title = 'Quotation Report'; +#false laziness w/report_cust_pkg.html +my @title_arg = (); + +my $prospectnum = ''; +my $prospect_main = ''; +if ( $cgi->param('prospectnum') =~ /^(\d+)$/ ) { + $prospectnum = $1; + $prospect_main = qsearchs({ + 'table' => 'prospect_main', + 'hashref' => { 'prospectnum' => $prospectnum }, + 'extra_sql' => ' AND '. $FS::CurrentUser::CurrentUser->agentnums_sql, + }) or die "unknown prospectnum $prospectnum"; + $title .= ': [_1]'; + push @title_arg, $prospect_main->name; +} + +my $custnum = ''; +my $cust_main = ''; +if ( $cgi->param('custnum') =~ /^(\d+)$/ ) { + $custnum = $1; + $cust_main = qsearchs({ + 'table' => 'cust_main', + 'hashref' => { 'custnum' => $custnum }, + 'extra_sql' => ' AND '. $FS::CurrentUser::CurrentUser->agentnums_sql, + }) or die "unknown custnum $custnum"; + $title .= ': [_1]'; + push @title_arg, $cust_main->name; +} + + -- 2.11.0