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))