Okay, so I've done some quick 'n dirty work on the 'Add Event' UI in the calendar. I've edited the 'addEventUI.php' file as follows:
PHP Code:
<!-- Dynamically add jQuery UI from a CDN --> <script type="text/javascript"> var script = document.createElement("script"); script.type = "text/javascript"; script.src = "https://code.jquery.com/ui/1.11.4/jquery-ui.min.js"; var uiCSS = document.createElement("link"); uiCSS.type = "text/css"; uiCSS.rel = "stylesheet"; uiCSS.href = "https://code.jquery.com/ui/1.11.4/themes/cupertino/jquery-ui.css"; document.head.appendChild(script); document.head.appendChild(uiCSS); </script> <!-- End include of jQuery UI --> <!-- Autocomplete jQuery --> <script> jQuery(window).load(function(){ jQuery( "#account_autocomplete" ).autocomplete({ // Beware: Autocomplete sets a search term as GET parameter to the URL // The php file has to take this into account and use the search term // in its mySQL query source: "JSON_Accounts.php", // On open we have to set a high z-index to the UL, else it will // fall behind the 'addEvent' UI box open: function(){ jQuery(this).autocomplete('widget').css('z-index', 999999); return false; }, // Here we add the result on selection to a hidden input field // That the calendar module uses to connect a crmentity select: function( event, ui) { // Add crmentity id to hidden input jQuery('input[name=parent_id]').attr('value',ui.item.value); // Also update the activity name with the activity type and account name jQuery('input[name=subject]').val(jQuery('#activitytype option:selected').text() + " " + ui.item.label); // Overwrite the input field with the account name in stead of the crmentity jQuery('#account_autocomplete').val(ui.item.label); return false; }, // Also update the input field with the label during focus focus: function( event, ui ) { // Overwrite the input field with the account name in stead of the crmentity jQuery('#account_autocomplete').val(ui.item.label); return false; }, minLength: 2 // Make sure 'autocomplete' is turned on for this field }).attr("autocomplete","on") // Add '_renderItem' to use HTML in the results, so we can use // things like address fields and better readable markup .autocomplete( "instance" )._renderItem = function( ul, item ) { return jQuery( "<li>" ) .append( "<a>" + "<b>" + item.label + "</b>" + "<br>" + item.street + "<br>" + item.code + " " + item.city + "</a>" ) .appendTo( ul ); }; }); </script> <!-- End autocomplete -->
Added to include jQuery UI and the cupertino theme (which is what the calendar uses), and I added:
include_once('vtlib/Vtiger/Module.php'); global $adb;
// Lines 11-24 from http://www.wowww.nl/2014/02/01/jquery-autocomplete-tutorial-php-mysql/ /* prevent direct access to this page */ $isAjax = isset($_SERVER['HTTP_X_REQUESTED_WITH']) AND strtolower($_SERVER['HTTP_X_REQUESTED_WITH']) === 'xmlhttprequest'; if(!$isAjax) { $user_error = 'Access denied - direct call is not allowed...'; trigger_error($user_error, E_USER_ERROR); } ini_set('display_errors',1);
/* if the 'term' variable is not sent with the request, exit */ if ( !isset($_REQUEST['term']) ) { exit; }
// Start the empty 'allaccounts' array $allaccounts = array();
// Get the term from the jQuery autocomplete GET request $term = trim(strip_tags($_GET['term']));
// Get the accounts that match part of the search term $accountresults = $adb->query("SELECT accountid, account_no, accountname FROM vtiger_account WHERE accountname LIKE '%$term%'");
// Create empty JSON array for this account $JsonAccount = array();
// 'vtiger_account' table has no address data, so another query for the table that has those, // for the current account (in the array parameter) $accountaddressres = $adb->pquery("SELECT accountaddressid, ship_code, ship_street, ship_city FROM vtiger_accountshipads WHERE accountaddressid=?", array($account[accountid]));
// Loop the shipping address resultset while ($address=$adb->fetch_array($accountaddressres)) { // Add the account name and address as the JSON label $JsonAccount['label'] = $account[accountname]; $JsonAccount['value'] = $account[accountid]; $JsonAccount['code'] = $address[ship_code]; $JsonAccount['street'] = $address[ship_street]; $JsonAccount['city'] = $address[ship_city]; }
// Push the current account with address info to the 'allaccounts' array $allaccounts[] = $JsonAccount; }
Obviously this gets the job done, but is not the most elegant solution. My main problems now are:
How to get the label for the input autocomplete field to take the system variable for the 'Accounts' module. I do want to use this like this and not use the standard 'related to' because I feel this requires too many clicks. I intend to re-design the entire "Add Event" UI in the future to increase workflow speed and use more keyboard input.
I have to place the JSON_Accounts.php file in the root, because I can't include the 'Module.php' file otherwise. Joe suggested that other module files could use this becuase they use the 'set_include_path()' function, but I don't see that function being used in the standard module files. My guess is that the other files can use includes as if they were files in the root because they get passed through the index.php root file. My file is not registered by the system and so does not pass through the index.php file. Sadly, I don't know how to change this so any help would be appreciated.
I'm thinking the Calendar module already uses a 'jQuery UI' include (haven't done the full research on this), so maybe the double include could be deleted?
Probably the javascript could be moved to a separate file and included in the 'AddEventUI.php' file to make the code less messy.
In the future, I'd like to offer the possibility to connect an event to both an Account as well as a Sales Order. I think this will require a new column in the database and some code that saves this when the event is saved. If anyone has some pointers on this, help is appreciated.
I'm creating a new file in the 'module/Calendar4You/' folder. I saw that other files in that folder included PHP files from the system in respect to the system root, so I tried including Module.php like:
PHP Code:
include_once('vtlib/Vtiger/Module.php');
Which gave me an error I understood, because I had to use:
PHP Code:
include_once('../../vtlib/Vtiger/Module.php');
to tell PHP to move back two levels first. But now I receive an error when Module.php tries to include files ("ModuleBasic.php") in its turn, because it is also trying to use a relative path. How come other PHP files can include using the system root, and I can only include using a relative one? Must be an easy answer but I can't see it.
I'm in the process of creating an XLM output for accounting software. At first I wanted to base this on the 'Inventory details' module, but there's a problem. Here's what I have so far:
include_once('vtlib/Vtiger/Module.php'); global $adb;
// Create DOM document $domtree = new DOMDocument('1.0', 'UTF-8');
// Create root element $xmlRoot = $domtree->createElement('xml');
// Append XML element to root $xmlRoot = $domtree->appendChild($xmlRoot);
// Get all invoices $invoiceresult = $adb->pquery('SELECT invoiceid, subject, invoicedate, invoice_no FROM vtiger_invoice',array());
// Loop invoices while ($invoicerow=$adb->fetch_array($invoiceresult)) {
// Set empty 'previousInvoice' $previousInvoice = '';
// Get inventory detail records for this invoice $inventoryresult = $adb->pquery('SELECT productid, account_id, related_to FROM vtiger_inventorydetails WHERE related_to=?',array($invoicerow[invoiceid]));
// Loop inventory details while ($inventoryrow=$adb->fetch_array($inventoryresult)) {
// Set and append XML children to xml root element only when not the same number as the last iteration if ($inventoryrow[related_to] != $previousInvoice) { // Create the element in the DOM $currentInvoice = $domtree->createElement('invoice'); // Append it to the XML tree $currentInvoice = $xmlRoot->appendChild($currentInvoice); // Create the invoice number element in the DOM $currentInvoiceNo = $domtree->createElement('invoicenumber', $invoicerow[invoice_no]); // Append it to the current invoice $currentInvoiceNo = $currentInvoice->appendChild($currentInvoiceNo); // Set the invoice date to correct date format $date = new DateTime($invoicerow[invoicedate]); // Create the invoice date element in the DOM $currentInvoiceDate = $domtree->createElement('invoicedate', $date->format('d-m-Y')); // Append it to the current invoice $currentInvoiceDate = $currentInvoice->appendChild($currentInvoiceDate); // Create an element called 'products' $currentInvoiceProducts = $domtree->createElement('products'); // Append it to the invoice $currentInvoiceProducts = $currentInvoice->appendChild($currentInvoiceProducts);
// Add an 'account' element to the DOM $currentAccount = $domtree->createElement('accounts'); // append the account element to the current invoice $currentAccount = $currentInvoice->appendChild($currentAccount); // Select account details from the database based on account_id in inventory details line $accountresult = $adb->pquery('SELECT accountname, account_no FROM vtiger_account WHERE accountid=?',array($inventoryrow[account_id])); // Get the account's row from the query $accountName = $adb->query_result_rowdata($accountresult,0); // Create a DOM element for the current account name $currentAccountName = $domtree->createElement('accountname',$accountName[accountname]); // Append this to the currentAccount $currentAccountName = $currentAccount->appendChild($currentAccountName); // Create a DOM element for the current account no $currentAccountNo = $domtree->createElement('accountno',$accountName[account_no]); // Append this to the currentAccount $currentAccountNo = $currentAccount->appendChild($currentAccountNo); }
// Select products from the database based on product id from inventory details $productresult = $adb->pquery('SELECT productname FROM vtiger_products WHERE productid=?',array($inventoryrow[productid])); while ($product=$adb->fetch_array($productresult)) { // Create an XML dom element for each product $currentProduct = $domtree->createElement('product',$product[productname]); // Append each one to the 'CurrentProducts' element $currentProduct = $currentInvoiceProducts->appendChild($currentProduct); }
$previousInvoice = $inventoryrow[related_to];
}
}
$domtree->save('TESTXML.xml'); ?>
Which produces:
Code:
<xml><invoice><invoicenumber>20151252</invoicenumber><invoicedate>09-11-2015</invoicedate><products><product>Zak granulaat</product><product>Verpakking- en verzendkosten</product><product>Zak granulaat</product><product>Verpakking- en verzendkosten</product></products><accounts><accountname>Schurer Kamphuis</accountname><accountno>ACC962</accountno></accounts></invoice><invoice><invoicenumber>20151256</invoicenumber><invoicedate>06-11-2015</invoicedate><products><product>GVL40</product><product>Verpakking- en verzendkosten</product></products><accounts><accountname>Autobedrijf Taris</accountname><accountno>ACC1055</accountno></accounts></invoice><invoice><invoicenumber>20151261</invoicenumber><invoicedate>06-11-2015</invoicedate><products/><accounts><accountname>Autoherstel Charlois</accountname><accountno>ACC1979</accountno></accounts></invoice></xml>
That's all very nice, but as you can see the first invoice holds the same products twice. This is because I edited and saved this invoice twice. I checked the database and the Inventory details module creates a record each and every time the related module record saves. This could be fine, maybe it's the designers intended behaviour, so not a complaint about the module, but not the behaviour I want.
So I searched for a way to list products and services related to an invoice from the traditional inventory module. I found that in the inventory_products_rel table all products ever listed get a record, and products that belong to the same inventory (list) receive the same inventory number. What I can't find out is how an invoice, SO or something like this knows which inventory number to use. I need this information to list the products, prices etc. in my XML.
Okay, so I've spent a couple of hours reading the entire PearDatabase file, and have two conclusions:
- Having learned a lot about PHP the last year, I still need more knowledge.
- The $adb is the system-wide used instance of the PearDatabase class, so all object methods live there through which you can communicate with the database.
Digging further into the method use of $adb, I found this example:
Code:
// Extra function to create unique invoice_number
function getInvoiceNumber() {
global $adb;
$sql="select max(subject) as aantal from vtiger_invoice where
Year(invoicedate) = year(now())";
$result = $adb->query($sql);
$invoice_subject= $adb->query_result($result,0,"aantal");
So basically the two most basic methods are 'query' in which you perform a query, and 'query_results', that'll give you an array with results. If anyone knows more good reading sources about this, please reply with links so I can learn more about his object, since it clearly seems to be one of the most important ones of the system.
I was trying if the new corebos could process negative amounts, so I took an existing quote and filled in -1 in stead of 1. This worked (great improvement in comparison to vTiger). But when I wanted to change the amount back to 1 I got the following fatal error (debug turned on):
Code:
Fatal error: Uncaught exception 'WebServiceException' with message 'Database fout tijdens uitvoeren van de gevraagde operatie' in /home/guidog/domains/cbx-nederland.nl/public_html/crmdevelop/include/Webservices/VtigerModuleOperation.php:66 Stack trace: #0 /home/guidog/domains/cbx-nederland.nl/public_html/crmdevelop/include/Webservices/Retrieve.php(44): VtigerModuleOperation->retrieve('36x35933') #1 /home/guidog/domains/cbx-nederland.nl/public_html/crmdevelop/modules/com_vtiger_workflow/VTEntityCache.inc(17): vtws_retrieve('36x35933', Object(Users)) #2 /home/guidog/domains/cbx-nederland.nl/public_html/crmdevelop/modules/com_vtiger_workflow/VTEntityCache.inc(97): VTWorkflowEntity->__construct(Object(Users), '36x35933') #3 /home/guidog/domains/cbx-nederland.nl/public_html/crmdevelop/modules/com_vtiger_workflow/VTEventHandler.inc(45): VTEntityCache->forId('36x35933') #4 /home/guidog/domains/cbx-nederland.nl/public_html/crmdevelop/include/events/VTEventTrigger.inc(142): VTWorkflowEventHandler->handleEvent('vtiger.entity.a...', in /home/guidog/domains/cbx-nederland.nl/public_html/crmdevelop/include/Webservices/VtigerModuleOperation.php on line 66
It seems there was some database error while trying to save. I don't know if this is related to the negative amount. I see some 'workflow' mentions in the error message, but as far as I know there are no workflows to update inventory on saving of quotes.
I will proceed to try other existing quotes to see if this is a one-time thing or structural. UPDATE
I tested some other quotes the same way, no problems there. Maybe this was a one-time thing, some error for this particular quote. Still I'd like to understand the error, so if anyone has some idea...
OK, for my own educational purposes on CoreBOS and to fullfill an actual need of ours, I'd like to set up this case here and see if I studied correctly:
We'd like to be able to create a Sales Order directly from a ticket. For this I need to create a "detail View Widget" following the post here. I have to register the widget following the system's API, and fill out the URL so that in the GET superglobal the Sales Order create view starts with the GET variables I want.
I updated my CoreBOS install to the latest files, github dated 4-11-2015. I've got it all working pretty nicely, but have one problem. I used to make PDF templates that output either a product or a service, based on what was listed. I used variables for both in the same table cell, since an empty value would just not be shown.
For instance, I could use
Code:
$PRODUCT_NAME$ $SERVICE_NAME$
which would output an empty string for productname when the line is a service and an empty string for servicename when the line was a product. I did NOT update PDF maker, but the behaviour has changed, I can no longer do this because for instance when I have a line that is product, the PDF will also output $SERVICE_NAME$.
In other words, the system doesn't recognise these values as variables anymore when they're empty.
The guys at corebos did some improvements on the Calendar4You module, which are great. Just one specific thing bothered me. We use the calendar so that the planner can oversee three, four different people at once. The new calendar view changed the event layout from inline to tabled. This is fine, but when you try to watch a lot of appointments in one day the appointments get narrower and text falls completely out. To change the behaviour back to default, open
is it possible to link all documents : Quotes, Sales Order, Invoice , Purchase order , IssueCards , ReceiptCards to cbEmployee as it is now with Accounts/Contacts?
