Tutorials on Python's tornado framework for data visualization

the expansion module used

xlrd:

In the Python language, read the extension tool for Excel. You can implement a read of a specified form, a specified cell.
Must be installed before use.
Download Address: https://pypi.python.org/pypi/xlrd
Unzip the CD into the extract directory, execute the python setup.py install

Datetime:

Python built-in module for manipulating date and time

Implement the Function module

Read the XLS file and enter the database

According to the year, month, day three parameters to get the day's duty situation

Pie chart (number of people who have completed on duty on the day/the number of tasks on duty on the day)

Waterfall Chart (on duty for all on duty on the same day)

According to the two parameters of the year and month to obtain the current month's duty situation

According to the annual parameters to obtain the current year's duty situation

Duty system

There are 6 classes a day:

8:00-9:45
9:45-11:20
13:30-15:10
15:10-17:00
17:00-18:35
19:00-22:00

Everyone has a maximum of one class per day.

Clock-in time only and half hour before and after the card is valid.

Clock-out is required for work and work, and missing punch is considered not on duty.

Analyze Excel Tables

My fingerprints. The timecard can export up to one months of punch-out records at a time. One problem is that the one months may span two months, and may span a year. For example: March 21, 2015-April 20, 2015, December 15, 2014-January 05, 2015. So be sure to pay attention to this hole when you write the processing method.

Exported tables:

= = It looks as if there is no one on duty, yes, that's it.
Everyone is so lazy t. T
Sign ...

Simple analysis,

• The Attendance record table is the third sheet of the file.
• The third line has a beginning and ending time
• Line four is a number for all dates
• Next every two lines: first behavior user information; second ACT attendance record

Ideas

Decide to store the relevant information with 3 collection separately:

