path: root/LedgerSMB/PE.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) 2003
#
#  Author: DWS Systems Inc.
#     Web: http://www.sql-ledger.org
#
#  Contributors:
#
#======================================================================
#
# This file has undergone whitespace cleanup.
#
#======================================================================
#
# Project module
# also used for partsgroups
#
#======================================================================

package PE;

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

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

    $form->{sort} = "projectnumber" unless $form->{sort};
    my @a       = ( $form->{sort} );
    my %ordinal = (
        projectnumber => 2,
        description   => 3,
        startdate     => 4,
        enddate       => 5,
    );
    my $sortorder = $form->sort_order( \@a, \%ordinal );

    my $query;
    my $where = "WHERE 1=1";

    $query = qq|
           SELECT pr.*, c.name 
             FROM project pr
        LEFT JOIN customer c ON (c.id = pr.customer_id)|;

    if ( $form->{type} eq 'job' ) {
        $where .= qq| AND pr.id NOT IN (SELECT DISTINCT id
                        FROM parts
                        WHERE project_id > 0)|;
    }

    my $var;
    if ( $form->{projectnumber} ne "" ) {
        $var = $dbh->quote( $form->like( lc $form->{projectnumber} ) );
        $where .= " AND lower(pr.projectnumber) LIKE $var";
    }
    if ( $form->{description} ne "" ) {
        $var = $dbh->quote( $form->like( lc $form->{description} ) );
        $where .= " AND lower(pr.description) LIKE $var";
    }

    ( $form->{startdatefrom}, $form->{startdateto} ) =
      $form->from_to( $form->{year}, $form->{month}, $form->{interval} )
      if $form->{year} && $form->{month};

    if ( $form->{startdatefrom} ) {
        $where .=
          " AND (pr.startdate IS NULL OR pr.startdate >= "
          . $dbh->quote( $form->{startdatefrom} ) . ")";
    }
    if ( $form->{startdateto} ) {
        $where .=
          " AND (pr.startdate IS NULL OR pr.startdate <= "
          . $dbh->quote( $form->{startdateto} ) . ")";
    }

    if ( $form->{status} eq 'orphaned' ) {
        $where .= qq| AND pr.id NOT IN (SELECT DISTINCT project_id
                                    FROM acc_trans
                    WHERE project_id > 0
                                 UNION
                                    SELECT DISTINCT project_id
                            FROM invoice
                    WHERE project_id > 0
                 UNION
                            SELECT DISTINCT project_id
                            FROM orderitems
                    WHERE project_id > 0
                 UNION
                            SELECT DISTINCT project_id
                            FROM jcitems
                    WHERE project_id > 0)
        |;

    }
    if ( $form->{status} eq 'active' ) {
        $where .= qq| 
            AND (pr.enddate IS NULL 
            OR pr.enddate >= current_date)|;
    }
    if ( $form->{status} eq 'inactive' ) {
        $where .= qq| AND pr.enddate <= current_date|;
    }

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

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

    my $i = 0;
    while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
        push @{ $form->{all_project} }, $ref;
        $i++;
    }

    $sth->finish;
    $dbh->commit;

    $i;

}

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

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

    my $query;
    my $sth;
    my $ref;
    my $where;

    if ( $form->{id} ) {

        $query = qq|
               SELECT pr.*, c.name AS customer
                 FROM project pr
            LEFT JOIN customer c ON (c.id = pr.customer_id)
                WHERE pr.id = ?|;
        $sth = $dbh->prepare($query);
        $sth->execute( $form->{id} ) || $form->dberror($query);

        $ref = $sth->fetchrow_hashref(NAME_lc);

        for ( keys %$ref ) { $form->{$_} = $ref->{$_} }

        $sth->finish;

        # check if it is orphaned
        $query = qq|
            SELECT count(*)
              FROM acc_trans
             WHERE project_id = ?
            UNION
            SELECT count(*)
              FROM invoice
             WHERE project_id = ?
            UNION
            SELECT count(*)
              FROM orderitems
             WHERE project_id = ?
            UNION
            SELECT count(*)
              FROM jcitems
             WHERE project_id = ?|;
        $sth = $dbh->prepare($query);
        $sth->execute( $form->{id}, $form->{id}, $form->{id}, $form->{id} )
          || $form->dberror($query);

        my $count;
        while ( ($count) = $sth->fetchrow_array ) {
            $form->{orphaned} += $count;
        }
        $sth->finish;
        $form->{orphaned} = !$form->{orphaned};
    }

    PE->get_customer( $myconfig, $form, $dbh );

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

    $dbh->commit;

}

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

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

    $form->{customer_id} ||= undef;

    $form->{projectnumber} =
      $form->update_defaults( $myconfig, "projectnumber", $dbh )
      unless $form->{projectnumber};
    my $enddate;
    my $startdate;
    $enddate   = $form->{enddate}   if $form->{enddate};
    $startdate = $form->{startdate} if $form->{startdate};

    if ( $form->{id} ) {

        $query = qq|
            UPDATE project
               SET projectnumber = ?,
                   description = ?,
                   startdate = ?,
                   enddate = ?,
                   customer_id = ?
             WHERE id = | . $dbh->quote( $form->{id} );
    }
    else {

        $query = qq|
            INSERT INTO project (projectnumber, description, 
                        startdate, enddate, customer_id)
                 VALUES (?, ?, ?, ?, ?)|;
    }
    $sth = $dbh->prepare($query);
    $sth->execute( $form->{projectnumber},
        $form->{description}, $startdate, $enddate, $form->{customer_id} )
      || $form->dberror($query);
    if (!$form->{id}){
        $query = "SELECT currval('id')";
        ($form->{id}) = $dbh->selectrow_array($query) || $form->dberror($query);
    }
    $form->run_custom_queries( 'project', 'UPDATE' );

    $dbh->commit;

}

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

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

    my $var;
    my $where = "1 = 1";

    if ( $form->{status} eq 'active' ) {
        $where = qq|
            (pr.enddate IS NULL OR pr.enddate >= current_date)
            AND pr.completed < pr.production|;
    }
    if ( $form->{status} eq 'inactive' ) {
        $where = qq|pr.completed = pr.production|;
    }

    if ( $form->{projectnumber} ) {
        $var = $dbh->quote( $form->like( lc $form->{projectnumber} ) );
        $where .= " AND lower(pr.projectnumber) LIKE $var";
    }

    if ( $form->{description} ) {
        $var = $dbh->quote( $form->like( lc $form->{description} ) );
        $where .= " AND lower(pr.description) LIKE $var";
    }

    $form->{sort} = "projectnumber" unless $form->{sort};
    my @a         = ( $form->{sort} );
    my %ordinal   = ( projectnumber => 2, description => 3 );
    my $sortorder = $form->sort_order( \@a, \%ordinal );

    my $query = qq|
           SELECT pr.*, p.partnumber
             FROM project pr
             JOIN parts p ON (p.id = pr.parts_id)
            WHERE $where
         ORDER BY $sortorder|;

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

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

    $query = qq|SELECT current_date|;
    ( $form->{stockingdate} ) = $dbh->selectrow_array($query)
      if !$form->{stockingdate};

    $dbh->commit;

}

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

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

    $form->{sort} = "projectnumber" unless $form->{sort};
    my @a         = ( $form->{sort} );
    my %ordinal   = ( projectnumber => 2, description => 3, startdate => 4 );
    my $sortorder = $form->sort_order( \@a, \%ordinal );

    my $query = qq|
           SELECT pr.*, p.partnumber, p.onhand, c.name
             FROM project pr
             JOIN parts p ON (p.id = pr.parts_id)
        LEFT JOIN customer c ON (c.id = pr.customer_id)
            WHERE 1=1|;

    if ( $form->{projectnumber} ne "" ) {
        $var = $dbh->quote( $form->like( lc $form->{projectnumber} ) );
        $query .= " AND lower(pr.projectnumber) LIKE $var";
    }
    if ( $form->{description} ne "" ) {
        $var = $dbh->quote( $form->like( lc $form->{description} ) );
        $query .= " AND lower(pr.description) LIKE $var";
    }

    ( $form->{startdatefrom}, $form->{startdateto} ) =
      $form->from_to( $form->{year}, $form->{month}, $form->{interval} )
      if $form->{year} && $form->{month};

    if ( $form->{startdatefrom} ) {
        $query .=
          " AND pr.startdate >= " . $dbh->quote( $form->{startdatefrom} );
    }
    if ( $form->{startdateto} ) {
        $query .= " AND pr.startdate <= " . $dbh->quote( $form->{startdateto} );
    }

    if ( $form->{status} eq 'active' ) {
        $query .= qq| AND NOT pr.production = pr.completed|;
    }
    if ( $form->{status} eq 'inactive' ) {
        $query .= qq| AND pr.production = pr.completed|;
    }
    if ( $form->{status} eq 'orphaned' ) {
        $query .= qq| 
            AND pr.completed = 0
            AND (pr.id NOT IN 
            (SELECT DISTINCT project_id
               FROM invoice
              WHERE project_id > 0
             UNION
             SELECT DISTINCT project_id
               FROM orderitems
              WHERE project_id > 0
             UNION
             SELECT DISTINCT project_id
               FROM jcitems
              WHERE project_id > 0)
             )|;
    }

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

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

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

    $sth->finish;

    $dbh->commit;

}

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

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

    my $query;
    my $sth;
    my $ref;

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

        $query = qq|
               SELECT pr.*, p.partnumber, 
                      p.description AS partdescription, p.unit, 
                      p.listprice, p.sellprice, p.priceupdate, 
                      p.weight, p.notes, p.bin, p.partsgroup_id,
                      ch.accno AS income_accno, 
                      ch.description AS income_description, 
                      pr.customer_id, c.name AS customer, 
                      pg.partsgroup
                 FROM project pr
            LEFT JOIN parts p ON (p.id = pr.parts_id)
            LEFT JOIN chart ch ON (ch.id = p.income_accno_id)
            LEFT JOIN customer c ON (c.id = pr.customer_id)
            LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
                WHERE pr.id = | . $dbh->quote( $form->{id} );
    }
    else {
        $query = qq|
            SELECT value, current_date AS startdate FROM defaults
             WHERE setting_key = 'weightunit'|;
    }

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

    $ref = $sth->fetchrow_hashref(NAME_lc);

    for ( keys %$ref ) { $form->{$_} = $ref->{$_} }

    $sth->finish;

    if ( $form->{id} ) {

        # check if it is orphaned
        $query = qq|
            SELECT count(*)
              FROM invoice
             WHERE project_id = ?
            UNION
            SELECT count(*)
              FROM orderitems
             WHERE project_id = ?
            UNION
            SELECT count(*)
              FROM jcitems
             WHERE project_id = ?|;
        $sth = $dbh->prepare($query);
        $sth->execute( $form->{id}, $form->{id}, $form->{id} )
          || $form->dberror($query);

        my $count;

        my $count;
        while ( ($count) = $sth->fetchrow_array ) {
            $form->{orphaned} += $count;
        }
        $sth->finish;

    }

    $form->{orphaned} = !$form->{orphaned};

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

    while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
        for ( split /:/, $ref->{link} ) {
            if (/IC/) {
                push @{ $form->{IC_links}{$_} },
                  {
                    accno       => $ref->{accno},
                    description => $ref->{description}
                  };
            }
        }
    }
    $sth->finish;

    if ( $form->{id} ) {
        $query = qq|
            SELECT ch.accno
              FROM parts p
              JOIN partstax pt ON (pt.parts_id = p.id)
              JOIN chart ch ON (pt.chart_id = ch.id)
             WHERE p.id = ?|;

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

        while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
            $form->{amount}{ $ref->{accno} } = $ref->{accno};
        }
        $sth->finish;
    }

    PE->get_customer( $myconfig, $form, $dbh );

    $dbh->commit;

}

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

    if ( !$dbh ) {
        $dbh = $form->{dbh};
    }

    my $query;
    my $sth;
    my $ref;

    if ( !$form->{startdate} ) {
        $query = qq|SELECT current_date|;
        ( $form->{startdate} ) = $dbh->selectrow_array($query);
    }

    my $where =
        qq|(startdate >= |
      . $dbh->quote( $form->{startdate} )
      . qq| OR startdate IS NULL OR enddate IS NULL)|;

    if ( $form->{enddate} ) {
        $where .=
            qq| AND (enddate >= |
          . $dbh->quote( $form->{enddate} )
          . qq| OR enddate IS NULL)|;
    }
    else {
        $where .= qq| AND (enddate >= current_date OR enddate IS NULL)|;
    }

    $query = qq|
        SELECT count(*)
          FROM customer
         WHERE $where|;
    my ($count) = $dbh->selectrow_array($query);

    if ( $count < $myconfig->{vclimit} ) {
        $query = qq|
            SELECT id, name
              FROM customer
             WHERE $where|;

        if ( $form->{customer_id} ) {
            $query .= qq|
                UNION 
                SELECT id,name
                  FROM customer
                 WHERE id = | . $dbh->quote( $form->{customer_id} );
        }

        $query .= qq|
            ORDER BY name|;
        $sth = $dbh->prepare($query);
        $sth->execute || $form->dberror($query);

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

}

