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->db_parse_numeric(sth => $sth, hashref => $ref);
            $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') ) {
            $form->db_parse_numeric(sth => $sth, hashref => $ref);
            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') ) {
            $form->db_parse_numeric(sth => $sth, hashref => $ref);
            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