沐曦留曳

SDB模版导出

  • ~4.02K 字
  1. 1. 模版导出
  2. 2. 问题处理
    1. 2.1. 同一个模版中,如果取值不一样,不能配置成相同的标签

模版导出

模版导出

  1. 配置视图SQL

  2. 在代码中设置变量，变量名称为视图SQL中所需要的参数名称

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    @Override
    public SdbLetterTemplateDTO templateExport(SdbLetterTemplateReqDTO dto) {
        String templateId = dto.getTemplateId();

        final TemplateExportDTO templateExportDTO=new TemplateExportDTO();
    //查询导出模版配置

        templateExportDTO.setTemplateConfigId(templateId);
        templateExportDTO.setTenantId(SystemContextHolder.getTenantId());
        templateExportDTO.setAppId(SystemContextHolder.getAppId());
        //TODO 文件名称规则
        templateExportDTO.setExportFileName("");
        templateExportDTO.setBusinessCode(EcosConstant.EXPORT_BUSINESS_CODE);
        //导出sql的参数拼接
        Map<String, List<String>> variableValueMap =new HashMap<>();
        // 参数填充

        templateExportDTO.setVariableValueMap(variableValueMap);
        //调用中台接口返回文件id
        log.info("export params json {}", JSON.toJSONString(templateExportDTO));
        final ActionResult<String> fileResult = templateFeignApi.templateExport(templateExportDTO);
        String fileId = FeignClientHelper.getActionResult(fileResult);
        if (StringUtils.isBlank(fileId)) {
            throw new RuntimeException("template-export-service word template error!");
        }
        log.info("返回文件ID {}",fileId);
        return SdbLetterTemplateDTO.builder().fileId(fileId).build();
    }

  3. 视图配置中选择参数与模版之中的变量一一匹配

  4. 如果所需的视图函数有多个，且需要的参数也为多个，则需要配置不同的视图变量

1
UPDATE `site_pro`.`t_sdb_submission_site_member` SET `study_id` = '8ac0815090fc4f370191018634e37fbb', `study_ref` = 'SDB-2024/0670', `submission_id` = '8ac0807c9116e9c9019116febd4500e0', `cluster_id` = 'ff8080818e3aeb7e018e3affb65b55ea', `site_id` = '8a0a0c4e8be666cd018beba9cdd061c1', `department_id` = '8a0a0c4e8be666cd018beba9cdd061c5', `cluster_name` = 'National University of Singapore', `site_name` = 'Duke-NUS', `department_name` = 'Research Office', `user_id` = '8ac083eb8c3ef0c7018c60ef5b103850', `user_name` = 'huxing2', `email` = '13469962889@139.com', `study_role` = 'CUSTODIAN', `role_name` = 'Custodian', `designation` = NULL, `profile_snapshot_id` = NULL, `group_value_id` = NULL, `to_del` = 0, `update_time` = NULL, `update_by` = NULL, `tenant_id` = '558f236ef19a4f559126a78c00255250', `version` = 0, `create_time` = '2024-08-05 10:51:30.387000', `create_by` = '8ac083eb8c3ef0c7018c60ef5b103850', `last_modify_time` = NULL, `sys_last_modify_time` = NULL, `last_modify_by` = NULL, `is_deleted` = 0, `sync_study` = 1, `record_source` = 'SDB_APP' WHERE `id` = '8ac0819d9117daf801912072f4d30059';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
SELECT
	`t`.`id` AS `study_id`,
	`ts`.`id` AS `submission_id`,
	`sss`.`id` AS `submision_site_id`,
	`ssm`.`id` AS `ssm_id`,
	`ts`.`form_outcome_date` AS `form_outcome_date`,(
		`ts`.`form_outcome_date` + INTERVAL 2 YEAR 
	) AS `two_years_form_outcome_date`,
	`ssm`.`user_name` AS `user_name`,
	`ssm`.`designation` AS `designation`,
	`ssm`.`department_name` AS `department_name`,
	`ssm`.`site_name` AS `site_name`,
	`ts`.`form_ref` AS `form_ref`,
	`t`.`title` AS `title`,
	`t`.`study_ref` AS `study_ref`,
	`ssm`.`role_name` AS `role_name` 
FROM
	(((
				`t_sdb_submission_site_member` `ssm`
				LEFT JOIN `t_sdb_submission` `ts` ON (((
							`ts`.`id` = `ssm`.`submission_id` 
							) 
					AND ( `ts`.`is_deleted` = 0 ))))
			LEFT JOIN `t_sdb_submission_site` `sss` ON (((
						`sss`.`submission_id` = `ssm`.`submission_id` 
						) 
					AND ( `sss`.`site_id` = `ssm`.`site_id` ) 
				AND ( `sss`.`is_deleted` = 0 ))))
		LEFT JOIN `t_sdb_study` `t` ON (((
					`t`.`id` = `ts`.`study_id` 
					) 
			AND ( `t`.`is_deleted` = 0 )))) 
WHERE
	((
			`ssm`.`is_deleted` = 0 
		) 
	AND ( `ssm`.`study_role` = 'CUSTODIAN' ))
	AND submission_id='$SUBMISSION_ID$'

问题处理

模版导出缺失部分数据

原因

大数据服务在执行视图SQL时会报错,需要对应的同学去排查

同一个模版中,如果取值不一样,不能配置成相同的标签