projekt kivitendo

use SL::IS;

use SL::Mailer;

use SL::MoreCommon;

use SL::Template;

  $query =

    qq|SELECT

         dunning_create_invoices_for_fees, dunning_ar_amount_fee,

         dunning_ar_amount_interest,       dunning_ar

       FROM defaults|;

  ($form->{create_invoices_for_fees}, $form->{AR_amount_fee},

   $form->{AR_amount_interest},       $form->{AR}           ) = selectrow_query($form, $dbh, $query);

  $query  = qq|UPDATE defaults SET dunning_create_invoices_for_fees = ?|;

  @values = ($form->{create_invoices_for_fees} ? 't' : 'f');

  if ($form->{create_invoices_for_fees}) {

    $query .= qq|, dunning_ar_amount_fee = ?, dunning_ar_amount_interest = ?, dunning_ar = ?|;

    push @values, conv_i($form->{AR_amount_fee}), conv_i($form->{AR_amount_interest}), conv_i($form->{AR});

  }

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

  $dbh->commit();

  $dbh->disconnect();

  $main::lxdebug->leave_sub();

}

sub create_invoice_for_fees {

  $main::lxdebug->enter_sub();

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

  my ($query, @values, $sth, $ref);

  $query =

    qq|SELECT

         dunning_create_invoices_for_fees, dunning_ar_amount_fee,

         dunning_ar_amount_interest, dunning_ar

       FROM defaults|;

  ($form->{create_invoices_for_fees}, $form->{AR_amount_fee},

   $form->{AR_amount_interest},       $form->{AR}           ) = selectrow_query($form, $dbh, $query);

  if (!$form->{create_invoices_for_fees}) {

    $main::lxdebug->leave_sub();

    return;

  }

  $query =

    qq|SELECT

         fee,

         COALESCE((

           SELECT MAX(d_fee.fee)

           FROM dunning d_fee

           WHERE (d_fee.trans_id   =  d.trans_id)

             AND (d_fee.dunning_id <> ?)

             AND NOT (d_fee.fee_interest_ar_id ISNULL)

         ), 0)

         AS max_previous_fee,

         interest,

         COALESCE((

           SELECT MAX(d_interest.interest)

           FROM dunning d_interest

           WHERE (d_interest.trans_id   =  d.trans_id)

             AND (d_interest.dunning_id <> ?)

             AND NOT (d_interest.fee_interest_ar_id ISNULL)

         ), 0)

         AS max_previous_interest

       FROM dunning d

       WHERE dunning_id = ?|;

  @values = ($dunning_id, $dunning_id, $dunning_id);

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

  my ($fee_remaining, $interest_remaining) = (0, 0);

  my ($fee_total, $interest_total) = (0, 0);

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

    $fee_remaining      += $form->round_amount($ref->{fee}, 2);

    $fee_remaining      -= $form->round_amount($ref->{max_previous_fee}, 2);

    $fee_total          += $form->round_amount($ref->{fee}, 2);

    $interest_remaining += $form->round_amount($ref->{interest}, 2);

    $interest_remaining -= $form->round_amount($ref->{max_previous_interest}, 2);

    $interest_total     += $form->round_amount($ref->{interest}, 2);

  }

  $sth->finish();

  my $amount = $fee_remaining + $interest_remaining;

  if (!$amount) {

    $main::lxdebug->leave_sub();

    return;

  }

  my ($ar_id) = selectrow_query($form, $dbh, qq|SELECT nextval('glid')|);

  $query =

    qq|INSERT INTO ar (id,          invnumber, transdate, gldate, customer_id,

                       taxincluded, amount,    netamount, paid,   duedate,

                       invoice,     curr,      notes,

                       employee_id)

       VALUES (

         ?,                     -- id

         ?,                     -- invnumber

         current_date,          -- transdate

         current_date,          -- gldate

         -- customer_id:

         (SELECT ar.customer_id

          FROM dunning dn

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

          WHERE dn.dunning_id = ?

          LIMIT 1),

         'f',                   -- taxincluded

         ?,                     -- amount

         ?,                     -- netamount

         0,                     -- paid

         -- duedate:

         (SELECT duedate FROM dunning WHERE dunning_id = ?),

         'f',                   -- invoice

         ?,                     -- curr

         ?,                     -- notes

         -- employee_id:

         (SELECT id FROM employee WHERE login = ?)

       )|;

  @values = ($ar_id,            # id

             $form->update_defaults($myconfig, 'invnumber', $dbh), # invnumber

             $dunning_id,       # customer_id

             $amount,

             $amount,

             $dunning_id,       # duedate

             (split m/:/, $myconfig->{currency})[0], # currency

             sprintf($main::locale->text('Automatically created invoice for fee and interest for dunning %s'), $dunning_id), # notes

             $form->{login});   # employee_id

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

  $query =

    qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, taxkey)

       VALUES (?, ?, ?, current_date, current_date, 0)|;

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

  @values = ($ar_id, conv_i($form->{AR_amount_fee}), $fee_remaining);

  do_statement($form, $sth, $query, @values);

  if ($interest_remaining) {

    @values = ($ar_id, conv_i($form->{AR_amount_interest}), $interest_remaining);

    do_statement($form, $sth, $query, @values);

  }

  @values = ($ar_id, conv_i($form->{AR}), -1 * $amount);

  do_statement($form, $sth, $query, @values);

  $sth->finish();

  $query = qq|UPDATE dunning SET fee_interest_ar_id = ? WHERE dunning_id = ?|;

  do_query($form, $dbh, $query, $ar_id, $dunning_id);

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

                            fee,        interest,          transdate,     duedate)

  $form->{DUNNING_PDFS_EMAIL} = [];

  $self->create_invoice_for_fees($myconfig, $form, $dbh, $dunning_id);

  $self->print_invoice_for_fees($myconfig, $form, $dunning_id, $dbh);

  $self->print_dunning($myconfig, $form, $dunning_id, $dbh);

  $form->{dunning_id} = $dunning_id;

  if ($send_email) {

    $self->send_email($myconfig, $form, $dunning_id, $dbh);

  }

