projekt kivitendo

package SL::Controller::ChartOfAccounts;

use strict;

use parent qw(SL::Controller::Base);

use POSIX qw(strftime);

use SL::CA;

use SL::ReportGenerator;

use SL::Controller::Helper::ReportGenerator;

use SL::Locale::String;

use Rose::Object::MakeMethods::Generic (

  scalar                  => [ qw(report) ],

);

__PACKAGE__->run_before(sub { $::auth->assert('report'); });

sub action_list {

  my ($self) = @_;

  if ( $::instance_conf->get_accounting_method eq 'cash' ) {

    $::form->{method} = "cash";

  }

  $self->prepare_report;

  $self->set_report_data;

  $self->report->generate_with_headers;

}

# private functions

sub prepare_report {

  my ($self) = @_;

  $self->report(SL::ReportGenerator->new(\%::myconfig, $::form));

  my @columns     = qw(accno description debit credit);

  my %column_defs = (

    accno       => { text => t8('Account') },

    description => { text => t8('Description') },

    debit       => { text => t8('Debit') },

    credit      => { text => t8('Credit') },

  );

  $self->report->set_options(

    std_column_visibility => 1,

    controller_class      => 'ChartOfAccounts',

    output_format         => 'HTML',

    title                 => t8('Chart of Accounts'),

    allow_pdf_export      => 1,

    allow_csv_export      => 1,

    attachment_basename   => t8('chart_of_accounts') . strftime('_%Y%m%d', localtime time),

  );

  $self->report->set_columns(%column_defs);

  $self->report->set_column_order(@columns);

  $self->report->set_export_options(qw(list));

  $self->report->set_options_from_form;

  $self->report->set_sort_indicator($::form->{sort}, 1);

}

sub set_report_data {

  my ($self) = @_;

  my $debit_sum = 0.;

  my $credit_sum = 0.;

  # i tried to use the get_balance function from SL::DB::Manager::Chart here,

  # but the results i got were different (numbers and defined balance/amount),

  # the database queries in CA are more sophisticated, therefore i'm still using these for now,

  # also performance wise they seem faster

  CA->all_accounts(\%::myconfig, \%$::form);

  my $formatted_zero = $::form->format_amount(\%::myconfig, 0., 2);

  for my $chart (@{ $::form->{CA} }) {

    my $balance = $chart->{amount};

    my $link = "controller.pl?action=ListTransactions%2freport_settings&accno=$chart->{accno}&link=1";

    if (defined($balance)) {

      my %data = (

        accno       => { data => $chart->{accno}, link => $link },

        description => { data => $chart->{description} },

        debit       => { data => $balance < 0 ? $::form->format_amount(\%::myconfig, $balance * -1., 2) : ''},

        credit      => { data => $balance >= 0 ? $::form->format_amount(\%::myconfig, $balance, 2) : ''},

      );

      $data{$_}->{align} = 'right' for qw(debit credit);

      map { $data{$_}->{class} = 'listheading' } keys %data if ($chart->{charttype} eq "H") ;

      $self->report->add_data(\%data);

      if ($balance < 0) {

        $debit_sum += $balance;

      } else {

        $credit_sum += $balance;

      }

    }

  }

  my %data_total = (

    accno       => { data => t8('Total') },

    description => { data => '' },

    debit       => { data => $::form->format_amount(\%::myconfig, $debit_sum * -1., 2)},

    credit      => { data => $::form->format_amount(\%::myconfig, $credit_sum, 2)},

  );

  %data_total{$_}->{align} = 'right' for qw(debit credit);

  %data_total{$_}->{class} = 'listtotal' for keys %data_total;

  $self->report->add_data(\%data_total);

}

1;

package SL::Controller::ListTransactions;

use strict;

use parent qw(SL::Controller::Base);

use POSIX qw(strftime);

use List::Util qw(first);

use Archive::Zip;

use SL::DB::Chart;

use SL::DB::AccTransaction;

use SL::CA;

use SL::ReportGenerator;

use SL::Controller::Helper::ReportGenerator;

use SL::Locale::String;

use SL::SessionFile::Random;

use SL::Helper::Flash qw(flash);

