org_and_attend_q_a = [
    {
        "question": "XX中心员工在林芝工作的天数",
        "answer": '''
                  SELECT DISTINCT p."code"                                                   AS "工号",
                                  p."name"                                                   AS "姓名",

                                  COUNT(DISTINCT TO_CHAR(a."attendance_time", 'yyyy-MM-dd')) AS "在林芝工作天数"
                  FROM "YJOA_APPSERVICE_DB"."t_pr3rl2oj_yj_person_database" p
                           LEFT JOIN "YJOA_APPSERVICE_DB"."t_yj_person_attendance" a ON p."code" = a."person_id"
                           LEFT JOIN "YJOA_APPSERVICE_DB"."t_yj_person_ac_area" ac
                                     ON a."access_control_point" = ac."ac_point"
                  WHERE p."dr" = 0
                    AND a."dr" = 0
                    AND ac."region" = 5
                    AND p."internal_dept" in
                        (SELECT "id"
                         FROM "IUAP_APDOC_BASEDOC"."org_orgs" START
                  WITH "name"||"shortname" LIKE '%xx中心%' AND "dr"=0 AND "enable"=1 AND "code" LIKE '%CYJ%'
                  CONNECT BY PRIOR "id" = "parentid")
                  GROUP BY p."code", p."name"
                  ORDER BY "在林芝工作天数" DESC LIMIT 1000
                  ''',
        "tags": ["员工", "部门","考勤", "工作地", "区域", "天数"],
        "category": "工作地考勤统计分析"
    }, {
        "question": "xx中心10月有哪些人迟到/早退",
        "answer": '''
                  SELECT p."id"                                                               AS "id",
                         p."code"                                                             AS "code",
                         p."name"                                                             AS "name",
                         CASE WHEN ps."status" = '1006' THEN '迟到/早退' ELSE ps."status" END AS "status"
                  FROM "YJOA_APPSERVICE_DB"."t_yj_person_status" ps
                           INNER JOIN "YJOA_APPSERVICE_DB"."t_pr3rl2oj_yj_person_database" p
                                      ON p."code" = ps."person_id"
                  WHERE ps."status" = '1006'
                    AND ps."date_value" LIKE '2025-10%'
                    AND ps."dr" = 0
                    AND p."dr" = 0
                    AND p."internal_dept" IN (SELECT "id" FROM "IUAP_APDOC_BASEDOC"."org_orgs" START
                  WITH "name" || "shortname" LIKE '%xx中心%' AND "dr"=0 AND "enable"=1 AND "code" LIKE '%CYJ%'
                  CONNECT BY PRIOR "id" = "parentid")
                  ORDER BY ps."date_value" DESC LIMIT 1000
                  ''',
        "tags": ["员工","部门", "考勤", "迟到","早退"],
        "category": "考勤管理"
    },
    {
        "question": "xx中心10月有哪些人出差",
        "answer": '''
                  SELECT p."id"                                                          AS "id",
                         p."code"                                                        AS "code",
                         p."name"                                                        AS "name",
                         CASE WHEN ps."status" = '1002' THEN '出差' ELSE ps."status" END AS "status"
                  FROM "YJOA_APPSERVICE_DB"."t_yj_person_status" ps
                           INNER JOIN "YJOA_APPSERVICE_DB"."t_pr3rl2oj_yj_person_database" p
                                      ON p."code" = ps."person_id"
                  WHERE ps."status" = '1002'
                    AND ps."date_value" LIKE '2025-10%'
                    AND ps."dr" = 0
                    AND p."dr" = 0
                    AND p."internal_dept" IN (SELECT "id" FROM "IUAP_APDOC_BASEDOC"."org_orgs" START
                  WITH "name" || "shortname" LIKE '%xx中心%' AND "dr"=0 AND "enable"=1 AND "code" LIKE '%CYJ%'
                  CONNECT BY PRIOR "id" = "parentid")
                  ORDER BY ps."date_value" DESC LIMIT 1000
                  ''',
        "tags": ["员工", "部门", "考勤", "出差"],
        "category": "考勤管理"
    },
    {
        "question": "xx中心10月有哪些人旷工",
        "answer": '''
                  SELECT p."id"                                                          AS "id",
                         p."code"                                                        AS "code",
                         p."name"                                                        AS "name",
                         CASE WHEN ps."status" = '1002' THEN '旷工' ELSE ps."status" END AS "status"
                  FROM "YJOA_APPSERVICE_DB"."t_yj_person_status" ps
                           INNER JOIN "YJOA_APPSERVICE_DB"."t_pr3rl2oj_yj_person_database" p
                                      ON p."code" = ps."person_id"
                  WHERE ps."status" = '1005'
                    AND ps."date_value" LIKE '2025-10%'
                    AND ps."dr" = 0
                    AND p."dr" = 0
                    AND p."internal_dept" IN (SELECT "id" FROM "IUAP_APDOC_BASEDOC"."org_orgs" START
                  WITH "name" || "shortname" LIKE '%xx中心%' AND "dr"=0 AND "enable"=1 AND "code" LIKE '%CYJ%'
                  CONNECT BY PRIOR "id" = "parentid")
                  ORDER BY ps."date_value" DESC LIMIT 1000
                  ''',
        "tags": ["员工", "部门", "考勤", "矿工"],
        "category": "考勤管理"
    },
    {
        "question": "xx中心10月有哪些人请假",
        "answer": '''
                  SELECT p."id"                                                          AS "id",
                         p."code"                                                        AS "code",
                         p."name"                                                        AS "name",
                         CASE WHEN ps."status" = '1003' THEN '请假' ELSE ps."status" END AS "status"
                  FROM "YJOA_APPSERVICE_DB"."t_yj_person_status" ps
                           INNER JOIN "YJOA_APPSERVICE_DB"."t_pr3rl2oj_yj_person_database" p
                                      ON p."code" = ps."person_id"
                  WHERE ps."status" = '1005'
                    AND ps."date_value" LIKE '2025-10%'
                    AND ps."dr" = 0
                    AND p."dr" = 0
                    AND p."internal_dept" IN (SELECT "id" FROM "IUAP_APDOC_BASEDOC"."org_orgs" START
                  WITH "name" || "shortname" LIKE '%xx中心%' AND "dr"=0 AND "enable"=1 AND "code" LIKE '%CYJ%'
                  CONNECT BY PRIOR "id" = "parentid")
                  ORDER BY ps."date_value" DESC LIMIT 1000
                  ''',
        "tags": ["员工", "部门", "考勤", "请假"],
        "category": "考勤管理"
    },
    {
        "question": "xx中心，9月迟到次数多前三是哪几个员工",
        "answer": '''
                  SELECT p.name       AS "employee_name",
                         p.code       AS "employee_code",
                         o.name       AS "department_name",
                         COUNT(ps.id) AS "late_count"
                  FROM "YJOA_APPSERVICE_DB"."t_pr3rl2oj_yj_person_database" p
                           JOIN "IUAP_APDOC_BASEDOC"."org_orgs" o ON p.internal_dept = o.id
                           JOIN "YJOA_APPSERVICE_DB"."t_yj_person_status" ps ON p.code = ps.person_id
                  WHERE p.dr = 0
                    AND ps.dr = 0
                    AND ps.status = '1006'
                    AND ps.date_value LIKE '2025-09%'
                    AND o.id IN (SELECT id FROM "IUAP_APDOC_BASEDOC"."org_orgs" START
                  WITH (name LIKE '%xx中心%' OR shortname LIKE '%xx中心%') AND dr=0 AND enable=1
                  CONNECT BY PRIOR id = parentid)
                  GROUP BY p.name, p.code, o.name
                  ORDER BY "late_count" DESC LIMIT 3
                  ''',
        "tags": ["员工", "部门", "考勤", "迟到","次数","排行"],
        "category": "考勤管理"
    }, {
        "question": "xx中心员工10月打卡记录查询",
        "answer": '''
                  SELECT a."person_name"                                                                    AS "person_name",
                         p."code"                                                                           AS "employee_code",
                         a."attendance_time"                                                                AS "attendance_time",
                         a."attendance_address"                                                             AS "attendance_address",
                         CASE
                             WHEN a."status" = 1 THEN '在岗'
                             WHEN a."status" = 2 THEN '出差'
                             WHEN a."status" = 3
                                 THEN '休假' END                                                            AS "status_desc",
                         CASE
                             WHEN a."enter_or_exit" = 1 THEN '进'
                             WHEN a."enter_or_exit" = 2
                                 THEN '出' END                                                              AS "entry_exit",
                         o."name"                                                                           AS "department_name"
                  FROM "YJOA_APPSERVICE_DB"."t_yj_person_attendance" a
                           JOIN "YJOA_APPSERVICE_DB"."t_pr3rl2oj_yj_person_database" p ON a."person_id" = p."code"
                           JOIN "IUAP_APDOC_BASEDOC"."org_orgs" o ON p."internal_dept" = o."id"
                  WHERE a."dr" = 0
                    AND p."dr" = 0
                    AND a."attendance_time" >= '2025-10-01'
                    AND a."attendance_time" < '2025-11-01'
                    AND o."id" IN (SELECT o2."id" FROM "IUAP_APDOC_BASEDOC"."org_orgs" o2
                  START WITH (o2."name" LIKE '%xx中心%'
                          OR o2."shortname" LIKE '%xx中心%')
                         AND o2."dr" = 0
                         AND o2."enable" = 1
                  CONNECT BY PRIOR o2."id" = o2."parentid")
                  ORDER BY a."attendance_time" DESC LIMIT 1000
                  ''',
        "tags": ["员工", "部门","考勤", "打卡记录", "明细查询"],
        "category": "考勤管理"
    }, {
        "question": "xx中心员工10月考勤查询",
        "answer": '''
                  SELECT p."code"                 AS "工号",
                         p."name"                 AS "姓名",
                         o."name"                 AS "部门名称",
                         ps."date_value"          AS "日期",
                         CASE
                             WHEN ps."status" = '1001' THEN '在岗'
                             WHEN ps."status" = '1002' THEN '出差'
                             WHEN ps."status" = '1003' THEN '休假,请假'
                             WHEN ps."status" = '1005' THEN '旷工'
                             WHEN ps."status" = '1006' THEN '迟到,早退'
                             WHEN ps."status" = '1007' THEN '休息日'
                             WHEN ps."status" = '4001' THEN 'am在岗pm缺勤'
                             WHEN ps."status" = '4002' THEN 'am缺勤pm在岗'
                             WHEN ps."status" = '6001' THEN 'am在岗pm早退'
                             WHEN ps."status" = '6002' THEN 'am迟到pm在岗'
                             WHEN ps."status" = '6004' THEN 'am迟到pm缺勤'
                             WHEN ps."status" = '4006' THEN 'am缺勤pm早退'
                             ELSE ps."status" END AS "人员状态"
                  FROM "YJOA_APPSERVICE_DB"."t_yj_person_status" ps
                           JOIN "YJOA_APPSERVICE_DB"."t_pr3rl2oj_yj_person_database" p ON ps."person_id" = p."code"
                           JOIN "IUAP_APDOC_BASEDOC"."org_orgs" o ON p."internal_dept" = o."id"
                  WHERE o."id" IN (SELECT id FROM "IUAP_APDOC_BASEDOC"."org_orgs" START
                  WITH ("name" LIKE '%xx中心%' OR "shortname" LIKE '%xx中心%') AND "enable" = 1 AND "dr" = 0 AND "code" LIKE '%CYJ%'
                  CONNECT BY PRIOR "id" = "parentid") AND ps."date_value" LIKE '2025-10%' AND ps."dr" = 0 AND p."dr" = 0
                  ORDER BY ps."date_value" DESC LIMIT 1000
                  ''',
        "tags": ["员工", "部门", "考勤状态", "明细查询"],
        "category": "考勤管理"
    },{
        "question": "10月迟到人数最多的部门",
        "answer": '''
                         SELECT o."name" AS "部门名称", COUNT(DISTINCT ps."person_id") AS "迟到人数"
                         FROM "YJOA_APPSERVICE_DB"."t_yj_person_status" ps
                                  JOIN "YJOA_APPSERVICE_DB"."t_pr3rl2oj_yj_person_database" p
                                       ON ps."person_id" = p."code"
                                  JOIN "IUAP_APDOC_BASEDOC"."org_orgs" o ON p."internal_dept" = o."id"
                         WHERE ps."status" = '1006'
                           AND ps."date_value" LIKE '2025-10%'
                           AND ps."dr" = 0
                           AND o."dr" = 0
                           AND o."enable" = 1
                           AND o."code" LIKE '%CYJ%'
                         GROUP BY o."name"
                         ORDER BY COUNT(DISTINCT ps."person_id") DESC LIMIT 1
                         ''',
        "tags": ["员工", "部门", "考勤", "迟到","人数","最值"],
        "category": "考勤管理"
    },
    {
        "question": "XX中心在藏最长时间的人是谁",
        "answer": '''
                  SELECT p."name" AS "姓名", p."code" AS "工号", COUNT(ps."id") AS "在藏天数"
                  FROM YJOA_APPSERVICE_DB."t_yj_person_status" ps
                           JOIN YJOA_APPSERVICE_DB."t_pr3rl2oj_yj_person_database" p ON ps."person_id" = p."code"
                  WHERE ps."is_in_tibet" = 1
                    AND ps."dr" = 0
                    AND p."dr" = 0
                    and p.internal_dept in (SELECT "id"
                                            FROM "IUAP_APDOC_BASEDOC"."org_orgs" START
                  WITH "name"||"shortname" LIKE '%xx中心%' AND "dr"=0 AND "enable"=1 AND "code" LIKE '%CYJ%'
                  CONNECT BY PRIOR "id" = "parentid"
                      )
                  GROUP BY p."name", p."code"
                  ORDER BY COUNT (ps."id") DESC LIMIT 1
                  '''
    },

]