def pretranslatedResourceList(self, project_id, endpoint):
cursor = self.conn.cursor()
query = """
SELECT *
FROM CICERON.V_PRETRANSLATED_RESOURCES
WHERE project_id = %s
ORDER BY id ASC
"""
cursor.execute(query, (project_id, ))
columns = [desc[0] for desc in cursor.description]
ret = cursor.fetchall()
resource_list = ciceron_lib.dbToDict(columns, ret)
for item in resource_list:
file_list_of_resource = self.provideFileListOfResource(
item['id'], endpoint)
item['resource_info'] = file_list_of_resource
item['requester_list'] = self.provideRequesterList(
project_id, item['id'])
item['translator_list'] = self.provideTranslatorList(
project_id, item['id'])
can_get, file_name, _ = self.provideCoverPhoto(project_id)
cover_photo_url = endpoint + '/user/pretranslated/project/{}/coverPhoto/{}'.format(
project_id, file_name)
return resource_list, cover_photo_url
def provideFileListOfResource(self, resource_id, endpoint):
cursor = self.conn.cursor()
query = """
SELECT
id
, project_id
, resource_id
, preview_permission
FROM CICERON.F_PRETRANSLATED_RESULT_FILE
WHERE resource_id = %s
ORDER BY id ASC
"""
cursor.execute(query, (resource_id, ))
columns = [desc[0] for desc in cursor.description]
ret = cursor.fetchall()
file_list = ciceron_lib.dbToDict(columns, ret)
for item in file_list:
can_get, file_name, _ = self.provideFile(item['resource_id'],
item['id'])
item[
'file_url'] = endpoint + '/user/pretranslated/project/{}/resource/{}/file/{}/{}'.format(
item['project_id'], item['resource_id'], item['id'],
file_name)
return file_list
def imageListing(self, tag_id, page=1):
cursor = self.conn.cursor()
query_count_tag_photos = """
SELECT count(*) FROM RAW.f_mapping_photo_crawltag
WHERE crawltag_id = %s
"""
query_select_tag_photos = """
SELECT id,
concat('/api/v2/admin/kangaroo/tag/', crawltag_id, '/img/', id, '/', filename) as image_url
FROM RAW.f_mapping_photo_crawltag mpc
JOIN RAW.f_photo p on (mpc.photo_id = p.id)
WHERE crawltag_id = %s
LIMIT 20
OFFSET 20 * (%s - 1)
"""
# 태그에 해당하는 사진 개수 구하기
cursor.execute(query_count_tag_photos, (tag_id, ))
cnt = cursor.fetchone()[0]
if cnt == 0:
msg = "This page don't exist."
return 404, msg
# 해당하는 사진 출력
cursor.execute(query_select_tag_photos, (tag_id, int(page), ))
columns = [ desc[0] for desc in cursor.description ]
imageinfo = cursor.fetchall()
image_list = ciceron_lib.dbToDict(columns, imageinfo)
return 200, image_list
def tagListing(self):
cursor = self.conn.cursor()
query_select_tags = """
SELECT id, tag_name as name FROM RAW.f_crawltag
WHERE status_id IS NULL
OR status_id = 0
"""
cursor.execute(query_select_tags)
columns = [ desc[0] for desc in cursor.description ]
taginfo = cursor.fetchall()
tag_list = ciceron_lib.dbToDict(columns, taginfo)
return 200, tag_list
def provideTranslatorList(self, project_id, resource_id):
cursor = self.conn.cursor()
query = """
SELECT *
FROM CICERON.V_PRETRANSLATED_TRANSLATOR
WHERE project_id = %s AND resource_id = %s
"""
cursor.execute(query, (
project_id,
resource_id,
))
column = [col[0] for col in cursor.description]
ret = cursor.fetchall()
translator_list = ciceron_lib.dbToDict(column, ret)
return translator_list
def getMyDownloadList(self, user_id):
cursor = self.conn.cursor()
query = """
SELECT
id
, resource_id
, is_paid
, is_sent
, is_downloaded
, feedback_score
, request_timestamp
FROM CICERON.V_PRETRANSLATED_MY_DOWNLOAD
WHERE user_id = %s
ORDER BY request_timestamp DESC
"""
cursor.execute(query, (user_id, ))
column = [desc[0] for desc in cursor.description]
ret = cursor.fetchall()
return ciceron_lib.dbToDict(column, ret)
def pretranslatedProjectList(self, endpoint, page=1):
cursor = self.conn.cursor()
query = """
SELECT *
FROM CICERON.V_PRETRANSLATED_PROJECT
ORDER BY id DESC
LIMIT 10
OFFSET {}
"""
cursor.execute(query.format((page - 1) * 10))
columns = [desc[0] for desc in cursor.description]
ret = cursor.fetchall()
project_list = ciceron_lib.dbToDict(columns, ret)
for item in project_list:
can_get, file_name, _ = self.provideCoverPhoto(item['id'])
item[
'cover_photo_url'] = endpoint + '/user/pretranslated/project/{}/coverPhoto/{}'.format(
item['id'], file_name)
return project_list