AutoScript
start_agent_info
AgentName = "AU_CEODASHBOARD_EXPERT"
AgentDescr = "AU_CEODASHBOARD_EXPERT"
AgentLanguage = ""
__timer_interval = 0 {....define (in seconds) the timer interval for the wake up}
__delete_on_termination = 0 {....1=the agent frees itself when terminates its task}
end_agent_info
start_action
function = __params[1]
__create_html = 1
__html_type = "DASHBOARD"
{........................................SUMMARY..................................}
if (function = "SUMMARY") then
window_caption = "CPO Dashboard"
id = 0
{--------------------------------- Count of Assosiates Participation ----------------------------}
id = id + 1
lab_caption[id] = "Count of Assosiates Participation"
start_sql "*" "KOSMOS"
SELECT AU_STEP.UNAME
FROM AU_STEP
WHERE (((AU_STEP.FINISHED)=1) AND ((AU_STEP.UNAME) Is Not Null Or (AU_STEP.UNAME)=''))
GROUP BY AU_STEP.UNAME
end_sql
q = QueryByName()
call TExecute(q)
val_caption[id] = TRecordCount(q)
val_unit[id] = "Persons"
expand_collapse[id] = True
expanded[id] = False
expand_height[id] = 0
agent_function[id] = "GET_ASSOSIATES_PARTICIPATION"
expand_to_window[id]= True
call FreeEmbSQL(q)
{--------------------------------- Count of Roles Participation ----------------------------}
id = id + 1
lab_caption[id] = "Count of Roles Participation"
start_sql "*" "KOSMOS"
SELECT AU_STEP.RNAME
FROM AU_STEP
WHERE (((AU_STEP.FINISHED)=1) AND ((AU_STEP.RNAME) Is Not Null Or (AU_STEP.RNAME)=''))
GROUP BY AU_STEP.RNAME
end_sql
q = QueryByName()
call TExecute(q)
val_caption[id] = TRecordCount(q)
val_unit[id] = "Roles"
expand_collapse[id] = True
expanded[id] = False
expand_height[id] = 0
agent_function[id] = "GET_ROLES_PARTICIPATION"
expand_to_window[id]= True
call FreeEmbSQL(q)
{--------------------------------- Assosiates Participation ratio ----------------------------}
id = id + 1
lab_caption[id] = "Assosiates Participation ratio"
start_sql "*" "KOSMOS"
SELECT AU_STEP.RNAME
FROM AU_STEP
WHERE (((AU_STEP.FINISHED)=1) AND ((AU_STEP.RNAME) Is Not Null Or (AU_STEP.RNAME)=''))
GROUP BY AU_STEP.RNAME
end_sql
q = QueryByName()
call TExecute(q)
cnt = TRecordCount(q)
call FreeEmbSQL(q)
start_sql "*" "KOSMOS"
SELECT AU_USER.NUNAME
FROM AU_USER
WHERE (((AU_USER.NUNAME) Is Not Null And (AU_USER.NUNAME)<>''))
GROUP BY AU_USER.NUNAME
end_sql
q = QueryByName()
call TExecute(q)
val_caption[id] = TRecordCount(q)
val_caption[id] = cnt / val_caption[id] * 100
val_caption[id] = format("%8.0f",val_caption[id])
val_unit[id] = "%"
call FreeEmbSQL(q) {...Assosiates Participation ratio}
{--------------------------------- Count of Activities that issued -----------------------}
id = id + 1
lab_caption[id] = "Count of Activities that issued"
start_sql "*" "KOSMOS"
SELECT COUNT(AU_STEP.AU_STEP) AS CNT
FROM AU_STEP
WHERE
(AU_STEP.ISTEP = 0) AND (AU_STEP.AU_PROCESS > 0)
end_sql
q = QueryByName()
call TExecute(q)
val_caption[id] = TGetFld(q,"CNT")
val_unit[id] = "Activities"
call FreeEmbSQL(q)
{--------------------------------- Count of Activities that scheduled -----------------------}
id = id + 1
lab_caption[id] = "Count of Activities that Scheduled"
start_sql "*" "KOSMOS"
SELECT AU_STEP_SCHED.AU_STEP
FROM AU_STEP_SCHED
GROUP BY AU_STEP_SCHED.AU_STEP
end_sql
q = QueryByName()
call TExecute(q)
val_caption[id] = TRecordCount(q)
val_unit[id] = "Activities"
call FreeEmbSQL(q)
{--------------------------------- Count of Closed Activities ----------------------------}
id = id + 1
lab_caption[id] = "Count of Closed Activities"
start_sql "*" "KOSMOS"
SELECT COUNT(AU_STEP.AU_STEP) AS CNT
FROM AU_STEP
WHERE
(AU_STEP.ISTEP = 0) AND (AU_STEP.AU_PROCESS > 0) AND (AU_STEP.FINISHED = 1)
end_sql
q = QueryByName()
call TExecute(q)
val_caption[id] = TGetFld(q,"CNT")
val_unit[id] = "Activities"
call FreeEmbSQL(q)
{--------------------------------- Mean time of Activities Waiting ----------------------------}
id = id + 1
lab_caption[id] = "Mean time of Activities Waiting"
start_sql "*" "KOSMOS"
SELECT Avg(AU_STEP.FTIME) AS AVER
FROM AU_STEP
WHERE (AU_STEP.FINISHED = 1) AND (FTIME > 0)
end_sql
q = QueryByName()
call TExecute(q)
val_caption[id] = TGetFld(q,"AVER")
val_caption[id] = val_caption[id] * 24
val_caption[id] = format("%8.2f",val_caption[id])
val_unit[id] = "Hours"
expand_collapse[id] = True
expanded[id] = False
expand_height[id] = 0
agent_function[id] = "GET_ACTIVITIES_MEANTIME_FOR_EXECUTION"
expand_to_window[id]= True
call FreeEmbSQL(q)
{--------------------------------- Count of Open Activities ----------------------------}
id = id + 1
lab_caption[id] = "Count of Open Activities"
start_sql "*" "KOSMOS"
SELECT COUNT(AU_STEP.AU_STEP) AS CNT
FROM AU_STEP
WHERE
(AU_STEP.ISTEP = 0) AND (AU_STEP.AU_PROCESS > 0) AND (AU_STEP.FINISHED = 0)
end_sql
q = QueryByName()
call TExecute(q)
val_caption[id] = TGetFld(q,"CNT")
val_unit[id] = "Activities"
call FreeEmbSQL(q)
{--------------------------------- Activities not read yet ----------------------------}
id = id + 1
lab_caption[id] = "Activities not read yet"
start_sql "*" "KOSMOS"
SELECT COUNT(AU_STEP.AU_STEP) AS CNT
FROM AU_STEP
WHERE (FIRST_VIEWED_AT_DATE IS NULL)
AND (AU_STEP.ISTEP = 0) AND (AU_STEP.AU_PROCESS > 0) AND (AU_STEP.FINISHED=0)
end_sql
q = QueryByName()
call TExecute(q)
val_caption[id] = TGetFld(q,"CNT")
val_unit[id] = "Activities"
call FreeEmbSQL(q)
{--------------------------------- Infos not read yet ----------------------------}
id = id + 1
lab_caption[id] = "Infos not read yet"
start_sql "*" "KOSMOS"
SELECT COUNT(AU_STEP.AU_STEP) AS CNT
FROM AU_STEP
WHERE (FIRST_VIEWED_AT_DATE IS NULL)
AND (AU_STEP.ISTEP = 1) AND (AU_STEP.AU_PROCESS > 0)
end_sql
q = QueryByName()
call TExecute(q)
val_caption[id] = TGetFld(q,"CNT")
val_unit[id] = "Infos"
call FreeEmbSQL(q)
{--------------------------------- Average Process Age ----------------------------}
id = id + 1
lab_caption[id] = "Average Process Age"
start_sql "*" "KOSMOS"
SELECT AU_PROCESS.FINAL_TIME AS AVG_FINAL_TIME
FROM AU_PROCESS INNER JOIN AU_AGENT ON AU_PROCESS.AGENT_ID = AU_AGENT.AG_ID
WHERE (AU_PROCESS.FINISHED=1)
end_sql
q = QueryByName()
call TExecute(q)
val_caption[id] = TGetFld(q,"AVG_FINAL_TIME")
val_unit[id] = "Days"
call FreeEmbSQL(q)
{--------------------------------- Average Process Overdue Time ----------------------------}
id = id + 1
lab_caption[id] = "Average Process Overdue Time"
start_sql "*" "KOSMOS"
SELECT AU_PROCESS.FINAL_TIME, AU_PROCESS.EST_TIME
FROM AU_PROCESS INNER JOIN AU_AGENT ON AU_PROCESS.AGENT_ID = AU_AGENT.AG_ID
WHERE (AU_PROCESS.FINISHED=1)
end_sql
q = QueryByName()
call TExecute(q)
count = TRecordCount(q)
call TFirst(q)
val1 = 0
val2 = 0
for i = 1 to count
v1 = TGetFld(q,"EST_TIME")
v2 = TGetFld(q,"FINAL_TIME")
val1 = val1 + v1
val2 = val2 + v2
call TNext(q)
next
val_caption[id] = 0+(val2-val1)/count
val_unit[id] = "Days"
call FreeEmbSQL(q)
{--------------------------------- Percentage Of Overdue Processes ----------------------------}
id = id + 1
lab_caption[id] = "Percentage Of Overdue Processes"
start_sql "*" "KOSMOS"
SELECT AU_PROCESS.EST_TIME, AU_PROCESS.FINAL_TIME
FROM AU_PROCESS INNER JOIN AU_AGENT ON AU_PROCESS.AGENT_ID = AU_AGENT.AG_ID
WHERE (AU_PROCESS.FINISHED=1)
end_sql
q = QueryByName()
call TExecute(q)
count = TRecordCount(q)
call TFirst(q)
val1 = 0
val2 = 0
count1 = 0
for i = 1 to count
v1 = TGetFld(q,"FINAL_TIME")
v2 = TGetFld(q,"EST_TIME")
val = v2 - v1
if (val < 0) then
count1 = count1 + 1
endif
call TNext(q)
next
val_caption[id] = count1 / count * 100
val_unit[id] = ""
call FreeEmbSQL(q)
{--------------------------------- Percentage Of Completed Processes ----------------------------}
id = id + 1
lab_caption[id] = "Percentage Of Completed Processes"
start_sql "*" "KOSMOS"
SELECT AU_PROCESS.EST_TIME, AU_PROCESS.FINAL_TIME
FROM AU_PROCESS INNER JOIN AU_AGENT ON AU_PROCESS.AGENT_ID = AU_AGENT.AG_ID
WHERE (AU_PROCESS.FINISHED=1)
end_sql
q = QueryByName()
call TExecute(q)
count = TRecordCount(q)
call TFirst(q)
count1 = 0
for i = 1 to count
v1 = TGetFld(q,"EST_TIME")
v2 = TGetFld(q,"FINAL_TIME")
aval = Abs(val1-val2)
bval = 0.05 * v1
if (aval <= bval) then
count1 = count1 + 1
endif
call TNext(q)
next
val_caption[id] = count1 / count * 100
val_unit[id] = ""
call FreeEmbSQL(q)
{--------------------------------- Average Time To Complete Activity ----------------------------}
id = id + 1
lab_caption[id] = "Average Time To Complete Activity"
start_sql "*" "KOSMOS"
SELECT AU_STEP.FTIME
FROM AU_STEP
WHERE (AU_STEP.FINISHED = 1)
end_sql
q = QueryByName()
call TExecute(q)
count = TRecordCount(q)
call TFirst(q)
val = 0
for i = 1 to count
v1 = TGetFld(q,"FTIME")
val = val + v1
call TNext(q)
next
val_caption[id] = val / count
val_unit[id] = "Days"
call FreeEmbSQL(q)
lab_count = id
return
endif
{.......................................GET_ASSOSIATES_PARTICIPATION..........................}
if (function = "GET_ASSOSIATES_PARTICIPATION") then {keep}
hbar_series = True
title = "Count of Closed Activities per Assosiate"
draw_type = "CHART"
y_count = 1
show_legend = False
color[1] = StringToColor("clGreen")
start_sql "*" "KOSMOS"
SELECT AU_STEP.UNAME AS UN, Count(AU_STEP.UNAME) AS CNT
FROM AU_STEP
WHERE (((AU_STEP.FINISHED)=1) AND ((AU_STEP.UNAME) Is Not Null Or (AU_STEP.UNAME)=''))
GROUP BY AU_STEP.UNAME
ORDER BY Count(AU_STEP.UNAME) DESC
end_sql
q = QueryByName()
call TExecute(q)
msg = error_message(q)
count = TRecordCount(q)
call TFirst(q)
for i = 1 to count
x[i] = TGetFld(q,"UN")
y1[i] = TGetFld(q,"CNT")
call TNext(q)
next
call FreeEmbSQL(q)
return
endif
{.......................................GET_ROLES_PARTICIPATION..........................}
if (function = "GET_ROLES_PARTICIPATION") then {keep}
hbar_series = True
title = "Count of Closed Activities per Role"
draw_type = "CHART"
y_count = 2
show_legend = False
start_sql "*" "KOSMOS"
SELECT AU_STEP.RNAME AS RN, Count(AU_STEP.UNAME) AS CNT
FROM AU_STEP
WHERE (((AU_STEP.FINISHED)=1) AND ((AU_STEP.RNAME) Is Not Null Or (AU_STEP.RNAME)=''))
GROUP BY AU_STEP.RNAME
ORDER BY Count(AU_STEP.UNAME) DESC
end_sql
q = QueryByName()
call TExecute(q)
msg = error_message(q)
count = TRecordCount(q)
call TFirst(q)
color[1] = StringToColor("clRed")
color[2] = StringToColor("clGreen")
for i = 1 to count
x[i] = TGetFld(q,"RN")
y1[i] = TGetFld(q,"CNT")
y2[i] = TGetFld(q,"CNT")
mark1[i] = y1[i]
mark2[i] = y2[i]
call TNext(q)
next
call FreeEmbSQL(q)
return
endif
{.......................................GET_ACTIVITIES_MEANTIME_FOR_EXECUTION..........................}
if (function = "GET_ACTIVITIES_MEANTIME_FOR_EXECUTION") then
line_series = True
title = "Activities Mean Time for Execution"
draw_type = "CHART"
y_count = 1
show_legend = False
left_axis_title = "Days to Finish"
bottom_axis_title = "Days Passed"
start_sql "*" "KOSMOS"
SELECT TOP 30 AU_STEP.FINISHED_AT_DATE AS EDATE,
Day([AU_STEP].[FINISHED_AT_DATE]) AS MDAY, Avg(AU_STEP.FTIME) AS FT
FROM AU_STEP
WHERE (((AU_STEP.AU_PROCESS)>0) AND ((AU_STEP.FINISHED)=1) AND ((AU_STEP.ISTEP)=0))
GROUP BY AU_STEP.FINISHED_AT_DATE
ORDER BY Day([AU_STEP].[FINISHED_AT_DATE]) DESC
end_sql
q = QueryByName()
call TExecute(q)
msg = error_message(q)
count = TRecordCount(q)
color[1] = StringToColor("clRed")
call TLast(q)
for i = 1 to count
x[i] = TGetFld(q,"MDAY")
y1[i] = TGetFld(q,"FT")
call TPrior(q)
next
call FreeEmbSQL(q)
return
endif
end_action
