Whitespace formatted and audited JC.pm

git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@452 4979c152-3d1c-0410-bac9-87ea11338e46

2 files changed, 507 insertions, 436 deletions

diff --git a/Changelog b/Changelog
index f24b9eac..1c7914d0 100644
--- a/Changelog
+++ b/Changelog
@@ -16,7 +16,7 @@ Security:
 * Audited IS.pm, GL.pm, IR.pm for SQL injection and moved to new API. (Chris T)
 * Audited User.pm for SQL injection. (Chris T)
 * Audited HR.pm, removed old, stale payroll code, moved to new API (Chris T)
-* Audited OP.pm, IC.pm and moved to new API (Chris T)
+* Audited OP.pm, JC.pm IC.pm and moved to new API (Chris T)
 
 Localization:
 * Moved localization files to standard codes (Seneca)
diff --git a/LedgerSMB/JC.pm b/LedgerSMB/JC.pm
index fcac1b1e..3bcad40e 100755
--- a/LedgerSMB/JC.pm
+++ b/LedgerSMB/JC.pm
@@ -23,7 +23,7 @@
 #
 #======================================================================
 #
-# This file has NOT undergone whitespace cleanup.
+# This file has undergone whitespace cleanup.
 #
 #======================================================================
 #
@@ -40,517 +40,588 @@ use LedgerSMB::Sysconfig;
 
 
 sub get_jcitems {
-  my ($self, $myconfig, $form) = @_;
+	my ($self, $myconfig, $form) = @_;
 
-  # connect to database
-  my $dbh = $form->dbconnect($myconfig);
+	# connect to database
+	my $dbh = $form->{dbh};
 
-  my $query = qq|SELECT current_date|;
-  ($form->{transdate}) = $dbh->selectrow_array($query);
+	my $query = qq|SELECT current_date|;
+	($form->{transdate}) = $dbh->selectrow_array($query);
 
-  ($form->{employee}, $form->{employee_id}) = $form->get_employee($dbh);
+	($form->{employee}, $form->{employee_id}) = $form->get_employee($dbh);
   
-  my $dateformat = $myconfig->{dateformat};
-  $dateformat =~ s/yy/yyyy/;
-  $dateformat =~ s/yyyyyy/yyyy/;
+	my $dateformat = $myconfig->{dateformat};
+	$dateformat =~ s/yy/yyyy/;
+	$dateformat =~ s/yyyyyy/yyyy/;
  
-  if ($form->{id}) {
-    # retrieve timecard/storescard
-    $query = qq|SELECT j.*, to_char(j.checkedin, 'HH24:MI:SS') AS checkedina,
-                to_char(j.checkedout, 'HH24:MI:SS') AS checkedouta,
-		to_char(j.checkedin, '$dateformat') AS transdate,
-		e.name AS employee, p.partnumber,
-		pr.projectnumber, pr.description AS projectdescription,
-		pr.production, pr.completed, pr.parts_id AS project
-                FROM jcitems j
-		JOIN employee e ON (e.id = j.employee_id)
-		JOIN parts p ON (p.id = j.parts_id)
-		JOIN project pr ON (pr.id = j.project_id)
-                WHERE j.id = $form->{id}|;
-    $sth = $dbh->prepare($query);
-    $sth->execute || $form->dberror($query);
-
-    $ref = $sth->fetchrow_hashref(NAME_lc);
+	if ($form->{id}) {
+		# retrieve timecard/storescard
+		$query = qq|
+			SELECT j.*, to_char(j.checkedin, 'HH24:MI:SS') 
+			       AS checkedina, 
+			       to_char(j.checkedout, 'HH24:MI:SS') 
+			       AS checkedouta, 
+			       to_char(j.checkedin, ?) AS transdate,
+			       e.name AS employee, p.partnumber,
+			       pr.projectnumber, 
+			       pr.description AS projectdescription,
+			       pr.production, pr.completed, 
+			       pr.parts_id AS project
+			  FROM jcitems j
+			  JOIN employee e ON (e.id = j.employee_id)
+			  JOIN parts p ON (p.id = j.parts_id)
+			  JOIN project pr ON (pr.id = j.project_id)
+			 WHERE j.id = ?|;
+		$sth = $dbh->prepare($query);
+		$sth->execute($dateformat, $form->{id}) 
+			|| $form->dberror($query);
+
+		$ref = $sth->fetchrow_hashref(NAME_lc);
     
-    for (keys %$ref) { $form->{$_} = $ref->{$_} }
-    $sth->finish;
-    $form->{project} = ($form->{project}) ? "job" : "project";
-    for (qw(checkedin checkedout)) {
-      $form->{$_} = $form->{"${_}a"};
-      delete $form->{"${_}a"};
-    }
-
-    $query = qq|SELECT s.printed, s.spoolfile, s.formname
-                FROM status s
-		WHERE s.formname = '$form->{type}'
-		AND s.trans_id = $form->{id}|;
-    $sth = $dbh->prepare($query);
-    $sth->execute || $form->dberror($query);
-
-    while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
-      $form->{printed} .= "$ref->{formname} " if $ref->{printed};
-      $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
-    }
-    $sth->finish;
-    for (qw(printed queued)) { $form->{$_} =~ s/ +$//g }
-  }
+		for (keys %$ref) { $form->{$_} = $ref->{$_} }
+		$sth->finish;
+		$form->{project} = ($form->{project}) ? "job" : "project";
+		for (qw(checkedin checkedout)) {
+			$form->{$_} = $form->{"${_}a"};
+			delete $form->{"${_}a"};
+		}
+
+		$query = qq|
+			SELECT s.printed, s.spoolfile, s.formname
+			  FROM status s
+			 WHERE s.formname = ?
+			       AND s.trans_id = ?|;
+		$sth = $dbh->prepare($query);
+		$sth->execute($form->{type}, $form->{id}) 
+			|| $form->dberror($query);
+
+		while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
+			$form->{printed} .= "$ref->{formname} " 
+				if $ref->{printed};
+			$form->{queued} .= 
+				"$ref->{formname} $ref->{spoolfile} " 
+					if $ref->{spoolfile};
+		}
+		$sth->finish;
+		for (qw(printed queued)) { $form->{$_} =~ s/ +$//g }
+	}
   
-  JC->jcitems_links($myconfig, $form, $dbh);
+	JC->jcitems_links($myconfig, $form, $dbh);
  
-  # get language codes
-  $query = qq|SELECT *
-              FROM language
-	      ORDER BY 2|;
-  $sth = $dbh->prepare($query);
-  $sth->execute || $form->dberror($query);
-
-  $form->{all_language} = ();
-  while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
-    push @{ $form->{all_language} }, $ref;
-  }
-  $sth->finish;
+	# get language codes
+	$query = qq|SELECT * FROM language ORDER BY 2|;
+	$sth = $dbh->prepare($query);
+	$sth->execute || $form->dberror($query);
+
+	$form->{all_language} = ();
+	while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
+		push @{ $form->{all_language} }, $ref;
+	}
+	$sth->finish;
   
