from mod_python import apache
from mod_python import util
import MySQLdb
import sys
import os


# DRSU database access class
class drsudb:
	def __init__(self):
		dbconnect = ['lwalab.phys.unm.edu','mcsdr', 'mcsdr_py','VGUV2t8zsVJBhQ36']
		__db=MySQLdb.connect(dbconnect[0], dbconnect[2], dbconnect[3], dbconnect[1])
		self.c = __db.cursor()
	
	def size(self, barcode):
		query = "SELECT SUM(size) FROM disks WHERE barcode='%s' " % barcode
		self.c.execute(query)
		row = self.c.fetchone()
		return row[0]
		
	def ndisks(self, barcode):
		query = "SELECT * FROM disks WHERE barcode='%s' " % barcode
		num_rows = self.c.execute(query)
		return num_rows
	
	def list_drsus(self,  orderby='timestamp'):
		query = "SELECT `barcode`,`owner`,`location`,`status`,`condition` FROM unit WHERE barcode LIKE 'S%%' ORDER BY `%s` DESC" % orderby
		data = []
		self.c.execute(query)
		rows = self.c.fetchall()
		for row in rows:
			size = self.size(row[0])
			num = self.ndisks(row[0])
			data.append([("<a href='detail?barcode=%s'>%s</a>" % (row[0], row[0])), row[1], row[2], row[3], row[4], ("%s TB/%s" % (size, num))])
		return data
		
	def list_external(self,  orderby='timestamp'):
		query = "SELECT `barcode`,`owner`,`location`,`status`,`condition` FROM unit WHERE barcode LIKE 'E%%' ORDER BY `%s` ASC" % orderby
		data = []
		self.c.execute(query)
		rows = self.c.fetchall()
		for row in rows:
			size = self.size(row[0])
			data.append([("<a href='detail?barcode=%s'>%s</a>" % (row[0], row[0])), row[1], row[2], row[3], row[4], ("%s TB" % (size))])
		return data
			
	def detail_summary(self, barcode):
		query = "SELECT `barcode`,`checksum`,`owner`,`location`,`status`,`condition`,`timestamp`,`comment` FROM unit WHERE barcode='%s'" % barcode
		num_rows = self.c.execute(query)
		if num_rows == 0:
			list = None
		else:
			row = self.c.fetchone()
			size = self.size(barcode)
			num = self.ndisks(barcode)
			list = [row[0], row[1], size,  num, row[2], row[3], row[4], row[5], row[6], row[7]]
		return list
		
	def list_disks(self, barcode):
		query = "SELECT `manufacturer`,`model`,`serial`,`firmware`,`size` FROM disks WHERE barcode='%s' ORDER BY serial ASC" % barcode
		self.c.execute(query)
		rows = self.c.fetchall()
		list = []
		i = 0
		for row in rows:
			i = i+1
			list.append([i, row[0], row[1], row[2], row[3], "%s TB" % row[4]])
		return list
		
	def list_usage(self, barcode, select, req):
		if select=='available':
			query = "SELECT `tag`,`size`,`timestamp`,`sdf`,`project`,`copied`,`comment` FROM `usage` WHERE barcode='%s' AND original='available'" % barcode
		else:
			query = "SELECT `tag`,`size`,`timestamp`,`sdf`,`project`,`copied`,`comment` FROM `usage` WHERE barcode='%s'" % barcod
		num_rows = self.c.execute(query)
		if num_rows>0:
			rows = self.c.fetchall()		
			list = []
			for row in rows:
				if row[5]=='copied':
					copied = 'x'
				else:
					copied = '&nbsp;'
				list.append(["<a href=searchtag?tag=%s>%s</a>" % (row[0], row[0]), row[1], row[2], row[3], row[4],copied , row[6]])
		else:
			list = [['None available', '']]
		return list
		
	def list_condition(self, barcode, req):
		query = "SELECT `timestamp`,`passed`,`write_average`,`read_average`,`comment` FROM `condition` WHERE barcode='%s' ORDER BY timestamp DESC" % barcode
		num_rows = self.c.execute(query)
		if num_rows>0:
			rows = self.c.fetchall()
			list = []
			for row in rows:
				if row[1] == 'yes':
					passed = 'x'
				else:
					passed = '&nbsp'
				list.append([row[0], passed, "%s MiB/s" % row[2], "%s MiB/s" % row[3],  row[4]])
		else:
			list = [['No conditioning records found!', '']]
		return list
		
	def get_tag(self, tag):
		query = "SELECT * FROM `usage` WHERE tag LIKE '%%%s%%' ORDER BY delivered ASC" % tag
		num_rows = self.c.execute(query)
		if num_rows>0:
			list = []
			rows = self.c.fetchall()
			once = True
			i = 1
			for row in rows:
				if once==True:
					list.append('Filename: <b>%s</b>' % row[3])
					list.append('Size: %s Byte (%.2f GByte)' % (row[5], row[5]/1073741824.0))
					list.append('File Created: %s' % row[2])
					list.append('Intention: %s' % row[4])
					list.append('Project/SDF: %s - %s' % (row[6], row[7]))
					query = "SELECT * FROM `qos` WHERE tag LIKE '%%%s%%'" % tag
					num_rows = self.c.execute(query)
					if num_rows>0:
						qos = self.c.fetchone()
						string = qos[9].split('\n')
						if len(string)>1:
						  list.append("<br><b>Filecheck Output:</b><pre><code>")
						  for each in string:
							  list.append("%s" % each)
						  list.append("</code></pre>")
						else:
						  list.append("<table border=1><tr><th></th><th>1X</th><th>1Y</th><th>2X</th><th>2Y</th></tr>")
						  list.append("<tr><td>Clipping</td><td>%s%%</td><td>%s%%</td><td>%s%%</td><td>%s%%</td></tr>" % (qos[1],qos[2],qos[3],qos[4]))
						  list.append("<tr><td>Power</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr></table>" % (qos[5],qos[6],qos[7],qos[8]))
					else:
						list.append("no clip level information present<br>")
				once = False
				list.append('<br><b>Entry %i</b> (%s):' % (i,row[2]))
				if row[9] == 'copied':
					list.append('<b>Copied</b>: %s' % row[12])
				if row[10]=='available':
					list.append('File <b>available</b> on disk with barcode: %s' % row[1])
				else:
					list.append('</b>deleted from disk: %s' % row[1])
				if row[11]=='Y':
					list.append('<br><b><blink>File delivered to PI</blink></b>')
				i=i+1
		else:
			list = ['File not found!']
		return list
		
	def projectfiles(self, code):
		query = "SELECT `tag`,`copied`,`delivered` FROM `usage` WHERE project LIKE '%%%s%%' ORDER BY `tag` DESC" % code
		num_rows = self.c.execute(query)
		if num_rows>0:
			list = []
			rows = self.c.fetchall()
			prev = ""
			for row in rows:
				if prev != row[0]:
					if row[2]=='Y':
						list.append("<a href='searchtag?tag=%s'>%s</a> (%s & delivered)<br>" % (row[0], row[0], row[1]))
					else:
						list.append("<a href='searchtag?tag=%s'>%s</a> (%s)<br>" % (row[0], row[0], row[1]))
					prev = row[0]
		else:
			list = ['No files found or project does not exist!']
		return list
		
	def __del__(self):
		try:
			self.__db.close()
		except AttributeError:
			pass


