/**
* created next PK of record
*
* @throws DatabaseException
*/
public long nextPK() throws DatabaseException {
log.debug("Model nextPK Started");
Connection conn = null;
long pk = 0;
try {
conn = JDBCDataSource.getConnection();
PreparedStatement pstmt = conn.prepareStatement("SELECT MAX(ID) FROM " + getTableName());
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
pk = rs.getInt(1);
}
rs.close();
} catch (Exception e) {
log.error("Database Exception..", e);
throw new DatabaseException("Exception : Exception in getting PK");
} finally {
JDBCDataSource.closeConnection(conn);
}
log.debug("Model nextPK End");
return pk + 1;
}
/**
* Create instance of Connection Pool
*
* @return
*/
public static JDBCDataSource getInstance() {
if (datasource == null) {
ResourceBundle rb = ResourceBundle.getBundle("in.co.sunrays.bundle.system");
datasource = new JDBCDataSource();
datasource.cpds = new ComboPooledDataSource();
try {
datasource.cpds.setDriverClass(rb.getString("driver"));
} catch (Exception e) {
e.printStackTrace();
}
datasource.cpds.setJdbcUrl(rb.getString("url"));
datasource.cpds.setUser(rb.getString("username"));
datasource.cpds.setPassword(rb.getString("password"));
datasource.cpds.setInitialPoolSize(new Integer((String) rb.getString("initialPoolSize")));
datasource.cpds.setAcquireIncrement(new Integer((String) rb.getString("acquireIncrement")));
datasource.cpds.setMaxPoolSize(new Integer((String) rb.getString("maxPoolSize")));
datasource.cpds.setMaxIdleTime(DataUtility.getInt(rb.getString("timeout")));
datasource.cpds.setMinPoolSize(new Integer((String) rb.getString("minPoolSize")));
}
return datasource;
}
/**
* Search Attendance with pagination
*
* @return list : List of Attendance
* @param bean : Search Parameters
* @param pageNo : Current Page No.
* @param pageSize : Size of Page
* @throws DatabaseException
*/
public List search(int pageNo, int pageSize) throws ApplicationException {
log.debug("Model search Started");
StringBuffer sql = new StringBuffer("SELECT * FROM ST_ATTENDANCESHEET WHERE 1=1");
if (id > 0) {
sql.append(" AND id = " + id);
}
if (roleId > 0) {
sql.append(" AND ROLE_ID like '" + roleId + "%'");
}
if (studentId > 0) {
sql.append(" AND STUDENT_ID like '" + studentId + "%'");
}
if (collegeId > 0) {
sql.append(" AND COLLEGE_ID = " + collegeId);
}
if (name != null && name.length() > 0) {
sql.append(" AND NAME like '" + name + "%'");
}
if (code != null && code.length() > 0) {
sql.append(" AND CODE like '" + code + "%'");
}
if (year != null && year.length() > 0) {
sql.append(" AND YEAR like '" + year + "%'");
}
if (month != null && month.length() > 0) {
sql.append(" AND MONTH like '" + month + "%'");
} else {
sql.append(
" AND MONTH = MONTH(CURDATE()) AND YEAR = YEAR(CURDATE()) ORDER BY MONTH,YEAR DESC");
}
// if page size is greater than zero then apply pagination
if (pageSize > 0) {
// Calculate start record index
pageNo = (pageNo - 1) * pageSize;
sql.append(" Limit " + pageNo + ", " + pageSize);
// sql.append(" limit " + pageNo + "," + pageSize);
}
log.info("SQL : " + sql);
ArrayList list = new ArrayList();
Connection conn = null;
try {
conn = JDBCDataSource.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql.toString());
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
AttendanceSheetModel model = populateModel(new AttendanceSheetModel(), rs);
list.add(model);
}
rs.close();
} catch (Exception e) {
log.error("Database Exception..", e);
throw new ApplicationException("Exception : Exception in search AttendanceSheet");
} finally {
JDBCDataSource.closeConnection(conn);
}
log.debug("Model search End");
return list;
}
public long add() throws ApplicationException {
System.out.println("in attendance");
log.debug("Model add Started");
Connection conn = null;
long pk = 0;
try {
conn = JDBCDataSource.getConnection();
pk = nextPK();
// Get auto-generated next primary key
conn.setAutoCommit(false); // Begin transaction
PreparedStatement pstmtupdate =
conn.prepareStatement(
"UPDATE ST_ATTENDANCESHEET SET D1=? WHERE STUDENT_ID=? AND ROLE_ID=? AND YEAR=? AND MONTH=?");
PreparedStatement pstmt =
conn.prepareStatement(
"INSERT INTO ST_ATTENDANCESHEET(ID,CODE,ROLE_ID,STUDENT_ID,COLLEGE_ID,NAME,YEAR,MONTH,DAY) VALUES(?,?,?,?,?,?,?,?,?)");
java.sql.Statement statement =
conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs =
statement.executeQuery(
"SELECT ID,CODE,ROLE_ID,STUDENT_ID,COLLEGE_ID,NAME,YEAR(STR_TO_DATE(DATE, '%Y-%m-%d')) AS YEAR,MONTH(STR_TO_DATE(DATE, '%Y-%m-%d')) AS MONTH,DAY(STR_TO_DATE(DATE, '%Y-%m-%d')) AS DAY FROM st_studentattendance");
while (rs.next()) {
Boolean flag = false;
long id = rs.getLong(1);
String code = rs.getString(2);
long roleId = rs.getLong(3);
long studentId = rs.getLong(4);
long collegeId = rs.getLong(5);
String studentName = rs.getString(6);
String year = rs.getString(7);
String month = rs.getString(8);
String d = rs.getString(9);
PreparedStatement pstmt2 =
conn.prepareStatement(
"SELECT * FROM ST_ATTENDANCESHEET WHERE STUDENT_ID=? AND ROLE_ID=? AND YEAR=? AND MONTH=? ");
pstmt2.setLong(1, studentId);
pstmt2.setLong(2, roleId);
pstmt2.setString(3, year);
pstmt2.setString(4, month);
ResultSet rs1 = pstmt2.executeQuery();
if (rs1.next()) {
flag = true;
}
if (flag == false) {
pstmt =
conn.prepareStatement(
"INSERT INTO ST_ATTENDANCESHEET(ID,CODE,ROLE_ID,STUDENT_ID,COLLEGE_ID,NAME,YEAR,MONTH,D"
+ (d)
+ ") VALUES(?,?,?,?,?,?,?,?,?)");
pstmt.setLong(1, id);
pstmt.setString(2, code);
pstmt.setLong(3, roleId);
pstmt.setLong(4, studentId);
pstmt.setLong(5, collegeId);
pstmt.setString(6, studentName);
pstmt.setString(7, year);
pstmt.setString(8, month);
pstmt.setInt(9, day);
pstmt.executeUpdate();
} else {
pstmtupdate =
conn.prepareStatement(
"UPDATE ST_ATTENDANCESHEET SET D"
+ (d)
+ "=? WHERE STUDENT_ID=? AND ROLE_ID=? AND YEAR=? AND MONTH=?");
pstmtupdate.setInt(1, day);
pstmtupdate.setLong(2, studentId);
pstmtupdate.setLong(3, roleId);
pstmtupdate.setString(4, year);
pstmtupdate.setString(5, month);
pstmtupdate.executeUpdate();
}
conn.commit(); // End transaction
pstmt.close();
}
PreparedStatement pstmt3 =
conn.prepareStatement(
"SELECT ID,SUM(D1+D2+D3+D4+D5+D6+D7+D8+D9+D10+D11+D12+D13+D14+D15+D16+D17+D18+D19+D20+D21+D22+D23+D24+D25+D26+D27+D28+D29+D30+D31) AS total FROM ST_ATTENDANCESHEET group By ID");
ResultSet rs3 = pstmt3.executeQuery();
while (rs3.next()) {
long aId = rs3.getLong(1);
int totalPresent = rs3.getInt(2);
PreparedStatement pstmtcount =
conn.prepareStatement("UPDATE ST_ATTENDANCESHEET SET TOTAL=? WHERE ID=?");
pstmtcount.setInt(1, totalPresent);
pstmtcount.setLong(2, aId);
pstmtcount.executeUpdate();
conn.commit(); // End transaction
pstmtcount.close();
}
} catch (Exception e) {
log.error("Database Exception..", e);
JDBCDataSource.trnRollback(conn);
throw new ApplicationException(e);
} finally {
JDBCDataSource.closeConnection(conn);
}
log.debug("Model add End");
return pk;
}