/// <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);
}