WordDao

public class WordDao extends BaseDao<Word>{
	public int[] updateTestWordsFromCB(List<Word> wordList) throws SQLException{
		if(wordList == null) throw new IllegalArgumentException("parm wordList is null");
		int result[] = null;
		Connection conn = DBUtil.getConnection();
		QueryRunner runner = new QueryRunner();
		String sql = "UPDATE " + tableName + "SET (FLDTESTTIMES=?,FLDPRIORITY=?,FLDERRORFAG=?,FLDLASTTESTDATE=?,FLDERRORTIMES=?) WHERE FLDWORDID=?";
			
		Object[][] params = new Object[wordList.size()][5];
		for(int i = 0;i<wordList.size();i++){
			params[i] = new Object[]{
					wordList.get(i).getFldTesttimes(),
					wordList.get(i).getFldPriority(),
					wordList.get(i).getFldErrorfag(),
					wordList.get(i).getFldLasttestdate(),
					wordList.get(i).getFldErrortimes(),
					wordList.get(i).getFldWordid()};
		}
		result = runner.batch(conn, sql, params);
		closeConnection(conn);
		return result;
	}
	
	public int[] updateTestWordsSalfFromCB(List<Word> wordList){
		Connection conn = DBUtil.getConnection();
		if(wordList == null) throw new IllegalArgumentException("parm wordList is null");
		int[] result = new int[wordList.size()];
		for(int i = 0;i<result.length;i++){
			if(wordList.get(i)==null){
				result[i] = 0;
			}else{
				try {
					result[i] = updateTestWordFromCB(conn,wordList.get(i),false);
				} catch (Exception e) {
					e.printStackTrace();
				}
			}    
		}
		closeConnection(conn);
		return result;
	}
	
	public int updateTestWordFromCB(Connection conn,Word word) throws SQLException{
		return updateTestWordFromCB(conn, word, true);
	}
	public int updateTestWordFromCB(Connection conn,Word word, boolean closeConn) throws SQLException{
		if(word == null) throw new IllegalArgumentException("parm word is null");
		int result = 0;
		QueryRunner runner = new QueryRunner();
		String sql = "UPDATE " + tableName + " SET FLDTESTTIMES=?,FLDPRIORITY=?,FLDERRORFAG=?,FLDLASTTESTDATE=?,FLDERRORTIMES=?,FLDFORGETTAG=? WHERE FLDWORDID=?";
		result = runner.update(conn, sql, word.getFldTesttimes(),word.getFldPriority(),word.getFldErrorfag(),word.getFldLasttestdate(),word.getFldErrortimes(),word.getFldForgettag(),word.getFldWordid());
		if(closeConn){
			closeConnection(conn);
		}
		return result;
	}
	
	public int saveWordFromCB(Word word) throws SQLException{
		Connection conn = DBUtil.getConnection();
		return saveWordFromCB(conn,word,true);
	}
	
	private int saveWordFromCB(Connection conn, Word word,boolean closeConn) throws SQLException{
		if(word == null) throw new IllegalArgumentException("parm word is null");
		int result = 0;
		if(checkExistWord(conn, word, false)){
			System.out.println("word (" + word.getFldWorden() + ") is exist.");
			return result;
		}
		QueryRunner runner = new QueryRunner();
		String sql = "INSERT INTO " + tableName + "(FLDWORDEN,FLDWORDCN,FLDCREATDATE,FLDTESTTIMES,FLDPRIORITY,FLDFROM,FLDSYMBOL,FLDATTR,FLDERRORTIMES) " +
				"VALUES (?,?,?,DEFAULT,DEFAULT,'CIBA',?,?,DEFAULT)";
		
		result = runner.update(conn, sql,word.getFldWorden(),word.getFldWordcn(),DateUtil.getTimeStamp(),word.getFldSymbol(),word.getFldAttr());// ,,,,);
		
		if(closeConn){
			closeConnection(conn);
		}
		return result;
	}

	public int[] saveWordsSalfFromCB(List<Word> wordList) throws SQLException{
		Connection conn = DBUtil.getConnection();
		if(wordList == null) throw new IllegalArgumentException("parm wordList is null");
		int[] result = new int[wordList.size()];
		for(int i = 0;i<result.length;i++){
			if(wordList.get(i)==null){
				result[i] = 0;
			}else{
				try {
					result[i] = saveWordFromCB(conn,wordList.get(i),false);
				} catch (Exception e) {
					System.out.println(e.getMessage() + ", " + result[i] + ", " + wordList.get(i).getFldWorden());
				}
			}    
		}
		closeConnection(conn);
		return result;
	}
	
//	public int[] saveWordsFromCB(List<Word> wordList) throws SQLException{
//		if(wordList == null) throw new IllegalArgumentException("parm wordList is null");
//		int result[] = null;
//		Connection conn = DBUtil.getConnection();
//		QueryRunner runner = new QueryRunner();
//		String sql = "INSERT INTO " + tableName + "(FLDWORDEN,FLDWORDCN,FLDCREATDATE,FLDTESTTIMES,FLDPRIORITY,FLDFROM,FLDSYMBOL,FLDATTR) " +
//			"VALUES (?,?,?,DEFAULT,DEFAULT,'CIBA',?,?)";
//			
//		Object[][] params = new Object[wordList.size()][5];
//		for(int i = 0;i<wordList.size();i++){
//			params[i] = new Object[]{wordList.get(i).getFldWorden(),wordList.get(i).getFldWordcn(),DateUtil.getTimeStamp(),wordList.get(i).getFldSymbol(),wordList.get(i).getFldAttr()};
//		}
//		result = runner.batch(conn, sql, params);
//		closeConnection(conn);
//		return result;
//	}
	
