public static String getSumExhibitQty(String track_no) throws Exception { String ret = ""; DB db = new DB(); try { AppArrestProveExhibit aae = new AppArrestProveExhibit(db); String sql = ""; sql += "select sum(aae.qty) qty, nvl(aae.qty_unit_name,u.thname) unit_name, count(aae.id) count_qty"; sql += " from application_arrest_exhibit aae "; sql += " left join unit u on u.unit_code = aae.qty_unit_code"; sql += " where aae.track_no = '" + track_no + "' "; sql += " group by nvl(aae.qty_unit_name,u.thname) "; sql += " order by nvl(aae.qty_unit_name,u.thname) "; // System.out.println(sql); // Test track_no = TN4900000042400 List<Map<String, Object>> list = aae.findBySql(sql); if (list.size() > 1) { int qty = 0; for (int i = 0; i < list.size(); i++) { qty += (list.get(i).get("count_qty") != null ? new Integer(list.get(i).get("count_qty").toString()) : null); } ret = Integer.toString(qty) + " รายการ"; } else { for (int i = 0; i < list.size(); i++) { String qty = NumberUtil.getNumberFormat(new Double(list.get(i).get("qty").toString()), 0, ""); if (!ret.equals("")) { ret += "\n" + qty + " " + (list.get(i).get("unit_name") != null ? list.get(i).get("unit_name").toString() : ""); } else { ret = qty + " " + (list.get(i).get("unit_name") != null ? list.get(i).get("unit_name").toString() : ""); } } } } catch (Exception e) { e.printStackTrace(); } finally { db.closedb(); } return ret; }
public static String getSumP038Alcohol(String track_no) throws Exception { String ret = ""; DB db = new DB(); AppArrestProveExhibit aae = new AppArrestProveExhibit(db); String sql = ""; sql += "select sum(aap.netweight) netweight, nvl(aap.netweight_unit_name,u.thname) unit_name, aa.legislation_id"; sql += " from app_arrest_prove_exhibit aap "; sql += " left join unit u on u.unit_code = aap.netweight_unit_code"; sql += " inner join application_arrest aa on aa.track_no=aap.track_no"; sql += " where aap.track_no = '" + track_no + "' "; sql += " group by nvl(aap.netweight_unit_name,u.thname), aa.legislation_id "; sql += " order by nvl(aap.netweight_unit_name,u.thname) "; // Test track_no = TN4900000042400 List<Map<String, Object>> list = aae.findBySql(sql); String legislation_alcohol = ConfigUtil.getConfig("legislation_alcohol"); if (list != null && list.size() > 0 && list.get(0).get("legislation_id").toString().equals(legislation_alcohol)) { for (int i = 0; i < list.size(); i++) { String qty = NumberUtil.getNumberFormat(new Double(list.get(i).get("netweight").toString()), 3, "0"); if (!ret.equals("")) { // ret += "\n" + list.get(i).get("netweight").toString() + " " + // list.get(i).get("unit_name").toString(); ret += "\n" + qty + " " + list.get(i).get("unit_name").toString(); } else { // ret = list.get(i).get("netweight").toString() + " " + // list.get(i).get("unit_name").toString(); ret = qty + " " + list.get(i).get("unit_name").toString(); } } } else ret = ""; return ret; }