path: root/LedgerSMB/IC.pm

#=====================================================================
# LedgerSMB
# Small Medium Business Accounting software
# http://www.ledgersmb.org/
#
# Copyright (C) 2006
# This work contains copyrighted information from a number of sources all used
# with permission.
#
# This file contains source code included with or based on SQL-Ledger which
# is Copyright Dieter Simader and DWS Systems Inc. 2000-2005 and licensed
# under the GNU General Public License version 2 or, at your option, any later
# version.  For a full list including contact information of contributors,
# maintainers, and copyright holders, see the CONTRIBUTORS file.
#
# Original Copyright Notice from SQL-Ledger 2.6.17 (before the fork):
# Copyright (C) 2000
#
#  Author: DWS Systems Inc.
#     Web: http://www.sql-ledger.org
#
#  Contributors:
#
#======================================================================
#
# This file has NOT undergone whitespace cleanup.
#
#======================================================================
#
# Inventory Control backend
#
#======================================================================

package IC;

sub get_part {
    my ( $self, $myconfig, $form ) = @_;

    # connect to db
    my $dbh = $form->{dbh};
    my $i;

    my $query = qq|
           SELECT p.*, c1.accno AS inventory_accno, 
                  c1.description AS inventory_description, 
                  c2.accno AS income_accno, 
                  c2.description AS income_description,
                  c3.accno AS expense_accno, 
                  c3.description AS expense_description, pg.partsgroup
             FROM parts p
        LEFT JOIN chart c1 ON (p.inventory_accno_id = c1.id)
        LEFT JOIN chart c2 ON (p.income_accno_id = c2.id)
        LEFT JOIN chart c3 ON (p.expense_accno_id = c3.id)
        LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
            WHERE p.id = ?|;
    my $sth = $dbh->prepare($query);
    $sth->execute( $form->{id} ) || $form->dberror($query);
    my $ref = $sth->fetchrow_hashref(NAME_lc);
    $form->db_parse_numeric(sth => $sth, hashref => $ref);

    # copy to $form variables
    for ( keys %$ref ) { $form->{$_} = $ref->{$_} }
    $sth->finish;

    # part, service item or labor
    $form->{item} = ( $form->{inventory_accno_id} ) ? 'part' : 'service';
    $form->{item} = 'labor' if !$form->{income_accno_id};

    if ( $form->{assembly} ) {
        $form->{item} = 'assembly';

        # retrieve assembly items
        $query = qq|
               SELECT p.id, p.partnumber, p.description,
                      p.sellprice, p.weight, a.qty, a.bom, a.adj,
                      p.unit, p.lastcost, p.listprice, 
                      pg.partsgroup, p.assembly, p.partsgroup_id
                 FROM parts p
                 JOIN assembly a ON (a.parts_id = p.id)
            LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
                WHERE a.id = ?|;

        $sth = $dbh->prepare($query);
        $sth->execute( $form->{id} ) || $form->dberror($query);

        $form->{assembly_rows} = 0;
        while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
            $form->{assembly_rows}++;
            foreach my $key ( keys %{$ref} ) {
                $form->{"${key}_$form->{assembly_rows}"} = $ref->{$key};
            }
        }
        $sth->finish;

    }

    # setup accno hash for <option checked>
    # {amount} is used in create_links
    for (qw(inventory income expense)) {
        $form->{amount}{"IC_$_"} = {
            accno       => $form->{"${_}_accno"},
            description => $form->{"${_}_description"}
        };
    }

    if ( $form->{item} =~ /(part|assembly)/ ) {

        if ( $form->{makemodel} ne "" ) {
            $query = qq|
                SELECT make, model
                  FROM makemodel
                 WHERE parts_id = ?|;

            $sth = $dbh->prepare($query);
            $sth->execute( $form->{id} ) || $form->dberror($query);

            while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
                push @{ $form->{makemodels} }, $ref;
            }
            $sth->finish;
        }
    }

    # now get accno for taxes
    $query = qq|
        SELECT c.accno FROM chart c, partstax pt
         WHERE pt.chart_id = c.id AND pt.parts_id = ?|;

    $sth = $dbh->prepare($query);
    $sth->execute( $form->{id} ) || $form->dberror($query);

    while ( ($key) = $sth->fetchrow_array ) {
        $form->{amount}{$key} = $key;
    }

    $sth->finish;

    my $id = $dbh->quote( $form->{id} );

    # is it an orphan
    $query = qq|
        SELECT parts_id FROM invoice WHERE parts_id = $id
        UNION
        SELECT parts_id FROM orderitems WHERE parts_id = $id
        UNION
        SELECT parts_id FROM assembly WHERE parts_id = $id
        UNION
        SELECT parts_id FROM jcitems WHERE parts_id = $id|;
    ( $form->{orphaned} ) = $dbh->selectrow_array($query);
    $form->{orphaned} = !$form->{orphaned};

    $form->{orphaned} = 0 if $form->{project_id};

    if ( $form->{item} eq 'assembly' ) {
        if ( $form->{orphaned} ) {
            $form->{orphaned} = !$form->{onhand};
        }
    }

    if ( $form->{item} =~ /(part|service)/ ) {

        # get vendors
        $query = qq|
              SELECT v.id, v.name, pv.partnumber,
                     pv.lastcost, pv.leadtime, 
                     pv.curr AS vendorcurr
                FROM partsvendor pv
                JOIN vendor v ON (v.id = pv.vendor_id)
               WHERE pv.parts_id = ?
            ORDER BY 2|;

        $sth = $dbh->prepare($query);
        $sth->execute( $form->{id} ) || $form->dberror($query);

        while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
            push @{ $form->{vendormatrix} }, $ref;
        }
        $sth->finish;
    }

    # get matrix
    if ( $form->{item} ne 'labor' ) {
        $query = qq|
               SELECT pc.pricebreak, pc.sellprice AS customerprice,
                      pc.curr AS customercurr, pc.validfrom, 
                      pc.validto, c.name, c.id AS cid, 
                      g.pricegroup, g.id AS gid
                 FROM partscustomer pc
            LEFT JOIN customer c ON (c.id = pc.customer_id)
            LEFT JOIN pricegroup g ON (g.id = pc.pricegroup_id)
                WHERE pc.parts_id = ?
             ORDER BY c.name, g.pricegroup, pc.pricebreak|;
        $sth = $dbh->prepare($query);
        $sth->execute( $form->{id} ) || $form->dberror($query);

        while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
            push @{ $form->{customermatrix} }, $ref;
        }
        $sth->finish;
    }

    $form->run_custom_queries( 'parts', 'SELECT' );

}

