#!/usr/bin/perl -Ilibs
use strict;
use warnings;

package API::Order::Process;

use Carp;
use Try::Tiny;
use List::Util;

use API::Warehouse;
use API::Warehouse::User;
use API::Order;
use API::Order::Payment;
use API::Order::Shipping;
use API::Order::Shipping::Shipment;
use API::Order::Shipping::BoxEstimate;
use API::Order::Shipping::Package;
use API::Utils::Scrub;
use ECS::Order;
use Data::Dumper;

our $Config;
BEGIN {
	use API::Config;
	$Config = $API::Config::Config;
}
use lib ($Config->{'Website_libs'});

use ECS::SQL;
use ECS::Error;
use ECS::Time;
use ECS::Config;
use ECS::Site;

use ECS::Order ();
use ECS::Shipping::Courier;
use ECS::Customer::Account;
use ECS::Payment::GiftCard ();
use ECS::EMail::Order;
use ECS::Warehouse::Picking ();
use ECS::Warehouse::Receiving;
use ECS::Warehouse::LPN;
use ECS::Product::Inventory::WIP;
use ECS::Product;
use ECS::Order::CartComponent;
use ECS::PTO;
use ECS::Shopify;
use ECS::Utils::HTML;
use Data::Dumper;
use PerlX::Maybe;

my $website_config = \%ECS::Config::c;

# maximum number of orders per picker
my %max_picker_fetch_orders = (
	"nextorders"	=>	6,
	"nextorderspriority" => 6,
	"osoob"     =>	25,
	"osoob_TMS" =>	25,
	"overstock"     =>  6,
	"priority_ship"	=>	6,
	"brakekit"		=>	6,
	"qualityControl" =>	6,
    "parkingspot" => 12,
);

my %status_map = map( (($_)x2), "Picking OSOOB", "Picking", "Picking Overstock");

my $max_payment_errors = 8; # We only allow 8 transactions to fail before we give up.  Verisign is probably down.

# wholesale drop ship flag product (WHSALEDROPSHIP), the presence of this product prevents the customer invoice from
# being printed out
our %WHSALE_FLAG_PRODUCTID = (
    1831891 => 1,
    2917998 => 1,
    3695375 => 1,
);

# Used for development.
#   - Fetch only one order at a time. if that one order fails payment, no others will be attempted
#   - To fetch a specific order, mark it as orders.priority = 'Priority'
if ($website_config->{'sandbox'}) {
	%max_picker_fetch_orders = map { $_ => 1 } (keys %max_picker_fetch_orders);
	$max_payment_errors = 1;
	
	$max_picker_fetch_orders{'osoob'} = 5; # just for testing
	$max_picker_fetch_orders{'osoob_TMS'} = 5; # just for testing
	$max_picker_fetch_orders{'qualityControl'} = 5; # just for testing
	$max_picker_fetch_orders{'parkingspot'} = 2; # just for testing
}

my $to_cents = sub {
	my $amount = shift;
	substr(
		my $cents = sprintf('%0.2f', $amount),
		-3,
		1
	) = '';
	$cents;
};

my $from_cents = sub {
	my $cents = shift;
	substr(
		my $amount = sprintf('%02.0f', $cents),
		-2,
		0
	) = '.';
	$amount;
};

