meeting error vcals after upgrade to 5.1.0

[Systems Navigator]

Hello,

After I updated Sugar to version 5.1.0 I got an error in the meetings module. When I schedule a meeting and I click save or delete or send invites I get an error.
This is the error

Query Failed:Update vcals set WHERE ID = '7589bb9d-6304-c651-1695-46ee8a744bbb'::MySQL error 1064: 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 'WHERE ID = '7589bb9d-6304-c651-1695-46ee8a744bbb'' at line 1

The ID of the vcal is the ID which corresponds with my user_id and the source Sugar. But other users get the same error with their user_id.

I also have another problem with the meeting module, which maybe deals with same problem. When I search on invites for a meeting the search function does not work. It works only when I don't fill in anything in the first or last name field and search blank. Another complication comes in here, because with one user when I invite him, he will not be added in the users subpanel. Other users will.

Does anybody have a clue how to solve this.

Regards,

OS: Windows 2003 Server R2
Sugar: Version 5.1.0 Pro
Webserver: apache 2.0.59
PHP: Version 5.2.5
Mysql: version 5.0.45

[privasphere]

I have a similar problem

I updated Version 4.5.1e to 5.1.0 and to 5.2.0

while saving an activity or calendar entry I get

Query Failed:Update vcals set WHERE ID = '1891bfe8-0651-531f-3528-45a6c4abf0f7'::MySQL error 1064: 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 'WHERE ID = '1891bfe8-0651-531f-3528-45a6c4abf0f7'' at line 1

can anybody help me?

I run:
linux
PHP Version 5.2.0-8+etch13
MySQL Version: 5.0.32
MySQL Server Info: 5.0.32

[clint]

I've created bug 27577 to track this and will have an engineer look into it.

[rzhou]

Hi,

Can you please go to the modules/vCals directory and open vardefs.php file? Do you see $dictionary['vCal'] or $dictionary['vcal']? Also for vCal.php file, do you see var $object_name = "vCal" or var $object_name = "vcal"?

If you still see vcal, that means the upgrade did not copy these two files. There might be some error in the upgradeWizard.log file. Open up this log file, and "Copying file to destination" should be there for the two files above.

Thanks.

[privasphere]

in vardefs.php I see:

***********
// Create the indexes
$dictionary['vCal'] = array('table' => 'vcals'
,'fields' => array (
'id' =>
array (
'name' => 'id',
'vname' => 'LBL_NAME',
'type' => 'id',
'required'=>true,
'reportable'=>false,
),
'deleted' =>
array (
'name' => 'deleted',
'vname' => 'LBL_DELETED',
'type' => 'bool',
'required' => true,
'reportable'=>false,
),
'date_entered' =>
array (
'name' => 'date_entered',
'vname' => 'LBL_DATE_ENTERED',
'type' => 'datetime',
),
'date_modified' =>
array (
'name' => 'date_modified',
'vname' => 'LBL_DATE_MODIFIED',
'type' => 'datetime',
),
'user_id' =>
array (
'name' => 'user_id',
'type' => 'id',
'required'=>true,
'reportable'=>false,
),
'type' =>

[...............]

$focus->source = 'sugar';
$focus->user_id = $user_focus->id;
$focus->save();
}


}

?>


**********


Thu, 15 Jan 2009 16:33:20 +0100 [UpgradeWizard] - Copying file to destination: /var/www/SugarOS-Full-4.5.1e/modules/vCals/vardefs.php

Thu, 15 Jan 2009 17:08:11 +0100 [UpgradeWizard] - unlinking [/var/www/SugarOS-Full-4.5.1e/cache/upload/upgrades/temp/NHqQBV/SugarCE-Upgrade-4.5.1-to-5.1.0c/modules/vCals/vardefs.php]

Thu, 15 Jan 2009 17:08:12 +0100 [UpgradeWizard] - unlinking [/var/www/SugarOS-Full-4.5.1e/cache/upload/upgrades/temp/4Vsulg/SugarCE-Upgrade-4.5.1-to-5.1.0c/modules/vCals/vardefs.php]

Thu, 15 Jan 2009 17:08:12 +0100 [UpgradeWizard] - unlinking [/var/www/SugarOS-Full-4.5.1e/cache/upload/upgrades/temp/4Vsulg/SugarCE-Upgrade-4.5.1-to-5.1.0c/modules/vCals/vCal.php]

Fri, 30 Jan 2009 15:38:33 +0100 [UpgradeWizard] - unlinking [/var/www/SugarOS-Full-4.5.1e/cache/upload/upgrades/temp/mYKX9K/patch/modules/vCals/vCal.php]

