projekt kivitendo

sub buchungsgruppe {

  $main::lxdebug->enter_sub();

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

  # connect to database

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

  # TODO: extract information about income/expense accounts from new table taxzone_chart

  my $query = qq|SELECT id, description,

                 inventory_accno_id,

                 (SELECT accno FROM chart WHERE id = inventory_accno_id) AS inventory_accno

                 FROM buchungsgruppen

                 ORDER BY sortkey|;

  my $sth = $dbh->prepare($query);

  $sth->execute || $form->dberror($query);

  $form->{ALL} = [];

  while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {

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

  }

  $sth->finish;

  $dbh->disconnect;

  $main::lxdebug->leave_sub();

}

sub get_buchungsgruppe {

  $main::lxdebug->enter_sub();

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

  my $query;

  # connect to database

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

  if ($form->{id}) {

    # TODO: extract information about income/expense accounts from new table taxzone_chart

    $query =

      qq|SELECT description, inventory_accno_id,

         (SELECT accno FROM chart WHERE id = inventory_accno_id) AS inventory_accno

         FROM buchungsgruppen

         WHERE id = ?|;

    my $sth = $dbh->prepare($query);

    $sth->execute($form->{id}) || $form->dberror($query . " ($form->{id})");

    my $ref = $sth->fetchrow_hashref("NAME_lc");

    map { $form->{$_} = $ref->{$_} } keys %$ref;

    $sth->finish;

    $query =

      qq|SELECT count(id) = 0 AS orphaned

         FROM parts

         WHERE buchungsgruppen_id = ?|;

    ($form->{orphaned}) = selectrow_query($form, $dbh, $query, $form->{id});

  }

  $query = "SELECT inventory_accno_id, income_accno_id, expense_accno_id ".

    "FROM defaults";

  ($form->{"std_inventory_accno_id"}, $form->{"std_income_accno_id"},

   $form->{"std_expense_accno_id"}) = selectrow_query($form, $dbh, $query);

  my $module = "IC";

  $query = qq|SELECT c.accno, c.description, c.link, c.id,

              d.inventory_accno_id, d.income_accno_id, d.expense_accno_id

              FROM chart c, defaults d

              WHERE c.link LIKE '%$module%'

              ORDER BY c.accno|;

  my $sth = $dbh->prepare($query);

  $sth->execute || $form->dberror($query);

  while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {

    foreach my $key (split(/:/, $ref->{link})) {

      if (!$form->{"std_inventory_accno_id"} && ($key eq "IC")) {

        $form->{"std_inventory_accno_id"} = $ref->{"id"};

      }

      if ($key =~ /$module/) {

        if (   ($ref->{id} eq $ref->{inventory_accno_id})

            || ($ref->{id} eq $ref->{income_accno_id})

            || ($ref->{id} eq $ref->{expense_accno_id})) {

          push @{ $form->{"${module}_links"}{$key} },

            { accno       => $ref->{accno},

              description => $ref->{description},

              selected    => "selected",

              id          => $ref->{id} };

        } else {

          push @{ $form->{"${module}_links"}{$key} },

            { accno       => $ref->{accno},

              description => $ref->{description},

              selected    => "",

              id          => $ref->{id} };

        }

      }

    }

  }

  $sth->finish;

  $dbh->disconnect;

  $main::lxdebug->leave_sub();

}

sub save_buchungsgruppe {

  $main::lxdebug->enter_sub();

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

  # connect to database

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

  my @values = ($form->{description}, $form->{inventory_accno_id},

                $form->{income_accno_id_0}, $form->{expense_accno_id_0},

                $form->{income_accno_id_1}, $form->{expense_accno_id_1},

                $form->{income_accno_id_2}, $form->{expense_accno_id_2},

                $form->{income_accno_id_3}, $form->{expense_accno_id_3});

  my $query;

  # id is the old record

  if ($form->{id}) {

    $query = qq|UPDATE buchungsgruppen SET

                description = ?, inventory_accno_id = ?,

                income_accno_id_0 = ?, expense_accno_id_0 = ?,

                income_accno_id_1 = ?, expense_accno_id_1 = ?,

                income_accno_id_2 = ?, expense_accno_id_2 = ?,

                income_accno_id_3 = ?, expense_accno_id_3 = ?

                WHERE id = ?|;

    push(@values, $form->{id});

  } else {

    $query = qq|SELECT COALESCE(MAX(sortkey) + 1, 1) FROM buchungsgruppen|;

    my ($sortkey) = $dbh->selectrow_array($query);

    $form->dberror($query) if ($dbh->err);

    push(@values, $sortkey);

    $query = qq|INSERT INTO buchungsgruppen

                (description, inventory_accno_id,

                income_accno_id_0, expense_accno_id_0,

                income_accno_id_1, expense_accno_id_1,

                income_accno_id_2, expense_accno_id_2,

                income_accno_id_3, expense_accno_id_3,

                sortkey)

                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)|;

  }

  do_query($form, $dbh, $query, @values);

  $dbh->disconnect;

  $main::lxdebug->leave_sub();

}

sub delete_buchungsgruppe {

  $main::lxdebug->enter_sub();

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

  # connect to database

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

  my $query = qq|DELETE FROM buchungsgruppen WHERE id = ?|;

  do_query($form, $dbh, $query, $form->{id});

  $dbh->disconnect;

  $main::lxdebug->leave_sub();

}

sub swap_sortkeys {

  $main::lxdebug->enter_sub();

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

  # connect to database

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

  my $query =

    qq|SELECT

       (SELECT sortkey FROM $table WHERE id = ?) AS sortkey1,

       (SELECT sortkey FROM $table WHERE id = ?) AS sortkey2|;

  my @values   = ($form->{"id1"}, $form->{"id2"});

  my @sortkeys = selectrow_query($form, $dbh, $query, @values);

  $query  = qq|UPDATE $table SET sortkey = ? WHERE id = ?|;

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

  do_statement($form, $sth, $query, $sortkeys[1], $form->{"id1"});

  do_statement($form, $sth, $query, $sortkeys[0], $form->{"id2"});

  $sth->finish();

  $dbh->commit();

  $main::lxdebug->leave_sub();

}