dolibarr 21.0.0-beta
adherentstats.class.php
Go to the documentation of this file.
1<?php
2/* Copyright (C) 2003 Rodolphe Quiedeville <rodolphe@quiedeville.org>
3 * Copyright (c) 2005-2011 Laurent Destailleur <eldy@users.sourceforge.net>
4 * Copyright (C) 2005-2009 Regis Houssin <regis.houssin@inodbox.com>
5 * Copyright (C) 2023 Waël Almoman <info@almoman.com>
6 * Copyright (C) 2024 MDW <mdeweerd@users.noreply.github.com>
7 * Copyright (C) 2024 Frédéric France <frederic.france@free.fr>
8 *
9 * This program is free software; you can redistribute it and/or modify
10 * it under the terms of the GNU General Public License as published by
11 * the Free Software Foundation; either version 3 of the License, or
12 * (at your option) any later version.
13 *
14 * This program is distributed in the hope that it will be useful,
15 * but WITHOUT ANY WARRANTY; without even the implied warranty of
16 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
17 * GNU General Public License for more details.
18 *
19 * You should have received a copy of the GNU General Public License
20 * along with this program. If not, see <https://www.gnu.org/licenses/>.
21 */
22
29include_once DOL_DOCUMENT_ROOT.'/core/class/stats.class.php';
30include_once DOL_DOCUMENT_ROOT.'/adherents/class/subscription.class.php';
31
32
36class AdherentStats extends Stats
37{
41 public $table_element;
42
46 public $memberid;
50 public $socid;
54 public $userid;
55
59 public $from;
63 public $field;
67 public $where;
68
69
77 public function __construct($db, $socid = 0, $userid = 0)
78 {
79 $this->db = $db;
80 $this->socid = $socid;
81 $this->userid = $userid;
82
83 $object = new Subscription($this->db);
84
85 $this->from = MAIN_DB_PREFIX.$object->table_element." as p";
86 $this->from .= ", ".MAIN_DB_PREFIX."adherent as m";
87
88 $this->field = 'subscription';
89
90 $this->where .= " m.statut != -1";
91 $this->where .= " AND p.fk_adherent = m.rowid AND m.entity IN (".getEntity('adherent').")";
92 if ($this->memberid) {
93 $this->where .= " AND m.rowid = ".((int) $this->memberid);
94 }
95 //if ($this->userid > 0) $this->where .= " AND fk_user_author = ".((int) $this->userid);
96 }
97
98
106 public function getNbByMonth($year, $format = 0)
107 {
108 $sql = "SELECT date_format(p.dateadh,'%m') as dm, count(*)";
109 $sql .= " FROM ".$this->from;
110 $sql .= " WHERE ".dolSqlDateFilter('p.dateadh', 0, 0, (int) $year, 1);
111 $sql .= " AND ".$this->where;
112 $sql .= " GROUP BY dm";
113 $sql .= $this->db->order('dm', 'DESC');
114
115 return $this->_getNbByMonth($year, $sql, $format);
116 }
117
123 public function getNbByYear()
124 {
125 $sql = "SELECT date_format(p.dateadh,'%Y') as dm, count(*)";
126 $sql .= " FROM ".$this->from;
127 $sql .= " WHERE ".$this->where;
128 $sql .= " GROUP BY dm";
129 $sql .= $this->db->order('dm', 'DESC');
130
131 return $this->_getNbByYear($sql);
132 }
133
141 public function getAmountByMonth($year, $format = 0)
142 {
143 $sql = "SELECT date_format(p.dateadh,'%m') as dm, sum(p.".$this->field.")";
144 $sql .= " FROM ".$this->from;
145 $sql .= " WHERE ".dolSqlDateFilter('p.dateadh', 0, 0, (int) $year, 1);
146 $sql .= " AND ".$this->where;
147 $sql .= " GROUP BY dm";
148 $sql .= $this->db->order('dm', 'DESC');
149
150 return $this->_getAmountByMonth($year, $sql, $format);
151 }
152
159 public function getAverageByMonth($year)
160 {
161 $sql = "SELECT date_format(p.dateadh,'%m') as dm, avg(p.".$this->field.")";
162 $sql .= " FROM ".$this->from;
163 $sql .= " WHERE ".dolSqlDateFilter('p.dateadh', 0, 0, (int) $year, 1);
164 $sql .= " AND ".$this->where;
165 $sql .= " GROUP BY dm";
166 $sql .= $this->db->order('dm', 'DESC');
167
168 return $this->_getAverageByMonth($year, $sql);
169 }
170
171
177 public function getAllByYear()
178 {
179 $sql = "SELECT date_format(p.dateadh,'%Y') as year, count(*) as nb, sum(".$this->field.") as total, avg(".$this->field.") as avg";
180 $sql .= " FROM ".$this->from;
181 $sql .= " WHERE ".$this->where;
182 $sql .= " GROUP BY year";
183 $sql .= $this->db->order('year', 'DESC');
184
185 return $this->_getAllByYear($sql);
186 }
187
194 public function countMembersByTypeAndStatus($numberYears = 0)
195 {
196 global $user;
197
198 $now = dol_now();
199 $endYear = (int) date('Y');
200 $startYear = $endYear - $numberYears;
201
202 $sql = "SELECT t.rowid as fk_adherent_type, t.libelle as label";
203 $sql .= ", COUNT(".$this->db->ifsql("d.statut = ".Adherent::STATUS_DRAFT, "'members_draft'", 'NULL').") as members_draft";
204 $sql .= ", COUNT(".$this->db->ifsql("d.statut = ".Adherent::STATUS_VALIDATED." AND (d.datefin IS NULL AND t.subscription = '1')", "'members_pending'", 'NULL').") as members_pending";
205 $sql .= ", COUNT(".$this->db->ifsql("d.statut = ".Adherent::STATUS_VALIDATED." AND (d.datefin >= '".$this->db->idate($now)."' OR t.subscription = 0)", "'members_uptodate'", 'NULL').") as members_uptodate";
206 $sql .= ", COUNT(".$this->db->ifsql("d.statut = ".Adherent::STATUS_VALIDATED." AND (d.datefin < '".$this->db->idate($now)."' AND t.subscription = 1)", "'members_expired'", 'NULL').") as members_expired";
207 $sql .= ", COUNT(".$this->db->ifsql("d.statut = ".Adherent::STATUS_EXCLUDED, "'members_excluded'", 'NULL').") as members_excluded";
208 $sql .= ", COUNT(".$this->db->ifsql("d.statut = ".Adherent::STATUS_RESILIATED, "'members_resiliated'", 'NULL').") as members_resiliated";
209 $sql .= " FROM ".MAIN_DB_PREFIX."adherent_type as t";
210 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."adherent as d ON t.rowid = d.fk_adherent_type AND d.entity IN (" . getEntity('adherent') . ")";
211 if ($numberYears) {
212 $sql .= " AND d.datefin > '".$this->db->idate(dol_get_first_day($startYear))."'";
213 }
214 $sql .= " WHERE t.entity IN (".getEntity('member_type').")";
215 $sql .= " AND t.statut = 1";
216 $sql .= " GROUP BY t.rowid, t.libelle";
217
218 dol_syslog("box_members_by_type::select nb of members per type", LOG_DEBUG);
219 $result = $this->db->query($sql);
220
221 $MembersCountArray = array();
222
223 if ($result) {
224 $num = $this->db->num_rows($result);
225 $i = 0;
226 $totalstatus = array(
227 'members_draft' => 0,
228 'members_pending' => 0,
229 'members_uptodate' => 0,
230 'members_expired' => 0,
231 'members_excluded' => 0,
232 'members_resiliated' => 0
233 );
234 while ($i < $num) {
235 $objp = $this->db->fetch_object($result);
236 $MembersCountArray[$objp->fk_adherent_type] = array(
237 'label' => $objp->label,
238 'members_draft' => (int) $objp->members_draft,
239 'members_pending' => (int) $objp->members_pending,
240 'members_uptodate' => (int) $objp->members_uptodate,
241 'members_expired' => (int) $objp->members_expired,
242 'members_excluded' => (int) $objp->members_excluded,
243 'members_resiliated' => (int) $objp->members_resiliated
244 );
245 $totalrow = 0;
246 foreach ($MembersCountArray[$objp->fk_adherent_type] as $key => $nb) {
247 if ($key != 'label') {
248 $totalrow += $nb;
249 $totalstatus[$key] += $nb;
250 }
251 }
252 $MembersCountArray[$objp->fk_adherent_type]['total_adhtype'] = $totalrow;
253 $i++;
254 }
255 $this->db->free($result);
256 $MembersCountArray['total'] = $totalstatus;
257 $MembersCountArray['total']['all'] = array_sum($totalstatus);
258 }
259
260 return $MembersCountArray;
261 }
262
269 public function countMembersByTagAndStatus($numberYears = 0)
270 {
271 global $user;
272
273 $now = dol_now();
274 $endYear = (int) date('Y');
275 $startYear = $endYear - $numberYears;
276 $MembersCountArray = [];
277
278 $sql = "SELECT c.rowid as fk_categorie, c.label as label";
279 $sql .= ", COUNT(".$this->db->ifsql("d.statut = ".Adherent::STATUS_DRAFT, "'members_draft'", 'NULL').") as members_draft";
280 $sql .= ", COUNT(".$this->db->ifsql("d.statut = ".Adherent::STATUS_VALIDATED." AND (d.datefin IS NULL AND t.subscription = '1')", "'members_pending'", 'NULL').") as members_pending";
281 $sql .= ", COUNT(".$this->db->ifsql("d.statut = ".Adherent::STATUS_VALIDATED." AND (d.datefin >= '".$this->db->idate($now)."' OR t.subscription = 0)", "'members_uptodate'", 'NULL').") as members_uptodate";
282 $sql .= ", COUNT(".$this->db->ifsql("d.statut = ".Adherent::STATUS_VALIDATED." AND (d.datefin < '".$this->db->idate($now)."' AND t.subscription = 1)", "'members_expired'", 'NULL').") as members_expired";
283 $sql .= ", COUNT(".$this->db->ifsql("d.statut = ".Adherent::STATUS_EXCLUDED, "'members_excluded'", 'NULL').") as members_excluded";
284 $sql .= ", COUNT(".$this->db->ifsql("d.statut = ".Adherent::STATUS_RESILIATED, "'members_resiliated'", 'NULL').") as members_resiliated";
285 $sql .= " FROM ".MAIN_DB_PREFIX."categorie as c";
286 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."categorie_member as ct ON c.rowid = ct.fk_categorie";
287 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."adherent as d ON d.rowid = ct.fk_member";
288 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."adherent_type as t ON t.rowid = d.fk_adherent_type";
289 $sql .= " WHERE c.entity IN (".getEntity('member_type').")";
290 $sql .= " AND d.entity IN (" . getEntity('adherent') . ")";
291 $sql .= " AND t.entity IN (" . getEntity('adherent') . ")";
292 if ($numberYears) {
293 $sql .= " AND d.datefin > '".$this->db->idate(dol_get_first_day($startYear))."'";
294 }
295 $sql .= " AND c.fk_parent = 0";
296 $sql .= " GROUP BY c.rowid, c.label";
297 $sql .= " ORDER BY label ASC";
298
299 dol_syslog("box_members_by_tag::select nb of members per tag", LOG_DEBUG);
300 $result = $this->db->query($sql);
301
302 if ($result) {
303 $num = $this->db->num_rows($result);
304 $i = 0;
305 $totalstatus = array(
306 'members_draft' => 0,
307 'members_pending' => 0,
308 'members_uptodate' => 0,
309 'members_expired' => 0,
310 'members_excluded' => 0,
311 'members_resiliated' => 0
312 );
313 while ($i < $num) {
314 $objp = $this->db->fetch_object($result);
315 $MembersCountArray[$objp->fk_categorie] = array(
316 'label' => $objp->label,
317 'members_draft' => (int) $objp->members_draft,
318 'members_pending' => (int) $objp->members_pending,
319 'members_uptodate' => (int) $objp->members_uptodate,
320 'members_expired' => (int) $objp->members_expired,
321 'members_excluded' => (int) $objp->members_excluded,
322 'members_resiliated' => (int) $objp->members_resiliated
323 );
324 $totalrow = 0;
325 foreach ($MembersCountArray[$objp->fk_categorie] as $key => $nb) {
326 if ($key != 'label') {
327 $totalrow += $nb;
328 $totalstatus[$key] += $nb;
329 }
330 }
331 $MembersCountArray[$objp->fk_categorie]['total_adhtag'] = $totalrow;
332 $i++;
333 }
334 $this->db->free($result);
335 $MembersCountArray['total'] = $totalstatus;
336 $MembersCountArray['total']['all'] = array_sum($totalstatus);
337 }
338
339 return $MembersCountArray;
340 }
341}
$object
if( $user->socid > 0) if(! $user->hasRight('accounting', 'chartofaccount')) $object
Definition card.php:66
Adherent\STATUS_EXCLUDED
const STATUS_EXCLUDED
Excluded.
Definition adherent.class.php:383
Adherent\STATUS_DRAFT
const STATUS_DRAFT
Draft status.
Definition adherent.class.php:371
Adherent\STATUS_RESILIATED
const STATUS_RESILIATED
Resiliated (membership end and was not renew)
Definition adherent.class.php:379
Adherent\STATUS_VALIDATED
const STATUS_VALIDATED
Validated status.
Definition adherent.class.php:375
AdherentStats
Class to manage statistics of members.
Definition adherentstats.class.php:37
AdherentStats\getNbByMonth
getNbByMonth($year, $format=0)
Return the number of proposition by month for a given year.
Definition adherentstats.class.php:106
AdherentStats\getAllByYear
getAllByYear()
Return nb, total and average.
Definition adherentstats.class.php:177
AdherentStats\__construct
__construct($db, $socid=0, $userid=0)
Constructor.
Definition adherentstats.class.php:77
AdherentStats\getNbByYear
getNbByYear()
Return the number of subscriptions by year.
Definition adherentstats.class.php:123
AdherentStats\getAverageByMonth
getAverageByMonth($year)
Return average amount each month.
Definition adherentstats.class.php:159
AdherentStats\countMembersByTypeAndStatus
countMembersByTypeAndStatus($numberYears=0)
Return count of member by status group by adh type, total and average.
Definition adherentstats.class.php:194
AdherentStats\getAmountByMonth
getAmountByMonth($year, $format=0)
Return the number of subscriptions by month for a given year.
Definition adherentstats.class.php:141
AdherentStats\countMembersByTagAndStatus
countMembersByTagAndStatus($numberYears=0)
Return count of member by status group by adh type, total and average.
Definition adherentstats.class.php:269
Stats
Parent class of statistics class.
Definition stats.class.php:33
Stats\_getAverageByMonth
_getAverageByMonth($year, $sql, $format=0)
Return the amount average par month for a given year.
Definition stats.class.php:608
Stats\_getAmountByMonth
_getAmountByMonth($year, $sql, $format=0)
Return the amount per month for a given year.
Definition stats.class.php:551
Stats\_getNbByYear
_getNbByYear($sql)
Return nb of elements by year.
Definition stats.class.php:408
Stats\_getAllByYear
_getAllByYear($sql)
Return nb of elements, total amount and avg amount each year.
Definition stats.class.php:437
Stats\_getNbByMonth
_getNbByMonth($year, $sql, $format=0)
Renvoie le nombre de documents par mois pour une annee donnee Return number of documents per month fo...
Definition stats.class.php:494
Subscription
Class to manage subscriptions of foundation members.
Definition subscription.class.php:36
dol_get_first_day
dol_get_first_day($year, $month=1, $gm=false)
Return GMT time for first day of a month or year.
Definition date.lib.php:600
dol_now
dol_now($mode='auto')
Return date for now.
Definition functions.lib.php:3779
dol_syslog
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='', $logcontext=null)
Write log message into outputs.
Definition functions.lib.php:2286
getEntity
getEntity($element, $shared=1, $currentobject=null)
Get list of entity id to use.
Definition functions.lib.php:448
Generated on Wed Jan 1 2025 01:00:18 for dolibarr by Doxygen 1.11.0