如何查询员工详细信息并关联他们的绩效指标? [英] How to query employee details and relate their performance metrics?

问题描述

我正在获取所有已批准但未存档的员工的ID，名字和姓氏.然后，我循环这些结果，并使用ID查询其他表以收集一些计数数据.

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看起来像这样:

Array (
    [0] => stdClass Object (
        [id] => 1
        [firstname] => xyz
        [lastname] => xyz
    )
    ...
)

第二个查询输出:

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
        )
    )
    ...
)

或此结构:

Array (
    [0] => stdClass Object (
        [id] => 1
        [firstname] => xyz
        [lastname] => xyz
        [fallowCall] => 0
        [confirmCount] => 0
        [disbursed] => 0
        [filesubmit] => 0
    )
    ...
)

我在这里添加了我的表结构和一些示例数据: https://www.db-fiddle.com/f/8MoWmKPuzTrrC3DQJsiX35/0

I have added the my table structure and some sample data here: https://www.db-fiddle.com/f/8MoWmKPuzTrrC3DQJsiX35/0

此处的一些注释

1)createdby是表tbl_employee

2)库表中的lead_id是表tbl_lead

3)tbl_fileStatus中的f_bankid是表tbl_bankdata

推荐答案

实际上，不必为了保存计数数据而创建其他深度/复杂度.此外，通过结合使用LEFT JOIN来连接相关表并应用所需的条件规则，只需访问数据库一次即可达到所需的结果.毫无疑问，这将为您的应用程序提供卓越的效率.左联接非常重要，因此计数可以为零而不会从结果集中排除员工.

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.

此外，我应该指出，您尝试查询的是错误地将MONTH()值与DATE()值进行了比较-永远不会结束. :)实际上，为了确保您的sql能够准确地将当前月份与当前年份隔离开，您还需要检查YEAR值.

Also, I should point out that your attempted query was mistakenly comparing a MONTH() value against a DATE() 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.

我推荐的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

SUM(IF())表达式是一种用于执行条件计数"的技术. 聚合数据"是通过使用GROUP BY形成的，并且有专门的"fileStatus数据由于GROUP BY调用而有效地堆积在自身上.如果调用COUNT(fileStatus.f_filestatus)，它将计算集群中的所有行.由于您希望区分f_filestatus = 1和f_filestatus = 2，因此使用IF()语句.这样做与COUNT()相同(对于每个符合条件的事件加1)，但与COUNT()的不同之处在于，除非IF()表达式为使满意. 另一个示例.

The SUM(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. If COUNT(fileStatus.f_filestatus) was called, it would count all of the rows in the cluster. Since you wish to differentiate between f_filestatus = 1 and f_filestatus = 2, an IF() statement is used. This is doing the same thing as COUNT() (adding 1 for every qualifying occurrence), but it is different from COUNT() in that it does not count specific rows (within the scope of the cluster) unless the IF() expression is satisfied. Another example.

这是db小提琴演示，其中对您提供的示例数据进行了一些调整: https://www.db-fiddle.com/f/8MoWmKPuzTrrC3DQJsiX35/4 (结果集将是好"，而当前是今年6月.)

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.)

将上面的字符串另存为$sql后，您可以简单地执行它并遍历对象数组，如下所示:

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
}

这篇关于如何查询员工详细信息并关联他们的绩效指标?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！