- #=====================================================================
- # 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.
- #
- # This file contains source code included with or based on SQL-Ledger which
- # is Copyright Dieter Simader and DWS Systems Inc. 2000-2005 and licensed
- # under the GNU General Public License version 2 or, at your option, any later
- # version. For a full list including contact information of contributors,
- # maintainers, and copyright holders, see the CONTRIBUTORS file.
- #
- # Original Copyright Notice from SQL-Ledger 2.6.17 (before the fork):
- # Copyright (C) 2001
- #
- # Author: DWS Systems Inc.
- # Web: http://www.sql-ledger.org
- #
- # Contributors:
- #
- #======================================================================
- #
- # This file has undergone whitespace cleanup
- #
- #======================================================================
- #
- # Order entry module
- # Quotation
- #
- #======================================================================
- 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|
- 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,
- o.shippingpoint, o.shipvia, e.name AS employee,
- m.name AS manager, o.curr, o.ponumber
- FROM oe o
- JOIN $form->{vc} ct ON (o.$form->{vc}_id = ct.id)
- LEFT JOIN employee e ON (o.employee_id = e.id)
- LEFT JOIN employee m ON (e.managerid = m.id)
- LEFT JOIN exchangerate ex
- ON (ex.curr = o.curr AND ex.transdate = o.transdate)
- 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|
- SELECT DISTINCT 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, o.shippingpoint,
- o.shipvia, e.name AS employee, o.curr,
- o.ponumber
- FROM oe o
- JOIN $form->{vc} ct ON (o.$form->{vc}_id = ct.id)
- 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|
- JOIN inventory i ON (oi.parts_id = i.parts_id)
- |;
- }
- $query .= qq|
- LEFT JOIN employee e ON (o.employee_id = e.id)
- LEFT JOIN exchangerate ex
- ON (ex.curr = o.curr
- AND ex.transdate = o.transdate)
- WHERE o.quotation = '0'
- 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|
- AND i.warehouse_id = ?
- AND (
- SELECT SUM(i.qty)
- FROM inventory i
- 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 "" ) {
- $ordnumber = ($ordnumber eq 'ordnumber') ? 'ordnumber' : 'quonumber';
- $query .= " AND lower($ordnumber) LIKE ?";
- push @queryargs, $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) ) {
- $form->db_parse_numeric(sth=>$sth, hashref=>$ref);
- $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|
- 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|
- INSERT INTO oe
- (id, ordnumber, quonumber, transdate, vendor_id,
- customer_id, reqdate, shippingpoint, shipvia,
- notes, intnotes, curr, closed, department_id,
- employee_id, language_code, ponumber, terms,
- quotation)
- VALUES
- ($form->{id}, ?, ?, ?, ?,
- ?, ?, ?, ?,
- ?, ?, ?, ?, ?,
- ?, ?, ?, ?, ?)|;
- @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->{"ship_$i"} = 0 unless $form->{"ship_$i"};
- $form->db_prepare_vars( "orderitems_id_$i", "id_$i", "description_$i",
- "project_id_$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"},
- $form->{taxaccounts} );
- 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 (|;
- $query .= qq|
- trans_id, parts_id, description, qty, sellprice,
- discount, unit, reqdate, project_id, ship,
- serialnumber, notes)
- VALUES (|;
- $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|
- UPDATE oe set
- ordnumber = ?,
- quonumber = ?,
- transdate = ?,
- vendor_id = ?,
- customer_id = ?,
- amount = ?,
- netamount = ?,
- reqdate = ?,
- taxincluded = ?,
- shippingpoint = ?,
- shipvia = ?,
- notes = ?,
- intnotes = ?,
- curr = ?,
- closed = ?,
- quotation = ?,
- department_id = ?,
- employee_id = ?,
- language_code = ?,
- ponumber = ?,
- 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;
- }
- sub delete {
- my ( $self, $myconfig, $form ) = @_;
- # connect to database
- my $dbh = $form->{dbh};
- # 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);
- my $spoolfile;
- my @spoolfiles = ();
- while ( ($spoolfile) = $sth->fetchrow_array ) {
- push @spoolfiles, $spoolfile;
- }
- $sth->finish;
- $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;
- }
- 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|
- 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|
- SELECT o.ordnumber, o.transdate, o.reqdate, o.terms,
- o.taxincluded, o.shippingpoint, o.shipvia,
- o.notes, o.intnotes, o.curr AS currency,
- e.name AS employee, o.employee_id,
- o.$form->{vc}_id, vc.name AS $form->{vc},
- o.amount AS invtotal, o.closed, o.reqdate,
- o.quonumber, o.department_id,
- d.description AS department, o.language_code,
- o.ponumber
- FROM oe o
- JOIN $form->{vc} vc ON (o.$form->{vc}_id = vc.id)
- LEFT JOIN employee 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);
- $form->db_parse_numeric(sth=>$sth, hashref=>$ref);
- 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|
- 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,
- o.reqdate, o.project_id, o.ship, o.serialnumber,
- o.notes, pr.projectnumber, pg.partsgroup,
- p.partsgroup_id, p.partnumber AS sku,
- p.listprice, p.lastcost, p.weight, p.onhand,
- p.inventory_accno_id, p.income_accno_id,
- p.expense_accno_id, t.description
- AS partsgrouptranslation
- FROM orderitems o
- JOIN parts p ON (o.parts_id = p.id)
- LEFT JOIN project pr ON (o.project_id = pr.id)
- LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
- LEFT JOIN translation t
- ON (t.trans_id = p.partsgroup_id
- 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|
- 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) ) {
- $form->db_parse_numeric(sth=>$sth, hashref=>$ref);
- ($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' );
- }
- 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|
- SELECT substr(value,1,3), value FROM defaults
- WHERE setting_key = 'curr'|;
- ( $form->{defaultcurrency}, $form->{currencies} ) =
- $dbh->selectrow_array($query);
- $query = qq|
- SELECT $buysell
- FROM exchangerate
- WHERE curr = ?
- AND transdate = ?|;
- 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} );
- my @exchangelist;
- @exchangelist = $eth1->fetchrow_array;
- $form->db_parse_numeric(sth=>$eth1, arrayref=>\@exchangelist);
- $form->{$var} = shift @array;
- if ( !$form->{$var} ) {
- $eth2->execute($var);
- @exchangelist = $eth2->fetchrow_array;
- $form->db_parse_numeric(sth=>$eth2, arrayref=>\@exchangelist);
- ( $form->{$var} ) = @exchangelist;
- ( $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|
- 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);
- $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|
- 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;
- my @qtylist = $sth->fetchrow_array;
- $form->db_parse_numeric(sth=>$sth, arrayref=>\@qtylist);
- ($qty) = @qtylist; $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;
- }
- 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|
- SELECT p.partnumber, p.description, p.unit, a.qty,
- pg.partsgroup, p.partnumber AS sku, p.assembly, p.id,
- p.bin
- FROM assembly a
- JOIN parts p ON (a.parts_id = p.id)
- LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
- 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) ) {
- $form->db_parse_numeric(sth=>$sth, hashref=>$ref);
- 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 $query = qq|
- SELECT description
- FROM project
- WHERE id = $id|;
- ($_) = $dbh->selectrow_array($query);
- $_;
- }
- sub get_warehouses {
- 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);
- while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
- push @{ $form->{all_warehouse} }, $ref;
- }
- $sth->finish;
- }
- 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|
- SELECT serialnumber, ship
- FROM orderitems
- WHERE trans_id = ?
- AND id = ?
- FOR UPDATE|;
- $sth = $dbh->prepare($query) || $form->dberror($query);
- $query = qq|
- SELECT sum(qty)
- FROM inventory
- WHERE parts_id = ?
- AND warehouse_id = ?|;
- $wth = $dbh->prepare($query) || $form->dberror($query);
- for my $i ( 1 .. $form->{rowcount} ) {
- $ship =
- ( abs( $form->{"ship_$i"} ) > abs( $form->{"qty_$i"} ) )
- ? $form->{"qty_$i"}
- : $form->{"ship_$i"};
- if ( $warehouse_id && $form->{type} eq 'ship_order' ) {
- $wth->execute( $form->{"id_$i"}, $warehouse_id )
- || $form->dberror;
- @qtylist = $wth->fetchrow_array;
- $form->db_parse_numeric(sth=>$wth, arrayref=>\@qtylist);
- ($qty) = @qtylist;
- $wth->finish;
- if ( $ship > $qty ) {
- $ship = $qty;
- }
- }
- if ($ship) {
- if ( !$form->{shippingdate} ) {
- $form->{shippingdate} = undef;
- }
- $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|
- UPDATE orderitems SET
- serialnumber = ?,
- ship = ?,
- reqdate = ?
- WHERE trans_id = ?
- AND id = ?|;
- $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;
- # update onhand for parts
- $form->update_balance(
- $dbh, "parts", "onhand",
- qq|id = $form->{"id_$i"}|,
- $form->{"ship_$i"} * $ml
- );
- }
- }
- my $rc = $dbh->commit;
- $rc;
- }
- sub adj_onhand {
- my ( $dbh, $form, $ml ) = @_;
- 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);
- $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 $ref;
- while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
- if ( $ref->{inventory_accno_id} || $ref->{assembly} ) {
- # do not update if assembly consists of all services
- if ( $ref->{assembly} ) {
- $ath->execute( $ref->{parts_id} )
- || $form->dberror($query);
- my ( $inv, $assembly ) = $ath->fetchrow_array;
- $ath->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 ) = @_;
- # 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 $id = $dbh->quote( $form->{id} );
- $query = qq|
- SELECT qty,
- (SELECT SUM(qty) FROM inventory
- WHERE trans_id = $id
- AND orderitems_id = ?) AS total
- 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|
- DELETE FROM inventory
- WHERE trans_id = ?
- AND qty = 0|;
- $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 .= "
- AND lower(p.partnumber) LIKE '$var'";
- }
- 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 .= "
- 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 .= "
- AND NOT i.warehouse_id = $towarehouse_id";
- }
- $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,
- i.warehouse_id
- FROM inventory i
- JOIN parts p ON (p.id = i.parts_id)
- LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
- JOIN warehouse w ON (w.id = i.warehouse_id)
- WHERE i.warehouse_id = $fromwarehouse_id
- $where
- GROUP BY p.id, p.partnumber, p.description,
- pg.partsgroup, w.description, i.warehouse_id
- ORDER BY $sortorder|;
- }
- else {
- if ($towarehouse_id) {
- $query = qq|
- SELECT p.id, p.partnumber, p.description,
- p.onhand,
- (SELECT SUM(qty)
- FROM inventory i
- WHERE i.parts_id = p.id) AS qty,
- pg.partsgroup, '' AS warehouse,
- 0 AS warehouse_id
- FROM parts p
- LEFT JOIN partsgroup pg
- ON (p.partsgroup_id = pg.id)
- WHERE p.onhand > 0
- $where
- UNION|;
- }
- $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,
- i.warehouse_id
- FROM inventory i
- JOIN parts p ON (p.id = i.parts_id)
- LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
- JOIN warehouse w ON (w.id = i.warehouse_id)
- WHERE i.warehouse_id != $towarehouse_id
- $where
- 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) ) {
- $form->db_parse_numeric(sth=>$sth, hashref=>$ref);
- $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|
- 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;
- $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|
- 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++ ) {
- if ( $form->{"ndx_$i"} ) {
- $sth->execute( $form->{"ndx_$i"} );
- while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
- $form->db_parse_numeric(sth=>$sth, hashref=>$ref);
- &add_items_required( "", $dbh, $form, $ref->{id},
- $ref->{required}, $ref->{assembly} );
- }
- $sth->finish;
- }
- }
- $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|
- 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) ) {
- $form->db_parse_numeric(sth=> $sth, hashref=> $ref);
- &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);
- $form->db_parse_numeric(sth=>$sth, hashref=>$ref);
- for ( keys %$ref ) {
- $form->{orderitems}{$parts_id}{$_} = $ref->{$_};
- }
- $sth->finish;
- $form->{orderitems}{$parts_id}{required} += $required;
- $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);
- # 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;
- }
- }
- 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|
- 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) ) {
- $form->db_parse_numeric(sth=>$sth, hashref=> $ref);
- $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|
- 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|
- 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|
- UPDATE oe SET
- ordnumber = ?,
- transdate = current_date,
- vendor_id = ?,
- customer_id = 0,
- amount = ?,
- netamount = ?,
- taxincluded = ?,
- curr = ?,
- employee_id = ?,
- 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;
- }
- 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->quote($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);
- # reset shipped
- $query = qq|
- UPDATE orderitems SET
- ship = 0
- WHERE trans_id = $id|;
- $dbh->do($query) || $form->dberror($query);
- }
- $ordnumber ||=
- $form->update_defaults( $myconfig, $numberfld, $dbh );
- #fixme: Change this
- $query = qq|
- INSERT INTO oe (ordnumber) VALUES ('$uid')|;
- $dbh->do($query) || $form->dberror($query);
- $query = qq|
- SELECT id
- FROM oe
- WHERE ordnumber = '$uid'|;
- ($id) = $dbh->selectrow_array($query);
- $ref->{employee_id} *= 1;
- $query = qq|
- UPDATE oe SET
- ordnumber = | . $dbh->quote($ordnumber) . qq|,
- transdate = current_date,
- vendor_id = $form->{vendor_id},
- customer_id = $form->{customer_id},
- amount = $amount,
- netamount = $netamount,
- reqdate = | . $form->dbquote( $ref->{reqdate}, SQL_DATE ) . qq|,
- taxincluded = '$ref->{taxincluded}',
- shippingpoint = | . $dbh->quote( $ref->{shippingpoint} ) . qq|,
- notes = | . $dbh->quote( $ref->{notes} ) . qq|,
- curr = '$curr',
- employee_id = $ref->{employee_id},
- intnotes = | . $dbh->quote( $ref->{intnotes} ) . qq|,
- shipvia = | . $dbh->quote( $ref->{shipvia} ) . qq|,
- language_code = '$ref->{language_code}',
- ponumber = | . $dbh->quote( $form->{ponumber} ) . qq|,
- department_id = $department_id
- WHERE id = $id|;
- $sth = $dbh->prepare($query);
- $sth->execute() || $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,
- project_id, ship, serialnumber, notes)
- 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;
- }
- 1;
|