# Fetches the next available orders that are GTG (good-to-go / ready to ship)
# processes payment on each order
# Requires
# - type (key out of $fetch_types_sql hash)
# - user
# - ipaddress
sub fetchNextGTG {  
	my $self = shift;
	my $input = shift;

	my $result = {
		'success' => 1,
		'error' => undef,
		'update_DB'	=> $Config->{'update_DB'}
	};
	
	return $result if (!$Config->{'update_DB'});
	
	if ( not defined $input->{'type'} ) {
		$result->{'success'} = 0;
		$result->{'error'} = 'Fetch type is not specified or not valid';
		return $result;
	}
	
	my $p = {
		'DBH' => ECS::SQL::connect(),
		'DBHr' => ECS::SQL::connect('SLAVE'),
		'input' => $input,
		'picker' => $input->{'user'},
	};
    my %status_map = (%status_map, %{ $input->{status_map} || {} });

    if ($input->{type} ne 'priority_ship') {
        @$input{ qw/type site/ } = split /_/ => $input->{type};
    }
	
	my $previous_fetched_orderIDs = ECS::Warehouse::Picking::getPickerOrderIDs($p);
	my $num_previous_fetched_orders = scalar(@{ $previous_fetched_orderIDs });

	$input->{site} = ECS::Site->fromName( $input->{site} || $ECS::Site::PRIMARY_SITE );
	local $max_picker_fetch_orders{ $input->{type} } =  $max_picker_fetch_orders{ $input->{type} };

    if ($input->{batch_maxOrders}) {
		$max_picker_fetch_orders{ $input->{type} } = $input->{batch_maxOrders}; 
		$num_previous_fetched_orders = 0;
	}

	
	if ($num_previous_fetched_orders >= $max_picker_fetch_orders{$input->{'type'}}) {
		$result->{'success'} = 0;
		$result->{'error'} = 'Maximum of '.$max_picker_fetch_orders{$input->{'type'}}.' orders have already been fetched';
		return $result;
	}
    my $qWarehousePickStationID = ECS::SQL::quote({
        'DBH'   => $p->{'DBH'},
        'value' => $p->{'input'}->{'warehousePickStationID'} || 0,
    });
	my $qWarehouseZoneID = ECS::SQL::quote({
        'DBH'   => $p->{'DBH'},
        'value' => $p->{'input'}->{'zoneID'} || 0,
    });

	my $stationFilter = $p->{'input'}->{'zoneID'}
		? " AND orders.warehouseZoneID = $qWarehouseZoneID "
		: " AND orders.warehousePickStationID = $qWarehousePickStationID ";

	my $orderSiteID = $input->{site}->siteID;
    my $site_filter = $input->{batch_siteID} ?  (sprintf ' AND %s.%s = %d ' => orders => siteID => $input->{batch_siteID}) : "";
	my $tms_prefix = ECS::Site::fromName(TMS)->prefix;
	# move batch types into second hash
	my %batch_types_sql = (

		priorityorders => <<~"",
			SELECT
				orders.orderID,
				SUM(cart.qty * COALESCE( productComponent.qtyRequired, 1 )) AS totalQty,
				MAX(IF(product.brakekit = 'YES',1,0)) AS hasBreakKits
			FROM orders
				JOIN orderShipping USING (orderID)
				LEFT JOIN shippingService USING (shippingServiceCode)
				LEFT JOIN cart ON (orders.orderID = cart.orderID)
				LEFT JOIN product ON (cart.productID = product.productID)
				LEFT JOIN productComponent ON (product.type = 'Kit' AND product.productID = productComponent.productID)
                LEFT JOIN shippingServiceGroup ON shippingServiceGroup.shippingServiceGroupID = COALESCE(orderShipping.originalShippingServiceGroupID, orderShipping.shippingServiceGroupID)
			WHERE
				orders.status = 'Received'
				AND (
					shippingService.expedited OR
                    shippingServiceGroup.priorityShipping OR
					orders.priority = 'Priority' 
				)
				$stationFilter
			GROUP BY
				cart.orderID
			HAVING
				hasBreakKits = 0 AND totalQty > 1
			ORDER BY
				orders.orderID

		wholesaleorders => <<~"",
			SELECT
				orders.orderID,
				SUM(cart.qty * COALESCE( productComponent.qtyRequired, 1 )) AS totalQty,
				MAX(IF(product.brakekit = 'YES',1,0)) AS hasBreakKits
			FROM orders
				JOIN orderShipping USING (orderID)
				LEFT JOIN shippingService USING (shippingServiceCode)
				LEFT JOIN cart ON (orders.orderID = cart.orderID)
				LEFT JOIN product ON (cart.productID = product.productID)
				LEFT JOIN productComponent ON (product.type = 'Kit' AND product.productID = productComponent.productID)
				LEFT JOIN customerGroupMatch cgm ON (orders.customerID = cgm.customerID AND orders.siteID = 1)
				LEFT JOIN $tms_prefix.customerGroupMatch tcgm ON (orders.customerID = tcgm.customerID AND orders.siteID = 2)
			WHERE
				orders.status = 'Received'
				AND COALESCE( cgm.customerGroupMatchID, tcgm.customerGroupMatchID ) IS NOT NULL
				$stationFilter
			GROUP BY
				cart.orderID
			HAVING
				hasBreakKits = 0 AND totalQty > 1
			ORDER BY
				orders.orderID
		
		"lateorders" => <<~"",
			SELECT
				orders.orderID,
				SUM(cart.qty * COALESCE( productComponent.qtyRequired, 1 )) AS totalQty,
				MAX(IF(product.brakekit = 'YES',1,0)) AS hasBreakKits
			FROM orders
				LEFT JOIN cart ON (orders.orderID = cart.orderID)
				LEFT JOIN product ON (cart.productID = product.productID)
				LEFT JOIN productComponent ON (product.type = 'Kit' AND product.productID = productComponent.productID)
			WHERE
				orders.status = 'Received'
				AND (
					LEAST(
						IF( orders.estimated_shipdate <> '$ECS::Order::BACK_ORDER_DATE', COALESCE(orders.estimated_shipdate, '$ECS::Order::ORDER_NO_DATE'), '$ECS::Order::ORDER_NO_DATE'),
						IF( orders.revised_ETS <> '$ECS::Order::BACK_ORDER_DATE', COALESCE(orders.revised_ETS, '$ECS::Order::ORDER_NO_DATE'), '$ECS::Order::ORDER_NO_DATE')
					 ) < DATE_FORMAT( NOW(), '%Y-%m-%d')
				)
				$stationFilter
			GROUP BY
				cart.orderID
			HAVING
				hasBreakKits = 0 AND totalQty > 1
			ORDER BY
				orders.orderID
		
		"todaysorders" => <<~"",
			SELECT
				orders.orderID,
				SUM(cart.qty * COALESCE( productComponent.qtyRequired, 1 )) AS totalQty,
				MAX(IF(product.brakekit = 'YES',1,0)) AS hasBreakKits
			FROM orders
				LEFT JOIN cart ON (orders.orderID = cart.orderID)
				LEFT JOIN product ON (cart.productID = product.productID)
				LEFT JOIN productComponent ON (product.type = 'Kit' AND product.productID = productComponent.productID)
			WHERE
				orders.status = 'Received'
				AND (
					LEAST(
						IF( orders.estimated_shipdate <> '$ECS::Order::BACK_ORDER_DATE', COALESCE(orders.estimated_shipdate, '$ECS::Order::ORDER_NO_DATE'), '$ECS::Order::ORDER_NO_DATE'),
						IF( orders.revised_ETS <> '$ECS::Order::BACK_ORDER_DATE', COALESCE(orders.revised_ETS, '$ECS::Order::ORDER_NO_DATE'), '$ECS::Order::ORDER_NO_DATE')
					 ) = DATE_FORMAT( NOW(), '%Y-%m-%d')
				)
				$stationFilter
			GROUP BY
				cart.orderID
			HAVING
				hasBreakKits = 0 AND totalQty > 1
			ORDER BY
				orders.orderID
		
		"futureorders" => <<~"",
			SELECT
				orders.orderID,
				SUM(cart.qty * COALESCE( productComponent.qtyRequired, 1 )) AS totalQty,
				MAX(IF(product.brakekit = 'YES',1,0)) AS hasBreakKits
			FROM orders
				LEFT JOIN cart ON (orders.orderID = cart.orderID)
				LEFT JOIN product ON (cart.productID = product.productID)
				LEFT JOIN productComponent ON (product.type = 'Kit' AND product.productID = productComponent.productID)
			WHERE
				orders.status = 'Received'
				AND (
					LEAST(
						IF( orders.estimated_shipdate <> '$ECS::Order::BACK_ORDER_DATE', COALESCE(orders.estimated_shipdate, '$ECS::Order::ORDER_NO_DATE'), '$ECS::Order::ORDER_NO_DATE'),
						IF( orders.revised_ETS <> '$ECS::Order::BACK_ORDER_DATE', COALESCE(orders.revised_ETS, '$ECS::Order::ORDER_NO_DATE'), '$ECS::Order::ORDER_NO_DATE')
					 ) > DATE_FORMAT( NOW(), '%Y-%m-%d')
				)
				$stationFilter
			GROUP BY
				cart.orderID
			HAVING
				hasBreakKits = 0 AND totalQty > 1
			ORDER BY
				orders.orderID
	
	);
	my $fetch_types_sql = {
		# fetch tpes for order batching
		%batch_types_sql,
		# Received orders: exclude one item order with type = 'Automotive' and any order with brake kits
		'nextorders' => "
			SELECT
				orders.orderID,
				SUM(cart.qty * COALESCE( productComponent.qtyRequired, 1 )) AS totalQty,
				MAX(IF(product.brakekit = 'YES',1,0)) AS hasBreakKits
			FROM orders
				JOIN orderShipping USING (orderID)
				LEFT JOIN shippingService USING (shippingServiceCode)
				LEFT JOIN cart ON (orders.orderID = cart.orderID)
				LEFT JOIN product ON (cart.productID = product.productID)
				LEFT JOIN productComponent ON (product.type = 'Kit' AND product.productID = productComponent.productID)
			WHERE
				orders.status = 'Received'
                $stationFilter
                $site_filter
			GROUP BY
				cart.orderID
			HAVING
				hasBreakKits = 0 AND totalQty > 1
			ORDER BY
				orders.orderID
		",
			# orders with status "Quality Control" irrespective of any warehouse pick station
		'qualityControl' => "
			SELECT
				orders.orderID
			FROM orders
			WHERE
				orders.status = 'Quality Control'
			GROUP BY
				orders.orderID
			ORDER BY
				orders.orderID
		",
		# Received one item orders, that DO have pictures and DO NOT contain kits, assembly nor brake kits
		'osoob' => "
			SELECT
				orders.orderID,
				SUM(cart.qty * COALESCE( productComponent.qtyRequired, 1 )) AS totalQty,
				MAX(IF(product.brakekit = 'YES',1,0)) AS hasBreakKits
			FROM orders
				JOIN orderShipping USING (orderID)
				LEFT JOIN shippingService USING (shippingServiceCode)
				LEFT JOIN cart ON (orders.orderID = cart.orderID)
				LEFT JOIN product ON (cart.productID = product.productID)
				LEFT JOIN productComponent ON (product.type = 'Kit' AND product.productID = productComponent.productID)
			WHERE
				orders.status = 'Received'
                AND orders.siteID = $orderSiteID
                $stationFilter
			GROUP BY
				cart.orderID
			HAVING
				hasBreakKits = 0 AND totalQty = 1
			ORDER BY
				orders.orderID 
		",
		# get all priority orders not just priority ship orders
		# * orders marked priority
		# * expedited shipping
		# * orders with a ship date of today
		'nextorderspriority' => "
			SELECT
				orders.orderID,
				SUM(cart.qty * COALESCE( productComponent.qtyRequired, 1 )) AS totalQty,
				MAX(IF(product.brakekit = 'YES',1,0)) AS hasBreakKits
			FROM orders
				JOIN orderShipping USING (orderID)
				LEFT JOIN shippingService USING (shippingServiceCode)
				LEFT JOIN cart ON (orders.orderID = cart.orderID)
				LEFT JOIN product ON (cart.productID = product.productID)
				LEFT JOIN productComponent ON (product.type = 'Kit' AND product.productID = productComponent.productID)
                LEFT JOIN shippingServiceGroup ON shippingServiceGroup.shippingServiceGroupID = COALESCE(orderShipping.originalShippingServiceGroupID, orderShipping.shippingServiceGroupID)
			WHERE
				orders.status = 'Received'
				AND (
					shippingService.expedited OR
                    shippingServiceGroup.priorityShipping OR
					orders.priority = 'Priority' OR (
					LEAST(
						IF( orders.estimated_shipdate <> '$ECS::Order::BACK_ORDER_DATE', COALESCE(orders.estimated_shipdate, '$ECS::Order::ORDER_NO_DATE'), '$ECS::Order::ORDER_NO_DATE'),
						IF( orders.revised_ETS <> '$ECS::Order::BACK_ORDER_DATE', COALESCE(orders.revised_ETS, '$ECS::Order::ORDER_NO_DATE'), '$ECS::Order::ORDER_NO_DATE')
					 ) <= DATE_FORMAT( NOW(), '%Y-%m-%d'))
				)
				$stationFilter
			GROUP BY
				cart.orderID
			HAVING
				hasBreakKits = 0 AND totalQty > 1
			ORDER BY
				orders.orderID
		",

		# batch orders priority == due today and marked priority
		'batchorderspriority' => "
			SELECT
				orders.orderID,
				SUM(cart.qty * COALESCE( productComponent.qtyRequired, 1 )) AS totalQty,
				MAX(IF(product.brakekit = 'YES',1,0)) AS hasBreakKits
			FROM orders
				JOIN orderShipping USING (orderID)
				LEFT JOIN shippingService USING (shippingServiceCode)
				LEFT JOIN cart ON (orders.orderID = cart.orderID)
				LEFT JOIN product ON (cart.productID = product.productID)
				LEFT JOIN productComponent ON (product.type = 'Kit' AND product.productID = productComponent.productID)
                LEFT JOIN shippingServiceGroup ON shippingServiceGroup.shippingServiceGroupID = COALESCE(orderShipping.originalShippingServiceGroupID, orderShipping.shippingServiceGroupID)
			WHERE
				orders.status = 'Received'
				AND (
					(shippingService.expedited OR
                    shippingServiceGroup.priorityShipping OR
					orders.priority = 'Priority') AND (
					LEAST(
						IF( orders.estimated_shipdate <> '$ECS::Order::BACK_ORDER_DATE', COALESCE(orders.estimated_shipdate, '$ECS::Order::ORDER_NO_DATE'), '$ECS::Order::ORDER_NO_DATE'),
						IF( orders.revised_ETS <> '$ECS::Order::BACK_ORDER_DATE', COALESCE(orders.revised_ETS, '$ECS::Order::ORDER_NO_DATE'), '$ECS::Order::ORDER_NO_DATE')
					 ) <= DATE_FORMAT( NOW(), '%Y-%m-%d'))
				)
				$stationFilter
			GROUP BY
				cart.orderID
			HAVING
				hasBreakKits = 0 AND totalQty > 1
			ORDER BY
				orders.orderID
		",

		# batch orders == not due today, priority is irrelevant
		'batchorders' => "
			SELECT
				orders.orderID,
				SUM(cart.qty * COALESCE( productComponent.qtyRequired, 1 )) AS totalQty,
				MAX(IF(product.brakekit = 'YES',1,0)) AS hasBreakKits
			FROM orders
				JOIN orderShipping USING (orderID)
				LEFT JOIN shippingService USING (shippingServiceCode)
				LEFT JOIN cart ON (orders.orderID = cart.orderID)
				LEFT JOIN product ON (cart.productID = product.productID)
				LEFT JOIN productComponent ON (product.type = 'Kit' AND product.productID = productComponent.productID)
                LEFT JOIN shippingServiceGroup ON shippingServiceGroup.shippingServiceGroupID = COALESCE(orderShipping.originalShippingServiceGroupID, orderShipping.shippingServiceGroupID)
			WHERE
				orders.status = 'Received'
				AND ((
					LEAST(
						IF( orders.estimated_shipdate <> '$ECS::Order::BACK_ORDER_DATE', COALESCE(orders.estimated_shipdate, '$ECS::Order::ORDER_NO_DATE'), '$ECS::Order::ORDER_NO_DATE'),
						IF( orders.revised_ETS <> '$ECS::Order::BACK_ORDER_DATE', COALESCE(orders.revised_ETS, '$ECS::Order::ORDER_NO_DATE'), '$ECS::Order::ORDER_NO_DATE')
					 ) > DATE_FORMAT( NOW(), '%Y-%m-%d'))
				)
				$stationFilter
			GROUP BY
				cart.orderID
			HAVING
				hasBreakKits = 0 AND totalQty > 1
			ORDER BY
				orders.orderID
		",


		# batch orders due today == due today, but not priority
		'batchordersduetoday' => "
			SELECT
				orders.orderID,
				SUM(cart.qty * COALESCE( productComponent.qtyRequired, 1 )) AS totalQty,
				MAX(IF(product.brakekit = 'YES',1,0)) AS hasBreakKits
			FROM orders
				JOIN orderShipping USING (orderID)
				LEFT JOIN shippingService USING (shippingServiceCode)
				LEFT JOIN cart ON (orders.orderID = cart.orderID)
				LEFT JOIN product ON (cart.productID = product.productID)
				LEFT JOIN productComponent ON (product.type = 'Kit' AND product.productID = productComponent.productID)
                LEFT JOIN shippingServiceGroup ON shippingServiceGroup.shippingServiceGroupID = COALESCE(orderShipping.originalShippingServiceGroupID, orderShipping.shippingServiceGroupID)
			WHERE
				orders.status = 'Received'
				AND (
					NOT ( COALESCE(shippingService.expedited,0) OR
                    COALESCE(shippingServiceGroup.priorityShipping,0) OR
					orders.priority = 'Priority') AND (
					LEAST(
						IF( orders.estimated_shipdate <> '$ECS::Order::BACK_ORDER_DATE', COALESCE(orders.estimated_shipdate, '$ECS::Order::ORDER_NO_DATE'), '$ECS::Order::ORDER_NO_DATE'),
						IF( orders.revised_ETS <> '$ECS::Order::BACK_ORDER_DATE', COALESCE(orders.revised_ETS, '$ECS::Order::ORDER_NO_DATE'), '$ECS::Order::ORDER_NO_DATE')
					 ) <= DATE_FORMAT( NOW(), '%Y-%m-%d'))
				)
				$stationFilter
			GROUP BY
				cart.orderID
			HAVING
				hasBreakKits = 0 AND totalQty > 1
			ORDER BY
				orders.orderID
		",
		'expedited' => "
			SELECT
				orders.orderID,
				SUM(cart.qty * COALESCE( productComponent.qtyRequired, 1 )) AS totalQty,
				MAX(IF(product.brakekit = 'YES',1,0)) AS hasBreakKits
			FROM orders
				JOIN orderShipping USING (orderID)
				LEFT JOIN shippingService USING (shippingServiceCode)
				LEFT JOIN cart ON (orders.orderID = cart.orderID)
				LEFT JOIN product ON (cart.productID = product.productID)
				LEFT JOIN productComponent ON (product.type = 'Kit' AND product.productID = productComponent.productID)
                LEFT JOIN shippingServiceGroup ON shippingServiceGroup.shippingServiceGroupID = COALESCE(orderShipping.originalShippingServiceGroupID, orderShipping.shippingServiceGroupID)
			WHERE
				orders.status = 'Received'
				AND (
					(shippingService.expedited OR
                    shippingServiceGroup.priorityShipping) AND (
					LEAST(
						IF( orders.estimated_shipdate <> '$ECS::Order::BACK_ORDER_DATE', COALESCE(orders.estimated_shipdate, '$ECS::Order::ORDER_NO_DATE'), '$ECS::Order::ORDER_NO_DATE'),
						IF( orders.revised_ETS <> '$ECS::Order::BACK_ORDER_DATE', COALESCE(orders.revised_ETS, '$ECS::Order::ORDER_NO_DATE'), '$ECS::Order::ORDER_NO_DATE')
					 ) <= DATE_FORMAT( NOW(), '%Y-%m-%d'))
				)
				$stationFilter
			GROUP BY
				cart.orderID
			HAVING
				hasBreakKits = 0 AND totalQty > 1
			ORDER BY
				orders.orderID
		",
		'priority_ship' => "
			SELECT
				orders.orderID
			FROM orders
				JOIN orderShipping USING (orderID)
				LEFT JOIN shippingService USING (shippingServiceCode)
				LEFT JOIN cart ON (orders.orderID = cart.orderID)
				LEFT JOIN product ON (cart.productID = product.productID)
                LEFT JOIN shippingServiceGroup ON shippingServiceGroup.shippingServiceGroupID = COALESCE(orderShipping.originalShippingServiceGroupID, orderShipping.shippingServiceGroupID)
			WHERE
				orders.status = 'Received'
				AND (shippingService.expedited OR shippingServiceGroup.priorityShipping)
                $stationFilter
			GROUP BY
				cart.orderID
			HAVING
				MAX(IF(product.brakekit = 'YES',1,0)) = 0
			ORDER BY
				orders.orderID
		",
		'brakekit' => "
			SELECT
				orders.orderID
			FROM (
				SELECT
					orders.orderID,
					MAX(IF(product.brakekit = 'YES',1,0)) AS brakekit
				FROM orders
					JOIN orderShipping USING (orderID)
					LEFT JOIN shippingService USING (shippingServiceCode)
					LEFT JOIN cart ON (orders.orderID = cart.orderID)
					LEFT JOIN product ON (cart.productID = product.productID)
				WHERE
					orders.status = 'Received'
                    $stationFilter
				GROUP BY
					orders.orderID
				HAVING
					brakekit = 1
				ORDER BY
					orders.orderID
			) AS orders
		",
        'parkingspot' => "
            SELECT
                orders.orderID
            FROM orders
            WHERE
                orders.status = 'Parking Spot'
                $stationFilter
                $site_filter
            GROUP BY
                orders.orderID
            ORDER BY
                orders.orderID
        "
	};
	
	$fetch_types_sql->{'overstock'} = $fetch_types_sql->{'nextorders'};
	
	if ( not defined $fetch_types_sql->{$input->{'type'}} ) {
		$result->{'success'} = 0;
		$result->{'error'} = 'Fetch type is not specified or not valid';
		return $result;
	}

	my $get_orderIDs = $p->{'DBHr'}->prepare($fetch_types_sql->{$input->{'type'}});
	my $exec_success =
		try { $get_orderIDs->execute; 1 }
		catch { warn $_; 0 };

	unless ($exec_success) {
		$result->{'success'} = 0;
		$result->{'error'} = 'Failed to Fetch Orders';
		return $result;
	}
	
	my @orderIDs = ();
	
	while (my $ord = $get_orderIDs->fetchrow_hashref) {
		push(@orderIDs, $ord->{'orderID'});
	}
	unless (@orderIDs) {
		$result->{'success'} = 0;
		$result->{'error'} = 'There are no orders available for picking.';
		return $result;
	}
	
	my $orders = ECS::Order->loadMulti({
		'DBH'     => $p->{'DBH'},
		'orderID' => \@orderIDs
	});

    # prepare to load the payment info
    $_->payments([]) for values %{ $orders };

    # load payments in bulk
    my $payments = ECS::Order::OrderPayment->loadMulti({
        'DBH'     => $p->{'DBH'},
        'orderID' => \@orderIDs,
        '_return' => "ARRAY",
    });
    for my $payment (@{ $payments }) {
        my $order = $orders->{ $payment->{'orderID'} };
        push @{ $order->payments }, $payment;
    }
	
	my $num_fetched = 0;
	
	$input->{'DBH'} = $p->{'DBH'};

    local $p->{'orders'} = [ values %{ $orders } ];
    my $locations = ECS::Warehouse::Location::search({
        'dbh'    => $p->{'DBHr'},
        'search' => [],
    });
    local $p->{'locations'} = { map { $_->locID => $_ } @{ $locations } };
    local $p->{'ordersToPick'} = $max_picker_fetch_orders{ $p->{'input'}->{'type'} };
    $p->{ordersToPick} = $input->{batch}{maxOrders} if $input->{batch} && $input->{batch}{maxOrders};
    local $p->{'pickingType'} = $input->{'type'};

    my @allOrderChanges = ();
    my @pickOrders = ECS::Warehouse::Picking::nextOrdersToPick($p);
    my @fetchedOrders;
    my $batch = $input->{batch};
    my $totalWeight = 0;
    my $maxWeight = +'inf';
    for my $order (@pickOrders) {
		$order->scrub;
		
		local $p->{'orderID'} = $order->orderID;
		
		
        next unless $order->readyToPick($input->{'type'});
        next if ($totalWeight + $order->weight) > $maxWeight;
        $totalWeight += $order->weight;

        $p->{'order'} = $order;
        
        # User and IP Address to log with orderstatus records
        $order->user($input->{'user'});
        $order->ipaddress($input->{'ipaddress'});
        
        # Lock the order only if order status is still 'Received'
        
        $p->{'reqStatus'} = 'Received';
		$p->{'reqStatus'} = 'Quality Control' if($input->{'type'} eq 'qualityControl');
        $p->{'reqStatus'} = 'Parking Spot' if($input->{'type'} eq 'parkingspot');
        next unless API::Order::lock($p);

        my %changes = ( 'orderID' => $order->{ 'orderID' } );
        if ($input->{'type'} eq 'osoob') {
            local $p->{'input'}->{'orderIDs'} = [$order->{'orderID'}];
            local $p->{picking} = 1;
            my $individualProducts = API::Order::Cart::getIndividualProducts($p);
            # move 1 qty from shelf to WIP_OSOO	
            my $qtyMoved = ECS::Product::Inventory::WIP::adjustWipQty({
                'dbh'   => $p->{'DBH'},
                'info'  => {
                    'productID' => $individualProducts->{$order->{'orderID'}}->[0]->{'productID'},
                    'fromLoc'   => 'primaryOnShelf',
                    'toLoc'     => 'osoo',
                    'qtyToMove' => 1
                }
            });
            
            # update the order status, based on the result from updating wip osoo 
            if ($qtyMoved) {
                $changes{ 'status' } = $status_map{ 'Picking OSOOB' };
            } else {
                $changes{ 'status' } = "Processing Error";
                $changes{ 'notes' } = "Could not update WIP OSOO qty";
                push @allOrderChanges, \%changes;
                next;
            }
            
        } elsif ($input->{'type'} eq 'overstock') {
            $changes{ 'status' } = $status_map{ 'Picking Overstock' };
        } else {
            $changes{ 'status' } = $status_map{ 'Picking' };
        }
        
        $changes{'picker'} = $input->{'user'};

        # set the cart component records
        ECS::Order::CartComponent::deleteAllCartComponents({
            'DBH'     => $p->{'DBH'},
            'orderID' => $order->orderID,
            'table'   => "cartComponentShipped",
        });
        ECS::Order::CartComponent::pushCartComponents({
            'DBH'     => $p->{'DBH'},
            'orderID' => $order->orderID,
            'siteID'  => $order->site->siteID,
            'table'   => "cartComponentPicked",
        });

        push @allOrderChanges, \%changes;
        $num_fetched ++;
        push @fetchedOrders, $order->orderID;
	}
	
	if (not $num_fetched) {
		$result->{'success'} = 0;
		$result->{'error'} = 'Failed to fetch new orders';
		return $result;
	}

    my $plhStatus = join ", ", ( "( ?, ?, ?, NOW() )" ) x @allOrderChanges;
    my $sqlStatus = qq{
        INSERT INTO orderstatus
            ( status, notes, orderID, timestamp )
        VALUES
            $plhStatus
    };
    my $sthStatus = $p->{ 'DBH' }->prepare( $sqlStatus );
    $sthStatus->execute(
        map {
            $_->{ 'status' },
            $_->{ 'notes' },
            $_->{ 'orderID' },
        } @allOrderChanges
    );

    my $plhOrder = join ", ", ( "( ?, ?, ? )" ) x @allOrderChanges;
    my $sqlOrder = qq{
        INSERT INTO orders
            ( orderID, status, picker )
        VALUES
            $plhOrder
        ON DUPLICATE KEY UPDATE
            status = VALUES( status ),
            picker = VALUES( picker )
    };
    my $sthOrder = $p->{ 'DBH' }->prepare( $sqlOrder );
    $sthOrder->execute(
        map {
            $_->{ 'orderID' },
            $_->{ 'status' },
            $_->{ 'picker' },
        } @allOrderChanges
    );

	$result->{data}{orderIDs} = \@fetchedOrders if ($input->{returnOrders} && @fetchedOrders);
	
	return $result;
} 