def handler(req):
	# Dynamic
	if os.path.split(req.uri)[1]  in ('', 'index'):
		status = index(req)
	elif os.path.split(req.uri)[1] == 'search':
		status = search(req)
	elif os.path.split(req.uri)[1] == 'overview':
		status = overview(req)
	elif os.path.split(req.uri)[1] == 'tools':
		status = tools(req)
	elif os.path.split(req.uri)[1] == 'detail':
		status = detail(req)
	elif os.path.split(req.uri)[1] == 'searchtag':
		status = searchtag(req)
	elif os.path.split(req.uri)[1] == 'viewproject':
		status = viewproject(req)
	# Static
	elif req.uri.find('/js/') != -1:
		status = _javascript(req)
	elif req.uri.find('/stylesheets/') != -1:
		status = _css(req)
	elif req.uri.find('/images/') != -1:
		status = _images(req)
	elif req.uri.find('/tools/') != -1:
		status = _tools(req)
	# Not found
	else:
		status = _notFound(req)
		
	return status


# Helper functions, could be converted to classes, if getting too much
def _css(req):
	filename = req.uri.split('/stylesheets/', 1)[1]
	
	try:
		fullpath = os.path.join(os.path.dirname(__file__), 'stylesheets', filename)
		fh = open(fullpath)
		content = fh.read()
		fh.close()
		
		req.content_type = "text/css"
		req.write( content )
		status = apache.OK
	except:
		status = _notFound(req)
		
	return status

