User guide

ProjectForge® 2013

Version: 5.4
Date: 2014-05-15
Project: ProjectForge® 2013
URL: www.projectforge.org
Author: Kai Reinhard
mailtok.reinhard@me.com

downloadPrint-friendly version: UserGuide.pdf

Contents

1Introduction
2Common concepts
2.1 Full-indexed search
2.1.1 Automatical wildcards
2.1.2 Search for structure elements
2.2 Quick-select
2.3 Filter for search in history of changes
2.4 Auto completion
2.5 Favorites / bookmarks
2.5.1 Favorites / bookmarks in list views
2.5.2 Favorites / bookmarks of time-sheets
2.6 Stay logged-in
3Calendar
3.1 Quick-time-period-selection
3.2 Holidays
3.3 Support of different time zones
3.4 View modes
3.4.1 Calendar views: month, week, day
3.4.2 Customizable views with fast switch
3.5 Drag&drop and range-select functionality
3.6 Roadmap
4Address book
4.1 Reverse search of phone numbers
4.2 Personal favorites
4.2.1 Export of vcards
4.2.1.1 Hint for Apple contacts
4.2.2 Export of personal list of telephone numbers
4.2.3 Personal list of phone numbers for your Snom telephone
4.3 Export of the list of addresses
4.4 Animation of phone number / zoom
4.5 Direct call via telephony system
4.5.1 Configuration
5Structure elements in ProjectForge (former tasks)
5.1 Consumption
5.1.1 Consumption bars for open structure elements
5.1.2 Consumption bars for finished structure elements
5.1.3 Suppressing the status of consumption bars
5.2 Booking of time sheets
6Order book
6.1 E-mail notification
7Scripting in ProjectForge®
7.1 Examples
7.1.1 Example: Hibernate criterion API
7.1.2 Example: Usage of ProjectForge®filters
7.2 Usage of date values
7.3 Access rights (DAOs)
7.4 Creation of Excel sheets
7.4.1 Example: Excel export of simple lists
7.4.2 Example: Excel export of joined objects
7.4.3 Own join objects
7.4.4 Formats
7.4.5 Modification of existing Excel files
7.4.6 Cell formats
7.4.7 Excel and time periods
7.5 Creation of diagrams and charts
7.6 Creation of zip archives containing multiple files
7.7 Nested and indexed properties
7.8 Import of Excel files
8Financial administration with ProjectForge®
8.1 Invoices
8.1.1 Assignment of cost units
8.1.2 Clone functionality for invoices (inbound and outbound)
8.2 Liquidity planning / forecast
8.2.1 Calculation of expected dates of payments
8.3 Datev-Import (German)
8.3.1 Grundsätzliches zum ProjectForge®-Excel-Import
8.3.1.1 Schritt 1: Auswahl der zu importierenden Excel-Datei
8.3.1.2 Schritt 2: Daten werden eingelesen
8.3.1.3 Schritt 3: Verproben
8.3.1.4 Schritt 4: Prüfen, selektieren und Übernahme
8.3.1.5 Schritt 5: Import abgeschlossen
8.3.2 Import der Buchungskonten (Kontenplan)
8.3.2.1 Format
8.3.3 Import der Buchungssätze (01-12)
8.3.3.1 Format
8.4 Other imports
8.4.1 Salaries of employees
8.5 Time sheets
8.5.1 Protection
8.5.2 Cost units
8.5.3 Booking of time sheets
8.5.4 Monthly employee reports
8.5.5 JIRA support
8.6 Cost unit accounting
8.6.1 Fraction of working time
8.7 Reporting via Report objectives (in progress)
9Data protection
9.1 Passwords
9.2 History of changes
9.3 Logging
9.4 Phone call - numbers
AGlossary

List of figures

1Full-indexed search
2Extended search filter for searching in the history of changes
3Favorites / bookmarks in list views
4Favorites / bookmarks in edit views
5Quick-time-period selection with browse mode for months and weeks
6Month display mode of team calendar.
7Week display mode of team calendar. The red line is the current time-line.
8Customize your view filters with own colors etc. You may switch the view with only two clicks.
9An example of subscribed calendars of ProjectForge in Apple's ical.
10An example of subscribed calendars of ProjectForge in Apple's ical.
11You may remove or modify single events or future events of recurrence events.
12You may configure the access to any calendar for groups and/or users.
13Create your personal address or telephone book.
14Choose the list of phone number for your Snom telephone.
15Import of the list of phone number via the web site of your Snom telephone device
16Animated zoom of phone numbers
17Direct phone call from the address book
18Direct phone call
19Hierarchy of structure elements
20Order book with e-mail notification
21Example of a script producing a chart
22Example file for an Excel import
23Liquidity with forecast of the cash flow
24Datev-Import, Schritt 1: Auswahl der Importdatei
25Datev-Import, Schritt 2: Einlesen der Datei
26Datev-Import, Schritt 3: Verproben
27Datev-Import, Schritt 4: Prüfen, Selektion und Übernahme
28Datev-Import, Schritt 5: Abschluss
29Excelformat für Datev-Import des Kontenplans
30Excelformat für Datev-Import der Buchungssätze
31Time sheet protection for structure elements (incl. structure sub elements)
32Assigning cost2 units to structure elements.
33Booking of time sheets to structure elements with given cost units

1 Introduction

This document includes some hints tips and best practices for the usage of ProjectForge®. It will never contain the whole functionality of ProjectForge® because a well-designed web application is easy to use and self-explained by tool tips etc.

2 Common concepts

2.1 Full-indexed search

All list views support a search field. Refer the tool tip to see which fields of the data objects are part of the index. (s. fig 1 ).
Figure 1: Enter a free text in the search field to start a full text query. Part of words are as well supported as logical expressions.
A detailed description of the possibilities may be find on Lucene page

lucene.apache.org/java/2_4_0/queryparsersyntax.html .


The following table shows some common used queries.
Expression Function
abc The search result contains all objects with fields contain words beginning with 'abc'. The search isn't case-sensitive.
*abc* The search result contains all objects with fields contain words containing 'abc'. The search isn't case-sensitive.
meier~ Phonetic search: Fields containing e. g. 'mayer' will also be found as 'meier'!
abc def Searches for fields which contain words beginning with 'abc' and/or 'def'.
+abc -def Search for fields containing words beginning with 'abc' but not with 'def'.
name:abc def Search for all objects where the field 'name' contains words beginning with 'abc' and searches for 'def' in all object fields.