sub save_job {
    my ( $self, $myconfig, $form ) = @_;
    $form->{projectnumber} =
      $form->update_defaults( $myconfig, "projectnumber", $dbh )
      unless $form->{projectnumber};

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

    my ($income_accno) = split /--/, $form->{IC_income};

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

    if ( $form->{id} ) {
        $query = qq|
            SELECT id FROM project
            WHERE id = | . $dbh->quote( $form->{id} );
        ( $form->{id} ) = $dbh->selectrow_array($query);
    }

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

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

        $query = qq|
            SELECT id FROM project 
             WHERE projectnumber = '$uid'|;
        ( $form->{id} ) = $dbh->selectrow_array($query);
    }


    $query = qq|
        UPDATE project 
           SET projectnumber = ?,
               description = ?,
               startdate = ?,
               enddate = ?,
               parts_id = ?
               production = ?,
               customer_id = ?
         WHERE id = ?|;
    $sth = $dbh->prepare($query);
    $sth->execute(
        $form->{projectnumber}, $form->{description}, $form->{startdate},
        $form->{enddate},       $form->{id},          $form->{production},
        $form->{customer_id},   $form->{id}
    ) || $form->dberror($query);

    #### add/edit assembly
    $query = qq|SELECT id FROM parts WHERE id = | . $dbh->quote( $form->{id} );
    my ($id) = $dbh->selectrow_array($query);

    if ( !$id ) {
        $query = qq|
        INSERT INTO parts (id) 
             VALUES (| . $dbh->quote( $form->{id} ) . qq|)|;
        $dbh->do($query) || $form->dberror($query);
    }

    my $partnumber =
      ( $form->{partnumber} )
      ? $form->{partnumber}
      : $form->{projectnumber};

    $query = qq|
        UPDATE parts 
           SET partnumber = ?,
               description = ?,
               priceupdate = ?,
               listprice = ?,
               sellprice = ?,
               weight = ?,
               bin = ?,
               unit = ?,
               notes = ?,
               income_accno_id = (SELECT id FROM chart
                                   WHERE accno = ?),
               partsgroup_id = ?,
               assembly = '1',
               obsolete = '1',
               project_id = ?
               WHERE id = ?|;

    $sth = $dbh->prepare($query);
    $sth->execute(
        $partnumber,
        $form->{partdescription},
        $form->{priceupdate},
        $form->parse_amount( $myconfig, $form->{listprice} ),
        $form->parse_amount( $myconfig, $form->{sellprice} ),
        $form->parse_amount( $myconfig, $form->{weight} ),
        $form->{bin},
        $form->{unit},
        $form->{notes},
        $income_accno,
        ($partsgroup_id) ? $partsgroup_id : undef,
        $form->{id},
        $form->{id}
    ) || $form->dberror($query);

    $query =
      qq|DELETE FROM partstax WHERE parts_id = | . $dbh->qupte( $form->{id} );
    $dbh->do($query) || $form->dberror($query);

    $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);
        }
    }

    $dbh->commit;

}

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

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

    my $ref;

    my $query = qq|SELECT * FROM project WHERE id = ?|;
    my $sth = $dbh->prepare($query) || $form->dberror($query);

    $query = qq|SELECT COUNT(*) FROM parts WHERE project_id = ?|;
    my $rvh = $dbh->prepare($query) || $form->dberror($query);

    if ( !$form->{stockingdate} ) {
        $query = qq|SELECT current_date|;
        ( $form->{stockingdate} ) = $dbh->selectrow_array($query);
    }

    $query = qq|SELECT * FROM parts WHERE id = ?|;
    my $pth = $dbh->prepare($query) || $form->dberror($query);

    $query = qq|
          SELECT j.*, p.lastcost FROM jcitems j
            JOIN parts p ON (p.id = j.parts_id)
           WHERE j.project_id = ?
                 AND j.checkedin <= | . $dbh->quote( $form->{stockingdate} ) . qq|
        ORDER BY parts_id|;
    my $jth = $dbh->prepare($query) || $form->dberror($query);

    $query = qq|
        INSERT INTO assembly (id, parts_id, qty, bom, adj)
             VALUES (?, ?, ?, '0', '0')|;
    my $ath = $dbh->prepare($query) || $form->dberror($query);

    my $i = 0;
    my $sold;
    my $ship;

    while (1) {
        $i++;
        last unless $form->{"id_$i"};

        $stock = $form->parse_amount( $myconfig, $form->{"stock_$i"} );

        if ($stock) {
            $sth->execute( $form->{"id_$i"} );
            $ref = $sth->fetchrow_hashref(NAME_lc);

            if ( $stock > ( $ref->{production} - $ref->{completed} ) ) {
                $stock = $ref->{production} - $ref->{completed};
            }
            if ( ( $stock * -1 ) > $ref->{completed} ) {
                $stock = $ref->{completed} * -1;
            }

            $pth->execute( $form->{"id_$i"} );
            $pref = $pth->fetchrow_hashref(NAME_lc);

            my %assembly  = ();
            my $lastcost  = 0;
            my $sellprice = 0;
            my $listprice = 0;

            $jth->execute( $form->{"id_$i"} );
            while ( $jref = $jth->fetchrow_hashref(NAME_lc) ) {
                $assembly{qty}{ $jref->{parts_id} } +=
                  ( $jref->{qty} - $jref->{allocated} );
                $assembly{parts_id}{ $jref->{parts_id} } = $jref->{parts_id};
                $assembly{jcitems}{ $jref->{id} }        = $jref->{id};
                $lastcost +=
                  $form->round_amount(
                    $jref->{lastcost} * ( $jref->{qty} - $jref->{allocated} ),
                    2 );
                $sellprice += $form->round_amount(
                    $jref->{sellprice} * ( $jref->{qty} - $jref->{allocated} ),
                    2
                );
                $listprice += $form->round_amount(
                    $jref->{listprice} * ( $jref->{qty} - $jref->{allocated} ),
                    2
                );
            }
            $jth->finish;

            $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'|;
            ($uid) = $dbh->selectrow_array($query);

            $lastcost = $form->round_amount( $lastcost / $stock, 2 );
            $sellprice =
              ( $pref->{sellprice} )
              ? $pref->{sellprice}
              : $form->round_amount( $sellprice / $stock, 2 );
            $listprice =
              ( $pref->{listprice} )
              ? $pref->{listprice}
              : $form->round_amount( $listprice / $stock, 2 );

            $rvh->execute( $form->{"id_$i"} );
            my ($rev) = $rvh->fetchrow_array;
            $rvh->finish;

            $query = qq|
                UPDATE parts 
                   SET partnumber = ?,
                       description = ?,
                       priceupdate = ?,
                       unit = ?,
                       listprice = ?,
                       sellprice = ?,
                       lastcost = ?,
                       weight = ?,
                       onhand = ?,
                       notes = ?,
                       assembly = '1',
                       income_accno_id = ?,
                       bin = ?,
                       project_id = ?
                 WHERE id = ?|;
            $sth = $dbh->prepare($query);
            $sth->execute(
                "$pref->{partnumber}-$rev", $pref->{partdescription},
                $form->{stockingdate},      $pref->{unit},
                $listprice,                 $sellprice,
                $lastcost,                  $pref->{weight},
                $stock,                     $pref->{notes},
                $pref->{income_accno_id},   $pref->{bin},
                $form->{"id_$i"},           $uid
            ) || $form->dberror($query);

            $query = qq|
                INSERT INTO partstax (parts_id, chart_id)
                     SELECT ?, chart_id FROM partstax
                      WHERE parts_id = ?|;
            $sth = $dbh->prepare($query);
            $sth->execute( $uid, $pref->{id} )
              || $form->dberror($query);

            $pth->finish;

            for ( keys %{ $assembly{parts_id} } ) {
                if ( $assembly{qty}{$_} ) {
                    $ath->execute(
                        $uid,
                        $assembly{parts_id}{$_},
                        $form->round_amount( $assembly{qty}{$_} / $stock, 4 )
                    );
                    $ath->finish;
                }
            }

            $form->update_balance( $dbh, "project", "completed",
                qq|id = $form->{"id_$i"}|, $stock );

            $query = qq|
                UPDATE jcitems 
                   SET allocated = qty
                 WHERE allocated != qty
                       AND checkedin <= ?
                       AND project_id = ?|;
            $sth = $dbh->prepare($query);
            $sth->execute( $form->{stockingdate}, $form->{"id_$i"} )
              || $form->dberror($query);

            $sth->finish;

        }

    }

    my $rc = $dbh->commit;

    $rc;

}

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

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

    $query = qq|DELETE FROM project WHERE 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);

    my $rc = $dbh->commit;

    $rc;

}

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

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

    $query = qq|DELETE FROM partsgroup WHERE 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);

    my $rc = $dbh->commit;

    $rc;

}

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

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

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

    my $rc = $dbh->commit;

    $rc;

}

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

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

    my %audittrail = (
        tablename => 'project',
        reference => $form->{id},
        formname  => $form->{type},
        action    => 'deleted',
        id        => $form->{id}
    );

    $form->audittrail( $dbh, "", \%audittrail );

    my $query = qq|DELETE FROM project WHERE 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);

    # delete all the assemblies
    $query = qq|
        DELETE FROM assembly a 
               JOIN parts p ON (a.id = p.id)
              WHERE p.project_id = ?|;
    $sth = $dbh->prepare($query);
    $sth->execute( $form->{id} ) || $form->dberror($query);

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

    my $rc = $dbh->commit;

    $rc;

}

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

    my $var;

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

    $form->{sort} = "partsgroup" unless $form->{partsgroup};
    my @a         = (partsgroup);
    my $sortorder = $form->sort_order( \@a );

    my $query = qq|SELECT g.* FROM partsgroup g|;

    my $where = "1 = 1";

    if ( $form->{partsgroup} ne "" ) {
        $var = $dbh->quote( $form->like( lc $form->{partsgroup} ) );
        $where .= " AND lower(partsgroup) LIKE $var";
    }
    $query .= qq| WHERE $where ORDER BY $sortorder|;

    if ( $form->{status} eq 'orphaned' ) {
        $query = qq|
               SELECT g.*
                 FROM partsgroup g
            LEFT JOIN parts p ON (p.partsgroup_id = g.id)
                WHERE $where
            EXCEPT
               SELECT g.*
                 FROM partsgroup g
                 JOIN parts p ON (p.partsgroup_id = g.id)
                WHERE $where
             ORDER BY $sortorder|;
    }

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

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

    $sth->finish;

    $i;

}

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

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

    if ( $form->{id} ) {
        $query = qq|
            UPDATE partsgroup 
               SET partsgroup = | . $dbh->quote( $form->{partsgroup} ) . qq|
             WHERE id = $form->{id}|;
    }
    else {
        $query = qq|
            INSERT INTO partsgroup (partsgroup)
                 VALUES (| . $dbh->quote( $form->{partsgroup} ) . qq|)|;
    }
    $dbh->do($query) || $form->dberror($query);

    $dbh->commit;

}

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

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

    my $query = qq|SELECT * FROM partsgroup WHERE id = ?|;
    my $sth   = $dbh->prepare($query);
    $sth->execute( $form->{id} ) || $form->dberror($query);

    my $ref = $sth->fetchrow_hashref(NAME_lc);

    for ( keys %$ref ) { $form->{$_} = $ref->{$_} }

    $sth->finish;

    # check if it is orphaned
    $query = qq|SELECT count(*) FROM parts WHERE partsgroup_id = ?|;
    $sth   = $dbh->prepare($query);
    $sth->execute( $form->{id} ) || $form->dberror($query);

    ( $form->{orphaned} ) = $sth->fetchrow_array;
    $form->{orphaned} = !$form->{orphaned};

    $sth->finish;

    $dbh->commit;

}

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

    my $var;

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

    $form->{sort} = "pricegroup" unless $form->{sort};
    my @a         = (pricegroup);
    my $sortorder = $form->sort_order( \@a );

    my $query = qq|SELECT g.* FROM pricegroup g|;

    my $where = "1 = 1";

    if ( $form->{pricegroup} ne "" ) {
        $var = $dbh->quote( $form->like( lc $form->{pricegroup} ) );
        $where .= " AND lower(pricegroup) LIKE $var";
    }
    $query .= qq|
        WHERE $where ORDER BY $sortorder|;

    if ( $form->{status} eq 'orphaned' ) {
        $query = qq|
            SELECT g.*
              FROM pricegroup g
             WHERE $where
                   AND g.id NOT IN (SELECT DISTINCT pricegroup_id
                                      FROM partscustomer
                                     WHERE pricegroup_id > 0)
        ORDER BY $sortorder|;
    }

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

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

    $sth->finish;
    $dbh->commit;

    $i;

}

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

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

    if ( $form->{id} ) {
        $query = qq|
            UPDATE pricegroup SET
                   pricegroup = ?
             WHERE id = | . $dbh->quote( $form->{id} );
    }
    else {
        $query = qq|
            INSERT INTO pricegroup (pricegroup)
            VALUES (?)|;
    }
    $sth = $dbh->prepare($query);
    $sth->execute( $form->{pricegroup} ) || $form->dberror($query);

    $dbh->commit;

}

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

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

    my $query = qq|SELECT * FROM pricegroup WHERE id = ?|;
    my $sth   = $dbh->prepare($query);
    $sth->execute( $form->{id} ) || $form->dberror($query);

    my $ref = $sth->fetchrow_hashref(NAME_lc);

    for ( keys %$ref ) { $form->{$_} = $ref->{$_} }

    $sth->finish;

    # check if it is orphaned
    $query = "SELECT count(*) FROM partscustomer WHERE pricegroup_id = ?";
    $sth   = $dbh->prepare($query);
    $sth->execute( $form->{id} ) || $form->dberror($query);

    ( $form->{orphaned} ) = $sth->fetchrow_array;
    $form->{orphaned} = !$form->{orphaned};

    $sth->finish;

    $dbh->commit;

}

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

    my $dbh   = $form->{dbh};
    my $where = "1 = 1";
    my $var;
    my $ref;

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

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

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

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

    my $query = qq|
          SELECT l.description AS language, 
                 t.description AS translation, l.code
            FROM translation t
            JOIN language l ON (l.code = t.language_code)
           WHERE trans_id = ?
        ORDER BY 1|;
    my $tth = $dbh->prepare($query);

    $query = qq|
          SELECT p.id, p.partnumber, p.description
            FROM parts p
           WHERE $where
        ORDER BY $sortorder|;

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

    my $tra;

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

        # get translations for description
        $tth->execute( $ref->{id} ) || $form->dberror;

        while ( $tra = $tth->fetchrow_hashref(NAME_lc) ) {
            $form->{trans_id} = $ref->{id};
            $tra->{id}        = $ref->{id};
            push @{ $form->{translations} }, $tra;
        }
        $tth->finish;

    }
    $sth->finish;

    &get_language( "", $dbh, $form ) if $form->{id};

    $dbh->commit;

}

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

    my $where = "1 = 1";
    my $ref;
    my $var;

    if ( $form->{description} ) {
        $var = $dbh->quote( $form->like( lc $form->{description} ) );
        $where .= " AND lower(p.partsgroup) LIKE $var";
    }
    $where .= " AND p.id = " . $dbh->quote( $form->{id} ) if $form->{id};

    my $query = qq|
          SELECT l.description AS language, 
                 t.description AS translation, l.code
            FROM translation t
            JOIN language l ON (l.code = t.language_code)
           WHERE trans_id = ?
        ORDER BY 1|;
    my $tth = $dbh->prepare($query);

    $form->sort_order();

    $query = qq|
          SELECT p.id, p.partsgroup AS description
            FROM partsgroup p
           WHERE $where
        ORDER BY 2 $form->{direction}|;

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

    my $tra;

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

        # get translations for partsgroup
        $tth->execute( $ref->{id} ) || $form->dberror;

        while ( $tra = $tth->fetchrow_hashref(NAME_lc) ) {
            $form->{trans_id} = $ref->{id};
            push @{ $form->{translations} }, $tra;
        }
        $tth->finish;

    }
    $sth->finish;

    &get_language( "", $dbh, $form ) if $form->{id};

    $dbh->commit;

}

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

    my $where = "1 = 1";
    my $var;
    my $ref;

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

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

    my %ordinal = ( 'projectnumber' => 2, 'description' => 3 );

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

    my $query = qq|
          SELECT l.description AS language, 
                 t.description AS translation, l.code
            FROM translation t
            JOIN language l ON (l.code = t.language_code)
           WHERE trans_id = ?
        ORDER BY 1|;
    my $tth = $dbh->prepare($query);

    $query = qq|
          SELECT p.id, p.projectnumber, p.description
            FROM project p
           WHERE $where
        ORDER BY $sortorder|;

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

    my $tra;

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

        # get translations for description
        $tth->execute( $ref->{id} ) || $form->dberror;

        while ( $tra = $tth->fetchrow_hashref(NAME_lc) ) {
            $form->{trans_id} = $ref->{id};
            $tra->{id}        = $ref->{id};
            push @{ $form->{translations} }, $tra;
        }
        $tth->finish;

    }
    $sth->finish;

    &get_language( "", $dbh, $form ) if $form->{id};

    $dbh->commit;

}

