sql

SELECT
    COUNT(wo_list.wo_id) AS count_wo_id,
    admin_employee.employee_name,
    admin_employee.employee_email
FROM
    wo_list
INNER JOIN wo_list_employee ON wo_list.wo_id = wo_list_employee.wo_id
INNER JOIN admin_employee ON wo_list_employee.employee_id = admin_employee.employee_id
WHERE
    wo_list.wo_target_time <> '' AND TIMESTAMPDIFF(
        HOUR,
        NOW(), wo_list.wo_target_time) <= 24 AND wo_list.wo_status < 6
    GROUP BY
        admin_employee.employee_name

 

 

 

SELECT
    COUNT(wo_list.wo_id),
    wo_list.wo_id,
    wo_list.wo_name,
    asset_list.asset_code,
    asset_list.asset_name,
    asset_location.location_code,
    asset_location.location_name,
    wo_history.wo_responsible_name,
    wo_list.wo_target_time,
    wo_list.wo_status
    
FROM
    wo_list
INNER JOIN wo_list_employee ON wo_list.wo_id = wo_list_employee.wo_id
INNER JOIN admin_employee ON wo_list_employee.employee_id = admin_employee.employee_id
INNER JOIN asset_list ON wo_list.wo_responsible_id = asset_list.asset_responsible_id
INNER JOIN asset_location ON asset_location.location_id = asset_list.location_id
INNER JOIN wo_history ON wo_history.wo_id = wo_list.wo_id
WHERE
    wo_list.wo_target_time <> '' AND TIMESTAMPDIFF(
        HOUR,
        NOW(), wo_list.wo_target_time) <= 24 AND wo_list.wo_status < 6

// 这一步是获取工单延迟的wo_id
$query = $DB->prepare("
    SELECT wo_id
    FROM
    wo_list
    WHERE wo_target_time <> ''
    AND TIMESTAMPDIFF(HOUR, NOW(), wo_target_time) <= 24 
    AND wo_status < 6
");

if (!$query->execute()) {
    Flight::error(new RuntimeException(errorInfo($inquiry)));
} elseif ($query->rowcount() == 0) {
    Flight::notFound();
}
$items=[];
// 现在是获取过期的wo_id（工单号）
while ($row = $query->fetch()) {
    $items[] =  $row->wo_id;
}
// 现在是获取到延迟的工单ID
  $string = implode(',', $items);
// 获取到的工单ID，去wo_list_employee 中获取employee_id
$inquiry = $DB->prepare("
        SELECT  employee_id
        ,wo_id
        FROM
        wo_list_employee
        WHERE
        wo_id IN(
        $string
        )
");

if (!$inquiry->execute()) {
    Flight::error(new RuntimeException(errorInfo($inquiry)));
} elseif ($inquiry->rowCount() == 0) {
    Flight::notFound();
} else {
    $inquiry->execute();
    while ($row = $inquiry->fetch(PDO::FETCH_OBJ)) {
        $emloyee_items[] =  $row->employee_id;
    }
}
 $unique_emloyee_items=array_unique($emloyee_items);

// 现在是获取到延迟的工单的员工ID
$emloyee_string = implode(',', $unique_emloyee_items);
// 根据获取到的员工ID 去找员工发送的邮件信息,员工的姓名以及员工的邮箱
$emloyee_inquiry = $DB->prepare("
        SELECT  employee_name,employee_email
        FROM
        admin_employee
        WHERE
        employee_id IN(
        $emloyee_string
        )
");

if (!$emloyee_inquiry->execute()) {
    Flight::error(new RuntimeException(errorInfo($inquiry)));
} elseif ($emloyee_inquiry->rowCount() == 0) {
    Flight::notFound();
} else {
    $emloyee_inquiry->execute();
    while ($row = $emloyee_inquiry->fetch(PDO::FETCH_OBJ)) {
      //$emloyee_email_items[] =  $row->employee_name;
      //$emloyee_email_items[] =  $row->employee_email;
        $row->employee_name =  $row->employee_name;
        $row->employee_email =  $row->employee_email;
        $emloyee_email_items[] = $row;
    }
}
// 这里现在是获取到了员工的邮件发送的地址以及员工的姓名
print_r($emloyee_email_items);
die;
// 在根据进行表关联

//send_email();

  
 // 用获取到的工单ID，去wo_list_employee 获取员工的id
 

// $employee_id = [];

// while ($row = $query->fetch()) {
//     $employee_id[] = $row->employee_id;
// }
// // 数组分割成字符串
// $string = implode(',', $employee_id);