GAT保险模块的修改

1.示意图

2.数据库的修改

drop table CAR_SECURE;

-- Create table
create table CAR_SECURE
(
  record_id            VARCHAR2(32) not null,
  host_no              VARCHAR2(32),
  secure_no            VARCHAR2(100),
  secure_start_time    DATE,
  secure_end_time      DATE,
  secure_handle        VARCHAR2(32),
  create_user_id       NUMBER(8),
  create_user_name     VARCHAR2(60),
  create_time          DATE,
  create_org_id        NUMBER(8),
  create_dept_id       NUMBER(8),
  create_org_dept_name VARCHAR2(300),
  update_user_id       NUMBER(8),
  update_user_name     VARCHAR2(60),
  update_time          DATE,
  update_org_id        NUMBER(8),
  update_dept_id       NUMBER(8),
  update_org_dept_name VARCHAR2(300),
  host_id              NUMBER(8),
  secure_pay           NUMBER(10,2),
  secure_acc_no        VARCHAR2(100),
  secure_acc_pay       NUMBER(10,2),
  secure_tax           NUMBER(10,2),
  secure_remark        VARCHAR2(300),
  secure_num           NUMBER(10,2)
)
tablespace GPS_USER
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 8K
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table 
comment on table CAR_SECURE
  is '保险';
-- Add comments to the columns 
comment on column CAR_SECURE.record_id
  is '主键';
comment on column CAR_SECURE.host_no
  is '车牌号码';
comment on column CAR_SECURE.secure_no
  is '商业保单号';
comment on column CAR_SECURE.secure_start_time
  is '起保时间';
comment on column CAR_SECURE.secure_end_time
  is '止保时间';
comment on column CAR_SECURE.secure_handle
  is '经办人';
comment on column CAR_SECURE.create_user_id
  is '记录创建人账户编号';
comment on column CAR_SECURE.create_user_name
  is '记录创建人姓名';
comment on column CAR_SECURE.create_time
  is '记录创建时间';
comment on column CAR_SECURE.create_org_id
  is '记录创建人机构编号';
comment on column CAR_SECURE.create_dept_id
  is '记录创建人部门编号';
comment on column CAR_SECURE.create_org_dept_name
  is '记录创建人单位名称';
comment on column CAR_SECURE.update_user_id
  is '记录更新人账户编号';
comment on column CAR_SECURE.update_user_name
  is '记录更新人姓名';
comment on column CAR_SECURE.update_time
  is '记录更新时间';
comment on column CAR_SECURE.update_org_id
  is '记录更新人机构编号';
comment on column CAR_SECURE.update_dept_id
  is '记录更新人部门编号';
comment on column CAR_SECURE.update_org_dept_name
  is '记录更新人单位名称';
comment on column CAR_SECURE.host_id
  is '车辆ID';
comment on column CAR_SECURE.secure_pay
  is '商业保险金额';
comment on column CAR_SECURE.secure_acc_no
  is '交强险保单号';
comment on column CAR_SECURE.secure_acc_pay
  is '交强险金额';
comment on column CAR_SECURE.secure_tax
  is '税';
comment on column CAR_SECURE.secure_remark
  is '备注';
comment on column CAR_SECURE.secure_num
  is '合计金额';
-- Create/Recreate primary, unique and foreign key constraints 
alter table CAR_SECURE
  add constraint CAR_SECURE_PK primary key (RECORD_ID)
  disable;

3.

http://localhost:8080/gps/test/main

superuser

jsecode@123

http://localhost:8080/gps/api.jsp

4.重要说明：

select t.RECORD_ID as recordId,
       t.HOST_ID as hostId,
       t.HOST_NO as carHostNO,
       t.SECURE_PAY as securePay,
       p.VEHICLE_MODEL as vehicleModel,
       po.APPSYSID as appSysId,
       po.APPDEPTID as appDeptId,
       t.SECURE_NO as secureNo,
       to_char(t.SECURE_START_TIME, 'yyyy-MM-dd HH24:mi:ss') as secureStartTime,
       to_char(t.SECURE_END_TIME, 'yyyy-MM-dd HH24:mi:ss') as secureEndTime,
       t.SECURE_HANDLE as secureHandle,
       t.secure_acc_no as secureAccNo,
       t.secure_acc_pay as secureAccPay,
       t.secure_tax as secureTax,
       t.secure_remark as secureRemark,
       (nvl(t.SECURE_PAY,0) + nvl(t.secure_acc_pay,0) + nvl(t.secure_tax,0)) as SECURE_NUM
  from CAR_SECURE_NEW t, POSITION_HOST_INFO_EXTEND p, POSITION_HOST_INFO po
 where t.host_id = p.hostid
   and t.host_id = po.hostid