# authorize payments on any listed orders, returning
# the IDs of successfully authorized orders
# Requires
#  - orderIDs => ArrayRef[Int]
# Returns
#  - orderIDs => ArrayRef[Int]
sub prepareGTG {
    my ($self, $input) = @_;

	my $result = {
		'success' => 1,
		'error' => undef,
		'data' => {},
		'update_DB'	=> $Config->{'update_DB'}
	};

    my $p = {
        'DBH' => ECS::SQL::connect(),
    };

    my $orders = ECS::Order->loadMulti({
        'DBH'     => $p->{'DBH'},
        'orderID' => $input->{'orderIDs'} || [],
    });
    
    my @preparedOrderIDs = ();
    for my $orderID (keys %{ $orders }) {
        my $order = $orders->{ $orderID };
        local $p->{'order'} = $order;

        $order->scrub;

        # must be ready to pick for either nextorders or overstock
        if(not $order->readyToPick("nextorders")
            and not $order->readyToPick("overstock")
            ) {
            next;
        }

        $order->user("cron");

        local $p->{'reqStatus'} = "Received";
        local $p->{'reqStatus'} = "Parking Spot" if ($order->orderLocation->orderLocationID);
        next unless API::Order::lock($p);

        # authorize the payments
        if($order->automatePayments("fetch")) {
            if ($order->reviewPaymentFraud) {
                $order->status('Processing Error');
                $order->addNote('Review Payment Fraud');
            }
            else {
                push @preparedOrderIDs, $orderID;
            }

        }
        else {
            $order->status("Processing Error");
            $order->addNote("Payment Failed");
        }

        $order->save or carp "Failed to save order $orderID";
    }

    $result->{'data'}->{'orderIDs'} = \@preparedOrderIDs;

    return $result;
}

