+ 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},
+ chart_id => $ref->{chart_id},
+ 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};
+ 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, c.id AS chart_id, tk.tax_id
+ FROM chart c
+ LEFT JOIN taxkeys tk ON (tk.chart_id = c.id)
+ WHERE c.link LIKE ?
+ AND (tk.id = (SELECT id FROM taxkeys WHERE taxkeys.chart_id = c.id AND startdate <= $transdate ORDER BY startdate DESC LIMIT 1)
+ OR c.link LIKE '%_tax%' OR c.taxkey_id IS NULL)
+ 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},
+ chart_id => $ref->{chart_id},
+ description => $ref->{description},
+ taxkey => $ref->{taxkey_id},
+ tax_id => $ref->{tax_id} };
+
+ $self->{accounts} .= "$ref->{accno} " unless $key =~ /tax/;
+ }
+ }
+ }
+
+
+ # get amounts from individual entries
+ $query =
+ qq|SELECT
+ c.accno, c.description,
+ a.acc_trans_id, a.source, a.amount, a.memo, a.transdate, a.gldate, a.cleared, a.project_id, a.taxkey, a.chart_id,
+ p.projectnumber,
+ t.rate, t.id
+ FROM acc_trans a
+ LEFT JOIN chart c ON (c.id = a.chart_id)
+ LEFT JOIN project p ON (p.id = a.project_id)
+ LEFT JOIN tax t ON (t.id= a.tax_id)
+ WHERE a.trans_id = ?
+ AND a.fx_transaction = '0'
+ ORDER BY a.acc_trans_id, a.transdate|;
+ $sth = $dbh->prepare($query);
+ do_statement($self, $sth, $query, $self->{id});
+
+ # get exchangerate for currency
+ $self->{exchangerate} =
+ $self->get_exchangerate($dbh, $self->{currency}, $self->{transdate}, $fld);
+ my $index = 0;
+
+ # store amounts in {acc_trans}{$key} for multiple accounts
+ while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
+ $ref->{exchangerate} =
+ $self->get_exchangerate($dbh, $self->{currency}, $ref->{transdate}, $fld);
+ if (!($xkeyref{ $ref->{accno} } =~ /tax/)) {
+ $index++;
+ }
+ if (($xkeyref{ $ref->{accno} } =~ /paid/) && ($self->{type} eq "credit_note")) {
+ $ref->{amount} *= -1;
+ }
+ $ref->{index} = $index;
+
+ push @{ $self->{acc_trans}{ $xkeyref{ $ref->{accno} } } }, $ref;
+ }
+
projects / mfinanz.git / blobdiff