def getAttendance(request, username):
if request.method == "POST":
cursor = connection.cursor()
cursor.execute("SELECT * FROM main_student WHERE username = '******';")
temp = cursor.fetchall()
attendanceData = []
for t in temp:
branch = t[4]
sem = t[9]
tblname = branch + "_" + sem + "_" + request.POST['subject']
cursor.execute(
"SELECT tableName FROM attendance_start_stop WHERE tableName = '" +
tblname + "';")
if cursor.fetchall():
cursor.execute("SELECT present,date,time FROM " + tblname +
" WHERE username = '******';")
for temp in cursor.fetchall():
t = {"present": temp[0], "date": temp[1], "time": temp[2]}
attendanceData.append(t)
return render(
request, "attendance/view_attendance.html", {
'attendanceData':
attendanceData,
'message':
"Your attendance in " + request.POST['subject'] +
" is as follows:"
})
else:
error = []
error.append("No details found for " + request.POST['subject'])
return render(request, "attendance/view_attendance.html",
{'error': error})
else:
return render(request, "attendance/view_attendance.html")
def refreshStudentAttendanceTable(request, username):
cursor = connection.cursor()
cursor.execute("SELECT * FROM main_student WHERE username = '******';")
temp = cursor.fetchall()
for t in temp:
branch = t[4]
sem = t[9]
cursor.execute(
"SELECT tableName FROM attendance_start_stop WHERE semester = '" +
sem + "' AND branch = '" + branch + "' AND status = '1';")
tableNames = cursor.fetchall()
on_going_attendance = []
for table in tableNames:
temp = table
s = ""
for t in range(len(temp)):
if table[t] == '(' or table[t] == ')' or table[t] == ',' or table[
t] == "'":
pass
else:
s += table[t]
table = s
on_going_attendance.append(table)
if tableNames:
return render(request, 'attendance/make_attendance.html', {
'on_going_attendance': on_going_attendance,
})
else:
error = []
error.append("OOPS! seems no attandance has been started")
return render(request, 'attendance/make_attendance.html', {
'error': error,
})
def refreshAttendanceTable(request, teacher):
teacher = teacher
cursor = connection.cursor()
cursor.execute(
"CREATE TABLE IF NOT EXISTS attendance_start_stop (branch VARCHAR(50), semester VARCHAR(50), subject VARCHAR(50), status INT, tableName VARCHAR(50), faculty VARCHAR(50), FOREIGN KEY (faculty) REFERENCES main_teacher(username) ON UPDATE CASCADE ON DELETE CASCADE);"
)
cursor.execute(
"SELECT tableName FROM attendance_start_stop WHERE faculty = '" +
teacher + "' AND status=1 ;")
tableNames = cursor.fetchall()
on_going_attendance = []
for table in tableNames:
temp = table
s = ""
for t in range(len(temp)):
if table[t] == '(' or table[t] == ')' or table[t] == ',' or table[
t] == "'":
pass
else:
s += table[t]
table = s
on_going_attendance.append(table)
if tableNames:
return render(request, 'attendance/start_stop.html', {
'on_going_attendance': on_going_attendance,
})
else:
error = []
error.append("You haven't started any attendance yet!!")
return render(request, 'attendance/start_stop.html', {
'error': error,
})
def clickedPresent(request, table, student):
cursor = connection.cursor()
cursor.execute("SELECT * FROM " + table + " WHERE username = '******' AND date = '" + str(date.today()) + "' AND present = 1;")
if cursor.fetchall():
error = []
error.append("You are alerady present")
return render(request, 'attendance/make_attendance.html',
{'error': error})
else:
today = date.today()
d = today.strftime("%Y-%m-%d")
cursor.execute("UPDATE " + table + " SET present = 1, time = '" +
str(datetime.now()) + "' WHERE username = '******' AND date = '" + d + "';")
return render(
request, 'attendance/make_attendance.html', {
'message':
"Your Attendance have been successfull recognized for " + table
})
def save_student_profile(request):
if request.method == "POST":
get_phone_no = request.POST.get("phone_no", "")
if get_phone_no:
phone_no = get_phone_no
else:
phone_no = request.POST["hidden_phone_no"]
get_parent_phone_no = request.POST.get("parent_phone_no", "")
if get_parent_phone_no:
parent_phone_no = get_parent_phone_no
else:
parent_phone_no = request.POST["hidden_parent_phone_no"]
get_student_id = request.POST.get("student_id", "")
if get_student_id:
student_id = get_student_id
else:
student_id = request.POST["hidden_student_id"]
get_roll_no = request.POST.get("roll_no", "")
if get_roll_no:
roll_no = get_roll_no
else:
roll_no = request.POST["hidden_roll_no"]
get_branch = request.POST.get("branch", "")
if get_branch:
branch = get_branch
else:
branch = request.POST["hidden_branch"]
get_email = request.POST.get("email", "")
if get_email:
email = get_email
else:
email = request.POST["hidden_email"]
get_uname = request.POST.get("uname", "")
if get_uname:
uname = get_uname
else:
uname = request.POST["hidden_uname"]
get_fullname = request.POST.get("fullname", "")
if get_fullname:
fullname = get_fullname
else:
fullname = request.POST["hidden_fullname"]
get_semester = request.POST.get("semester", "")
if get_semester:
semester = get_semester
else:
semester = request.POST["hidden_semester"]
error = []
cursor = connection.cursor()
cursor.execute("SELECT * FROM auth_user WHERE username <> '" +
request.user.username + "'")
for temp in cursor.fetchall():
if temp[4] == uname:
error.append("Username has already been taken")
if temp[7] == email:
error.append("Email has already been taken")
if phone_no == parent_phone_no:
error.append("Your's and Parent's phone number cannot be same")
cursor = connection.cursor()
cursor.execute("SELECT * FROM main_student WHERE username <> '" +
request.user.username + "'")
for temp in cursor.fetchall():
if temp[2] == phone_no:
error.append("Phone number must be unique")
break
if temp[3] == parent_phone_no:
error.append("Phone number must be unique")
break
if temp[5] == student_id:
error.append("Student id must be unique")
break
if temp[6] == roll_no and temp[4] == branch:
error.append("Roll NO. must be unique")
break
if error:
return render(request, "home.html", {'home_error': error})
else:
temp_username = request.user.username
cursor.execute("UPDATE auth_user SET username = '******', email = '" + email + "' WHERE username = '******'")
cursor.execute("UPDATE main_student SET username = '******', fullname = '" + fullname + "', phone_no = '" +
phone_no + "', parents_phone_no = '" +
parent_phone_no + "', branch = '" + branch +
"', student_id = '" + student_id +
"', roll_no = '" + roll_no + "',semester = 'sem" +
str(semester) + "' WHERE username = '******'")
return render(request, "home.html", {
'home_message':
"Your details have been successfully updated"
})
else:
return render(request, 'registration/student_my_profile.html')
def generateMuster(request):
if request.method == "POST":
response = HttpResponse(content_type='application/ms-excel')
response['content-Disposition'] = 'attachment; filename=Attendance'+\
str(datetime.now())+'.xls'
branch = request.POST['branch']
sem = request.POST['sem']
subject = request.POST['subject']
startDate = request.POST['start_date']
endDate = request.POST['end_date']
tblname = branch + "_" + sem + "_" + subject
if startDate < endDate:
cursor = connection.cursor()
# first getting all tablename and checking if there is attendance data or not
cursor.execute(
"SELECT tableName FROM attendance_start_stop WHERE faculty = '"
+ request.user.username + "'")
flag = 0
for temp in cursor.fetchall():
if temp[0].lower() == tblname.lower():
flag = 1
if flag == 1:
# adding heading to excel file
style = xlwt.XFStyle()
style.font.bold = True
style.alignment.wrap = 1 # Set wrap
workbook = xlwt.Workbook(encoding='utf-8')
worksheet = workbook.add_sheet('My Worksheet')
worksheet.write_merge(
0, 0, 0, 20, 'DHARMSINH DESAI UNIVERSITY, NADIAD',
xlwt.easyxf(
'align: horz center, vert center;pattern: pattern solid, fore_colour white;font: colour black, bold True, height 420;'
))
worksheet.write_merge(
1, 1, 0, 20, 'Attendance report for ' + branch + ' ' +
sem + ' ' + subject + ' ',
xlwt.easyxf(
'align: horz center, vert center;pattern: pattern solid, fore_colour white;font: colour black, bold True, height 320;'
))
worksheet.write_merge(
2, 2, 0, 20, 'From Date:' + startDate +
' To Date:' + endDate + '',
xlwt.easyxf(
'align: horz center, vert center;font: colour red, bold True, height 220;'
))
first_col = worksheet.col(1)
first_col.width = 420 * 20
worksheet.write(
3, 0, "Roll NO.",
xlwt.easyxf(
'align: horz center, vert center;pattern: pattern solid, fore_colour white;font: colour black, bold True;'
))
worksheet.write(
3, 1, "Sutudent name",
xlwt.easyxf(
'align: horz center, vert center;pattern: pattern solid, fore_colour white;font: colour black, bold True;'
))
# setting roll no and name of student in excel
row = 5
cursor.execute("SELECT name,roll_no FROM " + tblname +
" WHERE date BETWEEN '" + startDate +
"' AND '" + endDate +
"' GROUP BY roll_no ORDER BY roll_no")
for temp in cursor.fetchall():
worksheet.write(row, 0, temp[1])
worksheet.write(row, 1, temp[0])
row += 1
# setting date row
cursor.execute("SELECT date FROM " + tblname +
" WHERE date BETWEEN '" + startDate +
"' AND '" + endDate + "' GROUP BY date")
col = 2
month_col = 2
months = [
'January', 'February', 'March', 'April', 'May', 'June',
'July', 'August', 'September', 'October', 'November',
'December'
]
initial_month = -1
prev_month = -1
last_month = -1
count = 2
for temp in cursor.fetchall():
initial_month = int(str(temp[0])[5:7]) - 1
last_month = initial_month
if initial_month == prev_month:
pass
else:
if count > month_col:
worksheet.write_merge(
3, 3, month_col, count - 1, months[prev_month],
xlwt.easyxf(
'align: horz center, vert center;pattern: pattern solid, fore_colour white;font: colour black, bold True;'
))
month_col = count
prev_month = initial_month
temp_col = worksheet.col(col)
temp_col.width = 25 * 25
worksheet.write(4, col, str(temp[0])[8:10])
count += 1
col += 1
if last_month != -1:
worksheet.write_merge(
3, 3, month_col, count - 1, months[last_month],
xlwt.easyxf(
'align: horz center, vert center;pattern: pattern solid, fore_colour white;font: colour black, bold True;'
))
# getting data from database between start and end date
cursor.execute("SELECT * FROM " + tblname +
" WHERE date BETWEEN '" + startDate +
"' AND '" + endDate +
"' ORDER BY roll_no, date")
row = 4
col = 2
prev_roll_no = ''
for temp in cursor.fetchall():
initial_roll_no = str(temp[2])
if initial_roll_no == prev_roll_no:
col += 1
else:
prev_roll_no = initial_roll_no
row += 1
col = 2
if temp[3] == 1:
worksheet.write(row, col, "P",
xlwt.easyxf('font: colour green;'))
else:
worksheet.write(row, col, "A",
xlwt.easyxf('font: colour red;'))
workbook.save(response)
return response
else:
error = []
error.append("OPPS!! no data found")
return render(request, "attendance/generate_muster.html",
{'error': error})
else:
error = []
error.append("Start date cannot be greater then End date")
return render(request, "attendance/generate_muster.html",
{'error': error})
else:
return render(request, "attendance/generate_muster.html")
def registration_view(request):
if request.method == 'POST':
if request.POST['pass1'] == request.POST['pass2']:
phone_no = request.POST['phone_no']
parent_phone_no = request.POST['parent_phone_no']
student_id = request.POST['student_id']
roll_no = request.POST['roll_no']
branch = request.POST['branch']
email = request.POST['email']
error = []
cursor = connection.cursor()
cursor.execute("SELECT * FROM auth_user")
for temp in cursor.fetchall():
if temp[4] == request.POST['uname']:
error.append("Username has already been taken")
if temp[7] == email:
error.append("Email has already been taken")
if phone_no == parent_phone_no:
error.append("Your's and Parent's phone number cannot be same")
cursor = connection.cursor()
cursor.execute("SELECT * FROM main_student")
for temp in cursor.fetchall():
if temp[3] == phone_no:
error.append("Phone number must be unique")
break
if temp[4] == parent_phone_no:
error.append("Phone number must be unique")
break
if temp[6] == student_id:
error.append("Student id must be unique")
break
if temp[7] == roll_no and temp[5] == branch:
error.append("Roll NO. must be unique")
break
if error:
return render(request, 'registration/signup.html',
{'error': error})
else:
username = request.POST['uname']
email = request.POST['email']
user = User.objects.create_user(username=username,
password=request.POST['pass1'],
email=email)
fullname = request.POST['fullname']
phone_no = request.POST['phone_no']
parent_phone_no = request.POST['parent_phone_no']
branch = request.POST['branch']
student_id = request.POST['student_id']
roll_no = request.POST['roll_no']
sem = request.POST['sem']
newStudent = Student(username=username,
fullname=fullname,
phone_no=phone_no,
parents_phone_no=parent_phone_no,
branch=branch,
semester="sem" + sem,
student_id=student_id,
roll_no=roll_no,
user=user)
newStudent.save()
cursor.execute(
"CREATE TABLE IF NOT EXISTS attendancedate(tableName VARCHAR(30), date DATE);"
)
cursor.execute("SELECT * FROM attendancedate")
for temp in cursor.fetchall():
tblname = branch + "_" + "sem" + str(sem)
if temp[0][0:7].lower() == tblname.lower():
temp_con = connection.cursor()
sql = "INSERT INTO " + temp[
0] + " VALUES(%s,%s,%s,%s,%s,%s);"
val = (fullname, username, roll_no, 0, temp[1],
datetime.now())
temp_con.execute(sql, val)
login(request, user)
return redirect('home')
else:
return render(request, 'registration/signup.html',
{'error': "Password Don't Match"})
else:
return render(request, 'registration/signup.html')
def startAttendance(request):
if request.method == 'POST':
branch = request.POST['branch']
semester = request.POST['sem']
subject = request.POST['subject']
teacher = request.POST['teacher']
tblname = branch + "_" + semester + "_" + subject
error = []
if branch == '':
error.append("Branch must not be empty")
if semester == "Select the semester":
error.append("Please choose valid semester")
if subject == '':
error.append("Subject must not be empty")
if error:
return render(request, 'attendance/start_stop.html',
{'error': error})
else:
cursor = connection.cursor()
# inserting date data for future reference if any student signup later
cursor.execute(
"CREATE TABLE IF NOT EXISTS attendancedate(tableName VARCHAR(30), date DATE);"
)
cursor.execute("SELECT * FROM attendancedate WHERE tableName ='" +
tblname + "' AND date = '" + str(date.today()) +
"'")
if cursor.fetchall():
pass
else:
sql = "INSERT INTO attendancedate (tableName,date) VALUES(%s,%s);"
val = (tblname, date.today())
cursor.execute(sql, val)
on_going_attendance = []
cursor.execute(
"CREATE TABLE IF NOT EXISTS attendance_start_stop (branch VARCHAR(50), semester VARCHAR(50), subject VARCHAR(50), status INT, tableName VARCHAR(50), faculty VARCHAR(50), FOREIGN KEY (faculty) REFERENCES main_teacher(username));"
)
cursor.execute(
"SELECT * FROM attendance_start_stop WHERE tableName = '" +
tblname + "' AND faculty = '" + teacher + "';")
if cursor.fetchall():
cursor.execute(
"CREATE TABLE IF NOT EXISTS " + tblname +
" (name VARCHAR(50), username VARCHAR(30), roll_no VARCHAR(50), present INT, date DATE, time TIME, FOREIGN KEY (username) REFERENCES main_student(username) ON DELETE CASCADE ON UPDATE CASCADE);"
)
today = date.today()
d = today.strftime("%Y-%m-%d")
cursor.execute("SELECT * FROM " + tblname + " WHERE date = '" +
d + "';")
if cursor.fetchall():
pass
else:
cursor.execute(
"SELECT * FROM main_student WHERE branch = '" +
branch + "' AND semester = '" + semester + "';")
data = cursor.fetchall()
for d in data:
sql = "INSERT INTO " + tblname + " VALUES(%s,%s,%s,%s,%s,%s);"
val = (d[1], d[0], d[6], 0, date.today(),
datetime.now())
cursor.execute(sql, val)
sql = "UPDATE attendance_start_stop SET status = 1 WHERE tableName = '" + tblname + "';"
cursor.execute(sql)
cursor.execute(
"SELECT tableName FROM attendance_start_stop WHERE faculty = '"
+ teacher + "' AND status = 1 ;")
tableNames = cursor.fetchall()
for table in tableNames:
temp = table
s = ""
for t in range(len(temp)):
if table[t] == '(' or table[t] == ')' or table[
t] == ',' or table[t] == "'":
pass
else:
s += table[t]
table = s
on_going_attendance.append(table)
else:
cursor.execute(
"CREATE TABLE IF NOT EXISTS " + tblname +
" (name VARCHAR(50), username VARCHAR(30), roll_no VARCHAR(50), present INT, date DATE, time TIME, FOREIGN KEY (username) REFERENCES main_student(username) ON DELETE CASCADE ON UPDATE CASCADE);"
)
today = date.today()
d = today.strftime("%Y-%m-%d")
cursor.execute("SELECT * FROM " + tblname + " WHERE date = '" +
d + "';")
if cursor.fetchall():
pass
else:
cursor.execute(
"SELECT * FROM main_student WHERE branch = '" +
branch + "' AND semester = '" + semester + "';")
data = cursor.fetchall()
for d in data:
sql = "INSERT INTO " + tblname + " VALUES(%s,%s,%s,%s,%s,%s);"
val = (d[1], d[0], d[6], 0, date.today(),
datetime.now())
cursor.execute(sql, val)
sql = "INSERT INTO attendance_start_stop VALUES(%s,%s,%s,%s,%s,%s);"
val = (branch, semester, subject, 1, tblname, teacher)
cursor.execute(sql, val)
cursor.execute(
"SELECT tableName FROM attendance_start_stop WHERE faculty = '"
+ teacher + "' AND status = 1 ;")
tableNames = cursor.fetchall()
for table in tableNames:
temp = table
s = ""
for t in range(len(temp)):
if table[t] == '(' or table[t] == ')' or table[
t] == ',' or table[t] == "'":
pass
else:
s += table[t]
table = s
on_going_attendance.append(table)
return render(
request, 'attendance/start_stop.html', {
'message':
"Attendance for " + branch + " " + semester + " " +
subject + " has been started",
'on_going_attendance':
on_going_attendance
})
else:
return render(request, 'attendance/start_stop.html')