Set color from record

chris.cavage · December 1, 2011, 9:35pm

So here’s my events_rec.php that queries the info for the scheduler:

//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, previsit_categories.textColor 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, previsit_categories.color(color), previsit_categories.textColor(textColor)");
}   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");
   
   }

Here’s some generated XML (hope this helps!):

<data>
<event id="1322775061x1192">
<start_date>
<![CDATA[ 2011-12-01 13:20:00 ]]>
</start_date>
<end_date>
<![CDATA[ 2011-12-01 14:45:00 ]]>
</end_date>
<text>
<![CDATA[ ]]>
</text>
<rec_type>
<![CDATA[ ]]>
</rec_type>
<event_pid>
<![CDATA[ 0 ]]>
</event_pid>
<event_length>
<![CDATA[ 0 ]]>
</event_length>
<office_ID>
<![CDATA[ 3 ]]>
</office_ID>
<reason>
<![CDATA[ 1 ]]>
</reason>
<patient_ID>
<![CDATA[ 2123 ]]>
</patient_ID>
<name>
<![CDATA[ Wallace Mogel ]]>
</name>
<howheard_ID>
<![CDATA[ 26 ]]>
</howheard_ID>
<promo_ID>
<![CDATA[ 218 ]]>
</promo_ID>
<company_ID>
<![CDATA[ 1 ]]>
</company_ID>
<appt_status>
<![CDATA[ 1 ]]>
</appt_status>
<saw_by>
<![CDATA[ 1 ]]>
</saw_by>
<color>
<![CDATA[ ]]>
</color>
<textColor>
<![CDATA[ ]]>
</textColor>
</event>
<event id="1322775061x1193">
<start_date>
<![CDATA[ 2011-12-01 15:35:00 ]]>
</start_date>
<end_date>
<![CDATA[ 2011-12-01 17:00:00 ]]>
</end_date>
<text>
<![CDATA[ ]]>
</text>
<rec_type>
<![CDATA[ ]]>
</rec_type>
<event_pid>
<![CDATA[ 0 ]]>
</event_pid>
<event_length>
<![CDATA[ 0 ]]>
</event_length>
<office_ID>
<![CDATA[ 3 ]]>
</office_ID>
<reason>
<![CDATA[ -1 ]]>
</reason>
<patient_ID>
<![CDATA[ -1 ]]>
</patient_ID>
<name>
<![CDATA[ ]]>
</name>
<howheard_ID>
<![CDATA[ -1 ]]>
</howheard_ID>
<promo_ID>
<![CDATA[ -1 ]]>
</promo_ID>
<company_ID>
<![CDATA[ 1 ]]>
</company_ID>
<appt_status>
<![CDATA[ 2 ]]>
</appt_status>
<saw_by>
<![CDATA[ -1 ]]>
</saw_by>
<color>
<![CDATA[ ]]>
</color>
<textColor>
<![CDATA[ ]]>
</textColor>
</event>
<coll_options for="reason">
<item value="-1" label="None..."/>
<item value="1" label="Test"/>
<item value="2" label="Follow Up"/>
<item value="3" label="Service"/>
<item value="5" label="Delivery - Repair"/>
<item value="7" label="Other"/>
</coll_options>
<coll_options for="apptstatus">
<item value="1" label="Confirmed"/>
<item value="2" label="Unconfirmed"/>
<item value="3" label="Cancelled"/>
<item value="4" label="No Show"/>
<item value="5" label="Walk-In"/>
</coll_options>
<coll_options for="offices">
<item value="1" label="Office1"/>
<item value="2" label="Office2"/>
<item value="3" label="Office3"/>
</coll_options>
<coll_options for="howheard">
<item value="-1" label="None..."/>
<item value="10" label="Other"/>
<item value="24" label="Comeback"/>
<item value="26" label="Newspaper"/>
<item value="27" label="Television"/>
<item value="28" label="Radio"/>
<item value="29" label="Direct Mail"/>
<item value="30" label="Email"/>
<item value="31" label="Upgrade Letter"/>
<item value="34" label="Word of Mouth"/>
</coll_options>
<coll_options for="provider">
<item value="-1" label="None..."/>
<item value="1" label="Chris"/>
<item value="2" label="Nick"/>
</coll_options>
<coll_options for="promos">
<item value="-1" label="None..."/>
<item value="218" label="Newspaper 2011-11-28"/>
<item value="220" label=" Upgrade Letter 2011-11-28"/>
<item value="217" label="Newspaper 2011-11-28"/>
</coll_options>
</data>

