def Print(id):
if request.method == 'POST':
data = request.form.to_dict()
del data['csrf_token']
#글내용보기
cursor = db.get_db().cursor()
# cursor.execute("SELECT * FROM comment LEFT JOIN user ON user.id = comment.author_id WHERE comment.parent_id = %s ORDER BY comment.id DESC ",str(id))
sql = "SELECT * FROM `estimatesheet` LEFT JOIN `user-companyinfo` ON `estimatesheet`.a_id = `user-companyinfo`.a_id where `estimatesheet`.id = " + str(
id) + " ORDER BY `user-companyinfo`.id DESC"
cursor.execute(sql)
row = cursor.fetchone()
db.get_db().commit()
if data['inlineRadioOptions'] == 'option1':
targetfile = 'estimatesheet-pdf-basic.html'
if data['inlineRadioOptions'] == 'option2':
targetfile = 'estimatesheet-pdf-simple.html'
if data['inlineRadioOptions'] == 'option3':
targetfile = 'estimatesheet-pdf-simple2.html'
options = {'enable-local-file-access': None}
rendered = render_template(targetfile, ROW=row, DATA=data)
pdf = pdfkit.from_string(rendered, False, options=options)
file_name = urllib.parse.quote("견적서.pdf".encode('utf-8'))
response = make_response(pdf)
response.headers["Content-Type"] = "application/pdf; charset=utf-8'"
response.headers[
"Content-Disposition"] = 'inline; filename*=UTF-8\'\'%s' % file_name
return response
def Write():
author_id = session['id']
if request.method == 'POST':
data = request.form.to_dict()
del data['csrf_token']
columns = '`id`,' + '`a_id`,'
values = "NULL," + "'" + str(author_id) + "',"
columns += ', '.join("`" + str(x).replace('/', '_') + "`"
for x in data.keys())
values += ', '.join("'" + str(x).replace('/', '_') + "'"
for x in data.values())
sql = "INSERT INTO %s (%s) VALUES (%s)" % ('`estimatesheet`', columns,
values)
# DB 사용
cursor = db.get_db().cursor()
cursor.execute(sql)
db.get_db().commit()
flash("저장하였습니다")
return redirect(url_for('estimatesheet.root'))
return render_template('estimatesheet-main.html')
def list(page):
author_id = session['id']
#현재 페이지
currentpage = page
#한페이지에 표시되는 리스트수
pageperitem = 10
pageoffset = pageperitem * (currentpage - 1)
DATA = (author_id, pageperitem, pageoffset)
cursor = db.get_db().cursor()
cursor.execute("SELECT COUNT(*) AS CNT FROM `estimatesheet`")
count = cursor.fetchone()
pagination = Pagination(page=currentpage,
per_page=pageperitem,
total=count['CNT'],
css_framework="bootstrap4")
#글내용보기
cursor = db.get_db().cursor()
cursor.execute(
"SELECT * FROM `estimatesheet` WHERE `a_id` = %s ORDER BY id DESC LIMIT %s OFFSET %s"
% DATA)
rows = cursor.fetchall()
db.get_db().commit()
return render_template('estimatesheet-list.html',
ROWS=rows,
pagination=pagination)
def BusinessInfoWrite():
author_id = session['id']
if request.method == 'POST':
data = request.form.to_dict()
del data['csrf_token']
columns = '`id`,' + '`a_id`,'
values = "NULL," + "'" + str(author_id) + "',"
columns += ', '.join("`" + str(x).replace('/', '_') + "`"
for x in data.keys())
values += ', '.join("'" + str(x).replace('/', '_') + "'"
for x in data.values())
flash(columns)
sql = "INSERT INTO %s (%s) VALUES (%s) " % ('`user-companyinfo`',
columns, values)
# DB 사용
cursor = db.get_db().cursor()
cursor.execute(sql)
db.get_db().commit()
imagefileupload('imagelink', 'business', author_id)
imagefileupload('accountlink', 'account', author_id)
imagefileupload('stamp', 'stamp', author_id)
return redirect(url_for('estimatesheet.root'))
return render_template('estimatesheet-main.html')
def Register():
author_id = session['id']
mother_id = '1'
if request.method == 'POST':
data = request.form.to_dict()
del data['csrf_token']
columns = '`id`,'+'`a_id`,'+'`mother_id`,'
values = "NULL,"+"'"+str(author_id)+"',"+"'"+str(author_id)+"',"
columns += ', '.join("`"+str(x)+"`" for x in data.keys())
values += ', '.join("'"+str(x)+"'" for x in data.values())
sql = "INSERT INTO %s (%s) VALUES (%s)"%('`user-product`',columns, values)
# DB 사용
cursor = db.get_db().cursor()
cursor.execute(sql)
db.get_db().commit()
# flash("저장하였습니다")
flash(sql)
return render_template('product-main.html')
return render_template('product-main.html')
def Withdrawal(id):
# 수정 탈퇴
cursor = db.get_db().cursor()
cursor.execute("UPDATE user SET status = 0 WHERE id =%s",str(id) )
db.get_db().commit()
flash("탈퇴 하였습니다.")
return redirect( url_for('user.Main'))
def insert_message(c_number, c_name, message, c_time, c_YMH, name):
sql = f'''
INSERT INTO {name}(c_id, name, message, time, YMH)
values({c_number},'{c_name}', '{message}', '{c_time}', '{c_YMH}')
'''
cursor = db.get_db().cursor()
cursor.execute(sql)
db.get_db().commit()
cursor.close()
return rows
def Delete(id):
#삭제
cursor = db.get_db().cursor()
cursor.execute("DELETE FROM user WHERE id = %s",str(id))
db.get_db().commit()
flash("사용자 삭제하였습니다.")
return redirect( url_for('user.Main'))
def View(id):
#글내용보기
cursor = db.get_db().cursor()
# cursor.execute("SELECT * FROM comment LEFT JOIN user ON user.id = comment.author_id WHERE comment.parent_id = %s ORDER BY comment.id DESC ",str(id))
sql = "SELECT * FROM `estimatesheet` LEFT JOIN `user-companyinfo` ON `estimatesheet`.a_id = `user-companyinfo`.a_id where `estimatesheet`.id = " + str(
id) + " ORDER BY `user-companyinfo`.id DESC"
cursor.execute(sql)
row = cursor.fetchone()
db.get_db().commit()
return render_template('estimatesheet-view.html', ROW=row)
def RegisterBusinessinfo():
author_id = session['id']
#글내용보기
cursor = db.get_db().cursor()
cursor.execute(
"SELECT * FROM `user-companyinfo` WHERE `a_id` = %s ORDER BY `id` DESC",
author_id)
row = cursor.fetchone()
db.get_db().commit()
return render_template('estimatesheet-register-businessinfo.html', ROW=row)
def Delete_value(c):
#print(c)
csv_read = open(c, 'r', encoding='utf-8')
csv_reader = csv.reader(csv_read)
next(csv_reader)
cursor = db.get_db().cursor()
for row in csv_reader:
sql = '''
DELETE FROM product WHERE handle = %s
'''
cursor.execute(sql, row[0])
db.get_db().commit()
csv_read.close()
cursor.close()
def Update_table_etc(value_new_id, value_new_price,
value_new_option1_value, value_new_revise_times,
value_new_revise_last_time):
sql = '''
UPDATE product Set option1_value =%s, variant_price=%s, revise=%s, revise_last_time=%s where id = %s
'''
cursor = db.get_db().cursor()
cursor.execute(
sql,
(value_new_option1_value, value_new_price, value_new_revise_times,
value_new_revise_last_time, value_new_id))
rows = cursor.fetchall()
db.get_db().commit()
cursor.close()
return rows
def Main(page):
#현재 페이지
currentpage = page
#한페이지에 표시되는 리스트수
pageperitem = 10
pageoffset = pageperitem * (currentpage - 1)
DATA = (pageperitem, pageoffset)
cursor = db.get_db().cursor()
cursor.execute("SELECT COUNT(*) AS CNT FROM bbs")
count = cursor.fetchone()
pagination = Pagination(page=currentpage,
per_page=pageperitem,
total=count['CNT'],
css_framework="bootstrap4")
# 한페이지 리스트 수만큼 게시물 가져오기
cursor.execute(
"SELECT bbs.id, bbs.title, user.name, bbs.date, bbs.count FROM bbs LEFT JOIN user ON user.id = bbs.author_id ORDER BY bbs.id DESC LIMIT %s OFFSET %s"
% DATA)
rows = cursor.fetchall()
cursor.close()
return render_template('bbs-main.html', ROWS=rows, pagination=pagination)
def Insert_value(c):
csv_read = open(c, 'r', encoding='utf-8')
csv_reader = csv.reader(csv_read)
cursor = db.get_db().cursor()
next(csv_reader)
for row in csv_reader:
sql = '''
INSERT INTO product(handle, title, body_HTML, vendor, type, tags, published, option1_name, option1_value, option2_name, option2_value,
option3_name, option3_value, variant_sku, variant_grams, variant_inventory_tracker, variant_inventory_qty, variant_inventory_policy, variant_fullfillment_service, variant_price, variant_compare_at_price, varient_requires_shipping, variant_taxable,
variant_barcord, image_src, image_position, image_alt_text, gift_card, seo_title, seo_description, google_shopping_google_product_category, google_shopping_gender, goggle_shopping_age_group, google_shopping_mpn, google_shopping_adwords_grouping,
google_shopping_adwords_labels, google_shopping_condition, google_shopping_custom_product, google_shopping_custion_label0, google_shopping_custion_label1, google_shopping_custion_label2, google_shopping_custion_label3, google_shopping_custion_label4, variant_image, variant_weight_unit, variant_tax_code, cost_per_item, revise, revise_last_time)
values(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, 0, 0)
'''
cursor.execute(sql, row)
db.get_db().commit()
csv_read.close()
cursor.close()
def Vendor_read():
sql = '''
SELECT DISTINCT vendor FROM product
'''
cursor = db.get_db().cursor()
a = cursor.execute(sql)
rows = cursor.fetchall()
cursor.close()
return rows
def ValueSearch(vendor):
sql = '''
SELECT DISTINCT * FROM product where vendor = %s
'''
cursor = db.get_db().cursor()
a = cursor.execute(sql, vendor)
rows = cursor.fetchall()
cursor.close()
return rows
def HandleSearch(handle):
sql = '''
SELECT * FROM product where handle = %s
'''
cursor = db.get_db().cursor()
cursor.execute(sql, handle)
rows = cursor.fetchall()
cursor.close()
return rows
def select_keyword():
sql = f'''
SELECT name from chatbot_keyword
'''
cursor = db.get_db().cursor()
execute_sql = cursor.execute(sql)
rows = cursor.fetchall()
cursor.close()
return rows
def IndexValueread():
sql = '''
SELECT handle, title, image_src, variant_price FROM product
'''
cursor = db.get_db().cursor()
cursor.execute(sql)
rows = cursor.fetchall()
cursor.close()
return rows
def test():
sql = f'''
select base_keyword from daebak_bot
'''
cursor = db.get_db().cursor()
execute_sql = cursor.execute(sql)
rows = cursor.fetchall()
cursor.close()
return rows
def VendorSearch(vendor_get):
sql = '''
SELECT DISTINCT handle, title FROM product where vendor = %s
'''
cursor = db.get_db().cursor
a = cursor.execute(sql, vendor_get)
rows = cursor.fetchall()
cursor.close()
return rows
def select_content(cont):
sql = f'''
SELECT * from daebak_bot
where base_keyword = "{cont}"
'''
cursor = db.get_db().cursor()
execute_sql = cursor.execute(sql)
rows = cursor.fetchall()
cursor.close()
return rows
def Update():
if request.method == 'POST':
id = request.form['id']
name = request.form['name']
email = request.form['email']
phone = request.form['phone']
DATA = ( name, email, phone, id)
# 수정
cursor = db.get_db().cursor()
cursor.execute("UPDATE user SET name = '%s' , email = '%s', phone = '%s' WHERE id ='%s'" %DATA)
db.get_db().commit()
cursor.close()
flash("사용자 업데이트되었습니다.")
return redirect( url_for('user.Main'))
def select_basic_bot():
sql = f'''
SELECT * from daebak_bot
where id = "1"
'''
cursor = db.get_db().cursor()
execute_sql = cursor.execute(sql)
rows = cursor.fetchall()
cursor.close()
return rows
def Insert():
if request.method == 'POST':
name = request.form['name']
email = request.form['email']
phone = request.form['phone']
password = request.form['password']
pw_hash = bcrypt.generate_password_hash(password).decode('utf-8')
DATA = (name,email,phone,pw_hash)
# 삽입
cursor = db.get_db().cursor()
cursor.execute("INSERT INTO user (id, name, email, phone, password) VALUES (NULL, '%s','%s','%s','%s')" %DATA)
db.get_db().commit()
flash("사용자 추가하였습니다.")
if 'loggedin' in session:
return redirect( url_for('user.Main') )
else:
return redirect( url_for('login.Main'))
def Main():
cursor = db.get_db().cursor()
cursor.execute("SELECT * FROM user WHERE 1 ORDER BY id DESC")
rows = cursor.fetchall()
cursor.close()
return render_template('user.html',ROWS=rows)
def read_table(name, my_id):
sql = f'''
SELECT * from {name}
WHERE c_id = '{my_id}'
'''
cursor = db.get_db().cursor()
execute_sql = cursor.execute(sql)
rows = cursor.fetchall()
cursor.close()
print(rows)
return rows
def View(id):
#글내용보기
cursor = db.get_db().cursor()
cursor.execute(
"SELECT * FROM bbs INNER JOIN user ON user.id = bbs.author_id WHERE bbs.id = %s",
str(id))
row = cursor.fetchone()
row['title'] = html.unescape(row['title'])
row['content'] = html.unescape(row['content'])
db.get_db().commit()
#해당게시물에 대한 덧글 불러오기
cursor.execute(
"SELECT * FROM comment LEFT JOIN user ON user.id = comment.author_id WHERE comment.parent_id = %s ORDER BY comment.id DESC ",
str(id))
rowcomments = cursor.fetchall()
db.get_db().commit()
return render_template('bbs-view.html', ROW=row, ROWCOMMENTS=rowcomments)
def Write_comment():
if request.method == 'POST':
content = request.form['content']
content = html.escape(content)
parent_id = request.form['parent_id']
author_id = session['id']
DATA = (content, parent_id, author_id)
# 삽입
cursor = db.get_db().cursor()
cursor.execute(
"INSERT INTO comment (id, content, date,parent_id,author_id) VALUES (NULL, '%s',CURRENT_TIMESTAMP,'%s','%s')"
% DATA)
db.get_db().commit()
return redirect(url_for('bbs.View', id=parent_id))
return redirect(url_for('bbs.Main'))
def Update(id):
if request.method == 'GET':
#글내용보기
cursor = db.get_db().cursor()
cursor.execute(
"SELECT * FROM bbs INNER JOIN user ON user.id = bbs.author_id WHERE bbs.id = %s",
str(id))
row = cursor.fetchone()
row['title'] = html.unescape(row['title'])
row['content'] = html.unescape(row['content'])
db.get_db().commit()
return render_template('bbs-update.html', ROW=row)
if request.method == 'POST':
title = request.form['title']
title = html.escape(title)
content = request.form['content']
content = html.escape(content)
ID = id
DATA = (title, content, ID)
# 수정
cursor = db.get_db().cursor()
cursor.execute(
"UPDATE bbs SET title = '%s', content = '%s' WHERE id ='%s'" %
DATA)
db.get_db().commit()
flash(title + " 게시글을 수정하였습니다.")
return redirect(url_for('bbs.Main'))
