1 | <?php |
---|
2 | /** |
---|
3 | * @copyright 2005-2006 OpenPNE Project |
---|
4 | * @license http://www.php.net/license/3_01.txt PHP License 3.01 |
---|
5 | */ |
---|
6 | |
---|
7 | /** |
---|
8 | * ユーザーリスト取得 |
---|
9 | */ |
---|
10 | function db_admin_c_member_list($page, $page_size, &$pager) |
---|
11 | { |
---|
12 | $sql = 'SELECT c_member_id FROM c_member ORDER BY c_member_id'; |
---|
13 | $ids = db_get_col_page($sql, $page, $page_size); |
---|
14 | |
---|
15 | $c_member_list = array(); |
---|
16 | foreach ($ids as $id) { |
---|
17 | $c_member_list[] = db_common_c_member4c_member_id($id, true, true, 'private'); |
---|
18 | } |
---|
19 | |
---|
20 | $sql = 'SELECT COUNT(*) FROM c_member'; |
---|
21 | $total_num = db_get_one($sql); |
---|
22 | $pager = admin_make_pager($page, $page_size, $total_num); |
---|
23 | |
---|
24 | return $c_member_list; |
---|
25 | } |
---|
26 | |
---|
27 | function db_admin_c_member4mail_address($mail_address) |
---|
28 | { |
---|
29 | $sql = 'SELECT c_member_id FROM c_member_secure' . |
---|
30 | ' WHERE pc_address = ? OR ktai_address = ? OR regist_address = ?'; |
---|
31 | $enc_address = t_encrypt($mail_address); |
---|
32 | $params = array($enc_address, $enc_address, $enc_address); |
---|
33 | $list = db_get_col($sql, $params); |
---|
34 | |
---|
35 | $c_member_list = array(); |
---|
36 | foreach ($list as $c_member_id) { |
---|
37 | $c_member_list[] = db_common_c_member4c_member_id($c_member_id, true, true, 'private'); |
---|
38 | } |
---|
39 | return $c_member_list; |
---|
40 | } |
---|
41 | |
---|
42 | function db_admin_c_siteadmin($target) |
---|
43 | { |
---|
44 | $sql = 'SELECT * FROM c_siteadmin WHERE target = ?'; |
---|
45 | $params = array($target); |
---|
46 | return db_get_row($sql, $params); |
---|
47 | } |
---|
48 | |
---|
49 | function db_admin_insert_c_siteadmin($target, $body) |
---|
50 | { |
---|
51 | $data = array( |
---|
52 | 'target' => $target, |
---|
53 | 'body' => $body, |
---|
54 | 'r_date' => db_now(), |
---|
55 | ); |
---|
56 | return db_insert('c_siteadmin', $data); |
---|
57 | } |
---|
58 | |
---|
59 | function db_admin_update_c_siteadmin($target, $body) |
---|
60 | { |
---|
61 | $data = array( |
---|
62 | 'body' => $body, |
---|
63 | 'r_date' => db_now(), |
---|
64 | ); |
---|
65 | $where = array('target' => $target); |
---|
66 | return db_update('c_siteadmin', $data, $where); |
---|
67 | } |
---|
68 | |
---|
69 | function db_admin_update_c_sns_config($data) |
---|
70 | { |
---|
71 | $where = array('c_sns_config_id' => 1); |
---|
72 | return db_update('c_sns_config', $data, $where); |
---|
73 | } |
---|
74 | |
---|
75 | function db_admin_delete_c_profile_option($c_profile_option_id) |
---|
76 | { |
---|
77 | //function cache削除 |
---|
78 | pne_cache_drop('db_member_c_profile_list'); |
---|
79 | |
---|
80 | if (!$c_profile_option_id) { |
---|
81 | return false; |
---|
82 | } |
---|
83 | |
---|
84 | $sql = 'DELETE FROM c_member_profile WHERE c_profile_option_id = ?'; |
---|
85 | $params = array(intval($c_profile_option_id)); |
---|
86 | db_query($sql, $params); |
---|
87 | |
---|
88 | $sql = 'DELETE FROM c_profile_option WHERE c_profile_option_id = ?'; |
---|
89 | db_query($sql, $params); |
---|
90 | |
---|
91 | return true; |
---|
92 | } |
---|
93 | |
---|
94 | function db_admin_insert_c_profile_option($c_profile_id, $value, $sort_order) |
---|
95 | { |
---|
96 | //function cache削除 |
---|
97 | pne_cache_drop('db_member_c_profile_list'); |
---|
98 | |
---|
99 | $data = array( |
---|
100 | 'c_profile_id' => intval($c_profile_id), |
---|
101 | 'value' => $value, |
---|
102 | 'sort_order' => intval($sort_order), |
---|
103 | ); |
---|
104 | return db_insert('c_profile_option', $data); |
---|
105 | } |
---|
106 | |
---|
107 | function db_admin_update_c_profile_option($c_profile_option_id, $value, $sort_order) |
---|
108 | { |
---|
109 | //function cache削除 |
---|
110 | pne_cache_drop('db_member_c_profile_list'); |
---|
111 | |
---|
112 | $data = array('value' => $value); |
---|
113 | $where = array('c_profile_option_id' => intval($c_profile_option_id)); |
---|
114 | db_update('c_member_profile', $data, $where); |
---|
115 | |
---|
116 | $data = array( |
---|
117 | 'value' => $value, |
---|
118 | 'sort_order' => intval($sort_order), |
---|
119 | ); |
---|
120 | db_update('c_profile_option', $data, $where); |
---|
121 | } |
---|
122 | |
---|
123 | function db_admin_insert_c_banner($a_href, $type, $nickname) |
---|
124 | { |
---|
125 | $data = array( |
---|
126 | 'a_href' => $a_href, |
---|
127 | 'type' => $type, |
---|
128 | 'nickname' => $nickname, |
---|
129 | 'is_hidden_after' => 0, |
---|
130 | 'is_hidden_before' => 0, |
---|
131 | ); |
---|
132 | return db_insert('c_banner', $data); |
---|
133 | } |
---|
134 | |
---|
135 | function db_admin_update_c_banner($c_banner_id, $sets) |
---|
136 | { |
---|
137 | $where = array('c_banner_id' => intval($c_banner_id)); |
---|
138 | db_update('c_banner', $sets, $where); |
---|
139 | } |
---|
140 | |
---|
141 | function db_admin_delete_c_banner($c_banner_id) |
---|
142 | { |
---|
143 | db_admin_delete_c_image4c_banner_id($c_banner_id); |
---|
144 | |
---|
145 | $sql = 'DELETE FROM c_banner WHERE c_banner_id = ?'; |
---|
146 | $params = array(intval($c_banner_id)); |
---|
147 | db_query($sql, $params); |
---|
148 | } |
---|
149 | |
---|
150 | function db_admin_delete_c_image4c_banner_id($c_banner_id) |
---|
151 | { |
---|
152 | $sql = 'SELECT image_filename FROM c_banner WHERE c_banner_id = ?'; |
---|
153 | $params = array(intval($c_banner_id)); |
---|
154 | $image_filename = db_get_one($sql, $params); |
---|
155 | image_data_delete($image_filename); |
---|
156 | } |
---|
157 | |
---|
158 | function db_admin_insert_c_profile( |
---|
159 | $name |
---|
160 | , $caption |
---|
161 | , $info |
---|
162 | , $is_required |
---|
163 | , $public_flag_edit |
---|
164 | , $public_flag_default |
---|
165 | , $form_type |
---|
166 | , $sort_order |
---|
167 | , $disp_regist |
---|
168 | , $disp_config |
---|
169 | , $disp_search |
---|
170 | , $val_type |
---|
171 | , $val_regexp |
---|
172 | , $val_min |
---|
173 | , $val_max |
---|
174 | ) |
---|
175 | { |
---|
176 | $data = array( |
---|
177 | 'name' => $name, |
---|
178 | 'caption' => $caption, |
---|
179 | 'info' => $info, |
---|
180 | 'is_required' => (bool)$is_required, |
---|
181 | 'public_flag_edit' => (bool)$public_flag_edit, |
---|
182 | 'public_flag_default' => $public_flag_default, |
---|
183 | 'form_type' => $form_type, |
---|
184 | 'sort_order' => (int)$sort_order, |
---|
185 | 'disp_regist' => (bool)$disp_regist, |
---|
186 | 'disp_config' => (bool)$disp_config, |
---|
187 | 'disp_search' => (bool)$disp_search, |
---|
188 | 'val_type' => $val_type, |
---|
189 | 'val_regexp' => $val_regexp, |
---|
190 | 'val_min' => (int)$val_min, |
---|
191 | 'val_max' => (int)$val_max, |
---|
192 | ); |
---|
193 | return db_insert('c_profile', $data); |
---|
194 | } |
---|
195 | |
---|
196 | function db_admin_update_c_profile($c_profile_id |
---|
197 | , $name |
---|
198 | , $caption |
---|
199 | , $info |
---|
200 | , $is_required |
---|
201 | , $public_flag_edit |
---|
202 | , $public_flag_default |
---|
203 | , $form_type |
---|
204 | , $sort_order |
---|
205 | , $disp_regist |
---|
206 | , $disp_config |
---|
207 | , $disp_search |
---|
208 | , $val_type |
---|
209 | , $val_regexp |
---|
210 | , $val_min |
---|
211 | , $val_max |
---|
212 | ) |
---|
213 | { |
---|
214 | $data = array( |
---|
215 | 'name' => $name, |
---|
216 | 'caption' => $caption, |
---|
217 | 'info' => $info, |
---|
218 | 'is_required' => (bool)$is_required, |
---|
219 | 'public_flag_edit' => (bool)$public_flag_edit, |
---|
220 | 'public_flag_default' => $public_flag_default, |
---|
221 | 'form_type' => $form_type, |
---|
222 | 'sort_order' => intval($sort_order), |
---|
223 | 'disp_regist' => (bool)$disp_regist, |
---|
224 | 'disp_config' => (bool)$disp_config, |
---|
225 | 'disp_search' => (bool)$disp_search, |
---|
226 | 'val_type' => $val_type, |
---|
227 | 'val_regexp' => $val_regexp, |
---|
228 | 'val_min' => intval($val_min), |
---|
229 | 'val_max' => intval($val_max), |
---|
230 | ); |
---|
231 | $where = array('c_profile_id' => intval($c_profile_id)); |
---|
232 | db_update('c_profile', $data, $where); |
---|
233 | |
---|
234 | // 公開設定が固定のときはユーザーの設定値を上書き |
---|
235 | if (!$public_flag_edit) { |
---|
236 | $data = array('public_flag' => $public_flag_default); |
---|
237 | db_update('c_member_profile', $data, $where); |
---|
238 | } |
---|
239 | } |
---|
240 | |
---|
241 | function db_admin_delete_c_profile($c_profile_id) |
---|
242 | { |
---|
243 | $params = array(intval($c_profile_id)); |
---|
244 | |
---|
245 | // メンバーのプロフィールから削除 |
---|
246 | $sql = 'DELETE FROM c_member_profile WHERE c_profile_id = ?'; |
---|
247 | db_query($sql, $params); |
---|
248 | |
---|
249 | // 選択肢項目を削除 |
---|
250 | $sql = 'DELETE FROM c_profile_option WHERE c_profile_id = ?'; |
---|
251 | db_query($sql, $params); |
---|
252 | |
---|
253 | // プロフィール項目を削除 |
---|
254 | $sql = 'DELETE FROM c_profile WHERE c_profile_id = ?'; |
---|
255 | db_query($sql, $params); |
---|
256 | } |
---|
257 | |
---|
258 | function db_admin_c_profile4c_profile_id($c_profile_id) |
---|
259 | { |
---|
260 | $sql = 'SELECT * FROM c_profile WHERE c_profile_id = ?'; |
---|
261 | $params = array(intval($c_profile_id)); |
---|
262 | return db_get_row($sql, $params); |
---|
263 | } |
---|
264 | |
---|
265 | /** |
---|
266 | * 全バナー取得 |
---|
267 | * |
---|
268 | * @param int $limit 取得最大件数 |
---|
269 | * @return array_of_array c_banner_list バナー配列 |
---|
270 | */ |
---|
271 | function db_admin_c_banner_list4null($type = '') |
---|
272 | { |
---|
273 | $sql = 'SELECT * FROM c_banner'; |
---|
274 | $params = array(); |
---|
275 | if ($type) { |
---|
276 | $sql .= ' WHERE type = ?'; |
---|
277 | $params[] = $type; |
---|
278 | } |
---|
279 | return db_get_all($sql, $params); |
---|
280 | } |
---|
281 | |
---|
282 | function db_admin_c_commu_category_parent_list() |
---|
283 | { |
---|
284 | $sql = 'SELECT * FROM c_commu_category_parent ORDER BY sort_order'; |
---|
285 | return db_get_all($sql); |
---|
286 | } |
---|
287 | |
---|
288 | function db_admin_c_commu_category_list() |
---|
289 | { |
---|
290 | $sql = 'SELECT * FROM c_commu_category ORDER BY sort_order'; |
---|
291 | $list = db_get_all($sql); |
---|
292 | |
---|
293 | $category_list = array(); |
---|
294 | foreach ($list as $item) { |
---|
295 | $category_list[$item['c_commu_category_parent_id']][] = $item; |
---|
296 | } |
---|
297 | return $category_list; |
---|
298 | } |
---|
299 | |
---|
300 | function db_admin_insert_c_commu_category_parent($name, $sort_order) |
---|
301 | { |
---|
302 | $data = array( |
---|
303 | 'name' => $name, |
---|
304 | 'sort_order' => intval($sort_order), |
---|
305 | ); |
---|
306 | return db_insert('c_commu_category_parent', $data); |
---|
307 | } |
---|
308 | |
---|
309 | function db_admin_update_c_commu_category_parent($c_commu_category_parent_id, $name, $sort_order) |
---|
310 | { |
---|
311 | $data = array( |
---|
312 | 'name' => $name, |
---|
313 | 'sort_order' => intval($sort_order), |
---|
314 | ); |
---|
315 | $where = array( |
---|
316 | 'c_commu_category_parent_id' => intval($c_commu_category_parent_id) |
---|
317 | ); |
---|
318 | db_update('c_commu_category_parent', $data, $where); |
---|
319 | } |
---|
320 | |
---|
321 | function db_admin_delete_c_commu_category_parent($c_commu_category_parent_id) |
---|
322 | { |
---|
323 | $params = array(intval($c_commu_category_parent_id)); |
---|
324 | |
---|
325 | // 小カテゴリを削除 |
---|
326 | $sql = 'DELETE FROM c_commu_category WHERE c_commu_category_parent_id = ?'; |
---|
327 | db_query($sql, $params); |
---|
328 | |
---|
329 | // 中カテゴリを削除 |
---|
330 | $sql = 'DELETE FROM c_commu_category_parent WHERE c_commu_category_parent_id = ?'; |
---|
331 | db_query($sql, $params); |
---|
332 | } |
---|
333 | |
---|
334 | function db_admin_insert_c_commu_category($c_commu_category_parent_id, $name, $sort_order) |
---|
335 | { |
---|
336 | $data = array( |
---|
337 | 'c_commu_category_parent_id' => intval($c_commu_category_parent_id), |
---|
338 | 'name' => $name, |
---|
339 | 'sort_order' => intval($sort_order), |
---|
340 | ); |
---|
341 | return db_insert('c_commu_category', $data); |
---|
342 | } |
---|
343 | |
---|
344 | function db_admin_update_c_commu_category($c_commu_category_id, $name, $sort_order) |
---|
345 | { |
---|
346 | $data = array( |
---|
347 | 'name' => $name, |
---|
348 | 'sort_order' => intval($sort_order) |
---|
349 | ); |
---|
350 | $where = array('c_commu_category_id' => intval($c_commu_category_id)); |
---|
351 | db_update('c_commu_category', $data, $where); |
---|
352 | } |
---|
353 | |
---|
354 | function db_admin_delete_c_commu_category($c_commu_category_id) |
---|
355 | { |
---|
356 | // 小カテゴリを削除 |
---|
357 | $sql = 'DELETE FROM c_commu_category WHERE c_commu_category_id = ?'; |
---|
358 | $params = array(intval($c_commu_category_id)); |
---|
359 | db_query($sql, $params); |
---|
360 | } |
---|
361 | |
---|
362 | function db_admin_c_admin_user_id4username($username) |
---|
363 | { |
---|
364 | $sql = 'SELECT c_admin_user_id FROM c_admin_user WHERE username = ?'; |
---|
365 | $params = array($username); |
---|
366 | return db_get_one($sql, $params); |
---|
367 | } |
---|
368 | |
---|
369 | function db_admin_authenticate_password($uid, $password) |
---|
370 | { |
---|
371 | $sql = 'SELECT c_admin_user_id FROM c_admin_user WHERE c_admin_user_id = ? AND password = ?'; |
---|
372 | $params = array(intval($uid), md5($password)); |
---|
373 | return (bool)db_get_one($sql, $params); |
---|
374 | } |
---|
375 | |
---|
376 | function db_admin_update_c_admin_user_password($uid, $password) |
---|
377 | { |
---|
378 | $data = array('password' => md5($password)); |
---|
379 | $where = array('c_admin_user_id' => intval($uid)); |
---|
380 | db_update('c_admin_user', $data, $where); |
---|
381 | } |
---|
382 | |
---|
383 | function db_admin_c_admin_config4name($name) |
---|
384 | { |
---|
385 | $sql = 'SELECT value FROM c_admin_config WHERE name = ?'; |
---|
386 | $params = array($name); |
---|
387 | return db_get_one($sql, $params); |
---|
388 | } |
---|
389 | |
---|
390 | function db_admin_insert_c_admin_config($name, $value) |
---|
391 | { |
---|
392 | $data = array( |
---|
393 | 'name' => $name, |
---|
394 | 'value' => $value, |
---|
395 | ); |
---|
396 | return db_insert('c_admin_config', $data); |
---|
397 | } |
---|
398 | |
---|
399 | function db_admin_update_c_admin_config($name, $value) |
---|
400 | { |
---|
401 | $data = array('value' => $value); |
---|
402 | $where = array('name' => $name); |
---|
403 | db_update('c_admin_config', $data, $where); |
---|
404 | } |
---|
405 | |
---|
406 | function db_admin_replace_c_admin_config($name, $value) |
---|
407 | { |
---|
408 | $sql = 'DELETE FROM c_admin_config WHERE name = ?'; |
---|
409 | $params = array($name); |
---|
410 | db_query($sql, $params); |
---|
411 | |
---|
412 | $data = array( |
---|
413 | 'name' => strval($name), |
---|
414 | 'value' => strval($value), |
---|
415 | ); |
---|
416 | return db_insert('c_admin_config', $data); |
---|
417 | } |
---|
418 | |
---|
419 | function db_admin_c_admin_config_all() |
---|
420 | { |
---|
421 | $sql = 'SELECT name, value FROM c_admin_config'; |
---|
422 | return db_get_assoc($sql); |
---|
423 | } |
---|
424 | |
---|
425 | function db_admin_delete_c_image_link4image_filename($image_filename) |
---|
426 | { |
---|
427 | // c_banner (削除) |
---|
428 | $sql = 'DELETE FROM c_banner WHERE image_filename = ?'; |
---|
429 | $params = array($image_filename); |
---|
430 | db_query($sql, $params); |
---|
431 | |
---|
432 | // c_commu |
---|
433 | $tbl = 'c_commu'; |
---|
434 | _db_admin_empty_image_filename($tbl, $image_filename); |
---|
435 | |
---|
436 | // c_commu_topic_comment |
---|
437 | $tbl = 'c_commu_topic_comment'; |
---|
438 | _db_admin_empty_image_filename($tbl, $image_filename, 'image_filename1'); |
---|
439 | _db_admin_empty_image_filename($tbl, $image_filename, 'image_filename2'); |
---|
440 | _db_admin_empty_image_filename($tbl, $image_filename, 'image_filename3'); |
---|
441 | |
---|
442 | // c_diary |
---|
443 | $tbl = 'c_diary'; |
---|
444 | _db_admin_empty_image_filename($tbl, $image_filename, 'image_filename_1'); |
---|
445 | _db_admin_empty_image_filename($tbl, $image_filename, 'image_filename_2'); |
---|
446 | _db_admin_empty_image_filename($tbl, $image_filename, 'image_filename_3'); |
---|
447 | |
---|
448 | // c_diary_comment |
---|
449 | $tbl = 'c_diary_comment'; |
---|
450 | _db_admin_empty_image_filename($tbl, $image_filename, 'image_filename_1'); |
---|
451 | _db_admin_empty_image_filename($tbl, $image_filename, 'image_filename_2'); |
---|
452 | _db_admin_empty_image_filename($tbl, $image_filename, 'image_filename_3'); |
---|
453 | |
---|
454 | // c_member |
---|
455 | $tbl = 'c_member'; |
---|
456 | _db_admin_empty_image_filename($tbl, $image_filename); |
---|
457 | _db_admin_empty_image_filename($tbl, $image_filename, 'image_filename_1'); |
---|
458 | _db_admin_empty_image_filename($tbl, $image_filename, 'image_filename_2'); |
---|
459 | _db_admin_empty_image_filename($tbl, $image_filename, 'image_filename_3'); |
---|
460 | } |
---|
461 | |
---|
462 | function _db_admin_empty_image_filename($tbl, $image_filename, $column = 'image_filename') |
---|
463 | { |
---|
464 | $data = array( |
---|
465 | db_escapeIdentifier($column) => '', |
---|
466 | ); |
---|
467 | $where = array( |
---|
468 | db_escapeIdentifier($column) => $image_filename, |
---|
469 | ); |
---|
470 | db_update(db_escapeIdentifier($tbl), $data, $where); |
---|
471 | } |
---|
472 | |
---|
473 | function db_admin_c_profile_name_exists($name) |
---|
474 | { |
---|
475 | $sql = 'SELECT c_profile_id FROM c_profile WHERE name = ?'; |
---|
476 | $params = array($name); |
---|
477 | return db_get_one($sql, $params); |
---|
478 | } |
---|
479 | |
---|
480 | function db_admin_update_is_login_rejected($c_member_id) |
---|
481 | { |
---|
482 | $sql = 'SELECT is_login_rejected FROM c_member WHERE c_member_id = ?'; |
---|
483 | $params = array(intval($c_member_id)); |
---|
484 | $is_login_rejected = db_get_one($sql, $params); |
---|
485 | if (is_null($is_login_rejected)) { |
---|
486 | return false; |
---|
487 | } |
---|
488 | |
---|
489 | $data = array('is_login_rejected' => !($is_login_rejected)); |
---|
490 | $where = array('c_member_id' => intval($c_member_id)); |
---|
491 | return db_update('c_member', $data, $where); |
---|
492 | } |
---|
493 | |
---|
494 | function db_admin_c_admin_user_list() |
---|
495 | { |
---|
496 | $sql = 'SELECT * FROM c_admin_user ORDER BY c_admin_user_id'; |
---|
497 | return db_get_all($sql); |
---|
498 | } |
---|
499 | |
---|
500 | function db_admin_exists_c_admin_username($username) |
---|
501 | { |
---|
502 | $sql = 'SELECT c_admin_user_id FROM c_admin_user WHERE username = ?'; |
---|
503 | $params = array(strval($username)); |
---|
504 | return (bool)db_get_one($sql, $params); |
---|
505 | } |
---|
506 | |
---|
507 | function db_admin_insert_c_admin_user($username, $password, $auth_type) |
---|
508 | { |
---|
509 | $data = array( |
---|
510 | 'username' => strval($username), |
---|
511 | 'password' => md5($password), |
---|
512 | 'auth_type' => strval($auth_type), |
---|
513 | ); |
---|
514 | return db_insert('c_admin_user', $data); |
---|
515 | } |
---|
516 | |
---|
517 | function db_admin_delete_c_admin_user($c_admin_user_id) |
---|
518 | { |
---|
519 | $sql = 'DELETE FROM c_admin_user WHERE c_admin_user_id = ?'; |
---|
520 | $params = array(intval($c_admin_user_id)); |
---|
521 | return db_query($sql, $params); |
---|
522 | } |
---|
523 | |
---|
524 | function db_admin_get_auth_type($c_admin_user_id) |
---|
525 | { |
---|
526 | $sql = 'SELECT auth_type FROM c_admin_user WHERE c_admin_user_id = ?'; |
---|
527 | $params = array(intval($c_admin_user_id)); |
---|
528 | return db_get_one($sql, $params); |
---|
529 | } |
---|
530 | |
---|
531 | /** |
---|
532 | * ユーザーIDリスト取得(絞り込み対応) |
---|
533 | */ |
---|
534 | function _db_admin_c_member_id_list($cond_list) |
---|
535 | { |
---|
536 | $sql = 'SELECT c_member_id'. |
---|
537 | ' FROM c_member'. |
---|
538 | ' WHERE 1'; |
---|
539 | |
---|
540 | //開始年 |
---|
541 | if (!empty($cond_list['s_year'])) { |
---|
542 | $sql .= ' AND birth_year >= ?'; |
---|
543 | $params[] = $cond_list['s_year']; |
---|
544 | } |
---|
545 | //終了年 |
---|
546 | if (!empty($cond_list['e_year'])) { |
---|
547 | $sql .= ' AND birth_year <= ?'; |
---|
548 | $params[] = $cond_list['e_year']; |
---|
549 | } |
---|
550 | $sql .= ' ORDER BY c_member_id'; |
---|
551 | |
---|
552 | $ids = db_get_col($sql, $params); |
---|
553 | |
---|
554 | //各プロフィールごとで絞り結果をマージする |
---|
555 | $_sql = 'SELECT name FROM c_profile WHERE (form_type = ? OR form_type = ?)'; |
---|
556 | $profile = db_get_col($_sql, array('select', 'radio')); |
---|
557 | |
---|
558 | if ( $profile ) { |
---|
559 | foreach ($profile as $value) { |
---|
560 | if (!empty($cond_list[$value])) { |
---|
561 | $sql = 'SELECT c_member_id FROM c_member_profile WHERE c_profile_option_id = ?'; |
---|
562 | $params = array($cond_list[$value]); |
---|
563 | $temp_ids = db_get_col($sql, $params); |
---|
564 | $ids = array_intersect($ids, $temp_ids); |
---|
565 | } |
---|
566 | } |
---|
567 | } |
---|
568 | |
---|
569 | return $ids; |
---|
570 | } |
---|
571 | |
---|
572 | /** |
---|
573 | * ユーザーリスト取得 |
---|
574 | * 誕生年+プロフィール(select,radioのみ) |
---|
575 | */ |
---|
576 | function _db_admin_c_member_list($page, $page_size, &$pager, $cond_list) |
---|
577 | { |
---|
578 | $ids = _db_admin_c_member_id_list($cond_list); |
---|
579 | $total_num = count($ids); |
---|
580 | $ids = array_slice($ids, ($page - 1) * $page_size, $page_size); |
---|
581 | |
---|
582 | $c_member_list = array(); |
---|
583 | foreach ($ids as $id) { |
---|
584 | $c_member_list[] = db_common_c_member4c_member_id($id, true, true, 'private'); |
---|
585 | } |
---|
586 | |
---|
587 | if ($total_num > 0) { |
---|
588 | $pager = admin_make_pager($page, $page_size, $total_num); |
---|
589 | } |
---|
590 | |
---|
591 | return $c_member_list; |
---|
592 | } |
---|
593 | |
---|
594 | function db_c_profile_option4c_profile_option_id($c_profile_option_id) |
---|
595 | { |
---|
596 | $sql = "SELECT * FROM c_profile_option WHERE c_profile_option_id = ? "; |
---|
597 | $params = array(intval($c_profile_option_id)); |
---|
598 | return db_get_row($sql, $params); |
---|
599 | } |
---|
600 | |
---|
601 | /** |
---|
602 | * メンバー絞込みパラメータ取得 |
---|
603 | */ |
---|
604 | function validate_cond($requests) |
---|
605 | { |
---|
606 | $cond_list = array(); |
---|
607 | //誕生年 |
---|
608 | if (!empty($requests['s_year'])) { |
---|
609 | $cond_list['s_year'] = intval($requests['s_year']); |
---|
610 | } |
---|
611 | if (!empty($requests['e_year'])) { |
---|
612 | $cond_list['e_year'] = intval($requests['e_year']); |
---|
613 | } |
---|
614 | //プロフィール |
---|
615 | $profile_list = db_common_c_profile_list(); |
---|
616 | |
---|
617 | foreach ($profile_list as $key => $value) { |
---|
618 | if (!empty($requests[$key])) { |
---|
619 | $cond_list[$key] = intval($requests[$key]); |
---|
620 | } |
---|
621 | } |
---|
622 | return $cond_list; |
---|
623 | } |
---|
624 | |
---|
625 | function do_admin_send_mail($c_member_id, $subject, $body) |
---|
626 | { |
---|
627 | $c_member = db_common_c_member4c_member_id($c_member_id, true); |
---|
628 | |
---|
629 | if ($c_member['secure']['pc_address']) { |
---|
630 | $send_address = $c_member['secure']['pc_address']; |
---|
631 | } else { |
---|
632 | $send_address = $c_member['secure']['ktai_address']; |
---|
633 | } |
---|
634 | |
---|
635 | if (OPENPNE_MAIL_QUEUE) { |
---|
636 | //メールキューに蓄積 |
---|
637 | put_mail_queue($send_address, $subject, $body); |
---|
638 | } else { |
---|
639 | t_send_email($send_address, $subject, $body); |
---|
640 | } |
---|
641 | } |
---|
642 | |
---|
643 | //メッセージ受信メール(メールキュー蓄積対応) |
---|
644 | function do_admin_send_message($c_member_id_from, $c_member_id_to, $subject, $body) |
---|
645 | { |
---|
646 | //メッセージ |
---|
647 | $c_message_id = _do_insert_c_message($c_member_id_from, $c_member_id_to, $subject, $body); |
---|
648 | |
---|
649 | do_admin_send_message_mail_send($c_member_id_to, $c_member_id_from); |
---|
650 | do_admin_send_message_mail_send_ktai($c_member_id_to, $c_member_id_from); |
---|
651 | |
---|
652 | return $c_message_id; |
---|
653 | } |
---|
654 | |
---|
655 | //メッセージ受信メール(メールキュー蓄積対応) |
---|
656 | function do_admin_send_message_mail_send($c_member_id_to, $c_member_id_from) |
---|
657 | { |
---|
658 | $c_member_to = db_common_c_member4c_member_id($c_member_id_to, true); |
---|
659 | $pc_address = $c_member_to['secure']['pc_address']; |
---|
660 | $is_receive_mail = $c_member_to['is_receive_mail']; |
---|
661 | |
---|
662 | $params = array( |
---|
663 | "c_member_to" => db_common_c_member4c_member_id($c_member_id_to), |
---|
664 | "c_member_from" => db_common_c_member4c_member_id($c_member_id_from), |
---|
665 | ); |
---|
666 | return admin_fetch_send_mail($pc_address, 'm_pc_message_zyushin', $params, $is_receive_mail); |
---|
667 | } |
---|
668 | |
---|
669 | //◆メッセージ受信メール(携帯) |
---|
670 | function do_admin_send_message_mail_send_ktai($c_member_id_to, $c_member_id_from) |
---|
671 | { |
---|
672 | $c_member_to = db_common_c_member4c_member_id($c_member_id_to, true); |
---|
673 | $ktai_address = $c_member_to['secure']['ktai_address']; |
---|
674 | $is_receive_ktai_mail = $c_member_to['is_receive_ktai_mail']; |
---|
675 | $p = array('kad' => t_encrypt(db_member_username4c_member_id($c_member_to['c_member_id'], true))); |
---|
676 | $login_url = openpne_gen_url('ktai', 'page_o_login', $p); |
---|
677 | |
---|
678 | $params = array( |
---|
679 | 'c_member_to' => db_common_c_member4c_member_id($c_member_id_to), |
---|
680 | 'c_member_from' => db_common_c_member4c_member_id($c_member_id_from), |
---|
681 | 'login_url' => $login_url, |
---|
682 | ); |
---|
683 | return admin_fetch_send_mail($ktai_address, 'm_ktai_message_zyushin', $params, $is_receive_ktai_mail); |
---|
684 | } |
---|
685 | |
---|
686 | function admin_fetch_send_mail($address, $tpl_name, $params = array(), $force = true, $from = '') |
---|
687 | { |
---|
688 | $tpl_name .= '.tpl'; |
---|
689 | if ($tpl = fetch_mail_m_tpl($tpl_name, $params)) { |
---|
690 | list($subject, $body) = $tpl; |
---|
691 | if ($from) { |
---|
692 | if (OPENPNE_MAIL_QUEUE) { |
---|
693 | //メールキューに蓄積 |
---|
694 | put_mail_queue($address, $subject, $body, $force, $from); |
---|
695 | } else { |
---|
696 | t_send_email($address, $subject, $body, $force, $from); |
---|
697 | } |
---|
698 | } else { |
---|
699 | if (OPENPNE_MAIL_QUEUE) { |
---|
700 | //メールキューに蓄積 |
---|
701 | put_mail_queue($address, $subject, $body, $force); |
---|
702 | } else { |
---|
703 | t_send_email($address, $subject, $body, $force); |
---|
704 | } |
---|
705 | } |
---|
706 | return true; |
---|
707 | } else { |
---|
708 | return false; |
---|
709 | } |
---|
710 | } |
---|
711 | |
---|
712 | function db_access_analysis_c_admin_user_id4username($username) |
---|
713 | { |
---|
714 | $sql = "SELECT c_admin_user_id FROM c_admin_user" . |
---|
715 | " WHERE username = ?"; |
---|
716 | $params = array($username); |
---|
717 | return db_get_one($sql,$params); |
---|
718 | } |
---|
719 | |
---|
720 | function p_access_analysis_month_access_analysis_month($ktai_flag) |
---|
721 | { |
---|
722 | $sql = "SELECT concat(left(r_datetime, 7), '-01') as ym, count(*) as count" . |
---|
723 | " FROM c_access_log " . |
---|
724 | " where ktai_flag = ?" . |
---|
725 | " group by ym"; |
---|
726 | $params = array(intval($ktai_flag)); |
---|
727 | $list = db_get_all($sql,$params); |
---|
728 | return $list; |
---|
729 | } |
---|
730 | |
---|
731 | |
---|
732 | function p_access_analysis_day_access_analysis_day($ym, $ktai_flag) |
---|
733 | { |
---|
734 | $sql = "SELECT left(r_datetime,10) as ymd , count(*) as count" . |
---|
735 | " FROM c_access_log " . |
---|
736 | " where left(r_datetime, 7) = ?" . |
---|
737 | " and ktai_flag = ? " . |
---|
738 | " group by ymd"; |
---|
739 | $params = array(intval(substr($ym,0,7)),intval($ktai_flag)); |
---|
740 | $list = db_get_all($sql,$params); |
---|
741 | |
---|
742 | $year = substr($ym, 0, 4); |
---|
743 | $month = substr($ym, 5,2); |
---|
744 | |
---|
745 | $return = array(); |
---|
746 | $days_num = date("t", mktime (0,0,0,$month,1,$year)); |
---|
747 | for($i=1 ; $i<=$days_num; $i++) { |
---|
748 | $date = substr($ym,0,8) . substr("00".$i, -2, 2); |
---|
749 | |
---|
750 | $count = 0; |
---|
751 | foreach($list as $value) { |
---|
752 | if ($value['ymd'] == $date) { |
---|
753 | $count = $value['count']; |
---|
754 | } |
---|
755 | } |
---|
756 | $return[] = array("ymd"=>$date, "count"=>$count); |
---|
757 | } |
---|
758 | return $return; |
---|
759 | } |
---|
760 | |
---|
761 | function get_page_name($ktai_flag, $orderby=1) |
---|
762 | { |
---|
763 | if ($orderby == 1) { |
---|
764 | $orderby_str = " order by page_name asc"; |
---|
765 | } elseif ($orderby == -1) { |
---|
766 | $orderby_str = " order by page_name desc"; |
---|
767 | } |
---|
768 | $sql = "select distinct page_name from c_access_log " . |
---|
769 | " where ktai_flag = ? " . |
---|
770 | $orderby_str; |
---|
771 | $params = array(intval($ktai_flag)); |
---|
772 | return db_get_col($sql,$params); |
---|
773 | } |
---|
774 | |
---|
775 | function p_access_analysis_page_access_analysis_page4ym($ymd, $month_flag, $ktai_flag, $orderby) |
---|
776 | { |
---|
777 | |
---|
778 | if ($orderby == 1) { |
---|
779 | $orderby_str = " order by page_name asc"; |
---|
780 | } elseif ($orderby == -1) { |
---|
781 | $orderby_str = " order by page_name desc"; |
---|
782 | } elseif ($orderby == 2) { |
---|
783 | $orderby_str = " order by count asc"; |
---|
784 | } elseif ($orderby == -2) { |
---|
785 | $orderby_str = " order by count desc"; |
---|
786 | } |
---|
787 | $sql = "select page_name , count(*) as count from c_access_log where ktai_flag = ? "; |
---|
788 | $params = array(intval($ktai_flag)); |
---|
789 | if ($month_flag) { |
---|
790 | $sql .= " and left(r_datetime, 7) = ? "; |
---|
791 | array_push($params,substr($ymd,0,7)); |
---|
792 | } else { |
---|
793 | $sql .= " and left(r_datetime,10) = ? "; |
---|
794 | array_push($params,$ymd); |
---|
795 | } |
---|
796 | $sql .= " group by page_name ". $orderby_str; |
---|
797 | $list = db_get_all($sql,$params); |
---|
798 | |
---|
799 | $sum = 0; |
---|
800 | $return = array(); |
---|
801 | if (abs($orderby) == 1) { |
---|
802 | $page_name = get_page_name($ktai_flag, $orderby); |
---|
803 | foreach($page_name as $name) { |
---|
804 | $count = 0; |
---|
805 | foreach($list as $value) { |
---|
806 | if ($value['page_name'] == $name) $count = $value['count']; |
---|
807 | } |
---|
808 | |
---|
809 | list($is_target_c_member_id,$is_target_c_commu_id,$is_target_c_topic_id,$is_target_c_diary_id,$is_c_member_id) = get_is_show($name); |
---|
810 | |
---|
811 | $return[] = array("page_name"=>$name, "count"=> $count, "is_target_c_member_id"=> $is_target_c_member_id, "is_target_c_commu_id"=> $is_target_c_commu_id, "is_target_c_topic_id"=> $is_target_c_topic_id, "is_target_c_diary_id"=> $is_target_c_diary_id, "is_c_member_id"=> $is_c_member_id); |
---|
812 | $sum += $count; |
---|
813 | } |
---|
814 | } elseif (abs($orderby) == 2) { |
---|
815 | $page_name = get_page_name($ktai_flag); |
---|
816 | |
---|
817 | $t_page_name = $page_name; |
---|
818 | |
---|
819 | //アクセスがゼロのページを取得する |
---|
820 | foreach($page_name as $key=>$name) { |
---|
821 | foreach($list as $value) { |
---|
822 | if ($value['page_name'] == $name) { |
---|
823 | unset($page_name[$key]);//$listに含まれるページネームを削除 |
---|
824 | } |
---|
825 | } |
---|
826 | } |
---|
827 | |
---|
828 | foreach($page_name as $key=>$name) { |
---|
829 | $page_name[$key] = array("page_name"=>$name, "count"=>0); |
---|
830 | } |
---|
831 | |
---|
832 | if ($orderby == 2) { |
---|
833 | $return = array_merge($page_name , $list); |
---|
834 | } elseif ($orderby == -2) { |
---|
835 | $return = array_merge($list, $page_name); |
---|
836 | } |
---|
837 | foreach($list as $value) { |
---|
838 | $sum += $value['count']; |
---|
839 | } |
---|
840 | |
---|
841 | foreach($return as $value) { |
---|
842 | |
---|
843 | list($is_target_c_member_id,$is_target_c_commu_id,$is_target_c_topic_id,$is_target_c_diary_id,$is_c_member_id) = get_is_show($value['page_name']); |
---|
844 | |
---|
845 | $value['is_target_c_member_id'] = $is_target_c_member_id; |
---|
846 | $value['is_target_c_commu_id'] = $is_target_c_commu_id; |
---|
847 | $value['is_target_c_topic_id'] = $is_target_c_topic_id; |
---|
848 | $value['is_target_c_diary_id'] = $is_target_c_diary_id; |
---|
849 | $value['is_c_member_id'] = $is_c_member_id; |
---|
850 | $t_return[] = $value; |
---|
851 | |
---|
852 | } |
---|
853 | |
---|
854 | $return = $t_return; |
---|
855 | |
---|
856 | } |
---|
857 | |
---|
858 | return array($return, $sum); |
---|
859 | } |
---|
860 | |
---|
861 | |
---|
862 | /* |
---|
863 | * target_commu |
---|
864 | * |
---|
865 | */ |
---|
866 | function p_access_analysis_target_commu_target_commu4ym_page_name |
---|
867 | ($ymd, $month_flag, $page_name, $ktai_flag, $page, $page_size, $orderby=1) |
---|
868 | { |
---|
869 | |
---|
870 | $start = ($page - 1) * $page_size; |
---|
871 | |
---|
872 | if ($orderby == 1) { |
---|
873 | $orderby_str = " order by target_c_commu_id asc"; |
---|
874 | } elseif ($orderby == -1) { |
---|
875 | $orderby_str = " order by target_c_commu_id desc"; |
---|
876 | } elseif ($orderby == 2) { |
---|
877 | $orderby_str = " order by count asc"; |
---|
878 | } elseif ($orderby == -2) { |
---|
879 | $orderby_str = " order by count desc"; |
---|
880 | } |
---|
881 | |
---|
882 | $sql = "select target_c_commu_id , count(*) as count from c_access_log where ktai_flag = ? "; |
---|
883 | $params = array(intval($ktai_flag)); |
---|
884 | if ($month_flag) { |
---|
885 | $sql .= " and left(r_datetime, 7) = ? "; |
---|
886 | array_push($params,substr($ymd,0,7)); |
---|
887 | } else { |
---|
888 | $sql .= " and left(r_datetime,10) = ? "; |
---|
889 | array_push($params,$ymd); |
---|
890 | } |
---|
891 | if ($page_name!="all") { |
---|
892 | $sql .= " and page_name = ? "; |
---|
893 | array_push($params,$page_name); |
---|
894 | } |
---|
895 | $sql .= " and target_c_commu_id <> 0 "; |
---|
896 | $sql .= " group by target_c_commu_id " .$orderby_str." limit $start, $page_size"; |
---|
897 | $list = db_get_all($sql,$params); |
---|
898 | |
---|
899 | $return = array(); |
---|
900 | $sum = 0; |
---|
901 | foreach($list as $key => $value) { |
---|
902 | if ($value['target_c_commu_id']) { |
---|
903 | $c_commu = _db_c_commu4c_commu_id($value['target_c_commu_id']); |
---|
904 | $return[] = array_merge($value, $c_commu); |
---|
905 | $sum += $value['count']; |
---|
906 | } |
---|
907 | } |
---|
908 | |
---|
909 | $sql = "select count(*) from c_access_log where ktai_flag = ? "; |
---|
910 | $params = array(intval($ktai_flag)); |
---|
911 | if ($month_flag) { |
---|
912 | $sql .= " and left(r_datetime, 7) = ? "; |
---|
913 | array_push($params,substr($ymd,0,7)); |
---|
914 | } else { |
---|
915 | $sql .= " and left(r_datetime,10) = ? "; |
---|
916 | array_push($params,$ymd); |
---|
917 | } |
---|
918 | if ($page_name!="all") { |
---|
919 | $sql .= " and page_name = ? "; |
---|
920 | array_push($params,$page_name); |
---|
921 | } |
---|
922 | $sql .= " and target_c_commu_id <> 0 "; |
---|
923 | $sql .= " group by target_c_commu_id "; |
---|
924 | $result = db_get_all($sql,$params); |
---|
925 | $total_num = count($result); |
---|
926 | |
---|
927 | if ($total_num != 0) { |
---|
928 | $total_page_num = ceil($total_num / $page_size); |
---|
929 | if ($page >= $total_page_num) { |
---|
930 | $next = false; |
---|
931 | }else{ |
---|
932 | $next = true; |
---|
933 | } |
---|
934 | if ($page <= 1) { |
---|
935 | $prev = false; |
---|
936 | }else{ |
---|
937 | $prev = true; |
---|
938 | } |
---|
939 | } |
---|
940 | $start_num = ($page - 1) * $page_size + 1 ; |
---|
941 | $end_num = ($page - 1) * $page_size + $page_size > $total_num ? $total_num : ($page - 1) * $page_size + $page_size ; |
---|
942 | |
---|
943 | return array($return, $sum, $prev, $next, $total_num, $start_num, $end_num); |
---|
944 | } |
---|
945 | |
---|
946 | function p_access_analysis_target_topic_target_topic4ym_page_name |
---|
947 | ($ymd, $month_flag, $page_name, $ktai_flag, $page, $page_size, $orderby=1) |
---|
948 | { |
---|
949 | $start = ($page - 1) * $page_size; |
---|
950 | |
---|
951 | if ($orderby == 1) { |
---|
952 | $orderby_str = " order by target_c_commu_topic_id asc"; |
---|
953 | } elseif ($orderby == -1) { |
---|
954 | $orderby_str = " order by target_c_commu_topic_id desc"; |
---|
955 | } elseif ($orderby == 2) { |
---|
956 | $orderby_str = " order by count asc"; |
---|
957 | } elseif ($orderby == -2) { |
---|
958 | $orderby_str = " order by count desc"; |
---|
959 | } |
---|
960 | $where =" where ktai_flag = ? "; |
---|
961 | $params = array(intval($ktai_flag)); |
---|
962 | if ($month_flag) { |
---|
963 | $where .= " and left(r_datetime, 7) = ? "; |
---|
964 | array_push($params,substr($ymd,0,7)); |
---|
965 | } else { |
---|
966 | $where .= " and left(r_datetime,10) = ? "; |
---|
967 | array_push($params,$ymd); |
---|
968 | } |
---|
969 | if ($page_name!="all") { |
---|
970 | $where .= " and page_name = ? "; |
---|
971 | array_push($params,$page_name); |
---|
972 | } |
---|
973 | $sql = "select target_c_commu_topic_id , count(*) as count from c_access_log "; |
---|
974 | $sql .= $where." group by target_c_commu_topic_id " .$orderby_str." limit $start, $page_size"; |
---|
975 | $list = db_get_all($sql,$params); |
---|
976 | $sql = "select count(*) from c_access_log "; |
---|
977 | $sql .= $where ." group by target_c_commu_topic_id "; |
---|
978 | $result = db_get_all($sql,$params); |
---|
979 | $total_num = count($result); |
---|
980 | |
---|
981 | $return = array(); |
---|
982 | $sum = 0; |
---|
983 | foreach ($list as $key => $value) { |
---|
984 | if ($value['target_c_commu_topic_id']) { |
---|
985 | $c_commu_topic = c_topic_detail_c_topic4c_commu_topic_id($value['target_c_commu_topic_id']); |
---|
986 | $c_commu_topic['topic_name'] = $c_commu_topic['name']; |
---|
987 | $c_commu = _db_c_commu4c_commu_id($c_commu_topic['c_commu_id']); |
---|
988 | $c_commu_topic['commu_name'] = $c_commu['name']; |
---|
989 | $return[] = array_merge($value, $c_commu_topic); |
---|
990 | $sum += $value['count']; |
---|
991 | } |
---|
992 | } |
---|
993 | |
---|
994 | if ($total_num != 0) { |
---|
995 | $total_page_num = ceil($total_num / $page_size); |
---|
996 | if ($page >= $total_page_num) { |
---|
997 | $next = false; |
---|
998 | } else { |
---|
999 | $next = true; |
---|
1000 | } |
---|
1001 | if ($page <= 1) { |
---|
1002 | $prev = false; |
---|
1003 | } else { |
---|
1004 | $prev = true; |
---|
1005 | } |
---|
1006 | } |
---|
1007 | $start_num = ($page - 1) * $page_size + 1 ; |
---|
1008 | $end_num = ($page - 1) * $page_size + $page_size > $total_num ? $total_num : ($page - 1) * $page_size + $page_size ; |
---|
1009 | |
---|
1010 | return array($return, $sum, $prev, $next, $total_num, $start_num, $end_num); |
---|
1011 | } |
---|
1012 | |
---|
1013 | function p_access_analysis_target_diary_target_diary4ym_page_name |
---|
1014 | ($ymd, $month_flag, $page_name, $ktai_flag, $page, $page_size, $orderby=1) |
---|
1015 | { |
---|
1016 | |
---|
1017 | $start = ($page - 1) * $page_size; |
---|
1018 | |
---|
1019 | if ($orderby == 1) { |
---|
1020 | $orderby_str = " order by target_c_diary_id asc"; |
---|
1021 | } elseif ($orderby == -1) { |
---|
1022 | $orderby_str = " order by target_c_diary_id desc"; |
---|
1023 | } elseif ($orderby == 2) { |
---|
1024 | $orderby_str = " order by count asc"; |
---|
1025 | } elseif ($orderby == -2) { |
---|
1026 | $orderby_str = " order by count desc"; |
---|
1027 | } |
---|
1028 | |
---|
1029 | $sql = "select target_c_diary_id , count(*) as count from c_access_log where ktai_flag = ? "; |
---|
1030 | $params = array(intval($ktai_flag)); |
---|
1031 | if ($month_flag) { |
---|
1032 | $sql .= " and left(r_datetime, 7) = ? "; |
---|
1033 | array_push($params,substr($ymd,0,7)); |
---|
1034 | } else { |
---|
1035 | $sql .= " and left(r_datetime,10) = ? "; |
---|
1036 | array_push($params,$ymd); |
---|
1037 | } |
---|
1038 | if ($page_name!="all") { |
---|
1039 | $sql .= " and page_name = ? "; |
---|
1040 | array_push($params,$page_name); |
---|
1041 | } |
---|
1042 | $sql .= " and target_c_diary_id <> 0 "; |
---|
1043 | $sql .= " group by target_c_diary_id " . $orderby_str. " limit $start, $page_size"; |
---|
1044 | $list = db_get_all($sql,$params); |
---|
1045 | |
---|
1046 | $return = array(); |
---|
1047 | $sum = 0; |
---|
1048 | foreach ($list as $key => $value) { |
---|
1049 | if ($value['target_c_diary_id']) { |
---|
1050 | $c_diary = db_diary_get_c_diary4id($value['target_c_diary_id']); |
---|
1051 | $c_member = db_common_c_member4c_member_id($c_diary['c_member_id']); |
---|
1052 | $c_diary['nickname'] = $c_member['nickname']; |
---|
1053 | $return[] = array_merge($value, $c_diary); |
---|
1054 | $sum += $value['count']; |
---|
1055 | } |
---|
1056 | } |
---|
1057 | |
---|
1058 | $sql = "select count(*) from c_access_log where ktai_flag = ? "; |
---|
1059 | $params = array(intval($ktai_flag)); |
---|
1060 | if ($month_flag) { |
---|
1061 | $sql .= " and left(r_datetime, 7) = ? "; |
---|
1062 | array_push($params,substr($ymd,0,7)); |
---|
1063 | } else { |
---|
1064 | $sql .= " and left(r_datetime,10) = ? "; |
---|
1065 | array_push($params,$ymd); |
---|
1066 | } |
---|
1067 | $sql .= " and target_c_diary_id <> 0 "; |
---|
1068 | $sql .= " group by target_c_diary_id "; |
---|
1069 | $result = db_get_all($sql,$params); |
---|
1070 | $total_num = count($result); |
---|
1071 | |
---|
1072 | if ($total_num != 0) { |
---|
1073 | $total_page_num = ceil($total_num / $page_size); |
---|
1074 | if ($page >= $total_page_num) { |
---|
1075 | $next = false; |
---|
1076 | }else{ |
---|
1077 | $next = true; |
---|
1078 | } |
---|
1079 | if ($page <= 1) { |
---|
1080 | $prev = false; |
---|
1081 | }else{ |
---|
1082 | $prev = true; |
---|
1083 | } |
---|
1084 | } |
---|
1085 | $start_num = ($page - 1) * $page_size + 1 ; |
---|
1086 | $end_num = ($page - 1) * $page_size + $page_size > $total_num ? $total_num : ($page - 1) * $page_size + $page_size ; |
---|
1087 | |
---|
1088 | return array($return, $sum, $prev, $next, $total_num, $start_num, $end_num); |
---|
1089 | } |
---|
1090 | |
---|
1091 | function p_access_analysis_member_access_member4ym_page_name |
---|
1092 | ($ymd, $month_flag, $page_name, $ktai_flag, $page, $page_size, $orderby=1) |
---|
1093 | { |
---|
1094 | $start = ($page - 1) * $page_size; |
---|
1095 | |
---|
1096 | if ($orderby == 1) { |
---|
1097 | $orderby_str = " order by target_c_member_id asc"; |
---|
1098 | } elseif ($orderby == -1) { |
---|
1099 | $orderby_str = " order by target_c_member_id desc"; |
---|
1100 | } elseif ($orderby == 2) { |
---|
1101 | $orderby_str = " order by count asc"; |
---|
1102 | } elseif ($orderby == -2) { |
---|
1103 | $orderby_str = " order by count desc"; |
---|
1104 | } |
---|
1105 | |
---|
1106 | $where =" where ktai_flag = ? "; |
---|
1107 | $params = array(intval($ktai_flag)); |
---|
1108 | if ($month_flag) { |
---|
1109 | $where .= " and left(r_datetime, 7) = ? "; |
---|
1110 | array_push($params,substr($ymd,0,7)); |
---|
1111 | } else { |
---|
1112 | $where .= " and left(r_datetime,10) = ? "; |
---|
1113 | array_push($params,$ymd); |
---|
1114 | } |
---|
1115 | if ($page_name!="all") { |
---|
1116 | $where .= " and page_name = ? "; |
---|
1117 | array_push($params,$page_name); |
---|
1118 | } |
---|
1119 | |
---|
1120 | $sql = "select c_member_id , count(*) as count from c_access_log"; |
---|
1121 | $sql .= $where." group by c_member_id $orderby_str limit $start, $page_size"; |
---|
1122 | $list = db_get_all($sql,$params); |
---|
1123 | $sql = "select count(*) from c_access_log "; |
---|
1124 | $sql .= $where ." group by c_member_id "; |
---|
1125 | $result = db_get_all($sql,$params); |
---|
1126 | $total_num = count($result); |
---|
1127 | |
---|
1128 | $return = array(); |
---|
1129 | $sum = 0; |
---|
1130 | foreach($list as $key => $value) { |
---|
1131 | if ($value['c_member_id']) { |
---|
1132 | $c_member = _db_c_member4c_member_id($value['c_member_id']); |
---|
1133 | $return[] = array_merge($value, $c_member); |
---|
1134 | $sum += $value['count']; |
---|
1135 | } |
---|
1136 | } |
---|
1137 | |
---|
1138 | |
---|
1139 | if ($total_num != 0) { |
---|
1140 | $total_page_num = ceil($total_num / $page_size); |
---|
1141 | if ($page >= $total_page_num) { |
---|
1142 | $next = false; |
---|
1143 | }else{ |
---|
1144 | $next = true; |
---|
1145 | } |
---|
1146 | if ($page <= 1) { |
---|
1147 | $prev = false; |
---|
1148 | }else{ |
---|
1149 | $prev = true; |
---|
1150 | } |
---|
1151 | } |
---|
1152 | $start_num = ($page - 1) * $page_size + 1 ; |
---|
1153 | $end_num = ($page - 1) * $page_size + $page_size > $total_num ? $total_num : ($page - 1) * $page_size + $page_size ; |
---|
1154 | return array($return, $sum, $prev, $next, $total_num, $start_num, $end_num); |
---|
1155 | } |
---|
1156 | |
---|
1157 | function p_access_analysis_target_member_access_member4ym_page_name |
---|
1158 | ($ymd, $month_flag, $page_name, $ktai_flag, $page, $page_size, $orderby=1) |
---|
1159 | { |
---|
1160 | $start = ($page - 1) * $page_size; |
---|
1161 | |
---|
1162 | if ($orderby == 1) { |
---|
1163 | $orderby_str = " order by target_c_member_id asc"; |
---|
1164 | } elseif ($orderby == -1) { |
---|
1165 | $orderby_str = " order by target_c_member_id desc"; |
---|
1166 | } elseif ($orderby == 2) { |
---|
1167 | $orderby_str = " order by count asc"; |
---|
1168 | } elseif ($orderby == -2) { |
---|
1169 | $orderby_str = " order by count desc"; |
---|
1170 | } |
---|
1171 | $where =" where ktai_flag = ? "; |
---|
1172 | $params = array(intval($ktai_flag)); |
---|
1173 | if ($month_flag) { |
---|
1174 | $where .= " and left(r_datetime, 7) = ? "; |
---|
1175 | array_push($params,substr($ymd,0,7)); |
---|
1176 | } else { |
---|
1177 | $where .= " and left(r_datetime,10) = ? "; |
---|
1178 | array_push($params,$ymd); |
---|
1179 | } |
---|
1180 | if ($page_name!="all") { |
---|
1181 | $where .= " and page_name = ? "; |
---|
1182 | array_push($params,$page_name); |
---|
1183 | } |
---|
1184 | $sql = "select target_c_member_id , count(*) as count from c_access_log "; |
---|
1185 | $sql .= $where; |
---|
1186 | $sql .= " AND target_c_member_id <> 0 "; |
---|
1187 | $sql .= " group by target_c_member_id " . $orderby_str. " limit $start, $page_size"; |
---|
1188 | |
---|
1189 | $list = db_get_all($sql,$params); |
---|
1190 | |
---|
1191 | $return = array(); |
---|
1192 | $sum = 0; |
---|
1193 | foreach($list as $key => $value) { |
---|
1194 | if ($value['target_c_member_id']) { |
---|
1195 | $c_member = db_common_c_member4c_member_id($value['target_c_member_id']); |
---|
1196 | $return[] = array_merge($value, $c_member); |
---|
1197 | $sum += $value['count']; |
---|
1198 | } |
---|
1199 | } |
---|
1200 | |
---|
1201 | $where =" where ktai_flag = ? "; |
---|
1202 | $params = array(intval($ktai_flag)); |
---|
1203 | if ($month_flag) { |
---|
1204 | $where .= " and left(r_datetime, 7) = ? "; |
---|
1205 | array_push($params,substr($ymd,0,7)); |
---|
1206 | } else { |
---|
1207 | $where .= " and left(r_datetime,10) = ? "; |
---|
1208 | array_push($params,$ymd); |
---|
1209 | } |
---|
1210 | if ($page_name != "all") { |
---|
1211 | $where .= " and page_name = ? "; |
---|
1212 | array_push($params,$page_name); |
---|
1213 | } |
---|
1214 | $sql = "select count(*) from c_access_log " ; |
---|
1215 | $sql .= $where; |
---|
1216 | $sql .= " AND target_c_member_id <> 0 "; |
---|
1217 | $sql .= " group by target_c_member_id "; |
---|
1218 | |
---|
1219 | $result = db_get_all($sql,$params); |
---|
1220 | $total_num = count($result); |
---|
1221 | |
---|
1222 | if ($total_num != 0) { |
---|
1223 | $total_page_num = ceil($total_num / $page_size); |
---|
1224 | if ($page >= $total_page_num) { |
---|
1225 | $next = false; |
---|
1226 | } else { |
---|
1227 | $next = true; |
---|
1228 | } |
---|
1229 | if ($page <= 1) { |
---|
1230 | $prev = false; |
---|
1231 | } else { |
---|
1232 | $prev = true; |
---|
1233 | } |
---|
1234 | } |
---|
1235 | $start_num = ($page - 1) * $page_size + 1 ; |
---|
1236 | $end_num = ($page - 1) * $page_size + $page_size > $total_num ? $total_num : ($page - 1) * $page_size + $page_size ; |
---|
1237 | |
---|
1238 | return array($return, $sum, $prev, $next, $total_num, $start_num, $end_num); |
---|
1239 | } |
---|
1240 | |
---|
1241 | function get_is_show($name) |
---|
1242 | { |
---|
1243 | //必要のない詳細ボタンを消す |
---|
1244 | switch($name) { |
---|
1245 | case "h_home": |
---|
1246 | $is_target_c_member_id = 0; |
---|
1247 | $is_target_c_commu_id = 0; |
---|
1248 | $is_target_c_topic_id = 0; |
---|
1249 | $is_target_c_diary_id = 0; |
---|
1250 | $is_c_member_id = 1; |
---|
1251 | break; |
---|
1252 | |
---|
1253 | case "fh_friend_list": |
---|
1254 | $is_target_c_member_id = 0; |
---|
1255 | $is_target_c_commu_id = 0; |
---|
1256 | $is_target_c_topic_id = 0; |
---|
1257 | $is_target_c_diary_id = 0; |
---|
1258 | $is_c_member_id = 1; |
---|
1259 | break; |
---|
1260 | |
---|
1261 | case "h_message": |
---|
1262 | $is_target_c_member_id = 0; |
---|
1263 | $is_target_c_commu_id = 0; |
---|
1264 | $is_target_c_topic_id = 0; |
---|
1265 | $is_target_c_diary_id = 0; |
---|
1266 | $is_c_member_id = 1; |
---|
1267 | break; |
---|
1268 | |
---|
1269 | case "fh_diary_list": |
---|
1270 | $is_target_c_member_id = 0; |
---|
1271 | $is_target_c_commu_id = 0; |
---|
1272 | $is_target_c_topic_id = 0; |
---|
1273 | $is_target_c_diary_id = 0; |
---|
1274 | $is_c_member_id = 1; |
---|
1275 | break; |
---|
1276 | |
---|
1277 | case "h_message_box": |
---|
1278 | $is_target_c_member_id = 0; |
---|
1279 | $is_target_c_commu_id = 0; |
---|
1280 | $is_target_c_topic_id = 0; |
---|
1281 | $is_target_c_diary_id = 0; |
---|
1282 | $is_c_member_id = 1; |
---|
1283 | break; |
---|
1284 | |
---|
1285 | case "f_home": |
---|
1286 | $is_target_c_member_id = 1; |
---|
1287 | $is_target_c_commu_id = 0; |
---|
1288 | $is_target_c_topic_id = 0; |
---|
1289 | $is_target_c_diary_id = 0; |
---|
1290 | $is_c_member_id = 1; |
---|
1291 | break; |
---|
1292 | |
---|
1293 | case "h_com_find_all": |
---|
1294 | $is_target_c_member_id = 0; |
---|
1295 | $is_target_c_commu_id = 0; |
---|
1296 | $is_target_c_topic_id = 0; |
---|
1297 | $is_target_c_diary_id = 0; |
---|
1298 | $is_c_member_id = 1; |
---|
1299 | break; |
---|
1300 | |
---|
1301 | case "reply_message": |
---|
1302 | $is_target_c_member_id = 0; |
---|
1303 | $is_target_c_commu_id = 0; |
---|
1304 | $is_target_c_topic_id = 0; |
---|
1305 | $is_target_c_diary_id = 0; |
---|
1306 | $is_c_member_id = 1; |
---|
1307 | break; |
---|
1308 | |
---|
1309 | case "f_message_send": |
---|
1310 | $is_target_c_member_id = 0; |
---|
1311 | $is_target_c_commu_id = 0; |
---|
1312 | $is_target_c_topic_id = 0; |
---|
1313 | $is_target_c_diary_id = 0; |
---|
1314 | $is_c_member_id = 1; |
---|
1315 | break; |
---|
1316 | |
---|
1317 | case "f_message_send": |
---|
1318 | $is_target_c_member_id = 0; |
---|
1319 | $is_target_c_commu_id = 0; |
---|
1320 | $is_target_c_topic_id = 0; |
---|
1321 | $is_target_c_diary_id = 0; |
---|
1322 | $is_c_member_id = 1; |
---|
1323 | break; |
---|
1324 | |
---|
1325 | case "h_diary_list_all": |
---|
1326 | $is_target_c_member_id = 0; |
---|
1327 | $is_target_c_commu_id = 0; |
---|
1328 | $is_target_c_topic_id = 0; |
---|
1329 | $is_target_c_diary_id = 0; |
---|
1330 | $is_c_member_id = 1; |
---|
1331 | break; |
---|
1332 | |
---|
1333 | case "h_search": |
---|
1334 | $is_target_c_member_id = 0; |
---|
1335 | $is_target_c_commu_id = 0; |
---|
1336 | $is_target_c_topic_id = 0; |
---|
1337 | $is_target_c_diary_id = 0; |
---|
1338 | $is_c_member_id = 1; |
---|
1339 | break; |
---|
1340 | |
---|
1341 | case "f_message_send_confirm": |
---|
1342 | $is_target_c_member_id = 1; |
---|
1343 | $is_target_c_commu_id = 0; |
---|
1344 | $is_target_c_topic_id = 0; |
---|
1345 | $is_target_c_diary_id = 0; |
---|
1346 | $is_c_member_id = 1; |
---|
1347 | break; |
---|
1348 | |
---|
1349 | default: |
---|
1350 | $is_target_c_member_id = 1; |
---|
1351 | $is_target_c_commu_id = 1; |
---|
1352 | $is_target_c_topic_id = 1; |
---|
1353 | $is_target_c_diary_id = 1; |
---|
1354 | $is_c_member_id = 1; |
---|
1355 | break; |
---|
1356 | } |
---|
1357 | |
---|
1358 | return array($is_target_c_member_id,$is_target_c_commu_id,$is_target_c_topic_id,$is_target_c_diary_id,$is_c_member_id); |
---|
1359 | |
---|
1360 | } |
---|
1361 | |
---|
1362 | /** |
---|
1363 | |
---|
1364 | カラムごとに条件を指定して絞ったメンバーの一覧を返す |
---|
1365 | |
---|
1366 | [引数] |
---|
1367 | 適時追加していく |
---|
1368 | |
---|
1369 | $s_access_date 最終ログイン時刻 開始年月日 |
---|
1370 | $e_access_date 最終ログイン時刻 終了年月日 |
---|
1371 | |
---|
1372 | [返り値] |
---|
1373 | c_member_list |
---|
1374 | |
---|
1375 | */ |
---|
1376 | function p_member_edit_c_member_list($page_size, $page,$s_access_date='', $e_access_date='') |
---|
1377 | { |
---|
1378 | |
---|
1379 | $page = intval($page); |
---|
1380 | $page_size = intval($page_size); |
---|
1381 | |
---|
1382 | $limit = ""; |
---|
1383 | |
---|
1384 | //page_sizeが0の時は全て表示(pagerなし) |
---|
1385 | if ($page_size != 0) { |
---|
1386 | $limit = " LIMIT ".($page_size*($page-1)).",$page_size"; |
---|
1387 | } |
---|
1388 | $where = " WHERE 1 "; |
---|
1389 | |
---|
1390 | //指定された条件で絞っていく |
---|
1391 | if ($s_access_date != "") { |
---|
1392 | $where = $where . " and access_date >= ?"; |
---|
1393 | $params = array($s_access_date); |
---|
1394 | } |
---|
1395 | |
---|
1396 | if ($e_access_date != "") { |
---|
1397 | $where = $where . " and access_date < ?"; |
---|
1398 | $params = array($e_access_date); |
---|
1399 | } |
---|
1400 | |
---|
1401 | $select = "SELECT * FROM c_member"; |
---|
1402 | $order = " order by c_member_id"; |
---|
1403 | |
---|
1404 | $sql = $select . $where . $order . $limit; |
---|
1405 | $list = db_get_all_limit($sql, 0, $limit, $params); |
---|
1406 | |
---|
1407 | $sql = "select count(*) from c_member".$where; |
---|
1408 | |
---|
1409 | $total_num = db_get_one($sql, $params); |
---|
1410 | |
---|
1411 | if ($total_num != 0 && $page_size != 0) { |
---|
1412 | $total_page_num = ceil($total_num / $page_size); |
---|
1413 | if ($page >= $total_page_num) { |
---|
1414 | $next = false; |
---|
1415 | } else { |
---|
1416 | $next = true; |
---|
1417 | } |
---|
1418 | if ($page <= 1) { |
---|
1419 | $prev = false; |
---|
1420 | } else { |
---|
1421 | $prev = true; |
---|
1422 | } |
---|
1423 | } |
---|
1424 | |
---|
1425 | return array($list , $prev , $next, $total_num); |
---|
1426 | } |
---|
1427 | |
---|
1428 | function _db_c_member4c_member_id($c_member_id) |
---|
1429 | { |
---|
1430 | $sql = "SELECT * FROM c_member WHERE c_member_id= ? "; |
---|
1431 | $params = array(intval($c_member_id)); |
---|
1432 | return db_get_row($sql,$params); |
---|
1433 | } |
---|
1434 | |
---|
1435 | /** |
---|
1436 | * 男と女の人数を取得 |
---|
1437 | */ |
---|
1438 | function get_analysis_sex() |
---|
1439 | { |
---|
1440 | $sql = "select count(*) from c_member where sex = '男'"; |
---|
1441 | $analysis_sex['male'] = get_one4db($sql); |
---|
1442 | $sql = "select count(*) from c_member where sex = '女'"; |
---|
1443 | $analysis_sex['female'] = get_one4db($sql); |
---|
1444 | |
---|
1445 | return $analysis_sex; |
---|
1446 | |
---|
1447 | } |
---|
1448 | |
---|
1449 | /** |
---|
1450 | * 世代の人数を取得 |
---|
1451 | */ |
---|
1452 | function get_analysis_generation() |
---|
1453 | { |
---|
1454 | $analysis_generation = array( |
---|
1455 | '0~9' => 0, |
---|
1456 | '10~19' => 0, |
---|
1457 | '20~29' => 0, |
---|
1458 | '30~39' => 0, |
---|
1459 | '40~49' => 0, |
---|
1460 | '50~59' => 0, |
---|
1461 | '60~69' => 0, |
---|
1462 | '70~79' => 0, |
---|
1463 | '80~' =>0 |
---|
1464 | ); |
---|
1465 | |
---|
1466 | $sql = "select ((year(curdate()) - birth_year)- (RIGHT(CURDATE(),5)<concat(birth_month,'-',birth_day))) as age from c_member;"; |
---|
1467 | $lst = db_get_all($sql); |
---|
1468 | |
---|
1469 | $temp = array_keys($analysis_generation); |
---|
1470 | foreach($lst as $value) { |
---|
1471 | $key = (int)($value['age'] / 10); |
---|
1472 | if ($key > count($analysis_generation)-1) { |
---|
1473 | $analysis_generation[$temp[count($analysis_generation)-1]]++; |
---|
1474 | } else { |
---|
1475 | $analysis_generation[$temp[$key]]++; |
---|
1476 | } |
---|
1477 | } |
---|
1478 | |
---|
1479 | return $analysis_generation; |
---|
1480 | |
---|
1481 | } |
---|
1482 | |
---|
1483 | /** |
---|
1484 | * 地域別の人数を取得 |
---|
1485 | */ |
---|
1486 | function get_analysis_region() |
---|
1487 | { |
---|
1488 | $pref = p_regist_prof_c_profile_pref_list4null(); |
---|
1489 | $sql = "select pre_addr_c_profile_pref_id as pref_id from c_member"; |
---|
1490 | $lst = get_array_list4db($sql); |
---|
1491 | |
---|
1492 | foreach($pref as $value) { |
---|
1493 | $analysis_region[$value] = 0; |
---|
1494 | } |
---|
1495 | |
---|
1496 | foreach ($lst as $value) { |
---|
1497 | if ($value['pref_id'] == 0) { |
---|
1498 | $analysis_region['その他']++; |
---|
1499 | } else { |
---|
1500 | $analysis_region[$pref[$value['pref_id']]]++; |
---|
1501 | } |
---|
1502 | } |
---|
1503 | |
---|
1504 | return $analysis_region; |
---|
1505 | |
---|
1506 | } |
---|
1507 | |
---|
1508 | function get_analysis_date_month($year = "", $month = "") |
---|
1509 | { |
---|
1510 | $sql = "select date_format(r_date,'%Y-%m') from c_member order by r_date"; |
---|
1511 | $start_date = db_get_one($sql); |
---|
1512 | |
---|
1513 | $i = 0; |
---|
1514 | list($y, $m) = split("-",$start_date); |
---|
1515 | do{ |
---|
1516 | $date = date("Y-m", mktime (0,0,0,$m+$i++,1,$y)); |
---|
1517 | $analysis_date_month[$date] = 0; |
---|
1518 | }while($date < date("Y-m")); |
---|
1519 | |
---|
1520 | $sql = "select date_format(r_date,'%Y-%m') as d from c_member"; |
---|
1521 | $lst = db_get_all($sql); |
---|
1522 | foreach ($lst as $value) { |
---|
1523 | $analysis_date_month[$value['d']]++; |
---|
1524 | } |
---|
1525 | return $analysis_date_month; |
---|
1526 | } |
---|
1527 | |
---|
1528 | function get_analysis_date_day_d($date="") |
---|
1529 | { |
---|
1530 | if ($date == "") { |
---|
1531 | $date = date("Y-m-d"); |
---|
1532 | } |
---|
1533 | return $date; |
---|
1534 | } |
---|
1535 | |
---|
1536 | |
---|
1537 | function get_analysis_date_day($date="") |
---|
1538 | { |
---|
1539 | if ($date == "") { |
---|
1540 | $date = date("Y-m"); |
---|
1541 | } |
---|
1542 | //一ヶ月の日数 |
---|
1543 | $day_num = date("t"); |
---|
1544 | |
---|
1545 | for($i=1 ; $i<=$day_num; $i++) { |
---|
1546 | //一桁の数を二桁にする |
---|
1547 | if ($i < 10) { |
---|
1548 | $i = "0".$i; |
---|
1549 | } |
---|
1550 | $analysis_date_day[$i] = 0; |
---|
1551 | } |
---|
1552 | |
---|
1553 | |
---|
1554 | $sql = "select date_format(r_date,'%d') as d from c_member where date_format(r_date,'%Y-%m') = ?"; |
---|
1555 | $params = array($date); |
---|
1556 | $lst = db_get_all($sql,$params); |
---|
1557 | |
---|
1558 | foreach($lst as $value) { |
---|
1559 | $analysis_date_day[$value['d']]++; |
---|
1560 | } |
---|
1561 | |
---|
1562 | return $analysis_date_day; |
---|
1563 | } |
---|
1564 | |
---|
1565 | function p_access_analysis_select_profile_list() |
---|
1566 | { |
---|
1567 | $sql = "SELECT * " . |
---|
1568 | " FROM c_profile " . |
---|
1569 | " where form_type = 'select' "; |
---|
1570 | |
---|
1571 | $list = db_get_all($sql); |
---|
1572 | return $list; |
---|
1573 | } |
---|
1574 | |
---|
1575 | /** |
---|
1576 | * 指定されたIDのプロフィールの人数別一覧を作成 |
---|
1577 | */ |
---|
1578 | function get_analysis_profile($c_profile_id) |
---|
1579 | { |
---|
1580 | $sql = "select count(*) as count,value,c_profile.caption from c_member_profile " . |
---|
1581 | " LEFT JOIN c_profile ON c_profile.c_profile_id = c_member_profile.c_profile_id " . |
---|
1582 | " WHERE c_member_profile.c_profile_id = ? GROUP BY value "; |
---|
1583 | $params = array(intval($c_profile_id)); |
---|
1584 | $analysis_profile = db_get_all($sql,$params); |
---|
1585 | |
---|
1586 | return $analysis_profile; |
---|
1587 | } |
---|
1588 | |
---|
1589 | function get_analysis_count_profile_all($c_profile_id) |
---|
1590 | { |
---|
1591 | $sql = "select count(*) as count from c_member_profile " . |
---|
1592 | " WHERE c_profile_id = ? "; |
---|
1593 | $params = array(intval($c_profile_id)); |
---|
1594 | $analysis_profile = db_get_one($sql,$params); |
---|
1595 | |
---|
1596 | return $analysis_profile; |
---|
1597 | } |
---|
1598 | |
---|
1599 | function analysis_profile4c_profile_id($c_profile_id) |
---|
1600 | { |
---|
1601 | $sql = "SELECT * FROM c_profile" . |
---|
1602 | " WHERE c_profile_id = ? "; |
---|
1603 | $params = array(intval($c_profile_id)); |
---|
1604 | $profile = db_get_row($sql,$params); |
---|
1605 | |
---|
1606 | return $profile; |
---|
1607 | } |
---|
1608 | function monitor_diary_list($keyword,$page_size,$page) |
---|
1609 | { |
---|
1610 | |
---|
1611 | $page = intval($page); |
---|
1612 | $page_size = intval($page_size); |
---|
1613 | |
---|
1614 | $where = " where 1 "; |
---|
1615 | |
---|
1616 | if ($keyword) { |
---|
1617 | //全角空白を半角に統一 |
---|
1618 | $keyword = str_replace(" ", " ", $keyword); |
---|
1619 | $keyword_list = explode(" ", $keyword); |
---|
1620 | |
---|
1621 | for($i=0;$i < count($keyword_list);$i++) { |
---|
1622 | $keyword = check_search_word( $keyword_list[$i] ); |
---|
1623 | |
---|
1624 | $where .= " and (c_diary.subject like ? "; |
---|
1625 | $where .= " or c_diary.body like ? ) "; |
---|
1626 | $params[]="%$keyword%"; |
---|
1627 | $params[]="%$keyword%"; |
---|
1628 | } |
---|
1629 | } |
---|
1630 | |
---|
1631 | $select = " select c_diary.*"; |
---|
1632 | $from = " FROM c_diary"; |
---|
1633 | $order = " ORDER BY r_datetime desc"; |
---|
1634 | |
---|
1635 | $sql = $select . $from . $where . $order; |
---|
1636 | $list = db_get_all_limit($sql,($page-1)*$page_size,$page_size,$params); |
---|
1637 | foreach ($list as $key => $value) { |
---|
1638 | $list[$key]['c_member'] = db_common_c_member_with_profile($value['c_member_id']); |
---|
1639 | $list[$key]['count_comments'] = db_diary_count_c_diary_comment4c_diary_id($value['c_diary_id']); |
---|
1640 | } |
---|
1641 | |
---|
1642 | $sql = |
---|
1643 | "SELECT count(*) " |
---|
1644 | . $from |
---|
1645 | . $where ; |
---|
1646 | $total_num = db_get_one($sql,$params); |
---|
1647 | |
---|
1648 | $total_page_num = ceil($total_num / $page_size); |
---|
1649 | $next = ($page < $total_page_num); |
---|
1650 | $prev = ($page > 1); |
---|
1651 | |
---|
1652 | return array($list , $prev , $next, $total_num,$total_page_num); |
---|
1653 | } |
---|
1654 | |
---|
1655 | function monitor_diary_comment_list($keyword,$page_size,$page) |
---|
1656 | { |
---|
1657 | |
---|
1658 | $page = intval($page); |
---|
1659 | $page_size = intval($page_size); |
---|
1660 | |
---|
1661 | $where = " where 1 "; |
---|
1662 | |
---|
1663 | if ($keyword) { |
---|
1664 | //全角空白を半角に統一 |
---|
1665 | $keyword = str_replace(" ", " ", $keyword); |
---|
1666 | $keyword_list = explode(" ", $keyword); |
---|
1667 | |
---|
1668 | for($i=0;$i < count($keyword_list);$i++) { |
---|
1669 | $keyword = check_search_word( $keyword_list[$i] ); |
---|
1670 | |
---|
1671 | $where .= " and (c_diary_comment.body like ? ) "; |
---|
1672 | $params[]="%$keyword%"; |
---|
1673 | } |
---|
1674 | } |
---|
1675 | |
---|
1676 | $select = " select c_diary_comment.*,c_diary.subject"; |
---|
1677 | $from = " FROM c_diary_comment" |
---|
1678 | ." LEFT JOIN c_diary ON c_diary.c_diary_id = c_diary_comment.c_diary_id "; |
---|
1679 | $order = " ORDER BY r_datetime desc"; |
---|
1680 | |
---|
1681 | $sql = $select . $from . $where . $order; |
---|
1682 | $list = db_get_all_limit($sql,($page-1)*$page_size,$page_size,$params); |
---|
1683 | foreach ($list as $key => $value) { |
---|
1684 | $list[$key]['c_member'] = db_common_c_member_with_profile($value['c_member_id']); |
---|
1685 | $list[$key]['count_comments'] = db_diary_count_c_diary_comment4c_diary_id($value['c_diary_id']); |
---|
1686 | } |
---|
1687 | |
---|
1688 | $sql = |
---|
1689 | "SELECT count(*) " |
---|
1690 | . $from |
---|
1691 | . $where ; |
---|
1692 | $total_num = db_get_one($sql,$params); |
---|
1693 | |
---|
1694 | $total_page_num = ceil($total_num / $page_size); |
---|
1695 | $next = ($page < $total_page_num); |
---|
1696 | $prev = ($page > 1); |
---|
1697 | |
---|
1698 | return array($list , $prev , $next, $total_num,$total_page_num); |
---|
1699 | } |
---|
1700 | |
---|
1701 | |
---|
1702 | function monitor_topic_comment_list($keyword,$page_size,$page) |
---|
1703 | { |
---|
1704 | |
---|
1705 | $page = intval($page); |
---|
1706 | $page_size = intval($page_size); |
---|
1707 | |
---|
1708 | $where = " where 1 "; |
---|
1709 | |
---|
1710 | if ($keyword) { |
---|
1711 | $keyword = str_replace("?@", " ", $keyword); |
---|
1712 | $keyword_list = explode(" ", $keyword); |
---|
1713 | |
---|
1714 | for($i=0;$i < count($keyword_list);$i++) { |
---|
1715 | $keyword = check_search_word( $keyword_list[$i] ); |
---|
1716 | |
---|
1717 | $where .= " AND (ctc.body like ? )"; |
---|
1718 | $params[]="%$keyword%"; |
---|
1719 | } |
---|
1720 | } |
---|
1721 | |
---|
1722 | $select = " SELECT ctc.*,ct.name as topic_name,c.name as commu_name,m.nickname"; |
---|
1723 | $from = " FROM c_commu_topic_comment as ctc" |
---|
1724 | ." LEFT JOIN c_member as m ON ctc.c_member_id = m.c_member_id " |
---|
1725 | ." LEFT JOIN c_commu_topic as ct ON ct.c_commu_topic_id = ctc.c_commu_topic_id " |
---|
1726 | ." LEFT JOIN c_commu as c ON c.c_commu_id = ct.c_commu_id "; |
---|
1727 | $order = " ORDER BY r_datetime desc"; |
---|
1728 | |
---|
1729 | $sql = $select . $from . $where . $order; |
---|
1730 | |
---|
1731 | $list = db_get_all_limit($sql,($page-1)*$page_size,$page_size,$params); |
---|
1732 | |
---|
1733 | foreach ($list as $key => $value) { |
---|
1734 | $list[$key]['count_comments'] = _db_count_c_commu_topic_comments4c_commu_topic_id($value['c_commu_topic_id']); |
---|
1735 | } |
---|
1736 | |
---|
1737 | $sql = |
---|
1738 | "SELECT count(*) " |
---|
1739 | . $from |
---|
1740 | . $where ; |
---|
1741 | $total_num = db_get_one($sql,$params); |
---|
1742 | |
---|
1743 | $total_page_num = ceil($total_num / $page_size); |
---|
1744 | $next = ($page < $total_page_num); |
---|
1745 | $prev = ($page > 1); |
---|
1746 | |
---|
1747 | return array($list , $prev , $next, $total_num,$total_page_num); |
---|
1748 | } |
---|
1749 | |
---|
1750 | function monitor_topic_list($keyword,$page_size,$page) |
---|
1751 | { |
---|
1752 | $page = intval($page); |
---|
1753 | $page_size = intval($page_size); |
---|
1754 | |
---|
1755 | $where = " where 1 "; |
---|
1756 | |
---|
1757 | if ($keyword) { |
---|
1758 | $keyword = str_replace("?@", " ", $keyword); |
---|
1759 | $keyword_list = explode(" ", $keyword); |
---|
1760 | |
---|
1761 | for($i=0;$i < count($keyword_list);$i++) { |
---|
1762 | $keyword = check_search_word( $keyword_list[$i] ); |
---|
1763 | |
---|
1764 | $where .= " AND (ctc.body like ? "; |
---|
1765 | $where .= " OR ct.name like ? "; |
---|
1766 | $where .= " OR c.name like ? ) "; |
---|
1767 | $params[]="%$keyword%"; |
---|
1768 | $params[]="%$keyword%"; |
---|
1769 | $params[]="%$keyword%"; |
---|
1770 | } |
---|
1771 | } |
---|
1772 | |
---|
1773 | $select = " SELECT ct.*,ct.name as topic_name,c.name as commu_name,m.nickname,ctc.body as body"; |
---|
1774 | $from = " FROM c_commu_topic as ct" |
---|
1775 | ." LEFT JOIN c_member as m ON ct.c_member_id = m.c_member_id " |
---|
1776 | ." LEFT JOIN c_commu as c ON c.c_commu_id = ct.c_commu_id " |
---|
1777 | ." LEFT JOIN c_commu_topic_comment as ctc ON (ctc.c_commu_topic_id = ct.c_commu_topic_id AND ctc.number = 0)"; |
---|
1778 | $order = " ORDER BY r_datetime desc"; |
---|
1779 | |
---|
1780 | $sql = $select . $from . $where . $order; |
---|
1781 | |
---|
1782 | $list = db_get_all_limit($sql,($page-1)*$page_size,$page_size,$params); |
---|
1783 | |
---|
1784 | foreach ($list as $key => $value) { |
---|
1785 | $list[$key]['count_comments'] = _db_count_c_commu_topic_comments4c_commu_topic_id($value['c_commu_topic_id']); |
---|
1786 | } |
---|
1787 | |
---|
1788 | $sql = |
---|
1789 | "SELECT count(*) " |
---|
1790 | . $from |
---|
1791 | . $where ; |
---|
1792 | $total_num = db_get_one($sql,$params); |
---|
1793 | |
---|
1794 | $total_page_num = ceil($total_num / $page_size); |
---|
1795 | $next = ($page < $total_page_num); |
---|
1796 | $prev = ($page > 1); |
---|
1797 | |
---|
1798 | return array($list , $prev , $next, $total_num,$total_page_num); |
---|
1799 | } |
---|
1800 | |
---|
1801 | function _db_count_c_commu_topic_comments4c_commu_topic_id($c_commu_topic_id) |
---|
1802 | { |
---|
1803 | $sql = "SELECT count(*) FROM c_commu_topic_comment" . |
---|
1804 | " WHERE c_commu_topic_id = ? AND number > 0"; |
---|
1805 | $params = array($c_commu_topic_id); |
---|
1806 | return db_get_one($sql, $params); |
---|
1807 | } |
---|
1808 | //フリーページを追加 |
---|
1809 | function db_admin_insert_c_free_page($body) |
---|
1810 | { |
---|
1811 | $data = array( |
---|
1812 | 'body' => strval($body), |
---|
1813 | ); |
---|
1814 | return db_insert('c_free_page', $data); |
---|
1815 | } |
---|
1816 | |
---|
1817 | //フリーページを編集 |
---|
1818 | function db_admin_update_c_free_page($c_free_page_id, $body) |
---|
1819 | { |
---|
1820 | $data = array( |
---|
1821 | 'body' => strval($body), |
---|
1822 | ); |
---|
1823 | $where = array('c_free_page_id' => intval($c_free_page_id)); |
---|
1824 | return db_update('c_free_page', $data, $where); |
---|
1825 | } |
---|
1826 | |
---|
1827 | //フリーページを削除 |
---|
1828 | function db_admin_delete_c_free_page($c_free_page_id) |
---|
1829 | { |
---|
1830 | $sql = "DELETE FROM c_free_page WHERE c_free_page_id = ?"; |
---|
1831 | $params = array(intval($c_free_page_id)); |
---|
1832 | return db_query($sql, $params); |
---|
1833 | } |
---|
1834 | |
---|
1835 | |
---|
1836 | //フリーページを全て取得(ページャー付き) |
---|
1837 | function db_admin_get_c_free_page_all($page, $page_size, &$pager) |
---|
1838 | { |
---|
1839 | $sql = 'SELECT * FROM c_free_page ORDER BY c_free_page_id DESC'; |
---|
1840 | |
---|
1841 | $list = db_get_all_page($sql, $page, $page_size, $params); |
---|
1842 | |
---|
1843 | $sql = 'SELECT count(*) FROM c_free_page'; |
---|
1844 | $total_num = db_get_one($sql, $params); |
---|
1845 | $pager = admin_make_pager($page, $page_size, $total_num); |
---|
1846 | |
---|
1847 | return $list; |
---|
1848 | } |
---|
1849 | |
---|
1850 | //フリーページを一つ取得 |
---|
1851 | function db_admin_get_c_free_page_one($c_free_page_id) |
---|
1852 | { |
---|
1853 | $sql = 'SELECT * FROM c_free_page WHERE c_free_page_id = ?'; |
---|
1854 | $params = array(intval($c_free_page_id)); |
---|
1855 | |
---|
1856 | return db_get_row($sql, $params); |
---|
1857 | } |
---|
1858 | |
---|
1859 | ?> |
---|