1 # @tag: convert_taxzone
2 # @description: Setzt Fremdschlüssel und andere constraints auf die Tabellen tax und taxkeys
3 # @depends: taxzone_charts
4 package SL::DBUpgrade2::convert_taxzone;
9 use parent qw(SL::DBUpgrade2::Base);
14 # extract all buchungsgruppen data
15 my $buchungsgruppen_query = <<SQL;
16 SELECT * from buchungsgruppen;
19 my $sth = $self->dbh->prepare($buchungsgruppen_query);
20 $sth->execute || $::form->dberror($buchungsgruppen_query);
22 $::form->{buchungsgruppen} = [];
23 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
24 push @{ $::form->{buchungsgruppen} }, $ref;
28 # extract all tax_zone data
29 my $taxzone_query = <<SQL;
30 SELECT * from tax_zones;
33 $sth = $self->dbh->prepare($taxzone_query);
34 $sth->execute || $::form->dberror($taxzone_query);
36 $::form->{taxzones} = [];
37 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
38 push @{ $::form->{taxzones} }, $ref;
42 # convert Buchungsgruppen to taxzone_charts if any exist
43 # the default swiss COA doesn't have any, for example
44 if ( scalar @{ $::form->{buchungsgruppen} } > 0 ) {
45 my $taxzone_charts_update_query;
46 foreach my $taxzone ( @{$::form->{taxzones}} ) {
47 foreach my $buchungsgruppe ( @{$::form->{buchungsgruppen}} ) {
48 my $id = $taxzone->{id};
49 my $income_accno_id = $buchungsgruppe->{"income_accno_id_$id"};
50 my $expense_accno_id = $buchungsgruppe->{"expense_accno_id_$id"};
51 # TODO: check if the variables have a value
52 $taxzone_charts_update_query .= "INSERT INTO taxzone_charts (taxzone_id, buchungsgruppen_id, income_accno_id, expense_accno_id) VALUES ('$taxzone->{id}', '$buchungsgruppe->{id}', $income_accno_id, $expense_accno_id);\n";
55 $self->db_query($taxzone_charts_update_query) if $taxzone_charts_update_query;
58 my $clean_buchungsgruppen_query = <<SQL;
59 alter table buchungsgruppen drop column income_accno_id_0;
60 alter table buchungsgruppen drop column income_accno_id_1;
61 alter table buchungsgruppen drop column income_accno_id_2;
62 alter table buchungsgruppen drop column income_accno_id_3;
63 alter table buchungsgruppen drop column expense_accno_id_0;
64 alter table buchungsgruppen drop column expense_accno_id_1;
65 alter table buchungsgruppen drop column expense_accno_id_2;
66 alter table buchungsgruppen drop column expense_accno_id_3;
68 $sth = $self->dbh->prepare($clean_buchungsgruppen_query);
69 $sth->execute || $::form->dberror($clean_buchungsgruppen_query);