1. User information, including ID, name, dept
2. Record: Attendance record, including ID (user ID), y (year), M (month), D (Day), check (punch record)
3. Duty: Duty schedule, including ID (day of the Week, example: 1 for Monday), list (on-duty person ID), user_id:["Start_time", "end_time" (User duty start and end time)

Read the XLS file and save the new attendance record and the new user to the database.

According to the date parameter query corresponding record, check the day on duty arrangements, matching to get the day attendance record of the students on duty. will be on duty classmate's punch-in time and the duty time comparison pair, judge whether the normal punch-in, calculates the actual duty time, the actual duty percentage.

After that, output the JSON format data and generate the chart with Echarts.

Analysis of the same month, the same year's attendance records, but may be slightly more complicated.

All the explanations and specific ideas are put in the source code comments, please continue to read the source bar ~

Source

main.py

#!/usr/bin/env python#-*-coding:utf-8-*-import os.pathimport tornado.authimport tornado.escapeimport Tornado.httpserverimport tornado.ioloopimport tornado.optionsimport tornado.webfrom tornado.options Import define, Optionsimport pymongoimport timeimport datetimeimport xlrddefine ("Port", default=8007, help= "run on the given port", type =int) class Application (tornado.web.Application): def __init__ (self): handlers = [(R "/", MainHandler), "R"/R EAD ", Readhandler), (R"/day ", Dayhandler),] settings = Dict (Template_path=os.path.join (Os.path.dirname (_ _file__), "Templates"), Static_path=os.path.join (Os.path.dirname (__file__), "Static"), Debug=true,) con n = Pymongo. Connection ("localhost", 27017) self.db = conn["Kaoqin"] tornado.web.application.__init__ (self, handlers, **settings)  Class MainHandler (Tornado.web.RequestHandler): Def get (self): Passclass Readhandler (tornado.web.RequestHandler): def Get (self): #获取collection Coll_rEcord = Self.application.db.record Coll_user = self.application.db.user #读取excel表格 table = Xlrd.open_workbook ('/u Sers/ant/webdev/python/excel/data.xls ') #读取打卡记录sheet Sheet=table.sheet_by_index (2) #读取打卡月份范围 row3 = Sheet.row_ VALUES (2) m1 = Int (Row3[2][5:7]) m2 = int (row3[2][18:20]) #设置当前年份 y = Int (row3[2][0:4]) #设置当前月份为第一个月份 m = M1 #读取打卡日期范围 row4 = sheet.row_values (3) #初始化上一天 lastday = row4[0] #遍历第四行中的日期 for D in Row4: #如果日期        Less than the previous date #说明月份增大, modify the current month to the second month if d < lastday:m = M2 #如果当前两个月份分别为12月和1月 #说明跨年了, so the year +1      If M1 = = and M2 = = 1:y = y + 1 #用n计数, range 3 to (total number of rows/2+1) # (Total rows/2+1)-3 = number of users #即遍历所有用户        For-N in range (3, sheet.nrows/2+1): #取该用户的第一行, user information Line row_1 = Sheet.row_values (n*2-2) #获取用户id u_id = row_1[2] #获取用户姓名 u_name = row_1[10] #获取用户部门 u_dept = row_1[20] #查询该用户 use R = Coll_user.find_one ({"id": u_id}) #如果数据库中不存在该用户则创建新用户 if not user:user = Dict () user[' id '] = u_id user [' name '] = u_name user[' dept '] = u_dept coll_user.insert (user) #取该用户的第二行, i.e. attendance record line row_2 = sh         Eet.row_values (n*2-1) #获取改当前日期的下标 idx = Row4.index (d) #获取当前用户当前日期的考勤记录 check_data = Row_2[idx]          #初始化空考勤记录列表 check = list () #5个字符一组, traverse the attendance record and deposit the attendance record list for I in range (0,len (check_data)/5): Check.append (Check_data[i*5:i*5+5]) #查询当前用户当天记录 record = Coll_record.find_one ({"Y": Y, "M": M, "D":d, "id" : user[' id '}) #如果记录存在则更新记录 if Record:for item in check: #将新的考勤记录添加进之前的记录 if Item not in record[' check ': record[' Check '].append (item) Coll_record.save (record) #如果记录不 There is a new record inserted 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): #获取年月日参数 y = self.get_argument ("y", None) m = self.g  Et_argument ("M", none) d = self.get_argument ("D", none) #判断参数是否设置齐全 if Y and M and D: #将参数转换为整型数, easy to use y = Int (y) m = int (m) d = Int (d) #获取当天所有记录 Coll_record = Self.application.db.record record = Coll_re      Cord.find ({"Y": Y, "M": M, "D":d}) #获取当天为星期几 weekday = Datetime.datetime (y,m,d). Strftime ("%w") #获取当天值班表 Coll_duty = self.application.db.duty duty = Coll_duty.find_one ({"id": Int (Weekday)}) #初始化空目标记录 (the day on duty record) tar get = List () #遍历当天所有记录 for item in record: #当该记录的用户当天有值班任务时, computes and deposits the target array if int (item[' id ') in du ty[' list ': #通过用户id获取该用户值班起止时间 start = duty[item[' id ']][0] end = duty[item[' id ']][1] # Calculate Shift Duration/sec 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 #初始化实际值班百分比 item[' per '] = 0 #初始化上下班打卡时间 item[' start '] = 0 item[' End ' = 0 #遍历该用户打卡记录 for t in item[' check ']: #当比值班时 Early if T < start: #计算时间差 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 #当打卡时间在值班时 Within the first half hour if DIF <= 1800: #上班打卡成功 item[' start ' = Start elif T <  End: #如果还没上班打卡 If not item[' start ']: #则记录当前时间为上班打卡时间 item[' start ') = T else: #否则记录当前时间为下班打卡时间 item[' end '] = T else: #如果已经 Clock-in If item[' start ': #计算时间差 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 #当打卡时间 Within half an hour after duty time if dif <= 1800: #下班打卡成功 item[' end ' = End #当上班下班均打 Card if item[' start '] and item[' End ': #计算实际值班时长 date1 = Datetime.datetime (Y,m,d,int (item[' STA            RT '][:2]), int (item[' start '][-2:])) Date2 = Datetime.datetime (Y,m,d,int (item[' End '][:2]), int (item[' End '][-2:])) dif = (date2-date1). Seconds #计算 (actual on-duty/on-duty) percentage item[' per '] = Int (dif/float (item[' lengt H ']) () Else: #未正常上下班则视为未值班 item[' start '] = 0 item[' end '] = 0 #将记录添 Add to target array target.append (item) #输出数据 Self.render ("index.html", target = target) def main (): Tornado.options.parse_command_line () Http_server = Tornado.httpserver.HTTPServer (Application ()) HTTP_ Server.listen (oPtions.port) tornado.ioloop.IOLoop.instance (). Start () if __name__ = = "__main__": Main () index.html{{% for item in target     %} {' id ': {{item[' id '}}, ' Start ': {{item[' start '}}, ' End ': {{item[' end '}}, ' length ': {{item[' length '}}, ' per ': {{item[' per '}}}{% end%}}

At last

Temporarily write only to read the file and query the day on duty, and then continue to follow the previous plan to the small application finished.

Because of the privacy of a bunch of small partners, the test files were not sent up. But if you want to actually run a look at the classmate can tell me, I send the documents to you.

Possible use of a database INSERT statement: Db.duty.insert ({"id": 5, "list": [1,2],1:["19:00", "22:00"],2:["19:00", "22:00"]})

Hope to be helpful to the beginner like me!

