geo.php

Go to the documentation of this file.

<?php
/* Copyright (c) 2004-2011  Laurent Destailleur     <eldy@users.sourceforge.net>
 * Copyright (C) 2024-2025  MDW                     <mdeweerd@users.noreply.github.com>
 * Copyright (C) 2024   Alexandre Spangaro    <alexandre@inovea-conseil.com>
 * Copyright (C) 2024-2026  Frédéric France     <frederic.france@free.fr>
 *
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program. If not, see <https://www.gnu.org/licenses/>.
 */
 
// Load Dolibarr environment
require '../../main.inc.php';
require_once DOL_DOCUMENT_ROOT.'/core/lib/member.lib.php';
require_once DOL_DOCUMENT_ROOT.'/core/lib/functions2.lib.php';
require_once DOL_DOCUMENT_ROOT.'/core/class/dolgraph.class.php';
require_once DOL_DOCUMENT_ROOT.'/adherents/class/adherent.class.php';
 
$graphwidth = DolGraph::getDefaultGraphSizeForStats('width', '700');
$mapratio = 0.5;
$graphheight = round($graphwidth * $mapratio);
 
$mode = GETPOST('mode');
if (empty($mode)) {
  $mode = 'memberbycountry';
}
 
// Security check
if ($user->socid > 0) {
  $action = '';
  $socid = $user->socid;
}
restrictedArea($user, 'adherent', '', '', 'cotisation');
 
$year = (int) dol_print_date(dol_now('gmt'), "%Y", 'gmt');
$startyear = $year - (getDolGlobalString('MAIN_STATS_GRAPHS_SHOW_N_YEARS') ? max(1, min(10, getDolGlobalString('MAIN_STATS_GRAPHS_SHOW_N_YEARS'))) : 2);
$endyear = $year;
 
// Load translation files required by the page
$langs->loadLangs(array("companies", "members", "banks"));
 
 
/*
 * View
 */
 
$memberstatic = new Adherent($db);
 
$arrayjs = array('https://www.google.com/jsapi');
if (!empty($conf->dol_use_jmobile)) {
  $arrayjs = array();
}
 
$title = $langs->trans("Members");
 
$help_url = 'EN:Module_Services_En|FR:Module_Services|ES:M&oacute;dulo_Servicios|DE:Modul_Mitglieder';
 
llxHeader('', $title, $help_url, '', 0, 0, $arrayjs, '', '', 'mod-member page-stats_geo');
 
$param = '';
 
$newcardbutton = '';
$queryforbutton = array();
$queryforbutton['mode'] = 'common';
$newcardbutton .= dolGetButtonTitle($langs->trans('ViewList'), '', 'fa fa-bars imgforviewmode', dolBuildUrl(DOL_URL_ROOT.'/adherents/list.php', $queryforbutton), '', ((empty($mode) || $mode == 'common') ? 2 : 1), array('morecss' => 'reposition'));
$queryforbutton['mode'] = 'kanban';
$newcardbutton .= dolGetButtonTitle($langs->trans('ViewKanban'), '', 'fa fa-th-list imgforviewmode', dolBuildUrl(DOL_URL_ROOT.'/adherents/list.php', $queryforbutton), '', ($mode == 'kanban' ? 2 : 1), array('morecss' => 'reposition'));
$newcardbutton .= dolGetButtonTitle($langs->trans('Statistics'), '', 'fa fa-chart-bar imgforviewmode', dol_buildpath('/adherents/stats/index.php', 1).'?objecttype=adherent@adherent'.preg_replace('/(&|\?)*(mode|groupby)=[^&]+/', '', $param), '', 2, array('morecss' => 'reposition'));
$newcardbutton .= dolGetButtonTitleSeparator();
$newcardbutton .= dolGetButtonTitle($langs->trans('NewMember'), '', 'fa fa-plus-circle', dolBuildUrl(DOL_URL_ROOT.'/adherents/card.php', ['action' => 'create']), '', $user->hasRight('adherent', 'creer'));
 
print_barre_liste($title, 0, $_SERVER["PHP_SELF"], $param, '', '', '', 0, $langs->trans("Statistics"), $memberstatic->picto, 0, $newcardbutton, '', 0, 0, 0, 1);
 
//dol_mkdir($dir);
$data = array();
$tab = null;
$label = '';
 
