List of usage examples for java.util GregorianCalendar getTime
public final Date getTime()
Date object representing this Calendar's time value (millisecond offset from the Epoch"). From source file:ffsutils.TaskUtils.java
public static ArrayList<Tasks> getTrainingTask(Connection conn, UserAccount Username, String onlyUser) throws SQLException { System.out.println("getTrainingTask " + Username); Date date = new Date(); Calendar cal = Calendar.getInstance(); GregorianCalendar cal121 = new GregorianCalendar(); cal121.setTime(date);//from w ww . j av a 2s. co m cal121.add(Calendar.DATE, 3); Date days3 = cal121.getTime(); String year = Integer.toString(cal.get(Calendar.YEAR)); String month = Integer.toString(cal.get(Calendar.MONTH) + 1); String day = Integer.toString(cal.get(Calendar.DAY_OF_MONTH)); String hour = Integer.toString(cal.get(Calendar.HOUR_OF_DAY)); String minute = Integer.toString(cal.get(Calendar.MINUTE)); String sql = ""; if (onlyUser == null) { onlyUser = ""; } if (onlyUser.equals(null) | onlyUser.length() < 4) { sql = "select * from " + Username.getcompany() + ".tasks where ((((taskto1 = ?) and ((taskstat1 <> 'Completed') or (taskstat1 is null))) or ((taskto2 = ?) and ((taskstat2 <> 'Completed') or (taskstat2 is null))) or ((taskto3 = ?) and ((taskstat3 <> 'Completed') or (taskstat3 is null))) or ((taskto4 = ?) and ((taskstat4 <> 'Completed') or (taskstat4 is null))) or ((taskto5 = ?) and ((taskstat5 <> 'Completed') or (taskstat5 is null))) or ((taskto6 = ?) and ((taskstat6 <> 'Completed') or (taskstat6 is null))) or ((taskto7 = ?) and ((taskstat7 <> 'Completed') or (taskstat7 is null))) or ((taskto8 = ?) and ((taskstat8 <> 'Completed') or (taskstat8 is null))) or ((taskto9 = ?) and ((taskstat9 <> 'Completed') or (taskstat9 is null))) or ((taskto10 = ?) and ((taskstat10 <> 'Completed') or (taskstat10 is null))) or ((taskto11 = ?) and ((taskstat11 <> 'Completed') or (taskstat11 is null))) or ((taskto12 = ?) and ((taskstat12 <> 'Completed') or (taskstat12 is null))) or ((taskto13 = ?) and ((taskstat13 <> 'Completed') or (taskstat13 is null))) or ((taskto14 = ?) and ((taskstat14 <> 'Completed') or (taskstat14 is null))) or ((taskto15 = ?) and ((taskstat15 <> 'Completed') or (taskstat15 is null)))) or (taskfrom = ?))and (taskstat <> 'Completed') and (train = 'Y')"; } else { sql = "select * from " + Username.getcompany() + ".tasks where ((((taskto1 = ?) and ((taskstat1 <> 'Completed') or (taskstat1 is null))) or ((taskto2 = ?) and ((taskstat2 <> 'Completed') or (taskstat2 is null))) or ((taskto3 = ?) and ((taskstat3 <> 'Completed') or (taskstat3 is null))) or ((taskto4 = ?) and ((taskstat4 <> 'Completed') or (taskstat4 is null))) or ((taskto5 = ?) and ((taskstat5 <> 'Completed') or (taskstat5 is null))) or ((taskto6 = ?) and ((taskstat6 <> 'Completed') or (taskstat6 is null))) or ((taskto7 = ?) and ((taskstat7 <> 'Completed') or (taskstat7 is null))) or ((taskto8 = ?) and ((taskstat8 <> 'Completed') or (taskstat8 is null))) or ((taskto9 = ?) and ((taskstat9 <> 'Completed') or (taskstat9 is null))) or ((taskto10 = ?) and ((taskstat10 <> 'Completed') or (taskstat10 is null))) or ((taskto11 = ?) and ((taskstat11 <> 'Completed') or (taskstat11 is null))) or ((taskto12 = ?) and ((taskstat12 <> 'Completed') or (taskstat12 is null))) or ((taskto13 = ?) and ((taskstat13 <> 'Completed') or (taskstat13 is null))) or ((taskto14 = ?) and ((taskstat14 <> 'Completed') or (taskstat14 is null))) or ((taskto15 = ?) and ((taskstat15 <> 'Completed') or (taskstat15 is null)))) or (taskfrom = ?))and (taskstat <> 'Completed') and (train = 'Y') and (taskfrom='" + onlyUser + "')"; } PreparedStatement pstm = conn.prepareStatement(sql); pstm.setString(1, Username.getUserName()); pstm.setString(2, Username.getUserName()); pstm.setString(3, Username.getUserName()); pstm.setString(4, Username.getUserName()); pstm.setString(5, Username.getUserName()); pstm.setString(6, Username.getUserName()); pstm.setString(7, Username.getUserName()); pstm.setString(8, Username.getUserName()); pstm.setString(9, Username.getUserName()); pstm.setString(10, Username.getUserName()); pstm.setString(11, Username.getUserName()); pstm.setString(12, Username.getUserName()); pstm.setString(13, Username.getUserName()); pstm.setString(14, Username.getUserName()); pstm.setString(15, Username.getUserName()); pstm.setString(16, Username.getUserName()); ResultSet rs = pstm.executeQuery(); ArrayList<Tasks> list = new ArrayList<Tasks>(); while (rs.next()) { Date date1 = new Date(); Calendar cal1 = new GregorianCalendar(); if (rs.getTimestamp("revdate") == null) { cal1.setTime(rs.getTimestamp("enddate")); } else { cal1.setTime(rs.getTimestamp("revdate")); } String year1 = Integer.toString(cal1.get(Calendar.YEAR)); String month1 = Integer.toString(cal1.get(Calendar.MONTH) + 1); String day1 = Integer.toString(cal1.get(Calendar.DAY_OF_MONTH)); String hour1 = Integer.toString(cal1.get(Calendar.HOUR_OF_DAY)); String minute1 = Integer.toString(cal1.get(Calendar.MINUTE)); if (hour1.length() == 1) { hour1 = "0" + hour1; } if (minute1.length() == 1) { minute1 = "0" + minute1; } if (month1.length() == 1) { month1 = "0" + month1; } if (day1.length() == 1) { day1 = "0" + day1; } Date date2 = new Date(); Calendar cal2 = new GregorianCalendar(); cal2.setTime(rs.getTimestamp("enddate")); String year2 = Integer.toString(cal2.get(Calendar.YEAR)); String month2 = Integer.toString(cal2.get(Calendar.MONTH) + 1); String day2 = Integer.toString(cal2.get(Calendar.DAY_OF_MONTH)); String hour2 = Integer.toString(cal2.get(Calendar.HOUR_OF_DAY)); String minute2 = Integer.toString(cal2.get(Calendar.MINUTE)); if (hour2.length() == 1) { hour2 = "0" + hour2; } if (minute2.length() == 1) { minute2 = "0" + minute2; } if (month2.length() == 1) { month2 = "0" + month2; } if (day2.length() == 1) { day2 = "0" + day2; } Date date3 = new Date(); Calendar cal3 = new GregorianCalendar(); cal3.setTime(rs.getTimestamp("startdate")); String year3 = Integer.toString(cal3.get(Calendar.YEAR)); String month3 = Integer.toString(cal3.get(Calendar.MONTH) + 1); String day3 = Integer.toString(cal3.get(Calendar.DAY_OF_MONTH)); String hour3 = Integer.toString(cal3.get(Calendar.HOUR_OF_DAY)); String minute3 = Integer.toString(cal3.get(Calendar.MINUTE)); if (hour3.length() == 1) { hour3 = "0" + hour3; } if (minute3.length() == 1) { minute3 = "0" + minute3; } if (month3.length() == 1) { month3 = "0" + month3; } if (day3.length() == 1) { day3 = "0" + day3; } String tranid = rs.getString("tranid"); String taskfrom = rs.getString("taskfrom"); String description = rs.getString("description"); String revdate = year1 + "-" + month1 + "-" + day1 + " " + hour1 + ":" + minute1; String enddate = year2 + "-" + month2 + "-" + day2 + " " + hour2 + ":" + minute2; String taskstat; if (rs.getString("taskstat") == null) { taskstat = "Not Started"; } else { taskstat = rs.getString("taskstat"); } //String taskstat = rs.getString("taskstat"); String startdate = year3 + "-" + month3 + "-" + day3 + " " + hour3 + ":" + minute3; Tasks task = new Tasks(); task.setTranid(tranid); task.setTaskfrom(taskfrom); task.setDescription(description); task.setRevdate(revdate); task.setEnddate(enddate); task.setTaskstat(taskstat); task.setStartdate(startdate); String color1 = ""; if (date.after(rs.getTimestamp("revdate"))) { color1 = "red"; } if (color1 == "" && days3.after(rs.getTimestamp("revdate"))) { color1 = "orange"; } if (color1 == "") { color1 = "green"; } task.setlinkup1(color1); String color2 = "black"; if ((Username.equals(rs.getString("taskto1")) && (!"Y".equals(rs.getString("newt1")))) || (Username.equals(rs.getString("taskto2")) && (!"Y".equals(rs.getString("newt2")))) || (Username.equals(rs.getString("taskto3")) && (!"Y".equals(rs.getString("newt3")))) || (Username.equals(rs.getString("taskto4")) && (!"Y".equals(rs.getString("newt4")))) || (Username.equals(rs.getString("taskto5")) && (!"Y".equals(rs.getString("newt5")))) || (Username.equals(rs.getString("taskto6")) && (!"Y".equals(rs.getString("newt6")))) || (Username.equals(rs.getString("taskto7")) && (!"Y".equals(rs.getString("newt7")))) || (Username.equals(rs.getString("taskto8")) && (!"Y".equals(rs.getString("newt8")))) || (Username.equals(rs.getString("taskto9")) && (!"Y".equals(rs.getString("newt9")))) || (Username.equals(rs.getString("taskto10")) && (!"Y".equals(rs.getString("newt10")))) || (Username.equals(rs.getString("taskto11")) && (!"Y".equals(rs.getString("newt11")))) || (Username.equals(rs.getString("taskto12")) && (!"Y".equals(rs.getString("newt12")))) || (Username.equals(rs.getString("taskto13")) && (!"Y".equals(rs.getString("newt13")))) || (Username.equals(rs.getString("taskto14")) && (!"Y".equals(rs.getString("newt14")))) || (Username.equals(rs.getString("taskto15")) && (!"Y".equals(rs.getString("newt15"))))) { color2 = "green"; } task.setlinkup2(color2); list.add(task); } return list; }
From source file:ffsutils.TaskUtils.java
public static ArrayList<Tasks> getNewTask(Connection conn, UserAccount Username, String onlyUser) throws SQLException { System.out.println("getNewTask " + onlyUser); Date date = new Date(); Calendar cal = Calendar.getInstance(); GregorianCalendar cal121 = new GregorianCalendar(); cal121.setTime(date);//from w ww . j av a2s. co m cal121.add(Calendar.DATE, 3); Date days3 = cal121.getTime(); String year = Integer.toString(cal.get(Calendar.YEAR)); String month = Integer.toString(cal.get(Calendar.MONTH) + 1); String day = Integer.toString(cal.get(Calendar.DAY_OF_MONTH)); String hour = Integer.toString(cal.get(Calendar.HOUR_OF_DAY)); String minute = Integer.toString(cal.get(Calendar.MINUTE)); String sql = ""; if (onlyUser == null) { onlyUser = ""; } if (onlyUser.equals(null) | onlyUser.length() < 4) { sql = "select * from " + Username.getcompany() + ".tasks where ((taskstat <> 'Completed') or (taskstat is null)) and (((taskstat1 <> 'Completed') or (taskstat1 is null)) and (taskto1 = ?) and (comadd1 is null)) or (((taskstat2 <> 'Completed') or (taskstat2 is null)) and (taskto2 = ?) and (comadd2 is null)) or (((taskstat3 <> 'Completed') or (taskstat3 is null)) and (taskto3 = ?) and (comadd3 is null)) or (((taskstat4 <> 'Completed') or (taskstat4 is null)) and (taskto4 = ?) and (comadd4 is null)) or (((taskstat5 <> 'Completed') or (taskstat5 is null)) and (taskto5 = ?) and (comadd5 is null)) or (((taskstat6 <> 'Completed') or (taskstat6 is null)) and (taskto6 = ?) and (comadd6 is null)) or (((taskstat7 <> 'Completed') or (taskstat7 is null)) and (taskto7 = ?) and (comadd7 is null)) or (((taskstat8 <> 'Completed') or (taskstat8 is null)) and (taskto8 = ?) and (comadd8 is null)) or (((taskstat9 <> 'Completed') or (taskstat9 is null)) and (taskto9 = ?) and (comadd9 is null)) or (((taskstat11 <> 'Completed') or (taskstat11 is null)) and (taskto11 = ?) and (comadd11 is null)) or (((taskstat12 <> 'Completed') or (taskstat12 is null)) and (taskto12 = ?) and (comadd12 is null)) or (((taskstat13 <> 'Completed') or (taskstat13 is null)) and (taskto13 = ?) and (comadd13 is null)) or (((taskstat14 <> 'Completed') or (taskstat14 is null)) and (taskto14 = ?) and (comadd14 is null)) or (((taskstat15 <> 'Completed') or (taskstat15 is null)) and (taskto15 = ?) and (comadd15 is null)) or (((taskstat10 <> 'Completed') or (taskstat10 is null)) and (taskto10 = ?) and (comadd10 is null)) or (((taskstat <> 'Completed') or (taskstat is null)) and (taskfrom = ?) and (comaddf is null)) "; } else { sql = "select * from " + Username.getcompany() + ".tasks where (((taskstat <> 'Completed') or (taskstat is null)) and (((taskstat1 <> 'Completed') or (taskstat1 is null)) and (taskto1 = ?) and (comadd1 is null)) or (((taskstat2 <> 'Completed') or (taskstat2 is null)) and (taskto2 = ?) and (comadd2 is null)) or (((taskstat3 <> 'Completed') or (taskstat3 is null)) and (taskto3 = ?) and (comadd3 is null)) or (((taskstat4 <> 'Completed') or (taskstat4 is null)) and (taskto4 = ?) and (comadd4 is null)) or (((taskstat5 <> 'Completed') or (taskstat5 is null)) and (taskto5 = ?) and (comadd5 is null)) or (((taskstat6 <> 'Completed') or (taskstat6 is null)) and (taskto6 = ?) and (comadd6 is null)) or (((taskstat7 <> 'Completed') or (taskstat7 is null)) and (taskto7 = ?) and (comadd7 is null)) or (((taskstat8 <> 'Completed') or (taskstat8 is null)) and (taskto8 = ?) and (comadd8 is null)) or (((taskstat9 <> 'Completed') or (taskstat9 is null)) and (taskto9 = ?) and (comadd9 is null)) or (((taskstat11 <> 'Completed') or (taskstat11 is null)) and (taskto11 = ?) and (comadd11 is null)) or (((taskstat12 <> 'Completed') or (taskstat12 is null)) and (taskto12 = ?) and (comadd12 is null)) or (((taskstat13 <> 'Completed') or (taskstat13 is null)) and (taskto13 = ?) and (comadd13 is null)) or (((taskstat14 <> 'Completed') or (taskstat14 is null)) and (taskto14 = ?) and (comadd14 is null)) or (((taskstat15 <> 'Completed') or (taskstat15 is null)) and (taskto15 = ?) and (comadd15 is null)) or (((taskstat10 <> 'Completed') or (taskstat10 is null)) and (taskto10 = ?) and (comadd10 is null)) or (((taskstat <> 'Completed') or (taskstat is null)) and (taskfrom = ?) and (comaddf is null))) and (taskfrom ='" + onlyUser + "') "; } PreparedStatement pstm = conn.prepareStatement(sql); pstm.setString(1, Username.getUserName()); pstm.setString(2, Username.getUserName()); pstm.setString(3, Username.getUserName()); pstm.setString(4, Username.getUserName()); pstm.setString(5, Username.getUserName()); pstm.setString(6, Username.getUserName()); pstm.setString(7, Username.getUserName()); pstm.setString(8, Username.getUserName()); pstm.setString(9, Username.getUserName()); pstm.setString(10, Username.getUserName()); pstm.setString(11, Username.getUserName()); pstm.setString(12, Username.getUserName()); pstm.setString(13, Username.getUserName()); pstm.setString(14, Username.getUserName()); pstm.setString(15, Username.getUserName()); pstm.setString(16, Username.getUserName()); ResultSet rs = pstm.executeQuery(); ArrayList<Tasks> list = new ArrayList<Tasks>(); while (rs.next()) { Date date1 = new Date(); Calendar cal1 = new GregorianCalendar(); if (rs.getTimestamp("revdate") == null) { cal1.setTime(rs.getTimestamp("startdate")); } else { cal1.setTime(rs.getTimestamp("revdate")); } String year1 = Integer.toString(cal1.get(Calendar.YEAR)); String month1 = Integer.toString(cal1.get(Calendar.MONTH) + 1); String day1 = Integer.toString(cal1.get(Calendar.DAY_OF_MONTH)); String hour1 = Integer.toString(cal1.get(Calendar.HOUR_OF_DAY)); String minute1 = Integer.toString(cal1.get(Calendar.MINUTE)); if (hour1.length() == 1) { hour1 = "0" + hour1; } if (minute1.length() == 1) { minute1 = "0" + minute1; } if (month1.length() == 1) { month1 = "0" + month1; } if (day1.length() == 1) { day1 = "0" + day1; } Date date2 = new Date(); Calendar cal2 = new GregorianCalendar(); cal2.setTime(rs.getTimestamp("enddate")); String year2 = Integer.toString(cal2.get(Calendar.YEAR)); String month2 = Integer.toString(cal2.get(Calendar.MONTH) + 1); String day2 = Integer.toString(cal2.get(Calendar.DAY_OF_MONTH)); String hour2 = Integer.toString(cal2.get(Calendar.HOUR_OF_DAY)); String minute2 = Integer.toString(cal2.get(Calendar.MINUTE)); if (hour2.length() == 1) { hour2 = "0" + hour2; } if (minute2.length() == 1) { minute2 = "0" + minute2; } if (month2.length() == 1) { month2 = "0" + month2; } if (day2.length() == 1) { day2 = "0" + day2; } Date date3 = new Date(); Calendar cal3 = new GregorianCalendar(); cal3.setTime(rs.getTimestamp("startdate")); String year3 = Integer.toString(cal3.get(Calendar.YEAR)); String month3 = Integer.toString(cal3.get(Calendar.MONTH) + 1); String day3 = Integer.toString(cal3.get(Calendar.DAY_OF_MONTH)); String hour3 = Integer.toString(cal3.get(Calendar.HOUR_OF_DAY)); String minute3 = Integer.toString(cal3.get(Calendar.MINUTE)); if (hour3.length() == 1) { hour3 = "0" + hour3; } if (minute3.length() == 1) { minute3 = "0" + minute3; } if (month3.length() == 1) { month3 = "0" + month3; } if (day3.length() == 1) { day3 = "0" + day3; } String tranid = rs.getString("tranid"); String taskfrom = rs.getString("taskfrom"); String description = rs.getString("description"); String revdate = year1 + "-" + month1 + "-" + day1 + " " + hour1 + ":" + minute1; String enddate = year2 + "-" + month2 + "-" + day2 + " " + hour2 + ":" + minute2; String taskstat; if (rs.getString("taskstat") == null) { taskstat = "Not Started"; } else { taskstat = rs.getString("taskstat"); } //String taskstat = rs.getString("taskstat"); String startdate = year3 + "-" + month3 + "-" + day3 + " " + hour3 + ":" + minute3; Tasks task = new Tasks(); task.setTranid(tranid); task.setTaskfrom(taskfrom); task.setDescription(description); task.setRevdate(revdate); task.setEnddate(enddate); task.setTaskstat(taskstat); task.setStartdate(startdate); String color1 = ""; if (date.after(rs.getTimestamp("revdate"))) { color1 = "red"; } if (color1 == "" && days3.after(rs.getTimestamp("revdate"))) { color1 = "orange"; } if (color1 == "") { color1 = "green"; } task.setlinkup1(color1); String color2 = "black"; if ((Username.equals(rs.getString("taskto1")) && (!"Y".equals(rs.getString("newt1")))) || (Username.equals(rs.getString("taskto2")) && (!"Y".equals(rs.getString("newt2")))) || (Username.equals(rs.getString("taskto3")) && (!"Y".equals(rs.getString("newt3")))) || (Username.equals(rs.getString("taskto4")) && (!"Y".equals(rs.getString("newt4")))) || (Username.equals(rs.getString("taskto5")) && (!"Y".equals(rs.getString("newt5")))) || (Username.equals(rs.getString("taskto6")) && (!"Y".equals(rs.getString("newt6")))) || (Username.equals(rs.getString("taskto7")) && (!"Y".equals(rs.getString("newt7")))) || (Username.equals(rs.getString("taskto8")) && (!"Y".equals(rs.getString("newt8")))) || (Username.equals(rs.getString("taskto9")) && (!"Y".equals(rs.getString("newt9")))) || (Username.equals(rs.getString("taskto10")) && (!"Y".equals(rs.getString("newt10")))) || (Username.equals(rs.getString("taskto11")) && (!"Y".equals(rs.getString("newt11")))) || (Username.equals(rs.getString("taskto12")) && (!"Y".equals(rs.getString("newt12")))) || (Username.equals(rs.getString("taskto13")) && (!"Y".equals(rs.getString("newt13")))) || (Username.equals(rs.getString("taskto14")) && (!"Y".equals(rs.getString("newt14")))) || (Username.equals(rs.getString("taskto15")) && (!"Y".equals(rs.getString("newt15"))))) { color2 = "green"; } task.setlinkup2(color2); list.add(task); } return list; }
From source file:ffsutils.TaskUtils.java
public static ArrayList<Tasks> getUpdatedTasks(Connection conn, UserAccount Username, String onlyUser) throws SQLException { System.out.println("getUpdatedTasks " + Username); Date date = new Date(); Calendar cal = Calendar.getInstance(); GregorianCalendar cal121 = new GregorianCalendar(); cal121.setTime(date);/* ww w . j a v a 2 s . c o m*/ cal121.add(Calendar.DATE, 3); Date days3 = cal121.getTime(); String year = Integer.toString(cal.get(Calendar.YEAR)); String month = Integer.toString(cal.get(Calendar.MONTH) + 1); String day = Integer.toString(cal.get(Calendar.DAY_OF_MONTH)); String hour = Integer.toString(cal.get(Calendar.HOUR_OF_DAY)); String minute = Integer.toString(cal.get(Calendar.MINUTE)); String sql = ""; if (onlyUser == null) { onlyUser = ""; } if (onlyUser == null) { sql = "select * from " + Username.getcompany() + ".tasks where (((taskstat1 <> 'Completed') or (taskstat1 is null)) and (taskto1 = ?) and (newt1 is null)) or (((taskstat2 <> 'Completed') or (taskstat2 is null)) and (taskto2 = ?) and (newt2 is null)) or (((taskstat3 <> 'Completed') or (taskstat3 is null)) and (taskto3 = ?) and (newt3 is null)) or(((taskstat4 <> 'Completed') or (taskstat4 is null)) and (taskto4 = ?) and (newt4 is null)) or (((taskstat5 <> 'Completed') or (taskstat5 is null)) and (taskto5 = ?) and (newt5 is null)) or (((taskstat6 <> 'Completed') or (taskstat6 is null)) and (taskto6 = ?) and (newt6 is null)) or (((taskstat7 <> 'Completed') or (taskstat7 is null)) and (taskto7 = ?) and (newt7 is null)) or (((taskstat8 <> 'Completed') or (taskstat8 is null)) and (taskto8 = ?) and (newt8 is null)) or (((taskstat9 <> 'Completed') or (taskstat9 is null)) and (taskto9 = ?) and (newt9 is null)) or (((taskstat11 <> 'Completed') or (taskstat11 is null)) and (taskto11 = ?) and (newt11 is null)) or (((taskstat12 <> 'Completed') or (taskstat12 is null)) and (taskto12 = ?) and (newt12 is null)) or (((taskstat13 <> 'Completed') or (taskstat12 is null)) and (taskto13 = ?) and (newt13 is null)) or (((taskstat14 <> 'Completed') or (taskstat14 is null)) and (taskto14 = ?) and (newt14 is null)) or (((taskstat15 <> 'Completed') or (taskstat15 is null)) and (taskto15 = ?) and (newt15 is null)) or (((taskstat10 <> 'Completed') or (taskstat10 is null)) and (taskto10 = ?) and (newt10 is null)) or (((taskstat <> 'Completed') or (taskstat is null)) and (taskfrom = ?) and (newtf is null))"; } else { if (onlyUser.length() < 3) { sql = "select * from " + Username.getcompany() + ".tasks where (((taskstat1 <> 'Completed') or (taskstat1 is null)) and (taskto1 = ?) and (newt1 is null)) or (((taskstat2 <> 'Completed') or (taskstat2 is null)) and (taskto2 = ?) and (newt2 is null)) or (((taskstat3 <> 'Completed') or (taskstat3 is null)) and (taskto3 = ?) and (newt3 is null)) or(((taskstat4 <> 'Completed') or (taskstat4 is null)) and (taskto4 = ?) and (newt4 is null)) or (((taskstat5 <> 'Completed') or (taskstat5 is null)) and (taskto5 = ?) and (newt5 is null)) or (((taskstat6 <> 'Completed') or (taskstat6 is null)) and (taskto6 = ?) and (newt6 is null)) or (((taskstat7 <> 'Completed') or (taskstat7 is null)) and (taskto7 = ?) and (newt7 is null)) or (((taskstat8 <> 'Completed') or (taskstat8 is null)) and (taskto8 = ?) and (newt8 is null)) or (((taskstat9 <> 'Completed') or (taskstat9 is null)) and (taskto9 = ?) and (newt9 is null)) or (((taskstat11 <> 'Completed') or (taskstat11 is null)) and (taskto11 = ?) and (newt11 is null)) or (((taskstat12 <> 'Completed') or (taskstat12 is null)) and (taskto12 = ?) and (newt12 is null)) or (((taskstat13 <> 'Completed') or (taskstat12 is null)) and (taskto13 = ?) and (newt13 is null)) or (((taskstat14 <> 'Completed') or (taskstat14 is null)) and (taskto14 = ?) and (newt14 is null)) or (((taskstat15 <> 'Completed') or (taskstat15 is null)) and (taskto15 = ?) and (newt15 is null)) or (((taskstat10 <> 'Completed') or (taskstat10 is null)) and (taskto10 = ?) and (newt10 is null)) or (((taskstat <> 'Completed') or (taskstat is null)) and (taskfrom = ?) and (newtf is null))"; } else { sql = "select * from " + Username.getcompany() + ".tasks where ((((taskstat1 <> 'Completed') or (taskstat1 is null)) and (taskto1 = ?) and (newt1 is null)) or (((taskstat2 <> 'Completed') or (taskstat2 is null)) and (taskto2 = ?) and (newt2 is null)) or (((taskstat3 <> 'Completed') or (taskstat3 is null)) and (taskto3 = ?) and (newt3 is null)) or(((taskstat4 <> 'Completed') or (taskstat4 is null)) and (taskto4 = ?) and (newt4 is null)) or (((taskstat5 <> 'Completed') or (taskstat5 is null)) and (taskto5 = ?) and (newt5 is null)) or (((taskstat6 <> 'Completed') or (taskstat6 is null)) and (taskto6 = ?) and (newt6 is null)) or (((taskstat7 <> 'Completed') or (taskstat7 is null)) and (taskto7 = ?) and (newt7 is null)) or (((taskstat8 <> 'Completed') or (taskstat8 is null)) and (taskto8 = ?) and (newt8 is null)) or (((taskstat9 <> 'Completed') or (taskstat9 is null)) and (taskto9 = ?) and (newt9 is null)) or (((taskstat11 <> 'Completed') or (taskstat11 is null)) and (taskto11 = ?) and (newt11 is null)) or (((taskstat12 <> 'Completed') or (taskstat12 is null)) and (taskto12 = ?) and (newt12 is null)) or (((taskstat13 <> 'Completed') or (taskstat12 is null)) and (taskto13 = ?) and (newt13 is null)) or (((taskstat14 <> 'Completed') or (taskstat14 is null)) and (taskto14 = ?) and (newt14 is null)) or (((taskstat15 <> 'Completed') or (taskstat15 is null)) and (taskto15 = ?) and (newt15 is null)) or (((taskstat10 <> 'Completed') or (taskstat10 is null)) and (taskto10 = ?) and (newt10 is null)) or (((taskstat <> 'Completed') or (taskstat is null)) and (taskfrom = ?) and (newtf is null))) and (taskfrom = '" + onlyUser + "')"; } } PreparedStatement pstm = conn.prepareStatement(sql); pstm.setString(1, Username.getUserName()); pstm.setString(2, Username.getUserName()); pstm.setString(3, Username.getUserName()); pstm.setString(4, Username.getUserName()); pstm.setString(5, Username.getUserName()); pstm.setString(6, Username.getUserName()); pstm.setString(7, Username.getUserName()); pstm.setString(8, Username.getUserName()); pstm.setString(9, Username.getUserName()); pstm.setString(10, Username.getUserName()); pstm.setString(11, Username.getUserName()); pstm.setString(12, Username.getUserName()); pstm.setString(13, Username.getUserName()); pstm.setString(14, Username.getUserName()); pstm.setString(15, Username.getUserName()); pstm.setString(16, Username.getUserName()); ResultSet rs = pstm.executeQuery(); ArrayList<Tasks> list = new ArrayList<Tasks>(); while (rs.next()) { Date date1 = new Date(); Calendar cal1 = new GregorianCalendar(); if (rs.getTimestamp("revdate") == null) { cal1.setTime(rs.getTimestamp("enddate")); } else { cal1.setTime(rs.getTimestamp("revdate")); } String year1 = Integer.toString(cal1.get(Calendar.YEAR)); String month1 = Integer.toString(cal1.get(Calendar.MONTH) + 1); String day1 = Integer.toString(cal1.get(Calendar.DAY_OF_MONTH)); String hour1 = Integer.toString(cal1.get(Calendar.HOUR_OF_DAY)); String minute1 = Integer.toString(cal1.get(Calendar.MINUTE)); if (hour1.length() == 1) { hour1 = "0" + hour1; } if (minute1.length() == 1) { minute1 = "0" + minute1; } if (month1.length() == 1) { month1 = "0" + month1; } if (day1.length() == 1) { day1 = "0" + day1; } Date date2 = new Date(); Calendar cal2 = new GregorianCalendar(); cal2.setTime(rs.getTimestamp("enddate")); String year2 = Integer.toString(cal2.get(Calendar.YEAR)); String month2 = Integer.toString(cal2.get(Calendar.MONTH) + 1); String day2 = Integer.toString(cal2.get(Calendar.DAY_OF_MONTH)); String hour2 = Integer.toString(cal2.get(Calendar.HOUR_OF_DAY)); String minute2 = Integer.toString(cal2.get(Calendar.MINUTE)); if (hour2.length() == 1) { hour2 = "0" + hour2; } if (minute2.length() == 1) { minute2 = "0" + minute2; } if (month2.length() == 1) { month2 = "0" + month2; } if (day2.length() == 1) { day2 = "0" + day2; } Date date3 = new Date(); Calendar cal3 = new GregorianCalendar(); cal3.setTime(rs.getTimestamp("startdate")); String year3 = Integer.toString(cal3.get(Calendar.YEAR)); String month3 = Integer.toString(cal3.get(Calendar.MONTH) + 1); String day3 = Integer.toString(cal3.get(Calendar.DAY_OF_MONTH)); String hour3 = Integer.toString(cal3.get(Calendar.HOUR_OF_DAY)); String minute3 = Integer.toString(cal3.get(Calendar.MINUTE)); if (hour3.length() == 1) { hour3 = "0" + hour3; } if (minute3.length() == 1) { minute3 = "0" + minute3; } if (month3.length() == 1) { month3 = "0" + month3; } if (day3.length() == 1) { day3 = "0" + day3; } String tranid = rs.getString("tranid"); String taskfrom = rs.getString("taskfrom"); String description = rs.getString("description"); String revdate = year1 + "-" + month1 + "-" + day1 + " " + hour1 + ":" + minute1; String enddate = year2 + "-" + month2 + "-" + day2 + " " + hour2 + ":" + minute2; String taskstat; if (rs.getString("taskstat") == null) { taskstat = "Not Started"; } else { taskstat = rs.getString("taskstat"); } //String taskstat = rs.getString("taskstat"); String startdate = year3 + "-" + month3 + "-" + day3 + " " + hour3 + ":" + minute3; Tasks task = new Tasks(); task.setTranid(tranid); task.setTaskfrom(taskfrom); task.setDescription(description); task.setRevdate(revdate); task.setEnddate(enddate); task.setTaskstat(taskstat); task.setStartdate(startdate); String color1 = ""; if (date.after(rs.getTimestamp("revdate"))) { color1 = "red"; } if (color1 == "" && days3.after(rs.getTimestamp("revdate"))) { color1 = "orange"; } if (color1 == "") { color1 = "green"; } task.setlinkup1(color1); String color2 = "black"; if ((Username.equals(rs.getString("taskto1")) && (!"Y".equals(rs.getString("newt1")))) || (Username.equals(rs.getString("taskto2")) && (!"Y".equals(rs.getString("newt2")))) || (Username.equals(rs.getString("taskto3")) && (!"Y".equals(rs.getString("newt3")))) || (Username.equals(rs.getString("taskto4")) && (!"Y".equals(rs.getString("newt4")))) || (Username.equals(rs.getString("taskto5")) && (!"Y".equals(rs.getString("newt5")))) || (Username.equals(rs.getString("taskto6")) && (!"Y".equals(rs.getString("newt6")))) || (Username.equals(rs.getString("taskto7")) && (!"Y".equals(rs.getString("newt7")))) || (Username.equals(rs.getString("taskto8")) && (!"Y".equals(rs.getString("newt8")))) || (Username.equals(rs.getString("taskto9")) && (!"Y".equals(rs.getString("newt9")))) || (Username.equals(rs.getString("taskto10")) && (!"Y".equals(rs.getString("newt10")))) || (Username.equals(rs.getString("taskto11")) && (!"Y".equals(rs.getString("newt11")))) || (Username.equals(rs.getString("taskto12")) && (!"Y".equals(rs.getString("newt12")))) || (Username.equals(rs.getString("taskto13")) && (!"Y".equals(rs.getString("newt13")))) || (Username.equals(rs.getString("taskto14")) && (!"Y".equals(rs.getString("newt14")))) || (Username.equals(rs.getString("taskto15")) && (!"Y".equals(rs.getString("newt15"))))) { color2 = "green"; } task.setlinkup2(color2); list.add(task); } return list; }
From source file:edu.wisc.ssec.mcidasv.data.cyclone.AtcfStormDataSource.java
/** * _more_/*www. j av a2s . c o m*/ * * @param stormInfo * _more_ * @param tracks * _more_ * @param trackFile * _more_ * @param waysToUse * _more_ * @param throwError * _more_ * * * @return _more_ * @throws Exception * _more_ */ private boolean readTracks(StormInfo stormInfo, StormTrackCollection tracks, String trackFile, Hashtable<String, Boolean> waysToUse, boolean throwError) throws Exception { long t1 = System.currentTimeMillis(); byte[] bytes = readFile(trackFile, true); long t2 = System.currentTimeMillis(); // System.err.println("read time:" + (t2 - t1)); boolean isZip = trackFile.endsWith(".gz"); if ((bytes == null) && isZip) { String withoutGZ = trackFile.substring(0, trackFile.length() - 3); bytes = readFile(withoutGZ, true); isZip = false; } if (bytes == null) { if (throwError) { throw new BadDataException("Unable to read track file:" + trackFile); } return false; } if (isZip) { GZIPInputStream zin = new GZIPInputStream(new ByteArrayInputStream(bytes)); bytes = IOUtil.readBytes(zin); zin.close(); } GregorianCalendar convertCal = new GregorianCalendar(DateUtil.TIMEZONE_GMT); convertCal.clear(); String trackData = new String(bytes); List lines = StringUtil.split(trackData, "\n", true, true); SimpleDateFormat fmt = new SimpleDateFormat("yyyyMMddHH"); fmt.setTimeZone(TimeZone.getTimeZone("UTC")); Hashtable trackMap = new Hashtable(); Real altReal = new Real(RealType.Altitude, 0); // System.err.println("obs:" + lines.size()); /* * Hashtable okWays = new Hashtable(); okWays.put(WAY_CARQ, ""); * okWays.put(WAY_WRNG, ""); okWays.put(WAY_BEST, ""); okWays.put("ETA", * ""); okWays.put("NGX", ""); okWays.put("BAMS", ""); */ Hashtable seenDate = new Hashtable(); initParams(); int xcnt = 0; for (int i = 0; i < lines.size(); i++) { String line = (String) lines.get(i); if (i == 0) { // System.err.println(line); } List toks = StringUtil.split(line, ",", true); /* * System.err.println(toks.size() + " " + BASEIDX); * if(toks.size()<BASEIDX-1) { System.err.println("bad line:" + * line); continue; } else { System.err.println("good line:" + * line); } */ // BASIN,CY,YYYYMMDDHH,TECHNUM,TECH,TAU,LatN/S,LonE/W,VMAX,MSLP,TY,RAD,WINDCODE,RAD1,RAD2,RAD3,RAD4,RADP,RRP,MRD,GUSTS,EYE,SUBREGION,MAXSEAS,INITIALS,DIR,SPEED,STORMNAME,DEPTH,SEAS,SEASCODE,SEAS1,SEAS2,SEAS3,SEAS4 // AL, 01, 2007050612, , BEST, 0, 355N, 740W, 35, 1012, EX, 34, NEQ, // 0, 0, 0, 120, // AL, 01, 2007050812, 01, CARQ, -24, 316N, 723W, 55, 0, DB, 34, // AAA, 0, 0, 0, 0, String dateString = (String) toks.get(IDX_YYYYMMDDHH); String wayString = (String) toks.get(IDX_TECH); // if (okWays.get(wayString) == null) { // continue; // } boolean isBest = wayString.equals(WAY_BEST); boolean isWarning = wayString.equals(WAY_WRNG); boolean isCarq = wayString.equals(WAY_CARQ); int category = ((IDX_TY < toks.size()) ? getCategory((String) toks.get(IDX_TY)) : CATEGORY_XX); if (category != CATEGORY_XX) { // System.err.println("cat:" + category); } String fhour = (String) toks.get(IDX_TAU); int forecastHour = new Integer(fhour).intValue(); // A hack - we've seen some atfc files that have a 5 character // forecast hour // right padded with "00", eg., 01200 if ((fhour.length() == 5) && (forecastHour > 100)) { forecastHour = forecastHour / 100; } if (isWarning || isCarq) { forecastHour = -forecastHour; } // Check for unique dates for this way String dttmkey = wayString + "_" + dateString + "_" + forecastHour; if (seenDate.get(dttmkey) != null) { continue; } seenDate.put(dttmkey, dttmkey); Date dttm = fmt.parse(dateString); convertCal.setTime(dttm); String key; Way way = getWay(wayString, null); if (!isBest && (waysToUse != null) && (waysToUse.size() > 0) && (waysToUse.get(wayString) == null)) { continue; } if (isBest) { key = wayString; } else { key = wayString + "_" + dateString; convertCal.add(Calendar.HOUR_OF_DAY, forecastHour); } dttm = convertCal.getTime(); StormTrack track = (StormTrack) trackMap.get(key); if (track == null) { way = (isBest ? Way.OBSERVATION : way); track = new StormTrack(stormInfo, addWay(way), new DateTime(dttm), obsParams); trackMap.put(key, track); tracks.addTrack(track); } String latString = (String) toks.get(IDX_LAT); String lonString = (String) toks.get(IDX_LON); String t = latString + " " + lonString; boolean south = latString.endsWith("S"); boolean west = lonString.endsWith("W"); double latitude = Double.parseDouble(latString.substring(0, latString.length() - 1)) / 10.0; double longitude = Double.parseDouble(lonString.substring(0, lonString.length() - 1)) / 10.0; if (south) { latitude = -latitude; } if (west) { longitude = -longitude; } EarthLocation elt = new EarthLocationLite(new Real(RealType.Latitude, latitude), new Real(RealType.Longitude, longitude), altReal); List<Real> attributes = new ArrayList<Real>(); double windspeed = ((IDX_VMAX < toks.size()) ? getDouble((String) toks.get(IDX_VMAX)) : Double.NaN); double pressure = ((IDX_MSLP < toks.size()) ? getDouble((String) toks.get(IDX_MSLP)) : Double.NaN); attributes.add(PARAM_STORMCATEGORY.getReal((double) category)); attributes.add(PARAM_MINPRESSURE.getReal(pressure)); attributes.add(PARAM_MAXWINDSPEED_KTS.getReal(windspeed)); StormTrackPoint stp = new StormTrackPoint(elt, new DateTime(dttm), forecastHour, attributes); track.addPoint(stp); } return true; }
From source file:com.lp.server.auftrag.ejbfac.AuftragFacBean.java
/** * Fuer eine bestimmte Auftragsart fuer einen bestimmten Zeitraum und eine * bestimmte Terminart (Belegdatum, Liefertermin, Finaltermin) den * Nettoauftragswert in Abhaengigkeit vom aktuellen Mandanten bestimmen. <br> * Beruecksichtigt werden koennen entweder alle offenen Auftraege oder alle * eingegangenen Auftraege./*from w w w . jav a2 s.com*/ * * @param cNrAuftragartI * die Auftragart (Frei, Rahmen, Abruf) * @param whichKriteriumI * welche Zeitraumart (Belegdatum, Liefertermin, Finaltermin) * @param gcBerechnungsdatumVonI * ab diesem Datum * @param gcBerechnungsdatumBisI * bis zu diesem Datum * @param offenOderEingegangenI * sollen alle offenen oder alle eingegangengen Auftraegr * beruecksichtigt werden * @param theClientDto * der aktuelle Benutzer * @return BigDecimal der Nettoauftragswert, 0 wenn keine offenen Positionen * gefunden wurden * @throws EJBExceptionLP * Ausnahme */ public BigDecimal berechneSummeAuftragsnettowert(String cNrAuftragartI, String whichKriteriumI, GregorianCalendar gcBerechnungsdatumVonI, GregorianCalendar gcBerechnungsdatumBisI, String offenOderEingegangenI, TheClientDto theClientDto) throws EJBExceptionLP { StringBuffer buff = new StringBuffer(); buff.append(offenOderEingegangenI).append(", ").append(whichKriteriumI).append(", ").append(cNrAuftragartI) .append(", ").append("Von: ") .append(Helper.formatDatum(gcBerechnungsdatumVonI.getTime(), theClientDto.getLocUi())) .append(", Bis: ") .append(Helper.formatDatum(gcBerechnungsdatumBisI.getTime(), theClientDto.getLocUi())); myLogger.info(buff.toString()); BigDecimal nSummeAuftragsnettowert = new BigDecimal(0); Session session = null; try { SessionFactory factory = FLRSessionFactory.getFactory(); session = factory.openSession(); // Criteria duerfen keine Texts oder Blobs enthalten! // Criteria anlegen fuer alle referenzierten Objekte anlegen Criteria crit = session.createCriteria(FLRAuftragFuerUebersicht.class); // Einschraenken nach Mandant crit.add(Restrictions.eq("mandant_c_nr", theClientDto.getMandant())); // Einschraenken nach Auftragart crit.add(Restrictions.eq(AuftragFac.FLR_AUFTRAG_AUFTRAGART_C_NR, cNrAuftragartI)); if (whichKriteriumI.equals(AuftragFac.KRIT_BELEGDATUM)) { // Belegdatum von bis: flrauftrag.t_belegdatum if (gcBerechnungsdatumVonI != null) { crit.add(Restrictions.ge(AuftragFac.FLR_AUFTRAG_D_BELEGDATUM, new java.sql.Date(gcBerechnungsdatumVonI.getTimeInMillis()))); } if (gcBerechnungsdatumBisI != null) { crit.add(Restrictions.lt(AuftragFac.FLR_AUFTRAG_D_BELEGDATUM, new java.sql.Date(gcBerechnungsdatumBisI.getTimeInMillis()))); } } else if (whichKriteriumI.equals(AuftragFac.KRIT_LIEFERTERMIN)) { // Liefertermin von bis: flrauftrag.t_liefertermin if (gcBerechnungsdatumVonI != null) { crit.add(Restrictions.ge(AuftragFac.FLR_AUFTRAG_T_LIEFERTERMIN, new java.sql.Date(gcBerechnungsdatumVonI.getTimeInMillis()))); } if (gcBerechnungsdatumBisI != null) { crit.add(Restrictions.le(AuftragFac.FLR_AUFTRAG_T_LIEFERTERMIN, new java.sql.Date(gcBerechnungsdatumBisI.getTimeInMillis()))); } } else if (whichKriteriumI.equals(AuftragFac.KRIT_FINALTERMIN)) { // Belegdatum von bis: flrauftrag.t_finaltermin if (gcBerechnungsdatumVonI != null) { crit.add(Restrictions.ge(AuftragFac.FLR_AUFTRAG_T_FINALTERMIN, new java.sql.Date(gcBerechnungsdatumVonI.getTimeInMillis()))); } if (gcBerechnungsdatumBisI != null) { crit.add(Restrictions.le(AuftragFac.FLR_AUFTRAG_T_FINALTERMIN, new java.sql.Date(gcBerechnungsdatumBisI.getTimeInMillis()))); } } // Einschraenken nach Auftragstatus if (offenOderEingegangenI.equals(AuftragFac.AUFT_UMSATZUEBERSICHT_OFFEN)) { crit.add(Expression.or( Restrictions.eq(AuftragFac.FLR_AUFTRAG_AUFTRAGSTATUS_C_NR, AuftragServiceFac.AUFTRAGSTATUS_OFFEN), Restrictions.eq(AuftragFac.FLR_AUFTRAG_AUFTRAGSTATUS_C_NR, AuftragServiceFac.AUFTRAGSTATUS_TEILERLEDIGT))); } else if (offenOderEingegangenI.equals(AuftragFac.AUFT_UMSATZUEBERSICHT_EINGANG)) { crit.add(Restrictions.ne(AuftragFac.FLR_AUFTRAG_AUFTRAGSTATUS_C_NR, AuftragServiceFac.AUFTRAGSTATUS_ANGELEGT)); crit.add(Restrictions.ne(AuftragFac.FLR_AUFTRAG_AUFTRAGSTATUS_C_NR, AuftragServiceFac.AUFTRAGSTATUS_STORNIERT)); } List<?> list = crit.list(); if (offenOderEingegangenI.equals(AuftragFac.AUFT_UMSATZUEBERSICHT_OFFEN)) { nSummeAuftragsnettowert = berechneAuftragsnettowertOffen(list, session, theClientDto); } else if (offenOderEingegangenI.equals(AuftragFac.AUFT_UMSATZUEBERSICHT_EINGANG)) { nSummeAuftragsnettowert = berechneAuftragsnettowertEingang(list, theClientDto); } } catch (Throwable t) { throw new EJBExceptionLP(EJBExceptionLP.FEHLER_FLR, new Exception(t)); } finally { try { session.close(); } catch (HibernateException he) { throw new EJBExceptionLP(EJBExceptionLP.FEHLER_HIBERNATE, he); } } return nSummeAuftragsnettowert; }
From source file:ffsutils.TaskUtils.java
public static ArrayList<Tasks> getTaskToMeComp(Connection conn, UserAccount Username, String onlyUser) throws SQLException { System.out.println("getTaskToMeComp " + Username); Date date = new Date(); Calendar cal = Calendar.getInstance(); GregorianCalendar cal121 = new GregorianCalendar(); cal121.setTime(date);//from www . j ava 2s. co m cal121.add(Calendar.DATE, 3); Date days3 = cal121.getTime(); String year = Integer.toString(cal.get(Calendar.YEAR)); String month = Integer.toString(cal.get(Calendar.MONTH) + 1); String day = Integer.toString(cal.get(Calendar.DAY_OF_MONTH)); String hour = Integer.toString(cal.get(Calendar.HOUR_OF_DAY)); String minute = Integer.toString(cal.get(Calendar.MINUTE)); if (onlyUser == null) { onlyUser = ""; } String sql = ""; if (onlyUser.equals(null) | onlyUser.length() < 4) { sql = "select * from " + Username.getcompany() + ".tasks where (((taskto1 = ?) and (taskstat1 = 'Completed')) or ((taskto2 = ?) and (taskstat2 = 'Completed')) or ((taskto3 = ?) and (taskstat3 = 'Completed')) or ((taskto4 = ?) and (taskstat4 = 'Completed')) or ((taskto5 = ?) and (taskstat5 = 'Completed')) or ((taskto6 = ?) and (taskstat6 = 'Completed')) or ((taskto7 = ?) and (taskstat7 = 'Completed')) or ((taskto8 = ?) and (taskstat8 = 'Completed')) or ((taskto9 = ?) and (taskstat9 = 'Completed')) or ((taskto10 = ?) and (taskstat10 = 'Completed')) or ((taskto11 = ?) and (taskstat11 = 'Completed')) or ((taskto12 = ?) and (taskstat12 = 'Completed')) or ((taskto13 = ?) and (taskstat13 = 'Completed')) or ((taskto14 = ?) and (taskstat14 = 'Completed')) or ((taskto15 = ?) and (taskstat15 = 'Completed')) ) and (recur = '0')"; } else { sql = "select * from " + Username.getcompany() + ".tasks where ((((taskto1 = ?) and (taskstat1 = 'Completed')) or ((taskto2 = ?) and (taskstat2 = 'Completed')) or ((taskto3 = ?) and (taskstat3 = 'Completed')) or ((taskto4 = ?) and (taskstat4 = 'Completed')) or ((taskto5 = ?) and (taskstat5 = 'Completed')) or ((taskto6 = ?) and (taskstat6 = 'Completed')) or ((taskto7 = ?) and (taskstat7 = 'Completed')) or ((taskto8 = ?) and (taskstat8 = 'Completed')) or ((taskto9 = ?) and (taskstat9 = 'Completed')) or ((taskto10 = ?) and (taskstat10 = 'Completed')) or ((taskto11 = ?) and (taskstat11 = 'Completed')) or ((taskto12 = ?) and (taskstat12 = 'Completed')) or ((taskto13 = ?) and (taskstat13 = 'Completed')) or ((taskto14 = ?) and (taskstat14 = 'Completed')) or ((taskto15 = ?) and (taskstat15 = 'Completed')) ) and (recur = '0')) and (taskfrom = '" + onlyUser + "')"; } PreparedStatement pstm = conn.prepareStatement(sql); pstm.setString(1, Username.getUserName()); pstm.setString(2, Username.getUserName()); pstm.setString(3, Username.getUserName()); pstm.setString(4, Username.getUserName()); pstm.setString(5, Username.getUserName()); pstm.setString(6, Username.getUserName()); pstm.setString(7, Username.getUserName()); pstm.setString(8, Username.getUserName()); pstm.setString(9, Username.getUserName()); pstm.setString(10, Username.getUserName()); pstm.setString(11, Username.getUserName()); pstm.setString(12, Username.getUserName()); pstm.setString(13, Username.getUserName()); pstm.setString(14, Username.getUserName()); pstm.setString(15, Username.getUserName()); ResultSet rs = pstm.executeQuery(); ArrayList<Tasks> list = new ArrayList<Tasks>(); while (rs.next()) { Date date1 = new Date(); Calendar cal1 = new GregorianCalendar(); if (rs.getTimestamp("revdate") == null) { cal1.setTime(rs.getTimestamp("startdate")); } else { cal1.setTime(rs.getTimestamp("revdate")); } String year1 = Integer.toString(cal1.get(Calendar.YEAR)); String month1 = Integer.toString(cal1.get(Calendar.MONTH) + 1); String day1 = Integer.toString(cal1.get(Calendar.DAY_OF_MONTH)); String hour1 = Integer.toString(cal1.get(Calendar.HOUR_OF_DAY)); String minute1 = Integer.toString(cal1.get(Calendar.MINUTE)); if (hour1.length() == 1) { hour1 = "0" + hour1; } if (minute1.length() == 1) { minute1 = "0" + minute1; } if (month1.length() == 1) { month1 = "0" + month1; } if (day1.length() == 1) { day1 = "0" + day1; } Date date2 = new Date(); Calendar cal2 = new GregorianCalendar(); cal2.setTime(rs.getTimestamp("enddate")); String year2 = Integer.toString(cal2.get(Calendar.YEAR)); String month2 = Integer.toString(cal2.get(Calendar.MONTH) + 1); String day2 = Integer.toString(cal2.get(Calendar.DAY_OF_MONTH)); String hour2 = Integer.toString(cal2.get(Calendar.HOUR_OF_DAY)); String minute2 = Integer.toString(cal2.get(Calendar.MINUTE)); if (hour2.length() == 1) { hour2 = "0" + hour2; } if (minute2.length() == 1) { minute2 = "0" + minute2; } if (month2.length() == 1) { month2 = "0" + month2; } if (day2.length() == 1) { day2 = "0" + day2; } Date date3 = new Date(); Calendar cal3 = new GregorianCalendar(); cal3.setTime(rs.getTimestamp("startdate")); String year3 = Integer.toString(cal3.get(Calendar.YEAR)); String month3 = Integer.toString(cal3.get(Calendar.MONTH) + 1); String day3 = Integer.toString(cal3.get(Calendar.DAY_OF_MONTH)); String hour3 = Integer.toString(cal3.get(Calendar.HOUR_OF_DAY)); String minute3 = Integer.toString(cal3.get(Calendar.MINUTE)); if (hour3.length() == 1) { hour3 = "0" + hour3; } if (minute3.length() == 1) { minute3 = "0" + minute3; } if (month3.length() == 1) { month3 = "0" + month3; } if (day3.length() == 1) { day3 = "0" + day3; } String tranid = rs.getString("tranid"); String taskfrom = rs.getString("taskfrom"); String description = rs.getString("description"); String revdate = year1 + "-" + month1 + "-" + day1 + " " + hour1 + ":" + minute1; String enddate = year2 + "-" + month2 + "-" + day2 + " " + hour2 + ":" + minute2; String taskstat; if (rs.getString("taskstat") == null) { taskstat = "Not Started"; } else { taskstat = rs.getString("taskstat"); } //String taskstat = rs.getString("taskstat"); String startdate = year3 + "-" + month3 + "-" + day3 + " " + hour3 + ":" + minute3; Tasks task = new Tasks(); task.setTranid(tranid); task.setTaskfrom(taskfrom); task.setDescription(description); task.setRevdate(revdate); task.setEnddate(enddate); task.setTaskstat(taskstat); task.setStartdate(startdate); String color1 = ""; if (date.after(rs.getTimestamp("revdate"))) { color1 = "red"; } if (color1 == "" && days3.after(rs.getTimestamp("revdate"))) { color1 = "orange"; } if (color1 == "") { color1 = "green"; } task.setlinkup1(color1); String color2 = "black"; if ((Username.equals(rs.getString("taskto1")) && (!"Y".equals(rs.getString("newt1")))) || (Username.equals(rs.getString("taskto2")) && (!"Y".equals(rs.getString("newt2")))) || (Username.equals(rs.getString("taskto3")) && (!"Y".equals(rs.getString("newt3")))) || (Username.equals(rs.getString("taskto4")) && (!"Y".equals(rs.getString("newt4")))) || (Username.equals(rs.getString("taskto5")) && (!"Y".equals(rs.getString("newt5")))) || (Username.equals(rs.getString("taskto6")) && (!"Y".equals(rs.getString("newt6")))) || (Username.equals(rs.getString("taskto7")) && (!"Y".equals(rs.getString("newt7")))) || (Username.equals(rs.getString("taskto8")) && (!"Y".equals(rs.getString("newt8")))) || (Username.equals(rs.getString("taskto9")) && (!"Y".equals(rs.getString("newt9")))) || (Username.equals(rs.getString("taskto10")) && (!"Y".equals(rs.getString("newt10")))) || (Username.equals(rs.getString("taskto11")) && (!"Y".equals(rs.getString("newt11")))) || (Username.equals(rs.getString("taskto12")) && (!"Y".equals(rs.getString("newt12")))) || (Username.equals(rs.getString("taskto13")) && (!"Y".equals(rs.getString("newt13")))) || (Username.equals(rs.getString("taskto14")) && (!"Y".equals(rs.getString("newt14")))) || (Username.equals(rs.getString("taskto15")) && (!"Y".equals(rs.getString("newt15"))))) { color2 = "green"; } task.setlinkup2(color2); list.add(task); } String sql1 = "select * from " + Username.getcompany() + ".tasks where (recur =1) and (((taskto1 = ?) and ((taskstat1 = 'Completed') or (recurdate1 > enddate))) or((taskto2 = ?) and ((taskstat2 = 'Completed') or (recurdate2 > enddate))) or((taskto3 = ?) and ((taskstat3 = 'Completed') or (recurdate3 > enddate))) or((taskto4 = ?) and ((taskstat4 = 'Completed') or (recurdate4 > enddate))) or ((taskto5 = ?) and ((taskstat5 = 'Completed') or (recurdate5 > enddate))) or ((taskto6 = ?) and ((taskstat6 = 'Completed') or (recurdate6 > enddate))) or ((taskto7 = ?) and ((taskstat7 = 'Completed') or (recurdate7 > enddate))) or ((taskto8 = ?) and ((taskstat8 = 'Completed') or (recurdate8 > enddate))) or((taskto9 = ?) and ((taskstat9 = 'Completed') or (recurdate9 > enddate))) or ((taskto11 = ?) and ((taskstat11 = 'Completed') or (recurdate11 > enddate))) or ((taskto12 = ?) and ((taskstat12 = 'Completed') or (recurdate12 > enddate))) or ((taskto13 = ?) and ((taskstat13 = 'Completed') or (recurdate13 > enddate))) or ((taskto14 = ?) and ((taskstat14 = 'Completed') or (recurdate14 > enddate))) or ((taskto15 = ?) and ((taskstat15 = 'Completed') or (recurdate15 > enddate))) or ((taskto10 = ?) and ((taskstat10 = 'Completed') or (recurdate10 > enddate))))"; PreparedStatement pstm1 = conn.prepareStatement(sql1); pstm1.setString(1, Username.getUserName()); pstm1.setString(2, Username.getUserName()); pstm1.setString(3, Username.getUserName()); pstm1.setString(4, Username.getUserName()); pstm1.setString(5, Username.getUserName()); pstm1.setString(6, Username.getUserName()); pstm1.setString(7, Username.getUserName()); pstm1.setString(8, Username.getUserName()); pstm1.setString(9, Username.getUserName()); pstm1.setString(10, Username.getUserName()); pstm1.setString(11, Username.getUserName()); pstm1.setString(12, Username.getUserName()); pstm1.setString(13, Username.getUserName()); pstm1.setString(14, Username.getUserName()); pstm1.setString(15, Username.getUserName()); ResultSet rs1 = pstm1.executeQuery(); while (rs1.next()) { Date date1 = new Date(); Calendar cal1 = new GregorianCalendar(); if (rs1.getTimestamp("revdate") == null) { cal1.setTime(rs1.getTimestamp("enddate")); } else { cal1.setTime(rs1.getTimestamp("revdate")); } // cal1.setTime(rs1.getTimestamp("revdate")); String year1 = Integer.toString(cal1.get(Calendar.YEAR)); String month1 = Integer.toString(cal1.get(Calendar.MONTH) + 1); String day1 = Integer.toString(cal1.get(Calendar.DAY_OF_MONTH)); String hour1 = Integer.toString(cal1.get(Calendar.HOUR_OF_DAY)); String minute1 = Integer.toString(cal1.get(Calendar.MINUTE)); if (hour1.length() == 1) { hour1 = "0" + hour1; } if (minute1.length() == 1) { minute1 = "0" + minute1; } if (month1.length() == 1) { month1 = "0" + month1; } if (day1.length() == 1) { day1 = "0" + day1; } Date date2 = new Date(); Calendar cal2 = new GregorianCalendar(); cal2.setTime(rs1.getTimestamp("enddate")); String year2 = Integer.toString(cal2.get(Calendar.YEAR)); String month2 = Integer.toString(cal2.get(Calendar.MONTH) + 1); String day2 = Integer.toString(cal2.get(Calendar.DAY_OF_MONTH)); String hour2 = Integer.toString(cal2.get(Calendar.HOUR_OF_DAY)); String minute2 = Integer.toString(cal2.get(Calendar.MINUTE)); if (hour2.length() == 1) { hour2 = "0" + hour2; } if (minute2.length() == 1) { minute2 = "0" + minute2; } if (month2.length() == 1) { month2 = "0" + month2; } if (day2.length() == 1) { day2 = "0" + day2; } Date date3 = new Date(); Calendar cal3 = new GregorianCalendar(); cal3.setTime(rs1.getTimestamp("startdate")); String year3 = Integer.toString(cal3.get(Calendar.YEAR)); String month3 = Integer.toString(cal3.get(Calendar.MONTH) + 1); String day3 = Integer.toString(cal3.get(Calendar.DAY_OF_MONTH)); String hour3 = Integer.toString(cal3.get(Calendar.HOUR_OF_DAY)); String minute3 = Integer.toString(cal3.get(Calendar.MINUTE)); if (hour3.length() == 1) { hour3 = "0" + hour3; } if (minute3.length() == 1) { minute3 = "0" + minute3; } if (month3.length() == 1) { month3 = "0" + month3; } if (day3.length() == 1) { day3 = "0" + day3; } String tranid = rs1.getString("tranid"); String taskfrom = rs1.getString("taskfrom"); String description = rs1.getString("description"); String revdate = year1 + "-" + month1 + "-" + day1 + " " + hour1 + ":" + minute1; String enddate = year2 + "-" + month2 + "-" + day2 + " " + hour2 + ":" + minute2; String taskstat; if (rs1.getString("taskstat") == null) { taskstat = "Not Started"; } else { taskstat = rs1.getString("taskstat"); } //String taskstat = rs1.getString("taskstat"); String startdate = year3 + "-" + month3 + "-" + day3 + " " + hour3 + ":" + minute3; Tasks task = new Tasks(); task.setTranid(tranid); task.setTaskfrom(taskfrom); task.setDescription(description); task.setRevdate(revdate); task.setEnddate(enddate); task.setTaskstat(taskstat); task.setStartdate(startdate); String color1 = ""; if (date.after(rs.getTimestamp("revdate"))) { color1 = "red"; } if (color1 == "" && days3.after(rs.getTimestamp("revdate"))) { color1 = "orange"; } if (color1 == "") { color1 = "green"; } task.setlinkup1(color1); String color2 = "black"; if ((Username.equals(rs.getString("taskto1")) && (!"Y".equals(rs.getString("newt1")))) || (Username.equals(rs.getString("taskto2")) && (!"Y".equals(rs.getString("newt2")))) || (Username.equals(rs.getString("taskto3")) && (!"Y".equals(rs.getString("newt3")))) || (Username.equals(rs.getString("taskto4")) && (!"Y".equals(rs.getString("newt4")))) || (Username.equals(rs.getString("taskto5")) && (!"Y".equals(rs.getString("newt5")))) || (Username.equals(rs.getString("taskto6")) && (!"Y".equals(rs.getString("newt6")))) || (Username.equals(rs.getString("taskto7")) && (!"Y".equals(rs.getString("newt7")))) || (Username.equals(rs.getString("taskto8")) && (!"Y".equals(rs.getString("newt8")))) || (Username.equals(rs.getString("taskto9")) && (!"Y".equals(rs.getString("newt9")))) || (Username.equals(rs.getString("taskto10")) && (!"Y".equals(rs.getString("newt10")))) || (Username.equals(rs.getString("taskto11")) && (!"Y".equals(rs.getString("newt11")))) || (Username.equals(rs.getString("taskto12")) && (!"Y".equals(rs.getString("newt12")))) || (Username.equals(rs.getString("taskto13")) && (!"Y".equals(rs.getString("newt13")))) || (Username.equals(rs.getString("taskto14")) && (!"Y".equals(rs.getString("newt14")))) || (Username.equals(rs.getString("taskto15")) && (!"Y".equals(rs.getString("newt15"))))) { color2 = "green"; } task.setlinkup2(color2); list.add(task); } return list; }
From source file:ffsutils.TaskUtils.java
public static ArrayList<Tasks> getUrgentTasks(Connection conn, UserAccount Username, String onlyUser) throws SQLException { System.out.println("getUrgentTasks " + Username); Date date = new Date(); Calendar cal = Calendar.getInstance(); GregorianCalendar cal121 = new GregorianCalendar(); cal121.setTime(date);/*from www . j a v a 2 s . c o m*/ cal121.add(Calendar.DATE, 3); Date days3 = cal121.getTime(); String username = Username.getUserName(); cal.add(Calendar.DATE, 3); String year = Integer.toString(cal.get(Calendar.YEAR)); String month = Integer.toString(cal.get(Calendar.MONTH) + 1); String day = Integer.toString(cal.get(Calendar.DAY_OF_MONTH)); String hour = Integer.toString(cal.get(Calendar.HOUR_OF_DAY)); String minute = Integer.toString(cal.get(Calendar.MINUTE)); Date date1 = new Date(); Calendar cal1 = Calendar.getInstance(); cal1.add(Calendar.DATE, 4); String year1 = Integer.toString(cal1.get(Calendar.YEAR)); String month1 = Integer.toString(cal1.get(Calendar.MONTH) + 1); String day1 = Integer.toString(cal1.get(Calendar.DAY_OF_MONTH)); String hour1 = Integer.toString(cal1.get(Calendar.HOUR_OF_DAY)); String minute1 = Integer.toString(cal1.get(Calendar.MINUTE)); String sql = ""; if (onlyUser == null) { sql = "select * from " + Username.getcompany() + ".tasks where ((taskfrom = '" + username + "' ) or (taskto1 = '" + username + "') or (taskto2 = '" + username + "') or (taskto3 = '" + username + "') or (taskto4 = '" + username + "') or (taskto5 = '" + username + "') or (taskto6 = '" + username + "') or (taskto7 = '" + username + "') or (taskto8 = '" + username + "') or (taskto9 = '" + username + "') or (taskto10 = '" + username + "') or (taskto11 = '" + username + "') or (taskto12 = '" + username + "') or (taskto13 = '" + username + "') or (taskto14 = '" + username + "') or (taskto15 = '" + username + "'))and (taskstat <> 'Completed') and (revdate > '" + year + "/" + month + "/" + day + " 00:01') and (revdate < '" + year1 + "/" + month1 + "/" + day1 + " 23:59')"; } else { if (onlyUser.length() < 3) { sql = "select * from " + Username.getcompany() + ".tasks where ((taskfrom = '" + username + "' ) or (taskto1 = '" + username + "') or (taskto2 = '" + username + "') or (taskto3 = '" + username + "') or (taskto4 = '" + username + "') or (taskto5 = '" + username + "') or (taskto6 = '" + username + "') or (taskto7 = '" + username + "') or (taskto8 = '" + username + "') or (taskto9 = '" + username + "') or (taskto10 = '" + username + "') or (taskto11 = '" + username + "') or (taskto12 = '" + username + "') or (taskto13 = '" + username + "') or (taskto14 = '" + username + "') or (taskto15 = '" + username + "'))and (taskstat <> 'Completed') and (revdate > '" + year + "/" + month + "/" + day + " 00:01') and (revdate < '" + year1 + "/" + month1 + "/" + day1 + " 23:59')"; } else { sql = "select * from " + Username.getcompany() + ".tasks where (taskfrom = '" + onlyUser + "' ) and ((taskto1 = '" + username + "') or (taskto2 = '" + username + "') or (taskto3 = '" + username + "') or (taskto4 = '" + username + "') or (taskto5 = '" + username + "') or (taskto6 = '" + username + "') or (taskto7 = '" + username + "') or (taskto8 = '" + username + "') or (taskto9 = '" + username + "') or (taskto10 = '" + username + "') or (taskto11 = '" + username + "') or (taskto12 = '" + username + "') or (taskto13 = '" + username + "') or (taskto14 = '" + username + "') or (taskto15 = '" + username + "')) and (taskstat <> 'Completed') and (revdate > '" + year + "/" + month + "/" + day + " 00:01') and (revdate < '" + year1 + "/" + month1 + "/" + day1 + " 23:59')"; } } PreparedStatement pstm = conn.prepareStatement(sql); // pstm.setString(1, Username); ResultSet rs = pstm.executeQuery(); ArrayList<Tasks> list = new ArrayList<Tasks>(); while (rs.next()) { Date date10 = new Date(); Calendar cal10 = new GregorianCalendar(); if (rs.getTimestamp("revdate") == null) { cal1.setTime(rs.getTimestamp("enddate")); } else { cal1.setTime(rs.getTimestamp("revdate")); } String year10 = Integer.toString(cal10.get(Calendar.YEAR)); String month10 = Integer.toString(cal10.get(Calendar.MONTH) + 1); String day10 = Integer.toString(cal10.get(Calendar.DAY_OF_MONTH)); String hour10 = Integer.toString(cal10.get(Calendar.HOUR_OF_DAY)); String minute10 = Integer.toString(cal10.get(Calendar.MINUTE)); if (hour10.length() == 1) { hour10 = "0" + hour10; } if (minute10.length() == 1) { minute10 = "0" + minute10; } if (month10.length() == 1) { month10 = "0" + month10; } if (day10.length() == 1) { day10 = "0" + day10; } Date date2 = new Date(); Calendar cal2 = new GregorianCalendar(); cal2.setTime(rs.getTimestamp("enddate")); String year2 = Integer.toString(cal2.get(Calendar.YEAR)); String month2 = Integer.toString(cal2.get(Calendar.MONTH) + 1); String day2 = Integer.toString(cal2.get(Calendar.DAY_OF_MONTH)); String hour2 = Integer.toString(cal2.get(Calendar.HOUR_OF_DAY)); String minute2 = Integer.toString(cal2.get(Calendar.MINUTE)); if (hour2.length() == 1) { hour2 = "0" + hour2; } if (minute2.length() == 1) { minute2 = "0" + minute2; } if (month2.length() == 1) { month2 = "0" + month2; } if (day2.length() == 1) { day2 = "0" + day2; } Date date3 = new Date(); Calendar cal3 = new GregorianCalendar(); cal3.setTime(rs.getTimestamp("startdate")); String year3 = Integer.toString(cal3.get(Calendar.YEAR)); String month3 = Integer.toString(cal3.get(Calendar.MONTH) + 1); String day3 = Integer.toString(cal3.get(Calendar.DAY_OF_MONTH)); String hour3 = Integer.toString(cal3.get(Calendar.HOUR_OF_DAY)); String minute3 = Integer.toString(cal3.get(Calendar.MINUTE)); if (hour3.length() == 1) { hour3 = "0" + hour3; } if (minute3.length() == 1) { minute3 = "0" + minute3; } if (month3.length() == 1) { month3 = "0" + month3; } if (day3.length() == 1) { day3 = "0" + day3; } String tranid = rs.getString("tranid"); String taskfrom = rs.getString("taskfrom"); String description = rs.getString("description"); String revdate = year1 + "-" + month1 + "-" + day1 + " " + hour1 + ":" + minute1; String enddate = year2 + "-" + month2 + "-" + day2 + " " + hour2 + ":" + minute2; String taskstat; if (rs.getString("taskstat") == null) { taskstat = "Not Started"; } else { taskstat = rs.getString("taskstat"); } //String taskstat = rs.getString("taskstat"); String startdate = year3 + "-" + month3 + "-" + day3 + " " + hour3 + ":" + minute3; Tasks task = new Tasks(); task.setTranid(tranid); task.setTaskfrom(taskfrom); task.setDescription(description); task.setRevdate(revdate); task.setEnddate(enddate); task.setTaskstat(taskstat); task.setStartdate(startdate); String color1 = ""; if (date.after(rs.getTimestamp("revdate"))) { color1 = "red"; } if (color1 == "" && days3.after(rs.getTimestamp("revdate"))) { color1 = "orange"; } if (color1 == "") { color1 = "green"; } task.setlinkup1(color1); String color2 = "black"; if ((Username.equals(rs.getString("taskto1")) && (!"Y".equals(rs.getString("newt1")))) || (Username.equals(rs.getString("taskto2")) && (!"Y".equals(rs.getString("newt2")))) || (Username.equals(rs.getString("taskto3")) && (!"Y".equals(rs.getString("newt3")))) || (Username.equals(rs.getString("taskto4")) && (!"Y".equals(rs.getString("newt4")))) || (Username.equals(rs.getString("taskto5")) && (!"Y".equals(rs.getString("newt5")))) || (Username.equals(rs.getString("taskto6")) && (!"Y".equals(rs.getString("newt6")))) || (Username.equals(rs.getString("taskto7")) && (!"Y".equals(rs.getString("newt7")))) || (Username.equals(rs.getString("taskto8")) && (!"Y".equals(rs.getString("newt8")))) || (Username.equals(rs.getString("taskto9")) && (!"Y".equals(rs.getString("newt9")))) || (Username.equals(rs.getString("taskto10")) && (!"Y".equals(rs.getString("newt10")))) || (Username.equals(rs.getString("taskto11")) && (!"Y".equals(rs.getString("newt11")))) || (Username.equals(rs.getString("taskto12")) && (!"Y".equals(rs.getString("newt12")))) || (Username.equals(rs.getString("taskto13")) && (!"Y".equals(rs.getString("newt13")))) || (Username.equals(rs.getString("taskto14")) && (!"Y".equals(rs.getString("newt14")))) || (Username.equals(rs.getString("taskto15")) && (!"Y".equals(rs.getString("newt15"))))) { color2 = "green"; } task.setlinkup2(color2); list.add(task); } String sql1 = ""; if (onlyUser == null) { sql1 = "select * from " + Username.getcompany() + ".tasks where (((taskto1 = ?) and ((taskstat1 <> 'Completed') or (taskstat1 is null))) or ((taskto2 = ?) and ((taskstat2 <> 'Completed') or (taskstat2 is null))) or ((taskto3 = ?) and ((taskstat3 <> 'Completed') or (taskstat3 is null))) or ((taskto4 = ?) and ((taskstat4 <> 'Completed') or (taskstat4 is null))) or ((taskto5 = ?) and ((taskstat5 <> 'Completed') or (taskstat5 is null))) or ((taskto6 = ?) and ((taskstat6 <> 'Completed') or (taskstat6 is null))) or ((taskto7 = ?) and ((taskstat7 <> 'Completed') or (taskstat7 is null))) or ((taskto8 = ?) and ((taskstat8 <> 'Completed') or (taskstat8 is null))) or ((taskto9 = ?) and ((taskstat9 <> 'Completed') or (taskstat9 is null))) or ((taskto10 = ?) and ((taskstat10 <> 'Completed') or (taskstat10 is null))) or((taskto11 = ?) and ((taskstat11 <> 'Completed') or (taskstat11 is null))) or ((taskto12 = ?) and ((taskstat12 <> 'Completed') or (taskstat12 is null))) or ((taskto13 = ?) and ((taskstat13 <> 'Completed') or (taskstat13 is null))) or ((taskto14 = ?) and ((taskstat14 <> 'Completed') or (taskstat14 is null))) or ((taskto15 = ?) and ((taskstat15 <> 'Completed') or (taskstat15 is null)))) and (taskstat <> 'Completed') and (revdate > '" + year + "/" + month + "/" + day + " 00:01') and (revdate < '" + year1 + "/" + month1 + "/" + day1 + " 23:59')"; } else { if (onlyUser.length() < 3) { sql1 = "select * from " + Username.getcompany() + ".tasks where (((taskto1 = ?) and ((taskstat1 <> 'Completed') or (taskstat1 is null))) or ((taskto2 = ?) and ((taskstat2 <> 'Completed') or (taskstat2 is null))) or ((taskto3 = ?) and ((taskstat3 <> 'Completed') or (taskstat3 is null))) or ((taskto4 = ?) and ((taskstat4 <> 'Completed') or (taskstat4 is null))) or ((taskto5 = ?) and ((taskstat5 <> 'Completed') or (taskstat5 is null))) or ((taskto6 = ?) and ((taskstat6 <> 'Completed') or (taskstat6 is null))) or ((taskto7 = ?) and ((taskstat7 <> 'Completed') or (taskstat7 is null))) or ((taskto8 = ?) and ((taskstat8 <> 'Completed') or (taskstat8 is null))) or ((taskto9 = ?) and ((taskstat9 <> 'Completed') or (taskstat9 is null))) or ((taskto10 = ?) and ((taskstat10 <> 'Completed') or (taskstat10 is null))) or((taskto11 = ?) and ((taskstat11 <> 'Completed') or (taskstat11 is null))) or ((taskto12 = ?) and ((taskstat12 <> 'Completed') or (taskstat12 is null))) or ((taskto13 = ?) and ((taskstat13 <> 'Completed') or (taskstat13 is null))) or ((taskto14 = ?) and ((taskstat14 <> 'Completed') or (taskstat14 is null))) or ((taskto15 = ?) and ((taskstat15 <> 'Completed') or (taskstat15 is null)))) and (taskstat <> 'Completed') and (revdate > '" + year + "/" + month + "/" + day + " 00:01') and (revdate < '" + year1 + "/" + month1 + "/" + day1 + " 23:59')"; } else { sql1 = "select * from " + Username.getcompany() + ".tasks where (((taskto1 = ?) and ((taskstat1 <> 'Completed') or (taskstat1 is null))) or ((taskto2 = ?) and ((taskstat2 <> 'Completed') or (taskstat2 is null))) or ((taskto3 = ?) and ((taskstat3 <> 'Completed') or (taskstat3 is null))) or ((taskto4 = ?) and ((taskstat4 <> 'Completed') or (taskstat4 is null))) or ((taskto5 = ?) and ((taskstat5 <> 'Completed') or (taskstat5 is null))) or ((taskto6 = ?) and ((taskstat6 <> 'Completed') or (taskstat6 is null))) or ((taskto7 = ?) and ((taskstat7 <> 'Completed') or (taskstat7 is null))) or ((taskto8 = ?) and ((taskstat8 <> 'Completed') or (taskstat8 is null))) or ((taskto9 = ?) and ((taskstat9 <> 'Completed') or (taskstat9 is null))) or ((taskto10 = ?) and ((taskstat10 <> 'Completed') or (taskstat10 is null))) or((taskto11 = ?) and ((taskstat11 <> 'Completed') or (taskstat11 is null))) or ((taskto12 = ?) and ((taskstat12 <> 'Completed') or (taskstat12 is null))) or ((taskto13 = ?) and ((taskstat13 <> 'Completed') or(taskstat13 is null))) or ((taskto14 = ?) and ((taskstat14 <> 'Completed') or (taskstat14 is null))) or ((taskto15 = ?) and ((taskstat15 <> 'Completed') or (taskstat15 is null)))) and (taskstat <> 'Completed') and (revdate > '" + year + "/" + month + "/" + day + " 00:01') and (revdate < '" + year1 + "/" + month1 + "/" + day1 + " 23:59') and (taskfrom = '" + onlyUser + "')"; } } PreparedStatement pstm1 = conn.prepareStatement(sql1); pstm1.setString(1, Username.getUserName()); pstm1.setString(2, Username.getUserName()); pstm1.setString(3, Username.getUserName()); pstm1.setString(4, Username.getUserName()); pstm1.setString(5, Username.getUserName()); pstm1.setString(6, Username.getUserName()); pstm1.setString(7, Username.getUserName()); pstm1.setString(8, Username.getUserName()); pstm1.setString(9, Username.getUserName()); pstm1.setString(10, Username.getUserName()); pstm1.setString(11, Username.getUserName()); pstm1.setString(12, Username.getUserName()); pstm1.setString(13, Username.getUserName()); pstm1.setString(14, Username.getUserName()); pstm1.setString(15, Username.getUserName()); ResultSet rs1 = pstm1.executeQuery(); while (rs1.next()) { Date date11 = new Date(); Calendar cal11 = new GregorianCalendar(); if (rs1.getTimestamp("revdate") == null) { cal1.setTime(rs.getTimestamp("enddate")); } else { cal1.setTime(rs1.getTimestamp("revdate")); } // cal1.setTime(rs1.getTimestamp("revdate")); String year11 = Integer.toString(cal11.get(Calendar.YEAR)); String month11 = Integer.toString(cal11.get(Calendar.MONTH) + 1); String day11 = Integer.toString(cal11.get(Calendar.DAY_OF_MONTH)); String hour11 = Integer.toString(cal11.get(Calendar.HOUR_OF_DAY)); String minute11 = Integer.toString(cal11.get(Calendar.MINUTE)); if (hour11.length() == 1) { hour11 = "0" + hour11; } if (minute11.length() == 1) { minute11 = "0" + minute11; } if (month11.length() == 1) { month11 = "0" + month11; } if (day11.length() == 1) { day11 = "0" + day11; } Date date2 = new Date(); Calendar cal2 = new GregorianCalendar(); cal2.setTime(rs1.getTimestamp("enddate")); String year2 = Integer.toString(cal2.get(Calendar.YEAR)); String month2 = Integer.toString(cal2.get(Calendar.MONTH) + 1); String day2 = Integer.toString(cal2.get(Calendar.DAY_OF_MONTH)); String hour2 = Integer.toString(cal2.get(Calendar.HOUR_OF_DAY)); String minute2 = Integer.toString(cal2.get(Calendar.MINUTE)); if (hour2.length() == 1) { hour2 = "0" + hour2; } if (minute2.length() == 1) { minute2 = "0" + minute2; } if (month2.length() == 1) { month2 = "0" + month2; } if (day2.length() == 1) { day2 = "0" + day2; } Date date3 = new Date(); Calendar cal3 = new GregorianCalendar(); cal3.setTime(rs1.getTimestamp("startdate")); String year3 = Integer.toString(cal3.get(Calendar.YEAR)); String month3 = Integer.toString(cal3.get(Calendar.MONTH) + 1); String day3 = Integer.toString(cal3.get(Calendar.DAY_OF_MONTH)); String hour3 = Integer.toString(cal3.get(Calendar.HOUR_OF_DAY)); String minute3 = Integer.toString(cal3.get(Calendar.MINUTE)); if (hour3.length() == 1) { hour3 = "0" + hour3; } if (minute3.length() == 1) { minute3 = "0" + minute3; } if (month3.length() == 1) { month3 = "0" + month3; } if (day3.length() == 1) { day3 = "0" + day3; } String tranid = rs1.getString("tranid"); String taskfrom = rs1.getString("taskfrom"); String description = rs1.getString("description"); String revdate = year1 + "-" + month1 + "-" + day1 + " " + hour1 + ":" + minute1; String enddate = year2 + "-" + month2 + "-" + day2 + " " + hour2 + ":" + minute2; String taskstat; if (rs1.getString("taskstat") == null) { taskstat = "Not Started"; } else { taskstat = rs1.getString("taskstat"); } //String taskstat = rs1.getString("taskstat"); String startdate = year3 + "-" + month3 + "-" + day3 + " " + hour3 + ":" + minute3; Tasks task = new Tasks(); task.setTranid(tranid); task.setTaskfrom(taskfrom); task.setDescription(description); task.setRevdate(revdate); task.setEnddate(enddate); task.setTaskstat(taskstat); task.setStartdate(startdate); String color1 = ""; if (date.after(rs1.getTimestamp("revdate"))) { color1 = "red"; } if (color1 == "" && days3.after(rs1.getTimestamp("revdate"))) { color1 = "orange"; } if (color1 == "") { color1 = "green"; } task.setlinkup1(color1); String color2 = "black"; if ((Username.equals(rs1.getString("taskto1")) && (!"Y".equals(rs1.getString("newt1")))) || (Username.equals(rs1.getString("taskto2")) && (!"Y".equals(rs1.getString("newt2")))) || (Username.equals(rs1.getString("taskto3")) && (!"Y".equals(rs1.getString("newt3")))) || (Username.equals(rs1.getString("taskto4")) && (!"Y".equals(rs1.getString("newt4")))) || (Username.equals(rs1.getString("taskto5")) && (!"Y".equals(rs1.getString("newt5")))) || (Username.equals(rs1.getString("taskto6")) && (!"Y".equals(rs1.getString("newt6")))) || (Username.equals(rs1.getString("taskto7")) && (!"Y".equals(rs1.getString("newt7")))) || (Username.equals(rs1.getString("taskto8")) && (!"Y".equals(rs1.getString("newt8")))) || (Username.equals(rs1.getString("taskto9")) && (!"Y".equals(rs1.getString("newt9")))) || (Username.equals(rs1.getString("taskto10")) && (!"Y".equals(rs1.getString("newt10")))) || (Username.equals(rs1.getString("taskto11")) && (!"Y".equals(rs1.getString("newt11")))) || (Username.equals(rs1.getString("taskto12")) && (!"Y".equals(rs1.getString("newt12")))) || (Username.equals(rs1.getString("taskto13")) && (!"Y".equals(rs1.getString("newt13")))) || (Username.equals(rs1.getString("taskto14")) && (!"Y".equals(rs1.getString("newt14")))) || (Username.equals(rs1.getString("taskto15")) && (!"Y".equals(rs1.getString("newt15"))))) { color2 = "green"; } task.setlinkup2(color2); list.add(task); } return list; }
From source file:ffsutils.TaskUtils.java
public static ArrayList<Tasks> getTask(Connection conn, UserAccount Username, String onlyUser) throws SQLException { System.out.println("getTask " + Username.getUserName() + " " + onlyUser); Date date = new Date(); Calendar cal = Calendar.getInstance(); Calendar cal7 = Calendar.getInstance(); GregorianCalendar cal121 = new GregorianCalendar(); cal121.setTime(date);/*from ww w .j a v a 2s . c o m*/ cal121.add(Calendar.DATE, 3); Date days3 = cal121.getTime(); String year = Integer.toString(cal.get(Calendar.YEAR)); String month = Integer.toString(cal.get(Calendar.MONTH) + 1); String day = Integer.toString(cal.get(Calendar.DAY_OF_MONTH)); String hour = Integer.toString(cal.get(Calendar.HOUR_OF_DAY)); String minute = Integer.toString(cal.get(Calendar.MINUTE)); String sql = ""; if (onlyUser == null) { onlyUser = ""; } if (onlyUser.equals(null) | onlyUser.length() < 4) { sql = "select * from " + Username.getcompany() + ".tasks where (recur = '0') and (startdate < '" + year + "/" + month + "/" + day + " 23:59') and ((taskstat <> 'Completed') or (taskstat is NULL)) and (((taskto1 = ? ) and ((taskstat1 <> 'Completed') or (taskstat1 is NULL))) or ((taskto2 = ?) and ((taskstat2 <> 'Completed') or (taskstat2 is NULL))) or ((taskto3 = ?) and ((taskstat3 <> 'Completed') or (taskstat3 is NULL))) or ((taskto4 = ?) and ((taskstat4 <> 'Completed') or (taskstat4 is NULL))) or ((taskto5 = ?) and ((taskstat5 <> 'Completed') or (taskstat5 is NULL))) or ((taskto6 = ?) and ((taskstat6 <> 'Completed') or (taskstat6 is NULL))) or ((taskto7 = ?) and ((taskstat7 <> 'Completed') or (taskstat7 is NULL))) or ((taskto8 = ?) and ((taskstat8 <> 'Completed') or (taskstat8 is NULL))) or ((taskto9 = ?) and ((taskstat9 <> 'Completed') or (taskstat9 is NULL))) or ((taskto10 = ?) and ((taskstat10 <> 'Completed') or (taskstat10 is NULL))) or ((taskto11 = ?) and ((taskstat11 <> 'Completed') or (taskstat11 is NULL))) or ((taskto12 = ?) and ((taskstat12 <> 'Completed') or (taskstat12 is NULL))) or ((taskto13 = ?) and ((taskstat13 <> 'Completed') or (taskstat13 is NULL))) or ((taskto14 = ?) and ((taskstat14 <> 'Completed') or (taskstat14 is NULL))) or ((taskto15 = ?) and ((taskstat15 <> 'Completed') or (taskstat15 is NULL)))) "; } else { sql = "select * from " + Username.getcompany() + ".tasks where (recur = '0') and (startdate < '" + year + "/" + month + "/" + day + " 23:59') and ((taskstat <> 'Completed') or (taskstat is NULL)) and (((taskto1 = ? ) and ((taskstat1 <> 'Completed') or (taskstat1 is NULL))) or ((taskto2 = ?) and ((taskstat2 <> 'Completed') or (taskstat2 is NULL))) or ((taskto3 = ?) and ((taskstat3 <> 'Completed') or (taskstat3 is NULL))) or ((taskto4 = ?) and ((taskstat4 <> 'Completed') or (taskstat4 is NULL))) or ((taskto5 = ?) and ((taskstat5 <> 'Completed') or (taskstat5 is NULL))) or ((taskto6 = ?) and ((taskstat6 <> 'Completed') or (taskstat6 is NULL))) or ((taskto7 = ?) and ((taskstat7 <> 'Completed') or (taskstat7 is NULL))) or ((taskto8 = ?) and ((taskstat8 <> 'Completed') or (taskstat8 is NULL))) or ((taskto9 = ?) and ((taskstat9 <> 'Completed') or (taskstat9 is NULL))) or ((taskto10 = ?) and ((taskstat10 <> 'Completed') or (taskstat10 is NULL))) or ((taskto11 = ?) and ((taskstat11 <> 'Completed') or (taskstat11 is NULL))) or ((taskto12 = ?) and ((taskstat12 <> 'Completed') or (taskstat12 is NULL))) or ((taskto13 = ?) and ((taskstat13 <> 'Completed') or (taskstat13 is NULL))) or ((taskto14 = ?) and ((taskstat14 <> 'Completed') or (taskstat14 is NULL))) or ((taskto15 = ?) and ((taskstat15 <> 'Completed') or (taskstat15 is NULL)))) and (taskfrom = '" + onlyUser + "')"; } PreparedStatement pstm = conn.prepareStatement(sql); pstm.setString(1, Username.getUserName()); pstm.setString(2, Username.getUserName()); pstm.setString(3, Username.getUserName()); pstm.setString(4, Username.getUserName()); pstm.setString(5, Username.getUserName()); pstm.setString(6, Username.getUserName()); pstm.setString(7, Username.getUserName()); pstm.setString(8, Username.getUserName()); pstm.setString(9, Username.getUserName()); pstm.setString(10, Username.getUserName()); pstm.setString(11, Username.getUserName()); pstm.setString(12, Username.getUserName()); pstm.setString(13, Username.getUserName()); pstm.setString(14, Username.getUserName()); pstm.setString(15, Username.getUserName()); ResultSet rs = pstm.executeQuery(); ArrayList<Tasks> list = new ArrayList<Tasks>(); while (rs.next()) { Date date1 = new Date(); Calendar cal1 = new GregorianCalendar(); if (rs.getTimestamp("revdate") == null) { cal1.setTime(rs.getTimestamp("enddate")); } else { cal1.setTime(rs.getTimestamp("revdate")); } // cal1.setTime(rs.getTimestamp("revdate")); String year1 = Integer.toString(cal1.get(Calendar.YEAR)); String month1 = Integer.toString(cal1.get(Calendar.MONTH) + 1); String day1 = Integer.toString(cal1.get(Calendar.DAY_OF_MONTH)); String hour1 = Integer.toString(cal1.get(Calendar.HOUR_OF_DAY)); String minute1 = Integer.toString(cal1.get(Calendar.MINUTE)); if (hour1.length() == 1) { hour1 = "0" + hour1; } if (minute1.length() == 1) { minute1 = "0" + minute1; } if (month1.length() == 1) { month1 = "0" + month1; } if (day1.length() == 1) { day1 = "0" + day1; } Date date2 = new Date(); Calendar cal2 = new GregorianCalendar(); cal2.setTime(rs.getTimestamp("enddate")); String year2 = Integer.toString(cal2.get(Calendar.YEAR)); String month2 = Integer.toString(cal2.get(Calendar.MONTH) + 1); String day2 = Integer.toString(cal2.get(Calendar.DAY_OF_MONTH)); String hour2 = Integer.toString(cal2.get(Calendar.HOUR_OF_DAY)); String minute2 = Integer.toString(cal2.get(Calendar.MINUTE)); if (hour2.length() == 1) { hour2 = "0" + hour2; } if (minute2.length() == 1) { minute2 = "0" + minute2; } if (month2.length() == 1) { month2 = "0" + month2; } if (day2.length() == 1) { day2 = "0" + day2; } Date date3 = new Date(); Calendar cal3 = new GregorianCalendar(); cal3.setTime(rs.getTimestamp("startdate")); String year3 = Integer.toString(cal3.get(Calendar.YEAR)); String month3 = Integer.toString(cal3.get(Calendar.MONTH) + 1); String day3 = Integer.toString(cal3.get(Calendar.DAY_OF_MONTH)); String hour3 = Integer.toString(cal3.get(Calendar.HOUR_OF_DAY)); String minute3 = Integer.toString(cal3.get(Calendar.MINUTE)); if (hour3.length() == 1) { hour3 = "0" + hour3; } if (minute3.length() == 1) { minute3 = "0" + minute3; } if (month3.length() == 1) { month3 = "0" + month3; } if (day3.length() == 1) { day3 = "0" + day3; } String tranid = rs.getString("tranid"); String taskfrom = rs.getString("taskfrom"); String description = rs.getString("description"); String revdate = year1 + "-" + month1 + "-" + day1 + " " + hour1 + ":" + minute1; String taskstat; if (rs.getString("taskstat") == null) { taskstat = "Not Started"; } else { taskstat = rs.getString("taskstat"); } String enddate = year2 + "-" + month2 + "-" + day2 + " " + hour2 + ":" + minute2; //String taskstat = rs.getString("taskstat"); String startdate = year3 + "-" + month3 + "-" + day3 + " " + hour3 + ":" + minute3; Tasks task = new Tasks(); task.setTranid(tranid); task.setTaskfrom(taskfrom); task.setDescription(description); task.setRevdate(revdate); task.setEnddate(enddate); task.setTaskstat(taskstat); task.setStartdate(startdate); String color1 = ""; String color2 = "black"; if (rs.getTimestamp("revdate") == null) { cal7.setTime(rs.getTimestamp("enddate")); } else { cal7.setTime(rs.getTimestamp("revdate")); } if (date.after(cal7.getTime())) { color1 = "red"; } if (color1 == "" && days3.after(cal7.getTime())) { color1 = "orange"; } // if (date.after(rs.getTimestamp("revdate"))) { // color1 = "red"; // } // if (color1 == "" && days3.after(rs.getTimestamp("revdate"))) { // color1 = "orange"; // } if (color1 == "") { color1 = "green"; } task.setlinkup1(color1); // System.out.println("getTask " + color1 ); if ((Username.equals(rs.getString("taskto1")) && (!"Y".equals(rs.getString("newt1")))) || (Username.equals(rs.getString("taskto2")) && (!"Y".equals(rs.getString("newt2")))) || (Username.equals(rs.getString("taskto3")) && (!"Y".equals(rs.getString("newt3")))) || (Username.equals(rs.getString("taskto4")) && (!"Y".equals(rs.getString("newt4")))) || (Username.equals(rs.getString("taskto5")) && (!"Y".equals(rs.getString("newt5")))) || (Username.equals(rs.getString("taskto6")) && (!"Y".equals(rs.getString("newt6")))) || (Username.equals(rs.getString("taskto7")) && (!"Y".equals(rs.getString("newt7")))) || (Username.equals(rs.getString("taskto8")) && (!"Y".equals(rs.getString("newt8")))) || (Username.equals(rs.getString("taskto9")) && (!"Y".equals(rs.getString("newt9")))) || (Username.equals(rs.getString("taskto10")) && (!"Y".equals(rs.getString("newt10")))) || (Username.equals(rs.getString("taskto11")) && (!"Y".equals(rs.getString("newt11")))) || (Username.equals(rs.getString("taskto12")) && (!"Y".equals(rs.getString("newt12")))) || (Username.equals(rs.getString("taskto13")) && (!"Y".equals(rs.getString("newt13")))) || (Username.equals(rs.getString("taskto14")) && (!"Y".equals(rs.getString("newt14")))) || (Username.equals(rs.getString("taskto15")) && (!"Y".equals(rs.getString("newt15"))))) { color2 = "green"; } task.setlinkup2(color2); list.add(task); } String sql1 = "select * from " + Username.getcompany() + ".tasks where (recur =1) and ((taskstat <> 'Completed') or (taskstat is NULL)) and (startdate <= '" + year + "/" + month + "/" + day + " " + hour + ":" + minute + ":00') and (((taskto1 = ?) and ((taskstat1 <> 'Completed') or (taskstat1 is NULL)) and (RECURDATE1 <= '" + year + "/" + month + "/" + day + " " + hour + ":" + minute + ":00') and (recurdate1 < enddate)) or ((taskto2 = ?) and ((taskstat2 <> 'Completed') or (taskstat2 is NULL)) and (RECURDATE2 <= '" + year + "/" + month + "/" + day + " " + hour + ":" + minute + ":00') and (recurdate2 < enddate)) or ((taskto3 = ?) and ((taskstat3 <> 'Completed') or (taskstat3 is NULL)) and (RECURDATE3 <= '" + year + "/" + month + "/" + day + " " + hour + ":" + minute + ":00') and (recurdate3 < enddate)) or ((taskto4 = ?) and ((taskstat4 <> 'Completed') or (taskstat4 is NULL)) and (RECURDATE4 <= '" + year + "/" + month + "/" + day + " " + hour + ":" + minute + ":00') and (recurdate4 < enddate)) or ((taskto5 = ?) and ((taskstat5 <> 'Completed') or (taskstat5 is NULL)) and (RECURDATE5 <= '" + year + "/" + month + "/" + day + " " + hour + ":" + minute + ":00') and (recurdate5 < enddate)) or ((taskto6 = ?) and ((taskstat6 <> 'Completed') or (taskstat6 is NULL)) and (RECURDATE6 <= '" + year + "/" + month + "/" + day + " " + hour + ":" + minute + ":00') and (recurdate6 < enddate)) or ((taskto7 = ?) and ((taskstat7 <> 'Completed') or (taskstat7 is NULL)) and (RECURDATE7 <= '" + year + "/" + month + "/" + day + " " + hour + ":" + minute + ":00') and (recurdate7 < enddate)) or ((taskto8 = ?) and ((taskstat8 <> 'Completed') or (taskstat8 is NULL)) and (RECURDATE8 <= '" + year + "/" + month + "/" + day + " " + hour + ":" + minute + ":00') and (recurdate8 < enddate)) or ((taskto9 = ?) and ((taskstat9 <> 'Completed') or (taskstat9 is NULL)) and (RECURDATE9 <= '" + year + "/" + month + "/" + day + " " + hour + ":" + minute + ":00') and (recurdate9 < enddate)) or ((taskto11 = ?) and ((taskstat11 <> 'Completed') or (taskstat11 is NULL)) and (RECURDATE11 <= '" + year + "/" + month + "/" + day + " " + hour + ":" + minute + ":00') and (recurdate11 < enddate)) or ((taskto12 = ?) and ((taskstat12 <> 'Completed') or (taskstat12 is NULL)) and (RECURDATE12 <= '" + year + "/" + month + "/" + day + " " + hour + ":" + minute + ":00') and (recurdate12 < enddate)) or ((taskto13 = ?) and ((taskstat13 <> 'Completed') or (taskstat13 is NULL)) and (RECURDATE13 <= '" + year + "/" + month + "/" + day + " " + hour + ":" + minute + ":00') and (recurdate13 < enddate)) or ((taskto14 = ?) and ((taskstat14 <> 'Completed') or (taskstat14 is NULL)) and (RECURDATE14 <= '" + year + "/" + month + "/" + day + " " + hour + ":" + minute + ":00') and (recurdate14 < enddate)) or ((taskto15 = ?) and ((taskstat15 <> 'Completed') or (taskstat15 is NULL)) and (RECURDATE15 <= '" + year + "/" + month + "/" + day + " " + hour + ":" + minute + ":00') and (recurdate15 < enddate)) or ((taskto10 = ?) and ((taskstat10 <> 'Completed') or (taskstat10 is NULL)) and (RECURDATE10 <= '" + year + "/" + month + "/" + day + " " + hour + ":" + minute + ":00') and (recurdate10 < enddate)))"; PreparedStatement pstm1 = conn.prepareStatement(sql1); pstm1.setString(1, Username.getUserName()); pstm1.setString(2, Username.getUserName()); pstm1.setString(3, Username.getUserName()); pstm1.setString(4, Username.getUserName()); pstm1.setString(5, Username.getUserName()); pstm1.setString(6, Username.getUserName()); pstm1.setString(7, Username.getUserName()); pstm1.setString(8, Username.getUserName()); pstm1.setString(9, Username.getUserName()); pstm1.setString(10, Username.getUserName()); pstm1.setString(11, Username.getUserName()); pstm1.setString(12, Username.getUserName()); pstm1.setString(13, Username.getUserName()); pstm1.setString(14, Username.getUserName()); pstm1.setString(15, Username.getUserName()); ResultSet rs1 = pstm1.executeQuery(); while (rs1.next()) { Date date1 = new Date(); Calendar cal1 = new GregorianCalendar(); if (rs1.getTimestamp("revdate") == null) { cal1.setTime(rs1.getTimestamp("enddate")); } else { cal1.setTime(rs1.getTimestamp("revdate")); } // cal1.setTime(rs1.getTimestamp("revdate")); String year1 = Integer.toString(cal1.get(Calendar.YEAR)); String month1 = Integer.toString(cal1.get(Calendar.MONTH) + 1); String day1 = Integer.toString(cal1.get(Calendar.DAY_OF_MONTH)); String hour1 = Integer.toString(cal1.get(Calendar.HOUR_OF_DAY)); String minute1 = Integer.toString(cal1.get(Calendar.MINUTE)); if (hour1.length() == 1) { hour1 = "0" + hour1; } if (minute1.length() == 1) { minute1 = "0" + minute1; } if (month1.length() == 1) { month1 = "0" + month1; } if (day1.length() == 1) { day1 = "0" + day1; } Date date2 = new Date(); Calendar cal2 = new GregorianCalendar(); cal2.setTime(rs1.getTimestamp("enddate")); String year2 = Integer.toString(cal2.get(Calendar.YEAR)); String month2 = Integer.toString(cal2.get(Calendar.MONTH) + 1); String day2 = Integer.toString(cal2.get(Calendar.DAY_OF_MONTH)); String hour2 = Integer.toString(cal2.get(Calendar.HOUR_OF_DAY)); String minute2 = Integer.toString(cal2.get(Calendar.MINUTE)); if (hour2.length() == 1) { hour2 = "0" + hour2; } if (minute2.length() == 1) { minute2 = "0" + minute2; } if (month2.length() == 1) { month2 = "0" + month2; } if (day2.length() == 1) { day2 = "0" + day2; } Date date3 = new Date(); Calendar cal3 = new GregorianCalendar(); cal3.setTime(rs1.getTimestamp("startdate")); String year3 = Integer.toString(cal3.get(Calendar.YEAR)); String month3 = Integer.toString(cal3.get(Calendar.MONTH) + 1); String day3 = Integer.toString(cal3.get(Calendar.DAY_OF_MONTH)); String hour3 = Integer.toString(cal3.get(Calendar.HOUR_OF_DAY)); String minute3 = Integer.toString(cal3.get(Calendar.MINUTE)); if (hour3.length() == 1) { hour3 = "0" + hour3; } if (minute3.length() == 1) { minute3 = "0" + minute3; } if (month3.length() == 1) { month3 = "0" + month3; } if (day3.length() == 1) { day3 = "0" + day3; } String tranid = rs1.getString("tranid"); String taskfrom = rs1.getString("taskfrom"); String description = rs1.getString("description"); String revdate = year1 + "-" + month1 + "-" + day1 + " " + hour1 + ":" + minute1; String enddate = year2 + "-" + month2 + "-" + day2 + " " + hour2 + ":" + minute2; String taskstat; if (rs1.getString("taskstat") == null) { taskstat = "Not Started"; } else { taskstat = rs1.getString("taskstat"); } //String taskstat = rs1.getString("taskstat"); String startdate = year3 + "-" + month3 + "-" + day3 + " " + hour3 + ":" + minute3; Tasks task = new Tasks(); task.setTranid(tranid); task.setTaskfrom(taskfrom); task.setDescription(description); task.setRevdate(revdate); task.setEnddate(enddate); task.setTaskstat(taskstat); task.setStartdate(startdate); String color1 = ""; if (date.after(rs1.getTimestamp("revdate"))) { color1 = "red"; } if (color1 == "" && days3.after(rs1.getTimestamp("revdate"))) { color1 = "orange"; } if (color1 == "") { color1 = "green"; } task.setlinkup1(color1); String color2 = "black"; if ((Username.equals(rs1.getString("taskto1")) && (!"Y".equals(rs1.getString("newt1")))) || (Username.equals(rs1.getString("taskto2")) && (!"Y".equals(rs1.getString("newt2")))) || (Username.equals(rs1.getString("taskto3")) && (!"Y".equals(rs1.getString("newt3")))) || (Username.equals(rs1.getString("taskto4")) && (!"Y".equals(rs1.getString("newt4")))) || (Username.equals(rs1.getString("taskto5")) && (!"Y".equals(rs1.getString("newt5")))) || (Username.equals(rs1.getString("taskto6")) && (!"Y".equals(rs1.getString("newt6")))) || (Username.equals(rs1.getString("taskto7")) && (!"Y".equals(rs1.getString("newt7")))) || (Username.equals(rs1.getString("taskto8")) && (!"Y".equals(rs1.getString("newt8")))) || (Username.equals(rs1.getString("taskto9")) && (!"Y".equals(rs1.getString("newt9")))) || (Username.equals(rs1.getString("taskto10")) && (!"Y".equals(rs1.getString("newt10")))) || (Username.equals(rs1.getString("taskto11")) && (!"Y".equals(rs1.getString("newt11")))) || (Username.equals(rs1.getString("taskto12")) && (!"Y".equals(rs1.getString("newt12")))) || (Username.equals(rs1.getString("taskto13")) && (!"Y".equals(rs1.getString("newt13")))) || (Username.equals(rs1.getString("taskto14")) && (!"Y".equals(rs1.getString("newt14")))) || (Username.equals(rs1.getString("taskto15")) && (!"Y".equals(rs1.getString("newt15"))))) { color2 = "green"; } task.setlinkup2(color2); list.add(task); } return list; }
From source file:ffsutils.TaskUtils.java
public static ArrayList<Tasks> getTaskInFuture(Connection conn, UserAccount Username, String onlyUser) throws SQLException { System.out.println("getTaskInFuture " + onlyUser); Date date = new Date(); Calendar cal = Calendar.getInstance(); GregorianCalendar cal121 = new GregorianCalendar(); cal121.setTime(date);/*from ww w .j a va 2 s.c o m*/ cal121.add(Calendar.DATE, 3); Date days3 = cal121.getTime(); String year = Integer.toString(cal.get(Calendar.YEAR)); String month = Integer.toString(cal.get(Calendar.MONTH) + 1); String day = Integer.toString(cal.get(Calendar.DAY_OF_MONTH)); String hour = Integer.toString(cal.get(Calendar.HOUR_OF_DAY)); String minute = Integer.toString(cal.get(Calendar.MINUTE)); String sql = ""; if (onlyUser == null) { onlyUser = ""; } if (onlyUser.equals(null) | onlyUser.length() < 4) { sql = "select * from " + Username.getcompany() + ".tasks where (startdate > '" + year + "/" + month + "/" + day + " " + hour + ":" + minute + ":00') and ( recur = '0') and (((taskto1 = ?) and ((taskstat1 <> 'Completed') or (taskstat1 is NULL))) or ((taskto2 = ?) and ((taskstat2 <> 'Completed') or (taskstat2 is NULL))) or ((taskto3 = ?) and ((taskstat3 <> 'Completed') or (taskstat3 is NULL))) or ((taskto4 = ?) and ((taskstat4 <> 'Completed') or (taskstat4 is NULL))) or ((taskto5 = ?) and ((taskstat5 <> 'Completed') or (taskstat5 is NULL))) or ((taskto6 = ?) and ((taskstat6 <> 'Completed') or (taskstat6 is NULL))) or ((taskto7 = ?) and ((taskstat7 <> 'Completed') or (taskstat7 is NULL))) or ((taskto8 = ?) and ((taskstat8 <> 'Completed') or (taskstat8 is NULL))) or ((taskto9 = ?) and ((taskstat9 <> 'Completed') or (taskstat9 is NULL))) or ((taskto11 = ?) and ((taskstat11 <> 'Completed') or (taskstat11 is NULL))) or ((taskto12 = ?) and ((taskstat12 <> 'Completed') or (taskstat12 is NULL))) or ((taskto13 = ?) and ((taskstat13 <> 'Completed') or (taskstat13 is NULL))) or ((taskto14 = ?) and ((taskstat14 <> 'Completed') or (taskstat14 is NULL))) or ((taskto15 = ?) and ((taskstat15 <> 'Completed') or (taskstat15 is NULL))) or ((taskto10 = ?) and ((taskstat10 <> 'Completed') or (taskstat10 is NULL)))) and (taskstat <> 'Completed')"; } else { sql = "select * from " + Username.getcompany() + ".tasks where (startdate > '" + year + "/" + month + "/" + day + " " + hour + ":" + minute + ":00') and ( recur = '0') and (((taskto1 = ?) and ((taskstat1 <> 'Completed') or (taskstat1 is NULL))) or ((taskto2 = ?) and ((taskstat2 <> 'Completed') or (taskstat2 is NULL))) or ((taskto3 = ?) and ((taskstat3 <> 'Completed') or (taskstat3 is NULL))) or ((taskto4 = ?) and ((taskstat4 <> 'Completed') or (taskstat4 is NULL))) or ((taskto5 = ?) and ((taskstat5 <> 'Completed') or (taskstat5 is NULL))) or ((taskto6 = ?) and ((taskstat6 <> 'Completed') or (taskstat6 is NULL))) or ((taskto7 = ?) and ((taskstat7 <> 'Completed') or (taskstat7 is NULL))) or ((taskto8 = ?) and ((taskstat8 <> 'Completed') or (taskstat8 is NULL))) or ((taskto9 = ?) and ((taskstat9 <> 'Completed') or (taskstat9 is NULL))) or ((taskto11 = ?) and ((taskstat11 <> 'Completed') or (taskstat11 is NULL))) or ((taskto12 = ?) and ((taskstat12 <> 'Completed') or (taskstat12 is NULL))) or ((taskto13 = ?) and ((taskstat13 <> 'Completed') or (taskstat13 is NULL))) or ((taskto14 = ?) and ((taskstat14 <> 'Completed') or (taskstat14 is NULL))) or ((taskto15 = ?) and ((taskstat15 <> 'Completed') or (taskstat15 is NULL))) or ((taskto10 = ?) and ((taskstat10 <> 'Completed') or (taskstat10 is NULL)))) and (taskstat <> 'Completed') and (taskfrom = '" + onlyUser + "')"; } PreparedStatement pstm = conn.prepareStatement(sql); pstm.setString(1, Username.getUserName()); pstm.setString(2, Username.getUserName()); pstm.setString(3, Username.getUserName()); pstm.setString(4, Username.getUserName()); pstm.setString(5, Username.getUserName()); pstm.setString(6, Username.getUserName()); pstm.setString(7, Username.getUserName()); pstm.setString(8, Username.getUserName()); pstm.setString(9, Username.getUserName()); pstm.setString(10, Username.getUserName()); pstm.setString(11, Username.getUserName()); pstm.setString(12, Username.getUserName()); pstm.setString(13, Username.getUserName()); pstm.setString(14, Username.getUserName()); pstm.setString(15, Username.getUserName()); ResultSet rs = pstm.executeQuery(); ArrayList<Tasks> list = new ArrayList<Tasks>(); while (rs.next()) { Date date1 = new Date(); Calendar cal1 = new GregorianCalendar(); if (rs.getTimestamp("revdate") == null) { cal1.setTime(rs.getTimestamp("startdate")); } else { cal1.setTime(rs.getTimestamp("revdate")); } String year1 = Integer.toString(cal1.get(Calendar.YEAR)); String month1 = Integer.toString(cal1.get(Calendar.MONTH) + 1); String day1 = Integer.toString(cal1.get(Calendar.DAY_OF_MONTH)); String hour1 = Integer.toString(cal1.get(Calendar.HOUR_OF_DAY)); String minute1 = Integer.toString(cal1.get(Calendar.MINUTE)); if (hour1.length() == 1) { hour1 = "0" + hour1; } if (minute1.length() == 1) { minute1 = "0" + minute1; } if (month1.length() == 1) { month1 = "0" + month1; } if (day1.length() == 1) { day1 = "0" + day1; } Date date2 = new Date(); Calendar cal2 = new GregorianCalendar(); cal2.setTime(rs.getTimestamp("enddate")); String year2 = Integer.toString(cal2.get(Calendar.YEAR)); String month2 = Integer.toString(cal2.get(Calendar.MONTH) + 1); String day2 = Integer.toString(cal2.get(Calendar.DAY_OF_MONTH)); String hour2 = Integer.toString(cal2.get(Calendar.HOUR_OF_DAY)); String minute2 = Integer.toString(cal2.get(Calendar.MINUTE)); if (hour2.length() == 1) { hour2 = "0" + hour2; } if (minute2.length() == 1) { minute2 = "0" + minute2; } if (month2.length() == 1) { month2 = "0" + month2; } if (day2.length() == 1) { day2 = "0" + day2; } Date date3 = new Date(); Calendar cal3 = new GregorianCalendar(); cal3.setTime(rs.getTimestamp("startdate")); String year3 = Integer.toString(cal3.get(Calendar.YEAR)); String month3 = Integer.toString(cal3.get(Calendar.MONTH) + 1); String day3 = Integer.toString(cal3.get(Calendar.DAY_OF_MONTH)); String hour3 = Integer.toString(cal3.get(Calendar.HOUR_OF_DAY)); String minute3 = Integer.toString(cal3.get(Calendar.MINUTE)); if (hour3.length() == 1) { hour3 = "0" + hour3; } if (minute3.length() == 1) { minute3 = "0" + minute3; } if (month3.length() == 1) { month3 = "0" + month3; } if (day3.length() == 1) { day3 = "0" + day3; } String tranid = rs.getString("tranid"); String taskfrom = rs.getString("taskfrom"); String description = rs.getString("description"); String revdate = year1 + "-" + month1 + "-" + day1 + " " + hour1 + ":" + minute1; String enddate = year2 + "-" + month2 + "-" + day2 + " " + hour2 + ":" + minute2; String taskstat; if (rs.getString("taskstat") == null) { taskstat = "Not Started"; } else { taskstat = rs.getString("taskstat"); } //String taskstat = rs.getString("taskstat"); String startdate = year3 + "-" + month3 + "-" + day3 + " " + hour3 + ":" + minute3; Tasks task = new Tasks(); task.setTranid(tranid); task.setTaskfrom(taskfrom); task.setDescription(description); task.setRevdate(revdate); task.setEnddate(enddate); task.setTaskstat(taskstat); task.setStartdate(startdate); String color1 = ""; if (date.after(rs.getTimestamp("revdate"))) { color1 = "red"; } if (color1 == "" && days3.after(rs.getTimestamp("revdate"))) { color1 = "orange"; } if (color1 == "") { color1 = "green"; } task.setlinkup1(color1); String color2 = "black"; if ((Username.equals(rs.getString("taskto1")) && (!"Y".equals(rs.getString("newt1")))) || (Username.equals(rs.getString("taskto2")) && (!"Y".equals(rs.getString("newt2")))) || (Username.equals(rs.getString("taskto3")) && (!"Y".equals(rs.getString("newt3")))) || (Username.equals(rs.getString("taskto4")) && (!"Y".equals(rs.getString("newt4")))) || (Username.equals(rs.getString("taskto5")) && (!"Y".equals(rs.getString("newt5")))) || (Username.equals(rs.getString("taskto6")) && (!"Y".equals(rs.getString("newt6")))) || (Username.equals(rs.getString("taskto7")) && (!"Y".equals(rs.getString("newt7")))) || (Username.equals(rs.getString("taskto8")) && (!"Y".equals(rs.getString("newt8")))) || (Username.equals(rs.getString("taskto9")) && (!"Y".equals(rs.getString("newt9")))) || (Username.equals(rs.getString("taskto10")) && (!"Y".equals(rs.getString("newt10")))) || (Username.equals(rs.getString("taskto11")) && (!"Y".equals(rs.getString("newt11")))) || (Username.equals(rs.getString("taskto12")) && (!"Y".equals(rs.getString("newt12")))) || (Username.equals(rs.getString("taskto13")) && (!"Y".equals(rs.getString("newt13")))) || (Username.equals(rs.getString("taskto14")) && (!"Y".equals(rs.getString("newt14")))) || (Username.equals(rs.getString("taskto15")) && (!"Y".equals(rs.getString("newt15"))))) { color2 = "green"; } task.setlinkup2(color2); list.add(task); } String sql1 = ""; if (onlyUser == null) { onlyUser = ""; } if (onlyUser.equals(null) | onlyUser.length() < 4) { sql1 = "select * from " + Username.getcompany() + ".tasks where (recur =1) and (((taskto1 = ?) and ((taskstat1 <> 'Completed') or (taskstat1 is NULL)) and (RECURDATE1 >'" + year + "/" + month + "/" + day + " " + hour + ":" + minute + ":00') and (recurdate1 < enddate)) or ((taskto2 = ?) and ((taskstat2 <> 'Completed') or (taskstat2 is NULL)) and (RECURDATE2 >'" + year + "/" + month + "/" + day + " " + hour + ":" + minute + ":00') and (recurdate2 < enddate)) or ((taskto3 = ?) and ((taskstat3 <> 'Completed') or (taskstat3 is NULL)) and (RECURDATE3 >'" + year + "/" + month + "/" + day + " " + hour + ":" + minute + ":00') and (recurdate3 < enddate)) or ((taskto4 = ?) and ((taskstat4 <> 'Completed') or (taskstat4 is NULL)) and (RECURDATE4 >'" + year + "/" + month + "/" + day + " " + hour + ":" + minute + ":00') and (recurdate4 < enddate)) or ((taskto5 = ?) and ((taskstat5 <> 'Completed') or (taskstat5 is NULL)) and (RECURDATE5 >'" + year + "/" + month + "/" + day + " " + hour + ":" + minute + ":00') and (recurdate5 < enddate)) or ((taskto6 = ?) and ((taskstat6 <> 'Completed') or (taskstat6 is NULL)) and (RECURDATE6 >'" + year + "/" + month + "/" + day + " " + hour + ":" + minute + ":00') and (recurdate6 < enddate)) or ((taskto7 = ?) and ((taskstat7 <> 'Completed') or (taskstat7 is NULL)) and (RECURDATE7 >'" + year + "/" + month + "/" + day + " " + hour + ":" + minute + ":00') and (recurdate7 < enddate)) or ((taskto8 = ?) and ((taskstat8 <> 'Completed') or (taskstat8 is NULL)) and (RECURDATE8 >'" + year + "/" + month + "/" + day + " " + hour + ":" + minute + ":00') and (recurdate8 < enddate)) or ((taskto9 = ?) and ((taskstat9 <> 'Completed') or (taskstat9 is NULL)) and (RECURDATE9 >'" + year + "/" + month + "/" + day + " " + hour + ":" + minute + ":00') and (recurdate9 < enddate)) or ((taskto11 = ?) and ((taskstat11 <> 'Completed') or (taskstat11 is NULL)) and (RECURDATE11 >'" + year + "/" + month + "/" + day + " " + hour + ":" + minute + ":00') and (recurdate11 < enddate)) or ((taskto12 = ?) and ((taskstat12 <> 'Completed') or (taskstat12 is NULL)) and (RECURDATE12 >'" + year + "/" + month + "/" + day + " " + hour + ":" + minute + ":00') and (recurdate12 < enddate)) or ((taskto13 = ?) and ((taskstat13 <> 'Completed') or (taskstat13 is NULL)) and (RECURDATE13 >'" + year + "/" + month + "/" + day + " " + hour + ":" + minute + ":00') and (recurdate13 < enddate)) or ((taskto14 = ?) and ((taskstat14 <> 'Completed') or (taskstat14 is NULL)) and (RECURDATE14 >'" + year + "/" + month + "/" + day + " " + hour + ":" + minute + ":00') and (recurdate14 < enddate)) or ((taskto15 = ?) and ((taskstat15 <> 'Completed') or (taskstat15 is NULL)) and (RECURDATE15 >'" + year + "/" + month + "/" + day + " " + hour + ":" + minute + ":00') and (recurdate15 < enddate)) or ((taskto10 = ?) and ((taskstat10 <> 'Completed') or (taskstat10 is NULL)) and (RECURDATE10 >'" + year + "/" + month + "/" + day + " " + hour + ":" + minute + ":00') and (recurdate10 < enddate)))"; } else { sql1 = "select * from " + Username.getcompany() + ".tasks where (recur =1) and (((taskto1 = ?) and ((taskstat1 <> 'Completed') or (taskstat1 is NULL)) and (RECURDATE1 >'" + year + "/" + month + "/" + day + " " + hour + ":" + minute + ":00') and (recurdate1 < enddate)) or ((taskto2 = ?) and ((taskstat2 <> 'Completed') or (taskstat2 is NULL)) and (RECURDATE2 >'" + year + "/" + month + "/" + day + " " + hour + ":" + minute + ":00') and (recurdate2 < enddate)) or ((taskto3 = ?) and ((taskstat3 <> 'Completed') or (taskstat3 is NULL)) and (RECURDATE3 >'" + year + "/" + month + "/" + day + " " + hour + ":" + minute + ":00') and (recurdate3 < enddate)) or ((taskto4 = ?) and ((taskstat4 <> 'Completed') or (taskstat4 is NULL)) and (RECURDATE4 >'" + year + "/" + month + "/" + day + " " + hour + ":" + minute + ":00') and (recurdate4 < enddate)) or ((taskto5 = ?) and ((taskstat5 <> 'Completed') or (taskstat5 is NULL)) and (RECURDATE5 >'" + year + "/" + month + "/" + day + " " + hour + ":" + minute + ":00') and (recurdate5 < enddate)) or ((taskto6 = ?) and ((taskstat6 <> 'Completed') or (taskstat6 is NULL)) and (RECURDATE6 >'" + year + "/" + month + "/" + day + " " + hour + ":" + minute + ":00') and (recurdate6 < enddate)) or ((taskto7 = ?) and ((taskstat7 <> 'Completed') or (taskstat7 is NULL)) and (RECURDATE7 >'" + year + "/" + month + "/" + day + " " + hour + ":" + minute + ":00') and (recurdate7 < enddate)) or ((taskto8 = ?) and ((taskstat8 <> 'Completed') or (taskstat8 is NULL)) and (RECURDATE8 >'" + year + "/" + month + "/" + day + " " + hour + ":" + minute + ":00') and (recurdate8 < enddate)) or ((taskto9 = ?) and ((taskstat9 <> 'Completed') or (taskstat9 is NULL)) and (RECURDATE9 >'" + year + "/" + month + "/" + day + " " + hour + ":" + minute + ":00') and (recurdate9 < enddate)) or ((taskto11 = ?) and ((taskstat11 <> 'Completed') or (taskstat11 is NULL)) and (RECURDATE11 >'" + year + "/" + month + "/" + day + " " + hour + ":" + minute + ":00') and (recurdate11 < enddate)) or ((taskto12 = ?) and ((taskstat12 <> 'Completed') or (taskstat12 is NULL)) and (RECURDATE12 >'" + year + "/" + month + "/" + day + " " + hour + ":" + minute + ":00') and (recurdate12 < enddate)) or ((taskto13 = ?) and ((taskstat13 <> 'Completed') or (taskstat13 is NULL)) and (RECURDATE13 >'" + year + "/" + month + "/" + day + " " + hour + ":" + minute + ":00') and (recurdate13 < enddate)) or ((taskto14 = ?) and ((taskstat14 <> 'Completed') or (taskstat14 is NULL)) and (RECURDATE14 >'" + year + "/" + month + "/" + day + " " + hour + ":" + minute + ":00') and (recurdate14 < enddate)) or ((taskto15 = ?) and ((taskstat15 <> 'Completed') or (taskstat15 is NULL)) and (RECURDATE15 >'" + year + "/" + month + "/" + day + " " + hour + ":" + minute + ":00') and (recurdate15 < enddate)) or ((taskto10 = ?) and ((taskstat10 <> 'Completed') or (taskstat10 is NULL)) and (RECURDATE10 >'" + year + "/" + month + "/" + day + " " + hour + ":" + minute + ":00') and (recurdate10 < enddate))) and (taskfrom ='" + onlyUser + "')"; } PreparedStatement pstm1 = conn.prepareStatement(sql1); pstm1.setString(1, Username.getUserName()); pstm1.setString(2, Username.getUserName()); pstm1.setString(3, Username.getUserName()); pstm1.setString(4, Username.getUserName()); pstm1.setString(5, Username.getUserName()); pstm1.setString(6, Username.getUserName()); pstm1.setString(7, Username.getUserName()); pstm1.setString(8, Username.getUserName()); pstm1.setString(9, Username.getUserName()); pstm1.setString(10, Username.getUserName()); pstm1.setString(11, Username.getUserName()); pstm1.setString(12, Username.getUserName()); pstm1.setString(13, Username.getUserName()); pstm1.setString(14, Username.getUserName()); pstm1.setString(15, Username.getUserName()); ResultSet rs1 = pstm1.executeQuery(); while (rs1.next()) { Date date1 = new Date(); Calendar cal1 = new GregorianCalendar(); if (rs1.getTimestamp("revdate") == null) { cal1.setTime(rs1.getTimestamp("enddate")); } else { cal1.setTime(rs1.getTimestamp("revdate")); } // cal1.setTime(rs1.getTimestamp("revdate")); String year1 = Integer.toString(cal1.get(Calendar.YEAR)); String month1 = Integer.toString(cal1.get(Calendar.MONTH) + 1); String day1 = Integer.toString(cal1.get(Calendar.DAY_OF_MONTH)); String hour1 = Integer.toString(cal1.get(Calendar.HOUR_OF_DAY)); String minute1 = Integer.toString(cal1.get(Calendar.MINUTE)); if (hour1.length() == 1) { hour1 = "0" + hour1; } if (minute1.length() == 1) { minute1 = "0" + minute1; } if (month1.length() == 1) { month1 = "0" + month1; } if (day1.length() == 1) { day1 = "0" + day1; } Date date2 = new Date(); Calendar cal2 = new GregorianCalendar(); cal2.setTime(rs1.getTimestamp("enddate")); String year2 = Integer.toString(cal2.get(Calendar.YEAR)); String month2 = Integer.toString(cal2.get(Calendar.MONTH) + 1); String day2 = Integer.toString(cal2.get(Calendar.DAY_OF_MONTH)); String hour2 = Integer.toString(cal2.get(Calendar.HOUR_OF_DAY)); String minute2 = Integer.toString(cal2.get(Calendar.MINUTE)); if (hour2.length() == 1) { hour2 = "0" + hour2; } if (minute2.length() == 1) { minute2 = "0" + minute2; } if (month2.length() == 1) { month2 = "0" + month2; } if (day2.length() == 1) { day2 = "0" + day2; } Date date3 = new Date(); Calendar cal3 = new GregorianCalendar(); cal3.setTime(rs1.getTimestamp("startdate")); String year3 = Integer.toString(cal3.get(Calendar.YEAR)); String month3 = Integer.toString(cal3.get(Calendar.MONTH) + 1); String day3 = Integer.toString(cal3.get(Calendar.DAY_OF_MONTH)); String hour3 = Integer.toString(cal3.get(Calendar.HOUR_OF_DAY)); String minute3 = Integer.toString(cal3.get(Calendar.MINUTE)); if (hour3.length() == 1) { hour3 = "0" + hour3; } if (minute3.length() == 1) { minute3 = "0" + minute3; } if (month3.length() == 1) { month3 = "0" + month3; } if (day3.length() == 1) { day3 = "0" + day3; } String tranid = rs1.getString("tranid"); String taskfrom = rs1.getString("taskfrom"); String description = rs1.getString("description"); String revdate = year1 + "-" + month1 + "-" + day1 + " " + hour1 + ":" + minute1; String enddate = year2 + "-" + month2 + "-" + day2 + " " + hour2 + ":" + minute2; String taskstat; if (rs1.getString("taskstat") == null) { taskstat = "Not Started"; } else { taskstat = rs1.getString("taskstat"); } //String taskstat = rs1.getString("taskstat"); String startdate = year3 + "-" + month3 + "-" + day3 + " " + hour3 + ":" + minute3; Tasks task = new Tasks(); task.setTranid(tranid); task.setTaskfrom(taskfrom); task.setDescription(description); task.setRevdate(revdate); task.setEnddate(enddate); task.setTaskstat(taskstat); task.setStartdate(startdate); String color1 = ""; if (date.after(rs1.getTimestamp("revdate"))) { color1 = "red"; } if (color1 == "" && days3.after(rs1.getTimestamp("revdate"))) { color1 = "orange"; } if (color1 == "") { color1 = "green"; } task.setlinkup1(color1); String color2 = "black"; if ((Username.equals(rs1.getString("taskto1")) && (!"Y".equals(rs1.getString("newt1")))) || (Username.equals(rs1.getString("taskto2")) && (!"Y".equals(rs1.getString("newt2")))) || (Username.equals(rs1.getString("taskto3")) && (!"Y".equals(rs1.getString("newt3")))) || (Username.equals(rs1.getString("taskto4")) && (!"Y".equals(rs1.getString("newt4")))) || (Username.equals(rs1.getString("taskto5")) && (!"Y".equals(rs1.getString("newt5")))) || (Username.equals(rs1.getString("taskto6")) && (!"Y".equals(rs1.getString("newt6")))) || (Username.equals(rs1.getString("taskto7")) && (!"Y".equals(rs1.getString("newt7")))) || (Username.equals(rs1.getString("taskto8")) && (!"Y".equals(rs1.getString("newt8")))) || (Username.equals(rs1.getString("taskto9")) && (!"Y".equals(rs1.getString("newt9")))) || (Username.equals(rs1.getString("taskto10")) && (!"Y".equals(rs1.getString("newt10")))) || (Username.equals(rs1.getString("taskto11")) && (!"Y".equals(rs1.getString("newt11")))) || (Username.equals(rs1.getString("taskto12")) && (!"Y".equals(rs1.getString("newt12")))) || (Username.equals(rs1.getString("taskto13")) && (!"Y".equals(rs1.getString("newt13")))) || (Username.equals(rs1.getString("taskto14")) && (!"Y".equals(rs1.getString("newt14")))) || (Username.equals(rs1.getString("taskto15")) && (!"Y".equals(rs1.getString("newt15"))))) { color2 = "green"; } task.setlinkup2(color2); list.add(task); } return list; }
From source file:org.redis.shop.test.InitData.java
@Test public void bm_addSaleInfo() throws Exception { List<Category> categories = categoryService.getByBasicProperty(categoryService.getAll(), "level", 3); categories = categoryService.getByPropertyNotNull(categories, "parentCategory"); User user = userService.get("name", "c1"); Center center = centerService.get("user.userId", user.getUserId()); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); Date parse = sdf.parse("2015-11-01"); Date today = new Date(); today = sdf.parse(sdf.format(today)); Calendar calendar = Calendar.getInstance(); calendar.setTime(parse);//from ww w . j a va2s.com long time1 = calendar.getTimeInMillis(); calendar.setTime(today); long time2 = calendar.getTimeInMillis(); long betweenDays = (time2 - time1) / (1000 * 3600 * 24); GregorianCalendar cal = new GregorianCalendar(); List<SaleInfo> saleInfos = new ArrayList<SaleInfo>(); for (int i = 0; i <= betweenDays; i++) { cal.setTime(parse); cal.add(Calendar.DATE, i); Date dateNow = cal.getTime(); cal.add(Calendar.DAY_OF_MONTH, 7); Date endDate = cal.getTime(); for (Category category : categories) { SaleInfo si = new SaleInfo(); si.setCategory(category); si.setCenter(center); si.setStartDate(dateNow); si.setPerson("cwj"); si.setPhone("123456789"); si.setEndDate(endDate); si.setTitle("?" + category.getName()); si.setPrice(DataUtil.getRandm(20, 25)); si.setWeight((double) DataUtil.getRandm(200, 209)); System.out.println(si.getPrice()); System.out.println(si.getWeight()); si.setUnit("?"); si.setState(Enum_sale.D); si.setOutTime(dateNow); // saleInfoService.save(si); saleInfos.add(si); } } saleInfoService.save(saleInfos); }