Introduction

Hi,

This is chapter 0 in a 24 chapter course.  If you decide the course is for you and that it offers you what you want, please read this page very attentively. It provides instructions that will help you get the most out of what I offer.

Notice :  Please see this important update before trying to follow any of the steps described here.


Is this for you?

I'm directing this course at people who:

• know OpenERP but don't know accounting
• know accounting but don't know OpenERP
• know both, but don't know the nitty-gritty of OpenERP programming well enough for the task of configuring OpenERP.

What will you get out of it?

• You will follow the steps necessary to prepare OpenERP's Accounting and Finance module for a company to use.
• You will get a "browser automation" test rig, which performs all the data entry steps for you automatically.  You will need administrator authority over the OpenERP system.
• You will get some easy-to-use scripts for backup and restore.

I found the OpenERP guide book expects one to have too much prior accounting knowledge and provides too trivial an example.

I looked at a bunch of accounting books wondering with which to work .  My main concerns were about the science that goes into preparing an appropriate chart of accounts and to have a complete coherent worked-through example.  When I hit upon Alpha Teach Yourself Accounting in 24 Hours (ISBN-13: 978-0028641584) and saw the first sentence of the first paragraph saying "A Chart of Accounts is the blueprint used to build an accounting system..." and saw the examples, I figured I'd found the no nonsense guide I needed.

It isn't perfect.  For really great supplementary reading check this out...
http://www.dwmbeancounter.com/tutorial/Tutorial.html

In many ways that little gem is superior to Alpha Teach Yourself Accounting in 24 Hours!   I didn't use it to structure this blog because the example company seemed a little too trivial.  It explains brilliantly why things are done a certain way. 

I call this blog "Teach Yourself OpenERP Accounting in 24 Hours" without having the slightest idea whether anyone really will be able to do it in that time.  Their book, that I abbreviate to TYAi24H from now on, has 24 chapters, so I'm going to try to cover OpenERP's accounting and finance in 24 lessons.  I'll be cheating a bit though -- I'll leave all the explaining to the three source texts:

• the Alpha book TYAi24H mentioned above
• the Bean Counter tutorial mentioned above 
• and the OpenERP book you can obtain here:

http://doc.openerp.com/v6.0/book/

My value-added is to map correctly the generic paper-based concepts of TYAi24H to the concrete and specific case of OpenERP. 


Preliminaries:
My Lesson #1 assumes that you have already installed OpenERP and worked through the setup section from here...

http://doc.openerp.com/v6.0/book/1/1_3_Real_Case/1_3_Real_Case_use_case.html

...up to, but not including...
http://doc.openerp.com/v6.0/book/1/1_3_Real_Case/1_3_Real_Case_db_setup.html#configuring-accounts 

The easiest setup of OpenERP is the Windows All In one installer:
http://www.openerp.com/download/stable/win32/openerp-allinone-setup-6.0.3.exe


Extra goodies!
There are some tedious necessary tasks to be performed, so I have supplied you with automation scripts.  I created these for my own purposes, of course.  During the writing of this blog, I know I'm going to be running into one cul-de-sac after another, so scripting it all will save me a lot of misery.  The up-front effort is significant, but I have my fingers crossed that it'll save me time in the long run.  If you're willing to set aside an hour or so to get ready to use them you'll see what huge time-savers they are.

They will be useless unless you:

1. use FireFox and 
2. install the Selenium IDE plugin.

Preparing for OpenERP scripting:
If you don't have FireFox you'll have to work that out on your own, starting here...

http://www.mozilla.org/

As of November 2011, FireFox 8.  I've used it successfully under Ubuntu Linux 10.10 and under Windows 7.


Once you've installed FireFox, skim the Selenium IDE overview documentation, and then add it to FireFox as an AddOn:

Here's what the Selenium tools set is all about...

http://seleniumhq.org/

Here's an overview of Selenium IDE...

http://seleniumhq.org/projects/ide/

You'll find the link to install the FireFox add-on for Selenium IDE here...

http://xserve.openqa.org:8085/browse/IDE-EDITOR

... then:

1. click on the latest (green) build
2. click on the link "Selenium IDE Editor" in the Shared Artifacts section
3. save the selenium-ide.xpi file to disk
4. (in Firefox make sure you have saved any emails or whatever)
5. drag the saved file onto FireFox
6. accept for Firefox to install the Selenium IDE "xpi" as a plugin and then restart itself.

Excellent getting started steps are provided here...

http://seleniumhq.org/docs/02_selenium_ide.html

Scripting OpenERP:
When you're ready with Selenium IDE there are several steps more:

1. Get the scripts in a zip file here... https://github.com/martinhbramwell/SeleniumForOpenERP/zipball/master ... which is a download link on this page ...
https://github.com/martinhbramwell/SeleniumForOpenERP

