106 lines
3.7 KiB
JavaScript
106 lines
3.7 KiB
JavaScript
const { Pool } = require('pg');
|
|
const pool = new Pool({
|
|
host: '47.116.196.11',
|
|
port: 15432,
|
|
database: 'postgresql',
|
|
user: 'postgresql',
|
|
password: 'Jchl1528'
|
|
});
|
|
|
|
async function analyze() {
|
|
try {
|
|
// 1. 检查申请科室ID在adm_organization中是否存在
|
|
console.log('=== 分析申请科室ID是否有效 ===');
|
|
const deptResult = await pool.query(`
|
|
SELECT DISTINCT s.apply_dept_id
|
|
FROM hisdev.cli_surgery s
|
|
WHERE s.delete_flag = '0'
|
|
AND (s.apply_dept_name IS NULL OR s.apply_dept_name = '')
|
|
AND s.apply_dept_id IS NOT NULL
|
|
AND NOT EXISTS (
|
|
SELECT 1 FROM hisdev.adm_organization o WHERE o.id = s.apply_dept_id
|
|
)
|
|
`);
|
|
console.log(`申请科室ID无效的记录数: ${deptResult.rows.length}`);
|
|
if (deptResult.rows.length > 0) {
|
|
console.log('无效的apply_dept_id:', deptResult.rows.map(r => r.apply_dept_id));
|
|
}
|
|
|
|
// 2. 检查主刀医生ID在sys_user中是否存在
|
|
console.log('\n=== 分析主刀医生ID是否有效 ===');
|
|
const surgeonResult = await pool.query(`
|
|
SELECT DISTINCT s.main_surgeon_id
|
|
FROM hisdev.cli_surgery s
|
|
WHERE s.delete_flag = '0'
|
|
AND (s.main_surgeon_name IS NULL OR s.main_surgeon_name = '')
|
|
AND s.main_surgeon_id IS NOT NULL
|
|
AND NOT EXISTS (
|
|
SELECT 1 FROM hisdev.sys_user u WHERE u.user_id = s.main_surgeon_id
|
|
)
|
|
`);
|
|
console.log(`主刀医生ID无效的记录数: ${surgeonResult.rows.length}`);
|
|
if (surgeonResult.rows.length > 0) {
|
|
console.log('无效的main_surgeon_id:', surgeonResult.rows.map(r => r.main_surgeon_id));
|
|
}
|
|
|
|
// 3. 查看有ID但没有name的记录详情
|
|
console.log('\n=== 有ID但Name为空的记录详情 ===');
|
|
const detailResult = await pool.query(`
|
|
SELECT
|
|
s.id,
|
|
s.surgery_no,
|
|
s.apply_dept_id,
|
|
s.main_surgeon_id,
|
|
s.create_time,
|
|
EXISTS (SELECT 1 FROM hisdev.adm_organization o WHERE o.id = s.apply_dept_id) as dept_exists,
|
|
EXISTS (SELECT 1 FROM hisdev.sys_user u WHERE u.user_id = s.main_surgeon_id) as surgeon_exists
|
|
FROM hisdev.cli_surgery s
|
|
WHERE s.delete_flag = '0'
|
|
AND (
|
|
((s.apply_dept_name IS NULL OR s.apply_dept_name = '') AND s.apply_dept_id IS NOT NULL)
|
|
OR
|
|
((s.main_surgeon_name IS NULL OR s.main_surgeon_name = '') AND s.main_surgeon_id IS NOT NULL)
|
|
)
|
|
ORDER BY s.create_time DESC
|
|
LIMIT 10
|
|
`);
|
|
console.log(JSON.stringify(detailResult.rows, null, 2));
|
|
|
|
// 4. 检查最近创建的记录为什么name为空
|
|
console.log('\n=== 最近10条记录的创建情况 ===');
|
|
const recentResult = await pool.query(`
|
|
SELECT
|
|
s.id,
|
|
s.surgery_no,
|
|
s.apply_dept_id,
|
|
s.apply_dept_name,
|
|
s.main_surgeon_id,
|
|
s.main_surgeon_name,
|
|
s.create_time,
|
|
s.create_by,
|
|
CASE
|
|
WHEN s.apply_dept_id IS NULL THEN 'apply_dept_id为空'
|
|
WHEN NOT EXISTS (SELECT 1 FROM hisdev.adm_organization o WHERE o.id = s.apply_dept_id) THEN 'apply_dept_id无效'
|
|
ELSE 'apply_dept_id有效'
|
|
END as dept_status,
|
|
CASE
|
|
WHEN s.main_surgeon_id IS NULL THEN 'main_surgeon_id为空'
|
|
WHEN NOT EXISTS (SELECT 1 FROM hisdev.sys_user u WHERE u.user_id = s.main_surgeon_id) THEN 'main_surgeon_id无效'
|
|
ELSE 'main_surgeon_id有效'
|
|
END as surgeon_status
|
|
FROM hisdev.cli_surgery s
|
|
WHERE s.delete_flag = '0'
|
|
ORDER BY s.create_time DESC
|
|
LIMIT 10
|
|
`);
|
|
console.log(JSON.stringify(recentResult.rows, null, 2));
|
|
|
|
} catch (err) {
|
|
console.error('分析失败:', err.message);
|
|
} finally {
|
|
pool.end();
|
|
}
|
|
}
|
|
|
|
analyze();
|