API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Dec 8th, 2016
754
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 63.17 KB | None | 0 0
raw download clone embed print report
  1. var stateCodes = [["Alabama", "al", "49802"],
  2. ["Alaska", "ak", "76816"],
  3. ["Arizona", "az", "55371"],
  4. ["Arkansas", "ar", "47047"],
  5. ["California", "ca", "63544"],
  6. ["Colorado", "co", "66870"],
  7. ["Connecticut", "ct", "73424"],
  8. ["Delaware", "de", "62771"],
  9. ["District of Columbia", "dc", "84730"],
  10. ["Florida", "fl", "52125"],
  11. ["Georgia", "ga", "53127"],
  12. ["Hawaii", "hi", "66588"],
  13. ["Idaho", "id", "51499"],
  14. ["Illinois", "il", "61450"],
  15. ["Indiana", "in", "53050"],
  16. ["Iowa", "ia", "59977"],
  17. ["Kansas", "ks", "58548"],
  18. ["Kentucky", "ky", "47977"],
  19. ["Louisiana", "la", "49159"],
  20. ["Maine", "me", "54351"],
  21. ["Maryland", "md", "76248"],
  22. ["Massachusetts", "ma", "69395"],
  23. ["Michigan", "mi", "53450"],
  24. ["Minnesota", "mn", "65067"],
  25. ["Mississippi", "ms", "44523"],
  26. ["Missouri", "mo", "52188"],
  27. ["Montana", "mt", "55262"],
  28. ["Nebraska", "ne", "60334"],
  29. ["Nevada", "nv", "56071"],
  30. ["New Hampshire", "nh", "67677"],
  31. ["New Jersey", "nj", "70933"],
  32. ["New Mexico", "nm", "52166"],
  33. ["New York", "ny", "60502"],
  34. ["North Carolina", "nc", "52151"],
  35. ["North Dakota", "nd", "63989"],
  36. ["Ohio", "oh", "53960"],
  37. ["Oklahoma", "ok", "52279"],
  38. ["Oregon", "or", "56398"],
  39. ["Pennsylvania", "pa", "56505"],
  40. ["Rhode Island", "ri", "62724"],
  41. ["South Carolina", "sc", "50493"],
  42. ["South Dakota", "sd", "57630"],
  43. ["Tennessee", "tn", "49552"],
  44. ["Texas", "tx", "57174"],
  45. ["Utah", "ut", "58110"],
  46. ["Vermont", "vt", "61812"],
  47. ["Virginia", "va", "66585"],
  48. ["Washington", "wa", "65215"],
  49. ["West Virginia", "wv", "46103"],
  50. ["Wisconsin", "wi", "59064"],
  51. ["Wyoming", "wy", "64902"]];
  52.  
  53. var fontSizeMapping = {
  54. 11: 220,
  55. 12: 240,
  56. 13: 260,
  57. 14: 280,
  58. 15: 300,
  59. 16: 320
  60. };
  61.  
  62. $.ig.loader({
  63. scriptPath: "http://cdn-na.infragistics.com/igniteui/2015.2/latest/js/",
  64. cssPath: "http://cdn-na.infragistics.com/igniteui/2015.2/latest/css/",
  65. resources: 'modules/infragistics.util.js,' +
  66. 'modules/infragistics.documents.core.js,' +
  67. 'modules/infragistics.excel.js'
  68. });
  69.  
  70. var CreditReportExtractor = {
  71. scores: JSON.parse(localStorage.getItem("scores") || JSON.stringify({})),
  72.  
  73. personal: JSON.parse(localStorage.getItem("personal") || JSON.stringify({})),
  74.  
  75. cluster: JSON.parse(localStorage.getItem("cluster") || JSON.stringify({bank:[], closed: [], installment: []})),
  76.  
  77. createWorkbook: function(callback) {
  78. console.log("Creating workbook...");
  79.  
  80. var self = this,
  81. personName = self.personal.name,
  82. workbook = new $.ig.excel.Workbook($.ig.excel.WorkbookFormat.excel2007),
  83. calculatorWorksheet = workbook.worksheets().add("Calculator"),
  84. verificationCallWorksheet = workbook.worksheets().add("Verification Call"),
  85. summaryWorksheet = workbook.worksheets().add("Summary"),
  86. stateCodesWorksheet = workbook.worksheets().add("State Codes");
  87.  
  88. calculatorWorksheet = self.createCalculatorWorksheet(calculatorWorksheet);
  89. verificationCallWorksheet = self.createVerificationCallWorksheet(verificationCallWorksheet);
  90. summaryWorksheet = self.createSummaryWorksheet(summaryWorksheet);
  91. stateCodesWorksheet = self.createStateCodesWorksheet(stateCodesWorksheet);
  92.  
  93. workbook.save({ type: 'blob' }, function(data) {
  94. console.log(data);
  95. saveAs(data, personName.substr(0, 30) + ".xlsx");
  96.  
  97. if (typeof callback === "function")
  98. callback();
  99. },
  100. function(error) {
  101. console.log(error);
  102. });
  103. },
  104.  
  105. createCalculatorWorksheet: function(worksheet) {
  106. var self = this,
  107. bankAccounts = self.cluster.bank,
  108. retailCards = self.cluster.retail,
  109. closedAccounts = self.cluster.closed,
  110. authorized = self.cluster.authorized,
  111. installmentAccounts = self.cluster.installment,
  112. fraud = self.fraud,
  113. curRowIndex = 0,
  114. personal = self.personal,
  115. publicRecords = self.public,
  116. inquiries = self.inquiries,
  117. formattedString = new $.ig.excel.FormattedString( "Formatted String" ),
  118. setCurrencyModeToCell = function(cell, value) {
  119. balanceCellFormat = cell.cellFormat();
  120. balanceCellFormat.formatString("$0");
  121. cell.value(parseInt(value));
  122. },
  123. getAccountStatus = function(remarkString) {
  124. if (remarkString.toLowerCase().indexOf("paid") > -1)
  125. return "Paid";
  126. else if (remarkString.toLowerCase().indexOf("closed") > -1)
  127. return "Closed";
  128. else
  129. return "Paid";
  130. },
  131. setTitleModeToCell = function(cell, value) {
  132. cellFormat = cell.cellFormat();
  133. cellFormat.alignment($.ig.excel.HorizontalCellAlignment.center);
  134. cellFormat.font().bold(true);
  135. cellFormat.font().height(fontSizeMapping['12']);
  136. cell.value(value);
  137. },
  138. setTableHeadModeToCell = function(cell, value) {
  139. format = cell.cellFormat();
  140. format.alignment($.ig.excel.HorizontalCellAlignment.left);
  141. format.font().bold(true);
  142. format.fill($.ig.excel.CellFill.createSolidFill('#8DB4E3'));
  143. format.topBorderColorInfo(new $.ig.excel.WorkbookColorInfo('#000000'));
  144. format.rightBorderColorInfo(new $.ig.excel.WorkbookColorInfo('#000000'));
  145. format.bottomBorderColorInfo(new $.ig.excel.WorkbookColorInfo('#000000'));
  146. format.leftBorderColorInfo(new $.ig.excel.WorkbookColorInfo('#000000'));
  147. cell.value(value);
  148. },
  149. fillGreenToCell = function(cell) {
  150. format = cell.cellFormat();
  151. format.fill($.ig.excel.CellFill.createSolidFill('#DBEEF3'));
  152. format.topBorderColorInfo(new $.ig.excel.WorkbookColorInfo('#000000'));
  153. format.rightBorderColorInfo(new $.ig.excel.WorkbookColorInfo('#000000'));
  154. format.bottomBorderColorInfo(new $.ig.excel.WorkbookColorInfo('#000000'));
  155. format.leftBorderColorInfo(new $.ig.excel.WorkbookColorInfo('#000000'));
  156. return cell;
  157. },
  158. fillYellowToCell = function(cell) {
  159. format = cell.cellFormat();
  160. format.fill($.ig.excel.CellFill.createSolidFill('#FFFF00'));
  161. format.topBorderColorInfo(new $.ig.excel.WorkbookColorInfo('#000000'));
  162. format.rightBorderColorInfo(new $.ig.excel.WorkbookColorInfo('#000000'));
  163. format.bottomBorderColorInfo(new $.ig.excel.WorkbookColorInfo('#000000'));
  164. format.leftBorderColorInfo(new $.ig.excel.WorkbookColorInfo('#000000'));
  165. return cell;
  166. },
  167. drawBorderToCells = function(r1, c1, r2, c2) {
  168. for (var i = r1; i < r2 + 1; i ++) {
  169. for (var j = c1; j < c2 + 1; j ++) {
  170. format = worksheet.rows(i).cells(j).cellFormat();
  171.  
  172. format.topBorderColorInfo(new $.ig.excel.WorkbookColorInfo('#000000'));
  173. format.rightBorderColorInfo(new $.ig.excel.WorkbookColorInfo('#000000'));
  174. format.bottomBorderColorInfo(new $.ig.excel.WorkbookColorInfo('#000000'));
  175. format.leftBorderColorInfo(new $.ig.excel.WorkbookColorInfo('#000000'));
  176. }
  177. }
  178. },
  179. getLatePaymentCommonValue = function(param) {
  180. var result = {
  181. 30: "",
  182. 60: "",
  183. 90: ""
  184. },
  185. indArr = ['30', '60', '90'],
  186. indTempArr = ['TransUnion', 'Experian', 'Equifax'];
  187.  
  188. for (var i = 0; i < indArr.length; i++) {
  189. for (var j = 0; j < indTempArr.length; j++) {
  190. if (param[indTempArr[j]][indArr[i]])
  191. result[indArr[i]] = param[indTempArr[j]][indArr[i]];
  192. }
  193. }
  194.  
  195. return result;
  196. },
  197. createInqueryTable = function(rowIndex, inquiries) {
  198. var inqueryTableStartIndex = rowIndex,
  199. tempIndex = rowIndex;
  200.  
  201. setTableHeadModeToCell(worksheet.rows(tempIndex).cells(8), "Inquiries");
  202. setTableHeadModeToCell(worksheet.rows(tempIndex).cells(9), "Date");
  203. setTableHeadModeToCell(worksheet.rows(tempIndex).cells(10), "Experian");
  204. setTableHeadModeToCell(worksheet.rows(tempIndex).cells(11), "Equifax");
  205. setTableHeadModeToCell(worksheet.rows(tempIndex).cells(12), "Transunion");
  206. setTableHeadModeToCell(worksheet.rows(tempIndex).cells(13), "Type of Inquiry");
  207. setTableHeadModeToCell(worksheet.rows(tempIndex).cells(14), "60 Days Late");
  208. setTableHeadModeToCell(worksheet.rows(tempIndex).cells(15), "90 Days Late");
  209. setTableHeadModeToCell(worksheet.rows(tempIndex).cells(16), "120 Days Late");
  210. tempIndex++;
  211.  
  212. for(var i = 0; i < inquiries.length; i++) {
  213. item = inquiries[i];
  214. worksheet.rows(tempIndex).cells(8).value(item.name);
  215. worksheet.rows(tempIndex).cells(9).value(item.date);
  216.  
  217. switch(item.creditBureau) {
  218. case "Experian":
  219. worksheet.rows(tempIndex).cells(10).value("X");
  220. worksheet.rows(tempIndex).cells(10).cellFormat().alignment($.ig.excel.HorizontalCellAlignment.center);
  221. break;
  222.  
  223. case "Equifax":
  224. worksheet.rows(tempIndex).cells(11).value("X");
  225. worksheet.rows(tempIndex).cells(11).cellFormat().alignment($.ig.excel.HorizontalCellAlignment.center);
  226. break;
  227.  
  228. case "TransUnion":
  229. worksheet.rows(tempIndex).cells(12).value("X");
  230. worksheet.rows(tempIndex).cells(12).cellFormat().alignment($.ig.excel.HorizontalCellAlignment.center);
  231. break;
  232.  
  233. default:
  234. consoel.log("Unknown credit bureau found.");
  235. break;
  236. }
  237. tempIndex++;
  238. }
  239. drawBorderToCells(inqueryTableStartIndex, 8, tempIndex - 1, 16);
  240. };
  241.  
  242. // Column Width Config
  243. worksheet.columns(0).setWidth(17.14, $.ig.excel.WorksheetColumnWidthUnit.character);
  244. worksheet.columns(1).setWidth(10.29, $.ig.excel.WorksheetColumnWidthUnit.character);
  245. worksheet.columns(2).setWidth(17.29, $.ig.excel.WorksheetColumnWidthUnit.character);
  246. worksheet.columns(3).setWidth(15.71, $.ig.excel.WorksheetColumnWidthUnit.character);
  247. worksheet.columns(4).setWidth(12.71, $.ig.excel.WorksheetColumnWidthUnit.character);
  248. worksheet.columns(5).setWidth(14, $.ig.excel.WorksheetColumnWidthUnit.character);
  249. worksheet.columns(6).setWidth(18, $.ig.excel.WorksheetColumnWidthUnit.character);
  250. worksheet.columns(7).setWidth(7.57, $.ig.excel.WorksheetColumnWidthUnit.character);
  251. worksheet.columns(8).setWidth(15.43, $.ig.excel.WorksheetColumnWidthUnit.character);
  252. worksheet.columns(9).setWidth(12.71, $.ig.excel.WorksheetColumnWidthUnit.character);
  253. worksheet.columns(10).setWidth(10.14, $.ig.excel.WorksheetColumnWidthUnit.character);
  254. worksheet.columns(11).setWidth(11, $.ig.excel.WorksheetColumnWidthUnit.character);
  255. worksheet.columns(12).setWidth(12.14, $.ig.excel.WorksheetColumnWidthUnit.character);
  256. worksheet.columns(13).setWidth(10.71, $.ig.excel.WorksheetColumnWidthUnit.character);
  257. worksheet.columns(14).setWidth(10, $.ig.excel.WorksheetColumnWidthUnit.character);
  258. worksheet.columns(15).setWidth(10, $.ig.excel.WorksheetColumnWidthUnit.character);
  259. worksheet.columns(16).setWidth(10, $.ig.excel.WorksheetColumnWidthUnit.character);
  260.  
  261. // Printing person name
  262. worksheet.rows(curRowIndex).cells(0).value("Person Name");
  263. tmpCell = worksheet.mergedCellsRegions().add(curRowIndex, 1, curRowIndex, 7);
  264. tmpCell.value(personal.name);
  265. format = tmpCell.cellFormat();
  266. format.font().height(fontSizeMapping[16]);
  267. format.font().bold(true);
  268. drawBorderToCells(curRowIndex, 0, curRowIndex, 7);
  269. curRowIndex++;
  270.  
  271. // Rows 0 - Bank Accounts Section...
  272. bankCardsTitle = worksheet.mergedCellsRegions().add(curRowIndex, 0, curRowIndex, 4);
  273. setTitleModeToCell(bankCardsTitle,"Bank Cards");
  274. curRowIndex++;
  275.  
  276. // Rows 1
  277. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(0), "Account Name");
  278. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(1), "Balance");
  279. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(2), "Limit");
  280. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(3), "Debt to Credit Ratio");
  281. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(4), "Amount to Pay");
  282. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(5), "New Balance");
  283. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(6), "Account Number");
  284. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(8), "High Balance");
  285. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(9), "Highest Balance Held Ratio");
  286. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(11), "Date Opened");
  287. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(12), "Age");
  288. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(13), "30 Days Late");
  289. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(14), "60 Days Late");
  290. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(15), "90 Days Late");
  291. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(16), "120 Days Late");
  292. curRowIndex++;
  293.  
  294. // From Rows 2, Bank accounts
  295. bankAccountStartIndex = curRowIndex + 1;
  296. for (var i = 0; i < bankAccounts.length; i++) {
  297. worksheet.rows(curRowIndex).cells(0).value(bankAccounts[i].name);
  298. setCurrencyModeToCell(worksheet.rows(curRowIndex).cells(1), bankAccounts[i].balance);
  299. setCurrencyModeToCell(worksheet.rows(curRowIndex).cells(2), bankAccounts[i].limit);
  300. tmpCell = worksheet.getCell('D' + (curRowIndex+1));
  301. tmpCell.cellFormat().formatString("0%");
  302. tmpCell.applyFormula("=B" + (curRowIndex+1) + "/C" + (curRowIndex+1));
  303. worksheet.rows(curRowIndex).cells(4).applyFormula("=IF(C" + (curRowIndex+1) + "<=1000,B" + (curRowIndex+1) + ",IF(D" + (curRowIndex+1) + "<0.3,0,B" + (curRowIndex+1) + "-(C" + (curRowIndex+1) + "*0.3)))");
  304. fillGreenToCell(worksheet.rows(curRowIndex).cells(5)).applyFormula("=B" + (curRowIndex+1) + "-E" + (curRowIndex+1));
  305. worksheet.rows(curRowIndex).cells(6).value(bankAccounts[i].accountNumber);
  306. worksheet.rows(curRowIndex).cells(8).value(bankAccounts[i].highBalance);
  307. tmpCell = worksheet.rows(curRowIndex).cells(9);
  308. tmpCell.cellFormat().formatString("0%");
  309. tmpCell.applyFormula("=I" + (curRowIndex+1) + "/C" + (curRowIndex+1));
  310. worksheet.rows(curRowIndex).cells(11).value(bankAccounts[i].opened);
  311. worksheet.rows(curRowIndex).cells(12).applyFormula('=DATEDIF(L' + (curRowIndex+1) + ',TODAY(),"Y")');
  312. // worksheet.rows(curRowIndex).cells(13).value(bankAccounts[i].latePayments['30']);
  313. // worksheet.rows(curRowIndex).cells(14).value(bankAccounts[i].latePayments['60']);
  314. // worksheet.rows(curRowIndex).cells(15).value(bankAccounts[i].latePayments['90']);
  315. lateDates = getLatePaymentCommonValue(bankAccounts[i].latePaymentDates);
  316. worksheet.rows(curRowIndex).cells(13).value(lateDates['30']);
  317. worksheet.rows(curRowIndex).cells(14).value(lateDates['60']);
  318. worksheet.rows(curRowIndex).cells(15).value(lateDates['90']);
  319. curRowIndex++;
  320. }
  321. bankAccountEndIndex = curRowIndex;
  322.  
  323. drawBorderToCells(bankAccountStartIndex - 2, 0, bankAccountEndIndex - 1, 6);
  324. drawBorderToCells(bankAccountStartIndex - 2, 8, bankAccountEndIndex - 1, 9);
  325. drawBorderToCells(bankAccountStartIndex - 2, 11, bankAccountEndIndex - 1, 16);
  326.  
  327. // Rows 1+(bank accounts count) - Retail Cards Section...
  328. bankCardsTitle = worksheet.mergedCellsRegions().add(curRowIndex, 0, curRowIndex, 4);
  329. setTitleModeToCell(bankCardsTitle, "Retail Cards");
  330. curRowIndex++;
  331.  
  332. retailCardStartIndex = curRowIndex;
  333.  
  334. // Rows 2+(bank accounts count)
  335. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(0), "Account Name");
  336. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(1), "Balance");
  337. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(2), "Limit");
  338. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(3), "Debt to Credit Ratio");
  339. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(4), "Amount to Pay");
  340. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(5), "New Balance");
  341. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(6), "Account Number");
  342. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(8), "High Balance");
  343. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(9), "Highest Balance Held Ratio");
  344. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(11), "Date Opened");
  345. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(12), "Age");
  346. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(13), "30 Days Late");
  347. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(14), "60 Days Late");
  348. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(15), "90 Days Late");
  349. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(16), "120 Days Late");
  350. curRowIndex++;
  351.  
  352. for (var i = 0; i < retailCards.length; i++) {
  353. worksheet.rows(curRowIndex).cells(0).value(retailCards[i].name);
  354. setCurrencyModeToCell(worksheet.rows(curRowIndex).cells(1), retailCards[i].balance);
  355. setCurrencyModeToCell(worksheet.rows(curRowIndex).cells(2), retailCards[i].limit);
  356. tmpCell = worksheet.getCell('D' + (curRowIndex+1));
  357. tmpCell.cellFormat().formatString("0%");
  358. tmpCell.applyFormula("=B" + (curRowIndex+1) + "/C" + (curRowIndex+1));
  359. worksheet.rows(curRowIndex).cells(4).applyFormula("=IF(C" + (curRowIndex+1) + "<=1000,B" + (curRowIndex+1) + ",IF(D" + (curRowIndex+1) + "<0.3,0,B" + (curRowIndex+1) + "-(C" + (curRowIndex+1) + "*0.3)))");
  360. fillGreenToCell(worksheet.rows(curRowIndex).cells(5)).applyFormula("=B" + (curRowIndex+1) + "-E" + (curRowIndex+1));
  361. worksheet.rows(curRowIndex).cells(6).value(retailCards[i].accountNumber);
  362. worksheet.rows(curRowIndex).cells(8).value(retailCards[i].highBalance);
  363. tmpCell = worksheet.rows(curRowIndex).cells(9);
  364. tmpCell.cellFormat().formatString("0%");
  365. tmpCell.applyFormula("=I" + (curRowIndex+1) + "/C" + (curRowIndex+1));
  366. worksheet.rows(curRowIndex).cells(11).value(retailCards[i].opened);
  367. worksheet.rows(curRowIndex).cells(12).applyFormula('=DATEDIF(L' + (curRowIndex+1) + ',TODAY(),"Y")');
  368. // worksheet.rows(curRowIndex).cells(13).value(retailCards[i].latePayments['30']);
  369. // worksheet.rows(curRowIndex).cells(14).value(retailCards[i].latePayments['60']);
  370. // worksheet.rows(curRowIndex).cells(15).value(retailCards[i].latePayments['90']);
  371. lateDates = getLatePaymentCommonValue(retailCards[i].latePaymentDates);
  372. worksheet.rows(curRowIndex).cells(13).value(lateDates['30']);
  373. worksheet.rows(curRowIndex).cells(14).value(lateDates['60']);
  374. worksheet.rows(curRowIndex).cells(15).value(lateDates['90']);
  375. curRowIndex++;
  376. }
  377. retailCardEndIndex = curRowIndex;
  378.  
  379. // Adding a blank row
  380. curRowIndex++;
  381.  
  382. // Summary Line
  383. summaryLineIndex = curRowIndex + 1;
  384. setTitleModeToCell(worksheet.rows(curRowIndex).cells(0), "SUM:");
  385. fillYellowToCell(worksheet.rows(curRowIndex).cells(1)).applyFormula("=SUM(B3:B" + bankAccountEndIndex + ",B" + retailCardStartIndex + ":B" + retailCardEndIndex + ")");
  386. fillYellowToCell(worksheet.rows(curRowIndex).cells(2)).applyFormula("=SUM(C3:C" + bankAccountEndIndex + ",C" + retailCardStartIndex + ":C" + retailCardEndIndex + ")");
  387. setTitleModeToCell(worksheet.rows(curRowIndex).cells(3), "Total Amt to Pay:");
  388. fillYellowToCell(worksheet.rows(curRowIndex).cells(4)).applyFormula("=SUM(E3:E" + bankAccountEndIndex + ",E" + retailCardStartIndex + ":E" + retailCardEndIndex + ")");
  389. curRowIndex += 2;
  390.  
  391. // 4 + bankAccounts.length
  392. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(2), "Debt to credit ratio");
  393. fillYellowToCell(worksheet.rows(curRowIndex).cells(3)).applyFormula("=MAX(D3:D" + bankAccountEndIndex + ",D" + retailCardStartIndex + ":D" + retailCardEndIndex + ")");
  394. worksheet.rows(curRowIndex).cells(3).cellFormat().formatString("0%");
  395. self.summaryLineIndex = curRowIndex + 1;
  396.  
  397. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(8), "Highest Balance Held Ratio");
  398. fillYellowToCell(worksheet.rows(curRowIndex).cells(9)).applyFormula("=MAX(J3:J" + bankAccountEndIndex + ",J" + retailCardStartIndex + ":J" + retailCardEndIndex + ")");
  399. worksheet.rows(curRowIndex).cells(9).cellFormat().formatString("0%");
  400.  
  401. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(12), "Oldest Account");
  402. fillYellowToCell(worksheet.rows(curRowIndex).cells(13)).applyFormula("=MAX(M3:M" + bankAccountEndIndex + ",M" + retailCardStartIndex + ":M" + retailCardEndIndex + ")");
  403. curRowIndex += 2;
  404.  
  405. // Aggregate line
  406. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(2), "Aggregate ");
  407. worksheet.rows(curRowIndex).cells(3).cellFormat().formatString("0%");
  408. fillYellowToCell(worksheet.rows(curRowIndex).cells(3)).applyFormula("=B" + summaryLineIndex + "/C" + summaryLineIndex);
  409. curRowIndex += 2;
  410.  
  411. drawBorderToCells(retailCardStartIndex, 0, retailCardEndIndex - 1, 6);
  412. drawBorderToCells(retailCardStartIndex, 8, retailCardEndIndex - 1, 9);
  413. drawBorderToCells(retailCardStartIndex, 11, retailCardEndIndex - 1, 16);
  414.  
  415. // Closed Accounts With Balances and/or Lates line
  416. bankCardsTitle = worksheet.mergedCellsRegions().add(curRowIndex, 0, curRowIndex, 4);
  417. setTitleModeToCell(bankCardsTitle, "Closed Accounts With Balances and/or Lates");
  418. curRowIndex++;
  419.  
  420. var closedAccountStartIndex = curRowIndex;
  421.  
  422. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(0), "Account Name");
  423. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(1), "Account Type");
  424. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(2), "Balance");
  425. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(3), "Account Number");
  426. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(4), "Payment Status");
  427. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(5), "Account Status");
  428.  
  429. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(8), "Date Opened");
  430. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(9), "Last Reported");
  431. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(10), "30 Days Late");
  432. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(11), "60 Days Late");
  433. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(12), "90 Days Late");
  434. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(13), "120 Days Late");
  435. curRowIndex++;
  436.  
  437. for (var i = 0; i < closedAccounts.length; i++) {
  438. item = closedAccounts[i];
  439. worksheet.rows(curRowIndex).cells(0).value(item.name);
  440. worksheet.rows(curRowIndex).cells(1).value(item.type);
  441. setCurrencyModeToCell(worksheet.rows(curRowIndex).cells(2), item.balance);
  442. worksheet.rows(curRowIndex).cells(3).value(item.accountNumber);
  443. worksheet.rows(curRowIndex).cells(4).value(item.payStatus);
  444. fillGreenToCell(worksheet.rows(curRowIndex).cells(5)).value(getAccountStatus(item.remark));
  445.  
  446. worksheet.rows(curRowIndex).cells(8).value(item.opened);
  447. worksheet.rows(curRowIndex).cells(9).value(item.reported);
  448. // worksheet.rows(curRowIndex).cells(10).value(item.latePayments[30]);
  449. // worksheet.rows(curRowIndex).cells(11).value(item.latePayments[60]);
  450. // worksheet.rows(curRowIndex).cells(12).value(item.latePayments[90]);
  451. lateDates = getLatePaymentCommonValue(item.latePaymentDates);
  452. worksheet.rows(curRowIndex).cells(10).value(lateDates['30']);
  453. worksheet.rows(curRowIndex).cells(11).value(lateDates['60']);
  454. worksheet.rows(curRowIndex).cells(12).value(lateDates['90']);
  455. curRowIndex++;
  456. }
  457.  
  458. var closedAccountEndIndex = curRowIndex;
  459.  
  460. drawBorderToCells(closedAccountStartIndex, 0, closedAccountEndIndex - 1, 5);
  461. drawBorderToCells(closedAccountStartIndex, 8, closedAccountEndIndex - 1, 13);
  462.  
  463. // Authorized User Accounts
  464. bankCardsTitle = worksheet.mergedCellsRegions().add(curRowIndex, 0, curRowIndex, 4);
  465. setTitleModeToCell(bankCardsTitle, "Authorized User Accounts");
  466. curRowIndex++;
  467.  
  468. authorizedAccountStartIndex = curRowIndex;
  469.  
  470. // Rows
  471. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(0), "Account Name");
  472. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(1), "Balance");
  473. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(2), "Limit");
  474. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(3), "Debt to Credit Ratio");
  475. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(4), "Amount to Pay");
  476. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(5), "New Balance");
  477. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(6), "Account Number");
  478. createInqueryTable(curRowIndex, inquiries);
  479. curRowIndex++;
  480.  
  481. for (var i = 0; i < authorized.length; i++) {
  482. var item = authorized[i];
  483.  
  484. worksheet.rows(curRowIndex).cells(0).value(item.name);
  485. setCurrencyModeToCell(worksheet.rows(curRowIndex).cells(1), item.balance);
  486. setCurrencyModeToCell(worksheet.rows(curRowIndex).cells(2), item.limit);
  487. tmpCell = worksheet.getCell('D' + (curRowIndex+1));
  488. tmpCell.cellFormat().formatString("0%");
  489. tmpCell.applyFormula("=B" + (curRowIndex+1) + "/C" + (curRowIndex+1));
  490. worksheet.rows(curRowIndex).cells(4).applyFormula("=IF(C" + (curRowIndex+1) + "<=1000,B" + (curRowIndex+1) + ",IF(D" + (curRowIndex+1) + "<0.3,0,B" + (curRowIndex+1) + "-(C" + (curRowIndex+1) + "*0.3)))");
  491. fillGreenToCell(worksheet.rows(curRowIndex).cells(5)).applyFormula("=B" + (curRowIndex+1) + "-E" + (curRowIndex+1));
  492. worksheet.rows(curRowIndex).cells(6).value(item.accountNumber);
  493. curRowIndex++;
  494. }
  495.  
  496. drawBorderToCells(authorizedAccountStartIndex, 0, curRowIndex - 1, 6);
  497.  
  498. curRowIndex ++;
  499.  
  500. // Installment Accounts
  501. installmentAccountTitle = worksheet.mergedCellsRegions().add(curRowIndex, 0, curRowIndex, 4);
  502. setTitleModeToCell(installmentAccountTitle, "Installment Accounts");
  503. curRowIndex++;
  504.  
  505. installmentAccountStartIndex = curRowIndex;
  506.  
  507. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(0), "Account Name");
  508. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(1), "Type of Loan");
  509. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(2), "Balance");
  510. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(3), "Monthly Payment");
  511. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(4), "Date Opened");
  512. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(5), "Age");
  513. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(6), "Lates");
  514. curRowIndex++;
  515.  
  516. var buildLatePaymentDateString = function(acc) {
  517. var arr = {"30": [], "60": [], "90": []},
  518. result = [];
  519. $.each(acc.latePaymentDates, function(key, value) {
  520. $.each(value, function(ind, date) {
  521. if (date.length > 0)
  522. arr[ind].push(date);
  523. });
  524. });
  525.  
  526. $.each(arr, function(key, value) {
  527. if (value.length > 0)
  528. result.push(value.join(", ") + "/" + key);
  529. });
  530.  
  531. return result.join(" - ");
  532. };
  533.  
  534. for(var i = 0; i < installmentAccounts.length; i++) {
  535. item = installmentAccounts[i];
  536. worksheet.rows(curRowIndex).cells(0).value(item.name);
  537. worksheet.rows(curRowIndex).cells(1).value(item.type);
  538. setCurrencyModeToCell(worksheet.rows(curRowIndex).cells(2), item.balance);
  539. setCurrencyModeToCell(worksheet.rows(curRowIndex).cells(3), item.payment);
  540. worksheet.rows(curRowIndex).cells(4).value(item.opened);
  541. worksheet.rows(curRowIndex).cells(5).applyFormula('=DATEDIF(E' + (curRowIndex + 1) + ',TODAY(),"Y")');
  542. worksheet.rows(curRowIndex).cells(6).value(buildLatePaymentDateString(item));
  543. curRowIndex++;
  544. }
  545.  
  546. installmentAccountEndIndex = curRowIndex;
  547. drawBorderToCells(installmentAccountStartIndex, 0, installmentAccountEndIndex - 1, 6);
  548. drawBorderToCells(authorizedAccountStartIndex, 8, installmentAccountEndIndex - 1, 16);
  549.  
  550. curRowIndex++;
  551.  
  552. // Last section...
  553.  
  554. // Public records section
  555. setTitleModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 0, curRowIndex, 4), "Public Records");
  556. curRowIndex++;
  557.  
  558. publicRecordsStartIndex = curRowIndex;
  559. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(0), "Account Name");
  560. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(1), "Type");
  561. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(2), "Date");
  562. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(3), "Status");
  563. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(4), "Amount");
  564. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(5), "Account");
  565. curRowIndex++;
  566.  
  567. for(var i = 0; i < publicRecords.length; i ++) {
  568. item = publicRecords[i];
  569.  
  570. worksheet.rows(curRowIndex).cells(0).value(item.name);
  571. worksheet.rows(curRowIndex).cells(1).value(item.type);
  572. worksheet.rows(curRowIndex).cells(2).value(item.date);
  573. worksheet.rows(curRowIndex).cells(3).value(item.status);
  574. setCurrencyModeToCell(worksheet.rows(curRowIndex).cells(4), item.amount);
  575. worksheet.rows(curRowIndex).cells(5).value(item.accountNumber);
  576. curRowIndex++;
  577. }
  578.  
  579. drawBorderToCells(publicRecordsStartIndex, 0, curRowIndex - 1, 5);
  580.  
  581. curRowIndex ++;
  582.  
  583.  
  584. // Address section
  585. addressTableStartIndex = curRowIndex;
  586. setTableHeadModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 0, curRowIndex, 4), "Current Address");
  587. curRowIndex++;
  588. worksheet.mergedCellsRegions().add(curRowIndex, 0, curRowIndex, 4).value(personal.curAddress.split("\n").join(" ").trim());
  589. curRowIndex++;
  590.  
  591. setTableHeadModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 0, curRowIndex, 4), "Previous Address");
  592. curRowIndex ++;
  593. for (var i = 0; i < personal.prevAddress.length; i ++) {
  594. tempAdrr = personal.prevAddress[i];
  595. worksheet.mergedCellsRegions().add(curRowIndex, 0, curRowIndex, 4).value(tempAdrr.split("\n").join(" ").trim());
  596. curRowIndex ++;
  597. }
  598. drawBorderToCells(addressTableStartIndex, 0, curRowIndex - 1, 4);
  599.  
  600. curRowIndex++;
  601.  
  602. // Fraud alert section
  603. setTableHeadModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 0, curRowIndex, 6), "Fraud Alert");
  604. curRowIndex++;
  605. fraudAlertStartIndex = curRowIndex;
  606. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(0), "Bureau");
  607. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(1), "Date");
  608. setTableHeadModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 2, curRowIndex, 6), "Statement");
  609. curRowIndex++;
  610.  
  611. worksheet.rows(curRowIndex).cells(0).value("Transunion");
  612. worksheet.mergedCellsRegions().add(curRowIndex, 2, curRowIndex, 6).value(fraud.TransUnion);
  613. worksheet.rows(curRowIndex).cells(1).value(fraud.TrReportedDate);
  614. curRowIndex++;
  615.  
  616. worksheet.rows(curRowIndex).cells(0).value("Experian");
  617. worksheet.mergedCellsRegions().add(curRowIndex, 2, curRowIndex, 6).value(fraud.Experian);
  618. worksheet.mergedCellsRegions().add(curRowIndex, 2, curRowIndex, 6).value(fraud.Experian);
  619. worksheet.rows(curRowIndex).cells(1).value(fraud.ExReportedDate);
  620. curRowIndex++;
  621.  
  622. worksheet.rows(curRowIndex).cells(0).value("Equifax");
  623. worksheet.mergedCellsRegions().add(curRowIndex, 2, curRowIndex, 6).value(fraud.Equifax);
  624. worksheet.rows(curRowIndex).cells(1).value(fraud.EqReportedDate);
  625. curRowIndex++;
  626.  
  627. drawBorderToCells(fraudAlertStartIndex, 0, curRowIndex - 1, 6);
  628.  
  629. curRowIndex++;
  630.  
  631. // Credit scroes section
  632. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(0), "Credit Scores");
  633. curRowIndex++;
  634.  
  635. drawBorderToCells(curRowIndex, 0, curRowIndex + 2, 2);
  636.  
  637. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(0), "Experian");
  638. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(1), "Equifax");
  639. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(2), "Transunion");
  640. curRowIndex++;
  641.  
  642. worksheet.rows(curRowIndex).cells(0).value(parseInt(self.scores.Experian));
  643. worksheet.rows(curRowIndex).cells(1).value(parseInt(self.scores.Equifax));
  644. worksheet.rows(curRowIndex).cells(2).value(parseInt(self.scores.Transunion));
  645. curRowIndex++;
  646.  
  647. setTableHeadModeToCell(worksheet.rows(curRowIndex).cells(0), "Age of Client");
  648. worksheet.rows(curRowIndex).cells(1).value(self.personal.birthday);
  649. worksheet.rows(curRowIndex).cells(2).applyFormula('=2015-B' + (curRowIndex + 1));
  650. self.yearBornLineInxex = curRowIndex + 1;
  651. },
  652.  
  653. createVerificationCallWorksheet: function(worksheet) {
  654. var curRowIndex = 0,
  655. mergedRegion = worksheet.mergedCellsRegions().add( 0, 0, 0, 9 ),
  656. setLableModeToCell = function(cell, value, italicFlag) {
  657. cellFormat = cell.cellFormat();
  658. cellFormat.font().height(fontSizeMapping['13']);
  659. cellFormat.font().name("Arial Unicode MS");
  660. cell.value(value);
  661.  
  662. if (italicFlag) {
  663. cellFormat.font().italic(true);
  664. }
  665. },
  666. setDataFieldModeToCell = function(cell) {
  667. cellFormat = cell.cellFormat();
  668. cellFormat.font().height(fontSizeMapping['13']);
  669. cellFormat.font().name("Arial Unicode MS");
  670. cellFormat.fill($.ig.excel.CellFill.createSolidFill('#EDE52E'));
  671. cellFormat.bottomBorderColorInfo(new $.ig.excel.WorkbookColorInfo('#000000'));
  672. };
  673.  
  674. worksheet.columns(0).setWidth(25, $.ig.excel.WorksheetColumnWidthUnit.character);
  675. worksheet.columns(1).setWidth(13.71, $.ig.excel.WorksheetColumnWidthUnit.character);
  676. worksheet.columns(2).setWidth(10.14, $.ig.excel.WorksheetColumnWidthUnit.character);
  677. worksheet.columns(3).setWidth(13, $.ig.excel.WorksheetColumnWidthUnit.character);
  678. worksheet.columns(4).setWidth(14.29, $.ig.excel.WorksheetColumnWidthUnit.character);
  679. worksheet.columns(5).setWidth(10.43, $.ig.excel.WorksheetColumnWidthUnit.character);
  680. worksheet.columns(6).setWidth(10.14, $.ig.excel.WorksheetColumnWidthUnit.character);
  681. worksheet.columns(7).setWidth(12, $.ig.excel.WorksheetColumnWidthUnit.character);
  682. worksheet.columns(8).setWidth(10.14, $.ig.excel.WorksheetColumnWidthUnit.character);
  683. worksheet.columns(9).setWidth(10.14, $.ig.excel.WorksheetColumnWidthUnit.character);
  684. worksheet.columns(10).setWidth(10.14, $.ig.excel.WorksheetColumnWidthUnit.character);
  685. worksheet.columns(11).setWidth(10.14, $.ig.excel.WorksheetColumnWidthUnit.character);
  686.  
  687. mergedRegion.value("CORPORATION PROFILE");
  688. cellFormat = mergedRegion.cellFormat();
  689. cellFormat.fill($.ig.excel.CellFill.createSolidFill('#000000'));
  690. cellFormat.font().height(fontSizeMapping['13']);
  691. cellFormat.alignment($.ig.excel.HorizontalCellAlignment.center);
  692. cellFormat.font().colorInfo(new $.ig.excel.WorkbookColorInfo('#FFFFFF'));
  693. setLableModeToCell(worksheet.rows(curRowIndex).cells(11), "Go into Sheet 3 and ask which type of cards they have for Chase, Bank of America, Citi, and Capital One (if any)");
  694. curRowIndex++;
  695.  
  696. setLableModeToCell(worksheet.rows(curRowIndex).cells(0), "Business Name:");
  697. setDataFieldModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 1, curRowIndex, 9));
  698.  
  699. curRowIndex++;
  700.  
  701. setLableModeToCell(worksheet.rows(curRowIndex).cells(0), "Mailing Address:");
  702. setDataFieldModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 1, curRowIndex, 6));
  703. setLableModeToCell(worksheet.rows(curRowIndex).cells(7), "Suite #", true);
  704. setDataFieldModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 8, curRowIndex, 9));
  705. setLableModeToCell(worksheet.rows(curRowIndex).cells(11), " Verify Address on ID and Application");
  706. curRowIndex++;
  707.  
  708. setLableModeToCell(worksheet.rows(curRowIndex).cells(0), "Mailing Cont.:");
  709. setLableModeToCell(worksheet.rows(curRowIndex).cells(1), "City");
  710. setDataFieldModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 2, curRowIndex, 4));
  711. setLableModeToCell(worksheet.rows(curRowIndex).cells(5), "State", true);
  712. setDataFieldModeToCell(worksheet.rows(curRowIndex).cells(6));
  713. setLableModeToCell(worksheet.rows(curRowIndex).cells(7), "ZIP Code", true);
  714. setDataFieldModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 8, curRowIndex, 9));
  715. setLableModeToCell(worksheet.rows(curRowIndex).cells(11), " Funding Estimate Amounts.");
  716. curRowIndex++;
  717.  
  718. setLableModeToCell(worksheet.rows(curRowIndex).cells(0), "Tax Identification No.:");
  719. setDataFieldModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 1, curRowIndex, 2));
  720. setLableModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 3, curRowIndex, 4), "# of Employees:");
  721. setDataFieldModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 5, curRowIndex, 9));
  722. setLableModeToCell(worksheet.rows(curRowIndex).cells(11), " Seek Fee.");
  723. curRowIndex++;
  724.  
  725. setLableModeToCell(worksheet.rows(curRowIndex).cells(0), "Phone Number:");
  726. setDataFieldModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 1, curRowIndex, 2));
  727. setLableModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 3, curRowIndex, 4), "Web Domain:");
  728. setDataFieldModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 5, curRowIndex, 9));
  729. setLableModeToCell(worksheet.rows(curRowIndex).cells(11), " Multiple applications will be sent for credit cards.");
  730. curRowIndex++;
  731.  
  732. setLableModeToCell(worksheet.rows(curRowIndex).cells(0), "Type of Entity:");
  733. setDataFieldModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 1, curRowIndex, 2));
  734. setLableModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 3, curRowIndex, 4), "State of Incorporation:");
  735. setDataFieldModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 5, curRowIndex, 9));
  736. setLableModeToCell(worksheet.rows(curRowIndex).cells(11), " Funding Status Update");
  737. curRowIndex++;
  738.  
  739. setLableModeToCell(worksheet.rows(curRowIndex).cells(0), "Nature of Business:");
  740. setDataFieldModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 1, curRowIndex, 2));
  741. setLableModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 3, curRowIndex, 4), "Services Provided:");
  742. setDataFieldModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 5, curRowIndex, 9));
  743. setLableModeToCell(worksheet.rows(curRowIndex).cells(11), " APR, both introductory and ongoing rates.");
  744. curRowIndex++;
  745.  
  746. setLableModeToCell(worksheet.rows(curRowIndex).cells(0), "Business Incorp Date:");
  747. setDataFieldModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 1, curRowIndex, 2));
  748. setLableModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 3, curRowIndex, 4), "Business Start Date:");
  749. setDataFieldModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 5, curRowIndex, 9));
  750. setLableModeToCell(worksheet.rows(curRowIndex).cells(11), " Timeline of funding process.");
  751. curRowIndex++;
  752.  
  753. setLableModeToCell(worksheet.rows(curRowIndex).cells(0), "Business Gross Income");
  754. setDataFieldModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 1, curRowIndex, 2));
  755. setLableModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 3, curRowIndex, 4), "Net Profit");
  756. setDataFieldModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 5, curRowIndex, 9));
  757. setLableModeToCell(worksheet.rows(curRowIndex).cells(11), " Do\'s and don\'ts of credit report.");
  758. curRowIndex++;
  759.  
  760. setLableModeToCell(worksheet.rows(curRowIndex).cells(11), " How to handle bank calls and emails.");
  761. curRowIndex++;
  762.  
  763. setLableModeToCell(worksheet.rows(curRowIndex).cells(0), "GUARANTOR INFO");
  764. setLableModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 3, curRowIndex, 4), "Industry Experience:");
  765. setLableModeToCell(worksheet.rows(curRowIndex).cells(11), " Invoicing and Liquidation Instructions.");
  766. curRowIndex++;
  767.  
  768. setLableModeToCell(worksheet.rows(curRowIndex).cells(0), "Full Name:");
  769. setDataFieldModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 1, curRowIndex, 9));
  770. setLableModeToCell(worksheet.rows(curRowIndex).cells(11), " Does client understand APR, both introductory and ongoing rates?");
  771. curRowIndex++;
  772.  
  773. setLableModeToCell(worksheet.rows(curRowIndex).cells(1), "Last");
  774. setLableModeToCell(worksheet.rows(curRowIndex).cells(3), "First");
  775. setLableModeToCell(worksheet.rows(curRowIndex).cells(7), "Middle Name");
  776. setLableModeToCell(worksheet.rows(curRowIndex).cells(11), " Timeline of funding process: ");
  777. curRowIndex++;
  778.  
  779. setLableModeToCell(worksheet.rows(curRowIndex).cells(0), "Mailing Address:");
  780. setDataFieldModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 1, curRowIndex, 6));
  781. setLableModeToCell(worksheet.rows(curRowIndex).cells(7), "Suite #");
  782. setDataFieldModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 8, curRowIndex, 9));
  783. setLableModeToCell(worksheet.rows(curRowIndex).cells(11), " Do\'s and don\'ts of funding process: ");
  784. curRowIndex++;
  785.  
  786. setLableModeToCell(worksheet.rows(curRowIndex).cells(0), "Mailing Cont.:");
  787. setLableModeToCell(worksheet.rows(curRowIndex).cells(1), "City", true);
  788. setDataFieldModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 2, curRowIndex, 4));
  789. setLableModeToCell(worksheet.rows(curRowIndex).cells(5), "State", true);
  790. setDataFieldModeToCell(worksheet.rows(curRowIndex).cells(6));
  791. setLableModeToCell(worksheet.rows(curRowIndex).cells(7), "ZIP Code", true);
  792. setDataFieldModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 8, curRowIndex, 9));
  793. setLableModeToCell(worksheet.rows(curRowIndex).cells(11), " How to handle bank calls: ");
  794. curRowIndex++;
  795.  
  796. setLableModeToCell(worksheet.rows(curRowIndex).cells(0), "Social Security Number:");
  797. setDataFieldModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 1, curRowIndex, 2));
  798. setLableModeToCell(worksheet.rows(curRowIndex).cells(3), "Birth Date:");
  799. setDataFieldModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 4, curRowIndex, 6));
  800. setLableModeToCell(worksheet.rows(curRowIndex).cells(7), "Age", true);
  801. setDataFieldModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 8, curRowIndex, 9));
  802. setLableModeToCell(worksheet.rows(curRowIndex).cells(11), " Who their Seek Funding Coordinator is:");
  803. curRowIndex++;
  804.  
  805. setLableModeToCell(worksheet.rows(curRowIndex).cells(0), "Home Phone Number:");
  806. setDataFieldModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 1, curRowIndex, 2));
  807. setLableModeToCell(worksheet.rows(curRowIndex).cells(3), "Cell Number:");
  808. setDataFieldModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 4, curRowIndex, 9));
  809. setLableModeToCell(worksheet.rows(curRowIndex).cells(11), " Additional Comments:");
  810. curRowIndex++;
  811.  
  812. setLableModeToCell(worksheet.rows(curRowIndex).cells(0), "Email Address:");
  813. setDataFieldModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 1, curRowIndex, 2));
  814. setLableModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 3, curRowIndex, 4), "Mother\'s Maiden Name:");
  815. setDataFieldModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 5, curRowIndex, 9));
  816. curRowIndex++;
  817.  
  818. setLableModeToCell(worksheet.rows(curRowIndex).cells(0), "Time at Residence:");
  819. setDataFieldModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 1, curRowIndex, 2));
  820. setLableModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 3, curRowIndex, 4), "Gross Annual Income:");
  821. setDataFieldModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 5, curRowIndex, 9));
  822. curRowIndex++;
  823.  
  824. setLableModeToCell(worksheet.rows(curRowIndex).cells(0), "Drivers License:");
  825. setDataFieldModeToCell(worksheet.rows(curRowIndex).cells(1));
  826. setLableModeToCell(worksheet.rows(curRowIndex).cells(2), "State:");
  827. setDataFieldModeToCell(worksheet.rows(curRowIndex).cells(3));
  828. setLableModeToCell(worksheet.rows(curRowIndex).cells(4), "Issue Date:");
  829. setDataFieldModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 5, curRowIndex, 6));
  830. setLableModeToCell(worksheet.rows(curRowIndex).cells(7), "Expiration:");
  831. setDataFieldModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 8, curRowIndex, 9));
  832. curRowIndex++;
  833.  
  834. setLableModeToCell(worksheet.rows(curRowIndex).cells(0), "Seek Additional Info", true);
  835. curRowIndex++;
  836.  
  837. setLableModeToCell(worksheet.rows(curRowIndex).cells(0), "1. Income used for Personal Or Business?");
  838. curRowIndex++;
  839. setDataFieldModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 0, curRowIndex, 9));
  840. curRowIndex++;
  841.  
  842. setLableModeToCell(worksheet.rows(curRowIndex).cells(0), "2. Business Projection Used?");
  843. curRowIndex++;
  844. setDataFieldModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 0, curRowIndex, 9));
  845. curRowIndex++;
  846.  
  847. setLableModeToCell(worksheet.rows(curRowIndex).cells(0), "3. Business address used on application? (Cannot Be P.O. BOX)");
  848. curRowIndex++;
  849. setDataFieldModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 0, curRowIndex, 9));
  850. curRowIndex++;
  851.  
  852. setLableModeToCell(worksheet.rows(curRowIndex).cells(0), "4. Time in business?");
  853. curRowIndex++;
  854. setDataFieldModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 0, curRowIndex, 9));
  855. curRowIndex++;
  856.  
  857. setLableModeToCell(worksheet.rows(curRowIndex).cells(0), "5. Business Name Used? Business may have other names such as DBA,");
  858. curRowIndex++;
  859. setDataFieldModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 0, curRowIndex, 9));
  860. curRowIndex++;
  861. curRowIndex++;
  862. curRowIndex++;
  863.  
  864.  
  865. setLableModeToCell(worksheet.rows(curRowIndex).cells(0), "Business Questions:");
  866. curRowIndex++;
  867.  
  868. setLableModeToCell(worksheet.rows(curRowIndex).cells(0), "1. Can they receive mail at business address?");
  869. curRowIndex++;
  870. setDataFieldModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 0, curRowIndex, 9));
  871. curRowIndex++;
  872.  
  873. setLableModeToCell(worksheet.rows(curRowIndex).cells(0), "2. Does client have business checking account? What Bank? How much in deposits?");
  874. curRowIndex++;
  875. setDataFieldModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 0, curRowIndex, 9));
  876. curRowIndex++;
  877.  
  878. setLableModeToCell(worksheet.rows(curRowIndex).cells(0), "3. Are there business Derrogatories/BK?");
  879. curRowIndex++;
  880. setDataFieldModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 0, curRowIndex, 9));
  881. curRowIndex++;
  882.  
  883. setLableModeToCell(worksheet.rows(curRowIndex).cells(0), "4. Are there any existing business accounts?");
  884. curRowIndex++;
  885. setDataFieldModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 0, curRowIndex, 9));
  886. curRowIndex++;
  887.  
  888. setLableModeToCell(worksheet.rows(curRowIndex).cells(0), "5. If Yes, Need name of Bank, Credit Limits, Balances, Average monthly payment being made, current/delinquent on account");
  889. curRowIndex++;
  890. setDataFieldModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 0, curRowIndex, 9));
  891. curRowIndex++;
  892. curRowIndex++;
  893.  
  894. setLableModeToCell(worksheet.rows(curRowIndex).cells(0), "Personal Questions:");
  895. curRowIndex++;
  896.  
  897. setLableModeToCell(worksheet.rows(curRowIndex).cells(0), "1. Can they receive mail at personal address?");
  898. curRowIndex++;
  899. setDataFieldModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 0, curRowIndex, 9));
  900. curRowIndex++;
  901.  
  902. setLableModeToCell(worksheet.rows(curRowIndex).cells(0), "2. Personal BK in the past?");
  903. curRowIndex++;
  904. setDataFieldModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 0, curRowIndex, 9));
  905. curRowIndex++;
  906.  
  907. setLableModeToCell(worksheet.rows(curRowIndex).cells(0), "3. Personal Checking/Savings? What Banks? Current Deposit amounts? (If BOFA/CHASE-also ask last deposit amount, how much, when?)");
  908. curRowIndex++;
  909. setDataFieldModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 0, curRowIndex, 9));
  910. curRowIndex++;
  911.  
  912. setLableModeToCell(worksheet.rows(curRowIndex).cells(0), "4. Vehicles registered under PG (Year, Model, Color)?");
  913. curRowIndex++;
  914. setDataFieldModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 0, curRowIndex, 9));
  915. curRowIndex++;
  916.  
  917. setLableModeToCell(worksheet.rows(curRowIndex).cells(0), "5. College Graduated at? Year? Major? Any Special Degrees/License? (Example: real estate License)");
  918. curRowIndex++;
  919. setDataFieldModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 0, curRowIndex, 9));
  920. curRowIndex++;
  921.  
  922. setLableModeToCell(worksheet.rows(curRowIndex).cells(0), "6. Who else lives in the household? Need First,Middle,Last name for everyone in the household along with Date of Birth");
  923. curRowIndex++;
  924. setDataFieldModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 0, curRowIndex, 9));
  925. curRowIndex++;
  926.  
  927. setLableModeToCell(worksheet.rows(curRowIndex).cells(0), "7. Do they have personal credit cards with BofA/Chase? Last few purchases made, amount, due dates of each account.");
  928. curRowIndex++;
  929. setDataFieldModeToCell(worksheet.mergedCellsRegions().add(curRowIndex, 0, curRowIndex, 9));
  930. curRowIndex++;
  931.  
  932. curRowIndex += 2;
  933. setLableModeToCell(worksheet.rows(curRowIndex).cells(0), "Go into Sheet 3 and ask which type of cards they have for Chase, Bank of America, Citi, and Capital One (if any)");
  934. },
  935.  
  936. createSummaryWorksheet: function(worksheet) {
  937. var curRowIndex = 0,
  938. self = this,
  939. setBoldToCell = function(cell, value, underlineFlag) {
  940. cell.cellFormat().font().bold(true);
  941. cell.value(value);
  942.  
  943. if (underlineFlag)
  944. cell.cellFormat().font().underlineStyle($.ig.excel.FontUnderlineStyle.single);
  945. },
  946. fillBlueToCell = function(cell) {
  947. cell.cellFormat().fill($.ig.excel.CellFill.createSolidFill('#92D050'));
  948. return cell;
  949. },
  950. drawBorderToCells = function(r1, c1, r2, c2) {
  951. for (var i = r1; i < r2 + 1; i ++) {
  952. for (var j = c1; j < c2 + 1; j ++) {
  953. format = worksheet.rows(i).cells(j).cellFormat();
  954.  
  955. format.topBorderColorInfo(new $.ig.excel.WorkbookColorInfo('#000000'));
  956. format.rightBorderColorInfo(new $.ig.excel.WorkbookColorInfo('#000000'));
  957. format.bottomBorderColorInfo(new $.ig.excel.WorkbookColorInfo('#000000'));
  958. format.leftBorderColorInfo(new $.ig.excel.WorkbookColorInfo('#000000'));
  959. }
  960. }
  961. };
  962.  
  963. // Column width Config
  964.  
  965. worksheet.columns(0).setWidth(38, $.ig.excel.WorksheetColumnWidthUnit.character);
  966. worksheet.columns(1).setWidth(14.71, $.ig.excel.WorksheetColumnWidthUnit.character);
  967. worksheet.columns(2).setWidth(14.71, $.ig.excel.WorksheetColumnWidthUnit.character);
  968. worksheet.columns(3).setWidth(14.71, $.ig.excel.WorksheetColumnWidthUnit.character);
  969. worksheet.columns(4).setWidth(3.43, $.ig.excel.WorksheetColumnWidthUnit.character);
  970. worksheet.columns(5).setWidth(38, $.ig.excel.WorksheetColumnWidthUnit.character);
  971. worksheet.columns(6).setWidth(9.43, $.ig.excel.WorksheetColumnWidthUnit.character);
  972. worksheet.columns(7).setWidth(8.43, $.ig.excel.WorksheetColumnWidthUnit.character);
  973. worksheet.columns(8).setWidth(8.43, $.ig.excel.WorksheetColumnWidthUnit.character);
  974. worksheet.columns(9).setWidth(9.71, $.ig.excel.WorksheetColumnWidthUnit.character);
  975. worksheet.columns(10).setWidth(6.14, $.ig.excel.WorksheetColumnWidthUnit.character);
  976. worksheet.columns(11).setWidth(9.14, $.ig.excel.WorksheetColumnWidthUnit.character);
  977.  
  978. for (var i = 1; i < 24; i++) {
  979. if (i !== 17) {
  980. worksheet.rows(i).cells(6).cellFormat().fill($.ig.excel.CellFill.createSolidFill('#92D050'));
  981. } else {
  982. worksheet.rows(i).cells(6).cellFormat().fill($.ig.excel.CellFill.createSolidFill('#D7E4BC'));
  983. }
  984. }
  985.  
  986. drawBorderToCells(0, 0, 26, 3);
  987. drawBorderToCells(1, 5, 23, 7);
  988. drawBorderToCells(2, 9, 3, 11);
  989.  
  990. defaultFormat = worksheet.columns(0, 11).cellFormat();
  991. defaultFormat.font().height(fontSizeMapping['12']);
  992.  
  993. setBoldToCell(worksheet.rows(curRowIndex).cells(1), "Tier 1");
  994. setBoldToCell(worksheet.rows(curRowIndex).cells(2), "Tier 2");
  995. setBoldToCell(worksheet.rows(curRowIndex).cells(3), "Tier 3");
  996. setBoldToCell(worksheet.rows(curRowIndex).cells(6), "Inputs");
  997. setBoldToCell(worksheet.rows(curRowIndex).cells(9), "Credit Score");
  998. curRowIndex++;
  999.  
  1000. worksheet.rows(curRowIndex).cells(0).value("Credit Score");
  1001. worksheet.rows(curRowIndex).cells(1).value("720+");
  1002. worksheet.rows(curRowIndex).cells(2).value("690-719");
  1003. worksheet.rows(curRowIndex).cells(3).value("660-689");
  1004. worksheet.rows(curRowIndex).cells(5).value("Credit Score");
  1005. worksheet.rows(curRowIndex).cells(6).value("Inputs");
  1006. worksheet.rows(curRowIndex).cells(7).applyFormula('=IF(AND($G$2>=660,$G$2<=689),"Tier 3",(IF(AND($G$2>=690,$G$2<=719),"Tier 2",(IF(AND($G$2>=720,$G$2<=900),"Tier 1",(IF(AND($G$2>=500,$G$2<=659),"DECLINE",)))))))');
  1007. worksheet.rows(curRowIndex).cells(9).value("Credit Score");
  1008. curRowIndex++;
  1009.  
  1010. worksheet.rows(curRowIndex).cells(9).value("Experian");
  1011. worksheet.rows(curRowIndex).cells(10).value("Equifax");
  1012. worksheet.rows(curRowIndex).cells(11).value("Transunion");
  1013. curRowIndex++;
  1014.  
  1015. worksheet.rows(curRowIndex).cells(9).value(parseInt(self.scores.Experian));
  1016. worksheet.rows(curRowIndex).cells(10).value(parseInt(self.scores.Equifax));
  1017. worksheet.rows(curRowIndex).cells(11).value(parseInt(self.scores.Transunion));
  1018. curRowIndex++;
  1019.  
  1020. worksheet.rows(curRowIndex).cells(0).value("Debt to Credit Ratio");
  1021. worksheet.rows(curRowIndex).cells(1).value("0-45%");
  1022. worksheet.rows(curRowIndex).cells(2).value("46-50%");
  1023. worksheet.rows(curRowIndex).cells(3).value("51-65%");
  1024. worksheet.rows(curRowIndex).cells(5).value("Highest Utilization");
  1025. worksheet.rows(curRowIndex).cells(6).applyFormula("=Calculator!D" + self.summaryLineIndex);
  1026. worksheet.rows(curRowIndex).cells(7).applyFormula('=IF(AND($G$5>=0.1,$G$5<=0.45),"Tier 1",(IF(AND($G$5>=0.46,$G$5<=0.5),"Tier 2",(IF(AND($G$5>=0.51,$G$5<=0.65),"Tier 3",(IF(AND($G$5>=0.66,$G$5<=1),"DECLINE",)))))))');
  1027. curRowIndex++;
  1028.  
  1029. worksheet.rows(curRowIndex).cells(5).value("Aggregate Utilization");
  1030. worksheet.rows(curRowIndex).cells(6).applyFormula("=Calculator!D" + (self.summaryLineIndex + 2));
  1031. curRowIndex++;
  1032.  
  1033. worksheet.rows(curRowIndex).cells(0).value("Minimum # of open Lines");
  1034. worksheet.rows(curRowIndex).cells(1).value(3);
  1035. worksheet.rows(curRowIndex).cells(2).value(2);
  1036. worksheet.rows(curRowIndex).cells(3).value(2);
  1037. worksheet.rows(curRowIndex).cells(5).value("Minimum # of open Lines");
  1038. worksheet.rows(curRowIndex).cells(6).value(0);
  1039. worksheet.rows(curRowIndex).cells(7).applyFormula('=IF(AND($G$7>=0,$G$7<=1.9),"DECLINE",(IF(AND($G$7>=2,$G$7<=2.9),"Tier 2 Or Tier 3",(IF(AND($G$7>=3,$G$7<=99),"Tier 1",)))))');
  1040. curRowIndex++;
  1041.  
  1042. worksheet.rows(curRowIndex).cells(0).value("Minimum Age of Accounts (oldest)");
  1043. worksheet.rows(curRowIndex).cells(1).value(4);
  1044. worksheet.rows(curRowIndex).cells(2).value(2);
  1045. worksheet.rows(curRowIndex).cells(3).value(2);
  1046. worksheet.rows(curRowIndex).cells(5).value("Minimum Age of Accounts (oldest)");
  1047. worksheet.rows(curRowIndex).cells(6).applyFormula("=Calculator!N" + self.summaryLineIndex);
  1048. worksheet.rows(curRowIndex).cells(7).applyFormula('=IF(AND($G$8>=0,$G$8<=1.9),"DECLINE",(IF(AND($G$8>=2,$G$8<=3.9),"Tier 2 Or Tier 3",(IF(AND($G$8>=4,$G$8<=99),"Tier 1",)))))');
  1049. curRowIndex++;
  1050.  
  1051. worksheet.rows(curRowIndex).cells(0).value("Max # of Inquiries/ per bureau (last 6 months)");
  1052. worksheet.rows(curRowIndex).cells(1).value(2);
  1053. worksheet.rows(curRowIndex).cells(2).value(4);
  1054. worksheet.rows(curRowIndex).cells(3).value(6);
  1055. worksheet.rows(curRowIndex).cells(5).value("Max # of Inquiries/ per bureau (last 6 months)");
  1056. worksheet.rows(curRowIndex).cells(6).value(0);
  1057. worksheet.rows(curRowIndex).cells(7).applyFormula('=IF(AND($G$9>=0,$G$9<=2),"Tier 1",(IF(AND($G$9>=2.1,$G$9<=4),"Tier 2 ",(IF(AND($G$9>=4.1,$G$9<=6),"Tier 3",(IF(AND($G$9>=6.1,$G$9<=99),"DECLINE")))))))');
  1058. curRowIndex++;
  1059.  
  1060. worksheet.rows(curRowIndex).cells(0).value("Max # Derogatories (last 2 years)");
  1061. worksheet.rows(curRowIndex).cells(1).value(0);
  1062. worksheet.rows(curRowIndex).cells(2).value(1);
  1063. worksheet.rows(curRowIndex).cells(3).value(3);
  1064. worksheet.rows(curRowIndex).cells(5).value("Max # Deragatories 30 days late (last 2 years)");
  1065. worksheet.rows(curRowIndex).cells(6).value(0);
  1066. worksheet.rows(curRowIndex).cells(7).applyFormula('=IF(AND($G$10>=0,$G$10<=0.9),"Tier 1",(IF(AND($G$10>=1,$G$10<=1.9),"Tier 2 ",(IF(AND($G$10>=2,$G$10<=3.9),"Tier 3",(IF(AND($G$10>=4,$G$10<=99),"DECLINE")))))))');
  1067. curRowIndex++;
  1068.  
  1069. worksheet.rows(curRowIndex).cells(0).value("Max # Deragatories 30 days late (last 2 years)");
  1070. worksheet.rows(curRowIndex).cells(1).value(0);
  1071. worksheet.rows(curRowIndex).cells(2).value(1);
  1072. worksheet.rows(curRowIndex).cells(3).value(3);
  1073. worksheet.rows(curRowIndex).cells(5).value("Max # Deragatories 60 days late (last 2 years)");
  1074. worksheet.rows(curRowIndex).cells(6).value(0);
  1075. worksheet.rows(curRowIndex).cells(7).applyFormula('=IF($G$11=0,"All Tiers","DECLINE")');
  1076. curRowIndex++;
  1077.  
  1078. worksheet.rows(curRowIndex).cells(0).value("Max # Deragatories 60 days late (last 2 years)");
  1079. worksheet.rows(curRowIndex).cells(1).value(0);
  1080. worksheet.rows(curRowIndex).cells(2).value(0);
  1081. worksheet.rows(curRowIndex).cells(3).value(0);
  1082. worksheet.rows(curRowIndex).cells(5).value("Max # Derogatories (last 2 years)");
  1083. worksheet.rows(curRowIndex).cells(6).value(0);
  1084. worksheet.rows(curRowIndex).cells(7).applyFormula('=IF(AND($G$12>=0,$G$12<=0.9),"Tier 1",(IF(AND($G$12>=1,$G$12<=1.9),"Tier 2 ",(IF(AND($G$12>=2,$G$12<=3.9),"Tier 3",(IF(AND($G$12>=4,$G$12<=99),"DECLINE")))))))');
  1085. curRowIndex++;
  1086. curRowIndex++;
  1087.  
  1088. worksheet.rows(curRowIndex).cells(0).value("Highest Balance Held Ratio (Highest)");
  1089. worksheet.rows(curRowIndex).cells(1).value("60%+");
  1090. worksheet.rows(curRowIndex).cells(2).value("30-60%");
  1091. worksheet.rows(curRowIndex).cells(3).value("0-29%");
  1092. worksheet.rows(curRowIndex).cells(5).value("Highest Balance Held Ratio (Highest) ");
  1093. worksheet.rows(curRowIndex).cells(6).applyFormula("=Calculator!J" + self.summaryLineIndex);
  1094. worksheet.rows(curRowIndex).cells(7).applyFormula('=IF(AND($G$14>=0.61,$G$14<=0.99),"Tier 1",(IF(AND($G$14>=0.3,$G$14<=0.6),"Tier 2 ",(IF(AND($G$14>=0,$G$14<=0.29),"Tier 3",)))))');
  1095. curRowIndex++;
  1096. curRowIndex++;
  1097.  
  1098. worksheet.rows(curRowIndex).cells(0).value("# of Satisifed Accounts");
  1099. worksheet.rows(curRowIndex).cells(1).value("7+");
  1100. worksheet.rows(curRowIndex).cells(2).value("3--6");
  1101. worksheet.rows(curRowIndex).cells(3).value("1--2");
  1102. worksheet.rows(curRowIndex).cells(5).value("# of Satisifed Accounts");
  1103. worksheet.rows(curRowIndex).cells(6).value(0);
  1104. worksheet.rows(curRowIndex).cells(7).applyFormula('=IF(AND($G$16>=7,$G$16<=99),"Tier 1",(IF(AND($G$16>=3,$G$16<=6.9),"Tier 2 ",(IF(AND($G$16>=1,$G$16<=2.9),"Tier 3",(IF(AND($G$16>=0,$G$16<=0.9),"DECLINE")))))))');
  1105. curRowIndex++;
  1106.  
  1107. worksheet.rows(curRowIndex).cells(0).value("Mortgage Holder (Never Late)");
  1108. worksheet.rows(curRowIndex).cells(1).value("yes");
  1109. worksheet.rows(curRowIndex).cells(2).value("no");
  1110. worksheet.rows(curRowIndex).cells(3).value("no");
  1111. worksheet.rows(curRowIndex).cells(5).value("Mortgage Holder (Never Late)");
  1112. worksheet.rows(curRowIndex).cells(6).value("no");
  1113. worksheet.rows(curRowIndex).cells(7).applyFormula('=IF($G$17="yes","Tier 1","Tier 2 Or 3")');
  1114. curRowIndex++;
  1115.  
  1116. worksheet.rows(curRowIndex).cells(0).value("Conservative States ");
  1117. worksheet.rows(curRowIndex).cells(1).value("yes");
  1118. worksheet.rows(curRowIndex).cells(2).value("no");
  1119. worksheet.rows(curRowIndex).cells(3).value("no");
  1120. worksheet.rows(curRowIndex).cells(5).value("Enter State (lower case)");
  1121. worksheet.rows(curRowIndex).cells(6).value("CA");
  1122. worksheet.rows(curRowIndex).cells(7).value('');
  1123. curRowIndex++;
  1124.  
  1125. worksheet.rows(curRowIndex).cells(5).value("Conservative States ");
  1126. worksheet.rows(curRowIndex).cells(6).applyFormula("=IF(VLOOKUP($G$18,'State Codes'!$B$1:$C$51,2,FALSE)>=60000,\"no\",\"yes\")");
  1127. worksheet.rows(curRowIndex).cells(7).applyFormula('=IF($G$19="yes","Tier 1","Tier 2 Or 3")');
  1128. curRowIndex++;
  1129.  
  1130. worksheet.rows(curRowIndex).cells(0).value("Age of client");
  1131. worksheet.rows(curRowIndex).cells(1).value("25-60");
  1132. worksheet.rows(curRowIndex).cells(2).value("25-60");
  1133. worksheet.rows(curRowIndex).cells(3).value("22-65");
  1134. worksheet.rows(curRowIndex).cells(5).value("Year Born");
  1135. worksheet.rows(curRowIndex).cells(6).applyFormula("=Calculator!B" + self.yearBornLineInxex);
  1136. worksheet.rows(curRowIndex).cells(7).value('');
  1137. curRowIndex++;
  1138.  
  1139. worksheet.rows(curRowIndex).cells(5).value("Age of client");
  1140. worksheet.rows(curRowIndex).cells(6).applyFormula("=2015-G20");
  1141. worksheet.rows(curRowIndex).cells(7).applyFormula('=IF(AND($G$21>=25,$G$21<=60),"All Tiers",(IF(AND($G$21>=22,$G$21<=24.9),"Tier 3 ",(IF(AND($G$21>=61,$G$21<=65),"Tier 3",(IF(AND($G$21>=66,$G$21<=99),"DECLINE")))))))');
  1142. curRowIndex++;
  1143.  
  1144. worksheet.rows(curRowIndex).cells(0).value("Primary Funding Level");
  1145. worksheet.rows(curRowIndex).cells(1).value("$60,000-$90,000");
  1146. worksheet.rows(curRowIndex).cells(2).value("$30,000- $75,000");
  1147. worksheet.rows(curRowIndex).cells(3).value("$10,000- $40,000");
  1148. setBoldToCell(worksheet.rows(curRowIndex).cells(5), "Funding Holdbacks", true);
  1149. worksheet.rows(curRowIndex).cells(6).value("");
  1150. worksheet.rows(curRowIndex).cells(7).value('');
  1151. curRowIndex++;
  1152.  
  1153. worksheet.rows(curRowIndex).cells(0).value("Secondary Funding Levell");
  1154. worksheet.rows(curRowIndex).cells(1).value("$40,000- $70,000");
  1155. worksheet.rows(curRowIndex).cells(2).value("$10,000- $40,000");
  1156. worksheet.rows(curRowIndex).cells(3).value("$5,000- $30,000");
  1157. worksheet.rows(curRowIndex).cells(5).value("Mortgage Holder (Never Late)");
  1158. worksheet.rows(curRowIndex).cells(6).value("no");
  1159. worksheet.rows(curRowIndex).cells(7).applyFormula('=IF($G$23="yes","All Tiers","DECLINE")');
  1160. curRowIndex++;
  1161.  
  1162. worksheet.rows(curRowIndex).cells(5).value("Bankruptcies, Collections, Judgements ");
  1163. curRowIndex++;
  1164.  
  1165. setBoldToCell(worksheet.rows(curRowIndex).cells(0), "Funding Holdbacks", true);
  1166. curRowIndex++;
  1167.  
  1168. worksheet.rows(curRowIndex).cells(0).value("Age of client");
  1169. worksheet.rows(curRowIndex).cells(1).value("52-60");
  1170. worksheet.rows(curRowIndex).cells(2).value("22-25, 52-60");
  1171. worksheet.rows(curRowIndex).cells(3).value("22-25, 52-60");
  1172. curRowIndex++;
  1173.  
  1174. worksheet.rows(curRowIndex).cells(0).value("Mortgage Holder (Never Late)");
  1175. worksheet.rows(curRowIndex).cells(1).value("no");
  1176. worksheet.rows(curRowIndex).cells(2).value("no");
  1177. worksheet.rows(curRowIndex).cells(3).value("no");
  1178. curRowIndex++;
  1179. },
  1180.  
  1181. createStateCodesWorksheet: function(worksheet) {
  1182. var self = this;
  1183.  
  1184. worksheet.columns(0).setWidth(18, $.ig.excel.WorksheetColumnWidthUnit.character);
  1185. worksheet.columns(1).setWidth(3.14, $.ig.excel.WorksheetColumnWidthUnit.character);
  1186. worksheet.columns(2).setWidth(8.14, $.ig.excel.WorksheetColumnWidthUnit.character);
  1187.  
  1188. for (var i = 0; i < stateCodes.length; i++) {
  1189. for (var j = 0; j < stateCodes[i].length; j++) {
  1190. if (j === 2)
  1191. worksheet.rows(i).cells(j).value(parseInt(stateCodes[i][j]));
  1192. else
  1193. worksheet.rows(i).cells(j).value(stateCodes[i][j]);
  1194. }
  1195. }
  1196. },
  1197.  
  1198. refine: function(items, flag) {
  1199. var self = this,
  1200. result = "";
  1201. switch(flag) {
  1202. case "acc-num":
  1203. case "type":
  1204. case "name":
  1205. case "employer":
  1206. result = items[0];
  1207.  
  1208. for(var i = 1; i < items.length; i++) {
  1209. if (items[i].length > result.length)
  1210. result = items[i];
  1211. }
  1212. break;
  1213.  
  1214. case "lates":
  1215. result = parseInt(items[0] || 0);
  1216. for (var i = 0; i < items.length; i++) {
  1217. if (parseInt(items[i].substr(1)) > result) {
  1218. result = parseInt(items[i].substr(1));
  1219. }
  1220. }
  1221. break;
  1222.  
  1223. case "balance":
  1224. case "limit":
  1225. case "payment":
  1226. result = parseInt(items[0].substr(1) || 0);
  1227. for (var i = 0; i < items.length; i++) {
  1228. if (parseInt(items[i].substr(1)) > result) {
  1229. result = parseInt(items[i].substr(1));
  1230. }
  1231. }
  1232. break;
  1233.  
  1234. case "prev-addr":
  1235. result = [items[0]];
  1236. for (var i = 1; i < items.length; i++) {
  1237. if (items.indexOf(items[i]) !== -1) {
  1238. result.push(items[i]);
  1239. }
  1240. }
  1241. break;
  1242.  
  1243. default:
  1244. result = items[0];
  1245. for (var i = 1; i < items.length; i++) {
  1246. if (!result) {
  1247. result = items[i];
  1248. } else {
  1249. break;
  1250. }
  1251. }
  1252. break;
  1253. }
  1254.  
  1255. return result;
  1256. },
  1257.  
  1258. getMoreInfo: function() {
  1259. var self = this,
  1260. accounts = self.accounts;
  1261.  
  1262. self.curItem = self.accounts.shift();
  1263.  
  1264. if (self.curItem) {
  1265. self.saveState();
  1266. chrome.tabs.create({url: self.curItem.detailViewLink}, function(tab) {
  1267. console.log(self.curItem);
  1268. });
  1269. } else {
  1270. self.curItem = {};
  1271. self.stop();
  1272. }
  1273. }
  1274. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!