/** * Get Bank Account * @return bank account */ public MBankAccount GetBankAccount() { if (_bankAccount == null && GetC_BankAccount_ID() != 0) { _bankAccount = MBankAccount.Get(GetCtx(), GetC_BankAccount_ID()); } return(_bankAccount); }
/// <summary> /// Get CurrentBalance /// </summary> /// <param name="ctx"></param> /// <param name="fields"></param> /// <returns></returns> public Decimal GetCurrentBalance(Ctx ctx, string fields) { string[] paramValue = fields.Split(','); //Assign parameter value int C_BankAccount_ID = Util.GetValueOfInt(paramValue[0].ToString()); //End Assign parameter value MBankAccount ba = MBankAccount.Get(ctx, C_BankAccount_ID); return(ba.GetCurrentBalance()); }
/// <summary> /// Get AD_Org_ID from Bank Account /// </summary> /// <returns>AD_Org_ID or 0</returns> private int GetBank_Org_ID() { if (_C_BankAccount_ID == 0) { return(0); } // MBankAccount ba = MBankAccount.Get(GetCtx(), _C_BankAccount_ID); return(ba.GetAD_Org_ID()); }
/// <summary> /// Load Specific Document Details /// </summary> /// <returns>error message or null</returns> public override String LoadDocumentDetails() { MBankStatement bs = (MBankStatement)GetPO(); SetDateDoc(bs.GetStatementDate()); SetDateAcct(bs.GetStatementDate()); // Overwritten on Line Level _C_BankAccount_ID = bs.GetC_BankAccount_ID(); // Amounts SetAmount(AMTTYPE_Gross, bs.GetStatementDifference()); // Set Bank Account Info (Currency) MBankAccount ba = MBankAccount.Get(GetCtx(), _C_BankAccount_ID); SetC_Currency_ID(ba.GetC_Currency_ID()); // Contained Objects _lines = LoadLines(bs); log.Fine("Lines=" + _lines.Length); return(null); }
} // prepare /// <summary> /// doit /// </summary> /// <returns>Info</returns> protected override String DoIt() { log.Info("C_BankAccount_ID" + _C_BankAccount_ID); MBankAccount ba = MBankAccount.Get(GetCtx(), _C_BankAccount_ID); if (_C_BankAccount_ID == 0 || ba.Get_ID() != _C_BankAccount_ID) { throw new Exception("@NotFound@ @C_BankAccount_ID@ - " + _C_BankAccount_ID); } if (_AD_Org_ID != ba.GetAD_Org_ID() && ba.GetAD_Org_ID() != 0) { _AD_Org_ID = ba.GetAD_Org_ID(); } log.Info("AD_Org_ID=" + _AD_Org_ID); StringBuilder sql = null; int no = 0; String clientCheck = " AND AD_Client_ID=" + ba.GetAD_Client_ID(); // **** Prepare **** // Delete Old Imported if (_deleteOldImported) { sql = new StringBuilder("DELETE FROM I_Payment " + "WHERE I_IsImported='Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Delete Old Impored =" + no); } // Set Client, Org, IsActive, Created/Updated sql = new StringBuilder("UPDATE I_Payment " + "SET AD_Client_ID = COALESCE (AD_Client_ID,").Append(ba.GetAD_Client_ID()).Append(")," + " AD_Org_ID = COALESCE (AD_Org_ID,").Append(_AD_Org_ID).Append("),"); sql.Append(" IsActive = COALESCE (IsActive, 'Y')," + " Created = COALESCE (Created, SysDate)," + " CreatedBy = COALESCE (CreatedBy, 0)," + " Updated = COALESCE (Updated, SysDate)," + " UpdatedBy = COALESCE (UpdatedBy, 0)," + " I_ErrorMsg = NULL," + " I_IsImported = 'N' " + "WHERE I_IsImported<>'Y' OR I_IsImported IS NULL OR AD_Client_ID IS NULL OR AD_Org_ID IS NULL OR AD_Client_ID=0 OR AD_Org_ID=0"); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Info("Reset=" + no); String ts = DataBase.DB.IsPostgreSQL() ? "COALESCE(I_ErrorMsg,'')" : "I_ErrorMsg"; //java bug, it could not be used directly sql = new StringBuilder("UPDATE I_Payment o " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid Org, '" + "WHERE (AD_Org_ID IS NULL OR AD_Org_ID=0" + " OR EXISTS (SELECT * FROM AD_Org oo WHERE o.AD_Org_ID=oo.AD_Org_ID AND (oo.IsSummary='Y' OR oo.IsActive='N')))" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Warning("Invalid Org=" + no); } // Set Bank Account sql = new StringBuilder("UPDATE I_Payment i " + "SET C_BankAccount_ID=" + "( " + " SELECT C_BankAccount_ID " + " FROM C_BankAccount a, C_Bank b " + " WHERE b.IsOwnBank='Y' " + " AND a.AD_Client_ID=i.AD_Client_ID " + " AND a.C_Bank_ID=b.C_Bank_ID " + " AND a.AccountNo=i.BankAccountNo " + " AND b.RoutingNo=i.RoutingNo " + " OR b.SwiftCode=i.RoutingNo " + ") " + "WHERE i.C_BankAccount_ID IS NULL " + "AND i.I_IsImported<>'Y' " + "OR i.I_IsImported IS NULL").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Info("Bank Account (With Routing No)=" + no); } // sql = new StringBuilder("UPDATE I_Payment i " + "SET C_BankAccount_ID=" + "( " + " SELECT C_BankAccount_ID " + " FROM C_BankAccount a, C_Bank b " + " WHERE b.IsOwnBank='Y' " + " AND a.C_Bank_ID=b.C_Bank_ID " + " AND a.AccountNo=i.BankAccountNo " + " AND a.AD_Client_ID=i.AD_Client_ID " + ") " + "WHERE i.C_BankAccount_ID IS NULL " + "AND i.I_isImported<>'Y' " + "OR i.I_isImported IS NULL").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Info("Bank Account (Without Routing No)=" + no); } // sql = new StringBuilder("UPDATE I_Payment i " + "SET C_BankAccount_ID=(SELECT C_BankAccount_ID FROM C_BankAccount a WHERE a.C_BankAccount_ID=").Append(_C_BankAccount_ID); sql.Append(" and a.AD_Client_ID=i.AD_Client_ID) " + "WHERE i.C_BankAccount_ID IS NULL " + "AND i.BankAccountNo IS NULL " + "AND i.I_isImported<>'Y' " + "OR i.I_isImported IS NULL").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Info("Bank Account=" + no); } // sql = new StringBuilder("UPDATE I_Payment " + "SET I_isImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid Bank Account, ' " + "WHERE C_BankAccount_ID IS NULL " + "AND I_isImported<>'Y' " + "OR I_isImported IS NULL").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Warning("Invalid Bank Account=" + no); } // Set Currency sql = new StringBuilder("UPDATE I_Payment i " + "SET C_Currency_ID=(SELECT C_Currency_ID FROM C_Currency c" + " WHERE i.ISO_Code=c.ISO_Code AND c.AD_Client_ID IN (0,i.AD_Client_ID)) " + "WHERE C_Currency_ID IS NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Info("Set Currency=" + no); } // sql = new StringBuilder("UPDATE I_Payment i " //jz + "SET i.C_Currency_ID=(SELECT C_Currency_ID FROM C_BankAccount WHERE C_BankAccount_ID=i.C_BankAccount_ID) " + "SET C_Currency_ID=(SELECT C_Currency_ID FROM C_BankAccount WHERE C_BankAccount_ID=i.C_BankAccount_ID) " + "WHERE i.C_Currency_ID IS NULL " + "AND i.ISO_Code IS NULL").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Info("Set Currency=" + no); } // sql = new StringBuilder("UPDATE I_Payment " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=No Currency,' " + "WHERE C_Currency_ID IS NULL " + "AND I_IsImported<>'E' " + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Warning("No Currency=" + no); } // Set Amount sql = new StringBuilder("UPDATE I_Payment " + "SET ChargeAmt=0 " + "WHERE ChargeAmt IS NULL " + "AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Info("Charge Amount=" + no); } // sql = new StringBuilder("UPDATE I_Payment " + "SET TaxAmt=0 " + "WHERE TaxAmt IS NULL " + "AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Info("Tax Amount=" + no); } // sql = new StringBuilder("UPDATE I_Payment " + "SET WriteOffAmt=0 " + "WHERE WriteOffAmt IS NULL " + "AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Info("WriteOff Amount=" + no); } // sql = new StringBuilder("UPDATE I_Payment " + "SET DiscountAmt=0 " + "WHERE DiscountAmt IS NULL " + "AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Info("Discount Amount=" + no); } // // Set Date sql = new StringBuilder("UPDATE I_Payment " + "SET DateTrx=Created " + "WHERE DateTrx IS NULL " + "AND I_isImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Info("Trx Date=" + no); } sql = new StringBuilder("UPDATE I_Payment " + "SET DateAcct=DateTrx " + "WHERE DateAcct IS NULL " + "AND I_isImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Info("Acct Date=" + no); } // Invoice sql = new StringBuilder("UPDATE I_Payment i " + "SET C_Invoice_ID=(SELECT MAX(C_Invoice_ID) FROM C_Invoice ii" + " WHERE i.InvoiceDocumentNo=ii.DocumentNo AND i.AD_Client_ID=ii.AD_Client_ID) " + "WHERE C_Invoice_ID IS NULL AND InvoiceDocumentNo IS NOT NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Fine("Set Invoice from DocumentNo=" + no); } // BPartner sql = new StringBuilder("UPDATE I_Payment i " + "SET C_BPartner_ID=(SELECT MAX(C_BPartner_ID) FROM C_BPartner bp" + " WHERE i.BPartnerValue=bp.Value AND i.AD_Client_ID=bp.AD_Client_ID) " + "WHERE C_BPartner_ID IS NULL AND BPartnerValue IS NOT NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Fine("Set BP from Value=" + no); } sql = new StringBuilder("UPDATE I_Payment i " + "SET C_BPartner_ID=(SELECT MAX(C_BPartner_ID) FROM C_Invoice ii" + " WHERE i.C_Invoice_ID=ii.C_Invoice_ID AND i.AD_Client_ID=ii.AD_Client_ID) " + "WHERE C_BPartner_ID IS NULL AND C_Invoice_ID IS NOT NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Fine("Set BP from Invoice=" + no); } sql = new StringBuilder("UPDATE I_Payment " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=No BPartner,' " + "WHERE C_BPartner_ID IS NULL " + "AND I_IsImported<>'E' " + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Warning("No BPartner=" + no); } // Check Payment<->Invoice combination sql = new StringBuilder("UPDATE I_Payment " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'Err=Invalid Payment<->Invoice, ' " + "WHERE I_Payment_ID IN " + "(SELECT I_Payment_ID " + "FROM I_Payment i" + " INNER JOIN C_Payment p ON (i.C_Payment_ID=p.C_Payment_ID) " + "WHERE i.C_Invoice_ID IS NOT NULL " + " AND p.C_Invoice_ID IS NOT NULL " + " AND p.C_Invoice_ID<>i.C_Invoice_ID) ") .Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Info("Payment<->Invoice Mismatch=" + no); } // Check Payment<->BPartner combination sql = new StringBuilder("UPDATE I_Payment " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'Err=Invalid Payment<->BPartner, ' " + "WHERE I_Payment_ID IN " + "(SELECT I_Payment_ID " + "FROM I_Payment i" + " INNER JOIN C_Payment p ON (i.C_Payment_ID=p.C_Payment_ID) " + "WHERE i.C_BPartner_ID IS NOT NULL " + " AND p.C_BPartner_ID IS NOT NULL " + " AND p.C_BPartner_ID<>i.C_BPartner_ID) ") .Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Info("Payment<->BPartner Mismatch=" + no); } // Check Invoice<->BPartner combination sql = new StringBuilder("UPDATE I_Payment " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'Err=Invalid Invoice<->BPartner, ' " + "WHERE I_Payment_ID IN " + "(SELECT I_Payment_ID " + "FROM I_Payment i" + " INNER JOIN C_Invoice v ON (i.C_Invoice_ID=v.C_Invoice_ID) " + "WHERE i.C_BPartner_ID IS NOT NULL " + " AND v.C_BPartner_ID IS NOT NULL " + " AND v.C_BPartner_ID<>i.C_BPartner_ID) ") .Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Info("Invoice<->BPartner Mismatch=" + no); } // Check Invoice.BPartner<->Payment.BPartner combination sql = new StringBuilder("UPDATE I_Payment " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'Err=Invalid Invoice.BPartner<->Payment.BPartner, ' " + "WHERE I_Payment_ID IN " + "(SELECT I_Payment_ID " + "FROM I_Payment i" + " INNER JOIN C_Invoice v ON (i.C_Invoice_ID=v.C_Invoice_ID)" + " INNER JOIN C_Payment p ON (i.C_Payment_ID=p.C_Payment_ID) " + "WHERE p.C_Invoice_ID<>v.C_Invoice_ID" + " AND v.C_BPartner_ID<>p.C_BPartner_ID) ") .Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Info("Invoice.BPartner<->Payment.BPartner Mismatch=" + no); } // TrxType sql = new StringBuilder("UPDATE I_Payment " + "SET TrxType='S' " // MPayment.TRXTYPE_Sales + "WHERE TrxType IS NULL " + "AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Info("TrxType Default=" + no); } // TenderType sql = new StringBuilder("UPDATE I_Payment " + "SET TenderType='K' " // MPayment.TENDERTYPE_Check + "WHERE TenderType IS NULL " + "AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Info("TenderType Default=" + no); } // Document Type sql = new StringBuilder("UPDATE I_Payment i " + "SET C_DocType_ID=(SELECT C_DocType_ID FROM C_DocType d WHERE d.Name=i.DocTypeName" + " AND d.DocBaseType IN ('ARR','APP') AND i.AD_Client_ID=d.AD_Client_ID) " + "WHERE C_DocType_ID IS NULL AND DocTypeName IS NOT NULL AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Fine("Set DocType=" + no); } sql = new StringBuilder("UPDATE I_Payment " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid DocTypeName, ' " + "WHERE C_DocType_ID IS NULL AND DocTypeName IS NOT NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Warning("Invalid DocTypeName=" + no); } sql = new StringBuilder("UPDATE I_Payment " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=No DocType, ' " + "WHERE C_DocType_ID IS NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Warning("No DocType=" + no); } Commit(); //Import Bank Statement sql = new StringBuilder("SELECT * FROM I_Payment" + " WHERE I_IsImported='N'" + " ORDER BY C_BankAccount_ID, CheckNo, DateTrx, R_AuthCode"); MBankAccount account = null; IDataReader idr = null; // int lineNo = 10; int noInsert = 0; //int noInsertLine = 0; try { //pstmt = DataBase.prepareStatement(sql.ToString(), get_TrxName()); //ResultSet rs = pstmt.executeQuery(); idr = DataBase.DB.ExecuteReader(sql.ToString(), null, Get_TrxName()); while (idr.Read()) { X_I_Payment imp = new X_I_Payment(_ctx, idr, Get_TrxName()); // Get the bank account if (account == null || account.GetC_BankAccount_ID() != imp.GetC_BankAccount_ID()) { account = MBankAccount.Get(_ctx, imp.GetC_BankAccount_ID()); log.Info("New Account=" + account.GetAccountNo()); } // New Payment MPayment payment = new MPayment(_ctx, 0, Get_TrxName()); payment.SetAD_Org_ID(imp.GetAD_Org_ID()); payment.SetDocumentNo(imp.GetDocumentNo()); payment.SetPONum(imp.GetPONum()); payment.SetTrxType(imp.GetTrxType()); payment.SetTenderType(imp.GetTenderType()); payment.SetC_BankAccount_ID(imp.GetC_BankAccount_ID()); payment.SetRoutingNo(imp.GetRoutingNo()); payment.SetAccountNo(imp.GetAccountNo()); payment.SetCheckNo(imp.GetCheckNo()); payment.SetMicr(imp.GetMicr()); if (imp.GetCreditCardType() != null) { payment.SetCreditCardType(imp.GetCreditCardType()); } payment.SetCreditCardNumber(imp.GetCreditCardNumber()); if (imp.GetCreditCardExpMM() != 0) { payment.SetCreditCardExpMM(imp.GetCreditCardExpMM()); } if (imp.GetCreditCardExpYY() != 0) { payment.SetCreditCardExpYY(imp.GetCreditCardExpYY()); } payment.SetCreditCardVV(imp.GetCreditCardVV()); payment.SetSwipe(imp.GetSwipe()); payment.SetDateAcct(imp.GetDateTrx()); payment.SetDateTrx(imp.GetDateTrx()); // payment.setDescription(imp.getDescription()); // payment.SetC_BPartner_ID(imp.GetC_BPartner_ID()); payment.SetC_Invoice_ID(imp.GetC_Invoice_ID()); payment.SetC_DocType_ID(imp.GetC_DocType_ID()); payment.SetC_Currency_ID(imp.GetC_Currency_ID()); // payment.setC_ConversionType_ID(imp.getC_ConversionType_ID()); payment.SetC_Charge_ID(imp.GetC_Charge_ID()); payment.SetChargeAmt(imp.GetChargeAmt()); payment.SetTaxAmt(imp.GetTaxAmt()); payment.SetPayAmt(imp.GetPayAmt()); payment.SetWriteOffAmt(imp.GetWriteOffAmt()); payment.SetDiscountAmt(imp.GetDiscountAmt()); payment.SetWriteOffAmt(imp.GetWriteOffAmt()); // Copy statement line reference data payment.SetA_City(imp.GetA_City()); payment.SetA_Country(imp.GetA_Country()); payment.SetA_EMail(imp.GetA_EMail()); payment.SetA_Ident_DL(imp.GetA_Ident_DL()); payment.SetA_Ident_SSN(imp.GetA_Ident_SSN()); payment.SetA_Name(imp.GetA_Name()); payment.SetA_State(imp.GetA_State()); payment.SetA_Street(imp.GetA_Street()); payment.SetA_Zip(imp.GetA_Zip()); payment.SetR_AuthCode(imp.GetR_AuthCode()); payment.SetR_Info(imp.GetR_Info()); payment.SetR_PnRef(imp.GetR_PnRef()); payment.SetR_RespMsg(imp.GetR_RespMsg()); payment.SetR_Result(imp.GetR_Result()); payment.SetOrig_TrxID(imp.GetOrig_TrxID()); payment.SetVoiceAuthCode(imp.GetVoiceAuthCode()); // Save patment if (payment.Save()) { imp.SetC_Payment_ID(payment.GetC_Payment_ID()); imp.SetI_IsImported(X_I_Payment.I_ISIMPORTED_Yes); imp.SetProcessed(true); imp.Save(); noInsert++; } } // Close database connection idr.Close(); } catch (Exception e) { if (idr != null) { idr.Close(); } log.Log(Level.SEVERE, sql.ToString(), e); } // Set Error to indicator to not imported sql = new StringBuilder("UPDATE I_Payment " + "SET I_IsImported='N', Updated=SysDate " + "WHERE I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); AddLog(0, null, Utility.Util.GetValueOfDecimal(no), "@Errors@"); // AddLog(0, null, Utility.Util.GetValueOfDecimal(noInsert), "@C_Payment_ID@: @Inserted@"); return(""); } // doIt
} // prepare /// <summary> /// perform process /// </summary> /// <returns>info</returns> protected override String DoIt() { log.Info("AD_Org_ID=" + _AD_Org_ID + ", C_BankAccount_ID" + _C_BankAccount_ID); StringBuilder sql = null; int no = 0; String clientCheck = " AND AD_Client_ID=" + _AD_Client_ID; // **** Prepare **** // Delete Old Imported if (_deleteOldImported) { sql = new StringBuilder("DELETE FROM I_BankStatement " + "WHERE I_IsImported='Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Delete Old Impored =" + no); } // Set Client, Org, IsActive, Created/Updated sql = new StringBuilder("UPDATE I_BankStatement " + "SET AD_Client_ID = COALESCE (AD_Client_ID,").Append(_AD_Client_ID).Append(")," + " AD_Org_ID = COALESCE (AD_Org_ID,").Append(_AD_Org_ID).Append("),"); sql.Append(" IsActive = COALESCE (IsActive, 'Y')," + " Created = COALESCE (Created, SysDate)," + " CreatedBy = COALESCE (CreatedBy, 0)," + " Updated = COALESCE (Updated, SysDate)," + " UpdatedBy = COALESCE (UpdatedBy, 0)," + " I_ErrorMsg = NULL," + " I_IsImported = 'N' " + "WHERE I_IsImported<>'Y' OR I_IsImported IS NULL OR AD_Client_ID IS NULL OR AD_Org_ID IS NULL OR AD_Client_ID=0 OR AD_Org_ID=0"); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Info("Reset=" + no); String ts = DataBase.DB.IsPostgreSQL() ? "COALESCE(I_ErrorMsg,'')" : "I_ErrorMsg"; //java bug, it could not be used directly sql = new StringBuilder("UPDATE I_BankStatement o " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid Org, '" + "WHERE (AD_Org_ID IS NULL OR AD_Org_ID=0" + " OR EXISTS (SELECT * FROM AD_Org oo WHERE o.AD_Org_ID=oo.AD_Org_ID AND (oo.IsSummary='Y' OR oo.IsActive='N')))" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Warning("Invalid Org=" + no); } // Set Bank Account sql = new StringBuilder("UPDATE I_BankStatement i " + "SET C_BankAccount_ID=" + "( " + " SELECT C_BankAccount_ID " + " FROM C_BankAccount a, C_Bank b " + " WHERE b.IsOwnBank='Y' " + " AND a.AD_Client_ID=i.AD_Client_ID " + " AND a.C_Bank_ID=b.C_Bank_ID " + " AND a.AccountNo=i.BankAccountNo " + " AND b.RoutingNo=i.RoutingNo " + " OR b.SwiftCode=i.RoutingNo " + ") " + "WHERE i.C_BankAccount_ID IS NULL " + "AND i.I_IsImported<>'Y' " + "OR i.I_IsImported IS NULL").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Info("Bank Account (With Routing No)=" + no); } // sql = new StringBuilder("UPDATE I_BankStatement i " + "SET C_BankAccount_ID=" + "( " + " SELECT C_BankAccount_ID " + " FROM C_BankAccount a, C_Bank b " + " WHERE b.IsOwnBank='Y' " + " AND a.C_Bank_ID=b.C_Bank_ID " + " AND a.AccountNo=i.BankAccountNo " + " AND a.AD_Client_ID=i.AD_Client_ID " + ") " + "WHERE i.C_BankAccount_ID IS NULL " + "AND i.I_isImported<>'Y' " + "OR i.I_isImported IS NULL").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Info("Bank Account (Without Routing No)=" + no); } // sql = new StringBuilder("UPDATE I_BankStatement i " + "SET C_BankAccount_ID=(SELECT C_BankAccount_ID FROM C_BankAccount a WHERE a.C_BankAccount_ID=").Append(_C_BankAccount_ID); sql.Append(" and a.AD_Client_ID=i.AD_Client_ID) " + "WHERE i.C_BankAccount_ID IS NULL " + "AND i.BankAccountNo IS NULL " + "AND i.I_isImported<>'Y' " + "OR i.I_isImported IS NULL").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Info("Bank Account=" + no); } // sql = new StringBuilder("UPDATE I_BankStatement " + "SET I_isImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid Bank Account, ' " + "WHERE C_BankAccount_ID IS NULL " + "AND I_isImported<>'Y' " + "OR I_isImported IS NULL").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Warning("Invalid Bank Account=" + no); } // Set Currency sql = new StringBuilder("UPDATE I_BankStatement i " + "SET C_Currency_ID=(SELECT C_Currency_ID FROM C_Currency c" + " WHERE i.ISO_Code=c.ISO_Code AND c.AD_Client_ID IN (0,i.AD_Client_ID)) " + "WHERE C_Currency_ID IS NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Info("Set Currency=" + no); } // sql = new StringBuilder("UPDATE I_BankStatement i " //jz + "SET i.C_Currency_ID=(SELECT C_Currency_ID FROM C_BankAccount WHERE C_BankAccount_ID=i.C_BankAccount_ID) " + "SET C_Currency_ID=(SELECT C_Currency_ID FROM C_BankAccount WHERE C_BankAccount_ID=i.C_BankAccount_ID) " + "WHERE i.C_Currency_ID IS NULL " + "AND i.ISO_Code IS NULL").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Info("Set Currency=" + no); } // sql = new StringBuilder("UPDATE I_BankStatement " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid Currency,' " + "WHERE C_Currency_ID IS NULL " + "AND I_IsImported<>'E' " + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Warning("Invalid Currency=" + no); } // Set Amount sql = new StringBuilder("UPDATE I_BankStatement " + "SET ChargeAmt=0 " + "WHERE ChargeAmt IS NULL " + "AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Info("Charge Amount=" + no); } // sql = new StringBuilder("UPDATE I_BankStatement " + "SET InterestAmt=0 " + "WHERE InterestAmt IS NULL " + "AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Info("Interest Amount=" + no); } // sql = new StringBuilder("UPDATE I_BankStatement " + "SET TrxAmt=StmtAmt - InterestAmt - ChargeAmt " + "WHERE TrxAmt IS NULL " + "AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Info("Transaction Amount=" + no); } // sql = new StringBuilder("UPDATE I_BankStatement " + "SET I_isImported='E', I_ErrorMsg=" + ts + "||'Err=Invalid Amount, ' " + "WHERE TrxAmt + ChargeAmt + InterestAmt <> StmtAmt " + "AND I_isImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Info("Invaid Amount=" + no); } // Set Valuta Date sql = new StringBuilder("UPDATE I_BankStatement " + "SET ValutaDate=StatementLineDate " + "WHERE ValutaDate IS NULL " + "AND I_isImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Info("Valuta Date=" + no); } // Check Payment<->Invoice combination sql = new StringBuilder("UPDATE I_BankStatement " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'Err=Invalid Payment<->Invoice, ' " + "WHERE I_BankStatement_ID IN " + "(SELECT I_BankStatement_ID " + "FROM I_BankStatement i" + " INNER JOIN C_Payment p ON (i.C_Payment_ID=p.C_Payment_ID) " + "WHERE i.C_Invoice_ID IS NOT NULL " + " AND p.C_Invoice_ID IS NOT NULL " + " AND p.C_Invoice_ID<>i.C_Invoice_ID) ") .Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Info("Payment<->Invoice Mismatch=" + no); } // Check Payment<->BPartner combination sql = new StringBuilder("UPDATE I_BankStatement " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'Err=Invalid Payment<->BPartner, ' " + "WHERE I_BankStatement_ID IN " + "(SELECT I_BankStatement_ID " + "FROM I_BankStatement i" + " INNER JOIN C_Payment p ON (i.C_Payment_ID=p.C_Payment_ID) " + "WHERE i.C_BPartner_ID IS NOT NULL " + " AND p.C_BPartner_ID IS NOT NULL " + " AND p.C_BPartner_ID<>i.C_BPartner_ID) ") .Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Info("Payment<->BPartner Mismatch=" + no); } // Check Invoice<->BPartner combination sql = new StringBuilder("UPDATE I_BankStatement " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'Err=Invalid Invoice<->BPartner, ' " + "WHERE I_BankStatement_ID IN " + "(SELECT I_BankStatement_ID " + "FROM I_BankStatement i" + " INNER JOIN C_Invoice v ON (i.C_Invoice_ID=v.C_Invoice_ID) " + "WHERE i.C_BPartner_ID IS NOT NULL " + " AND v.C_BPartner_ID IS NOT NULL " + " AND v.C_BPartner_ID<>i.C_BPartner_ID) ") .Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Info("Invoice<->BPartner Mismatch=" + no); } // Check Invoice.BPartner<->Payment.BPartner combination sql = new StringBuilder("UPDATE I_BankStatement " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'Err=Invalid Invoice.BPartner<->Payment.BPartner, ' " + "WHERE I_BankStatement_ID IN " + "(SELECT I_BankStatement_ID " + "FROM I_BankStatement i" + " INNER JOIN C_Invoice v ON (i.C_Invoice_ID=v.C_Invoice_ID)" + " INNER JOIN C_Payment p ON (i.C_Payment_ID=p.C_Payment_ID) " + "WHERE p.C_Invoice_ID<>v.C_Invoice_ID" + " AND v.C_BPartner_ID<>p.C_BPartner_ID) ") .Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Info("Invoice.BPartner<->Payment.BPartner Mismatch=" + no); } // Detect Duplicates sql = new StringBuilder("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) "); StringBuilder updateSql = new StringBuilder("UPDATE I_Bankstatement " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'Err=Duplicate['||@param1||']' " + "WHERE I_BankStatement_ID=@param2").Append(clientCheck); //PreparedStatement pupdt = DataBase.prepareStatement(updateSql.ToString(), get_TrxName()); //PreparedStatement pstmtDuplicates = null; IDataReader idr = null; SqlParameter[] param = new SqlParameter[2]; no = 0; try { //pstmtDuplicates = DataBase.prepareStatement(sql.ToString(), get_TrxName()); //ResultSet rs = pstmtDuplicates.executeQuery(); idr = DataBase.DB.ExecuteReader(sql.ToString(), null, Get_TrxName()); while (idr.Read()) { String Info = "Line_ID=" + Utility.Util.GetValueOfInt(idr[1]) // s.getInt(2) // l.C_BankStatementLine_ID + ",EDTTrxID=" + Utility.Util.GetValueOfString(idr[2]); // rs.getString(3); // i.EftTrxID //pupdt.setString(1, Info); param[0] = new SqlParameter("@param1", Info); //pupdt.setInt(2, rs.getInt(1)); // i.I_BankStatement_ID param[1] = new SqlParameter("@param2", Utility.Util.GetValueOfInt(idr[0])); //pupdt.executeUpdate(); DataBase.DB.ExecuteQuery(updateSql.ToString(), param, Get_TrxName()); no++; } idr.Close(); } catch (Exception e) { if (idr != null) { idr.Close(); } log.Log(Level.SEVERE, "DetectDuplicates " + e.Message); } if (no != 0) { log.Info("Duplicates=" + no); } Commit(); //Import Bank Statement sql = new StringBuilder("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 = DataBase.prepareStatement(sql.ToString(), get_TrxName()); idr = DataBase.DB.ExecuteReader(sql.ToString(), null, Get_TrxName()); while (idr.Read()) { X_I_BankStatement imp = new X_I_BankStatement(_ctx, idr, Get_TrxName()); // Get the bank account for the first statement if (account == null) { account = MBankAccount.Get(_ctx, imp.GetC_BankAccount_ID()); 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 = MBankAccount.Get(_ctx, imp.GetC_BankAccount_ID()); 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(X_I_BankStatement.I_ISIMPORTED_Yes); imp.SetProcessed(true); imp.Save(); noInsertLine++; lineNo += 10; } line = null; } // Close database connection idr.Close(); } catch (Exception e) { if (idr != null) { idr.Close(); } log.Log(Level.SEVERE, sql.ToString(), e); } // Set Error to indicator to not imported sql = new StringBuilder("UPDATE I_BankStatement " + "SET I_IsImported='N', Updated=SysDate " + "WHERE I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); AddLog(0, null, Utility.Util.GetValueOfDecimal(no), "@Errors@"); // AddLog(0, null, Utility.Util.GetValueOfDecimal(noInsert), "@C_BankStatement_ID@: @Inserted@"); AddLog(0, null, Utility.Util.GetValueOfDecimal(noInsertLine), "@C_BankStatementLine_ID@: @Inserted@"); return(""); } // doIt