export calender data and import to outlook using custom query report in zuckerreports

[aheuermann]

I have been having troubles exporting calender data in a way that outlook can read in. The problem is that sugar doesnt keep track of a end_time field for meeting and calls, it just keeps the duration fields. I used the following query and zuckerReports to create a .csv file of all the meeting and call data of the user with zuckerReports module. The query converts the sugar time to central standard time so you will need to change where the "5" that is located in it to adjust for your time zone. Also, you will need to change the data type of the "sql1" field in the zucker_querytemplates table to account for longer queries. The query is long, but mostly because it will take into account any meeting length whether it be a few hours or days and still calculate the correct end_time, and end_date. I am wondering if there is a simpler way of easily exporting calendar data and insering it into outlook without having to pay for a plug-in feature.

Here is the query, I havent tested it thuroughly yet, so if you notice an error let me know. you will need the most up to date version of zuckerReports and then just create a custom query report, bind the CURRENT_USER parameter and save the resulting .csv file and then import and map fields in outlook:

(select time(date_sub(date_add(date_add(meetings.date_star t, INTERVAL (meetings.time_start/10000)hour), INTERVAL((meetings.time_start/100)%100)minute),INTERVAL 5 hour))as Start_Time,date(date_sub(date_add(date_add(meeting s.date_start, INTERVAL (meetings.time_start/10000)hour), INTERVAL ((meetings.time_start/100)%100)minute),INTERVAL 5 hour)) as Start_Date, time(date_sub(date_add(date_add(date_add(date_add( meetings.date_start, INTERVAL (meetings.time_start/10000)hour), INTERVAL ((meetings.time_start/100)%100)minute), INTERVAL meetings.duration_hours hour), INTERVAL meetings.duration_minutes minute),INTERVAL 5 hour)) as End_Time, date(date_sub(date_add(date_add(date_add(date_add( meetings.date_start, INTERVAL (meetings.time_start/10000)hour), INTERVAL ((meetings.time_start/100)%100)minute), INTERVAL meetings.duration_hours hour), INTERVAL meetings.duration_minutes minute),INTERVAL 5 hour)) as End_Date, meetings.name as Subject, IFNULL(meetings.description, '')as Description,IFNULL(meetings.location, '')as Location from meetings left outer join meetings_users on meetings_users.meeting_id = meetings.id && meetings.deleted =' 0' left outer join users on users.id = meetings_users.user_id && users.deleted='0' && users.id = '$CURRENT_USER' where meetings.deleted=0 && (meetings.assigned_user_id ='$CURRENT_USER'|| meetings_users.user_id='$CURRENT_USER') group by users.user_name, meetings.name)UNION (select time(date_sub(date_add(date_add(calls.date_start, INTERVAL (calls.time_start/10000)hour), INTERVAL((calls.time_start/100)%100)minute),INTERVAL 5 hour))as Start_Time,date(date_sub(date_add(date_add(calls.d ate_start, INTERVAL (calls.time_start/10000)hour), INTERVAL ((calls.time_start/100)%100)minute),INTERVAL 5 hour)) as Start_Date, time(date_sub(date_add(date_add(date_add(date_add( calls.date_start, INTERVAL (calls.time_start/10000)hour), INTERVAL ((calls.time_start/100)%100)minute), INTERVAL calls.duration_hours hour), INTERVAL calls.duration_minutes minute),INTERVAL 5 hour)) as End_Time, date(date_sub(date_add(date_add(date_add(date_add( calls.date_start, INTERVAL (calls.time_start/10000)hour), INTERVAL ((calls.time_start/100)%100)minute), INTERVAL calls.duration_hours hour), INTERVAL calls.duration_minutes minute),INTERVAL 5 hour)) as End_Date, calls.name as Subject, IFNULL(calls.description, '')as Description, '' as location from calls left outer join calls_users on calls_users.call_id = calls.id && calls.deleted =' 0' left outer join users on users.id = calls_users.user_id && users.deleted='0' && users.id = '$CURRENT_USER' where calls.deleted=0 && (calls.assigned_user_id ='$CURRENT_USER'|| calls_users.user_id='$CURRENT_USER') group by users.user_name, calls.name);

[ftreml]

Thanks aheuermann. I just built a package from it and published it on sugarforge:
http://www.sugarforge.org/frs/downlo...port_Query.zip