新增的时候已经把三个费用相加了，所以查询的时候直接查合计金额就可以了，不需要在sql语句中相加了
所以sql可以改为：
select t.RECORD_ID as recordId,
       t.HOST_ID as hostId,
       t.HOST_NO as carHostNO,
       t.SECURE_PAY as securePay,
       p.VEHICLE_MODEL as vehicleModel,
       po.APPSYSID as appSysId,
       po.APPDEPTID as appDeptId,
       t.SECURE_NO as secureNo,
       to_char(t.SECURE_START_TIME, 'yyyy-MM-dd HH24:mi:ss') as secureStartTime,
       to_char(t.SECURE_END_TIME, 'yyyy-MM-dd HH24:mi:ss') as secureEndTime,
       t.SECURE_HANDLE as secureHandle,
       t.secure_acc_no as secureAccNo,
       t.secure_acc_pay as secureAccPay,
       t.secure_tax as secureTax,
       t.secure_remark as secureRemark,
       t.secure_num as secureNum
  from CAR_SECURE_NEW t, POSITION_HOST_INFO_EXTEND p, POSITION_HOST_INFO po
 where t.host_id = p.hostid
   and t.host_id = po.hostid

起保和止保日期都不带时分秒

新增的时候代码修改：
如果三个费用的金额为空，都默认为0，不为空就是本身新增各自输入的值
合计金额是算出来的，页面不给用户输入

导入excel的时候，合计金额是三个金额相加，excel模板中不给合计这一栏

查询的接口：
car/secure/list
{
    "startRow":0,
    "maxSize":20
}

{
    "retCode":1,
    "retMsg":"操作成功",
    "list":[
        {
            "recordId":"20170317114341423010628",
            "appSysId":2009,
            "appDeptId":2,
            "orgDeptName":"盐城供电公司_响水供电公司",
            "carHostNO":"苏JF6772",
            "secureNo":"GGG",
            "secureStartTime":"2016-11-01",
            "secureEndTime":"2016-11-02",
            "secureHandle":"王哈哈",
            "secureAccNo":"NNN",
            "secureAccPay":52,
            "secureTax":45.12,
            "secureRemark":"黄哥哥",
            "secureNum":97.12
        },
        {
            "recordId":"20170317114117211014776",
            "appSysId":2009,
            "appDeptId":2,
            "orgDeptName":"盐城供电公司_响水供电公司",
            "carHostNO":"苏JF6772",
            "secureNo":"AAA",
            "secureStartTime":"2016-11-01",
            "secureEndTime":"2016-11-02",
            "secureHandle":"啊哈哈的",
            "securePay":"102",
            "secureAccNo":"BBB",
            "secureAccPay":156,
            "secureTax":452,
            "secureRemark":"黄磊",
            "secureNum":710
        },
        {
            "recordId":"20170217142337379013138",
            "appSysId":23551,
            "appDeptId":6,
            "orgDeptName":"警保部_财务处",
            "carHostNO":"15000",
            "secureNo":"11111111",
            "secureStartTime":"2017-01-01",
            "secureEndTime":"2017-02-01",
            "secureHandle":"张三2017",
            "vehicleModel":"轿车",
            "securePay":"454",
            "secureAccNo":"QQQQ",
            "secureAccPay":545,
            "secureTax":77,
            "secureRemark":"大阿斯顿",
            "secureNum":1076
        },
        {
            "recordId":"20170217104605546013002",
            "appSysId":23551,
            "appDeptId":1,
            "orgDeptName":"警保部_一支队",
            "carHostNO":"22111111",
            "secureNo":"SFG",
            "secureStartTime":"2017-01-01",
            "secureEndTime":"2017-02-01",
            "secureHandle":"ASDF",
            "securePay":"123",
            "secureAccNo":"ABCDE",
            "secureAccPay":145,
            "secureTax":100,
            "secureRemark":"刘备",
            "secureNum":368
        }
    ],
    "totalNum":23,
    "curRecordNum":4,
    "startRow":0,
    "nextStartRow":4
}


新增的接口：
car/secure/addCarSecure
{
    "carHostNO":"苏JF6772",
    "secureNo":"AAA",
    "secureStartTime":"2016-11-1",
    "secureEndTime":"2016-11-2",
    "secureHandle":"啊哈哈的",
    "securePay":"102",
    "secureAccNo":"BBB",
    "secureAccPay":"156",
    "secureTax":"452",
    "secureRemark":"黄磊"
}

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


修改的接口：
car/secure/modifyCarSecure
{
    "recordId":"20170317162902375015730",
    "secureNo":"ZHOU",
    "secureStartTime":"2016-12-2",
    "secureEndTime":"2016-12-3",
    "secureHandle":"周瑜",
    "securePay":"100.1",
    "secureAccNo":"SHU",
    "secureAccPay":"100.1",
    "secureTax":"100.1",
    "secureRemark":"周瑜"
}

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


删除的接口：
car/secure/delete 
[
    "20161117184331955013046"
]

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


导入的接口：
car/secure/importRecords
[{
    "lineNo":2,
    "carHostNO":"苏H92B67",
    "secureNo":"abcd",
    "secureStartTime":"2016-11-1",
    "secureEndTime":"2016-11-2",
    "secureHandle":"经办人的",
    "securePay":"",
    "secureAccNo":"BBB",
    "secureAccPay":"300",
    "secureTax":"",
    "secureNum":"456",
    "secureRemark":"李冰冰"

},
{
    "lineNo":3,
    "carHostNO":"苏HN1623",
    "secureNo":"abcd",
    "secureStartTime":"2016-11-1",
    "secureEndTime":"2016-11-2",
    "secureHandle":"经办人的",
    "securePay":"",
    "secureAccNo":"BBB",
    "secureAccPay":"300",
    "secureTax":"",
    "secureNum":"123",
    "secureRemark":"范冰冰"

}]

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