More progress on IS.pm

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

1 files changed, 208 insertions, 190 deletions

diff --git a/LedgerSMB/IS.pm b/LedgerSMB/IS.pm
index 773221a5..7d495376 100755
--- a/LedgerSMB/IS.pm
+++ b/LedgerSMB/IS.pm
@@ -1437,250 +1437,268 @@ sub post_invoice {
 
 
 sub process_assembly {
-  my ($dbh, $form, $id, $totalqty, $project_id) = @_;
-
-  my $query = qq|SELECT a.parts_id, a.qty, p.assembly,
-                 p.partnumber, p.description, p.unit,
-                 p.inventory_accno_id, p.income_accno_id,
-		 p.expense_accno_id
-                 FROM assembly a
-		 JOIN parts p ON (a.parts_id = p.id)
-		 WHERE a.id = $id|;
-  my $sth = $dbh->prepare($query);
-  $sth->execute || $form->dberror($query);
+	my ($dbh2, $form, $id, $totalqty, $project_id) = @_;
+	my $dbh = $form->{dbh};
+	my $query = qq|
+		SELECT a.parts_id, a.qty, p.assembly,
+		       p.partnumber, p.description, p.unit,
+		       p.inventory_accno_id, p.income_accno_id,
+		       p.expense_accno_id
+		  FROM assembly a
+		  JOIN parts p ON (a.parts_id = p.id)
+		 WHERE a.id = ?|;
+	my $sth = $dbh->prepare($query);
+	$sth->execute($id) || $form->dberror($query);
 
-  my $allocated;
+	my $allocated;
   
-  while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
+	while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
 
-    $allocated = 0;
+		$allocated = 0;
     
-    $ref->{inventory_accno_id} *= 1;
-    $ref->{expense_accno_id} *= 1;
+		$ref->{inventory_accno_id} *= 1;
+		$ref->{expense_accno_id} *= 1;
 
-    # multiply by number of assemblies
-    $ref->{qty} *= $totalqty;
+		# multiply by number of assemblies
+		$ref->{qty} *= $totalqty;
     
-    if ($ref->{assembly}) {
-      &process_assembly($dbh, $form, $ref->{parts_id}, $ref->{qty}, $project_id);
-      next;
-    } else {
-      if ($ref->{inventory_accno_id}) {
-	$allocated = &cogs($dbh, $form, $ref->{parts_id}, $ref->{qty}, $project_id);
-      }
-    }
+		if ($ref->{assembly}) {
+			&process_assembly(
+				$dbh, $form, $ref->{parts_id}, $ref->{qty}, 
+				$project_id);
+			next;
+		} else {
+			if ($ref->{inventory_accno_id}) {
+			$allocated = &cogs(
+				$dbh, $form, $ref->{parts_id}, 
+				$ref->{qty}, $project_id);
+			}
+		}
 
-    # save detail record for individual assembly item in invoice table
-    $query = qq|INSERT INTO invoice (trans_id, description, parts_id, qty,
-                sellprice, fxsellprice, allocated, assemblyitem, unit)
-		VALUES
-		($form->{id}, |
-		.$dbh->quote($ref->{description}).qq|,
-		$ref->{parts_id}, $ref->{qty}, 0, 0, $allocated, 't', |
-		.$dbh->quote($ref->{unit}).qq|)|;
-    $dbh->do($query) || $form->dberror($query);
+		$query = qq|
+			INSERT INTO invoice 
+			            (trans_id, description, parts_id, qty,
+ 			            sellprice, fxsellprice, allocated, 
+			            assemblyitem, unit)
+			     VALUES (?, ?, ?, ?, 0, 0, ?, 't', ?)|;
+
+		$sth = $dbh->prepare($query);
+		$sth->execute(
+			$form->{id}, $ref->{description}, $ref->{parts_id}, 
+			$ref->{qty}, $allocated, $ref->{unit})
+				|| $form->dberror($query);
 	 
-  }
+	}
 
