def process_localfiles(pmailslst, pmutex, pservfileprocessedlst, pfrompath,
ptopath):
#1.move file to his dir
#2.update maillst status by uid&flag
fileslst = os.listdir(pfrompath)
fileslst.sort()
for i in range(0, len(fileslst), 1):
if getidxkeywordinlst(
pservfileprocessedlst,
get2betw13(fileslst[i], public.GFLAG_UID,
public.GSEPTOR_FILE)) > -1:
#move to ptopath
logaq('moving file to ' + ptopath)
shutil.move(pfrompath + fileslst[i], ptopath + fileslst[i])
updateflagmsg_byuidflag(
pmailslst, pmutex,
get2betw13(fileslst[i], public.GFLAG_UID, public.GSEPTOR_FILE),
public.GSTATUS_SQLDATA_EXPORTED,
public.GSTATUS_SQLDATA_DOWNLOADED, '')
else:
#local file exist,serverfile no exist
tmpidx = getidxbyuidflag(
get2betw13(fileslst[i], public.GFLAG_UID, public.GSEPTOR_FILE),
public.GSTATUS_SQLDATA_EXPORTED, pmailslst)
if tmpidx > -1:
#os.rename(pfrompath+fileslst[i],(pfrompath+fileslst[i]).replace(public.GSUFFIX_FILEMAIL,'.drr'))
logaq('moving file to send')
shutil.move(pfrompath + fileslst[i],
public.gdir_atachmt_willsend)
updateflagmsg_byuidflag(
pmailslst, pmutex,
get2betw13(fileslst[i], public.GFLAG_UID,
public.GSEPTOR_FILE),
public.GSTATUS_SQLDATA_EXPORTED, public.GSTATUS_ERROR,
PG_MSG2)
def processfile(pmailslst, pmutex, ppath, pfilename):
#process file content for creating view
#Format:
#old file:select ...... from ......where ......;select ...... from ...... where ......;
#new file:create view filename_seq1V as select ...... from ......where ......;create view filename_seq2V as select ...... from ...... where ......;
global s2_charset
ltmpuid = get2betw13(pfilename, public.GPREFIX_FILEUID,
public.GSEPTOR_FILE)
tmpfd = open(ppath, 'rb')
try:
tmpdata = tmpfd.read()
finally:
tmpfd.close()
s2_charset = chardet.detect(tmpdata)['encoding']
if s2_charset[
0:
3] == 'UTF': #AMERICAN_AMERICA.ZHS16GBK AL32UTF8 ZHS16CGB231280
os.environ["NLS_LANG"] = public.GNLS_LANG_UTF8
else:
os.environ["NLS_LANG"] = public.GNLS_LANG_GBK
if check_fileformat(tmpdata.decode(s2_charset)):
lviewname = public.GFLAG_UID + get2betw13(
pfilename, public.GPREFIX_FILEUID, public.GSUFFIX_FILEMAIL)
tmpfiletext = replace_oldkeyword(
tmpdata.decode(s2_charset), S2_SELECT,
S2_CREATEORREP + lviewname + S2_SUFFIX, S2_SEGSEPTOR, 1,
lviewname + '_')
tmperr, tmpfiletext = remove_riskfactors(tmpfiletext)
if tmperr:
updateflagmsg_byuidflag(pmailslst, pmutex, ltmpuid,
public.GSTATUS_ATACHMT_DOWNLOADED,
public.GSTATUS_ERROR,
S2_RFACTORHINT + tmperr + S2_TAIL)
logaq(pfilename + ':' + S2_RFACTORHINT + tmperr, 'e')
if os.path.exists(public.gdir_history + pfilename):
os.remove(public.gdir_history + pfilename)
logaq('moving file to his')
shutil.move(ppath, public.gdir_history)
return False
tmpdata = tmpfiletext.encode(s2_charset)
tmpfd = open(ppath, 'wb')
try:
tmpfd.write(tmpdata)
finally:
tmpfd.close()
return True
else:
updateflagmsg_byuidflag(pmailslst, pmutex, ltmpuid,
public.GSTATUS_ATACHMT_DOWNLOADED,
public.GSTATUS_ERROR, S2_MSG4)
logaq(pfilename + ':' + S2_MSG4, 'e')
if os.path.exists(public.gdir_history + pfilename):
os.remove(public.gdir_history + pfilename)
logaq('moving file to his')
shutil.move(ppath, public.gdir_history)
return False
def put_main(pmailslst, pparaslst, pmutex):
fileslst = os.listdir(public.gdir_atachmt_downloaded)
fileslst.sort()
curmuid = -1
predbdusr = ''
curdbdusr = ''
for i in range(0, len(fileslst), 1):
print(fileslst[i])
path = os.path.join(public.gdir_atachmt_downloaded, fileslst[i])
#process file context
#
curdbdusr = getdbtnsdbusr(get2betw13(fileslst[i], 'UID', '_'),
public.GSTATUS_ATACHMT_DOWNLOADED, pmailslst)
print(curdbdusr)
if not curdbdusr:
os.rename(path, path.replace(public.GSUFFIX_FILEMAIL, '.urr'))
logaq('moving file to senddir')
shutil.move(path.replace(public.GSUFFIX_FILEMAIL, '.urr'),
public.gdir_atachmt_willsend)
updateflagmsg_byuidflag(pmailslst, pmutex,
get2betw13(fileslst[i], 'UID', '_'),
public.GSTATUS_ATACHMT_DOWNLOADED,
public.GSTATUS_ERROR, PG_MSG1)
if i > 0:
updateflagmsg_byuidflag(
pmailslst, pmutex, get2betw13(fileslst[i - 1], 'UID', '_'),
public.GSTATUS_ATACHMT_DOWNLOADED,
public.GSTATUS_ATACHMT_UPLOADED, '')
elif predbdusr != curdbdusr:
#getoinfo
if predbdusr:
transport.close()
loip, loprt, lousr, lopd = getoinforbytnsusr(curdbdusr, pparaslst)
print(loip + ',' + loprt + ',' + lousr + ',' + lopd)
transport = paramiko.Transport((loip, int(loprt)))
transport.connect(username=lousr, password=lopd)
sftp = paramiko.SFTPClient.from_transport(transport)
if os.path.isfile(path):
sftp.put(path, '/home/test/' + fileslst[i])
shutil.move(path, public.gdir_atachmt_uploaded)
#updatedict_inlist(pmailslst,pmutex,pg_dictname_uid,get2betw13(fileslst[i],'UID','_'),pg_dictname_flag,public.GSTATUS_ATACHMT_DOWNLOADED,public.GSTATUS_ATACHMT_UPLOADED)
if (predbdusr and predbdusr != curdbdusr) or (i == len(fileslst) - 1):
idxlast = i - 1
if i == len(fileslst) - 1:
idxlast = len(fileslst) - 1
print(get2betw13(fileslst[idxlast], 'UID', '_'))
updateflagmsg_byuidflag(pmailslst, pmutex,
get2betw13(fileslst[idxlast], 'UID', '_'),
public.GSTATUS_ATACHMT_DOWNLOADED,
public.GSTATUS_ATACHMT_UPLOADED, '')
if i == len(fileslst) - 1:
transport.close()
#if fileslst[i].find('DW')>-1:#fileuid not match mailslst uid
#print('----\n'+public.gdir_atachmt_uploaded+fileslst[i]+'\n'+public.gdir_atachmt_uploaded+'err.err'+'----\n')
#os.rename(public.gdir_atachmt_uploaded+fileslst[i],public.gdir_atachmt_uploaded+'err.err')
predbdusr = curdbdusr
def callprocedure(pmailslst, pparaslst, pmutex, ppath, pfilename, pproclst,
pmutexp, pdbconlst): #add pdbconlst
#!!!!!!!!!!this procedure maybe spend a long time!!!!!!!!!!!
#BUG FINDED 20181123
ltmpuid = get2betw13(pfilename, public.GPREFIX_FILEUID,
public.GSEPTOR_FILE)
updateflagmsg_byuidflag(pmailslst, pmutex, ltmpuid,
public.GSTATUS_ATACHMT_DOWNLOADED,
public.GSTATUS_SQLDATA_BEFEXPORT, '', time.time())
ltmpviewlike = public.GFLAG_UID + get2betw13(
pfilename, public.GPREFIX_FILEUID, public.GSUFFIX_FILEMAIL) + '%'
ltmpowner = getdbusrbyuid(ltmpuid, pmailslst).strip()
ltmpdbtns = getdbtnsbyuid(ltmpuid, pmailslst)
if len(ltmpowner) == 0:
ltmpowner = getdbusrbydbtns(ltmpdbtns, pparaslst)
logaq('dbtns:' + ltmpdbtns)
logaq('dbusr:'******'t exit)
#ltmpprocname=S2_PROC_PRODUCTDATA.replace('POWNER',ltmpowner.upper())
#ltmpprocname=ltmpprocname.replace('PVIEWLIKE',ltmpviewlike.upper())
#lrst=execprocedure(getsqlpluscon(ltmpdbtns,ltmpowner,pparaslst),ltmpprocname,pproclst,pmutexp)
lsqlerr = ''
for lerr in S2_ERROR:
if lrst.lower().find(lerr.lower()) > -1:
lsqlerr = lrst
if lsqlerr: #procedure exec error
lsqlerr = clearblankchar(lsqlerr)
updateflagmsg_byuidflag(pmailslst, pmutex, ltmpuid,
public.GSTATUS_SQLDATA_BEFEXPORT,
public.GSTATUS_ERROR, lsqlerr)
logaq(pfilename + ':' + lsqlerr, 'e')
if os.path.exists(public.gdir_history + pfilename):
os.remove(public.gdir_history + pfilename)
logaq('moving file to his')
shutil.move(ppath, public.gdir_history)
else: #cannot updateflag to exported until all UID attachments process over.
# updateflagmsg_byuidflag(pmailslst,pmutex,pmailslst[pidxuid]['uid'],public.GSTATUS_SQLDATA_BEFEXPORT,public.GSTATUS_SQLDATA_EXPORTED,'')
if os.path.exists(public.gdir_history + pfilename):
os.remove(public.gdir_history + pfilename)
logaq('moving file to his')
shutil.move(ppath, public.gdir_history)
def format_tocc(pstr):
rst = ''
for ml in pstr.split(','):
tmpem = get2betw13(ml, '<', '>')
if not tmpem:
tmpem = ml
if not rst:
rst = tmpem.strip()
else:
rst = rst + ',' + tmpem.strip()
return rst
def exeprocedure_bycxora(pconstr, pproname, pproparaowner, pproparaviewlike,
pdbconlst, pmutex):
#instead of execprocedure; solving BUG:mutithread call sqlplus hang
rst = 'begin exeprocedure_bycxora'
logaq('ConToDB constr:' + get2betw13(pconstr, '', '/') + '/******@' +
get2betw13(pconstr, '@', ''))
logaq('callproc:' + pproname + '(' + pproparaowner + ',' +
pproparaviewlike + ')')
try:
odb = cx_Oracle.Connection(pconstr)
dbcur = odb.cursor()
except Exception as e:
logaq('DB connect Error: %s' % e, 'e')
rst = "Error:DB connect Exception"
return rst
try:
add_dbcon(pdbconlst, odb, dbcur,
threading.currentThread().getName(), pmutex)
logaq('dblst size(AaddedBcall):' + str(len(pdbconlst)))
dbcur.callproc(pproname, [pproparaowner, pproparaviewlike])
logaq('remove dbfromlst')
remove_dbcon(pdbconlst, threading.currentThread().getName(), pmutex)
logaq('dblst size(After call&removed):' + str(len(pdbconlst)))
except Exception as e:
logaq('DB Error: %s' % e, 'e')
rst = S2_MSG5
logaq('remove dbfromlst')
remove_dbcon(pdbconlst, threading.currentThread().getName(), pmutex)
logaq('dblst size(After call&removed):' + str(len(pdbconlst)))
finally:
try:
dbcur.close()
except Exception as e:
logaq('dbcur close Error: %s' % e, 'e')
try:
odb.close()
except Exception as e:
logaq('dbcon close Error: %s' % e, 'e')
return rst
def execsfile(pconstr, pfile):
global s2_charset
logaq('NLS_LANG=' + os.environ["NLS_LANG"])
logaq(S2_SQLAPP + ' constr:' + get2betw13(pconstr, '', '/') + '/******@' +
get2betw13(pconstr, '@', '') + ' file:' + pfile) #/xlzhu@
procf = Popen([S2_SQLAPP, '-S', pconstr],
stdout=PIPE,
stdin=PIPE,
stderr=PIPE)
logaq('opensqlplus id:' + str(id(procf)))
logaq('begin create views...')
#procf.stdin.write(('@'+pfile).encode())#exist bug
try:
(outf,
errf) = procf.communicate(input=('@' + pfile).encode(s2_charset),
timeout=60)
logaq('create views end')
except TimeoutExpired:
logaq('sqlplus timeout', 'w')
procf.terminate()
(outf, errf) = procf.communicate()
except Exception as e:
logaq('sqlplus Exception: %s' % e, 'w')
(outf, errf) = procf.communicate()
if procf.returncode != 0:
returnmsgf = errf.decode()
logaq(
'returncode:' + str(procf.returncode) + 'err:' + returnmsgf +
'out:' + outf.decode(), 'e')
if len(returnmsgf) == 0:
returnmsgf = outf.decode()
if len(returnmsgf) == 0:
returnmsgf = S2_MSG5
#sys.exit(procf.returncode)
else:
returnmsgf = outf.decode()
return returnmsgf
def checkfilestatus(pmailslst, ppath, pfilename):
rst = False
tmpidxuid = getidxbyuidflag(
get2betw13(pfilename, public.GPREFIX_FILEUID, public.GSEPTOR_FILE),
public.GSTATUS_SQLDATA_BEFEXPORT, pmailslst)
if tmpidxuid > -1:
logaq(pfilename + ':' + S2_MSG2, 'w')
#shutil.move(ppath,public.gdir_history)
else:
tmpidxuid = getidxbyuidflag(
get2betw13(pfilename, public.GPREFIX_FILEUID, public.GSEPTOR_FILE),
public.GSTATUS_ATACHMT_DOWNLOADED, pmailslst)
if tmpidxuid < 0:
#write error file to his,move this file to his
logaq(pfilename + ':' + S2_MSG1, 'e')
#writefile(os.path.join(public.gdir_history,pfilename),S2_MSG1,'.err')
if os.path.exists(public.gdir_history + pfilename):
os.remove(public.gdir_history + pfilename)
logaq('moving file to his')
shutil.move(ppath, public.gdir_history)
else:
rst = True
return rst
def createviews(pmailslst, pparaslst, pmutex, ppath, pfilename):
#login to sqlplus exec sqlfile
rst = 'OK'
lsqlerr = ''
ltmpuid = get2betw13(pfilename, public.GPREFIX_FILEUID,
public.GSEPTOR_FILE)
lcon = getsqlpluscon(getdbtnsbyuid(ltmpuid, pmailslst),
getdbusrbyuid(ltmpuid, pmailslst), pparaslst)
if lcon:
lrst = execsfile(lcon, ppath)
try:
logaq('execsfile return')
except Exception as e:
logaq('log Error: %s' % e, 'e')
else:
logaq(pfilename + ':' + S2_MSG3, 'e')
if os.path.exists(public.gdir_history + pfilename):
os.remove(public.gdir_history + pfilename)
logaq('moving file to his')
shutil.move(ppath, public.gdir_history)
rst = ''
return rst
#logaq('judging returnstr error')
for lerr in S2_ERROR:
if lrst.lower().find(lerr.lower()) > -1:
lsqlerr = lrst
#logaq('judging end 20s')
time.sleep(20)
if lsqlerr: #sqlfile exec error
lsqlerr = clearblankchar(lsqlerr)
updateflagmsg_byuidflag(pmailslst, pmutex, ltmpuid,
public.GSTATUS_ATACHMT_DOWNLOADED,
public.GSTATUS_ERROR, lsqlerr)
logaq(pfilename + ':' + lsqlerr, 'e')
if os.path.exists(public.gdir_history + pfilename):
os.remove(public.gdir_history + pfilename)
logaq('moving file to his')
shutil.move(ppath, public.gdir_history)
rst = ''
return rst
def mails_main(pmaillst, pmutex, pdirattachment):
if breaktime():
exit()
try:
global ms_serv
global ms_msg1
lpretime = time.time()
ms_serv = smtplib.SMTP_SSL(public.gshst, public.gsprt)
ms_serv.login(public.gmusr, getpdbyen(public.gmenpd))
logaq('smtp server login ok', 'i')
while not breaktime():
while not getfilepgok(public.gmutex_filepg) and not breaktime():
logaq('waiting for filepg ' + str(public.gsleep) + 's...', 'i')
time.sleep(public.gsleep)
logaq('process exception mail')
process_exceptmaillst(pmaillst, pmutex)
continue
pretmpmuid = ''
tmpmuid = ''
existsplitf = False
relogin_mail(lpretime)
lpretime = time.time()
try:
lexistnewfile = False
fileslst = os.listdir(pdirattachment)
fileslst.sort()
for i in range(0, len(fileslst), 1):
tmplstfile = fileslst[i]
logaq('file:' + tmplstfile, 'i')
lpath = os.path.join(pdirattachment, tmplstfile)
if time.time() - os.path.getmtime(lpath) <= 1:
logaq('mtime<=1:' + lpath)
lexistnewfile = True
continue
tmpmuid = get2betw13(tmplstfile, public.GFLAG_UID,
public.GSUFFIX_TARGZFILEMAIL)
#--split file process
if tmpmuid == pretmpmuid:
existsplitf = True
if (existsplitf and (i<len(fileslst)-1) and get2betw13(fileslst[i+1],public.GFLAG_UID,'_')!=tmpmuid) \
or (existsplitf and i==len(fileslst)-1):
tmpfilelike = get2betw13(
tmplstfile, '', public.GSUFFIX_TARGZFILEMAIL
) + public.GSUFFIX_TARGZFILEMAIL
writefile(
pdirattachment + 'UID' + pretmpmuid,
ms_cmd_merge.replace('FILENAME', tmpfilelike),
'.cmd')
tmplstfile = tmplstfile + ',UID' + pretmpmuid + '.cmd'
existsplitf = False
#--end split file process
idxmail = getidxbyuidflag(
tmpmuid, public.GSTATUS_SQLDATA_DOWNLOADED, pmaillst)
ltmpuid = pmaillst[idxmail]['uid']
#get mailheader info
if idxmail > -1:
logaq('sendmail uid:' + tmpmuid)
ms_msg1 = ms_msg1.replace('STOPTIME',
public.gstop_time)
ms_msg1 = ms_msg1.replace('MAX_QTIME',
str(public.gmmaxqtime / 60))
send_mail(
public.gmusr, pmaillst[idxmail]['from'] + ',' +
pmaillst[idxmail]['to'], pmaillst[idxmail]['cc'],
'Re:' + pmaillst[idxmail]['subject'], ms_msg1,
public.gdir_atachmt_willsend, tmplstfile)
if pmaillst[idxmail]['uid'] != ltmpuid:
logaq('!!!!!!multithreads data conflict!!!!!!', 'e')
if os.path.exists(public.gdir_history + fileslst[i]):
os.remove(public.gdir_history + fileslst[i])
logaq('moving file to his uid:' + tmpmuid)
shutil.move(lpath, public.gdir_history)
#--delete pmaillst
if pretmpmuid and tmpmuid != pretmpmuid:
logaq('deleting mailmeta by preuid(cur<>pre)')
deldict_fromlist(pmaillst, pmutex, 'uid', pretmpmuid)
if i == (len(fileslst) - 1):
logaq('deleting mailmeta by uid(lastone)')
deldict_fromlist(pmaillst, pmutex, 'uid', tmpmuid)
#--end delete
pretmpmuid = tmpmuid
process_exceptmaillst(pmaillst, pmutex)
if not lexistnewfile:
setfilepgok(public.gmutex_filepg, False)
else:
time.sleep(3)
logaq(pmaillst)
logaq('while end', 'i')
except Exception as e:
logaq('mailss Error: %s' % e, 'e')
time.sleep(3)
finally:
logaq('while finally', 'i')
saveobject(pmaillst, 'maills4')
#refresh_ini()
finally:
try:
ms_serv.quit()
except Exception as e:
logaq('smtp exit exception: %s' % e, 'i')
def s2file_main(pmailslst, pparaslst, pmutex, pdb, pproclst, pmutexp,
pdbconlst): #add pdbconlst
while not getmailsaok(public.gmutex_mailsa) and not breaktime():
logaq('waiting for mailsa 10s...', 'i')
time.sleep(10)
continue
preuid = ''
curuid = ''
try:
fileslst = os.listdir(public.gdir_atachmt_downloaded)
fileslst.sort()
logaq('FilesCNT:' + str(len(fileslst)))
for i in range(0, len(fileslst), 1):
try:
lexistnewfile = False
#only process file start with pdb
logaq('Process idx:' + str(i))
if get2betw13(fileslst[i], '', public.GPREFIX_FILEUID) == pdb:
logaq('Curfile:' + fileslst[i])
lpath = os.path.join(public.gdir_atachmt_downloaded,
fileslst[i])
curuid = get2betw13(fileslst[i], public.GPREFIX_FILEUID,
public.GSEPTOR_FILE)
#if exist one attachment error in same mail,will not process others
if getflagbyuid(curuid, pmailslst) == public.GSTATUS_ERROR:
if os.path.exists(public.gdir_history + fileslst[i]):
os.remove(public.gdir_history + fileslst[i])
logaq('moving file to his')
shutil.move(lpath, public.gdir_history)
continue
#if all attachment have been processed in same mail,update flag to GSTATUS_SQLDATA_EXPORTED
if preuid and preuid != curuid:
updateflagmsg_byuidflag(
pmailslst, pmutex, preuid,
public.GSTATUS_SQLDATA_BEFEXPORT,
public.GSTATUS_SQLDATA_EXPORTED, '')
if time.time() - os.path.getmtime(lpath) <= 1:
logaq('MTime<=1:' + lpath)
lexistnewfile = True
continue
if not checkfilestatus(pmailslst, lpath, fileslst[i]):
continue
else:
#process file context
#1.judging file charset
#2.changing select segment to create or replace view ..... as select segment
#3.judging whether exist risk factors
if not processfile(pmailslst, pmutex, lpath,
fileslst[i]):
continue
#end process file context
#login to sqlplus exec sqlfile
if not createviews(pmailslst, pparaslst, pmutex, lpath,
fileslst[i]):
continue
else:
callprocedure(pmailslst, pparaslst, pmutex, lpath,
fileslst[i], pproclst, pmutexp,
pdbconlst)
preuid = curuid
logaq('Curfile end')
except Exception as e:
logaq('s2file Forloop Error: %s' % e, 'e')
time.sleep(3)
updateflagmsg_byuidflag(pmailslst, pmutex, curuid,
public.GSTATUS_ATACHMT_DOWNLOADED,
public.GSTATUS_ERROR, str(e))
updateflagmsg_byuidflag(pmailslst, pmutex, curuid,
public.GSTATUS_SQLDATA_BEFEXPORT,
public.GSTATUS_ERROR, str(e))
updateflagmsg_byuidflag(pmailslst, pmutex, curuid,
public.GSTATUS_SQLDATA_BEFEXPORT,
public.GSTATUS_SQLDATA_EXPORTED, '')
if not lexistnewfile:
setmailsaok(public.gmutex_mailsa, False)
else:
time.sleep(3)
sets2fileok(public.gmutex_s2file, True)
logaq(pmailslst)
except Exception as e:
logaq('s2file Error: %s' % e, 'e')
finally:
#saveobject(pmailslst,'maills2')
logaq('s2file finally')