tblmember.sql.php 2.44 KB
Newer Older
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
<?php

$sql["count_tblmember"] = "
SELECT 
    FOUND_ROWS() AS total
";
$sql["get_tblmember"] = "
SELECT  SQL_CALC_FOUND_ROWS
    a.member_id AS `id`,
    a.member_name AS `name`,
    a.member_birthdate AS `birthdate`,
    a.member_province_id AS `province_id`,
    a.member_gender AS `gender`,
    a.insert_timestamp AS `register_time`,
    a.insert_timestamp AS `insert_timestamp`,
    a.insert_user_id AS `insert_user_id`,
    a.update_timestamp AS `update_timestamp`,
    a.update_user_id AS `update_user_id`,
    c.province_name as `province`,
    IFNULL(CONCAT(b.`user_real_name`,', ',DATE_FORMAT(a.update_timestamp,'%d %b %Y %H:%i')),CONCAT(b.`user_real_name`,', ',DATE_FORMAT(a.insert_timestamp,'%d %b %Y %H:%i'))) AS last_modified
FROM tbl_member a
LEFT JOIN gtfw_user b ON b.`user_id` = a.`insert_user_id`
LEFT JOIN tbl_province c ON c.`province_id` = a.`member_province_id`
WHERE
    1 = 1
    --search--
--limit--    
";
$sql['list_province'] = "
select 
province_id as `id`,
province_name as `name`
from tbl_province where province_status = 1
";
$sql["get_detail_tblmember"] = "
SELECT 
    a.member_id AS `id`,
    a.member_name AS `name`,
    a.member_birthdate AS `birthdate`,
    a.member_province_id AS `province_id`,
    a.member_gender AS `gender`,
    a.member_latitude as `latitude`,
    a.member_longitude as `longitude`,
    a.insert_timestamp AS `insert_timestamp`,
    a.insert_user_id AS `insert_user_id`,
    a.update_timestamp AS `update_timestamp`,
    a.update_user_id AS `update_user_id`,
    CONCAT(b.`user_real_name`,', ',DATE_FORMAT(a.update_timestamp,'%%d %%b %%Y %%H:%%i')) AS last_update,
    CONCAT(b.`user_real_name`,', ',DATE_FORMAT(a.insert_timestamp,'%%d %%b %%Y %%H:%%i')) AS last_insert
FROM tbl_member a
LEFT JOIN gtfw_user b ON b.`user_id` = a.`insert_user_id`
WHERE member_id = %d
";
$sql["insert_tblmember"] = "
INSERT INTO tbl_member
(
    member_name,
    member_birthdate,
    member_province_id,
    member_gender,
    member_latitude,
    member_longitude,
    insert_timestamp,
    insert_user_id
) VALUES (
    %s,
    %s,
    %s,
    %s,
    %s,
    %s,
    NOW(),
    %s
)
";
$sql["update_tblmember"] = "
UPDATE tbl_member
SET 
    member_name = %s,
    member_birthdate = %s,
    member_province_id = %s,
    member_gender = %s,
    member_latitude = %s,
    member_longitude = %s,
    update_timestamp = NOW(),
    update_user_id = %s
WHERE member_id = %d
";
$sql["delete_tblmember"] = "
DELETE FROM tbl_member
WHERE member_id = %d
";