package com.bcxin.backend.service.impl; import cn.hutool.http.HttpRequest; import cn.hutool.http.HttpUtil; import com.alibaba.fastjson.JSONObject; import com.bcxin.backend.pojo.*; import com.bcxin.backend.service.SecuritySevicekService; import com.bcxin.backend.utils.SMEncryptUtil; import lombok.extern.slf4j.Slf4j; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.factory.annotation.Qualifier; import org.springframework.beans.factory.annotation.Value; import org.springframework.jdbc.core.BeanPropertyRowMapper; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.stereotype.Service; import org.springframework.util.ObjectUtils; import org.springframework.util.StringUtils; import java.text.SimpleDateFormat; import java.time.LocalDateTime; import java.time.format.DateTimeFormatter; import java.util.ArrayList; import java.util.Collection; import java.util.List; import java.util.Map; @Service @Slf4j public class SecuritySevicekServiceimpl implements SecuritySevicekService { @Autowired @Qualifier("governmentJdbcTemplate") protected JdbcTemplate jdbcTemplate; //域名配置 @Value("${myapps.nxgjApi.DN}") String DN; //归集接口 配置文件中获取 @Value("${myapps.nxgjApi.gjurl}") String gjUrl; @Value("${myapps.nxgjApi.tokenUrl}") String tokenUrl; @Value("${myapps.nxgjApi.NX_CLIENTID}") String NX_CLIENTID; @Value("${myapps.nxgjApi.NX_SECRETID}") String NX_SECRETID; @Override public String getSql() { String sql="select * from ( \n" + "select id,FORMID,created,lastmodified,AUDITUSER,ITEM_COMPANYNAME,ITEM_AUDITSTATUS,ITEM_REJECTREASON ,'保安服务许可申请' as Type, '无' as item_creditCode,TimeStampDiff(DAY,CREATED,LASTMODIFIED)+2 as timeL,replace(replace(SUBSTRING_INDEX(SUBSTRING_INDEX(ITEM_TEMPLATERARFILE,\",\\\"path\\\":\\\"\",-1),'\\\"',1),'\\\\',''),'/obpm/api/runtime','') as file from tlk_securitysevicek where ITEM_AUDITSTATUS in('0','1') and item_SyncStatus is null and created >'2021-01-01'\n" + "union \n" + "SELECT id,FORMID,created,lastmodified,AUDITUSER,ITEM_COMPANYNAME,ITEM_AUDITSTATUS,ITEM_REJECTREASON,'许可证项目法人变更' as Type ,'无' as item_creditCode,TimeStampDiff(DAY,CREATED,LASTMODIFIED)+2 as timeL,replace(replace(SUBSTRING_INDEX(SUBSTRING_INDEX(ITEM_FILE,\",\\\"path\\\":\\\"\",-1),'\\\"',1),'\\\\',''),'/obpm/api/runtime','') as file from tlk_legalchangek where ITEM_AUDITSTATUS in('0','1') and item_SyncStatus is null and created >'2021-01-01'\n" + "UNION\n" + "SELECT id,FORMID,created,lastmodified,AUDITUSER,ITEM_COMPANYNAME,ITEM_AUDITSTATUS,ITEM_REJECTREASON,'自行招用保安员单位备案' as Type,item_creditCode,TimeStampDiff(DAY,CREATED,LASTMODIFIED)+2 as timeL,replace(replace(SUBSTRING_INDEX(SUBSTRING_INDEX(ITEM_FILE,\",\\\"path\\\":\\\"\",-1),'\\\"',1),'\\\\',''),'/obpm/api/runtime','') as file from tlk_recruitment where ITEM_AUDITSTATUS in('0','1') and item_SyncStatus is null and created >'2021-01-01'\n" + "union\n" + "SELECT id,FORMID,created,lastmodified,AUDITUSER,ITEM_COMPANYNAME,ITEM_AUDITSTATUS,ITEM_REJECTREASON,'保安公司设立分公司备案' as Type,item_creditCode,TimeStampDiff(DAY,CREATED,LASTMODIFIED)+2 as timeL,replace(replace(SUBSTRING_INDEX(SUBSTRING_INDEX(ITEM_TEMPLATERARFILE,\",\\\"path\\\":\\\"\",-1),'\\\"',1),'\\\\',''),'/obpm/api/runtime','') as file from tlk_establish where ITEM_AUDITSTATUS in('0','1') and item_SyncStatus is null and created >'2021-01-01'\n" + "union\n" + "SELECT id,FORMID,created,lastmodified,AUDITUSER,ITEM_COMPANYNAME,ITEM_AUDITSTATUS,ITEM_REJECTREASON,'外省保安跨区服务设立分公司备案' as Type,item_creditCode,TimeStampDiff(DAY,CREATED,LASTMODIFIED)+2 as timeL,replace(replace(SUBSTRING_INDEX(SUBSTRING_INDEX(ITEM_TEMPLATERARFILE,\",\\\"path\\\":\\\"\",-1),'\\\"',1),'\\\\',''),'/obpm/api/runtime','') as file from tlk_crossr where ITEM_AUDITSTATUS in('0','1') and item_SyncStatus is null and created >'2021-01-01'\n" + "union\n" + "SELECT id,FORMID,created,lastmodified,AUDITUSER,ITEM_COMPANYNAME,ITEM_AUDITSTATUS,ITEM_REJECTREASON,'保安培训单位备案' as Type ,'无' as item_creditCode,TimeStampDiff(DAY,CREATED,LASTMODIFIED)+2 as timeL,replace(replace(SUBSTRING_INDEX(SUBSTRING_INDEX(ITEM_TEMPLATERARFILE,\",\\\"path\\\":\\\"\",-1),'\\\"',1),'\\\\',''),'/obpm/api/runtime','') as file from tlk_securitytrainings where ITEM_AUDITSTATUS in('0','1') and item_SyncStatus is null and created >'2021-01-01'\n" + ") b limit 20 "; return sql; } /** * 执行保安服务许可申请数据归集 */ @Override public void executeSecuritySeviceK() { Collection> collection = jdbcTemplate.queryForList(getSql()); log.error("查询数据库securityFlow，符合条件人数:{}", collection.size()); if (!ObjectUtils.isEmpty(collection) && collection.size() != 0) { collection.stream().forEach(map -> { JSONObject json = new JSONObject(); JSONObject json2 = new JSONObject(); List list1=new ArrayList(); List list2=new ArrayList(); List list3=new ArrayList(); //签名 循环里面时间戳需要不一样 String timestamp = System.currentTimeMillis()+""; Long time=System.currentTimeMillis(); String sign = SMEncryptUtil.sm3Encrypt(NX_CLIENTID + "|" + NX_SECRETID + "|"+ timestamp); //办件受理 UpProAccept upProAccept=new UpProAccept(); //办件过程 UpProProcess upProProcess=new UpProProcess(); //办件结果 UpProResult upProResult=new UpProResult(); //办件材料 UpProMaterial upProMaterial=new UpProMaterial(); //特殊材料 UpProSpecialProcedure upProSpecialProcedure = new UpProSpecialProcedure(); //时间格式 SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); //获取民警名称和对应的部门名称 String policeName=null; String departName=null; if (map.get("AUDITUSER")!= null){ PoliceDto policeDto=getPoliceNameById(map.get("AUDITUSER").toString()); if (policeDto!=null){ policeName=policeDto.getPoliceName(); departName=policeDto.getDepartName(); } } //办件受理信息参数公共部分 upProAccept.setApplyType("无");//申请类型 upProAccept.setApplyerName("无");//申请人名称 upProAccept.setApplyerPageCode("无");//申请人身份证 upProAccept.setApplyerPageType("无");//申请人证件类型 upProAccept.setApplyerType("1");//申请人类型 upProAccept.setAreaId("640000");//宁夏行政区划代码 校验区划位数是不是12位数 upProAccept.setCatalogCode("640000");//宁夏基本编码 upProAccept.setIsItemBase("0");//是否是事项库办件 upProAccept.setCdOperation("I");//同步类型，I：新增件， U：修改件 upProAccept.setContractMobile("无"); upProAccept.setDataSource("nxjgjg_bagl");//数据来源 固定值：部门名称首字母+系统名称首字母，(下划线链接’_’) 所属部门+系统名称的首字母 upProAccept.setProjectType("1");//办件类型1 即办件 2 承诺件 upProAccept.setPromiseTime(map.get("timeL") != null ? map.get("timeL").toString() : "无");//承诺时限, 最大5位数 upProAccept.setTaskVersion("1");// 事项版本 1 upProAccept.setPcc("5");//所属警钟 upProAccept.setOrgName(departName!=null ? departName : "无");//部门名称 upProAccept.setSys_lev("1");//所属系统级别 upProAccept.setSys_name("许可备案管理");//系统名称 upProAccept.setHandleUserName(policeName!=null ? policeName : "无");//民警名称 upProAccept.setOrgCode(map.get("item_creditCode") != null ? map.get("item_creditCode").toString() : "无");//区划内实施主体统一社会信用代码（见.字典附件）编码+无 upProAccept.setAcceptDate(map.get("created") != null ? ((LocalDateTime)map.get("created")).format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")) : null);//取到受理时间 upProAccept.setApplyDate(map.get("created") != null ? ((LocalDateTime)map.get("created")).format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")) : null);//取到申请时间 //推送时间 upProAccept.setCdTime(simpleDateFormat.format(time));//推送时间 upProAccept.setEnterpriseName(map.get("ITEM_COMPANYNAME") != null ? map.get("ITEM_COMPANYNAME").toString() : "无");//公司名称 upProAccept.setLocalProjectNo(map.get("id") != null ? map.get("id").toString() : null);//本地办件编号 upProAccept.setProjectNo(map.get("id") != null ? map.get("id").toString() : null);//办件编号(唯一编号) 本地id upProAccept.setPromiseDate(map.get("LASTMODIFIED") != null ? ((LocalDateTime)map.get("LASTMODIFIED")).format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")) : null);//承诺办结时间, 格式 yyyy-MM-dd HH:mm:ss upProAccept.setRowGuid(map.get("id") != null ? map.get("id").toString() : null);//记录唯一标识 本地id upProAccept.setCompanyCode(map.get("item_creditCode") != null ? map.get("item_creditCode").toString() : "无");//企业信用代码 upProAccept.setHandleUserId(map.get("AUDITUSER") != null ? map.get("AUDITUSER").toString() : "无");//受理人员id 民警ID //区分表单名字和表单id upProAccept.setTaskName(map.get("Type").toString());//事项名称政务服务事项的具体名称 upProAccept.setTaskCode(map.get("FORMID") != null ? map.get("FORMID").toString() : null); upProAccept.setTaskHandleItem(map.get("FORMID") != null ? map.get("FORMID").toString() : null);//业务办理项编码 module id upProAccept.setFileId(map.get("FORMID") != null ? map.get("FORMID").toString() : null);//事项id upProAccept.setModel_id(map.get("FORMID") != null ? map.get("FORMID").toString() : null);//事项model_id upProAccept.setLocalItemCode(map.get("FORMID") != null ? map.get("FORMID").toString() : null);//本地事项id upProAccept.setLocalTaskCode(map.get("FORMID") != null ? map.get("FORMID").toString() : null);//地方实施编码 //办件过程信息参数公共部分 upProProcess.setCdOperation("I");//同步类型，I：新增件， U：修改件 upProProcess.setCdTime(simpleDateFormat.format(time));//同步时间 yyyy-MM-dd HH:mm:ss now upProProcess.setDataSource("nxjgjg_bagl"); upProProcess.setHandleExplain("无");//办理意见 (备注) 无 upProProcess.setProcessName("办结");//办理环节名称 upProProcess.setIsNeedService("0");//本环节是否需要评价 1 是， 0否 upProProcess.setEventEndTime(map.get("LASTMODIFIED") != null ? ((LocalDateTime)map.get("LASTMODIFIED")).format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")) : null);//环节结束时间 yyyy-MM-dd HH:mm:ss upProProcess.setEventName("1");//业务动作 1 通过2 退回9 其他 过程默认通过 upProProcess.setEventStartTime(map.get("created") != null ? ((LocalDateTime)map.get("created")).format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")): null); upProProcess.setHandleUserName(policeName!=null ? policeName : "无");//办理人姓名 民警名称 upProProcess.setProcessId(map.get("id") != null ? map.get("id").toString() : null);//记录唯一标识本地id upProProcess.setProjectNo(map.get("id") != null ? map.get("id").toString() : null);//办件编号本地id upProProcess.setRowGuid(map.get("id") != null ? map.get("id").toString() : null);///本地id //添加到集合 list1.add(upProProcess); //办件结果信息参数公共部分 upProResult.setCdOperation("I");//同步类型，I：新增件， U：修改件 upProResult.setCdTime(simpleDateFormat.format(time));//同步时间 yyyy-MM-dd HH:mm:ss now upProResult.setDataSource("nxjgjg_bagl"); upProResult.setIsDeliveryResults("0");//是否快递递送结果，1 是， 0否 upProResult.setHandleUserName(policeName!=null ? policeName : "无");//办件人员结果姓名 upProResult.setProjectNo(map.get("id") != null ? map.get("id").toString() : null);//办件编号 本地id upProResult.setResultDate(map.get("LASTMODIFIED") != null ? ((LocalDateTime)map.get("LASTMODIFIED")).format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")) : null);//办结时间 yyyy-MM-dd HH:mm:ss upProResult.setResultExplain(map.get("ITEM_REJECTREASON") != null ? map.get("ITEM_REJECTREASON").toString() : "无");//办件结果描述 /** * 办件结果 * 0 不予批准 * 1 批准 * 2 转报 * 3 终止办件 */ upProResult.setResultType(map.get("ITEM_AUDITSTATUS") != null ? map.get("ITEM_AUDITSTATUS").toString() : "无");//判断办件结果 upProResult.setRowGuid(map.get("id") != null ? map.get("id").toString() : null);//记录标识(办件ID)）本地id //材料信息参数公共部分 upProMaterial.setCdOperation("I");//同步类型，I：新增件， U：修改件 upProMaterial.setCdTime(simpleDateFormat.format(System.currentTimeMillis()));//同步时间 yyyy-MM-dd HH:mm:ss now upProMaterial.setDataSource("nxjgjg_bagl"); upProMaterial.setGetType("2");//收取方式 upProMaterial.setMaterialAttachName("无"); upProMaterial.setMaterialName("材料归集");//材料名称 提交的材料名称 upProMaterial.setProjectNo(map.get("id") != null ? map.get("id").toString() : null);//办件编号 本地id upProMaterial.setRemark("无");//备注 upProMaterial.setRowGuid(map.get("id") != null ? map.get("id").toString() : null);//记录标识(办件ID) 本地id upProMaterial.setUrl(map.get("file") != null ? DN+map.get("file").toString() : "无");//附件地址(没有填 无) list3.add(upProMaterial); json.put("clientId",NX_CLIENTID); json.put("sign",sign); json.put("timestamp",timestamp); json.put("tskId",map.get("id") != null ? map.get("id").toString() : null); json.put("upProAccept",upProAccept); json.put("upProMaterial",list3); json.put("upProResult",upProResult); json.put("upProProcess",list1); json.put("upProSpecialProcedure",list2); log.error("body参数:{}",json.toJSONString()); //sm4加密 json2.put("resbody",SMEncryptUtil.sm4Encrypt(json.toJSONString())); String token=this.getToken(); log.error("数据token:{}",token); //判断token不为空 if (!StringUtils.isEmpty(token)){ //请求gj接口 JSONObject gjJson=this.gjPost(json2,token); log.error("gjJson接口参数:{}",gjJson.toJSONString()); if (gjJson.getObject("succ",Boolean.class)&&"操作成功".equals(gjJson.getString("msg"))){ //进入更新字段 upDateById(map.get("id").toString(),map.get("Type").toString()); } } }); } } /** * 获取token * @return */ public String getToken(){ String token=""; JSONObject json = new JSONObject(); json.put("clientId",NX_CLIENTID); json.put("secretId", SMEncryptUtil.sm3Encrypt(NX_SECRETID)); json.put("timestamp",System.currentTimeMillis()+""); String ret = HttpUtil.post(tokenUrl,json.toJSONString()); JSONObject jsonObject=JSONObject.parseObject(ret); jsonObject.getString("succ"); if (jsonObject.getObject("succ",Boolean.class)){ JSONObject jsonObject1=jsonObject.getJSONObject("data"); token=jsonObject1.getString("token"); } return token; } /** * 归集接口 * @return */ public JSONObject gjPost(JSONObject jsonObject,String token){ String ret=HttpRequest.post(gjUrl).header("Authorization",token).body(jsonObject.toJSONString()).execute().body(); JSONObject jsonObjectTwo=JSONObject.parseObject(ret); return jsonObjectTwo; } /** * 更新接口 */ public void upDateById(String id,String module){ String formName=""; switch (module){ case "保安服务许可申请": formName="tlk_securitysevicek"; break; case "许可证项目法人变更": formName="tlk_legalchangek"; break; case "自行招用保安员单位备案": formName="tlk_recruitment"; break; case "保安公司设立分公司备案": formName="tlk_establish"; break; case "外省保安跨区服务设立分公司备案": formName="tlk_crossr"; break; case "保安培训单位备案": formName="tlk_securitytrainings"; break; default: break; } log.error("formName-->{}",formName); String sql = " update "+formName+" set item_SyncStatus = 1 where ID= '"+id+"'"; jdbcTemplate.update(sql); } /** * 返回民警对象 * @param id * @returnu */ public PoliceDto getPoliceNameById(String id){ PoliceDto policeDto; String sql="SELECT u.name as policeName,u.id as policeId,d.name as departName from obpm2_security.t_user u join obpm2_security.t_department d on u.DEFAULTDEPARTMENT=d.id where u.id= ? "; System.out.println(sql); try { policeDto= jdbcTemplate.queryForObject(sql,new Object[]{id},new BeanPropertyRowMapper(PoliceDto.class)); }catch (Exception e){ policeDto=null; } return policeDto; } }