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 dui_bi():
mm2 = sql_server("192.168.1.8", "sa", "ldpjwy", "gjgl_xh", "1433")
r2 = mm2.select("dbo.cq1", {
'gdbh': 13431,
'zl_ne': '',
'zgjs': ''
}, ['bcqrxm', 'zl'])
df11 = [
x['bcqrxm'].replace("（亡）", "").replace("(亡）", "").replace("等", "")
for x in r2
]
b2 = []
bianli2(
r"\\192.168.1.5\动迁资料\黄浦征收项目\2021黄浦区厦门路地块（110、111、133、134街坊）旧城区改建项目\照片\110、111、134（四所）照片",
b2)
print(df11)
for ls in df11:
for ddf11 in b2:
if ddf11.find(ls) > -1:
print(ddf11)
break
def select(t={'gdbh': 13431, 'hnbh': 1}, list1=['bgbh', 'qz', 'fh']):
mm2 = sql_server("192.168.1.8", "sa", "ldpjwy", "gjgl_xh", "1433")
r2 = mm2.select("dbo.cq1", t, list1)
xls1 = pd.DataFrame(r2)
return xls1
def mssql_select(self):
ms_sqll = sql_server(self.ms_host, self.ms_user, self.ms_pwd,
self.ms_dataname, self.ms_port)
return ms_sqll.select(self.ms_table, self.selectwhere, self.ms_list)
def upload(fpath, applyNon, applyno_file_path, lose_file=[]): #applyNo 是List
sql = sql_server("192.168.1.8", "sa", "ldpjwy", "gjgl_xh", "1433")
sql2 = sql_server("192.168.1.8", "sa", "ldpjwy", "icbc_api", "1433")
for ia, applyNo in enumerate(applyNon):
result2 = sql2.select(["dbo.icbcapi_assessment"],
{"reportId": applyNo}, None) #先去上传的查看是否上传过
result = sql.select(["dbo.bdgl"], {"ybgxmbh": applyNo}, [
'zgjs', 'zbgh', 'zl', 'jzmj', 'fdc_dj', 'fczjz', 'zcs', 'szc',
'fwlx', 'ybgxmbh'
])
mulupdata = []
fp = []
inset = [] #insert1 表示要插入 update1表示更新
if result:
insertt = True
if result2:
if result['zgjs'] and result['zbgh'] and result[
'jzmj'] and result['fdc_dj'] and result[
'fczjz'] and result['fwlx'] and result['ybgxmbh']:
result2["emplName"] = result["zgjs"]
mulupdata.append(result2)
fp.append(applyno_file_path[ia])
inset.append("update1")
insertt = False
else:
insertt = False
lose_file.append(applyno_file_path[ia])
#del sql
#del sql2
if insertt:
if result['zgjs'] and result['zbgh'] and result[
'jzmj'] and result['fdc_dj'] and result[
'fczjz'] and result['fwlx'] and result[
'ybgxmbh']:
updata = {}
updata["corpId"] = "ASS00113"
updata["emplName"] = result['zgjs']
updata["applyNo"] = result['zbgh']
updata['assessAddress'] = result['zl']
updata['assessArea'] = float(result['jzmj'])
updata['assessUnitPrice'] = int(result['fdc_dj'])
updata['assessTotalPrice'] = int(
result['fczjz']) * 10000
updata['totalFloor'] = int(
result['zcs']) if result['zcs'] else 0
updata['floor'] = int(
result['szc']) if result['szc'] else 0
updata['orientation'] = '朝南'
updata['houseType'] = result['fwlx']
updata['schoolHouse'] = "0"
updata['bronzeMedal'] = "0"
updata['assessStatus'] = "6"
updata['assessNo'] = result['ybgxmbh']
#updata['fileUnifiedNo']=fileuid
updata['remarks'] = ""
mulupdata.append(updata)
#ups=updata.copy()
#ups.pop('emplName')
#ups.pop('remarks')
#ups['fileUnifiedNo']=fileuid
fp.append(applyno_file_path[ia])
inset.append("insert1")
else:
lose_file.append(applyno_file_path[ia])
else:
lose_file.append(applyno_file_path[ia])
print(mulupdata)
print(fp)
print(inset)
if mulupdata:
for ii, lsd in enumerate(mulupdata):
muldup(lsd, fp[ii], inset[ii])
print("---------------完成-------------")
def muldup(
updata,
fpath,
insertup,
):
if 'reportId' in list(updata.keys()):
updata.pop("reportId")
if 'flag' in list(updata.keys()):
updata.pop("flag")
s = setline()
chunkr = 512
s.setshappid("shapp.housepledge.xinheng")
getnameip = str(getip()).split(",")
s.setip(getnameip[0])
s.setname(getnameip[1])
ss = setaes(s)
fsize = os.path.getsize(fpath)
chunkcount = math.ceil(fsize / chunkr)
i = 0
s.seturl("/shlocal/might/magic/encrypt/upload/manual/")
with open(fpath, 'rb') as fr: #读取文件开始上传报告
upl = {}
upl['corpId'] = "ASS00113"
upl['empIName'] = updata["emplName"]
upl['fileName'] = fpath[fpath.rfind("\\") + 1:]
upl['chunks'] = chunkcount
while i < chunkcount:
fr.seek(i * chunkr)
m = base64.b64encode(fr.read(chunkr))
upl['chunk'] = i
upl['fileContent'] = m.decode()
s.setmsg(json.dumps(upl, ensure_ascii=False))
uuid2 = uuid.uuid1()
r2 = ss.dopost(uuid2)
print(r2)
if r2[0] == '0':
if i == 0:
updata['fileUnifiedNo'] = json.loads(
r2[1])['rows'][0]["fileUnifiedNo"]
print(updata['fileUnifiedNo'])
upl['fileUnifiedNo'] = updata['fileUnifiedNo']
print("正在上传" + fpath + "------" + str(i / chunkcount * 100) +
"%")
else:
print("----上传" + fpath + "失败-----")
raise Exception
i = i + 1
s.seturl("/shlocal/housepledge/assessplate/save/assessment"
) #housepledge/assessplate/query/applylist
jsonupd = json.dumps(updata, ensure_ascii=False)
s.setmsg(jsonupd)
uuid1 = uuid.uuid1()
result1 = ss.dopost(uuid1)
print(result1)
if result1:
if result1[0] == '0':
updata['reportId'] = updata["assessNo"]
updata['flag'] = "1"
updata['assessStatus'] = "7"
sql2 = sql_server("192.168.1.8", "sa", "ldpjwy", "icbc_api",
"1433")
sql2.updateorinsert("icbcapi_assessment",
{"applyNo": updata["applyNo"]}, updata,
['applyNo'], insertup)
else:
raise Exception
result2 = json.loads(result1[1])['rows']
result3 = [list(a.values()) for a in result2]
result4 = list(result2[0].keys())
laresult.append(result4)
for ff in result3:
sf = ff[0]
ff[0] = time.strftime("%Y-%m-%d",
time.localtime(int(sf / 1000)))
laresult.append(ff)
exlcelwrite(
laresult,
time.strftime("%Y%m%d%H%M%S", time.localtime(time.time())) +
".xls")
if __name__ == "__main__":
#f=r"E:\applyno"
#fl=bianli(f,"AID")
#upload(f,fl)
applyNo = ["AID0000037741"]
sql2 = sql_server("192.168.1.8", "sa", "ldpjwy", "icbc_api", "1433")
result2 = sql2.select(["dbo.icbcapi_assessment"], {"applyNo": applyNo},
None) #先去上传的查
print(result2)
# f="E:\\436.pdf"
# r=512
# file_rtow(f,r)
#getMD5(f)
#print(laresult)
#exlcelwrite(laresult, "xls//"+time.strftime("%Y%m%d%H%M%S", time.localtime(time.time()))+".xls")