Thread: Invoices

Invoices

From
Ludwig Meyerhoff
Date:
Hallo!

Maybe this is a bit off-topic, as this problem is more a "design"-one, but
I wanted to write a web-application write invoices more easy. I wonder if
it was a good idea to try this using Postgres or if it was better to write
the data of each invoice in a separate file in a separate directory.

I doubt it was a good idea to put all the data into one database like

create table invoices(
  invoice integer primary key,
  datum date,
  customer integer references customers,
  clerk integer references clerks
);
create table invoicedata(
  invoice integer references invoices,
  item integer references services,
);


as this would mean all the information of all invoices is stored in one
table, meaning the table will grow to a HUGE size making queries very
slow.


On the other side I doubt following solution will be a good idea, too!

create table invoices
(
  invoice integer primary key,
  datum date,
  customer integer references customers,
  clerk integer references clerk
);
create table INVOICENUMBER
(
  item integer references services,
  amount integer,
);

as this will create a HUGE number of tables and I think it was not a good
idea to give users permission to create new tables.


Maybe someone can help?


Saluti!

Ludwig


Re: Invoices

From
Robert Hentosh
Date:
On Thu, May 03, 2001 at 10:06:05PM +0200, Ludwig Meyerhoff wrote:
> Hallo!
>
> Maybe this is a bit off-topic, as this problem is more a "design"-one, but
> I wanted to write a web-application write invoices more easy. I wonder if
> it was a good idea to try this using Postgres or if it was better to write
> the data of each invoice in a separate file in a separate directory.
>
> I doubt it was a good idea to put all the data into one database like
>
> create table invoices(
>   invoice integer primary key,
>   datum date,
>   customer integer references customers,
>   clerk integer references clerks
> );
> create table invoicedata(
>   invoice integer references invoices,
>   item integer references services,
> );
>
>
> as this would mean all the information of all invoices is stored in one
> table, meaning the table will grow to a HUGE size making queries very
> slow.

That depends on what you think HUGE is and slow is.

Best thing to do is make a good estimation of how many
invoices you are planning to keep in the table and then
go generate a script to create a dummy table with that
many rows in it and test the reponse times.  Use EXPLAIN
command to find out what is slowing your queries and make
sure you have created indexes on the columns that your
queries use. ( Don't forget to run VACUUM on your DB after
you do the inserts)

It makes sense sometimes to split something like this into
two tables with identical stucture.  One table would be the
invoices that you use most commonly and the other an archive
of invoices that you move delete from the first table when you
don't need them in most of your query results. (Like invoices from a year ago)

> On the other side I doubt following solution will be a good idea, too!
>
> create table invoices
> (
>   invoice integer primary key,
>   datum date,
>   customer integer references customers,
>   clerk integer references clerk
> );
> create table INVOICENUMBER
> (
>   item integer references services,
>   amount integer,
> );
>
> as this will create a HUGE number of tables and I think it was not a good
> idea to give users permission to create new tables.
>
>
> Maybe someone can help?
>
>
> Saluti!
>
> Ludwig
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Re: Invoices

From
Vince Vielhaber
Date:
On Thu, 3 May 2001, Ludwig Meyerhoff wrote:

> Hallo!
>
> Maybe this is a bit off-topic, as this problem is more a "design"-one, but
> I wanted to write a web-application write invoices more easy. I wonder if
> it was a good idea to try this using Postgres or if it was better to write
> the data of each invoice in a separate file in a separate directory.
>
> I doubt it was a good idea to put all the data into one database like

