This article mainly introduces a tutorial to explain data visualization in the Tornado framework of Python. Tornado is an asynchronous and highly popular development framework. Friends who need it can refer to
Expansion Module Used
xlrd:
Python extension tool for reading Excel. Can read the specified form and the specified cell.
Must be installed before use.
Download address: https://pypi.python.org/pypi/xlrd
After decompression, cd to the decompression directory and execute python setup.py install
datetime:
Python has built-in modules for manipulating dates and times
Function module to be implemented
Read xls file and enter database
Obtain the duty status of the day according to the three parameters of year, month and day
Pie chart (number of people who completed the task on the day / number of people who did the task on the day)
Waterfall chart (the duty situation of all the staff on the day)
Get the duty status of the current month based on the year and month parameters
Obtain the duty status of the current year according to the annual parameters
Only valid on duty and within half an hour before and after.
You must check in at work and off time, and the absence of check in is considered unattended.
Analyze Excel Sheets
My Fingerprint Time Attendance Device can export the punch-in records for up to one month at a time. One problem is that this month may span two months or a year. For example: March 21, 2015-April 20, 2015, December 15, 2014-January 05, 2015. So pay attention to this pit when writing the processing method.
The exported table is shown in the figure:
=. = It looks like almost no one on duty, yes, that's it.
Everyone is so lazy. T
Sign ...
A brief analysis,
Attendance record sheet is the third sheet of the file
The third line has the start and end time
The fourth line is the number of all dates
The next two lines: the first line of user information; the second line of attendance records
Ideas
Decided to use 3 collections to store relevant information:
user: user information, including id, name, dept
record: attendance record, including id (user id), y (year), m (month), d (day), check (time card record)
duty: duty arrangement, including id (week number, example: 1 for Monday), list (duty staff id list), user_id: ["start_time", "end_time"] (user start and end time)
Read the xls file and save the new attendance records and new users into the database.
Query the corresponding record according to the year, month, and day parameters, and query the day's duty arrangement to match and obtain the attendance records of the students on duty on that day. Compare the check-in time of the classmates on duty with the time on duty, determine whether the time is normal, and calculate the actual duty time and the actual duty percentage.
After outputting the data in json format, use echarts to generate charts.
Analyzing the attendance records for the current month and year is similar, but it may be slightly more complicated.
All explanations and specific ideas are placed in the source code comments, please continue to look at the source code ~
class MainHandler (tornado.web.RequestHandler):
def get (self):
pass
class ReadHandler (tornado.web.RequestHandler):
def get (self):
#Get collection
coll_record = self.application.db.record
coll_user = self.application.db.user
#Read excel form
table = xlrd.open_workbook ('/ Users / ant / Webdev / python / excel / data.xls')
#Read punch card record sheet
sheet = table.sheet_by_index (2)
#Read the punch month range
row3 = sheet.row_values (2)
m1 = int (row3 [2] [5: 7])
m2 = int (row3 [2] [18:20])
#Set the current year
y = int (row3 [2] [0: 4])
#Set the current month as the first month
m = m1
#Read punch-in date range
row4 = sheet.row_values (3)
#Initialize the previous day
lastday = row4 [0]
#Iterate through the dates in the fourth line
for d in row4:
#If the date is less than the previous date
#Explanation month increases, then modify the current month to the second month
if d <lastday:
m = m2
#If the current two months are December and January
# DESCRIPTION New Year, so the year +1
if m1 == 12 and m2 == 1:
y = y + 1
#Count with n, range from 3 to (total number of lines / 2 + 1)
# (Total Lines / 2 + 1)-3 = Total Users
#Ie traverse all users
for n in range (3, sheet.nrows / 2 + 1):
#Take the first line of the user, that is, the user information line
row_1 = sheet.row_values (n * 2-2)
#Get user id
u_id = row_1 [2]
#Get user name
u_name = row_1 [10]
#Get user department
u_dept = row_1 [20]
#Query the user
user = coll_user.find_one ({"id": u_id})
#If the user does not exist in the database then create a new user
if not user:
user = dict ()
user ['id'] = u_id
user ['name'] = u_name
user ['dept'] = u_dept
coll_user.insert (user)
#Take the second line of the user, which is the attendance record line
row_2 = sheet.row_values (n * 2-1)
#Get the index of the current date
idx = row4.index (d)
#Get the attendance record of the current user's current date
check_data = row_2 [idx]
#Initialize empty attendance record list
check = list ()
# 5 character set, traverse attendance record and save it to attendance record list
for i in range (0, len (check_data) / 5):
check.append (check_data [i * 5: i * 5 + 5])
#Query the current user's day record
record = coll_record.find_one ({"y": y, "m": m, "d": d, "id": user ['id']})
#Update record if record exists
if record:
for item in check:
#Add new attendance record to previous record
if item not in record ['check']:
record ['check']. append (item)
coll_record.save (record)
#Insert a new record if the record does not exist
else:
record = {"y": y, "m": m, "d": d, "id": user ['id'], "check": check}
coll_record.insert (record)
class DayHandler (tornado.web.RequestHandler):
def get (self):
#Get year, month, and day parameters
y = self.get_argument ("y", None)
m = self.get_argument ("m", None)
d = self.get_argument ("d", None)
#Judging whether the parameters are fully set
if y and m and d:
#Convert parameters to integers for easy use
y = int (y)
m = int (m)
d = int (d)
#Get all records for the day
coll_record = self.application.db.record
record = coll_record.find ({"y": y, "m": m, "d": d})
#Get the day of the week
weekday = datetime.datetime (y, m, d) .strftime ("% w")
#Get the watch on the day
coll_duty = self.application.db.dutyduty = coll_duty.find_one ({"id": int (weekday)})
#Initialize empty target records (records of personnel on duty on that day)
target = list ()
#Iterate through all records for the day
for item in record:
#When the user of this record has an on-duty task for the day, calculate and store it in the target array
if int (item ['id']) in duty ['list']:
#Get the user's start and end time by user id
start = duty [item ['id']] [0]
end = duty [item ['id']] [1]
#Calculate duty time / second
date1 = datetime.datetime (y, m, d, int (start [: 2]), int (start [-2:]))
date2 = datetime.datetime (y, m, d, int (end [: 2]), int (end [-2:]))
item ['length'] = (date2-date1) .seconds
#Initial actual duty percentage
item ['per'] = 0
#Initial commute time
item ['start'] = 0
item ['end'] = 0
#Iterate through the user's punch card record
for t in item ['check']:
#When earlier than the duty time
if t <start:
#Calculate time difference
date1 = datetime.datetime (y, m, d, int (start [: 2]), int (start [-2:]))
date2 = datetime.datetime (y, m, d, int (t [: 2]), int (t [-2:]))
dif = (date1-date2) .seconds
#When the punch time is within half an hour before the duty time
if dif <= 1800:
#Go to work successfully
item ['start'] = start
elif t <end:
#If not yet at work
if not item ['start']:
# Then record the current time as time to work
item ['start'] = t
else:
#Otherwise record the current time as the punch time
item ['end'] = t
else:
#If you already work
if item ['start']:
#Calculate time difference
date1 = datetime.datetime (y, m, d, int (end [: 2]), int (end [-2:]))
date2 = datetime.datetime (y, m, d, int (t [: 2]), int (t [-2:]))
dif = (date1-date2) .seconds
#When the punch time is within half an hour after the duty time
if dif <= 1800:
#Get off to work successfully
item ['end'] = end
#Punch when you go to work
if item ['start'] and item ['end']:
#Calculate actual duty time
date1 = datetime.datetime (y, m, d, int (item ['start'] [: 2]), int (item ['start'] [-2:]))
date2 = datetime.datetime (y, m, d, int (item ['end'] [: 2]), int (item ['end'] [-2:]))
dif = (date2-date1) .seconds
#Calculate (actual shift duration / duration duration) percentage
item ['per'] = int (dif / float (item ['length']) * 100)
else:
# Don't go to work as normal
item ['start'] = 0
item ['end'] = 0
#Add records to the target array
target.append (item)
#Output Data
self.render ("index.html",
target = target
)
{
{% for item in target%}
{
'id': {{item ['id']}},
'start': {{item ['start']}},
'end': {{item ['end']}},
'length': {{item ['length']}},
'per': {{item ['per']}}
}
{% end%}
}
At last
For the time being, I will only read the file and check the duty situation on a certain day, and then I will continue to write this small application according to the previous plan.
Because of the privacy of a bunch of friends, the test file was not sent up. However, if there are students who want to see the actual operation, they can tell me that I will send you the file.
A database insert statement that may be used: db.duty.insert ({"id": 5, "list": [1,2], 1: ["19:00", "22:00"], 2: ["19:00", "22:00"]})
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
