前端
<el-button type="primary" size="small" @click="uploadBalance" icon="el-icon-upload">
导入
</el-button>
<el-dialog
title="导入员工假期时效性"
:visible.sync="uploadBalanceDialog"
:append-to-body="true"
:close-on-click-modal="false"
width="800px"
>
<div style="height: 400px">
<el-upload
:before-upload="beforeUpload"
:http-request="uploadSubmit"
:show-file-list="false"
class="upload-demo"
action="/"
drag
accept=".xls,.xlsx"
style="margin-top: 20px;margin-left: 195px;width: 360px"
>
<i class="el-icon-upload"></i>
<div class="el-upload__text">将文件拖到此处，或<em>点击上传</em></div>
<div slot="tip" class="el-upload__tip" style="margin-left: 125px">只能上传xls/xlsx文件</div>
</el-upload>
<el-button type="success" style="margin-left: 335px;margin-top: 15px" @click="templateDownload">模板下载</el-button>
</div>
</el-dialog>
//导入
uploadBalance(){
this.uploadBalanceDialog=!this.uploadBalanceDialog
},
beforeUpload(file) {
const msg = file.name.substring(file.name.lastIndexOf('.') + 1)
const checkResult = msg === 'xlsx' || msg === 'xls'
if (!checkResult) {
this.$message.error('上传格式错误')
} else {
this.file = file
}
return checkResult
},
//导入余额时效性
uploadSubmit() {
const loadingInstance = Loading.service({
fullscreen: true,
text: '正在上传中，请稍后...',
background: 'rgba(255,255,255,0.7)'
})
const file = new FormData()
file.append('file', this.file)
uploadBalance(file, true).then((data) => {
loadingInstance.close()
this.$alert(data.data.msg, '提示', {
confirmButtonText: '确定'
}).then(() => {
this.$nextTick(() => {
this.getMessage()
})
}).catch(() => {
this.$nextTick(() => {
this.getMessage()
})
})
this.uploadBalanceDialog = !this.uploadBalanceDialog
}).catch(() => {
this.$message.error('服务器内部出错，上传失败！')
loadingInstance.close()
})
},
templateDownload() {
const a = document.createElement('a')
a.style.display = 'none'
a.download = '员工余额时效性示例.xlsx'
a.href = window.SITE_CONFIG.templateDownloadURl+'balanceTemplate.xlsx'
a.click()
},
后端
1.controller
/**
* 导入员工假期余额时效性
*/
@RequestMapping(value = "/holidayBalance.do",method = RequestMethod.POST)
public Response uploadBalance(HttpServletResponse response, MultipartFile file, HttpSession session){
response.setContentType("text/html;charset=UTF-8");
AuthInfo authInfo = (AuthInfo) session.getAttribute(ConstantUtil.AUTH_INFO);
long staffId = authInfo.getStaffId();
InputStream in=null;
Map<String, Object> map = new HashMap<>();
try{
in = file.getInputStream();
this.holidayBalanceService.uploadBalance(staffId,in);
map.put("code", 200);
map.put("msg", "上传成功");
}catch (Exception e){
map.put("code", 405);
if (e instanceof ExcelAnalysisException) {
map.put("msg", e.getMessage());
} else {
e.printStackTrace();
map.put("msg", "上传错误，请联系管理员！");
}
} finally {
if (in != null) {
try {
in.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return Response.success(map);
}
2.service
@Transactional(rollbackFor = Exception.class)
public Map<String, Object> uploadBalance(long staffId, InputStream in) throws Exception {
Map<String, Object> map = new HashMap<>();
List<Map<String, Object>> sysStaff =holidayBalanceDao.selectSysStaff();
List<Map<String, Object>> holidayRuleId = holidayBalanceDao.selectHolidayRuleId();
EasyExcel.read(in, new HolidayBalanceListener(staffId,sysStaff,holidayRuleId,holidayBalanceDao)).sheet().headRowNumber(2).doRead();
return map;
}
3.dao
@Override
public List<Map<String, Object>> selectSysStaff() {
String sql = "SELECT staff_id,user_id,operator_name FROM sys_staff";
return this.getJdbcTemplate().queryForList(sql);
}
@Override
public List<Map<String, Object>> selectHolidayRuleId() {
String sql = "select HOLIDAY_RULE_ID,HOLIDAY_NAME from sys_holiday_rules";
return this.getJdbcTemplate().queryForList(sql);
}
@Override
public void batchInsert(List<HolidayBalanceDto> addBatchArgs) {
String sql = "insert into sys_holiday_inst(staff_Id,HOLIDAY_RULE_ID,HOLIDAY_BALANCE,EFFECT_TIME,DISABLE_TIME,CREATE_TIME,CREATE_STAFF,update_time,UPDATE_STAFF) \n" +
"values(?,?,?,STR_TO_DATE(?,'%Y%m%d%H%i'),STR_TO_DATE(?,'%Y%m%d%H%i'),now(),?,now(),?)";
List<Object[]> batchArgs = new ArrayList<Object[]>();
for (int i = 0; i < addBatchArgs.size(); i++) {
this.getJdbcTemplate().update(sql,new Object[]{
addBatchArgs.get(i).getStaffId(),addBatchArgs.get(i).getHolidayRuleId(),addBatchArgs.get(i).getHolidayBalance(),addBatchArgs.get(i).getEffectTime(),addBatchArgs.get(i).getDisableTime(),addBatchArgs.get(i).getCreateStaff(),addBatchArgs.get(i).getUpdateStaff()
});
}
}
@Override
public void batchUpdate(List<HolidayBalanceDto> editBatchArgs) {
String sql = "update sys_holiday_inst set HOLIDAY_BALANCE=?,EFFECT_TIME=STR_TO_DATE(?,'%Y%m%d%H%i'),DISABLE_TIME=STR_TO_DATE(?,'%Y%m%d%H%i'),CREATE_TIME=now(),CREATE_STAFF=?,update_time=now(),UPDATE_STAFF=?" +
"where staff_Id=? and HOLIDAY_RULE_ID=?";
for (int i = 0; i < editBatchArgs.size(); i++) {
this.getJdbcTemplate().update(sql,new Object[]{
editBatchArgs.get(i).getHolidayBalance(),editBatchArgs.get(i).getEffectTime(),editBatchArgs.get(i).getDisableTime(),editBatchArgs.get(i).getCreateStaff(),editBatchArgs.get(i).getUpdateStaff(),editBatchArgs.get(i).getStaffId(),editBatchArgs.get(i).getHolidayRuleId()
});
}
}
@Override
public Integer getCount(Long staffId, Long holidayRuleId) {
String sql="select count(*) from sys_holiday_inst where staff_Id=? and HOLIDAY_RULE_ID=?";
ArrayList<Object> params=new ArrayList<>();
params.add(staffId);
params.add(holidayRuleId);
return this.getJdbcTemplate().queryForObject(sql,int.class,params.toArray());
}
4.utils
package usi.holidayRules.utils;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelDataConvertException;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import com.alibaba.excel.read.metadata.holder.ReadRowHolder;
import org.apache.commons.beanutils.BeanUtils;
import usi.holidayRules.dao.HolidayBalanceDao;
import usi.holidayRules.dto.HolidayBalanceDto;
import usi.scheduleModular.dao.SysAffairsPersonDao;
import usi.scheduleModular.dto.SysAffairsPersonDto;
import usi.scheduleModular.dto.SysAffairsTeamDto;
import java.text.SimpleDateFormat;
import java.util.*;
/**
* 假期时效性导入
* @author qiyingbo
* @date 2023/2/27 17:37
*/
public class HolidayBalanceListener extends AnalysisEventListener<Map<Integer, String>> {
private static final Integer BATCH_COUNT = 1000;//每隔1000条存储数据库，然后清理list ，方便内存回收
private final HolidayBalanceDao holidayBalanceDao;
private final Long staffId;
private List<Map<String, Object>> sysStaff,holidayRuleId;
private final List<HolidayBalanceDto> addBatchArgs = new ArrayList<>();
private final List<HolidayBalanceDto> editBatchArgs = new ArrayList<>();
private List<Integer> indexs = new ArrayList<>(); //记录表头中关键字段
private SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmm");
private Map<String, String> sys_staff_map = new HashMap<>();
private Map<String,Integer> sys_holidayRuleId_map=new HashMap<>();
public HolidayBalanceListener(Long staffId,List<Map<String, Object>> sysStaff,List<Map<String, Object>> holidayRuleId,HolidayBalanceDao holidayBalanceDao) {
this.holidayBalanceDao = holidayBalanceDao;
this.staffId = staffId;
this.sysStaff = sysStaff;
this.holidayRuleId=holidayRuleId;
for (int i = 0; i < sysStaff.size(); i++) {
sys_staff_map.put(String.valueOf(sysStaff.get(i).get("user_id")), String.valueOf(sysStaff.get(i).get("staff_id")));
}
for (int i = 0; i <holidayRuleId.size() ; i++) {
sys_holidayRuleId_map.put(holidayRuleId.get(i).get("HOLIDAY_NAME").toString(),Integer.parseInt(holidayRuleId.get(i).get("HOLIDAY_RULE_ID").toString()));
}
}
//解析表头
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
ReadRowHolder readRowHolder = context.readRowHolder();
if(readRowHolder.getRowIndex()==1) {
CellData cellData;
String value;
boolean existJobNumber = false, existSickLeaveAll = false, existSickLeaveEffectiveDate = false,existSickLeaveExpirationDate=false
, existWinterVacationAll = false, existWinterVacationEffectiveDate = false,existWinterVacationExpirationDate=false
, existAbsenceLeaveAll = false, existAbsenceLeaveEffectiveDate = false,existAbsenceLeaveExpirationDate=false
, existParentalLeaveAll = false, existParentalLeaveEffectiveDate = false,existParentalLeaveExpirationDate=false
, existMaternityLeaveAll = false, existMaternityLeaveEffectiveDate = false,existMaternityLeaveExpirationDate=false
, existMarriageHolidayAll = false, existMarriageHolidayEffectiveDate = false,existMarriageHolidayExpirationDate=false
, existNursingLeaveAll = false, existNursingLeaveEffectiveDate = false,existNursingLeaveExpirationDate=false
, existSmallMaternityLeaveAll = false, existSmallMaternityLeaveEffectiveDate = false,existSmallMaternityLeaveExpirationDate=false
, existMaternitViewyLeaveAll = false, existMaternitViewyLeaveEffectiveDate = false,existMaternitViewyLeaveExpirationDate=false
, existBereavementLeaveAll = false, existBereavementLeaveEffectiveDate = false,existBereavementLeaveExpirationDate=false
, existIncentiveLeaveAll = false, existIncentiveLeaveEffectiveDate = false,existIncentiveLeaveExpirationDate=false
;
int size = headMap.keySet().size();
for (int i = 0; i < size; i++) {
value = headMap.get(i);
value = value == null ? "" : value.trim();
if ("员工工号".equals(value)) {
indexs.add(i);
existJobNumber = true;
} else if ("病假总余额".equals(value)) {
indexs.add(i);
existSickLeaveAll = true;
} else if ("病假生效时间".equals(value)) {
indexs.add(i);
existSickLeaveEffectiveDate = true;
} else if ("病假失效时间".equals(value)) {
indexs.add(i);
existSickLeaveExpirationDate = true;
}else if ("年假总余额".equals(value)) {
indexs.add(i);
existWinterVacationAll = true;
} else if ("年假生效时间".equals(value)) {
indexs.add(i);
existWinterVacationEffectiveDate = true;
} else if ("年假失效时间".equals(value)) {
indexs.add(i);
existWinterVacationExpirationDate = true;
}else if ("事假总余额".equals(value)) {
indexs.add(i);
existAbsenceLeaveAll = true;
} else if ("事假生效时间".equals(value)) {
indexs.add(i);
existAbsenceLeaveEffectiveDate = true;
} else if ("事假失效时间".equals(value)) {
indexs.add(i);
existAbsenceLeaveExpirationDate = true;
}
else if ("育儿假总余额".equals(value)) {
indexs.add(i);
existParentalLeaveAll = true;
} else if ("育儿假生效时间".equals(value)) {
indexs.add(i);
existParentalLeaveEffectiveDate = true;
} else if ("育儿假失效时间".equals(value)) {
indexs.add(i);
existParentalLeaveExpirationDate = true;
}else if ("产假总余额".equals(value)) {
indexs.add(i);
existMaternityLeaveAll = true;
} else if ("产假生效时间".equals(value)) {
indexs.add(i);
existMaternityLeaveEffectiveDate = true;
} else if ("产假失效时间".equals(value)) {
indexs.add(i);
existMaternityLeaveExpirationDate = true;
}else if ("婚假总余额".equals(value)) {
indexs.add(i);
existMarriageHolidayAll = true;
} else if ("婚假生效时间".equals(value)) {
indexs.add(i);
existMarriageHolidayEffectiveDate = true;
} else if ("婚假失效时间".equals(value)) {
indexs.add(i);
existMarriageHolidayExpirationDate = true;
}else if ("护理假总余额".equals(value)) {
indexs.add(i);
existNursingLeaveAll = true;
} else if ("护理假生效时间".equals(value)) {
indexs.add(i);
existNursingLeaveEffectiveDate = true;
} else if ("护理假失效时间".equals(value)) {
indexs.add(i);
existNursingLeaveExpirationDate = true;
}
else if ("小产假总余额".equals(value)) {
indexs.add(i);
existSmallMaternityLeaveAll = true;
} else if ("小产假生效时间".equals(value)) {
indexs.add(i);
existSmallMaternityLeaveEffectiveDate = true;
} else if ("小产假失效时间".equals(value)) {
indexs.add(i);
existSmallMaternityLeaveExpirationDate = true;
}else if ("产检假总余额".equals(value)) {
indexs.add(i);
existMaternitViewyLeaveAll = true;
} else if ("产检假生效时间".equals(value)) {
indexs.add(i);
existMaternitViewyLeaveEffectiveDate = true;
} else if ("产检假失效时间".equals(value)) {
indexs.add(i);
existMaternitViewyLeaveExpirationDate = true;
}else if ("丧假总余额".equals(value)) {
indexs.add(i);
existBereavementLeaveAll = true;
} else if ("丧假生效时间".equals(value)) {
indexs.add(i);
existBereavementLeaveEffectiveDate = true;
} else if ("丧假失效时间".equals(value)) {
indexs.add(i);
existBereavementLeaveExpirationDate = true;
}else if ("奖励休假总余额".equals(value)) {
indexs.add(i);
existIncentiveLeaveAll = true;
} else if ("奖励休假生效时间".equals(value)) {
indexs.add(i);
existIncentiveLeaveEffectiveDate = true;
} else if ("奖励休假失效时间".equals(value)) {
indexs.add(i);
existIncentiveLeaveExpirationDate = true;
}
}
if (!existJobNumber) {
cellData = new CellData("【员工工号】表头未找到");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() +1, 1, cellData, new ExcelContentProperty(), "");
} else if (!existSickLeaveAll) {
cellData = new CellData("【病假总余额】表头未找到");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() +1, 2, cellData, new ExcelContentProperty(), "");
} else if (!existSickLeaveEffectiveDate) {
cellData = new CellData("【病假生效时间】表头未找到");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() +1, 3, cellData, new ExcelContentProperty(), "");
} else if (!existSickLeaveExpirationDate) {
cellData = new CellData("【病假失效时间】表头未找到");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() +1, 4, cellData, new ExcelContentProperty(), "");
}
else if (!existWinterVacationAll) {
cellData = new CellData("【年假总余额】表头未找到");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() +1, 5, cellData, new ExcelContentProperty(), "");
} else if (!existWinterVacationEffectiveDate) {
cellData = new CellData("【年假生效时间】表头未找到");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() +1, 6, cellData, new ExcelContentProperty(), "");
} else if (!existWinterVacationExpirationDate) {
cellData = new CellData("【年假失效时间】表头未找到");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() +1, 7, cellData, new ExcelContentProperty(), "");
}else if (!existAbsenceLeaveAll) {
cellData = new CellData("【事假总余额】表头未找到");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() +1, 8, cellData, new ExcelContentProperty(), "");
} else if (!existAbsenceLeaveEffectiveDate) {
cellData = new CellData("【事假生效时间】表头未找到");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() +1, 9, cellData, new ExcelContentProperty(), "");
} else if (!existAbsenceLeaveExpirationDate) {
cellData = new CellData("【事假失效时间】表头未找到");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() +1, 9, cellData, new ExcelContentProperty(), "");
}else if (!existParentalLeaveAll) {
cellData = new CellData("【育儿假总余额】表头未找到");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() +1, 10, cellData, new ExcelContentProperty(), "");
} else if (!existParentalLeaveEffectiveDate) {
cellData = new CellData("【育儿假生效时间】表头未找到");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() +1, 11, cellData, new ExcelContentProperty(), "");
} else if (!existParentalLeaveExpirationDate) {
cellData = new CellData("【育儿假失效时间】表头未找到");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() +1, 12, cellData, new ExcelContentProperty(), "");
}else if (!existMaternityLeaveAll) {
cellData = new CellData("【产假总余额】表头未找到");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() +1, 13, cellData, new ExcelContentProperty(), "");
} else if (!existMaternityLeaveEffectiveDate) {
cellData = new CellData("【产假生效时间】表头未找到");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() +1, 14, cellData, new ExcelContentProperty(), "");
} else if (!existMaternityLeaveExpirationDate) {
cellData = new CellData("【产假失效时间】表头未找到");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() +1, 15, cellData, new ExcelContentProperty(), "");
}else if (!existMarriageHolidayAll) {
cellData = new CellData("【婚假总余额】表头未找到");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() +1, 16, cellData, new ExcelContentProperty(), "");
} else if (!existMarriageHolidayEffectiveDate) {
cellData = new CellData("【婚假生效时间】表头未找到");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() +1, 17, cellData, new ExcelContentProperty(), "");
} else if (!existMarriageHolidayExpirationDate) {
cellData = new CellData("【婚假失效时间】表头未找到");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() +1, 18, cellData, new ExcelContentProperty(), "");
}else if (!existNursingLeaveAll) {
cellData = new CellData("【护理假总余额】表头未找到");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() +1, 19, cellData, new ExcelContentProperty(), "");
} else if (!existNursingLeaveEffectiveDate) {
cellData = new CellData("【护理假生效时间】表头未找到");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() +1, 20, cellData, new ExcelContentProperty(), "");
} else if (!existNursingLeaveExpirationDate) {
cellData = new CellData("【护理假失效时间】表头未找到");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() +1, 21, cellData, new ExcelContentProperty(), "");
}else if (!existSmallMaternityLeaveAll) {
cellData = new CellData("【小产假总余额】表头未找到");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() +1, 22, cellData, new ExcelContentProperty(), "");
} else if (!existSmallMaternityLeaveEffectiveDate) {
cellData = new CellData("【小产假生效时间】表头未找到");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() +1, 23, cellData, new ExcelContentProperty(), "");
} else if (!existSmallMaternityLeaveExpirationDate) {
cellData = new CellData("【小产假失效时间】表头未找到");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() +1, 24, cellData, new ExcelContentProperty(), "");
}else if (!existMaternitViewyLeaveAll) {
cellData = new CellData("【产检假总余额】表头未找到");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() +1, 25, cellData, new ExcelContentProperty(), "");
} else if (!existMaternitViewyLeaveEffectiveDate) {
cellData = new CellData("【产检假生效时间】表头未找到");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() +1, 26, cellData, new ExcelContentProperty(), "");
} else if (!existMaternitViewyLeaveExpirationDate) {
cellData = new CellData("【产检假失效时间】表头未找到");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() +1, 27, cellData, new ExcelContentProperty(), "");
}else if (!existBereavementLeaveAll) {
cellData = new CellData("【丧假总余额】表头未找到");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() +1, 28, cellData, new ExcelContentProperty(), "");
} else if (!existBereavementLeaveEffectiveDate) {
cellData = new CellData("【丧假生效时间】表头未找到");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() +1, 29, cellData, new ExcelContentProperty(), "");
} else if (!existBereavementLeaveExpirationDate) {
cellData = new CellData("【丧假失效时间】表头未找到");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() +1, 30, cellData, new ExcelContentProperty(), "");
}else if (!existIncentiveLeaveAll) {
cellData = new CellData("【奖励休假总余额】表头未找到");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() +1, 31, cellData, new ExcelContentProperty(), "");
} else if (!existIncentiveLeaveEffectiveDate) {
cellData = new CellData("【奖励休假生效时间】表头未找到");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() +1, 32, cellData, new ExcelContentProperty(), "");
} else if (!existIncentiveLeaveExpirationDate) {
cellData = new CellData("【奖励休假失效时间】表头未找到");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() +1, 33, cellData, new ExcelContentProperty(), "");
}
}
}
//解析一条数据
@Override
public void invoke(Map<Integer, String> map, AnalysisContext analysisContext) {
ReadRowHolder readRowHolder = analysisContext.readRowHolder();
String staff_id = map.get(indexs.get(0));
if(null==staff_id||("").equals(staff_id)){
CellData cellData = new CellData("员工工号信息为空");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() + 1, 1, cellData, new ExcelContentProperty(), "");
}
staff_id = sys_staff_map.get(staff_id);
if(null==staff_id||("").equals(staff_id)){
CellData cellData = new CellData("系统没有对应的工号");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() + 1, 1, cellData, new ExcelContentProperty(), "");
}
String sickLeaveAll=map.get(indexs.get(1));
if(null==sickLeaveAll||("").equals(sickLeaveAll)){
CellData cellData = new CellData("病假总余额为空");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() + 1, 2, cellData, new ExcelContentProperty(), "");
}
String sickLeaveEffectDate=map.get(indexs.get(2));
if(null==sickLeaveEffectDate||("").equals(sickLeaveEffectDate)){
CellData cellData = new CellData("病假生效时间为空");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() + 1, 3, cellData, new ExcelContentProperty(), "");
}
String sickLeaveExpDate=map.get(indexs.get(3));
if(null==sickLeaveExpDate||("").equals(sickLeaveExpDate)){
CellData cellData = new CellData("病假失效时间为空");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() + 1, 4, cellData, new ExcelContentProperty(), "");
}
String winterVacationAll=map.get(indexs.get(4));
if(null==winterVacationAll||("").equals(winterVacationAll)){
CellData cellData = new CellData("年假总余额为空");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() + 1, 5, cellData, new ExcelContentProperty(), "");
}
String winterVacationEffectDate=map.get(indexs.get(5));
if(null==winterVacationEffectDate||("").equals(winterVacationEffectDate)){
CellData cellData = new CellData("年假生效时间为空");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() + 1, 6, cellData, new ExcelContentProperty(), "");
}
String winterVacationExpDate=map.get(indexs.get(6));
if(null==winterVacationExpDate||("").equals(winterVacationExpDate)){
CellData cellData = new CellData("年假失效时间为空");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() + 1, 7, cellData, new ExcelContentProperty(), "");
}
String absenceLeaveAll=map.get(indexs.get(7));
if(null==absenceLeaveAll||("").equals(absenceLeaveAll)){
CellData cellData = new CellData("事假总余额为空");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() + 1, 8, cellData, new ExcelContentProperty(), "");
}
String absenceLeaveEffectDate=map.get(indexs.get(8));
if(null==absenceLeaveEffectDate||("").equals(absenceLeaveEffectDate)){
CellData cellData = new CellData("事假生效时间为空");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() + 1, 9, cellData, new ExcelContentProperty(), "");
}
String absenceLeaveExpDate=map.get(indexs.get(9));
if(null==absenceLeaveExpDate||("").equals(absenceLeaveExpDate)){
CellData cellData = new CellData("事假失效时间为空");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() + 1, 10, cellData, new ExcelContentProperty(), "");
}
String parentalLeaveAll=map.get(indexs.get(10));
if(null==parentalLeaveAll||("").equals(parentalLeaveAll)){
CellData cellData = new CellData("育儿假总余额为空");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() + 1, 11, cellData, new ExcelContentProperty(), "");
}
String parentalLeaveEffectDate=map.get(indexs.get(11));
if(null==parentalLeaveEffectDate||("").equals(parentalLeaveEffectDate)){
CellData cellData = new CellData("育儿假生效时间为空");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() + 1, 12, cellData, new ExcelContentProperty(), "");
}
String parentalLeaveExpDate=map.get(indexs.get(12));
if(null==parentalLeaveExpDate||("").equals(parentalLeaveExpDate)){
CellData cellData = new CellData("育儿假失效时间为空");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() + 1, 13, cellData, new ExcelContentProperty(), "");
}
String marternityLeaveAll=map.get(indexs.get(13));
if(null==marternityLeaveAll||("").equals(marternityLeaveAll)){
CellData cellData = new CellData("产假总余额为空");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() + 1, 14, cellData, new ExcelContentProperty(), "");
}
String marternityLeaveEffectDate=map.get(indexs.get(14));
if(null==marternityLeaveEffectDate||("").equals(marternityLeaveEffectDate)){
CellData cellData = new CellData("产假生效时间为空");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() + 1, 15, cellData, new ExcelContentProperty(), "");
}
String marternityLeaveExpDate=map.get(indexs.get(15));
if(null==marternityLeaveExpDate||("").equals(marternityLeaveExpDate)){
CellData cellData = new CellData("产假失效时间为空");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() + 1, 16, cellData, new ExcelContentProperty(), "");
}
String marriageHolidayAll=map.get(indexs.get(16));
if(null==marriageHolidayAll||("").equals(marriageHolidayAll)){
CellData cellData = new CellData("婚假总余额为空");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() + 1, 17, cellData, new ExcelContentProperty(), "");
}
String marriageHolidayEffectDate=map.get(indexs.get(17));
if(null==marriageHolidayEffectDate||("").equals(marriageHolidayEffectDate)){
CellData cellData = new CellData("婚假生效时间为空");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() + 1, 18, cellData, new ExcelContentProperty(), "");
}
String marriageHolidayExpDate=map.get(indexs.get(18));
if(null==marriageHolidayExpDate||("").equals(marriageHolidayExpDate)){
CellData cellData = new CellData("婚假失效时间为空");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() + 1, 19, cellData, new ExcelContentProperty(), "");
}
String nursingLeaveAll=map.get(indexs.get(19));
if(null==nursingLeaveAll||("").equals(nursingLeaveAll)){
CellData cellData = new CellData("护理假总余额为空");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() + 1, 20, cellData, new ExcelContentProperty(), "");
}
String nursingLeaveEffectDate=map.get(indexs.get(20));
if(null==nursingLeaveEffectDate||("").equals(nursingLeaveEffectDate)){
CellData cellData = new CellData("护理假生效时间为空");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() + 1, 21, cellData, new ExcelContentProperty(), "");
}
String nursingLeaveExpDate=map.get(indexs.get(21));
if(null==nursingLeaveExpDate||("").equals(nursingLeaveExpDate)){
CellData cellData = new CellData("护理假失效时间为空");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() + 1, 22, cellData, new ExcelContentProperty(), "");
}
String smallMaternityAll=map.get(indexs.get(22));
if(null==smallMaternityAll||("").equals(smallMaternityAll)){
CellData cellData = new CellData("小产假总余额为空");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() + 1, 23, cellData, new ExcelContentProperty(), "");
}
String smallMaternityEffectDate=map.get(indexs.get(23));
if(null==smallMaternityEffectDate||("").equals(smallMaternityEffectDate)){
CellData cellData = new CellData("小产假生效时间为空");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() + 1, 24, cellData, new ExcelContentProperty(), "");
}
String smallMaternityExpDate=map.get(indexs.get(24));
if(null==nursingLeaveExpDate||("").equals(nursingLeaveExpDate)){
CellData cellData = new CellData("小产假失效时间为空");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() + 1, 25, cellData, new ExcelContentProperty(), "");
}
String maternityViewAll=map.get(indexs.get(25));
if(null==maternityViewAll||("").equals(maternityViewAll)){
CellData cellData = new CellData("产检假总余额为空");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() + 1, 26, cellData, new ExcelContentProperty(), "");
}
String maternityViewEffectDate=map.get(indexs.get(26));
if(null==maternityViewEffectDate||("").equals(maternityViewEffectDate)){
CellData cellData = new CellData("产检假生效时间为空");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() + 1, 27, cellData, new ExcelContentProperty(), "");
}
String maternityViewExpDate=map.get(indexs.get(27));
if(null==maternityViewExpDate||("").equals(maternityViewExpDate)){
CellData cellData = new CellData("产检假失效时间为空");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() + 1, 28, cellData, new ExcelContentProperty(), "");
}
String bereavementLeaveAll=map.get(indexs.get(28));
if(null==bereavementLeaveAll||("").equals(bereavementLeaveAll)){
CellData cellData = new CellData("丧假总余额为空");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() + 1, 29, cellData, new ExcelContentProperty(), "");
}
String bereavementLeaveEffectDate=map.get(indexs.get(29));
if(null==bereavementLeaveEffectDate||("").equals(bereavementLeaveEffectDate)){
CellData cellData = new CellData("丧假生效时间为空");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() + 1, 30, cellData, new ExcelContentProperty(), "");
}
String bereavementLeaveExpDate=map.get(indexs.get(30));
if(null==bereavementLeaveExpDate||("").equals(bereavementLeaveExpDate)){
CellData cellData = new CellData("丧假失效时间为空");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() + 1, 31, cellData, new ExcelContentProperty(), "");
}
String incentiveLeaveAll=map.get(indexs.get(31));
if(null==incentiveLeaveAll||("").equals(incentiveLeaveAll)){
CellData cellData = new CellData("奖励休假总余额为空");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() + 1, 32, cellData, new ExcelContentProperty(), "");
}
String incentiveLeaveEffectDate=map.get(indexs.get(32));
if(null==incentiveLeaveEffectDate||("").equals(incentiveLeaveEffectDate)){
CellData cellData = new CellData("奖励休假生效时间为空");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() + 1, 33, cellData, new ExcelContentProperty(), "");
}
String incentiveLeaveExpDate=map.get(indexs.get(33));
if(null==incentiveLeaveExpDate||("").equals(incentiveLeaveExpDate)){
CellData cellData = new CellData("奖励休假失效时间为空");
throw new ExcelDataConvertException(readRowHolder.getRowIndex() + 1, 34, cellData, new ExcelContentProperty(), "");
}
if (sickLeaveAll!=null){
HolidayBalanceDto holidayBalanceDto=new HolidayBalanceDto();
holidayBalanceDto.setStaffId(Long.parseLong(staff_id));//员工id
holidayBalanceDto.setHolidayRuleId(Long.valueOf(sys_holidayRuleId_map.get("病假").toString()));//假期规则id
holidayBalanceDto.setCreateStaff(Long.parseLong(staff_id));//创建人
holidayBalanceDto.setUpdateStaff(Long.parseLong(staff_id));//修改人
holidayBalanceDto.setHolidayBalance(Float.valueOf(map.get(indexs.get(1))));//假期余额
holidayBalanceDto.setEffectTime(map.get(indexs.get(2)));//假期生效时间
holidayBalanceDto.setDisableTime(map.get(indexs.get(3)));//假期失效效时间
//查条数
Integer count = this.holidayBalanceDao.getCount(holidayBalanceDto.getStaffId(), holidayBalanceDto.getHolidayRuleId());
if (count==0){
addBatchArgs.add(holidayBalanceDto);
}else {
editBatchArgs.add(holidayBalanceDto);
}
// 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
if ((addBatchArgs.size() + editBatchArgs.size()) >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
addBatchArgs.clear();
editBatchArgs.clear();
}
}
if (winterVacationAll!=null){
HolidayBalanceDto holidayBalanceDto=new HolidayBalanceDto();
holidayBalanceDto.setStaffId(Long.parseLong(staff_id));//员工id
holidayBalanceDto.setHolidayRuleId(Long.valueOf(sys_holidayRuleId_map.get("年假").toString()));//假期规则id
holidayBalanceDto.setCreateStaff(Long.parseLong(staff_id));//创建人
holidayBalanceDto.setUpdateStaff(Long.parseLong(staff_id));//修改人
holidayBalanceDto.setHolidayBalance(Float.valueOf(map.get(indexs.get(4))));//假期余额
holidayBalanceDto.setEffectTime(map.get(indexs.get(5)));//假期生效时间
holidayBalanceDto.setDisableTime(map.get(indexs.get(6)));//假期失效效时间
//查条数
Integer count = this.holidayBalanceDao.getCount(holidayBalanceDto.getStaffId(), holidayBalanceDto.getHolidayRuleId());
if (count==0){
addBatchArgs.add(holidayBalanceDto);
}else {
editBatchArgs.add(holidayBalanceDto);
}
// 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
if ((addBatchArgs.size() + editBatchArgs.size()) >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
addBatchArgs.clear();
editBatchArgs.clear();
}
}
if (absenceLeaveAll!=null){
HolidayBalanceDto holidayBalanceDto=new HolidayBalanceDto();
holidayBalanceDto.setStaffId(Long.parseLong(staff_id));//员工id
holidayBalanceDto.setHolidayRuleId(Long.valueOf(sys_holidayRuleId_map.get("事假").toString()));//假期规则id
holidayBalanceDto.setCreateStaff(Long.parseLong(staff_id));//创建人
holidayBalanceDto.setUpdateStaff(Long.parseLong(staff_id));//修改人
holidayBalanceDto.setHolidayBalance(Float.valueOf(map.get(indexs.get(7))));//假期余额
holidayBalanceDto.setEffectTime(map.get(indexs.get(8)));//假期生效时间
holidayBalanceDto.setDisableTime(map.get(indexs.get(9)));//假期失效效时间
//查条数
Integer count = this.holidayBalanceDao.getCount(holidayBalanceDto.getStaffId(), holidayBalanceDto.getHolidayRuleId());
if (count==0){
addBatchArgs.add(holidayBalanceDto);
}else {
editBatchArgs.add(holidayBalanceDto);
}
// 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
if ((addBatchArgs.size() + editBatchArgs.size()) >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
addBatchArgs.clear();
editBatchArgs.clear();
}
}
if (parentalLeaveAll!=null){
HolidayBalanceDto holidayBalanceDto=new HolidayBalanceDto();
holidayBalanceDto.setStaffId(Long.parseLong(staff_id));//员工id
holidayBalanceDto.setHolidayRuleId(Long.valueOf(sys_holidayRuleId_map.get("育儿假").toString()));//假期规则id
holidayBalanceDto.setCreateStaff(Long.parseLong(staff_id));//创建人
holidayBalanceDto.setUpdateStaff(Long.parseLong(staff_id));//修改人
holidayBalanceDto.setHolidayBalance(Float.valueOf(map.get(indexs.get(10))));//假期余额
holidayBalanceDto.setEffectTime(map.get(indexs.get(11)));//假期生效时间
holidayBalanceDto.setDisableTime(map.get(indexs.get(12)));//假期失效效时间
//查条数
Integer count = this.holidayBalanceDao.getCount(holidayBalanceDto.getStaffId(), holidayBalanceDto.getHolidayRuleId());
if (count==0){
addBatchArgs.add(holidayBalanceDto);
}else {
editBatchArgs.add(holidayBalanceDto);
}
// 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
if ((addBatchArgs.size() + editBatchArgs.size()) >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
addBatchArgs.clear();
editBatchArgs.clear();
}
}
if (marternityLeaveAll!=null){
HolidayBalanceDto holidayBalanceDto=new HolidayBalanceDto();
holidayBalanceDto.setStaffId(Long.parseLong(staff_id));//员工id
holidayBalanceDto.setHolidayRuleId(Long.valueOf(sys_holidayRuleId_map.get("产假").toString()));//假期规则id
holidayBalanceDto.setCreateStaff(Long.parseLong(staff_id));//创建人
holidayBalanceDto.setUpdateStaff(Long.parseLong(staff_id));//修改人
holidayBalanceDto.setHolidayBalance(Float.valueOf(map.get(indexs.get(13))));//假期余额
holidayBalanceDto.setEffectTime(map.get(indexs.get(14)));//假期生效时间
holidayBalanceDto.setDisableTime(map.get(indexs.get(15)));//假期失效效时间
//查条数
Integer count = this.holidayBalanceDao.getCount(holidayBalanceDto.getStaffId(), holidayBalanceDto.getHolidayRuleId());
if (count==0){
addBatchArgs.add(holidayBalanceDto);
}else {
editBatchArgs.add(holidayBalanceDto);
}
// 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
if ((addBatchArgs.size() + editBatchArgs.size()) >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
addBatchArgs.clear();
editBatchArgs.clear();
}
}
if (marriageHolidayAll!=null){
HolidayBalanceDto holidayBalanceDto=new HolidayBalanceDto();
holidayBalanceDto.setStaffId(Long.parseLong(staff_id));//员工id
holidayBalanceDto.setHolidayRuleId(Long.valueOf(sys_holidayRuleId_map.get("婚假").toString()));//假期规则id
holidayBalanceDto.setCreateStaff(Long.parseLong(staff_id));//创建人
holidayBalanceDto.setUpdateStaff(Long.parseLong(staff_id));//修改人
holidayBalanceDto.setHolidayBalance(Float.valueOf(map.get(indexs.get(16))));//假期余额
holidayBalanceDto.setEffectTime(map.get(indexs.get(17)));//假期生效时间
holidayBalanceDto.setDisableTime(map.get(indexs.get(18)));//假期失效效时间
//查条数
Integer count = this.holidayBalanceDao.getCount(holidayBalanceDto.getStaffId(), holidayBalanceDto.getHolidayRuleId());
if (count==0){
addBatchArgs.add(holidayBalanceDto);
}else {
editBatchArgs.add(holidayBalanceDto);
}
// 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
if ((addBatchArgs.size() + editBatchArgs.size()) >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
addBatchArgs.clear();
editBatchArgs.clear();
}
}
if (nursingLeaveAll!=null){
HolidayBalanceDto holidayBalanceDto=new HolidayBalanceDto();
holidayBalanceDto.setStaffId(Long.parseLong(staff_id));//员工id
holidayBalanceDto.setHolidayRuleId(Long.valueOf(sys_holidayRuleId_map.get("护理假").toString()));//假期规则id
holidayBalanceDto.setCreateStaff(Long.parseLong(staff_id));//创建人
holidayBalanceDto.setUpdateStaff(Long.parseLong(staff_id));//修改人
holidayBalanceDto.setHolidayBalance(Float.valueOf(map.get(indexs.get(19))));//假期余额
holidayBalanceDto.setEffectTime(map.get(indexs.get(20)));//假期生效时间
holidayBalanceDto.setDisableTime(map.get(indexs.get(21)));//假期失效效时间
//查条数
Integer count = this.holidayBalanceDao.getCount(holidayBalanceDto.getStaffId(), holidayBalanceDto.getHolidayRuleId());
if (count==0){
addBatchArgs.add(holidayBalanceDto);
}else {
editBatchArgs.add(holidayBalanceDto);
}
// 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
if ((addBatchArgs.size() + editBatchArgs.size()) >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
addBatchArgs.clear();
editBatchArgs.clear();
}
}
if (smallMaternityAll!=null){
HolidayBalanceDto holidayBalanceDto=new HolidayBalanceDto();
holidayBalanceDto.setStaffId(Long.parseLong(staff_id));//员工id
holidayBalanceDto.setHolidayRuleId(Long.valueOf(sys_holidayRuleId_map.get("小产假").toString()));//假期规则id
holidayBalanceDto.setCreateStaff(Long.parseLong(staff_id));//创建人
holidayBalanceDto.setUpdateStaff(Long.parseLong(staff_id));//修改人
holidayBalanceDto.setHolidayBalance(Float.valueOf(map.get(indexs.get(22))));//假期余额
holidayBalanceDto.setEffectTime(map.get(indexs.get(23)));//假期生效时间
holidayBalanceDto.setDisableTime(map.get(indexs.get(24)));//假期失效效时间
//查条数
Integer count = this.holidayBalanceDao.getCount(holidayBalanceDto.getStaffId(), holidayBalanceDto.getHolidayRuleId());
if (count==0){
addBatchArgs.add(holidayBalanceDto);
}else {
editBatchArgs.add(holidayBalanceDto);
}
// 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
if ((addBatchArgs.size() + editBatchArgs.size()) >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
addBatchArgs.clear();
editBatchArgs.clear();
}
}
if (maternityViewAll!=null){
HolidayBalanceDto holidayBalanceDto=new HolidayBalanceDto();
holidayBalanceDto.setStaffId(Long.parseLong(staff_id));//员工id
holidayBalanceDto.setHolidayRuleId(Long.valueOf(sys_holidayRuleId_map.get("产检假").toString()));//假期规则id
holidayBalanceDto.setCreateStaff(Long.parseLong(staff_id));//创建人
holidayBalanceDto.setUpdateStaff(Long.parseLong(staff_id));//修改人
holidayBalanceDto.setHolidayBalance(Float.valueOf(map.get(indexs.get(25))));//假期余额
holidayBalanceDto.setEffectTime(map.get(indexs.get(26)));//假期生效时间
holidayBalanceDto.setDisableTime(map.get(indexs.get(27)));//假期失效效时间
//查条数
Integer count = this.holidayBalanceDao.getCount(holidayBalanceDto.getStaffId(), holidayBalanceDto.getHolidayRuleId());
if (count==0){
addBatchArgs.add(holidayBalanceDto);
}else {
editBatchArgs.add(holidayBalanceDto);
}
// 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
if ((addBatchArgs.size() + editBatchArgs.size()) >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
addBatchArgs.clear();
editBatchArgs.clear();
}
}
if (bereavementLeaveAll!=null){
HolidayBalanceDto holidayBalanceDto=new HolidayBalanceDto();
holidayBalanceDto.setStaffId(Long.parseLong(staff_id));//员工id
holidayBalanceDto.setHolidayRuleId(Long.valueOf(sys_holidayRuleId_map.get("丧假").toString()));//假期规则id
holidayBalanceDto.setCreateStaff(Long.parseLong(staff_id));//创建人
holidayBalanceDto.setUpdateStaff(Long.parseLong(staff_id));//修改人
holidayBalanceDto.setHolidayBalance(Float.valueOf(map.get(indexs.get(28))));//假期余额
holidayBalanceDto.setEffectTime(map.get(indexs.get(29)));//假期生效时间
holidayBalanceDto.setDisableTime(map.get(indexs.get(30)));//假期失效效时间
//查条数
Integer count = this.holidayBalanceDao.getCount(holidayBalanceDto.getStaffId(), holidayBalanceDto.getHolidayRuleId());
if (count==0){
addBatchArgs.add(holidayBalanceDto);
}else {
editBatchArgs.add(holidayBalanceDto);
}
// 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
if ((addBatchArgs.size() + editBatchArgs.size()) >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
addBatchArgs.clear();
editBatchArgs.clear();
}
}
if (incentiveLeaveAll!=null){
HolidayBalanceDto holidayBalanceDto=new HolidayBalanceDto();
holidayBalanceDto.setStaffId(Long.parseLong(staff_id));//员工id
holidayBalanceDto.setHolidayRuleId(Long.valueOf(sys_holidayRuleId_map.get("奖励休假").toString()));//假期规则id
holidayBalanceDto.setCreateStaff(Long.parseLong(staff_id));//创建人
holidayBalanceDto.setUpdateStaff(Long.parseLong(staff_id));//修改人
holidayBalanceDto.setHolidayBalance(Float.valueOf(map.get(indexs.get(31))));//假期余额
holidayBalanceDto.setEffectTime(map.get(indexs.get(32)));//假期生效时间
holidayBalanceDto.setDisableTime(map.get(indexs.get(33)));//假期失效效时间
//查条数
Integer count = this.holidayBalanceDao.getCount(holidayBalanceDto.getStaffId(), holidayBalanceDto.getHolidayRuleId());
if (count==0){
addBatchArgs.add(holidayBalanceDto);
}else {
editBatchArgs.add(holidayBalanceDto);
}
// 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
if ((addBatchArgs.size() + editBatchArgs.size()) >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
addBatchArgs.clear();
editBatchArgs.clear();
}
}
}
//所有数据解析完成
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
// 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
if ((addBatchArgs.size() + editBatchArgs.size()) > 0) {
saveData();
// 存储完成清理 list
addBatchArgs.clear();
editBatchArgs.clear();
}
}
//数据解析异常
@Override
public void onException(Exception exception, AnalysisContext context) throws Exception {
exception.printStackTrace();
if (exception instanceof ExcelDataConvertException) {
// 抓取invoke中的异常 并抛出详细信息
ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException) exception;
throw new Exception("第" + excelDataConvertException.getRowIndex() + "行，第"
+ excelDataConvertException.getColumnIndex() + "列有问题，原因为" + excelDataConvertException.getCellData() + "，请仔细检查后重新上传！");
}
if (exception instanceof IllegalArgumentException) {
throw new Exception("上传的excel表格中，数据不合法，请仔细检查后重新上传！");
}
throw new Exception("上传错误！请仔细检查后重新上传！");
}
private void saveData() {
try {
this.holidayBalanceDao.batchInsert(addBatchArgs);
this.holidayBalanceDao.batchUpdate(editBatchArgs);
// System.out.println("入库成功：" + addBatchArgs.size());
// System.out.println("更新成功："+editBatchArgs.size());
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException("时间格式错误");
}
}
}
easyExcel 实现excel数据导入
前端
1.html
<el-col :span="4">
<el-button type="primary" size="small" @click="importManualDataOfPiece" v-if="isAuth('sys:pieceNum:manualData')"><el-icon class="el-icon-upload" />计件量表手工数据导入</el-button>
</el-col>
<!-- 导入数据弹窗-->
<el-dialog
title="计件量表手工数据导入"
:visible.sync="importManualDataDialogFormVisible"
:append-to-body="true"
:close-on-click-modal="false"
class="intf-dialog"
>
<div style="height: 400px">
<el-upload
:before-upload="beforeUpload"
:http-request="uploadSubmit"
:show-file-list="false"
class="upload-demo"
action="/"
drag
accept=".xls,.xlsx"
style="margin-top: 20px;margin-left: 195px;width: 360px"
>
<i class="el-icon-upload"></i>
<div class="el-upload__text">将文件拖到此处，或<em>点击上传</em></div>
<div slot="tip" class="el-upload__tip" style="margin-left: 125px">只能上传xls/xlsx文件</div>
</el-upload>
<el-button type="success" style="margin-left: 335px;margin-top: 15px" @click="templateDownload">模板下载</el-button>
</div>
</el-dialog>
2.script
//导入手工数据
importManualDataOfPiece() {
this.importManualDataDialogFormVisible = true;
},
// 模板下载
templateDownload() {
const a = document.createElement('a')
a.style.display = 'none'
a.download = '计件量表手工数据模版示例.xlsx'
a.href = window.SITE_CONFIG.templateDownloadURl+'manualDataTemplate.xlsx'
a.click()
},
// 上传之前
beforeUpload(file) {
const msg = file.name.substring(file.name.lastIndexOf('.') + 1)
const checkResult = msg === 'xlsx' || msg === 'xls'
if (!checkResult) {
this.$message.error('上传格式错误')
} else {
this.fileList = file
}
return checkResult
},
// 导入手工数据提交
uploadSubmit() {
const loadingInstance = Loading.service({
fullscreen: true,
text: '正在上传中，请稍后...',
background: 'rgba(255,255,255,0.7)'
})
const file = new FormData()
file.append('file', this.fileList)
uploadManualData(file, true).then(res => {
this.fileList = []
loadingInstance.close()
if (res.data.code ===200){
this.$message.success('文件上传成功')
}else if (res.data.code ===405){
this.$message.error(res.data.msg)
}
this.importManualDataDialogFormVisible = false
}).catch(() => {
this.fileList = []
this.$message.error('服务器内部出错，上传失败！')
loadingInstance.close()
})
},
//文件改变触发
handleChangeFile(file) {
const isXls = file.name.endsWith('.xls')||file.name.endsWith('.xlsx');
const isLt10M = file.size / 1024 / 1024 < 10;
if (!isXls) {
this.$message.error('上传文件只能是xls或xlsx格式!');
}
if (!isLt10M) {
this.$message.error('上传文件大小不能超过 10MB!');
}
this.fileList = []
if (isXls && isLt10M) {
this.fileList.push(file.raw)
return true;
} else {
this.$refs.uploadFile.clearFiles()
return false;
}
},
3.dto
package usi.pieceNum.dto;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
/**
* @author Administrator
*/
@Data
@NoArgsConstructor
@AllArgsConstructor
public class ExcelDTOForPiece {
// 日期
@ExcelProperty(index = 0,value = "日期")
private String dateTime;
// 班组
@ExcelProperty(index = 1,value = "班组")
private String teamName;
// 岗位
@ExcelProperty(index = 2,value = "岗位")
private String postName;
// 工号
@ExcelProperty(index = 3,value = "工号")
private String jobNumber;
// 姓名
@ExcelProperty(index = 4,value = "姓名")
private String staffName;
// IM一次解决且满意
@ExcelProperty(index = 5,value = "一次解决且满意")
private String IMOneDealAndSatisfied;
// IM一次解决未满意
@ExcelProperty(index = 6,value = "一次解决但未评满意（含未参评）")
private String IMOneDealNotSatisfied;
// IM一次未解决且满意
@ExcelProperty(index = 7,value = "未一次解决但满意")
private String IMNotDealAndSatisfied;
// IM一次未解决未满意
@ExcelProperty(index = 8,value = "未一次解决且未评满意（含未参评）")
private String IMNotDealNotSatisfied;
// 远柜一次解决且满意
@ExcelProperty(index = 9,value = "一次解决且满意")
private String yuanguiOneDealAndSatisfied;
// 远柜一次解决未满意
@ExcelProperty(index = 10,value = "一次解决但未评满意（含未参评）")
private String yuanguiOneDealNotSatisfied;
// 远柜一次未解决且满意
@ExcelProperty(index = 11,value = "未一次解决但满意")
private String yuanguiNotDealAndSatisfied;
// 远柜一次未解决未满意
@ExcelProperty(index = 12,value = "未一次解决且未评满意（含未参评）")
private String yuanguiNotDealNotSatisfied;
// 短信办结、联系不上短信办结（留言信箱处理）
@ExcelProperty(index = 13,value = "短信办结、联系不上短信办结")
private String SMSCompletion;
// 回拨呼通
@ExcelProperty(index = 14,value = "回拨呼通")
private String callBackHuTong;
// 回拨未呼通
@ExcelProperty(index = 15,value = "回拨未呼通")
private String callBackNotHuTong;
// 短信办结（营业厅）
@ExcelProperty(index = 16,value = "短信办结")
private String appSMSCompletion;
// 每小时员工带教
@ExcelProperty(index = 17,value = "每小时新员工带教、导师、其他协助工作")
private String teacherOfOneHour;
// 服务案例
@ExcelProperty(index = 18,value = "服务案例挖掘")
private String serverCases;
// bss异地拆机单审核回单
@ExcelProperty(index = 19,value = "BSS异地拆机单审核回单")
private String bssApprove;
// 调账日稽核量
@ExcelProperty(index = 20,value = "调账日稽核量")
private String auditOfDay;
// 调账月稽核时长
@ExcelProperty(index = 21,value = "调账月稽核时长")
private String auditOfMouth;
// 顺销日稽核量
@ExcelProperty(index = 22,value = "顺销日稽核量")
private String auditOfDayShunXiao;
// 微博监控时长
@ExcelProperty(index = 23,value = "微博及鹰眼监控时长")
private String microBlog;
// ai原生打标
@ExcelProperty(index = 24,value = "AI原声打标")
private String AIMarking;
// 打标结果质检
@ExcelProperty(index = 25,value = "打标结果质检")
private String markingQuality;
// 录音质检
@ExcelProperty(index = 26,value = "录音质检计件")
private String soundRecordQuality;
// 工单质检
@ExcelProperty(index = 27,value = "工单质检计件")
private String orderQuality;
// H5机器人原生语义训练
@ExcelProperty(index = 28,value = "H5机器人原声语义训练")
private String h5Train;
// IVR机器人原生语义训练
@ExcelProperty(index = 29,value = "IVR机器人原声语义训练")
private String IVRTrain;
// 智能文本原生语义训练
@ExcelProperty(index = 30,value = "智能文本原声语义训练")
private String intelligenceTrain;
// 信息采编
@ExcelProperty(index = 31,value = "信息采编")
private String informationGather;
// 信息审核
@ExcelProperty(index = 32,value = "信息审核")
private String informationApprove;
// 知识库意见建议
@ExcelProperty(index = 33,value = "知识库意见建议")
private String knowledgeBase;
// 翼声平台话务打标
@ExcelProperty(index = 34,value = "翼声平台话务打标")
private String trafficMarking;
// 处理51041工单
@ExcelProperty(index = 35,value = "处理51041工单")
private String order_51041;
}
4.监听器
package usi.pieceNum.utils;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelAnalysisException;
import com.alibaba.excel.exception.ExcelDataConvertException;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import com.alibaba.excel.read.metadata.holder.ReadRowHolder;
import com.alibaba.excel.util.StringUtils;
import org.apache.poi.ss.formula.functions.T;
import usi.pieceNum.dao.PieceNumDao;
import usi.pieceNum.dto.EasyExcelValiHelper;
import usi.pieceNum.dto.ExcelCheckErrDto;
import usi.pieceNum.dto.ExcelDTOForPiece;
import usi.pieceNum.service.PieceNumService;
import java.lang.reflect.Field;
import java.util.*;
public class ManualDataListener<T> extends AnalysisEventListener<T> {
//成功结果集
private List<T> successList = new ArrayList<>();
//失败结果集
private List<ExcelCheckErrDto<T>> errList = new ArrayList<>();
//excel对象的反射类
private Class<ExcelDTOForPiece> clazz;
private static final Integer BATCH_COUNT = 1000; //批量操作最大条数
List<List<ExcelDTOForPiece>> lists = new ArrayList<>();
private final PieceNumService pieceNumService;
private final String userId;
private final List<T> listAll; // 所有数据的集合
private List<ExcelDTOForPiece> addDayBatchArgs; // 插入手工数据日表参数
private List<ExcelDTOForPiece> updateDayBatchArgs; // 更新手工数据日表参数
private List<ExcelDTOForPiece> addMouthBatchArgs; // 插入手工数据月表参数
private List<ExcelDTOForPiece> updateMouthBatchArgs; // 更新手工数据月表参数
public ManualDataListener(String userId, PieceNumService pieceNumService,Class<ExcelDTOForPiece> clazz) {
this.userId = userId;
this.pieceNumService = pieceNumService;
this.clazz = clazz;
addDayBatchArgs = new ArrayList<>();
updateDayBatchArgs = new ArrayList<>();
addMouthBatchArgs = new ArrayList<>();
updateMouthBatchArgs = new ArrayList<>();
listAll = new ArrayList<>();
}
/**
* @param headMap 传入excel的头部（第一行数据）数据的index,name
* @description: 校验excel头部格式，必须完全匹配
*/
int x=0;
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
super.invokeHeadMap(headMap, context);
x++;
if (x==3){//第三行表头是真正的表头
if (clazz != null) {
try {
Map<Integer, String> indexNameMap = getIndexNameMap(clazz);
Set<Integer> keySet = indexNameMap.keySet();
for (Integer key : keySet) {
if (StringUtils.isEmpty(headMap.get(key))) {
throw new ExcelAnalysisException("解析excel出错，表头不能为空，请按照模板上传！");
}
if (!headMap.get(key).equals(indexNameMap.get(key))) {
throw new ExcelAnalysisException("解析excel出错，表头与模板不一致，请按照模板上传！");
}
}
} catch (NoSuchFieldException e) {
e.printStackTrace();
}
}
}
}
public Map<Integer, String> getIndexNameMap(Class clazz) throws NoSuchFieldException {
Map<Integer, String> result = new HashMap<>();
Field field;
Field[] fields = clazz.getDeclaredFields();
for (int i = 0; i < fields.length; i++) {
field = clazz.getDeclaredField(fields[i].getName());
field.setAccessible(true);
ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
if (excelProperty != null) {
int index = excelProperty.index();
String[] values = excelProperty.value();
StringBuilder value = new StringBuilder();
for (String v : values) {
value.append(v);
}
result.put(index, value.toString());
}
}
return result;
}
//解析一条数据
@Override
public void invoke(T t, AnalysisContext analysisContext) {
String errMsg;
try {
//根据excel数据实体中的javax.validation + 正则表达式来校验excel数据
errMsg = EasyExcelValiHelper.validateEntity(t);
} catch (NoSuchFieldException e) {
errMsg = "解析数据出错";
e.printStackTrace();
}
if (!StringUtils.isEmpty(errMsg)) {
ExcelCheckErrDto excelCheckErrDto = new ExcelCheckErrDto(t, errMsg);
errList.add(excelCheckErrDto);
} else {
listAll.add(t);
}
lists = pieceNumService.checkImportExcel((List<ExcelDTOForPiece>) listAll);
addDayBatchArgs =lists.get(0);
updateDayBatchArgs =lists.get(1);
addMouthBatchArgs =lists.get(2);
updateMouthBatchArgs =lists.get(3);
saveData(addDayBatchArgs,updateDayBatchArgs,addMouthBatchArgs,updateMouthBatchArgs);
}
private void saveData(List<ExcelDTOForPiece> addDayBatchArgs,List<ExcelDTOForPiece> updateDayBatchArgs,List<ExcelDTOForPiece> addMouthBatchArgs,List<ExcelDTOForPiece> updateMouthBatchArgs){
if(addDayBatchArgs.size()> BATCH_COUNT){
pieceNumService.addDayManualData(addDayBatchArgs);
// 存储完成清理 list
addDayBatchArgs.clear();
}
if(updateDayBatchArgs.size()> BATCH_COUNT){
pieceNumService.updateDayManualData(updateDayBatchArgs);
updateDayBatchArgs.clear();
}
if(addMouthBatchArgs.size()> BATCH_COUNT){
pieceNumService.addMouthManualData(addMouthBatchArgs);
addMouthBatchArgs.clear();
}
if(updateMouthBatchArgs.size()> BATCH_COUNT){
pieceNumService.updateMouthManualData(addMouthBatchArgs);
updateMouthBatchArgs.clear();
}
}
private void saveDataFinal(List<ExcelDTOForPiece> addDayBatchArgs,List<ExcelDTOForPiece> updateDayBatchArgs,List<ExcelDTOForPiece> addMouthBatchArgs,List<ExcelDTOForPiece> updateMouthBatchArgs){
if(addDayBatchArgs.size()> 0){
pieceNumService.addDayManualData(addDayBatchArgs);
// 存储完成清理 list
addDayBatchArgs.clear();
}
if(updateDayBatchArgs.size()> 0){
pieceNumService.updateDayManualData(updateDayBatchArgs);
updateDayBatchArgs.clear();
}
if(addMouthBatchArgs.size()> 0){
pieceNumService.addMouthManualData(addMouthBatchArgs);
addMouthBatchArgs.clear();
}
if(updateMouthBatchArgs.size()> 0){
pieceNumService.updateMouthManualData(updateMouthBatchArgs);
updateMouthBatchArgs.clear();
}
}
//所有数据解析完成了 都会来调用
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
saveDataFinal(addDayBatchArgs,updateDayBatchArgs,addMouthBatchArgs,updateMouthBatchArgs);
}
@Override
public void onException(Exception exception, AnalysisContext context) throws Exception {
if (exception instanceof ExcelDataConvertException) {
// 抓取invoke中的异常 并抛出详细信息
ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException)exception;
throw new Exception("第"+excelDataConvertException.getRowIndex()+"行，第"
+excelDataConvertException.getColumnIndex()+"列有问题，原因为"+excelDataConvertException.getCellData()+"，请仔细检查后重新上传！");
}
if (exception instanceof ExcelAnalysisException){
throw new Exception(exception.getMessage());
}
if (exception instanceof IllegalArgumentException) {
throw new Exception("上传的excel表格中，不允许有空数据，请仔细检查后重新上传！");
}
System.out.println(exception.getMessage());
throw new Exception("上传错误！请仔细检查后重新上传！");
}
}
5.controller
/**
* 计件项手工数据导入
* @param response
* @param file
* @param session
* @return
*/
@RequestMapping(value = "/uploadManualData.do" , method = RequestMethod.POST)
public Response uploadManualData(HttpServletResponse response, MultipartFile file, HttpSession session){
response.setContentType("text/html;charset=UTF-8");
AuthInfo authInfo = (AuthInfo) session.getAttribute(ConstantUtil.AUTH_INFO);
String userId = authInfo.getUserId();
Map<String,Object> map = new HashMap<>();
InputStream in = null;
PieceOverviewUpload pieceOverviewUpload = new PieceOverviewUpload();
/**
* 文件上传服务器
*/
SysFileDto sysFileDto=new SysFileDto();
String fileName = file.getOriginalFilename();
Date date = new Date();
String relativeDir = SftpUtil.getRelativeDir(date);
//新的文件名
String newName = usi.sys.util.CommonUtil.format(date, "HHmmssSSS") +
Math.round(Math.random() * 8999 + 1000) +
fileName.substring(fileName.lastIndexOf("."));
try{
in = file.getInputStream();
ManualDataListener manualDataListener = new ManualDataListener(userId, pieceNumService, ExcelDTOForPiece.class);
EasyExcel.read(in,ExcelDTOForPiece.class,manualDataListener).sheet().headRowNumber(3).doRead();
map.put("code",200);
map.put("msg","上传成功");
sysFileDto.setAbsolutePath(relativeDir+newName);
sysFileDto.setFileName(fileName);
sysFileDto.setFileSize((int) file.getSize());
sysFileDto.setFileType(file.getContentType());
if(true){//flag
long file_id=sysFileDao.insertFile(sysFileDto,"UploadPieceOverview");
pieceOverviewUpload.setFileId(file_id);
pieceOverviewUpload.setUploadResult("批量上传计件项手工数据成功！");
}else {
map.put("code", 0);
map.put("message", "文件未成功上传服务器。");
Response.success(map);
}
/**
* 上传结束
*/
pieceNumService.insertUploadResult(pieceOverviewUpload);
} catch (Exception e){
map.put("code",405);
if(e instanceof ExcelAnalysisException) {
map.put("msg",e.getMessage());
} else {
e.printStackTrace();
map.put("msg","上传错误，请联系管理员！");
}
} finally {
if(in != null){
try {
in.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return Response.success(map);
}
6.service
/**
* 校验数据
* @param listAll
*/
public List<List<ExcelDTOForPiece>> checkImportExcel(List<ExcelDTOForPiece> listAll) {
List<ExcelDTOForPiece> addDayBatchArgs = new ArrayList<>(); // 插入手工数据日表参数
List<ExcelDTOForPiece> updateDayBatchArgs = new ArrayList<>(); // 更新手工数据日表参数
List<ExcelDTOForPiece> addMouthBatchArgs = new ArrayList<>(); // 插入手工数据月表参数
List<ExcelDTOForPiece> updateMouthBatchArgs = new ArrayList<>(); // 更新手工数据月表参数
List<List<ExcelDTOForPiece>> list = new ArrayList<>();
for (int i = 0; i <listAll.size() ; i++) {
if (listAll.get(i).getDateTime().length() >7){//日
String monthId =listAll.get(i).getDateTime();
// 根据工号查询staffID
String staffId =pieceNumDao.getStaffIdByJobNumber(listAll.get(i).getJobNumber());
// 查询是否有相同记录，有就更新，没有就新增
int dauCount = pieceNumDao.checkRepeatByDay(monthId, staffId);
if (dauCount>0){//更新
updateDayBatchArgs.add(listAll.get(i));
}else {//新增
addDayBatchArgs.add(listAll.get(i));
}
}else {//月
String monthId =listAll.get(i).getDateTime();
// 根据工号查询staffID
String staffId =pieceNumDao.getStaffIdByJobNumber(listAll.get(i).getJobNumber());
// 查询是否有相同记录，有就更新，没有就新增
int dauCount = pieceNumDao.checkRepeatByMouth(monthId, staffId);
if (dauCount>0){//更新
updateMouthBatchArgs.add(listAll.get(i));
}else {//新增
addMouthBatchArgs.add(listAll.get(i));
}
}
}
list.add(addDayBatchArgs);
list.add(updateDayBatchArgs);
list.add(addMouthBatchArgs);
list.add(updateMouthBatchArgs);
return list;
}
/**
* 新增日数据
* @param addDayBatchArgs
*/
public void addDayManualData(List<ExcelDTOForPiece> addDayBatchArgs) {
pieceNumDao.addDayManualData(addDayBatchArgs);
}
/**
* 更新日数据
* @param updateDayBatchArgs
*/
public void updateDayManualData(List<ExcelDTOForPiece> updateDayBatchArgs) {
pieceNumDao.updateDayManualData(updateDayBatchArgs);
}
/**
* 新增月数据
* @param addMouthBatchArgs
*/
public void addMouthManualData(List<ExcelDTOForPiece> addMouthBatchArgs) {
pieceNumDao.addMouthManualData(addMouthBatchArgs);
}
/**
* 更新月数据
* @param updateMouthBatchArgs
*/
public void updateMouthManualData(List<ExcelDTOForPiece> updateMouthBatchArgs) {
pieceNumDao.updateMouthManualData(updateMouthBatchArgs);
}
7.daoimpl
@Override
public int checkRepeatByMouth(String monthId, String staffId) {
String sql = "SELECT count(*) FROM sys_piece_num WHERE staff_id=? AND salary_month=?";
Object[] args = {staffId,monthId };
return this.getJdbcTemplate().queryForObject(sql, args, Integer.class);
}
@Override
public int checkRepeatByDay(String monthId, String staffId) {
String sql = "SELECT count(*) FROM sys_piece_num_day WHERE staff_id=? AND salary_month=?";
Object[] args = {staffId,monthId };
return this.getJdbcTemplate().queryForObject(sql, args, Integer.class);
}
/**
* 注意：暂时没有51041工单
* @param addDayBatchArgs
*/
@Override
public void addDayManualData(List<ExcelDTOForPiece> addDayBatchArgs) {
String sql = "INSERT INTO sys_piece_num_day(staff_id,salary_month,create_time,field141,field142,field143,field144,field145,field146,field147,field148,field149,field150,field151,field152,field19,field20,field52,field44,field45,field51,field21,field26,field27,field24,field23,field28,field29,field30,field50,field70,field36,field200) VALUES ((SELECT staff_id FROM sys_staff WHERE USER_ID=?),?,now(),?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ";
for (int i = 0; i < addDayBatchArgs.size(); i++) {
this.getJdbcTemplate().update(sql,new Object[]{
addDayBatchArgs.get(i).getJobNumber(),
addDayBatchArgs.get(i).getDateTime(),
addDayBatchArgs.get(i).getIMOneDealAndSatisfied(),
addDayBatchArgs.get(i).getIMOneDealNotSatisfied(),
addDayBatchArgs.get(i).getIMNotDealAndSatisfied(),
addDayBatchArgs.get(i).getIMNotDealNotSatisfied(),
addDayBatchArgs.get(i).getYuanguiOneDealAndSatisfied(),
addDayBatchArgs.get(i).getYuanguiOneDealNotSatisfied(),
addDayBatchArgs.get(i).getYuanguiNotDealAndSatisfied(),
addDayBatchArgs.get(i).getYuanguiNotDealNotSatisfied(),
addDayBatchArgs.get(i).getSMSCompletion(),
addDayBatchArgs.get(i).getCallBackHuTong(),
addDayBatchArgs.get(i).getCallBackNotHuTong(),
addDayBatchArgs.get(i).getAppSMSCompletion(),
addDayBatchArgs.get(i).getTeacherOfOneHour(),
addDayBatchArgs.get(i).getServerCases(),
addDayBatchArgs.get(i).getBssApprove(),
addDayBatchArgs.get(i).getAuditOfDay(),
addDayBatchArgs.get(i).getAuditOfMouth(),
addDayBatchArgs.get(i).getAuditOfDayShunXiao(),
addDayBatchArgs.get(i).getMicroBlog(),
addDayBatchArgs.get(i).getAIMarking(),
addDayBatchArgs.get(i).getMarkingQuality(),
addDayBatchArgs.get(i).getSoundRecordQuality(),
addDayBatchArgs.get(i).getOrderQuality(),
addDayBatchArgs.get(i).getH5Train(),
addDayBatchArgs.get(i).getIVRTrain(),
addDayBatchArgs.get(i).getIntelligenceTrain(),
addDayBatchArgs.get(i).getInformationGather(),
addDayBatchArgs.get(i).getInformationApprove(),
addDayBatchArgs.get(i).getKnowledgeBase(),
addDayBatchArgs.get(i).getTrafficMarking(),
// addDayBatchArgs.get(i).getOrder_51041()
});
}
}
@Override
public void updateDayManualData(List<ExcelDTOForPiece> updateDayBatchArgs) {
String sql = "UPDATE sys_piece_num_day SET field141=?,field142=?,field143=?,field144=?,field145=?,field146=?,field147=?,field148=?,field149=?,field150=?,field151=?,field152=?,field19=?,field20=?,field52=?,field44=?,field45=?,field51=?,field21=?,field26=?,field27=?,field24=?,field23=?,field28=?,field29=?,field30=?,field50=?,field70=?,field36=?,field200=? WHERE staff_id=(SELECT staff_id FROM sys_staff WHERE USER_ID=?) AND salary_month =?";
for (int i = 0; i < updateDayBatchArgs.size(); i++) {
this.getJdbcTemplate().update(sql,new Object[]{
updateDayBatchArgs.get(i).getIMOneDealAndSatisfied(),
updateDayBatchArgs.get(i).getIMOneDealNotSatisfied(),
updateDayBatchArgs.get(i).getIMNotDealAndSatisfied(),
updateDayBatchArgs.get(i).getIMNotDealNotSatisfied(),
updateDayBatchArgs.get(i).getYuanguiOneDealAndSatisfied(),
updateDayBatchArgs.get(i).getYuanguiOneDealNotSatisfied(),
updateDayBatchArgs.get(i).getYuanguiNotDealAndSatisfied(),
updateDayBatchArgs.get(i).getYuanguiNotDealNotSatisfied(),
updateDayBatchArgs.get(i).getSMSCompletion(),
updateDayBatchArgs.get(i).getCallBackHuTong(),
updateDayBatchArgs.get(i).getCallBackNotHuTong(),
updateDayBatchArgs.get(i).getAppSMSCompletion(),
updateDayBatchArgs.get(i).getTeacherOfOneHour(),
updateDayBatchArgs.get(i).getServerCases(),
updateDayBatchArgs.get(i).getBssApprove(),
updateDayBatchArgs.get(i).getAuditOfDay(),
updateDayBatchArgs.get(i).getAuditOfMouth(),
updateDayBatchArgs.get(i).getAuditOfDayShunXiao(),
updateDayBatchArgs.get(i).getMicroBlog(),
updateDayBatchArgs.get(i).getAIMarking(),
updateDayBatchArgs.get(i).getMarkingQuality(),
updateDayBatchArgs.get(i).getSoundRecordQuality(),
updateDayBatchArgs.get(i).getOrderQuality(),
updateDayBatchArgs.get(i).getH5Train(),
updateDayBatchArgs.get(i).getIVRTrain(),
updateDayBatchArgs.get(i).getIntelligenceTrain(),
updateDayBatchArgs.get(i).getInformationGather(),
updateDayBatchArgs.get(i).getInformationApprove(),
updateDayBatchArgs.get(i).getKnowledgeBase(),
updateDayBatchArgs.get(i).getTrafficMarking(),
// addDayBatchArgs.get(i).getOrder_51041()
updateDayBatchArgs.get(i).getJobNumber(),
updateDayBatchArgs.get(i).getDateTime()
});
}
}
@Override
public void addMouthManualData(List<ExcelDTOForPiece> addMouthBatchArgs) {
String sql = "INSERT INTO sys_piece_num(staff_id,salary_month,create_time,field141,field142,field143,field144,field145,field146,field147,field148,field149,field150,field151,field152,field19,field20,field52,field44,field45,field51,field21,field26,field27,field24,field23,field28,field29,field30,field50,field70,field36,field200) VALUES ((SELECT staff_id FROM sys_staff WHERE USER_ID=?),?,now(),?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ";
for (int i = 0; i < addMouthBatchArgs.size(); i++) {
this.getJdbcTemplate().update(sql,new Object[]{
addMouthBatchArgs.get(i).getJobNumber(),
addMouthBatchArgs.get(i).getDateTime(),
addMouthBatchArgs.get(i).getIMOneDealAndSatisfied(),
addMouthBatchArgs.get(i).getIMOneDealNotSatisfied(),
addMouthBatchArgs.get(i).getIMNotDealAndSatisfied(),
addMouthBatchArgs.get(i).getIMNotDealNotSatisfied(),
addMouthBatchArgs.get(i).getYuanguiOneDealAndSatisfied(),
addMouthBatchArgs.get(i).getYuanguiOneDealNotSatisfied(),
addMouthBatchArgs.get(i).getYuanguiNotDealAndSatisfied(),
addMouthBatchArgs.get(i).getYuanguiNotDealNotSatisfied(),
addMouthBatchArgs.get(i).getSMSCompletion(),
addMouthBatchArgs.get(i).getCallBackHuTong(),
addMouthBatchArgs.get(i).getCallBackNotHuTong(),
addMouthBatchArgs.get(i).getAppSMSCompletion(),
addMouthBatchArgs.get(i).getTeacherOfOneHour(),
addMouthBatchArgs.get(i).getServerCases(),
addMouthBatchArgs.get(i).getBssApprove(),
addMouthBatchArgs.get(i).getAuditOfDay(),
addMouthBatchArgs.get(i).getAuditOfMouth(),
addMouthBatchArgs.get(i).getAuditOfDayShunXiao(),
addMouthBatchArgs.get(i).getMicroBlog(),
addMouthBatchArgs.get(i).getAIMarking(),
addMouthBatchArgs.get(i).getMarkingQuality(),
addMouthBatchArgs.get(i).getSoundRecordQuality(),
addMouthBatchArgs.get(i).getOrderQuality(),
addMouthBatchArgs.get(i).getH5Train(),
addMouthBatchArgs.get(i).getIVRTrain(),
addMouthBatchArgs.get(i).getIntelligenceTrain(),
addMouthBatchArgs.get(i).getInformationGather(),
addMouthBatchArgs.get(i).getInformationApprove(),
addMouthBatchArgs.get(i).getKnowledgeBase(),
addMouthBatchArgs.get(i).getTrafficMarking(),
// addDayBatchArgs.get(i).getOrder_51041()
});
}
}
@Override
public void updateMouthManualData(List<ExcelDTOForPiece> updateMouthBatchArgs) {
String sql = "UPDATE sys_piece_num SET field141=?,field142=?,field143=?,field144=?,field145=?,field146=?,field147=?,field148=?,field149=?,field150=?,field151=?,field152=?,field19=?,field20=?,field52=?,field44=?,field45=?,field51=?,field21=?,field26=?,field27=?,field24=?,field23=?,field28=?,field29=?,field30=?,field50=?,field70=?,field36=?,field200=? WHERE staff_id=(SELECT staff_id FROM sys_staff WHERE USER_ID=?) AND salary_month =?";
for (int i = 0; i < updateMouthBatchArgs.size(); i++) {
this.getJdbcTemplate().update(sql,new Object[]{
updateMouthBatchArgs.get(i).getIMOneDealAndSatisfied(),
updateMouthBatchArgs.get(i).getIMOneDealNotSatisfied(),
updateMouthBatchArgs.get(i).getIMNotDealAndSatisfied(),
updateMouthBatchArgs.get(i).getIMNotDealNotSatisfied(),
updateMouthBatchArgs.get(i).getYuanguiOneDealAndSatisfied(),
updateMouthBatchArgs.get(i).getYuanguiOneDealNotSatisfied(),
updateMouthBatchArgs.get(i).getYuanguiNotDealAndSatisfied(),
updateMouthBatchArgs.get(i).getYuanguiNotDealNotSatisfied(),
updateMouthBatchArgs.get(i).getSMSCompletion(),
updateMouthBatchArgs.get(i).getCallBackHuTong(),
updateMouthBatchArgs.get(i).getCallBackNotHuTong(),
updateMouthBatchArgs.get(i).getAppSMSCompletion(),
updateMouthBatchArgs.get(i).getTeacherOfOneHour(),
updateMouthBatchArgs.get(i).getServerCases(),
updateMouthBatchArgs.get(i).getBssApprove(),
updateMouthBatchArgs.get(i).getAuditOfDay(),
updateMouthBatchArgs.get(i).getAuditOfMouth(),
updateMouthBatchArgs.get(i).getAuditOfDayShunXiao(),
updateMouthBatchArgs.get(i).getMicroBlog(),
updateMouthBatchArgs.get(i).getAIMarking(),
updateMouthBatchArgs.get(i).getMarkingQuality(),
updateMouthBatchArgs.get(i).getSoundRecordQuality(),
updateMouthBatchArgs.get(i).getOrderQuality(),
updateMouthBatchArgs.get(i).getH5Train(),
updateMouthBatchArgs.get(i).getIVRTrain(),
updateMouthBatchArgs.get(i).getIntelligenceTrain(),
updateMouthBatchArgs.get(i).getInformationGather(),
updateMouthBatchArgs.get(i).getInformationApprove(),
updateMouthBatchArgs.get(i).getKnowledgeBase(),
updateMouthBatchArgs.get(i).getTrafficMarking(),
// addDayBatchArgs.get(i).getOrder_51041()
updateMouthBatchArgs.get(i).getJobNumber(),
updateMouthBatchArgs.get(i).getDateTime()
});
}
}
仅作为笔记，无抄袭！
扫一扫
2087
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
