Cacher is the code snippet organizer for pro developers

import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;


@Service
@Transactional(rollbackFor = Exception.class)
public class ServLogServiceImpl extends BaseServiceImpl<ServLogRepository,ServLog > implements ServLogService {
	@Autowired
	private ServLogRepository servLogRepository;
	@Autowired
	private EntityManager entityManager;
	@Autowired
	private InfoCategoryService infoCategoryService;
	@Autowired
	private ProviderRepository providerRepository;
	
	@Override
	public Page< ServLog > findAll ( ServLog servLog, Pageable pageable ) {
		if(servLog == null)
			return this.findAll(pageable);
		Specification<ServLog> spec = new Specification<ServLog>() {
			private static final long serialVersionUID = 1L;

			@Override
			public Predicate toPredicate(Root<ServLog> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
				List<Predicate> predicates = new ArrayList<> ();
				//todo 新增查询条件；
//				if (null != outServAccLog.getSuccess()) {
//					Predicate p = null;
//					p = criteriaBuilder.equal(root.get(OutServAccLog.PROP_SUCCESS), outServAccLog.getSuccess());
//					predicates.add(p);
//				}
//				if (null != outServAccLog.getBegin()) {
//					Predicate p = null;
//					p = criteriaBuilder.greaterThanOrEqualTo(root.get(OutServAccLog.PROP_REQ_TIME), outServAccLog.getBegin());
//					predicates.add(p);
//				}
//				if (null != outServAccLog.getEnd()) {
//					Predicate p = null;
//					p = criteriaBuilder.lessThanOrEqualTo(root.get(OutServAccLog.PROP_REQ_TIME), outServAccLog.getEnd());
//					predicates.add(p);
//				}
//				if ( StringUtils.isNotBlank ( outServAccLog.getRespSys () )) {
//					Predicate p = null;
//					p = criteriaBuilder.like(root.get(OutServAccLog.PROP_RESP_SYS),
//							MessageFormat.format ( "%{0}%", outServAccLog.getRespSys () ));
//					predicates.add(p);
//				}
				return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]));
			}
		};
		return servLogRepository.findAll(pageable);
	}

	@Override
	public void saveAll ( List< ServLog > list ) {
		servLogRepository.saveAll ( list );
	}
	/**
	 * 拼写异常查询明细SQL语句
	 */
	private StringBuilder getExcQuerySQL(PageVO<ServLog> pageVO, ServLog servLog,String slInfoCategoryName,BizErrorCodeEnum bizErrorCode) {
		StringBuilder dataSql = new StringBuilder("SELECT o.name AS req_name, s.request_user AS req_user ,");
		if(bizErrorCode != null) {
			dataSql.append("SUM(case when biz_err_code= '"+bizErrorCode.getValue()+"' then 1 else 0 end) AS exc_times,");
		}else {
			dataSql.append("SUM(case when normal=0 then 1 else 0 end) AS exc_times,");
		}
		dataSql.append("COUNT(s.request_user) AS total FROM serv_log s LEFT JOIN organ o ON o.no = s.request_org WHERE 1=1 ");		
				
		
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		dataSql.append("AND request_time>='"+sdf.format(servLog.getBegin())+"' ");
		//当前结束时间加一天
		Calendar c = Calendar.getInstance();
		c.setTime(servLog.getEnd());
		c.add(Calendar.DAY_OF_MONTH, 1);// 今天+1天
		Date End = c.getTime();
		servLog.setEnd(End);
			
		dataSql.append("AND request_time<'"+sdf.format(servLog.getEnd())+"' ");
		if(slInfoCategoryName != null && !"".equals(slInfoCategoryName)) {
			
			String infoCategoryId = null;
			List<InfoCategory> infoCategoryList = infoCategoryService.findAll();
			for(InfoCategory infoCategory:infoCategoryList) {
				if((infoCategory.getName()).equals(slInfoCategoryName)) {
					infoCategoryId = String.valueOf(infoCategory.getId());
				}
			}
			servLog.setInfoCategoryId(Long.valueOf(infoCategoryId));
			
			dataSql.append("AND info_category_id="+servLog.getInfoCategoryId()+" ");
		}
		
		dataSql.append("GROUP BY req_user,req_name HAVING exc_times>0 ORDER BY exc_times DESC ");
		return dataSql;
	}
	
	/**
	 * 异常查询明细分页查询
	 */
	@SuppressWarnings("unchecked")
	public List<ServLogDTO> groupBy(PageVO<ServLog> pageVO, ServLog servLog,String slInfoCategoryName,BizErrorCodeEnum bizErrorCode){
		StringBuilder dataSql = getExcQuerySQL(pageVO,servLog,slInfoCategoryName,bizErrorCode);
		dataSql.append("LIMIT "+pageVO.getOffset()+","+pageVO.getLimit());
		
		List<Object[]> dataList = this.entityManager.createNativeQuery(dataSql.toString())
				.getResultList();
		List<ServLogDTO> servLogDTOList = new ArrayList<>();
		for (Object object : dataList) {
			Object[] a = (Object[])object;
			String a0;
			if(a[0] == null) {
				a0 = "未知";
			}else {
				a0 = a[0].toString();
			}
			String a1 = a[1].toString();
			String a2 = a[2].toString();
			String a3 = a[3].toString();
			servLogDTOList.add(new ServLogDTO(a0,a1,BigDecimal.valueOf(Double.valueOf(a2)),BigDecimal.valueOf(Double.valueOf(a3))));
		}
		return servLogDTOList;
	}
	/**
	 * 异常查询明细结果集合总长度
	 */
	@SuppressWarnings("unchecked")
	public Long getServLogDTOListCount(PageVO<ServLog> pageVO, ServLog servLog,String slInfoCategoryName,BizErrorCodeEnum bizErrorCode) {
		StringBuilder dataSql = getExcQuerySQL(pageVO, servLog, slInfoCategoryName, bizErrorCode);
		List<Object[]> dataList = this.entityManager.createNativeQuery(dataSql.toString())
				.getResultList();
		
		return (long) dataList.size();
	}
	
	@Override
	public Page findManagementQueryRecords(Pageable pageable,ServLog servLog) {
		//List<ServLog> servLogList=servLogRepository.findDetailInfoData(servLog.getBegin() ,servLog.getEnd(),servLog.getProviderId() ,servLog.getInfoCategoryId(),servLog.getRequestUser(),longNormal);
		if(servLog == null)
			return this.findAll(pageable);
		Specification<ServLog> spec = new Specification<ServLog>() {
			private static final long serialVersionUID = 1L;

			@Override
			public Predicate toPredicate(Root<ServLog> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
				List<Predicate> predicates = new ArrayList<>();
				
				if (null!=servLog.getBegin()) {
					Predicate p = criteriaBuilder.greaterThanOrEqualTo(root.get("requestTime"), servLog.getBegin());
					predicates.add(p);
				}
				if (null!=servLog.getEnd()) {
					Date nextDay=getNextDate(servLog.getEnd(),DateTypeEnum.DAY);
					Predicate p = criteriaBuilder.lessThan(root.get("requestTime"), nextDay);
					predicates.add(p);
				}
				if (null!=servLog.getProviderId()) {
					Predicate p = criteriaBuilder.equal(root.get("providerId"), servLog.getProviderId());
					predicates.add(p);
				}
				if (null!=servLog.getInfoCategoryId()) {
					Predicate p = criteriaBuilder.equal(root.get("infoCategoryId"), servLog.getInfoCategoryId());
					predicates.add(p);
				}
				if (null!=servLog.getRequestUser()) {
					Predicate p = criteriaBuilder.equal(root.get("requestUser"), servLog.getRequestUser());
					predicates.add(p);
				}
				if (null!=servLog.getNormal()) {
					Predicate p = criteriaBuilder.equal(root.get("normal"), servLog.getNormal());
					predicates.add(p);
				}
				return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]));
			}
		};
		Page<ServLog> pageServLog=servLogRepository.findAll(spec, pageable);
		
		List<ServLog> content = pageServLog.getContent();
		content.stream().forEach(elem -> {
			ProviderAndInfoCategoryNameDTO dto = null;

			List<ProviderAndInfoCategoryNameDTO> list = providerRepository
					.findProviderNameAndInfoCategoryNameById(elem.getProviderId(), elem.getInfoCategoryId());
			if (!list.isEmpty()) {
				dto = list.get(0);
			}

			if (null != dto) {
				elem.setProviderName(dto.getProviderName());
				elem.setInfoCategoryName(dto.getInfoCategoryName());
			}
		});
		
		return pageServLog;
	}
	
	/*@Override
	public List<ServLog> findQueryTotalData(Date Begin, Date End, String infoCategoryId) {
		return servLogRepository.findQueryTotalData(Begin, End, infoCategoryId);
	}*/

	@Override
	public List<ServLog> findAll(ServLog servLog) {
		return null;
	}

	@Override
	public List<ServLog> findDetailInfoData(Date begin, Date end, Long providerId, Long infoCategoryId,
			String requestUser, Long normal) {
		return servLogRepository.findDetailInfoData(begin, end, providerId, infoCategoryId, requestUser, normal);
	}
	
	public List<ServLogDTO> getImageServLogList(List<Object[]> list,List<ServLogDTO> servLogDTOList){
		for (Object object : list) {
			Object[] a = (Object[])object;
			String a0 = a[0].toString();
			String a1 = a[1].toString();
			servLogDTOList.add(new ServLogDTO(Long.valueOf(a0), a1));
		}
		return servLogDTOList;
	}
	
	public List<ServLogDTO> getTableServLogList(List<Object[]> list,List<ServLogDTO> servLogDTOList){
		for (Object object : list) {
			Object[] a = (Object[])object;
			String a0 = null;
			if(a[0] == null) {
				a0 = "未知";
			}else {
				a0 = a[0].toString();
			}
			String a1 = a[1].toString();
			String a2 = a[2].toString();
			servLogDTOList.add(new ServLogDTO(a0,Long.valueOf(a1),a2));
		}
		return servLogDTOList;
	}
	public List<ServLogDTO> getMthBillServLogList(List<Object[]> list,List<ServLogDTO> servLogDTOList){
		for (Object object : list) {
			Object[] a = (Object[])object;
			
			String a0 = null;
			if(a[0] == null) {
				a0 = "未知";
			}else {
				a0 = a[0].toString();
			}
			
			String a1 = null;
			if(a[1] == null) {
				a1 = "未知";
			}else {
				a1 = a[1].toString();
			}
			
			String a2 = null;
			if(a[2] == null) {
				a2 = "未知";
			}else {
				a2 = a[2].toString();
			}
			
			String a3 = a[3].toString();
			String a4 = a[4].toString();
			
			String a5 = null;
			if(a[5] == null) {
				a5 = "0";
			}else {
				a5 = a[5].toString();
			}
			
			String a6 = null;
			if(a[6] == null) {
				a6 = "0";
			}else {
				a6 = a[6].toString();
			}
			servLogDTOList.add(new ServLogDTO(a0, a1, a2, Long.valueOf(a3), Long.valueOf(a4), Float.valueOf(a5), BigDecimal.valueOf(Double.valueOf(a6))));
		}
		return servLogDTOList;
	}
	
	public List<ServLogDTO> getYearBillServLogList(List<Object[]> list,List<ServLogDTO> servLogDTOList){
		for (Object object : list) {
			Object[] a = (Object[])object;
			String a0 = null;
			if(a[0] == null) {
				a0 = "未知";
			}else {
				a0 = a[0].toString();
			}
			String a1 = a[1].toString();
			String a2 = a[2].toString();
			String a3 = a[3].toString();
			String a4 = null;
			if(a[4] == null) {
				a4 = "0";
			}else {
				a4 = a[4].toString();
			}
			servLogDTOList.add(new ServLogDTO(a0,a1,Long.valueOf(a2),Long.valueOf(a3),BigDecimal.valueOf(Double.valueOf(a4))));
		}
		return servLogDTOList;
	}
	
	@Override
	public List<ServLogDTO> findInDayTotalData(String infoCategoryName,String reqOrg,String dataType){
		List<Object[]> list = servLogRepository.findInDayTotalData(infoCategoryName, reqOrg, dataType);
		List<ServLogDTO> servLogDTOList = new ArrayList<>();
		getImageServLogList(list,servLogDTOList);
		return servLogDTOList;
	}
	
	@Override
	public List<ServLogDTO> findInDayNumberData(String infoCategoryName, String reqOrg, 
			String dataType, String success) {
		List<Object[]> list = servLogRepository.findInDayNumberData(infoCategoryName, reqOrg, dataType, success);
		List<ServLogDTO> servLogDTOList = new ArrayList<>();
		getImageServLogList(list,servLogDTOList);
		return servLogDTOList;
		
	}
	
	public StringBuilder getInMthOrYearSQL(Date begin, Date end, String infoCategoryName, 
			OperateTypeEnum dataType,YesOrNo success,String accuracy) {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		StringBuilder dataSql = new StringBuilder("SELECT o.name AS req_name,COUNT(*) AS req_times,");
		if("mth".equals(accuracy)) {
			dataSql.append(" DATE_FORMAT(s.request_time,'%Y%m%d') AS req_time ");
		}else if("year".equals(accuracy)) {
			dataSql.append(" DATE_FORMAT(s.request_time,'%Y%m') AS req_time ");
		}
		dataSql.append(" FROM serv_log s "
				+ " LEFT JOIN info_category io ON s.info_category_id = io.id "
				+ " LEFT JOIN organ o ON o.no = s.request_org "
				+ " WHERE s.request_time >= '"+sdf.format(begin)+"' AND s.request_time< '"+sdf.format(end)+"' "
				+ " AND io.name = '"+infoCategoryName+"' "); 
				if(dataType != null) {
					dataSql.append(" AND s.data_type = "+dataType.getValue()+" ");
				}
				if(success != null) {
					dataSql.append(" AND s.success = "+success.getValue()+" ");
				}
				dataSql.append(" GROUP BY req_time,req_name");
		return dataSql;
	}
	
	@Override
	public List<ServLogDTO> findInMthNumberData(Date begin, Date end, String infoCategoryName, 
			OperateTypeEnum dataType,YesOrNo success) {
		String accuracy = "mth";
		StringBuilder dataSql = getInMthOrYearSQL(begin, end, infoCategoryName, dataType, success,accuracy);
		@SuppressWarnings("unchecked")
		List<Object[]> list = this.entityManager.createNativeQuery(dataSql.toString())
				.getResultList();		
		List<ServLogDTO> servLogDTOList = new ArrayList<>();
		getTableServLogList(list, servLogDTOList);
		return servLogDTOList;
	}

	@Override
	public List<ServLogDTO> findInYearNumberData(Date begin, Date end, String infoCategoryName, OperateTypeEnum dataType,YesOrNo success) {
		String accuracy = "year";
		StringBuilder dataSql = getInMthOrYearSQL(begin, end, infoCategoryName, dataType, success,accuracy);
		@SuppressWarnings("unchecked")
		List<Object[]> list = this.entityManager.createNativeQuery(dataSql.toString())
				.getResultList();
		List<ServLogDTO> servLogDTOList = new ArrayList<>();
		getTableServLogList(list, servLogDTOList);
		return servLogDTOList;
	}

	@Override
	public List<ServLogDTO> findOutDayTotalData(String infoCategoryName, String provider) {
		List<Object[]> list = servLogRepository.findOutDayTotalData(infoCategoryName, provider);
		List<ServLogDTO> servLogDTOList = new ArrayList<>();
		getImageServLogList(list,servLogDTOList);
		return servLogDTOList;
	}

	@Override
	public List<ServLogDTO> findOutDayNumberData(String infoCategoryName, String provider, String success) {
		List<Object[]> list = servLogRepository.findOutDayNumberData(infoCategoryName, provider, success);
		List<ServLogDTO> servLogDTOList = new ArrayList<>();
		getImageServLogList(list,servLogDTOList);
		return servLogDTOList;
	}

	@Override
	public List<ServLogDTO> findOutMthTotalData(Date begin, Date end, String infoCategoryName) {
		List<Object[]> list = servLogRepository.findOutMthTotalData(begin, end, infoCategoryName);
		List<ServLogDTO> servLogDTOList = new ArrayList<>();
		getTableServLogList(list, servLogDTOList);
		return servLogDTOList;
	}

	@Override
	public List<ServLogDTO> findOutMthNumberData(Date begin, Date end, String infoCategoryName, String success) {
		List<Object[]> list = servLogRepository.findOutMthNumberData(begin, end, infoCategoryName, success);
		List<ServLogDTO> servLogDTOList = new ArrayList<>();
		getTableServLogList(list, servLogDTOList);
		return servLogDTOList;
	}

	@Override
	public List<ServLogDTO> findOutYearTotalData(Date begin, Date end, String infoCategoryName) {
		List<Object[]> list = servLogRepository.findOutYearTotalData(begin, end, infoCategoryName);
		List<ServLogDTO> servLogDTOList = new ArrayList<>();
		getTableServLogList(list, servLogDTOList);
		return servLogDTOList;
	}

	@Override
	public List<ServLogDTO> findOutYearNumberData(Date begin, Date end, String infoCategoryName, String success) {
		List<Object[]> list = servLogRepository.findOutYearNumberData(begin, end, infoCategoryName, success);
		List<ServLogDTO> servLogDTOList = new ArrayList<>();
		getTableServLogList(list, servLogDTOList);
		return servLogDTOList;
	}

	@Override
	public List<ServLogDTO> findInMthBillData(Date begin, Date end, String reqOrg, String infoCategoryName) {
		List<Object[]> list = servLogRepository.findInMthBillData(begin, end, reqOrg, infoCategoryName);
		List<ServLogDTO> servLogDTOList = new ArrayList<>();
		getMthBillServLogList(list, servLogDTOList);
		return servLogDTOList;
	}

	@Override
	public List<ServLogDTO> findInYearBillData(Date begin, Date end, String reqOrg, String infoCategoryName) {
		List<Object[]> list = servLogRepository.findInYearBillData(begin, end, reqOrg, infoCategoryName);
		List<ServLogDTO> servLogDTOList = new ArrayList<>();
		getYearBillServLogList(list, servLogDTOList);
		return servLogDTOList;
	}

	@Override
	public List<ServLogDTO> findOutMthBillData(Date begin, Date end) {
		List<Object[]> list = servLogRepository.findOutMthBillData(begin, end);
		List<ServLogDTO> servLogDTOList = new ArrayList<>();
		getMthBillServLogList(list, servLogDTOList);
		return servLogDTOList;
	}

	@Override
	public List<ServLogDTO> findOutYearBillData(Date begin, Date end, String reqName, String infoCategoryName) {
		List<Object[]> list = servLogRepository.findOutYearBillData(begin, end, reqName, infoCategoryName);
		List<ServLogDTO> servLogDTOList = new ArrayList<>();
		getYearBillServLogList(list, servLogDTOList);
		return servLogDTOList;
	}

	@Override
	public List<ServLogDTO> findOutMthBillDetailsData(Date begin, Date end, String reqName, String infoCategoryName) {
		List<Object[]> list = servLogRepository.findOutMthBillDetailsData(begin, end, reqName, infoCategoryName);
		List<ServLogDTO> servLogDTOList = new ArrayList<>();
		getMthBillServLogList(list, servLogDTOList);
		return servLogDTOList;
	}
	@Override
	public List<ServLogDTO> findQueryTotalData(Date Begin, Date End, String infoCategoryId) {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		StringBuilder dataSql = new StringBuilder("SELECT COUNT(s.request_user) AS total, s.request_user AS req_user ,o.name AS req_name "
				+ " FROM serv_log s LEFT JOIN organ o ON o.no = s.request_org "
				+ " WHERE request_time>= '"+sdf.format(Begin)+"' AND request_time< '"+sdf.format(End)+"' ");
		if(infoCategoryId != null) {
			dataSql.append(" AND info_category_id = "+infoCategoryId);
		}
		dataSql.append(" GROUP BY req_user,req_name ORDER BY total DESC LIMIT 0, 20");
		@SuppressWarnings("unchecked")
		List<Object[]> list = this.entityManager.createNativeQuery(dataSql.toString())
				.getResultList();
		List<ServLogDTO> servLogDTOList = new ArrayList<>();
		for(Object object:list) {
			Object[] a = (Object[])object;
			String a0 = a[0].toString();
			String a1 = a[1].toString();
			String a2 = null;
			if(a[2] == null) {
				a2 = "未知";
			}else {
				a2 = a[2].toString();
			}
			servLogDTOList.add(new ServLogDTO(BigDecimal.valueOf(Double.valueOf(a0)),a1,a2));
		}
		return servLogDTOList;
	}

	/**
	 * 将当前Date类型的数据添加一天单位：天、月、年
	 * @param source 源时间
	 * @param dateType 添加的时间单位
	 * @return	返回添加后的时间
	 */
	private Date getNextDate(Date source,DateTypeEnum dateType) {
		Calendar calendar=Calendar.getInstance();
		calendar.setTime(source);
		if(dateType==DateTypeEnum.YEAR) {
			calendar.add(Calendar.YEAR, 1); 
		}
		if(dateType==DateTypeEnum.MONTH) {
			calendar.add(Calendar.MONTH, 1); 
		}
		if(dateType==DateTypeEnum.DAY) {
			calendar.add(Calendar.DAY_OF_MONTH, 1);  
		}
		return calendar.getTime();
	}
}