GAT项目新需求：保险管理修改

1.需求：

2.原表

3.现表

4.SQL语句：

--删除字段

ALTER TABLE CAR_SECURE DROP COLUMN SECURE_ACC_NO

ALTER TABLE CAR_SECURE DROP COLUMN SECURE_ACC_PAY

ALTER TABLE CAR_SECURE DROP COLUMN SECURE_NUM

5.代码修改：

新增时输入车牌号查询，带出发动机号和车架号：

/**
 * 查询车辆基本信息(根据车牌号码,主要查出车型和使用部门)
 * 后面增加了查询带出车架号和发动机号
 */
@RequestMapping(value = "/queryByHostNo/{hostNo}", method = RequestMethod.GET)
@ResponseBody
public BaseResp queryByHostNo(@PathVariable String hostNo){
    AbstractRequestExecutor executor = new AbstractRequestExecutor("请求访问车辆基本信息查询接口", "query host detail"){
        @Override
        protected DetailResp queryDetail(String hostNo) throws Exception{
            return carDealEtcService.queryByHostNo(hostNo);
        }
    };

    return executor.executeQueryDetail(hostNo);
}

DetailResp queryByHostNo(String hostNo) throws Exception;

@Override
public DetailResp queryByHostNo(String hostNo) throws Exception {
	DetailResp resp = new DetailResp();
	PositionHostInfo phiResp = hostManagerDAO.queryByHostNo(hostNo);
	//根据hostid查出车型
	PositionHostInfoExtend phieResp = null;
	if(Util.isNotEmpty(phiResp)){
		 phieResp = hostManagerDAO.getVehicleModel(phiResp.getHostid());
	}
	PositionHostInfoExtendTwo pt = new PositionHostInfoExtendTwo();
	if(Util.isNotEmpty(phiResp)){
		pt.setAppdeptid(phiResp.getAppdeptid());
		pt.setAppsysid(phiResp.getAppsysid());
	}
	if(Util.isNotEmpty(phieResp)){
		//车型
		pt.setVehicleModel(phieResp.getVehicleModel());
		//车架号
		pt.setHostVin(phieResp.getHostVin());
		//发动机
		pt.setEngineId(phieResp.getEngineId());
	}
	if(Util.isEmpty(pt)){
		resp.setRetCode(Constant.DATA_NOT_EXIST);
		resp.setRetMsg("查询不到数据，查询操作中止");
		return resp;
	}
	
	//翻译字典项
	DictionaryUtil.translate(pt);
	
	resp.setRetCode(Constant.RETURN_SUCCESS);
	resp.setRetMsg("查询操作成功");
	
	resp.setDetail(pt);
	
	return resp;
}

列表中要显示发动机号和车架号：

新增、修改、导入都比较类似：

CarSecureInfo：实体类中注掉不需要的字段

映射文件：

CarSecureListResp：

PositionHostInfoExtendTwo：查询带出发动机号和车架号

CarSecureEditReq：注掉不需要的字段，用于新增和修改

测试OK：

select t.RECORD_ID as recordId,
       t.HOST_ID as hostId,
       t.HOST_NO as carHostNO,
       t.SECURE_PAY as securePay,
       p.HOST_VIN as hostVin,
       p.ENGINE_ID as engineId,
       po.APPSYSID as appSysId,
       po.APPDEPTID as appDeptId,
       t.SECURE_NO as secureNo,
       to_char(t.SECURE_START_TIME, 'yyyy-MM-dd') as secureStartTime,
       to_char(t.SECURE_END_TIME, 'yyyy-MM-dd') as secureEndTime,
       t.SECURE_HANDLE as secureHandle,
       t.SECURE_TAX as secureTax,
       t.SECURE_REMARK as secureRemark
  from CAR_SECURE t, POSITION_HOST_INFO_EXTEND p, POSITION_HOST_INFO po
 where t.host_id = p.hostid
   and t.host_id = po.hostid
   and ((po.appsysid, po.appdeptid) in
       (select appsysid, appdeptid from table(get_subdept(-1, 2000, 0))))
 order by t.UPDATE_TIME desc
============================================================================
查询：
car/secure/list

 {
    "appSysId":"2009",
    "appDeptId":"2",
    "carHostNO":"苏JF6772",
    "startRow":0,
    "maxSize":20
}

