//Excel导出
@GetMapping("/export")
@ApiOperation("导出组件模版")
@OperateLog(type = EXPORT)
public CommonResult exportExcel(HttpServletResponse response){
return elementServiceImpl.exportElement(response);
}
//Excel导入
@PostMapping("/import")
@ApiOperation("导入组件模版")
@ApiImplicitParams({@ApiImplicitParam(name = "file", value = "Excel 文件", required = true, dataTypeClass = MultipartFile.class)})
public CommonResult importExcel(@RequestParam("file") MultipartFile file) throws Exception {
//拿到 Excel文书数据 传给 业务层处理
List<ElementExcelVo> list =
com.nb.easy.easyframe.framework.excel.core.util.ExcelUtils.read(file, ElementExcelVo.class,1);
//success 返回 data
return elementServiceImpl.importElement(list);
}
//分配组件
@PostMapping("/distribution")
@ApiOperation(value = "给组件分配电池片")
public CommonResult distribution(@ApiParam(value = "组件Id集合") @RequestParam String lotIds,
@ApiParam(value = "电池包批次号") @RequestParam(value = "outno",required = false) String outno,
@ApiParam(value = "电池包工厂编号") @RequestParam(value = "cjbh",required = false) String cjbh) {
return elementServiceImpl.distribution(lotIds,outno,cjbh);
}
//导出
public CommonResult exportElement(HttpServletResponse response) {
//TODO : 配置下载信息
//设置Content类型为 excel
response.setContentType("application/vnd.ms-excel");
//设置编码
response.setCharacterEncoding("utf-8");
//设置导出文件名
String fileName = null;
try {
//使用google浏览器下载无误 文书档案.xls,使用postman测试文件名会乱码
fileName = URLEncoder.encode("组件模版", "utf-8");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
//设置头信息及文件后缀名,并让此操作以下载方式打开
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xls");
//查询组件Excel在组件表的三个数据
List<ElementExcelFieldVo> excelField = elementMapper.selectElementExcelField();
if (excelField.size()==0){return null;}//校验
//excel集合
List<ElementExcelVo> excelVOList = new ArrayList<>();
//把表中添加到 excel 实体类中
PageResult<DictDataDO> pageResul =
dictDataService.getDictDataPage(new DictDataPageReqVO().setDictType("trace_lot_factory_type"));
for (ElementExcelFieldVo excelVO : excelField) {
ElementExcelVo elementExcelVo = new ElementExcelVo();
elementExcelVo.setLotId(excelVO.getLotId());
elementExcelVo.setParentLotRrn(excelVO.getParentLotRrn());
//找到对应的工厂名称并存入
for (DictDataDO dataDO : pageResul.getList()){
if (dataDO.getValue().equals(excelVO.getSource())){
elementExcelVo.setElementFactory(dataDO.getLabel());
break;
}
}
excelVOList.add(elementExcelVo);
}
//写出组件模版
try {
EasyExcel.write(response.getOutputStream(), ElementExcelVo.class)
.sheet("组件模版")
.doWrite(excelVOList);
} catch (IOException e) {
e.printStackTrace();
}
return commonResult.setMsg("组件模版导出成功");
}
//导入
@Transactional(rollbackFor = Exception.class) // 添加事务,异常则回滚所有导入
public CommonResult importElement(List<ElementExcelVo> elementExcelVoList) {
//判断传入的参数是否为空,为空则抛出异常码 new ErrorCode(1002003004, "导入用户数据不能为空!");
if (CollUtil.isEmpty(elementExcelVoList)) {
throw exception(USER_IMPORT_LIST_IS_EMPTY);
}
SimpleDateFormat df = new SimpleDateFormat("dd-MM-yyyy HH:mm:ss");
Date date = null;
try {
date = df.parse(df.format(new Date()));
} catch (ParseException e) {
//打印堆栈跟踪,交给系统处理 System.err
e.printStackTrace();
}
//添加
List<ElementDo> elementList = new ArrayList<>();
List<PvmIvData> pvmIvDataList = new ArrayList<>();
PageResult<DictDataDO> pageResul =
dictDataService.getDictDataPage(new DictDataPageReqVO().setDictType("trace_lot_factory_type"));
for (ElementExcelVo elementExcelVo : elementExcelVoList){
ElementDo element = new ElementDo();
element.setLotId(elementExcelVo.getLotId());
element.setParentLotRrn(Long.valueOf(elementExcelVo.getParentLotRrn()).toString());
//根据工厂名获取字典键值
for (DictDataDO dataDO : pageResul.getList()){
if (elementExcelVo.getElementFactory().equals(dataDO.getLabel())){
element.setSource(dataDO.getValue());
//同步时间
element.setCreated(new Timestamp(date.getTime()));
break;
}
}
element.setObjectRrn(elementExcelVo.getParentLotRrn().toString());
elementList.add(element);
//添加插入时间
PvmIvData pvmIvData = new PvmIvData();
BeanUtils.copyProperties(elementExcelVo,pvmIvData);
pvmIvData.setObjectRrn(elementExcelVo.getParentLotRrn().toString());
pvmIvData.setLotId(elementExcelVo.getLotId());
pvmIvData.setUpdated(date);
pvmIvDataList.add(pvmIvData);
//更新人
}
elementMapper.insertElement(elementList);
pvmIvDataMapper.insertPvm(pvmIvDataList);
return commonResult.setMsg("组件模版导入成功");
}
//给组件分配电池片
public CommonResult distribution(String lotIds,String outno, String cjbh){
String[] split = lotIds.split(",");
List<String> list = new ArrayList<>();
for (int i = 0; i < split.length; i++) {
list.add(split[i]);
}
//获取当前时间
SimpleDateFormat df = new SimpleDateFormat("dd-MM-yyyy HH:mm:ss");
Date date = null;
try {
date = df.parse(df.format(new Date()));
} catch (ParseException e) {
e.printStackTrace();
}
for (String lotId : list){
//拿到当前lotId对应的组件
Element element = elementMapper.selectElementByLotId(lotId);
//若当前组件 所需数量等于拥有数量,则无需分配,跳过当前循环
if((Long.valueOf(element.getReqQyt()) - element.getHavQyt()) <= 0) {
continue;
}
//电池片分配信息表 trace_element_package
List<BaReferEl> elementPackageList = new ArrayList<>();
synchronized (this){
//获取所有未分配完的电池包和未分配的电池包,取前100条记录
//待分配的电池片(按照批次、工厂来源进行筛选)
List<BatteryQty> batteryList = elementMapper.selectBatteryLimit(outno,cjbh);
//开始分配
Iterator<BatteryQty> iterator = batteryList.iterator();
while (iterator.hasNext()){
BatteryQty battery = iterator.next();
//还需要的电池片数量 = 需要的数量 - 拥有数量
Long num = Long.valueOf(element.getReqQyt()) - element.getHavQyt();
//分配记录表 trace_element_package
BaReferEl elementPackage = new BaReferEl();
//设置组件Id
elementPackage.setLotId(element.getLotId());
//设置电池包编号
elementPackage.setSbillno(battery.getSbillNo());
//设置ID
elementPackage.setId(UUID.randomUUID().toString());
//设置操作时间
elementPackage.setUpdated(new Timestamp(date.getTime()));
//若电池片数量 >= 组件所需数量 ,代表组件可以一次分配完
if (battery.getResidue() >= num){
//更新该组件拥有的电池片数量
element.setHavQyt(element.getHavQyt() + num);
//组件足量分配,设置状态
element.setQtyStatus(2);
//因为电池数量能够一次分配完该组件,所有直接设置 需要数量为0
element.setReqQyt("0");
//更新组件信息
element.setUpdated(new Timestamp(date.getTime()));
elementMapper.updateELement(element.getLotId());
//更新电池片信息
battery.setResidue(battery.getResidue() - num);
battery.setUpdated(new Timestamp(date.getTime()));
//若电池片数量 > 组件所需数量 ,分配完后该包电池分配完还剩余部分电池片
if(battery.getResidue() > 0){
//电池片未足量分配,还剩部分电池没分完
battery.setQtyStatus(1);
//更新电池片信息
elementMapper.updateBattery(battery.getSbillNo());
}else if (battery.getResidue() == 0){
//若电池片数量 = 组件所需数量 该电池包足量分配,该包电池分完
battery.setQtyStatus(2);
elementMapper.updateBattery(battery.getSbillNo());
}
//添加 分配记录表 trace_element_package信息
//设置分配给该组件的电池片数量
elementPackage.setQty(num);
elementMapper.insertElePackage(elementPackage);
//这包组件分配完成,继续下一包组件的分配
break;
}else {
//若电池数量 < 组件所需数量,一次分配不完,需要拆第二包
element.setHavQyt(element.getHavQyt() + battery.getResidue());
//更新组件信息
element.setUpdated(new Timestamp(date.getTime()));
//更改组件需要的数量
element.setReqQyt(num.toString());
//组件未足量分配,设置状态
element.setQtyStatus(1);
elementMapper.updateELement(element.getLotId());
//更新电池片信息
battery.setResidue(0L);
battery.setUpdated(new Timestamp(date.getTime()));
//该包电池分完
battery.setQtyStatus(2);
elementMapper.updateBattery(battery.getSbillNo());
//该包电池片足量分配,移除该电池包
iterator.remove();
//添加 分配记录表 trace_element_package信息
elementPackage.setQty(battery.getResidue());
elementMapper.insertElePackage(elementPackage);
//拆第二包,开始循环第二个电池包
}
}
}
}
return commonResult.setMsg("分配完成");
}
//根据lotId查询组件
Element selectElementByLotId(@Param("lotId") String lotId);
//查询未分配完的电池片包 1
List<BatteryQty> selectBattery();
//查询未分配的电池片包 0,取前100条
List<BatteryQty> selectBatteryLimit(@Param("outno") String outno, @Param("cjbh") String cjbh);
//更新element
void updateELement(@Param("lotId") String lotId);
//添加 分配记录表 trace_element_package信息
void insertElePackage(BaReferEl el);
//更新电池片信息
void updateBattery(@Param("sbillNo") String sbillNo);
void insertElement(@Param("list") List<ElementDo> list);
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.nb.easy.easyframe.module.trace.dao.ElementMapper">
<select id="select" resultType="com.nb.easy.easyframe.module.trace.bo.Element">
select object_rrn,parent_lot_rrn,lot_id,lot_type,part_desc,qty_status,source,date,req_qyt,created,hav_qyt from trace_element
<where>
( deleted = 0 or deleted is null)
<if test="item.qtyStatusList != null">
and
<foreach collection="item.qtyStatusList" item="qtyStatus" index="index" open="(" close=")" separator="or">
<if test="qtyStatus == 0">
(qty_status is null or qty_status=0)
</if>
<if test="qtyStatus == 1">
qty_status=1
</if>
<if test="qtyStatus == 2">
qty_status=2
</if>
</foreach>
</if>
<if test="item.lotType != null">
and lot_type like concat('%',#{item.lotType},'%')
</if>
<if test="item.parentLotRrn != null">
and parent_lot_rrn like concat('%',#{item.parentLotRrn},'%')
</if>
<if test="item.source != null">
and source like concat('%',#{item.source},'%')
</if>
<if test="item.lotId != null">
and lot_id like concat('%',#{item.lotId},'%')
</if>
<if test="item.dateStart != null">
and UNIX_TIMESTAMP(date) ">>= UNIX_TIMESTAMP("${item.dateStart}")
</if>
<if test="item.dateEnd != null">
and UNIX_TIMESTAMP(date) < UNIX_TIMESTAMP("${item.dateEnd}")
</if>
</where>
order by date desc
limit #{start} , #{pagesize}
</select>
<select id="selectBy" resultType="com.nb.easy.easyframe.module.trace.bo.Element"
parameterType="java.util.List">
select object_rrn,parent_lot_rrn,lot_id,lot_type,part_desc,qty_status,source,date,req_qyt,created,hav_qyt from trace_element
<where>
1=1
<if test="list!=null">
and
lot_id in
<foreach collection="list" item="item" index="index" open="(" close=")" separator=",">
#{item.lotId}
</foreach>
</if>
and
( deleted = 0 or deleted is null)
</where>
</select>
<select id="selectIds" resultType="com.nb.easy.easyframe.module.trace.bo.Element">
select lot_id,object_rrn from trace_element
where
( deleted = 0 or deleted is null)
<if test="item == 0">
and (checked = 0 or checked is null)
</if>
<if test="item == 1">
and checked = 1
</if>
order by date desc;
</select>
<select id="selectById" resultType="com.nb.easy.easyframe.module.trace.bo.Element"
parameterType="java.util.List">
select object_rrn,parent_lot_rrn,lot_id,lot_type,part_desc,qty_status,source,date,req_qyt,created,hav_qyt from trace_element
<where>
1=1
<if test="list!=null">
and
lot_id in
<foreach collection="list" item="item" index="index" open="(" close=")" separator=",">
#{item}
</foreach>
</if>
</where>
</select>
<select id="tol" resultType="java.lang.Long" >
select count(1) from trace_element
where
deleted=0
<if test="item.parentLotRrn != null">
and parent_lot_rrn like concat('%',#{item.parentLotRrn},'%')
</if>
<if test="item.lotType != null">
and lot_type like concat('%',#{item.lotType},'%')
</if>
<if test="item.source != null">
and source like concat('%',#{item.source},'%')
</if>
<if test="item.lotId != null">
and lot_id like concat('%',#{item.lotId},'%')
</if>
<if test="item.dateStart != null">
and UNIX_TIMESTAMP(date) ">>= UNIX_TIMESTAMP("${item.dateStart}")
</if>
<if test="item.dateEnd != null">
and UNIX_TIMESTAMP(date) < UNIX_TIMESTAMP("${item.dateEnd}")
</if>
</select>
<delete id="delete" parameterType="java.util.List">
delete from trace_element
<where>
object_rrn in
<foreach collection ="list" item="item" separator ="," open="(" close=")">
#{item.objectRrn}
</foreach>
</where>
</delete>
<delete id="deleteRep" >
delete from trace_element_repeat where 1=1
</delete>
<update id="update" parameterType="com.nb.easy.easyframe.module.trace.bo.Element">
update trace_element
<trim prefix="set" suffixOverrides=",">
<if test="item.lotId != null"> lot_id =#{item.lotId},</if>
<if test="item.lotType != null"> lot_type =#{item.lotType},</if>
<if test="item.lotAlias != null"> lot_alias =#{item.lotAlias},</if>
<if test="item.source != null"> source =#{item.source},</if>
<if test="item.date != null"> date =#{item.date},</if>
<if test="item.code != null"> code =#{item.code},</if>
<if test="item.user != null"> user =#{item.user},</if>
<if test="item.reqQyt != null"> req_qyt =#{item.reqQyt},</if>
<if test="item.woId != null"> wo_id =#{item.woId},</if>
<if test="item.user != null"> user =#{item.user},</if>
<if test="item.partRrn != null"> part_rrn =#{item.partRrn},</if>
<if test="item.partName != null"> part_name =#{item.partName},</if>
<if test="item.substrateId1 != null"> substrate_id1 =#{item.substrateId1},</if>
<if test="item.partVersion != null"> part_version =#{item.partVersion},</if>
<if test="item.partDesc != null"> part_desc =#{item.partDesc},</if>
<if test="item.customerCode != null"> customer_code =#{item.customerCode},</if>
<if test="item.customerOrder != null"> customer_order =#{item.customerOrder},</if>
<if test="item.deleted != null"> deleted =#{item.deleted},</if>
<if test="item.checked != null"> checked =#{item.checked},</if>
<if test="item.qtyStatus != null"> qty_status =#{item.qtyStatus},</if>
<if test="item.havQyt != null"> hav_qyt =#{item.havQyt},</if>
updated = now(),
</trim>
where object_rrn = #{item.objectRrn}
</update>
<update id="updateCh" parameterType="java.util.List">
update trace_element set checked=1
where
object_rrn in
<foreach collection ="list" item="item" separator ="," open="(" close=")">
#{item}
</foreach>
</update>
<update id="updateDe" parameterType="java.util.List">
update trace_element set deleted=1
where
object_rrn in
<foreach collection ="list" item="item" separator ="," open="(" close=")">
#{item}
</foreach>
</update>
<insert id="insert" parameterType="com.nb.easy.easyframe.module.trace.bo.Element" >
insert into trace_element
(object_rrn,lot_id,lot_type,lot_alias,source,date,code,user,req_qyt,wo_id,
part_rrn,part_name,substrate_id1,part_version,part_desc,customer_code,customer_order,created)
values
<foreach collection ="list" item="item" separator =",">
(#{item.objectRrn}, #{item.lotId}, #{item.lotType},#{item.lotAlias},
#{item.source}, #{item.date}, #{item.code},#{item.user},
#{item.reqQyt}, #{item.woId}, #{item.partRrn},#{item.partName},
#{item.substrateId1}, #{item.partVersion}, #{item.partDesc},#{item.customerCode},
#{item.customerOrder}, #{item.created}
)
</foreach >
</insert>
<insert id="insertToRep" parameterType="java.util.List">
insert into trace_element_repeat select * from trace_element where
lot_id in
<foreach collection ="list" item="item" separator ="," open="(" close=")">
#{item}
</foreach>
ON DUPLICATE KEY UPDATE
lot_id=values(lot_id),
lot_type=values(lot_type)
</insert>
<select id="selectByParentLotRrn" resultType="com.nb.easy.easyframe.module.trace.bo.Element">
select lot_id,parent_lot_rrn from trace_element
where
( deleted = 0 or deleted is null)
and
parent_lot_rrn in
<foreach collection="list" item="item" index="index" open="(" close=")" separator=",">
#{item}
</foreach>
</select>
<select id="selectByLotIDs" resultType="com.nb.easy.easyframe.module.trace.bo.Element">
select lot_id,parent_lot_rrn, from trace_element
where
( deleted = 0 or deleted is null)
and
lot_id in
<foreach collection="list" item="item" index="index" open="(" close=")" separator=",">
#{item}
</foreach>
</select>
<!--qty_status = 1-->
<select id="batteryStatusFir" resultType="com.nb.easy.easyframe.module.trace.vo.BatteryQueryResultVo">
SELECT sbillno,residue
FROM trace_battery_qty
<where>
and qty_status = 1
<if test="outno != null" >
and outno = #{outno}
</if>
<if test="cjbh != null" >
and cjbh = #{cjbh}
</if>
</where>
</select>
<!--qty_status = 0-->
<select id="batteryStatusSec" resultType="com.nb.easy.easyframe.module.trace.vo.BatteryQueryResultVo">
SELECT sbillno,residue
FROM trace_battery_qty
<where>
and qty_status = 0
<if test="outno != null" >
and outno = #{outno}
</if>
<if test="cjbh != null" >
and cjbh = #{cjbh}
</if>
</where>
</select>
<!--如果传入了组件需要的电池数量reqQyt,则更新默认的电池数量-->
<update id="updateReqQyt">
update trace_element set req_qyt = #{reqQyt}
</update>
<!--如果传入的参数为空则查询所有的,-->
<select id="selectEleAll" resultType="com.nb.easy.easyframe.module.trace.bo.Element">
select * from trace_element
where qty_status = 0 or qty_status = 1
</select>
<!--根据组件Id lot_id去查询-->
<select id="selectEleAllById" resultType="com.nb.easy.easyframe.module.trace.bo.Element">
select object_rrn,parent_lot_rrn,lot_id,lot_type,part_desc,qty_status,source,date,req_qyt,created,hav_qyt from trace_element
where qty_status = 0 or qty_status = 1
and lot_id in #{lotId}
</select>
<!--查询组件表的Excel字段 where req_qyt = 15-->
<select id="selectElementExcelField" resultType="com.nb.easy.easyframe.module.trace.vo.ElementExcelFieldVo">
select lot_id,parent_lot_rrn,source
from trace_element
</select>
<select id="selectElementTest" resultType="com.nb.easy.easyframe.module.trace.bo.Element">
select * from trace_element where object_rrn = 1234567;
</select>
<!--(object_rrn,parent_lot_rrn,lot_id,source) -->
<!-- <insert id="insertElement" parameterType="com.nb.easy.easyframe.module.trace.bo.ElementDo">-->
<!-- INSERT INTO trace_element (object_rrn,parent_lot_rrn,lot_id,source) values (#{objectRrn},#{parentLotRrn},#{lotId},#{source})-->
<!-- </insert>-->
<select id="selectElementByLotId" resultType="com.nb.easy.easyframe.module.trace.bo.Element">
select * from trace_element where lot_id = #{lotId}
</select>
<!--查询所有未分配完的电池包-->
<select id="selectBattery" resultType="com.nb.easy.easyframe.module.trace.bo.BatteryQty">
select * from trace_battery_qty where qty_status = 1
</select>
<!--只取前100条记录-->
<select id="selectBatteryLimit" resultType="com.nb.easy.easyframe.module.trace.bo.BatteryQty">
select * from trace_battery_qty
<where>
and qty_status = 0 OR qty_status = 1
<if test="outno != null" >
and outno = #{outno}
</if>
<if test="cjbh != null" >
and cjbh = #{cjbh}
</if>
</where>
LIMIT 0,100
</select>
<!--更新组件信息-->
<update id="updateELement">
update trace_element set lot_id = #{lotId}
</update>
<!--添加 分配记录表 trace_element_package信息-->
<insert id="insertElePackage" parameterType="com.nb.easy.easyframe.module.trace.bo.BaReferEl">
INSERT INTO trace_element_package (lot_id,sbillno,qty,id,deleted,updated) VALUES (lotId,sbillno,qty,id,0,updated)
</insert>
<!--更新电池片信息-->
<update id="updateBattery">
update trace_battery_qty set sbillno = #{sbillNo}
</update>
<insert id="insertElement" parameterType="com.nb.easy.easyframe.module.trace.bo.ElementDo" >
insert into trace_element
(object_rrn,parent_lot_rrn,lot_id,lot_type,lot_alias,wo_id,part_rrn,part_name,substrate_id1,part_version,
part_desc,customer_code,customer_order,qty_status,source,date,code,user,req_qyt,created,updated,deleted,checked,hav_qyt)
values
<foreach collection ="list" item="item" separator =",">
(#{item.objectRrn},#{item.parentLotRrn},#{item.lotId},#{item.lotType},#{item.lotAlias},#{item.woId},#{item.partRrn},
#{item.partName},#{item.substrateId1},#{item.partVersion},#{item.partDesc},#{item.customerCode},#{item.customerOrder},
#{item.qtyStatus},#{item.source},#{item.date},#{item.code},#{item.user},#{item.reqQyt},
#{item.created},#{item.updated},#{item.deleted},#{item.checked},#{item.havQyt})
</foreach >
</insert>
</mapper>
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.nb.easy.easyframe.module.trace.dao.PvmIvDataMapper">
<!--批量插入-->
<insert id="insertPvm" parameterType="com.nb.easy.easyframe.module.trace.bo.PvmIvData" >
insert into trace_pvm_iv_data
(object_rrn,org_rrn,is_active,created,created_by,updated,updated_by,lock_version,equipment_id,
lot_rrn,lot_id,pmax,imax,vmax,isc,voc,ff,eff,rs,rsh,Watt_marking,temperature,datetime,reserved01,
reserved02,reserved03,reserved04,reserved05,reserved06,reserved07,reserved08,tmod,
insol,sunref,stdisc)
values
<foreach collection ="list" item="item" separator =",">
(#{item.objectRrn},#{item.orgRrn},#{item.isActive},#{item.created},#{item.createdBy},#{item.updated},
#{item.updatedBy},#{item.lockVersion},#{item.equipmentId},#{item.lotRrn},#{item.lotId},#{item.pmax},
#{item.imax},#{item.vmax},#{item.isc},#{item.voc},#{item.ff},#{item.eff},#{item.rs},#{item.rsh},#{item.wattMarking},
#{item.temperature},#{item.datetime},#{item.reserved01},#{item.reserved02},#{item.reserved03},#{item.reserved04},
#{item.reserved05},#{item.reserved06},#{item.reserved07},#{item.reserved08},#{item.tmod},#{item.insol},#{item.sunref},#{item.stdisc})
</foreach >
</insert>
</mapper>