importbankstatement.java

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

			+ " WHERE i.C_Payment_ID IS NOT NULL "
			+ " AND i.C_BPartner_ID IS NOT NULL "
			+ " AND p.C_BPartner_ID IS NOT NULL "
			+ " AND p.C_BPartner_ID<>i.C_BPartner_ID "
			+ " GROUP BY I_BankStatement_ID "
			+ ")").append(clientCheck);
		no = DB.executeUpdate(sql.toString(), get_TrxName());
		if (no != 0)
			log.info("Payment<->BPartner Mismatch=" + no);
			
		//	Check Invoice<->BPartner combination
		sql = new StringBuffer("UPDATE I_BankStatement "
			+ "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'Err=Invalid Invoice<->BPartner, ' "
			+ "WHERE I_BankStatement_ID IN "
			+ "( "
			+ " SELECT I_BankStatement_ID "
			+ " FROM I_BankStatement i, C_Invoice v "
			+ " WHERE i.C_BPartner_ID IS NOT NULL "
			+ " AND i.C_Invoice_ID IS NOT NULL "
			+ " AND v.C_BPartner_ID IS NOT NULL "
			+ " AND v.C_BPartner_ID<>i.C_BPartner_ID "
			+ " GROUP BY I_BankStatement_ID "
			+ ")").append(clientCheck);
		no = DB.executeUpdate(sql.toString(), get_TrxName());
		if (no != 0)
			log.info("Invoice<->BPartner Mismatch=" + no);
			
		//	Check Invoice.BPartner<->Payment.BPartner combination
		sql = new StringBuffer("UPDATE I_BankStatement "
			+ "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'Err=Invalid Invoice.BPartner<->Payment.BPartner, ' "
			+ "WHERE I_BankStatement_ID IN "
			+ "( "
			+ " SELECT I_BankStatement_ID "
			+ " FROM I_BankStatement i, C_Invoice v, C_Payment p "
			+ " WHERE p.C_Invoice_ID<>v.C_Invoice_ID"
			+ " AND i.C_Invoice_ID IS NOT NULL "
			+ " AND i.C_Payment_ID IS NOT NULL "
			+ " AND v.C_BPartner_ID IS NOT NULL "
			+ " AND p.C_BPartner_ID IS NOT NULL "
			+ " AND v.C_BPartner_ID<>p.C_BPartner_ID "
			+ " GROUP BY I_BankStatement_ID "
			+ ")").append(clientCheck);
		no = DB.executeUpdate(sql.toString(), get_TrxName());
		if (no != 0)
			log.info("Invoice.BPartner<->Payment.BPartner Mismatch=" + no);
			
		//	Detect Duplicates
		 sql = new StringBuffer("SELECT i.I_BankStatement_ID, l.C_BankStatementLine_ID, i.EftTrxID "
			+ "FROM I_BankStatement i, C_BankStatement s, C_BankStatementLine l "
			+ "WHERE i.I_isImported='N' "
			+ "AND s.C_BankStatement_ID=l.C_BankStatement_ID "
			+ "AND i.EftTrxID IS NOT NULL AND "
			//	Concatinate EFT Info
			+ "(l.EftTrxID||l.EftAmt||l.EftStatementLineDate||l.EftValutaDate||l.EftTrxType||l.EftCurrency||l.EftReference||s.EftStatementReference "
			+ "||l.EftCheckNo||l.EftMemo||l.EftPayee||l.EftPayeeAccount) "
			+ "= "
			+ "(i.EftTrxID||i.EftAmt||i.EftStatementLineDate||i.EftValutaDate||i.EftTrxType||i.EftCurrency||i.EftReference||i.EftStatementReference "
			+ "||i.EftCheckNo||i.EftMemo||i.EftPayee||i.EftPayeeAccount) ");
		
		StringBuffer updateSql = new StringBuffer("UPDATE I_Bankstatement "
				+ "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'Err=Duplicate['||?||']' "
				+ "WHERE I_BankStatement_ID=?").append(clientCheck);
		PreparedStatement pupdt = DB.prepareStatement(updateSql.toString(), get_TrxName());
		
		PreparedStatement pstmtDuplicates = null;
		no = 0;
		try
		{
			pstmtDuplicates = DB.prepareStatement(sql.toString(), get_TrxName());
			ResultSet rs = pstmtDuplicates.executeQuery();
			while (rs.next())
			{
				String info = "Line_ID=" + rs.getInt(2)		//	l.C_BankStatementLine_ID
				 + ",EDTTrxID=" + rs.getString(3);			//	i.EftTrxID
				pupdt.setString(1, info);	
				pupdt.setInt(2, rs.getInt(1));	//	i.I_BankStatement_ID
				pupdt.executeUpdate();
				no++;
			}
			rs.close();
			pstmtDuplicates.close();
			pupdt.close();
			
			rs = null;
			pstmtDuplicates = null;
			pupdt = null;
		}
		catch(Exception e)
		{
			log.log(Level.SEVERE, "DetectDuplicates " + e.getMessage());
		}
		if (no != 0)
			log.info("Duplicates=" + no);
		
		commit();
		
		//Import Bank Statement
		sql = new StringBuffer("SELECT * FROM I_BankStatement"
			+ " WHERE I_IsImported='N'"
			+ " ORDER BY C_BankAccount_ID, Name, EftStatementDate, EftStatementReference");
			
		MBankStatement statement = null;
		MBankAccount account = null;
		PreparedStatement pstmt = null;
		int lineNo = 10;
		int noInsert = 0;
		int noInsertLine = 0;
		try
		{
			pstmt = DB.prepareStatement(sql.toString(), get_TrxName());
			ResultSet rs = pstmt.executeQuery();
				
			while (rs.next())
			{ 
				X_I_BankStatement imp = new X_I_BankStatement(m_ctx, rs, get_TrxName());
				//	Get the bank account for the first statement
				if (account == null)
				{
					account = new MBankAccount(m_ctx, imp.getC_BankAccount_ID(), get_TrxName());
					statement = null;
					log.info("New Statement, Account=" + account.getAccountNo());
				}
				//	Create a new Bank Statement for every account
				else if (account.getC_BankAccount_ID() != imp.getC_BankAccount_ID())
				{
					account = new MBankAccount(m_ctx, imp.getC_BankAccount_ID(), get_TrxName());
					statement = null;
					log.info("New Statement, Account=" + account.getAccountNo());
				}
				//	Create a new Bank Statement for every statement name
				else if ((statement.getName() != null) && (imp.getName() != null))
				{
					if (!statement.getName().equals(imp.getName()))
					{
						statement = null;
						log.info("New Statement, Statement Name=" + imp.getName());
					}
				}
				//	Create a new Bank Statement for every statement reference
				else if ((statement.getEftStatementReference() != null) && (imp.getEftStatementReference() != null))
				{
					if (!statement.getEftStatementReference().equals(imp.getEftStatementReference()))
					{
						statement = null;
						log.info("New Statement, Statement Reference=" + imp.getEftStatementReference());
					}
				}
				//	Create a new Bank Statement for every statement date
				else if ((statement.getStatementDate() != null) && (imp.getStatementDate() != null))
				{
					if (!statement.getStatementDate().equals(imp.getStatementDate()))
					{
						statement = null;
						log.info("New Statement, Statement Date=" + imp.getStatementDate());
					}
				}
				
				//	New Statement
				if (statement == null)
				{
					statement = new MBankStatement(account);
					statement.setEndingBalance(Env.ZERO);
					
					//	Copy statement data
					if (imp.getName() != null)
					{
						statement.setName(imp.getName());
					}
					if (imp.getStatementDate() != null)
					{
						statement.setStatementDate(imp.getStatementDate());
					}
					statement.setDescription(imp.getDescription());
					statement.setEftStatementReference(imp.getEftStatementReference());
					statement.setEftStatementDate(imp.getEftStatementDate());
					if (statement.save())
					{
						noInsert++;
					}
					lineNo = 10;
				}
				
				//	New StatementLine
				MBankStatementLine line = new MBankStatementLine(statement, lineNo);
				
				//	Copy statement line data
				//line.setC_BPartner_ID(imp.getC_BPartner_ID());
				//line.setC_Invoice_ID(imp.getC_Invoice_ID());
				line.setReferenceNo(imp.getReferenceNo());
				line.setDescription(imp.getLineDescription());
				line.setStatementLineDate(imp.getStatementLineDate());
				line.setDateAcct(imp.getStatementLineDate());
				line.setValutaDate(imp.getValutaDate());
				line.setIsReversal(imp.isReversal());
				line.setC_Currency_ID(imp.getC_Currency_ID());
				line.setTrxAmt(imp.getTrxAmt());
				line.setStmtAmt(imp.getStmtAmt());
				if (imp.getC_Charge_ID() != 0)
				{
					line.setC_Charge_ID(imp.getC_Charge_ID());
				}
				line.setInterestAmt(imp.getInterestAmt());
				line.setChargeAmt(imp.getChargeAmt());
				line.setMemo(imp.getMemo());
				if (imp.getC_Payment_ID() != 0)
				{
					line.setC_Payment_ID(imp.getC_Payment_ID());
				}
				
				//	Copy statement line reference data
				line.setEftTrxID(imp.getEftTrxID());
				line.setEftTrxType(imp.getEftTrxType());
				line.setEftCheckNo(imp.getEftCheckNo());
				line.setEftReference(imp.getEftReference());
				line.setEftMemo(imp.getEftMemo());
				line.setEftPayee(imp.getEftPayee());
				line.setEftPayeeAccount(imp.getEftPayeeAccount());
				line.setEftStatementLineDate(imp.getEftStatementLineDate());
				line.setEftValutaDate(imp.getEftValutaDate());
				line.setEftCurrency(imp.getEftCurrency());
				line.setEftAmt(imp.getEftAmt());
				
				//	Save statement line
				if (line.save())
				{
					imp.setC_BankStatement_ID(statement.getC_BankStatement_ID());
					imp.setC_BankStatementLine_ID(line.getC_BankStatementLine_ID());
					imp.setI_IsImported(true);
					imp.setProcessed(true);
					imp.save();
					noInsertLine++;
					lineNo += 10;	
				}
				line = null;
				
			}
			
			//	Close database connection
			rs.close();
			pstmt.close();
			rs = null;
			pstmt = null;

		}
		catch(Exception e)
		{
			log.log(Level.SEVERE, sql.toString(), e);
		}
		
		//	Set Error to indicator to not imported
		sql = new StringBuffer ("UPDATE I_BankStatement "
			+ "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_BankStatement_ID@: @Inserted@");
		addLog (0, null, new BigDecimal (noInsertLine), "@C_BankStatementLine_ID@: @Inserted@");
		return "";

	}	//	doIt

}	//	ImportBankStatement