import csv
import hashlib
import datetime

import openpyxl.worksheet
import requests

import openpyxl

from flask import Flask
from flask import request
from flask import url_for
from flask import session
from flask import redirect
from flask import Blueprint
from flask import make_response
from flask import render_template
from flask import json
import flask_login
#from flask_login import login_user, login_required, logout_user

from . import *

# https://flask-login.readthedocs.io/en/latest/
# 

db = openDB()
cur = db.cursor()
user = None
userutv = None
org = None
HTTP_METHODS = ['GET', 'HEAD', 'POST', 'PUT', 'DELETE', 'CONNECT', 'OPTIONS', 'TRACE', 'PATCH']

app = Flask(__name__)
app.secret_key = 'GkvT9KKu45'

login_manager = flask_login.LoginManager()
login_manager.init_app(app)

class User(flask_login.UserMixin): ...

#
def exc_handler(msg):
    pass

#theme context dictionary
ctx = {}

"""
@app.before_request
def preload():
    cur = db.cursor()
    cur = 
"""

@login_manager.request_loader
@login_manager.user_loader
def user_loader(vv):
    global user
    global ctx
    user = User()
    ip = request.remote_addr
    print('IP', ip)
    user_hash = request.cookies.get('dthash')
    if (None != user_hash) & ('' != user_hash):
        cur = db.cursor()
        res = cur.execute('''
            SELECT DISTINCT
                "users"."users_id",
                "login",
                "fullname",
                "loghash",
                (SELECT group_concat("code", '|') FROM (SELECT "code" FROM "users_roles" LEFT JOIN "roles" ON "users_roles"."roles_id"="roles"."roles_id" WHERE "users_roles"."users_id"="users"."users_id" )) AS "acclevel"
            FROM
                "users"
                LEFT JOIN "users_roles" ON "users"."users_id"="users_roles"."users_id"
                LEFT JOIN "roles" ON "users_roles"."roles_id" = "roles"."roles_id"
            WHERE
                "loghash" = :hash''',
        {
            'hash' : user_hash.strip()
        })
        usr = res.fetchone()
        db.commit()
        if(usr == None):
            user = None
            session.clear()
            #Response.clear()
            s = session
            print(s.__dir__)
            r = make_response(redirect(url_for('login')))
            r.set_cookie("dthash", expires=0)
            print('r:',r)
            #return r
            ctx = {
                'luser' : usr
            }
        else:
            user.id = usr['users_id']
            user.fullname = usr['fullname']
            user.acclevel = usr['acclevel'].split('|')
            res = cur.execute('SELECT * FROM "users_roles" ')
            #user.
            #user.is_active = True
            flask_login.login_user(user,force=True)
            print('uzivLogged: ',vars(user))
    else:
        user = None
        #res = make_response(redirect(url_for('/login')))
        #res.set_cookie("dthash", expires=0)
    if None != user:
        cur.execute('''
            SELECT
                "name",
                "obd",
                "opened"
            FROM
                "org" LEFT JOIN
                "org_stav" ON "org"."org_id"="org_stav"."org_id"
            WHERE
                "org_stav"."active" = 1
            ORDER BY
                "obd" DESC
            LIMIT 1
        ''')
        ctx['org'] = cur.fetchone()
        cur.execute('''
            SELECT
                "utv"."utvar" AS "utvar",
                "utv"."utvart" AS "utvart",
                "utv_stav"."obd" AS "obd",
                "utv_stav"."opened" AS "opened"
            FROM
                "utv" LEFT JOIN "utv_stav" ON "utv"."utvar_id"="utv_stav"."utvar_id"
            WHERE
                "utv"."utvar" IN (
                    SELECT "utvar" FROM "user_utv" WHERE "id_users"=:id_users
                )
                AND "utv_stav"."obd" = :obd
        ''',{
            'id_users' : usr['users_id'],
            'obd' : ctx['org']['obd']
        })
        ctx['userutv'] = cur.fetchall()
    ctx['strftime'] = datetime.datetime.strftime
    ctx['strptime'] = datetime.datetime.strptime
    #print(ctx)
    #print('uu:',user)
    return user


