switched monkeysphere-ssh-proxycommand to #!/bin/bash, as it has become more complex. - monkeysphere - Unnamed repository; edit this file 'description' to name the repository.

diff options

author	Daniel Kahn Gillmor <dkg@fifthhorseman.net>	2008-09-02 09:54:43 -0400
committer	Daniel Kahn Gillmor <dkg@fifthhorseman.net>	2008-09-02 09:54:43 -0400
commit	aeafd204305f1d426fd7aaa7b4a8510f04c35094 (patch)
tree	aa49d7d411e1a1f14933118f0ae8dda370da2b67 /doc/announcement.html
parent	a38d8f8ad0a5531f3b1c28cc7bc248c965d0f8ad (diff)

switched monkeysphere-ssh-proxycommand to #!/bin/bash, as it has become more complex.

Diffstat (limited to 'doc/announcement.html')

0 files changed, 0 insertions, 0 deletions

class="hl com"> # also used for partsgroups

#====================================================================

=head1 METHODS

=over

=cut

package PE;

=item PE->($myconfig, $form);

Populates the list referred to as $form->{all_project} with hashes containing

details about projects. Each hash contains the project record's fields along

with the name of any associated customer. If $form->{status} is 'orphaned',

only add projects that aren't referred to in any transactions, invoices,

orders, or time cards. If $form->{status} is 'active', only projects that have

not reached their enddate are added; when $form->{status} is 'inactive', only

add projects that have reached their enddates. When $form->{year} and

$form->{month} are set, use their values, along with that of $form->{interval},

to set the startdatefrom and startdateto attributes of $form. These attributes

are used to prepare a date range for accepted start dates. Both

$form->{description} and $form->{projectnumber} are used to limit the results.

Returns the number of projects added to the list. $myconfig is unused.

=cut

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)

    } elsif ( $form->{status} eq 'active' ) {

        $where .= qq| 

AND (pr.enddate IS NULL

            OR pr.enddate >= current_date)|;

    } elsif ( $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;

}

=item PE->get_project($myconfig, $form)

If $form->{id} is set, populates the $form attributes projectnumber,

description, startdate, enddate, parts_id, production, completed, and

customer_id with details from the project record and name with the associated

customer name. If the project is not used in any transaction, invoice, order,

or time card, $form->{orphaned} is set to true, otherwise false.

Even if $form->{id} is false, PE->get_customer is run, along with any custom

SELECT queries for the table 'project'.

=cut

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;

}

=item PE->save_project($myconfig, $form)

Updates a project, or adds a new one if $form->{id} is not set.

The $form attributes of startdate, enddate, customer_id, description, and

projectnumber are used for the project record. If $form->{projectnumber} is

false, a new one is obtained through $form->update_defaults. When a new

project is added, $form->{id} is set to that new id. Any custom queries for

UPDATE on the project table are run.

=cut

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;

}

=item PE->list_stock($myconfig, $form);

Populates the list referred to as $form->{all_project} with hashes that contain

details about projects.

Sets $form->{stockingdate} to the current date if it is not already set.

This function is probably unused.

$myconfig is unused.

=cut

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|;

    } elsif ( $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;

}

=item PE->jobs($myconfig, $form);

This function is probably unused.

$myconfig is unused.

=cut

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;

}

=item PE->get_job($myconfig, $form);

This function is probably unused as part of Dieter's incomplete job costing.

=cut

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;

}

=item PE->get_customer($myconfig, $form[, $dbh]);

Populates the list referred to as $form->{all_customer} with hashes containing

the ids and names of customers unless the number of customers added would be

greater than or equal to $myconfig->{vclimit}. $form->{startdate} and

$form->{enddate} form a date range to limit the results. If

$form->{customer_id} is set, then the customer with that id will be in the

result set.

=cut

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;

}

=item PE->save_job($myconfig, $form);

Yet another save function. This one is related to the incomplete job handling.

=cut

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;

}

=item PE->stock_assembly($myconfig, $form)

Looks like more of that job control code. IC.pm has the functions actually

used by assemblies.

=cut

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} ),

);

                $listprice += $form->round_amount(

                    $jref->{listprice} * ( $jref->{qty} - $jref->{allocated} ),

);

}

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

}

=item PE->delete_project($myconfig, $form);

