Mercian Labels-a UK SME Label Printer on Open Source migration

Coming from a MsSQL database with access queries/macros/modules and so on, its easy to be able to work out weekend days from two given dates, ie if you have two dates you can calculate how many days between them aren’t weekdays.

In postgresql, this isnt so easy!

there aren’t many examples on the net, without creating mass functions, but i’ve come up with this:

SELECT count(*) FROM generate_series(1, (higherdate::date - lowerdate::date)) i WHERE date_part(’dow’, higherdate::date + i) NOT IN (0,6);

the higherdate and lowerdate are exactly what they sound like, the higherdate has to be after the lowerdate.

That will work out how many days between each of those dates, and return a result that doesnt include sat/suns basically giving you working days!

It doesnt take into account holidays, but I didnt need it to..

Quick post here, as I was having trouble finding any information(that worked, or indepth enough to work for what I needed).

The type money is now deprecated from postgresql, and a few of our tables contained this data type. This is fine, and worked as we wanted, until there came a time I needed to do a query such as:

select * from mytable where (mytable.charge > 0);

seems easy enough, but if myfieldname is of type money, you will get nice errors such as:

ERROR: operator does not exist: money > integer
HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts.

Doing what the Hint suggests isnt easy, and theres pretty much no good way to cast money to anything.

A few examples on the net dont work, depending on version, or can mess up the data.

After a bit of searching and messing, ive found a solution that does whats needed:

First you need to do:

set lc_monetary=’C';

to basically control the currency, ours was set to £ but doing that sets it back to $ and makes things easier to work with.

Next you need to do:

alter table yourtable alter column yourfield type numeric(16,2) using translate(textin(cash_out(yourfield)), ‘$,’, ”)::numeric;

Once thats done, you’re set and it seems to work flawlessley (in version: 8.1.9 anyway).

May be a good idea to make a copy of the table before, just incase:

create table backup_table_yourtable AS select * from yourtable;

then if you have any issues you can revert back!

Sometimes I never learn, and check that backups are working as they should be.  Last night we had a zimbra failure that resulted in the default backup schedule not being run (incremental backup at 01:00), which measn that when we had to have a fresh install today we lost all yesterdays emails, calendar changes and additions, everything.

Yesterday I spent all day writing up 3 tactical plans for the business, and lost them all becuase of this.  Fortunately I got some of the work back from a syncd copy on a PDA, but not everything.  This episode also dented our team’s confidence in Zimbra as its the second time it has happened.

We have now improved the backup regime to daily full backups and hourly incremental ones.  We did this by changing the zimbra backup cron job using WEBMIN on port 10000 on our ubuntu server using the nice GUI webpage, and it seems to work.

A lesson for other new zimbra users, the default backups just ain’t enough, so update your instalation NOW to get more backups!

Lesson learnt the hard way (again).

I have just finished a rewarding 4 day course at OpenAdvantage on linux admin, very presented and run by Paul Cooper. I got a lot out of the course, but there was a wide range of attendees, from people with (literally) decades of unix experience to me, a microsoft convert making early steps in the command line environment. The course was based on the linuxIT course , for people working towards the linux professional institute exams (which I’m not taking!)

Overall, an excellent course. What I want to take away is a crib sheet of linux commands and notes I can sync into my address book, so that when I next need to run stuff on the command line its easy to hand. So here goes… Please note that these are my crib notes, not a definative linux command line crib sheet!

BASIC COMMAND LINE COMMANDS

echo “test” returns the text

CREDIT=300
echo $CREDIT returns the variable

ls -hl lists files in detail in human readable format
pwd tells you where you are in the directory structure

env shows the environment you are working in

ls -l >errors.txt redirects the output to errors.txt

set shows all variables
| Pipe symbol glues the lot together

ls f* gives all files starting with f
ls f???s.* gives all files but only with the right number of characters
ls [a-e]*.* for a range
ls my\ documents for files with a space in the name

SHORTCUTS

cntrol shift c and control shift v are copy and paste

tab gives tab completion of a predictable string
up arrowauto completes from the history of command lines submitted