@login_manager.unauthorized_handler
def unauthorized():
    if request.blueprint == 'api':
        abort(HTTPStatus.UNAUTHORIZED)
    return redirect(url_for('login'))

@app.route("/logout")
@flask_login.login_required
def logout():
    global user
    print('userl',vars(user)['id'])
    cur = db.cursor()
    res = cur.execute('''UPDATE "users" SET "loghash"=\'\' WHERE "users_id"=:uid''',
    {
        'uid' : vars(user)['id']
    })
    db.commit()
    r = make_response(redirect(url_for('login')))
    r.set_cookie('dthash','', expires=0)
    return r

@app.route("/login")
@app.route("/login", methods=['POST'])
def login():
    print('login')
    if ('POST' == request.method) & ('' != request.form.get('username')) & ('' != request.form.get('pwd')):
        print('login')
        pwd = hashlib.sha512((str(request.form.get('username').strip()).lower() + request.form.get('pwd').strip()).encode()).hexdigest()
        print('login',str(request.form.get('username').strip()).lower())
        print('heslo',pwd)
        cur = db.cursor()
        res = cur.execute('SELECT * FROM "users" WHERE LOWER("login") = :login AND "password" = :pwd',{
            'login' : request.form.get('username').strip().lower(),
            'pwd' : pwd
        })
        print(res)
        user = res.fetchone()
        print('uziv',user)
        if None != user :
            hash = hashlib.sha512(str(datetime.datetime.now().timestamp()).encode()).hexdigest()
            print('hash',hash)
            cur.execute('UPDATE "users" SET "loghash"=:hash WHERE LOWER("login") = :login AND "password" = :pwd',
            {
                'login' : request.form.get('username').strip().lower(),
                'pwd' : pwd,
                'hash' : hash
            } )
            db.commit()
            #request.cookies.set('dthash') = hash
            res = make_response(redirect(url_for('home')))
            res.set_cookie("dthash", value=hash)
            return res
    return render_template('login.html')


