def insertComment(parametri):
with connection.cursor() as cursor:
querystring = "insert into comments(username, allergy_name, comment) VALUES(%s,%s,%s)"
cursor.execute(querystring,
(parametri["username"], parametri["allergy_name"],
parametri["comment"]))
connection.commit()
with connection.cursor() as cursor:
querystring = "select id_user from user_allergy where id_allergy = (select id from allergies where name=%s)"
cursor.execute(querystring, parametri["allergy_name"])
result = cursor.fetchall()
querystring = "select id from users where username=%s"
cursor.execute(querystring, parametri["username"])
user = cursor.fetchone()[0]
mesaj = "New comment added on " + \
str(parametri["allergy_name"]) + " allergy"
ids = list(sum(result, ()))
for id_user in ids:
if user != id_user:
with connection.cursor() as cursor:
querystring = "insert into notifications(id_user, mesaj, tip) VALUES(%s,%s,%s)"
cursor.execute(querystring, (str(id_user), mesaj, 2))
connection.commit()
def priority_update():
currentRoutes = "SELECT route FROM wcs.verify_trailers"
cursor.execute(currentRoutes)
results = cursor.fetchall()
currentRoutes = []
for idx, r in enumerate(results):
currentRoutes.append(results[idx][0])
for route in currentRoutes:
date = "SELECT date FROM wcs.verify_trailers WHERE route=" + \
"'"+str(route)+"'"
cursor.execute(date)
result = cursor.fetchone()
date = result[0]
priority = "SELECT priority FROM wcs.route_statuses WHERE route=" + \
"'"+str(route)+"' AND date="+"'"+str(date)+"'"
cursor.execute(priority)
result = cursor.fetchone()
priority = result[0]
trailer = "SELECT trailer_number FROM wcs.route_statuses WHERE route=" + \
"'"+str(route)+"' AND date="+"'"+str(date)+"'"
cursor.execute(priority)
result = cursor.fetchone()
trailer = result[0]
cursor.execute("UPDATE wcs.verify_trailers SET priority="+"'"+str(priority) +
"',trailer_number="+"'"+str(trailer)+"' WHERE route="+"'"+str(route)+"'")
connection.commit()
return "priority/trailer update - complete"
def update_slot_to_free(self):
connection = db_connection.connect()
cursor = connection.cursor()
try:
cursor.execute("select slot from vehicle_details where vehicle_number='{}'".format(self.vehical_entry.get()))
slot = cursor.fetchone()
if slot == None or slot[0] != int(self.slot_entry.get()):
messagebox.showerror("Error", "Sorry, you have not booked this slot.", parent=self.toplevel_free_slot)
return
cursor.execute("update slot SET booked_status='Available' where slot_number={}".format(self.slot_entry.get()))
connection.commit()
self.vehical_entry.delete(0, END)
self.slot_entry.delete(0, END)
messagebox.showinfo("Successful", "Slot is free now..", parent=self.toplevel_free_slot)
except Exception as e:
connection.rollback()
messagebox.showerror("Error", e, parent=self.toplevel_free_slot)
print(e)
connection.close()
def insertAllergy(id, name, category, description, symptoms, prevention,
treatment, medication, years, people, age, percent):
with connection.cursor() as cursor:
querystring = "insert into allergies VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
cursor.execute(querystring,
(id, name, category, description, symptoms, prevention,
treatment, medication, years, people, age, percent))
connection.commit()
def insertFeedback(parametri):
with connection.cursor() as cursor:
querystring = "insert into feedback(id_user, name, email, rating, message) VALUES(%s,%s,%s,%s,%s)"
cursor.execute(
querystring,
(parametri["id_user"], parametri["name"], parametri["email"],
parametri["rating"], parametri["message"]))
connection.commit()
def insertVariblesIntoTable(name, number, proced, insurance):
x = datetime.datetime.now()
mySql_insert_query = """INSERT INTO myoffice (name,number,proced,insurance,rdate)
VALUES (%s, %s, %s, %s, %s) """
recordTuple = (name, number, proced, insurance, x)
cursor.execute(mySql_insert_query, recordTuple)
connection.commit()
# print("Record inserted successfully into Laptop table")
return print("Thank you")
def getNotificari(userID):
with connection.cursor() as cursor:
querystring = "SELECT * from notifications where id_user=%s;"
cursor.execute(querystring, (userID))
rezultat = cursor.fetchall()
querystring = "DELETE FROM notifications where id_user=%s;"
cursor.execute(querystring, (userID))
connection.commit()
return rezultat
def tryregister(username, password, email):
mycursor = connection.cursor()
# mycursor.execute("SELECT * FROM frontdesk", username, password,email)
# rs = mycursor.fetchall()
mySql_insert_query = """INSERT INTO frontdesk (username,password,email)
VALUES (%s, %s, %s) """
recordTuple = (username, password, email)
cursor.execute(mySql_insert_query, recordTuple)
connection.commit()
return print("done")
def saveAllergies(userID, allergies):
with connection.cursor() as cursor:
querystring = "DELETE FROM user_allergy where id_user=%s"
cursor.execute(querystring, (userID))
for i in allergies:
print(i)
querystring = "INSERT INTO user_allergy(id_user, id_allergy) VALUES(%s, %s);"
cursor.execute(querystring, (userID, i))
connection.commit()
def savePesticides(list):
connection = Pesticide.connection
cursor = Pesticide.cursor
sql = "INSERT INTO tarimsal(isim, fiyat, hastalik, dozaj, kullanim, tip) VALUES (%s, %s, %s, %s, %s, %s)"
values = list
cursor.executemany(sql, values)
try:
connection.commit()
except mysql.connector.Error as err:
pass
finally:
connection.close()
def insertSuggestion(name, category, description, symptoms, prevention,
treatment, medication, id_user):
with connection.cursor() as cursor:
querystring = "select max(id) from allergies"
cursor.execute(querystring)
id = int(cursor.fetchone()[0]) + 1
id = str(id)
querystring = "insert into allergies (id, name, category, description, symptoms, prevention, " \
"treatment, medication,user_id, validation) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
cursor.execute(querystring,
(id, name, category, description, symptoms, prevention,
treatment, medication, id_user, 0))
connection.commit()
def add_spring_notification():
with connection.cursor() as cursor:
querystring = "select id from users where id in (select id_user from user_allergy " \
"where id_allergy = ( select id_allergy from allergies where name = \"Pollen\")) "
cursor.execute(querystring)
result = cursor.fetchall()
result = list(sum(result, ()))
mesaj = "Atentie! Primavara este sezonul alergiilor la polen! "
for id in result:
with connection.cursor() as cursor:
querystring = "insert into notifications(id_user, mesaj, tip) values(%s,%s,%s)"
cursor.execute(querystring, (str(id), mesaj, 2))
connection.commit()
def add_summer_notification():
with connection.cursor() as cursor:
querystring = "select id from users where id in (select id_user from user_allergy " \
"where id_allergy = ( select id_allergy from allergies where name = \"Sun\")) "
cursor.execute(querystring)
result = cursor.fetchall()
result = list(sum(result, ()))
mesaj = "Atentie! Vine vara, stati la umbra! "
for id in result:
with connection.cursor() as cursor:
querystring = "insert into notifications(id_user, mesaj, tip) values(%s,%s,%s)"
cursor.execute(querystring, (str(id), mesaj, 2))
connection.commit()
def updatePesticidePrice(isim, fiyat):
connection = Pesticide.connection
cursor = Pesticide.cursor
isim = isim
fiyat = fiyat
sql = "UPDATE tarimsal SET fiyat= %s WHERE isim = %s"
values = (fiyat, isim)
cursor.execute(sql, values)
try:
connection.commit()
except mysql.connector.Error as err:
pass
finally:
connection.close()
def validate(id):
with connection.cursor() as cursor:
querystring = "UPDATE allergies SET validation=1 WHERE id= %s"
cursor.execute(querystring, str(id))
connection.commit()
with connection.cursor() as cursor:
querystring = "SELECT name, category from allergies where id=%s"
cursor.execute(querystring, id)
rezultat = cursor.fetchall()[0]
print(rezultat)
usersToNotify = selectUsersByCategoryAllergy(id)
for i in usersToNotify:
userID = i[0]
notify(
userID, "New allergy added! \nCategory: " + rezultat[1] +
"\nName: " + rezultat[0], 1)
def deletePesticide(isim):
connection = Pesticide.connection
cursor = connection.cursor()
isim = isim
value = str(isim)
sql = """delete from tarimsal where isim = %s"""
cursor.execute(sql, (value,))
try:
connection.commit()
except mysql.connector.Error as err:
pass
finally:
connection.close()
def freezer_cooler_picks():
routes = "SELECT route FROM wcs.verify_trailers WHERE status<>'Shipped'"
cursor.execute(routes)
results = cursor.fetchall()
routes = []
for idx, r in enumerate(results):
routes.append(results[idx][0])
# print(routes)
date = "SELECT date FROM wcs.verify_trailers WHERE route=" + \
"'"+str(routes[0])+"'"
cursor.execute(date)
result = cursor.fetchone()
date = result[0]
# print(date)
for r in routes:
pickGroup = ["Freezer", "Cooler"]
for p in pickGroup:
count = "SELECT COUNT(*) FROM assignment.dat_master WHERE route_no="+"'"+str(
r)+"' AND date="+"'"+str(date)+"' AND pick_group="+"'"+str(p)+"'"
cursor.execute(count)
result = cursor.fetchone()
count = int(result[0])
# print(count)
if count > 0:
count_scanned = "SELECT COUNT(*) FROM assignment.dat_master WHERE route_no="+"'"+str(
r)+"' AND date="+"'"+str(date)+"' AND pick_group="+"'"+str(p)+"' AND stop_scan=1"
cursor.execute(count_scanned)
result = cursor.fetchone()
count_scanned = int(result[0])
# print(count_scanned)
if count_scanned > 0:
cursor.execute("UPDATE wcs.verify_trailers SET " +
str(p)+"=1 WHERE route="+"'"+str(r)+"'")
cursor.execute("UPDATE assignment.dat_master SET dashboard_map=1, stop_scan=1 WHERE route_no=" +
"'"+str(r)+"' AND date="+"'"+str(date)+"' AND pick_group="+"'"+str(p)+"'")
connection.commit()
else:
pass
else:
pass
return "freezer/cooler picks processed"
def updatePesticideDisease(isim, hastalik):
connection = Pesticide.connection
cursor = Pesticide.cursor
isim = isim
hastalik = hastalik
sql = "UPDATE tarimsal SET hastalik= %s WHERE isim = %s"
values = (hastalik, isim)
cursor.execute(sql, values)
try:
connection.commit()
except mysql.connector.Error as err:
pass
finally:
connection.close()
def remove_table():
try:
connection = create_server_connection()
cursor = connection.cursor()
mySql_insert_query = """DELETE FROM perfilcores"""
cursor.execute(mySql_insert_query)
connection.commit()
print("Record removed successfully perfilcores table")
except mysql.connector.Error as error:
print("Failed to remove MySQL table {}".format(error))
finally:
if connection.is_connected():
cursor.close()
connection.close()
print("MySQL connection is closed")
def add_sesonal_notifications(seson):
with connection.cursor() as cursor:
querystring = "delete from notifications where tip=3"
cursor.execute(querystring)
querystring = "select distinct id_user from user_allergy where id_allergy = %s "
cursor.execute(querystring, seson["alergieID"])
result = cursor.fetchall()
result = list(sum(result, ()))
for id in result:
querystring = "insert into notifications(id_user, mesaj, tip) values(%s, %s, %s)"
cursor.execute(querystring, (id, seson["mesaj"], 3))
connection.commit()
def save_form_data(self):
connection = db_connection.connect()
cursor = connection.cursor()
try:
cursor.execute("insert into vehicle_details(full_name, vehicle_number, phone_number, gender, slot) values ('{}', '{}', {}, '{}', '{}')".format((self.fnameEntry.get()+" "+ self.lnameEntry.get()), self.velicle_num_Entry.get(), int(self.telnumEntry.get()), self.gender.get(), self.availabel_slot))
connection.commit()
cursor.execute("update slot SET booked_status='Booked' where slot_number={}".format(self.availabel_slot))
connection.commit()
messagebox.showinfo("Successful", "Slot Booked successfully..", parent=self.toplevel)
except Exception as e:
connection.rollback()
messagebox.showerror("Error", e, parent=self.toplevel)
print(e)
connection.close()
self.resetForm()
def notify(userID, mesaj, type):
with connection.cursor() as cursor:
querystring = "INSERT into notifications(id_user,mesaj,tip) VALUES(%s, %s, %s);"
cursor.execute(querystring, (userID, mesaj, type))
connection.commit()
with connection.cursor() as cursor:
querystring = "select email from users where id=%s "
cursor.execute(querystring, userID)
email = cursor.fetchone()
server = smtplib.SMTP('smtp.gmail.com', 25)
server.connect("smtp.gmail.com", 587)
server.ehlo()
server.starttls()
server.ehlo()
server.login("seallergy.notifications", "web2019!")
text = mesaj
server.sendmail("*****@*****.**", email, text)
server.quit()
def insert_varibles_into_table(codigo_item, seq_cor, desc_cor, anilox, densidade, tv, lab):
try:
connection = create_server_connection()
cursor = connection.cursor()
mySql_insert_query = """INSERT INTO perfilcores (codigo_item, seq_cor, desc_cor, anilox, densidade, tv, lab)
VALUES (%s, %s, %s, %s, %s, %s, %s) """
record = (codigo_item, seq_cor, desc_cor, anilox, densidade, tv, lab)
print(record)
cursor.execute(mySql_insert_query, record)
connection.commit()
print("Record inserted successfully into perfilcores table")
except mysql.connector.Error as error:
print("Failed to insert into MySQL table {}".format(error))
finally:
if connection.is_connected():
cursor.close()
connection.close()
print("MySQL connection is closed")
def insert_varibles_into_table(recurso, etapa, seq_fila, op, cod_item,
desc_item, cod_clicheria, iniprog, fimprog,
mrp):
try:
connection = create_server_connection()
cursor = connection.cursor()
mySql_insert_query = """INSERT INTO pcpfila (recurso, etapa, seq_fila, op, codigo_item, descricao_item, cod_clicheria, inicioprog, fimprog, mrp)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s) """
record = (recurso, etapa, seq_fila, op, cod_item, desc_item,
cod_clicheria, iniprog, fimprog, mrp)
print(record)
cursor.execute(mySql_insert_query, record)
connection.commit()
print("Record inserted successfully into fila table")
except mysql.connector.Error as error:
print("Failed to insert into MySQL table {}".format(error))
finally:
if connection.is_connected():
cursor.close()
connection.close()
print("MySQL connection is closed")
app["category"] = json.dumps(app["category"])
del dom, page
except urllib2.URLError, e:
debug("Start open page for pkg[%s]: 404 not found it." % pkg)
app["type"] = 1
return app
debug("Start...")
connection = connection.MySQLConnection(**dbconfig)
cursor = connection.cursor()
cursor.execute("SET NAMES utf8")
cursor.execute("SELECT pkg FROM appinfo WHERE type = 0 LIMIT 50")
appinfo = cursor.fetchall()
debug("Find %d appinfos." % len(appinfo))
threads = []
for pkg in appinfo:
thread = Thread(target=updateApp, args=(pkg[0],))
# thread.setDaemon(True)
thread.start()
threads.append(thread)
for thread in threads:
thread.join()
connection.commit()
cursor.close()
connection.close()
def deleteUser(id):
with connection.cursor() as cursor:
querystring = "delete from users WHERE id = %s"
cursor.execute(querystring, str(id))
connection.commit()
def deleteAllergy(id):
with connection.cursor() as cursor:
querystring = "delete from allergies WHERE id = %s"
cursor.execute(querystring, str(id))
connection.commit()
def deleteSuggestion(id):
with connection.cursor() as cursor:
querystring = "delete from suggestions WHERE id_suggestion = %s"
cursor.execute(querystring, str(id))
connection.commit()
def insertUserAllergy(id_user, id_allergy):
with connection.cursor() as cursor:
querystring = "insert into user_allergy VALUES(%s,%s)"
cursor.execute(querystring, (id_user, id_allergy))
connection.commit()
def seletUserAllergies(id_user):
with connection.cursor() as cursor:
querystring = "delete from user_allergy WHERE id_user = %s"
cursor.execute(querystring, str(id_user))
connection.commit()
def saveNotifications(userID, val1, val2, val3):
with connection.cursor() as cursor:
querystring = "UPDATE users SET notificare1=%s, notificare2=%s, notificare3=%s WHERE id=%s;"
cursor.execute(querystring, (val1, val2, val3, userID))
connection.commit()
