# This script reads OpenStudio_standards.xlsx
# and creates a JSON file containing all the information

require 'json'
require 'rubyXL'
require 'csv'

class String

  def snake_case
    downcase.gsub(' ', '_').gsub('-', '_')
  end

end

# Convert OpenStudio_Standards.xlsx to a series
# of JSON files for easier consumption.
class Hash

  def sort_by_key_updated(recursive = false, &block)
    self.keys.sort(&block).reduce({}) do |seed, key|
      seed[key] = self[key]
      if recursive && seed[key].is_a?(Hash)
        seed[key] = seed[key].sort_by_key(true, &block)
      elsif recursive && seed[key].is_a?(Array) && seed[key][0].is_a?(Hash)
        # Sort by the set of unique properties
        uniq_props = unique_properties(key)
        if uniq_props.size > 0
          # Temporarily replace real values with placeholders for sorting
          nil_placeholder = 'zzzz'
          true_placeholder = 'TRUETRUETRUE'
          false_placeholder = 'FALSEFALSEFALSE'
          seed[key].each do |item|
            item.keys.each do |key2|
              if item[key2].nil?
                item[key2] = nil_placeholder
              elsif [true].include?(item[key2])
                item[key2] = true_placeholder
              elsif [false].include?(item[key2])
                item[key2] = false_placeholder
              elsif item[key2].is_a?(String) && /(\d|\.)+E\d/.match(item[key2])
                # Replace scientific notation strings with floats
                item[key2] = item[key2].to_f
              end
            end
          end

          # Sort
          seed[key] = seed[key].sort_by do |hsh|
            sort_order = []
            uniq_props.each do |prop|
              if hsh.has_key?(prop)
                sort_order << hsh[prop]
              end
            end
            sort_order
          end

          # Replace placeholders with real values
          seed[key].each do |item|
            item.keys.each do |key2|
              if item[key2] == nil_placeholder
                item[key2] = nil
              elsif item[key2] == true_placeholder
                item[key2] = true
              elsif item[key2] == false_placeholder
                item[key2] = false
              end
            end
          end
        else
          seed[key] = seed[key]
        end
      end
      seed
    end
  end

end

