class TransSearchListsController < CommLogistics::Base::Controller::ListController
  def get_record_list
    @control_keys = 'trans_search_lists'
    
    ss = TransSearch.new
    #ss.post_where_sentence = ' GROUP BY b.user_position_id '
    #ss.date_table = 'b'
    ss.table_alias = {'stocks' => 'a'}
    # sortテーブルの置き換えに使用
    join_mcls = []
#    join_mcls = [{:tab => 'products',
#                  :cols => Product.column_names}]
    ars = ss.search(FLAG_ON, Stock, join_mcls, params)
    return ars
  end
  
  class TransSearch < Comm::Tool::SqlSearch
    #include CommLogistics::Const::Code
    def get_columns_and_tables(tab, join_lists, params, str_vals)
      str_cols = ' a.table_name,
                   CASE a.table_name 
                     WHEN "purchases" THEN "仕入処理" 
                     WHEN "sales" THEN "売上処理" 
                     WHEN "shippings" THEN "出荷処理"
                     WHEN "restorations" THEN "返却処理"
                     WHEN "samples" THEN "サンプル処理"
                     WHEN "disposals" THEN "廃棄処理"
                     WHEN "adjustments" THEN "在庫調整処理"
                   END AS table_name_dn,
                   a.id,
                   a.detail_id,
                   a.target_date,
                   a.shipping_state_code,  /*すべての状態をshipping_state_codeという名前で出力する*/
                   a.warehouse_id,
                   a.customer_id,
                   a.supplier_id,
                   a.sales_area_id,
                   a.user_position_id,
                   a.product_category_id,
                   a.product_set_id,
                   a.product_id,
                   a.lot_number,
                   a.serial_number,
                   a.ubd,
                   a.quantity,
                   a.abstract '
      base_columns = 'target_date, warehouse_id, customer_id, supplier_id, sales_area_id, user_position_id, product_category_id, product_set_id, product_id, lot_number, serial_number, ubd, quantity, m.disp_name AS abstract'
      diff_columns = 'warehouse_id, customer_id, supplier_id, product_id, lot_number, serial_number, ubd'
      #formからの条件を生成
      base_conditions = " WHERE target_date BETWEEN \"#{params[:start_target_date]}\" AND \"#{params[:end_target_date]}\" AND s.invalid_flag_code=#{MCODE_FLAG_OFF} "
      diff_conditions = " WHERE target_date > \"#{params[:end_target_date]}\" AND s.invalid_flag_code=#{MCODE_FLAG_OFF} "
      state_conditions = " AND s.state_code=#{MCODE_STATUS2_COMP} "
      
      conditions = sanitize_sql_for_conditions(params[:form_search], 'AND')
      search_conditions = ' AND ' + conditions unless conditions.blank?
      
      disposal_conditions = sanitize_sql_for_conditions(params[:form_search].reject{|key,val| key == 'customer_id'}, 'AND')
      disposal_search_conditions = ' AND ' + disposal_conditions unless disposal_conditions.blank?
      #own_conditions =  search_conditions.gsub('warehouse_id','own_warehouse_id') unless conditions.blank?
      
      str_tab =" FROM ( SELECT
                          \'shippings\' AS table_name, sd.id AS detail_id, sd.shipping_id AS id, s.shipping_state_code, #{base_columns}
                          FROM shipping_details AS sd LEFT JOIN shippings AS s ON s.id=sd.shipping_id 
                          LEFT JOIN master_app_production.code_masters AS m 
                             ON m.code_type_id=#{Comm::Const::DispName::CODE_MASTER_TYPE_INFO['shipping_type_code']} 
                             AND m.code_number=s.shipping_type_code
                          #{base_conditions} #{search_conditions}
                        UNION (SELECT 
                          \'restorations\' AS table_name, sd.id AS detail_id, sd.restoration_id AS id, state_code AS shipping_state_code, #{base_columns}
                          FROM restoration_details AS sd LEFT JOIN restorations AS s ON s.id=sd.restoration_id 
                          LEFT JOIN master_app_production.code_masters AS m 
                             ON m.code_type_id=#{Comm::Const::DispName::CODE_MASTER_TYPE_INFO['restoration_type_code']} 
                             AND m.code_number=s.restoration_type_code
                          #{base_conditions} #{search_conditions} )
                        UNION (SELECT 
                          \'sales\' AS table_name, sd.id AS detail_id, sd.sale_id AS id, state_code AS shipping_state_code, #{base_columns}
                          FROM sale_details AS sd LEFT JOIN sales AS s ON s.id=sd.sale_id 
                          LEFT JOIN master_app_production.code_masters AS m 
                             ON m.code_type_id=#{Comm::Const::DispName::CODE_MASTER_TYPE_INFO['sale_type_code']} 
                             AND m.code_number=s.sale_type_code
                          #{base_conditions} #{search_conditions} )
                        UNION (SELECT 
                          \'samples\' AS table_name, sd.id AS detail_id, sd.sample_id AS id, state_code AS shipping_state_code, #{base_columns}
                          FROM sample_details AS sd LEFT JOIN samples AS s ON s.id=sd.sample_id 
                          LEFT JOIN master_app_production.code_masters AS m 
                             ON m.code_type_id=#{Comm::Const::DispName::CODE_MASTER_TYPE_INFO['stock_type_code']} 
                             AND m.code_number=sd.stock_type_code
                          #{base_conditions} #{search_conditions} ) 
                        UNION (SELECT 
                          \'purchases\' AS table_name, sd.id AS detail_id, sd.purchase_id AS id, (s.state_code AND sd.inspect_status_code) AS shipping_state_code, #{base_columns}
                          FROM purchase_details AS sd LEFT JOIN purchases AS s ON s.id=sd.purchase_id 
                          LEFT JOIN master_app_production.code_masters AS m 
                             ON m.code_type_id=#{Comm::Const::DispName::CODE_MASTER_TYPE_INFO['purchase_type_code']} 
                             AND m.code_number=s.purchase_type_code
                          #{base_conditions} #{search_conditions} ) 
                        UNION (SELECT 
                          \'disposals\' AS table_name, sd.id AS detail_id, sd.disposal_id AS id, state_code AS shipping_state_code, target_date,warehouse_id, NULL AS customer_id, supplier_id, NULL AS sales_area_id, NULL AS user_position_id,product_category_id, product_set_id, product_id, lot_number, serial_number,ubd, quantity, '' AS abstract 
                          FROM disposal_details AS sd LEFT JOIN disposals AS s ON s.id=sd.disposal_id 
                          #{base_conditions} #{disposal_search_conditions} ) 
                        UNION (SELECT 
                          \'adjustments\' AS table_name, sd.id AS detail_id, sd.adjustment_id AS id,state_code AS shipping_state_code, #{base_columns}
                          FROM adjustment_details AS sd LEFT JOIN adjustments AS s ON s.id=sd.adjustment_id 
                          LEFT JOIN master_app_production.code_masters AS m 
                             ON m.code_type_id=#{Comm::Const::DispName::CODE_MASTER_TYPE_INFO['stock_type_code']} 
                             AND m.code_number=sd.stock_type_code
                          #{base_conditions} #{search_conditions} ) 
                          /*
                        UNION (SELECT 
                          \'accept_orders\' AS table_name, sd.id AS detail_id, sd.accept_order_id AS id,state_code AS shipping_state_code, #{base_columns}
                          FROM accept_order_details AS sd LEFT JOIN accept_orders AS s ON s.id=sd.accept_order_id 
                          LEFT JOIN master_app_production.code_masters AS m 
                             ON m.code_type_id=#{Comm::Const::DispName::CODE_MASTER_TYPE_INFO['accept_order_type_code']} 
                             AND m.code_number=s.accept_order_type_code
                          #{base_conditions} #{search_conditions} ) 
                        UNION (SELECT 
                          \'orders\' AS table_name, sd.id AS detail_id, sd.order_id AS id,state_code AS shipping_state_code, #{base_columns}
                          FROM order_details AS sd LEFT JOIN orders AS s ON s.id=sd.order_id 
                          LEFT JOIN master_app_production.code_masters AS m 
                             ON m.code_type_id=#{Comm::Const::DispName::CODE_MASTER_TYPE_INFO['order_type_code']} 
                             AND m.code_number=s.order_type_code
                          #{base_conditions} #{search_conditions} ) 
                          */
                      ) AS a "
      return str_cols, str_tab, str_vals
    end
    #から実装
    def set_target_date_to_where(tab, params, str_where)
    end
    def set_additional_where(str_where, params)
      unless params[:table_name].blank?
        add_str_where(str_where, " a.table_name IN (\"#{params[:table_name].gsub(' ', '","')}\")")
      end
    end
  end
end