use SL::Presenter::DatePeriod qw(get_dialog_defaults_from_report_generator

                                  populate_hidden_variables);

use Rose::Object::MakeMethods::Generic (

  scalar                  => [ qw(defaults title account from_date to_date report_type report) ],

  'scalar --get_set_init' => [ qw(accounts_list) ],

);

__PACKAGE__->run_before(sub { $::auth->assert('report'); });

# actions

sub action_report_settings {

  my ($self) = @_;

  $self->set_defaults;

  # if we're coming from a linked entry we want to pre-select the chart picker

  if ($::form->{link}) {

    my $account = first { $_->{accno} eq $::form->{accno} } @{ $self->accounts_list };

    $self->defaults->{chart_id} = $account->{chart_id};

  }

  $self->setup_report_settings_action_bar;

  $self->render('list_transactions/report_settings',

    title => t8('List Transactions'),

    accounts_list => $self->accounts_list,

    defaults => $self->defaults,

  );

}

sub action_list {

  my ($self) = @_;

  $self->set_dates;

  $self->report_type('HTML');

  # set account number from chart picker chart_id

  my $account = first { $_->{chart_id} eq $::form->{chart_id} } @{ $self->accounts_list };

  if (!$account) {

    flash('error', t8('No account selected. Please select an account.'));

    return $self->action_report_settings;

  }

  $::form->{accno} = $account->{accno};

  $self->set_title;

  $self->setup_list_action_bar;

  $self->prepare_report;

  $self->set_report_data;

  $self->report->generate_with_headers;

}

sub action_csv_options_export_all_charts {

  my ($self) = @_;

  $self->set_dates;

  # misusing the set_defaults function here a bit to easily

  # get the values from the form,

  # we have to get the values here because we have to forward them

  # to the csv options form using a hidden array

  $self->set_defaults;

  $self->defaults->{fromdate} = $self->from_date;

  $self->defaults->{todate} = $self->to_date;

  # dialog state is returned in a nested hash, this has to be flattened here

  my @hidden;

  push @hidden, map {

    { key => 'dateperiod_selected_preset_' . $_, value => $self->defaults->{dialog}->{$_} }

  } keys %{ $self->defaults->{dialog} };

  delete($self->defaults->{dialog});

  # handle the rest of the state

  push @hidden, map {

    { key => $_, value => $self->defaults->{$_} }

  } keys %{ $self->defaults };

  $self->setup_csv_options_action_bar;

  $self->render('report_generator/csv_export_options',

    title => t8('CSV export -- options'),

    HIDDEN => \@hidden,

  );

}

sub action_export_all_charts {

  my ($self) = @_;

  my $zip = Archive::Zip->new();

  for my $account (@{ $self->accounts_list }) {

    $::form->{accno} = $account->{accno};

    my $sfile = SL::SessionFile::Random->new(mode => "w");

    $self->set_title;

    $self->report_type('CSV');

    $self->prepare_report;

    $self->set_report_data;

    $self->report->_generate_csv_content($sfile->fh);

    $sfile->fh->close;

    $zip->addFile(

      $sfile->{file_name},

      t8('list_of_transactions') . "_" . t8('account') . "_" . $account->{accno} . ".csv"

    );

  }

  my $zipfile = SL::SessionFile::Random->new(mode => "w");

  unless ( $zip->writeToFileNamed($zipfile->file_name) == Archive::Zip::AZ_OK ) {

    die 'zipfile write error';

  }

  $zipfile->fh->close;

  $self->send_file(

    $zipfile->file_name,

    type => 'application/zip',

    name => t8('list_of_transactions') . strftime('_%Y%m%d', localtime time) . '.zip',

  );

}

# local functions

sub set_defaults {

  my ($self) = @_;

  # use values from form, then report generator form, then fallback

  my %fallback = (

    #accno         => $self->accounts_list->[0]->{accno},

    chart_id      => '',

    reporttype    => 'custom',

    year          => DateTime->today->year,

    duetyp        => '13',

    dateperiod_from_date => '',

    dateperiod_to_date => '',

    show_subtotals => 0,

    sort          => 'transdate',

  );

  my %defaults;

  for (keys %fallback) {

    $defaults{$_} = $::form->{$_} // $::form->{'report_generator_hidden_' . $_} // $fallback{$_};

  }

  $defaults{dialog} = get_dialog_defaults_from_report_generator('dateperiod');

  $self->defaults(\%defaults);

}

