public static int serDiccGroupByToWriter( ResultSet rs, Writer writer, int maxRows, String idPor, String[] idAcumulados, String campoAcumuladoNombre) { int rowsCount = 0; try { ArrayList<String> acumulado = null; String idActual = null; StringBuilder reg = null; reg = new StringBuilder(); String value = ""; if (rs != null) { ResultSetMetaData rsm = rs.getMetaData(); int countCol = rsm.getColumnCount(); String name = ""; for (int i = 1; i <= countCol; i++) { name = rsm.getColumnName(i); reg.append(name.toLowerCase()).append("\t"); } reg.append(campoAcumuladoNombre); writer.write(reg.toString() + EOL); while (rs.next()) { if (idActual == null) { reg = new StringBuilder(); acumulado = new ArrayList<String>(); idActual = rs.getString(idPor); for (int i = 1; i <= countCol; i++) { reg.append(rs.getString(i)).append("\t"); } for (String id : idAcumulados) { value = rs.getString(id); if (!rs.wasNull()) { acumulado.add(rs.getString(id)); } } } else { if (idActual.equals(rs.getString(idPor))) { for (String id : idAcumulados) { value = rs.getString(id); if (!rs.wasNull()) { acumulado.add(rs.getString(id)); } } } else { if (acumulado.size() > 0) { for (String str : acumulado) { reg.append(str).append(","); } reg.deleteCharAt(reg.length() - 1); } reg.append(EOL); writer.write(reg.toString()); rowsCount++; if (maxRows == rowsCount) { break; } idActual = rs.getString(idPor); reg = new StringBuilder(); acumulado = new ArrayList<String>(); for (int i = 1; i <= countCol; i++) { reg.append(rs.getString(i)).append("\t"); } for (String id : idAcumulados) { value = rs.getString(id); if (!rs.wasNull()) { acumulado.add(rs.getString(id)); } } } } } if (acumulado.size() > 0) { for (String str : acumulado) { reg.append(str).append(","); } reg.deleteCharAt(reg.length() - 1); } reg.append(EOL); writer.write(reg.toString()); rowsCount++; } } catch (SQLException e) { logm("ERR", 1, "Error al escribir registros", e); } catch (IOException e) { logm("ERR", 1, "Error al escribir registros", e); } return rowsCount; }
/** * Get Restriction Lines * * @param reload reload data * @return array of lines */ public MGoalRestriction[] getRestrictions(boolean reload) { if (m_restrictions != null && !reload) return m_restrictions; ArrayList<MGoalRestriction> list = new ArrayList<MGoalRestriction>(); // String sql = "SELECT * FROM PA_GoalRestriction " + "WHERE PA_Goal_ID=? AND IsActive='Y' " + "ORDER BY Org_ID, C_BPartner_ID, M_Product_ID"; PreparedStatement pstmt = null; ResultSet rs = null; try { pstmt = DB.prepareStatement(sql, get_Trx()); pstmt.setInt(1, getPA_Goal_ID()); rs = pstmt.executeQuery(); while (rs.next()) list.add(new MGoalRestriction(getCtx(), rs, get_Trx())); } catch (Exception e) { log.log(Level.SEVERE, sql, e); } finally { DB.closeStatement(pstmt); DB.closeResultSet(rs); } // m_restrictions = new MGoalRestriction[list.size()]; list.toArray(m_restrictions); return m_restrictions; } // getRestrictions
/** * Retrieves a List of Discussion Posts for a given Thread Id * * @param threadId The Id of the Thread to query * @return A List of Discussion Posts for the Thread */ public List<DiscussionPost> getPosts(int threadId) { ArrayList<DiscussionPost> posts = new ArrayList<>(); try { // Create a prepared statement PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM DiscussionPosts WHERE ThreadId = ?"); // Set the required parameters adn execute pstmt.setInt(1, threadId); ResultSet rs = pstmt.executeQuery(); // Retrieve the results and add to the list if (rs.isBeforeFirst()) { while (!rs.isAfterLast()) { DiscussionPost post = DiscussionPost.fromResultSet(rs); if (post != null) posts.add(post); } } } catch (Exception e) { logger.log(Level.SEVERE, "SQL Error", e); } return posts; }
/** * Get Accessible Goals * * @param ctx context * @return array of goals */ public static MGoal[] getGoals(Ctx ctx) { ArrayList<MGoal> list = new ArrayList<MGoal>(); String sql = "SELECT * FROM PA_Goal WHERE IsActive='Y' " + "ORDER BY SeqNo"; sql = MRole.getDefault(ctx, false) .addAccessSQL(sql, "PA_Goal", false, true); // RW to restrict Access PreparedStatement pstmt = null; ResultSet rs = null; try { pstmt = DB.prepareStatement(sql, (Trx) null); rs = pstmt.executeQuery(); while (rs.next()) { MGoal goal = new MGoal(ctx, rs, null); goal.updateGoal(false); list.add(goal); } } catch (Exception e) { s_log.log(Level.SEVERE, sql, e); } finally { DB.closeStatement(pstmt); DB.closeResultSet(rs); } MGoal[] retValue = new MGoal[list.size()]; list.toArray(retValue); return retValue; } // getGoals
/** * ************************************************************************ Create Missing * Document Types * * @param ctx context * @param AD_Client_ID client * @param sp server process * @param trx transaction */ public static void createDocumentTypes(Ctx ctx, int AD_Client_ID, SvrProcess sp, Trx trx) { s_log.info("AD_Client_ID=" + AD_Client_ID); String sql = "SELECT rl.Value, rl.Name " + "FROM AD_Ref_List rl " + "WHERE rl.AD_Reference_ID=183" + " AND rl.IsActive='Y' AND NOT EXISTS " + " (SELECT * FROM C_DocType dt WHERE dt.AD_Client_ID=? AND rl.Value=dt.DocBaseType)"; PreparedStatement pstmt = null; ResultSet rs = null; try { pstmt = DB.prepareStatement(sql, trx); pstmt.setInt(1, AD_Client_ID); rs = pstmt.executeQuery(); while (rs.next()) { String name = rs.getString(2); String value = rs.getString(1); s_log.config(name + "=" + value); MDocType dt = new MDocType(ctx, value, name, trx); if (dt.save()) { if (sp != null) sp.addLog(0, null, null, name); else s_log.fine(name); } else { if (sp != null) sp.addLog(0, null, null, "Not created: " + name); else s_log.warning("Not created: " + name); } } } catch (Exception e) { s_log.log(Level.SEVERE, sql, e); } finally { DB.closeResultSet(rs); DB.closeStatement(pstmt); } } // createDocumentTypes
/** * Returns the previous period * * @param period MPeriod * @param periodCount Count * @param trx trx * @param ctx Ctx * @return MPeriod */ public static MPeriod getPreviousPeriod(MPeriod period, Ctx ctx, Trx trx) { MPeriod newPeriod = null; String sql = "SELECT * FROM C_Period WHERE " + "C_Period.IsActive='Y' AND PeriodType='S' " + "AND C_Period.C_Year_ID IN " + "(SELECT C_Year_ID FROM C_Year WHERE C_Year.C_Calendar_ID = ? ) " + "AND ((C_Period.C_Year_ID * 1000) + C_Period.PeriodNo) " + " < ((? * 1000) + ?) ORDER BY C_Period.C_Year_ID DESC, C_Period.PeriodNo DESC"; PreparedStatement pstmt = null; ResultSet rs = null; try { pstmt = DB.prepareStatement(sql, trx); pstmt.setInt(1, period.getC_Calendar_ID()); pstmt.setInt(2, period.getC_Year_ID()); pstmt.setInt(3, period.getPeriodNo()); rs = pstmt.executeQuery(); if (rs.next()) newPeriod = new MPeriod(ctx, rs, trx); } catch (Exception e) { s_log.log(Level.SEVERE, sql, e); } finally { DB.closeResultSet(rs); DB.closeStatement(pstmt); } return newPeriod; }
/** * Find first Year Period of DateAcct based on Client Calendar * * @param ctx context * @param C_Calendar_ID calendar * @param DateAcct date * @return active first Period */ public static MPeriod getFirstInYear(Ctx ctx, int C_Calendar_ID, Timestamp DateAcct) { MPeriod retValue = null; String sql = "SELECT * " + "FROM C_Period " + "WHERE C_Year_ID IN " + "(SELECT p.C_Year_ID " + "FROM C_Year y" + " INNER JOIN C_Period p ON (y.C_Year_ID=p.C_Year_ID) " + "WHERE y.C_Calendar_ID=?" + " AND ? BETWEEN StartDate AND EndDate)" + " AND IsActive='Y' AND PeriodType='S' " + "ORDER BY StartDate"; PreparedStatement pstmt = null; ResultSet rs = null; try { pstmt = DB.prepareStatement(sql, (Trx) null); pstmt.setInt(1, C_Calendar_ID); pstmt.setTimestamp(2, DateAcct); rs = pstmt.executeQuery(); if (rs.next()) // first only retValue = new MPeriod(ctx, rs, null); } catch (SQLException e) { s_log.log(Level.SEVERE, sql, e); } finally { DB.closeStatement(pstmt); DB.closeResultSet(rs); } return retValue; } // getFirstInYear
/** * Find all the year records in a Calendar, it need not be a standard period (used in MRP) * * @param C_Calendar_ID calendar * @param ctx context * @param trx trx * @return MYear[] */ public static MYear[] getAllYearsInCalendar(int C_Calendar_ID, Ctx ctx, Trx trx) { List<MYear> years = new ArrayList<MYear>(); String sql = "SELECT * FROM C_Year WHERE " + "IsActive='Y' AND C_Calendar_ID = ? "; PreparedStatement pstmt = null; ResultSet rs = null; try { pstmt = DB.prepareStatement(sql, trx); pstmt.setInt(1, C_Calendar_ID); rs = pstmt.executeQuery(); while (rs.next()) years.add(new MYear(ctx, rs, trx)); } catch (Exception e) { s_log.log(Level.SEVERE, sql, e); } finally { DB.closeResultSet(rs); DB.closeStatement(pstmt); } MYear[] retValue = new MYear[years.size()]; years.toArray(retValue); return retValue; }
/** * Find the periods in a calendar year it need not be a standard period (used in MRP) * * @param C_Year_ID Year * @param periodType Period Type * @param ctx context * @param trx trx * @return MPeriod[] */ public static MPeriod[] getAllPeriodsInYear(int C_Year_ID, String periodType, Ctx ctx, Trx trx) { List<MPeriod> periods = new ArrayList<MPeriod>(); String sql = "SELECT * FROM C_Period WHERE IsActive='Y'"; sql = sql + " AND C_Year_ID = ?"; if (periodType != null) sql = sql + " AND PeriodType = ? "; sql = sql + " order by StartDate "; PreparedStatement pstmt = null; ResultSet rs = null; try { pstmt = DB.prepareStatement(sql, trx); pstmt.setInt(1, C_Year_ID); if (periodType != null) pstmt.setString(2, periodType); rs = pstmt.executeQuery(); while (rs.next()) periods.add(new MPeriod(ctx, rs, trx)); } catch (Exception e) { s_log.log(Level.SEVERE, sql, e); } finally { DB.closeResultSet(rs); DB.closeStatement(pstmt); } MPeriod[] retValue = new MPeriod[periods.size()]; periods.toArray(retValue); return retValue; }
/** * Retrieves all of the Discussion Threads associated with a Group Id * * @param groupId The Group Id to get Threads for * @return A List of Discussion Threads that belong to the Group */ public List<DiscussionThread> getThreads(int groupId) { ArrayList<DiscussionThread> threads = new ArrayList<>(); try { // Create a prepared statement PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM DiscussionThreads WHERE GroupId = ?"); // Set the required parameters and execute pstmt.setInt(1, groupId); ResultSet rs = pstmt.executeQuery(); // Get the results and add to the list if (rs.isBeforeFirst()) { while (!rs.isAfterLast()) { DiscussionThread thread = DiscussionThread.fromResultSet(rs); if (thread != null) { threads.add(thread); } } } } catch (Exception e) { logger.log(Level.SEVERE, "SQL Error", e); } return threads; }
private void fullAssociated() { boolean associated = true; String SQL = "Select * " + "from XX_VMR_REFERENCEMATRIX " + "where M_product IS NULL AND XX_VMR_PO_LINEREFPROV_ID=" + LineRefProv.get_ID(); try { PreparedStatement pstmt = DB.prepareStatement(SQL, null); ResultSet rs = pstmt.executeQuery(); while (rs.next()) { associated = false; } rs.close(); pstmt.close(); } catch (Exception a) { log.log(Level.SEVERE, SQL, a); } if (associated == true) { String SQL10 = "UPDATE XX_VMR_PO_LineRefProv " + " SET XX_ReferenceIsAssociated='Y'" + " WHERE XX_VMR_PO_LineRefProv_ID=" + LineRefProv.getXX_VMR_PO_LineRefProv_ID(); DB.executeUpdate(null, SQL10); // LineRefProv.setXX_ReferenceIsAssociated(true); // LineRefProv.save(); int dialog = Env.getCtx().getContextAsInt("#Dialog_Associate_Aux"); if (dialog == 1) { ADialog.info(m_WindowNo, m_frame, "MustRefresh"); Env.getCtx().remove("#Dialog_Associate_Aux"); } } else { String SQL10 = "UPDATE XX_VMR_PO_LineRefProv " + " SET XX_ReferenceIsAssociated='N'" + " WHERE XX_VMR_PO_LineRefProv_ID=" + LineRefProv.getXX_VMR_PO_LineRefProv_ID(); DB.executeUpdate(null, SQL10); // LineRefProv.setXX_ReferenceIsAssociated(false); // LineRefProv.save(); } }
/** * Find standard Period of DateAcct based on Client Calendar * * @param ctx context * @param C_Calendar_ID calendar * @param DateAcct date * @return active Period or null */ public static MPeriod getOfCalendar(Ctx ctx, int C_Calendar_ID, Timestamp DateAcct) { if (DateAcct == null) { s_log.warning("No DateAcct"); return null; } if (C_Calendar_ID == 0) { s_log.warning("No Calendar"); return null; } // Search in Cache first Iterator<MPeriod> it = s_cache.values().iterator(); while (it.hasNext()) { MPeriod period = it.next(); if (period.getC_Calendar_ID() == C_Calendar_ID && period.isStandardPeriod() && period.isInPeriod(DateAcct)) return period; } // Get it from DB MPeriod retValue = null; String sql = "SELECT * FROM C_Period " + "WHERE C_Year_ID IN " + "(SELECT C_Year_ID FROM C_Year WHERE C_Calendar_ID=?)" + " AND ? BETWEEN TRUNC(StartDate,'DD') AND TRUNC(EndDate,'DD')" + " AND IsActive='Y' AND PeriodType='S'"; PreparedStatement pstmt = null; ResultSet rs = null; try { pstmt = DB.prepareStatement(sql, (Trx) null); pstmt.setInt(1, C_Calendar_ID); pstmt.setTimestamp(2, TimeUtil.getDay(DateAcct)); rs = pstmt.executeQuery(); while (rs.next()) { MPeriod period = new MPeriod(ctx, rs, null); Integer key = Integer.valueOf(period.getC_Period_ID()); s_cache.put(key, period); if (period.isStandardPeriod()) retValue = period; } } catch (SQLException e) { s_log.log(Level.SEVERE, "DateAcct=" + DateAcct, e); } finally { DB.closeStatement(pstmt); DB.closeResultSet(rs); } if (retValue == null) s_log.warning( "No Standard Period for " + DateAcct + " (C_Calendar_ID=" + C_Calendar_ID + ")"); return retValue; } // get
/** * Creates a Discussion Thread in the database * * @param discussion The Discussion to insert */ public void createDiscussion(DiscussionThread discussion) { try { // Create a prepared statement PreparedStatement pstmt = conn.prepareStatement( "INSERT INTO DiscussionThreads (GroupId, ThreadName)" + "VALUES (?, ?)", Statement.RETURN_GENERATED_KEYS); // Set the required parameters and execute pstmt.setInt(1, discussion.getGroupId()); pstmt.setString(2, discussion.getThreadName()); pstmt.executeUpdate(); // Get the generated id ResultSet rs = pstmt.getGeneratedKeys(); if (rs.next()) discussion.setId(rs.getInt(1)); } catch (Exception e) { logger.log(Level.SEVERE, "SQL Error", e); } }
/** * Get Goals with Measure * * @param ctx context * @param PA_Measure_ID measure * @return goals */ public static MGoal[] getMeasureGoals(Ctx ctx, int PA_Measure_ID) { ArrayList<MGoal> list = new ArrayList<MGoal>(); String sql = "SELECT * FROM PA_Goal WHERE IsActive='Y' AND PA_Measure_ID=? " + "ORDER BY SeqNo"; PreparedStatement pstmt = null; ResultSet rs = null; try { pstmt = DB.prepareStatement(sql, (Trx) null); pstmt.setInt(1, PA_Measure_ID); rs = pstmt.executeQuery(); while (rs.next()) list.add(new MGoal(ctx, rs, null)); } catch (Exception e) { s_log.log(Level.SEVERE, sql, e); } finally { DB.closeStatement(pstmt); DB.closeResultSet(rs); } MGoal[] retValue = new MGoal[list.size()]; list.toArray(retValue); return retValue; } // getMeasureGoals
/** * gets all Periods in the Range * * @param startPeriod * @param endPeriod * @param calendar_ID * @return MPeriod[] */ public static MPeriod[] getAllPeriodsInRange( MPeriod startPeriod, MPeriod endPeriod, int calendar_ID, Ctx ctx, Trx trx) { if ((startPeriod.getC_Calendar_ID() != calendar_ID) || (endPeriod.getC_Calendar_ID() != calendar_ID)) { log.saveError("Error", "Periods do not belong to the calendar"); return null; } ArrayList<MPeriod> periods = new ArrayList<MPeriod>(); String sql = "SELECT * FROM C_Period WHERE " + "C_Period.IsActive='Y' AND PeriodType='S' " + "AND C_Period.C_Year_ID IN " + "(SELECT C_Year_ID FROM C_Year WHERE C_Year.C_Calendar_ID = ? ) " + // calendar_ID "AND ((C_Period.C_Year_ID * 1000) + C_Period.PeriodNo) BETWEEN" + " (? * 1000 + ?) AND (? * 1000 + ? )" + // start Period year ID, Period Number , End Period Year ID, Period Number " ORDER BY C_Period.C_Year_ID ASC, C_Period.PeriodNo ASC"; PreparedStatement pstmt = null; ResultSet rs = null; try { pstmt = DB.prepareStatement(sql, trx); pstmt.setInt(1, calendar_ID); pstmt.setInt(2, startPeriod.getC_Year_ID()); pstmt.setInt(3, startPeriod.getPeriodNo()); pstmt.setInt(4, endPeriod.getC_Year_ID()); pstmt.setInt(5, endPeriod.getPeriodNo()); rs = pstmt.executeQuery(); while (rs.next()) periods.add(new MPeriod(ctx, rs, trx)); } catch (Exception e) { s_log.log(Level.SEVERE, sql, e); } finally { DB.closeResultSet(rs); DB.closeStatement(pstmt); } MPeriod[] retValue = new MPeriod[periods.size()]; periods.toArray(retValue); return retValue; }
/** * Get User Goals * * @param ctx context * @param AD_User_ID user * @return array of goals */ public static MGoal[] getUserGoals(Ctx ctx) { int AD_Role_ID = ctx.getAD_Role_ID(); MRole role = MRole.get(ctx, AD_Role_ID); int AD_User_ID = ctx.getAD_User_ID(); if (AD_User_ID < 0) return getTestGoals(ctx); ArrayList<MGoal> list = new ArrayList<MGoal>(); String sql = "SELECT * FROM PA_Goal g " + "WHERE IsActive='Y'" + " AND AD_Client_ID=?" // #1 + " AND ("; if (!role.isWebStoreRole()) sql += " (AD_User_ID IS NULL AND AD_Role_ID IS NULL) OR "; sql += " AD_User_ID=?" // #2 + " OR EXISTS (SELECT * FROM AD_User_Roles ur " + "WHERE ?=ur.AD_User_ID AND g.AD_Role_ID=ur.AD_Role_ID AND ur.IsActive='Y')) " + "ORDER BY SeqNo"; PreparedStatement pstmt = null; ResultSet rs = null; try { pstmt = DB.prepareStatement(sql, (Trx) null); pstmt.setInt(1, ctx.getAD_Client_ID()); pstmt.setInt(2, AD_User_ID); pstmt.setInt(3, AD_User_ID); rs = pstmt.executeQuery(); while (rs.next()) { MGoal goal = new MGoal(ctx, rs, null); goal.updateGoal(false); list.add(goal); } } catch (Exception e) { s_log.log(Level.SEVERE, sql, e); } finally { DB.closeResultSet(rs); DB.closeStatement(pstmt); } MGoal[] retValue = new MGoal[list.size()]; list.toArray(retValue); return retValue; } // getUserGoals
/** * Load Regions (cached) * * @param ctx context */ private static void loadAllRegions(Ctx ctx) { String sql = "SELECT * FROM C_Region WHERE IsActive='Y'"; PreparedStatement pstmt = null; ResultSet rs = null; try { pstmt = DB.prepareStatement(sql, (Trx) null); rs = pstmt.executeQuery(); while (rs.next()) { MRegion r = new MRegion(ctx, rs, null); s_regions.put(String.valueOf(r.getC_Region_ID()), r); if (r.isDefault()) s_default = r; } } catch (SQLException e) { s_log.log(Level.SEVERE, sql, e); } finally { DB.closeResultSet(rs); DB.closeStatement(pstmt); } s_log.fine(s_regions.size() + " - default=" + s_default); } // loadAllRegions
/** * Creates a Discussion Post in the database * * @param post The Discussion Post to insert */ public void createPost(DiscussionPost post) { try { // Create a prepared statement PreparedStatement pstmt = conn.prepareStatement( "INSERT INTO DiscussionPosts (ThreadId, UserId, Message)" + "VALUES (?, ?, ?)", Statement.RETURN_GENERATED_KEYS); // Set the required parameters and execute pstmt.setInt(1, post.getThreadId()); pstmt.setInt(2, post.getUserId()); pstmt.setString(3, post.getMessage()); pstmt.executeUpdate(); // get the generated id ResultSet rs = pstmt.getGeneratedKeys(); if (rs.next()) post.setId(rs.getInt(1)); } catch (Exception e) { logger.log(Level.SEVERE, "SQL Error", e); } }
/** * Get Period Control * * @param requery requery * @return period controls */ public MPeriodControl[] getPeriodControls(boolean requery) { if (m_controls != null && !requery) return m_controls; // ArrayList<MPeriodControl> list = new ArrayList<MPeriodControl>(); String sql = "SELECT * FROM C_PeriodControl " + "WHERE C_Period_ID=?"; PreparedStatement pstmt = null; ResultSet rs = null; try { pstmt = DB.prepareStatement(sql, get_Trx()); pstmt.setInt(1, getC_Period_ID()); rs = pstmt.executeQuery(); while (rs.next()) list.add(new MPeriodControl(getCtx(), rs, get_Trx())); } catch (Exception e) { log.log(Level.SEVERE, sql, e); } finally { DB.closeResultSet(rs); DB.closeStatement(pstmt); } m_controls = new MPeriodControl[list.size()]; list.toArray(m_controls); return m_controls; } // getPeriodControls
/** * verifica si hay productos asociados * * @param table table */ private boolean verify() { String sql = "SELECT XX_VMR_ReferenceMatrix_ID " + "FROM XX_VMR_ReferenceMatrix " + "WHERE XX_VMR_PO_LINEREFPROV_ID=" + (Integer) LineRefProv.getXX_VMR_PO_LineRefProv_ID(); PreparedStatement pstmt = null; ResultSet rs = null; try { pstmt = DB.prepareStatement(sql, null); rs = pstmt.executeQuery(); while (rs.next()) { associatedReference_ID = rs.getInt("XX_VMR_ReferenceMatrix_ID"); rs.close(); pstmt.close(); return true; } } catch (SQLException e) { log.log(Level.SEVERE, sql, e); } finally { try { rs.close(); } catch (SQLException e1) { e1.printStackTrace(); } try { pstmt.close(); } catch (SQLException e) { e.printStackTrace(); } } return false; } // tableLoad
/** * Find Period of Date based on Client Calendar, it need not be a standard period (used in MRP) * * @param ctx context * @param C_Calendar_ID calendar * @param Date date * @param trx trx * @return active Period or null */ public static MPeriod getPeriod(Ctx ctx, int C_Calendar_ID, Timestamp Date, Trx trx) { if (Date == null) { s_log.warning("No Date"); return null; } if (C_Calendar_ID == 0) { s_log.warning("No Calendar"); return null; } // Get it from DB PreparedStatement pstmt = null; ResultSet rs = null; MPeriod retValue = null; String sql = "SELECT * FROM C_Period " + "WHERE C_Year_ID IN " + "(SELECT C_Year_ID FROM C_Year WHERE C_Calendar_ID=?)" + " AND ? BETWEEN TRUNC(StartDate,'DD') AND TRUNC(EndDate,'DD')" + " AND IsActive='Y' "; try { pstmt = DB.prepareStatement(sql, trx); pstmt.setInt(1, C_Calendar_ID); pstmt.setTimestamp(2, TimeUtil.getDay(Date)); rs = pstmt.executeQuery(); if (rs.next()) { retValue = new MPeriod(ctx, rs, trx); } } catch (SQLException e) { s_log.log(Level.SEVERE, "DateAcct=" + Date, e); } finally { DB.closeResultSet(rs); DB.closeStatement(pstmt); } if (retValue == null) s_log.warning("No Period for " + Date + " (C_Calendar_ID=" + C_Calendar_ID + ")"); return retValue; } // getPeriod
private boolean isInMatrix() { String SQL = "Select * " + "from XX_VMR_REFERENCEMATRIX " + "where XX_VMR_PO_LINEREFPROV_ID=" + LineRefProv.get_ID(); PreparedStatement pstmt = null; ResultSet rs = null; try { pstmt = DB.prepareStatement(SQL, null); rs = pstmt.executeQuery(); while (rs.next()) { return true; } } catch (Exception a) { log.log(Level.SEVERE, SQL, a); } finally { try { rs.close(); } catch (SQLException e1) { e1.printStackTrace(); } try { pstmt.close(); } catch (SQLException e) { e.printStackTrace(); } } return false; }
public static String[] dbRsColumnNames(ResultSet resultset) throws SQLException { java.util.Vector<String> rv = new java.util.Vector<String>(); if (resultset != null) { logm("DBG", 9, "ColumnCount=0 Calculando cantidad columnas desde metadata", null); ResultSetMetaData md = resultset.getMetaData(); int columnCount = md.getColumnCount(); logm("DBG", 9, "ColumnCount", columnCount); for (int i = 1; i <= columnCount; i++) { rv.add(md.getColumnName(i).toLowerCase()); } } String[] r = rv.toArray(new String[0]); logm("DBG", 9, "DB dbRsColumnNames", r); return r; }
/** * Set Resource Assignment - Callout * * @param oldS_ResourceAssignment_ID old value * @param newS_ResourceAssignment_ID new value * @param windowNo window * @throws Exception */ @UICallout public void setS_ResourceAssignment_ID( String oldS_ResourceAssignment_ID, String newS_ResourceAssignment_ID, int windowNo) throws Exception { if (newS_ResourceAssignment_ID == null || newS_ResourceAssignment_ID.length() == 0) return; int S_ResourceAssignment_ID = Integer.parseInt(newS_ResourceAssignment_ID); if (S_ResourceAssignment_ID == 0) return; // super.setS_ResourceAssignment_ID(S_ResourceAssignment_ID); int M_Product_ID = 0; String Name = null; String Description = null; BigDecimal Qty = null; String sql = "SELECT p.M_Product_ID, ra.Name, ra.Description, ra.Qty " + "FROM S_ResourceAssignment ra" + " INNER JOIN M_Product p ON (p.S_Resource_ID=ra.S_Resource_ID) " + "WHERE ra.S_ResourceAssignment_ID=?"; PreparedStatement pstmt = null; ResultSet rs = null; try { pstmt = DB.prepareStatement(sql, get_Trx()); pstmt.setInt(1, S_ResourceAssignment_ID); rs = pstmt.executeQuery(); if (rs.next()) { M_Product_ID = rs.getInt(1); Name = rs.getString(2); Description = rs.getString(3); Qty = rs.getBigDecimal(4); } } catch (SQLException e) { log.log(Level.SEVERE, sql, e); } finally { DB.closeResultSet(rs); DB.closeStatement(pstmt); } log.fine( "S_ResourceAssignment_ID=" + S_ResourceAssignment_ID + " - M_Product_ID=" + M_Product_ID); if (M_Product_ID != 0) { setM_Product_ID(M_Product_ID); if (Description != null) Name += " (" + Description + ")"; if (!".".equals(Name)) setDescription(Name); if (Qty != null) setQty(Qty); } } // setS_ResourceAssignment_ID
/** Process Button Pressed - Process Matching */ private void cmd_newProduct() { // Selecciono el departamento int depart = 0; String SQL = "Select XX_VMR_DEPARTMENT_ID " + "from XX_VMR_VENDORPRODREF " + "where XX_VMR_VENDORPRODREF_ID=" + LineRefProv.getXX_VMR_VendorProdRef_ID(); try { PreparedStatement pstmt = DB.prepareStatement(SQL, null); ResultSet rs = pstmt.executeQuery(); while (rs.next()) { depart = rs.getInt("XX_VMR_DEPARTMENT_ID"); } rs.close(); pstmt.close(); } catch (Exception a) { log.log(Level.SEVERE, SQL, a); } MVMRVendorProdRef vendorProdRef = new MVMRVendorProdRef(Env.getCtx(), LineRefProv.getXX_VMR_VendorProdRef_ID(), null); if (vendorProdRef.getXX_VMR_ProductClass_ID() > 0 && vendorProdRef.getXX_VMR_TypeLabel_ID() > 0) { MOrder order = new MOrder(Env.getCtx(), LineRefProv.getC_Order_ID(), null); // Selecciono el departamento X_XX_VMR_Department dept = new X_XX_VMR_Department(Env.getCtx(), vendorProdRef.getXX_VMR_Department_ID(), null); int category = dept.getXX_VMR_Category_ID(); // Selecciono la línea X_XX_VMR_Line line = new X_XX_VMR_Line(Env.getCtx(), vendorProdRef.getXX_VMR_Line_ID(), null); int typeInventory = line.getXX_VMR_TypeInventory_ID(); MProduct newProduct = new MProduct( Env.getCtx(), vendorProdRef.getXX_VMR_Department_ID(), vendorProdRef.getXX_VMR_Line_ID(), vendorProdRef.getXX_VMR_Section_ID(), vendorProdRef.get_ID(), vendorProdRef.getC_TaxCategory_ID(), vendorProdRef.getXX_VME_ConceptValue_ID(), typeInventory, null); // Se buscará si por la referencia para producto ya existe para asignarle el Tipo de // Exhibición String sql = "select * from M_Product where XX_VMR_DEPARTMENT_ID = " + vendorProdRef.getXX_VMR_Department_ID() + " and " + "XX_VMR_LINE_ID = " + vendorProdRef.getXX_VMR_Line_ID() + " and XX_VMR_SECTION_ID = " + vendorProdRef.getXX_VMR_Section_ID() + " and " + "XX_VMR_VendorProdRef_id = " + vendorProdRef.getXX_VMR_VendorProdRef_ID() + " order by M_Product_ID desc"; PreparedStatement pstmt = DB.prepareStatement(sql, null); ResultSet rs = null; try { rs = pstmt.executeQuery(); if (rs.next()) newProduct.setXX_VMR_TypeExhibition_ID(rs.getInt("XX_VMR_TypeExhibition_ID")); } catch (SQLException e) { e.printStackTrace(); } finally { DB.closeResultSet(rs); DB.closeStatement(pstmt); } if (vendorProdRef.getXX_VMR_Section_ID() > 0) { X_XX_VMR_Section section = new X_XX_VMR_Section(Env.getCtx(), vendorProdRef.getXX_VMR_Section_ID(), null); newProduct.setName(section.getName()); } else { newProduct.setName(vendorProdRef.getName()); } newProduct.setXX_VMR_Category_ID(category); newProduct.setXX_VMR_LongCharacteristic_ID(vendorProdRef.getXX_VMR_LongCharacteristic_ID()); newProduct.setXX_VMR_Brand_ID(vendorProdRef.getXX_VMR_Brand_ID()); newProduct.setXX_VMR_UnitConversion_ID(vendorProdRef.getXX_VMR_UnitConversion_ID()); newProduct.setXX_PiecesBySale_ID(vendorProdRef.getXX_PiecesBySale_ID()); newProduct.setXX_VMR_UnitPurchase_ID(vendorProdRef.getXX_VMR_UnitPurchase_ID()); newProduct.setXX_SaleUnit_ID(vendorProdRef.getXX_SaleUnit_ID()); newProduct.setC_Country_ID(order.getC_Country_ID()); newProduct.setIsActive(true); newProduct.setC_BPartner_ID(vendorProdRef.getC_BPartner_ID()); newProduct.setXX_VMR_TypeLabel_ID(vendorProdRef.getXX_VMR_TypeLabel_ID()); newProduct.setXX_VMR_ProductClass_ID(vendorProdRef.getXX_VMR_ProductClass_ID()); newProduct.setM_AttributeSet_ID(Env.getCtx().getContextAsInt("#XX_L_P_ATTRIBUTESETST_ID")); newProduct.setProductType(X_Ref_M_Product_ProductType.ITEM.getValue()); newProduct.save(); } else { // Creo variables de sesion para atraparlas en la ventana producto Env.getCtx().setContext("#Depart_Aux", depart); Env.getCtx().setContext("#Section_Aux", LineRefProv.getXX_VMR_Section_ID()); Env.getCtx().setContext("#Line_Aux", LineRefProv.getXX_VMR_Line_ID()); Env.getCtx().setContext("#VendorRef_Aux", LineRefProv.getXX_VMR_VendorProdRef_ID()); Env.getCtx().setContext("#FromProcess_Aux", "Y"); AWindow window_product = new AWindow(); Query query = Query.getNoRecordQuery("M_Product", true); window_product.initWindow(140, query); AEnv.showCenterScreen(window_product); // Obtenemos el GridController para setear la variable m_changed=true JRootPane jRootPane = ((JRootPane) window_product.getComponent(0)); JLayeredPane jLayeredPane = (JLayeredPane) jRootPane.getComponent(1); JPanel jPanel = (JPanel) jLayeredPane.getComponent(0); APanel aPanel = (APanel) jPanel.getComponent(0); VTabbedPane vTabbedPane = (VTabbedPane) aPanel.getComponent(0); GridController gridController = (GridController) vTabbedPane.getComponent(0); GridTable mTable = gridController.getMTab().getTableModel(); mTable.setChanged(true); MProduct.loadLineRefProv(LineRefProv, Env.getCtx()); // Borro las variables de sesion creadas Env.getCtx().remove("#Depart_Aux"); Env.getCtx().remove("#FromProcess_Aux"); Env.getCtx().remove("#Line_Aux"); Env.getCtx().remove("#Section_Aux"); Env.getCtx().remove("#VendorRef_Aux"); } } // cmd_newProduct
/** * Execute Process Instance and Lock UI. Calls lockUI and unlockUI if parent is a ASyncProcess * * <pre> * - Get Process Information * - Call Class * - Submit SQL Procedure * - Run SQL Procedure * </pre> */ public void run() { log.fine("AD_PInstance_ID=" + m_pi.getAD_PInstance_ID() + ", Record_ID=" + m_pi.getRecord_ID()); // Lock // lock(); // try {System.out.println(">> sleeping ..");sleep(20000);System.out.println(".. sleeping <<");} // catch (Exception e) {} // Get Process Information: Name, Procedure Name, ClassName, IsReport, IsDirectPrint String ProcedureName = ""; int AD_ReportView_ID = 0; int AD_Workflow_ID = 0; boolean IsReport = false; boolean IsDirectPrint = false; // String sql = "SELECT p.Name, p.ProcedureName,p.ClassName, p.AD_Process_ID," // 1..4 + " p.isReport,p.IsDirectPrint,p.AD_ReportView_ID,p.AD_Workflow_ID," // 5..8 + " CASE WHEN COALESCE(p.Statistic_Count,0)=0 THEN 0 ELSE p.Statistic_Seconds/p.Statistic_Count END CASE," + " p.IsServerProcess " + "FROM AD_Process p" + " INNER JOIN AD_PInstance i ON (p.AD_Process_ID=i.AD_Process_ID) " + "WHERE p.IsActive='Y'" + " AND i.AD_PInstance_ID=?"; if (!Env.isBaseLanguage(m_wscctx, "AD_Process")) sql = "SELECT t.Name, p.ProcedureName,p.ClassName, p.AD_Process_ID," // 1..4 + " p.isReport, p.IsDirectPrint,p.AD_ReportView_ID,p.AD_Workflow_ID," // 5..8 + " CASE WHEN COALESCE(p.Statistic_Count,0)=0 THEN 0 ELSE p.Statistic_Seconds/p.Statistic_Count END CASE," + " p.IsServerProcess " + "FROM AD_Process p" + " INNER JOIN AD_PInstance i ON (p.AD_Process_ID=i.AD_Process_ID) " + " INNER JOIN AD_Process_Trl t ON (p.AD_Process_ID=t.AD_Process_ID" + " AND t.AD_Language='" + Env.getAD_Language(m_wscctx) + "') " + "WHERE p.IsActive='Y'" + " AND i.AD_PInstance_ID=?"; // try { PreparedStatement pstmt = DB.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, null); pstmt.setInt(1, m_pi.getAD_PInstance_ID()); ResultSet rs = pstmt.executeQuery(); if (rs.next()) { m_pi.setTitle(rs.getString(1)); if (m_waiting != null) m_waiting.setTitle(m_pi.getTitle()); ProcedureName = rs.getString(2); m_pi.setClassName(rs.getString(3)); m_pi.setAD_Process_ID(rs.getInt(4)); // Report if ("Y".equals(rs.getString(5))) { IsReport = true; if ("Y".equals(rs.getString(6)) && !Ini.isPropertyBool(Ini.P_PRINTPREVIEW)) IsDirectPrint = true; } AD_ReportView_ID = rs.getInt(7); AD_Workflow_ID = rs.getInt(8); // int estimate = rs.getInt(9); if (estimate != 0) { m_pi.setEstSeconds(estimate + 1); // admin overhead if (m_waiting != null) m_waiting.setTimerEstimate(m_pi.getEstSeconds()); } m_IsServerProcess = "Y".equals(rs.getString(10)); } else log.log(Level.SEVERE, "No AD_PInstance_ID=" + m_pi.getAD_PInstance_ID()); rs.close(); pstmt.close(); } catch (SQLException e) { m_pi.setSummary( Msg.getMsg(m_wscctx, "ProcessNoProcedure") + " " + e.getLocalizedMessage(), true); // unlock(); log.log(Level.SEVERE, "run", e); return; } // No PL/SQL Procedure if (ProcedureName == null) ProcedureName = ""; /** ******************************************************************** Workflow */ if (AD_Workflow_ID > 0) { startWorkflow(AD_Workflow_ID); // unlock(); return; } /** ******************************************************************** Start Optional Class */ if (m_pi.getClassName() != null) { // Run Class if (!startProcess()) { // unlock(); return; } // No Optional SQL procedure ... done if (!IsReport && ProcedureName.length() == 0) { // unlock (); return; } // No Optional Report ... done if (IsReport && AD_ReportView_ID == 0) { // unlock (); return; } } // If not a report, we need a prodedure name if (!IsReport && ProcedureName.length() == 0) { m_pi.setSummary(Msg.getMsg(m_wscctx, "ProcessNoProcedure"), true); // unlock(); return; } /** ******************************************************************** Report submission */ if (IsReport) { // Optional Pre-Report Process if (ProcedureName.length() > 0) { if (!startDBProcess(ProcedureName)) { // unlock(); return; } } // Pre-Report // Start Report ----------------------------------------------- boolean ok = ReportCtl.start(m_pi, IsDirectPrint); m_pi.setSummary("Report", !ok); // unlock (); } /** ******************************************************************** Process submission */ else { if (!startDBProcess(ProcedureName)) { // unlock(); return; } // Success - getResult ProcessInfoUtil.setSummaryFromDB(m_pi); // unlock(); } // *** Process submission *** // log.fine(Log.l3_Util, "ProcessCtl.run - done"); } // run
public static int serRsCsvToWriter( ResultSet resultset, String[] columnNames, Writer writer, int maxRows, String separator) { int counter = 0; if (resultset != null) { if (columnNames == null) { try { logm("NFO", 9, "CSV titulos, obteniendo desde metadata", null); columnNames = dbRsColumnNames(resultset); } catch (SQLException e) { logmex("ERR", 1, "CSV titulos, obteniendo desde metadata", null, e); return -1; } } logm("DBG", 7, "CSV titulos", columnNames); // A: columnCount tiene el valor especificado o el default int columnCount = columnNames.length; // Itero el resultset escribiendo el output try { // XXX:OPCION escape separator si aparece en un valor? logm("DBG", 4, "ESCRIBIENDO ARCHIVO", resultset); for (int i = 0; i < columnCount - 1; i++) { logm("DBG", 9, "ESCRIBE COL: ", columnNames[i]); writer.write(columnNames[i]); writer.write(separator); } writer.write(columnNames[columnCount - 1]); writer.write(EOL); logm("DBG", 4, "SE ESCRIBIO LINEA DE COLUMNAS", null); logm("DBG", 4, "COUNTER", counter); logm("DBG", 4, "MAXROWS", maxRows); // A: escribi los nombres de las columnas boolean hasNext = resultset.next(); logm("DBG", 4, "NEXT", hasNext); while ((counter < maxRows || maxRows < 0) && hasNext) { logm("DBG", 4, "Escribiendo fila :", counter); String buf; for (int i = 1; i < columnCount; i++) { if ((buf = resultset.getString(i)) != null) { writer.write(buf); } logm("DBG", 9, "STR", buf); writer.write(separator); } if ((buf = resultset.getString(columnCount)) != null) { writer.write(buf); } logm("DBG", 9, "STR", buf); writer.write(EOL); counter++; // XXX:loguear un cartelito ej. cada 1000 hasNext = resultset.next(); } logm("DBG", 2, "termino de escribir lineas", null); } catch (SQLException s) { logmex("ERR", 0, "DB leyendo resultset para CSV", null, s); return -1; } catch (IOException e) { logmex("ERR", 0, "FILE WRITER CSV OUTPUT writing", null, e); return -1; } } else { logm("NFO", 3, "DB FILE CSV RESULTSET IS NULL, was expected?", null); } try { writer.close(); } catch (IOException e) { logmex("ERR", 0, "FILE WRITER CSV OUTPUT closing", null, e); return -1; } return counter; }
// *************************************************************************** // S: db public static int dbRsColumnCount(ResultSet rs) throws SQLException { return rs.getMetaData().getColumnCount(); }
public static void main(String arg[]) { Hashtable ignoreList = new Hashtable(); Class cl = null; Model model = null; System.out.println("Synchronizing forms with database..."); Db.init(); try { DatabaseMetaData meta = Db.getCon().getMetaData(); String[] types = {"TABLE"}; ResultSet rs = meta.getTables(null, null, "%", types); // read ignore.list ignoreList = AutogenerateUtil.readIgnoreList(); // prepare directory File fDir = new File("../../web/WEB-INF/views/crud_form"); if (!fDir.exists()) fDir.mkdir(); while (rs.next()) { // proper file name generationm String className = ""; String tableName = rs.getString("TABLE_NAME"); className = StringUtil.toProperClassName(tableName); // space allowed... // tableName = tableName.toUpperCase(); //If Oracle that need uppercase tablename. In MySQL // in Mac OS X (and probably Linux), it mustbe case sensitive // open table String sql = "select * from " + tableName; PreparedStatement pstmt = Db.getCon() .prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); ResultSet resultSet = pstmt.executeQuery(); ResultSetMetaData metaColumn = resultSet.getMetaData(); int nColoumn = metaColumn.getColumnCount(); // get foreign keys,and stored it in hashtable ResultSet rsFk = meta.getImportedKeys(Db.getCon().getCatalog(), null, tableName); Hashtable hashFk = new Hashtable(); System.out.println("FK Infos for table " + tableName); while (rsFk.next()) { String pkTableName = rsFk.getString("PKTABLE_NAME"); String pkColumnName = rsFk.getString("PKCOLUMN_NAME"); String fkColumnName = rsFk.getString("FKCOLUMN_NAME"); int fkSequence = rsFk.getInt("KEY_SEQ"); System.out.println( tableName + "." + fkColumnName + " => " + pkTableName + "." + pkColumnName); hashFk.put(fkColumnName, pkColumnName); hashFk.put(fkColumnName + "_table", pkTableName); } rsFk.close(); // create form page System.out.println( "Creating form page for " + tableName + " from table + " + application.config.Database.DB + "." + tableName); fDir = new File("../../web/WEB-INF/views/" + tableName); if (!fDir.exists()) fDir.mkdir(); File f = new File("../../web/WEB-INF/views/" + tableName + "/form_" + tableName + ".jsp"); if (ignoreList.get("form_" + tableName + ".jsp") != null) { Logger.getLogger(GenerateForm.class.getName()) .log(Level.INFO, "Ignoring creation of form_" + tableName + ".jsp"); } else { Writer out = new FileWriter(f); out.write( "<%@ page contentType=\"text/html; charset=UTF-8\" language=\"java\" import=\"java.sql.*,recite18th.library.Db,application.config.Config,recite18th.library.Pagination\" %>"); out.write("<%@ taglib uri=\"http://java.sun.com/jsp/jstl/core\" prefix=\"c\" %>\n"); out.write("<%@ taglib uri=\"http://java.sun.com/jsp/jstl/functions\" prefix=\"fn\" %>\n"); // create model for this class, use in detecting its PK Field cl = Class.forName("application.models." + className + "Model"); model = (Model) cl.newInstance(); // iterate all columns resultSet.beforeFirst(); resultSet.next(); out.write( "<table border=\"1\" align=\"center\" cellpadding=\"0\" cellspacing=\"0\" bordercolor=\"#E8EDFF\">\n"); out.write("<tr>\n"); out.write("<td>\n"); out.write( "<form action=\"<%=Config.base_url%>index/" + className + "/save\" method=\"post\" enctype=\"multipart/form-data\">\n"); // I hope it's // okay to // default it to // multipart data out.write("<table id=\"hor-zebra\" summary=\"Form " + className + "\">\n"); out.write("<thead>\n"); out.write("<tr>\n"); out.write("<th colspan=\"2\" class=\"odd\" scope=\"col\">Form " + className + " </th>\n"); out.write("</tr>\n"); out.write("</thead>\n"); out.write("<tbody>\n"); for (int i = 1; i <= nColoumn; i++) { String columnName = metaColumn.getColumnName(i); String dataType = metaColumn.getColumnClassName(i); out.write("<tr>\n"); // if(!columnName.equals(model.getPkFieldName())) // implementing the case of PK not // AutoIncrement // if(!metaColumn.isAutoIncrement(i)) // { // varying field input for type // foreign field, as chooser page view if (hashFk.get(columnName) != null) // TODO: what if PK is chooser also?? :) CUrrently I add it manually the // hidden_*Pk_nama* field { String fkTableName = hashFk.get(columnName + "_table") + ""; String fkColumnName = hashFk.get(columnName) + ""; String fkController = StringUtil.toProperClassName(fkTableName); out.write("<td>" + columnName + "</td>\n"); out.write("<td>\n"); out.write( "<input name=\"" + columnName + "\" type=\"hidden\" id=\"" + columnName + "\" value=\"${model." + columnName + "}\"/>\n"); out.write( "<input name=\"label_" + columnName + "\" readonly=\"true\" type=\"text\" id=\"label_" + columnName + "\" value=\"${model." + columnName + "}\"/>\n"); // TODO : translate I out.write( "<a href=\"<%=Config.base_url%>index/" + fkController + "/chooseView?height=220&width=700\" class=\"thickbox\">Pilih</a>"); out.write("</td>\n"); } else { // regular field input, not foreign key case if (!columnName.equals(model.getPkFieldName())) { out.write("<td>" + columnName + "</td>\n"); out.write("<td>\n"); // ENUM Column, displayed as HTML SELECT. May will only work for mysql only... Logger.getLogger(GenerateForm.class.getName()) .log(Level.INFO, columnName + " type is " + metaColumn.getColumnType(i)); if (metaColumn.getColumnType(i) == 1) { String enum_content[][] = Db.getDataSet( "SELECT SUBSTRING(COLUMN_TYPE,6,length(SUBSTRING(COLUMN_TYPE,6))-1) as enum_content " + " FROM information_schema.COLUMNS " + " WHERE TABLE_NAME='" + tableName + "' " + " AND COLUMN_NAME='" + columnName + "'"); if (enum_content.length > 0) { // Logger.getLogger(Model.class.getName()).log(Level.INFO, "Enum Content = " + // enum_content[0][0]); String enum_list[] = enum_content[0][0].split(","); out.write("<select name=\"" + columnName + "\" id=\"" + columnName + "\">\n"); for (int ienum_list = 0; ienum_list < enum_list.length; ienum_list++) out.write( "\t<option <c:if test=\"${model." + columnName + "=='" + enum_list[ienum_list].substring( 1, enum_list[ienum_list].length() - 1) + "'}\"> selected=\"selected\" </c:if> value=\"" + enum_list[ienum_list].substring( 1, enum_list[ienum_list].length() - 1) + "\">" + enum_list[ienum_list].substring( 1, enum_list[ienum_list].length() - 1) + "</option>\n"); out.write("</select>\n\n"); } else { // no enum content detected.. :) out.write( "<input name=\"" + columnName + "\" type=\"text\" id=\"" + columnName + "\" value=\"${model." + columnName + "}\"/>\n"); } } else if (metaColumn.getColumnType(i) == 91) { out.write( "<input name=\"" + columnName + "\" type=\"text\" id=\"" + columnName + "\" value=\"${model." + columnName + "}\"/>\n"); out.write("<script>\n"); out.write( " if(!isValidDate($('#" + columnName + "').val())) $('#" + columnName + "').val('1980-1-1');\n"); // TODO: default value out.write(" (function($){\n"); out.write(" $('#" + columnName + "').click(function() {\n"); out.write(" $('#" + columnName + "').DatePickerShow();\n"); out.write(" });\n"); out.write(" $('#" + columnName + "').DatePicker({\n"); out.write(" format:'Y-m-d',\n"); out.write(" date: $('#" + columnName + "').val(),\n"); out.write(" current: $('#" + columnName + "').val(),\n"); out.write(" starts: 1,\n"); out.write(" position: 'r',\n"); out.write(" onBeforeShow: function(){\n"); out.write( " $('#" + columnName + "').DatePickerSetDate($('#" + columnName + "').val(), true);\n"); out.write(" },\n"); out.write(" onChange: function(formated, dates){\n"); out.write(" $('#" + columnName + "').DatePickerHide();\n"); out.write(" $('#" + columnName + "').val(formated);\n"); out.write(" }\n"); out.write(" });\n"); out.write(" })(jQuery)\n"); out.write(" </script>\n"); } else { out.write( "<input name=\"" + columnName + "\" type=\"text\" id=\"" + columnName + "\" value=\"${model." + columnName + "}\"/>\n"); out.write("${" + columnName + "_error}\n"); // regular input field } } else { // PK case if (metaColumn.isAutoIncrement(i)) { out.write( "<input name=\"hidden_" + columnName + "\" type=\"hidden\" id=\"hidden_" + columnName + "\" value=\"${model." + columnName + "}\"/>\n"); } else { out.write("<td>" + columnName + "</td>\n"); out.write("<td>\n"); out.write( "<input name=\"" + columnName + "\" type=\"text\" id=\"" + columnName + "\" value=\"${model." + columnName + "}\"/>\n"); out.write("${" + columnName + "_error}\n"); out.write( "<input name=\"hidden_" + columnName + "\" type=\"hidden\" id=\"hidden_" + columnName + "\" value=\"${model." + columnName + "}\"/>\n"); } } out.write("</td>\n"); } out.write("</tr>\n"); } out.write("<tr class=\"odd\">\n"); out.write("<td> </td>\n"); out.write("<td><input type=\"submit\" name=\"Submit\" value=\"Simpan\">"); out.write( "<input name=\"Button\" type=\"button\" id=\"Submit\" value=\"Batal\" onClick=\"javascript:history.back(-1);\"></td>\n"); out.write("</tr>\n"); out.write("</tbody>\n"); out.write("</table>\n"); out.write("</form></td>\n"); out.write("</tr>\n"); out.write("</table>\n"); out.flush(); out.close(); } // create viewPage if (ignoreList.get("view_" + tableName + ".jsp") != null) { Logger.getLogger(GenerateForm.class.getName()) .log(Level.INFO, "Ignoring creation of view_" + tableName + ".jsp"); } else { System.out.println("Creating view page " + tableName); fDir = new File("../../web/WEB-INF/views/" + tableName); if (!fDir.exists()) fDir.mkdir(); File fView = new File("../../web/WEB-INF/views/" + tableName + "/view_" + tableName + ".jsp"); Writer outView = new FileWriter(fView); outView.write( "<%@ page contentType=\"text/html; charset=UTF-8\" language=\"java\" import=\"java.sql.*,recite18th.library.Db,application.config.Config,recite18th.library.Pagination\" %>"); outView.write("<%@ taglib uri=\"http://java.sun.com/jsp/jstl/core\" prefix=\"c\" %>\n"); outView.write( "<%@ taglib uri=\"http://java.sun.com/jsp/jstl/functions\" prefix=\"fn\" %>\n"); outView.write("<% int pagenum = 0; %>\n"); // outView.write("<%@ include file=\"/WEB-INF/views/header.jsp\" %>"); outView.write( "<a href=\"<%=Config.base_url%>index/" + className + "/input/-1\">Tambah Data</a>\n"); outView.write( "|| <a href=\"<%=Config.base_url%>index/" + className + "/print\">Cetak</a>\n"); outView.write("<table width=\"100%\" id=\"rounded-corner\">\n"); outView.write("<thead>\n"); // iterate all columns : table header outView.write(" <tr>\n"); outView.write(" <th scope=\"col\" class=\"rounded-company\">No.</th>\n"); resultSet.beforeFirst(); resultSet.next(); // get Primary Key Field Name : often use String pkFieldName = ""; try { Class params[] = null; Method objMethod = cl.getMethod("getPkFieldName", params); pkFieldName = "" + objMethod.invoke(model); } catch (Exception ex) { Logger.getLogger(Model.class.getName()).log(Level.SEVERE, null, ex); } // ALL Lower Case pkFieldName = pkFieldName.toLowerCase(); // customize column view page for (int i = 1; i <= nColoumn; i++) { String columnName = metaColumn.getColumnName(i).toLowerCase(); // Caution : ALL LowerCase String dataType = metaColumn.getColumnClassName(i); String thClass = "rounded-q1"; String thTitle = StringUtil.toProperFieldTitle(columnName); if (TableCustomization.getTable(tableName) != null) // there is customization for this table { if (TableCustomization.getTable(tableName).get(columnName) != null) { thTitle = TableCustomization.getTable(tableName).get(columnName) + ""; outView.write( " <th scope=\"col\" class=\"" + thClass + "\">" + thTitle + "</th>\n"); } } else { // standard view for this table : hide PK, because mostly is auto increment if (!metaColumn.isAutoIncrement(i)) outView.write( " <th scope=\"col\" class=\"" + thClass + "\">" + thTitle + "</th>\n"); } } outView.write(" <th scope=\"col\" class=\"rounded-q4\">Aksi</th>\n"); outView.write(" </tr>\n"); outView.write("</thead>\n"); outView.write("<tfoot>\n"); outView.write(" <tr>\n"); outView.write( " <td colspan=\"" + (nColoumn + 1) + "\" class=\"rounded-foot-left\"><%=Pagination.createLinks(pagenum)%></td>\n"); outView.write(" <td class=\"rounded-foot-right\"> </td>\n"); outView.write(" </tr>\n"); outView.write("</tfoot>\n"); outView.write("<tbody>\n"); outView.write(" <c:forEach items=\"${row}\" var=\"item\" varStatus=\"status\" >\n"); outView.write(" <tr>\n"); outView.write(" <td>${status.count}</td>\n"); // iterate all columns : table data resultSet.beforeFirst(); resultSet.next(); for (int i = 1; i <= nColoumn; i++) { String columnName = metaColumn.getColumnName(i); // if(!columnName.equals(pkFieldName)) //TOFIX : currently, PK Field is not shown if (TableCustomization.getTable(tableName) != null) { if (TableCustomization.getTable(tableName).get(columnName) != null) { outView.write(" <td>${item." + columnName + "}</td>\n"); } } else { if (!metaColumn.isAutoIncrement(i)) outView.write(" <td>${item." + columnName + "}</td>\n"); } } outView.write(" <td>\n"); outView.write( " <a href=\"<%=Config.base_url%>index/" + className + "/input/${item." + pkFieldName + "}\">Ubah</a>\n"); outView.write( " <a href=\"<%=Config.base_url%>index/" + className + "/delete/${item." + pkFieldName + "}\" onClick=\"return confirm('Apakah Anda yakin?');\">Hapus</a>\n"); outView.write(" </td>\n"); outView.write(" </tr>\n"); outView.write(" </c:forEach>\n"); outView.write("</tbody>\n"); outView.write("</table>\n"); // outView.write("<%@ include file=\"/WEB-INF/views/footer.jsp\" %>"); outView.flush(); outView.close(); } } } catch (Exception e) { e.printStackTrace(); } }
/** * Create Period Controls * * @param ctx context * @param AD_Client_ID client * @param sp server process * @param trx transaction */ public static void createPeriodControls(Ctx ctx, int AD_Client_ID, SvrProcess sp, Trx trx) { s_log.info("AD_Client_ID=" + AD_Client_ID); // Delete Duplicates String sql = "DELETE FROM C_PeriodControl " + "WHERE (C_Period_ID, DocBaseType) IN " + "(SELECT C_Period_ID, DocBaseType " + "FROM C_PeriodControl pc2 " + "GROUP BY C_Period_ID, DocBaseType " + "HAVING COUNT(*) > 1)" + " AND C_PeriodControl_ID NOT IN " + "(SELECT MIN(C_PeriodControl_ID) " + "FROM C_PeriodControl pc3 " + "GROUP BY C_Period_ID, DocBaseType)"; int no = DB.executeUpdate(trx, sql); s_log.info("Duplicates deleted #" + no); // Insert Missing sql = "SELECT DISTINCT p.AD_Client_ID, p.C_Period_ID, dbt.DocBaseType " + "FROM C_Period p, " + "C_DocBaseType dbt " + "WHERE p.AD_Client_ID=? " + " AND NOT EXISTS" + " (SELECT * FROM C_PeriodControl pc " + "WHERE pc.C_Period_ID=p.C_Period_ID AND pc.DocBaseType=dbt.DocBaseType)" + " AND (dbt.AD_Client_ID = 0 OR p.AD_Client_ID = dbt.AD_Client_ID)"; PreparedStatement pstmt = null; ResultSet rs = null; int counter = 0; try { pstmt = DB.prepareStatement(sql, trx); pstmt.setInt(1, AD_Client_ID); rs = pstmt.executeQuery(); while (rs.next()) { int Client_ID = rs.getInt(1); int C_Period_ID = rs.getInt(2); String DocBaseType = rs.getString(3); s_log.config( "AD_Client_ID=" + Client_ID + ", C_Period_ID=" + C_Period_ID + ", DocBaseType=" + DocBaseType); // MPeriodControl pc = new MPeriodControl(ctx, Client_ID, C_Period_ID, DocBaseType, trx); if (pc.save()) { counter++; s_log.fine(pc.toString()); } else s_log.warning("Not saved: " + pc); } } catch (Exception e) { s_log.log(Level.SEVERE, sql, e); } finally { DB.closeResultSet(rs); DB.closeStatement(pstmt); } if (sp != null) sp.addLog(0, null, new BigDecimal(counter), "@C_PeriodControl_ID@ @Created@"); s_log.info("Inserted #" + counter); } // createPeriodControls