Converting minutes into days:hours:minutes

One of my clients wanted a report that was based on order statuses (as we know now, TODOLIST), displaying the time spent in each status in the format DD:mm:hh. The field TODOLIST.DURATION holds this time in minutes and the task is to convert it.

One might ask, what is the problem with using DTOA to convert the number? It seems that if one does not display a month, then the number of days in the current month is used. For 51936, a number that I had, the expression DTOA (51936, 'DD:hh:mm') gives the result 06:01:36, because there aren't more than 30 days in the current month (the expression is equivalent to 36 days and some change).

The form DOCTODOLISTLOG purports to give the number of days and number of hours in separate columns but this isn't what the client wants and it's also not exact (a fraction of hours is given, not the number of the minutes). The number of days is defined as (0.0 + TODOLIST.DURATION)/ 1440.0. I don't understand why the number is promoted to a real, as TODOLIST.DURATION / 1440 gives the same answer (36). But I'm not taking a chance, so I will use the same expression. As I want an integral number of days, I need to use ITOA, but this won't work on a real, so first I have to round the number, giving the expression (ITOA (ROUND((0.0 + TODOLIST.DURATION)/ 1440.0), 2). The final 2 is to ensure that the result will have at least two digits, where leading zeroes are added as necessary. If I had kept the number as a real and used RTOA, then I could have chopped off any remainder but there would be no leading zeroes.

What about the hours and minutes? The way that I did this in a report was first to define a value that is not displayed: TODOLIST.DURATION MOD 1440. This is done in order to simplify the remaining calculations. Using 51936 as input, this expression results in 96; clearly this is equivalent to 1 hour and 36 minutes, or #207 / 60 and #207 mod 60, where #207 is the number of the expression.

Thus the final string is given by the following formula