sub set_title {

  my ($self) = @_;

  my $account = first { $_->{accno} eq $::form->{accno} } @{ $self->accounts_list };

  $self->title(join(" ", t8('List Transactions'), t8('Account'), $account->{text}));

}

sub set_dates {

  my ($self) = @_;

  # set dates according to selection

  $self->from_date($::form->{dateperiod_from_date});

  $self->to_date($::form->{dateperiod_to_date});

  # set this into form here for the CA-> routines

  $::form->{fromdate} = $self->from_date;

  $::form->{todate}   = $self->to_date;

  # (no further checks needed, a reasonable error is shown when dates are invalid)

}

sub prepare_report {

  my ($self) = @_;

  $self->report(SL::ReportGenerator->new(\%::myconfig, $::form));

  my @columns     = qw(transdate reference description gegenkonto debit credit ustkonto ustrate balance);

  my %column_defs = (

    transdate   => { text => t8('Date'), },

    reference   => { text => t8('Reference'), },

    description => { text => t8('Description'), },

    debit       => { text => t8('Debit'), },

    credit      => { text => t8('Credit'), },

    gegenkonto  => { text => t8('Gegenkonto'), },

    ustkonto    => { text => t8('USt-Konto'), },

    balance     => { text => t8('Balance'), },

    ustrate     => { text => t8('Satz %'), },

  );

  $self->report->set_options(

    std_column_visibility => 1,

    controller_class      => 'ListTransactions',

    output_format         => $self->report_type,

    title                 => $self->title,

    allow_pdf_export      => 1,

    allow_csv_export      => 1,

    attachment_basename   => t8('list_of_transactions') . strftime('_%Y%m%d', localtime time),

    top_info_text         => $self->get_top_info_text,

  );

  $self->report->set_columns(%column_defs);

  $self->report->set_column_order(@columns);

  my @hidden_variables = qw(accno chart_id show_subtotals sort);

  populate_hidden_variables('dateperiod', \@hidden_variables);

  $self->report->set_export_options(qw(list), @hidden_variables);

  $self->report->set_options_from_form;

  $self->report->set_sort_indicator($::form->{sort}, 1);

  # this is getting triggered but doesn't seem to have an effect

  #$::locale->set_numberformat_wo_thousands_separator(\%::myconfig) if lc($self->report->{options}->{output_format}) eq 'csv';

}

