Dear Sir,
I modified the code at sub reverse_invoice function at IS.pm
|
|
| Because, It wasn't working right with the allocated amount |
|
| Can I send it by email to revise it, If you like to modified it I will be regretful. |
|
| because I changed some code, so I don't know why It was written.
|
Advise me,
Best wishes,
Shaekir,
sub reverse_invoice {
my ( $dbh2, $form ) = @_;
my $dbh = $form->{dbh};
#SHC--23Oct-2008 I added it to read another select statment because I need to read some attributes but there are some confilict with the previous select statment which use the same object
my $dbh2 = $form->{dbh};
my $query = qq|
SELECT id FROM ar
WHERE id = ?|;
my $sth;
$sth = $dbh->prepare($query);
$sth->execute( $form->{id} );
my ($id) = $sth->fetchrow_array;
return unless $id;
# reverse inventory items
my $query = qq|
SELECT i.id, i.parts_id, i.qty, i.allocated,i.assemblyitem,i.istariff ,p.assembly,
p.inventory_accno_id , i.istariff
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} ) {
# 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
#SHC--23-10-2008 I search about the product where qty - allocated will be >0 to get the last recient vendor invoice to process the allocated of it
#This is the first case , when I have a stock and I need to reverse item to it
$query = qq|
SELECT id, trans_id, allocated FROM invoice
WHERE parts_id = ?
AND ((ABS(qty) - ABS(allocated)) > 0)
ORDER BY trans_id DESC|;
my $sth2 = $dbh2->prepare($query);
$sth2->execute( $ref->{parts_id} )
|| $form->dberror($query);
#================================================================
#SHC--23-10-2008, If I don't have record this mean , I don't have stock and I sold all what I have
if ($sth2->rows==0){
#SHC--23-10-2008 but in case I sold everything I will couldn't determine the recent vendor invoice because the qty always = allocated in all this product record
#so, I will add this condition , (qty + allocated) = 0) AND (qty <0) Desc
$query = qq|
SELECT id, trans_id, allocated
FROM invoice
WHERE parts_id = ?
AND ((qty + allocated) = 0) AND (qty <0)
ORDER BY trans_id DESC|;
$sth2 = $dbh2->prepare($query);
$sth2->execute( $ref->{parts_id} )
|| $form->dberror($query);
}
#==================================================================
#Idon't know why you add while, I work with one record not more
while ( my $inhref = $sth2->fetchrow_hashref(NAME_lc) ) {
#SHC--23-10-2008 I will bring the current allocated for the current item in the invoice and send it to the update_balance function at Form.pm
$allocated = $ref->{allocated};
$qty = $ref->{qty};
# update invoice
$form->update_balance( $dbh, "invoice", "allocated",
qq|id = $inhref->{id}|,
$allocated );
last if ( ( $ref->{qty} -= $qty ) <= 0 );
}
$sth2->finish;
}
}
$sth->finish;
# 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 = ?|;
$sth = $dbh->prepare($query);
$sth->execute( $form->{id} ) || $form->dberror($query);
$query = qq|DELETE FROM shipto WHERE trans_id = ?|;
$sth = $dbh->prepare($query);
$sth->execute( $form->{id} ) || $form->dberror($query);
$dbh->commit;
}
|