def _javascript(req):
	filename = req.uri.split('/js/', 1)[1]
	
	try:
		fullpath = os.path.join(os.path.dirname(__file__), 'js', filename)
		fh = open(fullpath)
		content = fh.read()
		fh.close()
		
		req.content_type = "text/javascript"
		req.write( content )
		status = apache.OK
	except:
		status = _notFound(req)
		
	return status

def _images(req):
	filename = req.uri.split('/images/', 1)[1]
	
	try:
		fullpath = os.path.join(os.path.dirname(__file__), 'images', filename)
		fh = open(fullpath, 'rb')
		content = fh.read()
		fh.close()
		
		# Figure out the MIME type to send
		ext = os.path.splitext(fullpath)[1]
		if ext == '.png':
			## PNG
			mime = "image/png"
		elif ext == '.jpeg' or ext == '.jpg':
			## JPEG
			mime = "image/jpeg"
		elif ext == '.gif':
			## GIF
			mime = "image/gif"
		else:
			## ???
			mime = "application/octet-stream"
			
		req.content_type = mime
		req.write( content )
		status = apache.OK
	except:
		status = _notFound(req)
		
	return status

def _tools(req):
	filename = req.uri.split('/tools/', 1)[1]
	
	try:
		fullpath = os.path.join(os.path.dirname(__file__), 'tools', filename)
		fh = open(fullpath, 'rb')
		content = fh.read()
		fh.close()
		
		req.content_type = "text"
		req.write( content )
		status = apache.OK
	except:
		status = _notFound(req)
		
	return status

def includehtml(req,fname):
	with open(fname,'r') as outfile:
		for line in outfile:
			req.write(line)

def table_header(req, list, id=None):
	if id is None:
		id = 'dataTable'
	req.write("<table id='%s'>\n<thead>\n<tr>\n" % id)
	for each in list:
		req.write(("<th>%s</th>\n" % each))
	req.write("</th>\n</tr>\n</thead>\n<tbody>\n")
	
def table_row(req, list):
	req.write("<tr>\n")
	for each in list:
		req.write(("<td>%s</td>\n" % each))
	req.write("</tr>\n")
	
def table_footer(req):
	req.write("</tbody>\n</table>\n")

def get_location(location, status, req):
	locdic = {'DR': 'loc', 'DR1': 'loc',  'DR2':'loc', 'DR3':'loc',  'DR4':'loc',  'DR5':'loc',  'MCS':'loc', 
			   'data':'contains data', 'free':'formatted & available for recording', 'new':'new unit', 
			   'cleared':'contains redundant data (can be deleted)', 'fault':'faulty unit'}
	if locdic[status]=='loc':
		loc = "%s:%s" % (location, status)
		stat = "data recording ..."
	elif len(location.split(':'))==2:
		loc = "<b>%s</b> to destination: <b>%s</b>" % (location.split(':')[0], location.split(':')[1])
		stat = locdic[status]
	else:
		loc = location
		stat = locdic[status]
	return loc, stat
	