	public List<Word> getWordsByCondition(Map<String, Object> conditionMap){
		if(conditionMap == null){
			throw new IllegalArgumentException("parm conditionMap is null");
		}
		List<Object> parmList = new ArrayList<Object>();
		StringBuffer sbSql = new StringBuffer("SELECT * FROM " +  tableName + " WHERE 1=1 ");
		List<Word> resultWordList = new ArrayList<Word>();
		
		if(conditionMap.get("wordkey")!=null){//	FLDWORDEN	VARCHAR	50	No
			sbSql.append(" AND FLDWORDEN like ? ");
			parmList.add("%" + conditionMap.get("wordkey") + "%");
		}

		if(conditionMap.get("createdatestart")!=null || conditionMap.get("createdateend")!=null){ // 	FLDCREATDATE
			if(conditionMap.get("createdatestart")!=null && conditionMap.get("createdateend")!=null){
				sbSql.append(" AND FLDCREATDATE BETWEEN ? AND ? ");
				parmList.add(conditionMap.get("createdatestart"));
				parmList.add(conditionMap.get("createdateend"));
			}else if(conditionMap.get("createdatestart")!=null && conditionMap.get("createdateend")==null){
				sbSql.append(" AND FLDCREATDATE >= ?");
				parmList.add(conditionMap.get("createdatestart"));
			}else if(conditionMap.get("createdatestart")==null && conditionMap.get("createdateend")!=null){
				sbSql.append(" AND FLDCREATDATE <= ?");
				parmList.add(conditionMap.get("createdateend"));
			}
		}
		
		if(conditionMap.get("ltdatestart")!=null || conditionMap.get("ltdateend")!=null){ // 	FLDLASTTESTDATE	VARCHAR	8	Yes
			if(conditionMap.get("ltdatestart")!=null && conditionMap.get("ltdateend")!=null){
				sbSql.append(" AND FLDLASTTESTDATE BETWEEN ? AND ? ");
				parmList.add(conditionMap.get("ltdatestart"));
				parmList.add(conditionMap.get("ltdateend"));
			}else if(conditionMap.get("ltdatestart")!=null && conditionMap.get("ltdateend")==null){
				sbSql.append(" AND FLDLASTTESTDATE >= ?");
				parmList.add(conditionMap.get("ltdatestart"));
			}else if(conditionMap.get("ltdatestart")==null && conditionMap.get("ltdateend")!=null){
				sbSql.append(" AND FLDLASTTESTDATE <= ?");
				parmList.add(conditionMap.get("ltdateend"));
			}
		}
		
		if(conditionMap.get("prioritystart")!=null || conditionMap.get("priorityend")!=null){ //	FLDPRIORITY	INTEGER	4	Yes
			if(conditionMap.get("prioritystart")!=null && conditionMap.get("priorityend")!=null){
				sbSql.append(" AND FLDPRIORITY BETWEEN ? AND ? ");
				parmList.add(conditionMap.get("prioritystart"));
				parmList.add(conditionMap.get("priorityend"));
			}else if(conditionMap.get("prioritystart")!=null && conditionMap.get("priorityend")==null){
				sbSql.append(" AND FLDPRIORITY >= ?");
				parmList.add(conditionMap.get("prioritystart"));
			}else if(conditionMap.get("prioritystart")==null && conditionMap.get("priorityend")!=null){
				sbSql.append(" AND FLDPRIORITY <= ?");
				parmList.add(conditionMap.get("priorityend"));
			}
		}	
		if(conditionMap.get("testtimesstart")!=null || conditionMap.get("testtimesend")!=null){ //	FLDTESTTIMES	INTEGER	4	Yes
			if(conditionMap.get("testtimesstart")!=null && conditionMap.get("testtimesend")!=null){
				sbSql.append(" AND FLDTESTTIMES BETWEEN ? AND ? ");
				parmList.add(conditionMap.get("testtimesstart"));
				parmList.add(conditionMap.get("testtimesend"));
			}else if(conditionMap.get("testtimesstart")!=null && conditionMap.get("testtimesend")==null){
				sbSql.append(" AND FLDTESTTIMES >= ?");
				parmList.add(conditionMap.get("testtimesstart"));
			}else if(conditionMap.get("testtimesstart")==null && conditionMap.get("testtimesend")!=null){
				sbSql.append(" AND FLDTESTTIMES <= ?");
				parmList.add(conditionMap.get("testtimesend"));
			}
		}	
		
		if(conditionMap.get("errortimesstart")!=null || conditionMap.get("errortimesend")!=null){	//FLDERRORTIMES
			if(conditionMap.get("errortimesstart")!=null && conditionMap.get("errortimesend")!=null){
				sbSql.append(" AND FLDERRORTIMES BETWEEN ? AND ? ");
				parmList.add(conditionMap.get("errortimesstart"));
				parmList.add(conditionMap.get("errortimesend"));
			}else if(conditionMap.get("errortimesstart")!=null && conditionMap.get("errortimesend")==null){
				sbSql.append(" AND FLDERRORTIMES >= ?");
				parmList.add(conditionMap.get("errortimesstart"));
			}else if(conditionMap.get("testtimesstart")==null && conditionMap.get("errortimesend")!=null){
				sbSql.append(" AND FLDERRORTIMES <= ?");
				parmList.add(conditionMap.get("errortimesend"));
			}
		}
		
		if(conditionMap.get("errortest")!=null){ //	FLDERRORFAG	VARCHAR	10	Yes
			sbSql.append(" AND FLDERRORFAG = ? ");
			if(conditionMap.get("errortest").toString().equalsIgnoreCase("true")){
				parmList.add("1");
			}else{
				parmList.add("0");
			}
		}	
		
		if(conditionMap.get("forgettag")!=null){ //	FLDFORGETAG	
			sbSql.append(" AND FLDFORGETTAG = ? ");
			if(conditionMap.get("forgettag").toString().equalsIgnoreCase("true")){
				parmList.add("1");
			}else{
				parmList.add("0");
			}
		}	
		
		if(conditionMap.get("wordcnkey")!=null){//	FLDWORDEN	VARCHAR	50	No
			sbSql.append(" AND FLDWORDCN like ? ");
			parmList.add("%" + conditionMap.get("wordcnkey") + "%");
		}
		
		if(conditionMap.get("showwords")!=null){
			if(ReadConfig.getKey("DB_DRIVER").indexOf("mysql")>=0){
				sbSql.append(" LIMIT ?");
				parmList.add(conditionMap.get("showwords"));
			}
		}
		
		
		
//		System.out.println(sbSql.toString());
		BeanListHandler<Word> wordListHandler = new BeanListHandler<Word>(Word.class);
		Connection conn = DBUtil.getConnection();
		QueryRunner queryRunner = new QueryRunner();
		try {
			resultWordList = queryRunner.query(conn, sbSql.toString(), wordListHandler, parmList.toArray());
			closeConnection(conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return resultWordList;
	}
	public boolean checkExistWord(Connection conn,Word word,boolean closeConn){
		String sql = "SELECT COUNT(FLDWORDEN) FROM " + tableName + " WHERE FLDWORDEN=?";
		boolean result = true;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, word.getFldWorden());
			rs = stmt.executeQuery();
			if(rs.next()){
				int existFlag = rs.getInt(1);
				if(existFlag==0){
					result = false;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		if(closeConn){
			DBUtil.closeAll(conn, stmt, rs);
		}else{
			DBUtil.closeAll(null, stmt, rs);
		}
		return result;
	}

	public int updateFullWordFromWordSYS(Word word){
		Connection conn = DBUtil.getConnection();
		try {
			return updateFullWordFromWordSYS(word,conn,true);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}
	
	public int updateFullWordFromWordSYS(Word word,Connection conn,boolean closeConn) throws SQLException {
		if(word == null) throw new IllegalArgumentException("parm word is null");
		int result = 0;
		QueryRunner runner = new QueryRunner();
		String sql = "UPDATE " + tableName + " SET FLDWORDEN=?,FLDWORDCN=?,FLDSYMBOL=?,FLDATTR=?,FLDLASTTESTDATE=?,FLDPRIORITY=?,"
				+ "FLDTESTTIMES=?,FLDFROM=?,FLDERRORFAG=?,FLDCREATDATE=?,FLDREMARK=?,FLDRELEVANCE=?,FLDERRORTIMES=?,FLDFORGETTAG=? WHERE FLDWORDID=?";
		result = runner.update(conn, sql
				,word.getFldWorden()
				,word.getFldWordcn()
				,word.getFldSymbol()
				,word.getFldAttr()
				,word.getFldLasttestdate()
				,word.getFldPriority()
				,word.getFldTesttimes()
				,word.getFldFrom()
				,word.getFldErrorfag()
				,word.getFldCreatdate()
				,word.getFldRemark()
				,word.getFldRelevance()
				,word.getFldErrortimes()
				,word.getFldForgettag()
				,word.getFldWordid()
				);
		if(closeConn){
			closeConnection(conn);
		}
		return result;
	}
}