FROM invoice ac
JOIN ar a ON (a.id = ac.trans_id)
JOIN parts p ON (ac.parts_id = p.id)
- JOIN chart c on (p.income_accno_id = c.id)
+ JOIN taxzone_charts t ON (p.buchungsgruppen_id = t.id)
+ JOIN chart c on (t.income_accno_id = c.id)
-- use transdate from subwhere
WHERE (c.category = 'I')
$subwhere
FROM invoice ac
JOIN ap a ON (a.id = ac.trans_id)
JOIN parts p ON (ac.parts_id = p.id)
- JOIN chart c on (p.expense_accno_id = c.id)
+ JOIN taxzone_charts t ON (p.buchungsgruppen_id = t.id)
+ JOIN chart c on (t.expense_accno_id = c.id)
WHERE (c.category = 'E')
$subwhere
$dpt_where
FROM invoice ac
JOIN ar a ON (a.id = ac.trans_id)
JOIN parts p ON (ac.parts_id = p.id)
- JOIN chart c on (p.income_accno_id = c.id)
+ JOIN taxzone_charts t ON (p.buchungsgruppen_id = t.id)
+ JOIN chart c on (t.income_accno_id = c.id)
-- use transdate from subwhere
WHERE (c.category = 'I')
$subwhere
FROM invoice ac
JOIN ap a ON (a.id = ac.trans_id)
JOIN parts p ON (ac.parts_id = p.id)
- JOIN chart c on (p.expense_accno_id = c.id)
+ JOIN taxzone_charts t ON (p.buchungsgruppen_id = t.id)
+ JOIN chart c on (t.expense_accno_id = c.id)
WHERE (c.category = 'E')
$subwhere
$dpt_where
FROM invoice ac
JOIN ar a ON (a.id = ac.trans_id)
JOIN parts p ON (ac.parts_id = p.id)
- JOIN chart c on (p.income_accno_id = c.id)
+ JOIN taxzone_charts t ON (p.buchungsgruppen_id = t.id)
+ JOIN chart c on (t.income_accno_id = c.id)
WHERE (c.category = 'I') $prwhere $dpt_where
AND ac.trans_id IN ( SELECT trans_id FROM acc_trans a WHERE (a.chart_link LIKE '%AR_paid%') $subwhere)
$project
FROM invoice ac
JOIN ap a ON (a.id = ac.trans_id)
JOIN parts p ON (ac.parts_id = p.id)
- JOIN chart c on (p.expense_accno_id = c.id)
+ JOIN taxzone_charts t ON (p.buchungsgruppen_id = t.id)
+ JOIN chart c on (t.expense_accno_id = c.id)
WHERE (c.category = 'E') $prwhere $dpt_where
AND ac.trans_id IN ( SELECT trans_id FROM acc_trans a WHERE (a.chart_link LIKE '%AP_paid%') $subwhere)
$project
FROM invoice ac
JOIN ar a ON (a.id = ac.trans_id)
JOIN parts p ON (ac.parts_id = p.id)
- JOIN chart c on (p.income_accno_id = c.id)
+ JOIN taxzone_charts t ON (p.buchungsgruppen_id = t.id)
+ JOIN chart c on (t.income_accno_id = c.id)
WHERE (c.category = 'I')
$prwhere
$dpt_where
FROM invoice ac
JOIN ap a ON (a.id = ac.trans_id)
JOIN parts p ON (ac.parts_id = p.id)
- JOIN chart c on (p.expense_accno_id = c.id)
+ JOIN taxzone_charts t ON (p.buchungsgruppen_id = t.id)
+ JOIN chart c on (t.expense_accno_id = c.id)
WHERE (c.category = 'E')
$prwhere
$dpt_where
FROM invoice ac
JOIN ar a ON (ac.trans_id = a.id)
JOIN parts p ON (ac.parts_id = p.id)
- JOIN chart c ON (p.income_accno_id = c.id)
+ JOIN taxzone_charts t ON (p.buchungsgruppen_id = t.id)
+ JOIN chart c ON (t.income_accno_id = c.id)
WHERE $invwhere
$dpt_where
$customer_where
FROM invoice ac
JOIN ap a ON (ac.trans_id = a.id)
JOIN parts p ON (ac.parts_id = p.id)
- JOIN chart c ON (p.expense_accno_id = c.id)
+ JOIN taxzone_charts t ON (p.buchungsgruppen_id = t.id)
+ JOIN chart c ON (t.expense_accno_id = c.id)
WHERE $invwhere
$dpt_where
$customer_no_union
FROM invoice ac
JOIN parts p ON (ac.parts_id = p.id)
JOIN ap a ON (ac.trans_id = a.id)
- JOIN chart c ON (p.expense_accno_id = c.id)
+ JOIN taxzone_charts t ON (p.buchungsgruppen_id = t.id)
+ JOIN chart c ON (t.expense_accno_id = c.id)
WHERE $invwhere
$dpt_where
$customer_no_union
FROM invoice ac
JOIN parts p ON (ac.parts_id = p.id)
JOIN ar a ON (ac.trans_id = a.id)
- JOIN chart c ON (p.income_accno_id = c.id)
+ JOIN taxzone_charts t ON (p.buchungsgruppen_id = t.id)
+ JOIN chart c ON (t.income_accno_id = c.id)
WHERE $invwhere
$dpt_where
$customer_where
$main::lxdebug->leave_sub();
}
-sub get_storno {
- $main::lxdebug->enter_sub();
- my ($self, $dbh, $form) = @_;
- my $arap = $form->{arap} eq "ar" ? "ar" : "ap";
- my $query = qq|SELECT invnumber FROM $arap WHERE invnumber LIKE "Storno zu "|;
- my $sth = $dbh->prepare($query);
- while(my $ref = $sth->fetchrow_hashref()) {
- $ref->{invnumer} =~ s/Storno zu //g;
- $form->{storno}{$ref->{invnumber}} = 1;
- }
- $main::lxdebug->leave_sub();
-}
-
sub aging {
$main::lxdebug->enter_sub();
&get_accounts_g($dbh, $last_period, $kummfromdate, $kummtodate, $form, "pos_bwa");
}
+ my %charts_by_category =
+ partition_by { $_->{pos_bwa} }
+ sort_by { $_->{accno} }
+ map { $form->{charts}->{$_} }
+ keys %{ $form->{charts} };
+ $form->{"charts_by_category"} = \%charts_by_category;
+
+ $form->{category_names} = AM->get_bwa_categories($myconfig, $form);
+
my @periods = qw(jetzt kumm);
my @gesamtleistung = qw(1 3);
my @gesamtkosten = qw (10 11 12 13 14 15 16 17 18 20);
my %categories = (I => "ERTRAG", E => "AUFWAND");
my $fromdate = conv_dateq($form->{fromdate});
my $todate = conv_dateq($form->{todate});
+ my $department_id = conv_i((split /--/, $form->{department})[1], 'NULL');
$form->{total} = 0;
my %category = (
name => $categories{$category},
total => 0,
- accounts => get_accounts_ch($category),
+ accounts => get_accounts_ch($category)
);
foreach my $account (@{$category{accounts}}) {
- $account->{total} += get_total_ch($account->{id}, $fromdate, $todate);
+ $account->{total} = get_total_ch($department_id, $account->{id}, $fromdate, $todate);
$category{total} += $account->{total};
- $account->{total} = $form->format_amount($myconfig, $form->parse_amount($myconfig, $account->{total}), 2);
+ $account->{total} = $form->format_amount($myconfig, $form->round_amount($account->{total}, 2), 2);
}
$form->{total} += $category{total};
- $category{total} = $form->format_amount($myconfig, $form->parse_amount($myconfig, $category{total}), 2);
+ $category{total} = $form->format_amount($myconfig, $form->round_amount($category{total}, 2), 2);
push(@{$form->{categories}}, \%category);
}
- $form->{total} = $form->format_amount($myconfig, $form->parse_amount($myconfig, $form->{total}), 2);
+ $form->{total} = $form->format_amount($myconfig, $form->round_amount($form->{total}, 2), 2);
$main::lxdebug->leave_sub();
return {};
$main::lxdebug->enter_sub();
my ($category) = @_;
- my ($inclusion);
+ my $inclusion = '' ;
if ($category eq 'I') {
$inclusion = "AND pos_er = NULL OR pos_er = '1'";
sub get_total_ch {
$main::lxdebug->enter_sub();
- my ($chart_id, $fromdate, $todate) = @_;
+ my ($department_id, $chart_id, $fromdate, $todate) = @_;
my $total = 0;
my $query = qq|
SELECT SUM(amount)
AND transdate >= ?
AND transdate <= ?
|;
- $total += _query($query, $chart_id, $fromdate, $todate)->[0]->{sum};
+ if ($department_id) {
+ $query .= qq| AND COALESCE(
+ (SELECT department_id FROM ar WHERE ar.id=trans_id),
+ (SELECT department_id FROM gl WHERE gl.id=trans_id),
+ (SELECT department_id FROM ap WHERE ap.id=trans_id)
+ ) = ? |;
+ $total += _query($query, $chart_id, $fromdate, $todate, $department_id)->[0]->{sum};
+ } else {
+ $total += _query($query, $chart_id, $fromdate, $todate)->[0]->{sum};
+ }
$main::lxdebug->leave_sub();
return $total;