macro to calculate the next scheduled play time

[jeremy savage]

Has anyone written a macro to calculate the next scheduled play time of a macro. You'd have to parse thru the macros.dat file to do so. I'd like to output theat time/date during the execution of the macro in question.

[Ernest]

Hi,
sounds interesting.
More details (code)?

E.

[jeremy savage]

thru this togtether and it seems to work so far:

//routine to calculate the next possible play time of a macroscheduler macro.
//The macro currently takes input manually and delivers the result to the user via message box and
//writes to a database.Macro must have a log file assigned (not necessarily active)
//11/29/02 12:12

vbstart
'function to send data toan SQL database
Sub Changenext(threadID,next_time)
On Error Resume Next
select case threadID
case "Alerts"
threadID="weather"
end select
Dim SQLString
set MyDB = CreateObject("ADODB.Connection")
MYDB.Open "DSN=central_s;UID=sa"
SQLString = "Update app_stat Set next_play = '" & next_time & "' Where (thread = '" & threadID & "')"
set rsapp_stat = MyDB.Execute(SQLString)
MyDB.Close
End Sub

' function to add 1 to day of week and come up with new day of week
function day_mod(daystr)
day_mod=(Cint(daystr) mod 7)+1
end function

'function to make a final date with date and time
function date_maker(datestr,timestr)
date_maker=datestr &" " & timestr
end function

'function to make full date w/ hrs and minutes
function date_time_maker(hstr,mstr)
date_time_maker=Date & " " & hstr & ":" & mstr &":00"
if hstr="nn" or mstr="nn" then date_time_maker=0
end function

'function to make time out of hrs and minutes
function time_maker(hstr,mstr)
time_maker= hstr & ":" & mstr & ":00"
if hstr="nn" or mstr="nn" then time_maker=0
end function

'function to generate current time
function current()
current=NOW
end function

'function to generate a date plus and increment in minutes
function add_date(int,thedate)
add_date=dateadd("n",int,thedate)
end function

'function to generate a date plus and increment in days
function add_date_day(int,thedate)
add_date_day=dateadd("d",int,thedate)
end function

'function to kick back the day of week given a date
function date_part(datestr)
date_part=datepart("w",datestr)
end function


'function to get difference of two dates
function date_diff(datestr1,datestr2)
date_diff=datediff("n",datestr2,datestr1)
end function

vbend


//create date and day number and constants
gdt>the_date
DOW>daynum
let>null=
let>l=0
let>c=-5
let>d=5

//input of the macro name is eliminated when used in a macro
input>name,Input Macro name here..

//look at the macros.dat file for schedule info..read line by line until name is found
label>start
let>l=l+1
readln>c:\program files\MJT net ltd\macro scheduler\macros.dat,l,line
if>line=##EOF##,exit
pos>name,line,1,result
if>result1,start

//get all the necessary parts ..this only works if macro has a log file set up
separate>line,:,parts

//get number representing the next day
vbeval>day_mod("%daynum%"),new_daynum
//get the full current date
vbeval>current,curr_date
//increment the date by 1
vbeval>add_date_day("1","%the_date%"),new_date

//get schedule interval in minutes..if interval is NULL,,make it zero
let>interval=parts_5
if>intervalnull,noset
let>interval=0
label>noset

//get the string of days in schedule
let>daystring=parts_2

//get original start time, make the full date as well
let>o_hr=parts_18
let>o_min=parts_19
vbeval>time_maker("%o_hr%","%o_min%"),o_date
vbeval>date_maker("%the_date%","%o_date%"),full_odate
let>xstr=full_odate

//get stop time
let>r_hr=parts_14
let>r_min=parts_15
vbeval>date_time_maker("%r_hr%","%r_min%"),r_stop
vbeval>date_diff("%curr_date%","%full_odate%"),differ

//check if the macro is running on a legal day
pos>daynum,daystring,1,result
if>result=0,next_day

//check if this is a correct time...
if>differ>c,next_legal
let>next_date=full_odate
goto>dbwrite

if>r_stop0,check_over
vbrun>changenext,%parts_1%,%next_date%
messagemodal>%next_date%
goto>exit

'has the new date gone past the limit?
label>check_over
vbeval>date_diff("%next_date%","%r_stop%"),differ

if>differ>0,next_day
label>dbwrite
vbrun>changenext,%Parts_1%,%next_date%
messagemodal>%next_date%
goto>exit

// this section increments the day until the day is legal and sets the time at
//the original time

