27 include_once DOL_DOCUMENT_ROOT.
'/core/class/stats.class.php';
28 include_once DOL_DOCUMENT_ROOT.
'/adherents/class/subscription.class.php';
39 public $table_element;
60 $this->socid = $socid;
61 $this->userid = $userid;
65 $this->from = MAIN_DB_PREFIX.$object->table_element.
" as p";
66 $this->from .=
", ".MAIN_DB_PREFIX.
"adherent as m";
68 $this->field =
'subscription';
70 $this->where .=
" m.statut != -1";
71 $this->where .=
" AND p.fk_adherent = m.rowid AND m.entity IN (".getEntity(
'adherent').
")";
73 if ($this->memberid) {
74 $this->where .=
" AND m.rowid = ".((int) $this->memberid);
89 $sql =
"SELECT date_format(p.dateadh,'%m') as dm, count(*)";
90 $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');
107 $sql =
"SELECT date_format(p.dateadh,'%Y') as dm, count(*)";
108 $sql .=
" FROM ".$this->from;
110 $sql .=
" WHERE ".$this->where;
111 $sql .=
" GROUP BY dm";
112 $sql .= $this->db->order(
'dm',
'DESC');
126 $sql =
"SELECT date_format(p.dateadh,'%m') as dm, sum(p.".$this->field.
")";
127 $sql .=
" FROM ".$this->from;
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');
145 $sql =
"SELECT date_format(p.dateadh,'%m') as dm, avg(p.".$this->field.
")";
146 $sql .=
" FROM ".$this->from;
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');
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;
167 $sql .=
" WHERE ".$this->where;
168 $sql .=
" GROUP BY year";
169 $sql .= $this->db->order(
'year',
'DESC');
185 $endYear = date(
'Y');
186 $startYear = $endYear - $numberYears;
188 $sql =
"SELECT t.rowid as fk_adherent_type, t.libelle as label";
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";
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') .
")";
200 $sql .=
" WHERE t.entity IN (".getEntity(
'member_type').
")";
201 $sql .=
" AND t.statut = 1";
202 $sql .=
" GROUP BY t.rowid";
204 dol_syslog(
"box_members_by_type::select nb of members per type", LOG_DEBUG);
205 $result = $this->db->query(
$sql);
207 $MembersCountArray = array();
210 $num = $this->db->num_rows($result);
212 $totalstatus = array(
214 'members_draft' => 0,
215 'members_pending' => 0,
216 'members_uptodate' => 0,
217 'members_expired' => 0,
218 'members_excluded' => 0,
219 'members_resiliated' => 0
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
233 foreach ($MembersCountArray[$objp->fk_adherent_type] as $key=>$nb) {
236 $totalstatus[$key] += $nb;
239 $MembersCountArray[$objp->fk_adherent_type][
'total_adhtype'] = $totalrow;
242 $this->db->free($result);
243 $MembersCountArray[
'total'] = $totalstatus;
244 $MembersCountArray[
'total'][
'all'] = array_sum($totalstatus);
247 return $MembersCountArray;
261 $endYear = date(
'Y');
262 $startYear = $endYear - $numberYears;
264 $sql =
"SELECT c.rowid as fk_categorie, c.label as label";
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";
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').
")";
281 $sql .=
" AND c.fk_parent = 0";
282 $sql .=
" GROUP BY c.rowid ORDER BY label ASC";
284 dol_syslog(
"box_members_by_type::select nb of members per type", LOG_DEBUG);
285 $result = $this->db->query(
$sql);
288 $num = $this->db->num_rows($result);
290 $MembersCountArray = [];
291 $totalstatus = array(
293 'members_draft' => 0,
294 'members_pending' => 0,
295 'members_uptodate' => 0,
296 'members_expired' => 0,
297 'members_excluded' => 0,
298 'members_resiliated' => 0
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
312 foreach ($MembersCountArray[$objp->fk_categorie] as $key=>$nb) {
315 $totalstatus[$key] += $nb;
318 $MembersCountArray[$objp->fk_categorie][
'total_adhtag'] = $totalrow;
321 $this->db->free($result);
322 $MembersCountArray[
'total'] = $totalstatus;
323 $MembersCountArray[
'total'][
'all'] = array_sum($totalstatus);
325 return $MembersCountArray;