Day24 - 將臺灣證券交易所的除權除息計算結果表存入 DB

13th鐵人賽

 2021/10/07

字數統計: 632閱讀時間: 3 min

前言

前面已經知道如何抓「臺灣證券交易所」的除權除息計算結果表 CSV 檔，接下來要處理資料，並存入 DB

說明

需要考量的情境，與前一篇是一樣的 (描述越來越精簡 XD)

實作

# app/features/twse/twt_49u/save_to_db.rb

module Twse::Twt49u
  class SaveToDb

    include Twse::Helpers

    def execute
      start_time = Time.current
      puts "#{self.class}, start_time: #{start_time.to_s}"

      latest_data_date = find_latest_data_date
      return puts "#{self.class}, 已經是最新的資料" if latest_data_date == start_time.to_date

      is_linux = `uname -a`[/Linux/].present?
      file_paths = Dir["data/twse/TWT49U/*"]
      file_paths.each do |file_path|
        rows = decode_data(file_path, is_linux)
        first_year, end_year = time_range(rows)
        row_index, end_index = rows_range(rows)
        all_rows, data_date_infos = filter_rows(rows, row_index, end_index)
        next if not_process?(data_date_infos, latest_data_date)

        filtered_stocks = filter_by_stocks(all_rows)
        Stock.import(filtered_stocks) if filtered_stocks.present?

        import_ex_stocks(all_rows)
      end
      puts "#{self.class}, done_time:#{Time.current}, #{(Time.current - start_time).to_s} sec"
    rescue StandardError => e
      puts "errors: #{e.inspect}, #{e.backtrace}"
    end

    private

    def find_latest_data_date
      ExStock.latest_data_date
    end

    def time_range(rows)
      first_year, first_month, first_day, end_year, end_month, end_day = rows[0].scan(/\d+/)
      [first_year, end_year].each_with_index do |year, index|
        year = "20" + (year.to_i + 11).to_s[1..2]
        if index.zero?
          first_year = year
        else
          end_year =year
        end
      end

      return [first_year, end_year]
    end

    def rows_range(rows)
      row_index = nil
      rows.each_with_index do |row, index|
        if row.include?("資料日期")
          row_index = index
          break
        end
      end

      end_index = nil
      rows.each_with_index do |row, index|
        if row.include?("公式")
          end_index = index
          break
        end
      end

      return [row_index, end_index]
    end

    def filter_rows(rows, row_index, end_index)
      all_rows = []
      data_date_infos = []
      rows[(row_index + 1)..(end_index - 1)].each do |row_string|
        row_item = []
        row_string.split('",').each { |row| row_item << row.gsub(/[=|,|"]/, '') }

        year, month, day = row_item[0].scan(/\d+/)
        year = "20" + (year.to_i + 11).to_s[1..2]
        data_date = year + month + day
        row_item[0] = data_date.to_date
        all_rows << row_item
        data_date_infos << row_item[0]
      end

      return [all_rows, data_date_infos]
    end

    def not_process?(data_date_infos, latest_data_date)
      data_date_infos.uniq!
      latest_data_date.present? && data_date_infos.max <= latest_data_date
    end

    def filter_by_stocks(all_rows)
      stock_infos = []
      all_rows.each { |rows| stock_infos << { code: rows[1], name: rows[2] } }
      stocks = Stock.all.select(:code).index_by(&:code)

      need_create_stocks = []
      stock_infos.each do |stock_info|
        stock = stocks[stock_info[:code]]
        next if stock
        next if need_create_stocks.any? { |item| item.code == stock_info[:code] }

        need_create_stocks << Stock.new(code: stock_info[:code], name: stock_info[:name])
      end
      need_create_stocks
    end

    def import_ex_stocks(all_rows)
      stocks = Stock.all.select(:code).index_by(&:code)

      need_create_ex_stocks = []
      all_rows.each do |row|
        stock = stocks[row[1]]

        need_create_ex_stocks << stock.exs.new(
          data_date: row[0],
          closing_price_before: row[3],
          reference_price: row[4],
          dr_value: row[5],
          dividend_right: ExStock::DIVIDEND_RIGHT[row[6]],
          limit_up: row[7],
          limit_down: row[8],
          opening_reference_price: row[9],
          ex_dividend_reference_price: row[10],
          reporting_day: row[12],
          price_book: row[13],
          eps: row[14],
        )
      end
      ExStock.import(need_create_ex_stocks) if need_create_ex_stocks.present?
    end

  end
end