Freshdesk Solutions XML Parsing

freshdesk_solutions_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|
			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 = "biociden"
$connection = MigrationDB.new "localhost", "root", "", $customer_name

def merge_read_files directory_path
	files = Dir[File.join("#{directory_path}","*.xml")]
	r_file = ""
	files.each do |file|
		r_file = r_file + ( File.read file ).gsub('<?xml version="1.0" encoding="UTF-8"?>','')
	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

# change the directory containing xmls here.
get_solutions "xmls"