REVERT TO CONSOLE

Why I post only once every 2 years…

Well, I figure I owe everyone an explanation. It’s pretty simple. I don’t really do anything interesting at the moment, so I don’t have much to share. This, my friends is as exciting as it gets.

public ArrayList<TotalByUnitVO> getTotalByUnitFilterGroup(FormPojo form) throws SQLException{
		Connepion conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String fromDt = "(select to_date(startdatetime, '" + Constants.DATE_FORMAT_MASK + "') from tbl_calendar where calendar_seq = " + form.getFiscalRangeFrom() + " )";
		String endDt = "(select to_date(enddatetime, '" + Constants.DATE_FORMAT_MASK + "') from tbl_calendar where calendar_seq = " + form.getFiscalRangeTo() + " )";
		StringParser parser = new StringParser();
		String grp1 = determineGroupBy(form, 1);
		String grp2 = determineGroupBy(form, 2);
		String line2 = "";
		if(grp2 != null && grp2.length() > 0 && !grp2.equals("NONE")) {
			line2 = ", p." + grp2;
		}
		String aggregate = "SUM";
		if(form.getAggregateFunction() != null && !form.getAggregateFunction().equals("NONE"))
			aggregate = form.getAggregateFunction();

		String sql="SELECT   " +
		"p." + grp1 +
		line2 +
		"," + aggregate + "(p.p_a) p_a" +
		"," + aggregate + "(p.p_h) p_h" +
		"," + aggregate + "(p.p_i) p_i" +
		"," + aggregate + "(p.p_l) p_l" +
		"," + aggregate + "(p.p_p) p_p" +
		"," + aggregate + "(p.p_r) p_r" +
		"," + aggregate + "(p.p_total) p_total" +
		",COUNT (distinct p.p_id) p_count" +
		" FROM p_total_by_unit p, tbl_calendar fc" +
		" WHERE p.s_dt_seq = fc.calendar_seq" +
		" AND to_date(fc.startdatetime, '" + Constants.DATE_FORMAT_MASK + "') >= " + fromDt + "" +
		" AND to_date(fc.enddatetime, '" + Constants.DATE_FORMAT_MASK + "') <= " + endDt + "";

		if(form.getPLT() != null)
			sql += " and p.plt in (" + parser.parseStringCollectionToSQLFormattedINString(form.getPLT()) + ")";
		if(form.getPB() != null)
			sql += " and p.pb in(" + parser.parseStringCollectionToSQLFormattedINString(form.getPB()) + ")";
		if(form.getPT() != null)
			sql += " and p.pt in(" + parser.parseStringCollectionToSQLFormattedINString(form.getPT()) + ")";

		sql += " GROUP BY p." + grp1;
		if(grp2 != null && grp2.length() > 0 && !grp2.equals("NONE")) {
			sql += ", p." + grp2;
		}

		System.out.println(sql);
		ArrayList<TotalByUnitVO> al = new ArrayList<TotalByUnitVO>();

     try {
         conn = DataSrc.getDataSource().getConnepion();
         pstmt = conn.prepareStatement(sql);
         rs = pstmt.executeQuery();
         int index = 0;

         while ( rs.next() ) {
         	TotalByUnitVO vo = new TotalByUnitVO();
         	index = 2;
         	if(grp2 != null && grp2.length() > 0 && !grp2.equals("NONE"))
         		index++;
         	vo.setpa(rs.getDouble(index++));
			vo.setph(rs.getDouble(index++));
			vo.setpi(rs.getDouble(index++));
			vo.setpl(rs.getDouble(index++));
			vo.setpp(rs.getDouble(index++));
			vo.setpr(rs.getDouble(index++));
         	if(grp1.equals("GR_COL_1"))
         		vo.setGroupCol1(rs.getString(1));
         	else if(grp1.equals("GR_COL_2"))
         		vo.setGroupCol2(rs.getString(1));
         	else if(grp1.equals("GR_COL_3"))
         		vo.setGroupCol3(rs.getString(1));

         	if(grp2 != null && grp2.length() > 0 && !grp2.equals("NONE")) {
				if(grp1.equals("GR_COL_1"))
					vo.setGroupCol1(rs.getString(2));
				else if(grp1.equals("GR_COL_2"))
					vo.setGroupCol2(rs.getString(2));
				else if(grp1.equals("GR_COL_3"))
					vo.setGroupCol3(rs.getString(2));
         	}

         	al.add(vo);
         }
         if ( rs != null ) rs.close();
         if ( pstmt != null ) pstmt.close();
         if ( conn != null ) conn.close();
     } catch ( SQLException se ) {
     	// throw exception for jsp to handle...
     	throw (SQLException) new SQLException(se.getLocalizedMessage()).initCause(se);
     } finally {
         try {
             if ( rs != null ) rs.close();
             if ( pstmt != null ) pstmt.close();
             if ( conn != null ) conn.close();
         } catch ( SQLException se ) {
         	// throw exception for jsp to handle...
         	throw (SQLException) new SQLException(se.getLocalizedMessage()).initCause(se);
         }
     }
     return al;
}

So there it is, some SQL with dynamic aggregate functions and group by’s. And it’s nice and dirty. There’s no hibernate, jdbc template, or any attempt to have a solid reusable design. It’s just plain jane shoot em up cowboy code. That’s what I do these days. It’s all about quantity, not quality. Which is why most of my time is spent fixing crappy code like this. Actually, this code is very good compared to what I usually deal with. I honestly don’t know how some “programmers” can pull the wool over people’s eyes for so long. These are the “programmers” that suffer from digital diarrhea. That is to say, nothing but streams of crap comes out of their fingers when they write code. One can also infer then, that in order to land and keep the jobs they have, they must suffer from oral diarrhea.

Well, until 2010.

-eokuwwy