1 @Override 2 public Map<String,List<Map.Entry<String,RmpPassengerDto>>> getPassengerByDateAndPlace(RmpPassengerReportConditionDto conditionDto) { 3 LogUtils.logInfo("进入客流报表服务层!"); 4 String regionId = conditionDto.getRegion_id(); 5 LogUtils.logInfo("区域ID"+regionId); 6 Integer regionType = conditionDto.getRegion_type(); 7 LogUtils.logInfo("区域类型"+regionType); 8 String startDate = conditionDto.getStartDate(); 9 LogUtils.logInfo("开始时间"+startDate); 10 String endDate = conditionDto.getEndDate(); 11 LogUtils.logInfo("结束时间"+endDate); 12 String dateType = conditionDto.getDateType(); 13 //江铃门店类型 14 Integer storeType = conditionDto.getStoreType(); 15 //获得图表显示的日期 16 List<String> timeXList; 17 18 19 //对日期进行处理 年 20XX-01-01 00:00:00~20XX-12-31 23:59:59 20 //月 20XX-XX-01 21 LogUtils.logInfo("开始处理时间信息。"); 22 Map<String,Date> startAndTime = vssCarRecordService.valiteDate(startDate,endDate,dateType); 23 LogUtils.logInfo("时间信息处理完成。"); 24 Date startTime = startAndTime.get("startTime"); 25 Date endTime = startAndTime.get("endTime"); 26 LogUtils.logInfo("开始时间:"+startTime); 27 LogUtils.logInfo("结束时间:"+endTime); 28 Collection<Date> dateList = getDateList(startTime,endTime,dateType); 29 timeXList = convertDateToStringSimple(dateList, true,dateType); 30 31 Map<String,RmpPassengerDto> resultMap = new TreeMap<>(new Comparator<String>() { 32 @Override 33 public int compare(String o1, String o2) { 34 return o1.compareTo(o2); 35 } 36 }); 37 for (String timeX: timeXList 38 ) { 39 resultMap.put(timeX,null); 40 } 41 42 Timestamp startTimes = new Timestamp(startTime.getTime()); 43 Timestamp endTimes = new Timestamp(endTime.getTime()); 44 45 List<Integer> regionIds = new ArrayList<>(); 46 if(regionId!=null && regionType != null) { 47 //通过region中path查询出所有的子门店 48 LogUtils.logInfo("查询区域信息。"); 49 regionIds = rmpPassengerUtilDao.getRegionIDsByParentNodes(regionId,-1); 50 } 51 Map<String,List<Map.Entry<String,RmpPassengerDto>>> brandDateMap = rmpPassengerDao.queryDataByDateAndPlaceDevideBrand(startTimes, endTimes, dateType, regionIds,resultMap,storeType); 52 return brandDateMap; 53 }
/*** 给定日期类型(年月周天)和日期计算出开始和结束的时间** @param startDate* @param endDate* @param dateType* @return*/public Map<String, Date> valiteDate(String startDate, String endDate, String dateType) {Calendar c = Calendar.getInstance();SimpleDateFormat sdf;Date startTime = new Date();Date endTime = new Date();int year;int month;switch (dateType) {case RmpReportConstant.LEVEL_YEAR:sdf = new SimpleDateFormat("yyyy");try {startTime = sdf.parse(startDate);c.setTime(startTime);year = c.get(Calendar.YEAR);c.set(year, 0, 01, 00, 00, 00);startTime = c.getTime();c.set(year, 11, 31, 23, 59, 59);endTime = c.getTime();} catch (ParseException e) {e.printStackTrace();}break;case RmpReportConstant.LEVEL_MONTH:sdf = new SimpleDateFormat("yyyy-MM");try {startTime = sdf.parse(startDate);c.setTime(startTime);year = c.get(Calendar.YEAR);month = c.get(Calendar.MONTH);int dayMonthEnd = c.getActualMaximum(Calendar.DAY_OF_MONTH);c.set(year, month, 01, 00, 00, 00);startTime = c.getTime();c.set(year, month, dayMonthEnd, 23, 59, 59);endTime = c.getTime();} catch (ParseException e) {e.printStackTrace();}break;case RmpReportConstant.LEVEL_WEEK:String startDates = RmpDateTimeUtil.getFirstDayOfWeek(startDate);String endDates = RmpDateTimeUtil.getLastDayOfWeek(startDate);sdf = new SimpleDateFormat("yyyy-MM-dd");try {startTime = sdf.parse(startDates);endTime = sdf.parse(endDates);} catch (ParseException e) {e.printStackTrace();}break;case RmpReportConstant.LEVEL_DAY:sdf = new SimpleDateFormat("yyyy-MM-dd");try {Date dayDate = sdf.parse(startDate);c.setTime(dayDate);c.set(Calendar.HOUR, 0);c.set(Calendar.MINUTE, 0);c.set(Calendar.SECOND, 0);c.set(Calendar.MILLISECOND, 0);startTime = c.getTime();c.set(Calendar.HOUR, 23);c.set(Calendar.MINUTE, 59);c.set(Calendar.SECOND, 59);c.set(Calendar.MILLISECOND, 999);endTime = c.getTime();} catch (ParseException e) {e.printStackTrace();}break;case RmpReportConstant.LEVEL_CUSTOM:sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");try {startTime = sdf.parse(startDate + " 00:00:00");endTime = sdf.parse(endDate + " 23:59:59");} catch (ParseException e) {e.printStackTrace();}break;default:break;}Map<String, Date> resultMap = new TreeMap<>();resultMap.put("startTime", startTime);resultMap.put("endTime", endTime);return resultMap;}
/*** 获取当前时间所属周的第一天* @author yuchuanjun 2016年12月14日 下午7:41:17* @param date* @return*/public static String getFirstDayOfWeek(String date) {try{SimpleDateFormat format = new SimpleDateFormat(DATA_FORMAT_yyyy_MM_dd);Calendar cal = Calendar.getInstance();cal.setTime(format.parse(date));int day = cal.get(Calendar.DAY_OF_WEEK);// 当周第一天是星期天 Calendar周日是每周的第一天if (day == 1) {day = 7;} else {day--;}// 周一cal.add(Calendar.DAY_OF_MONTH, -(day - 1));return format.format(cal.getTime());}catch(Exception e){e.printStackTrace();}return null;}/*** 获取当前时间所属周的最后一天* @author yuchuanjun 2016年12月14日 下午7:45:34* @param date* @return*/public static String getLastDayOfWeek(String date) {try{SimpleDateFormat format = new SimpleDateFormat(DATA_FORMAT_yyyy_MM_dd);Calendar cal = Calendar.getInstance();cal.setTime(format.parse(date));int day = cal.get(Calendar.DAY_OF_WEEK);// 当周第一天是星期天 Calendar周日是每周的第一天if (day == 1) {day = 7;} else {day--;}// 周日cal.add(Calendar.DAY_OF_MONTH, 7 - day);return format.format(cal.getTime());}catch(Exception e){e.printStackTrace();}return null;}
protected Collection<Date> getDateList( Date startDate,Date endDate,String dateType) {Collection<Date> dateSet = new ArrayList<Date>();if(endDate!=null){Calendar calendar = Calendar.getInstance();while(startDate.compareTo(endDate) <= 0) {calendar.setTime(startDate);dateSet.add(startDate);if("day".equals(dateType)){addCalendarDay(calendar);}else if("year".equals(dateType)){addCalendarYear(calendar);}else {addCalendar(calendar);}startDate = calendar.getTime();}}else{dateSet.add(startDate);}return dateSet;}
protected List<String> convertDateToStringSimple(Collection<Date> dateList, boolean isConvertToday,String dateType) {List<String> dateStrList = new ArrayList<String>();if(!RmpUtils.isEmpty(dateList)){for(Date date : dateList){if("day".equals(dateType)){dateStrList.add(convertDateToStringSimpleDay(date, isConvertToday));}else if("year".equals(dateType)){dateStrList.add(convertDateToStringSimpleYear(date, isConvertToday));}else{dateStrList.add(convertDateToStringSimple(date, isConvertToday));}}}return dateStrList;}protected String convertDateToStringSimple(Date date, boolean isConvertToday) {return RmpDateTimeUtil.dateToString(date, RmpDateTimeUtil.DATA_FORMAT_yyyy_MM_dd);}protected String convertDateToStringSimpleDay(Date date, boolean isConvertToday) {return RmpDateTimeUtil.dateToString(date, RmpDateTimeUtil.DATE_FORMAT_HH);}protected String convertDateToStringSimpleYear(Date date, boolean isConvertToday) {return RmpDateTimeUtil.dateToString(date, RmpDateTimeUtil.DATE_FORMAT_yyyy_MM);}
SELECTstore.brand_name,z. DATE,SUM (z.in_count) passenger_in_count,SUM (z.out_count) passenger_out_count FROM(SELECTx.region_id,x. DATE,SUM (x. IN) AS in_count,SUM (x. OUT) AS out_countFROM(SELECTto_char(v. TIME, 'yyyy-MM') DATE,v.region_id,SUM (v.passenger_in_count) AS IN,SUM (v.passenger_out_count) AS OUTFROMvss_passenger_hour_view vLEFT JOIN s_region s ON v.region_id = s.region_idWHEREs.region_level = 0AND v.datetime BETWEEN ?AND ?AND v.region_id IN (6, 7, 4)GROUP BYv. TIME,v.region_id) xGROUP BYx.region_id,DATE) z LEFT JOIN (SELECTrs.unit_id,rb.brand_nameFROMrmp_brand rbRIGHT JOIN rmp_store rs ON rb. ID = rs.brand_id ) store ON z.region_id = store.unit_id GROUP BYstore.brand_name,z. DATE
switch (timeType) {
case RmpReportConstant.LEVEL_YEAR:
queryDateType = "yyyy-MM";
break;
case RmpReportConstant.LEVEL_MONTH:
queryDateType = "yyyy-MM-dd";
break;
case RmpReportConstant.LEVEL_WEEK:
queryDateType = "yyyy-MM-dd";
break;
case RmpReportConstant.LEVEL_CUSTOM:
queryDateType = "yyyy-MM-dd";
break;
case RmpReportConstant.LEVEL_DAY:
queryDateType = "hh24";
break;
default:
break;
}