def get_engineer_count_with_ipc(ipc_type, town, limit=40): """ 根据ipc获取相关的所有工程师数量 :param ipc_type: 'ipc_root' or 'ipc_class' or 'ipc_class_sm' :param town: "开发区" :param limit: 数量 :return: tuple ==> ( {"ipc": "A", "number": 123}, ... ) """ sql = """ SELECT COUNT(num) as number,ipc from (SELECT COUNT(engineer_patent.engineer_id) as num, {ipc_code} as ipc FROM engineer_patent LEFT JOIN enterprise_engineer on engineer_patent.engineer_id=enterprise_engineer.engineer_id LEFT JOIN en_base_info on enterprise_engineer.en_id=en_base_info.en_id WHERE en_town="{town}" GROUP BY {ipc_code}, engineer_patent.engineer_id) as t GROUP BY ipc ORDER BY number desc limit {limit}""".format( ipc_code=ipc_type, town=town, limit=limit) data = db.select(sql) return data
def get_target_info(department_id, year): """ 获取某部门、某年的 任务目标信息 :return :empty tuple or list of dict ==>[{"id","name", "numbers"}] """ sql = "select id, target_name as name, numbers from target where department_id=? and year=?" return db.select(sql, department_id, year)
def getAllDistribution(town="开发区"): """ 获取全部企业的知识产权分布情况 """ sql = "SELECT COUNT(en_id) as total_com, sum(has_property) as focus_com " \ "FROM enterprise_property WHERE en_town='%s'" % town data = db.select(sql) data = format_pie_data(data) if data is None: return jsonify({"status": "获取数据失败"}) return jsonify({ "pie_data": [ { "key": "拥有知识产权的企业", "value": data["focus_com"], "click2": 1 }, { "key": "其他企业", "value": data["total_com"] - data["focus_com"], "click2": False }, ], "status": "ok" "" })
def get_ipc_map(depth=0): """ 获取ipc目录 :return: dict ==> {"ipc_id":"A", "ipc_content": "xxxx"} """ return db.select('select ipc_id,ipc_content from ipc where depth=?', depth)
def companyPropertySequence(town="开发区", property_type='发明专利'): """ 根据企业所拥有的某类知识产权数量排名 """ property_type_dict = { '发明专利': "patent", '实用新型专利': "utility_model_patent", '外观设计': "design_patent", '软件著作权': "software_copyright" } if str(property_type) not in property_type_dict: return jsonify({"status": "property_type 参数类型错误"}) key = property_type_dict.get(property_type) sql = "SELECT enterprise_property.en_id as id,en_name as name,{key} as num FROM enterprise_property " \ "LEFT JOIN en_base_info " \ "on enterprise_property.en_id=en_base_info.en_id " \ "WHERE enterprise_property.en_town='{town}' ORDER BY {key} DESC LIMIT 30".format(key=key, town=town) data = db.select(sql) if 0 == len(data): return jsonify({"status": "数据获取失败"}) return jsonify({ "data": data, "bar_graph": True, "title": "拥有 %s 的企业排名" % property_type, "xAxis_name": "企业名", "yAxis_name": "数量/件", "status": "ok" })
def get_engineer_group_by_ipc(ipc_type, ipc_code, town, limit=1200): """ 根据ipc获取工程师信息以及所在的公司 :param ipc_type: 选择的字段: 'ipc_root' or 'ipc_class' or 'ipc_class_sm' :param ipc_code: ipc代码: "A","B","B01","B03D",... :param town: 所属区镇 :param limit: 工程师数量上限 :return: tuple ==> ( {"en_id": 24790, "en_name": "昆山康佳电子有限公司", "engineer_id": 20, "engineer_name": "白占良"}, ... ) """ sql = """SELECT enterprise_engineer.en_id as en_id, enterprise_engineer.en_name as en_name, enterprise_engineer.engineer_id as engineer_id, enterprise_engineer.engineer_name as engineer_name FROM engineer_patent LEFT JOIN enterprise_engineer on engineer_patent.engineer_id=enterprise_engineer.engineer_id LEFT JOIN en_base_info on enterprise_engineer.en_id=en_base_info.en_id WHERE engineer_patent.{ipc_type}="{ipc_code}" and en_town="{town}" GROUP BY engineer_patent.engineer_id limit {limit} """\ .format(ipc_type=ipc_type, ipc_code=ipc_code, town=town, limit=limit) data = db.select(sql) return data
def get_records(user_name): """ 获取用户有关的消息 """ sql = 'select * from station_news where receiver_name= %s'.format( user_name) results = db.select(sql, user_name) return results
def get_server_list(): """ 获取可用服务商列表 : return: None or [{id, name, principal}, ...] """ sql = "SELECT charger_id as id, service_provider_name as name, charger_name as principal " \ "from service_provider where status=1" return db.select(sql)
def get_all_records(department_id): """ 获取某一部门的全部提交记录 """ sql = "SELECT submit_record.*, service_provider_name as name, " \ "FROM_UNIXTIME(submit_time, '%%Y-%%m-%%d') as sub_time " \ "FROM submit_record LEFT JOIN service_provider on submit_record.charger_id=service_provider.charger_id " \ "WHERE department_id=? ORDER BY audit_status ASC, submit_time DESC" return db.select(sql, department_id)
def get_assignments(charger_id): """ 根据负责人的id获取 :param charger_id: :return: """ sql = 'select * from assignment where charger_id=?' results = db.select(sql, charger_id) return results
def get_records_with_task(department_id, task): """ 获取某部门下,某类任务的全部提交 """ sql = "SELECT submit_record.*, service_provider_name as name, " \ "FROM_UNIXTIME(submit_time, '%%Y-%%m-%%d') as sub_time " \ "FROM submit_record LEFT JOIN service_provider on submit_record.charger_id=service_provider.charger_id " \ "WHERE department_id=? and mission_type=? ORDER BY audit_status ASC, submit_time DESC" return db.select(sql, department_id, task)
def get_patent_number_by_type(area="开发区", year=2020): """ 获取某一区域下近n年来 每年 不同类型的专利数量 """ sql = """SELECT a.pa_year, a.pa_type, count(1) number from enterprise_patent a LEFT JOIN en_base_info b on a.en_id=b.en_id where b.en_town='{}' and a.pa_year < {} GROUP BY a.pa_year, a.pa_type""".format(area, str(year)) outcome_list = db.select(sql) return outcome_list
def get_different_patent_type_count(town="开发区"): """ 获取某一区镇的各类知识产权数量 :return: None or tuple of dict """ sql = "SELECT pa_type as type, COUNT(pa_type) as count FROM enterprise_patent " \ "LEFT JOIN en_base_info on enterprise_patent.en_id = en_base_info.en_id " \ "WHERE en_town='{town}' GROUP BY pa_type".format(town=town) return db.select(sql)
def get_service_comparison(department_id, mission_type): """ 获取某一部门某一类型的各服务商完成任务的数量 """ sql = """ SELECT a.task_target, a.charger_id, a.charger_name, a.progress, s.service_provider_name company from assignment a left join service_provider s on a.charger_id=s.charger_id where type="{}" and department_id={} """.format(mission_type, department_id) return db.select(sql)
def get_search_history(self): """ 获取历史搜索记录 :return: """ sql = """ select search_text, gmt_create from search_history limit 5 """ return db.select(sql, bind='data_mining')
def getProperty(town="开发区"): """ 获取知识产权分布 """ sql = "SELECT SUM(patent) as patent, SUM(utility_model_patent) as utility, " \ "SUM(design_patent) as design, SUM(software_copyright) as sw FROM enterprise_property WHERE en_town='%s'" \ % town data = db.select(sql) return format_property_data(data, click2=2)
def get_service_situation(department_id): """ 根据部门id获取该部门的所用服务商的任务执行情况 """ sql = """ SELECT s.charger_name, s.charger_id, s.service_provider_name company, a.task_id, a.type, a.task_target, a.progress, FROM_UNIXTIME(a.deadline, "%%Y-%%m-%%d") deadline from assignment a left join service_provider s on a.charger_id=s.charger_id where a.department_id={} and a.status != 3 ORDER BY deadline desc """.format(department_id) return db.select(sql)
def get_enterprise_count_with_ipc(length, params, town, limit=40): # 根据ipc获取对应的专利数量 # sql_format = 'select left(pa_main_kind_num, {length}) as code, count(1) as amount ' \ # 'from enterprise_patent where left(pa_main_kind_num, {length}) in ({params})' \ # 'group by code order by amount desc limit {limit}' sql_format = """SELECT code, count(1) as number from (SELECT pa_applicant, left(pa_main_kind_num, {length}) as code FROM `enterprise_patent` where left(pa_main_kind_num, {length}) in ({params}) group by pa_applicant, code) as t group by code limit {limit}""" sql = sql_format.format(length=length, params=params, limit=limit) # 查询,并返回dict的数据 return db.select(sql)
def get_teacher_basic_info(self): """ 根据教师的多个id (id1, id2, ...) 获取教师的基本信息 :return: {id1: {"school": school, "institution": institution, "school_id": school_id, "institution_id": institution_id, "lab": lab, "name": name, "title": title }, id2: {.. } ... } """ logging.warn( "-------------------------------------get_teacher_basic_info----------------------------------" ) teacher_patent = self.teacher_patent if len(teacher_patent) == 0: return {} sql = """ select i.id teacher_id, i.name name, s.name school, s.id school_id, i.lab, i.institution institution, "教授" title, 111 institution_id from clean_inventor i LEFT JOIN school s on i.school_id = s.id where i.id in ( """ for d in teacher_patent: teacher_id = d["teacher_id"] sql += str(teacher_id) + "," sql = sql[0:-1] sql += ")" logging.warning("-----获取专家信息----" + sql) res = db.select(sql, bind='data_mining') teacher_basic_info = {} for d in list(res): tmp_dict = { "school": d["school"], "lab": d["lab"], "institution": d["institution"], "name": d["name"], "school_id": d["school_id"], "institution_id": d["institution_id"], "title": d["title"] } teacher_basic_info[d["teacher_id"]] = tmp_dict return teacher_basic_info
def companyProperty(com_id): """ 获取某一企业的知识产权信息 """ try: com_id = int(com_id) except Exception as e: return jsonify({"status": "企业id格数错误"}) sql = "SELECT patent as patent, utility_model_patent as utility, design_patent as design, software_copyright as sw " \ "FROM enterprise_property WHERE en_id=%d" % int(com_id) data = db.select(sql) return format_property_data(data)
def count_patents_with_ipc(length, ipc_list, limit=20): """ 根据IPC的特征按照专利的主分类号前若干个字符对专利进行统计 :param length: 取ipc_list的前几个字符 :param ipc_list: IPC组成的数组 :param limit: 限制返回的个数 :return: [{'code': '', 'amount': 1}, ...] """ # 根据ipc获取对应的专利数量 sql_format = 'select left(pa_main_kind_num, {length}) as code, count(1) as amount ' \ 'from enterprise_patent where left(pa_main_kind_num, {length}) in ({in_})' \ 'group by code order by amount desc limit {limit}' in_ = [] for ipc in ipc_list: in_.append('"%s"' % ipc) sql = sql_format.format(length=length, in_=','.join(in_), limit=limit) # 查询,并返回dict的数据 return db.select(sql)
def get_teacher_project_info(self): """ 获取该专家对应的项目信息 :return: [{"teacher_id": **, "project_name": **}] """ sql = """ select p.teacher_id, p.name project_name from funds p where p.category = 1 and p.teacher_id in ( """ if len(self.teacher_patent) == 0: return [] for d in self.teacher_patent: teacher_id = d["teacher_id"] sql += str(teacher_id) + "," sql = sql[0:-1] sql += ")" logging.warning("-----获取项目信息----" + sql) result = db.select(sql, bind='data_mining') return result
def get_teacher_patent(self, patent_id_list, school): """ 根据获取的专利id得到对应的教师id,以及专利名,及其专利公开号 :return: [ { "teacher_id": 11, "patent_id": 11, "patent_name": } ] """ sql = """ select i.id teacher_id, i.name teacher_name, p.id patent_id, p.title patent_name, s.id school_id, s.name school_name, p.publication_number, pt.summary from clean_inventor i LEFT JOIN clean_inventor_patent ip on i.id = ip.inventor_id LEFT JOIN patent p on ip.patent_id = p.id left join patent_text pt on pt.patent_id = p.id LEFT JOIN school s on i.school_id = s.id where p.id in ( """ # 上面sql中的s.name = school 条件用于只取某一学校的成果 teacher_patent = [] get_patent_num = 20 # 一次获取专利的数量 i = 0 if len(patent_id_list) != 0: for patent_id in patent_id_list: if i > get_patent_num: break sql += str(patent_id) + "," i += 1 sql = sql[0:-1] sql += ")" logging.warning("-----获取专利信息----" + sql) teacher_patent = db.select(sql, {"school": school}, bind='data_mining') self.teacher_patent = teacher_patent
def get_records(charger_id): sql = 'select *,FROM_UNIXTIME(submit_time, "%%Y-%%m-%%d") as sub_time from submit_record where charger_id=? ' \ 'order by submit_time desc' results = db.select(sql, charger_id) return results
def get_unread_records_num(department_id): """ 获取某部门未读消息数量 """ sql = "SELECT count(record_id) as count FROM submit_record WHERE department_id=? AND audit_status=0" return db.select(sql, department_id)
def get_departments(department_ids): sql_format = 'select * from department where id in(%s)' sql = sql_format % ','.join(['?'] * len(department_ids)) results = db.select(sql, *department_ids) return results
def get_records(charger_id): sql = 'select * from submit_record where charger_id=?' results = db.select(sql, charger_id) return results