Formula talk

Formula talk

Postby kunkel321 » Thu Feb 14, 2013 9:22 pm

I was just reading the /formulas page of the wiki.
http://freeplane.sourceforge.net/wiki/i ... p/Formulas

Regarding how to implement Excel-like formulas...
Excel allows a person to write User-Defined Functions. To do so, you use the VB Editor to insert a Module Object, then put you functions in that
=========
Sub MyGreatThing(value1, val2)
code stuff
End Sub
===========
Then from the spreadsheet you put "=MyGreatThing(A1,A2)" in one of the cells.

Maybe for Freeplane, you could have a FormulaDefinitions.groovy file in the scripts folder that would serve the same purpose as the VB module.
FP would then look in FormulaDefinitions.groovy when evaluating a formula.

The groovy file would also be a place to name nodes, for example
ID_1163147571 = "Fred"
kunkel321
 
Posts: 131
Joined: Thu Aug 16, 2012 4:23 am

Re: Formula talk

Postby boercher » Thu Feb 14, 2013 10:47 pm

kunkel321 wrote:Maybe for Freeplane, you could have a FormulaDefinitions.groovy file in the scripts folder that would serve the same purpose as the VB module.

Yes that would be great. But it works nearly like that, but you have to compile the groovy file to a class file which makes script execution faster. Here's how you can implement a new function "square()":

0. Add ''lib'' to the script class path under Tools->Preferences->Plugins->Formulas->Script classpath

1. Create the ''<freeplaneuserdir>/lib'' directory and open it on the command line. On Linux I did that with this that way:
Code: Select all
cd ~/.freeplane/1.2.x
mkdir lib
cd lib


2. Create a file ''FP.groovy'' in this directory with the following content
Code: Select all
def static square(x) {
      x * x
}


3. Open the directory on the command line and create a FP.class file.

3.1 I you have a full Groovy fully installed simply:
Code: Select all
groovyc FP.groovy


3.2 With only Freeplane installed:
Code: Select all
java -cp <freeplane_installation_dir>/plugins/org.freeplane.plugin.script/lib/groovy-all-1.8.6.jar org.codehaus.groovy.tools.FileSystemCompiler FP.groovy


4. Restart Freeplane

5. Create a node with the content
Code: Select all
=FP.square(2)


kunkel321 wrote:The groovy file would also be a place to name nodes

No, such data would belong to the map itself. But you could add a feature request for that if we don't have it already.

Volker

P.S.: Under Linux you can use this transscript:
Code: Select all
cd ~/.freeplane/1.2.x
mkdir lib
cd lib
cat > FP.groovy
def static square(x) {
      x * x
}
groovyc FP.groovy
boercher
 
Posts: 644
Joined: Tue Jul 26, 2011 7:13 am

Re: Formula talk

Postby kunkel321 » Mon Feb 18, 2013 7:28 pm

It worked!!!
Very cool--and thank you for the excellent instructions, Volker! :D

I'm curious, the PF.groovy file gets added to this jar file, correct?
C:\Program Files\Freeplane\plugins\org.freeplane.plugin.script\lib\groovy-all.jar

Note to any noobs (like me), The thing to compile with looks something like
java -cp "C:\Program Files\Freeplane\" /plugins/org.freeplane.plugin.script/lib/groovy-all-1.8.6.jar org.codehaus.groovy.tools.FileSystemCompiler FP.groovy
(on my system, at least)
And you get to the command thing with <Win Button +R>
:)

I'd like to recreate Excel's =NETWORK() function... I'll read up more on groovy date functions though, so I can ask questions more intelligently...
kunkel321
 
Posts: 131
Joined: Thu Aug 16, 2012 4:23 am

Re: Formula talk

Postby boercher » Tue Feb 19, 2013 12:47 am

kunkel321 wrote:I'm curious, the PF.groovy file gets added to this jar file, correct?

No, JARs are just a convenient way to bundle .class files but .class files can be used stand-alone as it is here.

I'm looking forward to your implementation of excel functions. There are some people already waiting for that :). When dealing with date and time don't miss to check out Groovy's TimeCategory! And here's a little script that might help you:
Code: Select all
def x(start, end, Date... holidays) {
    println "$start - $end"; holidays.each{println it}
}
def now = new Date()
x(now, now+10, now+2, now+5, now+6)


