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)