# CS288 Homework 9
# Read the skeleton code carefully and try to follow the structure
# You may modify the code but try to stay within the framework.

import libxml2
import sys
import os
import commands
import re
import sys

import MySQLdb

from xml.dom.minidom import parse, parseString

# for converting dict to xml 
from cStringIO import StringIO
from xml.parsers import expat

#def get_elms_for_atr_val(tag,atr,val):
def get_elms_for_atr_val(tag):
   lst=[]
   elms = dom.getElementsByTagName(tag)
   # ............
   
   lst=elms[2].childNodes
   
   lst = lst[1:]
   
   
   
   #attr = filter(lambda x: len(x.childNodes)==6, elms)
   #lst = filter(lambda x: get_text(x), attr)
   
   
   
   #.............
   return lst

# get all text recursively to the bottom
def get_text(e):
   lst=[]
   for x in e.childNodes:
   		if x.nodeType in (3,4):
   			if x.data != "\n":
   				lst.append(replace_white_space(x.data)) 
   		else:
   				lst=lst + get_text(x)
   # ............
   return lst

# replace whitespace chars
def replace_white_space(str):
   p = re.compile(r'\s+')
   new = p.sub(' ',str)   # a lot of \n\t\t\t\t\t\t
   return new.strip()

# replace but these chars including ':'
def replace_non_alpha_numeric(s):
   p = re.compile(r'[^a-zA-Z0-9:-]+')
   #   p = re.compile(r'\W+') # replace whitespace chars
   new = p.sub(' ',s)
   return new.strip()

# convert to xhtml
# use: java -jar tagsoup-1.2.jar --files html_file
def html_to_xml(fn):
   # ............
   lst=[]
   s = commands.getoutput('java -jar tagsoup-1.2.1.jar --files '+fn)
   lst = s.split('dst: ')
   xhtml_file = lst[-1]
   return xhtml_file

def extract_values(dm):
   lst = []
   l = get_elms_for_atr_val('table')
   
   # ............
   for i in l:
   		lst.append(get_text(i))
   
   # ............
   return lst

# mysql> describe most_active;
def insert_to_db(l,tbl):
	conn=MySQLdb.connect (host="localhost",db="stocks",user='root',passwd='larry')
	cursor = conn.cursor()
	s='create table '+tbl+' (symbol varchar(10),name varchar(80),price float,chng float,pchng float,volum int(10));'
	
	rslt=cursor.execute(s)
	print l
	
	for x in l:
		s="insert into "+tbl+" (symbol,name,price,chng,pchng,volum) values ('"+x[0]+"','"+x[1]+"','"+x[3][1:]+"','"+x[4]+"','"+x[5]+"','"+x[2].replace(",",'')+'");"'
		rslt=cursor.execute(s)
		
	
	
	
	conn.commit()
   # ............
   
	return cursor
# show databases;
# show tables;

def main():
	html_fn = sys.argv[1]
   	fn = html_fn.replace('.html','')
   	xhtml_fn = html_to_xml(html_fn)
 
   	global dom
   	dom = parse(xhtml_fn)

   	lst = extract_values(dom)

   # make sure your mysql server is up and running
   
  	cursor = insert_to_db(lst,fn) # fn = table name for mysql
  	
    
  	
  	#
  	#l = select_from_db(cursor,fn)
    # display the table on the screen

   # make sure the Apache web server is up and running
   # write a PHP script to display the table(s) on your browser

   	return 
   	#xml
# end of main()

if __name__ == "__main__":
    main()

# end of hw7.py