I wrote a couple of programs for various projects to do this (one was
for someone else so I can't release it).  I kept all the data in their
own tables - account info in the account table, customer info in the
customer table, etc. - and put the data together as I generated the
invoice.  Simple, huh?  Not exactly.

Here's where the problem arises.  You can create a really good looking
invoice, in fact you can create a whole bunch of really good looking
invoices.  What you can't do with a web app is send a form feed to the
printer!  The only solution I've found so far is to create each invoice
individually using javascript and each in their own window.  Then you
issue the print command to each window.  Here's where another problem
arises...  Each print command also presents you with a print dialog, so
if you have 100 invoices you have 100 windows and 100 print dialogs and
you have to answer each dialog.

There is an alternate method and that's to write a java app to do the
printing and launch and feed it via the browser clicking feeding it a
particular datafile (much like launching RealAudio or Acrobat or ...)
Of course that really shoots the hell out of the nice invoice you can
create in your web browser.

So in that respect I'm still looking for a solution.

Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH    email: vev@michvhf.com    http://www.pop4.net
         56K Nationwide Dialup from $16.00/mo at Pop4 Networking
        Online Campground Directory    http://www.camping-usa.com
       Online Giftshop Superstore    http://www.cloudninegifts.com
==========================================================================




Re: Invoices

From
Paul Ramsey
Date:
There are a couple good solutions to this:
- use PDFLib to generate printable format directly then pipe it directly
to the browser. There are PHPLib hooks in PHP and also a perl module
which hooks into it. You can even access it in Java via JNI calls.
- create HTML output, but pipe it to html2ps on the system, then pipe
that through ps2pdf, then pipe it back out to the browser. The nice
thing about this approach is that you don't have to write your own page
rendering routines, html2ps does it for you, and you just control it
with style-sheet directives.
In any event, reporting off a database from the browser does not have to
be clunky or unattractive, there's some very good tools available.

Vince Vielhaber wrote:

> Here's where the problem arises.  You can create a really good looking
> invoice, in fact you can create a whole bunch of really good looking
> invoices.  What you can't do with a web app is send a form feed to the
> printer!  The only solution I've found so far is to create each invoice
> individually using javascript and each in their own window.  Then you
> issue the print command to each window.  Here's where another problem
> arises...  Each print command also presents you with a print dialog, so
> if you have 100 invoices you have 100 windows and 100 print dialogs and
> you have to answer each dialog.

--
      __
     /
     | Paul Ramsey
     | Refractions Research
     | Email: pramsey@refractions.net
     | Phone: (250) 885-0632
     \_

Re: Invoices

From
"Mitch Vincent"
Date:
    I've done several application where printing was involved (Invoices,
packing slips, account statements, purchase orders, etc).. I generate PDFs
for this and either use Ghostscript to turn them into Postscript and print
them from the server or send the PDF (in one large file if needed) to the
user so they can print it.. It take a little while to get the PDF right the
first time but it's generally something you only have to do once.. I use PHP
a lot for the web apps and there are a few libraries available for PDF
creation (I've used both ClibPDf and PDFlib, they are both pretty nice)..

    Good luck!

-Mitch

> Here's where the problem arises.  You can create a really good looking
> invoice, in fact you can create a whole bunch of really good looking
> invoices.  What you can't do with a web app is send a form feed to the
> printer!  The only solution I've found so far is to create each invoice
> individually using javascript and each in their own window.  Then you
> issue the print command to each window.  Here's where another problem
> arises...  Each print command also presents you with a print dialog, so
> if you have 100 invoices you have 100 windows and 100 print dialogs and
> you have to answer each dialog.
>
>
> There is an alternate method and that's to write a java app to do the
> printing and launch and feed it via the browser clicking feeding it a
> particular datafile (much like launching RealAudio or Acrobat or ...)
> Of course that really shoots the hell out of the nice invoice you can
> create in your web browser.
>
> So in that respect I'm still looking for a solution.



Re: Invoices

From
Robert
Date:
Ludwig Meyerhoff wrote:
>
> Maybe this is a bit off-topic, as this problem is more a "design"-one, but
> I wanted to write a web-application write invoices more easy.
> ...

  Hi,

  I write the same application for the same reasons - we're three
partners and we all want to be able to create invoices and also see what
others create. A rather experimental version runs at
http://www.eucto.cz/ login as 'admin_jaskot', no password, you need
IE5+. It's in Czech, but you just go to 'Faktury' (Invoices), 'Nova
faktura' (New Invoice), 'Ulozit' (Save) and 'Tisk' (Print). The
application has grown a bit since I started and now it's slowly becoming
complete web-based accounting package. When I finish new version (in
maybe three weeks), it should do receivable, payable, cash, bank, should
print fine and should have basic support for multiple users and multiple
companies. Then I clean it up, add a bunch of gettext's to prepare an
English version and start to think really hard about releasing the
source. It's based on PostgreSQL/Apache/Perl (currently mod_perl &
HTML::Embperl) and it (almost) works with recent Mozilla builds. I
expect to get paid as a consultant by accounting companies that would
offer this as a web service to their clients and/or for running this
service for them.

  So, here's the preliminary inquiry: anybody interested in such a thing
as a user or as a developer?

  - Robert

Re: Invoices

From
Tony Grant
Date:
On 03 May 2001 17:05:44 -0700, Paul Ramsey wrote:
>
> There are a couple good solutions to this:
> - use PDFLib to generate printable format directly then pipe it directly
> to the browser. There are PHPLib hooks in PHP and also a perl module
> which hooks into it. You can even access it in Java via JNI calls.
> - create HTML output, but pipe it to html2ps on the system, then pipe
> that through ps2pdf, then pipe it back out to the browser. The nice
> thing about this approach is that you don't have to write your own page
> rendering routines, html2ps does it for you, and you just control it
> with style-sheet directives.
> In any event, reporting off a database from the browser does not have to
> be clunky or unattractive, there's some very good tools available.

