/** * Get Check for Payment * @param ctx context * @param C_Payment_ID id * @param trxName transaction * @return pay selection check for payment or null */ public static MPaySelectionCheck GetOfPayment(Ctx ctx, int C_Payment_ID, Trx trxName) { MPaySelectionCheck retValue = null; String sql = "SELECT * FROM C_PaySelectionCheck WHERE C_Payment_ID=" + C_Payment_ID; int count = 0; DataTable dt = null; IDataReader idr = null; try { idr = DataBase.DB.ExecuteReader(sql, null, trxName); dt = new DataTable(); dt.Load(idr); idr.Close(); foreach (DataRow dr in dt.Rows) { MPaySelectionCheck psc = new MPaySelectionCheck(ctx, dr, trxName); if (retValue == null) { retValue = psc; } else if (!retValue.IsProcessed() && psc.IsProcessed()) { retValue = psc; } count++; } } catch (Exception e) { if (idr != null) { idr.Close(); } _log.Log(Level.SEVERE, sql, e); } finally { if (idr != null) { idr.Close(); } dt = null; } if (count > 1) { _log.Warning("More then one for C_Payment_ID=" + C_Payment_ID); } return(retValue); }
/** * Get Checks of Payment Selection * * @param C_PaySelection_ID Payment Selection * @param PaymentRule Payment Rule * @param startDocumentNo start document no * @param trxName transaction * @return array of checks */ static public MPaySelectionCheck[] Get(int C_PaySelection_ID, String PaymentRule, int startDocumentNo, Trx trxName) { _log.Fine("C_PaySelection_ID=" + C_PaySelection_ID + ", PaymentRule=" + PaymentRule + ", startDocumentNo=" + startDocumentNo); List <MPaySelectionCheck> list = new List <MPaySelectionCheck>(); int docNo = startDocumentNo; String sql = "SELECT * FROM C_PaySelectionCheck " + "WHERE C_PaySelection_ID=" + C_PaySelection_ID + " AND PaymentRule='" + PaymentRule + "'"; DataTable dt; IDataReader idr = null; try { idr = DataBase.DB.ExecuteReader(sql, null, trxName); dt = new DataTable(); dt.Load(idr); idr.Close(); foreach (DataRow dr in dt.Rows) { MPaySelectionCheck check = new MPaySelectionCheck(Env.GetContext(), dr, trxName); // Set new Check Document No - saved in confirmPrint check.SetDocumentNo((docNo++).ToString()); list.Add(check); } } catch (Exception e) { if (idr != null) { idr.Close(); } _log.Log(Level.SEVERE, sql, e); } finally { if (idr != null) { idr.Close(); } dt = null; } // convert to Array MPaySelectionCheck[] retValue = new MPaySelectionCheck[list.Count]; retValue = list.ToArray(); return(retValue); }
/** * Create Check for Payment * @param ctx context * @param C_Payment_ID id * @param trxName transaction * @return pay selection check for payment or null */ public static MPaySelectionCheck CreateForPayment(Ctx ctx, int C_Payment_ID, Trx trxName) { if (C_Payment_ID == 0) { return(null); } MPayment payment = new MPayment(ctx, C_Payment_ID, null); // Map Payment Rule <- Tender Type String PaymentRule = PAYMENTRULE_Check; if (payment.GetTenderType().Equals(X_C_Payment.TENDERTYPE_CreditCard)) { PaymentRule = PAYMENTRULE_CreditCard; } else if (payment.GetTenderType().Equals(X_C_Payment.TENDERTYPE_DirectDebit)) { PaymentRule = PAYMENTRULE_DirectDebit; } else if (payment.GetTenderType().Equals(X_C_Payment.TENDERTYPE_DirectDeposit)) { PaymentRule = PAYMENTRULE_DirectDeposit; } // else if (payment.GetTenderType().Equals(MPayment.TENDERTYPE_Check)) // PaymentRule = MPaySelectionCheck.PAYMENTRULE_Check; // Create new PaySelection MPaySelection ps = new MPaySelection(ctx, 0, trxName); ps.SetC_BankAccount_ID(payment.GetC_BankAccount_ID()); ps.SetName(Msg.Translate(ctx, "C_Payment_ID") + ": " + payment.GetDocumentNo()); ps.SetDescription(payment.GetDescription()); ps.SetPayDate(payment.GetDateTrx()); ps.SetTotalAmt(payment.GetPayAmt()); ps.SetIsApproved(true); ps.Save(); // Create new PaySelection Line MPaySelectionLine psl = null; if (payment.GetC_Invoice_ID() != 0) { psl = new MPaySelectionLine(ps, 10, PaymentRule); psl.SetC_Invoice_ID(payment.GetC_Invoice_ID()); psl.SetIsSOTrx(payment.IsReceipt()); psl.SetOpenAmt(Decimal.Add(payment.GetPayAmt(), payment.GetDiscountAmt())); psl.SetPayAmt(payment.GetPayAmt()); psl.SetDiscountAmt(payment.GetDiscountAmt()); psl.SetDifferenceAmt(Env.ZERO); psl.Save(); } // Create new PaySelection Check MPaySelectionCheck psc = new MPaySelectionCheck(ps, PaymentRule); psc.SetC_BPartner_ID(payment.GetC_BPartner_ID()); psc.SetC_Payment_ID(payment.GetC_Payment_ID()); psc.SetIsReceipt(payment.IsReceipt()); psc.SetPayAmt(payment.GetPayAmt()); psc.SetDiscountAmt(payment.GetDiscountAmt()); psc.SetQty(1); psc.SetDocumentNo(payment.GetDocumentNo()); psc.SetProcessed(true); psc.Save(); // Update optional Line if (psl != null) { psl.SetC_PaySelectionCheck_ID(psc.GetC_PaySelectionCheck_ID()); psl.SetProcessed(true); psl.Save(); } // Indicate Done ps.SetProcessed(true); ps.Save(); return(psc); }
/** * Confirm Print. * Create Payments the first time * @param checks checks * @param batch batch * @return last Document number or 0 if nothing printed */ public static int ConfirmPrint(MPaySelectionCheck[] checks, MPaymentBatch batch) { int lastDocumentNo = 0; for (int i = 0; i < checks.Length; i++) { MPaySelectionCheck check = checks[i]; MPayment payment = new MPayment(check.GetCtx(), check.GetC_Payment_ID(), null); // Existing Payment if (check.GetC_Payment_ID() != 0) { // Update check number if (check.GetPaymentRule().Equals(PAYMENTRULE_Check)) { payment.SetCheckNo(check.GetDocumentNo()); if (!payment.Save()) { _log.Log(Level.SEVERE, "Payment not saved: " + payment); } } } else // New Payment { int _CountVA009 = Util.GetValueOfInt(DB.ExecuteScalar("SELECT COUNT(AD_MODULEINFO_ID) FROM AD_MODULEINFO WHERE PREFIX='VA009_' AND IsActive = 'Y'")); int _CountColumnIPS = Util.GetValueOfInt(DB.ExecuteScalar("SELECT COUNT(AD_Column_ID) FROM AD_Column WHERE Columnname='C_InvoicePaySchedule_ID' AND AD_Table_ID=(SELECT AD_Table_ID FROM AD_Table WHERE tablename = 'C_PaySelectionLine' and export_id='VIS_427')")); if (_CountVA009 > 0) { #region IF VA009 (VA Payment management is installed) payment = new MPayment(check.GetCtx(), 0, null); payment.SetAD_Org_ID(check.GetAD_Org_ID()); // if (check.GetPaymentRule().Equals(PAYMENTRULE_Check)) { payment.SetBankCheck(check.GetParent().GetC_BankAccount_ID(), false, check.GetDocumentNo()); } else if (check.GetPaymentRule().Equals(PAYMENTRULE_CreditCard)) { payment.SetTenderType(X_C_Payment.TENDERTYPE_CreditCard); } else if (check.GetPaymentRule().Equals(PAYMENTRULE_DirectDeposit) || check.GetPaymentRule().Equals(PAYMENTRULE_DirectDebit)) { payment.SetBankACH(check); } else { _log.Log(Level.SEVERE, "Unsupported Payment Rule=" + check.GetPaymentRule()); continue; } payment.SetTrxType(X_C_Payment.TRXTYPE_CreditPayment); payment.SetAmount(check.GetParent().GetC_Currency_ID(), check.GetPayAmt()); payment.SetDiscountAmt(check.GetDiscountAmt()); payment.SetDateTrx(check.GetParent().GetPayDate()); payment.SetC_BPartner_ID(check.GetC_BPartner_ID()); // Link to Batch if (batch != null) { if (batch.GetC_PaymentBatch_ID() == 0) { batch.Save(); // new } payment.SetC_PaymentBatch_ID(batch.GetC_PaymentBatch_ID()); } // Link to Invoice MPaySelectionLine[] psls = check.GetPaySelectionLines(false); _log.Fine("confirmPrint - " + check + " (#SelectionLines=" + psls.Length + ")"); if (check.GetQty() == 1 && psls != null && psls.Length == 1) { MPaySelectionLine psl = psls[0]; _log.Fine("Map to Invoice " + psl); // payment.SetC_Invoice_ID(psl.GetC_Invoice_ID()); if (_CountColumnIPS > 0) { payment.SetC_InvoicePaySchedule_ID(psl.GetC_InvoicePaySchedule_ID()); } payment.SetDiscountAmt(psl.GetDiscountAmt()); if (psl.GetDifferenceAmt() > 0) { payment.SetWriteOffAmt(psl.GetDifferenceAmt()); } else { payment.SetOverUnderAmt(psl.GetDifferenceAmt()); } } else { payment.SetDiscountAmt(Env.ZERO); } payment.SetWriteOffAmt(Env.ZERO); if (psls.Length == 1) { MPaySelectionLine psl = psls[0]; if (_CountColumnIPS > 0) { payment.SetC_InvoicePaySchedule_ID(psl.GetC_InvoicePaySchedule_ID()); } payment.SetC_Invoice_ID(psl.GetC_Invoice_ID()); payment.SetDiscountAmt(psl.GetDiscountAmt()); if (psl.GetDifferenceAmt() > 0) { payment.SetWriteOffAmt(psl.GetDifferenceAmt()); } else { payment.SetOverUnderAmt(psl.GetDifferenceAmt()); } } if (!payment.Save()) { _log.Log(Level.SEVERE, "Payment not saved: " + payment); } // int C_Payment_ID = payment.Get_ID(); if (C_Payment_ID < 1) { _log.Log(Level.SEVERE, "Payment not created=" + check); } else { check.SetC_Payment_ID(C_Payment_ID); check.Save(); // Payment process needs it // Should start WF payment.ProcessIt(DocActionVariables.ACTION_COMPLETE); if (!payment.Save()) { _log.Log(Level.SEVERE, "Payment not saved: " + payment); } } #endregion } else { payment = new MPayment(check.GetCtx(), 0, null); payment.SetAD_Org_ID(check.GetAD_Org_ID()); // if (check.GetPaymentRule().Equals(PAYMENTRULE_Check)) { payment.SetBankCheck(check.GetParent().GetC_BankAccount_ID(), false, check.GetDocumentNo()); } else if (check.GetPaymentRule().Equals(PAYMENTRULE_CreditCard)) { payment.SetTenderType(X_C_Payment.TENDERTYPE_CreditCard); } else if (check.GetPaymentRule().Equals(PAYMENTRULE_DirectDeposit) || check.GetPaymentRule().Equals(PAYMENTRULE_DirectDebit)) { payment.SetBankACH(check); } else { _log.Log(Level.SEVERE, "Unsupported Payment Rule=" + check.GetPaymentRule()); continue; } payment.SetTrxType(X_C_Payment.TRXTYPE_CreditPayment); payment.SetAmount(check.GetParent().GetC_Currency_ID(), check.GetPayAmt()); payment.SetDiscountAmt(check.GetDiscountAmt()); payment.SetDateTrx(check.GetParent().GetPayDate()); payment.SetC_BPartner_ID(check.GetC_BPartner_ID()); // Link to Batch if (batch != null) { if (batch.GetC_PaymentBatch_ID() == 0) { batch.Save(); // new } payment.SetC_PaymentBatch_ID(batch.GetC_PaymentBatch_ID()); } // Link to Invoice MPaySelectionLine[] psls = check.GetPaySelectionLines(false); _log.Fine("confirmPrint - " + check + " (#SelectionLines=" + psls.Length + ")"); if (check.GetQty() == 1 && psls != null && psls.Length == 1) { MPaySelectionLine psl = psls[0]; _log.Fine("Map to Invoice " + psl); // payment.SetC_Invoice_ID(psl.GetC_Invoice_ID()); payment.SetDiscountAmt(psl.GetDiscountAmt()); payment.SetWriteOffAmt(psl.GetDifferenceAmt()); Decimal overUnder = Decimal.Subtract(Decimal.Subtract(Decimal.Subtract(psl.GetOpenAmt(), psl.GetPayAmt()), psl.GetDiscountAmt()), psl.GetDifferenceAmt()); //Decimal overUnder = psl.GetOpenAmt().subtract(psl.GetPayAmt()) // .subtract(psl.GetDiscountAmt()).subtract(psl.GetDifferenceAmt()); payment.SetOverUnderAmt(overUnder); } else { payment.SetDiscountAmt(Env.ZERO); } payment.SetWriteOffAmt(Env.ZERO); if (!payment.Save()) { _log.Log(Level.SEVERE, "Payment not saved: " + payment); } // int C_Payment_ID = payment.Get_ID(); if (C_Payment_ID < 1) { _log.Log(Level.SEVERE, "Payment not created=" + check); } else { check.SetC_Payment_ID(C_Payment_ID); check.Save(); // Payment process needs it // Should start WF payment.ProcessIt(DocActionVariables.ACTION_COMPLETE); if (!payment.Save()) { _log.Log(Level.SEVERE, "Payment not saved: " + payment); } } } }// new Payment // Get Check Document No try { int no = int.Parse(check.GetDocumentNo()); if (lastDocumentNo < no) { lastDocumentNo = no; } } catch (FormatException ex) { _log.Log(Level.SEVERE, "DocumentNo=" + check.GetDocumentNo(), ex); } check.SetIsPrinted(true); check.SetProcessed(true); if (!check.Save()) { _log.Log(Level.SEVERE, "Check not saved: " + check); } } // all checks _log.Fine("Last Document No = " + lastDocumentNo); return(lastDocumentNo); }
/** * Export to File * @param checks array of checks * @param file file to export checks * @return number of lines */ public static int ExportToFile(MPaySelectionCheck[] checks, File file) { if (checks == null || checks.Length == 0) { return(0); } // Must be a file if (file.isDirectory()) { _log.Log(Level.WARNING, "File is directory - " + file.getAbsolutePath()); return(0); } // delete if exists try { if (file.exists()) { file.delete(); } } catch (Exception e) { _log.Log(Level.WARNING, "Could not delete - " + file.getAbsolutePath(), e); } char x = '"'; // ease int noLines = 0; StringBuilder line = null; try { FileWriter fw = new FileWriter(file); // write header line = new StringBuilder(); line.Append(x).Append("Value").Append(x).Append(",") .Append(x).Append("Name").Append(x).Append(",") .Append(x).Append("Contact").Append(x).Append(",") .Append(x).Append("Addr1").Append(x).Append(",") .Append(x).Append("Addr2").Append(x).Append(",") .Append(x).Append("City").Append(x).Append(",") .Append(x).Append("State").Append(x).Append(",") .Append(x).Append("ZIP").Append(x).Append(",") .Append(x).Append("Country").Append(x).Append(",") .Append(x).Append("ReferenceNo").Append(x).Append(",") .Append(x).Append("BPRoutingNo").Append(x).Append(",") .Append(x).Append("BPAccountNo").Append(x).Append(",") .Append(x).Append("BPAName").Append(x).Append(",") .Append(x).Append("BPACity").Append(x).Append(",") .Append(x).Append("BPBBAN").Append(x).Append(",") .Append(x).Append("BPIBAN").Append(x).Append(",") .Append(x).Append("BAName").Append(x).Append(",") .Append(x).Append("BARoutingNo").Append(x).Append(",") .Append(x).Append("BASwiftCode").Append(x).Append(",") .Append(x).Append("DocumentNo").Append(x).Append(",") .Append(x).Append("PayDate").Append(x).Append(",") .Append(x).Append("Currency").Append(x).Append(",") .Append(x).Append("PayAmount").Append(x).Append(",") .Append(x).Append("Comment").Append(x) .Append(Env.NL); fw.write(line.ToString()); noLines++; // write lines for (int i = 0; i < checks.Length; i++) { MPaySelectionCheck mpp = checks[i]; if (mpp == null) { continue; } // BPartner Info String[] bp = GetBPartnerInfo(mpp.GetC_BPartner_ID()); // TarGet BankAccount Info String[] bpba = GetBPBankAccountInfo(mpp.GetC_BP_BankAccount_ID()); // Comment - list of invoice document no StringBuilder comment = new StringBuilder(); MPaySelectionLine[] psls = mpp.GetPaySelectionLines(false); for (int l = 0; l < psls.Length; l++) { if (l > 0) { comment.Append(", "); } comment.Append(psls[l].GetInvoice().GetDocumentNo()); } line = new StringBuilder(); line.Append(x).Append(bp[BP_VALUE]).Append(x).Append(",") // Value .Append(x).Append(bp[BP_NAME]).Append(x).Append(",") // Name .Append(x).Append(bp[BP_CONTACT]).Append(x).Append(",") // Contact .Append(x).Append(bp[BP_ADDR1]).Append(x).Append(",") // Addr1 .Append(x).Append(bp[BP_ADDR2]).Append(x).Append(",") // Addr2 .Append(x).Append(bp[BP_CITY]).Append(x).Append(",") // City .Append(x).Append(bp[BP_REGION]).Append(x).Append(",") // State .Append(x).Append(bp[BP_POSTAL]).Append(x).Append(",") // ZIP .Append(x).Append(bp[BP_COUNTRY]).Append(x).Append(",") // Country .Append(x).Append(bp[BP_REFNO]).Append(x).Append(",") // ReferenceNo .Append(x).Append(bpba[BPBA_RoutingNo]).Append(x).Append(",") // Routing No (as of BPBankAccount .Append(x).Append(bpba[BPBA_AccountNo]).Append(x).Append(",") // AccountNo .Append(x).Append(bpba[BPBA_AName]).Append(x).Append(",") // Account Name .Append(x).Append(bpba[BPBA_ACity]).Append(x).Append(",") // Account City .Append(x).Append(bpba[BPBA_BBAN]).Append(x).Append(",") // BBAN .Append(x).Append(bpba[BPBA_IBAN]).Append(x).Append(",") // IBAN .Append(x).Append(bpba[BA_Name]).Append(x).Append(",") // Bank Name .Append(x).Append(bpba[BA_RoutingNo]).Append(x).Append(",") // Bank RoutingNo .Append(x).Append(bpba[BA_SwitftCode]).Append(x).Append(",") // SwiftCode // Payment Info .Append(x).Append(mpp.GetDocumentNo()).Append(x).Append(",") // DocumentNo .Append(mpp.GetParent().GetPayDate()).Append(",") // PayDate .Append(x).Append(MCurrency.GetISO_Code(Env.GetContext(), mpp.GetParent().GetC_Currency_ID())).Append(x).Append(",") // Currency .Append(mpp.GetPayAmt()).Append(",") // PayAmount .Append(x).Append(comment.ToString()).Append(x) // Comment .Append(Env.NL); fw.write(line.ToString()); noLines++; } // write line fw.flush(); fw.close(); } catch (Exception e) { _log.Log(Level.SEVERE, "", e); } return(noLines); }
/** * Confirm Print. * Create Payments the first time * @param checks checks * @param batch batch * @return last Document number or 0 if nothing printed */ public static int ConfirmPrint(MPaySelectionCheck[] checks, MPaymentBatch batch) { int lastDocumentNo = 0; for (int i = 0; i < checks.Length; i++) { MPaySelectionCheck check = checks[i]; MPayment payment = new MPayment(check.GetCtx(), check.GetC_Payment_ID(), null); // Existing Payment if (check.GetC_Payment_ID() != 0) { // Update check number if (check.GetPaymentRule().Equals(PAYMENTRULE_Check)) { payment.SetCheckNo(check.GetDocumentNo()); if (!payment.Save()) { _log.Log(Level.SEVERE, "Payment not saved: " + payment); } } } else // New Payment { payment = new MPayment(check.GetCtx(), 0, null); payment.SetAD_Org_ID(check.GetAD_Org_ID()); // if (check.GetPaymentRule().Equals(PAYMENTRULE_Check)) { payment.SetBankCheck(check.GetParent().GetC_BankAccount_ID(), false, check.GetDocumentNo()); } else if (check.GetPaymentRule().Equals(PAYMENTRULE_CreditCard)) { payment.SetTenderType(X_C_Payment.TENDERTYPE_CreditCard); } else if (check.GetPaymentRule().Equals(PAYMENTRULE_DirectDeposit) || check.GetPaymentRule().Equals(PAYMENTRULE_DirectDebit)) { payment.SetBankACH(check); } else { _log.Log(Level.SEVERE, "Unsupported Payment Rule=" + check.GetPaymentRule()); continue; } payment.SetTrxType(X_C_Payment.TRXTYPE_CreditPayment); payment.SetAmount(check.GetParent().GetC_Currency_ID(), check.GetPayAmt()); payment.SetDiscountAmt(check.GetDiscountAmt()); payment.SetDateTrx(check.GetParent().GetPayDate()); payment.SetC_BPartner_ID(check.GetC_BPartner_ID()); // Link to Batch if (batch != null) { if (batch.GetC_PaymentBatch_ID() == 0) { batch.Save(); // new } payment.SetC_PaymentBatch_ID(batch.GetC_PaymentBatch_ID()); } // Link to Invoice MPaySelectionLine[] psls = check.GetPaySelectionLines(false); _log.Fine("confirmPrint - " + check + " (#SelectionLines=" + psls.Length + ")"); if (check.GetQty() == 1 && psls != null && psls.Length == 1) { MPaySelectionLine psl = psls[0]; _log.Fine("Map to Invoice " + psl); // payment.SetC_Invoice_ID(psl.GetC_Invoice_ID()); payment.SetDiscountAmt(psl.GetDiscountAmt()); payment.SetWriteOffAmt(psl.GetDifferenceAmt()); Decimal overUnder = Decimal.Subtract(Decimal.Subtract(Decimal.Subtract(psl.GetOpenAmt(), psl.GetPayAmt()), psl.GetDiscountAmt()), psl.GetDifferenceAmt()); //Decimal overUnder = psl.GetOpenAmt().subtract(psl.GetPayAmt()) // .subtract(psl.GetDiscountAmt()).subtract(psl.GetDifferenceAmt()); payment.SetOverUnderAmt(overUnder); } else { payment.SetDiscountAmt(Env.ZERO); } payment.SetWriteOffAmt(Env.ZERO); if (!payment.Save()) { _log.Log(Level.SEVERE, "Payment not saved: " + payment); } // int C_Payment_ID = payment.Get_ID(); if (C_Payment_ID < 1) { _log.Log(Level.SEVERE, "Payment not created=" + check); } else { check.SetC_Payment_ID(C_Payment_ID); check.Save(); // Payment process needs it // Should start WF payment.ProcessIt(DocActionVariables.ACTION_COMPLETE); if (!payment.Save()) { _log.Log(Level.SEVERE, "Payment not saved: " + payment); } } } // new Payment // Get Check Document No try { int no = int.Parse(check.GetDocumentNo()); if (lastDocumentNo < no) { lastDocumentNo = no; } } catch (FormatException ex) { _log.Log(Level.SEVERE, "DocumentNo=" + check.GetDocumentNo(), ex); } check.SetIsPrinted(true); check.SetProcessed(true); if (!check.Save()) { _log.Log(Level.SEVERE, "Check not saved: " + check); } } // all checks _log.Fine("Last Document No = " + lastDocumentNo); return(lastDocumentNo); }