=head1 NAME
PE
=head1 SYNOPSIS
Support functions for projects, partsgroups, and parts
=head1 COPYRIGHT
#====================================================================
# 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
#
#====================================================================
=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} ),
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;
}
=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<i>} and
$form->{translation_I<i>}, where I<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<i>}, where I<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;
}
1;
=back