class NonActiveListsController < CommLogistics::Base::Controller::ListController
  #仕様別不稼働
  def get_record_list
    
    @control_keys = 'non_active_lists'
    
    ss = NonActiveSearch.new
    #ss.post_where_sentence = ' GROUP BY a.warehouse_id, a.customer_id, a.supplier_id, d.spec1 '
    #ss.date_table = 'b'
    ss.table_alias = {'stocks' => 't',
                      'warehouses' => 't'}
    # sortテーブルの置き換えに使用
    join_mcls = [{:tab => 'warehouses',
                  :cols => Warehouse.column_names}]
    ars = ss.search(FLAG_ON, Stock, join_mcls, params)
    return ars
  end
  
  class NonActiveSearch < Comm::Tool::SqlSearch
    def get_columns_and_tables(tab, join_lists, params, str_vals)
      require 'config/site_config'
      str_cols = ' * '
      w_ids = Warehouse.own_ids
      unshippable = params[:with_unshippable]==STR_TRUE ? '' : " AND s.ubd >= ADDDATE(CURDATE(), INTERVAL #{($MONTH_VALIDITY_PERIOD ? ' 30 * ' : '')} IFNULL(p.shipment_validity_period,0) DAY) "
      str_tab =  " FROM ( SELECT 
                         a.warehouse_id,
                         a.customer_id,
                         a.supplier_id,
                         b.user_position_id,
                         b.sales_area_id,
                         a.product_category_id,
                         a.product_set_id,
                         a.product_id,
                         a.spec1,
                         a.existing_quantity AS existing_quantity,
                         e.target_date AS last_sales_date,
                         DATEDIFF(CURDATE(),e.target_date) AS sales_date_cnt,
                         f.target_date AS last_shippings_date,
                         DATEDIFF(CURDATE(),f.target_date) AS shippings_date_cnt
                      FROM ( SELECT warehouse_id,
                                 customer_id,
                                 s.supplier_id,
                                 product_category_id,
                                 s.product_set_id,
                                 s.product_id,
                                 spec1,
                                 SUM(existing_quantity) AS existing_quantity
                               FROM stocks AS s LEFT JOIN master_app_production.products AS p ON s.product_id=p.id
                               WHERE existing_quantity > 0 
                                 AND warehouse_id NOT IN (#{w_ids.join(',')}) 
                                 #{unshippable}
                                 AND s.product_category_id IN (#{params[:product_category_id]})
                               GROUP BY warehouse_id, customer_id, supplier_id, s.product_id
                             ) AS a LEFT JOIN master_app_production.warehouses AS b ON a.warehouse_id=b.id
                                    LEFT JOIN (SELECT 
                                                warehouse_id,
                                                customer_id,
                                                s.supplier_id,
                                                sd.product_set_id,
                                                p.spec1,
                                                MAX(target_date) AS target_date
                                              FROM sale_details as sd 
                                              LEFT JOIN sales as s on sd.sale_id=s.id 
                                              LEFT JOIN master_app_production.products as p on sd.product_id=p.id
                                                        WHERE s.invalid_flag_code=#{MCODE_FLAG_OFF}
                                                          AND state_code=#{MCODE_STATUS2_COMP}
                                                          AND sd.product_category_id IN (#{params[:product_category_id]})
                                                        GROUP BY warehouse_id, customer_id, supplier_id, sd.product_set_id, spec1
                                               ) AS e ON  a.warehouse_id=e.warehouse_id 
                                                      AND a.customer_id=e.customer_id
                                                      AND a.supplier_id=e.supplier_id 
                                                      AND a.product_set_id=e.product_set_id
                                                      AND a.spec1=e.spec1
                                    LEFT JOIN (SELECT 
                                                warehouse_id,
                                                customer_id,
                                                s.supplier_id,
                                                sd.product_set_id,
                                                p.spec1,
                                                MAX(target_date) AS target_date
                                              FROM shipping_details as sd 
                                              LEFT JOIN shippings as s on sd.shipping_id=s.id 
                                              LEFT JOIN master_app_production.products as p on sd.product_id=p.id
                                                        WHERE s.invalid_flag_code=#{MCODE_FLAG_OFF}
                                                          AND s.shipping_state_code=#{MCODE_SHIPPING_STATE_COMP}
                                                          AND s.shipping_type_code NOT IN (#{MCODE_SHIPPING_TYPE_EXCHANGE})
                                                          AND sd.product_category_id IN (#{params[:product_category_id]})
                                                        GROUP BY warehouse_id, customer_id, supplier_id, sd.product_set_id, spec1
                                               ) AS f ON  a.warehouse_id=f.warehouse_id
                                                      AND a.customer_id=f.customer_id
                                                      AND a.supplier_id=f.supplier_id 
                                                      AND a.product_set_id=f.product_set_id 
                                                      AND a.spec1=f.spec1
                       WHERE f.target_date <= '#{params[:start_target_date]}'
                       ) AS t "
      return str_cols, str_tab, str_vals
    end
    #空実装
    def set_target_date_to_where(tab, params, str_where)
    end
  end
end