Volker
boercher
 
Posts: 644
Joined: Tue Jul 26, 2011 7:13 am

Re: Formula talk

Postby kunkel321 » Wed Feb 20, 2013 11:36 pm

Thanks for the cookbook link!

I put together a hodge podge of pseudo code...

The idea is that a single map will have many projects. Each project will have its own specific start date and duration. The start dates won't always be "today."
The duration will be a specific number of "workdays" (i.e. excluding weekends and holidays).
The holidays are defined as children of the node named "holidays." (Though just using an .ini file would also be a possibility)

The map setup will be like
Code: Select all
Project One
    2/20/2013     //Node ID = 1001, this is the start of project one
    =networkdays( ID:1001,35,ID:1003)
Project Two
    2/1/2013      //Node ID = 1002, start of proj two
    =networkdays( ID:1002,35,ID:1003)                     
Holidays           //Node ID = 1003
    2/14/2013
    7/4/20/13
    12/25/2013
    1/1/2014


The networkdays() function has these components:
=networkdays(<ID of node with start date>,<duration of days>,<ID of node that lists holidays>)
EDIT: Sorry I referenced the wrong Excel function... I was thinking of =WORKDAYS(), not "networkdays!"
Sorry about that! See also discussion here: viewtopic.php?f=1&t=553#p2810