sub save {
    my ( $self, $myconfig, $form ) = @_;
    $form->{partnumber} =
      $form->update_defaults( $myconfig, "partnumber", $dbh )
      if !$form->{partnumber};

    ( $form->{inventory_accno} ) = split( /--/, $form->{IC_inventory} );
    ( $form->{expense_accno} )   = split( /--/, $form->{IC_expense} );
    ( $form->{income_accno} )    = split( /--/, $form->{IC_income} );

    my $dbh = $form->{dbh};

    # undo amount formatting
    for (qw(rop weight listprice sellprice lastcost stock)) {
        $form->{$_} = $form->parse_amount( $myconfig, $form->{$_} );
    }

    $form->{makemodel} =
      ( ( $form->{make_1} ) || ( $form->{model_1} ) ) ? 1 : 0;

    $form->{assembly} = ( $form->{item} eq 'assembly' ) ? 1 : 0;
    for (qw(alternate obsolete onhand)) { $form->{$_} *= 1 }

    my $query;
    my $sth;
    my $i;
    my $null;
    my $vendor_id;
    my $customer_id;

    if ( $form->{id} ) {

        # get old price
        $query = qq|
            SELECT id, listprice, sellprice, lastcost, weight, 
                   project_id
              FROM parts
             WHERE id = ?|;
        my $sth = $dbh->prepare($query);
        $sth->execute( $form->{id} );
        my ( $id, $listprice, $sellprice, $lastcost, $weight, $project_id ) =
          $sth->fetchrow_array();

        if ($id) {

            if ( !$project_id ) {

                # if item is part of an assembly
                # adjust all assemblies
                $query = qq|
                    SELECT id, qty, adj
                      FROM assembly
                     WHERE parts_id = ?|;
                $sth = $dbh->prepare($query);
                $sth->execute( $form->{id} )
                  || $form->dberror($query);
                while ( my ( $id, $qty, $adj ) = $sth->fetchrow_array ) {

                    &update_assembly(
                        $dbh,           $form,
                        $id,            $qty,
                        $adj,           $listprice * 1,
                        $sellprice * 1, $lastcost * 1,
                        $weight * 1
                    );
                }
                $sth->finish;
            }

            if ( $form->{item} =~ /(part|service)/ ) {

                # delete partsvendor records
                $query = qq|
                    DELETE FROM partsvendor
                          WHERE parts_id = ?|;
                $sth = $dbh->prepare($query);
                $sth->execute( $form->{id} )
                  || $form->dberror($query);
            }

            if ( $form->{item} !~ /(service|labor)/ ) {

                # delete makemodel records
                $query = qq|
                    DELETE FROM makemodel
                          WHERE parts_id = ?|;
                $sth = $dbh->prepare($query);
                $sth->execute( $form->{id} )
                  || $form->dberror($query);
            }

            if ( $form->{item} eq 'assembly' ) {

                if ( $form->{onhand} ) {
                    &adjust_inventory( $dbh, $form, $form->{id},
                        $form->{onhand} * -1 );
                }

                if ( $form->{orphaned} ) {

                    # delete assembly records
                    $query = qq|
                        DELETE FROM assembly
                              WHERE id = ?|;
                    $sth = $dbh->prepare($query);
                    $sth->execute( $form->{id} )
                      || $form->dberror($query);
                }
                else {

                    for $i ( 1 .. $form->{assembly_rows} - 1 ) {

                        # update BOM, A only
                        for (qw(bom adj)) {
                            $form->{"${_}_$i"} *= 1;
                        }

                        $query = qq|
                            UPDATE assembly
                               SET bom = ?,
                                   adj = ?
                             WHERE id = ?
                                   AND parts_id = ?|;
                        $sth = $dbh->prepare($query);
                        $sth->execute(
                            $form->{"bom_$i"}, $form->{"adj_$i"},
                            $form->{id},       $form->{"id_$i"}
                        ) || $form->dberror($query);
                    }
                }

                $form->{onhand} += $form->{stock};

            }

            # delete tax records
            $query = qq|DELETE FROM partstax WHERE parts_id = ?|;

            $sth = $dbh->prepare($query);
            $sth->execute( $form->{id} ) || $form->dberror($query);

            # delete matrix
            $query = qq|
                DELETE FROM partscustomer 
                      WHERE parts_id = ?|;

            $sth = $dbh->prepare($query);
            $sth->execute( $form->{id} ) || $form->dberror($query);
        }
        else {
            $query = qq|INSERT INTO parts (id) VALUES (?)|;
            $sth   = $dbh->prepare($query);
            $sth->execute( $form->{id} ) || $form->dberror($query);
        }

    }

    if ( !$form->{id} ) {
        my $uid = localtime;
        $uid .= "$$";

        $query = qq|INSERT INTO parts (partnumber) VALUES ('$uid')|;
        $dbh->do($query) || $form->dberror($query);

        $query = qq|SELECT id FROM parts WHERE partnumber = '$uid'|;
        $sth   = $dbh->prepare($query);
        $sth->execute || $form->dberror($query);
        ( $form->{id} ) = $sth->fetchrow_array;
        $sth->finish;

        $form->{orphaned} = 1;
        $form->{onhand}   = ( $form->{stock} * 1 )
          if $form->{item} eq 'assembly';
    }

    my $partsgroup_id;
    ( $null, $partsgroup_id ) = split /--/, $form->{partsgroup};
    $partsgroup_id *= 1;


    if ( !$form->{priceupdate} ) {
        $form->{priceupdate} = 'now';
    }
    $query = qq|
        UPDATE parts 
           SET partnumber = ?,
               description = ?,
               makemodel = ?,
               alternate = ?,
               assembly = ?,
               listprice = ?,
               sellprice = ?,
               lastcost = ?,
               weight = ?,
               priceupdate = ?,
               unit = ?,
               notes = ?,
               rop = ?,
               bin = ?,
               inventory_accno_id = (SELECT id FROM chart
                                      WHERE accno = ?),
               income_accno_id = (SELECT id FROM chart
                                   WHERE accno = ?),
               expense_accno_id = (SELECT id FROM chart
                                    WHERE accno = ?),
               obsolete = ?,
               image = ?,
               drawing = ?,
               microfiche = ?,
               partsgroup_id = ?
         WHERE id = ?|;
    $sth = $dbh->prepare($query);
    $sth->execute(
        $form->{partnumber},      $form->{description},
        $form->{makemodel},       $form->{alternate},
        $form->{assembly},        $form->{listprice},
        $form->{sellprice},       $form->{lastcost},
        $form->{weight},          $form->{priceupdate},
        $form->{unit},            $form->{notes},
        $form->{rop},             $form->{bin},
        $form->{inventory_accno}, $form->{income_accno},
        $form->{expense_accno},   $form->{obsolete},
        $form->{image},           $form->{drawing},
        $form->{microfiche},      $partsgroup_id,
        $form->{id}
    ) || $form->dberror($query);

    # insert makemodel records
    if ( $form->{item} =~ /(part|assembly)/ ) {
        $query = qq|
            INSERT INTO makemodel (parts_id, make, model)
                 VALUES (?, ?, ?)|;
        $sth = $dbh->prepare($query) || $form->dberror($query);
        for $i ( 1 .. $form->{makemodel_rows} ) {
            if (   ( $form->{"make_$i"} ne "" )
                || ( $form->{"model_$i"} ne "" ) )
            {
                $sth->execute( $form->{id}, $form->{"make_$i"},
                    $form->{"model_$i"} )
                  || $form->dberror($query);
            }
        }
    }

    # insert taxes
    $query = qq|
        INSERT INTO partstax (parts_id, chart_id)
             VALUES (?, (SELECT id FROM chart WHERE accno = ?))|;
    $sth = $dbh->prepare($query);
    for ( split / /, $form->{taxaccounts} ) {
        if ( $form->{"IC_tax_$_"} ) {
            $sth->execute( $form->{id}, $_ )
              || $form->dberror($query);
        }
    }

    @a = localtime;
    $a[5] += 1900;
    $a[4]++;
    $a[4] = substr( "0$a[4]", -2 );
    $a[3] = substr( "0$a[3]", -2 );
    my $shippingdate = "$a[5]$a[4]$a[3]";

    ( $form->{employee}, $form->{employee_id} ) = $form->get_employee($dbh);

    # add assembly records
    if ( $form->{item} eq 'assembly' && !$project_id ) {

        if ( $form->{orphaned} ) {
            $query = qq|
                INSERT INTO assembly 
                            (id, parts_id, qty, bom, adj)
                     VALUES (?, ?, ?, ?, ?)|;
            $sth = $dbh->prepare($query);
            for $i ( 1 .. $form->{assembly_rows} ) {
                $form->{"qty_$i"} =
                  $form->parse_amount( $myconfig, $form->{"qty_$i"} );
                if ( !$form->{"bom_$i"} ) {
                    $form->{"bom_$i"} = undef;
                }

                if ( $form->{"id_$i"} && $form->{"qty_$i"} ) {
                    $sth->execute(
                        $form->{id}, $form->{"id_$i"},
                        $form->{"qty_$i"}, $form->{"bom_$i"} || 0,
                        $form->{"adj_$i"}
                    ) || $form->dberror($query);
                }
            }
        }

        # adjust onhand for the parts
        if ( $form->{onhand} ) {
            &adjust_inventory( $dbh, $form, $form->{id}, $form->{onhand} );
        }
    }

    # add vendors
    if ( $form->{item} ne 'assembly' ) {
        $updparts{ $form->{id} } = 1;

        for $i ( 1 .. $form->{vendor_rows} ) {
            if ( ( $form->{"vendor_$i"} ne "" )
                && $form->{"lastcost_$i"} )
            {

                ( $null, $vendor_id ) = split /--/, $form->{"vendor_$i"};

                for (qw(lastcost leadtime)) {
                    $form->{"${_}_$i"} =
                      $form->parse_amount( $myconfig, $form->{"${_}_$i"} );
                }

                $query = qq|
                    INSERT INTO partsvendor 
                                (vendor_id, parts_id, 
                                partnumber, lastcost, 
                                leadtime, curr)
                         VALUES (?, ?, ?, ?, ?, ?)|;
                $sth = $dbh->prepare($query);
                $sth->execute(
                    $vendor_id,               $form->{id},
                    $form->{"partnumber_$i"}, $form->{"lastcost_$i"},
                    $form->{"leadtime_$i"},   $form->{"vendorcurr_$i"}
                ) || $form->dberror($query);
            }
        }
    }

    # add pricematrix
    for $i ( 1 .. $form->{customer_rows} ) {

        for (qw(pricebreak customerprice)) {
            $form->{"${_}_$i"} =
              $form->parse_amount( $myconfig, $form->{"${_}_$i"} );
        }

        if ( $form->{"customerprice_$i"} ) {

            ( $null, $customer_id ) = split /--/, $form->{"customer_$i"};
            $customer_id *= 1;

            ( $null, $pricegroup_id ) = split /--/, $form->{"pricegroup_$i"};

            my $validfrom;
            my $validto;
            $validfrom = $form->{"validfrom_$i"} if $form->{"validfrom_$i"};
            $validto   = $form->{"validto_$i"}   if $form->{"validto_$i"};
            $query     = qq|
                INSERT INTO partscustomer 
                            (parts_id, customer_id,
                            pricegroup_id, pricebreak, 
                            sellprice, curr,
                            validfrom, validto)
                         VALUES (?, ?, ?, ?, ?, ?, ?, ?)|;
            $sth = $dbh->prepare($query);
            $sth->execute(
                $form->{id},                 $customer_id,
                $pricegroup_id,              $form->{"pricebreak_$i"},
                $form->{"customerprice_$i"}, $form->{"customercurr_$i"},
                $validfrom,                  $validto
            ) || $form->dberror($query);
        }
    }

    my $rc = $dbh->commit;

    $form->run_custom_queries( 'parts', 'UPDATE' );
    $rc;

}

