From 0c4110e2425480eed2cf93f220c75f823191ec27 Mon Sep 17 00:00:00 2001 From: Moritz Bunkus Date: Fri, 20 Jan 2017 11:52:50 +0100 Subject: [PATCH] =?utf8?q?Belegvorlagen:=20vorhandene=20Entw=C3=BCrfe=20in?= =?utf8?q?=20Vorlagen=20migrieren?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit --- locale/de/all | 1 + .../convert_drafts_to_record_templates.pl | 331 ++++++++++++++++++ 2 files changed, 332 insertions(+) create mode 100644 sql/Pg-upgrade2/convert_drafts_to_record_templates.pl diff --git a/locale/de/all b/locale/de/all index a7842347f..e7b5e6a00 100755 --- a/locale/de/all +++ b/locale/de/all @@ -3781,6 +3781,7 @@ $self->{texts} = { 'true' => 'wahr', 'uncleared' => 'Nicht abgeglichen', 'unconfigured' => 'unkonfiguriert', + 'unnamed record template' => 'unbenannte Belegvorlage', 'until' => 'bis', 'use program settings' => 'benutze Programmeinstellungen', 'use user config' => 'Verwende Benutzereinstellung', diff --git a/sql/Pg-upgrade2/convert_drafts_to_record_templates.pl b/sql/Pg-upgrade2/convert_drafts_to_record_templates.pl new file mode 100644 index 000000000..7507f9ab8 --- /dev/null +++ b/sql/Pg-upgrade2/convert_drafts_to_record_templates.pl @@ -0,0 +1,331 @@ +# @tag: convert_drafts_to_record_templates +# @description: Umwandlung von existierenden Entwürfen in Buchungsvorlagen für die Finanzbuchhaltung +# @depends: create_record_template_tables +package SL::DBUpgrade2::convert_drafts_to_record_templates; + +use strict; +use utf8; + +use YAML; + +use parent qw(SL::DBUpgrade2::Base); + +use SL::DBUtils; + +sub prepare_statements { + my ($self) = @_; + + $self->{q_draft} = qq| + SELECT description, form + FROM drafts + WHERE module = ? +|; + + $self->{q_template} = qq| + INSERT INTO record_templates ( + template_name, template_type, customer_id, vendor_id, + currency_id, department_id, project_id, employee_id, + taxincluded, direct_debit, ob_transaction, cb_transaction, + reference, description, ordnumber, notes, + ar_ap_chart_id + ) VALUES ( + ?, ? ,?, ?, + ?, ? ,?, ?, + ?, ? ,?, ?, + ?, ? ,?, ?, + ? + ) + RETURNING id +|; + + $self->{q_item} = qq| + INSERT INTO record_template_items ( + record_template_id, + chart_id, tax_id, project_id, + amount1, amount2, source, memo + ) VALUES ( + ?, + ?, ?, ?, + ?, ?, ?, ? + ) +|; + + $self->{h_draft} = $self->dbh->prepare($self->{q_draft}) || die; + $self->{h_template} = $self->dbh->prepare($self->{q_template}) || die; + $self->{h_item} = $self->dbh->prepare($self->{q_item}) || die; +} + +sub fetch_auxilliary_data { + my ($self) = @_; + + $self->{default_currency_id} = selectfirst_hashref_query($::form, $self->dbh, qq|SELECT currency_id FROM defaults|)->{currency_id}; + $self->{chart_ids_by_accno} = { selectall_as_map($::form, $self->dbh, qq|SELECT id, accno FROM chart|, 'accno', 'id') }; + $self->{currency_ids_by_name} = { selectall_as_map($::form, $self->dbh, qq|SELECT id, name FROM currencies|, 'name', 'id') }; +} + +sub finish_statements { + my ($self) = @_; + + $self->{h_item}->finish; + $self->{h_template}->finish; + $self->{h_draft}->finish; +} + +sub migrate_ar_drafts { + my ($self) = @_; + + $self->{h_draft}->execute('ar') || die $self->{h_draft}->errstr; + + while (my $draft_record = $self->{h_draft}->fetchrow_hashref) { + my $draft = YAML::Load($draft_record->{form}); + my $currency_id = $self->{currency_ids_by_name}->{$draft->{currency}}; + my ($employee_id) = $draft->{employee_id} || (split m{--}, $draft->{employee})[1] || undef; + + next unless $currency_id; + + my @values = ( + # template_name, template_type, customer_id, vendor_id, + $draft_record->{description} // $::locale->text('unnamed record template'), + 'ar_transaction', + $draft->{customer_id} || undef, + undef, + + # currency_id, department_id, project_id, employee_id, + $currency_id, + $draft->{department_id} || undef, + $draft->{globalproject_id} || undef, + $employee_id, + + # taxincluded, direct_debit, ob_transaction, cb_transaction, + $draft->{taxincluded} ? 1 : 0, + $draft->{direct_debit} ? 1 : 0, + 0, + 0, + + # reference, description, ordnumber, notes, + undef, + undef, + $draft->{ordnumber}, + $draft->{notes}, + + # ar_ap_chart_id + $self->{chart_ids_by_accno}->{$draft->{ARselected}}, + ); + + $self->{h_template}->execute(@values) || die $self->{h_template}->errstr; + my ($template_id) = $self->{h_template}->fetchrow_array; + + foreach my $row (1..$draft->{rowcount}) { + my ($chart_accno) = split m{--}, $draft->{"AR_amount_${row}"}; + my ($tax_id) = split m{--}, $draft->{"taxchart_${row}"}; + my $chart_id = $self->{chart_ids_by_accno}->{$chart_accno // ''}; + my $amount = $::form->parse_amount($self->{format}, $draft->{"amount_${row}"}); + + # $tax_id may be 0 as there's an entry in tax with id = 0. + # $chart_id must not be 0 as there's no entry in chart with id = 0. + # No $amount means empty row. + next unless $amount && $chart_id && (($tax_id // '') ne ''); + + @values = ( + # record_template_id, + $template_id, + + # chart_id, tax_id, project_id, + $chart_id, + $tax_id, + $draft->{"project_id_${row}"} || undef, + + # amount1, amount2, source, memo + $amount, + undef, + undef, + undef, + ); + + $self->{h_item}->execute(@values) || die $self->{h_item}->errstr; + } + } +} + +sub migrate_ap_drafts { + my ($self) = @_; + + $self->{h_draft}->execute('ap') || die $self->{h_draft}->errstr; + + while (my $draft_record = $self->{h_draft}->fetchrow_hashref) { + my $draft = YAML::Load($draft_record->{form}); + my $currency_id = $self->{currency_ids_by_name}->{$draft->{currency}}; + + next unless $currency_id; + + my @values = ( + # template_name, template_type, customer_id, vendor_id, + $draft_record->{description} // $::locale->text('unnamed record template'), + 'ap_transaction', + undef, + $draft->{vendor_id} || undef, + + # currency_id, department_id, project_id, employee_id, + $currency_id, + $draft->{department_id} || undef, + $draft->{globalproject_id} || undef, + undef, + + # taxincluded, direct_debit, ob_transaction, cb_transaction, + $draft->{taxincluded} ? 1 : 0, + $draft->{direct_credit} ? 1 : 0, + 0, + 0, + + # reference, description, ordnumber, notes, + undef, + undef, + $draft->{ordnumber}, + $draft->{notes}, + + # ar_ap_chart_id + $self->{chart_ids_by_accno}->{$draft->{APselected}}, + ); + + $self->{h_template}->execute(@values) || die $self->{h_template}->errstr; + my ($template_id) = $self->{h_template}->fetchrow_array; + + foreach my $row (1..$draft->{rowcount}) { + my ($chart_accno) = split m{--}, $draft->{"AP_amount_${row}"}; + my ($tax_id) = split m{--}, $draft->{"taxchart_${row}"}; + my $chart_id = $self->{chart_ids_by_accno}->{$chart_accno // ''}; + my $amount = $::form->parse_amount($self->{format}, $draft->{"amount_${row}"}); + + # $tax_id may be 0 as there's an entry in tax with id = 0. + # $chart_id must not be 0 as there's no entry in chart with id = 0. + # No $amount means empty row. + next unless $amount && $chart_id && (($tax_id // '') ne ''); + + @values = ( + # record_template_id, + $template_id, + + # chart_id, tax_id, project_id, + $chart_id, + $tax_id, + $draft->{"project_id_${row}"} || undef, + + # amount1, amount2, source, memo + $amount, + undef, + undef, + undef, + ); + + $self->{h_item}->execute(@values) || die $self->{h_item}->errstr; + } + } +} + +sub migrate_gl_drafts { + my ($self) = @_; + + $self->{h_draft}->execute('gl') || die $self->{h_draft}->errstr; + + while (my $draft_record = $self->{h_draft}->fetchrow_hashref) { + my $draft = YAML::Load($draft_record->{form}); + + my @values = ( + # template_name, template_type, customer_id, vendor_id, + $draft_record->{description} // $::locale->text('unnamed record template'), + 'gl_transaction', + undef, + undef, + + # currency_id, department_id, project_id, employee_id, + $self->{default_currency_id}, + $draft->{department_id} || undef, + undef, + undef, + + # taxincluded, direct_debit, ob_transaction, cb_transaction, + $draft->{taxincluded} ? 1 : 0, + 0, + $draft->{ob_transaction} ? 1 : 0, + $draft->{cb_transaction} ? 1 : 0, + + # reference, description, ordnumber, notes, + $draft->{reference}, + $draft->{description}, + undef, + undef, + + # ar_ap_chart_id + undef, + ); + + $self->{h_template}->execute(@values) || die $self->{h_template}->errstr; + my ($template_id) = $self->{h_template}->fetchrow_array; + + foreach my $row (1..$draft->{rowcount}) { + my ($chart_accno) = split m{--}, $draft->{"accno_${row}"}; + my ($tax_id) = split m{--}, $draft->{"taxchart_${row}"}; + my $chart_id = $self->{chart_ids_by_accno}->{$chart_accno // ''}; + my $debit = $::form->parse_amount($self->{format}, $draft->{"debit_${row}"}); + my $credit = $::form->parse_amount($self->{format}, $draft->{"credit_${row}"}); + + # $tax_id may be 0 as there's an entry in tax with id = 0. + # $chart_id must not be 0 as there's no entry in chart with id = 0. + # No $debit and no $credit means empty row. + next unless ($debit || $credit) && $chart_id && (($tax_id // '') ne ''); + + @values = ( + # record_template_id, + $template_id, + + # chart_id, tax_id, project_id, + $chart_id, + $tax_id, + $draft->{"project_id_${row}"} || undef, + + # amount1, amount2, source, memo + $debit, + $credit, + $draft->{"source_${row}"}, + $draft->{"memo_${row}"}, + ); + + $self->{h_item}->execute(@values) || die $self->{h_item}->errstr; + } + } +} + +sub clean_drafts { + my ($self) = @_; + + $self->db_query(qq|DELETE FROM drafts WHERE module IN ('ar', 'ap', 'gl')|); +} + +sub run { + my ($self) = @_; + + # A dummy for %::myconfig used for parsing numbers. The existing + # drafts have a fundamental flaw: they store numbers & dates in the + # database still formatted to the user's preferences. Determining + # the correct format is not possible. Therefore this script simply + # assumes that the installation is used by people with German + # preferences regarding both settings. + $self->{format} = { + numberformat => '1000,00', + dateformat => 'dd.mm.yy', + }; + + $self->prepare_statements; + $self->fetch_auxilliary_data; + $self->migrate_ar_drafts; + $self->migrate_ap_drafts; + $self->migrate_gl_drafts; + # $self->clean_drafts; + $self->finish_statements; + + # die "boom!"; + + return 1; +} + +1; -- 2.20.1