From: ivan Date: Sun, 15 Aug 2010 07:00:21 +0000 (+0000) Subject: additional by-otaker searches fixed for the brave new world of usernum, RT#9555 X-Git-Tag: TORRUS_1_0_9~392 X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=commitdiff_plain;h=55476aa0484f5ebada5f36e1407722d84609bd34 additional by-otaker searches fixed for the brave new world of usernum, RT#9555 --- diff --git a/httemplate/elements/select-user.html b/httemplate/elements/select-user.html index 6264398e2..ec2341be6 100644 --- a/httemplate/elements/select-user.html +++ b/httemplate/elements/select-user.html @@ -4,7 +4,10 @@ % } -% foreach my $usernum ( keys %{ $opt{'access_user'} } ) { +% foreach my $usernum ( +% sort { $opt{'access_user'}->{$a} cmp $opt{'access_user'}->{$b} } +% keys %{ $opt{'access_user'} } +% ) { % } @@ -16,12 +19,9 @@ my %opt = @_; unless ( $opt{'access_user'} ) { - tie %{ $opt{'access_user'} }, 'Tie::IxHash'; - my $sth = dbh->prepare(" SELECT usernum, username FROM access_user WHERE disabled = '' or disabled IS NULL - ORDER BY username ") or die dbh->errstr; $sth->execute or die $sth->errstr; while ( my $row = $sth->fetchrow_arrayref ) { diff --git a/httemplate/search/cust_bill_pkg_discount.html b/httemplate/search/cust_bill_pkg_discount.html index 088b29115..b472366be 100644 --- a/httemplate/search/cust_bill_pkg_discount.html +++ b/httemplate/search/cust_bill_pkg_discount.html @@ -85,6 +85,11 @@ if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { push @where, "cust_main.agentnum = $1"; } +#usernum +if ( $cgi->param('usernum') =~ /^(\d+)$/ ) { + push @where, "cust_pkg_discount.usernum = $1"; +} + # #classnum # # not specified: all classes # # 0: empty class @@ -110,18 +115,25 @@ if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { my $count_query = "SELECT COUNT(*), SUM(amount)"; -my $join_cust = ' JOIN cust_bill_pkg USING ( billpkgnum ) - JOIN cust_bill USING ( invnum ) - LEFT JOIN cust_main USING ( custnum ) '; +my $join_cust_pkg_discount = + 'LEFT JOIN cust_pkg_discount USING (pkgdiscountnum)'; + +my $join_cust = + ' JOIN cust_bill_pkg USING ( billpkgnum ) + JOIN cust_bill USING ( invnum ) + LEFT JOIN cust_main USING ( custnum ) '; -my $join_pkg = ' LEFT JOIN cust_pkg USING ( pkgnum ) - LEFT JOIN part_pkg USING ( pkgpart ) '; - #LEFT JOIN part_pkg AS override - # ON pkgpart_override = override.pkgpart '; +my $join_pkg = + ' LEFT JOIN cust_pkg ON ( cust_bill_pkg.pkgnum = cust_pkg.pkgnum ) + LEFT JOIN part_pkg USING ( pkgpart ) '; + #LEFT JOIN part_pkg AS override + # ON pkgpart_override = override.pkgpart '; my $where = ' WHERE '. join(' AND ', @where); -$count_query .= " FROM cust_bill_pkg_discount $join_cust $join_pkg $where"; +$count_query .= + " FROM cust_bill_pkg_discount $join_cust_pkg_discount $join_cust $join_pkg ". + $where; my @select = ( 'cust_bill_pkg_discount.*', @@ -135,7 +147,7 @@ push @select, 'cust_main.custnum', my $query = { 'table' => 'cust_bill_pkg_discount', - 'addl_from' => "$join_cust $join_pkg", + 'addl_from' => "$join_cust_pkg_discount $join_cust $join_pkg", 'hashref' => {}, 'select' => join(', ', @select ), 'extra_sql' => $where, diff --git a/httemplate/search/cust_credit.html b/httemplate/search/cust_credit.html index 9a14dceca..ad8472134 100755 --- a/httemplate/search/cust_credit.html +++ b/httemplate/search/cust_credit.html @@ -56,8 +56,8 @@ my $title = 'Credit Search Results'; my @search = (); -if ( $cgi->param('otaker') && $cgi->param('otaker') =~ /^([\w\.\-]+)$/ ) { - push @search, "cust_credit.otaker = '$1'"; +if ( $cgi->param('usernum') =~ /^(\d+)$/ ) { + push @search, "cust_credit.usernum = $1"; } if ( $cgi->param('agentnum') && $cgi->param('agentnum') =~ /^(\d+)$/ ) { diff --git a/httemplate/search/cust_pkg_discount.html b/httemplate/search/cust_pkg_discount.html index 233345e1c..d70c3116f 100644 --- a/httemplate/search/cust_pkg_discount.html +++ b/httemplate/search/cust_pkg_discount.html @@ -78,9 +78,9 @@ if ( $cgi->param('status') eq 'active' ) { "; #XXX also end date } -#otaker -if ( $cgi->param('otaker') && $cgi->param('otaker') =~ /^([\w\.\-]+)$/ ) { - push @where, "cust_pkg_discount.otaker = '$1'"; +#usernum +if ( $cgi->param('usernum') =~ /^(\d+)$/ ) { + push @where, "cust_pkg_discount.usernum = $1"; } #agent diff --git a/httemplate/search/report_cust_bill_pkg_discount.html b/httemplate/search/report_cust_bill_pkg_discount.html index f1879d4a9..f9ab901b5 100644 --- a/httemplate/search/report_cust_bill_pkg_discount.html +++ b/httemplate/search/report_cust_bill_pkg_discount.html @@ -5,9 +5,9 @@ - <% include( '/elements/tr-select-otaker.html', - 'label' => 'Discounts by employee: ', - 'otakers' => \@otakers, + <% include( '/elements/tr-select-user.html', + 'label' => 'Discounts by employee: ', + 'access_user' => \%access_user, ) %> @@ -39,9 +39,12 @@ die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); -my $sth = dbh->prepare("SELECT DISTINCT otaker FROM cust_pkg_discount") +my $sth = dbh->prepare("SELECT DISTINCT usernum FROM cust_pkg_discount") or die dbh->errstr; $sth->execute or die $sth->errstr; -my @otakers = map { $_->[0] } @{$sth->fetchall_arrayref}; +my @usernum = map $_->[0], @{$sth->fetchall_arrayref}; +my %access_user = + map { $_ => qsearchs('access_user',{'usernum'=>$_})->username } + @usernum; diff --git a/httemplate/search/report_cust_credit.html b/httemplate/search/report_cust_credit.html index 9c719b787..c7e552238 100644 --- a/httemplate/search/report_cust_credit.html +++ b/httemplate/search/report_cust_credit.html @@ -5,9 +5,9 @@
- <% include( '/elements/tr-select-otaker.html', - 'label' => 'Credits by employee: ', - 'otakers' => \@otakers, + <% include( '/elements/tr-select-user.html', + 'label' => 'Credits by employee: ', + 'access_user' => \%access_user, ) %> @@ -40,9 +40,13 @@ die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); -my $sth = dbh->prepare("SELECT DISTINCT otaker FROM cust_credit") +my $sth = dbh->prepare("SELECT DISTINCT usernum FROM cust_credit") or die dbh->errstr; $sth->execute or die $sth->errstr; -my @otakers = map { $_->[0] } @{$sth->fetchall_arrayref}; +my @usernum = map $_->[0], @{$sth->fetchall_arrayref}; +my %access_user = + map { $_ => qsearchs('access_user',{'usernum'=>$_})->username } + @usernum; + diff --git a/httemplate/search/report_cust_pkg_discount.html b/httemplate/search/report_cust_pkg_discount.html index 7ebd44f75..31774c384 100644 --- a/httemplate/search/report_cust_pkg_discount.html +++ b/httemplate/search/report_cust_pkg_discount.html @@ -16,9 +16,9 @@ - <% include( '/elements/tr-select-otaker.html', - 'label' => 'Discounts by employee: ', - 'otakers' => \@otakers, + <% include( '/elements/tr-select-user.html', + 'label' => 'Discounts by employee: ', + 'access_user' => \%access_user, ) %> @@ -42,9 +42,12 @@ die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); -my $sth = dbh->prepare("SELECT DISTINCT otaker FROM cust_pkg_discount") +my $sth = dbh->prepare("SELECT DISTINCT usernum FROM cust_pkg_discount") or die dbh->errstr; $sth->execute or die $sth->errstr; -my @otakers = map { $_->[0] } @{$sth->fetchall_arrayref}; +my @usernum = map $_->[0], @{$sth->fetchall_arrayref}; +my %access_user = + map { $_ => qsearchs('access_user',{'usernum'=>$_})->username } + @usernum; diff --git a/httemplate/search/report_h_cust_pay.html b/httemplate/search/report_h_cust_pay.html index fe7c4a9fa..5c7f27afb 100644 --- a/httemplate/search/report_h_cust_pay.html +++ b/httemplate/search/report_h_cust_pay.html @@ -88,7 +88,7 @@ ) %> - <% include( '/elements/tr-select-otaker.html' ) %> + <% include( '/elements/tr-select-user.html' ) %>
Payment