sub set_report_data {

  my ($self) = @_;

  CA->all_transactions(\%::myconfig, \%$::form);

  # this data is used in custom header

  $self->{eb_value} = $::form->{beginning_balance};

  $self->{saldo_old} += $::form->{beginning_balance};

  $self->{debit_old} += $::form->{beginning_balance};

  $self->{credit_old} += $::form->{beginning_balance};

  $self->set_report_custom_headers();

  # initialise totals

  $self->{total_debit} = 0.;

  $self->{total_credit} = 0.;

  my $subtotal_debit = 0.;

  my $subtotal_credit = 0.;

  $self->{balance} = $self->{saldo_old};

  # used for subtotals below

  my $sort_key = $::form->{sort};

  my $idx = 0;

  for my $tr (@{ $::form->{CA} }) {

    # sum up totals

    $self->{total_debit} += $tr->{debit};

    $self->{total_credit} += $tr->{credit};

    $subtotal_debit += $tr->{debit};

    $subtotal_credit += $tr->{credit};

    $self->{balance} -= $tr->{debit};

    $self->{balance} += $tr->{credit};

    # formatting

    my $credit = $tr->{credit} ? $::form->format_amount(\%::myconfig, $tr->{credit}, 2) : '0';

    my $debit = $tr->{debit} ? $::form->format_amount(\%::myconfig, $tr->{debit}, 2) : '0';

    my $ustrate = $::form->format_amount(\%::myconfig, $tr->{ustrate} * 100, 2) if ($tr->{ustrate} != 0);

    my $gegenkonto_string = "";

    foreach my $gegenkonto (@{ $tr->{GEGENKONTO} }) {

      if ($gegenkonto_string eq "") {

        $gegenkonto_string = $gegenkonto->{accno};

      } else {

        $gegenkonto_string .= ", " . $gegenkonto->{accno};

      }

    }

    my $reference_link = "$tr->{module}.pl?action=edit&id=$tr->{id}";

    my %data = (

      transdate   => { data => $tr->{transdate}, },

      reference   => { data => $tr->{reference}, link => $reference_link },

      description => { data => $tr->{description}, },

      gegenkonto  => { data => $gegenkonto_string, },

      debit       => { data => $debit },

      credit      => { data => $credit },

      ustkonto    => { data => $tr->{ustkonto}, },

      ustrate     => { data => $ustrate },

      balance     => { data => $::form->format_amount(\%::myconfig, $self->{balance}, 2, 'DRCR') },

    );

    $data{$_}->{align} = 'right' for qw(debit credit ustkonto ustrate balance);

    # use a row set here in order to keep the table coloring intact

    my @row_set;

    push @row_set, \%data;

    # show subtotals if setting enabled and ( last element reached or

    # next element has a different value in the field selected by sort key )

    if ( ($::form->{show_subtotals}) &&

      ( ($idx == scalar @{ $::form->{CA} } - 1) ||

        ($tr->{$sort_key} ne $::form->{CA}->[$idx + 1]->{$sort_key}) ) ) {

      my %data = map { $_ => { class => 'listtotal' } } keys %{ $self->report->{columns} };

      $data{credit}->{data} = $::form->format_amount(\%::myconfig, $subtotal_credit, 2);

      $data{debit}->{data} = $::form->format_amount(\%::myconfig, $subtotal_debit, 2);

      push @row_set, \%data;

      $subtotal_credit = 0.;

      $subtotal_debit = 0.;

    }

    $self->report->add_data(\@row_set);

    $idx++;

  }

  # debit credit and balance totals line

  my %data = map { $_ => { class => 'listtotal' } } keys %{ $self->report->{columns} };

  $data{credit}->{data} = $::form->format_amount(\%::myconfig, $self->{total_credit}, 2);

  $data{debit}->{data} = $::form->format_amount(\%::myconfig, $self->{total_debit}, 2);

  $data{balance}->{data} = $::form->format_amount(\%::myconfig, $self->{balance}, 2, 'DRCR');

  $data{$_}->{align} = 'right' for qw(debit credit balance);

  $self->report->add_data(\%data);

  $self->set_report_footer_lines();

}

sub set_report_footer_lines {

  my ($self) = @_;

  # line 1

  my %data = map { $_ => { class => 'listtotal' } } keys %{ $self->report->{columns} };

  $data{reference}->{data} = t8('EB-Wert');

  $data{description}  = { data => t8('Saldo neu'), class => 'listtotal', colspan => 2 };

  $data{debit}        = { data => t8('Jahresverkehrszahlen neu'), class => 'listtotal', colspan => 2 };

  $self->report->add_data(\%data);

  # line 2

  my %data2 = map { $_ => { class => 'listtotal' } } keys %{ $self->report->{columns} };

  $data2{reference}->{data} = format_debit_credit($self->{eb_value});

  $data2{description} = { data => format_debit_credit($self->{balance}), class => 'listtotal', colspan => 2 };

  $data2{debit}->{data} = $::form->format_amount(\%::myconfig, abs($self->{total_debit}), 2) . " S";

  $data2{credit}->{data} = $::form->format_amount(\%::myconfig, $self->{total_credit}, 2) . " H";

  $self->report->add_data(\%data2);

}

