AutoScript
start_agent_info
AgentName = "AU_ATAGLANCE_EXPERT"
AgentDescr = "AU_ATAGLANCE_EXPERT"
AgentLanguage = ""
__create_process = FALSE
__delete_on_termination = 0 {....1=the agent frees itself when terminates its task}
end_agent_info
start_action
function = __params[1]
uname = __params[2]
__create_html = 1
__html_type = "BOXGRID"
{........................................SUMMARY..................................}
if (function = "SUMMARY") then
window_caption = "Activities Executor Dashboard"
id = 0
{--------------------------------- New Activities that does not openend yet ----------------------------}
id = id + 1
lab_caption[id] = "New Activities that does not openend yet"
start_sql "*" "KOSMOS"
SELECT COUNT(AU_STEP.AU_STEP) AS CNT
FROM
((AU_STEP LEFT JOIN AU_PROCESS ON AU_STEP.AU_PROCESS = AU_PROCESS.AU_PROCESS)
LEFT JOIN AU_POS ON AU_STEP.RNAME = AU_POS.POS_D)
LEFT JOIN (AU_ORGAN LEFT JOIN AU_USER ON AU_ORGAN.KUSER = AU_USER.AU_USER)
ON AU_POS.AU_POS = AU_ORGAN.AU_POS
WHERE
((((AU_STEP.UNAME=:U) AND (AU_USER.UNAME IS NULL) AND (AU_STEP.FINISHED=0))
OR ((AU_USER.UNAME=:U) AND (AU_STEP.UNAME=AU_USER.UNAME) AND (AU_STEP.FINISHED=0))
OR ((AU_USER.UNAME=:U) AND ((AU_STEP.UNAME IS NULL) or (AU_STEP.UNAME='''')) AND (AU_STEP.FINISHED=0)))
AND (AU_STEP.ISTEP = 0)) AND (FIRST_VIEWED_AT_DATE IS NULL)
end_sql
q = QueryByName()
call TSetParam(q,"U",uname)
call TExecute(q)
val_caption[id] = TGetFld(q,"CNT")
call FreeEmbSQL(q)
{------------------------------------ Total Actvities ---------------------------}
id = id + 1
lab_caption[id] = "Total Actvities"
start_sql "*" "KOSMOS"
SELECT COUNT(AU_STEP.AU_STEP) AS CNT
FROM (((AU_STEP LEFT JOIN AU_PROCESS ON AU_STEP.AU_PROCESS = AU_PROCESS.AU_PROCESS)
LEFT JOIN AU_POS ON AU_STEP.RNAME = AU_POS.POS_D) LEFT JOIN (AU_ORGAN LEFT JOIN AU_USER
ON AU_ORGAN.KUSER = AU_USER.AU_USER) ON AU_POS.AU_POS = AU_ORGAN.AU_POS) LEFT JOIN AU_BUSINESS
ON AU_STEP.AU_BUSINESS = AU_BUSINESS.AU_BUSINESS
WHERE (((AU_USER.UNAME) Is Null) AND ((AU_STEP.UNAME)=:U) AND ((AU_STEP.FINISHED)=0)
AND ((AU_STEP.ISTEP)=0)) OR (((AU_USER.UNAME)=:U) AND ((AU_STEP.UNAME)=AU_USER.UNAME)
AND ((AU_STEP.FINISHED)=0) AND ((AU_STEP.ISTEP)=0)) OR (((AU_USER.UNAME)=:U)
AND ((AU_STEP.UNAME) Is Null Or (AU_STEP.UNAME)='') AND ((AU_STEP.FINISHED)=0) AND ((AU_STEP.ISTEP)=0))
end_sql
q = QueryByName()
call TSetParam(q,"U",uname)
call TExecute(q)
val_caption[id] = TGetFld(q,"CNT")
call FreeEmbSQL(q)
{--------------------------------- Information that does not opened yet ----------------------------}
id = id + 1
lab_caption[id] = "Information that does not opened yet"
start_sql "*" "KOSMOS"
SELECT COUNT(AU_STEP.AU_STEP) AS CNT
FROM
((AU_STEP LEFT JOIN AU_PROCESS ON AU_STEP.AU_PROCESS = AU_PROCESS.AU_PROCESS)
LEFT JOIN AU_POS ON AU_STEP.RNAME = AU_POS.POS_D)
LEFT JOIN (AU_ORGAN LEFT JOIN AU_USER ON AU_ORGAN.KUSER = AU_USER.AU_USER)
ON AU_POS.AU_POS = AU_ORGAN.AU_POS
WHERE
((((AU_STEP.UNAME=:U) AND (AU_USER.UNAME IS NULL) AND (AU_STEP.FINISHED=0))
OR ((AU_USER.UNAME=:U) AND (AU_STEP.UNAME=AU_USER.UNAME) AND (AU_STEP.FINISHED=0))
OR ((AU_USER.UNAME=:U) AND ((AU_STEP.UNAME IS NULL) or (AU_STEP.UNAME='''')) AND (AU_STEP.FINISHED=0)))
AND (AU_STEP.ISTEP = 1)) AND (FIRST_VIEWED_AT_DATE IS NULL)
end_sql
q = QueryByName()
call TSetParam(q,"U",uname)
call TExecute(q)
val_caption[id] = TGetFld(q,"CNT")
call FreeEmbSQL(q)
{--------------------------------- Total Information ----------------------------}
id = id + 1
lab_caption[id] = "Total Information"
start_sql "*" "KOSMOS"
SELECT COUNT(AU_STEP.AU_STEP) AS CNT
FROM AU_BUSINESS RIGHT JOIN (((AU_STEP LEFT JOIN AU_PROCESS ON AU_STEP.AU_PROCESS = AU_PROCESS.AU_PROCESS)
LEFT JOIN AU_POS ON AU_STEP.RNAME = AU_POS.POS_D) LEFT JOIN (AU_ORGAN
LEFT JOIN AU_USER ON AU_ORGAN.KUSER = AU_USER.AU_USER) ON AU_POS.AU_POS = AU_ORGAN.AU_POS)
ON AU_BUSINESS.AU_BUSINESS = AU_STEP.AU_BUSINESS
WHERE (((AU_USER.UNAME) Is Null) AND ((AU_STEP.UNAME)=:U) AND ((AU_STEP.ISTEP)=1)
AND ((AU_STEP.DELETED_FLAG) Is Null)) OR (((AU_USER.UNAME)=:U) AND ((AU_STEP.UNAME)=[AU_USER].[UNAME])
AND ((AU_STEP.ISTEP)=1) AND ((AU_STEP.DELETED_FLAG) Is Null)) OR (((AU_USER.UNAME)=:U)
AND ((AU_STEP.UNAME) Is Null Or (AU_STEP.UNAME)='')
AND ((AU_STEP.ISTEP)=1) AND ((AU_STEP.DELETED_FLAG) Is Null))
AND (AU_PROCESS.FINISHED = 0)
end_sql
q = QueryByName()
call TSetParam(q,"U",uname)
call TExecute(q)
val_caption[id] = TGetFld(q,"CNT")
call FreeEmbSQL(q)
{--------------------------------- Open Activities as Process Owner ----------------------------}
id = id + 1
lab_caption[id] = "Open Activities as Process Owner"
start_sql "*" "KOSMOS"
SELECT COUNT(AU_STEP.AU_STEP) AS CNT
FROM AU_STEP INNER JOIN AU_PROCESS ON AU_STEP.AU_PROCESS = AU_PROCESS.AU_PROCESS
WHERE (((AU_STEP.FINISHED)=0) AND ((AU_PROCESS.USER_OWNER)=:U) AND ((AU_STEP.ISTEP)=0))
AND (AU_PROCESS.EXPRET_ACTV_FLAG <> '1')
end_sql
q = QueryByName()
call TSetParam(q,"U",uname)
call TExecute(q)
val_caption[id] = TGetFld(q,"CNT")
call FreeEmbSQL(q)
{--------------------------------- Oldest Open Activity ----------------------------}
id = id + 1
lab_caption[id] = "Oldest Open Activity"
start_sql "*" "KOSMOS"
SELECT Min(AU_STEP.ASSIGNED_AT_DATE) AS DAT
FROM (((AU_STEP LEFT JOIN AU_PROCESS ON AU_STEP.AU_PROCESS = AU_PROCESS.AU_PROCESS)
LEFT JOIN AU_POS ON AU_STEP.RNAME = AU_POS.POS_D) LEFT JOIN (AU_ORGAN LEFT JOIN AU_USER
ON AU_ORGAN.KUSER = AU_USER.AU_USER) ON AU_POS.AU_POS = AU_ORGAN.AU_POS) LEFT JOIN AU_BUSINESS
ON AU_STEP.AU_BUSINESS = AU_BUSINESS.AU_BUSINESS
WHERE (((AU_USER.UNAME) Is Null) AND ((AU_STEP.UNAME)=:U) AND ((AU_STEP.FINISHED)=0)
AND ((AU_STEP.ISTEP)=0)) OR (((AU_USER.UNAME)=:U) AND ((AU_STEP.UNAME)=AU_USER.UNAME)
AND ((AU_STEP.FINISHED)=0) AND ((AU_STEP.ISTEP)=0)) OR (((AU_USER.UNAME)=:U)
AND ((AU_STEP.UNAME) Is Null Or (AU_STEP.UNAME)='') AND ((AU_STEP.FINISHED)=0) AND ((AU_STEP.ISTEP)=0))
end_sql
q = QueryByName()
call TSetParam(q,"U",uname)
call TExecute(q)
val_caption[id] = TGetFld(q,"DAT")
call FreeEmbSQL(q)
{--------------------------------- Newest Open Activity ----------------------------}
id = id + 1
lab_caption[id] = "Newest Open Activity"
start_sql "*" "KOSMOS"
SELECT Max(AU_STEP.ASSIGNED_AT_DATE) AS DAT
FROM (((AU_STEP LEFT JOIN AU_PROCESS ON AU_STEP.AU_PROCESS = AU_PROCESS.AU_PROCESS)
LEFT JOIN AU_POS ON AU_STEP.RNAME = AU_POS.POS_D) LEFT JOIN (AU_ORGAN LEFT JOIN AU_USER
ON AU_ORGAN.KUSER = AU_USER.AU_USER) ON AU_POS.AU_POS = AU_ORGAN.AU_POS) LEFT JOIN AU_BUSINESS
ON AU_STEP.AU_BUSINESS = AU_BUSINESS.AU_BUSINESS
WHERE (((AU_USER.UNAME) Is Null) AND ((AU_STEP.UNAME)=:U) AND ((AU_STEP.FINISHED)=0)
AND ((AU_STEP.ISTEP)=0)) OR (((AU_USER.UNAME)=:U) AND ((AU_STEP.UNAME)=AU_USER.UNAME)
AND ((AU_STEP.FINISHED)=0) AND ((AU_STEP.ISTEP)=0)) OR (((AU_USER.UNAME)=:U)
AND ((AU_STEP.UNAME) Is Null Or (AU_STEP.UNAME)='') AND ((AU_STEP.FINISHED)=0) AND ((AU_STEP.ISTEP)=0))
end_sql
q = QueryByName()
call TSetParam(q,"U",uname)
call TExecute(q)
val_caption[id] = TGetFld(q,"DAT")
call FreeEmbSQL(q)
{--------------------------------- Mean Time to Close Activities ----------------------------}
id = id + 1
lab_caption[id] = "Mean Time to Close Activities"
start_sql "*" "KOSMOS"
SELECT SUM(AU_STEP.FTIME) AS SM, COUNT(AU_STEP.FTIME) AS CNT
FROM
((AU_STEP LEFT JOIN AU_PROCESS ON AU_STEP.AU_PROCESS = AU_PROCESS.AU_PROCESS)
LEFT JOIN AU_POS ON AU_STEP.RNAME = AU_POS.POS_D)
LEFT JOIN (AU_ORGAN LEFT JOIN AU_USER ON AU_ORGAN.KUSER = AU_USER.AU_USER)
ON AU_POS.AU_POS = AU_ORGAN.AU_POS
WHERE
(((AU_STEP.UNAME=:U) AND (AU_USER.UNAME IS NULL) AND (AU_STEP.FINISHED=1))
OR ((AU_USER.UNAME=:U) AND (AU_STEP.UNAME=AU_USER.UNAME) AND (AU_STEP.FINISHED=1))
OR ((AU_USER.UNAME=:U) AND ((AU_STEP.UNAME IS NULL) or (AU_STEP.UNAME="")) AND (AU_STEP.FINISHED=1)))
AND (AU_STEP.ISTEP = 0) AND (AU_STEP.AU_PROCESS > 0)
AND (AU_STEP.FTIME > 0)
end_sql
q = QueryByName()
call TSetParam(q,"U",uname)
call TExecute(q)
sum = TGetFld(q,"SM")
cnt = TGetFld(q,"CNT")
call trace("sm=",sum," cnt=",cnt)
val_caption[id] = sum / cnt
val_caption[id] = val_caption[id] * 24
val_caption[id] = format("%8.1f",val_caption[id])
call FreeEmbSQL(q)
val_unit[id] = "Hours"
lab_count = id
return
endif
end_action
