Base de données d‘Electrification
Rurale et des Energies Renouvelables

Development Documentation

Descriptions of functions, database layout and all that other API stuff

How to control access to uploaded documents

A document is a collection of several data sets. In most cases the core data will be a file. Files are stored on the server under

/home/ungeo/to/uploads

While uploading a file the function pm_libs/pm_tools.php:pm_generic_store_file() processes the data sent by the upload form. It also checks the current date and puts the file to upload in a subfolder of uploads named with the current year and month.
Thus a file uploaded today would be stored under:

/home/ungeo/to/uploads/2020/07

Supplemental information and the file address will be stored in the data base table pm_files.

This files table is not shown directly in the portal, it is connected to the data set for a document. This data set, stored in pm_documents, is listed in http://at2er-portail.org/?pm_view=pm_doc&pm_section=pm_nat_nav.

The form for the upload is to be found in pm_libs/pm_forms.php:pm_form_fileup()

The document data set

Table structure:

Field	Type	Null	Key	Default	Extra
did	int(8)	NO	PRI	NULL	auto_increment
type	int(3)	NO		NULL
title	varchar(1024)	NO		NULL
description	text	NO		NULL
author	int(8)	NO		NULL
authorship_remarks	varchar(2048)	NO		NULL
institution	varchar(1024)	NO		NULL
publication_time_place	varchar(1024)	NO		NULL
volume	varchar(32)	NO		NULL
version	varchar(256)	NO		NULL
availability	int(4)	NO		NULL
creator	int(8)	NO		NULL
date	int(12)	NO		NULL
ressource	int(8)	NO		NULL
status	int(8)	NO		NULL

The value to control access is the number stored in the field availability.

The numbers in that field are defined in the table pm_attributes.

aid name desription type status parent
31 public 	... 	8 	2 	0
32 internal ... 	8 	2 	31
33 Internal ... 	8 	2 	31
34 admin 	... 	8 	2 	31

The pm_attributes holds several attribute data sets and can be extended as needed. The key value is type. The form to upload documents reads the type 8 and lists its results ordered by parent. The parent is the leading entry for the attribute list, the other entries are connected by the parent.

The ID of the attribute, named aid is the value for availability in the pm_documents table.

What do we have?

1. We know, what an attribute of type 8 means: availability.
2. That attribute is offered by the upload form to tag a document with it.
3. We can restrict the access to each document based on that number, that is available in every document dataset.

How to implement restrictions

Every user in the portal has a data set in pm_users. The field status holds a number from 0 to 10. Number 10 means Administrator(no restrictions) 0 would be User is suspended.

That status code is available for every user logged in to the portal.

This is your current data set:

string(1) "1"

This array is an ordered data set, its fields can be read individually. If you want to know about the status value, use the following code:

echo  $pm_user[0]['status'];

For convenience the system reads that value at login into a single variable.

echo  $pm_user_status;

Shows for you:

0

Thus we have that status value for the current user. We can use it when we list the documents to decide, what documents the current user shall see or not. The status can also be called a score.

We can make that decision at every point, the document is read. For now we have this in pm_custom_templates/pm_doc/pm_docs.php:

   if (isset($doc_data['document'])){
    $avail=$doc_data['document'][0]['availability'];
    //based on avail we can decide, who may see the file by using the score of the logged in user account
    //show everything for admin
     if ($pm_user_status >8){
      pm_show_single_document ($doc_data,$pm_show_options);
     }
     elseif ($pm_user_status < 8 and $pm_user_status > 4){
      if ($avail != 34){
       pm_show_single_document ($doc_data,$pm_show_options);
      }    
     }
     elseif ($pm_user_status < 5 and $pm_user_status > 1){
      if ($avail != 34 and $avail!= 33){
       pm_show_single_document ($doc_data,$pm_show_options);
      }    
     }
    }

As you see: first we check, if the document dataset is read correctly, then we can be quite sure, that the availability field holds usable data. Documents, that have undefined data at that field, shall be ignored in the portal.

Whoever has more than score/status 8 every document is shown, lesser status means stronger filtering.

Wherever a document is read or shown, this status/score value can be used to restrict reading and listing of any given single document.

Working with GIS data: SLD Styles

Background: how SLD works

SLD is basically a list of styling rules in a XML tree. Similar to CSS but with the important feature to be independant from a given Document Object Model(DOM) to select objects, that shall be styled.