# Defines the set of properties that should be unique across all objects of the same type.
# This set is used for checking for duplicate objects and for sorting objects in the JSON files.
def unique_properties(sheet_name)
  return case sheet_name
         when 'templates', 'standards', 'climate_zone_sets', 'constructions', 'curves', 'fans'
           ['name']
         when 'prm_constructions'
           ['template', 'name']
         when 'prm_exterior_lighting'
           ['template']
         when 'materials'
           ['name', 'code_category']
         when 'space_types', 'space_types_lighting', 'space_types_rendering_color', 'space_types_ventilation', 'space_types_occupancy', 'space_types_infiltration', 'space_types_equipment', 'space_types_thermostats', 'space_types_swh', 'space_types_exhaust'
           ['template', 'building_type', 'space_type']
         when 'exterior_lighting'
           ['exterior_lighting_zone_number', 'template']
         when 'schedules'
           ['name', 'day_types', 'start_date', 'end_date']
         when 'construction_properties'
           ['template', 'climate_zone_set', 'operation_type', 'intended_surface_type', 'standards_construction_type', 'building_category', 'orientation', 'minimum_percent_of_surface', 'maximum_percent_of_surface']
         when 'boilers'
           ['template', 'fluid_type', 'fuel_type', 'condensing', 'condensing_control', 'minimum_capacity', 'maximum_capacity', 'start_date', 'end_date']
         when 'chillers'
           ['template', 'cooling_type', 'condenser_type', 'compressor_type', 'absorption_type', 'variable_speed_drive', 'minimum_capacity', 'maximum_capacity', 'start_date', 'end_date']
         when 'furnaces'
           ['template', 'minimum_capacity', 'maximum_capacity', 'start_date', 'end_date']
         when 'heat_rejection'
           ['template', 'equipment_type', 'fan_type', 'start_date', 'end_date']
         when 'water_source_heat_pumps'
           ['template', 'minimum_capacity', 'maximum_capacity', 'start_date', 'end_date']
         when 'water_source_heat_pumps_heating'
           ['template', 'minimum_capacity', 'maximum_capacity', 'start_date', 'end_date']
         when 'heat_pumps'
           ['template', 'cooling_type', 'heating_type', 'subcategory', 'minimum_capacity', 'maximum_capacity', 'start_date', 'end_date']
         when 'heat_pumps_heating'
           ['template', 'cooling_type', 'subcategory', 'minimum_capacity', 'maximum_capacity', 'start_date', 'end_date']
         when 'unitary_acs'
           ['template', 'cooling_type', 'heating_type', 'subcategory', 'minimum_capacity', 'maximum_capacity', 'start_date', 'end_date']
         when 'water_heaters'
           ['template', 'fuel_type', 'minimum_capacity', 'maximum_capacity', 'start_date', 'end_date']
         when 'elevators'
           ['template', 'building_type']
         when 'refrigeration_system_lineup', 'refrigeration_system'
           ['template', 'building_type', 'size_category', 'system_type']
         when 'refrigerated_cases'
           ['template', 'size_category', 'case_type', 'case_category']
         when 'refrigeration_condenser'
           ['template', 'building_type', 'system_type', 'size_category']
         when 'refrigeration_walkins'
           ['template', 'size_category', 'walkin_type']
         when 'refrigeration_compressors'
           ['template', 'compressor_name', 'compressor_type']
         when 'economizers'
           ['template', 'climate_zone', 'data_center']
         when 'prm_economizers'
           ['template', 'climate_ID']
         when 'motors'
           ['template', 'number_of_poles', 'type', 'synchronous_speed', 'minimum_capacity', 'maximum_capacity']
         when 'ground_temperatures'
           ['building_type', 'template', 'climate_zone']
         when 'hvac_inference'
           ['template', 'size_category', 'heating_source', 'cooling_source', 'delivery_type']
         when 'size_category'
           ['template', 'building_category', 'minimum_floors', 'maximum_floors', 'minimum_area', 'maximum_area']
         when 'construction_sets'
           ['template', 'building_type', 'space_type', 'is_residential']
         when 'parking', 'entryways'
           ['building_type']
         when 'prototype_inputs'
           ['template', 'building_type', 'hvac_system']
         when 'climate_zones'
           ['name', 'standard']
         when 'energy_recovery'
           ['template', 'climate_zone', 'under_8000_hours', 'nontransient_dwelling', 'enthalpy_recovery_ratio_design_conditions']
         when 'space_types_lighting_control'
           ['template', 'building_type', 'space_type']
         when 'prm_hvac_bldg_type'
           ['template', 'hvac_building_type']
         when 'prm_swh_bldg_type'
           ['template', 'swh_building_type']
         when 'prm_wwr_bldg_type'
           ['template', 'wwr_building_type']
         when 'prm_baseline_hvac'
           ['template', 'hvac_building_type', 'bldg_area_min', 'bldg_area_max', 'bldg_flrs_min', 'bldg_flrs_max']
         when 'prm_heat_type'
           ['template', 'hvac_building_type', 'climate_zone']
         when 'prm_interior_lighting'
           ['template', 'lpd_space_type']
         when 'lpd_space_type'
           ['template', 'lpd_space_type']
         else
           []
        end
end

# Shortens JSON file path names to avoid Windows build errors when
# compiling the OpenStudio CLI
def shorten_sheet_name(sheet_name)
  short_sheet_name = sheet_name
  short_sheet_name = short_sheet_name.gsub('refrigeration_system_lineup', 'ref_lnup') # 19 saved
  short_sheet_name = short_sheet_name.gsub('refrigerated_cases', 'ref_cases') # 9 saved
  short_sheet_name = short_sheet_name.gsub('exterior_lighting', 'ext_ltg') # 10 saved
  short_sheet_name = short_sheet_name.gsub('space_types', 'spc_typ') # 10 saved

  return short_sheet_name
end

# Determine the directory of the data based on the spreadsheet name
def standard_parent_directory_from_spreadsheet_title(spreadsheet_title)
  data_directory = spreadsheet_title.downcase.gsub('openstudio_standards-', '').gsub(/\(\w*\)/, '').split('-').first
  # puts "Extracting standard parent directory from spreadsheet title #{spreadsheet_title} = #{data_directory}"

  return data_directory
