mysql 记录

Posted on 2025-04-25 Edited on 2026-04-14 In Database Word count in article: 889 Reading time ≈ 3 mins.

工作 mysql 笔记

老系统master，新系统ctm2
contract_code可以到contract 表中查询合同
form_no 到 form_ct_sign, form_ct_payment, business_form查询
act_audit_comment中查询审批记录
contract_payment_confirm是资金申请单
CO 是签订，CTP 是付款单
重置签订单 form_ct_sign 要先看付款单 form_ct_payment
签订单 form_ct_sign 产生 contract
查询流程：contract_id/form_no->data_id-> business_id->act_audit_comment

修改审批记录信息

根据实际情况修改代码，主要找到 business_id->act_audit_comment

select * from act_audit_comment where business_id in (select business_id from business_form where data_id in (select data_id from form_ct_payment/sign where data_id in (select data_id from form_ct_payment/sign where contract_id in (select contract_id from contract where contract_code=''))) and form_id=3)

合同期限消失

修改is_term = 1

修改未支付金额

根据 contract_id 查询 contract_payment

修改apply_amt=0

SELECT sum(amt) contract_amt,sum(paid_amt) yf_amt,sum(amt) - sum(paid_amt) - sum(apply_amt) wf_amt, sum(apply_amt) zf_amt, count(amt) as totalPno FROM contract_payment c WHERE contract_id = #{contractId}

!Pasted image 20250425140229.png !Pasted image 20250425140233.png !Pasted image 20250425140238.png

制单日期

form_ct_sign 的 create_date, create_time
business_form 的 create_date, create_time
contract 的 create_date

账号无法登录

userprgmrelation 是用户的部门，organization_emp_id 代表 organization 中的 orgn_id
organization 是部门，orgn_id 代表部门 id

删除 userorgnrelation 的 system !Pasted image 20250513163338.png 根据 organization 的部门orgn_id，查询当前用户部门

%% 查询用户多部门 %%
SELECT *
FROM userorgnrelation
WHERE EMPLOYEE_EMP_ID IN (
    SELECT EMPLOYEE_EMP_ID
    FROM userorgnrelation
    GROUP BY EMPLOYEE_EMP_ID
    HAVING COUNT(*) > 1
)
ORDER BY EMPLOYEE_EMP_ID;

部门科长

部门科长由部门授权决定的 !Pasted image 20250528165825.png 老系统处理人有空着的就查👇的 sql，然后重置

SELECT u.*
FROM sys_user u
INNER JOIN sys_user_role ur ON u.user_id = ur.user_id
INNER JOIN sys_role r ON ur.role_id = r.role_id
WHERE r.role_code = #{roleCode}
  AND u.dept_id = #{deptId}

在sys_user中修改 dept_id, dept_name

新系统

SELECT a.*
FROM (
    SELECT 
        t1.user_id,
        t1.user_cd,
        t1.user_name,
        o.ORGN_ID,
        o.ORGN_CD,
        o.ORGN_DESC,
        t3.ROLE_ID,
        t3.ROLE_CD,
        t3.ROLE_DESC,
        t1.HEAD_IMG
    FROM user t1
    LEFT JOIN userorgnrelation eor 
        ON t1.USER_ID = eor.EMPLOYEE_EMP_ID
    LEFT JOIN organization o 
        ON eor.ORGANIZATION_ORGN_ID = o.ORGN_ID
    LEFT JOIN userrolerelation t2 
        ON t1.USER_ID = t2.USERINFO_USER_ID
    LEFT JOIN role t3 
        ON t2.ROLE_ROLE_ID = t3.ROLE_ID
    WHERE
        t1.validity = '1'
        AND o.validity = '1'
        AND t3.ROLE_CD = #{roleCd}
        AND o.ORGN_ID = #{orgnId}
    ORDER BY t1.user_cd
) a;

# 部门权限 sys_user_org # 合同所在组织的权限

SELECT
 t2.ORGN_ID,
 t2.ORGN_CD,
 t2.ORGN_DESC,
 '' AS ORGNATTR_CD
FROM
 user_licen_orgn t1
  left join  organization t2 on t1.orgn_id=t2.orgn_id
  left join  `user` u on t1.user_id=u.user_id
WHERE
 u.USER_ID = #{userId} and t2.ORGN_ID is not null

重置单据

update business_form set `status` = 0 where form_no = '';
delete from act_audit_comment where business_id in (SELECT business_id from business_form where form_no = ''
);

替换文件并添加水印

修改 path 添加 pdf 路径，运行 java 文件加水印。
根据合同的 uuid，在 files 表中找到文件，复制文件路径
连接审计信息系统192.168.240.22，文件存到/ifcpFiles中。然后进行下面指令

传输文件到服务器

scp "filePath" 192.168.0.88:/ifcpFiles/#{folderPath}

连接服务器

ssh 192.168.0.88

cd /ifcpFiles/#{folderPath}

修改文件名，对应数据库的文件名

mv "fileName-2" "fileName"

财务支付信息

修改 act_audit_comment 的 start_time

select * from act_audit_comment where business_id in (select business_id from business_form
where data_id in (select data_id from form_ct_payment where form_no='') and form_id=3)

待审核重复

comment 是已审核 todo 是待办 task 是待审核

SELECT 
    c.task_state,
    c.task_id,
    c.task_name,
    c.start_time AS updateTime,
    bf.*,
    c1.contract_code,
    c1.amt AS contractAmt,
    c1.party_b,
    c1.start_date,
    c1.end_date,
    fcp.payable_amt
FROM act_audit_task c
INNER JOIN business_form bf 
    ON bf.business_id = c.business_id
LEFT JOIN form_ct_payment fcp 
    ON bf.data_id = fcp.data_id
LEFT JOIN contract c1 
    ON fcp.contract_id = c1.contract_id
WHERE 
    c.user_id = ?
ORDER BY 
    c.start_time DESC;

form_ct_payment 找 data_id, business_form 代入，找 business_id,代入act_audit_task中多的那个删掉，倒数第二

盖章查询

progress=2说明部门的不归他盖章，无权限

SELECT 
    c.*,
    cs.sort_name,
    ct.type_name
FROM 
    contract c
LEFT JOIN 
    contract_sort cs 
    ON cs.sort_id = c.sort_id
LEFT JOIN 
    contract_type ct 
    ON ct.type_id = c.type_id
WHERE 
    c.status = 3
    AND c.progress = 2
LIMIT 100;

查不到说明没有配置

SELECT 
    t2.ORGN_ID,
    t2.ORGN_CD,
    t2.ORGN_DESC,
    '' AS ORGNATTR_CD
FROM 
    user_licen_orgn t1
LEFT JOIN 
    organization t2 
    ON t1.orgn_id = t2.orgn_id
LEFT JOIN 
    `user` u 
    ON t1.user_id = u.user_id
WHERE 
    u.USER_ID = #{userId}
    AND t2.ORGN_ID IS NOT NULL
    AND t2.ORGN_ID = 137;

在user_licen_orgn增加记录

www

act_ru_variable act_ru_execution act_node act_ru_task act_re_procdef act_re_model act_hi_taskinst userorgnrelation userrolerelation