1 # @tag: convert_drafts_to_record_templates
2 # @description: Umwandlung von existierenden Entwürfen in Buchungsvorlagen für die Finanzbuchhaltung
3 # @depends: create_record_template_tables
4 package SL::DBUpgrade2::convert_drafts_to_record_templates;
11 use parent qw(SL::DBUpgrade2::Base);
15 sub prepare_statements {
18 $self->{q_draft} = qq|
19 SELECT description, form, employee_id
24 $self->{q_template} = qq|
25 INSERT INTO record_templates (
26 template_name, template_type, customer_id, vendor_id,
27 currency_id, department_id, project_id, employee_id,
28 taxincluded, direct_debit, ob_transaction, cb_transaction,
29 reference, description, ordnumber, notes,
42 INSERT INTO record_template_items (
44 chart_id, tax_id, project_id,
45 amount1, amount2, source, memo
53 $self->{h_draft} = $self->dbh->prepare($self->{q_draft}) || die;
54 $self->{h_template} = $self->dbh->prepare($self->{q_template}) || die;
55 $self->{h_item} = $self->dbh->prepare($self->{q_item}) || die;
58 sub fetch_auxilliary_data {
61 $self->{default_currency_id} = selectfirst_hashref_query($::form, $self->dbh, qq|SELECT currency_id FROM defaults|)->{currency_id};
62 $self->{chart_ids_by_accno} = { selectall_as_map($::form, $self->dbh, qq|SELECT id, accno FROM chart|, 'accno', 'id') };
63 $self->{currency_ids_by_name} = { selectall_as_map($::form, $self->dbh, qq|SELECT id, name FROM currencies|, 'name', 'id') };
66 sub finish_statements {
69 $self->{h_item}->finish;
70 $self->{h_template}->finish;
71 $self->{h_draft}->finish;
74 sub migrate_ar_drafts {
77 $self->{h_draft}->execute('ar') || die $self->{h_draft}->errstr;
79 while (my $draft_record = $self->{h_draft}->fetchrow_hashref) {
80 my $draft = YAML::Load($draft_record->{form});
81 my $currency_id = $self->{currency_ids_by_name}->{$draft->{currency}};
82 my $employee_id = $draft_record->{employee_id} || $draft->{employee_id} || (split m{--}, $draft->{employee})[1] || undef;
84 next unless $currency_id;
87 # template_name, template_type, customer_id, vendor_id,
88 $draft_record->{description} // $::locale->text('unnamed record template'),
90 $draft->{customer_id} || undef,
93 # currency_id, department_id, project_id, employee_id,
95 $draft->{department_id} || undef,
96 $draft->{globalproject_id} || undef,
99 # taxincluded, direct_debit, ob_transaction, cb_transaction,
100 $draft->{taxincluded} ? 1 : 0,
101 $draft->{direct_debit} ? 1 : 0,
105 # reference, description, ordnumber, notes,
112 $self->{chart_ids_by_accno}->{$draft->{ARselected}},
115 $self->{h_template}->execute(@values) || die $self->{h_template}->errstr;
116 my ($template_id) = $self->{h_template}->fetchrow_array;
118 foreach my $row (1..$draft->{rowcount}) {
119 my ($chart_accno) = split m{--}, $draft->{"AR_amount_${row}"};
120 my ($tax_id) = split m{--}, $draft->{"taxchart_${row}"};
121 my $chart_id = $self->{chart_ids_by_accno}->{$chart_accno // ''};
122 my $amount = $::form->parse_amount($self->{format}, $draft->{"amount_${row}"});
124 # $tax_id may be 0 as there's an entry in tax with id = 0.
125 # $chart_id must not be 0 as there's no entry in chart with id = 0.
126 next unless $chart_id && (($tax_id // '') ne '');
129 # record_template_id,
132 # chart_id, tax_id, project_id,
135 $draft->{"project_id_${row}"} || undef,
137 # amount1, amount2, source, memo
144 $self->{h_item}->execute(@values) || die $self->{h_item}->errstr;
149 sub migrate_ap_drafts {
152 $self->{h_draft}->execute('ap') || die $self->{h_draft}->errstr;
154 while (my $draft_record = $self->{h_draft}->fetchrow_hashref) {
155 my $draft = YAML::Load($draft_record->{form});
156 my $currency_id = $self->{currency_ids_by_name}->{$draft->{currency}};
157 my $employee_id = $draft_record->{employee_id} || $draft->{employee_id} || (split m{--}, $draft->{employee})[1] || undef;
159 next unless $currency_id;
162 # template_name, template_type, customer_id, vendor_id,
163 $draft_record->{description} // $::locale->text('unnamed record template'),
166 $draft->{vendor_id} || undef,
168 # currency_id, department_id, project_id, employee_id,
170 $draft->{department_id} || undef,
171 $draft->{globalproject_id} || undef,
174 # taxincluded, direct_debit, ob_transaction, cb_transaction,
175 $draft->{taxincluded} ? 1 : 0,
176 $draft->{direct_credit} ? 1 : 0,
180 # reference, description, ordnumber, notes,
187 $self->{chart_ids_by_accno}->{$draft->{APselected}},
190 $self->{h_template}->execute(@values) || die $self->{h_template}->errstr;
191 my ($template_id) = $self->{h_template}->fetchrow_array;
193 foreach my $row (1..$draft->{rowcount}) {
194 my ($chart_accno) = split m{--}, $draft->{"AP_amount_${row}"};
195 my ($tax_id) = split m{--}, $draft->{"taxchart_${row}"};
196 my $chart_id = $self->{chart_ids_by_accno}->{$chart_accno // ''};
197 my $amount = $::form->parse_amount($self->{format}, $draft->{"amount_${row}"});
199 # $tax_id may be 0 as there's an entry in tax with id = 0.
200 # $chart_id must not be 0 as there's no entry in chart with id = 0.
201 next unless $chart_id && (($tax_id // '') ne '');
204 # record_template_id,
207 # chart_id, tax_id, project_id,
210 $draft->{"project_id_${row}"} || undef,
212 # amount1, amount2, source, memo
219 $self->{h_item}->execute(@values) || die $self->{h_item}->errstr;
224 sub migrate_gl_drafts {
227 $self->{h_draft}->execute('gl') || die $self->{h_draft}->errstr;
229 while (my $draft_record = $self->{h_draft}->fetchrow_hashref) {
230 my $draft = YAML::Load($draft_record->{form});
231 my $employee_id = $draft_record->{employee_id} || $draft->{employee_id} || (split m{--}, $draft->{employee})[1] || undef;
234 # template_name, template_type, customer_id, vendor_id,
235 $draft_record->{description} // $::locale->text('unnamed record template'),
240 # currency_id, department_id, project_id, employee_id,
241 $self->{default_currency_id},
242 $draft->{department_id} || undef,
246 # taxincluded, direct_debit, ob_transaction, cb_transaction,
247 $draft->{taxincluded} ? 1 : 0,
249 $draft->{ob_transaction} ? 1 : 0,
250 $draft->{cb_transaction} ? 1 : 0,
252 # reference, description, ordnumber, notes,
254 $draft->{description},
262 $self->{h_template}->execute(@values) || die $self->{h_template}->errstr;
263 my ($template_id) = $self->{h_template}->fetchrow_array;
265 foreach my $row (1..$draft->{rowcount}) {
266 my ($chart_accno) = split m{--}, $draft->{"accno_${row}"};
267 my ($tax_id) = split m{--}, $draft->{"taxchart_${row}"};
268 my $chart_id = $self->{chart_ids_by_accno}->{$chart_accno // ''};
269 my $debit = $::form->parse_amount($self->{format}, $draft->{"debit_${row}"});
270 my $credit = $::form->parse_amount($self->{format}, $draft->{"credit_${row}"});
272 # $tax_id may be 0 as there's an entry in tax with id = 0.
273 # $chart_id must not be 0 as there's no entry in chart with id = 0.
274 next unless $chart_id && (($tax_id // '') ne '');
277 # record_template_id,
280 # chart_id, tax_id, project_id,
283 $draft->{"project_id_${row}"} || undef,
285 # amount1, amount2, source, memo
288 $draft->{"source_${row}"},
289 $draft->{"memo_${row}"},
292 $self->{h_item}->execute(@values) || die $self->{h_item}->errstr;
300 $self->db_query(qq|DELETE FROM drafts WHERE module IN ('ar', 'ap', 'gl')|);
306 # A dummy for %::myconfig used for parsing numbers. The existing
307 # drafts have a fundamental flaw: they store numbers & dates in the
308 # database still formatted to the user's preferences. Determining
309 # the correct format is not possible. Therefore this script simply
310 # assumes that the installation is used by people with German
311 # preferences regarding both settings.
313 numberformat => '1000,00',
314 dateformat => 'dd.mm.yy',
317 $self->prepare_statements;
318 $self->fetch_auxilliary_data;
319 $self->migrate_ar_drafts;
320 $self->migrate_ap_drafts;
321 $self->migrate_gl_drafts;
323 $self->finish_statements;