Example 11 with DBException
use of org.adempiere.exceptions.DBException in project adempiere by adempiere.
the class ImportReportLine 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_ReportLine " + "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_ReportLine " + "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 = ' '," + " I_IsImported = 'N' " + "WHERE I_IsImported<>'Y' OR I_IsImported IS NULL");
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Reset=" + no);
// ReportLineSetName (Default)
if (m_PA_ReportLineSet_ID != 0) {
sql = new StringBuffer("UPDATE I_ReportLine i " + "SET ReportLineSetName=(SELECT Name FROM PA_ReportLineSet r" + " WHERE PA_ReportLineSet_ID=").append(m_PA_ReportLineSet_ID).append(" AND i.AD_Client_ID=r.AD_Client_ID) " + "WHERE ReportLineSetName IS NULL AND PA_ReportLineSet_ID IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Set ReportLineSetName Default=" + no);
}
// Set PA_ReportLineSet_ID
sql = new StringBuffer("UPDATE I_ReportLine i " + "SET PA_ReportLineSet_ID=(SELECT PA_ReportLineSet_ID FROM PA_ReportLineSet r" + " WHERE i.ReportLineSetName=r.Name AND i.AD_Client_ID=r.AD_Client_ID) " + "WHERE PA_ReportLineSet_ID IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Set PA_ReportLineSet_ID=" + no);
//
sql = new StringBuffer("UPDATE I_ReportLine " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid ReportLineSet, ' " + "WHERE PA_ReportLineSet_ID IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.config("Invalid ReportLineSet=" + no);
// Ignore if there is no Report Line Name or ID
sql = new StringBuffer("UPDATE I_ReportLine " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'Ignored=NoLineName, ' " + "WHERE PA_ReportLine_ID IS NULL AND Name IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.config("Invalid LineName=" + no);
// Validate ElementValue
sql = new StringBuffer("UPDATE I_ReportLine i " + "SET C_ElementValue_ID=(SELECT C_ElementValue_ID FROM C_ElementValue e" + " WHERE i.ElementValue=e.Value AND i.AD_Client_ID=e.AD_Client_ID) " + "WHERE C_ElementValue_ID IS NULL AND ElementValue IS NOT NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Set C_ElementValue_ID=" + no);
// Validate C_ElementValue_ID
sql = new StringBuffer("UPDATE I_ReportLine " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid ElementValue, ' " + // MReportLine.LINETYPE_Calculation
"WHERE C_ElementValue_ID IS NULL AND LineType<>'C'" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.config("Invalid AccountType=" + no);
// Set SeqNo
sql = new StringBuffer("UPDATE I_ReportLine " + "SET SeqNo=I_ReportLine_ID " + "WHERE SeqNo IS NULL" + " AND I_IsImported='N'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Set SeqNo Default=" + no);
// Copy/Sync from first Row of Line
sql = new StringBuffer("UPDATE I_ReportLine i " + "SET (Description, SeqNo, IsSummary, IsPrinted, LineType, CalculationType, AmountType, PAAmountType, PAPeriodType, PostingType)=" + " (SELECT Description, SeqNo, IsSummary, IsPrinted, LineType, CalculationType, AmountType, PAAmountType, PAPeriodType, PostingType" + " FROM I_ReportLine ii WHERE i.Name=ii.Name AND i.PA_ReportLineSet_ID=ii.PA_ReportLineSet_ID" + " AND ii.I_ReportLine_ID=(SELECT MIN(I_ReportLine_ID) FROM I_ReportLine iii" + " WHERE i.Name=iii.Name AND i.PA_ReportLineSet_ID=iii.PA_ReportLineSet_ID)) " + "WHERE EXISTS (SELECT *" + " FROM I_ReportLine ii WHERE i.Name=ii.Name AND i.PA_ReportLineSet_ID=ii.PA_ReportLineSet_ID" + " AND ii.I_ReportLine_ID=(SELECT MIN(I_ReportLine_ID) FROM I_ReportLine iii" + " WHERE i.Name=iii.Name AND i.PA_ReportLineSet_ID=iii.PA_ReportLineSet_ID))" + " AND I_IsImported='N'").append(// not if previous error
clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Sync from first Row of Line=" + no);
// Validate IsSummary - (N) Y
sql = new StringBuffer("UPDATE I_ReportLine " + "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);
// Validate IsPrinted - (Y) N
sql = new StringBuffer("UPDATE I_ReportLine " + "SET IsPrinted='Y' " + "WHERE IsPrinted IS NULL OR IsPrinted NOT IN ('Y','N')" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Set IsPrinted Default=" + no);
// Validate Line Type - (S) C
sql = new StringBuffer("UPDATE I_ReportLine " + "SET LineType='S' " + "WHERE LineType IS NULL OR LineType NOT IN ('S','C')" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Set LineType Default=" + no);
// Validate Optional Calculation Type - A P R S
sql = new StringBuffer("UPDATE I_ReportLine " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid CalculationType, ' " + "WHERE CalculationType IS NOT NULL AND CalculationType NOT IN ('A','P','R','S')" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.config("Invalid CalculationType=" + no);
// Convert Optional Amount Type to PAAmount Type and PAPeriodType
sql = new StringBuffer("UPDATE I_ReportLine " + "SET PAAmountType = substr(AmountType,1,1), PAPeriodType = substr(AmountType,1,2) " + "WHERE AmountType IS NOT NULL AND (PAAmountType IS NULL OR PAPeriodType IS NULL) " + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.config("Converted AmountType=" + no);
// Validate Optional Amount Type -
sql = new StringBuffer("UPDATE I_ReportLine " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid PAAmountType, ' " + "WHERE PAAmountType IS NOT NULL AND UPPER(AmountType) NOT IN ('B','C','D','Q','S','R')" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.config("Invalid AmountType=" + no);
// Validate Optional Period Type -
sql = new StringBuffer("UPDATE I_ReportLine " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid PAPeriodType, ' " + "WHERE PAPeriodType IS NOT NULL AND UPPER(AmountType) NOT IN ('P','Y','T','N')" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.config("Invalid PeriodType=" + no);
// Validate Optional Posting Type - A B E S R
sql = new StringBuffer("UPDATE I_ReportLine " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid CalculationType, ' " + "WHERE PostingType IS NOT NULL AND PostingType NOT IN ('A','B','E','S','R')" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.config("Invalid PostingType=" + no);
// Set PA_ReportLine_ID
sql = new StringBuffer("UPDATE I_ReportLine i " + "SET PA_ReportLine_ID=(SELECT MAX(PA_ReportLine_ID) FROM PA_ReportLine r" + " WHERE i.Name=r.Name AND i.PA_ReportLineSet_ID=r.PA_ReportLineSet_ID) " + "WHERE PA_ReportLine_ID IS NULL AND PA_ReportLineSet_ID IS NOT NULL" + " AND I_IsImported='N'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Set PA_ReportLine_ID=" + no);
commitEx();
// -------------------------------------------------------------------
int noInsertLine = 0;
int noUpdateLine = 0;
// **** Create Missing ReportLines
sql = new StringBuffer("SELECT DISTINCT PA_ReportLineSet_ID, Name " + "FROM I_ReportLine " + "WHERE I_IsImported='N' AND PA_ReportLine_ID IS NULL" + " AND I_IsImported='N'").append(clientCheck);
try {
// Insert ReportLine
PreparedStatement pstmt_insertLine = DB.prepareStatement("INSERT INTO PA_ReportLine " + "(PA_ReportLine_ID,PA_ReportLineSet_ID," + "AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy," + "Name,SeqNo,IsPrinted,IsSummary,LineType)" + "SELECT ?,PA_ReportLineSet_ID," + "AD_Client_ID,AD_Org_ID,'Y',SysDate,CreatedBy,SysDate,UpdatedBy," + "Name,SeqNo,IsPrinted,IsSummary,LineType " + // + "WHERE PA_ReportLineSet_ID=? AND Name=? AND ROWNUM=1" // #2..3
"FROM I_ReportLine " + "WHERE I_ReportLine_ID=(SELECT MAX(I_ReportLine_ID) " + "FROM I_ReportLine " + // #2..3
"WHERE PA_ReportLineSet_ID=? AND Name=? " + //jz + clientCheck, get_TrxName());
clientCheck + ")", get_TrxName());
PreparedStatement pstmt = DB.prepareStatement(sql.toString(), get_TrxName());
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
int PA_ReportLineSet_ID = rs.getInt(1);
String Name = rs.getString(2);
//
try {
int PA_ReportLine_ID = DB.getNextID(m_AD_Client_ID, "PA_ReportLine", get_TrxName());
if (PA_ReportLine_ID <= 0)
throw new DBException("No NextID (" + PA_ReportLine_ID + ")");
pstmt_insertLine.setInt(1, PA_ReportLine_ID);
pstmt_insertLine.setInt(2, PA_ReportLineSet_ID);
pstmt_insertLine.setString(3, Name);
//
no = pstmt_insertLine.executeUpdate();
log.finest("Insert ReportLine = " + no + ", PA_ReportLine_ID=" + PA_ReportLine_ID);
noInsertLine++;
} catch (Exception ex) {
log.finest(ex.toString());
continue;
}
}
rs.close();
pstmt.close();
//
pstmt_insertLine.close();
} catch (SQLException e) {
log.log(Level.SEVERE, "Create ReportLine", e);
}
// Set PA_ReportLine_ID (for newly created)
sql = new StringBuffer("UPDATE I_ReportLine i " + "SET PA_ReportLine_ID=(SELECT MAX(PA_ReportLine_ID) FROM PA_ReportLine r" + " WHERE i.Name=r.Name AND i.PA_ReportLineSet_ID=r.PA_ReportLineSet_ID) " + "WHERE PA_ReportLine_ID IS NULL AND PA_ReportLineSet_ID IS NOT NULL" + " AND I_IsImported='N'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Set PA_ReportLine_ID=" + no);
// **** Update ReportLine
sql = new StringBuffer("UPDATE PA_ReportLine r " + "SET (Description,SeqNo,IsSummary,IsPrinted,LineType,CalculationType,AmountType,PAAmountType,PAPeriodType,PostingType,Updated,UpdatedBy)=" + " (SELECT Description,SeqNo,IsSummary,IsPrinted,LineType,CalculationType,AmountType,PAAmountType,PAPeriodType,PostingType,SysDate,UpdatedBy" + " FROM I_ReportLine i WHERE r.Name=i.Name AND r.PA_ReportLineSet_ID=i.PA_ReportLineSet_ID" + " AND i.I_ReportLine_ID=(SELECT MIN(I_ReportLine_ID) FROM I_ReportLine iii" + " WHERE i.Name=iii.Name AND i.PA_ReportLineSet_ID=iii.PA_ReportLineSet_ID)) " + "WHERE EXISTS (SELECT *" + " FROM I_ReportLine i WHERE r.Name=i.Name AND r.PA_ReportLineSet_ID=i.PA_ReportLineSet_ID" + " AND i.I_ReportLine_ID=(SELECT MIN(I_ReportLine_ID) FROM I_ReportLine iii" + " WHERE i.Name=iii.Name AND i.PA_ReportLineSet_ID=iii.PA_ReportLineSet_ID AND i.I_IsImported='N'))").append(clientCheck);
noUpdateLine = DB.executeUpdate(sql.toString(), get_TrxName());
log.config("Update PA_ReportLine=" + noUpdateLine);
// -------------------------------------------------------------------
int noInsertSource = 0;
int noUpdateSource = 0;
// **** Create ReportSource
sql = new StringBuffer("SELECT I_ReportLine_ID, PA_ReportSource_ID " + "FROM I_ReportLine " + "WHERE PA_ReportLine_ID IS NOT NULL" + " AND I_IsImported='N'").append(clientCheck);
try {
// Insert ReportSource
PreparedStatement pstmt_insertSource = DB.prepareStatement("INSERT INTO PA_ReportSource " + "(PA_ReportSource_ID," + "AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy," + "PA_ReportLine_ID,ElementType,C_ElementValue_ID) " + "SELECT ?," + "AD_Client_ID,AD_Org_ID,'Y',SysDate,CreatedBy,SysDate,UpdatedBy," + "PA_ReportLine_ID,'AC',C_ElementValue_ID " + "FROM I_ReportLine " + "WHERE I_ReportLine_ID=?" + " AND I_IsImported='N'" + clientCheck, get_TrxName());
// Update ReportSource
//jz
/*
String sqlt="UPDATE PA_ReportSource "
+ "SET (ElementType,C_ElementValue_ID,Updated,UpdatedBy)="
+ " (SELECT 'AC',C_ElementValue_ID,SysDate,UpdatedBy"
+ " FROM I_ReportLine"
+ " WHERE I_ReportLine_ID=?) "
+ "WHERE PA_ReportSource_ID=?"
+ clientCheck;
PreparedStatement pstmt_updateSource = DB.prepareStatement
(sqlt, get_TrxName());
*/
// Delete ReportSource - afalcone 22/02/2007 - F.R. [ 1642250 ] Import ReportLine / Very Slow Reports
PreparedStatement pstmt_deleteSource = DB.prepareStatement("DELETE FROM PA_ReportSource " + "WHERE C_ElementValue_ID IS NULL" + " AND PA_ReportSource_ID=?" + clientCheck, get_TrxName());
//End afalcone 22/02/2007 - F.R. [ 1642250 ] Import ReportLine / Very Slow Reports
// Set Imported = Y
PreparedStatement pstmt_setImported = DB.prepareStatement("UPDATE I_ReportLine SET I_IsImported='Y'," + " PA_ReportSource_ID=?, " + " Updated=SysDate, Processed='Y' WHERE I_ReportLine_ID=?", get_TrxName());
PreparedStatement pstmt = DB.prepareStatement(sql.toString(), get_TrxName());
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
int I_ReportLine_ID = rs.getInt(1);
int PA_ReportSource_ID = rs.getInt(2);
//
if (// New ReportSource
PA_ReportSource_ID == 0) {
try {
PA_ReportSource_ID = DB.getNextID(m_AD_Client_ID, "PA_ReportSource", get_TrxName());
if (PA_ReportSource_ID <= 0)
throw new DBException("No NextID (" + PA_ReportSource_ID + ")");
pstmt_insertSource.setInt(1, PA_ReportSource_ID);
pstmt_insertSource.setInt(2, I_ReportLine_ID);
//
no = pstmt_insertSource.executeUpdate();
log.finest("Insert ReportSource = " + no + ", I_ReportLine_ID=" + I_ReportLine_ID + ", PA_ReportSource_ID=" + PA_ReportSource_ID);
noInsertSource++;
} catch (Exception ex) {
log.finest("Insert ReportSource - " + ex.toString());
sql = new StringBuffer("UPDATE I_ReportLine i " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||").append(DB.TO_STRING("Insert ElementSource: " + ex.toString())).append("WHERE I_ReportLine_ID=").append(I_ReportLine_ID);
DB.executeUpdate(sql.toString(), get_TrxName());
continue;
}
} else // update Report Source
{
//jz
String sqlt = "UPDATE PA_ReportSource " + "SET (ElementType,C_ElementValue_ID,Updated,UpdatedBy)=" + //jz
" (SELECT CAST('AC' AS CHAR(2)),C_ElementValue_ID,SysDate,UpdatedBy" + " FROM I_ReportLine" + " WHERE I_ReportLine_ID=" + I_ReportLine_ID + ") " + "WHERE PA_ReportSource_ID=" + PA_ReportSource_ID + " " + clientCheck;
PreparedStatement pstmt_updateSource = DB.prepareStatement(sqlt, get_TrxName());
//pstmt_updateSource.setInt(2, PA_ReportSource_ID);
try {
no = pstmt_updateSource.executeUpdate();
//no = DB.executeUpdate(sqlt, get_TrxName());
log.finest("Update ReportSource = " + no + ", I_ReportLine_ID=" + I_ReportLine_ID + ", PA_ReportSource_ID=" + PA_ReportSource_ID);
noUpdateSource++;
} catch (SQLException ex) {
log.finest("Update ReportSource - " + ex.toString());
sql = new StringBuffer("UPDATE I_ReportLine i " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||").append(DB.TO_STRING("Update ElementSource: " + ex.toString())).append("WHERE I_ReportLine_ID=").append(I_ReportLine_ID);
DB.executeUpdate(sql.toString(), get_TrxName());
continue;
}
pstmt_updateSource.close();
}
// update source
// Set Imported to Y
pstmt_setImported.setInt(1, PA_ReportSource_ID);
pstmt_setImported.setInt(2, I_ReportLine_ID);
no = pstmt_setImported.executeUpdate();
if (no != 1)
log.log(Level.SEVERE, "Set Imported=" + no);
//
// afalcone 22/02/2007 - F.R. [ 1642250 ] Import ReportLine / Very Slow Reports
// Delete report sources with null account
pstmt_deleteSource.setInt(1, PA_ReportSource_ID);
no = pstmt_deleteSource.executeUpdate();
log.finest("Delete ReportSource with Null Account= " + no + ", I_ReportLine_ID=" + I_ReportLine_ID + ", PA_ReportSource_ID=" + PA_ReportSource_ID);
// End afalcone 22/02/2007 - F.R. [ 1642250 ] Import ReportLine / Very Slow Reports
commitEx();
}
rs.close();
pstmt.close();
//
pstmt_insertSource.close();
//jz pstmt_updateSource.close();
pstmt_setImported.close();
//
} catch (SQLException e) {
}
// Set Error to indicator to not imported
sql = new StringBuffer("UPDATE I_ReportLine " + "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(noInsertLine), "@PA_ReportLine_ID@: @Inserted@");
addLog(0, null, new BigDecimal(noUpdateLine), "@PA_ReportLine_ID@: @Updated@");
addLog(0, null, new BigDecimal(noInsertSource), "@PA_ReportSource_ID@: @Inserted@");
addLog(0, null, new BigDecimal(noUpdateSource), "@PA_ReportSource_ID@: @Updated@");
return "";
}
Also used :
DBException(org.adempiere.exceptions.DBException)
SQLException(java.sql.SQLException)
ResultSet(java.sql.ResultSet)
PreparedStatement(java.sql.PreparedStatement)
DBException(org.adempiere.exceptions.DBException)
SQLException(java.sql.SQLException)
BigDecimal(java.math.BigDecimal)
Example 12 with DBException
use of org.adempiere.exceptions.DBException in project adempiere by adempiere.
the class ImportBPartner method doIt.
// prepare
/**
* Perform process.
* @return Message
* @throws Exception
*/
protected String doIt() throws java.lang.Exception {
StringBuffer sql = null;
int no = 0;
String clientCheck = getWhereClause();
// Delete Old Imported
if (m_deleteOldImported) {
sql = new StringBuffer("DELETE I_BPartner " + "WHERE I_IsImported='Y'").append(clientCheck);
no = DB.executeUpdateEx(sql.toString(), get_TrxName());
log.fine("Delete Old Impored =" + no);
}
// Set Client, Org, IsActive, Created/Updated
sql = new StringBuffer("UPDATE I_BPartner " + "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 = ' '," + " I_IsImported = 'N' " + "WHERE I_IsImported<>'Y' OR I_IsImported IS NULL");
no = DB.executeUpdateEx(sql.toString(), get_TrxName());
log.fine("Reset=" + no);
ModelValidationEngine.get().fireImportValidate(this, null, null, ImportValidator.TIMING_BEFORE_VALIDATE);
// Set BP_Group
sql = new StringBuffer("UPDATE I_BPartner i " + "SET GroupValue=(SELECT MAX(Value) FROM C_BP_Group g WHERE g.IsDefault='Y'" + " AND g.AD_Client_ID=i.AD_Client_ID) ");
sql.append("WHERE GroupValue IS NULL AND C_BP_Group_ID IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdateEx(sql.toString(), get_TrxName());
log.fine("Set Group Default=" + no);
//
sql = new StringBuffer("UPDATE I_BPartner i " + "SET C_BP_Group_ID=(SELECT C_BP_Group_ID FROM C_BP_Group g" + " WHERE i.GroupValue=g.Value AND g.AD_Client_ID=i.AD_Client_ID) " + "WHERE C_BP_Group_ID IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdateEx(sql.toString(), get_TrxName());
log.fine("Set Group=" + no);
//
sql = new StringBuffer("UPDATE I_BPartner " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Group, ' " + "WHERE C_BP_Group_ID IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdateEx(sql.toString(), get_TrxName());
log.config("Invalid Group=" + no);
// Set Country
/**
sql = new StringBuffer ("UPDATE I_BPartner i "
+ "SET CountryCode=(SELECT CountryCode FROM C_Country c WHERE c.IsDefault='Y'"
+ " AND c.AD_Client_ID IN (0, i.AD_Client_ID) AND ROWNUM=1) "
+ "WHERE CountryCode IS NULL AND C_Country_ID IS NULL"
+ " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdateEx(sql.toString(), get_TrxName());
log.fine("Set Country Default=" + no);
**/
//
sql = new StringBuffer("UPDATE I_BPartner i " + "SET C_Country_ID=(SELECT C_Country_ID FROM C_Country c" + " WHERE i.CountryCode=c.CountryCode AND c.AD_Client_ID IN (0, i.AD_Client_ID)) " + "WHERE C_Country_ID IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdateEx(sql.toString(), get_TrxName());
log.fine("Set Country=" + no);
//
sql = new StringBuffer("UPDATE I_BPartner " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Country, ' " + "WHERE C_Country_ID IS NULL AND (City IS NOT NULL OR Address1 IS NOT NULL)" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdateEx(sql.toString(), get_TrxName());
log.config("Invalid Country=" + no);
// Set Region
sql = new StringBuffer("UPDATE I_BPartner i " + "Set RegionName=(SELECT MAX(Name) FROM C_Region r" + " WHERE r.IsDefault='Y' AND r.C_Country_ID=i.C_Country_ID" + " AND r.AD_Client_ID IN (0, i.AD_Client_ID)) ");
sql.append("WHERE RegionName IS NULL AND C_Region_ID IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdateEx(sql.toString(), get_TrxName());
log.fine("Set Region Default=" + no);
//
sql = new StringBuffer("UPDATE I_BPartner i " + "Set C_Region_ID=(SELECT C_Region_ID FROM C_Region r" + " WHERE r.Name=i.RegionName AND r.C_Country_ID=i.C_Country_ID" + " AND r.AD_Client_ID IN (0, i.AD_Client_ID)) " + "WHERE C_Region_ID IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdateEx(sql.toString(), get_TrxName());
log.fine("Set Region=" + no);
//
sql = new StringBuffer("UPDATE I_BPartner i " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Region, ' " + "WHERE C_Region_ID IS NULL " + " AND EXISTS (SELECT * FROM C_Country c" + " WHERE c.C_Country_ID=i.C_Country_ID AND c.HasRegion='Y')" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdateEx(sql.toString(), get_TrxName());
log.config("Invalid Region=" + no);
// Set Greeting
sql = new StringBuffer("UPDATE I_BPartner i " + "SET C_Greeting_ID=(SELECT C_Greeting_ID FROM C_Greeting g" + " WHERE i.BPContactGreeting=g.Name AND g.AD_Client_ID IN (0, i.AD_Client_ID)) " + "WHERE C_Greeting_ID IS NULL AND BPContactGreeting IS NOT NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdateEx(sql.toString(), get_TrxName());
log.fine("Set Greeting=" + no);
//
sql = new StringBuffer("UPDATE I_BPartner i " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Greeting, ' " + "WHERE C_Greeting_ID IS NULL AND BPContactGreeting IS NOT NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdateEx(sql.toString(), get_TrxName());
log.config("Invalid Greeting=" + no);
// Existing User. Lookup by AD_User.Email - BPartner
sql = new StringBuffer("UPDATE I_BPartner i " + "SET C_BPartner_ID=" + "(SELECT C_BPartner_ID FROM AD_User u " + "WHERE i.EMail=u.EMail AND u.AD_Client_ID=i.AD_Client_ID) " + "WHERE i.EMail IS NOT NULL AND I_IsImported='N'").append(clientCheck);
no = DB.executeUpdateEx(sql.toString(), get_TrxName());
log.fine("Found EMail User=" + no);
// Existing User. Lookup by AD_User.Email - AD_User
sql = new StringBuffer("UPDATE I_BPartner i " + "SET AD_User_ID=" + "(SELECT AD_User_ID FROM AD_User u " + "WHERE i.EMail=u.EMail AND u.AD_Client_ID=i.AD_Client_ID) " + "WHERE i.EMail IS NOT NULL AND I_IsImported='N'").append(clientCheck);
no = DB.executeUpdateEx(sql.toString(), get_TrxName());
log.fine("Found EMail User=" + no);
// Existing BPartner ? Match Value
sql = new StringBuffer("UPDATE I_BPartner i " + "SET C_BPartner_ID=(SELECT C_BPartner_ID FROM C_BPartner p" + " WHERE i.Value=p.Value AND p.AD_Client_ID=i.AD_Client_ID) " + "WHERE C_BPartner_ID IS NULL AND Value IS NOT NULL" + " AND I_IsImported='N'").append(clientCheck);
no = DB.executeUpdateEx(sql.toString(), get_TrxName());
log.fine("Found BPartner=" + no);
// Existing Contact ? Match Name
sql = new StringBuffer("UPDATE I_BPartner i " + "SET AD_User_ID=(SELECT AD_User_ID FROM AD_User c" + " WHERE i.ContactName=c.Name AND i.C_BPartner_ID=c.C_BPartner_ID AND c.AD_Client_ID=i.AD_Client_ID) " + "WHERE C_BPartner_ID IS NOT NULL AND AD_User_ID IS NULL AND ContactName IS NOT NULL" + " AND I_IsImported='N'").append(clientCheck);
no = DB.executeUpdateEx(sql.toString(), get_TrxName());
log.fine("Found Contact=" + no);
// Existing Location ? Exact Match
sql = new StringBuffer("UPDATE I_BPartner i " + "SET C_BPartner_Location_ID=(SELECT C_BPartner_Location_ID" + " FROM C_BPartner_Location bpl INNER JOIN C_Location l ON (bpl.C_Location_ID=l.C_Location_ID)" + " WHERE i.C_BPartner_ID=bpl.C_BPartner_ID AND bpl.AD_Client_ID=i.AD_Client_ID" + " AND (i.Address1=l.Address1 OR (i.Address1 IS NULL AND l.Address1 IS NULL))" + " AND (i.Address2=l.Address2 OR (i.Address2 IS NULL AND l.Address2 IS NULL))" + " AND (i.City=l.City OR (i.City IS NULL AND l.City IS NULL))" + " AND (i.Postal=l.Postal OR (i.Postal IS NULL AND l.Postal IS NULL))" + " AND (i.Postal_Add=l.Postal_Add OR (l.Postal_Add IS NULL AND l.Postal_Add IS NULL))" + " AND i.C_Region_ID=l.C_Region_ID AND i.C_Country_ID=l.C_Country_ID) " + "WHERE C_BPartner_ID IS NOT NULL AND C_BPartner_Location_ID IS NULL" + " AND I_IsImported='N'").append(clientCheck);
no = DB.executeUpdateEx(sql.toString(), get_TrxName());
log.fine("Found Location=" + no);
// Interest Area
sql = new StringBuffer("UPDATE I_BPartner i " + "SET R_InterestArea_ID=(SELECT R_InterestArea_ID FROM R_InterestArea ia " + "WHERE i.InterestAreaName=ia.Name AND ia.AD_Client_ID=i.AD_Client_ID) " + "WHERE R_InterestArea_ID IS NULL AND InterestAreaName IS NOT NULL" + " AND I_IsImported='N'").append(clientCheck);
no = DB.executeUpdateEx(sql.toString(), get_TrxName());
log.fine("Set Interest Area=" + no);
// Value is mandatory error
sql = new StringBuffer("UPDATE I_BPartner " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Value is mandatory, ' " + "WHERE Value IS NULL " + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdateEx(sql.toString(), get_TrxName());
log.config("Value is mandatory=" + no);
ModelValidationEngine.get().fireImportValidate(this, null, null, ImportValidator.TIMING_AFTER_VALIDATE);
commitEx();
if (p_IsValidateOnly) {
return "Validated";
}
// -------------------------------------------------------------------
int noInsert = 0;
int noUpdate = 0;
// Go through Records
sql = new StringBuffer("SELECT * FROM I_BPartner " + "WHERE I_IsImported='N'").append(clientCheck);
// gody: 20070113 - Order so the same values are consecutive.
sql.append(" ORDER BY Value, I_BPartner_ID");
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = DB.prepareStatement(sql.toString(), get_TrxName());
rs = pstmt.executeQuery();
// Remember Previous BP Value BP is only first one, others are contacts.
// All contacts share BP location.
// bp and bpl declarations before loop, we need them for data.
String Old_BPValue = "";
MBPartner bp = null;
MBPartnerLocation bpl = null;
while (rs.next()) {
// Remember Value - only first occurance of the value is BP
String New_BPValue = rs.getString("Value");
X_I_BPartner impBP = new X_I_BPartner(getCtx(), rs, get_TrxName());
log.fine("I_BPartner_ID=" + impBP.getI_BPartner_ID() + ", C_BPartner_ID=" + impBP.getC_BPartner_ID() + ", C_BPartner_Location_ID=" + impBP.getC_BPartner_Location_ID() + ", AD_User_ID=" + impBP.getAD_User_ID());
if (!New_BPValue.equals(Old_BPValue)) {
// **** Create/Update BPartner ****
bp = null;
if (// Insert new BPartner
impBP.getC_BPartner_ID() == 0) {
bp = new MBPartner(impBP);
ModelValidationEngine.get().fireImportValidate(this, impBP, bp, ImportValidator.TIMING_AFTER_IMPORT);
setTypeOfBPartner(impBP, bp);
if (bp.save()) {
impBP.setC_BPartner_ID(bp.getC_BPartner_ID());
log.finest("Insert BPartner - " + bp.getC_BPartner_ID());
noInsert++;
} else {
sql = new StringBuffer("UPDATE I_BPartner i " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||").append("'Cannot Insert BPartner, ' ").append("WHERE I_BPartner_ID=").append(impBP.getI_BPartner_ID());
DB.executeUpdateEx(sql.toString(), get_TrxName());
continue;
}
} else // Update existing BPartner
{
bp = new MBPartner(getCtx(), impBP.getC_BPartner_ID(), get_TrxName());
// bp.setValue(impBP.getValue());
if (impBP.getName() != null) {
bp.setName(impBP.getName());
bp.setName2(impBP.getName2());
}
if (impBP.getDUNS() != null)
bp.setDUNS(impBP.getDUNS());
if (impBP.getTaxID() != null)
bp.setTaxID(impBP.getTaxID());
if (impBP.getNAICS() != null)
bp.setNAICS(impBP.getNAICS());
if (impBP.getDescription() != null)
bp.setDescription(impBP.getDescription());
if (impBP.getC_BP_Group_ID() != 0)
bp.setC_BP_Group_ID(impBP.getC_BP_Group_ID());
// Employee values
if (impBP.getBirthday() != null)
bp.setBirthday(impBP.getBirthday());
if (impBP.getFathersName() != null)
bp.setFathersName(impBP.getFathersName());
if (impBP.getBloodGroup() != null)
bp.setBloodGroup(impBP.getBloodGroup());
if (impBP.getPlaceOfBirth() != null)
bp.setPlaceOfBirth(impBP.getPlaceOfBirth());
if (impBP.getGender() != null)
bp.setGender(impBP.getGender());
//
ModelValidationEngine.get().fireImportValidate(this, impBP, bp, ImportValidator.TIMING_AFTER_IMPORT);
setTypeOfBPartner(impBP, bp);
//
if (bp.save()) {
log.finest("Update BPartner - " + bp.getC_BPartner_ID());
noUpdate++;
} else {
sql = new StringBuffer("UPDATE I_BPartner i " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||").append("'Cannot Update BPartner, ' ").append("WHERE I_BPartner_ID=").append(impBP.getI_BPartner_ID());
DB.executeUpdateEx(sql.toString(), get_TrxName());
continue;
}
}
// **** Create/Update BPartner Location ****
bpl = null;
if (// Update Location
impBP.getC_BPartner_Location_ID() != 0) {
bpl = new MBPartnerLocation(getCtx(), impBP.getC_BPartner_Location_ID(), get_TrxName());
MLocation location = new MLocation(getCtx(), bpl.getC_Location_ID(), get_TrxName());
location.setC_Country_ID(impBP.getC_Country_ID());
location.setC_Region_ID(impBP.getC_Region_ID());
location.setCity(impBP.getCity());
location.setAddress1(impBP.getAddress1());
location.setAddress2(impBP.getAddress2());
location.setPostal(impBP.getPostal());
location.setPostal_Add(impBP.getPostal_Add());
if (!location.save())
log.warning("Location not updated");
else
bpl.setC_Location_ID(location.getC_Location_ID());
if (impBP.getPhone() != null)
bpl.setPhone(impBP.getPhone());
if (impBP.getPhone2() != null)
bpl.setPhone2(impBP.getPhone2());
if (impBP.getFax() != null)
bpl.setFax(impBP.getFax());
ModelValidationEngine.get().fireImportValidate(this, impBP, bpl, ImportValidator.TIMING_AFTER_IMPORT);
bpl.saveEx();
} else // New Location
if (impBP.getC_Country_ID() != 0 && impBP.getAddress1() != null && impBP.getCity() != null) {
MLocation location = new MLocation(getCtx(), impBP.getC_Country_ID(), impBP.getC_Region_ID(), impBP.getCity(), get_TrxName());
location.setAddress1(impBP.getAddress1());
location.setAddress2(impBP.getAddress2());
location.setPostal(impBP.getPostal());
location.setPostal_Add(impBP.getPostal_Add());
if (location.save())
log.finest("Insert Location - " + location.getC_Location_ID());
else {
rollback();
noInsert--;
sql = new StringBuffer("UPDATE I_BPartner i " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||").append("'Cannot Insert Location, ' ").append("WHERE I_BPartner_ID=").append(impBP.getI_BPartner_ID());
DB.executeUpdateEx(sql.toString(), get_TrxName());
continue;
}
//
bpl = new MBPartnerLocation(bp);
bpl.setC_Location_ID(location.getC_Location_ID());
bpl.setPhone(impBP.getPhone());
bpl.setPhone2(impBP.getPhone2());
bpl.setFax(impBP.getFax());
ModelValidationEngine.get().fireImportValidate(this, impBP, bpl, ImportValidator.TIMING_AFTER_IMPORT);
if (bpl.save()) {
log.finest("Insert BP Location - " + bpl.getC_BPartner_Location_ID());
impBP.setC_BPartner_Location_ID(bpl.getC_BPartner_Location_ID());
} else {
rollback();
noInsert--;
sql = new StringBuffer("UPDATE I_BPartner i " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||").append("'Cannot Insert BPLocation, ' ").append("WHERE I_BPartner_ID=").append(impBP.getI_BPartner_ID());
DB.executeUpdateEx(sql.toString(), get_TrxName());
continue;
}
}
}
Old_BPValue = New_BPValue;
// **** Create/Update Contact ****
MUser user = null;
if (impBP.getAD_User_ID() != 0) {
user = new MUser(getCtx(), impBP.getAD_User_ID(), get_TrxName());
if (user.getC_BPartner_ID() == 0)
user.setC_BPartner_ID(bp.getC_BPartner_ID());
else if (user.getC_BPartner_ID() != bp.getC_BPartner_ID()) {
rollback();
noInsert--;
sql = new StringBuffer("UPDATE I_BPartner i " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||").append("'BP of User <> BP, ' ").append("WHERE I_BPartner_ID=").append(impBP.getI_BPartner_ID());
DB.executeUpdateEx(sql.toString(), get_TrxName());
continue;
}
if (impBP.getC_Greeting_ID() != 0)
user.setC_Greeting_ID(impBP.getC_Greeting_ID());
String name = impBP.getContactName();
if (name == null || name.length() == 0)
name = impBP.getEMail();
user.setName(name);
if (impBP.getTitle() != null)
user.setTitle(impBP.getTitle());
if (impBP.getContactDescription() != null)
user.setDescription(impBP.getContactDescription());
if (impBP.getComments() != null)
user.setComments(impBP.getComments());
if (impBP.getPhone() != null)
user.setPhone(impBP.getPhone());
if (impBP.getPhone2() != null)
user.setPhone2(impBP.getPhone2());
if (impBP.getFax() != null)
user.setFax(impBP.getFax());
if (impBP.getEMail() != null)
user.setEMail(impBP.getEMail());
if (impBP.getBirthday() != null)
user.setBirthday(impBP.getBirthday());
if (bpl != null)
user.setC_BPartner_Location_ID(bpl.getC_BPartner_Location_ID());
ModelValidationEngine.get().fireImportValidate(this, impBP, user, ImportValidator.TIMING_AFTER_IMPORT);
if (user.save()) {
log.finest("Update BP Contact - " + user.getAD_User_ID());
} else {
rollback();
noInsert--;
sql = new StringBuffer("UPDATE I_BPartner i " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||").append("'Cannot Update BP Contact, ' ").append("WHERE I_BPartner_ID=").append(impBP.getI_BPartner_ID());
DB.executeUpdateEx(sql.toString(), get_TrxName());
continue;
}
} else // New Contact
if (impBP.getContactName() != null || impBP.getEMail() != null) {
user = new MUser(bp);
if (impBP.getC_Greeting_ID() != 0)
user.setC_Greeting_ID(impBP.getC_Greeting_ID());
String name = impBP.getContactName();
if (name == null || name.length() == 0)
name = impBP.getEMail();
user.setName(name);
user.setTitle(impBP.getTitle());
user.setDescription(impBP.getContactDescription());
user.setComments(impBP.getComments());
user.setPhone(impBP.getPhone());
user.setPhone2(impBP.getPhone2());
user.setFax(impBP.getFax());
user.setEMail(impBP.getEMail());
user.setBirthday(impBP.getBirthday());
if (bpl != null)
user.setC_BPartner_Location_ID(bpl.getC_BPartner_Location_ID());
ModelValidationEngine.get().fireImportValidate(this, impBP, user, ImportValidator.TIMING_AFTER_IMPORT);
if (user.save()) {
log.finest("Insert BP Contact - " + user.getAD_User_ID());
impBP.setAD_User_ID(user.getAD_User_ID());
} else {
rollback();
noInsert--;
sql = new StringBuffer("UPDATE I_BPartner i " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||").append("'Cannot Insert BPContact, ' ").append("WHERE I_BPartner_ID=").append(impBP.getI_BPartner_ID());
DB.executeUpdateEx(sql.toString(), get_TrxName());
continue;
}
}
// Interest Area
if (impBP.getR_InterestArea_ID() != 0 && user != null) {
MContactInterest ci = MContactInterest.get(getCtx(), impBP.getR_InterestArea_ID(), user.getAD_User_ID(), true, get_TrxName());
// don't subscribe or re-activate
ci.saveEx();
}
//
impBP.setI_IsImported(true);
impBP.setProcessed(true);
impBP.setProcessing(false);
impBP.saveEx();
commitEx();
}
// for all I_Product
DB.close(rs, pstmt);
} catch (SQLException e) {
rollback();
//log.log(Level.SEVERE, "", e);
throw new DBException(e, sql.toString());
} finally {
DB.close(rs, pstmt);
rs = null;
pstmt = null;
// Set Error to indicator to not imported
sql = new StringBuffer("UPDATE I_BPartner " + "SET I_IsImported='N', Updated=SysDate " + "WHERE I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdateEx(sql.toString(), get_TrxName());
addLog(0, null, new BigDecimal(no), "@Errors@");
addLog(0, null, new BigDecimal(noInsert), "@C_BPartner_ID@: @Inserted@");
addLog(0, null, new BigDecimal(noUpdate), "@C_BPartner_ID@: @Updated@");
}
return "";
}
Also used :
DBException(org.adempiere.exceptions.DBException)
SQLException(java.sql.SQLException)
PreparedStatement(java.sql.PreparedStatement)
MBPartner(org.compiere.model.MBPartner)
X_I_BPartner(org.compiere.model.X_I_BPartner)
BigDecimal(java.math.BigDecimal)
MBPartnerLocation(org.compiere.model.MBPartnerLocation)
MContactInterest(org.compiere.model.MContactInterest)
ResultSet(java.sql.ResultSet)
MLocation(org.compiere.model.MLocation)
MUser(org.compiere.model.MUser)
Example 13 with DBException
use of org.adempiere.exceptions.DBException in project adempiere by adempiere.
the class CityAutoCompleter method fillList.
public void fillList() {
// Carlos Ruiz - globalqss - improve to avoid going to the database on every keystroke
list.clear();
listShow.clear();
ArrayList<Object> params = new ArrayList<Object>();
final StringBuffer sql = new StringBuffer("SELECT cy.C_City_ID, cy.Name, cy.C_Region_ID, r.Name" + " FROM C_City cy" + " LEFT OUTER JOIN C_Region r ON (r.C_Region_ID=cy.C_Region_ID)" + " WHERE cy.AD_Client_ID IN (0,?)");
params.add(getAD_Client_ID());
if (getC_Region_ID() > 0) {
sql.append(" AND cy.C_Region_ID=?");
params.add(getC_Region_ID());
}
if (getC_Country_ID() > 0) {
sql.append(" AND cy.C_Country_ID=?");
params.add(getC_Country_ID());
}
sql.append(" ORDER BY cy.Name, r.Name");
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = DB.prepareStatement(sql.toString(), null);
DB.setParameters(pstmt, params);
rs = pstmt.executeQuery();
int i = 0;
while (rs.next()) {
CityVO vo = new CityVO(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getString(4));
list.add(vo);
if (i <= m_maxRows) {
listShow.add(vo);
} else if (i == m_maxRows + 1 && i > 0) {
listShow.add(ITEM_More);
}
i++;
}
} catch (SQLException e) {
throw new DBException(e, sql.toString());
} finally {
DB.close(rs, pstmt);
rs = null;
pstmt = null;
}
//
this.listBox.setListData(listShow.toArray());
}
Also used :
DBException(org.adempiere.exceptions.DBException)
SQLException(java.sql.SQLException)
ArrayList(java.util.ArrayList)
ResultSet(java.sql.ResultSet)
PreparedStatement(java.sql.PreparedStatement)
Example 14 with DBException
use of org.adempiere.exceptions.DBException in project adempiere by adempiere.
the class OrderReceiptIssue method executeQuery.
/**
* Query Info
*/
public void executeQuery(IMiniTable issue) {
final String sql = "SELECT " + // 1
"obl.PP_Order_BOMLine_ID," + // 2
"obl.IsCritical," + // 3
"p.Value," + // 4,5
"obl.M_Product_ID,p.Name," + // 6,7
"p.C_UOM_ID,u.Name," + // 8
"obl.QtyRequired," + // 9
"obl.QtyReserved," + // 10
"bomQtyAvailable(obl.M_Product_ID,obl.M_Warehouse_ID,0 ) AS QtyAvailable," + // 11
"bomQtyOnHand(obl.M_Product_ID,obl.M_Warehouse_ID,0) AS QtyOnHand," + // 12
"p.M_Locator_ID," + // 13,14
"obl.M_Warehouse_ID,w.Name," + // 15
"obl.QtyBom," + // 16
"obl.isQtyPercentage," + // 17
"obl.QtyBatch," + // 18
"obl.ComponentType," + // 19
"obl.QtyRequired - QtyDelivered AS QtyOpen," + // 20
"obl.QtyDelivered" + " FROM PP_Order_BOMLine obl" + " INNER JOIN M_Product p ON (obl.M_Product_ID = p.M_Product_ID) " + " INNER JOIN C_UOM u ON (p.C_UOM_ID = u.C_UOM_ID) " + " INNER JOIN M_Warehouse w ON (w.M_Warehouse_ID = obl.M_Warehouse_ID) " + " WHERE obl.PP_Order_ID = ?" + " ORDER BY obl." + MPPOrderBOMLine.COLUMNNAME_Line;
// reset table
int row = 0;
issue.setRowCount(row);
// Execute
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, getPP_Order_ID());
rs = pstmt.executeQuery();
while (rs.next()) {
// extend table
issue.setRowCount(row + 1);
// set values
// issue.
IDColumn id = new IDColumn(rs.getInt(1));
BigDecimal qtyBom = rs.getBigDecimal(15);
Boolean isQtyPercentage = rs.getString(16).equals("Y");
Boolean isCritical = rs.getString(2).equals("Y");
BigDecimal qtyBatch = rs.getBigDecimal(17);
BigDecimal qtyRequired = rs.getBigDecimal(8);
BigDecimal qtyOnHand = rs.getBigDecimal(11);
BigDecimal qtyOpen = rs.getBigDecimal(19);
BigDecimal qtyDelivered = rs.getBigDecimal(20);
String componentType = rs.getString(18);
BigDecimal toDeliverQty = getToDeliverQty();
BigDecimal openQty = getOpenQty();
BigDecimal scrapQty = getScrapQty();
BigDecimal componentToDeliverQty = Env.ZERO;
BigDecimal componentScrapQty = Env.ZERO;
BigDecimal componentQtyReq = Env.ZERO;
BigDecimal componentQtyToDel = Env.ZERO;
id.setSelected(isOnlyReceipt());
// PP_OrderBOMLine_ID
issue.setValueAt(id, row, 0);
// IsCritical
issue.setValueAt(isCritical, row, 1);
// Product's Search
issue.setValueAt(rs.getString(3), row, 2);
// key
// Product
issue.setValueAt(new KeyNamePair(rs.getInt(4), rs.getString(5)), row, 3);
// UOM
issue.setValueAt(new KeyNamePair(rs.getInt(6), rs.getString(7)), row, 4);
// ... 5 - ASI
// QtyRequired
issue.setValueAt(qtyRequired, row, 6);
// QtyDelivered
issue.setValueAt(qtyDelivered, row, 7);
// ... 8, 9, 10 - QtyToDeliver, QtyScrap, QtyOnHand
// OnHand
issue.setValueAt(qtyOnHand, row, 10);
// QtyReserved
issue.setValueAt(rs.getBigDecimal(9), row, 11);
// QtyAvailable
issue.setValueAt(rs.getBigDecimal(10), row, 12);
// ... 13 - M_Locator_ID
// Warehouse
issue.setValueAt(new KeyNamePair(rs.getInt(13), rs.getString(14)), row, 14);
// QtyBom
issue.setValueAt(qtyBom, row, 15);
// isQtyPercentage
issue.setValueAt(isQtyPercentage, row, 16);
// QtyBatch
issue.setValueAt(qtyBatch, row, 17);
if (componentType.equals(MPPProductBOMLine.COMPONENTTYPE_Component) || componentType.equals(MPPProductBOMLine.COMPONENTTYPE_Packing)) {
// If the there is product on hand and product is required
// the product should be selected
id.setSelected(qtyOnHand.signum() > 0 && qtyRequired.signum() > 0);
// PP_OrderBOMLine_ID
issue.setValueAt(id, row, 0);
if (isQtyPercentage) {
// If the quantity of product is calculated as a
// percentage
BigDecimal qtyBatchPerc = qtyBatch.divide(Env.ONEHUNDRED, 8, RoundingMode.HALF_UP);
if (isBackflush()) {
// Component from Qty To Deliver
if (qtyRequired.signum() == 0 || qtyOpen.signum() == 0) {
componentToDeliverQty = Env.ZERO;
} else {
componentToDeliverQty = toDeliverQty.multiply(qtyBatchPerc);
if (qtyRequired.subtract(qtyDelivered).signum() < 0 | componentToDeliverQty.signum() == 0)
componentToDeliverQty = qtyRequired.subtract(qtyDelivered);
}
if (componentToDeliverQty.signum() != 0) {
// TODO: arhipac: teo_sarca: is this a bug ?
// ...instead of toDeliverQty, qtyRequired
// should be used!
// componentQtyReq =
// toDeliverQty.multiply(qtyBatchPerc); // TODO:
// set scale 4
componentQtyToDel = componentToDeliverQty.setScale(4, BigDecimal.ROUND_HALF_UP);
// issue.setValueAt(toDeliverQty.multiply(qtyBatchPerc),
// row, 6); // QtyRequired
// QtyToDelivery
issue.setValueAt(componentToDeliverQty, row, 8);
}
} else {
// Only Issue - Calculate Component from Open
// Qty
componentToDeliverQty = qtyOpen;
if (componentToDeliverQty.signum() != 0) {
// scale 4
componentQtyReq = openQty.multiply(qtyBatchPerc);
componentQtyToDel = componentToDeliverQty.setScale(4, BigDecimal.ROUND_HALF_UP);
// QtyToDelivery
issue.setValueAt(componentToDeliverQty.setScale(8, BigDecimal.ROUND_HALF_UP), row, 8);
// QtyRequired
issue.setValueAt(openQty.multiply(qtyBatchPerc), row, 6);
}
}
if (scrapQty.signum() != 0) {
componentScrapQty = scrapQty.multiply(qtyBatchPerc);
if (componentScrapQty.signum() != 0) {
// QtyScrap
issue.setValueAt(componentScrapQty, row, 9);
}
} else
// QtyScrap
issue.setValueAt(componentScrapQty, row, 9);
} else {
// Absolute Qtys (not Percentage)
if (isBackflush()) {
// Component from Qty To Deliver
if (componentType.equals(MPPProductBOMLine.COMPONENTTYPE_Packing))
componentToDeliverQty = qtyRequired.subtract(qtyDelivered);
else
// TODO: set Number scale
componentToDeliverQty = toDeliverQty.multiply(qtyBom);
if (componentToDeliverQty.signum() != 0) {
if (componentType.equals(MPPProductBOMLine.COMPONENTTYPE_Packing))
componentQtyReq = qtyRequired.subtract(qtyDelivered);
else
componentQtyReq = toDeliverQty.multiply(qtyBom);
componentQtyToDel = componentToDeliverQty;
// QtyRequired
issue.setValueAt(componentQtyReq, row, 6);
// QtyToDelivery
issue.setValueAt(componentToDeliverQty, row, 8);
}
} else {
// Only Issue - Calculate Component from Open
// Qty
componentToDeliverQty = qtyOpen;
if (componentToDeliverQty.signum() != 0) {
if (componentType.equals(MPPProductBOMLine.COMPONENTTYPE_Packing))
componentQtyReq = qtyOpen;
else
componentQtyReq = openQty.multiply(qtyBom);
componentQtyToDel = componentToDeliverQty;
// QtyRequired
issue.setValueAt(componentQtyReq, row, 6);
// QtyToDelivery
issue.setValueAt(componentToDeliverQty, row, 8);
}
}
if (scrapQty.signum() != 0) {
// TODO:
componentScrapQty = scrapQty.multiply(qtyBom);
// scale
if (componentScrapQty.signum() != 0) {
// ScrapQty
issue.setValueAt(componentScrapQty, row, 9);
}
} else
// ScrapQty
issue.setValueAt(componentScrapQty, row, 9);
}
} else if (componentType.equals(MPPProductBOMLine.COMPONENTTYPE_Tools)) {
// TODO; set Number scale
componentToDeliverQty = qtyBom;
if (componentToDeliverQty.signum() != 0) {
componentQtyReq = qtyBom;
componentQtyToDel = componentToDeliverQty;
// QtyRequired
issue.setValueAt(qtyBom, row, 6);
// QtyToDelivery
issue.setValueAt(componentToDeliverQty, row, 8);
}
} else {
// QtyRequired
issue.setValueAt(Env.ZERO, row, 6);
//issue.setValueAt(Env.ZERO, row, 8); // QtyToDelivery
}
row++;
if (isOnlyIssue() || isBackflush()) {
int warehouse_id = rs.getInt(13);
int product_id = rs.getInt(4);
row += lotes(row, id, warehouse_id, product_id, componentQtyReq, componentQtyToDel, issue);
}
}
// while
} catch (SQLException e) {
throw new DBException(e);
} finally {
DB.close(rs, pstmt);
rs = null;
pstmt = null;
}
issue.autoSize();
}
Also used :
IDColumn(org.compiere.minigrid.IDColumn)
DBException(org.adempiere.exceptions.DBException)
SQLException(java.sql.SQLException)
ResultSet(java.sql.ResultSet)
PreparedStatement(java.sql.PreparedStatement)
KeyNamePair(org.compiere.util.KeyNamePair)
BigDecimal(java.math.BigDecimal)
Example 15 with DBException
use of org.adempiere.exceptions.DBException in project adempiere by adempiere.
the class VMRPDetailed method setMRP.
/**
* Fill header MRP information
*/
private void setMRP() {
int M_Product_ID = getM_Product_ID();
int M_AttributeSetInstance_ID = getM_AttributeSetInstance_ID();
int M_Warehouse_ID = getM_Warehouse_ID();
// Check Product (mandatory):
if (M_Product_ID <= 0)
return;
// Set Quantities
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
StringBuffer sql = new StringBuffer("SELECT ").append("BOMQtyOnHandASI(M_Product_ID,?,?,?) as qtyonhand, ").append("BOMQtyReservedASI(M_Product_ID,?,?,?) as qtyreserved, ").append("BOMQtyAvailableASI(M_Product_ID,?,?,?) as qtyavailable, ").append("BOMQtyOrderedASI(M_Product_ID,?,?,?) as qtyordered").append(" FROM M_Product WHERE M_Product_ID=?");
pstmt = DB.prepareStatement(sql.toString(), null);
DB.setParameters(pstmt, new Object[] { getM_AttributeSetInstance_ID(), getM_Warehouse_ID(), 0, getM_AttributeSetInstance_ID(), getM_Warehouse_ID(), 0, getM_AttributeSetInstance_ID(), getM_Warehouse_ID(), 0, getM_AttributeSetInstance_ID(), getM_Warehouse_ID(), 0, getM_Product_ID() });
rs = pstmt.executeQuery();
while (rs.next()) {
fOnhand.setValue(rs.getBigDecimal(1));
fReserved.setValue(rs.getBigDecimal(2));
fAvailable.setValue(rs.getBigDecimal(3));
fOrdered.setValue(rs.getBigDecimal(4));
}
} catch (SQLException ex) {
throw new DBException(ex);
} finally {
DB.close(rs, pstmt);
rs = null;
pstmt = null;
}
//
// Set UOM:
int uom_id = MProduct.get(getCtx(), M_Product_ID).getC_UOM_ID();
MUOM um = MUOM.get(getCtx(), uom_id);
KeyNamePair kum = new KeyNamePair(um.getC_UOM_ID(), um.get_Translation(MUOM.COLUMNNAME_Name));
fUOM.setText(kum.toString());
//
// Set Replenish Min Level:
BigDecimal replenishLevelMin = Env.ZERO;
if (getM_Warehouse_ID() > 0) {
String sql = "SELECT Level_Min FROM M_Replenish" + " WHERE AD_Client_ID=? AND M_Product_ID=? AND M_Warehouse_ID=?";
replenishLevelMin = DB.getSQLValueBD(null, sql, AD_Client_ID, M_Product_ID, M_Warehouse_ID);
}
fReplenishMin.setValue(replenishLevelMin);
}
Also used :
DBException(org.adempiere.exceptions.DBException)
MUOM(org.compiere.model.MUOM)
SQLException(java.sql.SQLException)
ResultSet(java.sql.ResultSet)
PreparedStatement(java.sql.PreparedStatement)
KeyNamePair(org.compiere.util.KeyNamePair)
ALayoutConstraint(org.compiere.apps.ALayoutConstraint)
BigDecimal(java.math.BigDecimal)
Aggregations
DBException (org.adempiere.exceptions.DBException)89
SQLException (java.sql.SQLException)82
PreparedStatement (java.sql.PreparedStatement)75
ResultSet (java.sql.ResultSet)75
BigDecimal (java.math.BigDecimal)27
ArrayList (java.util.ArrayList)23
Timestamp (java.sql.Timestamp)15
POResultSet (org.compiere.model.POResultSet)8
AdempiereException (org.adempiere.exceptions.AdempiereException)6
KeyNamePair (org.compiere.util.KeyNamePair)5
Connection (java.sql.Connection)4
Savepoint (java.sql.Savepoint)4
MProduct (org.compiere.model.MProduct)4
Trx (org.compiere.util.Trx)4
Date (java.util.Date)2
AtomicBoolean (java.util.concurrent.atomic.AtomicBoolean)2
IDColumn (org.compiere.minigrid.IDColumn)2
MLocator (org.compiere.model.MLocator)2
MTable (org.compiere.model.MTable)2
MUOM (org.compiere.model.MUOM)2
