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

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

463 lines 19.1 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 = wp_parse_args(
101	$this->data_cookie['arr_search'],
102	array(
103	'period' => '3',
104	'column' => '',
105	'word' => '',
106	)
107	);
108	} else {
109	$this->arr_search = array('period'=>'3', 'column'=>'', 'word'=>'');
110	}
111	if (isset($this->data_cookie['sortSwitchs'])) {
112	$this->sortSwitchs = $this->data_cookie['sortSwitchs'];
113	foreach ( $this->columns as $key => $value ) {
114	if ( ! isset( $this->sortSwitchs[ $value ] ) ) {
115	$this->sortSwitchs[ $value ] = 'DESC';
116	}
117	}
118	} else {
119	foreach($this->columns as $key => $value ){
120	$this->sortSwitchs[$value] = 'DESC';
121	}
122	}
123	$this->SetTotalRow();
124	$this->SetSelectedRow();
125
126	}
127
128	function SetParam()
129	{
130	$this->startRow = ($this->currentPage-1) * $this->maxRow;
131	}
132
133	function SetParamByQuery()
134	{
135	global $wpdb;
136
137	if(isset($_REQUEST['changePage'])){
138
139	$this->action = 'changePage';
140	$this->currentPage = (int)$_REQUEST['changePage'];
141	$this->sortColumn = (isset($this->data_cookie['sortColumn'])) ? $this->data_cookie['sortColumn'] : $this->sortColumn;
142	$this->sortSwitchs = (isset($this->data_cookie['sortSwitchs'])) ? $this->data_cookie['sortSwitchs'] : $this->sortSwitchs;
143	$this->userHeaderNames = (isset($this->data_cookie['userHeaderNames'])) ? $this->data_cookie['userHeaderNames'] : $this->userHeaderNames;
144	$this->searchSql = (isset($this->data_cookie['searchSql'])) ? $this->data_cookie['searchSql'] : $this->searchSql;
145	$this->arr_search = (isset($this->data_cookie['arr_search'])) ? $this->data_cookie['arr_search'] : $this->arr_search;
146	$this->totalRow = (isset($this->data_cookie['totalRow'])) ? $this->data_cookie['totalRow'] : $this->totalRow;
147	$this->selectedRow = (isset($this->data_cookie['selectedRow'])) ? $this->data_cookie['selectedRow'] : $this->selectedRow;
148	$this->placeholder_escape = (isset($this->data_cookie['placeholder_escape'])) ? $this->data_cookie['placeholder_escape'] : $this->placeholder_escape;
149
150	}else if(isset($_REQUEST['changeSort'])){
151
152	$this->action = 'changeSort';
153	$this->sortOldColumn = $this->sortColumn;
154	// Validate sortColumn.
155	if ( in_array( $_REQUEST['changeSort'], $this->columns ) ) {
156	$this->sortColumn = $_REQUEST['changeSort'];
157	} else {
158	$this->sortColumn = 'ID'; // default.
159	}
160	$this->sortSwitchs = (isset($this->data_cookie['sortSwitchs'])) ? $this->data_cookie['sortSwitchs'] : $this->sortSwitchs;
161	// Validate sortSwitchs.
162	if (isset($_REQUEST['switch']) && in_array($_REQUEST['switch'], array('ASC', 'DESC'))) {
163	$this->sortSwitchs[$this->sortColumn] = $_REQUEST['switch'];
164	} else {
165	$this->sortSwitchs[$this->sortColumn] = 'DESC'; // default.
166	}
167	$this->currentPage = (isset($this->data_cookie['currentPage'])) ? $this->data_cookie['currentPage'] : $this->currentPage;
168	$this->userHeaderNames = (isset($this->data_cookie['userHeaderNames'])) ? $this->data_cookie['userHeaderNames'] : $this->userHeaderNames;
169	$this->searchSql = (isset($this->data_cookie['searchSql'])) ? $this->data_cookie['searchSql'] : $this->searchSql;
170	$this->searchSwitchStatus = (isset($this->data_cookie['searchSwitchStatus'])) ? $this->data_cookie['searchSwitchStatus'] : $this->searchSwitchStatus;
171	$this->arr_search = (isset($this->data_cookie['arr_search'])) ? $this->data_cookie['arr_search'] : $this->arr_search;
172	$this->totalRow = (isset($this->data_cookie['totalRow'])) ? $this->data_cookie['totalRow'] : $this->totalRow;
173	$this->selectedRow = (isset($this->data_cookie['selectedRow'])) ? $this->data_cookie['selectedRow'] : $this->selectedRow;
174	$this->placeholder_escape = (isset($this->data_cookie['placeholder_escape'])) ? $this->data_cookie['placeholder_escape'] : $this->placeholder_escape;
175
176	} else if(isset($_REQUEST['searchIn'])){
177
178	$this->action = 'searchIn';
179	$this->arr_search['column'] = str_replace('`', '', $_REQUEST['search']['column']);
180	$this->arr_search['word'] = $_REQUEST['search']['word'];
181	$this->arr_search['period'] = isset($_REQUEST['search']['period']) ? intval($_REQUEST['search']['period']) : 0;
182	$this->searchSwitchStatus = str_replace(',', '', $_REQUEST['searchSwitchStatus']);
183
184	$this->currentPage = 1;
185	$this->sortColumn = (isset($this->data_cookie['sortColumn'])) ? $this->data_cookie['sortColumn'] : $this->sortColumn;
186	$this->sortSwitchs = (isset($this->data_cookie['sortSwitchs'])) ? $this->data_cookie['sortSwitchs'] : $this->sortSwitchs;
187	$this->userHeaderNames = (isset($this->data_cookie['userHeaderNames'])) ? $this->data_cookie['userHeaderNames'] : $this->userHeaderNames;
188	$this->totalRow = (isset($this->data_cookie['totalRow'])) ? $this->data_cookie['totalRow'] : $this->totalRow;
189	$this->placeholder_escape = $wpdb->placeholder_escape();
190
191	}else if(isset($_REQUEST['searchOut'])){
192
193	$this->action = 'searchOut';
194	$this->arr_search['column'] = '';
195	$this->arr_search['word'] = '';
196	$this->arr_search['period'] = (isset($this->data_cookie['arr_search']['period'])) ? $this->data_cookie['arr_search']['period'] : $this->arr_search['period'];
197	$this->searchSwitchStatus = str_replace(',', '', $_REQUEST['searchSwitchStatus']);
198
199	$this->currentPage = 1;
200	$this->sortColumn = (isset($this->data_cookie['sortColumn'])) ? $this->data_cookie['sortColumn'] : $this->sortColumn;
201	$this->sortSwitchs = (isset($this->data_cookie['sortSwitchs'])) ? $this->data_cookie['sortSwitchs'] : $this->sortSwitchs;
202	$this->userHeaderNames = (isset($this->data_cookie['userHeaderNames'])) ? $this->data_cookie['userHeaderNames'] : $this->userHeaderNames;
203	$this->totalRow = (isset($this->data_cookie['totalRow'])) ? $this->data_cookie['totalRow'] : $this->totalRow;
204	$this->placeholder_escape = '';
205
206	}else if(isset($_REQUEST['refresh'])){
207
208	$this->action = 'refresh';
209
210	$this->currentPage = (isset($this->data_cookie['currentPage'])) ? $this->data_cookie['currentPage'] : $this->currentPage;
211	$this->sortColumn = (isset($this->data_cookie['sortColumn'])) ? $this->data_cookie['sortColumn'] : $this->sortColumn;
212	$this->sortSwitchs = (isset($this->data_cookie['sortSwitchs'])) ? $this->data_cookie['sortSwitchs'] : $this->sortSwitchs;
213	$this->userHeaderNames = (isset($this->data_cookie['userHeaderNames'])) ? $this->data_cookie['userHeaderNames'] : $this->userHeaderNames;
214	$this->searchSql = (isset($this->data_cookie['searchSql'])) ? $this->data_cookie['searchSql'] : $this->searchSql;
215	$this->searchSwitchStatus = (isset($this->data_cookie['searchSwitchStatus'])) ? $this->data_cookie['searchSwitchStatus'] : $this->searchSwitchStatus;
216	$this->arr_search = (isset($this->data_cookie['arr_search'])) ? $this->data_cookie['arr_search'] : $this->arr_search;
217	$this->totalRow = (isset($this->data_cookie['totalRow'])) ? $this->data_cookie['totalRow'] : $this->totalRow;
218	$this->selectedRow = (isset($this->data_cookie['selectedRow'])) ? $this->data_cookie['selectedRow'] : $this->selectedRow;
219	$this->placeholder_escape = '';
220
221	}else{
222
223	$this->action = 'default';
224	$this->placeholder_escape = '';
225	}
226	}
227
228	function validationSearchParameters(){
229	if( 'none' != $this->arr_search['column'] && !in_array($this->arr_search['column'], $this->columns)){
230	$this->arr_search['column'] = reset($this->columns);
231	}
232	}
233
234	//GetRows
235	function GetRows()
236	{
237	global $wpdb;
238
239	$member_meta_table = usces_get_tablename( 'usces_member_meta' );
240
241	if($this->arr_search['column'] == 'none' \|\| WCUtils::is_blank($this->arr_search['column']) \|\| WCUtils::is_blank($this->arr_search['word']) ){
242	$join = "";
243	}else{
244	if( 'csmb_' === substr($this->arr_search['column'], 0, 5) ){
245	$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']) );
246	}else{
247	$join = "";
248	}
249	}
250
251	$where = $this->GetWhere();
252
253	$init_column = 'ID';
254	$sort_column = in_array( $this->sortColumn, $this->columns, true ) ? $this->sortColumn : $init_column;
255	$init_switch = 'DESC';
256	$sort_switch = $this->sortSwitchs[ $sort_column ] ?? $init_switch;
257	$sort_switch = in_array( strtoupper( $sort_switch ), array( 'ASC', 'DESC' ), true ) ? $sort_switch : $init_switch;
258	$order = ' ORDER BY ' . esc_sql( $sort_column ) . ' ' . esc_sql( $sort_switch );
259
260	if($this->pageLimit == 'on') {
261	$limit = $wpdb->prepare(' LIMIT %d, %d', $this->startRow, $this->maxRow);
262	}else{
263	$limit = '';
264	}
265	$mem_point = ( usces_is_membersystem_point() ) ? ', mem_point AS `point`' : '';
266	$query = $wpdb->prepare("SELECT ID, CONCAT(mem_name1, ' ', mem_name2) AS `name`,
267	CONCAT(mem_pref, mem_address1, mem_address2, ' ', mem_address3) AS `address`,
268	mem_tel AS `tel`, mem_email AS `email`, DATE_FORMAT(mem_registered, %s) AS `date`{$mem_point}
269	FROM {$this->table}",
270	'%Y-%m-%d %H:%i');
271
272	$query .= $join . $where . $order . $limit;
273
274	if( $this->placeholder_escape ){
275	add_filter( 'query', array( $this, 'remove_ph') );
276	}
277
278	$this->rows = $wpdb->get_results($query, ARRAY_A);
279	return $this->rows;
280	}
281
282	public function remove_ph( $query ) {
283	return str_replace( $this->placeholder_escape, '%', $query );
284	}
285
286	function SetTotalRow()
287	{
288	global $wpdb;
289	$query = "SELECT COUNT(ID) AS `ct` FROM {$this->table}";
290	$res = $wpdb->get_var($query);
291	$this->totalRow = $res;
292	}
293
294	function SetSelectedRow()
295	{
296	global $wpdb;
297
298	$member_meta_table = usces_get_tablename( 'usces_member_meta' );
299	if($this->arr_search['column'] == 'none' \|\| WCUtils::is_blank($this->arr_search['column']) \|\| WCUtils::is_blank($this->arr_search['word']) ){
300	$join = "";
301	}else{
302	if( 'csmb_' === substr($this->arr_search['column'], 0, 5) ){
303	$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']) );
304	}else{
305	$join = "";
306	}
307	}
308	$where = $this->GetWhere();
309	$mem_point = ( usces_is_membersystem_point() ) ? ', mem_point AS `point`' : '';
310	$query = $wpdb->prepare("SELECT ID, CONCAT(mem_name1, ' ', mem_name2) AS `name`,
311	CONCAT(mem_pref, mem_address1, mem_address2, ' ', mem_address3) AS `address`,
312	mem_tel AS `tel`, mem_email AS `email`, DATE_FORMAT(mem_registered, %s) AS `date`{$mem_point}
313	FROM {$this->table}",
314	'%Y-%m-%d %H:%i');
315	$query .= $join . $where;
316	$rows = $wpdb->get_results($query, ARRAY_A);
317	$this->selectedRow = ( $rows && is_array( $rows ) ) ? count( $rows ) : 0;
318
319	}
320
321	function GetWhere()
322	{
323	$str = '';
324	$thismonth = date('Y-m-01 00:00:00');
325	$lastmonth = date('Y-m-01 00:00:00', mktime(0, 0, 0, date('m')-1, 1, date('Y')));
326	$lastweek = date('Y-m-d 00:00:00', mktime(0, 0, 0, date('m'), date('d')-7, date('Y')));
327	$last30 = date('Y-m-d 00:00:00', mktime(0, 0, 0, date('m'), date('d')-30, date('Y')));
328	$last90 = date('Y-m-d 00:00:00', mktime(0, 0, 0, date('m'), date('d')-90, date('Y')));
329
330	if(!WCUtils::is_blank($this->searchSql)){
331	if( 'csmb_' === substr($this->arr_search['column'], 0, 5) ){
332	$str .= ' WHERE ' . $this->searchSql;
333	}else{
334	$str .= ' HAVING ' . $this->searchSql;
335	}
336	}
337
338	return $str;
339	}
340
341	function SearchIn()
342	{
343	global $wpdb;
344	if($this->arr_search['column'] == 'none' \|\| WCUtils::is_blank($this->arr_search['column']) \|\| WCUtils::is_blank($this->arr_search['word']) ){
345	$this->searchSql = '';
346	}else{
347	if( 'csmb_' === substr($this->arr_search['column'], 0, 5) ){
348	$this->searchSql = $wpdb->prepare('`meta_value` LIKE %s', "%".$this->arr_search['word']."%");
349	}else{
350	$this->searchSql = $wpdb->prepare('`' . esc_sql($this->arr_search['column']) . '` LIKE %s', "%".$this->arr_search['word']."%");
351	}
352	}
353	}
354
355	function SearchOut()
356	{
357	$this->searchSql = '';
358	}
359
360	function SetNavi()
361	{
362
363	$this->lastPage = ceil($this->selectedRow / $this->maxRow);
364	$this->previousPage = ($this->currentPage - 1 == 0) ? 1 : $this->currentPage - 1;
365	$this->nextPage = ($this->currentPage + 1 > $this->lastPage) ? $this->lastPage : $this->currentPage + 1;
366	$box = array();
367
368	for($i=0; $i<$this->naviMaxButton; $i++){
369	if($i > $this->lastPage-1) break;
370	if($this->lastPage <= $this->naviMaxButton) {
371	$box[] = $i+1;
372	}else{
373	if($this->currentPage <= 6) {
374	$label = $i + 1;
375	$box[] = $label;
376	}else{
377	$label = $i + 1 + $this->currentPage - 6;
378	$box[] = $label;
379	if($label == $this->lastPage) break;
380	}
381	}
382	}
383
384	$html = '';
385	$html .= '<ul class="clearfix">'."\n";
386	$html .= '<li class="rowsnum">' . $this->selectedRow . ' / ' . $this->totalRow . ' ' . __('cases', 'usces') . '</li>' . "\n";
387	if(($this->currentPage == 1) \|\| ($this->selectedRow == 0)){
388	$html .= '<li class="navigationStr">first<<</li>' . "\n";
389	$html .= '<li class="navigationStr">prev<</li>'."\n";
390	}else{
391	$html .= '<li class="navigationStr"><a href="' . site_url() . '/wp-admin/admin.php?page=usces_memberlist&changePage=1">first<<</a></li>' . "\n";
392	$html .= '<li class="navigationStr"><a href="' . site_url() . '/wp-admin/admin.php?page=usces_memberlist&changePage=' . $this->previousPage . '">prev<</a></li>'."\n";
393	}
394	if($this->selectedRow > 0) {
395	$box_count = count( $box );
396	for($i=0; $i<$box_count; $i++){
397	if($box[$i] == $this->currentPage){
398	$html .= '<li class="navigationButtonSelected">' . $box[$i] . '</li>'."\n";
399	}else{
400	$html .= '<li class="navigationButton"><a href="' . site_url() . '/wp-admin/admin.php?page=usces_memberlist&changePage=' . $box[$i] . '">' . $box[$i] . '</a></li>'."\n";
401	}
402	}
403	}
404	if(($this->currentPage == $this->lastPage) \|\| ($this->selectedRow == 0)){
405	$html .= '<li class="navigationStr">>next</li>'."\n";
406	$html .= '<li class="navigationStr">>>last</li>'."\n";
407	}else{
408	$html .= '<li class="navigationStr"><a href="' . site_url() . '/wp-admin/admin.php?page=usces_memberlist&changePage=' . $this->nextPage . '">>next</a></li>'."\n";
409	$html .= '<li class="navigationStr"><a href="' . site_url() . '/wp-admin/admin.php?page=usces_memberlist&changePage=' . $this->lastPage . '">>>last</a></li>'."\n";
410	}
411	if($this->searchSwitchStatus == 'OFF'){
412	$html .= '<li class="rowsnum"><a style="cursor:pointer;" id="searchVisiLink">' . __('Show the Operation field', 'usces') . '</a>'."\n";
413	}else{
414	$html .= '<li class="rowsnum"><a style="cursor:pointer;" id="searchVisiLink">' . __('Hide the Operation field', 'usces') . '</a>'."\n";
415	}
416
417	$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";
418	$html .= '</ul>'."\n";
419
420	$this->dataTableNavigation = $html;
421	}
422
423
424	function getCookie(){
425	$this->data_cookie = (isset($_COOKIE[$this->table])) ? json_decode(str_replace("\'","'",str_replace('\"','"', $_COOKIE[$this->table])),true) : [];
426	}
427
428	function SetHeaders()
429	{
430	foreach ($this->columns as $key => $value){
431	if($value == $this->sortColumn){
432	if($this->sortSwitchs[$value] == 'ASC'){
433	$str = __('[ASC]', 'usces');
434	$switch = 'DESC';
435	}else{
436	$str = __('[DESC]', 'usces');
437	$switch = 'ASC';
438	}
439	$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>';
440	}else{
441	$switch = $this->sortSwitchs[$value];
442	$this->headers[$value] = '<a href="' . site_url() . '/wp-admin/admin.php?page=usces_memberlist&changeSort=' . $value . '&switch=' . $switch . '"><span>' . $key . '</span></a>';
443	}
444	}
445	}
446
447	function GetSearchs()
448	{
449	return $this->arr_search;
450	}
451
452	function GetListheaders()
453	{
454	return $this->headers;
455	}
456
457	function GetDataTableNavigation()
458	{
459	return $this->dataTableNavigation;
460	}
461
462	}
463