projekt kivitendo

  my $c_mandate    = $params{vc} eq 'customer' ? ', mandator_id, mandate_date_of_signature' : '';

  my $query        = qq|SELECT id, name, account_number, bank, bank_code, iban, bic ${c_mandate}

  account_number            => { type => 'text' },

  bank                      => { type => 'text' },

  bank_code                 => { type => 'text' },

  bcc                       => { type => 'text' },

  bic                       => { type => 'varchar', length => 100 },

  business_id               => { type => 'integer' },

  c_vendor_id               => { type => 'text' },

  cc                        => { type => 'text' },

  city                      => { type => 'varchar', length => 75 },

  contact                   => { type => 'text' },

  country                   => { type => 'varchar', length => 75 },

  creditlimit               => { type => 'numeric', default => '0', precision => 5, scale => 15 },

  currency_id               => { type => 'integer', not_null => 1 },

  customernumber            => { type => 'text' },

  delivery_term_id          => { type => 'integer' },

  department_1              => { type => 'varchar', length => 75 },

  department_2              => { type => 'varchar', length => 75 },

  depositor                 => { type => 'text' },

  direct_debit              => { type => 'boolean', default => 'false' },

  discount                  => { type => 'float', precision => 4 },

  email                     => { type => 'text' },

  fax                       => { type => 'varchar', length => 30 },

  greeting                  => { type => 'text' },

  homepage                  => { type => 'text' },

  iban                      => { type => 'varchar', length => 100 },

  id                        => { type => 'integer', not_null => 1, sequence => 'id' },

  itime                     => { type => 'timestamp', default => 'now()' },

  klass                     => { type => 'integer', default => '0' },

  language                  => { type => 'varchar', length => 5 },

  language_id               => { type => 'integer' },

  mandate_date_of_signature => { type => 'date' },

  mandator_id               => { type => 'text' },

  mtime                     => { type => 'timestamp' },

  name                      => { type => 'text', not_null => 1 },

  notes                     => { type => 'text' },

  obsolete                  => { type => 'boolean', default => 'false' },

  payment_id                => { type => 'integer' },

  phone                     => { type => 'text' },

  salesman_id               => { type => 'integer' },

  street                    => { type => 'varchar', length => 75 },

  taxincluded               => { type => 'boolean' },

  taxincluded_checked       => { type => 'boolean' },

  taxnumber                 => { type => 'text' },

  taxzone_id                => { type => 'integer', default => '0', not_null => 1 },

  terms                     => { type => 'integer', default => '0' },

  user_password             => { type => 'text' },

  username                  => { type => 'varchar', length => 50 },

  ustid                     => { type => 'text' },

  zipcode                   => { type => 'varchar', length => 10 },

  amount                       => { type => 'numeric', precision => 5, scale => 25 },

  ap_id                        => { type => 'integer' },

  ar_id                        => { type => 'integer' },

  chart_id                     => { type => 'integer', not_null => 1 },

  end_to_end_id                => { type => 'varchar', length => 35 },

  executed                     => { type => 'boolean', default => 'false' },

  execution_date               => { type => 'date' },

  id                           => { type => 'integer', not_null => 1, sequence => 'id' },

  our_bic                      => { type => 'varchar', length => 100 },

  our_depositor                => { type => 'text' },

  our_iban                     => { type => 'varchar', length => 100 },

  reference                    => { type => 'varchar', length => 35 },

  requested_execution_date     => { type => 'date' },

  sepa_export_id               => { type => 'integer', not_null => 1 },

  vc_bic                       => { type => 'varchar', length => 100 },

  vc_depositor                 => { type => 'text' },

  vc_iban                      => { type => 'varchar', length => 100 },

  vc_mandate_date_of_signature => { type => 'date' },

  vc_mandator_id               => { type => 'text' },

  my $mandate  = $params{vc} eq 'customer' ? " AND COALESCE(vc.mandator_id, '') <> '' AND vc.mandate_date_of_signature IS NOT NULL " : '';

         COALESCE(vc.iban, '') <> '' AND COALESCE(vc.bic, '') <> '' ${mandate} AS vc_bank_info_ok,

  my $c_mandate = $params{vc} eq 'customer' ? ', vc_mandator_id, vc_mandate_date_of_signature' : '';

  my $p_mandate = $params{vc} eq 'customer' ? ', ?, ?' : '';

                                      our_iban,    our_bic,                  vc_iban,     vc_bic ${c_mandate})

                                      ?,           ?,                        ?,           ? ${p_mandate})|;

    push @values, $transfer->{vc_mandator_id}, conv_date($transfer->{vc_mandate_date_of_signature}) if $params{vc} eq 'customer';

    my $mandator_id = $params{vc} eq 'customer' ? ', mandator_id, mandate_date_of_signature' : '';

      $columns = qq|, arap.invnumber, arap.invoice, arap.transdate AS reference_date, vc.name AS vc_name, vc.${vc}number AS vc_number, c.accno AS chart_accno, c.description AS chart_description ${mandator_id}|;

        $xml->dataElement('DtOfSgntr', $self->_restricted_identification_sepa2($transaction->get('date_of_signature')));

  my @bank_columns    = qw(iban bic);

  push @bank_columns, qw(mandator_id mandate_date_of_signature) if $vc eq 'customer';

      if (any { !$info->{$_} } @bank_columns) {

      foreach (@bank_columns) {

      if ($item->{mandate_date_of_signature}) {

        ($yy, $mm, $dd)                    = $locale->parse_date($myconfig, $item->{mandate_date_of_signature});

        $item->{mandate_date_of_signature} = sprintf '%04d-%02d-%02d', $yy, $mm, $dd;

      }

                                 'end_to_end_id'  => $item->{end_to_end_id},

                                 'date_of_signature' => $item->{mandate_date_of_signature}, });

  'Mandate Date of Signature'   => 'Mandat-Unterschriftsdatum',

  'The required information consists of the IBAN and the BIC.' => 'Die benötigten Informationen bestehen aus der IBAN und der BIC.',

  'The required information consists of the IBAN, the BIC, the mandator ID and the mandate\'s date of signature.' => 'Die benötigten Informationen bestehen aus IBAN, BIC, Mandanten-ID und dem Unterschriftsdatum des Mandates.',