end

# Determine the directory name from the template
def standard_directory_name_from_template(template)
  directory_name = template.downcase.gsub(/\W/, '_').gsub('90_1', 'ashrae_90_1')
  # puts "Extracting standard directory from template #{template} = #{directory_name}"

  return directory_name
end

# checks whether your authorization credentials are set up to access the spreadsheets
# @return [Bool] returns true if api is working, false if not
def check_google_drive_configuration
  require 'google_drive'
  client_config_path = File.join(Dir.home, '.credentials', "client_secret.json")
  unless File.exists? client_config_path
    puts "Unable to locate client_secret.json file at #{client_config_path}."
    return false
  end
  puts 'attempting to access spreadsheets...'
  puts 'if you get an SSL error, disconnect from the VPN and try again'
  session = GoogleDrive::Session.from_config(client_config_path)

  # Gets list of remote files
  session.files.each do |file|
    puts file.title if file.title.include? 'OpenStudio'
  end

  puts 'Spreadsheets accessed successfully'
  return true
end

# Downloads the OpenStudio_Standards.xlsx from Google Drive
# @note This requires you to have a client_secret.json file saved in your
# username/.credentials folder.  To get one of these files, please contact
# marley.praprost@nrel.gov
def download_google_spreadsheets(spreadsheet_titles)
  require 'google_drive'
  client_config_path = File.join(Dir.home, '.credentials', "client_secret.json")
  unless File.exists? client_config_path
    puts "Unable to locate client_secret.json file at #{client_config_path}."
    return false
  end

  session = GoogleDrive::Session.from_config(client_config_path)

  # Gets list of remote files
  session.files.each do |file|
    if spreadsheet_titles.include?(file.title)
      puts "Found #{file.title}"
      file.export_as_file("#{File.dirname(__FILE__)}/#{file.title}.xlsx")
      puts "Downloaded #{file.title} to #{File.dirname(__FILE__)}/#{file.title}.xlsx"
    end
  end
  return true
end

def exclusion_list
  file_path = "#{__dir__}/exclude_list.csv"
  csv_file = CSV.read(file_path, headers:true)
  csv_data = csv_file.map {|row| row.to_hash}
  exclusion_array = { "os_stds" => { "worksheets" => [], "columns" => [] },
                      "data_lib" => { "worksheets" => [], "columns" => [] } }
  csv_file.each do |row|
    if row['columns'] == 'all'
      exclusion_array['os_stds']['worksheets'] << row['worksheet'] if row['exclude_from_os_stds'] == 'TRUE'
      exclusion_array['data_lib']['worksheets'] << row['worksheet'] if row['exclude_from_data_lib'] == 'TRUE'
    else
      exclusion_array['os_stds']['columns'] << row['columns'] if row['exclude_from_os_stds'] == 'TRUE'
      exclusion_array['data_lib']['columns'] << row['columns'] if row['exclude_from_data_lib'] == 'TRUE'
    end
  end
  return exclusion_array
end

def parse_units(unit)
  # useless_units = [nil, 'fraction', '%', 'COP_68F', 'COP_47F', '%/gal', 'Btu/hr/Btu/hr', 'Btu/hr/gal', 'BTU/hr/ft', 'W/BTU/h']
  units_to_skip = [nil, 'fraction', '%', 'EER', '>23m^2', '>84m^2', '<23m^2', '<84m^2']
  unit_parsed = nil
  if not units_to_skip.include?(unit)
    if unit == '%/gal'
      unit = '1/gal'
    end
    unit_parsed = OpenStudio.createUnit(unit)
    if unit_parsed.empty?
      unit_parsed = "Not recognized by OpenStudio"
    else
      unit_parsed = unit_parsed.get()
    end
  end
  return unit_parsed
end

