usc-e-shop / classes / dataList.class.php

dataList.class.php in Welcart e-Commerce 2.11.30, at classes/dataList.class.php

451 lines 18.8 KB

Raw Download Zip

1	<?php
2	class dataList
3	{
4	var $table; //テーブル名
5	var $rows; //データ
6	var $action; //アクション
7	var $startRow; //表示開始行番号
8	var $maxRow; //最大表示行数
9	var $currentPage; //現在のページNo
10	var $firstPage; //最初のページNo
11	var $previousPage; //前のページNo
12	var $nextPage; //次のページNo
13	var $lastPage; //最終ページNo
14	var $naviMaxButton; //ページネーション・ナビのボタンの数
15	var $dataTableNavigation; //ナヴィゲーションhtmlコード
16	var $arr_period; //表示データ期間
17	var $arr_search; //サーチ条件
18	var $searchSql; //簡易絞込みSQL
19	var $searchSwitchStatus; //サーチ表示スイッチ
20	var $columns; //データカラム
21	var $sortColumn; //現在ソート中のフィールド
22	var $sortOldColumn;
23	var $sortSwitchs; //各フィールド毎の昇順降順スイッチ
24	var $userHeaderNames; //ユーザー指定のヘッダ名
25	var $pageLimit; //ページ制限
26	var $placeholder_escape;
27	var $data_cookie;
28
29	public $totalRow;
30	public $selectedRow;
31	public $headers;
32
33	//Constructor
34	function __construct($tableName, $arr_column)
35	{
36
37	$this->table = $tableName;
38	$this->columns = $arr_column;
39	$this->rows = array();
40
41	$this->maxRow = apply_filters( 'usces_filter_memberlist_maxrow', 30 );
42	$this->naviMaxButton = 11;
43	$this->firstPage = 1;
44	$this->pageLimit = 'on';
45
46	$this->getCookie();
47	$this->SetDefaultParam();
48	$this->SetParamByQuery();
49	$this->validationSearchParameters();
50
51	$this->arr_period = array(__('This month', 'usces'), __('Last month', 'usces'), __('The past one week', 'usces'), __('Last 30 days', 'usces'), __('Last 90days', 'usces'), __('All', 'usces'));
52
53
54	}
55
56	function MakeTable()
57	{
58
59	$this->SetParam();
60
61	switch ($this->action){
62	case 'searchOut':
63	$this->SearchOut();
64	$this->SetSelectedRow();
65	$res = $this->GetRows();
66	break;
67
68	case 'changeSort':
69	case 'changePage':
70	case 'searchIn':
71	case 'refresh':
72	default:
73	$this->SearchIn();
74	$this->SetSelectedRow();
75	$res = $this->GetRows();
76	break;
77	}
78
79	$this->SetNavi();
80	$this->SetHeaders();
81
82	if($res){
83
84	return TRUE;
85
86	}else{
87	return FALSE;
88	}
89	}
90
91	//DefaultParam
92	function SetDefaultParam()
93	{
94	$this->startRow = isset($this->data_cookie['startRow']) ? $this->data_cookie['startRow'] : 0;
95	$this->currentPage = isset($this->data_cookie['currentPage']) ? $this->data_cookie['currentPage'] : 1;
96	$this->sortColumn = (isset($this->data_cookie['sortColumn'])) ? $this->data_cookie['sortColumn'] :'ID';
97	$this->searchSql = (isset($this->data_cookie['searchSql'])) ? $this->data_cookie['searchSql'] :'';
98	$this->searchSwitchStatus = (isset($this->data_cookie['searchSwitchStatus'])) ? $this->data_cookie['searchSwitchStatus'] :'OFF';
99	if (isset($this->data_cookie['arr_search'])) {
100	$this->arr_search = $this->data_cookie['arr_search'];
101	} else {
102	$this->arr_search = array('period'=>'3', 'column'=>'', 'word'=>'');
103	}
104	if (isset($this->data_cookie['sortSwitchs'])) {
105	$this->sortSwitchs = $this->data_cookie['sortSwitchs'];
106	} else {
107	foreach($this->columns as $key => $value ){
108	$this->sortSwitchs[$value] = 'DESC';
109	}
110	}
111	$this->SetTotalRow();
112	$this->SetSelectedRow();
113
114	}
115
116	function SetParam()
117	{
118	$this->startRow = ($this->currentPage-1) * $this->maxRow;
119	}
120
121	function SetParamByQuery()
122	{
123	global $wpdb;
124
125	if(isset($_REQUEST['changePage'])){
126
127	$this->action = 'changePage';
128	$this->currentPage = (int)$_REQUEST['changePage'];
129	$this->sortColumn = (isset($this->data_cookie['sortColumn'])) ? $this->data_cookie['sortColumn'] : $this->sortColumn;
130	$this->sortSwitchs = (isset($this->data_cookie['sortSwitchs'])) ? $this->data_cookie['sortSwitchs'] : $this->sortSwitchs;
131	$this->userHeaderNames = (isset($this->data_cookie['userHeaderNames'])) ? $this->data_cookie['userHeaderNames'] : $this->userHeaderNames;
132	$this->searchSql = (isset($this->data_cookie['searchSql'])) ? $this->data_cookie['searchSql'] : $this->searchSql;
133	$this->arr_search = (isset($this->data_cookie['arr_search'])) ? $this->data_cookie['arr_search'] : $this->arr_search;
134	$this->totalRow = (isset($this->data_cookie['totalRow'])) ? $this->data_cookie['totalRow'] : $this->totalRow;
135	$this->selectedRow = (isset($this->data_cookie['selectedRow'])) ? $this->data_cookie['selectedRow'] : $this->selectedRow;
136	$this->placeholder_escape = (isset($this->data_cookie['placeholder_escape'])) ? $this->data_cookie['placeholder_escape'] : $this->placeholder_escape;
137
138	}else if(isset($_REQUEST['changeSort'])){
139
140	$this->action = 'changeSort';
141	$this->sortOldColumn = $this->sortColumn;
142	// Validate sortColumn.
143	if ( in_array( $_REQUEST['changeSort'], $this->columns ) ) {
144	$this->sortColumn = $_REQUEST['changeSort'];
145	} else {
146	$this->sortColumn = 'ID'; // default.
147	}
148	$this->sortSwitchs = (isset($this->data_cookie['sortSwitchs'])) ? $this->data_cookie['sortSwitchs'] : $this->sortSwitchs;
149	// Validate sortSwitchs.
150	if (isset($_REQUEST['switch']) && in_array($_REQUEST['switch'], array('ASC', 'DESC'))) {
151	$this->sortSwitchs[$this->sortColumn] = $_REQUEST['switch'];
152	} else {
153	$this->sortSwitchs[$this->sortColumn] = 'DESC'; // default.
154	}
155	$this->currentPage = (isset($this->data_cookie['currentPage'])) ? $this->data_cookie['currentPage'] : $this->currentPage;
156	$this->userHeaderNames = (isset($this->data_cookie['userHeaderNames'])) ? $this->data_cookie['userHeaderNames'] : $this->userHeaderNames;
157	$this->searchSql = (isset($this->data_cookie['searchSql'])) ? $this->data_cookie['searchSql'] : $this->searchSql;
158	$this->searchSwitchStatus = (isset($this->data_cookie['searchSwitchStatus'])) ? $this->data_cookie['searchSwitchStatus'] : $this->searchSwitchStatus;
159	$this->arr_search = (isset($this->data_cookie['arr_search'])) ? $this->data_cookie['arr_search'] : $this->arr_search;
160	$this->totalRow = (isset($this->data_cookie['totalRow'])) ? $this->data_cookie['totalRow'] : $this->totalRow;
161	$this->selectedRow = (isset($this->data_cookie['selectedRow'])) ? $this->data_cookie['selectedRow'] : $this->selectedRow;
162	$this->placeholder_escape = (isset($this->data_cookie['placeholder_escape'])) ? $this->data_cookie['placeholder_escape'] : $this->placeholder_escape;
163
164	} else if(isset($_REQUEST['searchIn'])){
165
166	$this->action = 'searchIn';
167	$this->arr_search['column'] = str_replace('`', '', $_REQUEST['search']['column']);
168	$this->arr_search['word'] = $_REQUEST['search']['word'];
169	$this->arr_search['period'] = isset($_REQUEST['search']['period']) ? intval($_REQUEST['search']['period']) : 0;
170	$this->searchSwitchStatus = str_replace(',', '', $_REQUEST['searchSwitchStatus']);
171
172	$this->currentPage = 1;
173	$this->sortColumn = (isset($this->data_cookie['sortColumn'])) ? $this->data_cookie['sortColumn'] : $this->sortColumn;
174	$this->sortSwitchs = (isset($this->data_cookie['sortSwitchs'])) ? $this->data_cookie['sortSwitchs'] : $this->sortSwitchs;
175	$this->userHeaderNames = (isset($this->data_cookie['userHeaderNames'])) ? $this->data_cookie['userHeaderNames'] : $this->userHeaderNames;
176	$this->totalRow = (isset($this->data_cookie['totalRow'])) ? $this->data_cookie['totalRow'] : $this->totalRow;
177	$this->placeholder_escape = $wpdb->placeholder_escape();
178
179	}else if(isset($_REQUEST['searchOut'])){
180
181	$this->action = 'searchOut';
182	$this->arr_search['column'] = '';
183	$this->arr_search['word'] = '';
184	$this->arr_search['period'] = (isset($this->data_cookie['arr_search']['period'])) ? $this->data_cookie['arr_search']['period'] : $this->arr_search['period'];
185	$this->searchSwitchStatus = str_replace(',', '', $_REQUEST['searchSwitchStatus']);
186
187	$this->currentPage = 1;
188	$this->sortColumn = (isset($this->data_cookie['sortColumn'])) ? $this->data_cookie['sortColumn'] : $this->sortColumn;
189	$this->sortSwitchs = (isset($this->data_cookie['sortSwitchs'])) ? $this->data_cookie['sortSwitchs'] : $this->sortSwitchs;
190	$this->userHeaderNames = (isset($this->data_cookie['userHeaderNames'])) ? $this->data_cookie['userHeaderNames'] : $this->userHeaderNames;
191	$this->totalRow = (isset($this->data_cookie['totalRow'])) ? $this->data_cookie['totalRow'] : $this->totalRow;
192	$this->placeholder_escape = '';
193
194	}else if(isset($_REQUEST['refresh'])){
195
196	$this->action = 'refresh';
197
198	$this->currentPage = (isset($this->data_cookie['currentPage'])) ? $this->data_cookie['currentPage'] : $this->currentPage;
199	$this->sortColumn = (isset($this->data_cookie['sortColumn'])) ? $this->data_cookie['sortColumn'] : $this->sortColumn;
200	$this->sortSwitchs = (isset($this->data_cookie['sortSwitchs'])) ? $this->data_cookie['sortSwitchs'] : $this->sortSwitchs;
201	$this->userHeaderNames = (isset($this->data_cookie['userHeaderNames'])) ? $this->data_cookie['userHeaderNames'] : $this->userHeaderNames;
202	$this->searchSql = (isset($this->data_cookie['searchSql'])) ? $this->data_cookie['searchSql'] : $this->searchSql;
203	$this->searchSwitchStatus = (isset($this->data_cookie['searchSwitchStatus'])) ? $this->data_cookie['searchSwitchStatus'] : $this->searchSwitchStatus;
204	$this->arr_search = (isset($this->data_cookie['arr_search'])) ? $this->data_cookie['arr_search'] : $this->arr_search;
205	$this->totalRow = (isset($this->data_cookie['totalRow'])) ? $this->data_cookie['totalRow'] : $this->totalRow;
206	$this->selectedRow = (isset($this->data_cookie['selectedRow'])) ? $this->data_cookie['selectedRow'] : $this->selectedRow;
207	$this->placeholder_escape = '';
208
209	}else{
210
211	$this->action = 'default';
212	$this->placeholder_escape = '';
213	}
214	}
215
216	function validationSearchParameters(){
217	if( 'none' != $this->arr_search['column'] && !in_array($this->arr_search['column'], $this->columns)){
218	$this->arr_search['column'] = reset($this->columns);
219	}
220	}
221
222	//GetRows
223	function GetRows()
224	{
225	global $wpdb;
226
227	$member_meta_table = usces_get_tablename( 'usces_member_meta' );
228
229	if($this->arr_search['column'] == 'none' \|\| WCUtils::is_blank($this->arr_search['column']) \|\| WCUtils::is_blank($this->arr_search['word']) ){
230	$join = "";
231	}else{
232	if( 'csmb_' === substr($this->arr_search['column'], 0, 5) ){
233	$join = $wpdb->prepare(" LEFT JOIN {$member_meta_table} AS `mm` ON ID=mm.member_id AND mm.meta_key = %s", esc_sql($this->arr_search['column']) );
234	}else{
235	$join = "";
236	}
237	}
238
239	$where = $this->GetWhere();
240
241	$init_column = 'ID';
242	$sort_column = in_array( $this->sortColumn, $this->columns, true ) ? $this->sortColumn : $init_column;
243	$init_switch = 'DESC';
244	$sort_switch = $this->sortSwitchs[ $sort_column ] ?? $init_switch;
245	$sort_switch = in_array( strtoupper( $sort_switch ), array( 'ASC', 'DESC' ), true ) ? $sort_switch : $init_switch;
246	$order = ' ORDER BY ' . esc_sql( $sort_column ) . ' ' . esc_sql( $sort_switch );
247
248	if($this->pageLimit == 'on') {
249	$limit = $wpdb->prepare(' LIMIT %d, %d', $this->startRow, $this->maxRow);
250	}else{
251	$limit = '';
252	}
253	$mem_point = ( usces_is_membersystem_point() ) ? ', mem_point AS `point`' : '';
254	$query = $wpdb->prepare("SELECT ID, CONCAT(mem_name1, ' ', mem_name2) AS `name`,
255	CONCAT(mem_pref, mem_address1, mem_address2, ' ', mem_address3) AS `address`,
256	mem_tel AS `tel`, mem_email AS `email`, DATE_FORMAT(mem_registered, %s) AS `date`{$mem_point}
257	FROM {$this->table}",
258	'%Y-%m-%d %H:%i');
259
260	$query .= $join . $where . $order . $limit;
261
262	if( $this->placeholder_escape ){
263	add_filter( 'query', array( $this, 'remove_ph') );
264	}
265
266	$this->rows = $wpdb->get_results($query, ARRAY_A);
267	return $this->rows;
268	}
269
270	public function remove_ph( $query ) {
271	return str_replace( $this->placeholder_escape, '%', $query );
272	}
273
274	function SetTotalRow()
275	{
276	global $wpdb;
277	$query = "SELECT COUNT(ID) AS `ct` FROM {$this->table}";
278	$res = $wpdb->get_var($query);
279	$this->totalRow = $res;
280	}
281
282	function SetSelectedRow()
283	{
284	global $wpdb;
285
286	$member_meta_table = usces_get_tablename( 'usces_member_meta' );
287	if($this->arr_search['column'] == 'none' \|\| WCUtils::is_blank($this->arr_search['column']) \|\| WCUtils::is_blank($this->arr_search['word']) ){
288	$join = "";
289	}else{
290	if( 'csmb_' === substr($this->arr_search['column'], 0, 5) ){
291	$join = $wpdb->prepare(" LEFT JOIN {$member_meta_table} AS `mm` ON ID=mm.member_id AND mm.meta_key = %s", esc_sql($this->arr_search['column']) );
292	}else{
293	$join = "";
294	}
295	}
296	$where = $this->GetWhere();
297	$mem_point = ( usces_is_membersystem_point() ) ? ', mem_point AS `point`' : '';
298	$query = $wpdb->prepare("SELECT ID, CONCAT(mem_name1, ' ', mem_name2) AS `name`,
299	CONCAT(mem_pref, mem_address1, mem_address2, ' ', mem_address3) AS `address`,
300	mem_tel AS `tel`, mem_email AS `email`, DATE_FORMAT(mem_registered, %s) AS `date`{$mem_point}
301	FROM {$this->table}",
302	'%Y-%m-%d %H:%i');
303	$query .= $join . $where;
304	$rows = $wpdb->get_results($query, ARRAY_A);
305	$this->selectedRow = ( $rows && is_array( $rows ) ) ? count( $rows ) : 0;
306
307	}
308
309	function GetWhere()
310	{
311	$str = '';
312	$thismonth = date('Y-m-01 00:00:00');
313	$lastmonth = date('Y-m-01 00:00:00', mktime(0, 0, 0, date('m')-1, 1, date('Y')));
314	$lastweek = date('Y-m-d 00:00:00', mktime(0, 0, 0, date('m'), date('d')-7, date('Y')));
315	$last30 = date('Y-m-d 00:00:00', mktime(0, 0, 0, date('m'), date('d')-30, date('Y')));
316	$last90 = date('Y-m-d 00:00:00', mktime(0, 0, 0, date('m'), date('d')-90, date('Y')));
317
318	if(!WCUtils::is_blank($this->searchSql)){
319	if( 'csmb_' === substr($this->arr_search['column'], 0, 5) ){
320	$str .= ' WHERE ' . $this->searchSql;
321	}else{
322	$str .= ' HAVING ' . $this->searchSql;
323	}
324	}
325
326	return $str;
327	}
328
329	function SearchIn()
330	{
331	global $wpdb;
332	if($this->arr_search['column'] == 'none' \|\| WCUtils::is_blank($this->arr_search['column']) \|\| WCUtils::is_blank($this->arr_search['word']) ){
333	$this->searchSql = '';
334	}else{
335	if( 'csmb_' === substr($this->arr_search['column'], 0, 5) ){
336	$this->searchSql = $wpdb->prepare('`meta_value` LIKE %s', "%".$this->arr_search['word']."%");
337	}else{
338	$this->searchSql = $wpdb->prepare('`' . esc_sql($this->arr_search['column']) . '` LIKE %s', "%".$this->arr_search['word']."%");
339	}
340	}
341	}
342
343	function SearchOut()
344	{
345	$this->searchSql = '';
346	}
347
348	function SetNavi()
349	{
350
351	$this->lastPage = ceil($this->selectedRow / $this->maxRow);
352	$this->previousPage = ($this->currentPage - 1 == 0) ? 1 : $this->currentPage - 1;
353	$this->nextPage = ($this->currentPage + 1 > $this->lastPage) ? $this->lastPage : $this->currentPage + 1;
354	$box = array();
355
356	for($i=0; $i<$this->naviMaxButton; $i++){
357	if($i > $this->lastPage-1) break;
358	if($this->lastPage <= $this->naviMaxButton) {
359	$box[] = $i+1;
360	}else{
361	if($this->currentPage <= 6) {
362	$label = $i + 1;
363	$box[] = $label;
364	}else{
365	$label = $i + 1 + $this->currentPage - 6;
366	$box[] = $label;
367	if($label == $this->lastPage) break;
368	}
369	}
370	}
371
372	$html = '';
373	$html .= '<ul class="clearfix">'."\n";
374	$html .= '<li class="rowsnum">' . $this->selectedRow . ' / ' . $this->totalRow . ' ' . __('cases', 'usces') . '</li>' . "\n";
375	if(($this->currentPage == 1) \|\| ($this->selectedRow == 0)){
376	$html .= '<li class="navigationStr">first<<</li>' . "\n";
377	$html .= '<li class="navigationStr">prev<</li>'."\n";
378	}else{
379	$html .= '<li class="navigationStr"><a href="' . site_url() . '/wp-admin/admin.php?page=usces_memberlist&changePage=1">first<<</a></li>' . "\n";
380	$html .= '<li class="navigationStr"><a href="' . site_url() . '/wp-admin/admin.php?page=usces_memberlist&changePage=' . $this->previousPage . '">prev<</a></li>'."\n";
381	}
382	if($this->selectedRow > 0) {
383	$box_count = count( $box );
384	for($i=0; $i<$box_count; $i++){
385	if($box[$i] == $this->currentPage){
386	$html .= '<li class="navigationButtonSelected">' . $box[$i] . '</li>'."\n";
387	}else{
388	$html .= '<li class="navigationButton"><a href="' . site_url() . '/wp-admin/admin.php?page=usces_memberlist&changePage=' . $box[$i] . '">' . $box[$i] . '</a></li>'."\n";
389	}
390	}
391	}
392	if(($this->currentPage == $this->lastPage) \|\| ($this->selectedRow == 0)){
393	$html .= '<li class="navigationStr">>next</li>'."\n";
394	$html .= '<li class="navigationStr">>>last</li>'."\n";
395	}else{
396	$html .= '<li class="navigationStr"><a href="' . site_url() . '/wp-admin/admin.php?page=usces_memberlist&changePage=' . $this->nextPage . '">>next</a></li>'."\n";
397	$html .= '<li class="navigationStr"><a href="' . site_url() . '/wp-admin/admin.php?page=usces_memberlist&changePage=' . $this->lastPage . '">>>last</a></li>'."\n";
398	}
399	if($this->searchSwitchStatus == 'OFF'){
400	$html .= '<li class="rowsnum"><a style="cursor:pointer;" id="searchVisiLink">' . __('Show the Operation field', 'usces') . '</a>'."\n";
401	}else{
402	$html .= '<li class="rowsnum"><a style="cursor:pointer;" id="searchVisiLink">' . __('Hide the Operation field', 'usces') . '</a>'."\n";
403	}
404
405	$html .= '<li class="refresh"><a href="' . site_url() . '/wp-admin/admin.php?page=usces_memberlist&refresh">' . __('updates it to latest information', 'usces') . '</a></li>' . "\n";
406	$html .= '</ul>'."\n";
407
408	$this->dataTableNavigation = $html;
409	}
410
411
412	function getCookie(){
413	$this->data_cookie = (isset($_COOKIE[$this->table])) ? json_decode(str_replace("\'","'",str_replace('\"','"', $_COOKIE[$this->table])),true) : [];
414	}
415
416	function SetHeaders()
417	{
418	foreach ($this->columns as $key => $value){
419	if($value == $this->sortColumn){
420	if($this->sortSwitchs[$value] == 'ASC'){
421	$str = __('[ASC]', 'usces');
422	$switch = 'DESC';
423	}else{
424	$str = __('[DESC]', 'usces');
425	$switch = 'ASC';
426	}
427	$this->headers[$value] = '<a href="' . site_url() . '/wp-admin/admin.php?page=usces_memberlist&changeSort=' . $value . '&switch=' . $switch . '"><span class="sortcolumn">' . $key . ' ' . $str . '</span></a>';
428	}else{
429	$switch = $this->sortSwitchs[$value];
430	$this->headers[$value] = '<a href="' . site_url() . '/wp-admin/admin.php?page=usces_memberlist&changeSort=' . $value . '&switch=' . $switch . '"><span>' . $key . '</span></a>';
431	}
432	}
433	}
434
435	function GetSearchs()
436	{
437	return $this->arr_search;
438	}
439
440	function GetListheaders()
441	{
442	return $this->headers;
443	}
444
445	function GetDataTableNavigation()
446	{
447	return $this->dataTableNavigation;
448	}
449
450	}
451