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) + "/" })