# Exports spreadsheet data to data jsons, nested by the standards templates
#
# @param spreadsheet_titles
# @param dataset_type [String] valid choices are 'os_stds' or 'data_lib'
#   'os_stds' updates json files in openstudio standards, while 'data_lib' exports 90.1 jsons for the data library
def export_spreadsheet_to_json(spreadsheet_titles, dataset_type: 'os_stds')
  if dataset_type == 'data_lib'
    standards_dir = File.expand_path("#{__dir__}/../../data/standards/export")
    skip_list = exclusion_list['data_lib']
    skip_list['templates'] = ['DOE Ref Pre-1980',
                              'DOE Ref 1980-2004',
                              'NREL ZNE Ready 2017',
                              'ZE AEDG Multifamily',
                              'OEESC 2014',
                              'ICC IECC 2015',
                              'ECBC 2017',
                              '189.1-2009']
  else
    standards_dir = File.expand_path("#{__dir__}/../../lib/openstudio-standards/standards")
    skip_list = exclusion_list['os_stds']
    skip_list['templates'] = []
  end
  worksheets_to_skip = skip_list['worksheets']
  cols_to_skip = skip_list['columns']
  templates_to_skip = skip_list['templates']

  # List of columns that are boolean
  # (rubyXL returns 0 or 1, will translate to true/false)
  bool_cols = []
  bool_cols << 'hx'
  bool_cols << 'data_center'
  bool_cols << 'under_8000_hours'
  bool_cols << 'nontransient_dwelling'
  bool_cols << 'u_value_includes_interior_film_coefficient'
  bool_cols << 'u_value_includes_exterior_film_coefficient'

  warnings = []
  duplicate_data = []
  spreadsheet_titles.each do |spreadsheet_title|

    # Path to the xlsx file
    xlsx_path = "#{__dir__}/#{spreadsheet_title}.xlsx"

    unless File.exist?(xlsx_path)
      warnings << "could not find spreadsheet called #{spreadsheet_title}"
      next
    end

    puts "Parsing #{xlsx_path}"

    # Open workbook
    workbook = RubyXL::Parser.parse(xlsx_path)
    puts "After parse workbook"

    # Find all the template directories that match the search criteria embedded in the spreadsheet title
    dirs = spreadsheet_title.gsub('OpenStudio_Standards-', '').gsub(/\(\w*\)/, '').split('-')
    new_dirs = []
    dirs.each { |d| d == 'ALL' ? new_dirs << '*' : new_dirs << "*#{d}*" }
    glob_string = "#{standards_dir}/#{new_dirs.join('/')}"
    puts "--spreadsheet title embedded search criteria: #{glob_string} yields:"
