Hawthorn-Api

# HtImport 导入说明

Import 导入案例；
Author wangdongyang@expservice.com.cn

# 前端示例

导入模版下载 
<!-- 注：params上传附件参数 -->
<!-- 注：v-if 条件渲染  :visible 属性绑定，表示弹框的显示隐藏  .sync双向绑定同步修改visible的值 -->
<template>
  <div>
    <ht-table ref="tableListImport" ref-table="tableListImport" 
      :table-head="tableHeadImport" 
      :table-data="tableDataImport">
        <ht-button biz-type="downloadTemplate" @click="downloadImportTemplate()">导入模版下载</ht-button>
        <!-- 注：标签<template slot="import">实际开发需要放开 -->
        <!-- <template slot="import">
        <ht-import-dialog  :params="importParams" :cb="handleDelImport" />
      </template> -->
    </ht-table>
    <import-dialog v-if="importDialogVisible" :visible.sync="importDialogVisible" @handleSearch="handleSearch" />
  </div>
</template>
<script>
// import { importExcelData } from '@/api/ht.admin';
// import { downloadTemplate } from '@/utils';
// import importDialog from '@/views/oem/oms/.../module/importDialog';
export default {
  // 注：components 实际开发需要放开
  // components: { importDialog },
  data() {
    return {
      // 注：importExcelData 导入url路径
      // importPlan: importExcelData,
      importDialogVisible: false,
      // 注：1.sentury.oms对应后端  2.importTmp对应后端插入和检查临时表方法 3.maxColumn列数 4.maxBlankRows行数
      importParams: { bizType: 'sentury.oms@importTmp', maxColumn: 5, maxBlankRows: 3 },
      tableHeadImport: [
        {
          label: '用户ID',
          prop: 'id',
          align: 'left',
          width: '130px',
          query: true
        },
        {
          label: '姓名',
          prop: 'name',
          align: 'left',
          width: '130px'
        },
        {
          label: '手机号',
          prop: 'phone',
          width: 'auto',
          query: true
        },
        {
          label: '地址',
          prop: 'address',
          width: 'right'
        }
      ],
      tableDataImport:{},
    }
  },
  mounted() {
    this.handleSearch();
  },
  
  methods:{
    async handleSearch(event) {
      try {
        /**
         * @todo: 拼接查询条件---模拟数据
         * @author:  wangdongyang@expservice.com.cn
         * @Date: 2023-05-25 15:33:35
         */        
        this.tableDataImport = { 'current': 1, 'pages': 2, 'size': 3, 'total': 5, 
        'hitCount': true, 'searchCount': true, 'orders': [], 'optimizeCountSql': true,
          'records':
         [{ 'transHash': {}},
           { 'id': '1011', 'name': '王先生', 'phone': '10086', 'address': '吉林省长春市朝阳区533号', 'rownums': 1 },
           { 'id': '1012', 'name': '李先生', 'phone': '10010', 'address': '北京省北京市123号', 'rownums': 2 },
           { 'id': '1013', 'name': '朱先生', 'phone': '13560024570', 'address': '上海市', 'rownums': 3 },
         ] };
      } catch (e) {
        this.$notify.message(e, 'error');
      }
    },
    /**
     * @todo: 导入模板方法
     * @author: wangdongyang@expservice.com.cn
     * @Date: 2023-05-26 11:54:34
     */    
    async downloadImportTemplate() {
      // 注 ：上传模板位置-系统管理>配置管理>导入上传
      await downloadTemplate('import.xls');
    },
    /**
     * @todo: 弹出页面
     * @author: wangdongyang@expservice.com.cn
     * @Date: 2023-05-26 11:54:34
     */ 
    handleDelImport() {
      // 注 : importDialogVisible控制是否弹出页面
      this.importDialogVisible = true;
    }
  }
};
</script>
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
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
Expand Copy

# 前端示例(导入查询页面)

