Parses CSV files with attendees list, checks against the db if they exists, and manually creates rows if doesn't

missing_attendees.rb

require "mysql2"
require 'csv'

class MissingAttendees
  CSV_FILE_NAME = "#{Rails.root}/db/emails.csv"

  def self.fix_list!
    MissingAttendees.new.fix_list
  end

  def fix_list
    puts "Starting fix_list task"
    @connection = get_connection

    csv_text = File.read CSV_FILE_NAME
    csv = CSV.parse(csv_text)

    @inserted_items = []
    puts "csv file parsed, got #{csv.count} items"
    csv.each do |row|
      puts "\t processing row #{row.inspect}"
      uid, email, name, event, ticket_type, created_at = row
      event_model = Event.find_by_title event.strip

      if uid.blank? || email.blank? || name.blank? || event.blank?
         puts "\tMissing key data, skipping row"
         next
      end
      if exists_ticket(uid, email)
        puts "\tTicket exists, skipping row"
        next
      end
      unless is_in_whitelist(uid)
        puts "\tTicket not in whitelist"
        next
      end
      if event_model.blank?
        puts "\tNo event with name '#{event}' found"
        next
      end
      cart = Cart.create email: email
      insert_order(cart, email, name, event_model)

      #check the Order exists
      order = cart.orders.last

      if order.blank? || order.email != email || order.event.id != event_model.id
        puts "\t Error creating the recent order #{order}"
        next
      end

      ticket_type_model = event_model.ticket_types.find_by_name(ticket_type)
      if ticket_type_model.blank?
        puts "could not find ticket type #{ticket_type}"
        next
      end

      puts "\t Found ticket type #{ticket_type_model.name}..."
      puts "\t Inserting ticket..."

      insert_ticket(name, order, ticket_type_model, event_model, email, uid)
      @inserted_items << row
      puts "\t ---- done ----"
    end

    puts "Inserted items: #{@inserted_items.count}"
    puts "Inserted items: #{@inserted_items.inspect}"

  end

  def get_connection
    ActiveRecord::Base.connection
  end

  def insert_order(cart, email, name, event)
    # Order(id: integer, event_id: integer, email: string, first_name: string, last_name: string, payment_method: string,
    # discount_code: string, discount_percentage: integer, tax_percentage: decimal, vat_number: string, company_name: string,
    # billing_address: text, seller_vat_number: string, seller_company_name: string, seller_billing_address: text,
    #invoice_seq_number: integer, invoice_asked: boolean, paid: boolean, sent: boolean, created_at: datetime,
    #updated_at: datetime, money_reservation_cost: integer, currency_reservation_cost: string, currency: string,
    #account_id: integer, title: string, checkout_id: string, cart_id: integer, pigeon_network: boolean, pigeon_network_phone_number: string)

    order_sql = <<-EOF
      INSERT INTO ORDERS(event_id, email, first_name, last_name, payment_method,
      discount_code, discount_percentage, tax_percentage, vat_number, company_name,
      billing_address, seller_vat_number, seller_company_name, seller_billing_address,
      invoice_seq_number, invoice_asked, paid, sent, created_at,
      updated_at, money_reservation_cost, currency_reservation_cost, currency,
      account_id, title, checkout_id, cart_id, pigeon_network, pigeon_network_phone_number)
    EOF

    order_checkout_id = "FIKKET-#{SecureRandom.hex(8)}-#{cart.id}"
    order_sql << "VALUES('#{event.id}', '#{email}', '#{name}', '', 'OFFLINE', '', 0, 0.21, '', '', '', '', '', '', 0, false, true, true, null, null, 0, 'EUR', 'EUR', 0, '', '#{order_checkout_id}', #{cart.id}, false, null);"

    puts "\t inserting order data..."
    @connection.exec_insert order_sql, "SQL", []
  end

  def insert_ticket(name, order, ticket_type, event, email, uid)
    # Ticket(id: integer, order_id: integer, first_name: string, last_name: string, email: string, ticket_type_id: integer,
    # name: string, description: text, money_price: integer, currency_price: string, code: string, canceled: boolean,
    # remark: text, created_at: datetime, updated_at: datetime, uuid: string, attendee_uuid: string, money_food_price: integer,
    # currency_food_price: string)

    order_sql = <<-EOF
    INSERT INTO tickets (order_id, first_name, last_name, email, ticket_type_id,
    name, description, money_price, currency_price, code, canceled,
    remark, created_at, updated_at, uuid, attendee_uuid, money_food_price,
    currency_food_price: string)

    EOF

    #order_checkout_id = "FIKKET-#{SecureRandom.hex(8)}-#{cart.id}"
    order_sql << "VALUES(#{order.id}, '#{name}', '', '#{email}', #{ticket_type.id}, '#{event.title}', '', 0, 'EUR', '#{uid}', NULL, NULL, NULL, NULL, '', '', 0, 'EUR');"

    puts "\t inserting ticket data..."
    @connection.exec_insert order_sql, "SQL", []
  end

  def exists_ticket(uid, email)
    Ticket.find_by_code_and_email uid, email
  end

  def is_in_whitelist(uid)
    ["ANGXCN","AVACNW","BOXTWT","CUAIIU","FBXUOC","IHVNQG","KAHAIE",
     "KVRMJL","KXOWIU","LHKQWD","SQCIXV","SZSBIB","UCYNKO","WITZZK","XMNETC"].include? uid
  end
end