Problem with workflow

[fbrites]

Hi all,

I'm exploring the workflow in SugarCRM CE but I'm with some problems.

I'll describe what I did:

1) Create field 'Checkbox' for Opportunities module.

2) Create table 'my_workflow' in database:

2.1) create table my_workflow
(
id char(36),
user_id char(36),
process_step varchar(50),
predecessor varchar(50),
primary key (id)
);

3) Create file's module:
3.1) include/modules.php:

 $moduleList[] = 'my_workflow' 
  

3.2) modules/my_workflow/index.php

 <?php
if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry
Point');
 
$link = mysql_connect("localhost", "root", "");
mysql_select_db("workflow", $link);
 
if ($_REQUEST['assigned_user_id'])
{
$sql = "insert into my_workflow (id, user_id,
process_step,predecessor)";
$sql .= " values ";
$sql .= "('". create_guid() ."'";
$sql .= ",'".$_REQUEST['assigned_user_id']."'";
$sql .= ",'".$_REQUEST['prelim_stage']."'";
$sql .= ",'".$_REQUEST['predecessor']."')";
$result = mysql_query($sql, $link);
}
?>
 
<table width=100%>
<tr><td><b>Assigned User</b></td><td><b>Process step</b></td><td><b>Predecessor</b></td></tr>
<?php
 
$sql = "select u.user_name, w.process_step,w.predecessor";
$sql .= " from users u,my_workflow w";
$sql .= " where u.id = w.user_id";
$result = mysql_query($sql, $link);
$r=0;
while ($r < mysql_num_rows($result)){
    $user_name = mysql_result($result,$r,0);
    $ppi_stage = $app_list_strings['sales_stage_dom'][mysql_result($result,$r,1)];
    $predecessor = $app_list_strings['sales_stage_dom'][mysql_result($result,$r,2)];
    $op = "<tr>";
    $op .= "<td>" . $user_name . "</td>";
    $op .= "<td>" . $ppi_stage . "</td>";
    $op .= "<td>" . $predecessor . "</td>";
    $op .= "</tr>";
    echo $op;
    $r++;
}
?>
</table>
<hr>
<form action="index.php?module=my_workflow&action=index" method=post>
<table>
<tr>
<td>Assigned User</td>
<td>
<!-- Build the combo for the assigned user -->
 
<?php
$sql="select id,user_name from users where id <> '1'";
$sql .= " and deleted = 0 and status = 'Active'";
//$result = $db->query($sql);
$result = mysql_query($sql, $link);
$r=0;
 
?>
 
<select name='assigned_user_id'>
<?php
 
while ($r < mysql_num_rows($result))
{
$op = "<option value='";
$op .= mysql_result($result,$r,0) . "'>";
$op .= mysql_result($result,$r,1);
echo $op;
$r++;
}
?>
</select>
</td>
 
<td>Preliminary Investigation Stage</td>
<td>
<select name='prelim_stage'>
<?php
foreach ($app_list_strings['sales_stage_dom'] as $key => $value)
{
echo "<option value='" . $key . "'>$value";
}
?>
</select>
</td>
<td>Predecessor</td>
<td>
<select name='predecessor'>
<option value=NONE>None
<?php
foreach ($app_list_strings['sales_stage_dom'] as $key => $value)
{
echo "<option value='" . $key . "'>$value";
}
?>
</select>
</td>
</tr>
<tr><td colspan=4 align=center><input type=submit value="Save"></td></tr>
</form> 

4) Create dependent tasks to the database:

create table my_workflow_tasks (
id char(36),
user_id char(36),
process_step varchar(50),
title varchar(50),
primary key (id) );

5) Create file 'my_workflow.php': custom\include\my_workflow.php

