Home Explore Help
Register Sign In
java
/
mrqc-sys
2
0
Fork 0
Files Issues 0 Pull Requests 0 Wiki
Tree: a7a0192728
Branches Tags
mrqc-sys
/
doc
/
017.20200716v1.3.8
/
qc_inti_check.sql

qc_inti_check.sql 12 KB
History Raw

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135 
  1. use `qc`;
    2. 

  2. ALTER TABLE `med_check_info` ADD COLUMN `check_type` INT(11) NULL COMMENT '核查类型（0病历，1病案首页）' AFTER `status`;
    3. 

  3. UPDATE med_check_info a SET check_type =0;
    4. 

  4. UPDATE med_check_info a SET check_name = (SELECT linkman FROM sys_user b WHERE a.check_id=b.id) inkman FROM sys_user b WHERE a.check_id=b.id);
    5. 

  5. 

  6. 

  7. DELETE FROM sys_user_pageset WHERE user_id = -1;
    8. 

  8. 

  9. INSERT INTO `sys_user_pageset` (  `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `user_id`, `page_type`, `name`, `val`, `status`, `order_no`, `remark`) VALUES (  'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '-1', '1', '病历等级', 'level', '1', '1', NULL);
    10. 

  10. INSERT INTO `sys_user_pageset` (  `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `user_id`, `page_type`, `name`, `val`, `status`, `order_no`, `remark`) VALUES ( 'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '-1', '1', '病历得分', 'scoreRes', '1', '2', NULL);
    11. 

  11. INSERT INTO `sys_user_pageset` (  `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `user_id`, `page_type`, `name`, `val`, `status`, `order_no`, `remark`) VALUES (  'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '-1', '1', '病人住院序号', 'behospitalCode', '1', '3', NULL);
    12. 

  12. INSERT INTO `sys_user_pageset` (  `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `user_id`, `page_type`, `name`, `val`, `status`, `order_no`, `remark`) VALUES (  'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '-1', '1', '病案首页核查状态', 'mrStatus', '1', '4', NULL);
    13. 

  13. INSERT INTO `sys_user_pageset` (  `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `user_id`, `page_type`, `name`, `val`, `status`, `order_no`, `remark`) VALUES (  'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '-1', '1', '病案首页核查人', 'mrName', '1', '5', NULL);
    14. 

  14. INSERT INTO `sys_user_pageset` (  `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `user_id`, `page_type`, `name`, `val`, `status`, `order_no`, `remark`) VALUES ( 'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '-1', '1', '病案首页核查时间', 'mrTime', '1', '6', NULL);
    15. 

  15. INSERT INTO `sys_user_pageset` (  `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `user_id`, `page_type`, `name`, `val`, `status`, `order_no`, `remark`) VALUES (  'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '-1', '1', '病历核查状态', 'checkStatus', '1', '7', NULL);
    16. 

  16. INSERT INTO `sys_user_pageset` (  `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `user_id`, `page_type`, `name`, `val`, `status`, `order_no`, `remark`) VALUES (  'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '-1', '1', '病历核查人', 'chName', '1', '8', NULL);
    17. 

  17. INSERT INTO `sys_user_pageset` (  `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `user_id`, `page_type`, `name`, `val`, `status`, `order_no`, `remark`) VALUES (  'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '-1', '1', '病历核查时间', 'chTime', '1', '9', NULL);
    18. 

  18. INSERT INTO `sys_user_pageset` (  `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `user_id`, `page_type`, `name`, `val`, `status`, `order_no`, `remark`) VALUES (  'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '-1', '1', '病案号', 'fileCode', '1', '10', NULL);
    19. 

  19. INSERT INTO `sys_user_pageset` (  `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `user_id`, `page_type`, `name`, `val`, `status`, `order_no`, `remark`) VALUES (  'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '-1', '1', '病人姓名', 'name', '1', '11', NULL);
    20. 

  20. INSERT INTO `sys_user_pageset` (  `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `user_id`, `page_type`, `name`, `val`, `status`, `order_no`, `remark`) VALUES (  'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '-1', '1', '性别', 'sex', '1', '12', NULL);
    21. 

  21. INSERT INTO `sys_user_pageset` (  `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `user_id`, `page_type`, `name`, `val`, `status`, `order_no`, `remark`) VALUES (  'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '-1', '1', '年龄', 'age', '1', '13', NULL);
    22. 

  22. INSERT INTO `sys_user_pageset` (  `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `user_id`, `page_type`, `name`, `val`, `status`, `order_no`, `remark`) VALUES (  'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '-1', '1', '床号', 'bedCode', '1', '14', NULL);
    23. 

  23. INSERT INTO `sys_user_pageset` (  `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `user_id`, `page_type`, `name`, `val`, `status`, `order_no`, `remark`) VALUES (  'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '-1', '1', '入院日期', 'behospitalDate', '1', '15', NULL);
    24. 

  24. INSERT INTO `sys_user_pageset` (  `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `user_id`, `page_type`, `name`, `val`, `status`, `order_no`, `remark`) VALUES (  'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '-1', '1', '出院日期', 'leaveHospitalDate', '1', '16', NULL);
    25. 

  25. INSERT INTO `sys_user_pageset` (  `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `user_id`, `page_type`, `name`, `val`, `status`, `order_no`, `remark`) VALUES (  'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '-1', '1', '归档日期', 'placefileDate', '1', '17', NULL);
    26. 

  26. INSERT INTO `sys_user_pageset` (  `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `user_id`, `page_type`, `name`, `val`, `status`, `order_no`, `remark`) VALUES (  'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '-1', '1', '主诊断', 'diagnose', '1', '18', NULL);
    27. 

  27. INSERT INTO `sys_user_pageset` (  `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `user_id`, `page_type`, `name`, `val`, `status`, `order_no`, `remark`) VALUES (  'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '-1', '1', '住院医生', 'behDoctorName', '1', '19', NULL);
    28. 

  28. INSERT INTO `sys_user_pageset` (  `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `user_id`, `page_type`, `name`, `val`, `status`, `order_no`, `remark`) VALUES (  'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '-1', '1', '主管医生', 'doctorName', '1', '20', NULL);
    29. 

  29. INSERT INTO `sys_user_pageset` (  `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `user_id`, `page_type`, `name`, `val`, `status`, `order_no`, `remark`) VALUES (  'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '-1', '1', '主任医生', 'directorDoctorName', '1', '21', NULL);
    30. 

  30. INSERT INTO `sys_user_pageset` (  `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `user_id`, `page_type`, `name`, `val`, `status`, `order_no`, `remark`) VALUES (  'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '-1', '1', '科室', 'behDeptName', '1', '22', NULL);
    31. 

  31. INSERT INTO `sys_user_pageset` (  `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `user_id`, `page_type`, `name`, `val`, `status`, `order_no`, `remark`) VALUES ( 'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '-1', '1', '评分时间', 'gradeTime', '1', '23', NULL);
    32. 

  32. 

  33. -- 找出没有”核查状态“的人员并给加上
    34. 

  34.  INSERT INTO sys_user_pageset
    35. 

  35. (  `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `user_id`, `page_type`, `name`, `val`, `status`, `order_no`, `remark`)
    36. 

  36.  SELECT is_deleted,
    37. 

  37. gmt_create,
    38. 

  38.  gmt_modified,
    39. 

  39.  creator,
    40. 

  40. MODIFIER,
    41. 

  41.  user_id,
    42. 

  42.  page_type,
    43. 

  43.  CASE WHEN ISNULL(NAME) THEN "病案首页核查状态" ELSE "病案首页核查状态" END NAME,
    44. 

  44.  CASE WHEN ISNULL(userId) THEN "mrStatus" ELSE "mrStatus" END val,
    45. 

  45.  CASE WHEN ISNULL(STATUS) THEN 0 ELSE 0 END STATUS,
    46. 

  46.  CASE WHEN ISNULL(order_no) THEN 4 ELSE 4 END order_no,
    47. 

  47.  remark
    48. 

  48.  FROM (SELECT * FROM  sys_user_pageset  WHERE user_id <> -1) b LEFT JOIN  (SELECT c.user_id AS userId FROM  sys_user_pageset c  WHERE user_id <> -1 AND val = "mrStatus") p ON b.user_id = p.userId
    49. 

  49. WHERE  userId IS NULL
    50. 

  50.  GROUP BY user_id ;
    51. 

  51. 

  52. 

  53.  INSERT INTO sys_user_pageset
    54. 

  54. (  `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `user_id`, `page_type`, `name`, `val`, `status`, `order_no`, `remark`)
    55. 

  55.  SELECT is_deleted,
    56. 

  56. gmt_create,
    57. 

  57.  gmt_modified,
    58. 

  58.  creator,
    59. 

  59. MODIFIER,
    60. 

  60.  user_id,
    61. 

  61.  page_type,
    62. 

  62.  CASE WHEN ISNULL(NAME) THEN "病案首页核查人" ELSE "病案首页核查人" END NAME,
    63. 

  63.  CASE WHEN ISNULL(userId) THEN "mrName" ELSE "mrName" END val,
    64. 

  64.  CASE WHEN ISNULL(STATUS) THEN 0 ELSE 0 END STATUS,
    65. 

  65.  CASE WHEN ISNULL(order_no) THEN 5 ELSE 5 END order_no,
    66. 

  66.  remark
    67. 

  67.  FROM (SELECT * FROM  sys_user_pageset  WHERE user_id <> -1) b LEFT JOIN  (SELECT c.user_id AS userId FROM  sys_user_pageset c  WHERE user_id <> -1 AND val = "mrName") p ON b.user_id = p.userId
    68. 

  68. WHERE  userId IS NULL
    69. 

  69.  GROUP BY user_id ;
    70. 

  70. 

  71. 

  72.  INSERT INTO sys_user_pageset
    73. 

  73. (  `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `user_id`, `page_type`, `name`, `val`, `status`, `order_no`, `remark`)
    74. 

  74.  SELECT is_deleted,
    75. 

  75. gmt_create,
    76. 

  76.  gmt_modified,
    77. 

  77.  creator,
    78. 

  78. MODIFIER,
    79. 

  79.  user_id,
    80. 

  80.  page_type,
    81. 

  81.  CASE WHEN ISNULL(NAME) THEN "病案首页核查时间" ELSE "病案首页核查时间" END NAME,
    82. 

  82.  CASE WHEN ISNULL(userId) THEN "mrTime" ELSE "mrTime" END val,
    83. 

  83.  CASE WHEN ISNULL(STATUS) THEN 0 ELSE 0 END STATUS,
    84. 

  84.  CASE WHEN ISNULL(order_no) THEN 6 ELSE 6 END order_no,
    85. 

  85.  remark
    86. 

  86.  FROM (SELECT * FROM  sys_user_pageset  WHERE user_id <> -1) b LEFT JOIN  (SELECT c.user_id AS userId FROM  sys_user_pageset c  WHERE user_id <> -1 AND val = "mrTime") p ON b.user_id = p.userId
    87. 

  87. WHERE  userId IS NULL
    88. 

  88.  GROUP BY user_id ;
    89. 

  89. 

  90. 

  91. 

  92.  INSERT INTO sys_user_pageset
    93. 

  93. (  `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `user_id`, `page_type`, `name`, `val`, `status`, `order_no`, `remark`)
    94. 

  94.  SELECT is_deleted,
    95. 

  95. gmt_create,
    96. 

  96.  gmt_modified,
    97. 

  97.  creator,
    98. 

  98. MODIFIER,
    99. 

  99.  user_id,
    100. 

  100.  page_type,
    101. 

  101.  CASE WHEN ISNULL(NAME) THEN "病历核查人" ELSE "病历核查人" END NAME,
    102. 

  102.  CASE WHEN ISNULL(userId) THEN "chName" ELSE "chName" END val,
    103. 

  103.  CASE WHEN ISNULL(STATUS) THEN 0 ELSE 0 END STATUS,
    104. 

  104.  CASE WHEN ISNULL(order_no) THEN 7 ELSE 7 END order_no,
    105. 

  105.  remark
    106. 

  106.  FROM (SELECT * FROM  sys_user_pageset  WHERE user_id <> -1) b LEFT JOIN  (SELECT c.user_id AS userId FROM  sys_user_pageset c  WHERE user_id <> -1 AND val = "chName") p ON b.user_id = p.userId
    107. 

  107. WHERE  userId IS NULL
    108. 

  108.  GROUP BY user_id ;
    109. 

  109. 

  110. 

  111.  INSERT INTO sys_user_pageset
    112. 

  112. (  `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `user_id`, `page_type`, `name`, `val`, `status`, `order_no`, `remark`)
    113. 

  113.  SELECT is_deleted,
    114. 

  114. gmt_create,
    115. 

  115.  gmt_modified,
    116. 

  116.  creator,
    117. 

  117. MODIFIER,
    118. 

  118.  user_id,
    119. 

  119.  page_type,
    120. 

  120.  CASE WHEN ISNULL(NAME) THEN "病历核查时间" ELSE "病历核查时间" END NAME,
    121. 

  121.  CASE WHEN ISNULL(userId) THEN "chTime" ELSE "chTime" END val,
    122. 

  122.  CASE WHEN ISNULL(STATUS) THEN 0 ELSE 0 END STATUS,
    123. 

  123.  CASE WHEN ISNULL(order_no) THEN 8 ELSE 8 END order_no,
    124. 

  124.  remark
    125. 

  125.  FROM (SELECT * FROM  sys_user_pageset  WHERE user_id <> -1) b LEFT JOIN  (SELECT c.user_id AS userId FROM  sys_user_pageset c  WHERE user_id <> -1 AND val = "chTime") p ON b.user_id = p.userId
    126. 

  126. WHERE  userId IS NULL
    127. 

  127.  GROUP BY user_id ;
    128. 

  128. 

  129.  UPDATE sys_user_pageset a SET a.name = "病历核查状态" WHERE a.user_id <> -1 AND val ="checkStatus";
    130. 

  130. 

  131. -- 新增权限
    132. 

  132. INSERT INTO `sys_menu`(`id`, `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `name`, `parent_id`, `code`, `show_status`, `maintain_status`, `order_no`, `remark`) VALUES (38, 'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '31天再入院人数', 17, 'YH-ZKK-31TZRYRS', 1, 1, 14, '用户-质控科-31天再入院人数');
    133. 

  133. INSERT INTO `sys_role_menu`(`is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `role_id`, `menu_id`, `remark`) VALUES ('N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', -1, 38, '用户-质控科-31天再入院人数');
    134. 

  134. 

  135. update sys_menu set name ='时效性相关统计' where id=37;
    136.