def put(self, work_seq):
try:
db = dbHelper()
# Authorization 헤더로 담음
header = request.headers.get('Authorization')
if header is None:
return {"code": "err", "message": "Not Allow Authorization"}
try:
data = jwt.decode(header, "secret", algorithms=["HS256"])
except Exception as e:
return {"code": "err", "message": "Token Expired"}
parser = reqparse.RequestParser()
parser.add_argument('user_seq', type=int)
args = parser.parse_args()
sql = "UPDATE volunteer SET volunteer_yn = %s isDeleted = %s WHERE work_seq = %s AND user_seq = %s AND volunteer_yn = 'Y'"
db.cursor.execute(sql, ('N', work_seq, args['user_seq'], 'Y'))
# sql = "UPDATE work SET work_recruit = work_recruit + 1 WHERE work_seq = %s"
# db.cursor.execute(sql, work_seq)
db.conn.commit()
except Exception as e:
return {"code": "err", "message": str(e)}
return {"code": "success"}
def get(self, work_seq):
try:
db = dbHelper()
header = request.headers.get('Authorization')
if header is None:
return {"code": "err", "message": "Not Allow Authorization"}
try:
data = jwt.decode(header, "secret", algorithms=["HS256"])
except Exception as e:
return {"code": "err", "message": "Token Expired"}
sql = "SELECT a.volunteer_seq, a.user_seq, b.user_nm, b.user_gender, a.volunteer_content FROM volunteer a, user b WHERE a.user_seq = b.user_seq AND work_seq = %s;"
db.cursor.execute(sql, work_seq)
result = db.cursor.fetchall()
print(result)
if result is None:
return {"code": "err", "message": "Invalid Volunteer"}
except Exception as e:
return {"code": "err", "message": str(e)}
return {"code": "success", "data": result}
def setInterest(user_seq, work_type_seq):
db = dbHelper()
sql = "INSERT INTO user_interest (user_seq, work_type_seq) VALUES (%s, %s);"
db.cursor.execute(sql, (user_seq, work_type_seq))
db.conn.commit()
return True
def post(self):
try:
db = dbHelper()
parser = reqparse.RequestParser()
parser.add_argument('user_email', type=str)
parser.add_argument('user_pw', type=str)
args = parser.parse_args()
user_email = args['user_email']
user_pw = encrypt(args['user_pw'])
sql = "SELECT user_seq, user_nm FROM user WHERE user_email = %s AND user_pw = %s;"
db.cursor.execute(sql, (user_email, user_pw))
result = db.cursor.fetchone()
if result == None:
return {"code": "err", "message": "Invalid Account"}
else:
encoded_jwt = jwt.encode(result, "secret", algorithm="HS256")
return {"code": "success", "data": {"token": encoded_jwt}}
except Exception as e:
return {"code": "err", "message": str(e)}
return {"code": "success", "data": "token"}
def post(self):
try:
db = dbHelper()
parser = reqparse.RequestParser()
parser.add_argument('user_seq', type=int)
args = parser.parse_args()
sql = "SELECT TIMESTAMPDIFF(minute, now(), MAX(a.work_endAt)) AS deadline FROM bongo.work a, bongo.volunteer b WHERE a.work_seq = b.work_seq AND b.user_seq = %s ORDER BY a.work_endAt ASC"
db.cursor.execute(sql, args['user_seq'])
result = db.cursor.fetchall()
# JSON오류..
# sql = "SELECT a.work_seq, a.work_title, a.work_pay, a.work_startAt, a.work_endAt FROM bongo.work a, bongo.volunteer b WHERE a.work_startAt < now() AND a.work_seq = b.work_seq AND b.user_seq = %s;"
#
# db.cursor.execute(sql, args['user_seq'])
result1 = db.cursor.fetchall()
# for i in result1:
# print(result1(i))
except Exception as e:
return {"code": "err", "message": str(e)}
return {"code": "success", "data": result}
def resetInterest(user_seq):
db = dbHelper()
sql = "DELETE FROM user_interest WHERE user_seq = %s;"
db.cursor.execute(sql, (user_seq))
db.conn.commit()
return True
def post(self):
try:
db = dbHelper()
parser = reqparse.RequestParser()
parser.add_argument('user_id', type=str)
args = parser.parse_args()
__userID = args['user_id']
if validEmail(__userID):
# 중복 존재 X
return {
"code": "success",
"data": {
"isDuplicate": "n"
},
"message": "n"
}
else:
# 중복 존재
return {
"code": "success",
"data": {
"isDuplicate": "y"
},
"message": "y"
}
except Exception as e:
return {"code": "err", "message": str(e)}
return {"code": "success", "data": "token"}
def get(self, user_seq):
try:
db = dbHelper()
# Authorization 헤더로 담음
header = request.headers.get('Authorization')
if header is None:
return {"code": "err", "message": "Not Allow Authorization"}
try:
data = jwt.decode(header, "secret", algorithms=["HS256"])
except Exception as e:
return {"code": "err", "message": "Token Expired"}
sql = "SELECT * FROM user WHERE user_seq = %s;"
db.cursor.execute(sql, (user_seq))
result = db.cursor.fetchone()
if result == None:
return {"code": "err", "message": "Invalid User"}
profile_data = {
"user_nm": result['user_nm'],
"user_email": result['user_email'],
"user_birth": str(result['user_birth'])
}
except Exception as e:
return {"code": "err", "message": str(e)}
return {"code": "success", "data": profile_data}
def post(self):
try:
db = dbHelper()
parser = reqparse.RequestParser()
parser.add_argument('user_banknm', type=str)
parser.add_argument('user_account', type=str)
parser.add_argument('user_intro', type=str)
args = parser.parse_args()
header = request.headers.get('Authorization')
if header is None:
return {"code": "err", "message": "Not Allow Authorization"}
try:
data = jwt.decode(header, "secret", algorithms=["HS256"])
except Exception as e:
return {"code": "err", "message": "Token Expired"}
user_seq = data['user_seq']
# 프로필 정보 등록
updatedProfile = []
for key in args:
if args[key] != None and key != 'token':
setProfile(key, args[key], user_seq)
updatedProfile.append(key)
except Exception as e:
return {"code": "err", "message": str(e)}
return {"code": "success", "data": {"updated": updatedProfile}}
def post(self):
try:
db = dbHelper()
parser = reqparse.RequestParser()
parser.add_argument('interests', type=int, action='append')
args = parser.parse_args()
header = request.headers.get('Authorization')
if header is None:
return {"code": "err", "message": "Not Allow Authorization"}
try:
data = jwt.decode(header, "secret", algorithms=["HS256"])
except Exception as e:
return {"code": "err", "message": "Token Expired"}
user_seq = data['user_seq']
interests = args['interests']
# 관심분야 초기화
resetInterest(user_seq)
# 관심분야 추가
for work_type_seq in interests:
setInterest(user_seq, work_type_seq)
except Exception as e:
return {"code": "err", "message": str(e)}
return {"code": "success", "data": {"updated": interests}}
def get(self, work_seq):
try:
db = dbHelper()
sql = "SELECT * FROM work a, work_type b, company c WHERE c.company_seq = a.company_seq AND a.work_type_seq = b.work_type_seq AND a.work_seq = %s"
db.cursor.execute(sql, work_seq)
result = db.cursor.fetchone()
data = {
"work_seq": result['work_seq'],
"work_type_nm": result['work_type_nm'],
"company_seq": result['company_seq'],
"company_nm": result['company_nm'],
"work_title": result['work_title'],
"work_intro": result['work_intro'],
"work_img": result['work_img'],
"work_startAt": str(result['work_startAt']),
"work_endAt": str(result['work_endAt']),
"work_recruit": result['work_recruit'],
"work_pay": result['work_pay'],
"work_deposit_yn": result['work_deposit_yn'],
"isDeleted": result['isDeleted']
}
except Exception as e:
return {"code": "err", "message": str(e)}
return {"code": "success", "data": data}
def post(self):
try:
header = request.headers.get('Authorization')
if header is None:
return {"code": "err", "message": "Not Allow Authorization"}
try:
data = jwt.decode(header, "secret", algorithms=["HS256"])
except Exception as e:
return {"code": "err", "message": "Token Expired"}
user_seq = data['user_seq']
db = dbHelper()
parser = reqparse.RequestParser()
parser.add_argument('user_seq', type=int)
args = parser.parse_args()
sql = "UPDATE user SET user_deposit = user_deposiot - %s WHERE user_seq = %s"
db.cursor.execute(sql, (user_seq, args['amount']))
db.conn.commit()
except Exception as e:
return {"code": "err", "message": str(e)}
return {"code": "success"}
def get(self):
try:
header = request.headers.get('Authorization')
if header is None:
return {"code": "err", "message": "Not Allow Authorization"}
try:
data = jwt.decode(header, "secret", algorithms=["HS256"])
except Exception as e:
return {"code": "err", "message": "Token Expired"}
user_seq = data['user_seq']
db = dbHelper()
sql = "SELECT user_deposit FROM user WHERE user_seq = %s;"
db.cursor.execute(sql, (user_seq))
deposit = db.cursor.fetchone()
if deposit == None:
return {"code": "err", "message": "Unexpected Error"}
except Exception as e:
return {"code": "err", "message": str(e)}
return {"code": "success", "data": {"deposit": deposit}}
def setProfile(key, value, user_seq):
db = dbHelper()
sql = "UPDATE user SET " + str(
key) + " = %s, user_status = '1' WHERE user_seq = %s;"
db.cursor.execute(sql, (value, user_seq))
db.conn.commit()
return True
def get(self, work_type_seq):
try:
db = dbHelper()
sql = "SELECT work_seq, work_title, work_intro, work_img, work_recruit, work_pay, work_deposit_yn, isDeleted FROM work " \
"WHERE work_deposit_yn = %s " \
"AND work_type_seq = %s AND isDeleted = %s"
db.cursor.execute(sql, ('Y', work_type_seq, 'N'))
result = db.cursor.fetchall()
except Exception as e:
return {"code": "err", "message": str(e)}
return {"code": "success", "data": result}
def post(self, work_seq):
try:
db = dbHelper()
header = request.headers.get('Authorization')
if header is None:
return {"code": "err", "message": "Not Allow Authorization"}
try:
data = jwt.decode(header, "secret", algorithms=["HS256"])
except Exception as e:
return {"code": "err", "message": "Token Expired"}
parser = reqparse.RequestParser()
parser.add_argument('user_seq', type=int)
parser.add_argument('company_seq', type=int)
parser.add_argument('volunteer_seq', type=int)
parser.add_argument('score_title', type=str)
parser.add_argument('score_content', type=str)
parser.add_argument('score_value', type=int)
parser.add_argument('score_type', type=str)
# parser.add_argument('badge_nm', type=str) # 추후 정규화를 생각해봐야 함..
args = parser.parse_args()
sql = "INSERT INTO score (volunteer_seq, work_seq, company_seq, score_title, score_content, score_value, score_type) VALUES (%s, %s, %s, %s, %s, %s, %s)"
db.cursor.execute(
sql, (args['volunteer_seq'], work_seq, args['company_seq'],
args['score_title'], args['score_content'],
args['score_value'], args['score_type']))
sql = "SELECT sum(a.score_value) / count(a.company_seq) AS score_sum FROM bongo.score a, bongo.company b WHERE a.company_seq = b.company_seq AND a.company_seq = %s"
db.cursor.execute(sql, args['company_seq'])
result = db.cursor.fetchone()
result['score_sum'] = str(result['score_sum'])
sql = "UPDATE company SET company_score = %s WHERE company_seq = %s"
db.cursor.execute(sql, (result['score_sum'], args['company_seq']))
sql = "UPDATE volunteer SET isDeleted = %s WHERE volunteer_seq = %s"
db.cursor.execute(sql, ('Y', args['volunteer_seq']))
db.conn.commit()
except Exception as e:
return {"code": "err", "message": str(e)}
return {"code": "success"}
def get(self, work_title):
try:
db = dbHelper()
MLN = "%s" % work_title
sql = "SELECT work_seq, work_title, work_intro, work_img, work_recruit, work_pay, work_deposit_yn, isDeleted FROM work a, work_type b, company c WHERE c.company_seq = a.company_seq AND a.work_type_seq = b.work_type_seq AND a.work_title LIKE '%s' AND a.work_deposit_yn = 'Y'" % (
MLN)
db.cursor.execute(sql)
result = db.cursor.fetchall()
print(result)
except Exception as e:
return {"code": "err", "message": str(e)}
return {"code": "success", "data": result}
def validEmail(user_email):
# @ 검증
if "@" not in user_email:
return False
db = dbHelper()
sql = "SELECT user_seq FROM user WHERE user_email = %s;"
db.cursor.execute(sql, (user_email))
result = db.cursor.fetchone()
if result == None:
# 중복 없음
return True
# 중복 존재
return False
def put(self, work_seq, volunteer_seq):
try:
db = dbHelper()
# Authorization 헤더로 담음
header = request.headers.get('Authorization')
if header is None:
return {"code": "err", "message": "Not Allow Authorization"}
try:
data = jwt.decode(header, "secret", algorithms=["HS256"])
except Exception as e:
return {"code": "err", "message": "Token Expired"}
parser = reqparse.RequestParser()
parser.add_argument('user_seq', type=int)
parser.add_argument('score_title', type=str)
parser.add_argument('score_content', type=str)
parser.add_argument('score_value', type=int)
parser.add_argument('score_type', type=str)
# parser.add_argument('badge_nm', type=str) # 추후 정규화를 생각해봐야 함..
args = parser.parse_args()
sql = "UPDATE score SET score_title = %s, score_content = %s, score_value = %s, score_type = %s WHERE volunteer_seq = %s AND work_seq = %s"
db.cursor.execute(sql, (args['score_title'], args['score_content'],
args['score_value'], args['score_type'],
volunteer_seq, work_seq))
sql = "SELECT sum(a.score_value) / count(c.user_seq) AS score_sum FROM score a, volunteer b, user c WHERE a.volunteer_seq = b.volunteer_seq " \
"AND b.user_seq = c.user_seq AND b.user_seq = %s"
db.cursor.execute(sql, args['user_seq'])
result = db.cursor.fetchone()
result['score_sum'] = str(result['score_sum'])
sql = "UPDATE user SET user_score = %s WHERE user_seq = %s"
db.cursor.execute(sql, (result['score_sum'], args['user_seq']))
db.conn.commit()
except Exception as e:
return {"code": "err", "message": str(e)}
return {"code": "success"}
def post(self):
try:
db = dbHelper()
parser = reqparse.RequestParser()
parser.add_argument('amount', type=int)
args = parser.parse_args()
if args['amount'] == None:
return {"code": "err", "message": "Invalid Parameter amount"}
header = request.headers.get('Authorization')
if header is None:
return {"code": "err", "message": "Not Allow Authorization"}
try:
data = jwt.decode(header, "secret", algorithms=["HS256"])
except Exception as e:
return {"code": "err", "message": "Token Expired"}
user_seq = data['user_seq']
# 일반회원은 충전 불가
if data['user_type'] == "N":
return {"code": "err", "message": "Permission Denied"}
sql = "SELECT user_deposit FROM user WHERE user_seq = %s;"
db.cursor.execute(sql, (user_seq))
deposit = db.cursor.fetchone()
if deposit == None:
deposit = 0
else:
deposit = deposit['user_deposit']
deposit += int(args['amount'])
sql = "UPDATE user SET user_deposit = %s WHERE user_seq = %s;"
db.cursor.execute(sql, (deposit, user_seq))
db.conn.commit()
except Exception as e:
return {"code": "err", "message": str(e)}
return {"code": "success", "data": {"deposit": deposit}}
def post(self):
try:
db = dbHelper()
parser = reqparse.RequestParser()
parser.add_argument('user_email', type=str)
parser.add_argument('user_pw', type=str)
parser.add_argument('user_type', type=str)
parser.add_argument('user_birth', type=str)
parser.add_argument('user_gender', type=str)
parser.add_argument('user_nm', type=str)
args = parser.parse_args()
user_pw = encrypt(args['user_pw'])
# 이메일 검증 실패
if not validEmail(args['user_email']):
return {"code": "err", "message": "Invalid Email"}
# 파라미터 검증
for key in args:
if args[key] == None:
return {
"code": "err",
"message": "Invalid Parameter " + str(key)
}
sql = "INSERT INTO user (user_email, user_pw, user_birth, user_gender, user_nm) VALUES (%s, %s, %s, %s, %s);"
db.cursor.execute(sql,
(args['user_email'], user_pw, args['user_birth'],
args['user_gender'], args['user_nm']))
db.conn.commit()
user_seq = db.cursor.lastrowid
except Exception as e:
return {"code": "err", "message": str(e)}
encoded_jwt = jwt.encode({"user_seq": user_seq},
"secret",
algorithm="HS256")
return {"code": "success", "data": {"token": encoded_jwt}}
def post(self):
try:
db = dbHelper()
parser = reqparse.RequestParser()
parser.add_argument('user_seq', type=int)
args = parser.parse_args()
sql = "SELECT sum(a.work_pay) AS total_price FROM bongo.work a, bongo.volunteer b WHERE b.user_seq = %s AND b.work_seq = a.work_seq AND b.volunteer_createAt BETWEEN DATE_ADD(NOW(),INTERVAL -1 DAY ) AND NOW()"
db.cursor.execute(sql, args['user_seq'])
result = db.cursor.fetchone()
result['total_price'] = str(result['total_price'])
except Exception as e:
return {"code": "err", "message": str(e)}
return {"code": "success", "data": result['total_price']}
def post(self):
try:
db = dbHelper()
# Authorization 헤더로 담음
header = request.headers.get('Authorization')
if header is None:
return {"code": "err", "message": "Not Allow Authorization"}
try:
data = jwt.decode(header, "secret", algorithms=["HS256"])
except Exception as e:
return {"code": "err", "message": "Token Expired"}
parser = reqparse.RequestParser()
parser.add_argument('user_seq', type=int)
parser.add_argument('work_seq', type=int)
parser.add_argument('volunteer_content', type=str)
args = parser.parse_args()
# sql = "SELECT * FROM volunteer where user_seq = %s AND work_seq = %s AND volunteer_yn = %s"
# db.cursor.execute(sql, (args['user_seq'], args['work_seq'], 'Y'))
# result = db.cursor.fetchall()
#
# if result is not None:
# return {"code": "err", "message": "Already have an apply"}
sql = "INSERT into volunteer(user_seq, work_seq, volunteer_content, volunteer_yn) VALUES (%s, %s, %s, %s)"
db.cursor.execute(sql, (args['user_seq'], args['work_seq'],
args['volunteer_content'], 'Y'))
# sql = "UPDATE work SET work_recruit = work_recruit - 1 WHERE work_seq = %s"
# db.cursor.execute(sql, args['work_seq'])
db.conn.commit()
except Exception as e:
return {"code": "err", "message": str(e)}
return {"code": "success"}
def get(self, company_seq):
try:
db = dbHelper()
parser = reqparse.RequestParser()
parser.add_argument('token', type=str)
args = parser.parse_args()
# Authorization 헤더로 담음
header = request.headers.get('Authorization')
if header is None:
return {"code": "err", "message": "Not Allow Authorization"}
try:
data = jwt.decode(header, "secret", algorithms=["HS256"])
except Exception as e:
return {"code": "err", "message": "Token Expired"}
sql = "SELECT a.*, b.user_nm AS owner_nm FROM company a, user b WHERE a.owner_seq = b.user_seq AND company_seq = %s;"
db.cursor.execute(sql, company_seq)
result = db.cursor.fetchone()
if result is None:
return {"code": "err", "message": "Invalid User"}
profile_data = {
"company_nm": result['company_nm'],
"company_intro": result['company_intro'],
"company_img": result['company_img'],
"owner_nm": result['owner_nm']
}
except Exception as e:
return {"code": "err", "message": str(e)}
return {"code": "success", "data": profile_data}
def post(self):
try:
db = dbHelper()
# Authorization 헤더로 담음
header = request.headers.get('Authorization')
if header is None:
return {"code": "err", "message": "Not Allow Authorization"}
try:
data = jwt.decode(header, "secret", algorithms=["HS256"])
except Exception as e:
return {"code": "err", "message": "Token Expired"}
parser = reqparse.RequestParser()
parser.add_argument('work_type_seq', type=int)
parser.add_argument('company_seq', type=int)
parser.add_argument('work_title', type=str)
parser.add_argument('work_startAt', type=str)
parser.add_argument('work_endAt', type=str)
parser.add_argument('work_recruit', type=int)
parser.add_argument('work_pay', type=int)
parser.add_argument('work_intro', type=str)
args = parser.parse_args()
sql = "INSERT into work(work_type_seq, company_seq, work_title, work_recruit, work_pay, work_intro, work_startAt, work_endAt) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)"
db.cursor.execute(
sql,
(args['work_type_seq'], args['company_seq'],
args['work_title'], args['work_recruit'], args['work_pay'],
args['work_intro'], args['work_startAt'], args['work_endAt']))
db.conn.commit()
except Exception as e:
return {"code": "err", "message": str(e)}
return {"code": "success"}
def put(self):
try:
db = dbHelper()
header = request.headers.get('Authorization')
if header is None:
return {"code": "err", "message": "Not Allow Authorization"}
try:
data = jwt.decode(header, "secret", algorithms=["HS256"])
except Exception as e:
return {"code": "err", "message": "Token Expired"}
parser = reqparse.RequestParser()
parser.add_argument('volunteer_seq', type=int)
parser.add_argument('token', type=str)
args = parser.parse_args()
sql = "UPDATE volunteer SET employee_yn = %s WHERE volunteer_seq = %s;"
db.cursor.execute(sql, ('Y', args['volunteer_seq']))
db.conn.commit()
sql = "SELECT work_seq FROM volunteer WHERE volunteer_seq = %s;"
db.cursor.execute(sql, args['volunteer_seq'])
result = db.cursor.fetchone()
data['work_seq'] = str(result['work_seq'])
sql = "UPDATE work SET work_recruit = work_recruit - 1 WHERE work_seq = %s"
db.cursor.execute(sql, data['work_seq'])
db.conn.commit()
except Exception as e:
return {"code": "err", "message": str(e)}
return {"code": "success"}