#@app.route("/admusers")
@app.route("/admusers", methods=HTTP_METHODS)
#@app.route("/admusers/", methods=HTTP_METHODS)
@flask_login.login_required
def admusers():
    global user
    print("admusers")
    ctx = {
        'akc' : 'list',
        'luser' : user
    }
    cur = db.cursor()
    if 'POST' == request.method:
        ctx['akc'] = 'edit'
        if (None == request.form.get('users_id')) | ('' == request.form.get('users_id')):
            print(request.form.get('login'))
            print(request.form.get('pwd'))
            request.form.get('uid')
            cur.execute('''INSERT INTO "users"("login","uid","fullname") VALUES (:login, :uid, :fullname)''',
            {
                'login' : request.form.get('login'),
                'uid' : request.form.get('uid'),
                'fullname' : request.form.get('fullname'),
            })
            edtId = cur.lastrowid
        else:
            edtId = int(request.form.get('users_id'))
            cur.execute('''
                UPDATE "users" SET
                    "uid" = :uid,
                    "login" = :login,
                    "fullname" = :fullname
                WHERE
                    "users_id" = :users_id
            ''',{
                'login' : request.form.get('login'),
                'uid' : request.form.get('uid'),
                'fullname' : request.form.get('fullname'),
                'users_id' : edtId
            } )
            pass #update
        db.commit()
        if (None != request.form.get('pwd')) & ('' != str(request.form.get('pwd')).strip()):
            cur.execute('''
                UPDATE "users" SET
                    "password" = :password,
                    "oauth" = :oauth
                WHERE
                    "users_id" = :users_id
            ''' , {
                'password' : hashlib.sha512((str(request.form.get('login')).lower() + request.form.get('pwd')).encode()).hexdigest(),
                'oauth' : hashlib.sha512((str(request.form.get('uid')) + request.form.get('login').lower() + request.form.get('pwd')).encode()).hexdigest(),
                'users_id' : edtId
            } )
        acc = request.form.getlist('acclevel')
        print(acc)
        cur.execute('DELETE FROM "users_roles" WHERE "users_id"=:users_id',{
            'users_id' : edtId
        })
        for roleAcc in acc:
            cur.execute('INSERT INTO "users_roles"("users_id","roles_id") VALUES (:users_id, :roles_id)',{
                'users_id' : edtId,
                'roles_id' : roleAcc
            })
        db.commit()
        return make_response(redirect(url_for('admusers',  edit=str(edtId))))
    elif 'GET' == request.method:
        print('get')
        if (None != request.args.get('edit', type = str)):
            ctx['akc'] = 'edit'
            cur.execute('''
                SELECT
                    "users_id",
                    "login",
                    "uid",
                    "fullname", 
                    (SELECT group_concat("roles_id", ',') FROM (SELECT "roles"."roles_id" FROM "users_roles" LEFT JOIN "roles" ON "users_roles"."roles_id"="roles"."roles_id" WHERE "users_roles"."users_id"="users"."users_id" )) AS "acclevel"
                FROM
                    "users"
                WHERE
                    "users_id"=:users_id
                LIMIT 1
            ''',
            {
                'users_id' : request.args.get('edit', type = int)
            } )
            ctx['euser'] = cur.fetchone()
            print('ctx',ctx)
            ctx['acclevel'] = [int(x) for x in ctx['euser']['acclevel'].split(',')]
        elif (None != request.args.get('delete', type = str)):
            print('delete:', request.args.get('delete', type = int))
            idu = {
                'users_id' : request.args.get('delete', type = int)
            }
            print('idu',idu)
            cur.execute('DELETE FROM "user_utv" WHERE "users_id"=:users_id', idu)
            cur.execute('DELETE FROM "users_roles" WHERE "users_id=:users_id"', idu)
            cur.execute('DELETE FROM "users" WHERE "users_id"=:users_id', idu)
            db.commit()
        elif (None != request.args.get('add', type = str)):
            ctx['akc'] = 'edit'
            ctx['euser'] = {}
    cur.execute('''
        SELECT
            "users_id",
            "login",
            "uid",
            "fullname", 
            (SELECT group_concat("title", ',') FROM (SELECT "title" FROM "users_roles" LEFT JOIN "roles" ON "users_roles"."roles_id"="roles"."roles_id" WHERE "users_roles"."users_id"="users"."users_id" )) AS "acclevel"
        FROM
            "users"
        ORDER BY
            "uid"
        ''')
    ctx['ulist'] = cur.fetchall()
    db.commit()
    if('edit' == ctx['akc']):
        cur.execute('''
            SELECT
                "roles_id",
                "title"
            FROM
                "roles"
            ORDER BY
                "title"
        ''')
    ctx['roles'] = cur.fetchall()
    db.commit()
    print('ctx',ctx)
    return render_template('admusers.html', **ctx)

@app.route("/")
@app.route("/", methods=HTTP_METHODS)
@flask_login.login_required
def home():
    print("home")
    cur = db.cursor()
    ctx['akc'] = 'list'
    if 'HMU' in user.acclevel:
        cur.execute('''
            SELECT
                "utv"."utvar_id" AS "utvar_id",
                "utv"."utvar" AS "utvar",
                "utv"."utvart" AS "utvart",
                "utv_stav"."obd" AS "obd",
                "utv_stav"."opened" AS "opened"
            FROM
                "utv_stav" LEFT JOIN "utv" ON "utv"."utvar_id"="utv_stav"."utvar_id"
            WHERE
                "utv_stav"."obd" = :obd
        ''',{
            'obd' : ctx['org']['obd']
        })
    ctx['utvstav'] = cur.fetchall()
    print('rr',user.acclevel)
    print('ri',user.id)
    print('op',ctx['org']['opened'])
    return render_template('home.html', **ctx)
    # return('OK')

@app.route('/user', methods=['POST'])
@flask_login.login_required
def user():
    pass

