def deleteStock():
requestJsonData = request.json
db_class = dbModule.Database()
userId = check_session()
if userId is None:
app.logger.info("::::::: Session End ::::::: \n")
return 'End'
# sql = "DELETE FROM UserStockList \
# WHERE user_Id='" + userId + "' AND list_Acode='" + requestJsonData['id'] + "'"
sql = "UPDATE UserStockList \
SET status='D'\
WHERE user_Id='" + userId + "' AND list_Acode='" + requestJsonData[
'id'] + "'"
db_class.execute(sql)
db_class.commit()
db_class.close()
return ""
def interBook(page): # get 방식으로 page를 받아 페이징을 함
db_class = dbModule.Database()
print(page)
results = db_class.load(page)
results = json.loads(results)
pprint(results)
return jsonify(results)
def home():
'''sesssion control'''
'''
if not session.get('logged_in'):
print(session.get('logged_in'))
print("여기요1")
return render_template('MMG.html')
else:
print("여기요2")
if request.method=='POST':
username=request.form['id']
return render_template('MMG.html',id=username)
return render_template('MMG.html')
'''
if request.method == 'GET':
print("여기요3")
return render_template('login.html')
elif request.method == 'POST':
username = request.form['id']
password = request.form['pwd']
db_class = dbModule.Database()
# SELECT * FROM user where password = 12345678
sql = "SELECT * FROM testDB.user WHERE user_name = '" + username + "' AND password = " + password
print(sql)
user_list = db_class.executeAll(sql)
print(user_list)
try:
if user_list is not None:
return redirect(url_for('recommendation', id=username))
else:
return redirect(url_for('home'))
except:
return redirect(url_for('home'))
def getCurrentValue():
requestJsonData = request.json
db_class = dbModule.Database()
userId = check_session()
if userId is None:
app.logger.info("::::::: Session End ::::::: \n")
return 'End'
app.logger.info("::::::: getCurrentValue ::::::: \n %s", userId)
sql = "INSERT INTO UserStockList (user_Id, list_Code, list_Acode, list_Name, status, date) \
SELECT user_Id, '" + requestJsonData[
'displayedCode'] + "' as 'list_Code', '" + requestJsonData[
'id'] + "' as 'list_Acode', '" + requestJsonData[
'value'] + "' as 'list_Name', \
'I' as 'status', NOW() as 'date' \
FROM User WHERE user_Id = '" + userId + "'"
print(sql)
db_class.execute(sql)
db_class.commit()
db_class.close()
return generateChartDivTag(requestJsonData)
def dep_modal_submit():
if request.method == 'POST':
flight_id = request.form['bookId']
url = 'http://openapi.airport.kr/openapi/service/StatusOfPassengerFlights/getPassengerDepartures?ServiceKey=66063YTIfEiCLfP6GQaefJOoCIAGlG79bHmFOdBZ%2BrGEZ%2B6syoDTCimut6%2F%2FIXO2WF%2FP5m6KrYPnF16DvsPgfQ%3D%3D&from_time=0000&to_time=2400&airport=&'
url += 'flight_id='
url += flight_id
req = requests.get(url)
html = req.text
soup = BeautifulSoup(html, 'html.parser')
airline = ''
flightid = ''
scheduledatetime = ''
airport = ''
chkinrange = ''
gatenumber = ''
remark = ''
terminalid = ''
if soup.find('airline') != None:
airline = soup.find('airline').text
if soup.find('flightid') != None:
flightid = soup.find('flightid').text
if soup.find('scheduledatetime') != None:
scheduledatetime = soup.find('scheduledatetime').text
if soup.find('airport') != None:
airport = soup.find('airport').text
if soup.find('chkinrange') != None:
chkinrange = soup.find('chkinrange').text
if soup.find('gatenumber') != None:
gatenumber = soup.find('gatenumber').text
if soup.find('remark') != None:
remark = soup.find('remark').text
if soup.find('terminalid') != None:
terminalid = soup.find('terminalid').text
else:
flight_id = None
airplane = None
db_class = dbModule.Database()
p_id = session.get('logged_in')
sql = "UPDATE Airport.Departure \
SET flightid=%s, airline=%s, scheduledatetime=%s, airport=%s, chkinrange=%s, gatenumber=%s, remark=%s, terminalid=%s \
WHERE f_id=%s"
db_class.execute(sql,
(flightid, airline, scheduledatetime, airport,
chkinrange, gatenumber, remark, terminalid, p_id))
db_class.commit()
sql = "SELECT * FROM Airport.Departure WHERE f_id=%s"
row = db_class.executeAll(sql, (p_id))
db_class.commit()
return render_template('index.html', resultData=row[0])
def search():
db_class = dbModule.Database()
results = db_class.find(request.form['keyword']) # form 태그에서 넘어온 keyword로 find함수 실행
try:
results = json.loads(results)
pprint(results)
return render_template("search.html", json=results)# json 변수에 결과값을 담아 search.html로 이동
except UndefinedError: # 검색 결과가 없을때 예외처리
return "검색어를 잘못 입력하셨습니다."
def mypage_flight():
db_class = dbModule.Database()
p_id = session.get('logged_in')
sql = "SELECT * FROM Airport.Departure WHERE f_id=%s"
row = db_class.executeAll(sql, (p_id))
db_class.commit()
return render_template('reg_flight.html', resultData=row[0])
def mypage_route():
db_class = dbModule.Database()
p_id = session.get('logged_in')
sql = "SELECT * FROM Airport.route WHERE r_id=%s"
row = db_class.executeAll(sql, (p_id))
db_class.commit()
row_len = len(row)
return render_template('reg_route.html', resultData=row, row_len=row_len)
def insert():
db_class = dbModule.Database()
sql = "INSERT INTO testDB.testTable(test) VALUES('%s')" % ('testData')
db_class.execute(sql)
db_class.commit()
return render_template('test.html',
result='insert is done!',
resultData=None,
resultUPDATE=None)
def select():
db_class = dbModule.Database()
sql = "SELECT idx, test " \
" FROM testDB.testTable"
row = db_class.executeAll(sql)
print(row)
return render_template('/test/test.html',
result=None,
resultData=row[0],
resultUPDATE=None)
def home():
if not session.get('logged_in'):
return render_template('login.html')
else:
db_class = dbModule.Database()
p_id = session.get('logged_in')
sql = "SELECT * FROM Airport.Departure WHERE f_id=%s"
row = db_class.executeAll(sql, (p_id))
db_class.commit()
return render_template('index.html', resultData=row[0])
def index():
db_class = dbModule.Database()
sql = "SELECT title, content, url \
FROM nlpg$nlpg.news \
ORDER By id DESC \
LIMIT 10;"
row = db_class.executeAll(sql)
print(row)
return render_template('index.html', resultData=row[:11])
def logout():
db_class = dbModule.Database()
p_id = session.get('logged_in')
sql = "DELETE FROM Airport.Departure WHERE f_id=%s"
row = db_class.executeAll(sql, (p_id))
db_class.commit()
sql = "DELETE FROM Airport.customer WHERE p_id=%s"
row = db_class.executeAll(sql, (p_id))
db_class.commit()
session.pop('logged_in', None)
return render_template('login.html')
def register():
if request.method == 'POST':
user = request.form['materialLoginFormPhone']
if user != None:
session['logged_in'] = user
db_class = dbModule.Database()
sql = "INSERT IGNORE INTO Airport.customer VALUES(%s);"
db_class.execute(sql, (user))
db_class.commit()
sql = "INSERT IGNORE INTO Airport.Departure(f_id) VALUES(%s)"
db_class.execute(sql, (user))
db_class.commit()
return redirect(url_for('home'))
def getRandomFoodList():
db_class = dbModule.Database()
sql = "SELECT * FROM testDB.food_list"
row = db_class.executeAll(sql)
random_var = random.randrange(1,len(row))
#print(random_var)
result = []
count = 0
while(count<20):
random_var = random.randrange(1, len(row))
if row[random_var] not in result:
result.append(row[random_var])
count = count+1
for i in range(len(result)):
result[i] = result[i]['name']
return result
def update():
db_class = dbModule.Database()
sql = "UPDATE testDB.testTable" \
" SET test='%s'" \
" WHERE test='testData'" % 'update_Data'
db_class.execute(sql)
db_class.commit()
sql = "SELECT idx, test" \
" FROM testDB.testTable"
row = db_class.executeAll(sql)
return render_template('/test/test.html',
result=None,
resultData=None,
resultUPDATE=row[0])
def del_route():
if request.method == 'POST':
r_id = session.get('logged_in')
route = request.form['route']
db_class = dbModule.Database()
sql = "DELETE FROM Airport.route WHERE r_id=%s and route=%s"
db_class.execute(sql, (r_id, route))
db_class.commit()
sql = "SELECT * FROM Airport.route WHERE r_id=%s"
row = db_class.executeAll(sql, (r_id))
db_class.commit()
row_len = len(row)
return render_template('reg_route.html', resultData=row, row_len=row_len)
def recentHum():
choice_id = request.args.get('choice_id') #작물선택 기본키
# 가장 최근 기록된 습도 반환
db_class = dbModule.Database()
sql = "select r.rb_humidity from raspberry as r \
join plant_choice p \
on " + choice_id + " = r.choice_id \
where p.grow_flag = 1 \
order by r.rb_create limit 1"
row = db_class.executeAll(sql)
print("/recent-hum 결과", row)
return row[0]
def foodEvaluation():
# print(guser['list'])
esti = request.form['score']
print("여기를 지납니다만/evaluation")
temp_user = {'user_name': guser['id'],'food_name':guser['foodname'],'esti_time': guser['time'], 'cloud':guser['weather'], 'RorS': guser['RorS'],
'temp': guser['temperature'], 'emotion': guser['emotion'],'esti':esti}
db_class = dbModule.Database()
sql = "INSERT INTO testDB.food_evaluation(user_name, food_name, esti_time, cloud, RorS, temp, emotion, esti) " \
"VALUES('" + guser['id'] + "','" +guser['foodname']+ "','"+ repr(guser['time'])+ "','"+repr(guser['weather'])+ \
"','"+guser['RorS']+ "','"+repr(guser['temperature'])+ "','"+repr(guser['emotion'])+ "','"+esti+ "')"
print(sql)
db_class.executeAll(sql)
db_class.commit()
esti_time = datetime.now().hour
real_location = guser['location']
temperature = guser['temperature']
weather = guser['weather']
# return render_template('foodforme.html', recommendation_result=result,id=guser['id'])
#return redirect(url_for('recommendation_onemore', recommendation_result=rec_result_temp, id=guser['id'], user_location=real_location,temperature=temperature, weather=weather, count=guser['count'], cur_time=esti_time))
return render_template('foodforme.html', recommendation_result='', id=guser['id'], user_location=real_location, temperature=temperature, weather=weather,count = guser['count'],cur_time = esti_time)
def tempAndHum():
choice_id = request.args.get('choice_id') #작물선택 기본키
# 온/습도/시간 반환
db_class = dbModule.Database()
sql = "select r.rb_temperature, r.rb_humidity, r.rb_create from raspberry as r \
join plant_choice p \
on " + choice_id + " = r.choice_id \
where p.grow_flag = 1 \
order by r.rb_create"
row = db_class.executeAll(sql)
for datas in row:
t = datas["rb_create"]
datas["rb_create"] = t.strftime('%Y-%m-%d %H:%M:%S')
print("결과", row)
result = json.dumps(row, indent=3)
return result
def supplyWaterLogs():
choice_id = request.args.get('choice_id') #작물선택 기본키
# 가장 최근 급수 시간 출력
db_class = dbModule.Database()
sql = "select w.watering_time from watering_log as w \
join plant_choice p \
on " + choice_id + " = w.choice_id \
where p.grow_flag = 1 \
order by w.watering_time desc \
limit 1"
row = db_class.executeAll(sql)
t = row[0]["watering_time"]
row[0]["watering_time"] = t.strftime('%Y-%m-%d %H:%M:%S')
print("/supply-water-logs의 결과", row)
return row[0]
def recentImgs():
choice_id = request.args.get('choice_id') # 작물선택 기본키
# 가장 최근에 찍힌 사진 10개만 리턴
db_class = dbModule.Database()
sql = "select r.rb_img from raspberry as r \
join plant_choice p \
on " + choice_id + " = r.choice_id \
where p.grow_flag = 1 \
order by r.rb_create desc \
limit 10"
row = db_class.executeAll(sql)
for data in row:
data['rb_img'] = "/static/data/img/" + data['rb_img'] + ".jpg"
print(data['rb_img'])
print("/recent-imgs의 결과", row)
return json.dumps(row)
def save_route():
if request.method == 'POST':
r_id = session.get('logged_in')
route = request.form['route']
db_class = dbModule.Database()
sql = "INSERT INTO Airport.route(r_id,route) VALUES(%s, %s)"
db_class.execute(sql, (r_id, route))
db_class.commit()
global route_start
global route_end
global way_length
global num_middle
global send
return render_template('map1.html',
route_start=route_start,
route_end=route_end,
way_length=way_length,
num_middle=num_middle,
send=send)
def home():
userId = check_session()
if userId is None:
app.logger.info("::::::: Session End ::::::: \n")
return render_template('login.html')
db_class = dbModule.Database()
sql = "SELECT list_Code as displayedCode, list_Name as value, list_Acode as id, user_Id as auth \
FROM UserStockList\
WHERE "
if userId == 'admin':
sql += "status='I'"
else:
sql += "user_Id = '" + userId + "' AND status='I'"
sql += "ORDER BY user_Id"
row = db_class.executeAll(sql)
tags = ''
for r in row:
r['displayedCode'] = r['displayedCode'].decode('utf8', 'ignore')
r['value'] = r['value'].decode('utf8', 'ignore')
r['id'] = r['id'].decode('utf8', 'ignore')
r['auth'] = r['auth'].decode('utf8', 'ignore')
r['userId'] = userId
tags += generateChartDivTag(r)
app.logger.info("::::::: result for UserStockList table ::::::: \n %s",
row)
app.logger.info("::::::: result for Generated Tags ::::::: \n %s", tags)
db_class.close()
return render_template('home.html', resultData=tags, userid=userId)
def dayImgs():
choice_id = request.args.get('choice_id') # 작물선택 기본키
day = request.args.get('day')
# 가장 최근에 찍힌 사진 10개만 리턴
print("날짜", day)
db_class = dbModule.Database()
sql = '''select r.rb_img from raspberry as r \
join plant_choice p \
on ''' + choice_id + ''' = r.choice_id \
where p.grow_flag = 1 \
and r.rb_img LIKE '%%%%%s%%%%' \
order by r.rb_create desc''' % (day)
print(sql)
row = db_class.executeAll(sql)
for data in row:
data['rb_img'] = "/static/data/img/" + data['rb_img'] + ".jpg"
print(data['rb_img'])
print("/day-imgs의 결과", row)
return json.dumps(row)
def login():
'''Login Form'''
if request.method == 'GET':
print("여기요3")
return render_template('login.html')
elif request.method == 'POST':
print("여기요4")
username = request.form['id']
password = request.form['pwd']
db_class = dbModule.Database()
#SELECT * FROM user where password = 12345678
sql = "SELECT * FROM testDB.user WHERE user_name = '" + username + "' AND password = " + password
print(sql)
user_list = db_class.executeAll(sql)
print(user_list)
try:
if user_list is not None:
session['logged_in'] = True
return redirect(url_for('home', id=username))
else:
return redirect(url_for('home'))
except:
return redirect(url_for('home'))
def getFoodEvaluationlist():#to make dictionary
db_class = dbModule.Database()
sql = "SELECT * FROM testDB.food_evaluation"
row = db_class.executeAll(sql)
local_food_evaluation_list = row
# print(local_food_evaluation_list)
sql = "SELECT distinct user_name FROM testDB.food_evaluation"
row = db_class.executeAll(sql)
user_list = row
# print(user_list)
temp = {}
#print(temp)
# 함수화 시켜서 따로 빼도록 하자
# 사용자별로 구분하여 딕셔너리로 만들기
for each_user in user_list:
temp[each_user['user_name']] = {}
keys_of_list = ['food_name', 'esti_time', 'cloud', 'RorS', 'temp', 'emotion', 'esti']
for each_esti in local_food_evaluation_list:
temp[each_esti['user_name']][each_esti['food_name']] = {}
for i in keys_of_list:
temp[each_esti['user_name']][each_esti['food_name']][i] = each_esti[i]
return temp
def __init__(self,
id,
password,
username='******',
userclass='none',
userclasscode='02',
userphone='none',
useremail='none'):
self.user_id = id
self.user_name = username
self.password = password
self.user_class = userclass
self.user_class_code = userclasscode
self.user_phone = userphone
self.user_email = useremail
self.conn = dbModule.Database()
def index():
print("index(), /")
guser['location']=None
guser['count']=1
guser['eval_history']=None
guser['id']=(request.form['id'])
db_class = dbModule.Database()
sql = "SELECT * FROM testDB.user WHERE user_name = '" + guser['id'] + "'"
user_list = db_class.executeAll(sql)
if len(user_list)==0:
sql = "INSERT INTO testDB.user(user_name) VALUES('" +guser['id']+ "')"
db_class.executeAll(sql)
db_class.commit()
sql = "SELECT * FROM testDB.food_evaluation WHERE user_name = '" + guser['id'] + "'"
eval_user = db_class.executeAll(sql)
guser['eval_history']=eval_user
getStatus()
esti_time = datetime.now().hour#현재시간 html로 전달
real_location = guser['location']
temperature = guser['temperature']
weather = guser['weather']
return render_template('foodforme.html', id=guser['id'],user_location = real_location,temperature=temperature,weather=weather,count=guser['count'],cur_time = esti_time)
def __init__(self):
self.db_class = dbModule.Database()
