private static QueryCriterion CreateLoanCustomFilter(ReportSettings ReportSettings) { QueryCriterion queryCriterion = ReportSettings.ToQueryCriterion(); switch (ReportSettings.LoanFilterType) { case ReportLoanFilterType.Role: QueryCriterion criterion1 = (QueryCriterion) new BinaryOperation(BinaryOperator.And, (QueryCriterion) new OrdinalValueCriterion("LoanAssociateUser.RoleID", (object)ReportSettings.LoanFilterRoleId), (QueryCriterion) new StringValueCriterion("LoanAssociateUser.UserID", ReportSettings.LoanFilterUserInRole)); queryCriterion = queryCriterion != null?queryCriterion.And(criterion1) : criterion1; break; case ReportLoanFilterType.Organization: QueryCriterion criterion2 = (QueryCriterion) new OrdinalValueCriterion("AssociateUser.org_id", (object)ReportSettings.LoanFilterOrganizationId); if (ReportSettings.LoanFilterIncludeChildren) { criterion2 = criterion2.Or((QueryCriterion) new XRefValueCriterion("Associateuser.org_id", "org_descendents.descendent", (QueryCriterion) new OrdinalValueCriterion("org_descendents.oid", (object)ReportSettings.LoanFilterOrganizationId))); } queryCriterion = queryCriterion != null?queryCriterion.And(criterion2) : criterion2; break; case ReportLoanFilterType.UserGroup: QueryCriterion criterion3 = (QueryCriterion) new OrdinalValueCriterion("AssociateGroup.GroupID", (object)ReportSettings.LoanFilterUserGroupId); queryCriterion = queryCriterion != null?queryCriterion.And(criterion3) : criterion3; break; } if (ReportSettings.DynamicQueryCriterion != null) { queryCriterion = queryCriterion != null?queryCriterion.And(ReportSettings.DynamicQueryCriterion) : ReportSettings.DynamicQueryCriterion; } return(queryCriterion); }
public ActionResult Search(WebOrderRequestSearchViewModel webOrderRequestSearchViewModel) { GRINGlobalService grinGlobalService = new GRINGlobalService(this.AuthenticatedUserSession.Environment); Models.Query query = new Models.Query(); try { //if (webOrderRequestSearchViewModel.SelectedStatusCode != "ANY") //{ // QueryCriterion queryCriterion = new QueryCriterion { FieldName = "wor.status_code", FieldValue = webOrderRequestSearchViewModel.SelectedStatusCode, SearchOperatorCode = "=", DataType = "NVARCHAR" }; // query.QueryCriteria.Add(queryCriterion); //} //if (webOrderRequestSearchViewModel.SelectedTimeFrameCode > 0) //{ // QueryCriterion queryCriterion = new QueryCriterion { FieldName = "time_frame_code", FieldValue = webOrderRequestSearchViewModel.SelectedTimeFrameCode.ToString(), SearchOperatorCode = "=", DataType = "INT" }; // query.QueryCriteria.Add(queryCriterion); //} if (!String.IsNullOrEmpty(webOrderRequestSearchViewModel.RequestorEmailAddress)) { QueryCriterion queryCriterion = new QueryCriterion { FieldName = "wc.email", FieldValue = webOrderRequestSearchViewModel.RequestorEmailAddress.ToString(), SearchOperatorCode = "LIKE", DataType = "NVARCHAR" }; query.QueryCriteria.Add(queryCriterion); } if (!String.IsNullOrEmpty(webOrderRequestSearchViewModel.RequestorFirstName)) { QueryCriterion queryCriterion = new QueryCriterion { FieldName = "wc.first_name", FieldValue = webOrderRequestSearchViewModel.RequestorFirstName.ToString(), SearchOperatorCode = "LIKE", DataType = "NVARCHAR" }; query.QueryCriteria.Add(queryCriterion); } if (!String.IsNullOrEmpty(webOrderRequestSearchViewModel.RequestorLastName)) { QueryCriterion queryCriterion = new QueryCriterion { FieldName = "wc.last_name", FieldValue = webOrderRequestSearchViewModel.RequestorLastName, SearchOperatorCode = "LIKE", DataType = "NVARCHAR" }; query.QueryCriteria.Add(queryCriterion); } // Re-initialize main index view model, adding search results. webOrderRequestSearchViewModel.WebOrderRequests = grinGlobalService.SearchWebOrderRequests(query); webOrderRequestSearchViewModel.Statuses = grinGlobalService.GetWebOrderRequestStatuses(); webOrderRequestSearchViewModel.IntendedUseCodes = new SelectList(grinGlobalService.GetWebOrderRequestIntendedUseCodes(), "Name", "Description"); return(View("~/Views/GRINGlobal/WebOrder/Index.cshtml", webOrderRequestSearchViewModel)); } catch (Exception ex) { log.Error(ex.Message + ex.StackTrace); return(View("~/Views/Error/_Error.cshtml")); } }
public PartialViewResult _Search(string statusCode, int timeFrameCode, string requestorEmail, string requestorFirstName, string requestorLastName, string intendedUseCode, string selectedDateRange) { WebOrderRequestListViewModel webOrderRequestListViewModel = new WebOrderRequestListViewModel(); GRINGlobalService grinGlobalService = new GRINGlobalService(this.AuthenticatedUserSession.Environment); Models.Query query = new Models.Query(); try { if (!String.IsNullOrEmpty(requestorEmail)) { QueryCriterion queryCriterion = new QueryCriterion { FieldName = "wc.email", FieldValue = requestorEmail, SearchOperatorCode = "LIKE", DataType = "NVARCHAR" }; query.QueryCriteria.Add(queryCriterion); } if (!String.IsNullOrEmpty(requestorFirstName)) { QueryCriterion queryCriterion = new QueryCriterion { FieldName = "wc.first_name", FieldValue = requestorFirstName, SearchOperatorCode = "LIKE", DataType = "NVARCHAR" }; query.QueryCriteria.Add(queryCriterion); } if (!String.IsNullOrEmpty(requestorLastName)) { QueryCriterion queryCriterion = new QueryCriterion { FieldName = "wc.last_name", FieldValue = requestorLastName, SearchOperatorCode = "LIKE", DataType = "NVARCHAR" }; query.QueryCriteria.Add(queryCriterion); } if (!String.IsNullOrEmpty(intendedUseCode)) { QueryCriterion queryCriterion = new QueryCriterion { FieldName = "wor.intended_use_code", FieldValue = intendedUseCode, SearchOperatorCode = "LIKE", DataType = "NVARCHAR" }; query.QueryCriteria.Add(queryCriterion); } webOrderRequestListViewModel.WebOrderRequests = grinGlobalService.SearchWebOrderRequests(query); return(PartialView("~/Views/GRINGlobal/WebOrder/_List.cshtml", webOrderRequestListViewModel)); } catch (Exception ex) { log.Error(ex.Message + ex.StackTrace); return(PartialView("~/Views/Error/_Error.cshtml")); } }
public static IList <string> LoansLastModifiedBetween(Session session, QueryCriterion criterion) { IList <string> loans = new List <string>(); try { LoanIdentityList loanList = session.Loans.Query(criterion); foreach (LoanIdentity id in loanList) { loans.Add(id.Guid); } } catch { //noop } return(loans); }
public static IList<string> LoansLastModifiedBetween(Session session, QueryCriterion criterion) { IList<string> loans = new List<string>(); try { LoanIdentityList loanList = session.Loans.Query(criterion); foreach (LoanIdentity id in loanList) { loans.Add(id.Guid); } } catch { //noop } return loans; }
public static IList <string> LoansLastModifiedBetween(Session session, DateTime start, DateTime end) { DateFieldCriterion startDate = new DateFieldCriterion(); startDate.FieldName = LAST_MODIFIED_FIELD; startDate.MatchType = OrdinalFieldMatchType.GreaterThanOrEquals; startDate.Value = start; startDate.Precision = DateFieldMatchPrecision.Day; DateFieldCriterion endDate = new DateFieldCriterion(); endDate.FieldName = LAST_MODIFIED_FIELD; endDate.MatchType = OrdinalFieldMatchType.LessThan; endDate.Value = end; endDate.Precision = DateFieldMatchPrecision.Day; QueryCriterion joinCriterion = startDate.And(endDate); return(LoansLastModifiedBetween(session, joinCriterion)); }
public static IDictionary <string, DateTime> LoansAndLastModifiedPipeline(Session session, QueryCriterion criterion) { IDictionary <string, DateTime> loans = new Dictionary <string, DateTime>(); try { PipelineCursor cursor = session.Loans.QueryPipeline(criterion, PipelineSortOrder.None); foreach (PipelineData p in cursor) { loans.Add(p.LoanIdentity.Guid, (DateTime)p["LastModified"]); } cursor.Close(); } catch { //noop } return(loans); }
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() { 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 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 = 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 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() { 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); }
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; }
public static IDictionary<string, DateTime> LoansAndLastModifiedPipeline(Session session, QueryCriterion criterion) { IDictionary<string, DateTime> loans = new Dictionary<string, DateTime>(); try { PipelineCursor cursor = session.Loans.QueryPipeline(criterion, PipelineSortOrder.None); foreach (PipelineData p in cursor) { loans.Add(p.LoanIdentity.Guid, (DateTime) p["LastModified"]); } cursor.Close(); } catch { //noop } return loans; }
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.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 = ""; //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 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); }