STRCAT (ITOA (ROUND((0.0 + TODOLIST.DURATION)/ 1440.0), 2), ':',

ITOA (#207 / 60, 2), ':', ITOA (#207 MOD 60, 2))

If TODOLIST.DURATION is 51936, then this monstrosity results in the string 36:01:36  (yes, the client wanted that if the duration were over 12 days then it would appear in red - but that's another story).

A better way of calculating the dollar value of sales invoices

I wrote the other day about code to calculate the dollar value of sales invoices. I had written code that iterated by means of a cursor over the lines of an invoice and calculated the dollar value of each line, then summing these values. I was sure that there was a better way of doing this but I hadn't managed to find this better way. While I was describing the problem, I saw the quick way of getting the dollar value, using values in the connected transaction. This is yet another case of rubber duck debugging: describing the problem in words leads to a solution. Here is the code that is stored as a trigger in the 'func' form.

/***********************************************************
TEST_CNVINV2DOLLAR - Convert an invoice's total to dollars
11/11/23: New, much simpler version that doesn't require a cursor
and so can be used multiple times in a container procedure.
SUB 6200 -
Input: :IV
Output: :QP
************************************************************/
SUB 6200;
:VAT62 = :SUM62 = :EXCHANGE62 = :QP = 0.0;
:TMP62 = 0E9;
:DEBIT62 = '\0';
SELECT INVOICES.VAT, INVOICES.DEBIT, FNCTRANS.SUM2,
FNCTRANS.EXCHANGE2
INTO :VAT62, :DEBIT62, :SUM62, :EXCHANGE62
FROM INVOICES, FNCTRANS
WHERE INVOICES.FNCTRANS = FNCTRANS.FNCTRANS
AND INVOICES.IV = :IV;
:TMP62 = :VAT62 / :EXCHANGE62;
:QP = (:DEBIT62 = 'C' ? -1 : 1) * (:SUM62 - :TMP62);
RETURN;

Short and simple.

More on converting a currency string to a real (introducing STRINDEX)

As an astute comment pointed out, the code that I presented the other day for converting a currency string (like $1,234.56) to a real would have problems if the string represented an amount greater than one million. I was going to write that Priority has no STRPOS function; if it had one, then locating the commas and removing them would be simpler than my laborious code. 

I have just discovered that whilst there is no STRPOS function, there is a function called STRINDEX that is more or less the same. So if there was a string like '$1,234,567.89', it could be turned into a string that can be turned into a real with the following code.

SUB 850;
/* :ASTRING will be something like '$1,234,567.89';
*/
:ASTRING = SUBSTR (:ASTRING, 2, 24); /* Lose the leading dollar */
:AREAL = '';
:RESULT = 0E9;
:POS = 0;
LABEL 851;
SELECT STRINDEX (:ASTRING, ',', 1) INTO :POS FROM DUMMY;
GOTO 852 WHERE :POS = 0;
:AREAL = STRCAT (:AREAL, SUBSTR (:ASTRING, 1, :POS - 1));
:ASTRING = SUBSTR (:ASTRING, :POS + 1, 24);
LOOP 851;
LABEL 852;
:AREAL = STRCAT (:AREAL, :ASTRING);
:RESULT = ATOR (:AREAL);
RETURN;
  

Note that the $ sign is liable to be converted into the procedure's name (see here). Fortunately, in my case, the currency sign is ₪ which is ignored by the preprocessor.

Now that I know about the existance of the function STRINDEX, I am liable to use it whenever necessary. I doubt that I am going to update existing code but I will use it in the future. 

It turns out that STRINDEX exists only from version 21 onwards - and of course, the client who needs this code is running version 20.

More on the undocumented function ATOR

A year and a half ago, I wrote about the undocumented function ATOR that takes a string and turns it into a real number with a decimal point. A couple of days ago, I was dismayed to learn that the function wasn't working properly with an Excel file that someone had sent intended as input for an interface that uses ATOR. After looking at the file for a while, I realised that every line had four fields in the following format $1,234.56, whereas previously the fields had held 1234.56. ATOR chokes on this new input and so I had to write a new function. First, though, some examples:

  SELECT ATOR ('$1,061.25') FROM DUMMY FORMAT; >> error
  SELECT ATOR ('1,061.25') FROM DUMMY FORMAT; >> 0.010000  Why?
  SELECT ATOR ('1061.25') FROM DUMMY FORMAT; >> 1061.250000
  

So in order to parse the field (that was being passed as a string), first I had to strip off the dollar sign. It wasn't clear at first whether the dollar sign was at the beginning or end of the string, but it turned out to be the beginning. This was easy to do. Then I looked for the decimal point: anything before this is a number and should be read as is, the two digits after this are the digits after the decimal point and should be multiplied by 0.01. 

But this didn't work properly, either. It seems that the comma was causing problems, so first I had to find where the comma was - if at all - and make allowance for this. The final code is as follows

SUB 850;
/* AREAL is something like $ 12,345.67 */
:RTMP = 0.0;
:AREAL = STRIND (:AREAL, 2, 10); /* lose the $ */
SELECT STRPIECE (:AREAL, '.', 2, 1) INTO :TMP FROM DUMMY;
:RTMP = ATOI (STRIND (:TMP, 1, 2)) * 0.01; /* fractional part */
SELECT STRPIECE (:AREAL, '.', 1, 1) INTO :TMP FROM DUMMY;
SELECT STRPIECE (:TMP, ',', 1, 1) INTO :THOUSAND FROM DUMMY;
GOTO 851 WHERE :THOUSAND = :TMP;
:RTMP = :RTMP + (ATOI (:THOUSAND) * 1000.0) +
ATOI (STRPIECE (:TMP, ',', 2, 1));
GOTO 852;
LABEL 851;
:RTMP = :RTMP + ATOI (:THOUSAND);
LABEL 852;
RETURN;
  

This isn't something that I'm proud of, but at least it works. Before the subroutine is called, there is a check to see whether the field holds an 'ordinary' real like 1234.56 - this is accomplished with the ISFLOAT function, and if this returns 1, then ATOR is used on the field. Otherwise the subroutine is called.

An undocumented function in Priority - ATOR

There have been times, especially when I am programming interfaces that read external files and turn them into Priority data, that I wish that there was an Ascii2Real function, presumably ATOR, in the same way that there is an Ascii2Integer function (ATOI) and an Integer2Ascii function (ITOA). We have RTOA but no ATOR.

I've overcome this in the past by writing a subroutine that receives a string holding a real number (:QUANT) and returns a real number (:RQUANT).

SUB 879;
:RQUANT = :TT = 0E9;
SELECT STRPIECE (:QUANT, '.', 1, 1) INTO :TMP FROM DUMMY;
:RQUANT = ATOI (:TMP) + 0.00000;
SELECT STRPIECE (:QUANT ,'.', 2, 1) INTO :TMP FROM DUMMY;
GOTO 102 WHERE :TMP = '';
/* This is the decimal part of the number. Figure out what the
divisor has to be by the length of the number: e.g. num=3, div=1;
num=30104, div = 5
*/
:TT = 0.00000 + ATOI (:TMP);
:DIV = 1;
:I = 0;
:LEN = STRLEN (:TMP);
LABEL 101;
:DIV = :DIV * 10;
:I = :I + 1;
LOOP 101 WHERE :I < :LEN;
:RQUANT = :RQUANT + (:TT / :DIV);
LABEL 102;
RETURN;

Whilst this code works, I would prefer a predefined function in Priority. Today I was looking at a very old program that I did not write and saw the use of the ATOR function! As I know that this old program has correct syntax - and is used at least ten times a day - I felt fairly safe in replacing my subroutine with a simple function call, :RQUANT = ATOR (:QUANT). I also checked the syntax in WINDBI; lo and behold, this function turns a string like '3.1234' into a real number with six digits of accuracy, 3.123400.

Why would this useful function be undocumented, especially as it has existed in Priority since 2006? I won't even try to guess.