[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[5435] trunk
- Subject: SF.net SVN: ledger-smb:[5435] trunk
- From: ..hidden..
- Date: Sat, 29 Dec 2012 09:26:11 +0000
Revision: 5435
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=5435&view=rev
Author: einhverfr
Date: 2012-12-29 09:26:11 +0000 (Sat, 29 Dec 2012)
Log Message:
-----------
Consolidated goods and services search
Modified Paths:
--------------
trunk/LedgerSMB/IC.pm
trunk/bin/ic.pl
trunk/sql/Pg-database.sql
trunk/sql/modules/LOADORDER
trunk/sql/modules/menu_rebuild.sql
Added Paths:
-----------
trunk/LedgerSMB/Report/Inventory/Search.pm
trunk/UI/Reports/filters/search_goods.html
trunk/goods.pl
trunk/sql/modules/Goods.sql
Modified: trunk/LedgerSMB/IC.pm
===================================================================
--- trunk/LedgerSMB/IC.pm 2012-12-27 15:15:05 UTC (rev 5434)
+++ trunk/LedgerSMB/IC.pm 2012-12-29 09:26:11 UTC (rev 5435)
@@ -964,744 +964,6 @@
}
-sub all_parts {
- my ( $self, $myconfig, $form ) = @_;
-
- $dbh = $form->{dbh};
- my $where = '1 = 1';
- my $null;
- my $var;
- my $ref;
-
- for (qw(partnumber drawing microfiche)) {
- if ( $form->{$_} ne "" ) {
- $var = $dbh->quote( $form->like( lc $form->{$_} ) );
- $where .= " AND lower(p.$_) LIKE $var";
- }
- }
-
- # special case for description
- if ( $form->{description} ne "" ) {
- unless ( $form->{bought}
- || $form->{sold}
- || $form->{onorder}
- || $form->{ordered}
- || $form->{rfq}
- || $form->{quoted} )
- {
-
- $var = $dbh->quote( $form->like( lc $form->{description} ) );
- $where .= " AND lower(p.description) LIKE $var";
- }
- }
-
- # assembly components
- my $assemblyflds;
- if ( $form->{searchitems} eq 'component' ) {
- $assemblyflds = qq|, p1.partnumber AS assemblypartnumber,
- a.id AS assembly_id|;
- }
-
- # special case for serialnumber
- if ( $form->{l_serialnumber} ) {
- if ( $form->{serialnumber} ne "" ) {
- $var = $dbh->quote( $form->like( lc $form->{serialnumber} ) );
- $where .= " AND lower(i.serialnumber) LIKE $var";
- }
- }
-
- if ( ( $form->{warehouse} ne "" ) || $form->{l_warehouse} ) {
- $form->{l_warehouse} = 1;
- }
-
- if ( $form->{searchitems} eq 'part' ) {
- $where .= " AND p.inventory_accno_id > 0 AND p.income_accno_id > 0";
- }
- if ( $form->{searchitems} eq 'assembly' ) {
- $form->{bought} = "";
- $where .= " AND p.assembly = '1'";
- }
- if ( $form->{searchitems} eq 'service' ) {
- $where .= " AND p.assembly = '0' AND p.inventory_accno_id IS NULL";
- }
- if ( $form->{searchitems} eq 'labor' ) {
- $where .= " AND p.inventory_accno_id > 0 AND p.income_accno_id IS NULL";
- }
-
- # items which were never bought, sold or on an order
- if ( $form->{itemstatus} eq 'orphaned' ) {
- $where .= qq|
- AND p.onhand = 0
- AND p.id NOT IN (SELECT p.id FROM parts p
- JOIN invoice i
- ON (p.id = i.parts_id))
- AND p.id NOT IN (SELECT p.id FROM parts p
- JOIN assembly a
- ON (p.id = a.parts_id))
- AND p.id NOT IN (SELECT p.id FROM parts p
- JOIN orderitems o
- ON (p.id = o.parts_id))
- AND p.id NOT IN (SELECT p.id FROM parts p
- JOIN jcitems j
- ON (p.id = j.parts_id))|;
- }
-
- if ( $form->{itemstatus} eq 'obsolete' ) {
- $where .= " AND p.obsolete = '1'";
- }
- else {
- # Obsolete items should not show up on onhand, short, or active
- # reports --CT
- $where .= " AND p.obsolete = '0'";
- }
-
- if ( $form->{itemstatus} eq 'onhand' ) {
- $where .= " AND p.onhand > 0";
- }
- elsif ( $form->{itemstatus} eq 'short' ) {
- $where .= " AND p.onhand < p.rop";
- }
-
- my $makemodelflds = qq|, '', ''|;
- my $makemodeljoin;
-
- if ( ( $form->{make} ne "" )
- || $form->{l_make}
- || ( $form->{model} ne "" )
- || $form->{l_model} )
- {
- $makemodelflds = qq|, m.make, m.model|;
- $makemodeljoin = qq|LEFT JOIN makemodel m ON (m.parts_id = p.id)|;
-
- if ( $form->{make} ne "" ) {
- $var = $dbh->quote( $form->like( lc $form->{make} ) );
- $where .= " AND lower(m.make) LIKE $var";
- }
- if ( $form->{model} ne "" ) {
- $var = $dbh->quote( $form->like( lc $form->{model} ) );
- $where .= " AND lower(m.model) LIKE $var";
- }
- }
- if ( $form->{partsgroup} ne "" ) {
- ( $null, $var ) = split /--/, $form->{partsgroup};
- $where .= qq| AND p.partsgroup_id = | . $dbh->quote($var);
- }
-
- my %ordinal = (
- 'partnumber' => 2,
- 'description' => 3,
- 'bin' => 6,
- 'priceupdate' => 13,
- 'drawing' => 15,
- 'microfiche' => 16,
- 'partsgroup' => 18,
- 'make' => 21,
- 'model' => 22,
- 'assemblypartnumber' => 23
- );
-
- my @a = qw(partnumber description);
- my $sortorder = $form->sort_order( ..hidden.., \%ordinal );
-
- my $query = qq|SELECT value FROM defaults WHERE setting_key = 'curr'|;
- my ($curr) = $dbh->selectrow_array($query);
- $curr =~ s/:.*//;
- $curr = $dbh->quote($curr);
- my $flds = qq|
- p.id, p.partnumber, p.description, p.onhand, p.unit,
- p.bin, p.sellprice, p.listprice, p.lastcost, p.rop,
- p.avgcost,
- p.weight, p.priceupdate, p.image, p.drawing, p.microfiche,
- p.assembly, pg.partsgroup, $curr AS curr,
- a1.accno AS inventory, a2.accno AS income, a3.accno AS expense,
- p.notes
- $makemodelflds $assemblyflds
- |;
-
- $query = qq|
- SELECT $flds
- FROM parts p
- LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
- LEFT JOIN account a1 ON (a1.id = p.inventory_accno_id)
- LEFT JOIN account a2 ON (a2.id = p.income_accno_id)
- LEFT JOIN account a3 ON (a3.id = p.expense_accno_id)
- $makemodeljoin
- WHERE $where
- ORDER BY $sortorder|;
-
- # redo query for components report
- if ( $form->{searchitems} eq 'component' ) {
-
- $flds =~ s/p.onhand/a.qty AS onhand/;
-
- $query = qq|
- SELECT $flds
- FROM assembly a
- JOIN parts p ON (a.parts_id = p.id)
- JOIN parts p1 ON (a.id = p1.id)
- LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
- LEFT JOIN account a1 ON (a1.id = p.inventory_accno_id)
- LEFT JOIN account a2 ON (a2.id = p.income_accno_id)
- LEFT JOIN account a3 ON (a3.id = p.expense_accno_id)
- $makemodeljoin
- WHERE $where
- ORDER BY $sortorder|;
- }
-
- # rebuild query for bought and sold items
- if ( $form->{bought}
- || $form->{sold}
- || $form->{onorder}
- || $form->{ordered}
- || $form->{rfq}
- || $form->{quoted} )
- {
-
- $form->sort_order();
- @a = qw(partnumber description curr employee name
- serialnumber id);
- push @a, "invnumber" if ( $form->{bought} || $form->{sold} );
- push @a, "ordnumber" if ( $form->{onorder} || $form->{ordered} );
- push @a, "quonumber" if ( $form->{rfq} || $form->{quoted} );
-
- %ordinal = (
- 'partnumber' => 2,
- 'description' => 3,
- 'serialnumber' => 4,
- 'bin' => 7,
- 'priceupdate' => 14,
- 'partsgroup' => 19,
- 'invnumber' => 20,
- 'ordnumber' => 21,
- 'quonumber' => 22,
- 'name' => 24,
- 'employee' => 25,
- 'curr' => 26,
- 'make' => 29,
- 'model' => 30
- );
-
- $sortorder = $form->sort_order( ..hidden.., \%ordinal );
-
- my $union = "";
- $query = "";
-
- if ( $form->{bought} || $form->{sold} ) {
-
- my $invwhere = "$where";
- my $transdate =
- ( $form->{method} eq 'accrual' )
- ? "transdate"
- : "datepaid";
-
- $invwhere .= " AND i.assemblyitem = '0'";
- $invwhere .=
- " AND a.$transdate >= " . $dbh->quote( $form->{transdatefrom} )
- if $form->{transdatefrom};
- $invwhere .=
- " AND a.$transdate <= " . $dbh->quote( $form->{transdateto} )
- if $form->{transdateto};
-
- if ( $form->{description} ne "" ) {
- $var = $dbh->quote( $form->like( lc $form->{description} ) );
- $invwhere .= " AND lower(i.description) LIKE $var";
- }
-
- if ( $form->{open} || $form->{closed} ) {
- if ( $form->{open} && $form->{closed} ) {
- if ( $form->{method} eq 'cash' ) {
- $invwhere .= " AND a.amount = a.paid";
- }
- }
- else {
- if ( $form->{open} ) {
- if ( $form->{method} eq 'cash' ) {
- $invwhere .= " AND a.id = 0";
- }
- else {
- $invwhere .= " AND NOT a.amount = a.paid";
- }
- }
- else {
- $invwhere .= " AND a.amount = a.paid";
- }
- }
- }
- else {
- $invwhere .= " AND a.id = 0";
- }
-
- my $flds = qq|
- p.id, p.partnumber, i.description,
- i.serialnumber, i.qty AS onhand, i.unit, p.bin,
- i.sellprice, p.listprice, p.lastcost, p.rop,
- p.weight, p.avgcost, p.priceupdate, p.image,
- p.drawing, p.microfiche, p.assembly,
- pg.partsgroup, a.invnumber, a.ordnumber,
- a.quonumber, i.trans_id, ct.employee_id,
- e.first_name \|\| ' ' \|\| e.last_name AS employee, a.curr, a.till, p.notes
- $makemodelfld|;
-
- if ( $form->{bought} ) {
- my $rflds = $flds;
- $rflds =~ s/i.qty AS onhand/i.qty * -1 AS onhand/;
-
- $query = qq|
- SELECT $rflds, 'ir' AS module,
- '' AS type,
- (SELECT sell
- FROM exchangerate ex
- WHERE ex.curr = a.curr
- AND ex.transdate
- = a.$transdate)
- AS exchangerate, i.discount
- FROM invoice i
- JOIN parts p
- ON (p.id = i.parts_id)
- JOIN ap a ON (a.id = i.trans_id)
- JOIN entity_credit_account ct
- ON (a.entity_credit_account
- = ct.id)
- LEFT JOIN partsgroup pg
- ON (p.partsgroup_id = pg.id)
- LEFT JOIN employees e
- ON (a.person_id = e.entity_id)
- $makemodeljoin
- WHERE $invwhere|;
- $union = "
- UNION ALL";
- }
-
- if ( $form->{sold} ) {
- $query .= qq|
- $union
- SELECT $flds, 'is' AS module,
- '' AS type,
- (SELECT buy
- FROM exchangerate ex
- WHERE ex.curr = a.curr
- AND ex.transdate
- = a.$transdate)
- AS exchangerate, i.discount
- FROM invoice i
- JOIN parts p
- ON (p.id = i.parts_id)
- JOIN ar a ON (a.id = i.trans_id)
- JOIN entity_credit_account ct
- ON a.entity_credit_account
- = ct.id
- LEFT JOIN partsgroup pg
- ON (p.partsgroup_id = pg.id)
- LEFT JOIN employees e
- ON (a.person_id = e.entity_id)
- $makemodeljoin
- WHERE $invwhere|;
- $union = "
- UNION ALL";
- }
- }
-
- if ( $form->{onorder} || $form->{ordered} ) {
- my $ordwhere = "$where
- AND a.quotation = '0'";
- $ordwhere .=
- " AND a.transdate >= " . $dbh->quote( $form->{transdatefrom} )
- if $form->{transdatefrom};
- $ordwhere .=
- " AND a.transdate <= " . $dbh->quote( $form->{transdateto} )
- if $form->{transdateto};
-
- if ( $form->{description} ne "" ) {
- $var = $dbh->quote( $form->like( lc $form->{description} ) );
- $ordwhere .= " AND lower(i.description) LIKE $var";
- }
-
- if ( $form->{open} || $form->{closed} ) {
- unless ( $form->{open} && $form->{closed} ) {
- $ordwhere .= " AND a.closed = '0'"
- if $form->{open};
- $ordwhere .= " AND a.closed = '1'"
- if $form->{closed};
- }
- }
- else {
- $ordwhere .= " AND a.id = 0";
- }
-
- $flds = qq|
- p.id, p.partnumber, i.description,
- i.serialnumber, i.qty AS onhand, i.unit, p.bin,
- i.sellprice, p.listprice, p.lastcost, p.rop,
- p.weight, p.avgcost, p.priceupdate, p.image,
- p.drawing, p.microfiche, p.assembly,
- pg.partsgroup, '' AS invnumber, a.ordnumber,
- a.quonumber, i.trans_id, ct.employee_id,
- e.first_name \|\| ' ' \|\| e.last_name AS employee, a.curr, '0' AS till,
- p.notes
- $makemodelfld|;
-
- if ( $form->{ordered} ) {
- $query .= qq|
- $union
- SELECT $flds, 'oe' AS module,
- 'sales_order' AS type,
- (SELECT buy
- FROM exchangerate ex
- WHERE ex.curr = a.curr
- AND ex.transdate
- = a.transdate)
- AS exchangerate, i.discount
- FROM orderitems i
- JOIN parts p ON (i.parts_id = p.id)
- JOIN oe a ON (i.trans_id = a.id)
- JOIN entity_credit_account ct
- ON a.entity_credit_account
- = ct.id
- LEFT JOIN partsgroup pg
- ON (p.partsgroup_id = pg.id)
- LEFT JOIN employees e
- ON (a.person_id = e.entity_id)
- $makemodeljoin
- WHERE $ordwhere
- AND a.entity_credit_account
- IS NOT NULL|;
- $union = "
- UNION ALL";
- }
-
- if ( $form->{onorder} ) {
- $flds = qq|
- p.id, p.partnumber, i.description,
- i.serialnumber, i.qty AS onhand, i.unit,
- p.bin, i.sellprice, p.listprice,
- p.lastcost, p.rop, p.weight, p.avgcost,
- p.priceupdate, p.image, p.drawing,
- p.microfiche, p.assembly,
- pg.partsgroup, '' AS invnumber,
- a.ordnumber, a.quonumber,
- i.trans_id, ct.employee_id,
- e.first_name \|\| ' ' \|\| e.last_name AS employee,
- a.curr, '0' AS till, p.notes
- $makemodelfld|;
-
- $query .= qq|
- $union
- SELECT $flds, 'oe' AS module,
- 'purchase_order' AS type,
- (SELECT sell
- FROM exchangerate ex
- WHERE ex.curr = a.curr
- AND ex.transdate
- = a.transdate)
- AS exchangerate, i.discount
- FROM orderitems i
- JOIN parts p ON (i.parts_id = p.id)
- JOIN oe a ON (i.trans_id = a.id)
- JOIN entity_credit_account ct
- ON (a.entity_credit_account
- = ct.id)
- LEFT JOIN partsgroup pg
- ON (p.partsgroup_id = pg.id)
- LEFT JOIN employees e
- ON (a.person_id = e.entity_id)
- $makemodeljoin
- WHERE $ordwhere
- AND a.entity_credit_account
- > 0|;
- }
-
- }
-
- if ( $form->{rfq} || $form->{quoted} ) {
- my $quowhere = "$where
- AND a.quotation = '1'";
- $quowhere .=
- " AND a.transdate >= " . $dbh->quote( $form->{transdatefrom} )
- if $form->{transdatefrom};
- $quowhere .=
- " AND a.transdate <= " . $dbh->quote( $form->{transdateto} )
- if $form->{transdateto};
-
- if ( $form->{description} ne "" ) {
- $var = $dbh->quote( $form->like( lc $form->{description} ) );
- $quowhere .= " AND lower(i.description) LIKE $var";
- }
-
- if ( $form->{open} || $form->{closed} ) {
- unless ( $form->{open} && $form->{closed} ) {
- $ordwhere .= " AND a.closed = '0'"
- if $form->{open};
- $ordwhere .= " AND a.closed = '1'"
- if $form->{closed};
- }
- }
- else {
- $ordwhere .= " AND a.id = 0";
- }
-
- $flds = qq|
- p.id, p.partnumber, i.description,
- i.serialnumber, i.qty AS onhand, i.unit, p.bin,
- i.sellprice, p.listprice, p.lastcost, p.rop,
- p.weight, p.avgcost, p.priceupdate, p.image,
- p.drawing, p.microfiche, p.assembly,
- pg.partsgroup, '' AS invnumber, a.ordnumber,
- a.quonumber, i.trans_id, ct.employee_id,
- e.first_name \|\| ' ' \|\| e.last_name AS employee, a.curr, '0' AS till, p.notes
- $makemodelfld|;
-
- if ( $form->{quoted} ) {
- $query .= qq|
- $union
- SELECT $flds, 'oe' AS module,
- 'sales_quotation' AS type,
- (SELECT buy
- FROM exchangerate ex
- WHERE ex.curr = a.curr
- AND ex.transdate
- = a.transdate)
- AS exchangerate,
- i.discount
- FROM orderitems i
- JOIN parts p ON (i.parts_id = p.id)
- JOIN oe a ON (i.trans_id = a.id)
- JOIN entity_credit_account ct
- ON a.entity_credit_account
- = ct.id
- LEFT JOIN partsgroup pg
- ON (p.partsgroup_id = pg.id)
- LEFT JOIN employees e
- ON (a.person_id = e.entity_id)
- $makemodeljoin
- WHERE $quowhere
- AND a.entity_credit_account
- IS NOT NULL|;
- $union = "
- UNION ALL";
- }
-
- if ( $form->{rfq} ) {
- $flds = qq|
- p.id, p.partnumber, i.description,
- i.serialnumber, i.qty AS onhand,
- i.unit, p.bin, i.sellprice, p.listprice,
- p.lastcost, p.rop, p.weight, p.avgcost,
- p.priceupdate, p.image, p.drawing,
- p.microfiche, p.assembly,
- pg.partsgroup, '' AS invnumber,
- a.ordnumber, a.quonumber,
- i.trans_id, ct.employee_id,
- e.first_name \|\| ' ' \|\| e.last_name AS employee,
- a.curr, '0' AS till, p.notes
- $makemodelfld|;
-
- $query .= qq|
- $union
- SELECT $flds, 'oe' AS module,
- 'request_quotation' AS type,
- (SELECT sell
- FROM exchangerate ex
- WHERE ex.curr = a.curr
- AND ex.transdate
- = a.transdate)
- AS exchangerate, i.discount
- FROM orderitems i
- JOIN parts p ON (i.parts_id = p.id)
- JOIN oe a ON (i.trans_id = a.id)
- JOIN entity_credit_account ct
- ON (a.entity_credit_account
- = ct.id)
- LEFT JOIN partsgroup pg
- ON (p.partsgroup_id = pg.id)
- LEFT JOIN employees e
- ON (a.person_id = e.entity_id)
- $makemodeljoin
- WHERE $quowhere
- AND a.entity_credit_account
- > 0|;
- }
-
- }
-
- $query .= qq|
- ORDER BY $sortorder|;
-
- }
- $logger->debug("query = \n$query");
-
- my $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- $query = qq|
- SELECT c.accno
- FROM chart c
- JOIN partstax pt ON (pt.chart_id = c.id)
- WHERE pt.parts_id = ?
- ORDER BY accno|;
- my $pth = $dbh->prepare($query) || $form->dberror($query);
-
- while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
- $pth->execute( $ref->{id} );
- while ( ($accno) = $pth->fetchrow_array ) {
- $ref->{tax} .= "$accno ";
- }
- $pth->finish;
- $logger->debug("adding part $ref->{id}");
-
- push @{ $form->{parts} }, $ref;
- }
- $sth->finish;
-
- @a = ();
-
- # include individual items for assembly
- if ( ( $form->{searchitems} eq 'assembly' ) && $form->{individual} ) {
-
- if ( $form->{sold} || $form->{ordered} || $form->{quoted} ) {
- $flds = qq|
- p.id, p.partnumber, p.description,
- p.onhand AS perassembly, p.unit, p.bin,
- p.sellprice, p.listprice, p.lastcost, p.rop,
- p.avgcost, p.weight, p.priceupdate, p.image,
- p.drawing, p.microfiche, p.assembly,
- pg.partsgroup, p.notes
- $makemodelflds $assemblyflds |;
- }
- else {
-
- # replace p.onhand with a.qty AS onhand
- $flds =~ s/p\.onhand/a.qty AS perassembly/;
- }
-
- for ( @{ $form->{parts} } ) {
- push @a, $_;
- $_->{perassembly} = 1;
- $flds =~
- s/p\.onhand*AS perassembly/p\.onhand, a\.qty AS perassembly/;
- push @a,
- &include_assembly( $dbh, $myconfig, $form, $_->{id}, $flds,
- $makemodeljoin );
- push @a, { id => $_->{id}, assemblyitem => 1 };
- }
-
- # copy assemblies to $form->{parts}
- @{ $form->{parts} } = @a;
-
- }
-
- @a = ();
- if ( ( $form->{warehouse} ne "" ) || $form->{l_warehouse} ) {
-
- if ( $form->{warehouse} ne "" ) {
- my ( $desc, $var ) = split /--/, $form->{warehouse};
- $var = $dbh->quote($var);
- $desc = $dbh->quote($desc);
- $query = qq|
- SELECT SUM(qty) AS onhand,
- $desc AS description
- FROM inventory
- WHERE warehouse_id = $var
- AND parts_id = ?|;
- }
- else {
- $query = qq|
- SELECT SUM(i.qty) AS onhand,
- w.description AS warehouse
- FROM inventory i
- JOIN warehouse w ON (w.id = i.warehouse_id)
- WHERE i.parts_id = ?
- GROUP BY w.description|;
- }
-
- $sth = $dbh->prepare($query) || $form->dberror($query);
-
- for ( @{ $form->{parts} } ) {
-
- $sth->execute( $_->{id} ) || $form->dberror($query);
-
- if ( $form->{warehouse} ne "" ) {
-
- $ref = $sth->fetchrow_hashref(NAME_lc);
- if ( $ref->{onhand} != 0 ) {
- $_->{onhand} = $ref->{onhand};
- push @a, $_;
- }
-
- }
- else {
-
- push @a, $_;
-
- while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
- if ( $ref->{onhand} > 0 ) {
- push @a, $ref;
- }
- }
- }
-
- $sth->finish;
- }
-
- @{ $form->{parts} } = @a;
-
- }
-
- $dbh->commit;
-
-}
-
-sub include_assembly {
- my ( $dbh, $myconfig, $form, $id, $flds, $makemodeljoin ) = @_;
-
- $form->{stagger}++;
- if ( $form->{stagger} > $form->{pncol} ) {
- $form->{pncol} = $form->{stagger};
- }
-
- $form->{$id} = 1;
-
- my @a = qw(partnumber description bin);
- @a = grep !/$form->{sort}/, @a;
- $sortorder = "$form->{sort} $form->{direction}, " . join ',', @a;
-
- @a = ();
- my $query = qq|
- SELECT $flds
- FROM parts p
- JOIN assembly a ON (a.parts_id = p.id)
- LEFT JOIN partsgroup pg ON (pg.id = p.id)
- LEFT JOIN account a1 ON (a1.id = p.inventory_accno_id)
- LEFT JOIN account a2 ON (a2.id = p.income_accno_id)
- LEFT JOIN account a3 ON (a3.id = p.expense_accno_id)
- $makemodeljoin
- WHERE a.id = ?
- ORDER BY $sortorder|;
- my $sth = $dbh->prepare($query);
- $sth->execute($id) || $form->dberror($query);
-
- while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
- $ref->{assemblyitem} = 1;
- $ref->{stagger} = $form->{stagger};
-
- push @a, $ref;
- if ( $ref->{assembly} && !$form->{ $ref->{id} } ) {
- push @a,
- &include_assembly( $dbh, $myconfig, $form, $ref->{id}, $flds,
- $makemodeljoin );
- if ( $form->{stagger} > $form->{pncol} ) {
- $form->{pncol} = $form->{stagger};
- }
- }
- }
- $sth->finish;
-
- $form->{$id} = 0;
- $form->{stagger}--;
-
- @a;
-
-}
-
sub requirements {
my ( $self, $myconfig, $form ) = @_;
my $dbh = $form->{dbh};
Added: trunk/LedgerSMB/Report/Inventory/Search.pm
===================================================================
--- trunk/LedgerSMB/Report/Inventory/Search.pm (rev 0)
+++ trunk/LedgerSMB/Report/Inventory/Search.pm 2012-12-29 09:26:11 UTC (rev 5435)
@@ -0,0 +1,355 @@
+=head1 NAME
+
+LedgerSMB::Report::Inventory::Search - Search for Goods and Services in
+LedgerSMB
+
+=head1 SYNPOSIS
+
+ my $report = LedgerSMB::Report::Inventory::Search->new(%$request);
+ $report->render($request);
+
+=cut
+
+package LedgerSMB::Report::Inventory::Search;
+use Moose;
+extends 'LedgerSMB::Report';
+with 'LedgerSMB::Report::Dates';
+
+=head1 DESCRIPTION
+
+This is the main inventory item search for goods and services in LedgerSMB
+starting with version 1.4. Compared to LedgerSMB 1.3 this has no summary
+and details support and no searching for open vs closed invoices. The eqivalent
+of a summary report is found in the inventory activities report instead.
+
+The open/closed detection was omitted for performance reasons, and a search for
+unused items may take a while on larger databases.
+
+=head1 CRITERIA PROPERTIES
+
+In addition to standard dates, the following criteria are supported:
+
+=over
+
+=item partnumber string
+
+Prefix search on partnumber.
+
+=cut
+
+has partnumber => (is => 'ro', isa => 'Str', required => 0);
+
+=item description string
+
+Full text search on description of part
+
+=cut
+
+has description => (is => 'ro', isa => 'Str', required => 0);
+
+=item partsgroup_id int
+
+Limit search to partsgroup specified
+
+=cut
+
+has partsgroup_id => (is => 'ro', isa => 'Int', required => 0);
+
+=item serial_number text
+
+This is a serial number of the part, for a prefix search
+
+=cut
+
+has serial_number => (is => 'ro', isa => 'Str', required => 0);
+
+=item make
+
+Prefix search for parts with a make (and model) specified
+
+=cut
+
+has make => (is => 'ro', isa => 'Str', required => 0);
+
+=item model
+
+Prefix search on the model of the part
+
+=cut
+
+has model => (is => 'ro', isa => 'Str', required => 0);
+
+=item drawing
+
+Prefix search for drawing field
+
+=cut
+
+has drawing => (is => 'ro', isa => 'Str', required => 0);
+
+=item microfiche
+
+Prefix search for microfiche field
+
+=cut
+
+has microfiche => (is => 'ro', isa => 'Str', required => 0);
+
+=item status
+
+An enumerated string, with the following significance
+
+=over
+
+=item active
+
+Show non-obsolete parts
+
+=item obsolete
+
+Show obsolete parts
+
+=item short
+
+Show parts below their re-order point (ROP)
+
+=item unused
+
+Show parts with no invoices or orders attached (previously orphaned)
+
+=back
+
+=cut
+
+has status => (is => 'ro', isa => 'Str', required => 0);
+
+=item sales_invoices bool
+
+If true, show parts attached to sales invoices in the specified period
+
+=cut
+
+has sales_invoices => (is => 'ro', isa => 'Bool', required => 0);
+
+=item purchase_invoices bool
+
+If true, show parts attached to purchase/vendor invoices in the specified
+period.
+
+=cut
+
+has purchase_invpices => (is => 'ro', isa => 'Bool', required => 0);
+
+=item sales_orders
+
+If true, search parts in sales orders in the specified period.
+
+=cut
+
+has sales_orders => (is => 'ro', isa => 'Bool', required => 0);
+
+=item purchase_orders
+
+If true, search purchase orders in the specified period.
+
+=cut
+
+has purchase_orders => (is => 'ro', isa => 'Bool', required => 0);
+
+=item quotations
+
+If true, search quotations in the specified period
+
+=cut
+
+has quotations => (is => 'ro', isa => 'Bool', required => 0);
+
+=item rfqs
+
+If true, search Requests for Quotations for the specified period
+
+=cut
+
+has rfqs => (is => 'ro', isa => 'Bool', required => 0);
+
+=back
+
+=head1 INTERNALS
+
+=head2 columns
+
+=cut
+
+sub columns {
+ return [
+ {col_id => 'id',
+ type => 'href',
+ href_base => 'ic.pl?action=edit&id=',
+ name => LedgerSMB::Report::text('ID'),},
+
+ {col_id => 'partnumber',
+ type => 'href',
+ href_base => 'ic.pl?action=edit&id=',
+ name => LedgerSMB::Report::text('Part Number'),},
+
+ {col_id => 'description',
+ type => 'text',
+ name => LedgerSMB::Report::text('Description'),},
+
+ {col_id => 'unit',
+ type => 'text',
+ name => LedgerSMB::Report::text('Unit'),},
+
+ {col_id => 'onhand',
+ type => 'text',
+ name => LedgerSMB::Report::text('On Hand'),},
+
+ {col_id => 'rop',
+ type => 'text',
+ name => LedgerSMB::Report::text('ROP'),},
+
+ {col_id => 'bin',
+ type => 'text',
+ name => LedgerSMB::Report::text('Bin'),},
+
+ {col_id => 'weight',
+ type => 'text',
+ name => LedgerSMB::Report::text('Weight'),},
+
+ {col_id => 'listprice',
+ type => 'text',
+ name => LedgerSMB::Report::text('List Price'),},
+
+ {col_id => 'sellprice',
+ type => 'text',
+ name => LedgerSMB::Report::text('Sell Price'),},
+
+ {col_id => 'lastcost',
+ type => 'text',
+ name => LedgerSMB::Report::text('Last Cost'),},
+
+ {col_id => 'avgcost',
+ type => 'text',
+ name => LedgerSMB::Report::text('Avg. Cost'),},
+
+ {col_id => 'markup',
+ type => 'text',
+ name => LedgerSMB::Report::text('Markup'),},
+
+ {col_id => 'price_updated',
+ type => 'text',
+ name => LedgerSMB::Report::text('Price Updated'),},
+
+ {col_id => 'make',
+ type => 'text',
+ name => LedgerSMB::Report::text('Make'),},
+
+ {col_id => 'model',
+ type => 'text',
+ name => LedgerSMB::Report::text('Model'),},
+
+ {col_id => 'image',
+ type => 'href',
+ name => LedgerSMB::Report::text('Image'),},
+
+ {col_id => 'drawing',
+ type => 'href',
+ name => LedgerSMB::Report::text('Drawing'),},
+
+ {col_id => 'microfiche',
+ type => 'text',
+ name => LedgerSMB::Report::text('Microfiche'),},
+
+ {col_id => 'notes',
+ type => 'text',
+ name => LedgerSMB::Report::text('Notes'),},
+
+ {col_id => 'partsgroup',
+ type => 'text',
+ name => LedgerSMB::Report::text('Partsgroup'),},
+
+ {col_id => 'invnumber',
+ type => 'href',
+ name => LedgerSMB::Report::text('Invoice'),},
+
+ {col_id => 'ordnumber',
+ type => 'href',
+ name => LedgerSMB::Report::text('Order'),},
+
+ {col_id => 'quonumber',
+ type => 'href',
+ name => LedgerSMB::Report::text('Quotation'),},
+
+ {col_id => 'curr',
+ type => 'text',
+ name => LedgerSMB::Report::text('Currency'),},
+
+ {col_id => 'qty',
+ type => 'text',
+ name => LedgerSMB::Report::text('Qty'),},
+
+ {col_id => 'linetotal',
+ type => 'text',
+ name => LedgerSMB::Report::text('Total'),},
+
+ {col_id => 'serialnumber',
+ type => 'text',
+ name => LedgerSMB::Report::text('Serial Number'),},
+
+ ];
+}
+
+=head2 header_lines
+
+None yet
+
+=cut
+
+sub header_lines {
+ return [];
+};
+
+=head2 name
+
+Goods and Services
+
+=cut
+
+sub name {
+ return LedgerSMB::Report::text('Goods and Services');
+}
+
+=head1 METHODS
+
+=head2 run_report
+
+=cut
+
+sub run_report {
+ my ($self) = @_;
+ my @rows = $self->exec_method({funcname => 'goods__search'});
+ for my $r (@rows){
+ $r->{row_id} = $r->{id};
+
+ for my $field (qw(image drawing microfiche)){
+ $r->{"href_suffix_$field"} = $r->{field};
+ }
+
+ for my $field (qw(invnumber quonumber ordnumber)){
+ $r->{"href_suffix_$field"} = "$r->{module}.pl?action=edit&id=";
+ $r->{"href_suffix_$field"} .= $r->{trans_id} if $r->{trans_id};
+ $r->{"href_suffix_$field"} .= $r->{ord_id} if $r->{ord_id};
+ }
+ }
+ $self->rows(..hidden..);
+}
+
+=head1 COPYRIGHT
+
+COPYRIGHT (C) 2012 The LedgerSMB Core Team. This file may be re-used under the
+terms of the LedgerSMB General Public License version 2 or at your option any
+later version. Please see enclosed LICENSE file for details.
+
+=cut
+
+__PACKAGE__->meta->make_immutable;
Added: trunk/UI/Reports/filters/search_goods.html
===================================================================
--- trunk/UI/Reports/filters/search_goods.html (rev 0)
+++ trunk/UI/Reports/filters/search_goods.html 2012-12-29 09:26:11 UTC (rev 5435)
@@ -0,0 +1,384 @@
+<?lsmb INCLUDE "ui-header.html";
+ PROCESS "report_base.html";
+ PROCESS "elements.html" ?>
+<body>
+<div class="listtop"><?lsmb text('Search Goods and Services') ?></div>
+<form action="goods.pl" method="GET">
+<table>
+<tr>
+ <th><?lsmb text('Part Number') ?></th>
+ <td><?lsmb PROCESS input element_data = {
+ name = 'partnumber'
+ class = 'control_code'
+ type = 'text'
+ value = partnumber
+ size = 16
+ } ?></td>
+</tr>
+<tr>
+ <th><?lsmb text('Description') ?></th>
+ <td colspan="3"><?lsmb PROCESS input element_data = {
+ name = 'description'
+ class = 'description'
+ type = 'text'
+ value = description
+ size = 32
+ } ?></td>
+</tr>
+<tr>
+ <th><?lsmb text('Part Group') ?></th>
+ <td><?lsmb PROCESS select element_data = {
+ name = 'partsgroup_id'
+ class = 'partsgroup'
+default_values = [partsgroup_id]
+ text_attr = 'partsgroup'
+ value_attr = 'id'
+ } ?></td>
+ <th><?lsmb text('Serial Number') ?></th>
+ <td><?lsmb PROCESS input element_data = {
+ name = 'serialnumber'
+ class = 'control_code'
+ type = 'text'
+ value = serialnumber
+ size = 16
+ } ?></td>
+</tr>
+<tr>
+ <th><?lsmb text('Make') ?></th>
+ <td><?lsmb PROCESS input element_data = {
+ name = 'make'
+ class = 'control_code'
+ type = 'text'
+ value = make
+ size = 16
+ } ?></td>
+ <th><?lsmb text('Model') ?></th>
+ <td><?lsmb PROCESS input element_data = {
+ name = 'model'
+ class = 'control_code'
+ type = 'text'
+ value = model
+ size = 16
+ } ?></td>
+</tr>
+<tr>
+ <th><?lsmb text('Drawing') ?></th>
+ <td><?lsmb PROCESS input element_data = {
+ name = 'drawing'
+ class = 'control_code'
+ type = 'text'
+ value = make
+ size = 16
+ } ?></td>
+ <th><?lsmb text('Microfiche') ?></th>
+ <td><?lsmb PROCESS input element_data = {
+ name = 'microfiche'
+ class = 'control_code'
+ type = 'text'
+ value = model
+ size = 16
+ } ?></td>
+</tr>
+<tr><th> </th>
+ <td colspan="3"><?lsmb
+ old_label_pos = label_pos;
+ label_pos = -1;
+ PROCESS input element_data = {
+ name = "status"
+ type = 'radio'
+ checked = 'CHECKED'
+ value = 'active'
+ label = text('Active')
+ };
+ PROCESS input element_data = {
+ name = "status"
+ type = 'radio'
+ value = 'onhand'
+ label = text('On Hand') #'
+ };
+ PROCESS input element_data = {
+ name = "status"
+ type = 'radio'
+ value = 'short'
+ label = text('Short')
+ };
+ PROCESS input element_data = {
+ name = "status"
+ type = 'radio'
+ value = 'obsolete'
+ label = text('Obsolete')
+ };
+ PROCESS input element_data = {
+ name = "status"
+ type = 'radio'
+ value = 'unused'
+ label = text('Unused')
+ };
+ label_pos = old_label_pos;
+ ?></td>
+</tr>
+<tr><th> </th><td colspan="3"><hr /></td></tr>
+<tr>
+ <th> </th>
+ <th class="centered" colspan="3"><?lsmb text('Items Found In') ?></td>
+</tr>
+<?lsmb PROCESS date_row ?>
+<tr><th rowspan="2"> </th>
+ <td><?lsmb label_pos = 1;
+ PROCESS input element_data = {
+ type = "checkbox"
+ name = "sales_invoices"
+ value = 1
+ label = text('AR Invoices') #'
+ } ?></td>
+ <td><?lsmb
+ PROCESS input element_data = {
+ type = "checkbox"
+ name = "sales_orders"
+ value = 1
+ label = text('Sales Orders') #'
+ } ?></td>
+ <td><?lsmb
+ PROCESS input element_data = {
+ type = "checkbox"
+ name = "quotations"
+ value = 1
+ label = text('Quotations')
+ } ?></td>
+</tr>
+<tr>
+ <td><?lsmb
+ PROCESS input element_data = {
+ type = "checkbox"
+ name = "purchase_invoices"
+ value = 1
+ label = text('AP Invoices') #'
+ } ?></td>
+ <td><?lsmb
+ PROCESS input element_data = {
+ type = "checkbox"
+ name = "purchase_orders"
+ value = 1
+ label = text('Purchase Orders') #'
+ } ?></td>
+ <td><?lsmb
+ PROCESS input element_data = {
+ type = "checkbox"
+ name = "rfqs"
+ value = 1
+ label = text('RFQs')
+ } ?></td>
+</tr>
+<tr><th> </th><td colspan="3"><hr /></td></tr>
+<tr>
+ <th rowspan="2"> </th>
+ <th class="centered" colspan="3"><?lsmb text('Include in Report') ?></td>
+</tr>
+<tr><td colspan="4">
+ <table>
+ <tr><td>
+ <?lsmb PROCESS input element_data = {
+ name = 'col_running_number'
+ type = 'checkbox'
+ value = '1'
+ label = text('No.')
+ } ?></td>
+ <td>
+ <?lsmb PROCESS input element_data = {
+ name = 'col_partnumber'
+ type = 'checkbox'
+ value = '1'
+ checked = 'CHECKED'
+ label = text('Part Number') #'
+ } ?></td>
+ <td>
+ <?lsmb PROCESS input element_data = {
+ name = 'col_description'
+ type = 'checkbox'
+ value = '1'
+ checked = 'CHECKED'
+ label = text('Description')
+ } ?></td>
+ <td>
+ <?lsmb PROCESS input element_data = {
+ name = 'col_qty'
+ type = 'checkbox'
+ value = '1'
+ checked = 'CHECKED'
+ label = text('Qty')
+ } ?></td>
+ <td>
+ <?lsmb PROCESS input element_data = {
+ name = 'col_unit'
+ type = 'checkbox'
+ value = '1'
+ checked = 'CHECKED'
+ label = text('Unit')
+ } ?></td>
+ </tr>
+ <tr><td>
+ <?lsmb PROCESS input element_data = {
+ name = 'col_priceupdate'
+ type = 'checkbox'
+ value = '1'
+ label = text('Price Updated') #'
+ } ?></td>
+ <td>
+ <?lsmb PROCESS input element_data = {
+ name = 'col_partgroup'
+ type = 'checkbox'
+ value = '1'
+ label = text('Part Group') #'
+ } ?></td>
+ <td>
+ <?lsmb PROCESS input element_data = {
+ name = 'col_listprice'
+ type = 'checkbox'
+ value = '1'
+ label = text('List Price') #'
+ } ?></td>
+ <td>
+ <?lsmb PROCESS input element_data = {
+ name = 'col_sellprice'
+ type = 'checkbox'
+ value = '1'
+ checked = 'CHECKED'
+ label = text('Sell Price') #'
+ } ?></td>
+ <td>
+ <?lsmb PROCESS input element_data = {
+ name = 'col_lastcost'
+ type = 'checkbox'
+ value = '1'
+ checked = 'CHECKED'
+ label = text('Last Cost') #'
+ } ?></td>
+ </tr>
+ <tr><td>
+ <?lsmb PROCESS input element_data = {
+ name = 'col_avgcost'
+ type = 'checkbox'
+ value = '1'
+ label = text('Average Cost') #'
+ } ?></td>
+ <td>
+ <?lsmb PROCESS input element_data = {
+ name = 'col_linetotal'
+ type = 'checkbox'
+ value = '1'
+ label = text('Line Total') #'
+ } ?></td>
+ <td>
+ <?lsmb PROCESS input element_data = {
+ name = 'col_markup'
+ type = 'checkbox'
+ value = '1'
+ label = text('Markup')
+ } ?></td>
+ <td>
+ <?lsmb PROCESS input element_data = {
+ name = 'col_bin'
+ type = 'checkbox'
+ value = '1'
+ label = text('Bin')
+ } ?></td>
+ <td>
+ <?lsmb PROCESS input element_data = {
+ name = 'col_rop'
+ type = 'checkbox'
+ value = '1'
+ label = text('ROP')
+ } ?></td>
+ </tr>
+ <tr><td>
+ <?lsmb PROCESS input element_data = {
+ name = 'col_weight'
+ type = 'checkbox'
+ value = '1'
+ label = text('Weight')
+ } ?></td>
+ <td>
+ <?lsmb PROCESS input element_data = {
+ name = 'col_notes'
+ type = 'checkbox'
+ value = '1'
+ label = text('Notes')
+ } ?></td>
+ <td>
+ <?lsmb PROCESS input element_data = {
+ name = 'col_image'
+ type = 'checkbox'
+ value = '1'
+ label = text('Image')
+ } ?></td>
+ <td>
+ <?lsmb PROCESS input element_data = {
+ name = 'col_drawing'
+ type = 'checkbox'
+ value = '1'
+ label = text('Drawing')
+ } ?></td>
+ <td>
+ <?lsmb PROCESS input element_data = {
+ name = 'col_microfiche'
+ type = 'checkbox'
+ value = '1'
+ label = text('Microfiche')
+ } ?></td>
+ </tr>
+ <tr><td>
+ <?lsmb PROCESS input element_data = {
+ name = 'col_make'
+ type = 'checkbox'
+ value = '1'
+ label = text('Make')
+ } ?></td>
+ <td>
+ <?lsmb PROCESS input element_data = {
+ name = 'col_model'
+ type = 'checkbox'
+ value = '1'
+ label = text('Model')
+ } ?></td>
+ <td>
+ <?lsmb PROCESS input element_data = {
+ name = 'col_warehouse'
+ type = 'checkbox'
+ value = '1'
+ label = text('Warehouse')
+ } ?></td>
+ <td>
+ <?lsmb PROCESS input element_data = {
+ name = 'col_curr'
+ type = 'checkbox'
+ value = '1'
+ label = text('Currency')
+ } ?></td>
+ <td>
+ <?lsmb PROCESS input element_data = {
+ name = 'col_serialnumber'
+ type = 'checkbox'
+ value = '1'
+ label = text('Serial Number') #'
+ } ?></td>
+ </tr>
+ <tr><td>
+ <?lsmb PROCESS input element_data = {
+ name = 'subtotal'
+ type = 'checkbox'
+ value = '1'
+ label = text('subtotal')
+ } ?></td>
+ </tr>
+ </table>
+</table>
+<?lsmb PROCESS button element_data = {
+ name = 'action'
+ class = 'submit'
+ type = 'submit'
+ value = 'search'
+ text = text('Search')
+} ?>
+</form>
+</body>
+</html>
Modified: trunk/bin/ic.pl
===================================================================
--- trunk/bin/ic.pl 2012-12-27 15:15:05 UTC (rev 5434)
+++ trunk/bin/ic.pl 2012-12-29 09:26:11 UTC (rev 5435)
@@ -989,1415 +989,6 @@
}
-sub search {
-
- $form->get_partsgroup( \%myconfig,
- { searchitems => $form->{searchitems} } );
-
- IC->get_warehouses( \%myconfig, \%$form )
- unless $form->{searchitems} =~ /(service|labor)/;
-
- if ( @{ $form->{all_partsgroup} } ) {
- $partsgroup = qq|<option>\n|;
-
- for ( @{ $form->{all_partsgroup} } ) {
- $partsgroup .=
- qq|<option value="|
- . $form->quote( $_->{partsgroup} )
- . qq|--$_->{id}">$_->{partsgroup}\n|;
- }
-
- $partsgroup = qq|
- <th align=right nowrap>| . $locale->text('Group') . qq|</th>
- <td><select name=partsgroup>$partsgroup</select></td>
-|;
-
- $l_partsgroup =
- qq|<input name=l_partsgroup class=checkbox type=checkbox value=Y> |
- . $locale->text('Group');
- }
-
- $l_listprice =
- qq|<input name=l_listprice class=checkbox type=checkbox value=Y> |
- . $locale->text('List Price');
- $l_sellprice =
- qq|<input name=l_sellprice class=checkbox type=checkbox value=Y checked> |
- . $locale->text('Sell Price');
- $l_lastcost =
- qq|<input name=l_lastcost class=checkbox type=checkbox value=Y checked> |
- . $locale->text('Last Cost');
- $l_avgcost =
- qq|<input name=l_avgcost class=checkbox type=checkbox value=Y checked> |
- . $locale->text('Average Cost');
- $l_linetotal =
- qq|<input name=l_linetotal class=checkbox type=checkbox value=Y> |
- . $locale->text('Line Total');
- $l_markup =
- qq|<input name=l_markup class=checkbox type=checkbox value=Y> |
- . $locale->text('Markup');
- $l_account =
- qq|<input name=l_account class=checkbox type=checkbox value=Y> |
- . $locale->text('Accounts');
-
- $bought = qq|
- <td>
- <table>
- <tr>
- <td><input name=bought class=checkbox type=checkbox value=1></td>
- <td nowrap>| . $locale->text('Vendor Invoices') . qq|</td>
- </tr>
- <tr>
- <td><input name=onorder class=checkbox type=checkbox value=1></td>
- <td nowrap>| . $locale->text('Purchase Orders') . qq|</td>
- </tr>
- <tr>
- <td><input name=rfq class=checkbox type=checkbox value=1></td>
- <td nowrap>| . $locale->text('RFQ') . qq|</td>
- </tr>
- </table>
- </td>
-|;
-
- $sold = qq|
- <td>
- <table>
- <tr>
- <td><input name=sold class=checkbox type=checkbox value=1></td>
- <td nowrap>| . $locale->text('Sales Invoices') . qq|</td>
- </tr>
- <tr>
- <td><input name=ordered class=checkbox type=checkbox value=1></td>
- <td nowrap>| . $locale->text('Sales Orders') . qq|</td>
- </tr>
- <tr>
- <td><input name=quoted class=checkbox type=checkbox value=1></td>
- <td nowrap>| . $locale->text('Quotations') . qq|</td>
- </tr>
- </table>
- </td>
-|;
-
- $fromto = qq|
- <td>
- <table>
- <tr>
- <td nowrap><b>| . $locale->text('From') . qq|</b>
- <input class="date" name=transdatefrom size=11 title="$myconfig{dateformat}">
- <b>| . $locale->text('To') . qq|</b>
- <input class="date" name=transdateto size=11 title="$myconfig{dateformat}"></td>
- </tr>
- <tr>
- <td nowrap><input name=method class=radio type=radio value=accrual checked>|
- . $locale->text('Accrual') . qq|
- <input name=method class=radio type=radio value=cash>|
- . $locale->text('Cash')
- . qq|</td>
- </tr>
- <tr>
- <td nowrap>
- <input name=open class=checkbox type=checkbox value=1 checked> |
- . $locale->text('Open') . qq|
- <input name=closed class=checkbox type=checkbox> |
- . $locale->text('Closed') . qq|
- <input name=summary type=radio class=radio value=1> |
- . $locale->text('Summary') . qq|
- <input name=summary type=radio class=radio value=0 checked> |
- . $locale->text('Detail') . qq|
- </td>
- </tr>
- </table>
- </td>
-|;
-
- $l_name =
- qq|<input name=l_name class=checkbox type=checkbox value=Y> |
- . $locale->text('Name');
- $l_curr =
- qq|<input name=l_curr class=checkbox type=checkbox value=Y> |
- . $locale->text('Currency');
- $l_employee =
- qq|<input name=l_employee class=checkbox type=checkbox value=Y> |
- . $locale->text('Employee');
- $l_serialnumber =
- qq|<input name=l_serialnumber class=checkbox type=checkbox value=Y> |
- . $locale->text('Serial Number');
-
- $serialnumber = qq|
- <th align=right nowrap>| . $locale->text('Serial Number') . qq|</th>
- <td><input name=serialnumber size=20></td>
-|;
-
- $orphaned = qq|
- <input name=itemstatus class=radio type=radio value=orphaned> |
- . $locale->text('Orphaned');
-
- if ( $form->{searchitems} =~ /(all|part|assembly)/ ) {
-
- $onhand = qq|
- <input name=itemstatus class=radio type=radio value=onhand> |
- . $locale->text('On Hand') . qq|
- <input name=itemstatus class=radio type=radio value=short> |
- . $locale->text('Short') . qq|
-|;
-
- $makemodel = qq|
- <tr>
- <th align=right nowrap>| . $locale->text('Make') . qq|</th>
- <td><input name=make size=20></td>
- <th align=right nowrap>| . $locale->text('Model') . qq|</th>
- <td><input name=model size=20></td>
- </tr>
-|;
-
- $l_make =
- qq|<input name=l_make class=checkbox type=checkbox value=Y> |
- . $locale->text('Make');
- $l_model =
- qq|<input name=l_model class=checkbox type=checkbox value=Y> |
- . $locale->text('Model');
-
- $l_bin =
- qq|<input name=l_bin class=checkbox type=checkbox value=Y> |
- . $locale->text('Bin');
-
- $l_rop =
- qq|<input name=l_rop class=checkbox type=checkbox value=Y> |
- . $locale->text('ROP');
-
- $l_weight =
- qq|<input name=l_weight class=checkbox type=checkbox value=Y> |
- . $locale->text('Weight');
-
- if ( @{ $form->{all_warehouse} } ) {
- $selectwarehouse = "<option>\n";
-
- for ( @{ $form->{all_warehouse} } ) {
- $selectwarehouse .=
-qq|<option value="$_->{description}--$_->{id}">$_->{description}\n|;
- }
-
- $warehouse = qq|
- <th align=right nowrap>| . $locale->text('Warehouse') . qq|</th>
- <td><select name=warehouse>$selectwarehouse</select></td>
-|;
-
- $l_warehouse =
-qq|<input name=l_warehouse class=checkbox type=checkbox value=Y> |
- . $locale->text('Warehouse');
-
- }
-
- $drawing = qq|
- <tr>
- <th align=right nowrap>| . $locale->text('Drawing') . qq|</th>
- <td><input name=drawing size=20></td>
- <th align=right nowrap>| . $locale->text('Microfiche') . qq|</th>
- <td><input name=microfiche size=20></td>
- </tr>
-|;
-
- $l_image =
- qq|<input name=l_image class=checkbox type=checkbox value=Y> |
- . $locale->text('Image');
-
- $l_drawing =
- qq|<input name=l_drawing class=checkbox type=checkbox value=Y> |
- . $locale->text('Drawing');
- $l_microfiche =
-qq|<input name=l_microfiche class=checkbox type=checkbox value=Y> |
- . $locale->text('Microfiche');
-
- }
-
- if ( $form->{searchitems} eq 'assembly' ) {
-
- $bought = "";
-
- $toplevel = qq|
- <tr>
- <td></td>
- <td colspan=3>
- <input name=null class=radio type=radio checked> |
- . $locale->text('Top Level') . qq|
- <input name=individual class=checkbox type=checkbox value=1> |
- . $locale->text('Individual Items') . qq|
- </td>
- </tr>
-|;
- $bom =
- qq|<input name=itemstatus type=radio value=bom> |
- . $locale->text('BOM');
-
- }
- elsif ( $form->{searchitems} eq 'component' ) {
-
- $bought = "";
- $sold = "";
- $fromto = "";
- $l_name = "";
- $l_curr = "";
- $l_employee = "";
- $l_serialnumber = "";
-
- $warehouse = "";
- $serialnumber = "";
- $orphaned = "";
- $l_warehouse = "";
- $l_account = "";
-
- }
- elsif ( $form->{searchitems} eq 'labor' ) {
-
- $sold = "";
-
- $warehouse = "";
- $serialnumber = "";
- $l_avgcost = "";
-
- }
-
- @a = ();
- push @a,
-qq|<input name=l_runningnumber class=checkbox type=checkbox value=Y> |
- . $locale->text('No.');
- push @a,
-qq|<input name=l_partnumber class=checkbox type=checkbox value=Y checked> |
- . $locale->text('Number');
- push @a,
-qq|<input name=l_description class=checkbox type=checkbox value=Y checked> |
- . $locale->text('Description');
- push @a,
- qq|<input name=l_qty class=checkbox type=checkbox value=Y checked> |
- . $locale->text('Qty');
- push @a,
- qq|<input name=l_unit class=checkbox type=checkbox value=Y checked> |
- . $locale->text('Unit');
- push @a,
- qq|<input name=l_priceupdate class=checkbox type=checkbox value=Y> |
- . $locale->text('Updated');
- push @a, $l_partsgroup if $l_partsgroup;
- push @a, $l_listprice if $l_listprice;
- push @a, $l_sellprice if $l_sellprice;
- push @a, $l_lastcost if $l_lastcost;
- push @a, $l_avgcost if $l_avgcost;
- push @a, $l_linetotal if $l_linetotal;
- push @a, $l_markup if $l_markup;
- push @a, $l_bin if $l_bin;
- push @a, $l_rop if $l_rop;
- push @a, $l_weight if $l_weight;
- push @a, qq|<input name=l_notes class=checkbox type=checkbox value=Y> |
- . $locale->text('Notes');
- push @a, $l_image if $l_image;
- push @a, $l_drawing if $l_drawing;
- push @a, $l_microfiche if $l_microfiche;
- push @a, $l_make if $l_make;
- push @a, $l_model if $l_model;
- push @a, $l_warehouse if $l_warehouse;
- push @a, $l_account if $l_account;
- push @a, $l_name if $l_name;
- push @a, $l_curr if $l_curr;
- push @a, $l_employee if $l_employee;
- push @a, $l_serialnumber if $l_serialnumber;
-
- %title = (
- all => 'Items',
- part => 'Parts',
- labor => 'Labor/Overhead',
- service => 'Services',
- assembly => 'Assemblies',
- component => 'Components'
- );
-
- # $locale->text('Items')
- # $locale->text('Parts')
- # $locale->text('Labor/Overhead')
- # $locale->text('Services')
- # $locale->text('Assemblies')
- # $locale->text('Components')
-
- $form->{title} = $locale->text( $title{ $form->{searchitems} } );
-
- $form->header;
-
- print qq|
-<body>
-
-<form method=post action=$form->{script}>
-|;
-
- $form->hide_form(qw(searchitems title));
-
- print qq|
-
-<table width="100%">
- <tr><th class=listtop>$form->{title}</th></tr>
- <tr height="5"></tr>
- <tr valign=top>
- <td>
- <table>
- <tr>
- <th align=right nowrap>| . $locale->text('Number') . qq|</th>
- <td><input name=partnumber size=20></td>
- </tr>
- <tr>
- <th align=right nowrap>| . $locale->text('Description') . qq|</th>
- <td colspan=3><input name=description size=40></td>
- </tr>
- <tr>
- $warehouse
- </tr>
- <tr>
- $partsgroup
- $serialnumber
- </tr>
- $makemodel
- $drawing
- $toplevel
- <tr>
- <td></td>
- <td colspan=3>
- <input name=itemstatus class=radio type=radio value=active checked> |
- . $locale->text('Active') . qq|
- $onhand
- <input name=itemstatus class=radio type=radio value=obsolete> |
- . $locale->text('Obsolete') . qq|
- $orphaned
- $bom
- </td>
- </tr>
- <tr>
- <td></td>
- <td colspan=3>
- <hr size=1 noshade>
- </td>
- </tr>
- <tr>
- <td></td>
- $bought
- $sold
- $fromto
- <tr>
- <td></td>
- <td colspan=3>
- <hr size=1 noshade>
- </td>
- </tr>
- <tr>
- <th align=right nowrap>|
- . $locale->text('Include in Report')
- . qq|</th>
- <td colspan=3>
- <table>
- <tr>
-|;
-
- while (@a) {
- for ( 1 .. 5 ) {
- print qq|<td nowrap>| . shift @a;
- print qq|</td>\n|;
- }
- print qq|</tr>\n|;
- }
-
- print qq|
- </tr>
- <tr>
- <td><input name=l_subtotal class=checkbox type=checkbox value=Y> |
- . $locale->text('Subtotal')
- . qq|</td>
- </tr>
- </table>
- </td>
- </tr>
- </table>
- </td>
- </tr>
- <tr><td colspan=4><hr size=3 noshade></td></tr>
-</table>
-
-<input type="hidden" name="nextsub" value="generate_report">
-
-<br>
-<button class="submit" type="submit" name="action" value="continue">|
- . $locale->text('Continue')
- . qq|</button>|;
-
- $form->hide_form(qw(path login sessionid));
-
- print qq|
-</form>
-|;
-
- if ( $form->{lynx} ) {
- require "bin/menu.pl";
- &menubar;
- }
-
- print qq|
-
-</body>
-</html>
-|;
-
-}
-
-sub generate_report {
-
- # setup $form->{sort}
- unless ( $form->{sort} ) {
- if ( $form->{description} && !( $form->{partnumber} ) ) {
- $form->{sort} = "description";
- }
- else {
- $form->{sort} = "partnumber";
- }
- }
-
- if ( $form->{itemstatus} eq 'bom' ) {
- $form->{l_perassembly} = "Y" if $form->{l_qty} eq "Y";
- $form->{individual} = 1;
- $form->{title} = $locale->text('BOM');
- }
-
- $callback = "$form->{script}?action=generate_report";
- for (
- qw(path login sessionid searchitems itemstatus individual bom l_linetotal method)
- )
- {
- $callback .= qq|&$_=$form->{$_}|;
- }
- for (qw(warehouse partsgroup title)) {
- $callback .= qq|&$_=| . $form->escape( $form->{$_}, 1 );
- }
-
- # if we have a serialnumber limit search
- if ( $form->{serialnumber} || $form->{l_serialnumber} ) {
- $form->{l_serialnumber} = "Y";
- unless ( $form->{bought}
- || $form->{sold}
- || $form->{onorder}
- || $form->{ordered} )
- {
- if ( $form->{searchitems} eq 'assembly' ) {
- $form->{sold} = $form->{ordered} = 1;
- }
- else {
- $form->{bought} = $form->{sold} = $form->{onorder} =
- $form->{ordered} = 1;
- }
- }
- }
-
- if ( $form->{itemstatus} eq 'active' ) {
- $option .= $locale->text('Active') . " : ";
- }
- if ( $form->{itemstatus} eq 'obsolete' ) {
- $form->{onhand} = $form->{short} = 0;
-
- $form->{l_qty} = 0;
- $form->{warehouse} = "";
- $form->{l_warehouse} = 0;
-
- $option .= $locale->text('Obsolete') . " : ";
- }
- if ( $form->{itemstatus} eq 'orphaned' ) {
- $form->{onhand} = $form->{short} = 0;
- $form->{bought} = $form->{sold} = 0;
- $form->{onorder} = $form->{ordered} = 0;
- $form->{rfq} = $form->{quoted} = 0;
-
- $form->{l_qty} = 0;
- $form->{warehouse} = "";
- $form->{l_warehouse} = 0;
-
- $form->{transdatefrom} = $form->{transdateto} = "";
-
- $option .= $locale->text('Orphaned') . " : ";
- }
- if ( $form->{itemstatus} eq 'onhand' ) {
- $option .= $locale->text('On Hand') . " : ";
- $form->{l_onhand} = "Y";
- }
- if ( $form->{itemstatus} eq 'short' ) {
- $option .= $locale->text('Short') . " : ";
- $form->{l_onhand} = "Y";
- $form->{l_rop} = "Y" unless $form->{searchitems} eq 'labor';
-
- $form->{warehouse} = "";
- $form->{l_warehouse} = 0;
- }
-
- if ( $form->{l_account} ) {
- for (qw(l_name l_curr l_employee)) { delete $form->{$_} }
- }
- else {
- $ok = 0;
- foreach $l (qw(l_name l_curr l_employee)) {
- if ( $form->{$l} ) {
- foreach $v (qw(onorder ordered rfq quoted bought sold)) {
- if ( $form->{$v} ) {
- $ok = 1;
- last;
- }
- }
- if ( !$ok ) {
- for (qw(onorder ordered rfq quoted bought sold)) {
- $form->{$_} = 1;
- }
- }
- last;
- }
- }
- }
-
- if ( $form->{onorder} ) {
- $form->{l_ordnumber} = "Y";
- $callback .= "&onorder=$form->{onorder}";
- $option .= $locale->text('Purchase Order') . " : ";
- }
- if ( $form->{ordered} ) {
- $form->{l_ordnumber} = "Y";
- $callback .= "&ordered=$form->{ordered}";
- $option .= $locale->text('Sales Order') . " : ";
- }
- if ( $form->{rfq} ) {
- $form->{l_quonumber} = "Y";
- $callback .= "&rfq=$form->{rfq}";
- $option .= $locale->text('RFQ') . " : ";
- }
- if ( $form->{quoted} ) {
- $form->{l_quonumber} = "Y";
- $callback .= ""ed=$form->{quoted}";
- $option .= $locale->text('Quotation') . " : ";
- }
- if ( $form->{bought} ) {
- $form->{l_invnumber} = "Y";
- $callback .= "&bought=$form->{bought}";
- $option .= $locale->text('Vendor Invoice') . " : ";
- }
- if ( $form->{sold} ) {
- $form->{l_invnumber} = "Y";
- $callback .= "&sold=$form->{sold}";
- $option .= $locale->text('Sales Invoice') . " : ";
- }
- if ( $form->{sold} || $form->{bought} ) {
- $label = ucfirst $form->{method};
- $option .= $locale->text($label) . " : ";
- }
-
- if ( $form->{bought}
- || $form->{sold}
- || $form->{onorder}
- || $form->{ordered}
- || $form->{rfq}
- || $form->{quoted} )
- {
-
- # warehouse stuff is meaningless
- $form->{warehouse} = "";
- $form->{l_warehouse} = 0;
-
- $form->{l_account} = "";
-
- if ( $form->{open} ) {
- $callback .= "&open=$form->{open}";
- $option .= $locale->text('Open');
- }
- if ( $form->{closed} ) {
- $callback .= "&closed=$form->{closed}";
- if ( $form->{open} ) {
- $option .= " : " . $locale->text('Closed');
- }
- else {
- $option .= $locale->text('Closed');
- }
- }
- if ( $form->{summary} ) {
- $callback .= "&summary=$form->{summary}";
- $option .= " : " . $locale->text('Summary');
- $form->{l_ordnumber} = "";
- $form->{l_quonumber} = "";
- $form->{l_invnumber} = "";
- }
- else {
- $option .= " : " . $locale->text('Detail');
- }
-
- if ( $form->{transdatefrom} ) {
- $callback .= "&transdatefrom=$form->{transdatefrom}";
- $option .= "\n<br>"
- . $locale->text('From')
- . " "
- . $locale->date( \%myconfig, $form->{transdatefrom}, 1 );
- }
- if ( $form->{transdateto} ) {
- $callback .= "&transdateto=$form->{transdateto}";
- $option .= "\n<br>"
- . $locale->text('To')
- . " "
- . $locale->date( \%myconfig, $form->{transdateto}, 1 );
- }
- }
-
- if ( $form->{warehouse} ) {
- ($warehouse) = split /--/, $form->{warehouse};
- $option .= "<br>" . $locale->text('Warehouse') . " : $warehouse";
- $form->{l_warehouse} = 0;
- }
-
- $option .= "<br>";
-
- if ( $form->{partnumber} ) {
- $callback .= "&partnumber=" . $form->escape( $form->{partnumber}, 1 );
- $option .= $locale->text('Number') . qq| : $form->{partnumber}<br>|;
- }
- if ( $form->{partsgroup} ) {
- ($partsgroup) = split /--/, $form->{partsgroup};
- $option .= $locale->text('Group') . qq| : $partsgroup<br>|;
- }
- if ( $form->{serialnumber} ) {
- $callback .=
- "&serialnumber=" . $form->escape( $form->{serialnumber}, 1 );
- $option .=
- $locale->text('Serial Number') . qq| : $form->{serialnumber}<br>|;
- }
- if ( $form->{description} ) {
- $callback .= "&description=" . $form->escape( $form->{description}, 1 );
- $description = $form->{description};
- $description =~ s/\r?\n/<br>/g;
- $option .=
- $locale->text('Description') . qq| : $form->{description}<br>|;
- }
- if ( $form->{make} ) {
- $callback .= "&make=" . $form->escape( $form->{make}, 1 );
- $option .= $locale->text('Make') . qq| : $form->{make}<br>|;
- }
- if ( $form->{model} ) {
- $callback .= "&model=" . $form->escape( $form->{model}, 1 );
- $option .= $locale->text('Model') . qq| : $form->{model}<br>|;
- }
- if ( $form->{drawing} ) {
- $callback .= "&drawing=" . $form->escape( $form->{drawing}, 1 );
- $option .= $locale->text('Drawing') . qq| : $form->{drawing}<br>|;
- }
- if ( $form->{microfiche} ) {
- $callback .= "µfiche=" . $form->escape( $form->{microfiche}, 1 );
- $option .= $locale->text('Microfiche') . qq| : $form->{microfiche}<br>|;
- }
-
- if ( $form->{l_markup} ) {
- $form->{l_sellprice} = "Y";
- $form->{l_lastcostmarkup} = "Y" if $form->{l_lastcost};
- $form->{l_avgcostmarkup} = "Y" if $form->{l_avgcost};
- }
-
- @columns =
- $form->sort_columns(
- qw(partnumber description notes assemblypartnumber partsgroup make model bin onhand perassembly rop unit listprice linetotallistprice sellprice linetotalsellprice lastcost linetotallastcost lastcostmarkup avgcost linetotalavgcost avgcostmarkup curr priceupdate weight image drawing microfiche invnumber ordnumber quonumber name employee serialnumber warehouse)
- );
- unshift @columns, "runningnumber";
-
- if ( $form->{l_linetotal} ) {
- $form->{l_onhand} = "Y";
- $form->{l_linetotalsellprice} = "Y" if $form->{l_sellprice};
- $form->{l_linetotallastcost} = "Y" if $form->{l_lastcost};
- $form->{l_linetotalavgcost} = "Y" if $form->{l_avgcost};
- $form->{l_linetotallistprice} = "Y" if $form->{l_listprice};
- }
-
- if ( $form->{searchitems} eq 'service' ) {
-
- # remove bin, weight and rop from list
- for (qw(bin weight rop)) { $form->{"l_$_"} = "" }
-
- $form->{l_onhand} = "";
-
- # qty is irrelevant unless bought or sold
- if ( $form->{bought}
- || $form->{sold}
- || $form->{onorder}
- || $form->{ordered}
- || $form->{rfq}
- || $form->{quoted} )
- {
- $form->{l_onhand} = "Y";
- }
- else {
- for (qw(sellprice lastcost avgcost listprice)) {
- $form->{"l_linetotal$_"} = "";
- }
- }
- }
- else {
- $form->{l_onhand} = "Y" if $form->{l_qty};
- }
-
- foreach $item (@columns) {
- if ( $form->{"l_$item"} eq "Y" ) {
- push @column_index, $item;
-
- # add column to callback
- $callback .= "&l_$item=Y";
- }
- }
-
- if ( $form->{l_account} eq 'Y' ) {
- if ( $form->{searchitems} eq 'all' || $form->{searchitems} eq 'part' ) {
- push @column_index, (qw(inventory income expense tax));
- }
- elsif ( $form->{searchitems} eq 'service' ) {
- push @column_index, (qw(income expense tax));
- }
- elsif ( $form->{searchitems} eq 'assembly' ) {
- push @column_index, (qw(income tax));
- }
- else {
- push @column_index, (qw(inventory expense));
- }
-
- $callback .= "&l_account=Y";
- }
-
- if ( $form->{l_subtotal} eq 'Y' ) {
- $callback .= "&l_subtotal=Y";
- }
-
- IC->all_parts( \%myconfig, \%$form );
-
- $callback .= "&direction=$form->{direction}&oldsort=$form->{oldsort}";
-
- $href = $callback;
-
- $form->sort_order();
-
- $callback =~ s/(direction=).*?\&/$1$form->{direction}\&/;
-
- if ( $form->{searchitems} eq 'assembly' ) {
- if ( $form->{l_partnumber} ) {
-
- # replace partnumber with partnumber_
- $ndx = 0;
- foreach $item (@column_index) {
- $ndx++;
- last if $item eq 'partnumber';
- }
-
- splice @column_index, $ndx, 0,
- map { "partnumber_$_" } ( 1 .. $form->{pncol} );
- $colspan = $form->{pncol} + 1;
- }
- }
-
- if ( $form->{searchitems} eq 'component' ) {
- if ( $form->{l_partnumber} ) {
-
- # splice it in after the partnumber
- $ndx = 0;
- foreach $item (@column_index) {
- $ndx++;
- last if $item eq 'partnumber';
- }
-
- @a = splice @column_index, 0, $ndx;
- unshift @column_index, "assemblypartnumber";
- unshift @column_index, @a;
- }
- }
-
- $column_header{runningnumber} = qq|<th a class=listheading> </th>|;
- $column_header{partnumber} =
-qq|<th nowrap colspan=$colspan><a class=listheading href=$href&sort=partnumber>|
- . $locale->text('Number')
- . qq|</a></th>|;
- $column_header{description} =
- qq|<th nowrap><a class=listheading href=$href&sort=description>|
- . $locale->text('Description')
- . qq|</a></th>|;
- $column_header{notes} =
- qq|<th nowrap class=listheading>| . $locale->text('Notes') . qq|</th>|;
- $column_header{partsgroup} =
- qq|<th nowrap><a class=listheading href=$href&sort=partsgroup>|
- . $locale->text('Group')
- . qq|</a></th>|;
- $column_header{bin} =
- qq|<th><a class=listheading href=$href&sort=bin>|
- . $locale->text('Bin')
- . qq|</a></th>|;
- $column_header{priceupdate} =
- qq|<th nowrap><a class=listheading href=$href&sort=priceupdate>|
- . $locale->text('Updated')
- . qq|</a></th>|;
- $column_header{onhand} =
- qq|<th class=listheading nowrap>| . $locale->text('Qty') . qq|</th>|;
- $column_header{perassembly} = qq|<th> </th>|;
- $column_header{unit} =
- qq|<th class=listheading nowrap>| . $locale->text('Unit') . qq|</th>|;
- $column_header{listprice} =
- qq|<th class=listheading nowrap>|
- . $locale->text('List Price')
- . qq|</th>|;
- $column_header{lastcost} =
- qq|<th class=listheading nowrap>|
- . $locale->text('Last Cost')
- . qq|</th>|;
- $column_header{avgcost} =
- qq|<th class=listheading nowrap>| . $locale->text('Avg Cost') . qq|</th>|;
- $column_header{rop} =
- qq|<th class=listheading nowrap>| . $locale->text('ROP') . qq|</th>|;
- $column_header{weight} =
- qq|<th class=listheading nowrap>| . $locale->text('Weight') . qq|</th>|;
- $column_header{avgcostmarkup} = qq|<th class=listheading nowrap>%</th>|;
- $column_header{lastcostmarkup} = qq|<th class=listheading nowrap>%</th>|;
-
- $column_header{make} =
- qq|<th nowrap><a class=listheading href=$href&sort=make>|
- . $locale->text('Make')
- . qq|</a></th>|;
- $column_header{model} =
- qq|<th nowrap><a class=listheading href=$href&sort=model>|
- . $locale->text('Model')
- . qq|</a></th>|;
-
- $column_header{invnumber} =
- qq|<th nowrap><a class=listheading href=$href&sort=invnumber>|
- . $locale->text('Invoice Number')
- . qq|</a></th>|;
- $column_header{ordnumber} =
- qq|<th nowrap><a class=listheading href=$href&sort=ordnumber>|
- . $locale->text('Order Number')
- . qq|</a></th>|;
- $column_header{quonumber} =
- qq|<th nowrap><a class=listheading href=$href&sort=quonumber>|
- . $locale->text('Quotation')
- . qq|</a></th>|;
-
- $column_header{name} =
- qq|<th nowrap><a class=listheading href=$href&sort=name>|
- . $locale->text('Name')
- . qq|</a></th>|;
-
- $column_header{employee} =
- qq|<th nowrap><a class=listheading href=$href&sort=employee>|
- . $locale->text('Employee')
- . qq|</a></th>|;
-
- $column_header{sellprice} =
- qq|<th class=listheading nowrap>|
- . $locale->text('Sell Price')
- . qq|</th>|;
-
- for (qw(sellprice lastcost avgcost listprice)) {
- $column_header{"linetotal$_"} =
- qq|<th class=listheading nowrap>|
- . $locale->text('Extended')
- . qq|</th>|;
- }
-
- $column_header{curr} =
- qq|<th nowrap><a class=listheading href=$href&sort=curr>|
- . $locale->text('Curr')
- . qq|</a></th>|;
-
- $column_header{image} =
- qq|<th class=listheading nowrap>|
- . $locale->text('Image')
- . qq|</a></th>|;
- $column_header{drawing} =
- qq|<th nowrap><a class=listheading href=$href&sort=drawing>|
- . $locale->text('Drawing')
- . qq|</a></th>|;
- $column_header{microfiche} =
- qq|<th nowrap><a class=listheading href=$href&sort=microfiche>|
- . $locale->text('Microfiche')
- . qq|</a></th>|;
-
- $column_header{serialnumber} =
- qq|<th nowrap><a class=listheading href=$href&sort=serialnumber>|
- . $locale->text('Serial Number')
- . qq|</a></th>|;
-
- $column_header{assemblypartnumber} =
- qq|<th nowrap><a class=listheading href=$href&sort=assemblypartnumber>|
- . $locale->text('Assembly')
- . qq|</a></th>|;
-
- $column_header{warehouse} =
- qq|<th nowrap class=listheading>|
- . $locale->text('Warehouse')
- . qq|</th>|;
-
- $column_header{inventory} =
- qq|<th nowrap class=listheading>|
- . $locale->text('Inventory')
- . qq|</th>|;
- $column_header{income} =
- qq|<th nowrap class=listheading>| . $locale->text('Income') . qq|</th>|;
- $column_header{expense} =
- qq|<th nowrap class=listheading>| . $locale->text('Expense') . qq|</th>|;
- $column_header{tax} =
- qq|<th nowrap class=listheading>| . $locale->text('Tax') . qq|</th>|;
-
- $form->header;
-
- $i = 1;
- if ( $form->{searchitems} eq 'part' ) {
- $button{'Goods & Services--Add Part'}{code} =
-qq|<button class="submit" type="submit" name="action" value="add_part">|
- . $locale->text('Add Part')
- . qq|</button> |;
- $button{'Goods & Services--Add Part'}{order} = $i++;
- }
- if ( $form->{searchitems} eq 'service' ) {
- $button{'Goods & Services--Add Service'}{code} =
-qq|<button class="submit" type="submit" name="action" value="add_service">|
- . $locale->text('Add Service')
- . qq|</button> |;
- $button{'Goods & Services--Add Service'}{order} = $i++;
- }
- if ( $form->{searchitems} eq 'assembly' ) {
- $button{'Goods & Services--Add Assembly'}{code} =
-qq|<button class="submit" type="submit" name="action" value="add_assembly">|
- . $locale->text('Add Assembly')
- . qq|</button> |;
- $button{'Goods & Services--Add Assembly'}{order} = $i++;
- }
- if ( $form->{searchitems} eq 'labor' ) {
- $button{'Goods & Services--Add Labor/Overhead'}{code} =
-qq|<button class="submit" type="submit" name="action" value="add_labor_overhead">|
- . $locale->text('Add Labor/Overhead')
- . qq|</button> |;
- $button{'Goods & Services--Add Labor/Overhead'}{order} = $i++;
- }
-
- foreach $item ( split /;/, $myconfig{acs} ) {
- delete $button{$item};
- }
-
- print qq|
-<body>
-
-<table width=100%>
- <tr>
- <th class=listtop>$form->{title}</th>
- </tr>
- <tr height="5"></tr>
-
- <tr><td>$option</td></tr>
-
- <tr>
- <td>
- <table width=100%>
- <tr class=listheading>
-|;
-
- for (@column_index) { print "\n$column_header{$_}" }
-
- print qq|
- </tr>
- |;
-
- # add order to callback
- $form->{callback} = $callback .= "&sort=$form->{sort}";
-
- # escape callback for href
- $callback = $form->escape($callback);
-
- $k = $#{ @{ $form->{parts} } };
- @groupby = ( $form->{sort} );
-
- if ( $form->{summary} ) {
- @groupby = ();
- for (
- qw(partnumber description notes partsgroup make model bin curr priceupdate image drawing microfiche invnumber ordnumber quonumber name employee serialnumber warehouse)
- )
- {
- $a{$_} = 1;
- }
-
- for (@column_index) {
- if ( $a{$_} ) {
- push @groupby, $_;
- }
- }
- push @groupby, "id";
- }
-
- if ( $k > 0 ) {
- $samegroup = "";
- for (@groupby) { $samegroup .= $form->{parts}->[0]->{$_} }
- }
-
- $i = 0;
- $n = 0;
-
- foreach $ref ( @{ $form->{parts} } ) {
-
- $ref->{exchangerate} ||= 1;
- $ref->{discount} *= 1;
-
- if ( $form->{summary} ) {
-
- $summary{ $ref->{id} }{total} += $ref->{sellprice} * $ref->{onhand};
- $summary{ $ref->{id} }{onhand} += $ref->{onhand};
-
- if ( $n < $k ) {
- $nextgroup = "";
- for (@groupby) {
- $nextgroup .= $form->{parts}->[ $n + 1 ]->{$_};
- }
- $n++;
-
- $form->{parts}->[$n]->{exchangerate} ||= 1;
-
- if ( $samegroup eq $nextgroup ) {
- for (qw(exchangerate discount)) {
- $form->{parts}->[$n]->{$_} =
- ( $ref->{$_} + $form->{parts}->[$n]->{$_} ) / 2;
- }
- next;
- }
- $samegroup = $nextgroup;
- }
-
- $ref->{onhand} = $summary{ $ref->{id} }{onhand};
- $ref->{sellprice} =
- ( $ref->{onhand} )
- ? $summary{ $ref->{id} }{total} / $ref->{onhand}
- : 0;
-
- $summary{ $ref->{id} }{total} = 0;
- $summary{ $ref->{id} }{onhand} = 0;
-
- }
-
- if ( $form->{l_subtotal} eq 'Y' && !$ref->{assemblyitem} ) {
- if ( $sameitem ne $ref->{ $form->{sort} } ) {
- &parts_subtotal;
- $sameitem = $ref->{ $form->{sort} };
- }
- }
-
- $i++;
-
- if ( $form->{l_curr} ) {
- if ( $ref->{module} eq 'oe' ) {
- $ref->{sellprice} =
- $ref->{sellprice} * ( 1 - $ref->{discount} );
- }
- else {
- for (qw(sellprice listprice lastcost avgcost)) {
- $ref->{$_} /= $ref->{exchangerate};
- }
- }
- }
- else {
- if ( $ref->{module} eq 'oe' ) {
- $ref->{sellprice} =
- $ref->{sellprice} * ( 1 - $ref->{discount} );
- for (qw(sellprice listprice lastcost avgcost)) {
- $ref->{$_} *= $ref->{exchangerate};
- }
- }
- }
-
- if ( !$form->{summary} ) {
- for (qw(sellprice listprice lastcost avgcost)) {
- $ref->{$_} = $form->round_amount( $ref->{$_}, 2 );
- }
- }
-
- if ( $form->{l_markup} ) {
- $ref->{lastcostmarkup} =
- ( ( $ref->{sellprice} / $ref->{lastcost} ) - 1 ) * 100
- if $ref->{lastcost} != 0;
- $ref->{avgcostmarkup} =
- ( ( $ref->{sellprice} / $ref->{avgcost} ) - 1 ) * 100
- if $ref->{avgcost} != 0;
- }
-
- # use this for assemblies
- $onhand = $ref->{onhand};
-
- for (qw(description notes)) { $ref->{$_} =~ s/\r?\n/<br>/g }
-
- for ( 1 .. $form->{pncol} ) {
- $column_data{"partnumber_$_"} = "<td> </td>";
- }
-
- $column_data{runningnumber} = "<td align=right>$i</td>";
- $column_data{partnumber} =
-"<td><a href=$form->{script}?action=edit&id=$ref->{id}&path=$form->{path}&login=$form->{login}&sessionid=$form->{sessionid}&callback=$callback>$ref->{partnumber} </a></td>";
-
- if ( $ref->{assemblypartnumber} ) {
- if ( $sameid eq $ref->{id} ) {
- $i--;
- for (qw(runningnumber partnumber)) {
- $column_data{$_} = "<td> </td>";
- }
- }
- }
-
- $column_data{assemblypartnumber} =
-"<td><a href=$form->{script}?action=edit&id=$ref->{assembly_id}&path=$form->{path}&login=$form->{login}&sessionid=$form->{sessionid}&callback=$callback>$ref->{assemblypartnumber} </a></td>";
-
- if ( $ref->{assemblyitem} ) {
- $onhand = 0 if $form->{sold};
- $ref->{income} = "";
-
- for (qw(runningnumber partnumber)) {
- $column_data{$_} = "<td> </td>";
- }
- $i--;
-
- $column_data{"partnumber_$ref->{stagger}"} =
-"<td><a href=$form->{script}?action=edit&id=$ref->{id}&path=$form->{path}&login=$form->{login}&sessionid=$form->{sessionid}&callback=$callback>$ref->{partnumber} </a></td>";
-
- }
-
- for (qw(description notes partsgroup employee curr)) {
- $column_data{$_} = "<td>$ref->{$_} </td>";
- }
-
- $column_data{onhand} =
- "<td align=right>"
- . $form->format_amount( \%myconfig, $ref->{onhand}, '', " " )
- . "</td>";
- $column_data{perassembly} =
- "<td align=right>"
- . $form->format_amount( \%myconfig, $ref->{perassembly}, '',
- " " )
- . "</td>";
-
- if ( $form->{summary} ) {
- $column_data{sellprice} =
- "<td align=right>"
- . $form->format_amount( \%myconfig, $ref->{sellprice}, 4,
- " " )
- . "</td>";
- }
- else {
- $column_data{sellprice} =
- "<td align=right>"
- . $form->format_amount( \%myconfig, $ref->{sellprice}, 2,
- " " )
- . "</td>";
- }
- for (qw(listprice lastcost avgcost)) {
- $column_data{$_} =
- "<td align=right>"
- . $form->format_amount( \%myconfig, $ref->{$_}, 2, " " )
- . "</td>";
- }
-
- for (qw(lastcost avgcost)) {
- $column_data{"${_}markup"} = "<td align=right>"
- . $form->format_amount( \%myconfig, $ref->{"${_}markup"}, 1,
- " " )
- . "</td>";
- }
-
- if ( $form->{l_linetotal} ) {
- for (qw(sellprice lastcost avgcost listprice)) {
- $column_data{"linetotal$_"} = "<td align=right>"
- . $form->format_amount( \%myconfig,
- $ref->{onhand} * $ref->{$_},
- 2, " " )
- . "</td>";
- }
- }
-
- if ( $ref->{assemblyitem} && $ref->{stagger} > 1 ) {
- for (qw(sellprice lastcost avgcost listprice)) {
- $column_data{"linetotal$_"} = "<td> </td>";
- }
- }
-
- if ( !$ref->{assemblyitem} ) {
- $totalsellprice += $onhand * $ref->{sellprice};
- $totallastcost += $onhand * $ref->{lastcost};
- $totalavgcost += $onhand * $ref->{avgcost};
- $totallistprice += $onhand * $ref->{listprice};
-
- $subtotalonhand += $onhand;
- $subtotalsellprice += $onhand * $ref->{sellprice};
- $subtotallastcost += $onhand * $ref->{lastcost};
- $subtotalavgcost += $onhand * $ref->{avgcost};
- $subtotallistprice += $onhand * $ref->{listprice};
- }
-
- $column_data{rop} =
- "<td align=right>"
- . $form->format_amount( \%myconfig, $ref->{rop}, '', " " )
- . "</td>";
- $column_data{weight} =
- "<td align=right>"
- . $form->format_amount( \%myconfig, $ref->{weight}, '', " " )
- . "</td>";
- $column_data{unit} = "<td>$ref->{unit} </td>";
- $column_data{bin} = "<td>$ref->{bin} </td>";
- $column_data{priceupdate} = "<td>$ref->{priceupdate} </td>";
-
- $ref->{module} = 'ps' if $ref->{till};
- $column_data{invnumber} =
- ( $ref->{module} ne 'oe' )
- ? "<td><a href=$ref->{module}.pl?action=edit&type=invoice&id=$ref->{trans_id}&path=$form->{path}&login=$form->{login}&sessionid=$form->{sessionid}&callback=$callback>$ref->{invnumber} </a></td>"
- : "<td>$ref->{invnumber} </td>";
- $column_data{ordnumber} =
- ( $ref->{module} eq 'oe' )
- ? "<td><a href=$ref->{module}.pl?action=edit&type=$ref->{type}&id=$ref->{trans_id}&path=$form->{path}&login=$form->{login}&sessionid=$form->{sessionid}&callback=$callback>$ref->{ordnumber} </a></td>"
- : "<td>$ref->{ordnumber} </td>";
- $column_data{quonumber} =
- ( $ref->{module} eq 'oe' && !$ref->{ordnumber} )
- ? "<td><a href=$ref->{module}.pl?action=edit&type=$ref->{type}&id=$ref->{trans_id}&path=$form->{path}&login=$form->{login}&sessionid=$form->{sessionid}&callback=$callback>$ref->{quonumber} </a></td>"
- : "<td>$ref->{quonumber} </td>";
-
- $column_data{name} = "<td>$ref->{name} </td>";
-
- $column_data{image} =
- ( $ref->{image} )
- ? "<td><a href=$ref->{image}><img src=$ref->{image} height=32 border=0></a></td>"
- : "<td> </td>";
- $column_data{drawing} =
- ( $ref->{drawing} )
- ? "<td><a href=$ref->{drawing}>$ref->{drawing}</a></td>"
- : "<td> </td>";
- $column_data{microfiche} =
- ( $ref->{microfiche} )
- ? "<td><a href=$ref->{microfiche}>$ref->{microfiche}</a></td>"
- : "<td> </td>";
-
- for (qw(make model serialnumber warehouse inventory income expense tax))
- {
- $column_data{$_} = "<td>$ref->{$_} </td>";
- }
-
- $j++;
- $j %= 2;
- print "<tr class=listrow$j>";
-
- for (@column_index) { print "\n$column_data{$_}" }
-
- print qq|
- </tr>
-|;
-
- $sameid = $ref->{id};
-
- }
-
- if ( $form->{l_subtotal} eq 'Y' ) {
- &parts_subtotal;
- }
-
- if ( $form->{"l_linetotal"} ) {
- for (@column_index) { $column_data{$_} = "<td> </td>" }
- $column_data{linetotalsellprice} =
- "<th class=listtotal align=right>"
- . $form->format_amount( \%myconfig, $totalsellprice, 2, " " )
- . "</th>";
- $column_data{linetotallastcost} =
- "<th class=listtotal align=right>"
- . $form->format_amount( \%myconfig, $totallastcost, 2, " " )
- . "</th>";
- $column_data{linetotalavgcost} =
- "<th class=listtotal align=right>"
- . $form->format_amount( \%myconfig, $totalavgcost, 2, " " )
- . "</th>";
- $column_data{linetotallistprice} =
- "<th class=listtotal align=right>"
- . $form->format_amount( \%myconfig, $totallistprice, 2, " " )
- . "</th>";
-
- print "<tr class=listtotal>";
-
- for (@column_index) { print "\n$column_data{$_}" }
-
- print qq|</tr>
- |;
- }
-
- print qq|
- </table>
- </td>
- </tr>
- <tr><td><hr size=3 noshade></td></tr>
-</table>
-
-|;
-
- print qq|
-
-<br>
-
-<form method=post action=$form->{script}>
-
-<input type=hidden name=item value=$form->{searchitems}>
-|;
-
- $form->hide_form(qw(callback path login sessionid));
-
- foreach $item ( sort { $a->{order} <=> $b->{order} } %button ) {
- print $item->{code};
- }
-
- if ( $form->{lynx} ) {
- require "bin/menu.pl";
- &menubar;
- }
-
- print qq|
- </form>
-
-</body>
-</html>
-|;
-
-}
-
-sub parts_subtotal {
-
- for (@column_index) { $column_data{$_} = "<td> </td>" }
- $subtotalonhand = 0
- if ( $form->{searchitems} eq 'assembly' && $form->{individual} );
-
- $column_data{onhand} =
- "<th class=listsubtotal align=right>"
- . $form->format_amount( \%myconfig, $subtotalonhand, '', " " )
- . "</th>";
-
- $column_data{linetotalsellprice} =
- "<th class=listsubtotal align=right>"
- . $form->format_amount( \%myconfig, $subtotalsellprice, 2, " " )
- . "</th>";
- $column_data{linetotallistprice} =
- "<th class=listsubtotal align=right>"
- . $form->format_amount( \%myconfig, $subtotallistprice, 2, " " )
- . "</th>";
- $column_data{linetotallastcost} =
- "<th class=listsubtotal align=right>"
- . $form->format_amount( \%myconfig, $subtotallastcost, 2, " " )
- . "</th>";
- $column_data{linetotalavgcost} =
- "<th class=listsubtotal align=right>"
- . $form->format_amount( \%myconfig, $subtotalavgcost, 2, " " )
- . "</th>";
-
- $subtotalonhand = 0;
- $subtotalsellprice = 0;
- $subtotallistprice = 0;
- $subtotallastcost = 0;
- $subtotalavgcost = 0;
-
- print "<tr class=listsubtotal>";
-
- for (@column_index) { print "\n$column_data{$_}" }
-
- print qq|
- </tr>
-|;
-
-}
-
sub requirements {
$form->get_partsgroup( \%myconfig, { searchitems => 'parts' } );
Copied: trunk/goods.pl (from rev 5434, trunk/customer.pl)
===================================================================
--- trunk/goods.pl (rev 0)
+++ trunk/goods.pl 2012-12-29 09:26:11 UTC (rev 5435)
@@ -0,0 +1,8 @@
+#!/usr/bin/perl
+use FindBin;
+BEGIN {
+ lib->import($FindBin::Bin) unless $ENV{mod_perl}
+}
+
+
+require 'lsmb-request.pl';
Modified: trunk/sql/Pg-database.sql
===================================================================
--- trunk/sql/Pg-database.sql 2012-12-27 15:15:05 UTC (rev 5434)
+++ trunk/sql/Pg-database.sql 2012-12-29 09:26:11 UTC (rev 5435)
@@ -2674,24 +2674,9 @@
71 Quotations 70 1
72 RFQs 70 2
74 Journal Entry 73 1
-78 Add Part 77 1
-79 Add Service 77 2
-80 Add Assembly 77 3
-81 Add Overhead 77 4
-82 Add Group 77 5
-83 Add Pricegroup 77 6
-84 Stock Assembly 77 7
-85 Reports 77 8
-86 All Items 85 1
-87 Parts 85 2
88 Requirements 85 3
-89 Services 85 4
-90 Labor 85 5
91 Groups 85 6
92 Pricegroups 85 7
-93 Assembly 85 8
-94 Components 85 9
-95 Translations 77 9
96 Description 95 1
97 Partsgroup 95 2
99 Add Project 98 1
@@ -2780,6 +2765,10 @@
111 Trial Balance 109 1
112 Income Statement 109 2
60 Combine 50 5
+78 Add Part 77 2
+79 Add Service 77 3
+80 Add Assembly 77 4
+86 Search 77 1
201 Payments 200 1
202 Reverse Payment 200 2
210 Drafts 205 2
@@ -2850,9 +2839,14 @@
116 Batch Printing 0 20
9 Outstanding 4 1
10 Outstanding 24 1
+81 Add Overhead 77 5
+82 Add Group 77 6
+83 Add Pricegroup 77 7
+84 Stock Assembly 77 8
+95 Translations 77 10
+85 Reports 77 9
\.
-
--
-- Name: menu_node_parent_key; Type: CONSTRAINT; Schema: public; Owner: ledgersmb; Tablespace:
--
@@ -3057,30 +3051,12 @@
65 module order.pl 150
84 action stock_assembly 203
85 menu 1 204
-86 module ic.pl 205
-86 action search 610
-86 searchitems all 611
-87 module ic.pl 612
-87 action search 206
-87 searchitems part 210
88 module ic.pl 211
88 action requirements 212
-89 action search 213
-89 module ic.pl 214
-89 searchitems service 215
-90 action search 216
-90 module ic.pl 217
-90 searchitems labor 218
91 module pe.pl 221
91 action search 220
92 module pe.pl 224
92 action search 223
-93 action search 226
-93 module ic.pl 227
-93 searchitems assembly 228
-94 action search 229
-94 module ic.pl 230
-94 searchitems component 231
95 menu 1 232
96 module pe.pl 233
96 action translation 234
@@ -3209,6 +3185,7 @@
155 module am.pl 384
154 action add_sic 385
155 action list_sic 386
+86 action search_screen 610
156 menu 1 387
157 module am.pl 388
158 module am.pl 389
@@ -3517,9 +3494,9 @@
58 oe_class_id 1 47
64 oe_class_id 1 48
65 oe_class_id 2 49
+86 module goods.pl 205
\.
-
--
CREATE TABLE menu_acl (
Added: trunk/sql/modules/Goods.sql
===================================================================
--- trunk/sql/modules/Goods.sql (rev 0)
+++ trunk/sql/modules/Goods.sql 2012-12-29 09:26:11 UTC (rev 5435)
@@ -0,0 +1,128 @@
+BEGIN;
+
+DROP TYPE IF EXISTS goods_search_result CASCADE;
+
+CREATE TYPE goods_search_result AS (
+ invnumber text,
+ trans_id int,
+ ordnumber text,
+ ord_id int,
+ quonumber text,
+ partnumber text,
+ id int,
+ description text,
+ onhand numeric,
+ qty numeric,
+ unit varchar,
+ price_updated date,
+ partsgroup text,
+ listprice numeric,
+ sellprice numeric,
+ lastcost numeric,
+ avgcost numeric,
+ linetotal numeric,
+ markup numeric,
+ bin text,
+ rop numeric,
+ weight numeric,
+ notes text,
+ image text,
+ drawing text,
+ microfiche text,
+ make text,
+ model text,
+ curr char(3),
+ serialnumber text,
+ module text
+);
+
+CREATE OR REPLACE FUNCTION goods__search
+(in_partnumber text, in_description text,
+ in_partsgroup_id int, in_serial_number text, in_make text,
+ in_model text, in_drawing text, in_microfiche text,
+ in_status text, in_date_from date, in_date_to date,
+ in_sales_invoices bool, in_purchase_invoices bool,
+ in_sales_orders bool, in_purchase_orders bool, in_quotations bool,
+ in_rfqs bool)
+RETURNS SETOF goods_search_result
+LANGUAGE PLPGSQL STABLE AS $$
+BEGIN
+
+-- Trying a CTE here to cut down on left joins.
+RETURN QUERY
+ WITH orders (invnumber, id, ordnumber, quonumber, qty, sellprice,
+ serialnumber, recordtype, oe_class_id, parts_id, curr) as
+ (SELECT a.invnumber, a.id, null::text, null::text, i.qty, i.sellprice,
+ i.serialnumber, a.recordtype, null::int, i.parts_id, a.curr
+ FROM (SELECT id, invnumber, 'is'::text as recordtype, transdate, curr
+ FROM ar WHERE in_sales_invoices
+ UNION
+ SELECT id, invnumber, 'ir'::text as recordtype, transdate, curr
+ FROM ap WHERE in_purchase_invoices
+ ) a
+ JOIN invoice i ON i.trans_id = a.id
+ WHERE (in_date_from is null or in_date_from >= a.transdate) and
+ (in_date_to is null or in_date_to <= a.transdate)
+ UNION
+ SELECT null::text, o.id, o.ordnumber, o.quonumber, i.qty, i.sellprice,
+ i.serialnumber, 'oe', oe_class_id, i.parts_id, o.curr
+ FROM oe o
+ JOIN orderitems i ON o.id = i.trans_id
+ WHERE (o.oe_class_id = 1 AND in_sales_orders)
+ OR (o.oe_class_id = 2 AND in_purchase_orders)
+ OR (o.oe_class_id = 3 AND in_quotations)
+ OR (o.oe_class_id = 4 AND in_rfqs)
+ AND((in_date_from is null or in_date_from >= o.transdate) and
+ (in_date_to is null or in_date_to <= o.transdate))
+ )
+ SELECT o.invnumber,
+ CASE WHEN o.recordtype in ('ir', 'is') THEN o.id ELSE NULL END,
+ o.ordnumber,
+ CASE WHEN o.recordtype = 'oe' THEN o.id ELSE NULL END,
+ o.quonumber, p.partnumber,
+ p.id, p.description, p.onhand, o.qty, p.unit, p.priceupdate,
+ pg.partsgroup,
+ p.listprice, p.sellprice, p.lastcost, p.avgcost,
+ o.qty * o.sellprice as linetotal,
+ CASE WHEN p.lastcost = 0 THEN NULL
+ ELSE ((p.sellprice / p.lastcost) - 1) * 100
+ END as markup,
+ p.bin, p.rop, p.weight, p.notes, p.image, p.drawing, p.microfiche,
+ m.make, m.model, o.curr,
+ o.serialnumber, o.recordtype
+ FROM parts p
+ LEFT JOIN orders o ON o.parts_id = p.id
+ LEFT JOIN makemodel m ON m.parts_id = p.id
+ LEFT JOIN partsgroup pg ON p.partsgroup_id = pg.id
+ WHERE (in_partnumber is null or p.partnumber ilike in_partnumber || '%')
+ AND (in_description is null
+ or p.description @@ plainto_tsquery(in_description))
+ AND (in_partsgroup_id is null
+ or p.partsgroup_id = in_partsgroup_id )
+ AND (in_serial_number is null
+ or o.serialnumber = in_serial_number)
+ AND (in_make is null or m.make ilike in_make || '%')
+ AND (in_model is null or m.model ilike in_model || '%')
+ AND (in_drawing IS NULL OR p.drawing ilike in_drawing || '%')
+ AND (in_microfiche IS NULL
+ OR p.microfiche ilike in_microfiche || '%')
+ AND ((in_status = 'active' and not p.obsolete)
+ OR (in_status = 'obsolete' and p.obsolete)
+ OR (in_status = 'short' and p.onhand <= p.rop)
+ OR (in_status = 'unused'
+ AND NOT EXISTS (select 1 FROM invoice
+ WHERE parts_id = p.id
+ UNION
+ SELECT 1 FROM orderitems
+ WHERE parts_id = p.id)));
+END;
+$$;
+
+CREATE OR REPLACE FUNCTION partsgroups__list_all()
+RETURNS SETOF partsgroup LANGUAGE SQL STABLE AS $$
+
+SELECT * FROM partsgroup ORDER BY partsgroup;
+
+$$;
+
+COMMIT;
Modified: trunk/sql/modules/LOADORDER
===================================================================
--- trunk/sql/modules/LOADORDER 2012-12-27 15:15:05 UTC (rev 5434)
+++ trunk/sql/modules/LOADORDER 2012-12-29 09:26:11 UTC (rev 5435)
@@ -37,3 +37,4 @@
Payroll.sql
Transaction_Templates.sql
OrderEntry.sql
+Goods.sql
Modified: trunk/sql/modules/menu_rebuild.sql
===================================================================
--- trunk/sql/modules/menu_rebuild.sql 2012-12-27 15:15:05 UTC (rev 5434)
+++ trunk/sql/modules/menu_rebuild.sql 2012-12-29 09:26:11 UTC (rev 5435)
@@ -285,30 +285,12 @@
65 module order.pl 150
84 action stock_assembly 203
85 menu 1 204
-86 module ic.pl 205
-86 action search 610
-86 searchitems all 611
-87 module ic.pl 612
-87 action search 206
-87 searchitems part 210
88 module ic.pl 211
88 action requirements 212
-89 action search 213
-89 module ic.pl 214
-89 searchitems service 215
-90 action search 216
-90 module ic.pl 217
-90 searchitems labor 218
91 module pe.pl 221
91 action search 220
92 module pe.pl 224
92 action search 223
-93 action search 226
-93 module ic.pl 227
-93 searchitems assembly 228
-94 action search 229
-94 module ic.pl 230
-94 searchitems component 231
95 menu 1 232
96 module pe.pl 233
96 action translation 234
@@ -437,6 +419,7 @@
155 module am.pl 384
154 action add_sic 385
155 action list_sic 386
+86 action search_screen 610
156 menu 1 387
157 module am.pl 388
158 module am.pl 389
@@ -745,6 +728,7 @@
58 oe_class_id 1 47
64 oe_class_id 1 48
65 oe_class_id 2 49
+86 module goods.pl 205
\.
@@ -805,24 +789,9 @@
71 Quotations 70 1
72 RFQs 70 2
74 Journal Entry 73 1
-78 Add Part 77 1
-79 Add Service 77 2
-80 Add Assembly 77 3
-81 Add Overhead 77 4
-82 Add Group 77 5
-83 Add Pricegroup 77 6
-84 Stock Assembly 77 7
-85 Reports 77 8
-86 All Items 85 1
-87 Parts 85 2
88 Requirements 85 3
-89 Services 85 4
-90 Labor 85 5
91 Groups 85 6
92 Pricegroups 85 7
-93 Assembly 85 8
-94 Components 85 9
-95 Translations 77 9
96 Description 95 1
97 Partsgroup 95 2
99 Add Project 98 1
@@ -911,6 +880,10 @@
111 Trial Balance 109 1
112 Income Statement 109 2
60 Combine 50 5
+78 Add Part 77 2
+79 Add Service 77 3
+80 Add Assembly 77 4
+86 Search 77 1
201 Payments 200 1
202 Reverse Payment 200 2
210 Drafts 205 2
@@ -981,6 +954,12 @@
116 Batch Printing 0 20
9 Outstanding 4 1
10 Outstanding 24 1
+81 Add Overhead 77 5
+82 Add Group 77 6
+83 Add Pricegroup 77 7
+84 Stock Assembly 77 8
+95 Translations 77 10
+85 Reports 77 9
\.
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.