+ SL::DB->client->dbh;
+}
+
+sub get_standard_dbh {
+ my $dbh = SL::DB->client->dbh;
+
+ if ($dbh && !$dbh->{Active}) {
+ $main::lxdebug->message(LXDebug->INFO(), "get_standard_dbh: \$dbh is defined but not Active anymore");
+ SL::DB->client->dbh(undef);
+ }
+
+ SL::DB->client->dbh;
+}
+
+sub disconnect_standard_dbh {
+ SL::DB->client->dbh->rollback;
+}
+
+# /database
+
+sub date_closed {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $date, $myconfig) = @_;
+ my $dbh = $self->get_standard_dbh;
+
+ my $query = "SELECT 1 FROM defaults WHERE ? < closedto";
+ my $sth = prepare_execute_query($self, $dbh, $query, conv_date($date));
+
+ # Falls $date = '' - Fehlermeldung aus der Datenbank. Ich denke,
+ # es ist sicher ein conv_date vorher IMMER auszuführen.
+ # Testfälle ohne definiertes closedto:
+ # Leere Datumseingabe i.O.
+ # SELECT 1 FROM defaults WHERE '' < closedto
+ # normale Zahlungsbuchung über Rechnungsmaske i.O.
+ # SELECT 1 FROM defaults WHERE '10.05.2011' < closedto
+ # Testfälle mit definiertem closedto (30.04.2011):
+ # Leere Datumseingabe i.O.
+ # SELECT 1 FROM defaults WHERE '' < closedto
+ # normale Buchung im geschloßenem Zeitraum i.O.
+ # SELECT 1 FROM defaults WHERE '21.04.2011' < closedto
+ # Fehlermeldung: Es können keine Zahlungen für abgeschlossene Bücher gebucht werden!
+ # normale Buchung in aktiver Buchungsperiode i.O.
+ # SELECT 1 FROM defaults WHERE '01.05.2011' < closedto
+
+ my ($closed) = $sth->fetchrow_array;
+
+ $main::lxdebug->leave_sub();
+
+ return $closed;
+}
+
+# prevents bookings to the to far away future
+sub date_max_future {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $date, $myconfig) = @_;
+ my $dbh = $self->get_standard_dbh;
+
+ my $query = "SELECT 1 FROM defaults WHERE ? - current_date > max_future_booking_interval";
+ my $sth = prepare_execute_query($self, $dbh, $query, conv_date($date));
+
+ my ($max_future_booking_interval) = $sth->fetchrow_array;
+
+ $main::lxdebug->leave_sub();
+
+ return $max_future_booking_interval;
+}
+
+
+sub update_balance {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $dbh, $table, $field, $where, $value, @values) = @_;
+
+ # if we have a value, go do it
+ if ($value != 0) {
+
+ # retrieve balance from table
+ my $query = "SELECT $field FROM $table WHERE $where FOR UPDATE";
+ my $sth = prepare_execute_query($self, $dbh, $query, @values);
+ my ($balance) = $sth->fetchrow_array;
+ $sth->finish;
+
+ $balance += $value;
+
+ # update balance
+ $query = "UPDATE $table SET $field = $balance WHERE $where";
+ do_query($self, $dbh, $query, @values);
+ }
+ $main::lxdebug->leave_sub();
+}
+
+sub update_exchangerate {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $dbh, $curr, $transdate, $buy, $sell) = @_;
+ my ($query);
+ # some sanity check for currency
+ if ($curr eq '') {
+ $main::lxdebug->leave_sub();
+ return;
+ }
+ $query = qq|SELECT name AS curr FROM currencies WHERE id=(SELECT currency_id FROM defaults)|;
+
+ my ($defaultcurrency) = selectrow_query($self, $dbh, $query);
+
+ if ($curr eq $defaultcurrency) {
+ $main::lxdebug->leave_sub();
+ return;
+ }
+
+ $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 ($buy == 0) {
+ $buy = "";
+ }
+ if ($sell == 0) {
+ $sell = "";
+ }
+
+ $buy = conv_i($buy, "NULL");
+ $sell = conv_i($sell, "NULL");
+
+ my $set;
+ if ($buy != 0 && $sell != 0) {
+ $set = "buy = $buy, sell = $sell";
+ } elsif ($buy != 0) {
+ $set = "buy = $buy";
+ } elsif ($sell != 0) {
+ $set = "sell = $sell";
+ }
+
+ if ($sth->fetchrow_array) {
+ $query = qq|UPDATE exchangerate
+ SET $set
+ WHERE currency_id = (SELECT id FROM currencies WHERE name = ?)
+ AND transdate = ?|;
+
+ } else {
+ $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);
+
+ $main::lxdebug->leave_sub();
+}
+
+sub save_exchangerate {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $myconfig, $currency, $transdate, $rate, $fld) = @_;
+
+ SL::DB->client->with_transaction(sub {
+ my $dbh = SL::DB->client->dbh;
+
+ my ($buy, $sell);
+
+ $buy = $rate if $fld eq 'buy';
+ $sell = $rate if $fld eq 'sell';
+
+
+ $self->update_exchangerate($dbh, $currency, $transdate, $buy, $sell);
+ 1;
+ }) or do { die SL::DB->client->error };
+
+ $main::lxdebug->leave_sub();
+}
+
+sub get_exchangerate {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $dbh, $curr, $transdate, $fld) = @_;
+ my ($query);
+
+ unless ($transdate && $curr) {
+ $main::lxdebug->leave_sub();
+ return 1;
+ }
+
+ $query = qq|SELECT name AS curr FROM currencies WHERE id = (SELECT currency_id FROM defaults)|;
+
+ my ($defaultcurrency) = selectrow_query($self, $dbh, $query);
+
+ if ($curr eq $defaultcurrency) {
+ $main::lxdebug->leave_sub();
+ return 1;
+ }
+
+ $query = qq|SELECT e.$fld FROM exchangerate e
+ WHERE e.currency_id = (SELECT id FROM currencies WHERE name = ?) AND e.transdate = ?|;
+ my ($exchangerate) = selectrow_query($self, $dbh, $query, $curr, $transdate);
+
+
+
+ $main::lxdebug->leave_sub();
+
+ return $exchangerate;
+}
+
+sub check_exchangerate {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $myconfig, $currency, $transdate, $fld) = @_;
+
+ if ($fld !~/^buy|sell$/) {
+ $self->error('Fatal: check_exchangerate called with invalid buy/sell argument');
+ }
+
+ unless ($transdate) {
+ $main::lxdebug->leave_sub();
+ return "";
+ }
+
+ my ($defaultcurrency) = $self->get_default_currency($myconfig);
+
+ if ($currency eq $defaultcurrency) {
+ $main::lxdebug->leave_sub();
+ return 1;
+ }
+
+ my $dbh = $self->get_standard_dbh($myconfig);
+ my $query = qq|SELECT e.$fld FROM exchangerate e
+ WHERE e.currency_id = (SELECT id FROM currencies WHERE name = ?) AND e.transdate = ?|;
+
+ my ($exchangerate) = selectrow_query($self, $dbh, $query, $currency, $transdate);
+
+ $main::lxdebug->leave_sub();
+
+ return $exchangerate;
+}
+
+sub get_all_currencies {
+ $main::lxdebug->enter_sub();
+
+ my $self = shift;
+ my $myconfig = shift || \%::myconfig;
+ my $dbh = $self->get_standard_dbh($myconfig);
+
+ my $query = qq|SELECT name FROM currencies|;
+ my @currencies = map { $_->{name} } selectall_hashref_query($self, $dbh, $query);
+
+ $main::lxdebug->leave_sub();
+
+ return @currencies;
+}
+
+sub get_default_currency {
projects / mfinanz.git / blobdiff