sub set_report_custom_headers {

  my ($self) = @_;

  my @custom_headers = ();

  # line 1

  push @custom_headers, [

    { text => t8('Letzte Buchung'), },

    { text => t8('EB-Wert'), },

    { text => t8('Saldo alt'), 'colspan' => 2, },

    { text => t8('Jahresverkehrszahlen alt'), 'colspan' => 2, },

    { text => '', 'colspan' => 2, },

  ];

  push @custom_headers, [

    { text => $::form->{last_transaction}, },

    { text => format_debit_credit($self->{eb_value}), },

    { text => format_debit_credit($self->{saldo_old}), 'colspan' => 2, },

    { text => $::form->format_amount(\%::myconfig, abs($self->{debit_old}), 2) . " S", },

    { text => $::form->format_amount(\%::myconfig, $self->{credit_old}, 2) . " H", },

    { text => '', 'colspan' => 2, },

  ];

  # line 2

  # sorting is selected with radio button

  #my $link = "controller.pl?action=ListTransactions%2freport_settings&accno=$::form->{accno}&fromdate=$::form->{fromdate}&todate=$::form->{todate}&show_subtotals=$::form->{show_subtotals}";

  push @custom_headers, [

    { text => t8('Date'), }, # link => $link . "&sort=transdate", },

    { text => t8('Reference'), }, #'link' => $link . "&sort=reference",  },

    { text => t8('Description'), }, #'link' => $link . "&sort=description",  },

    { text => t8('Gegenkonto'), },

    { text => t8('Debit'), },

    { text => t8('Credit'), },

    { text => t8('USt-Konto'), },

    { text => t8('Satz %'), },

    { text => t8('Balance'), },

  ];

  $self->report->set_custom_headers(@custom_headers);

}

# action bar

sub setup_report_settings_action_bar {

  my ($self, %params) = @_;

  for my $bar ($::request->layout->get('actionbar')) {

    $bar->add(

      action => [

        t8('Show'),

        submit    => [ '#report_settings', { action => 'ListTransactions/list' } ],

        accesskey => 'enter',

      ],

      combobox => [

        action => [

          t8('Export'),

        ],

        action => [

          t8('Export all accounts to CSV (ZIP file)'),

          submit => [ '#report_settings', { action => 'ListTransactions/csv_options_export_all_charts' } ],

        ],

      ], # end of combobox "Export"

    );

  }

}

sub setup_csv_options_action_bar {

  my ($self, %params) = @_;

  for my $bar ($::request->layout->get('actionbar')) {

    $bar->add(

      action => [

        t8('Export'),

        submit    => [ '#report_generator_form', { action => 'ListTransactions/export_all_charts' } ],

        accesskey => 'enter',

      ],

      action => [

        t8('Back'),

        submit => [ '#report_generator_form', { action => 'ListTransactions/report_settings' } ],

      ],

    );

  }

}

sub setup_list_action_bar {

  my ($self, %params) = @_;

  for my $bar ($::request->layout->get('actionbar')) {

    $bar->add(

      action => [

        t8('Back'),

        submit => [ '#report_generator_form', { action => 'ListTransactions/report_settings' } ],

      ],

    );

  }

}

# helper

sub get_top_info_text {

  my ($self) = @_;

  my @text;

  if ($::form->{department}) {

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

    push @text, $::locale->text('Department') . " : $department";

  }

  if ($::form->{projectnumber}) {

    push @text, $::locale->text('Project Number') . " : $::form->{projectnumber}<br>";

  }

  push @text, join " ", t8('Period:'), $self->from_date, "-", $self->to_date;

  push @text, join " ", t8('Report date:'), $::locale->format_date_object(DateTime->now_local);

  push @text, join " ", t8('Company:'), $::instance_conf->get_company;

  join "\n", @text;

}

sub format_debit_credit {

  my $dc = shift;

  my $formatted_dc  = $::form->format_amount(\%::myconfig, abs($dc), 2) . ' ';

  $formatted_dc    .= ($dc > 0) ? t8('Credit (one letter abbreviation)') : t8('Debit (one letter abbreviation)');

  $formatted_dc;

}

sub init_accounts_list {

  CA->all_accounts(\%::myconfig, \%$::form);

  my @accounts_list = map { {

    text => "$_->{accno} - $_->{description}",

    accno => $_->{accno},

    chart_id => $_->{id},

  } } @{ $::form->{CA} };

  \@accounts_list;

}