{
    "retCode":1,
    "retMsg":"操作成功",
    "list":[
        {
            "recordId":"20170323140314738012734",
            "appSysId":2009,
            "appDeptId":2,
            "orgDeptName":"盐城供电公司_响水供电公司",
            "carHostNO":"苏JF6772",
            "secureNo":"AAA",
            "secureStartTime":"2016-11-01",
            "secureEndTime":"2016-11-02",
            "secureHandle":"啊哈哈的",
            "securePay":0.05,
            "secureTax":0,
            "secureRemark":"詹姆斯3",
            "engineId":"826197X",
            "hostVin":"L_2JNMFE2G5EN101417"
        },
        {
            "recordId":"20170323134019153013137",
            "appSysId":2009,
            "appDeptId":2,
            "orgDeptName":"盐城供电公司_响水供电公司",
            "carHostNO":"苏JF6772",
            "secureNo":"AAA",
            "secureStartTime":"2016-11-01",
            "secureEndTime":"2016-11-02",
            "secureHandle":"啊哈哈的",
            "securePay":102.24,
            "secureTax":452.23,
            "secureRemark":"詹姆斯2",
            "engineId":"826197X",
            "hostVin":"L_2JNMFE2G5EN101417"
        },
        {
            "recordId":"20170323133627911014404",
            "appSysId":2009,
            "appDeptId":2,
            "orgDeptName":"盐城供电公司_响水供电公司",
            "carHostNO":"苏JF6772",
            "secureNo":"AAA",
            "secureStartTime":"2016-11-01",
            "secureEndTime":"2016-11-02",
            "secureHandle":"啊哈哈的",
            "securePay":102,
            "secureTax":452,
            "secureRemark":"詹姆斯",
            "engineId":"826197X",
            "hostVin":"L_2JNMFE2G5EN101417"
        },
        {
            "recordId":"20170320162514963019983",
            "appSysId":2009,
            "appDeptId":2,
            "orgDeptName":"盐城供电公司_响水供电公司",
            "carHostNO":"苏JF6772",
            "secureNo":"AAA",
            "secureStartTime":"2016-11-01",
            "secureEndTime":"2016-11-02",
            "secureHandle":"啊哈哈的",
            "securePay":102,
            "secureTax":452,
            "secureRemark":"黄磊1",
            "engineId":"826197X",
            "hostVin":"L_2JNMFE2G5EN101417"
        },
        {
            "recordId":"20170320145248466019516",
            "appSysId":2009,
            "appDeptId":2,
            "orgDeptName":"盐城供电公司_响水供电公司",
            "carHostNO":"苏JF6772",
            "secureNo":"AAAA",
            "secureStartTime":"2017-01-01",
            "secureEndTime":"2017-02-01",
            "secureHandle":"张三88",
            "securePay":1000,
            "secureTax":120,
            "secureRemark":"啊哈哈",
            "engineId":"826197X",
            "hostVin":"L_2JNMFE2G5EN101417"
        },
        {
            "recordId":"20170320145248968014513",
            "appSysId":2009,
            "appDeptId":2,
            "orgDeptName":"盐城供电公司_响水供电公司",
            "carHostNO":"苏JF6772",
            "secureNo":"BBBB",
            "secureStartTime":"2017-02-01",
            "secureEndTime":"2017-03-01",
            "secureHandle":"李四88",
            "securePay":2000,
            "secureTax":101,
            "secureRemark":"额呵呵",
            "engineId":"826197X",
            "hostVin":"L_2JNMFE2G5EN101417"
        }
    ],
    "totalNum":6,
    "curRecordNum":6,
    "startRow":0,
    "nextStartRow":6,
    "orgDeptName":"盐城供电公司_响水供电公司"
}
======================================================================
输入车牌号查询车型和使用单位接口和车架号、还有发动机号：
car/secure/queryByHostNo/hh1007

{
    "retCode":1,
    "retMsg":"查询操作成功",
    "detail":{
        "appsysid":23551,
        "appdeptid":6,
        "orgDeptName":"警保部_财务处",
        "vehicleModel":"轿车",
        "engineId":"ss1",
        "hostVin":"dd1"
    }
}
======================================================================
新增：
car/secure/addCarSecure

{
	"carHostNO":"苏JF6772",
	"secureNo":"abcdefg",
	"secureStartTime":"2016-11-1",
	"secureEndTime":"2016-11-2",
	"secureHandle":"啊哈哈的",
    "securePay":"20",
    "secureTax":"30",
    "secureRemark":"嘿嘿嘿"

}

{
    "retCode":1,
    "retMsg":"操作成功"
}
======================================================================
修改：
car/secure/modifyCarSecure

{
	"recordId":"20170418100755826011594",
    "carHostNO":"人a1003",
	"secureNo":"AQWER2",
	"secureStartTime":"2016-11-2",
	"secureEndTime":"2016-11-3",
	"secureHandle":"哈哈的2",
    "securePay":"22",
    "secureTax":"32",
    "secureRemark":"嘿嘿嘿2"

}

{
    "retCode":1,
    "retMsg":"操作成功"
}

======================================================================
导入：
car/secure/importRecords

[{
    "carHostNO":"苏JF6772",
    "secureNo":"abcdefg",
    "secureStartTime":"2016-11-1",
    "secureEndTime":"2016-11-2",
    "secureHandle":"啊哈哈的",
    "securePay":"20",
    "secureTax":"30",
    "secureRemark":"嘿嘿嘿"

}]

{
    "retCode":1,
    "retMsg":"操作成功"
}