#@app.route('/f50')
@app.route('/f50', methods=HTTP_METHODS)
@flask_login.login_required
def f50():
    usr = vars(user)
    db.row_factory = sqlite3.Row
    ctx = {
        'akc' : 'list',
        'filter' : None
    }
    cur = db.cursor()
    if 'POST' == request.method:
        pass
    cur.execute(
        '''SELECT
            * 
        FROM
            "prac"
        WHERE 
            "utvar" IN (
                SELECT
                    "utvar"
                FROM
                    "user_utv"
                WHERE
                    "id_users" = :users_id
            )
        ORDER BY
            "oscis","cicin"
        ''',{
            'users_id' : usr['id']
        } )
    ctx['plist'] = cur.fetchall()
    cur.execute('''
        SELECT
            *
        FROM
            "operace"
        WHERE
            "utvar_id" IN (
                SELECT
                    "utv"."utvar_id"
                FROM
                    "user_utv"
					LEFT JOIN "utv" ON "utv"."utvar"="user_utv"."utvar"
                WHERE
                    "user_utv"."id_users" = :users_id
            )
        ORDER BY
            "operace_id"
    ''',{
            'users_id' : usr['id']
    } )
    ctx['ops'] = cur.fetchall()
    cur.execute('''
        SELECT
            *
        FROM
            "operace"
        WHERE
            "utvar_id" IN (
                SELECT
                    "utv"."utvar_id"
                FROM
                    "user_utv"
					LEFT JOIN "utv" ON "utv"."utvar"="user_utv"."utvar"
                WHERE
                    "user_utv"."id_users" = :users_id
            )
        ORDER BY
            "operace_id"
    ''',{
            'users_id' : usr['id']
    } )
    d = []
    for r in cur.fetchall():
        d.append(
            {
                'operace_id' : r['operace_id'],
                'title' : r['operacet'],
                'normocas' : r['normocas'],
                'tarifjed' : r['tarifjed'],
                'prostredi' : r['prostredi']
            }
        )
    ctx['oplist'] = json.dumps(d)
    return render_template('f50.html', **ctx)



@app.route('/tutv', methods=HTTP_METHODS)
@flask_login.login_required
def tutv():
    global user
    ctx = {
        "akc" : 'list',
        "luser" : user
    }
    cur = db.cursor()
    if request.method == 'POST':
        if request.files:
            uploaded_file = request.files['impfile'] # This line uses the same variable and worked fine
            fn = os.path.join(tmpDir, uploaded_file.filename)
            uploaded_file.save(os.path.join(tmpDir, uploaded_file.filename))
            wb = openpyxl.load_workbook(fn)
            ws = wb['tklcx']
            #colnames
            ColNames = {}
            Current  = 0
            for COL in ws.iter_cols(1, ws.max_column):
                ColNames[COL[0].value] = Current
                Current += 1
            #rows
            for rows in ws.iter_rows(min_row=2):
                print(rows[ColNames['utvar']].value)
                cur.execute('''
                    INSERT INTO "utv"("utvar","zapl","kopl","utvart")
                    VALUES(:utvar, :zapl, :kopl, :utvart)
                    ON CONFLICT ("utvar")
                    DO
                        UPDATE SET
                            "zapl" = :zapl,
                            "kopl" = :kopl,
                            "utvart" = :utvart
                        WHERE
                            "utvar" = :utvar
                ''', {
                    'utvar' : rows[ColNames['pracv']].value,
                    'zapl' : None,
                    'kopl' : None,
                    'utvart' : rows[ColNames['pracvt']].value
                },)
            db.commit()
            os.remove(fn)
    elif 'GET' == request.method:
        if (None != request.args.get('delete', type = str)):    
            cur.execute('DELETE FROM "utv" WHERE "utvar_id"=:kod', {
                'kod' : request.args.get('delete', type = int)
            } )
            db.commit()
    cur.execute('''SELECT "utvar_id","utvar","utvart" FROM "utv" ORDER BY "utvar"''')
    ctx["tutv"] = cur.fetchall()
    return render_template('tutv.html', **ctx)

