From 5e2cb6ed074776434a00757503b29224e489804b Mon Sep 17 00:00:00 2001 From: =?utf8?q?Sven=20Sch=C3=B6ling?= Date: Fri, 17 May 2013 17:12:45 +0200 Subject: [PATCH] Kundenauswahl bei Summen-/Saldenliste --- SL/RP.pm | 55 ++++++++++++++++++++++++++++--- bin/mozilla/rp.pl | 7 ++++ templates/webpages/rp/report.html | 7 ++++ 3 files changed, 65 insertions(+), 4 deletions(-) diff --git a/SL/RP.pm b/SL/RP.pm index 969e8adfb..3d7302658 100644 --- a/SL/RP.pm +++ b/SL/RP.pm @@ -748,6 +748,7 @@ sub trial_balance { my @headingaccounts = (); my $dpt_where; my $dpt_where_without_arapgl; + my ($customer_where, $customer_join, $customer_no_union); my $project; my $where = "1 = 1"; @@ -759,6 +760,11 @@ sub trial_balance { (SELECT department_id FROM gl WHERE gl.id=ac.trans_id), (SELECT department_id FROM ap WHERE ap.id=ac.trans_id)) = | . conv_i($department_id); } + if ($form->{customer_id}) { + $customer_join = qq| JOIN ar a ON (ac.trans_id = a.id) |; + $customer_where = qq| AND (a.customer_id = | . conv_i($form->{customer_id}, 'NULL') . qq|) |; + $customer_no_union = qq| AND 1=0 |; + } # project_id only applies to getting transactions # it has nothing to do with a trial balance @@ -805,8 +811,11 @@ sub trial_balance { my $min_max = $prefix eq 'from' ? 'min' : 'max'; $query = qq|SELECT ${min_max}(transdate) FROM acc_trans ac + $customer_join WHERE (1 = 1) $dpt_where_without_arapgl + $dpt_where + $customer_where $project|; ($form->{"${prefix}date"}) = selectfirst_array_query($form, $dbh, $query); } @@ -816,8 +825,11 @@ sub trial_balance { qq|SELECT c.accno, c.category, SUM(ac.amount) AS amount, c.description FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id) + $customer_join WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) AND ac.ob_transaction $dpt_where_without_arapgl + $dpt_where + $customer_where $project GROUP BY c.accno, c.category, c.description |; @@ -915,6 +927,7 @@ sub trial_balance { SELECT c.accno, c.description, c.category, SUM(ac.amount) AS amount FROM acc_trans ac JOIN chart c ON (c.id = ac.chart_id) + $customer_join WHERE $where $dpt_where_without_arapgl $project @@ -933,6 +946,7 @@ sub trial_balance { JOIN chart c ON (p.income_accno_id = c.id) WHERE $invwhere $dpt_where + $customer_where $project GROUP BY c.accno, c.description, c.category @@ -945,6 +959,7 @@ sub trial_balance { JOIN chart c ON (p.expense_accno_id = c.id) WHERE $invwhere $dpt_where + $customer_no_union $project GROUP BY c.accno, c.description, c.category |; @@ -971,8 +986,11 @@ sub trial_balance { (SELECT SUM(ac.amount) * -1 FROM acc_trans ac JOIN chart c ON (c.id = ac.chart_id) + $customer_join WHERE $where $dpt_where_without_arapgl + $dpt_where + $customer_where $project AND (ac.amount < 0) AND (c.accno = ?)) AS debit, @@ -980,41 +998,56 @@ sub trial_balance { (SELECT SUM(ac.amount) FROM acc_trans ac JOIN chart c ON (c.id = ac.chart_id) + $customer_join WHERE $where $dpt_where_without_arapgl + $dpt_where + $customer_where $project AND ac.amount > 0 AND c.accno = ?) AS credit, (SELECT SUM(ac.amount) FROM acc_trans ac JOIN chart c ON (ac.chart_id = c.id) + $customer_join WHERE $saldowhere $dpt_where_without_arapgl + $dpt_where + $customer_where $project AND c.accno = ? AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)) AS saldo, (SELECT SUM(ac.amount) FROM acc_trans ac JOIN chart c ON (ac.chart_id = c.id) + $customer_join WHERE $sumwhere $dpt_where_without_arapgl + $dpt_where + $customer_where $project - AND amount > 0 + AND ac.amount > 0 AND c.accno = ?) AS sum_credit, (SELECT SUM(ac.amount) FROM acc_trans ac JOIN chart c ON (ac.chart_id = c.id) + $customer_join WHERE $sumwhere $dpt_where_without_arapgl + $dpt_where + $customer_where $project - AND amount < 0 + AND ac.amount < 0 AND c.accno = ?) AS sum_debit, (SELECT max(ac.transdate) FROM acc_trans ac JOIN chart c ON (ac.chart_id = c.id) + $customer_join WHERE $where $dpt_where_without_arapgl + $dpt_where + $customer_where $project AND c.accno = ?) AS last_transaction @@ -1034,6 +1067,7 @@ sub trial_balance { JOIN chart c ON (p.expense_accno_id = c.id) WHERE $invwhere $dpt_where + $customer_no_union $project AND c.accno = ?) AS debit, @@ -1044,40 +1078,53 @@ sub trial_balance { JOIN chart c ON (p.income_accno_id = c.id) WHERE $invwhere $dpt_where + $customer_where $project AND c.accno = ?) AS credit, (SELECT SUM(ac.amount) FROM acc_trans ac JOIN chart c ON (ac.chart_id = c.id) + $customer_join WHERE $saldowhere $dpt_where_without_arapgl + $dpt_where + $customer_where $project AND c.accno = ? AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)) AS saldo, (SELECT SUM(ac.amount) FROM acc_trans ac JOIN chart c ON (ac.chart_id = c.id) + $customer_join WHERE $sumwhere $dpt_where_without_arapgl + $dpt_where + $customer_where $project - AND amount > 0 + AND ac.amount > 0 AND c.accno = ?) AS sum_credit, (SELECT SUM(ac.amount) FROM acc_trans ac JOIN chart c ON (ac.chart_id = c.id) + $customer_join WHERE $sumwhere + $dpt_where $dpt_where_without_arapgl + $customer_where $project - AND amount < 0 + AND ac.amount < 0 AND c.accno = ?) AS sum_debit, (SELECT max(ac.transdate) FROM acc_trans ac JOIN chart c ON (ac.chart_id = c.id) + $customer_join WHERE $where $dpt_where_without_arapgl + $dpt_where + $customer_where $project AND c.accno = ?) AS last_transaction |; diff --git a/bin/mozilla/rp.pl b/bin/mozilla/rp.pl index 860d38f5e..c2ca6939d 100644 --- a/bin/mozilla/rp.pl +++ b/bin/mozilla/rp.pl @@ -39,6 +39,7 @@ use POSIX qw(strftime); use SL::DB::Default; use SL::DB::Project; +use SL::DB::Customer; use SL::PE; use SL::RP; use SL::Iconv; @@ -140,6 +141,7 @@ sub report { ); $::form->{title} = $title{$::form->{report}}; + $::request->{layout}->add_javascripts('autocomplete_customer.js'); # get departments $::form->all_departments(\%::myconfig); @@ -630,6 +632,11 @@ sub generate_trial_balance { $form->{company} = $locale->text('Company') . " " . $defaults->company; push (@options, $form->{company}); + if ($::form->{customer_id}) { + my $customer = SL::DB::Manager::Customer->find_by(id => $::form->{customer_id}); + push @options, $::locale->text('Customer') . ' ' . $customer->displayable_name; + } + $form->{template_to} = $locale->date(\%myconfig, $form->{todate}, 0); diff --git a/templates/webpages/rp/report.html b/templates/webpages/rp/report.html index 08eacfd74..39931075a 100644 --- a/templates/webpages/rp/report.html +++ b/templates/webpages/rp/report.html @@ -67,6 +67,12 @@ [%- END %] +[%- BLOCK customer %] + + [% 'Customer' | $T8 %] + [% L.customer_picker('customer_id') %] + +[%- END %] [%- BLOCK projectnumber %] [% 'Project' | $T8 %] @@ -162,6 +168,7 @@ [%- END %] [%- IF is_trial_balance %] +[%- PROCESS customer %] [%- PROCESS projectnumber %] -- 2.20.1