New Deduplication Tool in BlueBox2.0

BlueBox2.0 Database Module now includes a new data deduplication tool to assist in scrubbing out annoying duplicate entries.

The new tool can be found in the Database Tools list in Admin->Database and works a follows:

• Select the class/module that requires cleansing (ie bb_users)
• Then select the fields that together or separately identify the duplicates (ie company_name and street_zipcode will find all users where the company_name and the street_zipcode are the same).
• You can also filter the results for a certain range, select how many to show and opt for 'and' or 'or' selectivity.
• When the duplicate entries in the database are displayed to you they are clustered in 'groups' and you can then select the entry in each group to keep, and thereby specify which entries to have deleted from the database. Entries that are an exact match are shown in in red to highlight severe duplications.
• Finally the system will process your requests and delete the duplicated entries, but most helpfully, it will also run through every table in the database and substitute the newly deleted entries with the correct entry you have chosen to keep, so that all your related data is automatically cleansed in the process.

Reading and Writing Data from an Excel File into BlueBox2.0

Today I will be explaining a very simple technique for reading and writing data in Excel files (xls) for use with the BlueBox2.0 platform, but this concept is generically valuable to any PHP/MySQL developer.

The Scenario:
This technique will be specifically useful for anyone who uses offline spreadsheets (xls/Excel) and wants to use these to show data from their BlueBox2.0 system in real time, and even more excitingly, wants to be able to enter/change data into their spreadsheet and have it automatically populate their BlueBox2.0 system in real time.

The Overview:
In essence what I am describing here is a simple web-query that is uploaded into a cell in your spreadsheet with a pre-defined query built into it. This query is set up to request a result from your BlueBox2.0 system. In the case of a read-only requirement, the result will be shown in the cell. In the case where you want data sent to the BlueBox2.0 system the query sends the value of another cell as part of the query, which is then processed on the server and the result is sent back to show in the cell.

For this to be most useful, I am building an example which sends data to the server, but in essence the only difference is that in the URL below you would simply leave out the bit in square-brackets.

The Ingredients:
To get this technique to work you will need the following:

• A notepad/text file on your desktop in the following format, with 3 lines, saved with the extension of .iqy
  
  WEB
  1
  http://www.yourdomain.com/?&class=bb_some_class&method=some_method&global_noincludes=rawtext&global_value=["my value"]
  
  Notes: Even though there seems to be 4 lines in my example, make sure that your notepad file is not breaking the 3rd line, as it should be one continuous line. Also: I have specifically used the new global_noincludes=rawtext option above as opposed to the usual global[noincludes]=rawtext because the URL in an iqy file cannot include square-brackets other than those specifying dynamic variables. For the same reason the value is passed as global_value and not global[value] etc.
• A BlueBox2.0 PHP class/method file set up in your system to receive the message, which would look something like this
  
  class bb_some_class extends baseclass{
          var $bbsetting_module_name="Some Class Name";
          var $bbsetting_skip_permissions=array("all");
      function some_method($conf=NULL){
          global $global,$global_value;
          resp("I got $global_value from Excel!");
      }
  }
  
  Notes: make sure you declare your bespoke global variables for this method (ie global_value etc) as these are not passing through the normal global[] name space. I also set bbsetting_skip_permissions to 'all' so that Excel can access this function easily, but this is a security concern. In a live version of this script you should add a client-side key and check the key in PHP before processing any data.

How to set it up:
Once you have the above 2 files set up, you can then open a new or existing excel spreadsheet and go to Data->Existing Connections->Browse for More. Select the .iqy file from your desktop.

You will then be asked to select the target cell for the response, and after that you will be challenged for the cell position where the script must load the value for ["my value"]. Once you have specified these 2 cells it will store the info and your cell will be automated to read/write to and from your BlueBox2.0 system.


Making it automatic:
When you are in the final stages of specify which cell to load the data from (above) you will see some checkboxes. make sure to select the 'Refresh automatically when cell value changes' option.

Draggable and Resizable Objects in BlueBox2.0

This week I want to share a new LGPL library we have used in a few BlueBox2.0 projects, by Angus Turnbull, TwinHelix Designs, http://www.twinhelix.com.

The use of this new function is to allow certain elements in your pages to be draggable and resizeable.

The small javascript library/function is called dragresize() and we have formalised it into BlueBox2.0 as follows:

To load it in your page, simply call the function inline with one of your print/resp statements as follows:

resp("

           " . dragresize("drag_me",0,0,0,0,2000,2000) . "

            <div class=drsElement style=\"position:absolute;top:200;left:200;background:silver;width:400;height:400;\">

                <div style=\"overflow:auto;width:100%;height:100%;\">

                <img src=http://www.blueboxonline.com/portal/img/2011/banners/jpegs/Specific-Product-Focused.jpg>

                </div>

            </div>

            ");

The important bits here are:

• dragresize("drag_me",0,0,0,0,2000,2000) where the 'drag_me' is a future-placeholder for scalability when this function will work for specified objects on the page, currently no used, but required. The numbers following that are max-width, max-height, left-threshold, top-threshold, right-threshold, and bottom-threshold.
•  and, all elements which you want resizable must be set with class=drsElement (for now, until we get the function to isolate to certain objects.)
• and finally, the elements which are resizable must be set with style-position of absolute or relative.

This example prints a floating box to the screen which, when clicked renders eight handles which allow you to resize the box, revealing less or more of the background image.

Currently our implementation of dragresize is for resizing only, and will only allow you to specify one set of parameters for the page. However, you can set an unlimited number of elements as class=drsElement, which will make them all resizeable.

BlueBox2.0 Surveys

Tucked away in the Publishing->WebTools menu is our original BlueBox Surveys module. This has recently been upgraded as follows:

• Link a surveys to any data in the BlueBox2.0 System:
  Using a new feature in the survey header data, you can specify whether to link it to another class/module in BB2.0. This allows you to capture surveys or data segments against other data elements in the system, ie: you could capture a customer satisfaction survey against certain sales orders or even against each customer file in the system.
  
• Create a survey and publish the direct-link using new encrypted survey id
  Surveys are now referred to via their long-id (a 32 character encrypted ID). This means that you can publish direct links to various surveys on your website or in emails and have members of the public submit data directly into the system securely.
  
• Control survey look and feel via CSS
  A new CSS template which determines that look of each element of the survey can be accessed by editing survey_css.template in the database templates module.
  
• Survey dashboard
  The survey dashboard allows you quick access to survey results as well as direct links to each survey. You can also add/edit survey data via this dashboard.
  
• Survey reporting
  You can report on survey data in detailed or summary view off the new survey dashboard.