pathans
Posts: 46
|
| Posted: 10/19/2012, 5:17 PM |
|
I have created an application for inventory. basically users can and see detail and edit any record if needed.
I would like to track all the changes user make in this setup.
all inventory related information is kept in inventory table. and I have changeby and changedate column which track information of who changed the record.
this only tells me who changed it and when i want to take it one step further and keep a log of all the changes.
what would you guys recommend.
|
 |
 |
MichaelMcDonald
Posts: 640
|
| Posted: 10/20/2012, 1:50 PM |
|
Speak with an inventory business and find out what the important log data is. They may advise that recording changes to inventory location are important to log as this affects how warehouse staff find, receive or despatch items, and it may be useful in identifying "shrinkage" related issues. .. such as who was the last person to transact on a piece of inventory. A log can also be used for measuring sales data such as counting number of items sold, returned, etc which can be used in operating cost analysis - to take this another step it might be good to study basic accounting and get an understanding of cost of goods calculations.
_________________
Central Coast, NSW, Australia.
|
|
|
|
pathans
Posts: 46
|
| Posted: 10/21/2012, 5:01 PM |
|
sorry its just a basic hardware inventory. where users are updating information and that's it.
I am trying to figure out when a user update a record i want to track that change that x user updated this from this to this. that's it.
|
|
|
|
DataDoIT
|
| Posted: 10/21/2012, 6:58 PM |
|
Create a table:
change_datetime (timestamp)
user_id (int, 11)
module (varchar, 20)
screen (varchar, 50)
db_table (varchar, 50)
primary_key (varchar, 50)
db_field (varchar, 50)
original_value (varchar, 255)
new_value (varchar, 255)
On the record form that you want to log, for each field's Before Show
add the Event 'Save Control Value' and put it into a Session variable
using the field's name.
For the record form's Before Update add Custom Code:
//Fields to check for changes in.
$Fields = array("field_1", "field_2", "field_3");
foreach ($Fields as $FieldName) {
if (CCGetFromPost($FieldName, "") <> CCGetSession($FieldName, "")) {
LogIt($FieldName, CCGetSession($FieldName, ""),
CCGetFromPost($FieldName, ""));
}
}
And finally the LogIt() function:
//LogIt
function LogIt($Field, $Original, $New)
{
//Logs the field change.
global $Connection1;
$change_datetime = date('Y-m-d H:i:s');
$user_id = CCGetUserID();
$module = "My Module";
$screen = "My Screen";
$db_table = "my_table";
$primary_key = CCGetParam("id", "");
$db_field = $Field;
$original_value = $Original;
$new_value = $New;
$SQL = "INSERT INTO log_changes SET "
. "change_datetime=" . CCToSQL($change_datetime, ccsDate)
. ", user_id=" . CCToSQL($user_id, ccsInteger)
. ", module=" . CCToSQL($module, ccsText)
. ", screen=" . CCToSQL($screen, ccsText)
. ", db_table=" . CCToSQL($db_table, ccsText)
. ", primary_key=" . CCToSQL($primary_key, ccsText)
. ", db_field=" . CCToSQL($db_field, ccsText)
. ", original_value='" . addslashes($original_value) . "'"
. ", new_value='" . addslashes($new_value) . "'";
$db = new clsDBConnection1();
$db->query($SQL);
$db->close();
return;
}
//End LogIt
Adjust connection name, field names, table names, etc. as needed.
|
|
|
|
bannedone
Posts: 273
|
| Posted: 10/21/2012, 7:19 PM |
|
Data
R U sure about the INSERT SQL???
I think SET is used for UPDATE...
Should the SQL be something like this???
$SQL=" INSERT INTO Log_Table_Name (list, of, field, names) (the, data, to, insert)";
Let us know
Thanks
_________________
John Real
CodeCharge Studio Support, Training, Consulting, Development, Web based solutions
http://realsites.biz
http://ccselite.com
Other Banned IDs on this Forum. jjrjr1, jjrjr2 |
|
|
|
DataDoIT
|
| Posted: 10/22/2012, 8:17 AM |
|
When using MySQL its beneficial to use INSERT/UPDATE/SET since it'll
allow you to dynamically alter your SQL where needed.
Ex:
if (this) {
$SQL = "INSERT INTO my_table SET ";
$Where = null;
}
else {
$SQL = "UPDATE my_table SET ";
$Where = " field1=$whatever";
}
$SQL .= "field1=$this";
$SQL .= ",field2=$that";
$SQL .= ",field3=$other";
$db->query($SQL . $Where);
Also easier to read and track field definitions and values.
|
|
|
|
bannedone
Posts: 273
|
| Posted: 10/22/2012, 3:19 PM |
|
Thanks Data
I did not know you could do INSERT with the SET statement.
LOL That will sure make life easier after doing it the list way all these years.
Always love to learn something new.
_________________
John Real
CodeCharge Studio Support, Training, Consulting, Development, Web based solutions
http://realsites.biz
http://ccselite.com
Other Banned IDs on this Forum. jjrjr1, jjrjr2 |
|
|
|
pathans
Posts: 46
|
| Posted: 11/12/2012, 11:38 AM |
|
Everything is working except one of the field is actually date field.
its not doing a compare for date field reset is working great.
anything i need to do special for date field?
thanks for your help
|
|
|
|
|