sub update_assembly {
    my (
        $dbh,       $form,      $id,       $qty, $adj,
        $listprice, $sellprice, $lastcost, $weight
    ) = @_;

    my $formlistprice = $form->{listprice};
    my $formsellprice = $form->{sellprice};

    if ( !$adj ) {
        $formlistprice = $listprice;
        $formsellprice = $sellprice;
    }

    my $query = qq|SELECT id, qty, adj FROM assembly WHERE parts_id = ?|;
    my $sth   = $dbh->prepare($query);
    $sth->execute($id) || $form->dberror($query);

    $form->{$id} = 1;    # Not sure what this is for...
                         # In fact, we don't seem to use it... Chris T

    while ( my ( $pid, $aqty, $aadj ) = $sth->fetchrow_array ) {
        &update_assembly(
            $dbh,       $form,      $pid,      $aqty * $qty, $aadj,
            $listprice, $sellprice, $lastcost, $weight
        ) if !$form->{$pid};
    }
    $sth->finish;
    $qty           = $dbh->quote($qty);
    $formlistprice = $dbh->quote( $formlistprice - $listprice );
    $formsellprice = $dbh->quote( $formsellprice - $sellprice );
    $formlastcost  = $dbh->quote( $form->{lastcost} - $lastcost );
    $weight        = $dbh->quote( $form->{weight} - $weight );
    $id            = $dbh->quote($id);

    $query = qq|
        UPDATE parts
           SET listprice = listprice +
               $qty * cast($formlistprice AS numeric),
               sellprice = sellprice +
               $qty * cast($formsellprice AS numeric),
               lastcost = lastcost +
               $qty * cast($formlastcost AS numeric),
               weight = weight +
               $qty * cast($weight AS numeric)
         WHERE id = $id|;
    $dbh->do($query) || $form->dberror($query);

    delete $form->{$id};

}

