+ 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 {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $myconfig, $transdate) = @_;
+
+ return $main::lxdebug->leave_sub() unless ($self->{payment_id});
+
+ my $dbh = $self->get_standard_dbh($myconfig);
+
+ my $query =
+ qq|SELECT p.terms_netto, p.terms_skonto, p.percent_skonto, p.description_long , p.description | .
+ qq|FROM payment_terms p | .
+ qq|WHERE p.id = ?|;
+
+ ($self->{terms_netto}, $self->{terms_skonto}, $self->{percent_skonto},
+ $self->{payment_terms}, $self->{payment_description}) =
+ selectrow_query($self, $dbh, $query, $self->{payment_id});
+
+ if ($transdate eq "") {
+ if ($self->{invdate}) {
+ $transdate = $self->{invdate};
+ } else {
+ $transdate = $self->{transdate};
+ }
+ }
+
+ $query =
+ qq|SELECT ?::date + ?::integer AS netto_date, ?::date + ?::integer AS skonto_date | .
+ qq|FROM payment_terms|;
+ ($self->{netto_date}, $self->{skonto_date}) =
+ selectrow_query($self, $dbh, $query, $transdate, $self->{terms_netto}, $transdate, $self->{terms_skonto});
+
+ 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"}) {
+ $query =
+ qq|SELECT t.translation, l.output_numberformat, l.output_dateformat, l.output_longdates | .
+ qq|FROM generic_translations t | .
+ qq|LEFT JOIN language l ON t.language_id = l.id | .
+ qq|WHERE (t.language_id = ?)
+ AND (t.translation_id = ?)
+ AND (t.translation_type = 'SL::DB::PaymentTerm/description_long')|;
+ my ($description_long, $output_numberformat, $output_dateformat,
+ $output_longdates) =
+ selectrow_query($self, $dbh, $query,
+ $self->{"language_id"}, $self->{"payment_id"});
+
+ $self->{payment_terms} = $description_long if ($description_long);
+
+ if ($output_dateformat) {
+ foreach my $key (qw(netto_date skonto_date)) {
+ $self->{$key} =
+ $main::locale->reformat_date($myconfig, $self->{$key},
+ $output_dateformat,
+ $output_longdates);
+ }
+ }
+
+ if ($output_numberformat &&
+ ($output_numberformat ne $myconfig->{"numberformat"})) {
+ my $saved_numberformat = $myconfig->{"numberformat"};
+ $myconfig->{"numberformat"} = $output_numberformat;
+ map { $formatted_amounts{$_} = $self->format_amount($myconfig, $amounts{$_}) } keys %amounts;
+ $myconfig->{"numberformat"} = $saved_numberformat;
+ }
+ }
+
+ $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;
+
+ map { $self->{payment_terms} =~ s/<%${_}%>/$formatted_amounts{$_}/g; } keys %formatted_amounts;
+
+ $self->{skonto_in_percent} = $formatted_amounts{skonto_in_percent};
+
+ $main::lxdebug->leave_sub();
+
+}
+
+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));
+ }
+
+ $main::lxdebug->leave_sub();
+}
+
+sub add_shipto {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $dbh, $id, $module) = @_;
+
+ my $shipto;
+ my @values;
+
+ foreach my $item (qw(name department_1 department_2 street zipcode city country
+ contact cp_gender phone fax email)) {
+ if ($self->{"shipto$item"}) {
+ $shipto = 1 if ($self->{$item} ne $self->{"shipto$item"});
+ }
+ push(@values, $self->{"shipto${item}"});
+ }
+
+ if ($shipto) {
+ if ($self->{shipto_id}) {
+ my $query = qq|UPDATE shipto set
+ shiptoname = ?,
+ shiptodepartment_1 = ?,
+ shiptodepartment_2 = ?,
+ shiptostreet = ?,
+ shiptozipcode = ?,
+ shiptocity = ?,
+ shiptocountry = ?,
+ 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
+ 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){
+ $query =
+ qq|INSERT INTO shipto (trans_id, shiptoname, shiptodepartment_1, shiptodepartment_2,
+ shiptostreet, shiptozipcode, shiptocity, shiptocountry,
+ shiptocontact, shiptocp_gender, shiptophone, shiptofax, shiptoemail, module)
+ VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)|;
+ do_query($self, $dbh, $query, $id, @values, $module);
+ }
+ }
+ }
+
+ $main::lxdebug->leave_sub();
+}
+
+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_duedate {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $myconfig, $reference_date) = @_;
+
+ my $terms = $self->{payment_id} ? SL::DB::PaymentTerm->new(id => $self->{payment_id}) ->load
+ : $self->{customer_id} ? SL::DB::Customer ->new(id => $self->{customer_id})->load->payment
+ : $self->{vendor_id} ? SL::DB::Vendor ->new(id => $self->{vendor_id}) ->load->payment
+ : $self->{invdate} ? undef # no payment terms, therefore invdate == duedate
+ : croak("Missing field in \$::form: payment_id, customer_id, vendor_id or invdate");
+ my $duedate = $terms ? $terms->calc_date(reference_date => $reference_date)->to_kivitendo : undef;
+
+ $main::lxdebug->leave_sub();
+
+ return $duedate;
+}
+
+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);
+
+ $main::lxdebug->leave_sub();
+}
+
+sub _get_shipto {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $dbh, $vc_id, $key) = @_;
+
+ $key = "all_shipto" unless ($key);
+
+ if ($vc_id) {
+ # get shipping addresses
+ my $query = qq|SELECT * FROM shipto WHERE trans_id = ?|;
+
+ $self->{$key} = selectall_hashref_query($self, $dbh, $query, $vc_id);
+
+ } else {
+ $self->{$key} = [];
+ }
+
+ $main::lxdebug->leave_sub();
+}
+
+sub _get_printers {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $dbh, $key) = @_;
+
+ $key = "all_printers" unless ($key);
+
+ my $query = qq|SELECT id, printer_description, printer_command, template_code FROM printers|;
+
+ $self->{$key} = selectall_hashref_query($self, $dbh, $query);
+
+ $main::lxdebug->leave_sub();
+}
+
+sub _get_charts {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $dbh, $params) = @_;
+ my ($key);
+
+ $key = $params->{key};
+ $key = "all_charts" unless ($key);
+
+ my $transdate = quote_db_date($params->{transdate});
+
+ my $query =
+ qq|SELECT c.id, c.accno, c.description, c.link, c.charttype, tk.taxkey_id, tk.tax_id | .
+ qq|FROM chart c | .
+ qq|LEFT JOIN taxkeys tk ON | .
+ qq|(tk.id = (SELECT id FROM taxkeys | .
+ qq| WHERE taxkeys.chart_id = c.id AND startdate <= $transdate | .
+ qq| ORDER BY startdate DESC LIMIT 1)) | .
+ qq|ORDER BY c.accno|;
+
+ $self->{$key} = selectall_hashref_query($self, $dbh, $query);
+
+ $main::lxdebug->leave_sub();
+}
+
+sub _get_taxcharts {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $dbh, $params) = @_;
+
+ my $key = "all_taxcharts";
+ my @where;
+
+ if (ref $params eq 'HASH') {
+ $key = $params->{key} if ($params->{key});
+ if ($params->{module} eq 'AR') {
+ push @where, 'chart_categories ~ \'[ACILQ]\'';
+
+ } elsif ($params->{module} eq 'AP') {
+ push @where, 'chart_categories ~ \'[ACELQ]\'';
+ }
+
+ } elsif ($params) {
+ $key = $params;
+ }
+
+ my $where = @where ? ' WHERE ' . join(' AND ', map { "($_)" } @where) : '';
+
+ my $query = qq|SELECT * FROM tax $where ORDER BY taxkey, rate|;
+
+ $self->{$key} = selectall_hashref_query($self, $dbh, $query);
+
+ $main::lxdebug->leave_sub();
+}
+
+sub _get_taxzones {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $dbh, $key) = @_;
+
+ $key = "all_taxzones" unless ($key);
+ my $tzfilter = "";
+ $tzfilter = "WHERE obsolete is FALSE" if $key eq 'ALL_ACTIVE_TAXZONES';
+
+ my $query = qq|SELECT * FROM tax_zones $tzfilter ORDER BY sortkey|;
+
+ $self->{$key} = selectall_hashref_query($self, $dbh, $query);
+
+ $main::lxdebug->leave_sub();
+}
+
+sub _get_employees {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $dbh, $params) = @_;
+
+ my $deleted = 0;
+
+ my $key;
+ if (ref $params eq 'HASH') {
+ $key = $params->{key};
+ $deleted = $params->{deleted};
+
+ } else {
+ $key = $params;
+ }
+
+ $key ||= "all_employees";
+ my $filter = $deleted ? '' : 'WHERE NOT COALESCE(deleted, FALSE)';
+ $self->{$key} = selectall_hashref_query($self, $dbh, qq|SELECT * FROM employee $filter ORDER BY lower(name)|);
+
+ $main::lxdebug->leave_sub();
+}
+
+sub _get_business_types {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $dbh, $key) = @_;
+
+ my $options = ref $key eq 'HASH' ? $key : { key => $key };
+ $options->{key} ||= "all_business_types";
+ my $where = '';
+
+ if (exists $options->{salesman}) {
+ $where = 'WHERE ' . ($options->{salesman} ? '' : 'NOT ') . 'COALESCE(salesman)';
+ }
+
+ $self->{ $options->{key} } = selectall_hashref_query($self, $dbh, qq|SELECT * FROM business $where ORDER BY lower(description)|);
+
+ $main::lxdebug->leave_sub();
+}
+
+sub _get_languages {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $dbh, $key) = @_;
+
+ $key = "all_languages" unless ($key);
+
+ my $query = qq|SELECT * FROM language ORDER BY id|;
+
+ $self->{$key} = selectall_hashref_query($self, $dbh, $query);
+
+ $main::lxdebug->leave_sub();
+}
+
+sub _get_dunning_configs {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $dbh, $key) = @_;
projects / mfinanz.git / blobdiff