private void ProcessRequests()
{
/**
* Due Requests (Scheduled -> Due)
*/
String sql = "SELECT * FROM R_Request "
+ "WHERE DueType='" + X_R_Request.DUETYPE_Scheduled + "' AND Processed='N'"
+ " AND DateNextAction > SysDate"
+ " AND AD_Client_ID=" + m_model.GetAD_Client_ID();
if (m_model.GetR_RequestType_ID() != 0)
{
sql += " AND R_RequestType_ID=" + m_model.GetR_RequestType_ID();
}
int count = 0;
int countEMails = 0;
try
{
DataSet ds = DB.ExecuteDataset(sql);
foreach (DataRow dr in ds.Tables[0].Rows)
{
MRequest request = new MRequest(GetCtx(), dr, null);
request.SetDueType();
if (request.IsDue())
{
if (request.GetRequestType().IsEMailWhenDue())
{
if (SendEmail(request, "RequestDue"))
{
request.SetDateLastAlert();
countEMails++;
}
}
request.Save();
count++;
}
}
}
catch (Exception e)
{
log.Log(Level.SEVERE, sql, e);
}
m_summary.Append("New Due #").Append(count);
if (countEMails > 0)
{
m_summary.Append(" (").Append(countEMails).Append(" EMail)");
}
m_summary.Append(" - ");
/**
* Overdue Requests.
* Due Requests - are they overdue? (Send EMail)
*/
sql = "SELECT * FROM R_Request r "
+ "WHERE r.DueType='" + X_R_Request.DUETYPE_Due + "' AND r.Processed='N'"
+ " AND AD_Client_ID=" + m_model.GetAD_Client_ID()
+ " AND EXISTS (SELECT * FROM R_RequestType rt "
+ "WHERE r.R_RequestType_ID=rt.R_RequestType_ID"
+ " AND addDays(r.DateNextAction,rt.DueDateTolerance) > SysDate)";
// + " AND addDays(COALESCE(r.DateNextAction,Updated),rt.DueDateTolerance)>SysDate)";
if (m_model.GetR_RequestType_ID() != 0)
{
sql += " AND r.R_RequestType_ID=" + m_model.GetR_RequestType_ID();
}
count = 0;
countEMails = 0;
try
{
DataSet ds = DB.ExecuteDataset(sql);
foreach (DataRow dr in ds.Tables[0].Rows)
{
MRequest request = new MRequest(GetCtx(), dr, null);
request.SetDueType();
if (request.IsOverdue())
{
if (request.GetRequestType().IsEMailWhenOverdue() &&
!TimeUtil.IsSameDay(request.GetDateLastAlert(), null))
{
if (SendEmail(request, "RequestDue"))
{
request.SetDateLastAlert();
countEMails++;
}
}
request.Save();
count++;
}
}
}
catch (Exception e)
{
log.Log(Level.SEVERE, sql, e);
}
m_summary.Append("New Overdue #").Append(count);
if (countEMails > 0)
{
m_summary.Append(" (").Append(countEMails).Append(" EMail)");
}
m_summary.Append(" - ");
/**
* Send (over)due alerts
*/
if (m_model.GetOverdueAlertDays() > 0)
{
sql = "SELECT * FROM R_Request "
+ "WHERE Processed='N'"
+ " AND AD_Client_ID=" + m_model.GetAD_Client_ID()
//jz + " AND (DateNextAction+" + m_model.getOverdueAlertDays() + ") > SysDate"
+ " AND addDays(DateNextAction," + m_model.GetOverdueAlertDays() + ") > SysDate "
+ " AND (DateLastAlert IS NULL";
if (m_model.GetRemindDays() > 0)
{
//jz sql += " OR (DateLastAlert+" + m_model.getRemindDays()
sql += " OR addDays(DateLastAlert," + m_model.GetRemindDays()
+ ") > SysDate ";
}
sql += ")";
if (m_model.GetR_RequestType_ID() != 0)
{
sql += " AND R_RequestType_ID=" + m_model.GetR_RequestType_ID();
}
count = 0;
countEMails = 0;
try
{
DataSet ds = DB.ExecuteDataset(sql);
foreach (DataRow dr in ds.Tables[0].Rows)
{
MRequest request = new MRequest(GetCtx(), dr, null);
request.SetDueType();
if (request.GetRequestType().IsEMailWhenOverdue() &&
((request.GetDateLastAlert() == null) ||
!TimeUtil.IsSameDay(request.GetDateLastAlert(), null)))
{
if (SendEmail(request, "RequestAlert"))
{
request.SetDateLastAlert();
countEMails++;
}
}
request.Save();
count++;
}
}
catch (SqlException e)
{
log.Log(Level.SEVERE, sql, e);
}
m_summary.Append("Alerts #").Append(count);
if (countEMails > 0)
{
m_summary.Append(" (").Append(countEMails).Append(" EMail)");
}
m_summary.Append(" - ");
} // Overdue
/**
* Escalate if Date Next Action + Overdue Assign Days > SysDate
*/
if (m_model.GetOverdueAssignDays() > 0)
{
sql = "SELECT * FROM R_Request "
+ "WHERE Processed='N'"
+ " AND AD_Client_ID=" + m_model.GetAD_Client_ID()
+ " AND IsEscalated='N'"
+ " AND addDays(DateNextAction," + m_model.GetOverdueAssignDays()
+ ") > SysDate";
if (m_model.GetR_RequestType_ID() != 0)
{
sql += " AND R_RequestType_ID=" + m_model.GetR_RequestType_ID();
}
count = 0;
countEMails = 0;
try
{
DataSet ds = DB.ExecuteDataset(sql);
foreach (DataRow dr in ds.Tables[0].Rows)
{
MRequest request = new MRequest(GetCtx(), dr, null);
if (Escalate(request))
{
count++;
}
}
}
catch (SqlException e)
{
log.Log(Level.SEVERE, sql, e);
}
m_summary.Append("Escalated #").Append(count).Append(" - ");
} // Esacalate
/**
* Send Inactivity alerts
*/
if (m_model.GetInactivityAlertDays() > 0)
{
sql = "SELECT * FROM R_Request r "
+ "WHERE r.Processed='N'"
+ " AND r.AD_Client_ID=" + m_model.GetAD_Client_ID()
// Nothing happening for x days
+ " AND addDays(r.Updated," + m_model.GetInactivityAlertDays() + ") < SysDate "
+ " AND (r.DateLastAlert IS NULL";
if (m_model.GetRemindDays() > 0)
{
sql += " OR addDays(r.DateLastAlert," + m_model.GetRemindDays()
+ ") < SysDate ";
}
sql += ")";
// Next Date & Updated over due date tolerance
sql += " AND EXISTS (SELECT * FROM R_RequestType rt "
+ "WHERE r.R_RequestType_ID=rt.R_RequestType_ID"
+ " AND addDays(COALESCE(r.DateNextAction,Updated),rt.DueDateTolerance) > SysDate)";
if (m_model.GetR_RequestType_ID() != 0)
{
sql += " AND r.R_RequestType_ID=" + m_model.GetR_RequestType_ID();
}
count = 0;
countEMails = 0;
try
{
DataSet ds = DB.ExecuteDataset(sql);
foreach (DataRow dr in ds.Tables[0].Rows)
{
MRequest request = new MRequest(GetCtx(), dr, null);
request.SetDueType();
// only once per day
if (!TimeUtil.IsSameDay(request.GetDateLastAlert(), null))
{
if (SendEmail(request, "RequestInactive"))
{
request.SetDateLastAlert();
countEMails++;
}
request.Save();
count++;
}
}
}
catch (SqlException e)
{
log.Log(Level.SEVERE, sql, e);
}
m_summary.Append("Inactivity #").Append(count);
if (countEMails > 0)
{
m_summary.Append(" (").Append(countEMails).Append(" EMail)");
}
m_summary.Append(" - ");
} // Inactivity
} // processRequests
public RequestProcessor(MRequestProcessor model)
: base(model, 60) // 1 minute delay
{
m_model = model;
m_client = MClient.Get(model.GetCtx(), model.GetAD_Client_ID());
} // RequestProcessor
