def select_detail_tot_shoping(self, token_no):
try:
query = """
SELECT
FORMAT(SUM(B.PD_CNT * C.PROD_PRICE),0) TOT_PROD_PRICE
FROM
(
SELECT
A.SHOP_NO
,A.PD_NO
,SUM(A.PD_CNT) PD_CNT
FROM TB_DETAIL_SHOPING_RETAIL A
WHERE 1=1
AND A.SHOP_NO = '%s'
GROUP BY A.SHOP_NO ,A.PD_NO
) B,
TB_PROD_RETAIL C
WHERE 1=1
AND B.PD_NO = C.PROD_SEQ
AND B.PD_CNT > 0
ORDER BY B.PD_NO ; """ % (token_no)
self.curs.execute(query)
result = self.curs.fetchone()
result = result[0]
return result
except Exception as e:
logger.error("select_detail_tot_shoping SQL : {}".format(e))
def insert_store_log(self, ip_addr, _id, rq_path, rq_result):
try:
query = "INSERT INTO TB_STORE_USER_LOG(IP, ACTION_USER, ACTION_URL, ACTION_RESULT) VALUES('%s', '%s', '%s', '%s')" % (
ip_addr, _id, rq_path, rq_result)
self.curs.execute(query)
self.con.commit()
except Exception as e:
logger.error("insert_store_log SQL : {}".format(e))
def update_pwdcnt_when_access(self, _id):
try:
query = "update TB_STORE_WEB_USER set PWD_WRONG_CNT = 0 where ID = '%s'" % (
_id)
self.curs.execute(query)
self.con.commit()
except Exception as e:
logger.error("update_pwdcnt_when_access SQL : {}".format(e))
def update_last_access(self, _id):
try:
query = "update TB_STORE_WEB_USER set LAST_ACCESS = now() where ID = '%s'" % (
_id)
self.curs.execute(query)
self.con.commit()
except Exception as e:
logger.error("update_last_access SQL : {}".format(e))
def update_pwd_wrong_cnt(self, _id, _pwd):
try:
if self.is_exist_ID(_id):
query = "update TB_STORE_WEB_USER set PWD_WRONG_CNT = PWD_WRONG_CNT+1 where id = '%s';" % (
_id)
self.curs.execute(query)
self.con.commit()
except Exception as e:
logger.error("update_pwd_wrong_cnt SQL : {}".format(e))
def is_exist_ID(self, _id):
try:
query = "SELECT * FROM TB_STORE_WEB_USER WHERE ID = '%s'" % (_id)
print("exist_id ", query)
self.curs.execute(query)
result = self.curs.fetchall()
return result
except Exception as e:
logger.error("is_exist_ID SQL : {}".format(e))
def select_admin_info(self, _id, _pwd):
try:
query = "SELECT ID,PWD,USE_YN,INPUT_ID, INPUT_DT,LAST_PWD_CH_DT,PWD_WRONG_CNT,USER_NAME,LOC_CODE,TO_DAYS(now())-TO_DAYS(LAST_PWD_CH_DT) AS LAST_PWD_CH_FROM_NOW, TO_DAYS(now())-TO_DAYS(LAST_ACCESS) AS LAST_ACCESS_FROM_NOW, USER_TYPE FROM TB_STORE_WEB_USER WHERE ID = '%s' and PWD = sha2('%s',256) " % (
_id, _pwd)
self.curs.execute(query)
result = self.curs.fetchall()
return result
except Exception as e:
logger.error("select_admin_info SQL : {}".format(e))
def select_products(self):
try:
query = """ SELECT PROD_NM,PROD_IMG,FORMAT(PROD_PRICE,0) FROM TB_PROD_RETAIL WHERE PROD_SEQ > 1 ; """
self.curs.execute(query)
result = self.curs.fetchall()
return result
except Exception as e:
logger.error("select_products SQL : {}".format(e))
def exist_id():
if request.method == 'POST':
_id = request.form.get('_id')
try:
with Database() as db:
exist_id = db.select_exist_id(_id) # 아이디 중복확인
except Exception as e:
logger.error("exist_id : {}".format(e))
return jsonify(result=exist_id)
def select_exist_id(self, _id):
try:
query = "SELECT COUNT(*) CNT FROM TB_STORE_WEB_USER WHERE ID = '%s' " % (
_id)
self.curs.execute(query)
result = self.curs.fetchone()
result = result[0]
return result
except Exception as e:
logger.error("select_exist_id SQL : {}".format(e))
def insert_signup(self, _id, _name, _password):
try:
query = "INSERT INTO TB_STORE_WEB_USER(ID,PWD,INPUT_ID,USER_NAME) VALUES('%s',sha2('%s',256),'%s','%s')" % (
_id, _password, _id, _name)
self.curs.execute(query)
self.con.commit()
result = "success"
return result
except Exception as e:
logger.error("insert_signup SQL : {}".format(e))
def change_pwd(self, _id, _pwd):
try:
query = "update TB_STORE_WEB_USER set PWD = sha2('%s',256), LAST_PWD_CH_DT = now() where ID = '%s'" % (
_pwd, _id)
self.curs.execute(query)
self.con.commit()
result = "success"
return result
except Exception as e:
logger.error("change_pwd SQL : {}".format(e))
def get_pwd_wrong_cnt(self, _id):
try:
self.curs.execute(
"SELECT PWD_WRONG_CNT FROM TB_STORE_WEB_USER WHERE ID = '%s'" %
(_id))
result = self.curs.fetchall()
return result[0][0]
except Exception as e:
logger.error("get_pwd_wrong_cnt SQL : {}".format(e))
def storemap():
if 'id' not in session:
return redirect(url_for('login'))
try:
pass
except Exception as e:
logger.error("storemap : {}".format(e))
return render_template('storemap.html')
def index():
if 'id' not in session:
return redirect(url_for('login'))
try:
with Database() as db:
pass
except Exception as e:
logger.error("index : {}".format(e))
return render_template('index.html', user_name=session['name'])
def products():
if 'id' not in session:
return redirect(url_for('login'))
try:
with Database() as db:
items = db.select_products()
except Exception as e:
logger.error("products : {}".format(e))
return render_template('products.html', items=items)
def receipts():
if 'id' not in session:
return redirect(url_for('login'))
try:
with Database() as db:
items = db.select_main_shoping(session['id'])
except Exception as e:
logger.error("receipts : {}".format(e))
return render_template('receipts.html', items=items)
def receipt_detail():
if 'id' not in session:
return redirect(url_for('login'))
try:
token_no = request.form.get('token_no')
with Database() as db:
items = db.select_detail_shoping(token_no)
tot = db.select_detail_tot_shoping(token_no)
except Exception as e:
logger.error("receipts : {}".format(e))
return render_template('receipt_detail.html', items=items, tot=tot)
def signup():
if request.method == 'POST':
_id = request.form.get('_id')
_name = request.form.get('_name')
_password = request.form.get('_password')
try:
with Database() as db:
res = db.insert_signup(_id, _name, _password)
session['id'] = _id
session['name'] = _name
except Exception as e:
logger.error("signup : {}".format(e))
return res
return render_template('signup.html')
def login():
if request.method == 'POST':
_id = request.form.get('_id')
_password = request.form.get('_password')
logger.info("_id : {}".format(_id))
logger.info("_password : {}".format(_password))
login_msgs = {
'empty': '빈칸이 하나라도 있으면 안됩니다.',
'wrong_pwd_or_id': '아이디 혹은 패스워드가 잘못되었습니다.',
'pwd_wrong_max':
'비밀번호를 ' + str(WRONG_PWD_MAX_CNT) + '회 이상 틀렸습니다. 관리자에게 문의해주세요.',
'need_change_pwd': '비밀번호를 변경해주세요.',
'isN': '사용이 정지된 계정입니다. 관리자에게 문의하세요.',
'ch_pwd': '90일이 지나서 비밀번호를 변경하셔야 합니다. 비밀번호를 변경해주세요.',
'expired': '마지막 사용 후 90일이 지났습니다. 관리자에게 문의하세요.'
}
try:
with Database() as db:
res = db.select_admin_info(_id, _password)
if not res:
db.update_pwd_wrong_cnt(_id, _password)
msg = 'wrong_pwd_or_id'
elif res[0][6] >= WRONG_PWD_MAX_CNT:
msg = 'pwd_wrong_max'
elif res[0][4] == res[0][5]:
session['id'] = _id
session['name'] = res[0][7]
msg = 'need_change_pwd'
elif res[0][2] == 'N':
msg = 'isN'
elif res[0][9] > CH_PWD_DAY:
msg = 'ch_pwd'
elif res[0][10] > EXPIRE_DAY:
msg = 'expired'
else:
msg = 'success'
db.update_last_access(_id)
db.update_pwdcnt_when_access(_id)
session['id'] = _id
session['name'] = res[0][7]
result = msg
system_log(request.remote_addr, _id, request.full_path, result)
except Exception as e:
logger.error("login_action : {}".format(e))
return result
return render_template('login.html')
def __init__(self):
host = MYSQL_HOST
port = int(MYSQL_PORT)
user = MYSQL_USER
password = MYSQL_PASSWD
db = MYSQL_DB
charset = 'utf8'
try:
self.con = pymysql.connect(host=host,
port=port,
user=user,
password=password,
db=db,
charset=charset)
self.curs = self.con.cursor()
except Exception as e:
logger.error("pymysql.connect : {}".format(e))
def is_correct_idpwd(self, _id, _pwd):
try:
query = "SELECT * FROM TB_STORE_WEB_USER WHERE ID = '%s' and PWD = sha2('%s',256)" % (
_id, _pwd)
self.curs.execute(query)
result = self.curs.fetchall()
if result:
# wrong pwd cnt =0으로 초기화
self.curs.execute(
"update TB_STORE_WEB_USER set PWD_WRONG_CNT = 0 where id = '%s';"
% (_id))
return True
else:
# wrong pwd cnt +=1
self.curs.execute(
"update TB_STORE_WEB_USER set PWD_WRONG_CNT = PWD_WRONG_CNT+1 where id = '%s';"
% (_id))
return False
except Exception as e:
logger.error("is_correct_idpwd SQL : {}".format(e))
def select_main_shoping(self, id):
try:
query = """
SELECT
A.SHOP_NO
,A.ID
,FORMAT(SUM(B.PD_CNT),0) AS PD_CNT
,DAYNAME(A.INPUT_DT) D1
,DATE_FORMAT(A.INPUT_DT,'%%b %%d') D2
,DATE_FORMAT(A.INPUT_DT,'%%h:%%i %%p') D3
FROM TB_MAIN_SHOPING_RETAIL A,
TB_DETAIL_SHOPING_RETAIL B
WHERE 1=1
AND A.SHOP_NO = B.SHOP_NO
AND A.ID = CAST('%s' AS UNSIGNED)
GROUP BY A.SHOP_NO,A.ID, DAYNAME(A.INPUT_DT),DATE_FORMAT(A.INPUT_DT,'%%b %%d'),DATE_FORMAT(A.INPUT_DT,'%%h:%%i %%p')
ORDER BY SHOP_NO DESC ; """ % (id)
self.curs.execute(query)
result = self.curs.fetchall()
return result
except Exception as e:
logger.error("select_main_shoping SQL : {}".format(e))
def chpwd():
if request.method == 'POST':
_id = request.form['_id']
_oldpassword = request.form['_oldpassword']
_password = request.form['_password']
chpwd_msgs = {
'empty': '빈칸이 하나라도 있으면 안됩니다.',
'wrong_pwd_or_id': '아이디 혹은 패스워드가 잘못되었습니다.',
'pwd_wrong_max':
'비밀번호를 ' + str(WRONG_PWD_MAX_CNT) + '회 이상 틀렸습니다. 관리자에게 문의해주세요.',
'success': '비밀번호가 성공적으로 변경되었습니다.'
}
try:
with Database() as db:
# 어차피 프론트에서 검사하고 넘어오긴함.
if _password == '' or _oldpassword == '' or _id == '':
result = 'empty'
return result
elif not db.is_correct_idpwd(_id, _oldpassword):
result = 'wrong_pwd_or_id'
return result
elif db.get_pwd_wrong_cnt(_id) >= WRONG_PWD_MAX_CNT:
result = 'pwd_wrong_max'
return result
else:
db.change_pwd(_id, _password)
msg = 'success'
result = msg
system_log(request.remote_addr, _id, request.full_path, result)
except Exception as e:
logger.error("chpwd : {}".format(e))
return result
return render_template('chpwd.html',
id=session['id'],
name=session['name'])
def system_log(ip, id, action_url, result):
try:
with Database() as db:
db.insert_store_log(ip, id, action_url, result)
except Exception as e:
logger.error("system_log : {}".format(e))