# Page functions
def summary(req):
	req.content_type = "text/html"
	dbconnect = ['lwalab.phys.unm.edu','mcsdr', 'mcsdr_py','VGUV2t8zsVJBhQ36']
	db=MySQLdb.connect(dbconnect[0], dbconnect[2], dbconnect[3], dbconnect[1])
	c = db.cursor()
	query = "SELECT barcode  FROM `unit` WHERE `barcode` LIKE 'S%' AND `location` LIKE 'UNM' AND (`status` LIKE 'new' OR `status` LIKE 'free' OR `status` LIKE 'cleared') AND `condition` = 'ok' ORDER BY barcode ASC"
	c.execute(query)
	drsus = c.rowcount
	req.write("<table>")
	req.write("<tr><td>DRSU's @ UNM available for DR:</td><td>Total:</td><td><b>%i</b></td></tr>" % (drsus))	
	
	query = "SELECT barcode FROM `unit` WHERE `barcode` LIKE 'S%' AND `owner` = 'LWA'"
	c.execute(query)
	total = c.rowcount
	query = "SELECT barcode  FROM `unit` WHERE `barcode` LIKE 'S%' AND `location` LIKE 'UNM' AND (`status` LIKE 'new' OR `status` LIKE 'free' OR `status` LIKE 'cleared') AND `condition` = 'ok' AND `owner`='LWA'"
	c.execute(query)	
	ready = c.rowcount
	req.write("<tr><td></td><td>LWA:</td><td>%i/%i</td></tr>" % (ready,total))
	
	query = "SELECT barcode FROM `unit` WHERE `barcode` LIKE 'S%' AND `owner` LIKE 'Long Island%'"
	c.execute(query)
	total = c.rowcount
	query = "SELECT barcode  FROM `unit` WHERE `barcode` LIKE 'S%' AND `location` LIKE 'UNM' AND (`status` LIKE 'new' OR `status` LIKE 'free' OR `status` LIKE 'cleared') AND `condition` = 'ok' AND `owner` LIKE 'Long Island%'"
	c.execute(query)
	ready = c.rowcount
	req.write("<tr><td></td><td>Long Island:</td><td>%i/%i</td></tr>" % (ready,total))
	
	query = "SELECT barcode FROM `unit` WHERE `barcode` LIKE 'S%' AND `owner` LIKE 'Virginia%'"
	c.execute(query)
	total = c.rowcount
	query = "SELECT barcode  FROM `unit` WHERE `barcode` LIKE 'S%' AND `location` LIKE 'UNM' AND (`status` LIKE 'new' OR `status` LIKE 'free' OR `status` LIKE 'cleared') AND `condition` = 'ok' AND `owner` LIKE 'Virginia%'"
	c.execute(query)
	ready = c.rowcount
	req.write("<tr><td></td><td>VT:</td><td>%s/%s</td></tr>" % (ready,total))

	query = "SELECT barcode FROM `unit` WHERE `barcode` LIKE 'S%'"
	c.execute(query)
	drsus = c.rowcount
	req.write("<tr><td>Number of DRSU's in DB:</td><td><b>%i</b></td></tr>" % (drsus))
	req.write("</table>")
	db.close()	
	return apache.OK

def index(req):
	req.content_type = "text/html"
	req.send_http_header()
	includehtml(req,"/var/www/drsus/header.html")
	req.write("<h1>Summary</h1>\n")
	summary(req)
	includehtml(req,"/var/www/drsus/footer.html")
	return apache.OK

def search(req):
	req.content_type = "text/html"
	req.send_http_header()
	includehtml(req,"/var/www/drsus/header.html")
	includehtml(req,"/var/www/drsus/searchform.html")
	includehtml(req,"/var/www/drsus/footer.html")
	return apache.OK

def tools(req):
	req.content_type = "text/html"
	req.send_http_header()
	includehtml(req,"/var/www/drsus/header.html")
	includehtml(req,"/var/www/drsus/tools.html")
	includehtml(req,"/var/www/drsus/footer.html")
	return apache.OK

