def find1(path1, bianhao): # 动迁系统查询 是否认领完毕 path1动迁照片路径 bianhao系统内总报告编号
df_m = model_excel()
mm2 = sql_server("192.168.1.8", "sa", "ldpjwy", "gjgl_xh", "1433")
r2 = mm2.select("dbo.cq1", {
'gdbh': 13431,
'zl_ne': '',
'zgjs': '王昱鹏'
}, ['bcqrxm'])
df11 = [x['bcqrxm'].replace("（亡）", "").replace("(亡）", "") for x in r2]
# df1=df_m.readexcel("E:\\21.xlsx",0,None,1,"H")
# df1.dropna(axis=0,inplace=True)
# df1[7]=df1[7].apply(lambda x: x.replace("（亡）","").replace("(亡）",""))
# df11=df1.iloc[:,0].tolist()
ls1 = []
bianli2(path1, ls1)
#print(ls1)
t = True
for name in ls1:
for pp in df11:
if name.find(pp) > -1:
t = False
break
if t:
print(name)
else:
t = True
def bijiao_excel(x1, x2):
r = pd.merge(x1, x2, how='left')
r.fillna(value=-99)
r.sort_values(by="nn")
f_excel = model_excel()
n_t = time.strftime("%Y%m%d%H%M%S", time.time())
f_excel.xlwingcreate(r, r"e:\bj_sl" + n_t + r".xlsx")
def model2(self):
ff1 = self.readexcel2()
ff2 = self.search2(['报告编号', '估价人员'])
dftf = pd.merge(ff1, ff2, how='left', left_on=10, right_on='报告编号')
dftf['估价人员'].replace("殷?", "殷旸", inplace=True)
dfff = dftf.groupby(['估价人员'], as_index=False)
ff2 = dfff[12].agg(np.sum)
print(ff2)
pff = dfff.size()
pff = pff.reset_index(name='数量')
ff3 = pd.merge(ff2, pff, how="left")
print(ff3)
xlww = model_excel()
xlww.xlwingwirte(dftf[[10, 12, '估价人员']], self.f, '结果', True, 'A1')
#width =0.5
#plt.rcParams['font.sans-serif'] = ['SimHei']
xlww.xlwingwirte(ff3, self.f, '结果', True, 'A1')
def model(self):
df1 = self.readexcel()
if df1.shape[0] > 10:
df1.drop(df1[df1[0].map(np.isnan)].index)
df2 = self.search(['报告编号', '地址', '客户', '完成日期', '总价', '估价人员'])
df2["地址"] = df2["地址"].apply(lambda x: repl(x))
df2["总价"] = df2["总价"] * 10000
df2["完成日期"] = df2["完成日期"].apply(lambda x: x.strftime("%Y%m%d"))
df2["估价人员"] = df2["估价人员"].apply(lambda x: x.replace("殷?", "殷旸"))
file2 = datetime.now().strftime("%Y-%m-%d,%H-%M-%S")
f1 = self.f.rfind(".")
#f2=self.f.rfind("\\")
st1 = self.f[f1:len(self.f)] #后缀名
#st2=self.f[f2:f1] #文件名
#st3=self.f[0:f2] #文件所在目录
fd2fullname = os.path.join(os.getcwd(), file2 + st1)
df2.to_excel(fd2fullname)
df2['总价'] = df2['总价'].astype(int)
#df3=df1.drop_duplicates(subset=[2],keep='first') #删除重复客户
#df4=df2.drop_duplicates(subset=['客户'],keep='first') #查询到的表删除重复客户
#df4=df4[['报告编号','客户']]
#df5=pd.merge(df3,df4,how='left',left_on=2,right_on='客户')
df7 = df2[['报告编号', '地址']]
df = pd.merge(df1, df7, how='left', left_on=5, right_on='地址')
#df6=df5[[0,'报告编号']]
#df=pd.merge(df,df6,how='left',on=0,)
#df['报告编号_x']=df.apply(lambda x: x['报告编号_x'] if x["报告编号_x"] is not np.nan else x['报告编号_y'],axis=1)
dft = pd.merge(df,
df2,
how='left',
left_on='报告编号',
right_on="报告编号")
dft.drop_duplicates(subset=[0], keep='first', inplace=True)
sna = dft['报告编号'].isna().sum()
print(sna)
xlww = model_excel()
xlww.xlwingwirte(dft[['报告编号', '完成日期', '总价', '估价人员']], self.f, None,
False, 'K5')
return sna
else:
return -1
def run(self):
lists1 = read_excel(self.fp, self.sheetname, self.nc)
sflist = []
mul = Pool(6)
qq = Manager().Queue()
for lsd in lists1:
mul.apply_async(runss, (lsd, qq))
mul.close()
mul.join()
while not qq.empty():
sflist.append(qq.get())
sflist.sort(key=lambda x: x[6])
xlww = model_excel()
df = pd.DataFrame(
sflist, columns=['售价', '开盘时间', '主力面积', '信息', '信息2', '名称', '序号'])
xlww.xlwingwirte(df, self.fp, self.sheetname, True)
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")
#fname2="E:\\"+str(time.strftime("%H%M%S",time.localtime()))+".xls"
#lists1=[["禹洲雍锦府",1]]
# print(lists1)
# test("澜庭")
fname = r"E:\414ww.xlsx"
lists1 = read_excel(fname, 'Sheet1', 3)
print(lists1)
longlis = []
mul = Pool(1)
sflist = []
qq = Manager().Queue()
for lsd in lists1:
mul.apply_async(runss, (lsd, qq))
#sflist.append(searchbegin(lsd))
mul.close()
mul.join()
while not qq.empty():
sflist.append(qq.get())
sflist.sort(key=lambda x: x[6])
print(sflist)
xlww = model_excel()
df = pd.DataFrame(sflist,
columns=['售价', '开盘时间', '主力面积', '信息', '信息2', '名称', '序号'])
#xlww.xlwingwirte(df,fname,'21S1',True,"K2")
print(time.strftime("%H:%M:%S", time.localtime()))
print((time.time() - t) / 60)