-  $sth->finish;
+	$sth->finish;
 
 }
 
 
 sub cogs {
-  my ($dbh, $form, $id, $totalqty, $project_id) = @_;
-
-  my $query = qq|SELECT i.id, i.trans_id, i.qty, i.allocated, i.sellprice,
-                 i.fxsellprice, p.inventory_accno_id, p.expense_accno_id
-		 FROM invoice i, parts p
-		 WHERE i.parts_id = p.id
-		 AND i.parts_id = $id
-		 AND (i.qty + i.allocated) < 0
+	my ($dbh2, $form, $id, $totalqty, $project_id) = @_;
+	my $dbh = $form->{dbh};
+	my $query = qq|
+		   SELECT i.id, i.trans_id, i.qty, i.allocated, i.sellprice,
+		          i.fxsellprice, p.inventory_accno_id, 
+		          p.expense_accno_id
+		     FROM invoice i, parts p
+		    WHERE i.parts_id = p.id
+		      AND i.parts_id = ?
+		      AND (i.qty + i.allocated) < 0
 		 ORDER BY trans_id|;
-  my $sth = $dbh->prepare($query);
-  $sth->execute || $form->dberror($query);
+	my $sth = $dbh->prepare($query);
+	$sth->execute($id) || $form->dberror($query);
 
-  my $allocated = 0;
-  my $qty;
+	my $allocated = 0;
+	my $qty;
   
-  while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
-    if (($qty = (($ref->{qty} * -1) - $ref->{allocated})) > $totalqty) {
-      $qty = $totalqty;
-    }
+	while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
+		if (($qty = (($ref->{qty} * -1) - $ref->{allocated})) 
+								> $totalqty) {
+			$qty = $totalqty;
+		}
     
-    $form->update_balance($dbh,
-			  "invoice",
-			  "allocated",
-			  qq|id = $ref->{id}|,
-			  $qty);
-
-    # total expenses and inventory
-    # sellprice is the cost of the item
-    my $linetotal = $form->round_amount($ref->{sellprice} * $qty, 2);
-
-    # add expense
-    push @{ $form->{acc_trans}{lineitems} }, {
-      chart_id => $ref->{expense_accno_id},
-      amount => $linetotal * -1,
-      project_id => $project_id,
-      invoice_id => $ref->{id} };
-
-    # deduct inventory
-    push @{ $form->{acc_trans}{lineitems} }, {
-      chart_id => $ref->{inventory_accno_id},
-      amount => $linetotal,
-      project_id => $project_id,
-      invoice_id => $ref->{id} };
-
-    # add allocated
-    $allocated += -$qty;
+		$form->update_balance(
+			$dbh, "invoice", "allocated", qq|id = $ref->{id}|, 
+			$qty);
+
+		# total expenses and inventory
+		# sellprice is the cost of the item
+		my $linetotal = $form->round_amount(
+			$ref->{sellprice} * $qty, 2);
+
+		# add expense
+		push @{ $form->{acc_trans}{lineitems} }, {
+			chart_id => $ref->{expense_accno_id},
+			amount => $linetotal * -1,
+			project_id => $project_id,
+			invoice_id => $ref->{id} };
+
+		# deduct inventory
+		push @{ $form->{acc_trans}{lineitems} }, {
+			chart_id => $ref->{inventory_accno_id},
+			amount => $linetotal,
+			project_id => $project_id,
+			invoice_id => $ref->{id} };
+
+		# add allocated
+		$allocated += -$qty;
     
-    last if (($totalqty -= $qty) <= 0);
-  }
+		last if (($totalqty -= $qty) <= 0);
+	}
 
-  $sth->finish;
+	$sth->finish;
 
