def find_company_by_artifact(source_artifact, test=False):
table_names = helper.get_table_names(test)
# author是运营者, apkname是开发者
# logger.info("find_company_by_artifact")
#if source_artifact["type"] != 4040 and source_artifact["type"] != 4050:
if source_artifact["type"] == 4010:
if source_artifact["link"] is not None and source_artifact["type"] != "":
conn = db.connect_torndb()
artifact = conn.get("select a.* from " + table_names["artifact"] + " a join " +
table_names["company"] + " c on c.id=a.companyId " +
"where (c.active is null or c.active !='N') and a.type=%s and a.link=%s limit 1",
source_artifact["type"],
source_artifact["link"])
conn.close()
if artifact is not None:
logger.info("find_company_by_artifact 1, %s, %s", artifact["type"], artifact["link"])
return artifact["companyId"]
if source_artifact["domain"] is not None and source_artifact["domain"] != "":
conn = db.connect_torndb()
artifact = conn.get("select a.* from " + table_names["artifact"] + " a join " +
table_names["company"] + " c on c.id=a.companyId " +
"where (c.active is null or c.active !='N') and a.type=%s and a.domain=%s limit 1",
source_artifact["type"],
source_artifact["domain"])
conn.close()
if artifact is not None:
logger.info("find_company_by_artifact 2, %s, %s", artifact["type"], artifact["domain"])
return artifact["companyId"]
return None
def aggregate_artifact(company_id,source_company_id, test=False):
table_names = helper.get_table_names(test)
# artifact
conn = db.connect_torndb()
sas = list(conn.query("select * from source_artifact where sourceCompanyId=%s", source_company_id))
for sa in sas:
if sa["active"] == "Y" or sa["active"] is None:
if sa["domain"] is not None and sa["domain"].strip() != "":
artifact = conn.get("select * from " + table_names["artifact"] + " where companyId=%s and type=%s and domain=%s limit 1",
company_id, sa["type"], sa["domain"])
else:
artifact = conn.get("select * from " + table_names["artifact"] + " where companyId=%s and type=%s and link=%s limit 1",
company_id, sa["type"], sa["link"])
if artifact is None:
logger.info("type: %s, link: %s, domain: %s", sa["type"], sa["link"], sa["domain"])
artifact_id = aggregator_db_util.insert_artifact(company_id, sa, test)
art = conn.get("select * from artifact where id=%s", artifact_id)
set_artifact_rank.set_artifact_rank(art)
if not test:
conn.update("update artifact set recommend=null where companyId=%s and recommend='Y'", company_id)
artifact = artifact_recommend.get_recommend_artifact(company_id)
if artifact:
conn.update("update artifact set recommend='Y' where id=%s", artifact["id"])
conn.close()
def find_company_by_full_name(full_name, test=False):
logger.info("find_company_by_full_name")
if full_name is None or full_name == "":
return None
full_name = name_helper.company_name_normalize(full_name)
table_names = helper.get_table_names(test)
conn = db.connect_torndb()
company = conn.get("select * from " + table_names["company"] + " where fullName=%s and (active is null or active !='N') limit 1", full_name)
conn.close()
if company is not None:
logger.info("find_company_by_full_name 1")
return company["id"]
conn = db.connect_torndb()
company_alias = conn.get("select a.* from " + table_names["company_alias"] +
" a join " + table_names["company"] + " c on c.id=a.companyId " +
"where (c.active is null or c.active !='N') and a.type=12010 and a.name=%s limit 1",
full_name)
conn.close()
if company_alias is not None:
logger.info("find_company_by_full_name 2")
return company_alias["companyId"]
return None
def get_company_code(name, test=False):
table_names = helper.get_table_names(test)
conn = db.connect_torndb()
if len(name) <8 :
pinyin = lazy_pinyin(name.decode('utf-8'))
company_code = ''.join(pinyin)
else:
pinyin = lazy_pinyin(name.decode('utf-8'), style=pypinyin.INITIALS)
company_code = ''.join(pinyin)
bs = bytes(company_code)
st = ''
for b in bs:
if re.match('-|[0-9a-zA-Z]', b):
st +=b
company_code = st
if len(company_code) >31:
company_code = company_code[0:30]
if len(company_code) < 3:
company_code = company_code+str(random.randint(1, 100))
sql = "select code from " + table_names["company"] + " where code = %s"
result = conn.get(sql, company_code)
while result is not None:
company_code += str(random.randint(1, 100))
result = conn.get(sql, company_code)
if result is None:
break
conn.close()
return company_code
def insert_company(code, source_company, test):
table_names = helper.get_table_names(test)
s = source_company
conn = db.connect_torndb()
sql = "insert " + table_names[
"company"] + "(code,name,fullName,description,brief,\
productDesc, modelDesc, operationDesc, teamDesc, marketDesc, compititorDesc, advantageDesc, planDesc, \
round,roundDesc,companyStatus,fundingType,preMoney,currency,\
locationId,address,phone,establishDate,logo,type,\
headCountMin,headCountMax,\
active,createTime,modifyTime) \
values(%s,%s,%s,%s,%s,\
%s,%s,%s,%s,%s,%s,%s,%s, \
%s,%s,%s,%s,%s,%s,\
%s,%s,%s,%s,%s,%s,\
%s,%s,\
%s,now(),now())"
company_id = conn.insert(
sql, code, s["name"], s["fullName"], s["description"], s["brief"],
s.get("productDesc"), s.get("modelDesc"), s.get("operationDesc"),
s.get("teamDesc"), s.get("marketDesc"), s.get("compititorDesc"),
s.get("advantageDesc"), s.get("planDesc"), s["round"], s["roundDesc"],
s["companyStatus"], s["fundingType"], s["preMoney"], s["currency"],
s["locationId"], s["address"], s["phone"], s["establishDate"],
s["logo"], s["type"], s["headCountMin"], s["headCountMax"], 'Y')
if not test:
conn.update("update source_company set companyId=%s where id=%s",
company_id, s["id"])
conn.close()
return company_id
def insert_company_member_rel(company_id,
member_id,
source_company_member_rel,
test=False):
rel = source_company_member_rel
conn = db.connect_torndb()
table_names = helper.get_table_names(test)
item = conn.get(
"select * from " + table_names["company_member_rel"] +
" where companyId=%s and memberId=%s "
"and (active is null or active='Y' or (active='N' and modifyUser != 139)) "
"limit 1", company_id, member_id)
if item is None:
cmrelId = conn.insert(
"insert " + table_names["company_member_rel"] + "(\
companyId,memberId,position,joinDate,leaveDate,type,\
active,createTime,modifyTime) \
values(%s,%s,%s,%s,%s,%s,'Y',now(),now())", company_id,
member_id, rel["position"], rel["joinDate"], rel["leaveDate"],
rel["type"])
else:
cmrelId = item["id"]
conn.close()
return cmrelId
def insert_artifact(company_id, sa, test=False):
table_names = helper.get_table_names(test)
conn = db.connect_torndb()
sql = "insert " + table_names[
"artifact"] + "(companyId,name,description,link,domain,type,active,createTime,modifyTime) \
values(%s,%s,%s,%s,%s,%s,%s,now(),now())"
artifact_id = conn.insert(sql, company_id, sa["name"], sa["description"],
sa["link"], sa["domain"], sa["type"],
sa["active"])
conn.close()
return artifact_id
def aggregate_one_member(company_id, source_member, test):
table_names = helper.get_table_names(test)
# member_id = source_member["memberId"]
# if member_id is not None:
# return member_id
member_id = None
conn = db.connect_torndb()
#同公司同名识别为同一个人
members = conn.query(
"select m.* from " + table_names["member"] + " m join " +
table_names["company_member_rel"] + " r on m.id=r.memberId " +
"where r.companyId=%s and (r.active is null or r.active='Y')",
company_id)
m = None
for m in members:
logger.info(json.dumps(m, ensure_ascii=False, cls=util.CJsonEncoder))
if m["name"] == source_member["name"]:
member_id = m["id"]
# flag = replace(m, source_member)
# if flag:
# logger.info("update")
# logger.info(json.dumps(m, ensure_ascii=False, cls=util.CJsonEncoder))
# aggregator_db_util.update_member(m)
break
if member_id is None and not test:
member_id = source_member["memberId"]
if member_id is not None:
#merge
if m is not None:
if not test:
flag = replace(m, source_member)
if flag:
logger.info("update")
logger.info(
json.dumps(m,
ensure_ascii=False,
cls=util.CJsonEncoder))
aggregator_db_util.update_member(m)
else:
member_id = aggregator_db_util.insert_member(source_member, test)
if not test:
conn.update("update source_member set memberId=%s where id=%s",
member_id, source_member["id"])
conn.close()
return member_id
def insert_member(source_member, test=False):
table_names = helper.get_table_names(test)
conn = db.connect_torndb()
member_id = conn.insert(
"insert " + table_names["member"] + "( \
name,description,education,work,photo,active,createTime,modifyTime \
) \
values(%s,%s,%s,%s,%s,'Y',now(),now())",
source_member["name"], source_member["description"],
source_member["education"], source_member["work"],
source_member["photo"])
conn.close()
return member_id
def add_company_alias(company_id, source_company_id, test=False):
table_names = helper.get_table_names(test)
conn = db.connect_torndb()
#source_company = conn.get("select * from source_company where id=%s", source_company_id)
source_company_names = conn.query("select * from source_company_name where sourceCompanyId=%s", source_company_id)
for s in source_company_names:
if s["name"] is None or s["name"].strip() == "":
continue
name = s["name"].strip()
alias = conn.get("select * from " + table_names["company_alias"] + " where companyId=%s and name=%s limit 1",
company_id, name)
if alias is None:
sql = "insert " + table_names["company_alias"] + "(companyId,name,type,active,createTime) \
values(%s,%s,%s,%s,now())"
conn.insert(sql, company_id, name, s["type"], 'Y')
conn.close()
def insert_company_new(code, source_company, test):
table_names = helper.get_table_names(test)
s = source_company
conn = db.connect_torndb()
sql = "insert " + table_names["company"] + "(code,name,description,brief,\
companyStatus,logo,active,createTime,modifyTime) \
values(%s,%s,%s,%s,\
%s,%s,%s,\
now(),now())"
company_id = conn.insert(sql, code, s["name"], s["description"],
s["brief"], s["companyStatus"], s["logo"], 'A')
if not test:
conn.update("update source_company set companyId=%s where id=%s",
company_id, s["id"])
conn.close()
return company_id
def aggregate_one_member(company_id, source_member, test):
table_names = helper.get_table_names(test)
# member_id = source_member["memberId"]
# if member_id is not None:
# return member_id
member_id = None
conn = db.connect_torndb()
#同公司同名识别为同一个人
members = conn.query(
"select m.* from " + table_names["member"] + " m join " +
table_names["company_member_rel"] + " r on m.id=r.memberId " +
"where r.companyId=%s", company_id)
m = None
for m in members:
if m["name"] == source_member["name"]:
member_id = m["id"]
break
if member_id is None and not test:
member_id = source_member["memberId"]
if member_id is not None:
#merge
if m is not None and m["verify"] != 'Y':
if not test:
flag = replace(m, source_member)
if flag:
aggregator_db_util.update_member(m)
else:
member_id = aggregator_db_util.insert_member(source_member, test)
if not test:
conn.update("update source_member set memberId=%s where id=%s",
member_id, source_member["id"])
conn.close()
return member_id
def find_company_by_short_name(source_company, test=False):
#产品名相同,则判断
#1. 地区相同
#2. 成立日期相同
#3. member有相同
#4. 融资事件
table_names = helper.get_table_names(test)
logger.info("find_company_by_short_name")
matched_company_id = None
conn = db.connect_torndb()
source_members = list(conn.query("select m.* from source_company_member_rel r join source_member m on m.id=r.sourceMemberId where r.sourceCompanyId=%s", source_company["id"]))
source_investor_ids = {}
source_fundings = list(conn.query("select * from source_funding where sourceCompanyId=%s",source_company["id"]))
for sf in source_fundings:
rels = list(conn.query("select * from source_funding_investor_rel where sourceFundingId=%s",sf["id"]))
for rel in rels:
source_investor = conn.get("select * from source_investor where id=%s", rel["sourceInvestorId"])
if source_investor["investorId"] is not None:
source_investor_ids[source_investor["investorId"]] = 1
short_names = list(conn.query("select * from source_company_name where type=12020 and sourceCompanyId=%s", source_company["id"]))
sns = []
for s in short_names:
sns.append(s["name"])
if source_company["name"] not in sns:
sns.append(source_company["name"])
for short_name in sns:
if short_name is None or short_name.strip() == "":
continue
short_name = short_name.strip()
logger.info("short_name: %s", short_name)
candidate_company_ids = []
cs = list(conn.query("select * from " + table_names["company"] +
" where name=%s and (active is null or active !='N')", short_name))
for c in cs:
company_id = c["id"]
candidate_company_ids.append(company_id)
aliases = list(conn.query("select a.companyId from " + table_names["company_alias"] +
" a join " + table_names["company"] + " c on c.id=a.companyId " +
"where (c.active is null or c.active!='N') and a.name=%s",short_name))
for alias in aliases:
company_id = alias["companyId"]
candidate_company_ids.append(company_id)
for company_id in candidate_company_ids:
company = conn.get("select * from " + table_names["company"] + " where id=%s and (active is null or active='Y')", company_id)
if company is None:
continue
#地区
location1 = source_company["locationId"]
location2 = company["locationId"]
if location1 > 0 and location1==location2:
matched_company_id = company_id
logger.info("find_company_by_short_name, location")
break
#成立日期
date1 = source_company["establishDate"]
date2 = company["establishDate"]
if date1 is not None and date2 is not None and \
date1.year==date2.year and date1.month==date2.month:
matched_company_id = company_id
logger.info("find_company_by_short_name, establish date")
break
#member
members = list(conn.query("select m.* from " + table_names["company_member_rel"] +
" r join " + table_names["member"] + " m on m.id=r.memberId where r.companyId=%s", company_id))
for member in members:
member_name = member["name"]
logger.info("member_name: %s", member_name)
if member_name is None or member_name == "":
continue
if not hz.is_chinese_string(member_name):
continue
for source_member in source_members:
#logger.info("source_member_name: %s", source_member["name"])
if member_name == source_member["name"]:
matched_company_id = company_id
logger.info("find_company_by_short_name, member")
break
if matched_company_id is not None:
break
if matched_company_id is not None:
break
# gongshang member
# TODO
#funding
fundings = list(conn.query("select * from " + table_names["funding"] + " where companyId=%s",company_id))
for f in fundings:
rels = list(conn.query("select * from " + table_names["funding_investor_rel"] + " where fundingId=%s",f["id"]))
for rel in rels:
if source_investor_ids.has_key(rel["investorId"]):
matched_company_id = company_id
logger.info("find_company_by_short_name, funding")
break
if matched_company_id is not None:
break
if matched_company_id is not None:
break
if matched_company_id is not None:
break
conn.close()
return matched_company_id
def aggregate2(str_id, company_id, corporate_id, sinvestorId, test=False):
if check_investor(int(sinvestorId), str_id) is False:
logger.info("nnnno investor")
return
table_names = helper.get_table_names(test)
mongo = db.connect_mongo()
conn = db.connect_torndb()
collection_dealLog = mongo.raw.qmp_tz_parser
item = collection_dealLog.find_one({"_id": ObjectId(str_id)})
sf = parseFinance(item)
f = conn.get(
"select * from " + table_names["funding"] +
" where corporateId=%s and round=%s and (active is null or active!='N') limit 1",
corporate_id, sf["round"])
if f is not None:
logger.info("corporate has this round already")
if f is None:
logger.info("insert")
sql = "insert " + table_names[
"funding"] + "(companyId,preMoney,postMoney,investment,\
round,roundDesc,currency,precise,fundingDate,fundingType,\
active,createTime,modifyTime,createUser,corporateId) \
values(%s,%s,%s,%s, %s,%s,%s,%s,%s,%s,'Y',now(),now(),%s,%s)"
fundingId = conn.insert(sql, company_id, sf["preMoney"],
sf["postMoney"], sf["investment"], sf["round"],
sf["roundDesc"], sf["currency"], sf["precise"],
sf["fundingDate"], 8030, -557, corporate_id)
else:
fundingId = f["id"]
if f:
pass
else:
investors = item["tzr"]
STR = []
InvestorIds = []
if check_investor(int(sinvestorId), str_id) is False:
pass
else:
for invs in investors.replace(",", "，").replace("、",
"，").split("，"):
invraw0 = invs
invraw = invs.strip().replace("领投", "").replace(
"跟投",
"").replace("参投",
"").replace("等",
"").replace(r'\n',
"").replace("\"", "")
if invraw.find("(") >= 0 or invraw.find("（") >= 0:
# logger.info("here")
invraw = invraw.replace("(", "（").split("（")[0]
investor_id = None
investor_id_name = None
investor = conn.get(
"select i.id, i.name from investor_alias ia join investor i on "
"ia.investorId=i.id where ia.name=%s and "
"(i.active is null or i.active='Y') and "
"(ia.active is null or ia.active='Y') limit 1", invraw)
if investor is None:
pass
else:
# logger.info(investor)
investor_id = investor["id"]
investor_id_name = investor["name"]
InvestorIds.append(int(investor_id))
if investor_id is None:
if len(STR) > 0:
STR.append({"text": "、", "type": "text"})
STR.append({"text": invraw0, "type": "text"})
else:
if len(STR) > 0:
STR.append({"text": "、", "type": "text"})
STR.append({
"text": invraw0,
"type": "investor",
"id": int(investor_id)
})
funding_investor_rel = conn.get(
"select * from " +
table_names["funding_investor_rel"] + " \
where investorId=%s and fundingId=%s limit 1",
investor_id, fundingId)
if funding_investor_rel is None:
sql = "insert " + table_names[
"funding_investor_rel"] + "(fundingId, investorType, investorId, companyId, currency, investment,\
precise,active,createTime,modifyTime,createUser) \
values(%s,%s,%s,%s,%s,%s,%s,'Y',now(),now(),%s)"
conn.insert(sql, fundingId, 38001, investor_id, None,
None, None, None, -2)
if len(STR) > 0:
investorRaw = "".join([si['text'] for si in STR])
logger.info(
"update raw-investors %s",
json.dumps(STR, ensure_ascii=False, cls=util.CJsonEncoder))
if len(STR) > 0:
conn.update(
"update funding set investors=%s, investorsRaw=%s where id=%s",
json.dumps(STR,
ensure_ascii=False,
cls=util.CJsonEncoder), investorRaw,
fundingId)
else:
conn.update(
"update funding set investors=%s, investorsRaw=%s where id=%s",
"", investorRaw, fundingId)
# update company stage
if not test:
funding = conn.get(
"select * from funding where corporateId=%s order by round desc, fundingDate desc limit 1",
corporate_id)
if funding is not None:
conn.update(
"update company set round=%s, roundDesc=%s where id=%s",
funding["round"], funding["roundDesc"], company_id)
conn.close()
def aggregate1(sf, company_id, corporate_id, test=False):
flag = True
table_names = helper.get_table_names(test)
conn = db.connect_torndb()
sfirs = conn.query(
"select * from source_funding_investor_rel where sourceFundingId=%s",
sf["id"])
# if sf["investment"] == 0 and len(sfirs)==0:
# conn.close()
# return True
f = conn.get(
"select * from " + table_names["funding"] +
" where companyId=%s and round=%s and (active is null or active!='N') limit 1",
company_id, sf["round"])
if f is not None:
logger.info("find here1")
if f is None and sf["fundingDate"] is not None and sf["round"] <= 1020:
'''
f = conn.get("select * from " + table_names["funding"] + " where companyId=%s and year(fundingDate)=%s and month(fundingDate)=%s and (active is null or active!='N') limit 1",
company_id, sf["fundingDate"].year, sf["fundingDate"].month)
'''
f = conn.get(
"select * from funding where companyId=%s and fundingDate>date_sub(%s,interval 1 month) and fundingDate<date_add(%s,interval 1 month) and (active is null or active!='N') limit 1",
company_id, sf["fundingDate"], sf["fundingDate"])
if f is not None:
logger.info("find here2")
if f is None:
logger.info("insert")
sql = "insert " + table_names[
"funding"] + "(companyId,preMoney,postMoney,investment,\
round,roundDesc,currency,precise,fundingDate,fundingType,\
active,createTime,modifyTime,createUser,corporateId) \
values(%s,%s,%s,%s, %s,%s,%s,%s,%s,%s,'Y',now(),now(),%s,%s)"
fundingId = conn.insert(sql, company_id, sf["preMoney"],
sf["postMoney"], sf["investment"], sf["round"],
sf["roundDesc"], sf["currency"], sf["precise"],
sf["fundingDate"], 8030, -544, corporate_id)
else:
fundingId = f["id"]
# if f["round"] == 1110 and sf["round"] == 1105:
# conn.update("update " + table_names["funding"] + " set round=1105 where id=%s",fundingId)
if f and f["createUser"] is not None:
pass
else:
STR = []
for sfir in sfirs:
investor_id = None
investor_company_id = None
if sfir["investorType"] == 38001:
source_investor = conn.get(
"select * from source_investor where id=%s",
sfir["sourceInvestorId"])
if source_investor is None:
flag = False
continue
investor_id = source_investor["investorId"]
if investor_id is None:
# flag = False
if len(STR) > 0:
STR.append({"text": "、", "type": "text"})
STR.append({
"text": source_investor["name"],
"type": "text"
})
continue
else:
investor = conn.get("select * from investor where id=%s",
investor_id)
if investor is None or investor["active"] == 'N':
# flag = False
if len(STR) > 0:
STR.append({"text": "、", "type": "text"})
STR.append({
"text": source_investor["name"],
"type": "text"
})
continue
else:
if len(STR) > 0:
STR.append({"text": "、", "type": "text"})
STR.append({
"text": investor["name"],
"type": "investor",
"id": int(investor_id)
})
else:
source_company = conn.get(
"select * from source_company where id=%s",
sfir["sourceCompanyId"])
if source_company is None or source_company[
"companyId"] is None:
flag = False
continue
investor_company_id = source_company["companyId"]
if sfir["investorType"] == 38001:
funding_investor_rel = conn.get(
"select * from " + table_names["funding_investor_rel"] +
" \
where investorId=%s and fundingId=%s limit 1",
investor_id, fundingId)
else:
funding_investor_rel = conn.get(
"select * from " + table_names["funding_investor_rel"] +
" \
where companyId=%s and fundingId=%s limit 1",
investor_company_id, fundingId)
if funding_investor_rel is None:
sql = "insert " + table_names[
"funding_investor_rel"] + "(fundingId, investorType, investorId, companyId, currency, investment,\
precise,active,createTime,modifyTime,createUser) \
values(%s,%s,%s,%s,%s,%s,%s,'Y',now(),now(),%s)"
conn.insert(sql, fundingId, sfir["investorType"], investor_id,
investor_company_id, sfir["currency"],
sfir["investment"], sfir["precise"], -2)
if len(STR) >= 0:
investorRaw = "".join([si['text'] for si in STR])
logger.info(
"update raw-investors %s",
json.dumps(STR, ensure_ascii=False, cls=util.CJsonEncoder))
if len(STR) > 0:
conn.update(
"update funding set investors=%s, investorsRaw=%s where id=%s",
json.dumps(STR, ensure_ascii=False, cls=util.CJsonEncoder),
investorRaw, fundingId)
else:
conn.update(
"update funding set investors=%s, investorsRaw=%s where id=%s",
"", investorRaw, fundingId)
# update company stage
if not test:
funding = conn.get(
"select * from funding where companyId=%s order by round desc, fundingDate desc limit 1",
company_id)
if funding is not None:
conn.update(
"update company set round=%s, roundDesc=%s where id=%s",
funding["round"], funding["roundDesc"], company_id)
conn.close()
return flag
def aggregate1(sf,source_company,company_id, test=False):
flag = True
table_names = helper.get_table_names(test)
conn = db.connect_torndb()
sfirs = conn.query("select * from source_funding_investor_rel where sourceFundingId=%s", sf["id"])
if sf["investment"] == 0 and len(sfirs)==0:
conn.close()
return True
f = conn.get("select * from " + table_names["funding"] + " where companyId=%s and round=%s and (active is null or active!='N') limit 1",
company_id, sf["round"])
if f is None and sf["fundingDate"] is not None and sf["round"]<=1020:
'''
f = conn.get("select * from " + table_names["funding"] + " where companyId=%s and year(fundingDate)=%s and month(fundingDate)=%s and (active is null or active!='N') limit 1",
company_id, sf["fundingDate"].year, sf["fundingDate"].month)
'''
f = conn.get("select * from funding where companyId=%s and fundingDate>date_sub(%s,interval 1 month) and fundingDate<date_add(%s,interval 1 month) and (active is null or active!='N') limit 1",
company_id, sf["fundingDate"], sf["fundingDate"])
if f is None:
sql = "insert " + table_names["funding"] + "(companyId,preMoney,postMoney,investment,\
round,roundDesc,currency,precise,fundingDate,fundingType,\
active,createTime,modifyTime) \
values(%s,%s,%s,%s, %s,%s,%s,%s,%s,%s,'Y',now(),now())"
fundingId=conn.insert(sql,
company_id,
sf["preMoney"],
sf["postMoney"],
sf["investment"],
sf["round"],
sf["roundDesc"],
sf["currency"],
sf["precise"],
sf["fundingDate"],
8030
)
else:
fundingId = f["id"]
if f["round"] == 1110 and sf["round"] == 1105:
conn.update("update " + table_names["funding"] + " set round=1105 where id=%s",fundingId)
logger.info("fundingId: %s", fundingId)
for sfir in sfirs:
investor_id = None
investor_company_id = None
if sfir["investorType"] == 38001:
source_investor = conn.get("select * from source_investor where id=%s", sfir["sourceInvestorId"])
if source_investor is None:
flag = False
continue
investor_id = source_investor["investorId"]
if investor_id is None:
flag = False
continue
investor = conn.get("select * from investor where id=%s", investor_id)
if investor is None or investor["active"] == 'N':
flag = False
continue
else:
source_company = conn.get("select * from source_company where id=%s", sfir["sourceCompanyId"])
if source_company is None or source_company["companyId"] is None:
flag = False
continue
investor_company_id = source_company["companyId"]
if sfir["investorType"] == 38001:
funding_investor_rel = conn.get("select * from " + table_names["funding_investor_rel"] + " \
where investorId=%s and fundingId=%s limit 1",
investor_id, fundingId)
else:
funding_investor_rel = conn.get("select * from " + table_names["funding_investor_rel"] + " \
where companyId=%s and fundingId=%s limit 1",
investor_company_id, fundingId)
if funding_investor_rel is None:
sql = "insert " + table_names["funding_investor_rel"] + "(fundingId, investorType, investorId, companyId, currency, investment,\
precise,active,createTime,modifyTime) \
values(%s,%s,%s,%s,%s,%s,%s,'Y',now(),now())"
conn.insert(sql,
fundingId,
sfir["investorType"],
investor_id,
investor_company_id,
sfir["currency"],
sfir["investment"],
sfir["precise"]
)
# update company stage
if not test:
funding = conn.get("select * from funding where companyId=%s order by round desc, fundingDate desc limit 1",
company_id)
if funding is not None:
conn.update("update company set round=%s, roundDesc=%s where id=%s",
funding["round"],funding["roundDesc"],company_id)
conn.close()
return flag
