Go to the documentation of this file.
26 include_once DOL_DOCUMENT_ROOT.
'/core/class/stats.class.php';
27 include_once DOL_DOCUMENT_ROOT.
'/adherents/class/subscription.class.php';
38 public $table_element;
61 $this->socid = $socid;
62 $this->userid = $userid;
66 $this->from = MAIN_DB_PREFIX.$object->table_element.
" as p";
67 $this->from .=
", ".MAIN_DB_PREFIX.
"adherent as m";
69 $this->field =
'subscription';
71 $this->where .=
" m.statut != -1";
72 $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);
90 $sql =
"SELECT date_format(p.dateadh,'%m') as dm, count(*)";
91 $sql .=
" FROM ".$this->from;
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');
108 $sql =
"SELECT date_format(p.dateadh,'%Y') as dm, count(*)";
109 $sql .=
" FROM ".$this->from;
111 $sql .=
" WHERE ".$this->where;
112 $sql .=
" GROUP BY dm";
113 $sql .= $this->
db->order(
'dm',
'DESC');
127 $sql =
"SELECT date_format(p.dateadh,'%m') as dm, sum(p.".$this->field.
")";
128 $sql .=
" FROM ".$this->from;
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');
146 $sql =
"SELECT date_format(p.dateadh,'%m') as dm, avg(p.".$this->field.
")";
147 $sql .=
" FROM ".$this->from;
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');
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;
168 $sql .=
" WHERE ".$this->where;
169 $sql .=
" GROUP BY year";
170 $sql .= $this->
db->order(
'year',
'DESC');
188 $endYear = date(
'Y');
189 $startYear = $endYear - $numberYears;
191 $sql =
"SELECT c.rowid as fk_categorie, c.label as label";
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";
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').
")";
206 $sql .=
" AND c.fk_parent = 0";
207 $sql .=
" GROUP BY c.rowid";
209 dol_syslog(
"box_members_by_type::select nb of members per type", LOG_DEBUG);
210 $result = $this->
db->query(
$sql);
213 $num = $this->
db->num_rows($result);
215 $MembersCountArray = [];
216 $totalstatus = array(
218 'members_draft' => 0,
219 'members_pending' => 0,
220 'members_uptodate' => 0,
221 'members_expired' => 0,
222 'members_excluded' => 0,
223 'members_resiliated' => 0
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
237 foreach ($MembersCountArray[$objp->fk_categorie] as $key=>$nb) {
240 $totalstatus[$key] += $nb;
243 $MembersCountArray[$objp->fk_categorie][
'total_adhtype'] = $totalrow;
246 $this->
db->free($result);
247 $MembersCountArray[
'total'] = $totalstatus;
248 $MembersCountArray[
'total'][
'all'] = array_sum($totalstatus);
250 return $MembersCountArray;
const STATUS_RESILIATED
Resiliated.
$conf db
API class for accounts.
getAllByYear()
Return nb, total and average.
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.
getAverageByMonth($year)
Return average amount each month.
getNbByYear()
Return the number of subscriptions by year.
Parent class of statistics class.
_getNbByYear($sql)
Return nb of elements by year.
_getAmountByMonth($year, $sql, $format=0)
Return the amount per month for a given year.
getAmountByMonth($year, $format=0)
Return the number of subscriptions by month for a given year.
countMembersByTagAndStatus($numberYears=2)
Return count of member by status group by adh type, total and average.
Class to manage statistics of members.
getEntity($element, $shared=1, $currentobject=null)
Get list of entity id to use.
const STATUS_VALIDATED
Validated status.
_getAllByYear($sql)
Return nb of elements, total amount and avg amount each year.
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='', $logcontext=null)
Write log message into outputs.
Class to manage subscriptions of foundation members.
dol_get_first_day($year, $month=1, $gm=false)
Return GMT time for first day of a month or year.
const STATUS_EXCLUDED
Excluded.
const STATUS_DRAFT
Draft status.
__construct($db, $socid=0, $userid=0)
Constructor.
_getNbByMonth($year, $sql, $format=0)
Renvoie le nombre de documents par mois pour une annee donnee Return number of documents per month fo...
dol_now($mode='auto')
Return date for now.
getNbByMonth($year, $format=0)
Return the number of proposition by month for a given year.
_getAverageByMonth($year, $sql, $format=0)
Renvoie le montant moyen par mois pour une annee donnee Return the amount average par month for a giv...