sub retrieve_assemblies {
    my ( $self, $myconfig, $form ) = @_;

    # connect to database
    my $dbh = $form->{dbh};

    my $where = '1 = 1';

    if ( $form->{partnumber} ne "" ) {
        my $partnumber = $dbh->quote( $form->like( lc $form->{partnumber} ) );
        $where .= " AND lower(p.partnumber) LIKE $partnumber";
    }

    if ( $form->{description} ne "" ) {
        my $description = $dbh->quote( $form->like( lc $form->{description} ) );
        $where .= " AND lower(p.description) LIKE $description";
    }
    $where .= qq| AND p.obsolete = '0'
        AND p.project_id IS NULL|;

    my %ordinal = (
        'partnumber'  => 2,
        'description' => 3,
        'bin'         => 4
    );

    my @a = qw(partnumber description bin);
    my $sortorder = $form->sort_order( \@a, \%ordinal );

    # retrieve assembly items
    my $query = qq|
          SELECT p.id, p.partnumber, p.description, p.bin, p.onhand, 
                 p.rop
            FROM parts p
           WHERE $where
                 AND p.assembly = '1'
         ORDER BY $sortorder|;

    my $sth = $dbh->prepare($query);
    $sth->execute || $form->dberror($query);

    $query = qq|
          SELECT sum(p.inventory_accno_id), p.assembly 
            FROM parts p
            JOIN assembly a ON (a.parts_id = p.id)
           WHERE a.id = ?
        GROUP BY p.assembly|;
    my $svh = $dbh->prepare($query) || $form->dberror($query);

    my $inh;
    if ( $form->{checkinventory} ) {
        $query = qq|
            SELECT p.id, p.onhand, a.qty
              FROM parts p
              JOIN assembly a ON (a.parts_id = p.id)
             WHERE (p.inventory_accno_id > 0 OR p.assembly)
                   AND p.income_accno_id > 0 AND a.id = ?|;
        $inh = $dbh->prepare($query) || $form->dberror($query);
    }

    my %available = ();
    my %required;
    my $ref;
    my $aref;
    my $stock;
    my $howmany;
    my $ok;

    while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
        $svh->execute( $ref->{id} );
        ( $ref->{inventory}, $ref->{assembly} ) = $svh->fetchrow_array;
        $svh->finish;

        if ( $ref->{inventory} || $ref->{assembly} ) {
            $ok = 1;
            if ( $form->{checkinventory} ) {
                $inh->execute( $ref->{id} )
                  || $form->dberror($query);
                $ok       = 0;
                %required = ();

                while ( $aref = $inh->fetchrow_hashref(NAME_lc) ) {

                    $available{ $aref->{id} } =
                      ( exists $available{ $aref->{id} } )
                      ? $available{ $aref->{id} }
                      : $aref->{onhand};
                    $required{ $aref->{id} } = $aref->{qty};

                    if ( $available{ $aref->{id} } >= $aref->{qty} ) {

                        $howmany =
                          ( $aref->{qty} )
                          ? int $available{ $aref->{id} } / $aref->{qty}
                          : 1;
                        if ($stock) {
                            $stock =
                              ( $stock > $howmany )
                              ? $howmany
                              : $stock;
                        }
                        else {
                            $stock = $howmany;
                        }
                        $ok = 1;

                        $available{ $aref->{id} } -= $aref->{qty} * $stock;

                    }
                    else {
                        $ok = 0;
                        for ( keys %required ) {
                            $available{$_} += $required{$_} * $stock;
                        }
                        $stock = 0;
                        last;
                    }
                }
                $inh->finish;
                $ref->{stock} = $stock;

            }
            push @{ $form->{assembly_items} }, $ref if $ok;
        }
    }
    $sth->finish;

    $dbh->commit;

}

