问题描述
grid中“授权类型+库存组织编码+编码”三个字段的值一样时，生效时间（起始时间->结束时间 跨度不重叠)
解决思路
保存数据时，先根据前台传递过来的封装好的dto（其中包含了起始时间和结束时间）从数据库中查询生效时间重叠的数据集合list。如果list为空，那么说明数据库中没有与这个dto生效时间重叠的数据，就可以做新增或者更新操作了。如果List不为空，则抛出自定义异常。
关于生效时间，这里认为有以下4种情况
1.起始时间、结束时间都不为空，起始时间-结束时间之间的跨度为生效时间
2.起始时间为空，结束时间不为空，结束时间之前的所有时间为生效时间
3.起始时间不为空，结束时间为空，起始时间之后的所有时间为生效时间
4.起始时间、结束时间都为空，没有生效时间
具体方案
这里从mapper层往上看
- mapper层
在xml文件中，在对应的select语句中添加判断生效时间是否重叠的条件
<select id="selectPurchaserAuth" resultMap="BaseResultMap">
...
<where>
<if test="purchaserId!=null">
and cgepa.PURCHASER_ID = #{purchaserId,jdbcType=DECIMAL}
</if>
<if test="enterpriseId!=null">
and cgepa.ENTERPRISE_ID = #{enterpriseId,jdbcType=DECIMAL}
</if>
<!--add by xiaotong.wu@hand-china.com 2017-10-16-->
<if test="authType!=null and organizationId!=null and itemId!=null and startDate!=null and endDate!=null">
and cgepa.AUTH_TYPE = #{authType}
and cgepa.ORGANIZATION_ID = #{organizationId}
and not (cgepa.START_DATE > #{endDate} or #{startDate} > cgepa.END_DATE )
</if>
<if test="authType!=null and organizationId!=null and itemId!=null and startDate ==null and endDate!=null">
and cgepa.AUTH_TYPE = #{authType}
and cgepa.ORGANIZATION_ID = #{organizationId}
and not cgepa.START_DATE > #{endDate}
</if>
<if test="authType!=null and organizationId!=null and itemId!=null and startDate !=null and endDate==null">
and cgepa.AUTH_TYPE = #{authType}
and cgepa.ORGANIZATION_ID = #{organizationId}
and cgepa.END_DATE > #{startDate}
</if>
<!--add end-->
</where>
</select>
- service层
1.将前台传过来的改变过的数据集合分为新增的数据集合与更新的数据集合
2.分别遍历两个数据集合，先判断数据库中是否有生效时间重叠的数据，再执行相应的操作
/**
* @description 根据实体分页查询出实体的集合
* @param requestContext
* @param dto
* @param page
* @param pageSize
* @return
*/
@Override
public List<GxpEntpPurchaserAuth> selectPurchaserAuth(IRequest requestContext, GxpEntpPurchaserAuth dto, int page,
int pageSize) {
PageHelper.startPage(page, pageSize);
List<GxpEntpPurchaserAuth> gxpEntpPurchaserAuths=gxpEntpPurchaserAuthMapper.selectPurchaserAuth(dto);
return gxpEntpPurchaserAuths;
}
/**
* @description 校验数据的合法性并插入数据库
* @param request
* @param dto
* @return
*/
@Override
public String validateThenSubmit(IRequest request, List<GxpEntpPurchaserAuth> dto) throws Exception {
String message = "";
GxpEntpPurchaserAuth purchaserAuthCheck = new GxpEntpPurchaserAuth();
List<GxpEntpPurchaserAuth> addList = new ArrayList<>();
List<GxpEntpPurchaserAuth> updateList = new ArrayList<>();
for(GxpEntpPurchaserAuth auth:dto){
//把批量提交数据分类，一类为新增数据，一类为更新数据
if(auth.get__status()==null||auth.get__status().trim().equals("")||auth.get__status().trim().equals("add")){
addList.add(auth);
}else {
updateList.add(auth);
}
}
//先更新
for (GxpEntpPurchaserAuth auth:updateList){
purchaserAuthCheck.setAuthId(auth.getAuthId());
purchaserAuthCheck.setPurchaserId(auth.getPurchaserId());
purchaserAuthCheck.setEnterpriseId(auth.getEnterpriseId());
purchaserAuthCheck.setAuthType(auth.getAuthType());
purchaserAuthCheck.setItemId(auth.getItemId());
purchaserAuthCheck.setOrganizationId(auth.getOrganizationId());
purchaserAuthCheck.setStartDate(auth.getStartDate());
purchaserAuthCheck.setEndDate(auth.getEndDate());
List<GxpEntpPurchaserAuth> purchaserAuthList = this.selectPurchaserAuth(request,purchaserAuthCheck,1,10);
if(auth.getStartDate()==null&&auth.getEndDate()==null){
//如果起始日期和生效日期都为null，则不做限制，那么做更新操作
auth.setLastUpdatedBy(request.getUserId());
this.updateByPrimaryKeySelective(request,auth);
} else
if(purchaserAuthList.size()>0){
//起始日期和生效日期不都为null
if((purchaserAuthList.size()==1)&&(purchaserAuthList.get(0).getAuthId().equals(auth.getAuthId()))){
//数据库中存在一条与更新后生效时间重叠的记录，并且这条记录就是要被更新的记录，那么做更新操作
auth.setLastUpdatedBy(request.getUserId());
this.updateByPrimaryKeySelective(request,auth);
}else {
/*1.数据库中存在一条与更新后生效时间重叠的记录，但是这条记录不是要被更新的记录，抛异常
*2.数据库中存在多条与更新后生效时间重叠的记录，抛异常
*/
GxpEntpPurchaserAuth gxpEntpPurchaserAuth = purchaserAuthList.get(0);
String code = "";
if (gxpEntpPurchaserAuth.getAuthType().equals("ITEM")){
code = gxpEntpPurchaserAuth.getItemNo();
}else {
code = gxpEntpPurchaserAuth.getItemCategoryCode();
}
message = "授权类型为:"+auth.getAuthTypeDesc()+",库存组织名称为："+gxpEntpPurchaserAuth.getOrganizationName()+",编码为:"
+code+",生效时间重叠！";
throw new RuntimeException(message);
}
}else {
//数据库中不存在与更新后生效时间重叠的记录，那么做更新操作
auth.setLastUpdatedBy(request.getUserId());
this.updateByPrimaryKeySelective(request,auth);
}
}
//再新增
for(GxpEntpPurchaserAuth auth:addList){
purchaserAuthCheck.setAuthId(auth.getAuthId());
purchaserAuthCheck.setPurchaserId(auth.getPurchaserId());
purchaserAuthCheck.setEnterpriseId(auth.getEnterpriseId());
purchaserAuthCheck.setAuthType(auth.getAuthType());
purchaserAuthCheck.setItemId(auth.getItemId());
purchaserAuthCheck.setOrganizationId(auth.getOrganizationId());
purchaserAuthCheck.setStartDate(auth.getStartDate());
purchaserAuthCheck.setEndDate(auth.getEndDate());
List<GxpEntpPurchaserAuth> purchaserAuthList = this.selectPurchaserAuth(request,purchaserAuthCheck,1,10);
if(auth.getStartDate()==null&&auth.getEndDate()==null){
//起始日期和生效日期都为null，则不做验证，那么做新增操作
this.insertSelective(request,auth);
}else
if(purchaserAuthList.size()>0){
//起始日期和生效日期不都为null
GxpEntpPurchaserAuth gxpEntpPurchaserAuth = purchaserAuthList.get(0);
String code = "";
if (gxpEntpPurchaserAuth.getAuthType().equals("ITEM")){
code = gxpEntpPurchaserAuth.getItemNo();
}else {
code = gxpEntpPurchaserAuth.getItemCategoryCode();
}
message = "授权类型为:"+gxpEntpPurchaserAuth.getAuthTypeDesc()+",库存组织名称为："+gxpEntpPurchaserAuth.getOrganizationName()+",编码为:"
+code+",生效时间重叠！";
throw new RuntimeException(message);
}else {
this.insertSelective(request,auth);
}
}
return message;
}
- controller层
将hap框架自带的批量更新方法batchUpdate换成自定义的validateThenSubmit方法
@RequestMapping(value = "/cux/gxp/entp/purchaser/auth/submit/{enterpriseId}")
@ResponseBody
public ResponseData update(@PathVariable Long enterpriseId, HttpServletRequest request, @RequestBody List<GxpEntpPurchaserAuth> dto){
IRequest requestCtx = createRequestContext(request);
ResponseData message = new ResponseData();
GxpEntpEnterprise gxpEntpEnterprise = new GxpEntpEnterprise();
gxpEntpEnterprise.setEnterpriseId(enterpriseId);
String statusCode = entpEnterpriseService.selectEnterPrise(null,gxpEntpEnterprise,1,1).get(0).getStatusCode();
String msg = null;
try {
msg = service.validateThenSubmit(requestCtx,dto);
} catch (Exception e) {
msg = e.getMessage();
message.setMessage(msg);
message.setSuccess(false);
e.printStackTrace();
}
if(msg.trim().equals("")||msg==null){
message.setSuccess(true);
message.setMessage(msg);
}
if("REVOKED".equals(statusCode)||"REJECTED".equals(statusCode)||"APPROVED".equals(statusCode)) {
gxpEntpEnterprise.setStatusCode("ALTER");
entpEnterpriseService.updateByPrimaryKeySelective(requestCtx,gxpEntpEnterprise);
}
}
- html
将grid的保存按钮的点击事件改为自定义的saveFunction()
saveFunction()中
1.为grid中更新的行数据对象添加一个值为'update'的__status字段
2.为grid中新增的行数据对象添加一个值为'add'的__status字段
3.将所有有变动的数据行发送到后台，并根据后台返回的结果来弹出不同的弹框
var viewModel = kendo.observable({
model: {},
createFunction: function () {
$('#purchaserAuthGrid').data('kendoGrid').addRow();
},
saveFunction: function () {
var dataSource = $('#purchaserAuthGrid').data('kendoGrid').dataSource;
if($('#purchaserAuthGrid').data('kendoGrid').validate()){
var created = dataSource.created();
var updated = dataSource.updated();
$.each(created,function (i,r) {
r['__status'] = 'add';
created[i].purchaserId = purchaserId;
});
$.each(updated,function (i,r) {
r['__status'] = 'update';
});
var total = created.concat(updated);
if((total!=null)&&(total!=[])&&(total.length!==0)){
$.ajax({
url: '${base.contextPath}' + "/cux/gxp/entp/purchaser/auth/submit/"+enterpriseId,
type:"POST",
async:false,
dataType:"json",
contentType:"application/json",
data:kendo.stringify(total),
success:function (args) {
if (args.success==true){
kendo.ui.showInfoDialog({
message:'保存成功！'
});
dataSource.page(1);
}else {
kendo.ui.showErrorDialog({
message:args.message
});
}
}
})
}
}
},
queryResource: function (e) {
$('#purchaserAuthGrid').data('kendoGrid').dataSource.page(1);
}
});
