def new():
currentUser=userio.read()
conn = mysql.connector.connect(**config)
#Display all usernames stored in 'users' in the Username field
conn.row_factory = lambda cursor, row: row[0]
c = conn.cursor()
# number of post
c.execute("SELECT MAX(Postid) FROM Post")
postnum = c.fetchall()
postnum = int(postnum[0][0])+1
print("postnum===",postnum)
# all user list
if currentUser>0:
c.execute("SELECT u.Uid,u.username FROM Seller s,User u WHERE s.Uid=u.Uid AND s.Uid="+str(currentUser))
else:
c.execute("SELECT u.Uid,u.username FROM User u, Seller s WHERE u.Uid=s.Uid")
results = c.fetchall()
users = [(results.index(item), item[1]) for item in results]
form = CarInfoForm()
form.username.choices = users
# specialist
c.execute("SELECT * FROM Specialist")
results = c.fetchall()
specialist = [(results.index(item),item[1]+', '+str(item[2])+' years working exp.') for item in results]
form.specialist.choices = specialist
form.stars.choices = ((0,(1)),(1,(2)),(2,(3)))
if form.validate_on_submit():
choices = form.username.choices
specialist = int(form.specialist.data)+1
stars = form.stars.choices[form.stars.data][1]
user = currentUser
title = form.title.data
content = form.content.data
location= form.location.data
color = form.color.data
carType =form.carType.data
brand = form.brand.data
price = form.price.data
manuYear = form.manuYear.data
image = form.image.data
timestamp = datetime.datetime.now().strftime('%Y-%m-%d')
title = title.replace("'","''")
content = content.replace("'","''")
#Add the new Post into the 'blogs' table in the database
query = 'insert into Post (Postid, Date_post, Title,Description,Location,Color,Car_type,Brand,Price,manu_year,Uid,Sid,Stars,Imageid) VALUES (' + "'" + str(postnum) + "'," + "'" + timestamp + "'," + "'" + title + "',"+ "'" + content + "'," + "'" + location + "'," + "'" + color + "',"+ "'" + carType + "'," + "'" + brand + "'," + "'" + price + "'," + "'" + manuYear + "'," + "'" + str(user) + "'," + "'" + str(specialist) + "'," + "'" + str(stars) + "'," + "'" + str(image) + "'" ')' #Build the query
print("query======",query)
c.execute(query) #Execute the query
conn.commit() #Commit the changes
flash(f'Blog created for {user}!', 'success')
return redirect(url_for('Posts'))
return render_template('new.html', title='Blog', form=form )
def edit_post(name):
currentUser=userio.read()
conn = mysql.connector.connect(**config)
#Display all usernames stored in 'users' in the Username field
conn.row_factory = lambda cursor, row: row[0]
c = conn.cursor()
if (request.method == 'POST'):
form = CarInfoForm(request.form)
# user
c.execute("SELECT Uid,First_name,Last_name FROM User WHERE Uid="+str(currentUser))
results = c.fetchall()
users = [(results.index(item), item) for item in results]
form.username.choices = users
# specialist
c.execute("SELECT * FROM Specialist")
specialist_res = c.fetchall()
specialist = [(specialist_res.index(item), item) for item in specialist_res]
form.specialist.choices = specialist
form.stars.choices = ((0,(1)),(1,(2)),(2,(3)))
if(form.validate_on_submit()):
specialist = form.specialist.choices[form.specialist.data][1]
stars = form.stars.choices[form.stars.data][1]
user = users[form.username.data][1]
title = form.title.data
content = form.content.data
location= form.location.data
color = form.color.data
carType =form.carType.data
brand = form.brand.data
price = form.price.data
manuYear = form.manuYear.data
image = form.image.data
timestamp = datetime.datetime.now().strftime('%Y-%m-%d')
# Add the new Post into the 'blogs' table in the database
title = title.replace("'","''")
content = content.replace("'","''")
# print("==========image========",image)
query = 'UPDATE Post SET Date_post='+"'"+timestamp+"'" +',Title='+"'" +title+"'" +',Description='+"'" +content+"'" +',Location='+"'" +location+"'" +',Color='+"'" +color+"'" +',Car_type='+"'" +carType+"'" +',Brand='+"'" +brand+"'" +',Price='+str(price)+',manu_year='+str(manuYear)+',Sid='+str(specialist[0])+',Stars='+str(stars) + ',Imageid=' +str(image)+' WHERE Postid='+str(name)
c.execute(query) #Execute the que ry
conn.commit() #Commit the changes
flash(f'Post edited for {user}!', 'success')
return redirect(url_for('Posts'))
return render_template('edit.html',title='Blog', form=form)
elif request.method == 'GET':
form = CarInfoForm()
c.execute("SELECT Uid,First_name,Last_name FROM User WHERE Uid="+str(currentUser))
results = c.fetchall()
users = [(results.index(item), item) for item in results]
conn.row_factory = dict_factory
c = conn.cursor()
c.execute("SELECT u.Uid,u.First_name,u.Last_name,p.* FROM Seller s,User u,Post p WHERE p.Uid=s.Uid AND s.Uid=u.Uid AND s.Uid="+str(currentUser)+" AND p.Postid="+name)
results = c.fetchall()
# put existing data in column
results = results[0]
# form = CarInfoForm()
form.username.choices = users
form.title.data = results[5]
form.content.data = results[6]
form.location.data = results[7]
form.color.data = results[8]
form.carType.data = results[9]
form.brand.data = results[10]
form.price.data = results[11]
form.manuYear.data = results[12]
form.image.data = results[16]
# specialist
c.execute("SELECT * FROM Specialist")
specialist_res = c.fetchall()
specialist = [(specialist_res.index(item), item) for item in specialist_res]
form.specialist.choices = specialist
form.stars.choices = ((0,(1)),(1,(2)),(2,(3)))
form.stars.data = results[15]-1
form.specialist.data = results[14]-1
return render_template('edit.html',title='Blog', form=form)