#
#= 表示名一覧取得 コントローラー
# Authors:: Sumiyo Yamamoto
# Copyright:: Copyright (C) OrbusNeich Medical K.K.  2010.
#--
# date        name                   note
# 2010.10.29  Sumiyo Yamamoto        新規作成
#-------------------------------------------------------------------------------
#++
class DispNamesController < Comm::BaseController::DispName
  include CommLogistics::Const::Code
  include Comm::Const::MasterCode
  #include Comm::Const::DispNameLists

protected
  #== データ取得
  #-----------------------------------------------------------------#++
  def get_disp_names
    super
    case params[:mode]
    when 'product'
      product_search
    when 'price_product'
      price_product_search
    when 'lot_number'
      stock_search('lot_number')
      # serial_numberによって分割しているレコードのマージ
      if params[:no_merge]!=STR_TRUE
        tmp = []
        dist_keys = [
          'warehouse_id',
          'customer_id',
          'supplier_id',
          'product_id',
          'lot_number',
          'ubd',
          'stock_type_code'
        ]
        @disp_names.each do |r|
          idx = tmp.search_val(r, dist_keys)
          if idx
            tmp[idx]['existing_quantity']  += r['existing_quantity']
            tmp[idx]['available_quantity'] += r['available_quantity']
          else
            tmp.push(r)
          end
        end
        @disp_names = tmp
      end
    when 'serial_number'
      stock_search('serial_number')
    when 'customer'
      customer_search
    when 'supplier'
      supplier_search
    when 'receivings_charge'
      charge_search(Receivable)
    when 'payings_charge'
      charge_search(Payable)
    when 'customer_warehouse'
      customer_warehouse_search
    when 'inventory_date'
      inventory_date_search
    end
    return
  end

  #== データ取得：製品(→未使用)
  #-----------------------------------------------------------------#++
  def product_search
    require 'config/site_config'
    one_month = $MONTH_VALIDITY_PERIOD ? ' 30 * ' : ''
    #RESERVE(予備)は含めない。
    w_ids = Warehouse.set(MFIND_V).asign('own_flag_code', Comm::Const::MasterCode::MCODE_OWN_ON).all.extract('id')
    #w_ids = Warehouse.own_ids
    if params[:product_set_id].blank?
      p_ids = ProductSet.set(MFIND_V).all.extract('id')
    else
      p_ids = [params[:product_set_id]]
    end
  
    unless w_ids.blank?
      if params[:own_supplier_flag]==STR_TRUE 
        supplier_consition = ''
      else
        if params[:supplier_ids].blank?
          return []
        end
        supplier_condition = " AND (p.supplier_id IN (#{params[:supplier_ids]}) OR p.supplier_id IS NULL)" 
      end
      
      if params[:only_existing_product]==STR_TRUE
        only_existing_product_condition = " AND IFNULL(s.stock_quantity,0) > 0 "
      end
      @disp_names = Stock.find_by_sql(["select 
                                          p.id, 
                                          p.unit_code, 
                                          p.invalid_flag_code AS ivf, 
                                          p.product_name, 
                                          p.product_set_id, 
                                          p.disp_name, 
                                          IFNULL(s.stock_quantity,0)-IFNULL(ss.shipping_quantity,0) as quantity,
                                          IFNULL(s.short_stock_quantity,0)-IFNULL(ss.short_shipping_quantity,0) as short_quantity 
                                  from master_app_production.products as p  
                                  left join ( 
                                      select product_id,
                                             sum(existing_quantity) as stock_quantity,
                                             sum(IF(ubd < ADDDATE('#{params[:target_date]}', INTERVAL #{one_month} p.shipment_validity_period DAY) , existing_quantity, 0 )) AS short_stock_quantity
                                          from stocks as s
                                          left join master_app_production.products as p ON s.product_id=p.id
                                          where s.product_set_id IN (?) AND s.warehouse_id IN (?) AND s.existing_quantity > 0 group by s.product_id
                                  ) as s on p.id=s.product_id 
                                  left join (
                                      select 
                                        product_id, 
                                        sum(sd.quantity) as shipping_quantity,
                                        sum(IF(sd.ubd < ADDDATE('#{params[:target_date]}', INTERVAL #{one_month} p.shipment_validity_period DAY) , sd.quantity, 0 )) AS short_shipping_quantity 
                                      from shippings as s 
                                          right join shipping_details as sd on s.id=sd.shipping_id 
                                          left join master_app_production.products as p on sd.product_id=p.id
                                          where s.invalid_flag_code=? and shipping_state_code NOT IN (?) and sd.product_set_id IN (?) AND own_warehouse_id IN (?) group by sd.product_id
                                  ) as ss on p.id=ss.product_id where product_set_id IN (?) #{supplier_condition} #{only_existing_product_condition}",
                             p_ids, w_ids, MCODE_FLAG_OFF, [ MCODE_SHIPPING_STATE_WAIT, MCODE_SHIPPING_STATE_COMP, MCODE_SHIPPING_STATE_BO], p_ids, w_ids, p_ids]).only_hashfy
    end
    return
  end
  #== データ取得：製品（価格設定用）(→未使用)
  #-----------------------------------------------------------------#++
#  def price_product_search
#    id = params[:id]
#    if !(id.blank?) && (ProductSet.find(id).price_unique_code == MCODE_PRICE_UNIQUE_PRODUCT)
#      product_search
#    else
#      add_list(LIST_ALL)
#    end
#    return
#  end

  #== データ取得：在庫
  #-----------------------------------------------------------------#++
  def get_stock_cols(target_col = '')
    return [
      'stocks.warehouse_id',
      'stocks.customer_id',
      'stocks.supplier_id',
      'stocks.product_set_id',
      'stocks.product_id',
      'stocks.unit_code',
      'stocks.lot_number',
      'stocks.ubd',
      'stocks.validity_period',
      'stocks.serial_number',
      'stocks.existing_quantity',
      'stocks.available_quantity',
      'stocks.short_shipped_quantity'
    ]
  end
  def stock_search(target_col = '')
    
    cols = get_stock_cols(target_col)
    find_opt = {:order => 'stocks.product_set_id,stocks.product_id,stocks.ubd asc', :select => cols.join(',')}
    condition_body = []
    
    # lotやserialのセルからのサーチ(no_merge=false)では、not_blank追加。ロット自動入力やバーコード入力時では、no_merge=true
    unless params[:no_merge]==STR_TRUE
      condition_body.push("(#{target_col} IS NOT NULL AND #{target_col} != '')")
    end
    # 各パラメータを条件に追加する。_id系
    ['customer_id', 'warehouse_id', 'supplier_id', 'product_set_id', 'product_id'].each{|col|
      col_sym = col.to_sym
      unless params[col_sym].blank?
        condition_body.push("(stocks.#{col} IN (#{params[col_sym]}) )")
      end
    }
    # 各パラメータを条件に追加する。非_id系/lotとubdは複数のパラメータを想定しない。
    ['lot_number', 'ubd'].each{|col|
      col_sym = col.to_sym
      unless params[col_sym].blank?
        condition_body.push("(stocks.#{col} = '#{params[col_sym]}' )")
      end
    }
    #在庫数量が有るものを出すかどうかの条件
    if params[:has_quantity] == STR_TRUE
      condition_body.push("(existing_quantity > 0)");
    end
    # 出荷可能な分だけ
    if params[:only_shippable] == STR_TRUE && params[:target_date]
      require 'config/site_config'
      one_month = $MONTH_VALIDITY_PERIOD ? ' 30 * ' : ''
      find_opt[:joins]='INNER JOIN master_app_production.products ON products.id=stocks.product_id'
      condition_body.push("(stocks.ubd >= ADDDATE('#{params[:target_date]}', INTERVAL #{one_month} IFNULL(products.shipment_validity_period,0) DAY))");
    end
    
    #findのパラメータを完成させる。
    if condition_body.length > 0
      find_opt[:conditions] = [condition_body.join(" AND ")]
    end
    
    hrs = Stock.all(find_opt).only_hashfy
    
=begin
    # 検索条件設定
    cols = get_stock_cols(target_col)
    if params[:no_merge]==STR_TRUE
      scope = Stock.selekt(cols)
    else
      scope = Stock.selekt(cols).not_blank(target_col)
    end
    # カラムの値指定による条件設定
    unless params[:customer_id].blank?
      scope = scope.asign('stocks.customer_id', params[:customer_id])
    end
    unless params[:warehouse_id].blank?
      scope = scope.asign('stocks.warehouse_id', params[:warehouse_id])
    end
    unless params[:supplier_id].blank?
      scope = scope.asign('stocks.supplier_id', params[:supplier_id])
    end
    unless params[:product_set_id].blank?
      scope = scope.asign('stocks.product_set_id', params[:product_set_id])
    end
    unless params[:product_id].blank?
      scope = scope.asign('stocks.product_id', params[:product_id])
    end
    unless params[:lot_number].blank?
      scope = scope.asign('stocks.lot_number', params[:lot_number])
    end
    unless params[:ubd].blank?
      scope = scope.asign('stocks.ubd', params[:ubd])
    end
    if params[:has_quantity] == STR_TRUE
      scope = scope.has_quantity
    end
    # 出荷可能な分だけ
    if params[:only_shippable] == STR_TRUE && params[:target_date]
      require 'config/site_config'
      one_month = $MONTH_VALIDITY_PERIOD ? ' 30 * ' : ''
      hrs = scope.all({:joins=>'INNER JOIN master_app_production.products ON products.id=stocks.product_id',:conditions=>["stocks.ubd >= ADDDATE('#{params[:target_date]}', INTERVAL #{one_month} IFNULL(products.shipment_validity_period,0) DAY)"], :order => 'stocks.product_set_id,stocks.product_id,stocks.ubd asc'}).only_hashfy
    else
      hrs = scope.all({:order => 'stocks.product_set_id,stocks.product_id,stocks.ubd asc'}).only_hashfy
    end
=end
    
    # 編集 #warehouse_idは常に入っているという前提で。。。
    unless params[:warehouse_id].blank?
      # 自社倉庫
      if Warehouse.is_own?(params[:warehouse_id].to_i)
        hrs.each do |hr|
          hr['stock_type_code'] = MCODE_STOCK_TYPE_OWN
          @disp_names.push(hr)
        end
      # 他社倉庫
      else
        hrs.each do |hr|
          # 短期分あり
          if hr['short_shipped_quantity'] > 0
            # 短期分
            short = hr.deep_copy
            short['stock_type_code'] = MCODE_STOCK_TYPE_SHRT
            short['existing_quantity'] = short['short_shipped_quantity']
            @disp_names.push(short)
            # 長期分
            hr['existing_quantity'] -= hr['short_shipped_quantity']
            if hr['existing_quantity'] > 0
              hr['stock_type_code'] = MCODE_STOCK_TYPE_LONG
              @disp_names.push(hr)
            end
          # 長期分のみ
          else
            hr['stock_type_code'] = MCODE_STOCK_TYPE_LONG
            @disp_names.push(hr)
          end
        end
      end
    end
    #加工処理フローでは製品ごとの原価をとってくる。
    if params[:with_product_price]==STR_TRUE
      add_product_price
    end
    return
  end
  
  def add_product_price
    tmp_price = nil
    unless params[:product_id].blank?
      #仕入先が自社だった場合は、products_pricesをみる
      if params[:supplier_id].to_i == OWN_SUPPLIER_ID
        price = ProductsPrice.last_one(params[:product_id], params[:target_date], true)
        tmp_price = price.price unless price.blank?
      #仕入先が自社以外の場合は、製品情報の原価情報をみる
      else
        ps_ar = ProductSet.find(params[:product_set_id])
        unless ps_ar.blank?
          if ps_ar.price_unique_code==MCODE_PRICE_UNIQUE_PRODUCT
            p_ar = Product.find(params[:product_id])
            tmp_price = p_ar.cost_price unless p_ar.blank?
          else
            tmp_price = ps_ar.cost_price
          end
        end
      end
      #@disp_namesに原価を追加する。
      unless tmp_price.blank?
        @disp_names.each do |dn|
          dn['product_price']=tmp_price
        end
      end
    end
  end
  #== データ取得：得意先(→未使用)
  #-----------------------------------------------------------------#++
#  def customer_search
#    warehouses = Warehouse.dlist(@req_cols).all.only_hashfy
#    rels = WarehousesCustomer.selekt(['warehouse_id', 'customer_id']).all.only_hashfy
#    @disp_names = Customer.dlist(@req_cols).all.only_hashfy
#    @disp_names.each do |r|
#      ids = (rels.select{|x| x['customer_id'] == r['id'] }).extract('warehouse_id')
#      tmps = []
#      ids.each do |id|
#        ret = warehouses.get_by_search_val(id, 'id')
#        if ret
#          tmps.push(ret)
#        end
#      end
#      r['warehouses'] = tmps
#    end
#    return
#  end

  #== データ取得：仕入先
  #-----------------------------------------------------------------#++
#  def supplier_search
#    warehouse_id = params[:warehouse_id]
#    if warehouse_id.blank?
#      @col_name = 'supplier_id'
#      normal_search
#    else
#      # 仕入先ID取得
#      ids = InvoicePrice.selekt('supplier_id').sassign('warehouse_id', warehouse_id).sassign('customer_id', params[:customer_id]).product_is(params[:product_set_id], params[:product_id]).all.extract('supplier_id')
#      
#      # 自社追加
#      if params[:include_own]=='show' || params[:include_own]=='search'
#        ids.push(OWN_SUPPLIER_ID)
#      end
#      # 仕入先disp_name取得
#      @disp_names = Supplier.dlist(@req_cols).passign('id', ids).all.only_hashfy if ids.length > 0
#      # 自社倉庫の在庫情報を添付する。
#      if params[:include_own]=='search' && @disp_names.length > 0
#        w_ids = Warehouse.dlist.asign('own_flag_code', Comm::Const::MasterCode::MCODE_OWN_ON).all.extract('id')
#        ar = Stock.own.has_quantity.passign('warehouse_id', w_ids).sassign('product_id', params[:product_id]).all.only_hashfy if w_ids.length > 0
#        @disp_names.each do |r|
#          r['stock']=ar if r['id']==OWN_SUPPLIER_ID
#        end
#      end
#      
#    end
#    
#    return
#  end

  #== データ取得：売掛・買掛
  #-----------------------------------------------------------------#++
  def charge_search(cls = nil)
    unless params[:id].blank?
      cols = ['id', 'cutoff_date', 'total', 'total_price', 'total_duty', 'total_carry', 'total_carry_duty', 'payment_date']
      cols.push(params[:trade_with]);
      period = Date.today.years_ago(2)
      @disp_names = cls.selekt(cols).target_charge(params[:id], period).all.only_hashfy
      
      # 対象フラグ設定
      @disp_names.each do |r|
        r['target'] = false
      end
      
      if params[:trans_date]
        trans_arr = ParseDate::parsedate(params[:trans_date])
        @disp_names.each do |r|
          if !r['payment_date'].blank? && trans_arr[0] == r['payment_date'].year && trans_arr[1] == r['payment_date'].month
            r['target'] = true
          end
        end
      end
    end
    return
  end
  #==特定の得意先に紐付けされている納品先のみを抽出
  def customer_warehouse_search
    #, :conditions => [" own_flag_code IN (?) OR id IN (#{wids.join(',')})", [Comm::Const::MasterCode::MCODE_OWN_ON, Comm::Const::MasterCode::MCODE_OWN_RESERVE] ]
    cids = CustomersUser.vcustomers(session[:user_id])
    unless cids.blank?
      wids = WarehousesCustomer.find(:all, :select => "warehouse_id", :group => 'warehouse_id', :conditions => [" customer_id IN (#{cids.join(',')}) "]).map {|r| r.warehouse_id }
      unless wids.blank?
        select = (['id', 'disp_name', "(invalid_flag_code OR IF( ( own_flag_code IN (#{Comm::Const::MasterCode::MCODE_OWN_ON},#{Comm::Const::MasterCode::MCODE_OWN_RESERVE}) OR id IN (#{wids.join(',')})) , #{MCODE_FLAG_OFF},#{MCODE_FLAG_ON})) as ivf"] + @req_cols).join(',')
        @disp_names = Warehouse.find(:all, :select => select, :order => 'disp_sort').only_hashfy
        return 
      end
    end
    select = (['id', 'disp_name', "(invalid_flag_code OR IF( ( own_flag_code IN (#{Comm::Const::MasterCode::MCODE_OWN_ON},#{Comm::Const::MasterCode::MCODE_OWN_RESERVE}) ) , #{MCODE_FLAG_OFF},#{MCODE_FLAG_ON})) as ivf"] + @req_cols).join(',')
    @disp_names = Warehouse.find(:all, :select => select, :order => 'disp_sort').only_hashfy
    return
  end
  #== 棚卸原票
  def inventory_date_search
     dates = Inventory.inventory_dates
     @disp_names = []
     dates.each_with_index{|date,idx| @disp_names << {'id'=>idx, 'disp_name'=>date.to_s}}
     return
  end
end