/**
* ************************************************************************ 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
/**
* 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
/**
* Process
*
* @return message
* @throws Exception
*/
@Override
protected String doIt() throws Exception {
log.info("C_AcctSchema_ID=" + p_C_AcctSchema_ID);
if (p_C_AcctSchema_ID == 0) throw new CompiereSystemException("C_AcctSchema_ID=0");
MAcctSchema as = MAcctSchema.get(getCtx(), p_C_AcctSchema_ID);
if (as.get_ID() == 0)
throw new CompiereSystemException("Not Found - C_AcctSchema_ID=" + p_C_AcctSchema_ID);
// Update
String sql =
"UPDATE M_Product_Acct pa "
+ "SET (P_Revenue_Acct,P_Expense_Acct,P_CostAdjustment_Acct,P_InventoryClearing_Acct,P_Asset_Acct,P_COGS_Acct,"
+ " P_PurchasePriceVariance_Acct,P_InvoicePriceVariance_Acct,"
+ " P_TradeDiscountRec_Acct,P_TradeDiscountGrant_Acct,"
+ " P_Resource_Absorption_Acct, P_MaterialOverhd_Acct)="
+ " (SELECT P_Revenue_Acct,P_Expense_Acct,P_CostAdjustment_Acct,P_InventoryClearing_Acct,P_Asset_Acct,P_COGS_Acct,"
+ " P_PurchasePriceVariance_Acct,P_InvoicePriceVariance_Acct,"
+ " P_TradeDiscountRec_Acct,P_TradeDiscountGrant_Acct,"
+ " P_Resource_Absorption_Acct, P_MaterialOverhd_Acct"
+ " FROM M_Product_Category_Acct pca"
+ " WHERE pca.M_Product_Category_ID="
+ p_M_Product_Category_ID
+ " AND pca.C_AcctSchema_ID= pa.C_AcctSchema_ID "
+ "), Updated=SysDate, UpdatedBy=0 "
+ "WHERE pa.C_AcctSchema_ID= ? "
+ " AND EXISTS (SELECT * FROM M_Product p "
+ "WHERE p.M_Product_ID=pa.M_Product_ID"
+ " AND p.M_Product_Category_ID= ? )";
int updated = DB.executeUpdate(get_TrxName(), sql, p_C_AcctSchema_ID, p_M_Product_Category_ID);
addLog(0, null, new BigDecimal(updated), "@Updated@");
// Insert new Products
sql =
"INSERT INTO M_Product_Acct "
+ "(M_Product_ID, C_AcctSchema_ID,"
+ " AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,"
+ " P_Revenue_Acct, P_Expense_Acct, P_CostAdjustment_Acct, P_InventoryClearing_Acct, P_Asset_Acct, P_CoGs_Acct,"
+ " P_PurchasePriceVariance_Acct, P_InvoicePriceVariance_Acct,"
+ " P_TradeDiscountRec_Acct, P_TradeDiscountGrant_Acct,"
+ " P_Resource_Absorption_Acct, P_MaterialOverhd_Acct) "
+ "SELECT p.M_Product_ID, acct.C_AcctSchema_ID,"
+ " p.AD_Client_ID, p.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0,"
+ " acct.P_Revenue_Acct, acct.P_Expense_Acct, acct.P_CostAdjustment_Acct, acct.P_InventoryClearing_Acct, acct.P_Asset_Acct, acct.P_CoGs_Acct,"
+ " acct.P_PurchasePriceVariance_Acct, acct.P_InvoicePriceVariance_Acct,"
+ " acct.P_TradeDiscountRec_Acct, acct.P_TradeDiscountGrant_Acct,"
+ " acct.P_Resource_Absorption_Acct, acct.P_MaterialOverhd_Acct "
+ "FROM M_Product p"
+ " INNER JOIN M_Product_Category_Acct acct ON (acct.M_Product_Category_ID=p.M_Product_Category_ID)"
+ "WHERE acct.C_AcctSchema_ID= ? " // #
+ " AND p.M_Product_Category_ID= ? " // #
+ " AND NOT EXISTS (SELECT * FROM M_Product_Acct pa "
+ "WHERE pa.M_Product_ID=p.M_Product_ID"
+ " AND pa.C_AcctSchema_ID=acct.C_AcctSchema_ID)";
int created = DB.executeUpdate(get_TrxName(), sql, p_C_AcctSchema_ID, p_M_Product_Category_ID);
addLog(0, null, new BigDecimal(created), "@Created@");
return "@Created@=" + created + ", @Updated@=" + updated;
} // doIt
/**
* 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
/**
* 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