# Get the orderID for the next Picked OOB or OSOOB of the specified product
# Requires
# - productID
# - type
# Returns
# - orderID
sub getNextPickedOneItemOrder { 
	my $self = shift;
	my $input = shift;
	
	my $result = {
		'success' => 1,
		'error' => undef,
		'data' => {},
		'update_DB'	=> $Config->{'update_DB'}
	};
	
	if (!defined $input->{'oneItemOrderType'} || !$input->{'oneItemOrderType'}) {
		$result->{'success'} = 0;
		$result->{'error'} = 'No One Item Order Type specified.';
		return $result;
	}

	if (!defined $input->{'productID'} || !$input->{'productID'}) {
		$result->{'success'} = 0;
		$result->{'error'} = 'No Product specified to get next Picked $input->{oneItemOrderType} order.';
		return $result;
	}
	
	
	my $DBH = ECS::SQL::connect();
	my $DBHr = ECS::SQL::connect('SLAVE');
	
	my $p = {
		'DBH'  => $DBH,
		'DBHr' => $DBHr,
		'input' => $input
	};
	
	my $query = qq(
		SELECT 
			orders.orderID, 
			orders.qty, 
			count(*) AS totalItems
		 FROM (
			SELECT
				DISTINCT(cart.orderID) AS orderID,
				SUM(cart.qty * COALESCE( cartComponentPicked.required, 1)) AS qty
			FROM cart
				JOIN orders USING(orderID)
			LEFT JOIN
				product USING (productID)
			LEFT JOIN
				cartComponentPicked USING (cartitemID)
			WHERE orders.status = ? AND (cart.productID = ? OR productComponent.componentProductID = ?)
			GROUP BY orderID HAVING qty = 1
		) AS orders
			JOIN cart USING(orderID)
			JOIN orderShipping USING (orderID)
			LEFT JOIN shippingService USING (shippingServiceCode)
		GROUP BY orders.orderID HAVING totalItems = 1
		ORDER BY orders.orderID ASC
		LIMIT 1
	);
	
	my ($sth, $rd);
	
	my $pickedStatus = 'Picked '.uc($p->{'input'}->{'oneItemOrderType'});
	
	# fetch only the next $pickedStatus order that still has only one item
	eval {
		$sth = $p->{'DBHr'}->prepare($query);
		$sth->execute($pickedStatus, @{ $p->{'input'} }{qw/productID/x2} );
		$rd = $sth->fetchall_arrayref({});
	};
	
	if($@ or $sth->err) {
		$result->{'success'} = 0;
		$result->{'error'} = 'Cannot fetch next '.$pickedStatus.' order';
		return $result;
	}
	
	if( !defined $rd or !scalar(@{$rd}) ) {
		$result->{'success'} = 0;
		$result->{'error'} = 'No more '.$pickedStatus.' orders for ES'.$p->{'input'}->{'productID'}.". Place product in Restock bin.";
		return $result;
	}
		
	# Get printer info for printing the Pick List at the packing station
	if (API::Warehouse::getStationPrinters($p)) {
		$result->{'data'}->{'labelPrinter'} = $p->{'labelPrinter'};
		$result->{'data'}->{'shippingLabelPrinter'} = $p->{'shippingLabelPrinter'};
	} else {
		$result->{'success'} = 0;
		$result->{'error'} = 'Could not retrieve Warehouse Printer location';
		return $result;
	}
	
	my $order = ECS::Order->load({
		'DBH'     => $p->{'DBHr'},
		'orderID' => $rd->[0]->{'orderID'},
	});
	
	unless ($order) {
		$result->{'success'} = 0;
		$result->{'error'} = 'Unable to retrieve order details';
		return $result;
	}
	
	$p->{'input'}->{'orderIDs'} = [$order->orderID];
	my $orders_cart_products = API::Order::Cart::getCartProductsSummary($p);
	my $orders_indv_products = API::Order::Cart::getIndividualProducts($p);
	
	$order->scrub;
	
	$p->{'OIO'} = 1;
	$p->{'order'} = $order;
	$p->{'cartProducts'} = (
		$orders_cart_products->{$order->orderID} || []
	);
	$p->{'individualProducts'} = (
		$orders_indv_products->{$order->orderID} || []
	);

    $result->{ 'data' }->{ 'printCustomerLabel' } = 1;
	
	$result->{'data'}->{'orderID'} = $order->orderID;
	
	return $result;
}

# Get the orderID for the next Picked OOB or OSOOB of the specified input
# or productIDs if multiple products match input
# Requires
# - product
# - type
# Returns
# - orderID
sub getNextPickedOneItemOrderMulti {
	my $self = shift;
	my $input = shift;
	
	my $result = {
		'success' => 1,
		'error' => undef,
		'data' => {},
		'update_DB'	=> $Config->{'update_DB'}
	};

	if (!defined $input->{'product'} || !$input->{'product'}) {
		$result->{'success'} = 0;
		$result->{'error'} = 'No Product specified to get next Picked OOB order.';
		return $result;
	}
	
	if (!defined $input->{'oneItemOrderType'} || !$input->{'oneItemOrderType'}) {
		$result->{'success'} = 0;
		$result->{'error'} = 'No One Item Order Type specified.';
		return $result;
	}
	
	my $DBH  = ECS::SQL::connect();
	my $DBHr = ECS::SQL::connect('SLAVE');
	
    my $original_product = $input->{original_product} = $input->{product};
    if ($original_product =~ m/^.+-.*\d{5}$/ms) {
        $input->{product} =~ s{\d{5}$}{}ms;
        $input->{product} =~ s{[^A-Za-z0-9]+}{}gms;
    }

	my $p = {
		'DBH'  => $DBH,
		'DBHr' => $DBHr,
		'input' => $input
	};

	my $pickedStatus = 'Picked '.uc($p->{'input'}->{'oneItemOrderType'});
	my $error;
	# make fetching order testable, refactor to sub
   	my $rd = try { 
		nextPickedOneItemOrderMulti( $p ) 
	} catch {
		$error = $_;
		[];
	};
	if ($error) {
		$result->{'success'} = 0;
		$result->{'error'} = "Cannot fetch next $pickedStatus order ($error)";
		return $result;
	}
	
	if( !defined $rd or !scalar(@{$rd}) ) {
		$result->{'success'} = 0;
		$result->{'error'} = 'No more '.$pickedStatus.' orders for ES'.$p->{'input'}->{'productID'}.". Place product in Restock bin.";
		return $result;
	}
		
	# Get printer info for printing the Pick List at the packing station
	if (API::Warehouse::getStationPrinters($p)) {
		$result->{'data'}->{'labelPrinter'} = $p->{'labelPrinter'};
		$result->{'data'}->{'shippingLabelPrinter'} = $p->{'shippingLabelPrinter'};
	} else {
		$result->{'success'} = 0;
		$result->{'error'} = 'Could not retrieve Warehouse Printer location';
		return $result;
	}
	
	my %products;
	for my $o (@$rd) {
		my @p = split(/,/, $o->{productsBrands});
		my %product; 
		@product{qw/brandName productID/} = split /;/ => $_ for @p;
		$products{ $product{productID} } = \%product;
	}
	if (scalar keys %products > 1) {
		$result->{success} = 1;
		$result->{productIDs} = \%products;
		return $result;
	}
	
	
	my $order = ECS::Order->load({
		'DBH'     => $p->{'DBHr'},
		'orderID' => $rd->[0]->{'orderID'},
	});
	
	unless ($order) {
		$result->{'success'} = 0;
		$result->{'error'} = 'Unable to retrieve order details';
		return $result;
	}
	
	$p->{'input'}->{'orderIDs'} = [$order->orderID];
	my $orders_cart_products = API::Order::Cart::getCartProductsSummary($p);
	my $orders_indv_products = API::Order::Cart::getIndividualProducts($p);
	
	$order->scrub;
	
	$p->{'OIO'} = 1;
	$p->{'order'} = $order;
	$p->{'cartProducts'} = (
		$orders_cart_products->{$order->orderID} || []
	);
	$p->{'individualProducts'} = (
		$orders_indv_products->{$order->orderID} || []
	);
	
    $result->{ 'data' }->{ 'printCustomerLabel' } = 1;
	
	$result->{'data'}->{'orderID'} = $order->orderID;
	
	return $result;
}