sub restock_assemblies {
    my ( $self, $myconfig, $form ) = @_;

    # connect to database
    my $dbh = $form->{dbh};

    for my $i ( 1 .. $form->{rowcount} ) {
        $form->{"qty_$i"} = $form->parse_amount( $myconfig, $form->{"qty_$i"} );

        if ( $form->{"qty_$i"} ) {
            &adjust_inventory( $dbh, $form, $form->{"id_$i"},
                $form->{"qty_$i"} );
        }

    }

    my $rc = $dbh->commit;

    $rc;

}

sub adjust_inventory {

    # Private method.  Do not commit transaction at end of function...
    my ( $dbh, $form, $id, $qty ) = @_;

    my $query = qq|
        SELECT p.id, p.inventory_accno_id, p.assembly, a.qty
          FROM parts p
          JOIN assembly a ON (a.parts_id = p.id)
         WHERE a.id = ?|;
    my $sth = $dbh->prepare($query);
    $sth->execute($id) || $form->dberror($query);

    while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {

        # is it a service item then loop
        if ( !$ref->{inventory_accno_id} ) {
            next if !$ref->{assembly};
        }

        # adjust parts onhand
        $form->update_balance(
            $dbh, "parts", "onhand",
            qq|id = $ref->{id}|,
            $qty * $ref->{qty} * -1
        );
    }

    $sth->finish;

    # update assembly
    $form->update_balance( $dbh, "parts", "onhand", qq|id = $id|, $qty );

}