label>next_day
Position>new_daynum,daystring,1,result
if>result=0,try_next
vbeval>date_maker("%new_date%","%o_date%"),new_date
vbrun>changenext,%Parts_1%,%new_date%
messagemodal>%new_date%
goto>exit

label>try_next
//inc the day and the date
vbeval>add_date_day("1","%new_date%"),new_date
vbeval>day_mod("%new_daynum%"),new_daynum
goto>next_day

// this section adds the interval to the original time until a new legal time is found
// if no legal time is found it goes to the next day
label>next_legal
if>interval=0,next_day
vbeval>add_date("%interval%","%xstr%"),temp_date
vbeval>date_diff("%temp_date%","%r_stop%"),differ
if>differ>0,next_day
vbeval>date_diff("%temp_date%","%curr_date%"),differ
let>xstr=temp_date
if>differ>d,there
if>differthere
let>next_date=temp_date
goto>dbwrite

label>exit

[Ernest]

Wow,
that's a "piece" of code!
So share it at MJT's "Scripts & Tips" section.

E.

[jeremy savage]

//routine to calculate the next possible play time of a macroscheduler macro.
//The macro currently takes input manually and delivers the result to the user via message box and
//writes to a database.Macro must have a log file assigned (not necessarily active)
//12/02/02 14:28

vbstart
'function to send data toan SQL database
Sub Changenext(threadID,next_time)
On Error Resume Next
select case threadID
case "Alerts"
threadID="weather"
end select
Dim SQLString
set MyDB = CreateObject("ADODB.Connection")
MYDB.Open "DSN=central_s;UID=sa"
SQLString = "Update app_stat Set next_play = '" & next_time & "' Where (thread = '" & threadID & "')"
set rsapp_stat = MyDB.Execute(SQLString)
MyDB.Close
End Sub

' function to add 1 to day of week and come up with new day of week
function day_mod(daystr)
day_mod=(Cint(daystr) mod 7)+1
end function

'function to make a final date with date and time
function date_maker(datestr,timestr)
date_maker=datestr &" " & timestr
end function

'function to make full date w/ hrs and minutes
function date_time_maker(hstr,mstr)
date_time_maker=Date & " " & hstr & ":" & mstr &":00"
if hstr="nn" or mstr="nn" then date_time_maker=0
end function

'function to make time out of hrs and minutes
function time_maker(hstr,mstr)
time_maker= hstr & ":" & mstr & ":00"
if hstr="nn" or mstr="nn" then time_maker=0
end function

'function to generate current time
function current()
current=NOW
end function

'function to generate a date plus and increment in minutes
function add_date(int,thedate)
add_date=dateadd("n",int,thedate)
end function

'function to generate a date plus and increment in minutes
function sub_date(int,thedate)
int=int*-1
sub_date=dateadd("n",int,thedate)
end function

'function to generate a date plus and increment in days
function add_date_day(int,thedate)
add_date_day=dateadd("d",int,thedate)
end function

'function to kick back the day of week given a date
function date_part(datestr)
date_part=datepart("w",datestr)
end function


'function to get difference of two dates
function date_diff(datestr1,datestr2)
date_diff=datediff("n",datestr2,datestr1)
end function

vbend


//create date and day number and constants
gdt>the_date
DOW>daynum
let>null=
let>l=0
let>c=-5
let>d=5

//input of the macro name is eliminated when used in a macro
input>name,Input Macro name here..

//look at the macros.dat file for schedule info..read line by line until name is found
label>start
let>l=l+1
readln>c:\program files\MJT net ltd\macro scheduler\macros.dat,l,line
if>line=##EOF##,exit
pos>name,line,1,result
if>result1,start

//get all the necessary parts ..this only works if macro has a log file set up
separate>line,:,parts

//get number representing the next day
vbeval>day_mod("%daynum%"),new_daynum
//get the full current date
vbeval>current,curr_date
//increment the date by 1
vbeval>add_date_day("1","%the_date%"),new_date

//get schedule interval in minutes..if interval is NULL,,make it zero
let>interval=parts_5
if>intervalnull,noset
let>interval=0
label>noset

//get the string of days in schedule
let>daystring=parts_2

//get original start time, make the full date as well
let>o_hr=parts_18
let>o_min=parts_19
vbeval>time_maker("%o_hr%","%o_min%"),o_date
vbeval>date_maker("%the_date%","%o_date%"),full_odate
let>xstr=full_odate

//get stop time
let>r_hr=parts_14
let>r_min=parts_15
vbeval>date_time_maker("%r_hr%","%r_min%"),r_stop
vbeval>date_diff("%curr_date%","%full_odate%"),differ

//check if the macro is running on a legal day
pos>daynum,daystring,1,result
if>result=0,next_day

