/// <summary> /// Generate PaySelection /// </summary> public string GeneratePaySelect(Ctx ctx, List <GridRecords> selectedRecords, Decimal?paymentAmt, String paymentRule, int C_BankAccount_ID, DateTime?payDate) { Trx trx = null; Trx p_trx = null; List <int> Invoice_ID = new List <int>(); List <Decimal?> openAmt = new List <Decimal?>(); List <Decimal?> payAmt = new List <Decimal?>(); int rowsSelected = 0; Decimal?totalAmt = 0; // BindingSource rowSource = vdgvPayment.ItemsSource as BindingSource; for (int i = 0; i < selectedRecords.Count; i++) { if ((Convert.ToBoolean(selectedRecords[i].SELECT))) { Decimal?amt = Util.GetValueOfDecimal(selectedRecords[i].PAYMENTAMOUNT); Invoice_ID.Add(Util.GetValueOfInt(selectedRecords[i].C_INVOICE_ID)); openAmt.Add(Util.GetValueOfDecimal(selectedRecords[i].AMOUNTDUE)); payAmt.Add(Util.GetValueOfDecimal(selectedRecords[i].PAYMENTAMOUNT)); rowsSelected++; } } if (rowsSelected == 0) { return(""); } //String paymentRule = Util.GetValueOfString(cmbPaymentRule.SelectedValue); //int C_BankAccount_ID = Util.GetValueOfInt(cmbBankAccount.SelectedValue); //// Create Header //DateTime? payDate = Util.GetValueOfDateTime(vdtpPayDate.SelectedDate); MPaySelection m_ps = new MPaySelection(ctx, 0, null); m_ps.SetName(Msg.GetMsg(ctx, "VPaySelect") + " - " + paymentRule + " - " + payDate.Value.Date); m_ps.SetPayDate(payDate); m_ps.SetC_BankAccount_ID(C_BankAccount_ID); m_ps.SetIsApproved(true); if (!m_ps.Save()) { //log.SaveError("SaveError", Msg.Translate(Envs.GetCtx(), "C_PaySelection_ID")); m_ps = null; return(""); } _C_PaySelection_ID = m_ps.GetC_PaySelection_ID(); string name = m_ps.GetName(); //string sqlTableID = "select ad_table_id from ad_table where tablename = 'C_PaySelection'"; //int AD_Table_ID = Util.GetValueOfInt(DB.ExecuteScalar(sqlTableID, null, null)); // log.Config(m_ps.ToString()); bool isSOTrx = false; if (X_C_Order.PAYMENTRULE_DirectDebit.Equals(paymentRule)) { isSOTrx = true; } int line = 0; Decimal?pAmt = Decimal.Zero; Decimal?oldpAmt = Decimal.Zero; for (int j = 0; j < Invoice_ID.Count; j++) { line = line + 10; if (Decimal.Add(pAmt.Value, Util.GetValueOfDecimal(payAmt[j])) > paymentAmt) { oldpAmt = Decimal.Subtract(paymentAmt.Value, pAmt.Value); } pAmt = Decimal.Add(pAmt.Value, Util.GetValueOfDecimal(payAmt[j])); // pAmt = Util.GetValueOfDecimal(payAmt[j]); MPaySelectionLine psl = new MPaySelectionLine(m_ps, line, paymentRule); //psl.SetInvoice(Util.GetValueOfInt(Invoice_ID[j]), isSOTrx, Util.GetValueOfDecimal(openAmt[j]), Util.GetValueOfDecimal(payAmt[j]), Decimal.Subtract(Util.GetValueOfDecimal(openAmt[j]), Util.GetValueOfDecimal(payAmt[j]))); if (paymentAmt >= pAmt) { psl.SetInvoice(Util.GetValueOfInt(Invoice_ID[j]), isSOTrx, Util.GetValueOfDecimal(openAmt[j]), Util.GetValueOfDecimal(payAmt[j]), Decimal.Zero); if (!psl.Save()) { // log.SaveError("PaymentSelectionLineNotSaved", "PaymentSelectionLineNotSaved"); return(""); } // log.Fine("C_Invoice_ID=" + Util.GetValueOfInt(Invoice_ID[j]) + ", PayAmt=" + Util.GetValueOfDecimal(payAmt[j])); } else { psl.SetInvoice(Util.GetValueOfInt(Invoice_ID[j]), isSOTrx, Util.GetValueOfDecimal(openAmt[j]), oldpAmt.Value, Decimal.Zero); if (!psl.Save()) { // log.SaveError("PaymentSelectionLineNotSaved", "PaymentSelectionLineNotSaved"); return(""); } // log.Fine("C_Invoice_ID=" + Util.GetValueOfInt(Invoice_ID[j]) + ", PayAmt=" + Util.GetValueOfDecimal(payAmt[j])); } } //if (false.Equals(((Message)sc).DialogResult)) //{ // Dispose(); // return; //} MPaySelection psel = new MPaySelection(ctx, _C_PaySelection_ID, null); if (psel.Get_ID() == 0) { throw new ArgumentException("Not found C_PaySelection_ID=" + _C_PaySelection_ID); } if (psel.IsProcessed()) { throw new ArgumentException("@Processed@"); } // MPaySelectionLine[] lines = psel.GetLines(false); List <MPaySelectionCheck> _list = new List <MPaySelectionCheck>(); for (int i = 0; i < lines.Length; i++) { MPaySelectionLine payLine = lines[i]; if (!payLine.IsActive() || payLine.IsProcessed()) { continue; } CreateCheck(ctx, payLine, _list); } // psel.SetProcessed(true); psel.Save(); //string sql = "select ad_form_id from ad_form where classname = 'VAdvantage.Apps.AForms.VPayPrint'"; //int AD_Form_ID = Util.GetValueOfInt(DB.ExecuteScalar(sql, null, null)); return("@C_PaySelectionCheck_ID@ - #" + _list.Count); //SetBusy(false); //Dispose(); //FormFrame ff = new FormFrame(); //ff.OpenForm(AD_Form_ID); } // generatePaySelect
/// <summary> /// Perrform Process. /// </summary> /// <returns>Message </returns> protected override String DoIt() { int count = 0; log.Info("C_PaySelection_ID=" + _C_PaySelection_ID + ", OnlyDiscount=" + _OnlyDiscount + ", OnlyDue=" + _OnlyDue + ", IncludeInDispute=" + _IncludeInDispute + ", MatchRequirement=" + _MatchRequirementI + ", PaymentRule=" + _PaymentRule + ", C_BP_Group_ID=" + _C_BP_Group_ID + ", C_BPartner_ID=" + _C_BPartner_ID); MPaySelection psel = new MPaySelection(GetCtx(), _C_PaySelection_ID, Get_TrxName()); int C_CurrencyTo_ID = psel.GetC_Currency_ID(); if (psel.Get_ID() == 0) { throw new ArgumentException("Not found C_PaySelection_ID=" + _C_PaySelection_ID); } if (psel.IsProcessed()) { throw new ArgumentException("@Processed@"); } // psel.getPayDate(); String sql = "SELECT C_Invoice_ID," // Open + " currencyConvert(invoiceOpen(i.C_Invoice_ID, 0)" + ",i.C_Currency_ID, " + C_CurrencyTo_ID + "," + DataBase.DB.TO_DATE(psel.GetPayDate(), true) + ", i.C_ConversionType_ID,i.AD_Client_ID,i.AD_Org_ID)," // ##1/2 Currency_To,PayDate // Discount + " currencyConvert(paymentTermDiscount(i.GrandTotal,i.C_Currency_ID,i.C_PaymentTerm_ID,i.DateInvoiced, " + DataBase.DB.TO_DATE(psel.GetPayDate(), true) + ")" // ##3 PayDate + ",i.C_Currency_ID," + C_CurrencyTo_ID + "," + DataBase.DB.TO_DATE(psel.GetPayDate(), true) + ",i.C_ConversionType_ID,i.AD_Client_ID,i.AD_Org_ID)," // ##4/5 Currency_To,PayDate + " PaymentRule, IsSOTrx " // 4..6 + "FROM C_Invoice i " + "WHERE"; if (_PaymentRule != null && _PaymentRule.Equals(X_C_Invoice.PAYMENTRULE_DirectDebit)) { sql += " IsSOTrx='Y'"; } else { sql += " IsSOTrx='N'"; } sql += " AND IsPaid='N' AND DocStatus IN ('CO','CL')" // ##6 + " AND AD_Client_ID=" + psel.GetAD_Client_ID() // ##7 // Existing Payments - Will reselect Invoice if prepared but not paid + " AND NOT EXISTS (SELECT * FROM C_PaySelectionLine psl " + "WHERE i.C_Invoice_ID=psl.C_Invoice_ID AND psl.IsActive='Y'" + " AND psl.C_PaySelectionCheck_ID IS NOT NULL)"; count = 7; // Disputed if (!_IncludeInDispute) { sql += " AND i.IsInDispute='N'"; } // PaymentRule (optional) if (_PaymentRule != null && _PaymentRule != " ") { sql += " AND PaymentRule=" + _PaymentRule; // ## count += 1; } // OnlyDiscount if (_OnlyDiscount) { if (_OnlyDue) { sql += " AND ("; } else { sql += " AND "; } sql += "paymentTermDiscount(invoiceOpen(C_Invoice_ID, 0), C_Currency_ID, C_PaymentTerm_ID, DateInvoiced, " + DataBase.DB.TO_DATE(psel.GetPayDate(), true) + ") > 0"; // ## count += 1; } // OnlyDue if (_OnlyDue) { if (_OnlyDiscount) { sql += " OR "; } else { sql += " AND "; } sql += "paymentTermDueDays(C_PaymentTerm_ID, DateInvoiced, " + DataBase.DB.TO_DATE(psel.GetPayDate(), true) + ") >= 0"; // ## count += 1; if (_OnlyDiscount) { sql += ")"; } } // Business Partner if (_C_BPartner_ID != 0 && _C_BPartner_ID != -1) { sql += " AND C_BPartner_ID=" + _C_BPartner_ID; // ## count += 1; } // Business Partner Group else if (_C_BP_Group_ID != 0 && _C_BP_Group_ID != -1) { sql += " AND EXISTS (SELECT * FROM C_BPartner bp " + "WHERE bp.C_BPartner_ID=i.C_BPartner_ID AND bp.C_BP_Group_ID=" + _C_BP_Group_ID + ")"; // ## count += 1; } // PO Matching Requiremnent if (_MatchRequirementI.Equals(X_C_Invoice.MATCHREQUIREMENTI_PurchaseOrder) || _MatchRequirementI.Equals(X_C_Invoice.MATCHREQUIREMENTI_PurchaseOrderAndReceipt)) { sql += " AND i._MatchRequirementI NOT IN ('N','R')" + " AND EXISTS (SELECT * FROM C_InvoiceLine il " + "WHERE i.C_Invoice_ID=il.C_Invoice_ID" + " AND QtyInvoiced IN (SELECT SUM(Qty) FROM M_MatchPO m " + "WHERE il.C_InvoiceLine_ID=m.C_InvoiceLine_ID))"; } // Receipt Matching Requiremnent if (_MatchRequirementI.Equals(X_C_Invoice.MATCHREQUIREMENTI_Receipt) || _MatchRequirementI.Equals(X_C_Invoice.MATCHREQUIREMENTI_PurchaseOrderAndReceipt)) { sql += " AND i._MatchRequirementI NOT IN ('N','P')" + " AND EXISTS (SELECT * FROM C_InvoiceLine il " + "WHERE i.C_Invoice_ID=il.C_Invoice_ID" + " AND QtyInvoiced IN (SELECT SUM(Qty) FROM M_MatchInv m " + "WHERE il.C_InvoiceLine_ID=m.C_InvoiceLine_ID))"; } // Document No else if (_DocumentNo_From != null && _DocumentNo_To != null) { sql += " AND i.DocumentNo BETWEEN " + DataBase.DB.TO_STRING(_DocumentNo_From) + " AND " + DataBase.DB.TO_STRING(_DocumentNo_To); } else if (_DocumentNo_From != null) { sql += " AND "; if (_DocumentNo_From.IndexOf('%') == -1) { sql += "i.DocumentNo >= " + DataBase.DB.TO_STRING(_DocumentNo_From); } else { sql += "i.DocumentNo LIKE " + DataBase.DB.TO_STRING(_DocumentNo_From); } } // int lines = 0; IDataReader idr = null; try { idr = DataBase.DB.ExecuteReader(sql, null, Get_TrxName()); while (idr.Read()) { int C_Invoice_ID = Utility.Util.GetValueOfInt(idr[0]); // rs.getInt(1); Decimal PayAmt = Utility.Util.GetValueOfDecimal(idr[1]); //rs.getBigDecimal(2); if (C_Invoice_ID == 0 || Env.ZERO.CompareTo(PayAmt) == 0) { continue; } Decimal DiscountAmt = Utility.Util.GetValueOfDecimal(idr[2]); //rs.getBigDecimal(3); String PaymentRule = Utility.Util.GetValueOfString(idr[3]); //rs.getString(4); bool isSOTrx = "Y".Equals(Utility.Util.GetValueOfString(idr[4])); //rs.getString(5)); // lines++; MPaySelectionLine pselLine = new MPaySelectionLine(psel, lines * 10, PaymentRule); pselLine.SetInvoice(C_Invoice_ID, isSOTrx, PayAmt, Decimal.Subtract(PayAmt, DiscountAmt), DiscountAmt); if (!pselLine.Save()) { if (idr != null) { idr.Close(); idr = null; } return(GetRetrievedError(pselLine, "Cannot save MPaySelectionLine")); //throw new Exception("Cannot save MPaySelectionLine"); } } idr.Close(); } catch (Exception e) { if (idr != null) { idr.Close(); } log.Log(Level.SEVERE, sql, e); } return("@C_PaySelectionLine_ID@ - #" + lines); }
/// <summary> /// Perform Process. /// </summary> /// <returns>Message </returns> protected override String DoIt() { //int count = 0; psel = new MPaySelection(GetCtx(), _C_PaySelection_ID, Get_TrxName()); #region Old Query //log.Info("C_PaySelection_ID=" + _C_PaySelection_ID // + ", OnlyDiscount=" + _OnlyDiscount + ", OnlyDue=" + _OnlyDue // + ", IncludeInDispute=" + _IncludeInDispute // + ", MatchRequirement=" + _MatchRequirementI // + ", PaymentRule=" + _PaymentRule // + ", C_BP_Group_ID=" + _C_BP_Group_ID + ", C_BPartner_ID=" + _C_BPartner_ID); //MPaySelection psel = new MPaySelection(GetCtx(), _C_PaySelection_ID, Get_TrxName()); //int C_CurrencyTo_ID = psel.GetC_Currency_ID(); //if (psel.Get_ID() == 0) //{ // throw new ArgumentException("Not found C_PaySelection_ID=" + _C_PaySelection_ID); //} //if (psel.IsProcessed()) //{ // throw new ArgumentException("@Processed@"); //} //// psel.getPayDate(); //String sql = "SELECT C_Invoice_ID," // // Open // + " currencyConvert(invoiceOpen(i.C_Invoice_ID, 0)" // + ",i.C_Currency_ID, " + C_CurrencyTo_ID + "," + DB.TO_DATE(psel.GetPayDate(), true) + ", i.C_ConversionType_ID,i.AD_Client_ID,i.AD_Org_ID)," // ##1/2 Currency_To,PayDate // // Discount // + " currencyConvert(paymentTermDiscount(i.GrandTotal,i.C_Currency_ID,i.C_PaymentTerm_ID,i.DateInvoiced, " + DB.TO_DATE(psel.GetPayDate(), true) + ")" // ##3 PayDate // + ",i.C_Currency_ID," + C_CurrencyTo_ID + "," + DB.TO_DATE(psel.GetPayDate(), true) + ",i.C_ConversionType_ID,i.AD_Client_ID,i.AD_Org_ID)," // ##4/5 Currency_To,PayDate // + " PaymentRule, IsSOTrx " // 4..6 // + "FROM C_Invoice i " // + "WHERE"; ////if (_PaymentRule != null && _PaymentRule.Equals(X_C_Invoice.PAYMENTRULE_DirectDebit)) ////{ //// sql += " IsSOTrx='Y'"; ////} ////else ////{ //// sql += " IsSOTrx='N'"; ////} //sql += " IsPaid='N' AND DocStatus IN ('CO','CL')" // ##6 // + " AND AD_Client_ID=" + psel.GetAD_Client_ID() // ##7 // // Existing Payments - Will reselect Invoice if prepared but not paid // + " AND NOT EXISTS (SELECT * FROM C_PaySelectionLine psl " // + "WHERE i.C_Invoice_ID=psl.C_Invoice_ID AND psl.IsActive='Y'" // + " AND psl.C_PaySelectionCheck_ID IS NOT NULL)"; //count = 7; //// Disputed //if (!_IncludeInDispute) //{ // sql += " AND i.IsInDispute='N'"; //} //// PaymentRule (optional) //if (_PaymentRule != null && _PaymentRule != " ") //{ // sql += " AND PaymentRule='" + _PaymentRule + "'"; // ## // count += 1; //} //// OnlyDiscount //if (_OnlyDiscount) //{ // if (_OnlyDue) // { // sql += " AND ("; // } // else // { // sql += " AND "; // } // sql += "paymentTermDiscount(invoiceOpen(C_Invoice_ID, 0), C_Currency_ID, C_PaymentTerm_ID, DateInvoiced, " + DB.TO_DATE(psel.GetPayDate(), true) + ") > 0"; // ## // count += 1; //} //// OnlyDue //if (_OnlyDue) //{ // if (_OnlyDiscount) // { // sql += " OR "; // } // else // { // sql += " AND "; // } // sql += "paymentTermDueDays(C_PaymentTerm_ID, DateInvoiced, " + DB.TO_DATE(psel.GetPayDate(), true) + ") >= 0"; // ## // count += 1; // if (_OnlyDiscount) // { // sql += ")"; // } //} //// Business Partner //if (_C_BPartner_ID != 0 && _C_BPartner_ID != -1) //{ // sql += " AND C_BPartner_ID=" + _C_BPartner_ID; // ## // count += 1; //} //// Business Partner Group //else if (_C_BP_Group_ID != 0 && _C_BP_Group_ID != -1) //{ // sql += " AND EXISTS (SELECT * FROM C_BPartner bp " // + "WHERE bp.C_BPartner_ID=i.C_BPartner_ID AND bp.C_BP_Group_ID=" + _C_BP_Group_ID + ")"; // ## // count += 1; //} //// PO Matching Requiremnent //if (_MatchRequirementI.Equals(X_C_Invoice.MATCHREQUIREMENTI_PurchaseOrder) // || _MatchRequirementI.Equals(X_C_Invoice.MATCHREQUIREMENTI_PurchaseOrderAndReceipt)) //{ // sql += " AND i._MatchRequirementI NOT IN ('N','R')" // + " AND EXISTS (SELECT * FROM C_InvoiceLine il " // + "WHERE i.C_Invoice_ID=il.C_Invoice_ID" // + " AND QtyInvoiced IN (SELECT SUM(Qty) FROM M_MatchPO m " // + "WHERE il.C_InvoiceLine_ID=m.C_InvoiceLine_ID))"; //} //// Receipt Matching Requiremnent //if (_MatchRequirementI.Equals(X_C_Invoice.MATCHREQUIREMENTI_Receipt) // || _MatchRequirementI.Equals(X_C_Invoice.MATCHREQUIREMENTI_PurchaseOrderAndReceipt)) //{ // sql += " AND i._MatchRequirementI NOT IN ('N','P')" // + " AND EXISTS (SELECT * FROM C_InvoiceLine il " // + "WHERE i.C_Invoice_ID=il.C_Invoice_ID" // + " AND QtyInvoiced IN (SELECT SUM(Qty) FROM M_MatchInv m " // + "WHERE il.C_InvoiceLine_ID=m.C_InvoiceLine_ID))"; //} //// Document No //else if (_DocumentNo_From != null && _DocumentNo_To != null) //{ // sql += " AND i.DocumentNo BETWEEN " // + DB.TO_STRING(_DocumentNo_From) + " AND " // + DB.TO_STRING(_DocumentNo_To); //} //else if (_DocumentNo_From != null) //{ // sql += " AND "; // if (_DocumentNo_From.IndexOf('%') == -1) // { // sql += "i.DocumentNo >= " // + DB.TO_STRING(_DocumentNo_From); // } // else // { // sql += "i.DocumentNo LIKE " // + DB.TO_STRING(_DocumentNo_From); // } //} #endregion String sql = CreateQuery(); // int lines = 0; IDataReader idr = null; try { idr = DB.ExecuteReader(sql, null, Get_TrxName()); while (idr.Read()) { int C_InvoicePaySchedule_ID = Util.GetValueOfInt(idr[0]); int C_Invoice_ID = Util.GetValueOfInt(idr[1]); // rs.getInt(1); Decimal PayAmt = Util.GetValueOfDecimal(idr[2]); //rs.getBigDecimal(2); if (C_Invoice_ID == 0 || Env.ZERO.CompareTo(PayAmt) == 0) { continue; } Decimal DiscountAmt = Util.GetValueOfDecimal(idr[5]); //rs.getBigDecimal(5); Decimal DiscountAmt2 = Util.GetValueOfDecimal(idr[6]); //rs.getBigDecimal(6); // OnlyDiscount if (_OnlyDiscount) { if (DiscountAmt == 0 && DiscountAmt2 == 0) { continue; //Skip this if discount is ZERO in the case of ONLY DISCOUNT } } if (DiscountAmt2 > 0) { DiscountAmt = DiscountAmt2; } String PaymentRule = Util.GetValueOfString(idr[3]); //rs.getString(3); bool isSOTrx = "Y".Equals(Util.GetValueOfString(idr[4])); //rs.getString(4)); // lines++; MPaySelectionLine pselLine = new MPaySelectionLine(psel, lines * 10, PaymentRule); pselLine.SetInvoice(C_Invoice_ID, isSOTrx, PayAmt, Decimal.Subtract(PayAmt, DiscountAmt), DiscountAmt); if (VA009_PaymentMethod_ID == 0) { MInvoicePaySchedule ips = new MInvoicePaySchedule(GetCtx(), C_InvoicePaySchedule_ID, Get_TrxName()); pselLine.Set_Value("VA009_PaymentMethod_ID", ips.GetVA009_PaymentMethod_ID()); _PaymentRule = (String)(DB.ExecuteScalar("SELECT VA009_PaymentBaseType FROM VA009_PaymentMethod WHERE IsActive='Y' AND VA009_PaymentMethod_ID=" + ips.GetVA009_PaymentMethod_ID())); pselLine.SetPaymentRule(_PaymentRule); } else { pselLine.Set_Value("VA009_PaymentMethod_ID", VA009_PaymentMethod_ID); pselLine.SetPaymentRule(_PaymentRule); } pselLine.SetC_InvoicePaySchedule_ID(C_InvoicePaySchedule_ID); // Set Invoice Pay Schedule if (!pselLine.Save()) { if (idr != null) { idr.Close(); idr = null; } //return GetReterivedError(pselLine, "Cannot save MPaySelectionLine"); throw new Exception("Cannot save MPaySelectionLine"); } } idr.Close(); } catch (Exception e) { if (idr != null) { idr.Close(); } log.Log(Level.SEVERE, sql, e); } return("@C_PaySelectionLine_ID@ - #" + lines); }