projekt kivitendo

# LX-Office ERP

# Copyright (C) 2004

# Based on SQL-Ledger Version 2.1.9

use SL::DBUtils;

use Data::Dumper;

sub balance_sheet {

$main::lxdebug->enter_sub();

my $last_period = 0;

my @categories = qw(A C L Q);

}

$form->{decimalplaces} *= 1;

# if there are any compare dates

if ($form->{compareasofdate}) {

$last_period = 1;

$form->{last_period} = conv_dateq($form->{compareasofdate});

}

# now we got $form->{A}{accno}{ } assets

# and $form->{L}{accno}{ } liabilities

# and $form->{Q}{accno}{ } equity

# build asset accounts

foreach my $category (grep { !/C/ } @categories) {

}

$account{$category}{subthis} = $form->{$category}{$key}{this};

$account{$category}{sublast} = $form->{$category}{$key}{last};

$account{$category}{subtotal} = 1;

$form->{$category}{$key}{this} = 0;

$form->{$category}{$key}{last} = 0;

next unless $form->{l_heading};

}

}

if ($last_period) {

}

}

}

}

$main::lxdebug->leave_sub();

}

sub get_accounts {

my ($null, $department_id) = split /--/, $form->{department};

my $query;

my $item;

my $sth;

my $category = qq| AND (| . join(" OR ", map({ "(c.category = " . $dbh->quote($_) . ")" } @{$categories})) . qq|) |;

$sth = prepare_execute_query($form, $dbh, $query);

my @headingaccounts = ();

$form->{ $ref->{category} }{ $ref->{accno} }{description} =

"$ref->{description}";

$form->{ $ref->{category} }{ $ref->{accno} }{charttype} = "H";

my $accno;

my $ref;

if ($ref->{category} eq 'C') {

$ref->{category} = 'A';

}

$form->{ $ref->{category} }{ $ref->{accno} }{accno} = $ref->{accno};

$form->{ $ref->{category} }{ $ref->{accno} }{charttype} = "A";

if ($last_period) {

# remove accounts with zero balance

foreach $category (@{$categories}) {

foreach $accno (keys %{ $form->{$category} }) {

delete $form->{$category}{$accno}

if ( $form->{$category}{$accno}{this} == 0

my $sth = prepare_execute_query($form, $dbh, $query);

if ($category eq "pos_bwa") {

if ($last_period) {

$form->{ $ref->{$category} }{kumm} += $ref->{amount};

}

my $acc_cash_where = "";

if ($form->{method} eq "cash") {

$acc_cash_where = qq| AND (ac.trans_id IN (SELECT id FROM ar WHERE datepaid>='$form->{fromdate}' AND datepaid<='$form->{todate}' UNION SELECT id FROM ap WHERE datepaid>='$form->{fromdate}' AND datepaid<='$form->{todate}' UNION SELECT id FROM gl WHERE transdate>='$form->{fromdate}' AND transdate<='$form->{todate}')) |;

}

# get beginning balances

$sth = prepare_execute_query($form, $dbh, $query, $form->{fromdate});

if ($ref->{amount} != 0 || $form->{all_accounts}) {

$trb{ $ref->{accno} }{description} = $ref->{description};

$sth = prepare_execute_query($form, $dbh, $query);

$trb{ $ref->{accno} }{description} = $ref->{description};

$trb{ $ref->{accno} }{charttype} = 'H';

$trb{ $ref->{accno} }{category} = $ref->{category};

$sth->finish;

$where = " 1 = 1 ";

my $tofrom;

if ($form->{fromdate} || $form->{todate}) {

if ($form->{fromdate}) {

my $fromdate = conv_dateq($form->{fromdate});

$saldosubwhere .= " AND transdate>=(select date_trunc('year', date $fromdate)) ";

}

if ($form->{todate}) {

my $todate = conv_dateq($form->{todate});

$saldosubwhere .= " AND (transdate <= $todate)";

}

}

$sth = prepare_execute_query($form, $dbh, $query);

# calculate the debit and credit in the period

$trb{ $ref->{accno} }{description} = $ref->{description};

$trb{ $ref->{accno} }{charttype} = 'A';

$trb{ $ref->{accno} }{category} = $ref->{category};

# get DR/CR

do_statement($form, $drcr, $q_drcr, $ref->{accno}, $ref->{accno}, $ref->{accno}, $ref->{accno}, $ref->{accno}, $ref->{accno});

$last_transaction = "";

while (($debit, $credit, $saldo, $haben_kumuliert, $soll_kumuliert, $last_transaction) = $drcr->fetchrow_array) {

$ref->{debit} += $debit;

}

# add subtotal

@accno = grep { $_ le "$ref->{accno}" } @headingaccounts;

$accno = pop @accno;

if ($accno) {

$dbh->disconnect;

# debits and credits for headings

foreach $ref (@{ $form->{TB} }) {

if ($accno eq $ref->{accno}) {

$ref->{debit} = $trb{$accno}{debit};

my $dpt_join;

if ($form->{department}) {

$dpt_join = qq| JOIN department d ON (a.department_id = d.id) |;

$where .= qq| AND (a.department_id = | . conv_i($department_id, 'NULL') . qq|)|;

}

while (my ($id) = $sth->fetchrow_array) {

do_statement($form, $sth_details, $q_details, $id);

$ref->{module} = ($ref->{invoice}) ? $invoice : $arap;

$ref->{exchangerate} = 1 unless $ref->{exchangerate};

push @{ $form->{AG} }, $ref;

my $sortorder = join ', ', $form->sort_columns(qw(transdate invnumber name));

$sortorder = $form->{sort} if ($form->{sort} && grep({ $_ eq $form->{sort} } qw(id transdate invnumber name netamount tax)));

if ($form->{report} !~ /nontaxable/) {

$query =

qq|SELECT a.id, '0' AS invoice, $transdate AS transdate, a.invnumber, n.name, a.netamount,

my $ml = 1;

my $arap;

if ($form->{db} eq 'ar') {

$table = 'customer';

$ml = -1;

}

$query = qq|SELECT id, accno, description FROM chart WHERE accno = ?|;

my $q_details =

qq|SELECT c.name, a.invnumber, a.ordnumber,

&get_accounts_g($dbh, $last_period, $form->{fromdate}, $form->{todate}, $form, "pos_bwa");

# if there are any compare dates

if ($form->{fromdate} || $form->{todate}) {

$last_period = 1;

if ($form->{fromdate}) {

$form->{todate} =~ /[0-9]*\.[0-9]*\.([0-9]*)/;

$year = $1;

}

&get_accounts_g($dbh, $last_period, $kummfromdate, $kummtodate, $form, "pos_bwa");

}

qw (rohertrag betriebrohertrag betriebsergebnis neutraleraufwand neutralerertrag ergebnisvorsteuern ergebnis gesamtleistung gesamtkosten);

$form->{ "$key" . "gesamtleistung" } = 0;

$form->{ "$key" . "gesamtkosten" } = 0;

'0');

}

}

$form->{ "$key" . "gesamtleistung" } += $form->{$item}{$key};

}

$form->{ "$key" . "gesamtkosten" } += $form->{$item}{$key};

}

$form->{ "$key" . "rohertrag" } =

'0');

}

}

$form->{ "$key" . "gl" . "$item" } =

$form->format_amount($myconfig,

$form->round_amount(

'0');

}

}

$form->{ "$key" . "gk" . "$item" } =

$form->format_amount($myconfig,

$form->round_amount(

'0');

}

}

$form->{ "$key" . "pk" . "$item" } =

$form->format_amount($myconfig,

$form->round_amount(

'0');

}

}

$form->{ "$key" . "auf" . "$item" } =

$form->format_amount($myconfig,

$form->round_amount(

}

}

$form->{ "$key" . "$item" } =

$form->format_amount($myconfig,

$form->round_amount($form->{ "$key" . "$item" },

my @categories_euro = qw(48 51 86 91 97 93 94);

$form->{decimalplaces} *= 1;

$form->{"$item"} = 0;

}

$form->{"$item"} = 0;

}

$form->{"65"} = $form->{"43"} - $form->{"66"};

$form->{"67"} = $form->{"43"} - $form->{"66"};

$form->{$item} =

$form->format_amount($myconfig, $form->round_amount($form->{$item}, 2),

2, '0');

}

