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);
}
$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} = [];
$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);
$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}),
} 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};
}
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"}),
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}");
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"});
$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} ) {
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 = (
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},
$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
-- 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 = ?)
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
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},
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)
$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);
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);
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);
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);
}
$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);
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);
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);
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);
$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}
$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|;
$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|;
"a.department_id" => "department_id",
"d.description" => "department",
"ct.name" => $table,
- "cu.curr" => "currency",
+ "cu.name AS curr" => "currency",
"current_date + ct.terms" => "duedate",
);
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);
&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});
}
}
$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}));
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
$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')|;
# 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
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
$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}) {
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 = ?,
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,
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
$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 = ?
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 | .
$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});
}
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 = ?
$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,
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);
}
$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':
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);
};
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);
projects / kivitendo-erp.git / commitdiff