dolibarr  18.0.0-alpha
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  *
6  * This program is free software; you can redistribute it and/or modify
7  * it under the terms of the GNU General Public License as published by
8  * the Free Software Foundation; either version 3 of the License, or
9  * (at your option) any later version.
10  *
11  * This program is distributed in the hope that it will be useful,
12  * but WITHOUT ANY WARRANTY; without even the implied warranty of
13  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14  * GNU General Public License for more details.
15  *
16  * You should have received a copy of the GNU General Public License
17  * along with this program. If not, see <https://www.gnu.org/licenses/>.
18  */
19 
26 include_once DOL_DOCUMENT_ROOT.'/core/class/stats.class.php';
27 include_once DOL_DOCUMENT_ROOT.'/adherents/class/subscription.class.php';
28 
29 
33 class AdherentStats extends Stats
34 {
38  public $table_element;
39 
40  public $memberid;
41  public $socid;
42  public $userid;
43 
44  public $from;
45  public $field;
46  public $where;
47 
48 
56  public function __construct($db, $socid = 0, $userid = 0)
57  {
58  global $conf;
59 
60  $this->db = $db;
61  $this->socid = $socid;
62  $this->userid = $userid;
63 
64  $object = new Subscription($this->db);
65 
66  $this->from = MAIN_DB_PREFIX.$object->table_element." as p";
67  $this->from .= ", ".MAIN_DB_PREFIX."adherent as m";
68 
69  $this->field = 'subscription';
70 
71  $this->where .= " m.statut != -1";
72  $this->where .= " AND p.fk_adherent = m.rowid AND m.entity IN (".getEntity('adherent').")";
73  //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);
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  //if (empty($user->rights->societe->client->voir) && !$user->socid) $sql.= ", ".MAIN_DB_PREFIX."societe_commerciaux as sc";
93  $sql .= " WHERE ".dolSqlDateFilter('p.dateadh', 0, 0, (int) $year, 1);
94  $sql .= " AND ".$this->where;
95  $sql .= " GROUP BY dm";
96  $sql .= $this->db->order('dm', 'DESC');
97 
98  return $this->_getNbByMonth($year, $sql, $format);
99  }
100 
106  public function getNbByYear()
107  {
108  $sql = "SELECT date_format(p.dateadh,'%Y') as dm, count(*)";
109  $sql .= " FROM ".$this->from;
110  //if (empty($user->rights->societe->client->voir) && !$user->socid) $sql.= ", ".MAIN_DB_PREFIX."societe_commerciaux as sc";
111  $sql .= " WHERE ".$this->where;
112  $sql .= " GROUP BY dm";
113  $sql .= $this->db->order('dm', 'DESC');
114 
115  return $this->_getNbByYear($sql);
116  }
117 
125  public function getAmountByMonth($year, $format = 0)
126  {
127  $sql = "SELECT date_format(p.dateadh,'%m') as dm, sum(p.".$this->field.")";
128  $sql .= " FROM ".$this->from;
129  //if (empty($user->rights->societe->client->voir) && !$user->socid) $sql.= ", ".MAIN_DB_PREFIX."societe_commerciaux as sc";
130  $sql .= " WHERE ".dolSqlDateFilter('p.dateadh', 0, 0, (int) $year, 1);
131  $sql .= " AND ".$this->where;
132  $sql .= " GROUP BY dm";
133  $sql .= $this->db->order('dm', 'DESC');
134 
135  return $this->_getAmountByMonth($year, $sql, $format);
136  }
137 
144  public function getAverageByMonth($year)
145  {
146  $sql = "SELECT date_format(p.dateadh,'%m') as dm, avg(p.".$this->field.")";
147  $sql .= " FROM ".$this->from;
148  //if (empty($user->rights->societe->client->voir) && !$this->socid) $sql.= ", ".MAIN_DB_PREFIX."societe_commerciaux as sc";
149  $sql .= " WHERE ".dolSqlDateFilter('p.dateadh', 0, 0, (int) $year, 1);
150  $sql .= " AND ".$this->where;
151  $sql .= " GROUP BY dm";
152  $sql .= $this->db->order('dm', 'DESC');
153 
154  return $this->_getAverageByMonth($year, $sql);
155  }
156 
157 
163  public function getAllByYear()
164  {
165  $sql = "SELECT date_format(p.dateadh,'%Y') as year, count(*) as nb, sum(".$this->field.") as total, avg(".$this->field.") as avg";
166  $sql .= " FROM ".$this->from;
167  //if (empty($user->rights->societe->client->voir) && !$this->socid) $sql.= ", ".MAIN_DB_PREFIX."societe_commerciaux as sc";
168  $sql .= " WHERE ".$this->where;
169  $sql .= " GROUP BY year";
170  $sql .= $this->db->order('year', 'DESC');
171 
172  return $this->_getAllByYear($sql);
173  }
174 
175 
176 
183  public function countMembersByTagAndStatus($numberYears = 2)
184  {
185  global $user;
186 
187  $now = dol_now();
188  $endYear = date('Y');
189  $startYear = $endYear - $numberYears;
190 
191  $sql = "SELECT c.rowid as fk_categorie, c.label as label";
192  $sql .= ", COUNT(".$this->db->ifsql("d.statut = ".Adherent::STATUS_DRAFT, "'members_draft'", 'NULL').") as members_draft";
193  $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";
194  $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";
195  $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";
196  $sql .= ", COUNT(".$this->db->ifsql("d.statut = ".Adherent::STATUS_EXCLUDED, "'members_excluded'", 'NULL').") as members_excluded";
197  $sql .= ", COUNT(".$this->db->ifsql("d.statut = ".Adherent::STATUS_RESILIATED, "'members_resiliated'", 'NULL').") as members_resiliated";
198  $sql .= " FROM ".MAIN_DB_PREFIX."categorie as c";
199  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."categorie_member as ct ON c.rowid = ct.fk_categorie";
200  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."adherent as d ON d.rowid = ct.fk_member";
201  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."adherent_type as t ON t.rowid = d.fk_adherent_type";
202  $sql .= " WHERE c.entity IN (".getEntity('member_type').")";
203  $sql .= " AND d.entity IN (" . getEntity('adherent') . ")";
204  $sql .= " AND t.entity IN (" . getEntity('adherent') . ")";
205  $sql .= " AND d.datefin > '".$this->db->idate(dol_get_first_day($startYear))."'";
206  $sql .= " AND c.fk_parent = 0";
207  $sql .= " GROUP BY c.rowid";
208 
209  dol_syslog("box_members_by_type::select nb of members per type", LOG_DEBUG);
210  $result = $this->db->query($sql);
211 
212  if ($result) {
213  $num = $this->db->num_rows($result);
214  $i = 0;
215  $MembersCountArray = [];
216  $totalstatus = array(
217  'label' => 'Total',
218  'members_draft' => 0,
219  'members_pending' => 0,
220  'members_uptodate' => 0,
221  'members_expired' => 0,
222  'members_excluded' => 0,
223  'members_resiliated' => 0
224  );
225  while ($i < $num) {
226  $objp = $this->db->fetch_object($result);
227  $MembersCountArray[$objp->fk_categorie] = array(
228  'label' => $objp->label,
229  'members_draft' => (int) $objp->members_draft,
230  'members_pending' => (int) $objp->members_pending,
231  'members_uptodate' => (int) $objp->members_uptodate,
232  'members_expired' => (int) $objp->members_expired,
233  'members_excluded' => (int) $objp->members_excluded,
234  'members_resiliated' => (int) $objp->members_resiliated
235  );
236  $totalrow = 0;
237  foreach ($MembersCountArray[$objp->fk_categorie] as $key=>$nb) {
238  if ($key!='label') {
239  $totalrow += $nb;
240  $totalstatus[$key] += $nb;
241  }
242  }
243  $MembersCountArray[$objp->fk_categorie]['total_adhtype'] = $totalrow;
244  $i++;
245  }
246  $this->db->free($result);
247  $MembersCountArray['total'] = $totalstatus;
248  $MembersCountArray['total']['all'] = array_sum($totalstatus);
249  }
250  return $MembersCountArray;
251  }
252 }
Adherent\STATUS_RESILIATED
const STATUS_RESILIATED
Resiliated.
Definition: adherent.class.php:404
db
$conf db
API class for accounts.
Definition: inc.php:41
AdherentStats\getAllByYear
getAllByYear()
Return nb, total and average.
Definition: adherentstats.class.php:163
$sql
if(isModEnabled('facture') &&!empty($user->rights->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') &&!empty($user->rights->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:745
AdherentStats\getAverageByMonth
getAverageByMonth($year)
Return average amount each month.
Definition: adherentstats.class.php:144
AdherentStats\getNbByYear
getNbByYear()
Return the number of subscriptions by year.
Definition: adherentstats.class.php:106
Stats
Parent class of statistics class.
Definition: stats.class.php:30
Stats\_getNbByYear
_getNbByYear($sql)
Return nb of elements by year.
Definition: stats.class.php:348
Stats\_getAmountByMonth
_getAmountByMonth($year, $sql, $format=0)
Return the amount per month for a given year.
Definition: stats.class.php:485
AdherentStats\getAmountByMonth
getAmountByMonth($year, $format=0)
Return the number of subscriptions by month for a given year.
Definition: adherentstats.class.php:125
AdherentStats\countMembersByTagAndStatus
countMembersByTagAndStatus($numberYears=2)
Return count of member by status group by adh type, total and average.
Definition: adherentstats.class.php:183
AdherentStats
Class to manage statistics of members.
Definition: adherentstats.class.php:33
getEntity
getEntity($element, $shared=1, $currentobject=null)
Get list of entity id to use.
Definition: functions.lib.php:190
Adherent\STATUS_VALIDATED
const STATUS_VALIDATED
Validated status.
Definition: adherent.class.php:400
Stats\_getAllByYear
_getAllByYear($sql)
Return nb of elements, total amount and avg amount each year.
Definition: stats.class.php:377
dol_syslog
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='', $logcontext=null)
Write log message into outputs.
Definition: functions.lib.php:1639
Subscription
Class to manage subscriptions of foundation members.
Definition: subscription.class.php:33
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:576
Adherent\STATUS_EXCLUDED
const STATUS_EXCLUDED
Excluded.
Definition: adherent.class.php:408
Adherent\STATUS_DRAFT
const STATUS_DRAFT
Draft status.
Definition: adherent.class.php:396
AdherentStats\__construct
__construct($db, $socid=0, $userid=0)
Constructor.
Definition: adherentstats.class.php:56
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:428
dol_now
dol_now($mode='auto')
Return date for now.
Definition: functions.lib.php:2947
AdherentStats\getNbByMonth
getNbByMonth($year, $format=0)
Return the number of proposition by month for a given year.
Definition: adherentstats.class.php:88
Stats\_getAverageByMonth
_getAverageByMonth($year, $sql, $format=0)
Renvoie le montant moyen par mois pour une annee donnee Return the amount average par month for a giv...
Definition: stats.class.php:543