They are plain text, so you don't need any special tool to download them or read their contents. Just extract the files from the zip file and they will be ready for use.


2. You'll need an extra plugin for Selenium IDE that adds conditional execution and looping.  You'll need the file SELBlocks.xpi contained in this zip file https://github.com/martinhbramwell/SEL-Blocks/zipball/master that you can obtain from the link on this page ...
   https://github.com/martinhbramwell/SEL-Blocks
   ... 
   
   Note that I provide a "sanity check" test suite called TestSuite_TestSELBlocksConstructs in the directory test. 
   
   
3. None of the scripts have any way to know where your OpenERP server is, but telling them is trivial.  The steps are:
1. start Firefox,
2. go to Login page of your OpenERP web server,
3. start Selenium IDE from the menu WebDeveloper  (or Tools in older versions of Firefox),
4. * * Clear the Base URL line * ,
5. click once in any of the three data entry fields of the OpenERP Login page,
6. run the first script.  Selenium will get the URL of your server and use it until further notice.

Generate a database:
The first suite of scripts to run is called "CreateVacantDatabase " contained in the folder of the same name here:

https://github.com/martinhbramwell/SeleniumForOpenERP/tree/master/CreateVacantDatabase

The script uses the default user name admin and password admin for the superadministrator, but uses the user name okok and password okok for a newly created database called esrd.


If you need to change that you can do so in the script:
SeleniumForOpenERP\CreateVacantDatabase\TestCase_SpecifyCriteria.html

Tip!
Before you start, make sure you know how to backup, restore and make copies of the databases of OpenERP.  Count on it -- you're going to suffer plenty of fits and starts.  Periodic snapshots will save you the misery of going back to Lesson #1 each time you screw up.

... but not always!  The real unpleasantness comes when your novice knowledge leads you to do something wrong early in the set up process whose consequences only appear much later on.  Your knowledge isn't yet sufficient to undo the damage and your backups aren't either.  My Selenium scripts go along way towards mitigating that.  You can fix the early mistake and run the scripts to get you quickly back to where you were.

OpenERP backups are a mess!  I wasn't able to get them working through OpenERP and found it very "clicky" with pgAdmin III !!  (I also found that I could only ever create databases with the web client and drop databases with the executable client. Hmmm.)  Instead I used the command line method as described below.

You should be able to cut'n paste each block below directly into your Linux command line and get the expected result.  You can do it a bit at a time, too.

Note that you'll need to search for and replace with your own data the following labels:  TypeYourDatabaseNameHere, TypeYourDatabaseHostHere, TypeYourDatabaseUserNameHere, TypeYourDatabasePasswordHere, TypeYourDatabasePortNumbeHere, TypeYourNEWDatabaseNameHere andTypeTheDateCodeOfThePreviouslyCreatedBackupHere.

Initial set up steps are :

# Begin
# Go to our home directory
# (Check that you get : /home/yourUserID)
cd ~/
pwd
# First we need a directory in which to keep our backup files ...
mkdir -p ~/databaseBackups
# (Check that you get : drwxr-xr-x 2 yourUserID yourGroup nn yr-mt-dy hr:mn databaseBackups)
ls -dl da*
# We'll be running our commands from our home directory but the activity will be in "databaseBackups"
#
# Next step :
# Since we don't want to have to type the database details each time we set up substitution variables...
# Please use your own details as indicated
# eg; where it says "export MyDatabasePortNumber=TypeYourDatabasePortNumberHere" you should
# replace it with something like "export MyDatabasePortNumber=5432"
#
export MyDatabaseName=TypeYourDatabaseNameHere
export MyDatabaseHost=TypeYourDatabaseHostHere
export MyDatabaseUserName=TypeYourDatabaseUserNameHere
export MyDatabasePassword=TypeYourDatabasePasswordHere
export MyDatabasePortNumber=TypeYourDatabasePortNumberHere
# (Check that you get : YourDatabaseHost:YourDatabasePortNumber:YourDatabaseName:YourDatabaseUserName:YourDatabasePassword)
echo ${MyDatabaseHost}:${MyDatabasePortNumber}:${MyDatabaseName}:${MyDatabaseUserName}:${MyDatabasePassword}
#
# Next we want to create a private password file
cat > .pgpass  <<EOF
${MyDatabaseHost}:${MyDatabasePortNumber}:*:${MyDatabaseUserName}:${MyDatabasePassword}
EOF
# Did that worked as expected?
# (Check that you get : YourDatabaseHost:YourDatabasePortNumber:*:YourDatabaseUserName:YourDatabasePassword)
cat .pgpass
# ... but, even so, Postgres will ignore it if it isn't really private, so :
chmod 600 .pgpass
# The -rw------- means only you can read and write it.
# (Check that you get : -rw------- 1 yourUserID yourGroup nn yr-mt-dy hr:mn .pgpass)
ls -la .pgpass
#
#
# End

