sub all_accounts {
$main::lxdebug->enter_sub();
- my ($self, $myconfig, $form) = @_;
+ my ($self, $myconfig, $form, $chart_id) = @_;
my %amount;
}
$sth->finish;
- $query =
- qq!SELECT c.accno, c.id, c.description, c.charttype, c.category, ! .
- qq! c.link, c.pos_bwa, c.pos_bilanz, c.pos_eur, c.valid_from, ! .
- qq! c.datevautomatik, comma(tk.startdate) AS startdate, ! .
- qq! comma(tk.taxkey_id) AS taxkey, ! .
- qq! comma(tx.taxdescription || to_char (tx.rate, '99V99' ) || '%') ! .
- qq! AS taxdescription, ! .
- qq! comma(tx.taxnumber) AS taxaccount, comma(tk.pos_ustva) ! .
- qq! AS tk_ustva, ! .
- qq! ( SELECT accno FROM chart c2 WHERE c2.id = c.id ) AS new_account ! .
- qq!FROM chart c ! .
- qq!LEFT JOIN taxkeys tk ON (c.id = tk.chart_id) ! .
- qq!LEFT JOIN tax tx ON (tk.tax_id = tx.id) ! .
- qq!GROUP BY c.accno, c.id, c.description, c.charttype, ! .
- qq! c.category, c.link, c.pos_bwa, c.pos_bilanz, c.pos_eur, ! .
- qq! c.valid_from, c.datevautomatik ! .
- qq!ORDER BY c.accno!;
+ my $where = "AND c.id = $chart_id" if ($chart_id ne '');
+
+ $query = qq{
+ SELECT
+ c.accno,
+ c.id,
+ c.description,
+ c.charttype,
+ c.category,
+ c.link,
+ c.pos_bwa,
+ c.pos_bilanz,
+ c.pos_eur,
+ c.valid_from,
+ c.datevautomatik,
+ comma(tk.startdate) AS startdate,
+ comma(tk.taxkey_id) AS taxkey,
+ comma(tx.taxdescription || to_char (tx.rate, '99V99' ) || '%') AS taxdescription,
+ comma(tx.taxnumber) AS taxaccount,
+ comma(tk.pos_ustva) AS tk_ustva,
+ ( SELECT accno
+ FROM chart c2
+ WHERE c2.id = c.id
+ ) AS new_account
+ FROM chart c
+ LEFT JOIN taxkeys tk ON (c.id = tk.chart_id)
+ LEFT JOIN tax tx ON (tk.tax_id = tx.id)
+ WHERE 1=1
+ $where
+ GROUP BY c.accno, c.id, c.description, c.charttype, c.gifi_accno,
+ c.category, c.link, c.pos_bwa, c.pos_bilanz, c.pos_eur, c.valid_from,
+ c.datevautomatik
+ ORDER BY c.accno
+ };
+
my $sth = prepare_execute_query($form, $dbh, $query);
$form->{CA} = [];
+
while (my $ca = $sth->fetchrow_hashref(NAME_lc)) {
$ca->{amount} = $amount{ $ca->{accno} };
if ($ca->{amount} < 0) {
@values = ();
foreach my $id (@id) {
-
+
# NOTE: Postgres is really picky about the order of implicit CROSS
# JOINs with ',' if you alias the tables and want to use the
# alias later in another JOIN. the alias you want to use has to
$dpt_join .
qq|WHERE | . $where . $dpt_where . $project .
qq| AND ac.chart_id = ? | .
- qq| AND ac.trans_id = a.id | .
qq|UNION | .
qq|FROM acc_trans ac, customer c, ar a | .
$dpt_join .
qq|WHERE | . $where . $dpt_where . $project .
- qq| AND ac.chart_id = ? | .
- qq| AND ac.trans_id = a.id | .
- qq| AND a.customer_id = c.id | .
+ qq| AND ac.chart_id = ? | .
+ qq| AND NOT a.storno | .
+ qq| AND a.customer_id = c.id | .
qq|UNION | .
qq|FROM acc_trans ac, vendor v, ap a | .
$dpt_join .
qq|WHERE | . $where . $dpt_where . $project .
- qq| AND ac.chart_id = ? | .
- qq| AND ac.trans_id = a.id | .
- qq| AND a.vendor_id = v.id |;
+ qq| AND ac.chart_id = ? | .
+ qq| AND NOT a.storno | .
+ qq| AND a.vendor_id = v.id |;
push(@values,
@where_values, @department_values, @project_values, $id,
$union = qq|UNION ALL|;
}
- $query .= qq|ORDER BY | . $sortorder;
+ $query .= qq|ORDER BY | . $form->{sort};
$sth = prepare_execute_query($form, $dbh, $query, @values);
$form->{CA} = [];