-  $dbh->disconnect;
+	$dbh->commit;
 
 }
 
 
 sub jcitems_links {
-  my ($self, $myconfig, $form, $dbh) = @_;
+	my ($self, $myconfig, $form, $dbh) = @_;
   
-  my $disconnect = 0;
+	my $disconnect = 0;
 
-  if (! $dbh) {
-    $dbh = $form->dbconnect($myconfig);
-    $disconnect = 1;
-  }
+	if (! $dbh) {
+		$dbh = $form->{dbh};
+	}
   
-  my $query;
-
-  if ($form->{project_id}) {
-    $form->{orphaned} = 1;
-    $query = qq|SELECT parts_id
-                FROM project
-	        WHERE id = $form->{project_id}|;
-    if ($dbh->selectrow_array($query)) {
-      $form->{project} = 'job';
-      $query = qq|SELECT id
-                  FROM project
-	          WHERE parts_id > 0
-	          AND production > completed
-	          AND id = $form->{project_id}|;
-      ($form->{orphaned}) = $dbh->selectrow_array($q);
-    } else {
-      $form->{project} = 'project';
-    }
-  }
-
-  JC->jcparts($myconfig, $form, $dbh);
+	my $query;
+
+	if ($form->{project_id}) {
+		$form->{orphaned} = 1;
+		$query = qq|SELECT parts_id FROM project WHERE id = ?|;
+		my $sth = $dbh->prepare($query);
+		$sth->execute($form->{project_id});
+
+		if ($sth->fetchrow_array($query)) {
+			$form->{project} = 'job';
+			$query = qq|
+				SELECT id
+				  FROM project
+				 WHERE parts_id > 0
+				       AND production > completed
+				       AND id = $form->{project_id}|;
+			my $sth = $dbh->prepare($query);
+			$sth->execute($form->{project_id});
+			($form->{orphaned}) = $sth->fetchrow_array();
+			$sth->finish;
+		} else {
+			$form->{project} = 'project';
+		}
+		$sth->finish;
+	}
+
+	JC->jcparts($myconfig, $form, $dbh);
    
-  $form->all_employees($myconfig, $dbh, $form->{transdate});
+	$form->all_employees($myconfig, $dbh, $form->{transdate});
   
-  my $where;
+	my $where;
+
+	if ($form->{transdate}) {
+		$where .= qq| 
+			AND (enddate IS NULL
+				OR enddate >= |.
+					$dbh->quote($form->{transdate}).qq|)
+			AND (startdate <= |.
+				$dbh->quote($form->{transdate}).qq|
+				OR startdate IS NULL)|;
+	}
   
-  if ($form->{transdate}) {
-    $where .= qq| AND (enddate IS NULL
-                       OR enddate >= '$form->{transdate}')
-                  AND (startdate <= '$form->{transdate}'
-		       OR startdate IS NULL)|;
-  }
+	if ($form->{project} eq 'job') {
+		$query = qq|
+			SELECT pr.*
+			  FROM project pr
+			 WHERE pr.parts_id > 0
+			       AND pr.production > pr.completed
+			       $where|;
+	} elsif ($form->{project} eq 'project') {
+		$query = qq|
+			SELECT pr.*
+			  FROM project pr
+			 WHERE pr.parts_id IS NULL
+			       $where|;
+	} else {
+		$query = qq|
+			SELECT pr.*
+			  FROM project pr
+			 WHERE 1=1
+			       $where
+			EXCEPT
+			SELECT pr.*
+			  FROM project pr
+			 WHERE pr.parts_id > 0
+			       AND pr.production = pr.completed|;
+	}
+
+	if ($form->{project_id}) {
+		$query .= qq|
+			UNION
+			SELECT *
+			  FROM project
+			 WHERE id = |.$dbh->quote($form->{project_id});
+	}
   
-  if ($form->{project} eq 'job') {
-    $query = qq|
-		 SELECT pr.*
-		 FROM project pr
-		 WHERE pr.parts_id > 0
-		 AND pr.production > pr.completed
-		 $where|;
-  } elsif ($form->{project} eq 'project') {
-    $query = qq|
-		 SELECT pr.*
-		 FROM project pr
-		 WHERE pr.parts_id IS NULL
-		 $where|;
-  } else {
-    $query = qq|
-    		 SELECT pr.*
-		 FROM project pr
-		 WHERE 1=1
-		 $where
-		 EXCEPT
-		 SELECT pr.*
-		 FROM project pr
-		 WHERE pr.parts_id > 0
-		 AND pr.production = pr.completed|;
-  }
-
-  if ($form->{project_id}) {
-    $query .= qq|
-                 UNION
-		 SELECT *
-		 FROM project
-		 WHERE id = $form->{project_id}|;
-  }
-  
-  $query .= qq|
+	$query .= qq|
                  ORDER BY projectnumber|;
 
-  $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;
-  
-  $dbh->disconnect if $disconnect;
-  
+	while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
+		push @{ $form->{all_project} }, $ref;
+	}
+	$sth->finish;
 }
 
 
 sub jcparts {
-  my ($self, $myconfig, $form, $dbh) = @_;
+	my ($self, $myconfig, $form, $dbh) = @_;
   
-  my ($null, $project_id) = split /--/, $form->{projectnumber};
-  $project_id *= 1;
-
-  my $query = qq|SELECT customer_id
-                 FROM project
-		 WHERE id = $project_id|;
-  my ($customer_id) = $dbh->selectrow_array($query);
-  $customer_id *= 1;
+	my ($null, $project_id) = split /--/, $form->{projectnumber};
+	$project_id = $dbh->quote($project_id);
+
+	my $query = qq|SELECT customer_id FROM project WHERE id = $project_id|;
+	my ($customer_id) = $dbh->selectrow_array($query);
+	$customer_id = $dbh->quote($customer_id);;
   
-  my $where;
-
-  if ($form->{project} eq 'job') {
-    $where = " AND p.income_accno_id IS NULL";
-    if ($form->{type} eq 'storescard') {
-      $where = " AND p.inventory_accno_id > 0
-                 AND p.income_accno_id > 0";
-    }
+	my $where;
+
+	if ($form->{project} eq 'job') {
+		$where = " AND p.income_accno_id IS NULL";
+		if ($form->{type} eq 'storescard') {
+			$where = " AND p.inventory_accno_id > 0
+			           AND p.income_accno_id > 0";
+		}
     
-    $query = qq|SELECT p.id, p.partnumber, p.description, p.sellprice,
-		 p.unit, t.description AS translation
-                 FROM parts p
-		 LEFT JOIN translation t ON (t.trans_id = p.id AND t.language_code = '$form->{language_code}')
-	         WHERE p.obsolete = '0'
-		 $where|;
-  } elsif ($form->{project} eq 'project') {
-    $where = " AND p.inventory_accno_id IS NULL";
-    if ($form->{type} eq 'storescard') {
-      $where = " AND p.inventory_accno_id > 0";
-    }
+		$query = qq|
+			   SELECT p.id, p.partnumber, p.description, 
+			          p.sellprice,
+			          p.unit, t.description AS translation
+			     FROM parts p
+			LEFT JOIN translation t 
+			          ON (t.trans_id = p.id 
+			          AND t.language_code 
+			          = |.$dbh->quote($form->{language_code}).qq|)
+			    WHERE p.obsolete = '0'
+			          $where|;
+	} elsif ($form->{project} eq 'project') {
+		$where = " AND p.inventory_accno_id IS NULL";
+		if ($form->{type} eq 'storescard') {
+		  $where = " AND p.inventory_accno_id > 0";
+		}
     
-    $query = qq|SELECT p.id, p.partnumber, p.description, p.sellprice,
-		 p.unit, t.description AS translation 
-		 FROM parts p 
-		 LEFT JOIN translation t ON (t.trans_id = p.id AND t.language_code = '$form->{language_code}')
-		 WHERE p.obsolete = '0'
-		 AND p.assembly = '0'
-		 $where|;
-  } else {
+		$query = qq|
+			   SELECT p.id, p.partnumber, p.description, 
+			          p.sellprice, p.unit, 
+			          t.description AS translation 
+			     FROM parts p 
+			LEFT JOIN translation t 
+			          ON (t.trans_id = p.id 
+			          AND t.language_code 
+			          = |.$dbh->quote($form->{language_code}).qq|)
+			    WHERE p.obsolete = '0'
+			          AND p.assembly = '0' $where|;
+	} else {
     
-    $query = qq|SELECT p.id, p.partnumber, p.description, p.sellprice,
-		 p.unit, t.description AS translation
-                 FROM parts p
-		 LEFT JOIN translation t ON (t.trans_id = p.id AND t.language_code = '$form->{language_code}')
-	         WHERE p.obsolete = '0'
-		 AND p.income_accno_id IS NULL
-		 UNION
-                 SELECT p.id, p.partnumber, p.description, p.sellprice,
-		 p.unit, t.description AS translation 
-		 FROM parts p 
-		 LEFT JOIN translation t ON (t.trans_id = p.id AND t.language_code = '$form->{language_code}')
-		 WHERE p.obsolete = '0'
-		 AND p.assembly = '0'
-		 AND p.inventory_accno_id IS NULL|;
-  }
-
-  $query .= qq|
+		$query = qq|
+			   SELECT p.id, p.partnumber, p.description, 
+			          p.sellprice, p.unit, 
+			          t.description AS translation
+			     FROM parts p
+			LEFT JOIN translation t 
+			          ON (t.trans_id = p.id 
+			          AND t.language_code 
+			          = |.$dbh->quote($form->{language_code}).qq|)
+			    WHERE p.obsolete = '0'
+			          AND p.income_accno_id IS NULL
+			UNION
+			   SELECT p.id, p.partnumber, p.description, 
+			          p.sellprice, p.unit, 
+			          t.description AS translation 
+			     FROM parts p 
+			LEFT JOIN translation t 
+			          ON (t.trans_id = p.id 
+			          AND t.language_code 
+			          = |.$dbh->quote($form->{language_code}).qq|)
+			    WHERE p.obsolete = '0'
+			          AND p.assembly = '0'
+			          AND p.inventory_accno_id IS NULL|;
+	}
+
+	$query .= qq|
 		 ORDER BY 2|;
-  my $sth = $dbh->prepare($query);
-  $sth->execute || $form->dberror($query);
+	my $sth = $dbh->prepare($query);
+	$sth->execute || $form->dberror($query);
 
-  my $pmh = PriceMatrix::price_matrix_query($dbh, $form);
-  IS::exchangerate_defaults($dbh, $form);
+	my $pmh = PriceMatrix::price_matrix_query($dbh, $form);
+	IS::exchangerate_defaults($dbh, $form);
 
-  while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
-    $ref->{description} = $ref->{translation} if $ref->{translation};
-    PriceMatrix::price_matrix($pmh, $ref, $form->{transdate}, 4, $form, $myconfig);
-    push @{ $form->{all_parts} }, $ref;
-  }
-  $sth->finish;
+	while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
+		$ref->{description} = $ref->{translation} 
+			if $ref->{translation};
+		PriceMatrix::price_matrix(
+			$pmh, $ref, $form->{transdate}, 4, $form, $myconfig);
+		push @{ $form->{all_parts} }, $ref;
+	}
+	$sth->finish;
 
 }
 
 
 sub delete_timecard {
-  my ($self, $myconfig, $form) = @_;
+	my ($self, $myconfig, $form) = @_;
 
-  # connect to database
-  my $dbh = $form->dbconnect_noauto($myconfig);
+	# connect to database
+	my $dbh = $form->{dbh};
  
-  my %audittrail = ( tablename  => 'jcitems',
-                     reference  => $form->{id},
-		     formname   => $form->{type},
-		     action     => 'deleted',
-		     id         => $form->{id} );
-
-  $form->audittrail($dbh, "", \%audittrail);
+	my %audittrail = ( 
+		tablename  => 'jcitems',
+		reference  => $form->{id},
+		formname   => $form->{type},
+		action     => 'deleted',
+		id         => $form->{id} );
+
+	$form->audittrail($dbh, "", \%audittrail);
  
-  my $query = qq|DELETE FROM jcitems
-                 WHERE id = $form->{id}|;
-  $dbh->do($query) || $form->dberror($query);
-
-  # delete spool files
-  $query = qq|SELECT spoolfile FROM status
-              WHERE formname = '$form->{type}'
-	      AND trans_id = $form->{id}
-	      AND spoolfile IS NOT NULL|;
-  my $sth = $dbh->prepare($query);
-  $sth->execute || $form->dberror($query);
-
-  my $spoolfile;
-  my @spoolfiles = ();
-
-  while (($spoolfile) = $sth->fetchrow_array) {
-    push @spoolfiles, $spoolfile;
-  }
-  $sth->finish;
-
-  # delete status entries
-  $query = qq|DELETE FROM status
-              WHERE formname = '$form->{type}'
-	      AND trans_id = $form->{id}|;
-  $dbh->do($query) || $form->dberror($query);
-
-  my $rc = $dbh->commit;
-
-  if ($rc) {
-    foreach $spoolfile (@spoolfiles) {
-      unlink "${LedgerSMB::Sysconfig::spool}/$spoolfile" if $spoolfile;
-    }
-  }
-
-  $dbh->disconnect;
-
-  $rc;
+	my $query = qq|DELETE FROM jcitems WHERE id = ?|;
+	my $sth = $dbh->prepare($query); 
+	$sth->execute($form->{id})|| $form->dberror($query);
+
+	# delete spool files
+	$query = qq|
+		SELECT spoolfile FROM status
+		 WHERE formname = ?
+		       AND trans_id = ?
+		       AND spoolfile IS NOT NULL|;
+	my $sth = $dbh->prepare($query);
+	$sth->execute($form->{type}, $form->{id}) || $form->dberror($query);
+
+	my $spoolfile;
+	my @spoolfiles = ();
+
+	while (($spoolfile) = $sth->fetchrow_array) {
+		push @spoolfiles, $spoolfile;
+	}
+	$sth->finish;
+
+	# delete status entries
+	$query = qq|
+		DELETE 
+		  FROM status
+		 WHERE formname = ?
+		       AND trans_id = ?|;
+	my $sth = $dbh->prepare($query);
+	$sth->execute($form->{type}, $form->{id}) || $form->dberror($query);
+
+	my $rc = $dbh->commit;
+
+	if ($rc) {
+		foreach $spoolfile (@spoolfiles) {
+			unlink "${LedgerSMB::Sysconfig::spool}/$spoolfile" 
+				if $spoolfile;
+		}
+	}
+
+	$dbh->{commit};
+
+	$rc;
 
 }
 
 
 sub jcitems {
-  my ($self, $myconfig, $form) = @_;
+	my ($self, $myconfig, $form) = @_;
 
-  # connect to database
-  my $dbh = $form->dbconnect($myconfig);
+	# connect to database
+	my $dbh = $form->{dbh};
   
-  my $query;
-  my $where = "1 = 1";
-  my $null;
-  my $var;
+	my $query;
+	my $where = "1 = 1";
+	my $null;
+	my $var;
   
-  if ($form->{projectnumber}) {
-    ($null, $var) = split /--/, $form->{projectnumber};
-    $where .= " AND j.project_id = $var";
-
-    $query = qq|SELECT parts_id
-                FROM project
-		WHERE id = $var|;
-    my ($job) = $dbh->selectrow_array($query);
-    $form->{project} = ($job) ? "job" : "project";
+	if ($form->{projectnumber}) {
+		($null, $var) = split /--/, $form->{projectnumber};
+		$var = $dbh->quote($var);
+		$where .= " AND j.project_id = $var";
+
+		$query = qq|SELECT parts_id FROM project WHERE id = $var|;
+		my ($job) = $dbh->selectrow_array($query);
+		$form->{project} = ($job) ? "job" : "project";
     
-  }
-  if ($form->{partnumber}) {
-    ($null, $var) = split /--/, $form->{partnumber};
-    $where .= " AND j.parts_id = $var";
-
-    $query = qq|SELECT inventory_accno_id
-                FROM parts
-		WHERE id = $var|;
-    my ($job) = $dbh->selectrow_array($query);
-    $form->{project} = ($job) ? "job" : "project";
+	}
+	if ($form->{partnumber}) {
+		($null, $var) = split /--/, $form->{partnumber};
+		$var = $dbh->quote($var);
+		$where .= " AND j.parts_id = $var";
+
+		$query = qq|
+			SELECT inventory_accno_id
+			  FROM parts
+			 WHERE id = $var|;
+		my ($job) = $dbh->selectrow_array($query);
+		$form->{project} = ($job) ? "job" : "project";
     
-  }
-  if ($form->{employee}) {
-    ($null, $var) = split /--/, $form->{employee};
-    $where .= " AND j.employee_id = $var";
-  }
-  if ($form->{open} || $form->{closed}) {
-    unless ($form->{open} && $form->{closed}) {
-      $where .= " AND j.qty != j.allocated" if $form->{open};
-      $where .= " AND j.qty = j.allocated" if $form->{closed};
-    }
-  }
+	}
+	if ($form->{employee}) {
+		($null, $var) = split /--/, $form->{employee};
+		$var = $dbh->quote($var);
+		$where .= " AND j.employee_id = $var";
+	}
+	if ($form->{open} || $form->{closed}) {
+		unless ($form->{open} && $form->{closed}) {
+			$where .= " AND j.qty != j.allocated" if $form->{open};
+			$where .= " AND j.qty = j.allocated" 
+				if $form->{closed};
+		}
+	}
   
-  ($form->{startdatefrom}, $form->{startdateto}) = $form->from_to($form->{year}, $form->{month}, $form->{interval}) if $form->{year} && $form->{month};
+	($form->{startdatefrom}, $form->{startdateto}) 
+		= $form->from_to(
+			$form->{year}, $form->{month}, $form->{interval}) 
+				if $form->{year} && $form->{month};
   
-  $where .= " AND j.checkedin >= '$form->{startdatefrom}'" if $form->{startdatefrom};
-  $where .= " AND j.checkedout < date '$form->{startdateto}' + 1" if $form->{startdateto};
-
-  my %ordinal = ( id => 1,
-                  description => 2,
-		  transdate => 7,
-		  partnumber => 9,
-		  projectnumber => 10,
-		  projectdescription => 11,
+	$where .= " AND j.checkedin >= ".$dbh->quote($form->{startdatefrom}) 
+		if $form->{startdatefrom};
+	$where .= " AND j.checkedout < date ".
+		$dbh->quote($form->{startdateto})." + 1" 
+			if $form->{startdateto};
+
+	my %ordinal = ( 
+		id => 1,
+		description => 2,
+		transdate => 7,
+		partnumber => 9,
+		projectnumber => 10,
+		projectdescription => 11,
 		);
   
-  my @a = (transdate, projectnumber);
-  my $sortorder = $form->sort_order(\@a, \%ordinal);
+	my @a = (transdate, projectnumber);
+	my $sortorder = $form->sort_order(\@a, \%ordinal);
   
-  my $dateformat = $myconfig->{dateformat};
-  $dateformat =~ s/yy$/yyyy/;
-  $dateformat =~ s/yyyyyy/yyyy/;
+	my $dateformat = $myconfig->{dateformat};
+	$dateformat =~ s/yy$/yyyy/;
+	$dateformat =~ s/yyyyyy/yyyy/;
   
-  if ($form->{project} eq 'job') {
-    if ($form->{type} eq 'timecard') {
-      $where .= " AND pr.parts_id > 0
-                  AND p.income_accno_id IS NULL";
-    }
+	if ($form->{project} eq 'job') {
+		if ($form->{type} eq 'timecard') {
+			$where .= " 
+				AND pr.parts_id > 0
+				AND p.income_accno_id IS NULL";
+		}
       
-    if ($form->{type} eq 'storescard') {
-      $where .= " AND pr.parts_id > 0
-                  AND p.income_accno_id > 0";
-    }
-  }
-  if ($form->{project} eq 'project') {
-    $where .= " AND pr.parts_id IS NULL";
-  }
+		if ($form->{type} eq 'storescard') {
+			$where .= " 
+				AND pr.parts_id > 0
+				AND p.income_accno_id > 0";
+		}
+	}
+	if ($form->{project} eq 'project') {
+		$where .= " AND pr.parts_id IS NULL";
+	}
   
-  $query = qq|SELECT j.id, j.description, j.qty, j.allocated,
-	      to_char(j.checkedin, 'HH24:MI') AS checkedin,
-	      to_char(j.checkedout, 'HH24:MI') AS checkedout,
-	      to_char(j.checkedin, 'yyyymmdd') AS transdate,
-	      to_char(j.checkedin, '$dateformat') AS transdatea,
-	      to_char(j.checkedin, 'D') AS weekday,
-	      p.partnumber,
-	      pr.projectnumber, pr.description AS projectdescription,
-	      e.employeenumber, e.name AS employee,
-	      to_char(j.checkedin, 'WW') AS workweek, pr.parts_id,
-	      j.sellprice
-	      FROM jcitems j
-	      JOIN parts p ON (p.id = j.parts_id)
-	      JOIN project pr ON (pr.id = j.project_id)
-	      JOIN employee e ON (e.id = j.employee_id)
-	      WHERE $where
-	      ORDER BY employee, employeenumber, $sortorder|;
-
-  $sth = $dbh->prepare($query);
-  $sth->execute || $form->dberror($query);
-
-  while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
-    $ref->{project} = ($ref->{parts_id}) ? "job" : "project";
-    $ref->{transdate} = $ref->{transdatea};
-    delete $ref->{transdatea};
-    push @{ $form->{transactions} }, $ref;
-  }
-  $sth->finish;
+	$query = qq|
+		SELECT j.id, j.description, j.qty, j.allocated,
+		       to_char(j.checkedin, 'HH24:MI') AS checkedin,
+		       to_char(j.checkedout, 'HH24:MI') AS checkedout,
+		       to_char(j.checkedin, 'yyyymmdd') AS transdate,
+		       to_char(j.checkedin, ?) AS transdatea,
+		       to_char(j.checkedin, 'D') AS weekday,
+		       p.partnumber,
+		       pr.projectnumber, pr.description AS projectdescription,
+		       e.employeenumber, e.name AS employee,
+		       to_char(j.checkedin, 'WW') AS workweek, pr.parts_id,
+		       j.sellprice
+		  FROM jcitems j
+		  JOIN parts p ON (p.id = j.parts_id)
+		  JOIN project pr ON (pr.id = j.project_id)
+		  JOIN employee e ON (e.id = j.employee_id)
+		 WHERE $where
+		ORDER BY employee, employeenumber, $sortorder|;
+
+	$sth = $dbh->prepare($query);
+	$sth->execute($dateformat) || $form->dberror($query);
+
+	while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
+		$ref->{project} = ($ref->{parts_id}) ? "job" : "project";
+		$ref->{transdate} = $ref->{transdatea};
+		delete $ref->{transdatea};
+		push @{ $form->{transactions} }, $ref;
+	}
+	$sth->finish;
   
