# -*- coding: utf-8 -*-
from django.template import RequestContext
from django.shortcuts import render_to_response
from django.http import HttpResponse
from django.http import HttpResponseRedirect
from django.views.decorators.csrf import csrf_exempt
from models import Score
from django.contrib.auth.models import User
from models import Task
import MySQLdb

DB_USER = 'user'
DB_PASS = 'ckj;ysq'
DB_BASE = 'Decanat'
@csrf_exempt
def SQL(request):
	cont = {}
	tt={}

	if request.method == "POST":
		if 'sqlcode' in request.POST:
			t=request.POST['sqlcode']
			if t.lower() in ['delete', 'drop', 'alter', 'insert','update','deny','revoke','grant']:
				t = "SELECT * FROM wrong"
			if t.lower() in ['xmachina']:
				t = "SELECT * FROM code"


			vasia=t

			number = 2
			if '*' in t:
				number = 1

			db_conn = MySQLdb.connect(user=DB_USER, passwd=DB_PASS, db=DB_BASE)
			cursor = db_conn.cursor()
			'''cursor.execute(t)'''

			try:
				db_conn = MySQLdb.connect(user=DB_USER, passwd=DB_PASS, db=DB_BASE)
				cursor.execute(t)

			except:
				t = "SELECT * FROM wrong"
				db_conn = MySQLdb.connect(user=DB_USER, passwd=DB_PASS, db=DB_BASE)
				cursor = db_conn.cursor()
				#ursor = db_conn.cursor(cursorclass=MySQLdb.cursors.SSDictCursor)
				cursor.execute(t)
				res = cursor.fetchall()
				number =1

			else:
				res= cursor.fetchall()
			cursor.close
			db_conn.close

			#r=res.decode('utf-8', 'ignore')
			resdate =_formatt(res, vasia, number)
			res = unicode(res)
			#res=str(res)

			cont = resdate

	return HttpResponse(cont, mimetype="text/html; charset=utf-8")



def _formatt(a,vasia,number):
	if number == 1:
		res = u'<table border="1" bgcolor="FFFFFF">'



	if number == 2:
		res = u'<table border="1" bgcolor="FFFFFF"><tr>'
		start = []
		for w in vasia.lower().replace(",", "").split():
			if w == 'select': continue
			elif w == 'from': break
			else: start.append(w)

		for nn in start:
			res += u"<td>{0}</td>".format(nn)
		res+=u'</tr>'




	for i in a:
		res += u"<tr>"
		for y in i:

			#res += u"<td>{0}</td>".format(y)
			res+=u'<td>'
			y=unicode(y)
			res+=y.decode('utf-8', 'ignore')
			res+=u'</td>'

		res += u"</tr>"
	res += u"</table>"
	return res

@csrf_exempt
def RESULT(request):
	tt=0
	t=0
	result=u'<table border="1">'
	result+=u'<tr><td></td>'
	for tsk in Task.objects.all():
		result+=u'<td>'
		un=tsk.group
		un=unicode(un)
		un+=u": "
		br=u"<br>"
		result = u" ".join([result,un,br,tsk.taskname])
		result+=u'</td>'
	result+=u'<td>'
	result+=u'Сумма баллов</td>'

	result+=u'</tr>'

	for usr in User.objects.all():
		result+=u'<tr><td>'
		result=u" ".join([result,usr.last_name, usr.first_name])
		result+=u'</td>'

		for tsk in Task.objects.all():

			result+=u'<td>'
			for scr in Score.objects.filter(student=usr, task=tsk):
				t = scr.score
			tt+=t
			r=unicode(t)
			t=0
			result+=r
			result+=u'</td>'
		rr=unicode(tt)
		tt=0
		result+=u'<td>'
		result+=rr
		result+=u'</td>'
		result+=u'</tr>'

	result+=u'</table>'


	return HttpResponse(result, mimetype="text/html; charset=utf-8")