From a4d740099476b4383a40a21ab5472e75806f3f4f Mon Sep 17 00:00:00 2001 From: Niclas Zimmermann Date: Mon, 6 May 2013 14:50:48 +0200 Subject: [PATCH] Verbesserungen in Upgrade-Scripten MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit In den Scripten currencies.pl rm_whitespaces wurden einige Ver- besserungen durchgeführt: 1. Umbenennung von Spalten curr in currency_id in allen Tabellen außer currencies. 2. Umbenennung von Spalte curr in name in Tabelle currencies. 3. Update in rm_whitespaces wird jetzt in einer Schleife erledigt anstatt für jede Tabelle eigenen Code zu schreiben. 4. Tabelle currencies hat jetzt id als Primärschlüssel und nicht mehr curr. Die oben aufgeführten Änderungen führen natürlich auch zu notwendigen Änderungen im Programm, welche dieses Commit ebenfalls enthält. --- SL/AM.pm | 8 +-- SL/AP.pm | 2 +- SL/AR.pm | 2 +- SL/CP.pm | 10 ++-- SL/CT.pm | 6 +-- SL/DN.pm | 6 +-- SL/DO.pm | 4 +- SL/Form.pm | 32 ++++++------ SL/IR.pm | 10 ++-- SL/IS.pm | 12 ++--- SL/OE.pm | 8 +-- sql/Pg-upgrade2/currencies.pl | 82 ++++++++++++++----------------- sql/Pg-upgrade2/rm_whitespaces.pl | 20 +++----- 13 files changed, 92 insertions(+), 110 deletions(-) diff --git a/SL/AM.pm b/SL/AM.pm index e4727047c..fb600ba62 100644 --- a/SL/AM.pm +++ b/SL/AM.pm @@ -1103,13 +1103,13 @@ sub save_defaults { for my $i (1..$form->{rowcount}) { if ($form->{"curr_$i"} ne $form->{"old_curr_$i"}) { - $query = qq|UPDATE currencies SET curr = '| . $form->{"curr_$i"} . qq|' WHERE curr = '| . $form->{"old_curr_$i"} . qq|'|; + $query = qq|UPDATE currencies SET name = '| . $form->{"curr_$i"} . qq|' WHERE name = '| . $form->{"old_curr_$i"} . qq|'|; do_query($form, $dbh, $query); } } if (length($form->{new_curr}) > 0) { - $query = qq|INSERT INTO currencies (curr) VALUES ('| . $form->{new_curr} . qq|')|; + $query = qq|INSERT INTO currencies (name) VALUES ('| . $form->{new_curr} . qq|')|; do_query($form, $dbh, $query); } @@ -1292,7 +1292,7 @@ sub defaultaccounts { $sth->finish; #Get currencies: - $query = qq|SELECT curr FROM currencies ORDER BY id|; + $query = qq|SELECT name AS curr FROM currencies ORDER BY id|; $form->{CURRENCIES} = []; @@ -1304,7 +1304,7 @@ sub defaultaccounts { $sth->finish; #Which of them is the default currency? - $query = qq|SELECT curr AS defaultcurrency FROM currencies WHERE id = (SELECT curr FROM defaults LIMIT 1);|; + $query = qq|SELECT name AS defaultcurrency FROM currencies WHERE id = (SELECT currency_id FROM defaults LIMIT 1);|; $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); diff --git a/SL/AP.pm b/SL/AP.pm index 810a15e52..ac12c2902 100644 --- a/SL/AP.pm +++ b/SL/AP.pm @@ -184,7 +184,7 @@ sub post_transaction { $query = qq|UPDATE ap SET invnumber = ?, transdate = ?, ordnumber = ?, vendor_id = ?, taxincluded = ?, amount = ?, duedate = ?, paid = ?, netamount = ?, - curr = (SELECT id FROM currencies WHERE curr = ?), notes = ?, department_id = ?, storno = ?, storno_id = ?, + currency_id = (SELECT id FROM currencies WHERE name = ?), notes = ?, department_id = ?, storno = ?, storno_id = ?, globalproject_id = ?, direct_debit = ? WHERE id = ?|; @values = ($form->{invnumber}, conv_date($form->{transdate}), diff --git a/SL/AR.pm b/SL/AR.pm index 3def7597a..1626c5863 100644 --- a/SL/AR.pm +++ b/SL/AR.pm @@ -134,7 +134,7 @@ sub post_transaction { } else { $query = qq|SELECT nextval('glid')|; ($form->{id}) = selectrow_query($form, $dbh, $query); - $query = qq|INSERT INTO ar (id, invnumber, employee_id, curr) VALUES (?, 'dummy', ?, (SELECT id FROM currencies WHERE curr=?))|; + $query = qq|INSERT INTO ar (id, invnumber, employee_id, currency_id) VALUES (?, 'dummy', ?, (SELECT id FROM currencies WHERE name=?))|; do_query($form, $dbh, $query, $form->{id}, $form->{employee_id}, $form->{currency}); $form->{invnumber} = $form->update_defaults($myconfig, "invnumber", $dbh) unless $form->{invnumber}; } diff --git a/SL/CP.pm b/SL/CP.pm index 91e032fe7..29ac35ab0 100644 --- a/SL/CP.pm +++ b/SL/CP.pm @@ -149,10 +149,10 @@ sub get_openinvoices { my $arap = $form->{arap} eq "ar" ? "ar" : "ap"; my $query = - qq|SELECT a.id, a.invnumber, a.transdate, a.amount, a.paid, cu.curr | . + qq|SELECT a.id, a.invnumber, a.transdate, a.amount, a.paid, cu.name AS curr | . qq|FROM $arap a | . - qq|LEFT JOIN currencies cu ON (cu.id=a.curr)| . - qq|WHERE (a.${vc}_id = ?) AND cu.curr = ? AND NOT (a.amount = a.paid)| . + qq|LEFT JOIN currencies cu ON (cu.id=a.currency_id)| . + qq|WHERE (a.${vc}_id = ?) AND cu.name = ? AND NOT (a.amount = a.paid)| . qq|ORDER BY a.id|; my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"${vc}_id"}), @@ -174,7 +174,7 @@ sub get_openinvoices { SELECT COUNT(*) FROM $arap WHERE (${vc}_id = ?) - AND ((SELECT cu.curr FROM currencies cu WHERE cu.id=${arap}.curr) <> ?) + AND ((SELECT cu.name FROM currencies cu WHERE cu.id=${arap}.currency_id) <> ?) AND (amount <> paid) SQL ($form->{openinvoices_other_currencies}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{"${vc}_id"}), "$form->{currency}"); @@ -250,7 +250,7 @@ sub process_payment { qq|SELECT $buysell | . qq|FROM exchangerate e | . qq|JOIN ${arap} a ON (a.transdate = e.transdate) | . - qq|WHERE (e.curr = (SELECT id FROM currencies WHERE curr = ?)) AND (a.id = ?)|; + qq|WHERE (e.currency_id = (SELECT id FROM currencies WHERE name = ?)) AND (a.id = ?)|; my ($exchangerate) = selectrow_query($form, $dbh, $query, $form->{currency}, $form->{"id_$i"}); diff --git a/SL/CT.pm b/SL/CT.pm index 37550e4af..bc307098f 100644 --- a/SL/CT.pm +++ b/SL/CT.pm @@ -71,7 +71,7 @@ sub get_tuple { $sth->finish; #get name of currency instead of id: - $query = qq|SELECT curr FROM currencies WHERE id=?|; + $query = qq|SELECT name AS curr FROM currencies WHERE id=?|; ($form->{curr}) = selectrow_query($form, $dbh, $query, conv_i($form->{curr})); if ( $form->{salesman_id} ) { @@ -320,7 +320,7 @@ sub save_customer { qq|user_password = ?, | . qq|c_vendor_id = ?, | . qq|klass = ?, | . - qq|curr = (SELECT id FROM currencies WHERE curr = ?), | . + qq|currency_id = (SELECT id FROM currencies WHERE name = ?), | . qq|taxincluded_checked = ? | . qq|WHERE id = ?|; my @values = ( @@ -472,7 +472,7 @@ sub save_vendor { qq| username = ?, | . qq| user_password = ?, | . qq| v_customer_id = ?, | . - qq| curr = (SELECT id FROM currencies WHERE curr = ?) | . + qq| currency_id = (SELECT id FROM currencies WHERE name = ?) | . qq|WHERE id = ?|; my @values = ( $form->{vendornumber}, diff --git a/SL/DN.pm b/SL/DN.pm index a960a4c00..8eb407b60 100644 --- a/SL/DN.pm +++ b/SL/DN.pm @@ -208,7 +208,7 @@ sub create_invoice_for_fees { $query = qq|INSERT INTO ar (id, invnumber, transdate, gldate, customer_id, taxincluded, amount, netamount, paid, duedate, - invoice, curr, notes, + invoice, currency_id, notes, employee_id) VALUES ( ?, -- id @@ -228,7 +228,7 @@ sub create_invoice_for_fees { -- duedate: (SELECT duedate FROM dunning WHERE dunning_id = ? LIMIT 1), 'f', -- invoice - (SELECT id FROM currencies WHERE curr = ?), -- curr + (SELECT id FROM currencies WHERE name = ?), -- curr ?, -- notes -- employee_id: (SELECT id FROM employee WHERE login = ?) @@ -761,7 +761,7 @@ sub print_dunning { ar.transdate, ar.duedate, ar.customer_id, ar.invnumber, ar.ordnumber, ar.cp_id, ar.amount, ar.netamount, ar.paid, - (SELECT cu.curr FROM currencies cu WHERE cu.id=ar.curr) AS curr, + (SELECT cu.name FROM currencies cu WHERE cu.id=ar.currency_id) AS curr, ar.amount - ar.paid AS open_amount, ar.amount - ar.paid + da.fee + da.interest AS linetotal diff --git a/SL/DO.pm b/SL/DO.pm index 2eb93376f..75ab8c3f8 100644 --- a/SL/DO.pm +++ b/SL/DO.pm @@ -348,7 +348,7 @@ sub save { shippingpoint = ?, shipvia = ?, notes = ?, intnotes = ?, closed = ?, delivered = ?, department_id = ?, language_id = ?, shipto_id = ?, globalproject_id = ?, employee_id = ?, salesman_id = ?, cp_id = ?, transaction_description = ?, - is_sales = ?, taxzone_id = ?, taxincluded = ?, terms = ?, curr = (SELECT id FROM currencies WHERE curr = ?) + is_sales = ?, taxzone_id = ?, taxincluded = ?, terms = ?, currency_id = (SELECT id FROM currencies WHERE name = ?) WHERE id = ?|; @values = ($form->{donumber}, $form->{ordnumber}, @@ -616,7 +616,7 @@ sub retrieve { d.description AS department, dord.language_id, dord.shipto_id, dord.globalproject_id, dord.delivered, dord.transaction_description, - dord.taxzone_id, dord.taxincluded, dord.terms, (SELECT cu.curr FROM currencies cu WHERE cu.id=dord.curr) AS currency + dord.taxzone_id, dord.taxincluded, dord.terms, (SELECT cu.name FROM currencies cu WHERE cu.id=dord.currency_id) AS currency FROM delivery_orders dord JOIN ${vc} cv ON (dord.${vc}_id = cv.id) LEFT JOIN employee e ON (dord.employee_id = e.id) diff --git a/SL/Form.pm b/SL/Form.pm index c2385ce79..c4c643751 100644 --- a/SL/Form.pm +++ b/SL/Form.pm @@ -1490,7 +1490,7 @@ sub update_exchangerate { $main::lxdebug->leave_sub(); return; } - $query = qq|SELECT curr FROM currencies WHERE id=(SELECT curr FROM defaults)|; + $query = qq|SELECT name AS curr FROM currencies WHERE id=(SELECT currency_id FROM defaults)|; my ($defaultcurrency) = selectrow_query($self, $dbh, $query); @@ -1499,8 +1499,8 @@ sub update_exchangerate { return; } - $query = qq|SELECT e.curr FROM exchangerate e - WHERE e.curr = (SELECT cu.id FROM currencies cu WHERE cu.curr=?) AND e.transdate = ? + $query = qq|SELECT e.currency_id FROM exchangerate e + WHERE e.currency_id = (SELECT cu.id FROM currencies cu WHERE cu.name=?) AND e.transdate = ? FOR UPDATE|; my $sth = prepare_execute_query($self, $dbh, $query, $curr, $transdate); @@ -1526,12 +1526,12 @@ sub update_exchangerate { if ($sth->fetchrow_array) { $query = qq|UPDATE exchangerate SET $set - WHERE curr = (SELECT id FROM currencies WHERE curr = ?) + WHERE currency_id = (SELECT id FROM currencies WHERE name = ?) AND transdate = ?|; } else { - $query = qq|INSERT INTO exchangerate (curr, buy, sell, transdate) - VALUES ((SELECT id FROM currencies WHERE curr = ?), $buy, $sell, ?)|; + $query = qq|INSERT INTO exchangerate (currency_id, buy, sell, transdate) + VALUES ((SELECT id FROM currencies WHERE name = ?), $buy, $sell, ?)|; } $sth->finish; do_query($self, $dbh, $query, $curr, $transdate); @@ -1571,7 +1571,7 @@ sub get_exchangerate { return 1; } - $query = qq|SELECT curr FROM currencies WHERE id = (SELECT curr FROM defaults)|; + $query = qq|SELECT name AS curr FROM currencies WHERE id = (SELECT currency_id FROM defaults)|; my ($defaultcurrency) = selectrow_query($self, $dbh, $query); @@ -1581,7 +1581,7 @@ sub get_exchangerate { } $query = qq|SELECT e.$fld FROM exchangerate e - WHERE e.curr = (SELECT id FROM currencies WHERE curr = ?) AND e.transdate = ?|; + WHERE e.currency_id = (SELECT id FROM currencies WHERE name = ?) AND e.transdate = ?|; my ($exchangerate) = selectrow_query($self, $dbh, $query, $curr, $transdate); @@ -1614,7 +1614,7 @@ sub check_exchangerate { my $dbh = $self->get_standard_dbh($myconfig); my $query = qq|SELECT e.$fld FROM exchangerate e - WHERE e.curr = (SELECT id FROM currencies WHERE curr = ?) AND e.transdate = ?|; + WHERE e.currency_id = (SELECT id FROM currencies WHERE name = ?) AND e.transdate = ?|; my ($exchangerate) = selectrow_query($self, $dbh, $query, $currency, $transdate); @@ -1631,7 +1631,7 @@ sub get_all_currencies { my $dbh = $self->get_standard_dbh($myconfig); my @currencies =(); - my $query = qq|SELECT curr FROM currencies|; + my $query = qq|SELECT name AS curr FROM currencies|; my $sth = prepare_execute_query($self, $dbh, $query); @@ -1650,7 +1650,7 @@ sub get_default_currency { my ($self, $myconfig) = @_; my $dbh = $self->get_standard_dbh($myconfig); - my $query = qq|SELECT curr FROM currencies WHERE id = (SELECT curr FROM defaults)|; + my $query = qq|SELECT name AS curr FROM currencies WHERE id = (SELECT currency_id FROM defaults)|; my ($defaultcurrency) = selectrow_query($self, $dbh, $query); @@ -2707,7 +2707,7 @@ sub create_links { $query = qq|SELECT a.cp_id, a.invnumber, a.transdate, a.${table}_id, a.datepaid, - a.duedate, a.ordnumber, a.taxincluded, (SELECT cu.curr FROM currencies cu WHERE cu.id=a.curr) AS currency, a.notes, + a.duedate, a.ordnumber, a.taxincluded, (SELECT cu.name FROM currencies cu WHERE cu.id=a.currency_id) AS currency, a.notes, a.intnotes, a.department_id, a.amount AS oldinvtotal, a.paid AS oldtotalpaid, a.employee_id, a.gldate, a.type, a.globalproject_id, ${extra_columns} @@ -2812,7 +2812,7 @@ sub create_links { $query = qq|SELECT d.closedto, d.revtrans, - (SELECT cu.curr FROM currencies cu WHERE cu.id=d.curr) AS defaultcurrency, + (SELECT cu.name FROM currencies cu WHERE cu.id=d.currency_id) AS defaultcurrency, (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno, (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno FROM defaults d|; @@ -2825,7 +2825,7 @@ sub create_links { $query = qq|SELECT current_date AS transdate, d.closedto, d.revtrans, - (SELECT cu.curr FROM currencies cu WHERE cu.id=d.curr) AS defaultcurrency, + (SELECT cu.name FROM currencies cu WHERE cu.id=d.currency_id) AS defaultcurrency, (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno, (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno FROM defaults d|; @@ -2864,7 +2864,7 @@ sub lastname_used { "a.department_id" => "department_id", "d.description" => "department", "ct.name" => $table, - "cu.curr" => "currency", + "cu.name AS curr" => "currency", "current_date + ct.terms" => "duedate", ); @@ -2899,7 +2899,7 @@ sub lastname_used { FROM $arap a LEFT JOIN $table ct ON (a.${table}_id = ct.id) LEFT JOIN department d ON (a.department_id = d.id) - LEFT JOIN currencies cu ON (cu.id=ct.curr) + LEFT JOIN currencies cu ON (cu.id=ct.currency_id) WHERE a.id = ?|; my $ref = selectfirst_hashref_query($self, $dbh, $query, $trans_id); diff --git a/SL/IR.pm b/SL/IR.pm index 0179d6e78..e46e902cc 100644 --- a/SL/IR.pm +++ b/SL/IR.pm @@ -77,7 +77,7 @@ sub post_invoice { &reverse_invoice($dbh, $form); } else { ($form->{id}) = selectrow_query($form, $dbh, qq|SELECT nextval('glid')|); - do_query($form, $dbh, qq|INSERT INTO ap (id, invnumber, curr) VALUES (?, '', (SELECT id FROM currencies WHERE curr=?))|, $form->{id}, $form->{currency}); + do_query($form, $dbh, qq|INSERT INTO ap (id, invnumber, currency_id) VALUES (?, '', (SELECT id FROM currencies WHERE name=?))|, $form->{id}, $form->{currency}); } } @@ -940,7 +940,7 @@ sub retrieve_invoice { $query = qq|SELECT cp_id, invnumber, transdate AS invdate, duedate, orddate, quodate, globalproject_id, ordnumber, quonumber, paid, taxincluded, notes, taxzone_id, storno, gldate, - intnotes, (SELECT cu.curr FROM currencies cu WHERE cu.id=ap.curr) AS currency, direct_debit + intnotes, (SELECT cu.name FROM currencies cu WHERE cu.id=ap.currency_id) AS currency, direct_debit FROM ap WHERE id = ?|; $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{id})); @@ -1087,7 +1087,7 @@ sub get_vendor { v.id AS vendor_id, v.name AS vendor, v.discount as vendor_discount, v.creditlimit, v.terms, v.notes AS intnotes, v.email, v.cc, v.bcc, v.language_id, v.payment_id, - v.street, v.zipcode, v.city, v.country, v.taxzone_id, (SELECT cu.curr FROM currencies cu WHERE cu.id=v.curr) AS curr, v.direct_debit, + v.street, v.zipcode, v.city, v.country, v.taxzone_id, (SELECT cu.name FROM currencies cu WHERE cu.id=v.currency_id) AS curr, v.direct_debit, $duedate + COALESCE(pt.terms_netto, 0) AS duedate, b.description AS business FROM vendor v @@ -1109,7 +1109,7 @@ sub get_vendor { $query = qq|SELECT o.amount, (SELECT e.sell FROM exchangerate e - WHERE (e.curr = o.curr) + WHERE (e.currency_id = o.currency_id) AND (e.transdate = o.transdate)) AS exch FROM oe o WHERE (o.vendor_id = ?) AND (o.quotation = '0') AND (o.closed = '0')|; @@ -1384,7 +1384,7 @@ sub vendor_details { # fax and phone and email as vendor* my $query = qq|SELECT ct.*, cp.*, ct.notes as vendornotes, phone as vendorphone, fax as vendorfax, email as vendoremail, - (SELECT cu.curr FROM currencies cu WHERE cu.id=ct.curr) AS currency + (SELECT cu.name FROM currencies cu WHERE cu.id=ct.currency_id) AS currency FROM vendor ct LEFT JOIN contacts cp ON (ct.id = cp.cp_cv_id) WHERE (ct.id = ?) $contact diff --git a/SL/IS.pm b/SL/IS.pm index c5147b1af..46a10f4e3 100644 --- a/SL/IS.pm +++ b/SL/IS.pm @@ -458,7 +458,7 @@ sub customer_details { my $query = qq|SELECT ct.*, cp.*, ct.notes as customernotes, ct.phone AS customerphone, ct.fax AS customerfax, ct.email AS customeremail, - (SELECT cu.curr FROM currencies cu WHERE cu.id=ct.curr) AS currency + (SELECT cu.name FROM currencies cu WHERE cu.id=ct.currency_id) AS currency FROM customer ct LEFT JOIN contacts cp on ct.id = cp.cp_cv_id WHERE (ct.id = ?) $where @@ -555,7 +555,7 @@ sub post_invoice { $query = qq|SELECT nextval('glid')|; ($form->{"id"}) = selectrow_query($form, $dbh, $query); - $query = qq|INSERT INTO ar (id, invnumber, curr) VALUES (?, ?, (SELECT id FROM currencies WHERE curr=?))|; + $query = qq|INSERT INTO ar (id, invnumber, currency_id) VALUES (?, ?, (SELECT id FROM currencies WHERE name=?))|; do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"}, $form->{currency}); if (!$form->{invnumber}) { @@ -1081,7 +1081,7 @@ sub post_invoice { amount = ?, netamount = ?, paid = ?, duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?, shipvia = ?, terms = ?, notes = ?, intnotes = ?, - curr = (SELECT id FROM currencies WHERE curr= ?), + currency_id = (SELECT id FROM currencies WHERE name = ?), department_id = ?, payment_id = ?, taxincluded = ?, type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?, employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?, @@ -1574,7 +1574,7 @@ sub retrieve_invoice { a.orddate, a.quodate, a.globalproject_id, a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate, a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id, - a.duedate, a.taxincluded, (SELECT cu.curr FROM currencies cu WHERE cu.id=a.curr) AS currency, a.shipto_id, a.cp_id, + a.duedate, a.taxincluded, (SELECT cu.name FROM currencies cu WHERE cu.id=a.currency_id) AS currency, a.shipto_id, a.cp_id, a.employee_id, a.salesman_id, a.payment_id, a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type, a.transaction_description, a.donumber, a.invnumber_for_credit_note, @@ -1750,7 +1750,7 @@ sub get_customer { c.id AS customer_id, c.name AS customer, c.discount as customer_discount, c.creditlimit, c.terms, c.email, c.cc, c.bcc, c.language_id, c.payment_id, c.street, c.zipcode, c.city, c.country, - c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id, (SELECT cu.curr FROM currencies cu WHERE cu.id=c.curr) AS curr, + c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id, (SELECT cu.name FROM currencies cu WHERE cu.id=c.currency_id) AS curr, c.taxincluded_checked, c.direct_debit, $duedate + COALESCE(pt.terms_netto, 0) AS duedate, b.discount AS tradediscount, b.description AS business @@ -1795,7 +1795,7 @@ sub get_customer { $query = qq|SELECT o.amount, (SELECT e.buy FROM exchangerate e - WHERE e.curr = o.curr + WHERE e.currency_id = o.currency_id AND e.transdate = o.transdate) FROM oe o WHERE o.customer_id = ? diff --git a/SL/OE.pm b/SL/OE.pm index c5a8c26ae..d90b6aa00 100644 --- a/SL/OE.pm +++ b/SL/OE.pm @@ -91,7 +91,7 @@ sub transactions { qq|JOIN $vc ct ON (o.${vc}_id = ct.id) | . qq|LEFT JOIN employee e ON (o.employee_id = e.id) | . qq|LEFT JOIN employee s ON (o.salesman_id = s.id) | . - qq|LEFT JOIN exchangerate ex ON (ex.curr = o.curr | . + qq|LEFT JOIN exchangerate ex ON (ex.currency_id = o.currency_id | . qq| AND ex.transdate = o.transdate) | . qq|LEFT JOIN project pr ON (o.globalproject_id = pr.id) | . qq|$periodic_invoices_joins | . @@ -311,7 +311,7 @@ sub save { $query = qq|SELECT nextval('id')|; ($form->{id}) = selectrow_query($form, $dbh, $query); - $query = qq|INSERT INTO oe (id, ordnumber, employee_id, curr) VALUES (?, '', ?, (SELECT curr FROM defaults))|; + $query = qq|INSERT INTO oe (id, ordnumber, employee_id, currency_id) VALUES (?, '', ?, (SELECT currency_id FROM defaults))|; do_query($form, $dbh, $query, $form->{id}, $form->{employee_id}); } @@ -494,7 +494,7 @@ sub save { qq|UPDATE oe SET ordnumber = ?, quonumber = ?, cusordnumber = ?, transdate = ?, vendor_id = ?, customer_id = ?, amount = ?, netamount = ?, reqdate = ?, taxincluded = ?, - shippingpoint = ?, shipvia = ?, notes = ?, intnotes = ?, curr = (SELECT id FROM currencies WHERE curr=?), closed = ?, + shippingpoint = ?, shipvia = ?, notes = ?, intnotes = ?, currency_id = (SELECT id FROM currencies WHERE name=?), closed = ?, delivered = ?, proforma = ?, quotation = ?, department_id = ?, language_id = ?, taxzone_id = ?, shipto_id = ?, payment_id = ?, delivery_vendor_id = ?, delivery_customer_id = ?, globalproject_id = ?, employee_id = ?, salesman_id = ?, cp_id = ?, transaction_description = ?, marge_total = ?, marge_percent = ? @@ -784,7 +784,7 @@ sub retrieve { $query = qq|SELECT o.cp_id, o.ordnumber, o.transdate, o.reqdate, o.taxincluded, o.shippingpoint, o.shipvia, o.notes, o.intnotes, - (SELECT cu.curr FROM currencies cu WHERE cu.id=o.curr) AS currency, e.name AS employee, o.employee_id, o.salesman_id, + (SELECT cu.name FROM currencies cu WHERE cu.id=o.currency_id) AS currency, e.name AS employee, o.employee_id, o.salesman_id, o.${vc}_id, cv.name AS ${vc}, o.amount AS invtotal, o.closed, o.reqdate, o.quonumber, o.department_id, o.cusordnumber, d.description AS department, o.payment_id, o.language_id, o.taxzone_id, diff --git a/sql/Pg-upgrade2/currencies.pl b/sql/Pg-upgrade2/currencies.pl index a2c498ec0..a89784902 100644 --- a/sql/Pg-upgrade2/currencies.pl +++ b/sql/Pg-upgrade2/currencies.pl @@ -122,10 +122,11 @@ sub run { sub create_and_fill_table { my $self = shift; #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)|; + my $query = qq|CREATE TABLE currencies (id SERIAL PRIMARY KEY, + name TEXT NOT NULL UNIQUE)|; $self->db_query($query); foreach my $item ( @_ ) { - $query = qq|INSERT INTO currencies (curr) VALUES ('| . $item . qq|')|; + $query = qq|INSERT INTO currencies (name) VALUES ('| . $item . qq|')|; $self->db_query($query); } @@ -148,26 +149,26 @@ sub create_and_fill_table { $self->db_query($query); } - #Create a new columns currency and update with curr.id: - $query = qq|ALTER TABLE ap ADD currency INTEGER; - ALTER TABLE ar ADD currency INTEGER; - ALTER TABLE oe ADD currency INTEGER; - ALTER TABLE customer ADD currency INTEGER; - ALTER TABLE delivery_orders ADD currency INTEGER; - ALTER TABLE exchangerate ADD currency INTEGER; - ALTER TABLE vendor ADD currency INTEGER; - ALTER TABLE defaults ADD currency INTEGER;|; + #Create a new columns currency_id and update with curr.id: + $query = qq|ALTER TABLE ap ADD currency_id INTEGER; + ALTER TABLE ar ADD currency_id INTEGER; + ALTER TABLE oe ADD currency_id INTEGER; + ALTER TABLE customer ADD currency_id INTEGER; + ALTER TABLE delivery_orders ADD currency_id INTEGER; + ALTER TABLE exchangerate ADD currency_id INTEGER; + ALTER TABLE vendor ADD currency_id INTEGER; + ALTER TABLE defaults ADD currency_id INTEGER;|; $self->db_query($query); #Set defaultcurrency: - $query = qq|UPDATE defaults SET currency= (SELECT id FROM currencies WHERE curr = '| . $main::form->{defaultcurrency} . qq|')|; + $query = qq|UPDATE defaults SET currency_id= (SELECT id FROM currencies WHERE name = '| . $main::form->{defaultcurrency} . qq|')|; $self->db_query($query); - $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 customer SET currency = (SELECT id FROM currencies c WHERE c.curr = customer.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);|; + $query = qq|UPDATE ap SET currency_id = (SELECT id FROM currencies c WHERE c.name = ap.curr); + UPDATE ar SET currency_id = (SELECT id FROM currencies c WHERE c.name = ar.curr); + UPDATE oe SET currency_id = (SELECT id FROM currencies c WHERE c.name = oe.curr); + UPDATE customer SET currency_id = (SELECT id FROM currencies c WHERE c.name = customer.curr); + UPDATE delivery_orders SET currency_id = (SELECT id FROM currencies c WHERE c.name = delivery_orders.curr); + UPDATE exchangerate SET currency_id = (SELECT id FROM currencies c WHERE c.name = exchangerate.curr); + UPDATE vendor SET currency_id = (SELECT id FROM currencies c WHERE c.name = vendor.curr);|; $self->db_query($query); #Drop column 'curr': @@ -181,37 +182,26 @@ sub create_and_fill_table { ALTER TABLE defaults DROP COLUMN curr;|; $self->db_query($query); - #Rename currency to curr: - $query = qq|ALTER TABLE defaults RENAME COLUMN currency TO curr; - ALTER TABLE ap RENAME COLUMN currency TO curr; - ALTER TABLE ar RENAME COLUMN currency TO curr; - ALTER TABLE oe RENAME COLUMN currency TO curr; - ALTER TABLE customer 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;|; - $self->db_query($query); - #Set NOT NULL constraints: - $query = qq|ALTER TABLE ap ALTER COLUMN curr SET NOT NULL; - ALTER TABLE ar ALTER COLUMN curr SET NOT NULL; - ALTER TABLE oe ALTER COLUMN curr SET NOT NULL; - ALTER TABLE customer ALTER COLUMN curr SET NOT NULL; - ALTER TABLE delivery_orders 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;|; + $query = qq|ALTER TABLE ap ALTER COLUMN currency_id SET NOT NULL; + ALTER TABLE ar ALTER COLUMN currency_id SET NOT NULL; + ALTER TABLE oe ALTER COLUMN currency_id SET NOT NULL; + ALTER TABLE customer ALTER COLUMN currency_id SET NOT NULL; + ALTER TABLE delivery_orders ALTER COLUMN currency_id SET NOT NULL; + ALTER TABLE exchangerate ALTER COLUMN currency_id SET NOT NULL; + ALTER TABLE vendor ALTER COLUMN currency_id SET NOT NULL; + ALTER TABLE defaults ALTER COLUMN currency_id SET NOT NULL;|; $self->db_query($query); #Set foreign keys: - $query = qq|ALTER TABLE ap ADD FOREIGN KEY (curr) REFERENCES currencies(id); - ALTER TABLE ar ADD FOREIGN KEY (curr) REFERENCES currencies(id); - ALTER TABLE oe ADD FOREIGN KEY (curr) REFERENCES currencies(id); - ALTER TABLE customer ADD FOREIGN KEY (curr) REFERENCES currencies(id); - ALTER TABLE delivery_orders 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);|; + $query = qq|ALTER TABLE ap ADD FOREIGN KEY (currency_id) REFERENCES currencies(id); + ALTER TABLE ar ADD FOREIGN KEY (currency_id) REFERENCES currencies(id); + ALTER TABLE oe ADD FOREIGN KEY (currency_id) REFERENCES currencies(id); + ALTER TABLE customer ADD FOREIGN KEY (currency_id) REFERENCES currencies(id); + ALTER TABLE delivery_orders ADD FOREIGN KEY (currency_id) REFERENCES currencies(id); + ALTER TABLE exchangerate ADD FOREIGN KEY (currency_id) REFERENCES currencies(id); + ALTER TABLE vendor ADD FOREIGN KEY (currency_id) REFERENCES currencies(id); + ALTER TABLE defaults ADD FOREIGN KEY (currency_id) REFERENCES currencies(id);|; $self->db_query($query); }; diff --git a/sql/Pg-upgrade2/rm_whitespaces.pl b/sql/Pg-upgrade2/rm_whitespaces.pl index c23489751..c63758a6e 100644 --- a/sql/Pg-upgrade2/rm_whitespaces.pl +++ b/sql/Pg-upgrade2/rm_whitespaces.pl @@ -11,20 +11,12 @@ use strict; sub run { my ($self) = @_; - my $query = qq|UPDATE ar SET curr = RTRIM(LTRIM(curr))|; - $self->db_query($query); - $query = qq|UPDATE ap SET curr = RTRIM(LTRIM(curr))|; - $self->db_query($query); - $query = qq|UPDATE oe SET curr = RTRIM(LTRIM(curr))|; - $self->db_query($query); - $query = qq|UPDATE customer SET curr = RTRIM(LTRIM(curr))|; - $self->db_query($query); - $query = qq|UPDATE delivery_orders SET curr = RTRIM(LTRIM(curr))|; - $self->db_query($query); - $query = qq|UPDATE exchangerate SET curr = RTRIM(LTRIM(curr))|; - $self->db_query($query); - $query = qq|UPDATE vendor SET curr = RTRIM(LTRIM(curr))|; - $self->db_query($query); + + my $query; + + foreach my $table (qw(ar ap oe customer delivery_orders exchangerate vendor)){ + $self->db_query(qq|UPDATE ${table} SET curr=BTRIM(curr)|) + } $query = qq|SELECT curr FROM defaults|; my ($curr) = $self->dbh->selectrow_array($query); -- 2.20.1