def post(self):
body_json = request.json
try:
variety_id = int(body_json.get('variety_id'))
group_name = body_json.get('name')
if not group_name:
raise ValueError('group ERROR')
utoken = body_json.get('utoken')
except Exception as e:
return jsonify({"message": "参数错误"}), 400
user_info = verify_json_web_token(utoken)
if not user_info or user_info['role_num'] > enums.RESEARCH:
return jsonify({"message": "登录已过期或不能操作"})
user_id = user_info['id']
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
# 查询用户权限
auth_statement = "SELECT `id` FROM `link_user_variety` " \
"WHERE `user_id`=%d AND `variety_id`=%d;" % (user_id, variety_id)
cursor.execute(auth_statement)
if not cursor.fetchone():
db_connection.close()
return jsonify({"message": "没有权限,不能这样操作"}), 400
save_statement = "INSERT INTO `info_variety_trendgroup` " \
"(`name`,`variety_id`,`author_id`) " \
"VALUES (%s,%s,%s);"
cursor.execute(save_statement, (group_name, variety_id, user_id))
new_id = db_connection.insert_id()
update_sort_statement = "UPDATE `info_variety_trendgroup` SET `sort`=%d WHERE `id`=%d;" % (
new_id, new_id)
cursor.execute(update_sort_statement)
db_connection.commit()
db_connection.close()
return jsonify({"message": "添加组成功!"}), 201
def post(self):
body_json = request.json
utoken = body_json.get('utoken', None)
user_info = verify_json_web_token(utoken)
if not user_info:
return jsonify({"message": "登录已过期!"}), 400
if user_info['role_num'] > 2:
return jsonify({"message": "没有权限进行这个操作!"}), 400
module_name = body_json.get('module_name', None)
parent_id = body_json.get('parent_id', None)
if not module_name:
return jsonify({"message": "参数错误! NOT FOUND NAME."}), 400
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
try:
if not parent_id:
insert_statement = "INSERT INTO `info_module` (`name`) VALUES (%s);"
cursor.execute(insert_statement, module_name)
else:
parent_id = int(parent_id)
insert_statement = "INSERT INTO `info_module`(`name`,`parent_id`) VALUES (%s,%s);"
cursor.execute(insert_statement, (module_name, parent_id))
new_mid = db_connection.insert_id() # 新加入的id
update_statement = "UPDATE `info_module` SET `sort`=%s WHERE `id`=%s;"
cursor.execute(update_statement, (new_mid, new_mid))
db_connection.commit()
except Exception as e:
db_connection.rollback()
db_connection.close()
return jsonify({"message": "添加失败{}".format(e)}), 400
else:
db_connection.close()
return jsonify({"message": "添加成功!"}), 201
def post(self):
json_body = request.json
imgcid = json_body.get('image_code_id', '')
machine_code = json_body.get('machine_code', None)
client = get_client(machine_code)
if not client:
return jsonify({'message': 'INVALID CLIENT,无法注册!'})
role_num = 5
if client['is_manager'] == 1:
role_num = 4
username = json_body.get('username', None)
password = json_body.get('password', None)
phone = json_body.get('phone', None)
email = json_body.get('email', '')
image_code = json_body.get('imgcode', None)
agent = request.headers.get('User-Agent', '')
user_origin = ''
if agent.startswith('Delivery'):
user_origin = 'delivery'
if not all([username, password, phone, image_code]):
return jsonify({'message': '请提交完整数据.'})
if not re.match(r'^[1][3-9][0-9]{9}$', phone): # 手机号验证
return jsonify({"message": "手机号有误!"})
redis_connection = RedisConnection()
real_imgcode = redis_connection.get_value('imgcid_%s' %
imgcid) # 取出验证码
if not real_imgcode or image_code.lower() != real_imgcode.lower():
return jsonify({"message": "验证码错误!"})
password = hash_user_password(password)
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
try:
save_statement = "INSERT INTO `info_user`(`username`,`password`,`phone`,`email`,`role_num`,`origin`)" \
"VALUES (%s,%s,%s,%s,%s,%s);"
cursor.execute(
save_statement,
(username, password, phone, email, role_num, user_origin))
# 写入第三方表(记录用户可登录的客户端表)
new_user_id = db_connection.insert_id()
client_id = int(client['id'])
expire_time = datetime.datetime.strptime("3000-01-01", "%Y-%m-%d")
uc_save_statement = "INSERT INTO `link_user_client`(`user_id`,`client_id`,`expire_time`)" \
"VALUES (%s,%s,%s);"
cursor.execute(uc_save_statement,
(new_user_id, client_id, expire_time))
db_connection.commit()
except Exception as e:
current_app.logger.error("用户注册错误:{}".format(e))
db_connection.rollback() # 事务回滚
db_connection.close()
return jsonify({"message": "注册失败%s" % str(e)}), 400
else:
return jsonify({"message": "注册成功"}), 201
def post(self):
json_data = request.json
token = json_data.get('utoken', None)
if not user_is_admin(token):
return jsonify("登录已过期或没有权限进行这个操作."), 400
# 验证上传的数据
module_name = json_data.get('module_name', None)
module_page_url = json_data.get('page_url', None)
module_parent_id = json_data.get('parent_id', None)
if not module_name:
return jsonify("请填写名称!"), 400
if module_parent_id and not module_page_url:
return jsonify("子级模块需填写页面路径！"), 400
module_page_url += ".html" # 加上后缀
# 写入数据库
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
try:
if not module_parent_id:
save_statement = "INSERT INTO `work_module` (`name`,`page_url`,`parent_id`) VALUES (%s,'',NULL);"
cursor.execute(save_statement, module_name)
new_id = db_connection.insert_id()
else:
save_statement = "INSERT INTO `work_module` (`name`,`page_url`,`parent_id`) VALUES (%s,%s,%s);"
cursor.execute(
save_statement,
(module_name, module_page_url, module_parent_id))
new_id = db_connection.insert_id()
# 修改sort值
update_sort_statement = "UPDATE `work_module` SET `sort`=%s WHERE `id`=%s;"
cursor.execute(update_sort_statement, (new_id, new_id))
db_connection.commit()
except Exception as e:
logger = current_app.logger
logger.error("新增系统模块错误:" + str(e))
db_connection.close()
return jsonify("系统发生了个错误。"), 400
else:
db_connection.close()
return self.get() # 查询所有
def post(self):
json_data = request.json
token = json_data.get('utoken', None)
if not user_is_admin(token):
return jsonify("登录已过期或没有权限进行这个操作."), 400
# 验证上传的数据
variety_name = json_data.get('variety_name', None)
variety_group_id = json_data.get('parent_id', None)
variety_en_code = json_data.get('en_code', None)
if not variety_name:
return jsonify("请填写名称!"), 400
if variety_group_id and not variety_en_code:
return jsonify("品种请填写英文代码."), 400
# 写入数据库
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
try:
if not variety_group_id:
save_statement = "INSERT INTO `variety` (`name`,`parent_id`) VALUES (%s,NULL);"
cursor.execute(save_statement, variety_name)
new_id = db_connection.insert_id()
else:
save_statement = "INSERT INTO `variety` (`name`, `parent_id`,`en_code`) VALUES (%s,%s,%s);"
cursor.execute(
save_statement,
(variety_name, variety_group_id, variety_en_code))
new_id = db_connection.insert_id()
# 修改sort值
update_sort_statement = "UPDATE `variety` SET `sort`=%s WHERE `id`=%s;"
cursor.execute(update_sort_statement, (new_id, new_id))
db_connection.commit()
except Exception as e:
logger = current_app.logger
logger.error("新增品种错误:" + str(e))
db_connection.close()
return jsonify("系统发生了个错误。"), 400
else:
db_connection.close()
return self.get() # 查询所有
def post(self):
body_json = request.json
utoken = body_json.get('utoken', None)
user_info = verify_json_web_token(utoken)
if not user_info:
return jsonify({"message": "登录已过期!"}), 400
if user_info['role_num'] > 2:
return jsonify({"message": "没有权限进行这个操作!"}), 400
variety_name = body_json.get('variety_name', None)
variety_name_en = body_json.get('variety_name_en', None)
exchange_num = body_json.get('exchange_num', 0)
parent_id = body_json.get('parent_num', 0)
if not variety_name:
return jsonify("参数错误! NOT FOUND NAME."), 400
# 保存
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
try:
if not parent_id:
insert_statement = "INSERT INTO `info_variety` (`name`) VALUES (%s);"
cursor.execute(insert_statement, variety_name)
else:
parent_id = int(parent_id)
exchange_num = int(exchange_num)
variety_name_en = variety_name_en.upper()
insert_statement = "INSERT INTO `info_variety` (`name`,`name_en`,`parent_id`,`exchange`) VALUES (%s,%s,%s,%s);"
cursor.execute(
insert_statement,
(variety_name, variety_name_en, parent_id, exchange_num))
new_vid = db_connection.insert_id()
cursor.execute(
"UPDATE `info_variety` SET `sort`=%d WHERE `id`=%d;" %
(new_vid, new_vid))
db_connection.commit()
except Exception as e:
db_connection.rollback()
db_connection.close()
current_app.logger.error("增加品种错误:{}".format(e))
return jsonify({"message": "增加品种错误."}), 400
else:
db_connection.close()
return jsonify({"message": "增加品种成功!"}), 201
def initial_tables_and_data():
# 连接mysql
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
# 系统模块信息表
cursor.execute("CREATE TABLE IF NOT EXISTS `work_module` ("
"`id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,"
"`name` VARCHAR(32) NOT NULL,"
"`page_url` VARCHAR (255) DEFAULT '',"
"`sort` INT(11) NOT NULL DEFAULT 0,"
"`parent_id` INT(11) DEFAULT NULL,"
"`is_active` BIT NOT NULL DEFAULT 1,"
"`is_private` BIT NOT NULL DEFAULT 0"
");")
# 创建用户信息表
cursor.execute(
"CREATE TABLE IF NOT EXISTS `user_info` ("
"`id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,"
"`name` VARCHAR(255) NOT NULL UNIQUE,"
"`fixed_code` VARCHAR(8) NOT NULL UNIQUE,"
"`join_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,"
"`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,"
"`password` VARCHAR(32) NOT NULL,"
"`phone` VARCHAR(11) NOT NULL DEFAULT '',"
"`email` VARCHAR(64) NOT NULL DEFAULT '',"
"`is_admin` BIT NOT NULL DEFAULT 0,"
"`is_active` BIT NOT NULL DEFAULT 0,"
"`org_id` INT(11) NOT NULL DEFAULT 0"
");")
# 用户与不用提交的模块第三方表
cursor.execute("CREATE TABLE IF NOT EXISTS `user_ndo_module` ("
"`id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,"
"`user_id` INT(11) NOT NULL,"
"`module_id` INT(11) NOT NULL,"
"`is_active` BIT NOT NULL DEFAULT 1,"
"UNIQUE KEY `user_id`(`user_id`,`module_id`)"
");")
# 系统中品种信息表
cursor.execute("CREATE TABLE IF NOT EXISTS `variety` ("
"`id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,"
"`name` VARCHAR(32) NOT NULL UNIQUE,"
"`en_code` VARCHAR(16) NOT NULL DEFAULT '',"
"`sort` INT(11) NOT NULL DEFAULT 0,"
"`parent_id` INT(11) DEFAULT NULL,"
"`is_active` BIT NOT NULL DEFAULT 1,"
"UNIQUE KEY `name_unique`(`name`,`en_code`)"
");")
"""添加系统默认菜单"""
# 加入系统设置菜单组
save_module = "INSERT INTO `work_module` (`name`,`page_url`,`parent_id`) VALUES ('系统设置','',NULL);"
cursor.execute(save_module)
new_mid = db_connection.insert_id() # 返回的id
# 修改sort
update_sort_statement = "UPDATE `work_module` SET `sort`=%s WHERE `id`=%s;"
cursor.execute(update_sort_statement, (new_mid, new_mid))
# 插入系统模块管理
insert_statement = "INSERT INTO `work_module` (`name`,`page_url`,`parent_id`) VALUES ('系统模块管理','sys-modules.html',%s);"
cursor.execute(insert_statement, (new_mid, ))
new_mid = db_connection.insert_id() # 返回的id
# 修改sort字段的值
update_sort_statement = "UPDATE `work_module` SET `sort`=%s WHERE `id`=%s;"
cursor.execute(update_sort_statement, (new_mid, new_mid))
"""添加一个默认管理员信息"""
# 新增一个管理员信息
save_admin = "INSERT INTO `user_info` (`name`,`fixed_code`,`password`,`is_admin`,`is_active`) VALUES ('管理员','rdyj321','bbe7977cef5fcf80a39b801fcfdda5e0', 1, 1);"
cursor.execute(save_admin)
# 提交数据
db_connection.commit()
db_connection.close() # 关闭数据库连接
def create_tables():
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
# 客户端信息表
cursor.execute(
"CREATE TABLE IF NOT EXISTS `info_client` ("
"`id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,"
"`name` VARCHAR(128) NOT NULL DEFAULT '',"
"`machine_code` VARCHAR(32) NOT NULL UNIQUE,"
"`join_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,"
"`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,"
"`is_manager` BIT NOT NULL DEFAULT 0,"
"`origin` VARCHAR(10) NOT NULL DEFAULT '',"
"`is_active` BIT NOT NULL DEFAULT 1"
");")
# 系统功能模块表
# level字段对应用户的角色枚举值
cursor.execute("CREATE TABLE IF NOT EXISTS `info_module` ("
"`id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,"
"`name` VARCHAR(128) NOT NULL UNIQUE,"
"`parent_id` INT(11) DEFAULT NULL,"
"`level` INT(11) DEFAULT 5,"
"`sort` INT(11) DEFAULT 0,"
"`is_active` BIT NOT NULL DEFAULT 1"
");")
# 用户-模块表(记录可进入的，level表示的是那些用户可见与否)
cursor.execute("CREATE TABLE IF NOT EXISTS `link_user_module` ("
"`id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,"
"`user_id` INT(11) NOT NULL,"
"`module_id` INT(11) NOT NULL,"
"`expire_time` DATETIME NOT NULL,"
"UNIQUE KEY `usermodule`(`user_id`,`module_id`)"
");")
# 添加系统管理模块
cursor.execute(
"INSERT INTO `info_module` (`name`,`level`) VALUES ('系统管理', 4);")
new_mid = db_connection.insert_id()
# 更新sort
cursor.execute("UPDATE `info_module` SET `sort`=%d WHERE `id`=%d" %
(new_mid, new_mid))
cursor.execute(
"INSERT INTO `info_module` (`name`,`parent_id`,`level`) VALUES ('运营管理', %d, 2);"
% new_mid)
new_mid = db_connection.insert_id()
cursor.execute("UPDATE `info_module` SET `sort`=%d WHERE `id`=%d" %
(new_mid, new_mid))
cursor.execute(
"INSERT INTO `info_module` (`name`,`parent_id`,`level`) VALUES ('角色管理', %d, 1);"
% new_mid)
new_mid = db_connection.insert_id()
cursor.execute("UPDATE `info_module` SET `sort`=%d WHERE `id`=%d" %
(new_mid, new_mid))
# 品种信息表
cursor.execute("CREATE TABLE IF NOT EXISTS `info_variety` ("
"`id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,"
"`name` VARCHAR(128) NOT NULL,"
"`name_en` VARCHAR(16) NOT NULL DEFAULT '',"
"`parent_id` INT(11) DEFAULT NULL,"
"`exchange` TINYINT(3) NOT NULL DEFAULT 0,"
"`sort` INT(11) DEFAULT 0,"
"`is_active` BIT NOT NULL DEFAULT 1,"
"UNIQUE KEY `varietyname`(`name`,`name_en`)"
");")
# 增加品种4个分组
cursor.execute("INSERT INTO `info_variety` (`name`) VALUES ('金融股指');")
new_vid = db_connection.insert_id()
cursor.execute("UPDATE `info_variety` SET `sort`=%d WHERE `id`=%d" %
(new_vid, new_vid))
cursor.execute("INSERT INTO `info_variety` (`name`) VALUES ('农业产品');")
new_vid = db_connection.insert_id()
cursor.execute("UPDATE `info_variety` SET `sort`=%d WHERE `id`=%d" %
(new_vid, new_vid))
cursor.execute("INSERT INTO `info_variety` (`name`) VALUES ('化工材料');")
new_vid = db_connection.insert_id()
cursor.execute("UPDATE `info_variety` SET `sort`=%d WHERE `id`=%d" %
(new_vid, new_vid))
cursor.execute("INSERT INTO `info_variety` (`name`) VALUES ('黑色金属');")
new_vid = db_connection.insert_id()
cursor.execute("UPDATE `info_variety` SET `sort`=%d WHERE `id`=%d" %
(new_vid, new_vid))
db_connection.commit()
db_connection.close()
