Adding a related sales order to the calendar event

[Guido1982]

I am in the process of relating activities directly to sales orders. I created a table that relates them and made a query in 'SaveEvent.php' that saves it, so far so good. Now I want every event to show that sales order as other related modules do. I saw that the Events.php file creates a JSON of all events, that I guess the 'fullcalendar.js' file uses to build the calendar. But now for the moment I am stumped on how to implement my query here and use it in the calendar. I think I should also edit 'CalendarView.tpl' (the jQuery to be exact), so that the JSON is expanded and the jQuery call uses this to build the calendar items. Does anyone have more detailled info?

[Guido1982]

I'm a bit lost here. Right above the part where I want to change the code, the variable 'record' gets used, for instance to create a link to the detail view:

PHP Code:

"<a target='_new' href='index.php?action=EditView&module=".$activitytypeid."&record=".$record."'>".$app['LNK_EDIT']."</a>"; 


And I see in the module that the record is the activity ID I'm looking for, but for some reason, it returns blank to me...

PHP Code:

                    // Added by MajorLabel: Get the associated sales order id and create a link to that sales order
                    $getRelatedOrder = $adb->pquery('SELECT so_id FROM vtiger_soactivityrel WHERE activity_id=?',array($record));
                    $relatedOrder = $adb->query_result_rowdata($getRelatedOrder,0);
                    $title .= "<br><b>Order:&nbsp;</b>$record"; 


NOTE: The third line is just for testing now, to see if I can get the correct record ID to even show up.

---

Wait, I noticed something. It does show up, in the pop-up field. Just not in the event in the calendar itself. So I guess I have to change this in calendarview.tpl...

---

OK, I found out the following. Normally, the Calendar module would get all fields for the Calendar and run them through 'transferForAddIntoTitle()'. Since I don't do that, I just append the string, the 'fullcalendar' plugin doesn't know what I'm doing. I have to prepare my string somehow so that the plugin understands it. Here's what I have:

PHP Code:

                    // Added by MajorLabel: Get the associated sales order id and create a link to that sales order
                    // Set up the query
                    $getRelatedOrder = $adb->pquery('SELECT so_id FROM vtiger_soactivityrel WHERE activity_id=?',array($record));
                    // Get the crmentity ID for the related sales order
                    $relatedOrder = $adb->query_result_rowdata($getRelatedOrder,0);
                    $relatedOrderID = $relatedOrder[so_id];
                    // Setup the query to get the sales order no and subject line
                    $getRelatedOrderDetails = $adb->pquery('SELECT subject, salesorder_no FROM vtiger_salesorder WHERE salesorderid=?',array($relatedOrder[so_id]));
                    // Create an array for this row
                    $relatedOrderDetails = $adb->query_result_rowdata($getRelatedOrderDetails,0);
                    $title .= "<br><b>Order:&nbsp;</b><a href='index.php?module=SalesOrder&parenttab=Sales&action=DetailView&record=$relatedOrderID'>$relatedOrderDetails[salesorder_no]: $relatedOrderDetails[subject]</a>";
                    // End addition by MajorLabel 


In 'Events.php'. Now I need to format this so the plugin understands it. The pop-up (when you click a calendar item) does work like I want it to.

[Guido1982]

In general, the Calendar events don't pick up the variables I use. That's the main problem.

---

I'm getting really stumped here. I even ended up with adding the related_so string to the JSON object and calling it in the javascript in the CalendarView.tpl file. Still the same. I added a screenshot of what is happening. In the pop-up all is well, I see my related order subject line, which is a link to the order details. But in the event itself the PHP variables just don't get passed...

[Guido1982]

Does anyone have an idea why my variables in Events.php are not passed on to the event in the calendar yet?

---

I've been studying the events.php file some more. Basically, the part where fields are added to show in the event is this:

PHP Code:

