#coding:utf8 import sys import os sys.path.append(os.path.join(os.path.dirname(__file__),"..")) import requests from utils.Utils import sendEmail,getCurrent_date,log import datetime import time from export.exportDocument import * import pandas as pd from apscheduler.schedulers.blocking import BlockingScheduler from export.exportUtils import rsa_encrpt import urllib.parse import base64 def export_medicine_friday(): current_date = time.strftime("%Y-%m-%d",time.localtime(time.mktime(time.localtime())-24*60*60)) start_time = time.strftime("%Y-%m-%d",time.localtime(time.mktime(time.localtime())-7*24*60*60)) if current_date<="2022-04-25": if datetime.datetime.now().weekday()==0: for i in range(10): try: df_data = exportDocument_medicine(start_time,current_date) df = pd.DataFrame(df_data) filename = os.path.dirname(__file__)+"/data/%s年%s至%s医疗数据导出.xlsx"%(start_time[:4],start_time,current_date) df.to_excel(filename,columns=list_df_columns) host = "smtp.exmail.qq.com" username = "vip@bidizhaobiao.com" password = "Biaoxun66-" receivers = ["1985262186@qq.com","1175730271@qq.com","1265797328@qq.com","1289358902@qq.com"] attachs = [filename] sendEmail(host,username,password,receivers,attachs=attachs) break except Exception as e: traceback.print_exc() log(str(e)) time.sleep(60) def export2(): def removeData(df_data): list_remove_index = [] list_c = df_data.get("招标人采购系统",[]) for _c_i in range(len(list_c)): if list_c[_c_i]=="企业采购系统": list_remove_index.append(_c_i) list_remove_index.reverse() print(list_remove_index) for k,v in df_data.items(): for _rc in list_remove_index: v.pop(_rc) for k,v in df_data.items(): v = v[:500] log("start export2:>>>>>>>>>>>>>>>") current_date = time.strftime("%Y-%m-%d",time.localtime(time.mktime(time.localtime())-24*60*60)) start_time = time.strftime("%Y-%m-%d",time.localtime(time.mktime(time.localtime())-7*24*60*60)) if current_date<="2024-02-28": if datetime.datetime.now().weekday()==4: for i in range(10): try: # start_time='2022-07-22' # current_date = '2022-07-28' log("start exporting export2:=================") # columns = ["doctitle","doctextcon","docchannel","product","province","bidway","city","district","page_time","industry","info_type","tenderee","project_code","project_name","sub_docs_json","tenderee_contact","tenderee_phone","agency","agency_contact","agency_phone","uuid","time_bidclose"] columns = ["doctitle","doctextcon","attachmenttextcon","docchannel","product","province","bidway","city","district","page_time","industry","info_type","tenderee","project_code","project_name","sub_docs_json","tenderee_contact","tenderee_phone","agency","agency_contact","agency_phone","uuid","time_bidclose","time_bidopen"] dict_channel = getDict_docchannel() list_query = [] str_keyword = ''' 机房建设 多媒体教室建设 数据中心机房 班班通 电子设备 视频 LED 监控 视频监控系统 信息安全 信息化建设 智能化 网络安全服务 网络安全系统 等级保护测评 信息系统 大数据中心 智慧教室建设 数据安全建设 可视化指挥联动平台 音频处理器 教学专用仪器 多媒体扩声系统 智慧校园 数字化 交换 路由 无线 城域网建设 触控一体机 教学设备 ''' list_keyword = splitIntoList(str_keyword,"[\s\n、，,]") str_not_keyword = ''' ''' list_not_key = splitIntoList(str_not_keyword,"[\s\n、，,]") tenderee_keywrod = "医院、大学、高校、高中" list_t_key = splitIntoList(tenderee_keywrod,"[\s\n、，,]") log(str(list_keyword)) bool_query = BoolQuery(must_queries=[ generateBoolShouldQuery(["doctitle","doctextcon","attachmenttextcon"],list_keyword,MatchPhraseQuery), generateBoolShouldQuery(["docchannel"],[52,102,114],TermQuery), RangeQuery("page_time",start_time,current_date,True,True), RangeQuery("status",151,300,True,True), # TermQuery("procurement_system","公安系统"), generateBoolShouldQuery(["province"],["湖南"],TermQuery), # generateBoolShouldQuery(["tenderee"],list_t_key,WildcardQuery) # generateBoolShouldQuery(["docchannel"],[101,118,119],TermQuery), ], # must_not_queries=[ # generateBoolShouldQuery(["doctitle"],list_not_key,MatchPhraseQuery), # ] ) list_query.append({"query":bool_query,"limit":700}) list_row = getDocument(list_query,columns) log("get document %d rows"%len(list_row)) df_data = {} set_line = set() # list_row = filterRow(list_row) getRowData(df_data,list_row,set_line,list_keyword,dict_channel,True) fixContactPerson(df_data,list_df_columns) removeData(df_data) df = pd.DataFrame(df_data) filename = os.path.dirname(__file__)+"/data/%s年%s至%s数据导出.xlsx"%(start_time[:4],start_time,current_date) df.to_excel(filename,columns=list_df_columns) log(str(filename)) host = "smtp.exmail.qq.com" username = "vip@bidizhaobiao.com" password = "Biaoxun66-" receivers = ["1175730271@qq.com","493894608@qq.com"] # receivers = ["1175730271@qq.com"] attachs = [filename] sendEmail(host,username,password,receivers,attachs=attachs) break except Exception as e: traceback.print_exc() def export5(): ''' 客户信息 公司名：武汉市浩盛特种建材有限责任公司 客户姓名：高诗琴 会员号：15392839439 会员等级：高级会员 成交金额：5900元 服务期：12个月 截止推送项目时间：2024-02-17 申请原因：2.16成交的新客户，客户反馈跟销售沟通是人工每天整理好项目信息推送，现在寻求技术协助支持。 客户需求： 地区：河北省、北京市、天津市、山东省、江苏省、上海市、浙江省、福建省、广东省、海南省 业务关键词：膨胀剂，抗裂剂，防水剂，外加剂，防腐剂，阻锈剂，密实剂，耐久性，氧化镁，镁质，涂料 （排除词，漆），需要招标信息 工作日下午四点文档信息推送到邮箱365531448@qq.com，每天更新最新的项目信息10条（不足10条可以按照更新信息推送） :return: ''' def getRowData(df_data,rows,set_line,list_keyword,dict_channel,dumplicate): dict_line = {} # list_data = getRow_ots(rows) _index = 0 rows.sort(key=lambda x:x.get("docid",0),reverse=True) set_col = set() df_columns = [] for row in rows[:10]: _index += 1 item = {} _dict = row set_dict_item_columns(set_col,df_columns,item,"省份",_dict.get("province","")) set_dict_item_columns(set_col,df_columns,item,"城市",_dict.get("city","")) set_dict_item_columns(set_col,df_columns,item,"公告类别",dict_channel.get(_dict.get("docchannel",""),"")) set_dict_item_columns(set_col,df_columns,item,"发布时间",_dict.get("page_time","")) set_dict_item_columns(set_col,df_columns,item,"公告标题",_dict.get("doctitle","")) set_dict_item_columns(set_col,df_columns,item,"招标单位",_dict.get("tenderee","")) set_dict_item_columns(set_col,df_columns,item,"招标联系人",_dict.get("tenderee_contact","")) set_dict_item_columns(set_col,df_columns,item,"招标联系人电话",_dict.get("tenderee_phone","")) set_dict_item_columns(set_col,df_columns,item,"代理单位",_dict.get("agency","")) set_dict_item_columns(set_col,df_columns,item,"代理单位联系人",_dict.get("agency_contact","")) set_dict_item_columns(set_col,df_columns,item,"代理单位联系人电话",_dict.get("agency_phone","")) set_dict_item_columns(set_col,df_columns,item,"链接","http://www.bidizhaobiao.com/info-%s.html"%str(_dict.get("docid",""))) for k,v in item.items(): if k not in df_data: df_data[k] = [] df_data[k].append(v) return df_columns log("start export5:>>>>>>>>>>>>>>>") current_date = time.strftime("%Y-%m-%d",time.localtime(time.mktime(time.localtime()))) if current_date<="2024-02-17": weekday = datetime.datetime.now().weekday() if weekday>=0 and weekday<=4: for i in range(10): try: # start_time='2022-07-22' # current_date = '2022-07-28' log("start exporting export2:=================") # columns = ["doctitle","doctextcon","docchannel","product","province","bidway","city","district","page_time","industry","info_type","tenderee","project_code","project_name","sub_docs_json","tenderee_contact","tenderee_phone","agency","agency_contact","agency_phone","uuid","time_bidclose"] columns = ["doctitle","doctextcon","attachmenttextcon","docchannel","product","province","bidway","city","district","page_time","industry","info_type","tenderee","project_code","project_name","sub_docs_json","tenderee_contact","tenderee_phone","agency","agency_contact","agency_phone","uuid","time_bidclose","time_bidopen"] columns = ["doctitle","docchannel","province","bidway","city","district","page_time","industry","info_type","tenderee","project_code","project_name","sub_docs_json","tenderee_contact","tenderee_phone","agency","agency_contact","agency_phone","uuid","time_bidclose","time_bidopen"] dict_channel = getDict_docchannel() list_query = [] str_province = ''' 河北、北京、天津、山东、江苏、上海、浙江、福建、广东、海南 ''' str_keyword = ''' 膨胀剂 抗裂剂 防水剂 外加剂 镁质 防腐剂 阻锈剂 密实剂 耐久性 氧化镁 ''' list_province = splitIntoList(str_province,"[\s\n、，,]") list_keyword = splitIntoList(str_keyword,"[\s\n、，,]") str_not_keyword = ''' 漆,复合风管，检测，涂料工程，涂料多乐士 ''' list_not_key = splitIntoList(str_not_keyword,"[\s\n、，,]") tenderee_keywrod = "医院、大学、高校、高中" list_t_key = splitIntoList(tenderee_keywrod,"[\s\n、，,]") log(str(list_keyword)) bool_query = BoolQuery(must_queries=[ generateBoolShouldQuery(["doctitle"],list_keyword,MatchPhraseQuery), generateBoolShouldQuery(["docchannel"],[52,102,114],TermQuery), TermQuery("page_time",current_date), RangeQuery("status",151,300,True,True), # TermQuery("procurement_system","公安系统"), generateBoolShouldQuery(["province"],list_province,TermQuery), # generateBoolShouldQuery(["tenderee"],list_t_key,WildcardQuery) # generateBoolShouldQuery(["docchannel"],[101,118,119],TermQuery), ], must_not_queries=[ generateBoolShouldQuery(["doctitle"],list_not_key,MatchPhraseQuery), ] ) list_query.append({"query":bool_query,"limit":700}) list_row = getDocument(list_query,columns) log("get document %d rows"%len(list_row)) df_data = {} set_line = set() # list_row = filterRow(list_row) df_column = getRowData(df_data,list_row,set_line,list_keyword,dict_channel,True) df = pd.DataFrame(df_data) print(df_data) filename = os.path.dirname(__file__)+"/data/%s年%s数据导出.xlsx"%(current_date[:4],current_date) df.to_excel(filename,columns=df_column) log(str(filename)) host = "smtp.exmail.qq.com" username = "vip@bidizhaobiao.com" password = "Biaoxun66-" receivers = ["1175730271@qq.com","365531448@qq.com"] # receivers = ["1175730271@qq.com"] attachs = [filename] sendEmail(host,username,password,receivers,attachs=attachs) break except Exception as e: traceback.print_exc() def export_document_except(): def getRowData(df_data,rows,dict_channel,set_columns1,list_df_column1): _index = 0 for row in rows: _index += 1 item = {} _dict = row _extract = json.loads(_dict.get("extract_json","{}")) product_attrs = _extract.get("product_attrs",{"data":[{}]}).get("data") docid = _dict.get("docid","") win_tenderer = "" bidding_budget = "" sub_docs_json = json.loads(_dict.get("sub_docs_json","[]")) for _doc in sub_docs_json: if win_tenderer=="": win_tenderer = _doc.get("win_tenderer","") if bidding_budget=="": bidding_budget= _doc.get("bidding_budget","") for _attrs in product_attrs: set_dict_item_columns(set_columns1,list_df_column1,item,"公告链接",'=HYPERLINK("http://www.bidizhaobiao.com/info-%s.html","查看公告")'%(str(docid))) set_dict_item_columns(set_columns1,list_df_column1,item,"公告时间",_dict.get("page_time")) set_dict_item_columns(set_columns1,list_df_column1,item,"省份",_dict.get("province")) set_dict_item_columns(set_columns1,list_df_column1,item,"城市",_dict.get("city")) set_dict_item_columns(set_columns1,list_df_column1,item,"地区",_dict.get("district")) set_dict_item_columns(set_columns1,list_df_column1,item,"项目标题",_dict.get("doctitle")) set_dict_item_columns(set_columns1,list_df_column1,item,"采购单位",_dict.get("tenderee")) set_dict_item_columns(set_columns1,list_df_column1,item,"中标单位",win_tenderer) set_dict_item_columns(set_columns1,list_df_column1,item,"产品名称",_dict.get("product")) set_dict_item_columns(set_columns1,list_df_column1,item,"规格型号",_attrs.get("specs","")) set_dict_item_columns(set_columns1,list_df_column1,item,"品牌",_attrs.get("brand","")) _quantity = "" _uniPrice = "" try: _quantity = int(_attrs.get("quantity","0")) if _quantity==0: _quantity = "" except Exception as e: pass try: _uniPrice = float(_attrs.get("_uniPrice","0")) if _uniPrice==0: _uniPrice = "" except Exception as e: pass set_dict_item_columns(set_columns1,list_df_column1,item,"数量",_quantity) set_dict_item_columns(set_columns1,list_df_column1,item,"单价（元）",_uniPrice) sumPrice = "" if _quantity!="" and _uniPrice!="": sumPrice = _quantity*_uniPrice set_dict_item_columns(set_columns1,list_df_column1,item,"总价（元）",sumPrice) set_dict_item_columns(set_columns1,list_df_column1,item,"项目金额（元）",bidding_budget) for k,v in item.items(): if k not in df_data: df_data[k] = [] df_data[k].append(v) current_date = time.strftime("%Y-%m-%d",time.localtime(time.mktime(time.localtime()))) current_date = '2022-04-01' columns = ["doctitle","docchannel","product","province","bidway","city","district","page_time","industry","info_type","tenderee","project_code","project_name","sub_docs_json","tenderee_contact","tenderee_phone","agency","agency_contact","agency_phone","uuid","time_bidclose","web_source_no","web_source_name","service_time","person_review","time_get_file_start","time_get_file_end","time_earnest_money_start","time_earnest_money_end"] dict_channel = getDict_docchannel() list_query = [] str_keyword = ''' 机房建设 多媒体教室建设 数据中心机房 班班通 电子设备 视频 LED 监控 视频监控系统 信息安全 信息化建设 网络安全 网络安全服务 网络安全系统 等级保护测评 信息系统 大数据中心 智慧教室建设 数据安全建设 可视化指挥联动平台 音频处理器 教学专用仪器 多媒体扩声系统 智慧校园 数字化 交换 路由 无线 信息安全 触控一体机 教学设备 ''' list_keyword = splitIntoList(str_keyword,"[\s\n、，,]") str_not_keyword = ''' ''' list_not_key = splitIntoList(str_not_keyword,"[\s\n、，,]") tenderee_keywrod = "医院、大学、高校、高中" list_t_key = splitIntoList(tenderee_keywrod,"[\s\n、，,]") s_q = BoolQuery(should_queries=[BoolQuery(must_queries=[TermQuery("docchannel",52)], must_not_queries=[WildcardQuery("tenderee","*")]), BoolQuery(must_queries=[generateBoolShouldQuery(["docchannel"],[101,119,120],TermQuery)], must_not_queries=[NestedQuery("sub_docs_json",WildcardQuery("sub_docs_json.win_tenderer","*"))])]) log(str(list_keyword)) set_columns1 = set() list_df_columns1 = [] bool_query = BoolQuery(must_queries=[ TermQuery("page_time",current_date), RangeQuery("status",151,451,True,True), s_q ], # must_not_queries=[ # generateBoolShouldQuery(["doctitle"],list_not_key,MatchPhraseQuery), # ] ) list_query.append({"query":bool_query,"limit":500000}) list_row = getDocument(list_query,columns) log("get document %d rows"%len(list_row)) df_data = {} set_line = set() # list_row = filterRow(list_row,list_not_key) getRowData(df_data,list_row,set_line,list_keyword,dict_channel,True) # fixContactPerson(df_data,list_df_columns) df = pd.DataFrame(df_data) filename = os.path.dirname(__file__)+"/data/%s异常数据导出.xlsx"%(current_date) print(list_df_columns) df.to_excel(filename,columns=list_df_columns) class Export3(): def trytimes(self): for _ in range(3): _succeed = self.export3() if _succeed: break def export3(self,): def getRowData(df_data,rows,dict_channel,set_columns1,list_df_column1): _index = 0 for row in rows: _index += 1 item = {} _dict = row _extract = json.loads(_dict.get("extract_json","{}")) demand_info = _extract.get("demand_info",{"data":[]}).get("data") docid = _dict.get("docid","") win_tenderer = "" bidding_budget = "" win_bid_price = "" sub_docs_json = json.loads(_dict.get("sub_docs_json","[]")) for _doc in sub_docs_json: if win_tenderer=="": win_tenderer = _doc.get("win_tenderer","") if bidding_budget=="": bidding_budget= _doc.get("bidding_budget","") if win_bid_price=="": win_bid_price = _doc.get("win_bid_price","") if len(demand_info)==0: demand_info = [{}] demand_info = [{}] for _attrs in demand_info: set_dict_item_columns(set_columns1,list_df_column1,item,"序号",_index) set_dict_item_columns(set_columns1,list_df_column1,item,"项目编号",_dict.get("project_code")) set_dict_item_columns(set_columns1,list_df_column1,item,"项目名称",_dict.get("project_name")) set_dict_item_columns(set_columns1,list_df_column1,item,"省份",_dict.get("province")) set_dict_item_columns(set_columns1,list_df_column1,item,"城市",_dict.get("city")) set_dict_item_columns(set_columns1,list_df_column1,item,"信息类型",dict_channel.get(_dict.get("docchannel"))) set_dict_item_columns(set_columns1,list_df_column1,item,"发布时间",_dict.get("crtime")) b_or_w = "" if bidding_budget!="": b_or_w = float(bidding_budget)/10000 elif win_bid_price!="": b_or_w = float(win_bid_price)/10000 set_dict_item_columns(set_columns1,list_df_column1,item,"中标金额/预算金额（万元）",b_or_w) set_dict_item_columns(set_columns1,list_df_column1,item,"招标单位",_dict.get("tenderee")) set_dict_item_columns(set_columns1,list_df_column1,item,"招标项目联系人","%s\n%s"%(_dict.get("tenderee_contact",""),_dict.get("tenderee_phone",""))) # 中标单位 中标项目联系人 中标单位主要联系人 中标单位联系人 代理单位 代理项目联系人 代理单位联系人 比地招标公告地址 set_dict_item_columns(set_columns1,list_df_column1,item,"招标单位联系人","") set_dict_item_columns(set_columns1,list_df_column1,item,"中标单位",win_tenderer) set_dict_item_columns(set_columns1,list_df_column1,item,"中标项目联系人","") set_dict_item_columns(set_columns1,list_df_column1,item,"中标单位主要联系人","") set_dict_item_columns(set_columns1,list_df_column1,item,"中标单位联系人","") set_dict_item_columns(set_columns1,list_df_column1,item,"代理单位",_dict.get("agency","")) set_dict_item_columns(set_columns1,list_df_column1,item,"代理项目联系人","%s\n%s"%(_dict.get("agency_contact",""),_dict.get("agency_phone",""))) set_dict_item_columns(set_columns1,list_df_column1,item,"代理单位联系人","") _key ='''MIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQC8uZYYV6ls+5KMzUfvsPvv3XRdwkcTBj/ppB03mijUPHYTGvYSE0cQTbQrnIbXFtUYJguakpKLmVyH+T/w6vhxbQNlaykfe8RXEh4i4IJk8s/Qb0E0xODsjKBEr8VdDYeqqduWrtJpttXAvv93SsTPvgZBditRzJAzk0XH56zL1wIDAQAB''' _key = base64.b64decode(_key) _url_map = {"userId":"001795335", "timestamp":"%d"%(time.time()*1000), "docid":str(_dict.get("docid",""))} _encrpt_text = base64.b64encode(rsa_encrpt(json.dumps(_url_map),_key)) _encrpt_text = urllib.parse.quote(_encrpt_text) _url = "http://www.bidizhaobiao.com/info-%s.html?emailSecret=%s"%(str(_dict.get("docid")),_encrpt_text) set_dict_item_columns(set_columns1,list_df_column1,item,"比地招标公告地址",_url) # set_dict_item_columns(set_columns1,list_df_column1,item,"项目编号",_dict.get("project_code")) # set_dict_item_columns(set_columns1,list_df_column1,item,"项目名称",_attrs.get("project_name")) # # set_dict_item_columns(set_columns1,list_df_column1,item,"需求",_attrs.get("demand")) # set_dict_item_columns(set_columns1,list_df_column1,item,"预算（元）",_attrs.get("budget")) # set_dict_item_columns(set_columns1,list_df_column1,item,"开始时间",_attrs.get("order_begin")) # set_dict_item_columns(set_columns1,list_df_column1,item,"中标单位",win_tenderer) # set_dict_item_columns(set_columns1,list_df_column1,item,"产品名称",_attrs.get("product")) # set_dict_item_columns(set_columns1,list_df_column1,item,"规格型号",_attrs.get("specs","")) # set_dict_item_columns(set_columns1,list_df_column1,item,"品牌",_attrs.get("brand","")) # _quantity = "" # _uniPrice = "" # try: # _quantity = int(_attrs.get("quantity","0")) # if _quantity==0: # _quantity = "" # except Exception as e: # pass # try: # _uniPrice = float(_attrs.get("unitPrice","0")) # if _uniPrice==0: # _uniPrice = "" # except Exception as e: # pass # set_dict_item_columns(set_columns1,list_df_column1,item,"数量",_quantity) # set_dict_item_columns(set_columns1,list_df_column1,item,"单价（元）",_uniPrice) # sumPrice = "" # if _quantity!="" and _uniPrice!="": # sumPrice = _quantity*_uniPrice # set_dict_item_columns(set_columns1,list_df_column1,item,"总价（元）",sumPrice) # set_dict_item_columns(set_columns1,list_df_column1,item,"项目金额（元）",bidding_budget) for k,v in item.items(): if k not in df_data: df_data[k] = [] df_data[k].append(v) def getRowData1(df_data,rows,dict_channel,set_columns1,list_df_column1): _index = 0 for row in rows: _index += 1 item = {} _dict = row _extract = json.loads(_dict.get("extract_json","{}")) demand_info = _extract.get("demand_info",{"data":[]}).get("data") docid = _dict.get("docid","") win_tenderer = "" bidding_budget = "" win_bid_price = "" sub_docs_json = json.loads(_dict.get("sub_docs_json","[]")) for _doc in sub_docs_json: if win_tenderer=="": win_tenderer = _doc.get("win_tenderer","") if bidding_budget=="": bidding_budget= _doc.get("bidding_budget","") if win_bid_price=="": win_bid_price = _doc.get("win_bid_price","") if len(demand_info)==0: demand_info = [{}] demand_info = [{}] for _attrs in demand_info: # set_dict_item_columns(set_columns1,list_df_column1,item,"序号",_index) # set_dict_item_columns(set_columns1,list_df_column1,item,"项目编号",_dict.get("project_code")) set_dict_item_columns(set_columns1,list_df_column1,item,"地区",_dict.get("district")) set_dict_item_columns(set_columns1,list_df_column1,item,"省份",_dict.get("province")) set_dict_item_columns(set_columns1,list_df_column1,item,"项目名称",_dict.get("project_name")) set_dict_item_columns(set_columns1,list_df_column1,item,"采购人",_dict.get("tenderee")) set_dict_item_columns(set_columns1,list_df_column1,item,"招标时间",_dict.get("page_time")) b_or_w = "" if bidding_budget!="": b_or_w = float(bidding_budget)/10000 elif win_bid_price!="": b_or_w = float(win_bid_price)/10000 set_dict_item_columns(set_columns1,list_df_column1,item,"招标预算",bidding_budget) set_dict_item_columns(set_columns1,list_df_column1,item,"中标时间",_dict.get("page_time")) set_dict_item_columns(set_columns1,list_df_column1,item,"中标金额",win_bid_price) set_dict_item_columns(set_columns1,list_df_column1,item,"中标人",win_tenderer) set_dict_item_columns(set_columns1,list_df_column1,item,"采购模式",_dict.get("bidway")) set_dict_item_columns(set_columns1,list_df_column1,item,"命中关键词",_dict.get("keyword")) set_dict_item_columns(set_columns1,list_df_column1,item,"采购品牌",'') set_dict_item_columns(set_columns1,list_df_column1,item,"采购数量",'') for k,v in item.items(): if k not in df_data: df_data[k] = [] df_data[k].append(v) def getContacts(contacts): try: if contacts is None or contacts=="": return "" _contacts = json.loads(contacts) list_c = [] _count = 0 for _c in _contacts: _count += 1 contact_person = _c.get("contact_person","") phone_no = _c.get("phone_no","") list_c.append("%s(企业联系人%d)\n%s"%(str(contact_person),_count,str(phone_no))) if _count>=5: break return ",\n".join(list_c) except Exception as e: return "" def fixContactPerson(df_data,list_df_columns,get_legal_person=False): set_enterprise = set() if len(df_data.keys())>0: for _tenderee,_agency,_win in zip(df_data["招标单位"],df_data["代理单位"],df_data["中标单位"]): set_enterprise.add(_tenderee) set_enterprise.add(_agency) set_enterprise.add(_win) if "" in set_enterprise: set_enterprise.remove("") if None in set_enterprise: set_enterprise.remove(None) dict_enterprise = getDictEnterprise(list(set_enterprise),columns_to_get = ["contacts","procurement_system"]) # conn = getConnection_oracle() # cursor = conn.cursor() if len(set_enterprise)>0: for _i in range(len(df_data["招标单位"])): _enterprise_name = df_data["招标单位"][_i] df_data["招标单位联系人"][_i] = getContacts(dict_enterprise.get(_enterprise_name,{}).get("contacts")) _enterprise_name = df_data["代理单位"][_i] df_data["代理单位联系人"][_i] = getContacts(dict_enterprise.get(_enterprise_name,{}).get("contacts")) _enterprise_name = df_data["中标单位"][_i] df_data["中标单位联系人"][_i] = getContacts(dict_enterprise.get(_enterprise_name,{}).get("contacts")) # if "采购系统" not in df_data: # df_data["采购系统"] = [] # df_data["采购系统"].append(dict_enterprise.get(_enterprise_name,{}).get("procurement_system","")) # list_df_columns.extend(['采购系统']) ''' export by customer's subscription :return: ''' try: ots_client = getConnect_ots() subscription4 = ''' 触控一体机 交互平板 交互一体机 交互智能平板 交互大屏 教学一体机 智慧屏 智慧黑板 互动黑板 班班通 多媒体教室 多媒体设备 多媒体系统 智慧教室 ''' # provinces = "河北 山西 广东 海南 江苏 安徽 山东 河南 湖北 湖南 重庆 黑龙江 陕西 甘肃 青海 宁夏" provinces = "海南 江苏 安徽 湖北 湖南 重庆 甘肃 青海 宁夏 河南 北京 天津" dict_channel = getDict_docchannel() list_province = splitIntoList(provinces,"\s|,|，|、") list_subscription4 = splitIntoList(subscription4,"\s|,|，|、") current_date = time.strftime("%Y-%m-%d",time.localtime(time.mktime(time.localtime()))) last_date = timeAdd(current_date,-1) # last_date = "2023-12-08" # current_date = "2023-12-10" set_columns1 = set() list_df_columns1 = [] bool_query = BoolQuery(must_queries=[ generateBoolShouldQuery(["docchannel"],[102,114],TermQuery), RangeQuery("page_time",last_date), RangeQuery("status",201,300,True,True), generateBoolShouldQuery(["doctitle","doctextcon","attachmenttextcon"],list_subscription4,MatchPhraseQuery), generateBoolShouldQuery(["province"],list_province,WildcardQuery), RangeQuery("crtime","%s 21:00:00"%last_date,"%s 21:00:00"%current_date) ], # must_not_queries=[ # generateBoolShouldQuery(["doctitle"],list_not_key,MatchPhraseQuery), # ] ) columns = ["doctitle","docchannel","original_docchannel","product","crtime","province","bidway","city","district","page_time","industry","info_type","tenderee","project_code","project_name","sub_docs_json","extract_json","tenderee_contact","tenderee_phone","agency","agency_contact","agency_phone"] list_query = [{"query":bool_query,"limit":50000}] list_row = getDocument(list_query,columns) filename0 = os.path.dirname(__file__)+"/data/%s订阅4订阅5数据导出数量%d_%s.xlsx"%(current_date,len(list_row),getCurrent_date("%Y-%m-%d_%H%M%S")) df_data = {} if len(list_row)>0: getRowData(df_data,list_row,dict_channel,set_columns1,list_df_columns1) fixContactPerson(df_data,list_df_columns1) df = pd.DataFrame(df_data) df.to_excel(filename0,columns=list_df_columns1,index=False) else: df = pd.DataFrame(df_data) df.to_excel(filename0,index=False) host = "smtp.exmail.qq.com" username = "vip1@bidizhaobiao.com" password = "Biaoxun666+" # receivers = ["1175730271@qq.com","995116318@qq.com","huangxiaofang@cvte.com"] receivers = ["1175730271@qq.com","1208135584@qq.com","youyuer@cvte.com","chenyuxue@cvte.com"] # receivers = ["1175730271@qq.com"] attachs = [filename0] sendEmail(host,username,password,receivers,attachs=attachs) return True except Exception as e: traceback.print_exc() return False def export3_1(self,): def getRowData(df_data,rows,dict_channel,set_columns1,list_df_column1): _index = 0 for row in rows: _index += 1 item = {} _dict = row _extract = json.loads(_dict.get("extract_json","{}")) demand_info = _extract.get("demand_info",{"data":[]}).get("data") docid = _dict.get("docid","") win_tenderer = "" bidding_budget = "" win_bid_price = "" sub_docs_json = json.loads(_dict.get("sub_docs_json","[]")) for _doc in sub_docs_json: if win_tenderer=="": win_tenderer = _doc.get("win_tenderer","") if bidding_budget=="": bidding_budget= _doc.get("bidding_budget","") if win_bid_price=="": win_bid_price = _doc.get("win_bid_price","") if len(demand_info)==0: demand_info = [{}] demand_info = [{}] for _attrs in demand_info: set_dict_item_columns(set_columns1,list_df_column1,item,"序号",_index) set_dict_item_columns(set_columns1,list_df_column1,item,"项目编号",_dict.get("project_code")) set_dict_item_columns(set_columns1,list_df_column1,item,"项目名称",_dict.get("project_name")) set_dict_item_columns(set_columns1,list_df_column1,item,"省份",_dict.get("province")) set_dict_item_columns(set_columns1,list_df_column1,item,"城市",_dict.get("city")) set_dict_item_columns(set_columns1,list_df_column1,item,"信息类型",dict_channel.get(_dict.get("docchannel"))) set_dict_item_columns(set_columns1,list_df_column1,item,"发布时间",_dict.get("crtime")) b_or_w = "" if bidding_budget!="": b_or_w = float(bidding_budget)/10000 elif win_bid_price!="": b_or_w = float(win_bid_price)/10000 set_dict_item_columns(set_columns1,list_df_column1,item,"中标金额/预算金额（万元）",b_or_w) set_dict_item_columns(set_columns1,list_df_column1,item,"招标单位",_dict.get("tenderee")) set_dict_item_columns(set_columns1,list_df_column1,item,"招标项目联系人","%s\n%s"%(_dict.get("tenderee_contact",""),_dict.get("tenderee_phone",""))) # 中标单位 中标项目联系人 中标单位主要联系人 中标单位联系人 代理单位 代理项目联系人 代理单位联系人 比地招标公告地址 set_dict_item_columns(set_columns1,list_df_column1,item,"招标单位联系人","") set_dict_item_columns(set_columns1,list_df_column1,item,"中标单位",win_tenderer) set_dict_item_columns(set_columns1,list_df_column1,item,"中标项目联系人","") set_dict_item_columns(set_columns1,list_df_column1,item,"中标单位主要联系人","") set_dict_item_columns(set_columns1,list_df_column1,item,"中标单位联系人","") set_dict_item_columns(set_columns1,list_df_column1,item,"代理单位",_dict.get("agency","")) set_dict_item_columns(set_columns1,list_df_column1,item,"代理项目联系人","%s\n%s"%(_dict.get("agency_contact",""),_dict.get("agency_phone",""))) set_dict_item_columns(set_columns1,list_df_column1,item,"代理单位联系人","") _key ='''MIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQC8uZYYV6ls+5KMzUfvsPvv3XRdwkcTBj/ppB03mijUPHYTGvYSE0cQTbQrnIbXFtUYJguakpKLmVyH+T/w6vhxbQNlaykfe8RXEh4i4IJk8s/Qb0E0xODsjKBEr8VdDYeqqduWrtJpttXAvv93SsTPvgZBditRzJAzk0XH56zL1wIDAQAB''' _key = base64.b64decode(_key) _url_map = {"userId":"001795335", "timestamp":"%d"%(time.time()*1000), "docid":str(_dict.get("docid",""))} _encrpt_text = base64.b64encode(rsa_encrpt(json.dumps(_url_map),_key)) _encrpt_text = urllib.parse.quote(_encrpt_text) _url = "http://www.bidizhaobiao.com/info-%s.html?emailSecret=%s"%(str(_dict.get("docid")),_encrpt_text) set_dict_item_columns(set_columns1,list_df_column1,item,"比地招标公告地址",_url) # set_dict_item_columns(set_columns1,list_df_column1,item,"项目编号",_dict.get("project_code")) # set_dict_item_columns(set_columns1,list_df_column1,item,"项目名称",_attrs.get("project_name")) # # set_dict_item_columns(set_columns1,list_df_column1,item,"需求",_attrs.get("demand")) # set_dict_item_columns(set_columns1,list_df_column1,item,"预算（元）",_attrs.get("budget")) # set_dict_item_columns(set_columns1,list_df_column1,item,"开始时间",_attrs.get("order_begin")) # set_dict_item_columns(set_columns1,list_df_column1,item,"中标单位",win_tenderer) # set_dict_item_columns(set_columns1,list_df_column1,item,"产品名称",_attrs.get("product")) # set_dict_item_columns(set_columns1,list_df_column1,item,"规格型号",_attrs.get("specs","")) # set_dict_item_columns(set_columns1,list_df_column1,item,"品牌",_attrs.get("brand","")) # _quantity = "" # _uniPrice = "" # try: # _quantity = int(_attrs.get("quantity","0")) # if _quantity==0: # _quantity = "" # except Exception as e: # pass # try: # _uniPrice = float(_attrs.get("unitPrice","0")) # if _uniPrice==0: # _uniPrice = "" # except Exception as e: # pass # set_dict_item_columns(set_columns1,list_df_column1,item,"数量",_quantity) # set_dict_item_columns(set_columns1,list_df_column1,item,"单价（元）",_uniPrice) # sumPrice = "" # if _quantity!="" and _uniPrice!="": # sumPrice = _quantity*_uniPrice # set_dict_item_columns(set_columns1,list_df_column1,item,"总价（元）",sumPrice) # set_dict_item_columns(set_columns1,list_df_column1,item,"项目金额（元）",bidding_budget) for k,v in item.items(): if k not in df_data: df_data[k] = [] df_data[k].append(v) def getContacts(contacts): try: if contacts is None or contacts=="": return "" if isinstance(contacts,str): _contacts = json.loads(contacts) else: _contacts = contacts list_c = [] _count = 0 for _c in _contacts: _count += 1 contact_person = _c.get("contact_person","") phone_no = _c.get("phone_no","") list_c.append("%s(企业联系人%d)\n%s"%(str(contact_person),_count,str(phone_no))) if _count>=5: break print("getContacts",",\n".join(list_c)) return ",\n".join(list_c) except Exception as e: traceback.print_exc() print("getContacts","") return "" def fixContactPerson(df_data,list_df_columns,get_legal_person=False): set_enterprise = set() if len(df_data.keys())>0: for _tenderee,_agency,_win in zip(df_data["招标单位"],df_data["代理单位"],df_data["中标单位"]): set_enterprise.add(_tenderee) set_enterprise.add(_agency) set_enterprise.add(_win) if "" in set_enterprise: set_enterprise.remove("") if None in set_enterprise: set_enterprise.remove(None) dict_enterprise = getDictEnterprise(list(set_enterprise),columns_to_get = ["contacts","procurement_system"]) # conn = getConnection_oracle() # cursor = conn.cursor() if len(set_enterprise)>0: for _i in range(len(df_data["招标单位"])): _enterprise_name = df_data["招标单位"][_i] df_data["招标单位联系人"][_i] = getContacts(dict_enterprise.get(_enterprise_name,{}).get("contacts")) _enterprise_name = df_data["代理单位"][_i] df_data["代理单位联系人"][_i] = getContacts(dict_enterprise.get(_enterprise_name,{}).get("contacts")) _enterprise_name = df_data["中标单位"][_i] df_data["中标单位联系人"][_i] = getContacts(dict_enterprise.get(_enterprise_name,{}).get("contacts")) # if "采购系统" not in df_data: # df_data["采购系统"] = [] # df_data["采购系统"].append(dict_enterprise.get(_enterprise_name,{}).get("procurement_system","")) # list_df_columns.extend(['采购系统']) ''' export by customer's subscription :return: ''' try: ots_client = getConnect_ots() subscription4 = ''' 录播、三个课堂、互动课堂、同步课堂、双师课堂、专递课堂、微格教室、远程课堂、督导巡课、推门听课、互动教学 ''' # provinces = "河北 山西 广东 海南 江苏 安徽 山东 河南 湖北 湖南 重庆 黑龙江 陕西 甘肃 青海 宁夏" provinces = "海南 江苏 安徽 湖北 湖南 重庆 甘肃 青海 宁夏 河南" dict_channel = getDict_docchannel() list_province = splitIntoList(provinces,"\s|,|，|、") list_subscription4 = splitIntoList(subscription4,"\s|,|，|、") current_date = time.strftime("%Y-%m-%d",time.localtime(time.mktime(time.localtime()))) last_date = timeAdd(current_date,-1) # last_date = "2023-08-14" # current_date = "2023-08-20" set_columns1 = set() list_df_columns1 = [] bool_query = BoolQuery(must_queries=[ generateBoolShouldQuery(["docchannel"],[102,114],TermQuery), RangeQuery("page_time",last_date), RangeQuery("status",201,300,True,True), generateBoolShouldQuery(["doctitle","doctextcon","attachmenttextcon"],list_subscription4,MatchPhraseQuery), # generateBoolShouldQuery(["province"],list_province,WildcardQuery), RangeQuery("crtime","%s 21:00:00"%last_date,"%s 21:00:00"%current_date) ], # must_not_queries=[ # generateBoolShouldQuery(["doctitle"],list_not_key,MatchPhraseQuery), # ] ) columns = ["doctitle","docchannel","original_docchannel","product","crtime","province","bidway","city","district","page_time","industry","info_type","tenderee","project_code","project_name","sub_docs_json","extract_json","tenderee_contact","tenderee_phone","agency","agency_contact","agency_phone"] list_query = [{"query":bool_query,"limit":50000}] list_row = getDocument(list_query,columns) filename0 = os.path.dirname(__file__)+"/data/%s订阅4订阅5数据导出数量%d_%s.xlsx"%(current_date,len(list_row),getCurrent_date("%Y-%m-%d_%H%M%S")) df_data = {} if len(list_row)>0: getRowData(df_data,list_row,dict_channel,set_columns1,list_df_columns1) fixContactPerson(df_data,list_df_columns1) df = pd.DataFrame(df_data) df.to_excel(filename0,columns=list_df_columns1,index=False) else: df = pd.DataFrame(df_data) df.to_excel(filename0,index=False) host = "smtp.exmail.qq.com" username = "vip1@bidizhaobiao.com" password = "Biaoxun666+" # receivers = ["1175730271@qq.com","995116318@qq.com","huangxiaofang@cvte.com"] receivers = ["1175730271@qq.com","1208135584@qq.com","wanghongyan@cvte.com"] # receivers = ["1175730271@qq.com"] attachs = [filename0] sendEmail(host,username,password,receivers,attachs=attachs) return True except Exception as e: traceback.print_exc() return False def export4(self): def getRowData(df_data,rows,dict_channel,set_columns1,list_df_column1): dict_filter,dict_update = getFixData() _index = 0 for row in rows: _index += 1 item = {} _dict = row keys = row["keyword"] log("================") log(keys) log(_dict.get("doctitle","")+_dict.get("doctextcon","")+_dict.get("attachmenttextcon","")) _keyword = str(list(set([ i for i in re.findall("|".join([re.escape(i) for i in keys if i!='']),_dict.get("doctitle","")+_dict.get("doctextcon","")+_dict.get("attachmenttextcon","")) if i!='' ]))) log(_keyword) _extract = json.loads(_dict.get("extract_json","{}")) if len(set(keys)&set(["AR一体机","一体机","短焦投影仪","教学一体机","云桌面","录播","电子书包","电子班牌","智慧黑板"]))>0: product_attrs = _extract.get("product_attrs",{"data":[{}]}).get("data") else: product_attrs = [{}] if len(product_attrs)==0: product_attrs = [{}] docid = _dict.get("docid","") win_tenderer = "" bidding_budget = "" win_bid_price = "" sub_docs_json = json.loads(_dict.get("sub_docs_json","[]")) for _doc in sub_docs_json: if win_tenderer=="": win_tenderer = _doc.get("win_tenderer","") if bidding_budget=="": bidding_budget= _doc.get("bidding_budget","") if win_bid_price=="": win_bid_price = _doc.get("win_bid_price","") _index1 = 0 if _dict.get("docid") in dict_filter: print("====filter") continue if _dict.get("docid") in dict_update: print("====update") win_bid_price = dict_update[_dict.get("docid")].get("win_bid_price","") bidding_budget= dict_update[_dict.get("docid")].get("bidding_budget","") for _attrs in product_attrs: _index1 += 1 if re.search("运费",_attrs.get("product","")) is not None: if _index1==1: _attrs = {} else: continue set_dict_item_columns(set_columns1,list_df_column1,item,"序号",_index) # set_dict_item_columns(set_columns1,list_df_column1,item,"正文",_dict.get("doctextcon","")) # set_dict_item_columns(set_columns1,list_df_column1,item,"附件",_dict.get("attachmenttextcon","")) # set_dict_item_columns(set_columns1,list_df_column1,item,"项目名称",_dict.get("project_name","")) set_dict_item_columns(set_columns1,list_df_column1,item,"docid",_dict.get("docid")) set_dict_item_columns(set_columns1,list_df_column1,item,"网站链接",'=HYPERLINK("http://www.bidizhaobiao.com/info-%s.html","查看公告")'%(str(docid))) set_dict_item_columns(set_columns1,list_df_column1,item,"省份",_dict.get("province")) set_dict_item_columns(set_columns1,list_df_column1,item,"城市",_dict.get("city")) set_dict_item_columns(set_columns1,list_df_column1,item,"地区",_dict.get("district")) set_dict_item_columns(set_columns1,list_df_column1,item,"公告时间",_dict.get("page_time")) set_dict_item_columns(set_columns1,list_df_column1,item,"招标公告时间",'') set_dict_item_columns(set_columns1,list_df_column1,item,"项目标题",_dict.get("doctitle")) set_dict_item_columns(set_columns1,list_df_column1,item,"采购单位",_dict.get("tenderee")) set_dict_item_columns(set_columns1,list_df_column1,item,"中标供应商",win_tenderer) set_dict_item_columns(set_columns1,list_df_column1,item,"采购方式",_dict.get("bidway","")) set_dict_item_columns(set_columns1,list_df_column1,item,"招标采购方式","") set_dict_item_columns(set_columns1,list_df_column1,item,"采购系统","") set_dict_item_columns(set_columns1,list_df_column1,item,"产品名称",_attrs.get("product")) set_dict_item_columns(set_columns1,list_df_column1,item,"品牌",_attrs.get("brand","")) # set_dict_item_columns(set_columns1,list_df_column1,item,"规格型号",_attrs.get("specs","")) set_dict_item_columns(set_columns1,list_df_column1,item,"预算金额（元）",bidding_budget) # set_dict_item_columns(set_columns1,list_df_column1,item,"招标预算金额（元）","") _quantity = "" _uniPrice = "" try: _quantity = int(re.sub("[^\d]","",_attrs.get("quantity","0"))) if _quantity==0: _quantity = "" except Exception as e: pass try: _uniPrice = float(_attrs.get("unitPrice","0")) if _uniPrice==0: _uniPrice = "" except Exception as e: pass set_dict_item_columns(set_columns1,list_df_column1,item,"数量",_quantity) set_dict_item_columns(set_columns1,list_df_column1,item,"单价（元）",_uniPrice) sumPrice = "" if _quantity!="" and _uniPrice!="": sumPrice = _quantity*_uniPrice set_dict_item_columns(set_columns1,list_df_column1,item,"总价（元）",sumPrice) set_dict_item_columns(set_columns1,list_df_column1,item,"中标金额（元）",win_bid_price) set_dict_item_columns(set_columns1,list_df_column1,item,"关键词",_keyword) for k,v in item.items(): if k not in df_data: df_data[k] = [] df_data[k].append(v) def filterRow(list_row,key="docid"): set_id = set() _filter_row = [] for row in list_row: _v = row.get(key,"") if _v in set_id: continue set_id.add(_v) _filter_row.append(row) return _filter_row def fixContactPerson(df_data,list_df_columns,get_legal_person=False): set_enterprise = set() if len(df_data.keys())>0: for _tenderee,_win_tenderer in zip(df_data["采购单位"],df_data["中标供应商"]): set_enterprise.add(_tenderee) set_enterprise.add(_win_tenderer) if "" in set_enterprise: set_enterprise.remove("") if None in set_enterprise: set_enterprise.remove(None) dict_enterprise = getDictEnterprise(list(set_enterprise),columns_to_get = ["procurement_system"]) # conn = getConnection_oracle() # cursor = conn.cursor() if len(set_enterprise)>0: for _i in range(len(df_data["采购单位"])): _enterprise_name = df_data["采购单位"][_i] # if df_data["招标联系人电话"][_i]=="": # contacts = dict_enterprise.get(_enterprise_name,{}).get("contacts") # if contacts is not None: # _person,_phone = getOneContact(contacts) # df_data["招标联系人"][_i] = _person # df_data["招标联系人电话"][_i] = _phone df_data["采购系统"][_i] = dict_enterprise.get(_enterprise_name,{}).get("procurement_system","") def fixZhaobiao_page_time(df_data): ots_client = getConnect_ots() for i in range(len(df_data["docid"])): _docid = df_data["docid"][i] bool_query = BoolQuery(must_queries=[TermQuery("docids",_docid)]) rows,next_token,total_count,is_all_succeed = ots_client.search("project2","project2_index", SearchQuery(bool_query,limit=1), ColumnsToGet(["zhao_biao_page_time","bidway","bidding_budget"],ColumnReturnType.SPECIFIED)) list_data = getRow_ots(rows) if len(list_data)>0: df_data["招标公告时间"][i] = list_data[0].get("zhao_biao_page_time","") df_data["招标采购方式"][i] = list_data[0].get("bidway","") new_bidding_budget = list_data[0].get("bidding_budget",0) if df_data["预算金额（元）"][i]=="" or float(df_data["预算金额（元）"][i])==0: if df_data["中标金额（元）"][i]!="" and new_bidding_budget>=float(df_data["中标金额（元）"][i]): df_data["预算金额（元）"][i] = new_bidding_budget # df_data["招标预算金额（元）"][i] = list_data[0].get("bidding_budget","") def popRows(df_data): list_pop = [] set_docid = set() for _c in range(len(df_data["采购系统"])): if df_data["采购系统"][_c]!="教育系统" or df_data["中标金额（元）"][_c]=="": list_pop.append(_c) set_docid.add(df_data["docid"][_c]) list_pop.reverse() for k,v in df_data.items(): for _p in list_pop: v.pop(_p) _index = 0 set_docid1 = set() for _i in range(len(df_data["序号"])): _docid = df_data["docid"][_i] if _docid not in set_docid1: _index += 1 set_docid1.add(_docid) df_data["序号"][_i] = _index return len(set_docid) df = pd.read_excel("20220927v1.4.xlsx",sheetname=1) list_search_dict = [] for title_kw,content_kw,title_rm,content_rm in zip(df["标题采集关键词"],df["全文采集关键词"],df["标题排除词"],df["全文排除词"]): if not isinstance(title_kw,str): title_kw = "" if not isinstance(content_kw,str): content_kw = "" if not isinstance(title_rm,str): title_rm = "" if not isinstance(content_rm,str): content_rm = "" _dict = {"title_kw":re.split("\s|,|，",str(title_kw)), "content_kw":re.split("\s|,|，",str(content_kw)), "title_rm":re.split("\s|,|，",str(title_rm)), "content_rm":re.split("\s|,|，",str(content_rm))} list_search_dict.append(_dict) columns = ["extract_json","doctextcon","attachmenttextcon","doctitle","docchannel","product","province","bidway","city","district","page_time","industry","info_type","tenderee","project_code","project_name","sub_docs_json","tenderee_contact","tenderee_phone","agency","agency_contact","agency_phone","uuid","time_bidclose","web_source_no","web_source_name","service_time","person_review","time_get_file_start","time_get_file_end","time_earnest_money_start","time_earnest_money_end"] current_date = time.strftime("%Y-%m-%d",time.localtime(time.mktime(time.localtime()))) last_date = timeAdd(current_date,-1) current_date = "2022-10-14" last_date = "2023-05-07" dict_channel = getDict_docchannel() list_mq = [] list_query = [] for _d in list_search_dict: print(_d) kw_should_q = [] list_keys = [] title_kw = _d.get("title_kw",[]) if len(title_kw)>0: kw_should_q.append(generateBoolShouldQuery(["doctitle"],title_kw,MatchPhraseQuery)) list_keys.extend(title_kw) content_kw = _d.get("content_kw",[]) if len(content_kw)>0: kw_should_q.append(generateBoolShouldQuery(["doctextcon","attachmenttextcon"],content_kw,MatchPhraseQuery)) list_keys.extend(content_kw) rm_should_q = [] title_rm = _d.get("title_rm",[]) if len(title_rm)>0: rm_should_q.append(generateBoolShouldQuery(["doctitle"],title_rm,MatchPhraseQuery)) content_rm = _d.get("content_rm",[]) if len(content_rm)>0: rm_should_q.append(generateBoolShouldQuery(["doctextcon","attachmenttextcon"],content_rm,MatchPhraseQuery)) _query = BoolQuery(must_queries=[ generateBoolShouldQuery(["docchannel"],[101],TermQuery), # RangeQuery("page_time",last_date,current_date), RangeQuery("page_time",current_date,last_date), # RangeQuery("status",201,300,True,True), # TermQuery("docid",263568527), # generateBoolShouldQuery(["doctitle","doctextcon","attachmenttextcon"],list_keys,MatchPhraseQuery), BoolQuery(should_queries=kw_should_q), # generateBoolShouldQuery(["province"],list_province,WildcardQuery), # RangeQuery("crtime","%s"%last_date,"%s"%current_date) ], must_not_queries=rm_should_q) list_query.append({"query":_query,"limit":50000,"keyword":list_keys}) break def getFixData(): filename = "C:\\Users\\Administrator\\Desktop\\视源数据清洗.xlsx" dict_filter = {} dict_update = {} df = pd.read_excel(filename,0) for _docid,bidding_budget,win_bid_price,is_dup,is_fb in zip(df["docid"],df["检查招标金额"],df["中标金额（元）"],df["是否重复"],df["是否废标"]): if str(is_dup) in ("是","1") or str(is_fb) in ("是","1"): dict_filter[_docid] = 1 else: dict_update[_docid] = {"docid":_docid, "bidding_budget":bidding_budget, "win_bid_price":win_bid_price} df = pd.read_excel(filename,1) for _docid,bidding_budget,win_bid_price,is_dup,is_fb in zip(df["docid"],df["检查招标金额"],df["中标金额（元）"],df["是否重复"],df["是否废标"]): if str(is_dup) in ("是","1") or str(is_fb) in ("是","1"): dict_filter[_docid] = 1 else: dict_update[_docid] = {"docid":_docid, "bidding_budget":bidding_budget, "win_bid_price":win_bid_price} return dict_filter,dict_update list_row = getDocument(list_query,columns) list_row = filterRow(list_row) print("list_row",len(list_row)) set_columns1 = set() list_df_columns1 = [] df_data = {} getRowData(df_data,list_row,dict_channel,set_columns1,list_df_columns1) print("len df_data",len(df_data["docid"])) fixContactPerson(df_data,list_df_columns1) fixZhaobiao_page_time(df_data) size_pop = popRows(df_data) print("size_pop",size_pop) df = pd.DataFrame(df_data) filename = os.path.dirname(__file__)+"/data/%s关键词数据导出数量%d_%s.xlsx"%(current_date,len(list_row)-size_pop,getCurrent_date("%Y-%m-%d_%H%M%S")) df.to_excel(filename,columns=list_df_columns1,index=False) host = "smtp.exmail.qq.com" username = "vip@bidizhaobiao.com" password = "Biaoxun66-" receivers = ["1175730271@qq.com","747012698@qq.com","995116318@qq.com"] receivers = ["1175730271@qq.com"] attachs = [filename] sendEmail(host,username,password,receivers,attachs=attachs) def export4_by_project(self): def getRowData(df_data,rows,dict_channel,set_columns1,list_df_column1): # dict_filter,dict_update = getFixData() ots_client = getConnect_ots() _index = 0 for row in rows: _index += 1 item = {} _dict = row keys = row["keyword"] log("================") log(keys) docid = row.get("docids","") log(docid) try: docid_0 = docid.split(",")[0] except Exception as e: continue log(docid) log(_dict.get("doctitle","")+_dict.get("doctextcon","")+_dict.get("attachmenttextcon","")) _keyword = str(list(set([ i for i in re.findall("|".join([re.escape(i) for i in keys if i!='']),_dict.get("doctitle","")+_dict.get("doctextcon","")+_dict.get("attachmenttextcon","")) if i!='' ]))) log(_keyword) product_attrs = [{}] # for doc in docid.split(","): # _q = BoolQuery(must_queries=[TermQuery("docid",int(doc))]) # r,n,t,_ = ots_client.search("document","document_index",SearchQuery(_q),columns_to_get=ColumnsToGet(["extract_json",ColumnReturnType.SPECIFIED])) # r = getRow_ots(r) # if len(r)>0: # _extract = json.loads(r[0].get("extract_json","{}")) # if len(set(keys)&set(["AR一体机","一体机","短焦投影仪","教学一体机","云桌面","录播","电子书包","电子班牌","智慧黑板"]))>0: # product_attrs = _extract.get("product_attrs",{"data":[{}]}).get("data") # else: # product_attrs = [{}] # if len(product_attrs)==0: # product_attrs = [{}] # win_tenderer = "" # bidding_budget = "" # win_bid_price = "" # sub_docs_json = json.loads(_dict.get("sub_docs_json","[]")) # for _doc in sub_docs_json: # if win_tenderer=="": # win_tenderer = _doc.get("win_tenderer","") # if bidding_budget=="": # bidding_budget= _doc.get("bidding_budget","") # if win_bid_price=="": # win_bid_price = _doc.get("win_bid_price","") _index1 = 0 # if _dict.get("docid") in dict_filter: # print("====filter") # continue # if _dict.get("docid") in dict_update: # print("====update") # win_bid_price = dict_update[_dict.get("docid")].get("win_bid_price","") # bidding_budget= dict_update[_dict.get("docid")].get("bidding_budget","") for _attrs in product_attrs: _index1 += 1 if re.search("运费",_attrs.get("product","")) is not None: if _index1==1: _attrs = {} else: continue set_dict_item_columns(set_columns1,list_df_column1,item,"序号",_index) # set_dict_item_columns(set_columns1,list_df_column1,item,"正文",_dict.get("doctextcon","")) # set_dict_item_columns(set_columns1,list_df_column1,item,"附件",_dict.get("attachmenttextcon","")) # set_dict_item_columns(set_columns1,list_df_column1,item,"项目名称",_dict.get("project_name","")) set_dict_item_columns(set_columns1,list_df_column1,item,"docid",docid) set_dict_item_columns(set_columns1,list_df_column1,item,"网站链接",'=HYPERLINK("http://www.bidizhaobiao.com/info-%s.html","查看公告")'%(str(docid))) set_dict_item_columns(set_columns1,list_df_column1,item,"省份",_dict.get("province")) set_dict_item_columns(set_columns1,list_df_column1,item,"城市",_dict.get("city")) set_dict_item_columns(set_columns1,list_df_column1,item,"地区",_dict.get("district")) set_dict_item_columns(set_columns1,list_df_column1,item,"公告时间",_dict.get("page_time")) set_dict_item_columns(set_columns1,list_df_column1,item,"招标公告时间",_dict.get("zhao_biao_page_time")) set_dict_item_columns(set_columns1,list_df_column1,item,"项目标题",_dict.get("doctitles")) set_dict_item_columns(set_columns1,list_df_column1,item,"采购单位",_dict.get("tenderee")) set_dict_item_columns(set_columns1,list_df_column1,item,"中标供应商",_dict.get("win_tenderer")) set_dict_item_columns(set_columns1,list_df_column1,item,"采购方式",_dict.get("bidway","")) set_dict_item_columns(set_columns1,list_df_column1,item,"招标采购方式","") set_dict_item_columns(set_columns1,list_df_column1,item,"采购系统","") set_dict_item_columns(set_columns1,list_df_column1,item,"产品名称",_attrs.get("product")) set_dict_item_columns(set_columns1,list_df_column1,item,"品牌",_attrs.get("brand","")) # set_dict_item_columns(set_columns1,list_df_column1,item,"规格型号",_attrs.get("specs","")) set_dict_item_columns(set_columns1,list_df_column1,item,"预算金额（元）",_dict.get("bidding_budget")) # set_dict_item_columns(set_columns1,list_df_column1,item,"招标预算金额（元）","") _quantity = "" _uniPrice = "" try: _quantity = int(re.sub("[^\d]","",_attrs.get("quantity","0"))) if _quantity==0: _quantity = "" except Exception as e: pass try: _uniPrice = float(_attrs.get("unitPrice","0")) if _uniPrice==0: _uniPrice = "" except Exception as e: pass set_dict_item_columns(set_columns1,list_df_column1,item,"数量",_quantity) set_dict_item_columns(set_columns1,list_df_column1,item,"单价（元）",_uniPrice) sumPrice = "" if _quantity!="" and _uniPrice!="": sumPrice = _quantity*_uniPrice set_dict_item_columns(set_columns1,list_df_column1,item,"总价（元）",sumPrice) set_dict_item_columns(set_columns1,list_df_column1,item,"中标金额（元）",_dict.get("win_bid_price")) set_dict_item_columns(set_columns1,list_df_column1,item,"关键词",_keyword) for k,v in item.items(): if k not in df_data: df_data[k] = [] df_data[k].append(v) def filterRow(list_row,key="docid"): set_id = set() _filter_row = [] for row in list_row: _v = row.get(key,"") if _v in set_id: continue set_id.add(_v) _filter_row.append(row) return _filter_row def fixContactPerson(df_data,list_df_columns,get_legal_person=False): set_enterprise = set() if len(df_data.keys())>0: for _tenderee,_win_tenderer in zip(df_data["采购单位"],df_data["中标供应商"]): set_enterprise.add(_tenderee) set_enterprise.add(_win_tenderer) if "" in set_enterprise: set_enterprise.remove("") if None in set_enterprise: set_enterprise.remove(None) dict_enterprise = getDictEnterprise(list(set_enterprise),columns_to_get = ["procurement_system"]) # conn = getConnection_oracle() # cursor = conn.cursor() if len(set_enterprise)>0: for _i in range(len(df_data["采购单位"])): _enterprise_name = df_data["采购单位"][_i] # if df_data["招标联系人电话"][_i]=="": # contacts = dict_enterprise.get(_enterprise_name,{}).get("contacts") # if contacts is not None: # _person,_phone = getOneContact(contacts) # df_data["招标联系人"][_i] = _person # df_data["招标联系人电话"][_i] = _phone df_data["采购系统"][_i] = dict_enterprise.get(_enterprise_name,{}).get("procurement_system","") def fixZhaobiao_page_time(df_data): ots_client = getConnect_ots() for i in range(len(df_data["docid"])): _docid = df_data["docid"][i] bool_query = BoolQuery(must_queries=[TermQuery("docids",_docid)]) rows,next_token,total_count,is_all_succeed = ots_client.search("project2","project2_index", SearchQuery(bool_query,limit=1), ColumnsToGet(["zhao_biao_page_time","bidway","bidding_budget"],ColumnReturnType.SPECIFIED)) list_data = getRow_ots(rows) if len(list_data)>0: df_data["招标公告时间"][i] = list_data[0].get("zhao_biao_page_time","") df_data["招标采购方式"][i] = list_data[0].get("bidway","") new_bidding_budget = list_data[0].get("bidding_budget",0) if df_data["预算金额（元）"][i]=="" or float(df_data["预算金额（元）"][i])==0: if df_data["中标金额（元）"][i]!="" and new_bidding_budget>=float(df_data["中标金额（元）"][i]): df_data["预算金额（元）"][i] = new_bidding_budget # df_data["招标预算金额（元）"][i] = list_data[0].get("bidding_budget","") def popRows(df_data): list_pop = [] set_docid = set() for _c in range(len(df_data["采购系统"])): if df_data["采购系统"][_c]!="教育系统" or df_data["中标金额（元）"][_c]=="": list_pop.append(_c) set_docid.add(df_data["docid"][_c]) list_pop.reverse() for k,v in df_data.items(): for _p in list_pop: v.pop(_p) _index = 0 set_docid1 = set() for _i in range(len(df_data["序号"])): _docid = df_data["docid"][_i] if _docid not in set_docid1: _index += 1 set_docid1.add(_docid) df_data["序号"][_i] = _index return len(set_docid) df = pd.read_excel("20220927v1.4.xlsx",sheetname=1) list_search_dict = [] for title_kw,content_kw,title_rm,content_rm in zip(df["标题采集关键词"],df["全文采集关键词"],df["标题排除词"],df["全文排除词"]): if not isinstance(title_kw,str): title_kw = "" if not isinstance(content_kw,str): content_kw = "" if not isinstance(title_rm,str): title_rm = "" if not isinstance(content_rm,str): content_rm = "" _dict = {"title_kw":re.split("\s|,|，",str(title_kw)), "content_kw":re.split("\s|,|，",str(content_kw)), "title_rm":re.split("\s|,|，",str(title_rm)), "content_rm":re.split("\s|,|，",str(content_rm))} list_search_dict.append(_dict) columns = ["docids","doctitles","product","province","bidway","city","district","zhao_biao_page_time","page_time","industry","info_type","tenderee","bidding_budget","project_code","project_name","win_tenderer","win_bid_price","tenderee_contact","tenderee_phone","agency","agency_contact","agency_phone","uuid","time_bidclose","web_source_no","web_source_name","service_time","person_review","time_get_file_start","time_get_file_end","time_earnest_money_start","time_earnest_money_end"] current_date = time.strftime("%Y-%m-%d",time.localtime(time.mktime(time.localtime()))) last_date = timeAdd(current_date,-1) current_date = "2022-10-14" last_date = "2023-05-07" dict_channel = getDict_docchannel() list_mq = [] list_query = [] for _d in list_search_dict: print(_d) kw_should_q = [] list_keys = [] title_kw = _d.get("title_kw",[]) if len(title_kw)>0: kw_should_q.append(generateBoolShouldQuery(["doctitles"],title_kw,MatchPhraseQuery)) list_keys.extend(title_kw) content_kw = _d.get("content_kw",[]) if len(content_kw)>0: kw_should_q.append(generateBoolShouldQuery(["doctextcon","attachmenttextcon"],content_kw,MatchPhraseQuery)) list_keys.extend(content_kw) rm_should_q = [] title_rm = _d.get("title_rm",[]) if len(title_rm)>0: rm_should_q.append(generateBoolShouldQuery(["doctitles"],title_rm,MatchPhraseQuery)) content_rm = _d.get("content_rm",[]) if len(content_rm)>0: rm_should_q.append(generateBoolShouldQuery(["doctextcon","attachmenttextcon"],content_rm,MatchPhraseQuery)) _query = BoolQuery(must_queries=[ # generateBoolShouldQuery(["docchannel"],[101],TermQuery), # RangeQuery("page_time",last_date,current_date), RangeQuery("page_time",current_date,last_date), RangeQuery("status",201,300,True,True), # TermQuery("docid",263568527), # generateBoolShouldQuery(["doctitle","doctextcon","attachmenttextcon"],list_keys,MatchPhraseQuery), BoolQuery(should_queries=kw_should_q), # # generateBoolShouldQuery(["province"],list_province,WildcardQuery), # # RangeQuery("crtime","%s"%last_date,"%s"%current_date) ], must_not_queries=rm_should_q) list_query.append({"query":_query,"limit":50000,"keyword":list_keys}) def getFixData(): filename = "C:\\Users\\Administrator\\Desktop\\视源数据清洗.xlsx" dict_filter = {} dict_update = {} df = pd.read_excel(filename,0) for _docid,bidding_budget,win_bid_price,is_dup,is_fb in zip(df["docid"],df["检查招标金额"],df["中标金额（元）"],df["是否重复"],df["是否废标"]): if str(is_dup) in ("是","1") or str(is_fb) in ("是","1"): dict_filter[_docid] = 1 else: dict_update[_docid] = {"docid":_docid, "bidding_budget":bidding_budget, "win_bid_price":win_bid_price} df = pd.read_excel(filename,1) for _docid,bidding_budget,win_bid_price,is_dup,is_fb in zip(df["docid"],df["检查招标金额"],df["中标金额（元）"],df["是否重复"],df["是否废标"]): if str(is_dup) in ("是","1") or str(is_fb) in ("是","1"): dict_filter[_docid] = 1 else: dict_update[_docid] = {"docid":_docid, "bidding_budget":bidding_budget, "win_bid_price":win_bid_price} return dict_filter,dict_update list_row = getDocument(list_query,columns,table_name="project2",table_index="project2_index") list_row = filterRow(list_row,key="uuid") print("list_row",len(list_row)) set_columns1 = set() list_df_columns1 = [] df_data = {} getRowData(df_data,list_row,dict_channel,set_columns1,list_df_columns1) print("len df_data",len(df_data["docid"])) fixContactPerson(df_data,list_df_columns1) # fixZhaobiao_page_time(df_data) # size_pop = popRows(df_data) size_pop = 0 print("size_pop",size_pop) df = pd.DataFrame(df_data) filename = os.path.dirname(__file__)+"/data/%s关键词数据导出数量%d_%s.xlsx"%(current_date,len(list_row)-size_pop,getCurrent_date("%Y-%m-%d_%H%M%S")) df.to_excel(filename,columns=list_df_columns1,index=False) host = "smtp.exmail.qq.com" username = "vip@bidizhaobiao.com" password = "Biaoxun66-" receivers = ["1175730271@qq.com","747012698@qq.com","995116318@qq.com"] receivers = ["1175730271@qq.com"] attachs = [filename] sendEmail(host,username,password,receivers,attachs=attachs) import json def getUserInfo(self,code,userid): data = { "codeStr": code, "userId": userid, } auth = {'Content-Type': 'application/json',"Authorization": "Bearer eyJhbGciOiJSUzI1NiJ9.eyJpc3MiOiJCWEtDX1VOSVRFX0FQSSIsInN1YiI6IjIwIiwiZGF0YSI6eyJkZXZpY2VUeXBlIjoiMjAiLCJyb2xlIjoiMjAiLCJpZCI6ImJ4a2Mtd29ya2JlbmNoIn0sImV4cCI6MTk2NjY5MjE4MSwiaWF0IjoxNjUxMDQ0MTgxfQ.lFurpoMOaVmCH3GKmJ_cqrseuSaEZJBndp8PE4QpjFY6R2mfXh5e96zn_Ma3qVkp-NKAlpA1nRYl1y08xkzj07KEx4HO_Nh6v3sfGwnDyRUz35SW3yr1fhvnbh5hEpnoCVJnFQfoMXFfn780VuOcKd01lNUjABFbSLvypDv8p-gJzkrE7z5YB53tZ_lGm_dbzKihTBjG1sBYEKJT3ekYz5Px_n-lw05IkUdbckE7n6Xj7PRPTaEjzoc3PF_pgESsdcTPSDhJlLR8x63YCqbmWy6ydhwnlEQE5rHIFA2Q5VhIxUcT7fXUBZNGmzRgaqxB4dIg2369IhpeBAJ2TQ63qg"} r = requests.patch("https://unite.bidizhaobiao.com/api/v1/authorization/services",json=data,headers=auth) if r.status_code==200: return r.status_code,json.loads(r.content.decode("utf8")) return r.status_code,"" def export_15824381998(): def removeData(df_data): list_remove_index = [] list_c = df_data.get("招标人采购系统",[]) for _c_i in range(len(list_c)): if list_c[_c_i]=="企业采购系统": list_remove_index.append(_c_i) list_remove_index.reverse() print(list_remove_index) for k,v in df_data.items(): for _rc in list_remove_index: v.pop(_rc) for k,v in df_data.items(): v = v[:500] def getRowData_zb(df_data,rows,set_line,list_keyword,dict_channel,dumplicate): dict_line = {} # list_data = getRow_ots(rows) _index = 0 rows.sort(key=lambda x:x.get("docid",0),reverse=True) set_col = set() df_columns = [] for row in rows: _index += 1 item = {} _dict = row bidding_budget = 0 sub_docs = json.loads(_dict.get("sub_docs_json","[]")) for doc in sub_docs: if doc.get("bidding_budget",0)>0: bidding_budget = doc.get("bidding_budget",0) set_dict_item_columns(set_col,df_columns,item,"省份",_dict.get("province","")) set_dict_item_columns(set_col,df_columns,item,"城市",_dict.get("city","")) set_dict_item_columns(set_col,df_columns,item,"公告类别",dict_channel.get(_dict.get("docchannel",""),"")) set_dict_item_columns(set_col,df_columns,item,"发布时间",_dict.get("page_time","")) set_dict_item_columns(set_col,df_columns,item,"公告标题",_dict.get("doctitle","")) set_dict_item_columns(set_col,df_columns,item,"采购内容",_dict.get("product","")) set_dict_item_columns(set_col,df_columns,item,"采购方式",_dict.get("bidway","")) set_dict_item_columns(set_col,df_columns,item,"项目金额",bidding_budget) set_dict_item_columns(set_col,df_columns,item,"招标单位",_dict.get("tenderee","")) set_dict_item_columns(set_col,df_columns,item,"招标联系人",_dict.get("tenderee_contact","")) set_dict_item_columns(set_col,df_columns,item,"招标联系人电话",_dict.get("tenderee_phone","")) set_dict_item_columns(set_col,df_columns,item,"代理单位",_dict.get("agency","")) set_dict_item_columns(set_col,df_columns,item,"代理联系人",_dict.get("agency_contact","")) set_dict_item_columns(set_col,df_columns,item,"代理联系人电话",_dict.get("agency_phone","")) set_dict_item_columns(set_col,df_columns,item,"获取文件截止时间",_dict.get("time_get_file_end","")) set_dict_item_columns(set_col,df_columns,item,"开标时间",_dict.get("time_bidopen","")) set_dict_item_columns(set_col,df_columns,item,"链接","http://www.bidizhaobiao.com/info-%s.html"%str(_dict.get("docid",""))) for k,v in item.items(): if k not in df_data: df_data[k] = [] df_data[k].append(v) return df_columns def getRowData_yc(df_data,rows,set_line,list_keyword,dict_channel,dumplicate): dict_line = {} # list_data = getRow_ots(rows) _index = 0 rows.sort(key=lambda x:x.get("docid",0),reverse=True) set_col = set() df_columns = [] for row in rows: _index += 1 item = {} _dict = row _type = _dict.get("type",0) if _type==0: _type = "周期预测" elif _type==1: _type = "采购意向" elif _type==2: _type = "到期预测" else: _type = "废标重招" set_dict_item_columns(set_col,df_columns,item,"省份",_dict.get("province","")) set_dict_item_columns(set_col,df_columns,item,"城市",_dict.get("city","")) set_dict_item_columns(set_col,df_columns,item,"预测类型",_type) set_dict_item_columns(set_col,df_columns,item,"预计开始时间",_dict.get("may_begin","")) set_dict_item_columns(set_col,df_columns,item,"预计结束时间",_dict.get("may_end","")) set_dict_item_columns(set_col,df_columns,item,"采购内容",_dict.get("product","")) set_dict_item_columns(set_col,df_columns,item,"上次采购时间",_dict.get("last_page_time","")) set_dict_item_columns(set_col,df_columns,item,"招标单位",_dict.get("tenderee","")) set_dict_item_columns(set_col,df_columns,item,"招标联系人",_dict.get("tenderee_contact","")) set_dict_item_columns(set_col,df_columns,item,"招标联系人电话",_dict.get("tenderee_phone","")) set_dict_item_columns(set_col,df_columns,item,"代理单位",_dict.get("agency","")) set_dict_item_columns(set_col,df_columns,item,"代理联系人",_dict.get("agency_contact","")) set_dict_item_columns(set_col,df_columns,item,"代理联系人电话",_dict.get("agency_phone","")) for k,v in item.items(): if k not in df_data: df_data[k] = [] df_data[k].append(v) return df_columns def getRowData_win(df_data,rows,set_line,list_keyword,dict_channel,dumplicate): dict_line = {} # list_data = getRow_ots(rows) _index = 0 rows.sort(key=lambda x:x.get("docid",0),reverse=True) set_col = set() df_columns = [] for row in rows: _index += 1 item = {} _dict = row bidding_budget = 0 sub_docs = json.loads(_dict.get("sub_docs_json","[]")) zbr = "" zbje = "" for doc in sub_docs: if doc.get("bidding_budget",0)>0: bidding_budget = doc.get("bidding_budget",0) if doc.get("win_tenderer") is not None: zbr += str(doc.get("win_tenderer"))+"\r\n" zbje += str(doc.get("win_bid_price",0))+"\r\n" if doc.get("second_tenderer") is not None: zbr += str(doc.get("second_tenderer"))+"\r\n" zbje += str(doc.get("second_bid_price",0))+"\r\n" if doc.get("third_tenderer") is not None: zbr += str(doc.get("third_tenderer"))+"\r\n" zbje += str(doc.get("third_bid_price",0))+"\r\n" set_dict_item_columns(set_col,df_columns,item,"省份",_dict.get("province","")) set_dict_item_columns(set_col,df_columns,item,"城市",_dict.get("city","")) set_dict_item_columns(set_col,df_columns,item,"公告类别",dict_channel.get(_dict.get("docchannel",""),"")) set_dict_item_columns(set_col,df_columns,item,"发布时间",_dict.get("page_time","")) set_dict_item_columns(set_col,df_columns,item,"公告标题",_dict.get("doctitle","")) set_dict_item_columns(set_col,df_columns,item,"采购内容",_dict.get("product","")) set_dict_item_columns(set_col,df_columns,item,"采购方式",_dict.get("bidway","")) set_dict_item_columns(set_col,df_columns,item,"项目金额",bidding_budget) set_dict_item_columns(set_col,df_columns,item,"招标单位",_dict.get("tenderee","")) set_dict_item_columns(set_col,df_columns,item,"招标联系人",_dict.get("tenderee_contact","")) set_dict_item_columns(set_col,df_columns,item,"招标联系人电话",_dict.get("tenderee_phone","")) set_dict_item_columns(set_col,df_columns,item,"代理单位",_dict.get("agency","")) set_dict_item_columns(set_col,df_columns,item,"代理联系人",_dict.get("agency_contact","")) set_dict_item_columns(set_col,df_columns,item,"代理联系人电话",_dict.get("agency_phone","")) set_dict_item_columns(set_col,df_columns,item,"获取文件截止时间",_dict.get("time_get_file_end","")) set_dict_item_columns(set_col,df_columns,item,"开标时间",_dict.get("time_bidopen","")) set_dict_item_columns(set_col,df_columns,item,"中标公司",zbr) set_dict_item_columns(set_col,df_columns,item,"中标金额",zbje) set_dict_item_columns(set_col,df_columns,item,"链接","http://www.bidizhaobiao.com/info-%s.html"%str(_dict.get("docid",""))) for k,v in item.items(): if k not in df_data: df_data[k] = [] df_data[k].append(v) return df_columns def fixContactPerson(df_data,list_df_columns,get_legal_person=False): set_enterprise = set() if len(df_data.keys())>0: for _tenderee,_agency in zip(df_data["招标单位"],df_data["代理单位"]): set_enterprise.add(_tenderee) set_enterprise.add(_agency) if "" in set_enterprise: set_enterprise.remove("") if None in set_enterprise: set_enterprise.remove(None) dict_enterprise = getDictEnterprise(list(set_enterprise),columns_to_get = ["procurement_system","company_org_type","reg_capital","actual_capital","contacts","estiblish_time","social_staff_num","zhong_biao_number","tou_biao_number","credit_code","legal_person_name","phone_number"]) # conn = getConnection_oracle() # cursor = conn.cursor() if len(set_enterprise)>0: for _i in range(len(df_data["招标单位"])): _enterprise_name = df_data["招标单位"][_i] if df_data["招标联系人电话"][_i]=="": contacts = dict_enterprise.get(_enterprise_name,{}).get("contacts") if contacts is not None: _person,_phone = getOneContact(contacts) df_data["招标联系人"][_i] = _person df_data["招标联系人电话"][_i] = _phone _enterprise_name = df_data["代理单位"][_i] if df_data["代理联系人电话"][_i]=="": contacts = dict_enterprise.get(_enterprise_name,{}).get("contacts") if contacts is not None: _person,_phone = getOneContact(contacts) df_data["代理联系人"][_i] = _person df_data["代理联系人电话"][_i] = _phone # _enterprise_name = df_data["中标单位"][_i] # if get_legal_person: # _person = dict_enterprise.get(_enterprise_name,{}).get("legal_person_name","") # _phone = dict_enterprise.get(_enterprise_name,{}).get("phone_number","") # if len(_phone)==11 and _phone[0]=="1": # df_data["中标单位联系人"][_i] = _person # df_data["中标单位联系电话"][_i] = _phone # else: # if df_data["中标单位联系电话"][_i]=="": # contacts = dict_enterprise.get(_enterprise_name,{}).get("contacts") # if contacts is not None: # _person,_phone = getOneContact(contacts,mobile_only=True) # df_data["中标单位联系人"][_i] = _person # df_data["中标单位联系电话"][_i] = _phone log("start export 15824381998:>>>>>>>>>>>>>>>") current_date = getCurrent_date(format="%Y-%m-%d") last_date = timeAdd(current_date,-2) start_time = time.strftime("%Y-%m-%d",time.localtime(time.mktime(time.localtime())-7*24*60*60)) if current_date<="2025-05-01": for i in range(10): try: # start_time='2022-07-22' # current_date = '2022-07-28' log("start exporting export2:=================") # columns = ["doctitle","doctextcon","docchannel","product","province","bidway","city","district","page_time","industry","info_type","tenderee","project_code","project_name","sub_docs_json","tenderee_contact","tenderee_phone","agency","agency_contact","agency_phone","uuid","time_bidclose"] dict_channel = getDict_docchannel() list_query = [] str_keyword = ''' 光伏组件 ''' list_keyword = splitIntoList(str_keyword,"[\s\n、，,]") str_not_keyword = ''' 清洗机器人 ''' list_not_key = splitIntoList(str_not_keyword,"[\s\n、，,]") tenderee_keywrod = "医院、大学、高校、高中" list_t_key = splitIntoList(tenderee_keywrod,"[\s\n、，,]") log(str(list_keyword)) columns = ["doctitle","docchannel","product","province","bidway","city","district","page_time","industry","info_type","tenderee","project_code","project_name","sub_docs_json","tenderee_contact","tenderee_phone","agency","agency_contact","agency_phone","uuid","time_get_file_end","time_bidopen"] bool_query = BoolQuery(must_queries=[ generateBoolShouldQuery(["doctitle"],list_keyword,MatchPhraseQuery), generateBoolShouldQuery(["docchannel"],[51,52,102,103,104,105,114],TermQuery), RangeQuery("crtime",last_date+" 21:00:00",current_date+" 21:00:00",True,True), RangeQuery("status",201,300,True,True), # TermQuery("procurement_system","公安系统"), # generateBoolShouldQuery(["province"],["湖南"],TermQuery), # generateBoolShouldQuery(["tenderee"],list_t_key,WildcardQuery) # generateBoolShouldQuery(["docchannel"],[101,118,119],TermQuery), ], must_not_queries=[ generateBoolShouldQuery(["doctitle","doctextcon","attachmenttextcon"],list_not_key,MatchPhraseQuery), ] ) list_row = getDocument([{"query":bool_query,"limit":500}],columns,thread_count=1) log("get document %d rows"%len(list_row)) df_data = {} set_line = set() # list_row = filterRow(list_row) df_columns = getRowData_zb(df_data,list_row,set_line,list_keyword,dict_channel,True) fixContactPerson(df_data,df_columns) df = pd.DataFrame(df_data) df_data_filter = {} for c in df_columns: if c not in df_data_filter: df_data_filter[c] = [] for tenderee_i in range(len(df_data["招标单位"])): if df_data["招标单位"][tenderee_i] in ("国家电力投资集团有限公司","中国大唐集团有限公司"): for c in df_columns: df_data_filter[c].append(df_data[c][tenderee_i]) df_f = pd.DataFrame(df_data_filter) time_end = timeAdd(current_date,60) columns = ["project_name","type","province","city","bidding_budget","city","demand","last_page_time"] bool_query = BoolQuery(must_queries=[ generateBoolShouldQuery(["demand","product"],list_keyword,MatchPhraseQuery), RangeQuery("may_end",range_from=current_date), RangeQuery("may_end",range_to=time_end), # TermQuery("procurement_system","公安系统"), # generateBoolShouldQuery(["province"],["湖南"],TermQuery), # generateBoolShouldQuery(["tenderee"],list_t_key,WildcardQuery) # generateBoolShouldQuery(["docchannel"],[101,118,119],TermQuery), ], must_not_queries=[ generateBoolShouldQuery(["demand","product"],list_not_key,MatchPhraseQuery), ] ) list_row = getDocument([{"query":bool_query,"limit":500}],columns,table_name="preproject",table_index="preproject_index",thread_count=1,sort_column="may_end") log("get document %d rows"%len(list_row)) df_data = {} set_line = set() # list_row = filterRow(list_row) df_columns_yc = getRowData_yc(df_data,list_row,set_line,list_keyword,dict_channel,True) fixContactPerson(df_data,df_columns_yc) df1 = pd.DataFrame(df_data) df_data_filter = {} for c in df_columns_yc: if c not in df_data_filter: df_data_filter[c] = [] for tenderee_i in range(len(df_data["招标单位"])): if df_data["招标单位"][tenderee_i] in ("国家电力投资集团有限公司","中国大唐集团有限公司"): for c in df_columns_yc: if c not in df_data_filter: df_data_filter[c] = [] df_data_filter[c].append(df_data[c][tenderee_i]) df1_f = pd.DataFrame(df_data_filter) filename = os.path.dirname(__file__)+"/data/%s年%s招标数据导出%s.xlsx"%(start_time[:4],current_date,getCurrent_date(format="%Y-%m-%d_%H%M%S")) with pd.ExcelWriter(filename) as writer: df.to_excel(writer,sheet_name="招标数据",columns=df_columns if not df.empty else None) df_f.to_excel(writer,sheet_name="特定甲方招标数据",columns=df_columns if not df_f.empty else None) df1.to_excel(writer,sheet_name="项目预测",columns=df_columns_yc[:-3] if not df1.empty else None) df1_f.to_excel(writer,sheet_name="特定甲方项目预测",columns=df_columns_yc[:-3] if not df1_f.empty else None) log(str(filename)) host = "smtp.exmail.qq.com" username = "vip@bidizhaobiao.com" password = "Biaoxun66-" receivers = ["1175730271@qq.com","565748324@qq.com","1396488964@qq.com"] # receivers = ["1175730271@qq.com"] attachs = [filename] sendEmail(host,username,password,receivers,attachs=attachs) df_data_filter = {} for c in df_columns: if c not in df_data_filter: df_data_filter[c] = [] for tenderee_i in range(len(df_data["招标单位"])): if df_data["招标单位"][tenderee_i] in ("国家电力投资集团有限公司","国家能源投资集团有限责任公司","国家能源集团龙源阿里新能源（阿里）有限公司"): for c in df_columns: df_data_filter[c].append(df_data[c][tenderee_i]) df_f = pd.DataFrame(df_data_filter) df_data_filter1 = {} for c in df_columns_yc: if c not in df_data_filter1: df_data_filter1[c] = [] for tenderee_i in range(len(df_data["招标单位"])): if df_data["招标单位"][tenderee_i] in ("国家电力投资集团有限公司","国家能源投资集团有限责任公司","国家能源集团龙源阿里新能源（阿里）有限公司"): for c in df_columns_yc: if c not in df_data_filter1: df_data_filter1[c] = [] df_data_filter1[c].append(df_data[c][tenderee_i]) df1_f1 = pd.DataFrame(df_data_filter1) filename = os.path.dirname(__file__)+"/data/%s年%s招标数据导出%s.xlsx"%(start_time[:4],current_date,getCurrent_date(format="%Y-%m-%d_%H%M%S")) with pd.ExcelWriter(filename) as writer: df_f.to_excel(writer,sheet_name="特定甲方招标数据",columns=df_columns if not df_f.empty else None) df1_f1.to_excel(writer,sheet_name="特定甲方项目预测",columns=df_columns_yc[:-3] if not df1_f.empty else None) log(str(filename)) host = "smtp.exmail.qq.com" username = "vip@bidizhaobiao.com" password = "Biaoxun66-" receivers = ["450604061@qq.com"] # receivers = ["1175730271@qq.com"] attachs = [filename] sendEmail(host,username,password,receivers,attachs=attachs) columns = ["doctitle","docchannel","product","province","bidway","city","district","page_time","industry","info_type","tenderee","project_code","project_name","sub_docs_json","tenderee_contact","tenderee_phone","agency","agency_contact","agency_phone","uuid","time_get_file_end","time_bidopen"] bool_query = BoolQuery(must_queries=[ generateBoolShouldQuery(["doctitle"],list_keyword,MatchPhraseQuery), generateBoolShouldQuery(["docchannel"],[101,118,119,120,121,122],TermQuery), RangeQuery("crtime",last_date+" 21:00:00",current_date+" 21:00:00",True,True), RangeQuery("status",201,300,True,True), # TermQuery("procurement_system","公安系统"), # generateBoolShouldQuery(["province"],["湖南"],TermQuery), # generateBoolShouldQuery(["tenderee"],list_t_key,WildcardQuery) # generateBoolShouldQuery(["docchannel"],[101,118,119],TermQuery), ], must_not_queries=[ generateBoolShouldQuery(["doctitle","doctextcon","attachmenttextcon"],list_not_key,MatchPhraseQuery), ] ) list_row = getDocument([{"query":bool_query,"limit":500}],columns,thread_count=1) log("get document %d rows"%len(list_row)) df_data = {} set_line = set() # list_row = filterRow(list_row) df_columns = getRowData_win(df_data,list_row,set_line,list_keyword,dict_channel,True) fixContactPerson(df_data,df_columns) df = pd.DataFrame(df_data) # 调整行高 # for i in range(1, len(df) + 1): # df.style.set_properties(max_col=100, min_col=1, max_row=i, min_row=i, h=20, wrap_text=True) # 设置行高为20，自动换行 styled_df = (df.style .set_properties(**{'text-align': 'center', 'white-space': 'pre-line'}) .set_table_styles([{'selector': 'td', 'props': [('text-align', 'center')]}])) filename = os.path.dirname(__file__)+"/data/%s年%s中标数据导出%s.xlsx"%(start_time[:4],current_date,getCurrent_date(format="%Y-%m-%d_%H%M%S")) with pd.ExcelWriter(filename,engine="openpyxl") as writer: styled_df.to_excel(writer,columns = df_columns,index=False) adjust_excel(filename,filename,columns=["A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R"]) host = "smtp.exmail.qq.com" username = "vip@bidizhaobiao.com" password = "Biaoxun66-" receivers = ["1175730271@qq.com","565748324@qq.com","1396488964@qq.com","1141385052@qq.com","1713739820@qq.com"] # receivers = ["1175730271@qq.com"] attachs = [filename] sendEmail(host,username,password,receivers,attachs=attachs) break except Exception as e: traceback.print_exc() def export_13510123669(): def removeData(df_data): list_remove_index = [] list_c = df_data.get("招标人采购系统",[]) for _c_i in range(len(list_c)): if list_c[_c_i]=="企业采购系统": list_remove_index.append(_c_i) list_remove_index.reverse() print(list_remove_index) for k,v in df_data.items(): for _rc in list_remove_index: v.pop(_rc) for k,v in df_data.items(): v = v[:500] def getRowData_zb(df_data,rows,set_line,list_keyword,dict_channel,dumplicate): dict_line = {} # list_data = getRow_ots(rows) _index = 0 rows.sort(key=lambda x:x.get("docid",0),reverse=True) set_col = set() df_columns = [] for row in rows: _index += 1 item = {} _dict = row bidding_budget = 0 sub_docs = json.loads(_dict.get("sub_docs_json","[]")) zbr = "" zbje = 0 win_tenderer_manager = "" win_tenderer_phone = "" for doc in sub_docs: if doc.get("bidding_budget",0)>0: bidding_budget = doc.get("bidding_budget",0) if doc.get("win_tenderer") is not None: zbr = str(doc.get("win_tenderer","")) zbje = doc.get("win_bid_price",0) win_tenderer_manager = str(doc.get("win_tenderer_manager","")) win_tenderer_phone = str(doc.get("win_tenderer_phone","")) doc_type = "" if dict_channel.get(_dict.get("docchannel",""),"")[:2] in ("中标","合同","候选","废标","开标","验收"): doc_type = "中标" else: doc_type = "招标" set_dict_item_columns(set_col,df_columns,item,"序号",_index) set_dict_item_columns(set_col,df_columns,item,"省份",_dict.get("province","未知")) set_dict_item_columns(set_col,df_columns,item,"城市",_dict.get("city","未知")) set_dict_item_columns(set_col,df_columns,item,"公告类型",doc_type) set_dict_item_columns(set_col,df_columns,item,"公告名称",_dict.get("doctitle","")) set_dict_item_columns(set_col,df_columns,item,"项目类型",'') set_dict_item_columns(set_col,df_columns,item,"项目编号",_dict.get("project_code")) set_dict_item_columns(set_col,df_columns,item,"发布时间",_dict.get("page_time","")) set_dict_item_columns(set_col,df_columns,item,"信息类型","%s-%s"%(dict_channel.get(_dict.get("docchannel",""),""),_dict.get("bidway",""))) set_dict_item_columns(set_col,df_columns,item,"报名开始时间",_dict.get("time_registration_start","")) set_dict_item_columns(set_col,df_columns,item,"报名截止时间",_dict.get("time_registration_end","")) set_dict_item_columns(set_col,df_columns,item,"投标开始时间",_dict.get("time_bidstart","")) set_dict_item_columns(set_col,df_columns,item,"投标结束时间",_dict.get("time_bidopen","")) set_dict_item_columns(set_col,df_columns,item,"预算金额（万元）",bidding_budget/10000) set_dict_item_columns(set_col,df_columns,item,"成交金额（万元）",zbje/10000) set_dict_item_columns(set_col,df_columns,item,"招标单位",_dict.get("tenderee","")) set_dict_item_columns(set_col,df_columns,item,"招标单位所在地","") set_dict_item_columns(set_col,df_columns,item,"招标联系人",_dict.get("tenderee_contact","")) set_dict_item_columns(set_col,df_columns,item,"招标联系人电话",_dict.get("tenderee_phone","")) set_dict_item_columns(set_col,df_columns,item,"招标联系人1","") set_dict_item_columns(set_col,df_columns,item,"招标联系人电话1","") set_dict_item_columns(set_col,df_columns,item,"招标联系人2","") set_dict_item_columns(set_col,df_columns,item,"招标联系人电话2","") set_dict_item_columns(set_col,df_columns,item,"招标联系人3","") set_dict_item_columns(set_col,df_columns,item,"招标联系人电话3","") set_dict_item_columns(set_col,df_columns,item,"代理单位",_dict.get("agency","")) set_dict_item_columns(set_col,df_columns,item,"代理单位所在地","") set_dict_item_columns(set_col,df_columns,item,"代理联系人",_dict.get("agency_contact","")) set_dict_item_columns(set_col,df_columns,item,"代理联系人电话",_dict.get("agency_phone","")) set_dict_item_columns(set_col,df_columns,item,"代理联系人1","") set_dict_item_columns(set_col,df_columns,item,"代理联系人电话1","") set_dict_item_columns(set_col,df_columns,item,"代理联系人2","") set_dict_item_columns(set_col,df_columns,item,"代理联系人电话2","") set_dict_item_columns(set_col,df_columns,item,"代理联系人3","") set_dict_item_columns(set_col,df_columns,item,"代理联系人电话3","") set_dict_item_columns(set_col,df_columns,item,"中标单位",zbr) set_dict_item_columns(set_col,df_columns,item,"中标单位所在地","") set_dict_item_columns(set_col,df_columns,item,"项目中标联系人",win_tenderer_manager) set_dict_item_columns(set_col,df_columns,item,"项目中标联系电话",win_tenderer_phone) set_dict_item_columns(set_col,df_columns,item,"中标法人","") set_dict_item_columns(set_col,df_columns,item,"中标法人电话","") set_dict_item_columns(set_col,df_columns,item,"中标股东","") set_dict_item_columns(set_col,df_columns,item,"中标股东电话","") set_dict_item_columns(set_col,df_columns,item,"中标高管","") set_dict_item_columns(set_col,df_columns,item,"中标高管电话","") set_dict_item_columns(set_col,df_columns,item,"中标联系人1","") set_dict_item_columns(set_col,df_columns,item,"中标联系人电话1","") set_dict_item_columns(set_col,df_columns,item,"中标联系人2","") set_dict_item_columns(set_col,df_columns,item,"中标联系人电话2","") set_dict_item_columns(set_col,df_columns,item,"中标联系人3","") set_dict_item_columns(set_col,df_columns,item,"中标联系人电话3","") set_dict_item_columns(set_col,df_columns,item,"招标/采购内容",_dict.get("product","")) set_dict_item_columns(set_col,df_columns,item,"资质","") set_dict_item_columns(set_col,df_columns,item,"公告详情链接","http://www.bidizhaobiao.com/info-%s.html"%str(_dict.get("docid",""))) set_dict_item_columns(set_col,df_columns,item,"招投标分析（合作商）","") set_dict_item_columns(set_col,df_columns,item,"历史中标单位","") set_dict_item_columns(set_col,df_columns,item,"应对策略(作为填)","") for k,v in item.items(): if k not in df_data: df_data[k] = [] df_data[k].append(v) return df_columns def getRowData_yc(df_data,rows,set_line,list_keyword,dict_channel,dumplicate): dict_line = {} # list_data = getRow_ots(rows) _index = 0 rows.sort(key=lambda x:x.get("docid",0),reverse=True) set_col = set() df_columns = [] for row in rows: _index += 1 item = {} _dict = row _type = _dict.get("type",0) if _type==0: _type = "周期预测" elif _type==1: _type = "采购意向" elif _type==2: _type = "到期预测" else: _type = "废标重招" set_dict_item_columns(set_col,df_columns,item,"省份",_dict.get("province","")) set_dict_item_columns(set_col,df_columns,item,"城市",_dict.get("city","")) set_dict_item_columns(set_col,df_columns,item,"类型",'') set_dict_item_columns(set_col,df_columns,item,"预测类型",_type) set_dict_item_columns(set_col,df_columns,item,"项目类型","") set_dict_item_columns(set_col,df_columns,item,"预计开始时间",_dict.get("may_begin","")) set_dict_item_columns(set_col,df_columns,item,"预计结束时间",_dict.get("may_end","")) set_dict_item_columns(set_col,df_columns,item,"采购内容",_dict.get("product","")) set_dict_item_columns(set_col,df_columns,item,"上次采购时间",_dict.get("last_page_time","")) set_dict_item_columns(set_col,df_columns,item,"招标单位",_dict.get("tenderee","")) set_dict_item_columns(set_col,df_columns,item,"招标联系人",_dict.get("tenderee_contact","")) set_dict_item_columns(set_col,df_columns,item,"招标联系人电话",_dict.get("tenderee_phone","")) set_dict_item_columns(set_col,df_columns,item,"代理单位",_dict.get("agency","")) set_dict_item_columns(set_col,df_columns,item,"代理联系人",_dict.get("agency_contact","")) set_dict_item_columns(set_col,df_columns,item,"代理联系人电话",_dict.get("agency_phone","")) set_dict_item_columns(set_col,df_columns,item,"招投标分析（合作商）","") set_dict_item_columns(set_col,df_columns,item,"历史中标单位","") set_dict_item_columns(set_col,df_columns,item,"应对策略(作为填)","") for k,v in item.items(): if k not in df_data: df_data[k] = [] df_data[k].append(v) return df_columns def fixContactPerson(df_data,list_df_columns,get_legal_person=False): set_enterprise = set() if len(df_data.keys())>0: for _tenderee,_agency in zip(df_data["招标单位"],df_data["代理单位"]): set_enterprise.add(_tenderee) set_enterprise.add(_agency) if "" in set_enterprise: set_enterprise.remove("") if None in set_enterprise: set_enterprise.remove(None) dict_enterprise = getDictEnterprise(list(set_enterprise),columns_to_get = ["procurement_system","company_org_type","reg_capital","actual_capital","contacts","estiblish_time","social_staff_num","zhong_biao_number","tou_biao_number","credit_code","legal_person_name","phone_number"]) # conn = getConnection_oracle() # cursor = conn.cursor() if len(set_enterprise)>0: for _i in range(len(df_data["招标单位"])): _enterprise_name = df_data["招标单位"][_i] if df_data["招标联系人电话"][_i]=="": contacts = dict_enterprise.get(_enterprise_name,{}).get("contacts") if contacts is not None: _person,_phone = getOneContact(contacts) df_data["招标联系人"][_i] = _person df_data["招标联系人电话"][_i] = _phone df_data["项目类型"][_i] = dict_enterprise.get(_enterprise_name,{}).get("procurement_system") _enterprise_name = df_data["代理单位"][_i] if df_data["代理联系人电话"][_i]=="": contacts = dict_enterprise.get(_enterprise_name,{}).get("contacts") if contacts is not None: _person,_phone = getOneContact(contacts) df_data["代理联系人"][_i] = _person df_data["代理联系人电话"][_i] = _phone # _enterprise_name = df_data["中标单位"][_i] # if get_legal_person: # _person = dict_enterprise.get(_enterprise_name,{}).get("legal_person_name","") # _phone = dict_enterprise.get(_enterprise_name,{}).get("phone_number","") # if len(_phone)==11 and _phone[0]=="1": # df_data["中标单位联系人"][_i] = _person # df_data["中标单位联系电话"][_i] = _phone # else: # if df_data["中标单位联系电话"][_i]=="": # contacts = dict_enterprise.get(_enterprise_name,{}).get("contacts") # if contacts is not None: # _person,_phone = getOneContact(contacts,mobile_only=True) # df_data["中标单位联系人"][_i] = _person # df_data["中标单位联系电话"][_i] = _phone def fixContactPerson1(df_data,list_df_columns,get_legal_person=False): set_enterprise = set() if len(df_data.keys())>0: for _tenderee,_agency,_win in zip(df_data["招标单位"],df_data["代理单位"],df_data["中标单位"]): set_enterprise.add(_tenderee) set_enterprise.add(_agency) set_enterprise.add(_win) if "" in set_enterprise: set_enterprise.remove("") if None in set_enterprise: set_enterprise.remove(None) dict_enterprise = getDictEnterprise(list(set_enterprise),columns_to_get = ["procurement_system","company_org_type","reg_capital","actual_capital","contacts","estiblish_time","social_staff_num","zhong_biao_number","tou_biao_number","credit_code","legal_person_name","phone_number","reg_location","province","city"]) # print("dict_enterprise",dict_enterprise) # conn = getConnection_oracle() # cursor = conn.cursor() if len(set_enterprise)>0: for _i in range(len(df_data["招标单位"])): _enterprise_name = df_data["招标单位"][_i] contacts = dict_enterprise.get(_enterprise_name,{}).get("contacts") if contacts is not None: for _index in range(min(len(contacts),3)): contact_person,phone_no = contacts[_index].get("contact_person"),contacts[_index].get("phone_no") is_legal_person,is_manager,is_shareholder = contacts[_index].get("is_legal_person",0),contacts[_index].get("is_manager",0),contacts[_index].get("is_shareholder",0) df_data["招标联系人%s"%str(_index+1)][_i] = contact_person df_data["招标联系人电话%s"%str(_index+1)][_i] = phone_no df_data["招标单位所在地"][_i] = "%s-%s"%(dict_enterprise.get(_enterprise_name,{}).get("province",""),dict_enterprise.get(_enterprise_name,{}).get("city","")) df_data["项目类型"][_i] = dict_enterprise.get(_enterprise_name,{}).get("procurement_system") _enterprise_name = df_data["代理单位"][_i] contacts = dict_enterprise.get(_enterprise_name,{}).get("contacts") if contacts is not None: for _index in range(min(len(contacts),3)): contact_person,phone_no = contacts[_index].get("contact_person"),contacts[_index].get("phone_no") is_legal_person,is_manager,is_shareholder = contacts[_index].get("is_legal_person",0),contacts[_index].get("is_manager",0),contacts[_index].get("is_shareholder",0) df_data["代理联系人%s"%str(_index+1)][_i] = contact_person df_data["代理联系人电话%s"%str(_index+1)][_i] = phone_no df_data["代理单位所在地"][_i] = "%s-%s"%(dict_enterprise.get(_enterprise_name,{}).get("province",""),dict_enterprise.get(_enterprise_name,{}).get("city","")) _enterprise_name = df_data["中标单位"][_i] df_data["中标单位所在地"][_i] = "%s-%s"%(dict_enterprise.get(_enterprise_name,{}).get("province",""),dict_enterprise.get(_enterprise_name,{}).get("city","")) _person = dict_enterprise.get(_enterprise_name,{}).get("legal_person_name","") _phone = dict_enterprise.get(_enterprise_name,{}).get("phone_number","") if len(_phone)==11 and _phone[0]=="1": df_data["中标法人"][_i] = _person df_data["中标法人电话"][_i] = _phone contacts = dict_enterprise.get(_enterprise_name,{}).get("contacts") if contacts is not None: for _index in range(min(len(contacts),3)): contact_person,phone_no = contacts[_index].get("contact_person"),contacts[_index].get("phone_no") is_legal_person,is_manager,is_shareholder = contacts[_index].get("is_legal_person",0),contacts[_index].get("is_manager",0),contacts[_index].get("is_shareholder",0) if is_manager: df_data["中标高管"][_i] = contact_person df_data["中标高管电话"][_i] = phone_no if is_shareholder: df_data["中标股东"][_i] = contact_person df_data["中标股东电话"][_i] = phone_no df_data["中标联系人%s"%str(_index+1)][_i] = contact_person df_data["中标联系人电话%s"%str(_index+1)][_i] = phone_no log("start export 15824381998:>>>>>>>>>>>>>>>") current_date = getCurrent_date(format="%Y-%m-%d") start_time = time.strftime("%Y-%m-%d",time.localtime(time.mktime(time.localtime())-7*24*60*60)) if current_date<="2025-02-04" and datetime.datetime.now().weekday() in (1,4): for i in range(10): try: if datetime.datetime.now().weekday()==1: last_date = timeAdd(current_date,-4) if datetime.datetime.now().weekday() in (4,): last_date = timeAdd(current_date,-3) # start_time='2022-07-22' # current_date = '2022-07-28' page_time_start = timeAdd(current_date,-7) log("start exporting export2:=================") # columns = ["doctitle","doctextcon","docchannel","product","province","bidway","city","district","page_time","industry","info_type","tenderee","project_code","project_name","sub_docs_json","tenderee_contact","tenderee_phone","agency","agency_contact","agency_phone","uuid","time_bidclose"] dict_channel = getDict_docchannel() list_query = [] str_keyword = ''' 多功能位移机，失能群体安全转移，助行机器人，行走机器人，护理机器人，大小便护理，洗浴机，洗浴机器人，吃饭机器人，养老护理，老年评估，防摔，智能护理，智慧康养 ''' list_keyword = splitIntoList(str_keyword,"[\s\n、，,]") str_not_keyword = ''' 清洗机器人 ''' list_not_key = splitIntoList(str_not_keyword,"[\s\n、，,]") tenderee_keywrod = "医院、大学、高校、高中" list_t_key = splitIntoList(tenderee_keywrod,"[\s\n、，,]") log(str(list_keyword)) columns = ["doctitle","docchannel","product","province","bidway","city","district","page_time","industry","info_type","tenderee","project_code","project_name","sub_docs_json","tenderee_contact","tenderee_phone","agency","agency_contact","agency_phone","uuid","time_get_file_end","time_bidopen","time_bidstart","time_registration_start","time_registration_end"] bool_query = BoolQuery(must_queries=[ generateBoolShouldQuery(["doctitle"],list_keyword,MatchPhraseQuery), generateBoolShouldQuery(["docchannel"],[51,52,102,103,105,104,114,121,122,101,119,120,118],TermQuery), RangeQuery("crtime",last_date+" 10:00:00",current_date+" 10:00:00",True,True), RangeQuery("status",201,300,True,True), RangeQuery("page_time",page_time_start) # TermQuery("procurement_system","公安系统"), # generateBoolShouldQuery(["province"],["湖南"],TermQuery), # generateBoolShouldQuery(["tenderee"],list_t_key,WildcardQuery) # generateBoolShouldQuery(["docchannel"],[101,118,119],TermQuery), ], must_not_queries=[ generateBoolShouldQuery(["doctitle","doctextcon","attachmenttextcon"],list_not_key,MatchPhraseQuery), ] ) list_row = getDocument([{"query":bool_query,"limit":500}],columns,thread_count=1) log("get document %d rows"%len(list_row)) df_data = {} set_line = set() # list_row = filterRow(list_row) df_columns = getRowData_zb(df_data,list_row,set_line,list_keyword,dict_channel,True) fixContactPerson1(df_data,df_columns) df = pd.DataFrame(df_data) df_data_filter_11 = {} for c in df_columns: if c not in df_data_filter_11: df_data_filter_11[c] = [] df_data_filter_12 = {} for c in df_columns: if c not in df_data_filter_12: df_data_filter_12[c] = [] for _i in range(len(df_data["项目类型"])): if df_data["项目类型"][_i] in ("教育系统","科研系统"): for c in df_columns: if c not in df_data_filter_11: df_data_filter_11[c] = [] df_data_filter_11[c].append(df_data[c][_i]) else: for c in df_columns: if c not in df_data_filter_12: df_data_filter_12[c] = [] df_data_filter_12[c].append(df_data[c][_i]) df_f11 = pd.DataFrame(df_data_filter_11) df_f12 = pd.DataFrame(df_data_filter_12) time_end = timeAdd(current_date,60) columns = ["project_name","type","province","city","bidding_budget","city","demand","last_page_time"] bool_query = BoolQuery(must_queries=[ generateBoolShouldQuery(["demand","product"],list_keyword,MatchPhraseQuery), RangeQuery("may_end",range_from=current_date), RangeQuery("may_end",range_to=time_end), # TermQuery("procurement_system","公安系统"), # generateBoolShouldQuery(["province"],["湖南"],TermQuery), # generateBoolShouldQuery(["tenderee"],list_t_key,WildcardQuery) # generateBoolShouldQuery(["docchannel"],[101,118,119],TermQuery), ], must_not_queries=[ generateBoolShouldQuery(["demand","product"],list_not_key,MatchPhraseQuery), ] ) list_row = getDocument([{"query":bool_query,"limit":500}],columns,table_name="preproject",table_index="preproject_index",thread_count=1,sort_column="may_end") log("get document %d rows"%len(list_row)) df_data = {} set_line = set() # list_row = filterRow(list_row) df_columns_yc = getRowData_yc(df_data,list_row,set_line,list_keyword,dict_channel,True) fixContactPerson(df_data,df_columns_yc) for _i in range(len(df_data["项目类型"])): if df_data["项目类型"][_i] in ("政府办公室系统","财政系统","民事系统","企业采购系统","卫生系统"): df_data["类型"][_i] = "政企" elif df_data["项目类型"][_i] in ("教育系统","科研系统"): df_data["类型"][_i] = "教育" else: df_data["类型"][_i] = "其他" df1 = pd.DataFrame(df_data) filename = os.path.dirname(__file__)+"/data/%s年%s项目数据导出.xlsx"%(start_time[:4],current_date) with pd.ExcelWriter(filename) as writer: df_f11.to_excel(writer,sheet_name="教育",columns=df_columns if not df_f11.empty else None,index=False) df_f12.to_excel(writer,sheet_name="政企",columns=df_columns if not df_f12.empty else None,index=False) df1.to_excel(writer,sheet_name="项目预测",columns=df_columns_yc if not df1.empty else None) log(str(filename)) host = "smtp.exmail.qq.com" username = "vip@bidizhaobiao.com" password = "Biaoxun66-" # receivers = ["724949655@qq.com","1396488964@qq.com"] receivers = ["md47@zuowei.com"] # receivers = ["1175730271@qq.com"] attachs = [filename] sendEmail(host,username,password,receivers,attachs=attachs) break except Exception as e: traceback.print_exc() def job_export(): _scheduler = BlockingScheduler() e3 = Export3() # _scheduler.add_job(export_medicine_friday,"cron",hour=8) # _scheduler.add_job(export_medicine_friday,"cron",second="*/1") _scheduler.add_job(export2,"cron",hour=9) _scheduler.add_job(export5,"cron",hour=16) # _scheduler.add_job(e3.export4,"cron",hour=7) _scheduler.add_job(e3.trytimes,"cron",hour=21) _scheduler.add_job(e3.export3_1,"cron",hour=21) _scheduler.add_job(export_15824381998,"cron",hour=21) _scheduler.add_job(export_13510123669,"cron",hour=10) _scheduler.start() if __name__=="__main__": # job_export() # export_medicine_friday() # export2() # export_document_except() # e3 = Export3() # e3.export4_by_project() # e3.export4() # e3.trytimes() # e3.export3_1() # export5() # export_15824381998() export_13510123669()