# Utility which takes schedules from the NECB 2015 tables spreadsheet and puts them into a JSON file which somewhat
# matches the NECB 2011 schedules.json file.

require 'rubyXL'
require 'json'

class Sched_create
  # Set a bunch of variables which generally remain static and are added to the new schedulesNECB2015.json file because
  # that was how they were in the NECB2011 schedules.json file.

  template = 'NECB2015'
  name = 'NECB-'
  name_pre_mod = ['', '', '-Electric', '', '', '', '']
  name_post_mod = ['', '', '', '', '-Cooling', '-Heating', '']
  category = ['Occupancy', 'Lighting', 'Equipment', 'Fan', 'Thermostat Setpoint', 'Thermostat Setpoint', 'Service Water Heating']
  units = ['FRACTION', 'FRACTION', 'FRACTION', 'ON_OFF', 'TEMPERATURE', 'TEMPERATURE', 'FRACTION']
  day_types = ['Default|Wkdy', 'Sat', 'Sun|Hol']
  start_date = '2014-01-01T00:00:00+00:00'
  end_date = '2014-12-31T00:00:00+00:00'
  type = 'Hourly'
  notes = nil
  table_array = []
  entry_array = []
  json_array = []
  refs = ['assumption']

  # Open Excel file containing schdeules and look for the schedlues worksheet.

  workbook = RubyXL::Parser.parse('./NECB2015_tables-171121.xlsx')
  worksheet = workbook['A-8.4.3.2.(1)']

  # Determine how many rows are used in the schedules worksheet.  Had to do this because there were some nil lines
  # between each schedule in the worksheet.  This would cause the .each do command to break early.

  sheetlength = worksheet.dimension.ref.row_range.end
  table_index = 0

  # Start looping through each schedule in the worksheet.  RubyXL reads a few lines after the end of the schedules in
  # the worksheet.  The -32 is there to make sure that there is actually another schedule (or enough room for one) in
  # the worksheet thus preventing errors due to manipulating nulls.

  while table_index < (sheetlength - 32)
    curr_loc = 5
    cat_index = 0

    # Loop through each category defined above (order of categories should match how they are presented in the worksheet).

    category.each_with_index do |cat, cat_index|
      # Build the schedule name using the category and some prefixes and suffixes because the nomenclature was not
      # consistent.  Mostly trying to match what is in the NECB2011 schedules.json file.

      table_name = name + worksheet[table_index + 1][0].value.to_s[-1] + name_pre_mod[cat_index] + '-' + cat + name_post_mod[cat_index]

      # Loop through each day type: Weekdays/default, Saturdays, and Sundays/holidays.

      day_types.each do |day_type|
        line_values = []
        values = []

        # Need special if statements to handle Fans which are "On" and "Off" in the workbook but BTAP represents as 1 and 0.
        # Also need to handle when cooling is "off" in the workbook but which BTAP represents as setting the setpoint
        # really high.  The normal case (just a fraction) is at the end

        if cat == 'Fan'
          worksheet[table_index + curr_loc].cells.drop(1).each do |fan_val|
            if fan_val.value == 'On'
              line_values << 1.0
            else
              line_values << 0.0
            end
          end
        elsif name_post_mod[cat_index] == '-Cooling'
          worksheet[table_index + curr_loc].cells.drop(1).each do |cool_val|
            if cool_val.value == 'Off'
              line_values << 35
            else
              line_values << cool_val.value
            end
          end
        else
          worksheet[table_index + curr_loc].cells.drop(1).each { |col_ref| line_values << col_ref.value }
        end

        # The workbook lists the schedules started at 1:00 am and ending at 12:00 am (not sure why).  OpenStudio
        # schedules start at 12:00 am and end at 11:00 pm (which makes more sense to me).  I'm not sure if the to_f
        # statements need to be there but I added them anyway so all of the values in the .json file would be to at
        # least 1 decimal place (like the originial NECB2011 schedules.json file).

        values << line_values[23].to_f
        line_values.each { |ln_value| values << ln_value.to_f }
        values.pop

        # Put everything for this day in a hash (the order according to the original NECB2011 schedules.json file).

        entry_array = {
          'template' => template,
          'name' => table_name,
          'category' => cat,
          'units' => units[cat_index],
          'day_types' => day_type,
          'start_date' => start_date,
          'end_date' => end_date,
          'type' => type,
          'notes' => notes,
          'values' => values
        }

        # Add this day's hash to all of the others (I was calling them arrays but they were hashes).

        json_array << entry_array

        # Go to the next line for the next day.

        curr_loc += 1
      end

      # Go to the next line for the next category.

      curr_loc += 1
    end

    # End of the table set the location of the next table (which starts immediately after the preceding one)

    table_index += curr_loc - 1
  end

  # Add a few hashes to the hash containing all of the schedules in the worksheet.  This is to match the formatting
  # found in the original NECB2011 schedules.json file.

  table_array = {
    'tables' => [
      'name' => 'schedules',
      'data_type' => 'table',
      'refs' => refs,
      'table' => json_array
    ]
  }

  # Turn the big hash in memory into a .json format and save it to file.  The pretty part is so the json file is nicely
  # ordered and relatively easy to follow.

  File.open('./schedulesNECB2015.json', 'w') do |each_file|
    each_file.write(JSON.pretty_generate(table_array))
  end
end