<!-- 注：type="primary"按钮样式 -->
<template>
  <ht-dialog title="导入数据" :visible.sync="isShow">
    <ht-tabs tab-position="top" @tab-click="handleTab">
      <ht-tab-pane label="导入正确数据">
        <ht-table ref="importSuccessList" ref-table="importSuccessList" :table-head="importSuccessHead" :table-data="importSuccessData" table-type="import" />
      </ht-tab-pane>
      <ht-tab-pane label="导入错误数据">
        <ht-table ref="importErrorList" ref-table="importErrorList" :table-head="importErrorHead" :table-data="importErrorData" table-type="import" />
      </ht-tab-pane>
    </ht-tabs>
    <!-- 注：标签<template #footer>实际开发需要放开 -->
    <!-- <template #footer>
      <ht-button type="primary" @click="handleExport">导出错误数据</ht-button>
      <ht-button type="primary" :disabled="isSave" @click="handleConfirm">导入正确数据</ht-button>
    </template> -->
  </ht-dialog>
</template>
<script>
// import { sleep, exportError } from '@/utils';
// import { queryImportSuccessResult, queryImportErrorResult, saveImportData, exportImportError } from '@/api/oem/oms/baseMng/...';
export default {
  // 注：name 实际开发需要放开
  // name: 'importDialogEdit',
  props: {
    visible: {
      type: Boolean,
      required: false,
      default: false
    }
  },
  data() {
    return {
      isShow: this.visible,
      importDialogVisible: this.visible,
      isSave: false,
      isShowError: false,
      importSuccessHead: [
        {
          label: '姓名',
          prop: 'name'
        },
        {
          label: '手机号',
          prop: 'phone'
        },
        {
          label: '地址',
          prop: 'address'
        },
        {
          label: '状态',
          prop: 'status'
        }
      ],
      importSuccessData: {},
      importErrorHead: [
        {
          label: '姓名',
          prop: 'name'
        },
        {
          label: '手机号',
          prop: 'phone'
        },
        {
          label: '地址',
          prop: 'address'
        },
        {
          label: '状态',
          prop: 'status'
        },
        {
          label: '错误信息',
          prop: 'errorRemark'
        }
      ],
      importErrorData: {}
    };
  },
  watch: {
    isShow(newValue, oldValue) {
      sleep(200).then(() => {
        this.$emit('update:visible', newValue);
      });
    }
  },
  created() {
    // 注：this.handleQuerySuccess();实际开发需要放开
    // this.handleQuerySuccess();
  },
  methods: {
    /**
     * @todo: 保存
     * @author: wangdongyang@expservice.com.cn
     * @Date: 2023-05-26 17:47:24
     */
    async handleConfirm() {
      try {
        // 注：saveImportData保存方法 isShow是否展示
        await this.$crud.submit(saveImportData, {});
        this.isSave = true;
        this.isShow = false;
        this.$emit('handleSearch');
      } catch (e) {
        this.$notify.message(e, 'error');
      }
    },
    /**
     *@todo: 导出错误数据
     * @author: wangdongyang@expservice.com.cn
     *@Date: 2023-05-26 17:47:39
     */
    handleExport() {
      // 注： exportImportError导出方法
      exportError(this.importErrorHead, exportImportError);
    },
    handleTab(vm) {
      if (vm.index === '1' && !this.isShowError) {
        this.isShowError = true;
        // 注：查询错误数据方法
        this.handleQueryError();
      }
    },
    /**
     * @todo: 查询正确数据方法
     * @author: wangdongyang@expservice.com.cn
     * @Date: 2023-05-29 17:37:37
     */
    
    async handleQuerySuccess() {
      // 注：queryImportSuccessResult 查询正确数据方法
      this.importSuccessData = await this.$crud.fetchTable('', queryImportSuccessResult, {});
    },
    /**
     *@todo: 查询错误数据方法
     * @author: wangdongyang@expservice.com.cn
     *@Date: 2023-05-29 17:37:37
     */  
    async handleQueryError() {
      // 注：queryImportErrorResult 查询错误数据方法
      this.importErrorData = await this.$crud.fetchTable('', queryImportErrorResult, {});
    }
  }
};
</script>
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
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
Expand Copy