sub delete {
    my ( $self, $myconfig, $form ) = @_;

    my $dbh = $form->{dbh};

    my $query;

    $query = qq|DELETE FROM parts WHERE id = ?|;
    $sth   = $dbh->prepare($query);
    $sth->execute( $form->{id} ) || $form->dberror($query);

    $query = qq|DELETE FROM partstax WHERE parts_id = ?|;
    $sth   = $dbh->prepare($query);
    $sth->execute( $form->{id} ) || $form->dberror($query);

    if ( $form->{item} ne 'assembly' ) {
        $query = qq|DELETE FROM partsvendor WHERE parts_id = ?|;
        $sth   = $dbh->prepare($query);
        $sth->execute( $form->{id} ) || $form->dberror($query);
    }

    # check if it is a part, assembly or service
    if ( $form->{item} ne 'service' ) {
        $query = qq|DELETE FROM makemodel WHERE parts_id = ?|;
        $sth   = $dbh->prepare($query);
        $sth->execute( $form->{id} ) || $form->dberror($query);
    }

    if ( $form->{item} eq 'assembly' ) {
        $query = qq|DELETE FROM assembly WHERE id = ?|;
        $sth   = $dbh->prepare($query);
        $sth->execute( $form->{id} ) || $form->dberror($query);
    }

    $query = qq|DELETE FROM inventory WHERE parts_id = ?|;
    $sth   = $dbh->prepare($query);
    $sth->execute( $form->{id} ) || $form->dberror($query);

    $query = qq|DELETE FROM partscustomer WHERE parts_id = ?|;
    $sth   = $dbh->prepare($query);
    $sth->execute( $form->{id} ) || $form->dberror($query);

    $query = qq|DELETE FROM translation WHERE trans_id = ?|;
    $sth   = $dbh->prepare($query);
    $sth->execute( $form->{id} ) || $form->dberror($query);

    # commit
    my $rc = $dbh->commit;

    $rc;

}

