[Phpgroupware-users] Porting of SQL-Ledger to run on top of phpgroupware

[C K Wu]

Hello, folks,

This is a rather lengthy post.  Please bear with me.

I have started a project at sourceforge and named it, CK-Ledger, which
is a port of SQL-Ledger to phpgroupware.  Before I go any further, I
like to congratulate Dieter Simader and the SQL-Ledger team in
developing an excellent open source solution.

Since CK-Ledger is based on SQL-Ledger, its functionalities are very
similar to SQL-Ledger's.  The features that I have added, include,

LedgerAdmin – data backup/restore;
Ledger – audit control report, account journal history, ledger listing;
Inventory – audit control report, inventory in/out history, explicit
inventory adjustment transaction, goods receipt transaction, good
delivery transaction;
Service – separate module for access by non-warehouse staff;
Accounts Payable – audit control report, manual override of system
calculated tax, separate rate and account setting for tax received and
paid, explicit payment transaction, matching of payment against invoice
allowing one payment settling multiple invoices, invoice/PO matching,
invoice payment history, cheque printing;
Accounts Receivable – audit control report, manual override of system
calculated tax, separate rate and account setting for tax received and
paid, explicit receipt transaction, matching of receipt against invoice
allowing one receipt settling multiple invoices, invoice/SO matching,
invoice receipt history, customer statement, invoice reminder, official
receipt;
Purchase Order – audit control report, purchase order billing history;
Sales Order – audit control report, sales order billing history, order
acknowledgement;

However, there are SQL-Ledger features that I have not (or not yet)
implemented, notably standalone AP/AR transactions and foreign currency
translation.  At the same time, the internal data structure of CK-Ledger
and SQL-Ledger are different.

Most transactions are provided with standard search, add, view copy and
delete facilities.  Journal reversing feature is included.  While
currently, here in Hong Kong, we do not charge a transactional tax (in
the form of sales or VAT tax), such tax is being contemplated.
Therefore, I have produced, 2 streams, to handle invoices, POs and SOs
that do and do not carry tax.

CK-Ledger is structured as 8 phpgroupware modules, LedgerAdmin, Ledger,
Inventory, Service, AccountsPayable, AccountsReceivable, PurchaseOrder
and SalesOrder.  Each of these modules can be set up as visible or
hidden from specific user (group) utilizing phpgroupware's native
grouping and admin functions.

Please, be warned that the current version, v.0.0.5, is a half-baked
edition of a pre-alpha version, so DON'T and I repeat, DON'T, apply this
version in any production environment.  For one thing, the rounding and
presentation of tax calculation results have not been sufficiently
stress tested.  However, I did test this version against the following
configuration without any major error:

RedHat 7.2, Apache 1.3.20, PostgreSQL 7.1.3, phpgroupware 0.9.12 (most
stable setup)
RedHat 7.2, Apache 1.3.20, PostgreSQL 7.1.3, phpgroupware 0.9.14RC1
(Some errors occurred when logging out of phpgroupware, but this would
not affect the working of CK-Ledger)
Mandrake 8.1, Apache 1.3.20, PostgreSQL 7.1.2, phpgroupware 0.9.14RC1
(Same errors as with RedHat 7.2)

To test drive CK-Ledger, download the tarball from sourceforge.net.
Expand the tarball into 8 folders (ledadmin, ledger, inventory, service,
ap, ar, po, so) within the phpgroupware directory.  Login as
phpgroupware administrator to enable individually the 8 modules.  [To
avoid confusion with phpgroupware's native inventory module, you may
like to name CK-Ledger's inventory module, as something like,
CK-Inventory.]  Invoke LedgerAdmin and create test data as indicated in
the index page.  I have also posted the detail installation procedure at
address@hidden  Please report any error you
found to address@hidden, I'll gladly rectify any
error as soon as possible.

I have not applied for CK-Ledger to be included under phpgroupware
because,