2.1.1 Automatical wildcards

Hint

If the search field contains only sub-string with alphanumerical characters (including @._* and spaces) ProjectForge® appends automatically the wildcard character '*'. For all other expressions the entered term will be leaved untouched. Logical expressions of Lucene (the keywords AND, OR and NOT) will also be leaved untouched).

Expression Modification by ProjectForge
hello hello*
hello ProjectForge hello* ProjectForge*
k.reinhard@projectforge k.reinhard@projectforge*
hello AND 2008-11-21 NOT hurz OR test hello* AND 2008-11-21 NOT hurz* OR test*
(Expressions containing special characters such as -+, will be leaved untouched.)
hello AND name:hurzel hello AND name:hurzel
(no substituion because of ':'.)
+hello and hurzel +hello and hurzel
(no substituion because of '+'.)
If in doubt, ProjectForge® displays any substitution of the user's input below the search field.

2.1.2 Search for structure elements

ProjectForge® searches also for names of ancestor structure elements. If you search for time-sheets for 'Yellow logistics' all time-sheets assigned to the structure element 'Yellow logistics' or descendant structure elements will be found.

2.2 Quick-select

If you choose any list view for selection (e. g. structure element) and only one entry is found by the search this entry will be selected automatically and ProjectForge® returns to the caller page.

2.3 Filter for search in history of changes

Almost all list pages supports an extended filter for searching for objects regarding the history of changes, e. g. the user wants to find any object which was changed during the last 10 minutes etc. Click on the plus sign at the right bottom of the search field's label to see the opportunities.
All other filter criterias will also be used. Fig. 2 shows the extended search of the time sheet list view.
Figure 2: Through the extended search filter you may search for objects which were changed during a given time period.

2.4 Auto completion

For more and more fields an auto-completion is supported. After entering 2 characters a list of entries suggested by the server will be displayed for a convenient selection. You may enter the cursor up- and down-keys to select entries without using your mouse.
A double-mouse-click, if supported by the input field, will show recent used entries or a list of all available objects.

2.5 Favorites / bookmarks

For sending pages of ProjectForge® as link via e-mail or for bookmarking them in your browser, you should use the menu entry 'Show as bookmark'. ProjectForge® offers a link including all settings of the page (e. g. the filter settings of the list view).
Please do not use the displayed url in your browser because this URL isn't valid outside your current session.

2.5.1 Favorites / bookmarks in list views

As fig. 3 shows ProjectForge® offers two links in list views:
  1. Direct link for list view without preset of the filter parameter (the default view is used with the recent parameters).
  2. Direct link for list view with preset of the filter parameters.
Figure 3: This figure demonstrates the bookmarking functionality of the list views. The first link contains only a bookmark for the list view without the preset of any parameters (the default view is used). The second link contains a bookmark with the preset of all filter parameters.

2.5.2 Favorites / bookmarks of time-sheets

As figure 4 shows, ProjectForge® offers also two links in time-sheet edit pages:
  1. Direct link for displaying an object by id.
  2. Direct link including parameters for calling the edit page with pre-filled parameters. This is usefull for adding new time-sheets with pre-filled parameters.
The second link may be used for creation of new time-sheets with templates (e. g. in JIRA or e-mails).
Figure 4:

2.6 Stay logged-in

On the login-screen the user may choose the 'stay-logged-in' option.

3 Calendar

The calendar is one of the most important functionalities of ProjectForge®. You may organize your time-sheets and personal events as well as team events in a convenient way. ProjectForge® is designed to improve the efficiency of projects and project teams from single-person-projects up to large-sized projects.

3.1 Quick-time-period-selection

You may browse very fast through month and/or weeks with the following element of ProjectForge®: Fig. 5 .
Figure 5: Quick-time-period selection with browse mode for months and weeks

3.2 Holidays

ProjectForge® support holidays and you may configure own holidays in config.xml.

3.3 Support of different time zones

ProjectForge® supports different time-zones at once. The user may change his time-zone if he travels through different time-zones.

3.4 View modes

3.4.1 Calendar views: month, week, day

Figure 6: Month display mode of team calendar.
Figure 7: Week display mode of team calendar. The red line is the current time-line.

3.4.2 Customizable views with fast switch

You're able to customize different filters of your calendars to show and use. Switch the view with only two clicks. You may give your different calendars different colors.
Figure 8: Customize your view filters with own colors etc. You may switch the view with only two clicks.

3.5 Drag&drop and range-select functionality

You may create, move, resize and copy events by drag&drop and range-select functionality. The the videos or demo system for experience this convenient feature.

3.6 Roadmap

Further planned developments are described here.

4 Address book

4.1 Reverse search of phone numbers

ProjectForge® adds every phone number without any special characters and white spaces to the search index. You may enter any sub string of the phone number to find the address connected to this phone number (if exist).

Hint

Sometimes you may omit the country prefix of the phone number to find the address.

4.2 Personal favorites

You may import addresses of the list view in your personal address book (e. g. Apple contacts) and synchronize the addresses with your mobile device. The export format is VCard (vcf) for addresses and a csv format for phone numbers (e. g. for your telephony system).
Your favorite addresses will be highlighted in the list view.

4.2.1 Export of vcards

You may choose the check-box 'Favorite' beside the form of address in the edit view of an address for marking the whole address as favorite (see fig. 13 ). Click the button 'Export vCards' for downloading all marked addresses. You may choose single phone number for exporting them in csv format by clicking the check-box '*' right after the desired phone number.

Hint

Please use UTF-8 as encoding in your address book software!
Figure 13: This figure demonstrates the possibility to mark addresses or phone numbers as favorites.

4.2.1.1 Hint for Apple contacts

Multiple exports of addresses may result in multiple note entries. Please use the following Apple script for removing such multiple note entries:
1
2
3
4
5
6
7
8
9
tell application "Address Book"
repeat with thisPerson in every person
if (exists (note of thisPerson)) and ((note of thisPerson) contains "CLASS: WORK") then
log "Name: " & (name of thisPerson)
delete note of thisPerson
end if
end repeat
save
end tell
  1. You may excecute this script directly in your Apple-Script editor. (The result 'missing value' is expected.)
  2. Afterwards you should import the ProjectForge® download (vCard).

