/
CustomerReport.php
71 lines (60 loc) · 1.98 KB
/
CustomerReport.php
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
<?php
namespace SilverShop\Reports;
use SilverStripe\Security\Member;
/**
* List top customers, especially those who spend alot, and those who buy alot.
*
* @todo customer making the most purchases
* @todo customer who has spent the most money
* @todo new registrations graph
* @todo demographics
*/
class CustomerReport extends ShopPeriodReport
{
protected $title = 'Customers';
protected $description = 'Understand which customers spend the most.';
protected $dataClass = Member::class;
protected $periodfield = '"SilverShop_Order"."Paid"';
public function columns()
{
return [
'FirstName' => 'First Name',
'Surname' => 'Surname',
'Email' => 'Email',
'Created' => 'Joined',
'Spent' => 'Spent',
'Orders' => 'Orders',
'edit' => [
'title' => 'Edit',
'formatting' => '<a href=\"admin/security/users/EditForm/field/users/item/$ID/edit\" target=\"_new\">edit</a>',
],
];
}
public function getReportField()
{
$field = parent::getReportField();
return $field;
}
public function query($params)
{
$query = parent::query($params);
$query->selectField($this->periodfield, 'FilterPeriod')
->addSelect(
['"Member"."ID"', '"Member"."FirstName"', '"Member"."Surname"', '"Member"."Email"', '"Member"."Created"']
)
->selectField('COUNT("SilverShop_Order"."ID")', 'Orders')
->selectField('SUM("SilverShop_Order"."Total")', 'Spent');
$query->addInnerJoin('SilverShop_Order', '"Member"."ID" = "SilverShop_Order"."MemberID"');
$query->addGroupBy('"Member"."ID"');
if (!$query->getOrderBy()) {
$query->setOrderBy(
[
'Spent' => 'DESC',
'Orders' => 'DESC'
]
);
}
$query->setLimit(50);
return $query;
}
}