-  $allocated;
-  
+	$allocated;
+	$dbh->commit;
 }
 
 
 
 sub reverse_invoice {
-  my ($dbh, $form) = @_;
-  
-  my $query = qq|SELECT id FROM ar
-                 WHERE id = $form->{id}|;
-  my ($id) = $dbh->selectrow_array($query);
-
-  return unless $id;
-
-  # reverse inventory items
-  my $query = qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly,
-		 p.inventory_accno_id
-                 FROM invoice i
-		 JOIN parts p ON (i.parts_id = p.id)
-		 WHERE i.trans_id = $form->{id}|;
-  my $sth = $dbh->prepare($query);
-  $sth->execute || $form->dberror($query);
+	my ($dbh2, $form) = @_;
+	my $dbh = $form->{dbh};
+	my $query = qq|
+		SELECT id FROM ar
+		WHERE id = ?|;
 
-  while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
+	my $sth;
+	$sth = $dbh->prepare($query);
+	$sth->execute($form->{id});
+	my ($id) = $sth->fetchrow_array($query);
 
-    if ($ref->{inventory_accno_id} || $ref->{assembly}) {
+	return unless $id;
 
-      # if the invoice item is not an assemblyitem adjust parts onhand
-      if (!$ref->{assemblyitem}) {
-        # adjust onhand in parts table
-	$form->update_balance($dbh,
-	                      "parts",
-			      "onhand",
-			      qq|id = $ref->{parts_id}|,
-			      $ref->{qty});
-      }
+	# reverse inventory items
+	my $query = qq|
+		SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly,
+		       p.inventory_accno_id
+		  FROM invoice i
+		  JOIN parts p ON (i.parts_id = p.id)
+		 WHERE i.trans_id = ?|;
+	my $sth = $dbh->prepare($query);
+	$sth->execute($form->{id}) || $form->dberror($query);
+
+	while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
+
+		if ($ref->{inventory_accno_id} || $ref->{assembly}) {
 
-      # loop if it is an assembly
-      next if ($ref->{assembly});
+			# if the invoice item is not an assemblyitem 
+			# adjust parts onhand
+			if (!$ref->{assemblyitem}) {
+				# adjust onhand in parts table
+				$form->update_balance(
+					$dbh, "parts", "onhand",
+					qq|id = $ref->{parts_id}|, $ref->{qty});
+			}
+
+			# loop if it is an assembly
+			next if ($ref->{assembly});
       
-      # de-allocated purchases
-      $query = qq|SELECT id, trans_id, allocated
-                  FROM invoice
-		  WHERE parts_id = $ref->{parts_id}
-		  AND allocated > 0
-		  ORDER BY trans_id DESC|;
-      my $sth = $dbh->prepare($query);
-      $sth->execute || $form->dberror($query);
-
-      while (my $inhref = $sth->fetchrow_hashref(NAME_lc)) {
-	$qty = $ref->{qty};
-	if (($ref->{qty} - $inhref->{allocated}) > 0) {
-	  $qty = $inhref->{allocated};
-	}
+			# de-allocated purchases
+			$query = qq|
+				  SELECT id, trans_id, allocated
+				    FROM invoice
+				   WHERE parts_id = ?
+				         AND allocated > 0
+				ORDER BY trans_id DESC|;
+			my $sth = $dbh->prepare($query);
+			$sth->execute($ref->{parts_id}) 
+				|| $form->dberror($query);
+
+			while (my $inhref = $sth->fetchrow_hashref(NAME_lc)) {
+				$qty = $ref->{qty};
+				if (($ref->{qty} - $inhref->{allocated}) > 0) {
+					$qty = $inhref->{allocated};
+				}
 	
-	# update invoice
-	$form->update_balance($dbh,
-			      "invoice",
-			      "allocated",
-			      qq|id = $inhref->{id}|,
-			      $qty * -1);
-
-        last if (($ref->{qty} -= $qty) <= 0);
-      }
-      $sth->finish;
-    }
-  }
+				# update invoice
+				$form->update_balance(
+					$dbh, "invoice", "allocated",
+					qq|id = $inhref->{id}|, $qty * -1);
+
+				last if (($ref->{qty} -= $qty) <= 0);
+			}
+			$sth->finish;
+		}
+	}
   
-  $sth->finish;
+	$sth->finish;
   
-  # delete acc_trans
-  $query = qq|DELETE FROM acc_trans
-              WHERE trans_id = $form->{id}|;
-  $dbh->do($query) || $form->dberror($query);
+	# delete acc_trans
+	$query = qq|DELETE FROM acc_trans WHERE trans_id = ?|;
+
+	$sth = $dbh->prepare($query);
+	$sth->execute($form->{id}) || $form->dberror($query);
  
