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":"操作成功"
}