# Get order details specific to Packing
# This compiles all of the data necessary to display the order detail information on the Pack page.
# The data is returned in $result, which is included on the pack page as JSON so that it can be 
# processed by the scanning javascript (pick_pack/includes/js/scanner.js).
#
# Requires
# - orderID
# - user
# - ipaddress
# - warehouseStation
# Returns
# - order
# - managers
# - ignoreShipmentReasons
# - overridePackReasons
# - packingConfig
# - giftCardsActivated
sub getPackOrderDetails { 
	my $self = shift;
	my $input = shift;
	
	my $result = {
		'success' => 1,
		'error' => undef,
		'data' => {},
		'update_DB'	=> $Config->{'update_DB'}
	};
	
	if ((!defined $input->{'orderID'} || !$input->{'orderID'}) && ( !defined $input->{'LPN'} || !$input->{'LPN'})) {
		$result->{'success'} = 0;
		$result->{'error'} = 'No orderID or LPN specified to get Packing Details';
		return $result;
	}
    # my %status_map = (%status_map, %{ $input->{status_map} || {} });
	
    my $DBH = ECS::SQL::connect();
    my $DBHr = ECS::SQL::connect('SLAVE');
    my $orderID;
    
    
    if ( defined $input->{'LPN'} ) {
    
        $input->{'LPN'} =~ s/^L0*//g;
        
        my $sql = "SELECT orderID from lpn WHERE lpnID = ? AND status=\"ASSIGNED\"";
        
        my $dbHandle = $DBHr->prepare($sql);
        $dbHandle->execute($input->{'LPN'});
        
        my $rd = $dbHandle->fetchall_arrayref({});
        
        if( !scalar @$rd ) {
          $result->{'success'} = 0;
		  $result->{'error'} = 'LPN scanned is not associated with any OrderID';
		  return $result;
        } else {        
            $orderID = $rd->[0]->{orderID};
        }
    }
    
    $orderID = $input->{'orderID'} if !defined $orderID;
     
	if (!defined $input->{'warehouseStation'} || !$input->{'warehouseStation'}) {
		$result->{'success'} = 0;
		$result->{'error'} = 'Warehouse Station ID not specified';
		$result->{'error'} .= '. Sandbox Developer Note: add warehouseStation=station1 to your URL to set the cookie' if ($website_config->{'sandbox'});
		return $result;
	}
	
	
	
	my $sql = "
		SELECT
			orderID,
			orders.siteID,
			CONCAT_WS('-', SUBSTR(refID,1,3), SUBSTR(refID,4,3), SUBSTR(refID,7)) AS pretty_refID,
			refID,
			status,
			picker,
			packer,
			order_time,
			customerID,
			shipping_country,
			shipping_zip,
            shipping_state,
			orderShipping.shippingServiceCode,
			orderShipping.dropship,
			orderShipping.estimateShipmentID,
			orderShipping.shipmentID,
			orderShipping.ignoreShipment,
			orderShipping.ignoreShipmentReasonID,
			orderShipping.overridePackReasonID,
			shipment.keyType AS shipmentKeyType
		FROM orders
			JOIN orderShipping USING (orderID)
			LEFT JOIN shipment USING(shipmentID)
		WHERE orderID = ".$orderID;
	
	my $rd = ECS::SQL::query({ 'DBH' => $DBHr, 'sql' => $sql });
	
	if (!$rd) {
		$result->{'success'} = 0;
		$result->{'error'} = 'Failed to fetch order details';
		return $result;
	}
	
	my $order = $rd->[0];
	
	API::Utils::Scrub::emptyToUndef($order);
	
	my $p = {
		'DBH' => $DBHr,
		'input' => $order,
		'order' => $order
	};
	
	$p->{'input'}->{'user'} = $input->{'user'};
	$p->{'input'}->{'ipaddress'} = $input->{'ipaddress'};
	$p->{'input'}->{'warehouseStation'} = $input->{'warehouseStation'};
	
	if (!API::Warehouse::getStationPrinters($p)) {
		$result->{'success'} = 0;
		$result->{'error'} = 'Could not retrieve Warehouse Printer location';
		return $result;
	}
	
	my @update_cols;
	
	if (
		$order->{'status'} eq $status_map{'Picking'}
		or $order->{'status'} eq $status_map{'Picking Overstock'} 
	) {
		# Update order status to 'Picked'
		$order->{'status'} = 'Picked';
		push(@update_cols, 'status');
	} elsif ($order->{'status'} eq $status_map{'Picking OSOOB'}) {
		# Update order status to 'Picked OSOOB'
		$order->{'status'} = 'Picked OSOOB';
		push(@update_cols, 'status');
	} elsif ($order->{'status'} =~ /^Picked/) {
		# Do nothing
	} else {
		$result->{'success'} = 0;
		$result->{'error'} = 'Order '.$order->{'orderID'}.' has status of '.$order->{'status'}.'.<br>Cannot be scanned at this point';
		return $result;
	}
	
	# don't update packer if input has noUpdatePacker set (for warehouseOS/verifyComplete
	unless ($input->{noUpdatePacker}) {
		if ($input->{'user'} ne ($order->{'packer'} || '')) {
			push(@update_cols, 'packer'); 
			# add order status note if updating packer from undefined
			if (!defined $order->{packer}) {
				push @update_cols, 'status';
			}
		}
	$order->{'packer'} = $input->{'user'};
	}
	
	if (scalar(@update_cols)) {
		$input->{'DBH'} = $DBH;
		$input->{'orders'} = [$order];
		$input->{'columns'} = \@update_cols;
		
		API::Order::updateOrders(undef, $input);
	}
	
	$order->{'shippingServiceName'} = ECS::Shipping::Courier::getServiceLabel($p);
	# get site
	$order->{site} = ECS::Site->fromID( $order->{siteID} );
	my $site = $order->{site};
	$p->{site} = $site;
	
	API::Order::Shipping::getCourier($p);
	
	$order->{'intl'} = ECS::Shipping::Courier::isInternational($p);
	
	# getIndividualProducts will switch useBrandedBoxes to 0 if needed
	$order->{'useBrandedBoxes'} = 1;
	
	$p->{'input'}->{'orderIDs'} = [$order->{'orderID'}];
	my $individual_products = API::Order::Cart::getIndividualProducts($p);
	$order->{'packProducts'} = $individual_products->{$order->{'orderID'}};

    # remove virtual products from the packing screen
    $order->{'packProducts'} = [ grep { not $_->{'virtual'} } @{ $order->{'packProducts'} } ];
	
	$order->{'needPics'} = 0;

    $order->{ 'autoPackNotAllowedReason' } = API::Order::Shipping::getAutoPackNotAllowedReason( {
        'order' => $order,
    } );
	
	foreach my $product (@{ $order->{'packProducts'} }) {
		$order->{'needPics'} = 1 if ($product->{'needPics'});
	}
	
	API::Order::Shipping::getService($p);
	API::Order::Shipping::getBoxLimits($p);
	API::Order::Shipping::getWarehouseShipment($p);
	API::Order::Shipping::getAvailableBoxes($p);
    {
        # this writes to the DB
        local $p->{ 'DBH' } = $DBH;
        local $p->{ 'DBHr' } = undef;
	    API::Order::Shipping::BoxEstimate::getBoxes($p);
    }
	API::Order::Shipping::Package::getExistingPackages($p);
	
	$order->{'giftCardsActivated'} = ECS::Payment::GiftCard::getGiftCardsActivatedOnOrder($p);
	
	$result->{'data'}->{'order'} = $order;
	$result->{'data'}->{'managers'} = API::Warehouse::getManagers($p);
    $result->{'data'}->{'packerID'} = API::Warehouse::User::getUserID($p);
	$result->{'data'}->{'ignoreShipmentReasons'} = API::Order::Shipping::Shipment::getIgnoreShipmentReasons($p);
	$result->{'data'}->{'overridePackReasons'} = API::Order::Shipping::Shipment::getOverridePackReasons($p);
	$result->{'data'}->{'packingConfig'} = $website_config->{'shipping'}->{'packing'};

    # print customer invoice at packing table for LPN scans
    if($input->{'LPN'}) {
        $result->{ 'data' }->{ 'printCustomerLabel' } = 1;
    }

	return $result;
}

sub getCustomerLabel {
    my ( $self, $input ) = @_;

	my $result = {
		'success'   => 1,
		'error'     => undef,
		'data'      => {},
		'update_DB'	=> $Config->{ 'update_DB' },
	};
	
    my $orderID = $input->{ 'orderID' };
	if ( not $orderID ) {
		$result->{ 'success' } = 0;
		$result->{ 'error' } = 'Need orderID for customer label';
		return $result;
	}

    my $warehouseStation = $input->{ 'warehouseStation' };
    if ( not $warehouseStation ) {
        $result->{ 'success' } = 0;
        $result->{ 'error' } = "Need warehouseStation for customer label";
    }
	
    my $DBH  = ECS::SQL::connect();
    my $DBHr = ECS::SQL::connect('SLAVE');
    my $p = {
        'DBH'   => $DBHr,
        'input' => {
            'orderID'          => $orderID,
            'orderIDs'         => [ $orderID ],
            'warehouseStation' => $warehouseStation,
        },
    };

    my $orders_cart_products = API::Order::Cart::getCartProductsSummary( $p );
    $p->{'cartProducts'} = (
        $orders_cart_products->{ $orderID } || []
    );

    # PS-2151: do not print a customer invoice if the wholesale drop ship flag product is present
    my $skip_customer_label = List::Util::first {
        $WHSALE_FLAG_PRODUCTID{ $_->{ 'productID' } }
    } @{ $p->{cartProducts} || [] };

    if ( $skip_customer_label
        and $input->{ 'mode' } eq "ship"
        ) {
        return $result;
    }

    $p->{'skipPickList'} = 1;
    $p->{'order'} = ECS::Order->load({
        'DBH'     => $DBHr,
        'orderID' => $orderID,
    });
    if (API::Warehouse::getStationPrinters($p)) {
        $result->{'data'}->{'labelPrinter'} = $p->{'labelPrinter'};
        $result->{'data'}->{'shippingLabelPrinter'} = $p->{'shippingLabelPrinter'};
        $result->{'data'}->{'formsPrinter'} = $p->{'formsPrinter'};
    }

    $p->{skipCustomerLabel} = $skip_customer_label;
    $result->{'data'}->{'pickListLabel'} = ECS::Warehouse::Picking::getOrderPickListLabel($p);

    return $result;
}

