/// <summary>
/// Process
/// </summary>
/// <returns>info</returns>
protected override String DoIt()
{
log.Info("C_DunningRun_ID=" + _C_DunningRun_ID + ",R_MailText_ID=" + _R_MailText_ID
+ ", EmailPDF=" + _EMailPDF + ",IsOnlyIfBPBalance=" + _IsOnlyIfBPBalance);
// Need to have Template
if (_EMailPDF && _R_MailText_ID == 0)
{
throw new Exception("@NotFound@: @R_MailText_ID@");
}
String subject = "";
MMailText mText = null;
if (_EMailPDF)
{
mText = new MMailText(GetCtx(), _R_MailText_ID, Get_TrxName());
if (_EMailPDF && mText.Get_ID() == 0)
{
throw new Exception("@NotFound@: @R_MailText_ID@ - " + _R_MailText_ID);
}
subject = mText.GetMailHeader();
}
//
MDunningRun run = new MDunningRun(GetCtx(), _C_DunningRun_ID, Get_TrxName());
if (run.Get_ID() == 0)
{
throw new Exception("@NotFound@: @C_DunningRun_ID@ - " + _C_DunningRun_ID);
}
// Print Format on Dunning Level
MDunningLevel level = new MDunningLevel(GetCtx(), run.GetC_DunningLevel_ID(), Get_TrxName());
//MPrintFormat format = MPrintFormat.Get(GetCtx(), level.GetDunning_PrintFormat_ID(), false);
MClient client = MClient.Get(GetCtx());
int count = 0;
int errors = 0;
MDunningRunEntry[] entries = run.GetEntries(false);
for (int i = 0; i < entries.Length; i++)
{
MDunningRunEntry entry = entries[i];
if (_IsOnlyIfBPBalance && Env.Signum(entry.GetAmt()) <= 0)
{
continue;
}
// To BPartner
MBPartner bp = new MBPartner(GetCtx(), entry.GetC_BPartner_ID(), Get_TrxName());
if (bp.Get_ID() == 0)
{
AddLog(entry.Get_ID(), null, null, "@NotFound@: @C_BPartner_ID@ " + entry.GetC_BPartner_ID());
errors++;
continue;
}
// To User
MUser to = new MUser(GetCtx(), entry.GetAD_User_ID(), Get_TrxName());
if (_EMailPDF)
{
if (to.Get_ID() == 0)
{
AddLog(entry.Get_ID(), null, null, "@NotFound@: @AD_User_ID@ - " + bp.GetName());
errors++;
continue;
}
else if (to.GetEMail() == null || to.GetEMail().Length == 0)
{
AddLog(entry.Get_ID(), null, null, "@NotFound@: @EMail@ - " + to.GetName());
errors++;
continue;
}
}
// BP Language
//Language language =Language.getLoginLanguage(); // Base Language
Language language = Env.GetLoginLanguage(GetCtx());
String tableName = "C_Dunning_Header_v";
if (client.IsMultiLingualDocument())
{
tableName += "t";
String AD_Language = bp.GetAD_Language();
if (AD_Language != null)
{
//language =language.getLanguage(AD_Language);
}
}
// format.SetLanguage(language);
// format.SetTranslationLanguage(language);
// query
Query query = new Query(tableName);
query.AddRestriction("C_DunningRunEntry_ID", Query.EQUAL,
entry.GetC_DunningRunEntry_ID());
// Engine
//PrintInfo info = new PrintInfo(
// bp.GetName(),
// X_C_DunningRunEntry.Table_ID,
// entry.GetC_DunningRunEntry_ID(),
// entry.GetC_BPartner_ID());
//info.SetDescription(bp.GetName() + ", Amt=" + entry.GetAmt());
//ReportEngine re = new ReportEngine(GetCtx(), format, query, info);
bool printed = false;
if (_EMailPDF)
{
EMail email = client.CreateEMail(to.GetEMail(), to.GetName(), null, null);
if (email == null || !email.IsValid())
{
AddLog(entry.Get_ID(), null, null,
"@RequestActionEMailError@ Invalid EMail: " + to);
errors++;
continue;
}
mText.SetUser(to); // variable context
mText.SetBPartner(bp);
mText.SetPO(entry);
String message = mText.GetMailText(true);
if (mText.IsHtml())
{
email.SetMessageHTML(mText.GetMailHeader(), message);
}
else
{
email.SetSubject(mText.GetMailHeader());
email.SetMessageText(message);
}
//
//File attachment = re.GetPDF(File.createTempFile("Dunning", ".pdf"));
//log.Fine(to + " - " + attachment);
//email.AddAttachment(attachment);
//
String msg = email.Send();
MUserMail um = new MUserMail(mText, entry.GetAD_User_ID(), email);
um.Save();
if (msg.Equals(EMail.SENT_OK))
{
AddLog(entry.Get_ID(), null, null,
bp.GetName() + " @RequestActionEMailOK@");
count++;
printed = true;
}
else
{
AddLog(entry.Get_ID(), null, null,
bp.GetName() + " @RequestActionEMailError@ " + msg);
errors++;
}
}
else
{
//re.print();
count++;
printed = true;
}
if (printed)
{
entry.SetProcessed(true);
entry.Save();
DunningLevelConsequences(level, entry);
}
} // for all dunning letters
if (errors == 0)
{
run.SetProcessed(true);
run.Save();
}
if (_EMailPDF)
{
return("@Sent@=" + count + " - @Errors@=" + errors);
}
return("@Printed@=" + count);
}
/// <summary>
/// Add Invoices to Run tab of dunning run window.
/// </summary>
/// <returns>No of invoices that are created.</returns>
private int AddInvoices()
{
//int size = 3;
int count = 0;
SqlParameter[] param = null;
// Changes for Invoice schedule if valid true
if (!_level.IsShowNotDue())
{
param = new SqlParameter[3];
}
else
{
param = new SqlParameter[1];
}
sql.Append("SELECT I.C_INVOICE_ID, I.C_CURRENCY_ID, I.GRANDTOTAL, INVOICEOPEN(I.C_INVOICE_ID,Ips.C_INVOICEPAYSCHEDULE_ID), "
+ " DAYSBETWEEN(@param1,IPS.DUEDATE),"
+ " i.IsInDispute, i.C_BPartner_ID, "
+ " CASE "
+ " WHEN i.issotrx ='Y' AND i.isreturntrx='N'" // Invoice Customer
+ " THEN ips.DueAmt"
+ " WHEN i.issotrx ='Y' AND i.isreturntrx='Y'" // AR Credit Memo
+ " THEN ips.DueAmt * -1"
+ " WHEN i.issotrx ='N' AND i.isreturntrx='N'" // Invoice Vendor
+ " THEN ips.DueAmt * -1"
+ " ELSE ips.DueAmt" // AP Credit Memo
+ " END AS DueAmt,"
+ " ips.C_INVOICEPAYSCHEDULE_ID"
+ " FROM C_Invoice i "
+ " LEFT JOIN C_INVOICEPAYSCHEDULE ips "
+ " ON ips.c_invoice_id =i.c_invoice_id "
+ " WHERE ips.VA009_IsPaid ='N' "
+ " AND i.AD_Client_ID = " + _run.GetAD_Client_ID()
+ " AND i.AD_Org_ID = " + _run.GetAD_Org_ID()
+ " AND i.DocStatus IN ('CO','CL') "
+ " AND (NOT i.InvoiceCollectionType IN ('" + X_C_Invoice.INVOICECOLLECTIONTYPE_CollectionAgency + "', "
+ "'" + X_C_Invoice.INVOICECOLLECTIONTYPE_LegalProcedure + "', '" + X_C_Invoice.INVOICECOLLECTIONTYPE_Uncollectable + "')"
+ " OR InvoiceCollectionType IS NULL) ");
if (_SalesRep_ID > 0)
{
sql.Append(" AND i.SalesRep_ID= " + _SalesRep_ID);
}
if (!_level.IsShowNotDue())
{
sql.Append(" AND IPS.DUEDATE <=@param2 "
+ " AND DATEINVOICED <=@param3 ");
}
sql.Append(" AND IPS.ISVALID='Y' "
+ " AND i.ispayschedulevalid='Y' "
+ " AND EXISTS "
+ " (SELECT * "
+ " FROM C_DunningLevel dl "
+ " WHERE dl.C_DunningLevel_ID= " + _run.GetC_DunningLevel_ID()
+ " AND dl.C_Dunning_ID IN "
+ " (SELECT COALESCE(bp.C_Dunning_ID, bpg.C_Dunning_ID) "
+ " FROM C_BPartner bp "
+ " INNER JOIN C_BP_Group bpg "
+ " ON (bp.C_BP_Group_ID =bpg.C_BP_Group_ID) "
+ " WHERE i.C_BPartner_ID=bp.C_BPartner_ID "
+ " ) "
+ " )");
if (!_level.IsShowAllDue())
{
param[0] = new SqlParameter("@param1", _run.GetDunningDate());
if (!_level.IsShowNotDue())
{
param[1] = new SqlParameter("@param2", _run.GetDunningDate().Value.AddDays(Util.GetValueOfInt(-1 * _level.GetDaysAfterDue())));
param[2] = new SqlParameter("@param3", _run.GetDunningDate().Value.AddDays(Util.GetValueOfInt(-1 * _level.GetDaysAfterDue())));
}
}
else
{
param[0] = new SqlParameter("@param1", _run.GetDunningDate());
if (!_level.IsShowNotDue())
{
param[1] = new SqlParameter("@param2", _run.GetDunningDate());
param[2] = new SqlParameter("@param3", _run.GetDunningDate());
}
}
// for specific Business Partner
if (_C_BPartner_ID != 0)
{
//size = size + 1;
sql.Append(" AND i.C_BPartner_ID=" + _C_BPartner_ID); // ##6
}
// or a specific group
if (_C_BP_Group_ID != 0)
{
//size = size + 1;
sql.Append(" AND EXISTS (SELECT * FROM C_BPartner bp "
+ "WHERE i.C_BPartner_ID=bp.C_BPartner_ID AND bp.C_BP_Group_ID=" + _C_BP_Group_ID + ")"); // ##;
}
// Only Sales Trx
if (_OnlySOTrx)
{
sql.Append(" AND i.IsSOTrx='Y'");
}
// Only single currency
if (!_IsAllCurrencies)
{
sql.Append(" AND i.C_Currency_ID=" + _C_Currency_ID);
}
// log.info(sql);
String sql2 = null;
// if sequentially we must check for other levels with smaller days for
// which this invoice is not yet included!
if (_level.GetParent().IsCreateLevelsSequentially())
{
// Build a list of all topmost Dunning Levels
MDunningLevel[] previousLevels = _level.GetPreviousLevels();
if (previousLevels != null && previousLevels.Length > 0)
{
for (int i = 0; i < previousLevels.Length; i++)
{
sql.Append(" AND i.C_Invoice_ID IN (SELECT C_Invoice_ID FROM C_DunningRunLine WHERE " +
"C_DunningRunEntry_ID IN (SELECT C_DunningRunEntry_ID FROM C_DunningRunEntry WHERE " +
"C_DunningRun_ID IN (SELECT C_DunningRun_ID FROM C_DunningRun WHERE " +
"C_DunningLevel_ID=" + previousLevels[i].Get_ID() + ")) AND Processed<>'N')");
}
}
}
// ensure that we do only dunn what's not yet dunned, so we lookup the max of last Dunn Date which was processed
sql2 = "SELECT COUNT(*), COALESCE(DAYSBETWEEN(MAX(dr2.DunningDate), MAX(dr.DunningDate)),0)"
+ "FROM C_DunningRun dr2, C_DunningRun dr"
+ " INNER JOIN C_DunningRunEntry dre ON (dr.C_DunningRun_ID=dre.C_DunningRun_ID)"
+ " INNER JOIN C_DunningRunLine drl ON (dre.C_DunningRunEntry_ID=drl.C_DunningRunEntry_ID) "
+ "WHERE drl.Processed='Y' AND dr2.C_DunningRun_ID=@C_DunningRun_ID AND drl.C_Invoice_ID=@C_Invoice_ID AND C_InvoicePaySchedule_ID=@C_InvoicePaySchedule_ID AND dr.C_DunningRun_ID!=@C_DunningRun_ID"; // ##1 ##2
Decimal DaysAfterDue = _run.GetLevel().GetDaysAfterDue();
int DaysBetweenDunning = _run.GetLevel().GetDaysBetweenDunning();
IDataReader idr = null;
IDataReader idr1 = null;
try
{
idr = DataBase.DB.ExecuteReader(sql.ToString(), param, Get_TrxName());
sql.Clear();
//
while (idr.Read())
{
C_Invoice_ID = 0; C_Currency_ID = 0; GrandTotal = 0; Open = 0; DaysDue = 0;
IsInDispute = false; C_BPartner_ID = 0; PaySchedule_ID = 0;
C_Invoice_ID = Utility.Util.GetValueOfInt(idr[0]);
C_Currency_ID = Utility.Util.GetValueOfInt(idr[1]);
GrandTotal = Utility.Util.GetValueOfDecimal(idr[2]);
Open = Utility.Util.GetValueOfDecimal(idr[7]);
DaysDue = Utility.Util.GetValueOfInt(idr[4]);
IsInDispute = "Y".Equals(Utility.Util.GetValueOfString(idr[5]));
C_BPartner_ID = Utility.Util.GetValueOfInt(idr[6]);
PaySchedule_ID = Utility.Util.GetValueOfInt(idr[8]);
//
// Check for Dispute
if (!_IncludeInDispute && IsInDispute)
{
continue;
}
// Check for an open amount
if (Env.ZERO.CompareTo(Open) == 0)
{
continue;
}
//
timesDunned = 0;
daysAfterLast = 0;
//2nd record set
SqlParameter[] param1 = new SqlParameter[3];
param1[0] = new SqlParameter("@C_DunningRun_ID", _run.Get_ID());
param1[1] = new SqlParameter("@C_Invoice_ID", C_Invoice_ID);
param1[2] = new SqlParameter("@C_InvoicePaySchedule_ID", PaySchedule_ID);
idr1 = DataBase.DB.ExecuteReader(sql2, param1, Get_TrxName());
// SubQuery
if (idr1.Read())
{
timesDunned = Utility.Util.GetValueOfInt(idr1[0]);
daysAfterLast = Utility.Util.GetValueOfInt(idr1[1]);
}
idr1.Close();
// SubQuery
// Ensure that Daysbetween Dunning is enforced
// Ensure Not ShowAllDue and Not ShowNotDue is selected
// PROBLEM: If you have ShowAll activated then DaysBetweenDunning is not working, because we don't know whether
// there is something which we really must Dunn.
//if (DaysBetweenDunning != 0 && daysAfterLast != 0 && daysAfterLast < DaysBetweenDunning && !_level.IsShowAllDue() && !_level.IsShowNotDue())
if (DaysBetweenDunning != 0 && daysAfterLast != 0 && daysAfterLast < DaysBetweenDunning)
{
continue;
}
// We will minus the timesDunned if this is the DaysBetweenDunning is not fullfilled.
// Remember in checkup later we must reset them!
// See also checkDunningEntry()
if (daysAfterLast < DaysBetweenDunning)
{
timesDunned = timesDunned * -1;
}
//
if (CreateInvoiceLine(C_Invoice_ID, C_Currency_ID, GrandTotal, Open,
DaysDue, IsInDispute, C_BPartner_ID,
timesDunned, daysAfterLast, PaySchedule_ID))
{
count++;
}
}
idr.Close();
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
}
if (idr1 != null)
{
idr1.Close();
}
log.Log(Level.SEVERE, "addInvoices", e);
}
return(count);
}
/// <summary>
/// Add Invoices to Run
/// </summary>
/// <returns>no of invoices</returns>
private int AddInvoices()
{
int size = 5;
int count = 0;
String sql = "SELECT i.C_Invoice_ID, i.C_Currency_ID,"
+ " i.GrandTotal*i.MultiplierAP,"
+ " invoiceOpen(i.C_Invoice_ID,i.C_InvoicePaySchedule_ID)*MultiplierAP,"
+ " COALESCE(daysBetween(@param1,ips.DueDate),paymentTermDueDays(i.C_PaymentTerm_ID,i.DateInvoiced,@param2))," // ##1/2
+ " i.IsInDispute, i.C_BPartner_ID "
+ "FROM C_Invoice_v i "
+ " LEFT OUTER JOIN C_InvoicePaySchedule ips ON (i.C_InvoicePaySchedule_ID=ips.C_InvoicePaySchedule_ID) "
+ "WHERE i.IsPaid='N' AND i.AD_Client_ID=@param3" // ##3
+ " AND i.DocStatus IN ('CO','CL')"
// Invoice Collection Status Collection Agency, Uncollectable, Legal will not been dunned any longer as per Def. YS + KP 12/02/06
+ " AND (NOT i.InvoiceCollectionType IN ('" + X_C_Invoice.INVOICECOLLECTIONTYPE_CollectionAgency + "', "
+ "'" + X_C_Invoice.INVOICECOLLECTIONTYPE_LegalProcedure + "', '" + X_C_Invoice.INVOICECOLLECTIONTYPE_Uncollectable + "')"
+ " OR InvoiceCollectionType IS NULL)"
// Do not show future docs...
+ " AND DateInvoiced<=@param4" // ##4
// Only BP(Group) with Dunning defined
+ " AND EXISTS (SELECT * FROM C_DunningLevel dl "
+ "WHERE dl.C_DunningLevel_ID=@param5" // // ##5
+ " AND dl.C_Dunning_ID IN "
+ "(SELECT COALESCE(bp.C_Dunning_ID, bpg.C_Dunning_ID) "
+ "FROM C_BPartner bp"
+ " INNER JOIN C_BP_Group bpg ON (bp.C_BP_Group_ID=bpg.C_BP_Group_ID) "
+ "WHERE i.C_BPartner_ID=bp.C_BPartner_ID))";
// for specific Business Partner
if (_C_BPartner_ID != 0)
{
size = size + 1;
sql += " AND i.C_BPartner_ID=@param6"; // ##6
}
// or a specific group
else if (_C_BP_Group_ID != 0)
{
size = size + 1;
sql += " AND EXISTS (SELECT * FROM C_BPartner bp "
+ "WHERE i.C_BPartner_ID=bp.C_BPartner_ID AND bp.C_BP_Group_ID=@param6)"; // ##6
}
// Only Sales Trx
if (_OnlySOTrx)
{
sql += " AND i.IsSOTrx='Y'";
}
// Only single currency
if (!_IsAllCurrencies)
{
sql += " AND i.C_Currency_ID=" + _C_Currency_ID;
}
// log.info(sql);
String sql2 = null;
// if sequentially we must check for other levels with smaller days for
// which this invoice is not yet included!
if (_level.GetParent().IsCreateLevelsSequentially())
{
// Build a list of all topmost Dunning Levels
MDunningLevel[] previousLevels = _level.GetPreviousLevels();
if (previousLevels != null && previousLevels.Length > 0)
{
String sqlAppend = "";
for (int i = 0; i < previousLevels.Length; i++)
{
sqlAppend += " AND i.C_Invoice_ID IN (SELECT C_Invoice_ID FROM C_DunningRunLine WHERE " +
"C_DunningRunEntry_ID IN (SELECT C_DunningRunEntry_ID FROM C_DunningRunEntry WHERE " +
"C_DunningRun_ID IN (SELECT C_DunningRun_ID FROM C_DunningRun WHERE " +
"C_DunningLevel_ID=" + previousLevels[i].Get_ID() + ")) AND Processed<>'N')";
}
sql += sqlAppend;
}
}
// ensure that we do only dunn what's not yet dunned, so we lookup the max of last Dunn Date which was processed
sql2 = "SELECT COUNT(*), COALESCE(DAYSBETWEEN(MAX(dr2.DunningDate), MAX(dr.DunningDate)),0)"
+ "FROM C_DunningRun dr2, C_DunningRun dr"
+ " INNER JOIN C_DunningRunEntry dre ON (dr.C_DunningRun_ID=dre.C_DunningRun_ID)"
+ " INNER JOIN C_DunningRunLine drl ON (dre.C_DunningRunEntry_ID=drl.C_DunningRunEntry_ID) "
+ "WHERE drl.Processed='Y' AND dr2.C_DunningRun_ID=@C_DunningRun_ID AND drl.C_Invoice_ID=@C_Invoice_ID"; // ##1 ##2
Decimal DaysAfterDue = _run.GetLevel().GetDaysAfterDue();
int DaysBetweenDunning = _run.GetLevel().GetDaysBetweenDunning();
IDataReader idr = null;
IDataReader idr1 = null;
try
{
SqlParameter[] param = new SqlParameter[size];
param[0] = new SqlParameter("@param1", _run.GetDunningDate());
param[1] = new SqlParameter("@param2", _run.GetDunningDate());
param[2] = new SqlParameter("@param3", _run.GetAD_Client_ID());
param[3] = new SqlParameter("@param4", _run.GetDunningDate());
param[4] = new SqlParameter("@param5", _run.GetC_DunningLevel_ID());
if (_C_BPartner_ID != 0)
{
//pstmt.setInt(6, _C_BPartner_ID);
param[5] = new SqlParameter("@param6", _C_BPartner_ID);
}
else if (_C_BP_Group_ID != 0)
{
//pstmt.setInt(6, _C_BP_Group_ID);
param[5] = new SqlParameter("@param6", _C_BP_Group_ID);
}
idr = DataBase.DB.ExecuteReader(sql, param, Get_TrxName());
//
while (idr.Read())
{
int C_Invoice_ID = Utility.Util.GetValueOfInt(idr[0]);
int C_Currency_ID = Utility.Util.GetValueOfInt(idr[1]);
Decimal GrandTotal = Utility.Util.GetValueOfDecimal(idr[2]);
Decimal Open = Utility.Util.GetValueOfDecimal(idr[3]);
int DaysDue = Utility.Util.GetValueOfInt(idr[4]);
bool IsInDispute = "Y".Equals(Utility.Util.GetValueOfString(idr[5]));
int C_BPartner_ID = Utility.Util.GetValueOfInt(idr[6]);
//
// Check for Dispute
if (!_IncludeInDispute && IsInDispute)
{
continue;
}
// Check the day again based on rulesets
if (DaysDue < Utility.Util.GetValueOfInt(DaysAfterDue) && !_level.IsShowAllDue())
{
continue;
}
// Check for an open amount
if (Env.ZERO.CompareTo(Open) == 0)
{
continue;
}
//
int timesDunned = 0;
int daysAfterLast = 0;
//2nd record set
SqlParameter[] param1 = new SqlParameter[2];
param1[0] = new SqlParameter("@C_DunningRun_ID", _run.Get_ID());
param1[1] = new SqlParameter("@C_Invoice_ID", C_Invoice_ID);
idr1 = DataBase.DB.ExecuteReader(sql2, param1, Get_TrxName());
// SubQuery
if (idr1.Read())
{
timesDunned = Utility.Util.GetValueOfInt(idr1[0]);
daysAfterLast = Utility.Util.GetValueOfInt(idr1[1]);
}
idr1.Close();
// SubQuery
// Ensure that Daysbetween Dunning is enforced
// Ensure Not ShowAllDue and Not ShowNotDue is selected
// PROBLEM: If you have ShowAll activated then DaysBetweenDunning is not working, because we don't know whether
// there is something which we really must Dunn.
if (DaysBetweenDunning != 0 && daysAfterLast < DaysBetweenDunning && !_level.IsShowAllDue() && !_level.IsShowNotDue())
{
continue;
}
// We don't want to show non due documents
if (DaysDue < 0 && !_level.IsShowNotDue())
{
continue;
}
// We will minus the timesDunned if this is the DaysBetweenDunning is not fullfilled.
// Remember in checkup later we must reset them!
// See also checkDunningEntry()
if (daysAfterLast < DaysBetweenDunning)
{
timesDunned = timesDunned * -1;
}
//
CreateInvoiceLine(C_Invoice_ID, C_Currency_ID, GrandTotal, Open,
DaysDue, IsInDispute, C_BPartner_ID,
timesDunned, daysAfterLast);
count++;
}
idr.Close();
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
}
if (idr1 != null)
{
idr1.Close();
}
log.Log(Level.SEVERE, "addInvoices", e);
}
return(count);
}