[dgrecruit]

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.

[RussH]

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' [email protected]
echo "FOR YOUR ACTION" | mail -s "ACTION REQUIRED:All active joborders" -a /tmp/active-joborders.xlsx [email protected]


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

exit 

[richardhicks]

There are a lot of programs and software systems which track team's daily activities and progress in the real-time.

[copiousquirk]

Is there a way to do this with a hosted online opencats ATS and see realtime data?

[RussH]

well so long as you have access to your database (which you can do even online, shared), you should be able to use one of the dashboard frameworks such as smashing https://smashing.github.io/ (demo here http://dashingdemo.herokuapp.com/sample). This would show whatever metrics you wanted, realtime, you can then have this on a webpage or on a large tv display in the office.

It's one of my someday/oneday projects to find time to play with it. If you progress anything please let me know!!