# called when SHIP barcode is scanned on packing screen
# Requires
# - user
# - ipaddress
# - warehouseStation
# - boxes
# - ignoreShipment (1/0)
# - ignoreShipmentReasonID
# - overridePackReasonID
# - orderID
# - giftCardsToActivate
# Returns in 'data'
# - shippingLabels
# - shippingForms
# - printer
# - formsprinter
sub shipOrder {
	my $self = shift;
	my $input = shift;
	
	ECS::Order::log_ptco($input->{'orderID'}, "Calling shipOrder in Process.pm");

	my $result = {
		'success' => 1,
		'error' => undef,
		'data' => {},
		'update_DB'	=> $Config->{'update_DB'}
	};
	
	unless ($input->{'orderID'}) {
		$result->{'success'} = 0;
		$result->{'error'} = 'No orderID specified to Ship';
		return $result;
	}
	
	if (!defined $input->{'boxes'} || !scalar(@{ $input->{'boxes'} })) {
		$result->{'success'} = 0;
		$result->{'error'} = 'No boxes specified to Ship';
		return $result;
	}
	
	return $result if (!$Config->{'update_DB'});

	my $DBH = ECS::SQL::connect();
	my $DBHr = ECS::SQL::connect('SLAVE');
	
	my $order = ECS::Order->load({
		'DBH'     => $DBH,
		'orderID' => $input->{'orderID'}
	});
	
	unless ($order) {
		$result->{'success'} = 0;
		$result->{'error'} = 'Order details could not be retrieved';
		return $result;
	}
	my $site = $order->site;

	local $ECS::Config::c{shipping} = $order->site->localConfig( q{shipping} );
	local $ECS::Config::c{taxID} = $order->site->localConfig( q{taxID} );

	# Copy Order-Shipping info into Order for ECS::Shipping $input support
	for my $k (
		'shippingServiceCode',
		'shipping_cost',
		'shipmentID',
        'originalShippingServiceCode',
	) {
		$order->{$k} = $order->shipping->{$k};
	}
	
	# Copy Order-Shipping-Shipment info into Order
	if (my $shipment = $order->shipping->shipment) {
		$order->{'shipmentKeyType'} = $shipment->{'keyType'};
	}
	
	$order->scrub;
	
	my $p = {
		'DBH'  => $DBH,
		'DBHr' => $DBHr,
		'order' => $order,
		'input' => $order,
		'orderID' => $order->{'orderID'},
		'site' => $order->site,
	};
	
	$p->{'input'}->{'user'} = $input->{'user'};
	$p->{'input'}->{'ipaddress'} = $input->{'ipaddress'};
	$p->{'input'}->{'warehouseStation'} = $input->{'warehouseStation'};
	
	unless (
		$order->status =~ /^Picked/ # order has one of the Picked statuses: Picked, Picked OOB, Picked OSOOB
	) {
		$result->{'success'} = 0;
		$result->{'error'} = 'Order '.$order->{'orderID'}.' has status of '.$order->status.'.<br>Cannot be shipped at this point';
		return $result;
	}
	
	if (API::Warehouse::getStationPrinters($p)) {
		$result->{'data'}->{'labelPrinter'} = $p->{'labelPrinter'};
		$result->{'data'}->{'shippingLabelPrinter'} = $p->{'shippingLabelPrinter'};
		$result->{'data'}->{'formsPrinter'} = $p->{'formsPrinter'};
	} else {
		$result->{'success'} = 0;
		$result->{'error'} = 'Could not retrieve Warehouse Printer location';
		return $result;
	}
	$p->{'reqStatus'} = $order->status;
	
	if (!API::Order::lock($p)) {
		$result->{'success'} = 0;
		$result->{'error'} = 'Failed to Lock Order '.$order->{'orderID'};
		return $result;
	}
	
	if (!API::Order::mmLock($p)) {
		$result->{'success'} = 0;
		$result->{'error'} = 'Failed to MM Transaction Lock Order '.$order->{'orderID'};
		return $result;
	}
	
	# User and IP Address to log with orderstatus records
	$order->user($input->{'user'});
	$order->ipaddress($input->{'ipaddress'});
	
	# Prepare the order to be MM Unlocked (when complete)
	$order->{'mmlock'} = 'NO';
	
	$order->logNote('Packing complete, Processing shipment.');
	
	# getIndividualProducts will switch useBrandedBoxes to 0 if needed
	$order->{'useBrandedBoxes'} = 1;
	
    # Get individual products
    $p->{'input'}->{'orderIDs'} = [$order->{'orderID'}];
    my $individual_products = API::Order::Cart::getIndividualProducts($p);
    $order->{'packProducts'} = $individual_products->{$order->{'orderID'}};

	API::Order::Shipping::getWarehouseShipment($p);

    # get all the services for optimization which meets
    # customer original expectation and if order is eligible
    API::Order::Shipping::getServicesForPackOptimization($p);
    
    # get rates and replace service as the least expensive one
    # if there were more services to check for
    $p->{'packBoxes'} = $input->{'boxes'};
    $p->{'isResidential'} = 1;
	if ( $p->{'isDealerPricing'}
		and uc($order->{'shipping_address1'}) eq uc($order->{'billing_address1'})
		and uc($order->{'shipping_address2'}) eq uc($order->{'billing_address2'})
		and uc($order->{'shipping_city'}) eq uc($order->{'billing_city'})
		and uc($order->{'shipping_country'}) eq uc($order->{'billing_country'})
		and uc($order->{'shipping_zip'}) eq uc($order->{'billing_zip'})
	) {
		$p->{'isResidential'} = 0;
	}

    # optimization will happen here
    if (
        scalar keys %{ $p->{'servicesForPackOptimization'} } > 1
        && ! $website_config->{'disablePackOptimization'}
    ) {
        API::Order::Shipping::optimizeShipping($p);
		ECS::Order::log_ptco($input->{'orderID'}, "sub shipOrder:shippingServiceCode: " . Dumper($p->{'order'}->{'shippingServiceCode'}));
		ECS::Order::log_ptco($input->{'orderID'}, "sub shipOrder:servicesForPackOptimization: " . Dumper($p->{'servicesForPackOptimization'}));
		ECS::Order::log_ptco($input->{'orderID'}, "sub shipOrder:shippingRates: " . Dumper($p->{shippingRates}));
    }

    API::Order::Shipping::getCourier($p);
    $order->{'intl'} = ECS::Shipping::Courier::isInternational($p);
    API::Order::Shipping::getService($p);
    API::Order::Shipping::getAvailableBoxes($p);

	$order->{'packages'} = $input->{'boxes'};
	$order->{'inputIgnoreShipment'} = $input->{'ignoreShipment'};
	$order->{'boxEstimatesVolume'} = $input->{'boxEstimatesVolume'};
	
	# If the packer ignore the estimated box sizes, but uses the same box sizes
	#    - overridePackReasonID is the reason why they did this
	#    - delete all ignoreShipment info. they actually used a previous shipment config
	if (
		defined $input->{'overridePackReasonID'}
		and $input->{'overridePackReasonID'}
	) {
		$order->{'overridePackReasonID'} = $input->{'overridePackReasonID'};
		
		delete $input->{'ignoreShipment'};
		delete $input->{'ignoreShipmentReasonID'};
		
		delete $order->{'inputIgnoreShipment'};
	}
	
	API::Order::Shipping::Package::savePackages($p);
	
	API::Order::Shipping::Package::buildPackageProductDetails($p);
	
	API::Order::Shipping::Shipment::savePreviousShipment($p);

	if (
		$input->{'ignoreShipment'}
		and defined $input->{'ignoreShipmentReasonID'}
		and $input->{'ignoreShipmentReasonID'}
	) {
		$order->{'ignoreShipmentReasonID'} = $input->{'ignoreShipmentReasonID'};
		$order->{'overridePackReasonID'} = undef;
	}
	
	ECS::Payment::GiftCard::pendActivateGiftCardsOnOrder({
		'DBH'                 => $p->{'DBH'},
		'order'               => $order,
		'giftCardsToActivate' => $input->{'giftCardsToActivate'},
		'site'                => $site,
	});
	
	$input->{'DBH'} = $DBH;
	$input->{'orders'} = [$order];
	
	# Copy Order Info into Order Shipping so that Order Shipping can be saved
	for my $k (
		'shipmentID',
		'ignoreShipment',
		'ignoreShipmentReasonID',
		'overridePackReasonID',
        'packOriginalServiceCode',
        'packOriginalCostActual',
        'shippingServiceCode',
	) {
		$order->shipping->{$k} = $order->{$k};
	}

    $order->shipping->{ 'warehouseStationID' } = $input->{'warehouseStation'};
    $order->shipping->{ 'packedAt' } = ECS::Time::epochToDatetime( time );

    # Update orderShipping record
    $order->shipping->save;
    
    # prevent order from being set back to 'Picked' in the database during automatePayments,
    # see ECS::Order `around 'automatePayments'`
    $order->status( "LOCKED" );
	my $payment_success = $order->automatePayments('ship'); # Will update the order
	
	# If payment was successful, but was marked as Fraud
	#    - Send order to "Holding Picked" (because it is boxed, needs to be in HOLDING bin
	#    - Print out "HOLD" labels
	#    - Skip purchasing shipping labels
	if ($order->reviewPaymentFraud) {
		
		$order->{'holdReason'} = 'Review Payment Fraud';
		
		$order->status('Holding Picked');
		$order->addNote($order->{'holdReason'});
		
		API::Order::Shipping::Package::holdPackages($p);
		
	} else {
		
		# lazy-build the customer object for non-moose support in shipping modules
		$order->customer; 
		
		delete $p->{'template'};
		
		if ($payment_success) {
			
			# Begin the handling of gift cards.
			my $numPhysicalGiftCards = 0;
			my $numActualProducts = 0;
			my $hasGiftCards = 0;
			foreach my $product (@{ $order->{'packProducts'} }) {
				if ($product->{'taxExempt'}) {
					$numPhysicalGiftCards++;
					$hasGiftCards = 1;
				} else {
					$numActualProducts++;
				}
			}
			
			# Activate the gift cards now.  This will ensure that orders even with only electronic 
			# gift cards are handled as well.  We won't print labels though for those types of orders.
			ECS::Payment::GiftCard::activateGiftCardsOnOrder({
				'DBH'                 => $p->{'DBH'},
				'order'               => $order,
				'giftCardsToActivate' => $input->{'giftCardsToActivate'},
				'site'                => $order->site,
			});
			
			if (ECS::Payment::GiftCard::deliverElectronicGiftCards($p)) {
				$hasGiftCards = 1;
			}
			
			if (
				$numPhysicalGiftCards
				and not $numActualProducts
			) {
				# If physical gift cards are being purchased and no other physical products are
				# being purchased, print an address label, put it on an envelope with a stamp,
				# and mail the envelope in regular mail.
				API::Order::Shipping::Package::getAddressLabel($p);
				$order->status('Shipped');
				$order->{'dtShipped'} = 'NOW()';
			} else {
				# If we have physical products, print a shipping label and ship the order.
				# This will also account for if we have physical gift cards, because we'll just
				# throw them in the box with the other products.
				API::Order::Shipping::Package::shipPackages($p);
				# update the shippingServiceCode; this is subject to change during the shipping process (i.e. Fedex Ground)
				$order->shipping->{'shippingServiceCode'} = $order->{'shippingServiceCode'};
			}
			
			if ($hasGiftCards) {
				local $p->{'purchasedGiftCards'} = $input->{'giftCardsToActivate'};
				ECS::Payment::GiftCard::addPurchasedNote($p);
			}
			
		} else {
			
			$order->{'holdReason'} = 'Payment FAILED';
            $order->status("Holding Picked");

            # if the courier has a custom way of handling payment failures, use it
            my $handledFailure;
            if($p->{'courier'}->{'paymentFailed'}) {
                $handledFailure = $p->{'courier'}->{'paymentFailed'}->($p);
            }

            # if the custom method fails or is non-existant, do the usual package holding stuff
            API::Order::Shipping::Package::holdPackages($p) unless $handledFailure;
			
		}
		
		# Notify the customer if an email template was specified
		ECS::EMail::Order::send($p) if (defined $p->{'template'});
		
	}
	
	# Copy Order Info into Order Shipping so that Order Shipping can be saved
	for my $k (
		'dtShipped',
		'shippingCostActual',
		'tracking_number',
	) {
		$order->shipping->{$k} = $order->{$k};
	}

	# Update Order
	unless ($order->save) {
		$result->{'success'} = 0;
		$result->{'error'} = 'Failed to Update Order';
		return $result;
	}
    
    $order->savePackOverrideMessage($input->{'packOverrideMessages'});
	
	if(defined $input->{'oneItemOrderType'} and $input->{'oneItemOrderType'}) {
		# move 1 qty out of WIP_OOB or WIP_OSOOB
		# code wise, the qty is moved fictively to shelf
		# but it will not be GTG for other orders due the status of the order 'Shipped'
		# it will wait to be invoiced	
		ECS::Product::Inventory::WIP::adjustWipQty({
			'dbh'   =>	$p->{'DBH'},
			'info'  => {
				'productID'	=>	$order->{'packProducts'}->[0]->{'productID'},
				'fromLoc'	=>	$input->{'oneItemOrderType'},
				'toLoc'		=>	'onshelf',
				'qtyToMove'	=>	1
				}
			});
	}
	
	# Build an array of shipping labels to send back
	
	my @shipping_labels;
	
	my @internal_labels;
	foreach my $package (grep defined($_->{shiplabel}), @{ $order->{'packages'} }) {
		push(@shipping_labels, $package->{'shiplabel'}); 
		push @internal_labels, $package->{internallabel} ? '1' : '0';
	}
	
	$result->{data}{internalLabels} = \@internal_labels;
	$result->{'data'}->{'shippingLabels'} = \@shipping_labels;
	$result->{'data'}->{'shippingForms'} = $order->{'shippingForms'};
	
	if ($order->status eq 'Shipped') {
        # create breakdown of cart component records at time of sale
        ECS::Order::CartComponent::pushCartComponentShipped({
            'DBH' => $DBH,
            'orderID' => $order->orderID,
            'siteID'  => $order->site->siteID,
        });
        
        API::Order::Payment::invoice({
            'DBH' => $DBH,
            'order' => $order,
            'input' => {
                'user' => $input->{'user'},
                'userID' => $input->{'userID'},
                'ipaddress' => $input->{'ipaddress'},
                reqStatus => $p->{reqStatus},
            },
        });
        
        ECS::Warehouse::LPN::clearLPNFromOrder({
            'dbh' => $DBH,
            'orderID' => $order->orderID,
        });

		$order->enqueue_shopify_fulfillment;
        $order->enqueue_lifetime_replacement;
		$order->enqueue_wesupply_fulfillment;
    }
	
	return $result;
}

