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;

}

分享到：

2012-04-12 11:28

瀏覽 7157

分類:數據庫

評論

1 樓

123qqwe

2016-06-22

你確定,能被看懂!!!!

總結

以上是生活随笔為你收集整理的mysql周报内容范文_Mysql各种报表查询含实例，日报，周报，月报，时间差自动计算...的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。