GAT專案新需求：保險管理修改

ZHOU_VIP發表於2017-04-19

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


相關文章