if(!defined('sugarEntry') || !sugarEntry) die(
'Not A Valid Entry Point');
require_once('data/SugarBean.php');
require_once('modules/Opportunities/Opportunity.php');
class my_workflow {
function my_workflow (&$bean, $event, $arguments) {
GLOBAL $db;
$link = mysql_connect("localhost", "root", "");
mysql_select_db("workflow", $link);
 
 
if ( $bean->chk_complete_c == 1 ) {
 
$sql = "select * from my_workflow_tasks";
$sql .= " where process_step = '" . $bean->sales_stage . "'";
$result = mysql_query($sql, $link);
if (mysql_numrows($result) > 0) {
 
$sql = "select id from tasks";
$sql .= " where parent_id = '" . $bean->id . "'";
$sql .= " and status <> 'Completed'";
$status_result = $db->query($sql);
 
if (mysql_numrows($status_result) > 0)
{
$tasks_outstanding = 1;
}
}
 
if (! $tasks_outstanding)
{
$sql = "select user_id, process_step from my_workflow";
$sql .= " where predecessor = '" . $bean->sales_stage . "'";
$result = mysql_query($sql, $link);
$bean->assigned_user_id = mysql_result($result,0,0);
$bean->sales_stage = mysql_result($result,0,1);
 
$sql = "select user_id,title from my_workflow_tasks";
$sql .= " where process_step = '" . $bean->sales_stage . "'";
$result = mysql_query($sql, $link);
$r=0;
while ($r < mysql_numrows($result))
{
$sql = "insert into tasks";
$sql .= "(id,parent_id,date_entered,date_modified";
$sql .= ",assigned_user_id,name,status,parent_type)";
$sql .= " values ";
$sql .= "(";
$sql .= "'" . create_guid() . "'";
$sql .= ",'" . $bean->id . "'";
$sql .= ",now(),now()";
$sql .= ",'" . mysql_result($result,$r,0) . "'";
$sql .= ",'" . mysql_result($result,$r,1) . "'";
$sql .= ",'Not Started'";
$sql .= ",'Opportunities'";
$sql .= ")";
$result = mysql_query($sql, $link);
$r++;
}
}
$bean->chk_complete_c = 0;
}
}
}
<?php  

6) Create file modules/my_workflow/my_workflow_tasks.php:

 <?php
if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');
$step='Needs Analysis';
$task['title'][0] = 'Subject Interviews';
$task['title'][1] = 'Witness Interviews';
$task['username'][0] = 'monkw';
$task['username'][1] = 'pittc';
for ( $c = 0; $c <= 1; $c++ )
{
$sql = "select id from users";
$sql .= " where user_name = '" . $task['username'][$c] . "'";
$result = $db->query($sql);
$user_id = mysql_result($result,0,0);
$sql = "insert into my_workflow_tasks";
$sql .= "(id, user_id, process_step, title)";
$sql .= " values ";
$sql .= "('". create_guid() ."'";
$sql .= ",'". $user_id ."'";
$sql .= ",'". $step ."'";
$sql .= ",'". $task['title'][$c] ."')";
$db->query($sql);
}
?>  

7) Modify 'lang.us.php' file:

 $app_list_strings['sales_stage_dom']=array (
'Prospecting' => 'Fact Gathering',
'Qualification' => 'Witness and Subject Location',
'Needs Analysis' => 'Witness and Subject Interviews',
'Value Proposition' => 'Scene Investigation',
'Id. Decision Makers' => 'Financial and background Investigation',
'Perception Analysis' => 'Document and evidence retrieval',
'Proposal/Price Quote' => 'Covert Camera surveillance',
'Negotiation/Review' => 'Wiretapping',
'Closed Won' => 'Full Investigation required',
'Closed Lost' => 'Insufficient Evidence',
 
$mod_strings['lbl_chk_complete_c_10'] = "Investigation Stage Completed";
 
$app_list_strings['moduleList']['my_workflow'] = 'My Workflow'; 
  

I want to:
- The tasks are created automatically and assigned to the correct person
- The stage cannot be completed until all tasks have been closed

The module 'My_Workflow' works well (see picture http://www.dibconsulting.com/sites/images/workflow1.jpg ) but the process stage workflow in Oportunities module does not work. The depedent tasks also not work.

What am I doing wrong?

Someone can help me?

Thanks