summaryrefslogtreecommitdiff
path: root/LedgerSMB/PE.pm
diff options
context:
space:
mode:
Diffstat (limited to 'LedgerSMB/PE.pm')
-rw-r--r--LedgerSMB/PE.pm2119
1 files changed, 1046 insertions, 1073 deletions
diff --git a/LedgerSMB/PE.pm b/LedgerSMB/PE.pm
index 099f94ce..209f6767 100644
--- a/LedgerSMB/PE.pm
+++ b/LedgerSMB/PE.pm
@@ -1,8 +1,8 @@
#=====================================================================
-# LedgerSMB
+# 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.
@@ -34,61 +34,62 @@
package PE;
-
sub projects {
- my ($self, $myconfig, $form) = @_;
-
- my $dbh = $form->{dbh};
-
- $form->{sort} = "projectnumber" unless $form->{sort};
- my @a = ($form->{sort});
- my %ordinal = ( projectnumber => 2,
- description => 3,
- startdate => 4,
- enddate => 5,
- );
- my $sortorder = $form->sort_order(\@a, \%ordinal);
-
- my $query;
- my $where = "WHERE 1=1";
-
- $query = qq|
+ 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
+ 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
+ }
+
+ 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
@@ -105,66 +106,64 @@ sub projects {
WHERE project_id > 0)
|;
- }
- if ($form->{status} eq 'active') {
- $where .= qq|
+ }
+ if ( $form->{status} eq 'active' ) {
+ $where .= qq|
AND (pr.enddate IS NULL
OR pr.enddate >= current_date)|;
- }
- if ($form->{status} eq 'inactive') {
- $where .= qq| AND pr.enddate <= current_date|;
- }
+ }
+ if ( $form->{status} eq 'inactive' ) {
+ $where .= qq| AND pr.enddate <= current_date|;
+ }
- $query .= qq|
+ $query .= qq|
$where
ORDER BY $sortorder|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
+ $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++;
- }
+ my $i = 0;
+ while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
+ push @{ $form->{all_project} }, $ref;
+ $i++;
+ }
- $sth->finish;
- $dbh->commit;
-
- $i;
+ $sth->finish;
+ $dbh->commit;
-}
+ $i;
+}
sub get_project {
- my ($self, $myconfig, $form) = @_;
+ my ( $self, $myconfig, $form ) = @_;
+
+ my $dbh = $form->{dbh};
- my $dbh = $form->{dbh};
+ my $query;
+ my $sth;
+ my $ref;
+ my $where;
- my $query;
- my $sth;
- my $ref;
- my $where;
-
- if ($form->{id}) {
+ if ( $form->{id} ) {
-
- $query = qq|
+ $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);
+ $sth = $dbh->prepare($query);
+ $sth->execute( $form->{id} ) || $form->dberror($query);
- $ref = $sth->fetchrow_hashref(NAME_lc);
-
- for (keys %$ref) { $form->{$_} = $ref->{$_} }
+ $ref = $sth->fetchrow_hashref(NAME_lc);
- $sth->finish;
+ for ( keys %$ref ) { $form->{$_} = $ref->{$_} }
- # check if it is orphaned
- $query = qq|
+ $sth->finish;
+
+ # check if it is orphaned
+ $query = qq|
SELECT count(*)
FROM acc_trans
WHERE project_id = ?
@@ -180,176 +179,170 @@ sub get_project {
SELECT count(*)
FROM jcitems
WHERE project_id = ?|;
- $sth = $dbh->prepare($query);
- $sth->execute(
- $form->{id}, $form->{id}, $form->{id}, $form->{id}
- )|| $form->dberror($query);
+ $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};
- }
+ my $count;
+ while ( ($count) = $sth->fetchrow_array ) {
+ $form->{orphaned} += $count;
+ }
+ $sth->finish;
+ $form->{orphaned} = !$form->{orphaned};
+ }
- PE->get_customer($myconfig, $form, $dbh);
+ PE->get_customer( $myconfig, $form, $dbh );
- $form->run_custom_queries('project', 'SELECT');
+ $form->run_custom_queries( 'project', 'SELECT' );
- $dbh->commit;
+ $dbh->commit;
}
-
sub save_project {
- my ($self, $myconfig, $form) = @_;
-
- my $dbh = $form->{dbh};
-
- $form->{customer_id} ||= undef;
-
- $form->{projectnumber}
- = $form->update_defaults($myconfig, "projectnumber", $dbh)
- unless $form->{projectnumber};
- my $enddate;
- my $startdate;
- $enddate = $form->{enddate} if $form->{enddate};
- $startdate = $form->{startdate} if $form->{startdate};
-
- if ($form->{id}) {
-
- $query = qq|
+ 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|
+ 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);
- $form->run_custom_queries('project', 'UPDATE');
-
- $dbh->commit;
+ }
+ $sth = $dbh->prepare($query);
+ $sth->execute( $form->{projectnumber},
+ $form->{description}, $startdate, $enddate, $form->{customer_id} )
+ || $form->dberror($query);
+ $form->run_custom_queries( 'project', 'UPDATE' );
-}
+ $dbh->commit;
+}
sub list_stock {
- my ($self, $myconfig, $form) = @_;
-
- my $dbh = $form->{dbh};
+ my ( $self, $myconfig, $form ) = @_;
+
+ my $dbh = $form->{dbh};
- my $var;
- my $where = "1 = 1";
+ my $var;
+ my $where = "1 = 1";
- if ($form->{status} eq 'active') {
- $where = qq|
+ if ( $form->{status} eq 'active' ) {
+ $where = qq|
(pr.enddate IS NULL OR pr.enddate >= current_date)
AND pr.completed < pr.production|;
- }
- if ($form->{status} eq 'inactive') {
- $where = qq|pr.completed = pr.production|;
- }
-
- if ($form->{projectnumber}) {
- $var = $dbh->quote($form->like(lc $form->{projectnumber}));
- $where .= " AND lower(pr.projectnumber) LIKE $var";
- }
-
- if ($form->{description}) {
- $var = $dbh->quote($form->like(lc $form->{description}));
- $where .= " AND lower(pr.description) LIKE $var";
- }
-
- $form->{sort} = "projectnumber" unless $form->{sort};
- my @a = ($form->{sort});
- my %ordinal = ( projectnumber => 2, description => 3 );
- my $sortorder = $form->sort_order(\@a, \%ordinal);
-
- my $query = qq|
+ }
+ if ( $form->{status} eq 'inactive' ) {
+ $where = qq|pr.completed = pr.production|;
+ }
+
+ if ( $form->{projectnumber} ) {
+ $var = $dbh->quote( $form->like( lc $form->{projectnumber} ) );
+ $where .= " AND lower(pr.projectnumber) LIKE $var";
+ }
+
+ if ( $form->{description} ) {
+ $var = $dbh->quote( $form->like( lc $form->{description} ) );
+ $where .= " AND lower(pr.description) LIKE $var";
+ }
+
+ $form->{sort} = "projectnumber" unless $form->{sort};
+ my @a = ( $form->{sort} );
+ my %ordinal = ( projectnumber => 2, description => 3 );
+ my $sortorder = $form->sort_order( \@a, \%ordinal );
+
+ my $query = qq|
SELECT pr.*, p.partnumber
FROM project pr
JOIN parts p ON (p.id = pr.parts_id)
WHERE $where
ORDER BY $sortorder|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
+ $sth = $dbh->prepare($query);
+ $sth->execute || $form->dberror($query);
- while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
- push @{ $form->{all_project} }, $ref;
- }
- $sth->finish;
+ 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;
-
-}
+ $query = qq|SELECT current_date|;
+ ( $form->{stockingdate} ) = $dbh->selectrow_array($query)
+ if !$form->{stockingdate};
+ $dbh->commit;
+
+}
sub jobs {
- my ($self, $myconfig, $form) = @_;
-
- my $dbh = $form->{dbh};
-
- $form->{sort} = "projectnumber" unless $form->{sort};
- my @a = ($form->{sort});
- my %ordinal = (projectnumber => 2, description => 3, startdate => 4);
- my $sortorder = $form->sort_order(\@a, \%ordinal);
-
- my $query = qq|
+ 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|
+ 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
@@ -364,42 +357,41 @@ sub jobs {
FROM jcitems
WHERE project_id > 0)
)|;
- }
+ }
- $query .= qq|
+ $query .= qq|
ORDER BY $sortorder|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
+ $sth = $dbh->prepare($query);
+ $sth->execute || $form->dberror($query);
- while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
- push @{ $form->{all_project} }, $ref;
- }
+ while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
+ push @{ $form->{all_project} }, $ref;
+ }
- $sth->finish;
-
- $dbh->commit;
-
-}
+ $sth->finish;
+ $dbh->commit;
+
+}
sub get_job {
- my ($self, $myconfig, $form) = @_;
+ my ( $self, $myconfig, $form ) = @_;
- # connect to database
- my $dbh = $form->{dbh};
+ # connect to database
+ my $dbh = $form->{dbh};
- my $query;
- my $sth;
- my $ref;
+ my $query;
+ my $sth;
+ my $ref;
- if ($form->{id}) {
- $query = qq|
+ if ( $form->{id} ) {
+ $query = qq|
SELECT value FROM defaults
WHERE setting_key = 'weightunit'|;
- ($form->{weightunit}) = $dbh->selectrow_array($query);
+ ( $form->{weightunit} ) = $dbh->selectrow_array($query);
- $query = qq|
+ $query = qq|
SELECT pr.*, p.partnumber,
p.description AS partdescription, p.unit,
p.listprice, p.sellprice, p.priceupdate,
@@ -413,25 +405,27 @@ sub get_job {
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|
+ 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);
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
+ for ( keys %$ref ) { $form->{$_} = $ref->{$_} }
- $ref = $sth->fetchrow_hashref(NAME_lc);
-
- for (keys %$ref) { $form->{$_} = $ref->{$_} }
+ $sth->finish;
- $sth->finish;
+ if ( $form->{id} ) {
- if ($form->{id}) {
- # check if it is orphaned
- $query = qq|
+ # check if it is orphaned
+ $query = qq|
SELECT count(*)
FROM invoice
WHERE project_id = ?
@@ -443,165 +437,167 @@ sub get_job {
SELECT count(*)
FROM jcitems
WHERE project_id = ?|;
- $sth = $dbh->prepare($query);
- $sth->execute(
- $form->{id}, $form->{id}, $form->{id}
- )|| $form->dberror($query);
+ $sth = $dbh->prepare($query);
+ $sth->execute( $form->{id}, $form->{id}, $form->{id} )
+ || $form->dberror($query);
- my $count;
+ my $count;
- my $count;
- while (($count) = $sth->fetchrow_array) {
- $form->{orphaned} += $count;
- }
- $sth->finish;
+ my $count;
+ while ( ($count) = $sth->fetchrow_array ) {
+ $form->{orphaned} += $count;
+ }
+ $sth->finish;
- }
+ }
- $form->{orphaned} = !$form->{orphaned};
-
- $query = qq|
+ $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|
+ $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;
-}
+ $sth = $dbh->prepare($query);
+ $sth->execute( $form->{id} ) || $form->dberror($query);
+ while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
+ $form->{amount}{ $ref->{accno} } = $ref->{accno};
+ }
+ $sth->finish;
+ }
+
+ PE->get_customer( $myconfig, $form, $dbh );
+
+ $dbh->commit;
+
+}
sub get_customer {
- my ($self, $myconfig, $form, $dbh) = @_;
-
- if (! $dbh) {
- $dbh = $form->{dbh};
- }
-
- my $query;
- my $sth;
- my $ref;
-
- if (! $form->{startdate}) {
- $query = qq|SELECT current_date|;
- ($form->{startdate}) = $dbh->selectrow_array($query);
- }
-
- my $where = qq|(startdate >= |.$dbh->quote($form->{startdate}).
- qq| OR startdate IS NULL OR enddate IS NULL)|;
-
- if ($form->{enddate}) {
- $where .= qq| AND (enddate >= |.$dbh->quote($form->{enddate}).
- qq| OR enddate IS NULL)|;
- } else {
- $where .=
- qq| AND (enddate >= current_date OR enddate IS NULL)|;
- }
-
- $query = qq|
+ 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);
+ my ($count) = $dbh->selectrow_array($query);
- if ($count < $myconfig->{vclimit}) {
- $query = qq|
+ if ( $count < $myconfig->{vclimit} ) {
+ $query = qq|
SELECT id, name
FROM customer
WHERE $where|;
- if ($form->{customer_id}) {
- $query .= qq|
+ if ( $form->{customer_id} ) {
+ $query .= qq|
UNION
SELECT id,name
FROM customer
- WHERE id = |.
- $dbh->quote($form->{customer_id});
- }
+ WHERE id = | . $dbh->quote( $form->{customer_id} );
+ }
- $query .= qq|
+ $query .= qq|
ORDER BY name|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
+ $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;
- }
+ @{ $form->{all_customer} } = ();
+ while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
+ push @{ $form->{all_customer} }, $ref;
+ }
+ $sth->finish;
+ }
}
-
sub save_job {
- my ($self, $myconfig, $form) = @_;
-
- my $dbh = $form->{dbh};
-
- my ($income_accno) = split /--/, $form->{IC_income};
-
- my ($partsgroup, $partsgroup_id) = split /--/, $form->{partsgroup};
-
- if ($form->{id}) {
- $query = qq|
+ my ( $self, $myconfig, $form ) = @_;
+
+ 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|
+ 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);
+ $dbh->do($query) || $form->dberror($query);
- $query = qq|
+ $query = qq|
SELECT id FROM project
WHERE projectnumber = '$uid'|;
- ($form->{id}) = $dbh->selectrow_array($query);
- }
+ ( $form->{id} ) = $dbh->selectrow_array($query);
+ }
- $form->{projectnumber}
- = $form->update_defaults($myconfig, "projectnumber", $dbh)
- unless $form->{projectnumber};
+ $form->{projectnumber} =
+ $form->update_defaults( $myconfig, "projectnumber", $dbh )
+ unless $form->{projectnumber};
- $query = qq|
+ $query = qq|
UPDATE project
SET projectnumber = ?,
description = ?,
@@ -611,31 +607,30 @@ sub save_job {
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);
-
+ $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);
+ }
- #### add/edit assembly
- $query = qq|SELECT id FROM parts WHERE id = |.$dbh->quote($form->{id});
- my ($id) = $dbh->selectrow_array($query);
+ my $partnumber =
+ ( $form->{partnumber} )
+ ? $form->{partnumber}
+ : $form->{projectnumber};
- 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|
+ $query = qq|
UPDATE parts
SET partnumber = ?,
description = ?,
@@ -654,155 +649,155 @@ sub save_job {
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|
+ $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;
+ $sth = $dbh->prepare($query);
+ for ( split / /, $form->{taxaccounts} ) {
+ if ( $form->{"IC_tax_$_"} ) {
+ $sth->execute( $form->{id}, $_ )
+ || $form->dberror($query);
+ }
+ }
-}
+ $dbh->commit;
+}
sub stock_assembly {
- my ($self, $myconfig, $form) = @_;
-
- my $dbh = $form->{dbh};
-
- my $ref;
-
- my $query = qq|SELECT * FROM project WHERE id = ?|;
- my $sth = $dbh->prepare($query) || $form->dberror($query);
-
- $query = qq|SELECT COUNT(*) FROM parts WHERE project_id = ?|;
- my $rvh = $dbh->prepare($query) || $form->dberror($query);
-
- if (! $form->{stockingdate}) {
- $query = qq|SELECT current_date|;
- ($form->{stockingdate}) = $dbh->selectrow_array($query);
- }
-
- $query = qq|SELECT * FROM parts WHERE id = ?|;
- my $pth = $dbh->prepare($query) || $form->dberror($query);
-
- $query = qq|
+ 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|
+ AND j.checkedin <= | . $dbh->quote( $form->{stockingdate} ) . qq|
ORDER BY parts_id|;
- my $jth = $dbh->prepare($query) || $form->dberror($query);
+ my $jth = $dbh->prepare($query) || $form->dberror($query);
- $query = qq|
+ $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|
+ 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);
+ $dbh->do($query) || $form->dberror($query);
- $query = qq|
+ $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|
+ ($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 = ?,
@@ -819,188 +814,181 @@ sub stock_assembly {
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|
+ $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|
+ $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 = $dbh->prepare($query);
+ $sth->execute( $form->{stockingdate}, $form->{"id_$i"} )
+ || $form->dberror($query);
- $sth->finish;
-
- }
+ $sth->finish;
- }
+ }
- my $rc = $dbh->commit;
-
- $rc;
+ }
-}
+ my $rc = $dbh->commit;
+ $rc;
+
+}
sub delete_project {
- my ($self, $myconfig, $form) = @_;
-
- my $dbh = $form->{dbh};
-
- $query = qq|DELETE FROM project WHERE id = ?|;
- $sth = $dbh->prepare($query);
- $sth->execute($form->{id}) || $form->dberror($query);
-
- $query = qq|DELETE FROM translation
+ 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 = $form->{id}|;
- $sth = $dbh->prepare($query);
- $sth->execute($form->{id}) || $form->dberror($query);
+ $sth = $dbh->prepare($query);
+ $sth->execute( $form->{id} ) || $form->dberror($query);
- my $rc = $dbh->commit;
+ my $rc = $dbh->commit;
- $rc;
-
-}
+ $rc;
+}
sub delete_partsgroup {
- my ($self, $myconfig, $form) = @_;
-
- my $dbh = $form->{dbh};
-
- $query = qq|DELETE FROM partsgroup WHERE id = ?|;
- $sth = $dbh->prepare($query);
- $sth->execute($form->{id}) || $form->dberror($query);
-
- $query = qq|DELETE FROM translation WHERE trans_id = ?|;
- $sth = $dbh->prepare($query);
- $sth->execute($form->{id}) || $form->dberror($query);
-
- my $rc = $dbh->commit;
-
- $rc;
-
-}
+ my ( $self, $myconfig, $form ) = @_;
+ my $dbh = $form->{dbh};
-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;
+ $query = qq|DELETE FROM partsgroup WHERE id = ?|;
+ $sth = $dbh->prepare($query);
+ $sth->execute( $form->{id} ) || $form->dberror($query);
+
+ $query = qq|DELETE FROM translation WHERE trans_id = ?|;
+ $sth = $dbh->prepare($query);
+ $sth->execute( $form->{id} ) || $form->dberror($query);
+
+ my $rc = $dbh->commit;
+
+ $rc;
}
+sub delete_pricegroup {
+ my ( $self, $myconfig, $form ) = @_;
+
+ my $dbh = $form->{dbh};
+
+ $query = qq|DELETE FROM pricegroup WHERE id = ?|;
+ $sth = $dbh->prepare($query);
+ $sth->execute( $form->{id} ) || $form->dberror($query);
+
+ my $rc = $dbh->commit;
+
+ $rc;
+
+}
sub delete_job {
- my ($self, $myconfig, $form) = @_;
-
- my $dbh = $form->{dbh};
-
- my %audittrail = (
- tablename => 'project',
- reference => $form->{id},
- formname => $form->{type},
- action => 'deleted',
- id => $form->{id} );
-
- $form->audittrail($dbh, "", \%audittrail);
-
- my $query = qq|DELETE FROM project WHERE id = ?|;
- $sth = $dbh->prepare($query);
- $sth->execute($form->{id}) || $form->dberror($query);
-
- $query = qq|DELETE FROM translation WHERE trans_id = ?|;
- $sth = $dbh->prepare($query);
- $sth->execute($form->{id}) || $form->dberror($query);
-
- # delete all the assemblies
- $query = qq|
+ 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);
+ $sth = $dbh->prepare($query);
+ $sth->execute( $form->{id} ) || $form->dberror($query);
- my $rc = $dbh->commit;
+ $query = qq|DELETE FROM parts WHERE project_id = ?|;
+ $sth = $dbh->prepare($query);
+ $sth->execute( $form->{id} ) || $form->dberror($query);
- $rc;
+ my $rc = $dbh->commit;
-}
+ $rc;
+}
sub partsgroups {
- my ($self, $myconfig, $form) = @_;
-
- my $var;
-
- my $dbh = $form->{dbh};
-
- $form->{sort} = "partsgroup" unless $form->{partsgroup};
- my @a = (partsgroup);
- my $sortorder = $form->sort_order(\@a);
-
- my $query = qq|SELECT g.* FROM partsgroup g|;
-
- my $where = "1 = 1";
-
- if ($form->{partsgroup} ne "") {
- $var = $dbh->quote($form->like(lc $form->{partsgroup}));
- $where .= " AND lower(partsgroup) LIKE '$var'";
- }
- $query .= qq| WHERE $where ORDER BY $sortorder|;
-
- if ($form->{status} eq 'orphaned') {
- $query = qq|
+ 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)
@@ -1011,101 +999,98 @@ sub partsgroups {
JOIN parts p ON (p.partsgroup_id = g.id)
WHERE $where
ORDER BY $sortorder|;
- }
+ }
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
+ $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++;
- }
+ my $i = 0;
+ while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
+ push @{ $form->{item_list} }, $ref;
+ $i++;
+ }
- $sth->finish;
-
- $i;
+ $sth->finish;
-}
+ $i;
+}
sub save_partsgroup {
- my ($self, $myconfig, $form) = @_;
-
- my $dbh = $form->{dbh};
-
- if ($form->{id}) {
- $query = qq|
+ my ( $self, $myconfig, $form ) = @_;
+
+ my $dbh = $form->{dbh};
+
+ if ( $form->{id} ) {
+ $query = qq|
UPDATE partsgroup
- SET partsgroup = |.
- $dbh->quote($form->{partsgroup}).qq|
+ SET partsgroup = | . $dbh->quote( $form->{partsgroup} ) . qq|
WHERE id = $form->{id}|;
- } else {
- $query = qq|
+ }
+ else {
+ $query = qq|
INSERT INTO partsgroup (partsgroup)
- VALUES (|.$dbh->quote($form->{partsgroup}).qq|)|;
- }
- $dbh->do($query) || $form->dberror($query);
+ VALUES (| . $dbh->quote( $form->{partsgroup} ) . qq|)|;
+ }
+ $dbh->do($query) || $form->dberror($query);
- $dbh->commit;
+ $dbh->commit;
}
-
sub get_partsgroup {
- my ($self, $myconfig, $form) = @_;
+ 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 $dbh = $form->{dbh};
- my $ref = $sth->fetchrow_hashref(NAME_lc);
-
- for (keys %$ref) { $form->{$_} = $ref->{$_} }
+ my $query = qq|SELECT * FROM partsgroup WHERE id = ?|;
+ my $sth = $dbh->prepare($query);
+ $sth->execute( $form->{id} ) || $form->dberror($query);
- $sth->finish;
+ my $ref = $sth->fetchrow_hashref(NAME_lc);
- # 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);
+ for ( keys %$ref ) { $form->{$_} = $ref->{$_} }
- ($form->{orphaned}) = $sth->fetchrow_array;
- $form->{orphaned} = !$form->{orphaned};
-
- $sth->finish;
-
- $dbh->commit;
+ $sth->finish;
-}
+ # check if it is orphaned
+ $query = qq|SELECT count(*) FROM parts WHERE partsgroup_id = ?|;
+ $sth = $dbh->prepare($query);
+ $sth->execute( $form->{id} ) || $form->dberror($query);
+ ( $form->{orphaned} ) = $sth->fetchrow_array;
+ $form->{orphaned} = !$form->{orphaned};
+
+ $sth->finish;
+
+ $dbh->commit;
+
+}
sub pricegroups {
- my ($self, $myconfig, $form) = @_;
-
- my $var;
-
- my $dbh = $form->{dbh};
-
- $form->{sort} = "pricegroup" unless $form->{sort};
- my @a = (pricegroup);
- my $sortorder = $form->sort_order(\@a);
-
- my $query = qq|SELECT g.* FROM pricegroup g|;
-
- my $where = "1 = 1";
-
- if ($form->{pricegroup} ne "") {
- $var = $dbh->quote($form->like(lc $form->{pricegroup}));
- $where .= " AND lower(pricegroup) LIKE $var";
- }
- $query .= qq|
+ 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|
+
+ if ( $form->{status} eq 'orphaned' ) {
+ $query = qq|
SELECT g.*
FROM pricegroup g
WHERE $where
@@ -1113,396 +1098,387 @@ sub pricegroups {
FROM partscustomer
WHERE pricegroup_id > 0)
ORDER BY $sortorder|;
- }
+ }
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
+ $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++;
- }
+ my $i = 0;
+ while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
+ push @{ $form->{item_list} }, $ref;
+ $i++;
+ }
- $sth->finish;
- $dbh->commit;
-
- $i;
+ $sth->finish;
+ $dbh->commit;
-}
+ $i;
+}
sub save_pricegroup {
- my ($self, $myconfig, $form) = @_;
-
- my $dbh = $form->{dbh};
-
- if ($form->{id}) {
- $query = qq|
+ 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|
+ 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;
+ }
+ $sth = $dbh->prepare($query);
+ $sth->execute( $form->{pricegroup} ) || $form->dberror($query);
-}
+ $dbh->commit;
+}
sub get_pricegroup {
- my ($self, $myconfig, $form) = @_;
+ 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 $dbh = $form->{dbh};
- my $ref = $sth->fetchrow_hashref(NAME_lc);
-
- for (keys %$ref) { $form->{$_} = $ref->{$_} }
+ my $query = qq|SELECT * FROM pricegroup WHERE id = ?|;
+ my $sth = $dbh->prepare($query);
+ $sth->execute( $form->{id} ) || $form->dberror($query);
- $sth->finish;
+ my $ref = $sth->fetchrow_hashref(NAME_lc);
- # check if it is orphaned
- $query = "SELECT count(*) FROM partscustomer WHERE pricegroup_id = ?";
- $sth = $dbh->prepare($query);
- $sth->execute($form->{id}) || $form->dberror($query);
+ for ( keys %$ref ) { $form->{$_} = $ref->{$_} }
- ($form->{orphaned}) = $sth->fetchrow_array;
- $form->{orphaned} = !$form->{orphaned};
+ $sth->finish;
- $sth->finish;
-
- $dbh->commit;
+ # check if it is orphaned
+ $query = "SELECT count(*) FROM partscustomer WHERE pricegroup_id = ?";
+ $sth = $dbh->prepare($query);
+ $sth->execute( $form->{id} ) || $form->dberror($query);
-}
+ ( $form->{orphaned} ) = $sth->fetchrow_array;
+ $form->{orphaned} = !$form->{orphaned};
+
+ $sth->finish;
+ $dbh->commit;
+
+}
sub description_translations {
- my ($self, $myconfig, $form) = @_;
-
- my $dbh = $form->{dbh};
- my $where = "1 = 1";
- my $var;
- my $ref;
-
- for (qw(partnumber description)) {
- if ($form->{$_}) {
- $var = $dbh->quote($form->like(lc $form->{$_}));
- $where .= " AND lower(p.$_) LIKE $var";
- }
- }
-
- $where .= " AND p.obsolete = '0'";
- $where .= " AND p.id = ".$dbh->quote($form->{id}) if $form->{id};
-
-
- my %ordinal = ( 'partnumber' => 2, 'description' => 3 );
-
- my @a = qw(partnumber description);
- my $sortorder = $form->sort_order(\@a, \%ordinal);
-
- my $query = qq|
+ 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|
+ 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 $sth = $dbh->prepare($query);
+ $sth->execute || $form->dberror($query);
- my $tra;
-
- while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
- push @{ $form->{translations} }, $ref;
+ my $tra;
- # get translations for description
- $tth->execute($ref->{id}) || $form->dberror;
+ while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
+ push @{ $form->{translations} }, $ref;
- while ($tra = $tth->fetchrow_hashref(NAME_lc)) {
- $form->{trans_id} = $ref->{id};
- $tra->{id} = $ref->{id};
- push @{ $form->{translations} }, $tra;
- }
- $tth->finish;
+ # get translations for description
+ $tth->execute( $ref->{id} ) || $form->dberror;
- }
- $sth->finish;
+ while ( $tra = $tth->fetchrow_hashref(NAME_lc) ) {
+ $form->{trans_id} = $ref->{id};
+ $tra->{id} = $ref->{id};
+ push @{ $form->{translations} }, $tra;
+ }
+ $tth->finish;
- &get_language("", $dbh, $form) if $form->{id};
+ }
+ $sth->finish;
- $dbh->commit;
+ &get_language( "", $dbh, $form ) if $form->{id};
-}
+ $dbh->commit;
+}
sub partsgroup_translations {
- my ($self, $myconfig, $form) = @_;
- my $dbh = $form->{dbh};
+ my ( $self, $myconfig, $form ) = @_;
+ my $dbh = $form->{dbh};
- my $where = "1 = 1";
- my $ref;
- my $var;
+ 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};
-
+ 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|
+ 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|
+ 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 $sth = $dbh->prepare($query);
+ $sth->execute || $form->dberror($query);
- my $tra;
-
- while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
- push @{ $form->{translations} }, $ref;
+ my $tra;
- # get translations for partsgroup
- $tth->execute($ref->{id}) || $form->dberror;
+ while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
+ push @{ $form->{translations} }, $ref;
- while ($tra = $tth->fetchrow_hashref(NAME_lc)) {
- $form->{trans_id} = $ref->{id};
- push @{ $form->{translations} }, $tra;
- }
- $tth->finish;
+ # get translations for partsgroup
+ $tth->execute( $ref->{id} ) || $form->dberror;
- }
- $sth->finish;
+ while ( $tra = $tth->fetchrow_hashref(NAME_lc) ) {
+ $form->{trans_id} = $ref->{id};
+ push @{ $form->{translations} }, $tra;
+ }
+ $tth->finish;
- &get_language("", $dbh, $form) if $form->{id};
+ }
+ $sth->finish;
- $dbh->commit;
+ &get_language( "", $dbh, $form ) if $form->{id};
-}
+ $dbh->commit;
+}
sub project_translations {
- my ($self, $myconfig, $form) = @_;
- my $dbh = $form->{dbh};
-
- my $where = "1 = 1";
- my $var;
- my $ref;
-
- for (qw(projectnumber description)) {
- if ($form->{$_}) {
- $var = $dbh->quote($form->like(lc $form->{$_}));
- $where .= " AND lower(p.$_) LIKE $var";
- }
- }
-
- $where .= " AND p.id = ".$dbh->quote($form->{id}) if $form->{id};
-
-
- my %ordinal = ( 'projectnumber' => 2, 'description' => 3 );
-
- my @a = qw(projectnumber description);
- my $sortorder = $form->sort_order(\@a, \%ordinal);
-
- my $query = qq|
+ 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|
+ 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 $sth = $dbh->prepare($query);
+ $sth->execute || $form->dberror($query);
- my $tra;
-
- while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
- push @{ $form->{translations} }, $ref;
+ my $tra;
- # get translations for description
- $tth->execute($ref->{id}) || $form->dberror;
+ while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
+ push @{ $form->{translations} }, $ref;
- while ($tra = $tth->fetchrow_hashref(NAME_lc)) {
- $form->{trans_id} = $ref->{id};
- $tra->{id} = $ref->{id};
- push @{ $form->{translations} }, $tra;
- }
- $tth->finish;
+ # get translations for description
+ $tth->execute( $ref->{id} ) || $form->dberror;
- }
- $sth->finish;
+ while ( $tra = $tth->fetchrow_hashref(NAME_lc) ) {
+ $form->{trans_id} = $ref->{id};
+ $tra->{id} = $ref->{id};
+ push @{ $form->{translations} }, $tra;
+ }
+ $tth->finish;
- &get_language("", $dbh, $form) if $form->{id};
+ }
+ $sth->finish;
- $dbh->commit;
+ &get_language( "", $dbh, $form ) if $form->{id};
-}
+ $dbh->commit;
+}
sub get_language {
- my ($self, $dbh, $form) = @_;
-
- my $query = qq|SELECT * FROM language ORDER BY 2|;
- my $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
+ my ( $self, $dbh, $form ) = @_;
- while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
- push @{ $form->{all_language} }, $ref;
- }
- $sth->finish;
+ my $query = qq|SELECT * FROM language ORDER BY 2|;
+ my $sth = $dbh->prepare($query);
+ $sth->execute || $form->dberror($query);
-}
+ while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
+ push @{ $form->{all_language} }, $ref;
+ }
+ $sth->finish;
+}
sub save_translation {
- my ($self, $myconfig, $form) = @_;
+ my ( $self, $myconfig, $form ) = @_;
- my $dbh = $form->{dbh};
+ my $dbh = $form->{dbh};
- my $query = qq|DELETE FROM translation WHERE trans_id = ?|;
- $sth = $dbh->prepare($query);
- $sth->execute($form->{id})|| $form->dberror($query);
+ my $query = qq|DELETE FROM translation WHERE trans_id = ?|;
+ $sth = $dbh->prepare($query);
+ $sth->execute( $form->{id} ) || $form->dberror($query);
- $query = qq|
+ $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;
+ my $sth = $dbh->prepare($query) || $form->dberror($query);
+
+ foreach my $i ( 1 .. $form->{translation_rows} ) {
+ if ( $form->{"language_code_$i"} ne "" ) {
+ $sth->execute(
+ $form->{id},
+ $form->{"language_code_$i"},
+ $form->{"translation_$i"}
+ );
+ $sth->finish;
+ }
+ }
+ $dbh->commit;
}
-
sub delete_translation {
- my ($self, $myconfig, $form) = @_;
-
- my $dbh = $form->{dbh};
-
- my $query = qq|DELETE FROM translation WHERE trans_id = ?|;
- $sth = $dbh->prepare($query);
- $sth->execute($form->{id})|| $form->dberror($query);
+ my ( $self, $myconfig, $form ) = @_;
+
+ my $dbh = $form->{dbh};
- $dbh->commit;
+ my $query = qq|DELETE FROM translation WHERE trans_id = ?|;
+ $sth = $dbh->prepare($query);
+ $sth->execute( $form->{id} ) || $form->dberror($query);
+
+ $dbh->commit;
}
sub timecard_get_currency {
- my $self = shift @_;
- my $form = shift @_;
- my $dbh = $form->{dbh};
- my $query = qq|SELECT curr FROM customer WHERE id = ?|;
- my $sth = $dbh->prepare($query);
- $sth->execute($form->{customer_id});
- my ($curr) = $sth->fetchrow_array;
- $form->{currency} = $curr;
+ my $self = shift @_;
+ my $form = shift @_;
+ my $dbh = $form->{dbh};
+ my $query = qq|SELECT curr FROM customer WHERE id = ?|;
+ my $sth = $dbh->prepare($query);
+ $sth->execute( $form->{customer_id} );
+ my ($curr) = $sth->fetchrow_array;
+ $form->{currency} = $curr;
}
-
sub project_sales_order {
- my ($self, $myconfig, $form) = @_;
-
- # connect to database
- my $dbh = $form->{dbh};
-
- my $query = qq|SELECT current_date|;
- my ($transdate) = $dbh->selectrow_array($query);
-
- $form->all_years($myconfig, $dbh);
-
- $form->all_projects($myconfig, $dbh, $transdate);
-
- $form->all_employees($myconfig, $dbh, $transdate);
-
- $dbh->commit;
+ my ( $self, $myconfig, $form ) = @_;
-}
+ # connect to database
+ my $dbh = $form->{dbh};
+
+ my $query = qq|SELECT current_date|;
+ my ($transdate) = $dbh->selectrow_array($query);
+
+ $form->all_years( $myconfig, $dbh );
+ $form->all_projects( $myconfig, $dbh, $transdate );
+
+ $form->all_employees( $myconfig, $dbh, $transdate );
+
+ $dbh->commit;
+
+}
sub get_jcitems {
- my ($self, $myconfig, $form) = @_;
-
- my $dbh = $form->{dbh};
-
- my $null;
- my $var;
- my $where;
-
- if ($form->{projectnumber}) {
- ($null, $var) = split /--/, $form->{projectnumber};
- $var = $dbh->quote($var);
- $where .= " AND j.project_id = $var";
- }
-
- if ($form->{employee}) {
- ($null, $var) = split /--/, $form->{employee};
- $var = $dbh->quote($var);
- $where .= " AND j.employee_id = $var";
- }
-
- ($form->{transdatefrom}, $form->{transdateto})
- = $form->from_to(
- $form->{year}, $form->{month}, $form->{interval})
- if $form->{year} && $form->{month};
-
- if ($form->{transdatefrom}) {
- $where .= " AND j.checkedin >= ".
- $dbh->quote($form->{transdatefrom});
- }
- if ($form->{transdateto}) {
- $where .= " AND j.checkedout <= (date ".
- $dbh->quote($form->{transdateto}) .
- " + interval '1 days')";
- }
-
- my $query;
- my $ref;
-
- $query = qq|
+ 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,
@@ -1517,83 +1493,80 @@ sub get_jcitems {
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);
+ 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|
+ # tax accounts
+ $query = qq|
SELECT c.accno
FROM chart c
JOIN partstax pt ON (pt.chart_id = c.id)
WHERE pt.parts_id = ?|;
- my $tth = $dbh->prepare($query) || $form->dberror($query);
- my $ptref;
-
- while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
-
- $tth->execute($ref->{parts_id});
- $ref->{taxaccounts} = "";
- while ($ptref = $tth->fetchrow_hashref(NAME_lc)) {
- $ref->{taxaccounts} .= "$ptref->{accno} ";
- }
- $tth->finish;
- chop $ref->{taxaccounts};
-
- $ref->{amount} = $ref->{sellprice} * $ref->{qty};
-
- push @{ $form->{jcitems} }, $ref;
- }
-
- $sth->finish;
-
- $query = qq|SELECT value FROM defaults WHERE setting_key = 'curr'|;
- ($form->{currency}) = $dbh->selectrow_array($query);
- $form->{currency} =~ s/:.*//;
- $form->{defaultcurrency} = $form->{currency};
-
- $query = qq|
+ my $tth = $dbh->prepare($query) || $form->dberror($query);
+ my $ptref;
+
+ while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
+
+ $tth->execute( $ref->{parts_id} );
+ $ref->{taxaccounts} = "";
+ while ( $ptref = $tth->fetchrow_hashref(NAME_lc) ) {
+ $ref->{taxaccounts} .= "$ptref->{accno} ";
+ }
+ $tth->finish;
+ chop $ref->{taxaccounts};
+
+ $ref->{amount} = $ref->{sellprice} * $ref->{qty};
+
+ push @{ $form->{jcitems} }, $ref;
+ }
+
+ $sth->finish;
+
+ $query = qq|SELECT value FROM defaults WHERE setting_key = 'curr'|;
+ ( $form->{currency} ) = $dbh->selectrow_array($query);
+ $form->{currency} =~ s/:.*//;
+ $form->{defaultcurrency} = $form->{currency};
+
+ $query = qq|
SELECT c.accno, t.rate
FROM tax t
JOIN chart c ON (c.id = t.chart_id)|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
- while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
- $form->{taxaccounts} .= "$ref->{accno} ";
- $form->{"$ref->{accno}_rate"} = $ref->{rate};
- }
- chop $form->{taxaccounts};
- $sth->finish;
-
- $dbh->commit;
-
-}
+ $sth = $dbh->prepare($query);
+ $sth->execute || $form->dberror($query);
+ while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
+ $form->{taxaccounts} .= "$ref->{accno} ";
+ $form->{"$ref->{accno}_rate"} = $ref->{rate};
+ }
+ chop $form->{taxaccounts};
+ $sth->finish;
+ $dbh->commit;
-sub allocate_projectitems {
- my ($self, $myconfig, $form) = @_;
-
- my $dbh = $form->{dbh};
-
- for my $i (1 .. $form->{rowcount}) {
- for (split / /, $form->{"jcitems_$i"}) {
- my ($id, $qty) = split /:/, $_;
- $form->update_balance(
- $dbh, 'jcitems', 'allocated', "id = $id",
- $qty);
- }
- }
-
- $rc = $dbh->commit;
-
- $rc;
-
}
+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;
generated by cgit v1.2.3 (git 2.39.1) at 2024-06-27 17:57:41 +0000