sub assembly_item {
    my ( $self, $myconfig, $form ) = @_;
    my $dbh = $form->{dbh};

    my $i = $form->{assembly_rows};
    my $var;
    my $null;
    my $where = "p.obsolete = '0'";

    if ( $form->{"partnumber_$i"} ne "" ) {
        $var = $dbh->quote( $form->like( lc $form->{"partnumber_$i"} ) );
        $where .= " AND lower(p.partnumber) LIKE $var";
    }
    if ( $form->{"description_$i"} ne "" ) {
        $var = $dbh->quote( $form->like( lc $form->{"description_$i"} ) );
        $where .= " AND lower(p.description) LIKE $var";
    }
    if ( $form->{"partsgroup_$i"} ne "" ) {
        ( $null, $var ) = split /--/, $form->{"partsgroup_$i"};
        $var = $dbh->quote($var);
        $where .= qq| AND p.partsgroup_id = $var|;
    }

    if ( $form->{id} ) {
        $where .= " AND p.id != " . $dbh->quote( $form->{id} );
    }

    if ( $form->{"description_$i"} ne "" ) {
        $where .= " ORDER BY p.description";
    }
    else {
        $where .= " ORDER BY p.partnumber";
    }

    my $query = qq|
           SELECT p.id, p.partnumber, p.description, p.sellprice,
                  p.weight, p.onhand, p.unit, p.lastcost,
                  pg.partsgroup, p.partsgroup_id
             FROM parts p
        LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
            WHERE $where|;
    my $sth = $dbh->prepare($query);
    $sth->execute || $form->dberror($query);

    while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
        push @{ $form->{item_list} }, $ref;
    }

    $sth->finish;

}

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 'active' ) {
        $where .= " AND p.obsolete = '0'";
    }
    if ( $form->{itemstatus} eq 'obsolete' ) {
        $where .= " AND p.obsolete = '1'";
    }
    if ( $form->{itemstatus} eq 'onhand' ) {
        $where .= " AND p.onhand > 0";
    }
    if ( $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( \@a, \%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,
        c1.accno AS inventory, c2.accno AS income, c3.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 chart c1 ON (c1.id = p.inventory_accno_id)
        LEFT JOIN chart c2 ON (c2.id = p.income_accno_id)
        LEFT JOIN chart c3 ON (c3.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 chart c1 ON (c1.id = p.inventory_accno_id)
            LEFT JOIN chart c2 ON (c2.id = p.income_accno_id)
            LEFT JOIN chart c3 ON (c3.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( \@a, \%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.name, 
                e.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 vendor ct 
                              ON (a.vendor_id = ct.id)
                    LEFT JOIN partsgroup pg 
                              ON (p.partsgroup_id = pg.id)
                    LEFT JOIN employee e 
                              ON (a.employee_id = e.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 customer ct 
                              USING (entity_id)
                    LEFT JOIN partsgroup pg 
                              ON (p.partsgroup_id = pg.id)
                    LEFT JOIN employee 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.name, 
                e.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 customer ct 
                              USING (entity_id)
                    LEFT JOIN partsgroup pg 
                              ON (p.partsgroup_id = pg.id)
                    LEFT JOIN employee e 
                              ON (a.employee_id = e.id)
                    $makemodeljoin
                        WHERE $ordwhere 
                              AND a.entity_id 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.name,e.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 vendor ct 
                              ON (a.vendor_id = ct.id)
                    LEFT JOIN partsgroup pg 
                              ON (p.partsgroup_id = pg.id)
                    LEFT JOIN employee e 
                              ON (a.employee_id = e.id)
                    $makemodeljoin
                        WHERE $ordwhere
                              AND a.vendor_id > 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.name, 
                e.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 customer ct 
                              USING (entity_id)
                    LEFT JOIN partsgroup pg 
                              ON (p.partsgroup_id = pg.id)
                    LEFT JOIN employee e 
                              ON (a.employee_id = e.id)
                    $makemodeljoin
                        WHERE $quowhere
                              AND a.entity_id 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.name, e.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 vendor ct 
                              ON (a.vendor_id = ct.id)
                    LEFT JOIN partsgroup pg 
                              ON (p.partsgroup_id = pg.id)
                    LEFT JOIN employee e 
                              ON (a.employee_id = e.id)
                    $makemodeljoin
                        WHERE $quowhere
                              AND a.vendor_id > 0|;
            }

        }

        $query .= qq|
            ORDER BY $sortorder|;

    }

    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;

        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 chart c1 ON (c1.id = p.inventory_accno_id)
        LEFT JOIN chart c2 ON (c2.id = p.income_accno_id)
        LEFT JOIN chart c3 ON (c3.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};

    my $null;
    my $var;
    my $ref;

    my $where = qq|p.obsolete = '0'|;
    my $dwhere;

    for (qw(partnumber description)) {
        if ( $form->{$_} ne "" ) {
            $var = $dbh->quote( $form->like( lc $form->{$_} ) );
            $where .= qq| AND lower(p.$_) LIKE $var|;
        }
    }

    if ( $form->{partsgroup} ne "" ) {
        ( $null, $var ) = split /--/, $form->{partsgroup};
        $var = $dbh->quote($var);
        $where .= qq| AND p.partsgroup_id = $var|;
    }

    # connect to database

    my ( $transdatefrom, $transdateto );
    if ( $form->{year} ) {
        ( $transdatefrom, $transdateto ) =
          $form->from_to( $form->{year}, '01', 12 );

        $dwhere = qq| AND a.transdate >= '$transdatefrom'
            AND a.transdate <= '$transdateto'|;
    }

    $query = qq|
          SELECT p.id, p.partnumber, p.description, sum(i.qty) AS qty, 
                 p.onhand, extract(MONTH FROM a.transdate) AS month,
                 '0' AS so, '0' AS po
            FROM invoice i
            JOIN parts p ON (p.id = i.parts_id)
            JOIN ar a ON (a.id = i.trans_id)
           WHERE $where $dwhere AND p.inventory_accno_id > 0
        GROUP BY p.id, p.partnumber, p.description, p.onhand,
                 extract(MONTH FROM a.transdate)|;
    my $sth = $dbh->prepare($query);
    $sth->execute || $form->dberror($query);

    my %parts;
    while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
        $parts{ $ref->{id} } = $ref;
    }
    $sth->finish;

    my %ofld = ( customer => so, vendor => po );

    for (qw(customer vendor)) {
        $query = qq|
              SELECT p.id, p.partnumber, p.description, 
                     sum(qty) - sum(ship) AS $ofld{$_}, p.onhand,
                     0 AS month
                FROM orderitems i
                JOIN parts p ON (p.id = i.parts_id)
                JOIN oe a ON (a.id = i.trans_id)
               WHERE $where AND p.inventory_accno_id > 0
                     AND p.assembly = '0' AND a.closed = '0'
                     AND a.${_}_id > 0
            GROUP BY p.id, p.partnumber, p.description, p.onhand,
                     month|;
        $sth = $dbh->prepare($query);
        $sth->execute || $form->dberror($query);

        while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
            if ( exists $parts{ $ref->{id} }->{ $ofld{$_} } ) {
                $parts{ $ref->{id} }->{ $ofld{$_} } += $ref->{ $ofld{$_} };
            }
            else {
                $parts{ $ref->{id} } = $ref;
            }
        }
        $sth->finish;
    }

    # add assemblies from open sales orders
    $query = qq|
        SELECT DISTINCT a.id AS orderid, b.id, i.qty - i.ship AS qty
          FROM parts p
          JOIN assembly b ON (b.parts_id = p.id)
          JOIN orderitems i ON (i.parts_id = b.id)
          JOIN oe a ON (a.id = i.trans_id)
         WHERE $where
               AND (p.inventory_accno_id > 0 OR p.assembly = '1')
               AND a.closed = '0'|;
    $sth = $dbh->prepare($query);
    $sth->execute || $form->dberror($query);

    while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
        &requirements_assembly( $dbh, $form, \%parts, $ref->{id}, $ref->{qty},
            $where )
          if $ref->{qty};
    }
    $sth->finish;

    $dbh->commit;

    for (
        sort { $parts{$a}->{ $form->{sort} } cmp $parts{$b}->{ $form->{sort} } }
        keys %parts
      )
    {
        push @{ $form->{parts} }, $parts{$_};
    }

}

