- public TodoDataDto getTodoData() throws Exception {
- TodoDataDto todoDataDto = new TodoDataDto();
- try (MYSQL mysql = new MYSQL("speedrent")) {
- SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
- Calendar start = Calendar.getInstance();
- start.set(Calendar.HOUR_OF_DAY, 0);
- start.set(Calendar.MINUTE, 0);
- start.set(Calendar.SECOND, 0);
- String date = df.format(start.getTime());
- int totalToday = 0;
- String sql = "SELECT COUNT(*) FROM callcenter_sales WHERE date_created >= '" + date + "'";
- ResultSet rs = mysql.query(sql);
- while (rs.next()) {
- totalToday = rs.getInt(1);
- }
- int ptotalToday = 0;
- sql = "SELECT COUNT(*) FROM property WHERE date_created >= '" + date + "' AND created_by LIKE '%SYSTEM%'";
- rs = mysql.query(sql);
- while (rs.next()) {
- ptotalToday = rs.getInt(1);
- }
- start.add(Calendar.DAY_OF_YEAR, -1);
- Calendar end = Calendar.getInstance();
- end.add(Calendar.DAY_OF_YEAR, -1);
- String startDate = df.format(start.getTime());
- String endDate = df.format(end.getTime());
- int totalYesterday = 0;
- sql = "SELECT COUNT(*) FROM callcenter_sales WHERE date_created >= '" + startDate + "' AND date_created <= '" + endDate + "'";
- rs = mysql.query(sql);
- while (rs.next()) {
- totalYesterday = rs.getInt(1);
- }
- int ptotalYesterday = 0;
- sql = "SELECT COUNT(*) FROM property WHERE date_created >= '" + startDate + "' AND date_created <= '" + endDate + "' AND created_by LIKE '%SYSTEM%'";
- rs = mysql.query(sql);
- while (rs.next()) {
- ptotalYesterday = rs.getInt(1);
- }
- start = Calendar.getInstance();
- start.set(Calendar.HOUR_OF_DAY, 0);
- start.set(Calendar.MINUTE, 0);
- start.set(Calendar.SECOND, 0);
- start.add(Calendar.DAY_OF_YEAR, -6);
- startDate = df.format(start.getTime());
- int totalLastWeek = 0;
- sql = "SELECT COUNT(*) FROM callcenter_sales WHERE date_created >= '" + startDate + "'";
- rs = mysql.query(sql);
- while (rs.next()) {
- totalLastWeek = rs.getInt(1);
- }
- int ptotalLastWeek = 0;
- sql = "SELECT COUNT(*) FROM property WHERE date_created >= '" + startDate + "' AND created_by LIKE '%SYSTEM%'";
- rs = mysql.query(sql);
- while (rs.next()) {
- ptotalLastWeek = rs.getInt(1);
- }
- start = Calendar.getInstance();
- start.set(Calendar.HOUR_OF_DAY, 0);
- start.set(Calendar.MINUTE, 0);
- start.set(Calendar.SECOND, 0);
- start.set(Calendar.DAY_OF_MONTH, 1);
- int day = Calendar
- .getInstance()
- .get(Calendar.DAY_OF_MONTH) + 1;
- startDate = df.format(start.getTime());
- int totalLastMonth = 0;
- sql = "SELECT COUNT(*) FROM callcenter_sales WHERE date_created >= '" + startDate + "'";
- rs = mysql.query(sql);
- while (rs.next()) {
- totalLastMonth = rs.getInt(1);
- }
- int ptotalLastMonth = 0;
- sql = "SELECT COUNT(*) FROM property WHERE date_created >= '" + startDate + "' AND created_by LIKE '%SYSTEM%'";
- rs = mysql.query(sql);
- while (rs.next()) {
- ptotalLastMonth = rs.getInt(1);
- }
- Calendar c = Calendar.getInstance();
- int hour = c.get(Calendar.HOUR_OF_DAY) + 1;
- DecimalFormat decimalFormat = new DecimalFormat("#.00");
- SimpleDateFormat format = new SimpleDateFormat("dd MMM hh:mmaa");
- String data = "";
- for (int i = 0; i < 30; i++) {
- Calendar cal = Calendar.getInstance();
- cal.set(Calendar.HOUR_OF_DAY, 0);
- cal.set(Calendar.MINUTE, 0);
- cal.set(Calendar.SECOND, 0);
- cal.add(Calendar.DAY_OF_YEAR, -1 * i);
- long time = cal
- .getTime()
- .getTime();
- String sd = df.format(cal.getTime());
- cal.set(Calendar.HOUR_OF_DAY, 0);
- cal.set(Calendar.MINUTE, 0);
- cal.set(Calendar.SECOND, 0);
- cal.add(Calendar.DAY_OF_YEAR, 1);
- String ed = df.format(cal.getTime());
- sql = "SELECT COUNT(*) FROM callcenter_sales WHERE date_created >= '" + sd + "' AND date_created < '" + ed + "'";
- rs = mysql.query(sql);
- while (rs.next()) {
- int count = rs.getInt(1);
- data += "cr.push([" + time + ", " + count + "]);n";
- }
- }
- for (int i = 0; i < 30; i++) {
- Calendar cal = Calendar.getInstance();
- cal.set(Calendar.HOUR_OF_DAY, 0);
- cal.set(Calendar.MINUTE, 0);
- cal.set(Calendar.SECOND, 0);
- cal.add(Calendar.DAY_OF_YEAR, -1 * i);
- long time = cal
- .getTime()
- .getTime();
- String sd = df.format(cal.getTime());
- cal.set(Calendar.HOUR_OF_DAY, 0);
- cal.set(Calendar.MINUTE, 0);
- cal.set(Calendar.SECOND, 0);
- cal.add(Calendar.DAY_OF_YEAR, 1);
- String ed = df.format(cal.getTime());
- sql = "SELECT COUNT(*) FROM property WHERE date_created >= '" + sd + "' AND date_created < '" + ed + "' AND created_by LIKE '%SYSTEM%'";
- rs = mysql.query(sql);
- while (rs.next()) {
- int count = rs.getInt(1);
- data += "cl.push([" + time + ", " + count + "]);n";
- }
- }
- todoDataDto.lastUpdate = format.format(new Date());
- todoDataDto.countToday = totalToday + "";
- todoDataDto.avgToday = (decimalFormat.format((double) totalToday / (double) hour)) + "";
- todoDataDto.countYesterday = totalYesterday + "";
- todoDataDto.avgYesterday = (decimalFormat.format((double) totalYesterday / (double) hour)) + "";
- todoDataDto.countLastWeek = totalLastWeek + "";
- todoDataDto.avgLastWeek = (decimalFormat.format((double) totalLastWeek / 7.0)) + "";
- todoDataDto.countLastMonth = totalLastMonth + "";
- todoDataDto.avgLastMonth = (decimalFormat.format((double) totalLastMonth / (double) day)) + "";
- todoDataDto.pcountToday = ptotalToday + "";
- todoDataDto.pavgToday = (decimalFormat.format((double) ptotalToday / (double) hour)) + "";
- todoDataDto.pcountYesterday = ptotalYesterday + "";
- todoDataDto.pavgYesterday = (decimalFormat.format((double) ptotalYesterday / (double) hour)) + "";
- todoDataDto.pcountLastWeek = ptotalLastWeek + "";
- todoDataDto.pavgLastWeek = (decimalFormat.format((double) ptotalLastWeek / 7.0)) + "";
- todoDataDto.pcountLastMonth = ptotalLastMonth + "";
- todoDataDto.pavgLastMonth = (decimalFormat.format((double) ptotalLastMonth / (double) day)) + "";
- todoDataDto.data = data;
- } catch (Exception ex) {
- LOGGER.error(ex.getMessage(), ex);
- }
- return todoDataDto;
- }