/
jqCols.php
102 lines (85 loc) · 2.95 KB
/
jqCols.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
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
<?php
class jqCols extends jqGrid
{
protected function init()
{
$this->options = array(
'sortname' => 'book_id',
'sortorder' => 'asc',
);
$this->query = "
SELECT {fields}
FROM tbl_order_item i
JOIN tbl_order o ON (i.order_id=o.id)
JOIN tbl_customer c ON (o.customer_id=c.id)
JOIN tbl_books b ON (i.book_id=b.id)
WHERE {where}
";
#Set columns
$this->cols = array(
#Real id is hidden
'id' => array('hidden' => true,
'db' => 'i.id',
),
#This column is constructed by SQL-expression
#Searching and sorting still works!
'c_name' => array('label' => 'Customer name',
'db' => "CONCAT(c.first_name, ' ', c.last_name)",
'width' => 35,
),
'book_id' => array('label' => 'Book ID',
'db' => 'b.id',
'width' => 10,
'align' => 'center',
'formatter' => 'integer',
),
'book_name' => array('label' => 'Book Name',
'db' => 'b.name',
'width' => 30,
),
'quantity' => array('label' => 'Quantity',
'db' => 'i.quantity',
'db_agg' => 'sum',
'width' => 10,
'formatter' => 'integer',
'align' => 'center',
),
#Field with same name, but different tables
'orig_price' => array('label' => 'Orig price',
'db' => 'b.price', #price from 1st table
'db_agg' => 'sum',
'width' => 14,
'formatter' => 'integer',
'align' => 'right',
),
'item_price' => array('label' => 'Item price',
'db' => 'i.price', #price from 2nd table
'db_agg' => 'sum', #avg price for all items
'width' => 14,
'formatter' => 'integer',
'align' => 'right',
),
#This column is processed MANUALLY in PHP code
'diff_price' => array('label' => 'Diff',
'manual' => true,
'width' => 12,
'search' => false,
'sortable' => false,
'align' => 'right',
),
#This column exists only in PHP code
'discount' => array('db' => 'c.discount',
'unset' => true,
),
);
$this->render_filter_toolbar = true;
}
protected function parseRow($r)
{
#Calc diff_price in PHP
$r['diff_price'] = $r['orig_price'] - $r['item_price'];
#Highlight customers with discount > 0.1
$r['_class'] = array('c_name' => ($r['discount'] > 0.1) ? 'bold font-green' : null);
return $r;
}
}