In SLD the pointers to the objects are directly programmable, thus the SLD does not depend on predefined structures in the GIS layer, that shall be styled, but allows for direct styling commands to arbitrary elements of the layer, usually defined in a SHP file.

A Geographer working with say: QGIS, may select the elements in a layer and give it the tag "STATUS", for each feature of that element, the field "STATUS" may have differend values such as "Existant" or "Planned". The specialist can now determine in SLD, that a feature with STATUS=Existant shall be drawn in dark green, a feature with STATUS=Planned shall be drawn in light yellow etc.

The software, that draws the map, needst to implement these rules in any way its developers see fit. Based on the rules of the XML format and ... well: Logic.

This is where the work begins for us:

How SLD is read in the portal software

1. Connect the layer to a given SLD file
2. Read the SLD file while processing the layer
3. Apply the extracted rules to the Java Scrip code, that is passed to Leaflet to draw the layer

Step 1 is done wherever you want to draw a layer onto a map. You need the store, where the layer is registerd in GIS, the name of the layer and the name of the SLD file(the path to the file is standardised als /sld/sld/ and does not need to be used in the code, that invokes the layer).

At the point, where you want the JS for Leaflet to be rendered, you use the function /pm_libs/pm_tools.php:pm_make_leafletgisobjects_byrequest($pm_gis_layers_features,$pm_store_name,$pm_style,$options)

The Argument $pm_style can be used, to apply primitive styles such as color if you do not SLD, behold: ProjectMaps Software is friendly and forgiving ;-)

The SLD can be connected by the Argument $options.

Please be aware, that $options has to be an array, even if you pass only one value...

