#!/usr/bin/ruby

# Need the DateTime library.
require "date"
require "mysql"

# Declaring some needed arrays for manipulating data.
input_file_line = Array.new
output_file_line = Array.new
input_file_headline = Array.new

# Name of the SQL database and table
sql_hostname = "****"
sql_username = "****"
sql_password = "****"
sql_database = "****"
sql_table = "****"

# Connecting to database
my = Mysql.connect(sql_hostname, sql_username, sql_password, sql_database)

# Opening the file streaming. Need to specify the path/filename.csv here.
input_filename = "test.csv"
input_file_stream = File.open(input_filename, "r")

# This line stats parsing the file. First line should be the login line.
input_file_headline = input_file_stream.gets.to_s.split(/,/)
login_id = input_file_headline[14].match(/[0-9]+/).to_s

# Output file creation
#output_filename =  Date.parse(input_file_headline[15]).to_s + "_" + login_id + ".sql"
output_filename = Time.now.strftime("%Y-%m-%d").to_s + ".sql"
output_file = File.open(output_filename, 'w')

# Writing input file headling to output file. Taking the first action from the file.
output_file_line = "INSERT INTO " + sql_table + " (created_at, updated_at, date,phone_id, action, position_id) VALUES (\"" + Time.now.strftime("%Y-%m-%d %H:%M:%S") + "\",\"" + updated_at = Time.now.strftime("%Y-%m-%d %H:%M:%S") + "\",\"" + Date.parse(input_file_headline[15]).to_s + " " + input_file_headline[16].gsub(/"/, "") + "\"," + login_id + "," + input_file_headline[18] + ", " + input_file_headline[19] + ");\r\n"
output_file.write(output_file_line)
#my.query(output_file_line)

# Looping through the input file. Taking values needed and writing to output file. Ends on EOF.
begin
	input_file_line = input_file_stream.gets.to_s.split(/,/)
	output_file_line = "INSERT INTO " + sql_table + " (created_at, updated_at, date, phone_id, action, position_id) VALUES (\"" + Time.now.strftime("%Y-%m-%d %H:%M:%S") + "\",\"" + updated_at = Time.now.strftime("%Y-%m-%d %H:%M:%S") + "\",\"" + Date.parse(input_file_line[14]).to_s + " " + input_file_line[15].gsub(/"/, "") + "\"," + login_id + "," + input_file_line[17] + ", " + input_file_line[18] + ");\r\n"
	output_file.write(output_file_line)
	#my.query(output_file_line)
	#puts input_file_line[24]

end while !input_file_stream.eof?
#end while (input_file_line[24].empty? == false)
puts input_file_line[24].empty?

# Closing the streamings.
input_file_stream.close()
output_file.close()

puts ">> File " + input_filename + " parsed and imported successfully."
puts ">> File " + output_filename + " created for future use."