+# get contacts for id, if no contact return {"","","","",""}
+sub _get_contacts {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $dbh, $id, $key) = @_;
+
+ $key = "all_contacts" unless ($key);
+ $self->{$key} = [];
+
+ my $query =
+ qq|SELECT c.cp_id, c.cp_cv_id, c.cp_name, c.cp_givenname, c.cp_abteilung | .
+ qq|FROM contacts c | .
+ qq|WHERE cp_cv_id = ? | .
+ qq|ORDER BY lower(c.cp_name)|;
+ my $sth = $dbh->prepare($query);
+ $sth->execute($id) || $self->dberror($query . " ($id)");
+
+ my $i = 0;
+ while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
+ push @{ $self->{$key} }, $ref;
+ $i++;
+ }
+
+ if ($i == 0) {
+ push @{ $self->{$key} }, { { "", "", "", "", "", "" } };
+ }
+ $sth->finish;
+ $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)|;
+ my $sth = $dbh->prepare($query);
+ $sth->execute(@values) ||
+ $self->dberror($query . " (" . join(", ", @values) . ")");
+
+ $self->{$key} = [];
+ while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
+ push(@{ $self->{$key} }, $ref);
+ }
+
+ $sth->finish;
+ $main::lxdebug->leave_sub();
+}
+
+sub _get_shipto {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $dbh, $vc_id, $key) = @_;
+
+ $key = "all_shipto" unless ($key);
+ $self->{$key} = [];
+
+ # get shipping addresses
+ my $query =
+ qq|SELECT s.shipto_id,s.shiptoname,s.shiptodepartment_1 | .
+ qq|FROM shipto s | .
+ qq|WHERE s.trans_id = ?|;
+ my $sth = $dbh->prepare($query);
+ $sth->execute($vc_id) || $self->dberror($query . " ($vc_id)");
+
+ while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
+ push(@{ $self->{$key} }, $ref);
+ }
+ $sth->finish;
+
+ $main::lxdebug->leave_sub();
+}
+
+sub _get_printers {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $dbh, $key) = @_;
+
+ $key = "all_printers" unless ($key);
+ $self->{$key} = [];
+
+ my $query = qq|SELECT id, printer_description, printer_command FROM printers|;
+ my $sth = $dbh->prepare($query);
+ $sth->execute() || $self->dberror($query);
+
+ while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
+ push(@{ $self->{$key} }, $ref);
+ }
+ $sth->finish;
+
+ $main::lxdebug->leave_sub();
+}
+
+sub _get_charts {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $dbh, $params) = @_;
+
+ $key = $params->{key};
+ $key = "all_charts" unless ($key);
+ $self->{$key} = [];
+
+ my $transdate = quote_db_date($params->{transdate});
+
+ my $query =
+ qq|SELECT c.accno, c.description, c.link, 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|;
+
+ my $sth = $dbh->prepare($query);
+ $sth->execute() || $self->dberror($query);
+
+ while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
+ push(@{ $self->{$key} }, $ref);
+ }
+ $sth->finish;
+
+ $main::lxdebug->leave_sub();
+}
+
+sub _get_taxcharts {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $dbh, $key) = @_;
+
+ $key = "all_taxcharts" unless ($key);
+ $self->{$key} = [];
+
+ my $query = qq|SELECT * FROM tax ORDER BY taxkey|;
+
+ my $sth = $dbh->prepare($query);
+ $sth->execute() || $self->dberror($query);
+
+ while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
+ push(@{ $self->{$key} }, $ref);
+ }
+ $sth->finish;
+
+ $main::lxdebug->leave_sub();
+}
+
+sub _get_employees {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $dbh, $key) = @_;
+
+ $key = "all_employees" unless ($key);
+ $self->{$key} =
+ selectall_hashref_query($self, $dbh, qq|SELECT * FROM employee|);
+
+ $main::lxdebug->leave_sub();
+}
+
+sub get_lists {
+ $main::lxdebug->enter_sub();
+
+ my $self = shift;
+ my %params = @_;
+
+ my $dbh = $self->dbconnect(\%main::myconfig);
+ my ($sth, $query, $ref);
+
+ my $vc = $self->{"vc"} eq "customer" ? "customer" : "vendor";
+ my $vc_id = $self->{"${vc}_id"};
+
+ if ($params{"contacts"}) {
+ $self->_get_contacts($dbh, $vc_id, $params{"contacts"});
+ }
+
+ if ($params{"shipto"}) {
+ $self->_get_shipto($dbh, $vc_id, $params{"shipto"});
+ }
+
+ if ($params{"projects"} || $params{"all_projects"}) {
+ $self->_get_projects($dbh, $params{"all_projects"} ?
+ $params{"all_projects"} : $params{"projects"},
+ $params{"all_projects"} ? 1 : 0);
+ }
+
+ if ($params{"printers"}) {
+ $self->_get_printers($dbh, $params{"printers"});
+ }
+
+ if ($params{"charts"}) {
+ $self->_get_charts($dbh, $params{"charts"});
+ }
+
+ if ($params{"taxcharts"}) {
+ $self->_get_taxcharts($dbh, $params{"taxcharts"});
+ }
+
+ if ($params{"employees"}) {
+ $self->_get_employees($dbh, $params{"employees"});
+ }
+
+ $dbh->disconnect();
+
+ $main::lxdebug->leave_sub();
+}
+
+# this sub gets the id and name from $table
+sub get_name {