@RequestMapping(params = "datagridsbgf")
public void datagridsbgf(LdcCommonStateEntity ldcCommonState, HttpServletRequest request,
HttpServletResponse response, DataGrid dataGrid) {
CriteriaQuery cq = new CriteriaQuery(LdcCommonStateEntity.class, dataGrid);
//查询条件组装器
org.jeecgframework.core.extend.hqlsearch.HqlGenerateUtil.installHql(cq, ldcCommonState, request.getParameterMap());
cq.add();
//分页
int row = dataGrid.getRows();
int page = dataGrid.getPage();
int firstRow = row*(page-1);
int endrow=row+firstRow;
StringBuilder where = new StringBuilder();
String sysOrgCode = request.getParameter("sysOrgCode");
if(StringUtils.isNotEmpty(sysOrgCode)) {
where.append(" and d.sys_Org_Code like '"+sysOrgCode+"%'");
}
// 当核注清单回执和数据订阅回执会插入大量数据到state_comment字段中，这边过滤state_operator !='系统'不然会报错
where.append(" and state_operator !='系统' ");
TSUser user = ResourceUtil.getSessionUser();
//2.获取用户的角色
List<TSRoleUser> rUsers1 = systemService.findByProperty(TSRoleUser.class, "TSUser.id", user.getId());
boolean isture=true;
for (TSRoleUser ru : rUsers1) {
System.out.println(ru.getTSRole().getRoleName());
if(ru.getTSRole().getRoleName().equals("通富管理员")||ru.getTSRole().getRoleName().equals("管理员")){
isture=false;
}
}
if(isture){
String orgcode=jdbcTemplate.queryForObject(" select ORG_CODE from T_S_DEPART where ID=?", String.class,user.getDepartid()) ;
where.append(" and d.sys_Org_Code like '"+orgcode+"%'");
}
// 报关单号
String entryid1 = request.getParameter("entryid1");
if(StringUtils.isNotEmpty(entryid1)) {
where.append(" and entry_id like '%"+entryid1+"%'");
}
// 业务编号
String clientno = request.getParameter("clientno");
if(StringUtils.isNotEmpty(clientno)) {
where.append(" and d.client_no like '%"+clientno+"%'");
}
// 申报日期开始
String declarationDataBegin = request.getParameter("declarationData_begin");
// 申报日期结束
String declarationDataEnd = request.getParameter("declarationData_end");
if(StringUtils.isNotEmpty(declarationDataBegin)) {
where.append(" and d.declaration_data >= '"+declarationDataBegin+"'");
}
if(StringUtils.isNotEmpty(declarationDataEnd)) {
StringBuffer plsb = new StringBuffer(declarationDataEnd);
plsb.append(" 23:59:59");
where.append(" and d.declaration_data <= '"+plsb.toString()+"'");
}
// 进口/出口
String ieFlag = request.getParameter("ieFlag");
if(StringUtils.isNotEmpty(ieFlag)) {
where.append(" and d.ieFlag like '%"+ieFlag+"%'");
}
// 操作
String state = request.getParameter("state");
if(StringUtils.isNotEmpty(state)) {
where.append(" and to_char(state_comment) like '%"+state+"%'");
}else{
where.append(" and (to_char(state_comment) like '%审核通过%' or to_char(state_comment) like '%审核不通过%') ");
}
/*String sql ="SELECT d.id,entry_id as entryId,d.client_no as clientNo,d.bill_no as billNo,d.voy_no as voyNo,d.declaration_data as declarationData,"
+ "d.ieFlag,lcs.state_pname as statePname,lcs.state_comment as stateComment,lcs.state_operator as stateOperator,lcs.state_time as stateTime,lcs.state_pid as statePid "
+ " FROM ldc_common_state lcs LEFT JOIN dec_main d ON d.id = lcs.state_fid "
+ " where d.id is not null and lcs.state_pid is not null "+where.toString()+" and rownum>= "+firstRow+" and rownum<="+firstRow+row;
*/
String sql="SELECT sysOrgCode,ID, entryId, clientNo, billNo, voyNo, declarationData, ieFlag, statePname, stateComment, stateOperator, stateTime, statePid, rn from( SELECT D.sys_Org_Code as sysOrgCode,D.ID, entry_id AS entryId, D.client_no AS clientNo, D.bill_no AS billNo, D.voy_no AS voyNo, D.declaration_data AS declarationData, D.ieFlag, lcs.state_pname AS statePname, lcs.state_comment AS stateComment, lcs.state_operator AS stateOperator, lcs.state_time AS stateTime, lcs.state_pid AS statePid, rownum rn FROM ldc_common_state lcs LEFT JOIN dec_main D ON D.ID = lcs.state_fid WHERE D.ID IS NOT NULL AND lcs.state_pid IS NOT NULL "
+where.toString()+"and rownum"
+ " <= "+endrow+" ) where rn > "+firstRow;
List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
// 节点名通过state_pid取重新查找，进度情况包含操作和备注，需要拆分
for (Map map2 : list) {
// 节点名
String pid = (String) map2.get("statePid");
if(StringUtils.isNotBlank(pid)) {
String num = "Select count(*) from ldc_common_state where id = '"+pid+"' ";
num = jdbcTemplate.queryForObject(num, String.class);
if("1".equals(num)) {
String statePname = "Select state_pname as statePname from ldc_common_state where id = '"+pid+"' ";
statePname = jdbcTemplate.queryForObject(statePname, String.class);
map2.put("statePname", statePname);
}
}
// 操作和备注+++++状态放在第一行，审核不通过为Y其他都为N
String stateComment = (String) map2.get("stateComment");
if(StringUtils.isNotBlank(stateComment)) {
if(stateComment.contains("_")) {
String[] stateComments = stateComment.split("_");
// 获取第一个_之前的字符串
map2.put("state", stateComments[0]);
// 获取第一个_之后的字符串
map2.put("comment", stateComment.substring(stateComment.indexOf("_")+1));
if(stateComments[0].equals("审核不通过")) {
map2.put("zhuangtai", "N");
}else {
map2.put("zhuangtai", "Y");
}
}else {
map2.put("state", stateComment);
if(stateComment.equals("审核不通过")) {
map2.put("zhuangtai", "N");
}else {
map2.put("zhuangtai", "Y");
}
}
}
}
String sql1 = "SELECT count(*) "
+ " FROM ldc_common_state lcs LEFT JOIN dec_main d ON d.id = lcs.state_fid "
+ " where d.id is not null and lcs.state_pid is not null "+where.toString();
int count= jdbcTemplate.queryForObject(sql1, Integer.class);
dataGrid.setTotal(count);
dataGrid.setResults(list);
TagUtil.datagrid(response, dataGrid);
}