The pseudo code(which may amuse you :D ) is
Code: Select all
// NOTE TO READERS: THIS IS NOT WORKING CODE !!!!
def FP.Networkdays(start,duration,holidays)
    import org.codehaus.groovy.runtime.TimeCategory
    def startDate = start //This date is read from the branch that is the sibling to the one with the formula.
    def dueDate = startDate  //This starts as the same as "startDate" but then gets incremented.
    def holidates = node("holidays").children.all     //Points to children of node "Holidays."   
    def NumDays = duration   
    def Tally = 0
    def DayOfWeek = dueDate( //converted to day of week )
        while ( Tally-- < NumDays ) {
            // Assess each day. Increment "dueDate" with each loop, but only increment "Tally" if
            // The day is a work day (i.e. not a weekend, and not a holiday). 
            dueDate = dueDate +1
                If dueDate(day of week) != "Sat"
                And IF dueDate(day of week) != "Sun"       
                And IF dueDate(day of week) != node.holidates.children
                    Tally = Tally +1               // Or maybe "++Tally"
}


Hopefully the logic as at least sound. Note that I was unable to locate a way to get the day-of-week of a particular date... Surly it's possible though.
Thoughts?
kunkel321
 
Posts: 131
Joined: Thu Aug 16, 2012 4:23 am

Re: Formula talk

Postby kunkel321 » Wed Mar 06, 2013 5:00 pm

I think I'm getting closer to a realistic " WORKDAYS()" solution here...

Edit: I'm taking out the weekDay variable.... Calendar.DAY_OF_WEEK can be calculated on-the-fly during each loop...
Code: Select all
NOTICE TO FORUM USERS: THIS IS NOT WORKING CODE!!!
def FP.Networkdays(start,duration,holidays)
    import org.codehaus.groovy.runtime.TimeCategory
    def startDate = node['start'] //This date is read from the branch that is the sibling to the one with the formula.
    def dueDate = startDate  //This starts as the same as "startDate" but then gets incremented.
    def NumDays = node['duration']   
    def Tally = 0
        while ( Tally-- < NumDays ) {
            // Assess each day. Increment "dueDate" with each loop, but only increment "Tally" if
            // The day is a work day (i.e. not a weekend, and not a holiday). 
            dueDate = dueDate.to.date.clearTime() + 1
                If Date.parse('dueDate')[Calendar.DAY_OF_WEEK] != "SATURDAY"
                And IF Date.parse('dueDate')[Calendar.DAY_OF_WEEK] != "SUNDAY"       
                And IF dueDate != =ID_123456789.children.find {}  // compare it against the holidays.
                    ++Tally //increment Tally variable by one


Also, I wanted to say thanks to Volker for his directions on setting up a scripting environment, here
http://freeplane.sourceforge.net/wiki/i ... ment_setup
It went pretty smoothly--and I have to say this Eclipse tool is pretty cool!
kunkel321
 
Posts: 131
Joined: Thu Aug 16, 2012 4:23 am

Re: Formula talk

Postby kunkel321 » Thu Mar 07, 2013 8:41 pm

A bit off-topic here, but I thought I'd share a couple of Excel "gantt charts" that I've done. They both make use of the =WORKDAY() function discussed above.

GanttSpreadsheets.zip
(216.44 KiB) Downloaded 71 times


"GanttSheet" is an xls and will work on older versions of Excel. It was essentially a proof-of-concept project.
"temp~Evals" is an xlsx and will require at least Excel 2007. It is the one I actually use and is fairly involved. (obviously the names are fake)
I doubt if either will work on OO or Google Docs.

Note that I quoted "gantt chart" because they don't show within-project dependencies--they're designed to show one project on each line.

Note also: There are no macros in either spreadsheet, so you should be able to use them without getting "macrovirus" warnings from Office. Lastly, The formulas and conditional formatting are "locked," but there's no password.... Simply go to "Unprotect Sheet" to unlock.
kunkel321
 
Posts: 131
Joined: Thu Aug 16, 2012 4:23 am

Re: Formula talk

Postby simong2013 » Tue Jan 21, 2014 8:46 pm

The Freeplane wiki links to this thread, saying it describes how to compile a groovy script. Given that, I thought I'd add a note explaining how to compile a groovy script that uses some of the Freeplane libraries.

I wrote a script, based on one in Jodi Krol's scriptlib, that imported some of the Freeplane libraries, such as org.freeplane.features.edge.EdgeStyle. I wanted to compile that script to allow it to be used in other scripts I'm writing. When compiling the script I needed to tell the compiler where to find the Freeplane libraries. That's done with the -cp command line switch, which sets the CLASSPATH. eg

Code: Select all
groovyc -cp "C:/Program Files/Freeplane/plugins/org.freeplane.plugin.script/lib/plugin.jar;C:/Program Files/Freeplane/core/org.freeplane.core/lib/freeplaneeditor.jar" BranchFormatter.NodeShape.groovy


Note this example was from a Windows machine. I suspect you may need to use a different separator character between jar files for Linux.

One problem I faced was trying to work out which jar files contained the Freeplane libraries I wanted to import. Initially I tried opening the various jar files in the Freeplane lib folder with 7Zip, to see what classes they contained. In the end, however, I found it was easier to use trial and error since there are only a dozen or so jar files that were likely candidates. Alternatively, since there are so few jar files, you could add them all to the CLASSPATH.

An aside about using package names when compiling a script:

If the script being compiled is non-trivial or you want to compile several scripts, you may want to specify a package name at the top of the script, eg

Code: Select all
package branchformatter

import org.freeplane.plugin.script.proxy.Proxy

class NodeShape {

    static void String getShapeStyle(Proxy.Node nodeToRead) {
        ...
    }
}


As someone new to both Java and Groovy the package name seems to act like a namespace. So when you import the compiled script into another script you would use:

Code: Select all
import static branchformatter.NodeShape.getShapeStyle

def result = getShapeStyle(node)
def title = "Import Test"
def message = "Node Shape: " + result
ui.informationMessage(ui.frame, message, title)


or, alternatively:

Code: Select all
import branchformatter.NodeShape

def result = NodeShape.getShapeStyle(node)
:
:


These examples are specific to importing static methods of a class.

Note the static keyword in the first import example and the lack of it in the second. Also note that I had to use the class name in the second example in the line of code that called the imported method, whereas I didn't need to include the class name in the first example.

As I mentioned above I'm totally new to both Java and Groovy so take this post with a grain of salt. There may be a much better way of achieving what I wanted to achieve. Having said that, it worked for me.
simong2013
 
Posts: 9
Joined: Thu Dec 26, 2013 2:49 am

Re: Formula talk

Postby boercher » Tue Jan 28, 2014 5:34 pm

Thanks for this post. Probably the best way to take up scripting in Freeplane (at least if you are really interested in it) is to use Eclipse, an integrated development environment.

Volker
boercher
 
Posts: 644
Joined: Tue Jul 26, 2011 7:13 am


Return to Open Discussion

Who is online

Users browsing this forum: No registered users and 1 guest

cron