def getRegionsPorts(svr_type, rgn):
CONN = make_a_connection('B2BACE.accdb')[0]
if svr_type == 'WAS':
svr_ports = "SELECT DISTINCT SERVER_PORT FROM WAS_SERVER_INFO WHERE SERVER_NAME='%s';" % rgn
all_ports = getRecords(CONN, svr_ports).fetchall()
print all_ports
all_ports = [list(x) for x in all_ports]
msg_disc = disconnect_database(CONN)
print msg_disc
return json.dumps(all_ports)
elif svr_type == 'JBOSS':
svr_ports = "SELECT DISTINCT SERVER_PORT FROM JBOSS_SERVER_INFO WHERE SERVER_NAME='%s';" % rgn
all_ports = getRecords(CONN, svr_ports).fetchall()
print all_ports
all_ports = [list(x) for x in all_ports]
msg_disc = disconnect_database(CONN)
print msg_disc
return json.dumps(all_ports)
def getRegions(svr):
print svr
CONN = make_a_connection('B2BACE.accdb')[0]
if svr == 'WAS':
svr_rgns = 'SELECT DISTINCT SERVER_NAME FROM WAS_SERVER_INFO;'
all_rgns = getRecords(CONN, svr_rgns).fetchall()
print all_rgns
all_rgns = [list(x) for x in all_rgns]
msg_disc = disconnect_database(CONN)
print msg_disc
return json.dumps(all_rgns)
elif svr == 'JBOSS':
svr_rgns = 'SELECT DISTINCT SERVER_NAME FROM JBOSS_SERVER_INFO;'
all_rgns = getRecords(CONN, svr_rgns).fetchall()
print all_rgns
all_rgns = [list(x) for x in all_rgns]
msg_disc = disconnect_database(CONN)
print msg_disc
return json.dumps(all_rgns)
def get_servers(server_type, server_region, server_port):
""" returns all the WAS/JBOSS servers with port specific number """
# Connect to the Database
CONN = make_a_connection('B2BACE.accdb')[0]
if server_type == 'WAS':
txn_query_1 = "SELECT SERVER_ENDPOINT, SERVER_ID FROM WAS_SERVER_INFO WHERE SERVER_NAME=\'%s\' AND SERVER_PORT=\'%s\';" % (
server_region, server_port)
print txn_query_1
temp_servers = getRecords(CONN, txn_query_1).fetchall()
req_servers = [list(row) for row in temp_servers]
msg_disc = disconnect_database(CONN)
print msg_disc
return json.dumps({'allservers': req_servers})
elif server_type == 'JBOSS':
txn_query_1 = "SELECT SERVER_ENDPOINT, SERVER_ID FROM JBOSS_SERVER_INFO WHERE SERVER_NAME=\'%s\' AND SERVER_PORT=\'%s\';" % (
server_region, server_port)
print txn_query_1
temp_servers = getRecords(CONN, txn_query_1).fetchall()
req_servers = [list(row) for row in temp_servers]
msg_disc = disconnect_database(CONN)
print msg_disc
return json.dumps({'allservers': req_servers})
def getStudentInfo(rollnumber):
if request.method == 'GET':
conn = make_a_connection()
# Student Info Table
userRecordsQuery = 'SELECT * FROM STUDENT_INFO WHERE ROLL_NUMBER=%d' % int(
rollnumber)
print userRecordsQuery
keys = [
'rollnum', 'fname', 'mname', 'lname', 'dob', 'doa', 'studpic',
'ppic', 'studclass', 'studsec', 'studsts', 'studgender'
]
records = getRecords(conn[0], userRecordsQuery).fetchall()
print records
studentDict = dict(zip(keys, records[0]))
print studentDict
# Student Parent Table
parRecordsQuery = 'SELECT * FROM STUDENT_PARENT WHERE ROLL_NUMBER=%d' % int(
rollnumber)
print parRecordsQuery
pkeys = [
'rollnum', 'fathername', 'mothername', 'fathoccp', 'mothoccup'
]
precords = getRecords(conn[0], parRecordsQuery).fetchall()
print precords
pdict = dict(zip(pkeys, precords[0]))
print pdict
#student contact Table
cntRecordsQuery = 'SELECT * FROM STUDENT_CONTACT WHERE ROLL_NUMBER=%d' % int(
rollnumber)
print cntRecordsQuery
ckeys = ['rollnum', 'prim', 'sec', 'email']
crecords = getRecords(conn[0], cntRecordsQuery).fetchall()
print crecords
cdict = dict(zip(ckeys, crecords[0]))
print cdict
# Student Address table
addrRecordsQuery = 'SELECT * FROM STUDENT_ADDRESS WHERE ROLL_NUMBER=%d' % int(
rollnumber)
print addrRecordsQuery
akeys = ['rollnum', 'studaddr']
arecords = getRecords(conn[0], addrRecordsQuery).fetchall()
print arecords
adict = dict(zip(akeys, arecords[0]))
print adict
discDB = disconnect_database(conn[0])
print discDB[0]
return render_template('studentInfo.html',
studentDict=studentDict,
parentDict=pdict,
contDict=cdict,
addDict=adict)
def real():
# Connect to the Database
CONN = make_a_connection('B2BACE.accdb')[0]
try:
txn_query = 'SELECT TRANS_NAME FROM TRANS_REAL;'
all_real_trans = getRecords(CONN, txn_query).fetchall()
except Exception, e:
print(e)
def getregions(txnName):
# Connect to the Database
CONN = make_a_connection('B2BACE.accdb')[0]
try:
txn_query = 'SELECT REGION, PAYLD_ENDPT FROM TRANS_REGION;'
temp = getRecords(CONN, txn_query).fetchall()
txn_region = json.dumps([list(row) for row in temp])
except Exception, e:
print(e)
def sendNotice():
conn = make_a_connection()
print conn
# Get classes and section
getClassesQuery = 'SELECT CLASSES FROM CLASS_INFO;'
allClasses = getRecords(conn[0], getClassesQuery).fetchall()
allClasses = [x for x in allClasses if x[0] is not None]
discDB = disconnect_database(conn[0])
print discDB
return render_template('sendNotice.html', classes=allClasses)
def all_servers():
""" renders the all servers html page """
# Connect to the Database
CONN = make_a_connection('B2BACE.accdb')[0]
try:
txn_query = 'SELECT TRANS_NAME FROM TRANS_REAL;'
svr_query = 'SELECT SERVER_TYPE FROM GENERIC_REQUEST;'
all_real_trans = getRecords(CONN, txn_query).fetchall()
all_svrs = getRecords(CONN, svr_query).fetchall()
except Exception, e:
print(e)
def realBaselineTest():
# Connect to the Database
CONN = make_a_connection('B2BACE.accdb')[0]
try:
txn_query1 = 'SELECT REGION FROM TRANS_REGION;'
txn_query2 = 'SELECT TRANS_NAME FROM TRANS_REAL;'
temp = getRecords(CONN, txn_query1).fetchall()
all_real_trans = getRecords(CONN, txn_query2).fetchall()
txn_region = [list(row) for row in temp]
all_txns = [list(row) for row in all_real_trans]
except Exception, e:
print(e)
def getStudentList():
conn = make_a_connection()
print conn
if request.method == 'POST':
allClases = request.json['classes']
studentsDict = dict()
for classes in allClases:
print classes
try:
getInfoQuery = """SELECT si.ROLL_NUMBER, si.FIRST_NAME, si.MIDDLE_NAME, si.LAST_NAME, si.STUDENT_CLASS, SI.GENDER, sc.PRIMARY_MOB FROM \
STUDENT_INFO AS si INNER JOIN STUDENT_CONTACT AS sc ON (si.ROLL_NUMBER = sc.ROLL_NUMBER AND si.STUDENT_STATUS = 'ACTIVE' AND si.STUDENT_CLASS = \'%s\');""" % classes
print getInfoQuery
getInfoRecords = getRecords(conn[0], getInfoQuery).fetchall()
print type(getInfoRecords[0])
getInfoRecordsArr = [list(x) for x in getInfoRecords]
print type(getInfoRecordsArr[0])
studentsDict[classes] = getInfoRecordsArr
except Exception, e:
print e
discDB = disconnect_database(conn[0])
print discDB
print studentsDict
return json.dumps(studentsDict)
def getFacultyList():
conn = make_a_connection()
print conn
if request.method == 'POST':
groups = request.json['group']
groupDict = dict()
for group in groups:
print group
try:
getInfoQuery = """ SELECT STAFF_ID, STAFF_NAME, STAFF_GENDER, STAFF_PRIMARY_CONT FROM STAFF_INFO WHERE STAFF_STATUS = 'ACTIVE'; """
print getInfoQuery
getInfoRecords = getRecords(conn[0], getInfoQuery).fetchall()
print type(getInfoRecords[0])
getInfoRecordsArr = [list(x) for x in getInfoRecords]
print type(getInfoRecordsArr[0])
groupDict[group] = getInfoRecordsArr
except Exception, e:
print e
discDB = disconnect_database(conn[0])
print discDB
print groupDict
return json.dumps(groupDict)
def editStudentInfo(rnum):
conn = make_a_connection()
# Get classes and section
getClassesQuery = 'SELECT CLASSES FROM CLASS_INFO;'
getSectionsQuery = 'SELECT SECTIONS FROM CLASS_INFO;'
allClasses = getRecords(conn[0], getClassesQuery)
allSections = getRecords(conn[0], getSectionsQuery)
allClasses = [x for x in allClasses if x[0] is not None]
allSections = [x for x in allSections if x[0] is not None]
if request.method == 'GET':
# Student Info Table
userRecordsQuery = 'SELECT * FROM STUDENT_INFO WHERE ROLL_NUMBER=%d' % int(
rnum)
print userRecordsQuery
keys = [
'rollnum', 'fname', 'mname', 'lname', 'dob', 'doa', 'studpic',
'ppic', 'studclass', 'studsec', 'studsts', 'studgender'
]
records = getRecords(conn[0], userRecordsQuery).fetchall()
print records
studentDict = dict(zip(keys, records[0]))
print studentDict
# Student Parent Table
parRecordsQuery = 'SELECT * FROM STUDENT_PARENT WHERE ROLL_NUMBER=%d' % int(
rnum)
print parRecordsQuery
pkeys = [
'rollnum', 'fathername', 'mothername', 'fathoccp', 'mothoccup'
]
precords = getRecords(conn[0], parRecordsQuery).fetchall()
print precords
pdict = dict(zip(pkeys, precords[0]))
print pdict
#student contact Table
cntRecordsQuery = 'SELECT * FROM STUDENT_CONTACT WHERE ROLL_NUMBER=%d' % int(
rnum)
print cntRecordsQuery
ckeys = ['rollnum', 'prim', 'sec', 'email']
crecords = getRecords(conn[0], cntRecordsQuery).fetchall()
print crecords
cdict = dict(zip(ckeys, crecords[0]))
print cdict
# Student Address table
addrRecordsQuery = 'SELECT * FROM STUDENT_ADDRESS WHERE ROLL_NUMBER=%d' % int(
rnum)
print addrRecordsQuery
akeys = ['rollnum', 'studaddr']
arecords = getRecords(conn[0], addrRecordsQuery).fetchall()
print arecords
adict = dict(zip(akeys, arecords[0]))
print adict
discDB = disconnect_database(conn[0])
print discDB[0]
return render_template('editStudentInfo.html',
rnum=rnum,
allClasses=allClasses,
allSections=allSections,
studentDict=studentDict,
parentDict=pdict,
contDict=cdict,
addDict=adict,
gender=['Male', 'Female'])
elif request.method == 'POST':
print '1'
# roll number
rnum = rnum
# student info
fname = request.form['fname']
mname = request.form['mname']
lname = request.form['lname']
print '2'
sgender = request.form['sgender']
dob = request.form['dob']
print '3'
sclass = request.form['class-selected']
ssection = request.form['section-selected']
print '4'
status = request.form['status']
doa = request.form['doa']
print '5'
spic = request.files['student-pic'].filename
# print spic
print '6'
# spic file
spicFileObj = request.files['student-pic']
print spicFileObj.filename
spicDestPath = app.config['UPLOAD_FOLDER_STUDENT_PIC']
# ppic file
ppicFileObj = request.files['parent-pic']
ppicDestPath = app.config['UPLOAD_FOLDER_PARENT_PIC']
# parents info
fcname = request.form['fcname']
foccup = request.form['foccup']
print '7'
mcname = request.form['mcname']
moccup = request.form['moccup']
print '8'
mobPrim = request.form['mob-primary']
mobSec = request.form['mob-secondary']
print '9'
email = request.form['email']
addr = request.form['comp-addr']
print '10'
allUpds = []
try:
if spicFileObj.filename != "":
spicFilepath = uploadfile(rnum, spicFileObj, spicDestPath,
ALLOWED_PIC_EXTNS)
print spicFilepath[1]
updStudPicQuery = """ UPDATE STUDENT_INFO SET STUDENT_PICTURE = \'%s\' WHERE ROLL_NUMBER = %d""" \
% (
spicFilepath[1].split('\\')[1],
int(rnum)
)
updSPic = update_records(conn[0], updStudPicQuery)
if ppicFileObj.filename != "":
ppicFilepath = uploadfile(rnum, ppicFileObj, ppicDestPath,
ALLOWED_PIC_EXTNS)
print ppicFilepath[1]
updParPicQuery = """ UPDATE STUDENT_INFO SET PARENT_PICTURE = \'%s\' WHERE ROLL_NUMBER = %d""" \
% (
ppicFilepath[1].split('\\')[1],
int(rnum)
)
updPPic = update_records(conn[0], updParPicQuery)
# student records Insert
updQuery = \
"""UPDATE STUDENT_INFO SET FIRST_NAME = \'%s\', MIDDLE_NAME = \'%s\', LAST_NAME = \'%s\', DOB = \'%s\', DOA = \'%s\', STUDENT_CLASS = \'%s\', STUDENT_SECTION = \'%s\', STUDENT_STATUS = \'%s\', GENDER = \'%s\' WHERE ROLL_NUMBER = %d;""" \
% (
fname.strip(),
mname.strip(),
lname.strip(),
dob,
doa,
# spicFilepath[1].split('\\')[1],
# ppicFilepath[1].split('\\')[1],
sclass.strip(),
ssection.strip(),
status.strip(),
sgender.strip(),
int(rnum)
)
print updQuery
updRecds = update_records(conn[0], updQuery)
print updRecds
if updRecds == 0:
allUpds.append(1)
# Parents records Insert
pUpdQuery = \
""" UPDATE STUDENT_PARENT SET FATHER_NAME = \'%s\', MOTHER_NAME = \'%s\', FATHER_OCCUPATION = \'%s\', MOTHER_OCCUPATION = \'%s\' WHERE ROLL_NUMBER = %d; """ \
% (
fcname.strip(),
mcname.strip(),
foccup.strip(),
moccup.strip(),
int(rnum)
)
print pUpdQuery
pUpdRecds = update_records(conn[0], pUpdQuery)
print pUpdRecds
if pUpdRecds == 0:
allUpds.append(1)
# Address insert
addrUpdQuery = \
""" UPDATE STUDENT_ADDRESS SET STUDENT_ADDRESS = \'%s\' WHERE ROLL_NUMBER = %d; """ \
% (
addr.strip(),
int(rnum)
)
print addrUpdQuery
addrUpdRecds = update_records(conn[0], addrUpdQuery)
print addrUpdRecds
if addrUpdRecds == 0:
allUpds.append(1)
# Contact Info insert
cntUpdQuery = \
""" UPDATE STUDENT_CONTACT SET PRIMARY_MOB = \'%s\', SECONDARY_MOB = \'%s\', EMAIL = \'%s\' WHERE ROLL_NUMBER = %d; """ \
% (
mobPrim.strip(),
mobSec.strip(),
email.strip(),
int(rnum)
)
print cntUpdQuery
cntUpdRecds = update_records(conn[0], cntUpdQuery)
print cntUpdRecds
if cntUpdRecds == 0:
allUpds.append(1)
except Exception, e:
print e
# check if all insert were successfull, then commit
if sum(allUpds) == 4:
conn[0].commit()
print 'commit'
flash('Saved Successfully!!')
discDB = disconnect_database(conn[0])
print discDB[0]
else:
flash('Error in updating records. Please try again!!')
discDB = disconnect_database(conn[0])
print discDB[0]
return redirect(url_for('getStudentInfo', rollnumber=rnum))
def addStaff():
conn = make_a_connection()
lastIDNumQuery = 'SELECT TOP 1 STAFF_ID FROM STAFF_INFO ORDER BY STAFF_ID DESC;'
lastIDNum = getRecords(conn[0], lastIDNumQuery).fetchall()
newIDNum = int(lastIDNum[0][0]) + 1
if request.method == 'POST':
sname = request.form['staffname']
dob = request.form['dob']
doj = request.form['doj']
gender = request.form['gender']
pcont = request.form['mob-primary']
scont = request.form['mob-secondary']
email = request.form['email']
addr = request.form['addr']
sts = request.form['sts']
# stafpic file
stfPicFileObj = request.files['staff-pic']
stfPicDestPath = app.config['UPLOAD_FOLDER_STAFF_PIC']
stafpicFilepath = uploadfile(newIDNum, stfPicFileObj, stfPicDestPath,
ALLOWED_PIC_EXTNS)
print stafpicFilepath[1]
staffInsertQuery = """INSERT INTO STAFF_INFO (STAFF_ID, STAFF_NAME, STAFF_GENDER, STAFF_DOB, STAFF_DOJ, STAFF_PRIMARY_CONT, STAFF_SECONDARY_CONT, STAFF_EMAIL, STAFF_ADDR, STAFF_STATUS, STAFF_PIC) VALUES \
(\'%s\', \'%s\', \'%s\', \'%s\',\'%s\', \'%s\', \'%s\', \'%s\', \'%s\', \'%s\', \'%s\');""" \
% (
newIDNum,
sname.strip(),
gender.strip(),
dob.strip(),
doj.strip(),
pcont.strip(),
scont.strip(),
email.strip(),
addr.strip(),
sts.strip(),
stafpicFilepath[1].split('\\')[1]
)
print staffInsertQuery
try:
ins = insert_records(conn[0], staffInsertQuery)
print ins
conn[0].commit()
print 'commit'
disc = disconnect_database(conn[0])
print disc[0]
flash("Submitted Successfully!")
return redirect(url_for('addStaff'))
except Exception, e:
print e
discDB = disconnect_database(conn[0])
print discDB[0]
flash("Submitted Successfully!")
return redirect(url_for('addStaff'))
def newAdmission():
""" New admission """
conn = make_a_connection()
getClassesQuery = 'SELECT CLASSES FROM CLASS_INFO;'
getSectionsQuery = 'SELECT SECTIONS FROM CLASS_INFO;'
lastRnum = int(get_last_rollnumber(conn[0])) + 1
studentStatus = "Active"
allClasses = getRecords(conn[0], getClassesQuery)
allSections = getRecords(conn[0], getSectionsQuery)
allClasses = [x for x in allClasses if x[0] is not None]
allSections = [x for x in allSections if x[0] is not None]
if request.method == 'POST':
print '1'
# roll number
rnum = lastRnum
# student info
fname = request.form['fname']
mname = request.form['mname']
lname = request.form['lname']
print '2'
sgender = request.form['sgender']
dob = request.form['dob']
print '3'
sclass = request.form['class-selected']
ssection = request.form['section-selected']
print '4'
status = studentStatus
doa = request.form['doa']
print '5'
spic = request.files['student-pic'].filename
# print spic
print '6'
# spic file
spicFileObj = request.files['student-pic']
spicDestPath = app.config['UPLOAD_FOLDER_STUDENT_PIC']
# ppic file
ppicFileObj = request.files['parent-pic']
ppicDestPath = app.config['UPLOAD_FOLDER_PARENT_PIC']
# parents info
fcname = request.form['fcname']
foccup = request.form['foccup']
print '7'
mcname = request.form['mcname']
moccup = request.form['moccup']
print '8'
mobPrim = request.form['mob-primary']
mobSec = request.form['mob-secondary']
print '9'
email = request.form['email']
addr = request.form['comp-addr']
print addr
print '10'
allIns = []
try:
spicFilepath = uploadfile(rnum, spicFileObj, spicDestPath,
ALLOWED_PIC_EXTNS)
print spicFilepath[1]
ppicFilepath = uploadfile(rnum, ppicFileObj, ppicDestPath,
ALLOWED_PIC_EXTNS)
print ppicFilepath[1]
# student records Insert
insQuery = \
"""INSERT INTO STUDENT_INFO (ROLL_NUMBER, FIRST_NAME, MIDDLE_NAME, LAST_NAME, DOB, DOA, STUDENT_PICTURE, PARENT_PICTURE, STUDENT_CLASS, STUDENT_SECTION, STUDENT_STATUS, GENDER) VALUES \
(%d, \'%s\', \'%s\', \'%s\', \'%s\', \'%s\', \'%s\', \'%s\', \'%s\', \'%s\', \'%s\', \'%s\');""" \
% (
int(rnum),
fname.strip(),
mname.strip(),
lname.strip(),
dob,
doa,
spicFilepath[1].split('\\')[1],
ppicFilepath[1].split('\\')[1],
sclass.strip(),
ssection.strip(),
status.strip(),
sgender.strip(),
)
print insQuery
insRecds = insert_records(conn[0], insQuery)
print insRecds
if insRecds == 0:
allIns.append(1)
# Parents records Insert
pInsQuery = \
""" INSERT INTO STUDENT_PARENT (ROLL_NUMBER, FATHER_NAME, MOTHER_NAME, FATHER_OCCUPATION, MOTHER_OCCUPATION) VALUES \
(%d, \'%s\', \'%s\', \'%s\', \'%s\'); """ \
% (
int(rnum),
fcname.strip(),
mcname.strip(),
foccup.strip(),
moccup.strip()
)
print pInsQuery
pinsRecds = insert_records(conn[0], pInsQuery)
print pinsRecds
if pinsRecds == 0:
allIns.append(1)
# Address insert
addrInsQuery = \
""" INSERT INTO STUDENT_ADDRESS (ROLL_NUMBER, STUDENT_ADDRESS) VALUES \
(%d, \'%s\'); """ \
% (
int(rnum),
addr.strip()
)
print addrInsQuery
addrinsRecds = insert_records(conn[0], addrInsQuery)
print addrinsRecds
if addrinsRecds == 0:
allIns.append(1)
# Contact Info insert
cntInsQuery = \
""" INSERT INTO STUDENT_CONTACT (ROLL_NUMBER, PRIMARY_MOB, SECONDARY_MOB, EMAIL) VALUES \
(%d, \'%s\', \'%s\', \'%s\'); """ \
% (
int(rnum),
mobPrim.strip(),
mobSec.strip(),
email.strip()
)
print cntInsQuery
cntinsRecds = insert_records(conn[0], cntInsQuery)
print cntinsRecds
if cntinsRecds == 0:
allIns.append(1)
except Exception, e:
print e
# check if all insert were successfull, then commit
if sum(allIns) == 4:
conn[0].commit()
print 'commit'
flash('Saved Successfully!!')
discDB = disconnect_database(conn[0])
print discDB[0]
else:
flash('Error in saving records. Please try again!!')
discDB = disconnect_database(conn[0])
print discDB[0]
return redirect(
url_for('newAdmission',
classes=allClasses,
sections=allSections,
gender=['Male', 'Female'],
rnum=lastRnum,
sts=studentStatus))
def studentSearch():
conn = make_a_connection()
getClassesQuery = 'SELECT CLASSES FROM CLASS_INFO;'
getSectionsQuery = 'SELECT SECTIONS FROM CLASS_INFO;'
allClasses = getRecords(conn[0], getClassesQuery)
allSections = getRecords(conn[0], getSectionsQuery)
allClasses = [x for x in allClasses if x[0] is not None]
allSections = [x for x in allSections if x[0] is not None]
if request.method == 'POST':
if request.form['rnum'] != "":
rnum = request.form['rnum']
# query by roll number
rnumQuery = 'SELECT ROLL_NUMBER, FIRST_NAME, MIDDLE_NAME, LAST_NAME, DOB, STUDENT_CLASS, STUDENT_SECTION FROM STUDENT_INFO WHERE ROLL_NUMBER=%d;' % int(
rnum)
print rnumQuery
records = getRecords(conn[0], rnumQuery)
print records
return render_template('studentSearch.html',
classes=allClasses,
sections=allSections,
records=records)
elif (request.form['fname'] != "" or request.form['mname'] != ""
or request.form['lname'] != ""):
fname = request.form['fname']
mname = request.form['mname']
lname = request.form['lname']
if (fname != "" and mname != "" and lname != ""):
nameQuery = """ SELECT ROLL_NUMBER, FIRST_NAME, MIDDLE_NAME, LAST_NAME, DOB, STUDENT_CLASS, STUDENT_SECTION
FROM STUDENT_INFO
WHERE FIRST_NAME=\'%s\'
AND MIDDLE_NAME=\'%s\'
AND LAST_NAME=\'%s\'; """ % (fname, mname, lname)
print nameQuery
elif (fname != "" and mname != "" and lname == ""):
nameQuery = """ SELECT ROLL_NUMBER, FIRST_NAME, MIDDLE_NAME, LAST_NAME, DOB, STUDENT_CLASS, STUDENT_SECTION
FROM STUDENT_INFO
WHERE FIRST_NAME=\'%s\'
AND MIDDLE_NAME=\'%s\'; """ % (fname, mname)
print nameQuery
elif (mname != "" and lname != "" and fname == ""):
nameQuery = """ SELECT ROLL_NUMBER, FIRST_NAME, MIDDLE_NAME, LAST_NAME, DOB, STUDENT_CLASS, STUDENT_SECTION
FROM STUDENT_INFO
WHERE MIDDLE_NAME=\'%s\'
AND LAST_NAME=\'%s\'; """ % (mname, lname)
print nameQuery
elif (fname != "" and lname != "" and mname == ""):
nameQuery = """ SELECT ROLL_NUMBER, FIRST_NAME, MIDDLE_NAME, LAST_NAME, DOB, STUDENT_CLASS, STUDENT_SECTION
FROM STUDENT_INFO
WHERE FIRST_NAME=\'%s\'
AND LAST_NAME=\'%s\'; """ % (fname, lname)
print nameQuery
elif (fname == "" and mname == "" and lname != ""):
nameQuery = """ SELECT ROLL_NUMBER, FIRST_NAME, MIDDLE_NAME, LAST_NAME, DOB, STUDENT_CLASS, STUDENT_SECTION
FROM STUDENT_INFO
WHERE LAST_NAME=\'%s\'; """ % (lname, )
print nameQuery
elif (mname == "" and lname == "" and fname != ""):
nameQuery = """ SELECT ROLL_NUMBER, FIRST_NAME, MIDDLE_NAME, LAST_NAME, DOB, STUDENT_CLASS, STUDENT_SECTION
FROM STUDENT_INFO
WHERE FIRST_NAME=\'%s\'; """ % (fname, )
print nameQuery
elif (lname == "" and fname == "" and mname != ""):
nameQuery = """ SELECT ROLL_NUMBER, FIRST_NAME, MIDDLE_NAME, LAST_NAME, DOB, STUDENT_CLASS, STUDENT_SECTION
FROM STUDENT_INFO
WHERE MIDDLE_NAME=\'%s\'; """ % (mname, )
print nameQuery
records = getRecords(conn[0], nameQuery).fetchall()
discDB = disconnect_database(conn[0])
print discDB[0]
return render_template('studentSearch.html',
classes=allClasses,
sections=allSections,
records=records)
else:
selClass = request.form['class']
selSec = request.form['section']
classQuery = """ SELECT ROLL_NUMBER, FIRST_NAME, MIDDLE_NAME, LAST_NAME, DOB, STUDENT_CLASS, STUDENT_SECTION
FROM STUDENT_INFO
WHERE STUDENT_CLASS = \'%s\'
AND STUDENT_SECTION = \'%s\'; """ % (selClass, selSec)
records = getRecords(conn[0], classQuery).fetchall()
discDB = disconnect_database(conn[0])
print discDB[0]
return render_template('studentSearch.html',
classes=allClasses,
sections=allSections,
records=records)
return render_template('studentSearch.html',
classes=allClasses,
sections=allSections)
def processX12(region, txnName):
""" Process the X12 received by add to the Generic Payload request
and return the processed X12 response, Tracking Id and
description """
print region
print txnName
# Connect to the Database
CONN = make_a_connection('B2BACE.accdb')[0]
try:
# Prepare the select query
# txn_query_1 = "SELECT GENRIC_REQ FROM TRANS_REAL WHERE TRANS_NAME='%s';" % txnName
query_regn_servr_type = "SELECT PAYLD_ENDPT, SERVER_TYPE FROM TRANS_REGION WHERE REGION='%s';" % region
# Fetch records from the DB by running the query
# db_records_1 = getRecords(CONN, txn_query_1).fetchall()
db_regn_servr_type = getRecords(CONN, query_regn_servr_type).fetchall()
# server type
server_endpnt = db_regn_servr_type[0][0]
server_type_name = db_regn_servr_type[0][1]
print server_endpnt
print server_type_name
# get XML requests
query_get_XML = "SELECT XML_REQUEST FROM GENERIC_REQUEST WHERE SERVER_TYPE = '%s';" % server_type_name
dummy_XML_request = getRecords(CONN, query_get_XML).fetchall()
# Update the Payload request with the request X12
request_XML = str(dummy_XML_request[0][0]).replace(
'#Req_X12#',
request.json['reqX12']).replace('#Req_Type#', txnName.strip())
print request_XML
# Prepare the header for the webservice request
headers = {
'Content-Type': 'text/xml',
'charset': 'UTF-8',
'SOAPAction': ""
}
# Update the Payload request with the request X12
# request_XML = str(db_records_1[0][0]).replace('#Req_X12#', request.json['reqX12'])
# make a POST request
r = requests.post(server_endpnt, request_XML, headers=headers)
print r
print 'parsing at minidom'
# Parse the XML response
xmldoc = minidom.parseString(r.text)
# Prepare the response
response = []
response.append(
xmldoc.getElementsByTagName('payload')[0].childNodes[0].data)
response.append(
xmldoc.getElementsByTagName('trackingId')[0].childNodes[0].data)
response.append(
xmldoc.getElementsByTagName('returnCodeDescription')
[0].childNodes[0].data)
print response
except Exception, e:
print(e)
def run_all_servers(server_type, txnName):
# Connect to the Database
CONN = make_a_connection('B2BACE.accdb')[0]
if request.method == 'POST':
# try:
if server_type == 'WAS':
print request.json['ser_id']
# Prepare the select query
query_server_id = "SELECT SERVER_ENDPOINT FROM WAS_SERVER_INFO WHERE SERVER_ID='%s';" % request.json[
'ser_id']
# txn_query_2 = "SELECT GENRIC_REQ FROM TRANS_REAL WHERE TRANS_NAME='%s';" % txnName
# get XML requests
query_get_XML = "SELECT XML_REQUEST FROM GENERIC_REQUEST WHERE SERVER_TYPE = '%s';" % 'WAS'
dummy_XML_request = getRecords(CONN, query_get_XML).fetchall()
# Fetch records from the DB by running the query
db_server_edpt = getRecords(CONN, query_server_id).fetchall()
# db_records_2 = getRecords(CONN, txn_query_2).fetchall()
elif server_type == 'JBOSS':
print request.json['ser_id']
# Prepare the select query
query_server_id = "SELECT SERVER_ENDPOINT FROM JBOSS_SERVER_INFO WHERE SERVER_ID='%s';" % request.json[
'ser_id']
# get XML requests
query_get_XML = "SELECT XML_REQUEST FROM GENERIC_REQUEST WHERE SERVER_TYPE = '%s';" % 'JBOSS'
dummy_XML_request = getRecords(CONN, query_get_XML).fetchall()
# Fetch records from the DB by running the query
db_server_edpt = getRecords(CONN, query_server_id).fetchall()
# Update the Payload request with the request X12
request_XML = str(dummy_XML_request[0][0]).replace(
'#Req_X12#',
request.json['reqX12']).replace('#Req_Type#', txnName.strip())
# Prepare the header for the webservice request
headers = {
'Content-Type': 'text/xml',
'charset': 'UTF-8',
'SOAPAction': ""
}
# make a POST request
s = time()
r = requests.post(db_server_edpt[0][0], request_XML, headers=headers)
e = int(round((time() - s) * 1000))
print e
sleep(2)
# Parse the XML response
xmldoc = minidom.parseString(r.text)
# Prepare the response
response = []
response.append(
xmldoc.getElementsByTagName('payload')[0].childNodes[0].data)
response.append(
xmldoc.getElementsByTagName('trackingId')[0].childNodes[0].data)
response.append(
str(
xmldoc.getElementsByTagName('returnCode')
[0].childNodes[0].data))
response.append(
xmldoc.getElementsByTagName('returnCodeDescription')
[0].childNodes[0].data)
response.append(e)
print response
# except Exception, e:
# print(e)
msg_disc = disconnect_database(CONN)
print msg_disc
return json.dumps(response)
def regressionreal():
# Connect to the Database
CONN = make_a_connection('B2BACE.accdb')[0]
txn_query1 = 'SELECT REGION FROM TRANS_REGION;'
temp_regions = getRecords(CONN, txn_query1).fetchall()
txn_region = [list(row) for row in temp_regions]
if request.method == 'POST':
# Extract all the json reuest params
xvald = request.json['xvald']
lvald = request.json['lvald']
transType = request.json['transType']
regX12 = request.json['regX12']
region = request.json['region']
xyn = request.json['xyn']
lyn = request.json['lyn']
# prepare the select query
query_regn_servr_type = "SELECT PAYLD_ENDPT, SERVER_TYPE FROM TRANS_REGION WHERE REGION='%s';" % region
# run queries
db_regn_servr_type = getRecords(CONN, query_regn_servr_type).fetchall()
# server type
server_endpnt = db_regn_servr_type[0][0]
server_type_name = db_regn_servr_type[0][1]
print server_endpnt
print server_type_name
# get XML requests
query_get_XML = "SELECT XML_REQUEST FROM GENERIC_REQUEST WHERE SERVER_TYPE = '%s';" % server_type_name
dummy_XML_request = getRecords(CONN, query_get_XML).fetchall()
# Update the Payload request with the request X12
request_XML = str(dummy_XML_request[0][0]).replace(
'#Req_X12#', regX12).replace('#Req_Type#', transType.strip())
print request_XML
# Prepare the header for the webservice request
headers = {
'Content-Type': 'text/xml',
'charset': 'UTF-8',
'SOAPAction': ""
}
msg_disc = disconnect_database(CONN)
print msg_disc
# make a POST request
r = requests.post(server_endpnt, request_XML, headers=headers)
xmldoc = minidom.parseString(r.text)
x12resp = xmldoc.getElementsByTagName('payload')[0].childNodes[0].data
tckngId = xmldoc.getElementsByTagName(
'trackingId')[0].childNodes[0].data
respCodeDesc = xmldoc.getElementsByTagName(
'returnCodeDescription')[0].childNodes[0].data
print respCodeDesc
# expected XML response status
# expresp, loopvalds = xvald.split('\n')
expresp, allLoopvalds = xvald.split('\n', 1)
# expected output
# loop, seg, segval = loopvalds.split('|')
xmlFinalResultArr = []
# check if x12 validation is required
if xyn.strip().upper() == 'YES':
# proceed if user checks for success
if expresp.strip().upper() == 'SUCCESS':
# proceed if response is success
if respCodeDesc.strip().upper() == 'SUCCESS':
# convert X12 to XML
xmlresponse = x12ToXML(x12resp)
print xmlresponse
# to capture responses for each segment validation
xmlResponseSegmentsArr = []
allLoopvaldsArr = allLoopvalds.split("\n")
for x in allLoopvaldsArr:
print x
temploop, tempseg, tempval = x.split("|")
# get segment values
tempXmlResponseSegments = getSegmentValues(
xmlresponse.strip(), temploop.strip(),
tempseg.strip())
print tempXmlResponseSegments
xmlResponseSegmentsArr.append(tempXmlResponseSegments)
# checking if the success key exists in the dictionary
if 'success' in tempXmlResponseSegments:
# compare and get the result
tempRespResult = compareResponse(
tempXmlResponseSegments['success'],
tempval.strip(), temploop.strip())
if tempRespResult['result'] == 'Pass':
print tempRespResult['msg']
xmlFinalResultArr.append(
{'Pass': ['Pass', tempRespResult['msg']]})
# return json.dumps()
else:
print tempRespResult['msg']
xmlFinalResultArr.append(
{'Fail': ['Fail', tempRespResult['msg']]})
# return json.dumps
elif 'error' in tempXmlResponseSegments:
print tempXmlResponseSegments['error']
xmlFinalResultArr.append({
'Fail':
['Fail', tempXmlResponseSegments['error']]
})
# return json.dumps
else:
xmlFinalResultArr.append({'Fail': ['Fail', respCodeDesc]})
# return json.dumps(xmlFinalResultArr)
# To do error scenarios
elif expresp.upper() == 'ERROR':
if respCodeDesc.upper() == 'ERROR':
pass
else:
xmlFinalResultArr.append(
{'Pass': ['Pass', 'No X12 validation required.']})
print xmlFinalResultArr
# return response
for x in xmlFinalResultArr:
if 'Fail' in x:
print "inside"
return json.dumps({
'result': 'Fail',
'msg': xmlFinalResultArr,
'responseX12': x12resp,
'tckngId': tckngId,
'respCodeDesc': respCodeDesc
})
return json.dumps({
'result': 'Pass',
'msg': xmlFinalResultArr,
'responseX12': x12resp,
'tckngId': tckngId,
'respCodeDesc': respCodeDesc
})
msg_disc = disconnect_database(CONN)
print msg_disc
return render_template("regressionreal.html", regions=txn_region)
def sendLogs(region, txnName, txnID, logsFlag):
""" returns logs file array object , logs flag Y means a log file will
be created, else only logs string will be created"""
# Connect to the Database
CONN = make_a_connection('B2BACE.accdb')[0]
getUrl_query = "SELECT LOG_ENDPT FROM TRANS_REGION WHERE REGION = '%s';" % region
getLogFilename_query = "SELECT LOG_FILE FROM TRANS_REAL WHERE TRANS_NAME = '%s';" % txnName
logUrl = str(getRecords(CONN, getUrl_query).fetchall()[0][0])[1:-1]
logFilename = getRecords(CONN, getLogFilename_query).fetchall()[0][0]
msg_disc = disconnect_database(CONN)
print msg_disc
print logUrl
print logFilename
all_filenames = str(logFilename).split(',')
for filex in all_filenames:
tempUrl = '%s?file=%s' % (logUrl, filex.strip())
print tempUrl
logsObj = getLogs(tempUrl, txnID)
print "received logsObj"
if logsObj != 1:
if logsFlag == 'N':
logsJSON = {'Logs': '%s' % logsObj, 'filepath': 'NA'}
return json.dumps([{
'item': k,
'message': v
} for k, v in logsJSON.items()],
indent=4)
elif logsFlag == 'Y':
print "in logs Flag Y"
cur_dt_time = getDateTime()
date_today = cur_dt_time[0]
time_now = cur_dt_time[1]
writeFile = writeToFile(foldername='logs',
ObjLog=logsObj,
txnName=txnName,
dt=date_today,
tm=time_now)
print 'write done'
if writeFile != 1:
logsJSON = {
'Logs': '%s' % logsObj,
'filepath': '%s' % writeFile
}
# return logsJSON
return json.dumps([{
'item': k,
'message': v
} for k, v in logsJSON.items()],
indent=4)
else:
print "logs object returned 1 from getLogs"
errorJSON = {
'Logs':
'Error - Txn Id not found. Please check Region, TxnName and ID',
'filepath': 'NA'
}
return json.dumps([{
'item': k,
'message': v
} for k, v in errorJSON.items()],
indent=4)