# Directship Orders when they get shipped (get a PR tracking
# number or manually marked shipped)
# Requires
# - user
# - ipaddress
# - orderID
# Returns in 'data'
# - success
# - orderStatus
sub deliverDSOrder {
	my $self = shift;
	my $input = shift;
	
	my $result = {
		'success' => 1,
		'error' => undef,
		'data' => {},
		'update_DB'	=> $Config->{'update_DB'}
	};
	
	unless ($input->{'orderID'}) {
		$result->{'success'} = 0;
		$result->{'error'} = 'No orderID specified to Deliver';
		return $result;
	}

	my $pr_courier  = $input->{prCourier};
	my $newStatus 	= $input->{'newStatus'} || 'Shipped';
	my $shipping		= $input->{'updateShipping'} || 0;
	local $input->{template} = $input->{template};
	my $pr_count = $input->{prCount} || 0;
	my $order_complete = defined $input->{orderComplete} ? $input->{orderComplete} : 1;
	my $pr_items = defined $input->{prItems} ? $input->{prItems} : 0;
	$pr_count = 0 if $pr_count == 1 && $order_complete;
	
	return $result if (!$Config->{'update_DB'});
	
	my $DBH  = ECS::SQL::connect();
	my $DBHr = ECS::SQL::connect('SLAVE');
	
	my $order = ECS::Order->load({
		'DBH'     => $DBH,
		'orderID' => $input->{'orderID'}
	});
	
	unless ($order) {
		$result->{'success'} = 0;
		$result->{'error'} = 'Order details could not be retrieved';
		return $result;
	}

	local $ECS::Config::c{shipping} = $order->site->localConfig( q{shipping} );
	local $ECS::Config::c{taxID} = $order->site->localConfig( q{taxID} );

	$order->scrub;
	
	my $p = {
		'DBH'   => $DBH,
		'DBHr'  => $DBHr,
		'order' => $order,
		'site'  => $order->site,
	};

	$p->{'reqStatus'} = $order->status;
	
	if (!API::Order::lock($p)) {
		$result->{'success'} = 0;
		$result->{'error'} = 'Failed to Lock Order '.$order->{'orderID'};
		return $result;
	}
	
	if (!API::Order::mmLock($p)) {
		$result->{'success'} = 0;
		$result->{'error'} = 'Failed to MM Transaction Lock Order '.$order->{'orderID'};
		return $result;
	}
	
	# User and IP Address to log with orderstatus records
	$order->user($input->{'user'});
	$order->ipaddress($input->{'ipaddress'});
	
	# Prepare the order to be MM Unlocked (when complete)
	$order->{'mmlock'} = 'NO';
	
	my $payment_success = $order->automatePayments('ship'); # Will update the order
	
	# If payment was successful, but was marked as Fraud
	#    - Send order to "Processing Error" status
	if ($order->reviewPaymentFraud) {
		
		$order->status('Processing Error');
		$order->addNote('Review Payment Fraud');
		
	} elsif ($payment_success) {
		
		$order->status($newStatus);
		if ($newStatus eq 'Shipped') {
			$order->shipping->{'dtShipped'} = 'NOW()';
		}
		my $note = '';
		if ($input->{poID}) {
			$note .= 'PO#'.$input->{poID}.' ';
		}
		if ($input->{prID}) {
			$note .= 'PR#'.$input->{prID}.' ';
		}
		if ($note) {
			$order->addNote( $note );
		}
		
	} else {
		
		$order->status('Processing Error');
		$order->addNote('Payment FAILED');
		
	}
	my $orderID = $order->orderID;
	my $invoice = $pr_count ? ECS::Order::Invoice->new({ DBH => $DBH, DBHr => $DBHr, data => {orderID => $orderID }}) : undef;
	my @invoices = $order->getInvoices;
	# update shipping?
	if ($shipping) {
		for (qw/dtShipped/) {
			if (defined( $input->{ $_ } )) {
				$order->shipping->{$_} = $input->{$_}
			}
		}
		if (defined( $input->{shippingCostActual} )) {
			$order->shipping->{shippingCostActual} += $input->{shippingCostActual};
		}
		if (defined $input->{tracking_number} && $order_complete) {
			$order->shipping->{tracking_number} ||= $input->{tracking_number};
		}
		if ($invoice) {
			$invoice->shipping( ECS::Order::Invoice::Shipping->new({
				DBH => $DBH,
				DBHr => $DBHr,
				data => {
					shipping_cost => $order_complete ? $order->shipping->{shipping_cost} : '0.00',
					tracking_number => $input->{tracking_number},
					shippingCostActual => $input->{shippingCostActual},
					maybe courier => $pr_courier,
				}
			}));
		}
	}
	
	# Update Order
	unless ($order->save) {
		$result->{'success'} = 0;
		$result->{'error'} = 'Failed to Update Order';
		return $result;
	}
	
	#shipped or DS Shipped
	if ($order->status =~ /Shipped$/) {
        # create breakdown of cart component records at time of sale
        ECS::Order::CartComponent::pushCartComponentShipped({
            'DBH' => $DBH,
            'orderID' => $order->orderID,
            'siteID'  => $order->site->siteID,
        });
        
		# filter item list (using invoiceItems) to get products shipped
		if ($invoice) {
			$invoice->total( $order->total );
			$invoice->subtotal( $order->subtotal );
			$invoice->sales_tax( $order->sales_tax );
			my $invoice_items = $order->invoiceItems;
			my $cart_contents = $order->cartComponents;
			my $new_invoice_items = [];
			my $new_cart_contents = [];
			my $order_subtotal_cents = 0;
			my $orig_subtotal_cents = $order->subtotal->$to_cents;
			if ($order_complete) {
				use integer;
				my $invoice_subtotal_cents = 0;
				my $invoice_salestax_cents = 0;
				my $invoice_total_cents    = 0;
				for my $invoice (@invoices) {
					$invoice_subtotal_cents += $invoice->subtotal->$to_cents;
					$invoice_total_cents += $invoice->total->$to_cents;
					$invoice_salestax_cents += $invoice->sales_tax->$to_cents;
				}
				my $remaining_subtotal = $order->subtotal->$to_cents - $invoice_subtotal_cents;
				my $remaining_total = $order->total->$to_cents - $invoice_total_cents;
				my $remaining_salestax = $order->sales_tax->$to_cents - $invoice_salestax_cents;
				$invoice->total( $remaining_total->$from_cents );
				$invoice->subtotal( $remaining_subtotal->$from_cents );
				$invoice->sales_tax( $remaining_salestax->$from_cents );
			}
			

			my %unit_prices;
			my %pr_invoice_items = %$pr_items;
			for my $item ($invoice_items->@*) {
				if (my $pr_qty = delete $pr_invoice_items{ $item->{product}->productID }) {
					$item->{qty} = $pr_qty;
					push $new_invoice_items->@*, $item;
					my $unitprice_cents = $item->{unitprice}->$to_cents;
					my $extprice_cents = $unitprice_cents * $pr_qty;
					$order_subtotal_cents += $extprice_cents;
					my $unitprice = $unitprice_cents->$from_cents;
					my $extprice = $extprice_cents->$from_cents;
					$unit_prices{ $item->{product}->productID } = $unitprice;

					$invoice->addItem( my $invoice_item = ECS::Order::Invoice::Item->new({
						DBH => $DBH,
						DBHr => $DBHr,
						data => {
							productID => scalar( $item->{product}->productID ),
							qty => $pr_qty,
							unitprice => $unitprice,
							extprice => $extprice,
						}
					}));
				}
			}
			my %pr_cart_items = %$pr_items;
			for my $cartitem ($cart_contents->@*) {
				next unless my $pr_item = delete $pr_cart_items{ $cartitem->productID };
				my $unitprice = $unit_prices{ $cartitem->productID } || "0.00";
				my $new_cartitem = (ref $cartitem)->new({
					%$cartitem,
					qty => $pr_item,
					unitprice => $unitprice,
				});
				push $new_cart_contents->@*, $new_cartitem;
			}
			my $invoice_subtotal_complete = $invoice->subtotal->$to_cents;
			my $invoice_subtotal = $order_subtotal_cents->$from_cents;
			if ($order_complete && $order_subtotal_cents != $invoice_subtotal_complete) { # need to adjust subtotal
				my $subtotal_diff = $order_subtotal_cents - $invoice_subtotal_complete;
				# find candidate product
				my $candidate_item;
				for my $invoice_item ($invoice->getItems) {
					next unless $invoice_item->extprice->$to_cents > $subtotal_diff;
					my $item_qty = $invoice_item->qty;
					if (( ($invoice_item->extprice->$to_cents % $item_qty) + ($subtotal_diff % $item_qty) ) == 0 ) { 
						# multiple of quantity, can update unitprice
						$candidate_item = $invoice_item;
						last;
					}
				}
				if ($candidate_item) {
					use integer;
					my $unit_price = $candidate_item->unitprice->$to_cents;
					my $ext_price = $candidate_item->extprice->$to_cents;
					my $diff = $subtotal_diff / $candidate_item->qty;
					$unit_price -= $diff;
					$ext_price = $unit_price * $candidate_item->qty;
					$candidate_item->unitprice( $unit_price->$from_cents );
					$candidate_item->extprice( $ext_price->$from_cents );
				} else { # have to adjust products
					my @sorted_items = sort { 
						$b->unitprice <=> $a->unitprice ||
						$b->qty       <=> $a->qty
					} $invoice->getItems;
					for my $item (@sorted_items) {
						next unless $item->unitprice->$to_cents > $subtotal_diff;
						my $unit_cents = $item->unitprice->$to_cents;
						my $new_unit_cents = $unit_cents - $subtotal_diff;
						my $new_item = (ref $item)->new( 
							%$item, 
							qty => 1,
							unitprice => $new_unit_cents->$from_cents,
							extprice  => $new_unit_cents->$from_cents,
						);
						$invoice->addItem( $new_item );
						$item->qty( $item->qty - 1);
						$item->extprice( ($item->unitprice->$to_cents * $item->qty)->$from_cents );
						last;
					}
				}
			}
		
			my $new_sales_tax = 0;
			if (!$order_complete && $order->sales_tax > 0) {
				# work out the percentage of the order shipped on this invoice to attempt to guess tax
				use integer;
				my $sales_tax_cents = $order->sales_tax->$to_cents;
				my $percent = ( ($order_subtotal_cents * 100) / $orig_subtotal_cents );
				my $new_sales_tax_cents = ($sales_tax_cents * $percent ) / 100;
				$new_sales_tax = $new_sales_tax_cents->$from_cents;
			}
			if (!$order_complete) {
				$invoice->subtotal( $invoice_subtotal );
				$invoice->sales_tax( $new_sales_tax );
				my $order_fee = ($pr_count && $pr_count == 1 && $order->wiretransferfee) || 0;
				$invoice->total( $invoice->subtotal + $order_fee + $invoice->sales_tax + $invoice->shipping->{shipping_cost} );
			}
			$invoice->prID( $input->{prID} );
			$invoice->save;
			$order->_clear_invoices;
			$order->cartContents( $new_cart_contents );
			$order->invoiceItems( $new_invoice_items );

		}

        API::Order::Payment::invoice({
            'DBH' => $DBH,
            'order' => $order,
			'dropShip'	=> 1, 
			orderComplete => $order_complete,
			prCount => $pr_count,
			invoice => $invoice,
			prItems => +{ %$pr_items },
            'input' => {
                'user' => $input->{'user'},
                'ipaddress' => $input->{'ipaddress'},
                reqStatus => $p->{reqStatus},
            },
        });
       	

		local $order->{total} = $order->total;
		local $order->{subtotal} = $order->subtotal;
		local $order->{sales_tax} = $order->sales_tax;
		local $order->{wiretransferfee} = $order->wiretransferfee;
		local $order->shipping->{shipping_cost} = $order->shipping->{shipping_cost};
		my $packages;
		if ($invoice) {
			$order->{total} = $invoice->total,
			$order->{subtotal} = $invoice->subtotal,
			$order->{sales_tax} = $invoice->subtotal,
			$order->shipping->{shipping_cost} = $invoice->shipping->{shipping_cost};
			$order->{wiretransferfee} = 0 unless $pr_count == 1;
			$packages = [$invoice->tracking];
		}
		if ($input->{emailShipped} && ($order->status eq 'Shipped' || $order->status eq 'DS Partial Shipped')) {
			$p->{template} = $input->{template};
			$p->{orderID}  = $order->{orderID};
			$p->{order}    = $order;
			$p->{packages} = $packages if $packages;
			ECS::EMail::Order::send($p) if (defined $input->{'template'});
		}
		if ($order->status eq 'Shipped') { # must be shipped, not DS shipped here
			$order->enqueue_shopify_fulfillment;
            $order->enqueue_lifetime_replacement;
			$order->enqueue_wesupply_fulfillment;


		}

  }
	
	$result->{'data'}->{'orderStatus'} = $order->status;
	
	return $result;
}

