Training tracking program I want to build for work

Started by SpleenBeGone, Jan 29 2013 09:21 AM

Please log in to reply

34 replies to this topic

#1 SpleenBeGone

Deer Leader of the Goriest Revolution

Administrators
14,951 posts

LocationHouston

Posted 29 January 2013 - 09:21 AM

I'd like to build a program for tracking all of our training at work to make things 100x easier. The whole thing needs database storage to work, I'm sure, so my initial thought was PHP + SQL. I'm not terribly fluent in either though, so if there's something that might make this better, I'm open to giving a new language or program a try.
I need a page to add a new employee, which is simple enough, something like this:
Posted Image

Then I need a page with search abilities to find employees, and list their training, along with other things. Here's an example:
Posted Image

A simple login should be all the security needed, possibly with the ability to support multiple users.
A third page, right after the login which shows employees with training soon to expire, and the ability to click their name and go to the proper display page would also be very nice.

I'm thinking if it's not web based, it could also be done using one of microsoft's frameworks for VB or C#, as I imagine they can handle different databases as well.

#2 Guest_ElatedOwl_*

Guests

Posted 29 January 2013 - 10:04 AM

All of this is actually pretty simple. I would recommend keeping it web based; managing deployments across multiple workstations sucks. I would definitely recommend PHP + MySQL,

Simple non-enterprisey apps are what PHP was originally designed for
Free
Easier to configure than IIS (seriously, I fking hate IIS)

You're looking at ~5 tables,

Users

ID (Identity column, int)
Username
E-Mail
Salt
Password (this should be encrypted combined with the salt)
eID (optional, only use if each user is going to be in the employees table as well)

Employees

