/**
* 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;
}
/**
* After Save
*
* @param newRecord new
* @param success success
* @return success
*/
@Override
protected boolean afterSave(boolean newRecord, boolean success) {
// also used for afterDelete
String sql =
"UPDATE M_AttributeSet mas"
+ " SET IsInstanceAttribute='Y' "
+ "WHERE M_AttributeSet_ID= ? "
+ " AND IsInstanceAttribute='N'"
+ " AND (IsSerNo='Y' OR IsLot='Y' OR IsGuaranteeDate='Y'"
+ " OR EXISTS (SELECT * FROM M_AttributeUse mau"
+ " INNER JOIN M_Attribute ma ON (mau.M_Attribute_ID=ma.M_Attribute_ID) "
+ "WHERE mau.M_AttributeSet_ID=mas.M_AttributeSet_ID"
+ " AND mau.IsActive='Y' AND ma.IsActive='Y'"
+ " AND ma.IsInstanceAttribute='Y')"
+ ")";
int no = DB.executeUpdate(get_Trx(), sql, getM_AttributeSet_ID());
if (no != 0) log.fine("afterSave - Set Instance Attribute");
//
sql =
"UPDATE M_AttributeSet mas"
+ " SET IsInstanceAttribute='N' "
+ "WHERE M_AttributeSet_ID=? "
+ " AND IsInstanceAttribute='Y'"
+ " AND IsSerNo='N' AND IsLot='N' AND IsGuaranteeDate='N'"
+ " AND NOT EXISTS (SELECT * FROM M_AttributeUse mau"
+ " INNER JOIN M_Attribute ma ON (mau.M_Attribute_ID=ma.M_Attribute_ID) "
+ "WHERE mau.M_AttributeSet_ID=mas.M_AttributeSet_ID"
+ " AND mau.IsActive='Y' AND ma.IsActive='Y'"
+ " AND ma.IsInstanceAttribute='Y')";
no = DB.executeUpdate(get_Trx(), sql, getM_AttributeSet_ID());
if (no != 0) log.fine("afterSave - Reset Instance Attribute");
return success;
} // afterSave
/**
* 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
/**
* ************************************************************************ 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
/**
* 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
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
/**
* FindById Searches the database for a Group object by the object id.
*
* @param connection The jdbc connection
* @param id The id to select by
*/
public static Post FindByID(Long post_ID) throws SQLException {
Post post = null;
PreparedStatement findPostId = null;
Connection conn = null;
String sql = String.format("SELECT * from %s where postid = ?", Post.dquote(POST), post_ID);
if (post_ID != null) {
try {
conn = DB.getConnection();
findPostId = conn.prepareStatement(sql);
findPostId.setLong(1, post_ID);
ResultSet rs = findPostId.executeQuery();
if (rs.next()) {
post =
new Post(
rs.getString(Post.CONTENT),
rs.getString(Post.TYPE),
rs.getTimestamp(Post.TIMESTAMP),
rs.getLong(Post.USER_ID),
rs.getLong(Post.WALL_ID),
rs.getLong(Post.POST_ID));
}
findPostId.close();
conn.close();
return post;
} catch (SQLException e) {
Logger.debug("Error retrieving post.", e);
}
}
return post;
}
public static void deletePost(Long post_ID) throws SQLException {
Post post = null;
String deletePostStr =
String.format("DELETE from %s where postid = ?", Post.dquote(POST), post_ID);
Connection conn = null;
PreparedStatement deleteP = null;
if (post_ID != null) {
try {
conn = DB.getConnection();
deleteP = conn.prepareStatement(deletePostStr);
ResultSet rs = deleteP.executeQuery();
if (rs.next()) {
Logger.debug("Failed to delete the Post.");
}
deleteP.close();
conn.close();
} catch (SQLException e) {
Logger.debug("Failed while trying to delete the post.");
}
} else {
Logger.debug("Post id is null.");
}
}
/**
* ************************************************************************ Lineas de Remesa
*
* @param whereClause where clause or null (starting with AND)
* @return lines
*/
public MRemesaLine[] getLines(String whereClause, String orderClause) {
ArrayList list = new ArrayList();
StringBuffer sql = new StringBuffer("SELECT * FROM C_RemesaLine WHERE C_Remesa_ID=? ");
if (whereClause != null) sql.append(whereClause);
if (orderClause != null) sql.append(" ").append(orderClause);
PreparedStatement pstmt = null;
try {
pstmt = DB.prepareStatement(sql.toString(), get_TrxName());
pstmt.setInt(1, getC_Remesa_ID());
ResultSet rs = pstmt.executeQuery();
while (rs.next()) list.add(new MRemesaLine(getCtx(), rs));
rs.close();
pstmt.close();
pstmt = null;
} catch (Exception e) {
log.saveError("getLines - " + sql, e);
} finally {
try {
if (pstmt != null) pstmt.close();
} catch (Exception e) {
}
pstmt = null;
}
//
MRemesaLine[] lines = new MRemesaLine[list.size()];
list.toArray(lines);
return lines;
} // getLines
@Override
protected void saveUser(User u) {
/*procedure add_user(
p_id in dxdy_user.id%type,
p_username in dxdy_user.username%type,
p_status in dxdy_status.status_name%type,
p_reg_date in varchar2);
*/
super.saveUser(u);
String procedure = "{call dxdy_executable.add_user (?, ?, ?, ?)}";
CallableStatement st = null;
try {
st = connection.prepareCall(procedure);
st.setInt(1, u.ID);
st.setString(2, u.name);
st.setString(3, u.status);
st.setString(4, u.regDate);
st.execute();
// System.out.println("User ID = " + u.ID + ": OK");
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (st != null) {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
/**
* ************************************************************************ Start Workflow.
*
* @param AD_Workflow_ID workflow
* @return true if started
*/
private boolean startWorkflow(int AD_Workflow_ID) {
log.fine(AD_Workflow_ID + " - " + m_pi);
boolean started = false;
if (DB.isRemoteProcess()) {
log.info("trying to running on the server");
Server server = CConnection.get().getServer();
try {
if (server != null) { // See ServerBean
log.info("running on the server");
m_pi = server.workflow(m_wscctx, m_pi, AD_Workflow_ID);
log.finest("server => " + m_pi);
started = true;
}
} catch (Exception ex) {
log.log(Level.SEVERE, "AppsServer error", ex);
started = false;
}
}
// Run locally
if (!started && !m_IsServerProcess) {
log.info("running locally");
MWorkflow wf = MWorkflow.get(m_wscctx, AD_Workflow_ID);
MWFProcess wfProcess = null;
if (m_pi.isBatch()) wfProcess = wf.start(m_pi); // may return null
else wfProcess = wf.startWait(m_pi); // may return null
started = wfProcess != null;
}
return started;
} // startWorkflow
/**
* 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
/**
* 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
/** Update Header. Set Approved Amount */
private void updateHeader() {
String sql =
"UPDATE S_TimeExpense te"
+ " SET ApprovalAmt = "
+ "(SELECT SUM(Qty*ConvertedAmt) FROM S_TimeExpenseLine tel "
+ "WHERE te.S_TimeExpense_ID=tel.S_TimeExpense_ID) "
+ "WHERE S_TimeExpense_ID=? ";
DB.executeUpdate(get_Trx(), sql, getS_TimeExpense_ID());
if (get_Trx() != null) get_Trx().commit();
} // updateHeader
/**
* 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
/**
* 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;
}
/**
* 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
public static MRequestAction[] getActions(MRequest request) {
ArrayList<MRequestAction> retVal = new ArrayList<MRequestAction>();
String sql = "SELECT * FROM R_RequestAction WHERE R_Request_ID = ? ";
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = DB.prepareStatement(sql, request.get_Trx());
pstmt.setInt(1, request.getR_Request_ID());
rs = pstmt.executeQuery();
while (rs.next()) retVal.add(new MRequestAction(request.getCtx(), rs, request.get_Trx()));
} catch (Exception e) {
log.severe(e.toString());
} finally {
DB.closeResultSet(rs);
DB.closeStatement(pstmt);
}
MRequestAction[] actions = new MRequestAction[retVal.size()];
retVal.toArray(actions);
return actions;
}
/**
* 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
/**
* ************************************************************************ Start Java Process
* Class. instanciate the class implementing the interface ProcessCall. The class can be a
* Server/Client class (when in Package org compiere.process or org.compiere.model) or a client
* only class (e.g. in org.compiere.report)
*
* @return true if success
*/
private boolean startProcess() {
log.fine(m_pi.toString());
boolean started = false;
if (DB.isRemoteProcess()) {
Server server = CConnection.get().getServer();
try {
if (server != null) { // See ServerBean
m_pi = server.process(m_wscctx, m_pi);
log.finest("server => " + m_pi);
started = true;
}
} catch (UndeclaredThrowableException ex) {
Throwable cause = ex.getCause();
if (cause != null) {
if (cause instanceof InvalidClassException)
log.log(
Level.SEVERE, "Version Server <> Client: " + cause.toString() + " - " + m_pi, ex);
else
log.log(Level.SEVERE, "AppsServer error(1b): " + cause.toString() + " - " + m_pi, ex);
} else log.log(Level.SEVERE, " AppsServer error(1) - " + m_pi, ex);
started = false;
} catch (Exception ex) {
Throwable cause = ex.getCause();
if (cause == null) cause = ex;
log.log(Level.SEVERE, "AppsServer error - " + m_pi, cause);
started = false;
}
}
// Run locally
if (!started && !m_IsServerProcess) {
ProcessCall myObject = null;
try {
Class myClass = Class.forName(m_pi.getClassName());
myObject = (ProcessCall) myClass.newInstance();
if (myObject == null) m_pi.setSummary("No Instance for " + m_pi.getClassName(), true);
else myObject.startProcess(m_wscctx, m_pi, m_trx);
if (m_trx != null) {
m_trx.commit();
m_trx.close();
}
} catch (Exception e) {
if (m_trx != null) {
m_trx.rollback();
m_trx.close();
}
m_pi.setSummary("Error starting Class " + m_pi.getClassName(), true);
log.log(Level.SEVERE, m_pi.getClassName(), e);
}
}
return !m_pi.isError();
} // startProcess
/**
* 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
/** Update Remesa */
private boolean updateRemesa() {
// Update Remesa
String sql =
"UPDATE C_Remesa "
+ " SET TotalAmt="
+ " (SELECT COALESCE(SUM(rl.LineNetAmt),0) FROM C_RemesaLine rl GROUP BY rl.C_Remesa_ID HAVING rl.C_Remesa_ID="
+ getC_Remesa_ID()
+ ")"
// + " FROM C_Remesa rm"
+ " WHERE C_Remesa_ID="
+ getC_Remesa_ID();
int no = DB.executeUpdate(sql);
return no == 1;
} // updateHeaderTax
/**
* Fill the table using m_sql
*
* @param table table
*/
private static void tableLoad(MiniTable table) {
// log.finest(m_sql + " - " + m_groupBy);
String sql =
MRole.getDefault()
.addAccessSQL(m_sql.toString(), "tab", MRole.SQL_FULLYQUALIFIED, MRole.SQL_RO)
+ m_groupBy
+ m_orderBy;
log.finest(sql);
try {
Statement stmt = DB.createStatement();
ResultSet rs = stmt.executeQuery(sql);
table.loadTable(rs);
stmt.close();
} catch (SQLException e) {
log.log(Level.SEVERE, sql, e);
}
} // tableLoad
/**
* ************************************************************************ Start Database Process
*
* @param ProcedureName PL/SQL procedure name
* @return true if success
*/
private boolean startDBProcess(String ProcedureName) {
// execute on this thread/connection
log.fine(ProcedureName + "(" + m_pi.getAD_PInstance_ID() + ")");
String sql = "{call " + ProcedureName + "(?)}";
try {
CallableStatement cstmt = DB.prepareCall(sql, ResultSet.CONCUR_UPDATABLE, null); // ro??
cstmt.setInt(1, m_pi.getAD_PInstance_ID());
cstmt.executeUpdate();
cstmt.close();
} catch (Exception e) {
log.log(Level.SEVERE, sql, e);
m_pi.setSummary(Msg.getMsg(m_wscctx, "ProcessRunError") + " " + e.getLocalizedMessage());
m_pi.setError(true);
return false;
}
// log.fine(Log.l4_Data, "ProcessCtl.startProcess - done");
return true;
} // startDBProcess
/**
* 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
/** Persist Saves the post to the database, this function will create a new database connection */
public void Persist() {
PreparedStatement pstmt = null;
Connection conn = null;
try {
conn = DB.getConnection();
String sql = "UPDATE " + User.dquote(Post.POST);
sql += " set " + Post.CONTENT + " = ?, ";
sql += Post.TYPE + " = ?, ";
sql += Post.TIMESTAMP + " = ?, ";
sql += Post.USER_ID + " = ?, ";
sql += Post.WALL_ID + " = ?, ";
sql += "where " + Post.POST_ID + " = ?";
Logger.debug("Generated update: [%s]", sql);
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, this.content);
pstmt.setString(2, this.type);
pstmt.setTimestamp(3, this.time_stamp);
pstmt.setLong(4, this.user_ID);
pstmt.setLong(5, this.wall_ID);
pstmt.setLong(6, this.post_ID);
pstmt.executeUpdate();
pstmt.close();
conn.close();
} catch (SQLException e) {
// Attempt to close the connection
Logger.debug("Error while persisting Post");
if (conn != null) {
try {
conn.close();
} catch (Exception x) {
Logger.debug("Error while closing connection during exception", x);
}
}
}
}
