399 lines
18 KiB
SQL
399 lines
18 KiB
SQL
-- ============================================================================
|
||
-- OnboardX 基础业务假数据初始化脚本（MySQL 8.0+）
|
||
--
|
||
-- 使用：
|
||
-- 1. 先执行 20260828_reset_onboarding_business_data_v2.sql；
|
||
-- 2. 再执行本脚本；
|
||
-- 3. 发起任务时选择：
|
||
-- 重庆示例科技有限公司 / 技术部 / 后端开发工程师 /
|
||
-- 技术岗位标准入职模板（测试）。
|
||
--
|
||
-- 特点：
|
||
-- - 不删除任何数据；
|
||
-- - 不覆盖保留的 ob_variable_definition 和 ob_material_type；
|
||
-- - 只生成基础配置，不生成任务、员工或电子签约流程；
|
||
-- - 固定业务 Id + ON DUPLICATE KEY UPDATE，可重复执行。
|
||
--
|
||
-- 合同源文件默认自动选择 sys_file 中最近的 DOCX，并优先排除 OB20 开头的
|
||
-- 已生成任务合同。若自动选择不对，把 NULL 改为正确的源 DOCX 文件 Id。
|
||
-- 例如：SET @ob_seed_source_docx_file_id := 842701790859333;
|
||
-- ============================================================================
|
||
|
||
USE `OnboardX`;
|
||
SET NAMES utf8mb4;
|
||
SET @ob_seed_source_docx_file_id := NULL;
|
||
|
||
SET @ob_seed_source_docx_file_id := COALESCE(
|
||
@ob_seed_source_docx_file_id,
|
||
(
|
||
SELECT file.`Id`
|
||
FROM `sys_file` file
|
||
WHERE LOWER(TRIM(LEADING '.' FROM COALESCE(file.`Suffix`, ''))) = 'docx'
|
||
AND COALESCE(file.`IsDelete`, 0) = 0
|
||
ORDER BY
|
||
CASE WHEN UPPER(COALESCE(file.`Name`, '')) LIKE 'OB20%' THEN 1 ELSE 0 END,
|
||
file.`CreateTime` DESC,
|
||
file.`Id` DESC
|
||
LIMIT 1
|
||
)
|
||
);
|
||
|
||
DROP PROCEDURE IF EXISTS `ob_assert_seed_prerequisites`;
|
||
DELIMITER $$
|
||
CREATE PROCEDURE `ob_assert_seed_prerequisites`()
|
||
BEGIN
|
||
IF NOT EXISTS (
|
||
SELECT 1 FROM `ob_variable_definition`
|
||
WHERE COALESCE(`IsDelete`, 0) = 0
|
||
AND LOWER(COALESCE(`Status`, 'enable')) = 'enable'
|
||
) THEN
|
||
SIGNAL SQLSTATE '45000'
|
||
SET MESSAGE_TEXT = '初始化失败：ob_variable_definition 没有启用数据';
|
||
END IF;
|
||
|
||
IF NOT EXISTS (
|
||
SELECT 1 FROM `ob_material_type`
|
||
WHERE COALESCE(`IsDelete`, 0) = 0
|
||
AND LOWER(COALESCE(`Status`, 'enable')) = 'enable'
|
||
) THEN
|
||
SIGNAL SQLSTATE '45000'
|
||
SET MESSAGE_TEXT = '初始化失败：ob_material_type 没有启用数据';
|
||
END IF;
|
||
|
||
IF @ob_seed_source_docx_file_id IS NULL OR NOT EXISTS (
|
||
SELECT 1 FROM `sys_file`
|
||
WHERE `Id` = @ob_seed_source_docx_file_id
|
||
AND LOWER(TRIM(LEADING '.' FROM COALESCE(`Suffix`, ''))) = 'docx'
|
||
AND COALESCE(`IsDelete`, 0) = 0
|
||
) THEN
|
||
SIGNAL SQLSTATE '45000'
|
||
SET MESSAGE_TEXT = '初始化失败：没有可用的合同源 DOCX，请在脚本顶部指定文件 Id';
|
||
END IF;
|
||
END$$
|
||
DELIMITER ;
|
||
|
||
CALL `ob_assert_seed_prerequisites`();
|
||
DROP PROCEDURE IF EXISTS `ob_assert_seed_prerequisites`;
|
||
|
||
START TRANSACTION;
|
||
|
||
-- 1. 公司
|
||
INSERT INTO `ob_company`
|
||
(`Id`, `CompanyCode`, `CompanyName`, `LegalName`, `UnifiedSocialCreditCode`,
|
||
`LegalRepresentative`, `RegisteredAddress`, `ContactPhone`, `Remark`,
|
||
`Status`, `CreateTime`, `UpdateTime`, `CreateUser`, `UpdateUser`, `IsDelete`)
|
||
VALUES
|
||
(8800000000000001, 'DEMO-COMPANY-001', '重庆示例科技有限公司', '重庆示例科技有限公司',
|
||
'91500000MA5UDEMO01', '张示例', '重庆市渝北区示例大道 100 号', '023-88886666',
|
||
'测试公司，可在后台修改', 'ENABLE', NOW(3), NOW(3), 'seed-script', 'seed-script', 0)
|
||
ON DUPLICATE KEY UPDATE
|
||
`CompanyCode` = VALUES(`CompanyCode`),
|
||
`CompanyName` = VALUES(`CompanyName`),
|
||
`LegalName` = VALUES(`LegalName`),
|
||
`UnifiedSocialCreditCode` = VALUES(`UnifiedSocialCreditCode`),
|
||
`LegalRepresentative` = VALUES(`LegalRepresentative`),
|
||
`RegisteredAddress` = VALUES(`RegisteredAddress`),
|
||
`ContactPhone` = VALUES(`ContactPhone`),
|
||
`Remark` = VALUES(`Remark`),
|
||
`Status` = 'ENABLE',
|
||
`UpdateTime` = NOW(3),
|
||
`UpdateUser` = 'seed-script',
|
||
`IsDelete` = 0;
|
||
|
||
-- 2. 部门
|
||
INSERT INTO `ob_department`
|
||
(`Id`, `CompanyId`, `DepartmentCode`, `DepartmentName`, `SortCode`, `Remark`,
|
||
`Status`, `CreateTime`, `UpdateTime`, `CreateUser`, `UpdateUser`, `IsDelete`)
|
||
VALUES
|
||
(8800000000000101, 8800000000000001, 'DEMO-DEPT-HR', '人事部', 10,
|
||
'负责招聘、入职和员工关系', 'ENABLE', NOW(3), NOW(3), 'seed-script', 'seed-script', 0),
|
||
(8800000000000102, 8800000000000001, 'DEMO-DEPT-TECH', '技术部', 20,
|
||
'负责产品研发和质量保障', 'ENABLE', NOW(3), NOW(3), 'seed-script', 'seed-script', 0),
|
||
(8800000000000103, 8800000000000001, 'DEMO-DEPT-OPERATION', '运营部', 30,
|
||
'负责产品运营和客户服务', 'ENABLE', NOW(3), NOW(3), 'seed-script', 'seed-script', 0)
|
||
ON DUPLICATE KEY UPDATE
|
||
`CompanyId` = VALUES(`CompanyId`),
|
||
`DepartmentCode` = VALUES(`DepartmentCode`),
|
||
`DepartmentName` = VALUES(`DepartmentName`),
|
||
`SortCode` = VALUES(`SortCode`),
|
||
`Remark` = VALUES(`Remark`),
|
||
`Status` = 'ENABLE',
|
||
`UpdateTime` = NOW(3),
|
||
`UpdateUser` = 'seed-script',
|
||
`IsDelete` = 0;
|
||
|
||
-- 3. 岗位及默认人事合同参数
|
||
INSERT INTO `ob_position`
|
||
(`Id`, `CompanyId`, `DepartmentId`, `PositionCode`, `PositionName`,
|
||
`DefaultContractYears`, `DefaultProbationMonths`, `DefaultParamsJson`, `Remark`,
|
||
`Status`, `CreateTime`, `UpdateTime`, `CreateUser`, `UpdateUser`, `IsDelete`)
|
||
VALUES
|
||
(8800000000000201, 8800000000000001, 8800000000000101,
|
||
'DEMO-HR-SPECIALIST', '人事专员', 3, 3,
|
||
JSON_OBJECT('hr.BaseSalary', '4500', 'hr.PositionSalary', '1000',
|
||
'hr.PerformanceAllowance', '500', 'hr.AttendanceBonus', '200',
|
||
'hr.RegularSalary', '6200', 'hr.WorkCity', '重庆',
|
||
'hr.PositionName', '人事专员', 'hr.ContractTerm', '3', 'hr.ProbationTerm', '3'),
|
||
'测试岗位', 'ENABLE', NOW(3), NOW(3), 'seed-script', 'seed-script', 0),
|
||
(8800000000000202, 8800000000000001, 8800000000000102,
|
||
'DEMO-BACKEND-ENGINEER', '后端开发工程师', 3, 3,
|
||
JSON_OBJECT('hr.BaseSalary', '6500', 'hr.PositionSalary', '2500',
|
||
'hr.PerformanceAllowance', '1000', 'hr.AttendanceBonus', '200',
|
||
'hr.RegularSalary', '10200', 'hr.WorkCity', '重庆',
|
||
'hr.PositionName', '后端开发工程师', 'hr.ContractTerm', '3', 'hr.ProbationTerm', '3'),
|
||
'完整流程推荐使用此岗位', 'ENABLE', NOW(3), NOW(3), 'seed-script', 'seed-script', 0),
|
||
(8800000000000203, 8800000000000001, 8800000000000102,
|
||
'DEMO-FRONTEND-ENGINEER', '前端开发工程师', 3, 3,
|
||
JSON_OBJECT('hr.BaseSalary', '6200', 'hr.PositionSalary', '2300',
|
||
'hr.PerformanceAllowance', '1000', 'hr.AttendanceBonus', '200',
|
||
'hr.RegularSalary', '9700', 'hr.WorkCity', '重庆',
|
||
'hr.PositionName', '前端开发工程师', 'hr.ContractTerm', '3', 'hr.ProbationTerm', '3'),
|
||
'测试岗位', 'ENABLE', NOW(3), NOW(3), 'seed-script', 'seed-script', 0),
|
||
(8800000000000204, 8800000000000001, 8800000000000102,
|
||
'DEMO-QA-ENGINEER', '测试工程师', 3, 3,
|
||
JSON_OBJECT('hr.BaseSalary', '5500', 'hr.PositionSalary', '1800',
|
||
'hr.PerformanceAllowance', '800', 'hr.AttendanceBonus', '200',
|
||
'hr.RegularSalary', '8300', 'hr.WorkCity', '重庆',
|
||
'hr.PositionName', '测试工程师', 'hr.ContractTerm', '3', 'hr.ProbationTerm', '3'),
|
||
'测试岗位', 'ENABLE', NOW(3), NOW(3), 'seed-script', 'seed-script', 0),
|
||
(8800000000000205, 8800000000000001, 8800000000000103,
|
||
'DEMO-OPERATION-SPECIALIST', '运营专员', 3, 3,
|
||
JSON_OBJECT('hr.BaseSalary', '4500', 'hr.PositionSalary', '1200',
|
||
'hr.PerformanceAllowance', '800', 'hr.AttendanceBonus', '200',
|
||
'hr.RegularSalary', '6700', 'hr.WorkCity', '重庆',
|
||
'hr.PositionName', '运营专员', 'hr.ContractTerm', '3', 'hr.ProbationTerm', '3'),
|
||
'测试岗位', 'ENABLE', NOW(3), NOW(3), 'seed-script', 'seed-script', 0)
|
||
ON DUPLICATE KEY UPDATE
|
||
`CompanyId` = VALUES(`CompanyId`),
|
||
`DepartmentId` = VALUES(`DepartmentId`),
|
||
`PositionCode` = VALUES(`PositionCode`),
|
||
`PositionName` = VALUES(`PositionName`),
|
||
`DefaultContractYears` = VALUES(`DefaultContractYears`),
|
||
`DefaultProbationMonths` = VALUES(`DefaultProbationMonths`),
|
||
`DefaultParamsJson` = VALUES(`DefaultParamsJson`),
|
||
`Remark` = VALUES(`Remark`),
|
||
`Status` = 'ENABLE',
|
||
`UpdateTime` = NOW(3),
|
||
`UpdateUser` = 'seed-script',
|
||
`IsDelete` = 0;
|
||
|
||
-- 4. 技术部合同模板及适用岗位
|
||
INSERT INTO `ob_contract_template`
|
||
(`Id`, `CompanyId`, `DepartmentId`, `TemplateCode`, `TemplateName`, `ContractType`,
|
||
`Description`, `Status`, `CreateTime`, `UpdateTime`, `CreateUser`, `UpdateUser`, `IsDelete`)
|
||
VALUES
|
||
(8800000000000301, 8800000000000001, 8800000000000102,
|
||
'DEMO-LABOR-CONTRACT-001', '技术岗位劳动合同（测试）', '劳动合同',
|
||
'测试合同模板，源 DOCX 由脚本顶部变量决定',
|
||
'ENABLE', NOW(3), NOW(3), 'seed-script', 'seed-script', 0)
|
||
ON DUPLICATE KEY UPDATE
|
||
`CompanyId` = VALUES(`CompanyId`),
|
||
`DepartmentId` = VALUES(`DepartmentId`),
|
||
`TemplateCode` = VALUES(`TemplateCode`),
|
||
`TemplateName` = VALUES(`TemplateName`),
|
||
`ContractType` = VALUES(`ContractType`),
|
||
`Description` = VALUES(`Description`),
|
||
`Status` = 'ENABLE',
|
||
`UpdateTime` = NOW(3),
|
||
`UpdateUser` = 'seed-script',
|
||
`IsDelete` = 0;
|
||
|
||
INSERT INTO `ob_contract_template_position`
|
||
(`Id`, `ContractTemplateId`, `PositionId`, `Status`, `CreateTime`, `UpdateTime`,
|
||
`CreateUser`, `UpdateUser`, `IsDelete`)
|
||
VALUES
|
||
(8800000000000401, 8800000000000301, 8800000000000202,
|
||
'ENABLE', NOW(3), NOW(3), 'seed-script', 'seed-script', 0),
|
||
(8800000000000402, 8800000000000301, 8800000000000203,
|
||
'ENABLE', NOW(3), NOW(3), 'seed-script', 'seed-script', 0),
|
||
(8800000000000403, 8800000000000301, 8800000000000204,
|
||
'ENABLE', NOW(3), NOW(3), 'seed-script', 'seed-script', 0)
|
||
ON DUPLICATE KEY UPDATE
|
||
`ContractTemplateId` = VALUES(`ContractTemplateId`),
|
||
`PositionId` = VALUES(`PositionId`),
|
||
`Status` = 'ENABLE',
|
||
`UpdateTime` = NOW(3),
|
||
`UpdateUser` = 'seed-script',
|
||
`IsDelete` = 0;
|
||
|
||
-- 5. 已启用合同版本
|
||
INSERT INTO `ob_contract_template_version`
|
||
(`Id`, `TemplateId`, `VersionNo`, `SourceDocxFileId`, `FileHash`,
|
||
`EffectiveDate`, `ExpiryDate`, `VersionStatus`, `Remark`,
|
||
`Status`, `CreateTime`, `UpdateTime`, `CreateUser`, `UpdateUser`, `IsDelete`)
|
||
VALUES
|
||
(8800000000000311, 8800000000000301, '1.0', @ob_seed_source_docx_file_id,
|
||
CONCAT('seed-file-', @ob_seed_source_docx_file_id),
|
||
CURDATE(), DATE_ADD(CURDATE(), INTERVAL 10 YEAR), 'enabled', '测试合同版本',
|
||
'ENABLE', NOW(3), NOW(3), 'seed-script', 'seed-script', 0)
|
||
ON DUPLICATE KEY UPDATE
|
||
`TemplateId` = VALUES(`TemplateId`),
|
||
`VersionNo` = VALUES(`VersionNo`),
|
||
`SourceDocxFileId` = VALUES(`SourceDocxFileId`),
|
||
`FileHash` = VALUES(`FileHash`),
|
||
`EffectiveDate` = VALUES(`EffectiveDate`),
|
||
`ExpiryDate` = VALUES(`ExpiryDate`),
|
||
`VersionStatus` = 'enabled',
|
||
`Remark` = VALUES(`Remark`),
|
||
`Status` = 'ENABLE',
|
||
`UpdateTime` = NOW(3),
|
||
`UpdateUser` = 'seed-script',
|
||
`IsDelete` = 0;
|
||
|
||
-- SQL 不能解压 DOCX 识别占位符，因此复制当前启用的四类变量字典。
|
||
-- DOCX 未使用的变量不会改变文件；这样能避免实际占位符初始化时遗漏。
|
||
INSERT INTO `ob_template_variable`
|
||
(`Id`, `TemplateVersionId`, `VariableCode`, `VariableName`, `VariableType`, `DataType`,
|
||
`EmployeeField`, `DefaultValue`, `Required`, `HrEditable`, `SortCode`, `Remark`,
|
||
`Status`, `CreateTime`, `UpdateTime`, `CreateUser`, `UpdateUser`, `IsDelete`)
|
||
SELECT
|
||
8000000000000000 + definition.`Id`,
|
||
8800000000000311,
|
||
definition.`VariableCode`,
|
||
definition.`VariableName`,
|
||
CASE LOWER(definition.`VariableCategory`)
|
||
WHEN 'employee' THEN 'employee'
|
||
WHEN 'hr' THEN 'contract'
|
||
WHEN 'company' THEN 'company'
|
||
WHEN 'system' THEN 'system'
|
||
END,
|
||
COALESCE(NULLIF(definition.`DataType`, ''), 'string'),
|
||
CASE
|
||
WHEN LOWER(definition.`VariableCategory`) IN ('employee', 'company')
|
||
THEN SUBSTRING_INDEX(definition.`VariableCode`, '.', -1)
|
||
ELSE NULL
|
||
END,
|
||
definition.`DefaultValue`,
|
||
COALESCE(definition.`Required`, 0),
|
||
CASE WHEN LOWER(definition.`VariableCategory`) = 'hr' THEN 1 ELSE 0 END,
|
||
COALESCE(definition.`SortCode`, 100),
|
||
definition.`Remark`,
|
||
'ENABLE', NOW(3), NOW(3), 'seed-script', 'seed-script', 0
|
||
FROM `ob_variable_definition` definition
|
||
WHERE COALESCE(definition.`IsDelete`, 0) = 0
|
||
AND LOWER(COALESCE(definition.`Status`, 'enable')) = 'enable'
|
||
AND LOWER(definition.`VariableCategory`) IN ('employee', 'hr', 'company', 'system')
|
||
AND definition.`Id` BETWEEN 1 AND 1007199254740991
|
||
ON DUPLICATE KEY UPDATE
|
||
`VariableName` = VALUES(`VariableName`),
|
||
`VariableType` = VALUES(`VariableType`),
|
||
`DataType` = VALUES(`DataType`),
|
||
`EmployeeField` = VALUES(`EmployeeField`),
|
||
`DefaultValue` = VALUES(`DefaultValue`),
|
||
`Required` = VALUES(`Required`),
|
||
`HrEditable` = VALUES(`HrEditable`),
|
||
`SortCode` = VALUES(`SortCode`),
|
||
`Remark` = VALUES(`Remark`),
|
||
`Status` = 'ENABLE',
|
||
`UpdateTime` = NOW(3),
|
||
`UpdateUser` = 'seed-script',
|
||
`IsDelete` = 0;
|
||
|
||
-- 6. 可直接发起任务的入职模板
|
||
INSERT INTO `ob_onboarding_template`
|
||
(`Id`, `TemplateCode`, `TemplateName`, `CompanyId`, `DepartmentId`, `Description`,
|
||
`Status`, `CreateTime`, `UpdateTime`, `CreateUser`, `UpdateUser`, `IsDelete`)
|
||
VALUES
|
||
(8800000000000501, 'DEMO-ONBOARDING-TECH', '技术岗位标准入职模板（测试）',
|
||
8800000000000001, 8800000000000102,
|
||
'适用于后端、前端和测试工程师，可用于完整入职流程测试',
|
||
'ENABLE', NOW(3), NOW(3), 'seed-script', 'seed-script', 0)
|
||
ON DUPLICATE KEY UPDATE
|
||
`TemplateCode` = VALUES(`TemplateCode`),
|
||
`TemplateName` = VALUES(`TemplateName`),
|
||
`CompanyId` = VALUES(`CompanyId`),
|
||
`DepartmentId` = VALUES(`DepartmentId`),
|
||
`Description` = VALUES(`Description`),
|
||
`Status` = 'ENABLE',
|
||
`UpdateTime` = NOW(3),
|
||
`UpdateUser` = 'seed-script',
|
||
`IsDelete` = 0;
|
||
|
||
INSERT INTO `ob_onboarding_template_position`
|
||
(`Id`, `OnboardingTemplateId`, `PositionId`, `Status`, `CreateTime`, `UpdateTime`,
|
||
`CreateUser`, `UpdateUser`, `IsDelete`)
|
||
VALUES
|
||
(8800000000000601, 8800000000000501, 8800000000000202,
|
||
'ENABLE', NOW(3), NOW(3), 'seed-script', 'seed-script', 0),
|
||
(8800000000000602, 8800000000000501, 8800000000000203,
|
||
'ENABLE', NOW(3), NOW(3), 'seed-script', 'seed-script', 0),
|
||
(8800000000000603, 8800000000000501, 8800000000000204,
|
||
'ENABLE', NOW(3), NOW(3), 'seed-script', 'seed-script', 0)
|
||
ON DUPLICATE KEY UPDATE
|
||
`OnboardingTemplateId` = VALUES(`OnboardingTemplateId`),
|
||
`PositionId` = VALUES(`PositionId`),
|
||
`Status` = 'ENABLE',
|
||
`UpdateTime` = NOW(3),
|
||
`UpdateUser` = 'seed-script',
|
||
`IsDelete` = 0;
|
||
|
||
INSERT INTO `ob_onboarding_template_contract`
|
||
(`Id`, `OnboardingTemplateId`, `ContractTemplateId`, `ContractTemplateVersionId`,
|
||
`Required`, `PrintCopies`, `SortCode`, `Status`, `CreateTime`, `UpdateTime`,
|
||
`CreateUser`, `UpdateUser`, `IsDelete`)
|
||
VALUES
|
||
(8800000000000701, 8800000000000501, 8800000000000301, 8800000000000311,
|
||
1, 1, 10, 'ENABLE', NOW(3), NOW(3), 'seed-script', 'seed-script', 0)
|
||
ON DUPLICATE KEY UPDATE
|
||
`OnboardingTemplateId` = VALUES(`OnboardingTemplateId`),
|
||
`ContractTemplateId` = VALUES(`ContractTemplateId`),
|
||
`ContractTemplateVersionId` = VALUES(`ContractTemplateVersionId`),
|
||
`Required` = 1,
|
||
`PrintCopies` = 1,
|
||
`SortCode` = 10,
|
||
`Status` = 'ENABLE',
|
||
`UpdateTime` = NOW(3),
|
||
`UpdateUser` = 'seed-script',
|
||
`IsDelete` = 0;
|
||
|
||
-- 所有启用资料类型都会显示；一寸白底证件照必传，其余选传。
|
||
INSERT INTO `ob_onboarding_template_material`
|
||
(`Id`, `OnboardingTemplateId`, `MaterialTypeId`, `Required`, `SortCode`,
|
||
`Status`, `CreateTime`, `UpdateTime`, `CreateUser`, `UpdateUser`, `IsDelete`)
|
||
SELECT
|
||
7000000000000000 + material.`Id`,
|
||
8800000000000501,
|
||
material.`Id`,
|
||
CASE
|
||
WHEN material.`MaterialName` LIKE '%一寸白底%'
|
||
OR LOWER(material.`MaterialCode`) IN ('photo', 'id_photo', 'head_photo') THEN 1
|
||
ELSE 0
|
||
END,
|
||
ROW_NUMBER() OVER (ORDER BY material.`Id`) * 10,
|
||
'ENABLE', NOW(3), NOW(3), 'seed-script', 'seed-script', 0
|
||
FROM `ob_material_type` material
|
||
WHERE COALESCE(material.`IsDelete`, 0) = 0
|
||
AND LOWER(COALESCE(material.`Status`, 'enable')) = 'enable'
|
||
AND material.`Id` BETWEEN 1 AND 2007199254740991
|
||
ON DUPLICATE KEY UPDATE
|
||
`Required` = VALUES(`Required`),
|
||
`SortCode` = VALUES(`SortCode`),
|
||
`Status` = 'ENABLE',
|
||
`UpdateTime` = NOW(3),
|
||
`UpdateUser` = 'seed-script',
|
||
`IsDelete` = 0;
|
||
|
||
COMMIT;
|
||
|
||
-- 执行结果：先核对自动选中的源 DOCX，再核对初始化数量。
|
||
SELECT
|
||
'SUCCESS' AS `Result`,
|
||
@ob_seed_source_docx_file_id AS `SourceDocxFileId`,
|
||
(SELECT `Name` FROM `sys_file` WHERE `Id` = @ob_seed_source_docx_file_id LIMIT 1) AS `SourceDocxFileName`,
|
||
8800000000000001 AS `CompanyId`,
|
||
8800000000000102 AS `DepartmentId`,
|
||
8800000000000202 AS `RecommendedPositionId`,
|
||
8800000000000501 AS `OnboardingTemplateId`;
|
||
|
||
SELECT
|
||
(SELECT COUNT(*) FROM `ob_company`
|
||
WHERE `Id` = 8800000000000001 AND COALESCE(`IsDelete`, 0) = 0) AS `CompanyCount`,
|
||
(SELECT COUNT(*) FROM `ob_department`
|
||
WHERE `CompanyId` = 8800000000000001 AND COALESCE(`IsDelete`, 0) = 0) AS `DepartmentCount`,
|
||
(SELECT COUNT(*) FROM `ob_position`
|
||
WHERE `CompanyId` = 8800000000000001 AND COALESCE(`IsDelete`, 0) = 0) AS `PositionCount`,
|
||
(SELECT COUNT(*) FROM `ob_template_variable`
|
||
WHERE `TemplateVersionId` = 8800000000000311 AND COALESCE(`IsDelete`, 0) = 0) AS `ContractVariableCount`,
|
||
(SELECT COUNT(*) FROM `ob_onboarding_template_material`
|
||
WHERE `OnboardingTemplateId` = 8800000000000501 AND COALESCE(`IsDelete`, 0) = 0) AS `MaterialCount`;
|