class StocksReservesListsController < CommLogistics::Base::Controller::ListController
  #
  # 予備在庫
  # 
  def get_record_list
    @control_keys = 'stocks_reserves_lists'
    
    ss = StocksReservesSearch.new
    #ss.date_table = 'b'
    ss.table_alias = {'stocks' => 't',
                      'accept_orders' => 't'}
    join_mcls = [{:tab => 'accept_orders',
                  :cols => AcceptOrder.column_names}]
    w_ids = Warehouse.reserve_ids
    if w_ids.blank?
      ars = []
    else
      ars = ss.search(FLAG_ON, Stock, join_mcls, params)
    end
    return ars
  end
  
  class StocksReservesSearch < Comm::Tool::SqlSearch
    def get_columns_and_tables(tab, join_lists, params, str_vals)
      str_cols = ' * '
      w_ids = Warehouse.reserve_ids
      str_tab =  " FROM ( SELECT
                     a.target_date,
                     a.user_id,
                     s.warehouse_id,
                     s.supplier_id,
                     s.product_category_id,
                     s.product_set_id,
                     s.product_id,
                     s.lot_number,
                     s.serial_number,
                     s.ubd,
                     s.existing_quantity,
                     a.id AS accept_order_id,
                     sr.diff_quantity
                   FROM (SELECT * FROM stocks WHERE warehouse_id IN (#{w_ids.join(',')}) AND existing_quantity>0) AS s 
                   /*予備在庫に移動した分*/
                   LEFT JOIN (
                     SELECT 
                       sr1.accept_order_id,
                       sr1.supplier_id,
                       sr1.warehouse_id,
                       sr1.product_id,
                       sr1.lot_number,
                       sr1.serial_number,
                       sr1.ubd,
                       IFNULL(sr1.quantity1,0) - IFNULL(sr2.quantity2,0) AS diff_quantity
                     FROM (
                        SELECT *, SUM(quantity) AS quantity1 FROM (
                            SELECT 
                                  \'shippings\' AS trans_type,
                                  accept_order_id,
                                  supplier_id,
                                  warehouse_id,
                                  product_id,
                                  lot_number,
                                  serial_number, 
                                  ubd,
                                  quantity
                                FROM shipping_details AS sd LEFT JOIN shippings AS s ON sd.shipping_id=s.id 
                                WHERE warehouse_id IN (#{w_ids.join(',')}) 
                                  AND accept_order_id IS NOT NULL 
                                  AND s.invalid_flag_code=#{MCODE_FLAG_OFF} 
                                  AND s.shipping_state_code=#{MCODE_SHIPPING_STATE_COMP}
                            UNION 
                              SELECT 
                                  \'restorations\' AS trans_type,
                                  accept_order_id, 
                                  supplier_id,
                                  own_warehouse_id AS warehouse_id,
                                  product_id, 
                                  lot_number, 
                                  serial_number, 
                                  ubd,
                                  quantity 
                                FROM restoration_details AS rd LEFT JOIN restorations AS r ON rd.restoration_id=r.id 
                                WHERE own_warehouse_id IN (#{w_ids.join(',')}) 
                                  AND accept_order_id IS NOT NULL
                                  AND r.invalid_flag_code=#{MCODE_FLAG_OFF} 
                                  AND r.state_code=#{MCODE_STATUS2_COMP}
                        ) AS tmp_sr1 GROUP BY trans_type, accept_order_id, supplier_id, warehouse_id, product_id, lot_number, serial_number, ubd
                     ) AS sr1 
                   /*予備在庫から予備でない自社在庫に移動した分*/
                     LEFT JOIN 
                        (SELECT *, SUM(quantity) AS quantity2 FROM (
                            SELECT 
                                  \'shippings\' AS trans_type,
                                  accept_order_id,
                                  supplier_id,
                                  own_warehouse_id AS warehouse_id,
                                  product_id,
                                  lot_number,
                                  serial_number, 
                                  ubd,
                                  quantity
                                FROM shipping_details AS sd LEFT JOIN shippings AS s ON sd.shipping_id=s.id 
                                WHERE own_warehouse_id IN (#{w_ids.join(',')}) 
                                  AND accept_order_id IS NOT NULL 
                                  AND s.invalid_flag_code=#{MCODE_FLAG_OFF} 
                                  AND s.shipping_state_code=#{MCODE_SHIPPING_STATE_COMP}
                            UNION 
                              SELECT 
                                  \'restorations\' AS trans_type,
                                  accept_order_id,
                                  supplier_id, 
                                  warehouse_id,
                                  product_id, 
                                  lot_number, 
                                  serial_number, 
                                  ubd,
                                  quantity 
                                FROM restoration_details AS rd LEFT JOIN restorations AS r ON rd.restoration_id=r.id 
                                WHERE warehouse_id IN (#{w_ids.join(',')}) 
                                  AND accept_order_id IS NOT NULL
                                  AND r.invalid_flag_code=#{MCODE_FLAG_OFF} 
                                  AND r.state_code=#{MCODE_STATUS2_COMP}
                        ) AS tmp_sr2 GROUP BY trans_type, accept_order_id, supplier_id, warehouse_id, product_id, lot_number, serial_number, ubd
                     ) AS sr2 ON sr1.accept_order_id=sr2.accept_order_id
                             AND sr1.supplier_id=sr2.supplier_id
                             AND sr1.warehouse_id=sr2.warehouse_id
                             AND sr1.product_id=sr2.product_id
                             AND sr1.lot_number=sr2.lot_number
                             AND sr1.serial_number=sr2.serial_number
                             AND sr1.ubd=sr2.ubd
                   ) AS sr ON s.supplier_id=sr.supplier_id
                          AND s.warehouse_id=sr.warehouse_id 
                          AND s.product_id=sr.product_id 
                          AND s.lot_number=sr.lot_number 
                          AND s.serial_number=sr.serial_number 
                          AND s.ubd=sr.ubd 
                   LEFT JOIN accept_orders AS a ON sr.accept_order_id=a.id 
                   WHERE sr.diff_quantity > 0 OR (sr.accept_order_id is NULL AND sr. s.existing_quantity > 0)
                 ) AS t "
      return str_cols, str_tab, str_vals
    end
  end
end