// Define sql
$sql = null;
if ($mode == 'memberbycountry') {
  $label = $langs->trans("Country");
  $tab = 'statscountry';
 
  $sql = "SELECT COUNT(DISTINCT d.rowid) as nb, COUNT(s.rowid) as nbsubscriptions, MAX(d.datevalid) as lastdate, MAX(s.dateadh) as lastsubscriptiondate, c.code, c.label";
  $sql .= " FROM ".MAIN_DB_PREFIX."adherent as d";
  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."c_country as c on d.country = c.rowid";
  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."subscription as s ON s.fk_adherent = d.rowid";
  $sql .= " WHERE d.entity IN (".getEntity('adherent').")";
  $sql .= " AND d.statut <> ".Adherent::STATUS_DRAFT;
  $sql .= " GROUP BY c.label, c.code";
} elseif ($mode == 'memberbystate') {
  $label = $langs->trans("Country");
  $label2 = $langs->trans("State");
  $tab = 'statsstate';
 
  $data = array();
  $sql = "SELECT COUNT(DISTINCT d.rowid) as nb, COUNT(s.rowid) as nbsubscriptions, MAX(d.datevalid) as lastdate, MAX(s.dateadh) as lastsubscriptiondate, co.code, co.label, c.nom as label2"; //
  $sql .= " FROM ".MAIN_DB_PREFIX."adherent as d";
  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."c_departements as c on d.state_id = c.rowid";
  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."c_regions as r on c.fk_region = r.code_region";
  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."c_country as co on d.country = co.rowid";
  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."subscription as s ON s.fk_adherent = d.rowid";
  $sql .= " WHERE d.entity IN (".getEntity('adherent').")";
  $sql .= " AND d.statut <> ".Adherent::STATUS_DRAFT;
  $sql .= " GROUP BY co.label, co.code, c.nom";
  //print $sql;
} elseif ($mode == 'memberbyregion') {
  $label = $langs->trans("Country");
  $label2 = $langs->trans("Region"); //département
  $tab = 'statsregion'; //onglet
 
  $data = array(); //tableau de donnée
  $sql = "SELECT COUNT(DISTINCT d.rowid) as nb, COUNT(s.rowid) as nbsubscriptions, MAX(d.datevalid) as lastdate, MAX(s.dateadh) as lastsubscriptiondate, co.code, co.label, r.nom as label2";
  $sql .= " FROM ".MAIN_DB_PREFIX."adherent as d";
  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."c_departements as c on d.state_id = c.rowid";
  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."c_regions as r on c.fk_region = r.code_region";
  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."c_country as co on d.country = co.rowid";
  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."subscription as s ON s.fk_adherent = d.rowid";
  $sql .= " WHERE d.entity IN (".getEntity('adherent').")";
  $sql .= " AND d.statut <> ".Adherent::STATUS_DRAFT;
  $sql .= " GROUP BY co.label, co.code, r.nom"; //+
} elseif ($mode == 'memberbytown') {
  $label = $langs->trans("Country");
  $label2 = $langs->trans("Town");
  $tab = 'statstown';
 
  $data = array();
  $sql = "SELECT COUNT(DISTINCT d.rowid) as nb, COUNT(s.rowid) as nbsubscriptions, MAX(d.datevalid) as lastdate, MAX(s.dateadh) as lastsubscriptiondate, c.code, c.label, d.town as label2";
  $sql .= " FROM ".MAIN_DB_PREFIX."adherent as d";
  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."c_country as c on d.country = c.rowid";
  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."subscription as s ON s.fk_adherent = d.rowid";
  $sql .= " WHERE d.entity IN (".getEntity('adherent').")";
  $sql .= " AND d.statut <> ".Adherent::STATUS_DRAFT;
  $sql .= " GROUP BY c.label, c.code, d.town";
}
 
$langsen = new Translate('', $conf);
$langsen->setDefaultLang('en_US');
$langsen->load("dict");
//print $langsen->trans("Country"."FI");exit;
 