if (isset($options['sld'])){

Asks, if you have passed the option $options['sld'] when calling the function, its value needs to be a name of an sld file stored in /sld/sld/ . The file needs to exist and be readable by the webserver, feel free to implement further checks for that to avoid ugly error messages and dysfunctional maps in case the file is not available(deleted by you cat or not uploaded already or being named "MySLD.sld" instead "mySld.sld").

If the option is passed to the function the File is read and processed:

 if (isset($options['sld'])){
 
  $sld_file_source=$options['sld'];
  $section=$options['sld_section'];
  $sld_rules_digest=pm_sld2array($sld_file_source,$section);
 
 }

As you see, the processing demands you to pass another option: $options['sld_section']. This will be in most cases the sld_section "Rule" the interface allows you, to select other sections as well if needed, but for now, it demands you to name the wanted section explicitly. So the correct code to call the function could be like seen in the National Map (/pm_templates/pm_gis/pm_nat_map.php):

 elseif($layer_name=="reseau_ht"){
  $color="#df0808";
  $options['sld']="reseau_ht.sld";
  $options['sld_section']="Rule";
 }
 
 //[...some other code...]
 
 $pm_gis_layer_features[0]=pm_get_gis_features($layer_name);
 
 $pm_LayerGroup=pm_make_leafletgisobjects_byrequest($pm_gis_layer_features,$pm_store,$color,$options);
 

The function pm_get_gis_features($layer_name) gets the list of features for layer_name. The layer_name is taken from a list of layers, that shall be drawn on the map. Important: the options need to be individual for each layer in that list, therefore the elseif ($layer_name=="reseau_ht"): we want the SLD file reseau_ht.sld applied to that layer only, not to every given layer.

Back to pm_make_leafletgisobjects_byrequest:

If the options are passed properly, the file $options['sld'] will be read by a helper function:

$sld_rules_digest=pm_sld2array($sld_file_source,$section);

This function splits the SLD file in 3 parts, the second of these parts holds the Rule elements of its XML tree. To this part a XML parser built into PHP is applied. It returns the subelements of every Rule as a section of an array, that can be processed in PHP.

In order to get the results, as demanded in the SLD file, we need to implement the logic in the SLD in the PHP code of pm_make_leafletgisobjects_byrequest.

How to translate the demands in SLD to Java Script code for Leaflet

All this is done in pm_make_leafletgisobjects_byrequest, here we have now the digest of the rules set in the SLD, we need to implement the logic as defined by the Geographer in the SLD, that is now available as a PHP array by the name of $sld_rules_digest:

$sld_rules_digest=pm_sld2array($sld_file_source,$section);

//[...some other code...]

if(is_array($pm_gis_layers_features)){
 //read all features of the layers passed to function
 foreach($pm_gis_layers_features as $pm_feature_info){ 
 
  $layer_objects="";
  $gisoid_number=0;
 
   if(is_array($pm_feature_info)){
  foreach($pm_feature_info['features'] as $feature){  
  
   $fields="";
   $icon="smallIcon";
   $icon_file="";
   $pm_sld_icon_png="";
   if (isset($options['icon'])){
    $icon=$options['icon'];
   }
   $pm_gis_head="";
    
   foreach($feature['properties'] as $fieldname => $val){
    //properties is the realm that has to be checked for filtering 
    
    if (isset($sld_rules_digest)){
    
     if ($fieldname == "Situation"){
      
      $sld_ruled_color=explode(":",$sld_rules_digest[$val][0]);
      $sld_ruled_width=explode(":",$sld_rules_digest[$val][1]);
      $pm_style=$sld_ruled_color[1];
      $pm_width=$sld_ruled_width[1];
   
     }
     if ($fieldname == "STATUS"){
      
      $sld_ruled_color=explode(":",$sld_rules_digest[$val][0]);
      $sld_ruled_width=explode(":",$sld_rules_digest[$val][1]);
      $pm_style=$sld_ruled_color[1];
      $pm_width=$sld_ruled_width[1];
   
     }
     if ($fieldname == "Mehh"){
      
      $sld_ruled_color=explode(":",$sld_rules_digest[$val][0]);
      $sld_ruled_width=explode(":",$sld_rules_digest[$val][1]);
      $pm_style=$sld_ruled_color[1];
      $pm_width=$sld_ruled_width[1];
   
     }
     if ($fieldname == "Single symbol"){
      $sld_ruled_fill=explode(":",$sld_rules_digest[$val][0]);
      $sld_ruled_color=explode(":",$sld_rules_digest[$val][1]);
      $sld_ruled_width=explode(":",$sld_rules_digest[$val][2]);
      $pm_fill=$sld_ruled_fill[1];
      $pm_style=$sld_ruled_color[1];
      $pm_width=$sld_ruled_width[1];

     }
     if (isset($sld_rules_digest['Single symbol']['ExternalGraphic'])){
      //this is only the SVG as usually put into the sld by QGis, need to find the PNG by that
       $pm_sld_icon_svg=$sld_rules_digest['Single symbol']['ExternalGraphic'];
       $icon_file="sld/svg/"."$pm_sld_icon_svg".".png";
       if (is_file($icon_file)){
        $pm_sld_icon_png="sld/svg/"."$pm_sld_icon_svg".".png";
       
       }
      }
    }//End if isset sld rules digest
   }
 }
}

Explanation:

• Line 15 - 22: we set default values, that are used, if the SLD rules do not demand other values.
• Line 24: Every property in the Shapefile is read and its field name and value are stored in 2 variables.
• Line 27: The SLD rules are processed only, if they are successfully read from the SLD file
• Line 29: This is the point of the logical connection: if the name of the field in SHP, that is read, is a ogc:PropertyName in the SLD-XML, a temporary array is created with the PHP function explode, that takes the corresponding entry from the $sld_rules_digest.
  The next step is to read the value set in the SLD from the digest into a single variable, that will be used in the JS code for Leaflet.
  All that code is processed for each property in the features in that layer. Only, if there is the match field name in SHP == ogc:PropertyName the value is read and overwrites the defaults for $pm_style and $pm_width to set color and width of a vector line on the map.
• Line 53: Single symbol is a standard property name in SLD, it allows to set SVG Icons on the map, we do not use that for now...
  
• Line 64: if however, the Single symbol has a subelement ExternalGraphic, then we can find and use a icon file as known from the training site. Plese remember, that that file needs to be created and uploaded to the svg-folder by you, it is not available automatically.

If you need to implement another rule in a new SLD file, just insert a new code block like this:

     if ($fieldname == "NewSHPProperty"){
      
      $sld_ruled_color=explode(":",$sld_rules_digest[$val][0]);
      $sld_ruled_width=explode(":",$sld_rules_digest[$val][1]);
      $pm_style=$sld_ruled_color[1];
      $pm_width=$sld_ruled_width[1];
   
     }

If the NewSHPProperty is in the SHP processed by the code above, its style as defined in the corresponding SLD will be found and applied like so:

     var tube<?php echo $gisID_stripped?> = new L.Polygon(latLonExtraVectors<?php echo $gisoid_number?>, {
      <?php if (isset($options['noclick'])){ echo "interactive:false, ";} ?>
      color: '<?php echo $pm_style;?>',
      weight: 2,
      opacity: 0.4,
      fillColor:'<?php echo $pm_fill;?>' ,
      fillOpacity: 0.1,
      smoothFactor: 1
     });

How to import additional ODK databases

First of all, you need to import the DB into your MariaDB/MySQL DB on the server.

Create a new, empty DB with PHPMyAdmin and import the dump file "newDB.sql" (depends on the name of the new DB) in that DB.

The basic Tools to view and visualise OKD data in the Documents section are independant from ODK Aggregate. Thus it is possible, to view any given database, that is constructed and maintained by ODK.

The default ODK database is set in the file pm_libs/pm_odk_reader.php:

//read stuff from ODK Aggregate
//grabbing the db using PDO
class pm_odk_db_connection{

private $host = 'localhost';
private $dbname = 'pm_odk';
private $username = 'pm_odk';
private $password ='[REAL PASSWORD NOT DISCLOSED HERE..]';  

public $db_odk_object;

function __construct(){

    $this->connect();   

}

This allows for a multitude of methods to view other ODK databases as well, we could:

1. Implement a switch via an a Element:

      <a href="?pm_view=pm_doc&pm_section=pm_nat_nav&pm_switch_odk=otherODKdb" >Switch to otherODKdb</a>

   A click on that link would create the global variable $_GET['pm_switch_odk'] with the value otherODKdb, that could be read in pm_odk_reader.php and processed in a simple if control structure.
2. Simply copy pm_odk_reader.php, change the code in that copy as needed and clone the pm_odk_* files in pm_doc as well. Make sure to change these clones to read the copy of pm_odk_reader.php you made before.
   Just put a link in pm_doc/pm_docs.php that makes the section require your cloned files and you're good to go.

While the second method may look complex and less elegant at first glance, I still recommend it.

Why is that? Because the reader and the user frontend files in pm_doc have to be customized to read the tables in one specific ODK database. You will encounter new tables with new names and field names in every new ODK DB and the reader needs to implement that differences.

The backend: pm_odk_reader.php

The reader uses object oriented PHP, the class pm_odk_collector reads and analyses any given ODK style DB and its functions(methods) return the data for showing it in pm_odk_docs.php and pm_odk_map.php in pm_doc/.

This class should work with any ODK DB without modification. The same is true for class pm_odk_get_selected_objects.

The class pm_odk_get_connected_data and class pm_odk_analyser however are customized for specific tables that exist in the already registered default database named pm_odk in MariaDB.

Analyse the DB you import and create versions of these classes modified for the imported database. This is also needed, when you wish to have new forms and thus tables in the existing default DB pm_odk.

The frontends pm_odk_docs.php and pm_odk_map.php

Here we view the data delivered by the reader.

require ("pm_libs/pm_odk_reader.php");
//test ODK
$pm_odk_filter="";
$pm_odk_collect = new pm_odk_collector();
 
$pm_odk_tables=$pm_odk_collect->pm_odk_get_tables();

echo "<h2>Tables de l'ODK</h2>
 <ul>
";
foreach ($pm_odk_tables as $pm_odk_table){
 //pm_vardump($pm_odk_table);
 
 if ($pm_odk_table[0] == "DEMOGRAPHY4_CORE" 
  or preg_match("/DEMOGRAPHY4_*/",$pm_odk_table[0]) 
  or $pm_odk_table[0] =="BASE_LINE_PROENERGIES_CORE"
  or preg_match("/BASE_LINE_PROENERGIES_*/",$pm_odk_table[0])){

This includes the reader and creates an object from pm_odk_collector, that reads the data. In line 14 and 16 the frontend selects specific tables from the given DB. Similar code muss be written for other tables in an imported DB.

The file pm_odk_map.php has similar but more adaptions for the specific DB. Most of the grab specific field names that are relevant for the connection of data and used in flyout boxes in the map.

At the end of this file you also find a small demo for ChartJS, it visualises revenue data if available, when the vue details link is clicked in a flyout in the map.

Basics for implementing any kind of data

Any kind of datasets, be it GIS data, generic SQL databases or ODK needs to be individually analysed in order to set it up for display in the portal.

Make sure you have these points covered:

1. Find out about the structure of the data, especially dependencies of normalised datasets
2. Determine the values, that shall be visualised and analysed in the portal
3. Make a list of names and adresses in the datasets(tables, important columns
4. Write and test functions to read the data step by step, from simple to fancy
5. Evaluate special functions you may need to work with certain data items(Base64 encoded data etc)