1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
| -- 新增consultation_method欄位
|
| CREATE OR REPLACE VIEW omo.appointment_customer_view
| AS SELECT a.id AS appointment_id,
| a.phone,
| a.email,
| a.contact_type,
| a.gender,
| a.age,
| a.job,
| a.requirement,
| a.communicate_status,
| a.hope_contact_time,
| a.other_requirement,
| a.agent_no,
| a.appointment_date,
| a.customer_id,
| a.consultant_view_time,
| a.consultant_read_time,
| a.contact_time,
| a.status,
| a.last_modified_date,
| c.name,
| a.consultation_method
| FROM appointment a
| LEFT JOIN customer c ON a.customer_id = c.id;
|
|
|
| -- public.appointment_customer_consultant_view source
|
| CREATE OR REPLACE VIEW omo.appointment_customer_consultant_view
| AS SELECT appointment.id AS appointment_id,
| appointment.phone,
| appointment.email,
| appointment.contact_type,
| appointment.gender,
| appointment.age,
| appointment.job,
| appointment.requirement,
| appointment.communicate_status,
| appointment.hope_contact_time,
| appointment.other_requirement,
| appointment.appointment_date,
| appointment.last_modified_date,
| appointment.agent_no,
| appointment.consultant_view_time,
| appointment.consultant_read_time,
| appointment.contact_time,
| appointment.status,
| consultant.name AS consultant_name,
| consultant.phone_number AS consultant_phone_number,
| consultant.email AS consultant_email,
| consultant.role AS consultant_role,
| customer.name AS customer_name,
| appointment.consultation_method as consultation_method
| FROM appointment
| LEFT JOIN consultant ON appointment.agent_no::text = consultant.agent_no::text
| LEFT JOIN customer ON appointment.customer_id = customer.id;
|
|
|
|
| -- public.appointment_customer_consultant_view source , 新增consultant_gender和consultant_entry_date欄位
|
| CREATE OR REPLACE VIEW public.appointment_customer_consultant_view
| AS SELECT appointment.id AS appointment_id,
| appointment.phone,
| appointment.email,
| appointment.contact_type,
| appointment.gender,
| appointment.age,
| appointment.job,
| appointment.requirement,
| appointment.communicate_status,
| appointment.hope_contact_time,
| appointment.other_requirement,
| appointment.appointment_date,
| appointment.last_modified_date,
| appointment.agent_no,
| appointment.consultant_view_time,
| appointment.consultant_read_time,
| appointment.contact_time,
| appointment.status,
| consultant.name AS consultant_name,
| consultant.phone_number AS consultant_phone_number,
| consultant.email AS consultant_email,
| consultant.role AS consultant_role,
| customer.name AS customer_name,
| appointment.consultation_method,
| consultant.gender as consultant_gender,
| consultant.entry_date as consultant_entry_date
| FROM appointment
| LEFT JOIN consultant ON appointment.agent_no::text = consultant.agent_no::text
| LEFT JOIN customer ON appointment.customer_id = customer.id;
|
|
