projekt kivitendo

# @tag: currencies

# @description: Erstellt neue Tabelle currencies. Währungen können dann einfacher eingegeben und unkritisch geändert werden.

# @depends: release_3_0_0 rm_whitespaces

use utf8;

use strict;

  #Check wheather default currency exists

  my $query = qq|SELECT curr FROM defaults|;

  if (length($currencies) == 0 and length($main::form->{defaultcurrency}) == 0){

    print_no_default_currency();

              UNION

              SELECT DISTINCT curr FROM vendor|;

  $sth->execute || $self->dberror($query);

  $main::form->{ORPHANED_CURRENCIES} = [];

  if ($main::form->{continue_options} eq 'insert') {

    for my $i (0..($rowcount-1)){

    }

    return 1;

  }

        if ($main::form->{"curr_$i"} eq $item){

          $still_orphaned = 0;

          $query = qq|DELETE FROM exchangerate WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;

          $query = qq|UPDATE ap SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;

          $query = qq|UPDATE ar SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;

          $query = qq|UPDATE oe SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;

          $query = qq|UPDATE customer SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;

          $query = qq|UPDATE delivery_orders SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;

          $query = qq|UPDATE vendor SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;

          last;

        }

      }

        return do_update();

      }

    }

    return 1;

  }

  #No orphaned currencies, so create table:

  return 1;

}; # end do_update

sub create_and_fill_table {

  #Create an fill table currencies:

  my $query = qq|CREATE TABLE currencies (id INTEGER DEFAULT nextval(('id'::text)::regclass) UNIQUE NOT NULL, curr TEXT PRIMARY KEY)|;

  foreach my $item ( @_ ) {

    $query = qq|INSERT INTO currencies (curr) VALUES ('| . $item . qq|')|;

  }

  #Set default currency if no currency was chosen:

  $query .= qq|UPDATE delivery_orders SET curr = '| . $main::form->{"defaultcurrency"} . qq|' WHERE curr IS NULL or curr='';|;

  $query .= qq|UPDATE vendor SET curr = '| . $main::form->{"defaultcurrency"} . qq|' WHERE curr IS NULL or curr='';|;

  $query .= qq|DELETE FROM exchangerate WHERE curr IS NULL or curr='';|;

  #Check wheather defaultcurrency is already in table currencies:

  $query = qq|SELECT curr FROM currencies WHERE curr = '| . $main::form->{defaultcurrency} . qq|'|;

  if (!$insert_default) {

    $query = qq|INSERT INTO currencies (curr) VALUES ('| . $main::form->{defaultcurrency} . qq|')|;

  }

  #Create a new columns currency and update with curr.id:

              ALTER TABLE exchangerate ADD currency INTEGER;

              ALTER TABLE vendor ADD currency INTEGER;

              ALTER TABLE defaults ADD currency INTEGER;|;

  #Set defaultcurrency:

  $query = qq|UPDATE defaults SET currency= (SELECT id FROM currencies WHERE curr = '| . $main::form->{defaultcurrency} . qq|')|;

  $query = qq|UPDATE ap SET currency = (SELECT id FROM currencies c WHERE c.curr = ap.curr);

              UPDATE ar SET currency = (SELECT id FROM currencies c WHERE c.curr = ar.curr);

              UPDATE oe SET currency = (SELECT id FROM currencies c WHERE c.curr = oe.curr);

              UPDATE delivery_orders SET currency = (SELECT id FROM currencies c WHERE c.curr = delivery_orders.curr);

              UPDATE exchangerate SET currency = (SELECT id FROM currencies c WHERE c.curr = exchangerate.curr);

              UPDATE vendor SET currency = (SELECT id FROM currencies c WHERE c.curr = vendor.curr);|;

  #Drop column 'curr':

  $query = qq|ALTER TABLE ap DROP COLUMN curr;

              ALTER TABLE exchangerate DROP COLUMN curr;

              ALTER TABLE vendor DROP COLUMN curr;

              ALTER TABLE defaults DROP COLUMN curr;|;

  #Rename currency to curr:

  $query = qq|ALTER TABLE defaults RENAME COLUMN currency TO curr;

              ALTER TABLE delivery_orders RENAME COLUMN currency TO curr;

              ALTER TABLE exchangerate RENAME COLUMN currency TO curr;

              ALTER TABLE vendor RENAME COLUMN currency TO curr;|;

  #Set NOT NULL constraints:

  $query = qq|ALTER TABLE ap ALTER COLUMN curr SET NOT NULL;

              ALTER TABLE exchangerate ALTER COLUMN curr SET NOT NULL;

              ALTER TABLE vendor ALTER COLUMN curr SET NOT NULL;

              ALTER TABLE defaults ALTER COLUMN curr SET NOT NULL;|;

  #Set foreign keys:

  $query = qq|ALTER TABLE ap ADD FOREIGN KEY (curr) REFERENCES currencies(id);

              ALTER TABLE exchangerate ADD FOREIGN KEY (curr) REFERENCES currencies(id);

              ALTER TABLE vendor ADD FOREIGN KEY (curr) REFERENCES currencies(id);

              ALTER TABLE defaults ADD FOREIGN KEY (curr) REFERENCES currencies(id);|;

};

  print $main::form->parse_html_template("dbupgrade/orphaned_currencies");

};

# @tag: rm_whitespaces

# @description: Entfernt mögliche Leerzeichen am Anfang und Ende jeder Währung

# @depends: release_3_0_0

use utf8;

use strict;

  my $query = qq|UPDATE ar SET curr = RTRIM(LTRIM(curr))|;

  $query = qq|UPDATE ap SET curr = RTRIM(LTRIM(curr))|;

  $query = qq|UPDATE oe SET curr = RTRIM(LTRIM(curr))|;

  $query = qq|UPDATE customer SET curr = RTRIM(LTRIM(curr))|;

  $query = qq|UPDATE delivery_orders SET curr = RTRIM(LTRIM(curr))|;

  $query = qq|UPDATE exchangerate SET curr = RTRIM(LTRIM(curr))|;

  $query = qq|UPDATE vendor SET curr = RTRIM(LTRIM(curr))|;

  $query = qq|SELECT curr FROM defaults|;

  $curr  =~ s/ //g;

  $query = qq|UPDATE defaults SET curr = '$curr'|;

  return 1;

};