import.rb

#!/usr/bin/env ruby

#Script to import a large set of users from a CSV 
#into CMSMS's FrontEndUsers module.  Assumes the 
#'cms_' database prefix.

require 'rubygems'
require 'faster_csv'
require 'pp'
require 'mysql'

def get_id(mysql)
	id = 0
	sql = 'SELECT id FROM cms_module_feusers_users_seq'
	res = mysql.query(sql)
	res.each do |row|
		id = row[0].to_i
	end
	mysql.query('UPDATE cms_module_feusers_users_seq set id = id + 1')
	id
end

def get_prop_id(mysql)
	id = 0
	sql = 'SELECT id FROM cms_module_feusers_properties_seq'
	res = mysql.query(sql)
	res.each do |row|
		id = row[0].to_i
	end
	mysql.query('UPDATE cms_module_feusers_properties_seq set id = id + 1')
	id
end
 
count = 0
badrecords = 0
bademails = []
cols = Array.new
row = nil
hash = {}

mysql = Mysql::new("localhost", "user", "password", "db")

#mysql.query('DELETE FROM cms_module_feusers_properties WHERE userid NOT IN (52,51)')
#mysql.query('DELETE FROM cms_module_feusers_belongs WHERE userid NOT IN (52,51) AND groupid = 2')
#mysql.query('DELETE FROM cms_module_feusers_users WHERE id NOT IN (52,51)')
#mysql.query('DELETE FROM cms_module_feusers_tempcode WHERE userid NOT IN (52,51)')

FasterCSV.open("import.csv", "rb", {:quote_char => '"', :col_sep =>',', :row_sep =>:auto}) do |csv|
	begin
		csv.each do |row|
			if count == 0
				cols = row
				cols[0] = 'userid'
				pp cols
			else
				hash = Hash[*cols.zip(row).flatten]
				new_id = get_id(mysql)

				sql = "INSERT INTO cms_module_feusers_users (id, username, password, createdate, expires) VALUES (" + new_id.to_s + ", '" + mysql.escape_string(hash['username']) + "', md5('" + mysql.escape_string(hash['password']) + "'), now(), now() + interval 5 year)"
				mysql.query(sql)
				sql = "INSERT INTO cms_module_feusers_belongs VALUES (" + new_id.to_s + ", 2)"
				mysql.query(sql)

				hash.each_pair do |key, value|
					unless key == 'username' or key == 'password'
						new_prop_id = get_prop_id(mysql)
						sql = "INSERT INTO cms_module_feusers_properties VALUES (" + new_prop_id.to_s + ", " + new_id.to_s + ", '" + mysql.escape_string(key.to_s) + "', '" + mysql.escape_string(value.to_s) + "')"
						mysql.query(sql)
					end
				end

			end
			count = count + 1
		end
	rescue
		puts "Crapped on : " + count.to_s + "\n"
		bademails << row[1]
		badrecords = badrecords + 1
		retry
	end
end

puts "Processed: " + count.to_s + "\n"
puts "Skipped: " + badrecords.to_s + "\n"
pp bademails