module CommLogistics::Modules::PartialSql
  include CommLogistics::Const::Code
  include CommLogistics::Const::Error
  
  def make_customer_supplier_filter_condition(condition="")
    require "config/site_config.rb"
    invalid_flag=false
    filter_flag=false
    if $ENABLE_SUPPLIER_FILTER==true
      if !User.own_user_flag(session[:user_id])
        sids = SuppliersUser.vsuppliers(session[:user_id])
        if sids.blank?
          #なにもないので、無理矢理あり得ないwhereを追加する。
          #condition += ' AND b.invalid_flag_code!=b.invalid_flag_code'
          invalid_flag=true
        else
          condition += " AND s.supplier_id IN (#{sids.join(',')})"
          filter_flag=true
        end
      end
    end
    if $ENABLE_CUSTOMER_FILTER==true
      if !User.own_user_flag(session[:user_id])
        cids = CustomersUser.vcustomers(session[:user_id])
        if cids.blank?
          #なにもないので、無理矢理あり得ないwhereを追加する。
          #condition += ' AND b.invalid_flag_code!=b.invalid_flag_code'
          invalid_flag=true
        else
          condition += " AND s.customer_id IN (#{cids.join(',')})"
          filter_flag=true
        end
      end
    end
    
    if filter_flag==false && invalid_flag==true
      condition += ' AND s.invalid_flag_code!=s.invalid_flag_code'
    end
    
    return condition
  end
  
  def make_stock_sales_price_and_quantity(cols, params)
    detail_price, detail_quantity = make_sales_price_and_quantity_cols
    sql = "(SELECT #{cols}, #{detail_price}, #{detail_quantity} FROM sale_details as sd 
                       LEFT JOIN sales as s on s.id=sd.sale_id 
                       WHERE s.invalid_flag_code=#{MCODE_FLAG_OFF} 
                         AND sd.stock_flag_code=#{MCODE_FLAG_ON}
                         AND s.state_code=#{MCODE_STATUS2_COMP}
                         AND s.sale_type_code!=#{MCODE_SALE_TYPE_ADJUST}
                         AND s.target_date BETWEEN \"#{params[:start_target_date]}\" AND \"#{params[:end_target_date]}\"
                         #{params[:sales_condition].blank? ? '' : params[:sales_condition]}
                     GROUP BY #{cols} )"
    return sql
  end
  
  def make_sales_price_and_quantity_cols
    quantity_sql = "IFNULL(sd.quantity, 1)"
    detail_price = " SUM(CASE s.price_fraction_method_code 
                        WHEN #{MCODE_FRACTION_METHOD_FLOOR} THEN TRUNCATE(#{quantity_sql} * sd.price,0)
                        WHEN #{MCODE_FRACTION_METHOD_CEIL}  THEN TRUNCATE(IF(#{quantity_sql} * sd.price >= 0, #{quantity_sql} * sd.price + 0.9, #{quantity_sql} * sd.price - 0.9),0)
                        WHEN #{MCODE_FRACTION_METHOD_ROUND} THEN TRUNCATE(IF(#{quantity_sql} * sd.price >= 0, #{quantity_sql} * sd.price + 0.5, #{quantity_sql} * sd.price - 0.5),0)
                        ELSE 0 END
                       ) AS sale_total_price "
    detail_quantity = " SUM(IFNULL(sd.quantity,0)) AS sale_total_quantity "
    return detail_price, detail_quantity
  end
  
  def make_stock_diff(diff_date, diff_columns, diff_quantity=nil, add_group_conditions="", group_by=nil, target_conditions="")
    diff_quantity ||= "SUM(IFNULL(out_quantity,0)) - SUM(IFNULL(in_quantity,0)) AS diff_quantity,
                       SUM(IFNULL(out_a_quantity,0)) - SUM(IFNULL(in_a_quantity,0)) AS diff_a_quantity,
                       SUM(IFNULL(out_s_quantity,0)) - SUM(IFNULL(in_s_quantity,0)) AS diff_s_quantity"
    diff_quantity_cols = []
    diff_quantity.split(",\n").each do |dqc|
      tmp_col = dqc.split(" AS ")[1].strip
      diff_quantity_cols.push(" e." + tmp_col + " !=0 ")
    end
    state_conditions = " AND s.state_code=#{MCODE_STATUS2_COMP} "
    tmp_target = diff_date.instance_of?(Array) ? " s.target_date BETWEEN \"#{diff_date[0]}\" AND \"#{diff_date[1]}\" " : " s.target_date > \"#{diff_date}\" " 
    diff_conditions = " WHERE #{tmp_target} AND s.invalid_flag_code=#{MCODE_FLAG_OFF} AND sd.stock_flag_code=#{MCODE_FLAG_ON} " + target_conditions
    #取引パターンによって変化する。
    purchase_diff_columns = diff_columns.sub("supplier_id", " IF(purchase_type_code=#{MCODE_PURCHASE_TYPE_STORING},supplier_id,#{OWN_SUPPLIER_ID}) AS supplier_id ")
    disposal_diff_columns = diff_columns.sub("customer_id", " NULL AS customer_id ")
    own_diff_columns = diff_columns.sub("warehouse_id", " own_warehouse_id AS warehouse_id ").sub("customer_id", " NULL AS customer_id ")
    return "(SELECT * FROM ( 
                  SELECT #{diff_columns},
                      #{diff_quantity}
                      /*
                      SUM(IFNULL(in_quantity,0)) AS in_quantity, 
                      SUM(IFNULL(out_quantity,0)) AS out_quantity,
                      SUM(IFNULL(in_a_quantity,0)) AS in_a_quantity, 
                      SUM(IFNULL(out_a_quantity,0)) AS out_a_quantity, 
                      SUM(IFNULL(in_s_quantity,0)) AS in_s_quantity, 
                      SUM(IFNULL(out_s_quantity,0)) AS out_s_quantity
                      */
                    FROM
                    (SELECT
                      \'shippings\' AS table_name, sd.id AS detail_id, 
                      quantity AS in_quantity, 0 AS out_quantity, 
                      quantity AS in_a_quantity, 0 AS out_a_quantity,
                      IF(s.shipping_type_code=#{MCODE_SHIPPING_TYPE_SHORT}, quantity, 0) AS in_s_quantity, 0 AS out_s_quantity,
                      #{diff_columns.sub('location_number', '\'\' AS location_number')}
                      FROM shipping_details AS sd LEFT JOIN shippings AS s ON s.id=sd.shipping_id 
                      #{diff_conditions} AND s.shipping_state_code=#{MCODE_SHIPPING_STATE_COMP} 
                    UNION (SELECT 
                      \'restorations\' AS table_name, sd.id AS detail_id, 
                      0 AS in_quantity, quantity AS out_quantity, 
                      0 AS in_a_quantity, quantity AS out_a_quantity,
                      0 AS in_s_quantity, IF(sd.stock_type_code=#{MCODE_STOCK_TYPE_SHRT}, quantity, 0) AS out_s_quantity,
                      #{diff_columns.sub('location_number', '\'\' AS location_number')}
                      FROM restoration_details AS sd LEFT JOIN restorations AS s ON s.id=sd.restoration_id 
                      #{diff_conditions} #{state_conditions} )
                      
                    /*ライブスルー含まない。 プログラムに忠実に在庫主が自社のもののみ落とす*/
                    UNION (SELECT 
                      \'sales\' AS table_name, sd.id AS detail_id, 
                      0 AS in_quantity, quantity AS out_quantity, 
                      0 AS in_a_quantity, quantity AS out_a_quantity,
                      0 AS in_s_quantity, IF(sd.stock_type_code=#{MCODE_STOCK_TYPE_SHRT}, quantity, 0) AS out_s_quantity,
                      #{diff_columns.sub('location_number', '\'\' AS location_number')}
                      FROM sale_details AS sd LEFT JOIN sales AS s ON s.id=sd.sale_id 
                      #{diff_conditions} #{state_conditions} 
                      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, 
                      0 AS in_quantity, quantity AS out_quantity, 
                      0 AS in_a_quantity, quantity AS out_a_quantity,
                      0 AS in_s_quantity, IF(sd.stock_type_code=#{MCODE_STOCK_TYPE_SHRT}, quantity, 0) AS out_s_quantity,
                      #{diff_columns.sub('location_number', '\'\' AS location_number')}
                      FROM sample_details AS sd LEFT JOIN samples AS s ON s.id=sd.sample_id 
                      #{diff_conditions} #{state_conditions} AND sample_trigger_code!=#{MCODE_SAMPLE_TRIGGER_THROUGH} ) 
                    /*プログラムに忠実に自社発注のもののみ入庫する*/
                    UNION (SELECT 
                      \'purchases-in\' AS table_name, sd.id AS detail_id,
                      quantity AS in_quantity, 0 AS out_quantity, 
                      quantity AS in_a_quantity, 0 AS out_a_quantity,
                      IF(sd.stock_type_code=#{MCODE_STOCK_TYPE_SHRT}, quantity, 0) AS in_s_quantity, 0 AS out_s_quantity,
                      #{purchase_diff_columns}
                      FROM purchase_details AS sd LEFT JOIN purchases AS s ON s.id=sd.purchase_id 
                      #{diff_conditions} AND sd.inspect_status_code=#{MCODE_STATUS2_COMP} 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, 
                      0 AS in_quantity, quantity AS out_quantity, 
                      0 AS in_a_quantity, quantity AS out_a_quantity,
                      0 AS in_s_quantity, IF(sd.stock_type_code=#{MCODE_STOCK_TYPE_SHRT}, quantity, 0) AS out_s_quantity,
                      #{diff_columns}
                      FROM purchase_details AS sd LEFT JOIN purchases AS s ON s.id=sd.purchase_id 
                      #{diff_conditions} AND sd.inspect_status_code=#{MCODE_STATUS2_COMP} 
                      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, 
                      0 AS in_quantity, quantity AS out_quantity, 
                      0 AS in_a_quantity, quantity AS out_a_quantity,
                      0 AS in_s_quantity, 0 AS out_s_quantity,
                      #{disposal_diff_columns}
                      FROM disposal_details AS sd LEFT JOIN disposals AS s ON s.id=sd.disposal_id 
                      #{diff_conditions} #{state_conditions} ) 
                    UNION (SELECT 
                      \'adjustments\' AS table_name, sd.id AS detail_id, 
                      quantity AS in_quantity, 0 AS out_quantity, 
                      quantity AS in_a_quantity, 0 AS out_a_quantity,
                      IF(sd.stock_type_code=#{MCODE_STOCK_TYPE_SHRT}, quantity, 0) AS in_s_quantity, 0 AS out_s_quantity,
                      #{diff_columns}
                      FROM adjustment_details AS sd LEFT JOIN adjustments AS s ON s.id=sd.adjustment_id 
                      #{diff_conditions} #{state_conditions} ) 
                    UNION (SELECT 
                      \'shippings-own\' AS table_name, sd.id AS detail_id, 
                      0 AS in_quantity, quantity AS out_quantity, 
                      0 AS in_a_quantity, quantity AS out_a_quantity,
                      0 AS in_s_quantity, 0 AS out_s_quantity,
                      #{own_diff_columns}
                      FROM shipping_details AS sd LEFT JOIN shippings AS s ON s.id=sd.shipping_id 
                      #{diff_conditions} AND s.shipping_state_code=#{MCODE_SHIPPING_STATE_COMP} AND own_warehouse_id IS NOT NULL)
                    UNION (SELECT 
                      \'restorations-own\' AS table_name, sd.id AS detail_id, 
                      quantity AS in_quantity, 0 AS out_quantity, 
                      quantity AS in_a_quantity, 0 AS out_a_quantity,
                      0 AS in_s_quantity, 0 AS out_s_quantity,
                      #{own_diff_columns}
                      FROM restoration_details AS sd LEFT JOIN restorations AS s ON s.id=sd.restoration_id 
                      #{diff_conditions} #{state_conditions} AND own_warehouse_id IS NOT NULL)
                    UNION (SELECT 
                      \'shippings-own-avl\' AS table_name, sd.id AS detail_id, 
                      0 AS in_quantity, 0 AS out_quantity, 
                      0 AS in_a_quantity, quantity AS out_a_quantity,
                      0 AS in_s_quantity, 0 AS out_s_quantity,
                      #{own_diff_columns}
                      FROM shipping_details AS sd LEFT JOIN shippings AS s ON s.id=sd.shipping_id 
                      #{diff_conditions} AND s.shipping_state_code > #{MCODE_SHIPPING_STATE_BO} AND own_warehouse_id IS NOT NULL)
                     ) AS diff #{add_group_conditions} GROUP BY #{(group_by || diff_columns)}
                 ) AS e HAVING #{diff_quantity_cols.join(" OR ")})"
  end
  
  def make_non_inspect_quantity_from_stock_diff(stock_diff_sql, diff_columns) 
    unions = stock_diff_sql.split("UNION")
    tmp_purchase_in = ""
    tmp_purchase_out = ""
    unions.each do |split_sql|
      if    /purchases-in/  =~ split_sql
        tmp_purchase_in = split_sql
      elsif /purchases-out/ =~ split_sql
        tmp_purchase_out = split_sql
      end
    end
        #初めて入荷するロットの検品が未の場合、JOIN する相手がいないから数量0で一覧に出てくるようにしむける。 => 未 でもstocksテーブルに生成するように、mng_stock.rb purchase.rbを変更
#        quant_cols = ", existing_quantity, available_quantity, short_shipped_quantity"
#        extra_cols = ", validity_period, unit_code, product_category_id, product_set_id, sd.created_at, sd.updated_at"
#        stocks_body = "( SELECT * FROM 
#                         (SELECT id #{quant_cols} #{extra_cols} ,#{diff_columns} FROM stocks AS sd
#                          UNION (SELECT -1 * sd.id AS id #{quant_cols.gsub(',',', 0 AS ')} #{extra_cols}, #{tmp_purchase_in.split("out_s_quantity,")[1]}
#                          UNION (SELECT -1 * sd.id AS id #{quant_cols.gsub(',',', 0 AS ')} #{extra_cols}, #{tmp_purchase_out.split("out_s_quantity,")[1]}
#                         ) AS tmp_s GROUP BY #{diff_columns} 
#                      )"
    non_inspect_sql = " 
      SELECT #{diff_columns},
        SUM(IFNULL(in_quantity,0)) - SUM(IFNULL(out_quantity,0)) AS diff_quantity
      FROM (
        #{tmp_purchase_in}
        UNION #{tmp_purchase_out}
      ) AS tmp_p GROUP BY #{diff_columns}
    ".gsub('>','<=').gsub('sd.inspect_status_code','sd.inspect_status_code!')
    return non_inspect_sql
  end
end