public void Load(int custKey)
{
CustSummary summary = DataAccess.GetCustStatus(custKey);
CreditLimit = summary.CreditLimit;
AgingDate = summary.AgingDate;
PmtTermsId = summary.PmtTermsId;
Collector = summary.Collector;
HoldStatusKey = summary.HoldStatusKey;
HoldStatusId = (HoldStatusKey == null) ? "Good" : summary.HoldStatusId;
if (OverLimit(summary))
{
ARStatus = "Over";
}
if (OverDue(summary))
{
ARStatus += "Late";
}
if (ARStatus == null)
{
ARStatus = "Good";
}
}
private AccountStatus AssessStatus(CustSummary summary)
{
_prevStatus = summary.HoldStatusKey == null ? AccountStatus.Good : (AccountStatus)summary.HoldStatusKey;
switch (_prevStatus)
{
case AccountStatus.VIP:
return(AccountStatus.VIP);
case AccountStatus.ManualHold:
return(AccountStatus.ManualHold);
case AccountStatus.AutoHold:
case AccountStatus.Good:
if (OverLimit(summary) || OverDue(summary))
{
return(AccountStatus.AutoHold);
}
else
{
return(AccountStatus.Good);
}
default: //for completeness; this should never be the case
return(AccountStatus.Good);
}
}
internal static void UpdateCustomerOnHold(CustSummary summary, AccountStatus newStatus)
{
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["sage"].ConnectionString))
{
con.Open();
SqlCommand cmd = new SqlCommand("spOPUpdateARStatus", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@CustKey", summary.CustKey);
cmd.Parameters.AddWithValue("@HoldStatusKey", newStatus);
cmd.Parameters.AddWithValue("@LastUpdate", DateTime.Now);
cmd.Parameters.AddWithValue("@Balance45", summary.Balance45);
cmd.Parameters.AddWithValue("@Balance60", summary.Balance60);
cmd.Parameters.AddWithValue("@Balance90", summary.Balance90);
cmd.Parameters.AddWithValue("@TotalBalance", summary.TotalBalance);
if (summary.LastPmtDate == null)
{
cmd.Parameters.AddWithValue("@LastPmtDate", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@LastPmtDate", summary.LastPmtDate);
}
cmd.Parameters.AddWithValue("@CreditLimit", summary.CreditLimit);
cmd.Parameters.AddWithValue("@Collector", summary.Collector);
cmd.Parameters.AddWithValue("@CustID", summary.CustId);
cmd.Parameters.AddWithValue("@CustName", summary.CustName);
cmd.Parameters.AddWithValue("@PmtTerms", summary.PmtTermsId);
if (summary.LastPmtAmt == null)
{
cmd.Parameters.AddWithValue("@LastPmtAmt", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@LastPmtAmt", summary.LastPmtAmt);
}
cmd.Parameters.AddWithValue("@ContactName", summary.Name);
cmd.Parameters.AddWithValue("@ContactPhone", summary.Phone);
if (summary.PhoneExt == null)
{
cmd.Parameters.AddWithValue("@ContactPhoneExt", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@ContactPhoneExt", summary.PhoneExt);
}
cmd.Parameters.AddWithValue("@CustType", summary.CustClassId);
cmd.ExecuteNonQuery();
}
}
internal static void ClearLetterFlags(CustSummary summary)
{
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["sage"].ConnectionString))
{
con.Open();
SqlCommand cmd = new SqlCommand("spOPARStatusClearLetterFlags", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@CustKey", summary.CustKey);
cmd.ExecuteNonQuery();
}
}
internal static void DeleteCustomerOnHold(CustSummary summary)
{
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["sage"].ConnectionString))
{
string sql = String.Format(@"DELETE tcpCustHold WHERE CustKey={0}", summary.CustKey);
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
}
/*
* Called by SA Manage Customer to update a single customer's AR Status.
*
* newStatus can have one of these values:
* VIP - put this guy on permanent Good
* MANUAL_HOLD - put this guy on permanent Bad
* AUTO_HOLD - let ARStatus decide each night (different meaning than above)
*/
public void Update(int custKey, AccountStatus newStatus)
{
CustSummary summary = DataAccess.GetCustStatus(custKey);
_prevStatus = summary.HoldStatusKey == null ? AccountStatus.Good : (AccountStatus)summary.HoldStatusKey;
_newStatus = newStatus;
switch (_newStatus)
{
// AR rep forces a manual status
case AccountStatus.VIP:
case AccountStatus.ManualHold:
if (_newStatus == AccountStatus.VIP)
{
InjectRemark(summary.CustId, "Placed on VIP");
}
else if (_newStatus == AccountStatus.ManualHold)
{
InjectRemark(summary.CustId, "Placed on Manual Hold");
}
// if not in table then add, else update
if (_prevStatus == AccountStatus.Good)
{
DataAccess.InsertCustomerOnHold(summary, _newStatus);
DataAccess.LogStatusChange(summary.CustKey, _prevStatus, _newStatus);
}
else
{
DataAccess.UpdateCustomerOnHold(summary, _newStatus);
}
break;
// if the customer was on manual hold, convert to Auto in good standing or bad
case AccountStatus.AutoHold:
if (_prevStatus != AccountStatus.Good)
{
if (OverLimit(summary) || OverDue(summary))
{
DataAccess.UpdateCustomerOnHold(summary, _newStatus);
}
else
{
InjectRemark(summary.CustId, "Removed from Hold");
DataAccess.DeleteCustomerOnHold(summary);
DataAccess.LogStatusChange(summary.CustKey, _prevStatus, _newStatus);
}
}
break;
}
}
/*
* Update tcpCustHold as appropriate
* if New & Prev Status = Good then do nothing
* if NewStatus <> Good and PrevStatus = Good then INSERT
* if NewStatus = Good and PrevStatus <> Good then DELETE
* else UPDATE Status field and other fields
*/
private void UpdateStatus(CustSummary summary)
{
//accumulate values across all customers
_totalOwed += summary.TotalBalance;
_totalOver45 += summary.Balance45;
_totalOver60 += summary.Balance60;
_totalOver90 += summary.Balance90;
if (_newStatus == AccountStatus.ManualHold || _newStatus == AccountStatus.AutoHold)
{
_totalOnHold++;
}
// state machine trnasition logic
if (_prevStatus == AccountStatus.VIP || _prevStatus == AccountStatus.ManualHold)
{
DataAccess.UpdateCustomerOnHold(summary, _newStatus);
}
else if (_prevStatus == AccountStatus.Good && _newStatus == AccountStatus.AutoHold)
{
InjectRemark(summary.CustId, "Placed on Hold");
DataAccess.InsertCustomerOnHold(summary, _newStatus);
DataAccess.LogStatusChange(summary.CustKey, _prevStatus, _newStatus);
_totalNewHold++;
}
else if (_prevStatus == AccountStatus.AutoHold && _newStatus == AccountStatus.Good)
{
InjectRemark(summary.CustId, "Removed from Hold");
DataAccess.DeleteCustomerOnHold(summary);
DataAccess.LogStatusChange(summary.CustKey, _prevStatus, _newStatus);
_totalOffHold++;
}
else if (_prevStatus == AccountStatus.AutoHold && _newStatus == AccountStatus.AutoHold)
{
DataAccess.UpdateCustomerOnHold(summary, _newStatus);
if (DataAccess.HasLettersSent(summary))
{
if (!OverDue(summary) && OverLimit(summary))
{
DataAccess.ClearLetterFlags(summary);
InjectRemark(summary.CustId, "Now current, but still over creditlimit");
}
}
}
}
internal static bool HasLettersSent(CustSummary summary)
{
int retval;
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["sage"].ConnectionString))
{
con.Open();
SqlCommand cmd = new SqlCommand("spOPARStatusTestLetterFlags", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@CustKey", SqlDbType.Int).Value = summary.CustKey;
SqlParameter outputParam = cmd.Parameters.Add("@FlagCount", SqlDbType.Int);
outputParam.Direction = ParameterDirection.Output;
cmd.ExecuteScalar();
retval = (int)cmd.Parameters["@FlagCount"].Value;
}
return(retval == 0 ? false : true);
}
internal static CustSummary GetCustStatus(int custKey)
{
CustSummary summary = new CustSummary();
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["sage"].ConnectionString))
{
con.Open();
SqlCommand cmd = con.CreateCommand();
cmd.CommandText = "spOParCustStatusFull";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@CustKey", custKey));
var dr = cmd.ExecuteReader();
while (dr.Read())
{
summary.CustKey = (int)dr["CustKey"];
summary.CustId = dr["CustId"].ToString().TrimEnd();
summary.CustName = dr["CustName"].ToString().TrimEnd();
summary.CustClassId = dr["CustClassId"].ToString().TrimEnd();
summary.Collector = dr["Collector"].ToString();
summary.HoldStatusKey = dr["HoldStatusKey"] == DBNull.Value ? (AccountStatus?)null : (AccountStatus)dr["HoldStatusKey"];
summary.HoldStatusId = dr["HoldStatusId"] == DBNull.Value ? "Good" : dr["HoldStatusId"].ToString();
summary.AgingDate = dr["AgingDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dr["AgingDate"]);
summary.TotalBalance = Convert.ToDecimal(dr["TotalBalance"]);
summary.Balance45 = Convert.ToDecimal(dr["Balance45"]);
summary.Balance60 = Convert.ToDecimal(dr["Balance60"]);
summary.Balance90 = Convert.ToDecimal(dr["Balance90"]);
summary.TotalLate = Convert.ToDecimal(dr["TotalLate"]);
summary.CreditLimit = Convert.ToDecimal(dr["CreditLimit"]);
summary.PmtTermsId = dr["PmtTermsId"].ToString().TrimEnd();
summary.LastPmtAmt = dr["LastPmtAmt"] == DBNull.Value ? (decimal?)null : Convert.ToDecimal(dr["LastPmtAmt"]);
summary.LastPmtDate = dr["LastPmtDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dr["LastPmtDate"]);
summary.Name = dr["Name"].ToString();
summary.Phone = dr["Phone"].ToString();
summary.PhoneExt = dr["PhoneExt"] == DBNull.Value ? null : dr["PhoneExt"].ToString();
summary.Status = (short)dr["Status"];
summary.AccountType = dr["AccountType"].ToString();
}
return(summary);
}
}
public bool OverDue(CustSummary summary)
{
return(summary.TotalLate > MinAllowedOverdue);
}
public bool OverLimit(CustSummary summary)
{
return(summary.TotalBalance - summary.CreditLimit > 0);
}