-  $dbh->disconnect;
+	$dbh->commit;
 
 }
 
 
 sub save {
-  my ($self, $myconfig, $form) = @_;
+	my ($self, $myconfig, $form) = @_;
   
-  # connect to database
-  my $dbh = $form->dbconnect_noauto($myconfig);
+	my $dbh = $form->{dbh};
 
-  my $query;
-  my $sth;
+	my $query;
+	my $sth;
   
-  my ($null, $project_id) = split /--/, $form->{projectnumber};
-
-  if ($form->{id}) {
-    # check if it was a job
-    $query = qq|SELECT pr.parts_id, pr.production - pr.completed
-		FROM project pr
-		JOIN jcitems j ON (j.project_id = pr.id)
-		WHERE j.id = $form->{id}|;
-    my ($job_id, $qty) = $dbh->selectrow_array($query);
-
-    if ($job_id && $qty == 0) {
-      $dbh->disconnect;
-      return -1;
-    }
+	my ($null, $project_id) = split /--/, $form->{projectnumber};
+
+	if ($form->{id}) {
+		# check if it was a job
+		$query = qq|
+			SELECT pr.parts_id, pr.production - pr.completed
+			  FROM project pr
+			  JOIN jcitems j ON (j.project_id = pr.id)
+			 WHERE j.id = ?|;
+		$sth = $dbh->prepare($query);
+		$sth->execute($form->{id});
+		my ($job_id, $qty) = $sth->fetchrow_array();
+		$sth->finish;
+		if ($job_id && $qty == 0) {
+			return -1;
+		}
     
-    # check if new one belongs to a job
-    if ($project_id) {
-      $query = qq|SELECT pr.parts_id, pr.production - pr.completed
-		  FROM project pr
-		  WHERE pr.id = $project_id|;
-      my ($job_id, $qty) = $dbh->selectrow_array($query);
-
-      if ($job_id && $qty == 0) {
-	$dbh->disconnect;
-	return -2;
-      }
-    }
+		# check if new one belongs to a job
+		if ($project_id) {
+			$query = qq|
+				SELECT pr.parts_id, 
+				       pr.production - pr.completed
+				  FROM project pr
+				 WHERE pr.id = ?|;
+			$sth = $dbh->prepare($query);
+			$sth->execute($project_id);
+			my ($job_id, $qty) = $sth->fetchrow_array();
+
+			if ($job_id && $qty == 0) {
+				$dbh->disconnect;
+				return -2;
+			}
+		}
     
-  } else {
-    my $uid = localtime;
-    $uid .= "$$";
-
-    $query = qq|INSERT INTO jcitems (description)
-                VALUES ('$uid')|;
-    $dbh->do($query) || $form->dberror($query);
-
-    $query = qq|SELECT id FROM jcitems
-                WHERE description = '$uid'|;
-    ($form->{id}) = $dbh->selectrow_array($query);
-  }
-
-  for (qw(inhour inmin insec outhour outmin outsec)) { $form->{$_} = substr("00$form->{$_}", -2) }
-  for (qw(qty sellprice allocated)) { $form->{$_} = $form->parse_amount($myconfig, $form->{$_}) }
-
-  my $checkedin = "$form->{inhour}$form->{inmin}$form->{insec}";
-  my $checkedout = "$form->{outhour}$form->{outmin}$form->{outsec}";
+	} else {
+		my $uid = localtime;
+		$uid .= "$$";
+
+		$query = qq|INSERT INTO jcitems (description) VALUES ('$uid')|;
+		$dbh->do($query) || $form->dberror($query);
+
+		$query = qq|SELECT id FROM jcitems WHERE description = '$uid'|;
+		($form->{id}) = $dbh->selectrow_array($query);
+	}
+
+	for (qw(inhour inmin insec outhour outmin outsec)) { 
+		$form->{$_} = substr("00$form->{$_}", -2); 
+	}
+	for (qw(qty sellprice allocated)) { 
+		$form->{$_} = $form->parse_amount($myconfig, $form->{$_}); 
+	}
+
+	my $checkedin = "$form->{inhour}$form->{inmin}$form->{insec}";
+	my $checkedout = "$form->{outhour}$form->{outmin}$form->{outsec}";
   
-  my $outdate = $form->{transdate};
-  if ($checkedout < $checkedin) {
-    $outdate = $form->add_date($myconfig, $form->{transdate}, 1, 'days');
-  }
-
-  ($null, $form->{employee_id}) = split /--/, $form->{employee};
-  unless ($form->{employee_id}) {
-    ($form->{employee}, $form->{employee_id}) = $form->get_employee($dbh);
-  } 
-
-  my $parts_id;
-  ($null, $parts_id) = split /--/, $form->{partnumber};
+	my $outdate = $form->{transdate};
+	if ($checkedout < $checkedin) {
+		$outdate = $form->add_date(
+			$myconfig, $form->{transdate}, 1, 'days');
+	}
+
+	($null, $form->{employee_id}) = split /--/, $form->{employee};
+	unless ($form->{employee_id}) {
+		($form->{employee}, $form->{employee_id}) 
+			= $form->get_employee($dbh);
+	} 
+
+	my $parts_id;
+	($null, $parts_id) = split /--/, $form->{partnumber};
   
-  $query = qq|UPDATE jcitems SET
-              project_id = $project_id,
-	      parts_id = $parts_id,
-	      description = |.$dbh->quote($form->{description}).qq|,
-	      qty = $form->{qty},
-	      allocated = $form->{allocated},
-	      sellprice = $form->{sellprice},
-	      fxsellprice = $form->{sellprice},
-	      serialnumber = |.$dbh->quote($form->{serialnumber}).qq|,
-	      checkedin = timestamp '$form->{transdate} $form->{inhour}:$form->{inmin}:$form->{insec}',
-	      checkedout = timestamp '$outdate $form->{outhour}:$form->{outmin}:$form->{outsec}',
-	      employee_id = $form->{employee_id},
-	      notes = |.$dbh->quote($form->{notes}).qq|
-	      WHERE id = $form->{id}|;
-  $dbh->do($query) || $form->dberror($query);
-
-  # save printed, queued
-  $form->save_status($dbh);
-
-  my %audittrail = ( tablename  => 'jcitems',
-                     reference  => $form->{id},
-		     formname   => $form->{type},
-		     action     => 'saved',
-		     id         => $form->{id} );
-
-  $form->audittrail($dbh, "", \%audittrail);
+	$query = qq|
+		UPDATE jcitems 
+		   SET project_id = ?,
+		       parts_id = ?,
+		       description = ?,
+		       qty = ?,
+		       allocated = ?,
+		       sellprice = ?,
+		       fxsellprice = ?,
+		       serialnumber = ?,
+		       checkedin = ?::timestamp,
+		       checkedout = ?::timestamp,
+		       employee_id = ?,
+		       notes = ?
+		 WHERE id = ?|;
+	$sth = $dbh->prepare($query);
+	$sth->execute(
+		$project_id, $parts_id, $form->{description}, $form->{qty},
+		$form->{allocated}, $form->{sellprice}, $form->{sellprice},
+		$form->{serialnumber}, 
+		"$form->{transdate} $form->{inhour}:$form->{inmin}:".
+			$form->{insec},
+		"$outdate $form->{outhour}:$form->{outmin}:$form->{outsec}",
+		$form->{employee_id}, $form->{notes}, $form->{id}
+		) || $form->dberror($query);
+
+	# save printed, queued
+	$form->save_status($dbh);
+
+	my %audittrail = ( 
+		tablename  => 'jcitems',
+		reference  => $form->{id},
+		formname   => $form->{type},
+		action     => 'saved',
+		id         => $form->{id} );
+
+	$form->audittrail($dbh, "", \%audittrail);
   
-  my $rc = $dbh->commit;
+	my $rc = $dbh->commit;
   
-  $rc;
+	$rc;
 
 }