use Data::Dumper;
use SL::DB::Helper::AccountingPeriod qw(get_balance_starting_date);
use List::Util qw(sum);
+use List::UtilsBy qw(partition_by sort_by);
use SL::DB;
# use warnings;
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
if ($form->{fromdate} || $form->{todate}) {
if ($form->{fromdate}) {
$fromdate = conv_dateq($form->{fromdate});
+ my $fiscal_year_startdate = conv_dateq($self->get_balance_starting_date($form->{fromdate}));
+ # my $date_trunc = "(select date_trunc('year', date $fromdate))";
$tofrom .= " AND (ac.transdate >= $fromdate)";
$subwhere .= " AND (ac.transdate >= $fromdate)";
- $sumsubwhere .= " AND (ac.transdate >= (select date_trunc('year', date $fromdate))) ";
- $saldosubwhere .= " AND (ac,transdate>=(select date_trunc('year', date $fromdate))) ";
- $invwhere .= " AND (a.transdate >= $fromdate)";
- $glsaldowhere .= " AND ac.transdate>=(select date_trunc('year', date $fromdate)) ";
+ $sumsubwhere .= " AND (ac.transdate >= $fiscal_year_startdate) ";
+ $saldosubwhere .= " AND (ac.transdate >= $fiscal_year_startdate) ";
+ $invwhere .= " AND (a.transdate >= $fromdate)";
+ $glsaldowhere .= " AND (ac.transdate >= $fiscal_year_startdate) ";
$glwhere = " AND (ac.transdate >= $fromdate)";
- $glsumwhere = " AND (ac.transdate >= (select date_trunc('year', date $fromdate))) ";
+ $glsumwhere = " AND (ac.transdate >= $fiscal_year_startdate) ";
}
if ($form->{todate}) {
$todate = conv_dateq($form->{todate});
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
}
- my ($debit, $credit, $saldo, $soll_saldo, $haben_saldo,$soll_kummuliert, $haben_kummuliert, $last_transaction);
+ my ($debit, $credit, $saldo, $soll_saldo, $haben_saldo, $soll_kumuliert, $haben_kumuliert, $last_transaction);
foreach my $accno (sort keys %trb) {
$ref = {};
$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();
phone as customerphone, fax as customerfax, ${ct}number,
"invnumber", "transdate",
(amount - COALESCE((SELECT sum(amount)*$ml FROM acc_trans WHERE chart_link ilike '%paid%' AND acc_trans.trans_id=${arap}.id AND acc_trans.transdate <= (date $todate)),0)) as "open", "amount",
- "duedate", invoice, ${arap}.id, date_part('days', now() - duedate) as overduedays,
+ "duedate", invoice, ${arap}.id, date_part('days', now() - duedate) as overduedays, datepaid, (amount - paid) as current_open,
(SELECT $buysell
FROM exchangerate
WHERE (${arap}.currency_id = exchangerate.currency_id)
&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);
$form, "pos_eur");
+ # add extra information to form to be used by template
+ my %charts_by_category =
+ partition_by { $_->{pos_eur} }
+ sort_by { $_->{accno} }
+ map { $form->{charts}->{$_} }
+ keys %{ $form->{charts} };
+ $form->{"charts_by_category"} = \%charts_by_category;
+
+ $form->{"categories_income"} = \@categories_einnahmen;
+ $form->{"categories_expense"} = \@categories_ausgaben;
+
+ $form->{category_names} = AM->get_eur_categories($myconfig, $form);
+
+ my %eur_amounts;
+
foreach my $item (@categories_einnahmen) {
- $form->{"eur${item}"} =
- $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2),2);
+ $eur_amounts{$item} = $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2),2);
$form->{"sumeura"} += $form->{$item};
}
foreach my $item (@categories_ausgaben) {
- $form->{"eur${item}"} =
- $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2),2);
+ $eur_amounts{$item} = $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2),2);
$form->{"sumeurb"} += $form->{$item};
}
$form->{"guvsumme"} = $form->{"sumeura"} - $form->{"sumeurb"};
+ $form->{eur_amounts} = \%eur_amounts;
+
foreach my $item (@ergebnisse) {
$form->{$item} =
$form->format_amount($myconfig, $form->round_amount($form->{$item}, 2),2);
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;