-  # delete invoice entries
-  $query = qq|DELETE FROM invoice
-              WHERE trans_id = $form->{id}|;
-  $dbh->do($query) || $form->dberror($query);
+	# delete invoice entries
+	$query = qq|DELETE FROM invoice WHERE trans_id = ?|;
+	$sth = $dbh->prepare($query);
+	$sth->execute($form->{id}) || $form->dberror($query);
 
-  $query = qq|DELETE FROM shipto
-              WHERE trans_id = $form->{id}|;
-  $dbh->do($query) || $form->dberror($query);
+	$query = qq|DELETE FROM shipto WHERE trans_id = ?|;
+	$sth = $dbh->prepare($query);
+	$sth->execute($form->{id}) || $form->dberror($query);
 
-  $dbh->commit;
+	$dbh->commit;
 
 }
 
 
 
 sub delete_invoice {
-  my ($self, $myconfig, $form) = @_;
+	my ($self, $myconfig, $form) = @_;
 
-  # connect to database
-  my $dbh = $form->dbconnect_noauto($myconfig);
+	my $dbh = $form->{dbh};
+	my $sth;
 
-  &reverse_invoice($dbh, $form);
+	&reverse_invoice($dbh, $form);
   
-  my %audittrail = ( tablename  => 'ar',
-                     reference  => $form->{invnumber},
-		     formname   => $form->{type},
-		     action     => 'deleted',
-		     id         => $form->{id} );
+	my %audittrail = ( 
+		tablename  => 'ar',
+		reference  => $form->{invnumber},
+		formname   => $form->{type},
+		action     => 'deleted',
+		id         => $form->{id} );
  
-  $form->audittrail($dbh, "", \%audittrail);
+	$form->audittrail($dbh, "", \%audittrail);
      
-  # delete AR record
-  my $query = qq|DELETE FROM ar
-                 WHERE id = $form->{id}|;
-  $dbh->do($query) || $form->dberror($query);
-
-  # delete spool files
-  $query = qq|SELECT spoolfile FROM status
-              WHERE trans_id = $form->{id}
-	      AND spoolfile IS NOT NULL|;
-  my $sth = $dbh->prepare($query);
-  $sth->execute || $form->dberror($query);
+	# delete AR record
+	my $query = qq|DELETE FROM ar WHERE id = ?|;
+	$sth = $dbh->prepare($query);
+	$sth->execute($form->{id}) || $form->dberror($query);
 
-  my $spoolfile;
-  my @spoolfiles = ();
+	# delete spool files
+	$query = qq|
+		SELECT spoolfile FROM status
+		 WHERE trans_id = $form->{id} AND spoolfile IS NOT NULL|;
+	$sth = $dbh->prepare($query);
+	$sth->execute($form->{id}) || $form->dberror($query);
+
+	my $spoolfile;
+	my @spoolfiles = ();
   
-  while (($spoolfile) = $sth->fetchrow_array) {
-    push @spoolfiles, $spoolfile;
-  }
-  $sth->finish;  
+	while (($spoolfile) = $sth->fetchrow_array) {
+		push @spoolfiles, $spoolfile;
+	}
+	$sth->finish;  
 
-  # delete status entries
-  $query = qq|DELETE FROM status
-              WHERE trans_id = $form->{id}|;
-  $dbh->do($query) || $form->dberror($query);
+	# delete status entries
+	$query = qq|DELETE FROM status WHERE trans_id = ?|;
+	$sth = $dbh->prepare($query);
+	$sth->execute($form->{id}) || $form->dberror($query);
 
-  my $rc = $dbh->commit;
+	my $rc = $dbh->commit;
 
-  if ($rc) {
-    foreach $spoolfile (@spoolfiles) {
-      unlink "${LedgerSMB::Sysconfig::spool}/$spoolfile" if $spoolfile;
-    }
-  }
+	if ($rc) {
+		foreach $spoolfile (@spoolfiles) {
+		unlink "${LedgerSMB::Sysconfig::spool}/$spoolfile" 
+			if $spoolfile;
+		}
+	}
   
-  $dbh->disconnect;
+  $dbh->commit;
   
   $rc;