// Define $data array
dol_syslog("Count member", LOG_DEBUG);
if ($sql != null) {
  $resql = $db->query($sql);
} else {
  $resql = false;
  dol_syslog(__FILE__.":No SQL, invalid mode '$mode'", LOG_ERR);
}
if ($resql) {
  $num = $db->num_rows($resql);
  $i = 0;
  while ($i < $num) {
    $obj = $db->fetch_object($resql);
    if ($mode == 'memberbycountry') {
      $data[] = array(
        'label' => (string) (($obj->code && $langs->trans("Country".$obj->code) != "Country".$obj->code) ? picto_from_langcode($obj->code, 'class="saturatemedium paddingrightonly"', 1).' '.$langs->trans("Country".$obj->code) : ($obj->label ? $obj->label : '<span class="opacitymedium">'.$langs->trans("Unknown").'</span>')),
        'label_en' => (string) (($obj->code && $langsen->transnoentitiesnoconv("Country".$obj->code) != "Country".$obj->code) ? $langsen->transnoentitiesnoconv("Country".$obj->code) : ($obj->label ? $obj->label : '<span class="opacitymedium">'.$langs->trans("Unknown").'</span>')),
        'code' => (string) $obj->code,
        'nb' => (int) $obj->nb,
        'lastdate' => $db->jdate($obj->lastdate),
        'lastsubscriptiondate' => $db->jdate($obj->lastsubscriptiondate)
      );
    } elseif ($mode == 'memberbyregion') { //+
      $data[] = array(
        'label' => (string) (($obj->code && $langs->trans("Country".$obj->code) != "Country".$obj->code) ? picto_from_langcode($obj->code, 'class="saturatemedium paddingrightonly"', 1).' '.$langs->trans("Country".$obj->code) : ($obj->label ? $obj->label : '<span class="opacitymedium">'.$langs->trans("Unknown").'</span>')),
        'label_en' => (string) (($obj->code && $langsen->transnoentitiesnoconv("Country".$obj->code) != "Country".$obj->code) ? $langsen->transnoentitiesnoconv("Country".$obj->code) : ($obj->label ? $obj->label : '<span class="opacitymedium">'.$langs->trans("Unknown").'</span>')),
        'label2' => ($obj->label2 ? $obj->label2 : '<span class="opacitymedium">'.$langs->trans("Unknown").'</span>'),
        'nb' => (int) $obj->nb,
        'lastdate' => $db->jdate($obj->lastdate),
        'lastsubscriptiondate' => $db->jdate($obj->lastsubscriptiondate)
      );
    } elseif ($mode == 'memberbystate') {
      $data[] = array(
        'label' => (string) (($obj->code && $langs->trans("Country".$obj->code) != "Country".$obj->code) ? picto_from_langcode($obj->code, 'class="saturatemedium paddingrightonly"', 1).' '.$langs->trans("Country".$obj->code) : ($obj->label ? $obj->label : '<span class="opacitymedium">'.$langs->trans("Unknown").'</span>')),
        'label_en' => (string) (($obj->code && $langsen->transnoentitiesnoconv("Country".$obj->code) != "Country".$obj->code) ? $langsen->transnoentitiesnoconv("Country".$obj->code) : ($obj->label ? $obj->label : '<span class="opacitymedium">'.$langs->trans("Unknown").'</span>')),
        'label2' => ($obj->label2 ? $obj->label2 : '<span class="opacitymedium">'.$langs->trans("Unknown").'</span>'),
        'nb' => (int) $obj->nb,
        'lastdate' => $db->jdate($obj->lastdate),
        'lastsubscriptiondate' => $db->jdate($obj->lastsubscriptiondate)
      );
    } elseif ($mode == 'memberbytown') {
      $data[] = array(
        'label' => (string) (($obj->code && $langs->trans("Country".$obj->code) != "Country".$obj->code) ? picto_from_langcode($obj->code, 'class="saturatemedium paddingrightonly"', 1).' '.$langs->trans("Country".$obj->code) : ($obj->label ? $obj->label : '<span class="opacitymedium">'.$langs->trans("Unknown").'</span>')),
        'label_en' => (string) (($obj->code && $langsen->transnoentitiesnoconv("Country".$obj->code) != "Country".$obj->code) ? $langsen->transnoentitiesnoconv("Country".$obj->code) : ($obj->label ? $obj->label : '<span class="opacitymedium">'.$langs->trans("Unknown").'</span>')),
        'label2' => (string) ($obj->label2 ? $obj->label2 : '<span class="opacitymedium">'.$langs->trans("Unknown").'</span>'),
        'nb' => (int) $obj->nb,
        'lastdate' => $db->jdate($obj->lastdate),
        'lastsubscriptiondate' => $db->jdate($obj->lastsubscriptiondate)
      );
    }
 
    $i++;
  }
  $db->free($resql);
} else {
  dol_print_error($db);
}
 
 
$head = member_stats_prepare_head($memberstatic);
 
