dolibarr 21.0.3
geo.php
Go to the documentation of this file.
1<?php
2/* Copyright (c) 2004-2011 Laurent Destailleur <eldy@users.sourceforge.net>
3 * Copyright (C) 2024 MDW <mdeweerd@users.noreply.github.com>
4 * Copyright (C) 2024 Alexandre Spangaro <alexandre@inovea-conseil.com>
5 * Copyright (C) 2024 Frédéric France <frederic.france@free.fr>
6 *
7 * This program is free software; you can redistribute it and/or modify
8 * it under the terms of the GNU General Public License as published by
9 * the Free Software Foundation; either version 3 of the License, or
10 * (at your option) any later version.
11 *
12 * This program is distributed in the hope that it will be useful,
13 * but WITHOUT ANY WARRANTY; without even the implied warranty of
14 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15 * GNU General Public License for more details.
16 *
17 * You should have received a copy of the GNU General Public License
18 * along with this program. If not, see <https://www.gnu.org/licenses/>.
19 */
20
27// Load Dolibarr environment
28require '../../main.inc.php';
29require_once DOL_DOCUMENT_ROOT.'/core/lib/member.lib.php';
30require_once DOL_DOCUMENT_ROOT.'/core/lib/functions2.lib.php';
31require_once DOL_DOCUMENT_ROOT.'/core/class/dolgraph.class.php';
32require_once DOL_DOCUMENT_ROOT.'/adherents/class/adherent.class.php';
33
42$graphwidth = DolGraph::getDefaultGraphSizeForStats('width', '700');
43$mapratio = 0.5;
44$graphheight = round($graphwidth * $mapratio);
45
46$mode = GETPOST('mode') ? GETPOST('mode') : '';
47
48
49// Security check
50if ($user->socid > 0) {
51 $action = '';
52 $socid = $user->socid;
53}
54$result = restrictedArea($user, 'adherent', '', '', 'cotisation');
55
56$year = (int) dol_print_date(dol_now('gmt'), "%Y", 'gmt');
57$startyear = $year - (!getDolGlobalString('MAIN_STATS_GRAPHS_SHOW_N_YEARS') ? 2 : max(1, min(10, getDolGlobalString('MAIN_STATS_GRAPHS_SHOW_N_YEARS'))));
58$endyear = $year;
59
60// Load translation files required by the page
61$langs->loadLangs(array("companies", "members", "banks"));
62
63
64/*
65 * View
66 */
67
68$memberstatic = new Adherent($db);
69
70$arrayjs = array('https://www.google.com/jsapi');
71if (!empty($conf->dol_use_jmobile)) {
72 $arrayjs = array();
73}
74
75$title = $langs->trans("Statistics");
76if ($mode == 'memberbycountry') {
77 $title = $langs->trans("MembersStatisticsByCountries");
78}
79if ($mode == 'memberbystate') {
80 $title = $langs->trans("MembersStatisticsByState");
81}
82if ($mode == 'memberbytown') {
83 $title = $langs->trans("MembersStatisticsByTown");
84}
85if ($mode == 'memberbyregion') {
86 $title = $langs->trans("MembersStatisticsByRegion");
87}
88
89$help_url = 'EN:Module_Services_En|FR:Module_Services|ES:M&oacute;dulo_Servicios|DE:Modul_Mitglieder';
90
91llxHeader('', $title, $help_url, '', 0, 0, $arrayjs, '', '', 'mod-member page-stats_geo');
92
93print load_fiche_titre($title, '', $memberstatic->picto);
94
95//dol_mkdir($dir);
96$data = array();
97$tab = null;
98$label = '';
99
100if ($mode) {
101 // Define sql
102 $sql = null;
103 if ($mode == 'memberbycountry') {
104 $label = $langs->trans("Country");
105 $tab = 'statscountry';
106
107 $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";
108 $sql .= " FROM ".MAIN_DB_PREFIX."adherent as d";
109 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."c_country as c on d.country = c.rowid";
110 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."subscription as s ON s.fk_adherent = d.rowid";
111 $sql .= " WHERE d.entity IN (".getEntity('adherent').")";
112 $sql .= " AND d.statut <> ".Adherent::STATUS_DRAFT;
113 $sql .= " GROUP BY c.label, c.code";
114 //print $sql;
115 }
116
117 if ($mode == 'memberbystate') {
118 $label = $langs->trans("Country");
119 $label2 = $langs->trans("State");
120 $tab = 'statsstate';
121
122 $data = array();
123 $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"; //
124 $sql .= " FROM ".MAIN_DB_PREFIX."adherent as d";
125 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."c_departements as c on d.state_id = c.rowid";
126 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."c_regions as r on c.fk_region = r.code_region";
127 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."c_country as co on d.country = co.rowid";
128 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."subscription as s ON s.fk_adherent = d.rowid";
129 $sql .= " WHERE d.entity IN (".getEntity('adherent').")";
130 $sql .= " AND d.statut <> ".Adherent::STATUS_DRAFT;
131 $sql .= " GROUP BY co.label, co.code, c.nom";
132 //print $sql;
133 }
134 if ($mode == 'memberbyregion') { //
135 $label = $langs->trans("Country");
136 $label2 = $langs->trans("Region"); //département
137 $tab = 'statsregion'; //onglet
138
139 $data = array(); //tableau de donnée
140 $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";
141 $sql .= " FROM ".MAIN_DB_PREFIX."adherent as d";
142 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."c_departements as c on d.state_id = c.rowid";
143 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."c_regions as r on c.fk_region = r.code_region";
144 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."c_country as co on d.country = co.rowid";
145 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."subscription as s ON s.fk_adherent = d.rowid";
146 $sql .= " WHERE d.entity IN (".getEntity('adherent').")";
147 $sql .= " AND d.statut <> ".Adherent::STATUS_DRAFT;
148 $sql .= " GROUP BY co.label, co.code, r.nom"; //+
149 //print $sql;
150 }
151 if ($mode == 'memberbytown') {
152 $label = $langs->trans("Country");
153 $label2 = $langs->trans("Town");
154 $tab = 'statstown';
155
156 $data = array();
157 $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";
158 $sql .= " FROM ".MAIN_DB_PREFIX."adherent as d";
159 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."c_country as c on d.country = c.rowid";
160 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."subscription as s ON s.fk_adherent = d.rowid";
161 $sql .= " WHERE d.entity IN (".getEntity('adherent').")";
162 $sql .= " AND d.statut <> ".Adherent::STATUS_DRAFT;
163 $sql .= " GROUP BY c.label, c.code, d.town";
164 //print $sql;
165 }
166
167 $langsen = new Translate('', $conf);
168 $langsen->setDefaultLang('en_US');
169 $langsen->load("dict");
170 //print $langsen->trans("Country"."FI");exit;
171
172 // Define $data array
173 dol_syslog("Count member", LOG_DEBUG);
174 if ($sql != null) {
175 $resql = $db->query($sql);
176 } else {
177 $resql = false;
178 dol_syslog(__FILE__.":No SQL, invalid mode '$mode'", LOG_ERR);
179 }
180 if ($resql) {
181 $num = $db->num_rows($resql);
182 $i = 0;
183 while ($i < $num) {
184 $obj = $db->fetch_object($resql);
185 if ($mode == 'memberbycountry') {
186 $data[] = array('label' => (($obj->code && $langs->trans("Country".$obj->code) != "Country".$obj->code) ? img_picto('', DOL_URL_ROOT.'/theme/common/flags/'.strtolower($obj->code).'.png', '', 1).' '.$langs->trans("Country".$obj->code) : ($obj->label ? $obj->label : '<span class="opacitymedium">'.$langs->trans("Unknown").'</span>')),
187 'label_en' => (($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>')),
188 'code' => $obj->code,
189 'nb' => $obj->nb,
190 'lastdate' => $db->jdate($obj->lastdate),
191 'lastsubscriptiondate' => $db->jdate($obj->lastsubscriptiondate)
192 );
193 }
194 if ($mode == 'memberbyregion') { //+
195 $data[] = array(
196 'label' => (($obj->code && $langs->trans("Country".$obj->code) != "Country".$obj->code) ? img_picto('', DOL_URL_ROOT.'/theme/common/flags/'.strtolower($obj->code).'.png', '', 1).' '.$langs->trans("Country".$obj->code) : ($obj->label ? $obj->label : '<span class="opacitymedium">'.$langs->trans("Unknown").'</span>')),
197 'label_en' => (($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>')),
198 'label2' => ($obj->label2 ? $obj->label2 : '<span class="opacitymedium">'.$langs->trans("Unknown").'</span>'),
199 'nb' => $obj->nb,
200 'lastdate' => $db->jdate($obj->lastdate),
201 'lastsubscriptiondate' => $db->jdate($obj->lastsubscriptiondate)
202 );
203 }
204 if ($mode == 'memberbystate') {
205 $data[] = array('label' => (($obj->code && $langs->trans("Country".$obj->code) != "Country".$obj->code) ? img_picto('', DOL_URL_ROOT.'/theme/common/flags/'.strtolower($obj->code).'.png', '', 1).' '.$langs->trans("Country".$obj->code) : ($obj->label ? $obj->label : '<span class="opacitymedium">'.$langs->trans("Unknown").'</span>')),
206 'label_en' => (($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>')),
207 'label2' => ($obj->label2 ? $obj->label2 : '<span class="opacitymedium">'.$langs->trans("Unknown").'</span>'),
208 'nb' => $obj->nb,
209 'lastdate' => $db->jdate($obj->lastdate),
210 'lastsubscriptiondate' => $db->jdate($obj->lastsubscriptiondate)
211 );
212 }
213 if ($mode == 'memberbytown') {
214 $data[] = array('label' => (($obj->code && $langs->trans("Country".$obj->code) != "Country".$obj->code) ? img_picto('', DOL_URL_ROOT.'/theme/common/flags/'.strtolower($obj->code).'.png', '', 1).' '.$langs->trans("Country".$obj->code) : ($obj->label ? $obj->label : '<span class="opacitymedium">'.$langs->trans("Unknown").'</span>')),
215 'label_en' => (($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>')),
216 'label2' => ($obj->label2 ? $obj->label2 : '<span class="opacitymedium">'.$langs->trans("Unknown").'</span>'),
217 'nb' => $obj->nb,
218 'lastdate' => $db->jdate($obj->lastdate),
219 'lastsubscriptiondate' => $db->jdate($obj->lastsubscriptiondate)
220 );
221 }
222
223 $i++;
224 }
225 $db->free($resql);
226 } else {
227 dol_print_error($db);
228 }
229}
230
231
232$head = member_stats_prepare_head($memberstatic);
233
234print dol_get_fiche_head($head, $tab, '', -1, '');
235
236
237// Print title
238if ($mode && !count($data)) {
239 print $langs->trans("NoValidatedMemberYet").'<br>';
240 print '<br>';
241} else {
242 if ($mode == 'memberbycountry') {
243 print '<span class="opacitymedium">'.$langs->trans("MembersByCountryDesc");
244 if (getDolGlobalString("GOOGLE_SHOW_COUNTRY_GRAPH")) {
245 print $langs->trans("MembersByCountryDesc2");
246 }
247 print '</span><br>';
248 } elseif ($mode == 'memberbystate') {
249 print '<span class="opacitymedium">'.$langs->trans("MembersByStateDesc").'</span><br>';
250 } elseif ($mode == 'memberbytown') {
251 print '<span class="opacitymedium">'.$langs->trans("MembersByTownDesc").'</span><br>';
252 } elseif ($mode == 'memberbyregion') {
253 print '<span class="opacitymedium">'.$langs->trans("MembersByRegion").'</span><br>'; //+
254 } else {
255 print '<span class="opacitymedium">'.$langs->trans("MembersStatisticsDesc").'</span><br>';
256 print '<br>';
257 print '<a href="'.$_SERVER["PHP_SELF"].'?mode=memberbycountry">'.$langs->trans("MembersStatisticsByCountries").'</a><br>';
258 print '<br>';
259 print '<a href="'.$_SERVER["PHP_SELF"].'?mode=memberbystate">'.$langs->trans("MembersStatisticsByState").'</a><br>';
260 print '<br>';
261 print '<a href="'.$_SERVER["PHP_SELF"].'?mode=memberbytown">'.$langs->trans("MembersStatisticsByTown").'</a><br>';
262 print '<br>'; //+
263 print '<a href="'.$_SERVER["PHP_SELF"].'?mode=memberbyregion">'.$langs->trans("MembersStatisticsByRegion").'</a><br>'; //+
264 }
265 print '<br>';
266}
267
268
269// Show graphics
270if (getDolGlobalString("GOOGLE_SHOW_COUNTRY_GRAPH") && $mode == 'memberbycountry') {
271 global $theme_bordercolor, $theme_datacolor, $theme_bgcolor, $theme_bgcoloronglet;
272 $color_file = DOL_DOCUMENT_ROOT.'/theme/'.$conf->theme.'/theme_vars.inc.php';
273 if (is_readable($color_file)) {
274 include $color_file;
275 }
276
277 // Assume we've already included the proper headers so just call our script inline
278 // More doc: https://developers.google.com/chart/interactive/docs/gallery/geomap?hl=fr-FR
279 print "\n<script type='text/javascript'>\n";
280 print "google.load('visualization', '1', {'packages': ['geomap']});\n";
281 print "google.setOnLoadCallback(drawMap);\n";
282 print "function drawMap() {\n\tvar data = new google.visualization.DataTable();\n";
283
284 // Get the total number of rows
285 print "\tdata.addRows(".count($data).");\n";
286 print "\tdata.addColumn('string', 'Country');\n";
287 print "\tdata.addColumn('number', 'Number');\n";
288
289 // loop and dump
290 $i = 0;
291 foreach ($data as $val) {
292 $valcountry = strtoupper($val['code']); // Should be ISO-3166 code (faster)
293 //$valcountry=ucfirst($val['label_en']);
294 if ($valcountry == 'Great Britain') {
295 $valcountry = 'United Kingdom';
296 } // fix case of uk (when we use labels)
297 print "\tdata.setValue(".$i.", 0, \"".$valcountry."\");\n";
298 print "\tdata.setValue(".$i.", 1, ".$val['nb'].");\n";
299 // Google's Geomap only supports up to 400 entries
300 if ($i >= 400) {
301 break;
302 }
303 $i++;
304 }
305
306 print "\tvar options = {};\n";
307 print "\toptions['dataMode'] = 'regions';\n";
308 print "\toptions['showZoomOut'] = false;\n";
309 //print "\toptions['zoomOutLabel'] = '".dol_escape_js($langs->transnoentitiesnoconv("Numbers"))."';\n";
310 print "\toptions['width'] = ".$graphwidth.";\n";
311 print "\toptions['height'] = ".$graphheight.";\n";
312 print "\toptions['colors'] = [0x".colorArrayToHex($theme_datacolor[1], 'BBBBBB').", 0x".colorArrayToHex($theme_datacolor[0], '444444')."];\n";
313 print "\tvar container = document.getElementById('".$mode."');\n";
314 print "\tvar geomap = new google.visualization.GeoMap(container);\n";
315 print "\tgeomap.draw(data, options);\n";
316 print "}\n";
317 print "</script>\n";
318
319 // print the div tag that will contain the map
320 print '<div class="center" id="'.$mode.'"></div>'."\n";
321}
322
323if ($mode) {
324 // Print array
325 print '<div class="div-table-responsive">'; // You can use div-table-responsive-no-min if you don't need reserved height for your table
326 print '<table class="liste centpercent noborder">';
327 print '<tr class="liste_titre">';
328 print '<th>'.$label.'</th>';
329 if (isset($label2)) {
330 print '<th class="center">'.$label2.'</th>';
331 }
332 print '<th class="right">'.$langs->trans("NbOfMembers").' <span class="opacitymedium">('.$langs->trans("AllTime").')</span></th>';
333 print '<th class="center">'.$langs->trans("LastMemberDate").'</th>';
334 print '<th class="center">'.$langs->trans("LatestSubscriptionDate").'</th>';
335 print '</tr>';
336
337 foreach ($data as $val) {
338 print '<tr class="oddeven">';
339 print '<td>'.$val['label'].'</td>';
340 if (isset($label2)) {
341 print '<td class="center">'.$val['label2'].'</td>';
342 }
343 print '<td class="right">'.$val['nb'].'</td>';
344 print '<td class="center">'.dol_print_date($val['lastdate'], 'dayhour').'</td>';
345 print '<td class="center">'.dol_print_date($val['lastsubscriptiondate'], 'dayhour').'</td>';
346 print '</tr>';
347 }
348
349 print '</table>';
350 print '</div>';
351}
352
353
354print dol_get_fiche_end();
355
356// End of page
357llxFooter();
358$db->close();
llxFooter
llxFooter($comment='', $zone='private', $disabledoutputofmessages=0)
Empty footer.
Definition wrapper.php:87
llxHeader
if(!defined('NOREQUIRESOC')) if(!defined( 'NOREQUIRETRAN')) if(!defined('NOTOKENRENEWAL')) if(!defined( 'NOREQUIREMENU')) if(!defined('NOREQUIREHTML')) if(!defined( 'NOREQUIREAJAX')) llxHeader($head='', $title='', $help_url='', $target='', $disablejs=0, $disablehead=0, $arrayofjs='', $arrayofcss='', $morequerystring='', $morecssonbody='', $replacemainareaby='', $disablenofollow=0, $disablenoindex=0)
Empty header.
Definition wrapper.php:71
Adherent
Class to manage members of a foundation.
Definition adherent.class.php:49
DolGraph\getDefaultGraphSizeForStats
static getDefaultGraphSizeForStats($direction, $defaultsize='')
getDefaultGraphSizeForStats
Definition dolgraph.class.php:1691
Translate
Class to manage translations.
Definition translate.class.php:33
colorArrayToHex
colorArrayToHex($arraycolor, $colorifnotfound='888888')
Convert an array with RGB value into hex RGB value.
Definition functions2.lib.php:2392
load_fiche_titre
load_fiche_titre($title, $morehtmlright='', $picto='generic', $pictoisfullpath=0, $id='', $morecssontable='', $morehtmlcenter='')
Load a title with picto.
Definition functions.lib.php:6541
img_picto
img_picto($titlealt, $picto, $moreatt='', $pictoisfullpath=0, $srconly=0, $notitle=0, $alt='', $morecss='', $marginleftonlyshort=2, $allowothertags=array())
Show picto whatever it's its name (generic function)
Definition functions.lib.php:5159
dol_get_fiche_head
dol_get_fiche_head($links=array(), $active='', $title='', $notab=0, $picto='', $pictoisfullpath=0, $morehtmlright='', $morecss='', $limittoshow=0, $moretabssuffix='', $dragdropfile=0)
Show tabs of a record.
Definition functions.lib.php:2769
dol_get_fiche_end
dol_get_fiche_end($notab=0)
Return tab footer of a card.
Definition functions.lib.php:2963
dol_now
dol_now($mode='auto')
Return date for now.
Definition functions.lib.php:3904
dol_print_date
dol_print_date($time, $format='', $tzoutput='auto', $outputlangs=null, $encodetooutput=false)
Output date in a string format according to outputlangs (or langs if not defined).
Definition functions.lib.php:3515
GETPOST
GETPOST($paramname, $check='alphanohtml', $method=0, $filter=null, $options=null, $noreplace=0)
Return value of a param into GET or POST supervariable.
Definition functions.lib.php:802
dol_print_error
dol_print_error($db=null, $error='', $errors=null)
Displays error message system with all the information to facilitate the diagnosis and the escalation...
Definition functions.lib.php:6167
getDolGlobalString
getDolGlobalString($key, $default='')
Return a Dolibarr global constant string value.
Definition functions.lib.php:228
dol_syslog
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='', $logcontext=null)
Write log message into outputs.
Definition functions.lib.php:2382
member_stats_prepare_head
member_stats_prepare_head($object)
Return array head with list of tabs to view object stats information.
Definition member.lib.php:284
$conf
global $conf
The following vars must be defined: $type2label $form $conf, $lang, The following vars may also be de...
Definition member.php:79
restrictedArea
restrictedArea(User $user, $features, $object=0, $tableandshare='', $feature2='', $dbt_keyfield='fk_soc', $dbt_select='rowid', $isdraft=0, $mode=0)
Check permissions of a user to show a page and an object.
Definition security.lib.php:429
Generated on Wed Aug 6 2025 01:00:15 for dolibarr by Doxygen 1.11.0