import re, os, string, datetime
import MySQLdb
import cherrypy
import openpyxl
from jinja2 import Environment, FileSystemLoader

env = Environment(loader=FileSystemLoader('templates'))



class GlobalConfig(object):
        cherrypy.config.update({'server.socket_host':'0.0.0.0',
                                'server.socket_port':8080})

class Main(object):
    global userName
    global dataBase
    global permID
    dataBase = None
    userName = None
    permID = None
    global query
    global db
    db = MySQLdb.connect(host='localhost',
                        user='root',
                        passwd='0000',
                        db='obort')
    query = db.cursor()


    def reconnect(self):
        global db
        global query
        if dataBase is None:
            db = MySQLdb.connect(host='localhost',
                                 user='root',
                                 passwd='0000',
                                 db='obort')
            query = db.cursor()
            return False
        else:
            db = MySQLdb.connect(host='localhost',
                                 user='root',
                                 passwd='0000',
                                 db=dataBase)
            query = db.cursor()
            return True

    @cherrypy.expose
    def index(self):
        if self.SessionChecker():
            raise cherrypy.HTTPRedirect("/main")
        tmpl = env.get_template('login.html')
        return tmpl.render()


## Main

    @cherrypy.expose
    def main(self):
        if self.SessionChecker():
            if self.reconnect():
                tmpl = env.get_template('index.html')
                permID = self.sql("SELECT `permID` FROM `general` WHERE `username`='"+userName+"'")
                permID = permID[0][0]
                cherrypy.session['permID'] = permID
                # Group Users
                print (" Username = " + userName)
                print ( " Perm  =  "  + permID)
                # Group Users
                if permID == '1':
                    companies = self.sql("SELECT `name` FROM `companies`")
                    return tmpl.render(username=userName, dbID=dataBase, companies=companies)


                # Group Admin
                if permID == '2':
                    pass
        else:
            tmpl = env.get_template("login.html")
            return tmpl.render()



    def sql(self,i):
        print(i)
        query.execute(i)
        data = query.fetchall()
        #data = list(data)
        print(data)
        print(type(data))
        return data



## Modules :

    #Add company
    @cherrypy.expose
    def add_company(self, naziv, opis):
        print(opis, naziv)
        #Create Table :P
        a = query.execute("CREATE TABLE `"+naziv+str(datetime.datetime.now().year)+"` ( `id` int(11) NOT NULL, `prd_jib` double NOT NULL, `prd_naziv` text NOT NULL, `prd_adresa` text NOT NULL, `prd_djelatnost` text NOT NULL, `prd_telefon` text NOT NULL, `prd_email` text NOT NULL, `ppo_jib` double NOT NULL, `ppo_ime` text NOT NULL, `ppo_add` text NOT NULL, `ppo_tel` text NOT NULL, `ppo_email` text NOT NULL )")
        d = query.execute("INSERT INTO `"+str(dataBase)+"`.`companies`(`name`, `logoID`, `opis`) VALUES ('"+naziv+"', NULL,NULL)")
        if a:
            b
        db.commit()
        return "success"
    #Insert - if ya kn0w wh4t i mean...
    #query.execute("INSERT INTO `"+naziv+datetime.datetime.now().year+"`(`id`, `prd_jib`, `prd_naziv`, `prd_adresa`, `prd_djelatnost`, `prd_telefon`, `prd_email`, `ppo_jib`, `ppo_ime`, `ppo_add`, `ppo_tel`, `ppo_email`) VALUES ([value-1],[value-2],[value-3],[value-4],[value-5],[value-6],[value-7],[value-8],[value-9],[value-10],[value-11],[value-12])")







    #Add user
    @cherrypy.expose
    def add_user(self,username, password, email, permsID, imep, tel):
        print("Add user function...")
        username = username
        password = password
        email = email
        permsID = permsID
        imep = imep
        tel = tel
        #print("INSERT INTO `general`(`username`, `password`, `email`, `permID`, `imep`, `tel`) VALUES ('"+username+"', '"+password+"', '"+email+"', '"+permsID+"', '"+imep+"', '"+tel+"')")
        #print("INSERT INTO `obort`.`users`(`username`, `password`, `dbID`, `uLimit`,