Freshdesk XML Parser

freshdesk_xml_parser.rb

require 'ruby-cheerio'
require 'mysql'

class MigrationDB

	def initialize domain, user_name, password, db_name
		begin
			@db_connection = Mysql.new(domain, user_name, password)
			@db_name = db_name
		rescue Exception => e
			p "Error connecting to DB Server.."
		end
		create_and_use_db
	end

	def create_table table_name, columns
		execute_query do
			"CREATE TABLE IF NOT EXISTS #{table_name}(`#{columns.join('` TEXT,`')}` TEXT)"
		end
	end

	def update_table table_name, key, value, id_key, id_value
		execute_query do
			"UPDATE #{table_name} SET `#{key}` = \"#{escape_string(value)}\""
		end
	end

	def insert_table table_name, i_hash
		execute_query do
			"INSERT #{table_name}(`#{i_hash.keys.join('`,`')}`) VALUES(\"#{i_hash.values.map{|p| escape_string(p)}.join('","')}\")"
		end
	end

	def create_and_insert_table table_name, a_hash
		column_names = a_hash.map{|h| h.keys}.flatten.uniq
		create_table table_name, column_names
		a_hash.each do |i_hash|
			p "inserting table #{table_name}: #{i_hash}"
			insert_table table_name, i_hash  
		end
	end

	private

	def create_and_use_db
		execute_query do
			"CREATE DATABASE IF NOT EXISTS #{@db_name}"
		end

		execute_query do
			"USE #{@db_name}"
		end
	end

	def escape_string str
		str.gsub!(/\"/,'\'') if str.include? '"'
		str.gsub!(/\'/,'\'') if str.include? "'"
		str
	end

	def execute_query
		begin
			@db_connection.query(yield) if block_given?
		rescue Exception => e
			p e
		end
	end

end


# change the customer name, DB details here.
$customer_name = ""
$connection = MigrationDB.new "localhost", "root", "", $customer_name

def merge_read_files directory_path, file_part_name=nil
	files = (file_part_name.nil?)?(Dir[File.join("#{directory_path}","*.xml")]):(Dir[File.join("#{directory_path}","#{file_part_name}*.xml")])
	r_file = ""
	files.each do |file|
		r_file = r_file + ( File.read file ).gsub('<?xml version="1.0" encoding="UTF-8"?>','')
		p "Merging File: #{file}.."
	end
	"<file>"+r_file+"</file>"
end

def get_solutions directory_path
	doc = RubyCheerio.new(merge_read_files directory_path)
	categories = Array.new
	folders = Array.new
	articles = Array.new
	doc.find('solution-category').each do |scategory|
		categories << { created_at: scategory.find('created-at')[0].text, id: scategory.find('id')[0].text, updated_at: scategory.find('updated-at')[0].text, description: scategory.find('description')[0].text, name: scategory.find('name')[0].text } 
		solution_folders = scategory.find('solution-folder')
		solution_folders.each do |sfolder|
			folders << { id: sfolder.find('id')[0].text, category_id: sfolder.find('category-id')[0].text, description: sfolder.find('description')[0].text, name: sfolder.find('name')[0].text, updated_at: sfolder.find('updated-at')[0].text, visibility: sfolder.find('visibility')[0].text }
			solution_article = sfolder.find('solution-article')
			solution_article.each do |sarticle|
				articles << {folder_id: sfolder.find('id')[0].text, title: sarticle.find('title')[0].text, description: sarticle.find('description')[0].text, created_at: sarticle.find('created-at')[0].text, art_type: sarticle.find('art-type')[0].text, user_id: sarticle.find('user-id')[0].text}
			end
		end
	end
	$connection.create_and_insert_table 'categories', categories
	$connection.create_and_insert_table 'folders', folders
	$connection.create_and_insert_table 'articles', articles
end

def get_distinct_custom_fields doc
	custom_fields = Array.new
	doc.find('helpdesk-ticket').each do |ticket|
		nokogiri_xml = Nokogiri::XML(ticket.find('custom_field')[0].html)
		fields = nokogiri_xml.children[0].children.map{|c| c.name}
		fields.delete "text"
		custom_fields << fields
	end
	custom_fields.flatten.uniq
end

# get_tickets updated with custom_fields.
def get_tickets directory_path
	doc = RubyCheerio.new( merge_read_files directory_path, "Ticket")
	tickets = Array.new
	notes = Array.new
	custom_fields = Array.new
	custom_fields_keys = get_distinct_custom_fields doc
	doc.find('helpdesk-ticket').each do |ticket|
		# Tickets part.
		tickets << { created_at: ticket.find('created-at')[0].text, deleted: ticket.find('deleted')[0].text, description: ticket.find('description')[0].text, description_html: ticket.find('description-html')[0].text, display_id: ticket.find('display-id')[0].text, group_id: ticket.find('group-id')[0].text, id: ticket.find('id')[0].text, requester_id: ticket.find('requester-id')[0].text, responder_id: ticket.find('responder-id')[0].text, spam: ticket.find('spam')[0].text, source: ticket.find('source')[0].text, status: ticket.find('status')[0].text, subject: ticket.find('subject')[0].text, ticket_type: ticket.find('ticket-type')[0].text, trained: ticket.find('trained')[0].text,  updated_at: ticket.find('updated-at')[0].text, status_name: ticket.find('status-name')[0].text,  priority_name: ticket.find('priority-name')[0].text, source_name: ticket.find('source-name')[0].text, requester_name: ticket.find('requester-name')[0].text, responder_name: ticket.find('responder-name')[0].text, attachments: ticket.find('attachments')[0].text }
		# Notes part.
		ticket.find('helpdesk-note').each do |note|
			notes << { ticket_display_id: ticket.find('display-id')[0].text, body: note.find('body')[0].text, body_html: note.find('body-html')[0].text, created_at: note.find('created-at')[0].text, deleted: note.find('deleted')[0].text, id: note.find('id')[0].text, incoming: note.find('incoming')[0].text, is_private: note.find('private')[0].text, source: note.find('source')[0].text, updated_at: note.find('updated-at')[0].text, user_id: note.find('user-id')[0].text, attachments: note.find('attachments')[0].text }
		end
		# Custom_fields part.
		ticket_custom_fields = Hash.new
		custom_fields_keys.each do |cf|
			acf = ticket.find(cf)
			unless acf.empty?
				ticket_custom_fields[cf] = acf[0].text
			end
		end
		ticket_custom_fields['ticket_display_id'] = ticket.find('display-id')[0].text
		custom_fields << ticket_custom_fields
	end
	$connection.create_and_insert_table 'custom_fields', custom_fields
	$connection.create_and_insert_table 'tickets', tickets
	$connection.create_and_insert_table 'notes', notes
end

def get_users directory_path
	doc = RubyCheerio.new( merge_read_files directory_path, "User")
	users = Array.new
	doc.find('user').each do |user|
		users << { id: user.find('id')[0].text, name: user.find('name')[0].text, phone: user.find('phone')[0].text, mobile: user.find('mobile')[0].text, email: user.find('email')[0].text, address: user.find('address')[0].text, twitter_id: user.find('twitter-id')[0].text }
	end
	$connection.create_and_insert_table 'users', users
end


# change the directory containing xmls here and the customer name on the top.
# get_solutions "xmls"
# get_tickets "input"
# get_users "input"