home and end keys more up and down the line
man is the manual command for HELP!
eg
man ls

q to quit

alias ll=ls -l is a shortcut command

cd /media is absolute command to the media

FINDING FILES

find -name “f*.txt”

or

locate / “f*.txt”

REMOVE FILES

rmdir for directories
rm -r music removes the directory and all contents

COPY FILES

cp fil* my\ documents to copy

MOVE FILES

mv is move file

FILES SYSTEMS
ext2 is the linux old equivilant of fat32, a basic file format for linux
ext3 joural based file system - best to use

swap file partition should be double actual physical memory

lspci lists the hardware

RUNNING AS ROOT

sudo runs a single command as root
su is switch user eg
su - root

FORMAT DISKS

sudo fdisk /dev/sda runs the fdisk command

df tool for mounted and
du -h /usr as an example of finding file sizes

PERMISSIONS
user u
group g
other o

read r or 4
write w or 2
execute x or 1

character 1 is file info, 1-3 is user,4-6 is group,7-9 other

chmod u+x files.txt gives execute permision to users

PROCESSES

pstree -p shows the runningprocesses
ps ax shows all process for all users

kill pid number kills the process
or
kill -9 pid number

top gives the running processes

nohup runs outside the session

TEXT EDITOR

at is a basic text editor
control D stops it

head -5 hello.txt gives you the last 5 lines of hello.txt

grep term filename > results.txt

VI or VIM is a good embedded text editor

q! to quit

esc to go back to command line
x to save and escape

SSH

at command line you can ssh to any url

STOP AND START PROCESES
eg
sudo /etc/init.d/ssh stop

w
whoami tells you who is on a machine

SECURECOPY

scp root@ the address :whoteist.txt whitelist.txt is a secure copy

TIMING

crontab -l to list
crontab -e to edit

eg
45 14 * * * /bin/touch /home/demo/results.txt
will touch the results file at 14:45 every day

NETWORK

ifconfig gives you your network information, your IP address etc

---

I hope this is enough of a memory dump for future reference.  Thanks Paul for a very useful course

although the techies love it,probably for good reason, a lotof user interface with linux is at the command line, not the usual GUI interface that we are geneticly engineered to know and understand…. this means that if you really want to use some of the linux features and functions, you have to know how to use the command line.

I have some basic unix experience from numerical modelling research, but not enough that I didn’t have to start at square one.

I tried this website, which I was impressed with, and guided me through the basic functions well.

www.linux.org/lessons

Its worth a go , after 30 minutes I could even find and load our website files and edit them with the VI text editor.

I still prefer the GUI ubuntu interface though!

Well.. we finally decided that we should give the MVC framework process a chance, to see if we could truly benifit from it.

I’m going to be using a simple ish MVC framework and Smarty for the templating system, this seems to be working both logically and easy for us, or fairly easy anyway.

The way im going to do this is have a main page which will control the configuration etc, and then a series of templates for each section (ill come back to this) and then modules for each section too.

What i mean by section is this.. Basically the way I see how our company runs the existing database is based on a few sections(Customers, Orders, Searching, Report).. there is more, but this is just an example, so i’ve decided to have these as our main controlling elements.

So our intranet should work on this basis: http://address/index.php?section=Customer meaning our program knows we only want to be working with whatever is involved with a Customer, the same goes for Orders, Reports and so on, keeping the whole process a lot simpler (or so i think anyway)

With this in mind, ive now got a CustomerModelController which will take aditional parameters defining which action should be performed now we’re only concerned with the customer, for instance Search for Customers.

Our intranet again shows how easy and logical this makes things:

http://address/index.php?section=Customer&action=search_for_users or add_user etc, you get the idea anyway.

These methods allow me to have custom templates for each section, and still keep my code nice and tidy, so i’ve not got 100’s of smaller functions within one big controller.

I’ve tried not to go into too much detail here, so not to confuse people/myself anymore, but if anyone wants further explanations just let me know, and ill go into more detail.

Just want to say thanks to the people who have commented on the MVC ideas so far, definately made an interesting read, cheers!