---
# System prepended metadata

title: 2025-05-05 EOB上線Rundown

---

# 2025-05-05 EOB上線Rundown

- 時間:5/5 10:10開始  
- 上版的排程會需要約 3 小時作業  
- 測試單位:資訊&禮儀
- browser cache議題評估後，影響較大的會是[EOB前台](https://eobituary.lyls.com.tw/etiquette/manage)&[EOB後台](https://site-eobituary-backend.lyls.com.tw/)，兩個系統皆為內部人員操作平台，會提醒內部同仁使用無痕或是請出cache
- 目前實驗<br>1.透過cname轉址<br>2.nginx轉址，期望達到不受DNS渲染的影響

---

## ⏰ 正式上版時程表

|日期| 時間           | 項目 | 負責人 |狀態|備註|
|--|---------------|----------|-----------------------------------------------------|--|-|
|05/02|下班前|  `eob-vue-forestage`、`eob-vue-backstage`、`eob-api` 的 `release/20250505` 發Prod pr | 資轉-展誠||[eob-vue-forestage](https://gitlab.lungyengroup.com.tw/eob/eob-vue-forestage/-/merge_requests/132)、[eob-vue-backstage](https://gitlab.lungyengroup.com.tw/eob/eob-vue-backstage/-/merge_requests/25)、[eob-api](https://gitlab.lungyengroup.com.tw/eob/eob-api/-/merge_requests/99)|
|05/05| **10:10 ~ 10:20**| 將 [前台](https://eobituary.lyls.com.tw/)、[後台](https://eobituary-backend.lyls.com.tw/) 切換至維護頁面 | 網系-文偉||doamin:製作:https://eobituary.lyls.com.tw<br>後台、家屬、賓客:https://eobituary-backend.lyls.com.tw|
||**10:10 ~ 10:20**| Prod DB 做snapshot(✅ 備份約10min)| 網系-文偉|
||**10:20 ~ 11:00**| 1.sql語法執行 <br>2.prod merge `release/20250505` 並進行 CI/CD Build Project| 資轉-展誠 ||<a href="#SQL-語法執行">SQL 語法執行</a>|
||**10:20 ~ 10:45**| 移除內網維護頁 & 調整DNS僅公司內網可連線 | 網系-文偉|清DNS語法|
||**11:00 ~ 11:30**| 資訊內部測試 & 處理issue | 資訊-雅英、展誠|
||**11:30 ~ 12:40**| 內部測試 & 處理issue | 禮儀-小釧&資訊-雅英、展誠|
||**12:40 ~ 12:59**| 移除外網維護頁 & 調回DNS| 網系-文偉 |
||**13:00**        | [前台](https://eobituary.lyls.com.tw/)、[後台](https://eobituary-backend.lyls.com.tw/) 正式對外 | 網系-文偉|
||**13:00 ~ 14:00**        | Grafana & skywalk觀察 | 資轉-terence|
|後續觀察||EOB 同步資材系統有無異常重複訃聞|資轉-展誠|||

---

## 🔁 Rollback Plan

|日期| 時間           | 項目 | 負責人 |狀態|
|--|---------------|----------|-----------------------------------------------------|--|
|05/05| **11:30後** |  k8s rollback（預估約 15 分鐘） |資轉-展誠|
||**11:30後** | Prod DB rollback（預估約 30 分鐘）| 網系-文偉|
||**11:30~11:45**| 內部測試 | 禮儀-小釧3人&資訊-雅英、展誠|
||**11:45~11:55**| 移除外網維護頁 & 調回DNS| 網系-文偉 |
||**12:00**      | [前台](https://eobituary.lyls.com.tw/)、[後台](https://eobituary-backend.lyls.com.tw/) 正式對外 | 網系-文偉|

---

## SQL 語法執行

:::spoiler <!-- :heavy_check_mark: --> 新增 Table

##### envelope_data

```sql=
CREATE TABLE EOB_Prod.dbo.envelope_data (
	id bigint IDENTITY(1,1) NOT NULL,
	obituary_id bigint NOT NULL,
	content nvarchar(500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	update_dt datetime2 NOT NULL,
	user_id nvarchar(32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	CONSTRAINT PK__envelope__3213E83FA964BCCE PRIMARY KEY (id)
);
```

##### param_data

```sql=
CREATE TABLE EOB_Prod.dbo.param_data (
	id bigint IDENTITY(1,1) NOT NULL,
	param_type int NOT NULL,
	param_value nvarchar(500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	update_dt datetime2 NOT NULL,
	user_id nvarchar(32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	CONSTRAINT PK_param_data PRIMARY KEY (id)
);

```

##### sysdiagrams

```sql=
CREATE TABLE EOB_Prod.dbo.sysdiagrams (
	name sysname COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	principal_id int NOT NULL,
	diagram_id int IDENTITY(1,1) NOT NULL,
	version int NULL,
	definition varbinary(MAX) NULL,
	CONSTRAINT PK__sysdiagr__C2B05B61788F4297 PRIMARY KEY (diagram_id),
	CONSTRAINT UK_principal_name UNIQUE (principal_id,name)
);
```

:::

:::spoiler <!-- :heavy_check_mark: --> 新增 View Table - 1

##### view_answer_message_count_2

```sql=
create view view_answer_message_count_2 as 
select id, erp_case_id ,deceased , ceremony_date , ceremony_family_time ,obituary.dept_id ,obituary.area_id , etiquette_user_id, f.user_name,case_status ,create_dt,
ISNULL ( answer_reply_count , 0 ) as  answer_reply_count, 
ISNULL ( answer_reply_attend_count , 0 ) as  answer_reply_attend_count,
ISNULL ( answer_attend_count , 0 ) as  answer_attend_count,
ISNULL ( message_count , 0 ) as  message_count
from obituary 
left join (select obituary_id,  count(*) as answer_reply_count  from guest_answer where obituary_question_id in(select id from obituary_question  where question_id =3 ) group by obituary_id) as b on id = b.obituary_id
left outer join (select  obituary_id, count(*)  as answer_reply_attend_count from  guest_answer where obituary_question_id in(select id from obituary_question  where question_id =3 )  and  (ISNUMERIC(answer)  = 1 And  answer!='0')  group by obituary_id) as c on id= c.obituary_id
left outer join (select obituary_id, SUM(cast (answer as int) ) as answer_attend_count  from guest_answer where obituary_question_id in(select id from obituary_question  where question_id =3) and ISNUMERIC(answer) = 1 and answer not like '%,%' group by obituary_id) as d  on id= d.obituary_id
left outer join (select obituary_id,  count(*) as message_count  from guest_message group by obituary_id) as e  on id= e.obituary_id
inner join user_info as f on etiquette_user_id = f.user_id;
```

:::

:::spoiler <!-- :heavy_check_mark: --> Table: obituary 新增欄位

##### obituary

```sql=
ALTER TABLE obituary
ADD limitwords INT NOT NULL DEFAULT 40,
    theme_thank_card_done BIT NOT NULL DEFAULT 0,
    remembrance_card_done BIT NOT NULL DEFAULT 0,
    carousel_seconds INT NOT NULL DEFAULT 8,
    show_survey_enable BIT NOT NULL DEFAULT 1,
    bereavement_shorturl_content NVARCHAR(700) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    guest_shorturl_content NVARCHAR(700) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    copy_name NVARCHAR(16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL;
```

:::

:::spoiler <!-- :heavy_check_mark: --> 新增索引

##### statistics_data

```sql=
CREATE NONCLUSTERED INDEX statistics_data_idx01 
ON EOB_Prod.dbo.statistics_data (guest_code ASC, statistics_type ASC)  
WITH (
    PAD_INDEX = OFF,
    FILLFACTOR = 100,
    SORT_IN_TEMPDB = OFF,
    IGNORE_DUP_KEY = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON
) 
ON [PRIMARY];
```

:::

:::spoiler <!-- :heavy_check_mark: --> fn_diagramobjects

```sql=
CREATE FUNCTION dbo.fn_diagramobjects() 
	RETURNS int
	WITH EXECUTE AS N'dbo'
	AS
	BEGIN
		declare @id_upgraddiagrams		int
		declare @id_sysdiagrams			int
		declare @id_helpdiagrams		int
		declare @id_helpdiagramdefinition	int
		declare @id_creatediagram	int
		declare @id_renamediagram	int
		declare @id_alterdiagram 	int 
		declare @id_dropdiagram		int
		declare @InstalledObjects	int

		select @InstalledObjects = 0

		select 	@id_upgraddiagrams = object_id(N'dbo.sp_upgraddiagrams'),
			@id_sysdiagrams = object_id(N'dbo.sysdiagrams'),
			@id_helpdiagrams = object_id(N'dbo.sp_helpdiagrams'),
			@id_helpdiagramdefinition = object_id(N'dbo.sp_helpdiagramdefinition'),
			@id_creatediagram = object_id(N'dbo.sp_creatediagram'),
			@id_renamediagram = object_id(N'dbo.sp_renamediagram'),
			@id_alterdiagram = object_id(N'dbo.sp_alterdiagram'), 
			@id_dropdiagram = object_id(N'dbo.sp_dropdiagram')

		if @id_upgraddiagrams is not null
			select @InstalledObjects = @InstalledObjects + 1
		if @id_sysdiagrams is not null
			select @InstalledObjects = @InstalledObjects + 2
		if @id_helpdiagrams is not null
			select @InstalledObjects = @InstalledObjects + 4
		if @id_helpdiagramdefinition is not null
			select @InstalledObjects = @InstalledObjects + 8
		if @id_creatediagram is not null
			select @InstalledObjects = @InstalledObjects + 16
		if @id_renamediagram is not null
			select @InstalledObjects = @InstalledObjects + 32
		if @id_alterdiagram  is not null
			select @InstalledObjects = @InstalledObjects + 64
		if @id_dropdiagram is not null
			select @InstalledObjects = @InstalledObjects + 128
		
		return @InstalledObjects 
	END;
```

:::

:::spoiler <!-- :heavy_check_mark: --> sp_alterdiagram

```sql=
CREATE PROCEDURE dbo.sp_alterdiagram
	(
		@diagramname 	sysname,
		@owner_id	int	= null,
		@version 	int,
		@definition 	varbinary(max)
	)
	WITH EXECUTE AS 'dbo'
	AS
	BEGIN
		set nocount on
	
		declare @theId 			int
		declare @retval 		int
		declare @IsDbo 			int
		
		declare @UIDFound 		int
		declare @DiagId			int
		declare @ShouldChangeUID	int
	
		if(@diagramname is null)
		begin
			RAISERROR ('Invalid ARG', 16, 1)
			return -1
		end
	
		execute as caller;
		select @theId = DATABASE_PRINCIPAL_ID();	 
		select @IsDbo = IS_MEMBER(N'db_owner'); 
		if(@owner_id is null)
			select @owner_id = @theId;
		revert;
	
		select @ShouldChangeUID = 0
		select @DiagId = diagram_id, @UIDFound = principal_id from dbo.sysdiagrams where principal_id = @owner_id and name = @diagramname 
		
		if(@DiagId IS NULL or (@IsDbo = 0 and @theId <> @UIDFound))
		begin
			RAISERROR ('Diagram does not exist or you do not have permission.', 16, 1);
			return -3
		end
	
		if(@IsDbo <> 0)
		begin
			if(@UIDFound is null or USER_NAME(@UIDFound) is null) -- invalid principal_id
			begin
				select @ShouldChangeUID = 1 ;
			end
		end

		-- update dds data			
		update dbo.sysdiagrams set definition = @definition where diagram_id = @DiagId ;

		-- change owner
		if(@ShouldChangeUID = 1)
			update dbo.sysdiagrams set principal_id = @theId where diagram_id = @DiagId ;

		-- update dds version
		if(@version is not null)
			update dbo.sysdiagrams set version = @version where diagram_id = @DiagId ;

		return 0
	END;
```

:::

:::spoiler <!-- :heavy_check_mark: --> sp_creatediagram

```sql=
CREATE PROCEDURE dbo.sp_creatediagram
	(
		@diagramname 	sysname,
		@owner_id		int	= null, 	
		@version 		int,
		@definition 	varbinary(max)
	)
	WITH EXECUTE AS 'dbo'
	AS
	BEGIN
		set nocount on
	
		declare @theId int
		declare @retval int
		declare @IsDbo	int
		declare @userName sysname
		if(@version is null or @diagramname is null)
		begin
			RAISERROR (N'E_INVALIDARG', 16, 1);
			return -1
		end
	
		execute as caller;
		select @theId = DATABASE_PRINCIPAL_ID(); 
		select @IsDbo = IS_MEMBER(N'db_owner');
		revert; 
		
		if @owner_id is null
		begin
			select @owner_id = @theId;
		end
		else
		begin
			if @theId <> @owner_id
			begin
				if @IsDbo = 0
				begin
					RAISERROR (N'E_INVALIDARG', 16, 1);
					return -1
				end
				select @theId = @owner_id
			end
		end
		-- next 2 line only for test, will be removed after define name unique
		if EXISTS(select diagram_id from dbo.sysdiagrams where principal_id = @theId and name = @diagramname)
		begin
			RAISERROR ('The name is already used.', 16, 1);
			return -2
		end
	
		insert into dbo.sysdiagrams(name, principal_id , version, definition)
				VALUES(@diagramname, @theId, @version, @definition) ;
		
		select @retval = @@IDENTITY 
		return @retval
	END;
```

:::

:::spoiler <!-- :heavy_check_mark: --> sp_dropdiagram

```sql=
CREATE PROCEDURE dbo.sp_dropdiagram
	(
		@diagramname 	sysname,
		@owner_id	int	= null
	)
	WITH EXECUTE AS 'dbo'
	AS
	BEGIN
		set nocount on
		declare @theId 			int
		declare @IsDbo 			int
		
		declare @UIDFound 		int
		declare @DiagId			int
	
		if(@diagramname is null)
		begin
			RAISERROR ('Invalid value', 16, 1);
			return -1
		end
	
		EXECUTE AS CALLER;
		select @theId = DATABASE_PRINCIPAL_ID();
		select @IsDbo = IS_MEMBER(N'db_owner'); 
		if(@owner_id is null)
			select @owner_id = @theId;
		REVERT; 
		
		select @DiagId = diagram_id, @UIDFound = principal_id from dbo.sysdiagrams where principal_id = @owner_id and name = @diagramname 
		if(@DiagId IS NULL or (@IsDbo = 0 and @UIDFound <> @theId))
		begin
			RAISERROR ('Diagram does not exist or you do not have permission.', 16, 1)
			return -3
		end
	
		delete from dbo.sysdiagrams where diagram_id = @DiagId;
	
		return 0;
	END;
```

:::

:::spoiler <!-- :heavy_check_mark: --> sp_helpdiagramdefinition

```sql=
CREATE PROCEDURE dbo.sp_helpdiagramdefinition
	(
		@diagramname 	sysname,
		@owner_id	int	= null 		
	)
	WITH EXECUTE AS N'dbo'
	AS
	BEGIN
		set nocount on

		declare @theId 		int
		declare @IsDbo 		int
		declare @DiagId		int
		declare @UIDFound	int
	
		if(@diagramname is null)
		begin
			RAISERROR (N'E_INVALIDARG', 16, 1);
			return -1
		end
	
		execute as caller;
		select @theId = DATABASE_PRINCIPAL_ID();
		select @IsDbo = IS_MEMBER(N'db_owner');
		if(@owner_id is null)
			select @owner_id = @theId;
		revert; 
	
		select @DiagId = diagram_id, @UIDFound = principal_id from dbo.sysdiagrams where principal_id = @owner_id and name = @diagramname;
		if(@DiagId IS NULL or (@IsDbo = 0 and @UIDFound <> @theId ))
		begin
			RAISERROR ('Diagram does not exist or you do not have permission.', 16, 1);
			return -3
		end

		select version, definition FROM dbo.sysdiagrams where diagram_id = @DiagId ; 
		return 0
	END;
```

:::

:::spoiler <!-- :heavy_check_mark: --> sp_helpdiagrams

```sql=
CREATE PROCEDURE dbo.sp_helpdiagrams
	(
		@diagramname sysname = NULL,
		@owner_id int = NULL
	)
	WITH EXECUTE AS N'dbo'
	AS
	BEGIN
		DECLARE @user sysname
		DECLARE @dboLogin bit
		EXECUTE AS CALLER;
			SET @user = USER_NAME();
			SET @dboLogin = CONVERT(bit,IS_MEMBER('db_owner'));
		REVERT;
		SELECT
			[Database] = DB_NAME(),
			[Name] = name,
			[ID] = diagram_id,
			[Owner] = USER_NAME(principal_id),
			[OwnerID] = principal_id
		FROM
			sysdiagrams
		WHERE
			(@dboLogin = 1 OR USER_NAME(principal_id) = @user) AND
			(@diagramname IS NULL OR name = @diagramname) AND
			(@owner_id IS NULL OR principal_id = @owner_id)
		ORDER BY
			4, 5, 1
	END;
```

:::

:::spoiler <!-- :heavy_check_mark: --> sp_renamediagram

```sql=
CREATE PROCEDURE dbo.sp_renamediagram
	(
		@diagramname 		sysname,
		@owner_id		int	= null,
		@new_diagramname	sysname
	
	)
	WITH EXECUTE AS 'dbo'
	AS
	BEGIN
		set nocount on
		declare @theId 			int
		declare @IsDbo 			int
		
		declare @UIDFound 		int
		declare @DiagId			int
		declare @DiagIdTarg		int
		declare @u_name			sysname
		if((@diagramname is null) or (@new_diagramname is null))
		begin
			RAISERROR ('Invalid value', 16, 1);
			return -1
		end
	
		EXECUTE AS CALLER;
		select @theId = DATABASE_PRINCIPAL_ID();
		select @IsDbo = IS_MEMBER(N'db_owner'); 
		if(@owner_id is null)
			select @owner_id = @theId;
		REVERT;
	
		select @u_name = USER_NAME(@owner_id)
	
		select @DiagId = diagram_id, @UIDFound = principal_id from dbo.sysdiagrams where principal_id = @owner_id and name = @diagramname 
		if(@DiagId IS NULL or (@IsDbo = 0 and @UIDFound <> @theId))
		begin
			RAISERROR ('Diagram does not exist or you do not have permission.', 16, 1)
			return -3
		end
	
		-- if((@u_name is not null) and (@new_diagramname = @diagramname))	-- nothing will change
		--	return 0;
	
		if(@u_name is null)
			select @DiagIdTarg = diagram_id from dbo.sysdiagrams where principal_id = @theId and name = @new_diagramname
		else
			select @DiagIdTarg = diagram_id from dbo.sysdiagrams where principal_id = @owner_id and name = @new_diagramname
	
		if((@DiagIdTarg is not null) and  @DiagId <> @DiagIdTarg)
		begin
			RAISERROR ('The name is already used.', 16, 1);
			return -2
		end		
	
		if(@u_name is null)
			update dbo.sysdiagrams set [name] = @new_diagramname, principal_id = @theId where diagram_id = @DiagId
		else
			update dbo.sysdiagrams set [name] = @new_diagramname where diagram_id = @DiagId
		return 0
	END;
```

:::

:::spoiler <!-- :heavy_check_mark: --> sp_upgraddiagrams

```sql=
CREATE PROCEDURE dbo.sp_upgraddiagrams
	AS
	BEGIN
		IF OBJECT_ID(N'dbo.sysdiagrams') IS NOT NULL
			return 0;
	
		CREATE TABLE dbo.sysdiagrams
		(
			name sysname NOT NULL,
			principal_id int NOT NULL,	-- we may change it to varbinary(85)
			diagram_id int PRIMARY KEY IDENTITY,
			version int,
	
			definition varbinary(max)
			CONSTRAINT UK_principal_name UNIQUE
			(
				principal_id,
				name
			)
		);


		/* Add this if we need to have some form of extended properties for diagrams */
		/*
		IF OBJECT_ID(N'dbo.sysdiagram_properties') IS NULL
		BEGIN
			CREATE TABLE dbo.sysdiagram_properties
			(
				diagram_id int,
				name sysname,
				value varbinary(max) NOT NULL
			)
		END
		*/

		IF OBJECT_ID(N'dbo.dtproperties') IS NOT NULL
		begin
			insert into dbo.sysdiagrams
			(
				[name],
				[principal_id],
				[version],
				[definition]
			)
			select	 
				convert(sysname, dgnm.[uvalue]),
				DATABASE_PRINCIPAL_ID(N'dbo'),			-- will change to the sid of sa
				0,							-- zero for old format, dgdef.[version],
				dgdef.[lvalue]
			from dbo.[dtproperties] dgnm
				inner join dbo.[dtproperties] dggd on dggd.[property] = 'DtgSchemaGUID' and dggd.[objectid] = dgnm.[objectid]	
				inner join dbo.[dtproperties] dgdef on dgdef.[property] = 'DtgSchemaDATA' and dgdef.[objectid] = dgnm.[objectid]
				
			where dgnm.[property] = 'DtgSchemaNAME' and dggd.[uvalue] like N'_EA3E6268-D998-11CE-9454-00AA00A3F36E_' 
			return 2;
		end
		return 1;
	END;
```

:::

:::spoiler <!-- :heavy_check_mark: --> 新增 View Table - 2

##### view_answer_attend_count

```sql=
create view view_answer_attend_count as 
select obituary_id, cast (answer as int) as answer from guest_answer 
where obituary_question_id in (select obituary_question_id from  view_answer_attend_count_base);
```

##### view_answer_attend_count_base

```sql=
create view view_answer_attend_count_base as
select obituary_question_id from guest_answer where obituary_question_id in(select id from obituary_question  where question_id =3) and ISNUMERIC(answer) = 1 and answer not like '%,%';
```

:::

:::spoiler <!-- :heavy_check_mark: --> 新增資料

##### Table: param_data

```sql=
INSERT INTO EOB_Prod.dbo.param_data (param_type, param_value, update_dt, user_id)
VALUES (1, N'3', GETDATE(), N'Z0000033');

INSERT INTO EOB_Prod.dbo.param_data (param_type, param_value, update_dt, user_id)
VALUES (2, N'家屬專區', GETDATE(), N'Z0000033');

INSERT INTO EOB_Prod.dbo.param_data (param_type, param_value, update_dt, user_id)
VALUES (3, N'陪伴，不會隨著困難而放棄\n牽掛，不會隨著離別而減少', GETDATE(), N'Z0000033');

INSERT INTO EOB_Prod.dbo.param_data (param_type, param_value, update_dt, user_id)
VALUES (4, N'追思函', GETDATE(), N'Z0000033');

INSERT INTO EOB_Prod.dbo.param_data (param_type, param_value, update_dt, user_id)
VALUES (5, N'愛，不會隨著離去而消失\n思念，不會隨著逝去而停止', GETDATE(), N'Z0000033');
```

##### Table: menu

```sql=
INSERT INTO EOB_Prod.dbo.menu
(menu_description, menu_name, menu_order, menu_path, parent_id, api_collection)
VALUES(N'其它樣式管理', N'其它樣式管理', 6, N'/other', 10, N'');
```

:::

:::spoiler <!-- :heavy_check_mark: --> 修改 View Table

##### view_answer_message_count

```sql=
-- 後台管理 -> 案件管理 -> 賓客回覆管理 & 追思留言管理 需在案號顯示副本名稱
-- 所以在 select 欄位中加入 copy_name

ALTER VIEW view_answer_message_count as
select id, erp_case_id ,deceased , ceremony_date , ceremony_family_time ,obituary.dept_id ,obituary.area_id , etiquette_user_id, f.user_name,case_status ,create_dt,
       ISNULL ( answer_reply_count , 0 ) as  answer_reply_count,
       ISNULL ( answer_reply_attend_count , 0 ) as  answer_reply_attend_count,
       ISNULL ( answer_attend_count , 0 ) as  answer_attend_count,
       ISNULL ( message_count , 0 ) as  message_count,
       copy_name
from obituary
         left join (select obituary_id,  count(*) as answer_reply_count  from guest_answer where obituary_question_id in(select id from obituary_question  where question_id =3 ) group by obituary_id) as b on id = b.obituary_id
         left outer join (select  obituary_id, count(*)  as answer_reply_attend_count from  guest_answer where obituary_question_id in(select id from obituary_question  where question_id =3 )  and  (ISNUMERIC(answer)  = 1 And  answer!='0')  group by obituary_id) as c on id= c.obituary_id
         left outer join (select obituary_id, SUM(cast (answer as int) ) as answer_attend_count  from guest_answer where obituary_question_id in(select id from obituary_question  where question_id =3) and ISNUMERIC(answer) = 1 and answer not like '%,%' group by obituary_id) as d  on id= d.obituary_id
         left outer join (select obituary_id,  count(*) as message_count  from guest_message group by obituary_id) as e  on id= e.obituary_id
         inner join user_info as f on etiquette_user_id = f.user_id;
```

:::

:::spoiler <!-- :heavy_check_mark: --> 更新欄位值

##### Table: obituary

```sql=
-- ADD theme_thank_card_done BIT NULL DEFAULT 0
-- 改為
-- ADD theme_thank_card_done BIT NOT NULL DEFAULT 0
-- 上正式時觀察

-- 有需要才用以下語法
select count(*)
from obituary
where theme_thank_card_done is null

update obituary
set theme_thank_card_done = 0
where theme_thank_card_done is null
```

```sql=
-- ADD remembrance_card_done BIT NULL DEFAULT 0
-- 改為
-- ADD remembrance_card_done BIT NOT NULL DEFAULT 0
-- 上正式時觀察

-- 有需要才用以下語法
select count(*)
from obituary
where remembrance_card_done is null

update obituary
set remembrance_card_done = 0
where remembrance_card_done is null
```

```sql=
-- ADD show_survey_enable BIT NULL DEFAULT 1
-- 改為
-- ADD show_survey_enable BIT NOT NULL DEFAULT 1
-- 上正式時觀察

-- 有需要才用以下語法
select count(*)
from obituary
where show_survey_enable is null

UPDATE obituary
SET show_survey_enable = 1
where show_survey_enable is null
```

:::
