mysql 记录

mysql 记录

2025-04-25 14:01:22

· 老系统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}

 

 

 

制单日期

· 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

 

根据 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;

部门科长

部门科长由部门授权决定的

老系统处理人有空着的就查��的 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