module CommLogistics::Tools
  #
  #= 型番別在庫表取得
  #
  class ProductsStocks < CommLogistics::Tools::ProductsStocksSearch
    def get_quantity(tab, join_lists, params, str_vals)
      quantity, in_quantity, out_quantity, in_own_quantity, out_own_quantity, stock_quantity, diff_quantity = super(tab, join_lists, params, str_vals)
      #自社分を含めるかどうか
      if params[:with_own] then
        #自社倉庫のIDを集める
        w_ids = params[:own_warehouse_ids]
        #指定日の自社倉庫在庫数
        quantity += ",a.own_quantity - IFNULL(b.own_in_quantity,0) + IFNULL(b.own_out_quantity,0) AS own_quantity"
        #自社倉庫分の数量
        in_quantity  += ",IF(warehouse_id IN (#{w_ids}), sd.quantity, 0) AS own_in_quantity,0 AS own_out_quantity"
        out_quantity += ",0 AS own_in_quantity,IF(warehouse_id IN (#{w_ids}), sd.quantity, 0) AS own_out_quantity"
        #自社倉庫分の数量
        in_own_quantity  += ",IF(own_warehouse_id IN (#{w_ids}), sd.quantity, 0) AS own_in_quantity,0 AS own_out_quantity"
        out_own_quantity += ",0 AS own_in_quantity,IF(own_warehouse_id IN (#{w_ids}), sd.quantity, 0) AS own_out_quantity"
        #自社倉庫在庫数と、指定日までの差分
        stock_quantity += ",SUM(IF(warehouse_id IN (#{w_ids}), existing_quantity, 0)) AS own_quantity"
        diff_quantity  += ",SUM(own_in_quantity) AS own_in_quantity,SUM(own_out_quantity) AS own_out_quantity"
      end
      if params[:with_ubd] then
        (y,m,d) = params[:target_date].split(/-/)
        current_target_date = (Date.new(y.to_i, m.to_i, 1) >> 1).to_s
        previous_target_date = Date.new(y.to_i, m.to_i, 1).to_s
        quantity += ", a.ubd_quantity - IFNULL(b.ubd_in_quantity,0) + IFNULL(b.ubd_out_quantity,0) AS ubd_quantity
                     , a.ubd_pre_quantity - IFNULL(b.ubd_pre_in_quantity,0) + IFNULL(b.ubd_pre_out_quantity,0) AS ubd_expired
                     , a.ubd_ext_quantity - IFNULL(b.ubd_ext_in_quantity,0) + IFNULL(b.ubd_ext_out_quantity,0) AS ubd_ext_quantity"
        in_quantity  += ", IF(ubd < \"#{current_target_date }\", sd.quantity, 0) AS ubd_in_quantity, 0 AS ubd_out_quantity
                         , IF(ubd < \"#{previous_target_date}\", sd.quantity, 0) AS ubd_pre_in_quantity, 0 AS ubd_pre_out_quantity
                         , IF(ubd < \"#{current_target_date }\" AND pd.max_validity_period > sd.validity_period, sd.quantity, 0) AS ubd_ext_in_quantity, 0 AS ubd_ext_out_quantity"
        out_quantity += ", 0 AS ubd_in_quantity, IF(ubd < \"#{current_target_date }\", sd.quantity, 0) AS ubd_out_quantity
                         , 0 AS ubd_pre_in_quantity, IF(ubd < \"#{previous_target_date}\", sd.quantity, 0) AS ubd_pre_out_quantity
                         , 0 AS ubd_ext_in_quantity, IF(ubd < \"#{current_target_date }\" AND pd.max_validity_period > sd.validity_period, sd.quantity, 0) AS ubd_ext_out_quantity"
        in_own_quantity  += ", IF(ubd < \"#{current_target_date }\", sd.quantity, 0) AS ubd_in_quantity, 0 AS ubd_out_quantity
                         , IF(ubd < \"#{previous_target_date}\", sd.quantity, 0) AS ubd_pre_in_quantity, 0 AS ubd_pre_out_quantity
                         , IF(ubd < \"#{current_target_date }\" AND pd.max_validity_period > sd.validity_period, sd.quantity, 0) AS ubd_ext_in_quantity, 0 AS ubd_ext_out_quantity"
        out_own_quantity += ", 0 AS ubd_in_quantity, IF(ubd < \"#{current_target_date }\", sd.quantity, 0) AS ubd_out_quantity
                         , 0 AS ubd_pre_in_quantity, IF(ubd < \"#{previous_target_date}\", sd.quantity, 0) AS ubd_pre_out_quantity
                         , 0 AS ubd_ext_in_quantity, IF(ubd < \"#{current_target_date }\" AND pd.max_validity_period > sd.validity_period, sd.quantity, 0) AS ubd_ext_out_quantity"
        stock_quantity += ", SUM(IF(ubd < \"#{current_target_date }\", existing_quantity, 0)) AS ubd_quantity
                           , SUM(IF(ubd < \"#{previous_target_date}\", existing_quantity, 0)) AS ubd_pre_quantity
                           , SUM(IF(ubd < \"#{current_target_date }\" AND pd.max_validity_period > sd.validity_period, existing_quantity, 0)) AS ubd_ext_quantity"
        diff_quantity  += ", SUM(ubd_in_quantity) AS ubd_in_quantity, SUM(ubd_out_quantity) AS ubd_out_quantity
                           , SUM(ubd_pre_in_quantity) AS ubd_pre_in_quantity, SUM(ubd_pre_out_quantity) AS ubd_pre_out_quantity
                           , SUM(ubd_ext_in_quantity) AS ubd_ext_in_quantity, SUM(ubd_ext_out_quantity) AS ubd_ext_out_quantity"
        if params[:with_own]
          quantity += ", a.ubd_own_quantity - IFNULL(b.ubd_own_in_quantity,0) + IFNULL(b.ubd_own_out_quantity,0) AS ubd_own_quantity
                       , a.ubd_ext_own_quantity - IFNULL(b.ubd_ext_own_in_quantity,0) + IFNULL(b.ubd_ext_own_out_quantity,0) AS ubd_ext_own_quantity"
          in_quantity  += ", IF(ubd < \"#{current_target_date }\" AND warehouse_id IN (#{w_ids}), sd.quantity, 0) AS ubd_own_in_quantity, 0 AS ubd_own_out_quantity
                           , IF(ubd < \"#{current_target_date }\" AND pd.max_validity_period > sd.validity_period AND warehouse_id IN (#{w_ids}), sd.quantity, 0) AS ubd_ext_own_in_quantity, 0 AS ubd_ext_own_out_quantity"
          out_quantity += ", 0 AS ubd_own_in_quantity, IF(ubd < \"#{current_target_date }\" AND warehouse_id IN (#{w_ids}), sd.quantity, 0) AS ubd_own_out_quantity
                           , 0 AS ubd_ext_own_in_quantity, IF(ubd < \"#{current_target_date }\" AND pd.max_validity_period > sd.validity_period AND warehouse_id IN (#{w_ids}), sd.quantity, 0) AS ubd_ext_own_out_quantity"
          in_own_quantity  += ", IF(ubd < \"#{current_target_date }\" AND own_warehouse_id IN (#{w_ids}), sd.quantity, 0) AS ubd_own_in_quantity, 0 AS ubd_own_out_quantity
                           , IF(ubd < \"#{current_target_date }\" AND pd.max_validity_period > sd.validity_period AND own_warehouse_id IN (#{w_ids}), sd.quantity, 0) AS ubd_ext_own_in_quantity, 0 AS ubd_ext_own_out_quantity"
          out_own_quantity += ", 0 AS ubd_own_in_quantity, IF(ubd < \"#{current_target_date }\" AND own_warehouse_id IN (#{w_ids}), sd.quantity, 0) AS ubd_own_out_quantity
                           , 0 AS ubd_ext_own_in_quantity, IF(ubd < \"#{current_target_date }\" AND pd.max_validity_period > sd.validity_period AND own_warehouse_id IN (#{w_ids}), sd.quantity, 0) AS ubd_ext_own_out_quantity"
          stock_quantity += ", SUM(IF(ubd < \"#{current_target_date }\" AND warehouse_id IN (#{w_ids}), existing_quantity, 0)) AS ubd_own_quantity
                             , SUM(IF(ubd < \"#{current_target_date }\" AND pd.max_validity_period > sd.validity_period AND warehouse_id IN (#{w_ids}), existing_quantity, 0)) AS ubd_ext_own_quantity"
          diff_quantity  += ", SUM(ubd_own_in_quantity) AS ubd_own_in_quantity, SUM(ubd_own_out_quantity) AS ubd_own_out_quantity
                             , SUM(ubd_ext_own_in_quantity) AS ubd_ext_own_in_quantity, SUM(ubd_ext_own_out_quantity) AS ubd_ext_own_out_quantity"
        end
      end
      return quantity, in_quantity, out_quantity, in_own_quantity, out_own_quantity, stock_quantity, diff_quantity
    end
    
    def get_trans_join(tab, join_lists, params, str_vals)
      return " LEFT JOIN master_app_production.products AS pd ON pd.id=sd.product_id "
    end
  end
end