public List retrieve(IReport report) {
List list = new ArrayList();
Map map = new HashMap();
//
if (null != report.getEndTime()) {
map.put(ReportMeta.PROP_ENDTIME, report.getEndTime());
//
if (null != report.getName()) {
map.put(ReportMeta.PROP_NAME, report.getName());
// （ ）
if (null != report.getQid() && report.getQid() > 0) {
map.put(ReportMeta.PROP_QID, report.getQid());
}
}
// ， ， ：1L，
if (null == report.getQid()) {
report.setQid(0L);
}
}
String sql = "";
//
String tendency = "' ',' ',' - ',' - ',' - '";
String hourarea = " " + dayhour + ":00:00";
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd" + hourarea);
if (map.size() > 0) {
//
if ("daily".equals(report.getType())) {
sql = "select name,quantity,start_time startTime,end_time endTime from tbl_report "
+ "where end_time between date_add(:endTime,interval :dayHour hour_second) and :endTime";
//
} else if ("daily_tendency".equals(report.getType())) {
sql = "select name,quantity,start_time startTime,end_time endTime from tbl_report "
+ "where end_time between date_add(:endTime,interval :dayHour hour_second) "
+ "and :endTime and name in (:tendency)";
//
} else if ("daily_question".equals(report.getType())) {
sql = "select name,quantity,start_time startTime,end_time endTime from tbl_report "
+ "where end_time between date_add(:endTime,interval :dayHour hour_second) and :endTime "
+ "and name in (select content from tbl_keyword where type = ' ' and "
+ "state = 'normal') order by quantity desc";
//
} else if ("daily_question_child".equals(report.getType())) {
sql = "select name,quantity,start_time startTime,end_time endTime,qid,description,description2 from tbl_report "
+ "where end_time between date_add(:endTime,interval :dayHour hour_second) and :endTime "
+ "and name in (select content from tbl_keyword where type = ' ' and "
+ "state = 'normal') ";
// ( ： 15:00~ 15:00)
} else if ("daily_monday".equals(report.getType())) {
sql = "select name,sum(quantity) quantity from tbl_report "
+ "where end_time between ADDDATE(:endTime,-WEEKDAY(:endTime)-2) "
+ "and ADDDATE(:endTime,-WEEKDAY(:endTime)) group by name";
// ( ： 15:00~ 15:00)
} else if ("daily_tendency_monday".equals(report.getType())) {
sql = "select name,sum(quantity) quantity from tbl_report "
+ "where end_time between ADDDATE(:endTime,-WEEKDAY(:endTime)-2) "
+ "and ADDDATE(:endTime,-WEEKDAY(:endTime)) and name in (:tendency) group by name";
// ( ： 15:00~ 15:00)
} else if ("daily_question_monday".equals(report.getType())) {
sql = "select name,sum(quantity) quantity from tbl_report "
+ "where end_time between ADDDATE(:endTime,-WEEKDAY(:endTime)-2) "
+ "and ADDDATE(:endTime,-WEEKDAY(:endTime)) "
+ "and name in (select content from tbl_keyword where type = ' ' and "
+ "state = 'normal') group by name order by quantity desc";
// ( ： 15:00~ 15:00)
} else if ("daily_question_child_monday".equals(report.getType())) {
sql = "select name,sum(quantity) quantity,start_time startTime,end_time endTime,qid,description,description2 from tbl_report "
+ "where end_time between ADDDATE(:endTime,-WEEKDAY(:endTime)-2) "
+ "and ADDDATE(:endTime,-WEEKDAY(:endTime)) "
+ "and name in (select content from tbl_keyword where type = ' ' and "
+ "state = 'normal') group by name";
//
} else if ("weekly_day".equals(report.getType())) {
sql = "select name,quantity,start_time startTime,end_time endTime from tbl_report "
+ "where end_time between "
+ "(select date_sub(:endTime, interval weekday(:endTime) +:plusDay day)) "
+ "and (select date_sub(:endTime,interval weekday(:endTime) +:reduceDay day)) "
+ "order by end_time";
//
} else if ("weekly_count".equals(report.getType())) {
sql = "select name,sum(quantity) quantity,start_time startTime,end_time endTime from tbl_report where end_time between "
+ "(select date_sub(:endTime,interval weekday(:endTime) +:plusDay day)) and "
+ "(select date_sub(:endTime,interval weekday(:endTime) +:reduceDay day)) group by name";
//
} else if ("weekly_count_tendency".equals(report.getType())) {
sql = "select name,sum(quantity) quantity from tbl_report where end_time between "
+ "(select date_sub(:endTime,interval weekday(:endTime) +:plusDay day)) and "
+ "(select date_sub(:endTime,interval weekday(:endTime) +:reduceDay day)) and "
+ "name in (:tendency) group by name";
// (report.name=" ")
} else if ("weekly_count_valid".equals(report.getType())) {
// sql =
// "select ' ' name,sum(quantity) quantity,end_time endTime from tbl_report "
// +
// "where end_time between (select date_sub(:endTime,interval weekday(:endTime) +:plusDay day)) "
// +
// "and (select date_sub(:endTime,interval weekday(:endTime) +:reduceDay day)) "
// +
// "and name in (:tendency) group by end_time order by end_time";
sql = "select name,quantity,start_time startTime,end_time endTime from tbl_report where end_time between "
+ "(select date_sub(:endTime,interval weekday(:endTime) +:plusDay day)) "
+ "and (select date_sub(:endTime,interval weekday(:endTime) +:reduceDay day)) "
+ "and name = :name group by endTime";
//
} else if ("weekly_count_question".equals(report.getType())) {
sql = "select name,sum(quantity) quantity,start_time startTime,end_time endTime from tbl_report where end_time between "
+ "(select date_sub(:endTime,interval weekday(:endTime) +:plusDay day)) and "
+ "(select date_sub(:endTime,interval weekday(:endTime) +:reduceDay day)) and "
+ "name in (select content from tbl_keyword where type = ' ' and "
+ "state = 'normal') group by name";
//
} else if ("weekly_count_question_child".equals(report.getType())) {
sql = "select name,sum(quantity) quantity,qid,description,description2 from tbl_report where end_time between "
+ "(select date_sub(:endTime,interval weekday(:endTime) +:plusDay day)) and "
+ "(select date_sub(:endTime,interval weekday(:endTime) +:reduceDay day)) and "
+ "name in (select content from tbl_keyword where type = ' ' and "
+ "state = 'normal') group by name";
//
} else if ("monthly_count".equals(report.getType())) {
sql = "select name,sum(quantity) quantity from tbl_report where end_time between "
+ "(select date_add(date_add(last_day(:endTime),interval -1 month),"
+ "interval +15 hour)) and (select date_add(last_day(:endTime),"
+ "interval +15 hour)) group by name";
//
} else if ("monthly_count_tendency".equals(report.getType())) {
sql = "select name,sum(quantity) quantity from tbl_report where end_time between "
+ "(select date_add(date_add(last_day(:endTime),interval -1 month),"
+ "interval +15 hour)) and (select date_add(last_day(:endTime),"
+ "interval +15 hour)) and name in (:tendency) group by name";
//
} else if ("monthly_count_question".equals(report.getType())) {
sql = "select name,sum(quantity) quantity from tbl_report where end_time between "
+ "(select date_add(date_add(last_day(:endTime),interval -1 month),"
+ "interval +15 hour)) and (select date_add(last_day(:endTime),"
+ "interval +15 hour)) and name in (select content from tbl_keyword where type = ' ' and "
+ "state = 'normal') group by name";
//
} else if ("monthly_count_question_child".equals(report.getType())) {
sql = "select name,sum(quantity) quantity,qid,description,description2,description3 from tbl_report where end_time between "
+ "(select date_add(date_add(last_day(:endTime),interval -1 month),"
+ "interval +15 hour)) and (select date_add(last_day(:endTime),"
+ "interval +15 hour)) and name in (select content from tbl_keyword where type = ' ' and "
+ "state = 'normal') group by name";
} else if ("monthly_count_valid".equals(report.getType())) {
sql = "select name,sum(quantity) quantity,start_time startTime,end_time endTime from tbl_report where end_time between "
+ "(select date_add(date_add(last_day(:endTime),interval -1 month),"
+ "interval +15 hour)) and (select date_add(last_day(:endTime),"
+ "interval +15 hour)) "
+ "and name = :name";
//
} else if ("duration_count_tendency".equals(report.getType())) {
sql = "select name,sum(quantity) quantity from tbl_report where end_time between "
+ ":startTime and :endTime and name in (:tendency) group by name";
//
} else if ("duration_count_question".equals(report.getType())) {
sql = "select name,sum(quantity) quantity,start_time startTime,end_time endTime from tbl_report where end_time between "
+ ":startTime and :endTime and "
+ "name in (select content from tbl_keyword where type = ' ' and "
+ "state = 'normal') group by name";
//
} else if ("duration_count_question_child".equals(report.getType())) {
sql = "select name,sum(quantity) quantity,qid,description,description2 from tbl_report where end_time between "
+ ":startTime and :endTime and "
+ "name in (select content from tbl_keyword where type = ' ' and "
+ "state = 'normal') group by name";
//
} else if ("is_exists".equals(report.getType())) {
sql = "select name from tbl_report where end_time = :endTime";
}
sql = sql.replace(
":startTime",
"'"
+ (null != report.getStartTime() ? sdf
.format(report.getStartTime()) : "") + "'")
.replace(":endTime",
"'" + sdf.format(report.getEndTime()) + "'")
.replace(":dayHour", "'-23:59:59'").replace(":plusDay",
2 + (7 * report.getQid()) + "").replace(
":reduceDay", -4 + (7 * report.getQid()) + "")
.replace(":tendency", tendency).replace(
":name",
"'"
+ (null != report.getName() ? report
.getName() : "") + "'");
list = reportDao
.listQuery(sql, map, QueryStrType.SQL, Report.class);
}
return list;
}