Calculation/sql/update.sql

-- 请按如下分类提交sql！！！
-- Version V3.5.51.0145
-- uat 2026-01-21
-- prod 2026-01-22

------------------------------------
-- 表结构
------------------------------------

ALTER TABLE `zh_contract`
ADD COLUMN `type` varchar(255) NULL DEFAULT '' COMMENT '合同类型（筛选的字段）' AFTER `name`,
ADD COLUMN `remark1` varchar(1000) NULL DEFAULT '' COMMENT '备注1' AFTER `remark`;

ALTER TABLE `zh_contract_tree`
ADD COLUMN `remark1` varchar(1000) NULL DEFAULT '' COMMENT '备注1' AFTER `remark`;

ALTER TABLE `zh_contract`

ADD COLUMN `calc` decimal(30, 6) NULL DEFAULT NULL COMMENT '计算1' AFTER `remark2`,
ADD COLUMN `calc2` decimal(30, 6) NULL DEFAULT NULL COMMENT '计算2' AFTER `calc`;

ALTER TABLE `zh_shenpi_audit`
ADD COLUMN `audit_group` varchar(20) NOT NULL DEFAULT '' COMMENT '审批分组' AFTER `audit_ledger_id`,
ADD COLUMN `audit_group_order` tinyint(4) NOT NULL DEFAULT 0 COMMENT '组内审批顺序' AFTER `audit_group`,
ADD COLUMN `audit_group_limit` tinyint(4) NOT NULL DEFAULT 0 COMMENT '组内是否受限' AFTER `audit_group_order`,
ADD COLUMN `audit_checkno_valid` tinyint(4) UNSIGNED NOT NULL DEFAULT 1 COMMENT '是否允许审批退回' AFTER `audit_group_limit`,
ADD COLUMN `audit_group_need` tinyint(4) UNSIGNED NOT NULL DEFAULT 1 COMMENT '组内最少审批人数' AFTER `audit_checkno_valid`,
ADD COLUMN `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间' AFTER `sp_group`;

ALTER TABLE `zh_stage_audit`
ADD COLUMN `audit_group` varchar(20) NOT NULL DEFAULT '' COMMENT '审批分组' AFTER `audit_ledger_id`,
ADD COLUMN `audit_group_order` tinyint(4) NOT NULL DEFAULT 0 COMMENT '组内顺序' AFTER `audit_group`,
ADD COLUMN `audit_group_limit` tinyint(4) NOT NULL DEFAULT 0 COMMENT '组内是否受限' AFTER `audit_group_order`,
ADD COLUMN `audit_checkno_valid` tinyint(4) UNSIGNED NOT NULL DEFAULT 1 COMMENT '是否允许审批退回' AFTER `audit_group_limit`,
ADD COLUMN `audit_group_need` tinyint(4) UNSIGNED NOT NULL DEFAULT 1 COMMENT '组内最少审批人数' AFTER `audit_checkno_valid`;

ALTER TABLE `zh_tender_permission`
ADD COLUMN `cost` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '成本管理权限(,分隔，具体见代码定义)' AFTER `schedule`;

ALTER TABLE `zh_bills_template_list`
ADD COLUMN `sub_type` tinyint(4) UNSIGNED NOT NULL DEFAULT 0 COMMENT '二级分类' AFTER `remark`;

ALTER TABLE `zh_sub_project`
ADD COLUMN `cost_ledger_template` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '成本台账模板id' AFTER `common_json`,
ADD COLUMN `cost_analysis_template` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '成本分析模板id' AFTER `cost_ledger_template`,
ADD COLUMN `cost_calc_template` varchar(36) NOT NULL DEFAULT '' COMMENT '成本分析，计算模板id' AFTER `cost_analysis_template`;

CREATE TABLE `zh_cost_stage`  (
  `id` varchar(36) NOT NULL COMMENT 'uuid',
  `tid` int(11) UNSIGNED NOT NULL COMMENT '标段id(zh_tender.id)',
  `stage_type` varchar(20) NULL COMMENT '期类型（台账ledger/账面book/分析analysis）',
  `stage_order` int(11) UNSIGNED NOT NULL COMMENT '期序号',
  `create_user_id` int(11) NOT NULL COMMENT '创建人id',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_user_id` int(11) UNSIGNED NOT NULL COMMENT '最后修改人id',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
  `stage_date` varchar(20) NOT NULL COMMENT '报审月份',
  `decimal` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '{"tp": 6}' COMMENT '小数位数',
  `audit_times` tinyint(4) NOT NULL COMMENT '审批轮次',
  `audit_status` tinyint(4) NOT NULL COMMENT '审批状态',
  `audit_max_sort` tinyint(4) UNSIGNED NOT NULL DEFAULT 0 COMMENT '最大审批排序',
  `audit_begin_time` timestamp NULL COMMENT '开始审批时间',
  `audit_end_time` timestamp NULL COMMENT '审批结束时间',
  `final_auditor_str` varchar(255) NOT NULL DEFAULT '' COMMENT '终审缓存信息',
  `stage_tp` json NOT NULL COMMENT '金额',
  `stage_pre_tp` json NOT NULL COMMENT '截止上期金额',
  PRIMARY KEY (`id`),
  INDEX `idx_tid_type`(`tid`, `stage_type`) USING BTREE
);

