#! /usr/bin/env python3 import os import sys import hashlib import sqlite3 class cInstDTOdm: db = None appDir = None def __init__(self): self.appDir = os.path.dirname(os.path.realpath(__file__)) #print(self.appDir) def run(self): if os.path.isfile(os.path.join(self.appDir,'odm.sqlite')): self.updateDB() else: self.createDB() def __del__(self): self.db.close() def createDB(self): print('CreateDB') self.db = sqlite3.connect('odm.sqlite') cur = self.db.cursor() """ Create table "users" and INSERT admin """ cur.execute(''' CREATE TABLE "users"( "users_id" INTEGER PRIMARY KEY AUTOINCREMENT, "uid" INTEGER, "login" VARCHAR(50), "password" CHARACTER(128) DEFAULT \'\', "oauth" CHARACTER(128) DEFAULT \'\', "fullname" VARCHAR(255), "loghash" CHARACTER(128) DEFAULT \'\' ) ''') self.db.commit() rootUser = self.getDefaultUser() cur.execute('''INSERT INTO "users" ("uid","login","password","oauth", "fullname") VALUES (:uid, :login,:password,:oauth, :login)''', rootUser ) users_id = cur.lastrowid self.db.commit() cur.execute('CREATE INDEX "idx_user_uid" ON "users"("id_users")') cur.execute('CREATE INDEX "idx_user_loghash" ON "users"("loghash")') self.db.commit() cur.execute(''' CREATE TABLE "roles"( "roles_id" INTEGER PRIMARY KEY AUTOINCREMENT, "code" VARCHAR(10), "title" VARCHAR(150) ) ''') self.db.commit() cur.execute('INSERT INTO "roles"("code","title") VALUES(\'ADMIN\',\'Správce\')') cur.execute('INSERT INTO "roles"("code","title") VALUES(\'HMU\',\'Hlavní mzdová účetní\')') cur.execute('INSERT INTO "roles"("code","title") VALUES(\'LEAD\',\'Vedoucí pobočky/dílny\')') cur.execute('INSERT INTO "roles"("code","title") VALUES(\'PRAC\',\'Pracovník\')') self.db.commit() cur.execute('CREATE INDEX "idx_roles_code" ON "roles"("code")') self.db.commit() cur.execute(''' CREATE TABLE "users_roles"( "users_id" INTEGER NOT NULL, "roles_id" INTEGER NOT NULL ) ''') self.db.commit() cur.execute('CREATE INDEX "idx_ru_users" ON "users_roles"("users_id")') cur.execute('CREATE INDEX "idx_ru_roles" ON "users_roles"("roles_id")') self.db.commit() cur.execute('''INSERT INTO "users_roles"("users_id","roles_id") VALUES(:users_id, (SELECT "roles_id" FROM "roles" WHERE "code"=\'ADMIN\' LIMIT 1) )''', { 'users_id' : users_id } ) """ Create tables tutv + interconnect to user """ cur.execute(''' CREATE TABLE "utv"( "utvar_id" INTEGER PRIMARY KEY AUTOINCREMENT, "utvar" INTEGER, "zapl" CHARACTER(10), "kopl" CHARACTER(10), "utvart" VARCHAR(120) ) ''') self.db.commit() cur.execute('CREATE UNIQUE INDEX "unq_utv_utvar" ON "utv" ("utvar")') cur.execute('CREATE INDEX "idx_utv_utvar" ON "utv"("utvar")') self.db.commit() cur.execute(''' CREATE TABLE "utv_stav"( "utvar_id" INTEGER, "obd" CHAR(7), "opened" INT DEFAULT 1 ) ''') cur.execute('CREATE UNIQUE INDEX "unq_utv_stav("utvar_id","obd")"') self.db.commit() cur.execute(''' CREATE TABLE "user_utv"( "id_users" INTEGER NOT NULL, "utvar" INTEGER NOT NULL ) ''') self.db.commit() cur.execute('CREATE INDEX "idx_user_utv_user" ON "user_utv"("id_users")') cur.execute('CREATE INDEX "idx_user_utv_utvar" ON "user_utv"("utvar")') cur.execute('CREATE UNIQUE INDEX "unq_user_utvar" ON "user_utv"("id_users","utvar")') self.db.commit() """ create table prac (list of prac) """ cur.execute(''' CREATE TABLE "prac"( "oscis" INTEGER NOT NULL, "cicin" INTEGER DEFAULT 0, "utvar" INTEGER, "prijm" VARCHAR(255), "tarif" float ) ''') self.db.commit() cur.execute('CREATE INDEX "idx_prac_utvar" ON "prac"("utvar")') cur.execute('CREATE UNIQUE INDEX "unq_prac_oscis_cicin" ON "prac"("oscis","cicin")') self.db.commit() """ create table todm (list of odm) """ cur.execute(''' CREATE TABLE "todm"( "kod" INTEGER NOT NULL, "zapl" CHARACTER(10), "kopl" CHARACTER(10), "sazba" FLOAT, "sazbap" FLOAT, "kodt" VARCHAR(50) ) ''') self.db.commit() cur.execute('CREATE UNIQUE INDEX "unq_todm_kod" ON "todm" ("kod")') cur.execute('CREATE INDEX "idx_todm_kod" ON "todm"("kod")') cur.execute('CREATE INDEX "idx_todm_zapl" ON "todm"("zapl")') cur.execute('CREATE INDEX "idx_todm_kopl" ON "todm"("kopl")') cur.execute('CREATE INDEX "idx_todm_zako" ON "todm"("zapl","kopl")') self.db.commit() cur.execute(''' CREATE TABLE "org"( "org_id" INTEGER PRIMARY KEY, "name" VARCHAR(100) ) ''') self.db.commit() cur.execute(''' CREATE TABLE "org_stav"( "org_id" INTEGER, "obd" CHAR(7), "opened" INTEGER DEFAULT 1 ) ''') cur.execute('CREATE UNIQUE INDEX "unq_org_stav" ON "org_stav" ("org_id","obd")') self.db.commit() cur.execute(''' CREATE TABLE "f50"( "oscis" INTEGER NOT NULL, "cicin" INTEGER NOT NULL, "obdz" CHAR(7), "kod" INTEGER NOT NULL, "odmph" FLOAT, "odmcas" FLOAT, "odmp" FLOAT, "odmnj" FLOAT ) ''') self.db.commit() cur.execute('CREATE INDEX "idx_f50_oscis" ON "f50"("oscis")') cur.execute('CREATE INDEX "idx_f50_obdz" ON "f50"("obdz")') cur.execute(''' CREATE TABLE "operace"( "operace_id" INTEGER NOT NULL, "utvar_id" INTEGER NOT NULL, "normocas" FLOAT, "tarifjed" FLOAT, "prostredi" FLOAT, "operacet" VARCHAR(150), "kododm" INTEGER NOT NULL DEFAULT 10, "genodm" INTEGER DEFAULT NULL ) ''') self.db.commit() cur.execute('CREATE UNIQUE INDEX "unq_operace_id_kod" ON "operace"("operace_id","utvar_id")') cur.execute('CREATE INDEX "idx_operace_utvar" ON "operace"("utvar_id")') cur.execute('CREATE INDEX "idx_operace_id" ON "operace"("operace_id")') self.db.commit() def updateDB(self): self.db = sqlite3.connect('odm.sqlite') cur = self.db.cursor() cur2 = self.db.cursor() print('UpdateDB') #cur.execute('DROP INDEX "idx_org_active"') #cur.execute('ALTER TABLE "org" DROP COLUMN "active"') #cur.execute('ALTER TABLE "org_stav" ADD COLUMN "active" INTEGER' ) #cur.execute('UPDATE "org_stav" SET "active"=1') #cur.execute('CREATE INDEX "idx_org_stav_active" ON "org_stav"("active")') cur.execute('''DELETE FROM "org_stav"''') cur.execute('''DELETE FROM "utv_stav"''') cur.execute(''' INSERT INTO "main"."org_stav" ("org_id", "obd", "opened", "active") VALUES (1, \'2024.04.01\', 0, 1); ''') cur.execute(''' INSERT INTO "main"."org_stav" ("org_id", "obd", "opened", "active") VALUES (1, \'2024.05.01\', 1, 1); ''') cur.execute(''' INSERT INTO "utv_stav"("utvar_id","obd","opened") SELECT "utv"."utvar_id" AS "utvar_id", "org_stav"."obd" AS "obd", "org_stav"."opened" AS "opened" FROM "utv", "org_stav" ''') self.db.commit() def getDefaultUser(self): user = { 'uid' : 0, 'login' : '', 'password' : '' } while True: print('Přihlašovací jméno správce: ') try: user['login'] = input() user['login'] = user['login'].strip().lower() if '' != user['login']: break except: print('Neplatný vstup!') while True: print('Heslo správce: ') try: user['password'] = input() user['password'] = user['password'].strip() if '' != user['password']: break except: print('Neplatný vstup!') user['oauth'] = hashlib.sha512((str(user['uid']) + user['login'].lower() + user['password']).encode()).hexdigest() user['login'] = user['login'].lower() user['password'] = hashlib.sha512((str(user['login']).lower() + user['password']).encode()).hexdigest() print(type(user['password']),':::',user['password']) #sys.exit(0) return user if '__main__' == __name__: inst = cInstDTOdm() inst.run() else: print('This is program, not library') sys.exit(1)