Light At The End Of The Tunnel

At work we wanted to use orangeHRM to allow the staff to request leave and have it signed off by the managers, getting rid of a slow paper based system that offered little visibility to the staff on when the leave was signed off. The only issue is that whilst orangeHRM provides some reports, it does not currently allow for a leave summary report to be exported only shown in the web interface as tabbed pages.

As I’m trying to learn Python at the moment I thought I might be able to write a stand alone script that queried the database orangeHRM uses, and print out a summary of the signed off leave.

For accessing the database I used the MySQLdb python library. I won’t discuss how to use that as many others already cover it. The data is stored across three tables, hs_hr_employee, hs_hr_leavetype, and hs_hr_leave. hs_hr_employee contains all the information about the staff, hs_hr_leavetype contains the type of leave an employee can take, i.e. sick leave, leave in lieu, annual leave etc. , hs_hr_leave contains the leave requested an whether it has been approved.

Firstly, we need to retrieve the employee details [python] # create a dictionary cursor so that column values can be accessed by name # rather than by position cursor = conn.cursor (MySQLdb.cursors.DictCursor)

# retrieve the employee details, but we are only interested in employee number,
# id, lastname and firstname
cursor.execute ("SELECT emp_number, employee_id, emp_lastname, emp_firstname FROM hs_hr_employee")
employee_set = cursor.fetchall ()

[/python] OK so now we have the employee details, we retrieve all the leave types [python] cursor.execute (“SELECT * FROM hs_hr_leavetype”) leave_type_set = cursor.fetchall() [/python] Now we retrieve all the leave [python] cursor.execute (“SELECT * FROM hs_hr_leave”) leave_set = cursor.fetchall () [/python] All we do now is generate the report [python] print “Leave Description, Leave Date, Lenght of Leave, Firstname, Lastname” for leave in leave_set: for leave_type in leave_type_set: if leave_type[“leave_type_id”] == leave[“leave_type_id”]: leave_description = leave_type[“leave_type_name”] break

    for emp_row in employee_set:
        if leave["employee_id"] == emp_row["emp_number"]:
            emp_firstname = emp_row["emp_firstname"]
            emp_lastname = emp_row["emp_lastname"]
            break
    print "%s, %s, %s, %s, %s" % (leave_description,
         leave["leave_date"],leave["leave_length_days"],emp_firstname,emp_lastname)

[/python] As you can see from my code snippets above, my SQL is not too hot either, I’m sure there are more efficient ways of retrieving the data.

The story doesn’t end there as the report needs to be useful. One way for the data to be visible to all is to provide the data as calendar entries that the staff can refer to. To do this I would provide an ics file from a web server, allowing staff to subscribe to that feed.

To Work with ics files in Python the only library I could find was iCalendar.  To create the ics file you need to create a calendar objectTo create the ics file you need to create a calendar object [python] # Create a iCalendar object cal = Calendar() cal.add(‘prodid’, ‘-//PKJE calendar product//pkje.net//') cal.add(‘version’, ‘2.0’) [/python] OK now we need to create calendar events for the staff leave instead of creating a report [python] # split leave_date into year, month, day year = leave[“leave_date”].year month = leave[“leave_date”].month day = leave[“leave_date”].day

    # Create a Calendar event
    event = Event()
    event.add('summary', leave_description+" "+emp_firstname+" "+emp_lastname)
    event.add('dtstart', datetime(year,month,day,8,0,0,tzinfo=None))
    event.add('dtend', datetime(year,month,day,18,0,0,tzinfo=None))
    event.add('dtstamp', datetime(year,month,day,0,10,0,tzinfo=None))
    event['uid'] = str(year)+str(month)+str(day)+str(leave_type["leave_type_id"])+emp_firstname+emp_lastname
    event.add('priority', 5)
    cal.add_component(event)

[/python] As you can see we use the leave entry to create simple single day entries, and at the moment the code does not handle half days, but will do in the future. The final step is to write the calendar entries out to a file [python] f = open(‘leave-pkje.ics’, ‘wb’) f.write(cal.as_string()) f.close() [/python] I have the script run every night so that it can keep up to date with new requests, and now no-one has an excuse not to know who is on holiday.