def post(self, **kwargs):
res = {}
u_id = kwargs.get('user_id')
pw = request.form.get('pw')
try:
query = "SELECT cred_value FROM credential " \
"WHERE user_id=:u_id AND cred_key = 'local_pw'"
result = g.db.execute(text(query), u_id=u_id).fetchone()
if result is None:
res['e_msg'] = bad_request('User invalid')
db_pw = result[0]
if not check_password_hash(db_pw, pw):
res['e_msg'] = bad_request('User invalid')
g.db.execute(text("""DELETE FROM user WHERE id=:id"""), id=u_id)
g.db.execute(text("""DELETE FROM credential WHERE user_id=:id"""),
id=u_id)
res['e_msg'] = success()
except:
raise Exception
return res
def get(self, **kwargs):
# TODO: order_id should be changed to 'report_id'
# TODO: report template for EAV table should be made
job_id = request.args.get('job_id')
user_id = kwargs.get('user_id')
res = {}
# user_id와 order_id(report_id)가 매칭되는지 비교
try:
query = text("SELECT user_id FROM job WHERE id=:job_id")
cursor = g.db.execute(query, job_id=job_id)
row = dict(cursor.fetchone().items())
if not int(row['user_id']) is int(user_id):
res['e_msg'] = bad_request('User, order id mismatched')
# 매칭되면 report 데이터 불러오기
cursor = g.db.execute(text("SELECT big_json FROM report_json WHERE job_id=:job_id"), job_id=job_id)
row = cursor.fetchone()
if len(row) == 0:
res['e_msg'] = not_found('Data not found')
except:
raise Exception
res['e_msg'] = success()
res['data'] = dict(row.items())
return res
def get(self):
enc = FriskEncrypt(app.config['SECRET_KEY'])
u_id, s_id, code = request.args.get('u_id'), request.args.get('s_id'), request.args.get('code')
fb_result = requests.get('https://graph.facebook.com/v2.8/oauth/access_token', params={
'client_id': app.config['FB_APP_CONFIG']['client_id'],
'redirect_uri': 'https://api.frisk.rocks/auth/fbtemp?' + urllib.urlencode({'u_id': u_id, 's_id': s_id}),
'client_secret': app.config['FB_APP_CONFIG']['app_secret'],
'code': code
}).json()
dec_uid = enc.decrypt(urllib.unquote(u_id).encode('utf-8'))
# try:
query = """
INSERT INTO credential (user_id, cred_key, cred_value, expires)
VALUES (:id, :key, :token, :expires)
ON DUPLICATE KEY UPDATE cred_value=:token, expires=:expires
"""
expires = datetime.datetime.now() + datetime.timedelta(hours=29)
g.db.execute(text(query), token=fb_result['access_token'], id=dec_uid, key="fb_access_token", expires=expires)
res = dict()
res['e_msg'] = success()
update_state(u_id, 1, True, u'토큰 받아오기 완료')
uri_to_go = 'http://friskweb-react-gateway.s3-website.ap-northeast-2.amazonaws.com/#/service/{}/purchase/1' \
.format(s_id)
return redirect(uri_to_go)
def get(self, **kwargs):
# TODO: order_id and job_id need convention unity
order_id = request.args.get('order_id')
user_id = kwargs.get('user_id')
res = {}
# user_id 와 order_id가 매칭되는지 확인
try:
query = text("""SELECT user_id FROM job WHERE id=:id""")
cursor = g.db.execute(query, id=order_id)
row = dict(cursor.fetchone().items())
if not int(row['user_id']) is int(user_id):
res['e_msg'] = bad_request('User, order id mismatched')
# 매칭되면 job_log 테이블에서 데이터 가져오기
query = text(
"""SELECT job_id,step_id,step_co_name,step_class_name,e_msg,tb_msg
FROM job_log WHERE job_log.job_id=:order_id""")
rows = g.db.execute(query, order_id=order_id).fetchall()
except:
raise Exception
res['e_msg'] = success()
res['data'] = [dict(i.items()) for i in rows]
return res
def get(self, **kwargs):
res = {}
user_id = kwargs.get('user_id')
print "user_id : %s" % user_id
member_type = member_type_check(user_id)
if member_type == 'vc':
query = text(
"SELECT firm_name, status, runway, avg_burn_mon, cash_remaining \
FROM overview inner join firm_info on overview.firm_id = firm_info.firm_id"
)
res['data'] = g.db.execute(query).fetchall()
if res is None or len(res['data']) <= 0:
res['e_msg'] = not_found('Portfolio is not found')
return res
else:
res['data'] = [dict(i.items()) for i in res['data']]
res['e_msg'] = success()
else:
res['e_msg'] = bad_request('This page is only for VC')
return res
def get(self, **kwargs):
service_id = request.args.get('service_id')
user_id = kwargs.get('user_id')
result = check_service(user_id, service_id)
res = {'e_msg': success(), 'data': result.values()}
return jsonify(res)
def get(self):
service_id = request.args.get('service_id')
res = {}
try:
suggested = g.db.execute(text("""SELECT * FROM service WHERE id IN
(SELECT suggested FROM service_suggest
WHERE service_id=:id)"""),
id=service_id).fetchall()
if suggested is None:
res['e_msg'] = success()
res['data'] = []
return res
suggested = [dict(i.items()) for i in suggested]
res['data'] = suggested
res['e_msg'] = success()
return res
except:
raise Exception
def post(self, **kwarg):
service_req = request.form.get('service_id')
user_req = kwarg.get('user_id')
res = {}
# service id가 유효한지 확인
if not g.db.execute(text('SELECT id FROM service WHERE id = :id'),
id=service_req).fetchone():
res['e_msg'] = bad_request('Service id invalid')
return res
if False in validate_all(
user_id=user_req,
task_ids=required_tasks(service_req)).values():
res['e_msg'] = bad_request(
'Not all requited tasks are done. Please check again.')
return res
data = {
'service_id': service_req,
'user_id': user_req,
'status': 'waiting'
}
try:
# job table 업데이트
query = text("""
INSERT INTO job (user_id,service_id,status)
VALUES (:user_id,:service_id,:status)
""")
cursor = g.db.execute(query, **data)
id = str(cursor.lastrowid)
# job_log table 업데이트
query = text("""
INSERT INTO job_log (step_id, job_id, step_co_name)
VALUES (:step_id,:job_id,:step_co_name)""")
g.db.execute(query, step_id=1, job_id=id, step_co_name='preparing')
# 방금 업데이트 한 내역 불러오기
row = g.db.execute(
text("""SELECT id,user_id,service_id FROM job WHERE id=:id"""),
id=id).fetchone()
except:
raise Exception
res['e_msg'] = success()
res['data'] = dict(row.items())
return res
def get(self):
res = {}
# 전체 인증 기업 리스트 불러오기
try:
cursor = g.db
rows = cursor.execute(
text("""SELECT id, disp_name, acc_type FROM dsource""")
).fetchall()
except:
raise Exception
res['data'] = [dict(i.items()) for i in rows]
res['e_msg'] = success()
return res
def get(self, **kwargs):
res = {}
service_id = request.args.get('service_id')
user_id = kwargs.get('user_id')
# 해당 service에 대한 나의 인증 현황 보여주기
try:
query = """
SELECT mand.dsource_id, mand.credcol, cred.cred_value, cred.expires, mand.redirect
FROM (
SELECT service_id, sd.dsource_id, credcol, redirect
FROM service_dsource AS sd
JOIN mand_credcol AS mc
ON sd.dsource_id = mc.dsource_id
WHERE service_id = :service_id
) AS mand
LEFT JOIN (
SELECT user_id, cred_key, cred_value, expires
FROM credential
WHERE user_id = :user_id
) AS cred
ON mand.credcol = cred.cred_key
"""
rows = g.db.execute(text(query),
service_id=service_id,
user_id=user_id).fetchall()
except:
raise Exception
result = [dict(i.items()) for i in rows]
# 상황에 따라 status를 다르게 지정
for row in result:
row['redirect'] = callback_uri(row['credcol'], user_id, service_id)
if row['cred_value'] is None:
row['status'] = 'issue'
elif row['expires'] is not None and row[
'expires'] < datetime.utcnow():
row['status'] = 'renew'
else:
row['status'] = 'OK'
res['data'] = result
res['e_msg'] = success()
return jsonify(res)
def get(self):
res = {}
try:
res['data'] = g.db.execute(
text("SELECT * FROM service")).fetchall()
except:
raise Exception
if res is None or len(res['data']) <= 0:
res['e_msg'] = not_found('Service id not found')
return res
res['data'] = [dict(i.items()) for i in res['data']]
res['e_msg'] = success()
return res
def post(self):
name = request.form.get('id')
pw = request.form.get('pw')
# name = request.args.get('id')
# pw = request.args.get('pw')
res = {}
try:
db_info = g.db.execute(text('''SELECT user.id, credential.cred_value
FROM credential JOIN user ON user_id=id
WHERE customer_user_cred=:name AND cred_key=:key'''),
name=name, key="local_pw").fetchone()
except:
raise Exception
if db_info is None:
res['e_msg'] = bad_request('User not found')
else:
db_user_id = db_info[0]
db_pw = db_info[1]
# member type
mem_type_query = 'SELECT member_type FROM user JOIN firm_info ON firm_info.firm_id=user.firm_id \
where user.customer_user_cred=:name'
mem_type = g.db.execute(text(mem_type_query), name=name).fetchone()[0]
res['member_type'] = mem_type
# If user matched, get refresh token
if check_password_hash(db_pw, pw):
query = "SELECT cred_value FROM credential WHERE cred_key='login_refresh_token' AND user_id=:id"
refresh_token = g.db.execute(text(query), id=db_user_id).fetchone()[0]
res['refresh_token'] = refresh_token
# If scope verified, get access token
if jwt_scope_verify(str(refresh_token), app.config['SECRET_KEY']):
access_token = jwt_access_token(db_user_id, 31536000, app.config['SECRET_KEY'], 'master')
res['access_token'] = access_token
res['e_msg'] = success()
else:
res['e_msg'] = unauthorized('User id or password invalid, please try again')
return res
def get(self, **kwargs):
id = kwargs.get('user_id')
res = {}
try:
user_name = g.db.execute(
text(""" SELECT customer_user_cred FROM user WHERE id=:id"""),
id=id).fetchone()
except:
raise Exception
if user_name is not None:
res['data'] = dict(name=user_name[0])
res['e_msg'] = success()
return res
res['e_msg'] = not_found('Id not exists')
return res
def get(self):
res = {}
service_id = request.args.get('service_id')
try:
info = g.db.execute(text("SELECT * FROM service WHERE id=:id"),
id=service_id).fetchall()
except:
raise Exception
if info is None or len(info) <= 0:
res['e_msg'] = bad_request('Service id not found')
info = [dict(i.items()) for i in info]
res['data'] = info[0]
res['e_msg'] = success()
return res
def post(self, **kwargs):
u_id, token = kwargs.get('user_id'), request.form.get('token')
res = {}
# try:
query = """
INSERT INTO credential (user_id, cred_key, cred_value, expires)
VALUES (:id, :key, :token, :expires)
ON DUPLICATE KEY UPDATE cred_value=:token, expires=:expires
"""
expires = datetime.datetime.now() + datetime.timedelta(hours=29)
g.db.execute(text(query),
token=token,
id=u_id,
key="fb_access_token",
expires=expires)
res['e_msg'] = success()
update_state(u_id, 1, True, u'토큰 받아오기 완료')
return res
def get(self, **kwargs):
res = {}
pf_id = request.args.get('pf_id')
job_id = request.args.get('job_id')
try:
info = g.db.execute(text("SELECT * FROM portfolios WHERE id=:id"),
id=pf_id).fetchall()
except:
raise Exception
if info is None or len(info) <= 0:
res['e_msg'] = bad_request('Portfolio id not found')
info = [dict(i.items()) for i in info]
res['data'] = info[0]
res['e_msg'] = success()
return res
def post(self):
name = request.args.get('name')
remain_month = request.args.get('drmonth')
monthly_use = request.args.get('dmum')
remain_money = request.args.get('drmoney')
res = {}
try:
sql = "INSERT INTO portfolios (name, remain_month, monthly_use_money, remain_money) VALUES (%s, %s, %s, %s)"
g.db.execute(sql, (name, remain_month, monthly_use, remain_money))
confirm_sql = "SELECT * FROM portfolios WHERE name=:name"
confirm_res = g.db.execute(text(confirm_sql), name=name).fetchall()
sql_res = [dict(i.items()) for i in confirm_res]
res['data'] = sql_res
res['e_msg'] = success()
except:
raise Exception
return res
def get(self, **kwargs):
res = {}
user_id = kwargs.get('user_id')
# job 테이블에서 나의 주문내역 가져오기
try:
cursor = g.db
query = """
SELECT j.id, service_id, service_name, try_started_at, try_ended_at, status
FROM job as j JOIN service as s ON j.service_id=s.id
WHERE user_id=:user_id
ORDER BY id DESC
"""
rows = cursor.execute(text(query), user_id=user_id).fetchall()
except:
raise Exception
res['e_msg'] = success()
res['data'] = [dict(i.items()) for i in rows]
return jsonify(res)
def post(self):
name = request.form.get('id')
pw = request.form.get('pw')
res = {}
if len(pw) < 4:
res['e_msg'] = bad_request('Password too short')
return res
try:
db_id = g.db.execute(text('''SELECT user.id, credential.cred_value
FROM credential JOIN user ON user_id=id
WHERE customer_user_cred=:name AND cred_key=:key '''),
name=name, key="local_pw").fetchone()
except:
raise Exception
if db_id is not None:
res['e_msg'] = bad_request('User already exists')
else:
try:
g.db.execute(text("INSERT INTO user (customer_user_cred) VALUES (:name)"), name=name)
index = g.db.execute(text("SELECT id FROM user WHERE customer_user_cred=:name"), name=name).fetchone()[0]
g.db.execute(text("INSERT INTO credential (user_id, cred_key, cred_value) VALUES (:index, :key, :pw)"),
index=index, key='local_pw',
pw=generate_password_hash(pw))
refresh_token = jwt_refresh_token(index, 'master', app.config['SECRET_KEY'])
g.db.execute(text('''INSERT INTO credential (user_id, cred_key, cred_value)
VALUES (:user_id, :cred_key, :cred_value)'''),
user_id=index, cred_key='login_refresh_token', cred_value=refresh_token)
res['e_msg'] = success()
except:
raise Exception
return res
def post(self, **kwargs):
res = {}
user_id = kwargs.get('user_id')
industry = nfil(request.form.get('industry'))
job_class = nfil(request.form.get('job_class'))
E_type = nfil(request.form.get('E_type'))
sex = nfil(request.form.get('sex'))
education = nfil(request.form.get('education'))
age = nfil(request.form.get('age'))
duration = nfil(request.form.get('duration'))
W_type = nfil(request.form.get('W_type'))
query = """
INSERT INTO user_survey (user_id, industry, job_class, E_type, sex, education, age, duration, W_type)
VALUES(:user_id, :industry, :job_class, E_type, sex, education, age, duration, W_type)
ON DUPLICATE KEY UPDATE industry=:industry, job_class=:job_class,E_type=:E_type,sex=:sex, education=:education, age=:age, duration=:duration, W_type=:W_type
"""
try:
g.db.execute(text(query),
user_id=user_id,
industry=industry,
job_class=job_class,
E_type=E_type,
sex=sex,
education=education,
age=age,
duration=duration,
W_type=W_type)
except Exception as e:
raise Exception(e)
update_state(user_id, 5, True, u'직업군 설문조사 완료')
res['e_msg'] = success()
return res
def post(self, **kwargs):
attr = request.form.get('attribute')
to = request.form.get('to')
u_id = kwargs.get('user_id')
res = dict()
try:
if attr == 'pw':
query = "INSERT INTO credential (user_id, cred_key, cred_value) " \
"VALUES (:user_id, :cred_key, :cred_value) " \
"ON DUPLICATE KEY UPDATE cred_key=:cred_key, cred_value=:cred_value;"
g.db.execute(text(query),
user_id=u_id,
cred_key='local_pw',
cred_value=generate_password_hash(to))
res['e_msg'] = success()
else:
res['e_msg'] = not_found('User not found')
except:
raise Exception
return res
def post(self, **kwargs):
print kwargs
json_report = request.form.get('json_report')
json_reports = json.loads(json_report)
# 현재는 크게 의미 없어 주석처리
# job_info = json_reports['job_info']
acc_info = json_reports['acc_infos'][0]
last_crawl = json_reports['last_crawl']
# For query
id = acc_info['user']
crawldate = datetime.now().strftime('%Y-%m-%d')
data = acc_info['transactions']
res = {}
try:
query = """
INSERT INTO friskraw(id, crawldate, data, last_crawl)
VALUES(:id, :crawldate, :data, :last_crawl)
"""
g.db.execute(text(query),
id=str(id),
crawldate=crawldate,
data=str(data),
last_crawl=str(last_crawl))
except:
'''
res['e_msg'] = bad_request("DB ERROR")
return res
'''
raise Exception
res['e_msg'] = success()
return res
def post(self, **kwargs):
id, credcol = kwargs.get('user_id'), request.form.get('credcol')
res = {}
try:
credcols = g.db.execute(text("SELECT DISTINCT credcol FROM mand_credcol")).fetchall()
credcols = [i[0] for i in credcols]
if credcol is None or credcol not in credcols:
res['e_msg'] = bad_request('Invalid Credcol')
return res
rowcounted = g.db.execute(text("""DELETE FROM credential WHERE user_id=:id and cred_key=:cred_key """),
id=id, cred_key=credcol).rowcount
if rowcounted > 0:
res['e_msg'] = success()
else:
res['e_msg'] = not_found('Token not found')
except:
raise Exception()
return res
def get(self, **kwargs):
res = {}
dsource_id = request.args.get('dsource_id')
user_id = kwargs.get('user_id')
# 해당 기업에 대해 나의 인증 현황 불러오기
try:
cursor = g.db
query = text("""
SELECT credential.user_id, mand.id, mand.disp_name, mand.acc_type,
mand.credcol, mand.redirect, credential.cred_value, credential.expires,
if(credential.cred_value is null,'issue',if(credential.expires>now(),'OK','renew')) as status
FROM
(SELECT dsource.id, dsource.disp_name, dsource.acc_type, mand_credcol.credcol, mand_credcol.redirect
FROM dsource
JOIN mand_credcol
ON dsource.id=mand_credcol.dsource_id) as mand
LEFT JOIN
(SELECT *
FROM credential
WHERE user_id=:user_id) as credential
ON
mand.credcol=credential.cred_key
WHERE
mand.id=:dsource_id
""")
rows = cursor.execute(query, user_id=user_id, dsource_id=dsource_id).fetchall()
res['data'] = [dict(i.items()) for i in rows]
for datum in res['data']:
datum['expires'] = str(datum['expires'])
res['e_msg'] = success()
return res
except:
raise Exception
def get(self):
code = request.args.get('code')
scope = request.args.get('scope')
try:
referer = request.headers['referer']
parsed_url = urlparse.urlparse(referer)
parsed_query = urlparse.parse_qs(parsed_url.query)
except KeyError:
parsed_query = {}
if 'client_info' in parsed_query:
client_info = json.loads(base64.urlsafe_b64decode(str(parsed_query['client_info'][0])))
else:
client_info = json.loads(base64.urlsafe_b64decode(str(request.args.get('client_info'))))
requester = client_info['requester']
if requester == 'friskweb':
data = json.loads(FriskEncrypt(app.config['SECRET_KEY']).decrypt(client_info['data']))
u_id = data['user_id']
redirect_uri = 'http://friskweb-react-gateway.s3-website.ap-northeast-2.amazonaws.com/#/service/{}/purchase/1'.format(
data['service_id']
)
now = data['now']
elif requester == 'friskweb_t':
data = json.loads(FriskEncrypt(app.config['SECRET_KEY']).decrypt(client_info['data']))
u_id = data['user_id']
redirect_uri = 'http://www.frisk.rocks:5505/order/?service_id={}'.format(
data['service_id']
)
now = data['now']
elif requester == 'test':
data = json.loads(FriskEncrypt(app.config['SECRET_KEY']).decrypt(client_info['data']))
u_id = data['user_id']
now = data['now']
else:
return {'e_msg': bad_request('Cannot verify the request')}
# 정보 기한 만료 시 바로 리다이렉트
if int(now) + 60 < time.time():
return redirect(redirect_uri)
try:
# KFTC로 code를 보내고 token관련 정보를 얻어옴
result = KFTCAuth().get_token(code)
# 응답을 바탕으로 필요한 정보 얻기
access_token = result['access_token']
expires = datetime.datetime.now() + datetime.timedelta(seconds=result['expires_in'])
refresh_token = result['refresh_token']
user_seq_no = result['user_seq_no']
cred_key = {
'login': '******',
'inquiry': 'kftc_inquiry_access_token'
}.get(result['scope'])
cred_key_refresh = {
'login': '******',
'inquiry': 'kftc_inquiry_refresh_token'
}.get(result['scope'])
res = {}
# access_token DB에 씀
query = "INSERT INTO credential (user_id, cred_key, cred_value, expires) " \
"VALUES (:id, :cred_key, :cred_value, :expires) " \
"ON DUPLICATE KEY UPDATE cred_value=:cred_value, expires=:expires;"
g.db.execute(text(query), id=u_id, cred_key=cred_key, cred_value=access_token, expires=expires)
# refresh_token DB에 씀
query = "INSERT INTO credential (user_id, cred_key, cred_value) " \
"VALUES (:id, :cred_key, :cred_value) " \
"ON DUPLICATE KEY UPDATE cred_value=:cred_value;"
g.db.execute(text(query), id=u_id, cred_key=cred_key_refresh, cred_value=refresh_token, expires=expires)
# user_seq_no(KFTC에서 user를 구분하는 id) DB에 씀
query = "INSERT INTO credential (user_id, cred_key, cred_value) " \
"VALUES (:u_id, :cred_key, :cred_value) " \
"ON DUPLICATE KEY UPDATE cred_value=:cred_value"
g.db.execute(text(query), u_id=u_id, cred_key='kftc_user_seq_no', cred_value=user_seq_no)
res['e_msg'] = success()
if result.get('scope') == u'login':
update_state(u_id, 2, True, u'토큰 받아오기 완료')
elif result.get('scope') == u'inquiry':
update_state(u_id, 3, True, u'토큰 받아오기 완료')
except:
raise Exception
if requester == 'test':
return 'Access Token: {}'.format(access_token)
else:
return redirect(redirect_uri)
def get(self, **kwargs):
res = {}
try:
query = """
SELECT mand.dsource_id, cred.user_id, mand.credcol, cred.cred_value
FROM (
SELECT service_id, sd.dsource_id, credcol
FROM service_dsource AS sd
JOIN mand_credcol AS mc
ON sd.dsource_id = mc.dsource_id
WHERE service_id = 80004
) AS mand
LEFT JOIN (
SELECT user_id, cred_key, cred_value
FROM credential
) AS cred
ON mand.credcol = cred.cred_key
INNER JOIN (
SELECT user_id, service_id
FROM job
WHERE service_id = 80004) AS jb
ON jb.user_id = cred.user_id
ORDER BY user_id
"""
cred_rows = g.db.execute(text(query)).fetchall()
except:
raise Exception
creds = [dict(i.items()) for i in cred_rows]
print creds
creds_sorted = {}
for row in creds:
if creds_sorted in (row['user_id']) is not True:
creds_sorted[row['user_id']] = []
creds_sorted[row['user_id']].append(row)
else:
creds_sorted[row['user_id']].append(row)
job_infos = []
for values in creds_sorted.values():
cred_acc_no = ''
cred_user_id = ''
cred_user_pw = ''
for row in values:
if row['credcol'] == 'cred_acc_no':
cred_acc_no = row['cred_value']
elif row['credcol'] == 'cred_user_id':
cred_user_id = row['cred_value']
elif row['credcol'] == 'cred_user_pw':
cred_user_pw = row['cred_value']
job_infos.append(
make_job_info(cred_acc_no=cred_acc_no,
cred_user_id=cred_user_id,
cred_user_pw=cred_user_pw))
res['data'] = job_infos
res['e_msg'] = success()
return jsonify(res)
