class StocksManufacturedListsController < CommLogistics::Base::Controller::ListController
  def get_record_list
    @control_keys = 'stocks_manufactured_lists'
    
    ss = StocksManufacturedSearch.new
    #ss.post_where_sentence = ' , p.product_id '
    #ss.date_table = 'b'
    ss.table_alias = {'stocks' => 't'}
    # sortテーブルの置き換えに使用
    join_mcls = []
#    join_mcls = [{:tab => 'products',
#                  :cols => Product.column_names}]
    ars = ss.search(FLAG_ON, Stock, join_mcls, params)
    return ars
  end
  
  class StocksManufacturedSearch < Comm::Tool::SqlSearch
    include CommLogistics::Const::Code
    def get_columns_and_tables(tab, join_lists, params, str_vals)
      str_cols = ' * '
      str_tab =  " FROM ( SELECT
                       s.supplier_id,
                       s.product_category_id,
                       s.product_set_id,
                       s.product_id,
                       s.ubd,
                       s.validity_period,
                       s.existing_quantity,
                       DATE_SUB(s.ubd,INTERVAL s.validity_period MONTH) AS manufactured_date,
                       out_quantity,
                       in_quantity
                     FROM ( SELECT
                            supplier_id,
                            product_category_id,
                            product_set_id,
                            product_id,
                            ubd,
                            validity_period,
                            SUM(existing_quantity) AS existing_quantity
                          FROM stocks
                          WHERE existing_quantity > 0
                          GROUP BY supplier_id, product_id, ubd ) AS s 
                   LEFT JOIN ( SELECT
                                #{OWN_SUPPLIER_ID} AS supplier_id,
                                product_category_id,
                                product_set_id,
                                product_id,
                                ubd,
                                validity_period,
                                SUM(IF(p.purchase_type_code=#{MCODE_PURCHASE_TYPE_EXTEND_UNLOAD}, quantity, 0)) AS  out_quantity,
                                SUM(IF(p.purchase_type_code=#{MCODE_PURCHASE_TYPE_EXTEND_LOAD}, quantity, 0)) AS in_quantity
                          FROM purchase_details AS pd LEFT JOIN purchases AS p ON pd.purchase_id=p.id
                          WHERE (p.purchase_type_code=#{MCODE_PURCHASE_TYPE_EXTEND_UNLOAD} || p.purchase_type_code=#{MCODE_PURCHASE_TYPE_EXTEND_LOAD})
                            AND p.invalid_flag_code=#{MCODE_FLAG_OFF}
                            AND p.state_code=#{MCODE_STATUS2_COMP}
                          GROUP BY supplier_id, product_id,ubd
                   ) AS pr ON s.supplier_id=pr.supplier_id AND s.product_id=pr.product_id AND s.ubd=pr.ubd
                 ) AS t "
      return str_cols, str_tab, str_vals
    end
  end
end