Deletes the database entry in project identified by $form->{id} and associated

translations.

$myconfig is unused.

=cut

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;

}

=item PE->delete_partsgroup($myconfig, $form);

Deletes the entry in partsgroup identified by $form->{id} and associated

translations.

$myconfig is unused.

=cut

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;

}

=item PE->delete_pricegroup($myconfig, $form);

Deletes the pricegroup entry identified by $form->{id}.

$myconfig is unused.

=cut

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;

}

=item PE->delete_job($myconfig, $form);

An "enhanced" variant of PE->delete_project. In addition to deleting the

project identified by $form->{id} and the associated translations, also deletes

all parts and assemblies with $form->{id} as a project_id. This function adds

an audit trail entry for the table 'project' and the action 'deleted' where the

formname is taken from $form->{type}.

$myconfig is unused.

=cut

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;

}

=item PE->partsgroups($myconfig, $form);

Populates the list referred to as $form->{item_list} with hashes containing

the id and partsgroup (name) for all the partsgroups in the database. If

$form->{partsgroup} is non-empty, the results are limited to the partsgroups

that contain that value in their name (case insensitive). If $form->{status}

is 'orphaned', only partsgroups that are not associated with a part are added.

The number of partsgroups added to $form->{item_list} is returned.

$myconfig is unused.

=cut

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;

}

=item PE->save_partsgroup($myconfig, $form);

Save a partsgroup record. If $form->{id} is set, update the description of

the partsgroup with that id to be $form->{partsgroup}. Otherwise, create a

new partsgroup with that description.

$myconfig is unused.

=cut

sub save_partsgroup {

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

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

    my @group = ($form->{partsgroup});

    if ( $form->{id} ) {

        $query = qq|

UPDATE partsgroup

SET partsgroup = ?

WHERE id = ?|;

        push @group,  $form->{id};

}

else {

        $query = qq|

INSERT INTO partsgroup (partsgroup)

VALUES (?)|;

}

    $dbh->do($query, undef, @group) || $form->dberror($query);

    $dbh->commit;

}

=item PE->get_partsgroup($myconfig, $form);

Sets $form->{partsgroup} to the description of the partsgroup identified by

$form->{id}. If there are no parts entries associated with that partsgroup,

$form->{orphaned} is made true, otherwise it is set to false.

$myconfig is unused.

=cut

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;

}

=item PE->pricegroups($myconfig, $form);

Populates the list referred to as $form->{item_list} with hashes containing

details (id and pricegroup (description)) about pricegroups. All the groups

are added unless $form->{pricegroup} is set, in which case it will search for

groups with that description, or $form->{status} is 'orphaned', which limits

the results to those not related to any customers (partscustomer table). The

return value is the number of pricegroups added to the list.

$myconfig is unused.

=cut

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;

}

=item PE->save_pricegroup($myconfig, $form);

Adds or updates a pricegroup. If $form->{id} is set, update the pricegroup

value using $form->{pricegroup}. If $form->{id} is not set, adds a new

pricegroup with a pricegroup value of $form->{pricegroup}.

$myconfig is unused.

=cut

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;

}

=item PE->get_pricegroup($myconfig, $form);

Sets $form->{pricegroup} to the description of the pricegroup identified by

$form->{id}. If the pricegroup is not mentioned in partscustomer,

$form->{orphaned} is set true, otherwise false.

=cut

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;

}

=item PE::description_translations('', $myconfig, $form);

Populates the list referred to as $form->{translations} with hashes detailing

non-obsolete goods and services and their translated descriptions. The main

details hash immediately precedes its set of translations and has the

attributes id, partnumber, and description. The translations have the

attributes id (same as in the main hash), language, translation, and code.

When $form->{id} is set, only adds an entry for the item having that id, but

also populates $form->{all_language} using PE::get_language. The attributes

partnumber and description are searchable and if set, will limit the results to

only those that match them.

$myconfig is unused. $form->{trans_id} is set to the last encountered part id.

=cut

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;

}

=item PE::partsgroup_translations("", $myconfig, $form)

Populates the list referred to as $form->{translations} with hashrefs containing

details about partsgroups and their translated names. A master hash contains

the id and description of the partsgroup and is immediately followed by its

translation hashes, which contain the language, translation, and code of the

