def get_by_category(category_id):
cursor = mysql.get_db().cursor()
cursor.execute(
'select id, content_type, text_content, picture_id, video_id from case_content where '
'id in (select content_id from case_category_content where category_id=%s)',
(category_id, ))
return cursor.fetchall()
def create_page_info(role_id, page_title, contents):
if not RoleplayInfoHelper.get_by_id(role_id):
return False, 'invalid role_id'
db = mysql.get_db()
cursor = db.cursor()
# get page size
cursor.execute(
'select count(*) from role_page where role_id=%s',
(role_id,)
)
page_size = cursor.fetchone()[0]
print page_size
# insert role_page
cursor.execute(
"insert into role_page (role_id, title, pagination) "
"values (%s, %s, %s)",
(role_id, page_title, int(page_size) + 1)
)
RoleplayInfoHelper.modify(role_id, [('page_size', int(page_size) + 1)])
db.commit()
page_id = cursor.lastrowid
for content in contents:
RoleplayContentHelper.create_content(page_id, content)
return True, page_id
def create_worker(worker_name, worker_job_id, worker_depart_ids):
if WorkerHelper.get_by_name(worker_name) is not None:
return False
db = mysql.get_db()
cursor = db.cursor()
cursor.execute("insert into worker (name, job_id) "
"values (%s, %s)", (worker_name, worker_job_id))
db.commit()
if cursor.rowcount != 1:
return False
worker = WorkerHelper.get_by_name(worker_name)
if not worker:
return False
success = True
for depart_id in worker_depart_ids:
if not WorkerHelper.add_worker_depart(worker['worker_id'],
depart_id):
success = False
if success:
return True
else:
abort(400)
return False
def get_by_page_id(page_id):
cursor = mysql.get_db().cursor()
cursor.execute(
'select id, content_type, text_content, picture_id, video_id from role_page_content where page_id=%s',
[page_id]
)
return cursor.fetchall()
def get_by_case_id(case_id):
cursor = mysql.get_db().cursor()
cursor.execute(
'select id, name, description from case_category where '
'id in (select category_id from case_info_category where case_id=%s)',
(case_id, ))
return cursor.fetchall()
def get_by_name(item_type_name):
cursor = mysql.get_db().cursor()
cursor.execute(
'select item_type.id, item_type.name, item_type.description'
' from item_type'
' where item_type.name=%s', [item_type_name])
return cursor.fetchone()
def create_roleplay_info(role_name, role_desc, depart_id, picture_id):
if RoleplayInfoHelper.get_by_name(role_name) is not None:
return False, 'duplicate role play name'
#if not PictureHelper.get_by_id(picture_id):
# return False, 'invalid picture_id'
#if not DepartHelper.get_by_id(depart_id):
# return False, 'invalid depart_id'
if not depart_id :
depart_id = 0
db = mysql.get_db()
cursor = db.cursor()
cursor.execute(
"insert into role (name, description, page_size, depart_id, picture) "
"values (%s, %s, '0', %s, %s)",
(role_name, role_desc, depart_id, picture_id)
)
db.commit()
# row count为1表示插入成功
if cursor.rowcount != 1:
return False, 'insert into db failed'
else:
return True, cursor.lastrowid
def create(file):
video_name = file.filename
ext = video_name.rsplit('.', 1)[1].lower()
if '.' in video_name and ext in VIDEO_EXTS:
gene_name = str(uuid.uuid4())
new_video_name = gene_name + '.' + ext
address = os.path.join('video', new_video_name)
file.save(os.path.join(app.config['UPLOAD_FOLDER'], address))
screenshot_name = gene_name + '.jpeg'
screenshot_address = os.path.join('video', 'screenshot',
screenshot_name)
screenshot = VideoStream(
os.path.join(app.config['UPLOAD_FOLDER'],
address)).get_frame_at_sec(2).image()
screenshot.save(
os.path.join(app.config['UPLOAD_FOLDER'], screenshot_address))
db = mysql.get_db()
cursor = db.cursor()
cursor.execute(
"insert into video (name, address, screenshot) "
"values (%s, %s, %s)",
(video_name, address, screenshot_address))
db.commit()
if cursor.rowcount != 1:
return False, 'insert into db failed'
return True, cursor.lastrowid
else:
return False, 'invalid extension'
def get_by_manager_id(manager_id):
cursor = mysql.get_db().cursor()
cursor.execute(
'select auth.id, auth.description from authority auth, manager_auth '
'where manager_auth.manager_id=%s and auth.id = manager_auth.auth_id',
[manager_id])
return cursor.fetchall()
def get_by_roleid(role_id):
cursor = mysql.get_db().cursor()
cursor.execute(
'select id, title, pagination from role_page where role_id=%s',
[role_id]
)
return cursor.fetchall()
def create_case_category(case_id, category_name, category_desc):
if CaseInfoHelper.get_by_id(case_id) is None:
print 'can not find case'
return False
db = mysql.get_db()
cursor = db.cursor()
cursor.execute(
"insert into case_category (name, description) "
"values (%s, %s)", (category_name, category_desc))
db.commit()
# row count为1表示插入成功
if cursor.rowcount != 1:
return False
category_id = cursor.lastrowid
cursor.execute(
"insert into case_info_category (case_id, category_id) "
"values (%s, %s)", (case_id, category_id))
db.commit()
# row count为1表示插入成功
if cursor.rowcount != 1:
return False
else:
return category_id
def get_by_name(worker_name):
cursor = mysql.get_db().cursor()
cursor.execute(
'select worker.id, worker.name, job.id, job.name, job.description'
' from worker, job where worker.id=%s and worker.job_id = job.id',
[worker_name])
return cursor.fetchone()
def get_by_pagination(role_id, pagination):
cursor = mysql.get_db().cursor()
cursor.execute(
'select id, title, pagination from role_page where role_id=%s and pagination=%s',
[role_id, pagination]
)
return cursor.fetchone()
def get_by_name(depart_name):
cursor = mysql.get_db().cursor()
cursor.execute(
'select id, name, type, description from department where name=%s',
[depart_name]
)
return cursor.fetchone()
def create_manager(username, password, auth_list=None):
if ManagerHelper.get_by_name(username) is not None:
return False
db = mysql.get_db()
cursor = db.cursor()
cursor.execute(
"insert into manager_account (username, password) "
"values (%s, %s)", (username, password))
db.commit()
# row count为1表示插入成功
if cursor.rowcount != 1:
return False
else:
if auth_list:
# get created manager
manager = ManagerHelper.get_by_name(username)
# success, insert authorities
is_success = True
for auth_id in auth_list:
if AuthorityHelper.get_by_id(auth_id) is not None:
if not AuthorityHelper.add_authority(
manager['manager_id'], auth_id):
is_success = False
else:
pass
else:
pass
return is_success
else:
return True
def get_by_name(role_name):
cursor = mysql.get_db().cursor()
cursor.execute(
'select id, name, description, page_size, depart_id, picture from role where name=%s',
[role_name]
)
return cursor.fetchone()
def create_content(page_id, content):
content_type = content['content_type']
sql_content = None # 字段名
arg_content = None # 内容
if str(content_type) == '1':
sql_content = 'text_content'
arg_content = content['text_content']
elif str(content_type) == '2':
sql_content = 'video_id'
arg_content = content['video_content']
elif str(content_type) == '3':
sql_content = 'picture_id'
arg_content = content['picture_content']
db = mysql.get_db()
cursor = db.cursor()
cursor.execute(
"insert into role_page_content (page_id, content_type, {}) values (%s, %s, %s)".format(sql_content),
(page_id, content_type, arg_content)
)
db.commit()
# row count为1表示插入成功
if cursor.rowcount != 1:
return False
return cursor.lastrowid
def remove_authority(manager_id, auth_id):
db = mysql.get_db()
cursor = db.cursor()
cursor.execute(
"delete from manager_auth "
"where manager_id=%s and auth_id=%s", (manager_id, auth_id))
db.commit()
return cursor.rowcount == 1
def add_authority(manager_id, auth_id):
db = mysql.get_db()
cursor = db.cursor()
cursor.execute(
"insert into manager_auth(manager_id, auth_id) "
"values(%s, %s)", (manager_id, auth_id))
db.commit()
return cursor.rowcount == 1
def get_by_worker(worker_id):
cursor = mysql.get_db().cursor()
cursor.execute(
'select department.id, department.name, department.type, department.description '
'from department, depart_worker '
'where department.id = depart_worker.depart_id and depart_worker.worker_id=%s',
(worker_id,)
)
return cursor.fetchall()
def delete_by_id(content_id):
if CaseContentHelper.get_by_id(content_id) is None:
return False
db = mysql.get_db()
cursor = db.cursor()
cursor.execute("delete from case_content where id=%s", (content_id, ))
db.commit()
return cursor.rowcount == 1
def delete_by_id(item_type_id):
if ItemTypeHelper.get_by_id(item_type_id) is None:
return False
db = mysql.get_db()
cursor = db.cursor()
cursor.execute("delete from item_type where id=%s", (item_type_id, ))
db.commit()
return cursor.rowcount == 1
def delete_by_id(worker_id):
if WorkerHelper.get_by_id(worker_id) is None:
return False
db = mysql.get_db()
cursor = db.cursor()
cursor.execute("delete from worker where id=%s", (worker_id, ))
db.commit()
return cursor.rowcount == 1
def add_depart_item(depart_id, item_id):
db = mysql.get_db()
cursor = db.cursor()
cursor.execute(
"insert into depart_item (depart_id, item_id) "
"values (%s, %s)", (depart_id, item_id))
db.commit()
return cursor.rowcount == 1
def get_by_item_id(item_id):
cursor = mysql.get_db().cursor()
cursor.execute(
'select item.id, item.name, item.description, item.type_id, '
'item_type.name, item_type.description'
' from item, item_type'
' where item.type_id = item_type.id'
' and item.id=%s', [item_id])
return cursor.fetchone()
def get_by_depart_id(depart_id):
cursor = mysql.get_db().cursor()
cursor.execute(
'select item.id, item.name, item.description, item.type_id, '
'item_type.name, item_type.description'
' from item, item_type, depart_item'
' where item.type_id = item_type.id and depart_item.item_id = item.id'
' and depart_item.depart_id=%s', [depart_id])
return cursor.fetchall()
def modify_username(manager_id, manager_name):
if ManagerHelper.get_by_id(manager_id) is None:
return False
db = mysql.get_db()
cursor = db.cursor()
cursor.execute("update manager_account set username=%s where id=%s",
(manager_name, manager_id))
db.commit()
return cursor.rowcount == 1
def modify_password(user_id, password):
if UserHelper.get_by_id(user_id) is None:
return False
db = mysql.get_db()
cursor = db.cursor()
cursor.execute("update user_account set password=%s where id=%s",
(password, user_id))
db.commit()
return cursor.rowcount == 1
def delete_by_id(content_id):
db = mysql.get_db()
cursor = db.cursor()
cursor.execute(
"delete from role_page_content where id=%s",
(content_id,)
)
db.commit()
return cursor.rowcount == 1
def delete_by_page_id(page_id):
db = mysql.get_db()
cursor = db.cursor()
cursor.execute(
"delete from role_page_content where page_id=%s",
(page_id,)
)
db.commit()
return True
