Friday 27 April 2012

Where to place the Workings out in a Payroll MVC Script.

At present we're in the process of creating a script that works out shift details and payments.

Here's what needs to happen. 

>  find all shifts for a user.

>  Calculate each shift total - pay rate x hours = total

>  Then add all the shifts together and take off the insurance amount for the week.


In procedural PHP this is how I've seen it done.

$sql = get list off all users.

$sql = all shifts for that user

while = a loop that calculates each shift and puts the total in an array

array_push = we add together all the shift

total = minus the shift amount and they you have it.


So the common sense would say that in a MVC format the solution would be similar.   However consider this solution. 


on line 39 of components/com_projectfork/models/bacs.php  we have this query

$query1 = "

SELECT
#__time_diff.name,
#__time_diff.user_id, #__time_diff.hourly_rate,
#__time_diff.cb_insurancerate,
#__time_diff.bumber,
sum((#__time_diff.time_diff)*
#__time_diff.hourly_rate)-
#__time_diff.cb_insurancerate as total


FROM #__time_diff ".$filter." GROUP BY #__time_diff.user_id

";

The trick to this is understanding this question.

The workings are done inside the mySQL call, but how are the shifts added together ?

and the answer is in the usage of MySQL sum

No comments: