def export_tables():
timestamp = datetime.now(tz=TIMEZONE).strftime("%Y%m%d.%H%M%S")
db = get_db()
all_tables = [
"t_lietotaji", "t_biroji", "t_pilsetas", "t_projekti", "t_pozicijas",
"t_vienumi", "t_ieraksti", "t_komentari", "t_darbibas",
"t_kategorijas", "t_razotaji"
]
directory_name = "backup_" + timestamp
directory = os.path.join(current_app.config['BACKUP_FOLDER'],
directory_name)
os.mkdir(directory)
print(directory)
for table in all_tables:
print(">> Table: {}".format(table))
query = "SELECT * FROM {}".format(table)
filename = "{}_{}.csv".format(timestamp, table)
file_path = os.path.join(directory, filename)
df = pd.read_sql_query(query, db)
csv = df.to_csv(index=False)
print(csv)
file = open(file_path, 'w', encoding="utf-16")
file.write(csv)
file.close()
flash("File located at: {}".format(file_path))
def tables():
if request.method == 'POST':
timestamp = datetime.now(tz=TIMEZONE).strftime("%Y%m%d.%H%M%S")
db = get_db()
table = request.form['table']
if table == "all":
html = get_all_tables()
elif table == "export_csv":
export_tables()
return render_template("track/tables.html")
elif table == "backup":
backup_db()
return render_template("track/tables.html")
else:
df = get_table(table)
html = df.to_html(index=False)
html = TABLE_STYLE + "\n" + html
filename = "tables/tabula-{}.html".format(table)
full_path = os.path.join(current_app.config['REPORTING_FOLDER'],
filename)
file_path = os.path.join('trakeris/templates/', full_path)
file = open(file_path, 'w', encoding="utf-8", errors='ignore')
file.write(html)
file.close()
flash("File located at: {}".format(os.path.abspath(full_path)))
return render_template(full_path)
return render_template("track/tables.html")
def view(item_id):
db = get_db()
item = get_item(item_id)
comments = get_comments(item_id)
history = get_history(item_id)
if request.method == 'POST':
komentars = request.form['komentars']
liet_id = g.user['liet_id']
error = None
if komentars is None:
error = "Komentārs ir tukšs"
if error is not None:
flash(error)
else:
db.execute(
'''INSERT INTO t_komentari(
komentars,
vienum_id,
liet_id)
VALUES (?, ?, ?)''', (
komentars,
item_id,
liet_id,
))
db.commit()
return redirect(url_for("track.view", item_id=item_id))
return render_template("track/view.html",
item=item,
comments=comments,
history=history)
def queries():
db = get_db()
t_lietotaji = db.execute('''SELECT liet_id, lietv
FROM t_lietotaji''').fetchall()
t_projekti = db.execute('''SELECT proj_id, projekts
FROM t_projekti''').fetchall()
t_razotaji = db.execute('''SELECT razot_id, razotajs
FROM t_razotaji''').fetchall()
t_biroji = db.execute('''SELECT biroj_id, birojs
FROM t_biroji''').fetchall()
darb_sk = db.execute('''SELECT count(liet_id) AS 'darb_sk'
FROM t_lietotaji''').fetchone()
darb_sk = darb_sk['darb_sk']
vienum_sk = db.execute('''SELECT count(vienum_id) AS 'vienum_sk'
FROM t_vienumi''').fetchone()
vienum_sk = vienum_sk['vienum_sk']
proj_sk = db.execute('''SELECT count(proj_id) AS 'proj_sk'
FROM t_projekti''').fetchone()
proj_sk = proj_sk['proj_sk']
briv_vienum_sk = db.execute('''SELECT count(vienum_id) AS 'briv_vienum_sk'
FROM t_vienumi
WHERE liet_id == '';''').fetchone()
briv_vienum_sk = briv_vienum_sk['briv_vienum_sk']
if request.method == 'POST':
query = request.form['table']
try:
f_lietotajs = request.form['lietotajs']
f_projekts = request.form['projekts']
f_razotajs = request.form['razotajs']
f_birojs = request.form['birojs']
except:
f_lietotajs = None
f_projekts = None
f_razotajs = None
f_birojs = None
print("Doing the buttons.")
full_path = get_query(query, f_lietotajs, f_projekts, f_razotajs,
f_birojs)
return render_template(full_path)
return render_template("track/queries.html",
t_lietotaji=t_lietotaji,
t_razotaji=t_razotaji,
t_projekti=t_projekti,
t_biroji=t_biroji,
darb_sk=darb_sk,
vienum_sk=vienum_sk,
proj_sk=proj_sk,
briv_vienum_sk=briv_vienum_sk)
def instert_into_db(table, cols, data):
col_count = len(cols.split(","))
values_string = ", ?" * ((col_count) - 1)
print()
query = 'INSERT INTO {} ({}) VALUES (?{})'.format(
table, cols, values_string)
print("Tavs vaicājums:\n", query)
print("Turpināt vai rediģēt? (r - rediģēt)")
user_input = input()
if user_input == 'r':
print("\nIerakstiet savu vaicājumu:")
new_query = input()
print("Apstriprināt? (y, a - izmantot iepriekšējo, n - atcelt darbību)")
user_input = input()
if user_input == 'y':
query = new_query
print("Tiek izmantots vaicājums:\n" + query)
elif user_input == 'a':
print("Tiek izmantots vaicājums:\n" + query)
else:
raise "Programmas darbība tiek pārtraukta..."
db = get_db()
for row in data:
if col_count == 1:
values = row
else:
values = []
for val in row:
values.append(val)
if col_count == 1:
db.execute(query, [values])
db.commit()
else:
db.execute(query, (values))
db.commit()
cursor = db.cursor()
cursor.execute("SELECT * FROM {}".format(table))
result = cursor.fetchall()
for row in result:
for field in row:
print(field, end=" | ")
print("")
def get_comments(item_id):
db = get_db()
comments = db.execute(
'''SELECT koment_id, k.komentars,
v.vienum_nosauk, l.lietv, noris_laiks
FROM t_komentari k
JOIN t_vienumi v ON k.vienum_id = v.vienum_id
JOIN t_lietotaji l ON k.liet_id = l.liet_id
WHERE v.vienum_id = ?''',
(item_id, ),
).fetchall()
return comments
def load_logged_in_user():
user_id = session.get('user_id')
db = get_db()
if user_id is None:
g.user = None
else:
g.user = db.execute(
'''SELECT l.liet_id, l.lietv, vards, uzv, poz.pozicija, profil_bild_cels
FROM t_lietotaji l
JOIN t_pozicijas poz ON l.poz_id = poz.poz_id
WHERE l.liet_id = ?''',
(user_id, ),
).fetchone()
def get_history(item_id):
db = get_db()
history = db.execute(
'''SELECT i.ierakst_id, v.vienum_nosauk, l.lietv,
d.darbiba, i.noris_laiks
FROM t_ieraksti i
JOIN t_vienumi v ON i.vienum_id = v.vienum_id
JOIN t_lietotaji l ON i.liet_id = l.liet_id
JOIN t_darbibas d ON i.darb_id = d.darb_id
WHERE v.vienum_id = ?''',
(item_id, ),
).fetchall()
return history
def addme():
timestamp = datetime.now(tz=TIMEZONE).strftime("%Y-%m-%d %H:%M:%S")
item_id = request.args.get('item_id')
user_id = request.args.get('user_id')
darb_id = 3
print("item={}, user={}".format(item_id, user_id))
db = get_db()
db.execute(
'''UPDATE t_vienumi
SET liet_id = ?,
atjauninats = ?
WHERE vienum_id = ?''', (user_id, timestamp, item_id))
update_history(item_id, user_id, darb_id)
db.commit()
return redirect(url_for("track.index"))
def index():
user_id = session.get('user_id')
db = get_db()
t_vienumi = db.execute(
'''SELECT vienum_id, svitr_kods, vienum_nosauk, modelis,
r.razotajs, iss_aprakst, detalas,
k.kateg_id, k.kategorija, b.biroj_id, b.birojs,
l.liet_id, l.lietv, bilde_cels, atjauninats
FROM t_vienumi v
LEFT JOIN t_razotaji r ON v.razot_id = r.razot_id
LEFT JOIN t_kategorijas k ON v.kateg_id = k.kateg_id
LEFT JOIN t_biroji b ON v.biroj_id = b.biroj_id
LEFT JOIN t_lietotaji l ON v.liet_id = l.liet_id
ORDER BY atjauninats DESC''').fetchall()
return render_template('track/index.html',
user_id=user_id,
t_vienumi=t_vienumi)
def get_item(item_id):
db = get_db()
item = db.execute(
'''SELECT vienum_id, svitr_kods, vienum_nosauk, modelis,
r.razotajs, iss_aprakst, detalas,
k.kategorija, b.birojs, l.liet_id, l.lietv, bilde_cels,
v.nopirkt_dat, v.izveid_dat, v.atjauninats
FROM t_vienumi v
LEFT JOIN t_razotaji r ON v.razot_id = r.razot_id
LEFT JOIN t_kategorijas k ON v.kateg_id = k.kateg_id
LEFT JOIN t_biroji b ON v.biroj_id = b.biroj_id
LEFT JOIN t_lietotaji l ON v.liet_id = l.liet_id
WHERE v.vienum_id = ?''',
(item_id, ),
).fetchone()
if item is None:
abort(404, "Item id {0} doesn't exist.".format(item_id))
return item
def get_all_tables():
db = get_db()
all_tables = [
"t_lietotaji", "t_biroji", "t_pilsetas", "t_projekti", "t_pozicijas",
"t_vienumi", "t_ieraksti", "t_komentari", "t_darbibas",
"t_kategorijas", "t_razotaji"
]
combined_html = []
for table in all_tables:
print(">> Table: {}".format(table))
query = "SELECT * FROM {}".format(table)
df = pd.read_sql_query(query, db)
combined_html.append(str(df.to_html(index=False)))
html = " ".join(combined_html)
html = TABLE_STYLE + "\n" + html
return html
def login():
if request.method == 'POST':
lietv = request.form['lietv']
parole = request.form['parole']
db = get_db()
error = None
user = db.execute(
'SELECT liet_id, lietv, parole FROM t_lietotaji WHERE lietv = ?',
(lietv, )).fetchone()
if user is None:
error = 'Nepareizs lietotājs vai parole.'
elif not check_password_hash(user['parole'], parole):
error = 'Nepareizs lietotājs vai parole.'
if error is None:
session.clear()
session['user_id'] = user['liet_id']
return redirect(url_for('track.index'))
flash(error)
return render_template('auth/login.html')
def choose_query(query_name, f_lietotaji=None, f_projekti=None, f_razotaji=None, f_birojs=None):
db = get_db()
if query_name == "t_lietotaji":
query_name = t_lietotaji
elif query_name == "t_biroji":
query_name = t_biroji
elif query_name == "t_vienumi":
query_name = t_vienumi
elif query_name == "vc_pilsetas":
query_name = vc_pilsetas
elif query_name == "vc_birojs":
query_name = vc_birojs
elif query_name == "vc_lietotaji":
query_name = vc_lietotaji
elif query_name == "vc_projekti":
query_name = vc_projekti
elif query_name == "vc_pozicijas":
query_name = vc_pozicijas
elif query_name == "vc_kategorijas":
query_name = vc_kategorijas
elif query_name == "vc_razotaji":
query_name = vc_razotaji
elif query_name == "vg_pilsetas":
query_name = vg_pilsetas
elif query_name == "vg_biroji":
query_name = vg_biroji
elif query_name == "vg_kategorijas":
query_name = vg_kategorijas
elif query_name == "t_ieraksti":
query_name = t_ieraksti
elif query_name == "t_komentari":
query_name = t_komentari
else:
print("Unknown query: [{}]".format(query_name))
return query_name
def update_history(item_id, liet_id, darb_id):
db = get_db()
db.execute(
'''INSERT INTO t_ieraksti(vienum_id, liet_id, darb_id)
VALUES (?, ?, ?)''', (item_id, liet_id, darb_id))
def edit(item_id):
timestamp = datetime.now(tz=TIMEZONE).strftime("%Y-%m-%d %H:%M:%S")
db = get_db()
item = get_item(item_id)
today_date = date.today()
old_vienumi_array = []
new_vienumi_array = []
old_vienumi = db.execute(
'''SELECT liet_id, biroj_id, nopirkt_dat,
iss_aprakst, kateg_id, razot_id, vienum_nosauk,
modelis, bilde_cels, detalas
FROM t_vienumi
WHERE vienum_id = ?''',
(item_id, )).fetchone()
for i, value in enumerate(old_vienumi):
old_vienumi_array.append(value)
print("old_value[{}]: '{}'".format(i, value))
t_biroji = db.execute('''SELECT biroj_id, birojs
FROM t_biroji''').fetchall()
t_kategorijas = db.execute('''SELECT kateg_id, kategorija
FROM t_kategorijas''').fetchall()
t_razotaji = db.execute('''SELECT razot_id, razotajs
FROM t_razotaji''').fetchall()
t_lietotaji = db.execute('''SELECT liet_id, lietv
FROM t_lietotaji''').fetchall()
if request.method == 'POST':
print("--> POST Requested detected.")
lietv = request.form['lietv'] # Rule for it
print("--> lietv: [{}]".format(lietv))
vienum_nosauk = request.form['vienum_nosauk']
birojs = request.form['birojs']
kategorija = request.form['kategorija']
nopirkt_dat = request.form['nopirkt_dat']
iss_aprakst = request.form['iss_aprakst']
razotajs = request.form['razotajs']
modelis = request.form['modelis']
detalas = request.form['detalas']
user_id = session.get('user_id')
darb_id = 2
filename = db.execute(
'''SELECT bilde_cels
FROM t_vienumi WHERE vienum_id = ?''',
(item_id, )).fetchone()
filename = filename['bilde_cels']
svitr_kods = db.execute(
'''SELECT svitr_kods
FROM t_vienumi WHERE vienum_id = ?''',
(item_id, )).fetchone()
biroj_id = db.execute('''SELECT * FROM t_biroji WHERE birojs = ?''',
(birojs, )).fetchone()
kateg_id = db.execute(
'''SELECT * FROM t_kategorijas WHERE kategorija = ?''',
(kategorija, )).fetchone()
razot_id = db.execute(
'''SELECT * FROM t_razotaji WHERE LOWER(razotajs) = LOWER(?)''',
(razotajs, )).fetchone()
print("--> Data Fetched.")
if lietv != "":
liet_id = db.execute(
'''SELECT liet_id FROM t_lietotaji
WHERE LOWER(lietv) = LOWER(?)''',
(lietv, )).fetchone()
else:
liet_id = {"liet_id": ""}
if razot_id is None:
db.execute(
'''INSERT INTO t_razotaji (razotajs)
VALUES (?)''', (razotajs, ))
db.commit()
razot_id = db.execute(
'''SELECT * FROM t_razotaji WHERE razotajs = ?''',
(razotajs, )).fetchone()
file = request.files['bilde_cels']
if file and allowed_file(file.filename):
filename = ("{}_{}".format(svitr_kods['svitr_kods'],
secure_filename(file.filename)))
file.save(os.path.join(current_app.config['ITEM_IMGAES'],
filename))
print("File saved in: " + current_app.config['ITEM_IMGAES'] +
filename)
error = None
if vienum_nosauk is None:
error = "Nosaukums ir obligāts."
elif birojs is None:
error = "Atrašanās vieta ir obligāta."
elif kategorija is None:
error = "Kategorija ir obligāta."
if error is not None:
flash(error)
else:
print("--> Error is None .")
print(filename)
db.execute(
'''UPDATE t_vienumi
SET vienum_nosauk = ?, modelis = ?, razot_id = ?,
iss_aprakst = ?, detalas = ?, kateg_id = ?,
biroj_id = ?, liet_id = ?, bilde_cels = ?,
nopirkt_dat = ?, atjauninats = ? WHERE vienum_id = ?''',
(
vienum_nosauk,
modelis,
razot_id['razot_id'],
iss_aprakst,
detalas,
kateg_id['kateg_id'],
biroj_id['biroj_id'],
liet_id['liet_id'],
filename,
nopirkt_dat,
timestamp,
item_id,
))
print("--> Query executed.")
print("--> New vienumi starting...")
new_vienumi = db.execute(
'''SELECT liet_id, biroj_id, nopirkt_dat,
iss_aprakst, kateg_id, razot_id, vienum_nosauk,
modelis, bilde_cels, detalas
FROM t_vienumi
WHERE vienum_id = ?''',
(item_id, )).fetchone()
print("--> New vienumi fetched.")
for i, value in enumerate(new_vienumi):
new_vienumi_array.append(value)
print(" new_value[{}]: '{}'".format(i, value))
array_change_before = list(
set(old_vienumi_array) - set(new_vienumi_array))
array_change_after = list(
set(new_vienumi_array) - set(old_vienumi_array))
array_changed_count = len(
set(new_vienumi_array) - set(old_vienumi_array))
if (array_change_before == []):
array_change_before = array_change_before.append("")
array_changed_count += 1
elif (array_change_after == []):
array_change_after = array_change_after.append("")
array_changed_count += 1
if (array_changed_count > 0):
print("--> If changed 1 or 0 fulfilled.")
if (old_vienumi_array[0] == user_id
and new_vienumi_array[0] == ""):
print("--> User has returned the item.")
darb_id = 4
elif new_vienumi_array[0] == user_id:
print("--> User has taken the item.")
darb_id = 3
else:
print("--> Array before - {}.".format(
old_vienumi_array[0]))
print(array_change_before)
print("--> Array after - {}.".format(new_vienumi_array[0]))
print(array_change_after)
if (array_changed_count > 1):
update_history(item_id, user_id, 2)
print("--> Item was also editted.")
print("--> Changed values({}) are - {}".format(
array_changed_count, array_change_after))
update_history(item_id, user_id, darb_id)
print("--> History table updated.")
db.commit()
print("--> Commit successfull.")
return redirect(url_for("track.index"))
return render_template("track/edit.html",
item=item,
timestamp=timestamp,
t_kategorijas=t_kategorijas,
t_biroji=t_biroji,
t_razotaji=t_razotaji,
t_lietotaji=t_lietotaji,
today_date=today_date)
def relations():
db = get_db()
return render_template("track/relations.html")
def register():
db = get_db()
t_pozicijas = db.execute('''SELECT poz_id, pozicija
FROM t_pozicijas''').fetchall()
t_projekti = db.execute('''SELECT proj_id, projekts
FROM t_projekti''').fetchall()
t_biroji = db.execute('''SELECT biroj_id, birojs
FROM t_biroji''').fetchall()
if request.method == 'POST':
lietv = (request.form['lietv']).lower()
parole = request.form['parole']
vards = (request.form['vards']).capitalize()
uzv = request.form['uzv'].capitalize()
pozicija = request.form['pozicija']
projekts = request.form['projekts']
birojs = request.form['birojs']
pers_kods = request.form['pers_kods']
epasts = (request.form['epasts']).lower()
tel_num = request.form['tel_num']
filename = 'default.png'
proj_id = db.execute('''SELECT * FROM t_projekti WHERE projekts = ?''',
(projekts, )).fetchone()
poz_id = db.execute('''SELECT * FROM t_pozicijas WHERE pozicija = ?''',
(pozicija, )).fetchone()
biroj_id = db.execute('''SELECT * FROM t_biroji WHERE birojs = ?''',
(birojs, )).fetchone()
# check if the post request has the file part
if 'profil_bild_cels' not in request.files:
flash('Request.files: ' + request.files)
file = request.files['profil_bild_cels']
# flash("File found: " + file.filename)
# if user does not select file, browser also
# submit an empty part without filename
if file.filename == '':
flash('No selected file... Using default.')
if file and allowed_file(file.filename):
filename = ("{}_{}".format(lietv, secure_filename(file.filename)))
file.save(
os.path.join(current_app.config['UPLOAD_FOLDER'], filename))
flash("File saved in: " + current_app.config['UPLOAD_FOLDER'] +
filename)
error = None
if not lietv:
# Ģenerēt lietotājvārdu
lietv = ("{}.{}".format(vards, uzv)).lower()
if db.execute(
'SELECT liet_id FROM t_lietotaji WHERE LOWER(lietv) = LOWER(?)',
(lietv, )).fetchone() is not None:
error = 'Lietotājs {} jau eksistē'.format(lietv)
if not parole:
error = 'Parole ir obligāta.'
elif not vards:
error = 'Vārds ir obligāts'
elif not uzv:
error = 'Uzvārds ir obligāts.'
elif not pozicija:
error = 'Pozīcija ir obligāta.'
elif (pozicija).lower() == 'administrators':
error = 'Jaunus administratorus var tikai piereģistrēt administrators'
elif not projekts:
error = 'Projekts ir obligāts.'
elif not birojs:
error = 'Birojs ir obligāts.'
elif not pers_kods:
error = 'Personas kods ir obligāts.'
elif not epasts:
error = 'E-pasts ir obligāts.'
elif not tel_num:
error = 'Telefona numurs ir obligāts.'
elif db.execute('SELECT liet_id FROM t_lietotaji WHERE lietv = ?',
(lietv, )).fetchone() is not None:
error = 'Lietotājs {} jau eksistē'.format(lietv)
if error is None:
db.execute(
'INSERT INTO t_lietotaji (lietv, parole, vards, uzv,'
' poz_id, proj_id, biroj_id, pers_kods, epasts, tel_num, profil_bild_cels)'
'VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
(lietv, generate_password_hash(parole), vards, uzv,
poz_id['poz_id'], proj_id['proj_id'], biroj_id['biroj_id'],
pers_kods, epasts, tel_num, filename))
db.commit()
return redirect(url_for('track.index'))
flash(error)
return render_template('auth/register.html',
t_pozicijas=t_pozicijas,
t_projekti=t_projekti,
t_biroji=t_biroji)
def get_table(table):
db = get_db()
query = "SELECT * FROM {}".format(table)
df = pd.read_sql_query(query, db)
return df
def get_query(query, f_lietotajs=None, f_projekts=None, f_razotajs=None, f_birojs=None):
db = get_db()
query_name = query
query = choose_query(query)
if query_name == "vg_pilsetas":
query_array = []
pilsetas = db.execute(t_pilsetas).fetchall()
for pilseta in pilsetas:
print(pilseta['pilseta'])
df = pd.read_sql_query(vg_pilsetas.format(pilseta['pilseta']), db)
html = df.to_html(index=False)
query_array.append("<h1 style='font-size:72px; padding-bottom:0px;'>{}</h1>".format(pilseta['pilseta']))
query_array.append(html)
html = " ".join(query_array)
elif query_name == "vg_biroji":
query_array = []
biroji = db.execute(t_biroji).fetchall()
for birojs in biroji:
print(birojs['birojs'])
df = pd.read_sql_query(vg_biroji.format(birojs['birojs']), db)
html = df.to_html(index=False)
query_array.append("<h1 style='font-size:72px; padding-bottom:0px;'>{}</h1>".format(birojs['birojs']))
query_array.append(html)
html = " ".join(query_array)
elif query_name == "vg_kategorijas":
query_array = []
kategorijas = db.execute(t_kategorijas).fetchall()
for kategorija in kategorijas:
print(kategorija['kategorija'])
df = pd.read_sql_query(vg_kategorijas.format(kategorija['kategorija']), db)
html = df.to_html(index=False)
query_array.append("<h1 style='font-size:72px; padding-bottom:0px;'>{}</h1>".format(kategorija['kategorija']))
query_array.append(html)
html = " ".join(query_array)
elif query_name == "f_lietotaji" and f_lietotajs != None:
query_array = []
df = pd.read_sql_query(f_lietotaji_info.format(f_lietotajs), db)
html = df.to_html(index=False)
query_array.append("<h1 style='font-size:72px; padding-bottom:0px;'>{}</h1>".format("Dati"))
query_array.append(html)
df = pd.read_sql_query(f_lietotaji_vienumi.format(f_lietotajs), db)
html = df.to_html(index=False)
query_array.append("<h1 style='font-size:72px; padding-bottom:0px;'>{}</h1>".format("Vienumi"))
query_array.append(html)
df = pd.read_sql_query(f_lietotaji_ieraksti.format(f_lietotajs), db)
html = df.to_html(index=False)
query_array.append("<h1 style='font-size:72px; padding-bottom:0px;'>{}</h1>".format("Ieraksti"))
query_array.append(html)
df = pd.read_sql_query(f_lietotaji_komentari.format(f_lietotajs), db)
html = df.to_html(index=False)
query_array.append("<h1 style='font-size:72px; padding-bottom:0px;'>{}</h1>".format("Komentāri"))
query_array.append(html)
html = " ".join(query_array)
elif query_name == "f_projekti" and f_projekts != None:
query_array = []
df = pd.read_sql_query(f_projekti_vienumi.format(f_projekts), db)
html = df.to_html(index=False)
query_array.append("<h1 style='font-size:72px; padding-bottom:0px;'>{}</h1>".format("Projekta vienumi"))
query_array.append(html)
df = pd.read_sql_query(f_projekti_darbinieki.format(f_projekts), db)
html = df.to_html(index=False)
query_array.append("<h1 style='font-size:72px; padding-bottom:0px;'>{}</h1>".format("Projekta komanda"))
query_array.append(html)
html = " ".join(query_array)
elif query_name == "f_razotaji" and f_razotajs != None:
query_array = []
df = pd.read_sql_query(f_razotaji_vienumi.format(f_razotajs), db)
html = df.to_html(index=False)
query_array.append("<h1 style='font-size:72px; padding-bottom:0px;'>{}</h1>".format("Ražotāja vienumi"))
query_array.append(html)
html = " ".join(query_array)
elif query_name == "f_biroji" and f_birojs != None:
query_array = []
df = pd.read_sql_query(f_biroji_darbinieki.format(f_birojs), db)
html = df.to_html(index=False)
query_array.append("<h1 style='font-size:72px; padding-bottom:0px;'>{}</h1>".format("Darbinieki"))
query_array.append(html)
df = pd.read_sql_query(f_biroji_vienumi.format(f_birojs), db)
html = df.to_html(index=False)
query_array.append("<h1 style='font-size:72px; padding-bottom:0px;'>{}</h1>".format("Vienumi"))
query_array.append(html)
html = " ".join(query_array)
else:
df = pd.read_sql_query(query, db)
html = df.to_html(index=False)
html = TABLE_STYLE + "\n" + html
filename = "queries/query-{}.html".format(query_name)
full_path = os.path.join(current_app.config['REPORTING_FOLDER'],
filename)
file_path = os.path.join('trakeris/templates/', full_path)
file = open(file_path, 'w', encoding="utf-8", errors='ignore')
file.write(html)
file.close()
flash("File located at: {}".format(os.path.abspath(full_path)))
return full_path
def add():
db = get_db()
today_date = date.today()
t_biroji = db.execute('''SELECT biroj_id, birojs
FROM t_biroji''').fetchall()
t_kategorijas = db.execute('''SELECT kateg_id, kategorija
FROM t_kategorijas''').fetchall()
t_razotaji = db.execute('''SELECT razot_id, razotajs
FROM t_razotaji''').fetchall()
if request.method == 'POST':
vienum_nosauk = request.form['vienum_nosauk'] # Obligāts
birojs = request.form['birojs'] # Obligāts
kategorija = request.form['kategorija'] # Obligāts
nopirkt_dat = request.form['nopirkt_dat']
iss_aprakst = request.form['iss_aprakst']
razotajs = request.form['razotajs']
modelis = request.form['modelis']
detalas = request.form['detalas']
filename = 'default_item.png'
darb_id = 1
vienum_id = db.execute('''SELECT MAX(vienum_id)+1
AS jaunakais_id
FROM t_vienumi''').fetchone()
jaunakais_id = vienum_id['jaunakais_id']
svitr_kods = db.execute('''SELECT MAX(svitr_kods)+1
AS lielakais_cip
FROM t_vienumi''').fetchone()
biroj_id = db.execute(
'''SELECT * FROM t_biroji
WHERE birojs = ?''', (birojs, )).fetchone()
kateg_id = db.execute(
'''SELECT * FROM t_kategorijas
WHERE kategorija = ?''',
(kategorija, )).fetchone()
razot_id = db.execute(
'''SELECT * FROM t_razotaji
WHERE LOWER(razotajs) = LOWER(?)''',
(razotajs, )).fetchone()
if razot_id is None:
db.execute(
'''INSERT INTO t_razotaji (razotajs)
VALUES (?)''', (razotajs, ))
db.commit()
razot_id = db.execute(
'''SELECT * FROM t_razotaji
WHERE razotajs = ?''',
(razotajs, )).fetchone()
# check if the post request has the file part
if 'bilde_cels' not in request.files:
print('Request.files: ' + request.files)
file = request.files['bilde_cels']
if file.filename == '':
print('No selected file... Using default.')
if file and allowed_file(file.filename):
filename = ("{}_{}".format(svitr_kods['lielakais_cip'],
secure_filename(file.filename)))
file.save(os.path.join(current_app.config['ITEM_IMGAES'],
filename))
print("File saved in: " + current_app.config['ITEM_IMGAES'] +
filename)
error = None
if vienum_nosauk is None:
error = "Nosaukums ir obligāts."
elif birojs is None:
error = "Atrašanās vieta ir obligāta."
elif kategorija is None:
error = "Kategorija ir obligāta."
if error is not None:
flash(error)
else:
db.execute(
'''INSERT INTO t_vienumi (
svitr_kods,vienum_nosauk,modelis,razot_id,
iss_aprakst,detalas,kateg_id,
biroj_id,liet_id,bilde_cels,nopirkt_dat)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)''', (
svitr_kods['lielakais_cip'],
vienum_nosauk,
modelis,
razot_id['razot_id'],
iss_aprakst,
detalas,
kateg_id['kateg_id'],
biroj_id['biroj_id'],
g.user['liet_id'],
filename,
nopirkt_dat,
))
update_history(jaunakais_id, g.user['liet_id'], darb_id)
db.commit()
return redirect(url_for("track.index"))
return render_template('track/add.html',
today_date=today_date,
t_kategorijas=t_kategorijas,
t_biroji=t_biroji,
t_razotaji=t_razotaji)