With that prepared for any future use, we can create a Postgres backup file at any moment with a single command "pg_dump":

# Begin
# Now we can run our back up command
cd ~/
pg_dump ${MyDatabaseName} -v -Fc -U ${MyDatabaseUserName} -h ${MyDatabaseHost} -p ${MyDatabasePortNumber} > ./databaseBackups/OpenERP_${MyDatabaseName}_$(date +%y%m%d%H%M).backup
# (Check that you got many dozens of lines like : "pg_dump: dumping contents of table some_table_name")
#
# Now check the backups directory ...
# (Check that you get : "-rw-r--r-- 1 yourUserID yourGroup nn yr-mt-dy hr:mn OpenERP_YourDatabaseName_yrmtdyhrmn.backup")
ls -l ./databaseBackups/
#
# You now have a back up file.
#   *** Run it again and you'll have two backup files ***
# Done
#

To get it back, you can run these two steps :
1) Create the new database...

#
# Begin
# Now we want to create a new database from the backup.
# We make a substitution variable for its name
export My_New_DatabaseName=TypeYourNEWDatabaseNameHere
# (Check that you get : "Creating new database : Your_NEW_Database_Name")
echo Creating new database : ${My_New_DatabaseName}
#
# Let's get rid of the old one first, if there.  If not, ignore the warning.
dropdb -U ${MyDatabaseUserName}  -h ${MyDatabaseHost} -p ${MyDatabasePortNumber} ${My_New_DatabaseName}
#
# Now we can create the database
# (Check that you get : 'CREATE DATABASE "Your_NEW_Database_Name" TEMPLATE template0;')
createdb -e -U ${MyDatabaseUserName}  -h ${MyDatabaseHost} -p ${MyDatabasePortNumber} -T template0 ${My_New_DatabaseName}
# We now have a new empty database into which we can restore the backup we made earlier.
#

2) Restore the new database into it...

#
# Begin
# The last step is to restore the database into the newly created one.
# We make a substitution variable for the backup file
export My_OldArchive_Prefix=OpenERP_
export My_OldArchive_Suffix=.backup
export My_OldArchive_DateCode=TypeTheDateCodeOfThePreviouslyCreatedBackupHere
export My_OldArchive_Name=${My_OldArchive_Prefix}${MyDatabaseName}_${My_OldArchive_DateCode}${My_OldArchive_Suffix}
# (Check that you get : "Restoring archive : OpenERP_YourDatabaseName_TheDateCodeOfThePreviouslyCreatedBackup.backup")
echo Restoring archive : ${My_OldArchive_Name}
# Now we can create the database
# (Check that you get : 'CREATE DATABASE "Your_NEW_Database_Name" TEMPLATE template0;')
pg_restore -Fc -d ${My_New_DatabaseName} -U ${MyDatabaseUserName}  -h ${MyDatabaseHost} -p ${MyDatabasePortNumber} ~/databaseBackups/${My_OldArchive_Name}
#
#
#      ¡ ¡ ¡ We now have a new copy of the previous database ! ! !
#
#

¡¡ With that done, everything gets simple !!

In future you just have to run single line commands like these :

Backup

pg_dump ${MyDatabaseName} -Fc -U ${MyDatabaseUserName} -h ${MyDatabaseHost} -p ${MyDatabasePortNumber} > ./databaseBackups/OpenERP_${MyDatabaseName}_$(date +%y%m%d%H%M).backup

Create New Database

export My_New_DatabaseName=esrdBK_004

dropdb -U ${MyDatabaseUserName}  -h ${MyDatabaseHost} -p ${MyDatabasePortNumber} ${My_New_DatabaseName}

createdb -e -U ${MyDatabaseUserName}  -h ${MyDatabaseHost} -p ${MyDatabasePortNumber} -T template0 ${My_New_DatabaseName}

Restore to New Database

ls -lrt ./databaseBackups/

export My_OldArchive_DateCode=TypeTheDateCodeOfThePreviouslyCreatedBackupHere

pg_restore -Fc -d ${My_New_DatabaseName} -U ${MyDatabaseUserName}  -h ${MyDatabaseHost} -p ${MyDatabasePortNumber} ~/databaseBackups/${My_OldArchive_Name}

¡ Caution ! - Restore operations may confuse the OpenERP Web Server.  The best policy is to shut it down before changing the database underneath it, then restart afterwards.

Ready?
If you've done all of the above you should be all set for Lesson #1...