Revision 032e5fcd
Von Moritz Bunkus vor mehr als 16 Jahren hinzugefügt
| SL/DATEV.pm | ||
|---|---|---|
| 26 | 26 |
|
| 27 | 27 |
package DATEV; |
| 28 | 28 |
|
| 29 |
use SL::DBUtils; |
|
| 30 |
|
|
| 29 | 31 |
use Data::Dumper; |
| 30 | 32 |
|
| 31 | 33 |
sub get_datev_stamm {
|
| ... | ... | |
| 217 | 219 |
sub get_transactions {
|
| 218 | 220 |
$main::lxdebug->enter_sub(); |
| 219 | 221 |
|
| 220 |
my ($myconfig, $form, $fromto) = @_; |
|
| 221 |
|
|
| 222 |
# connect to database |
|
| 223 |
my $dbh = $form->dbconnect($myconfig); |
|
| 222 |
my $dbh = $form->get_standard_dbh($myconfig); |
|
| 224 | 223 |
|
| 225 | 224 |
$fromto =~ s/transdate/ac\.transdate/g; |
| 226 | 225 |
|
| 227 |
$query = qq|SELECT id, taxkey, rate FROM tax|; |
|
| 228 |
$sth = $dbh->prepare($query); |
|
| 229 |
$sth->execute || $form->dberror($query); |
|
| 226 |
my %taxes = selectall_as_map($form, $dbh, qq|SELECT id, rate FROM tax|, 'id', 'rate'); |
|
| 227 |
|
|
| 228 |
my $query = |
|
| 229 |
qq|SELECT ac.oid, ac.transdate, ac.trans_id,ar.id, ac.amount, ac.taxkey, |
|
| 230 |
ar.invnumber, ar.duedate, ar.amount as umsatz, |
|
| 231 |
ct.name, |
|
| 232 |
c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, |
|
| 233 |
t.chart_id, t.rate, t.id AS taxid, t.taxkey AS taxtaxkey |
|
| 234 |
FROM acc_trans ac,ar ar, customer ct, chart c |
|
| 235 |
LEFT JOIN tax t ON (t.chart_id = c.id) |
|
| 236 |
WHERE $fromto |
|
| 237 |
AND (ac.trans_id = ar.id) |
|
| 238 |
AND (ac.trans_id = ar.id) |
|
| 239 |
AND (ar.customer_id = ct.id) |
|
| 240 |
AND (ac.chart_id = c.id) |
|
| 241 |
|
|
| 242 |
UNION ALL |
|
| 243 |
|
|
| 244 |
SELECT ac.oid, ac.transdate, ac.trans_id,ap.id, ac.amount, ac.taxkey, |
|
| 245 |
ap.invnumber, ap.duedate, ap.amount as umsatz, |
|
| 246 |
ct.name, |
|
| 247 |
c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, |
|
| 248 |
t.chart_id, t.rate, t.id AS taxid, t.taxkey AS taxtaxkey |
|
| 249 |
FROM acc_trans ac, ap ap, vendor ct, chart c |
|
| 250 |
LEFT JOIN tax t ON (t.chart_id = c.id) |
|
| 251 |
WHERE $fromto |
|
| 252 |
AND (ac.trans_id = ap.id) |
|
| 253 |
AND (ap.vendor_id = ct.id) |
|
| 254 |
AND (ac.chart_id = c.id) |
|
| 255 |
|
|
| 256 |
UNION ALL |
|
| 257 |
|
|
| 258 |
SELECT ac.oid, ac.transdate, ac.trans_id,gl.id, ac.amount, ac.taxkey, |
|
| 259 |
gl.reference AS invnumber, gl.transdate AS duedate, ac.amount as umsatz, |
|
| 260 |
gl.description AS name, |
|
| 261 |
c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, |
|
| 262 |
t.chart_id, t.rate, t.id AS taxid, t.taxkey AS taxtaxkey |
|
| 263 |
FROM acc_trans ac, gl gl, chart c |
|
| 264 |
LEFT JOIN tax t ON (t.chart_id = c.id) |
|
| 265 |
WHERE $fromto |
|
| 266 |
AND (ac.trans_id = gl.id) |
|
| 267 |
AND (ac.chart_id = c.id) |
|
| 268 |
|
|
| 269 |
ORDER BY trans_id, oid|; |
|
| 270 |
|
|
| 271 |
my $sth = prepare_execute_query($form, $dbh, $query); |
|
| 230 | 272 |
|
| 231 |
while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
|
|
| 232 |
$taxes{ $ref->{id} } = $ref->{rate};
|
|
| 233 |
} |
|
| 234 |
|
|
| 235 |
$sth->finish(); |
|
| 236 |
|
|
| 237 |
$query = |
|
| 238 |
qq|SELECT ac.oid, ac.transdate, ac.trans_id,ar.id, ac.amount, ac.taxkey, ar.invnumber, ar.duedate, ar.amount as umsatz, |
|
| 239 |
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, |
|
| 240 |
chart c LEFT JOIN tax t ON |
|
| 241 |
(t.chart_id=c.id)WHERE $fromto AND ac.trans_id=ar.id AND ac.trans_id=ar.id |
|
| 242 |
AND ar.customer_id=ct.id AND ac.chart_id=c.id |
|
| 243 |
UNION ALL |
|
| 244 |
SELECT ac.oid, ac.transdate, ac.trans_id,ap.id, ac.amount, ac.taxkey, ap.invnumber, ap.duedate, ap.amount as umsatz, |
|
| 245 |
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 |
|
| 246 |
(t.chart_id=c.id) |
|
| 247 |
WHERE $fromto AND ac.trans_id=ap.id AND ap.vendor_id=ct.id AND ac.chart_id=c.id |
|
| 248 |
UNION ALL |
|
| 249 |
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, |
|
| 250 |
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, |
|
| 251 |
chart c LEFT JOIN tax t ON |
|
| 252 |
(t.chart_id=c.id) WHERE $fromto AND ac.trans_id=gl.id AND ac.chart_id=c.id |
|
| 253 |
ORDER BY trans_id, oid|; |
|
| 254 |
|
|
| 255 |
$sth = $dbh->prepare($query); |
|
| 256 |
$sth->execute || $form->dberror($query); |
|
| 257 | 273 |
$i = 0; |
| 258 | 274 |
$g = 0; |
| 259 | 275 |
my $counter = 0; |
| 260 |
@splits; |
|
| 276 |
my @splits;
|
|
| 261 | 277 |
while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
|
| 262 | 278 |
$count = 0; |
| 263 | 279 |
$firstrun = 1; |
Auch abrufbar als: Unified diff
Kosmetik