def getAgentNode(agent_id, agent_type=LABEL["uniAGENT"]):
"""
根据 id 获取 地区/高校 技转中心中介节点基本信息
"""
cql = "match (n:{agent}) where n.id = '{id}' return n.id as id, n.name as name".format(agent=agent_type,
id=agent_id)
# cql = "match (n:{agent}) where n.id = {id} return n.name as name".format(agent=agent_type, id=agent_id)
return neo4j.run(cql)
def getVisitEngineerRelation(agent_id, limit=30):
cql = "match (agent:{agent_label})-[r:knows]-(p:{target})-[:{rel}]-(org:{org}) where agent.id='{id}' " \
"return agent.id as s_id, agent.name as s_name, p.id as t_id, p.name as t_name, " \
"org.id as org_id, org.name as org_name, " \
"r.activity as activity, r.cooperation as coop, r.visited as visited " \
"order by r.update_time desc limit {limit}" \
.format(agent_label=LABEL["areaAGENT"], target=LABEL["ENGINEER"], rel=RELATION["EMPLOY"], org=LABEL["COMPANY"],
id=agent_id, limit=limit)
return neo4j.run(cql)
def fuzzyMatchOrg(label, name):
"""
根据组织机构名，获取对应id
:param label:
:param name:
:return:[] or [{id:123, name: xxx}, .... ]
"""
cql = """match (node:{label}) where node.name=~".*{name}.*" """ \
"""return node.id as id, node.name as name limit 5""".format(label=label, name=name)
return neo4j.run(cql)
def getVisitTeacherRelation(agent_id, limit=30):
cql = "match (agent:{agent_label})-[r:knows]-(p:{target})-[:include]-(i:{inst})-[:include]-(org:{uni}) " \
"where agent.id='{id}' " \
"return agent.id as s_id, agent.name as s_name, p.id as t_id, p.name as t_name, " \
"i.name as institution, org.id as org_id, org.name as org_name, " \
"r.activity as activity, r.cooperation as coop, r.visited as visited " \
"order by r.update_time desc limit {limit}" \
.format(agent_label=LABEL["uniAGENT"], target=LABEL["TEACHER"], inst=LABEL["INSTITUTION"],
uni=LABEL["UNIVERSITY"], id=agent_id, limit=limit)
return neo4j.run(cql)
def getTeamTechnicalFieldDistribute(team_id, teacher=True):
"""
获取企业工程师 / 高校专家的 团队专利技术分布 ==> 各个ipc下的专利数量分布
:return: [{ipc:xxx, patent:xxx, date: 1429142400}, ...], 结果中包含重复数据，需要去重
"""
label = LABEL["TEACHER"] if teacher is True else LABEL["ENGINEER"]
cql = f"Match (n:{label})-[:write]-(p:Patent)-[:include]-(ipc:IPC) " \
f"where n.team={team_id} " \
f"return ipc.code as ipc, id(p) as patent, p.application_date as date"
return neo4j.run(cql)
def getTeamField(eid, tid):
"""
统计 工程师团队 与 专家团队 中所有节点的industry属性，作为行业交集
:return: [{}]
"""
cql = "match (e:Engineer{team:%d}) with distinct(e.industry) as industry_e, count(e.industry) as count_e " \
"match (t:Teacher{team:%d}) with distinct(t.industry) as industry_t, count(t.industry) as count_t, " \
"industry_e, count_e " \
"return industry_e, industry_t order by count_e desc, count_t desc limit 1" % (eid, tid)
return neo4j.run(cql)
def getTeacherTeamBasicInfo(t_id):
"""
获取专家团队的基本信息, 包括 专家名， 团队人数，所属高校和学院名
:param t_id: 专家团队核心节点的 id
:return: [{name, members, institution, org}]
"""
cql = "match (org:{uni})-[:include]-(i:{inst})-[:include]-(t:{teacher}) where t.id={id} " \
"return org.name as org, t.member as members, t.name as name, i.name as institution" \
.format(uni=LABEL["UNIVERSITY"], inst=LABEL["INSTITUTION"], teacher=LABEL["TEACHER"], id=t_id)
return neo4j.run(cql)
def totalRecommendTeacherForArea(area_id, university_id, team=True):
"""
获取 地区 推荐适合与 高校 合作的企业 的总数
"""
relation = RELATION["CSM"] if team else RELATION.get("PSM")
cql = f"Match (to:Town)-[:locate]-(c:Company)-[:employ]-(e:Engineer)-[r:{relation}]-(t:Teacher)" \
f"-[:include]-(u:University) where to.id={area_id} and u.id in {university_id} " \
"return count(r) as total".format(relation=relation, area_id=area_id, university_id=university_id)
return neo4j.run(cql)
def getEngineerTeamBasicInfo(e_id):
"""
获取工程师团队的基本信息， 包括 工程师名， 团队人数，企业名
:param e_id: 工程师团队核心节点的 id
:return: [{name, members, org}]
"""
cql = "match (org:{com})-[:employ]-(e:{engineer}) where e.id={id} " \
"return org.name as org, e.member as members, e.name as name, e.team_patent as team_patent" \
.format(com=LABEL["COMPANY"], engineer=LABEL["ENGINEER"], id=e_id)
return neo4j.run(cql)
def getTeamMembers(team_id, teacher=True):
"""
获取团队成员信息，用于展示团队关系图
"""
label = LABEL["TEACHER"] if teacher is True else LABEL["ENGINEER"]
cql = "match (t1:{label})-[r:cooperate]->(t2:{label}) " \
"where t1.team={team_id} and t2.team={team_id} and r.frequency > 2 " \
"and t1.id <> t2.id return t1.id as id1, t1.name as name1, t1.patent as patent1, r.frequency as count, " \
"t2.id as id2, t2.name as name2, t2.patent as patent2 ".format(label=label, team_id=team_id)
return neo4j.run(cql)
def getUserInfo(userid, label=LABEL["areaAGENT"]):
"""
根据 用户id 获取用户信息
:param userid: int or str
:param label: Agent_Area or Agent_University
:return: [{"id": 19036, "name": "清华大学"}, ...] or []
"""
cql = "match (agent:{label})-[:work]-()-[:include]-(org) " \
"where agent.id='{userid}' " \
"return org.id as id, org.name as name".format(label=label, userid=userid)
return neo4j.run(cql)
def getUsefulTowns(userid):
"""
获取可选择区镇
:return: [{"id": 2, "name": "开发区"}]
"""
cql = "match (uniAgent:{uniAgent})-[:partner]-(areaAgent:{areaAgent})-[:work]-()-[:include]-(t:Town) " \
"where uniAgent.id='{userid}' " \
"with distinct(t) as town " \
"return town.id as id, town.name as name" \
.format(areaAgent=LABEL["areaAGENT"], uniAgent=LABEL["uniAGENT"], userid=userid)
return neo4j.run(cql)
def getTechnicalFieldComparison(eid=None, tid=None, team=True):
"""
TODO to be deleted
获取 企业工程师与专家的共同专利
"""
param = "team" if team is True else "id"
cql = "Match (e:Engineer)-[:write]-(ep:Patent)-[:include]-(ipc:IPC)-[:include]-(tp:Patent)-[:write]-(t:Teacher) " \
f"where e.{param}={eid} and t.{param}={tid} " \
"return ep.application_number as e_patent, " \
"tp.application_number as t_patent, ipc.code as ipc".format(param=param, eid=eid, tid=tid)
return neo4j.run(cql)
def getTeamPatentsNumber(team_id, teacher=True):
"""
统计团队中的全部成员的专利数量，将结果写入 团队核心节点中的 team_patent 属性中
:param team_id:
:param teacher:
:return:[{"team_patent": 123}]
"""
label = LABEL["TEACHER"] if teacher is True else LABEL["ENGINEER"]
cql = "Match (l:{label})-[:write]-(p:Patent) where l.team={id} " \
"return count(distinct(p)) as nums".format(label=label, id=team_id)
return neo4j.run(cql)
def fuzzyMatchEngineer(com_id, name):
"""
根据工程师名 & 企业id, 模糊匹配
:param com_id: int
:param name:
:return:
"""
cql = """match (c:{com})-[:employ]-(e:{engineer})""" \
"""where c.id={com_id} and e.name=~".*{name}.*" """ \
"""return e.id as id, e.name as name limit 5""" \
.format(com=LABEL["COMPANY"], engineer=LABEL["ENGINEER"], com_id=com_id, name=name)
return neo4j.run(cql)
def fuzzyMatchTeacher(uni_id, name):
"""
根据教师名， 模糊匹配
:param uni_id: int
:param name:
:return:
"""
cql = """match (u:{uni})-[:include]-(i:{inst})-[:include]-(t:{teacher})""" \
"""where u.id={uni_id} and t.name=~".*{name}.*" """ \
"""return t.id as id, t.name as name, i.name as institution limit 5""" \
.format(uni=LABEL["UNIVERSITY"], inst=LABEL["INSTITUTION"], teacher=LABEL["TEACHER"], uni_id=uni_id, name=name)
return neo4j.run(cql)
def linkEngineerByPartner(agent_id, engineer_id):
"""
通过 地区搭档获取联络路径
:param agent_id: str
:param engineer_id:
:return: [{start, target, partner}] or []
"""
cql = "MATCH (target:Engineer)-[:employ]-(:Company)-[:locate]-(town:Town) " \
"where target.id={engineer_id} with target, town " \
"match (start:Agent_University)-[r:partner]-(partner:Agent_Area)-[:work]-()-[:include]-(t:Town) " \
"where start.id='{agent_id}' and t.id=town.id " \
"return start, partner, target".format(engineer_id=engineer_id, agent_id=agent_id)
return neo4j.run(cql)
def linkTeacherByPartner(agent_id, teacher_id):
"""
通过 地区搭档获取联络路径
:param agent_id: str
:param teacher_id:
:return: [{start, target, partner}] or []
"""
cql = "MATCH (target:Teacher)-[:include]-(:Institution)-[:include]-(u:University) " \
"where target.id={teacher_id} with target, u " \
"match (start:Agent_Area)-[r:partner]-(partner:Agent_University)-[:work]-()-[:include]-(u2:University) " \
"where start.id='{agent_id}' and u2.id=u.id " \
"return start, partner, target".format(teacher_id=teacher_id, agent_id=agent_id)
return neo4j.run(cql)
def recommendInstitutionForCompany(company_id, limit=20):
"""
根据企业id， 为特定企业推荐 高校学院
:param company_id: list [123, ..]
:param limit:
:return: [{town_id, town_name, c_id, c_name, i_id, i_name, u_id, u_name, weight)}]
"""
cql = "match (town:Town)-[:locate]-(c:Company)-[r:{relation}]-(i:Institution)-[:include]-(u:University) " \
"where c.id in {company_id} " \
"return town.id as town_id,town.name as town_name, c.id as c_id, c.name as c_name, " \
"i.id as i_id, i.name as i_name, u.id as u_id, u.name as u_name, r.weight as weight " \
"order by weight desc limit {limit}".format(relation=RELATION["CISM"], company_id=company_id, limit=limit)
return neo4j.run(cql)
def getAgentPartnerRelation(agent_id, agent_type=LABEL["uniAGENT"]):
"""
获取技转中心中介 个人中心网络中的 同事关系
:param agent_id:
:param agent_type: Agent_Area or Agent_University
:return: [{t_id, t_name, org_id, org_name}]
"""
# cql = "match (agent:{agent_label})-[p:partner]-(partner)-[:work]-(org) where agent.id={id} " \
# "return agent.name as s_name, agent.id as s_id, org.id as org_id, org.name as org_name, " \
# "partner.id as t_id,partner.name as t_name".format(agent_label=agent_type, id=agent_id)
cql = "match (agent:{agent_label})-[p:partner]-(partner)-[:work]-(org) where agent.id='{id}' " \
"return partner.id as t_id,partner.name as t_name, org.id as org_id, org.name as org_name" \
.format(agent_label=agent_type, id=agent_id)
return neo4j.run(cql)
def recommendEngineerAndTeacherTeam2(com_id, limit=20):
"""
指定企业 & 专家，推荐 专家和 工程师团队
:param com_id: list [12,34,3]
:param limit:
:return: [{c_id, c_name, e_id, e_name, t_id, t_name, i_id, i_name, u_id, u_name, weight)}]
"""
cql = "match (c:Company)-[:employ]-(e:Engineer)-[r:{relation}]-(t:Teacher)-[:include]-" \
"(i:Institution)-[:include]-(u:University) " \
"where c.id in {company_id} " \
"return c.id as c_id, c.name as c_name, e.id as e_id, e.name as e_name, t.id as t_id, t.name as t_name, " \
"i.id as i_id, i.name as i_name, u.id as u_id, u.name as u_name, r.weight as weight " \
"order by weight desc limit {limit}".format(relation=RELATION["CTT"], company_id=com_id, limit=limit)
return neo4j.run(cql)
def recommendInstitutionAndCompany(town_id, uni_id, limit=20):
"""
根据选定区域的id、 以及选定高校的id, 推荐地区企业 和 高校学院
:param town_id: list [2, ...]
:param uni_id: list [23, ..] or []
:param limit:
:return: [{town_id, town_name, c_id, c_name, i_id, i_name, u_id, u_name, weight)}]
"""
cql = "match (town:Town)-[:locate]-(c:Company)-[r:{relation}]-(i:Institution)-[:include]-(u:University) " \
"where town.id in {town_id} and u.id in {uni_id} " \
"return town.id as town_id,town.name as town_name, c.id as c_id, c.name as c_name, " \
"i.id as i_id, i.name as i_name, u.id as u_id, u.name as u_name, r.weight as weight " \
"order by weight desc limit {limit}".format(relation=RELATION["CISM"], town_id=town_id, uni_id=uni_id, limit=limit)
return neo4j.run(cql)
def recommendUniversityAndCompany(town_id, limit=20):
"""
根据选定区域id，向指定区域推荐 企业 和 高校
:param town_id: list
:param limit:
:return: [{town_id, town_name, c_id, c_name, u_id, u_name, weight}, ...]
# {col0_id, col0_name, col1_id, col1_name, col2_id, col2_name, weight}
"""
cql = "match (town:Town)-[:locate]-(c:Company)-[r:{relation}]-(u:University) " \
"where town.id in {town_id} " \
"return town.id as town_id,town.name as town_name, c.id as c_id, c.name as c_name, " \
"u.id as u_id, u.name as u_name, r.weight as weight " \
"order by weight desc limit {limit}".format(relation=RELATION["CUSM"], town_id=town_id, limit=limit)
return neo4j.run(cql)
def linkTargetByOrg(agent_id, agent_label, target_id, target_label):
"""
通过组织机构获取联络路径
:param agent_id: str
:param agent_label:
:param target_id: int
:param target_label:
:return: [{start, target, org}] or []
"""
cql = "match (start:{agent_label}), (target:{target_label})-[:include|employ]-(org) " \
"where start.id='{agent_id}' and target.id={target_id} " \
"return start, target, org".format(agent_label=agent_label, agent_id=agent_id, target_id=target_id,
target_label=target_label)
return neo4j.run(cql)
def recommendCompanyForTeacher(teacher_id, area_id, skip=0, limit=20):
"""
向 高校老师推荐 企业
:param teacher_id:
:param area_id:
:param skip:
:param limit:
:return: [{t_id, t_name, weight, e_id, e_name, c_id, c_name, town_id, town_name}, ... ]
"""
cql = "Match (t:Teacher)-[r]-(e:Engineer)-[:employ]-(c:Company)-[:locate]-(to:Town) " \
f"where to.id={area_id} and t.id={teacher_id} " \
"return t.id as t_id, t.name as t_name, r.weight as weight, e.id as e_id, e.name as e_name, " \
"c.id as c_id, c.name as c_name, to.id as town_id, to.name as town_name " \
"order by weight asc skip {skip} limit {limit}".format(teacher_id=teacher_id, area_id=area_id,
skip=skip * limit, limit=limit)
return neo4j.run(cql)
def getSimilarPatents(team_teacher,
team_engineer,
teacher=True,
skip=0,
limit=15):
"""
根据 专家团队和工程师团队的 team_id 获取团队间 相似的专利
:return: [] or [{"teacher": Node type data, "engineer": Node type data}]
"""
patent = "pt" if teacher else "pe"
cql = "match (t:Teacher)-[:write]-(pt:Patent)-[:include]-(:IPC)-[:include]-(pe:Patent)-[:write]-(e:Engineer) " \
"where t.team={team_teacher} and e.team={team_engineer} " \
"return distinct({patent}.application_number) as code, {patent}.name as name, " \
"{patent}.application_date as date order by date desc skip {skip} limit {limit}" \
.format(team_teacher=team_teacher, team_engineer=team_engineer, patent=patent, skip=skip, limit=limit)
return neo4j.run(cql)
def recommendTeacherForCompany(company_id, university_id, team=True, skip=0, limit=20):
"""
向 企业推荐适合合作的 高校老师
:param company_id: list : [12, 23, 34, ...] or []
:param university_id: list : [23, ..] or []
:param team: 是否以团队为单位
:param skip:
:param limit: 限制数量
:return: [] or [{c_id, c_name, e_id, e_name, weight, t_id, t_name, u_id, u_name}, ...]
"""
relation = RELATION["CSM"] if team else RELATION.get("PSM")
cql = "match (c:Company)-[:employ]-(e:Engineer)-[r:{relation}]-(t:Teacher)-[:include]-(u:University) " \
"where c.id in {company_id} and u.id in {university_id} and e.id=e.team and t.id=t.team " \
"return c.id as c_id, c.name as c_name, e.id as e_id, e.name as e_name, r.weight as weight, " \
"t.id as t_id, t.name as t_name, u.id as u_id, u.name as u_name " \
"order by weight asc skip {skip} limit {limit}".format(relation=relation, company_id=company_id,
university_id=university_id, skip=skip * limit,
limit=limit)
return neo4j.run(cql)
def recommendTeacherForArea(area_id, university_id, team=True, sort="", skip=0, limit=40):
"""
向 地区 推荐适合与 高校 合作的企业
:param area_id:
:param university_id: list
:param team:
:param sort: 用于排序的其他字段，eg: company.name desc,
:param skip:
:param limit:
:return: [{**town_id, town_name,** c_id, c_name, e_id, e_name, weight, t_id, t_name, u_id, u_name}, ...]
"""
relation = RELATION["CSM"] if team else RELATION.get("PSM")
cql = "Match (to:Town)-[:locate]-(c:Company)-[:employ]-(e:Engineer)-[r:{relation}]-(t:Teacher)" \
"-[:include]-(u:University) where to.id={area_id} and u.id in {university_id} " \
"return c.id as c_id, c.name as c_name, e.id as e_id, e.name as e_name, e.member as e_member, " \
"r.weight as weight, t.name as t_name, t.id as t_id, t.institution as institution, t.member as t_member, " \
"u.name as u_name, u.id as u_id " \
"order by {sort} weight asc skip {skip} limit {limit}" \
.format(relation=relation, area_id=area_id, university_id=university_id, sort=sort, skip=skip * limit,
limit=limit)
return neo4j.run(cql)
def getLinkPath(agent_id,
agent_label,
target_id,
target_label,
step=3,
limit=5):
"""
获取两节点之间的联络路径
:param agent_id: str
:param agent_label:
:param target_id: int
:param target_label:
:param step: 整型，从 源节点到目标节点 的中间结点数量
:param limit: 整型，路径数量
:return: list of Path
"""
cql = "MATCH p=(start:{agent_label})-[r:knows|partner|cooperate|include|employ*..{step}]-(target:{target_label}) " \
"where start.id='{agent_id}' and target.id={target_id} " \
"return p as path limit {limit}" \
.format(agent_label=agent_label, agent_id=agent_id, target_id=target_id, target_label=target_label, step=step,
limit=limit)
return neo4j.run(cql)
def getUniversityList(limit=100):
cql = "match (u:University) where u.teachers > 0 return u.id as id, u.name as name " \
"order by u.teachers desc limit {limit}".format(limit=limit)
return neo4j.run(cql)
