From d331a3d77f0b24a301412799d2e9cc5fa1bf27ba Mon Sep 17 00:00:00 2001 From: Niclas Zimmermann Date: Tue, 19 Mar 2013 11:37:49 +0100 Subject: [PATCH] Nutzung der Tabelle currencies Dieser Commmit passt den Code an, um die Tabelle currencies zu nutzen. --- 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, 109 insertions(+), 142 deletions(-) diff --git a/SL/AM.pm b/SL/AM.pm index 61441faec..86b29d196 100644 --- a/SL/AM.pm +++ b/SL/AM.pm @@ -1063,10 +1063,6 @@ 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 = @@ -1089,7 +1085,6 @@ sub save_defaults { servicenumber = ?, sdonumber = ?, pdonumber = ?, - curr = ?, businessnumber = ?, weightunit = ?, language_id = ?|; @@ -1101,11 +1096,24 @@ sub save_defaults { $form->{customernumber}, $form->{vendornumber}, $form->{articlenumber}, $form->{servicenumber}, $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(); @@ -1119,7 +1127,7 @@ sub save_preferences { my $dbh = $form->get_standard_dbh($myconfig); - my ($currency, $businessnumber) = selectrow_query($form, $dbh, qq|SELECT curr, businessnumber FROM defaults|); + my ($businessnumber) = selectrow_query($form, $dbh, qq|SELECT businessnumber FROM defaults|); # update name my $query = qq|UPDATE employee SET name = ? WHERE login = ?|; @@ -1127,10 +1135,6 @@ 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 54abcd17e..810a15e52 100644 --- a/SL/AP.pm +++ b/SL/AP.pm @@ -55,7 +55,6 @@ 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}); @@ -185,7 +184,7 @@ sub post_transaction { $query = qq|UPDATE ap SET invnumber = ?, transdate = ?, ordnumber = ?, vendor_id = ?, taxincluded = ?, amount = ?, duedate = ?, paid = ?, netamount = ?, - curr = ?, notes = ?, department_id = ?, storno = ?, storno_id = ?, + curr = (SELECT id FROM currencies WHERE curr = ?), notes = ?, department_id = ?, storno = ?, storno_id = ?, globalproject_id = ?, direct_debit = ? WHERE id = ?|; @values = ($form->{invnumber}, conv_date($form->{transdate}), @@ -665,7 +664,6 @@ 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 795849583..3def7597a 100644 --- a/SL/AR.pm +++ b/SL/AR.pm @@ -56,7 +56,6 @@ 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 : @@ -135,8 +134,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) VALUES (?, 'dummy', ?)|; - do_query($form, $dbh, $query, $form->{id}, $form->{employee_id}); + $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}); $form->{invnumber} = $form->update_defaults($myconfig, "invnumber", $dbh) unless $form->{invnumber}; } } @@ -156,12 +155,12 @@ sub post_transaction { qq|UPDATE ar set invnumber = ?, ordnumber = ?, transdate = ?, customer_id = ?, taxincluded = ?, amount = ?, duedate = ?, paid = ?, - netamount = ?, curr = ?, notes = ?, department_id = ?, + netamount = ?, 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->{currency}, $form->{notes}, conv_i($form->{department_id}), + conv_date($form->{duedate}), $form->{paid}, $form->{netamount}, $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); @@ -426,7 +425,6 @@ 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 5786bf862..6f4dbf625 100644 --- a/SL/CP.pm +++ b/SL/CP.pm @@ -93,10 +93,9 @@ sub paymentaccounts { } $sth->finish; - # get currencies and closedto - $query = qq|SELECT curr, closedto FROM defaults|; - ($form->{currencies}, $form->{closedto}) = - selectrow_query($form, $dbh, $query); + # get closedto + $query = qq|SELECT closedto FROM defaults|; + ($form->{closedto}) = selectrow_query($form, $dbh, $query); $dbh->disconnect; @@ -150,9 +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, a.curr | . + qq|SELECT a.id, a.invnumber, a.transdate, a.amount, a.paid, cu.curr | . qq|FROM $arap a | . - qq|WHERE (a.${vc}_id = ?) AND (COALESCE(a.curr, '') = ?) AND NOT (a.amount = a.paid)| . + 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|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 (COALESCE(curr, '') <> ?) + AND ((SELECT cu.curr FROM currencies cu WHERE cu.id=${arap}.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 = ?) AND (a.id = ?)|; + qq|WHERE (e.curr = (SELECT id FROM currencies WHERE 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 97f57c72c..37550e4af 100644 --- a/SL/CT.pm +++ b/SL/CT.pm @@ -68,11 +68,12 @@ sub get_tuple { my $ref = $sth->fetchrow_hashref("NAME_lc"); map { $form->{$_} = $ref->{$_} } keys %$ref; + $sth->finish; - # remove any trailing whitespace - $form->{curr} =~ s/\s*$//; + #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})); - $sth->finish; if ( $form->{salesman_id} ) { my $query = qq|SELECT ct.name AS salesman | . @@ -319,7 +320,7 @@ sub save_customer { qq|user_password = ?, | . qq|c_vendor_id = ?, | . qq|klass = ?, | . - qq|curr = ?, | . + qq|curr = (SELECT id FROM currencies WHERE curr = ?), | . qq|taxincluded_checked = ? | . qq|WHERE id = ?|; my @values = ( @@ -362,7 +363,7 @@ sub save_customer { $form->{user_password}, $form->{c_vendor_id}, conv_i($form->{klass}), - substr($form->{currency}, 0, 3), + $form->{currency}, $form->{taxincluded_checked} ne '' ? $form->{taxincluded_checked} : undef, $form->{id} ); @@ -471,7 +472,7 @@ sub save_vendor { qq| username = ?, | . qq| user_password = ?, | . qq| v_customer_id = ?, | . - qq| curr = ? | . + qq| curr = (SELECT id FROM currencies WHERE curr = ?) | . qq|WHERE id = ?|; my @values = ( $form->{vendornumber}, @@ -511,7 +512,7 @@ sub save_vendor { $form->{username}, $form->{user_password}, $form->{v_customer_id}, - substr($form->{currency}, 0, 3), + $form->{currency}, $form->{id} ); do_query($form, $dbh, $query, @values); diff --git a/SL/DN.pm b/SL/DN.pm index 46749ab37..a960a4c00 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 - ?, -- curr + (SELECT id FROM currencies WHERE 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, - ar.curr, + (SELECT cu.curr FROM currencies cu WHERE cu.id=ar.curr) 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 14a499a7c..a12fac14b 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 = ? + is_sales = ?, taxzone_id = ?, taxincluded = ?, terms = ?, curr = (SELECT id FROM currencies WHERE 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, dord.curr AS currency + dord.taxzone_id, dord.taxincluded, dord.terms, (SELECT cu.curr FROM currencies cu WHERE cu.id=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,9 +638,6 @@ 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 141233d94..98a8b5e9a 100644 --- a/SL/Form.pm +++ b/SL/Form.pm @@ -1490,11 +1490,9 @@ sub update_exchangerate { $main::lxdebug->leave_sub(); return; } - $query = qq|SELECT curr FROM defaults|; - - my ($currency) = selectrow_query($self, $dbh, $query); - my ($defaultcurrency) = split m/:/, $currency; + $query = qq|SELECT curr FROM currencies WHERE id=(SELECT curr FROM defaults)|; + my ($defaultcurrency) = selectrow_query($self, $dbh, $query); if ($curr eq $defaultcurrency) { $main::lxdebug->leave_sub(); @@ -1502,7 +1500,7 @@ sub update_exchangerate { } $query = qq|SELECT e.curr FROM exchangerate e - WHERE e.curr = ? AND e.transdate = ? + WHERE e.curr = (SELECT cu.id FROM currencies cu WHERE cu.curr=?) AND e.transdate = ? FOR UPDATE|; my $sth = prepare_execute_query($self, $dbh, $query, $curr, $transdate); @@ -1528,12 +1526,12 @@ sub update_exchangerate { if ($sth->fetchrow_array) { $query = qq|UPDATE exchangerate SET $set - WHERE curr = ? + WHERE curr = (SELECT id FROM currencies WHERE curr = ?) AND transdate = ?|; } else { $query = qq|INSERT INTO exchangerate (curr, buy, sell, transdate) - VALUES (?, $buy, $sell, ?)|; + VALUES ((SELECT id FROM currencies WHERE curr = ?), $buy, $sell, ?)|; } $sth->finish; do_query($self, $dbh, $query, $curr, $transdate); @@ -1573,18 +1571,17 @@ sub get_exchangerate { return 1; } - $query = qq|SELECT curr FROM defaults|; + $query = qq|SELECT curr FROM currencies WHERE id = (SELECT curr FROM defaults)|; - my ($currency) = selectrow_query($self, $dbh, $query); - my ($defaultcurrency) = split m/:/, $currency; + my ($defaultcurrency) = selectrow_query($self, $dbh, $query); - if ($currency eq $defaultcurrency) { + if ($curr eq $defaultcurrency) { $main::lxdebug->leave_sub(); return 1; } $query = qq|SELECT e.$fld FROM exchangerate e - WHERE e.curr = ? AND e.transdate = ?|; + WHERE e.curr = (SELECT id FROM currencies WHERE curr = ?) AND e.transdate = ?|; my ($exchangerate) = selectrow_query($self, $dbh, $query, $curr, $transdate); @@ -1617,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 = ? AND e.transdate = ?|; + WHERE e.curr = (SELECT id FROM currencies WHERE curr = ?) AND e.transdate = ?|; my ($exchangerate) = selectrow_query($self, $dbh, $query, $currency, $transdate); @@ -1632,11 +1629,16 @@ 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 defaults|; + my $query = qq|SELECT curr FROM currencies|; - my ($curr) = selectrow_query($self, $dbh, $query); - my @currencies = grep { $_ } map { s/\s//g; $_ } split m/:/, $curr; + my $sth = prepare_execute_query($self, $dbh, $query); + + while (my $ref = $sth->fetchrow_hashref()) { + push(@currencies, $ref->{curr}); + } + $sth->finish; $main::lxdebug->leave_sub(); @@ -1647,11 +1649,14 @@ sub get_default_currency { $main::lxdebug->enter_sub(); my ($self, $myconfig) = @_; - my @currencies = $self->get_all_currencies($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); $main::lxdebug->leave_sub(); - return $currencies[0]; + return $defaultcurrency; } sub set_payment_options { @@ -2191,9 +2196,7 @@ $main::lxdebug->enter_sub(); $key = "all_currencies" unless ($key); - my $query = qq|SELECT curr AS currency FROM defaults|; - - $self->{$key} = [split(/\:/ , selectfirst_hashref_query($self, $dbh, $query)->{currency})]; + $self->{$key} = [$self->get_all_currencies()]; $main::lxdebug->leave_sub(); } @@ -2704,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, a.curr AS currency, a.notes, + a.duedate, a.ordnumber, a.taxincluded, (SELECT cu.curr FROM currencies cu WHERE cu.id=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} @@ -2722,9 +2725,6 @@ 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,9 +2808,11 @@ sub create_links { } $sth->finish; + #check das: $query = qq|SELECT - d.curr AS currencies, d.closedto, d.revtrans, + d.closedto, d.revtrans, + (SELECT cu.curr FROM currencies cu WHERE cu.id=d.curr) 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|; @@ -2822,7 +2824,8 @@ sub create_links { # get date $query = qq|SELECT - current_date AS transdate, d.curr AS currencies, d.closedto, d.revtrans, + current_date AS transdate, d.closedto, d.revtrans, + (SELECT cu.curr FROM currencies cu WHERE cu.id=d.curr) 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|; @@ -2832,7 +2835,7 @@ sub create_links { if ($self->{"$self->{vc}_id"}) { # only setup currency - ($self->{currency}) = split(/:/, $self->{currencies}) if !$self->{currency}; + ($self->{currency}) = $self->{defaultcurrency} if !$self->{currency}; } else { @@ -2857,19 +2860,17 @@ sub lastname_used { my ($arap, $where); $table = $table eq "customer" ? "customer" : "vendor"; - my %column_map = ("a.curr" => "currency", - "a.${table}_id" => "${table}_id", + my %column_map = ("a.${table}_id" => "${table}_id", "a.department_id" => "department_id", "d.description" => "department", "ct.name" => $table, - "ct.curr" => "cv_curr", + "cu.curr" => "currency", "current_date + ct.terms" => "duedate", ); if ($self->{type} =~ /delivery_order/) { $arap = 'delivery_orders'; - delete $column_map{"a.curr"}; - delete $column_map{"ct.curr"}; + delete $column_map{"cu.currency"}; } elsif ($self->{type} =~ /_order/) { $arap = 'oe'; @@ -2898,18 +2899,12 @@ 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 1f0ae58f7..d52641e86 100644 --- a/SL/IR.pm +++ b/SL/IR.pm @@ -57,6 +57,7 @@ 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); @@ -70,18 +71,16 @@ 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) VALUES (?, '')|, $form->{id}); + do_query($form, $dbh, qq|INSERT INTO ap (id, invnumber, curr) VALUES (?, '', (SELECT id FROM currencies WHERE curr=?))|, $form->{id}, $form->{currency}); } } - 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 { @@ -684,7 +683,7 @@ sub post_invoice { orddate = ?, quodate = ?, vendor_id = ?, amount = ?, netamount = ?, paid = ?, duedate = ?, invoice = ?, taxzone_id = ?, notes = ?, taxincluded = ?, - intnotes = ?, curr = ?, storno_id = ?, storno = ?, + intnotes = ?, storno_id = ?, storno = ?, cp_id = ?, employee_id = ?, department_id = ?, globalproject_id = ?, direct_debit = ? WHERE id = ?|; @@ -693,7 +692,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}, $form->{currency}, conv_i($form->{storno_id}), $form->{storno} ? 't' : 'f', + $form->{intnotes}, 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', @@ -922,8 +921,7 @@ 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, - d.curr AS currencies + (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno $q_invdate FROM defaults d|; $ref = selectfirst_hashref_query($form, $dbh, $query); @@ -940,15 +938,12 @@ 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, curr AS currency, direct_debit + intnotes, (SELECT cu.curr FROM currencies cu WHERE cu.id=ap.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 @@ -1090,7 +1085,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, v.curr, v.direct_debit, + 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, $duedate + COALESCE(pt.terms_netto, 0) AS duedate, b.description AS business FROM vendor v @@ -1100,11 +1095,8 @@ sub get_vendor { my $ref = selectfirst_hashref_query($form, $dbh, $query, @values); map { $params->{$_} = $ref->{$_} } keys %$ref; - # remove any trailing whitespace - $form->{curr} =~ s/\s*$//; - - # use vendor currency if not empty - $form->{currency} = $form->{curr} if $form->{curr}; + # use vendor currency + $form->{currency} = $form->{curr}; $params->{creditremaining} = $params->{creditlimit}; @@ -1388,7 +1380,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, - ct.curr AS currency + (SELECT cu.curr FROM currencies cu WHERE cu.id=ct.curr) AS currency FROM vendor ct LEFT JOIN contacts cp ON (ct.id = cp.cp_cv_id) WHERE (ct.id = ?) $contact @@ -1407,8 +1399,6 @@ 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 1deeb2a47..cd4461891 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, - ct.curr AS currency + (SELECT cu.curr FROM currencies cu WHERE cu.id=ct.curr) AS currency FROM customer ct LEFT JOIN contacts cp on ct.id = cp.cp_cv_id WHERE (ct.id = ?) $where @@ -478,9 +478,6 @@ 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 @@ -536,6 +533,8 @@ 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 @@ -556,8 +555,8 @@ sub post_invoice { $query = qq|SELECT nextval('glid')|; ($form->{"id"}) = selectrow_query($form, $dbh, $query); - $query = qq|INSERT INTO ar (id, invnumber) VALUES (?, ?)|; - do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"}); + $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}); if (!$form->{invnumber}) { $form->{invnumber} = @@ -570,9 +569,6 @@ 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 { @@ -1085,7 +1081,8 @@ sub post_invoice { amount = ?, netamount = ?, paid = ?, duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?, shipvia = ?, terms = ?, notes = ?, intnotes = ?, - curr = ?, department_id = ?, payment_id = ?, taxincluded = ?, + curr = (SELECT id FROM currencies WHERE 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 = ?, @@ -1558,8 +1555,7 @@ 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, - d.curr AS currencies + (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno ${query_transdate} FROM defaults d|; @@ -1578,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, a.curr AS currency, a.shipto_id, a.cp_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.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, @@ -1590,9 +1586,6 @@ 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 @@ -1757,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, c.curr, + 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.taxincluded_checked, c.direct_debit, $duedate + COALESCE(pt.terms_netto, 0) AS duedate, b.discount AS tradediscount, b.description AS business @@ -1772,11 +1765,8 @@ sub get_customer { map { $form->{$_} = $ref->{$_} } keys %$ref; - # remove any trailing whitespace - $form->{curr} =~ s/\s*$//; - - # use customer currency if not empty - $form->{currency} = $form->{curr} if $form->{curr}; + # use customer currency + $form->{currency} = $form->{curr}; $query = qq|SELECT sum(amount - paid) AS dunning_amount diff --git a/SL/OE.pm b/SL/OE.pm index 94b97e998..5c1ecd7eb 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) VALUES (?, '', ?)|; + $query = qq|INSERT INTO oe (id, ordnumber, employee_id, curr) VALUES (?, '', ?, (SELECT curr 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 = ?, closed = ?, + shippingpoint = ?, shipvia = ?, notes = ?, intnotes = ?, curr = (SELECT id FROM currencies WHERE 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}, - substr($form->{currency}, 0, 3), $form->{closed} ? 't' : 'f', + $form->{currency}, $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,14 +759,13 @@ 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, - d.curr AS currencies + (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno $query_add FROM defaults d|; my $ref = selectfirst_hashref_query($form, $dbh, $query); map { $form->{$_} = $ref->{$_} } keys %$ref; - ($form->{currency}) = split(/:/, $form->{currencies}) unless ($form->{currency}); + $form->{currency} = $form->get_default_currency($myconfig); # set reqdate if this is an invoice->order conversion. If someone knows a better check to ensure # we come from invoices, feel free. @@ -785,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, - o.curr AS currency, e.name AS employee, o.employee_id, o.salesman_id, + (SELECT cu.curr FROM currencies cu WHERE cu.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, @@ -807,9 +806,6 @@ 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 a70c32eeb..b9aed484a 100644 --- a/bin/mozilla/cp.pl +++ b/bin/mozilla/cp.pl @@ -109,10 +109,9 @@ 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 = split(/:/, $form->{currencies}); - chomp $curr[0]; + @curr = $form->get_all_currencies(); $form->{defaultcurrency} = $form->{currency} = $form->{oldcurrency} = - $curr[0]; + $form->get_default_currency(\%myconfig); # 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 b1540e161..17d589f56 100644 --- a/bin/mozilla/ic.pl +++ b/bin/mozilla/ic.pl @@ -1489,8 +1489,7 @@ sub link_part { IC->create_links("IC", \%myconfig, \%$form); # currencies - map({ $form->{selectcurrency} .= "