name:
src/Utilities/TypeConverter.ts
-rw-r--r--
28248
1import * as moment from "moment";
2import {
3 RefError,
4 ValueError,
5 DivZeroError
6} from "../Errors";
7import {
8 DateRegExBuilder
9} from "./DateRegExBuilder";
10import {
11 Cell
12} from "../Cell";
13
14const MONTHDIG_DAYDIG = DateRegExBuilder.DateRegExBuilder()
15 .start()
16 .MM().FLEX_DELIMITER().DD_W_SPACE().OPTIONAL_TIMESTAMP_CAPTURE_GROUP()
17 .end()
18 .build();
19const YEAR_MONTHDIG_DAY = DateRegExBuilder.DateRegExBuilder()
20 .start()
21 .OPTIONAL_DAYNAME().OPTIONAL_COMMA().YYYY().FLEX_DELIMITER_LOOSEDOT().MM().FLEX_DELIMITER_LOOSEDOT().DD_W_SPACE().OPTIONAL_TIMESTAMP_CAPTURE_GROUP()
22 .end()
23 .build();
24const MONTHDIG_DAY_YEAR = DateRegExBuilder.DateRegExBuilder()
25 .start()
26 .OPTIONAL_DAYNAME().OPTIONAL_COMMA().MM().FLEX_DELIMITER_LOOSEDOT().DD().FLEX_DELIMITER_LOOSEDOT().YYYY14_W_SPACE().OPTIONAL_TIMESTAMP_CAPTURE_GROUP()
27 .end()
28 .build();
29const DAY_MONTHNAME_YEAR = DateRegExBuilder.DateRegExBuilder()
30 .start()
31 .OPTIONAL_DAYNAME().OPTIONAL_COMMA().DD().FLEX_DELIMITER_LOOSEDOT().MONTHNAME().FLEX_DELIMITER_LOOSEDOT().YYYY14_W_SPACE().OPTIONAL_TIMESTAMP_CAPTURE_GROUP()
32 .end()
33 .build();
34const MONTHNAME_DAY_YEAR = DateRegExBuilder.DateRegExBuilder()
35 .start()
36 .OPTIONAL_DAYNAME().OPTIONAL_COMMA().MONTHNAME().FLEX_DELIMITER_LOOSEDOT().DD().FLEX_DELIMITER_LOOSEDOT().YYYY14_W_SPACE().OPTIONAL_TIMESTAMP_CAPTURE_GROUP()
37 .end()
38 .build();
39const YEAR_MONTHDIG = DateRegExBuilder.DateRegExBuilder()
40 .start()
41 .OPTIONAL_DAYNAME().OPTIONAL_COMMA().YYYY14().FLEX_DELIMITER().MM_W_SPACE().OPTIONAL_TIMESTAMP_CAPTURE_GROUP()
42 .end()
43 .build();
44const MONTHDIG_YEAR = DateRegExBuilder.DateRegExBuilder()
45 .start()
46 .OPTIONAL_DAYNAME().OPTIONAL_COMMA().MM().FLEX_DELIMITER().YYYY14_W_SPACE().OPTIONAL_TIMESTAMP_CAPTURE_GROUP()
47 .end()
48 .build();
49const YEAR_MONTHNAME = DateRegExBuilder.DateRegExBuilder()
50 .start()
51 .OPTIONAL_DAYNAME().OPTIONAL_COMMA().YYYY14().FLEX_DELIMITER().MONTHNAME_W_SPACE().OPTIONAL_TIMESTAMP_CAPTURE_GROUP()
52 .end()
53 .build();
54const MONTHNAME_YEAR = DateRegExBuilder.DateRegExBuilder()
55 .start()
56 .OPTIONAL_DAYNAME().OPTIONAL_COMMA().MONTHNAME().FLEX_DELIMITER().YYYY2_OR_4_W_SPACE().OPTIONAL_TIMESTAMP_CAPTURE_GROUP()
57 .end()
58 .build();
59// For reference: https://regex101.com/r/47GARA/1/
60const TIMESTAMP = DateRegExBuilder.DateRegExBuilder()
61 .start()
62 .TIMESTAMP_UNITS_CAPTURE_GROUP()
63 .end()
64 .build();
65// The first year to use when calculating the number of days in a date
66const FIRST_YEAR = 1900;
67// The year 2000.
68const Y2K_YEAR = 2000;
69
70function isUndefined(x) {
71 return x === undefined;
72}
73function isDefined(x) {
74 return x !== undefined;
75}
76
77/**
78 * Matches a timestamp string, adding the units to the moment passed in.
79 * @param timestampString to parse. ok formats: "10am", "10:10", "10:10am", "10:10:10", "10:10:10am", etc.
80 * @param momentToMutate to mutate
81 * @returns {Moment} mutated and altered.
82 */
83function matchTimestampAndMutateMoment(timestampString : string, momentToMutate: moment.Moment) : moment.Moment {
84 let matches = timestampString.match(TIMESTAMP);
85 if (matches && matches[1] !== undefined) { // 10am
86 let hours = parseInt(matches[2]);
87 if (hours > 12) {
88 throw new Error();
89 }
90 momentToMutate.add(hours, 'hours');
91 } else if (matches && matches[6] !== undefined) { // 10:10
92 let hours = parseInt(matches[7]);
93 let minutes = parseInt(matches[8]);
94 momentToMutate.add(hours, 'hours').add(minutes, 'minutes');
95 } else if (matches && matches[11] !== undefined) { // 10:10am
96 let hours = parseInt(matches[13]);
97 let minutes = parseInt(matches[14]);
98 let pmTrue = (matches[16].toLowerCase() === "pm");
99 if (hours > 12) {
100 throw new Error();
101 }
102 if (pmTrue) {
103 // 12pm is just 0am, 4pm is 16, etc.
104 momentToMutate.set('hours', hours === 12 ? hours : 12 + hours);
105 } else {
106 if (hours !== 12) {
107 momentToMutate.set('hours', hours);
108 }
109 }
110 momentToMutate.add(minutes, 'minutes');
111 } else if (matches && matches[17] !== undefined) { // 10:10:10
112 let hours = parseInt(matches[19]);
113 let minutes = parseInt(matches[20]);
114 let seconds = parseInt(matches[21]);
115 momentToMutate.add(hours, 'hours').add(minutes, 'minutes').add(seconds, 'seconds');
116 } else if (matches && matches[23] !== undefined) { // // 10:10:10am
117 let hours = parseInt(matches[25]);
118 let minutes = parseInt(matches[26]);
119 let seconds = parseInt(matches[27]);
120 let pmTrue = (matches[28].toLowerCase() === "pm");
121 if (hours > 12) {
122 throw new Error();
123 }
124 if (pmTrue) {
125 // 12pm is just 0am, 4pm is 16, etc.
126 momentToMutate.set('hours', hours === 12 ? hours : 12 + hours);
127 } else {
128 if (hours !== 12) {
129 momentToMutate.set('hours', hours);
130 }
131 }
132 momentToMutate.add(minutes, 'minutes').add(seconds, 'seconds');
133 } else {
134 throw new Error();
135 }
136 return momentToMutate;
137}
138
139/**
140 * Static class of helpers used to convert let ious types to each other.
141 */
142class TypeConverter {
143
144 public static ORIGIN_MOMENT = moment.utc([1899, 11, 30]).startOf("day");
145 private static SECONDS_IN_DAY = 86400;
146
147
148 /**
149 * Converts a datetime string to a moment object. Will return undefined if the string can't be converted.
150 * @param {string} timeString - string to parse and convert.
151 * @returns {moment.Moment}
152 */
153 static stringToMoment(timeString : string) : moment.Moment {
154 let m = TypeConverter.parseStringToMoment(timeString);
155 if (m === undefined || !m.isValid()) {
156 return undefined;
157 }
158 return m;
159 }
160
161 /**
162 * Converts a time-formatted string to a number between 0 and 1, exclusive on 1.
163 * @param timeString
164 * @returns {number} representing time of day
165 */
166 static stringToTimeNumber(timeString: string) : number {
167 let m;
168 try {
169 m = matchTimestampAndMutateMoment(timeString, moment.utc([FIRST_YEAR]).startOf("year"));
170 } catch (e) {
171 m = TypeConverter.parseStringToMoment(timeString);
172 if (m === undefined || !m.isValid()) {
173 throw new Error();
174 }
175 }
176 // If the parsing didn't work, try parsing as timestring alone
177 return (3600 * m.hours() + 60 * m.minutes() + m.seconds()) / 86400;
178 }
179
180 /**
181 * Parses a string returning a moment that is either valid, invalid or undefined.
182 * @param dateString to parse.
183 * @returns {moment}
184 */
185 private static parseStringToMoment(dateString : string) : moment.Moment {
186 let m;
187
188 /**
189 * Creates moment object from years, months and days.
190 * @param years of moment
191 * @param months of moment in number or string format (eg: January)
192 * @param days of moment
193 * @returns {Moment} created moment
194 */
195 function createMoment(years, months, days) : moment.Moment {
196 let actualYear = years;
197 if (years >= 0 && years < 30) {
198 actualYear = Y2K_YEAR + years;
199 } else if (years >= 30 && years < 100) {
200 actualYear = FIRST_YEAR + years;
201 }
202 let tmpMoment = moment.utc([actualYear]).startOf("year");
203 if (typeof months === "string") {
204 tmpMoment.month(months);
205 } else {
206 tmpMoment.set("months", months);
207 }
208 // If we're specifying more days than there are in this month
209 if (days > tmpMoment.daysInMonth() - 1) {
210 throw new Error();
211 }
212 return tmpMoment.add(days, 'days');
213 }
214
215 // Check MONTHDIG_DAYDIG, MM(fd)DD, '01/06'
216 // NOTE: Must come before YEAR_MONTHDIG matching.
217 if (m === undefined) {
218 let matches = dateString.match(MONTHDIG_DAYDIG);
219 if (matches && matches.length >= 10) {
220 let months = parseInt(matches[1]) - 1; // Months are zero indexed.
221 let days = parseInt(matches[3]) - 1; // Days are zero indexed.
222 let tmpMoment = createMoment(moment.utc().get("years"), months, days);
223 if (matches[8] !== undefined) {
224 tmpMoment = matchTimestampAndMutateMoment(matches[8], tmpMoment);
225 }
226 m = tmpMoment
227 }
228 }
229
230 // Check YEAR_MONTHDIG, YYYY(fd)MM, '1992/06'
231 // NOTE: Must come before YEAR_MONTHDIG_DAY matching.
232 if (m === undefined) {
233 let matches = dateString.match(YEAR_MONTHDIG);
234 if (matches && matches.length >= 6) {
235 let years = parseInt(matches[3]);
236 let months = parseInt(matches[5]) - 1; // Months are zero indexed.
237 let tmpMoment = createMoment(years, months, 0);
238 if (matches[6] !== undefined) {
239 tmpMoment = matchTimestampAndMutateMoment(matches[6], tmpMoment);
240 }
241 m = tmpMoment;
242 }
243 }
244
245 // Check YEAR_MONTHDIG_DAY, YYYY(fd)MM(fd)DD, "1992/06/24"
246 if (m === undefined) {
247 let matches = dateString.match(YEAR_MONTHDIG_DAY);
248 if (matches && matches.length >= 8) {
249 // Check delimiters. If they're not the same, throw error.
250 if (matches[4].replace(/\s*/g, '') !== matches[6].replace(/\s*/g, '')) {
251 throw new Error();
252 }
253 let years = parseInt(matches[3]);
254 let months = parseInt(matches[5]) - 1; // Months are zero indexed.
255 let days = parseInt(matches[7]) - 1; // Days are zero indexed.
256 let tmpMoment = createMoment(years, months, days);
257 if (matches.length >= 9 && matches[8] !== undefined) {
258 tmpMoment = matchTimestampAndMutateMoment(matches[8], tmpMoment);
259 }
260 m = tmpMoment;
261 }
262 }
263
264 // Check MONTHDIG_YEAR, MM(fd)YYYY, '06/1992'
265 // NOTE: Must come before MONTHDIG_DAY_YEAR matching.
266 if (m === undefined) {
267 let matches = dateString.match(MONTHDIG_YEAR);
268 if (matches && matches.length >= 6) {
269 let years = parseInt(matches[5]);
270 let months = parseInt(matches[3]) - 1; // Months are zero indexed.
271 let tmpMoment = createMoment(years, months, 0);
272 if (matches[6] !== undefined) {
273 tmpMoment = matchTimestampAndMutateMoment(matches[6], tmpMoment);
274 }
275 m = tmpMoment;
276 }
277 }
278
279 // Check MONTHDIG_DAY_YEAR, MM(fd)DD(fd)YYYY, "06/24/1992"
280 if (m === undefined) {
281 let matches = dateString.match(MONTHDIG_DAY_YEAR);
282 if (matches && matches.length >= 8) {
283 // Check delimiters. If they're not the same, throw error.
284 if (matches[4].replace(/\s*/g, '') !== matches[6].replace(/\s*/g, '')) {
285 throw new Error();
286 }
287 let years = parseInt(matches[7]);
288 let months = parseInt(matches[3]) - 1; // Months are zero indexed.
289 let days = parseInt(matches[5]) - 1; // Days are zero indexed.
290 let tmpMoment = createMoment(years, months, days);
291 if (matches.length >= 9 && matches[8] !== undefined) {
292 tmpMoment = matchTimestampAndMutateMoment(matches[8], tmpMoment);
293 }
294 m = tmpMoment;
295 }
296 }
297
298 // Check MONTHNAME_YEAR, Month(fd)YYYY, 'Aug 1992'
299 // NOTE: Needs to come before DAY_MONTHNAME_YEAR matching.
300 if (m === undefined) {
301 let matches = dateString.match(MONTHNAME_YEAR);
302 if (matches && matches.length >= 6) {
303 let years = parseInt(matches[5]);
304 let monthName = matches[3];
305 let tmpMoment = createMoment(years, monthName, 0);
306 if (matches[6] !== undefined) {
307 tmpMoment = matchTimestampAndMutateMoment(matches[6], tmpMoment);
308 }
309 m = tmpMoment;
310 }
311 }
312
313 // Check MONTHNAME_DAY_YEAR, Month(fd)DD(fd)YYYY, 'Aug 19 2020'
314 if (m === undefined) {
315 let matches = dateString.match(MONTHNAME_DAY_YEAR);
316 if (matches && matches.length >= 8) {
317 // Check delimiters. If they're not the same, throw error.
318 if (matches[4].replace(/\s*/g, '') !== matches[6].replace(/\s*/g, '')) {
319 throw new Error();
320 }
321 let years = parseInt(matches[7]);
322 let monthName = matches[3];
323 let days = parseInt(matches[5]) - 1; // Days are zero indexed.
324 let tmpMoment = createMoment(years, monthName, days);
325 if (matches.length >= 9 && matches[8] !== undefined) {
326 tmpMoment = matchTimestampAndMutateMoment(matches[8], tmpMoment);
327 }
328 m = tmpMoment;
329 }
330 }
331
332 // Check DAY_MONTHNAME_YEAR, DD(fd)Month(fd)YYYY, '24/July/1992'
333 if (m === undefined) {
334 let matches = dateString.match(DAY_MONTHNAME_YEAR);
335 if (matches && matches.length >= 8) {
336 let years = parseInt(matches[7]);
337 let monthName = matches[5];
338 let days = parseInt(matches[3]) - 1; // Days are zero indexed.
339 let firstDelimiter = matches[4].replace(/\s*/g, '');
340 let secondDelimiter = matches[6].replace(/\s*/g, '');
341 // Check delimiters. If they're not the same, and the first one isn't a space, throw error.
342 if (firstDelimiter !== secondDelimiter && firstDelimiter !== "") {
343 throw new Error();
344 }
345 let tmpMoment = createMoment(years, monthName, days);
346 if (matches.length >= 9 && matches[8] !== undefined) {
347 tmpMoment = matchTimestampAndMutateMoment(matches[8], tmpMoment);
348 }
349 m = tmpMoment;
350 }
351 }
352
353 // Check YEAR_MONTHNAME, YYYY(fd)Month, '1992/Aug'
354 if (m === undefined) {
355 let matches = dateString.match(YEAR_MONTHNAME);
356 if (matches && matches.length >= 6) {
357 let years = parseInt(matches[3]);
358 let monthName = matches[5];
359 let tmpMoment = createMoment(years, monthName, 0);
360 if (matches[6] !== undefined) {
361 tmpMoment = matchTimestampAndMutateMoment(matches[6], tmpMoment);
362 }
363 m = tmpMoment;
364 }
365 }
366 return m;
367 }
368
369 /**
370 * Parses a string as a date number. Throws error if parsing not possible.
371 * @param dateString to parse
372 * @returns {number} resulting date
373 */
374 public static stringToDateNumber(dateString : string) : number {
375 // m will be set and valid or invalid, or will remain undefined
376 let m;
377 try {
378 m = TypeConverter.parseStringToMoment(dateString);
379 } catch (e) {
380 throw new ValueError("DATEVALUE parameter '" + dateString + "' cannot be parsed to date/time.");
381 }
382 if (m === undefined || !m.isValid()) {
383 throw new ValueError("DATEVALUE parameter '" + dateString + "' cannot be parsed to date/time.");
384 }
385 return TypeConverter.momentToDayNumber(m.set('hours', 0).set('minutes', 0).set('seconds', 0));
386 }
387
388 /**
389 * Converts strings to numbers, returning undefined if string cannot be parsed to number. Examples: "100", "342424",
390 * "10%", "33.213131", "41.1231", "10e+1", "10E-1", "10.44E1", "-$9.29", "+$9.29", "1,000.1", "2000,000,000".
391 * For reference see: https://regex101.com/r/PwghnF/9/
392 * @param value to parse.
393 * @returns {number} or undefined
394 */
395 public static stringToNumber(value: string) : number {
396 const NUMBER_REGEX = /^ *([\+/-])? *(\$)? *([\+/-])? *((\d+)?(,\d{3})?(,\d{3})?(,\d{3})?(,\d{3})?)? *(\.)? *(\d*)? *(e|E)? *([\+/-])? *(\d*)? *(%)? *$/;
397
398 let matches = value.match(NUMBER_REGEX);
399 if (matches !== null) {
400 let firstSign = matches[1];
401 let currency = matches[2];
402 let secondSign = matches[3];
403 let wholeNumberWithCommas = matches[4];
404 let decimalPoint = matches[10];
405 let decimalNumber = matches[11];
406 let sciNotation = matches[12];
407 let sciNotationSign = matches[13];
408 let sciNotationFactor = matches[14];
409 let percentageSign = matches[15];
410
411 // Number is not valid if it is a currency and in scientific notation.
412 if (isDefined(currency) && isDefined(sciNotation)) {
413 return;
414 }
415 // Number is not valid if there are two signs.
416 if (isDefined(firstSign) && isDefined(secondSign)) {
417 return;
418 }
419 // Number is not valid if we have 'sciNotation' but no 'sciNotationFactor'
420 if (isDefined(sciNotation) && isUndefined(sciNotationFactor)) {
421 return;
422 }
423 let activeSign;
424 if (isUndefined(firstSign) && isUndefined(secondSign)) {
425 activeSign = "+";
426 } else if (!isUndefined(firstSign)) {
427 activeSign = firstSign;
428 } else {
429 activeSign = secondSign;
430 }
431 let x;
432 if (isDefined(wholeNumberWithCommas)) {
433 if (isDefined(decimalNumber) && isDefined(decimalNumber)) {
434 x = parseFloat(activeSign + wholeNumberWithCommas.split(",").join("") + decimalPoint + decimalNumber);
435 } else {
436 x = parseFloat(activeSign + wholeNumberWithCommas.split(",").join(""));
437 }
438 } else {
439 x = parseFloat(activeSign + "0" + decimalPoint + decimalNumber);
440 }
441
442 if (isDefined(sciNotation) && isDefined(sciNotationFactor)) {
443 sciNotationSign = isDefined(sciNotationSign) ? sciNotationSign : "+";
444 // x + "e" + "-" + "10"
445 x = parseFloat(x.toString() + sciNotation.toString() + "" + sciNotationSign.toString() + sciNotationFactor.toString())
446 }
447 if (!isUndefined(percentageSign)) {
448 x = x * 0.01;
449 }
450 return x;
451 } else {
452 try {
453 return TypeConverter.stringToDateNumber(value);
454 } catch (_) {
455 return;
456 }
457 }
458 }
459
460 /**
461 * Converts any value to an inverted number or throws an error if it cannot coerce it to the number type
462 * @param value to convert
463 * @returns {number} to return. Will always return a number or throw an error. Never returns undefined.
464 */
465 public static valueToInvertedNumber(value: any) {
466 return TypeConverter.valueToNumber(value) * (-1);
467 }
468
469 /**
470 * Converts any value to a number or throws an error if it cannot coerce it to the number type
471 * @param value to convert
472 * @returns {number} to return. Will always return a number or throw an error. Never returns undefined.
473 */
474 public static valueToNumber(value : any) {
475 if (value instanceof Cell) {
476 if (value.isBlank()) {
477 return 0;
478 } else {
479 if (value.hasError()) {
480 throw value.getError();
481 }
482 value = value.getValue();
483 }
484 }
485 if (typeof value === "number") {
486 return value;
487 } else if (typeof value === "string") {
488 if (value === "") {
489 return 0;
490 }
491 let n = TypeConverter.stringToNumber(value);
492 if (n === undefined) {
493 throw new ValueError("Function expects number values, but is text and cannot be coerced to a number.");
494 }
495 return n;
496 } else if (typeof value === "boolean") {
497 return value ? 1 : 0;
498 }
499 return 0;
500 }
501
502 /**
503 * Converts any value to a number, defaulting to 0 value in cases in which it cannot coerce it to a number type
504 * @param value to conver
505 * @returns {number} to return. Will always return a number or 0.
506 */
507 public static valueToNumberGracefully(value: any) : number {
508 try {
509 return TypeConverter.valueToNumber(value);
510 } catch (e) {
511 return 0;
512 }
513 }
514
515 /**
516 * Converts any value to a boolean or throws an error if it cannot coerce it to the boolean type.
517 * @param value to convert
518 * @returns {boolean} to return.
519 */
520 public static valueToBoolean(value: any) {
521 if (value instanceof Cell) {
522 if (value.isBlank()) {
523 return false;
524 } else {
525 if (value.hasError()) {
526 throw value.getError();
527 }
528 value = value.getValue();
529 }
530 }
531 if (typeof value === "number") {
532 return value !== 0;
533 } else if (typeof value === "string") {
534 throw new ValueError("___ expects boolean values. But '" + value + "' is a text and cannot be coerced to a boolean.")
535 } else if (typeof value === "boolean") {
536 return value;
537 }
538 }
539 /**
540 * Convert a value to string.
541 * @param value of any type, including array. array cannot be empty.
542 * @returns {string} string representation of value
543 */
544 public static valueToString(value: any) : string {
545 if (value instanceof Cell) {
546 if (value.isBlank()) {
547 return "";
548 } else {
549 if (value.hasError()) {
550 throw value.getError();
551 }
552 return value.getValue().toString();
553 }
554 } else if (typeof value === "number") {
555 return value.toString();
556 } else if (typeof value === "string") {
557 return value;
558 } else if (typeof value === "boolean") {
559 return value ? "TRUE" : "FALSE";
560 }
561 }
562
563 /**
564 * Converts a value to a time number; a value between 0 and 1, exclusive on 1.
565 * @param value to convert
566 * @returns {number} representing a time value
567 */
568 public static valueToTimestampNumber(value: any) : number {
569 if (value instanceof Cell) {
570 if (value.isBlank()) {
571 return 0;
572 } else {
573 if (value.hasError()) {
574 throw value.getError();
575 }
576 return value.getValue();
577 }
578 } else if (typeof value === "number") {
579 return value;
580 } else if (typeof value === "string") {
581 if (value == "") {
582 return 0;
583 }
584 try {
585 return TypeConverter.stringToTimeNumber(value)
586 } catch (e) {
587 if (TypeConverter.canCoerceToNumber(value)) {
588 return TypeConverter.valueToNumber(value);
589 }
590 throw new ValueError("___ expects number values. But '" + value + "' is a text and cannot be coerced to a number.")
591 }
592 } else if (typeof value === "boolean") {
593 return 0; // value between 0 and 1, exclusive on 1.
594 }
595 return 0;
596 }
597
598 /**
599 * Returns true if string is number format.
600 * @param str to check
601 * @returns {boolean}
602 */
603 public static isNumber(str : string) {
604 return str.match("\s*(\d+\.?\d*$)|(\.\d+$)|([0-9]{2}%$)|([0-9]{1,}$)") !== null;
605 }
606
607 /**
608 * Returns true if we can coerce it to the number type.
609 * @param value to coerce
610 * @returns {boolean} if could be coerced to a number
611 */
612 public static canCoerceToNumber(value: any) : boolean {
613 if (typeof value === "number" || typeof value === "boolean" || value instanceof Cell) {
614 return true;
615 } else if (typeof value === "string") {
616 return TypeConverter.isNumber(value);
617 }
618 return false;
619 }
620
621 /**
622 * Takes any input type and will throw a REF_ERROR or coerce it into a number.
623 * @param input to attempt to coerce into a number
624 * @returns {number} number representation of the input
625 */
626 public static firstValueAsNumber(input: any) : number {
627 if (input instanceof Array) {
628 if (input.length === 0) {
629 throw new RefError("Reference does not exist.");
630 }
631 return TypeConverter.firstValueAsNumber(input[0]);
632 }
633 return TypeConverter.valueToNumber(input);
634 }
635
636 /**
637 * Takes any input type and will throw a REF_ERROR or coerce it into a string.
638 * @param input to attempt to coerce into a string
639 * @returns {number} number representation of the input
640 */
641 public static firstValueAsString(input: any) : string {
642 if (input instanceof Array) {
643 if (input.length === 0) {
644 throw new RefError("Reference does not exist.");
645 }
646 return TypeConverter.firstValueAsString(input[0]);
647 }
648 return TypeConverter.valueToString(input);
649 }
650
651
652 /**
653 * Returns the first value that is not of the type array. Will throw RefError if any empty arrays are passed in.
654 * @param input to retrieve first value of
655 * @returns {any} any non-array value.
656 */
657 public static firstValue(input: any) : any {
658 if (input instanceof Array) {
659 if (input.length === 0) {
660 throw new RefError("Reference does not exist.");
661 }
662 return TypeConverter.firstValue(input[0]);
663 }
664 return input;
665 }
666
667 /**
668 * Takes any input type and will throw a REF_ERROR or coerce it into a string.
669 * @param input to attempt to coerce into a string
670 * @returns {number} number representation of the input
671 */
672 public static firstValueAsBoolean(input: any): boolean {
673 if (input instanceof Array) {
674 if (input.length === 0) {
675 throw new RefError("Reference does not exist.");
676 }
677 return TypeConverter.firstValueAsBoolean(input[0]);
678 }
679 return TypeConverter.valueToBoolean(input);
680 }
681
682 /**
683 * Takes the input type and will throw a REF_ERROR or coerce it into a date number
684 * @param input input to attempt to coerce to a date number
685 * @param coerceBoolean should a boolean be converted
686 * @returns {number} representing a date
687 */
688 public static firstValueAsDateNumber(input: any, coerceBoolean?: boolean) : number {
689 coerceBoolean = coerceBoolean || false;
690 if (input instanceof Array) {
691 if (input.length === 0) {
692 throw new RefError("Reference does not exist.");
693 }
694 return TypeConverter.firstValueAsDateNumber(input[0], coerceBoolean || false);
695 }
696 return TypeConverter.valueToDateNumber(input, coerceBoolean);
697 }
698
699 /**
700 * Takes the input type and will throw a REF_ERROR or coerce it into a time number
701 * @param input input to attempt to coerce to a time number
702 * @returns {number} representing time of day
703 */
704 public static firstValueAsTimestampNumber(input : any) : number {
705 if (input instanceof Array) {
706 if (input.length === 0) {
707 throw new RefError("Reference does not exist.");
708 }
709 return TypeConverter.firstValueAsTimestampNumber(input[0]);
710 }
711 return TypeConverter.valueToTimestampNumber(input);
712 }
713
714 /**
715 * Convert a value to date number if possible.
716 * @param value to convert
717 * @param coerceBoolean should a boolean be converted
718 * @returns {number} date
719 */
720 public static valueToDateNumber(value: any, coerceBoolean?: boolean) : number {
721 if (value instanceof Cell) {
722 if (value.isBlank()) {
723 return 0;
724 } else {
725 if (value.hasError()) {
726 throw value.getError();
727 }
728 return value.getValue();
729 }
730 } else if (typeof value === "number") {
731 return value;
732 } else if (typeof value === "string") {
733 try {
734 return TypeConverter.stringToDateNumber(value);
735 } catch (e) {
736 if (TypeConverter.canCoerceToNumber(value)) {
737 return TypeConverter.valueToNumber(value);
738 }
739 throw new ValueError("___ expects date values. But '" + value + "' is a text and cannot be coerced to a date.")
740 }
741 } else if (typeof value === "boolean") {
742 if (coerceBoolean) {
743 return value ? 1 : 0;
744 }
745 throw new ValueError("___ expects date values. But '" + value + "' is a boolean and cannot be coerced to a date.")
746 }
747 }
748
749 /**
750 * Converts a moment to a date number.
751 * @param m to convert
752 * @returns {number} date
753 */
754 public static momentToNumber(m : moment.Moment) : number {
755 return m.diff(this.ORIGIN_MOMENT, "seconds") / this.SECONDS_IN_DAY;
756 }
757
758 /**
759 * Converts a moment to a date number, floored to the whole day date.
760 * @param m to convert
761 * @returns {number} date
762 */
763 public static momentToDayNumber(m : moment.Moment) : number {
764 return Math.floor(TypeConverter.momentToNumber(m));
765 }
766
767 /**
768 * Converts a number to moment.
769 * @param n to convert
770 * @returns {Moment} date
771 */
772 public static numberToMoment(n : number) : moment.Moment {
773 return moment.utc(TypeConverter.ORIGIN_MOMENT).add(n, "days");
774 }
775
776 /**
777 * Converts a number to moment while preserving the decimal part of the number.
778 * @param n to convert
779 * @returns {Moment} date
780 */
781 public static decimalNumberToMoment(n : number) : moment.Moment {
782 return moment.utc(TypeConverter.ORIGIN_MOMENT).add(n * TypeConverter.SECONDS_IN_DAY * 1000, "milliseconds");
783 }
784
785 /**
786 * Using timestamp units, create a time number between 0 and 1, exclusive on end.
787 * @param hours
788 * @param minutes
789 * @param seconds
790 * @returns {number} representing time of day between 0 and 1, exclusive on end.
791 */
792 public static unitsToTimeNumber(hours: number, minutes: number, seconds: number): number {
793 let v = (((hours % 24) * 60 * 60) + ((minutes) * 60) + (seconds)) / 86400;
794 return v % 1;
795 }
796}
797
798/**
799 * Catches divide by zero situations and throws them as errors
800 * @param n number to check
801 * @returns {number} n as long as it's not zero.
802 */
803let checkForDevideByZero = function(n : number) : number {
804 n = +n; // Coerce to number.
805 if (!n) { // Matches +0, -0, NaN
806 throw new DivZeroError("Evaluation of function caused a divide by zero error.");
807 }
808 return n;
809};
810
811export {
812 TypeConverter,
813 checkForDevideByZero
814}