importgljournal.java

大家共享愉快, 共享愉快, 共享愉快, 共享愉快,共享愉快

		sql = new StringBuffer ("SELECT SUM(AmtSourceDr)-SUM(AmtSourceCr), SUM(AmtAcctDr)-SUM(AmtAcctCr) "
			+ "FROM I_GLJournal "
			+ "WHERE I_IsImported='N'").append (clientCheck);
		PreparedStatement pstmt = null;
		try
		{
			pstmt = DB.prepareStatement (sql.toString(), get_TrxName());
			ResultSet rs = pstmt.executeQuery ();
			if (rs.next ())
			{
				BigDecimal source = rs.getBigDecimal(1);
				BigDecimal acct = rs.getBigDecimal(2);
				if (source != null && source.signum() == 0
					&& acct != null && acct.signum() == 0)
					log.info ("Import Balance = 0");
				else
					log.warning("Balance Source=" + source  + ", Acct=" + acct);
				if (source != null)
					addLog (0, null, source, "@AmtSourceDr@ - @AmtSourceCr@");
				if (acct != null)
					addLog (0, null, acct, "@AmtAcctDr@ - @AmtAcctCr@");
			}
			rs.close ();
			pstmt.close ();
			pstmt = null;
		}
		catch (SQLException ex)
		{
			log.log(Level.SEVERE, sql.toString(), ex);
		}
		try
		{
			if (pstmt != null)
				pstmt.close ();
		}
		catch (SQLException ex1)
		{
		}
		pstmt = null;

		//	Count Errors
		int errors = DB.getSQLValue(get_TrxName(), 
			"SELECT COUNT(*) FROM I_GLJournal WHERE I_IsImported NOT IN ('Y','N')" + clientCheck);

		if (errors != 0)
		{
			if (m_IsValidateOnly || m_IsImportOnlyNoErrors)
				throw new Exception ("@Errors@=" + errors);
		}
		else if (m_IsValidateOnly)
			return "@Errors@=" + errors;

		log.info("Validation Errors=" + errors);
		commit();
		
		/*********************************************************************/

		int noInsert = 0;
		int noInsertJournal = 0;
		int noInsertLine = 0;

		MJournalBatch batch = null;		//	Change Batch per Batch DocumentNo
		String BatchDocumentNo = "";
		MJournal journal = null;
		String JournalDocumentNo = "";
		Timestamp DateAcct = null;

		//	Go through Journal Records
		sql = new StringBuffer ("SELECT * FROM I_GLJournal "
			+ "WHERE I_IsImported='N'").append (clientCheck)
			.append(" ORDER BY COALESCE(BatchDocumentNo, TO_NCHAR(I_GLJournal_ID)),	COALESCE(JournalDocumentNo, TO_NCHAR(I_GLJournal_ID)), C_AcctSchema_ID, PostingType, C_DocType_ID, GL_Category_ID, C_Currency_ID, TRUNC(DateAcct), Line, I_GLJournal_ID");
		try
		{
			pstmt = DB.prepareStatement (sql.toString (), get_TrxName());
			ResultSet rs = pstmt.executeQuery ();
			//
			while (rs.next())
			{
				X_I_GLJournal imp = new X_I_GLJournal (getCtx (), rs, get_TrxName());

				//	New Batch if Batch Document No changes
				String impBatchDocumentNo = imp.getBatchDocumentNo();
				if (impBatchDocumentNo == null)
					impBatchDocumentNo = "";
				if (batch == null
					|| imp.isCreateNewBatch()
					|| journal.getC_AcctSchema_ID() != imp.getC_AcctSchema_ID()
					|| !BatchDocumentNo.equals(impBatchDocumentNo))
				{
					BatchDocumentNo = impBatchDocumentNo;	//	cannot compare real DocumentNo
					batch = new MJournalBatch (getCtx(), 0, null);
					batch.setClientOrg(imp.getAD_Client_ID(), imp.getAD_OrgDoc_ID());
					if (imp.getBatchDocumentNo() != null 
						&& imp.getBatchDocumentNo().length() > 0)
						batch.setDocumentNo (imp.getBatchDocumentNo());
					batch.setC_DocType_ID(imp.getC_DocType_ID());
					batch.setPostingType(imp.getPostingType());
					String description = imp.getBatchDescription();
					if (description == null || description.length() == 0)
						description = "*Import-";
					else
						description += " *Import-";
					description += new Timestamp(System.currentTimeMillis());
					batch.setDescription(description);
					if (!batch.save())
					{
						log.log(Level.SEVERE, "Batch not saved");
						Exception ex = CLogger.retrieveException();
						if (ex != null)
						{
							addLog (0, null, null, ex.getLocalizedMessage());
							throw ex;
						}
						break;
					}
					noInsert++;
					journal = null;
				}
				//	Journal
				String impJournalDocumentNo = imp.getJournalDocumentNo();
				if (impJournalDocumentNo == null)
					impJournalDocumentNo = "";
				Timestamp impDateAcct = TimeUtil.getDay(imp.getDateAcct());
				if (journal == null
					|| imp.isCreateNewJournal()
					|| !JournalDocumentNo.equals(impJournalDocumentNo)
					|| journal.getC_DocType_ID() != imp.getC_DocType_ID()
					|| journal.getGL_Category_ID() != imp.getGL_Category_ID()
					|| !journal.getPostingType().equals(imp.getPostingType())
					|| journal.getC_Currency_ID() != imp.getC_Currency_ID()
					|| !impDateAcct.equals(DateAcct)
				)
				{
					JournalDocumentNo = impJournalDocumentNo;	//	cannot compare real DocumentNo
					DateAcct = impDateAcct;
					journal = new MJournal (getCtx(), 0, get_TrxName());
					journal.setGL_JournalBatch_ID(batch.getGL_JournalBatch_ID());
					journal.setClientOrg(imp.getAD_Client_ID(), imp.getAD_OrgDoc_ID());
					//
					String description = imp.getBatchDescription();
					if (description == null || description.length() == 0)
						description = "(Import)";
					journal.setDescription (description);
					if (imp.getJournalDocumentNo() != null && imp.getJournalDocumentNo().length() > 0)
						journal.setDocumentNo (imp.getJournalDocumentNo());
					//
					journal.setC_AcctSchema_ID (imp.getC_AcctSchema_ID());
					journal.setC_DocType_ID (imp.getC_DocType_ID());
					journal.setGL_Category_ID (imp.getGL_Category_ID());
					journal.setPostingType (imp.getPostingType());
					journal.setGL_Budget_ID(imp.getGL_Budget_ID());
					//
					journal.setCurrency (imp.getC_Currency_ID(), imp.getC_ConversionType_ID(), imp.getCurrencyRate());
					//
					journal.setC_Period_ID(imp.getC_Period_ID());
					journal.setDateAcct(imp.getDateAcct());		//	sets Period if not defined
					journal.setDateDoc (imp.getDateAcct());
					//
					if (!journal.save())
					{
						log.log(Level.SEVERE, "Journal not saved");
						Exception ex = CLogger.retrieveException();
						if (ex != null)
						{
							addLog (0, null, null, ex.getLocalizedMessage());
							throw ex;
						}
						break;
					}
					noInsertJournal++;
				}

				//	Lines
				MJournalLine line = new MJournalLine (journal);
				//
				line.setDescription(imp.getDescription());
				line.setCurrency (imp.getC_Currency_ID(), imp.getC_ConversionType_ID(), imp.getCurrencyRate());
				//	Set/Get Account Combination
				if (imp.getC_ValidCombination_ID() == 0)
				{
					MAccount acct = MAccount.get(getCtx(), imp.getAD_Client_ID(), imp.getAD_Org_ID(), 
						imp.getC_AcctSchema_ID(), imp.getAccount_ID(), 0,
						imp.getM_Product_ID(), imp.getC_BPartner_ID(), imp.getAD_OrgTrx_ID(),
						imp.getC_LocFrom_ID(), imp.getC_LocTo_ID(), imp.getC_SalesRegion_ID(),
						imp.getC_Project_ID(), imp.getC_Campaign_ID(), imp.getC_Activity_ID(),
						imp.getUser1_ID(), imp.getUser2_ID(), 0, 0);
					if (acct != null && acct.get_ID() == 0)
						acct.save();
					if (acct == null || acct.get_ID() == 0)
					{
						imp.setI_ErrorMsg("ERROR creating Account");
						imp.setI_IsImported(false);
						imp.save();
						continue;
					}
					else
					{
						line.setC_ValidCombination_ID(acct.get_ID());
						imp.setC_ValidCombination_ID(acct.get_ID());
					}
				}
				else
					line.setC_ValidCombination_ID (imp.getC_ValidCombination_ID());
				//
				line.setLine (imp.getLine());
				line.setAmtSourceCr (imp.getAmtSourceCr());
				line.setAmtSourceDr (imp.getAmtSourceDr());
				line.setAmtAcct (imp.getAmtAcctDr(), imp.getAmtAcctCr());	//	only if not 0
				line.setDateAcct (imp.getDateAcct());
				//
				line.setC_UOM_ID(imp.getC_UOM_ID());
				line.setQty(imp.getQty());
				//
				if (line.save())
				{
					imp.setGL_JournalBatch_ID(batch.getGL_JournalBatch_ID());
					imp.setGL_Journal_ID(journal.getGL_Journal_ID());
					imp.setGL_JournalLine_ID(line.getGL_JournalLine_ID());
					imp.setI_IsImported(true);
					imp.setProcessed(true);
					if (imp.save())
						noInsertLine++;
				}
			}	//	while records
			rs.close();
			pstmt.close();
		}
		catch (Exception e)
		{
			log.log(Level.SEVERE, "", e);
		}
		//	clean up
		try
		{
			if (pstmt != null)
				pstmt.close ();
		}
		catch (SQLException ex1)
		{
		}
		pstmt = null;

		//	Set Error to indicator to not imported
		sql = new StringBuffer ("UPDATE I_GLJournal "
			+ "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), "@GL_JournalBatch_ID@: @Inserted@");
		addLog (0, null, new BigDecimal (noInsertJournal), "@GL_Journal_ID@: @Inserted@");
		addLog (0, null, new BigDecimal (noInsertLine), "@GL_JournalLine_ID@: @Inserted@");
		return "";
	}	//	doIt

}	//	ImportGLJournal