4.2.2 Export of personal list of telephone numbers

You may export telephone numbers of addresses (marked in the address edit page by clicking the check-box '*', refer fig. 13 ). You may import phone numbers to your mobile device or system telephone (e. g. SNOM telephone) if vCard isn't supported.
Via the button 'Export phone list' all' all marked phone numbers will be exported with ISO-8859 encoding as csv file. ProjectForge® extends the names of the contacs automaticall with "mobile" or "private", if the number is not the business phone number.

4.2.3 Personal list of phone numbers for your Snom telephone

The exported list may be uploaded directly via the web site of the desired Snom telephone. The following figures 14 and 15 demonstrate the procedure of such an import.

Hint

Any previous imported phone number will be overwritten (no multiple entries are expected).
Figure 14:
Figure 15: It's important to choose 'Name' for the first column and 'Number' for the second column.

4.3 Export of the list of addresses

You may export the current displayed list of addresses for usage with LibreOffice or MS Excel. For reasons of data-protection only the user's favorite addresses will be exported. Members of the groups PF_Finance or PF_Marketing are able to download all address for using them for marketing events etc.

Hint

The first address of each row is the mailing address. It's the postal address if exist, otherwise the business address.

4.4 Animation of phone number / zoom

fig. 16 shows the Mouse-over-functionality, if the mouse cursor is positioned over a phone number. You may dial the number with your phone now.
Figure 16: Animated zoom of phone numbers

4.5 Direct call via telephony system

The user may configure one or more telephone numbers under 'My account'. Afterwards he is able to initiate phone calls directly from ProjectForge® if configured by the administrators and supported by the telephony system.
17 demonstrates the possibility to initiate a phone call directly from ProjectForge®.
Figure 17:
Fig. 18 shows the auto-completion functionality to initiate phone calls very easy.

Hint

Please use the 'return' key for initiating the phone call. Like on most pages the 'return' key result in the default action.
Figure 18:

4.5.1 Configuration

Please add your personal phone number (or a list of phone number) under 'My account'.

5 Structure elements in ProjectForge (former tasks)

The term 'structure element' is used in a generic way and represents customers, projects, releases, structure elements etc. They're organized hierarchical. Access rights may depend on structure elements (and are derived from ancestor structure elements if configured). Fig. 19 shows a typical tree view of structure elements in ProjectForge®.
Figure 19: Hierarchy of structure elements in ProjectForge®

The following table describes the fields of structure elements:
Field Description
Parent structure element The structure element is a descendant structure element of the parent structure element. The structure elements at the top of the hierarchy is the root node which is the onliest structure element without a parent structure element.
Name
Referenz Multi purpose field, optional. This field is derived for all descendant structure elements if not overwritten in such structure elements. This is useful for exports etc.
Status
Priority
Short description
Description
Progress optional without further functionality.
Maximum hours If given, the consumption will be displayed in the structure tree as well as during booking a time sheet, see 5.1 .
Responsible ProjectForge®-User for information
Cost2 All cost2 values are project specific if a project was assigned to this structure element or any ancestor structure element. The black list defines cost 2 entries which should be excluded of the assigned project. If no project is assigned, cost 2 value are configurable by using a white list.
Protect time sheets until If set, only time sheets with start dates after the given date may-be inserted and updated. Members of the financial staff are able to insert, update and delete older time-sheets of other users (not own time sheets).
Protection of privacy If checked the time sheets of this structure element and any descendent structure elements are invisible for other users (except the members of the financial staff).

5.1 Consumption

Consumptions are displayed as part of the structure tree as well as during every booking process of time sheets. The consumption of already used hours of a structure element including all structure sub elements is displayed as a tool tip.

5.1.1 Consumption bars for open structure elements

Color Meaning Tool tip
No budget given Total consumption in man-days.
Consumption 0-80% Total consumption in man-days and percent, green bar.
Consumption 80%-90% Total consumption in man-days and percent.
Consumption 90%-100% Total consumption in man-days and percent.
Consumption over 100% Total consumption in man-days and percent.

5.1.2 Consumption bars for finished structure elements

Color Meaning Tool tip
No budget given. Total consumption in man-days.
Consumption 0-100% Total consumption in man-days and percent.
Consumption 100%-110% Total consumption in man-days and percent.
Consumption über 110% Total consumption in man-days and percent.

5.1.3 Suppressing the status of consumption bars

Enter 0 as maximum hours of a structure element for suppressing the status (color and percentage) of a consumption bar. This is use-ful for not demotivating a team or if not all orders with budgets are assigned to a structure element.

5.2 Booking of time sheets

Time sheets must be assigned to a structure element. For improving the quality of time sheets different rules may be definied in ProjectForge®.
Following rules are implemented, if the user doesn't hit the rules he won't be able to book his working time on such structure elements.
  1. The user needs the access to this structure element or any ancestor structure element including the right of booking time sheets.
  2. The structure element or any ancestor structure element may not have a time-protection setting. The users will not be able to modify or add time sheets for a day before time-protection (date field).
  3. The structure element or any ancestor structure element is closed or deleted.
  4. The booking status of a structure element or any ancestor structure element is 'completely closed'.
  5. If the structure element is not a structure leaf element (has descendents) and the booking status is configured as 'only leaf nodes'.
  6. If any descendent structure element has an assigned order. This rule is important because otherwise ProjectForge® won't be able to calculate the consumption of budgets defined by orders.
If a structure element is closed for booking of time sheets the user may modify fields not affecting financial parameters (such as description but not times, duration, cost settings etc.) The user may therefore correct typos etc. in time sheets.

6 Order book

Offers and orders may be managed by ProjectForge®. The may-be assigned to project managers having access to their orders and offers. Administrative staff members may have access to all orders if they have the right to do (see user administration of ProjectForge®).

6.1 E-mail notification

All modifications of orders, if not done by the project manager itself will be sent by e-mail including a quick link to the order, see fig. 20 , a notication is suppressed if the check-box is disabled..
Figure 20: Order book with e-mail notification