#   $dbh->commit();

  $dbh->disconnect();

  $main::lxdebug->leave_sub();

}

sub send_email {

  $main::lxdebug->enter_sub();

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

         dcfg.email_body,     dcfg.email_subject, dcfg.email_attachment,

         c.email AS recipient

       FROM dunning d

       LEFT JOIN dunning_config dcfg ON (d.dunning_config_id = dcfg.id)

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

       LEFT JOIN customer c          ON (ar.customer_id      = c.id)

       WHERE (d.dunning_id = ?)

       LIMIT 1|;

  my $ref = selectfirst_hashref_query($form, $dbh, $query, $dunning_id);

  if (!$ref || !$ref->{recipient} || !$myconfig->{email}) {

    $main::lxdebug->leave_sub();

    return;

  my $template     = PlainTextTemplate->new(undef, $form, $myconfig);

  my $mail         = Mailer->new();

  $mail->{from}    = $myconfig->{email};

  $mail->{to}      = $ref->{recipient};

  $mail->{subject} = $template->parse_block($ref->{email_subject});

  $mail->{message} = $template->parse_block($ref->{email_body});

  if ($myconfig->{signature}) {

    $mail->{message} .= "\n-- \n$myconfig->{signature}";

  }

  $mail->{message} =~ s/\r\n/\n/g;

  if ($ref->{email_attachment} && @{ $form->{DUNNING_PDFS_EMAIL} }) {

    $mail->{attachments} = $form->{DUNNING_PDFS_EMAIL};

  }

  $mail->send();

  $main::lxdebug->leave_sub();

}

sub set_template_options {

  $main::lxdebug->enter_sub();

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

  $form->{language}     = $form->get_template_language($myconfig);

  $form->{printer_code} = $form->get_printer_code($myconfig);

  $form->{IN}  = "$form->{formname}$form->{language}$form->{printer_code}.html";

  $form->{pdf} = 1;

  if ($form->{"format"} =~ /opendocument/) {

    $form->{IN} =~ s/html$/odt/;

    $form->{IN} =~ s/html$/tex/;

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

  # Don't allow access outside of $spool.

  $copies        *= 1;

  $copies         = 1 unless $copies;

  my $inputfiles  = join " ", map { "${main::spool}/$_ " x $copies } @{ $form->{DUNNING_PDFS} };

  my $dunning_id  = $form->{dunning_id};

  $dunning_id     =~ s|[^\d]||g;

  my $in = IO::File->new("gs -dBATCH -dNOPAUSE -q -sDEVICE=pdfwrite -sOutputFile=- $inputfiles |");

  $form->error($main::locale->text('Could not spawn ghostscript.')) unless $in;

  my $out;

  if ($form->{media} eq 'printer') {

    $form->get_printer_code($myconfig);

    if ($form->{printer_command}) {

      $out = IO::File->new("| $form->{printer_command}");

    $form->error($main::locale->text('Could not spawn the printer command.')) unless $out;

  } else {

    $out = IO::File->new('>-');

    $out->print(qq|Content-Type: Application/PDF\n| .

                qq|Content-Disposition: attachment; filename="dunning_${dunning_id}.pdf"\n\n|);

  }

  while (my $line = <$in>) {

    $out->print($line);

  $in->close();

  $out->close();

  map { unlink("${main::spool}/$_") } @{ $form->{DUNNING_PDFS} };

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

  my $dbh = $provided_dbh ? $provided_dbh : $form->dbconnect_noauto($myconfig);

  $dunning_id =~ s|[^\d]||g;

    qq|SELECT

         da.fee, da.interest,

         da.transdate  AS dunning_date,

         da.duedate    AS dunning_duedate,

         dcfg.template AS formname,

         dcfg.email_subject, dcfg.email_body, dcfg.email_attachment,

         ar.transdate,       ar.duedate,      ar.customer_id,

         ar.invnumber,       ar.ordnumber,

         ar.amount,          ar.netamount,    ar.paid,

         ar.amount - ar.paid AS open_amount

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

  $sth->finish();

    qq|SELECT

         c.id AS customer_id, c.name,         c.street,       c.zipcode, c.city,

         c.country,           c.department_1, c.department_2, c.email

       FROM dunning d

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

       LEFT JOIN customer c ON (ar.customer_id = c.id)

       WHERE (d.dunning_id = ?)

       LIMIT 1|;

  $self->set_template_options($myconfig, $form);

  my $filename          = "dunning_${dunning_id}_" . Common::unique_id() . ".pdf";

  $form->{OUT}          = ">${main::spool}/$filename";

  $form->{keep_tmpfile} = 1;

  delete $form->{tmpfile};

  push @{ $form->{DUNNING_PDFS} }, $filename;

  push @{ $form->{DUNNING_PDFS_EMAIL} }, { 'filename' => $filename,

                                           'name'     => "dunning_${dunning_id}.pdf" };

  $form->parse_template($myconfig, $main::userspath);

  $dbh->disconnect() unless $provided_dbh;

  $main::lxdebug->leave_sub();

}

sub print_invoice_for_fees {

  $main::lxdebug->enter_sub();

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

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

  my ($query, @values, $sth);

  $query =

    qq|SELECT

         d.fee_interest_ar_id,

         dcfg.template

       FROM dunning d

       LEFT JOIN dunning_config dcfg ON (d.dunning_config_id = dcfg.id)

       WHERE d.dunning_id = ?|;

  my ($ar_id, $template) = selectrow_query($form, $dbh, $query, $dunning_id);

  if (!$ar_id) {

    $main::lxdebug->leave_sub();

    return;

  my $saved_form = save_form();

  $query = qq|SELECT SUM(fee), SUM(interest) FROM dunning WHERE id = ?|;

  my ($fee_total, $interest_total) = selectrow_query($form, $dbh, $query, $dunning_id);

  $query =

    qq|SELECT

         ar.invnumber, ar.transdate, ar.amount, ar.netamount,

         ar.duedate,   ar.notes,     ar.notes AS invoicenotes,

         c.name,      c.department_1,   c.department_2, c.street, c.zipcode, c.city, c.country,

         c.contact,   c.customernumber, c.phone,        c.fax,    c.email,

         c.taxnumber, c.sic_code,       c.greeting

       FROM ar

       LEFT JOIN customer c ON (ar.customer_id = c.id)

       WHERE ar.id = ?|;

  $ref = selectfirst_hashref_query($form, $dbh, $query, $ar_id);

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

  $query = qq|SELECT * FROM employee WHERE login = ?|;

  $ref = selectfirst_hashref_query($form, $dbh, $query, $form->{login});

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

  $query = qq|SELECT * FROM acc_trans WHERE trans_id = ? ORDER BY oid ASC|;

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

  my ($row, $fee, $interest) = (0, 0, 0);

  while ($ref = $sth->fetchrow_hashref()) {

    next if ($ref->{amount} < 0);

    $row++;

    if ($row == 1) {

      $fee = $ref->{amount};

      $interest = $ref->{amount};

  $form->{fee}        = $form->round_amount($fee,             2);

  $form->{interest}   = $form->round_amount($interest,        2);

  $form->{invamount}  = $form->round_amount($fee + $interest, 2);

  $form->{dunning_id} = $dunning_id;

  $form->{formname}   = "${template}_invoice";

  map { $form->{$_} = $form->format_amount($myconfig, $form->{$_}, 2) } qw(fee interest invamount);

  $self->set_template_options($myconfig, $form);

  my $filename = Common::unique_id() . "dunning_invoice_${dunning_id}.pdf";

  $form->{OUT}          = ">$main::spool/$filename";

  $form->{keep_tmpfile} = 1;

  delete $form->{tmpfile};

  map { delete $form->{$_} } grep /^[a-z_]+_\d+$/, keys %{ $form };

  $form->parse_template($myconfig, $main::userspath);

  restore_form($saved_form);

  push @{ $form->{DUNNING_PDFS} }, $filename;

  push @{ $form->{DUNNING_PDFS_EMAIL} }, { 'filename' => $filename,

                                           'name'     => "dunning_invoice_${dunning_id}.pdf" };

  $dbh->disconnect() unless $provided_dbh;