My problem is with mailing labels. Standard Avery sort. How do I do
about formatting multiple pages for mass mailings?

Any pointers?

Cheers

Tony Grant

--
RedHat Linux on Sony Vaio C1XD/S
http://www.animaproductions.com/linux2.html


Re: Invoices

From
"Roderick A. Anderson"
Date:
On Fri, 4 May 2001, Robert wrote:

>   I write the same application for the same reasons - we're three
> partners and we all want to be able to create invoices and also see what
> others create.

Have you looked at SQL-Ledger?  THough I disagree with some of the
table designs it is a pretty good accounting package.  (Given I'm not an
accountant.)  Possible down side is it is web-server/browser based.  Has
support designed in for international use.


Rod
--
                 Remove the word 'try' from your vocabulary ...
                     Don't try.  Do it or don't do it ...
                                Steers try!

                                                            Don Aslett


Re: Invoices

From
Vince Vielhaber
Date:
On Fri, 4 May 2001, Roderick A. Anderson wrote:

> On Fri, 4 May 2001, Robert wrote:
>
> >   I write the same application for the same reasons - we're three
> > partners and we all want to be able to create invoices and also see what
> > others create.
>
> Have you looked at SQL-Ledger?  THough I disagree with some of the
> table designs it is a pretty good accounting package.  (Given I'm not an
> accountant.)  Possible down side is it is web-server/browser based.  Has
> support designed in for international use.

Got a URL?

Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH    email: vev@michvhf.com    http://www.pop4.net
         56K Nationwide Dialup from $16.00/mo at Pop4 Networking
        Online Campground Directory    http://www.camping-usa.com
       Online Giftshop Superstore    http://www.cloudninegifts.com
==========================================================================




Re: Invoices

From
Vince Vielhaber
Date:
On 4 May 2001, Tony Grant wrote:

> On 03 May 2001 17:05:44 -0700, Paul Ramsey wrote:
> >
> > There are a couple good solutions to this:
> > - use PDFLib to generate printable format directly then pipe it directly
> > to the browser. There are PHPLib hooks in PHP and also a perl module
> > which hooks into it. You can even access it in Java via JNI calls.
> > - create HTML output, but pipe it to html2ps on the system, then pipe
> > that through ps2pdf, then pipe it back out to the browser. The nice
> > thing about this approach is that you don't have to write your own page
> > rendering routines, html2ps does it for you, and you just control it
> > with style-sheet directives.
> > In any event, reporting off a database from the browser does not have to
> > be clunky or unattractive, there's some very good tools available.
>
> My problem is with mailing labels. Standard Avery sort. How do I do
> about formatting multiple pages for mass mailings?
>
> Any pointers?

I'd write a simple C app to read the database and write directly to
the printer port.  First print to paper and line it up over the labels.

Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH    email: vev@michvhf.com    http://www.pop4.net
         56K Nationwide Dialup from $16.00/mo at Pop4 Networking
        Online Campground Directory    http://www.camping-usa.com
       Online Giftshop Superstore    http://www.cloudninegifts.com
==========================================================================




Re: Invoices

From
Robert
Date:
Vince Vielhaber wrote:
>
> On Fri, 4 May 2001, Roderick A. Anderson wrote:
>
> > On Fri, 4 May 2001, Robert wrote:
> >
> > >   I write the same application for the same reasons - we're three
> > > partners and we all want to be able to create invoices and also see what
> > > others create.
> >
> > Have you looked at SQL-Ledger?  THough I disagree with some of the
> > table designs it is a pretty good accounting package.  (Given I'm not an
> > accountant.)  Possible down side is it is web-server/browser based.  Has
> > support designed in for international use.
>
> Got a URL?

  It used to be at http://www.simtax.ca/acc/ but it's not responding
right now. I didn't like few things about it, but I don't remember what
it was anymore, I'm going to take a look at it again though.
  Anyway, there's similar service from Oracle (and few others), but it's
not free (just free test) and when I last checked it wasn't as nice as
it could be. And it wasn't built on PostgreSQL ;-)

  - Robert

Re: Invoices

From
Vince Vielhaber
Date:
On Fri, 4 May 2001, Robert wrote:

> Vince Vielhaber wrote:
> >
> > On Fri, 4 May 2001, Roderick A. Anderson wrote:
> >
> > > On Fri, 4 May 2001, Robert wrote:
> > >
> > > >   I write the same application for the same reasons - we're three
> > > > partners and we all want to be able to create invoices and also see what
> > > > others create.
> > >
> > > Have you looked at SQL-Ledger?  THough I disagree with some of the
> > > table designs it is a pretty good accounting package.  (Given I'm not an
> > > accountant.)  Possible down side is it is web-server/browser based.  Has
> > > support designed in for international use.
> >
> > Got a URL?
>
>   It used to be at http://www.simtax.ca/acc/ but it's not responding
> right now. I didn't like few things about it, but I don't remember what
> it was anymore, I'm going to take a look at it again though.
>   Anyway, there's similar service from Oracle (and few others), but it's
> not free (just free test) and when I last checked it wasn't as nice as
> it could be. And it wasn't built on PostgreSQL ;-)

