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)