def add_ranking(request):
"""
添加榜单
:param request:
:return:
"""
if request.method == "POST":
try:
_table = {
"name_cn": request.POST["table_name_cn"],
"type": 1
} # 榜单
_fields = request.POST.getlist("field_name_cn")
table_id = Table.create_table(_table, _fields)
if table_id:
return HttpResponseRedirect("/backend/ranking/" +
str(table_id) + "/")
else:
raise Exception("Add Ranking Failed")
except Exception as e:
logger.error(str(e))
messages.error(request, "添加榜单失败")
urls = copy.deepcopy(SIDEBAR_URL)
urls[3]["active"] = True
return render(request, "backend/ranking/add.html", {
"self": request.user,
"urls": urls
})
def get_ranking(request, ranking_id):
"""
:return:
"""
ranking = Table.get_table_by_id(int(ranking_id))
if request.method == "POST":
print(request.POST)
try:
fields = request.POST.getlist("field_name_cn")
for _field in fields:
if not Field.objects.filter(table=ranking,
name_cn=_field).count():
Field.objects.create(table=ranking, name_cn=_field)
else:
messages.info(request, "存在重复字段，已忽略")
messages.success(request, "添加字段成功")
except Exception as e:
logger.error(str(e))
messages.error(request, "添加字段失败")
fields = Table.get_fields_by_table_id(int(ranking_id)).order_by("id")
urls = copy.deepcopy(SIDEBAR_URL)
urls[0]["active"] = True
return render(request, "backend/ranking/ranking.html", {
"self": request.user,
"ranking": ranking,
"fields": fields,
"urls": urls
})
def create_database(conn, cursor, db_name):
r = 1
try:
sql = "CREATE DATABASE IF NOT EXISTS %s" % db_name # orginal database-create sql
r = sql_execute(conn, cursor, sql, None)
except pymysql.Error as e:
logger.error("------------Error %d: %s" % (e.args[0], e.args[1]))
return r
def add_news(request):
"""
:param request:
:return:
"""
if request.method == "POST":
try:
news = {
"user":
request.user,
"title":
request.POST.get("title"),
"keywords":
request.POST.get("keywords"),
"link":
request.POST.get("link"),
"abstract":
request.POST.get("abstract", ""),
"content":
request.POST.get("content", ""),
"is_published":
True if "is_published" in request.POST else False,
"is_allow_comments":
True if "is_allow_comments" in request.POST else False,
"is_stick_top":
True if "is_stick_top" in request.POST else False,
"is_bold":
True if "is_bold" in request.POST else False
}
colleges = request.POST["colleges"].split(
",") if request.POST["colleges"] != '' else []
tags = request.POST["tags"].split(
",") if request.POST["tags"] != '' else []
_news = create_news(news, tags, colleges)
messages.success(request, "添加新闻成功")
return HttpResponseRedirect("/backend/news/modify/" +
str(_news.id) + "/")
except Exception as e:
logger.error(str(e))
logger.error(traceback.format_exc())
messages.error(request, "添加新闻失败")
form = NewsUEditorModelForm()
urls = copy.deepcopy(SIDEBAR_URL)
urls[2]["active"] = True
add_news_classification = news_classification({})
return render(
request, "backend/news/add.html", {
"self": request.user,
"fields": add_news_classification,
"urls": urls,
"form": form,
"get_colleges_by_nation_url": "/api/college/by/nation/",
"news_image_upload_url": "/api/news/image_upload/"
})
def modify_news(request, news_id):
"""
:return:
"""
news = get_news_by_id(news_id)
if request.method == "POST":
try:
_news = {
"id":
int(news_id),
"user":
request.user,
"title":
request.POST.get("title"),
"keywords":
request.POST.get("keywords"),
"link":
request.POST.get("link"),
"abstract":
request.POST.get("abstract", ""),
"content":
request.POST.get("content", ""),
"is_published":
True if "is_published" in request.POST else False,
"is_allow_comments":
True if "is_allow_comments" in request.POST else False,
"is_stick_top":
True if "is_stick_top" in request.POST else False,
"is_bold":
True if "is_bold" in request.POST else False
}
colleges = request.POST["colleges"].split(
",") if request.POST["colleges"] != '' else []
tags = request.POST["tags"].split(
",") if request.POST["tags"] != '' else []
news = update_news(_news, map(int, tags), map(int, colleges))
messages.success(request, "修改新闻成功")
except Exception as e:
logger.error(str(e))
logger.error(traceback.format_exc())
messages.error(request, "修改新闻失败")
form = NewsUEditorModelForm(instance=news)
modify_news_classification = news_classification(news.__dict__)
urls = copy.deepcopy(SIDEBAR_URL)
urls[0]["active"] = True
return render(
request, "backend/news/modify.html", {
"self": request.user,
"fields": modify_news_classification,
"urls": urls,
"form": form,
"get_colleges_by_nation_url": "/api/college/by/nation/",
"news_image_upload_url": "/api/news/image_upload/"
})
def drop_tables(cursor, tables_list):
if len(tables_list) == 0: # none table to drop
logger.error("None table to drop.")
return -1
try:
for table in tables_list: # fetch one table a time
cursor.execute("DROP TABLE %s" % table)
logger.debug("SQL Result: %s", cursor.fetchone())
except pymysql.Error as e:
logger.error("Error %d: %s" % (e.args[0], e.args[1]))
return 1
def sql_execute(conn, cursor, sql, args):
try:
cursor.execute(sql, args) # execute orginal SQL, with args or none.
if conn is not None:
conn.commit() # DELETE, INSERT, UPDATE operations should commit.
ret = cursor.fetchall()
except pymysql.Error as e:
logger.error("mysql_base_api sql_execute Error %d: %s" %
(e.args[0], e.args[1]))
ret = (e.args[0], e.args[1])
return ret # sql-execute results.
def sql_execute_keyreturn(conn, cursor, sql, args):
pmkey = None # store primary keys
try:
cursor.execute(sql, args) # execute orginal SQL, with args or none.
pmkey = cursor.lastrowid
if conn is not None:
conn.commit() # DELETE, INSERT, UPDATE operations should commit.
except pymysql.Error as e:
logger.error("mysql_base_api sql_execute Error %d: %s" %
(e.args[0], e.args[1]))
return pmkey # primary keys and sql-execute results.
def sql_executemany(conn, cursor, sql, args):
pmkey = None # store primary keys
try:
cursor.executemany(sql,
args) # execute orginal SQL, with args or none.
pmkey = cursor.lastrowid
if conn is not None:
conn.commit() # DELETE, INSERT, UPDATE operations should commit.
except pymysql.Error as e:
logger.error("Error %d: %s" % (e.args[0], e.args[1]))
logger.error(traceback.format_exc())
return pmkey # sql-execute results.
def create_tables(cursor, create_scripts):
if len(create_scripts) == 0: # none sql script
logger.info("None table to create.")
return -1
try:
for script in create_scripts: # fetch one table-create script a time
cursor.execute(script)
logger.error("SQL Result: %s", cursor.fetchone())
except pymysql.Error as e:
logger.error("Error %d: %s" % (e.args[0], e.args[1]))
return 0
return 1
def clean_college(request):
"""
院校清空
:param request:
:return:
"""
return_dict = {}
try:
College_model.objects.all().delete()
return_dict["success"] = "院校清空成功"
except Exception as e:
logger.error(str(e))
logger.error(traceback.format_exc())
return_dict["error"] = "院校清空失败"
return JsonResponse(return_dict)
def delete_college(request):
"""
删除一所院校
:param request:
:return:
"""
return_dict = {}
try:
College_model.objects.get(id=int(request.POST["_id"])).delete()
return_dict["success"] = "删除成功"
except Exception as e:
logger.error(str(e))
logger.error(traceback.format_exc())
return_dict["error"] = "删除失败"
return JsonResponse(return_dict)
def delete_news(request):
"""
删除一所院校
:param request:
:return:
"""
return_dict = {}
try:
News.objects.get(id=int(request.POST["_id"])).delete()
return_dict["success"] = "删除成功"
except Exception as e:
logger.error(str(e))
logger.error(traceback.format_exc())
return_dict["error"] = "删除失败"
return HttpResponse(json.dumps(return_dict))
def delete_ranking(request):
"""
删除一所
:param request:
:return:
"""
return_dict = {}
try:
Table.delete_table(int(request.POST["_id"]))
return_dict["success"] = "删除成功"
except Exception as e:
logger.error(str(e))
logger.error(traceback.format_exc())
return_dict["error"] = "删除失败"
return JsonResponse(return_dict)
def batch_delete_college(request):
"""
批量删除院校
:param request:
:return:
"""
return_dict = {}
try:
delete_list = request.POST.getlist("_ids[]")
College_model.objects.filter(id__in=delete_list).delete()
return_dict["success"] = "删除成功"
except Exception as e:
logger.error(str(e))
logger.error(traceback.format_exc())
return_dict["error"] = "删除失败"
return JsonResponse(return_dict)
def add_college(request):
"""
添加一所院校
:param request:
:return:
"""
if request.method == "POST":
try:
nation = Nation.objects.get(code=request.POST["city"])
college = College_model.objects.create(
name_cn=request.POST["name_cn"],
id_code=request.POST["id_code"],
department=Department.objects.get(
id=int(request.POST["department"])),
edu_level=EduLevel.objects.get(
id=int(request.POST["edu_level"])),
edu_class=EduClass.objects.get(
id=int(request.POST["edu_class"])),
city=nation.city,
area=Area.objects.get(nation_code_2=nation.code[:2]).name_cn,
province=Nation.objects.get(id=nation.parent).province,
nation_code=nation.code,
is_vice_ministry=True
if "is_vice_ministry" in request.POST else False,
is_211=True if "is_211" in request.POST else False,
is_985=True if "is_985" in request.POST else False,
is_985_platform=True
if "is_985_platform" in request.POST else False,
is_double_first_class=True
if "is_double_first_class" in request.POST else False,
is_cancelled=True if "is_cancelled" in request.POST else False,
setup_time=get_date_from_post(
request.POST.get("setup_time", "")),
cancel_time=get_date_from_post(
request.POST.get("cancel_time", "")),
note=request.POST.get("note", ""),
transfer_to=request.POST.get("transfer_to", ""))
messages.success(request, "添加成功")
return HttpResponseRedirect("/backend/college/modify/" +
str(college.id) + "/")
except IntegrityError as e: # 处理重复添加错误
logger.error(str(e))
logger.error(traceback.format_exc())
messages.error(request, "添加失败，院校名称或识别码重复添加")
except Exception as e:
logger.error(str(e))
logger.error(traceback.format_exc())
messages.error(request, "添加失败，请检查添加的数据")
urls = copy.deepcopy(SIDEBAR_URL)
urls[2]["active"] = True
add_college_classification = college_classification(obj={})
return render(request, "backend/college/add.html", {
"self": request.user,
"fields": add_college_classification,
"urls": urls
})
def delete_rankings(request):
"""
删除一所
:return:
"""
return_dict = {}
try:
delete_batch = int(request.POST["_id"])
batch = BatchOfTable.objects.get(id=delete_batch)
Ranking.objects.filter(batch=batch).delete()
batch.delete()
return_dict["success"] = "删除成功"
except Exception as e:
logger.error(str(e))
logger.error(traceback.format_exc())
return_dict["error"] = "删除失败"
return JsonResponse(return_dict)
def batch_delete_ranking(request):
"""
批量删除
:param request:
:return:
"""
return_dict = {}
try:
delete_list = request.POST.getlist("_ids[]")
for i in delete_list:
Table.delete_table(int(i))
return_dict["success"] = "删除成功"
except Exception as e:
logger.error(str(e))
logger.error(traceback.format_exc())
return_dict["error"] = "删除失败"
return JsonResponse(return_dict)
def batch_delete_rankings_batches(request):
"""
批量删除
:return:
"""
return_dict = {}
try:
delete_list = request.POST.getlist("_ids[]")
batches = BatchOfTable.objects.filter(id__in=delete_list)
for _batch in batches:
Ranking.objects.filter(batch=_batch).delete()
batches.delete()
return_dict["success"] = "删除成功"
except Exception as e:
logger.error(str(e))
logger.error(traceback.format_exc())
return_dict["error"] = "删除失败"
return JsonResponse(return_dict)
def get_nation_code(city):
"""
根据城市名获取行政区划代码
:param city:
:return:
"""
nation_code = ""
try:
if len(city.strip()) == 0:
raise Exception()
nation_code = Nation.objects.get(
Q(city=city, province="")
| Q(district=city, province="", city="")).code
except ObjectDoesNotExist:
logger.error(city.encode("utf-8") + "Does not Exist")
except Exception as e:
logger.error(str(e) + ": " + city)
finally:
return nation_code
def modify_college(request, college_id):
"""
修改一所院校
:param request:
:param college_id:
:return:
"""
college = College_model.objects.get(id=college_id) # 根据id获取院校
if request.method == "POST":
try:
nation = Nation.objects.get(code=request.POST["city"])
college.name_cn = request.POST["name_cn"]
college.id_code = request.POST["id_code"]
college.department = Department.objects.get(
id=int(request.POST["department"]))
college.edu_level = EduLevel.objects.get(
id=int(request.POST["edu_level"]))
college.edu_class = EduClass.objects.get(
id=int(request.POST["edu_class"]))
college.city = nation.city
college.area = Area.objects.get(
nation_code_2=nation.code[:2]).name_cn
college.province = Nation.objects.get(id=nation.parent).province
college.is_vice_ministry = True if "is_vice_ministry" in request.POST else False
college.is_211 = True if "is_211" in request.POST else False
college.is_985 = True if "is_985" in request.POST else False
college.is_985_platform = True if "is_985_platform" in request.POST else False
college.is_double_first_class = True if "is_double_first_class" in request.POST else False
college.is_cancelled = True if "is_cancelled" in request.POST else False
college.setup_time = get_date_from_post(
request.POST.get("setup_time", ""))
college.cancel_time = get_date_from_post(
request.POST.get("cancel_time", ""))
college.note = request.POST.get("note", "")
college.transfer_to = request.POST.get("transfer_to", "")
college.save() # 保存
messages.success(request, "修改成功")
except IntegrityError as e: # 处理重复添加错误
logger.error(str(e))
logger.error(traceback.format_exc())
messages.error(request, "添加失败，院校名称或识别码重复添加")
except Exception as e:
logger.error(str(e))
logger.error(traceback.format_exc())
messages.error(request, "添加失败，请检查添加的数据")
fields_dict = college_classification(college.__dict__)
urls = copy.deepcopy(SIDEBAR_URL)
urls[0]["active"] = True
return render(request, "backend/college/modify.html", {
"self": request.user,
"fields": fields_dict,
"urls": urls
})
def image_upload(request):
"""
:param request:
:return:
"""
return_dict = {"message": "error", "success": 0}
try:
if request.method == "POST":
if "editormd-image-file" in request.FILES:
news_image = NewsImage.objects.create(
image=request.FILES["editormd-image-file"])
return_dict = {
"message": "success",
"success": 1,
"url": MEDIA_URL + str(news_image.image)
}
except Exception as e:
logger.error(str(e))
return HttpResponse(json.dumps(return_dict))
def import_college(request):
"""
从excel导入院校信息
:param request:
:return:
"""
model_fields = [x.verbose_name
for x in College_model._meta.fields] # 读取数据库中院校表的字段别名
if request.method == "POST":
return_dict = {}
try:
_file = request.FILES.get("file_upload") # 上传的文件
wb = excel_loader.load_excel(_file) # 读取excel
head = excel_loader.get_excel_head(wb)
body = excel_loader.get_excel_body_generator(wb)
temp_dict = {}
for f in model_fields[1:]:
if f in head:
temp_dict[f] = head.index(f) # 找到数据库字段对应在excel的位置
for record in body:
nation_code = get_nation_code(
record[temp_dict[model_fields[6]]])
College_model.objects.create( # 创建记录
name_cn=empty_is_empty(record[temp_dict[model_fields[1]]]),
id_code=empty_is_empty(record[temp_dict[model_fields[2]]]),
department=get_obj_or_insert_obj(
Department,
empty_is_empty(record[temp_dict[model_fields[3]]])),
area=Area.objects.get(
nation_code_2=nation_code[:2]).name_cn
if not nation_code == '' else '',
province=empty_is_empty(
record[temp_dict[model_fields[5]]]),
city=empty_is_empty(record[temp_dict[model_fields[6]]]),
nation_code=nation_code,
edu_level=get_obj_or_insert_obj(
EduLevel,
empty_is_empty(record[temp_dict[model_fields[8]]])),
edu_class=get_obj_or_insert_obj(
EduClass,
empty_is_empty(record[temp_dict[model_fields[9]]])),
is_vice_ministry=empty_is_false(
record[temp_dict[model_fields[10]]]),
is_211=empty_is_false(record[temp_dict[model_fields[11]]]),
is_985=empty_is_false(record[temp_dict[model_fields[12]]]),
is_985_platform=empty_is_false(
record[temp_dict[model_fields[13]]]),
is_double_first_class=empty_is_false(
record[temp_dict[model_fields[14]]]),
setup_time=get_date_from_excel(
record[temp_dict[model_fields[15]]]),
cancel_time=get_date_from_excel(
record[temp_dict[model_fields[16]]]),
note=empty_is_empty(record[temp_dict[model_fields[17]]]),
is_cancelled=empty_is_false(
record[temp_dict[model_fields[18]]]),
transfer_to=empty_is_empty(
record[temp_dict[model_fields[19]]]),
)
return_dict["success"] = "success"
except Exception as e:
logger.error(str(e))
logger.error(traceback.format_exc())
return_dict["error"] = "出现错误，请检查文件及内容格式"
finally:
return JsonResponse(return_dict)
urls = copy.deepcopy(SIDEBAR_URL) # 侧边栏网址
urls[3]["active"] = True
return render(
request, "backend/college/import.html", {
"self": request.user,
"fields": model_fields[1:4] + model_fields[5:7] + model_fields[8:],
"urls": urls,
"file_upload_url": "/backend/college/import/",
"college_clean_url": "/backend/college/clean/"
})
def sql_execute_noreturn(cursor, sql, args):
try:
cursor.execute(sql, args)
except pymysql.Error as e:
logger.error("Error %d: %s" % (e.args[0], e.args[1]))
def sql_close(conn, cursor):
try:
cursor.close() # close cursor
conn.close() # close connection
except pymysql.Error as e:
logger.error("Error %d: %s" % (e.args[0], e.args[1]))
def import_ranking(request, ranking_id):
"""
:return:
"""
if request.method == "POST":
return_dict = {}
try:
_file = request.FILES.get("file_upload") # 上传的文件
wb = excel_loader.load_excel(_file) # 读取excel
head = excel_loader.get_excel_head(wb)
body = excel_loader.get_excel_body_generator(wb)
batch = request.POST["extra"]
_table = Table.get_table_by_id(int(ranking_id)) # 获取表
_batch = matching_batch(batch, _table) # 匹配批次，如果已存在批次，抛出错误
matching_field(_table, head) # 如果excel中出现未定义的字段，抛出错误
# 看字段中是否存在“学校名称”或者“学校标识码”
if "学校标识码" in head:
cur = head.index("学校标识码") # 优先记录学校标识码位置
elif "学校名称" in head:
cur = head.index("学校名称") # 记录学校名称位置
else:
cur = -1
# 分别处理每行数据
length = len(head)
for record in body:
data = {}
for i in range(length): # 生成json数据，“字段”：数据
data[head[i]] = empty_is_empty_str(record[i])
ranking = Ranking.objects.create(batch=_batch, data=data)
if cur != -1: # 字段中存在“学校名称”或者“学校标识码”，则建立与对应学校之间的关系
param = record[cur] # “学校名称”或者“学校标识码”对应字段
try:
college = College.objects.get(
Q(name_cn=param) | Q(id_code=param)) # 找到该学校
RankingAndCollegeRelation.objects.create(
college=college, ranking=ranking) # 建立该字段与学校之间的关系
except ObjectDoesNotExist:
logger.warning(param + "不存在")
return_dict["success"] = "success"
except IndexError as e:
logger.error(str(e))
return_dict["error"] = "请选择批次"
except IntegrityError as e: # 仅处理重复添加错误
logger.error(str(e))
return_dict["error"] = "批次重复"
except Exception as e:
logger.error(str(e))
logger.error(traceback.format_exc())
return_dict["error"] = "出现错误，请检查文件及内容格式"
finally:
return JsonResponse(return_dict)
year = YearSeasonMonth.objects.filter(type=1)
_ranking = Table.get_table_by_id(int(ranking_id))
_fields = Table.get_fields_by_table_id(int(ranking_id))
fields = [str(f.name_cn) for f in _fields]
urls = copy.deepcopy(SIDEBAR_URL) # 侧边栏网址
urls[0]["active"] = True
return render(
request, "backend/ranking/import.html", {
"self": request.user,
"fields": fields,
"ranking": _ranking,
"year": year,
"urls": urls,
"file_upload_url":
"/backend/ranking/import/" + str(ranking_id) + "/"
})