$form->{$item} =

$form->format_amount($myconfig, $form->round_amount($form->{$item}, 0),

0, '0');

$form->{decimalplaces} *= 1;

$form->{$item} = 0;

}

$form->{$item} = 0;

}

$form->{$item} = 0;

}

&get_accounts_g($dbh, $last_period, $form->{fromdate}, $form->{todate},

$form, "pos_eur");

$form->{"eur${item}"} =

$form->format_amount($myconfig, $form->round_amount($form->{$item}, 2));

$form->{"sumeura"} += $form->{$item};

}

$form->{"eur${item}"} =

$form->format_amount($myconfig, $form->round_amount($form->{$item}, 2));

$form->{"sumeurb"} += $form->{$item};

$form->{"guvsumme"} = $form->{"sumeura"} - $form->{"sumeurb"};

$form->{$item} =

$form->format_amount($myconfig, $form->round_amount($form->{$item}, 2));

}

use SL::RP;

use SL::Iconv;

use SL::ReportGenerator;

require "bin/mozilla/arap.pl";

require "bin/mozilla/common.pl";

$form->{endbold} = "</b>";

$form->{br} = "<br>";

$form->{asofdate} = $form->current_date(\%myconfig) unless $form->{asofdate};

($form->{department}) = split /--/, $form->{department};

