projekt kivitendo

  my ($self, $myconfig, $form, $rows, $userspath, $spool, $sendmail) = @_;

  my ($dunning_id) = selectrow_query($form, $dbh, qq|SELECT nextval('id')|);

    $query = qq|UPDATE ar SET dunning_config_id = ? WHERE id = ?|;

    @values = ($form->{"next_dunning_config_id_$row"},

               $form->{"inv_id_$row"});

      qq|INSERT INTO dunning (dunning_id, dunning_config_id, dunning_level,

                              trans_id, fee, interest, transdate, duedate)

         VALUES (?, ?, (SELECT dunning_level FROM dunning_config WHERE id = ?),

    @values = ($dunning_id,

               conv_i($form->{"next_dunning_config_id_$row"}),

               conv_i($form->{"next_dunning_config_id_$row"}),

       FROM ar LEFT JOIN dunning_config ON (dunning_config.id = ar.dunning_config_id)

  delete($form->{tmpfile});

                    WHEN a.dunning_config_id IS NULL

                    ELSE (SELECT dunning_level

                          FROM dunning_config

                          WHERE id = a.dunning_config_id

                          ORDER BY dunning_level

                          LIMIT 1)

    $where .= qq| AND a.dunning_config_id = ?|;

         dnn.dunning_description AS next_dunning_description, dnn.id AS next_dunning_config_id,

       LEFT JOIN dunning_config dn ON (dn.id = a.dunning_config_id)

    $where .= qq| AND a.dunning_config_id = ?|;

    $where .= qq| AND (a.amount > a.paid) AND (da.dunning_config_id = a.dunning_config_id) |;

         ct.name AS customername, ct.id AS customer_id, a.duedate, da.fee,

         da.interest, dn.dunning_description, da.transdate AS dunning_date,

         da.duedate AS dunning_duedate, da.dunning_id, da.dunning_config_id

       FROM ar a

       JOIN customer ct ON (a.customer_id = ct.id), dunning da

       LEFT JOIN dunning_config dn ON (da.dunning_config_id = dn.id)

       $where

       ORDER BY name, a.id|;

  my ($self, $myconfig, $form, $dunning_id, $userspath,$spool, $sendmail) = @_;

       FROM dunning da

       LEFT JOIN dunning_config ON (dunning_config.id = da.dunning_config_id)

       LEFT JOIN ar ON (ar.id = da.trans_id)

       WHERE (da.dunning_id = ?)|;

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

                 WHERE a.paid < a.amount AND a.customer_id = ? AND a.dunning_config_id IS NOT NULL|;

                 WHERE id in (SELECT dunning_config_id from ar WHERE paid < amount AND customer_id = ? AND dunning_id IS NOT NULL)

    qq|<td><input type=checkbox name=active_$i value=1></td>|;

  if ($ref->{next_dunning_config_id} ne "") {

     $form->{selectdunning} =~ s/value=$ref->{next_dunning_config_id}/value=$ref->{next_dunning_config_id} selected/;

  $dunning = qq|<select name=next_dunning_config_id_$i>$form->{selectdunning}</select>|;

        DN->save_dunning(\%myconfig, \%$form, \@rows, $userspath, $spool, $sendmail);

        DN->save_dunning(\%myconfig, \%$form, \@rows, $userspath, $spool, $sendmail);

  my %columns = (

    "dunning_duedate" => "next_duedate",

    "duedate" => "inv_duedate",

    "transdate" => "invdate",

    "amount" => "invamount",

    );

  my ($previous_dunning_id, $first_row_for_dunning);

    if ($previous_dunning_id != $ref->{dunning_id}) {

      $j++;

      $j = $j % 2;

      $first_row_for_dunning = 1;

    } else {

      $first_row_for_dunning = 0;

    }

    $previous_dunning_id = $ref->{dunning_id};

    foreach (qw(dunning_date dunning_duedate duedate transdate customername

                amount fee interest)) {

      my $col = $columns{$_} ? $columns{$_} : $_;

      $column_data{$col} = "<td>" . H($ref->{$_}) . "</td>";

    if ($first_row_for_dunning) {

      $column_data{dunning_description} =

        qq|<td><a href="dn.pl?action=print_dunning&format=pdf&media=screen&| .

        qq|dunning_id=| . E($ref->{dunning_id}) .

        join(map({ "&${_}=" . E($form->{$_}) } qw(login path password callback))) .

        qq|">| . H($ref->{dunning_description}) . qq|</a></td>|;

    } else {

      $column_data{dunning_description} = qq|<td>&nbsp;</td>|;

      $column_data{customername} = qq|<td>&nbsp;</td>|;

    }

    $column_data{invnumber} =

      qq|<td><a href="| . ($ref->{invoice} ? "is.pl" : "ar.pl" ) .

      qq|?action=edit&id=| . H($ref->{id}) .

      join(map({ "&${_}=" . E($form->{$_}) } qw(login path password callback))) .

      qq|">| . H($ref->{invnumber}) . qq|</a></td>|;

  DN->print_dunning(\%myconfig, \%$form, $form->{dunning_id}, $userspath, $spool, $sendmail);

$LXDebug::global_level = LXDebug::ALL;

-- @tag: dunning_dunning_id

-- @description: In der Tabelle dunning ist dunning_id falsch benannt und es fehlt eine Spalte, die mehrere Einträge zusammenfasst.

-- @depends: release_2_4_2

ALTER TABLE dunning ADD COLUMN dunning_config_id integer;

UPDATE dunning SET dunning_config_id = dunning_id;

ALTER TABLE dunning ADD FOREIGN KEY (dunning_config_id) REFERENCES dunning_config (id);

ALTER TABLE dunning ADD COLUMN itime timestamp;

ALTER TABLE dunning ALTER COLUMN itime SET DEFAULT now();

UPDATE dunning SET itime = now();

ALTER TABLE dunning ADD COLUMN mtime timestamp;

CREATE TRIGGER mtime_dunning

    BEFORE UPDATE ON dunning

    FOR EACH ROW

    EXECUTE PROCEDURE set_mtime();

UPDATE dunning SET dunning_id = nextval('id');

ALTER TABLE ar RENAME COLUMN dunning_id TO dunning_config_id;

ALTER TABLE ar ADD FOREIGN KEY (dunning_config_id) REFERENCES dunning_config (id);