+ $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 {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $myconfig) = @_;
+ my $dbh = $self->get_standard_dbh($myconfig);
+ my $query = qq|SELECT name AS curr FROM currencies WHERE id = (SELECT currency_id FROM defaults)|;
+
+ my ($defaultcurrency) = selectrow_query($self, $dbh, $query);
+
+ $main::lxdebug->leave_sub();
+
+ return $defaultcurrency;
+}
+
+sub set_payment_options {
+ my ($self, $myconfig, $transdate, $type) = @_;
+
+ my $terms = $self->{payment_id} ? SL::DB::PaymentTerm->new(id => $self->{payment_id})->load : undef;
+ return if !$terms;
+
+ my $is_invoice = $type =~ m{invoice}i;
+
+ $transdate ||= $self->{invdate} || $self->{transdate};
+ my $due_date = $self->{duedate} || $self->{reqdate};
+
+ $self->{$_} = $terms->$_ for qw(terms_netto terms_skonto percent_skonto);
+ $self->{payment_description} = $terms->description;
+ $self->{netto_date} = $terms->calc_date(reference_date => $transdate, due_date => $due_date, terms => 'net')->to_kivitendo;
+ $self->{skonto_date} = $terms->calc_date(reference_date => $transdate, due_date => $due_date, terms => 'discount')->to_kivitendo;
+
+ my ($invtotal, $total);
+ my (%amounts, %formatted_amounts);
+
+ if ($self->{type} =~ /_order$/) {
+ $amounts{invtotal} = $self->{ordtotal};
+ $amounts{total} = $self->{ordtotal};
+
+ } elsif ($self->{type} =~ /_quotation$/) {
+ $amounts{invtotal} = $self->{quototal};
+ $amounts{total} = $self->{quototal};
+
+ } else {
+ $amounts{invtotal} = $self->{invtotal};
+ $amounts{total} = $self->{total};
+ }
+ map { $amounts{$_} = $self->parse_amount($myconfig, $amounts{$_}) } keys %amounts;
+
+ $amounts{skonto_in_percent} = 100.0 * $self->{percent_skonto};
+ $amounts{skonto_amount} = $amounts{invtotal} * $self->{percent_skonto};
+ $amounts{invtotal_wo_skonto} = $amounts{invtotal} * (1 - $self->{percent_skonto});
+ $amounts{total_wo_skonto} = $amounts{total} * (1 - $self->{percent_skonto});
+
+ foreach (keys %amounts) {
+ $amounts{$_} = $self->round_amount($amounts{$_}, 2);
+ $formatted_amounts{$_} = $self->format_amount($myconfig, $amounts{$_}, 2);
+ }
+
+ if ($self->{"language_id"}) {
+ my $language = SL::DB::Language->new(id => $self->{language_id})->load;
+
+ $self->{payment_terms} = $type =~ m{invoice}i ? $terms->translated_attribute('description_long_invoice', $language->id) : undef;
+ $self->{payment_terms} ||= $terms->translated_attribute('description_long', $language->id);
+
+ if ($language->output_dateformat) {
+ foreach my $key (qw(netto_date skonto_date)) {
+ $self->{$key} = $::locale->reformat_date($myconfig, $self->{$key}, $language->output_dateformat, $language->output_longdates);
+ }
+ }
+
+ if ($language->output_numberformat && ($language->output_numberformat ne $myconfig->{numberformat})) {
+ local $myconfig->{numberformat};
+ $myconfig->{"numberformat"} = $language->output_numberformat;
+ $formatted_amounts{$_} = $self->format_amount($myconfig, $amounts{$_}) for keys %amounts;
+ }
+ }
+
+ $self->{payment_terms} = $self->{payment_terms} || ($is_invoice ? $terms->description_long_invoice : undef) || $terms->description_long;
+
+ $self->{payment_terms} =~ s/<%netto_date%>/$self->{netto_date}/g;
+ $self->{payment_terms} =~ s/<%skonto_date%>/$self->{skonto_date}/g;
+ $self->{payment_terms} =~ s/<%currency%>/$self->{currency}/g;
+ $self->{payment_terms} =~ s/<%terms_netto%>/$self->{terms_netto}/g;
+ $self->{payment_terms} =~ s/<%account_number%>/$self->{account_number}/g;
+ $self->{payment_terms} =~ s/<%bank%>/$self->{bank}/g;
+ $self->{payment_terms} =~ s/<%bank_code%>/$self->{bank_code}/g;
+ $self->{payment_terms} =~ s/<\%bic\%>/$self->{bic}/g;
+ $self->{payment_terms} =~ s/<\%iban\%>/$self->{iban}/g;
+ $self->{payment_terms} =~ s/<\%mandate_date_of_signature\%>/$self->{mandate_date_of_signature}/g;
+ $self->{payment_terms} =~ s/<\%mandator_id\%>/$self->{mandator_id}/g;
+
+ map { $self->{payment_terms} =~ s/<%${_}%>/$formatted_amounts{$_}/g; } keys %formatted_amounts;
+
+ $self->{skonto_in_percent} = $formatted_amounts{skonto_in_percent};
+
+}
+
+sub get_template_language {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $myconfig) = @_;
+
+ my $template_code = "";
+
+ if ($self->{language_id}) {
+ my $dbh = $self->get_standard_dbh($myconfig);
+ my $query = qq|SELECT template_code FROM language WHERE id = ?|;
+ ($template_code) = selectrow_query($self, $dbh, $query, $self->{language_id});
+ }
+
+ $main::lxdebug->leave_sub();
+
+ return $template_code;
+}
+
+sub get_printer_code {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $myconfig) = @_;
+
+ my $template_code = "";
+
+ if ($self->{printer_id}) {
+ my $dbh = $self->get_standard_dbh($myconfig);
+ my $query = qq|SELECT template_code, printer_command FROM printers WHERE id = ?|;
+ ($template_code, $self->{printer_command}) = selectrow_query($self, $dbh, $query, $self->{printer_id});
+ }
+
+ $main::lxdebug->leave_sub();
+
+ return $template_code;
+}
+
+sub get_shipto {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $myconfig) = @_;
+
+ my $template_code = "";
+
+ if ($self->{shipto_id}) {
+ my $dbh = $self->get_standard_dbh($myconfig);
+ my $query = qq|SELECT * FROM shipto WHERE shipto_id = ?|;
+ my $ref = selectfirst_hashref_query($self, $dbh, $query, $self->{shipto_id});
+ map({ $self->{$_} = $ref->{$_} } keys(%$ref));
+
+ my $cvars = CVar->get_custom_variables(
+ dbh => $dbh,
+ module => 'ShipTo',
+ trans_id => $self->{shipto_id},
+ );
+ $self->{"shiptocvar_$_->{name}"} = $_->{value} for @{ $cvars };
+ }
+
+ $main::lxdebug->leave_sub();
+}
+
+sub add_shipto {
+ my ($self, $dbh, $id, $module) = @_;
+
+ my $shipto;
+ my @values;
+
+ foreach my $item (qw(name department_1 department_2 street zipcode city country gln
+ contact cp_gender phone fax email)) {
+ if ($self->{"shipto$item"}) {
+ $shipto = 1 if ($self->{$item} ne $self->{"shipto$item"});
+ }
+ push(@values, $self->{"shipto${item}"});
+ }
+
+ return if !$shipto;
+
+ my $shipto_id = $self->{shipto_id};
+
+ if ($self->{shipto_id}) {
+ my $query = qq|UPDATE shipto set
+ shiptoname = ?,
+ shiptodepartment_1 = ?,
+ shiptodepartment_2 = ?,
+ shiptostreet = ?,
+ shiptozipcode = ?,
+ shiptocity = ?,
+ shiptocountry = ?,
+ shiptogln = ?,
+ shiptocontact = ?,
+ shiptocp_gender = ?,
+ shiptophone = ?,
+ shiptofax = ?,
+ shiptoemail = ?
+ WHERE shipto_id = ?|;
+ do_query($self, $dbh, $query, @values, $self->{shipto_id});
+ } else {
+ my $query = qq|SELECT * FROM shipto
+ WHERE shiptoname = ? AND
+ shiptodepartment_1 = ? AND
+ shiptodepartment_2 = ? AND
+ shiptostreet = ? AND
+ shiptozipcode = ? AND
+ shiptocity = ? AND
+ shiptocountry = ? AND
+ shiptogln = ? AND
+ shiptocontact = ? AND
+ shiptocp_gender = ? AND
+ shiptophone = ? AND
+ shiptofax = ? AND
+ shiptoemail = ? AND
+ module = ? AND
+ trans_id = ?|;
+ my $insert_check = selectfirst_hashref_query($self, $dbh, $query, @values, $module, $id);
+ if(!$insert_check){
+ my $insert_query =
+ qq|INSERT INTO shipto (trans_id, shiptoname, shiptodepartment_1, shiptodepartment_2,
+ shiptostreet, shiptozipcode, shiptocity, shiptocountry, shiptogln,
+ shiptocontact, shiptocp_gender, shiptophone, shiptofax, shiptoemail, module)
+ VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)|;
+ do_query($self, $dbh, $insert_query, $id, @values, $module);
+
+ $insert_check = selectfirst_hashref_query($self, $dbh, $query, @values, $module, $id);
+ }
+
+ $shipto_id = $insert_check->{shipto_id};
+ }
+
+ return unless $shipto_id;
+
+ CVar->save_custom_variables(
+ dbh => $dbh,
+ module => 'ShipTo',
+ trans_id => $shipto_id,
+ variables => $self,
+ name_prefix => 'shipto',
+ );
+}
+
+sub get_employee {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $dbh) = @_;
+
+ $dbh ||= $self->get_standard_dbh(\%main::myconfig);
+
+ my $query = qq|SELECT id, name FROM employee WHERE login = ?|;
+ ($self->{"employee_id"}, $self->{"employee"}) = selectrow_query($self, $dbh, $query, $self->{login});
+ $self->{"employee_id"} *= 1;
+
+ $main::lxdebug->leave_sub();
+}
+
+sub get_employee_data {
+ $main::lxdebug->enter_sub();
+
+ my $self = shift;
+ my %params = @_;
+ my $defaults = SL::DB::Default->get;
+
+ Common::check_params(\%params, qw(prefix));
+ Common::check_params_x(\%params, qw(id));
+
+ if (!$params{id}) {
+ $main::lxdebug->leave_sub();
+ return;
+ }
+
+ my $myconfig = \%main::myconfig;
+ my $dbh = $params{dbh} || $self->get_standard_dbh($myconfig);
+
+ my ($login, $deleted) = selectrow_query($self, $dbh, qq|SELECT login,deleted FROM employee WHERE id = ?|, conv_i($params{id}));
+
+ if ($login) {
+ # login already fetched and still the same client (mandant) | same for both cases (delete|!delete)
+ $self->{$params{prefix} . '_login'} = $login;
+ $self->{$params{prefix} . "_${_}"} = $defaults->$_ for qw(address businessnumber co_ustid company duns taxnumber);
+
+ if (!$deleted) {
+ # get employee data from auth.user_config
+ my $user = User->new(login => $login);
+ $self->{$params{prefix} . "_${_}"} = $user->{$_} for qw(email fax name signature tel);
+ } else {
+ # get saved employee data from employee
+ my $employee = SL::DB::Manager::Employee->find_by(id => conv_i($params{id}));
+ $self->{$params{prefix} . "_${_}"} = $employee->{"deleted_$_"} for qw(email fax signature tel);
+ $self->{$params{prefix} . "_name"} = $employee->name;
+ }
+ }
+ $main::lxdebug->leave_sub();
+}
+
+sub _get_contacts {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $dbh, $id, $key) = @_;
+
+ $key = "all_contacts" unless ($key);
+
+ if (!$id) {
+ $self->{$key} = [];
+ $main::lxdebug->leave_sub();
+ return;
+ }
+
+ my $query =
+ qq|SELECT cp_id, cp_cv_id, cp_name, cp_givenname, cp_abteilung | .
+ qq|FROM contacts | .
+ qq|WHERE cp_cv_id = ? | .
+ qq|ORDER BY lower(cp_name)|;
+
+ $self->{$key} = selectall_hashref_query($self, $dbh, $query, $id);
+
+ $main::lxdebug->leave_sub();
+}
+
+sub _get_projects {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $dbh, $key) = @_;
+
+ my ($all, $old_id, $where, @values);
+
+ if (ref($key) eq "HASH") {
+ my $params = $key;
+
+ $key = "ALL_PROJECTS";
+
+ foreach my $p (keys(%{$params})) {
+ if ($p eq "all") {
+ $all = $params->{$p};
+ } elsif ($p eq "old_id") {
+ $old_id = $params->{$p};
+ } elsif ($p eq "key") {
+ $key = $params->{$p};
+ }
+ }
+ }
+
+ if (!$all) {
+ $where = "WHERE active ";
+ if ($old_id) {
+ if (ref($old_id) eq "ARRAY") {
+ my @ids = grep({ $_ } @{$old_id});
+ if (@ids) {
+ $where .= " OR id IN (" . join(",", map({ "?" } @ids)) . ") ";
+ push(@values, @ids);
+ }
+ } else {
+ $where .= " OR (id = ?) ";
+ push(@values, $old_id);
+ }
+ }
+ }
+
+ my $query =
+ qq|SELECT id, projectnumber, description, active | .
+ qq|FROM project | .
+ $where .
+ qq|ORDER BY lower(projectnumber)|;
+
+ $self->{$key} = selectall_hashref_query($self, $dbh, $query, @values);
projects / mfinanz.git / blobdiff