# define Current Earnings account

$padding = ($form->{l_heading}) ? $form->{padding} : "";

$form->{this_period} = $locale->date(\%myconfig, $form->{asofdate}, 0);

$form->{IN} = "balance_sheet.html";

$form->{templates} = $myconfig{templates};

$lxdebug->leave_sub();

}

'AR Transaction' => 'Debitorenbuchung',

'AR Transaction (abbreviation)' => 'D',

'AR Transactions' => 'Debitorenbuchungen',

'About' => '?ber',

'Abrechnungsnummer' => 'Abrechnungsnummer',

'Abteilung' => 'Abteilung',

'Auto Send?' => 'Auto. Versand?',

'Automatically created invoice for fee and interest for dunning %s' => 'Automatisch erzeugte Rechnung f?r Geb?hren und Zinsen zu Mahnung %s',

'Available qty' => 'Lagerbestand',

'BOM' => 'St?ckliste',

'BWA' => 'BWA',

'Back' => 'Zur?ck',

'ELSTER Export (Winston)' => 'ELSTER Export nach Winston',

'ELSTER Export nach Winston' => 'ELSTER Export nach Winston',

'ELSTER Tax Number' => 'ELSTER-Steuernummer',

'EU with VAT ID' => 'EU mit UstId-Nummer',

'EU without VAT ID' => 'EU ohne UstId-Nummer',

'EUER' => 'Einnahmen-/?berschussrechnung',

'Konten' => 'Konten',

'Kontonummernerweiterung (KNE)' => 'Kontonummernerweiterung (KNE)',

'L' => 'L',

'LP' => 'LP',

'LaTeX Templates' => 'LaTeX-Vorlagen',

'Landscape' => 'Querformat',

'TODO list' => 'Aufgabenliste',

'TODO list options' => 'Aufgabenlistenoptionen',

'TOP100' => 'Top 100',

'Tax' => 'Steuer',

'Tax Consultant' => 'Steuerberater/-in',

'Tax Included' => 'Steuer im Preis inbegriffen',

'create_aging_subtotal_row' => 'create_aging_subtotal_row',

'create_list_accounts_subtotal_row' => 'create_list_accounts_subtotal_row',

'create_subtotal_row' => 'create_subtotal_row',

'delivery_customer_selection' => 'delivery_customer_selection',

'e_mail' => 'e_mail',

'format_dates' => 'format_dates',