For example, the Python Tornado framework for data visualization tutorial, pythontornado

For example, the Python Tornado framework for data visualization tutorial, pythontornado

Extended modules used

Xlrd:

An extension tool for reading Excel in Python. You can read a specified form or cell.
Installation is required before use.
: Https://pypi.python.org/pypi/xlrd
Decompress the package and cd it to the decompressed directory. Execute python setup. py install.

Datetime:

Python built-in module for date and time operations

Functional modules to be implemented

Read the xls file and input it to the database

Obtain the on-duty status of the current day based on the year, month, and day parameters.

Pie Chart (number of persons on duty on the current day/Number of persons on duty not completed on the current day)

Waterfall chart (the duty status of all personnel on duty on the day)

Obtain the duty status for the current month based on the year and month parameters.

Obtain the duty status of the current year based on the annual Parameter

Duty System

There are 6 classes every day:

Am-Am
Am-Am
PM-PM
PM-PM
PM-PM
PM-PM

Each person has at most one class per day.

Only the duty time and the first half of the hour are effective.

Punching is required at work and off work. If you do not have a punching card, it is deemed that you are not on duty.

Analyze an Excel table

My fingerprint attendance machine can export at most one month's logging records at a time. One problem is that this month may span two months or one year. For example, from January 1, to January 1, to January 1. Therefore, you must pay attention to this pitfall when writing a processing method.

Export table:

=. = It seems that no one is on duty, right.
Everyone is very lazy. T
Sign...

For a brief analysis,

• The attendance record table is the third sheet of the file.
• Row 3 has a start and end time
• The fourth row is the number of all dates.
• The following two rows: First behavior user information; second behavior attendance record

Ideas

It is decided to store related information using three collections:

1. User: user information, including id, name, and dept
2. Record: attendance record, including id (User id), y (year), m (month), d (day), check (punch record)
3. Duty: duty schedule, including id (number of weeks, for example, 1 indicates Monday), list (list of persons on duty id), user_id: ["start_time ", "end_time"] (user's duty start time and End Time)

Read the xls file and store new attendance records and new users to the database.

Query the corresponding record based on the year, month, and day parameters. query the duty schedule for the current day and obtain the attendance records of the employees on duty on the current day. Compare the punching time of the on-duty personnel with the duty time to determine whether the punching is normal, and calculate the actual duty time and actual duty percentage.

Then, the json format data is output and echarts is used to generate charts.

The Analysis of attendance records for the current month and current year is the same, but it may be a little complicated.

All the explanations and specific ideas are included in the source code comments. Please continue to read the source code ~

Source code

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 "/read", 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,) conn = 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): # get collection coll_record = self. application. db. record coll_user = self. application. db. user # Read the excel table = xlrd. open_workbook ('/Users/ant/Webdev/python/excel/data.xls') # Read the logging sheet = table. sheet_by_index (2) # Read the month 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 to the first month m = m1 # Read the date range row4 = sheet. row_values (3) # initialize lastday = row4 [0] # traverse the date in the fourth row for d in row4: # if the date is smaller than the previous date # It indicates that the month is increased, modify the current month to the second month if d <lastday: m = m2 # if the current two months are respectively December and January # indicates that the new year has passed, so year + 1 if m1 = 12 and m2 = 1: y = y + 1 # count with n, range: 3 to (total number of rows/2 + 1) # (total number of rows/2 + 1)-3 = total number of users # traverse all users for n in range (3, sheet. nrows/2 + 1): # obtain the first row of the user, that is, the row row_1 = sheet. row_values (n * 2-2) # obtain user id u_id = row_1 [2] # obtain user name u_name = row_1 [10] # obtain user department u_dept = row_1 [20] # query user = coll_user.find_one ({"id ": u_id}) # create a new user if not user: user = dict () if the user does not exist in the Database () user ['id'] = u_id user ['name'] = u_name user ['dept'] = u_dept coll_user.insert (user) # obtain the second row of the user, that is, the attendance record row row_2 = sheet. row_values (n * 2-1) # obtain the subscript idx = row4.index (d) of the current date) # obtain the attendance record check_data = row_2 [idx] # initialize the empty attendance record list check = list () # A group of 5 Characters, traverse the attendance record and save it to the attendance record list for I in range (0, len (check_data)/5): check. append (check_data [I * 5: I * 5 + 5]) # query record = coll_record.find_one ({"y": y, "m": m, "d": d, "id": user ['id']}) # if a record exists, update the record if record: for item in check: # Add the new attendance record to the previous record if item not in record ['check']: record ['check']. append (item) coll_record.save (record) # If the record does not exist, insert the new record 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): # obtain the parameter y = self. get_argument ("y", None) m = self. get_argument ("m", None) d = self. get_argument ("d", None) # determine whether the parameter is set completely. if y and m and d: # convert the parameter to an integer, which facilitates the use of y = int (y) m = int (m) d = int (d) # retrieve all records of the current day coll_record = self. application. db. record = coll_record.find ({"y": y, "m": m, "d": d}) # obtain weekday = datetime for the day of the week. datetime (y, m, d ). strftime ("% w") # obtain the daily duty schedule coll_duty = self. application. db. duty = coll_duty.find_one ({"id": int (weekday)}) # Initialize an empty target record (person on duty on the day) target = list () # traverse all records for item in record on the current day: # When the user of the record has a duty task on the current day, calculate and store the target array if int (item ['id']) in duty ['LIST']: # obtain the start time and end time of the user's duty using the user id. start = duty [item ['id'] [0] end = duty [item ['id'] [1] # Calculate the duty duration/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 # initialize the actual duty percentage item ['per '] = 0 # initialize the punching time item ['start'] = 0 item ['end'] = 0 # traverse the user's punching record for t in item ['check']: # When it is earlier than the duty time, if t <start: # Calculate the 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 # if dif <= 1800: # successful punching at work item ['start'] = start elif t <end: # if you have not completed the work-time punching, if not item ['start']: # The current recording time is the work-time punching time item ['start'] = t else: # Otherwise, the current record time is the off-duty punch time item ['end'] = t else: # if the off-duty punch item ['start']: # Calculate the 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 # if dif <= 1800: # after-work punch-in success item ['end'] = end # after work all times if item ['start'] and item ['end']: # Calculate the 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 # Calculated (actual duty time/duty time) Percentage item ['per '] = int (dif/float (item ['length']) * 100) else: # abnormal commuting is considered as not on duty item ['start'] = 0 item ['end'] = 0 # Add record to target array. append (item) # output data 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 % }}

Last

Currently, only the files are read and the on-duty status of the day is queried. Later, the small application will be written according to the previous plan.

Because of the privacy of a bunch of friends, the test file was not sent. However, if you want to run it, you can tell me that I will send the file to you.

A possible database insert statement: db. duty. insert ({"id": 5, "list": [], 1: ["", ""], 2 ", "22:00"]})

Hope it will be helpful for beginners like me!