sub get_language {
    my ( $self, $dbh, $form ) = @_;

    my $query = qq|SELECT * FROM language ORDER BY 2|;
    my $sth   = $dbh->prepare($query);
    $sth->execute || $form->dberror($query);

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

}

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

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

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

    $query = qq|
        INSERT INTO translation (trans_id, language_code, description)
             VALUES (?, ?, ?)|;
    my $sth = $dbh->prepare($query) || $form->dberror($query);

    foreach my $i ( 1 .. $form->{translation_rows} ) {
        if ( $form->{"language_code_$i"} ne "" ) {
            $sth->execute(
                $form->{id},
                $form->{"language_code_$i"},
                $form->{"translation_$i"}
            );
            $sth->finish;
        }
    }
    $dbh->commit;

}

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

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

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

    $dbh->commit;

}

sub timecard_get_currency {
    my $self  = shift @_;
    my $form  = shift @_;
    my $dbh   = $form->{dbh};
    my $query = qq|SELECT curr FROM customer WHERE id = ?|;
    my $sth   = $dbh->prepare($query);
    $sth->execute( $form->{customer_id} );
    my ($curr) = $sth->fetchrow_array;
    $form->{currency} = $curr;
}

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

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

    my $query = qq|SELECT current_date|;
    my ($transdate) = $dbh->selectrow_array($query);

    $form->all_years( $myconfig, $dbh );

    $form->all_projects( $myconfig, $dbh, $transdate );

    $form->all_employees( $myconfig, $dbh, $transdate );

    $dbh->commit;

}

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

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

    my $null;
    my $var;
    my $where;

    if ( $form->{projectnumber} ) {
        ( $null, $var ) = split /--/, $form->{projectnumber};
        $var = $dbh->quote($var);
        $where .= " AND j.project_id = $var";
    }

    if ( $form->{employee} ) {
        ( $null, $var ) = split /--/, $form->{employee};
        $var = $dbh->quote($var);
        $where .= " AND j.employee_id = $var";
    }

    ( $form->{transdatefrom}, $form->{transdateto} ) =
      $form->from_to( $form->{year}, $form->{month}, $form->{interval} )
      if $form->{year} && $form->{month};

    if ( $form->{transdatefrom} ) {
        $where .=
          " AND j.checkedin >= " . $dbh->quote( $form->{transdatefrom} );
    }
    if ( $form->{transdateto} ) {
        $where .=
            " AND j.checkedout <= (date "
          . $dbh->quote( $form->{transdateto} )
          . " + interval '1 days')";
    }

    my $query;
    my $ref;

    $query = qq|
           SELECT j.id, j.description, j.qty - j.allocated AS qty,
                  j.sellprice, j.parts_id, pr.$form->{vc}_id, 
                  j.project_id, j.checkedin::date AS transdate, 
                  j.notes, c.name AS $form->{vc}, pr.projectnumber, 
                  p.partnumber
             FROM jcitems j
             JOIN project pr ON (pr.id = j.project_id)
             JOIN employee e ON (e.id = j.employee_id)
             JOIN parts p ON (p.id = j.parts_id)
        LEFT JOIN $form->{vc} c ON (c.id = pr.$form->{vc}_id)
            WHERE pr.parts_id IS NULL
                  AND j.allocated != j.qty $where
         ORDER BY pr.projectnumber, c.name, j.checkedin::date|;

    if ( $form->{summary} ) {
        $query =~ s/j\.description/p\.description/;
        $query =~ s/c\.name,/c\.name, j\.parts_id, /;
    }

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

    # tax accounts
    $query = qq|
        SELECT c.accno
          FROM chart c
          JOIN partstax pt ON (pt.chart_id = c.id)
         WHERE pt.parts_id = ?|;
    my $tth = $dbh->prepare($query) || $form->dberror($query);
    my $ptref;

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

        $tth->execute( $ref->{parts_id} );
        $ref->{taxaccounts} = "";
        while ( $ptref = $tth->fetchrow_hashref(NAME_lc) ) {
            $ref->{taxaccounts} .= "$ptref->{accno} ";
        }
        $tth->finish;
        chop $ref->{taxaccounts};

        $ref->{amount} = $ref->{sellprice} * $ref->{qty};

        push @{ $form->{jcitems} }, $ref;
    }

    $sth->finish;

    $query = qq|SELECT value FROM defaults WHERE setting_key = 'curr'|;
    ( $form->{currency} ) = $dbh->selectrow_array($query);
    $form->{currency} =~ s/:.*//;
    $form->{defaultcurrency} = $form->{currency};

    $query = qq|
        SELECT c.accno, t.rate
          FROM tax t
          JOIN chart c ON (c.id = t.chart_id)|;
    $sth = $dbh->prepare($query);
    $sth->execute || $form->dberror($query);
    while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
        $form->{taxaccounts} .= "$ref->{accno} ";
        $form->{"$ref->{accno}_rate"} = $ref->{rate};
    }
    chop $form->{taxaccounts};
    $sth->finish;

    $dbh->commit;

}

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

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

    for my $i ( 1 .. $form->{rowcount} ) {
        for ( split / /, $form->{"jcitems_$i"} ) {
            my ( $id, $qty ) = split /:/, $_;
            $form->update_balance( $dbh, 'jcitems', 'allocated', "id = $id",
                $qty );
        }
    }

    $rc = $dbh->commit;

    $rc;

}

1;