def overview(req):
	db = drsudb()
	formdata = util.FieldStorage(req)
	req.content_type = "text/html"
	req.send_http_header()
	includehtml(req,"/var/www/drsus/header.html")
	req.write("<h1>Overview</h1>\n")
	req.write("<p>Overview of all DRSUs and external disks in the database.  Jump to <a href='#DRSU'>DRSUs</a> or <a href='#external'>external disks</a>.</p>")
	# Output for DRSUs
	req.write("<h3><a name='DRSU'></a>DRSUs</h3>\n")
	table_header(req, ["Barcode", "Owner", 
					   "Location", "Status", 
					   "Condition", "Size/Disks"], id='drsusTable')
	try:
		for each in db.list_drsus(formdata['orderby']):
			table_row(req, each)
	except:
		for each in db.list_drsus():
			table_row(req, each)
	table_footer(req)
	# Output for External Disks
	req.write("<br /><br />\n")
	req.write("<h3><a name='external'></a>External Disks</h3>\n")
	table_header(req, ["Barcode", "Owner", 
					   "Location", "Status", 
					   "Condition", "Size"], id='externalsTable')
	try:
		for each in db.list_external(formdata['orderby']):
			table_row(req, each)
	except:
		for each in db.list_external():
			table_row(req, each)
	table_footer(req)
	req.write("""
<script type="text/javascript">
  jQuery.extend( jQuery.fn.dataTableExt.oSort, {

    "num-html-asc": function ( x, y ) {
    var a = Number(x.replace( /<.*?>/g, "" ));
    var b = Number(y.replace( /<.*?>/g, "" ));
    return ((a < b) ? -1 : ((a > b) ? 1 : 0));
    },

    "num-html-desc": function ( x, y ) {
    var a = Number(x.replace( /<.*?>/g, "" ));
    var b = Number(y.replace( /<.*?>/g, "" ));
    return ((a < b) ? 1 : ((a > b) ? -1 : 0));
    },
    
    "text-html-asc": function ( x, y ) {
    var a = x.replace( /<.*?>/g, "" );
    var b = y.replace( /<.*?>/g, "" );
    return ((a < b) ? -1 : ((a > b) ? 1 : 0));
    },

    "text-html-desc": function ( x, y ) {
    var a = x.replace( /<.*?>/g, "" );
    var b = y.replace( /<.*?>/g, "" );
    return ((a < b) ? 1 : ((a > b) ? -1 : 0));
    }
    } );
    
    $(document).ready(function() {
        $("#drsusTable").dataTable( {
      "aaSorting" : [ [2, 'asc'], [3, 'desc' ], ],
      "aoColumns" : [
                { "sType": "text-html" },
                { "sType": "text-html" },
                { "sType": "text-html" },
                { "sType": "text-html" },
                { "sType": "text-html" },
                null,
                ]
      } );
    });

    $(document).ready(function() {
        $("#externalsTable").dataTable( {
      "aaSorting" : [ [2, 'asc'], [3, 'desc' ], ],
      "aoColumns" : [
                { "sType": "text-html" },
                { "sType": "text-html" },
                { "sType": "text-html" },
                { "sType": "text-html" },
                { "sType": "text-html" },
                null,
                ]
      } );
    });
</script>
""")
	includehtml(req,"/var/www/drsus/footer.html")
	return apache.OK
	
def searchtag(req):
	db = drsudb()
	formdata = util.FieldStorage(req)
	try: 
		tag = formdata['tag']
		req.content_type = "text/html"
		req.send_http_header()
		includehtml(req,"/var/www/drsus/header.html")
		req.write("<h1>Details for %s</h1>\n" % tag)
		for each in db.get_tag(tag):
			req.write("%s<br>" % each)		
		includehtml(req,"/var/www/drsus/footer.html")
	except:
		req.content_type = "text/html"
		req.send_http_header()
		req.write("ERROR!")
	return apache.OK
	
def viewproject(req):
	db = drsudb()
	formdata = util.FieldStorage(req)
	req.content_type = "text/html"
	req.send_http_header()
	try:
		code = formdata['code']
		includehtml(req,"/var/www/drsus/header.html")
		req.write("Files related to project code <b>%s</b>:<br><br>" % code)
		for each in db.projectfiles(code):
			req.write(each)			 
		includehtml(req,"/var/www/drsus/footer.html")
	except:
		req.write("ERROR!")
	return apache.OK
	