#    template_dirs = Dir.glob(glob_string).select { |f| File.directory?(f) && !f.include?('data') && !f.include?('prm')}
    template_dirs = Dir.glob(glob_string).select { |f| File.directory?(f) && !f.include?('data')}
    template_dirs.each do |template_dir|
      puts "----#{template_dir}"
    end

    # Export each tab to a hash, where the key is the sheet name
    # and the value is an array of objects
    standards_data = {}
    list_of_sheets = []
    list_of_names = []
    list_of_units = []
    list_of_OS_okay_units = []
    workbook.worksheets.each do |worksheet|
      sheet_name = worksheet.sheet_name.snake_case

      # Skip the specified worksheets
      if worksheets_to_skip.include?(sheet_name)
        puts "Skipping #{sheet_name}"
        next
      else
        puts "Processing #{sheet_name}"
      end

      # All spreadsheets must have headers in row 3
      # and data from roworksheet 4 onward.
      header_row = 2 # Base 0

      # Get all data
      # extract_data was deprecated in rubyXL
      # inputting the method here https://github.com/weshatheleopard/rubyXL/issues/201
      all_data = worksheet.sheet_data.rows.map { |row|
        row.cells.map { |c| c && c.value() } unless row.nil?
      }

      # Get the header row data
      header_data = all_data[header_row]

      # Format the headers and parse out units (in parentheses)
      headers = []
      header_data.each do |header_string|
        break if header_string.nil?
        header = {}
        header["name"] = header_string.gsub(/\(.*\)/, '').strip.snake_case
        header_unit_parens = header_string.scan(/\(.*\)/)[0]
        list_of_sheets << sheet_name
        list_of_names << header_string.gsub(/\(.*\)/, '').strip.snake_case
        if header_unit_parens.nil?
          header["units"] = nil
          list_of_units << nil
          list_of_OS_okay_units << nil
        else
          header["units"] = header_unit_parens.gsub(/\(|\)/, '').strip
          list_of_units << header_unit_parens.gsub(/\(|\)/, '').strip
          list_of_OS_okay_units << parse_units(header_unit_parens.gsub(/\(|\)/, '').strip)
        end
        headers << header
      end
      puts "--found #{headers.size} columns"

      # Loop through all rows and export
      # data for the row to a hash.
      objs = []
      for i in (header_row + 1)..(all_data.size - 1)
        row = all_data[i]
        # Stop when reach a blank row
        break if row.nil?
        # puts "------row #{i} = #{row}"
        obj = {}

        # Check if all cells in the row are null
        all_null = true
        for j in 0..headers.size - 1
          # Flip the switch if a value is found
          unless row[j].nil?
            all_null = false
          end
        end

        # Skip recording empty rows
        next if all_null == true

        # Store values from appropriate columns
        for j in 0..headers.size - 1
          val = row[j]
          # Don't record nil values
          # next if val.nil?
          # Flip the switch if a value is found
          unless val.nil?
            all_null = false
          end
          # Skip specified columns
          next if cols_to_skip.include?(headers[j]['name'])
          # Convert specified columns to boolean
          if bool_cols.include?(headers[j]['name'])
            if val == 1
              val = true
            elsif val == 0
              val = false
            else
              val = nil
            end
          end
          # Convert date columns to standard format
          if headers[j]['name'].include?('_date')
            if val.is_a?(DateTime)
              val = val.to_s
            else
              begin
                val = DateTime.parse(val).to_s
              rescue ArgumentError, TypeError
                puts "ERROR - value '#{val}', class #{val.class} in #{sheet_name}, row #{i}, col #{j} is not a valid date"
                return false
              end
            end
          end

          # Record the value
          obj[headers[j]['name']] = val
          # Skip recording units for unitless values
          next if headers[j]['units'].nil?
          # Record the units
          # obj["#{headers[j]['name']}_units"] = headers[j]['units']
        end

        # Store the array of objects
        # special cases for some types
        if sheet_name == 'climate_zone_sets'
          new_obj = {}
          new_obj['name'] = obj['name']
          items = []
          obj.each do |key, val2|
            # Skip the key
            next if key == 'name'
            # Skip blank climate zone values
            next if val2.nil?
            items << val2
          end
          new_obj['climate_zones'] = items
          objs << new_obj
        elsif sheet_name == 'constructions' or sheet_name == 'prm_constructions'
          new_obj = {}
          new_obj['name'] = obj['name']
          items = []
          obj.each do |key, val2|
            # Skip the key
            next if key == 'name'
            # Put materials into an array,
            # record other fields normally
            if key.include?('material')
              # Skip blank material values
              next if val2.nil?
              items << val2
            else
              new_obj[key] = val2
            end
          end
          new_obj['materials'] = items
          objs << new_obj
        elsif sheet_name == 'schedules'
          new_obj = {}
          new_obj['name'] = obj['name']
          items = []
          obj.each do |key, val2|
            # Skip the key
            next if key == 'name'
            # Put materials into an array,
            # record other fields normally
            if key.include?('hr')
              # Skip blank hourly values
              next if val2.nil?
              items << val2
            else
              new_obj[key] = val2
            end
          end
          new_obj['values'] = items
          objs << new_obj
        else
          objs << obj
        end

      end

      # Report how many objects were found
      puts "--found #{objs.size} rows"

      # Skip to the next sheet if no objects were found
      if objs.size.zero?
        warnings << "did not export #{sheet_name} in #{spreadsheet_title} because no rows were found"
        next
      end

      # Save the objects to the hash
      standards_data[sheet_name] = objs
    end


    filename_out = spreadsheet_title.gsub(/[()]/, '')
    # CSV.open("metadata.csv", "wb") {|csv| headers.to_a.each {|elem| csv << elem} }
    # list_metadata = [list_of_sheets, list_of_names, list_of_units, list_of_OS_okay_units].transpose
    list_metadata = [list_of_sheets, list_of_names, list_of_units].transpose
    list_metadata.insert(0, ['Sheet', 'Name', 'Unit']) # [1, 2, 2.5, 3, 4]
    File.write("data/standards/metadata_units_#{filename_out}.csv", list_metadata.map(&:to_csv).join)
    # Check for duplicate data in space_types_* sheets
    standards_data.each_pair do |sheet_name, objs|
      skip_duplicate_check = []
      next if skip_duplicate_check.include?(sheet_name)

      # Defines the set of properties that should be unique across all objects of the same type
      unique_props = unique_properties(sheet_name)
      # Ensure that a set of properties was defined
      if unique_props.empty?
        puts "--ERROR no unique set of properties was defined for #{sheet_name}, cannot check for duplicates"
        return false
      end

      # Check for duplicates using unique property set
      puts "Checking #{sheet_name} for duplicates based on columns: #{unique_props.join(', ')}"
      found_objs = {}
      objs.each_with_index do |obj, i|
        unique_aspects = []
        unique_props.each { |prop| unique_aspects << obj[prop] }
        unique_aspect = unique_aspects.join('|')
        if found_objs.include?(unique_aspect)
          status = 'different'
          status = 'same' if obj.to_s == found_objs[unique_aspect]
          duplicate_data << [spreadsheet_title, sheet_name, i, status, unique_aspect]
        end
        found_objs[unique_aspect] = obj.to_s
      end
    end

    # Merge all space_types_* sheets into a single space_types sheet
    space_types = {}
    standards_data.each_pair do |sheet_name, objs|
      next unless sheet_name.include?('space_types_')
      puts "Merging #{sheet_name} into space_types"
      objs.each do |obj|
        name = "#{obj['template']}|#{obj['building_type']}|#{obj['space_type']}"
        if space_types[name].nil?
          space_types[name] = obj
        else
          space_types[name].merge!(obj)
        end
      end

      # Remove space_types_* from the standards_data
      standards_data.delete(sheet_name)
    end
    standards_data['space_types'] = space_types.values unless space_types.empty?

    # Export each set of objects to a JSON file
    standards_data.each_pair do |sheet_name, objs|
      puts "Writing #{sheet_name} which has #{objs.size} objects"

      # Record the number of files that were exported for error checking
      jsons_written = 0

      # Split template-specific datasets into separate files.
      # Store common/shared datasets under a higher-level folder
      parent_dir = standard_parent_directory_from_spreadsheet_title(spreadsheet_title)
      if objs.first.has_key?('template')
        puts '--template-specific, writing a file for each template with just that templates objects'

        # Split objects by template
        templates_to_objects = {}
        objs.each do |obj|
          temp = obj['template']
          if templates_to_objects[temp]
            templates_to_objects[temp] << obj
          else
            templates_to_objects[temp] = [obj]
          end
        end

        # Write out a file for each template with the objects for that template.
        templates_to_objects.each_pair do |template, objs|
          next if template == 'Any'
          next if templates_to_skip.include?(template)
          template_dir_name = standard_directory_name_from_template(template)
          # Sort the objects
          sorted_objs = {sheet_name => objs}.sort_by_key_updated(true) {|x, y| x.to_s <=> y.to_s}

          # Also check directly underneath the parent directory
          if /prm/.match(template_dir_name) && !/prm/.match(parent_dir)
            child_dir = "#{standards_dir}/#{parent_dir}_prm/#{template_dir_name}"
          else
            child_dir = "#{standards_dir}/#{parent_dir}/#{template_dir_name}"
          end
          additional_dirs = []
          additional_dirs << child_dir if Dir.exist?(child_dir)
          possible_template_dirs = template_dirs + additional_dirs
          puts "Additional dir = #{child_dir}"

          wrote_json = false
          possible_template_dirs.each do |template_dir|
            last_dir = template_dir.split('/').last
            next unless last_dir == template_dir_name
            data_dir = "#{template_dir}/data"
            Dir.mkdir(data_dir) unless Dir.exist?(data_dir)
            json_path = "#{data_dir}/#{template_dir_name}.#{shorten_sheet_name(sheet_name)}.json"
            if json_path.size > 256
              puts "--ERROR the JSON path is #{json_path.size - 256} characters longer than the Window 256 character limit, cannot write to #{json_path}"
              return false
            end
            File.open(json_path, 'w:UTF-8') do |file|
              file << JSON.pretty_generate(sorted_objs)
            end
            puts "--successfully generated #{json_path}"
            jsons_written += 1
            wrote_json = true
          end

          unless wrote_json
            warnings << "did not export data for template #{template} from #{sheet_name} in #{spreadsheet_title} because there was no valid directory matching '#{template_dir_name}' based on the spreadsheet title embedded search criteria"
          end
        end

        # Stop if no objects on this sheet list the template called 'Any'
        next if templates_to_objects['Any'].nil?

        # Sort the objects
        sorted_objs = {sheet_name => templates_to_objects['Any']}.sort_by_key_updated(true) {|x, y| x.to_s <=> y.to_s}

        # Write all objects that use the 'Any' template to the parent directories
        wrote_any_template_json = false
        template_dirs.uniq.each do |template_dir|
          data_dir = "#{template_dir}/data"
          Dir.mkdir(data_dir) unless Dir.exist?(data_dir)
          json_path = "#{data_dir}/Any.#{shorten_sheet_name(sheet_name)}.json"
          if json_path.size > 256
            puts "--ERROR the JSON path is #{json_path.size - 256} characters longer than the Window 256 character limit, cannot write to #{json_path}"
            return false
          end
          File.open(json_path, 'w:UTF-8') do |file|
            file << JSON.pretty_generate(sorted_objs)
          end
          puts "--successfully generated #{json_path}"
          jsons_written += 1
          wrote_any_template_json = true
        end

        unless wrote_any_template_json
          warnings << "did not export data for template 'Any' from #{sheet_name} in #{spreadsheet_title} because there was no valid directory to store 'Any' template data."
        end
      else
        puts '--not template-specific, writing a file with all objects for each template specified by search criteria'

        # Sort the objects
        sorted_objs = {sheet_name => objs}.sort_by_key_updated(true) {|x, y| x.to_s <=> y.to_s}

        # Write out a file for each template with all objects on the sheet
        template_dirs.each do |template_dir|
          data_dir = "#{template_dir}/data"
          Dir.mkdir(data_dir) unless Dir.exist?(data_dir)
          json_path = "#{data_dir}/#{parent_dir}.#{shorten_sheet_name(sheet_name)}.json"
          if json_path.size > 256
            puts "--ERROR the JSON path is #{json_path.size - 256} characters longer than the Window 256 character limit, cannot write to #{json_path}"
            return false
          end
          File.open(json_path, 'w:UTF-8') do |file|
            file << JSON.pretty_generate(sorted_objs)
          end
          puts "--successfully generated #{json_path}"
          jsons_written += 1
        end
      end

      # Confirm that at least 1 JSON file was written per sheet
      if jsons_written.zero?
        puts "--ERROR no JSON files were written for #{sheet_name} even though objects were found.  Check sheet contents or delete rows."
        # return false
      end
    end
  end

  # Print out all the warnings in one place
  puts "\n**** WARNINGS ****"
  warnings.each { |w| puts "WARNING #{w}" }

  # Write duplicates out to file
  if duplicate_data.size > 0
    duplicate_log_path = "#{__dir__}/openstudio_standards_duplicates_log.csv"
    puts "\n**** DUPLICATES ****"
    puts "There were #{duplicate_data.size} duplicate objects found.  See detail in: #{duplicate_log_path}"
    CSV.open(duplicate_log_path, 'wb') do |csv|
      csv << ['Spreadsheet Name', 'Worksheet Name', 'Object Index in Worksheet', 'Similarity of Duplicate', 'Duplicate Object']
      duplicate_data.each do |duplicate|
        csv << duplicate
      end
    end
    # Open the duplicates csv file
    if Gem.win_platform?
      system("start #{File.expand_path(duplicate_log_path)}")
    else
      system("open #{File.expand_path(duplicate_log_path)}")
    end
  end

  return true
end