7 Scripting in ProjectForge®

You may edit, store and execute scripts in ProjectForge for generating Excel files out of the database of ProjectForge® as well as charts etc. It's also possible to upload Excel master file to scripts for modifiing existing Excel files.
A script editor is built-in in ProjectForge®.

7.1 Examples

7.1.1 Example: Hibernate criterion API

Example

1
2
3
4
5
6
7
8
import org.hibernate.criterion.*
import org.projectforge.core.*

QueryFilter filter = new QueryFilter()         // Define a query
filter.addOrder(Order.asc("username"))         // Set order attribute
List userList = userDao.getList(filter)        // Get all users

return "Number of found users: " + userList.size
You may use the Hibernate criterion API. If the script returns a string the string will be displayed.

7.1.2 Example: Usage of ProjectForge®filters

Example

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
import org.projectforge.core.*
import org.projectforge.timesheet.*
import org.projectforge.common.*

DateHolder date = new DateHolder() // Using the user's current time zone

TimesheetFilter tf = new TimesheetFilter()
date.setDate(2009, Calendar.JANUARY, 01, 00, 00, 00)
tf.setStartTime(date.date)
date.setDate(2009, Calendar.JANUARY, 31, 23, 59, 59)
tf.setStopTime(date.date)
tf.setUserId(17)
List timesheetList = timesheetDao.getList(tf)

return "Number of found time sheets: " + timesheetList.size

7.2 Usage of date values

ProjectForge® uses everytime the time zone of the logged-in user. Internally all dates and times are stored as UTC! Therefore all queries needs to be executed with the correct time zone. The class DateHolder and DayHolder does everything for you (the correct time zone of the logged-in user is used).

Example

1
2
3
4
5
6
7
8
9
10
11
import org.projectforge.common.*

DateHolder date = new DateHolder() // user's current time zone / locale

date.setDate(2009, Calendar.JANUARY, 01) // CET: 01.01.2009 00:00
String result = "UTC: " + date.date // UTC: Wed Dec 31 23:00:00 UTC 2008

date.setDate(2009, Calendar.APRIL, 01, 17, 03) // CEST: 01.04.2009 17:03
result += ", UTC: " + date.date // UTC: Wed Apr 01 15:03:00 UTC 2009

return result

7.3 Access rights (DAOs)

You may access all database entries by using the DAOs (DataAccessObjects) of ProjectForge®. The DAOs ensures that only such entities are returned for those the user has access to. All entities without the user's access are removed by the DAOs of the result lists.

7.4 Creation of Excel sheets

If a script returns an ExportWorkbook, a Excel file is created and will result in a download. Check this out:

7.4.1 Example: Excel export of simple lists

Example

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
import org.hibernate.criterion.*
import org.projectforge.core.*
import org.projectforge.excel.*

QueryFilter filter = new QueryFilter()         // Defines a query
filter.addOrder(Order.asc("username"))         // Sets sort order
List userList = userDao.getList(filter)        // Gets the user's list

