X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FWH.pm;h=dac6911e29efac52d61819bdff5013ea10c35c03;hb=76a39ab4f52c7f25d18bb7dc714262ac8d400720;hp=eaba55332cf5f352398a19300ac73166ea3864c0;hpb=5067d7bd31514962af9730b33323b831d87164f8;p=kivitendo-erp.git diff --git a/SL/WH.pm b/SL/WH.pm index eaba55332..dac6911e2 100644 --- a/SL/WH.pm +++ b/SL/WH.pm @@ -25,7 +25,8 @@ # GNU General Public License for more details. # You should have received a copy of the GNU General Public License # along with this program; if not, write to the Free Software -# Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA. +# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, +# MA 02110-1335, USA. #====================================================================== # # Warehouse module @@ -419,10 +420,29 @@ sub get_warehouse_journal { my $sort_order = $form->{order}; $sort_col = $filter{sort} unless $sort_col; + $sort_col = 'shippingdate' if $sort_col eq 'date'; $sort_order = ($sort_col = 'shippingdate') unless $sort_col; - $sort_col = 'shippingdate' if $sort_col eq 'date'; - $sort_order = $filter{order} unless $sort_order; - my $sort_spec = "${sort_col} " . ($sort_order ? " DESC" : " ASC"); + + my %orderspecs = ( + 'shippingdate' => ['shippingdate', 'r_itime', 'r_parts_id'], + 'bin_to' => ['bin_to', 'r_itime', 'r_parts_id'], + 'bin_from' => ['bin_from', 'r_itime', 'r_parts_id'], + 'warehouse_to' => ['warehouse_to, r_itime, r_parts_id'], + 'warehouse_from' => ['warehouse_from, r_itime, r_parts_id'], + 'partnumber' => ['partnumber'], + 'partdescription'=> ['partdescription'], + 'partunit' => ['partunit, r_itime, r_parts_id'], + 'qty' => ['qty, r_itime, r_parts_id'], + 'oe_id' => ['oe_id'], + 'comment' => ['comment'], + 'trans_type' => ['trans_type'], + 'employee' => ['employee'], + 'projectnumber' => ['projectnumber'], + ); + + $sort_order = $filter{order} unless $sort_order; + my $ASC = ($sort_order ? " DESC" : " ASC"); + my $sort_spec = join("$ASC , ", @{$orderspecs{$sort_col}}). " $ASC"; my $where_clause = @filter_ary ? join(" AND ", @filter_ary) . " AND " : ''; @@ -432,8 +452,7 @@ sub get_warehouse_journal { "partnumber" => "p.partnumber", "partdescription" => "p.description", "classification_id" => "p.classification_id", - "assembly" => "p.assembly", - "inventory_accno_id" => "p.inventory_accno_id", + "part_type" => "p.part_type", "bindescription" => "b.description", "chargenumber" => "i1.chargenumber", "bestbefore" => "i1.bestbefore", @@ -466,8 +485,8 @@ sub get_warehouse_journal { }; $form->{l_classification_id} = 'Y'; - $form->{l_assembly} = 'Y'; - $form->{l_inventory_accno_id} = 'Y'; + $form->{l_part_type} = 'Y'; + $form->{l_itime} = 'Y'; $form->{l_invoice_id} = $form->{l_oe_id} if $form->{l_oe_id}; # build the select clauses. @@ -478,12 +497,12 @@ sub get_warehouse_journal { } my $group_clause = join ", ", map { +/^l_/; "r_$'" } - ( grep( { !/qty$/ and /^l_/ and $form->{$_} eq 'Y' } keys %$form), qw(l_parts_id l_partunit l_shippingdate) ); + ( grep( { !/qty$/ and /^l_/ and $form->{$_} eq 'Y' } keys %$form), qw(l_parts_id l_partunit l_shippingdate l_itime) ); $where_clause = defined($where_clause) ? $where_clause : ''; my $query = - qq|SELECT DISTINCT $select{trans} + qq|SELECT * FROM (SELECT DISTINCT $select{trans} FROM inventory i1 LEFT JOIN inventory i2 ON i1.trans_id = i2.trans_id LEFT JOIN parts p ON i1.parts_id = p.id @@ -531,9 +550,20 @@ sub get_warehouse_journal { WHERE $where_clause i1.qty > 0 AND i1.trans_id IN ( SELECT i.trans_id FROM inventory i GROUP BY i.trans_id HAVING COUNT(i.trans_id) = 1 ) GROUP BY $group_clause - ORDER BY r_${sort_spec}|; + ORDER BY r_${sort_spec}) AS lines WHERE r_qty>0|; + + my @all_vars = (@filter_vars,@filter_vars,@filter_vars); - my $sth = prepare_execute_query($form, $dbh, $query, @filter_vars, @filter_vars, @filter_vars); + if ($filter{limit}) { + $query .= " LIMIT ?"; + push @all_vars,$filter{limit}; + } + if ($filter{offset}) { + $query .= " OFFSET ?"; + push @all_vars, $filter{offset}; + } + + my $sth = prepare_execute_query($form, $dbh, $query, @all_vars); my ($h_oe_id, $q_oe_id); if ($form->{l_oe_id}) { @@ -702,6 +732,11 @@ sub get_warehouse_report { push @filter_vars, trim($form->{bestbefore}); } + if ($filter{classification_id}) { + push @filter_ary, "p.classification_id = ?"; + push @filter_vars, $filter{classification_id}; + } + if ($filter{ean}) { push @filter_ary, "p.ean ILIKE ?"; push @filter_vars, like($filter{ean}); @@ -755,8 +790,7 @@ sub get_warehouse_report { "partnumber" => "p.partnumber", "partdescription" => "p.description", "classification_id" => "p.classification_id", - "assembly" => "p.assembly", - "inventory_accno_id" => "p.inventory_accno_id", + "part_type" => "p.part_type", "bindescription" => "b.description", "binid" => "b.id", "chargenumber" => "i.chargenumber", @@ -766,10 +800,11 @@ sub get_warehouse_report { "warehousedescription" => "w.description", "partunit" => "p.unit", "stock_value" => "p.lastcost / COALESCE(pfac.factor, 1)", + "purchase_price" => "p.lastcost", ); $form->{l_classification_id} = 'Y'; - $form->{l_assembly} = 'Y'; - $form->{l_inventory_accno_id} = 'Y'; + $form->{l_part_type} = 'Y'; + my $select_clause = join ', ', map { +/^l_/; "$select_tokens{$'} AS $'" } ( grep( { !/qty/ and /^l_/ and $form->{$_} eq 'Y' } keys %$form), qw(l_parts_id l_qty l_partunit) ); @@ -787,7 +822,7 @@ sub get_warehouse_report { qw(l_parts_id l_qty l_partunit) ); my $query = - qq|SELECT $select_clause + qq|SELECT * FROM ( SELECT $select_clause FROM inventory i LEFT JOIN parts p ON i.parts_id = p.id LEFT JOIN bin b ON i.bin_id = b.id @@ -795,9 +830,17 @@ sub get_warehouse_report { $joins WHERE $where_clause GROUP BY $group_clause - ORDER BY $sort_spec|; + ORDER BY $sort_spec ) AS lines WHERE qty<>0|; - my $sth = prepare_execute_query($form, $dbh, $query, @filter_vars); + if ($filter{limit}) { + $query .= " LIMIT ?"; + push @filter_vars,$filter{limit}; + } + if ($filter{offset}) { + $query .= " OFFSET ?"; + push @filter_vars, $filter{offset}; + } + my $sth = prepare_execute_query($form, $dbh, $query, @filter_vars ); my (%non_empty_bins, @all_fields, @contents);