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;
9 use parent qw(SL::DBUpgrade2::Base);
14 sub prepare_statements {
17 $self->{q_draft} = qq|
18 SELECT description, form, employee_id
23 $self->{q_template} = qq|
24 INSERT INTO record_templates (
25 template_name, template_type, customer_id, vendor_id,
26 currency_id, department_id, project_id, employee_id,
27 taxincluded, direct_debit, ob_transaction, cb_transaction,
28 reference, description, ordnumber, notes,
41 INSERT INTO record_template_items (
43 chart_id, tax_id, project_id,
44 amount1, amount2, source, memo
52 $self->{h_draft} = $self->dbh->prepare($self->{q_draft}) || die;
53 $self->{h_template} = $self->dbh->prepare($self->{q_template}) || die;
54 $self->{h_item} = $self->dbh->prepare($self->{q_item}) || die;
57 sub fetch_auxilliary_data {
60 $self->{default_currency_id} = selectfirst_hashref_query($::form, $self->dbh, qq|SELECT currency_id FROM defaults|)->{currency_id};
61 $self->{chart_ids_by_accno} = { selectall_as_map($::form, $self->dbh, qq|SELECT id, accno FROM chart|, 'accno', 'id') };
62 $self->{currency_ids_by_name} = { selectall_as_map($::form, $self->dbh, qq|SELECT id, name FROM currencies|, 'name', 'id') };
65 sub finish_statements {
68 $self->{h_item}->finish;
69 $self->{h_template}->finish;
70 $self->{h_draft}->finish;
73 sub migrate_ar_drafts {
76 $self->{h_draft}->execute('ar') || die $self->{h_draft}->errstr;
78 while (my $draft_record = $self->{h_draft}->fetchrow_hashref) {
79 my $draft = SL::YAML::Load($draft_record->{form});
80 my $currency_id = $self->{currency_ids_by_name}->{$draft->{currency}};
81 my $employee_id = $draft_record->{employee_id} || $draft->{employee_id} || (split m{--}, $draft->{employee})[1] || undef;
83 next unless $currency_id;
86 # template_name, template_type, customer_id, vendor_id,
87 $draft_record->{description} // $::locale->text('unnamed record template'),
89 $draft->{customer_id} || undef,
92 # currency_id, department_id, project_id, employee_id,
94 $draft->{department_id} || undef,
95 $draft->{globalproject_id} || undef,
98 # taxincluded, direct_debit, ob_transaction, cb_transaction,
99 $draft->{taxincluded} ? 1 : 0,
100 $draft->{direct_debit} ? 1 : 0,
104 # reference, description, ordnumber, notes,
111 $self->{chart_ids_by_accno}->{$draft->{ARselected}},
114 $self->{h_template}->execute(@values) || die $self->{h_template}->errstr;
115 my ($template_id) = $self->{h_template}->fetchrow_array;
117 foreach my $row (1..$draft->{rowcount}) {
118 my ($chart_accno) = split m{--}, $draft->{"AR_amount_${row}"};
119 my ($tax_id) = split m{--}, $draft->{"taxchart_${row}"};
120 my $chart_id = $self->{chart_ids_by_accno}->{$chart_accno // ''};
121 my $amount = $::form->parse_amount($self->{format}, $draft->{"amount_${row}"});
123 # $tax_id may be 0 as there's an entry in tax with id = 0.
124 # $chart_id must not be 0 as there's no entry in chart with id = 0.
125 next unless $chart_id && (($tax_id // '') ne '');
128 # record_template_id,
131 # chart_id, tax_id, project_id,
134 $draft->{"project_id_${row}"} || undef,
136 # amount1, amount2, source, memo
143 $self->{h_item}->execute(@values) || die $self->{h_item}->errstr;
148 sub migrate_ap_drafts {
151 $self->{h_draft}->execute('ap') || die $self->{h_draft}->errstr;
153 while (my $draft_record = $self->{h_draft}->fetchrow_hashref) {
154 my $draft = SL::YAML::Load($draft_record->{form});
155 my $currency_id = $self->{currency_ids_by_name}->{$draft->{currency}};
156 my $employee_id = $draft_record->{employee_id} || $draft->{employee_id} || (split m{--}, $draft->{employee})[1] || undef;
158 next unless $currency_id;
161 # template_name, template_type, customer_id, vendor_id,
162 $draft_record->{description} // $::locale->text('unnamed record template'),
165 $draft->{vendor_id} || undef,
167 # currency_id, department_id, project_id, employee_id,
169 $draft->{department_id} || undef,
170 $draft->{globalproject_id} || undef,
173 # taxincluded, direct_debit, ob_transaction, cb_transaction,
174 $draft->{taxincluded} ? 1 : 0,
175 $draft->{direct_credit} ? 1 : 0,
179 # reference, description, ordnumber, notes,
186 $self->{chart_ids_by_accno}->{$draft->{APselected}},
189 $self->{h_template}->execute(@values) || die $self->{h_template}->errstr;
190 my ($template_id) = $self->{h_template}->fetchrow_array;
192 foreach my $row (1..$draft->{rowcount}) {
193 my ($chart_accno) = split m{--}, $draft->{"AP_amount_${row}"};
194 my ($tax_id) = split m{--}, $draft->{"taxchart_${row}"};
195 my $chart_id = $self->{chart_ids_by_accno}->{$chart_accno // ''};
196 my $amount = $::form->parse_amount($self->{format}, $draft->{"amount_${row}"});
198 # $tax_id may be 0 as there's an entry in tax with id = 0.
199 # $chart_id must not be 0 as there's no entry in chart with id = 0.
200 next unless $chart_id && (($tax_id // '') ne '');
203 # record_template_id,
206 # chart_id, tax_id, project_id,
209 $draft->{"project_id_${row}"} || undef,
211 # amount1, amount2, source, memo
218 $self->{h_item}->execute(@values) || die $self->{h_item}->errstr;
223 sub migrate_gl_drafts {
226 $self->{h_draft}->execute('gl') || die $self->{h_draft}->errstr;
228 while (my $draft_record = $self->{h_draft}->fetchrow_hashref) {
229 my $draft = SL::YAML::Load($draft_record->{form});
230 my $employee_id = $draft_record->{employee_id} || $draft->{employee_id} || (split m{--}, $draft->{employee})[1] || undef;
233 # template_name, template_type, customer_id, vendor_id,
234 $draft_record->{description} // $::locale->text('unnamed record template'),
239 # currency_id, department_id, project_id, employee_id,
240 $self->{default_currency_id},
241 $draft->{department_id} || undef,
245 # taxincluded, direct_debit, ob_transaction, cb_transaction,
246 $draft->{taxincluded} ? 1 : 0,
248 $draft->{ob_transaction} ? 1 : 0,
249 $draft->{cb_transaction} ? 1 : 0,
251 # reference, description, ordnumber, notes,
253 $draft->{description},
261 $self->{h_template}->execute(@values) || die $self->{h_template}->errstr;
262 my ($template_id) = $self->{h_template}->fetchrow_array;
264 foreach my $row (1..$draft->{rowcount}) {
265 my ($chart_accno) = split m{--}, $draft->{"accno_${row}"};
266 my ($tax_id) = split m{--}, $draft->{"taxchart_${row}"};
267 my $chart_id = $self->{chart_ids_by_accno}->{$chart_accno // ''};
268 my $debit = $::form->parse_amount($self->{format}, $draft->{"debit_${row}"});
269 my $credit = $::form->parse_amount($self->{format}, $draft->{"credit_${row}"});
271 # $tax_id may be 0 as there's an entry in tax with id = 0.
272 # $chart_id must not be 0 as there's no entry in chart with id = 0.
273 next unless $chart_id && (($tax_id // '') ne '');
276 # record_template_id,
279 # chart_id, tax_id, project_id,
282 $draft->{"project_id_${row}"} || undef,
284 # amount1, amount2, source, memo
287 $draft->{"source_${row}"},
288 $draft->{"memo_${row}"},
291 $self->{h_item}->execute(@values) || die $self->{h_item}->errstr;
299 $self->db_query(qq|DELETE FROM drafts WHERE module IN ('ar', 'ap', 'gl')|);
305 # A dummy for %::myconfig used for parsing numbers. The existing
306 # drafts have a fundamental flaw: they store numbers & dates in the
307 # database still formatted to the user's preferences. Determining
308 # the correct format is not possible. Therefore this script simply
309 # assumes that the installation is used by people with German
310 # preferences regarding both settings.
312 numberformat => '1000,00',
313 dateformat => 'dd.mm.yy',
316 $self->prepare_statements;
317 $self->fetch_auxilliary_data;
318 $self->migrate_ar_drafts;
319 $self->migrate_ap_drafts;
320 $self->migrate_gl_drafts;
322 $self->finish_statements;