ABILITY TO TRACK YOUR TEAM'S ACTIVITIES

What do you want to see in OpenCATS? What the must-have, useful feature that's missing? Is it major redesign or a small tweak? Share here and we can doscuss, agree, add to the roadmap.

Moderators: RussH, cptr13

Post Reply
dgrecruit
Posts: 1
Joined: 08 Apr 2018, 18:18
what is two(2) plus three(3) ?: 5

ABILITY TO TRACK YOUR TEAM'S ACTIVITIES

Post by dgrecruit » 08 Apr 2018, 18:20

As I use Opencats to track my entire team's recruiting activity, we, as managers, need to see what our team is working on.

We'd like to run reports on WHO entered WHICH candidates in certain time periods.

Aka, we have 5 team members. 1000 candidates are added through the 5 people, yet I only see some people's name on each candidate page. Thus, we'd really like to see how many each individual of the team of 5 is adding how many new candidates to the system

This is the most pressing matter, although I have many other ideas of what can be created with this tool. So far, Opencats is awesome.

User avatar
RussH
Site Admin
Posts: 802
Joined: 12 Apr 2008, 08:28
what is two(2) plus three(3) ?: 5
Location: UK
Contact:

Re: ABILITY TO TRACK YOUR TEAM'S ACTIVITIES

Post by RussH » 11 Apr 2018, 21:49

dgrecruit wrote:
08 Apr 2018, 18:20
As I use Opencats to track my entire team's recruiting activity, we, as managers, need to see what our team is working on.

We'd like to run reports on WHO entered WHICH candidates in certain time periods.

Aka, we have 5 team members. 1000 candidates are added through the 5 people, yet I only see some people's name on each candidate page. Thus, we'd really like to see how many each individual of the team of 5 is adding how many new candidates to the system

This is the most pressing matter, although I have many other ideas of what can be created with this tool. So far, Opencats is awesome.
Hi dgrecruit..

good to hear you're looking to extend it!

what you're after is very easy with a judicial sql query. I have something similar - I have weekly emailed reports showing active joborders sorted by the time since last activity update - the report can then show whatever info you want.

As opencats is a pretty simply mysql backend, if you can write a query for what you want you can export the data.

I've used a GUI MySQL query builder https://sourceforge.net/projects/sqleo/ to generate the correct query and to check the results before incorporating it into the script. It's a beginners cheat.. but if you can't write the query in your head (or pay someone to write the query you want) then it's a very simple drag 'n drop route to working towards the data you need.

The issue is where you want the data. If you want it in opencats, then it's another tab, some custom php coding by a developer - and integrated into the ACLs so that it's restricted to an admin group etc etc. I had a particular use-case and didn't want it within opencats - hence the email option.

{I'm currently looking at working a 'sales team' dashboard with metrics, top & bottom recruiters, etc, etc, using dashing.io which seems far too simple to be believed. But that's a project yet to be started)

If you want to look into the email option, I'm happy to share more details on how it's implemented - but the basic code is below (note: you'd need another script to convert the csv to xls (I've got csv2xlsx from http://gitlab.com/DerLinkshaender/csv2xlsx if you wanted to send it as an excel attachment. You could send an embedded table far more easily but as soon as the line length gets too long, it wraps untidily.
Finally, in this case I use mutt to send the email as I have a mailrelay accessible. If you don't there are alternatives available.

Code: Select all

[russh@apps ~]# cat check-active-joborders.sh 
#!/bin/bash
# Proper header for a Bash script.

# Send summary of active JobOrders sorted by date of last activity (oldest first)
mysql -u root -p OPENCATS --password=YOURPASSWORDHERE << QUERY_INPUT

#sql commands go here
USE opencats;

SELECT
	opencats.joborder.joborder_id AS Job,
	opencats.joborder.status,
	opencats.user.user_name AS Recruiter,
	opencats.company.name,
	opencats.joborder.title AS Job_Title,
	DATE_FORMAT( opencats.joborder.date_created , '%M %D, %Y' ) AS "Creation_Date",
	DATEDIFF( NOW( ) , opencats.activity.date_modified ) AS "Days_since_touched",
	opencats.contact.first_name,
	opencats.contact.last_name,
	LEFT( opencats.activity.notes , 30 ) AS "Latest_Notes_Summary"
FROM
	opencats.user
	INNER JOIN opencats.joborder
	 ON opencats.user.user_id = opencats.joborder.recruiter
	RIGHT OUTER JOIN opencats.activity
	 ON opencats.joborder.joborder_id = opencats.activity.joborder_id
	INNER JOIN opencats.company
	 ON opencats.joborder.company_id = opencats.company.company_id
	INNER JOIN opencats.contact
	 ON opencats.joborder.company_id = opencats.contact.company_id
WHERE
	opencats.joborder.status = "Active"
GROUP BY
	opencats.joborder.joborder_id
ORDER BY
	opencats.user.user_name ASC INTO OUTFILE '/tmp/active-joborders.csv'
		FIELDS TERMINATED BY '|'
		ENCLOSED BY '"'
		LINES TERMINATED BY '\n';

QUERY_INPUT

# convert to XLSX
csv2xlsx -infile "/tmp/active-joborders.csv" -outfile "/tmp/active-joborders.xlsx" 

# Email the results to recipients    

# mutt -a '/tmp/active-joborders.xlsx' -s 'All Active Joborders' sourceemail@domain.com
echo "FOR YOUR ACTION" | mail -s "ACTION REQUIRED:All active joborders" -a /tmp/active-joborders.xlsx recruiter_dist_list@domain.com


# clean-up the tmp file or MYSQL won't over-write
rm /tmp/active-joborders.csv
rm /tmp/active-joborders.xlsx

exit 
RussH

Report your issues and feature requests;
https://github.com/opencats/opencats/issues

Please CLICK THE TICK to accept the answer!

Post Reply