# 后端示例

  • ImportExlSaveTmp(导入excel保存至临时表类)
  @Resource
  private CsImportTmpMapper csImportTmpMapper;

  /**
   * @remark: 插入临时表:CsImportTmpPO
   * @param rownums
   * @param list
   * @param userAccount
   * @param bizPk
   * @param delFlag
   * @param params 
   * @return: void 
   * @author: wangdongyang
   * @date: 2023/5/30 16:20
   * @version: 1.0.1 
   * Modification History:
   * Date       Author         Version        Description
   * -----------------------------------------------------------
   * 2023/5/30  wangdongyang   v1.0.1         init
   */
  public void importTmp(int rownums, List<Map<Integer, Object>> list, String userAccount, Long bizPk, Boolean delFlag, Map<String, Object> params)
  {
    // 注：删除临时表数据（根据用户）
    if (delFlag == true)
    {
      LambdaQueryWrapper<CsImportTmpPO> qw = Wrappers.lambdaQuery();
      qw.eq(CsImportTmpPO::getAccount, userAccount);
      csImportTmpMapper.delete(qw);
    }
    List<CsImportTmpPO> csImportTmpPOS = new ArrayList<>();
    AtomicInteger rowNums = new AtomicInteger((rownums + Integer.valueOf(params.get("maxBlankRows").toString())));
    for (Map<Integer, Object> map : list)
    {
      CsImportTmpPO importTmpPO = new CsImportTmpPO();
      importTmpPO.setRowNums(rowNums.getAndIncrement());
      importTmpPO.setAccount(userAccount);
      importTmpPO.setName(MyUtil.getValue(map.get(0)));
      importTmpPO.setPhone(MyUtil.getValue(map.get(1)));
      importTmpPO.setAddress(MyUtil.getValue(map.get(2)));
      importTmpPO.setStatus(MyUtil.getValue(map.get(3)));
      // 注：setDataCheck(0)校验数据结果，0正确数据 1错误数据
      importTmpPO.setDataCheck(0);
      csImportTmpPOS.add(importTmpPO);
    }
    csImportTmpMapper.saveBatch(csImportTmpPOS, CsImportTmpPO.class);
  }
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
39
40
41
42
43
44
45
46
  • ImportExlCheckTmp(excel导入临时表的数据校验)
  @Resource
  private CsImportTmpMapper csImportTmpMapper;

  /**
   * @remark: 对插入临时表的数据进行校验
   * @param userAccount
   * @param bizPk
   * @param params 
   * @return: java.util.List<com.hawthorn.platform.model.dto.ExcelErrorDTO> 
   * @author: wangdongyang
   * @date: 2023/5/30 16:21
   * @version: 1.0.1 
   * Modification History:
   * Date       Author         Version        Description
   * -----------------------------------------------------------
   * 2023/5/30  wangdongyang   v1.0.1         init
   */
  public List<ExcelErrorDTO> importTmp(String userAccount, Long bizPk, Map<String, Object> params) throws IllegalAccessException
  {
    // 注：list用来存放错误信息
    List<ExcelErrorDTO> saveErrorList = new LinkedList<>();

    // 注：将临时表数据取出
    LambdaQueryWrapper<CsImportTmpPO> qw = Wrappers.lambdaQuery();
    qw.eq(CsImportTmpPO::getAccount, userAccount);
    List<CsImportTmpPO> list = csImportTmpMapper.selectList(qw);

    // 注：用来存放校验失败需要保存的数据
    List<Map<String, Object>> tmpList = checkDataService.checkDataRule(list);

    // 注：验证客户是否存在
    List<Map<String, Object>> checkCustomerList = csImportTmpMapper.checkCustomer(bizPk);
    tmpList.addAll(checkCustomerList);

    // 注：验证excel中是否重复
    List<Map<String, Object>> checkRepeatList = csImportTmpMapper.checkRepeat();
    tmpList.addAll(checkRepeatList);

    if (!CollectionUtils.isEmpty(tmpList))
    {
      csImportTmpMapper.updateBatchByRowNum(tmpList);
    }
    return saveErrorList;
  }
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
39
40
41
42
43
44
  • Mapper(check临时表Mapper层)
  /**
   * @version v1.0.1 
   * @copyright: Copyright (c)
   * @remark: check临时表Mapper层 
   * @author:wangdongyang
   * @date:2023/5/30 16:33
   */
  @Repository
  public interface CsImportTmpMapper extends DBMapper<CsImportTmpPO>
  {
    // 注：导入验证客户是否存在
    @SelectProvider(CsImportTmpSqlProvider.class)
    List<Map<String, Object>> checkCustomer(Long bizPk);

    // 注：导入验证excel中数据是否重复
    @SelectProvider(CsImportTmpSqlProvider.class)
    List<Map<String, Object>> checkRepeat();

    // 注：更新错误信息到临时表
    @UpdateProvider(CsImportTmpSqlProvider.class)
    boolean updateBatchByRowNum(@Param("list") List<Map<String, Object>> list);
  }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
  • Provider(查询临时表校验sql)
  /**
   * @remark: 验证客户是否存在
   * @param  
   * @return: java.lang.String 
   * @author: wangdongyang
   * @date: 2023/5/30 16:35
   * @version: 1.0.1 
   * Modification History:
   * Date       Author         Version        Description
   * -----------------------------------------------------------
   * 2023/5/30  wangdongyang   v1.0.1         init
   */
  public String checkCustomer(Long bizPk)
  {
    StringBuffer sql = new StringBuffer();
    sql.append(" select \n ");
    sql.append(" row_nums   rowNums,  \n ");
    sql.append(" CONCAT('第',row_nums,'行客户名称不存在数据库中') errorRemark \n");
    sql.append(" from \n ");
    sql.append(" cs_oem_se_ba_part_month_tmp t  \n ");
    sql.append(" where \n ");
    sql.append(" not exists ( \n ");
    sql.append(" select \n ");
    sql.append(" 1  \n ");
    sql.append(" from \n ");
    sql.append(" base_customer a  \n ");
    sql.append(" where \n ");
    sql.append(" t.PART_NAME = a.customer_name  \n ");
    sql.append(" )");
    sql.append(" and t.account = '" + ContextHolder.getUserAccount() + "'");
    return sql.toString();
  }

  /**
   * @remark: 验证excel中是否重复
   * @param  
   * @return: java.lang.String 
   * @author: wangdongyang
   * @date: 2023/5/30 16:35
   * @version: 1.0.1 
   * Modification History:
   * Date       Author         Version        Description
   * -----------------------------------------------------------
   * 2023/5/30  wangdongyang   v1.0.1         init
   */
  public static String checkRepeat()
  {
    StringBuffer sql = new StringBuffer();
    sql.append(" select a.row_nums rowNums, concat('第', a.row_nums, '行数据存在重复') errorRemark");
    sql.append(" from cs_oem_se_ba_part_month_tmp a ");
    sql.append(" where exists (");
    sql.append(" select 1");
    sql.append(" from cs_oem_se_ba_part_month_tmp b");
    sql.append(" where a.PART_NAME = b.PART_NAME");
    sql.append(" and b.account = '" + ContextHolder.getUserAccount() + "'\n");
    sql.append(" group by b.PART_NAME having COUNT(1) > 1)");
    sql.append(" and a.account = '" + ContextHolder.getUserAccount() + "'");
    return sql.toString();
  }

  /**
   * @remark: 更新错误信息到临时表
   * @param  
   * @return: java.lang.String 
   * @author: wangdongyang
   * @date: 2023/5/30 16:35
   * @version: 1.0.1 
   * Modification History:
   * Date       Author         Version        Description
   * -----------------------------------------------------------
   * 2023/5/30  wangdongyang   v1.0.1         init
   */
  public static String updateBatchByRowNum(List<Map<String, Object>> list)
    {
        StringBuffer sql = new StringBuffer();
        for (Map<String, Object> map : list)
        {
            sql.append("  update cs_oem_se_ba_part_month_tmp set\n ");
            sql.append("  data_check = '1',\n ");
            sql.append("  error_remark = CONCAT_WS('，',error_remark,'" + map.get("errorRemark") + "')\n ");
            sql.append("  where row_nums = " + map.get("rowNums") + ";\n ");
        }
        return sql.toString();
    }
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
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
  • Controller
  /**
   * @remark: 查询导入成功数据
   * @param params 
   * @return: com.baomidou.mybatisplus.core.metadata.IPage<java.util.Map<java.lang.String,java.lang.Object>> 
   * @author: wangdongyang
   * @date: 2023/5/30 16:41
   * @version: 1.0.1 
   * Modification History:
   * Date       Author         Version        Description
   * -----------------------------------------------------------
   * 2023/5/30  wangdongyang   v1.0.1         init
   */
  @ApiOperation(value = "查询->导入成功结果", notes = "查询导入成功结果")
  @ApiImplicitParams({
      @ApiImplicitParam(name = "params", value = "查询条件", dataTypeClass = String.class)
  })
  @PostMapping("/queryImportSuccessResult")
  public IPage<Map<String, Object>> queryImportSuccessResult(@RequestBody String params)
  {
    Page<Map<String, Object>> page = new Page<>();
    String conditions = MyUtil.getConditionsWhere(params, page);
    return csImportService.queryImportSuccessResult(page, conditions);
  }

  /**
   * @remark: 查询导入失败数据
   * @param params 
   * @return: com.baomidou.mybatisplus.core.metadata.IPage<java.util.Map<java.lang.String,java.lang.Object>> 
   * @author: wangdongyang
   * @date: 2023/5/30 16:41
   * @version: 1.0.1 
   * Modification History:
   * Date       Author         Version        Description
   * -----------------------------------------------------------
   * 2023/5/30  wangdongyang   v1.0.1         init
   */
  @ApiOperation(value = "查询->导入失败结果", notes = "查询导入失败结果")
  @ApiImplicitParams({
      @ApiImplicitParam(name = "params", value = "查询条件", dataTypeClass = String.class)
  })
  @PostMapping("/queryImportErrorResult")
  public IPage<Map<String, Object>> queryImportErrorResult(@RequestBody String params)
  {
    Page<Map<String, Object>> page = new Page<>();
    String conditions = MyUtil.getConditionsWhere(params, page);
    return csImportService.queryImportErrorResult(page, conditions);
  }

  /**
   * @remark: 导出错误数据
   * @param response
   * @param param 
   * @return: void 
   * @author: wangdongyang
   * @date: 2023/5/30 16:42
   * @version: 1.0.1 
   * Modification History:
   * Date       Author         Version        Description
   * -----------------------------------------------------------
   * 2023/5/30  wangdongyang   v1.0.1         init
   */
  @ApiOperation(value = "导出->导出错误数据", notes = "导出错误数据")
  @ApiImplicitParams({
      @ApiImplicitParam(name = "response", value = "response", dataTypeClass = HttpServletResponse.class),
      @ApiImplicitParam(name = "params", value = "params", dataTypeClass = String.class)
  })
  @PostMapping(value = "/exportImportError")
  public void exportImportError(HttpServletResponse response, @RequestBody Map<String, Object> param) throws IOException
  {
    // 注：param数据不为空判断
    AssertMyUtil.notNull(param, BizCode.VARIABLE_NOT_EMPTY, "param");
    csImportService.exportImportError(response, "导出错误数据", param);
  }

  // 注：保存导入数据
  @ApiOperation(value = "保存->导入数据", notes = "保存导入数据")
  @PostMapping("/saveImportData")
  public void saveImportData(Map<String, String> map)
  {
    csImportService.saveImportData(map);
  }
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
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
  • Service
  // 注：查询导入成功数据
  IPage<Map<String, Object>> queryImportSuccessResult(Page<Map<String, Object>> page, String conditions);

  // 注：查询导入失败数据
  IPage<Map<String, Object>> queryImportErrorResult(Page<Map<String, Object>> page, String conditions);

  // 注：导出-导入错误数据
  void exportImportError(HttpServletResponse response, String fileName, Map<String, Object> params) throws IOException;

  // 注：保存导入数据
  void saveImportData(Map<String, String> map);