So I got the textColor and color to change finally. You HAVE to also have the column name in the db table named ‘textColor’ and ‘color’ for this to work! Once I did that, the events change color based on the query.

However, NO events can be updated or deleted. So, that’s the other issue I’m working against.

Thank YOU!

Ilya · December 1, 2011, 10:58pm

Hello,

[code]

[/code] So initially colors are not set? [quote] So I got the textColor and color to change finally. You HAVE to also have the column name in the db table named 'textColor' and 'color' for this to work! Once I did that, the events change color based on the query. [/quote] No, they can be named whatever you like but must be selected as color and textColor (through aliases for example). [quote] However, NO events can be updated or deleted. So, that's the other issue I'm working against. [/quote] For the update operations you are using render_sql, that query won't save events :slight_smile: You can check the log what query is executed when you try to save something. Usually you do complex select for the select mode and simple render_table for the update.

Kind regards,
Ilya

chris.cavage · December 2, 2011, 12:21am

Hmmm… in order to get it to work by naming the column name something else I’d have to do this in the query (I guess this is via the aliases like you say?):

“SELECT DifferentEventColorName AS color, DifferentTextColorName AS textColor…”,“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), textColor(textColor)”

This wouldn’t work (as I tested it):
“SELECT DifferentEventColorName, DifferentTextColorName…”,“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, DifferentEventColorName(color), DifferentTextColorName(textColor)”

Either way, I got the colors to work using the first method. I actually just changed the column names in my db to color and textColor. Same difference.

Ok I changed my query as you suggested to this (hope I’m right):

//render sql for viewing
 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, previsit_categories.textColor 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, previsit_categories.color(color), previsit_categories.textColor(textColor)");   
 } else {
 // render table for update/delete
 	$scheduler->render_table("events_rec","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");
   
   }

The event still never updates/deletes.

When I check the log after deleting a sample event, for example, here’s the info:

====================================
Log started, 01/12/2011 05:12:35
====================================

DataProcessor object initialized
1322784332x1194_id => 1322784332x1194
1322784332x1194_start_date => 2011-12-02 15:25
1322784332x1194_end_date => 2011-12-02 16:50
1322784332x1194_text => inserting
1322784332x1194_rec_type => 
1322784332x1194_event_pid => 0
1322784332x1194_event_length => 0
1322784332x1194_office_ID => 3
1322784332x1194_reason => -1
1322784332x1194_patient_ID => -1
1322784332x1194_name =>  
1322784332x1194_howheard_ID => -1
1322784332x1194_promo_ID => -1
1322784332x1194_company_ID => 1
1322784332x1194_appt_status => 2
1322784332x1194_saw_by => 1
1322784332x1194_color => 
1322784332x1194_textColor => 
1322784332x1194_!nativeeditor_status => deleted
ids => 1322784332x1194

Row data [1322784332x1194]
event_id => 1322784332x1194
start_date => 2011-12-02 15:25
end_date => 2011-12-02 16:50
text => inserting
rec_type => 
event_pid => 0
event_length => 0
office_ID => 3
reason => -1
patient_ID => -1
name =>  
howheard_ID => -1
promo_ID => -1
company_ID => 1
appt_status => 2
saw_by => 1
color => 
textColor => 
!nativeeditor_status => deleted

DELETE FROM events_rec WHERE event_id='1322784332x1194'

