<?php
namespace web\ambap;

global $db;

class Project {
    static function setwhere($db, $post) {
        $query = $post->get('query');
        $csql = new \ciy\sql('lab_rproject');

        if (isset($query['name']) && !empty(trim($query['name']))) {
            $csql->where('name LIKE ?', '%' . trim($query['name']) . '%');
        }
        if (isset($query['rcate']) && $query['rcate'] > 0) {
            $csql->where('rcate = ?', $query['rcate']);
        }
        if (isset($query['rprojectstatus']) && $query['rprojectstatus'] > 0) {
            $csql->where('rprojectstatus = ?', $query['rprojectstatus']);
        }
        if (isset($query['direction']) && $query['direction'] > 0) {
            $csql->where('direction = ?', $query['direction']);
        }
    
        $liid = $post->getint('liid', 0);
        if ($liid > 0) {
            $csql->where('rcate = ?', $liid);
        }

        $csql->order('recordtime DESC');
        return [$query, $csql];
    }

    public static function json_list() {
        global $db;
        $rsuser = verifyfast();
        $post = new \ciy\post();
    
        list($where, $csql) = self::setwhere($db, $post);

        $pageno = $post->getint('pageno', 1);
        $pagecount = $post->getint('pagecount', 10);
      

        try {
            $countSqlObj = new \ciy\sql($csql->table);
            $countSqlObj->column('COUNT(*) as cnt');
            $countSqlObj->where($csql->where, $csql->tsmt);
            $countSqlObj->order('');
            $countSqlObj->selecttail('');
          


            $countResult = $db->getone($countSqlObj);
            $total = $countResult['cnt'];
            

            $listSql = new \ciy\sql('lab_rproject p');
            $listSql->column('p.*, (
    SELECT GROUP_CONCAT(u.name SEPARATOR ", ")
    FROM lab_projectmember pm_sub
    LEFT JOIN lab_user u ON pm_sub.uid = u.id
    WHERE pm_sub.pid = p.id
) AS member_names');
            //$listSql->column('p.*');
            $listSql->where($csql->where, $csql->tsmt);
            //$listSql->selecttail('LEFT JOIN lab_projectmember pm ON p.id = pm.pid');
            //$listSql->selecttail('LEFT JOIN lab_user u ON pm.uid = u.id');
            //$listSql->selecttail('GROUP BY p.id');
            $listSql->order($csql->order);
            $listSql->limit($pageno, $pagecount);
         


            $rows = $db->get($listSql);
            // --- 修复：确保 $rows 是一个数组 ---
        if ($rows === false) {
            $rows = [];
        }
        $list_count = count($rows);
            $ret = [
                'searchwhere' => $where,
                'pageno' => $pageno,
                'pagecount' => $pagecount,
                'count' => $total,
                'list' => $rows
            ];
            $ret['debug_console'] = [
                'raw_db_count' => $total,
                'raw_db_list_count' => $list_count,
                'sql_where_clause' => $csql->where,
                'sql_params' => $csql->tsmt,
                'sql_order_clause' => $csql->order,
            ];
            
            if ($post->getbool('once')) {
                $ret['once'] = true;
                $ret['lis'] = getcatas($db, 'rcate');
                $input = [];
                $input[] = [
                    'type' => 'input',
                    'form' => 'name',
                    'name' => '课题标题',
                    'prop' => ' style="width:10em;"'
                ];
                $input[] = [
                    'type' => 'select',
                    'form' => 'rprojectstatus',
                    'name' => '项目状态',
                    'select' => 'rprojectstatus',
                    'all' => '全部'
                ];
                $ret['searchinput'] = $input;
            }

            $orgSql = new \ciy\sql('lab_orgbase');
            $orgList = $db->get(csql: $orgSql);
            $ret['laborg'] = $orgList ?: [];

            return succjson($ret);
            
        } catch (\Exception $e) {
            return errjson('查询列表失败：' . $e->getMessage());
        }
        
    }