def detail(req):
	db = drsudb()
	formdata = util.FieldStorage(req)
	try:
		if formdata['what']=='barcode':
			formdata.add_field('barcode', formdata['search'])
		elif formdata['what']=='checksum':
			req.write("Checksum search not implemented yet!")
		elif formdata['what']=='tag':
			util.redirect(req, 'searchtag?tag=%s' % formdata['search'])
		elif formdata['what']=='project':
			util.redirect(req, 'viewproject?code=%s' % formdata['search'])
	except:
		pass
	req.content_type = "text/html"
	req.send_http_header()
	includehtml(req,"/var/www/drsus/header.html")
	# Show summary
	try:
		summary = db.detail_summary(formdata['barcode'])
		if summary != None:
			req.write("<h1>Details of %s</h1>\n" % summary[0])
			req.write("<p>Jump to <a href='#disks'>disk listing</a>, <a href='#files'>file listing</a>, or <a href='#history'>conditing history.</p>\n")
			req.write("<table border=0><tr>")
			req.write("<td>Barcode:</td><td><b>%s</b></td></tr>" % summary[0])
			req.write("<tr><td>Checksum (md5):</td><td>%s</td></tr>" % summary[1])
			req.write("<tr><td>Total Size:</td><td>%s TB</td></tr>" % summary[2])
			req.write("<tr><td>Number of disks:</td><td>%s</td></tr>" % summary[3])
			req.write("<tr><td>Owner:</td><td>%s</td></tr>" % summary[4])
			loc, stat = get_location(summary[5], summary[6], req)
			req.write("<tr><td>Location:</td><td>%s</td></tr>" % loc)
			req.write("<tr><td>Status:</td><td>%s</td></tr>" % stat)
			req.write("<tr><td>Condition:</td><td>%s</td></tr>" % summary[7])
			req.write("<tr><td>Last change:</td><td>%s</td></tr>" % summary[8])
			req.write("<tr><td>Comments:</td><td>%s</td></tr>" % summary[9])
			req.write("</table><br><br><hline border=1>")
			
			# Show disk infos
			req.write("<h3><a name='disks'></a>Disks</h3>")
			table_header(req, ["Disk", "Manufacturer", "Model", "Serial", "Firmware", "Size"], id='disksTable')
			for each in db.list_disks(formdata['barcode']):
				table_row(req, each)
			table_footer(req)
			req.write("<br /><br />\n")
			
			# Show usage history (only available files, with link to show all) 
			req.write("<h3><a name='files'></a>Files</h3>")
			table_header(req, ["Filename", "Size", "Time Created", "sdf", "Project", "Copied", "Comments"], id='filesTable')
			for each in db.list_usage(formdata['barcode'], 'available', req):
				table_row(req, each)
			table_footer(req)
			req.write("<br /><br />\n")
			
			# Show conditioning history
			req.write("<h3><a name='history'></a>Conditioning History</h3>")
			table_header(req, ["Timestamp", "Pass", "Write Average", "Read Average", "Comments"], id='historyTable')
			for each in db.list_condition(formdata['barcode'], req):
				table_row(req, each)
			table_footer(req)
			
			# Javascript fun
			req.write("""
<script type="text/javascript">
  jQuery.extend( jQuery.fn.dataTableExt.oSort, {

    "num-html-asc": function ( x, y ) {
    var a = Number(x.replace( /<.*?>/g, "" ));
    var b = Number(y.replace( /<.*?>/g, "" ));
    return ((a < b) ? -1 : ((a > b) ? 1 : 0));
    },

    "num-html-desc": function ( x, y ) {
    var a = Number(x.replace( /<.*?>/g, "" ));
    var b = Number(y.replace( /<.*?>/g, "" ));
    return ((a < b) ? 1 : ((a > b) ? -1 : 0));
    },

    "text-html-asc": function ( x, y ) {
    var a = x.replace( /<.*?>/g, "" );
    var b = y.replace( /<.*?>/g, "" );
    return ((a < b) ? -1 : ((a > b) ? 1 : 0));
    },

    "text-html-desc": function ( x, y ) {
    var a = x.replace( /<.*?>/g, "" );
    var b = y.replace( /<.*?>/g, "" );
    return ((a < b) ? 1 : ((a > b) ? -1 : 0));
    }
    } );
    
    $(document).ready(function() {
        $("#disksTable").dataTable( {
      "aaSorting" : [ [0, 'asc'], ],
      "aoColumns" : [
                { "sType": "num-html" },
                { "sType": "text-html" },
                { "sType": "text-html" },
                { "sType": "text-html" },
                { "sType": "text-html" },
                null,
                ]
      } );
    });
    
    $(document).ready(function() {
        $("#filesTable").dataTable( {
      "aaSorting" : [ [0, 'desc'], [2, 'desc' ], ],
      "aoColumns" : [
                { "sType": "text-html" },
                { "sType": "num-html" },
                { "sType": "text-html" },
                null,
                { "sType": "text-html" },
                { "sType": "text-html" },
                null,
                ]
      } );
    });

</script>
""")
		else:
			req.write("No unit with this barcode known!")
	except:
		req.write("This only works if a barcode tag is provided anything else is not implemented!")
	includehtml(req,"/var/www/drsus/footer.html")
	return apache.OK

def _notFound(req):
	return apache.HTTP_NOT_FOUND