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