if ($detailview_permissions) {
    $sql0 = "SELECT * FROM its4you_calendar4you_event_fields WHERE userid = ? AND view = ?";
    $result0 = $adb->pquery($sql0, array($current_user->id,$view));
    $num_rows0 = $adb->num_rows($result0);
    if ($num_rows0 > 0) {
        while($row0 = $adb->fetchByAssoc($result0)) {
            $sql01 = "SELECT uitype, columnname, fieldlabel FROM vtiger_field WHERE fieldname = ? AND tabid IN (?,?)";
            $result01 = $adb->pquery($sql01, array($row0['fieldname'],$calendar_tabid,$events_tabid));
            $columnname = $adb->query_result($result01,0,"columnname");
            $fieldlabel = $adb->query_result($result01,0,"fieldlabel");
            $uitype = $adb->query_result($result01,0,"uitype");
            $Field_data = array("fieldname"=>$row0['fieldname'], "columnname"=>$columnname, "fieldlabel" => $fieldlabel, "uitype"=>$uitype);
            if ($row0['type'] == "1") {
                $Showed_Field[$row0['event']] = $Field_data;
            } else {
                $Event_Info[$row0['event']][] = $Field_data;
            }
        }
    }
} 


I need some better understanding of this, can anyone help me as to what the 'tabid' is in the system? I think I should integrate my sales order connection deeper into the module in stead of 'laying it on top' as I do now. But first I need a better understanding of its inner workings.

Also, I need to understand what the 'type' is in table `its4you_calendar4you_event_fields`. It only says 1 or 2, but I don't know what they represent.

[joebordes]

tabid is a unique number that each module is given in the system. each module when it is installed is given one, the base modules usually have the same one because they are installed in the same order during install but that is not mandatory.
You can see the list of assigned tabids in vtiger_tab table
this number is used freely through out the application to identify a module.

I don't know what the type is, may be "Event" and "Task" which are in the same table but logically separated in the code ??

HTH

[Guido1982]

Hey Joe,

Thanks, that makes sense.

About the Event and Task separation, that was my first thought too, but more investigation taught me it was not...

---

I see the tabid is collected here:

PHP Code:

$events_tabid = getTabId("Events"); 


Which is a method of the Calendar4You class I think (will look into this). I don't know if 'Events' can be related to a sales order in the default system setup (I think it cannot), so I'll have to change this first to start at the root of the process.

---

Hmm, looking in the 'vtiger_acitivity' table I see there is only room for one related module, but I need two.

[joebordes]

Look at this commit: aef5145afe132d106186dcd59451d6d83b5477d3
That was added by MSL and adds Vendors to the Calendar modules, maybe you can follow the same path.
You should also be able to add a uitype 10 field there.

[joebordes]

getTabId is a system function in include/utils

[Guido1982]

getTabId is a system function in include/utils

From reading the name, I guess it does nothing else but return the ID number for the modulename you put into it?

---

Look at this commit:  aef5145afe132d106186dcd59451d6d83b5477d3
That was added by MSL and adds Vendors to the Calendar modules, maybe you can follow the same path.
You should also be able to add a uitype 10 field there.

Thanks, I'll look into this.

---

I took a look at the commit, but that is not quite what I want to do. What MSL did was add 'Vendors' to the 'select related' dropdown, so that you could relate a Vendor. I already use this functionality to relate an Account (via my autocomplete field). I want to create a second relation (for which I have also created a special table in the database) to activities. That all works, I edited the 'Save' and 'Delete' files to make sure the relation is saved and deleted when an event is deleted. I've got it all working. The only problem I am facing is that the '$title' variable (that holds the actual content of the event as shown in the calendar) does not process my variables. During the creation of the '$Activities' array in Events.php, this array is made and presented as JSON, so that the fullcalendar plugin that CalendarView.tpl uses can use it to build the events in the presentation. For some reason, the variables I use to set the sales order name and create a link to it are empty here. In the white pop-up that appears when you click an event (the actions pop-up) my variables do work, so I can click to view the related sales order. I don't understand why they don't work in the JSON part. But typing this, I'm thinking I maybe have made some formatting mistakes.

[Guido1982]

I keep banging my head against the wall with this. I've spent hours trying various methods, but still the same result: My variables do not get passed into the AJAX call in the CalendarView.tpl. Only in the separate pop-up, that loads up later.