Ideas To Improve Existing Functionality of webERP

1. Traceability of GRNs paid is not as good as it could be. To identify when an item received was paid for it would be great to be able to click on a stock movement in the movements inquiry and find the GRN it was received on and the supplier invoice it was paid for on. Currently the only loop back to the GRN is through the narrative on the GL transactions created if the GL integration to stock is not used then we don't even have this! I think what is required is to store the GRNs against supplier invoices in a new table - credit notes would need to alter these or perhaps store another negative entry.
2. A history of the costs could be maintained together with the reasons for cost changes with user names effecting the cost changes.
3. An update and insert transaction log - with the username who effected the updates inserts. A new table would be required. This could be done through modifications to the DB_query script and the text of the messages sent to the DB_query function used as log entries. Or maybe the prnMsg function....

<Tim>
The problem here is that none of the messages currently sent to DB_query are very informative regarding what the transaction was doing. That information is contained in $SQL, but I think this could cause security issues? If the sql could be stored in a secure way, then a script could be written to parse this string, and display it in a readable form as an audit trail.
</Tim>
<Phil>Yes - unless we add another parameter to DB_query and do "a little" rework on existing scripts - or what about making a stab at what is being done based on the calling script? - we still need transaction numbers etc though. I am not sure that we want to duplicate the SQL log which is accessible already - provided it is enabled in my.cnf. This is quite a job to effect but probably a significant over-sight in the original system development ... whoops:-(</Phil>

<Tim>
What I had in mind was different than the sql log, I had in mind a table with the following fields :

transactionid (auto incremented)
userid
datetime
sqlstring

Within the DB_query function we would then filter out all queries other than INSERT/UPDATE/DELETE queries, and just update the above table with these queries.

Then for the reporting side a script could be written that parsed the sqlstring (there are php classes released under gpl that will parse sql statements, though I haven't investigated them) and output to a table with the following headings:

|Transaction id | User | Date |Table Name |Field Name |New Value| Type of Change|

Or something along these lines.

This would keep the changes needed to just a few scripts.
</Tim>

<Phil>
Yes that would work ... maybe with the addition of the calling script as an additional field?
Thinking about it some more in fact the UPDATE/INSERT/DELETE sql is probably the most succinct way of describing what is happening - rather than long winded narrative that we would otherwise have to change every call to DB_query - it is the association of the login name and the sql we are missing from the sql log. Of course the mysql binary log does filter out select queries already - but we don't have the name next to the sql with that so maybe pragmatically this is the best solution. It will be clever script to parse these into english though...

May be useful to build in also a new configuration option for the number of months of audit log history to keep otherwise this could end up one "big momma" of a table!
</Phil>


<JeffWang>
in weberp_production version, source ConnectDB_mysql.inc, they used a function AuditTrail($sql, $user, $page) and a table `sqlaudit`.
Hope this is helpful.
Yes, the problem is we need a config parameter to control the increase of table `sqlaudit`
</JeffWang>

<Phil> .... et voila - Tim coded this up and I put in tupence - committed to CVS 13 November 2007 - nice!!</Phil>
Valid XHTML :: Valid CSS: :: Powered by WikkaWikiGet webERP Accounting & Business Management at SourceForge.net. Fast, secure and Free Open Source software downloads