php - How to query employee details and relate their performance metrics?
Get the solution ↓↓↓I am fetching the id, first name, and last name of all employees that are approved and not archived. Then I am looping these results and using the ids to query other tables to collect some count data.
I tried the below code, but I am not getting the expected output.
$queryEmp = "
SELECT id, firstname, lastname
FROM tbl_employee as e
WHERE is_archive=0 and is_approved=1
";
$getQuery= $this->db->query($queryEmp);
$result= $getQuery->result();
foreach ($result as $key=> $value) {
//echo "<pre>";
print_r($value);
$day = "MONTH(date_of_created) = DATE(CURRENT_DATE())";
$group = "f_id IN (SELECT MAX(f_id) FROM tbl_fileStatus GROUP BY f_bankid)";
$condiion = "and ba.createdby='" . $value->id . "' and " . $day ." and " . $group;
$query2 = "
select
(SELECT COUNT(c_id)
FROM tbl_lead
WHERE leadstatus='1' AND ".$day.") as confirmCount,
(SELECT COUNT(f_id)
FROM tbl_fileStatus as fs
join tbl_bankdata as ba on ba.bank_id=fs.f_bankid
WHERE fs.f_filestatus=1 " . $condiion . ") as disbursed,
(SELECT COUNT(f_id)
FROM tbl_fileStatus as fs
join tbl_bankdata as ba on ba.bank_id=fs.f_bankid
WHERE fs.f_filestatus=2 ".$condiion.") as filesubmit
";
# code...
$getQuery2= $this->db->query($query2);
$result2[]=$getQuery2->result();
}
echo "<pre>";
print_r(result2);
$result
looks like this:
Array (
[0] => stdClass Object (
[id] => 1
[firstname] => xyz
[lastname] => xyz
)
...
)
Second query output:
Array (
[0] => Array (
[0] => stdClass Object (
[fallowCall] => 0
[confirmCount] => 0
[disbursed] => 0
[filesubmit] => 0
)
)
...
)
How can I produce the correct results which relate respective employees with with their performance metrics? Either this structure:
Array (
[0] => stdClass Object (
[id] => 1
[firstname] => xyz
[lastname] => xyz
[somename] => (
[fallowCall] => 0
[confirmCount] => 0
[disbursed] => 0
[filesubmit] => 0
)
)
...
)
Or this structure:
Array (
[0] => stdClass Object (
[id] => 1
[firstname] => xyz
[lastname] => xyz
[fallowCall] => 0
[confirmCount] => 0
[disbursed] => 0
[filesubmit] => 0
)
...
)
I have added the my table structure and some sample data here: https://www.db-fiddle.com/f/8MoWmKPuzTrrC3DQJsiX35/0
some notes here
1)createdby
is the id of tabletbl_employee
2)lead_id
in the bank table is thec_id
of the tabletbl_lead
3)f_bankid
in thetbl_fileStatus
is thebank_id
of the tabletbl_bankdata
Answer
Solution:
There is actually no need to create the additional depth/complexity just to hold the count data. Furthermore, by using a combination of LEFT JOINs to connect the related tables and apply your required conditional rules, you can achieve your desired result by making just one trip to the database. This will without question provide superior efficiency for your application. LEFT JOINs are important to use so that counts can be zero without excluding employees from the result set.
Also, I should point out that your attempted query was mistakenly comparing aMONTH()
value against aDATE()
value -- that was never going to end well. :) In fact, to ensure that your sql is accurately isolating the current month from the current year, you need to be also checking the YEAR value.
My recommended sql:
SELECT
employees.id,
employees.firstname,
employees.lastname,
COUNT(DISTINCT leads.c_id) AS leadsThisMonth,
SUM(IF(fileStatus.f_filestatus = 1, 1, 0)) AS disbursedThisMonth,
SUM(IF(fileStatus.f_filestatus = 2, 1, 0)) AS filesubmitThisMonth
FROM tbl_employee AS employees
LEFT JOIN tbl_lead AS leads
ON employees.id = leads.createdby
AND leadstatus = 1
AND MONTH(leads.date_of_created) = MONTH(CURRENT_DATE())
AND YEAR(leads.date_of_created) = YEAR(CURRENT_DATE())
LEFT JOIN tbl_bankdata AS bankData
ON employees.id = bankData.createdby
LEFT JOIN tbl_fileStatus AS fileStatus
ON bankData.bank_id = fileStatus.f_bankid
AND MONTH(fileStatus.date_of_created) = MONTH(CURRENT_DATE())
AND YEAR(fileStatus.date_of_created) = YEAR(CURRENT_DATE())
AND fileStatus.f_id = (
SELECT MAX(subFileStatus.f_id)
FROM tbl_fileStatus AS subFileStatus
WHERE subFileStatus.f_bankid = bankData.bank_id
GROUP BY subFileStatus.f_bankid
)
WHERE employees.is_archive = 0
AND employees.is_approved = 1
GROUP BY employees.id, employees.firstname, employees.lastname
TheSUM(IF())
expression is a technique used to execute a "conditional count". "Aggregate data" is formed by using GROUP BY and there are specialized "aggregate functions" which must be used to create linear/flat data from these clusters/non-flat collections of data.fileStatus
data is effectively piled up upon itself due to the GROUP BY call. IfCOUNT(fileStatus.f_filestatus)
was called, it would count all of the rows in the cluster. Since you wish to differentiate betweenf_filestatus = 1
andf_filestatus = 2
, anIF()
statement is used. This is doing the same thing asCOUNT()
(adding 1 for every qualifying occurrence), but it is different fromCOUNT()
in that it does not count specific rows (within the scope of the cluster) unless theIF()
expression is satisfied. Another example.
Here is a db fiddle demo with some adjustments to your supplied sample data: https://www.db-fiddle.com/f/8MoWmKPuzTrrC3DQJsiX35/4 (The result set will only be "good" while the current is June of this year.)
After saving the above string as$sql
, you can simply execute it and loop through the array of objects like this:
foreach ($this->db->query($sql)->result() as $object) {
// these are the properties available in each object
// $object->id
// $object->firstname
// $object->lastname
// $object->leadsThisMonth
// $object->disbursedThisMonth
// $object->filesubmitThisMonth
}
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: php_network_getaddresses: getaddrinfo failed: temporary failure in name resolution
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.