# called when only virtual items need virtually delivered
# Requires
# - user
# - ipaddress
# - orderID
# Returns in 'data'
# - success
# - orderStatus
sub deliverOrder {
	my $self = shift;
	my $input = shift;
	
	my $result = {
		'success' => 1,
		'error' => undef,
		'data' => {},
		'update_DB'	=> $Config->{'update_DB'}
	};
	
	unless ($input->{'orderID'}) {
		$result->{'success'} = 0;
		$result->{'error'} = 'No orderID specified to Deliver';
		return $result;
	}
	
	return $result if (!$Config->{'update_DB'});
	
	my $DBH  = ECS::SQL::connect();
	my $DBHr = ECS::SQL::connect('SLAVE');
	
	my $order = ECS::Order->load({
		'DBH'     => $DBH,
		'orderID' => $input->{'orderID'}
	});
	
	unless ($order) {
		$result->{'success'} = 0;
		$result->{'error'} = 'Order details could not be retrieved';
		return $result;
	}

	local $ECS::Config::c{shipping} = $order->site->localConfig( q{shipping} );
	local $ECS::Config::c{taxID} = $order->site->localConfig( q{taxID} );
	my $site = $order->site;

	$order->scrub;
	
	my $p = {
		'DBH'   => $DBH,
		'DBHr'  => $DBHr,
		'order' => $order,
		'site'  => $site,
	};
	
	$p->{'reqStatus'} = $order->status;
	
	if (!API::Order::lock($p)) {
		$result->{'success'} = 0;
		$result->{'error'} = 'Failed to Lock Order '.$order->{'orderID'};
		return $result;
	}
	
	if (!API::Order::mmLock($p)) {
		$result->{'success'} = 0;
		$result->{'error'} = 'Failed to MM Transaction Lock Order '.$order->{'orderID'};
		return $result;
	}
	
	# User and IP Address to log with orderstatus records
	$order->user($input->{'user'});
	$order->ipaddress($input->{'ipaddress'});
	
	# Prepare the order to be MM Unlocked (when complete)
	$order->{'mmlock'} = 'NO';
	
	my $payment_success = $order->automatePayments('ship'); # Will update the order
	
	# If payment was successful, but was marked as Fraud
	#    - Send order to "Processing Error" status
	if ($order->reviewPaymentFraud) {
		
		$order->status('Processing Error');
		$order->addNote('Review Payment Fraud');
		
	} elsif ($payment_success) {
		
		ECS::Payment::GiftCard::deliverElectronicGiftCards($p);
		ECS::Payment::GiftCard::addPurchasedNote($p);
		
		$order->status('Shipped');
		$order->shipping->{'dtShipped'} = 'NOW()';
		
	} else {
		
		$order->status('Processing Error');
		$order->addNote('Payment FAILED');
		
	}
	
	# Update Order
	unless ($order->save) {
		$result->{'success'} = 0;
		$result->{'error'} = 'Failed to Update Order';
		return $result;
	}
	
	if ($order->status eq 'Shipped') {
        # create breakdown of cart component records at time of sale
        ECS::Order::CartComponent::pushCartComponentShipped({
            'DBH' => $DBH,
            'orderID' => $order->orderID,
            'siteID'  => $order->site->siteID,
        });
        
        API::Order::Payment::invoice({
            'DBH' => $DBH,
            'order' => $order,
            'site'  => $site,
            'input' => {
                'user' => $input->{'user'},
                'ipaddress' => $input->{'ipaddress'},
            },
        });
    }
	
	$result->{'data'}->{'orderStatus'} = $order->status;
	
	return $result;
}

sub getProductInfoById {
    my ( $p, $productID ) = @_;

    my $productClass = $p->{'site'}->classFor("Product");
    my %dbh = (
            'dbh'        => $p->{'site'}->estore_dbhr,
            'estore_dbh' => $p->{'site'}->estore_dbhr,
        );

    my @productObjects = ();
    push @productObjects, $productClass->new({
        %dbh,
        'productID' => $productID,
    });
    

    $productClass->loadMulti({
        %dbh,
        'products' => \@productObjects,
    });

    return \@productObjects;
}

# expects:
#   user                : Str
#   orderID             : Int
#   giftCardsToActivate : ArrayRef[Str]
sub validate {  
    my ( $self, $input ) = @_;

	my $result = {
		'success'   => 0,
		'error'     => undef,
		'data'      => {},
		'update_DB'	=> 1,
	};

    my $orderID   = $input->{ 'orderID'   } or croak "Missing orderID";
    my $user      = $input->{ 'user'      } or croak "Missing user";
    my $ipaddress = $input->{ 'ipaddress' } or croak "Missing ipaddress";

    my $giftCardsToActivate = $input->{ 'giftCardsToActivate' }
        or croak "Missing giftCardsToActivate";
    my $oneItemOrderType = $input->{ 'oneItemOrderType' }
        or croak "Missing oneItemOrderType";

    my $site = ECS::Site->fromName( "ECS" );

	my $order = ECS::Order->load( {
		'DBH'     => $site->estore_dbh,
		'orderID' => $orderID,
	} );
    $order->user( $user );
    $order->ipaddress( $ipaddress );
    $order->status( "LOCKED" );
    
    my $paymentSuccess = $order->automatePayments( "ship" );

    # intentionally NOT looking for payment failures here, we will
    # look for those after the product goes through the packing machine

    if ( @{ $giftCardsToActivate } ) {
        ECS::Payment::GiftCard::pendActivateGiftCardsOnOrder( {
            'DBH'                 => $site->estore_dbh,
            'order'               => $order,
            'site'                => $order->site,
            'giftCardsToActivate' => $giftCardsToActivate,
        } );
        ECS::Payment::GiftCard::activateGiftCardsOnOrder( {
            'DBH'                 => $site->estore_dbh,
            'order'               => $order,
            'site'                => $order->site,
            'giftCardsToActivate' => $giftCardsToActivate,
        } );
        ECS::Payment::GiftCard::addPurchasedNote( {
            'order'              => $order,
            'purchasedGiftCards' => $giftCardsToActivate,
        } );
    }

    if ( $oneItemOrderType =~ m/^(?:os)?oob$/ ) {
        my $firstItem = $order->cartComponents->[ 0 ];

        ECS::Product::Inventory::WIP::adjustWipQty( {
            'dbh'  => $site->estore_dbh,
            'info' => {
                'productID' => $firstItem->productID,
                'fromLoc'   => $oneItemOrderType,
                'toLoc'     => "onshelf",
                'qtyToMove' => 1,
            },
        } );
    }

    $order->status( "Validated" );
    $order->save;

    $result->{ 'success' } = 1;
    return $result;
}

# not an api method
sub nextPickedOneItemOrderMulti {
	my $p = shift;
	my $query = qq{
		SELECT 
			orders.orderID, 
			orders.qty, 
			count(*) AS totalItems,
			GROUP_CONCAT( DISTINCT CONCAT( brand.name, ';', COALESCE( cartComponentPicked.productID, cart.productID ) ) ) AS productsBrands
		 FROM (
			SELECT
				DISTINCT(cart.orderID) AS orderID,
				MAX( COALESCE( bc.brandID, brand.brandID )) AS brandID,
				SUM(cart.qty * COALESCE( cartComponentPicked.required, 1 )) AS qty
			FROM cart
				JOIN orders USING(orderID)
				JOIN product USING (productID)
				LEFT JOIN brand USING (brandID)
				LEFT JOIN productVendor USING (productID, vendorID)
				LEFT JOIN vendor USING (vendorID)
				JOIN cartComponentPicked USING (cartitemID)
				LEFT JOIN product pc ON (cartComponentPicked.productID = pc.productID)
				LEFT JOIN productVendor pvc ON (pvc.productID = cartComponentPicked.productID AND pvc.vendorID = cartComponentPicked.vendorID)
				LEFT JOIN (SELECT productID, barCode FROM productOem WHERE barCode IN (?,?)) pe ON (pe.productID = cartComponentPicked.productID)
				LEFT JOIN vendor vc ON (cartComponentPicked.vendorID = vc.vendorID)
				LEFT JOIN brand bc ON (pc.brandID = bc.brandID)
			WHERE orders.status = ? AND 
				CASE product.type
					WHEN 'Kit' THEN
						CASE COALESCE( pvc.optInMethod, vc.optInMethod, 'Stickers' )
							WHEN 'UPC' THEN
								(cartComponentPicked.productID = ? OR pc.UPCCode = ?)
							WHEN 'OEM' THEN
								(pe.barCode IN (?,?) OR pc.UPCCode = ? OR cartComponentPicked.productID = ? 
									OR (bc.genuine AND SUBSTRING_INDEX(pc.partnumber, '~', 1) = ?))
							WHEN 'UPC_OEM' THEN
								(pe.barCode IN (?,?) OR cartComponentPicked.productID = ? OR pc.UPCCode = ? 
									OR  (bc.genuine AND SUBSTRING_INDEX(pc.partnumber, '~', 1) = ?))
							ELSE
								(cartComponentPicked.productID = ?)
						END

					ELSE
						CASE COALESCE( productVendor.optInMethod, vendor.optInMethod, 'Stickers' )
							WHEN 'UPC' THEN
								(cart.productID = ? OR product.UPCCode = ?)
							WHEN 'OEM' THEN
								(pe.barCode IN (?,?) OR product.UPCCode = ? OR cart.productID = ? 
									OR (brand.genuine AND SUBSTRING_INDEX(cart.partnumber, '~', 1) = ?))
							WHEN 'UPC_OEM' THEN
								(pe.barCode IN (?,?) OR cart.productID = ? OR product.UPCCode = ? 
									OR  (brand.genuine AND SUBSTRING_INDEX(cart.partnumber, '~', 1) = ?))
							ELSE
								(cart.productID = ?)
						END

					END
			GROUP BY orderID HAVING qty = 1
		) AS orders
			JOIN cart USING(orderID)
			JOIN cartComponentPicked USING (cartitemID)
			JOIN orderShipping USING (orderID)
			LEFT JOIN shippingService USING (shippingServiceCode)
			LEFT JOIN brand USING (brandID)
		GROUP BY orders.orderID HAVING totalItems = 1
		ORDER BY orders.orderID ASC
	};

	my ($sth, $rd);
	
	my $pickedStatus = 'Picked '.uc($p->{'input'}->{'oneItemOrderType'});

	# fetch only the next $pickedStatus order that still has only one item
	$sth = $p->{'DBHr'}->prepare($query);
	die $p->{DBHr}->errstr unless $sth;
	$p->{input}{original_product} ||= $p->{input}{product};
	$sth->execute( 
		@{ $p->{input} }{qw/product original_product/}, 
		$pickedStatus, 
		(
			($p->{input}{product})x2, 
			@{ $p->{input} }{qw/product original_product/}, ($p->{input}{product})x3,
			@{ $p->{input} }{qw/product original_product/}, ($p->{input}{product})x4,
		)x2
	);
			
	$rd = $sth->fetchall_arrayref({});
	die $sth->err if $sth->err;

	return $rd;
	
}

1;
