query calculated fields procedure
Description
Assign a value to query calculated fields
Parameters
| oiReturnStatus | output | integer | Return status of the method. |
Internal usage
QadFinancials
program code (program1/bdaemonfunctions.p)
/* ================================================================================================================= */
/* Set the maximum number of records that we will count to state the number of waiting queue records to be processed */
/* by a single daemon. This is to avoid that we would loose to much time when we have to eg count a million records */
/* ================================================================================================================= */
assign viMaxNumberQueueWaitingToCount = 50000.
/* ====================================================================================== */
/* List the number of dynamic buffers etc that we need to go through the query-result-set */
/* ====================================================================================== */
create query vhQuery in widget-pool "non-persistent":U.
vhQuery:forward-only = yes.
vhQuery:set-buffers(ih_Buffer).
vhQuery:query-prepare("for each ":U + ih_Buffer:table).
vhQuery:query-open().
vhQuery:get-first().
/* ================================== */
/* Cycle through the query result set */
/* ================================== */
do while not vhQuery:query-off-end:
/* ======================= */
/* Last Started UTC / User */
/* ======================= */
if {&tiDaemonLastStartTime} <> ?
then {&tcCalcDaemonLastStartTimeUTC} = string ({&tiDaemonLastStartTime},"HH:MM:SS":U).
if {&tiLastStartedUsr_ID} <> "":U and
{&tiLastStartedUsr_ID} <> " ":U and
{&tiLastStoppedUsr_ID} <> 0 and
{&tiLastStartedUsr_ID} <> ?
then do :
find Usr where
Usr.Usr_ID = {&tiLastStartedUsr_ID}
no-lock no-error.
if available Usr
then assign {&tcCalcDaemonLastStartUserLogin} = Usr.UsrLogin.
else assign {&tcCalcDaemonLastStartUserLogin} = "?":U.
end. /* if {&tiLastStartedUsr_ID} <> "":U and */
else assign {&tcCalcDaemonLastStartUserLogin} = trim(#T-40'<system>':15(654364144)T-40#).
/* ======================= */
/* Last Stopped UTC / User */
/* ======================= */
if {&tiDaemonLastEndTime} <> ?
then {&tcCalcDaemonLastEndTimeUTC} = string ({&tiDaemonLastEndTime},"HH:MM:SS":U).
if {&tiLastStoppedUsr_ID} <> "":U and
{&tiLastStoppedUsr_ID} <> " ":U and
{&tiLastStoppedUsr_ID} <> 0 and
{&tiLastStoppedUsr_ID} <> ?
then do :
find Usr where
Usr.Usr_ID = {&tiLastStoppedUsr_ID}
no-lock no-error.
if available Usr
then assign {&tcCalcDaemonLastEndUserLogin} = Usr.UsrLogin.
else assign {&tcCalcDaemonLastEndUserLogin} = "?":U.
end. /* if {&tiLastStoppedUsr_ID} <> "":U and */
else assign {&tcCalcDaemonLastEndUserLogin} = trim(#T-17'<system>':15(654364144)T-17#).
/* ================ */
/* Daemon Name */
/* ================ */
assign {&tcCalcDaemonName} = if {&tcDaemonName} = {&BALANCEDAEMON}
then {&BALANCEDAEMON-TR}
else if {&tcDaemonName} = {&BUDGETDAEMON}
then {&BUDGETDAEMON-TR}
else if {&tcDaemonName} = {&CROSSCOMPANYDAEMON}
then {&CROSSCOMPANYDAEMON-TR}
else if {&tcDaemonName} = {&CUBEDAEMON}
then {&CUBEDAEMON-TR}
else if {&tcDaemonName} = {&HISTORYDAEMON}
then {&HISTORYDAEMON-TR}
else if {&tcDaemonName} = {&REPLICATIONDAEMON}
then {&REPLICATIONDAEMON-TR}
else if {&tcDaemonName} = {&REPORTDAEMON}
then {&REPORTDAEMON-TR}
else if {&tcDaemonName} = {&SCANDAEMON}
then {&SCANDAEMON-TR}
else if {&tcDaemonName} = {&TIMEOUTDAEMON}
then {&TIMEOUTDAEMON-TR}
else if {&tcDaemonName} = {&XMLDAEMON}
then {&XMLDAEMON-TR}
else if {&tcDaemonName} = {&EVENTDAEMON}
then {&EVENTDAEMON-TR}
else "?":U.
/* ==================================================================================== */
/* Status - Possible values: */
/* Not Configured: Main info (Login & OSCommand) is missing in the fcDaemon record */
/* Disabled: The NbrOfInstances of the daemon is zero */
/* Inactive: Simply based on the status of the daemon in the db */
/* Starting: Simply based on the status of the daemon in the db */
/* Stopping: Simply based on the status of the daemon in the db */
/* Running: The status in the db is running and the processes can be found */
/* Unknown: The status in the db is running but there is no process that can be found */
/* ==================================================================================== */
/* First check the statusses that can be simply checked against the db-content */
assign {&tcCalcDaemonStatus} = if {&tcDaemonLogin} = "":U or
{&tcDaemonLogin} = ? or
{&tcDaemonOsCommand} = "":U or
{&tcDaemonOsCommand} = ?
then {&DAEMONSTATUSXNOTINDB-NOTCONFIGURED-TR}
else if {&tiDaemonMaxNumberOfInstances} = 0
then {&DAEMONSTATUSXNOTINDB-DISABLED-TR}
else if {&tcDaemonStatus} = {&DAEMONSTATUS-INACTIVE}
then {&DAEMONSTATUS-INACTIVE-TR}
else if {&tcDaemonStatus} = {&DAEMONSTATUS-STARTING}
then {&DAEMONSTATUS-STARTING-TR}
else if {&tcDaemonStatus} = {&DAEMONSTATUS-STOPPING}
then {&DAEMONSTATUS-STOPPING-TR}
else "":U.
/* Check the Processes of the daemon to distinguish Running from Unknown */
if {&tcDaemonStatus} = {&DAEMONSTATUS-RUNNING} and
({&tcCalcDaemonStatus} = "":U or
{&tcCalcDaemonStatus} = ?)
then do :
/* Verify if the processes are active and valid to distinguish Running from Unknown */
assign vlAllValidProcesses = true.
do viProcessCounter = 1 to num-entries({&tcDaemonProcessIDs}):
/* Get the Server-name and the Process ID */
assign viProcessId = 0.
if num-entries (entry(viProcessCounter,{&tcDaemonProcessIDs}),":") = 2
then assign vcProcessHostname = entry(1,entry(viProcessCounter,{&tcDaemonProcessIDs}),":")
viProcessID = integer(entry(2,entry(viProcessCounter,{&tcDaemonProcessIDs}),":")) no-error.
else assign viProcessID = integer(entry(viProcessCounter,{&tcDaemonProcessIDs})) no-error.
/* Verify the correctness of the content in the db-field */
if error-status:error or
viProcessID = 0 or
viProcessID = ?
then do :
assign vlAllValidProcesses = false.
leave.
end. /* if error-status:error or */
/* Check if the process is still active - neglect ocMessage when calling ProcessIsValid as here we do not care about the underlying reason */
<M-88 run Main (input viSessionId (iiSessionId)) in TDaemonUtility>
<M-25 run ProcessIsValid
(input {&tcDaemonName} (icDaemonName),
input vcProcessHostname (icProcessHostname),
input viProcessID (iiProcessNr),
input {&tcDaemonAppserver} (icDaemonAppserverURL),
output vlAllValidProcesses (olValid),
output vcMessage (ocMessage),
output viFcReturnSuper (oiReturnStatus)) in TDaemonUtility>
run gipr_DeleteProcedure in vhFcComponent.
delete procedure vhFcComponent.
if viFcReturnSuper < 0 or
vlAllValidProcesses = false
then leave.
end. /* do viProcessCounter = 1 to num-entries({&tcDaemonProcessIDs}): */
/* Use vlAllValidProcesses to distinguish the statusses Running from Unknown */
assign {&tcCalcDaemonStatus} = if vlAllValidProcesses <> true
then {&DAEMONSTATUSXNOTINDB-UNKNOWN-TR}
else {&DAEMONSTATUS-RUNNING-TR}.
end. /* if {&tcDaemonStatus} = {&DAEMONSTATUS-RUNNING} and */
/* ===================================================================================================== */
/* The next calculated fields are all based upon the data in table fcDaemonQueue of the fcDaemon-record */
/* so we will read here all fcDaemonQueue-records of the current fcDaemon record and use them further on */
/* to calculate the different numbers about the processed, waiting and failed ones */
/* ===================================================================================================== */
/* Note we will use direct db-access due to the nubmer of fcDaemonQueue-records that can be very high */
/* ===================================================================================================== */
/* ===================================================================================================== */
/* Note that we will set the timezone to zero in the begining of this code-block as this ensures 'today' */
/* and 'time' are in UTC - as that is also the timezone the info in fcDaemonQueue is stored - and that */
/* at the end of this code-block we will reste the timezone to its original value */
/* ===================================================================================================== */
/* Note that if DaemonKeepProcessedOKItems=false for the daemon then we cannot provide any infornation */
/* about he correctly processed daemon-request */
/* ===================================================================================================== */
/* Note that some some daemons, the fcDaemonQueue records represent in fact more than just a single */
/* daemon-request; in these cases field DaemonQueueRefDescr equals {&DAEMONQUEUEREFERENCE-MULTIPLE} and */
/* Field DaemonQueueRef will then start with {&DAEMONQUEUEREFERENCE-MULTIPLE} and will also contain the */
/* number of requests. We will here only take this into account for the processed ones */
/* ===================================================================================================== */
assign viOriginalTimeZone = session:timezone
session:timezone = 0. /* to ensure time and today are in UTC */
<I-47 {READDIRECTDBACCESS
&READTABLENAMES = "fcDaemonQueue"}>
assign viNumberQueueWaiting = 0
viNumberQueueFailed = 0
viNumberQueueProcessed1Hour = (if {&tlDaemonKeepProcessedOKItems} <> false then 0 else ?)
viNumberQueueProcessed24Hour = (if {&tlDaemonKeepProcessedOKItems} <> false then 0 else ?).
for each fcDaemonQueue fields (DaemonID DaemonQueueStatus DaemonQueueEndDate DaemonQueueEndTime DaemonQueueRef) where
fcDaemonQueue.DaemonID = {&tiDaemonFunctions_ID}
no-lock :
if fcDaemonQueue.DaemonQueueStatus = {&DAEMONQUEUESTATUS-WAITING}
then assign viNumberQueueWaiting = (if viNumberQueueWaiting >= viMaxNumberQueueWaitingToCount
then viMaxNumberQueueWaitingToCount
else viNumberQueueWaiting + 1).
else if fcDaemonQueue.DaemonQueueStatus = {&DAEMONQUEUESTATUS-PROCESSEDERR}
then assign viNumberQueueFailed = viNumberQueueFailed + 1.
else if {&tlDaemonKeepProcessedOKItems} <> false and
fcDaemonQueue.DaemonQueueStatus = {&DAEMONQUEUESTATUS-PROCESSEDOK} and
fcDaemonQueue.DaemonQueueEndDate <> ? and
fcDaemonQueue.DaemonQueueEndTime <> ? and
fcDaemonQueue.DaemonQueueEndTime <> 0
then do :
/* Look for the ones processed in the last hour */
if /* Current time is after 1 AM: we only need to look for today*/
(time > 3600 and
fcDaemonQueue.DaemonQueueEndDate = today and
fcDaemonQueue.DaemonQueueEndTime >= time - 3600) or
/* Current time is before 1 AM: we need to look for today and for the last part yesterday */
(time <= 3600 and
(fcDaemonQueue.DaemonQueueEndDate = today OR
(fcDaemonQueue.DaemonQueueEndDate = today - 1 and
fcDaemonQueue.DaemonQueueEndTime >= 86400 - (3600 - time)) ))
then do :
if fcDaemonQueue.DaemonQueueRef begins {&DAEMONQUEUEREFERENCE-MULTIPLE}
then do:
assign viNumberOfRequests = integer(replace(replace(entry(2,fcDaemonQueue.DaemonQueueRef," ":U),{&DAEMONQUEUEREFERENCE-COUNT},"":U),"=":U,"":U)) no-error.
if error-status:error
then assign viNumberOfRequests = 1.
end. /* if fcDaemonQueue.DaemonQueueRef begins {&DAEMONQUEUEREFERENCE-MULTIPLE} */
else assign viNumberOfRequests = 1.
assign viNumberQueueProcessed1Hour = viNumberQueueProcessed1Hour + viNumberOfRequests.
end. /* if /* Current time is after 1 AM: we only need to look for today*/ */
/* Look for the ones processed in the last 24 hours */
if (fcDaemonQueue.DaemonQueueEndDate = today and
fcDaemonQueue.DaemonQueueEndTime <= time ) Or
(fcDaemonQueue.DaemonQueueEndDate = today - 1 and
fcDaemonQueue.DaemonQueueEndTime >= time )
then do :
if fcDaemonQueue.DaemonQueueRef begins {&DAEMONQUEUEREFERENCE-MULTIPLE}
then do:
assign viNumberOfRequests = integer(replace(replace(entry(2,fcDaemonQueue.DaemonQueueRef," ":U),{&DAEMONQUEUEREFERENCE-COUNT},"":U),"=":U,"":U)) no-error.
if error-status:error
then assign viNumberOfRequests = 1.
end. /* if fcDaemonQueue.DaemonQueueRef begins {&DAEMONQUEUEREFERENCE-MULTIPLE} */
else assign viNumberOfRequests = 1.
assign viNumberQueueProcessed24Hour = viNumberQueueProcessed24Hour + viNumberOfRequests.
end. /* if (fcDaemonQueue.DaemonQueueEndDate >= today - 1 and */
end. /* if fcDaemonQueue.DaemonQueueStatus = {&DAEMONQUEUESTATUS-PROCESSEDOK} and */
end. /* for each fcDaemonQueue where */
assign session:timezone = viOriginalTimeZone. /* Set the time-zone back to its original value */
/* =============================================================================================== */
/* Note that for some daemons, the complete list of waiting ones is not yet in table fcDaemonQueue */
/* but still waiting in another Q-table (whose name starts with Q and is different per daemon) */
/* that is used to create the fcDaemonQueue records upon */
/* For these daemons, the total umber of waiting ones is the number of records in the Q-table */
/* minus the incorrectly processed on that are in fcDaemonQueue */
/* =============================================================================================== */
<I-85 {READDIRECTDBACCESS
&READTABLENAMES = "fcDaemonQueue, QCInvoiceMovement, QDInvoiceMovement, QBudgetLinkActual, QBudgetLinkCommit, QCrossCyPosting, QFRWCube, QPostingLine"}>
if {&tcDaemonName} = {&BALANCEDAEMON}
then do :
assign viNumberQueueWaiting = 0.
for each QCInvoiceMovement fields() no-lock:
assign viNumberQueueWaiting = viNumberQueueWaiting + 1.
if viNumberQueueWaiting > viMaxNumberQueueWaitingToCount + viNumberQueueFailed then leave.
end. /* for each */
for each QDInvoiceMovement fields() no-lock:
assign viNumberQueueWaiting = viNumberQueueWaiting + 1.
if viNumberQueueWaiting > viMaxNumberQueueWaitingToCount + viNumberQueueFailed then leave.
end. /* for each */
assign viNumberQueueWaiting = viNumberQueueWaiting - viNumberQueueFailed.
end. /* if {&tcDaemonName} = */
else if {&tcDaemonName} = {&BUDGETDAEMON}
then do :
assign viNumberQueueWaiting = 0.
for each QBudgetLinkActual fields() no-lock:
assign viNumberQueueWaiting = viNumberQueueWaiting + 1.
if viNumberQueueWaiting > viMaxNumberQueueWaitingToCount + viNumberQueueFailed then leave.
end. /* for each */
for each QBudgetLinkCommit fields() no-lock:
assign viNumberQueueWaiting = viNumberQueueWaiting + 1.
if viNumberQueueWaiting > viMaxNumberQueueWaitingToCount + viNumberQueueFailed then leave.
end. /* for each */
assign viNumberQueueWaiting = viNumberQueueWaiting - viNumberQueueFailed.
end. /* if {&tcDaemonName} = */
else if {&tcDaemonName} = {&CROSSCOMPANYDAEMON}
then do :
assign viNumberQueueWaiting = 0.
for each QCrossCyPosting fields() no-lock:
assign viNumberQueueWaiting = viNumberQueueWaiting + 1.
if viNumberQueueWaiting > viMaxNumberQueueWaitingToCount + viNumberQueueFailed then leave.
end. /* for each */
assign viNumberQueueWaiting = viNumberQueueWaiting - viNumberQueueFailed.
end. /* if {&tcDaemonName} = */
else if {&tcDaemonName} = {&CUBEDAEMON}
then do :
assign viNumberQueueWaiting = 0.
for each QFRWCube fields() no-lock:
assign viNumberQueueWaiting = viNumberQueueWaiting + 1.
if viNumberQueueWaiting > viMaxNumberQueueWaitingToCount + viNumberQueueFailed then leave.
end. /* for each */
assign viNumberQueueWaiting = viNumberQueueWaiting - viNumberQueueFailed.
end. /* if {&tcDaemonName} = */
else if {&tcDaemonName} = {&HISTORYDAEMON}
then do :
assign viNumberQueueWaiting = 0.
for each QPostingLine fields() no-lock:
assign viNumberQueueWaiting = viNumberQueueWaiting + 1.
if viNumberQueueWaiting > viMaxNumberQueueWaitingToCount + viNumberQueueFailed then leave.
end. /* for each */
assign viNumberQueueWaiting = viNumberQueueWaiting - viNumberQueueFailed.
end. /* if {&tcDaemonName} = */
/* ================ */
/* # Failed Queue */
/* ================ */
assign {&tcCalcQueueFailed} = trim(string(viNumberQueueFailed,">>>,>>>,>>9":U)).
/* ====================== */
/* # Processed 1 H Queue */
/* ====================== */
assign {&tcCalcQueueProcessed1Hour} = if viNumberQueueProcessed1Hour = ?
then trim(#T-46'N/A':7(291800793)T-46#)
else trim(string(viNumberQueueProcessed1Hour,">>>,>>>,>>9":U))
no-error.
/* ====================== */
/* # Processed 24 H Queue */
/* ====================== */
assign {&tcCalcQueueProcessed24Hour} = if viNumberQueueProcessed24Hour = ?
then trim(#T-43'N/A':7(291800793)T-43#)
else trim(string(viNumberQueueProcessed24Hour,">>>,>>>,>>9":U))
no-error.
/* ================ */
/* # Waiting Queue */
/* ================ */
assign {&tcCalcQueueWaiting} = if viNumberQueueWaiting >= viMaxNumberQueueWaitingToCount
then "> ":U + trim(string(viMaxNumberQueueWaitingToCount,">>>,>>>,>>9":U))
else trim(string(viNumberQueueWaiting,">>>,>>>,>>9":U))
no-error.
/* ================ */
/* Get next record */
/* ================ */
vhQuery:get-next().
end. /* do while not vhQuery:query-off-end: */
vhQuery:query-close().
delete object vhQuery.