def download_fileinfo3(id,reportid): #写完临时数据,然后写到pusr_dwf_info,并返回下载文件所需要的参数 url = u"http://www.adrs.org.cn/ADR/ep/PSURService/PSURService/queryFileInfoForJSP" jim = {"funcID":"123","userID":"admin","operations":[{"actionName":"query","count":1,"operationDatas":[{"objectID":id,"classid":"2401"}]}]} _searchdata = send_post_json_me(url, jim) _data = json.loads(_searchdata) DWF_id = _data['ResponseMessage']['operations'][0]['count'] for i in range(0, DWF_id): # p0 = _data['ResponseMessage']['operations'][0]['operationDatas'][i]['es'][0]['v'] p1 = _data['ResponseMessage']['operations'][0]['operationDatas'][i]['es'][1]['v'] p2 = _data['ResponseMessage']['operations'][0]['operationDatas'][i]['es'][2]['v'] p7 = _data['ResponseMessage']['operations'][0]['operationDatas'][i]['es'][7]['v'] p8 = _data['ResponseMessage']['operations'][0]['operationDatas'][i]['es'][8]['v'] p11 = _data['ResponseMessage']['operations'][0]['operationDatas'][i]['es'][11]['v'] p12 = _data['ResponseMessage']['operations'][0]['operationDatas'][i]['es'][12]['v'] p13 = _data['ResponseMessage']['operations'][0]['operationDatas'][i]['es'][13]['v'] p16 = _data['ResponseMessage']['operations'][0]['operationDatas'][i]['es'][16]['v'] data_pusr_dwf_info = [p0, p1, p2, p7, p8, p11, p12, p13, p16,reportid] pusr_dwf_info_sql = ( "insert into pusr_dwfinfo_down (FileType,UploadDepartmentName,AttachmentFileType,FileName,UpLoadDate,FilePath,FileSize,FileID,ShowFileName,ReportID) " "value(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" ) mdrsql.mdr_insert_alone(pusr_dwf_info_sql, data_pusr_dwf_info)
def write_DWF(sometest, showid, id): #dwf = bussness.psur_DWF_info(sometest) evalinfo = psur_DWF_info(sometest) einfoid = len(evalinfo) if einfoid == 1: #eidata = evalinfo[0] #BackUp1 eidata = [id, evalinfo[0], showid] pusr_eval_info = ( "insert into pusr_dwf_info (ReportID,BackUp1,BackUp2) " "value(%s,%s,%s)") mdrsql.mdr_insert_alone(pusr_eval_info, eidata) else: n = 6 dataout = [evalinfo[i:i + n] for i in range(0, len(evalinfo), n)] pusr_eval_info = ( "insert into pusr_dwf_info (ReportID,IteratorID,FileName,FileType,FileInfo,UpLoadDate,UploadDepartmentName,BackUp2) " "value(%s,%s,%s,%s,%s,%s,%s,%s)") for dodata in dataout: eidata = [ id, dodata[0], dodata[1], dodata[2], dodata[3], dodata[4], dodata[5], showid ] mdrsql.mdr_insert_alone(pusr_eval_info, eidata)
def crawler_adr_fullidlist(sometime): # page_idx = 1 step = 100 end_time = datetime.datetime.strptime(sometime, "%Y-%m-%d") start_time = datetime.datetime.strptime(sometime, "%Y-%m-%d") start_time = start_time.strftime("%a %b %d %Y 00:00:00 GMT+0800") end_time = end_time.strftime("%a %b %d %Y 00:00:00 GMT+0800") filters = { "beginTime": start_time, "endTime": end_time } start_pos = 0 for res_page in login_new_adr.get_page_list(filters,start_pos, step): page_idx += 1 business_idx = 1 for qrow in res_page: business_idx += 1 show_id = qrow["report_id"] bianma = qrow["report_id2"] #编码 fungible_name = qrow["personal_his"] #代报单位 report_unit_name = qrow["report_unit_name"] #报告单位 medic_list = qrow["general_name"] #通用名称,用药集合 adr_list = qrow["adr_name"] #不良反应名称 data_source = qrow["data_source"] #个例来源 report_type = qrow["new_flag"] #报告类型 StateReportDate = qrow["report_date"] #国家中心接收时间 insert_sql = u'insert into adr_full_id_list(report_id,report_id2,personal_his,report_unit_name,general_name,adr_name,data_source,new_flag,report_date)values(%s,%s,%s,%s,%s,%s,%s,%s,%s)' mdrsql.mdr_insert_alone(insert_sql,[show_id,bianma,fungible_name,report_unit_name,medic_list,adr_list,data_source,report_type,StateReportDate])
def import_GMP_Info(data,company_name,deviceInfo): device_sql = "SELECT manufacturer_name_cn,province,city,district FROM `mdr_gmp` where manufacturer_name_cn='%s' limit 1" % ( company_name ) rows_device = mdrsql.mdr_select(device_sql) if rows_device: IsMatchingFactory = u'是' UnMatchFactory = "" rows_device_gmp = rows_device[0] StandardFactory = rows_device_gmp[0] manufacturerProvinceName = rows_device_gmp[1] manufacturerCity = rows_device_gmp[2] manufacturerCounty = rows_device_gmp[3] else: IsMatchingFactory = u'否' UnMatchFactory = company_name StandardFactory = "" manufacturerProvinceName = "" manufacturerCity = "" manufacturerCounty = "" device_sql = ( """ replace into mdr_devicebusiness( BianMa, ProvinceName, District, County, ReportUnitName, ReportUnitAddress,ReportUnitTel,Postalcode,UnitType, HappenDate,KnowDate,ReportDate,AcceptDate,StateReportDate, State,CertificateNumber,TradeName,classification,firstreason, firstdone,useplace,specifications,Productnumber,Batchnumber, operator,ImplantationDate,StopDate,EffectiveDate, manufacturer_address,manufacturer_tel,IsMatchingDevice,DeviceStandard, PName,SubName,Name,manageclass,classcode, UnMatchDevice, IsMatchingFactory, StandardFactory,manufacturerProvinceName, manufacturerCity, manufacturerCounty,UnMatchFactory) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)""" ) device_data = [ data["BianMa"], data["ProvinceName"], data["District"], data["County"], data["ReportUnitName"], data["ReportUnitAddress"], data["ReportUnitTel"], data["Postalcode"], data["UnitType"], data["HappenDate"], data["KnowDate"], data["ReportDate"], data["ReportDate"], data["StateReportDate"], data["State"], data["CertificateNumber"], data["TradeName"], data["classification"], data["firstreason"], data["firstdone"], data["useplace"], data["specifications"], data["Productnumber"], data["Batchnumber"], data["operator"], data["ImplantationDate"], data["StopDate"], data["EffectiveDate"], data["manufacturer_address"], data["manufacturer_tel"], data["IsMatchingDevice"], data["DeviceStandard"], deviceInfo["SuperClassName"], deviceInfo["subname"], data["DeviceStandard"], deviceInfo["manageclass"], deviceInfo["classcode"], data["UnMatchDevice"], IsMatchingFactory, StandardFactory, manufacturerProvinceName, manufacturerCity, manufacturerCounty, UnMatchFactory] mdrsql.mdr_insert_alone(device_sql, device_data) return (IsMatchingFactory,UnMatchFactory)
def mdr_falt_business(qg_info, name): qg_set = [] sanlei = [] IsMatchingFault = None IsADRorAccident_tag = None for SuperFaultName in qg_info: qg_sql = ( "SELECT SuperClassName,subname FROM `mdr_deviceinstrument` where Name='%s' limit 1" % name ) rows_qg = mdrsql.mdr_select(qg_sql) if rows_qg: IsMatchingFault = u'是' for row_qg_data in rows_qg: s_name_1 = row_qg_data[0] s_name_2 = row_qg_data[1] compose_data = s_name_1+':'+s_name_2+':'+name accident_sql = ( "replace into mdr_fault(SuperClassName,SubName,Name,NonStandardName,IsDeviceMatching,RealFaultName) " "values(%s,%s,%s,%s,%s,%s)" ) accident_data = (s_name_1, s_name_2, name, None, u'是', SuperFaultName) mdrsql.mdr_insert_alone(accident_sql, accident_data) qg_set.append(SuperFaultName) qg_setdata = utils.data_set(qg_set) UnMatchFault = "" IsADRorAccident_tag = "2" sanlei.append(s_name_1) sanlei.append(s_name_2) sanlei.append(name) else: accident_sql = ( "replace into mdr_fault(SuperClassName,SubName,Name,NonStandardName,IsDeviceMatching,RealFaultName) " "values(%s,%s,%s,%s,%s,%s)" ) accident_data = (None, None, None, name, u'否', SuperFaultName) mdrsql.mdr_insert_alone(accident_sql, accident_data) IsMatchingFault = u'是' UnMatchFault = utils.str_to_unicode(SuperFaultName) qg_set.append(UnMatchFault) qg_setdata = utils.data_set(qg_set) sanlei.append(name) # qg_data = ( # data["BianMa"], data["ProvinceName"], data["District"], data["County"], # data["ReportUnitName"], data["ReportUnitAddress"], data["ReportUnitTel"], data["Postalcode"], # data["UnitType"], data["HappenDate"], data["KnowDate"], data["ReportDate"], # data["ReportDate"], data["StateReportDate"], data["State"],StandardFault, # StandardFault, IsMatchingFault, SuperFaultName, UnMatchFault) # qg_sql = ( # "replace into mdr_faultbusiness(BianMa,ProvinceName,District,County,ReportUnitName,ReportUnitAddress,ReportUnitTel,Postalcode,UnitType ,HappenDate,KnowDate,ReportDate,AcceptDate,StateReportDate,State,StandardFault,Name,IsMatchingFault,SuperFaultName,UnMatchFault)" # "values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" # ) # mdrsql.mdr_insert_alone(qg_sql, qg_data) return (IsMatchingFault, IsADRorAccident_tag, qg_setdata, sanlei)
def get_sh_info(sh_info, data): sh_set = [] is_adr = None clinicdetail_Name = None clinicdetail_SubID = None clinicsub_ID = None clinicsub_Name = None clinic_ID = None clinic_NAME = None IsADRorAccident_tag = "" for sh_info_item in sh_info: sh_query_sql = "SELECT clinicdetail.SubID, clinicdetail.Name,clinicsub.ID,clinicsub.Name,clinic.ID,clinic.NAME FROM clinicdetail, clinicsub,clinic WHERE clinicdetail.SubID=clinicsub.ID and clinicsub.PID=clinic.ID and clinicdetail.Name= '%s' limit 1" %(sh_info_item) rows_sh = mdrsql.mdr_select(sh_query_sql) if rows_sh: is_adr = u'是' for row_sh_data in rows_sh: #sh_s_name = row_sh_data[0] clinicdetail_Name = row_sh_data[1] clinicdetail_SubID = row_sh_data[0] clinicsub_ID = row_sh_data[2] clinicsub_Name = row_sh_data[3] clinic_ID = row_sh_data[4] clinic_NAME = row_sh_data[5] sh_set.append(sh_info_item) adr_data_list = sh_info_item IsADRorAccident_tag = "1" _un_sh_info = "" else: is_adr = u'否' _un_sh_info = u"[非标准:" + sh_info_item + u"]" sh_set.append(_un_sh_info) adr_data_list = utils.data_set(sh_set) #sh_s_name = "" clinicdetail_Name = "" clinicdetail_SubID = "" clinicsub_ID = "" clinicsub_Name = "" clinic_ID = "" clinic_NAME = "" sh_data = [ data["BianMa"], data["ProvinceName"], data["District"], data["County"], data["ReportUnitName"], data["ReportUnitAddress"], data["ReportUnitTel"], data["Postalcode"], data["UnitType"], data["HappenDate"], data["KnowDate"], data["ReportDate"], data["ReportDate"], data["StateReportDate"], data["State"], is_adr, clinicdetail_Name, clinicdetail_Name, clinicdetail_SubID, clinicsub_ID, clinicsub_Name, clinic_ID, clinic_NAME, _un_sh_info] sh_sql = ( "replace into mdr_adrbusiness(BianMa,ProvinceName,District,County,ReportUnitName,ReportUnitAddress,ReportUnitTel,Postalcode,UnitType ,HappenDate,KnowDate,ReportDate,AcceptDate,StateReportDate,State,IsMatchingADR,ADRStandardID,Name,SID1,SubID,SubName,PID,PName,UnMatchADR)" "values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" ) mdrsql.mdr_insert_alone(sh_sql, sh_data) return (is_adr, adr_data_list,IsADRorAccident_tag)
def get_icd_info(data,icd_ok): icd_standname = "" icd_set = [] IsMatchingAffect = None UnMatchAffect = None icd_setdata = None _un_tag_icd_1 = utils.str_to_unicode("[非标准:") _un_tag_icd_2 = utils.str_to_unicode("]") for item in icd_ok: #_item = item.strip(_trim_tag).strip() icd_sql = ( "SELECT StandardIcdName,icd_a_name,icd_b_name,icd_c_name,PathName FROM `mdr_icd` where StandardIcdName='%s' limit 1" %(item) ) rows_icd = mdrsql.mdr_select(icd_sql) if rows_icd: IsMatchingAffect = u'是' UnMatchAffect = "" for icd_ok_info in rows_icd: icd_a_name = icd_ok_info[0] icd_b_name = icd_ok_info[1] AffectStandardName = icd_ok_info[2] PathName = icd_ok_info[3] icd_set.append(item) icd_setdata = item icd_standname = AffectStandardName else: IsMatchingAffect = u'否' un_icd_info = _un_tag_icd_1+item+_un_tag_icd_2 _un_icd_info = utils.str_to_unicode(un_icd_info) UnMatchAffect = _un_icd_info icd_a_name = "" icd_b_name = "" AffectStandardName = "" PathName = "" icd_standname = AffectStandardName icd_set.append(UnMatchAffect) icd_setdata = utils.data_set(icd_set) icd_sql = ( "replace into mdr_icdbusiness(BianMa,ProvinceName,District,County,ReportUnitName,ReportUnitAddress,ReportUnitTel,Postalcode,UnitType ,HappenDate,KnowDate,ReportDate,AcceptDate,StateReportDate,State,IsMatchingAffect,AffectStandardName,icd_a_name,icd_b_name,icd_c_name,PathName,UnMatchAffect)" "values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" ) icd_data = [ data["BianMa"], data["ProvinceName"], data["District"], data["County"], data["ReportUnitName"], data["ReportUnitAddress"], data["ReportUnitTel"], data["Postalcode"], data["UnitType"], data["HappenDate"], data["KnowDate"], data["ReportDate"], data["ReportDate"], data["StateReportDate"], data["State"],IsMatchingAffect, AffectStandardName, icd_a_name, icd_b_name, AffectStandardName, PathName, UnMatchAffect] mdrsql.mdr_insert_alone(icd_sql, icd_data) return (IsMatchingAffect, UnMatchAffect,icd_setdata)
def download_fileinfo3(id, reportid): #写完临时数据,然后写到pusr_dwf_info,并返回下载文件所需要的参数 url = u"http://www.adrs.org.cn/ADR/ep/PSURService/PSURService/queryFileInfoForJSP" jim = { "funcID": "123", "userID": "admin", "operations": [{ "actionName": "query", "count": 1, "operationDatas": [{ "objectID": id, "classid": "2401" }] }] } _searchdata = send_post_json_me(url, jim) _data = json.loads(_searchdata) DWF_id = _data['ResponseMessage']['operations'][0]['count'] for i in range(0, DWF_id): # p0 = _data['ResponseMessage']['operations'][0]['operationDatas'][i][ 'es'][0]['v'] p1 = _data['ResponseMessage']['operations'][0]['operationDatas'][i][ 'es'][1]['v'] p2 = _data['ResponseMessage']['operations'][0]['operationDatas'][i][ 'es'][2]['v'] p7 = _data['ResponseMessage']['operations'][0]['operationDatas'][i][ 'es'][7]['v'] p8 = _data['ResponseMessage']['operations'][0]['operationDatas'][i][ 'es'][8]['v'] p11 = _data['ResponseMessage']['operations'][0]['operationDatas'][i][ 'es'][11]['v'] p12 = _data['ResponseMessage']['operations'][0]['operationDatas'][i][ 'es'][12]['v'] p13 = _data['ResponseMessage']['operations'][0]['operationDatas'][i][ 'es'][13]['v'] p16 = _data['ResponseMessage']['operations'][0]['operationDatas'][i][ 'es'][16]['v'] data_pusr_dwf_info = [p0, p1, p2, p7, p8, p11, p12, p13, p16, reportid] pusr_dwf_info_sql = ( "insert into pusr_dwfinfo_down (FileType,UploadDepartmentName,AttachmentFileType,FileName,UpLoadDate,FilePath,FileSize,FileID,ShowFileName,ReportID) " "value(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)") mdrsql.mdr_insert_alone(pusr_dwf_info_sql, data_pusr_dwf_info)
def write_ChangeInfo(sometest, showid, id): evalinfo = psur_changelog_info(sometest) einfoid = len(evalinfo) if einfoid == 1: #eidata = evalinfo[0] #BackUp1 eidata = [id, evalinfo[0], showid] pusr_eval_info = ( "insert into pusr_changelog_info (ReportID,BackUp1,BackUp2) " "value(%s,%s,%s)" ) mdrsql.mdr_insert_alone(pusr_eval_info, eidata) else: n = 6 dataout = [evalinfo[i:i+n] for i in range(0, len(evalinfo), n)] pusr_eval_info = ( "insert into pusr_changelog_info (ReportID,IteratorID,ChangePeopleName,ChangeType,ChangeDate,ChangeValue,ChangeOperator,BackUp2) " "value(%s,%s,%s,%s,%s,%s,%s,%s)" ) for dodata in dataout: eidata = [id, dodata[0], dodata[1],dodata[2],dodata[3],dodata[4],dodata[5],showid] mdrsql.mdr_insert_alone(pusr_eval_info, eidata)
def write_Eval(sometest, showid, id): evalinfo = psur_eval_info(sometest) einfoid = len(evalinfo) if einfoid == 1: #eidata = evalinfo[0] #BackUp1 eidata = [id, evalinfo[0], showid] pusr_eval_info = ( "insert into pusr_eval_info (ReportID,BackUp1,BackUp2) " "value(%s,%s,%s)" ) mdrsql.mdr_insert_alone(pusr_eval_info, eidata) else: n = 6 dataout = [evalinfo[i:i+n] for i in range(0, len(evalinfo), n)] pusr_eval_info = ( "insert into pusr_eval_info (ReportID,EvalUity,EvalValue,EvalInfo,EvalDate,Evalpeople,FileName,BackUp2) " "value(%s,%s,%s,%s,%s,%s,%s,%s)" ) for dodata in dataout: eidata = [id, dodata[0], dodata[1],dodata[2],dodata[3],dodata[4],dodata[5],showid] mdrsql.mdr_insert_alone(pusr_eval_info, eidata)
def write_DWF(sometest, showid, id): #dwf = bussness.psur_DWF_info(sometest) evalinfo = psur_DWF_info(sometest) einfoid = len(evalinfo) if einfoid == 1: #eidata = evalinfo[0] #BackUp1 eidata = [id, evalinfo[0], showid] pusr_eval_info = ( "insert into pusr_dwf_info (ReportID,BackUp1,BackUp2) " "value(%s,%s,%s)" ) mdrsql.mdr_insert_alone(pusr_eval_info, eidata) else: n = 6 dataout = [evalinfo[i:i+n] for i in range(0, len(evalinfo), n)] pusr_eval_info = ( "insert into pusr_dwf_info (ReportID,IteratorID,FileName,FileType,FileInfo,UpLoadDate,UploadDepartmentName,BackUp2) " "value(%s,%s,%s,%s,%s,%s,%s,%s)" ) for dodata in dataout: eidata = [id, dodata[0], dodata[1],dodata[2],dodata[3],dodata[4],dodata[5],showid] mdrsql.mdr_insert_alone(pusr_eval_info, eidata)
def write_ChangeInfo(sometest, showid, id): evalinfo = psur_changelog_info(sometest) einfoid = len(evalinfo) if einfoid == 1: #eidata = evalinfo[0] #BackUp1 eidata = [id, evalinfo[0], showid] pusr_eval_info = ( "insert into pusr_changelog_info (ReportID,BackUp1,BackUp2) " "value(%s,%s,%s)") mdrsql.mdr_insert_alone(pusr_eval_info, eidata) else: n = 6 dataout = [evalinfo[i:i + n] for i in range(0, len(evalinfo), n)] pusr_eval_info = ( "insert into pusr_changelog_info (ReportID,IteratorID,ChangePeopleName,ChangeType,ChangeDate,ChangeValue,ChangeOperator,BackUp2) " "value(%s,%s,%s,%s,%s,%s,%s,%s)") for dodata in dataout: eidata = [ id, dodata[0], dodata[1], dodata[2], dodata[3], dodata[4], dodata[5], showid ] mdrsql.mdr_insert_alone(pusr_eval_info, eidata)
def write_Eval(sometest, showid, id): evalinfo = psur_eval_info(sometest) einfoid = len(evalinfo) if einfoid == 1: #eidata = evalinfo[0] #BackUp1 eidata = [id, evalinfo[0], showid] pusr_eval_info = ( "insert into pusr_eval_info (ReportID,BackUp1,BackUp2) " "value(%s,%s,%s)") mdrsql.mdr_insert_alone(pusr_eval_info, eidata) else: n = 6 dataout = [evalinfo[i:i + n] for i in range(0, len(evalinfo), n)] pusr_eval_info = ( "insert into pusr_eval_info (ReportID,EvalUity,EvalValue,EvalInfo,EvalDate,Evalpeople,FileName,BackUp2) " "value(%s,%s,%s,%s,%s,%s,%s,%s)") for dodata in dataout: eidata = [ id, dodata[0], dodata[1], dodata[2], dodata[3], dodata[4], dodata[5], showid ] mdrsql.mdr_insert_alone(pusr_eval_info, eidata)
def PSUR_Report(_timedict): # t = _timedict t_start = t['beginTime'] t_end = t['endTime'] RF_list = gather_report_total(_timedict) for i in range(0, RF_list): meid = i*100 print "meid:", meid jim = {"funcID":"QUERY_DATA","userID":78919,"operations":[{"actionName":"query","operationDatas":[{"ENTRY_START_DATE_2401":t_start,"ENTRY_END_DATE_2401":t_end,"ACTIVE_CONSTITUENT_2430":"","TRADENAME_CN_2431":"","DRUGNAME_CN_FORM_2431":"","DOSEFORM_2431":"","DRUG_STATUS_2431":"","APPROVAL_NO_2431":"","REPORT_ID_2430":"","PICKER_UNIT_NAME_2430":"","START_APPLY_DATE_2430":"","END_APPLY_DATE_2430":"","listid":"2430_1","start":meid,"limit":100}]}]} #查询结果列表 try: _searchdata = send_post_json_me(Psur_url, jim) _data = json.loads(_searchdata) #print 'json:', _data searchdataid = _data['ResponseMessage']['operations'][0]['count'] for i in range(0, searchdataid): PICKER_UNIT_ADDR = _data['ResponseMessage']['operations'][0]['operationDatas'][i]['es'][0]['v'] REPORT_DATE = _data['ResponseMessage']['operations'][0]['operationDatas'][i]['es'][2]['v'] PICKER_UNIT_FAX = _data['ResponseMessage']['operations'][0]['operationDatas'][i]['es'][3]['v'] PICKER_UNIT_TEL = _data['ResponseMessage']['operations'][0]['operationDatas'][i]['es'][6]['v'] DRUG_TYPE_NAME = _data['ResponseMessage']['operations'][0]['operationDatas'][i]['es'][7]['v'] ACTIVE_CONSTITUENT = _data['ResponseMessage']['operations'][0]['operationDatas'][i]['es'][10]['v'] OBJ_ID = _data['ResponseMessage']['operations'][0]['operationDatas'][i]['es'][13]['v'] INTERNATION_BEGIN_DATE = _data['ResponseMessage']['operations'][0]['operationDatas'][i]['es'][15]['v'] PSUR_CONCLUSION = _data['ResponseMessage']['operations'][0]['operationDatas'][i]['es'][17]['v'] PROD_SOURCE_2430_SHOW = _data['ResponseMessage']['operations'][0]['operationDatas'][i]['es'][18]['v'] ENTRY_DATE = _data['ResponseMessage']['operations'][0]['operationDatas'][i]['es'][19]['v'] PROD_DESC = _data['ResponseMessage']['operations'][0]['operationDatas'][i]['es'][21]['v'] INDICATIONS = _data['ResponseMessage']['operations'][0]['operationDatas'][i]['es'][25]['v'] PICKER_UNIT_LINKMAN = _data['ResponseMessage']['operations'][0]['operationDatas'][i]['es'][26]['v'] PICKER_UNIT_NAME = _data['ResponseMessage']['operations'][0]['operationDatas'][i]['es'][28]['v'] PICKER_UNIT_EMAIL = _data['ResponseMessage']['operations'][0]['operationDatas'][i]['es'][29]['v'] PICKER_UNIT_DEPT = _data['ResponseMessage']['operations'][0]['operationDatas'][i]['es'][31]['v'] PICKER_SIGN = _data['ResponseMessage']['operations'][0]['operationDatas'][i]['es'][32]['v'] PICKER_UNIT_POST = _data['ResponseMessage']['operations'][0]['operationDatas'][i]['es'][34]['v'] DATA_END_DATE = _data['ResponseMessage']['operations'][0]['operationDatas'][i]['es'][35]['v'] REPORT_ID = _data['ResponseMessage']['operations'][0]['operationDatas'][i]['es'][36]['v'] DRUGNAME_CN_FORM = _data['ResponseMessage']['operations'][0]['operationDatas'][i]['es'][38]['v'] DATA_START_DATE = _data['ResponseMessage']['operations'][0]['operationDatas'][i]['es'][40]['v'] tempdata = ( REPORT_ID, #36,报告编码 DRUG_TYPE_NAME, #7,药品分类 ACTIVE_CONSTITUENT, #10,活性成分 PROD_SOURCE_2430_SHOW, #18,国产/进口 DRUGNAME_CN_FORM, #38,通用名称 DATA_START_DATE, #41,数据起日期 DATA_END_DATE, #35,数据止日期 PICKER_UNIT_NAME, #28,报告单位名称 ENTRY_DATE, #19,国家中心接收时间 OBJ_ID #13 ) #新添加: #1.判断已存在,则不再添加 #2.为了解决下载error中的数据,添加标识 has_sql = "select ReportID from pusr_business where ReportID='%s'" % (REPORT_ID) hasset = mdrsql.mdr_select(has_sql) if hasset: #print hasset print u"已存在此报告编码:",REPORT_ID continue else: #ReportID, DrugType, CFZC, Classify, TYMC, Start_Date, End_Date, FactoryName, StateReportDate, BackUp1 #查询数据写入 print "ENTRY_DATE:",ENTRY_DATE insert_sql = ( "insert into pusr_query (ReportID, DrugType, CFZC, Classify, TYMC, Start_Date, End_Date, FactoryName, StateReportDate, BackUp1) " "value(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" ) mdrsql.mdr_insert_alone(insert_sql, tempdata) #{ ylname = None code = None myname = None ylycn = None ymcn_sql = "select distinct ylname,code,myname,ylycn from ym where ymcn='%s'" % (DRUGNAME_CN_FORM) ymcndataset = mdrsql.mdr_select(ymcn_sql) if ymcndataset: for ymcndata in ymcndataset: ylname = ymcndata[0] code = ymcndata[1] myname = ymcndata[2] ylycn = ymcndata[3] else: ylname = "" code = "" myname = "" ylycn = "" #} pname =None dtypeID = None localdata_sql = "select distinct ProvinceName,DistrictTypeID from unit where Name='%s'" % (PICKER_UNIT_NAME) localdataset = mdrsql.mdr_select(localdata_sql) if localdataset: for localdata in localdataset: pname = localdata[0] dtypeID = localdata[1] else: pname = "" dtypeID = "" boss_add_data = [ylname, code, myname, ylycn, pname, dtypeID] #主表信息写入 reportdata =[ # #报告表编码 36 REPORT_ID, #国际诞生日 15 INTERNATION_BEGIN_DATE, #活性成分(处方组成) 10 ACTIVE_CONSTITUENT, #药品分类 7 DRUG_TYPE_NAME, #国产/进口 18 PROD_SOURCE_2430_SHOW, #适应症(功能主治) 25 INDICATIONS, #产品情况说明 21 PROD_DESC, #本期报告结论 17 PSUR_CONCLUSION, #报告人 32 PICKER_SIGN, #报告日期 2 REPORT_DATE, #报告单位名称 28 PICKER_UNIT_NAME, #传真 3 PICKER_UNIT_FAX, #报告单位地址 0 PICKER_UNIT_ADDR, #邮政编码 34 PICKER_UNIT_POST, #负责部门 31 PICKER_UNIT_DEPT, #联系电话 6 PICKER_UNIT_TEL, #联系人26 PICKER_UNIT_LINKMAN, #电子邮件 29 PICKER_UNIT_EMAIL, #国家中心接收时间 19 ENTRY_DATE ] #主表信息更新 somedata = get_report(OBJ_ID) #print u"额外数据解析,即返回整个文本",somedata #extrareportdata = data2clean(extradata(somedata)) extrareportdata = extradata2(somedata) dataall = reportdata + extrareportdata + boss_add_data #报告表编码 36 #REPORT_ID,INTERNATION_BEGIN_DATE,ACTIVE_CONSTITUENT,DRUG_TYPE_NAME,PROD_SOURCE_2430_SHOW,INDICATIONS,PROD_DESC,PSUR_CONCLUSION,PICKER_SIGN,REPORT_DATE,PICKER_UNIT_NAME,PICKER_UNIT_FAX,PICKER_UNIT_ADDR, PICKER_UNIT_POST,PICKER_UNIT_DEPT,PICKER_UNIT_TEL,PICKER_UNIT_LINKMAN,PICKER_UNIT_EMAIL,ENTRY_DATE insert_report = ( "insert into pusr_business (REPORTID,INTERNATION_BEGIN_DATE,ACTIVE_CONSTITUEN,DRUG_TYPE_NAME,PROD_SOURCE,INDICATIONS,PROD_DESC,PSUR_CONCLUSION,PICKER_SIGN,REPORT_DATE,PICKER_UNIT_NAME,PICKER_UNIT_FAX,PICKER_UNIT_ADDR, PICKER_UNIT_POST,PICKER_UNIT_DEPT,PICKER_UNIT_TEL,PICKER_UNIT_LINKMAN,PICKER_UNIT_EMAIL,ENTRY_DATE,BGQ,ylname,code,myname,ylycn,ProvinceName,District) " "value(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" ) mdrsql.mdr_insert_alone(insert_report, dataall) print REPORT_ID #psur业务主表批准文号部分11字段1-N数据表 extrainfo = extradata3(somedata) n=11 dataout = [extrainfo[i:i+n] for i in range(0, len(extrainfo), n)] for temp in dataout: d0 = temp[0] d1 = temp[1] d2 = temp[2] if d1: pass else: d1 = "0000-00-00" if d2: pass else: d2 = "0000-00-00" d3 = temp[3] d4 = temp[4] d5 = temp[5] d6 = temp[6] d7 = temp[7] d8 = temp[8] d9= temp[9] d10 = temp[10] extraalldata = [REPORT_ID,d0,d1,d2,d3,d4,d5,d6,d7,d8,d9,d10] insert_report_extra = ( "insert into pusr_business_extradata (REPORTID,RegisterID,RegisterTime,FirstRegisterTime,TYMC,SPMC,DrugMState,GG,JX,BQSC,BQXL,CountUser) " "value(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" ) mdrsql.mdr_insert_alone(insert_report_extra, extraalldata) #评价信息读取 write_Eval(somedata, OBJ_ID, REPORT_ID) #修改信息读取 write_ChangeInfo(somedata, OBJ_ID, REPORT_ID) #显示附件信息读取 write_DWF(somedata, OBJ_ID, REPORT_ID) #下载文件信息写入 download_fileinfo3(OBJ_ID,REPORT_ID) #附件下载 """ if Tag: myautodown(OBJ_ID,REPORT_ID) else: pass """ myautodown(OBJ_ID,REPORT_ID) time.sleep(0.001) except (IndexError, KeyError, ValueError), exception: print "Main:",exception print "main:",_searchdata
def import_from_html(qrow): show_id = qrow["report_id"] bianma = qrow["report_id2"] #编码 fungible_name = qrow["personal_his"] #代报单位 report_unit_name = qrow["report_unit_name"] #报告单位 medic_list = qrow["general_name"] #通用名称,用药集合 adr_list = qrow["adr_name"] #不良反应名称 data_source = qrow["data_source"] #个例来源 report_type = qrow["new_flag"] #报告类型 StateReportDate = qrow["report_date"] #国家中心接收时间 if fungible_name == "null" : fungible_name = "" if report_unit_name == "null" : report_unit_name = "" unit_name = "" if fungible_name != "" : unit_name = fungible_name else : unit_name = report_unit_name #此处应该修改为get_unit_info2(id),id是报表编码 unit_area = get_unit_info(unit_name) #unit_area = get_unit_info2(bianma) #medic_list = clean_invalid_chars(medic_list) #adr_list = clean_invalid_chars(adr_list) conn = database_adr.getConnection() cur = conn.cursor() cur.execute("select count(*) from business_gather where show_id='%s'" % show_id) results = cur.fetchall() ret = results[0] generate_id = 0 if ret[0] > 0 : logging.info("show_id:%s is repeat!" % (show_id)) return #已经导入过了,不再重复导入 else: cur.execute("insert into business_gather_serial(id) values (null)") cur.execute("select LAST_INSERT_ID()") ids = cur.fetchall().pop() generate_id = ids[0] sql = "" try: show_url = REPORT_SHOW_URL % (show_id) body = login_new_adr.send_adr_url(show_url) if not body: print 'get url:%s body is failure!' % show_url return all_fields = adrparseradr_adr.get_all_fields(body) #业务主表 business_data = adrparseradr_adr.get_business_row(all_fields) if "unittype" not in business_data: business_data["unittype"] = "" #track_date match = re_track_date.search(body) if match : business_data["trackdate"] = match.group(1) else: business_data["trackdate"] = "0000-00-00" business_data["id"] = generate_id business_data["show_id"] = show_id business_data["bianma"] = bianma business_data["reporttype"] = report_type business_data["examplesource"] = data_source business_data["statereportdate"] = StateReportDate business_data["fungiblereportunit"] = fungible_name business_data["reportunitname"] = report_unit_name """ if isinstance(unit_area, dict): print "get_unit_info:", unit_area business_data.update(unit_area) else: unit_area = get_unit_info2(bianma) print "get_unit_info2:", unit_area business_data.update(unit_area) """ #患者分类 if not "age" in business_data : print "unknown age! ",show_id business_data["sufferertype"] = "" else : try: age = float(business_data["age"]) if age > 1000 : age = 0 business_data["age"] = age if age < 12 : business_data["sufferertype"] = u"儿童" elif age < 18: business_data["sufferertype"] = u"青少年" elif age < 60: business_data["sufferertype"] = u"成人" else: business_data["sufferertype"] = u"老人" except : business_data["age"] = 0 if "该报告没有上传附件!" in body : business_data["isattachments"] = u"无" else: business_data["isattachments"] = u"是" #重点监控医院标记 business_data["isimporthospital"] = u"否" if business_data["unittype"] == u"医疗机构" : cur.execute("select count(*) from imphospital where hospitalcn='%s' limit 1" % str_to_unicode(business_data["reportunitname"])) result = cur.fetchall().pop() if result[0] > 0 : business_data["isimporthospital"] = u"是" #用药集合 is_matched, medics, my_data = get_Medication_Match(medic_list) if is_matched: business_data["ismedicationmatch"] = u"是" else: business_data["ismedicationmatch"] = u"否" business_data["medicationlist"] = medics #adrlist is_adr_matched, adrs, adr_datas = get_Adr_Match(adr_list) if is_adr_matched: business_data["isadrmatch"] = u"是" else: business_data["isadrmatch"] = u"否" business_data["adrlist"] = adrs if "oldsickness" in business_data: #business_data["oldsickness"] = clean_invalid_chars(business_data["oldsickness"]) business_data["oldsickness"] = str_to_unicode(business_data["oldsickness"]) #公共数据 share_data = {} share_data["id"] = business_data["id"] share_data["bianma"] = str_to_unicode(business_data["bianma"]) share_data["reporttype"] = str_to_unicode(business_data["reporttype"]) share_data["kickbacktype"] = str_to_unicode(business_data["kickbacktype"]) if "kickbacktype" in business_data else "" share_data["reportunitname"] = str_to_unicode(business_data["reportunitname"]) share_data["unittype"] = str_to_unicode(business_data["unittype"]) share_data["happendate"] = str_to_unicode(business_data["happendate"]) if "happendate" in business_data else str_to_unicode("0000-00-00") share_data["reportdate"] = str_to_unicode(business_data["reportdate"]) if "reportdate" in business_data else str_to_unicode("0000-00-00") share_data["statereportdate"] = str_to_unicode(business_data["statereportdate"]) if "statereportdate" in business_data else str_to_unicode("0000-00-00") share_data["trackdate"] = str_to_unicode(business_data["trackdate"]) if "trackdate" in business_data else str_to_unicode("0000-00-00") share_data["isimporthospital"] = str_to_unicode(business_data["isimporthospital"]) share_data["imphospitalname"] = str_to_unicode(business_data["reportunitname"]) unit_area = get_unit_info(unit_name) if isinstance(unit_area, dict): #print "get_unit_info:", unit_area business_data.update(unit_area) else: #unit_area = get_unit_info2(bianma)[0] unit_area = get_unit_info2(bianma) #print "get_unit_info2:", unit_area #business_data.update(unit_area) if unit_area: business_data.update(unit_area) else: pass #新增逻辑,表明 import mdrsql #重复单位不再插入,2014-11-24 #刘欣毅修改 2015-03-20 #屏蔽单位明细表所有检测和add工作 #cur.execute("select Name FROM unitdetail where Name='%s'" % str_to_unicode(unit_name)) #rows = cur.fetchall() #if len(rows) > 0: # pass #else: # sql_id = u"SELECT max(ID) FROM unitdetail" # cur.execute(sql_id) # mydata = cur.fetchall() # maxId = mydata[0][0] # print maxId # if maxId is None : # maxId = 0 # else : # maxId = int(maxId) # maxId = maxId + 1 # data_ud = (unit_name, share_data["unittype"]) # print data_ud # sql_insert_unitdetail = ( # "insert into unitdetail (Name,UnitType) " # "value(%s,%s)" # ) # mdrsql.mdr_insert_alone(sql_insert_unitdetail, data_ud) #conn = database_adr.getConnection() #cur = conn.cursor() #根据编码查询district_code_china.sql找到行政区域信息->组装核心单位信息->把此上报单位插入unit/Detail表 #bianma: 4116271569994201400001 #bianma[0:6]: 411627 print "bianma:", bianma id2 = bianma[0:6] sql = u"select ProvinceName,Name,County,bianma from district_code_china where bianma='%s' limit 1" % str_to_unicode(id2) cur.execute(sql) rows2 = cur.fetchall() if len(rows2) > 0: # import time insert_today = time.strftime('%Y-%m-%d', time.localtime(time.time())) sql_id = u"SELECT max(ID) FROM unit" cur.execute(sql_id) mydata_unit = cur.fetchall() #data_u = (str(mydata_unit[0][0]+1), share_data["unittype"], rows[1], unit_name, rows[0], rows[2], rows[3], u"", u"1", insert_today ) #print "rows:", rows2 #print "mydata_unit[0][0]:", mydata_unit[0][0] #print "share_data[unittype]:",share_data["unittype"] #print "insert_today:", insert_today #print "unit_name:", unit_name print "district_code_china:", rows2[0][0],rows2[0][1], rows2[0][2], rows2[0][3] data_u = (str(int(mydata_unit[0][0])+1), share_data["unittype"], rows2[0][1], unit_name, rows2[0][0], rows2[0][2], rows2[0][3], u"1", insert_today, insert_today) sql_insert_unit = ( "insert into unit (ID,UnitTypeID,DistrictTypeID,Name,ProvinceName,County,CodeID,audit,regDate,auditDate)" "value(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" ) mdrsql.mdr_insert_alone(sql_insert_unit,data_u) else: pass #ID, # UnitTypeID, # DistrictTypeID, # Name, # ProvinceName, # # County, # CodeID, # Address, # audit, # auditDate #不合理用药数据 pass_rows = get_pass_medic(my_data) if len(pass_rows) > 0: business_data["ispass"] = u"是" for _ymcn,row in pass_rows.iteritems() : row.update(share_data) database_adr.insert_to_table("olappass", row) else: business_data["ispass"] = u"否" #ROR判断 ror_datas = get_ror_match(my_data) if len(ror_datas) > 0: business_data["isror"] = u"是" else: business_data["isror"] = u"否" #重点监控药品 imp_yaos = get_import_medic(my_data) business_data["isimportmedication"] = u"否" if len(imp_yaos) > 0 : business_data["isimportmedication"] = u"是" #基本药物 ned_data = get_ned_province(my_data) business_data["isned"] = u"无" if len(ned_data) > 0 : business_data["isned"] = u"有" distrust_Rows = adrparseradr_adr.get_distrust_Rows(all_fields,share_data) #怀疑用药明细 auxiliary_rows = adrparseradr_adr.get_auxiliary_rows(all_fields,share_data) #并用药 leepchdom_data_rows = distrust_Rows + auxiliary_rows for row in leepchdom_data_rows: if "tradename" in row : row["tradename"] = str_to_unicode(row["tradename"].replace("*","")) if "reason" in row : row["reason"] = str_to_unicode(row["reason"].replace("*","")) factory_name = None if "standardfactory" in row : #row["standardfactory"] = clean_invalid_chars(str_to_unicode(row["standardfactory"])) row["standardfactory"] = str_to_unicode(row["standardfactory"]) factory_name = str_to_unicode(row["standardfactory"]) if factory_name: factory_data = get_medic_factory(factory_name) if len(factory_data) > 0: if "licensenumber" in row and len(row["licensenumber"]) > 0 and "licensenumber" in factory_data: del factory_data["licensenumber"] row.update(factory_data) row["ismatchingfactory"] = u"是" else: row["ismatchingfactory"] = u"否" row["unmatchfactory"] = factory_name del row["standardfactory"] if "_generalname" in row : #row["_generalname"] = clean_invalid_chars(str_to_unicode(row["_generalname"])) row["_generalname"] = str_to_unicode(row["_generalname"]) general_name = str_to_unicode(row["_generalname"]) general_name = clean_invalid_chars(general_name) iidx = general_name.find("(") if iidx > 0 : general_name = general_name[0:iidx] row["ismatchingmedication"] = u"否" row["unmatchmedication"] = str_to_unicode(general_name) if general_name in my_data: #母药数据 row.update(my_data[general_name]) row["ismatchingmedication"] = u"是" del row["unmatchmedication"] #药品类型,国药、省药 row["isned"] = u"否" if general_name in ned_data: ned = ned_data[general_name] if ned : row["isned"] = u"是" if ned["isned"] == u"1": row["nedtype"] = u"国药" else : row["nedtype"] = u"省药" #ROR标志 row["isror"] = u"否" if general_name in ror_datas: row["isror"] = u"是" #otc otc_data = get_otc_type(general_name) row["isdescription"] = u"无" if otc_data: row.update(otc_data) row["isdescription"] = u"有" #important medic row["isimportmedication"] = u"否" if general_name in imp_yaos: row["isimportmedication"] = u"是" database_adr.insert_to_table("olapleechdom", row) #不良反应表 for adr_name,adr_data in adr_datas.iteritems(): if isinstance(adr_data,dict): adr_data.update(share_data) adr_data["ismatchingclinic"] = "是" else: adr_data = {} adr_data.update(share_data) adr_data["ismatchingclinic"] = "否" adr_data["unmatchclinic"] = adr_name database_adr.insert_to_table("olapclinic", adr_data) #附件表数据 attaches = adrparseradr_adr.get_attachments(body) if len(attaches) > 0: for row in attaches: #row["id"] = business_data["id"] row["bianma"] = business_data["bianma"] database_adr.insert_to_table("attachments", row) #关联性评价 appraise_data = get_appraises(show_id, body) business_data.update(appraise_data) #主表数据入库 database_adr.insert_to_table("business_gather", business_data) print "%s is done!" % (show_id) except Exception as err: logging.info("show_id : %s \t" % (show_id)) logging.info(str(err.args) + ":\n") traceback.print_exc() try: logging.info(traceback.format_exc() + "\n") except: pass conn.commit() conn.close()
def crawler_mdr_record_ids(_timedict): '''抓取mdr数据的所有ID''' #获取循环次数 pageCount = gather.mdr_get_page_count_by_day(_timedict) MDR_query = [] print u'有效数据页数:', pageCount for idx in range(0, pageCount): startPos = idx*100 jim = {"funcID":"QUERY_DATA", "userID":78919, "operations":[{"actionName":"query", "operationDatas":[{"PROD_NAME_1540":"", "REG_NO_1540":"", "DEVICE_CLASS_ID_1540":"", "DEVICE_CLASS_NAME_1540":"", "REPORT_DATE_START":_timedict, "REPORT_DATE_END":_timedict, "REPORT_NUMBER_1540":"", "CREATE_DATE_START":"", "CREATE_DATE_END":"", "SUPERVISE_ORG_ID_1540":"","SUPERVISE_ORG_NAME_1540":"","MAN_NAME_1540":"","REPORT_UNIT_NAME_1540":"","PATIENT_NAME_1540":"","EVALUATE_DATE_START":"","EVALUATE_DATE_END":"","MANAGE_CATEGORY_1540":"","listid":"1540","start":startPos,"limit":100}]}]} #查询结果列表 _searchdata = send_post_json_me(totalHome, jim) # _data = json.loads(_searchdata) #当前页记录数 currentPageRecordCount = _data['ResponseMessage']['operations'][0]['count'] print u'第 %s 页共有记录数: %s' % (idx+1,currentPageRecordCount) for idx in range(0, currentPageRecordCount): #病人姓名 _p_name = _data['ResponseMessage']['operations'][0]['operationDatas'][idx]['es'][26]['v'] p_name = data_check_type(_p_name) #报告id _report_id = _data['ResponseMessage']['operations'][0]['operationDatas'][idx]['es'][4]['v'] report_id = data_check_type(_report_id) #报告单位名称 _r_u_name = _data['ResponseMessage']['operations'][0]['operationDatas'][idx]['es'][27]['v'] r_u_name = data_check_type(_r_u_name) #事件后果 _event_name = _data['ResponseMessage']['operations'][0]['operationDatas'][idx]['es'][12]['v'] event_name = data_check_type(_event_name) #产品名称 _pro_name = _data['ResponseMessage']['operations'][0]['operationDatas'][idx]['es'][20]['v'] pro_name = data_check_type(_pro_name) #报告日期 _sned_date = _data['ResponseMessage']['operations'][0]['operationDatas'][idx]['es'][2]['v'] sned_date = data_check_type(_sned_date) #接受日期 _create_date = _data['ResponseMessage']['operations'][0]['operationDatas'][idx]['es'][6]['v'] create_date = data_check_type(_create_date) #评价状态 _info = _data['ResponseMessage']['operations'][0]['operationDatas'][idx]['es'][16]['v'] info = data_check_type(_info) #厂家名称 _factory_name = _data['ResponseMessage']['operations'][0]['operationDatas'][idx]['es'][7]['v'] factory_name = data_check_type(_factory_name) #view对象 _fd_obj = _data['ResponseMessage']['operations'][0]['operationDatas'][idx]['es'][14]['v'] fd_obj = data_check_type(_fd_obj) #退回状态 _bs = _data['ResponseMessage']['operations'][0]['operationDatas'][idx]['es'][21]['v'] _bs_ = data_check_type(_bs) #补充材料 _bccl = _data['ResponseMessage']['operations'][0]['operationDatas'][idx]['es'][24]['v'] bccl = data_check_type(_bccl) obj_id = unicode_to_str(fd_obj) obj_name = unicode_to_str(r_u_name) filters = [obj_id, obj_name, info, _bs_, create_date, bccl, report_id, sned_date] insert_sql = ( "insert into mdr_fullidlist (ViewID,ReportUnitName,ValueState,BackState,ReceiveDate,AddSource,ReportID,SendDate) " "value(%s,%s,%s,%s,%s,%s,%s,%s)" ) mdrsql.mdr_insert_alone(insert_sql, filters) time.sleep(0.001) return True
def mdr_get_smd_appraise(smdrReportViewHtml, BianMa, obj_id, obj_name, DateTag): '''获取不良事件评价''' global re_eval, re_table, re_td, re_EvalId_val AppraiseLevel = "" report_sql = ''' replace into mdr_appraise( `BianMa` , `Obj_id`, `AppraiseConclusion`, `ReportRequirement` , `ReportPreliminaryAnalysisOfEvents`, `AppraiseMemo` , `AppraiseUnitName`, `AppraiseMan` , `AppraiseDate`, `DateTag`,`ADRDateAnalyse`,`ADRTypeAnalyse`,`OtherRelatedAnalyse`,`Appraise`,`AppraiseLevel` ) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) ''' match = re_eval.search(smdrReportViewHtml) if not match: print u'还没有评价信息', BianMa return try: body = match.group(1) reports = [] for table in re_table.finditer(body): if not table: print 'BianMa:', BianMa, '解析评价信息失败!body:', body continue values = [] for td in re_td.finditer(table.group(1)): if not td: print 'BianMa:', BianMa, '解析评价信息失败!table:', table.group(0) values.append(td.group(1).strip()) AppraiseConclusion = getTdVal(values[2]) ReportRequirement = getTdVal(values[4]) ReportPreliminaryAnalysisOfEvents = getTdVal(values[6]) AppraiseMemo = getTdVal(values[8]) AppraiseUnitName = getTdVal(values[10]) AppraiseMan = getTdVal(values[12]) AppraiseDate = getTdVal(values[14]) evalIdMatch = re_EvalId_val.search(values[2]) evalId = None evalHtml = None ADRDateAnalyse = "" ADRTypeAnalyse = "" OtherRelatedAnalyse = "" Appraise = "" AppraiseLevel = get_AppraiseLevel(AppraiseUnitName) if evalIdMatch: evalId = evalIdMatch.group(1) if evalId: (ADRDateAnalyse, ADRTypeAnalyse, OtherRelatedAnalyse, Appraise) = getEvalInfo(evalId) report_value = (BianMa, obj_id, AppraiseConclusion, ReportRequirement, ReportPreliminaryAnalysisOfEvents, AppraiseMemo, AppraiseUnitName, AppraiseMan, AppraiseDate, DateTag, ADRDateAnalyse, ADRTypeAnalyse, OtherRelatedAnalyse, Appraise, AppraiseLevel) mdrsql.mdr_insert_alone(report_sql, report_value) except Exception, err: print BianMa, u'不良事件评论获取失败!', err #监管机构评价信息默认为空 AppraiseConclusion = "" ReportRequirement = "" ReportPreliminaryAnalysisOfEvents = "" AppraiseMemo = "Appraise error" AppraiseUnitName = "" AppraiseMan = "" AppraiseDate = "" ADRDateAnalyse = "" ADRTypeAnalyse = "" OtherRelatedAnalyse = "" Appraise = "" AppraiseLevel = "" report_value = (BianMa, obj_id, AppraiseConclusion, ReportRequirement, ReportPreliminaryAnalysisOfEvents, AppraiseMemo, AppraiseUnitName, AppraiseMan, AppraiseDate, DateTag, ADRDateAnalyse, ADRTypeAnalyse, OtherRelatedAnalyse, Appraise, AppraiseLevel) mdrsql.mdr_insert_alone(report_sql, report_value)
def get_smd_report(smdrReportViewHtml, report_id, obj_id, obj_name, ReportUnitLinkman, ReportUnitWork, DateTag): #D.关联性评价 global re_evalute, re_td report_sql = ('''replace into mdr_reports( BianMa, ReportUnitnName, ReportAppraiseDate, ReportUnitADRDateAnalyse, ReportUnitADRTypeAnalyse, ReportUnitOtherRelatedAnalyse, ReportUnitAppraise, ReportUnitLinkman, ReportUnitWork, DateTag, ReportUnitComments) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)''') mdr_sql = "SELECT BianMa,ReportUnitName,StateReportDate FROM `mdr_business_gather` where BianMa='%s'" % ( report_id) mdr_row = mdrsql.mdr_select(mdr_sql) # 返回结果是多行 if len(mdr_row) > 0: mdr_row = mdr_row[0] if len(mdr_row) != 3: return BianMa = mdr_row[0] ReportUnitName = mdr_row[1] ReportAppraiseDate = mdr_row[2] eval_match = re_evalute.search(smdrReportViewHtml) if not eval_match: print u'暂时还没有关联性评价信息!' return ReportUnitADRDateAnalyse = "" ReportUnitADRTypeAnalyse = "" ReportUnitOtherRelatedAnalyse = "" ReportUnitAppraise = "" ReportUnitComments = "" #单位评价备注,如果能正常抓取信息,备注默认为空,否则=Unit appraise error try: ping_jia_infos = re_td.findall(eval_match.group(1)) if len(ping_jia_infos) > 0: ReportUnitADRDateAnalyse = getTdVal(ping_jia_infos[2][0]) ReportUnitADRTypeAnalyse = getTdVal(ping_jia_infos[4][0]) ReportUnitOtherRelatedAnalyse = getTdVal(ping_jia_infos[6][0]) ReportUnitAppraise = getTdVal(ping_jia_infos[8][0]) report_values = (BianMa, ReportUnitName, ReportAppraiseDate, ReportUnitADRDateAnalyse, ReportUnitADRTypeAnalyse, ReportUnitOtherRelatedAnalyse, ReportUnitAppraise, ReportUnitLinkman, ReportUnitWork, DateTag, ReportUnitAppraise) mdrsql.mdr_insert_alone(report_sql, report_values) except Exception, err: print BianMa, u'关联性评价获取失败', err #评价信息默认为空 ReportUnitADRDateAnalyse = "" ReportUnitADRTypeAnalyse = "" ReportUnitOtherRelatedAnalyse = "" ReportUnitAppraise = "" ReportUnitComments = "" report_values = (BianMa, ReportUnitName, ReportAppraiseDate, ReportUnitADRDateAnalyse, ReportUnitADRTypeAnalyse, ReportUnitOtherRelatedAnalyse, ReportUnitAppraise, ReportUnitLinkman, ReportUnitWork, DateTag, ReportUnitAppraise) mdrsql.mdr_insert_alone(report_sql, report_values)
def mdr_get_smd_appraise(smdrReportViewHtml, BianMa, obj_id,obj_name,DateTag): '''获取不良事件评价''' global re_eval,re_table,re_td,re_EvalId_val AppraiseLevel = "" report_sql = ''' replace into mdr_appraise( `BianMa` , `Obj_id`, `AppraiseConclusion`, `ReportRequirement` , `ReportPreliminaryAnalysisOfEvents`, `AppraiseMemo` , `AppraiseUnitName`, `AppraiseMan` , `AppraiseDate`, `DateTag`,`ADRDateAnalyse`,`ADRTypeAnalyse`,`OtherRelatedAnalyse`,`Appraise`,`AppraiseLevel` ) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) ''' match = re_eval.search(smdrReportViewHtml) if not match : print u'还没有评价信息',BianMa return try: body = match.group(1) reports = [] for table in re_table.finditer(body): if not table : print 'BianMa:',BianMa, '解析评价信息失败!body:', body continue values = [] for td in re_td.finditer(table.group(1)): if not td : print 'BianMa:',BianMa, '解析评价信息失败!table:',table.group(0) values.append(td.group(1).strip()) AppraiseConclusion = getTdVal(values[2]) ReportRequirement = getTdVal(values[4]) ReportPreliminaryAnalysisOfEvents = getTdVal(values[6]) AppraiseMemo = getTdVal(values[8]) AppraiseUnitName = getTdVal(values[10]) AppraiseMan = getTdVal(values[12]) AppraiseDate = getTdVal(values[14]) evalIdMatch = re_EvalId_val.search(values[2]) evalId = None evalHtml = None ADRDateAnalyse = "" ADRTypeAnalyse = "" OtherRelatedAnalyse = "" Appraise = "" AppraiseLevel = get_AppraiseLevel(AppraiseUnitName) if evalIdMatch: evalId = evalIdMatch.group(1) if evalId : (ADRDateAnalyse,ADRTypeAnalyse,OtherRelatedAnalyse,Appraise) = getEvalInfo(evalId) report_value = (BianMa, obj_id, AppraiseConclusion,ReportRequirement,ReportPreliminaryAnalysisOfEvents,AppraiseMemo, AppraiseUnitName,AppraiseMan,AppraiseDate,DateTag,ADRDateAnalyse,ADRTypeAnalyse,OtherRelatedAnalyse,Appraise,AppraiseLevel) mdrsql.mdr_insert_alone(report_sql, report_value) except Exception, err: print BianMa,u'不良事件评论获取失败!', err #监管机构评价信息默认为空 AppraiseConclusion = "" ReportRequirement = "" ReportPreliminaryAnalysisOfEvents = "" AppraiseMemo = "Appraise error" AppraiseUnitName = "" AppraiseMan = "" AppraiseDate = "" ADRDateAnalyse = "" ADRTypeAnalyse = "" OtherRelatedAnalyse = "" Appraise = "" AppraiseLevel="" report_value = (BianMa, obj_id, AppraiseConclusion,ReportRequirement,ReportPreliminaryAnalysisOfEvents,AppraiseMemo, AppraiseUnitName,AppraiseMan,AppraiseDate,DateTag,ADRDateAnalyse,ADRTypeAnalyse,OtherRelatedAnalyse,Appraise,AppraiseLevel) mdrsql.mdr_insert_alone(report_sql, report_value)
def get_smd_report(smdrReportViewHtml, report_id,obj_id, obj_name,ReportUnitLinkman,ReportUnitWork,DateTag): #D.关联性评价 global re_evalute,re_td report_sql = ( '''replace into mdr_reports( BianMa, ReportUnitnName, ReportAppraiseDate, ReportUnitADRDateAnalyse, ReportUnitADRTypeAnalyse, ReportUnitOtherRelatedAnalyse, ReportUnitAppraise, ReportUnitLinkman, ReportUnitWork, DateTag, ReportUnitComments) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)''' ) mdr_sql = "SELECT BianMa,ReportUnitName,StateReportDate FROM `mdr_business_gather` where BianMa='%s'" % (report_id) mdr_row = mdrsql.mdr_select(mdr_sql) # 返回结果是多行 if len(mdr_row) > 0 : mdr_row = mdr_row[0] if len(mdr_row) != 3 : return BianMa = mdr_row[0] ReportUnitName = mdr_row[1] ReportAppraiseDate = mdr_row[2] eval_match = re_evalute.search(smdrReportViewHtml) if not eval_match: print u'暂时还没有关联性评价信息!' return ReportUnitADRDateAnalyse = "" ReportUnitADRTypeAnalyse = "" ReportUnitOtherRelatedAnalyse = "" ReportUnitAppraise = "" ReportUnitComments="" #单位评价备注,如果能正常抓取信息,备注默认为空,否则=Unit appraise error try: ping_jia_infos = re_td.findall(eval_match.group(1)) if len(ping_jia_infos) > 0: ReportUnitADRDateAnalyse = getTdVal(ping_jia_infos[2][0]) ReportUnitADRTypeAnalyse = getTdVal(ping_jia_infos[4][0]) ReportUnitOtherRelatedAnalyse = getTdVal(ping_jia_infos[6][0]) ReportUnitAppraise = getTdVal(ping_jia_infos[8][0]) report_values = (BianMa,ReportUnitName,ReportAppraiseDate,ReportUnitADRDateAnalyse,ReportUnitADRTypeAnalyse,ReportUnitOtherRelatedAnalyse,ReportUnitAppraise,ReportUnitLinkman,ReportUnitWork,DateTag,ReportUnitAppraise) mdrsql.mdr_insert_alone(report_sql, report_values) except Exception, err: print BianMa,u'关联性评价获取失败',err #评价信息默认为空 ReportUnitADRDateAnalyse = "" ReportUnitADRTypeAnalyse = "" ReportUnitOtherRelatedAnalyse = "" ReportUnitAppraise = "" ReportUnitComments="" report_values = (BianMa,ReportUnitName,ReportAppraiseDate,ReportUnitADRDateAnalyse,ReportUnitADRTypeAnalyse,ReportUnitOtherRelatedAnalyse,ReportUnitAppraise,ReportUnitLinkman,ReportUnitWork,DateTag,ReportUnitAppraise) mdrsql.mdr_insert_alone(report_sql, report_values)
def mdr_import_report(qrow): ''' 抓取指定的记录 ''' smdrReportViewHtml = None is_icd = None icd_setdata = None viewId = qrow['ViewID'] ValueState = qrow['ValueState'] #评价状态 backState = qrow['BackState'] #退回状态 StateReportDate = qrow['ReceiveDate'] #接收日期 AddSource = qrow['AddSource'] #补充材料 ReportID = qrow['ReportID'].strip() #报告id ReportUnitName = qrow['ReportUnitName'] #报告单位名称 SendDate = qrow['SendDate'] #上报日期 ReportID = utils.unicode_to_str(ReportID.strip()) ReportUnitName = utils.str_to_unicode(ReportUnitName) print u"报告编码(MDR):", ReportID appraise_status = 0 #评价状态 if ValueState: appraise_status = 1 #退回状态 elif backState: appraise_status = 2 mdr_data = mdr_get_report_data(viewId, ReportID, SendDate) qg_info = utils.getsmdrfda(viewId, ReportID, SendDate) sh_info = utils.getSmdrWhoArt(ReportID, SendDate, viewId) # 获取报告单位所在行政区信息 (County,District,ProvinceName) = utils.getReportUnitDistrictInfo(ReportUnitName) mdr_data["County"] = County mdr_data["District"] = District mdr_data["ProvinceName"] = ProvinceName mdr_data["ReportUnitName"] = ReportUnitName mdr_data["BianMa"] = ReportID mdr_data["StateReportDate"] = StateReportDate #获取报告状态 report_status = utils.getReportStatus(ReportID, viewId, SendDate) IsNotifyGov_value = u'已通知药监部门' if u'已通知药监部门' in report_status else '' IsNotifyFactory_value = u'已通知生产企业' if u'已通知生产企业' in report_status else '' IsNotifyUnit_value = u'已通知使用单位' if u'已通知使用单位' in report_status else '' IsNotifyShop_value = u'已通知经营企业' if u'已通知经营企业' in report_status else '' State = u','.join(report_status) mdr_data["State"] = State icd_ok = mdr_data["icd_ok"] if len(icd_ok) != 0: (is_icd, UnMatchAffect,icd_setdata) = get_icd_info(mdr_data, icd_ok) # #获取器械基础信息 # (UnMatchDevice,IsMatchingDevice,deviceInfo) = utils.getDeviceInstrumentInfo(mdr_data["DeviceStandard"]) # mdr_data["UnMatchDevice"] = UnMatchDevice # mdr_data["IsMatchingDevice"] = IsMatchingDevice IsMatchingFault = None IsADRorAccident_tag = None qg_setdata = None if qg_info: (IsMatchingFault, IsADRorAccident_tag, qg_setdata, sanlei) = mdr_falt_business(qg_info, mdr_data["DeviceStandard"]) else: IsMatchingFault = '否' is_adr = None adr_data_list = None if sh_info: (is_adr, adr_data_list, IsADRorAccident_tag) = get_sh_info(sh_info,mdr_data) else: is_adr = u'否' if qg_info and sh_info: IsADRorAccident_tag = "3" else: IsADRorAccident_tag = "4" mdr_Home_1 = 'http://www.adrs.org.cn/MDR/scripts/mdr/smdr/smdReportView.jsp' querydata_1 = { "action" : "update", "FD_OBJECTID" : viewId, "UNIT_NAME" : utils.unicode_to_str(ReportUnitName), "start" : "1", "limit" : "10" } smdrReportViewHtml = utils.send_post(mdr_Home_1, urllib.urlencode( querydata_1)) if not backState and smdrReportViewHtml: #获取关联性评价信息 utils.mdr_get_smd_appraise(smdrReportViewHtml, ReportID, viewId, ReportUnitName,mdr_data["ReportDate"]) #获取器械基础信息 (UnMatchDevice,IsMatchingDevice,deviceInfo) = utils.getDeviceInstrumentInfo(mdr_data["DeviceStandard"]) mdr_data["UnMatchDevice"] = UnMatchDevice mdr_data["IsMatchingDevice"] = IsMatchingDevice if mdr_data["company_name"] : #存在,先去查询GMP (IsMatchingFactory,UnMatchFactory) = import_GMP_Info(mdr_data,mdr_data["company_name"] ,deviceInfo) mdr_data["IsMatchingFactory"] = IsMatchingFactory mdr_data["UnMatchFactory"] = UnMatchFactory d_tag = "" main_data = [ mdr_data["BianMa"], mdr_data["ProvinceName"], mdr_data["District"], mdr_data["County"], mdr_data["ReportUnitName"], mdr_data["ReportUnitAddress"], mdr_data["ReportUnitTel"], mdr_data["Postalcode"], mdr_data["UnitType"], mdr_data["HappenDate"], mdr_data["KnowDate"], mdr_data["ReportDate"], mdr_data["ReportDate"], mdr_data["StateReportDate"], mdr_data["State"], mdr_data["patient_name"], mdr_data["patient_gender"], mdr_data["patient_birthday"], mdr_data["patient_age"], mdr_data["age_class"], mdr_data["patient_telephone"], mdr_data["event_memo"], mdr_data["event_consequence"], mdr_data["death_time"], appraise_status, d_tag, IsNotifyGov_value, IsNotifyFactory_value, IsNotifyUnit_value, IsNotifyShop_value, IsMatchingDevice, UnMatchDevice, is_icd, icd_setdata, IsMatchingFault, qg_setdata, None, is_adr, adr_data_list ] #信息插入 main_sql = ( u"""replace into mdr_business_gather( BianMa,ProvinceName,District,County, ReportUnitName, ReportUnitAddress,ReportUnitTel,Postalcode,UnitType,HappenDate,KnowDate,ReportDate,AcceptDate,StateReportDate,State,SuffererName,Sex,Birthday,Age,SuffererType,TelePhone,DepictDispose,Events,DeathDate,ReportInfo,DumplicateData, IsnotifyGov, IsnotifyFactory, IsnotifyUnit, IsnotifyShop,IsDeviceMatch,DeviceList,IsICDMatch,ICDList,IsFaultMatch,FaultList,IsADRorAccident,IsADRMatch,ADRList) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)""" ) mdrsql.mdr_insert_alone(main_sql, main_data) #D.关联性评价 ,一定要在insert主表之后执行 if smdrReportViewHtml : utils.get_smd_report(smdrReportViewHtml, ReportID, viewId,ReportUnitName,mdr_data["reporter"],mdr_data["reporter_class"],mdr_data["ReportDate"]) #print u"本次下载任务已经结束" if qg_setdata: if len(sanlei) > 2: qg_data = ( mdr_data["BianMa"], mdr_data["ProvinceName"], mdr_data["District"], mdr_data["County"], mdr_data["ReportUnitName"], mdr_data["ReportUnitAddress"], mdr_data["ReportUnitTel"], mdr_data["Postalcode"], mdr_data["UnitType"], mdr_data["HappenDate"], mdr_data["KnowDate"], mdr_data["ReportDate"], mdr_data["ReportDate"], mdr_data["StateReportDate"], mdr_data["State"], IsMatchingFault, qg_setdata, sanlei[2], sanlei[1], sanlei[0], None) qg_sql = ( "replace into mdr_faultbusiness(BianMa,ProvinceName,District,County,ReportUnitName,ReportUnitAddress," "ReportUnitTel,Postalcode,UnitType,HappenDate,KnowDate,ReportDate,AcceptDate,StateReportDate,State," "IsMatchingFault,StandardFaultName,Name,SubName,SuperClassName,NonName)" "values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" ) mdrsql.mdr_insert_alone(qg_sql, qg_data) else: qg_data = ( mdr_data["BianMa"], mdr_data["ProvinceName"], mdr_data["District"], mdr_data["County"], mdr_data["ReportUnitName"], mdr_data["ReportUnitAddress"], mdr_data["ReportUnitTel"], mdr_data["Postalcode"], mdr_data["UnitType"], mdr_data["HappenDate"], mdr_data["KnowDate"], mdr_data["ReportDate"], mdr_data["ReportDate"], mdr_data["StateReportDate"], mdr_data["State"], IsMatchingFault, qg_setdata, None, None, None, sanlei[0]) qg_sql = ( "replace into mdr_faultbusiness(BianMa,ProvinceName,District,County,ReportUnitName,ReportUnitAddress," "ReportUnitTel,Postalcode,UnitType,HappenDate,KnowDate,ReportDate,AcceptDate,StateReportDate,State," "IsMatchingFault,StandardFaultName,Name,SubName,SuperClassName,NonName)" "values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" ) mdrsql.mdr_insert_alone(qg_sql, qg_data)
def clear_invalid_data(strDate): ''' 清除某天无效的记录 ''' beginDate = datetime.datetime.strptime(strDate,"%Y-%m-%d") endDate = beginDate + datetime.timedelta(days=180) pageSize = 100 pageStart = 0 processCnt = 0 isComplete = False deleted_data_query_url = 'http://www.adrs.org.cn/ADR/ep/alreadyReport/AlreadyReportService/queryDeleteReport/' while not isComplete: query_data = '{"funcID":"0000000","userID":608575,"operations":[{"actionName":"query","operationDatas":[{"ENTRY_START_DATE_2200":"%s","ENTRY_END_DATE_2200":"%s","DELETE_START_DATE_2226":"%s","DELETE_END_DATE_2226":"%s","REPORT_ID_2200":"","GENERAL_NAME_2200":"","ADR_RESULT_2660":"","PATIENT_NAME_2200":"","FACTORY_NAME_2200":"","REPORTDELETE_2200":"1","REPORTRepeat_2200":"1","listid":"2433","start":%s,"limit":%s}]}]}' % (strDate,strDate,strDate, endDate.strftime("%Y-%m-%d"),pageStart, pageSize) #query_data = { # "funcID": "0000000", # "userID": 608575, # "operations": [{ # "actionName": "query", # "operationDatas": [{ # "ENTRY_START_DATE_2200":strDate, # "ENTRY_END_DATE_2200": endDate.strftime("%Y-%m-%d"), # "DELETE_START_DATE_2226": strDate, # "DELETE_END_DATE_2226": endDate.strftime("%Y-%m-%d"), # "REPORT_ID_2200": "", # "GENERAL_NAME_2200": "", # "ADR_RESULT_2660": "", # "PATIENT_NAME_2200": "", # "FACTORY_NAME_2200": "", # "REPORTDELETE_2200": "1", # "REPORTRepeat_2200": "1", # "listid": "2433", # "start": pageStart, # "limit": pageSize # }] # }] #} strResponse = utils.mdr_send_post(deleted_data_query_url, query_data,None,None,None) jsonData = None try: jsonData = json.loads(strResponse) except Exception,err: print u'获取%s的删除数据失败!' % strDate return pageTotal = jsonData["ResponseMessage"]["operations"][0]["pageTotal"] currTotal = jsonData["ResponseMessage"]["operations"][0]["count"] if pageTotal <= 0: print u'date: %s 没有删除的数据' % strDate return if currTotal < pageSize : isComplete = True #如果当前返回结果小于pageSize , 证明是最后一页 datas = jsonData["ResponseMessage"]["operations"][0]["operationDatas"] for rowData in datas: showid = rowData["es"][3]["v"] bianma = rowData["es"][12]["v"] reason = rowData["es"][8]["v"] report_date = rowData["es"][1]["v"] deleted_date= rowData["es"][2]["v"] row = [showid,bianma,reason,report_date,deleted_date] mdrsql.mdr_insert_alone("replace into adr_deleted_gather(show_id,bianma,reason,report_date,deleted_date)values(%s,%s,%s,%s,%s)",row)
def PSUR_Report(_timedict): # t = _timedict t_start = t['beginTime'] t_end = t['endTime'] RF_list = gather_report_total(_timedict) for i in range(0, RF_list): meid = i * 100 print "meid:", meid jim = { "funcID": "QUERY_DATA", "userID": 78919, "operations": [{ "actionName": "query", "operationDatas": [{ "ENTRY_START_DATE_2401": t_start, "ENTRY_END_DATE_2401": t_end, "ACTIVE_CONSTITUENT_2430": "", "TRADENAME_CN_2431": "", "DRUGNAME_CN_FORM_2431": "", "DOSEFORM_2431": "", "DRUG_STATUS_2431": "", "APPROVAL_NO_2431": "", "REPORT_ID_2430": "", "PICKER_UNIT_NAME_2430": "", "START_APPLY_DATE_2430": "", "END_APPLY_DATE_2430": "", "listid": "2430_1", "start": meid, "limit": 100 }] }] } #查询结果列表 try: _searchdata = send_post_json_me(Psur_url, jim) _data = json.loads(_searchdata) #print 'json:', _data searchdataid = _data['ResponseMessage']['operations'][0]['count'] for i in range(0, searchdataid): PICKER_UNIT_ADDR = _data['ResponseMessage']['operations'][0][ 'operationDatas'][i]['es'][0]['v'] REPORT_DATE = _data['ResponseMessage']['operations'][0][ 'operationDatas'][i]['es'][2]['v'] PICKER_UNIT_FAX = _data['ResponseMessage']['operations'][0][ 'operationDatas'][i]['es'][3]['v'] PICKER_UNIT_TEL = _data['ResponseMessage']['operations'][0][ 'operationDatas'][i]['es'][6]['v'] DRUG_TYPE_NAME = _data['ResponseMessage']['operations'][0][ 'operationDatas'][i]['es'][7]['v'] ACTIVE_CONSTITUENT = _data['ResponseMessage']['operations'][0][ 'operationDatas'][i]['es'][10]['v'] OBJ_ID = _data['ResponseMessage']['operations'][0][ 'operationDatas'][i]['es'][13]['v'] INTERNATION_BEGIN_DATE = _data['ResponseMessage'][ 'operations'][0]['operationDatas'][i]['es'][15]['v'] PSUR_CONCLUSION = _data['ResponseMessage']['operations'][0][ 'operationDatas'][i]['es'][17]['v'] PROD_SOURCE_2430_SHOW = _data['ResponseMessage']['operations'][ 0]['operationDatas'][i]['es'][18]['v'] ENTRY_DATE = _data['ResponseMessage']['operations'][0][ 'operationDatas'][i]['es'][19]['v'] PROD_DESC = _data['ResponseMessage']['operations'][0][ 'operationDatas'][i]['es'][21]['v'] INDICATIONS = _data['ResponseMessage']['operations'][0][ 'operationDatas'][i]['es'][25]['v'] PICKER_UNIT_LINKMAN = _data['ResponseMessage']['operations'][ 0]['operationDatas'][i]['es'][26]['v'] PICKER_UNIT_NAME = _data['ResponseMessage']['operations'][0][ 'operationDatas'][i]['es'][28]['v'] PICKER_UNIT_EMAIL = _data['ResponseMessage']['operations'][0][ 'operationDatas'][i]['es'][29]['v'] PICKER_UNIT_DEPT = _data['ResponseMessage']['operations'][0][ 'operationDatas'][i]['es'][31]['v'] PICKER_SIGN = _data['ResponseMessage']['operations'][0][ 'operationDatas'][i]['es'][32]['v'] PICKER_UNIT_POST = _data['ResponseMessage']['operations'][0][ 'operationDatas'][i]['es'][34]['v'] DATA_END_DATE = _data['ResponseMessage']['operations'][0][ 'operationDatas'][i]['es'][35]['v'] REPORT_ID = _data['ResponseMessage']['operations'][0][ 'operationDatas'][i]['es'][36]['v'] DRUGNAME_CN_FORM = _data['ResponseMessage']['operations'][0][ 'operationDatas'][i]['es'][38]['v'] DATA_START_DATE = _data['ResponseMessage']['operations'][0][ 'operationDatas'][i]['es'][40]['v'] tempdata = ( REPORT_ID, #36,报告编码 DRUG_TYPE_NAME, #7,药品分类 ACTIVE_CONSTITUENT, #10,活性成分 PROD_SOURCE_2430_SHOW, #18,国产/进口 DRUGNAME_CN_FORM, #38,通用名称 DATA_START_DATE, #41,数据起日期 DATA_END_DATE, #35,数据止日期 PICKER_UNIT_NAME, #28,报告单位名称 ENTRY_DATE, #19,国家中心接收时间 OBJ_ID #13 ) #新添加: #1.判断已存在,则不再添加 #2.为了解决下载error中的数据,添加标识 has_sql = "select ReportID from pusr_business where ReportID='%s'" % ( REPORT_ID) hasset = mdrsql.mdr_select(has_sql) if hasset: #print hasset print u"已存在此报告编码:", REPORT_ID continue else: #ReportID, DrugType, CFZC, Classify, TYMC, Start_Date, End_Date, FactoryName, StateReportDate, BackUp1 #查询数据写入 print "ENTRY_DATE:", ENTRY_DATE insert_sql = ( "insert into pusr_query (ReportID, DrugType, CFZC, Classify, TYMC, Start_Date, End_Date, FactoryName, StateReportDate, BackUp1) " "value(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)") mdrsql.mdr_insert_alone(insert_sql, tempdata) #{ ylname = None code = None myname = None ylycn = None ymcn_sql = "select distinct ylname,code,myname,ylycn from ym where ymcn='%s'" % ( DRUGNAME_CN_FORM) ymcndataset = mdrsql.mdr_select(ymcn_sql) if ymcndataset: for ymcndata in ymcndataset: ylname = ymcndata[0] code = ymcndata[1] myname = ymcndata[2] ylycn = ymcndata[3] else: ylname = "" code = "" myname = "" ylycn = "" #} pname = None dtypeID = None localdata_sql = "select distinct ProvinceName,DistrictTypeID from unit where Name='%s'" % ( PICKER_UNIT_NAME) localdataset = mdrsql.mdr_select(localdata_sql) if localdataset: for localdata in localdataset: pname = localdata[0] dtypeID = localdata[1] else: pname = "" dtypeID = "" boss_add_data = [ ylname, code, myname, ylycn, pname, dtypeID ] #主表信息写入 reportdata = [ # #报告表编码 36 REPORT_ID, #国际诞生日 15 INTERNATION_BEGIN_DATE, #活性成分(处方组成) 10 ACTIVE_CONSTITUENT, #药品分类 7 DRUG_TYPE_NAME, #国产/进口 18 PROD_SOURCE_2430_SHOW, #适应症(功能主治) 25 INDICATIONS, #产品情况说明 21 PROD_DESC, #本期报告结论 17 PSUR_CONCLUSION, #报告人 32 PICKER_SIGN, #报告日期 2 REPORT_DATE, #报告单位名称 28 PICKER_UNIT_NAME, #传真 3 PICKER_UNIT_FAX, #报告单位地址 0 PICKER_UNIT_ADDR, #邮政编码 34 PICKER_UNIT_POST, #负责部门 31 PICKER_UNIT_DEPT, #联系电话 6 PICKER_UNIT_TEL, #联系人26 PICKER_UNIT_LINKMAN, #电子邮件 29 PICKER_UNIT_EMAIL, #国家中心接收时间 19 ENTRY_DATE ] #主表信息更新 somedata = get_report(OBJ_ID) #print u"额外数据解析,即返回整个文本",somedata #extrareportdata = data2clean(extradata(somedata)) extrareportdata = extradata2(somedata) dataall = reportdata + extrareportdata + boss_add_data #报告表编码 36 #REPORT_ID,INTERNATION_BEGIN_DATE,ACTIVE_CONSTITUENT,DRUG_TYPE_NAME,PROD_SOURCE_2430_SHOW,INDICATIONS,PROD_DESC,PSUR_CONCLUSION,PICKER_SIGN,REPORT_DATE,PICKER_UNIT_NAME,PICKER_UNIT_FAX,PICKER_UNIT_ADDR, PICKER_UNIT_POST,PICKER_UNIT_DEPT,PICKER_UNIT_TEL,PICKER_UNIT_LINKMAN,PICKER_UNIT_EMAIL,ENTRY_DATE insert_report = ( "insert into pusr_business (REPORTID,INTERNATION_BEGIN_DATE,ACTIVE_CONSTITUEN,DRUG_TYPE_NAME,PROD_SOURCE,INDICATIONS,PROD_DESC,PSUR_CONCLUSION,PICKER_SIGN,REPORT_DATE,PICKER_UNIT_NAME,PICKER_UNIT_FAX,PICKER_UNIT_ADDR, PICKER_UNIT_POST,PICKER_UNIT_DEPT,PICKER_UNIT_TEL,PICKER_UNIT_LINKMAN,PICKER_UNIT_EMAIL,ENTRY_DATE,BGQ,ylname,code,myname,ylycn,ProvinceName,District) " "value(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" ) mdrsql.mdr_insert_alone(insert_report, dataall) print REPORT_ID #psur业务主表批准文号部分11字段1-N数据表 extrainfo = extradata3(somedata) n = 11 dataout = [ extrainfo[i:i + n] for i in range(0, len(extrainfo), n) ] for temp in dataout: d0 = temp[0] d1 = temp[1] d2 = temp[2] if d1: pass else: d1 = "0000-00-00" if d2: pass else: d2 = "0000-00-00" d3 = temp[3] d4 = temp[4] d5 = temp[5] d6 = temp[6] d7 = temp[7] d8 = temp[8] d9 = temp[9] d10 = temp[10] extraalldata = [ REPORT_ID, d0, d1, d2, d3, d4, d5, d6, d7, d8, d9, d10 ] insert_report_extra = ( "insert into pusr_business_extradata (REPORTID,RegisterID,RegisterTime,FirstRegisterTime,TYMC,SPMC,DrugMState,GG,JX,BQSC,BQXL,CountUser) " "value(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)") mdrsql.mdr_insert_alone(insert_report_extra, extraalldata) #评价信息读取 write_Eval(somedata, OBJ_ID, REPORT_ID) #修改信息读取 write_ChangeInfo(somedata, OBJ_ID, REPORT_ID) #显示附件信息读取 write_DWF(somedata, OBJ_ID, REPORT_ID) #下载文件信息写入 download_fileinfo3(OBJ_ID, REPORT_ID) #附件下载 """ if Tag: myautodown(OBJ_ID,REPORT_ID) else: pass """ myautodown(OBJ_ID, REPORT_ID) time.sleep(0.001) except (IndexError, KeyError, ValueError), exception: print "Main:", exception print "main:", _searchdata
def downallfiles3(id, psurobjid, filename, reportid): # url ="http://www.adrs.org.cn/ADR/cdrAttachment/attachmentDiskManager/" #id = "109913972074597010463482100000" #psurobjid = "243013972078880790070655100000" import random some = random.randrange(1000000000000000, 9999999999999999) boundary = '-----------------------------' + str(some) parts = [] parts.append('--' + boundary) parts.append('Content-Disposition: form-data; name="action"') parts.append('') parts.append(str("download")) parts.append('--' + boundary) parts.append('Content-Disposition: form-data; name="id"') parts.append('') parts.append(id) parts.append('--' + boundary + '--') parts.append('') body = '\r\n'.join(parts) header = { 'Accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8', 'Accept-Encoding': 'gzip, deflate', 'Host': 'Host: www.adrs.org.cn', 'Accept-Language': 'en-US,en;q=0.5', 'Connection': 'keep-alive', 'User-Agent':'Mozilla/5.0 (Windows NT 5.1; rv:30.0) Gecko/20100101 Firefox/30.0', 'Content-Type': 'multipart/form-data; boundary=%(boundary)s' % {"boundary": boundary}, 'Referer':'http://www.adrs.org.cn/ADR/page/adrpage/psur/viewPsurReport.jsp?fdObjectId=%(objid)s&type=2' % {"objid": psurobjid} } try: common_url = urllib2.Request(url, headers=header) response = urllib2.urlopen(common_url, data=body, timeout=120) #print "response.info():",response.info() from cStringIO import StringIO from gzip import GzipFile data2 = GzipFile('', 'r', 0, StringIO(response.read())).read() response_data = data2 # 定义要创建的目录 import pathDlg #mkpath = "c:\\mykill\\web\\" mkpath = pathDlg.readini_path() path = mkdir(mkpath, psurobjid) name = path + "\\"+filename print name local_file = open(name, "wb") local_file.write(response_data) local_file.close() except (urllib2.HTTPError, urllib2.URLError, socket.error), exception: print u"下载出现错误,打印文件信息:",id, psurobjid, filename,reportid tempdata = (reportid, psurobjid, filename,id) insert_sql = ( "insert into pusr_dwfinfo_down_error(ReportID, FileID,ShowFileName BackUp1) " "value(%s,%s,%s,%s,%s)" ) mdrsql.mdr_insert_alone(insert_sql, tempdata) pass
def downallfiles3(id, psurobjid, filename, reportid): # url = "http://www.adrs.org.cn/ADR/cdrAttachment/attachmentDiskManager/" #id = "109913972074597010463482100000" #psurobjid = "243013972078880790070655100000" import random some = random.randrange(1000000000000000, 9999999999999999) boundary = '-----------------------------' + str(some) parts = [] parts.append('--' + boundary) parts.append('Content-Disposition: form-data; name="action"') parts.append('') parts.append(str("download")) parts.append('--' + boundary) parts.append('Content-Disposition: form-data; name="id"') parts.append('') parts.append(id) parts.append('--' + boundary + '--') parts.append('') body = '\r\n'.join(parts) header = { 'Accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8', 'Accept-Encoding': 'gzip, deflate', 'Host': 'Host: www.adrs.org.cn', 'Accept-Language': 'en-US,en;q=0.5', 'Connection': 'keep-alive', 'User-Agent': 'Mozilla/5.0 (Windows NT 5.1; rv:30.0) Gecko/20100101 Firefox/30.0', 'Content-Type': 'multipart/form-data; boundary=%(boundary)s' % { "boundary": boundary }, 'Referer': 'http://www.adrs.org.cn/ADR/page/adrpage/psur/viewPsurReport.jsp?fdObjectId=%(objid)s&type=2' % { "objid": psurobjid } } try: common_url = urllib2.Request(url, headers=header) response = urllib2.urlopen(common_url, data=body, timeout=120) #print "response.info():",response.info() from cStringIO import StringIO from gzip import GzipFile data2 = GzipFile('', 'r', 0, StringIO(response.read())).read() response_data = data2 # 定义要创建的目录 import pathDlg #mkpath = "c:\\mykill\\web\\" mkpath = pathDlg.readini_path() path = mkdir(mkpath, psurobjid) name = path + "\\" + filename print name local_file = open(name, "wb") local_file.write(response_data) local_file.close() except (urllib2.HTTPError, urllib2.URLError, socket.error), exception: print u"下载出现错误,打印文件信息:", id, psurobjid, filename, reportid tempdata = (reportid, psurobjid, filename, id) insert_sql = ( "insert into pusr_dwfinfo_down_error(ReportID, FileID,ShowFileName BackUp1) " "value(%s,%s,%s,%s,%s)") mdrsql.mdr_insert_alone(insert_sql, tempdata) pass