Fri, 30 Jan 2009 15:38:33 +0100 [UpgradeWizard] - unlinking [/var/www/SugarOS-Full-4.5.1e/cache/upload/upgrades/temp/JeBmJF/patch/modules/vCals/vCal.php]

Fri, 30 Jan 2009 15:38:33 +0100 [UpgradeWizard] - unlinking [/var/www/SugarOS-Full-4.5.1e/cache/upload/upgrades/temp/IJMexc/patch/modules/vCals/vCal.php]

Fri, 30 Jan 2009 15:38:33 +0100 [UpgradeWizard] - unlinking [/var/www/SugarOS-Full-4.5.1e/cache/upload/upgrades/temp/6SFycx/patch/modules/vCals/vCal.php]

Mon, 05 Feb 2007 10:34:03 +0100 [UpgradeWizard] - unlinking [/opt/sugarcrm-4.5.0h/htdocs/sugarcrm/cache/upload/upgrades/temp/tggk9Y/patch/modules/vCals/vCal.php]

Mon, 05 Feb 2007 10:34:03 +0100 [UpgradeWizard] - unlinking [/opt/sugarcrm-4.5.0h/htdocs/sugarcrm/cache/upload/upgrades/temp/JOaN51/patch/modules/vCals/vCal.php]

[privasphere]

sorry - some input missing


vardefs.php:


// Create the indexes
$dictionary['vCal'] = array('table' => 'vcals'
,'fields' => array (
'id' =>
array (
'name' => 'id',
'vname' => 'LBL_NAME',
'type' => 'id',
'required'=>true,
'reportable'=>false,
),
'deleted' =>
array (
'name' => 'deleted',
'vname' => 'LBL_DELETED',
'type' => 'bool',
'required' => true,
'reportable'=>false,
),
'date_entered' =>
array (
'name' => 'date_entered',
'vname' => 'LBL_DATE_ENTERED',
'type' => 'datetime',
),
'date_modified' =>
array (
'name' => 'date_modified',
'vname' => 'LBL_DATE_MODIFIED',
'type' => 'datetime',
),
'user_id' =>
array (
'name' => 'user_id',
'type' => 'id',
'required'=>true,
'reportable'=>false,
),
'type' =>
array (
'name' => 'type',
'type' => 'varchar',
'len' =>25,
),
'source' =>
array (
'name' => 'source',
'type' => 'varchar',
'len' =>25,
),
'content' =>
array (
'name' => 'content',
'type' => 'text',
),


)
, 'indices' => array (
array('name' =>'vcalspk', 'type' =>'primary', 'fields'=>array('id')),
array('name' =>'idx_vcal', 'type' =>'index', 'fields'=>array('type', 'user_id'))
)

);
?>

[privasphere]

vCal.php


require_once('data/SugarBean.php');
require_once('include/utils.php');
require_once('modules/Calendar/Calendar.php');

