AutoScript
start_agent_info
AgentName = "AU_PPC_EXPERT"
AgentDescr = "Production Planning and Control 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"
window_caption = "Production Planning"
{........................................SUMMARY..................................}
if (function = "SUMMARY") then
id = 0
{--------------------------------- Production Start Date ----------------------------}
id = id + 1
lab_caption[id] = "Production Start Date"
start_sql "*" "KOSMOS"
SELECT SDATE1, SDATE2, STIME1, STIME2
FROM AU_STEP_SCHED
WHERE (AU_STEP_SCHED.AU_PROCESS = :P) AND (OPTIMUM_FLAG = '0') AND (STIME > 0)
ORDER BY AU_STEP_SCHED
end_sql
q = QueryByName()
call TSetParam(q,"P",__params[2])
call TExecute(q)
call TFirst(q)
dd1 = TGetFld(q,"SDATE1")
tt1 = TGetFld(q,"STIME1")
call TLast(q)
dd2 = TGetFld(q,"SDATE2")
tt2 = TGetFld(q,"STIME2")
val_caption[id] = dd1
expand_collapse[id] = False
expanded[id] = False
expand_height[id] = 0
call FreeEmbSQL(q)
{--------------------------------- Production Start Time----------------------------}
id = id + 1
lab_caption[id] = "Production Start Time"
val_caption[id] = tt1
expand_collapse[id] = False
expanded[id] = False
expand_height[id] = 0
{--------------------------------- Production Finish Date ----------------------------}
id = id + 1
lab_caption[id] = "Production Finish Date"
val_caption[id] = dd2
expand_collapse[id] = False
expanded[id] = False
expand_height[id] = 0
{--------------------------------- Production Finish Time ----------------------------}
id = id + 1
lab_caption[id] = "Production Finish Time"
val_caption[id] = tt2
expand_collapse[id] = False
expanded[id] = False
expand_height[id] = 0
{--------------------------------- Optimum Production Makespan in Days----------------------------}
id = id + 1
lab_caption[id] = "Optimum Production Makespan in Days"
start_sql "*" "KOSMOS"
SELECT SDATE1, SDATE2, STIME1, STIME2
FROM AU_STEP_SCHED
WHERE (AU_STEP_SCHED.AU_PROCESS = :P) AND (OPTIMUM_FLAG = "1") AND (STIME > 0)
ORDER BY AU_STEP_SCHED
end_sql
q = QueryByName()
call TSetParam(q,"P",__params[2])
call TExecute(q)
call TFirst(q)
dd1 = TGetFld(q,"SDATE1")
tt1 = TGetFld(q,"STIME1")
dd1 = DateToNum(dd1)
tt1 = TimeToNum(tt1)
call TLast(q)
dd2 = TGetFld(q,"SDATE2")
tt2 = TGetFld(q,"STIME2")
dd2 = DateToNum(dd2)
tt2 = TimeToNum(tt2)
days0 = dd2 + tt2 - dd1 - tt1
days0 = format("%.2f",days0)
val_caption[id] = days0
val_unit[id] = "Days"
row_color[id] = RGBToColor(193,255,132)
expand_collapse[id] = True
expanded[id] = False
expand_height[id] = 0
expand_to_window[id]= True
agent_function[id] = "GANTT_SCHED_OPT"
agent_function_param1[id] = __params[2]
call FreeEmbSQL(q)
{--------------------------------- Production Makespan in Days----------------------------}
id = id + 1
lab_caption[id] = "Production Makespan in Days"
start_sql "*" "KOSMOS"
SELECT SDATE1, SDATE2, STIME1, STIME2
FROM AU_STEP_SCHED
WHERE (AU_STEP_SCHED.AU_PROCESS = :P) AND (OPTIMUM_FLAG = "0") AND (STIME > 0)
ORDER BY AU_STEP_SCHED
end_sql
q = QueryByName()
call TSetParam(q,"P",__params[2])
call TExecute(q)
call TFirst(q)
d1 = TGetFld(q,"SDATE1")
t1 = TGetFld(q,"STIME1")
d1 = DateToNum(d1)
t1 = TimeToNum(t1)
call TLast(q)
d2 = TGetFld(q,"SDATE2")
t2 = TGetFld(q,"STIME2")
d2 = DateToNum(d2)
t2 = TimeToNum(t2)
days = d2 + t2 - d1 - t1
days = format("%.2f",days)
val_caption[id] = days
val_unit[id] = "Days"
row_color[id] = RGBToColor(252,153,135)
expand_collapse[id] = True
expanded[id] = False
expand_height[id] = 0
expand_to_window[id]= True
agent_function[id] = "GANTT_SCHED"
agent_function_param1[id] = __params[2]
call FreeEmbSQL(q)
{--------------------------------- Production Makespan in Hours----------------------------}
id = id + 1
lab_caption[id] = "Production Makespan in Hours"
hours = days*24
hours = format("%.2f",hours)
val_caption[id] = hours
val_unit[id] = "Hours"
expand_collapse[id] = True
expanded[id] = True
expand_height[id] = 0
expand_to_window[id]= True
agent_function[id] = "GANTT_SCHED"
agent_function_param1[id] = __params[2]
{--------------------------------- Labor Hours Requirement ----------------------------}
id = id + 1
lab_caption[id] = "Labor Hours Requirement"
start_sql "*" "KOSMOS"
SELECT SUM(SHHOURS) AS DAYS
FROM AU_STEP_SCHED
WHERE (AU_STEP_SCHED.AU_PROCESS = :P) AND (OPTIMUM_FLAG = "0")
end_sql
q = QueryByName()
call TSetParam(q,"P",__params[2])
call TExecute(q)
days = TGetFld(q,"DAYS")
hours = days * 24
hours = format("%.1f",hours)
val_caption[id] = hours
val_unit[id] = "Hours"
call FreeEmbSQL(q)
{--------------------------------- Labor Setup Hours Requirement ----------------------------}
id = id + 1
lab_caption[id] = " Setup Hours Requirement"
start_sql "*" "KOSMOS"
SELECT SUM(SHHOURS) AS DAYS
FROM AU_STEP_SCHED
WHERE (AU_STEP_SCHED.AU_PROCESS = :P) AND (AU_STEP_SCHED.ACTIVITY LIKE "setup%") AND (OPTIMUM_FLAG = "0")
end_sql
q = QueryByName()
call TSetParam(q,"P",__params[2])
call TExecute(q)
days = TGetFld(q,"DAYS")
hours1 = days * 24
hours1 = format("%.1f",hours1)
val_caption[id] = hours1
val_unit[id] = "Hours"
call FreeEmbSQL(q)
{--------------------------------- Labor Production Hours Requirement ----------------------------}
id = id + 1
lab_caption[id] = " Production Hours Requirement"
start_sql "*" "KOSMOS"
SELECT SUM(SHHOURS) AS DAYS
FROM AU_STEP_SCHED
WHERE (AU_STEP_SCHED.AU_PROCESS = :P) AND (AU_STEP_SCHED.ACTIVITY LIKE "prod%") AND (OPTIMUM_FLAG = "0")
end_sql
q = QueryByName()
call TSetParam(q,"P",__params[2])
call TExecute(q)
days = TGetFld(q,"DAYS")
hours2 = days * 24
hours2 = format("%.1f",hours2)
val_caption[id] = hours2
val_unit[id] = "Hours"
call FreeEmbSQL(q)
{--------------------------------- Labor Setup Cost ----------------------------}
id = id + 1
lab_caption[id] = " Setup Labor Cost"
cost1 = hours1 * 10
cost1 = format("%.0f",cost1)
val_caption[id] = cost1
val_unit[id] = "Euros"
{--------------------------------- Labor Production Cost ----------------------------}
id = id + 1
lab_caption[id] = " Production Labor Cost"
cost2 = hours2 * 8
cost2 = format("%.0f",cost2)
val_caption[id] = cost2
val_unit[id] = "Euros"
{--------------------------------- Machine Hours Requirement ----------------------------}
id = id + 1
lab_caption[id] = "Machine Hours Requirement"
start_sql "*" "KOSMOS"
SELECT SUM(SMHOURS) AS DAYS
FROM AU_STEP_SCHED
WHERE (AU_STEP_SCHED.AU_PROCESS = :P) AND (OPTIMUM_FLAG = "0")
end_sql
q = QueryByName()
call TSetParam(q,"P",__params[2])
call TExecute(q)
days = TGetFld(q,"DAYS")
hours = days * 24
hours = format("%.1f",hours)
val_caption[id] = hours
val_unit[id] = "Hours"
call FreeEmbSQL(q)
{--------------------------------- Machine Setup Hours Requirement ----------------------------}
id = id + 1
lab_caption[id] = " Setup Hours Requirement"
start_sql "*" "KOSMOS"
SELECT SUM(SMHOURS) AS DAYS
FROM AU_STEP_SCHED
WHERE (AU_STEP_SCHED.AU_PROCESS = :P) AND (AU_STEP_SCHED.ACTIVITY LIKE "setup%") AND (OPTIMUM_FLAG = "0")
end_sql
q = QueryByName()
call TSetParam(q,"P",__params[2])
call TExecute(q)
days = TGetFld(q,"DAYS")
hours1 = days * 24
hours1 = format("%.1f",hours1)
val_caption[id] = hours1
val_unit[id] = "Hours"
call FreeEmbSQL(q)
expand_collapse[id] = True
expand_to_window[id]= True
expanded[id] = True
expand_height[id] = 0
agent_function[id] = "GANTT_SCHED_SETUP"
agent_function_param1[id] = __params[2]
{--------------------------------- Machine Production Hours Requirement ----------------------------}
id = id + 1
lab_caption[id] = " Production Hours Requirement"
start_sql "*" "KOSMOS"
SELECT SUM(SMHOURS) AS DAYS
FROM AU_STEP_SCHED
WHERE (AU_STEP_SCHED.AU_PROCESS = :P) AND (AU_STEP_SCHED.ACTIVITY LIKE "prod%") AND (OPTIMUM_FLAG = "0")
end_sql
q = QueryByName()
call TSetParam(q,"P",__params[2])
call TExecute(q)
days = TGetFld(q,"DAYS")
hours2 = days * 24
hours2 = format("%.1f",hours2)
val_caption[id] = hours2
val_unit[id] = "Hours"
call FreeEmbSQL(q)
expand_collapse[id] = True
expand_to_window[id]= True
expanded[id] = True
expand_height[id] = 0
agent_function[id] = "GANTT_SCHED_PROD"
agent_function_param1[id] = __params[2]
{--------------------------------- Machine Setup Cost ----------------------------}
id = id + 1
lab_caption[id] = " Setup Machine Cost"
cost1 = hours1 * 10
cost1 = format("%.0f",cost1)
val_caption[id] = cost1
val_unit[id] = "Euros"
{--------------------------------- Machine Production Cost ----------------------------}
id = id + 1
lab_caption[id] = " Production Machine Cost"
cost2 = hours2 * 8
cost2 = format("%.0f",cost2)
val_caption[id] = cost2
val_unit[id] = "Euros"
expand_collapse[id] = True
expand_to_window[id]= True
expanded[id] = True
expand_height[id] = 0
agent_function[id] = "INCREMENTAL_WORKFORCE"
agent_function_param1[id] = __params[2]
{--------------------------------- Optimized Unused Machine Time in Hours----------------------------}
id = id + 1
lab_caption[id] = "Optimized Unused Machine Time in Hours"
start_sql "*" "KOSMOS"
SELECT AU_RESOURCE_GROUP
FROM AU_STEP_SCHED
WHERE (AU_STEP_SCHED.AU_PROCESS = :P) AND (OPTIMUM_FLAG = "1")
GROUP BY AU_RESOURCE_GROUP
end_sql
q0 = QueryByName()
call TSetParam(q0,"P",__params[2])
call TExecute(q0)
count0 = TRecordCount(q0)
call TFirst(q0)
unused = 0
sum0 = 0
days0 = 0
for j = 1 to count0
rg_id = TGetFld(q0,"AU_RESOURCE_GROUP")
start_sql "*" "KOSMOS"
SELECT SDATE1, SDATE2, STIME1, STIME2
FROM AU_STEP_SCHED
WHERE (AU_STEP_SCHED.AU_PROCESS = :P) AND (OPTIMUM_FLAG = '1')
AND (AU_RESOURCE_GROUP = :R)
ORDER BY AU_STEP_SCHED
end_sql
q = QueryByName()
call TSetParam(q,"P",__params[2])
call TSetParam(q,"R",rg_id)
call TExecute(q)
call TFirst(q)
d1 = TGetFld(q,"SDATE1")
t1 = TGetFld(q,"STIME1")
d1 = DateToNum(d1)
t1 = TimeToNum(t1)
call TLast(q)
d2 = TGetFld(q,"SDATE2")
t2 = TGetFld(q,"STIME2")
d2 = DateToNum(d2)
t2 = TimeToNum(t2)
call FreeEmbSQL(q)
days = d2 + t2 - d1 - t1
days0 = days0 + days
sum = SelectFrom("SELECT SUM(STIME) FROM AU_STEP_SCHED WHERE (AU_PROCESS=",__params[2],") AND (AU_RESOURCE_GROUP=",rg_id,") AND (OPTIMUM_FLAG = '0')")
sum0 = sum0 + sum
unused = unused + days - sum
call TNext(q0)
next
call FreeEmbSQL(q0)
unused = unused * 24
unused = format("%.2f",unused)
sum0 = sum0 * 24
days0 = days0 * 24
val_caption[id] = unused
val_unit[id] = "Hours"
row_color[id] = RGBToColor(193,255,132)
expand_collapse[id] = True
expand_to_window[id]= True
expanded[id] = True
expand_height[id] = 0
agent_function[id] = "GANTT_SCHED_GAP_OPT"
agent_function_param1[id] = __params[2]
{--------------------------------- Optimized Usage of Machine Time ----------------------------}
id = id + 1
lab_caption[id] = "Optimized Usage of Machine Time"
pc = 0+(days0-unused) / days0 * 100
pc = format("%.1f",pc)
val_caption[id] = pc
val_unit[id] = "%"
row_color[id] = RGBToColor(193,255,132)
expand_collapse[id] = True
expand_to_window[id]= True
expanded[id] = True
expand_height[id] = 0
agent_function[id] = "GANTT_SCHED_GAP_OPT"
agent_function_param1[id] = __params[2]
{--------------------------------- Unused Machine Time in Hours----------------------------}
id = id + 1
lab_caption[id] = "Unused Machine Time in Hours"
start_sql "*" "KOSMOS"
SELECT AU_RESOURCE_GROUP
FROM AU_STEP_SCHED
WHERE (AU_STEP_SCHED.AU_PROCESS = :P) AND (OPTIMUM_FLAG = '0')
GROUP BY AU_RESOURCE_GROUP
end_sql
q0 = QueryByName()
call TSetParam(q0,"P",__params[2])
call TExecute(q0)
count0 = TRecordCount(q0)
call TFirst(q0)
unused = 0
sum0 = 0
days0 = 0
for j = 1 to count0
rg_id = TGetFld(q0,"AU_RESOURCE_GROUP")
start_sql "*" "KOSMOS"
SELECT SDATE1, SDATE2, STIME1, STIME2
FROM AU_STEP_SCHED
WHERE (AU_STEP_SCHED.AU_PROCESS = :P) AND (OPTIMUM_FLAG = '0')
AND (AU_RESOURCE_GROUP = :R)
ORDER BY AU_STEP_SCHED
end_sql
q = QueryByName()
call TSetParam(q,"P",__params[2])
call TSetParam(q,"R",rg_id)
call TExecute(q)
call TFirst(q)
d1 = TGetFld(q,"SDATE1")
t1 = TGetFld(q,"STIME1")
d1 = DateToNum(d1)
t1 = TimeToNum(t1)
call TLast(q)
d2 = TGetFld(q,"SDATE2")
t2 = TGetFld(q,"STIME2")
d2 = DateToNum(d2)
t2 = TimeToNum(t2)
call FreeEmbSQL(q)
days = d2 + t2 - d1 - t1
days0 = days0 + days
sum = SelectFrom("SELECT SUM(STIME) FROM AU_STEP_SCHED WHERE (AU_PROCESS=",__params[2],") AND (AU_RESOURCE_GROUP=",rg_id,") AND (OPTIMUM_FLAG = '0')")
sum0 = sum0 + sum
unused = unused + days - sum
call TNext(q0)
next
call FreeEmbSQL(q0)
unused = unused * 24
unused = format("%.2f",unused)
sum0 = sum0 * 24
days0 = days0 * 24
val_caption[id] = unused
val_unit[id] = "Hours"
row_color[id] = RGBToColor(252,153,135)
expand_collapse[id] = True
expand_to_window[id]= True
expanded[id] = True
expand_height[id] = 0
agent_function[id] = "GANTT_SCHED_GAP"
agent_function_param1[id] = __params[2]
{--------------------------------- Usage of Machine Time ----------------------------}
id = id + 1
lab_caption[id] = "Usage of Machine Time"
pc = 0+(days0-unused) / days0 * 100
pc = format("%.1f",pc)
val_caption[id] = pc
val_unit[id] = "%"
row_color[id] = RGBToColor(252,153,135)
expand_collapse[id] = True
expand_to_window[id]= True
expanded[id] = True
expand_height[id] = 0
agent_function[id] = "GANTT_SCHED_GAP"
agent_function_param1[id] = __params[2]
lab_count = id
return
endif
{.......................................INCREMENTAL_WORKFORCE................................}
if (function = "INCREMENTAL_WORKFORCE") then {keep}
line_series = True
title = strcat("Workforce Chart for Process ",__params[2])
draw_type = "CHART"
y_count = 1
show_legend = True
start_sql "*" "KOSMOS"
SELECT AU_STEP_SCHED.AU_PROCESS, AU_STEP_SCHED.ACTIVITY, AU_STEP_SCHED.SWORKERS, AU_RESOURCE_LOG.STIME0, AU_RESOURCE_LOG.MODE
FROM AU_RESOURCE_LOG INNER JOIN AU_STEP_SCHED ON AU_RESOURCE_LOG.AU_STEP_SCHED = AU_STEP_SCHED.AU_STEP_SCHED
WHERE (AU_STEP_SCHED.AU_PROCESS = :PR)
ORDER BY AU_RESOURCE_LOG.STIME0
end_sql
q = QueryByName()
call TSetParam(q,"PR",__params[2])
call TExecute(q)
msg = error_message(q)
count1 = TRecordCount(q)
i = 1
sum1 = 0
sum2 = 0
call TFirst(q)
for j = 1 to count1
activ = TGetFld(q,"ACTIVITY")
x[i] = TGetFld(q,"STIME0")
y = TGetFld(q,"SWORKERS")
mode = TGetFld(q,"MODE")
if (activ = "setup") then
y1[i] = sum1
i = i + 1
if (mode = "START") then
sum1 = sum1 + y
endif
if (mode = "STOP") then
sum1 = sum1 - y
endif
i1 = i - 1
x[i] = x[i1]
y1[i] = sum1
i = i + 1
endif
call TNext(q)
next
call FreeEmbSQL(q)
count = i - 1
for i = 1 to count
call trace("i=",i,", x=",x[i],", y=",y1[i])
next
color[1] = StringToColor("clBlue")
color[2] = StringToColor("clRed")
color[3] = StringToColor("clGreen")
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 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
{.......................................GANTT_SCHED_OPT..........................}
if (function = "GANTT_SCHED_OPT") then
gantt_series = True
title = strcat("Optimum Scheduled Activities for Process : ",__params[2])
bottom_axis_title = "Time"
left_axis_title = "Machinery"
draw_type = "CHART"
y_count = 1
show_legend = True
start_sql "*" "KOSMOS"
SELECT AU_STEP_SCHED.*, AU_RESOURCE_GROUP.GTITLE
FROM AU_STEP_SCHED INNER JOIN AU_RESOURCE_GROUP
ON AU_STEP_SCHED.AU_RESOURCE_GROUP = AU_RESOURCE_GROUP.AU_RESOURCE_GROUP
WHERE (AU_PROCESS= :PR) AND (OPTIMUM_FLAG = "1")
ORDER BY AU_STEP_SCHED
end_sql
q = QueryByName()
call TSetParam(q,"PR",__params[2])
call TExecute(q)
count = TRecordCount(q)
call TFirst(q)
for i = 1 to count
color[i] = TGetFld(q,"COLOR_ID")
y[i] = TGetFld(q,"AU_RESOURCE_GROUP")
descr[i] = TGetFld(q,"GTITLE")
hint[i] = TGetFld(q,"OREASON")
d1 = TGetFld(q,"SDATE1")
d2 = TGetFld(q,"SDATE2")
t1 = TGetFld(q,"STIME1")
t2 = TGetFld(q,"STIME2")
d1 = DateToNum(d1)
d2 = DateToNum(d2)
t1 = TimeToNum(t1)
t2 = TimeToNum(t2)
d1[i] = d1 + t1
d2[i] = d2 + t2
call TNext(q)
next
call FreeEmbSQL(q)
return
endif
{.......................................GANTT_SCHED..........................}
if (function = "GANTT_SCHED") then
gantt_series = True
title = strcat("Scheduled Activities for Process : ",__params[2]," (Restrictions)")
bottom_axis_title = "Time"
left_axis_title = "Machinery"
draw_type = "CHART"
y_count = 1
show_legend = True
start_sql "*" "KOSMOS"
SELECT AU_STEP_SCHED.*, AU_RESOURCE_GROUP.GTITLE
FROM AU_STEP_SCHED INNER JOIN AU_RESOURCE_GROUP
ON AU_STEP_SCHED.AU_RESOURCE_GROUP = AU_RESOURCE_GROUP.AU_RESOURCE_GROUP
WHERE (AU_PROCESS= :PR) AND (OPTIMUM_FLAG = '0')
ORDER BY AU_STEP_SCHED
end_sql
q = QueryByName()
call TSetParam(q,"PR",__params[2])
call TExecute(q)
count = TRecordCount(q)
call TFirst(q)
for i = 1 to count
color[i] = TGetFld(q,"COLOR_ID")
y[i] = TGetFld(q,"AU_RESOURCE_GROUP")
descr[i] = TGetFld(q,"GTITLE")
hint[i] = TGetFld(q,"OREASON")
d1 = TGetFld(q,"SDATE1")
d2 = TGetFld(q,"SDATE2")
t1 = TGetFld(q,"STIME1")
t2 = TGetFld(q,"STIME2")
d1 = DateToNum(d1)
d2 = DateToNum(d2)
t1 = TimeToNum(t1)
t2 = TimeToNum(t2)
d1[i] = d1 + t1
d2[i] = d2 + t2
call TNext(q)
next
call FreeEmbSQL(q)
return
endif
{.......................................GANTT_SCHED_SETUP..........................}
if (function = "GANTT_SCHED_SETUP") then
gantt_series = True
title = strcat("Scheduled Setup for Process : ",__params[2])
bottom_axis_title = "Time"
left_axis_title = "Machinery"
draw_type = "CHART"
y_count = 1
show_legend = True
start_sql "*" "KOSMOS"
SELECT AU_STEP_SCHED.*, AU_RESOURCE_GROUP.GTITLE
FROM AU_STEP_SCHED INNER JOIN AU_RESOURCE_GROUP
ON AU_STEP_SCHED.AU_RESOURCE_GROUP = AU_RESOURCE_GROUP.AU_RESOURCE_GROUP
WHERE (AU_PROCESS= :PR) AND (ACTIVITY LIKE 'setup%') AND (OPTIMUM_FLAG = '0')
ORDER BY AU_STEP_SCHED
end_sql
q = QueryByName()
call TSetParam(q,"PR",__params[2])
call TExecute(q)
count = TRecordCount(q)
call TFirst(q)
for i = 1 to count
color[i] = StringToColor("clRed")
y[i] = TGetFld(q,"AU_RESOURCE_GROUP")
descr[i] = TGetFld(q,"GTITLE")
hint[i] = TGetFld(q,"OREASON")
d1 = TGetFld(q,"SDATE1")
d2 = TGetFld(q,"SDATE2")
t1 = TGetFld(q,"STIME1")
t2 = TGetFld(q,"STIME2")
d1 = DateToNum(d1)
d2 = DateToNum(d2)
t1 = TimeToNum(t1)
t2 = TimeToNum(t2)
d1[i] = d1 + t1
d2[i] = d2 + t2
call TNext(q)
next
call FreeEmbSQL(q)
return
endif
{.......................................GANTT_SCHED_PROD.........................}
if (function = "GANTT_SCHED_PROD") then
gantt_series = True
title = strcat("Scheduled Production for Process : ",__params[2])
bottom_axis_title = "Time"
left_axis_title = "Machinery"
draw_type = "CHART"
y_count = 1
show_legend = True
start_sql "*" "KOSMOS"
SELECT AU_STEP_SCHED.*, AU_RESOURCE_GROUP.GTITLE
FROM AU_STEP_SCHED INNER JOIN AU_RESOURCE_GROUP
ON AU_STEP_SCHED.AU_RESOURCE_GROUP = AU_RESOURCE_GROUP.AU_RESOURCE_GROUP
WHERE (AU_PROCESS= :PR) AND (ACTIVITY LIKE 'prod%') AND (OPTIMUM_FLAG = '0')
ORDER BY AU_STEP_SCHED
end_sql
q = QueryByName()
call TSetParam(q,"PR",__params[2])
call TExecute(q)
count = TRecordCount(q)
call TFirst(q)
for i = 1 to count
color[i] = StringToColor("clLime")
y[i] = TGetFld(q,"AU_RESOURCE_GROUP")
descr[i] = TGetFld(q,"GTITLE")
hint[i] = TGetFld(q,"OREASON")
d1 = TGetFld(q,"SDATE1")
d2 = TGetFld(q,"SDATE2")
t1 = TGetFld(q,"STIME1")
t2 = TGetFld(q,"STIME2")
d1 = DateToNum(d1)
d2 = DateToNum(d2)
t1 = TimeToNum(t1)
t2 = TimeToNum(t2)
d1[i] = d1 + t1
d2[i] = d2 + t2
call TNext(q)
next
call FreeEmbSQL(q)
return
endif
{.......................................GANTT_SCHED_GAP.........................}
if (function = "GANTT_SCHED_GAP") then
gantt_series = True
title = strcat("Unused Times for Process : ",__params[2])
bottom_axis_title = "Time"
left_axis_title = "Machinery"
draw_type = "CHART"
y_count = 1
show_legend = True
start_sql "*" "KOSMOS"
SELECT AU_STEP_SCHED.*, AU_RESOURCE_GROUP.GTITLE
FROM AU_STEP_SCHED INNER JOIN AU_RESOURCE_GROUP
ON AU_STEP_SCHED.AU_RESOURCE_GROUP = AU_RESOURCE_GROUP.AU_RESOURCE_GROUP
WHERE (AU_PROCESS= :PR) AND (OPTIMUM_FLAG = '0')
ORDER BY AU_STEP_SCHED
end_sql
q = QueryByName()
call TSetParam(q,"PR",__params[2])
call TExecute(q)
count = TRecordCount(q)
call TFirst(q)
off = 0
for i = 1 to count
rg_id1 = TGetFld(q,"AU_RESOURCE_GROUP")
d1 = TGetFld(q,"SDATE2")
t1 = TGetFld(q,"STIME2")
str1 = strcat(d1,t1)
call TNext(q)
d2 = TGetFld(q,"SDATE1")
t2 = TGetFld(q,"STIME1")
str2 = strcat(d2,t2)
d1 = DateToNum(d1)
d2 = DateToNum(d2)
t1 = TimeToNum(t1)
t2 = TimeToNum(t2)
rg_id2 = TGetFld(q,"AU_RESOURCE_GROUP")
dif_rg = rg_id2-rg_id1
dif_tim = d2+t2-d1-t1
if (dif_rg = 0) and (str1 <> str2) then
off = off + 1
color[off] = StringToColor("clBlue")
y[off] = rg_id1
descr[off] = strcat("Station ",y[off])
descr[off] = TGetFld(q,"GTITLE")
d1[off] = d1 + t1
d2[off] = d2 + t2
endif
next
count = off
call FreeEmbSQL(q)
return
endif
{.......................................GANTT_SCHED_GAP_OPT.........................}
if (function = "GANTT_SCHED_GAP_OPT") then
gantt_series = True
title = strcat("Optimized Unused Times for Process : ",__params[2])
bottom_axis_title = "Time"
left_axis_title = "Machinery"
draw_type = "CHART"
y_count = 1
show_legend = True
start_sql "*" "KOSMOS"
SELECT AU_STEP_SCHED.*, AU_RESOURCE_GROUP.GTITLE
FROM AU_STEP_SCHED INNER JOIN AU_RESOURCE_GROUP
ON AU_STEP_SCHED.AU_RESOURCE_GROUP = AU_RESOURCE_GROUP.AU_RESOURCE_GROUP
WHERE (AU_PROCESS= :PR) AND (OPTIMUM_FLAG = '1')
ORDER BY AU_STEP_SCHED
end_sql
q = QueryByName()
call TSetParam(q,"PR",__params[2])
call TExecute(q)
count = TRecordCount(q)
call TFirst(q)
off = 0
for i = 1 to count
rg_id1 = TGetFld(q,"AU_RESOURCE_GROUP")
d1 = TGetFld(q,"SDATE2")
t1 = TGetFld(q,"STIME2")
str1 = strcat(d1,t1)
call TNext(q)
d2 = TGetFld(q,"SDATE1")
t2 = TGetFld(q,"STIME1")
str2 = strcat(d2,t2)
d1 = DateToNum(d1)
d2 = DateToNum(d2)
t1 = TimeToNum(t1)
t2 = TimeToNum(t2)
rg_id2 = TGetFld(q,"AU_RESOURCE_GROUP")
dif_rg = rg_id2-rg_id1
dif_tim = d2+t2-d1-t1
if (dif_rg = 0) and (str1 <> str2) then
off = off + 1
color[off] = StringToColor("clBlue")
y[off] = rg_id1
descr[off] = strcat("Station ",y[off])
descr[off] = TGetFld(q,"GTITLE")
d1[off] = d1 + t1
d2[off] = d2 + t2
endif
next
count = off
call FreeEmbSQL(q)
return
endif
end_action
