Set color from record

Scheduler Javascript
#1

I am using the scheduler with php/mysql table.

How can I set the event colors based on the “SELECT” query that has a field called “color” in it?

My select is something like this:

$scheduler->render_sql("SELECT * from events_rec LEFT JOIN appt_types ON appt_types.ID=events_rec.appt_type WHERE office_ID IN ($office_id) AND appt_status IN ($appt_progress) AND saw_by IN ($provider) AND company_ID = '" . addslashes($_SESSION['Company_ID']) . "'","event_id","start_date,end_date,text,rec_type,event_pid,event_length,office_ID,reason,patient_ID,name,howheard_ID,promo_ID,company_ID,appt_status,saw_by, appt_color");

So when the events load, the color is based on the appt_color field in the query. Thanks.

#2

Hello,

Try changing

appt_color");

to

appt_color(color)");

Scheduler support ‘color’ and I believe ‘textColor’ options.

Kind regards,
Ilya

#3

That seemed to work.
However, I can’t add/update records then to the calendar… I am guessing because of the LEFT JOIN that I would need in order to set the color.

Is there a way to set the color for the event in my php script elsewhere?

#4

Hello,

I believe it would be easier to use your custom query to load events and simple render_table for other operations.

if ($scheduler_connector->is_select_mode()){ // your render_sql for select mode } else { // render table for update/delete }
Kind regards,
Ilya

#5

Thanks Ilya…

So in my php code have something like this?

//render sql
   if ($scheduler_connector->is_select_mode()){
	$scheduler->render_sql("select * from events_rec LEFT JOIN categories ON categories.ID = events_rec.Category where office_ID IN ($office_id) AND appt_status IN ($appt_progress) AND saw_by IN ($provider) AND company_ID = '" . addslashes($_SESSION['Company_ID']) . "'","event_id","start_date,end_date,text,rec_type,event_pid,event_length,office_ID,reason,patient_ID,name,howheard_ID,promo_ID,company_ID,appt_status,saw_by, color(color)");   
}   else {
	
	$scheduler->render_sql("select * from events_rec where office_ID IN ($office_id) AND appt_status IN ($appt_progress) AND saw_by IN ($provider) AND company_ID = '" . addslashes($_SESSION['Company_ID']) . "'","event_id","start_date,end_date,text,rec_type,event_pid,event_length,office_ID,reason,patient_ID,name,howheard_ID,promo_ID,company_ID,appt_status,saw_by");
   }

I am working on it now, but I get a blank error box when the scheduler loads.

#6

Hello,

Your code seems to be correct but edit following line:

if ($scheduler_connector->is_select_mode()){

If in your case you named it simply $scheduler then it should be:

if ($scheduler->is_select_mode()){

Kind regards,
Ilya

#7

Of course, thanks.

//render sql
 if ($scheduler->is_select_mode()){
   $scheduler->render_sql("select start_date,end_date,text,rec_type,event_pid,event_length,office_ID,reason,patient_ID,name,howheard_ID,promo_ID,events_rec.company_ID,appt_status,saw_by, previsit_categories.Color from events_rec LEFT JOIN previsit_categories ON previsit_categories.ID = events_rec.reason where office_ID IN ($office_id) AND appt_status IN ($appt_progress) AND saw_by IN ($provider) AND events_rec.company_ID = '" . addslashes($_SESSION['Company_ID']) . "'","event_id","start_date,end_date,text,rec_type,event_pid,event_length,office_ID,reason,patient_ID,name,howheard_ID,promo_ID,company_ID,appt_status,saw_by, Color(color)");   
}   else {
   $scheduler->render_sql("select * from events_rec where office_ID IN ($office_id) AND appt_status IN ($appt_progress) AND saw_by IN ($provider) AND company_ID = '" . addslashes($_SESSION['Company_ID']) . "'","event_id","start_date,end_date,text,rec_type,event_pid,event_length,office_ID,reason,patient_ID,name,howheard_ID,promo_ID,company_ID,appt_status,saw_by");
   
   }

While it seems to load events now, the color doesn’t change and no events can be deleted. Events can be added and updated though.

Thanks.

#8

Hello,

Please enable log and check what kind of query scheduler tries to execute when deleting event.

$scheduler = new schedulerConnector($res); $scheduler->enable_log("log.txt",false);

What do you mean? When colors should change in your case?

Kind regards,
Ilya

#9

Yes, that’s what I meant. When the query loads, the event color doesn’t change to the color from the query.

I will get back to you once I use the log, like you suggest.

#10

Does it change the color if you switch between views/another date?
Please copy part of the returning xml here.

Kind regards,
Ilya

#11

No it doesn’t change color when I switch between views/days.

When I try to delete the event here’s the info from the log.txt file:

====================================
Log started, 23/11/2011 01:11:34
====================================

DataProcessor object initialized
1322078630x401_id => 1322078630x401
1322078630x401_start_date => 2011-11-23 10:45
1322078630x401_end_date => 2011-11-23 11:25
1322078630x401_text => 
1322078630x401_rec_type => 
1322078630x401_event_pid => 0
1322078630x401_event_length => 0
1322078630x401_office_ID => 4
1322078630x401_reason => -1
1322078630x401_patient_ID => -1
1322078630x401_name =>  
1322078630x401_howheard_ID => -1
1322078630x401_promo_ID => -1
1322078630x401_company_ID => 1
1322078630x401_appt_status => 2
1322078630x401_saw_by => -1
1322078630x401_color => 
1322078630x401_!nativeeditor_status => deleted
ids => 1322078630x401

Row data [1322078630x401]
event_id => 1322078630x401
start_date => 2011-11-23 10:45
end_date => 2011-11-23 11:25
text => 
rec_type => 
event_pid => 0
event_length => 0
office_ID => 4
reason => -1
patient_ID => -1
name =>  
howheard_ID => -1
promo_ID => -1
company_ID => 1
appt_status => 2
saw_by => -1
color => 
!nativeeditor_status => deleted

DELETE FROM events_rec WHERE event_id='1322078630x401' AND ( office_ID IN (4) AND appt_status IN (1,2,3,4,5) AND saw_by IN (1,2,-1) AND company_ID = '1')

Edit operation finished
0 => action:deleted; sid:1322078630x401; tid:1322078630x401;

Done in 0.00553297996521s

Here’s the log info for the error as to why the color isn’t showing/loading:

====================================
Log started, 23/11/2011 01:11:50
====================================

SELECT  start_date,end_date,text,rec_type,event_pid,event_length,office_ID,reason,patient_ID,name,howheard_ID,promo_ID,events_rec.company_ID,appt_status,saw_by, previsit_categories.Color FROM events_rec LEFT JOIN previsit_categories ON previsit_categories.ID = events_rec.reason WHERE  office_ID IN (4) AND appt_status IN (1,2,3,4,5) AND saw_by IN (1,2,-1) AND events_rec.company_ID = '1'

Undefined index:  color at /home1/mysounds/public_html/myfolder/dhtmlxScheduler/codebase/connector/scheduler_connector.php line 18

The above query DOES have a field of “black” for a color for various records. I tested the query on my server.

#12

Hello,

Please note that ‘Color’ and ‘color’ are not the same thing. And scheduler expects ‘color’.

Kind regards,
Ilya

#13

Thanks. Yeah, I realize that. I feel that I’m using the code correctly:

/render sql
if ($scheduler->is_select_mode()){
   $scheduler->render_sql("select start_date,end_date,text,rec_type,event_pid,event_length,office_ID,reason,patient_ID,name,howheard_ID,promo_ID,events_rec.company_ID,appt_status,saw_by, previsit_categories.Color from events_rec LEFT JOIN previsit_categories ON previsit_categories.ID = events_rec.reason where office_ID IN ($office_id) AND appt_status IN ($appt_progress) AND saw_by IN ($provider) AND events_rec.company_ID = '" . addslashes($_SESSION['Company_ID']) . "'","event_id","start_date,end_date,text,rec_type,event_pid,event_length,office_ID,reason,patient_ID,name,howheard_ID,promo_ID,company_ID,appt_status,saw_by, Color(color)");   
}   else {
   $scheduler->render_sql("select * from events_rec where office_ID IN ($office_id) AND appt_status IN ($appt_progress) AND saw_by IN ($provider) AND company_ID = '" . addslashes($_SESSION['Company_ID']) . "'","event_id","start_date,end_date,text,rec_type,event_pid,event_length,office_ID,reason,patient_ID,name,howheard_ID,promo_ID,company_ID,appt_status,saw_by");
   
   }

…but still no colors, and events cannot be deleted. I took a break from this, but will come back to it to try to figure it out.

#14

Hello,

Please copy part of the generated xml.

Are colors applied during initial load?

Are the any javascript errors?

Kind regards,
Ilya

#15

Sure, I wouldn’t mind supplying XML. Can you tell me how I can do that? Thanks. :smiley:

#16

No, no javascript errors.

#17

Hello,

You can attach to your message (“Filename:” “Browse” section) or simply copy the text, for example:

<data> <event id="5000002"> <start_date>2011-09-02 10:00:00</start_date> <end_date>2011-09-02 18:00:00</end_date> <text>timu3</text> <title>timu3</title> <type></type> <detail>xijie3</detail> <location>didian3</location> <rec_type>week_1___2,4#no</rec_type> <event_pid>0</event_pid> <event_length>3600</event_length> <lat></lat> <lng></lng> <source>google calendar</source> <readonly>1</readonly> </event> <event id="2"> <start_date>2011-09-02 06:00</start_date> <end_date>2011-09-02 07:00</end_date> <text>group1 Tong</text> <title>group1 Tong</title> <type>Game</type> <detail>aaa</detail> <location>test loc3() ll,ooo,ny,11223</location> <rec_type></rec_type> <event_pid>0</event_pid> <event_length>0</event_length> <lat>0.000000</lat> <lng>0.000000</lng> <source>guest2</source> <readonly>1</readonly> </event> </data>
Kind regards,
Ilya

#18

Sorry for the confusion.

I meant to say, I don’t know how to capture the generated XML once the scheduler loads.

I am querying my db using php/mysql.

#19

I added:

var xml = scheduler.toXML(); //xm string

Do I have to somehow create a button that will export the scheduler to xml? Thanks.

#20

Hello,

Simply open your events.php link in the browser.

Kind regards,
Ilya