} // sendAlert
private bool Escalate(MRequest request)
{
// Get Supervisor
MUser supervisor = request.GetSalesRep(); // self
int supervisor_ID = request.GetSalesRep().GetSupervisor_ID();
if ((supervisor_ID == 0) && (m_model.GetSupervisor_ID() != 0))
{
supervisor_ID = m_model.GetSupervisor_ID();
}
if ((supervisor_ID != 0) && (supervisor_ID != request.GetAD_User_ID()))
{
supervisor = MUser.Get(GetCtx(), supervisor_ID);
}
// Escalated: Request {0} to {1}
String subject = Msg.GetMsg(m_client.GetAD_Language(), "RequestEscalate",
new String[] { request.GetDocumentNo(), supervisor.GetName() });
String to = request.GetSalesRep().GetEMail();
if ((to == null) || (to.Length == 0))
{
log.Warning("SalesRep has no EMail - " + request.GetSalesRep());
}
else
{
m_client.SendEMail(request.GetSalesRep_ID(), subject, request.GetSummary(), request.CreatePDF());
}
// Not the same - send mail to supervisor
if (request.GetSalesRep_ID() != supervisor.GetAD_User_ID())
{
to = supervisor.GetEMail();
if ((to == null) || (to.Length == 0))
{
log.Warning("Supervisor has no EMail - " + supervisor);
}
else
{
m_client.SendEMail(supervisor.GetAD_User_ID(), subject, request.GetSummary(), request.CreatePDF());
}
}
// ----------------
request.SetDueType();
request.SetIsEscalated(true);
request.SetResult(subject);
return(request.Save());
} // escalate
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