projekt kivitendo

  my @values  = map(

    {

      if ( $_ eq 'cp_gender' ) {

        $form->{$_} eq 'f' ? 'f' : 'm';

      } elsif ( $_ eq 'cp_birthday' && $form->{cp_birthday} eq '' ) {

        undef;

      } else {

        $form->{$_};

      }

    }

    @columns

  );

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

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

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

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

  'Birthday (after conversion)' => 'Geburtstag (nach Umstellung)',

  'Birthday (before conversion)' => 'Geburtstag (vor Umstellung)',

  'Conversion of "birthday" contact person attribute' => 'Umstellung des Kontaktpersonenfeldes "Geburtstag"',

  'Database ID'                 => 'Datenbank-ID',

  'Entries for which automatic conversion failed:' => 'Einträge, für die die automatische Umstellung fehlschlug:',

  'Entries for which automatic conversion succeeded:' => 'Einträge, für die die automatische Umstellung erfolgreich war:',

  'The contact person attribute "birthday" is converted from a free-form text field into a date field.' => 'Das Kontaktpersonenfeld "Geburtstag" wird von einem freien Textfeld auf ein Datumsfeld umgestellt.',

  'This requires you to manually correct entries for which an automatic conversion failed and to check those for which it succeeded.' => 'Dies erfordert, dass Sie diejenigen Einträge manuell korrigieren, für die die automatische Umstellung fehlschlug, sowie dass Sie diejenigen überprüfen, für die die Umstellung erfolgreich war.',

# @tag: contacts_convert_cp_birthday_to_date

# @description: Umstellung cp_birthday von Freitext auf Datumsfeld

# @depends: release_2_7_0

package contacts_convert_cp_birthday_to_date;

use strict;

die 'This script cannot be run from the command line.' if !$::form;

sub convert_to_date {

  my ($str) = @_;

  return '' if !$str;

  my $sth = $dbh->prepare('SELECT ?::date AS date') or return undef;

  $sth->execute($str)                               or return undef;

  return $sth->fetchrow_hashref->{date};

}

sub update {

  my @data      = ();

  my @auto_data = ();

  my $sql       = <<SQL;

    SELECT

      cp_id,

      cp_givenname,

      cp_name,

      cp_birthday AS cp_birthday_old

    FROM contacts

    ORDER BY cp_id;

SQL

  my $sth = $dbh->prepare($sql) or die $dbh->errstr;

  $sth->execute or die $dbh->errstr;

  my $i = -1;

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

    $i++;

    $row->{cp_birthday} = convert_to_date($::form->{form_submitted} ? $::form->{'cp_birthday_'. $i} : $row->{cp_birthday_old});

    $row->{row_index}   = $i;

    if ( defined($row->{cp_birthday}) ) {

       push(@auto_data, $row);

    } else {

       push(@data,      $row);

    }

  }

  $::form->{data}       = \@data;

  $::form->{auto_data}  = \@auto_data;

  $::form->{row_length} = $i;

  if (@data) {

    print $::form->parse_html_template('dbupgrade/contacts_convert_cp_birthday_to_date_form');

    return 2;

  } else {

    $sql = <<SQL;

      ALTER TABLE contacts DROP COLUMN cp_birthday;

      ALTER TABLE contacts ADD COLUMN cp_birthday date;

SQL

    $dbh->do($sql);

    $sql = <<SQL;

      UPDATE contacts

      SET   cp_birthday = ?

      WHERE cp_id = ?

SQL

    $sth = $dbh->prepare($sql) or die $dbh->errstr;

    foreach (grep { $_->{cp_birthday} ne '' } @auto_data) {

      $sth->execute($_->{cp_birthday}, $_->{cp_id}) or die $dbh->errstr;

    }

    return 1;

  }

}

return update();

      <td>

        [% L.date_tag('cp_birthday', cp_birthday) %]

      </td>

[%- USE HTML %]

[%- USE L %]

[%- USE LxERP %]

<h1>[%- LxERP.t8('Conversion of "birthday" contact person attribute') %]</h1>

<p>

 [%- LxERP.t8('The contact person attribute "birthday" is converted from a free-form text field into a date field.') %]

 [%- LxERP.t8('This requires you to manually correct entries for which an automatic conversion failed and to check those for which it succeeded.') %]

</p>

[% BLOCK birthday_table %]

  <table>

    <tr>

      <th>[%- LxERP.t8('Database ID') %]</th>

      <th>[%- LxERP.t8('Name') %]</th>

      <th>[%- LxERP.t8('Given Name') %]</th>

      <th>[%- LxERP.t8('Birthday (before conversion)') %]</th>

      <th>[%- LxERP.t8('Birthday (after conversion)') %]</th>

    </tr>

    [% FOREACH row IN data %]

      <tr class="listrow[% loop.count % 2 %]">

        <input type="hidden" name="cp_id_[% row.row_index %]" value="[% row.cp_id %]">

        <td>[% row.cp_id %]</td>

        <td>[% row.cp_givenname | html %]</td>

        <td>[% row.cp_name | html %]</td>

        <td>[% row.cp_birthday_old | html %]</td>

        <td>[% L.date_tag('cp_birthday_'_ row.row_index, row.cp_birthday) %]</td>

      </tr>

    [% END %]

  </table>

[% END %]

<form action="[% script %]" method="POST">

  <h2>[%- LxERP.t8('Entries for which automatic conversion failed:') %]</h2>

  [% PROCESS birthday_table data = data %]

  <h2>[%- LxERP.t8('Entries for which automatic conversion succeeded:') %]</h2>

  [% PROCESS birthday_table data = auto_data %]

  <input type="hidden" name="row_length" value="[% row_length %]">

  <input type="hidden" name="action" value="LoginScreen/login">

  <input type="submit" name="form_submitted" value="save">

</form>