import json import os import re import copy # from konfig import Config from datetime import datetime import sys import multiprocessing import threading import plugins.utils.scraplib as scraplib #공통 라이브러리 import requests from concurrent.futures import ThreadPoolExecutor # pluins from plugins.sql.hooks.mysql_hook import CommonHookMySQL #환경설정 파일 로드 # CONF = Config(os.path.dirname(__file__) + "\\conf.ini") # DB_CONF = CONF.get_map("DB") # DRIVER = CONF.get_map("DRIVER") # DRIVER["EXT_PATH"] = os.path.dirname(__file__) + DRIVER["EXT_PATH"] #=============================================================================================================================================== #공고처리 db #=============================================================================================================================================== # 공고문 자동화 # self 로 불러오면 스레드때문에 여러개의 생성자를 계속생성함, db터짐 # DB 연결 ID DB_CONN_ID = "COLLECT_SERVER_DB" def html_conv(bidid): Util = scraplib.Util() #auto_link = "http://222.234.3.20:9090/html_extract?bidid={bidid}".format(bidid=bidid) auto_link = "http://222.234.3.21:9090/html_extract?bidid={bidid}".format(bidid=bidid) print(auto_link) try: Util.get_URL(auto_link, {}) return "suceess" except Exception as e: print(e) return e class I2: ####################################################################### #######################공고 처리 부분#################################### ####################################################################### def __init__(self): self.dbconn_BI = scraplib.dbconn() self.Util = scraplib.Util() self.Etl = scraplib.Etl() self.worker_tp = ThreadPoolExecutor(10) #create #self.dbconn_BI.conn.cursor() # dbclose def create_doc(self, syscollect, item_all_data, pattern): print("테스트") item_all = copy.deepcopy(item_all_data) bid_key = item_all['bid_key']if "bid_key" in item_all else [] bid_value = item_all['bid_value'] if "bid_value" in item_all else [] bid_content = item_all['bid_content'] if "bid_content" in item_all else [] bid_local = item_all['bid_local'] if "bid_local" in item_all else [] # 2024.07.15 추가 -> 공고 원문에서 제공하는 텍스트 본연의 데이터를 저장하는 공간 생성 bid_org = item_all['bid_org'] if "bid_org" in item_all else [] bid_goods = item_all['bid_goods'] if "bid_goods" in item_all else [] bid_memo = item_all['bid_memo'] if "bid_memo" in item_all else [] bid_notice_memo = item_all['bid_notice_memo'] if "bid_notice_memo" in item_all else [] bid_res = item_all['bid_res'] if "bid_res" in item_all else [] bid_succom = item_all['bid_succom'] if "bid_succom" in item_all else [] bid_succom_mutual_flag = item_all['bid_succom_mutual_flag'] if "bid_succom_mutual_flag" in item_all else [] premiumList = item_all['premiumList'] if "premiumList" in item_all else [] const_cost_arr = item_all['bid_const_cost_list'] if "bid_const_cost_list" in item_all else [] #재입찰추가 const_cost_arr_rebid = item_all['const_cost_arr_rebid'] if "const_cost_arr_rebid" in item_all else [] bbs_common_issue = item_all['bbs_common_issue'] if "bbs_common_issue" in item_all else [] #재입찰추가끝 join_part_list = item_all['join_part_list'] if "join_part_list" in item_all else [] mutual_text = item_all['mutual_text'] if "mutual_text" in item_all else [] bid_error = item_all['bid_error'] if "bid_error" in item_all else [] nbid_jongsim_lnk = item_all['nbid_jongsim_lnk'] if "nbid_jongsim_lnk" in item_all else [] auto_service = item_all['auto_service'] if "auto_service" in item_all else [] uniform_price_list = item_all['uniform_price_list'] if "uniform_price_list" in item_all else [] crawl_text_log = item_all['crawl_text_log'] if "crawl_text_log" in item_all else [] print("****************************************") #print(bid_local) print("****************************************") #item_etc = item_all['item_etc'] if "item_etc" in item_all else [] #bid_key = item_all['bid_key'] #bid_value = item_all['bid_value'] #bid_content = item_all['bid_content'] #item_etc = item_all['item_etc'] #bid_res = item_all['bid_res'] #bid_succom = item_all['bid_succom'] whereis = bid_key['whereis'] bidtype = bid_key['bidtype'] notinum = syscollect['dkey'] #notinum_ex예외처리 ext_whereis = ["53", "04","90","07","11","52","05","03"]# 철도시설공단, 가스공사, k아파트, 수자원공사, 석유공사, 철도공사,토지주택공사, 한전 if whereis in ext_whereis: notinum_ex = '' else: notinum_ex = syscollect['dkey_ext'] proc = bid_key['bidproc'] #나중에 전체적용할때는 bid_key기준으로 넣는다. 그때는 item_etc['bidproc'] 뺄 예정 item_etc['bidproc'] rebid_no = syscollect['rebid_no'] if rebid_no == '': rebid_no = None bunryu_no = None #이게 뭔질 모르겟음 #복수공고에서 쓸것처럼 보임 #데이터확인결과 사실상 안쓰는듯 if bunryu_no == '': bunryu_no = None # 정정공고인 경우 임의로 수집되게 허용된 발주처 # 공고번호가 같아도 수집될수있게 허용한다. 후에 공고번호를 업데이트하여 정상처리 randomly_notinum_bidproc_m = ["93", "90", "91", "15"] #마사회, k아파트, 한국전자통신연구원, 국토정보공사 i2db_cnf={} if whereis in randomly_notinum_bidproc_m: if bid_key['bidproc'] == 'M': i2db_cnf['notinum'] = notinum + "-1" else: i2db_cnf['notinum'] = notinum else: i2db_cnf['notinum'] = notinum #rebid_no None처리( new_rebid() 안타게 하기위해) 발주처 # 재입찰있는데 재입찰 번호 필요없는 발주처 : 한국철도공사,전자통신연구원, 국토정보공사, KDN,K-apt # 한국철도공사, 국방부, 석유공사, 비드포유, 학교장터, 한국수력원자력, 가스공사, 철도시설공단, 전자통신연구원, 토지주택공사, 국제협력단. 마사회, 국토정보공사,강원랜드, KDN, K-apt, SR, KBS rebid_no_is_None = ["52", "10", "11", "17", "13", "07", "04", "53", "91", "05", "83", "93", "15", "16", "81", "90", "20", "19", "44", "21"] if whereis in rebid_no_is_None: i2db_cnf['rebid_no'] = None else: i2db_cnf['rebid_no'] = rebid_no i2db_cnf['notinum_ex'] = notinum_ex i2db_cnf['proc'] = proc i2db_cnf['bunryu_no'] = bunryu_no i2db_cnf['pattern'] = pattern i2db_cnf['whereis'] = whereis # 한전 공고수집시 이슈 조치 (2022.11.10) # 용역으로 수집되는 조건이지만 입력자 입력시 공사로 변경한 공고의 경우 -> 전차수 공고로 매칭이 안되는 현상 발생 # 다른 발주처도 적용해도 되는지 확인이 안되어 한전만 우선 적용 if whereis == "03": bidtype = None i2db_cnf['bidtype'] = bidtype print("===============================") print(pattern) print(i2db_cnf['notinum']) print("===============================") pno = re.match(pattern, i2db_cnf['notinum']) if pno: print("공고처리 시작") else: print("공고번호 패턴이 맞지 않습니다.") return False #init 처리 : 필요없는경우 버릴듯 #공고번호 형식 유무 #recollect인지 확인 #입/낙찰 구분 함 if syscollect["recollect_stats"] == True or syscollect["proc"] == 'recollect': # 개찰쪽 if i2db_cnf['proc'] == 'S' or i2db_cnf['proc'] == 'F': self.load_last_bidid(i2db_cnf, 'nbid') else: #입찰쪽쪽 self.load_last_bidid(i2db_cnf, 'bid') #중복체크(함수로뺌) b = self.isdup(i2db_cnf, 'duple') if b == True: print("이미 입력된 공고") return False #개찰 if proc == 'S' or proc == 'F': bidid = self.load_last_bidid(i2db_cnf, 'bidid') if bidid is None: #입찰 입력이 안되있으면 컷 print("입찰이없는공고") return False usp_query = "CALL usp_bid_proc('{bidid}', '{proc}', @bidid)".format(bidid=bidid, proc=proc) usp_res = self.dbconn_BI.sql_exec(usp_query, "D") new_bidid = bidid #입찰 elif proc == 'R' and i2db_cnf['rebid_no'] is not None: print("재입찰처리") new_bidid = self.new_rebid(i2db_cnf) elif proc == 'B' and i2db_cnf['bunryu_no'] is not None: print("정상공고 이면서 분류번호 값이 있는경우 복수공고 등으로 보임. 사실상 사용안하는거같음") new_bidid = self.new_bunryu(i2db_cnf) else: print("기타 조건 (정상, 정정, 취소, 재입찰 등) ") new_bidid= self.new_normal(i2db_cnf) print("@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@") print(new_bidid) print("@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@") # 관내 처리 if len(bid_local) > 0: bid_local = self.Etl.filter_bid_local(bid_local) item_all['bid_local'] = bid_local # 밑에 gonggo_opt_stting에 item_all에 넣기 위함 #A값/순공사원가 처리 if new_bidid is not None and new_bidid != '': #공고처리 완료후 #bidproc 초기화 if proc != 'C': bid_key['bidproc'] = None #상단에서 이미 bid_key의 bidproc을 세팅하여 스켈레톤 코드를 만들었으므로 더이상 업데이트 되지 않게 처리 # opt, constnm 처리 if len(bid_key) > 0 : bid_key['opt'] = self.gonggo_opt_stting(proc, item_all, new_bidid) if proc !='S' and proc != 'F': bid_key['constnm'] = self.gonggo_constnm_stting(proc, item_all, new_bidid) if proc !='R': bid_key['concode'] = self.gonggo_concode_stting(item_all) # UPSERT 처리 데이터 리스트 upsert_bid_key_list = [] upsert_bid_value_list = [] upsert_bid_content_list = [] upsert_bid_res_list = [] upsert_bid_memo_list = [] upsert_bid_notice_memo_list = [] upsert_premiumList_list = [] ############################################## #################고정 업데이트 ################# ############################################## #bid_key if proc != 'S' and proc != 'F': bid_key['writedt'] = datetime.now().strftime("%Y-%m-%d %H:%M:%S") _bid_key = { "key": {"bidid": new_bidid}, "table": "bid_key", "type": "update", "value": dict(bid_key), "del_col": [] } self.dbconn_BI.Upsert_table(_bid_key) #bid_value _bid_value = { "key": {"bidid": new_bidid}, "table": "bid_value", "type": "update", "value": dict(bid_value), "del_col": [] } self.dbconn_BI.Upsert_table(_bid_value) #bid_content _bid_content = { "key": {"bidid": new_bidid}, "table": "bid_content", "type": "update", "whereis": whereis, "value": dict(bid_content), "del_col": [], "bidtype" : bidtype } self.dbconn_BI.Upsert_table(_bid_content) #bid_res _bid_res = { "key": {"bidid": new_bidid}, "table": "bid_res", "type": "update", "value": dict(bid_res), "del_col": [] } self.dbconn_BI.Upsert_table(_bid_res) ############################################## #################유동 업데이트 ################# ############################################## ########### ####단일#### ########### #bid_memo if len(bid_memo) > 0: bid_memo['bidid'] = new_bidid _bid_memo = { "key": {"bidid": new_bidid}, "table": "bid_memo", "type": "insertonly", "value": dict(bid_memo), "del_col": [] } self.dbconn_BI.Upsert_table(_bid_memo) # bid_notice_memo if len(bid_notice_memo) > 0: bid_notice_memo['bidid'] = new_bidid _bid_notice_memo = { "key": {"bidid": new_bidid}, "table": "bid_notice_memo", "type": "insertonly", "value": dict(bid_notice_memo), "del_col": [] } self.dbconn_BI.Upsert_table(_bid_notice_memo) # preminuList if len(premiumList) > 0: premiumList['bidid'] = new_bidid premiumList['writedt'] = "NOW()" if "cost1" in premiumList and premiumList['cost1'] is not None and premiumList['cost1'] != '' or\ "cost2" in premiumList and premiumList['cost2'] is not None and premiumList['cost2'] != '' or\ "cost3" in premiumList and premiumList['cost3'] is not None and premiumList['cost3'] != '' or\ "cost4" in premiumList and premiumList['cost4'] is not None and premiumList['cost4'] != '' or \ "cost5" in premiumList and premiumList['cost5'] is not None and premiumList['cost5'] != '' or \ "cost6" in premiumList and premiumList['cost6'] is not None and premiumList['cost6'] != '' or\ "cost7" in premiumList and premiumList['cost7'] is not None and premiumList['cost7'] != '' or\ "cost8" in premiumList and premiumList['cost8'] is not None and premiumList['cost8'] != '' or\ "cost_total" in premiumList and premiumList['cost_total'] is not None and premiumList['cost_total'] != '' or\ "direct_labor_cost" in premiumList and premiumList['direct_labor_cost'] is not None and premiumList['direct_labor_cost'] != '': _premiumList = { "key": {"bidid": new_bidid}, "table": "premiumList", "type": "insertonly", "value": dict(premiumList), "del_col": [] } self.dbconn_BI.Upsert_table(_premiumList) #mutual_text if len(mutual_text) > 0: mutual_text['bidid'] = new_bidid _mutual_text = { "key": {"bidid": new_bidid}, "table": "mutual_text", "type": "insertonly", "value": dict(mutual_text), "del_col": [] } self.dbconn_BI.Upsert_table(_mutual_text) #순공사원가 if len(const_cost_arr) > 0: self.Etl.confirm_const_cost_list_new(bid_key, proc, const_cost_arr, new_bidid) ##재입찰추가## #순공사원가 재입찰 #재입찰은 기존차수데이터 인설트만 해준다 if len(const_cost_arr_rebid) > 0: const_cost_arr_rebid['bidid'] = new_bidid const_cost_arr_rebid['writedt'] = "NOW()" _const_cost_arr_rebid = { "key": {"bidid": new_bidid}, "table": "bid_const_cost_list", "type": "insertonly", "value": dict(const_cost_arr_rebid), "del_col": [] } self.dbconn_BI.Upsert_table(_const_cost_arr_rebid) #내역첨부여부 if len(bbs_common_issue) > 0: bbs_common_issue['bidid'] = new_bidid _bbs_common_issue = { "key": {"bidid": new_bidid}, "table": "bbs_common_issue", "type": "insertonly", "value": dict(bbs_common_issue), "del_col": [] } self.dbconn_BI.Upsert_table(_bbs_common_issue) ##재입찰추가끝## ########### ####배열#### ########### #bid_local if len(bid_local) > 0: for idx in bid_local: if bid_local[idx] is not None: if "loccode"in bid_local[idx]: del bid_local[idx]['loccode'] bid_local[idx]['bidid'] = new_bidid _bid_local = { "key": {"bidid": new_bidid}, "table": "bid_local", "type": "insertonly", "value": dict(bid_local[idx]), "del_col": [] } self.dbconn_BI.Upsert_table(_bid_local) #bid_goods if len(bid_goods) > 0: for idx in bid_goods: bid_goods[idx]['bidid'] = new_bidid _bid_goods = { "key": {"bidid": new_bidid}, "table": "bid_goods", "type": "insertonly", "value": dict(bid_goods[idx]), "del_col": [] } self.dbconn_BI.Upsert_table(_bid_goods) # bid_error if len(bid_error) > 0: for idx in bid_error: _bid_err = { "key": {"bidid": new_bidid}, "table": "bid_error", "type": "insertonly", "value": {"err_code": bid_error[idx], "writedt": "NOW()", "bidid": new_bidid}, "del_col": [] } self.dbconn_BI.Upsert_table(_bid_err) # nbid_jongsim_lnk if len(nbid_jongsim_lnk) > 0: nbid_jongsim_lnk['bidid'] = new_bidid _nbid_jongsim_lnk = { "key": {"bidid": new_bidid}, "table": "nbid_jongsim_lnk", "type": "insertonly", "value": dict(nbid_jongsim_lnk), "del_col": [] } self.dbconn_BI.Upsert_table(_nbid_jongsim_lnk) # bid_succom if len(bid_succom) > 0: for idx in bid_succom: bid_succom[idx]['bidid'] = new_bidid _bid_succom = { "key": {"bidid": new_bidid}, "table": "bid_succom", "type": "insertonly", "value": dict(bid_succom[idx]), "del_col": [] } self.dbconn_BI.Upsert_table(_bid_succom) # bid_succom_mutual_flag if len(bid_succom_mutual_flag) > 0: for idx in bid_succom_mutual_flag: bid_succom_mutual_flag[idx]['bidid'] = new_bidid _bid_succom_mutual_flag = { "key": {"bidid": new_bidid}, "table": "bid_succom_mutual_flag", "type": "insertonly", "value": dict(bid_succom_mutual_flag[idx]), "del_col": [] } self.dbconn_BI.Upsert_table(_bid_succom_mutual_flag) #join_part_list if len(join_part_list) > 0: for idx in join_part_list: join_part_list[idx]['bidid'] = new_bidid _join_part_list = { "key": {"bidid": new_bidid}, "table": "join_part_list", "type": "insertonly", "value": dict(join_part_list[idx]), "del_col": [] } self.dbconn_BI.Upsert_table(_join_part_list) #auto_service if len(auto_service) > 0: auto_service['bidid'] = new_bidid auto_service['writedt'] = "NOW()" _auto_service = { "key": {"bidid": new_bidid}, "table": "auto_service_log", "type": "insertonly", "value": dict(auto_service), "del_col": [] } self.dbconn_BI.Upsert_table(_auto_service) # uniform_price_list if len(uniform_price_list) > 0: uniform_price_list['bidid'] = new_bidid uniform_price_list['writedt'] = "NOW()" _uniform_price_list = { "key": {"bidid": new_bidid}, "table": "uniform_price_list", "type": "insertonly", "value": dict(uniform_price_list), "del_col": [] } self.dbconn_BI.Upsert_table(_uniform_price_list) # crawl_text_log if len(crawl_text_log) > 0: crawl_text_log['bidid'] = new_bidid crawl_text_log['writedt'] = "NOW()" _crawl_text_log = { "key": {"bidid": new_bidid}, "table": "crawl_text_log", "type": "insertonly", "value": dict(crawl_text_log), "del_col": [] } self.dbconn_BI.Upsert_table(_crawl_text_log) # bid_org if len(bid_org) > 0: bid_org['bidid'] = new_bidid _bid_org = { "key": {"bidid": new_bidid}, "table": "bid_org", "type": "insertonly", "value": dict(bid_org), "del_col": [] } self.dbconn_BI.Upsert_table(_bid_org) # =============================== # SMPP (입찰일때만) # =============================== # if (proc != 'S' or proc != 'F'):# and whereis == '44': # print('SMPPPPP탐') # _where = {} # _where['bidid'] = new_bidid # # # check_bid_common_data --> bidid로 검색해서 있는지 확인 # check_bid_common_data = self.dbconn_BI.ck_Exist_cnt(_where, "bid_common_data") # print('check_bid_common_data', check_bid_common_data) # # # 코드 값 return # smpp_code = self.smpp(item_all_data, new_bidid) # print('smpp_code : ', smpp_code) # # if check_bid_common_data > 0: # 데이터 있으면 update # print('데이터 있음 --> UPDATE') # _bid_common_data = { # "key": {"bidid": new_bidid}, # "table": "bid_common_data", # "type": "updateonly", # "value": {"bidid": new_bidid, "notinum": notinum, "whereis" : whereis, # "common_data_code" : smpp_code, "editdt" : "NOW()"}, # "del_col": [] # } # self.dbconn_BI.Upsert_table(_bid_common_data) # # if check_bid_common_data == 0 and smpp_code != False: # 데이터 없고 SMPP 코드값 있으면 insert # print('데이터 없고 SMPP 코드 있음 --> INSERT') # _bid_common_data = { # "key": {"bidid": new_bidid}, # "table": "bid_common_data", # "type": "insertonly", # "value": {"bidid": new_bidid, "notinum": notinum, "whereis" : whereis, "common_data_code" : smpp_code, # "writedt" : "NOW()", "editdt" : "NOW()"}, # "del_col": [] # } # self.dbconn_BI.Upsert_table(_bid_common_data) #공고문자동화 #공고문 자동화 제외 ext_auto_gonggo = ["91",'17','21'] #전자통신연구원 , 이비즈포유, 농수산식품유통공사 if whereis in ext_auto_gonggo: print("공고문 자동화 패스") elif proc != 'S' and proc != 'F' and proc != 'R': print("ㄱㄱㅁㅈㄷㅎ") #worker_t = threading.Thread(target=html_conv, args=([new_bidid])) #worker_t.setDaemon(True) #worker_t.start() #worker_t.join() #결과값 반환해야 나감 의미없음 # 공고문 자동수집 제외 조건 html_conv_process = True # 전자시담공고로 자동점검 공고 기준의 경우 제외 if bid_key.get('auto_service_code') and 'e_sidam' in bid_key['auto_service_code']: html_conv_process = False if html_conv_process: self.worker_tp.submit(html_conv, new_bidid) print("ㄱㄱㅁㅈㄷㅎ") #self.html_conv(new_bidid) #lock풀기 self.unlock(new_bidid) #self.dbconn_BI.conn.close() # dbclose return new_bidid else: return False print("라이브러리 끝") #=============================== # SMPP 코드 체크 #=============================== # def smpp(self, item_all, bidid): # print("SMPPPPPPPPPPPPPPPPPPPPPPPPPP") # print('bidid\n', bidid) # bid_key = item_all['bid_key'] if "bid_key" in item_all else [] # bid_value = item_all['bid_value'] if "bidㄴ_value" in item_all else [] # bid_content = item_all['bid_content'] if "bid_content" in item_all else [] # # whereis = bid_key['whereis'] # bidtype = bid_key['bidtype'] # constnm = bid_key['constnm'].replace("\n", "").replace(" ", "") # # promise_org = '' # bidcomment = '' # bid_html = '' # # if 'promise_org' in bid_value and bid_value['promise_org'] is not None: # promise_org = bid_value['promise_org'].replace("\n", "").replace(" ", "") # print("[계약방법] : ", promise_org) # # if 'bidcomment' in bid_content and bid_content['bidcomment'] is not None: # bidcomment = bid_content['bidcomment'].replace("\n", "").replace(" ", "") # print("[자격조건] : ", bidcomment) # # if 'bid_html' in bid_content and bid_content['bid_html'] is not None: # bid_html = bid_content['bid_html'].replace("\n", "").replace(" ", "") # print("[bid_html] : ", bid_html) # # updateCheckCode = '' # log = [] # # auto_set_qry = "SELECT * FROM bid_common_data_auto_set WHERE state = '1'" # auto_set_rst = self.dbconn_BI.sql_exec(auto_set_qry, "S") # # for row in auto_set_rst: # # # 판단 유무 기록 # auto_check = {} # # # DB정보 변수 세팅 # auto_set_checkCode = row[1] # auto_set_searchWord = row[2] # auto_set_whereis = row[3] # auto_set_bidtype = row[4] # # # ----------------------------------- # # 발주처 조건 판단 -> 전체 or 발주처코드 # # ----------------------------------- # auto_check["whereis"] = 'Y' if auto_set_whereis == 'ALL' or whereis == auto_set_whereis else "N" # # # ----------------------------------- # # 공고분류 판단 -> 전체 or 시설,용역,물품 # # ----------------------------------- # auto_check["bidtype"] = 'Y' if auto_set_bidtype == 'ALL' or bidtype == auto_set_bidtype else "N" # # # ----------------------------------- # # 텍스트 비교 판단 -> 매칭, 제외 조건별 체크 # # ----------------------------------- # auto_check["searchWord"] = 'N' # # pattern = json.loads(auto_set_searchWord) # # # print(pattern) # # 세팅된 공고명 <<< 매칭 >>> key 값이 있고 + 공고명에서 정규식 매칭이 될 경우 # if "Ture_constnm" in pattern and len(re.findall(pattern["Ture_constnm"], constnm)) > 0: # # 세팅된 공고명 >>> 제외 <<< key 값이 없거나 | 공고명에서 정규식 매칭이 안될 경우 # if ("False_constnm" not in pattern or ( # "False_constnm" in pattern and len(re.findall(pattern["False_constnm"], constnm))) <= 0): # auto_check["searchWord"] = 'Y' # log.append(pattern["Ture_constnm"]) # # # 세팅된 자격조건 <<< 매칭 >>> key 값이 있고 + 자격조건에서 정규식 매칭이 될 경우 # if "Ture_bidcomment" in pattern and len(re.findall(pattern["Ture_bidcomment"], bidcomment)) > 0: # # 세팅된 자격조건 >>> 제외 <<< key 값이 없거나 | 자격조건에서 정규식 매칭이 안될 경우 # if ("False_bidcomment" not in pattern or ( # "False_bidcomment" in pattern and len( # re.findall(pattern["False_bidcomment"], bidcomment))) <= 0): # auto_check["searchWord"] = 'Y' # log.append(pattern["Ture_bidcomment"]) # # # 세팅된 공고문 <<< 매칭 >>> key 값이 있고 + 공고문에서 정규식 매칭이 될 경우 # if "Ture_bid_html" in pattern and len(re.findall(pattern["Ture_bid_html"], bid_html)) > 0: # # 세팅된 공고문 >>> 제외 <<< key 값이 없거나 | 공고문에서 정규식 매칭이 안될 경우 # if ("False_bid_html" not in pattern or ( # "False_bid_html" in pattern and len(re.findall(pattern["False_bid_html"], bid_html))) <= 0): # auto_check["searchWord"] = 'Y' # log.append(pattern["Ture_bid_html"]) # # # 세팅된 계약방법 <<< 매칭 >>> key 값이 있고 + 계약방법에서 정규식 매칭이 될 경우 # if "Ture_promise_org" in pattern and len(re.findall(pattern["Ture_promise_org"], promise_org)) > 0: # # 세팅된 계약방법 >>> 제외 <<< key 값이 없거나 | 계약방법에서 정규식 매칭이 안될 경우 # if ("False_promise_org" not in pattern or ( # "False_promise_org" in pattern and len( # re.findall(pattern["False_promise_org"], promise_org))) <= 0): # auto_check["searchWord"] = 'Y' # log.append(pattern["Ture_promise_org"]) # # # N으로 판단된 조건이 없을 경우 코드 세팅 # if "N" not in auto_check.values(): # # SMPP저장된 코드에 없을 경우에만 코드 추가 # # if len(re.findall(auto_set_checkCode, common_data_code)) <= 0: # updateCheckCode += auto_set_checkCode + '|' # # updateCheckCode = updateCheckCode.rstrip('|') # # print("==================================================") # print("업데이트 될 코드 --> ", updateCheckCode) # print("체크 된 텍스트", log) # # if updateCheckCode != '': # return updateCheckCode # else: # return False #중복체크 및 하위데이터 체크 def isdup(self, i2db_cnf, type): print("여들어옴") if type == 'child': #하위 데이터체크일경우 where_arr = self.build_where(i2db_cnf, 'arr', True) elif type == 'duple': #중복체크 if i2db_cnf['proc'] == 'R' and i2db_cnf['rebid_no'] is not None and i2db_cnf['rebid_no'] != '': where_arr = self.build_where(i2db_cnf, 'arr') #duple_q = "SELECT COUNT(*) FROM view_bid_uid WHERE 1 AND {where_q}".format(where_q=where_q) #duple_q = duple_q.replace("bidproc='R'", "bidproc='B'") if where_arr['bidproc']['data'] == 'R': #재입찰일떄만 bidproc을 지워준다. where_arr['bidproc']['data'] = 'B' del where_arr['bidproc'] else: where_arr = self.build_where(i2db_cnf, 'arr', None, False) #duple_q = "SELECT COUNT(*) FROM view_bid_uid WHERE 1 AND {where_q}".format(where_q=where_q) cnt_flag = self.dbconn_BI.ck_Exist_one_difftype(where_arr, "view_bid_uid") #0보다 크면 true print("@#$@#$@#$") print(cnt_flag) print("@#$@#$@#$") if cnt_flag == True: #중복, 하위데이터잇음 #정정일 경우에는 자체정정을 한번 체크해준다. if i2db_cnf['proc'] == 'M': mod_arr = {} mod_arr['whereis'] = {} mod_arr['dkey'] = {} mod_arr['whereis']['type'] = 'equal' mod_arr['whereis']['data'] = i2db_cnf['whereis'] mod_arr['dkey']['type'] = 'equal' mod_arr['dkey']['data'] = i2db_cnf['notinum'] if i2db_cnf['whereis'] == '11': if i2db_cnf['rebid_no'] is None: mod_arr['rebid_no'] = {} mod_arr['rebid_no']['type'] = 'equal' mod_arr['rebid_no']['data'] = '0' if i2db_cnf['notinum_ex'] is None: mod_arr['dkey_ext'] = {} mod_arr['dkey_ext']['type'] = 'equal' mod_arr['dkey_ext']['data'] = '0' if i2db_cnf['rebid_no'] is not None and i2db_cnf['rebid_no'] != '': mod_arr['rebid_no'] = {} mod_arr['rebid_no']['type'] = 'equal' mod_arr['rebid_no']['data'] = i2db_cnf['rebid_no'] if i2db_cnf['notinum_ex'] is not None and i2db_cnf['notinum_ex'] != '': mod_arr['dkey_ext'] = {} mod_arr['dkey_ext']['type'] = 'equal' mod_arr['dkey_ext']['data'] = i2db_cnf['notinum_ex'] mod_flag = self.dbconn_BI.ck_Exist_one_difftype(mod_arr, "mod_data_table") # 0보다 크면 true if mod_flag == True: return False return True else: return False #where 만들어주는 함수 def build_where(self, i2db_cnf, return_type, isChild = False , state_d_include = True): where_q ="" where_arr = {} if isChild == True: fno = re.match(i2db_cnf['pattern'], i2db_cnf['notinum']) if fno: where_q = where_q + "notinum LIKE '{fno}%'".format(fno=fno.group(1)) where_arr['notinum'] = {} where_arr['notinum']['type'] = 'text' where_arr['notinum']['data'] = "LIKE '{fno}%'".format(fno=fno.group(1)) else: print("pattern 매치 결과가 없습니다") return False else: where_q = where_q + "notinum = '{notinum}'".format(notinum=i2db_cnf['notinum']) where_arr['notinum'] = {} where_arr['notinum']['type'] = 'equal' where_arr['notinum']['data'] = i2db_cnf['notinum'] if i2db_cnf['proc'] == 'B' or i2db_cnf['proc'] == 'M': if i2db_cnf['whereis'] == '81' or i2db_cnf['whereis'] == '11': where_q = where_q + "AND bidproc = '{bidproc}'".format(bidproc=i2db_cnf['proc']) where_arr['bidproc'] = {} where_arr['bidproc']['type'] = 'equal' where_arr['bidproc']['data'] = i2db_cnf['proc'] if i2db_cnf['proc'] == 'L' or i2db_cnf['proc'] == 'R' or i2db_cnf['proc'] == 'S' or i2db_cnf['proc'] == 'F' or i2db_cnf['proc'] == 'C': if i2db_cnf['proc'] == 'R' and i2db_cnf['rebid_no'] is not None and i2db_cnf['rebid_no'] != '': where_q = where_q + "AND bidid_rebidno = '{rebid_no}'".format(rebid_no=i2db_cnf['rebid_no']) where_arr['bidid_rebidno'] = {} where_arr['bidid_rebidno']['type'] = 'equal' where_arr['bidid_rebidno']['data'] = i2db_cnf['rebid_no'] where_q = where_q + "AND bidproc = '{proc}'".format(proc=i2db_cnf['proc']) where_arr['bidproc'] = {} where_arr['bidproc']['type'] = 'equal' where_arr['bidproc']['data'] = i2db_cnf['proc'] where_q = where_q + "AND whereis = '{whereis}'".format(whereis=i2db_cnf['whereis']) where_arr['whereis'] = {} where_arr['whereis']['type'] = 'equal' where_arr['whereis']['data'] = i2db_cnf['whereis'] if i2db_cnf['bunryu_no'] is not None and i2db_cnf['bunryu_no'] != '': where_q = where_q + "AND bidid_bunryu = '{bunryu_no}'".format(whereis=i2db_cnf['bunryu_no']) where_arr['bidid_bunryu'] = {} where_arr['bidid_bunryu']['type'] = 'equal' where_arr['bidid_bunryu']['data'] = i2db_cnf['bunryu_no'] if i2db_cnf['notinum_ex'] is not None and i2db_cnf['notinum_ex'] != '': where_q = where_q + "AND notinum_ex = '{notinum_ex}'".format(notinum_ex=i2db_cnf['notinum_ex']) where_arr['notinum_ex'] = {} where_arr['notinum_ex']['type'] = 'equal' where_arr['notinum_ex']['data'] = i2db_cnf['notinum_ex'] if i2db_cnf['bidtype'] is not None and i2db_cnf['bidtype'] != '': where_q = where_q + "AND bidtype = '{bidtype}'".format(bidtype=i2db_cnf['bidtype']) where_arr['bidtype'] = {} where_arr['bidtype']['type'] = 'equal' where_arr['bidtype']['data'] = i2db_cnf['bidtype'] if state_d_include == True: #없애도 될거같음. 일단 내비둠 where_q = where_q + "AND state NOT in ('D')" where_arr['state'] = {} where_arr['state']['type'] = 'text' where_arr['state']['data'] = "NOT in ('D')" if return_type == 'arr': print("where_arr") print(where_arr) return where_arr else: return where_q #마지막 공고 데이터 def load_last_bidid(self, i2db_cnf, flag): #입찰 낙찰이 같은거같은데, 소스상에스 큰 차이를 보이지않음. 일단 통합 if flag == 'bidid': where_q = self.build_where(i2db_cnf, 'text', True) else: where_q = self.build_where(i2db_cnf, 'text') query = "SELECT bidid FROM view_bid_uid WHERE 1 AND {where_q} ORDER BY bidid DESC limit 1".format(where_q=where_q) bidid = self.dbconn_BI.sql_exec(query, "S_one") if bidid is not None: if flag == 'nbid': table_query = "CALL usp_bid_res_del('{bidid}')".format(bidid=bidid) self.dbconn_BI.sql_exec(table_query, "D") del_query = "DELETE FROM `i2`.`bid_succom` WHERE `bidid` = '{bidid}' ".format(bidid=bidid) self.dbconn_BI.sql_exec(del_query, "D") del_sub_query = "DELETE FROM `i2`.`bid_succom_mutual_flag` WHERE `bidid` = '{bidid}' ".format(bidid=bidid) self.dbconn_BI.sql_exec(del_sub_query, "D") #return None #bidid 셀렉트 할 경우에만 리턴값을 넣어줌. 추후 필요에 의해 변경할 여지 있음 elif flag == 'bid': table_query = "CALL usp_bid_del('{bidid}')".format(bidid=bidid) self.dbconn_BI.sql_exec(table_query, "D") #return None # bidid 셀렉트 할 경우에만 리턴값을 넣어줌. 추후 필요에 의해 변경할 여지 있음 if flag == 'bidid': return bidid return None def new_rebid(self, i2db_cnf): last_bidid = self.load_last_bidid(i2db_cnf, 'bidid') if last_bidid is None or last_bidid == '': print('new_rebid create failed') return '' query = "CALL usp_bid_create_rebid2('{last_bidid}', '{rebid_no}', @bidid)".format(last_bidid=last_bidid, rebid_no=i2db_cnf['rebid_no']) res = self.dbconn_BI.sql_exec(query, "D") print("new_rebid") if res == False: return '' bidid = self.dbconn_BI.sql_exec("SELECT @bidid", "S_one") return bidid def new_bunryu(self, i2db_cnf): query = "SELECT bidid FROM view_bid_uid WHERE whereis='{whereis}' AND notinum='{notinum}'".format(whereis=i2db_cnf['whereis'], notinum=i2db_cnf['notinum']) parent_bidid = self.dbconn_BI.sql_exec(query, "S_one") if parent_bidid is None: parent_bidid = '' if i2db_cnf['bunryu_no'] is None or i2db_cnf['bunryu_no'] == '': print("new_bunryu==> new_bunryu 파라미터 없음") return '' query = "CALL usp_bid_create_bunryu('{parent_bidid}', '{bunryu_no}', @bidid)".format(parent_bidid=parent_bidid, bunryu_no=i2db_cnf['bunryu_no']) res = self.dbconn_BI.sql_exec(query, "D") if res == False: return '' bidid = self.dbconn_BI.sql_exec("SELECT @bidid", "S_one") return bidid def new_normal(self, i2db_cnf): ischild = self.isdup(i2db_cnf, 'child') bidid = self.bulk(ischild, i2db_cnf) #bulk에서 bidid 또는 ''을 반환 return bidid def bulk(self,ismod, i2db_cnf): proc = i2db_cnf['proc'] if ismod == False: query = "CALL usp_bid_proc('', '{proc}', @bidid)".format(proc=proc) res = self.dbconn_BI.sql_exec(query, "D") bidid = self.dbconn_BI.sql_exec("SELECT @bidid", "S_one") else: print("@여기@") last_bidid = self.load_last_bidid(i2db_cnf, 'bidid') if proc == 'B': proc = 'M' query = "CALL usp_bid_proc('{last_bidid}', '{proc}', @bidid)".format(last_bidid=last_bidid, proc=proc) res = self.dbconn_BI.sql_exec(query, "D") bidid = self.dbconn_BI.sql_exec("SELECT @bidid", "S_one") if bidid is None or bidid == '': print("벌크데이터생성실패") #return '' bidid='' return bidid ####################################################################### ########################순공사 원가 ###########scraplib로 옯김############ ####################################################################### def gonggo_opt_stting(self, proc, data, bidid ): #bid_key = data['bid_key'] if data['bid_key'] is not None else None #bid_local = data['bid_local'] if data['bid_local'] is not None else None bid_key = data['bid_key'] if "bid_key" in data else [] bid_local = data['bid_local'] if "bid_local" in data else [] chasu = '' if bidid is not None: chasu = re.match('[A-Z0-9]{15}-([0-9]{2})-[0-9]{2}-[0-9]{2}', bidid) chasu = int(chasu.group(1)) opt_arr = self.Etl.conv_bin(bid_key['opt']) if bid_key['opt'] is not None else [] print("옵션셋") #####발주처별세팅####### if bid_key['whereis'] == '17': if "explaindt" in bid_key and bid_key['explaindt'] is not None: opt_arr.append(3) if "convention" in bid_key and bid_key['convention'] is not None and bid_key['convention'] == '1': opt_arr.append(8) ######공통######### #opt_arr.append(3) if chasu != '': if proc == 'M' or proc == 'B': if int(chasu) > 0: opt_arr.append(1) if proc == 'S' or proc == 'F': opt_arr.append(5) if proc == 'C': opt_arr.append(16) if proc == 'R': opt_arr.append(17) if proc == 'L': opt_arr.append(1) #정정추가해줌 opt_arr.append(18) if len(bid_local)>0: opt_arr.append(11) opt = self.Etl.pow_sum(list(set(opt_arr))) return opt def gonggo_constnm_stting(self, proc, data, bidid): #bid_key = data['bid_key'] if data['bid_key'] is not None else None #bid_local = data['bid_local'] if data['bid_local'] is not None else None bid_key = data['bid_key'] if "bid_key" in data else [] bid_local = data['bid_local'] if "bid_local" in data else [] chasu = '' rebid_no = '' if bidid is not None: chasu = re.match('[A-Z0-9]{15}-([0-9]{2})-[0-9]{2}-[0-9]{2}', bidid) rebid_no = re.match('[A-Z0-9]{15}-[0-9]{2}-([0-9]{2})-[0-9]{2}', bidid) chasu = int(chasu.group(1)) rebid_no = int(rebid_no.group(1)) print("공고명") constnm_ext_arr = [] if bid_key['whereis'] == '10': constnm_ext_arr.append(bid_key['notinum_ex']) if proc == 'C': constnm_ext_arr.append('취소') if proc == 'L': constnm_ext_arr.append('연기공고') if "bidcls" in bid_key and bid_key['bidcls'] is not None and bid_key['bidcls'] == '04': constnm_ext_arr.append('방문입찰') if "bidcls" in bid_key and bid_key['bidcls'] is not None and bid_key['bidcls'] == '00': constnm_ext_arr.append('직찰') if "conlevel" in bid_key and bid_key['conlevel'] is not None and bid_key['conlevel'] != '0' and bid_key['conlevel'] != '': constnm_ext_arr.append('{conlevel}등급'.format(conlevel=bid_key['conlevel'])) if len(bid_local) > 0: # {"0": {"code": "4722", "name": "경상북도 포항시"}} for idx, value in enumerate(bid_local): if bid_local[value] is not None: bid_local_arr = bid_local[value]['name'].split(" ") if len(bid_local_arr) > 1: constnm_ext_arr.append(bid_local_arr[1]) if chasu != '' and proc != 'C': if chasu > 2: constnm_ext_arr.append('{chasu}차정정'.format(chasu=chasu)) if chasu == 2: if bid_key['whereis'] == '01' or bid_key['whereis'] == '03': constnm_ext_arr.append('재정정') if rebid_no != '' and proc == 'R': if rebid_no == 1: constnm_ext_arr.append('재입찰') if rebid_no > 1: constnm_ext_arr.append('{rebid_no}차재입찰'.format(rebid_no=rebid_no)) if bid_key['constnm'].find("주계약자관리방식") != -1: constnm_ext_arr.append('주계약자관리방식') constnm_arr = bid_key['constnm'].split("//") if len(constnm_ext_arr) > 0: constnm = constnm_arr[0]+"//" for idx, value in enumerate(constnm_ext_arr): constnm = constnm+"("+constnm_ext_arr[idx]+")" else: constnm = bid_key['constnm'] return constnm def gonggo_concode_stting(self, data): print("gonggo_concode_stting") bid_key = data['bid_key'] if "bid_key" in data else [] concode = '' if bid_key['concode'] is not None: concode = bid_key['concode'] if bid_key['concode'].find("C001") != -1 and bid_key['concode'].find("C003") == -1: concode = bid_key['concode']+"|C003" if bid_key['concode'].find("C002") != -1 and bid_key['concode'].find("C003") == -1: concode = bid_key['concode']+"|C003" return concode #unlock 함수 def unlock(self, bidid): table_query = "CALL usp_bid_unlock('{bidid}')".format(bidid=bidid) self.dbconn_BI.sql_exec(table_query, "D") def insert_bid_err(self, bidid, bid_err=[]): # bid_error if len(bid_err) > 0: for value in bid_err: _bid_err = { "key": {"bidid": bidid}, "table": "bid_error", "type": "insertonly", "value": {"err_code": value, "writedt":"NOW()", "bidid":bidid}, "del_col": [] } self.dbconn_BI.Upsert_table(_bid_err) def auto_service_set(item_bidkey, pct_org): # print("[auto_service_set] 공고 미입력 자동 서비스 Start") # ================================================== # # 용역 특정 면허일경우 자동수집되도록 처리 진행 # 1. 지정된 면허가 단일로 나왔을 경우, 시스템상 지역정보가 없으면 전국을 찍어준다. (자동수집이여서 지역이 서비스 되야하기 때문) # 2. 개찰일은 있으나 투찰마감일이 없을경우, 투찰마감일에 개찰일을 넣어준다. # item_bidkey['location'] # item_bidkey['closedt'] = closedt # item_bidkey['constdt'] = constdt # S045 (소독), S048 (세탁물), S049 (장의/분묘), S055 (학술/연구), S057 (여행/휴양/관광), S064 (근로자파견), S065 (보험), S066 (회계), S074 (교육관련), # 공고문 자동수집으로 면허가 추가될경우 단일공고가 아니게 되는 예외사항이 발생, log를 쌓고 공고문 자동수집에서 변경이 필요하면 데이터를 리셋해준다. # ================================================== # updateData = {} logData = {} update_code = '' update_data = '' Etl = scraplib.Etl() # 용역 비주류 공고 자동화 sercode_auto = {"01"} # k아파트 공고 자동화 (협정일 있는 공고) kapt_auto = {"90"} # =============================== # k아파트 공고 자동화 # =============================== if item_bidkey["whereis"] in kapt_auto: print("[현장설명일시] --> ", item_bidkey["explaindt"]) if item_bidkey["explaindt"]: logData['update_code'] = "explaindt" logData['update_data'] = item_bidkey["explaindt"] logData['updateProgram'] = 'crawl' logData['whereis'] = item_bidkey["whereis"] #=============================== # 용역 비주류 공고 자동화 #=============================== if item_bidkey["whereis"] in sercode_auto: sercode_auto_process = False if item_bidkey["sercode"] is not None: # 1. 비주류 단일 면허이면 자동서비스 필요 True sercode_auto_limit = ['S045', 'S048', 'S049', 'S055', 'S057', 'S064', 'S065', 'S066', 'S074'] if item_bidkey["sercode"] in sercode_auto_limit: sercode_auto_process = True # print("[용역 비주류 면허 포함 True]" + item_bidkey["sercode"]) # 2. 시설, 물품 면허가 있으면 단일 면허로 판단하지 않기로 함, 제외 False if (item_bidkey["concode"] is not None and item_bidkey["concode"]) or (item_bidkey["purcode"] is not None and item_bidkey["purcode"]): sercode_auto_process = False # print("[시설, 물품 면허 있음 False]") #=========== 테스트 ====================== # 테스트 공고 -> 20230508380-00 # 용역면허 단일 테스트 -> Y # 시설,물품 면허 단일 테스트 -> Y # 지역 세팅 테스트 -> N # 날짜 세팅 테스트 -> Y # 날짜 공백 체크 테스트 -> Y #투찰마감일 #item_bidkey["closedt"] = None #item_bidkey["closedt"] = "0000-00-00 00:00:00" #item_bidkey["closedt"] = "" #개찰일 #item_bidkey["constdt"] = None #item_bidkey["constdt"] = "0000-00-00 00:00:00" #item_bidkey["constdt"] = "" #지역 #item_bidkey["location"] = None #item_bidkey["location"] = "" #item_bidkey["location"] = 1 #직찰공고 #item_bidkey["bidcls"] = "00" # =========== 테스트 ====================== if sercode_auto_process == True: # print("자동 서비스 조건 OOOOOOOO") # 3. 직찰공고이면서, 투찰마감일이 없고 개찰일만 있을경우 투찰마감일에 개찰일 넣어줌 if item_bidkey["bidcls"] == "00" and not Etl.null_turn(item_bidkey["closedt"]) and Etl.null_turn(item_bidkey["constdt"]): # print("개찰일 세팅") updateData["closedt"] = item_bidkey["constdt"] update_code = auto_service_err_set(update_code, 'C') update_data = auto_service_err_set(update_data, 'closedt:'+item_bidkey["constdt"]) # 4. 지역정보가 없을 경우 전국으로 세팅 if not item_bidkey["location"]: # print("전국 세팅") updateData["location"] = 1 update_code = auto_service_err_set(update_code, 'L') update_data = auto_service_err_set(update_data, 'location:1') # 5. 투찰율 정보가 없을 경우 투찰율 세팅 if not item_bidkey["pct"] and pct_org: updateData["pct"] = str(pct_org) update_code = auto_service_err_set(update_code, 'P') update_data = auto_service_err_set(update_data, 'pct:'+str(pct_org)) if update_code and update_data: logData['update_code'] = update_code logData['update_data'] = update_data logData['updateProgram'] = 'crawl' else: # print("[자동 서비스 조건 XXXXXXX]") pass # print("[지역]" + str(item_bidkey["location"])) # print("[개찰일]" + item_bidkey["constdt"]) # print("[투찰마감일]" + item_bidkey["closedt"]) # print("[auto_service_set] 공고 미입력 자동 서비스 End") return updateData, logData def auto_service_err_set(data, plus_data): if data: data = data + '|' + plus_data else: data = plus_data return data