from mod_python import apache
from pysqlite2 import dbapi2 as sqlite
from xml.dom.minidom import getDOMImplementation, parse, parseString
import os
import urllib
import sys

def loadNewCities(req, con):


	con = sqlite.connect( os.path.join(os.path.dirname(req.filename), 'cities'))
	con.text_factory = str
	con.row_factory = sqlite.Row

	cur = con.cursor()

	try:
                cur.execute( """
                             DELETE 
                             FROM cities
                             """)
        except:
                returnData = "Tulipa virhe %s" % sys.exc_info()[0]

	webFile = urllib.urlopen("http://fil.nrk.no/yr/viktigestader/verda.txt")
	filename =  os.path.join(os.path.dirname(req.filename), 'cities.txt')
	localFile = open(filename, 'w')
	localFile.write(webFile.read())
	webFile.close()
	localFile.close()

	for line in open(filename,'r').readlines():
		linecontent = line.split("\t")
		#following line won't work before You change content_type for text
		#req.write(str(len(linecontent)) + linecontent[0] + " " + linecontent[3] + " " + linecontent[10]+ " " + linecontent[12]+ " " +linecontent[13]+ " " + linecontent[17] + "<br>")
		try:
			cur.execute("""
                		INSERT INTO cities( Country, CityName, Lat, Lon, WeatherLink)
		                VALUES (:country, :cityname, :Lat, :Lon, :Link)
        	        	""", {"country":linecontent[10], "cityname":linecontent[3], "Lat":linecontent[12], "Lon":linecontent[13], "Link":linecontent[17]})
		except:
			return  "Tulipa virhe %s" % sys.exc_info()[0]
	con.commit()		
	return 0

#********************************************************************
#************************Main function*******************************
#**************This function returns list of cities******************
#********************************************************************
def index(req):
	con = sqlite.connect( os.path.join(os.path.dirname(req.filename), 'cities'))
        con.text_factory = str
        con.row_factory = sqlite.Row


	#req.content_type= "text/xml"
	#req.content_type = 'Content-type: text/xml; charset=utf-8'
	req.content_type = 'text/xml; charset=utf-8'
	impl = getDOMImplementation()
	doc = impl.createDocument("http://www.w3.org/1999/xhtml", "countries", None)
	doc.documentElement.setAttribute("xmlns", "http://www.w3.org/1999/xhtml")

	#Do check if file is modified after previous db update. If newer available call loadNewCities
	loadNewCities(req,con)
	cur = con.cursor()

	root = doc.createElement("root")
        doc.documentElement.appendChild(root)

	try:
		cur.execute("""
			SELECT DISTINCT Country
			FROM cities
			ORDER BY Country ASC
			""")
        except:
                returnData = "Tulipa virhe %s" % sys.exc_info()[0]

	for rivi in cur:	
		tr = doc.createElement("country")

		td = doc.createElement("countryname")
	   	tr.appendChild(td)
   		#td.appendChild( doc.createTextNode( content[index].decode("ISO-8859-1") ) ); 
		td.appendChild( doc.createTextNode( rivi['Country'].decode("UTF-8") ) );
		#req.write( rivi['CityName'])
		
		root.appendChild(tr)

	req.write( doc.toxml("UTF-8") )