-- @description: Einführen einer Mandanten-ID- und Signatur-Datums-Spalte bei Kunden.

ALTER TABLE customer          ADD COLUMN mandator_id                  text;

ALTER TABLE customer          ADD COLUMN mandate_date_of_signature    date;

ALTER TABLE sepa_export_items ADD COLUMN vc_mandator_id               text;

ALTER TABLE sepa_export_items ADD COLUMN vc_mandate_date_of_signature date;

UPDATE sepa_export_items

SET vc_mandator_id = (

  SELECT c.customernumber

  FROM ar

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

  WHERE ar.id = sepa_export_items.ar_id

),

vc_mandate_date_of_signature = '2010-01-01'::date

WHERE ar_id IS NOT NULL;

    [%- IF ( SELF.is_customer ) %]

      <th align="right">[% 'Mandate Date of Signature' | $T8 %]</th>

      <td>

        [% L.date_tag('cv.mandate_date_of_signature', SELF.cv.mandate_date_of_signature) %]

      </td>

    [%- END %]

     [% "The required information consists of the IBAN and the BIC." | $T8 %]

     [% "The required information consists of the IBAN, the BIC, the mandator ID and the mandate's date of signature." | $T8 %]

      <th class="listheading">[% 'Mandate Date of Signature' | $T8 %]</th>

      <td>[% L.date_tag("vc_bank_info[].mandate_date_of_signature", vbi.mandate_date_of_signature) %]</td>