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
