projekt kivitendo

use Data::Dumper;

use SL::DBUtils;

sub all_accounts {

  $main::lxdebug->enter_sub();

  my ($self, $myconfig, $form, $chart_id) = @_;

  # connect to database

  my $dbh = $form->dbconnect($myconfig);

  $form->{CA} = [];

    $ca->{amount} = $amount{ $ca->{accno} };

    if ($ca->{amount} < 0) {

      $ca->{debit} = $ca->{amount} * -1;

  #    $where .= " AND ac.transdate <= '$form->{todate}'";

  #  }

  if ($form->{fromdate}) {

    $where .= qq| AND ac.transdate >= ?|;

    $subwhere .= qq| AND transdate >= ?|;

  my $sortorder = join ', ',

    $form->sort_columns(qw(transdate reference description));

  # Oracle workaround, use ordinal positions

  my %ordinal = (transdate   => 4,

      ($form->{old_balance_credit}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{fromdate}, $form->{accno});

      # get current saldo

      $query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id) WHERE ((select date_trunc('year', ac.transdate::date)) >= (select date_trunc('year', ?::date))) $todate AND c.accno = ? AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)|;

      ($form->{saldo_new}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{accno});

      #get current balance

      $query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id) WHERE ((select date_trunc('year', ac.transdate::date)) >= (select date_trunc('year', ?::date))) $todate AND c.accno = ? AND ac.amount < 0 AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)|;

      ($form->{current_balance_debit}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{accno});

      $query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) >= (select date_trunc('year', ?::date))) $todate AND c.accno = ? AND ac.amount > 0 AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)|;

      ($form->{current_balance_credit}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{accno});

    }

      qq|WHERE | . $where . $dpt_where . $project .

      qq| AND ac.chart_id = ? | .

      qq| AND ac.trans_id = a.id | .

      qq| AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)| .

    push(@values,

         @where_values, @department_values, @project_values, $id,

      $fromdate_where =~ s/ac\./a\./;

      $todate_where   =~ s/ac\./a\./;

      $query .=

        qq|UNION ALL | .

        $todate_where .

        $dpt_where .

        $project .

        qq|UNION ALL | .

        qq|SELECT a.id, a.invnumber, v.name, a.transdate, | .

        $fromdate_where .

        $todate_where .

        $dpt_where .

      push(@values,

           $id, @department_values, @project_values,

           $id, @department_values, @project_values);

  my $sort = grep({ $form->{sort} eq $_ } qw(transdate reference description)) ? $form->{sort} : 'transdate';

  $query .= qq|ORDER BY $sort|;

  #get detail information for each transaction

        qq|SELECT accno, | .

        qq|amount, transdate FROM acc_trans LEFT JOIN chart ON (chart_id=chart.id) WHERE | .

        qq|trans_id = ? AND sign(amount) <> sign(?) AND chart_id <> ? AND transdate = ?|;

  my $trans_sth = $dbh->prepare($trans_query);

  $form->{CA} = [];

    # ap

    if ($ca->{module} eq "ap") {

      $ca->{module} = ($ca->{invoice}) ? 'ir' : 'ap';

    #get detail information for this transaction

    $trans_sth->execute($ca->{id}, $ca->{amount}, $ca->{chart_id}, $ca->{transdate}) ||

    $form->dberror($trans_query . " (" . join(", ", $ca->{id}) . ")");

      if (($ca->{transdate} eq $trans->{transdate}) && ($ca->{amount} * $trans->{amount} < 0)) {

        if ($trans->{amount} < 0) {

          $trans->{debit}  = $trans->{amount} * -1;