caseydentinger

select op.orders_id as picked
       from orders_products op
       join products_stock_adjustments psa on
         op.orders_id = psa.orders_id and
         op.products_id = psa.products_id and
         op.products_quantity = psa.adjustment_amount_physical * -1
       where op.orders_id = {$this->id()}

caseydentinger

select sum(op.products_quantity) as quantity
 from orders o
 join orders_products op on o.orders_id = op.orders_id
 join products p on op.products_id = p.products_id
 join parts pa on p.products_id = pa.products_id
 where o.orders_status in (1,5)
 and pa.parts_id = xxxx;

caseydentinger

      $q = 'select sum(op.products_quantity) as quantity
            from orders_products op
            join (
              select distinct orders_id
              from orders
              where orders_status in (1,5)
            ) o on o.orders_id = op.orders_id
            join (
              select distinct products_id
              from parts

caseydentinger

create table new_products_stock_adjustments (
  products_stock_adjustments_id int(12) auto_increment,
  products_id int(12) default 0,
  purchases_id int(12) default 0,
  assemblies_runs_id int(12) default 0,
  batch_id int(12) default 0,
  orders_id int(12) default 0,
  admin_users_id int(12) default 0,
  customers_id int(12) default 0,
  adjustment_amount int(12) default 0,

caseydentinger

select sql_no_cache o.*

from (select * from orders where orders_status = 1) o

  where (
    select count(*) from orders_products op
    join products p on op.products_id = p.products_id
    join parts pa on p.products_id = pa.products_id
    where op.products_quantity > (
      p.products_physical_quantity - ifnull(

caseydentinger

  public function getPickingArray ()
  {
    $dbi = SparkDBI::getInstance();

    $purchased = $dbi->fetchAllRows(
      'select products_id as id, products_quantity as qty
       from orders_products
       where orders_id = ' . $dbi->escape($this->id())
    );

caseydentinger

    public function qoaKeyName ()
    {
      return 'quantity_on_orders_' . $this->id();
    }

    /**
     * Quantity of this part's storefront product available for sale to the general public.
     */
    public function quantityForSale ()
    {

caseydentinger

create table stock_adjustments (

  -- for uniqueness
  id int(10) auto_increment,

  -- for chronological ordering, now with microseconds
  date timestamp(6),

  -- these will always refer to a product
  products_id int(10) default null,

caseydentinger

, (CAST(products.products_physical_quantity AS signed) - products.quantity_on_orders) AS virtual_quantity
, MAX(virtual_quantity, 0) AS inbox_quantity
, MAX(virtual_quantity * -1, 0) AS backorder_quantity
, products.products_physical_quantity AS physical_quantity

caseydentinger

    $params = ['status' => $this->req()->status,
               'value'  => $this->req()->value,
               'unit'   => $this->req()->unit];

\SparkLib\Fail::log($params);
    $query = "select o.*, sm.name as shipping from orders o
              join orders_status_history osh on osh.orders_status_history_id = ({$join_sub})
              join shipping_methods sm on o.shipping_method = sm.shipping_methods_id
              join payment_methods pm on o.payment_methods_id = pm.payment_methods_id
              where o.orders_status = :status