sub requirements_assembly {
    my ( $dbh, $form, $parts, $id, $qty, $where ) = @_;

    # assemblies
    my $query = qq|
        SELECT p.id, p.partnumber, p.description, a.qty * ? AS so, 
               p.onhand, p.assembly, p.partsgroup_id
          FROM assembly a 
          JOIN parts p ON (p.id = a.parts_id)
         WHERE $where AND a.id = ? AND p.inventory_accno_id > 0
         
         UNION
      
        SELECT p.id, p.partnumber, p.description, a.qty * ? AS so, 
               p.onhand, p.assembly, p.partsgroup_id
          FROM assembly a
          JOIN parts p ON (p.id = a.parts_id)
         WHERE a.id = ? AND p.assembly = '1'|;

    my $sth = $dbh->prepare($query);
    $sth->execute( $qty, $id, $qty, $id ) || $form->dberror($query);

    while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
        if ( $ref->{assembly} ) {
            &requirements_assembly( $dbh, $form, $parts, $ref->{id}, $ref->{so},
                $where );
            next;
        }

        if ( exists $parts->{ $ref->{id} }{so} ) {
            $parts->{ $ref->{id} }{so} += $ref->{so};
        }
        else {
            $parts->{ $ref->{id} } = $ref;
        }
    }
    $sth->finish;

}

sub create_links {
    my ( $self, $module, $myconfig, $form ) = @_;

    # connect to database
    my $dbh = $form->{dbh};

    my $ref;

    my $query = qq|
        SELECT accno, description, link
          FROM chart
         WHERE link LIKE ?
         ORDER BY accno|;
    my $sth = $dbh->prepare($query);
    $sth->execute("%$module%") || $form->dberror($query);

    while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
        foreach my $key ( split /:/, $ref->{link} ) {
            if ( $key =~ /$module/ ) {
                push @{ $form->{"${module}_links"}{$key} },
                  {
                    accno       => $ref->{accno},
                    description => $ref->{description}
                  };
            }
        }
    }
    $sth->finish;

    if ( $form->{item} ne 'assembly' ) {
        $query = qq|SELECT count(*) FROM vendor|;
        my ($count) = $dbh->selectrow_array($query);

        if ( $count < $myconfig->{vclimit} ) {
            $query = qq|SELECT v.id, e.name 
                FROM vendor v 
                join entity e on e.id = v.entity_id 
                ORDER BY e.name|;
            $sth   = $dbh->prepare($query);
            $sth->execute || $form->dberror($query);

            while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
                push @{ $form->{all_vendor} }, $ref;
            }
            $sth->finish;
        }
    }

    # pricegroups, customers
    $query = qq|SELECT count(*) FROM customer|;
    ($count) = $dbh->selectrow_array($query);

    if ( $count < $myconfig->{vclimit} ) {
        $query = qq|SELECT c.id, e.name 
            FROM customer c 
            join entity e on e.id = c.entity_id 
            ORDER BY e.name|;
        $sth   = $dbh->prepare($query);
        $sth->execute || $form->dberror($query);

        while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
            push @{ $form->{all_customer} }, $ref;
        }
        $sth->finish;
    }

    $query = qq|SELECT id, pricegroup FROM pricegroup ORDER BY pricegroup|;
    $sth   = $dbh->prepare($query);
    $sth->execute || $form->dberror($query);

    while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
        push @{ $form->{all_pricegroup} }, $ref;
    }
    $sth->finish;

    if ( $form->{id} ) {
        $query = qq|
            SELECT value FROM defaults 
             WHERE setting_key = 'weightunit'|;
        ( $form->{weightunit} ) = $dbh->selectrow_array($query);
        $query = qq|
            SELECT value FROM defaults 
             WHERE setting_key = 'curr'|;
        ( $form->{currencies} ) = $dbh->selectrow_array($query);

    }
    else {

        # Dieter: FIXME left joins not working
        $query = qq|
            SELECT (SELECT value FROM defaults 
                     WHERE setting_key = 'weightunit') 
                   AS weightunit,  current_date AS priceupdate,
                   (SELECT value FROM defaults 
                     WHERE setting_key = 'curr') AS currencies,
                   c1.accno AS inventory_accno, 
                   c1.description AS inventory_description,
                   c2.accno AS income_accno, 
                   c2.description AS income_description,
                   c3.accno AS expense_accno, 
                   c3.description AS expense_description
              FROM chart c1, chart c2, chart c3 
             WHERE c1.id IN (SELECT value FROM defaults 
             WHERE setting_key = 'inventory_accno_id')
                   AND c2.id IN (SELECT value FROM defaults
             WHERE setting_key = 'income_accno_id')
                   AND c3.id IN (SELECT value FROM defaults
                                  WHERE setting_key 
                                        = 'expense_accno_id')|;
        $sth = $dbh->prepare($query);
        $sth->execute || $form->dberror($query);

        $ref = $sth->fetchrow_hashref(NAME_lc);
        for (qw(weightunit priceupdate currencies)) {
            $form->{$_} = $ref->{$_};
        }

        # setup accno hash, {amount} is used in create_links
        for (qw(inventory income expense)) {
            $form->{amount}{"IC_$_"} = {
                accno       => $ref->{"${_}_accno"},
                description => $ref->{"${_}_description"}
            };
        }

        $sth->finish;
    }

    $dbh->commit;

}

sub get_warehouses {
    my ( $self, $myconfig, $form ) = @_;

    my $dbh = $form->{dbh};

    my $query = qq|SELECT id, description FROM warehouse|;

    my $sth = $dbh->prepare($query);
    $sth->execute || $form->dberror($query);

    while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
        push @{ $form->{all_warehouse} }, $ref;
    }
    $sth->finish;

    $dbh->commit;

}

1;