1;

  'Company:'                    => 'Firma:',

  'Export all accounts to CSV (ZIP file)' => 'Alle Konten als CSV exportieren (ZIP-Datei)',

  'Jahresverkehrszahlen alt'    => 'Jahresverkehrszahlen alt',

  'Letzte Buchung'              => 'Letzte Buchung',

  'No account selected. Please select an account.' => 'Kein Konto ausgewählt. Bitte Konto auswählen.',

  'Report date:'                => 'Bericht Datum:',

  'Saldo alt'                   => 'Saldo alt',

  'Show subtotals'              => 'Zwischensummen anzeigen',

  'Sort by'                     => 'Sortieren nach',

  'account'                     => 'konto',

  'Company:'                    => '',

  'Export all accounts to CSV (ZIP file)' => '',

  'Jahresverkehrszahlen alt'    => '',

  'Letzte Buchung'              => '',

  'No account selected. Please select an account.' => '',

  'Report date:'                => '',

  'Saldo alt'                   => '',

  'Show subtotals'              => '',

  'Sort by'                     => '',

  'account'                     => '',

    action: ChartOfAccounts/list

- parent: reports

  id: reports_list_transactions

  name: List Transactions

  order: 150

  access: report

  params:

    action: ListTransactions/report_settings

[% USE HTML %]

[% USE LxERP %]

[% USE L %]

[% USE P %]

[% USE T8 %]

[% INCLUDE 'common/flash.html' %]

<h1>[% title | html %]</h1>

<form name="Form" method="post" action="controller.pl" id="report_settings">

  [% L.hidden_tag("action", "ListTransactions/report_settings") %]

  <div class="wrapper">

    <h3>[% "Account" | $T8 %]</h3>

    [% P.chart.picker('chart_id', defaults.chart_id) %]

    <h3>[% "Period" | $T8 %]</h3>

    [% P.date_period.picker('dateperiod', defaults.dateperiod_from_date,

                                          defaults.dateperiod_to_date,

                                          dialog_defaults => defaults.dialog) %]

    <h3>[% "Options" | $T8 %]</h3>

    <div class="wi-lightwide">[% L.checkbox_tag('show_subtotals', checked => defaults.show_subtotals,

                                              label => LxERP.t8('Show subtotals')) %]</div>

    <h3>[% "Sort by" | $T8 %]</h3>

    <div class="wi-normal">[% P.radio_button_tag('sort', value => 'transdate',

                                                  checked => defaults.sort == 'transdate',

                                                  label => LxERP.t8('Date')) %]</div>

    <div class="wi-normal">[% P.radio_button_tag('sort', value => 'reference',

                                                  checked => defaults.sort == 'reference',

                                                  label => LxERP.t8('Reference')) %]</div>

    <div class="wi-normal">[% P.radio_button_tag('sort', value => 'description',

                                                  checked => defaults.sort == 'description',

                                                  label => LxERP.t8('Description')) %]</div>

  </div>

</form>

[%- USE HTML %]

[%- USE LxERP %]

[%- USE L %]

[%- USE P %]

[%- USE T8 %]

[%- INCLUDE 'common/flash.html' %]

<form name="Form" method="post" action="controller.pl" id="report_settings">

[% L.hidden_tag("action", "ListTransactions/report_settings") %]

<h2>[% "Account" | $T8 %]</h2>

[% P.chart.picker('chart_id', defaults.chart_id) %]

<h2>[% "Period" | $T8 %]</h2>

[% P.date_period.picker('dateperiod', defaults.dateperiod_from_date,

                                      defaults.dateperiod_to_date,

                                      dialog_defaults => defaults.dialog) %]

<h2>[% "Options" | $T8 %]</h2>

[% L.checkbox_tag('show_subtotals', checked => defaults.show_subtotals,

                                    label => LxERP.t8('Show subtotals')) %]

<h2>[% "Sort by" | $T8 %]</h2>

[% P.radio_button_tag('sort', value => 'transdate',

                              checked => defaults.sort == 'transdate',

                              label => LxERP.t8('Date')) %]

[% P.radio_button_tag('sort', value => 'reference',

                              checked => defaults.sort == 'reference',

                              label => LxERP.t8('Reference')) %]

[% P.radio_button_tag('sort', value => 'description',

                              checked => defaults.sort == 'description',

                              label => LxERP.t8('Description')) %]

</form>

<hr>