private StringFieldCriterion selectLoanFolder()
{
StringFieldCriterion folderCri = new StringFieldCriterion();
folderCri.FieldName = "Loan.LoanFolder";
folderCri.MatchType = StringFieldMatchType.Exact;
switch (reportNum)
{
case 8:
folderCri.Value = "My Pipeline";
break;
case 9:
folderCri.Value = "Completed Loans";
break;
case 10:
folderCri.Value = "Serviced Loans";
break;
case 11:
folderCri.Value = "Adverse Loans";
break;
case 12:
folderCri.Value = "Prospective Clients";
break;
default:
break;
}
log.Info("updating '" + folderCri.Value + "' folder");
return(folderCri);
}
/// <summary>
/// returns the guid for a given loan_number, using Encompass's search function
/// </summary>
/// <param name="session">an active Encompass session</param>
/// <param name="loan_number">loan number of the loan</param>
/// <returns>the guid (as a string) or String.Empty if not found.</returns>
public static string GetGuidForLoanNumber(Session session, string loan_number)
{
StringFieldCriterion sfc = new StringFieldCriterion("Loan.LoanNumber", loan_number,
StringFieldMatchType.Exact, true);
LoanIdentityList loanList = session.Loans.Query(sfc);
string guid = String.Empty;
//Loan.LoanNumber SHOULD be unique, but there is a timing bug
//If multiple loans found, retun the last one
foreach (LoanIdentity id in loanList)
{
guid = id.Guid;
}
return(guid);
}
/// <summary>
/// Entry point for the SDK Application
/// </summary>
static void Main(string[] args)
{
// Start the Encompass Session using a user's credentials
Session session = new Session();
session.Start(
String.Format("https://{0}.ea.elliemae.net${0}", ConfigurationManager.AppSettings["InstanceID"]),
ConfigurationManager.AppSettings["UserID"],
ConfigurationManager.AppSettings["Password"]
);
// Query for a Loan
StringFieldCriterion cri = new StringFieldCriterion()
{
FieldName = "Loan.LoanNumber",
Value = "1801EM000070"
};
// Run the query to get the Loan Identifier
LoanIdentityList loanIds = session.Loans.Query(cri);
if (loanIds.Count != 1)
{
Console.WriteLine("Loan number did not return a unique match");
return;
}
// Open and lock the Loan
string loanGuid = loanIds[0].Guid;
using (Loan loan = session.Loans.Open(loanGuid, true, true))
{
loan.Fields["1887"].Value = DateTime.Today;
loan.Commit();
loan.Unlock();
}
// Close the session
session.End();
}
public static LoanMetadata GetLoanMetadata(Session session, string loanGuid)
{
LoanMetadata meta = null;
var guid = new StringFieldCriterion();
guid.FieldName = "GUID";
guid.MatchType = StringFieldMatchType.CaseInsensitive;
guid.Value = loanGuid;
var fields = new StringList();
fields.Add(FIELD_LOAN_LASTMODIFIED);
fields.Add(FIELD_LOAN_GUID);
fields.Add(FIELD_LOAN_FOLDER);
fields.Add(FIELD_LOAN_NUMBER);
try
{
// Using Reports so that we can accomodate LoanFileSeqeuenceNumber when we add it to the reporting database
var cursor = session.Reports.OpenReportCursor(fields, guid);
if (cursor.Count > 0)
{
var item = cursor.GetItem(0);
meta = new LoanMetadata
{
Guid = (string)item[FIELD_LOAN_GUID],
LoanNumber = (string)item[FIELD_LOAN_NUMBER],
LoanFolder = (string)item[FIELD_LOAN_FOLDER],
LastModified = (DateTime)item[FIELD_LOAN_LASTMODIFIED]
};
}
cursor.Close();
}
catch
{
// noop; return null
}
return(meta);
}
public static Loan OpenLoanFromLoanNumber(Session session, string loan_number)
{
StringFieldCriterion sfc = new StringFieldCriterion("Loan.LoanNumber", loan_number,
StringFieldMatchType.Exact, true);
LoanIdentityList loanList = session.Loans.Query(sfc);
string guid = null;
//Loan.LoanNumber SHOULD be unique, but there is a timing bug
//If multiple loans found, retun the last one
foreach (LoanIdentity id in loanList)
{
guid = id.Guid;
}
if (!String.IsNullOrWhiteSpace(guid))
{
return session.Loans.Open(guid);
}
else
{
return null;
}
}
private String startApplication()
{
Console.Out.WriteLine("Program running...");
String text = "";
//Investor Lock Date
/* DateFieldCriterion invLockEmpty = new DateFieldCriterion();
* invLockEmpty.FieldName = "Fields.2220";
* invLockEmpty.Value = DateFieldCriterion.EmptyDate;
* invLockEmpty.MatchType = OrdinalFieldMatchType.Equals;
*
* //non empty CTC date
* DateFieldCriterion ctcNonEmpty = new DateFieldCriterion();
* ctcNonEmpty.FieldName = "Fields.Log.MS.Date.Clear to Close";
* ctcNonEmpty.Value = DateFieldCriterion.NonEmptyDate;
* ctcNonEmpty.MatchType = OrdinalFieldMatchType.Equals;
*
* //empty funding date
* DateFieldCriterion fundDateEmpty = new DateFieldCriterion();
* fundDateEmpty.FieldName = "Fields.Log.MS.Date.Funding";
* fundDateEmpty.Value = DateFieldCriterion.EmptyDate;
* fundDateEmpty.MatchType = OrdinalFieldMatchType.Equals;
*
* //or
* DateFieldCriterion fundDateNotEmpty = new DateFieldCriterion();
* fundDateNotEmpty.FieldName = "Fields.Log.MS.Date.Funding";
* fundDateNotEmpty.Value = DateFieldCriterion.NonEmptyDate;
* fundDateNotEmpty.MatchType = OrdinalFieldMatchType.Equals;*/
StringFieldCriterion folderCri = new StringFieldCriterion();
folderCri.FieldName = "Loan.LoanFolder";
folderCri.Value = "My Pipeline";
folderCri.MatchType = StringFieldMatchType.Exact;
StringFieldCriterion folderCri2 = new StringFieldCriterion();
folderCri2.FieldName = "Loan.LoanFolder";
folderCri2.Value = "Completed Loans";
folderCri2.MatchType = StringFieldMatchType.Exact;
DateFieldCriterion purchDateNotEmpty = new DateFieldCriterion();
purchDateNotEmpty.FieldName = "Fields.Log.MS.Date.Purchased";
purchDateNotEmpty.Value = DateFieldCriterion.NonEmptyDate;
purchDateNotEmpty.MatchType = OrdinalFieldMatchType.Equals;
DateFieldCriterion purchDateThisMonth = new DateFieldCriterion();
purchDateThisMonth.FieldName = "Fields.Log.MS.Date.Purchased";
purchDateThisMonth.Value = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
purchDateThisMonth.MatchType = OrdinalFieldMatchType.GreaterThan;
QueryCriterion fullQuery = purchDateThisMonth.And(purchDateNotEmpty).And(folderCri.Or(folderCri2));
StringList fields = new StringList();
Row row = new Row();
row.setHeader(true);
row.add("Investor");
fields.Add("Fields.2825");
row.add("Inv #");
fields.Add("Fields.2826");
row.add("Loan #");
fields.Add("Fields.364");
row.add("Borrower Name");
fields.Add("Fields.4002");
fields.Add("Fields.4000");
row.add("Address");
fields.Add("Fields.11");
row.add("Rate");
fields.Add("Fields.3");
row.add("Loan Amount");
fields.Add("Fields.1109");
row.add("Funded");
fields.Add("Fields.Log.MS.Date.Funding");
row.add("Purchased");
fields.Add("Fields.Log.MS.Date.Purchased");
// row.add("Base YSP");
fields.Add("Fields.2232");
// row.add("Total Adj");
fields.Add("Fields.2273");
// row.add("Net YSP");
fields.Add("Fields.2274");
// row.add("Net SRP");
fields.Add("Fields.2276");
row.add("Total Rebate");
row.add("Rebate");
row.add("Investor Interest");
fields.Add("Fields.2834");
row.add("Investor Fees");
fields.Add("Fields.2373");
fields.Add("Fields.2375");
fields.Add("Fields.2377");
fields.Add("Fields.2379");
fields.Add("Fields.2381");
fields.Add("Fields.2383");
row.add("Appraisal");
fields.Add("Fields.641");
row.add("Credit Fee");
fields.Add("Fields.640");
row.add("Interest");
fields.Add("Fields.334");
row.add("Escrow Fees");
fields.Add("Fields.NEWHUD2.X11");
fields.Add("Fields.NEWHUD2.X14");
fields.Add("Fields.NEWHUD.X808");
fields.Add("Fields.NEWHUD.X810");
fields.Add("Fields.NEWHUD.X812");
fields.Add("Fields.NEWHUD.X814");
fields.Add("Fields.NEWHUD.X816");
fields.Add("Fields.NEWHUD.X818");
fields.Add("Fields.NEWHUD.X639");
row.add("Title Fees");
fields.Add("Fields.NEWHUD.X572");
fields.Add("Fields.NEWHUD.X639");
fields.Add("Fields.NEWHUD.X215");
fields.Add("Fields.NEWHUD.X216");
fields.Add("Fields.1763");
fields.Add("Fields.1768");
fields.Add("Fields.1773");
fields.Add("Fields.1778");
fields.Add("Fields.NEWHUD.X1604");
fields.Add("Fields.NEWHUD.X1612");
row.add("Recording Fee");
fields.Add("Fields.NEWHUD.X607");
row.add("Processor");
fields.Add("Fields.362");
row.add("Loan Officer");
fields.Add("Fields.317");
//loan purpose
fields.Add("Fields.19");
report.Add(row);
SortCriterionList sortOrder = new SortCriterionList();
sortOrder.Add(new SortCriterion("Fields.Log.MS.Date.Purchased", SortOrder.Ascending));
LoanReportCursor results = Program.mySession.getSession().Reports.OpenReportCursor(fields, fullQuery, sortOrder);
Console.Out.WriteLine(results.ToString());
int count = results.Count;
Console.Out.WriteLine("Total Files" + ": " + count);
//end program if results empty
if (count == 0)
{
results.Close();
Environment.Exit(0);
}
//local variables
double rebateTotal = 0;
double escrowTotal = 0;
double titleTotal = 0;
double interestTotal = 0;
//double warehouseFeeTotal = 0;
//double warehouseIntTotal = 0;
double appraisalTotal = 0;
double creditTotal = 0;
double recordingTotal = 0;
double investorFeeTotal = 0;
double investorIntTotal = 0;
text += "Total Files Purchased this Month-to-Date: <b>" + count + "</b><br/><br/>";
text += "<div class='small'><ul>Fees and Income not included" +
"<li>warehouse fees and interest</li>" +
"<li>payoff fees refunded to borrower</li>" +
"</ul> </div>";
//iterate through query and format
foreach (LoanReportData data in results)
{
Row line = new Row();
line.add(data["Fields.2825"].ToString());
line.add(data["Fields.2826"].ToString());
line.add(data["Fields.364"].ToString());
line.add((data["Fields.4002"].ToString() + " " + data["Fields.4000"].ToString()).ToUpper());
line.add(data["Fields.11"].ToString().ToUpper());
line.add(Convert.ToDouble(data["Fields.3"]).ToString("F3"));
int loanAmt = Convert.ToInt32(data["Fields.1109"]);
line.add(loanAmt.ToString("C"));
line.add(Convert.ToDateTime(data["Fields.Log.MS.Date.Funding"]).ToShortDateString());
line.add(Convert.ToDateTime(data["Fields.Log.MS.Date.Purchased"]).ToShortDateString());
// line.add(Utility.toPercent(data["Fields.2232"]));
// line.add(Utility.toPercent(data["Fields.2273"]));
// line.add(Utility.toPercent(data["Fields.2274"]));
// line.add(Utility.toPercent(data["Fields.2276"]));
Double rebate = (Convert.ToDouble(data["Fields.2274"]) + Convert.ToDouble(data["Fields.2276"]));
if (rebate <= 0.0001)
{
line.add(rebate.ToString("F3"), 1);
line.add(" ");
}
else
{
line.add(rebate.ToString("F3"));
Double rebateAmt = (rebate - 100) * loanAmt * .01;
if (rebateAmt <= 0)
{
line.add(rebateAmt.ToString("C"), 2);
}
else
{
line.add(rebateAmt.ToString("C"));
}
rebateTotal += rebateAmt;
}
double investorInt = Convert.ToDouble(data["Fields.2834"]);
line.add((investorInt * -1).ToString("C"));
investorIntTotal += investorInt;
double investorFees = Convert.ToDouble(data["Fields.2373"]) + Convert.ToDouble(data["Fields.2375"])
+ Convert.ToDouble(data["Fields.2377"]) + Convert.ToDouble(data["Fields.2379"])
+ Convert.ToDouble(data["Fields.2381"]) + Convert.ToDouble(data["Fields.2383"]);
line.add((investorFees * -1).ToString("C"));
investorFeeTotal += investorFees;
double appraisalFee = (Convert.ToDouble(data["Fields.641"]));
line.add((-1 * appraisalFee).ToString("C")); //appraisal fee
appraisalTotal += appraisalFee;
double creditFee = (Convert.ToDouble(data["Fields.640"]));
line.add((creditFee * -1).ToString("C")); //Credit fee
creditTotal += creditFee;
double interest = Convert.ToDouble(data["Fields.334"]);
line.add((interest).ToString("C")); //interest
interestTotal += interest;
String purpose = data["Fields.19"].ToString();
if (purpose.Contains("Refi"))
{
double escrowFees = Convert.ToDouble(data["Fields.NEWHUD2.X11"]) +
Convert.ToDouble(data["Fields.NEWHUD.X808"]) + Convert.ToDouble(data["Fields.NEWHUD2.X14"]) +
Convert.ToDouble(data["Fields.NEWHUD.X810"]) + Convert.ToDouble(data["Fields.NEWHUD.X812"]) +
Convert.ToDouble(data["Fields.NEWHUD.X814"]) + Convert.ToDouble(data["Fields.NEWHUD.X816"]) +
Convert.ToDouble(data["Fields.NEWHUD.X818"]);
line.add(escrowFees.ToString("C"));
escrowTotal += escrowFees;
double titleFees =
Convert.ToDouble(data["Fields.NEWHUD.X215"]) + Convert.ToDouble(data["Fields.NEWHUD.X216"]) +
Convert.ToDouble(data["Fields.1763"]) + Convert.ToDouble(data["Fields.1768"]) +
Convert.ToDouble(data["Fields.NEWHUD.X639"]) +
Convert.ToDouble(data["Fields.1773"]) + Convert.ToDouble(data["Fields.1778"]) +
Convert.ToDouble(data["Fields.NEWHUD.X1604"]) + Convert.ToDouble(data["Fields.NEWHUD.X1612"]);
titleTotal += titleFees;
//Convert.ToDouble(data["Fields.NEWHUD.X572"]) owner's title
line.add((-1 * titleFees).ToString("C"));
double recordingFee = Convert.ToDouble(data["Fields.NEWHUD.X607"]);
line.add((recordingFee * -1).ToString("C"));
recordingTotal += recordingFee;
}
else
{
line.add("Purchase");
line.add("Purchase");
line.add("Purchase");
}
line.add(data["Fields.362"].ToString());
line.add(data["Fields.317"].ToString());
report.Add(line);
Console.Out.Write("."); //status bar
}
//process last row
Row totals = new Row();
totals.setHeader(true);
totals.add("");
totals.add("");
totals.add("");
totals.add("");
totals.add("");
totals.add("");
totals.add("");
totals.add("Count");
totals.add(count.ToString());
totals.add("Total");
totals.add(rebateTotal.ToString("C"));
totals.add((-1 * investorIntTotal).ToString("C"));
totals.add((-1 * investorFeeTotal).ToString("C"));
totals.add((-1 * appraisalTotal).ToString("C"));
totals.add((-1 * creditTotal).ToString("C"));
totals.add((-1 * interestTotal).ToString("C"));
totals.add(escrowTotal.ToString("C"));
totals.add((-1 * titleTotal).ToString("C"));
totals.add((-1 * recordingTotal).ToString("C"));
totals.add("Grand Total");
double sum = rebateTotal - investorIntTotal - investorFeeTotal
- appraisalTotal - creditTotal + interestTotal + escrowTotal
- titleTotal - recordingTotal;
totals.add(sum.ToString("C"));
report.Add(totals);
Console.Out.WriteLine("");
results.Close();
text += formatReport(report);
return(text);
}
private void BatchUpdater()
{
Session newSession = new Session();
newSession.Start("https://TEBE11147866.ea.elliemae.net$TEBE11147866", "admin", "Y0uT@lk!ngT0M3?");
String currentUser = EncompassApplication.Session.UserID;
//Excel prep and call
Microsoft.Office.Interop.Excel.Application userApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook userWorkbook = userApp.Workbooks.Open(@"\\ftwfs02\Groups\POS\Encompass Support\Batch_Updater1.csv");
Microsoft.Office.Interop.Excel._Worksheet userWorksheet = userWorkbook.Sheets[1];
Microsoft.Office.Interop.Excel.Range userRange = userWorksheet.UsedRange;
//userWorksheet.Cells.NumberFormat = "General";
try
{
//set shortcut for loan call
Loan loan = EncompassApplication.CurrentLoan;
//row/column setup
int rCnt = 1;
int cCnt = 1;
int rowCount = userRange.Rows.Count;
int colCount = userRange.Columns.Count;
string r1c2string = "";
string r1c3string = "";
string r1c4string = "";
string r1c5string = "";
string r1c6string = "";
var batchT = loan.Fields["CX.PC.BATCH.TYPE"].Value;
string batchType = batchT.ToString();
DateTime dateRan = new DateTime();
dateRan = DateTime.Now;
double colData1 = 0;
string colDatSt = colData1.ToString();
cCnt++;
var r1c2 = userWorksheet.Cells[rCnt, cCnt].Value2;
r1c2string = r1c2.ToString();
cCnt++;
var r1c3 = userWorksheet.Cells[rCnt, cCnt].Value2;
if (r1c3 != null)
{
r1c3string = r1c3.ToString();
cCnt++;
}
var r1c4 = userWorksheet.Cells[rCnt, cCnt].Value2;
if (r1c4 != null)
{
r1c4string = r1c4.ToString();
cCnt++;
}
var r1c5 = userWorksheet.Cells[rCnt, cCnt].Value2;
if (r1c5 != null)
{
r1c5string = r1c5.ToString();
cCnt++;
}
var r1c6 = userWorksheet.Cells[rCnt, cCnt].Value2;
if (r1c6 != null)
{
r1c6string = r1c6.ToString();
cCnt++;
}
cCnt = 1;
rCnt = 2;
cCnt = 2;
var colData2 = userWorksheet.Cells[rCnt, cCnt].Text;
string colData2string = "";
cCnt++;
var colData3 = userWorksheet.Cells[rCnt, cCnt].Text;
string colData3string = "";
cCnt++;
var colData4 = userWorksheet.Cells[rCnt, cCnt].Text;
string colData4string = "";
cCnt++;
var colData5 = userWorksheet.Cells[rCnt, cCnt].Text;
string colData5string = "";
cCnt++;
var colData6 = userWorksheet.Cells[rCnt, cCnt].Text;
string colData6string = "";
cCnt = 1;
for (rCnt = 2; rCnt <= rowCount; rCnt++)
{
if (userWorksheet.Cells[rCnt, cCnt].Value2 is null)
{
break;
}
colData1 = (double)userWorksheet.Cells[rCnt, cCnt].Value2;
colDatSt = colData1.ToString();
if (colData1.ToString().Substring(0, 1) == "6")
{
colDatSt = "00" + colDatSt;
colData1 = Convert.ToDouble(colDatSt);
}
cCnt++;
colData2 = userWorksheet.Cells[rCnt, cCnt].Text;
colData2string = colData2.ToString();
//Additional columns as needed
cCnt++;
colData3 = userWorksheet.Cells[rCnt, cCnt].Text;
if (colData3 != "")
{
colData3string = colData3.ToString();
cCnt++;
}
colData4 = userWorksheet.Cells[rCnt, cCnt].Text;
if (colData4 != "")
{
colData4string = colData4.ToString();
cCnt++;
}
colData5 = userWorksheet.Cells[rCnt, cCnt].Text;
if (colData5 != "")
{
colData5string = colData5.ToString();
cCnt++;
}
colData6 = userWorksheet.Cells[rCnt, cCnt].Text;
if (colData6 != "")
{
colData6string = colData6.ToString();
cCnt++;
}
cCnt = 1;
StringFieldCriterion cri = new StringFieldCriterion();
cri.FieldName = "Loan.LoanNumber";
cri.Value = colDatSt;
BatchUpdate batch = new BatchUpdate(cri);
batch.Fields.Add(r1c2string, colData2string);
if (colData3string != "")
{
batch.Fields.Add(r1c3string, colData3string);
}
if (colData4string != "")
{
batch.Fields.Add(r1c4string, colData4string);
}
if (colData5string != "")
{
batch.Fields.Add(r1c5string, colData5string);
}
if (colData6string != "")
{
batch.Fields.Add(r1c6string, colData6string);
}
batch.Fields.Add("CX.PC.BATCH.COLL.TRACK", currentUser);
batch.Fields.Add("CX.PC.BATCH.COL.DATE", dateRan);
batch.Fields.Add("CX.PC.BATCH.IRON.MOUNTAIN", batchType);
EncompassApplication.Session.Loans.SubmitBatchUpdate(batch);
}
}
catch (Exception)
{
MessageBox.Show("Something went wrong. Please click YES to save on the next popup, rebuild the spreadsheet and run the Batch Update again.");
}
finally
{
userRange.Delete(XlDeleteShiftDirection.xlShiftUp);
string fileName = @"\\ftwfs02\Groups\POS\Encompass Support\Batch_Updater1.csv";
string folder = System.IO.Path.GetDirectoryName(fileName);
if (System.IO.Directory.Exists(folder))
{
userWorkbook.Close(true, fileName, null);
}
userApp.Quit();
Marshal.ReleaseComObject(userWorksheet);
Marshal.ReleaseComObject(userWorkbook);
Marshal.ReleaseComObject(userApp);
newSession.End();
}
//userRange.Delete(XlDeleteShiftDirection.xlShiftUp);
//string fileName = @"H:\Encompass Support\Batch_Updater2.csv";
//string folder = System.IO.Path.GetDirectoryName(fileName);
//if (System.IO.Directory.Exists(folder))
//{
// userWorkbook.Close(true, fileName, null);
//}
//userApp.Quit();
//Marshal.ReleaseComObject(userWorksheet);
//Marshal.ReleaseComObject(userWorkbook);
//Marshal.ReleaseComObject(userApp);
//newSession.End();
}
private List <LoanActionResult> UnassignRole(IProgress <string> progress, Role roleSelected, List <string> loanNumbersToUnassign)
{
List <LoanActionResult> response = new List <LoanActionResult>();
int count = 0;
foreach (var loanNumber in loanNumbersToUnassign)
{
count++;
string loanProgress = $"{count}/{loanNumbersToUnassign.Count}. {loanNumber}.";
progress.Report($"{loanProgress} Searching for loan..");
var loanResult = new LoanActionResult()
{
WasSuccessful = true
};
try
{
StringFieldCriterion loanNumberCri = new StringFieldCriterion();
loanNumberCri.FieldName = "Loan.LoanNumber";
loanNumberCri.Value = loanNumber;
loanNumberCri.MatchType = StringFieldMatchType.Exact;
loanNumberCri.Include = true;
QueryCriterion fullQuery = loanNumberCri;
LoanIdentityList ids = EncompassApplication.Session.Loans.Query(fullQuery);
if (ids.Count == 0) // if no loans found
{
progress.Report($"{loanProgress} Cannot find loan.");
loanResult.Result = $"Cannot find loan";;
response.Add(loanResult);
// logger.Info((rowIndex - 1) + "/" + (rowCount - 1) + ". Cannot find loan number \"" + loanNumberCri + "\". Going to next.");
continue; // skip the remainder of this iteration
}
//logger.Info((rowIndex - 1) + "/" + (rowCount - 1) + ". Opening loan guid: " + ids[0].Guid);
progress.Report($"{loanProgress} Opening loan..");
Loan currentLoan = EncompassApplication.Session.Loans.Open(ids[0].Guid);
loanResult.LoanNumber = currentLoan.LoanNumber;
if (currentLoan.GetCurrentLocks().Count > 0)
{
string msg = $"Loan opened by {currentLoan.GetCurrentLock().LockedBy}";
progress.Report($"{loanProgress} {msg}");
loanResult.Result = msg;
response.Add(loanResult);
//loanSummaryCell.Value = $"Loan opened by {currentLoan.GetCurrentLock().LockedBy}";
currentLoan.Close();
continue;
}
currentLoan.Lock();
bool needToSaveLoan = false;
string userName = "";
foreach (LoanAssociate loanAssociate in currentLoan.Associates)
{
if (loanAssociate.WorkflowRole == roleSelected)
{
if (loanAssociate.User != null)
{
userName = loanAssociate.User.FullName;
loanAssociate.Unassign();
needToSaveLoan = true;
}
}
}
if (needToSaveLoan)
{
string msg = $"'{roleSelected.Name}' role successfully unassigned";
progress.Report($"{loanProgress} {msg}. Saving...");
currentLoan.Commit();
loanResult.Result = msg;
}
else
{
string msg = $"'{roleSelected.Name}' role not found on loan";
progress.Report($"{loanProgress} {msg}.");
loanResult.Result = msg;
}
currentLoan.Close();
}
catch (Exception ex)
{
loanResult.WasSuccessful = false;
progress.Report($"{loanProgress} ERROR HIT {ex.ToString()}.");
loanResult.ErrorMessage = ex.ToString();
}
response.Add(loanResult);
}
return(response);
}
private String startApplication()
{
log.Info("Program running...");
String text = "";
DateFieldCriterion cri = new DateFieldCriterion();
cri.FieldName = "Fields.Log.MS.Date.Funding";
cri.Value = DateFieldCriterion.NonEmptyDate;
cri.MatchType = OrdinalFieldMatchType.Equals;
DateFieldCriterion cri2 = new DateFieldCriterion();
cri2.FieldName = "Fields.Log.MS.Date.Purchased";
cri2.Value = DateFieldCriterion.EmptyDate;
cri2.MatchType = OrdinalFieldMatchType.Equals;
StringFieldCriterion folderCri = new StringFieldCriterion();
folderCri.FieldName = "Loan.LoanFolder";
folderCri.Value = "My Pipeline";
folderCri.MatchType = StringFieldMatchType.Exact;
QueryCriterion fullQuery = folderCri.And(cri.And(cri2));
StringList fields = new StringList();
fields.Add("Fields.VEND.X263");
fields.Add("Fields.352");
fields.Add("Fields.364");
fields.Add("Fields.37");
fields.Add("Fields.4000");
fields.Add("Fields.1109");
fields.Add("Fields.362");
fields.Add("Fields.317");
fields.Add("Fields.Log.MS.Date.Funding");
SortCriterionList sortOrder = new SortCriterionList();
sortOrder.Add(new SortCriterion("Fields.Log.MS.Date.Funding"));
LoanReportCursor results = Program.mySession.getSession().Reports.OpenReportCursor(fields, fullQuery, sortOrder);
Console.Out.WriteLine(results.ToString());
int count = results.Count;
log.Info("Total Files Not Purchased " + DateTime.Now.ToShortDateString() + ": " + count);
text += "Total Files Not Purchased: <b>" + count + "</b><br/><br/>";
//headers
Row row = new Row();
row.setHeader(true);
row.add("Investor");
row.add("Inv #");
row.add("Loan #");
row.add("Borrower Name");
//row.add("First Name");
row.add("Loan Amount");
row.add("Processor");
row.add("Loan Officer");
row.add("Funding Date");
report.Add(row);
foreach (LoanReportData data in results)
{
Row line = new Row();
line.add(data["Fields.VEND.X263"].ToString());
line.add(data["Fields.352"].ToString());
line.add(data["Fields.364"].ToString());
line.add(data["Fields.37"].ToString().ToUpper() + ", " + data["Fields.4000"].ToString().ToUpper());
line.add(Convert.ToInt32(data["Fields.1109"]).ToString("C"));
line.add(data["Fields.362"].ToString());
line.add(data["Fields.317"].ToString());
line.add(Math.Ceiling(DateTime.Now.Subtract(Convert.ToDateTime(data["Fields.Log.MS.Date.Funding"])).TotalDays).ToString());
report.Add(line);
Console.Out.Write("."); //status bar
}
Console.Out.WriteLine("");
results.Close();
text += formatReport(report);
return(text);
}
private static void ConditionReport(string SellerID, string LimitedSet, string newFileName)
{
try
{
//login to server
Session session = new Session();
session.Start("https://be11147937.ea.elliemae.net$be11147937", "sdkreport", "CqR3Fdt3LTKwVKCr");
using (session)
{
//build loan query
DateFieldCriterion tpoSubmitted = new DateFieldCriterion();
tpoSubmitted.FieldName = "Fields.TPO.X90";
tpoSubmitted.Value = DateFieldCriterion.NonEmptyDate;
tpoSubmitted.MatchType = OrdinalFieldMatchType.Equals;
tpoSubmitted.Precision = DateFieldMatchPrecision.Exact;
StringFieldCriterion sellerID = new StringFieldCriterion();
sellerID.FieldName = "Fields.TPO.X15";
sellerID.Value = SellerID;
sellerID.MatchType = StringFieldMatchType.Exact;
if (SellerID.ToUpper().Equals("ALL"))
{
sellerID.Value = "";
sellerID.Include = false;
}
else { sellerID.Include = true; }
NumericFieldCriterion allCondCount = new NumericFieldCriterion();
allCondCount.FieldName = "Fields.UWC.ALLCOUNT";
allCondCount.Value = 0;
allCondCount.MatchType = OrdinalFieldMatchType.GreaterThan;
StringFieldCriterion msPurchaseAC = new StringFieldCriterion();
msPurchaseAC.FieldName = "Fields.Log.MS.Status.Purchase - AC";
msPurchaseAC.Value = "Achieved";
msPurchaseAC.MatchType = StringFieldMatchType.Contains;
msPurchaseAC.Include = false;
StringFieldCriterion loanFolder = new StringFieldCriterion();
loanFolder.FieldName = "Loan.LoanFolder";
loanFolder.Value = "Corr. Active";
loanFolder.MatchType = StringFieldMatchType.Exact;
loanFolder.Include = true;
QueryCriterion query = loanFolder.And(sellerID.And(allCondCount.And(tpoSubmitted.And(msPurchaseAC))));
//get loans
LoanIdentityList ids = session.Loans.Query(query);
Console.WriteLine("Found " + ids.Count + " loans");
if (ids.Count > 0)
{
//Found Loans build Excel file
FileInfo newFile = new FileInfo(@"output\" + newFileName);
if (newFile.Exists)
{
newFile.Delete();
newFile = new FileInfo(@"output\"+newFileName);
}
using (ExcelPackage package = new ExcelPackage(newFile))
{
//add a new worksheet
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Conditions");
//write header row
worksheet.Cells["A1"].Value = "Seller Name";
worksheet.Cells["B1"].Value = "Seller Loan Num";
worksheet.Cells["C1"].Value = "DH Loan Num";
worksheet.Cells["D1"].Value = "Borrower Name";
worksheet.Cells["E1"].Value = "Property State";
worksheet.Cells["F1"].Value = "Loan Purpose";
worksheet.Cells["G1"].Value = "Occupancy";
worksheet.Cells["H1"].Value = "Last Finished Milestone";
worksheet.Cells["I1"].Value = "Last Finished Milestone Date";
worksheet.Cells["J1"].Value = "Date Condition Added";
worksheet.Cells["K1"].Value = "Condition Status";
worksheet.Cells["L1"].Value = "Condition Type";
worksheet.Cells["M1"].Value = "Condition Title";
worksheet.Cells["N1"].Value = "Condition Details";
worksheet.Cells["O1"].Value = "Added By";
worksheet.Cells["P1"].Value = "For Intenal Use";
worksheet.Cells["Q1"].Value = "For External Use";
int row = 2;
foreach (LoanIdentity id in ids)
{
Loan loan = session.Loans.Open(id.Guid);
LogUnderwritingConditions conds = loan.Log.UnderwritingConditions;
//we only want the report to contain loans with conditions
if (conds.Count > 0)
{
var ClosedList = new List<ConditionStatus> { (ConditionStatus)7, (ConditionStatus)8, (ConditionStatus)12 };
Console.WriteLine("Found " + conds.Count + " total conditions in Loan: " + loan.LoanNumber);
if (LimitedSet.ToUpper().Equals("LIMITED"))
{
Console.WriteLine("Only Pulling Open Conditions");
}
foreach (UnderwritingCondition cond in conds)
{
if (LimitedSet.ToUpper().Equals("ALL") || (!LimitedSet.ToUpper().Equals("ALL") && !ClosedList.Contains(cond.Status)))
{
//fill in row details here
worksheet.Cells[row, 1].Value = loan.Fields["TPO.X14"];
worksheet.Cells[row, 2].Value = loan.Fields["CX.DH.SELLERLOANNUM"];
worksheet.Cells[row, 3].Value = loan.Fields["364"];
worksheet.Cells[row, 4].Value = loan.Fields["4002"] + ", " + loan.Fields["4000"];
worksheet.Cells[row, 5].Value = loan.Fields["14"];
worksheet.Cells[row, 6].Value = loan.Fields["19"];
worksheet.Cells[row, 7].Value = loan.Fields["1811"];
worksheet.Cells[row, 8].Value = loan.Fields["LOG.MS.LASTCOMPLETED"];
worksheet.Cells[row, 9].Value = loan.Fields["MS.STATUSDATE"].ToDate().Date;
worksheet.Cells[row, 10].Value = cond.DateAdded.Date;
worksheet.Cells[row, 11].Value = cond.Status.ToString();
worksheet.Cells[row, 12].Value = cond.PriorTo.ToString();
worksheet.Cells[row, 13].Value = cond.Title.ToString();
worksheet.Cells[row, 14].Value = cond.Description.ToString().Replace("\"", "'");
worksheet.Cells[row, 15].Value = cond.AddedBy.ToString();
worksheet.Cells[row, 16].Value = cond.ForInternalUse.ToString();
worksheet.Cells[row, 17].Value = cond.ForExternalUse.ToString();
row++;
}
}
row++;
}
}
//format range as table
var range = worksheet.Cells[1, 1, row - 2, 17];
var xltable = worksheet.Tables.Add(range, null);
xltable.TableStyle = TableStyles.Medium14;
//finish up worksheet
worksheet.Cells.AutoFitColumns(0);
worksheet.Column(9).Style.Numberformat.Format = "m/d/yyyy";
worksheet.Column(10).Style.Numberformat.Format = "m/d/yyyy";
worksheet.Column(14).Style.WrapText = true;
worksheet.PrinterSettings.RepeatRows = worksheet.Cells["1:1"];
worksheet.View.PageLayoutView = false;
worksheet.View.PageLayoutView = false;
//finish up package
package.Save();
}
}
}
}
catch (Exception ex)
{
Console.WriteLine("Unknown Error:");
Console.WriteLine("\r\n\r\n{0}", ex);
}
} // End ConditionReport
private String startApplication()
{
log.Info("Program running...");
String text = "";
//Investor Lock Date
DateFieldCriterion invLockEmpty = new DateFieldCriterion();
invLockEmpty.FieldName = "Fields.2220";
invLockEmpty.Value = DateFieldCriterion.EmptyDate;
invLockEmpty.MatchType = OrdinalFieldMatchType.Equals;
//non empty CTC date
DateFieldCriterion ctcNonEmpty = new DateFieldCriterion();
ctcNonEmpty.FieldName = "Fields.Log.MS.Date.Clear to Close";
ctcNonEmpty.Value = DateFieldCriterion.NonEmptyDate;
ctcNonEmpty.MatchType = OrdinalFieldMatchType.Equals;
//empty funding date
DateFieldCriterion fundDateEmpty = new DateFieldCriterion();
fundDateEmpty.FieldName = "Fields.Log.MS.Date.Funding";
fundDateEmpty.Value = DateFieldCriterion.EmptyDate;
fundDateEmpty.MatchType = OrdinalFieldMatchType.Equals;
//or
DateFieldCriterion fundDateNotEmpty = new DateFieldCriterion();
fundDateNotEmpty.FieldName = "Fields.Log.MS.Date.Funding";
fundDateNotEmpty.Value = DateFieldCriterion.NonEmptyDate;
fundDateNotEmpty.MatchType = OrdinalFieldMatchType.Equals;
StringFieldCriterion folderCri = new StringFieldCriterion();
folderCri.FieldName = "Loan.LoanFolder";
folderCri.Value = "My Pipeline";
folderCri.MatchType = StringFieldMatchType.Exact;
DateFieldCriterion purchDateEmpty = new DateFieldCriterion();
purchDateEmpty.FieldName = "Fields.Log.MS.Date.Purchased";
purchDateEmpty.Value = DateFieldCriterion.EmptyDate;
purchDateEmpty.MatchType = OrdinalFieldMatchType.Equals;
QueryCriterion fullQuery = folderCri.And((ctcNonEmpty.And(fundDateEmpty)));
StringList fields = new StringList();
Row row = new Row();
row.setHeader(true);
row.add("Investor");
fields.Add("Fields.2825");
row.add("Inv #");
fields.Add("Fields.2826");
row.add("Loan #");
fields.Add("Fields.364");
row.add("Borrower Name");
fields.Add("Fields.4002");
fields.Add("Fields.4000");
row.add("Address");
fields.Add("Fields.11");
row.add("Rate");
fields.Add("Fields.3");
row.add("Loan Amount");
fields.Add("Fields.1109");
row.add("Milestone");
fields.Add("Fields.Log.MS.CurrentMilestone");
row.add("Date Started");
fields.Add("Fields.Log.MS.Date.Started");
row.add("Base YSP");
fields.Add("Fields.2232");
row.add("Total Adj");
fields.Add("Fields.2273");
row.add("Net YSP");
fields.Add("Fields.2274");
row.add("Net SRP");
fields.Add("Fields.2276");
row.add("Total Rebate");
row.add("Locked");
fields.Add("Fields.2400");
row.add("Victoria Lock Date");
fields.Add("Fields.761");
fields.Add("Fields.2149");
row.add("Inv Lock Date");
fields.Add("Fields.2220");
row.add("Inv Lock Exp");
fields.Add("Fields.2222");
row.add("P/S/I");
fields.Add("Fields.1811");
row.add("Purpose");
fields.Add("Fields.19");
row.add("Processor");
fields.Add("Fields.362");
row.add("Loan Officer");
fields.Add("Fields.317");
report.Add(row);
SortCriterionList sortOrder = new SortCriterionList();
sortOrder.Add(new SortCriterion("Fields.Log.MS.CurrentMilestone", SortOrder.Descending));
sortOrder.Add(new SortCriterion("Fields.2149", SortOrder.Ascending));
LoanReportCursor results = Program.mySession.getSession().Reports.OpenReportCursor(fields, fullQuery, sortOrder);
Console.Out.WriteLine(results.ToString());
int count = results.Count;
log.Info("Total Files" + ": " + count);
text += "Total Files CTC, Not Funded: <b>" + count + "</b><br/><br/>";
//iterate through query and format
foreach (LoanReportData data in results)
{
Row line = new Row();
line.add(data["Fields.2825"].ToString());
line.add(data["Fields.2826"].ToString());
line.add(data["Fields.364"].ToString());
line.add((data["Fields.4002"].ToString() + " " + data["Fields.4000"].ToString()).ToUpper());
line.add(data["Fields.11"].ToString().ToUpper());
line.add(Convert.ToDouble(data["Fields.3"]).ToString("F3"));
line.add(Convert.ToInt32(data["Fields.1109"]).ToString("C"));
line.add(data["Fields.Log.MS.CurrentMilestone"].ToString());
line.add(Convert.ToDateTime(data["Fields.Log.MS.Date.Started"]).ToShortDateString());
line.add(Utility.toPercent(data["Fields.2232"]));
line.add(Utility.toPercent(data["Fields.2273"]));
line.add(Utility.toPercent(data["Fields.2274"]));
line.add(Utility.toPercent(data["Fields.2276"]));
line.add((Convert.ToDouble(data["Fields.2274"]) + Convert.ToDouble(data["Fields.2276"])).ToString("F3"));
line.add(data["Fields.2400"].ToString());
line.add(Utility.toShortDate(data["Fields.2149"]));
line.add(Utility.toShortDate(data["Fields.2220"]));
line.add(Utility.toShortDate(data["Fields.2222"]));
//occupancy
line.add(data["Fields.1811"].ToString().Substring(0, 1));
String purpose = data["Fields.19"].ToString();
if (purpose.Equals("Cash-Out Refinance"))
{
line.add("C/O Refi");
}
else if (purpose.Equals("NoCash-Out Refinance"))
{
line.add("No C/O Refi");
}
else if (purpose.Equals("Purchase"))
{
line.add("Purch");
}
else
{
line.add(purpose);
}
line.add(data["Fields.362"].ToString());
line.add(data["Fields.317"].ToString());
report.Add(line);
Console.Out.Write("."); //status bar
}
Console.Out.WriteLine("");
results.Close();
text += formatReport(report);
return(text);
}
private String startApplication()
{
log.Info("Program running...");
String text = "";
DateFieldCriterion cri = new DateFieldCriterion();
cri.FieldName = "Fields.Log.MS.Date.Clear to Close";
cri.Value = DateFieldCriterion.EmptyDate;
cri.MatchType = OrdinalFieldMatchType.Equals;
DateFieldCriterion cri2 = new DateFieldCriterion();
cri2.FieldName = "Fields.Log.MS.Date.Started";
cri2.Value = DateTime.Today.AddDays(-60); //last 60 days
cri2.MatchType = OrdinalFieldMatchType.GreaterThanOrEquals;
StringFieldCriterion folderCri = new StringFieldCriterion();
folderCri.FieldName = "Loan.LoanFolder";
folderCri.Value = "My Pipeline";
folderCri.MatchType = StringFieldMatchType.Exact;
QueryCriterion fullQuery = folderCri.And(cri.And(cri2));
StringList fields = new StringList();
Row row = new Row();
row.setHeader(true);
row.add("Milestone");
fields.Add("Fields.Log.MS.CurrentMilestone");
row.add("Date Started");
fields.Add("Fields.Log.MS.Date.Started");
row.add("Date Submitted");
fields.Add("Fields.Log.MS.Date.Submittal");
row.add("Loan #");
fields.Add("Fields.364");
row.add("Borrower Name");
fields.Add("Fields.4002");
fields.Add("Fields.4000");
row.add("Address");
fields.Add("Fields.11");
row.add("Loan Amount");
fields.Add("Fields.1109");
row.add("Purpose");
fields.Add("Fields.19");
row.add("Term");
fields.Add("Fields.4");
row.add("Rate");
fields.Add("Fields.3");
row.add("Locked Date");
fields.Add("Fields.761");
row.add("Processor");
fields.Add("Fields.362");
row.add("Loan Officer");
fields.Add("Fields.317");
report.Add(row);
SortCriterionList sortOrder = new SortCriterionList();
sortOrder.Add(new SortCriterion("Fields.Log.MS.Date.Started", SortOrder.Ascending));
LoanReportCursor results = Program.mySession.getSession().Reports.OpenReportCursor(fields, fullQuery, sortOrder);
Console.Out.WriteLine(results.ToString());
int count = results.Count;
log.Info("Total Files Not CTC " + ": " + count);
text += "Total Files Not CTC last 60 days: <b>" + count + "</b><br/><br/>";
//iterate through query and format
foreach (LoanReportData data in results)
{
Row line = new Row();
line.add(data["Fields.Log.MS.CurrentMilestone"].ToString());
line.add(Convert.ToDateTime(data["Fields.Log.MS.Date.Started"]).ToShortDateString());
line.add(Utility.toShortDate(data["Fields.Log.MS.Date.Submittal"]));
line.add(data["Fields.364"].ToString());
line.add(data["Fields.4002"].ToString().ToUpper() + ", " + data["Fields.4000"].ToString().ToUpper());
line.add(data["Fields.11"].ToString().ToUpper());
line.add(Convert.ToInt32(data["Fields.1109"]).ToString("C"));
line.add(data["Fields.19"].ToString());
line.add(Convert.ToInt32(data["Fields.4"]).ToString());
line.add(Convert.ToDouble(data["Fields.3"]).ToString("F3"));
line.add(Utility.toShortDate(data["Fields.761"]));
line.add(data["Fields.362"].ToString());
line.add(data["Fields.317"].ToString());
report.Add(line);
Console.Out.Write("."); //status bar
}
Console.Out.WriteLine("");
results.Close();
text += formatReport(report);
return(text);
}
private void button1_Click_1(object sender, EventArgs e)
{
btnGetActualField.Enabled = false;
if (encompassLoan == null)
{
var session = new Session();
session.Start(dfsServerAddress.Text, dfsUserName.Text, dfsPassword.Text);
var loanNumberCriterion =
new StringFieldCriterion {
FieldName = "Loan.LoanNumber", Value = dfsLoanNumber.Text
};
var queryCriterionLoanFolder = new StringFieldCriterion
{
FieldName = "Loan.LoanFolder",
Value = "(Trash)",
MatchType = StringFieldMatchType.Exact,
Include = false
};
var queryCriterion = loanNumberCriterion.And(queryCriterionLoanFolder);
using (var pipelineCursor = session.Loans.QueryPipeline(queryCriterion, PipelineSortOrder.None))
{
// Check cursor
if (pipelineCursor == null)
{
throw new InvalidOperationException(
$"Pipeline query yielded no results (loan number: '{dfsLoanNumber.Text}').");
}
// Check pipeline data
if (pipelineCursor.Count == 0)
{
pipelineCursor.Close();
throw new InvalidOperationException(
$"Pipeline cursor does not contain any data (loan number: '{dfsLoanNumber.Text}').");
}
var data = pipelineCursor.GetItem(0);
if (data == null)
{
pipelineCursor.Close();
throw new InvalidOperationException(
$"Pipeline data from cursor is null (loan number: '{dfsLoanNumber.Text}').");
}
// Open loan
var loanName = data.LoanIdentity.LoanName;
pipelineCursor.Close();
if (string.IsNullOrWhiteSpace(loanName))
{
throw new InvalidOperationException(
$"Loan name retrieved from pipeline data is null or empty (loan number: '{dfsLoanNumber.Text}').");
}
encompassLoan = session.Loans.Folders[data.LoanIdentity.LoanFolder].OpenLoan(loanName);
}
}
btnGetActualField.Enabled = true;
try
{
var field = encompassLoan.Fields[dfsFieldId.Text];
var encompassField = new EncompassFieldDescriptor
{
FieldID = field.ID,
ReadOnly = field.ReadOnly,
Description = field.Descriptor.Description,
Format = field.Descriptor.Format.ToString(),
};
actualFields.Add(encompassField);
grdFields.DataSource = null;
grdFields.DataSource = actualFields;
AdjustGridColumnWidth();
}
catch (Exception exception)
{
MessageBox.Show(exception.Message);
}
}
private String startApplication()
{
log.Info("Program running...");
String text = "";
DateFieldCriterion cri = new DateFieldCriterion();
cri.FieldName = "Fields.Log.MS.Date.Clear to Close";
cri.Value = DateFieldCriterion.EmptyDate;
cri.MatchType = OrdinalFieldMatchType.Equals;
DateFieldCriterion cri2 = new DateFieldCriterion();
cri2.FieldName = "Fields.Log.MS.Date.Started";
cri2.Value = DateTime.Today.AddDays(-1 * days); //last num days
cri2.MatchType = OrdinalFieldMatchType.GreaterThanOrEquals;
StringFieldCriterion folderCri = new StringFieldCriterion();
folderCri.FieldName = "Loan.LoanFolder";
folderCri.Value = "My Pipeline";
folderCri.MatchType = StringFieldMatchType.Exact;
QueryCriterion fullQuery = folderCri.And(cri.And(cri2));
StringList fields = new StringList();
Row header = new Row();
header.setHeader(true);
header.add("Milestone");
fields.Add("Fields.Log.MS.CurrentMilestone");
header.add("Date Started");
fields.Add("Fields.Log.MS.Date.Started");
header.add("Date Submitted");
fields.Add("Fields.Log.MS.Date.Submittal");
header.add("Loan #");
fields.Add("Fields.364");
header.add("Borrower Name");
fields.Add("Fields.4002");
fields.Add("Fields.4000");
header.add("Address");
fields.Add("Fields.11");
header.add("Loan Amount");
fields.Add("Fields.1109");
header.add("Purpose");
fields.Add("Fields.19");
header.add("Term");
fields.Add("Fields.4");
header.add("Rate");
fields.Add("Fields.3");
header.add("Locked Date");
fields.Add("Fields.761");
header.add("Processor");
fields.Add("Fields.362");
header.add("Loan Officer");
fields.Add("Fields.317");
header.add("Milestone Notes");
fields.Add("Fields.Log.MS.Stage");
fields.Add("Fields.Log.MS.Comments.Approval");
fields.Add("Fields.Log.MS.Comments.Clear to Close");
fields.Add("Fields.Log.MS.Comments.Completion");
fields.Add("Fields.Log.MS.Comments.Cond Approval");
fields.Add("Fields.Log.MS.Comments.Docs Drawn");
fields.Add("Fields.Log.MS.Comments.Docs Signing");
fields.Add("Fields.Log.MS.Comments.Funding");
fields.Add("Fields.Log.MS.Comments.Processing");
fields.Add("Fields.Log.MS.Comments.Purchased");
fields.Add("Fields.Log.MS.Comments.Ready for Docs");
fields.Add("Fields.Log.MS.Comments.Resubmittal");
fields.Add("Fields.Log.MS.Comments.Sent to Title");
fields.Add("Fields.Log.MS.Comments.Shipping");
fields.Add("Fields.Log.MS.Comments.Started");
fields.Add("Fields.Log.MS.Comments.Submittal");
report.Add(header);
SortCriterionList sortOrder = new SortCriterionList();
sortOrder.Add(new SortCriterion("Fields.362", SortOrder.Ascending));
sortOrder.Add(new SortCriterion("Fields.Log.MS.Date.Started", SortOrder.Descending));
LoanReportCursor results = Program.mySession.getSession().Reports.OpenReportCursor(fields, fullQuery, sortOrder);
//Console.Out.WriteLine(results.ToString());
int count = results.Count;
log.Info("Total Files" + ": " + count);
text += "Total Files:<b>" + count + "</b> active last " + days + " days<br/><br/>";
String currProcessor = "";//"null" string
count = 0;
//iterate through query and format
foreach (LoanReportData data in results)
{
if (!currProcessor.Equals(data["Fields.362"].ToString()))
{
Row subheader = new Row();
subheader.setHeader(true);
subheader.add("Processor: ");
subheader.add(currProcessor);
subheader.add("Count: " + count);
report.Add(subheader);
text += formatReport(report);
text += "<br/><br/>";
// reset
count = 0;
report.Clear();
currProcessor = data["Fields.362"].ToString();
report.Add(header);
}
count++;
Row line = new Row();
line.add(data["Fields.Log.MS.CurrentMilestone"].ToString());
line.add(Convert.ToDateTime(data["Fields.Log.MS.Date.Started"]).ToShortDateString());
if (Math.Ceiling(DateTime.Now.Subtract(Convert.ToDateTime(data["Fields.Log.MS.Date.Started"])).TotalDays) > 60)
{
line.setWarn(true);
}
line.add(Utility.toShortDate(data["Fields.Log.MS.Date.Submittal"]));
line.add(data["Fields.364"].ToString());
line.add(data["Fields.4002"].ToString().ToUpper() + ", " + data["Fields.4000"].ToString().ToUpper());
line.add(data["Fields.11"].ToString().ToUpper());
line.add(Convert.ToInt32(data["Fields.1109"]).ToString("C"));
line.add(data["Fields.19"].ToString());
line.add(Convert.ToInt32(data["Fields.4"]).ToString());
line.add(Convert.ToDouble(data["Fields.3"]).ToString("F3"));
line.add(Utility.toShortDate(data["Fields.761"]));
line.add(data["Fields.362"].ToString());
line.add(data["Fields.317"].ToString());
//milestone comments
string nextMilestone = data["Fields.Log.MS.Stage"].ToString();
//string notes = "";
StringList log = new StringList();
log.Add(data["Fields.Log.MS.Comments.Started"].ToString());
log.Add(data["Fields.Log.MS.Comments.Processing"].ToString());
log.Add(data["Fields.Log.MS.Comments.Submittal"].ToString());
log.Add(data["Fields.Log.MS.Comments.Cond Approval"].ToString());
log.Add(data["Fields.Log.MS.Comments.Resubmittal"].ToString());
log.Add(data["Fields.Log.MS.Comments.Clear to Close"].ToString());
log.Add(data["Fields.Log.MS.Comments.Ready for Docs"].ToString());
log.Add(data["Fields.Log.MS.Comments.Docs Drawn"].ToString());
log.Add(data["Fields.Log.MS.Comments.Docs Signing"].ToString());
log.Add(data["Fields.Log.MS.Comments.Funding"].ToString());
log.Add(data["Fields.Log.MS.Comments.Purchased"].ToString());
log.Add(data["Fields.Log.MS.Comments.Shipping"].ToString());
log.Add(data["Fields.Log.MS.Comments.Completion"].ToString());
String notes = "";
foreach (string l in log)
{
if (l.Trim().Length != 0)
{
using (StringReader reader = new StringReader(l.ToString()))
{
string li = "";
while ((li = reader.ReadLine()) != null)
{
notes += li.Trim() + "<br/>";
}
}
}
}
line.add(notes);
if (Program.debug)
{
line.add(data.Guid);
}
report.Add(line);
Console.Out.Write("."); //status bar
}
Console.Out.WriteLine("");
results.Close();
//text += formatReport(report);
return(text);
}
private void startApplication()
{
log.Debug("Program running...");
//setup folder query
StringFieldCriterion folderCri = selectLoanFolder();
//execute encompass query
LoanReportCursor results = encompassQueryBuilder(folderCri);
int total = results.Count;
int count = 0;
log.Info("Total Files: " + total);
//query list for mySql
//List<MySqlCommand> queries = new List<MySqlCommand>();
log.Debug("connecting to MySQL...");
db_connect connection = new db_connect();
connection.connect();
log.Debug("connected to MySQL");
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = connection.getConnection();
foreach (LoanReportData data in results)
{
Dictionary <String, String> map = dbQueryBuilder(data);
/*cmd.CommandText = string.Format(
* "INSERT INTO loans(guid, investor, investorNum, createdAt, b1_lname, b1_fname, loanAmt, loanNum, fundedDate, processor, loan_officer, purchasedDate) " +
* "values(@v1, @v2, @v3, @createdAt, @v4, @v5, @v6, @v7, @v8, @v9, @v10, " +
* "@v11, @currMilestone) " +
* "ON DUPLICATE KEY UPDATE guid=@v1, investor=@v2, investorNum=@v3, updatedAt=@updatedAt, b1_lname=@v4, b1_fname=@v5, "+
* "loanAmt=@v6, loanNum=@v7, fundedDate=@v8, processor=@v9, loan_officer=@v10, " +
* "purchasedDate=@v11"
* );*/
var now = timestamp.ToString("yyyy - MM - dd HH: mm:ss");
StringBuilder insert = new StringBuilder();
insert.Append("INSERT INTO loans(guid, createdAt, loanFolder ");
//cols
foreach (String key in map.Keys)
{
insert.Append(", " + key);
}
//values
insert.Append(") ");
StringBuilder values = new StringBuilder();
values.Append("VALUES('" + data.Guid + "', '" + now + "', '" + folderCri.Value + "'");
foreach (String key in map.Keys)
{
if (map[key] != null)
{
values.Append(", '" + MySqlHelper.EscapeString(" " + map[key]) + "'");
}
else
{
values.Append(", NULL");
}
}
values.Append(") ");
StringBuilder update2 = new StringBuilder();
update2.Append("ON DUPLICATE KEY UPDATE updatedAt='" + now + "', loanFolder='" + folderCri.Value + "'");
foreach (String key in map.Keys)
{
if (map[key] != null)
{
update2.Append(", " + key + "='" + MySqlHelper.EscapeString(" " + map[key]) + "'");
}
else
{
update2.Append(", " + key + "=NULL");
}
}
//mysql command
cmd.CommandText = insert.ToString() + values.ToString() + update2.ToString();
/* cmd.Parameters.Clear();
* cmd.Parameters.AddWithValue("@v1", data.Guid);
* cmd.Parameters.AddWithValue("@v2", map["investor"]);
* cmd.Parameters.AddWithValue("@v3", map["investorNum"]);
* cmd.Parameters.AddWithValue("@createdAt", timestamp.ToString("yyyy-MM-dd HH:mm:ss"));
* cmd.Parameters.AddWithValue("@updatedAt", timestamp.ToString("yyyy-MM-dd HH:mm:ss"));
* cmd.Parameters.AddWithValue("@v4", map["b1_lname"]);
* cmd.Parameters.AddWithValue("@v5", map["b1_fname"]);
* cmd.Parameters.AddWithValue("@v6", map["loanAmt"]);
* cmd.Parameters.AddWithValue("@v7", map["loanNum"]);
* cmd.Parameters.AddWithValue("@v8", map["fundedDate"]);
* cmd.Parameters.AddWithValue("@v9", map["processor"]);
* cmd.Parameters.AddWithValue("@v10", map["loanOfficer"]);
* cmd.Parameters.AddWithValue("@v11", map["purchasedDate"]);
* cmd.Prepare();*/
cmd.ExecuteNonQuery();
//status output
count++;
if (count % 10 == 0)
{
Console.Write("\r{0} ", "writing: " + count + "/" + total);
}
}
Console.Out.WriteLine("");
results.Close();
TimeSpan time = DateTime.Now.Subtract(timestamp);
/*connection.addLog("db_update", "updated: " + folderCri.Value + total + " rows in " +
* String.Format("{0} min {1}.{2} sec"
* , time.Minutes
* , time.Seconds
* , time.Milliseconds) );*/
connection.close();
log.Info("Finished updating db in " +
String.Format("{0} min {1}.{2} sec"
, time.Minutes
, time.Seconds
, time.Milliseconds));
}
