dolibarr  20.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 
29 include_once DOL_DOCUMENT_ROOT.'/core/class/stats.class.php';
30 include_once DOL_DOCUMENT_ROOT.'/adherents/class/subscription.class.php';
31 
32 
36 class AdherentStats extends Stats
37 {
41  public $table_element;
42 
43  public $memberid;
44  public $socid;
45  public $userid;
46 
47  public $from;
48  public $field;
49  public $where;
50 
51 
59  public function __construct($db, $socid = 0, $userid = 0)
60  {
61  $this->db = $db;
62  $this->socid = $socid;
63  $this->userid = $userid;
64 
65  $object = new Subscription($this->db);
66 
67  $this->from = MAIN_DB_PREFIX.$object->table_element." as p";
68  $this->from .= ", ".MAIN_DB_PREFIX."adherent as m";
69 
70  $this->field = 'subscription';
71 
72  $this->where .= " m.statut != -1";
73  $this->where .= " AND p.fk_adherent = m.rowid AND m.entity IN (".getEntity('adherent').")";
74  if ($this->memberid) {
75  $this->where .= " AND m.rowid = ".((int) $this->memberid);
76  }
77  //if ($this->userid > 0) $this->where .= " AND fk_user_author = ".((int) $this->userid);
78  }
79 
80 
88  public function getNbByMonth($year, $format = 0)
89  {
90  $sql = "SELECT date_format(p.dateadh,'%m') as dm, count(*)";
91  $sql .= " FROM ".$this->from;
92  $sql .= " WHERE ".dolSqlDateFilter('p.dateadh', 0, 0, (int) $year, 1);
93  $sql .= " AND ".$this->where;
94  $sql .= " GROUP BY dm";
95  $sql .= $this->db->order('dm', 'DESC');
96 
97  return $this->_getNbByMonth($year, $sql, $format);
98  }
99 
105  public function getNbByYear()
106  {
107  $sql = "SELECT date_format(p.dateadh,'%Y') as dm, count(*)";
108  $sql .= " FROM ".$this->from;
109  $sql .= " WHERE ".$this->where;
110  $sql .= " GROUP BY dm";
111  $sql .= $this->db->order('dm', 'DESC');
112 
113  return $this->_getNbByYear($sql);
114  }
115 
123  public function getAmountByMonth($year, $format = 0)
124  {
125  $sql = "SELECT date_format(p.dateadh,'%m') as dm, sum(p.".$this->field.")";
126  $sql .= " FROM ".$this->from;
127  $sql .= " WHERE ".dolSqlDateFilter('p.dateadh', 0, 0, (int) $year, 1);
128  $sql .= " AND ".$this->where;
129  $sql .= " GROUP BY dm";
130  $sql .= $this->db->order('dm', 'DESC');
131 
132  return $this->_getAmountByMonth($year, $sql, $format);
133  }
134 
141  public function getAverageByMonth($year)
142  {
143  $sql = "SELECT date_format(p.dateadh,'%m') as dm, avg(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->_getAverageByMonth($year, $sql);
151  }
152 
153 
159  public function getAllByYear()
160  {
161  $sql = "SELECT date_format(p.dateadh,'%Y') as year, count(*) as nb, sum(".$this->field.") as total, avg(".$this->field.") as avg";
162  $sql .= " FROM ".$this->from;
163  $sql .= " WHERE ".$this->where;
164  $sql .= " GROUP BY year";
165  $sql .= $this->db->order('year', 'DESC');
166 
167  return $this->_getAllByYear($sql);
168  }
169 
176  public function countMembersByTypeAndStatus($numberYears = 0)
177  {
178  global $user;
179 
180  $now = dol_now();
181  $endYear = (int) date('Y');
182  $startYear = $endYear - $numberYears;
183 
184  $sql = "SELECT t.rowid as fk_adherent_type, t.libelle as label";
185  $sql .= ", COUNT(".$this->db->ifsql("d.statut = ".Adherent::STATUS_DRAFT, "'members_draft'", 'NULL').") as members_draft";
186  $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";
187  $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";
188  $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";
189  $sql .= ", COUNT(".$this->db->ifsql("d.statut = ".Adherent::STATUS_EXCLUDED, "'members_excluded'", 'NULL').") as members_excluded";
190  $sql .= ", COUNT(".$this->db->ifsql("d.statut = ".Adherent::STATUS_RESILIATED, "'members_resiliated'", 'NULL').") as members_resiliated";
191  $sql .= " FROM ".MAIN_DB_PREFIX."adherent_type as t";
192  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."adherent as d ON t.rowid = d.fk_adherent_type AND d.entity IN (" . getEntity('adherent') . ")";
193  if ($numberYears) {
194  $sql .= " AND d.datefin > '".$this->db->idate(dol_get_first_day($startYear))."'";
195  }
196  $sql .= " WHERE t.entity IN (".getEntity('member_type').")";
197  $sql .= " AND t.statut = 1";
198  $sql .= " GROUP BY t.rowid, t.libelle";
199 
200  dol_syslog("box_members_by_type::select nb of members per type", LOG_DEBUG);
201  $result = $this->db->query($sql);
202 
203  $MembersCountArray = array();
204 
205  if ($result) {
206  $num = $this->db->num_rows($result);
207  $i = 0;
208  $totalstatus = array(
209  'label' => 'Total',
210  'members_draft' => 0,
211  'members_pending' => 0,
212  'members_uptodate' => 0,
213  'members_expired' => 0,
214  'members_excluded' => 0,
215  'members_resiliated' => 0
216  );
217  while ($i < $num) {
218  $objp = $this->db->fetch_object($result);
219  $MembersCountArray[$objp->fk_adherent_type] = array(
220  'label' => $objp->label,
221  'members_draft' => (int) $objp->members_draft,
222  'members_pending' => (int) $objp->members_pending,
223  'members_uptodate' => (int) $objp->members_uptodate,
224  'members_expired' => (int) $objp->members_expired,
225  'members_excluded' => (int) $objp->members_excluded,
226  'members_resiliated' => (int) $objp->members_resiliated
227  );
228  $totalrow = 0;
229  foreach ($MembersCountArray[$objp->fk_adherent_type] as $key => $nb) {
230  if ($key != 'label') {
231  $totalrow += $nb;
232  $totalstatus[$key] += $nb;
233  }
234  }
235  $MembersCountArray[$objp->fk_adherent_type]['total_adhtype'] = $totalrow;
236  $i++;
237  }
238  $this->db->free($result);
239  $MembersCountArray['total'] = $totalstatus;
240  $MembersCountArray['total']['all'] = array_sum($totalstatus);
241  }
242 
243  return $MembersCountArray;
244  }
245 
252  public function countMembersByTagAndStatus($numberYears = 0)
253  {
254  global $user;
255 
256  $now = dol_now();
257  $endYear = (int) date('Y');
258  $startYear = $endYear - $numberYears;
259  $MembersCountArray = [];
260 
261  $sql = "SELECT c.rowid as fk_categorie, c.label as label";
262  $sql .= ", COUNT(".$this->db->ifsql("d.statut = ".Adherent::STATUS_DRAFT, "'members_draft'", 'NULL').") as members_draft";
263  $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";
264  $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";
265  $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";
266  $sql .= ", COUNT(".$this->db->ifsql("d.statut = ".Adherent::STATUS_EXCLUDED, "'members_excluded'", 'NULL').") as members_excluded";
267  $sql .= ", COUNT(".$this->db->ifsql("d.statut = ".Adherent::STATUS_RESILIATED, "'members_resiliated'", 'NULL').") as members_resiliated";
268  $sql .= " FROM ".MAIN_DB_PREFIX."categorie as c";
269  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."categorie_member as ct ON c.rowid = ct.fk_categorie";
270  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."adherent as d ON d.rowid = ct.fk_member";
271  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."adherent_type as t ON t.rowid = d.fk_adherent_type";
272  $sql .= " WHERE c.entity IN (".getEntity('member_type').")";
273  $sql .= " AND d.entity IN (" . getEntity('adherent') . ")";
274  $sql .= " AND t.entity IN (" . getEntity('adherent') . ")";
275  if ($numberYears) {
276  $sql .= " AND d.datefin > '".$this->db->idate(dol_get_first_day($startYear))."'";
277  }
278  $sql .= " AND c.fk_parent = 0";
279  $sql .= " GROUP BY c.rowid, c.label";
280  $sql .= " ORDER BY label ASC";
281 
282  dol_syslog("box_members_by_tag::select nb of members per tag", LOG_DEBUG);
283  $result = $this->db->query($sql);
284 
285  if ($result) {
286  $num = $this->db->num_rows($result);
287  $i = 0;
288  $totalstatus = array(
289  'label' => 'Total',
290  'members_draft' => 0,
291  'members_pending' => 0,
292  'members_uptodate' => 0,
293  'members_expired' => 0,
294  'members_excluded' => 0,
295  'members_resiliated' => 0
296  );
297  while ($i < $num) {
298  $objp = $this->db->fetch_object($result);
299  $MembersCountArray[$objp->fk_categorie] = array(
300  'label' => $objp->label,
301  'members_draft' => (int) $objp->members_draft,
302  'members_pending' => (int) $objp->members_pending,
303  'members_uptodate' => (int) $objp->members_uptodate,
304  'members_expired' => (int) $objp->members_expired,
305  'members_excluded' => (int) $objp->members_excluded,
306  'members_resiliated' => (int) $objp->members_resiliated
307  );
308  $totalrow = 0;
309  foreach ($MembersCountArray[$objp->fk_categorie] as $key => $nb) {
310  if ($key != 'label') {
311  $totalrow += $nb;
312  $totalstatus[$key] += $nb;
313  }
314  }
315  $MembersCountArray[$objp->fk_categorie]['total_adhtag'] = $totalrow;
316  $i++;
317  }
318  $this->db->free($result);
319  $MembersCountArray['total'] = $totalstatus;
320  $MembersCountArray['total']['all'] = array_sum($totalstatus);
321  }
322 
323  return $MembersCountArray;
324  }
325 }
if($user->socid > 0) if(! $user->hasRight('accounting', 'chartofaccount')) $object
Definition: card.php:58
const STATUS_EXCLUDED
Excluded.
const STATUS_DRAFT
Draft status.
const STATUS_RESILIATED
Resiliated.
const STATUS_VALIDATED
Validated status.
Class to manage statistics of members.
getNbByMonth($year, $format=0)
Return the number of proposition by month for a given year.
getAllByYear()
Return nb, total and average.
__construct($db, $socid=0, $userid=0)
Constructor.
getNbByYear()
Return the number of subscriptions by year.
getAverageByMonth($year)
Return average amount each month.
countMembersByTypeAndStatus($numberYears=0)
Return count of member by status group by adh type, total and average.
getAmountByMonth($year, $format=0)
Return the number of subscriptions by month for a given year.
countMembersByTagAndStatus($numberYears=0)
Return count of member by status group by adh type, total and average.
Parent class of statistics class.
Definition: stats.class.php:32
_getAverageByMonth($year, $sql, $format=0)
Return the amount average par month for a given year.
_getAmountByMonth($year, $sql, $format=0)
Return the amount per month for a given year.
_getNbByYear($sql)
Return nb of elements by year.
_getAllByYear($sql)
Return nb of elements, total amount and avg amount each year.
_getNbByMonth($year, $sql, $format=0)
Renvoie le nombre de documents par mois pour une annee donnee Return number of documents per month fo...
Class to manage subscriptions of foundation members.
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
dol_get_first_day($year, $month=1, $gm=false)
Return GMT time for first day of a month or year.
Definition: date.lib.php:595
dol_now($mode='auto')
Return date for now.
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='', $logcontext=null)
Write log message into outputs.
getEntity($element, $shared=1, $currentobject=null)
Get list of entity id to use.