print dol_get_fiche_head($head, (string) $tab, '', -1, '');
 
 
// Print title
if (!count($data)) {
  print '<span class="opacitymedium">'.$langs->trans("NoValidatedMemberYet").'</span><br>';
  print '<br>';
} else {
  if (empty($mode) || $mode == 'memberbycountry') {
    print '<span class="opacitymedium">'.$langs->trans("MembersByCountryDesc");
    if (getDolGlobalString("GOOGLE_SHOW_COUNTRY_GRAPH")) {
      print $langs->trans("MembersByCountryDesc2");
    }
    print '</span><br>';
  } elseif ($mode == 'memberbystate') {
    print '<span class="opacitymedium">'.$langs->trans("MembersByStateDesc").'</span><br>';
  } elseif ($mode == 'memberbytown') {
    print '<span class="opacitymedium">'.$langs->trans("MembersByTownDesc").'</span><br>';
  } elseif ($mode == 'memberbyregion') {
    print '<span class="opacitymedium">'.$langs->trans("MembersByRegion").'</span><br>'; //+
  }
  print '<span class="opacitymedium">'.$langs->trans("DraftMembersAreExcluded").'</span><br>';
  print '<br>';
}
 
 
// Show graphics
if (getDolGlobalString("GOOGLE_SHOW_COUNTRY_GRAPH") && $mode == 'memberbycountry') {
  global $theme_bordercolor, $theme_datacolor, $theme_bgcolor, $theme_bgcoloronglet;
  $color_file = DOL_DOCUMENT_ROOT.'/theme/'.$conf->theme.'/theme_vars.inc.php';
  if (is_readable($color_file)) {
    include $color_file;
  }
 
  // Assume we've already included the proper headers so just call our script inline
  // More doc: https://developers.google.com/chart/interactive/docs/gallery/geomap?hl=fr-FR
  print "\n<script type='text/javascript'>\n";
  print "google.load('visualization', '1', {'packages': ['geomap']});\n";
  print "google.setOnLoadCallback(drawMap);\n";
  print "function drawMap() {\n\tvar data = new google.visualization.DataTable();\n";
 
  // Get the total number of rows
  print "\tdata.addRows(".count($data).");\n";
  print "\tdata.addColumn('string', 'Country');\n";
  print "\tdata.addColumn('number', 'Number');\n";
 
  // loop and dump
  $i = 0;
  foreach ($data as $val) {
    $valcountry = strtoupper($val['code']); // Should be ISO-3166 code (faster)
    // $valcountry = ucfirst($val['label_en']);
    // if ($valcountry == 'Great Britain') {
    //  $valcountry = 'United Kingdom';
    // }    // fix case of uk (when we use labels)
    print "\tdata.setValue(".$i.", 0, \"".$valcountry."\");\n";
    print "\tdata.setValue(".$i.", 1, ".$val['nb'].");\n";
    // Google's Geomap only supports up to 400 entries
    if ($i >= 400) {
      break;
    }
    $i++;
  }
 
  print "\tvar options = {};\n";
  print "\toptions['dataMode'] = 'regions';\n";
  print "\toptions['showZoomOut'] = false;\n";
  //print "\toptions['zoomOutLabel'] = '".dol_escape_js($langs->transnoentitiesnoconv("Numbers"))."';\n";
  print "\toptions['width'] = ".$graphwidth.";\n";
  print "\toptions['height'] = ".$graphheight.";\n";
  print "\toptions['colors'] = [0x".colorArrayToHex($theme_datacolor[1], 'BBBBBB').", 0x".colorArrayToHex($theme_datacolor[0], '444444')."];\n";
  print "\tvar container = document.getElementById('".$mode."');\n";
  print "\tvar geomap = new google.visualization.GeoMap(container);\n";
  print "\tgeomap.draw(data, options);\n";
  print "}\n";
  print "</script>\n";
 
  // print the div tag that will contain the map
  print '<div class="center" id="'.$mode.'"></div>'."\n";
}
 
// Print array
print '<div class="div-table-responsive">'; // You can use div-table-responsive-no-min if you don't need reserved height for your table
print '<table class="liste centpercent noborder">';
print '<tr class="liste_titre">';
print '<th>'.$label.'</th>';
if (isset($label2)) {
  print '<th class="center">'.$label2.'</th>';
}
print '<th class="right">'.$langs->trans("NbOfMembers").' <span class="opacitymedium">('.$langs->trans("AllTime").')</span></th>';
print '<th class="center">'.$langs->trans("LastMemberDate").'</th>';
print '<th class="center">'.$langs->trans("LatestSubscriptionDate").'</th>';
print '</tr>';
 
foreach ($data as $val) {
  print '<tr class="oddeven">';
  print '<td>'.$val['label'].'</td>';
  if (isset($label2)) {
    print '<td class="center">'.$val['label2'].'</td>';
  }
  print '<td class="right">'.$val['nb'].'</td>';
  print '<td class="center">'.dol_print_date($val['lastdate'], 'dayhour', 'auto', null, false, 1).'</td>';
  print '<td class="center">'.dol_print_date($val['lastsubscriptiondate'], 'dayhour', 'auto', null, false, 1).'</td>';
  print '</tr>';
}
 
print '</table>';
print '</div>';
 
 
print dol_get_fiche_end();
 
// End of page
llxFooter();
$db->close();