From 9557707c3a18daeb9371f2366b8637a7e1f0fa3c Mon Sep 17 00:00:00 2001 From: Moritz Bunkus Date: Mon, 6 May 2013 08:31:26 +0200 Subject: [PATCH] Revert "Nutzung der Tabelle currencies" This reverts commit be776964b83340e69fa2f02eae59b703c0c95b88. --- SL/AM.pm | 26 ++++---- SL/AP.pm | 4 +- SL/AR.pm | 10 ++-- SL/CP.pm | 16 ++--- SL/CT.pm | 15 +++-- SL/DN.pm | 4 +- SL/DO.pm | 7 ++- SL/Form.pm | 75 +++++++++++++----------- SL/IR.pm | 32 ++++++---- SL/IS.pm | 34 +++++++---- SL/OE.pm | 16 +++-- bin/mozilla/cp.pl | 5 +- bin/mozilla/ic.pl | 3 +- bin/mozilla/ir.pl | 2 +- templates/webpages/am/edit_defaults.html | 2 +- 15 files changed, 142 insertions(+), 109 deletions(-) diff --git a/SL/AM.pm b/SL/AM.pm index e4727047c..3dbff77ff 100644 --- a/SL/AM.pm +++ b/SL/AM.pm @@ -1060,6 +1060,10 @@ sub save_defaults { my %accnos; map { ($accnos{$_}) = split(m/--/, $form->{$_}) } qw(inventory_accno income_accno expense_accno fxgain_accno fxloss_accno ar_paid_accno); + $form->{curr} =~ s/ //g; + my @currencies = grep { $_ ne '' } split m/:/, $form->{curr}; + my $currency = join ':', @currencies; + # these defaults are database wide my $query = @@ -1083,6 +1087,7 @@ sub save_defaults { assemblynumber = ?, sdonumber = ?, pdonumber = ?, + curr = ?, businessnumber = ?, weightunit = ?, language_id = ?|; @@ -1095,24 +1100,11 @@ sub save_defaults { $form->{articlenumber}, $form->{servicenumber}, $form->{assemblynumber}, $form->{sdonumber}, $form->{pdonumber}, + $currency, $form->{businessnumber}, $form->{weightunit}, conv_i($form->{language_id})); do_query($form, $dbh, $query, @values); - $main::lxdebug->message(0, "es gibt rowcount: " . $form->{rowcount}); - - 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|'|; - do_query($form, $dbh, $query); - } - } - - if (length($form->{new_curr}) > 0) { - $query = qq|INSERT INTO currencies (curr) VALUES ('| . $form->{new_curr} . qq|')|; - do_query($form, $dbh, $query); - } - $dbh->commit(); $main::lxdebug->leave_sub(); @@ -1126,7 +1118,7 @@ sub save_preferences { my $dbh = $form->get_standard_dbh($myconfig); - my ($businessnumber) = selectrow_query($form, $dbh, qq|SELECT businessnumber FROM defaults|); + my ($currency, $businessnumber) = selectrow_query($form, $dbh, qq|SELECT curr, businessnumber FROM defaults|); # update name my $query = qq|UPDATE employee SET name = ? WHERE login = ?|; @@ -1134,6 +1126,10 @@ sub save_preferences { my $rc = $dbh->commit(); + # save first currency in myconfig + $currency =~ s/:.*//; + $form->{currency} = $currency; + $form->{businessnumber} = $businessnumber; $myconfig = User->new(login => $form->{login}); diff --git a/SL/AP.pm b/SL/AP.pm index 810a15e52..54abcd17e 100644 --- a/SL/AP.pm +++ b/SL/AP.pm @@ -55,6 +55,7 @@ sub post_transaction { my $exchangerate = 0; $form->{defaultcurrency} = $form->get_default_currency($myconfig); + delete $form->{currency} unless $form->{defaultcurrency}; ($null, $form->{department_id}) = split(/--/, $form->{department}); @@ -184,7 +185,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 = ?, + curr = ?, notes = ?, department_id = ?, storno = ?, storno_id = ?, globalproject_id = ?, direct_debit = ? WHERE id = ?|; @values = ($form->{invnumber}, conv_date($form->{transdate}), @@ -664,6 +665,7 @@ sub post_payment { $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate}); $form->{defaultcurrency} = $form->get_default_currency($myconfig); + delete $form->{currency} unless $form->{defaultcurrency}; # Get the AP accno. $query = diff --git a/SL/AR.pm b/SL/AR.pm index 3def7597a..795849583 100644 --- a/SL/AR.pm +++ b/SL/AR.pm @@ -56,6 +56,7 @@ sub post_transaction { my $dbh = $provided_dbh ? $provided_dbh : $form->dbconnect_noauto($myconfig); $form->{defaultcurrency} = $form->get_default_currency($myconfig); + delete $form->{currency} unless $form->{defaultcurrency}; # set exchangerate $form->{exchangerate} = ($form->{currency} eq $form->{defaultcurrency}) ? 1 : @@ -134,8 +135,8 @@ 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=?))|; - do_query($form, $dbh, $query, $form->{id}, $form->{employee_id}, $form->{currency}); + $query = qq|INSERT INTO ar (id, invnumber, employee_id) VALUES (?, 'dummy', ?)|; + do_query($form, $dbh, $query, $form->{id}, $form->{employee_id}); $form->{invnumber} = $form->update_defaults($myconfig, "invnumber", $dbh) unless $form->{invnumber}; } } @@ -155,12 +156,12 @@ sub post_transaction { qq|UPDATE ar set invnumber = ?, ordnumber = ?, transdate = ?, customer_id = ?, taxincluded = ?, amount = ?, duedate = ?, paid = ?, - netamount = ?, notes = ?, department_id = ?, + netamount = ?, curr = ?, notes = ?, department_id = ?, employee_id = ?, storno = ?, storno_id = ?, globalproject_id = ?, direct_debit = ? WHERE id = ?|; my @values = ($form->{invnumber}, $form->{ordnumber}, conv_date($form->{transdate}), conv_i($form->{customer_id}), $form->{taxincluded} ? 't' : 'f', $form->{amount}, - conv_date($form->{duedate}), $form->{paid}, $form->{netamount}, $form->{notes}, conv_i($form->{department_id}), + conv_date($form->{duedate}), $form->{paid}, $form->{netamount}, $form->{currency}, $form->{notes}, conv_i($form->{department_id}), conv_i($form->{employee_id}), $form->{storno} ? 't' : 'f', $form->{storno_id}, conv_i($form->{globalproject_id}), $form->{direct_debit} ? 't' : 'f', conv_i($form->{id})); do_query($form, $dbh, $query, @values); @@ -425,6 +426,7 @@ sub post_payment { $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate}); $form->{defaultcurrency} = $form->get_default_currency($myconfig); + delete $form->{currency} unless $form->{defaultcurrency}; # Get the AR accno (which is normally done by Form::create_links()). $query = diff --git a/SL/CP.pm b/SL/CP.pm index 91e032fe7..18673c215 100644 --- a/SL/CP.pm +++ b/SL/CP.pm @@ -93,9 +93,10 @@ sub paymentaccounts { } $sth->finish; - # get closedto - $query = qq|SELECT closedto FROM defaults|; - ($form->{closedto}) = selectrow_query($form, $dbh, $query); + # get currencies and closedto + $query = qq|SELECT curr, closedto FROM defaults|; + ($form->{currencies}, $form->{closedto}) = + selectrow_query($form, $dbh, $query); $dbh->disconnect; @@ -149,10 +150,9 @@ 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, a.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|WHERE (a.${vc}_id = ?) AND (COALESCE(a.curr, '') = ?) 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 (COALESCE(curr, '') <> ?) 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.curr = ?) 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..97f57c72c 100644 --- a/SL/CT.pm +++ b/SL/CT.pm @@ -68,12 +68,11 @@ sub get_tuple { my $ref = $sth->fetchrow_hashref("NAME_lc"); map { $form->{$_} = $ref->{$_} } keys %$ref; - $sth->finish; - #get name of currency instead of id: - $query = qq|SELECT curr FROM currencies WHERE id=?|; - ($form->{curr}) = selectrow_query($form, $dbh, $query, conv_i($form->{curr})); + # remove any trailing whitespace + $form->{curr} =~ s/\s*$//; + $sth->finish; if ( $form->{salesman_id} ) { my $query = qq|SELECT ct.name AS salesman | . @@ -320,7 +319,7 @@ sub save_customer { qq|user_password = ?, | . qq|c_vendor_id = ?, | . qq|klass = ?, | . - qq|curr = (SELECT id FROM currencies WHERE curr = ?), | . + qq|curr = ?, | . qq|taxincluded_checked = ? | . qq|WHERE id = ?|; my @values = ( @@ -363,7 +362,7 @@ sub save_customer { $form->{user_password}, $form->{c_vendor_id}, conv_i($form->{klass}), - $form->{currency}, + substr($form->{currency}, 0, 3), $form->{taxincluded_checked} ne '' ? $form->{taxincluded_checked} : undef, $form->{id} ); @@ -472,7 +471,7 @@ sub save_vendor { qq| username = ?, | . qq| user_password = ?, | . qq| v_customer_id = ?, | . - qq| curr = (SELECT id FROM currencies WHERE curr = ?) | . + qq| curr = ? | . qq|WHERE id = ?|; my @values = ( $form->{vendornumber}, @@ -512,7 +511,7 @@ sub save_vendor { $form->{username}, $form->{user_password}, $form->{v_customer_id}, - $form->{currency}, + substr($form->{currency}, 0, 3), $form->{id} ); do_query($form, $dbh, $query, @values); diff --git a/SL/DN.pm b/SL/DN.pm index a960a4c00..46749ab37 100644 --- a/SL/DN.pm +++ b/SL/DN.pm @@ -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 + ?, -- 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, + ar.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..15e16cc16 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 = ?, curr = ? 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, dord.curr 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) @@ -638,6 +638,9 @@ sub retrieve { } $sth->finish(); + # remove any trailing whitespace + $form->{currency} =~ s/\s*$//; + $form->{donumber_array} =~ s/\s*$//g; $form->{saved_donumber} = $form->{donumber}; diff --git a/SL/Form.pm b/SL/Form.pm index c2385ce79..7b5d257b8 100644 --- a/SL/Form.pm +++ b/SL/Form.pm @@ -1490,9 +1490,11 @@ sub update_exchangerate { $main::lxdebug->leave_sub(); return; } - $query = qq|SELECT curr FROM currencies WHERE id=(SELECT curr FROM defaults)|; + $query = qq|SELECT curr FROM defaults|; + + my ($currency) = selectrow_query($self, $dbh, $query); + my ($defaultcurrency) = split m/:/, $currency; - my ($defaultcurrency) = selectrow_query($self, $dbh, $query); if ($curr eq $defaultcurrency) { $main::lxdebug->leave_sub(); @@ -1500,7 +1502,7 @@ sub update_exchangerate { } $query = qq|SELECT e.curr FROM exchangerate e - WHERE e.curr = (SELECT cu.id FROM currencies cu WHERE cu.curr=?) AND e.transdate = ? + WHERE e.curr = ? AND e.transdate = ? FOR UPDATE|; my $sth = prepare_execute_query($self, $dbh, $query, $curr, $transdate); @@ -1526,12 +1528,12 @@ sub update_exchangerate { if ($sth->fetchrow_array) { $query = qq|UPDATE exchangerate SET $set - WHERE curr = (SELECT id FROM currencies WHERE curr = ?) + WHERE curr = ? AND transdate = ?|; } else { $query = qq|INSERT INTO exchangerate (curr, buy, sell, transdate) - VALUES ((SELECT id FROM currencies WHERE curr = ?), $buy, $sell, ?)|; + VALUES (?, $buy, $sell, ?)|; } $sth->finish; do_query($self, $dbh, $query, $curr, $transdate); @@ -1571,17 +1573,18 @@ sub get_exchangerate { return 1; } - $query = qq|SELECT curr FROM currencies WHERE id = (SELECT curr FROM defaults)|; + $query = qq|SELECT curr FROM defaults|; - my ($defaultcurrency) = selectrow_query($self, $dbh, $query); + my ($currency) = selectrow_query($self, $dbh, $query); + my ($defaultcurrency) = split m/:/, $currency; - if ($curr eq $defaultcurrency) { + if ($currency eq $defaultcurrency) { $main::lxdebug->leave_sub(); return 1; } $query = qq|SELECT e.$fld FROM exchangerate e - WHERE e.curr = (SELECT id FROM currencies WHERE curr = ?) AND e.transdate = ?|; + WHERE e.curr = ? AND e.transdate = ?|; my ($exchangerate) = selectrow_query($self, $dbh, $query, $curr, $transdate); @@ -1614,7 +1617,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.curr = ? AND e.transdate = ?|; my ($exchangerate) = selectrow_query($self, $dbh, $query, $currency, $transdate); @@ -1629,16 +1632,11 @@ sub get_all_currencies { my $self = shift; my $myconfig = shift || \%::myconfig; my $dbh = $self->get_standard_dbh($myconfig); - my @currencies =(); - my $query = qq|SELECT curr FROM currencies|; + my $query = qq|SELECT curr FROM defaults|; - my $sth = prepare_execute_query($self, $dbh, $query); - - while (my $ref = $sth->fetchrow_hashref()) { - push(@currencies, $ref->{curr}); - } - $sth->finish; + my ($curr) = selectrow_query($self, $dbh, $query); + my @currencies = grep { $_ } map { s/\s//g; $_ } split m/:/, $curr; $main::lxdebug->leave_sub(); @@ -1649,14 +1647,11 @@ sub get_default_currency { $main::lxdebug->enter_sub(); my ($self, $myconfig) = @_; - my $dbh = $self->get_standard_dbh($myconfig); - my $query = qq|SELECT curr FROM currencies WHERE id = (SELECT curr FROM defaults)|; - - my ($defaultcurrency) = selectrow_query($self, $dbh, $query); + my @currencies = $self->get_all_currencies($myconfig); $main::lxdebug->leave_sub(); - return $defaultcurrency; + return $currencies[0]; } sub set_payment_options { @@ -2196,7 +2191,9 @@ $main::lxdebug->enter_sub(); $key = "all_currencies" unless ($key); - $self->{$key} = [$self->get_all_currencies()]; + my $query = qq|SELECT curr AS currency FROM defaults|; + + $self->{$key} = [split(/\:/ , selectfirst_hashref_query($self, $dbh, $query)->{currency})]; $main::lxdebug->leave_sub(); } @@ -2707,7 +2704,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, a.curr 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} @@ -2725,6 +2722,9 @@ sub create_links { $self->{$key} = $ref->{$key}; } + # remove any trailing whitespace + $self->{currency} =~ s/\s*$//; + my $transdate = "current_date"; if ($self->{transdate}) { $transdate = $dbh->quote($self->{transdate}); @@ -2808,11 +2808,9 @@ sub create_links { } $sth->finish; - #check das: $query = qq|SELECT - d.closedto, d.revtrans, - (SELECT cu.curr FROM currencies cu WHERE cu.id=d.curr) AS defaultcurrency, + d.curr AS currencies, d.closedto, d.revtrans, (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|; @@ -2824,8 +2822,7 @@ sub create_links { # get date $query = qq|SELECT - current_date AS transdate, d.closedto, d.revtrans, - (SELECT cu.curr FROM currencies cu WHERE cu.id=d.curr) AS defaultcurrency, + current_date AS transdate, d.curr AS currencies, d.closedto, d.revtrans, (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|; @@ -2835,7 +2832,7 @@ sub create_links { if ($self->{"$self->{vc}_id"}) { # only setup currency - ($self->{currency}) = $self->{defaultcurrency} if !$self->{currency}; + ($self->{currency}) = split(/:/, $self->{currencies}) if !$self->{currency}; } else { @@ -2860,17 +2857,19 @@ sub lastname_used { my ($arap, $where); $table = $table eq "customer" ? "customer" : "vendor"; - my %column_map = ("a.${table}_id" => "${table}_id", + my %column_map = ("a.curr" => "currency", + "a.${table}_id" => "${table}_id", "a.department_id" => "department_id", "d.description" => "department", "ct.name" => $table, - "cu.curr" => "currency", + "ct.curr" => "cv_curr", "current_date + ct.terms" => "duedate", ); if ($self->{type} =~ /delivery_order/) { $arap = 'delivery_orders'; - delete $column_map{"cu.currency"}; + delete $column_map{"a.curr"}; + delete $column_map{"ct.curr"}; } elsif ($self->{type} =~ /_order/) { $arap = 'oe'; @@ -2899,12 +2898,18 @@ 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) WHERE a.id = ?|; my $ref = selectfirst_hashref_query($self, $dbh, $query, $trans_id); map { $self->{$_} = $ref->{$_} } values %column_map; + # remove any trailing whitespace + $self->{currency} =~ s/\s*$// if $self->{currency}; + $self->{cv_curr} =~ s/\s*$// if $self->{cv_curr}; + + # if customer/vendor currency is set use this + $self->{currency} = $self->{cv_curr} if $self->{cv_curr}; + $main::lxdebug->leave_sub(); } diff --git a/SL/IR.pm b/SL/IR.pm index 0179d6e78..73b3e14ed 100644 --- a/SL/IR.pm +++ b/SL/IR.pm @@ -57,7 +57,6 @@ sub post_invoice { # connect to database, turn off autocommit my $dbh = $provided_dbh ? $provided_dbh : $form->dbconnect_noauto($myconfig); $form->{defaultcurrency} = $form->get_default_currency($myconfig); - my $defaultcurrency = $form->{defaultcurrency}; my $ic_cvar_configs = CVar->get_configs(module => 'IC', dbh => $dbh); @@ -71,16 +70,18 @@ sub post_invoice { my $all_units = AM->retrieve_units($myconfig, $form); -#markierung if (!$payments_only) { if ($form->{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) VALUES (?, '')|, $form->{id}); } } + my ($currencies) = selectfirst_array_query($form, $dbh, qq|SELECT curr FROM defaults|); + my $defaultcurrency = (split m/:/, $currencies)[0]; + if ($form->{currency} eq $defaultcurrency) { $form->{exchangerate} = 1; } else { @@ -685,7 +686,7 @@ sub post_invoice { orddate = ?, quodate = ?, vendor_id = ?, amount = ?, netamount = ?, paid = ?, duedate = ?, invoice = ?, taxzone_id = ?, notes = ?, taxincluded = ?, - intnotes = ?, storno_id = ?, storno = ?, + intnotes = ?, curr = ?, storno_id = ?, storno = ?, cp_id = ?, employee_id = ?, department_id = ?, globalproject_id = ?, direct_debit = ? WHERE id = ?|; @@ -694,7 +695,7 @@ sub post_invoice { conv_date($form->{orddate}), conv_date($form->{quodate}), conv_i($form->{vendor_id}), $amount, $netamount, $form->{paid}, conv_date($form->{duedate}), '1', $taxzone_id, $form->{notes}, $form->{taxincluded} ? 't' : 'f', - $form->{intnotes}, conv_i($form->{storno_id}), $form->{storno} ? 't' : 'f', + $form->{intnotes}, $form->{currency}, conv_i($form->{storno_id}), $form->{storno} ? 't' : 'f', conv_i($form->{cp_id}), conv_i($form->{employee_id}), conv_i($form->{department_id}), conv_i($form->{globalproject_id}), $form->{direct_debit} ? 't' : 'f', @@ -923,7 +924,8 @@ sub retrieve_invoice { (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno, (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno, (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 + (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno, + d.curr AS currencies $q_invdate FROM defaults d|; $ref = selectfirst_hashref_query($form, $dbh, $query); @@ -940,12 +942,15 @@ 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, curr AS currency, direct_debit FROM ap WHERE id = ?|; $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{id})); map { $form->{$_} = $ref->{$_} } keys %$ref; + # remove any trailing whitespace + $form->{currency} =~ s/\s*$//; + $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "sell"); # get shipto @@ -1087,7 +1092,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, v.curr, v.direct_debit, $duedate + COALESCE(pt.terms_netto, 0) AS duedate, b.description AS business FROM vendor v @@ -1097,8 +1102,11 @@ sub get_vendor { my $ref = selectfirst_hashref_query($form, $dbh, $query, @values); map { $params->{$_} = $ref->{$_} } keys %$ref; - # use vendor currency - $form->{currency} = $form->{curr}; + # remove any trailing whitespace + $form->{curr} =~ s/\s*$//; + + # use vendor currency if not empty + $form->{currency} = $form->{curr} if $form->{curr}; $params->{creditremaining} = $params->{creditlimit}; @@ -1384,7 +1392,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 + ct.curr AS currency FROM vendor ct LEFT JOIN contacts cp ON (ct.id = cp.cp_cv_id) WHERE (ct.id = ?) $contact @@ -1403,6 +1411,8 @@ sub vendor_details { } map { $form->{$_} = $ref->{$_} } keys %$ref; + # remove any trailing whitespace + $form->{currency} =~ s/\s*$// if ($form->{currency}); my $custom_variables = CVar->get_custom_variables('dbh' => $dbh, 'module' => 'CT', diff --git a/SL/IS.pm b/SL/IS.pm index c5147b1af..ce8db64b9 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 + ct.curr AS currency FROM customer ct LEFT JOIN contacts cp on ct.id = cp.cp_cv_id WHERE (ct.id = ?) $where @@ -478,6 +478,9 @@ sub customer_details { map { $form->{$_} = $ref->{$_} } keys %$ref; + # remove any trailing whitespace + $form->{currency} =~ s/\s*$// if ($form->{currency}); + if ($form->{delivery_customer_id}) { $query = qq|SELECT *, notes as customernotes @@ -533,8 +536,6 @@ sub post_invoice { } $form->{defaultcurrency} = $form->get_default_currency($myconfig); - my $defaultcurrency = $form->{defaultcurrency}; - # Seit neuestem wird die department_id schon übergeben UND $form->department nicht mehr # korrekt zusammengebaut. Sehr wahrscheinlich beim Umstieg auf T8 kaputt gegangen # Ich lass den Code von 2005 erstmal noch stehen ;-) jb 03-2011 @@ -555,8 +556,8 @@ 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=?))|; - do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"}, $form->{currency}); + $query = qq|INSERT INTO ar (id, invnumber) VALUES (?, ?)|; + do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"}); if (!$form->{invnumber}) { $form->{invnumber} = @@ -569,6 +570,9 @@ sub post_invoice { my ($netamount, $invoicediff) = (0, 0); my ($amount, $linetotal, $lastincomeaccno); + my ($currencies) = selectfirst_array_query($form, $dbh, qq|SELECT curr FROM defaults|); + my $defaultcurrency = (split m/:/, $currencies)[0]; + if ($form->{currency} eq $defaultcurrency) { $form->{exchangerate} = 1; } else { @@ -1081,8 +1085,7 @@ sub post_invoice { amount = ?, netamount = ?, paid = ?, duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?, shipvia = ?, terms = ?, notes = ?, intnotes = ?, - curr = (SELECT id FROM currencies WHERE curr= ?), - department_id = ?, payment_id = ?, taxincluded = ?, + curr = ?, department_id = ?, payment_id = ?, taxincluded = ?, type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?, employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?, cp_id = ?, marge_total = ?, marge_percent = ?, @@ -1555,7 +1558,8 @@ sub retrieve_invoice { (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno, (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno, (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 + (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno, + d.curr AS currencies ${query_transdate} FROM defaults d|; @@ -1574,7 +1578,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, a.curr 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, @@ -1586,6 +1590,9 @@ sub retrieve_invoice { $ref = selectfirst_hashref_query($form, $dbh, $query, $id); map { $form->{$_} = $ref->{$_} } keys %{ $ref }; + # remove any trailing whitespace + $form->{currency} =~ s/\s*$//; + $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy"); # get shipto @@ -1750,7 +1757,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, c.curr, c.taxincluded_checked, c.direct_debit, $duedate + COALESCE(pt.terms_netto, 0) AS duedate, b.discount AS tradediscount, b.description AS business @@ -1765,8 +1772,11 @@ sub get_customer { map { $form->{$_} = $ref->{$_} } keys %$ref; - # use customer currency - $form->{currency} = $form->{curr}; + # remove any trailing whitespace + $form->{curr} =~ s/\s*$//; + + # use customer currency if not empty + $form->{currency} = $form->{curr} if $form->{curr}; $query = qq|SELECT sum(amount - paid) AS dunning_amount diff --git a/SL/OE.pm b/SL/OE.pm index c5a8c26ae..3b3845c50 100644 --- a/SL/OE.pm +++ b/SL/OE.pm @@ -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) VALUES (?, '', ?)|; 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 = ?, curr = ?, 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 = ? @@ -506,7 +506,7 @@ sub save { $amount, $netamount, conv_date($reqdate), $form->{taxincluded} ? 't' : 'f', $form->{shippingpoint}, $form->{shipvia}, $form->{notes}, $form->{intnotes}, - $form->{currency}, $form->{closed} ? 't' : 'f', + substr($form->{currency}, 0, 3), $form->{closed} ? 't' : 'f', $form->{delivered} ? "t" : "f", $form->{proforma} ? 't' : 'f', $quotation, conv_i($form->{department_id}), conv_i($form->{language_id}), conv_i($form->{taxzone_id}), @@ -759,13 +759,14 @@ sub retrieve { (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno, (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno, (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 + (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno, + d.curr AS currencies $query_add FROM defaults d|; my $ref = selectfirst_hashref_query($form, $dbh, $query); map { $form->{$_} = $ref->{$_} } keys %$ref; - $form->{currency} = $form->get_default_currency($myconfig); + ($form->{currency}) = split(/:/, $form->{currencies}) unless ($form->{currency}); # set reqdate if this is an invoice->order conversion. If someone knows a better check to ensure # we come from invoices, feel free. @@ -784,7 +785,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, + o.curr 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, @@ -806,6 +807,9 @@ sub retrieve { if ($ref) { map { $form->{$_} = $ref->{$_} } keys %$ref; + # remove any trailing whitespace + $form->{currency} =~ s/\s*$//; + $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ? "quonumber" : "ordnumber"}; # set all entries for multiple ids blank that yield different information diff --git a/bin/mozilla/cp.pl b/bin/mozilla/cp.pl index b9aed484a..a70c32eeb 100644 --- a/bin/mozilla/cp.pl +++ b/bin/mozilla/cp.pl @@ -109,9 +109,10 @@ sub payment { # geben und hier reinparsen, oder besser multibox oder html auslagern? # Antwort: form->currency wird mit oldcurrency oder curr[0] überschrieben # Wofür macht das Sinn? - @curr = $form->get_all_currencies(); + @curr = split(/:/, $form->{currencies}); + chomp $curr[0]; $form->{defaultcurrency} = $form->{currency} = $form->{oldcurrency} = - $form->get_default_currency(\%myconfig); + $curr[0]; # Entsprechend präventiv die Auswahlliste für Währungen # auch mit value= zusammenbauen (s.a. oben bugfix 1771) diff --git a/bin/mozilla/ic.pl b/bin/mozilla/ic.pl index 8200b0110..222a4d1d5 100644 --- a/bin/mozilla/ic.pl +++ b/bin/mozilla/ic.pl @@ -1489,7 +1489,8 @@ sub link_part { IC->create_links("IC", \%myconfig, \%$form); # currencies - map({ $form->{selectcurrency} .= "