        public static function json_addinit() {
        global $db;
        $rsuser = verifyfast();
        $post = new \ciy\post();
        $id = $post->getint('id');
        if ($post->get('act') == 'edit') {
    
            $userSql = new \ciy\sql('lab_user');
            $userList = $db->get($userSql);
            $ret['users'] = $userList ?: [];
            
            $orgSql = new \ciy\sql('lab_orgbase');
            $orgList = $db->get($orgSql);
            $ret['orgs'] = $orgList ?: [];

            $memberSql = new \ciy\sql('lab_projectmember m');
            $memberSql->column('m.id as mid, m.pid, m.uid, u.name AS uname, m.prole, m.ctime, m.utime')
                      ->where('m.pid = ?', $id)
                      ->selecttail('LEFT JOIN lab_user u ON m.uid = u.id');
            $members = $db->get($memberSql);
            $ret['members'] = $members ?: [];

            $ret['rcate'] = getcatas($db, 'rcate');
            $ret['rprojectstatus'] = getcatas($db, 'rprojectstatus');
            $ret['direction'] = getcatas($db, 'direction');
            $ret['isweb'] = getcatas($db, 'isweb');
            $ret['mdeptlevel'] = getcatas($db, 'mdeptlevel');
            $ret['prole'] = getcatas($db, 'prole');
            $ret['principal_id']=getcatas($db,'principal_id');
        }
            return succjson($ret);
    }

    public static function json_info() {
        global $db;
        $post = new \ciy\post();
        $id = $post->getint('id');
        if ($id <= 0) {
            return errjson('请传入有效的项目ID');
        }
        
        $csql = new \ciy\sql('lab_rproject');
        $csql->where('id = ?', $id);
        $row = $db->getone($csql);
        if (!is_array($row)) {
            return errjson('项目数据不存在');
        }

        $ret['data'] = $row;

  
    
            $userSql = new \ciy\sql('lab_user');
            $userList = $db->get($userSql);
            $ret['users'] = $userList ?: [];

            $orgSql = new \ciy\sql('lab_orgbase');
            $orgList = $db->get($orgSql);
            $ret['orgs'] = $orgList ?: [];
// --- 修改后的代码：使用 getraw 执行原始 SQL ---
$members = $db->getraw(
    "SELECT m.id as mid, m.pid, m.uid, u.name as uname, m.prole, m.ctime, m.utime 
     FROM lab_projectmember m 
     LEFT JOIN lab_user u ON m.uid = u.id 
     WHERE m.pid = ?", 
    [$id]
);
// 将结果赋值给 $ret 数组
$ret['members'] = $members ?: [];
// --- 修改结束 ---

            $ret['rcate'] = getcatas($db, 'rcate');
            $ret['rprojectstatus'] = getcatas($db, 'rprojectstatus');
            $ret['direction'] = getcatas($db, 'direction');
            $ret['isweb'] = getcatas($db, 'isweb');
            $ret['mdeptlevel'] = getcatas($db, 'mdeptlevel');
            $ret['prole'] = getcatas($db, 'prole');
        return succjson($ret);
    }

