def _parseStatement_Create(self, statement):
match = self.re_CreateStatement.search(statement)
if match:
table = SQLHelper.SqlTable( match.group('tablename') )
for ln in match.group('body').split(','):
match = self.re_GetColumn.search(ln)
if match:
name = match.group('name')
type = match.group('type')
if re.search("unsigned",ln): unsigned = True
else: unsigned = False
if re.search("NOT NULL",ln): nullVal = False
else: nullVal = False
if re.search("AUTO_INCREMENT",ln): autoInc = True
else: autoInc = False
match = re.search("DEFAULT\s+(?P<val>\S+)",ln)
if match: default = match.group('val')
else: default=None
table.addColumn( SQLHelper.SqlColumn(columnName=name, columnType=type,
isPrimary=False, defaultVal=default,
nullValid=nullVal, autoIncrement=autoInc,
isUnsigned=unsigned) )
if re.search("PRIMARY\s+KEY",ln):
primaryKey = re.search("PRIMARY\s+KEY\s+[(][`](?P<key>\w+)[`][)]",ln).group('key')
for column in table.columns:
if column.name == primaryKey:
column.primary = True
self.addTable(table)
def createStr_JavaStruct(self): s = "" s += self.createStr_Header() + "\n" s += "\n" s += SQLHelper.indent(self.createStr_Variables()) + "\n" s += "\n" s += SQLHelper.indent(self.createStr_Constants()) + "\n" s += "\n" s += SQLHelper.indent(self.createStr_Init()) + "\n" s += "\n" s += SQLHelper.indent(self.createStr_getValues()) + "\n" s += "\n" s += SQLHelper.indent(self.createStr_fromCursor()) + "\n" s += "\n" s += SQLHelper.indent(self.createStr_getProjection()) + "\n" s += "\n" s += SQLHelper.indent(self.createStr_isString()) + "\n" s += "\n" s += SQLHelper.indent(self.createStr_needsConverted()) + "\n" s += "\n" s += SQLHelper.indent(self.createStr_jsonToCV()) + "\n" s += "\n" s += SQLHelper.indent(self.createStr_getDisplayData()) + "\n" s += "\n" s += self.createStr_Footer() return s
def checkOriginFilesConsistency(caseName):
syslog.syslog("PCAP APP: checkOriginFilesConsistency started: "+str(datetime.datetime.now()))
originFiles = SQLHelper.loadFiles(caseName, 'origin')
#files in directory
files = [f for f in os.listdir(CASES_DIR + caseName + ORIGIN_DIR) if os.path.isfile(os.path.join(CASES_DIR + caseName + ORIGIN_DIR,f))]
#add orrigin/ prefix to each file
files = map(lambda fileName: 'origin/'+ fileName, files)
# check if are some new or deleted files
newFiles = [file for file in files if file not in originFiles]
deletedFiles = [file for file in originFiles if file not in files]
for file in deletedFiles:
saveFile.removeFile(caseName, CASES_DIR + caseName + PCAP_DIR + file)
for file in newFiles:
a = saveFile.addFile(caseName, CASES_DIR + caseName + PCAP_DIR + file)
# check if files was overwritten
originFiles = SQLHelper.loadFiles(caseName, 'origin', additionalColumn = ',SIZE')
for file in originFiles:
fileSize = os.path.getsize(CASES_DIR + caseName + PCAP_DIR + file[0])
if file[1] != fileSize:
saveFile.removeFile(caseName, CASES_DIR + caseName + PCAP_DIR + file[0])
saveFile.addFile(caseName, CASES_DIR + caseName + PCAP_DIR + file[0])
syslog.syslog("PCAP APP: checkOriginFilesConsistency ended: "+str(datetime.datetime.now()))
def main():
SQLHelper.initdatabase(config.DBNAME)
SQLHelper.inittable()
db = pymysql.connect(host='localhost', user='******',
password=config.SQLPWD, db=config.DBNAME,
port=3306, charset='utf8', local_infile=True)
cursor = db.cursor()
templist = [(CNNAMES, 0), (CNANAMES, 1), (EN2CNNAMES, 2)]
# 1. 单条插入最慢
# 2. 多条插入后再提交事务稍快
# 3. load data最快
# 注意：有的系统上面要以\r\n分割行，有的系统要以\n分割行
for name in templist:
sql = "load data local infile '{}' into table chinese " \
"lines terminated by '\\r\\n' ignore 3 lines (content) set nametype={};" \
.format(name[0], name[1])
loaddata(sql, cursor)
templist = [(CNIDIOM, 'idiom'), (JPNAMES, 'japanese'), (ENNAMES, 'english')]
for name in templist:
sql = "load data local infile '{}' into table {} " \
"lines terminated by '\\r\\n' ignore 3 lines (content) ;" \
.format(name[0], name[1])
loaddata(sql, cursor)
cursor.close()
db.close()
def sendFriends(self):
#发送好友列表
result=sql.GetData("select * from Friend where UserNumber='"+self.student[1]+"'")
length=len(result)
if length !=0:
for item in result:
stu=sql.GetData("select * from User where UserNumber='"+item[2]+"'")[0]
self.sendLine("friendlist|||"+str(stu[1])+"|||"+str(stu[2])+"|||"+str(stu[4])+"|||"+str(stu[5]))
def inserFriend(self,number):
#添加一条好友记录
field=["UserNumber","FriendNumber"]
values=[str(self.student[1]),str(number)]
values1=[str(number),str(self.student[1])]
if sql.GetCountMore("Friend", field,values)==0:
sql.Insert("Friend", field, values)
if sql.GetCountMore("Friend", field,values1)==0:
sql.Insert("Friend",field,values1)
def GetCourseOfAready(Account,Password,Term):
atry=actiontojwc2.Tojwc()
# zhanghao="020252009009";
# Term="20111"
get=atry.trytoconnect(Account,Password,Term)
if get==0:
try:
print time.ctime()+"\t"+"抓取已选课程---学号："+Account
atry.getContents()
parser=atry.GetAlreadyCourse("http://jwc.ouc.edu.cn:8080/ouc/SS020.do")
option=parser('option')#抓取option数量
#判断信息中是否含有
if len(option)>0:
for i in range(len(option)):
termChoice=""#课程学期
termChoice=str(parser('option')[i]['value'])
if termChoice>"2009"+"0":
html=atry.GetAreadyCourseContent(termChoice, "http://jwc.ouc.edu.cn:8080/ouc/SS020.do")
course=html.findAll('table',width="100%")
#print str(course[3]),str(course[4])
ask=["UserNumber","Term"]
answer=[Account,termChoice]
count=SQLHelper.GetCountMore("Course", ask, answer);
#count=0代表第一次插入已选课程
if count==0:
field=["UserNumber","CourseContent","Curriculum","Term"]
#MySQLdb.escape_string()参数必须是string类型的
# str(course[3])为已选课程的详细信息,str(course[4])为已选课程的表哥形式
value=[Account,MySQLdb.escape_string(str(course[3])),MySQLdb.escape_string(str(course[4])),termChoice]
insert=SQLHelper.Insert("Course", field, value)
if insert==1:
print time.ctime()+"\t成绩---"+Account+"---Insert OK"
else:
print "插入个人课表出现程序上的错误，请检查！！"
else:
fields=["CourseContent","Curriculum"]
values=[MySQLdb.escape_string(str(course[3])),MySQLdb.escape_string(str(course[4]))]
ask=["UserNumber","Term"]
answer=[Account,termChoice]
update=SQLHelper.UpdateMore("Course", fields, values, ask, answer)
if update==1:
print time.ctime()+"\t课表---"+Account+"---Update OK"
else:
print "插入个人课表出现程序上的错误，请检查！！"
atry.logout()
print time.ctime()+"\t"+"学生课表更新完成。"
except Exception:
atry.logout()
print traceback.print_exc()
return 3
else :
if get==1:
print time.ctime()+"\t课表---"+Account+"登陆失败，请检查用户名、密码是否正确。"
if get==2:
print time.ctime()+"\t课表---"+Account+"该用户已经登录，请登出或稍后再试!"
def createStr_CreateStr(self):
s = "public static final String[] SQL_CREATE_ENTRIES = {\n"
for table in self.tables:
s += SQLHelper.indent( SQLHelper.toJavaString(table.createStr_CreateStr()))
s += ",\n\n"
tmp = table.createStr_InsertStr()
if tmp:
s += SQLHelper.indent( SQLHelper.toJavaString(tmp))
s += ",\n\n"
return s[0:-3] +"\n};"
def addFriend(self,number,hostnum,flag):
result=sql.GetData("select * from User where UserNumber='"+number+"'")
if len(result)==0:
self.sendLine("friends|||add|||0")
else:
#self.sendLine("friends|||add|||3")
sql.Insert("Message", ["sourcenum","desnum","flag"], [hostnum,number,flag])
if flag=="2":
#同意好友申请，数据库中增加记录
self.inserFriend(number)
self.sendFriends()
def createStr_CreateStr(self):
s = "public static final String[] SQL_CREATE_ENTRIES = {\n"
for table in self.tables:
s += SQLHelper.indent(
SQLHelper.toJavaString(table.createStr_CreateStr()))
s += ",\n\n"
tmp = table.createStr_InsertStr()
if tmp:
s += SQLHelper.indent(SQLHelper.toJavaString(tmp))
s += ",\n\n"
return s[0:-3] + "\n};"
def createStr_JavaSqLite(self): s = "" s += self.createStr_Header() +"\n" s += "\n" s += SQLHelper.indent(self.createStr_Classes()) +"\n" s += "\n" s += SQLHelper.indent(self.createStr_CreateStr()) +"\n" s += "\n" s += SQLHelper.indent(self.createStr_DropStr()) +"\n" s += "\n" s += self.createStr_Footer() return s
def sendMsg(self):
result=sql.GetData("select * from Message where desnum='"+self.student[1]+"'")
if not len(result)==0:
for item in result:
if item[3]=="2":
self.sendFriends()
stu=sql.GetData("select * from User where UserNumber='"+item[1]+"'")
for one in stu:
self.sendLine("friends|||add|||"+str(item[3])+"|||" +str(one[1])+"|||"+str(one[2])+"|||"+str(one[4])+"|||"+str(one[5]))
sql.Delete("Message", "desnum", self.student[1])
else:
print self.student[1]+"\tNo new message."
def createStr_JavaSqLite(self):
s = ""
s += self.createStr_Header() + "\n"
s += "\n"
s += SQLHelper.indent(self.createStr_Classes()) + "\n"
s += "\n"
s += SQLHelper.indent(self.createStr_CreateStr()) + "\n"
s += "\n"
s += SQLHelper.indent(self.createStr_DropStr()) + "\n"
s += "\n"
s += self.createStr_Footer()
return s
def AnalyzeChoiceCourse(Account,Password,Term):
atry=actiontojwc2.Tojwc()
get=atry.trytoconnect(Account,Password,Term)
if get==0:
atry.getContents()
parser=atry.GetStudentChooseCourse("http://jwc.ouc.edu.cn:8080/ouc/SS003.do")
p=re.compile("\<span\>\d\d\d\d\d\d\d\<br \/>\<\/span\>")
number=parser('span')
for i in range(len(number)):
coursenumber=p.findall(str(number[i]))
if len(coursenumber)>0:
courseNumber=str(coursenumber[0])[6:len(coursenumber)-14];
# data0=SQLHelper.GetData("select * from MajorCourse where ChooseNumber='"+courseNumber+"' and Term='"+Term+"'")
# print courseNumber
#
# majorID=None;
# pNumber=None;
# if len(data0)>0:
## for k in range(len(data0[0])):
## print str(data0[0][k])
# majorID=string.atoi(str(data0[0][0]))
# pNumber=string.atoi(str(data0[0][10]))
# else:
# data1=SQLHelper.GetData("select * from AllCourse where CourseNumber='"+courseNumber+"' and Term='"+Term+"'")
# if len(data1)>0:
## for l in range(len(data1[0])):
##
## print str(data1[0][l])
# majorID=string.atoi(str(data1[0][0]))
# pNumber=string.atoi(str(data1[0][8]))
# else:
# print "课程号为："+courseNumber+"不存在！！"
## print majorID,pNumber
ask=["MajorID","Account","Term"]
answer=[courseNumber,Account,Term]
count=SQLHelper.GetCountMore("CourseAnalysis", ask, answer);
#count=0表中没有该课程的任何信息
if count==0:
field=["MajorID","Account","Term"]
# #MySQLdb.escape_string()参数必须是string类型的
# # str(course[3])为已选课程的详细信息,str(course[4])为已选课程的表哥形式
value=[courseNumber,Account,Term]
# for j in range(len(value)):
# print str(value[j]),type(value[j])
insert=SQLHelper.Insert("CourseAnalysis",field,value)
if insert==1:
print "OK"
else:
print "插入选课分析表出现程序上的错误，请检查！！"
atry.logout()
else :
print get
def getReadableFileInfo(fileName, caseName):
fileID = SQLHelper.getFileID(fileName, caseName)
info = SQLHelper.getFileInfo(fileID)
s = sizeof_fmt(info[1]).replace(" ", "")
if info[0] is None:
#return ["None",sizeof_fmt(info[1]), info[2], info[3], info[4]]
return ["None",s, info[2], info[3], info[4], info[5], fileID]
conn = sqlite3.connect(DATABASE)
conn.execute('pragma foreign_keys=ON')
q = conn.execute("SELECT CONTENT FROM FILTERS WHERE ID = ?", (info[0],))
filterContent = q.fetchone()[0]
conn.commit()
conn.close()
return [filterContent, s, info[2], info[3], info[4], info[5], fileID]
def getSizeOfNewFilesInCase(caseName):
originFiles = SQLHelper.loadFiles(caseName, 'origin')
#files in directory
files = [f for f in os.listdir(CASES_DIR + caseName + ORIGIN_DIR) if os.path.isfile(os.path.join(CASES_DIR + caseName + ORIGIN_DIR, f))]
files = map(lambda fileName: 'origin/'+ fileName, files)
newFiles = [file for file in files if file not in originFiles]
# check if files was overwritten
overwritten = 0
originFiles = SQLHelper.loadFiles(caseName, 'origin', additionalColumn = ',SIZE')
for file in originFiles:
if os.path.isfile(CASES_DIR + caseName + PCAP_DIR + file[0]):
fileSize = os.path.getsize(CASES_DIR + caseName + PCAP_DIR + file[0])
if file[1] != fileSize:
overwritten += fileSize
return sum(os.path.getsize(CASES_DIR+ caseName + PCAP_DIR + f) for f in newFiles) + overwritten if newFiles or overwritten != 0 else ""
def GetBiXiuCourse(Account,Password,Term):
atry=actiontojwc2.Tojwc()
get=atry.trytoconnect(Account,Password,Term)
if get==0:
try:
print time.ctime()+"\t"+"抓取通识必修课---学期："+Term
atry.getContents()
academic=atry.GetAcademic("http://jwc.ouc.edu.cn:8080/ouc/SS013.do")
# term="20111"
number=len(academic[0])
#print str(academic[0][0]),str(academic[0][2])
m=0
for i in range(2):
if i==0:
m=0
else:
m=2
for j in range(len(academic)):
CourseOne=atry.GetAllCourse(str(academic[j][0]), str(academic[j][2]), str(m), "http://jwc.ouc.edu.cn:8080/ouc/SS013.do")
#print CourseOne
coursemore=actiontojwc2.GrabCourse(CourseOne)
for k in range(len(coursemore)):
ask=["CourseNumber","SchoolHouse","Term"]
answer=[str(coursemore[k][0]),str(m),Term]
count=SQLHelper.GetCountMore("AllCourse", ask, answer)
#count=SQLHelper.GetCountOne("AllCourse", "CourseNumber", str(coursemore[k][0]))
#print count
if count>0:
continue;
else:
field=["CourseName","CourseNumber","CourseTime","XueShi","CourseScore","Money","TeacherName","PeopleNumber","Remark","SchoolHouse","Term","Academic","CourseType"]
value=[str(academic[j][3]),str(coursemore[k][0]),str(coursemore[k][1]),str(coursemore[k][2]),str(coursemore[k][3]),str(coursemore[k][4]),str(coursemore[k][5]),str(coursemore[k][6]),str(coursemore[k][7]),str(m),Term,str(academic[j][1]),"0"]
insert=SQLHelper.Insert("AllCourse",field ,value)
if insert==1:
continue
else:
print "通识必修课抓取错误。"
print time.ctime()+"\t"+"通识必修课抓取完成。"
atry.logout()
except Exception:
atry.logout()
print traceback.print_exc()
return 3
else :
if get==1:
print "登陆失败，请检查用户名、密码是否正确。"
if get==2:
print "该用户已经登录，请登出或稍后再试!"
def findGap(crewID, date, cursor):
# searches the SQL for a given crew and date
# Get all requests older than current date
# [11] is MOB ETA
# [13] is DEMOB ETD
rows = SQ.getDatesOld(crewID, date, cursor)
print(len(rows))
reqIDGap = -1
# Checks if crew is assigned after most recent assignment
if (not checkFirstRow(crewID, rows[0], date, cursor)):
for i in range(len(rows) - 1):
# determines how long it would take to go to homebase on current assignmnet
timeHome = calcTimeNeeded(crewID, rows[i][0], cursor)
# determines how long the gap between assignmnets actually is
timeBetween = gapCalc(rows[i][11], rows[i + 1][13])
print(timeHome)
print(timeBetween)
if (timeHome != None and timeBetween != None):
# diff is the difference between the time it would take to get home and the time they have
# if it is positive then the crew is at their homebase
diff = (timeBetween - timeHome)
delta = diff.total_seconds()
print("Delta: ", delta)
# Positive means that the crew CAN return home for R and R
if delta >= 0:
print("POSITIVE: ", diff)
reqIDGap = i
if (reqIDGap != -1):
break
if (reqIDGap != -1):
getPrevReqDays(rows, i, crewID, date, cursor)
else:
return 14
def updateFile(filePath, caseName, filterID):
syslog.syslog("PCAP APP: updateFile: "+filePath+" started: "+str(datetime.datetime.now()))
fileName = getDBNameFromPath(filePath)
dateTimes = getDateTimeFromFile(filePath)
fileSize = os.path.getsize(filePath)
fileID = SQLHelper.getFileID(fileName, caseName)
if fileID:
SQLHelper.updateFileInfo(fileID, filterID, fileSize, dateTimes)
else:
conn = sqlite3.connect(DATABASE)
q = conn.execute("SELECT ID FROM CASES WHERE CASES.NAME = ?",(caseName,))
caseID = q.fetchone()[0]
q = conn.execute("INSERT INTO FILES VALUES (null,?,'filtered',?,?,?,?,?,?,null)",(fileName, caseID, filterID, fileSize, dateTimes[0], dateTimes[1],'description',))
conn.commit()
conn.close()
syslog.syslog("PCAP APP: updateFile: "+filePath+" ended: "+str(datetime.datetime.now()))
def getFilter(caseName, fileName = None, type = "case"):
conn = sqlite3.connect(DATABASE)
if fileName is None:
q = conn.execute("SELECT FILTERID FROM CASES WHERE CASES.NAME = ?",(caseName,))
IDs = q.fetchone()
if IDs is None:
return "None"
filterID = IDs[0]
q = conn.execute("SELECT CONTENT FROM FILTERS WHERE FILTERS.ID = ?",(filterID,))
filterContent = q.fetchone()
# if filter exist set it to its string value else set it to None
filterContent = filterContent[0] if filterContent else "None"
else:
fileID = SQLHelper.getFileID(fileName, caseName)
q = conn.execute("SELECT FILTERID FROM FILES WHERE FILES.ID = ?", (fileID,))
IDs = q.fetchone()
if IDs is None:
return "None"
filterID = IDs[0]
q = conn.execute("SELECT CONTENT FROM FILTERS WHERE FILTERS.ID = ?",(filterID,))
filterContent = q.fetchone()
# if filter exist set it to its string value else set it to None
filterContent = filterContent[0] if filterContent else filterID
conn.close()
return filterContent if filterContent else "None"
def applyTmpFilter(filePath, filterContent, caseName):
currentFilter = helper.getFilter(caseName, helper.getDBNameFromPath(filePath), type = 'file')
filteredFileName = applyFilterOnFile(filePath, filterContent, caseName, True)
if not os.path.isfile(CASES_DIR + caseName + TMP_DIR + filteredFileName):
return None
summFilter = currentFilter + ' && ' + filterContent if currentFilter != 'None' else filterContent
conn = sqlite3.connect(DATABASE)
conn.execute('pragma foreign_keys=ON')
# crete new fitler in db
conn.execute("INSERT INTO FILTERS VALUES(null, ?, \'\', \'\')",(summFilter,))
q = conn.execute('SELECT max(ID) FROM FILTERS')
filterID = q.fetchone()[0]
q = conn.execute("SELECT ID FROM CASES WHERE CASES.NAME = ?",(caseName,))
IDs = q.fetchone()
caseID = IDs[0]
if SQLHelper.getFileID(helper.getDBNameFromPath(CASES_DIR + caseName + TMP_DIR + filteredFileName), caseName) is not None:
conn.commit()
conn.close()
helper.updateFile(CASES_DIR + caseName + TMP_DIR + filteredFileName, caseName, filterID)
else:
sourceFile = helper.getDBNameFromPath(filePath)
fileSize = os.path.getsize(CASES_DIR + caseName + TMP_DIR + filteredFileName)
dateTimes = helper.getDateTimeFromFile(CASES_DIR + caseName + TMP_DIR + filteredFileName)
conn.execute("INSERT INTO FILES VALUES (null, ?, ?, ?, ?, ?, ?, ?, ?, ?)", ("tmp/"+filteredFileName, "tmp", caseID, filterID, fileSize, dateTimes[0], dateTimes[1], sourceFile,'description',))
#conn.execute("INSERT INTO FILES VALUES (null,\'"+"tmp/"+filteredFileName+"\',\'tmp\',"+str(caseID)+","+str(filterID)+","+str(fileSize)+",\'"+dateTimes[0]+"\',\'"+ dateTimes[1]+"\',\'"+sourceFile+"\')")
conn.commit()
conn.close()
return filteredFileName
def applyTimeFilterOnFile(filePath, caseName, start = '', end = '', override = False):
syslog.syslog("PCAP APP: applyTimeFilterOnFile: "+filePath+" started: "+str(datetime.datetime.now()))
if start == '' and end == '':
return None
tmpF = tempfile.NamedTemporaryFile(delete=True)
outputFileName = os.path.basename(tmpF.name + '.pcap')
outputFilePath = CASES_DIR + caseName + TMP_DIR + outputFileName
subprocess.call(['editcap','-A', start, '-B',end, filePath,outputFilePath])
if not os.path.isfile(outputFilePath):
return None
if override:
os.rename(outputFilePath, filePath)
outputFilePath = filePath
outputFileName = helper.getDBNameFromPath(filePath)
conn = sqlite3.connect(DATABASE)
conn.execute('pragma foreign_keys=ON')
# crete new fitler in db
q = conn.execute("SELECT ID FROM CASES WHERE CASES.NAME = ?", (caseName,))
IDs = q.fetchone()
caseID = IDs[0]
if SQLHelper.getFileID(helper.getDBNameFromPath(outputFilePath), caseName) is not None:
conn.commit()
conn.close()
helper.updateFile(outputFilePath, caseName, 'null')
else:
sourceFile = helper.getDBNameFromPath(filePath)
fileSize = os.path.getsize(outputFilePath)
dateTimes = helper.getDateTimeFromFile(outputFilePath)
conn.execute("INSERT INTO FILES VALUES (null, ?, ?, ?, null, ?, ?, ?, ?, ?)", ("tmp/"+outputFileName, "tmp", caseID, fileSize, dateTimes[0], dateTimes[1], sourceFile,'description',))
# conn.execute("INSERT INTO FILES VALUES (null,\'"+"tmp/"+outputFileName+"\',\'tmp\',"+str(caseID)+",null,"+str(fileSize)+",\'"+dateTimes[0]+"\',\'"+ dateTimes[1]+"\',\'"+sourceFile+"\')")
conn.commit()
conn.close()
syslog.syslog("PCAP APP: applyTimeFilterOnFile: "+filePath+" ended: "+str(datetime.datetime.now()))
return outputFilePath
def sendNewsPage(self,newsclass):
#教务处新闻页数
jwc=sql.GetCountOne("News", "NewsClassID", newsclass)
page=jwc/10
if jwc%10 !=0:
page=page+1
self.sendLine("newspage|||"+str(page)+"|||"+newsclass)
def sendTerm(self):
#发送用户的学期列表，用户通过学期来获取课程信息
result=sql.GetData("select Term from Course where UserNumber="+str(self.student[1]))
length=len(result)
for one in result:
self.sendLine("term|||"+str(one[0]))
# self.sendLine("termend")
self.sendCourse(result[length-2][0])
def showTables():
ret = sql.GetCommand("show tables")
if len(ret) == 0:
print "No table found!"
else:
for table in ret:
print table
return ret
def ODS():
session['refresh'] = False
latestAssetDate = SQLHelper.getLatestAssetDate()
AssetsUpdatedThisMonth = SQLHelper.getAssetsUpdatedThisMonth(
latestAssetDate)
NewFundsThisMonth = SQLHelper.getNewFundsThisMonth()
NewFundsThisMonth = sum(NewFundsThisMonth)
SQLHelper.generateAssetGraph(latestAssetDate)
SQLHelper.generateNewFundsGraph()
#SQLHelper.generateAssetMonthly(latestAssetDate)
f = open("previous.txt", "r")
previous = f.readline()
f.close()
fundsSinceRefresh = NewFundsThisMonth - int(previous)
f = open("previous.txt", "w")
f.write(str(NewFundsThisMonth))
f.close()
return render_template('ODS.html',
latestAssetDate=latestAssetDate,
AssetsUpdatedThisMonth=AssetsUpdatedThisMonth,
NewFundsThisMonth=NewFundsThisMonth,
fundsSinceRefresh=fundsSinceRefresh)
def Insert(title,content,time,name,id):#插入到数据库中
count=GetCount(title,name,time);
if count <1:
number="insert into News(Title,Content,Time,NewsClassName,NewsClassID) values('%s','%s','%s','%s','%d')"% (title,content,time,name,id)
insert=SQLHelper.InsertAboutNumber(number)
if insert==1:
print "OK"
else:
print "插入新闻出现错误！！"
def send10News(self,page,newsclass):
#发送10条新闻，客户端一页显示10条
ten=sql.GetData("SELECT * FROM News where NewsClassID="+newsclass+" order by id desc LIMIT "+str(int(page)*10)+ ",10")
if len(ten)!=0:
for item in ten:
print item[0]
self.sendLine("news|||"+str(item[0])+"|||"+str(item[1])+"|||"+str(item[4]))
if page=="0":
self.curnewsid[newsclass]=str(ten[0][0])
def searchFriend(self,name):
result=sql.GetData("select * from User where UserName='******'")
#搜索结果
#不存在
if len(result)==0:
self.sendLine("friends|||search|||0")
else:
for item in result:
self.sendLine(("friends|||search|||1|||"+item[1]+"|||"+item[2]+"|||"+item[4]+"|||"+item[5]).encode('UTF-8'))
def showInformation(tablename):
cmd = "select * from %s" % tablename
ret = sql.GetCommand(cmd)
if len(ret) == 0:
print "No information found in %s" % (tablename)
else:
for item in ret:
print item
return ret
def GetCount(title, classname): #验证数据库中是否包含该条信息
# cur=db.cursor()
#count=cur.execute("select * from Information where Title='"+title+"' and ClassName='"+classname+"'")
ask = ["Title", "NewsClassName"]
answer = [title, classname]
count = SQLHelper.GetCountMore("News", ask, answer)
#cur.close()
#db.close()
return count
def sendCourse(self,term):
#发送课程
result=sql.GetData("select * from Course where UserNumber="+str(self.student[1])+" and Term="+term)[0]
# self.sendLine("coursetable")
# self.send(str(result[2]))
# self.sendLine("yeah")
# self.sendLine("coursetable|||"+str(term)+"|||0|||")
# self.transport.write(str(result[2]))
# print result[2]
self.send("coursetable|||"+str(term)+"|||1|||",str(result[3]))
def sendNewsbyID(self,id,newsclass):
#id=新闻id，newsclass=新闻类型
#按照id发送新闻
result=sql.GetData("select * from News where NewsClassID="+newsclass+" and id >'"+id+"' order by id desc ")
if len(result) !=0:
self.curnewsid[newsclass]=str(result[0][0])
for item in result:
self.sendLine("newsupdated|||"+str(item[0])+"|||"+str(item[1])+"|||"+str(item[4]))
else:
print newsclass+"\tNo new news."
def checkFilteredFileConsistency(caseName):
syslog.syslog("PCAP APP: checkFilteredFileConsistency started: "+str(datetime.datetime.now()))
filteredFiles = SQLHelper.loadFiles(caseName, 'filtered')
#files in directory
files = [f for f in os.listdir(CASES_DIR + caseName + PCAP_DIR) if os.path.isfile(os.path.join(CASES_DIR + caseName + PCAP_DIR, f))]
deletedFiles = [file for file in filteredFiles if file not in files]
for file in deletedFiles:
saveFile.removeFile(caseName, CASES_DIR + caseName + PCAP_DIR + file)
syslog.syslog("PCAP APP: checkFilteredFileConsistency ended: "+str(datetime.datetime.now()))
def start():
# This should be removed if ever committed to an online place or shared
address = "*****@*****.**"
password = "******"
mainPath = r"C:\Users\Carson\OneDrive\College CSU\Year 4\Summer 2018"
download = mainPath + r"\IHT Project\Data\Server\Daily Test\Full"
con = connectEmail(address, password)
emailNumbers = get_unread(con)
print("Unread: ", emailNumbers)
count = 1
conn = SQ.connectDB("root", "Code", "localhost", "iht_test")
cursor = conn.cursor(buffered=True)
# Checks if we have unread emails
if (emailNumbers != None):
# Iterates through all the unread emails
for i in emailNumbers:
print("Running: ", str(count) + " / ", len(emailNumbers))
#### Email Download and Process ####
result, data = con.fetch(i, '(RFC822)')
msg = email.message_from_bytes(data[0][1])
temp = data[0][1].splitlines()
date = str(temp[2][12:])
date = date[3:-1]
get_attachments(download, msg, date=date)
# Gets the filename of the downloaded attatchemnts
fileName = "blank"
for part in msg.walk():
if (fileName == "blank" or fileName == None):
fileName = part.get_filename()
fileName = fileName[:-4] + "csv"
fileName = fileName.replace('\n', '')
fileName = fileName.replace('\r ', ' ')
# Determines which file we are dealing with and processes accordingly
if ("Report View of CREW STATUS TABLE - ONCE DAILY" in fileName):
#print("Update Crews")
crewsFile = open(download + "\\" + fileName)
SQ.update_crews(crewsFile, cursor)
conn.commit()
elif ("Report View of CREW REQUEST AUTOMATED QUERY - Released in prior 48 hours"
in fileName):
#print("Update All")
requestFile = open(download + "\\" + fileName)
SQ.update_all(requestFile, cursor)
conn.commit()
elif ("Report View of CREW REQUEST AUTOMATED QUERY - ONCE DAILY"
in fileName):
#print("Update All")
requestFile = open(download + "\\" + fileName)
SQ.update_all(requestFile, cursor)
conn.commit()
count += 1
def applyFilterOnCase(caseName, newFilter, mode = "edit", start = '', end = ''):
syslog.syslog("PCAP APP: applyFilterOnCase: "+caseName+" started: "+str(datetime.datetime.now()))
IDs = SQLHelper.getCaseAndFilterIDs(caseName)
if IDs is None:
return
caseID = IDs[0]
filterID = IDs[1]
conn = sqlite3.connect(DATABASE)
conn.execute('pragma foreign_keys=ON')
if mode == "edit":
if filterID:
q = conn.execute("UPDATE FILTERS SET CONTENT = ?, START_DATETIME = ?, END_DATETIME = ? WHERE FILTERS.ID = ?", (newFilter, start, end, filterID,))
#q = conn.execute("UPDATE FILTERS SET CONTENT = \'"+newFilter+"\', START_DATETIME = \'"+start+"\', end_DATETIME = \'"+end+"\' WHERE FILTERS.ID = "+str(filterID))
else:
q = conn.execute("INSERT INTO FILTERS VALUES(null, ?, ?, ?)",(newFilter, start, end,))
q = conn.execute('SELECT max(ID) FROM FILTERS')
filterID = q.fetchone()[0]
q = conn.execute("UPDATE CASES SET FILTERID = ? WHERE CASES.ID = ?", (filterID, caseID))
q = conn.execute("SELECT FILENAME FROM FILES WHERE FILES.TYPE = ? AND FILES.CASEID = ?", ('origin', caseID,))
else:
#q = conn.execute("SELECT CONTENT FROM FILTERS WHERE FILTERS.ID = ?", (filterID,))
#currentFilter = q.fetchone()
#currentFilter = currentFilter[0]
#if newFilter:
# newFilter = currentFilter + " && " + newFilter if currentFilter else newFilter
#else:
#newFilter = currentFilter
q = conn.execute("UPDATE FILTERS SET CONTENT = ?, START_DATETIME = ?, end_DATETIME = ? WHERE FILTERS.ID = ?", (newFilter, start, end, filterID,))
q = conn.execute("SELECT FILENAME FROM FILES WHERE FILES.TYPE = ? AND FILES.CASEID = ?", ('filtered', caseID))
files = []
for row in q:
files.append(CASES_DIR + caseName + PCAP_DIR + row[0])
conn.commit()
conn.close()
for file in files:
filteredFileName = applyFilterOnFile(file, newFilter, caseName, override = True)
if not os.path.isfile(CASES_DIR + caseName + PCAP_DIR + filteredFileName):
f = open(CASES_DIR + caseName + PCAP_DIR + filteredFileName, 'w')
f.write("")
f.close()
if mode == "append":
os.rename(CASES_DIR + caseName + PCAP_DIR + filteredFileName, file)
filteredFileName = os.path.basename(file)
applyTimeFilterOnFile(CASES_DIR + caseName + PCAP_DIR + filteredFileName, caseName, start, end, override = True)
helper.updateFile(CASES_DIR + caseName + PCAP_DIR + filteredFileName, caseName, filterID)
helper.clearTmp(caseName)
syslog.syslog("PCAP APP: applyFilterOnCase: "+caseName+" ended: "+str(datetime.datetime.now()))
def printPCAPs(case):
#print all pcap files in this case PCAPs directory
path = CASES_DIR + case.caseName + PCAP_DIR
listDir = os.listdir(path)
originFiles = SQLHelper.loadFiles(case.caseName, 'origin')
filteredFiles = SQLHelper.loadFiles(case.caseName, 'filtered')
tmpFiles = SQLHelper.loadFiles(case.caseName, 'tmp')
print '<h2>Available PCAP files</h2>'
formStr = '<form action="main.py" class="form-horizontal" method="post">'
formStr += '<a data-toggle="collapse" href="#originFiles"><h3>Original files: ('+helper.readableSizeOfDirectory(CASES_DIR+case.caseName+ORIGIN_DIR)+')</h3></a>' if originFiles else ""
formStr += '<div id="originFiles" class="collapse">'
formStr += '<table id="" class="display" cellspacing="0" width="100%">'
formStr += '<thead><tr><th>ID</th><th>Name</th><th>Size</th><th>First Packet</th><th>Last Packet</th><th>Filter</th><th>Source File</th><th>Description</th></tr><tbody>'
originFiles.sort()
for file in originFiles:
info = helper.getReadableFileInfo(file,case.caseName)
formStr += '<tr><th>%s</th><th><div class="radio"><label><input type="radio" value="%s" name="filePath"> <b>%s</b></label></div> </th><th>%s</th><th>%s</th><th>%s</th><th>%s</th><th>%s</th><th>%s</th></tr>' % (str(info[6]),path + file, file, info[1], info[2], info[3], info[0], info[4], info[5])
formStr += '</tbody></table></div>'
formStr += '<a data-toggle="collapse" href="#filteredFiles"><h3>Filtered files: ('+helper.readableSizeOfDirectory(CASES_DIR+case.caseName+PCAP_DIR)+')</h3></a>' if filteredFiles else ""
formStr += '<div id="filteredFiles" class="collapse">'
formStr += '<table id="" class="display" cellspacing="0" width="100%">'
formStr += '<thead><tr><th>ID</th><th>Name</th><th>Size</th><th>First Packet</th><th>Last Packet</th><th>Filter</th><th>Source File</th><th>Description</th></tr><tbody>'
filteredFiles.sort()
for file in filteredFiles:
info = helper.getReadableFileInfo(file,case.caseName)
sourceID = str(SQLHelper.getFileID(info[4],case.caseName)) + '.' if str(SQLHelper.getFileID(info[4],case.caseName)) != 'None' else ''
formStr += '<tr><th>%s</th><th><div class="radio"><label><input type="radio" value="%s" name="filePath"> <b>%s</b></label></div> </th><th>%s</th><th>%s</th><th>%s</th><th>%s</th><th>%s</th><th>%s</th></tr>' % (str(info[6]),path + file, file, info[1], info[2], info[3], info[0], sourceID + info[4], info[5])
formStr += '</tbody></table></div>'
tmpFiles.sort
if tmpFiles:
sourceID = str(SQLHelper.getFileID(info[4],case.caseName)) + '.' if str(SQLHelper.getFileID(info[4],case.caseName)) != 'None' else ''
formStr += '<a data-toggle="collapse" href="#tmpFiles"><h3>Temporary files: ('+helper.readableSizeOfDirectory(CASES_DIR+case.caseName+TMP_DIR)+')</h3></a>'
formStr += '<div id="tmpFiles" class="collapse">'
formStr += '<table id="" class="display" cellspacing="0" width="100%">'
formStr += '<thead><tr><th>ID</th><th>Name</th><th>Size</th><th>First Packet</th><th>Last Packet</th><th>Filter</th><th>Source File</th><th>Description</th></tr><tbody>'
for file in tmpFiles:
info = helper.getReadableFileInfo(file,case.caseName)
formStr += '<tr><th>%s</th><th><div class="radio"><label><input type="radio" value="%s" name="filePath"> <b>%s</b></label></div> </th><th>%s</th><th>%s</th><th>%s</th><th>%s</th><th>%s</th><th>%s</th></tr>' % (str(info[6]),path + file, file, info[1], info[2], info[3], info[0], sourceID + info[4], info[5])
formStr += '</tbody></table></div>'
formStr += '<div class="form-group"><div class="col-md-6">'
formStr += '<input type="submit" class="btn btn-default pull-right" value="Select File" name="selectFile"/>'
formStr += '<input type="submit" class="btn btn-default pull-left" name="clearTmp" value="Delete All Temporary Files"></div>'
formStr += '</div>'
formStr += '<input type="hidden" name="actions" value="deleteFile">'
formStr += '<input type="hidden" name="pagesToRender" value="showFile"><input type="hidden" name="caseName" value="'+case.caseName+'">'
formStr += '</form>'
print formStr
print '<hr/>'
def createStr_DropStr(self):
s = "public static final String[] SQL_DELETE_ENTRIES = {\n"
for table in self.tables:
tmp = "\""+ table.createStr_DropStr() +"\""
s += SQLHelper.indent(tmp) +",\n"
return s[0:-2] +"\n};"
def updateFileDescription(filePath, caseName, description):
fileName = getDBNameFromPath(filePath)
fileID = SQLHelper.getFileID(fileName, caseName)
SQLHelper.updateFileDescription(fileID, description)
def printInputFilterForm(filePath, caseName):
info = helper.getReadableFileInfo(helper.getDBNameFromPath(filePath), caseName)
approximateTime = helper.getRenderGraphTime(os.path.getsize(filePath))
title = 'Approximate time to render graph is: ' + helper.getReadableTimeInfo(approximateTime)
originFiles = SQLHelper.loadFiles(caseName, 'origin')
filteredFiles = SQLHelper.loadFiles(caseName, 'filtered')
tmpFiles = SQLHelper.loadFiles(caseName, 'tmp')
filesApproxStr = generateFilesAproximateTimeString(originFiles, caseName)
filesApproxStr += generateFilesAproximateTimeString(filteredFiles, caseName)
filesApproxStr += generateFilesAproximateTimeString(tmpFiles, caseName)
options = '<optgroup label="Original files">'
for file in originFiles:
options += ' <option value="'+file+'">'+file+'</option>'
options += '</optgroup>'
options += '<option data-divider="true"></option>'
options += '<optgroup label="Filtered files">'
for file in filteredFiles:
options += ' <option value="'+file+'">'+file+'</option>'
options += '</optgroup>'
options += '<option data-divider="true"></option>'
options += '<optgroup label="Temporary files">'
for file in tmpFiles:
options += ' <option value="'+file+'">'+file+'</option>'
allFiles = originFiles + filteredFiles + tmpFiles
table = '<div id="files" class="collapse">'
table += '<table id="" class="display" cellspacing="0" width="100%">'
table += '<thead><tr><th>ID</th><th>Name</th><th>Size</th><th>First Packet</th><th>Last Packet</th><th>Filter</th><th>Source File</th><th>Description</th></tr><tbody>'
for file in allFiles:
info = helper.getReadableFileInfo(file, caseName)
sourceID = str(SQLHelper.getFileID(info[4],caseName)) + '.' if str(SQLHelper.getFileID(info[4],caseName)) != 'None' else ''
table += '<tr><th>%s</th><th><div class="checkbox"><label><input type="checkbox" value="%s" name="additionalFiles" id="additionalFiles"><b>%s</b></label></div> </th><th>%s</th><th>%s</th><th>%s</th><th>%s</th><th>%s</th><th>%s</th></tr>' % (str(info[6]), file, file, info[1], info[2], info[3], info[0], sourceID + info[4], info[5])
table += '</tbody></table></div>'
info = helper.getReadableFileInfo(helper.getDBNameFromPath(filePath), caseName)
options += '</optgroup>'
print '<h2>File Details</h2>'
formStr = '<form action="main.py" class="form-horizontal" method="post">'
formStr += '<div class="form-group"><label class="col-md-2">Current filter on File:</label>'
formStr += '<p class="col-md-10 form-control-static">'+info[0]+'</p></div>'
formStr += '<div class="form-group"><label class="col-md-2">Size:</label>'
formStr += '<p class="col-md-10 form-control-static">'+info[1]+'</p></div>'
formStr += '<div class="form-group"><label class="col-md-2">First Packet:</label>'
formStr += '<p class="col-md-10 form-control-static">'+info[2]+'</p></div>'
formStr += '<div class="form-group"><label class="col-md-2">Last Packet:</label>'
formStr += '<p class="col-md-10 form-control-static">'+info[3]+'</p></div>'
formStr += '<div class="form-group"><label class="col-md-2">Source File:</label>'
formStr += '<p class="col-md-10 form-control-static">'+info[4]+'</p></div>'
formStr += '<div class="form-group"><label class="col-md-2">Description:</label>'
formStr += '<div class="col-md-4"><input type="text" class="form-control" name="description"value="%s" /></div>' % info[5]
formStr += '<input type="hidden" name="pagesToRender" value="showFile">'
formStr += '<input type="hidden" name="caseName" value="'+caseName+'"/>'
formStr += '<input type="hidden" name="filePath" value="'+filePath+'"/>'
formStr += '<input type="hidden" name="actions" value="editDescription"/>'
formStr += '<input type="submit" class="btn btn-default" value="Edit" name="Edit"/></div></form>'
formStr += '<h2>Graph settings</h2>'
formStr += '<form action="main.py" class="form-horizontal" method="post">'
formStr += '<div class="form-group"><label class="col-md-2">Time window:</label>'
formStr += '<div class="col-md-2"><label>From:</label><input type="text" title="Enter date and time in format: YYYY-MM-DD HH:MM:SS" class="form-control" value="%s"name="start"/></div>' % (info[2])
formStr += '<div class="col-md-2"><label>To:</label><input type="text" title="Enter date and time in format: YYYY-MM-DD HH:MM:SS" class="form-control" value="%s"name="end"/></div><div class="col-md-4">*This condition applies on ALL selected files.</div></div>' % (info[3])
formStr += '<div class="form-group"><label class="col-md-2">Files to compare:</label>'
formStr += '<div class="col-md-10"><a data-toggle="collapse" href="#files">Show/Hide</a></div></div>'
formStr += '<div class="form-group"><div class="col-md-12"> '+table+'</div></div>'
formStr += '<div class="form-group"><label class="col-md-2">Edit filter:</label>'
formStr += '<div class="col-md-4"><textarea class="form-control" name="filterContent"></textarea></div><div class="col-md-4">*This condition applies ONLY on primary selected file.</div></div>'
formStr += '<div class="form-group"><label class="col-md-2">Graph time sample rate(s):</label>'
formStr += '<div class="col-md-4"><input type="text" title="Enter amount of seconds is one time tick in graph. To low value can cause text label colisions. If empty, default value will be used." class="form-control" name="xtics"/></div></div>'
formStr += '<div class="form-group"><div class="col-md-6">'
formStr += '<input type="submit" class="btn btn-default pull-right" title="'+title+'"value="Render Graph" name="renderGraph" id="renderGraph" onclick="var t = getSumTime(\''+filesApproxStr+'\','+str(approximateTime)+');startProgresBar(t);">'
formStr += '<input type="submit" class="btn btn-default pull-right" title="'+title+'"name="renderDetailedGraph" value="Render Detailed Graph" name="renderDetailedGraph" id="renderDetailedGraph" onclick="var t = getSumTime(\''+filesApproxStr+'\','+str(approximateTime)+');startProgresBar(t);"></div></div>'
formStr += '<input type="hidden" name="actions" value="applyFilter">'
formStr += '<input type="hidden" name="pagesToRender" value="showFile:showGraph">'
formStr += '<input type="hidden" name="caseName" value="'+caseName+'">'
formStr += '<input type="hidden" name="filePath" value="'+filePath+'">'
formStr += '</form>'
print formStr
print '<hr/>'
print htmlGen.generateProgresBar()
from SQLHelper import *
# import module in different directory
import os, sys
sys.path.append(os.path.abspath('..'))
import csv_helper as csvh
import helper_format as hf
if __name__ == "__main__":
# Create engine object and Session class (as properties of SQLHelper)
sqlHelper = SQLHelper()
sqlHelper.create_engine()
sqlHelper.create_Session_class()
