dolibarr  19.0.0-dev
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  *
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 include_once DOL_DOCUMENT_ROOT.'/core/class/stats.class.php';
28 include_once DOL_DOCUMENT_ROOT.'/adherents/class/subscription.class.php';
29 
30 
34 class AdherentStats extends Stats
35 {
39  public $table_element;
40 
41  public $memberid;
42  public $socid;
43  public $userid;
44 
45  public $from;
46  public $field;
47  public $where;
48 
49 
57  public function __construct($db, $socid = 0, $userid = 0)
58  {
59  $this->db = $db;
60  $this->socid = $socid;
61  $this->userid = $userid;
62 
63  $object = new Subscription($this->db);
64 
65  $this->from = MAIN_DB_PREFIX.$object->table_element." as p";
66  $this->from .= ", ".MAIN_DB_PREFIX."adherent as m";
67 
68  $this->field = 'subscription';
69 
70  $this->where .= " m.statut != -1";
71  $this->where .= " AND p.fk_adherent = m.rowid AND m.entity IN (".getEntity('adherent').")";
72  //if (empty($user->rights->societe->client->voir) && !$user->socid) $this->where .= " AND p.fk_soc = sc.fk_soc AND sc.fk_user = " .((int) $user->id);
73  if ($this->memberid) {
74  $this->where .= " AND m.rowid = ".((int) $this->memberid);
75  }
76  //if ($this->userid > 0) $this->where .= " AND fk_user_author = ".((int) $this->userid);
77  }
78 
79 
87  public function getNbByMonth($year, $format = 0)
88  {
89  $sql = "SELECT date_format(p.dateadh,'%m') as dm, count(*)";
90  $sql .= " FROM ".$this->from;
91  //if (empty($user->rights->societe->client->voir) && !$user->socid) $sql.= ", ".MAIN_DB_PREFIX."societe_commerciaux as sc";
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  //if (empty($user->rights->societe->client->voir) && !$user->socid) $sql.= ", ".MAIN_DB_PREFIX."societe_commerciaux as sc";
110  $sql .= " WHERE ".$this->where;
111  $sql .= " GROUP BY dm";
112  $sql .= $this->db->order('dm', 'DESC');
113 
114  return $this->_getNbByYear($sql);
115  }
116 
124  public function getAmountByMonth($year, $format = 0)
125  {
126  $sql = "SELECT date_format(p.dateadh,'%m') as dm, sum(p.".$this->field.")";
127  $sql .= " FROM ".$this->from;
128  //if (empty($user->rights->societe->client->voir) && !$user->socid) $sql.= ", ".MAIN_DB_PREFIX."societe_commerciaux as sc";
129  $sql .= " WHERE ".dolSqlDateFilter('p.dateadh', 0, 0, (int) $year, 1);
130  $sql .= " AND ".$this->where;
131  $sql .= " GROUP BY dm";
132  $sql .= $this->db->order('dm', 'DESC');
133 
134  return $this->_getAmountByMonth($year, $sql, $format);
135  }
136 
143  public function getAverageByMonth($year)
144  {
145  $sql = "SELECT date_format(p.dateadh,'%m') as dm, avg(p.".$this->field.")";
146  $sql .= " FROM ".$this->from;
147  //if (empty($user->rights->societe->client->voir) && !$this->socid) $sql.= ", ".MAIN_DB_PREFIX."societe_commerciaux as sc";
148  $sql .= " WHERE ".dolSqlDateFilter('p.dateadh', 0, 0, (int) $year, 1);
149  $sql .= " AND ".$this->where;
150  $sql .= " GROUP BY dm";
151  $sql .= $this->db->order('dm', 'DESC');
152 
153  return $this->_getAverageByMonth($year, $sql);
154  }
155 
156 
162  public function getAllByYear()
163  {
164  $sql = "SELECT date_format(p.dateadh,'%Y') as year, count(*) as nb, sum(".$this->field.") as total, avg(".$this->field.") as avg";
165  $sql .= " FROM ".$this->from;
166  //if (empty($user->rights->societe->client->voir) && !$this->socid) $sql.= ", ".MAIN_DB_PREFIX."societe_commerciaux as sc";
167  $sql .= " WHERE ".$this->where;
168  $sql .= " GROUP BY year";
169  $sql .= $this->db->order('year', 'DESC');
170 
171  return $this->_getAllByYear($sql);
172  }
173 
180  public function countMembersByTypeAndStatus($numberYears = 0)
181  {
182  global $user;
183 
184  $now = dol_now();
185  $endYear = date('Y');
186  $startYear = $endYear - $numberYears;
187 
188  $sql = "SELECT t.rowid as fk_adherent_type, t.libelle as label";
189  $sql .= ", COUNT(".$this->db->ifsql("d.statut = ".Adherent::STATUS_DRAFT, "'members_draft'", 'NULL').") as members_draft";
190  $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";
191  $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";
192  $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";
193  $sql .= ", COUNT(".$this->db->ifsql("d.statut = ".Adherent::STATUS_EXCLUDED, "'members_excluded'", 'NULL').") as members_excluded";
194  $sql .= ", COUNT(".$this->db->ifsql("d.statut = ".Adherent::STATUS_RESILIATED, "'members_resiliated'", 'NULL').") as members_resiliated";
195  $sql .= " FROM ".MAIN_DB_PREFIX."adherent_type as t";
196  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."adherent as d ON t.rowid = d.fk_adherent_type AND d.entity IN (" . getEntity('adherent') . ")";
197  if ($numberYears) {
198  $sql .= " AND d.datefin > '".$this->db->idate(dol_get_first_day($startYear))."'";
199  }
200  $sql .= " WHERE t.entity IN (".getEntity('member_type').")";
201  $sql .= " AND t.statut = 1";
202  $sql .= " GROUP BY t.rowid";
203 
204  dol_syslog("box_members_by_type::select nb of members per type", LOG_DEBUG);
205  $result = $this->db->query($sql);
206 
207  $MembersCountArray = array();
208 
209  if ($result) {
210  $num = $this->db->num_rows($result);
211  $i = 0;
212  $totalstatus = array(
213  'label' => 'Total',
214  'members_draft' => 0,
215  'members_pending' => 0,
216  'members_uptodate' => 0,
217  'members_expired' => 0,
218  'members_excluded' => 0,
219  'members_resiliated' => 0
220  );
221  while ($i < $num) {
222  $objp = $this->db->fetch_object($result);
223  $MembersCountArray[$objp->fk_adherent_type] = array(
224  'label' => $objp->label,
225  'members_draft' => (int) $objp->members_draft,
226  'members_pending' => (int) $objp->members_pending,
227  'members_uptodate' => (int) $objp->members_uptodate,
228  'members_expired' => (int) $objp->members_expired,
229  'members_excluded' => (int) $objp->members_excluded,
230  'members_resiliated' => (int) $objp->members_resiliated
231  );
232  $totalrow = 0;
233  foreach ($MembersCountArray[$objp->fk_adherent_type] as $key=>$nb) {
234  if ($key!='label') {
235  $totalrow += $nb;
236  $totalstatus[$key] += $nb;
237  }
238  }
239  $MembersCountArray[$objp->fk_adherent_type]['total_adhtype'] = $totalrow;
240  $i++;
241  }
242  $this->db->free($result);
243  $MembersCountArray['total'] = $totalstatus;
244  $MembersCountArray['total']['all'] = array_sum($totalstatus);
245  }
246 
247  return $MembersCountArray;
248  }
249 
256  public function countMembersByTagAndStatus($numberYears = 0)
257  {
258  global $user;
259 
260  $now = dol_now();
261  $endYear = date('Y');
262  $startYear = $endYear - $numberYears;
263 
264  $sql = "SELECT c.rowid as fk_categorie, c.label as label";
265  $sql .= ", COUNT(".$this->db->ifsql("d.statut = ".Adherent::STATUS_DRAFT, "'members_draft'", 'NULL').") as members_draft";
266  $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";
267  $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";
268  $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";
269  $sql .= ", COUNT(".$this->db->ifsql("d.statut = ".Adherent::STATUS_EXCLUDED, "'members_excluded'", 'NULL').") as members_excluded";
270  $sql .= ", COUNT(".$this->db->ifsql("d.statut = ".Adherent::STATUS_RESILIATED, "'members_resiliated'", 'NULL').") as members_resiliated";
271  $sql .= " FROM ".MAIN_DB_PREFIX."categorie as c";
272  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."categorie_member as ct ON c.rowid = ct.fk_categorie";
273  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."adherent as d ON d.rowid = ct.fk_member";
274  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."adherent_type as t ON t.rowid = d.fk_adherent_type";
275  $sql .= " WHERE c.entity IN (".getEntity('member_type').")";
276  $sql .= " AND d.entity IN (" . getEntity('adherent') . ")";
277  $sql .= " AND t.entity IN (" . getEntity('adherent') . ")";
278  if ($numberYears) {
279  $sql .= " AND d.datefin > '".$this->db->idate(dol_get_first_day($startYear))."'";
280  }
281  $sql .= " AND c.fk_parent = 0";
282  $sql .= " GROUP BY c.rowid ORDER BY label ASC";
283 
284  dol_syslog("box_members_by_type::select nb of members per type", LOG_DEBUG);
285  $result = $this->db->query($sql);
286 
287  if ($result) {
288  $num = $this->db->num_rows($result);
289  $i = 0;
290  $MembersCountArray = [];
291  $totalstatus = array(
292  'label' => 'Total',
293  'members_draft' => 0,
294  'members_pending' => 0,
295  'members_uptodate' => 0,
296  'members_expired' => 0,
297  'members_excluded' => 0,
298  'members_resiliated' => 0
299  );
300  while ($i < $num) {
301  $objp = $this->db->fetch_object($result);
302  $MembersCountArray[$objp->fk_categorie] = array(
303  'label' => $objp->label,
304  'members_draft' => (int) $objp->members_draft,
305  'members_pending' => (int) $objp->members_pending,
306  'members_uptodate' => (int) $objp->members_uptodate,
307  'members_expired' => (int) $objp->members_expired,
308  'members_excluded' => (int) $objp->members_excluded,
309  'members_resiliated' => (int) $objp->members_resiliated
310  );
311  $totalrow = 0;
312  foreach ($MembersCountArray[$objp->fk_categorie] as $key=>$nb) {
313  if ($key!='label') {
314  $totalrow += $nb;
315  $totalstatus[$key] += $nb;
316  }
317  }
318  $MembersCountArray[$objp->fk_categorie]['total_adhtag'] = $totalrow;
319  $i++;
320  }
321  $this->db->free($result);
322  $MembersCountArray['total'] = $totalstatus;
323  $MembersCountArray['total']['all'] = array_sum($totalstatus);
324  }
325  return $MembersCountArray;
326  }
327 }
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:31
_getAverageByMonth($year, $sql, $format=0)
Renvoie le montant moyen par mois pour une annee donnee Return the amount average par month for a giv...
_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('facture') && $user->hasRight('facture', 'lire')) if((isModEnabled('fournisseur') &&empty($conf->global->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') &&!empty($user->rights->tax->charges->lire)) if(isModEnabled('facture') &&isModEnabled('commande') && $user->hasRight("commande", "lire") &&empty($conf->global->WORKFLOW_DISABLE_CREATE_INVOICE_FROM_ORDER)) $sql
Social contributions to pay.
Definition: index.php:746
dol_get_first_day($year, $month=1, $gm=false)
Return GMT time for first day of a month or year.
Definition: date.lib.php:576
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.