Edit operation finished
0 => action:deleted; sid:1322784332x1194; tid:1322784332x1194;

Done in 0.00457906723022s

When I look at the XML file, here’s the event details that I am trying to delete as a test:

<event id="1322784615x1194">
<start_date>
<![CDATA[ 2011-12-02 15:25:00 ]]>
</start_date>
<end_date>
<![CDATA[ 2011-12-02 16:50:00 ]]>
</end_date>
<text>
<![CDATA[ delete me]]>
</text>
<rec_type>
<![CDATA[ ]]>
</rec_type>
<event_pid>
<![CDATA[ 0 ]]>
</event_pid>
<event_length>
<![CDATA[ 0 ]]>
</event_length>
<office_ID>
<![CDATA[ 3 ]]>
</office_ID>
<reason>
<![CDATA[ -1 ]]>
</reason>
<patient_ID>
<![CDATA[ -1 ]]>
</patient_ID>
<name>
<![CDATA[ ]]>
</name>
<howheard_ID>
<![CDATA[ -1 ]]>
</howheard_ID>
<promo_ID>
<![CDATA[ -1 ]]>
</promo_ID>
<company_ID>
<![CDATA[ 1 ]]>
</company_ID>
<appt_status>
<![CDATA[ 2 ]]>
</appt_status>
<saw_by>
<![CDATA[ 1 ]]>
</saw_by>
<color>
<![CDATA[ ]]>
</color>
<textColor>
<![CDATA[ ]]>
</textColor>
</event>

What I notice is that the XML event id info is: 1322784615x1194

And the DELETE query deletes WHERE event_id=‘1322784332x1194’

These are two different IDs. I hope my detective work is useful. This would explain why nothing is updated/deleted, I think.

I am gonna get this!

Ilya · December 2, 2011, 1:00pm

Hello,

   $scheduler->render_sql("SELECT start_date,end_date,text,rec_type,

You also need to select ‘event_id’ if that’s the correct name of the id field.

Kind regards,
Ilya

chris.cavage · December 2, 2011, 3:00pm

//render sql for viewing
 if ($scheduler->is_select_mode()){
   $scheduler->render_sql("SELECT event_id, 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, previsit_categories.textColor 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, previsit_categories.color(color), previsit_categories.textColor(textColor)");   
 } else {
 // render table for update/delete
 	$scheduler->render_table("events_rec","event_id","event_id");
   
   }

I added what you suggested.

The above code now allows me to delete but not insert/update! It’s evidently baby steps for me!

chris.cavage · December 2, 2011, 9:29pm

What I notice, is that the event_id generated on creating a new event is not the auto-incremented value my primary key, event_id, is set for.

Before I started this thread, when an event was created, the event_id primary key would auto increment nicely. Not sure of the logic to make the event_id now upon new record.

Either way, nothing can be inserted/updated.

Does that mean anything?

chris.cavage · December 2, 2011, 9:36pm

So events can be updated… just not inserted.

Here’s the reason why:

When a new event in inserted, it sets event_id to 2147483647 all the time. If it were set to NULL when inserting, then the auto increment would work in my db.

I found this out by auto incrementing each new event manually in my db… everytime I add an event that event_id is in the event_id field. I cannot update/delete that record and I cannot insert another record until I manually change that event_id to something other than 2147483647, of course.

Any ideas?

chris.cavage · December 2, 2011, 9:56pm

  if ($scheduler->is_select_mode()){ // ' code for loading data
$SQL = "SELECT event_id,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, previsit_categories.textColor 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']) . "'"; 
$scheduler->render_sql($SQL,"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, previsit_categories.color(color), previsit_categories.textColor(textColor)", "", "");
}
else {	 // code for other operations - i.e. update/insert/delete
$scheduler->render_table("events_rec","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");

}

Phew. I hope the above looks ok now. Not sure why this was giving me such a hard time.

Hopefully I’m set now. Thanks so much.