migrate_standards.rb

require 'mongo'
require 'nokogiri'
require 'pg'

task 'qcloud:migrate:standards' do
  mongo_conn = Mongo::Connection.new.db("qcloud")
  pg_conn = PG.connect(dbname: "qcloud_dev", host: 'localhost')

  standards_collection = mongo_conn["standards"]
  sheets_collection = mongo_conn["sheets"]

  standards = standards_collection.find

  def xml_for_standard(standard)
    check_id = 1
    standard_builder = Nokogiri::XML::Builder.new do |xml|
      xml.standard {
        xml.check_groups {
          standard.fetch("check_groups", []).each do |check_group|
            xml.check_group(name: check_group["name"]) {
              check_group.fetch("checks", []).each do |check|
                xml.check(label: check["label"],
                          type: check["field_type"],
                          required: check["required"],
                          values: check["values"].join(","),
                          id: check_id)
                check_id += 1
              end
            }
          end
        }
      }
    end
    standard_builder.to_xml
  end

  def xml_for_sheet(sheet)
    check_id = 1
    checks = []
    sheet.fetch("check_groups", []).each do |check_group|
      checks.concat(check_group.fetch("checks", []))
    end

    sheet_builder = Nokogiri::XML::Builder.new do |xml|
      xml.sheet {
        xml.checks {
          checks.each do |check|
            xml.check(id: check_id) {
              xml.text(check["value"])
            }
            check_id += 1
          end
        }
      }
    end

    sheet_builder.to_xml
  end


  standards.each do |standard|

    xml = pg_conn.escape_string(xml_for_standard(standard))
    query = <<-SQL
    INSERT INTO standards( name
                         , company_name
                         , category_name
                         , company_mongo_id
                         , category_mongo_id
                         , state
                         , sheets_created
                         , sheets_count
                         , created_at
                         , updated_at
                         , document
                         , mongo_id )
    VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)
    SQL
    pg_conn.exec(query, [standard["standard_name"],
                         standard["company_name"],
                         standard["category_name"],
                         standard["company_id"],
                         standard["category_id"],
                         standard["state"],
                         standard["sheets_created"],
                         standard["sheets_count"],
                         standard["created_at"],
                         standard["updated_at"],
                         xml,
                         standard["_id"].to_s])

  end

  sheets = sheets_collection.find

  puts "Starting Sheet import"
  sheets.each_with_index do |sheet, idx|

    if idx % 100 == 0
      puts "Processing sheet #{idx}..."
    end

    xml = xml_for_sheet(sheet)

    query = <<-STUFF
    INSERT INTO sheets( created_at
                      , updated_at
                      , completed_at
                      , document
                      , mongo_id
                      , standard_id )
               VALUES ( $1, $2, $3, $4, $5,
                        (SELECT s.id
                         FROM standards s
                         WHERE s.mongo_id = $6
                         LIMIT 1))
    STUFF

    pg_conn.exec(query, [sheet["created_at"],
                         sheet["updated_at"],
                         sheet["completed_at"],
                         xml,
                         sheet["_id"].to_s,
                         sheet["standard_id"].to_s])

  end
end