X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FGL.pm;h=9e3806a05b1878cc967b0fbe54e60371eee180e7;hb=c078749ac84160099a6bca79c288f4f11e871eb8;hp=ade1948badb83c60c3a7dc245f267c042c03a33e;hpb=d1408ca13458a782cbde2b768cdd7abdbdcb9348;p=kivitendo-erp.git diff --git a/SL/GL.pm b/SL/GL.pm index ade1948ba..9e3806a05 100644 --- a/SL/GL.pm +++ b/SL/GL.pm @@ -41,6 +41,8 @@ package GL; use Data::Dumper; use SL::DATEV qw(:CONSTANTS); use SL::DBUtils; +use SL::Util qw(trim); +use SL::DB; use strict; @@ -48,22 +50,25 @@ sub delete_transaction { my ($self, $myconfig, $form) = @_; $main::lxdebug->enter_sub(); - # connect to database - my $dbh = $form->dbconnect_noauto($myconfig); + SL::DB->client->with_transaction(sub { + do_query($form, SL::DB->client->dbh, qq|DELETE FROM gl WHERE id = ?|, conv_i($form->{id})); + 1; + }) or do { die SL::DB->client->error }; - # acc_trans entries are deleted by database triggers. - do_query($form, $dbh, qq|DELETE FROM gl WHERE id = ?|, conv_i($form->{id})); - - # commit and redirect - my $rc = $dbh->commit; - $dbh->disconnect; $main::lxdebug->leave_sub(); +} - $rc; +sub post_transaction { + my ($self, $myconfig, $form) = @_; + $main::lxdebug->enter_sub(); + my $rc = SL::DB->client->with_transaction(\&_post_transaction, $self, $myconfig, $form); + + $::lxdebug->leave_sub; + return $rc; } -sub post_transaction { +sub _post_transaction { my ($self, $myconfig, $form) = @_; $main::lxdebug->enter_sub(); @@ -72,8 +77,7 @@ sub post_transaction { my $i; - # connect to database, turn off AutoCommit - my $dbh = $form->dbconnect_noauto($myconfig); + my $dbh = SL::DB->client->dbh; # post the transaction # make up a unique handle and store in reference field @@ -104,7 +108,7 @@ sub post_transaction { $query = qq|INSERT INTO gl (id, employee_id) | . qq|VALUES (?, (SELECT id FROM employee WHERE login = ?))|; - @values = ($form->{id}, $form->{login}); + @values = ($form->{id}, $::myconfig{login}); do_query($form, $dbh, $query, @values); } @@ -171,7 +175,11 @@ sub post_transaction { qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, source, memo, project_id, taxkey, tax_id, chart_link) VALUES (?, (SELECT chart_id FROM tax WHERE id = ?), - ?, ?, ?, ?, ?, ?, ?, (SELECT link FROM chart WHERE accno = ?))|; + ?, ?, ?, ?, ?, ?, ?, (SELECT link + FROM chart + WHERE id = (SELECT chart_id + FROM tax + WHERE id = ?)))|; @values = (conv_i($form->{id}), conv_i($form->{"tax_id_$i"}), $tax, conv_date($form->{transdate}), $form->{"source_$i"}, $form->{"memo_$i"}, $project_id, $taxkey, conv_i($form->{"tax_id_$i"}), conv_i($form->{"tax_id_$i"})); @@ -192,32 +200,24 @@ sub post_transaction { exporttype => DATEV_ET_BUCHUNGEN, format => DATEV_FORMAT_KNE, dbh => $dbh, - from => $transdate, - to => $transdate, + trans_id => $form->{id}, ); $datev->export; if ($datev->errors) { - $dbh->rollback; die join "\n", $::locale->text('DATEV check returned errors:'), $datev->errors; } } - # commit and redirect - my $rc = $dbh->commit; - $dbh->disconnect; - $main::lxdebug->leave_sub(); - - return $rc; + return 1; } sub all_transactions { my ($self, $myconfig, $form) = @_; $main::lxdebug->enter_sub(); - # connect to database - my $dbh = $form->dbconnect($myconfig); + my $dbh = SL::DB->client->dbh; my ($query, $sth, $source, $null, $space); my ($glwhere, $arwhere, $apwhere) = ("1 = 1", "1 = 1", "1 = 1"); @@ -227,9 +227,9 @@ sub all_transactions { $glwhere .= qq| AND g.reference ILIKE ?|; $arwhere .= qq| AND a.invnumber ILIKE ?|; $apwhere .= qq| AND a.invnumber ILIKE ?|; - push(@glvalues, '%' . $form->{reference} . '%'); - push(@arvalues, '%' . $form->{reference} . '%'); - push(@apvalues, '%' . $form->{reference} . '%'); + push(@glvalues, like($form->{reference})); + push(@arvalues, like($form->{reference})); + push(@apvalues, like($form->{reference})); } if ($form->{department}) { @@ -246,44 +246,42 @@ sub all_transactions { $glwhere .= " AND ac.trans_id IN (SELECT trans_id from acc_trans WHERE source ILIKE ?)"; $arwhere .= " AND ac.trans_id IN (SELECT trans_id from acc_trans WHERE source ILIKE ?)"; $apwhere .= " AND ac.trans_id IN (SELECT trans_id from acc_trans WHERE source ILIKE ?)"; - push(@glvalues, '%' . $form->{source} . '%'); - push(@arvalues, '%' . $form->{source} . '%'); - push(@apvalues, '%' . $form->{source} . '%'); + push(@glvalues, like($form->{source})); + push(@arvalues, like($form->{source})); + push(@apvalues, like($form->{source})); } # default Datumseinschränkung falls nicht oder falsch übergeben (sollte nie passieren) $form->{datesort} = 'transdate' unless $form->{datesort} =~ /^(transdate|gldate)$/; - if ($form->{datefrom}) { + if (trim($form->{datefrom})) { $glwhere .= " AND ac.$form->{datesort} >= ?"; $arwhere .= " AND ac.$form->{datesort} >= ?"; $apwhere .= " AND ac.$form->{datesort} >= ?"; - push(@glvalues, $form->{datefrom}); - push(@arvalues, $form->{datefrom}); - push(@apvalues, $form->{datefrom}); + push(@glvalues, trim($form->{datefrom})); + push(@arvalues, trim($form->{datefrom})); + push(@apvalues, trim($form->{datefrom})); } - if ($form->{dateto}) { + if (trim($form->{dateto})) { $glwhere .= " AND ac.$form->{datesort} <= ?"; $arwhere .= " AND ac.$form->{datesort} <= ?"; $apwhere .= " AND ac.$form->{datesort} <= ?"; - push(@glvalues, $form->{dateto}); - push(@arvalues, $form->{dateto}); - push(@apvalues, $form->{dateto}); + push(@glvalues, trim($form->{dateto})); + push(@arvalues, trim($form->{dateto})); + push(@apvalues, trim($form->{dateto})); } - if ($form->{description}) { + if (trim($form->{description})) { $glwhere .= " AND g.description ILIKE ?"; $arwhere .= " AND ct.name ILIKE ?"; $apwhere .= " AND ct.name ILIKE ?"; - push(@glvalues, '%' . $form->{description} . '%'); - push(@arvalues, '%' . $form->{description} . '%'); - push(@apvalues, '%' . $form->{description} . '%'); + push(@glvalues, like($form->{description})); + push(@arvalues, like($form->{description})); + push(@apvalues, like($form->{description})); } - - if ($form->{employee} =~ /--/) { - ($form->{employee_id},$form->{employee_name}) = split(/--/,$form->{employee}); - #if ($form->{employee_id}) { + + if ($form->{employee_id}) { $glwhere .= " AND g.employee_id = ? "; $arwhere .= " AND a.employee_id = ? "; $apwhere .= " AND a.employee_id = ? "; @@ -292,13 +290,13 @@ sub all_transactions { push(@apvalues, conv_i($form->{employee_id})); } - if ($form->{notes}) { + if (trim($form->{notes})) { $glwhere .= " AND g.notes ILIKE ?"; $arwhere .= " AND a.notes ILIKE ?"; $apwhere .= " AND a.notes ILIKE ?"; - push(@glvalues, '%' . $form->{notes} . '%'); - push(@arvalues, '%' . $form->{notes} . '%'); - push(@apvalues, '%' . $form->{notes} . '%'); + push(@glvalues, like($form->{notes})); + push(@arvalues, like($form->{notes})); + push(@apvalues, like($form->{notes})); } if ($form->{accno}) { @@ -351,8 +349,6 @@ sub all_transactions { } } - my $false = ($myconfig->{dbdriver} eq 'Pg') ? "FALSE" : q|'0'|; - my %sort_columns = ( 'id' => [ qw(id) ], 'transdate' => [ qw(transdate id) ], @@ -366,7 +362,7 @@ sub all_transactions { 'source' => { 'gl' => 'ac.source', 'arap' => 'ac.source', }, 'description' => { 'gl' => 'g.description', 'arap' => 'ct.name', }, ); - + # sortdir = sort direction (ascending or descending) my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC'; my $sortkey = $sort_columns{$form->{sort}} ? $form->{sort} : $form->{datesort}; # default used to be transdate @@ -382,7 +378,7 @@ sub all_transactions { $query = qq|SELECT - ac.acc_trans_id, g.id, 'gl' AS type, $false AS invoice, g.reference, ac.taxkey, c.link, + ac.acc_trans_id, g.id, 'gl' AS type, FALSE AS invoice, g.reference, ac.taxkey, c.link, g.description, ac.transdate, ac.gldate, ac.source, ac.trans_id, ac.amount, c.accno, g.notes, t.chart_id, CASE WHEN (COALESCE(e.name, '') = '') THEN e.login ELSE e.name END AS employee @@ -620,8 +616,6 @@ sub all_transactions { ($form->{account_description}) = selectrow_query($form, $dbh, $query, $form->{accno}); } - $dbh->disconnect; - $main::lxdebug->leave_sub(); } @@ -631,8 +625,7 @@ sub transaction { my ($query, $sth, $ref, @values); - # connect to database - my $dbh = $form->dbconnect($myconfig); + my $dbh = SL::DB->client->dbh; $query = qq|SELECT closedto, revtrans FROM defaults|; ($form->{closedto}, $form->{revtrans}) = selectrow_query($form, $dbh, $query); @@ -669,19 +662,7 @@ sub transaction { FROM acc_trans a 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 = - (SELECT tk.tax_id - FROM taxkeys tk - WHERE (tk.taxkey_id = a.taxkey) AND - ((CASE WHEN a.chart_id IN - (SELECT chart_id FROM taxkeys WHERE taxkey_id = a.taxkey) - THEN tk.chart_id = a.chart_id - ELSE 1 = 1 - END) - OR (c.link LIKE '%tax%')) - AND (startdate <= a.transdate) - ORDER BY startdate DESC LIMIT 1)) + 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|; @@ -716,18 +697,24 @@ sub transaction { ORDER BY c.accno|; $form->{chart} = selectall_hashref_query($form, $dbh, $query, conv_date($form->{transdate})); - $dbh->disconnect; - $main::lxdebug->leave_sub(); } sub storno { + my ($self, $form, $myconfig, $id) = @_; $main::lxdebug->enter_sub(); + my $rc = SL::DB->client->with_transaction(\&_storno, $self, $form, $myconfig, $id); + + $::lxdebug->leave_sub; + return $rc; +} + +sub _storno { my ($self, $form, $myconfig, $id) = @_; my ($query, $new_id, $storno_row, $acc_trans_rows); - my $dbh = $form->get_standard_dbh($myconfig); + my $dbh = SL::DB->client->dbh; $query = qq|SELECT nextval('glid')|; ($new_id) = selectrow_query($form, $dbh, $query); @@ -735,10 +722,14 @@ sub storno { $query = qq|SELECT * FROM gl WHERE id = ?|; $storno_row = selectfirst_hashref_query($form, $dbh, $query, $id); - $storno_row->{id} = $new_id; - $storno_row->{storno_id} = $id; - $storno_row->{storno} = 't'; - $storno_row->{reference} = 'Storno-' . $storno_row->{reference}; + $storno_row->{id} = $new_id; + $storno_row->{storno_id} = $id; + $storno_row->{storno} = 't'; + $storno_row->{reference} = 'Storno-' . $storno_row->{reference}; + + $query = qq|SELECT id FROM employee WHERE login = ?|; + my ($employee_id) = selectrow_query($form, $dbh, $query, $::myconfig{login}); + $storno_row->{employee_id} = $employee_id; delete @$storno_row{qw(itime mtime gldate)}; @@ -760,9 +751,7 @@ sub storno { do_query($form, $dbh, $query, (values %$row)); } - $dbh->commit; - - $main::lxdebug->leave_sub(); + return 1; } sub get_chart_balances { @@ -799,5 +788,27 @@ sub get_chart_balances { $main::lxdebug->leave_sub(); } +sub get_tax_dropdown { + my ($self, $accno) = @_; + + my $myconfig = \%main::myconfig; + my $form = $main::form; + + my $dbh = $form->get_standard_dbh($myconfig); + + my $query = qq|SELECT category FROM chart WHERE accno = ?|; + my ($category) = selectrow_query($form, $dbh, $query, $accno); + + $query = qq|SELECT * FROM tax WHERE chart_categories like '%$category%' order by taxkey, rate|; + + my $sth = prepare_execute_query($form, $dbh, $query); + + my @tax_accounts = (); + while (my $ref = $sth->fetchrow_hashref("NAME_lc")) { + push(@tax_accounts, $ref); + } + + return @tax_accounts; +} 1;