require_dependency "lib/comm_logistics/list_controllers/sales_warehouses_lists_controller.rb"

class SalesWarehousesListsController < CommLogistics::Base::Controller::ListController
  def get_record_list
    @control_keys = 'sales_warehouses_lists'
    
    ss = SalesWarehousesSearch.new
    ss.table_alias = {'sale_details' => 'a',
                      'sales' => 'a',
                      'sales_users' => 'a'}
    ars = ss.search(FLAG_ON, SaleDetail, [], params)
    return ars
  end
  
  class SalesWarehousesSearch < Comm::Tool::SqlSearch
    include CommLogistics::Const::Code
    def get_columns_and_tables(tab, join_lists, params, str_vals)
      str_cols = ' * '
      str_tab =  " FROM ( SELECT b.target_date,
                                 b.operate_date,
                                 b.warehouse_id,
                                 b.customer_id,
                                 b.user_position_id,
                                 b.sales_area_id,
                                 b.supplier_id,
                                 b.global_area_id,
                                 a.*,
                                 b.sale_type_code,
                                 b.state_code,
                                 SUM(a.quantity) AS total_quantity,
                                 IF(e.price IS NULL, f.price, e.price) AS master_price,
                                 IF(e.old_price IS NULL, f.old_price, e.old_price) AS master_old_price
                           FROM sale_details AS a LEFT JOIN sales AS b ON a.sale_id=b.id 
                           LEFT JOIN (SELECT * FROM master_app_production.wholesale_prices WHERE product_id>0) AS e
                                      ON  b.warehouse_id=e.warehouse_id 
                                      AND b.supplier_id=e.supplier_id
                                      AND b.customer_id=e.customer_id
                                      AND a.product_id=e.product_id
                           LEFT JOIN (SELECT * FROM master_app_production.wholesale_prices WHERE product_id=#{LIST_ALL}) AS f
                                      ON  b.warehouse_id=f.warehouse_id 
                                      AND b.supplier_id=f.supplier_id
                                      AND b.customer_id=f.customer_id
                                      AND a.product_set_id=f.product_set_id
                           WHERE b.target_date between \'#{params[:start_target_date]}\' AND \'#{params[:end_target_date]}\'
                                 #{params[:trans_condition].blank? ? '' : params[:trans_condition]}
                                 AND b.state_code=#{MCODE_STATUS2_COMP}
                                 AND b.invalid_flag_code=#{MCODE_FLAG_OFF}
                           GROUP BY b.target_date, b.operate_date, b.warehouse_id, a.product_id, b.state_code, a.price ) AS a"
      return str_cols, str_tab, str_vals
    end
    def set_target_date_to_where(tab, params, str_where)
    end
  end
end