//check if this is a correct time...
if>differ>c,next_legal,last_legal
let>next_date=full_odate
goto>dbwrite

if>r_stop0,check_over
vbrun>changenext,%parts_1%,%next_date%
messagemodal>%next_date%
goto>exit

'has the new date gone past the limit?
label>check_over
vbeval>date_diff("%next_date%","%r_stop%"),differ

if>differ>0,next_day
label>dbwrite
vbrun>changenext,%Parts_1%,%next_date%
messagemodal>%next_date%
goto>exit

// this section increments the day until the day is legal and sets the time at
//the original time

label>next_day
Position>new_daynum,daystring,1,result
if>result=0,try_next
vbeval>date_maker("%new_date%","%o_date%"),new_date
vbrun>changenext,%Parts_1%,%new_date%
messagemodal>%new_date%
goto>exit

label>try_next
//inc the day and the date
vbeval>add_date_day("1","%new_date%"),new_date
vbeval>day_mod("%new_daynum%"),new_daynum
goto>next_day

// this section adds the interval to the original time until a new legal time is found
// if no legal time is found it goes to the next day
label>next_legal
if>interval=0,next_day
vbeval>add_date("%interval%","%xstr%"),temp_date
vbeval>date_diff("%temp_date%","%r_stop%"),differ
if>differ>0,next_day
vbeval>date_diff("%temp_date%","%curr_date%"),differ
let>xstr=temp_date
if>differ>d,there
if>differthere
let>next_date=temp_date
goto>dbwrite


//section to calculate the leagl run time if current time is less than original date
label>last_legal
//is the current time greater than the stop date

//decrement the Original date
vbeval>sub_date("%interval%","%xstr%"),temp_date
//is this new date greater than the stop date?
vbeval>date_diff("%temp_date%","%r_stop%"),differ
if>differ>2,next_day
vbeval>date_diff("%temp_date%","%curr_date%"),differ
//if>differdifferxstr=temp_date
goto>last_legal

label>zone
let>next_date=temp_date
goto>dbwrite




label>exit

[jeremy savage]

//routine to calculate the next possible play time of a macroscheduler macro. Ignores monthly sched box.
//The macro currently takes input manually and delivers the result to the user via message box and
//writes to a database
//12/04/02 10:00

vbstart

'function to send data to a SQL database
Sub Changenext(threadID,next_time)
On Error Resume Next
Dim SQLString
set MyDB = CreateObject("ADODB.Connection")
MYDB.ConnectionTimeout=15
MYDB.Open "DSN=central_s;UID=sa"
SQLString = "Update app_stat Set next_play = '" & next_time & "' Where (thread = '" & threadID & "')"
set rsapp_stat = MyDB.Execute(SQLString)
MyDB.Close
End Sub

'function to create upper case string
function upper_case(str)
upper_case=Ucase(str)
end function

' function to add 1 to day of week and come up with new day of week
function day_mod(daystr)
day_mod=(Cint(daystr) mod 7)+1
end function

'function to make a final date with date and time
function date_maker(datestr,timestr)
date_maker=datestr &" " & timestr
end function

'function to make ftoday's full date w/ current date and added hrs and minutes
function date_time_maker(hstr,mstr)
date_time_maker=Date & " " & hstr & ":" & mstr &":00"
if hstr="nn" or mstr="nn" then date_time_maker=0
end function

'function to make time out of hrs and minutes
function time_maker(hstr,mstr)
time_maker= hstr & ":" & mstr & ":00"
if hstr="nn" or mstr="nn" then time_maker=0
end function

'function to generate current time
function current()
current=NOW
end function

'function to generate a date plus and increment in minutes
function add_date(int,thedate)
add_date=dateadd("n",int,thedate)
end function

'function to generate a date minus an increment in minutes
function sub_date(int,thedate)
int=int*-1
sub_date=dateadd("n",int,thedate)
end function

'function to generate a date plus and increment in days
function add_date_day(int,thedate)
add_date_day=dateadd("d",int,thedate)
end function

'function to kick back the day of week given a date
function date_part(datestr)
date_part=datepart("w",datestr)
end function


'function to get difference of two dates
function date_diff(datestr1,datestr2)
date_diff=datediff("n",datestr2,datestr1)
end function

'function to make a valid minute or hour
function time_fix(str)
time_fix=str
if str="0" then time_fix="00"
end function


vbend


//create date and day number and constants
gdt>the_date
DOW>daynum
let>null=
let>l=0
let>c=-5
let>d=5
let>neg=-1

