module CommLogistics::Tools
  #
  #= 在庫数取得SQL
  #
  class ProductsStocksSearch < Comm::Tool::SqlSearch
    include CommLogistics::Const::Code
    #サーチ関数オーバーライド
    def search(params)
      return super(FLAG_ON, Stock, [], params)
    end
    
    def get_quantity(tab, join_lists, params, str_vals)
      #指定日の在庫数量
      quantity = "a.quantity - IFNULL(b.in_quantity,0) + IFNULL(b.out_quantity,0) AS quantity"
      #自社、貸出合計の数量
      in_quantity  = "sd.quantity AS in_quantity, 0 AS out_quantity"
      out_quantity = "0 AS in_quantity, sd.quantity AS out_quantity"
      #自社倉庫合計の数量
      in_own_quantity  = "sd.quantity AS in_quantity, 0 AS out_quantity"
      out_own_quantity = "0 AS in_quantity, sd.quantity AS out_quantity"
      #在庫数量と指定日ばでの差分
      stock_quantity = "SUM(existing_quantity) AS quantity"
      diff_quantity = " SUM(in_quantity) AS in_quantity, SUM(out_quantity) AS out_quantity"
      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 ""
    end
    
    def get_columns_and_tables(tab, join_lists, params, str_vals)
      @table_alias = {'stocks' => 'a'}
      quantity, in_quantity, out_quantity, in_own_quantity, out_own_quantity, stock_quantity, diff_quantity = get_quantity(tab, join_lists, params, str_vals)
      join = get_trans_join(tab, join_lists, params, str_vals)
      
      str_cols = " a.supplier_id AS supplier_id, a.product_id AS product_id, #{quantity} "
      #formからの条件を生成
      condition_date = "target_date > \"#{params[:target_date]}\" AND s.invalid_flag_code=#{MCODE_FLAG_OFF}"
      condition_state = "s.state_code=#{MCODE_STATUS2_COMP} "
      condition_products = " product_id IN (#{params[:product_ids]})"
      condition_target = condition_products + " AND s.supplier_id IN (#{params[:supplier_ids]}) "
      condition_stock_target = condition_products + " AND sd.supplier_id IN (#{params[:supplier_ids]}) "
      condition_purchase = condition_products + " AND ((purchase_type_code=#{MCODE_PURCHASE_TYPE_STORING} AND s.supplier_id IN (#{params[:supplier_ids]})) OR (purchase_type_code!=#{MCODE_PURCHASE_TYPE_STORING} AND #{OWN_SUPPLIER_ID} IN (#{params[:supplier_ids]}))) "
      str_tab =" FROM (
                        SELECT 
                          sd.supplier_id, product_id, #{stock_quantity}
                        FROM stocks as sd #{join}
                          WHERE #{condition_stock_target}
                          GROUP BY sd.supplier_id, sd.product_id
                      ) AS a 
                      LEFT JOIN ( 
                        SELECT 
                          supplier_id, product_id, #{diff_quantity}
                        FROM
                          (SELECT
                              \'shippings\' AS table_name, sd.id AS detail_id, s.supplier_id, sd.product_id,#{in_quantity}
                            FROM shipping_details AS sd LEFT JOIN shippings AS s ON s.id=sd.shipping_id #{join}
                            WHERE s.shipping_state_code=#{MCODE_SHIPPING_STATE_COMP} AND #{condition_date} AND #{condition_target}
                          UNION (SELECT 
                              \'restorations\' AS table_name, sd.id AS detail_id, s.supplier_id, sd.product_id,#{out_quantity}
                            FROM restoration_details AS sd LEFT JOIN restorations AS s ON s.id=sd.restoration_id #{join}
                            WHERE #{condition_state} AND #{condition_date} AND #{condition_target} )
                            
                          /*ライブスルー含まない。在庫主が自社のものだけ落とす*/
                          UNION (SELECT 
                              \'sales\' AS table_name, sd.id AS detail_id, s.supplier_id, sd.product_id,#{out_quantity}
                            FROM sale_details AS sd LEFT JOIN sales AS s ON s.id=sd.sale_id #{join}
                            WHERE #{condition_state} AND #{condition_date} AND #{condition_target} 
                            AND sale_type_code!=#{MCODE_SALE_TYPE_ADJUST} AND sale_trigger_code!=#{MCODE_SALE_TRIGGER_THROUGH} AND sd.stock_flag_code=#{MCODE_STOCK_FLAG_ON})
                          /*ライブスルー含まない。*/
                          UNION (SELECT 
                              \'samples\' AS table_name, sd.id AS detail_id, s.supplier_id, sd.product_id,#{out_quantity}
                            FROM sample_details AS sd LEFT JOIN samples AS s ON s.id=sd.sample_id #{join}
                            WHERE #{condition_state} AND #{condition_date} AND #{condition_target} AND sample_trigger_code!=#{MCODE_SAMPLE_TRIGGER_THROUGH}) 
                            
                          /*仕入は自社発注のときは、自社として仕入れる。 ライブスルーは含まない*/
                          UNION (SELECT 
                              \'purchases-in\' AS table_name, sd.id AS detail_id, IF(purchase_type_code=#{MCODE_PURCHASE_TYPE_STORING},s.supplier_id,#{OWN_SUPPLIER_ID}) AS supplier_id, sd.product_id,#{in_quantity}
                            FROM purchase_details AS sd LEFT JOIN purchases AS s ON s.id=sd.purchase_id #{join}
                            WHERE sd.inspect_status_code=#{MCODE_STATUS2_COMP} AND #{condition_date} AND #{condition_purchase} AND purchase_type_code!=#{MCODE_PURCHASE_TYPE_ADJUST} AND purchase_trigger_code IN (#{MCODE_PURCHASE_TRIGGER_ACCEPT_ORDER},#{MCODE_PURCHASE_TRIGGER_MANUFACTURE}) AND sd.stock_flag_code=#{MCODE_STOCK_FLAG_ON}) 
                            
                          /*自社受注と(自社発注の)貸出買取の場合は在庫が他社オーナーから自社オーナーに移るが、その後売り上げで落ちるはずなので、実際は落とすだけ。*/
                          UNION (SELECT 
                              \'purchases-out\' AS table_name, sd.id AS detail_id, s.supplier_id, sd.product_id,#{out_quantity}
                            FROM purchase_details AS sd LEFT JOIN purchases AS s ON s.id=sd.purchase_id #{join}
                            WHERE sd.inspect_status_code=#{MCODE_STATUS2_COMP} AND #{condition_date} AND #{condition_target} 
                              AND (s.purchase_trigger_code=#{MCODE_PURCHASE_TRIGGER_ACCEPT_ORDER} AND s.purchase_type_code=#{MCODE_PURCHASE_TYPE_LENDING} AND sd.stock_flag_code=#{MCODE_STOCK_FLAG_ON}) )
                            
                          UNION (SELECT 
                              \'disposals\' AS table_name, sd.id AS detail_id, s.supplier_id, sd.product_id,#{out_quantity}
                            FROM disposal_details AS sd LEFT JOIN disposals AS s ON s.id=sd.disposal_id #{join}
                            WHERE #{condition_state} AND #{condition_date} AND #{condition_target} ) 
                          UNION (SELECT 
                              \'adjustments\' AS table_name, sd.id AS detail_id, s.supplier_id, sd.product_id,#{in_quantity}
                            FROM adjustment_details AS sd LEFT JOIN adjustments AS s ON s.id=sd.adjustment_id #{join}
                            WHERE #{condition_state} AND #{condition_date} AND #{condition_target} ) 
                          UNION (SELECT 
                              \'shippings-own\' AS table_name, sd.id AS detail_id, s.supplier_id, sd.product_id,#{out_own_quantity}
                            FROM shipping_details AS sd LEFT JOIN shippings AS s ON s.id=sd.shipping_id #{join}
                            WHERE s.shipping_state_code=#{MCODE_SHIPPING_STATE_COMP} AND own_warehouse_id IS NOT NULL AND #{condition_date} AND #{condition_target} )
                          UNION (SELECT 
                              \'restorations-own\' AS table_name, sd.id AS detail_id, s.supplier_id, sd.product_id,#{in_own_quantity}
                            FROM restoration_details AS sd LEFT JOIN restorations AS s ON s.id=sd.restoration_id #{join}
                            WHERE #{condition_state} AND own_warehouse_id IS NOT NULL AND #{condition_date} AND #{condition_target} )
                          ) AS diff GROUP BY supplier_id, product_id
                      ) AS b ON a.supplier_id=b.supplier_id AND a.product_id=b.product_id
                    "
      return str_cols, str_tab, str_vals
    end
    #から実装
    def set_target_date_to_where(tab, params, str_where)
    end
  end  
end