beforeInsert / Update problem

Suite and Optimus Suite 5
#1

Hi guys,

I have a table populated using a dataprocessor, of which the first column is a coro so I use a OptionsConnector, like this

$GetRatesSQL = “select t1.DefRateID, t1.EntityID, t1.Rate, t1.Hours, t1.TaskID, t2.TaskDesc from calloutrates t1, tasks t2 where EntityID= $_GET[ClientID] and EntityType=‘Client’ and t1.TaskID=t2.TaskID”;
$options = new OptionsConnector($connection);
$options->render_table(“tasks”,“TaskID”,“TaskID(value),TaskDesc(label)”);
$resconn->set_options(“TaskDesc”,$options);
$resconn->render_sql($GetRatesSQL,“DefRateID”,‘TaskDesc,Rate,Hours’);

This all works fine. However I now need to add some new functionality to log changes to this table to an audit table. I am using the events beforeUpdate, beforeInsert and beforeDelete to call another function to log the event, as this

$resconn->event->attach(“beforeUpdate”,“logTaskChange”);
$resconn->event->attach(“beforeDelete”,“logTaskChange”);
$resconn->event->attach(“beforeInsert”,“logTaskChange”);

My problem is, that if the event is an update the label (TaskDesc) from the first column or coro is passed by the dataprocessor which is perfect. But if the event is insert, the value (TaskID) is passed in, which is not what I want. is there any way to resolve this?

heres my log function thats called on the above events

function logTaskChange($action)
{
$Status = $action->get_status();
$TaskDesc = $action->get_value(“TaskDesc”);
$DefRate = $action->get_value(“Rate”);
$Defhours = $action->get_value(“Hours”);

$ClientID = $_GET[ClientID];
$InsertSQL = mysql_query("insert into changelog (action, area, entityid, details, user) VALUES ('$Status', 'client', '$ClientID', '$TaskDesc rate = [$DefRate], Default Hours = [$Defhours]', '$_SESSION[username]')");
return true;

}

thanks

#2

You are using coro column in not expected way.
Normally it must be

$GetRatesSQL = "select t1.DefRateID, t1.EntityID, t1.Rate, t1.Hours, t1.TaskID from calloutrates t1 where EntityID= $_GET[ClientID] and EntityType='Client'"; ... $resconn->render_sql($GetRatesSQL,"DefRateID",'TaskID,Rate,Hours');

So you will provide only IDs of columns and they will be linked with labels on client side by grid.

If you need not TaskID for all client side operations, you can use original solution , and

$options->render_table("tasks","TaskID","TaskDesc(value),TaskDesc(label)");

In such case, client side code will not know about TaskID at all , and will provide TaskDesc for all operatons.

#3

Hi Stanislav,

I see the error in the original code so changed it as advised. Now in all cases the task id is returned to the server. However to log the action taken by the user I need to create an entry in an audit table using the TaskDesc and not the TaskID, so I have the following log functions,

function getTaskName($taskID)
{
	$GetTaskDesc = "select TaskDesc from tasks where TaskID=$taskID";
	$result = mysql_query($GetTaskDesc,$connection) or die(mysql_error());
			
	while ($row = mysql_fetch_array($result)) 
	{
		$taskname = $row['TaskDesc'];
	}
	
	return $taskname;
}

function logTaskChange($action)
{
	$Status = $action->get_status();

        $DefRate = $action->get_value("Rate");
        $Defhours = $action->get_value("Hours");
	$TaskID = $action->get_value("TaskID");
	
	$TaskDesc = getTaskName($TaskID);

	$ClientID = $_GET[ClientID];
	$InsertSQL = mysql_query("insert into changelog (action, area, entityid, details, user) VALUES ('$Status', 'client', '$ClientID', '$TaskDesc rate = [$DefRate], Default Hours = [$Defhours]', '$_SESSION[username]')");
	return true;
}

however the insert/update fails, and I get the following message in the dataprocessor debug window,

Sending all data at once
Server url: clientdetail.php?editing=true&action=clienttasks&ClientID=23&uid=1271269169645 parameters

3096_gr_id=3096
3096_c0=27
3096_c1=33
3096_c2=8
3096_!nativeeditor_status=updated
ids=3096

Server response received details

Not an XML, probably incorrect content type specified ( must be text/xml ), or some text output was started before XML data

This only happens when I try to look up the task desc from the task id,

THe changes to the code to poulate the grid was

	$GetRatesSQL = "select t1.DefRateID, t1.EntityID, t1.Rate, t1.Hours, t1.TaskID from calloutrates t1 where EntityID= $_GET[ClientID] and EntityType='Client'";
	
		$resconn->event->attach("beforeUpdate","logTaskChange");
		$resconn->event->attach("beforeDelete","logTaskChange");
		$resconn->event->attach("beforeInsert","logTaskChange");
	
		$resconn->sql->attach("Delete", "Delete from calloutrates WHERE DefRateID={DefRateID}");
		$resconn->sql->attach("Insert", "Insert into calloutrates (`EntityType`, `EntityID`, `TaskID`, `Rate`, `Hours`) VALUES ('Client', '$_GET[ClientID]', '{TaskDesc}', '{Rate}', '{Hours}')");
		
		$options = new OptionsConnector($connection);
		$options->render_table("tasks","TaskID","TaskID(value),TaskDesc(label)");
		$resconn->set_options("TaskID",$options);
		
		$resconn->render_sql($GetRatesSQL,"DefRateID",'TaskID,Rate,Hours');
#4

Probably some error occurs during custom sql call, you can add the next line

$resconn->enable_log(“some.log”);
$resconn->render_sql($GetRatesSQL,“DefRateID”,‘TaskID,Rate,Hours’);

to enable server side error logging. Log file will contain more detailed error info.

#5

Next code can be used to store extra data ( text value of combo cell , in your case ) and send it to the server side.

[code]dp.attachEvent(“onRowMark”,function(id,state,mode){ //for each update
if (state == true && (mode == “inserted” || mode == “updated”)){
mygrid.setUserData(id,“description”, mygrid.cells(id, INDEX).getTitle());
}
return true;
})

//on server side
$action->get_value(“description”);[/code]

INDEX - index of column in question

#6

Thanks Stanislav, that works perfectly.