//input of the macro name can be eliminated when used in a macro
//input>name,Input Macro name here..
vbeval>upper_case("%name%"),name

//look at the macros.dat file for schedule info..read line by line until name is found
//take care of case issues ..compare in upper case
label>start
let>l=l+1
readln>c:\program files\MJT net ltd\macro scheduler\macros.dat,l,line
if>line=##EOF##,exit
pos>:,line,1,colon
let>colon=colon-1
MidStr>line,1,colon,sline
vbeval>upper_case("%sline%"),sline

pos>name,sline,1,result
if>result1,start
//get all the necessary parts
separate>line,:,parts

//get numbers representing the next day and date
vbeval>day_mod("%daynum%"),new_daynum
//get the full current date
vbeval>current,curr_date
//increment the date by 1
vbeval>add_date_day("1","%the_date%"),new_date

//get schedule interval in minutes..if interval is NULL,,make it zero
let>interval=parts_5
if>intervalnull,noset
let>interval=0
label>noset
//make a negative interval
let>tint=interval*neg
//get the string of days in schedule
let>daystring=parts_2

// if no daystring exists then exit macro
if>daystring=null,exit

//get original start time, make the full date as well
//check for log file and adjust values accordingly by shifting one over
if>parts_9"",log_file
let>o_hr=parts_17
let>o_min=parts_18
let>r_hr=parts_13
let>r_min=parts_14
goto>make_time
label>log_file
let>o_hr=parts_18
let>o_min=parts_19
//get stop time
let>r_hr=parts_14
let>r_min=parts_15

//turn the repeat and stop times into full dates with time
label>make_time
//make sure single 0 is now 00 in hr and min values
vbeval>time_fix("%r_hr%"),r_hr
vbeval>time_fix("%r_min%"),r_min
vbeval>time_fix("%o_hr%"),o_hr
vbeval>time_fix("%o_min%"),o_min
vbeval>time_maker("%o_hr%","%o_min%"),o_date
vbeval>date_time_maker("%r_hr%","%r_min%"),r_stop
vbeval>date_maker("%the_date%","%o_date%"),full_odate
//preserver the original start date in an extra place
let>xstr=full_odate

//get the difference between the current time and the original start date
vbeval>date_diff("%curr_date%","%full_odate%"),differ

//check if the macro is running on a legal day by stepping thru the day string
pos>daynum,daystring,1,result
if>result=0,next_day

//check if this is a correct time...

//if difference is more than a full interval behind..look for last legal date prior to original
if>differdiffer>0,next_legal
if>differ=0,next_legal

//else the next date is the original date
let>next_date=full_odate
goto>dbwrite


label>dbwrite
vbrun>changenext,%Parts_1%,%next_date%
//messagemodal>%next_date%
goto>exit

// this section increments the day until the day is legal and sets the time at
//the original time

label>next_day
Position>new_daynum,daystring,1,result
if>result=0,try_next
vbeval>date_maker("%new_date%","%o_date%"),new_date
vbrun>changenext,%Parts_1%,%new_date%
//messagemodal>%new_date%
goto>exit

label>try_next
//inc the day and the date
vbeval>add_date_day("1","%new_date%"),new_date
vbeval>day_mod("%new_daynum%"),new_daynum
goto>next_day

// this section adds the interval to the original time until a new legal time is found
// if no legal time is found it goes to the next day
label>next_legal
if>interval=0,next_day
vbeval>add_date("%interval%","%xstr%"),temp_date
if>r_stop=0,skip_diff
vbeval>date_diff("%temp_date%","%r_stop%"),differ
if>differ>0,next_day
label>skip_diff
vbeval>date_diff("%temp_date%","%curr_date%"),differ
let>xstr=temp_date
if>differ>0,there
if>differdiffer=0,next_legal
label>there
let>next_date=temp_date
goto>dbwrite


//section to calculate the legal run time if current time is less than original date
label>last_legal
if>interval0,dec_it
let>next_date=xstr
goto>dbwrite
//is the current time greater than the stop date
label>dec_it
if>r_stop=0,dec_that
let>next_date=xstr
goto>dbwrite
label>dec_that
//decrement the Original date
vbeval>sub_date("%interval%","%xstr%"),temp_date
if>r_stop=0,skip_differ
//is this new date greater than the stop date?
vbeval>date_diff("%temp_date%","%r_stop%"),differ
if>differ>0,next_day
label>skip_differ
vbeval>date_diff("%temp_date%","%curr_date%"),differ
if>differ=interval,zone
if>differxstr=temp_date
goto>last_legal

label>zone
let>next_date=temp_date
goto>dbwrite

label>exit