def getlistl(dataname,us,pwd,host1,ch,mu,tablename,list_l,searchlist):
mysqlm=mysql_model(dataname,us,pwd,host1,ch)
mresult=mysqlm.selectmul(mu,tablename,list_l,searchlist)
df=pd.DataFrame(mresult,columns=searchlist)
return df
def search2(self, columnsl):
smo = mysql_model(self.d, self.u, self.pw, self.h, "gbk")
lsr = smo.select(self.w, self.t, self.ts, {'fd10': [self.t4, self.t1]})
p = pd.DataFrame(lsr, columns=columnsl)
return p
def conn(self):
cn = mysql_model(
self.d, "user", "7940", "192.168.1.3"
) #",user","7940","192.168.1.3" "root","111","localhost"
redata = self.readexcel()
listp1 = [x[self.wh[0]] for x in redata]
resp = []
inlist = []
inlist.append(listp1) #in 搜索 当中的数值
df1 = pd.DataFrame(redata, columns=self.wh)
# for flp in redata:
# pp=cn.select({self.wh[0]:flp[self.wh[0]]},"reports",self.wh)
# print(pp)
# resp.append(pp)
filenew = os.path.join(
os.getcwd(),
datetime.now().strftime("%Y%m%d-%H%M%S") + ".xlsx")
print(inlist)
resp = cn.selectmul([self.wh[0]], 'reports', inlist, self.wh)
df2 = pd.DataFrame(
resp,
columns=['fd1', 'fd33_2', 'fd34_2', 'ck_2', 'fd35_2', 'fd36_2'])
df2['fd33_2'].fillna(0, inplace=True)
df2['ck_2'].fillna("-1", inplace=True)
df3 = df2.loc[(df2["fd33_2"] > 0) | ((df2["ck_2"] != "-1") &
(df2["ck_2"] != ""))]
df3.to_excel(filenew)
flienew2 = os.path.join(
os.getcwd(),
datetime.now().strftime("%Y%m%d-%H%M%S") + ".xlsx")
df2.to_excel(flienew2)
#df2['count']=df2['fd33_2'].apply(lambda x: 1 if x>0 else 0)
#if df2['count'].sum() <3 :
#df2.fillna("",inplace=True)
df = pd.merge(df1, df2, how='left', on='fd1')
df.drop(df[(df["fd33_2"] > 0) | ((df["ck_2"] != "-1") &
(df["ck_2"] != ""))].index,
inplace=True)
print(df.shape[0])
#df['fd33']=df['fd33']+df['fd33_2']
#df['fd34']=df['fd34']+df['fd34_2']
#df['checkno']=df['checkno']+df['ck_2']
#resultr=df[self.wh].to_dict('records')
resultr2 = df[self.wh[1:]].to_dict('records')
searhf1 = df[[self.wh[0]]]
searhf = searhf1.to_dict(
'records') #[ {self.wh[0]:x[self.wh[0]]} for x in resultr]
cn.update(resultr2, searhf, 'reports')
return True
def msqlinset(self):
t, df1 = self.readandcheck()
if t:
mconn = mysql_model(dataname=self.d,
us=self.u,
host1=self.h,
pwd=self.pwd,
ch="gbk")
df_ins = np.array(df1).tolist()
mconn.manyinsert(self.l, df_ins, self.t)
def select_mysql_to_excel(database, user, pwd, host, tablename, where,
search_list, between):
smo = mysql_model(database, user, pwd, host, "gbk")
lsr = smo.select(where, tablename, search_list, between, False)
df1 = pd.DataFrame(lsr)
df1.columns = [
'id', '报告类型', '项目编号', '房地产座落', '委托单位', '借款方', '不动产权利人', '委托方联系人',
'估价时点', '完成日期', '估价目的', '估价设定用途', '建筑面积', '房地产总价', '房地产单价', '估价人员',
'协办人员', '开票金额', '开票日期', '到帐日期', '付款单位', '项目来源(总部)', '项目来源(分部)',
'项目来源联系人', '项目进度', '项目状态', '备注', '暗号', '委托号', '收据金额', '内评价格'
]
df = df1[(df1['报告类型'] == 'X') | (df1['报告类型'] == 'F') | (df1['报告类型'] == 'T')
| (df1['报告类型'] == 'Z')]
df = df[~df['项目编号'].str.contains('临')]
ex = model_excel()
n_t = time.strftime("%Y%m%d%H%M%S")
df['建筑面积'] = df['建筑面积'].astype("float64")
df['房地产总价'] = df['房地产总价'].astype("int64")
print(df.info())
ex.xlwingcreate(df, r"e:\工行" + n_t + r".xlsx")
def mysql_shen_qian(n1, n2, lst):
t = mysql_model("im2006",
'user',
pwd="7940",
host1="192.168.1.3",
ch="utf8")
#t=mysql_model("im2006")
f = ["fd1"]
#fd1=[["X2021-00300","X2021-00601"]]
fd1 = lst
select_list = [
'id', 'fd1', 'fd29', 'fd41', 'fd27', 'fd28', 'auditlevel', 'fd10',
'fd25', 'signature', 'fd37'
]
result = t.selectmul(f, "reports", fd1, select_list, True)
g_ls = []
where_ls = []
print(result)
for r1 in result:
where_1 = {}
if not r1['auditlevel']:
r1['auditlevel'] = 'B'
if r1["fd41"] == '待归档':
pass
else:
r1["fd41"] = '待归档'
if r1["fd28"]:
pass
else:
r1["fd28"] = r1["fd10"].strftime("%Y-%m-%d")
if r1['fd29']:
pass
else:
if r1['fd37']:
if r1['fd37'].find("银行") > -1:
r1['fd29'] = '陈伟林'
else:
r1['fd29'] = '齐刚'
else:
r1['fd29'] = '陈伟林'
if r1["fd27"]:
pass
else:
if r1['fd25'] == n1 or r1['fd25'] == n2:
r1['fd27'] = r1['fd25']
elif r1['signature'] == n1 or r1['signature'] == n2:
r1['fd27'] = r1['signature']
else:
if r1['fd1'][0:1] == "G":
r1['fd27'] = '齐刚'
else:
r1['fd27'] = '陈伟林'
r1.pop("signature")
r1.pop("fd25")
r1.pop("fd10")
r1.pop("fd37")
where_1["id"] = r1.pop("id")
g_ls.append(r1)
where_ls.append(where_1)
print(g_ls)
t.update(g_ls, where_ls, "reports")
def inser_or_update_mysql(self, list_ms, tablename):
mysql = mysql_model(self.mysql_d, self.mysql_u, self.mysql_p,
self.mysql_h)
whe = []
searchwhere = []
for ms_l in list_ms:
whe_dict = {}
search = {}
if ms_l['ybgxmbh']: #有报告编号
if ms_l['ybgxmbh'].find("XX") < 0:
if ms_l['ybgxmbh'].find("G") > -1: #是个贷报告
whe_dict['fd1'] = ms_l['ybgxmbh'][7:18]
whe_dict['person'] = "朱留青"
whe_dict['depperson'] = "柴书钦"
whe_dict['distrperson'] = "朱留青"
if ms_l['pgmd'] == '房地产抵押估价':
whe_dict['fd11'] = '抵押价值评估'
whe_dict['category'] = ms_l['ybgxmbh'][0]
search["fd1"] = whe_dict['fd1']
if str(ms_l['xq']).find("崇明") > -1:
whe_dict['district'] = "崇明县"
else:
whe_dict['district'] = ms_l['xq']
whe_dict['fd3'] = ms_l['xmmc']
whe_dict['buildingname'] = ms_l['lpmc']
whe_dict['fd4'] = ms_l['wtr']
whe_dict['fd7'] = ms_l['zdkr']
whe_dict['startdate'] = ms_l['qprq']
whe_dict['carryondate'] = ms_l['qprq']
whe_dict['distrdate'] = ms_l['qprq']
whe_dict['fd9'] = ms_l['pgsd']
whe_dict['fd10'] = ms_l['jsrq']
ls = str(ms_l['pgff']).split(',')
whe_dict['fd12'] = " ".join(ls)
if ms_l['bgnr'] == '居住' or ms_l['bgnr'] == '住宅':
whe_dict['fd13'] = '住宅'
whe_dict['fd14'] = '住宅'
whe_dict['fd15'] = '住宅'
if ms_l['qsxz'].find("集体") > -1:
whe_dict['landfeature'] = "集体"
else:
whe_dict['landfeature'] = "国有"
whe_dict['fd16'] = ms_l['tdqbfs']
if ms_l['jzmj']: #建筑面积
whe_dict['fd18'] = ms_l['jzmj']
if ms_l['fczjz']: #总价
whe_dict['fd20'] = int(ms_l['fczjz'])
if ms_l['fdc_dj']: #单价
whe_dict['fd21'] = int(ms_l['fdc_dj'])
if ms_l['qzgjs']: #签字估价师
ls2 = str(ms_l['qzgjs']).split(',')
whe_dict['fd25'] = ls2[0]
whe_dict['signature'] = ls2[-1:][0]
whe_dict['fd26'] = ms_l['zgjs']
if ms_l['wtr']: #来源
if ms_l['wtr'].find('工商'):
whe_dict['fd37'] = "中国工商银行"
if ms_l['xmly']: #支行
sp = ms_l['xmly'].replace("支行", "")
if sp == "漕河泾开发区":
whe_dict["fd38"] = "漕河泾"
elif sp == "浦东开发区":
whe_dict["fd38"] = "浦开发"
else:
whe_dict["fd38"] = sp
whe_dict['fd39'] = ms_l['lxry']
whe_dict['fd41'] = "待审核"
fd31 = 0.5
if whe_dict["district"] == "崇明县" or whe_dict[
"district"] == "外省市":
fd31 += 2
elif whe_dict["district"] == "金山区":
fd31 += 1
elif whe_dict["district"] == "南汇区" or whe_dict[
"district"] == "奉贤区" or whe_dict[
"district"] == "嘉定区" or whe_dict[
"district"] == "松江区" or whe_dict[
"district"] == "青浦区":
fd31 += 0.5
whe_dict['fd31'] = fd31
fd31b = 0.5
if whe_dict['category'] == "F" or whe_dict[
'category'] == "X":
if whe_dict['fd11'] != "财产鉴证" and whe_dict[
'fd11'] != "赠与、继承" and whe_dict[
'fd11'] != '房地产课税评估':
fd31b += 1
elif whe_dict['category'] == "T":
fd31b += 2
if whe_dict['fd37'] == '法院':
fd31b += 2
elif whe_dict['fd37'] != "中国工商银行":
fd31b += 0.5
if whe_dict['category'] == "F" or whe_dict[
'category'] == "X" or whe_dict['category'] == "T":
if whe_dict['fd20'] > 10000:
fd31b += 1
elif whe_dict['fd20'] > 1000:
fd31b += 0.5
whe_dict['fd31b'] = fd31b
searchwhere.append(search)
whe.append(whe_dict)
if whe:
mysql.updateorinsertmany(whe, searchwhere, tablename)