AutoScript
start_agent_info
AgentName = "AU_ORGAN_DASH_EXPERT"
AgentDescr = "Organogramme Dashboard 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]
scode = __params[2]
scode = strcat(scode,"%")
__create_html = 1
__html_type = "DASHBOARD"
{........................................SUMMARY..................................}
if (function = "SUMMARY") then
id = 0
{--------------------------------- Activity ----------------------------}
id = id + 1
lab_caption[id] = "Department"
start_sql "*" "KOSMOS"
SELECT AU_POS.POS_D
FROM AU_POS
WHERE (AU_POS.DCODE = :C)
end_sql
q = QueryByName()
call TSetParam(q,"C",__params[2])
call TExecute(q)
val_caption[id] = TGetFld(q,"POS_D")
val_unit[id] = ""
call FreeEmbSQL(q)
{--------------------------------- Count of Associates Participation ----------------------------}
id = id + 1
lab_caption[id] = "Count of Associates Participation"
start_sql "*" "KOSMOS"
SELECT AU_STEP.UNAME
FROM AU_STEP INNER JOIN AU_POS ON AU_STEP.RNAME = AU_POS.POS_D
WHERE (((AU_STEP.FINISHED)=1) AND ((AU_STEP.UNAME) Is Not Null Or (AU_STEP.UNAME)=''''))
AND ((AU_POS.DCODE) LIKE :C)
GROUP BY AU_STEP.UNAME
end_sql
q = QueryByName()
call TSetParam(q,"C",scode)
call TExecute(q)
val_caption[id] = TRecordCount(q)
val_unit[id] = "Persons"
call FreeEmbSQL(q)
expand_collapse[id] = True
expanded[id] = False
expand_height[id] = 0
agent_function[id] = "GET_ASSOSIATES_PARTICIPATION"
agent_function_param1[id] = scode
expand_to_window[id]= True
{--------------------------------- Count of Roles Participation ----------------------------}
id = id + 1
lab_caption[id] = "Count of Roles Participation"
start_sql "*" "KOSMOS"
SELECT AU_STEP.RNAME
FROM AU_STEP INNER JOIN AU_POS ON AU_STEP.RNAME = AU_POS.POS_D
WHERE (((AU_STEP.FINISHED)=1) AND ((AU_STEP.RNAME) Is Not Null Or (AU_STEP.RNAME)=''''))
AND ((AU_POS.DCODE) LIKE :C)
GROUP BY AU_STEP.RNAME
end_sql
q = QueryByName()
call TSetParam(q,"C",scode)
call TExecute(q)
val_caption[id] = TRecordCount(q)
val_unit[id] = "Roles"
call FreeEmbSQL(q)
expand_collapse[id] = True
expanded[id] = False
expand_height[id] = 0
agent_function[id] = "GET_ROLES_PARTICIPATION"
agent_function_param1[id] = scode
expand_to_window[id]= True
{--------------------------------- Assosiates Participation ratio ----------------------------}
id = id + 1
lab_caption[id] = "Associates Participation ratio"
start_sql "*" "KOSMOS"
SELECT AU_STEP.RNAME
FROM AU_STEP INNER JOIN AU_POS ON AU_STEP.RNAME = AU_POS.POS_D
WHERE (((AU_STEP.FINISHED)=1) AND ((AU_STEP.RNAME) Is Not Null Or (AU_STEP.RNAME)=''''))
AND ((AU_POS.DCODE) LIKE :C)
GROUP BY AU_STEP.RNAME
end_sql
q = QueryByName()
call TSetParam(q,"C",scode)
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)
{--------------------------------- 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 INNER JOIN AU_POS ON AU_STEP.RNAME = AU_POS.POS_D
WHERE
(AU_STEP.ISTEP = 0) AND (AU_STEP.AU_PROCESS > 0)
AND ((AU_POS.DCODE) LIKE :C)
end_sql
q = QueryByName()
call TSetParam(q,"C",scode)
call TExecute(q)
val_caption[id] = TGetFld(q,"CNT")
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 INNER JOIN AU_POS ON AU_STEP.RNAME = AU_POS.POS_D
WHERE
(AU_STEP.ISTEP = 0) AND (AU_STEP.AU_PROCESS > 0) AND (AU_STEP.FINISHED = 1)
AND ((AU_POS.DCODE) LIKE :C)
end_sql
q = QueryByName()
call TSetParam(q,"C",scode)
call TExecute(q)
val_caption[id] = TGetFld(q,"CNT")
val_unit[id] = "Activities"
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 INNER JOIN AU_POS ON AU_STEP.RNAME = AU_POS.POS_D
WHERE
(AU_STEP.ISTEP = 0) AND (AU_STEP.AU_PROCESS > 0) AND (AU_STEP.FINISHED = 0)
AND ((AU_POS.DCODE) LIKE :C)
end_sql
q = QueryByName()
call TSetParam(q,"C",scode)
call TExecute(q)
val_caption[id] = TGetFld(q,"CNT")
val_unit[id] = "Activities"
call FreeEmbSQL(q)
{-------------------------- Mean time of Closed Activities Waiting -------------------------}
id = id + 1
lab_caption[id] = "Mean time of Closed Activities Waiting"
start_sql "*" "KOSMOS"
SELECT Avg(AU_STEP.FTIME) AS AVER
FROM AU_STEP INNER JOIN AU_POS ON AU_STEP.RNAME = AU_POS.POS_D
WHERE (AU_STEP.FINISHED = 1) AND (FTIME > 0)
AND ((AU_POS.DCODE) LIKE :C)
end_sql
q = QueryByName()
call TSetParam(q,"C",scode)
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"
call FreeEmbSQL(q)
expand_collapse[id] = True
expanded[id] = False
expand_height[id] = 0
agent_function[id] = "GET_ASSIGNED_ACTIVITIES"
agent_function_param1[id] = scode
expand_to_window[id]= True
lab_count = id
return
endif
{.......................................GET_ASSOSIATES_PARTICIPATION..........................}
if (function = "GET_ASSOSIATES_PARTICIPATION") then {keep}
hbar_series = True
title = "Count of Closed Activities per Associate"
draw_type = "CHART"
y_count = 1
show_legend = False
start_sql "*" "KOSMOS"
SELECT AU_STEP.UNAME AS UN, Count(AU_STEP.UNAME) AS CNT
FROM AU_STEP INNER JOIN AU_POS ON AU_STEP.RNAME = AU_POS.POS_D
WHERE (((AU_STEP.FINISHED)=1) AND ((AU_STEP.UNAME) Is Not Null Or (AU_STEP.UNAME)=''''))
AND ((AU_POS.DCODE) LIKE :C)
GROUP BY AU_STEP.UNAME
ORDER BY AU_STEP.UNAME
end_sql
q = QueryByName()
call TSetParam(q,"C",scode)
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 = 1
show_legend = False
start_sql "*" "KOSMOS"
SELECT AU_STEP.RNAME AS RN, Count(AU_STEP.UNAME) AS CNT
FROM AU_STEP INNER JOIN AU_POS ON AU_STEP.RNAME = AU_POS.POS_D
WHERE (((AU_STEP.FINISHED)=1) AND ((AU_STEP.RNAME) Is Not Null Or (AU_STEP.RNAME)=''''))
AND ((AU_POS.DCODE) LIKE :C)
GROUP BY AU_STEP.RNAME
ORDER BY AU_STEP.RNAME
end_sql
q = QueryByName()
call TSetParam(q,"C",scode)
call TExecute(q)
msg = error_message(q)
count = TRecordCount(q)
if (count = 0) then
call FreeEmbSQL(q)
return
endif
call TFirst(q)
for i = 1 to count
x[i] = TGetFld(q,"RN")
y1[i] = TGetFld(q,"CNT")
call TNext(q)
next
call FreeEmbSQL(q)
return
endif
{.......................................GET_ASSIGNED_ACTIVITIES..........................}
if (function = "GET_ASSIGNED_ACTIVITIES") then
line_series = True
title = "Assigned Activities / Closed Activities and Activities Acumulated"
draw_type = "CHART"
y_count = 3
line1 = "Assinged"
line2 = "Closed"
line3 = "Acumulated"
show_legend = True
left_axis_title = "Count of Activities"
bottom_axis_title = "Days since the begining"
start_sql "*" "KOSMOS"
SELECT AU_STEP.ASSIGNED_AT_DATE, Count(AU_STEP.AU_STEP) AS CNT
FROM AU_STEP INNER JOIN AU_POS ON AU_STEP.RNAME = AU_POS.POS_D
WHERE ((AU_POS.DCODE Like :C) AND (AU_STEP.AU_PROCESS > 0))
GROUP BY AU_STEP.ASSIGNED_AT_DATE
ORDER BY AU_STEP.ASSIGNED_AT_DATE
end_sql
q = QueryByName()
call TSetParam(q,"C",scode)
call TExecute(q)
count = TRecordCount(q)
if (count = 0) then
call FreeEmbSQL(q)
return
endif
call TFirst(q)
date1 = TGetFld(q,"ASSIGNED_AT_DATE")
date2 = crDate
date1_num = DateToNum(date1)
date2_num = DateToNum(date2)
array_dim = date2_num - date1_num
if (array_dim > 0) then
for i = 1 to array_dim+1
x[i] = i
y1[i] = 0
y2[i] = 0
y3[i] = 0
next
for i = 1 to count
d1 = TGetFld(q,"ASSIGNED_AT_DATE")
d = DateToNum(d1)
d = d - date1_num
y1[d+1] = TGetFld(q,"CNT")
call trace("ass d1=",d1,", cnt=",y1[d+1])
call TNext(q)
next
call FreeEmbSQL(q)
start_sql "*" "KOSMOS"
SELECT AU_STEP.FINISHED_AT_DATE, Count(AU_STEP.AU_STEP) AS CNT
FROM AU_STEP INNER JOIN AU_POS ON AU_STEP.RNAME = AU_POS.POS_D
WHERE ((AU_POS.DCODE Like :C) AND (AU_STEP.AU_PROCESS > 0) AND (AU_STEP.FINISHED=1))
GROUP BY AU_STEP.FINISHED_AT_DATE
ORDER BY AU_STEP.FINISHED_AT_DATE
end_sql
q1 = QueryByName()
call TSetParam(q1,"C",scode)
call TExecute(q1)
count = TRecordCount(q1)
call TFirst(q1)
for i = 1 to count
d1 = TGetFld(q1,"FINISHED_AT_DATE")
d = DateToNum(d1)
d = d - date1_num
y2[d+1] = TGetFld(q1,"CNT")
call trace("fin d1=",d1,", cnt=",y1[d+1])
call TNext(q1)
next
call FreeEmbSQL(q1)
for i = 1 to array_dim+1
if (i = 1) then
y3[i] = y1[i] - y2[i]
elseif (i > 1) then
y3[i] = y3[i-1] + y1[i] - y2[i]
endif
call trace("d=",i,", y1=",y1[i],", y2=",y2[i],", y3=",y3[i])
next
count = array_dim+1
endif
return
endif
end_action