1
2
3
4
5
6
7
8
9
10
11
12
  • Impl
  /**
   * @remark: 注：查询导入成功数据
   * @param page
   * @param conditions 
   * @return: com.baomidou.mybatisplus.core.metadata.IPage<java.util.Map<java.lang.String,java.lang.Object>> 
   * @author: wangdongyang
   * @date: 2023/5/30 16:49
   * @version: 1.0.1 
   * Modification History:
   * Date       Author         Version        Description
   * -----------------------------------------------------------
   * 2023/5/30  wangdongyang   v1.0.1         init
   */
  @Override
  public IPage<Map<String, Object>> queryImportSuccessResult(Page<Map<String, Object>> page, String conditions)
  {
    return transResultService.transResult(csImportMapper.queryImportSuccessResult(page, conditions), new HashMap<>());
  }

  /**
   * @remark: 查询导入失败数据
   * @param page
   * @param conditions 
   * @return: com.baomidou.mybatisplus.core.metadata.IPage<java.util.Map<java.lang.String,java.lang.Object>> 
   * @author: wangdongyang
   * @date: 2023/5/30 16:49
   * @version: 1.0.1 
   * Modification History:
   * Date       Author         Version        Description
   * -----------------------------------------------------------
   * 2023/5/30  wangdongyang   v1.0.1         init
   */
  @Override 
  public IPage<Map<String, Object>> queryImportErrorResult(Page<Map<String, Object>> page, String conditions)
  {
    return csImportMapper.queryImportErrorResult(page, conditions);
  }

  /**
   * @remark: 导入的错误数据
   * @param response
   * @param fileName
   * @param params 
   * @return: void 
   * @author: wangdongyang
   * @date: 2023/5/30 16:49
   * @version: 1.0.1 
   * Modification History:
   * Date       Author         Version        Description
   * -----------------------------------------------------------
   * 2023/5/30  wangdongyang   v1.0.1         init
   */
  @Override
  public void exportImportError(HttpServletResponse response, String fileName, Map<String, Object> params) throws IOException
  {
    String header = MyUtil.getValue(params.get("header"));
    String param = MyUtil.getValue(params.get("params"));
    String qc = MyUtil.getConditionsWhere(param, null);
    List<List<String>> headList = new ArrayList<>();
    List<String> headFields = excelApiService.toHeader(header, headList);
    String fields = ArrayMyUtil.join(headFields.toArray(), ",");
    List<Map<String, Object>> dataList = csImportMapper.exportImportError(fields, qc);
    HashMap<String, String> map = new HashMap<>();
    excelApiService.export(response, fileName, headFields, headList, dataList, map);
  }

  /**
   * @remark: 注：保存导入数据
   * @param map 
   * @return: void 
   * @author: wangdongyang
   * @date: 2023/5/30 16:50
   * @version: 1.0.1 
   * Modification History:
   * Date       Author         Version        Description
   * -----------------------------------------------------------
   * 2023/5/30  wangdongyang   v1.0.1         init
   */
  @Override
  public void saveImportData(Map<String, String> map)
  {
    map.put("companyId",String.valueOf(redisMyClient.getDeptInfo(ContextHolder.getDeptId()).get("companyId").toString()));
    csImportMapper.queryImportSuccessResultList(map);
  }
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
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
  • Mapper
  // 注：查询导入成功数据
  @SelectProvider(CsImportProvider.class)
  IPage<Map<String, Object>> queryImportSuccessResult(Page<Map<String, Object>> page, String qc);

  // 注：查询导入失败数据
  @SelectProvider(CsImportProvider.class)
  IPage<Map<String, Object>> queryImportErrorResult(Page<Map<String, Object>> page, String qc);

  // 注：导出-导入错误数据
  @SelectProvider(CsImportProvider.class)
  List<Map<String, Object>> exportImportError(String fields, String qc);

  // 注：保存导入数据  
  @SelectProvider(CsImportProvider.class)
  List<Map<String, Object>> queryImportSuccessResultList(Map<String, String> map);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
  • Provide
  /**
     * @remark: 查询导入成功数据
     * @param page
     * @param qc 
     * @return: java.lang.String 
     * @author: wangdongyang
     * @date: 2023/5/30 16:45
     * @version: 1.0.1 
     * Modification History:
     * Date       Author         Version        Description
     * -----------------------------------------------------------
     * 2023/5/30  wangdongyang   v1.0.1         init
     */
  public static String queryImportSuccessResult(Page<Map<String, Object>> page, String qc) {
    StringBuffer sql = new StringBuffer();
    sql.append(" select \n ");
    sql.append("  name, \n ");
    sql.append("  phone, \n ");
    sql.append("  address, \n ");
    sql.append("  status \n ");
    sql.append(" from \n ");
    sql.append("  cs_oem_se_ba_part_month_tmp \n ");
    sql.append(" where ");
    sql.append(qc);
    sql.append(" and data_check = '0'  \n ");
    sql.append(" and account = '" + ContextHolder.getUserAccount() + "'");
    return sql.toString();
  }

  /**
     * @remark: 查询导入失败数据
     * @param page
     * @param qc 
     * @return: java.lang.String 
     * @author: wangdongyang
     * @date: 2023/5/30 16:45
     * @version: 1.0.1 
     * Modification History:
     * Date       Author         Version        Description
     * -----------------------------------------------------------
     * 2023/5/30  wangdongyang   v1.0.1         init
     */
  public static String queryImportErrorResult(Page<Map<String, Object>> page, String qc) {
    StringBuffer sql = new StringBuffer();
    sql.append(" select \n ");
    sql.append("  name, \n ");
    sql.append("  phone, \n ");
    sql.append("  address, \n ");
    sql.append("  error_remark    errorRemark  \n ");
    sql.append(" from \n ");
    sql.append("  cs_oem_se_ba_part_month_tmp \n ");
    sql.append(" where ");
    sql.append(qc);
    sql.append(" and data_check = '1'  \n ");
    sql.append(" and account = '" + ContextHolder.getUserAccount() + "'");
    sql.append(" order by row_nums");
    return sql.toString();
  }

  /**
     * @remark: 导出-导入错误数据
     * @param fields
     * @param qc 
     * @return: java.lang.String 
     * @author: wangdongyang
     * @date: 2023/5/30 16:46
     * @version: 1.0.1 
     * Modification History:
     * Date       Author         Version        Description
     * -----------------------------------------------------------
     * 2023/5/30  wangdongyang   v1.0.1         init
     */
  public static String exportImportError(String fields, String qc) {
    StringBuffer sql = new StringBuffer();
    sql.append(" select\n ");
    sql.append(fields);
    sql.append(" from cs_oem_se_ba_part_month_tmp a");
    sql.append(" where");
    sql.append(qc);
    sql.append(" and account = '" + ContextHolder.getUserAccount() + "'");
    return sql.toString();
  }

  /**
     * @remark: 保存导入数据
     * @param map 
     * @return: java.lang.String 
     * @author: wangdongyang
     * @date: 2023/5/30 16:47
     * @version: 1.0.1 
     * Modification History:
     * Date       Author         Version        Description
     * -----------------------------------------------------------
     * 2023/5/30  wangdongyang   v1.0.1         init
     */
  public static String queryImportSuccessResultList(Map<String, String> map) {
    StringBuffer sql = new StringBuffer();
    sql.append(" INSERT cs_oem_se_ba_part_month ( \n ");
    sql.append(" name, \n ");
    sql.append(" phone, \n ");
    sql.append(" address, \n ");
    sql.append(" create_by, \n ");
    sql.append(" create_time, \n ");
    sql.append(" company_id, \n ");
    sql.append(" oem_company_id \n ");
    sql.append(" ) SELECT\n ");
    sql.append("     B.name, \n ");
    sql.append("     B.phone, \n ");
    sql.append("     B.address, \n ");
    sql.append("'" + ContextHolder.getUserAccount() + "',");
    sql.append("'" + LocalDateTime.now() + "',");
    sql.append(" '" + map.get("companyId") + "',\n ");
    sql.append(" '" + ContextHolder.getOemCompanyId() + "'\n ");
    sql.append("     FROM \n ");
    sql.append("      cs_oem_se_ba_part_month_tmp B \n ");
    sql.append("     WHERE  \n ");
    sql.append("       B.DATA_CHECK = 0 \n ");
    return sql.toString();
  }
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
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119

# 注


 // 在临时表PO中,需要校验的字段添加注解
 // 导入公共调用方法
    (
      filedName="表头名",
      checkRule={校验规则(非正则表达式)(需要使用{})},
      regex=正则表达式校验规则,
      message="提示信息"
    )
 // 公共校验规则从ExcelCheckConstant获取
 @CheckData
    (
      filedName = "数量", 
      checkRule = {ExcelCheckConstant.IS_NULL, ExcelCheckConstant.IS_REGEX}, 
      regex = ExcelCheckConstant.IS_PURE_NUMBER_0, 
      message = "必须为数字，并且小于等于6位"
    )
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

# 版本

  • v1.0.0

← 导出 →