rado
Posts: 221
|
| Posted: 05/21/2009, 6:16 PM |
|
HI,
I spend full day traying to figure out why my suctom script doesn't work with no success, so please help me. Please, please please...
I have grid and update record for task management . The grid is showing current state of single task and on the same page there is an update record that has 4 listbox where user can change the status of : task type, task status, task prority and ownership of task. Everything works fine when I change the value of one of the listbox the grid is updated right away, and all changes are updated in DB. So now, I added for update record in "On validate" event "Save control Value" action for every 4 controls and save them into variable. After that I added in "After Update" event for update record the custom script which suppose to select data from db and compare them with the current value of controls (previously saved with "Save control Value" in "On Validate" event , then if there is difference between current value of control and value from database (which should be changed after db update) I'm sending email to person who was re-assigned for the task ("tasks.user_id_assign_to"):
Here is the custom code that makes me big headache whole day today:
//Query required Data
global $db;
$db = new clsDBrr_residence();
$SQL = "SELECT users.user_email as Email, ".
"tasks.task_name as TaskName, ".
"tasks.type_id as TaskTypeIdNew, ".
"tasks.status_id as TaskStatusIdNew, ".
"tasks.priority_id as TaskPriorityIdNew, ".
"tasks.user_id_assign_to as TaskUidAssignToNew, ".
"task_types.type_name as TaskTypeNameNew, ".
"task_statuses.status_name as TaskStatusNameNew, ".
"task_priorities.priority_name as TaskPriorityNameNew, ".
"users.user_full_name as FullName ".
"FROM tasks ".
"LEFT JOIN users ON (tasks.user_id_assign_to = users.user_id),".
"LEFT JOIN task_types ON (tasks.type_id = task_types.type_id), ".
"LEFT JOIN task_statuses ON (tasks.status_id = task_statuses.status_id), ".
"LEFT JOIN task_priorities ON (tasks.priority_id = task_priorities.priority_id) ".
"WHERE task_id = " . $db->ToSQL($task_id_var, ccsInteger);
$db->query($SQL);
// Set varibles value
if($db->next_record()) {
$user_first_last_name = $db->f("FullName");
$user_email = $db->f("Email");
$Task_Uid_Assign_To_New = $db->f("TaskUidAssignToNew");
$Task_Type_Name_New = $db->f("TaskTypeNameNew");
$Task_Type_Id_New = $db->f("TaskTypeIdNew");
$Task_Status_Name_New = $db->f("TaskStatusNameNew");
$Task_Status_Id_New = $db->f("TaskStatusIdNew");
$Task_Priority_Id_New = $db->f("TaskPriorityIdNew");
$Task_Priority_Name_New = $db->f("TaskPriorityNameNew");
$Task_Name = $db->f("TaskName");
$UserText = "The data on Task #:". $task_id_var ." has been changed";
$UserBody = "";
if (!(($task_type_id_var_curr = $Task_Type_Id_New) AND
($task_status_id_var_curr = $Task_Status_Id_New) AND
($task_priority_id_var_curr = $Task_Priority_Id_New) AND
($transfer_task_to_user_id_var_curr = $Task_Uid_Assign_To_New))) {
if (!($task_type_id_var_curr = $Task_Type_Id_New)) {
$UserBody = $UserBody."<p>Task type has been changed to: ". $Task_Type_Name_New ."</p>";
}
if (!($task_status_id_var_curr = $Task_Status_Id_New)) {
$UserBody = $UserBody."<p>Task status has been changed to: ". $Task_Status_Name_New ."</p>";
}
if (!($task_priority_id_var_curr = $Task_Priority_Id_New)) {
$UserBody = $UserBody."<p>Task priority has been changed to: ". $Task_Priority_Name_New ."</p>";
}
if (!($transfer_task_to_user_id_var_curr = $Task_Uid_Assign_To_New)) {
$UserBody = $UserBody."<p>Task has been transfered to: ". $user_first_last_name ."</p>";
}
//send the email
$FromAddress = "webadmin@kw4rent.com" ; // my email FROM here
$Additional_Headers="From: $FromAddress\r\nReply-To: $FromAddress\r\nContent-Type: text/html";
$UserSubject = "The status of Task #:". $task_id_var ."";
$Subject = $UserSubject;
$Message = $UserBody;
$result=mail ($user_email, $Subject, $Message, $Additional_Headers);
}
// ------------------------- //End Custom Code
When I run update record, change the listbox, and hit update,I'm getting error:
"Warning: mail() [function.mail]: SMTP server response: 503 5.5.2 Need Rcpt command. in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\kw4rent_intranet\TaskUpdate_events.php on line 341"
The line 341 is:
$result=mail ($user_email, $Subject, $Message, $Additional_Headers);
Please help good people:)
Rado
|
 |
 |
peterr
Posts: 5971
|
| Posted: 05/21/2009, 10:41 PM |
|
Based on the debugging section of CCS documentation try checking all the values you're using when sending an email. Use the echo command before the $result = mail.
echo "user_email:" . $user_email . "<br>";
echo "Subject:" . $Subject . "<br>";
echo "Message:" . $Message . "<br>";
echo "Additional_Headers:" . $Additional_Headers . "<br>";
exit;
//$result=mail ($user_email, $Subject, $Message, $Additional_Headers);
Also see: http://www.google.com/search?q=PHP+mail+%22Need+Rcpt+command%22
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
Gena
Posts: 591
|
| Posted: 05/21/2009, 11:18 PM |
|
as Peter said.
+ you have defined $UserText but have never used it. check it too
_________________
Gena |
|
|
|
rado
Posts: 221
|
| Posted: 05/22/2009, 3:49 AM |
|
Thanks Peter and Gena,
When I replace the "user_email" variable with the real email in "$result=mail ($user_email, $Subject, $Message, $Additional_Headers);" line I got email but all required variables are empty, so definitively is problem in SQL query but I don't know where
Rado
|
|
|
|
rado
Posts: 221
|
| Posted: 05/22/2009, 4:21 AM |
|
I followed Peter's direction for debugging and nothing come up???
Rado
|
|
|
|
Gena
Posts: 591
|
| Posted: 05/22/2009, 4:57 AM |
|
what you mean "nothing come up"?
you should see some output like
user_email:
Subject:
Message:
Additional_Headers:
But it seems that your SQL is wrong because
if($db->next_record()) {
doesn't work (and you have echos inside that IF )
put echo like
echo $SQL;
and see your sql query. your can check if it look good, even better to copy it and paste in your favorite Query viewer,run and see if it works.
_________________
Gena |
|
|
|
Gena
Posts: 591
|
| Posted: 05/22/2009, 5:00 AM |
|
also where you inti
$task_id_var
variable??
also why you use
global $db;
you don't need.
_________________
Gena |
|
|
|
rado
Posts: 221
|
| Posted: 05/22/2009, 5:28 AM |
|
This is what I got (no data):
==============================
user_email:
Subject:
Message:
Additional_Headers:
==============================
I saved all controls values (including "task_id_var) in "On Validate" of the update record and Custom code is set in "After Update" event.
Thanks a lot
Rado
|
|
|
|
Gena
Posts: 591
|
| Posted: 05/22/2009, 5:45 AM |
|
so check your SQL and variables visibility
_________________
Gena |
|
|
|
rado
Posts: 221
|
| Posted: 05/22/2009, 6:00 AM |
|
Sorry Gena
I don't understand what you mean with "visibility"... However one additional thing that I did is try to display the values of the saved controls and gess what? They are empty:
========================================================================
echo "task type id:" .$task_type_id_var_curr . "<br>";
echo "task id:" .$task_id_var . "<br>";
echo "task status id:" .$task_status_id_var_curr . "<br>";
echo "taskk priority id:" .$task_priority_id_var_curr . "<br>";
echo "task user assign to id:" .$transfer_task_to_user_id_var_curr . "<br>";
========================================================================
task type id:
task id:
task status id:
taskk priority id:
task user assign to id:
Now I'm not sure is it correct place to save control values on "On validate" event for record?
Rado
|
|
|
|
Gena
Posts: 591
|
| Posted: 05/22/2009, 6:09 AM |
|
"visibility"... is if you can "see" and use your variables across your php code. In CCS each events is separated function. So if you create and use some variable in one event it doesn't mean that you can "see" and use the same variable in other event. Because for each events (functions) your variables are local. To see it accross you need to use
global $your_vars;
or move all your codes in one appropriated event.
_________________
Gena |
|
|
|
rado
Posts: 221
|
| Posted: 05/22/2009, 6:17 AM |
|
Sorry Gena. Too many questions..
Where should I put "Save control value" (currently in "On validate) to be visible across my php code. How I can make them "global"?
Thanks,
Rado
|
|
|
|
rado
Posts: 221
|
| Posted: 05/22/2009, 6:49 AM |
|
I just check all "Saved Control" values are global
=============================================================================
//Save Control Value @168-85A7E2A9
global $task_id_var;
$task_id_var = $Container->task_id_1->GetValue();
//End Save Control Value
//Save Control Value @164-5740E7D8
global $task_type_id_var_curr;
$task_type_id_var_curr = $Container->task_type->GetValue();
//End Save Control Value
//Save Control Value @165-FECE3477
global $task_status_id_var_curr;
$task_status_id_var_curr = $Container->status_id->GetValue();
//End Save Control Value
//Save Control Value @166-FD96E95A
global $task_priority_id_var_curr;
$task_priority_id_var_curr = $Container->priority_id->GetValue();
//End Save Control Value
//Save Control Value @167-93E54B9C
global $transfer_task_to_user_id_var_curr;
$transfer_task_to_user_id_var_curr = $Container->transfer_task->GetValue();
//End Save Control Value
===================================================================
It's only question is the place where I saved them ("On validate") ok. Maybe another event???
|
|
|
|
melvyn
Posts: 333
|
| Posted: 05/22/2009, 2:52 PM |
|
Rado, I'm sure your first issue is with the
"WHERE task_id = " . $db->ToSQL($task_id_var, ccsInteger);
I would try doing this now before modify:
"WHERE task_id = " . $db->ToSQL($task_id_var, ccsInteger);
$db->query($SQL);
echo "task id var = " . $task_id_var;
echo $SQL;
exit(0);
$task_id_var has no value (as Gena told you). You'll see that in the echo.
If $task_id_var has a value now, then ignore the following the report here.
Now, let's theorize:
If you have
function a(){
$var = 1
}
And need that value in side function b:
function b(){
global $var;
// code here
}
The point is: the var is globalized in the function which is calling the var, in this case you must use global $task_id_var; in the AfterUpdate code:
//Query required Data
//global $db; // Delete this. You don't need it to be global, and you're defining it in the next line.
global $task_id_var;
$db = new clsDBrr_residence();
$SQL = ...
Try. Put an eye on the echo. I'll interrupt after inserting, before sending the mail.
_________________
Melvyn Perez
Puro Codigo
http://purocodigo.com |
|
|
|
rado
Posts: 221
|
| Posted: 05/23/2009, 4:49 AM |
|
This is what I did:
I set the following custom code into "On Validate" event for the update record:
//Custom Code @169-2A29BDB7 // -------------------------
global $task_id_var;
$task_id_var = $Container->task_id->GetValue();
global $task_type_id_var_curr;
$task_type_id_var_curr = $Container->task_type->GetValue();
global $task_priority_id_var_curr;
$task_priority_id_var_curr = $Container->priority_id->GetValue();
global $task_status_id_var_curr;
$task_status_id_var_curr = $Container->status_id->GetValue();
global $transfer_task_to_user_id_var_curr;
$transfer_task_to_user_id_var_curr = $Container->transfer_task->GetValue();
echo "CONTROLS VALUES BEFORE BUTTON UPDATE IS CLICKED: " ."<br>";
echo "task type id: " .$task_type_id_var_curr . "<br>";
echo "task id: " .$task_id_var . "<br>";
echo "task status id: " .$task_status_id_var_curr . "<br>";
echo "task priority id: " .$task_priority_id_var_curr . "<br>";
echo "task user assign to id: " .$transfer_task_to_user_id_var_curr . "<br>";
//Query required Data
$db = new clsDBrr_residence();
$SQL = "SELECT users.user_email as Email, ".
"tasks.task_name as TaskName, ".
"tasks.type_id as TaskTypeIdNew, ".
"tasks.status_id as TaskStatusIdNew, ".
"tasks.priority_id as TaskPriorityIdNew, ".
"tasks.user_id_assign_to as TaskUidAssignToNew, ".
"task_types.type_name as TaskTypeNameNew, ".
"task_statuses.status_name as TaskStatusNameNew, ".
"task_priorities.priority_name as TaskPriorityNameNew, ".
"users.user_full_name as FullName ".
"FROM tasks ".
"INNER JOIN users ON (tasks.user_id_assign_to = users.user_id), ".
"INNER JOIN task_types ON (tasks.type_id = task_types.type_id), ".
"INNER JOIN task_statuses ON (tasks.status_id = task_statuses.status_id), ".
"INNER JOIN task_priorities ON (tasks.priority_id = task_priorities.priority_id) ".
"WHERE task_id = " . $db->ToSQL($task_id_var, ccsInteger);
$db->query($SQL);
// Set varibles value
$user_first_last_name = $db->f("FullName");
$user_email = $db->f("Email");
$Task_Uid_Assign_To_New = $db->f("TaskUidAssignToNew");
$Task_Type_Name_New = $db->f("TaskTypeNameNew");
$Task_Type_Id_New = $db->f("TaskTypeIdNew");
$Task_Status_Name_New = $db->f("TaskStatusNameNew");
$Task_Status_Id_New = $db->f("TaskStatusIdNew");
$Task_Priority_Id_New = $db->f("TaskPriorityIdNew");
$Task_Priority_Name_New = $db->f("TaskPriorityNameNew");
$Task_Name = $db->f("TaskName");
echo "" ."<br>";
echo "CONTROLS VALUES AFTER BUTTON UPDATE IS CLICKED:" ."<br>";
echo "task type id :" .$Task_Type_Id_New . "<br>";
echo "task id :" .$task_id_var . "<br>";
echo "task status id :" .$Task_Status_Id_New . "<br>";
echo "task priority id :" .$Task_Priority_Id_New . "<br>";
echo "task user assign to id :" .Task_Uid_Assign_To_New . "<br>";
exit;
My output was (after I change one control value):
============================================================
CONTROLS VALUES BEFORE BUTTON UPDATE IS CLICKED:
task type id: 2
task id: 1
task status id: 4
task priority id: 2
task user assign to id: 6
CONTROLS VALUES AFTER BUTTON UPDATE IS CLICKED:
task type id :
task id :1
task status id :
task priority id :
task user assign to id :Task_Uid_Assign_To_New
=======================================================
So, here are two problems:
The data that I got in "CONTROLS VALUES BEFORE BUTTON UPDATE IS CLICKED: " group
where data after the "Update button" was clicked instead before. (So I have to find out where is the
appropriate event where I can set the values of controls that where before any change in update
record)
Even if I have $task_id_var available my query didn't return any data (Listed in "CONTROLS
VALUES AFTER BUTTON UPDATE IS CLICKED" group.
|
|
|
|
melvyn
Posts: 333
|
| Posted: 05/23/2009, 8:28 AM |
|
In my previous post I also recommended echo $SQL.
"WHERE task_id = " . $db->ToSQL($task_id_var, ccsInteger);
$db->query($SQL);
echo $SQL;
exit(0);
When you get that SQL in your browser you'll see which vars are empty, also you can copy the entire SQL and run it in your MySQL client (phpmyadmin, navicat, heidisql, sqlyog or whatever you use) in order to check what's going wrong.
Go and echo the $SQL and post it here.
_________________
Melvyn Perez
Puro Codigo
http://purocodigo.com |
|
|
|
rado
Posts: 221
|
| Posted: 05/23/2009, 8:42 AM |
|
This is what I got:
===============================================================================
CONTROLS VALUES BEFORE BUTTON UPDATE IS CLICKED:
task type id: 2
task id: 21
task status id:
task priority id: 1
task user assign to id: 6
ECHO SQL:
task id var = 21
SELECT users.user_email as Email,
tasks.task_name as TaskName,
tasks.type_id as TaskTypeIdNew,
tasks.status_id as TaskStatusIdNew,
tasks.priority_id as TaskPriorityIdNew,
tasks.user_id_assign_to as TaskUidAssignToNew,
task_types.type_name as TaskTypeNameNew,
task_statuses.status_name as TaskStatusNameNew,
task_priorities.priority_name as TaskPriorityNameNew,
users.user_full_name as FullName
FROM tasks
INNER JOIN users ON (tasks.user_id_assign_to = users.user_id),
INNER JOIN task_types ON (tasks.type_id = task_types.type_id),
INNER JOIN task_statuses ON (tasks.status_id = task_statuses.status_id),
INNER JOIN task_priorities ON (tasks.priority_id = task_priorities.priority_id)
WHERE task_id = 21
============================================================================
Thanks a lot Melvyn
|
|
|
|
melvyn
Posts: 333
|
| Posted: 05/23/2009, 8:45 AM |
|
Try to run on your mysql client, I guess nothing will be in the output.
Are you sure you're using inner joins? Change those INNER JOIN by RIGHT JOIN
_________________
Melvyn Perez
Puro Codigo
http://purocodigo.com |
|
|
|
rado
Posts: 221
|
| Posted: 05/23/2009, 8:48 AM |
|
When I run statement in sql client this is what I get:
===============================================================================
SQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN task_types ON (tasks.type_id = task_types.type_id),
INNER JOIN task_' at line 13
============================================================================
My line 13 is
INNER JOIN task_types ON (tasks.type_id = task_types.type_id),
|
|
|
|
rado
Posts: 221
|
| Posted: 05/23/2009, 8:56 AM |
|
This is what I get:
==============================================================================
SQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT JOIN task_types ON (tasks.type_id = task_types.type_id),
INNER JOIN task_s' at line 13
==============================================================================
My line 13 is:
INNER JOIN task_types ON (tasks.type_id = task_types.type_id),
I tried to change everything to RIGHT JOIN and even LEFT JOIN, but same error
Rado
|
|
|
|
melvyn
Posts: 333
|
| Posted: 05/23/2009, 9:40 AM |
|
This is not related to the join. Well, there isn't any field named task_id, you must refer to it as task.task_id.
Regarding to the join, isn't copied exactly. The error reports: INNER JOIN task_s' at line 13
Your sql doesn't show anything starting with task_s (note the _s) in the supossed line 13 other than statuses.
Again: use RIGHT JOIN on all them and test.
_________________
Melvyn Perez
Puro Codigo
http://purocodigo.com |
|
|
|
rado
Posts: 221
|
| Posted: 05/23/2009, 10:23 AM |
|
Now I re-typed line 13 and got:
SQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RIGHT JOIN task_types ON (tasks.type_id = task_types.type_id),
RIGHT JOIN task_' at line 13
|
|
|
|
rado
Posts: 221
|
| Posted: 05/23/2009, 11:10 AM |
|
This is the statement that finally returned data:
SELECT users.user_email AS Email,
tasks.user_id_assign_to AS TaskUidAssignToNew,
tasks.type_id AS TaskTypeIdNew,
task_types.type_name AS TaskTypeNameNew,
tasks.task_name AS TaskName,
tasks.status_id AS TaskStatusIdNew,
task_statuses.status_name AS TaskStatusNameNew,
tasks.priority_id AS TaskPriorityIdNew,
task_priorities.priority_name AS TaskPriorityNameNew
FROM ((((tasks LEFT JOIN users ON tasks.user_id_assign_to = users.user_id)
LEFT JOIN task_types ON tasks.type_id = task_types.type_id)
LEFT JOIN task_statuses ON tasks.status_id = task_statuses.status_id)
LEFT JOIN task_priorities ON tasks.priority_id = task_priorities.priority_id)
WHERE tasks.task_id = 21
Rado
|
|
|
|
rado
Posts: 221
|
| Posted: 05/23/2009, 11:42 AM |
|
But custom code with same statement still doesn't return data.
Any idea??
Rado
$db = new clsDBrr_residence();
$SQL = "SELECT users.user_email AS Email, ".
"tasks.user_id_assign_to AS TaskUidAssignToNew, ".
"tasks.type_id AS TaskTypeIdNew, ".
"task_types.type_name AS TaskTypeNameNew, ".
"tasks.task_name AS TaskName, ".
"tasks.status_id AS TaskStatusIdNew, ".
"task_statuses.status_name AS TaskStatusNameNew, ".
"tasks.priority_id AS TaskPriorityIdNew, ".
"task_priorities.priority_name AS TaskPriorityNameNew ".
"FROM ((((tasks ".
"LEFT JOIN users ON tasks.user_id_assign_to = users.user_id) ".
"LEFT JOIN task_types ON tasks.type_id = task_types.type_id) ".
"LEFT JOIN task_statuses ON tasks.status_id = task_statuses.status_id) ".
"LEFT JOIN task_priorities ON tasks.priority_id = task_priorities.priority_id) ".
"WHERE tasks.task_id = " . $db->ToSQL($task_id_var, ccsInteger);
$db->query($SQL);
Thanks
|
|
|
|
rado
Posts: 221
|
| Posted: 05/23/2009, 11:55 AM |
|
When I copy and paste the results from "echo $SQL" into mysql client again I'm getting correct data.
Ohhhh where is the problem 
Rado
|
|
|
|
rado
Posts: 221
|
| Posted: 05/25/2009, 9:07 PM |
|
Is there anybody who would like to give me just hint where could be the problem.
Rado
|
|
|
|
|