@app.route('/todm', methods=HTTP_METHODS)
@flask_login.login_required
def todm():
    ctx = {
        "akc" : 'list'
    }
    cur = db.cursor()
    if request.method == 'POST':
        if request.files:
            uploaded_file = request.files['impfile'] # This line uses the same variable and worked fine
            fn = os.path.join(tmpDir, uploaded_file.filename)
            uploaded_file.save(os.path.join(tmpDir, uploaded_file.filename))
            wb = openpyxl.load_workbook(fn)
            ws = wb['todm']
            #colnames
            ColNames = {}
            Current  = 0
            for COL in ws.iter_cols(1, ws.max_column):
                ColNames[COL[0].value] = Current
                Current += 1
            #rows
            for rows in ws.iter_rows(min_row=2):
                cur.execute('''
                    INSERT INTO "todm"("kod","zapl","kopl","sazba","sazbap","kodt")
                    VALUES(:kod, :zapl, :kopl, :sazba, :sazbap, :kodt)
                    ON CONFLICT ("kod")
                    DO
                        UPDATE SET
                            "zapl" = :zapl,
                            "kopl" = :kopl,
                            "sazba" = :sazba,
                            "sazbap" = :sazbap,
                            "kodt" = :kodt
                        WHERE
                            "kod" = :kod
                ''', {
                    'kod' : rows[ColNames['kod']].value,
                    'zapl' : rows[ColNames['zapl']].value,
                    'kopl' : rows[ColNames['kopl']].value,
                    'sazba' : rows[ColNames['sazba']].value,
                    'sazbap' : rows[ColNames['sazbap']].value,
                    'kodt' : rows[ColNames['kodt']].value
                })
            db.commit()
            os.remove(fn)
    elif 'GET' == request.method:
        if (None != request.args.get('delete', type = str)):    
            cur.execute('DELETE FROM "todm" WHERE "kod"=:kod', {
                'kod' : request.args.get('delete', type = int)
            } )
            db.commit()
    cur.execute('''SELECT "kod","sazba","sazbap","kodt" FROM "todm" ORDER BY "kod"''')
    ctx["todm"] = cur.fetchall()
    return render_template('todm.html', **ctx)