class vCal extends SugarBean {
// Stored fields
var $id;
var $date_modified;
var $user_id;
var $content;
var $deleted;
var $type;
var $source;
var $module_dir = "vCals";
var $table_name = "vcals";

var $object_name = "vcal";

var $new_schema = true;

var $field_defs = array(
);

// This is used to retrieve related fields from form posts.
var $additional_column_fields = Array();

function vCal()
{

parent::SugarBean();
$this->disable_row_level_security = true;
}

function get_summary_text()
{
return "";
}

function create_list_query($order_by, $where, $show_deleted = 0)
{
}

function fill_in_additional_list_fields()
{
}

function fill_in_additional_detail_fields()
{
}

function get_list_view_data()
{
}

// combines all freebusy vcals and returns just the FREEBUSY lines as a string
function get_freebusy_lines_cache(&$user_bean)
{
$str = '';
// First, get the list of IDs.
$query = "SELECT id from vcals where user_id='{$user_bean->id}' AND type='vfb' AND deleted=0";
$vcal_arr = $this->build_related_list($query, new vCal());

foreach ($vcal_arr as $focus)
{
if (empty($focus->content))
{
return '';
}

$lines = explode("\n",$focus->content);

foreach ($lines as $line)
{
if ( preg_match('/^FREEBUSY[;:]/i',$line))
{
$str .= "$line\n";
}
}
}

return $str;
}

// query and create the FREEBUSY lines for SugarCRM Meetings and Calls and
// return the string
function create_sugar_freebusy(&$user_bean,&$start_date_tim e,&$end_date_time)
{
$str = '';
global $DO_USER_TIME_OFFSET;

$DO_USER_TIME_OFFSET = true;
// get activities.. queries Meetings and Calls
$acts_arr =
CalendarActivity::get_activities($user_bean->id,
false,
$start_date_time,
$end_date_time
);

// loop thru each activity, get start/end time in UTC, and return FREEBUSY strings
for ($i = 0;$i < count($acts_arr);$i++)
{
$act =$acts_arr[$i];
$date_arr = array('ts'=>$act->start_time->ts );
$start_time = new DateTimeUtil($date_arr,true);

$date_arr = array('ts'=>$act->end_time->ts );
$end_time = new DateTimeUtil($date_arr,true);

$str .= "FREEBUSY:". $start_time->get_utc_date_time() ."/". $end_time->get_utc_date_time()."\n";

}

return $str;

}

// return a freebusy vcal string
function get_vcal_freebusy(&$user_focus,$cached=true)
{
$str = "BEGIN:VCALENDAR\n";
$str .= "VERSION:2.0\n";
$str .= "PRODID:-//SugarCRM//SugarCRM Calendar//EN\n";
$str .= "BEGIN:VFREEBUSY\n";

$name = $user_focus->first_name. " ". $user_focus->last_name;
$email = $user_focus->email1;


// get current time local
$date_arr = array();
$now_date_time_local = new DateTimeUtil($date_arr,true);

// get current time GMT
$date_arr = array('ts'=>$now_date_time_local->ts - $now_date_time_local->tz_offset);
$now_date_time = new DateTimeUtil($date_arr,true);

// get start date GMT ( 1 day ago )
$date_arr = array(
'day'=>$now_date_time->day - 1,
'month'=>($now_date_time->month),
'hour'=>($now_date_time->hour),
'min'=>($now_date_time->min),
'year'=>$now_date_time->year);

$start_date_time = new DateTimeUtil($date_arr,true);


// get date 2 months from start date
$date_arr = array(
'day'=>$start_date_time->day,
'month'=>($start_date_time->month + 2),
'hour'=>($start_date_time->hour),
'min'=>($start_date_time->min),
'year'=>$start_date_time->year);

$end_date_time = new DateTimeUtil($date_arr,true);

// get UTC time format
$utc_start_time = $start_date_time->get_utc_date_time();
$utc_end_time = $end_date_time->get_utc_date_time();
$utc_now_time = $now_date_time->get_utc_date_time();

$str .= "ORGANIZER;CN=$name:$email\n";
$str .= "DTSTART:$utc_start_time\n";
$str .= "DTEND:$utc_end_time\n";

// now insert the freebusy lines
// retrieve cached freebusy lines from vcals
if ($cached == true)
{
$str .= $this->get_freebusy_lines_cache($user_focus);
}
// generate freebusy from Meetings and Calls
else
{
$str .= $this->create_sugar_freebusy($user_focus,$start_date_tim e,$end_date_time);
}

// UID:20030724T213406Z-10358-1000-1-12@phoenix
$str .= "DTSTAMP:$utc_now_time\n";
$str .= "END:VFREEBUSY\n";
$str .= "END:VCALENDAR\n";
return $str;

}

// static function:
// cache vcals
function cache_sugar_vcal(&$user_focus)
{
vCal::cache_sugar_vcal_freebusy($user_focus);
}

// static function:
// caches vcal for Activities in Sugar database
function cache_sugar_vcal_freebusy(&$user_focus)
{
$focus = new vCal();
// set freebusy members and save
$arr = array('user_id'=>$user_focus->id,'type'=>'vfb','source'=>'sugar');
$focus->retrieve_by_string_fields($arr);


$focus->content = $focus->get_vcal_freebusy($user_focus,false);
$focus->type = 'vfb';
$focus->date_modified = null;
$focus->source = 'sugar';
$focus->user_id = $user_focus->id;
$focus->save();
}


}

?>

[rzhou]

Hi,

The vardefs.php looks fine, but the vCal.php did not get updated for some reason.

You can get the vCal.php from the upgrade package and replace the current file.

Basically the line

var $object_name = "vcal";

needs to be changed to

var $object_name = "vCal";

[privasphere]

thanks a lot for the help

I hadn't to correct the upper case error - I just copied the entire file vCal.php from the update file SugarCE-Upgrade-4.5.1-to-5.1.0c.zip to the system.

My file in sugar still had the date 09-18-2007 - I think it has not been updated in the update process.

With the 'new' vCal.php it works fine now!

regards & thanks