ID (identity column)
First name
Last name
SSN
ccID (craft code ID to link it to the craft code table)
Picture (I'd upload the pictures to whatever folder and name them based off their MD5 then store that MD5 string here)

craftCodes

ID (identity column)
code
name (this and description are optional, I'm not sure what a craft code is but its generally useful to have as much info as possible assosciated with it)
description

courses

ID
name, etc. (any relevant course info should have a field, don't know the specifics)
validTime (amount of time the course stays valid, ie 50 days until it expires)

employeeTraining

ID (its optional here - if you have the disk space I'd use it to avoid dupe row issues)
cID (course ID)
eID (employee ID)
dateCompleted

#3 SpleenBeGone

Deer Leader of the Goriest Revolution

Administrators
14,951 posts

LocationHouston

Posted 29 January 2013 - 10:18 AM

All of this would be run on an internal intranet server, though it'd have to get ported out for users at our two other locations. Disk space isn't really an issue, if things work properly, I could pretty easily convince them to drop some money on a handfull of extra hard drives for a RAID, as it is, I've got a pair of 2TB drives in RAID1.

Craft codes are basically codes that stand for what an employee does, and what they get paid for it.

Now, with the training courses, I've got a list of the major ones we use, close to 100 I believe. I'd like to go ahead and have those in place in the table, and an autocomplete so when a user starts typing, it brings up the relevant courses for them to select, or just adds what they type in, if it doesn't yet exist.

Looks like I'm going to have to get better at PHP after all.
<.<

#4 Guest_ElatedOwl_*

Guests

Posted 29 January 2013 - 10:25 AM

You'll want to familiarize yourself with javascript/jQuery as well if you want rich client side features. (like auto complete)

There are a lot of out of the box jQuery plugins for stuff like that, but they're kind of shitty IMO. (but I am known to have a good case of not invented here syndrome when it comes to easy implementable software) Generalized to work in every case usually means its harder to work with in your specific case or inhibits you somehow.

#5 SpleenBeGone

Deer Leader of the Goriest Revolution

Administrators
14,951 posts

LocationHouston

Posted 29 January 2013 - 10:58 AM

Yeah, I figured a lot of the stuff like that would be AJAX or similar. There's no real time limit on this or anything, I just know we need something better than excel spread sheets.
Plus, if done right, I could probably sell it to other companies as well.

#6 DaRatmastah

Captain Overexcited Cyclops

Members
463 posts

Posted 29 January 2013 - 11:01 AM

As far as the PHP code goes, I honestly wouldn't worry too much. You're going to need an insert, and a couple database reads(MAYBE some joining stuff). It sounds like you're probably going to want to spend a lot more time on the usability side which, as emosaurus said, is allll javascipt/jquery, baby.

Be a Game Character

#7 Guest_ElatedOwl_*

Guests

Posted 29 January 2013 - 11:12 AM

Yeah, I figured a lot of the stuff like that would be AJAX or similar. There's no real time limit on this or anything, I just know we need something better than excel spread sheets.
Plus, if done right, I could probably sell it to other companies as well.

Heh, I don't know if I'd go as far as selling it. I don't mean any offense but you don't really know what you're doing yet, which is ok if the software is in-house. As soon as you sell it you have to be concerned about tighter security, tighter/more re-usable code, documentation, licensing and support for your customers.

#8 SpleenBeGone

Deer Leader of the Goriest Revolution

Administrators
14,951 posts

LocationHouston

Posted 29 January 2013 - 01:08 PM

Oh, I know, absolutely, but if I could get a good base done right, as I learn more it could eventually evolve into something marketable. I meant more down the line as the company has grown quite a bit, and things have gotten perfected.

#9 Guest_ElatedOwl_*

Guests

Posted 29 January 2013 - 01:54 PM

'sho nuff. Let me know if you run into any issues with the pseudo code and I can help you out

#10 SpleenBeGone

Deer Leader of the Goriest Revolution

Administrators
14,951 posts

LocationHouston

Posted 29 January 2013 - 02:10 PM

I'm sure I will. >.>

#11 K_N

Megabyte

Members
576 posts

LocationPhoenix

Posted 29 January 2013 - 09:01 PM

If you need any technical assistance with the PHP/MySQL give me a shout on facebook. I am, after all, a professional PHP Developer.

Rumors of my demise have been greatly exaggerated.

#12 SpleenBeGone

Deer Leader of the Goriest Revolution

Administrators
14,951 posts

LocationHouston

Posted 30 January 2013 - 06:02 AM

That you are. >.>

I might start on the psuedo code this evening or tomorrow, depending on how busy it gets at work.

#13 Coconut Man

Gigabyte

Members
798 posts

LocationThe latest Smash Major

Posted 30 January 2013 - 07:48 AM

Speaking of work,

This is completely off topic, and I'm not responsible if you get in trouble for this, but google cantyouseeimbusy

it has a decent few games that look like work. I used it during school when I was supposed to be working.

I really don't recommend it if you have something to do, but if you're all finished and want to try it...

Really, though, I'm only suggesting this to mess around, not to blow off work with.

#14 SpleenBeGone

Deer Leader of the Goriest Revolution

Administrators
14,951 posts

LocationHouston

Posted 30 January 2013 - 08:03 AM

I have full, unrestricted and unmonitored access to the internet, an office with a door, and a boss that really doesn't care what I do, as long as I get my job done. I don't need stuff that looks like work. >.>
Thanks though.

#15 Coconut Man

Gigabyte

Members
798 posts

LocationThe latest Smash Major

Posted 30 January 2013 - 05:38 PM

Well, I'm gonna have to ask my workplace to block that site in the future, because I find it too tempting.

#16 SpleenBeGone

Deer Leader of the Goriest Revolution

Administrators
14,951 posts

LocationHouston

Posted 04 February 2013 - 08:03 AM

Working on this a bit, and I'm trying to figure out the best way to show courses that are expiring.
Basically, I know that

expireDate=dateCompleted+validTime
if(expireDate<=currentDate-30)
{
echo(cID)
}

How would be the best way to pool all the information at once, as all courses that are valid to the user ID need to be checked.

#17 Guest_ElatedOwl_*

Guests

Posted 04 February 2013 - 08:18 AM

Working on this a bit, and I'm trying to figure out the best way to show courses that are expiring.
Basically, I know that
expireDate=dateCompleted+validTime
if(expireDate<=currentDate-30)
{
echo(cID)
}
How would be the best way to pool all the information at once, as all courses that are valid to the user ID need to be checked.

You should do that from the query. Any time you reduce a data set you want to do it all on the database - that's what its meant for. MySQL has a DATEDIFF function, so you can do something like

select * from whateverTable where DATEDIFF(CURDATE(), dateCompleted) > valdTime

#18 SpleenBeGone

Deer Leader of the Goriest Revolution

Administrators
14,951 posts

LocationHouston

Posted 04 February 2013 - 08:42 AM

Oh nifty.
Using that though, wouldn't it only select courses that had already expired, and not ones set to expire?

#19 Guest_ElatedOwl_*

Guests

Posted 04 February 2013 - 09:00 AM

I misread that. <_< If you wanted courses that are going to expire within the next 30 days I think it would be

select * from whateverTable where DATEDIFF(DATE_ADD(dateCompleted, INTERVAL validTime DAY), CURDATE()) BETWEEN 0 AND 30

if you want it to include expired courses as well change the between to a <= 30

Also keep in mind that select * is considered bad practice, you should explicitly set your fields when you're not testing.

#20 SpleenBeGone

Deer Leader of the Goriest Revolution

Administrators
14,951 posts

LocationHouston

Posted 04 February 2013 - 09:08 AM

Yeah, it would be <=30.
(it's like a 30 with a battering ram)

I'll be posting back here with more noob questions soon. >.>

Back to Coding

Training tracking program I want to build for work

#2 Guest_ElatedOwl_*

#4 Guest_ElatedOwl_*

#7 Guest_ElatedOwl_*

#9 Guest_ElatedOwl_*

#17 Guest_ElatedOwl_*

#19 Guest_ElatedOwl_*

Sign In