+ my ($self, $myconfig, $table, $module) = @_;
+
+ my $ref;
+ my $dbh = $self->get_standard_dbh;
+
+ $table = $table eq "customer" ? "customer" : "vendor";
+
+ # build selection list
+ # Hotfix für Bug 1837 - Besser wäre es alte Buchungsbelege
+ # OHNE Auswahlliste (reines Textfeld) zu laden. Hilft aber auch
+ # nicht für veränderbare Belege (oe, do, ...)
+ my $obsolete = $self->{id} ? '' : "WHERE NOT obsolete";
+ my $query = qq|SELECT count(*) FROM $table $obsolete|;
+ my ($count) = selectrow_query($self, $dbh, $query);
+
+ if ($count <= $myconfig->{vclimit}) {
+ $query = qq|SELECT id, name, salesman_id
+ FROM $table $obsolete
+ ORDER BY name|;
+ $self->{"all_$table"} = selectall_hashref_query($self, $dbh, $query);
+ }
+
+ # get self
+ $self->get_employee($dbh);
+
+ # setup sales contacts
+ $query = qq|SELECT e.id, e.name
+ FROM employee e
+ WHERE (e.sales = '1') AND (NOT e.id = ?)
+ ORDER BY name|;
+ $self->{all_employees} = selectall_hashref_query($self, $dbh, $query, $self->{employee_id});
+
+ # this is for self
+ push(@{ $self->{all_employees} },
+ { id => $self->{employee_id},
+ name => $self->{employee} });
+
+ # prepare query for departments
+ $query = qq|SELECT id, description
+ FROM department
+ ORDER BY description|;
+
+ $self->{all_departments} = selectall_hashref_query($self, $dbh, $query);
+
+ # get languages
+ $query = qq|SELECT id, description
+ FROM language
+ ORDER BY id|;
+
+ $self->{languages} = selectall_hashref_query($self, $dbh, $query);
+
+ # get printer
+ $query = qq|SELECT printer_description, id
+ FROM printers
+ ORDER BY printer_description|;
+
+ $self->{printers} = selectall_hashref_query($self, $dbh, $query);
+
+ # get payment terms
+ $query = qq|SELECT id, description
+ FROM payment_terms
+ ORDER BY sortkey|;
+
+ $self->{payment_terms} = selectall_hashref_query($self, $dbh, $query);
+
+ $main::lxdebug->leave_sub();
+}
+
+sub new_lastmtime {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $table, $provided_dbh) = @_;
+
+ my $dbh = $provided_dbh ? $provided_dbh : $self->get_standard_dbh;
+ return unless $self->{id};
+ croak ("wrong call, no valid table defined") unless $table =~ /^(oe|ar|ap|delivery_orders|parts)$/;
+
+ my $query = "SELECT mtime, itime FROM " . $table . " WHERE id = ?";
+ my $ref = selectfirst_hashref_query($self, $dbh, $query, $self->{id});
+ $ref->{mtime} ||= $ref->{itime};
+ $self->{lastmtime} = $ref->{mtime};
+ $main::lxdebug->message(LXDebug->DEBUG2(),"new lastmtime=".$self->{lastmtime});
+
+ $main::lxdebug->leave_sub();
+}
+
+sub mtime_ischanged {
+ my ($self, $table, $option) = @_;
+
+ return unless $self->{id};
+ croak ("wrong call, no valid table defined") unless $table =~ /^(oe|ar|ap|delivery_orders|parts)$/;
+
+ my $query = "SELECT mtime, itime FROM " . $table . " WHERE id = ?";
+ my $ref = selectfirst_hashref_query($self, $self->get_standard_dbh, $query, $self->{id});
+ $ref->{mtime} ||= $ref->{itime};
+
+ if ($self->{lastmtime} && $self->{lastmtime} ne $ref->{mtime} ) {
+ $self->error(($option eq 'mail') ?
+ t8("The document has been changed by another user. No mail was sent. Please reopen it in another window and copy the changes to the new window") :
+ t8("The document has been changed by another user. Please reopen it in another window and copy the changes to the new window")
+ );
+ $::dispatcher->end_request;
+ }
+}
+
+sub language_payment {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $myconfig) = @_;
+
+ my $dbh = $self->get_standard_dbh($myconfig);
+ # get languages
+ my $query = qq|SELECT id, description
+ FROM language
+ ORDER BY id|;
+
+ $self->{languages} = selectall_hashref_query($self, $dbh, $query);
+
+ # get printer
+ $query = qq|SELECT printer_description, id
+ FROM printers
+ ORDER BY printer_description|;
+
+ $self->{printers} = selectall_hashref_query($self, $dbh, $query);
+
+ # get payment terms
+ $query = qq|SELECT id, description
+ FROM payment_terms
+ ORDER BY sortkey|;
+
+ $self->{payment_terms} = selectall_hashref_query($self, $dbh, $query);
+
+ # get buchungsgruppen
+ $query = qq|SELECT id, description
+ FROM buchungsgruppen|;
+
+ $self->{BUCHUNGSGRUPPEN} = selectall_hashref_query($self, $dbh, $query);
+
+ $main::lxdebug->leave_sub();
+}
+
+# this is only used for reports
+sub all_departments {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $myconfig, $table) = @_;
+
+ my $dbh = $self->get_standard_dbh($myconfig);
+
+ my $query = qq|SELECT id, description
+ FROM department
+ ORDER BY description|;
+ $self->{all_departments} = selectall_hashref_query($self, $dbh, $query);
+
+ delete($self->{all_departments}) unless (@{ $self->{all_departments} || [] });
+
+ $main::lxdebug->leave_sub();
+}
+
+sub create_links {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $module, $myconfig, $table, $provided_dbh) = @_;
+
+ my ($fld, $arap);
+ if ($table eq "customer") {
+ $fld = "buy";
+ $arap = "ar";
+ } else {
+ $table = "vendor";
+ $fld = "sell";
+ $arap = "ap";
+ }
+
+ $self->all_vc($myconfig, $table, $module);
+
+ # get last customers or vendors
+ my ($query, $sth, $ref);
+
+ my $dbh = $provided_dbh ? $provided_dbh : $self->get_standard_dbh($myconfig);
+ my %xkeyref = ();
+
+ if (!$self->{id}) {
+
+ my $transdate = "current_date";
+ if ($self->{transdate}) {
+ $transdate = $dbh->quote($self->{transdate});
+ }
+
+ # now get the account numbers
+# $query = qq|SELECT c.accno, c.description, c.link, c.taxkey_id, tk.tax_id
+# FROM chart c, taxkeys tk
+# WHERE (c.link LIKE ?) AND (c.id = tk.chart_id) AND tk.id =
+# (SELECT id FROM taxkeys WHERE (taxkeys.chart_id = c.id) AND (startdate <= $transdate) ORDER BY startdate DESC LIMIT 1)
+# ORDER BY c.accno|;
+
+# same query as above, but without expensive subquery for each row. about 80% faster
+ $query = qq|
+ SELECT c.accno, c.description, c.link, c.taxkey_id, tk2.tax_id
+ FROM chart c
+ -- find newest entries in taxkeys
+ INNER JOIN (
+ SELECT chart_id, MAX(startdate) AS startdate
+ FROM taxkeys
+ WHERE (startdate <= $transdate)
+ GROUP BY chart_id
+ ) tk ON (c.id = tk.chart_id)
+ -- and load all of those entries
+ INNER JOIN taxkeys tk2
+ ON (tk.chart_id = tk2.chart_id AND tk.startdate = tk2.startdate)
+ WHERE (c.link LIKE ?)
+ ORDER BY c.accno|;
+
+ $sth = $dbh->prepare($query);
+
+ do_statement($self, $sth, $query, like($module));
+
+ $self->{accounts} = "";
+ while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
+
+ foreach my $key (split(/:/, $ref->{link})) {
+ if ($key =~ /\Q$module\E/) {
+
+ # cross reference for keys
+ $xkeyref{ $ref->{accno} } = $key;
+
+ push @{ $self->{"${module}_links"}{$key} },
+ { accno => $ref->{accno},
+ description => $ref->{description},
+ taxkey => $ref->{taxkey_id},
+ tax_id => $ref->{tax_id} };
+
+ $self->{accounts} .= "$ref->{accno} " unless $key =~ /tax/;
+ }
+ }
+ }
+ }
+
+ # get taxkeys and description
+ $query = qq|SELECT id, taxkey, taxdescription FROM tax|;
+ $self->{TAXKEY} = selectall_hashref_query($self, $dbh, $query);
+
+ if (($module eq "AP") || ($module eq "AR")) {
+ # get tax rates and description
+ $query = qq|SELECT * FROM tax|;
+ $self->{TAX} = selectall_hashref_query($self, $dbh, $query);
+ }
+
+ my $extra_columns = '';
+ $extra_columns .= 'a.direct_debit, ' if ($module eq 'AR') || ($module eq 'AP');
+
+ if ($self->{id}) {
+ $query =
+ qq|SELECT
+ a.cp_id, a.invnumber, a.transdate, a.${table}_id, a.datepaid,
+ a.duedate, a.ordnumber, a.taxincluded, (SELECT cu.name FROM currencies cu WHERE cu.id=a.currency_id) AS currency, a.notes,
+ a.mtime, a.itime,
+ 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}
+ c.name AS $table,
+ d.description AS department,
+ e.name AS employee
+ FROM $arap a
+ JOIN $table c ON (a.${table}_id = c.id)
+ LEFT JOIN employee e ON (e.id = a.employee_id)
+ LEFT JOIN department d ON (d.id = a.department_id)
+ WHERE a.id = ?|;
+ $ref = selectfirst_hashref_query($self, $dbh, $query, $self->{id});
+
+ foreach my $key (keys %$ref) {
+ $self->{$key} = $ref->{$key};
+ }
+ $self->{mtime} ||= $self->{itime};
+ $self->{lastmtime} = $self->{mtime};
projects / mfinanz.git / blobdiff