// Generates the number of loans accessible by each Encompass user static Dictionary <string, int> generateLoanAccessInformation(Session session) { // Create the result set dictionary Dictionary <string, int> userLoanAccessCounts = new Dictionary <string, int>(); // Get the list of all users from the server UserList users = session.Users.GetAllUsers(); foreach (User user in users) { try { // Impersonate this user session.ImpersonateUser(user.ID); // Retrieve a loan cursor with all loans (i.e. no filter applied). We pass an empty // field list since we don't need any actual field data. StringList fields = new StringList(); using (LoanReportCursor cursor = session.Reports.OpenReportCursor(fields, null)) { // The number of loans in the cursor is the total number this user can access userLoanAccessCounts[user.ID] = cursor.Count; // Show a tick on the console Console.Error.Write("."); } } catch (Exception ex) { Console.Error.WriteLine("Failed to retrieve loan count for user {0}: {1}", user.ID, ex); } finally { // End the impersonation session.RestoreIdentity(); } } // Write an endline to the error console Console.Error.WriteLine(); return(userLoanAccessCounts); }
public static IDictionary <string, DateTime> LoansAndLastModifiedReport(Session session, QueryCriterion criterion) { IDictionary <string, DateTime> loans = new Dictionary <string, DateTime>(); StringList fields = new StringList(); fields.Add("Loan.LastModified"); try { LoanReportCursor results = session.Reports.OpenReportCursor(fields, criterion); foreach (LoanReportData d in results) { loans.Add(d.Guid, (DateTime)d["Loan.LastModified"]); } results.Close(); } catch { //noop } return(loans); }
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 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 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 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 String startApplication() { log.Info("Program running..."); String text = ""; DateFieldCriterion cri = new DateFieldCriterion(); cri.FieldName = "Fields.Log.MS.Date.Funding"; cri.Value = DateTime.Now; cri.Precision = DateFieldMatchPrecision.Day; QueryCriterion fullQuery = cri; 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"); LoanReportCursor results = Program.mySession.getSession().Reports.OpenReportCursor(fields, fullQuery); //LoanIdentityList ids = session.Loans.Query(fullQuery); int count = results.Count; log.Info("Total Files Funded " + cri.Value.ToShortDateString() + ": " + count); text += "Total Files Funded on " + cri.Value.ToShortDateString() + ": <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("Loan Amount"); row.add("Processor"); row.add("Loan Officer"); 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()); report.Add(line); Console.Out.Write("."); //status bar } Console.Out.WriteLine(""); results.Close(); text += formatReport(report); return(text); }
private LoanReportCursor encompassQueryBuilder(QueryCriterion folderCri) { 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.11"); //prop address fields.Add("Fields.12"); //prop city fields.Add("Fields.15"); //prop zip fields.Add("Fields.362"); //processor fields.Add("Fields.317"); //loan officer fields.Add("Fields.Log.MS.CurrentMilestone"); fields.Add("Fields.Log.MS.Date.Started"); fields.Add("Fields.Log.MS.Date.Submittal"); fields.Add("Fields.Log.MS.Date.Clear to Close"); fields.Add("Fields.Log.MS.Date.Docs Drawn"); fields.Add("Fields.Log.MS.Date.Docs Signing"); fields.Add("Fields.Log.MS.Date.Funding"); fields.Add("Fields.Log.MS.Date.Purchased"); fields.Add("Fields.Log.MS.Date.Completion"); 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.Shipping"); fields.Add("Fields.Log.MS.Comments.Started"); fields.Add("Fields.Log.MS.Comments.Submittal"); fields.Add("Fields.19"); //loan purpose fields.Add("Fields.4"); //loan term fields.Add("Fields.1811"); //occupancy fields.Add("Fields.3"); //interest rate fields.Add("Fields.1393"); //current status fields.Add("Fields.761"); //locked date fields.Add("Fields.2149"); //victoria lock date fields.Add("Fields.2220"); //investor lock date fields.Add("Fields.2222"); //investor lock exp date fields.Add("Fields.2287"); //investor lock type fields.Add("Fields.2232"); //base YSP fields.Add("Fields.2273"); //total adjustments fields.Add("Fields.2277"); //net ysp fields.Add("Fields.2276"); //net srp fields.Add("Fields.SERVICE.X8"); //servicing status fields.Add("Fields.682"); //first payment date fields.Add("Fields.3514"); //1st payment to investor fields.Add("Fields.SERVICE.X9"); //last statement printed date // fields.Add("Fields.SERVICE.X10");//Interm Service Printed fields.Add("Fields.SERVICE.X39"); //number of payments LoanReportCursor results = Program.mySession.getSession().Reports.OpenReportCursor(fields, folderCri); return(results); //LoanIdentityList ids = session.Loans.Query(fullQuery); }
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)); }