@app.route('/ukony', methods=HTTP_METHODS)
@flask_login.login_required
def ukony():
    usr = vars(user)
    cur = db.cursor()
    ctx = {
        'akc' : 'list'
    }
    if None != request.args.get('utv', type = int):
        cur.execute('''
            SELECT group_concat("utvar", '|')  AS "utvary" FROM (SELECT
                "user_utv"."utvar" As "utvar"
            FROM
                "user_utv"
                LEFT JOIN "utv" ON "user_utv"."utvar"="utv"."utvar"
            WHERE
                "user_utv"."id_users" = :users_id
            ORDER BY
                "utv"."utvar")
        ''', {
            'users_id' : usr['id']
        })
        res = cur.fetchone()
        if None == res['utvary']:
            userutv = {}
        else:    
            userutv = res['utvary'].strip().split('|')
        print('utvary prihl. uzivatele: ', userutv)
        if ('ADMIN' in usr['acclevel']) or (request.args.get('utv', type = int) in userutv):
            ctx['utv'] = request.args.get('utv', type = int)
    else:
        return make_response(redirect(url_for('/')))
    if 'POST' == request.method:
        if request.files:
            utv = request.args.get('utv', type = int)
            uploaded_file = request.files['impfile'] # This line uses the same variable and worked fine
            fn = os.path.join(tmpDir, uploaded_file.filename)
            uploaded_file.save(os.path.join(tmpDir, uploaded_file.filename))
            fr = csv.reader(open(fn,"r", encoding='IBM852'), delimiter=',',quotechar='\'')
            next(fr,None)
            for line in fr:
                print(line)
                cur.execute('''
                    INSERT INTO "operace"(
                        "operace_id","utvar_id","normocas","tarifjed","prostredi","operacet", "kododm", "genodm"
                    ) VALUES (
                        :operace_id,:utvar_id, :normocas, :tarifjed, :prostredi, :operacet, 10, 0
                    ) ON CONFLICT ("operace_id","utvar_id") DO UPDATE SET
                        "normocas" = :normocas,
                        "tarifjed" = :tarifjed,
                        "prostredi" = :prostredi,
                        "operacet" = operacet,
                        "kododm" = 10,
                        "genodm" = NULL
                    ''',{
                        'utvar_id' : utv,
                        'operace_id' : line[0],
                        'normocas' : float(line[1]),
                        'tarifjed' : float(line[2]),
                        'prostredi' : float(line[3]),
                        "operacet" : line[7].replace('\\,',', ').replace(';','')
                    }
                )
            db.commit()
            os.remove(fn)
        elif None != request.form.get('operace_id', type=int):
            a = {
                'operacet' : request.form.get('operacet', type=str),
                'operace_id' : request.form.get('operace_id', type=int),
                'utvar_id' : request.form.get('utvar_id', type=int),
                'normocas': request.form.get('normocas', type=float),
                'tarifjed' : request.form.get('tarifjed', type=float),
                'prostredi' : request.form.get('prostredi', type=float),
                'kododm' : request.form.get('kododm',type=int)
            }
            if('null' == request.form.get('genodm', type=str).lower()):
                a['genodm'] = None
            else:
                a['genodm'] = request.form.get('genodm', type=int)
            cur.execute('''
                UPDATE "operace" SET
                    "operacet" = :operacet,
                    "normocas" = :normocas,
                    "tarifjed" = :tarifjed,
                    "prostredi" = :prostredi,
                    "kododm" = :kododm,
                    "genodm" = :genodm
                WHERE
                    "operace_id" = :operace_id
                    AND "utvar_id" = :utvar_id
            ''', a)
            db.commit()
            return make_response(redirect(url_for('ukony', edit=request.form.get('operace_id', type=int), utv=request.form.get('utvar_id', type=int))))
    elif None != request.args.get('delete', type=int):
        cur.execute('''DELETE FROM "operace" WHERE "utvar_id"=:utvar_id AND "operace_id"=:operace_id''',{
            'utvar_id' : ctx['utv'],
            'operace_id' : request.args.get('delete', type=int)
        })
    elif None != request.args.get('edit', type=int):
        cur.execute('''SELECT "operacet", "normocas", "tarifjed", "prostredi", "operace_id", "utvar_id", "kododm", "genodm" FROM "operace" WHERE "utvar_id"=:utvar_id AND "operace_id"=:operace_id''',{
            'utvar_id' : request.args.get('utv', type = int),
            'operace_id' : request.args.get('edit', type=int)
        })
        ctx['eop'] = cur.fetchone()
        print(ctx)
        cur.execute('''SELECT "kod", "kodt" FROM "todm" ORDER BY "kod"''')
        ctx['odmlist'] = cur.fetchall()
        db.commit()
        ctx['akc'] = 'edit'
    if 'list' == ctx['akc']:
        cur.execute('''SELECT "operacet", "normocas", "tarifjed", "prostredi", "operace_id", "utvar_id" FROM "operace" WHERE "utvar_id"=:utvar_id ORDER BY "operace_id"''',{
            'utvar_id' : request.args.get('utv', type = int)
        })
        ctx['oplist'] = cur.fetchall()
    return render_template('ukony.html', **ctx)