    public static function json_add() {
        global $db;
        $rsuser = verifyfast();
        $post = new \ciy\post();
        $recordno = $post->get('recordno');
        $laborgid = $post->getint('laborgid', 0);
        $isweb = $post->getint('isweb', 0);
        $rcate = $post->getint('rcate', 0);
        $name = $post->get('name');
        $descs = $post->get('descs');
        $mdeptname = $post->get('mdeptname');
        $mdeptlevel = $post->getint('mdeptlevel', 0);
        $budgetmoney = $post->getint('budgetmoney', 0);
        $serviceorg = $post->get('serviceorg');
        $principal_id = $post->getint('principal_id', 0);
        $rprojectstatus = $post->getint('rprojectstatus', 0);
        $progress = $post->getint('progress', 0);
        $lasttimes = $post->getint('lasttimes', 0);
        $latestcontent = $post->get('latestcontent');
        $latestattachment = $post->get('latestattachment');
        $direction = $post->getint('direction', 0);

        if (empty($name)) {
            return errjson('课题标题不能为空');
        }
        if (empty($recordno)) {
            return errjson('备案编号不能为空');
        }
        if ($laborgid > 0) {
            $checkOrgSql = new \ciy\sql('lab_orgbase');
            $checkOrgSql->where('id = ?', $laborgid);
            if (!$db->getone($checkOrgSql)) {
                return errjson('所选机构不存在');
            }
        }
        if ($principal_id > 0) {
            $checkUserSql = new \ciy\sql('lab_user');
            $checkUserSql->where('id = ?', $principal_id);
            if (!$db->getone($checkUserSql)) {
                return errjson('所选负责人不存在');
            }
        }

        $currentTime = time();
        $insertData = [
            'recordtime' => $currentTime,
            'recordno' => $recordno,
            'laborgid' => $laborgid,
            'orgname' => '', 
            'isweb' => $isweb,
            'rcate' => $rcate,
            'name' => $name,
            'descs' => $descs,
            'mdeptname' => $mdeptname,
            'mdeptlevel' => $mdeptlevel,
            'budgetmoney' => $budgetmoney,
            'serviceorg' => $serviceorg,
            'principal_id' => $principal_id,
            'principal_name' => '', 
            'rprojectstatus' => $rprojectstatus,
            'progress' => $progress,
            'lasttimes' => $lasttimes,
            'latestcontent' => $latestcontent,
            'latestattachment' => $latestattachment,
            'direction' => $direction
        ];

        try {
            $db->begin();

            $projectSql = new \ciy\sql('lab_rproject');
            if ($db->insert($projectSql, $insertData) === false) {
                throw new \Exception('新增项目失败: ' . $db->error);
            }
            $projectId = $db->insert_id();

            if ($laborgid > 0) {
                $orgSql = new \ciy\sql('lab_orgbase');
                $orgSql->where('id = ?', $laborgid);
                $orgInfo = $db->getone($orgSql);
                if ($orgInfo) {
                    $updateOrgNameSql = new \ciy\sql('lab_rproject');
                    $updateOrgNameSql->where('id = ?', $projectId);
                    $db->update($updateOrgNameSql, [
                        'orgname' => $orgInfo['name'],
                        'orgshortname' => $orgInfo['shortname'] ?? $orgInfo['name']
                    ]);
                }
            }
            if ($principal_id > 0) {
                $userSql = new \ciy\sql('lab_user');
                $userSql->where('id = ?', $principal_id);
                $userInfo = $db->getone($userSql);
                if ($userInfo) {
                    $updatePrincipalNameSql = new \ciy\sql('lab_rproject');
                    $updatePrincipalNameSql->where('id = ?', $projectId);
                    $db->update($updatePrincipalNameSql, [
                        'principal_name' => $userInfo['name']
                    ]);
                }
            }
 
            if ($principal_id > 0) {
                 $memberInsertData = [
                     'pid' => $projectId,
                     'uid' => $principal_id,
                     'prole' => 10, 
                     'ctime' => $currentTime,
                     'utime' => $currentTime
                 ];
                 $memberSql = new \ciy\sql('lab_projectmember');
                 if ($db->insert($memberSql, $memberInsertData) === false) {
                     throw new \Exception('添加项目负责人到成员列表失败: ' . $db->error);
                 }
            }

            $db->commit();

            $insertData['id'] = $projectId;
            savelogdb($db, $rsuser['id'], 'lab_rproject', null, $insertData);

            return succjson(['id' => $projectId, 'name' => $name]);

        } catch (\Exception $e) {
            $db->rollback();
            return errjson($e->getMessage());
        }
    }