CREATE TABLE `zh_cost_stage_audit`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `tid` int(11) UNSIGNED NOT NULL COMMENT '标段id',
  `stage_id` varchar(36) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '期id',
  `audit_id` int(11) UNSIGNED NOT NULL COMMENT '流程参与人id(含原报)',
  `name` varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '参与人-姓名',
  `company` varchar(60) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '参与人-单位',
  `role` varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '参与人-角色',
  `mobile` varchar(15) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '参与人-电话',
  `audit_times` int(11) UNSIGNED NOT NULL DEFAULT 1 COMMENT '审批次数',
  `audit_order` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '预定流程顺序',
  `audit_type` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '流程类型',
  `active_order` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '实际流程顺序',
  `audit_status` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '流程状态',
  `audit_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '流程结束时间',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
  `opinion` varchar(1000) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '意见',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;

CREATE TABLE `zh_cost_stage_file`  (
  `id` varchar(36) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT 'uuid',
  `tid` int(11) UNSIGNED NOT NULL COMMENT '标段id',
  `stage_id` varchar(36) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '期stage_id(zh_cost_stage.id)',
  `stage_type` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '类型(ledger/book/analysis/...)',
  `rela_id` varchar(36) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT 'uuid(zh_cost_stage_ledger.id/...)',
  `rela_sub_id` varchar(36) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT 'uuid(zh_cost_stage_detail.id/...)',
  `filename` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '文件名',
  `fileext` varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '文件后缀',
  `filesize` int(11) NOT NULL COMMENT '文件大小',
  `filepath` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '文件存储路径',
  `user_id` int(11) UNSIGNED NOT NULL COMMENT '用户id(zh_project_account.id)',
  `user_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '用户名(缓存)',
  `user_company` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '公司(缓存)',
  `user_role` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '角色(缓存)',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
  `is_deleted` tinyint(4) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否删除',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;

CREATE TABLE `zh_cost_stage_ledger` (
  `id` varchar(36) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT 'uuid',
  `cost_id` varchar(36) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT 'uuid（不同期保持统一）',
  `tender_id` int(11) unsigned NOT NULL COMMENT '标段id',
  `stage_id` varchar(36) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '期id',
  `tree_id` int(11) NOT NULL COMMENT '节点id',
  `tree_pid` int(11) NOT NULL COMMENT '父节点id',
  `tree_level` tinyint(4) NOT NULL COMMENT '层级',
  `tree_order` mediumint(4) NOT NULL COMMENT '同级排序',
  `tree_full_path` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT '层级定位辅助字段parent.full_path-tree_id',
  `tree_is_leaf` tinyint(4) unsigned NOT NULL DEFAULT '1' COMMENT '是否叶子节点，界面显示辅助字段',
  `code` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '编号',
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '名称',
  `unit` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '单位',
  `pre_pay_tp` decimal(24,8) NOT NULL DEFAULT '0.00000000' COMMENT '截止上期-付款',
  `pre_cut_tp` decimal(24,8) NOT NULL DEFAULT '0.00000000' COMMENT '截止上期-扣款',
  `pre_yf_tp` decimal(24,8) NOT NULL DEFAULT '0.00000000' COMMENT '截止上期-应付',
  `pre_sf_tp` decimal(24,8) NOT NULL DEFAULT '0.00000000' COMMENT '截止上期-实付',
  `pay_tp` decimal(24,8) NOT NULL DEFAULT '0.00000000' COMMENT '付款',
  `cut_tp` decimal(24,8) NOT NULL DEFAULT '0.00000000' COMMENT '扣款',
  `yf_tp` decimal(24,8) NOT NULL DEFAULT '0.00000000' COMMENT '应付',
  `sf_tp` decimal(24,8) NOT NULL DEFAULT '0.00000000' COMMENT '实付',
  `postil` varchar(1000) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '本期批注',
  `memo` varchar(1000) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '备注',
  `add_user_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '创建人',
  `add_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_user_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '最后编辑人',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后编辑时间',
  `read_pay_tp` decimal(24,8) NOT NULL DEFAULT '0.00000000' COMMENT '本期付款-只读',
  `read_cut_tp` decimal(24,8) NOT NULL DEFAULT '0.00000000' COMMENT '本期扣款-只读',
  `read_yf_tp` decimal(24,8) NOT NULL DEFAULT '0.00000000' COMMENT '本期应付-只读',
  `read_sf_tp` decimal(24,8) NOT NULL DEFAULT '0.00000000' COMMENT '本期实付-只读',
  `calc_his` json DEFAULT NULL COMMENT '本期历史',
  `is_used` tinyint(4) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否已使用',
  `is_deal` tinyint(4) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否为合同节点',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `zh_cost_stage_detail`  (
  `id` varchar(36) NOT NULL COMMENT 'uuid',
  `tender_id` int(11) NOT NULL COMMENT '标段id',
  `stage_id` varchar(36) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '期id',
  `ledger_id` varchar(36) NOT NULL COMMENT '所属台账id(zh_stage_ledger.id)',
  `cost_id` varchar(36) CHARACTER SET utf16le NOT NULL COMMENT '所属台账cost_id(不同期一致zh_stage_ledger.cost_id)',
  `d_order` int(11) NOT NULL COMMENT '排序',
  `code` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '编号',
  `name` varchar(255) CHARACTER SET utf16 NOT NULL DEFAULT '' COMMENT '名称',
  `party_b` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '乙方',
  `pre_pay_tp` decimal(24, 8) NOT NULL DEFAULT 0.00000000 COMMENT '截止上期-付款',
  `pre_cut_tp` decimal(24, 8) NOT NULL DEFAULT 0.00000000 COMMENT '截止上期-扣款',
  `pre_yf_tp` decimal(24, 8) NOT NULL DEFAULT 0.00000000 COMMENT '截止上期-应付',
  `pre_sf_tp` decimal(24, 8) NOT NULL DEFAULT 0.00000000 COMMENT '截止上期-实付',
  `pay_tp` decimal(24, 8) NOT NULL DEFAULT 0.00000000 COMMENT '付款',
  `cut_tp` decimal(24, 8) NOT NULL DEFAULT 0.00000000 COMMENT '扣款',
  `yf_tp` decimal(24, 8) NOT NULL DEFAULT 0.00000000 COMMENT '应付',
  `sf_tp` decimal(24, 8) NOT NULL DEFAULT 0.00000000 COMMENT '实付',
  `postil` varchar(1000) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '本期批注',
  `memo` varchar(1000) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '备注',
  `add_user_id` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '创建人',
  `add_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间',
  `update_user_id` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '最后编辑人',
  `update_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '最后编辑时间',
  `read_pay_tp` decimal(24, 8) NOT NULL DEFAULT 0.00000000 COMMENT '本期付款-只读',
  `read_cut_tp` decimal(24, 8) NOT NULL DEFAULT 0.00000000 COMMENT '本期扣款-只读',
  `read_yf_tp` decimal(24, 8) NOT NULL DEFAULT 0.00000000 COMMENT '本期应付-只读',
  `read_sf_tp` decimal(24, 8) NOT NULL DEFAULT 0.00000000 COMMENT '本期实付-只读',
  `calc_his` json NULL COMMENT '本期历史',
  `is_used` tinyint(4) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否已使用',
  `is_deal` tinyint(4) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否为合同节点',
  PRIMARY KEY (`id`)
);

