From 666fd833584fe2e3618a397fe9d9a9bdf4c5b94b Mon Sep 17 00:00:00 2001 From: einhverfr Date: Thu, 26 Apr 2007 18:00:56 +0000 Subject: Doing a simple Perltidy commit so that I can evaluate differences between the branches and make sure patches are up to date git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@1103 4979c152-3d1c-0410-bac9-87ea11338e46 --- LedgerSMB/OE.pm | 4291 +++++++++++++++++++++++++++---------------------------- 1 file changed, 2130 insertions(+), 2161 deletions(-) (limited to 'LedgerSMB/OE.pm') diff --git a/LedgerSMB/OE.pm b/LedgerSMB/OE.pm index de624ddd..9dc7cea3 100644 --- a/LedgerSMB/OE.pm +++ b/LedgerSMB/OE.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. @@ -23,8 +23,8 @@ # #====================================================================== # -# This file has undergone whitespace cleanup -# +# This file has undergone whitespace cleanup +# #====================================================================== # # Order entry module @@ -36,47 +36,46 @@ package OE; use LedgerSMB::Tax; use LedgerSMB::Sysconfig; - sub transactions { - my ($self, $myconfig, $form) = @_; - - # connect to database - my $dbh = $form->{dbh}; - - my $query; - my $null; - my $var; - my $ordnumber = 'ordnumber'; - my $quotation = '0'; - my $department; - - my $rate = ($form->{vc} eq 'customer') ? 'buy' : 'sell'; - - ($form->{transdatefrom}, $form->{transdateto}) = - $form->from_to($form->{year}, $form->{month}, $form->{interval}) - if $form->{year} && $form->{month}; - - if ($form->{type} =~ /_quotation$/) { - $quotation = '1'; - $ordnumber = 'quonumber'; - } - - my $number = $form->like(lc $form->{$ordnumber}); - my $name = $form->like(lc $form->{$form->{vc}}); - my @dptargs = (); - - for (qw(department employee)) { - if ($form->{$_}) { - ($null, $var) = split /--/, $form->{$_}; - $department .= " AND o.${_}_id = ?"; - push @dptargs, $var; - } - } - - if ($form->{vc} ne 'customer'){ # Sanitize $form->{vc} - $form->{vc} = 'vendor'; - } - my $query = qq| + my ( $self, $myconfig, $form ) = @_; + + # connect to database + my $dbh = $form->{dbh}; + + my $query; + my $null; + my $var; + my $ordnumber = 'ordnumber'; + my $quotation = '0'; + my $department; + + my $rate = ( $form->{vc} eq 'customer' ) ? 'buy' : 'sell'; + + ( $form->{transdatefrom}, $form->{transdateto} ) = + $form->from_to( $form->{year}, $form->{month}, $form->{interval} ) + if $form->{year} && $form->{month}; + + if ( $form->{type} =~ /_quotation$/ ) { + $quotation = '1'; + $ordnumber = 'quonumber'; + } + + my $number = $form->like( lc $form->{$ordnumber} ); + my $name = $form->like( lc $form->{ $form->{vc} } ); + my @dptargs = (); + + for (qw(department employee)) { + if ( $form->{$_} ) { + ( $null, $var ) = split /--/, $form->{$_}; + $department .= " AND o.${_}_id = ?"; + push @dptargs, $var; + } + } + + if ( $form->{vc} ne 'customer' ) { # Sanitize $form->{vc} + $form->{vc} = 'vendor'; + } + my $query = qq| SELECT o.id, o.ordnumber, o.transdate, o.reqdate, o.amount, ct.name, o.netamount, o.$form->{vc}_id, ex.$rate AS exchangerate, o.closed, o.quonumber, @@ -91,35 +90,36 @@ sub transactions { WHERE o.quotation = ? $department|; - my @queryargs = @dptargs; - unshift @queryargs, $quotation; - - my %ordinal = ( - id => 1, - ordnumber => 2, - transdate => 3, - reqdate => 4, - name => 6, - quonumber => 11, - shipvia => 13, - employee => 14, - manager => 15, - curr => 16, - ponumber => 17); - - my @a = (transdate, $ordnumber, name); - push @a, "employee" if $form->{l_employee}; - if ($form->{type} !~ /(ship|receive)_order/) { - push @a, "manager" if $form->{l_manager}; - } - my $sortorder = $form->sort_order(\@a, \%ordinal); - - # build query if type eq (ship|receive)_order - if ($form->{type} =~ /(ship|receive)_order/) { - - my ($warehouse, $warehouse_id) = split /--/, $form->{warehouse}; - - $query = qq| + my @queryargs = @dptargs; + unshift @queryargs, $quotation; + + my %ordinal = ( + id => 1, + ordnumber => 2, + transdate => 3, + reqdate => 4, + name => 6, + quonumber => 11, + shipvia => 13, + employee => 14, + manager => 15, + curr => 16, + ponumber => 17 + ); + + my @a = ( transdate, $ordnumber, name ); + push @a, "employee" if $form->{l_employee}; + if ( $form->{type} !~ /(ship|receive)_order/ ) { + push @a, "manager" if $form->{l_manager}; + } + my $sortorder = $form->sort_order( \@a, \%ordinal ); + + # build query if type eq (ship|receive)_order + if ( $form->{type} =~ /(ship|receive)_order/ ) { + + my ( $warehouse, $warehouse_id ) = split /--/, $form->{warehouse}; + + $query = qq| SELECT DISTINCT o.id, o.ordnumber, o.transdate, o.reqdate, o.amount, ct.name, o.netamount, o.$form->{vc}_id, ex.$rate AS exchangerate, @@ -131,13 +131,13 @@ sub transactions { JOIN orderitems oi ON (oi.trans_id = o.id) JOIN parts p ON (p.id = oi.parts_id)|; - if ($warehouse_id && $form->{type} eq 'ship_order') { - $query .= qq| + if ( $warehouse_id && $form->{type} eq 'ship_order' ) { + $query .= qq| JOIN inventory i ON (oi.parts_id = i.parts_id) |; - } + } - $query .= qq| + $query .= qq| LEFT JOIN employees e ON (o.employee_id = e.id) LEFT JOIN exchangerate ex ON (ex.curr = o.curr @@ -146,10 +146,10 @@ sub transactions { AND (p.inventory_accno_id > 0 OR p.assembly = '1') AND oi.qty != oi.ship $department|; - @queryargs = @dptargs; #reset @queryargs - - if ($warehouse_id && $form->{type} eq 'ship_order') { - $query .= qq| + @queryargs = @dptargs; #reset @queryargs + + if ( $warehouse_id && $form->{type} eq 'ship_order' ) { + $query .= qq| AND i.warehouse_id = ? AND ( SELECT SUM(i.qty) @@ -157,185 +157,192 @@ sub transactions { WHERE oi.parts_id = i.parts_id AND i.warehouse_id = ? ) > 0|; - push(@queryargs, $warehouse_id, $warehouse_id); - } - - } - - if ($form->{"$form->{vc}_id"}) { - $query .= qq| AND o.$form->{vc}_id = $form->{"$form->{vc}_id"}|; - } elsif ($form->{$form->{vc}} ne "") { - $query .= " AND lower(ct.name) LIKE ?"; - push @queryargs, $name; - } - - if ($form->{$ordnumber} ne "") { - $query .= " AND lower(?) LIKE ?"; - push @queryargs, $ordnumber, $number; - $form->{open} = 1; - $form->{closed} = 1; - } - if ($form->{ponumber} ne "") { - $query .= " AND lower(ponumber) LIKE ?"; - push @queryargs, $form->{ponumber}; - } - - if (!$form->{open} && !$form->{closed}) { - $query .= " AND o.id = 0"; - } elsif (!($form->{open} && $form->{closed})) { - $query .= ($form->{open}) ? - " AND o.closed = '0'" : " AND o.closed = '1'"; - } - - if ($form->{shipvia} ne "") { - $var = $form->like(lc $form->{shipvia}); - $query .= " AND lower(o.shipvia) LIKE ?"; - push @queryargs, $var; - } - - if ($form->{description} ne "") { - $var = $form->like(lc $form->{description}); - $query .= " AND o.id IN (SELECT DISTINCT trans_id + push( @queryargs, $warehouse_id, $warehouse_id ); + } + + } + + if ( $form->{"$form->{vc}_id"} ) { + $query .= qq| AND o.$form->{vc}_id = $form->{"$form->{vc}_id"}|; + } + elsif ( $form->{ $form->{vc} } ne "" ) { + $query .= " AND lower(ct.name) LIKE ?"; + push @queryargs, $name; + } + + if ( $form->{$ordnumber} ne "" ) { + $query .= " AND lower(?) LIKE ?"; + push @queryargs, $ordnumber, $number; + $form->{open} = 1; + $form->{closed} = 1; + } + if ( $form->{ponumber} ne "" ) { + $query .= " AND lower(ponumber) LIKE ?"; + push @queryargs, $form->{ponumber}; + } + + if ( !$form->{open} && !$form->{closed} ) { + $query .= " AND o.id = 0"; + } + elsif ( !( $form->{open} && $form->{closed} ) ) { + $query .= + ( $form->{open} ) ? " AND o.closed = '0'" : " AND o.closed = '1'"; + } + + if ( $form->{shipvia} ne "" ) { + $var = $form->like( lc $form->{shipvia} ); + $query .= " AND lower(o.shipvia) LIKE ?"; + push @queryargs, $var; + } + + if ( $form->{description} ne "" ) { + $var = $form->like( lc $form->{description} ); + $query .= " AND o.id IN (SELECT DISTINCT trans_id FROM orderitems WHERE lower(description) LIKE '$var')"; - push @queryargs, $var; - } - - if ($form->{transdatefrom}) { - $query .= " AND o.transdate >= ?"; - push @queryargs, $form->{transdatefrom}; - } - if ($form->{transdateto}) { - $query .= " AND o.transdate <= ?"; - push @queryargs, $form->{transdateto}; - } - - $query .= " ORDER by $sortorder"; - - my $sth = $dbh->prepare($query); - $sth->execute(@queryargs) || $form->dberror($query); - - my %oid = (); - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { - $ref->{exchangerate} = 1 unless $ref->{exchangerate}; - if ($ref->{id} != $oid{id}{$ref->{id}}) { - push @{ $form->{OE} }, $ref; - $oid{vc}{$ref->{curr}}{$ref->{"$form->{vc}_id"}}++; - } - $oid{id}{$ref->{id}} = $ref->{id}; - } - $sth->finish; - - $dbh->commit; - - if ($form->{type} =~ /^consolidate_/) { - @a = (); - foreach $ref (@{ $form->{OE} }) { - push @a, $ref if $oid{vc}{$ref->{curr}} - {$ref->{"$form->{vc}_id"}} > 1; - } - - @{ $form->{OE} } = @a; - } + push @queryargs, $var; + } + + if ( $form->{transdatefrom} ) { + $query .= " AND o.transdate >= ?"; + push @queryargs, $form->{transdatefrom}; + } + if ( $form->{transdateto} ) { + $query .= " AND o.transdate <= ?"; + push @queryargs, $form->{transdateto}; + } + + $query .= " ORDER by $sortorder"; + + my $sth = $dbh->prepare($query); + $sth->execute(@queryargs) || $form->dberror($query); + + my %oid = (); + while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) { + $ref->{exchangerate} = 1 unless $ref->{exchangerate}; + if ( $ref->{id} != $oid{id}{ $ref->{id} } ) { + push @{ $form->{OE} }, $ref; + $oid{vc}{ $ref->{curr} }{ $ref->{"$form->{vc}_id"} }++; + } + $oid{id}{ $ref->{id} } = $ref->{id}; + } + $sth->finish; + + $dbh->commit; + + if ( $form->{type} =~ /^consolidate_/ ) { + @a = (); + foreach $ref ( @{ $form->{OE} } ) { + push @a, $ref + if $oid{vc}{ $ref->{curr} }{ $ref->{"$form->{vc}_id"} } > 1; + } + + @{ $form->{OE} } = @a; + } } - - sub save { - my ($self, $myconfig, $form) = @_; - - $form->db_prepare_vars("quonumber", "transdate", "vendor_id", - "customer_id", "reqdate", "taxincluded", "shippingpoint", - "shipvia", "currency", "department_id", - "employee_id", "language_code", "ponumber", "terms"); - # connect to database, turn off autocommit - my $dbh = $form->{dbh}; - my @queryargs; - my $quotation; - my $ordnumber; - my $numberfld; - $form->{vc} = ($form->{vc} eq 'customer') ? 'customer': 'vendor'; - if ($form->{type} =~ /_order$/) { - $quotation = "0"; - $ordnumber = "ordnumber"; - $numberfld = ($form->{vc} eq 'customer') ? "sonumber" : - "ponumber"; - } else { - $quotation = "1"; - $ordnumber = "quonumber"; - $numberfld = ($form->{vc} eq 'customer') ? "sqnumber" : - "rfqnumber"; - } - - $form->{"$ordnumber"} = $form->update_defaults( - $myconfig, $numberfld, $dbh) - unless $form->{ordnumber}; - - - my $query; - my $sth; - my $null; - my $exchangerate = 0; - - ($null, $form->{employee_id}) = split /--/, $form->{employee}; - if (! $form->{employee_id}) { - ($form->{employee}, $form->{employee_id}) = - $form->get_employee($dbh); - $form->{employee} = "$form->{employee}--$form->{employee_id}"; - } - - my $ml = ($form->{type} eq 'sales_order') ? 1 : -1; - - $query = qq| + my ( $self, $myconfig, $form ) = @_; + + $form->db_prepare_vars( + "quonumber", "transdate", "vendor_id", "customer_id", + "reqdate", "taxincluded", "shippingpoint", "shipvia", + "currency", "department_id", "employee_id", "language_code", + "ponumber", "terms" + ); + + # connect to database, turn off autocommit + my $dbh = $form->{dbh}; + my @queryargs; + my $quotation; + my $ordnumber; + my $numberfld; + $form->{vc} = ( $form->{vc} eq 'customer' ) ? 'customer' : 'vendor'; + if ( $form->{type} =~ /_order$/ ) { + $quotation = "0"; + $ordnumber = "ordnumber"; + $numberfld = + ( $form->{vc} eq 'customer' ) + ? "sonumber" + : "ponumber"; + } + else { + $quotation = "1"; + $ordnumber = "quonumber"; + $numberfld = + ( $form->{vc} eq 'customer' ) + ? "sqnumber" + : "rfqnumber"; + } + + $form->{"$ordnumber"} = + $form->update_defaults( $myconfig, $numberfld, $dbh ) + unless $form->{ordnumber}; + + my $query; + my $sth; + my $null; + my $exchangerate = 0; + + ( $null, $form->{employee_id} ) = split /--/, $form->{employee}; + if ( !$form->{employee_id} ) { + ( $form->{employee}, $form->{employee_id} ) = $form->get_employee($dbh); + $form->{employee} = "$form->{employee}--$form->{employee_id}"; + } + + my $ml = ( $form->{type} eq 'sales_order' ) ? 1 : -1; + + $query = qq| SELECT p.assembly, p.project_id FROM parts p WHERE p.id = ?|; - my $pth = $dbh->prepare($query) || $form->dberror($query); - - - if ($form->{id}) { - $query = qq|SELECT id FROM oe WHERE id = $form->{id}|; - - if ($dbh->selectrow_array($query)) { - &adj_onhand($dbh, $form, $ml) - if $form->{type} =~ /_order$/; - - $query = qq|DELETE FROM orderitems 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); - - } else { # id is not in the database - delete $form->{id}; - } - - } - - my $did_insert = 0; - if (! $form->{id}) { - $query = qq|SELECT nextval('id')|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - ($form->{id}) = $sth->fetchrow_array; - $sth->finish; - - my $uid = localtime; - $uid .= "$$"; - if (!$form->{reqdate}){ - $form->{reqdate} = undef; - } - if (!$form->{transdate}){ - $form->{transdate} = "now"; - } - - if (($form->{closed} ne 't') and ($form->{closed} ne "1")){ - $form->{closed} = 'f'; - } - # $form->{id} is safe because it is only pulled *from* the db. - $query = qq| + my $pth = $dbh->prepare($query) || $form->dberror($query); + + if ( $form->{id} ) { + $query = qq|SELECT id FROM oe WHERE id = $form->{id}|; + + if ( $dbh->selectrow_array($query) ) { + &adj_onhand( $dbh, $form, $ml ) + if $form->{type} =~ /_order$/; + + $query = qq|DELETE FROM orderitems 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); + + } + else { # id is not in the database + delete $form->{id}; + } + + } + + my $did_insert = 0; + if ( !$form->{id} ) { + $query = qq|SELECT nextval('id')|; + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + ( $form->{id} ) = $sth->fetchrow_array; + $sth->finish; + + my $uid = localtime; + $uid .= "$$"; + if ( !$form->{reqdate} ) { + $form->{reqdate} = undef; + } + if ( !$form->{transdate} ) { + $form->{transdate} = "now"; + } + + if ( ( $form->{closed} ne 't' ) and ( $form->{closed} ne "1" ) ) { + $form->{closed} = 'f'; + } + + # $form->{id} is safe because it is only pulled *from* the db. + $query = qq| INSERT INTO oe (id, ordnumber, quonumber, transdate, vendor_id, customer_id, reqdate, shippingpoint, shipvia, @@ -347,211 +354,207 @@ sub save { ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)|; - @queryargs = ( - $form->{ordnumber}, $form->{quonumber}, - $form->{transdate}, $form->{vendor_id}, - $form->{customer_id}, $form->{reqdate}, - $form->{shippingpoint}, $form->{shipvia}, - $form->{notes}, $form->{intnotes}, $form->{currency}, - $form->{closed}, $form->{department_id}, - $form->{employee_id}, $form->{language_code}, - $form->{ponumber}, $form->{terms}, $quotation); - $sth = $dbh->prepare($query); - $sth->execute(@queryargs) || $form->dberror($query); - $sth->finish; - - @queries = $form->run_custom_queries('oe', 'INSERT'); - - } - - my $amount; - my $linetotal; - my $discount; - my $project_id; - my $taxrate; - my $taxamount; - my $fxsellprice; - my %taxbase; - my @taxaccounts; - my %taxaccounts; - my $netamount = 0; - - my $rowcount = $form->{rowcount}; - for my $i (1 .. $rowcount) { - $form->db_prepare_vars("orderitems_id_$i", "id_$i", - "description_$i", "project_id_$i", "ship_$i"); - - for (qw(qty ship)) { - $form->{"${_}_$i"} = $form->parse_amount( - $myconfig, $form->{"${_}_$i"} - ); - } - - $form->{"discount_$i"} = $form->parse_amount( - $myconfig, $form->{"discount_$i"} - ) / 100; - - $form->{"sellprice_$i"} = $form->parse_amount( - $myconfig, $form->{"sellprice_$i"} - ); - - if ($form->{"qty_$i"}) { - $pth->execute($form->{"id_$i"}); - $ref = $pth->fetchrow_hashref(NAME_lc); - for (keys %$ref) { $form->{"${_}_$i"} = $ref->{$_} } - $pth->finish; - - $fxsellprice = $form->{"sellprice_$i"}; - - my ($dec) = ($form->{"sellprice_$i"} =~ /\.(\d+)/); - $dec = length $dec; - my $decimalplaces = ($dec > 2) ? $dec : 2; - - $discount = $form->round_amount( - $form->{"sellprice_$i"} * - $form->{"discount_$i"}, - $decimalplaces - ); - $form->{"sellprice_$i"} = $form->round_amount( - $form->{"sellprice_$i"} - $discount, - $decimalplaces - ); - - $linetotal = $form->round_amount( - $form->{"sellprice_$i"} * $form->{"qty_$i"}, 2 - ); - - @taxaccounts = Tax::init_taxes($form, - $form->{"taxaccounts_$i"}); - if ($form->{taxincluded}) { - $taxamount = Tax::calculate_taxes(\@taxaccounts, - $form, $linetotal, 1); - $form->{"sellprice_$i"} = Tax::extract_taxes(\@taxaccounts, - $form, $form->{"sellprice_$i"}); - $taxbase = Tax::extract_taxes(\@taxaccounts, - $form, $linetotal); - } else { - $taxamount = Tax::apply_taxes(\@taxaccounts, - $form, $linetotal); - $taxbase = $linetotal; - } - - if (@taxaccounts && $form->round_amount($taxamount, 2) - == 0) { - if ($form->{taxincluded}) { - foreach $item (@taxaccounts) { - $taxamount = - $form->round_amount( - $item->value, 2); - $taxaccounts{$item->account} += - $taxamount; - $taxdiff += $taxamount; - $taxbase{$item->account} += - $taxbase; - } - $taxaccounts{$taxaccounts[0]->account} - += $taxdiff; - } else { - foreach $item (@taxaccounts) { - $taxaccounts{$item->account} += - $item->value; - $taxbase{$item->account} += - $taxbase; - } - } - } else { - foreach $item (@taxaccounts) { - $taxaccounts{$item->account} += - $item->value; - $taxbase{$item->account} += $taxbase; - } - } - - $netamount += $form->{"sellprice_$i"} - * $form->{"qty_$i"}; - - if ($form->{"projectnumber_$i"} ne "") { - ($null, $project_id) - = split /--/, - $form->{"projectnumber_$i"}; - } - $project_id = $form->{"project_id_$i"} - if $form->{"project_id_$i"}; - - if (!$form->{"reqdate_$i"}){ - $form->{"reqdate_$i"} = undef; - } - - @queryargs = (); - # save detail record in orderitems table - $query = qq|INSERT INTO orderitems (|; - if ($form->{"orderitems_id_$i"}){ - $query .= "id, "; - } - $query .= qq| + @queryargs = ( + $form->{ordnumber}, $form->{quonumber}, + $form->{transdate}, $form->{vendor_id}, + $form->{customer_id}, $form->{reqdate}, + $form->{shippingpoint}, $form->{shipvia}, + $form->{notes}, $form->{intnotes}, + $form->{currency}, $form->{closed}, + $form->{department_id}, $form->{employee_id}, + $form->{language_code}, $form->{ponumber}, + $form->{terms}, $quotation + ); + $sth = $dbh->prepare($query); + $sth->execute(@queryargs) || $form->dberror($query); + $sth->finish; + + @queries = $form->run_custom_queries( 'oe', 'INSERT' ); + + } + + my $amount; + my $linetotal; + my $discount; + my $project_id; + my $taxrate; + my $taxamount; + my $fxsellprice; + my %taxbase; + my @taxaccounts; + my %taxaccounts; + my $netamount = 0; + + my $rowcount = $form->{rowcount}; + for my $i ( 1 .. $rowcount ) { + $form->db_prepare_vars( + "orderitems_id_$i", "id_$i", + "description_$i", "project_id_$i", + "ship_$i" + ); + + for (qw(qty ship)) { + $form->{"${_}_$i"} = + $form->parse_amount( $myconfig, $form->{"${_}_$i"} ); + } + + $form->{"discount_$i"} = + $form->parse_amount( $myconfig, $form->{"discount_$i"} ) / 100; + + $form->{"sellprice_$i"} = + $form->parse_amount( $myconfig, $form->{"sellprice_$i"} ); + + if ( $form->{"qty_$i"} ) { + $pth->execute( $form->{"id_$i"} ); + $ref = $pth->fetchrow_hashref(NAME_lc); + for ( keys %$ref ) { $form->{"${_}_$i"} = $ref->{$_} } + $pth->finish; + + $fxsellprice = $form->{"sellprice_$i"}; + + my ($dec) = ( $form->{"sellprice_$i"} =~ /\.(\d+)/ ); + $dec = length $dec; + my $decimalplaces = ( $dec > 2 ) ? $dec : 2; + + $discount = + $form->round_amount( + $form->{"sellprice_$i"} * $form->{"discount_$i"}, + $decimalplaces ); + $form->{"sellprice_$i"} = + $form->round_amount( $form->{"sellprice_$i"} - $discount, + $decimalplaces ); + + $linetotal = + $form->round_amount( $form->{"sellprice_$i"} * $form->{"qty_$i"}, + 2 ); + + @taxaccounts = Tax::init_taxes( $form, $form->{"taxaccounts_$i"} ); + if ( $form->{taxincluded} ) { + $taxamount = + Tax::calculate_taxes( \@taxaccounts, $form, $linetotal, 1 ); + $form->{"sellprice_$i"} = + Tax::extract_taxes( \@taxaccounts, $form, + $form->{"sellprice_$i"} ); + $taxbase = + Tax::extract_taxes( \@taxaccounts, $form, $linetotal ); + } + else { + $taxamount = + Tax::apply_taxes( \@taxaccounts, $form, $linetotal ); + $taxbase = $linetotal; + } + + if ( @taxaccounts && $form->round_amount( $taxamount, 2 ) == 0 ) { + if ( $form->{taxincluded} ) { + foreach $item (@taxaccounts) { + $taxamount = $form->round_amount( $item->value, 2 ); + $taxaccounts{ $item->account } += $taxamount; + $taxdiff += $taxamount; + $taxbase{ $item->account } += $taxbase; + } + $taxaccounts{ $taxaccounts[0]->account } += $taxdiff; + } + else { + foreach $item (@taxaccounts) { + $taxaccounts{ $item->account } += $item->value; + $taxbase{ $item->account } += $taxbase; + } + } + } + else { + foreach $item (@taxaccounts) { + $taxaccounts{ $item->account } += $item->value; + $taxbase{ $item->account } += $taxbase; + } + } + + $netamount += $form->{"sellprice_$i"} * $form->{"qty_$i"}; + + if ( $form->{"projectnumber_$i"} ne "" ) { + ( $null, $project_id ) = split /--/, + $form->{"projectnumber_$i"}; + } + $project_id = $form->{"project_id_$i"} + if $form->{"project_id_$i"}; + + if ( !$form->{"reqdate_$i"} ) { + $form->{"reqdate_$i"} = undef; + } + + @queryargs = (); + + # save detail record in orderitems table + $query = qq|INSERT INTO orderitems (|; + if ( $form->{"orderitems_id_$i"} ) { + $query .= "id, "; + } + $query .= qq| trans_id, parts_id, description, qty, sellprice, discount, unit, reqdate, project_id, ship, serialnumber, notes) VALUES (|; - if ($form->{"orderitems_id_$i"}){ - $query .= "?, "; - push @queryargs, $form->{"orderitems_id_$i"}; - } - $query .= qq| ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)|; - $sth = $dbh->prepare($query); - push (@queryargs, - $form->{id}, $form->{"id_$i"}, - $form->{"description_$i"}, $form->{"qty_$i"}, - $fxsellprice, $form->{"discount_$i"}, - $form->{"unit_$i"}, $form->{"reqdate_$i"}, - $project_id, $form->{"ship_$i"}, - $form->{"serialnumber_$i"}, - $form->{"notes_$i"}); - $sth->execute(@queryargs) || $form->dberror($query); - - $form->{"sellprice_$i"} = $fxsellprice; - } - $form->{"discount_$i"} *= 100; - } - - - # set values which could be empty - for (qw(vendor_id customer_id taxincluded closed quotation)) - { $form->{$_} *= 1 } - - # add up the tax - my $tax = 0; - for (keys %taxaccounts) { $tax += $taxaccounts{$_} } - - $amount = $form->round_amount($netamount + $tax, 2); - $netamount = $form->round_amount($netamount, 2); - - if ($form->{currency} eq $form->{defaultcurrency}) { - $form->{exchangerate} = 1; - } else { - $exchangerate = $form->check_exchangerate( - $myconfig, $form->{currency}, $form->{transdate}, - ($form->{vc} eq 'customer') ? 'buy' : 'sell'); - } - - $form->{exchangerate} = ($exchangerate) ? $exchangerate : - $form->parse_amount($myconfig, $form->{exchangerate}); - - - ($null, $form->{department_id}) = split(/--/, $form->{department}); - for (qw(department_id terms)) { $form->{$_} *= 1 } - if ($did_insert){ - $query = qq| + if ( $form->{"orderitems_id_$i"} ) { + $query .= "?, "; + push @queryargs, $form->{"orderitems_id_$i"}; + } + $query .= qq| ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)|; + $sth = $dbh->prepare($query); + push( @queryargs, + $form->{id}, $form->{"id_$i"}, + $form->{"description_$i"}, $form->{"qty_$i"}, + $fxsellprice, $form->{"discount_$i"}, + $form->{"unit_$i"}, $form->{"reqdate_$i"}, + $project_id, $form->{"ship_$i"}, + $form->{"serialnumber_$i"}, $form->{"notes_$i"} ); + $sth->execute(@queryargs) || $form->dberror($query); + + $form->{"sellprice_$i"} = $fxsellprice; + } + $form->{"discount_$i"} *= 100; + } + + # set values which could be empty + for (qw(vendor_id customer_id taxincluded closed quotation)) { + $form->{$_} *= 1; + } + + # add up the tax + my $tax = 0; + for ( keys %taxaccounts ) { $tax += $taxaccounts{$_} } + + $amount = $form->round_amount( $netamount + $tax, 2 ); + $netamount = $form->round_amount( $netamount, 2 ); + + if ( $form->{currency} eq $form->{defaultcurrency} ) { + $form->{exchangerate} = 1; + } + else { + $exchangerate = + $form->check_exchangerate( $myconfig, $form->{currency}, + $form->{transdate}, + ( $form->{vc} eq 'customer' ) ? 'buy' : 'sell' ); + } + + $form->{exchangerate} = + ($exchangerate) + ? $exchangerate + : $form->parse_amount( $myconfig, $form->{exchangerate} ); + + ( $null, $form->{department_id} ) = split( /--/, $form->{department} ); + for (qw(department_id terms)) { $form->{$_} *= 1 } + if ($did_insert) { + $query = qq| UPDATE oe SET amount = ?, netamount = ?, taxincluded = ? WHERE id = ?|; - @queryargs = ($amount, $netamount, $form->{taxincluded}, - $form->{id}); - } else { - # save OE record - $query = qq| + @queryargs = ( $amount, $netamount, $form->{taxincluded}, $form->{id} ); + } + else { + + # save OE record + $query = qq| UPDATE oe set ordnumber = ?, quonumber = ?, @@ -576,215 +579,190 @@ sub save { terms = ? WHERE id = ?|; - if (!$form->{reqdate}){ - $form->{reqdate} = undef; - } - - @queryargs = ($form->{ordnumber}, - $form->{quonumber}, - $form->{transdate}, - $form->{vendor_id}, - $form->{customer_id}, - $amount, - $netamount, - $form->{reqdate}, - $form->{taxincluded}, - $form->{shippingpoint}, - $form->{shipvia}, - $form->{notes}, - $form->{intnotes}, - $form->{currency}, - $form->{closed}, - $quotation, - $form->{department_id}, - $form->{employee_id}, - $form->{language_code}, - $form->{ponumber}, - $form->{terms}, - $form->{id}); - } - $sth = $dbh->prepare($query); - $sth->execute(@queryargs) || $form->dberror($query); - - if (!$did_insert){ - @queries = $form->run_custom_queries('oe', 'UPDATE'); - } - - - $form->{ordtotal} = $amount; - - # add shipto - $form->{name} = $form->{$form->{vc}}; - $form->{name} =~ s/--$form->{"$form->{vc}_id"}//; - $form->add_shipto($dbh, $form->{id}); - - # save printed, emailed, queued - $form->save_status($dbh); - - if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) { - if ($form->{vc} eq 'customer') { - $form->update_exchangerate( - $dbh, - $form->{currency}, - $form->{transdate}, - $form->{exchangerate}, - 0); - } - if ($form->{vc} eq 'vendor') { - $form->update_exchangerate( - $dbh, - $form->{currency}, - $form->{transdate}, - 0, - $form->{exchangerate}); - } - } - - - if ($form->{type} =~ /_order$/) { - # adjust onhand - &adj_onhand($dbh, $form, $ml * -1); - &adj_inventory($dbh, $myconfig, $form); - } - - my %audittrail = ( - tablename => 'oe', - reference => ($form->{type} =~ /_order$/) - ? $form->{ordnumber} : $form->{quonumber}, - formname => $form->{type}, - action => 'saved', - id => $form->{id} ); - - $form->audittrail($dbh, "", \%audittrail); - - $form->save_recurring($dbh, $myconfig); - - my $rc = $dbh->commit; - - $rc; + if ( !$form->{reqdate} ) { + $form->{reqdate} = undef; + } + + @queryargs = ( + $form->{ordnumber}, $form->{quonumber}, + $form->{transdate}, $form->{vendor_id}, + $form->{customer_id}, $amount, + $netamount, $form->{reqdate}, + $form->{taxincluded}, $form->{shippingpoint}, + $form->{shipvia}, $form->{notes}, + $form->{intnotes}, $form->{currency}, + $form->{closed}, $quotation, + $form->{department_id}, $form->{employee_id}, + $form->{language_code}, $form->{ponumber}, + $form->{terms}, $form->{id} + ); + } + $sth = $dbh->prepare($query); + $sth->execute(@queryargs) || $form->dberror($query); + + if ( !$did_insert ) { + @queries = $form->run_custom_queries( 'oe', 'UPDATE' ); + } + + $form->{ordtotal} = $amount; + + # add shipto + $form->{name} = $form->{ $form->{vc} }; + $form->{name} =~ s/--$form->{"$form->{vc}_id"}//; + $form->add_shipto( $dbh, $form->{id} ); + + # save printed, emailed, queued + $form->save_status($dbh); + + if ( ( $form->{currency} ne $form->{defaultcurrency} ) && !$exchangerate ) { + if ( $form->{vc} eq 'customer' ) { + $form->update_exchangerate( $dbh, $form->{currency}, + $form->{transdate}, $form->{exchangerate}, 0 ); + } + if ( $form->{vc} eq 'vendor' ) { + $form->update_exchangerate( $dbh, $form->{currency}, + $form->{transdate}, 0, $form->{exchangerate} ); + } + } + + if ( $form->{type} =~ /_order$/ ) { + + # adjust onhand + &adj_onhand( $dbh, $form, $ml * -1 ); + &adj_inventory( $dbh, $myconfig, $form ); + } + + my %audittrail = ( + tablename => 'oe', + reference => ( $form->{type} =~ /_order$/ ) + ? $form->{ordnumber} + : $form->{quonumber}, + formname => $form->{type}, + action => 'saved', + id => $form->{id} + ); + + $form->audittrail( $dbh, "", \%audittrail ); + + $form->save_recurring( $dbh, $myconfig ); + + my $rc = $dbh->commit; + + $rc; } - - sub delete { - my ($self, $myconfig, $form) = @_; + my ( $self, $myconfig, $form ) = @_; - # connect to database - my $dbh = $form->{dbh}; + # connect to database + my $dbh = $form->{dbh}; - # delete spool files - my $query = qq| + # delete spool files + my $query = qq| SELECT spoolfile FROM status WHERE trans_id = ? AND spoolfile IS NOT NULL|; - $sth = $dbh->prepare($query); - $sth->execute($form->{id}) || $form->dberror($query); + $sth = $dbh->prepare($query); + $sth->execute( $form->{id} ) || $form->dberror($query); - my $spoolfile; - my @spoolfiles = (); + my $spoolfile; + my @spoolfiles = (); - while (($spoolfile) = $sth->fetchrow_array) { - push @spoolfiles, $spoolfile; - } - $sth->finish; + while ( ($spoolfile) = $sth->fetchrow_array ) { + push @spoolfiles, $spoolfile; + } + $sth->finish; - - $query = qq| + $query = qq| SELECT o.parts_id, o.ship, p.inventory_accno_id, p.assembly FROM orderitems o JOIN parts p ON (p.id = o.parts_id) WHERE trans_id = ?|; - $sth = $dbh->prepare($query); - $sth->execute($form->{id}) || $form->dberror($query); - - if ($form->{type} =~ /_order$/) { - $ml = ($form->{type} eq 'purchase_order') ? -1 : 1; - while (my ($id, $ship, $inv, $assembly) - = $sth->fetchrow_array) { - $form->update_balance( - $dbh, - "parts", - "onhand", - "id = $id", - $ship * $ml) - if ($inv || $assembly); - } - } - $sth->finish; - - # delete inventory - $query = qq|DELETE FROM inventory WHERE trans_id = ?|; - $sth = $dbh->prepare($query); - $sth->execute($form->{id}) || $form->dberror($query); - $sth->finish; - - # delete status entries - $query = qq|DELETE FROM status WHERE trans_id = ?|; - $sth = $dbh->prepare($query); - $sth->execute($form->{id}) || $form->dberror($query); - $sth->finish; - - # delete OE record - $query = qq|DELETE FROM oe WHERE id = ?|; - $sth = $dbh->prepare($query); - $sth->execute($form->{id}) || $form->dberror($query); - $sth->finish; - - # delete individual entries - $query = qq|DELETE FROM orderitems WHERE trans_id = ?|; - $sth->finish; - - $query = qq|DELETE FROM shipto WHERE trans_id = ?|; - $sth = $dbh->prepare($query); - $sth->execute($form->{id}) || $form->dberror($query); - $sth->finish; - - my %audittrail = ( - tablename => 'oe', - reference => ($form->{type} =~ /_order$/) - ? $form->{ordnumber} : $form->{quonumber}, - formname => $form->{type}, - action => 'deleted', - id => $form->{id} ); - - $form->audittrail($dbh, "", \%audittrail); - - my $rc = $dbh->commit; - - if ($rc) { - foreach $spoolfile (@spoolfiles) { - unlink "${LedgerSMB::Sysconfig::spool}/$spoolfile" if $spoolfile; - } - } - - $rc; - + $sth = $dbh->prepare($query); + $sth->execute( $form->{id} ) || $form->dberror($query); + + if ( $form->{type} =~ /_order$/ ) { + $ml = ( $form->{type} eq 'purchase_order' ) ? -1 : 1; + while ( my ( $id, $ship, $inv, $assembly ) = $sth->fetchrow_array ) { + $form->update_balance( $dbh, "parts", "onhand", "id = $id", + $ship * $ml ) + if ( $inv || $assembly ); + } + } + $sth->finish; + + # delete inventory + $query = qq|DELETE FROM inventory WHERE trans_id = ?|; + $sth = $dbh->prepare($query); + $sth->execute( $form->{id} ) || $form->dberror($query); + $sth->finish; + + # delete status entries + $query = qq|DELETE FROM status WHERE trans_id = ?|; + $sth = $dbh->prepare($query); + $sth->execute( $form->{id} ) || $form->dberror($query); + $sth->finish; + + # delete OE record + $query = qq|DELETE FROM oe WHERE id = ?|; + $sth = $dbh->prepare($query); + $sth->execute( $form->{id} ) || $form->dberror($query); + $sth->finish; + + # delete individual entries + $query = qq|DELETE FROM orderitems WHERE trans_id = ?|; + $sth->finish; + + $query = qq|DELETE FROM shipto WHERE trans_id = ?|; + $sth = $dbh->prepare($query); + $sth->execute( $form->{id} ) || $form->dberror($query); + $sth->finish; + + my %audittrail = ( + tablename => 'oe', + reference => ( $form->{type} =~ /_order$/ ) + ? $form->{ordnumber} + : $form->{quonumber}, + formname => $form->{type}, + action => 'deleted', + id => $form->{id} + ); + + $form->audittrail( $dbh, "", \%audittrail ); + + my $rc = $dbh->commit; + + if ($rc) { + foreach $spoolfile (@spoolfiles) { + unlink "${LedgerSMB::Sysconfig::spool}/$spoolfile" if $spoolfile; + } + } + + $rc; + } +sub retrieve { + use LedgerSMB::PriceMatrix; + my ( $self, $myconfig, $form ) = @_; + # connect to database + my $dbh = $form->{dbh}; -sub retrieve { - use LedgerSMB::PriceMatrix; - my ($self, $myconfig, $form) = @_; - - # connect to database - my $dbh = $form->{dbh}; - - my $query; - my $sth; - my $var; - my $ref; - - $query = qq| + my $query; + my $sth; + my $var; + my $ref; + + $query = qq| SELECT value, current_date FROM defaults WHERE setting_key = 'curr'|; - ($form->{currencies}, $form->{transdate}) = - $dbh->selectrow_array($query); - - if ($form->{id}) { - - # retrieve order - $query = qq| + ( $form->{currencies}, $form->{transdate} ) = $dbh->selectrow_array($query); + + if ( $form->{id} ) { + + # retrieve order + $query = qq| SELECT o.ordnumber, o.transdate, o.reqdate, o.terms, o.taxincluded, o.shippingpoint, o.shipvia, o.notes, o.intnotes, o.curr AS currency, @@ -799,43 +777,42 @@ sub retrieve { LEFT JOIN employees e ON (o.employee_id = e.id) LEFT JOIN department d ON (o.department_id = d.id) WHERE o.id = ?|; - $sth = $dbh->prepare($query); - $sth->execute($form->{id}) || $form->dberror($query); - - $ref = $sth->fetchrow_hashref(NAME_lc); - for (keys %$ref) { $form->{$_} = $ref->{$_} } - $sth->finish; - - $query = qq|SELECT * FROM shipto WHERE trans_id = ?|; - $sth = $dbh->prepare($query); - $sth->execute($form->{id}) || $form->dberror($query); - - $ref = $sth->fetchrow_hashref(NAME_lc); - for (keys %$ref) { $form->{$_} = $ref->{$_} } - $sth->finish; - - # get printed, emailed and queued - $query = qq| + $sth = $dbh->prepare($query); + $sth->execute( $form->{id} ) || $form->dberror($query); + + $ref = $sth->fetchrow_hashref(NAME_lc); + for ( keys %$ref ) { $form->{$_} = $ref->{$_} } + $sth->finish; + + $query = qq|SELECT * FROM shipto WHERE trans_id = ?|; + $sth = $dbh->prepare($query); + $sth->execute( $form->{id} ) || $form->dberror($query); + + $ref = $sth->fetchrow_hashref(NAME_lc); + for ( keys %$ref ) { $form->{$_} = $ref->{$_} } + $sth->finish; + + # get printed, emailed and queued + $query = qq| SELECT s.printed, s.emailed, s.spoolfile, s.formname FROM status s WHERE s.trans_id = ?|; - $sth = $dbh->prepare($query); - $sth->execute($form->{id}) || $form->dberror($query); - - while ($ref = $sth->fetchrow_hashref(NAME_lc)) { - $form->{printed} .= "$ref->{formname} " - if $ref->{printed}; - $form->{emailed} .= "$ref->{formname} " - if $ref->{emailed}; - $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " - if $ref->{spoolfile}; - } - $sth->finish; - for (qw(printed emailed queued)) { $form->{$_} =~ s/ +$//g } - - - # retrieve individual items - $query = qq| + $sth = $dbh->prepare($query); + $sth->execute( $form->{id} ) || $form->dberror($query); + + while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) { + $form->{printed} .= "$ref->{formname} " + if $ref->{printed}; + $form->{emailed} .= "$ref->{formname} " + if $ref->{emailed}; + $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " + if $ref->{spoolfile}; + } + $sth->finish; + for (qw(printed emailed queued)) { $form->{$_} =~ s/ +$//g } + + # retrieve individual items + $query = qq| SELECT o.id AS orderitems_id, p.partnumber, p.assembly, o.description, o.qty, o.sellprice, o.parts_id AS id, o.unit, o.discount, p.bin, @@ -855,764 +832,773 @@ sub retrieve { AND t.language_code = ?) WHERE o.trans_id = ? ORDER BY o.id|; - $sth = $dbh->prepare($query); - $sth->execute($form->{language_code}, $form->{id}) - || $form->dberror($query); - - # foreign exchange rates - &exchangerate_defaults($dbh, $form); - - # query for price matrix - my $pmh = PriceMatrix::price_matrix_query($dbh, $form); - - # taxes - $query = qq| + $sth = $dbh->prepare($query); + $sth->execute( $form->{language_code}, $form->{id} ) + || $form->dberror($query); + + # foreign exchange rates + &exchangerate_defaults( $dbh, $form ); + + # query for price matrix + my $pmh = PriceMatrix::price_matrix_query( $dbh, $form ); + + # taxes + $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 $taxrate; - my $ptref; - my $sellprice; - my $listprice; - - while ($ref = $sth->fetchrow_hashref(NAME_lc)) { - - ($decimalplaces) = ($ref->{sellprice} =~ /\.(\d+)/); - $decimalplaces = length $decimalplaces; - $decimalplaces = ($decimalplaces > 2) ? - $decimalplaces : 2; - - $tth->execute($ref->{id}); - $ref->{taxaccounts} = ""; - $taxrate = 0; - - while ($ptref = $tth->fetchrow_hashref(NAME_lc)) { - $ref->{taxaccounts} .= "$ptref->{accno} "; - $taxrate += $form->{"$ptref->{accno}_rate"}; - } - $tth->finish; - chop $ref->{taxaccounts}; - - # preserve price - $sellprice = $ref->{sellprice}; - - # multiply by exchangerate - $ref->{sellprice} = $form->round_amount( - $ref->{sellprice} * $form->{$form->{currency}}, - $decimalplaces - ); - - for (qw(listprice lastcost)) { - $ref->{$_} = $form->round_amount( - $ref->{$_} / $form->{$form->{currency}}, - $decimalplaces - ); - } - - # partnumber and price matrix - PriceMatrix::price_matrix( - $pmh, $ref, $form->{transdate}, $decimalplaces, - $form, $myconfig); - - $ref->{sellprice} = $sellprice; - - $ref->{partsgroup} = $ref->{partsgrouptranslation} - if $ref->{partsgrouptranslation}; - - push @{ $form->{form_details} }, $ref; - - } - $sth->finish; - - # get recurring transaction - $form->get_recurring; - - @queries = $form->run_custom_queries('oe', 'SELECT'); - $form->{dbh}->commit; - } else { - - # get last name used - $form->lastname_used($myconfig, $dbh, $form->{vc}) - unless $form->{"$form->{vc}_id"}; - - delete $form->{notes}; - - } - - $dbh->commit; + my $tth = $dbh->prepare($query) || $form->dberror($query); -} + my $taxrate; + my $ptref; + my $sellprice; + my $listprice; + + while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) { + + ($decimalplaces) = ( $ref->{sellprice} =~ /\.(\d+)/ ); + $decimalplaces = length $decimalplaces; + $decimalplaces = ( $decimalplaces > 2 ) ? $decimalplaces : 2; + + $tth->execute( $ref->{id} ); + $ref->{taxaccounts} = ""; + $taxrate = 0; + + while ( $ptref = $tth->fetchrow_hashref(NAME_lc) ) { + $ref->{taxaccounts} .= "$ptref->{accno} "; + $taxrate += $form->{"$ptref->{accno}_rate"}; + } + $tth->finish; + chop $ref->{taxaccounts}; + + # preserve price + $sellprice = $ref->{sellprice}; + + # multiply by exchangerate + $ref->{sellprice} = + $form->round_amount( + $ref->{sellprice} * $form->{ $form->{currency} }, + $decimalplaces ); + + for (qw(listprice lastcost)) { + $ref->{$_} = + $form->round_amount( + $ref->{$_} / $form->{ $form->{currency} }, + $decimalplaces ); + } + + # partnumber and price matrix + PriceMatrix::price_matrix( $pmh, $ref, $form->{transdate}, + $decimalplaces, $form, $myconfig ); + $ref->{sellprice} = $sellprice; + + $ref->{partsgroup} = $ref->{partsgrouptranslation} + if $ref->{partsgrouptranslation}; + + push @{ $form->{form_details} }, $ref; + + } + $sth->finish; + + # get recurring transaction + $form->get_recurring; + + @queries = $form->run_custom_queries( 'oe', 'SELECT' ); + $form->{dbh}->commit; + } + else { + + # get last name used + $form->lastname_used( $myconfig, $dbh, $form->{vc} ) + unless $form->{"$form->{vc}_id"}; + + delete $form->{notes}; + + } + + $dbh->commit; + +} sub exchangerate_defaults { - my ($dbh2, $form) = @_; - $dbh = $form->{dbh}; - my $var; - my $buysell = ($form->{vc} eq "customer") ? "buy" : "sell"; - - # get default currencies - my $query = qq| + my ( $dbh2, $form ) = @_; + $dbh = $form->{dbh}; + my $var; + my $buysell = ( $form->{vc} eq "customer" ) ? "buy" : "sell"; + + # get default currencies + my $query = qq| SELECT substr(value,1,3), value FROM defaults WHERE setting_key = 'curr'|; - ($form->{defaultcurrency}, $form->{currencies}) - = $dbh->selectrow_array($query); + ( $form->{defaultcurrency}, $form->{currencies} ) = + $dbh->selectrow_array($query); - $query = qq| + $query = qq| SELECT $buysell FROM exchangerate WHERE curr = ? AND transdate = ?|; - my $eth1 = $dbh->prepare($query) || $form->dberror($query); - $query = qq~ + my $eth1 = $dbh->prepare($query) || $form->dberror($query); + $query = qq~ SELECT max(transdate || ' ' || $buysell || ' ' || curr) FROM exchangerate WHERE curr = ?~; - my $eth2 = $dbh->prepare($query) || $form->dberror($query); - - # get exchange rates for transdate or max - foreach $var (split /:/, substr($form->{currencies},4)) { - $eth1->execute($var, $form->{transdate}); - ($form->{$var}) = $eth1->fetchrow_array; - if (! $form->{$var} ) { - $eth2->execute($var); - - ($form->{$var}) = $eth2->fetchrow_array; - ($null, $form->{$var}) = split / /, $form->{$var}; - $form->{$var} = 1 unless $form->{$var}; - $eth2->finish; - } - $eth1->finish; - } - - $form->{$form->{currency}} = $form->{exchangerate} - if $form->{exchangerate}; - $form->{$form->{currency}} ||= 1; - $form->{$form->{defaultcurrency}} = 1; - -} + my $eth2 = $dbh->prepare($query) || $form->dberror($query); + + # get exchange rates for transdate or max + foreach $var ( split /:/, substr( $form->{currencies}, 4 ) ) { + $eth1->execute( $var, $form->{transdate} ); + ( $form->{$var} ) = $eth1->fetchrow_array; + if ( !$form->{$var} ) { + $eth2->execute($var); + + ( $form->{$var} ) = $eth2->fetchrow_array; + ( $null, $form->{$var} ) = split / /, $form->{$var}; + $form->{$var} = 1 unless $form->{$var}; + $eth2->finish; + } + $eth1->finish; + } + + $form->{ $form->{currency} } = $form->{exchangerate} + if $form->{exchangerate}; + $form->{ $form->{currency} } ||= 1; + $form->{ $form->{defaultcurrency} } = 1; +} sub order_details { - use LedgerSMB::CP; - my ($self, $myconfig, $form) = @_; - - # connect to database - my $dbh = $form->{dbh}; - my $query; - my $sth; - - my $item; - my $i; - my @sortlist = (); - my $projectnumber; - my $projectdescription; - my $projectnumber_id; - my $translation; - my $partsgroup; - - my @queryargs; - - my @taxaccounts; - my %taxaccounts; # I don't think this works. - my $tax; - my $taxrate; - my $taxamount; - - my %translations; - - my $language_code = $form->{dbh}->quote($form->{language_code}); - $query = qq| + use LedgerSMB::CP; + my ( $self, $myconfig, $form ) = @_; + + # connect to database + my $dbh = $form->{dbh}; + my $query; + my $sth; + + my $item; + my $i; + my @sortlist = (); + my $projectnumber; + my $projectdescription; + my $projectnumber_id; + my $translation; + my $partsgroup; + + my @queryargs; + + my @taxaccounts; + my %taxaccounts; # I don't think this works. + my $tax; + my $taxrate; + my $taxamount; + + my %translations; + + my $language_code = $form->{dbh}->quote( $form->{language_code} ); + $query = qq| SELECT p.description, t.description FROM project p LEFT JOIN translation t ON (t.trans_id = p.id AND t.language_code = $language_code) WHERE id = ?|; - my $prh = $dbh->prepare($query) || $form->dberror($query); + my $prh = $dbh->prepare($query) || $form->dberror($query); - $query = qq| + $query = qq| SELECT inventory_accno_id, income_accno_id, expense_accno_id, assembly FROM parts WHERE id = ?|; - my $pth = $dbh->prepare($query) || $form->dberror($query); - - my $sortby; - - # sort items by project and partsgroup - for $i (1 .. $form->{rowcount}) { - - if ($form->{"id_$i"}) { - # account numbers - $pth->execute($form->{"id_$i"}); - $ref = $pth->fetchrow_hashref(NAME_lc); - for (keys %$ref) { $form->{"${_}_$i"} = $ref->{$_} } - $pth->finish; - - $projectnumber_id = 0; - $projectnumber = ""; - $form->{partsgroup} = ""; - $form->{projectnumber} = ""; - - if ($form->{groupprojectnumber} - || $form->{grouppartsgroup}) { - - $inventory_accno_id = - ($form->{"inventory_accno_id_$i"} || - $form->{"assembly_$i"}) ? "1" : ""; - - if ($form->{groupprojectnumber}) { - ($projectnumber, $projectnumber_id) = - split /--/, - $form->{"projectnumber_$i"}; - } - if ($form->{grouppartsgroup}) { - ($form->{partsgroup}) = split /--/, - $form->{"partsgroup_$i"}; - } - - if ($projectnumber_id && - $form->{groupprojectnumber}) { - if ($translation{$projectnumber_id}) { - $form->{projectnumber} = - $translation{$projectnumber_id}; - } else { - # get project description - $prh->execute( - $projectnumber_id); - ($projectdescription, - $translation) = - $prh->fetchrow_array; - $prh->finish; - - $form->{projectnumber} = - ($translation) ? - "$projectnumber, \n" . - "$translation" - : "$projectnumber, \n" . - "$projectdescription"; - - $translation{$projectnumber_id} - = $form->{projectnumber}; - } - } - - if ($form->{grouppartsgroup} - && $form->{partsgroup}) { - $form->{projectnumber} .= " / " - if $projectnumber_id; - $form->{projectnumber} .= - $form->{partsgroup}; - } - - $form->format_string(projectnumber); - - } - - $sortby = qq|$projectnumber$form->{partsgroup}|; - - if ($form->{sortby} ne 'runningnumber') { - for (qw(partnumber description bin)) { - $sortby .= $form->{"${_}_$i"} - if $form->{sortby} eq $_; - } - } - - push @sortlist, [ $i, - "$projectnumber$form->{partsgroup}". - "$inventory_accno_id", - $form->{projectnumber}, $projectnumber_id, - $form->{partsgroup}, $sortby ]; - } - - } - - delete $form->{projectnumber}; - - # sort the whole thing by project and group - @sortlist = sort { $a->[5] cmp $b->[5] } @sortlist; - - - # if there is a warehouse limit picking - if ($form->{warehouse_id} && $form->{formname} =~ - /(pick|packing)_list/) { - # run query to check for inventory - $query = qq| + my $pth = $dbh->prepare($query) || $form->dberror($query); + + my $sortby; + + # sort items by project and partsgroup + for $i ( 1 .. $form->{rowcount} ) { + + if ( $form->{"id_$i"} ) { + + # account numbers + $pth->execute( $form->{"id_$i"} ); + $ref = $pth->fetchrow_hashref(NAME_lc); + for ( keys %$ref ) { $form->{"${_}_$i"} = $ref->{$_} } + $pth->finish; + + $projectnumber_id = 0; + $projectnumber = ""; + $form->{partsgroup} = ""; + $form->{projectnumber} = ""; + + if ( $form->{groupprojectnumber} + || $form->{grouppartsgroup} ) + { + + $inventory_accno_id = + ( $form->{"inventory_accno_id_$i"} || $form->{"assembly_$i"} ) + ? "1" + : ""; + + if ( $form->{groupprojectnumber} ) { + ( $projectnumber, $projectnumber_id ) = + split /--/, $form->{"projectnumber_$i"}; + } + if ( $form->{grouppartsgroup} ) { + ( $form->{partsgroup} ) = split /--/, + $form->{"partsgroup_$i"}; + } + + if ( $projectnumber_id + && $form->{groupprojectnumber} ) + { + if ( $translation{$projectnumber_id} ) { + $form->{projectnumber} = + $translation{$projectnumber_id}; + } + else { + + # get project description + $prh->execute($projectnumber_id); + ( $projectdescription, $translation ) = + $prh->fetchrow_array; + $prh->finish; + + $form->{projectnumber} = + ($translation) + ? "$projectnumber, \n" . "$translation" + : "$projectnumber, \n" . "$projectdescription"; + + $translation{$projectnumber_id} = + $form->{projectnumber}; + } + } + + if ( $form->{grouppartsgroup} + && $form->{partsgroup} ) + { + $form->{projectnumber} .= " / " + if $projectnumber_id; + $form->{projectnumber} .= $form->{partsgroup}; + } + + $form->format_string(projectnumber); + + } + + $sortby = qq|$projectnumber$form->{partsgroup}|; + + if ( $form->{sortby} ne 'runningnumber' ) { + for (qw(partnumber description bin)) { + $sortby .= $form->{"${_}_$i"} + if $form->{sortby} eq $_; + } + } + + push @sortlist, + [ + $i, + "$projectnumber$form->{partsgroup}" . "$inventory_accno_id", + $form->{projectnumber}, + $projectnumber_id, + $form->{partsgroup}, + $sortby + ]; + } + + } + + delete $form->{projectnumber}; + + # sort the whole thing by project and group + @sortlist = sort { $a->[5] cmp $b->[5] } @sortlist; + + # if there is a warehouse limit picking + if ( $form->{warehouse_id} && $form->{formname} =~ /(pick|packing)_list/ ) { + + # run query to check for inventory + $query = qq| SELECT sum(qty) AS qty FROM inventory WHERE parts_id = ? AND warehouse_id = ?|; - $sth = $dbh->prepare($query) || $form->dberror($query); - - for $i (1 .. $form->{rowcount}) { - $sth->execute($form->{"id_$i"}, $form->{warehouse_id}) - || $form->dberror; - - ($qty) = $sth->fetchrow_array; - $sth->finish; - - $form->{"qty_$i"} = 0 if $qty == 0; - - if ($form->parse_amount($myconfig, $form->{"ship_$i"}) - > $qty) { - $form->{"ship_$i"} = - $form->format_amount($myconfig, $qty); - } - } - } - - - my $runningnumber = 1; - my $sameitem = ""; - my $subtotal; - my $k = scalar @sortlist; - my $j = 0; - - foreach $item (@sortlist) { - $i = $item->[0]; - $j++; - - if ($form->{groupprojectnumber} || $form->{grouppartsgroup}) { - if ($item->[1] ne $sameitem) { - $sameitem = $item->[1]; - - $ok = 0; - - if ($form->{groupprojectnumber}) { - $ok = $form->{"projectnumber_$i"}; - } - if ($form->{grouppartsgroup}) { - $ok = $form->{"partsgroup_$i"} - unless $ok; - } - - if ($ok) { - if ($form->{"inventory_accno_id_$i"} - || $form->{"assembly_$i"}) { - - push(@{ $form->{part} }, ""); - push(@{ $form->{service} }, - NULL); - } else { - push(@{ $form->{part} }, NULL); - push(@{ $form->{service} }, ""); - } - - push(@{ $form->{description} }, - $item->[2]); - for ( - qw(taxrates runningnumber - number sku qty ship unit bin - serialnumber requiredate - projectnumber sellprice - listprice netprice discount - discountrate linetotal weight - itemnotes) - ) { - push(@{ $form->{$_} }, ""); - } - push(@{ $form->{lineitems} }, - { amount => 0, tax => 0 }); - } - } - } - - - $form->{"qty_$i"} = $form->parse_amount( - $myconfig, $form->{"qty_$i"}); - $form->{"ship_$i"} = $form->parse_amount( - $myconfig, $form->{"ship_$i"}); - - if ($form->{"qty_$i"}) { - - $form->{totalqty} += $form->{"qty_$i"}; - $form->{totalship} += $form->{"ship_$i"}; - $form->{totalweight} += ($form->{"weight_$i"} - * $form->{"qty_$i"}); - $form->{totalweightship} += ($form->{"weight_$i"} - * $form->{"ship_$i"}); - - # add number, description and qty to $form->{number} - push(@{ $form->{runningnumber} }, $runningnumber++); - push(@{ $form->{number} }, - qq|$form->{"partnumber_$i"}|); - push(@{ $form->{sku} }, qq|$form->{"sku_$i"}|); - push(@{ $form->{description} }, - qq|$form->{"description_$i"}|); - push(@{ $form->{itemnotes} }, $form->{"notes_$i"}); - push(@{ $form->{qty} }, $form->format_amount( - $myconfig, $form->{"qty_$i"})); - push(@{ $form->{ship} }, $form->format_amount( - $myconfig, $form->{"ship_$i"})); - push(@{ $form->{unit} }, qq|$form->{"unit_$i"}|); - push(@{ $form->{bin} }, qq|$form->{"bin_$i"}|); - push(@{ $form->{serialnumber} }, - qq|$form->{"serialnumber_$i"}|); - push(@{ $form->{requiredate} }, - qq|$form->{"reqdate_$i"}|); - push(@{ $form->{projectnumber} }, - qq|$form->{"projectnumber_$i"}|); - - push(@{ $form->{sellprice} }, $form->{"sellprice_$i"}); - - push(@{ $form->{listprice} }, $form->{"listprice_$i"}); - - push(@{ $form->{weight} }, $form->format_amount( - $myconfig, - $form->{"weight_$i"} * $form->{"ship_$i"})); - - my $sellprice = $form->parse_amount( - $myconfig, $form->{"sellprice_$i"}); - my ($dec) = ($sellprice =~ /\.(\d+)/); - $dec = length $dec; - my $decimalplaces = ($dec > 2) ? $dec : 2; - - my $discount = $form->round_amount( - $sellprice * $form->parse_amount( - $myconfig, - $form->{"discount_$i"}) / 100, - $decimalplaces); - - # keep a netprice as well, (sellprice - discount) - $form->{"netprice_$i"} = $sellprice - $discount; - - my $linetotal = $form->round_amount( - $form->{"qty_$i"} * $form->{"netprice_$i"}, 2); - - if ($form->{"inventory_accno_id_$i"} - || $form->{"assembly_$i"}) { - - push(@{ $form->{part} }, $form->{"sku_$i"}); - push(@{ $form->{service} }, NULL); - $form->{totalparts} += $linetotal; - } else { - push(@{ $form->{service} }, $form->{"sku_$i"}); - push(@{ $form->{part} }, NULL); - $form->{totalservices} += $linetotal; - } - - push(@{ $form->{netprice} }, - ($form->{"netprice_$i"}) - ? $form->format_amount( - $myconfig, - $form->{"netprice_$i"}, - $decimalplaces) - : " "); - - $discount = ($discount) - ? $form->format_amount( - $myconfig, - $discount * -1, - $decimalplaces) - : " "; - - push(@{ $form->{discount} }, $discount); - push(@{ $form->{discountrate} }, - $form->format_amount($myconfig, - $form->{"discount_$i"})); - - $form->{ordtotal} += $linetotal; - - # this is for the subtotals for grouping - $subtotal += $linetotal; - - $form->{"linetotal_$i"} = $form->format_amount( - $myconfig, $linetotal, 2); - push(@{ $form->{linetotal} }, $form->{"linetotal_$i"}); - - @taxaccounts = Tax::init_taxes($form, - $form->{"taxaccounts_$i"}); - - my $ml = 1; - my @taxrates = (); - - $tax = 0; - - $taxamount = Tax::calculate_taxes(\@taxaccounts, - $form, $linetotal, 1); - $taxbase = Tax::extract_taxes(\@taxaccounts, - $form, $linetotal); - foreach $item (@taxaccounts) { - push @taxrates, Math::BigFloat->new(100) * - $item->rate; - if ($form->{taxincluded}) { - $taxaccounts{$item->account} += - $item->value; - $taxbase{$item->account} += $taxbase; - } else { - Tax::apply_taxes(\@taxaccounts, $form, - $linetotal); - $taxbase{$item->account} += $linetotal; - $taxaccounts{$item->account} += - $item->value; - } - } - if ($form->{taxincluded}) { - $tax += Tax::calculate_taxes(\@taxaccounts, - $form, $linetotal, 1); - } else { - $tax += Tax::calculate_taxes(\@taxaccounts, - $form, $linetotal, 0); - } - - push(@{ $form->{lineitems} }, - { amount => $linetotal, - tax => $form->round_amount($tax, 2) }); - push(@{ $form->{taxrates} }, - join ' ', sort { $a <=> $b } @taxrates); - - if ($form->{"assembly_$i"}) { - $form->{stagger} = -1; - &assembly_details($myconfig, - $form, $dbh, $form->{"id_$i"}, - $oid{$myconfig->{dbdriver}}, - $form->{"qty_$i"}); - } - - } - - # add subtotal - if ($form->{groupprojectnumber} || $form->{grouppartsgroup}) { - if ($subtotal) { - if ($j < $k) { - # look at next item - if ($sortlist[$j]->[1] ne $sameitem) { - - if ($form->{"inventory_accno_id_$i"} - || $form->{"assembly_$i"}) { - - push(@{ $form->{part} }, - ""); - push(@{ - $form->{service} - }, NULL); - } else { - push(@{ - $form->{service} - }, ""); - push(@{ $form->{part} }, - NULL); - } - - for (qw( - taxrates runningnumber - number sku qty ship unit - bin serialnumber - requiredate - projectnumber sellprice - listprice netprice - discount discountrate - weight itemnotes) - ) { - - push(@{ $form->{$_} }, - ""); - } - - push(@{ $form->{description} }, - $form->{groupsubtotaldescription}); - - push(@{ $form->{lineitems} }, - { amount => 0, - tax => 0 }); - - if ($form->{groupsubtotaldescription} - ne "") { - push(@{ - $form->{linetotal} - }, - $form->format_amount($myconfig, $subtotal, 2)); - } else { - push(@{ - $form->{linetotal} - }, ""); - } - $subtotal = 0; - } - - } else { - - # got last item - if ($form->{groupsubtotaldescription} - ne "") { - - if ($form->{"inventory_accno_id_$i"} - || $form->{"assembly_$i"}) { - push(@{ $form->{part} }, - ""); - push(@{ - $form->{service} - }, NULL); - } else { - push(@{ - $form->{service} - }, ""); - push(@{ $form->{part} }, - NULL); - } - - for (qw( - taxrates runningnumber - number sku qty ship unit - bin serialnumber - requiredate - projectnumber sellprice - listprice netprice - discount discountrate - weight itemnotes) - ) { - - push(@{ $form->{$_} }, - ""); - } - - push(@{ $form->{description} }, - $form->{groupsubtotaldescription}); - - push(@{ $form->{linetotal} }, - $form->format_amount( - $myconfig, - $subtotal, - 2)); - push(@{ $form->{lineitems} }, - { amount => 0, - tax => 0 }); - } - } - } - } - } - - - $tax = 0; - - foreach $item (sort keys %taxaccounts) { - if ($form->round_amount($taxaccounts{$item}, 2)) { - $tax += $taxamount = $form->round_amount( - $taxaccounts{$item}, 2); - - push(@{ $form->{taxbaseinclusive} }, - $form->{"${item}_taxbaseinclusive"} - = $form->round_amount( - $taxbase{$item} + $tax, 2)); - push(@{ $form->{taxbase} }, - $form->{"${item}_taxbase"} - = $form->format_amount($myconfig, - $taxbase{$item}, 2)); - push(@{ $form->{tax} }, - $form->{"${item}_tax"} - = $form->format_amount($myconfig, - $taxamount, 2)); - - push(@{ $form->{taxdescription} }, - $form->{"${item}_description"}); - - $form->{"${item}_taxrate"} = - $form->format_amount($myconfig, - $form->{"${item}_rate"} * 100); - - push(@{ $form->{taxrate} }, $form->{"${item}_taxrate"}); - - push(@{ $form->{taxnumber} }, - $form->{"${item}_taxnumber"}); - } - } - - # adjust taxes for lineitems - my $total = 0; - for (@{ $form->{lineitems} }) { - $total += $_->{tax}; - } - if ($form->round_amount($total,2) != $form->round_amount($tax,2)) { - # get largest amount - for (reverse sort { $a->{tax} <=> $b->{tax} } - @{ $form->{lineitems} }) { - - $_->{tax} -= $total - $tax; - last; - } - } - $i = 1; - for (@{ $form->{lineitems} }) { - push(@{ $form->{linetax} }, - $form->format_amount($myconfig, $_->{tax}, 2, "")); - } - - - for (qw(totalparts totalservices)) { - $form->{$_} = $form->format_amount($myconfig, $form->{$_}, 2); - } - for (qw(totalqty totalship totalweight)) { - $form->{$_} = $form->format_amount($myconfig, $form->{$_}); - } - $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal}, - 2); - $form->{ordtotal} = ($form->{taxincluded}) - ? $form->{ordtotal} - : $form->{ordtotal} + $tax; - - my $c; - if ($form->{language_code} ne "") { - $c = new CP $form->{language_code}; - } else { - $c = new CP $myconfig->{countrycode}; - } - $c->init; - my $whole; - ($whole, $form->{decimal}) = split /\./, $form->{ordtotal}; - $form->{decimal} .= "00"; - $form->{decimal} = substr($form->{decimal}, 0, 2); - - $form->{text_decimal} = $c->num2text($form->{decimal} * 1); - $form->{text_amount} = $c->num2text($whole); - $form->{integer_amount} = $form->format_amount($myconfig, $whole); - - # format amounts - $form->{quototal} = $form->{ordtotal} = - $form->format_amount($myconfig, $form->{ordtotal}, 2); - - $form->format_string(qw(text_amount text_decimal)); - - $query = qq| + $sth = $dbh->prepare($query) || $form->dberror($query); + + for $i ( 1 .. $form->{rowcount} ) { + $sth->execute( $form->{"id_$i"}, $form->{warehouse_id} ) + || $form->dberror; + + ($qty) = $sth->fetchrow_array; + $sth->finish; + + $form->{"qty_$i"} = 0 if $qty == 0; + + if ( $form->parse_amount( $myconfig, $form->{"ship_$i"} ) > $qty ) { + $form->{"ship_$i"} = $form->format_amount( $myconfig, $qty ); + } + } + } + + my $runningnumber = 1; + my $sameitem = ""; + my $subtotal; + my $k = scalar @sortlist; + my $j = 0; + + foreach $item (@sortlist) { + $i = $item->[0]; + $j++; + + if ( $form->{groupprojectnumber} || $form->{grouppartsgroup} ) { + if ( $item->[1] ne $sameitem ) { + $sameitem = $item->[1]; + + $ok = 0; + + if ( $form->{groupprojectnumber} ) { + $ok = $form->{"projectnumber_$i"}; + } + if ( $form->{grouppartsgroup} ) { + $ok = $form->{"partsgroup_$i"} + unless $ok; + } + + if ($ok) { + if ( $form->{"inventory_accno_id_$i"} + || $form->{"assembly_$i"} ) + { + + push( @{ $form->{part} }, "" ); + push( @{ $form->{service} }, NULL ); + } + else { + push( @{ $form->{part} }, NULL ); + push( @{ $form->{service} }, "" ); + } + + push( @{ $form->{description} }, $item->[2] ); + for ( + qw(taxrates runningnumber + number sku qty ship unit bin + serialnumber requiredate + projectnumber sellprice + listprice netprice discount + discountrate linetotal weight + itemnotes) + ) + { + push( @{ $form->{$_} }, "" ); + } + push( @{ $form->{lineitems} }, { amount => 0, tax => 0 } ); + } + } + } + + $form->{"qty_$i"} = $form->parse_amount( $myconfig, $form->{"qty_$i"} ); + $form->{"ship_$i"} = + $form->parse_amount( $myconfig, $form->{"ship_$i"} ); + + if ( $form->{"qty_$i"} ) { + + $form->{totalqty} += $form->{"qty_$i"}; + $form->{totalship} += $form->{"ship_$i"}; + $form->{totalweight} += + ( $form->{"weight_$i"} * $form->{"qty_$i"} ); + $form->{totalweightship} += + ( $form->{"weight_$i"} * $form->{"ship_$i"} ); + + # add number, description and qty to $form->{number} + push( @{ $form->{runningnumber} }, $runningnumber++ ); + push( @{ $form->{number} }, qq|$form->{"partnumber_$i"}| ); + push( @{ $form->{sku} }, qq|$form->{"sku_$i"}| ); + push( @{ $form->{description} }, qq|$form->{"description_$i"}| ); + push( @{ $form->{itemnotes} }, $form->{"notes_$i"} ); + push( + @{ $form->{qty} }, + $form->format_amount( $myconfig, $form->{"qty_$i"} ) + ); + push( + @{ $form->{ship} }, + $form->format_amount( $myconfig, $form->{"ship_$i"} ) + ); + push( @{ $form->{unit} }, qq|$form->{"unit_$i"}| ); + push( @{ $form->{bin} }, qq|$form->{"bin_$i"}| ); + push( @{ $form->{serialnumber} }, qq|$form->{"serialnumber_$i"}| ); + push( @{ $form->{requiredate} }, qq|$form->{"reqdate_$i"}| ); + push( @{ $form->{projectnumber} }, + qq|$form->{"projectnumber_$i"}| ); + + push( @{ $form->{sellprice} }, $form->{"sellprice_$i"} ); + + push( @{ $form->{listprice} }, $form->{"listprice_$i"} ); + + push( + @{ $form->{weight} }, + $form->format_amount( + $myconfig, $form->{"weight_$i"} * $form->{"ship_$i"} + ) + ); + + my $sellprice = + $form->parse_amount( $myconfig, $form->{"sellprice_$i"} ); + my ($dec) = ( $sellprice =~ /\.(\d+)/ ); + $dec = length $dec; + my $decimalplaces = ( $dec > 2 ) ? $dec : 2; + + my $discount = $form->round_amount( + $sellprice * + $form->parse_amount( $myconfig, $form->{"discount_$i"} ) / + 100, + $decimalplaces + ); + + # keep a netprice as well, (sellprice - discount) + $form->{"netprice_$i"} = $sellprice - $discount; + + my $linetotal = + $form->round_amount( $form->{"qty_$i"} * $form->{"netprice_$i"}, + 2 ); + + if ( $form->{"inventory_accno_id_$i"} + || $form->{"assembly_$i"} ) + { + + push( @{ $form->{part} }, $form->{"sku_$i"} ); + push( @{ $form->{service} }, NULL ); + $form->{totalparts} += $linetotal; + } + else { + push( @{ $form->{service} }, $form->{"sku_$i"} ); + push( @{ $form->{part} }, NULL ); + $form->{totalservices} += $linetotal; + } + + push( + @{ $form->{netprice} }, + ( $form->{"netprice_$i"} ) + ? $form->format_amount( $myconfig, $form->{"netprice_$i"}, + $decimalplaces ) + : " " + ); + + $discount = + ($discount) + ? $form->format_amount( $myconfig, $discount * -1, + $decimalplaces ) + : " "; + + push( @{ $form->{discount} }, $discount ); + push( + @{ $form->{discountrate} }, + $form->format_amount( $myconfig, $form->{"discount_$i"} ) + ); + + $form->{ordtotal} += $linetotal; + + # this is for the subtotals for grouping + $subtotal += $linetotal; + + $form->{"linetotal_$i"} = + $form->format_amount( $myconfig, $linetotal, 2 ); + push( @{ $form->{linetotal} }, $form->{"linetotal_$i"} ); + + @taxaccounts = Tax::init_taxes( $form, $form->{"taxaccounts_$i"} ); + + my $ml = 1; + my @taxrates = (); + + $tax = 0; + + $taxamount = + Tax::calculate_taxes( \@taxaccounts, $form, $linetotal, 1 ); + $taxbase = Tax::extract_taxes( \@taxaccounts, $form, $linetotal ); + foreach $item (@taxaccounts) { + push @taxrates, Math::BigFloat->new(100) * $item->rate; + if ( $form->{taxincluded} ) { + $taxaccounts{ $item->account } += $item->value; + $taxbase{ $item->account } += $taxbase; + } + else { + Tax::apply_taxes( \@taxaccounts, $form, $linetotal ); + $taxbase{ $item->account } += $linetotal; + $taxaccounts{ $item->account } += $item->value; + } + } + if ( $form->{taxincluded} ) { + $tax += + Tax::calculate_taxes( \@taxaccounts, $form, $linetotal, 1 ); + } + else { + $tax += + Tax::calculate_taxes( \@taxaccounts, $form, $linetotal, 0 ); + } + + push( + @{ $form->{lineitems} }, + { + amount => $linetotal, + tax => $form->round_amount( $tax, 2 ) + } + ); + push( @{ $form->{taxrates} }, + join ' ', sort { $a <=> $b } @taxrates ); + + if ( $form->{"assembly_$i"} ) { + $form->{stagger} = -1; + &assembly_details( $myconfig, $form, $dbh, $form->{"id_$i"}, + $oid{ $myconfig->{dbdriver} }, + $form->{"qty_$i"} ); + } + + } + + # add subtotal + if ( $form->{groupprojectnumber} || $form->{grouppartsgroup} ) { + if ($subtotal) { + if ( $j < $k ) { + + # look at next item + if ( $sortlist[$j]->[1] ne $sameitem ) { + + if ( $form->{"inventory_accno_id_$i"} + || $form->{"assembly_$i"} ) + { + + push( @{ $form->{part} }, "" ); + push( @{ $form->{service} }, NULL ); + } + else { + push( @{ $form->{service} }, "" ); + push( @{ $form->{part} }, NULL ); + } + + for ( + qw( + taxrates runningnumber + number sku qty ship unit + bin serialnumber + requiredate + projectnumber sellprice + listprice netprice + discount discountrate + weight itemnotes) + ) + { + + push( @{ $form->{$_} }, "" ); + } + + push( + @{ $form->{description} }, + $form->{groupsubtotaldescription} + ); + + push( + @{ $form->{lineitems} }, + { + amount => 0, + tax => 0 + } + ); + + if ( $form->{groupsubtotaldescription} ne "" ) { + push( + @{ $form->{linetotal} }, + $form->format_amount( $myconfig, $subtotal, 2 ) + ); + } + else { + push( @{ $form->{linetotal} }, "" ); + } + $subtotal = 0; + } + + } + else { + + # got last item + if ( $form->{groupsubtotaldescription} ne "" ) { + + if ( $form->{"inventory_accno_id_$i"} + || $form->{"assembly_$i"} ) + { + push( @{ $form->{part} }, "" ); + push( @{ $form->{service} }, NULL ); + } + else { + push( @{ $form->{service} }, "" ); + push( @{ $form->{part} }, NULL ); + } + + for ( + qw( + taxrates runningnumber + number sku qty ship unit + bin serialnumber + requiredate + projectnumber sellprice + listprice netprice + discount discountrate + weight itemnotes) + ) + { + + push( @{ $form->{$_} }, "" ); + } + + push( + @{ $form->{description} }, + $form->{groupsubtotaldescription} + ); + + push( + @{ $form->{linetotal} }, + $form->format_amount( $myconfig, $subtotal, 2 ) + ); + push( + @{ $form->{lineitems} }, + { + amount => 0, + tax => 0 + } + ); + } + } + } + } + } + + $tax = 0; + + foreach $item ( sort keys %taxaccounts ) { + if ( $form->round_amount( $taxaccounts{$item}, 2 ) ) { + $tax += $taxamount = $form->round_amount( $taxaccounts{$item}, 2 ); + + push( + @{ $form->{taxbaseinclusive} }, + $form->{"${item}_taxbaseinclusive"} = + $form->round_amount( $taxbase{$item} + $tax, 2 ) + ); + push( + @{ $form->{taxbase} }, + $form->{"${item}_taxbase"} = + $form->format_amount( $myconfig, $taxbase{$item}, 2 ) + ); + push( + @{ $form->{tax} }, + $form->{"${item}_tax"} = + $form->format_amount( $myconfig, $taxamount, 2 ) + ); + + push( @{ $form->{taxdescription} }, + $form->{"${item}_description"} ); + + $form->{"${item}_taxrate"} = + $form->format_amount( $myconfig, $form->{"${item}_rate"} * 100 ); + + push( @{ $form->{taxrate} }, $form->{"${item}_taxrate"} ); + + push( @{ $form->{taxnumber} }, $form->{"${item}_taxnumber"} ); + } + } + + # adjust taxes for lineitems + my $total = 0; + for ( @{ $form->{lineitems} } ) { + $total += $_->{tax}; + } + if ( $form->round_amount( $total, 2 ) != $form->round_amount( $tax, 2 ) ) { + + # get largest amount + for ( reverse sort { $a->{tax} <=> $b->{tax} } @{ $form->{lineitems} } ) + { + + $_->{tax} -= $total - $tax; + last; + } + } + $i = 1; + for ( @{ $form->{lineitems} } ) { + push( + @{ $form->{linetax} }, + $form->format_amount( $myconfig, $_->{tax}, 2, "" ) + ); + } + + for (qw(totalparts totalservices)) { + $form->{$_} = $form->format_amount( $myconfig, $form->{$_}, 2 ); + } + for (qw(totalqty totalship totalweight)) { + $form->{$_} = $form->format_amount( $myconfig, $form->{$_} ); + } + $form->{subtotal} = $form->format_amount( $myconfig, $form->{ordtotal}, 2 ); + $form->{ordtotal} = + ( $form->{taxincluded} ) + ? $form->{ordtotal} + : $form->{ordtotal} + $tax; + + my $c; + if ( $form->{language_code} ne "" ) { + $c = new CP $form->{language_code}; + } + else { + $c = new CP $myconfig->{countrycode}; + } + $c->init; + my $whole; + ( $whole, $form->{decimal} ) = split /\./, $form->{ordtotal}; + $form->{decimal} .= "00"; + $form->{decimal} = substr( $form->{decimal}, 0, 2 ); + + $form->{text_decimal} = $c->num2text( $form->{decimal} * 1 ); + $form->{text_amount} = $c->num2text($whole); + $form->{integer_amount} = $form->format_amount( $myconfig, $whole ); + + # format amounts + $form->{quototal} = $form->{ordtotal} = + $form->format_amount( $myconfig, $form->{ordtotal}, 2 ); + + $form->format_string(qw(text_amount text_decimal)); + + $query = qq| SELECT value FROM defaults WHERE setting_key = 'weightunit'|; - ($form->{weightunit}) = $dbh->selectrow_array($query); - - $dbh->commit; + ( $form->{weightunit} ) = $dbh->selectrow_array($query); -} + $dbh->commit; +} sub assembly_details { - my ($myconfig, $form, $dbh, $id, $oid, $qty) = @_; - - my $sm = ""; - my $spacer; - - $form->{stagger}++; - if ($form->{format} eq 'html') { - $spacer = " " x (3 * ($form->{stagger} - 1)) - if $form->{stagger} > 1; - } - if ($form->{format} =~ /(postscript|pdf)/) { - if ($form->{stagger} > 1) { - $spacer = ($form->{stagger} - 1) * 3; - $spacer = '\rule{'.$spacer.'mm}{0mm}'; - } - } - - # get parts and push them onto the stack - my $sortorder = ""; - - if ($form->{grouppartsgroup}) { - $sortorder = qq|ORDER BY pg.partsgroup, a.id|; - } else { - $sortorder = qq|ORDER BY a.id|; - } - - my $where = ($form->{formname} eq 'work_order') - ? "1 = 1" - : "a.bom = '1'"; - - my $query = qq| + my ( $myconfig, $form, $dbh, $id, $oid, $qty ) = @_; + + my $sm = ""; + my $spacer; + + $form->{stagger}++; + if ( $form->{format} eq 'html' ) { + $spacer = " " x ( 3 * ( $form->{stagger} - 1 ) ) + if $form->{stagger} > 1; + } + if ( $form->{format} =~ /(postscript|pdf)/ ) { + if ( $form->{stagger} > 1 ) { + $spacer = ( $form->{stagger} - 1 ) * 3; + $spacer = '\rule{' . $spacer . 'mm}{0mm}'; + } + } + + # get parts and push them onto the stack + my $sortorder = ""; + + if ( $form->{grouppartsgroup} ) { + $sortorder = qq|ORDER BY pg.partsgroup, a.id|; + } + else { + $sortorder = qq|ORDER BY a.id|; + } + + my $where = + ( $form->{formname} eq 'work_order' ) + ? "1 = 1" + : "a.bom = '1'"; + + my $query = qq| SELECT p.partnumber, p.description, p.unit, a.qty, pg.partsgroup, p.partnumber AS sku, p.assembly, p.id, p.bin @@ -1622,331 +1608,325 @@ sub assembly_details { WHERE $where AND a.id = ? $sortorder|; - my $sth = $dbh->prepare($query); - $sth->execute($id) || $form->dberror($query); - - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { - - for (qw(partnumber description partsgroup)) { - $form->{"a_$_"} = $ref->{$_}; - $form->format_string("a_$_"); - } - - if ($form->{grouppartsgroup} && $ref->{partsgroup} ne $sm) { - for (qw( - taxrates number sku unit qty runningnumber ship - bin serialnumber requiredate projectnumber - sellprice listprice netprice discount - discountrate linetotal weight itemnotes) - ) { - - push(@{ $form->{$_} }, ""); - } - $sm = ($form->{"a_partsgroup"}) - ? $form->{"a_partsgroup"} : ""; - push(@{ $form->{description} }, "$spacer$sm"); - - push(@{ $form->{lineitems} }, - { amount => 0, tax => 0 }); - - } - - if ($form->{stagger}) { - - push(@{ $form->{description} }, - qq|$spacer$form->{"a_partnumber"}, |. - qq|$form->{"a_description"}|); - - for (qw( - taxrates number sku runningnumber ship - serialnumber requiredate projectnumber - sellprice listprice netprice discount - discountrate linetotal weight itemnotes) - ) { - - push(@{ $form->{$_} }, "") - } - - } else { - - push(@{ $form->{description} }, - qq|$form->{"a_description"}|); - push(@{ $form->{sku} }, $form->{"a_partnumber"}); - push(@{ $form->{number} }, $form->{"a_partnumber"}); - - for (qw( - taxrates runningnumber ship serialnumber - requiredate projectnumber sellprice listprice - netprice discount discountrate linetotal weight - itemnotes) - ) { - - push(@{ $form->{$_} }, "") - } - - } - - push(@{ $form->{lineitems} }, { amount => 0, tax => 0 }); - - push(@{ $form->{qty} }, $form->format_amount( - $myconfig, $ref->{qty} * $qty)); - - for (qw(unit bin)) { - $form->{"a_$_"} = $ref->{$_}; - $form->format_string("a_$_"); - push(@{ $form->{$_} }, $form->{"a_$_"}); - } - - if ($ref->{assembly} && $form->{formname} eq 'work_order') { - &assembly_details( - $myconfig, $form, $dbh, $ref->{id}, $oid, - $ref->{qty} * $qty); - } - - } - $sth->finish; - - $form->{stagger}--; - -} + my $sth = $dbh->prepare($query); + $sth->execute($id) || $form->dberror($query); + + while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) { + + for (qw(partnumber description partsgroup)) { + $form->{"a_$_"} = $ref->{$_}; + $form->format_string("a_$_"); + } + + if ( $form->{grouppartsgroup} && $ref->{partsgroup} ne $sm ) { + for ( + qw( + taxrates number sku unit qty runningnumber ship + bin serialnumber requiredate projectnumber + sellprice listprice netprice discount + discountrate linetotal weight itemnotes) + ) + { + + push( @{ $form->{$_} }, "" ); + } + $sm = ( $form->{"a_partsgroup"} ) ? $form->{"a_partsgroup"} : ""; + push( @{ $form->{description} }, "$spacer$sm" ); + + push( @{ $form->{lineitems} }, { amount => 0, tax => 0 } ); + + } + + if ( $form->{stagger} ) { + + push( + @{ $form->{description} }, + qq|$spacer$form->{"a_partnumber"}, | + . qq|$form->{"a_description"}| + ); + + for ( + qw( + taxrates number sku runningnumber ship + serialnumber requiredate projectnumber + sellprice listprice netprice discount + discountrate linetotal weight itemnotes) + ) + { + + push( @{ $form->{$_} }, "" ); + } + + } + else { + + push( @{ $form->{description} }, qq|$form->{"a_description"}| ); + push( @{ $form->{sku} }, $form->{"a_partnumber"} ); + push( @{ $form->{number} }, $form->{"a_partnumber"} ); + + for ( + qw( + taxrates runningnumber ship serialnumber + requiredate projectnumber sellprice listprice + netprice discount discountrate linetotal weight + itemnotes) + ) + { + + push( @{ $form->{$_} }, "" ); + } + + } + + push( @{ $form->{lineitems} }, { amount => 0, tax => 0 } ); + + push( + @{ $form->{qty} }, + $form->format_amount( $myconfig, $ref->{qty} * $qty ) + ); + + for (qw(unit bin)) { + $form->{"a_$_"} = $ref->{$_}; + $form->format_string("a_$_"); + push( @{ $form->{$_} }, $form->{"a_$_"} ); + } + + if ( $ref->{assembly} && $form->{formname} eq 'work_order' ) { + &assembly_details( $myconfig, $form, $dbh, $ref->{id}, $oid, + $ref->{qty} * $qty ); + } + + } + $sth->finish; + + $form->{stagger}--; +} sub project_description { - my ($self, $dbh, $id) = @_; + my ( $self, $dbh, $id ) = @_; - my $query = qq| + my $query = qq| SELECT description FROM project WHERE id = $id|; - ($_) = $dbh->selectrow_array($query); - - $_; + ($_) = $dbh->selectrow_array($query); -} + $_; +} sub get_warehouses { - my ($self, $myconfig, $form) = @_; - - my $dbh = $form->{dbh}; - # setup warehouses - my $query = qq| + my ( $self, $myconfig, $form ) = @_; + + my $dbh = $form->{dbh}; + + # setup warehouses + my $query = qq| SELECT id, description FROM warehouse ORDER BY 2|; - my $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + my $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { - push @{ $form->{all_warehouse} }, $ref; - } - $sth->finish; + while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) { + push @{ $form->{all_warehouse} }, $ref; + } + $sth->finish; - $dbh->commit; + $dbh->commit; } - sub save_inventory { - my ($self, $myconfig, $form) = @_; - - my ($null, $warehouse_id) = split /--/, $form->{warehouse}; - $warehouse_id *= 1; - - my $ml = ($form->{type} eq 'ship_order') ? -1 : 1; - - my $dbh = $form->{dbh}; - my $sth; - my $wth; - my $serialnumber; - my $ship; - - my ($null, $employee_id) = split /--/, $form->{employee}; - ($null, $employee_id) = $form->get_employee($dbh) if ! $employee_id; - - $query = qq| + my ( $self, $myconfig, $form ) = @_; + + my ( $null, $warehouse_id ) = split /--/, $form->{warehouse}; + $warehouse_id *= 1; + + my $ml = ( $form->{type} eq 'ship_order' ) ? -1 : 1; + + my $dbh = $form->{dbh}; + my $sth; + my $wth; + my $serialnumber; + my $ship; + + my ( $null, $employee_id ) = split /--/, $form->{employee}; + ( $null, $employee_id ) = $form->get_employee($dbh) if !$employee_id; + + $query = qq| SELECT serialnumber, ship FROM orderitems WHERE trans_id = ? AND id = ? FOR UPDATE|; - $sth = $dbh->prepare($query) || $form->dberror($query); + $sth = $dbh->prepare($query) || $form->dberror($query); - $query = qq| + $query = qq| SELECT sum(qty) FROM inventory WHERE parts_id = ? AND warehouse_id = ?|; - $wth = $dbh->prepare($query) || $form->dberror($query); - + $wth = $dbh->prepare($query) || $form->dberror($query); + + for my $i ( 1 .. $form->{rowcount} ) { + $form->{"ship_$i"} = 0 unless $form->{"ship_$i"}; - for my $i (1 .. $form->{rowcount}) { - $form->{"ship_$i"} = 0 unless $form->{"ship_$i"}; + $ship = + ( abs( $form->{"ship_$i"} ) > abs( $form->{"qty_$i"} ) ) + ? $form->{"qty_$i"} + : $form->{"ship_$i"}; - $ship = (abs($form->{"ship_$i"}) > abs($form->{"qty_$i"})) - ? $form->{"qty_$i"} - : $form->{"ship_$i"}; - - if ($warehouse_id && $form->{type} eq 'ship_order') { + if ( $warehouse_id && $form->{type} eq 'ship_order' ) { - $wth->execute($form->{"id_$i"}, $warehouse_id) - || $form->dberror; + $wth->execute( $form->{"id_$i"}, $warehouse_id ) + || $form->dberror; - ($qty) = $wth->fetchrow_array; - $wth->finish; + ($qty) = $wth->fetchrow_array; + $wth->finish; - if ($ship > $qty) { - $ship = $qty; - } - } + if ( $ship > $qty ) { + $ship = $qty; + } + } - - if ($ship) { + if ($ship) { - if (!$form->{shippingdate}){ - $form->{shippingdate} = undef; - } + if ( !$form->{shippingdate} ) { + $form->{shippingdate} = undef; + } - $ship *= $ml; - $query = qq| + $ship *= $ml; + $query = qq| INSERT INTO inventory (parts_id, warehouse_id, qty, trans_id, orderitems_id, shippingdate, employee_id) VALUES (?, ?, ?, ?, ?, ?, ?)|; - $sth2 = $dbh->prepare($query); - $sth2->execute( - $form->{"id_$i"}, $warehouse_id, - $ship, $form->{"id"}, - $form->{"orderitems_id_$i"}, - $form->{shippingdate}, - $employee_id - ) || $form->dberror($query); - $sth2->finish; - - # add serialnumber, ship to orderitems - $sth->execute( - $form->{id}, $form->{"orderitems_id_$i"}) - || $form->dberror; - ($serialnumber, $ship) = $sth->fetchrow_array; - $sth->finish; - - $serialnumber .= " " if $serialnumber; - $serialnumber .= qq|$form->{"serialnumber_$i"}|; - $ship += $form->{"ship_$i"}; - - $query = qq| + $sth2 = $dbh->prepare($query); + $sth2->execute( $form->{"id_$i"}, $warehouse_id, $ship, + $form->{"id"}, $form->{"orderitems_id_$i"}, + $form->{shippingdate}, $employee_id ) + || $form->dberror($query); + $sth2->finish; + + # add serialnumber, ship to orderitems + $sth->execute( $form->{id}, $form->{"orderitems_id_$i"} ) + || $form->dberror; + ( $serialnumber, $ship ) = $sth->fetchrow_array; + $sth->finish; + + $serialnumber .= " " if $serialnumber; + $serialnumber .= qq|$form->{"serialnumber_$i"}|; + $ship += $form->{"ship_$i"}; + + $query = qq| UPDATE orderitems SET serialnumber = '$serialnumber', ship = $ship, reqdate = '$form->{shippingdate}' WHERE trans_id = $form->{id} AND id = $form->{"orderitems_id_$i"}|; - $sth2 = $dbh->prepare($query); - $sth2->execute( - $serialnumber, $ship, - $form->{shippingdate}, $form->{id}, - $form->{"orderitems_id_$i"} - ) || $form->dberror($query); - $sth2->finish; - - # update order with ship via - $query = qq| + $sth2 = $dbh->prepare($query); + $sth2->execute( $serialnumber, $ship, $form->{shippingdate}, + $form->{id}, $form->{"orderitems_id_$i"} ) + || $form->dberror($query); + $sth2->finish; + + # update order with ship via + $query = qq| UPDATE oe SET shippingpoint = ?, shipvia = ? WHERE id = ?|; - $sth2 = $dbh->prepare($query); - $sth2->execute( - $form->{shippingpoint}, $form->{shipvia}, - $form->{id} - ) || $form->dberror($query); - $sth2->finish; + $sth2 = $dbh->prepare($query); + $sth2->execute( $form->{shippingpoint}, + $form->{shipvia}, $form->{id} ) + || $form->dberror($query); + $sth2->finish; - # update onhand for parts - $form->update_balance($dbh, - "parts", - "onhand", - qq|id = $form->{"id_$i"}|, - $form->{"ship_$i"} * $ml); + # update onhand for parts + $form->update_balance( + $dbh, "parts", "onhand", + qq|id = $form->{"id_$i"}|, + $form->{"ship_$i"} * $ml + ); - } - } + } + } - my $rc = $dbh->commit; + my $rc = $dbh->commit; - $rc; + $rc; } - sub adj_onhand { - my ($dbh, $form, $ml) = @_; + my ( $dbh, $form, $ml ) = @_; - my $query = qq| + my $query = qq| SELECT oi.parts_id, oi.ship, p.inventory_accno_id, p.assembly FROM orderitems oi JOIN parts p ON (p.id = oi.parts_id) WHERE oi.trans_id = ?|; - my $sth = $dbh->prepare($query); - $sth->execute($form->{id}) || $form->dberror($query); + my $sth = $dbh->prepare($query); + $sth->execute( $form->{id} ) || $form->dberror($query); - $query = qq| + $query = qq| SELECT sum(p.inventory_accno_id), p.assembly FROM parts p JOIN assembly a ON (a.parts_id = p.id) WHERE a.id = ? GROUP BY p.assembly|; - my $ath = $dbh->prepare($query) || $form->dberror($query); + my $ath = $dbh->prepare($query) || $form->dberror($query); - my $ref; - - while ($ref = $sth->fetchrow_hashref(NAME_lc)) { + my $ref; - if ($ref->{inventory_accno_id} || $ref->{assembly}) { + while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) { - # do not update if assembly consists of all services - if ($ref->{assembly}) { - $ath->execute($ref->{parts_id}) - || $form->dberror($query); + if ( $ref->{inventory_accno_id} || $ref->{assembly} ) { - my ($inv, $assembly) = $ath->fetchrow_array; - $ath->finish; + # do not update if assembly consists of all services + if ( $ref->{assembly} ) { + $ath->execute( $ref->{parts_id} ) + || $form->dberror($query); - next unless ($inv || $assembly); - - } + my ( $inv, $assembly ) = $ath->fetchrow_array; + $ath->finish; - # adjust onhand in parts table - $form->update_balance($dbh, - "parts", - "onhand", - qq|id = $ref->{parts_id}|, - $ref->{ship} * $ml); - } - } - - $sth->finish; + next unless ( $inv || $assembly ); -} + } + # adjust onhand in parts table + $form->update_balance( + $dbh, "parts", "onhand", + qq|id = $ref->{parts_id}|, + $ref->{ship} * $ml + ); + } + } + + $sth->finish; + +} sub adj_inventory { - my ($dbh, $myconfig, $form) = @_; + my ( $dbh, $myconfig, $form ) = @_; - - # increase/reduce qty in inventory table - my $query = qq| + # increase/reduce qty in inventory table + my $query = qq| SELECT oi.id, oi.parts_id, oi.ship FROM orderitems oi WHERE oi.trans_id = ?|; - my $sth = $dbh->prepare($query); - $sth->execute($form->{id}) || $form->dberror($query); + my $sth = $dbh->prepare($query); + $sth->execute( $form->{id} ) || $form->dberror($query); - my $id = $dbh->quote($form->{id}); - $query = qq| + my $id = $dbh->quote( $form->{id} ); + $query = qq| SELECT qty, (SELECT SUM(qty) FROM inventory WHERE trans_id = $id @@ -1954,99 +1934,96 @@ sub adj_inventory { FROM inventory WHERE trans_id = $id AND orderitems_id = ?|; - my $ith = $dbh->prepare($query) || $form->dberror($query); - - my $qty; - my $ml = ($form->{type} =~ /(ship|sales)_order/) ? -1 : 1; - - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { - - $ith->execute($ref->{id}, $ref->{id}) || $form->dberror($query); - - my $ship = $ref->{ship}; - while (my $inv = $ith->fetchrow_hashref(NAME_lc)) { - - if (($qty = (($inv->{total} * $ml) - $ship)) >= 0) { - $qty = $inv->{qty} * $ml - if ($qty > ($inv->{qty} * $ml)); - - $form->update_balance($dbh, - "inventory", - "qty", - qq|$oid{$myconfig->{dbdriver}} |. - qq|= $inv->{oid}|, - $qty * -1 * $ml); - $ship -= $qty; - } - } - $ith->finish; - - } - $sth->finish; - - # delete inventory entries if qty = 0 - $query = qq| + my $ith = $dbh->prepare($query) || $form->dberror($query); + + my $qty; + my $ml = ( $form->{type} =~ /(ship|sales)_order/ ) ? -1 : 1; + + while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) { + + $ith->execute( $ref->{id}, $ref->{id} ) || $form->dberror($query); + + my $ship = $ref->{ship}; + while ( my $inv = $ith->fetchrow_hashref(NAME_lc) ) { + + if ( ( $qty = ( ( $inv->{total} * $ml ) - $ship ) ) >= 0 ) { + $qty = $inv->{qty} * $ml + if ( $qty > ( $inv->{qty} * $ml ) ); + + $form->update_balance( + $dbh, "inventory", "qty", + qq|$oid{$myconfig->{dbdriver}} | . qq|= $inv->{oid}|, + $qty * -1 * $ml + ); + $ship -= $qty; + } + } + $ith->finish; + + } + $sth->finish; + + # delete inventory entries if qty = 0 + $query = qq| DELETE FROM inventory WHERE trans_id = ? AND qty = 0|; - $sth = $dbh->prepare($query); - $sth->execute($form->{id}) || $form->dberror($query); + $sth = $dbh->prepare($query); + $sth->execute( $form->{id} ) || $form->dberror($query); } - sub get_inventory { - my ($self, $myconfig, $form) = @_; - - my $where; - my $query; - my $null; - my $fromwarehouse_id; - my $towarehouse_id; - my $var; - - my $dbh = $form->{dbh}; - - if ($form->{partnumber} ne "") { - $var = $dbh->quote($form->like(lc $form->{partnumber})); - $where .= " + my ( $self, $myconfig, $form ) = @_; + + my $where; + my $query; + my $null; + my $fromwarehouse_id; + my $towarehouse_id; + my $var; + + my $dbh = $form->{dbh}; + + if ( $form->{partnumber} ne "" ) { + $var = $dbh->quote( $form->like( lc $form->{partnumber} ) ); + $where .= " AND lower(p.partnumber) LIKE '$var'"; - } - if ($form->{description} ne "") { - $var = $dbh->quote($form->like(lc $form->{description})); - $where .= " + } + if ( $form->{description} ne "" ) { + $var = $dbh->quote( $form->like( lc $form->{description} ) ); + $where .= " AND lower(p.description) LIKE '$var'"; - } - if ($form->{partsgroup} ne "") { - ($null, $var) = split /--/, $form->{partsgroup}; - $var = $dbh->quote($var); - $where .= " + } + if ( $form->{partsgroup} ne "" ) { + ( $null, $var ) = split /--/, $form->{partsgroup}; + $var = $dbh->quote($var); + $where .= " AND pg.id = $var"; - } - - - ($null, $fromwarehouse_id) = split /--/, $form->{fromwarehouse}; - $fromwarehouse_id = $dbh->quote($fromwarehouse_id); - - ($null, $towarehouse_id) = split /--/, $form->{towarehouse}; - $towarehouse_id = $dbh->quote($towarehouse_id); - - my %ordinal = ( - partnumber => 2, - description => 3, - partsgroup => 5, - warehouse => 6, - ); - - my @a = (partnumber, warehouse); - my $sortorder = $form->sort_order(\@a, \%ordinal); - - if ($fromwarehouse_id) { - if ($towarehouse_id) { - $where .= " + } + + ( $null, $fromwarehouse_id ) = split /--/, $form->{fromwarehouse}; + $fromwarehouse_id = $dbh->quote($fromwarehouse_id); + + ( $null, $towarehouse_id ) = split /--/, $form->{towarehouse}; + $towarehouse_id = $dbh->quote($towarehouse_id); + + my %ordinal = ( + partnumber => 2, + description => 3, + partsgroup => 5, + warehouse => 6, + ); + + my @a = ( partnumber, warehouse ); + my $sortorder = $form->sort_order( \@a, \%ordinal ); + + if ($fromwarehouse_id) { + if ($towarehouse_id) { + $where .= " AND NOT i.warehouse_id = $towarehouse_id"; - } - $query = qq| + } + $query = qq| SELECT p.id, p.partnumber, p.description, sum(i.qty) * 2 AS onhand, sum(i.qty) AS qty, pg.partsgroup, w.description AS warehouse, @@ -2060,9 +2037,10 @@ sub get_inventory { GROUP BY p.id, p.partnumber, p.description, pg.partsgroup, w.description, i.warehouse_id ORDER BY $sortorder|; - } else { - if ($towarehouse_id) { - $query = qq| + } + else { + if ($towarehouse_id) { + $query = qq| SELECT p.id, p.partnumber, p.description, p.onhand, (SELECT SUM(qty) @@ -2076,9 +2054,9 @@ sub get_inventory { WHERE p.onhand > 0 $where UNION|; - } + } - $query .= qq| + $query .= qq| SELECT p.id, p.partnumber, p.description, sum(i.qty) * 2 AS onhand, sum(i.qty) AS qty, pg.partsgroup, w.description AS warehouse, @@ -2092,349 +2070,341 @@ sub get_inventory { GROUP BY p.id, p.partnumber, p.description, pg.partsgroup, w.description, i.warehouse_id ORDER BY $sortorder|; - } - - my $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - - while ($ref = $sth->fetchrow_hashref(NAME_lc)) { - $ref->{qty} = $ref->{onhand} - $ref->{qty}; - push @{ $form->{all_inventory} }, $ref if $ref->{qty} > 0; - } - $sth->finish; - - $dbh->commit; -} + } + + my $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) { + $ref->{qty} = $ref->{onhand} - $ref->{qty}; + push @{ $form->{all_inventory} }, $ref if $ref->{qty} > 0; + } + $sth->finish; + $dbh->commit; +} sub transfer { - my ($self, $myconfig, $form) = @_; - - my $dbh = $form->{dbh}; - - ($form->{employee}, $form->{employee_id}) = $form->get_employee($dbh); - - my @a = localtime; - $a[5] += 1900; - $a[4]++; - $a[4] = substr("0$a[4]", -2); - $a[3] = substr("0$a[3]", -2); - $shippingdate = "$a[5]$a[4]$a[3]"; - - my %total = (); - - - - my $query = qq| + my ( $self, $myconfig, $form ) = @_; + + my $dbh = $form->{dbh}; + + ( $form->{employee}, $form->{employee_id} ) = $form->get_employee($dbh); + + my @a = localtime; + $a[5] += 1900; + $a[4]++; + $a[4] = substr( "0$a[4]", -2 ); + $a[3] = substr( "0$a[3]", -2 ); + $shippingdate = "$a[5]$a[4]$a[3]"; + + my %total = (); + + my $query = qq| INSERT INTO inventory (warehouse_id, parts_id, qty, shippingdate, employee_id) VALUES (?, ?, ?, '$shippingdate', $form->{employee_id})|; - $sth = $dbh->prepare($query) || $form->dberror($query); - - my $qty; - - for my $i (1 .. $form->{rowcount}) { - $qty = $form->parse_amount($myconfig, $form->{"transfer_$i"}); - - $qty = $form->{"qty_$i"} if ($qty > $form->{"qty_$i"}); - - if ($qty > 0) { - # to warehouse - if ($form->{warehouse_id}) { - $sth->execute($form->{warehouse_id}, - $form->{"id_$i"}, $qty, - $shippingdate, $form->{employee_id}) - || $form->dberror; - $sth->finish; - } - - # from warehouse - if ($form->{"warehouse_id_$i"}) { - $sth->execute($form->{"warehouse_id_$i"}, - $form->{"id_$i"}, $qty * -1) - || $form->dberror; - $sth->finish; - } - } - } - - my $rc = $dbh->commit; - $dbh->commit; - - $rc; + $sth = $dbh->prepare($query) || $form->dberror($query); -} + my $qty; + + for my $i ( 1 .. $form->{rowcount} ) { + $qty = $form->parse_amount( $myconfig, $form->{"transfer_$i"} ); + $qty = $form->{"qty_$i"} if ( $qty > $form->{"qty_$i"} ); + + if ( $qty > 0 ) { + + # to warehouse + if ( $form->{warehouse_id} ) { + $sth->execute( $form->{warehouse_id}, $form->{"id_$i"}, $qty, + $shippingdate, $form->{employee_id} ) + || $form->dberror; + $sth->finish; + } + + # from warehouse + if ( $form->{"warehouse_id_$i"} ) { + $sth->execute( $form->{"warehouse_id_$i"}, + $form->{"id_$i"}, $qty * -1 ) + || $form->dberror; + $sth->finish; + } + } + } + + my $rc = $dbh->commit; + $dbh->commit; + + $rc; + +} sub get_soparts { - my ($self, $myconfig, $form) = @_; - - # connect to database - my $dbh = $form->{dbh}; - - my $id; - my $ref; - - # store required items from selected sales orders - my $query = qq| + my ( $self, $myconfig, $form ) = @_; + + # connect to database + my $dbh = $form->{dbh}; + + my $id; + my $ref; + + # store required items from selected sales orders + my $query = qq| SELECT p.id, oi.qty - oi.ship AS required, p.assembly FROM orderitems oi JOIN parts p ON (p.id = oi.parts_id) WHERE oi.trans_id = ?|; - my $sth = $dbh->prepare($query) || $form->dberror($query); - - for (my $i = 1; $i <= $form->{rowcount}; $i++) { + my $sth = $dbh->prepare($query) || $form->dberror($query); - if ($form->{"ndx_$i"}) { + for ( my $i = 1 ; $i <= $form->{rowcount} ; $i++ ) { - $sth->execute($form->{"ndx_$i"}); - - while ($ref = $sth->fetchrow_hashref(NAME_lc)) { - &add_items_required( - "", $dbh, $form, $ref->{id}, - $ref->{required}, $ref->{assembly}); - } - $sth->finish; - } + if ( $form->{"ndx_$i"} ) { - } + $sth->execute( $form->{"ndx_$i"} ); - $query = qq|SELECT current_date|; - ($form->{transdate}) = $dbh->selectrow_array($query); - - # foreign exchange rates - &exchangerate_defaults($dbh, $form); + while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) { + &add_items_required( "", $dbh, $form, $ref->{id}, + $ref->{required}, $ref->{assembly} ); + } + $sth->finish; + } - $dbh->commit; + } -} + $query = qq|SELECT current_date|; + ( $form->{transdate} ) = $dbh->selectrow_array($query); + + # foreign exchange rates + &exchangerate_defaults( $dbh, $form ); + + $dbh->commit; +} sub add_items_required { - my ($self, $dbh, $form, $parts_id, $required, $assembly) = @_; - - my $query; - my $sth; - my $ref; - - if ($assembly) { - $query = qq| + my ( $self, $dbh, $form, $parts_id, $required, $assembly ) = @_; + + my $query; + my $sth; + my $ref; + + if ($assembly) { + $query = qq| SELECT p.id, a.qty, p.assembly FROM assembly a JOIN parts p ON (p.id = a.parts_id) WHERE a.id = ?|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - - while ($ref = $sth->fetchrow_hashref(NAME_lc)) { - &add_items_required( - "", $dbh, $form, $ref->{id}, - $required * $ref->{qty}, $ref->{assembly}); - } - $sth->finish; - - } else { - - $query = qq| + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) { + &add_items_required( "", $dbh, $form, $ref->{id}, + $required * $ref->{qty}, + $ref->{assembly} ); + } + $sth->finish; + + } + else { + + $query = qq| SELECT partnumber, description, lastcost FROM parts WHERE id = ?|; - $sth = $dbh->prepare($query); - $sth->execute($parts_id) || $form->dberror($query); - $ref = $sth->fetchrow_hashref(NAME_lc); - for (keys %$ref) { - $form->{orderitems}{$parts_id}{$_} = $ref->{$_}; - } - $sth->finish; + $sth = $dbh->prepare($query); + $sth->execute($parts_id) || $form->dberror($query); + $ref = $sth->fetchrow_hashref(NAME_lc); + for ( keys %$ref ) { + $form->{orderitems}{$parts_id}{$_} = $ref->{$_}; + } + $sth->finish; - $form->{orderitems}{$parts_id}{required} += $required; + $form->{orderitems}{$parts_id}{required} += $required; - $query = qq| + $query = qq| SELECT pv.partnumber, pv.leadtime, pv.lastcost, pv.curr, pv.vendor_id, v.name FROM partsvendor pv JOIN vendor v ON (v.id = pv.vendor_id) WHERE pv.parts_id = ?|; - $sth = $dbh->prepare($query) || $form->dberror($query); + $sth = $dbh->prepare($query) || $form->dberror($query); - # get cost and vendor - $sth->execute($parts_id); + # get cost and vendor + $sth->execute($parts_id); - while ($ref = $sth->fetchrow_hashref(NAME_lc)) { - for (keys %$ref) { - $form->{orderitems}{$parts_id}{partsvendor} - {$ref->{vendor_id}}{$_} = $ref->{$_}; - } - } - $sth->finish; - - } + while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) { + for ( keys %$ref ) { + $form->{orderitems}{$parts_id}{partsvendor}{ $ref->{vendor_id} } + {$_} = $ref->{$_}; + } + } + $sth->finish; -} + } +} sub generate_orders { - my ($self, $myconfig, $form) = @_; - - my $i; - my %a; - my $query; - my $sth; - - for ($i = 1; $i <= $form->{rowcount}; $i++) { - for (qw(qty lastcost)) { - $form->{"${_}_$i"} = $form->parse_amount( - $myconfig, $form->{"${_}_$i"}); - } - - if ($form->{"qty_$i"}) { - ($vendor, $vendor_id) = - split /--/, $form->{"vendor_$i"}; - if ($vendor_id) { - $a{$vendor_id}{$form->{"id_$i"}}{qty} - += $form->{"qty_$i"}; - for (qw(curr lastcost)) { - $a{$vendor_id}{$form->{"id_$i"}}{$_} - = $form->{"${_}_$i"}; - } - } - } - } - - # connect to database - my $dbh = $form->{dbh}; - - # foreign exchange rates - &exchangerate_defaults($dbh, $form); - - my $amount; - my $netamount; - my $curr = ""; - my %tax; - my $taxincluded = 0; - my $vendor_id; - - my $description; - my $unit; - - my $sellprice; - - foreach $vendor_id (keys %a) { - - %tax = (); - - $query = qq| + my ( $self, $myconfig, $form ) = @_; + + my $i; + my %a; + my $query; + my $sth; + + for ( $i = 1 ; $i <= $form->{rowcount} ; $i++ ) { + for (qw(qty lastcost)) { + $form->{"${_}_$i"} = + $form->parse_amount( $myconfig, $form->{"${_}_$i"} ); + } + + if ( $form->{"qty_$i"} ) { + ( $vendor, $vendor_id ) = + split /--/, $form->{"vendor_$i"}; + if ($vendor_id) { + $a{$vendor_id}{ $form->{"id_$i"} }{qty} += $form->{"qty_$i"}; + for (qw(curr lastcost)) { + $a{$vendor_id}{ $form->{"id_$i"} }{$_} = $form->{"${_}_$i"}; + } + } + } + } + + # connect to database + my $dbh = $form->{dbh}; + + # foreign exchange rates + &exchangerate_defaults( $dbh, $form ); + + my $amount; + my $netamount; + my $curr = ""; + my %tax; + my $taxincluded = 0; + my $vendor_id; + + my $description; + my $unit; + + my $sellprice; + + foreach $vendor_id ( keys %a ) { + + %tax = (); + + $query = qq| SELECT v.curr, v.taxincluded, t.rate, c.accno FROM vendor v LEFT JOIN vendortax vt ON (v.id = vt.vendor_id) LEFT JOIN tax t ON (t.chart_id = vt.chart_id) LEFT JOIN chart c ON (c.id = t.chart_id) WHERE v.id = ?|; - $sth = $dbh->prepare($query); - $sth->execute($vendor_id) || $form->dberror($query); - while ($ref = $sth->fetchrow_hashref(NAME_lc)) { - $curr = $ref->{curr}; - $taxincluded = $ref->{taxincluded}; - $tax{$ref->{accno}} = $ref->{rate}; - } - $sth->finish; - - $curr ||= $form->{defaultcurrency}; - $taxincluded *= 1; - - my $uid = localtime; - $uid .= "$$"; - - # TODO: Make this function insert as much as possible - $query = qq| + $sth = $dbh->prepare($query); + $sth->execute($vendor_id) || $form->dberror($query); + while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) { + $curr = $ref->{curr}; + $taxincluded = $ref->{taxincluded}; + $tax{ $ref->{accno} } = $ref->{rate}; + } + $sth->finish; + + $curr ||= $form->{defaultcurrency}; + $taxincluded *= 1; + + my $uid = localtime; + $uid .= "$$"; + + # TODO: Make this function insert as much as possible + $query = qq| INSERT INTO oe (ordnumber) VALUES ('$uid')|; - $dbh->do($query) || $form->dberror($query); - - $query = qq|SELECT id FROM oe WHERE ordnumber = '$uid'|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - my ($id) = $sth->fetchrow_array; - $sth->finish; - - $amount = 0; - $netamount = 0; - - foreach my $parts_id (keys %{ $a{$vendor_id} }) { - - if (($form->{$curr} - * $form->{$a{$vendor_id}{$parts_id}{curr}}) > 0) { - - $sellprice = $a{$vendor_id}{$parts_id}{lastcost} - / $form->{$curr} - * $form->{$a{$vendor_id}{$parts_id} - {curr}}; - } else { - $sellprice = - $a{$vendor_id}{$parts_id}{lastcost}; - } - $sellprice = $form->round_amount($sellprice, 2); - - my $linetotal = $form->round_amount( - $sellprice * $a{$vendor_id}{$parts_id}{qty}, 2); - - $query = qq| + $dbh->do($query) || $form->dberror($query); + + $query = qq|SELECT id FROM oe WHERE ordnumber = '$uid'|; + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + my ($id) = $sth->fetchrow_array; + $sth->finish; + + $amount = 0; + $netamount = 0; + + foreach my $parts_id ( keys %{ $a{$vendor_id} } ) { + + if ( ( $form->{$curr} * $form->{ $a{$vendor_id}{$parts_id}{curr} } ) + > 0 ) + { + + $sellprice = + $a{$vendor_id}{$parts_id}{lastcost} / $form->{$curr} * + $form->{ $a{$vendor_id}{$parts_id}{curr} }; + } + else { + $sellprice = $a{$vendor_id}{$parts_id}{lastcost}; + } + $sellprice = $form->round_amount( $sellprice, 2 ); + + my $linetotal = + $form->round_amount( $sellprice * $a{$vendor_id}{$parts_id}{qty}, + 2 ); + + $query = qq| SELECT p.description, p.unit, c.accno FROM parts p LEFT JOIN partstax pt ON (p.id = pt.parts_id) LEFT JOIN chart c ON (c.id = pt.chart_id) WHERE p.id = ?|; - $sth = $dbh->prepare($query); - $sth->execute($parts_id) || $form->dberror($query); - - my $rate = 0; - my $taxes = ''; - while ($ref = $sth->fetchrow_hashref(NAME_lc)) { - $description = $ref->{description}; - $unit = $ref->{unit}; - $rate += $tax{$ref->{accno}}; - $taxes .= "$ref->{accno} "; - } - $sth->finish; - chop $taxes; - my @taxaccounts = Tax::init_taxes($form, $taxes); - - $netamount += $linetotal; - if ($taxincluded) { - $amount += $linetotal; - } else { - $amount += $form->round_amount( - Tax::apply_taxes(\@taxaccounts, $form, - $linetotal), 2); - } - - - $query = qq| + $sth = $dbh->prepare($query); + $sth->execute($parts_id) || $form->dberror($query); + + my $rate = 0; + my $taxes = ''; + while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) { + $description = $ref->{description}; + $unit = $ref->{unit}; + $rate += $tax{ $ref->{accno} }; + $taxes .= "$ref->{accno} "; + } + $sth->finish; + chop $taxes; + my @taxaccounts = Tax::init_taxes( $form, $taxes ); + + $netamount += $linetotal; + if ($taxincluded) { + $amount += $linetotal; + } + else { + $amount += + $form->round_amount( + Tax::apply_taxes( \@taxaccounts, $form, $linetotal ), 2 ); + } + + $query = qq| INSERT INTO orderitems (trans_id, parts_id, description, qty, ship, sellprice, unit) VALUES (?, ?, ?, ?, 0, ?, ?)|; - $sth = $dbh->prepare($query); - $sth->execute( - $id, $parts_id, $description, - $a{vendor_id}{parts_id}{qty}, $sellprice, - $unit - ) || $form->dberror($query); - - } - - my $ordnumber = $form->update_defaults($myconfig, 'ponumber'); - - my $null; - my $employee_id; - my $department_id; - - ($null, $employee_id) = $form->get_employee($dbh); - ($null, $department_id) = split /--/, $form->{department}; - $department_id *= 1; - - $query = qq| + $sth = $dbh->prepare($query); + $sth->execute( $id, $parts_id, $description, + $a{vendor_id}{parts_id}{qty}, + $sellprice, $unit ) + || $form->dberror($query); + + } + + my $ordnumber = $form->update_defaults( $myconfig, 'ponumber' ); + + my $null; + my $employee_id; + my $department_id; + + ( $null, $employee_id ) = $form->get_employee($dbh); + ( $null, $department_id ) = split /--/, $form->{department}; + $department_id *= 1; + + $query = qq| UPDATE oe SET ordnumber = ?, transdate = current_date, @@ -2448,152 +2418,149 @@ sub generate_orders { department_id = ?, ponumber = ? WHERE id = ?|; - $sth = $dbh->prepare($query); - $sth->execute( - $ordnumber, $vendor_id, $amount, $netamount, - $taxincluded, $curr, $employee_id, - $department_id, $form->{ponumber}, $id - ) || $form->dberror($query); - - } - - my $rc = $dbh->commit; - - $rc; - -} + $sth = $dbh->prepare($query); + $sth->execute( + $ordnumber, $vendor_id, $amount, + $netamount, $taxincluded, $curr, + $employee_id, $department_id, $form->{ponumber}, + $id + ) || $form->dberror($query); + } + + my $rc = $dbh->commit; + + $rc; + +} sub consolidate_orders { - my ($self, $myconfig, $form) = @_; - - # connect to database - my $dbh = $form->{dbh}; - - my $i; - my $id; - my $ref; - my %oe = (); - - my $query = qq|SELECT * FROM oe WHERE id = ?|; - my $sth = $dbh->prepare($query) || $form->dberror($query); - - for ($i = 1; $i <= $form->{rowcount}; $i++) { - # retrieve order - if ($form->{"ndx_$i"}) { - $sth->execute($form->{"ndx_$i"}); - - $ref = $sth->fetchrow_hashref(NAME_lc); - $ref->{ndx} = $i; - $oe{oe}{$ref->{curr}}{$ref->{id}} = $ref; - - $oe{vc}{$ref->{curr}}{$ref->{"$form->{vc}_id"}}++; - $sth->finish; - } - } - - $query = qq|SELECT * FROM orderitems WHERE trans_id = ?|; - $sth = $dbh->prepare($query) || $form->dberror($query); - - foreach $curr (keys %{ $oe{oe} }) { - - foreach $id (sort { - $oe{oe}{$curr}{$a}->{ndx} - <=> $oe{oe}{$curr}{$b}->{ndx} } - keys %{ $oe{oe}{$curr} }) { - - # retrieve order - $vc_id = $oe{oe}{$curr}{$id}->{"$form->{vc}_id"}; - - if ($oe{vc}{$oe{oe}{$curr}{$id}->{curr}}{$vc_id} > 1) { - - push @{ $oe{orders}{$curr}{$vc_id} }, $id; - - $sth->execute($id); - while ($ref = $sth->fetchrow_hashref(NAME_lc)) { - push @{ $oe{orderitems}{$curr}{$id} }, - $ref; - } - $sth->finish; - - } - } - } - - - my $ordnumber = $form->{ordnumber}; - my $numberfld = ($form->{vc} eq 'customer') ? 'sonumber' : 'ponumber'; - - my ($department, $department_id) = $form->{department}; - $department_id *= 1; - - my $uid = localtime; - $uid .= "$$"; - - my @orderitems = (); - - foreach $curr (keys %{ $oe{orders} }) { - - foreach $vc_id (sort { - $a <=> $b - } keys %{ $oe{orders}{$curr} }) { - - # the orders - @orderitems = (); - $form->{customer_id} = $form->{vendor_id} = 0; - $form->{"$form->{vc}_id"} = $vc_id; - $amount = 0; - $netamount = 0; - - foreach $id (@{ $oe{orders}{$curr}{$vc_id} }) { - - # header - $ref = $oe{oe}{$curr}{$id}; - - $amount += $ref->{amount}; - $netamount += $ref->{netamount}; - - $id = $dbh->quore($id); - foreach $item - (@{ $oe{orderitems}{$curr}{$id} }) { - - push @orderitems, $item; - } - - # close order - $query = qq| + my ( $self, $myconfig, $form ) = @_; + + # connect to database + my $dbh = $form->{dbh}; + + my $i; + my $id; + my $ref; + my %oe = (); + + my $query = qq|SELECT * FROM oe WHERE id = ?|; + my $sth = $dbh->prepare($query) || $form->dberror($query); + + for ( $i = 1 ; $i <= $form->{rowcount} ; $i++ ) { + + # retrieve order + if ( $form->{"ndx_$i"} ) { + $sth->execute( $form->{"ndx_$i"} ); + + $ref = $sth->fetchrow_hashref(NAME_lc); + $ref->{ndx} = $i; + $oe{oe}{ $ref->{curr} }{ $ref->{id} } = $ref; + + $oe{vc}{ $ref->{curr} }{ $ref->{"$form->{vc}_id"} }++; + $sth->finish; + } + } + + $query = qq|SELECT * FROM orderitems WHERE trans_id = ?|; + $sth = $dbh->prepare($query) || $form->dberror($query); + + foreach $curr ( keys %{ $oe{oe} } ) { + + foreach $id ( + sort { $oe{oe}{$curr}{$a}->{ndx} <=> $oe{oe}{$curr}{$b}->{ndx} } + keys %{ $oe{oe}{$curr} } + ) + { + + # retrieve order + $vc_id = $oe{oe}{$curr}{$id}->{"$form->{vc}_id"}; + + if ( $oe{vc}{ $oe{oe}{$curr}{$id}->{curr} }{$vc_id} > 1 ) { + + push @{ $oe{orders}{$curr}{$vc_id} }, $id; + + $sth->execute($id); + while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) { + push @{ $oe{orderitems}{$curr}{$id} }, $ref; + } + $sth->finish; + + } + } + } + + my $ordnumber = $form->{ordnumber}; + my $numberfld = ( $form->{vc} eq 'customer' ) ? 'sonumber' : 'ponumber'; + + my ( $department, $department_id ) = $form->{department}; + $department_id *= 1; + + my $uid = localtime; + $uid .= "$$"; + + my @orderitems = (); + + foreach $curr ( keys %{ $oe{orders} } ) { + + foreach $vc_id ( sort { $a <=> $b } keys %{ $oe{orders}{$curr} } ) { + + # the orders + @orderitems = (); + $form->{customer_id} = $form->{vendor_id} = 0; + $form->{"$form->{vc}_id"} = $vc_id; + $amount = 0; + $netamount = 0; + + foreach $id ( @{ $oe{orders}{$curr}{$vc_id} } ) { + + # header + $ref = $oe{oe}{$curr}{$id}; + + $amount += $ref->{amount}; + $netamount += $ref->{netamount}; + + $id = $dbh->quore($id); + foreach $item ( @{ $oe{orderitems}{$curr}{$id} } ) { + + push @orderitems, $item; + } + + # close order + $query = qq| UPDATE oe SET closed = '1' WHERE id = $id|; - $dbh->do($query) || $form->dberror($query); + $dbh->do($query) || $form->dberror($query); - # reset shipped - $query = qq| + # reset shipped + $query = qq| UPDATE orderitems SET ship = 0 WHERE trans_id = $id|; - $dbh->do($query) || $form->dberror($query); - } + $dbh->do($query) || $form->dberror($query); + } - $ordnumber ||= $form->update_defaults( - $myconfig, $numberfld, $dbh); + $ordnumber ||= + $form->update_defaults( $myconfig, $numberfld, $dbh ); - #fixme: Change this - $query = qq| + #fixme: Change this + $query = qq| INSERT INTO oe (ordnumber) VALUES ('$uid')|; - $dbh->do($query) || $form->dberror($query); + $dbh->do($query) || $form->dberror($query); - $query = qq| + $query = qq| SELECT id FROM oe WHERE ordnumber = '$uid'|; - ($id) = $dbh->selectrow_array($query); + ($id) = $dbh->selectrow_array($query); + + $ref->{employee_id} *= 1; - $ref->{employee_id} *= 1; - - $query = qq| + $query = qq| UPDATE oe SET - ordnumber = |.$dbh->quote($ordnumber).qq|, + ordnumber = | . $dbh->quote($ordnumber) . qq|, transdate = current_date, vendor_id = ?, customer_id = ?, @@ -2611,24 +2578,28 @@ sub consolidate_orders { ponumber = ?, department_id = ? WHERE id = ?|; - $sth = $dbh->prepare($query); - $sth->execute ( - $form->{vendor_id}, $form->{customer_id}, $amount, $netamount, - $form->{reqdate}, $form->{taxincluded}, $form->{shippingpoint}, - $form->{notes}, $curr, $ref->{employee_id}, $form->{intnotes}, - $form->{shipvia}, $ref->{language_code}, $form->{po_number}, - $department_id, $id - ) || $form->dberror($query); - - - # add items - foreach $item (@orderitems) { - for (qw( - qty sellprice discount project_id ship) - ) { - $item->{$_} *= 1; - } - $query = qq| + $sth = $dbh->prepare($query); + $sth->execute( + $form->{vendor_id}, $form->{customer_id}, + $amount, $netamount, + $form->{reqdate}, $form->{taxincluded}, + $form->{shippingpoint}, $form->{notes}, + $curr, $ref->{employee_id}, + $form->{intnotes}, $form->{shipvia}, + $ref->{language_code}, $form->{po_number}, + $department_id, $id + ) || $form->dberror($query); + + # add items + foreach $item (@orderitems) { + for ( + qw( + qty sellprice discount project_id ship) + ) + { + $item->{$_} *= 1; + } + $query = qq| INSERT INTO orderitems (trans_id, parts_id, description, qty, sellprice, discount, unit, reqdate, @@ -2636,27 +2607,25 @@ sub consolidate_orders { VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)|; - $sth = $dbh->prepare($query); - $sth->execute( - $id, $item->{parts_id}, $item->{description}, - $item->{qty}, $item->{sellprice}, - $item->{discount}, $item->{unit}, - $form->{reqdate}, $item->{project_id}, - $item->{ship}, $item->{serialnumber}, - $item->{notes} - ) || $form->dberror($query); - - } - } - } - - - $rc = $dbh->commit; - - $rc; - -} + $sth = $dbh->prepare($query); + $sth->execute( + $id, $item->{parts_id}, + $item->{description}, $item->{qty}, + $item->{sellprice}, $item->{discount}, + $item->{unit}, $form->{reqdate}, + $item->{project_id}, $item->{ship}, + $item->{serialnumber}, $item->{notes} + ) || $form->dberror($query); + + } + } + } + + $rc = $dbh->commit; + $rc; + +} 1; -- cgit v1.2.3