ExportWorkbook workbook = new ExportWorkbook();// Creates a new Excel work book
ExportSheet sheet = workbook.addSheet("Users"// Creates a new Excel sheet
sheet.contentProvider.colWidths = [10, 20]     // Sets the width of the first two columns.
sheet.propertyNames = ["username""lastname"// Defines the properties to export.
sheet.addRow().setCapitalizedValues(sheet.propertyNames)  // Creates a heading row.
sheet.addRows(userList)                        // Add all user's, one per row.

return workbook

7.4.2 Example: Excel export of joined objects

Example

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
import org.projectforge.core.*
import org.projectforge.excel.*
import org.projectforge.timesheet.*
import java.text.SimpleDateFormat

TimesheetFilter tf = new TimesheetFilter()
SimpleDateFormat df = new SimpleDateFormat("dd.MM.yyyy HH:mm")
tf.setStartTime(df.parse("01.01.2009 00:00"))
tf.setStopTime(df.parse("31.01.2009 23:59"))
tf.setUserId(17)
List timesheetList = timesheetDao.getList(tf)

ExportWorkbook workbook = new ExportWorkbook();
ExportSheet sheet = workbook.addSheet("Timesheets")
sheet.contentProvider.colWidths = [10, 20, 15, 10]
sheet.contentProvider.putFormat(java.sql.Timestamp.class,"DD.MM.YYYY hh:mm"// Defines the format for all time stamps
sheet.contentProvider.putFormat("stopTime","hh:mm")             // Defines the format for the property 'stopTime'
sheet.addRow().setValues("Username""Beschreibung""StartDate""StopDate""fakturiert")
sheet.propertyNames = ["user.username""description""startTime""stopTime""kost2.kost2Art.fakturiert"]
sheet.addRows(timesheetList)

return workbook

7.4.3 Own join objects

You may join database entities very simple:

Example

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
import org.fibu.*
import org.fibu.kost.*
import org.projectforge.excel.*
import org.projectforge.core.*

class JoinedObject {
  BuchungssatzDO satz
  EmployeeDO employee
}

// Load list of account records (BuchungssatzDO):
BuchungssatzFilter filter = new BuchungssatzFilter();
filter.setFrom(2008, 0) // Januar
filter.setTo(2008, 11)  // Dezember

def buchungssaetze = buchungssatzDao.getList(filter)
buchungssaetze = buchungssaetze.findAll{ satz -> (5000..5999).contains(satz.konto.nummer) }

// Load list of employees
def employees = employeeDao.getList().findAll{it.kost1 != null}

def result = new ArrayList()
buchungssaetze.each{bs ->
  el = new JoinedObject()
  el.satz = bs
  el.employee = employees.find{employee -> employee.kost1Id == bs.kost1.id}
  result.add(el)
}

// Generate Excel work book:
ExportWorkbook workbook = new ExportWorkbook();
ExportSheet sheet = workbook.addSheet("Result")
sheet.contentProvider.colWidths = [10, 10, 8, 8, 10, 10, 20, 20]
sheet.addRow().setValues("Datum""Betrag""Konto""Gegen-konto""Kost1""Kost2""Name")
sheet.contentProvider.putFormat("satz.datum""DD.MM.YYYY")
sheet.contentProvider.putFormat("satz.betrag""#,##0.00$;[Red]-#,##0.00$"// English format
sheet.contentProvider.putFormat("satz.konto.nummer""0")
sheet.contentProvider.putFormat("satz.gegenKonto.nummer""0")
sheet.propertyNames = ["satz.datum""satz.betrag""satz.konto.nummer""satz.gegenKonto.nummer",
  "satz.kost1.formattedNumber""satz.kost2.formattedNumber""employee.user.fullname"]
sheet.addRows(result)

return workbook

7.4.4 Formats

Example

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
import org.projectforge.calendar.*
import org.projectforge.common.*
import org.projectforge.core.*
import org.projectforge.excel.*

ExportWorkbook workbook = new ExportWorkbook();// Creates new work book
ExportSheet sheet = workbook.addSheet("Test"// Creates new sheet
sheet.contentProvider.colWidths = [20, 20, 20]
sheet.addRow().setValues("Typ""Precision""Value")
sheet.addRow().setValues("DayHolder""Day"new DayHolder())
sheet.addRow().setValues("DateHolder""Day"new DateHolder().setPrecision(DatePrecision.DAY))
sheet.addRow().setValues("DateHolder""Minutes"new DateHolder().setPrecision(DatePrecision.MINUTE))
sheet.addRow().setValues("DateHolder""Seconds"new DateHolder().setPrecision(DatePrecision.SECOND))
sheet.addRow().setValues("DateHolder""Millis"new DateHolder().setPrecision(DatePrecision.MILLISECOND))

return workbook

Hint

ProjectForge® uses the precision of DateHolder objects for the output as default, e. g. for precision SECOND the date with time stamp including seconds is used, for precision DAY only the date without any time stamp is used. Supported precisions are: DAY, HOUR_OF_DAY, MINUTE_15, MINUTE, SECOND, MILLISECOND.

7.4.5 Modification of existing Excel files

You may upload Excel files (stored in scripts) which you may modifiy inside your script.

Example

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
import org.hibernate.criterion.*
import org.projectforge.core.*
import org.projectforge.excel.*

QueryFilter filter = new QueryFilter()         // Defines a query
filter.addOrder(Order.asc("username"))         // Sets sort order
List userList = userDao.getList(filter)        // Gets the user list

ExportWorkbook workbook = new ExportWorkbook(script.file)
ExportSheet sheet = workbook.addSheet("Users"// Creates a new sheet
sheet.contentProvider.colWidths = [10, 20]     // Sets the width of the first two columns.
sheet.propertyNames = ["username""lastname"// Defines the properties to export.
sheet.addRow().setCapitalizedValues(sheet.propertyNames)  // Creates a heading row.
sheet.addRows(userList)                        // Add all user's, one per row.

return workbook

7.4.6 Cell formats

You should use the English notation:

Example

DD.MM.YYYY hh:mm Date with time of day
DD.MM.YYYY hh:mm:ss Date with time of day including seconds
0 Normal output of number without thousands delimiter.
#,##0.00$ Currency (Dollar)
#,##0.00$;[Red]-#,##0.00$ Currency with red colored negative values.

7.4.7 Excel and time periods

Excel uses internally a fraction of 24 hours. For converting for example milliseconds to hours in Excel you may use the following code:
1
2
3
BigDecimal duration = new BigDecimal(durationInMillis / 1000); // Seconds
duration = duration.divide(new BigDecimal(60 * 60 * 24), 8, RoundingMode.HALF_UP); // Fraction of day (24 hours)
double excelDuration = duration.doubleValue();
You may use "[h]:mm" as cell format.

7.5 Creation of diagrams and charts

You may create charts using (www.jfree.org).
Figure 21: Example of a script producing a chart

Example

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
import org.jfree.chart.*
import org.jfree.chart.plot.*
import org.jfree.data.general.*
import org.jfree.util.*
import org.projectforge.excel.*

DefaultPieDataset dataset = new DefaultPieDataset()
dataset.setValue("Linux", 15)
dataset.setValue("Mac", 8)
dataset.setValue("Windows", 70)
dataset.setValue("Others", 7)
JFreeChart chart = ChartFactory.createPieChart3D("Users on www.heise.de", dataset, truetruefalse)
PiePlot3D plot = (PiePlot3D) chart.getPlot()

ExportJFreeChart export = new ExportJFreeChart(chart, 800, 600)
return export

7.6 Creation of zip archives containing multiple files

You may create zip files containing multiple files (Excel sheets and diagrams).

Example

1
2
3
4
5
6
7
import ...
ExportZipArchive zip = new ExportZipArchive("my-first-archive"// File name will be: my-first-archive.zip
ExportWorkbook workbook = new ExportWorkbook(...)
zip.add("sheet.xls", workbook)
ExportJFreeChart chart = new ExportJFreeChart(...)
zip.add("chart.jpg", chart)
return zip

7.7 Nested and indexed properties

You may use bean properties using the common used notation:
1
2
sheet.propertyNames = ["user.username""description""startTime""stopTime",
         "kost2.kost2Art.fakturiert"]
as well as indexed properties (such as the property username of the user).
Nested and indexed properties are supported as well: users[3].name or result.userList[0].

7.8 Import of Excel files

Fig. 22 shows an example file.
Figure 22: Example file for an Excel import

Example

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
import java.text.*;
import org.projectforge.excel.*
NumberFormat nf = NumberFormat.getCurrencyInstance(Locale.UK);

xls = reportScriptingStorage.getFile("import-example.xls"// Was uploaded with this name.
ExportWorkbook workbook = new ExportWorkbook(xls);// Use script.file instead of xls in script list.
ExportSheet sheet = workbook.getSheet("Sheet-1"); // Gets sheet with the given title.
rows = sheet.rows; // Get all rows.
result = "";
total = 0.0;

for (int i = 1; i < sheet.rows.size; i++) {
  row = sheet.getRow(i);
  no = row.getCell(0).getNumericCellValue();
  name = row.getCell(1).getStringCellValue();
  amount = row.getCell(2).getNumericCellValue();
  total += amount;
  result += (int)no + ". The amount of " + name + " is: " + nf.format(amount) + "\n";
}
result += "The total amount is: " + nf.format(total);
return result;
The output is:
 1. The amount of Susan is: £50.23
2. The amount of Hugo is: £17.00
3. The amount of Lisa is: £15.00
4. The amount of Mona is: £10.00
The total amount is: £92.23

8 Financial administration with ProjectForge®

8.1 Invoices

ProjectForge® supportes the management of invoices (inbound and outbound, creditors and debitors):

8.1.1 Assignment of cost units

It's recommended to try this feature for a better understanding.
You may assign positions of invoices to cost1 and cost2 units. An autocomplete functionality helps you to distribute the amounts to cost units very fast. You may enter numbers of cost units as well as text (e. g. text fields of a cost unit such as description. customer, type etc.).
An unlimitited number of assignments are supported. After adding new assignments ProjectForge® prefills the amount of the invoice which wasn't assigned yet. The amount may be given as amount or as percent value.
For better tracebility you may not delete positions (after stored in the database). If an entry should be removed, enter '0' as amount.

8.1.2 Clone functionality for invoices (inbound and outbound)

You may clone existing invoices for a faster process of inserting new invoices:

8.2 Liquidity planning / forecast

Based on invoices and additional liquidity entries (others than invoices) ProjectForge® gives you the opportunity to manage your cash flow very easy including a forecast of your cash flow.

8.2.1 Calculation of expected dates of payments

ProjectForge® uses due dates for forecasting the cash flow and account balance. For invoices ProjectForge® calculates the expected dates of payment based on already paid invoices in the last 12 month of the same:
  1. Project: Do paid invoices already exist for the same project?
  2. Customer number: Do paid invoices already exist for the customer (given by the cost number)?
  3. Customer text: Do paid invoices already exist for the customer (full text)?
  4. Customer short text: Do paid invoices already exist for the customer (the beginning of the customer text will be used)?
If any paid invoice was found (starting with project) a mean time of payment of the paid invoices is used for calculating the expected date of payment.
Figure 23: Liquidity with forecast of the cash flow
Please refer the AdministrationGuide for enabling the liquidity plugin of ProjectForge®.

8.3 Datev-Import (German)

Über diese Funktion werden die Konten (Kontenplan) und die Buchungssätze eingelesen. Diese Daten werden vom Steuerbüro in Form eines Excel-Exports geliefert.

8.3.1 Grundsätzliches zum ProjectForge®-Excel-Import

Im folgenden wird anhand des Kontenplans der Import und die notwendigen Prüfschritte erläutert.

8.3.1.1 Schritt 1: Auswahl der zu importierenden Excel-Datei

Abb. 24 zeigt den ersten Schritt: Es wird die zu importierende Excel-Datei über den Browserknopf "Datei auswählen" im lokalen Dateisystem selektiert. Anschließend wird die gewünschte Importfunktion (z. B. "Import Kontenplan") gewählt.
Figure 24: Datev-Import, Schritt 1: Auswahl der Importdatei

8.3.1.2 Schritt 2: Daten werden eingelesen

Abb. 25 zeigt den zweiten Schritt: Die Daten wurden erfolgreich eingelesen und die importierten Tabellenblätter (Sheets) untereinander angezeigt (standardmäßig sind alle Blätter zugeklappt). Im Fehlerfalle wird der Fehler mit Tabellenblatt, Zeilennummer und Spaltennummer angezeigt (z. B. wenn ein nicht passendes Zahlenformat in einer Zelle erkannt wurde.) Fehlerhafte Datensätze können bereits eingesehen werden (durch Aufklappen des Importbereichs).
Figure 25: Datev-Import, Schritt 2: Einlesen der Datei

8.3.1.3 Schritt 3: Verproben

Abb. 26 zeigt den zweiten Schritt: Die Daten wurden erfolgreich eingelesen und die importierten Tabellenblätter (Sheets) untereinander angezeigt (standardmäßig sind alle Blätter zugeklappt). Nun sollte die Funktion "Verproben" für das gewünschte Tabellenblatt / die gewünschten Tabellenblätter gewählt werden.
Beim Verproben gleicht ProjectForge® die importierten Datensätze mit evtl. bereits vorhandenen Datensätzen ab. Nach dem Verproben werden auch neue und modifizierte Datensätze angezeigt.
Figure 26: Datev-Import, Schritt 3: Verproben

8.3.1.4 Schritt 4: Prüfen, selektieren und Übernahme

Abb. 27 zeigt den vierten Schritt: Nach der Verprobung können nun die zu speichernden Datensätze einer Sichtprüfung unterzogen werden. Bei modifizierten Einträgen wird über einen Tool tip der ursprüngliche Wert angezeigt.
Nach der Prüfung können die Datensätze selektiert werden, die in ProjectForge® übernommen werden sollen. Mit der Funktion "Select all" können auch alle angezeigten Datensätze markiert werden. Anschließend kann über "Commit" eine Übernahme bestätigt und angestoßen werden.
Figure 27: Datev-Import, Schritt 4: Prüfen, Selektion und Übernahme

8.3.1.5 Schritt 5: Import abgeschlossen

Abb. 28 zeigt den fünften Schritt: Nach dem Import wird angezeigt, wieviel Datensätze übernommen wurden. Ein abschließendes "Verproben" sollte aufgerufen werden. Wenn alle Änderungen erfolgreich übernommen wurden und alle Datensätze selektiert worden waren, so müsste abschließend für das Tabellenblatt "nichts zu tun" angezeigt werden. Wurde nur ein Teilsatz der Daten selektiert, so werden die übrigen noch zur Auswahl angeboten.
Figure 28: Datev-Import, Schritt 5: Abschluss

8.3.2 Import der Buchungskonten (Kontenplan)

Für den Import der Buchungskonten wird im zu importierenden Exceldokument ein Tabellenreiter mit dem Namen "Kontenplan" erwartet. Dieses Tabellenblatt wird für den Import verwendet. Alle anderen Tabellenblätter werden ignoriert.

8.3.2.1 Format

Folgendes Format wird erwartet (Abb. 29 ).
Figure 29: Excelformat für Datev-Import des Kontenplans
In Zeile 2 wird die Kopfspalte mit den Namen "Konto" und "Bezeichnung" erwartet. Vorhandene Konten werden nicht gelöscht, sondern bei Vorhandensein modifiziert.

8.3.3 Import der Buchungssätze (01-12)

Für den Import der Buchungssätze werden im zu importierenden Exceldokument die Tabellenblätter mit dem Namen "01", "02", ..., "12" berücksichtigt. Diese Blätter stehen für die zu importierenden Buchungsmonate.

8.3.3.1 Format

Folgendes Format wird erwartet (Abb. 30 ).
Figure 30: Excelformat für Datev-Import der Buchungssätze
In Zeile 2 wird die Kopfspalte mit den Namen "Konto" und "Bezeichnung" erwartet. Vorhandene Konten werden nicht gelöscht, sondern bei Vorhandensein modifiziert.

8.4 Other imports

8.4.1 Salaries of employees

The following Excel format is assumed:
  1. A monthly sheet has the title of format 'yyyy-mm', e. g. "2009-05".
  2. The columns are:
    1. Cost1 unit of the employee, e. g. '3.050.00.00'
    2. Employee: Last name, surname
    3. Gross salary including taxes and insurance, e. g. '1,846.77'
    4. bonus, royalty, overtime premium etc. as decimal value
    5. Special payment as decimal value
    6. Car as decimal value
    7. Total will be ignored
    8. Comment as text field
The following script generates the database SQL-Statement for importing the salaries:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
import org.projectforge.core.*
import org.projectforge.excel.*
import java.text.*

year = 2009; month = 4;

if (month < 10) monthString = "0" + month
else monthString = "" + month
NumberFormat nf = NumberFormat.getNumberInstance(Locale.GERMANY);
nf.setMaximumFractionDigits(2);
nf.setMinimumFractionDigits(2);

xls = reportScriptingStorage.getFile("2009-Gehaltslisten.xls"// Was uploaded with this name.
ExportWorkbook workbook = new ExportWorkbook(xls); // Uses the uploaded Excel file.
ExportSheet sheet = workbook.getSheet(year + "-" + monthString); // Gets the sheet.
rows = sheet.rows; // get all rows
String result = "";

for (int i = 4; i < sheet.rows.size; i++) {
  row = sheet.getRow(i);
  cost1String = row.getCell(0).getStringCellValue();
  employeeName = row.getCell(1).getStringCellValue();
  gross = row.getCell(2).getNumericCellValue();
  bonus = row.getCell(3).getNumericCellValue();
  special = row.getCell(4).getNumericCellValue();
  car = row.getCell(5).getNumericCellValue();
  comment = row.getCell(7).getStringCellValue();
  total = new BigDecimal(gross + bonus + special + car).setScale(2, java.math.RoundingMode.HALF_UP);
  if (employeeName != null)
    employee = employeeDao.getByName(employeeName);
  if (kost1String == null)
    break;
  kost1 = kost1Dao.getKost1(kost1String);
  if (kost1 == null) {
    result += "\n-- **********" + kost1String;
  } else if (kost1.getDescription().equals(employeeName) == false) {
    result += "\n-- **********" + kost1.getDescription() + " != " + employeeName;
  } else if (employee == null) {
    result += "\n-- ********** Employee '" + employeeName + "' not found.";
  } else {
    text = "Excel-Import Kai";
    if (tantieme_zv > 0) text += "; bonus/royalty/overtime: " + nf.format(bonus);
    if (special > 0) text += "; Special: " + nf.format(special);
    if (car > 0) text += "; car: " + nf.format(car);
    if (comment != null && comment.trim().size() > 0) text += "; " + comment;
    result += "\ninsert into t_fibu_employee_salary (pk,created,last_update,deleted,gross,month,type,year,comment,employee_id) values (nextval('hibernate_sequence'),now(), now(),false," + total + ", " + (month - 1)+ ", 'SALARY', " + year + ",'" + text + "'," + employee.id + ");";
  }
}
return result;

8.5 Time sheets

8.5.1 Protection

You may protect time sheets by date for structure elements (including all descendent structure elements). This protection ensures that no employee is able to modifiy time sheets with dates before the date where the protection ends.
This is useful if the time sheets are invoiced or taken over by other accounting systems. Fig. 31 shows a structure element with a protection of time sheets until 30/06/2013.
Time sheets with dates before the date of protection may only be changed by members of the financial administration staff (regarding times and cost assignements).
Figure 31: Time sheet protection for structure elements (incl. structure sub elements)

8.5.2 Cost units

If there is any cost2 unit associated with a structure element (directly or derived), an user has to select a suitable cost unit while booking time sheets.
Fig. 32 shows a structure element with the assigned project '5.200.00 - ProjectForge®' and the cost unit '5.200.00.*'.
Figure 32: Assigning cost2 units to structure elements.

Hint

If you are missing cost units you may add them via project edit page (with status 'ACTIVE').
If you want to disable cost units you may change the status of such units to 'INACTIVE' or 'ENDED' or you may remove them by using the black- or white list (see fig. ref label="fig:aufgabeKost2" type="full" />).

8.5.3 Booking of time sheets

For time sheets assigned to structure elements with assigned cost units a selection of a cost unit is required as shown in fig. 33 .
Figure 33: Booking of time sheets to structure elements with given cost units

8.5.4 Monthly employee reports

A monthly employee report for users with all time sheets ordered by cost units, structure element, user and weeks are also available as PDF for download. These reports may me used for e. g. signing monthly reports of employees by their managers.

8.5.5 JIRA support

JIRA issues are supported if configured. If JIRA issues are contained in e. g. time sheets or description of structure elements then the will be displayed with links to the configured JIRA system.

8.6 Cost unit accounting

8.6.1 Fraction of working time

You may configure a fraction for single cost units or cost unit types (default is 1). Example: if the travelling time should be counted only as half regarding the employees working time, you may configure the fraction as 0,5.

8.7 Reporting via Report objectives (in progress)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<ReportObjective title="Customer ACME" id="ACME" suppressOther="true" suppressDuplicates="true">
  <kost1-include>3.*</kost1-include>
  <kost1-exclude>*.01</kost1-exclude>
  <kost1-exclude>*.12</kost1-exclude>
  <kost2-include>5.*</kost2-include>
  <kost2-exclude>*.02</kost2-exclude>
  <kost2-exclude>*.11</kost2-exclude>
  <ReportObjective title="Project ACME-WEB-Portal" id="ACME-WEB-Portal">
    <kost2-include>5.020.01.*</kost2-include>
  </ReportObjective>
  <ReportObjective title="Project ACME-Java-Migration" id="ACME-Java-Migration">
    <kost2-include>5.020.02.*</kost2-include>
  </ReportObjective>
</ReportObjective>
  1. If an include list is empty, all records will match for this include list (wildcard).
  2. If an include list isn't empty, at least one entry must match.
  3. For multiple include lists a record must match all include lists (logical 'and').
  4. If a record matches at least one entry of any exclude list, this record will be excluded.
The method String.matches(regExp) is used for evaluating the regular expressions. You may refer the expression syntax of the Java class java.util.regex.Pattern for a better understanding. For your convenience ProjectForge® modifies automatically the configured expressions before usage: All points ('.') will be escaped and the wildcard character '*' is replaced by '.*'. You may suppress the automatical modification by prepending an apostrophe, e. g. "'^5\.510.*" will not be modified (only the apostrophe itself is removed before evaluation).
Examples: (This strategy of replacement is for convenience purposes because '.' means normally any character and '*' means a multiple occurence of the character before the asterisk: for example "3.1*" will match "3x1111" but not "3.120.08.02".)

Hint

A child ReportObjective may only consist of entries of the parent ReportObjective (only a subset)!
You may access the ReportObjectives directly.

Example

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
import org.fibu.*
import org.fibu.kost.*
import org.projectforge.excel.*
import org.projectforge.core.*

class JoinedObject {
  BuchungssatzDO satz
  EmployeeDO employee
}

// Loading the list with all accounting records:
def records = reportStorage.getCurrentReport().getBuchungssaetze()
records = records.findAll{ satz -> (5000..5999).contains(satz.konto.nummer) }

// Loading the list of employees:
def employees = employeeDao.getList().findAll{it.kost1 != null}

def result = new ArrayList()
records.each{bs ->
  el = new JoinedObject()
  el.satz = bs
  el.employee = employees.find{employee -> employee.kost1Id == bs.kost1.id}
  result.add(el)
}

// Generation of an Excel workbook:
ExportWorkbook workbook = new ExportWorkbook();
ExportSheet sheet = workbook.addSheet("Ergebnis")
sheet.contentProvider.colWidths = [10, 10, 8, 8, 10, 10, 20, 20]
sheet.addRow().setValues("Date""Amount""Account""Contra account""Cost 1""Cost 2""Name")
sheet.contentProvider.putFormat("satz.datum""DD.MM.YYYY")
sheet.contentProvider.putFormat("satz.betrag""#,##0.00$;[Red]-#,##0.00$"// English format
sheet.contentProvider.putFormat("satz.konto.nummer""0")
sheet.contentProvider.putFormat("satz.gegenKonto.nummer""0")
sheet.propertyNames = ["satz.datum""satz.betrag""satz.konto.nummer""satz.gegenKonto.nummer",
  "satz.kost1.formattedNumber""satz.kost2.formattedNumber""employee.user.fullname"]
sheet.addRows(result)

return workbook

9 Data protection

9.1 Passwords

Passwords will be stored SHA-encrypted in the database. Directly after usage of passwords (such as in the login screen) all password variables will be deleted. Passwords are not part of any log file entry.

9.2 History of changes

All modifications of entities (create, update, delete) are stored with time stamp, user who cause the modification and the modified fields with the old and new value. The history of changes is displayed at the edit page of an entity. There are single special fields which are not part of this history mechanism.

9.3 Logging

Access violations and actions of users (such as modification of entities) as well as any error message are stored in log files. The log file is important for analyzing error etc.

9.4 Phone call - numbers

Every phone call will be logged. A log file entry contains:
  1. User causes the phone call
  2. Source phone number
  3. Destination phone number without the last 3 digits, eg 012345xxx.

A Glossary

The developers of ProjectForge® were using the German language until several years ago for some financial terms. Nowadays English is the main language of the development (for Java classes, documentation etc.). Here you may find translation and description of common used terms.
German English Description
Aufgabe task Since mid of 2013 the term task was replaced by 'structure elements' because such elements may represent not only tasks, they represent customers, projects, releases, tasks etc.
Auftrag order
Beleg receipt
Betrag ammount
Brutto gross (amount)
Buchungssatz accounting records Accounting records are entities which are imported by external financial systems (e. g. DATEV in Germany). The recommended workflow is to organize invoices, salaries of the employees etc. with ProjectForge® and then to import the data in the external financial system. The re-imported accounting records should match the data in ProjectForge.
ProjectForge® and the scripts also work if you don't have accounting records, you may use the origin entities such as invoices etc. directly in your scripts.
The plural of accounting record is: Buchungssaetze
BWA business assessment Term used inside the financial module of ProjectForge®.
DATEV Software common used in Germany for financial administration. ProjectForge® offers import and export interfaces.
Datum date
Eingangsrechnung invoice Used for incoming invoices (creditors).
fakturiert invoiced
Fibu financial administration Fibu is the abbreviation of the German term 'Finanzbuchhaltung'.
Gegenkonto contra account Term used inside the financial module of ProjectForge®.
Kontenplan account structure Term used inside the financial module of ProjectForge®.
Konto account Term used inside the financial module of ProjectForge®.
Kost cost Term used inside the financial module of ProjectForge®. Kost1/Kost2 -> cost1/cost2 is the short form of 'cost unit'.
Kostenträger cost unit Term used inside the financial module of ProjectForge®.
Kostenträgerart type of cost unit Term used inside the financial module of ProjectForge®. Examples for types of cost units are: 'Travelling', 'Acquisition', 'Meetings' etc. Types are customizable and used for specifiing different types of costs in projects and/or company wide.
Kostenzuweisung cost assignment Term used inside the financial module of ProjectForge®. Costs, such as employee salaries, position of invoices (incoming and outgoing) etc. are assigned to cost units.
Kunde customer
Menge amount
Netto net (amount)
Projekt project
Rechnung invoice Used for outgoing invoices (debitors).