CREATE TABLE `zh_cost_stage_tag`  (
  `id` varchar(36) NOT NULL COMMENT 'uuid',
  `tender_id` int(11) NOT NULL COMMENT '标段id(zh_tender.id)',
  `stage_id` varchar(11) NOT NULL COMMENT '期id(zh_cost_stage.id)',
  `stage_type` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '期类型(ledger/book/analysis/...)',
  `rela_id` varchar(36) NOT NULL COMMENT '关联id(zh_cost_stage_ledger.id/...)',
  `rela_sub_id` varchar(36) NOT NULL COMMENT '次关联id(zh_cost_stage_detail.id/...)',
  `create_user_id` int(11) NOT NULL COMMENT '创建人id(zh_project_accnount.id)',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_user_id` int(11) NOT NULL COMMENT '最后修改人id(zh_project_account.id)',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
  `share` tinyint(1) NOT NULL DEFAULT 0 COMMENT '是否共享给其他参与人',
  `color` varchar(7) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL DEFAULT '' COMMENT '书签颜色',
  `comment` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL COMMENT '批注',
  PRIMARY KEY (`id`)
);

CREATE TABLE `zh_cost_stage_book` (
  `id` varchar(36) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT 'uuid(zh_cost_stage_ledger.id)',
  `cost_id` varchar(36) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT 'uuid（不同期保持统一zh_cost_stage_ledger.cost_id）',
  `tender_id` int(11) unsigned NOT NULL COMMENT '标段id',
  `stage_id` varchar(36) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '期id',
  `pre_in_tp` decimal(24,8) NOT NULL DEFAULT '0.00000000' COMMENT '截止上期-入账金额',
  `in_tp` decimal(24,8) NOT NULL DEFAULT '0.00000000' COMMENT '入账金额',
  `postil` varchar(1000) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '本期批注',
  `memo` varchar(1000) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '备注',
  `add_user_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '创建人',
  `add_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_user_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '最后编辑人',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后编辑时间',
  `read_in_tp` decimal(24,8) NOT NULL DEFAULT '0.00000000' COMMENT '本期入账-只读',
  `calc_his` json DEFAULT NULL COMMENT '本期历史',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

------------------------------------
-- 表数据
------------------------------------