1.Currently, it only work with PostgreSQL.  Back when I started the
software porting, MySQL was still being criticised for “not supporting
transactions”
2.I don't intend to make CK-Ledger compatible with fee charging database
engines, ie Oracle, MSSQL, Sybase, etc, although I am OCP(Oracle8) and
MCDBA certified.
3.Currently, CK-Ledger definitely fails some of phpgroupware's coding
standards.
4.The protracted discussion over “gif” is too much a drag for me.  I am
not a political puritan by any standard.
5.I have not even started to consider php3 compatibility.  CK-Ledger is
developed using php-4.0.6.

If you are wondering why I did the software porting in the first place,
here are the reasons,

1.I am fully unemployed at the moment, so may as well use the spare
time.
2.My backgrounds are LPIC-1, OCP(AD, Order Fulfillment, Procurement),
MCSE, MCDBA, MCSD, FHKSA, CISA, so when I saw Antonio Gallardo's email
back in Jan-2001 suggesting integration of SQL-Ledger and phpgroupware,
it was too tempting to pass over.

My immediate plan is to produce a simplified/traditional Chinese version
of CK-Ledger for use in the Greater China region (PRC, Hong Kong, Macau,
Taiwan) and, at the same time, work on an hr and payroll extension for
CK-Ledger and to better integrate CK-Ledger with phpgroupware.  I'll
probably create a Setup3 compliant version when phpgroupware 0.9.14 is
formally released.

I would like to appeal for assistance from other developers in the
Greater China region in helping to produce the Chinese version of
CK-Ledger, especially in terms of the format of reports required by the
local taxation authority when reporting VAT received and recovery
claimed.

At the same time, assistance from other phpgroupware developers is most
welcome.  Comparing to phpgroupware developers, I am definitely email
novice.  While CK-Ledger does implement auto emailing of invoice,
statement, order acknowledgement to customer, and purchase order to
vendor, such features are rather rudimentary.  The only audit trail of
having a email sent is a bcc back to the sender's email address.  It
would be nice to have copy of the email auto dropped into invoice_sent,
statement_sent, po_sent (etc) folders within the phpgroupware
environment, or dropped into a customer/vendor specific folder.  With
direct emailing of purchase order to vendor, the email could be a
primary piece of legal evidence on the T&Cs of goods/service provision.
Thus, whether the email has been sent/received and the timing of the
email posting/receipt could have  major legal implications.  It would be
nice to have options to sent receipt-requesting email, to auto-attach
electronic signature or to have the email sent via SSL, etc, etc, etc.
Again it would be nice to have the various documents auto-faxed instead
of being emailed.  I have actually tried html2ps+gs+efax and
successively faxed out but the resulting fax was simply too ugly.  All
these features are beyond me, so your assistance, in any form, is most
welcome and will be acknowledged.  The importance of such features is
evidenced by a SQL-Ledger user indicating his/her main use of SQL-Ledger
was to automate the sending of invoices.  Other phpgroupware module
developers integrating his/her module with CK-Ledger, eg time billing,
would also be most welcome.

Assistance from MySQL developers to ensure CK-Ledger works with MySQL
will also be highly appreciated.  For one thing, I am sure the Data
Restore routine will not work with MySQL, because I am getting lazy and
simply use a PostgreSQL command, “psql”, to facilitate the data restore.

Currently, there is only one default template.  Both idsociety and
verdilak are empty.  Developers with an artistic touch are welcome to
fill in the missing templates or to provide other new templates.

While contributions will be graciously acknowledged in the index page of
each module, I don't like to go into political arguments as to how major
a contribution should be, before it is acknowledged, so you will have to
trust me on deciding who and what to acknowledge.

Come to think about it.  If some folks would consolidate phpgroupware's
file handling capability, add in the ability to manipulate
OpenOffice/KOffice docs, CAD (QCAD, SuperMap, AutoCAD, Microstation)
files, etc, to produce a Document Management System within phpgroupware,
then phpgroupware would probably become phpCorporateWare with phpgw
consultants everywhere.

This is my HK$0.02 contribution towards phpgroupware v.1.0.

Have a nice, happy and perhaps belated Chinese New Year (be you Chinese
or not),
Wu Chiu Kay aka CK Wu aka CK (CK is the preferred alias)
Hong Kong