/**
* ************************************************************************ 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
/**
* Is Period Open for Doc Base Type
*
* @param DocBaseType document base type
* @param dateAcct accounting date
* @return error message or null
*/
public String isOpen(String DocBaseType, Timestamp dateAcct) {
if (!isActive()) {
s_log.warning("Period not active: " + getName());
return "@C_Period_ID@ <> @IsActive@";
}
MAcctSchema as = MClient.get(getCtx(), getAD_Client_ID()).getAcctSchema();
if (as != null && as.isAutoPeriodControl()) {
if (!as.isAutoPeriodControlOpen(dateAcct)) return "@PeriodClosed@ - @AutoPeriodControl@";
// We are OK
Timestamp today = new Timestamp(System.currentTimeMillis());
if (isInPeriod(today) && as.getC_Period_ID() != getC_Period_ID()) {
as.setC_Period_ID(getC_Period_ID());
as.save();
}
return null;
}
// Standard Period Control
if (DocBaseType == null) {
log.warning(getName() + " - No DocBaseType");
return "@NotFound@ @DocBaseType@";
}
MPeriodControl pc = getPeriodControl(DocBaseType);
if (pc == null) {
log.warning(getName() + " - Period Control not found for " + DocBaseType);
return "@NotFound@ @C_PeriodControl_ID@: " + DocBaseType;
}
log.fine(getName() + ": " + DocBaseType);
if (pc.isOpen()) return null;
return "@PeriodClosed@ - @C_PeriodControl_ID@ (" + DocBaseType + ", " + dateAcct + ")";
} // isOpen
/**
* 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
/**
* 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