    public static function json_update() {
        global $db;
        $rsuser = verifyfast();
        $post = new \ciy\post();

        $id = $post->getint('id');
        if ($id <= 0) {
            return errjson('请传入有效的项目ID');
        }
        $csql = new \ciy\sql('lab_rproject');
        $csql->where('id = ?', $id);
        $originalData = $db->getone($csql);
        if (!is_array($originalData)) {
            return errjson('项目数据不存在');
        }

        $recordno = $post->get('recordno');
        $laborgid = $post->getint('laborgid', 0);
        $isweb = $post->getint('isweb', 0);
        $rcate = $post->getint('rcate', 0);
        $name = $post->get('name');
        $descs = $post->get('descs');
        $mdeptname = $post->get('mdeptname');
        $mdeptlevel = $post->getint('mdeptlevel', 0);
        $budgetmoney = $post->getint('budgetmoney', 0);
        $serviceorg = $post->get('serviceorg');
        $principal_id = $post->getint('principal_id', 0);
        $rprojectstatus = $post->getint('rprojectstatus', 0);
        $progress = $post->getint('progress', 0);
        $lasttimes = $post->getint('lasttimes', 0);
        $latestcontent = $post->get('latestcontent');
        $latestattachment = $post->get('latestattachment');
        $direction = $post->getint('direction', 0);

        $newMembers = $post->get('members', []);

        if (empty($name)) {
            return errjson('课题标题不能为空');
        }
        if (empty($recordno)) {
            return errjson('备案编号不能为空');
        }
        if ($laborgid > 0) {
            $checkOrgSql = new \ciy\sql('lab_orgbase');
            $checkOrgSql->where('id = ?', $laborgid);
            if (!$db->getone($checkOrgSql)) {
                return errjson('所选机构不存在');
            }
        }
        if ($principal_id > 0) {
            $checkUserSql = new \ciy\sql('lab_user');
            $checkUserSql->where('id', $principal_id);
            if (!$db->getone($checkUserSql)) {
                return errjson('所选负责人不存在');
            }
        }
        $rawProleOptions = getcatas($db, 'prole');
        $validProleIds = array_column($rawProleOptions, 'id');
    
        foreach ($newMembers as $member) {
            $prole = $member['prole'] ?? 10;

            if (!in_array($prole, $validProleIds)) {
                return errjson("成员角色ID无效: $prole");
            }
        }

        $updateData = [
            'recordno' => $recordno,
            'laborgid' => $laborgid,
            'isweb' => $isweb,
            'rcate' => $rcate,
            'name' => $name,
            'descs' => $descs,
            'mdeptname' => $mdeptname,
            'mdeptlevel' => $mdeptlevel,
            'budgetmoney' => $budgetmoney,
            'serviceorg' => $serviceorg,
            'principal_id' => $principal_id,
            'rprojectstatus' => $rprojectstatus,
            'progress' => $progress,
            'lasttimes' => $lasttimes,
            'latestcontent' => $latestcontent,
            'latestattachment' => $latestattachment,
            'direction' => $direction
        ];

        if ($laborgid > 0) {
            $orgSql = new \ciy\sql('lab_orgbase');
            $orgSql->where('id = ?', $laborgid);
            $orgInfo = $db->getone($orgSql);
            if ($orgInfo) {
                $updateData['orgname'] = $orgInfo['name'];
            } else {
                $updateData['orgname'] = null;
            }
        } else {
            $updateData['orgname'] = null;
        }

        if ($principal_id > 0) {
            $userSql = new \ciy\sql('lab_user');
            $userSql->where('id = ?', $principal_id);
            $userInfo = $db->getone($userSql);
            if ($userInfo) {
                $updateData['principal_name'] = $userInfo['name'];
            } else {
                $updateData['principal_name'] = null;
            }
        } else {
            $updateData['principal_name'] = null;
        }


        try {
            $db->begin();

            $updateSql = new \ciy\sql('lab_rproject');
            $updateSql->where('id = ?', $id);
            if ($db->update($updateSql, $updateData) === false) {
                throw new \Exception('更新项目基本信息失败: ' . $db->error);
            }
            if ($id > 0) {
                $currentMemberSql = new \ciy\sql('lab_projectmember');
                $currentMemberSql->where('pid = ?', $id);
                foreach ($newMembers as $member) {
                    $mid = $member['mid'] ?? 0; 
                    $uid = $member['uid'] ?? 0; 
                    $prole = $member['prole'] ?? 10; 
                    $isNew = $member['isNew'] ?? false; 
                    $isDeleted = $member['isDeleted'] ?? false;

                    if ($isDeleted) {
                        if ($mid > 0) {
                             $delSql = new \ciy\sql('lab_projectmember');
                             $delSql->where('id = ?', $mid);
                             if ($db->delete($delSql) === false) {
                                 throw new \Exception("删除成员失败 (ID: $mid): " . $db->error);
                             }
                        }
                    } elseif ($uid > 0) { 
                        if ($isNew) {
                            // 对于新成员，先检查是否已存在 ---
                            $checkSql = new \ciy\sql('lab_projectmember');
                            $checkSql->where('pid = ? AND uid = ?', [$id, $uid]);
                            $existingRecord = $db->getone($checkSql);

                            if ($existingRecord) {
                                $updateMemberData = [
                                     'prole' => $prole,
                                     'utime' => time()
                                ];
                                $updateSql = new \ciy\sql('lab_projectmember');
                                $updateSql->where('pid = ? AND uid = ?', [$id, $uid]); 
                                if ($db->update($updateSql, $updateMemberData) === false) {
                                     throw new \Exception("更新成员信息失败 (PID: $id, UID: $uid): " . $db->error);
                                }
                                $incomingMemberIds[] = $existingRecord['id']; 
                            } else {
                                $insertMemberData = [
                                     'pid' => $id,
                                     'uid' => $uid,
                                     'prole' => $prole, 
                                     'ctime' => time(),
                                     'utime' => time()
                                 ];
                                 $insertSql = new \ciy\sql('lab_projectmember');
                                 if ($db->insert($insertSql, $insertMemberData) === false) {
                                     throw new \Exception("添加新成员失败: " . $db->error);
                                 }
                                 $newlyInsertedId = $db->insert_id();
                                 $incomingMemberIds[] = $newlyInsertedId;
                            }
                        } else {
                            if ($mid > 0) {
                                $updateMemberData = [
                                     'uid' => $uid,
                                     'prole' => $prole,
                                     'utime' => time()
                                ];
                                $updateSql = new \ciy\sql('lab_projectmember');
                                $updateSql->where('id = ?', $mid);
                                if ($db->update($updateSql, $updateMemberData) === false) {
                                     throw new \Exception("更新成员信息失败 (ID: $mid): " . $db->error);
                                }
                                $incomingMemberIds[] = $mid;
                            }
                        }
                    }
                }
            }

            $db->commit();
            savelogdb($db, $rsuser['id'], 'lab_rproject', $originalData, $updateData);

            $finalSql = new \ciy\sql('lab_rproject');
            $finalSql->where('id = ?', $id);
            $finalRow = $db->getone($finalSql);

            return succjson(['data' => $finalRow]);

        } catch (\Exception $e) {
            $db->rollback();
            return errjson($e->getMessage());
        }
    }

    public static function json_del() {
        global $db;
        $rsuser = verifyfast();
        $post = new \ciy\post();
        $id = $post->getint('id');

        $csql = new \ciy\sql('lab_rproject');
        $csql->where('id = ?', $id);
        $row = $db->getone($csql);
        if (!is_array($row)) {
            return errjson('项目数据不存在');
        }

        try {
            $db->begin();
            $delMemberSql = new \ciy\sql('lab_projectmember');
            $delMemberSql->where('pid = ?', $id);
            if ($db->delete($delMemberSql) === false) {
                throw new \Exception('删除项目成员关联失败:' . $db->error);
            }

            delme($db, $row['id'], 'lab_rproject');

            savelogdb($db, $rsuser['id'], 'lab_rproject', $row, null);

            $db->commit();
        } catch (\Exception $ex) {
            $db->rollback();
            return errjson($ex->getMessage());
        }

        return succjson();
    }
}
?>