@app.route('/userutv', methods=HTTP_METHODS)
@flask_login.login_required
def userutv():
    ctx = {
        'akc' : "list"
    }
    cur = db.cursor()
    if 'POST' == request.method:
        if (None != request.form.get('euser')) and (None != request.form.get('utvar')):
            cur.execute('''INSERT INTO "user_utv"("id_users","utvar") VALUES (:id_users,:utvar)''',{
                'id_users' : request.form.get('euser', type=int),
                'utvar' : request.form.get('utvar', type=int)
            })
            db.commit()    
        ctx['euser'] = request.args.get('euser', type = int)
        return make_response(redirect(url_for('userutv',  euser=str(ctx['euser']))))
    elif 'GET' == request.method:
        if None != request.args.get('delete', type = int) and (None != request.args.get('euser', type = int)):
            cur.execute('DELETE FROM "user_utv" WHERE "id_users"=:user_id AND "utvar"=:utvar',{
                'user_id' : request.args.get('euser', type = int),
                'utvar' : request.args.get('delete', type = int)
            })
            db.commit()
        ctx['euser'] = request.args.get('euser', type = int)
        if 0 >= ctx['euser']:
            return redirect(url_for('/users'))    
    else:
        return redirect(url_for('/users'))
    cur.execute('''SELECT "fullname" FROM "users" WHERE "users_id"=:users_id''',{
        'users_id' : ctx['euser']
    })
    ctx['eusername'] = cur.fetchone
    uselect = {
        'users_id' : ctx['euser']
    }
    cur.execute('''
        SELECT
            "utvar_id",
            "utvar",
            "utvart"
        FROM
            "utv"
        WHERE
            "utv" NOT IN (
                SELECT "utv" FROM "user_utv" WHERE "user_utv"."utvar" = "utv"."utvar"  AND "user_utv"."id_users"= :users_id
            )
        ORDER BY
            "utvar"''',uselect)
    ctx["tutv"] = cur.fetchall()
    cur.execute('''
        SELECT
            "user_utv"."utvar",
            "utv"."utvart"
        FROM
            "user_utv"
            LEFT JOIN "utv" ON "user_utv"."utvar"="utv"."utvar"
        WHERE
            "user_utv"."id_users" = :users_id
        ORDER BY
            "utv"."utvar"
    ''', uselect)
    ctx["selutv"] = cur.fetchall()
    db.commit()
    return render_template('userutv.html', **ctx)
    

@app.route('/tprac', methods=HTTP_METHODS)
@flask_login.login_required
def tprac():
    cur = db.cursor()
    ctx = {
        'akc' : 'list'
    }
    if request.method == 'POST':
        if request.files:
            uploaded_file = request.files['impfile'] # This line uses the same variable and worked fine
            fn = os.path.join(tmpDir, uploaded_file.filename)
            uploaded_file.save(os.path.join(tmpDir, uploaded_file.filename))
            wb = openpyxl.load_workbook(fn)
            ws = wb['f5y']
            #colnames
            ColNames = {}
            Current  = 0
            for COL in ws.iter_cols(1, ws.max_column):
                ColNames[COL[0].value] = Current
                Current += 1
            #rows
            for rows in ws.iter_rows(min_row=2):
                print(rows[ColNames['pracv']].value)
                cur.execute('''
                    INSERT INTO "prac"("oscis","cicin","utvar","prijm","tarif")
                    VALUES(:oscis,:cicin,:utvar,:prijm,:tarif)
                    ON CONFLICT ("oscis","cicin")
                    DO
                        UPDATE SET
                            "utvar" = :utvar,
                            "prijm" = :prijm,
                            "tarif" = :tarif
                ''', {
                    'oscis' : rows[ColNames['oscis']].value,
                    'cicin' : rows[ColNames['cicin']].value,
                    'utvar' : rows[ColNames['pracv']].value,
                    'prijm' : rows[ColNames['prijm']].value,
                    'tarif' : float(str(rows[ColNames['cicin']].value).replace(',','.'))
                })
            db.commit()
            os.remove(fn)
    cur.execute('''
        SELECT
            *
        FROM
            "prac"           
        ORDER BY
            "utvar","oscis","cicin"
    ''')
    ctx["plist"] = cur.fetchall()
    db.commit()
    return render_template('tprac.html', **ctx)

@app.route('/error')
def error():
    ctx = {
        'msg' : request.args['messages'],
        'msg_ses' : session['messages']
    }
    msg = ''
    return render_template('error.html', **ctx)

if __name__ == "__main__":
    app.run(host='0.0.0.0', port=8080, debug=False)