translation. The list contains the details for all partsgroups unless

$form->{description} is set, in which case only partsgroups with a matching

description are included, or $form->{id} is set. When $form->{id} is set, only

translations for the partgroup with that are included and $form->{all_language}

is populated by get_language.

$myconfig is unused. $form->{trans_id} is set to the last id encountered.

=cut

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;

}

=item PE::project_translations("", $myconfig, $form)

Populates the list referred to as $form->{translations} with hashrefs containing

details about projects and their translated names. A master hash contains the

id, project number, and description of the project and is immediately followed

by its translation hashes, which have the same id as the master and also

contain the language, translation, and code of the translation. The list

contains the details for all projects unless $form->{description} or

$form->{projectnumber} is set, in which case only projects that match the

appropriate field are included, or $form->{id} is set. When $form->{id} is

set, only translations for the project with that id are included and

$form->{all_language} is populated by get_language.

$myconfig is unused. $form->{trans_id} is set to the last encountered id.

=cut

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;

}

=item PE::get_language("", $dbh, $form)

Populates the list referred to as $form->{all_language} with hashes containing

the code and description of all languages registered with the system in the

language table.

=cut

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;

}

=item PE::save_translation("", $myconfig, $form);

Deletes all translations with the trans_id (part id, project id, or partsgroup

id) of $form->{id} then adds new entries for $form->{id}. The number of

translation entries is obtained from $form->{translation_rows}. The actual

translation entries are derived from $form->{language_code_I} and

$form->{translation_I}, where I is some integer between 1 and

$form->{translation_rows} inclusive.

$myconfig is unused.

=cut

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;

}

=item PE::delete_translation("", $myconfig, $form);

Deletes all translation entries that have the trans_id of $form->{id}.

$myconfig is unused.

=cut

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;

}

=item PE->timecard_get_currency($form);

Sets $form->{currency} to the currency set for the customer who has the id

$form->{customer_id}.

=cut

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;

}

=item PE::project_sales_order("", $myconfig, $form)

Executes $form->all_years, $form->all_projects, and $form->all_employees, with

a limiting transdate of the current date.

=cut

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;

}

=item PE->get_jcitems($myconfig, $form);

This function is used as part of the sales order generation accessible from the

projects interface, to generate the list of possible orders.

Populates the list referred to as $form->{jcitems} with hashes containing

details about sales orders that can be generated that relate to projects. Each

of the hashes has the attributes id (timecard id), description (timecard

description), qty (unallocated chargeable hours), sellprice (hourly rate),

parts_id (service id), customer_id, project_id, transdate (date on timecard),

notes, customer (customer name), projectnumber, partnumber, taxaccounts (space

separated list that contains the account numbers of taxes that apply to the

service), and amount (qty*sellprice). If $form->{summary} is true, the

description field contains the service description instead of the timecard

description.

All possible, unconsolidated sales orders are normally listed. If

$form->{projectnumber} is set, only orders associated with the project are

listed. $form->{employee} limits the list to timecards with the given employee.

When $form->{year} and $form->{month} are set, the transdatefrom and transdateto

attributes are populated with values derived from the year, month, and interval

$form attributes. $form->{transdatefrom} is used to limit the results to

time cards checked in on or after that date. $form->{transdateto} limits to

time cards checked out on or before the provided date. $form->{vc} must be

'customer'.

Regardless of the values added to $form->{jcitems}, this function sets

$form->{currency} and $form->{defaultcurrency} to the first currency mentioned

in defaults. It also fills $form->{taxaccounts} with a space separated list

of the account numbers of all tax accounts and for each accno forms a

$form->{${accno}_rate} attribute that contains the tax's rate as expressed in

the tax table.

$myconfig is unused.

=cut

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) ) {

        $form->db_parse_numeric(sth=>$sth, hashref=>$ref);

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

}

=item PE->allocate_projectitems($myconfig, $form);

Updates the jcitems table to adjust the allocated quantities of time. The

time cards, and allocated time, to update is obtained from the various space

separated lists $form->{jcitems_I}, where I is between 1 and the value of

$form->{rowcount}. Each element of those space separated lists is a colon

separated pair where the first element is the time card id and the second

element is the increase in allocated hours.

$myconfig is unused.

=cut

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;

}


context:
space:
mode: