X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FGL.pm;h=46c2d49b193b5f2bbdafcb2b7726d6286d2fac3a;hb=95b5d54bac9dc0cb47c67444c9e19c1d68b0d520;hp=a1d3a9d2221215cac4af804ee9a89d996883db28;hpb=36666afc8fe77a7c4922932101761facefffebc1;p=kivitendo-erp.git diff --git a/SL/GL.pm b/SL/GL.pm index a1d3a9d22..46c2d49b1 100644 --- a/SL/GL.pm +++ b/SL/GL.pm @@ -123,13 +123,15 @@ sub _post_transaction { $query = qq|UPDATE gl SET reference = ?, description = ?, notes = ?, - transdate = ?, deliverydate = ?, department_id = ?, taxincluded = ?, - storno = ?, storno_id = ?, ob_transaction = ?, cb_transaction = ? + transdate = ?, deliverydate = ?, tax_point = ?, department_id = ?, taxincluded = ?, + storno = ?, storno_id = ?, ob_transaction = ?, cb_transaction = ?, + transaction_description = ? WHERE id = ?|; @values = ($form->{reference}, $form->{description}, $form->{notes}, - conv_date($form->{transdate}), conv_date($form->{deliverydate}), conv_i($form->{department_id}), $form->{taxincluded} ? 't' : 'f', + conv_date($form->{transdate}), conv_date($form->{deliverydate}), conv_date($form->{tax_point}), conv_i($form->{department_id}), $form->{taxincluded} ? 't' : 'f', $form->{storno} ? 't' : 'f', conv_i($form->{storno_id}), $form->{ob_transaction} ? 't' : 'f', $form->{cb_transaction} ? 't' : 'f', + $form->{transaction_description}, conv_i($form->{id})); do_query($form, $dbh, $query, @values); @@ -299,6 +301,15 @@ sub all_transactions { push(@apvalues, like($form->{notes})); } + if (trim($form->{transaction_description})) { + $glwhere .= " AND g.transaction_description ILIKE ?"; + $arwhere .= " AND a.transaction_description ILIKE ?"; + $apwhere .= " AND a.transaction_description ILIKE ?"; + push(@glvalues, like($form->{transaction_description})); + push(@arvalues, like($form->{transaction_description})); + push(@apvalues, like($form->{transaction_description})); + } + if ($form->{accno}) { $glwhere .= " AND c.accno = '$form->{accno}'"; $arwhere .= " AND c.accno = '$form->{accno}'"; @@ -328,10 +339,15 @@ sub all_transactions { push(@apvalues, $project_id, $project_id); } - my ($project_columns, $project_join); + my ($project_columns, $project_join); + my ($arap_globalproject_columns, $arap_globalproject_join); + my ($gl_globalproject_columns); if ($form->{"l_projectnumbers"}) { - $project_columns = qq|, ac.project_id, pr.projectnumber|; - $project_join = qq|LEFT JOIN project pr ON (ac.project_id = pr.id)|; + $project_columns = qq|, ac.project_id, pr.projectnumber|; + $project_join = qq|LEFT JOIN project pr ON (ac.project_id = pr.id)|; + $arap_globalproject_columns = qq|, a.globalproject_id, globalpr.projectnumber AS globalprojectnumber|; + $arap_globalproject_join = qq|LEFT JOIN project globalpr ON (a.globalproject_id = globalpr.id)|; + $gl_globalproject_columns = qq|, NULL AS globalproject_id, '' AS globalprojectnumber|; } if ($form->{accno}) { @@ -350,17 +366,20 @@ sub all_transactions { } my %sort_columns = ( - 'id' => [ qw(id) ], - 'transdate' => [ qw(transdate id) ], - 'gldate' => [ qw(gldate id) ], - 'reference' => [ qw(lower_reference id) ], - 'description' => [ qw(lower_description id) ], - 'accno' => [ qw(accno transdate id) ], + 'id' => [ qw(id) ], + 'transdate' => [ qw(transdate id) ], + 'gldate' => [ qw(gldate id) ], + 'reference' => [ qw(lower_reference id) ], + 'description' => [ qw(lower_description id) ], + 'accno' => [ qw(accno transdate id) ], + 'department' => [ qw(department transdate id) ], + 'transaction_description' => [ qw(lower_transaction_description id) ], ); my %lowered_columns = ( - 'reference' => { 'gl' => 'g.reference', 'arap' => 'a.invnumber', }, - 'source' => { 'gl' => 'ac.source', 'arap' => 'ac.source', }, - 'description' => { 'gl' => 'g.description', 'arap' => 'ct.name', }, + 'reference' => { 'gl' => 'g.reference', 'arap' => 'a.invnumber', }, + 'source' => { 'gl' => 'ac.source', 'arap' => 'ac.source', }, + 'description' => { 'gl' => 'g.description', 'arap' => 'ct.name', }, + 'transaction_description' => { 'gl' => 'g.transaction_description', 'arap' => 'a.transaction_description', }, ); # sortdir = sort direction (ascending or descending) @@ -381,11 +400,13 @@ sub all_transactions { 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, + d.description AS department, g.transaction_description, CASE WHEN (COALESCE(e.name, '') = '') THEN e.login ELSE e.name END AS employee - $project_columns + $project_columns $gl_globalproject_columns $columns_for_sorting{gl} FROM gl g - LEFT JOIN employee e ON (g.employee_id = e.id), + LEFT JOIN employee e ON (g.employee_id = e.id) + LEFT JOIN department d ON (g.department_id = d.id), acc_trans ac $project_join, chart c LEFT JOIN tax t ON (t.chart_id = c.id) WHERE $glwhere @@ -397,11 +418,14 @@ sub all_transactions { SELECT ac.acc_trans_id, a.id, 'ar' AS type, a.invoice, a.invnumber, ac.taxkey, c.link, ct.name, ac.transdate, ac.gldate, ac.source, ac.trans_id, ac.amount, c.accno, a.notes, t.chart_id, + d.description AS department, a.transaction_description, CASE WHEN (COALESCE(e.name, '') = '') THEN e.login ELSE e.name END AS employee - $project_columns + $project_columns $arap_globalproject_columns $columns_for_sorting{arap} FROM ar a - LEFT JOIN employee e ON (a.employee_id = e.id), + LEFT JOIN employee e ON (a.employee_id = e.id) + LEFT JOIN department d ON (a.department_id = d.id) + $arap_globalproject_join, acc_trans ac $project_join, customer ct, chart c LEFT JOIN tax t ON (t.chart_id=c.id) WHERE $arwhere @@ -414,11 +438,14 @@ sub all_transactions { SELECT ac.acc_trans_id, a.id, 'ap' AS type, a.invoice, a.invnumber, ac.taxkey, c.link, ct.name, ac.transdate, ac.gldate, ac.source, ac.trans_id, ac.amount, c.accno, a.notes, t.chart_id, + d.description AS department, a.transaction_description, CASE WHEN (COALESCE(e.name, '') = '') THEN e.login ELSE e.name END AS employee - $project_columns + $project_columns $arap_globalproject_columns $columns_for_sorting{arap} FROM ap a - LEFT JOIN employee e ON (a.employee_id = e.id), + LEFT JOIN employee e ON (a.employee_id = e.id) + LEFT JOIN department d ON (a.department_id = d.id) + $arap_globalproject_join, acc_trans ac $project_join, vendor ct, chart c LEFT JOIN tax t ON (t.chart_id=c.id) WHERE $apwhere @@ -484,7 +511,8 @@ sub all_transactions { } $ref->{"projectnumbers"} = {}; - $ref->{"projectnumbers"}->{$ref->{"projectnumber"}} = 1 if ($ref->{"projectnumber"}); + $ref->{"projectnumbers"}->{$ref->{"projectnumber"}} = 1 if ($ref->{"projectnumber"}); + $ref->{"projectnumbers"}->{$ref->{"globalprojectnumber"}} = 1 if ($ref->{"globalprojectnumber"}); $balance = $ref->{amount}; @@ -540,7 +568,8 @@ sub all_transactions { $balance = (int($balance * 100000) + int(100000 * $ref2->{amount})) / 100000; - $ref->{"projectnumbers"}->{$ref2->{"projectnumber"}} = 1 if ($ref2->{"projectnumber"}); + $ref->{"projectnumbers"}->{$ref2->{"projectnumber"}} = 1 if ($ref2->{"projectnumber"}); + $ref->{"projectnumbers"}->{$ref2->{"globalprojectnumber"}} = 1 if ($ref2->{"globalprojectnumber"}); if ($ref2->{chart_id} > 0) { # all tax accounts, following lines if ($ref2->{amount} < 0) { @@ -637,11 +666,12 @@ sub transaction { if ($form->{id}) { $query = - qq|SELECT g.reference, g.description, g.notes, g.transdate, g.deliverydate, + qq|SELECT g.reference, g.description, g.notes, g.transdate, g.deliverydate, g.tax_point, g.storno, g.storno_id, g.department_id, d.description AS department, e.name AS employee, g.taxincluded, g.gldate, - g.ob_transaction, g.cb_transaction + g.ob_transaction, g.cb_transaction, + g.transaction_description FROM gl g LEFT JOIN department d ON (d.id = g.department_id) LEFT JOIN employee e ON (e.id = g.employee_id) @@ -773,12 +803,22 @@ sub get_chart_balances { } sub get_active_taxes_for_chart { - my ($self, $chart_id, $transdate) = @_; + my ($self, $chart_id, $transdate, $tax_id) = @_; my $chart = SL::DB::Chart->new(id => $chart_id)->load; my $active_taxkey = $chart->get_active_taxkey($transdate); + + my $where = [ chart_categories => { like => '%' . $chart->category . '%' } ]; + + if ( defined $tax_id && $tax_id >= 0 ) { + $where = [ or => [ chart_categories => { like => '%' . $chart->category . '%' }, + id => $tax_id + ] + ]; + } + my $taxes = SL::DB::Manager::Tax->get_all( - where => [ chart_categories => { like => '%' . $chart->category . '%' }], + where => $where, sort_by => 'taxkey, rate', ); @@ -789,3 +829,50 @@ sub get_active_taxes_for_chart { } 1; + +__END__ + +=pod + +=encoding utf8 + +=head1 NAME + +SL::GL - some useful GL functions + +=head1 FUNCTIONS + +=over 4 + +=item C $transdate $tax_id + +Returns a list of valid taxes for a certain chart. + +If the optional param transdate exists one entry in the returning list +may get the attribute C for this specific tax-dependent date. +The possible entries are filtered by the charttype of the tax, i.e. only taxes +whose chart_categories match the category of the chart will be shown. + +In the case of existing records, e.g. when opening an old ar record, due to +changes in the configurations the desired tax might not be available in the +dropdown anymore. If we are loading an old record and know its tax_id (from +acc_trans), we can pass $tax_id as the third parameter and be sure that the +original tax always appears in the dropdown. + +The functions returns an array which may be used for building dropdowns in ar/ap/gl code. + +=back + +=head1 TODO + +Nothing here yet. + +=head1 BUGS + +Nothing here yet. + +=head1 AUTHOR + +G. Richardson Egrichardson@kivitec.deE + +=cut