sj,ddlmZGddZdS)) annotationsceZdZdZddZdS)BasePostgresPilotMixinz'Legacy employee work log queries mixin.returnstrcdS)z5SQL fallback query using the legacy pilot log source.u SELECT COALESCE(employee_code, CONCAT('EMP-', employee_id::text), 'EMP-UNKNOWN') AS employee_code, COALESCE(employee_name, 'Chưa cập nhật') AS employee_name, COALESCE(role_title, 'Chưa cập nhật') AS role_title, COALESCE(agent_role, role_group, 'Theo dõi pilot') AS agent_role, COALESCE(project_code, '') AS project_code, COALESCE(project_name, 'Chưa gán dự án') AS project_name, COALESCE(status, 'Đang theo dõi') AS status, COALESCE(TO_CHAR(shift_date, 'YYYY-MM-DD'), TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD')) AS shift_date, COALESCE(summary, 'Chưa có tóm tắt ca làm việc pilot.') AS summary, COALESCE(priority, 'Trung bình') AS priority, COALESCE(role_group, agent_role, 'Chưa phân nhóm') AS role_group, COALESCE(next_priority, 'Chưa xác định ưu tiên tiếp theo') AS next_priority, COALESCE(progress_percent, 0) AS progress_percent, COALESCE(completed_count, 0) AS completed_count, COALESCE(missing_count, 0) AS missing_count, COALESCE(findings::jsonb, '[]'::jsonb) AS findings, COALESCE(missing_items::jsonb, '[]'::jsonb) AS missing_items, COALESCE(notes::jsonb, '[]'::jsonb) AS notes, COALESCE(logs::jsonb, '[]'::jsonb) AS logs FROM ( SELECT base.employee_id, base.project_id, base.employee_code, base.employee_name, base.role_title, base.agent_role, base.project_code, base.project_name, base.status, base.shift_date, base.summary, base.priority, base.role_group, base.next_priority, base.progress_percent, base.completed_count, base.missing_count, summary_lists.findings, summary_lists.missing_items, summary_lists.notes, detail_logs.logs FROM ( SELECT DISTINCT ON (l.employee_id) l.employee_id, l.project_id, COALESCE(e.employee_code, CONCAT('EMP-', l.employee_id::text)) AS employee_code, COALESCE(e.full_name, 'Chưa cập nhật') AS employee_name, COALESCE(e.job_title, 'Chưa cập nhật') AS role_title, COALESCE(e.department, l.role_group, 'Theo dõi pilot') AS agent_role, COALESCE(p.project_code, '') AS project_code, COALESCE(p.project_name, 'Chưa gán dự án') AS project_name, COALESCE(l.status, 'Đang theo dõi') AS status, l.shift_date, COALESCE(l.summary, 'Chưa có tóm tắt ca làm việc pilot.') AS summary, COALESCE(l.priority, 'Trung bình') AS priority, COALESCE(l.role_group, 'Chưa phân nhóm') AS role_group, COALESCE(l.next_priority, 'Chưa xác định ưu tiên tiếp theo') AS next_priority, COALESCE(l.progress_percent, 0) AS progress_percent, COALESCE(l.completed_count, 0) AS completed_count, COALESCE(l.missing_count, 0) AS missing_count FROM employee_pilot_logs l LEFT JOIN employees e ON e.id = l.employee_id LEFT JOIN projects p ON p.id = l.project_id ORDER BY l.employee_id, l.shift_date DESC, l.action_timestamp DESC, l.created_at DESC, l.id DESC ) base LEFT JOIN LATERAL ( SELECT json_agg(DISTINCT finding.value) FILTER (WHERE finding.value IS NOT NULL) AS findings, json_agg(DISTINCT missing_item.value) FILTER (WHERE missing_item.value IS NOT NULL) AS missing_items, json_agg(DISTINCT note_item.value) FILTER (WHERE note_item.value IS NOT NULL) AS notes FROM employee_pilot_logs l LEFT JOIN LATERAL jsonb_array_elements_text( CASE WHEN jsonb_typeof(COALESCE(l.findings::jsonb, '[]'::jsonb)) = 'array' THEN COALESCE(l.findings::jsonb, '[]'::jsonb) ELSE '[]'::jsonb END ) AS finding(value) ON TRUE LEFT JOIN LATERAL jsonb_array_elements_text( CASE WHEN jsonb_typeof(COALESCE(l.missing_items::jsonb, '[]'::jsonb)) = 'array' THEN COALESCE(l.missing_items::jsonb, '[]'::jsonb) ELSE '[]'::jsonb END ) AS missing_item(value) ON TRUE LEFT JOIN LATERAL jsonb_array_elements_text( CASE WHEN jsonb_typeof(COALESCE(l.notes::jsonb, '[]'::jsonb)) = 'array' THEN COALESCE(l.notes::jsonb, '[]'::jsonb) ELSE '[]'::jsonb END ) AS note_item(value) ON TRUE WHERE l.employee_id = base.employee_id AND l.project_id = base.project_id AND l.shift_date = base.shift_date ) summary_lists ON TRUE LEFT JOIN LATERAL ( SELECT jsonb_agg( jsonb_build_object( 'timestamp', COALESCE(TO_CHAR(log_item.action_timestamp, 'HH24:MI'), ''), 'action', COALESCE(log_item.action, 'Cập nhật pilot'), 'ai_request', to_jsonb(log_item.ai_request), 'ai_result', CASE WHEN jsonb_typeof(COALESCE(log_item.ai_result::jsonb, '[]'::jsonb)) = 'array' THEN COALESCE(log_item.ai_result::jsonb, '[]'::jsonb) ELSE jsonb_build_array(log_item.ai_result::text) END, 'findings', CASE WHEN jsonb_typeof(COALESCE(log_item.findings::jsonb, '[]'::jsonb)) = 'array' THEN COALESCE(log_item.findings::jsonb, '[]'::jsonb) ELSE jsonb_build_array(log_item.findings::text) END, 'notes', CASE WHEN jsonb_typeof(COALESCE(log_item.notes::jsonb, '[]'::jsonb)) = 'array' THEN COALESCE(log_item.notes::jsonb, '[]'::jsonb) ELSE jsonb_build_array(log_item.notes::text) END ) ORDER BY log_item.shift_date DESC, log_item.action_timestamp DESC, log_item.created_at DESC, log_item.id DESC ) AS logs FROM employee_pilot_logs log_item WHERE log_item.employee_id = base.employee_id AND log_item.project_id = base.project_id AND log_item.shift_date = base.shift_date ) detail_logs ON TRUE ) aggregated ORDER BY employee_code r >C:\Projects\DSCons\app\core\postgres\base_pkg\pilot_queries.py_employee_pilot_logs_queryz1BasePostgresPilotMixin._employee_pilot_logs_querys| | r N)rr)__name__ __module__ __qualname____doc__r r r r rrs411~ ~ ~ ~ ~ ~ r rN) __future__rrr r r rsS""""""A A A A A A A A A A r