Yep, that site's gone.  I did find this however:  http://sql-ledger.com/

Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH    email: vev@michvhf.com    http://www.pop4.net
         56K Nationwide Dialup from $16.00/mo at Pop4 Networking
        Online Campground Directory    http://www.camping-usa.com
       Online Giftshop Superstore    http://www.cloudninegifts.com
==========================================================================




Re: Invoices

From
"Roderick A. Anderson"
Date:
On Fri, 4 May 2001, Vince Vielhaber wrote:

> Got a URL?

OOPS!  http://www.sql-ledger.org/


Rod
--
                 Remove the word 'try' from your vocabulary ...
                     Don't try.  Do it or don't do it ...
                                Steers try!

                                                            Don Aslett


Re: Invoices

From
Louis-David Mitterrand
Date:
* On Thu, May 03, 2001 at 07:45:23PM -0400, Vince Vielhaber wrote:
> On Thu, 3 May 2001, Ludwig Meyerhoff wrote:
>
> > Hallo!
> >
> > Maybe this is a bit off-topic, as this problem is more a "design"-one, but
> > I wanted to write a web-application write invoices more easy. I wonder if
> > it was a good idea to try this using Postgres or if it was better to write
> > the data of each invoice in a separate file in a separate directory.
> >
> > I doubt it was a good idea to put all the data into one database like
>
> I wrote a couple of programs for various projects to do this (one was
> for someone else so I can't release it).  I kept all the data in their
> own tables - account info in the account table, customer info in the
> customer table, etc. - and put the data together as I generated the
> invoice.  Simple, huh?  Not exactly.
>
> Here's where the problem arises.  You can create a really good looking
> invoice, in fact you can create a whole bunch of really good looking
> invoices.  What you can't do with a web app is send a form feed to the
> printer!

Yes you can. Try the CSS2 reference manual:

    13.3 Page breaks

    The following sections explain page formatting in CSS2. Five properties
    indicate where the user agent may or should break pages, and on what
    page (left or right) the subsequent content should resume. Each page
    break ends layout in the current page box and causes remaining pieces of
    the document tree to be laid out in a new page box.

And it works, we use it in our own app.

--
    OENONE: Elle vient.
 HIPPOLYTE: Il suffit : je la laisse en ces lieux,
            Et ne lui montre point un visage odieux.
                                          (Phèdre, J-B Racine, acte 1, scène 2)

Re: Invoices

From
Vince Vielhaber
Date:
On Sun, 13 May 2001, Louis-David Mitterrand wrote:

> * On Thu, May 03, 2001 at 07:45:23PM -0400, Vince Vielhaber wrote:
> > On Thu, 3 May 2001, Ludwig Meyerhoff wrote:
> >
> > > Hallo!
> > >
> > > Maybe this is a bit off-topic, as this problem is more a "design"-one, but
> > > I wanted to write a web-application write invoices more easy. I wonder if
> > > it was a good idea to try this using Postgres or if it was better to write
> > > the data of each invoice in a separate file in a separate directory.
> > >
> > > I doubt it was a good idea to put all the data into one database like
> >
> > I wrote a couple of programs for various projects to do this (one was
> > for someone else so I can't release it).  I kept all the data in their
> > own tables - account info in the account table, customer info in the
> > customer table, etc. - and put the data together as I generated the
> > invoice.  Simple, huh?  Not exactly.
> >
> > Here's where the problem arises.  You can create a really good looking
> > invoice, in fact you can create a whole bunch of really good looking
> > invoices.  What you can't do with a web app is send a form feed to the
> > printer!
>
> Yes you can. Try the CSS2 reference manual:
>
>     13.3 Page breaks
>
>     The following sections explain page formatting in CSS2. Five properties
>     indicate where the user agent may or should break pages, and on what
>     page (left or right) the subsequent content should resume. Each page
>     break ends layout in the current page box and causes remaining pieces of
>     the document tree to be laid out in a new page box.
>
> And it works, we use it in our own app.

Yes, that was pointed out last week.  It works with opera and ie, not
with mozilla or any of the netscapes (4 or 6) but it is a start.

Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH    email: vev@michvhf.com    http://www.pop4.net
         56K Nationwide Dialup from $16.00/mo at Pop4 Networking
        Online Campground Directory    http://www.camping-usa.com
       Online Giftshop Superstore    http://www.cloudninegifts.com
==========================================================================