Example 1 with X_I_ElementValue
use of org.compiere.model.X_I_ElementValue in project adempiere by adempiere.
the class ImportAccount method doIt.
// prepare
/**
* Perform process.
* @return Message
* @throws Exception
*/
protected String doIt() throws java.lang.Exception {
StringBuffer sql = null;
int no = 0;
String clientCheck = " AND AD_Client_ID=" + m_AD_Client_ID;
// Delete Old Imported
if (m_deleteOldImported) {
sql = new StringBuffer("DELETE I_ElementValue " + "WHERE I_IsImported='Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Delete Old Impored =" + no);
}
// Set Client, Org, IsActive, Created/Updated
sql = new StringBuffer("UPDATE I_ElementValue " + "SET AD_Client_ID = COALESCE (AD_Client_ID, ").append(m_AD_Client_ID).append(")," + " AD_Org_ID = COALESCE (AD_Org_ID, 0)," + " IsActive = COALESCE (IsActive, 'Y')," + " Created = COALESCE (Created, SysDate)," + " CreatedBy = COALESCE (CreatedBy, 0)," + " Updated = COALESCE (Updated, SysDate)," + " UpdatedBy = COALESCE (UpdatedBy, 0)," + " I_ErrorMsg = ' '," + " Processed = 'N', " + " I_IsImported = 'N' " + "WHERE I_IsImported<>'Y' OR I_IsImported IS NULL");
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Reset=" + no);
// Set Element
if (m_C_Element_ID != 0) {
sql = new StringBuffer("UPDATE I_ElementValue " + "SET ElementName=(SELECT Name FROM C_Element WHERE C_Element_ID=").append(m_C_Element_ID).append(") " + "WHERE ElementName IS NULL AND C_Element_ID IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Set Element Default=" + no);
}
//
sql = new StringBuffer("UPDATE I_ElementValue i " + "SET C_Element_ID = (SELECT C_Element_ID FROM C_Element e" + " WHERE i.ElementName=e.Name AND i.AD_Client_ID=e.AD_Client_ID)" + "WHERE C_Element_ID IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Set Element=" + no);
//
sql = new StringBuffer("UPDATE I_ElementValue " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Element, ' " + "WHERE C_Element_ID IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.config("Invalid Element=" + no);
// No Name, Value
sql = new StringBuffer("UPDATE I_ElementValue " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=No Name, ' " + "WHERE (Value IS NULL OR Name IS NULL)" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.config("Invalid Name=" + no);
// Set Column
sql = new StringBuffer("UPDATE I_ElementValue i " + "SET AD_Column_ID = (SELECT AD_Column_ID FROM AD_Column c" + " WHERE UPPER(i.Default_Account)=UPPER(c.ColumnName)" + " AND c.AD_Table_ID IN (315,266) AND AD_Reference_ID=25) " + "WHERE Default_Account IS NOT NULL AND AD_Column_ID IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Set Column=" + no);
//
sql = new StringBuffer("UPDATE I_ElementValue " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Column, ' " + "WHERE AD_Column_ID IS NULL AND Default_Account IS NOT NULL" + // ignore default account
" AND UPPER(Default_Account)<>'DEFAULT_ACCT'" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.config("Invalid Column=" + no);
// Set Post* Defaults (ignore errors)
String[] yColumns = new String[] { "PostActual", "PostBudget", "PostStatistical", "PostEncumbrance" };
for (int i = 0; i < yColumns.length; i++) {
sql = new StringBuffer("UPDATE I_ElementValue SET ").append(yColumns[i]).append("='Y' WHERE ").append(yColumns[i]).append(" IS NULL OR ").append(yColumns[i]).append(" NOT IN ('Y','N')" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Set " + yColumns[i] + " Default=" + no);
}
// Summary
sql = new StringBuffer("UPDATE I_ElementValue " + "SET IsSummary='N' " + "WHERE IsSummary IS NULL OR IsSummary NOT IN ('Y','N')" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Set IsSummary Default=" + no);
// Doc Controlled
sql = new StringBuffer("UPDATE I_ElementValue " + "SET IsDocControlled = CASE WHEN AD_Column_ID IS NOT NULL THEN 'Y' ELSE 'N' END " + "WHERE IsDocControlled IS NULL OR IsDocControlled NOT IN ('Y','N')" + " AND I_IsImported='N'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Set IsDocumentControlled Default=" + no);
// Check Account Type A (E) L M O R
sql = new StringBuffer("UPDATE I_ElementValue " + "SET AccountType='E' " + "WHERE AccountType IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Set AccountType Default=" + no);
//
sql = new StringBuffer("UPDATE I_ElementValue " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid AccountType, ' " + "WHERE AccountType NOT IN ('A','E','L','M','O','R')" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.config("Invalid AccountType=" + no);
// Check Account Sign (N) C B
sql = new StringBuffer("UPDATE I_ElementValue " + "SET AccountSign='N' " + "WHERE AccountSign IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Set AccountSign Default=" + no);
//
sql = new StringBuffer("UPDATE I_ElementValue " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid AccountSign, ' " + "WHERE AccountSign NOT IN ('N','C','D')" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.config("Invalid AccountSign=" + no);
// No Value
sql = new StringBuffer("UPDATE I_ElementValue " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=No Key, ' " + "WHERE (Value IS NULL OR Value='')" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.config("Invalid Key=" + no);
// **** Update ElementValue from existing
sql = new StringBuffer("UPDATE I_ElementValue i " + "SET C_ElementValue_ID=(SELECT C_ElementValue_ID FROM C_ElementValue ev" + " INNER JOIN C_Element e ON (ev.C_Element_ID=e.C_Element_ID)" + " WHERE i.C_Element_ID=e.C_Element_ID AND i.AD_Client_ID=e.AD_Client_ID" + " AND i.Value=ev.Value) " + "WHERE C_ElementValue_ID IS NULL" + " AND I_IsImported='N'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Found ElementValue=" + no);
// update Charge
sql = new StringBuffer("UPDATE I_ElementValue i " + "SET C_Charge_ID = (SELECT C_Charge_ID FROM C_Charge c" + " WHERE i.ChargeName=c.Name AND i.AD_Client_ID=c.AD_Client_ID)" + "WHERE C_Charge_ID IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Set Charge=" + no);
// update Tax Category
sql = new StringBuffer("UPDATE I_ElementValue i " + "SET C_TaxCategory_ID = (SELECT C_TaxCategory_ID FROM C_TaxCategory c" + " WHERE i.TaxCategoryName=c.Name AND i.AD_Client_ID=c.AD_Client_ID)" + "WHERE C_TaxCategory_ID IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Set Tax Category=" + no);
commitEx();
// -------------------------------------------------------------------
int noInsert = 0;
int noUpdate = 0;
// Go through Records
sql = new StringBuffer("SELECT * " + "FROM I_ElementValue " + "WHERE I_IsImported='N'").append(clientCheck).append(" ORDER BY I_ElementValue_ID");
try {
PreparedStatement pstmt = DB.prepareStatement(sql.toString(), get_TrxName());
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
X_I_ElementValue impEV = new X_I_ElementValue(getCtx(), rs, get_TrxName());
int C_ElementValue_ID = impEV.getC_ElementValue_ID();
int I_ElementValue_ID = impEV.getI_ElementValue_ID();
// **** Create/Update ElementValue
if (// New
C_ElementValue_ID == 0) {
MElementValue ev = new MElementValue(impEV);
if (ev.save()) {
noInsert++;
if (!ev.isSummary())
updateCharge(impEV, ev);
impEV.setC_ElementValue_ID(ev.getC_ElementValue_ID());
impEV.setI_IsImported(true);
impEV.saveEx();
} else {
sql = new StringBuffer("UPDATE I_ElementValue i " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||").append(DB.TO_STRING("Insert ElementValue ")).append("WHERE I_ElementValue_ID=").append(I_ElementValue_ID);
DB.executeUpdate(sql.toString(), get_TrxName());
}
} else // Update existing
{
MElementValue ev = new MElementValue(getCtx(), C_ElementValue_ID, get_TrxName());
if (ev.get_ID() != C_ElementValue_ID) {
}
ev.set(impEV);
if (ev.save()) {
noUpdate++;
if (!ev.isSummary())
updateCharge(impEV, ev);
impEV.setI_IsImported(true);
impEV.saveEx();
} else {
sql = new StringBuffer("UPDATE I_ElementValue i " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||").append(DB.TO_STRING("Update ElementValue")).append("WHERE I_ElementValue_ID=").append(I_ElementValue_ID);
DB.executeUpdate(sql.toString(), get_TrxName());
}
}
}
// for all I_ElementValue
rs.close();
pstmt.close();
} catch (SQLException e) {
throw new Exception("create", e);
}
// Set Error to indicator to not imported
sql = new StringBuffer("UPDATE I_ElementValue " + "SET I_IsImported='N', Updated=SysDate " + "WHERE I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
addLog(0, null, new BigDecimal(no), "@Errors@");
addLog(0, null, new BigDecimal(noInsert), "@C_ElementValue_ID@: @Inserted@");
addLog(0, null, new BigDecimal(noUpdate), "@C_ElementValue_ID@: @Updated@");
commitEx();
// ***** Set Parent
sql = new StringBuffer("UPDATE I_ElementValue i " + "SET ParentElementValue_ID=(SELECT C_ElementValue_ID" + " FROM C_ElementValue ev WHERE i.C_Element_ID=ev.C_Element_ID" + " AND i.ParentValue=ev.Value AND i.AD_Client_ID=ev.AD_Client_ID) " + "WHERE ParentElementValue_ID IS NULL" + " AND I_IsImported='Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Found Parent ElementValue=" + no);
//
sql = new StringBuffer("UPDATE I_ElementValue " + "SET I_ErrorMsg=I_ErrorMsg||'Info=ParentNotFound, ' " + "WHERE ParentElementValue_ID IS NULL AND ParentValue IS NOT NULL" + " AND I_IsImported='Y' AND Processed='N'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.config("Not Found Parent ElementValue=" + no);
//
sql = new StringBuffer("SELECT i.ParentElementValue_ID, i.I_ElementValue_ID," + " e.AD_Tree_ID, i.C_ElementValue_ID, i.Value||'-'||i.Name AS Info " + "FROM I_ElementValue i" + " INNER JOIN C_Element e ON (i.C_Element_ID=e.C_Element_ID) " + "WHERE i.C_ElementValue_ID IS NOT NULL AND e.AD_Tree_ID IS NOT NULL" + " AND i.ParentElementValue_ID IS NOT NULL" + " AND i.I_IsImported='Y' AND Processed='N' AND i.AD_Client_ID=").append(m_AD_Client_ID);
int noParentUpdate = 0;
try {
PreparedStatement pstmt = DB.prepareStatement(sql.toString(), get_TrxName());
ResultSet rs = pstmt.executeQuery();
//
String updateSQL = "UPDATE AD_TreeNode SET Parent_ID=?, SeqNo=? " + "WHERE AD_Tree_ID=? AND Node_ID=?";
//begin e-evolution vpj-cd 15 nov 2005 PostgreSQL
//PreparedStatement updateStmt = DB.prepareStatement(updateSQL, get_TrxName());
PreparedStatement updateStmt = DB.prepareStatement(updateSQL, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE, get_TrxName());
//
while (rs.next()) {
// Parent
updateStmt.setInt(1, rs.getInt(1));
// SeqNo (assume sequenec in import is the same)
updateStmt.setInt(2, rs.getInt(2));
// Tree
updateStmt.setInt(3, rs.getInt(3));
// Node
updateStmt.setInt(4, rs.getInt(4));
try {
no = updateStmt.executeUpdate();
noParentUpdate += no;
} catch (SQLException ex) {
log.log(Level.SEVERE, "(ParentUpdate)", ex);
no = 0;
}
if (no == 0)
log.info("Parent not found for " + rs.getString(5));
}
rs.close();
pstmt.close();
} catch (SQLException e) {
log.log(Level.SEVERE, "(ParentUpdateLoop) " + sql.toString(), e);
}
addLog(0, null, new BigDecimal(noParentUpdate), "@ParentElementValue_ID@: @Updated@");
commitEx();
// Reset Processing Flag
sql = new StringBuffer("UPDATE I_ElementValue " + "SET Processing='-'" + "WHERE I_IsImported='Y' AND Processed='N' AND Processing='Y'" + " AND C_ElementValue_ID IS NOT NULL").append(clientCheck);
if (m_updateDefaultAccounts)
sql.append(" AND AD_Column_ID IS NULL");
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Reset Processing Flag=" + no);
if (m_updateDefaultAccounts)
updateDefaults(clientCheck);
// Update Description
sql = new StringBuffer("SELECT * FROM C_ValidCombination vc " + "WHERE EXISTS (SELECT * FROM I_ElementValue i " + "WHERE vc.Account_ID=i.C_ElementValue_ID)");
// Done
sql = new StringBuffer("UPDATE I_ElementValue " + "SET Processing='N', Processed='Y'" + "WHERE I_IsImported='Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Processed=" + no);
return "";
}
Also used :
MElementValue(org.compiere.model.MElementValue)
SQLException(java.sql.SQLException)
ResultSet(java.sql.ResultSet)
PreparedStatement(java.sql.PreparedStatement)
X_I_ElementValue(org.compiere.model.X_I_ElementValue)
SQLException(java.sql.SQLException)
BigDecimal(java.math.BigDecimal)
Aggregations
BigDecimal (java.math.BigDecimal)1
PreparedStatement (java.sql.PreparedStatement)1
ResultSet (java.sql.ResultSet)1
SQLException (java.sql.SQLException)1
MElementValue (org.compiere.model.MElementValue)1
X_I_ElementValue (org.compiere.model.X_I_ElementValue)1
