- my ($myconfig, $form, $fromto) = @_;
-
- # connect to database
- my $dbh = $form->dbconnect($myconfig);
-
- $fromto =~ s/transdate/ac\.transdate/g;
-
- $query = qq|SELECT taxkey, rate FROM tax|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
- $taxes{ $ref->{taxkey} } = $ref->{rate};
- }
-
- $sth->finish();
-
- $query =
- qq|SELECT ac.oid, ac.transdate, ac.trans_id,ar.id, ac.amount, ac.taxkey, ar.invnumber, ar.duedate, ar.amount as umsatz,
- ct.name, c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, t.chart_id, t.rate FROM acc_trans ac,ar ar, customer ct,
- chart c LEFT JOIN tax t ON
- (t.chart_id=c.id)WHERE $fromto AND ac.trans_id=ar.id AND ac.trans_id=ar.id
- AND ar.customer_id=ct.id AND ac.chart_id=c.id
- UNION ALL
- SELECT ac.oid, ac.transdate, ac.trans_id,ap.id, ac.amount, ac.taxkey, ap.invnumber, ap.duedate, ap.amount as umsatz,
- ct.name, c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, t.chart_id, t.rate FROM acc_trans ac, ap ap, vendor ct, chart c LEFT JOIN tax t ON
- (t.chart_id=c.id)
- WHERE $fromto AND ac.trans_id=ap.id AND ap.vendor_id=ct.id AND ac.chart_id=c.id
- UNION ALL
- SELECT ac.oid, ac.transdate, ac.trans_id,gl.id, ac.amount, ac.taxkey, gl.reference AS invnumber, gl.transdate AS duedate, ac.amount as umsatz,
- gl.description AS name, c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, t.chart_id, t.rate FROM acc_trans ac, gl gl,
- chart c LEFT JOIN tax t ON
- (t.chart_id=c.id) WHERE $fromto AND ac.trans_id=gl.id AND ac.chart_id=c.id
- ORDER BY trans_id, oid|;
-
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
- $i = 0;
- $g = 0;
+ my $fromto = shift;
+
+ my $myconfig = \%main::myconfig;
+ my $form = $main::form;
+
+ my $dbh = $form->get_standard_dbh($myconfig);
+
+ my @errors = ();
+
+ $fromto =~ s/transdate/ac\.transdate/g;
+
+ my %taxes = selectall_as_map($form, $dbh, qq|SELECT id, rate FROM tax|, 'id', 'rate');
+
+ my $query =
+ qq|SELECT ac.oid, ac.transdate, ac.trans_id,ar.id, ac.amount, ac.taxkey,
+ ar.invnumber, ar.duedate, ar.amount as umsatz,
+ ct.name,
+ c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id,
+ t.chart_id, t.rate, t.id AS taxid, t.taxkey AS taxtaxkey
+ FROM acc_trans ac,ar ar, customer ct, chart c
+ LEFT JOIN tax t ON (t.chart_id = c.id)
+ WHERE $fromto
+ AND (ac.trans_id = ar.id)
+ AND (ac.trans_id = ar.id)
+ AND (ar.customer_id = ct.id)
+ AND (ac.chart_id = c.id)
+
+ UNION ALL
+
+ SELECT ac.oid, ac.transdate, ac.trans_id,ap.id, ac.amount, ac.taxkey,
+ ap.invnumber, ap.duedate, ap.amount as umsatz,
+ ct.name,
+ c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id,
+ t.chart_id, t.rate, t.id AS taxid, t.taxkey AS taxtaxkey
+ FROM acc_trans ac, ap ap, vendor ct, chart c
+ LEFT JOIN tax t ON (t.chart_id = c.id)
+ WHERE $fromto
+ AND (ac.trans_id = ap.id)
+ AND (ap.vendor_id = ct.id)
+ AND (ac.chart_id = c.id)
+
+ UNION ALL
+
+ SELECT ac.oid, ac.transdate, ac.trans_id,gl.id, ac.amount, ac.taxkey,
+ gl.reference AS invnumber, gl.transdate AS duedate, ac.amount as umsatz,
+ gl.description AS name,
+ c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id,
+ t.chart_id, t.rate, t.id AS taxid, t.taxkey AS taxtaxkey
+ FROM acc_trans ac, gl gl, chart c
+ LEFT JOIN tax t ON (t.chart_id = c.id)
+ WHERE $fromto
+ AND (ac.trans_id = gl.id)
+ AND (ac.chart_id = c.id)
+
+ ORDER BY trans_id, oid|;
+
+ my $sth = prepare_execute_query($form, $dbh, $query);
+
+ my @splits;