/*
	Kaid : CREATE PROCEDURE AND ALTER PROCEDURE 各跑一次
*/
-------------------------------------------------------------------CREATE--------------------------------------------------------------------------------------------------
/****** Object:  StoredProcedure [dbo].[sp_app_equip_maintain_colse]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: APP 維修單 close
	OODA_RESULT: SCALAR
*/
/*
	修改歷程
	時間				修正人		Mark
	 260625			Kaid		Create
*/
CREATE PROCEDURE [dbo].[sp_app_equip_maintain_colse] 
	@mt_event_id	BIGINT,
	@mtsid			INT,
	@fs_arr			NVARCHAR(MAX) = NULL,
	@remark			NVARCHAR(50) = NULL,
	@update_uid		SMALLINT
AS
	DECLARE @eqpid			INT
	DECLARE @event_log_id	BIGINT = -1
	DECLARE @now		DATETIME = GETDATE()

	SELECT @eqpid = eqpid FROM dbo.maintenance_event_log WHERE mt_event_id = @mt_event_id

	UPDATE dbo.maintenance_event
	SET
		mtsid = @mtsid,
		end_dt = @now,
		end_uid = @update_uid
	WHERE	
		mt_event_id = @mt_event_id

	INSERT INTO dbo.maintenance_event_log
		(mt_event_id, mtsid, eqpid, remark, mt_dt, create_dt, create_uid)
	VALUES
		(@mt_event_id, @mtsid, @eqpid, @remark, @now, @now, @update_uid)

	SELECT @event_log_id = SCOPE_IDENTITY()

	IF(@fs_arr IS NOT NULL)
	BEGIN
		INSERT INTO dbo.maintenance_fs
			SELECT
				@event_log_id, value
			FROM
				string_split(@fs_arr, ',')
	END

	UPDATE dbo.eqp
	SET
		eqpsid = 5,
		update_dt = GETDATE(),
		update_uid = @update_uid
	WHERE
		eqpid = @eqpid 

	SELECT @event_log_id AS RESULT
GO
/****** Object:  StoredProcedure [dbo].[sp_app_mold_bind_update]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: 更新設備上下刀狀態
	OODA_RESULT: SCALAR
*/
/*
	修改歷程
	時間				修正人		Mark
	 260618			Kaid		Create
*/
CREATE PROCEDURE [dbo].[sp_app_mold_bind_update] 
	@moldid			INT,
	@eqpid			INT,
	@moldsid		TINYINT,
	@moldbindsid	TINYINT,
	@opid			INT = NULL,
	@usage_count	INT = NULL,
	@fsid			NVARCHAR(50) = NULL,
	@remark			NVARCHAR(50) = NULL,
	@change_dt		DATETIME,
	@update_uid		SMALLINT
AS
	DECLARE @state		INT = -99
	DECLARE @check_mold	INT = -1
	DECLARE @check_eqp	INT = -1
	DECLARE @is_on		BIT 
	DECLARE @check_op	INT = -1
	DECLARE @moldtid	TINYINT = 0

	SELECT @moldtid = moldtid FROM dbo.mold WHERE moldid = @moldid

	SELECT TOP(1) @is_on = is_on FROM dbo.mold_bind_log WHERE eqpid = @eqpid ORDER BY create_dt DESC
	SELECT @check_mold = moldid, @state = moldbindsid FROM dbo.mold WHERE moldid = @moldid AND is_deleted = 0
	SELECT @check_eqp = eqpid FROM dbo.eqp WHERE @eqpid = @eqpid AND is_deleted = 0
	SELECT @check_op = opid FROM dbo.map_op_eqp WHERE eqpid = @eqpid  AND (opid = @opid OR @opid is NULL)

	IF EXISTS (SELECT * FROM dbo.mold WHERE moldtid = @moldtid AND moldid IN (SELECT moldid FROM dbo.map_op_mold WHERE eqpid = @eqpid))
	BEGIN
		SET @state = -1 -- 機台已綁同類型刀具
	END
	ELSE IF (@check_mold < 0 )
	BEGIN
		SET	@state = -2 -- 找不到模具
	END
	ELSE IF (@check_eqp < 0)
	BEGIN
		SET @state = -3 -- 找不到機台
	END
	ELSE IF (@state <> 1 AND @moldbindsid = 2)
	BEGIN
		SET @state = -4 -- 模具狀態不能綁定
	END
	ELSE IF(@check_op <= 0)
	BEGIN
		SET @state = -5 -- 機台未綁站點
	END
	ELSE
	BEGIN
		UPDATE dbo.mold
		SET
			
			moldsid = @moldsid,
			moldbindsid = @moldbindsid,
			update_dt = GETDATE(),
			update_uid = @update_uid
		WHERE
			moldid = @moldid

		IF(@moldsid = 4 AND @moldbindsid = 2) -- 上刀
		BEGIN
			INSERT INTO dbo.mold_bind_log
				(eqpid, moldid, usage_count, moldsid, moldtid, is_on, fsid, remark, [uid], change_dt, create_dt, create_uid)
			VALUES
				(@eqpid, @moldid, @usage_count, @moldsid, @moldtid, 1, @fsid, @remark, @update_uid, GETDATE(), GETDATE(), @update_uid)

			INSERT INTO dbo.map_op_mold
				(moldid, opid, eqpid)
			VALUES
				(@moldid, @check_op, @eqpid)

			SET @state = 1		
		END
		ELSE -- 下刀 損換、送修...都算下刀
		BEGIN
			IF(@usage_count IS NULL OR @usage_count = 0)
			BEGIN
				SELECT TOP(1) @usage_count = usage_count FROM dbo.mold_bind_log WHERE is_on = 1 AND eqpid = @eqpid AND moldid = @moldid ORDER BY create_dt DESC
			END

			INSERT INTO dbo.mold_bind_log
				(eqpid, moldid, usage_count, moldsid, moldtid, is_on, fsid, remark, [uid], change_dt, create_dt, create_uid)
			VALUES
				(@eqpid, @moldid, @usage_count, @moldsid, @moldtid, 0, @fsid, @remark, @update_uid, @change_dt, GETDATE(), @update_uid)

			DELETE FROM dbo.map_op_mold
			WHERE moldid = @moldid AND eqpid = @eqpid
				
			SET @state = 1				

		END
	END

	SELECT @state AS RESULT
GO
/****** Object:  StoredProcedure [dbo].[sp_app_mpoeqp_list_BTOX]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: 拿可用機台
	OODA_RESULT: RECORDSET
*/
/*
	修改歷程
	時間			修正人		Mark
	 260525			Kaid		CREATE
*/
CREATE PROCEDURE [dbo].[sp_app_mpoeqp_list_BTOX]
	@oactid		INT,
	@apteid		SMALLINT
AS
	SELECT DISTINCT
		dbo.mpoeqp.eqpid, eqpno, eqpname, IIF(dbo.iot.iot_id IS NULL , 'False', 'True') AS is_binded 
	FROM
		dbo.mpoeqp INNER JOIN
		dbo.eqp ON dbo.eqp.eqpid = dbo.mpoeqp.eqpid LEFT JOIN
		dbo.iot ON dbo.iot.eqpid = dbo.eqp.eqpid
	WHERE
		mpoid IN (SELECT mpoid FROM dbo.mpo WHERE oactid = @oactid AND apteid = @apteid) AND dbo.eqp.eqpsid IN (1, 2, 5)
GO
/****** Object:  StoredProcedure [dbo].[sp_app_sys_role_funcode_list]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: 權限角色權限清單
	OODA_RESULT: RECORDSET
*/
CREATE PROCEDURE [dbo].[sp_app_sys_role_funcode_list] 
	@rid		SMALLINT = -1
AS

	DECLARE @roleFuncode TABLE
	(
		funcode_id	SMALLINT,
		is_auth		BIT,
		has_write	BIT
	)

	IF @rid = -1
	BEGIN
		SELECT          
			fncid, fncno AS fncname, isapp, isweb, 'FALSE' AS is_auth, 'FALSE' AS has_write
		FROM              
			dbo.cfnc
		WHERE
			isapp <> 0 OR isweb <> 0
	END
	ELSE
	BEGIN
		INSERT INTO @roleFuncode
			SELECT fcid, 1, has_write FROM dbo.sysrfc WHERE rid = @rid

		SELECT          
			fncid, fncno AS fncname, isapp, isweb, ISNULL(roleTb.is_auth, 'FALSE') AS is_auth, has_write
		FROM              
			dbo.cfnc LEFT JOIN 
			@roleFuncode AS roleTb ON roleTb.funcode_id = dbo.cfnc.fncid
		WHERE
			isapp <> 0 OR isweb <> 0
		ORDER BY isapp, funcode_id
	END
GO
/****** Object:  StoredProcedure [dbo].[sp_BTOX_erp_info_create]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: BTOX 顏色
	OODA_RESULT: SCALAR
*/
/*
	修改歷程
	時間				修正人		Mark
	 20727			Kaid		Create
*/
CREATE PROCEDURE [dbo].[sp_BTOX_erp_info_create] 
    @runcard_no                     NVARCHAR(50) = null
   ,@customer                       NVARCHAR(50) = null
   ,@material_no                    NVARCHAR(50) = null
   ,@color                          NVARCHAR(50) = null
   ,@plasma                         NVARCHAR(50) = null
   ,@reich                          NVARCHAR(50) = null
   ,@remark                         NVARCHAR(50) = null
   ,@reich2                         NVARCHAR(50) = null
   ,@lot_no                         NVARCHAR(50) = null
   ,@lens_specifications_bc         FLOAT = NULL--成品 BC
   ,@lens_specifications_bcx        FLOAT = NULL--成品 BCX
   ,@lens_specifications_bcy        FLOAT = NULL--成品 BCY
   ,@lens_specifications_ct         FLOAT = NULL--成品 CT
   ,@lens_specificatons_tc          FLOAT = NULL--成品 TC
   ,@lens_specifications_dia        FLOAT = NULL--成品 DIA
   ,@lens_specifications_power      FLOAT = NULL--成品 POWER
   ,@lens_specifications_powerx     FLOAT = NULL--成品 POWERX
   ,@lens_specifications_powery     FLOAT = NULL--成品 POWERY
   ,@lens_semi_specifications_bc    FLOAT = NULL--半成品 BC
   ,@lens_semi_specifications_bcx   FLOAT = NULL--半成品 BCX
   ,@lens_semi_specifications_bcy   FLOAT = NULL--半成品 BCY
   ,@lens_semi_specifications_ct    FLOAT = NULL--半成品 CT
   ,@lens_semi_specifications_tc    FLOAT = NULL--半成品 TC
   ,@user_id                        INT = NULL
   ,@runcard_id                     BIGINT
AS
    INSERT INTO btox.erp_info
        (
         runcard_id 
        ,runcard_no
        ,customer
        ,material_no
        ,color--下拉選單
        ,plasma
        ,reich
        ,remark
        ,reich2
        ,lot_no
        ,lens_specifications_bc--成品 BC
        ,lens_specifications_bcx--成品 BCX
        ,lens_specifications_bcy--成品 BCY
        ,lens_specifications_ct--成品 CT
        ,lens_specificatons_tc--成品 TC
        ,lens_specifications_dia--成品 DIA
        ,lens_specifications_power--成品 POWER
        ,lens_specifications_powerx--成品 POWERX
        ,lens_specifications_powery--成品 POWERY
        ,lens_semi_specifications_bc--半成品 BC
        ,lens_semi_specifications_bcx--半成品 BCX
        ,lens_semi_specifications_bcy--半成品 BCY
        ,lens_semi_specifications_ct--半成品 CT
        ,lens_semi_specifications_tc--半成品 TC
        ,create_dt
        ,update_dt
        ,[user_id] 
        )
    VALUES
        (
         @runcard_id
        ,@runcard_no
        ,@customer
        ,@material_no
        ,@color--下拉選單
        ,@plasma
        ,@reich
        ,@remark
        ,@reich2
        ,@lot_no
        ,@lens_specifications_bc--成品 BC
        ,@lens_specifications_bcx--成品 BCX
        ,@lens_specifications_bcy--成品 BCY
        ,@lens_specifications_ct--成品 CT
        ,@lens_specificatons_tc--成品 TC
        ,@lens_specifications_dia--成品 DIA
        ,@lens_specifications_power--成品 POWER
        ,@lens_specifications_powerx--成品 POWERX
        ,@lens_specifications_powery--成品 POWERY
        ,@lens_semi_specifications_bc--半成品 BC
        ,@lens_semi_specifications_bcx--半成品 BCX
        ,@lens_semi_specifications_bcy--半成品 BCY
        ,@lens_semi_specifications_ct--半成品 CT
        ,@lens_semi_specifications_tc--半成品 TC
        ,GETDATE()
        ,GETDATE()
        ,@user_id
        )
	
    SELECT SCOPE_IDENTITY () AS RESULT
GO
/****** Object:  StoredProcedure [dbo].[sp_BTOX_erp_info_get]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: BTOX 顏色
	OODA_RESULT: RECORDSET
*/
/*
	修改歷程
	時間				修正人		Mark
	 20727			Kaid		Create
*/
CREATE PROCEDURE [dbo].[sp_BTOX_erp_info_get] 
    @rec_id       BIGINT
AS
	SELECT
		[rec_id]
      ,[runcard_id]
      ,[runcard_no]
      ,[customer]
      ,[material_no]
      ,[color]--下拉選單
      ,[plasma]
      ,[reich]
      ,[remark]
      ,[reich2]
      ,[lot_no]
      ,[lens_specifications_bc]--成品 BC
      ,[lens_specifications_bcx]--成品 BCX
      ,[lens_specifications_bcy]--成品 BCY
      ,[lens_specifications_ct]--成品 CT
      ,[lens_specificatons_tc]--成品 TC
      ,[lens_specifications_dia]--成品 DIA
      ,[lens_specifications_power]--成品 POWER
      ,[lens_specifications_powerx]--成品 POWERX
      ,[lens_specifications_powery]--成品 POWERY
      ,[lens_semi_specifications_bc]--半成品 BC
      ,[lens_semi_specifications_bcx]--半成品 BCX
      ,[lens_semi_specifications_bcy]--半成品 BCY
      ,[lens_semi_specifications_ct]--半成品 CT
      ,[lens_semi_specifications_tc]--半成品 TC
      ,[create_dt]
      ,[update_dt]
	FROM
		(
			SELECT
				rcid
			FROM
				dbo.rc
			WHERE
				rcsteid <> 6
		) AS rcTb INNER JOIN
        (
            SELECT
                [rec_id]
            ,[runcard_id]
            ,[runcard_no]
            ,[customer]
            ,[material_no]
            ,[color]--下拉選單
            ,[plasma]
            ,[reich]
            ,[remark]
            ,[reich2]
            ,[lot_no]
            ,[lens_specifications_bc]--成品 BC
            ,[lens_specifications_bcx]--成品 BCX
            ,[lens_specifications_bcy]--成品 BCY
            ,[lens_specifications_ct]--成品 CT
            ,[lens_specificatons_tc]--成品 TC
            ,[lens_specifications_dia]--成品 DIA
            ,[lens_specifications_power]--成品 POWER
            ,[lens_specifications_powerx]--成品 POWERX
            ,[lens_specifications_powery]--成品 POWERY
            ,[lens_semi_specifications_bc]--半成品 BC
            ,[lens_semi_specifications_bcx]--半成品 BCX
            ,[lens_semi_specifications_bcy]--半成品 BCY
            ,[lens_semi_specifications_ct]--半成品 CT
            ,[lens_semi_specifications_tc]--半成品 TC
            ,[create_dt]
            ,[update_dt]
            FROM
                btox.erp_info
            WHERE
                rec_id = @rec_id
        ) AS erpTb ON erpTb.runcard_id = rcTb.rcid
GO
/****** Object:  StoredProcedure [dbo].[sp_BTOX_erp_info_list]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: BTOX 顏色
	OODA_RESULT: RECORDSET
*/
/*
	修改歷程
	時間				修正人		Mark
	 20727			Kaid		Create
*/
CREATE PROCEDURE [dbo].[sp_BTOX_erp_info_list] 
    @erp_order_no       NVArCHAR(50) = NULL
AS
    IF (@erp_order_no IS NULL) SET @erp_order_no = ''

	SELECT
		[rec_id]
      ,[runcard_id]
      ,[runcard_no]
      ,[customer]
      ,[material_no]
      ,[color]--下拉選單
      ,[plasma]
      ,[reich]
      ,erpTb.[remark]
      ,[reich2]
      ,[lot_no]
      ,[lens_specifications_bc]--成品 BC
      ,[lens_specifications_bcx]--成品 BCX
      ,[lens_specifications_bcy]--成品 BCY
      ,[lens_specifications_ct]--成品 CT
      ,[lens_specificatons_tc]--成品 TC
      ,[lens_specifications_dia]--成品 DIA
      ,[lens_specifications_power]--成品 POWER
      ,[lens_specifications_powerx]--成品 POWERX
      ,[lens_specifications_powery]--成品 POWERY
      ,[lens_semi_specifications_bc]--半成品 BC
      ,[lens_semi_specifications_bcx]--半成品 BCX
      ,[lens_semi_specifications_bcy]--半成品 BCY
      ,[lens_semi_specifications_ct]--半成品 CT
      ,[lens_semi_specifications_tc]--半成品 TC
      ,[create_dt]
      ,erpTb.[update_dt]
      ,ptid
      ,dbo.p.pid
	FROM
		(
			SELECT
				rcid
			FROM
				dbo.rc
			WHERE
				rcsteid <> 6
		) AS rcTb INNER JOIN
        (
            SELECT
                [rec_id]
            ,[runcard_id]
            ,[runcard_no]
            ,[customer]
            ,[material_no]
            ,[color]--下拉選單
            ,[plasma]
            ,[reich]
            ,[remark]
            ,[reich2]
            ,[lot_no]
            ,[lens_specifications_bc]--成品 BC
            ,[lens_specifications_bcx]--成品 BCX
            ,[lens_specifications_bcy]--成品 BCY
            ,[lens_specifications_ct]--成品 CT
            ,[lens_specificatons_tc]--成品 TC
            ,[lens_specifications_dia]--成品 DIA
            ,[lens_specifications_power]--成品 POWER
            ,[lens_specifications_powerx]--成品 POWERX
            ,[lens_specifications_powery]--成品 POWERY
            ,[lens_semi_specifications_bc]--半成品 BC
            ,[lens_semi_specifications_bcx]--半成品 BCX
            ,[lens_semi_specifications_bcy]--半成品 BCY
            ,[lens_semi_specifications_ct]--半成品 CT
            ,[lens_semi_specifications_tc]--半成品 TC
            ,[create_dt]
            ,[update_dt]
            FROM
                btox.erp_info
            WHERE
                runcard_no LIKE '%' + @erp_order_no + '%'
        ) AS erpTb ON erpTb.runcard_id = rcTb.rcid INNER JOIN
        (
            SELECT
                rcid, pid
            FROM
                dbo.mpc
            WHERE
                is_processing = 1 AND is_deleted = 0
        ) AS mpcTb ON mpcTb.rcid = rcTb.rcid INNER JOIN
        dbo.p ON dbo.p.pid = mpcTb.pid
GO
/****** Object:  StoredProcedure [dbo].[sp_BTOX_erp_info_update]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: BTOX 顏色
	OODA_RESULT: VOID
*/
/*
	修改歷程
	時間				修正人		Mark
	 20727			Kaid		Create
*/
CREATE PROCEDURE [dbo].[sp_BTOX_erp_info_update]
    @rec_id                         BIGINT,
    @runcard_no                     NVARCHAR(50) = null
   ,@customer                       NVARCHAR(50) = null
   ,@material_no                    NVARCHAR(50) = null
   ,@color                          NVARCHAR(50) = null
   ,@plasma                         NVARCHAR(50) = null
   ,@reich                          NVARCHAR(50) = null
   ,@remark                         NVARCHAR(50) = null
   ,@reich2                         NVARCHAR(50) = null
   ,@lot_no                         NVARCHAR(50) = null
   ,@lens_specifications_bc         FLOAT = NULL--成品 BC
   ,@lens_specifications_bcx        FLOAT = NULL--成品 BCX
   ,@lens_specifications_bcy        FLOAT = NULL--成品 BCY
   ,@lens_specifications_ct         FLOAT = NULL--成品 CT
   ,@lens_specificatons_tc          FLOAT = NULL--成品 TC
   ,@lens_specifications_dia        FLOAT = NULL--成品 DIA
   ,@lens_specifications_power      FLOAT = NULL--成品 POWER
   ,@lens_specifications_powerx     FLOAT = NULL--成品 POWERX
   ,@lens_specifications_powery     FLOAT = NULL--成品 POWERY
   ,@lens_semi_specifications_bc    FLOAT = NULL--半成品 BC
   ,@lens_semi_specifications_bcx   FLOAT = NULL--半成品 BCX
   ,@lens_semi_specifications_bcy   FLOAT = NULL--半成品 BCY
   ,@lens_semi_specifications_ct    FLOAT = NULL--半成品 CT
   ,@lens_semi_specifications_tc    FLOAT = NULL--半成品 TC
   ,@user_id                        INT = NULL
AS
    UPDATE btox.erp_info
    SET
         runcard_no = @runcard_no
        ,customer = @customer
        ,material_no = @material_no
        ,color = @color--下拉選單
        ,plasma = @plasma
        ,reich = @reich
        ,remark = @remark
        ,reich2 = @reich2
        ,lot_no = @lot_no
        ,lens_specifications_bc = @lens_specifications_bc--成品 BC
        ,lens_specifications_bcx = @lens_specifications_bcx --成品 BCX
        ,lens_specifications_bcy = @lens_specifications_bcy--成品 BCY
        ,lens_specifications_ct = @lens_specifications_ct--成品 CT
        ,lens_specificatons_tc = @lens_specificatons_tc--成品 TC
        ,lens_specifications_dia = @lens_specifications_dia--成品 DIA
        ,lens_specifications_power = @lens_specifications_power--成品 POWER
        ,lens_specifications_powerx = @lens_specifications_powerx--成品 POWERX
        ,lens_specifications_powery = @lens_specifications_powery--成品 POWERY
        ,lens_semi_specifications_bc = @lens_semi_specifications_bc--半成品 BC
        ,lens_semi_specifications_bcx = @lens_semi_specifications_bcx--半成品 BCX
        ,lens_semi_specifications_bcy = @lens_semi_specifications_bcy--半成品 BCY
        ,lens_semi_specifications_ct = @lens_semi_specifications_ct--半成品 CT
        ,lens_semi_specifications_tc = @lens_semi_specifications_tc--半成品 TC
        ,update_dt = GETDATE()
        ,[user_id] = @user_id
    WHERE   
        rec_id = @rec_id
	
    SELECT SCOPE_IDENTITY () AS RESULT
GO
/****** Object:  StoredProcedure [dbo].[sp_BTOX_material_info_list]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: BTOX 顏色
	OODA_RESULT: RECORDSET
*/
/*
	修改歷程
	時間				修正人		Mark
	 20727			Kaid		Create
*/
CREATE PROCEDURE [dbo].[sp_BTOX_material_info_list] 
AS
	SELECT
		color
	FROM
		btox.material_info
GO
/****** Object:  StoredProcedure [dbo].[sp_mes_runtask_checkout]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: Runtask check-out
	OODA_RESULT: SCALAR
*/
/*
	修改歷程
	時間			修正人		Mark
	260526		Kaid		CREATE
*/
CREATE PROCEDURE [dbo].[sp_mes_runtask_checkout]
	@rcid				BIGINT,
	@mpoid				BIGINT,
	@checkout_qty		DECIMAL(19,4),
	@scrap_qty			DECIMAL(19,4),
	@update_dt			DATETIME,
	@update_uid			SMALLINT
AS
	DECLARE @state	BIGINT = -1
	DECLARE @mpcid	BIGINT = -1
	DECLARE @oseq	INT = -1
	DECLARE @eqpid	INT = NULL
	DECLARE @opid	BIGINT

	SELECT @mpcid = mpcid, @oseq = oseq, @opid = oactid FROM dbo.mpo WHERE mpoid = @mpoid AND rtsteid = 51
	SELECT @state = rtid FROM dbo.rt WHERE mpoid = @mpoid AND rtsteid = 51
	SELECT @eqpid = eqpid FROM dbo.rteqp WHERE rtid = @state

	IF(@state > 0)
	BEGIN
		UPDATE dbo.mpo
		SET
			rtsteid = 57, 
			update_uid = @update_uid,
			update_dt = @update_dt
		WHERE
			mpoid = @mpoid

		UPDATE dbo.rt
		SET
			rtsteid = 57, 
			checkout_qty = @checkout_qty,
			scrap_qty = @scrap_qty,
			oenduid = @update_uid,
			oenddt = @update_dt,
			update_uid = @update_uid,
			update_dt = @update_dt
		WHERE
			rtid = @state
		
		-- Kaid : 最後一站直接工單結案
		IF NOT EXISTS(SELECT COUNT(*) FROM dbo.mpo WHERE mpcid = @mpcid AND oseq > @oseq)
		BEGIN
			UPDATE dbo.rc
			SET
				rcsteid = 6,
				prod_end_dt = @update_dt,
				update_uid = @update_uid,
				update_dt = @update_dt
			WHERE
				rcid = @rcid
		END

		IF EXISTS (SELECT COUNT(*) FROM dbo.rteqp WHERE rtid = @state)
		BEGIN
			INSERT INTO dbo.eqpstlg
				SELECT TOP(1)
					eqpid, 5, @rcid, @state, 'sysAction', @update_uid, @update_dt
				FROM
					dbo.rteqp 
				WHERE 
					rtid = @state

			UPDATE dbo.eqp
				SET
					eqpsid = 5,
					update_dt = @update_dt,
					update_uid = @update_uid
				WHERE
					eqpid = @eqpid
		END

		DECLARE @is_on			BIT
		DECLARE @mold_log_id	BIGINT

		DECLARE @cur_moldid	INT 

		DECLARE mdo CURSOR FOR
			SELECT
				moldid
			FROM
				dbo.map_op_mold INNER JOIN
				dbo.map_op_eqp ON dbo.map_op_eqp.eqpid = dbo.map_op_mold.eqpid AND dbo.map_op_eqp.opid = dbo.map_op_mold.opid
			WHERE
				dbo.map_op_eqp.eqpid = @eqpid AND dbo.map_op_mold.opid = @opid
		OPEN mdo
		FETCH NEXT FROM mdo INTO @cur_moldid
		WHILE(@@FETCH_STATUS = 0)
		BEGIN
			SELECT TOP(1) @mold_log_id = recid, @is_on = is_on FROM dbo.mold_bind_log WHERE eqpid = @eqpid AND moldid = @cur_moldid ORDER BY create_dt DESC

			IF(@is_on = 'True')
			BEGIN
				UPDATE dbo.mold_bind_log
				SET
					usage_count = usage_count + 1
				WHERE	
					recid = @mold_log_id

		END

			FETCH NEXT FROM mdo INTO @cur_moldid
		END
		CLOSE mdo
		DEALLOCATE mdo

		
	END

	SELECT @state AS result
GO
/****** Object:  StoredProcedure [dbo].[sp_web_department_create]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: create control_chart (return id)
	OODA_RESULT: SCALAR
*/
/*
	修改歷程
	時間			修正人		Mark
	20250106		Jade		Create
*/
CREATE PROCEDURE [dbo].[sp_web_department_create] 
	@department_no		nvarchar(50),
	@department_name	nvarchar(50),
	@update_by			smallint

AS
	INSERT INTO dbo.dept
		(deptno,deptname, pltid, is_delete, update_dt, update_uid)
	VALUES
		(@department_no,@department_name, NULL, 0, GETDATE(), @update_by)

	SELECT @@IDENTITY  AS RESULT
GO
/****** Object:  StoredProcedure [dbo].[sp_web_department_delete]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: create control_chart (return id)
	OODA_RESULT: SCALAR
*/
CREATE PROCEDURE [dbo].[sp_web_department_delete] 
	@deptid		smallint,
	@update_by	smallint
AS
	UPDATE dbo.dept
	SET 
		is_delete = 1,
		update_dt = GETDATE(),
		update_uid = @update_by
	WHERE
		deptid = @deptid

GO
/****** Object:  StoredProcedure [dbo].[sp_web_department_info_by_id]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
 OODA_REMARK: 
 OODA_RESULT: RECORDSET
*/

/*
	修改歷程
	時間			修正人		Mark
	2025/01/06		Jade		CREATE
*/

CREATE PROCEDURE [dbo].[sp_web_department_info_by_id]

    @deptid		SMALLINT

AS
	SELECT 
		deptid, deptno, deptname
	FROM 
		dbo.dept
	WHERE 
		dbo.dept.deptid = @deptid
GO
/****** Object:  StoredProcedure [dbo].[sp_web_department_list]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: GET department list
	OODA_RESULT: RECORDSET
*/
/*
	修改歷程
	時間			修正人		Mark
	20250106		Jade		Create
*/
CREATE PROCEDURE [dbo].[sp_web_department_list]

AS

		SELECT
			dbo.dept.deptid,
			deptno,
			deptname,
			dbo.dept.update_dt,
			username
		FROM 
			dbo.dept INNER JOIN
			dbo.[user] ON dbo.[user].[uid] = dbo.dept.update_uid
		WHERE
			dbo.dept.is_delete = 0 
GO
/****** Object:  StoredProcedure [dbo].[sp_web_department_option_list]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: sp_web_department_all_list
	OODA_RESULT: RECORDSET
*/
/*
	修改歷程
	時間			修正人		Mark
	 060428		Kaid		Create
*/
CREATE PROCEDURE [dbo].[sp_web_department_option_list] 
AS
	SELECT          
		deptid, deptno, deptname
	FROM              
		dbo.dept
	WHERE
		is_delete = 0
GO
/****** Object:  StoredProcedure [dbo].[sp_web_department_update]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: create control_chart (return id)
	OODA_RESULT: SCALAR
*/
/*
	修改歷程
	時間			修正人		Mark
	20250106		Jade		Create
*/
CREATE PROCEDURE [dbo].[sp_web_department_update] 
	@deptid				smallint,
	@department_name	nvarchar(50),
	@update_by			smallint
AS
	UPDATE dbo.dept
	SET 
		deptname = @department_name,
		update_dt = GETDATE(),
		update_uid = @update_by
	WHERE
		deptid = @deptid

GO
/****** Object:  StoredProcedure [dbo].[sp_web_equip_option_list_by_state]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: WEB設備主檔List (option)
	OODA_RESULT: RECORDSET
*/
/*
	修改歷程
	時間				修正人		Mark
	 260507			Kaid		Create
*/
CREATE PROCEDURE [dbo].[sp_web_equip_option_list_by_state] 
	@eqpsid		SMALLINT = NULL
AS
	SELECT 
		eqpid, eqpno, eqpname
	FROM 
		dbo.eqp 
	WHERE 
		is_deleted = 0 AND (dbo.eqp.eqpsid = @eqpsid OR @eqpsid IS NULL)
	
GO
/****** Object:  StoredProcedure [dbo].[sp_web_equip_state_log_list]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: 設備變更紀錄 (前5筆)
	OODA_RESULT: RECORDSET
*/
/*
	修改歷程
	時間				修正人		Mark
	 260508			Kaid		Create
*/
CREATE PROCEDURE [dbo].[sp_web_equip_state_log_list] 
	@eqpid		INT,
	@data_count	INT = 5
AS
	SELECT
		dbo.ceqps.eqpsno, dbo.ceqps.eqpsname, logTb.remark, username, logTb.create_dt, IIF(btox.erp_info.runcard_no IS NULL, '', btox.erp_info.runcard_no) AS erp_no
	FROM
		(
			SELECT TOP(@data_count)
				eqpid, eqpsid, rcid, rtid, remark, create_by, create_dt
			FROM
				dbo.eqpstlg
			WHERE
				eqpid = @eqpid
			ORDER BY create_dt DESC
		) AS logTb INNER JOIN 
		dbo.ceqps  ON dbo.ceqps.eqpsid = logTb.eqpsid INNER JOIN
		dbo.[user] ON dbo.[user].[uid] = logTb.create_by LEFT JOIN
		dbo.rc ON dbo.rc.rcid = logTb.rcid  LEFT JOIN
		btox.erp_info ON btox.erp_info.runcard_id = dbo.rc.rcid
GO
/****** Object:  StoredProcedure [dbo].[sp_web_maintain_alarm_create]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: 新增維修區域 (return id)
	OODA_RESULT: SCALAR
*/
CREATE PROCEDURE [dbo].[sp_web_maintain_alarm_create] 
	@alarmno			NVARCHAR(50),
	@alarmname		NVARCHAR(60)
AS
	DECLARE @state BIGINT = -1 

	SELECT @state = alarmid FROM dbo.calarm WHERE alarmno = @alarmno

	IF @state < 0
	BEGIN
		INSERT INTO dbo.calarm
			(alarmno, alarmname)
		VALUES
			(@alarmno, @alarmname)

		SELECT @state = SCOPE_IDENTITY()
	END
	ELSE
	BEGIN
		SET @state = -1
	END
	
	SELECT @state AS RESULT
GO
/****** Object:  StoredProcedure [dbo].[sp_web_maintain_alarm_delete]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: 刪除維修區域 (return id)
	OODA_RESULT: SCALAR
*/
CREATE PROCEDURE [dbo].[sp_web_maintain_alarm_delete] 
	@alarmid				INT,
	@update_uid			smallint
AS
	DECLARE @state BIGINT = -1 

	SELECT @state = alarmid FROM dbo.calarm WHERE alarmid = @alarmid

	IF @state > 0
	BEGIN
		UPDATE dbo.calarm
		SET
			is_deleted = 1
		WHERE
			alarmid = @alarmid
	END

	SELECT @state AS result
GO
/****** Object:  StoredProcedure [dbo].[sp_web_maintain_alarm_get]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: web part list 產品主檔列表
	OODA_RESULT: RECORDSET
*/

/*

	修改歷程
	時間				修正人		Mark
	2026/04/29			Kaid		CREATE
*/

CREATE PROCEDURE [dbo].[sp_web_maintain_alarm_get] 
	@alarmid				INT
AS		
	SELECT          
		alarmid, alarmno, alarmname
	FROM              
		dbo.calarm
	WHERE          
		alarmid = @alarmid
GO
/****** Object:  StoredProcedure [dbo].[sp_web_maintain_alarm_list]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: web part list 產品主檔列表
	OODA_RESULT: RECORDSET
*/

/*

	修改歷程
	時間				修正人		Mark
	2026/04/29			Kaid		CREATE
*/

CREATE PROCEDURE [dbo].[sp_web_maintain_alarm_list] 

AS		
	SELECT          
		alarmid, alarmno, alarmname, is_system
	FROM              
		dbo.calarm
	WHERE          
		is_deleted = 0 
GO
/****** Object:  StoredProcedure [dbo].[sp_web_maintain_alarm_update]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: 更新維修區域 (return id)
	OODA_RESULT: SCALAR
*/
CREATE PROCEDURE [dbo].[sp_web_maintain_alarm_update] 
	@alarmid			INT,
	@alarmname		nvarchar(50)
AS
	DECLARE @state BIGINT = -1
	
	SELECT @state = alarmid FROM dbo.calarm WHERE alarmid = @alarmid
	
	IF @state > 0
	BEGIN
		UPDATE dbo.calarm
		SET
			alarmname = @alarmname
		WHERE
			alarmid = @alarmid	
	END

	SELECT @state AS RESULT
GO
/****** Object:  StoredProcedure [dbo].[sp_web_maintain_area_create]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: 新增維修區域 (return id)
	OODA_RESULT: SCALAR
*/
CREATE PROCEDURE [dbo].[sp_web_maintain_area_create] 
	@mtano			NVARCHAR(50),
	@mtaname		NVARCHAR(60),
	@remark			NVARCHAR(MAX) = NULL,
	@update_uid		SMALLINT
AS
	DECLARE @state BIGINT = -1 

	SELECT @state = mtaid FROM dbo.cmta WHERE mtano = @mtano

	IF @state < 0
	BEGIN
		INSERT INTO dbo.cmta
			(mtano, mtaname, remark)
		VALUES
			(@mtano, @mtaname, @remark)

		SELECT @state = SCOPE_IDENTITY()
	END
	ELSE
	BEGIN
		SET @state = -1
	END
	
	SELECT @state AS RESULT
GO
/****** Object:  StoredProcedure [dbo].[sp_web_maintain_area_delete]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: 刪除維修區域 (return id)
	OODA_RESULT: SCALAR
*/
CREATE PROCEDURE [dbo].[sp_web_maintain_area_delete] 
	@mtaid				INT,
	@update_uid			smallint
AS
	DECLARE @state BIGINT = -1 

	SELECT @state = mtaid FROM dbo.cmta WHERE mtaid = @mtaid

	IF @state > 0
	BEGIN
		UPDATE dbo.cmta
		SET
			is_deleted = 1
		WHERE
			mtaid = @mtaid
	END

	SELECT @state AS result
GO
/****** Object:  StoredProcedure [dbo].[sp_web_maintain_area_get]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: web part list 產品主檔列表
	OODA_RESULT: RECORDSET
*/

/*

	修改歷程
	時間				修正人		Mark
	2026/04/29			Kaid		CREATE
*/

CREATE PROCEDURE [dbo].[sp_web_maintain_area_get] 
	@mtaid				INT
AS		
	SELECT          
		mtaid, mtano, mtaname
	FROM              
		dbo.cmta
	WHERE          
		mtaid = @mtaid
GO
/****** Object:  StoredProcedure [dbo].[sp_web_maintain_area_list]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: web part list 產品主檔列表
	OODA_RESULT: RECORDSET
*/

/*

	修改歷程
	時間				修正人		Mark
	2026/04/29			Kaid		CREATE
*/

CREATE PROCEDURE [dbo].[sp_web_maintain_area_list] 

AS		
	SELECT          
		mtaid, mtano, mtaname
	FROM              
		dbo.cmta
	WHERE          
		is_deleted = 0 
GO
/****** Object:  StoredProcedure [dbo].[sp_web_maintain_area_update]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: 更新維修區域 (return id)
	OODA_RESULT: SCALAR
*/
CREATE PROCEDURE [dbo].[sp_web_maintain_area_update] 
	@mtaid			INT,
	@mtaname		nvarchar(50),
	@remark			NVARCHAR(MAX) = NULL
AS
	DECLARE @state BIGINT = -1
	
	SELECT @state = mtaid FROM dbo.cmta WHERE mtaid = @mtaid
	
	IF @state > 0
	BEGIN
		UPDATE dbo.cmta
		SET
			mtaname = @mtaname,
			remark = @remark
		WHERE
			mtaid = @mtaid	
	END

	SELECT @state AS RESULT
GO
/****** Object:  StoredProcedure [dbo].[sp_web_maintain_reason_create]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: 新增維修原因 (return id)
	OODA_RESULT: SCALAR
*/
CREATE PROCEDURE [dbo].[sp_web_maintain_reason_create] 
	@mtresno		NVARCHAR(50),
	@mtresname		NVARCHAR(60)
AS
	DECLARE @state BIGINT = -1 

	SELECT @state = mtresid FROM dbo.cmtres WHERE mtresno = @mtresno

	IF @state < 0
	BEGIN
		INSERT INTO dbo.cmtres
			(mtresno, mtresname)
		VALUES
			(@mtresno, @mtresname)

		SELECT @state = SCOPE_IDENTITY()
	END
	ELSE
	BEGIN
		SET @state = -1
	END
	
	SELECT @state AS RESULT
GO
/****** Object:  StoredProcedure [dbo].[sp_web_maintain_reason_delete]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: 刪除維修區域 (return id)
	OODA_RESULT: SCALAR
*/
CREATE PROCEDURE [dbo].[sp_web_maintain_reason_delete] 
	@mtresid			INT,
	@update_uid			smallint
AS
	DECLARE @state BIGINT = -1 

	SELECT @state = mtresid FROM dbo.cmtres WHERE mtresid = @mtresid

	IF @state > 0
	BEGIN
		UPDATE dbo.cmtres
		SET
			is_deleted = 1
		WHERE
			mtresid = @mtresid
	END

	SELECT @state AS result
GO
/****** Object:  StoredProcedure [dbo].[sp_web_maintain_reason_get]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: web part list 產品主檔列表
	OODA_RESULT: RECORDSET
*/

/*

	修改歷程
	時間				修正人		Mark
	2026/04/29			Kaid		CREATE
*/

CREATE PROCEDURE [dbo].[sp_web_maintain_reason_get] 
	@mtresid				INT
AS		
	SELECT          
		mtresid, mtresno, mtresname
	FROM              
		dbo.cmtres
	WHERE          
		mtresid = @mtresid
GO
/****** Object:  StoredProcedure [dbo].[sp_web_maintain_reason_list]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: web part list 產品主檔列表
	OODA_RESULT: RECORDSET
*/

/*

	修改歷程
	時間				修正人		Mark
	2026/04/29			Kaid		CREATE
*/

CREATE PROCEDURE [dbo].[sp_web_maintain_reason_list] 

AS		
	SELECT          
		mtresid, mtresno, mtresname
	FROM              
		dbo.cmtres
	WHERE          
		is_deleted = 0 
GO
/****** Object:  StoredProcedure [dbo].[sp_web_maintain_reason_update]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: 更新維修區域 (return id)
	OODA_RESULT: SCALAR
*/
CREATE PROCEDURE [dbo].[sp_web_maintain_reason_update] 
	@mtresid		INT,
	@mtresname		nvarchar(50),
	@remark			NVARCHAR(MAX) = NULL
AS
	DECLARE @state BIGINT = -1
	
	SELECT @state = mtresid FROM dbo.cmtres WHERE mtresid = @mtresid
	
	IF @state > 0
	BEGIN
		UPDATE dbo.cmtres
		SET
			mtresname = @mtresname
		WHERE
			mtresid = @mtresid
	END

	SELECT @state AS RESULT
GO
/****** Object:  StoredProcedure [dbo].[sp_web_mold_list]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: 模具新增
	OODA_RESULT: RECORDSET
*/
/*
	修改歷程
	時間				修正人		Mark
	 260507			Kaid		Create
*/
CREATE PROCEDURE [dbo].[sp_web_mold_list] 
	@fctid		TINYINT = NULL,
	@moldtid	TINYINT = NULL
AS
	SELECT DISTINCT
		dbo.mold.moldid, dbo.cmoldt.moldtid, dbo.cfct.fctid, dbo.cmolds.moldsid, dbo.cmoldbinds.moldbindsid,
		dbo.cfct.fctname, dbo.cmoldt.moldtname, dbo.mold.moldno, dbo.mold.moldname,
		dbo.cmolds.mdsname, dbo.cmoldbinds.moldbindsname, dbo.mold.remark, dbo.mold.update_dt, dbo.[user].username,
		dbo.eqp.eqpid, eqpno, eqpname
	FROM
		dbo.mold INNER JOIN
		dbo.cmoldbinds ON dbo.mold.moldbindsid = dbo.cmoldbinds.moldbindsid INNER JOIN
		dbo.cmolds ON dbo.cmolds.moldsid = dbo.mold.moldsid INNER JOIN
		dbo.cfct ON dbo.cfct.fctid = dbo.mold.fctid INNER JOIN
		dbo.cmoldt ON dbo.cmoldt.moldtid = dbo.mold.moldtid INNER JOIN
		dbo.[user] ON dbo.[user].[uid] = dbo.mold.update_uid LEFT JOIN
		dbo.map_op_mold ON dbo.map_op_mold.moldid = dbo.mold.moldid LEFT JOIN
		dbo.map_op_eqp ON dbo.map_op_eqp.eqpid = dbo.map_op_mold.eqpid LEFT JOIN
		dbo.eqp ON dbo.eqp.eqpid = dbo.map_op_eqp.eqpid
	WHERE
		(dbo.mold.fctid = @fctid OR @fctid IS NULL) AND (dbo.mold.moldtid = @moldtid OR @moldtid IS NULL)
		AND mold.is_deleted = 0
GO


-------------------------------------------------------------------ALTER--------------------------------------------------------------------------------------------------
/****** Object:  StoredProcedure [dbo].[sp_app_equip_maintain_colse]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: APP 維修單 close
	OODA_RESULT: SCALAR
*/
/*
	修改歷程
	時間				修正人		Mark
	 260625			Kaid		Create
*/
ALTER PROCEDURE [dbo].[sp_app_equip_maintain_colse] 
	@mt_event_id	BIGINT,
	@mtsid			INT,
	@fs_arr			NVARCHAR(MAX) = NULL,
	@remark			NVARCHAR(50) = NULL,
	@update_uid		SMALLINT
AS
	DECLARE @eqpid			INT
	DECLARE @event_log_id	BIGINT = -1
	DECLARE @now		DATETIME = GETDATE()

	SELECT @eqpid = eqpid FROM dbo.maintenance_event_log WHERE mt_event_id = @mt_event_id

	UPDATE dbo.maintenance_event
	SET
		mtsid = @mtsid,
		end_dt = @now,
		end_uid = @update_uid
	WHERE	
		mt_event_id = @mt_event_id

	INSERT INTO dbo.maintenance_event_log
		(mt_event_id, mtsid, eqpid, remark, mt_dt, create_dt, create_uid)
	VALUES
		(@mt_event_id, @mtsid, @eqpid, @remark, @now, @now, @update_uid)

	SELECT @event_log_id = SCOPE_IDENTITY()

	IF(@fs_arr IS NOT NULL)
	BEGIN
		INSERT INTO dbo.maintenance_fs
			SELECT
				@event_log_id, value
			FROM
				string_split(@fs_arr, ',')
	END

	UPDATE dbo.eqp
	SET
		eqpsid = 5,
		update_dt = GETDATE(),
		update_uid = @update_uid
	WHERE
		eqpid = @eqpid 

	SELECT @event_log_id AS RESULT
GO
/****** Object:  StoredProcedure [dbo].[sp_app_mold_bind_update]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: 更新設備上下刀狀態
	OODA_RESULT: SCALAR
*/
/*
	修改歷程
	時間				修正人		Mark
	 260618			Kaid		Create
*/
ALTER PROCEDURE [dbo].[sp_app_mold_bind_update] 
	@moldid			INT,
	@eqpid			INT,
	@moldsid		TINYINT,
	@moldbindsid	TINYINT,
	@opid			INT = NULL,
	@usage_count	INT = NULL,
	@fsid			NVARCHAR(50) = NULL,
	@remark			NVARCHAR(50) = NULL,
	@change_dt		DATETIME,
	@update_uid		SMALLINT
AS
	DECLARE @state		INT = -99
	DECLARE @check_mold	INT = -1
	DECLARE @check_eqp	INT = -1
	DECLARE @is_on		BIT 
	DECLARE @check_op	INT = -1
	DECLARE @moldtid	TINYINT = 0

	SELECT @moldtid = moldtid FROM dbo.mold WHERE moldid = @moldid

	SELECT TOP(1) @is_on = is_on FROM dbo.mold_bind_log WHERE eqpid = @eqpid ORDER BY create_dt DESC
	SELECT @check_mold = moldid, @state = moldbindsid FROM dbo.mold WHERE moldid = @moldid AND is_deleted = 0
	SELECT @check_eqp = eqpid FROM dbo.eqp WHERE @eqpid = @eqpid AND is_deleted = 0
	SELECT @check_op = opid FROM dbo.map_op_eqp WHERE eqpid = @eqpid  AND (opid = @opid OR @opid is NULL)

	IF EXISTS (SELECT * FROM dbo.mold WHERE moldtid = @moldtid AND moldid IN (SELECT moldid FROM dbo.map_op_mold WHERE eqpid = @eqpid))
	BEGIN
		SET @state = -1 -- 機台已綁同類型刀具
	END
	ELSE IF (@check_mold < 0 )
	BEGIN
		SET	@state = -2 -- 找不到模具
	END
	ELSE IF (@check_eqp < 0)
	BEGIN
		SET @state = -3 -- 找不到機台
	END
	ELSE IF (@state <> 1 AND @moldbindsid = 2)
	BEGIN
		SET @state = -4 -- 模具狀態不能綁定
	END
	ELSE IF(@check_op <= 0)
	BEGIN
		SET @state = -5 -- 機台未綁站點
	END
	ELSE
	BEGIN
		UPDATE dbo.mold
		SET
			
			moldsid = @moldsid,
			moldbindsid = @moldbindsid,
			update_dt = GETDATE(),
			update_uid = @update_uid
		WHERE
			moldid = @moldid

		IF(@moldsid = 4 AND @moldbindsid = 2) -- 上刀
		BEGIN
			INSERT INTO dbo.mold_bind_log
				(eqpid, moldid, usage_count, moldsid, moldtid, is_on, fsid, remark, [uid], change_dt, create_dt, create_uid)
			VALUES
				(@eqpid, @moldid, @usage_count, @moldsid, @moldtid, 1, @fsid, @remark, @update_uid, GETDATE(), GETDATE(), @update_uid)

			INSERT INTO dbo.map_op_mold
				(moldid, opid, eqpid)
			VALUES
				(@moldid, @check_op, @eqpid)

			SET @state = 1		
		END
		ELSE -- 下刀 損換、送修...都算下刀
		BEGIN
			IF(@usage_count IS NULL OR @usage_count = 0)
			BEGIN
				SELECT TOP(1) @usage_count = usage_count FROM dbo.mold_bind_log WHERE is_on = 1 AND eqpid = @eqpid AND moldid = @moldid ORDER BY create_dt DESC
			END

			INSERT INTO dbo.mold_bind_log
				(eqpid, moldid, usage_count, moldsid, moldtid, is_on, fsid, remark, [uid], change_dt, create_dt, create_uid)
			VALUES
				(@eqpid, @moldid, @usage_count, @moldsid, @moldtid, 0, @fsid, @remark, @update_uid, @change_dt, GETDATE(), @update_uid)

			DELETE FROM dbo.map_op_mold
			WHERE moldid = @moldid AND eqpid = @eqpid
				
			SET @state = 1				

		END
	END

	SELECT @state AS RESULT
GO
/****** Object:  StoredProcedure [dbo].[sp_app_mpoeqp_list_BTOX]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: 拿可用機台
	OODA_RESULT: RECORDSET
*/
/*
	修改歷程
	時間			修正人		Mark
	 260525			Kaid		CREATE
*/
ALTER PROCEDURE [dbo].[sp_app_mpoeqp_list_BTOX]
	@oactid		INT,
	@apteid		SMALLINT
AS
	SELECT DISTINCT
		dbo.mpoeqp.eqpid, eqpno, eqpname, IIF(dbo.iot.iot_id IS NULL , 'False', 'True') AS is_binded 
	FROM
		dbo.mpoeqp INNER JOIN
		dbo.eqp ON dbo.eqp.eqpid = dbo.mpoeqp.eqpid LEFT JOIN
		dbo.iot ON dbo.iot.eqpid = dbo.eqp.eqpid
	WHERE
		mpoid IN (SELECT mpoid FROM dbo.mpo WHERE oactid = @oactid AND apteid = @apteid) AND dbo.eqp.eqpsid IN (1, 2, 5)
GO
/****** Object:  StoredProcedure [dbo].[sp_app_sys_role_funcode_list]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: 權限角色權限清單
	OODA_RESULT: RECORDSET
*/
ALTER PROCEDURE [dbo].[sp_app_sys_role_funcode_list] 
	@rid		SMALLINT = -1
AS

	DECLARE @roleFuncode TABLE
	(
		funcode_id	SMALLINT,
		is_auth		BIT,
		has_write	BIT
	)

	IF @rid = -1
	BEGIN
		SELECT          
			fncid, fncno AS fncname, isapp, isweb, 'FALSE' AS is_auth, 'FALSE' AS has_write
		FROM              
			dbo.cfnc
		WHERE
			isapp <> 0 OR isweb <> 0
	END
	ELSE
	BEGIN
		INSERT INTO @roleFuncode
			SELECT fcid, 1, has_write FROM dbo.sysrfc WHERE rid = @rid

		SELECT          
			fncid, fncno AS fncname, isapp, isweb, ISNULL(roleTb.is_auth, 'FALSE') AS is_auth, has_write
		FROM              
			dbo.cfnc LEFT JOIN 
			@roleFuncode AS roleTb ON roleTb.funcode_id = dbo.cfnc.fncid
		WHERE
			isapp <> 0 OR isweb <> 0
		ORDER BY isapp, funcode_id
	END
GO
/****** Object:  StoredProcedure [dbo].[sp_BTOX_erp_info_create]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: BTOX 顏色
	OODA_RESULT: SCALAR
*/
/*
	修改歷程
	時間				修正人		Mark
	 20727			Kaid		Create
*/
ALTER PROCEDURE [dbo].[sp_BTOX_erp_info_create] 
    @runcard_no                     NVARCHAR(50) = null
   ,@customer                       NVARCHAR(50) = null
   ,@material_no                    NVARCHAR(50) = null
   ,@color                          NVARCHAR(50) = null
   ,@plasma                         NVARCHAR(50) = null
   ,@reich                          NVARCHAR(50) = null
   ,@remark                         NVARCHAR(50) = null
   ,@reich2                         NVARCHAR(50) = null
   ,@lot_no                         NVARCHAR(50) = null
   ,@lens_specifications_bc         FLOAT = NULL--成品 BC
   ,@lens_specifications_bcx        FLOAT = NULL--成品 BCX
   ,@lens_specifications_bcy        FLOAT = NULL--成品 BCY
   ,@lens_specifications_ct         FLOAT = NULL--成品 CT
   ,@lens_specificatons_tc          FLOAT = NULL--成品 TC
   ,@lens_specifications_dia        FLOAT = NULL--成品 DIA
   ,@lens_specifications_power      FLOAT = NULL--成品 POWER
   ,@lens_specifications_powerx     FLOAT = NULL--成品 POWERX
   ,@lens_specifications_powery     FLOAT = NULL--成品 POWERY
   ,@lens_semi_specifications_bc    FLOAT = NULL--半成品 BC
   ,@lens_semi_specifications_bcx   FLOAT = NULL--半成品 BCX
   ,@lens_semi_specifications_bcy   FLOAT = NULL--半成品 BCY
   ,@lens_semi_specifications_ct    FLOAT = NULL--半成品 CT
   ,@lens_semi_specifications_tc    FLOAT = NULL--半成品 TC
   ,@user_id                        INT = NULL
   ,@runcard_id                     BIGINT
AS
    INSERT INTO btox.erp_info
        (
         runcard_id 
        ,runcard_no
        ,customer
        ,material_no
        ,color--下拉選單
        ,plasma
        ,reich
        ,remark
        ,reich2
        ,lot_no
        ,lens_specifications_bc--成品 BC
        ,lens_specifications_bcx--成品 BCX
        ,lens_specifications_bcy--成品 BCY
        ,lens_specifications_ct--成品 CT
        ,lens_specificatons_tc--成品 TC
        ,lens_specifications_dia--成品 DIA
        ,lens_specifications_power--成品 POWER
        ,lens_specifications_powerx--成品 POWERX
        ,lens_specifications_powery--成品 POWERY
        ,lens_semi_specifications_bc--半成品 BC
        ,lens_semi_specifications_bcx--半成品 BCX
        ,lens_semi_specifications_bcy--半成品 BCY
        ,lens_semi_specifications_ct--半成品 CT
        ,lens_semi_specifications_tc--半成品 TC
        ,create_dt
        ,update_dt
        ,[user_id] 
        )
    VALUES
        (
         @runcard_id
        ,@runcard_no
        ,@customer
        ,@material_no
        ,@color--下拉選單
        ,@plasma
        ,@reich
        ,@remark
        ,@reich2
        ,@lot_no
        ,@lens_specifications_bc--成品 BC
        ,@lens_specifications_bcx--成品 BCX
        ,@lens_specifications_bcy--成品 BCY
        ,@lens_specifications_ct--成品 CT
        ,@lens_specificatons_tc--成品 TC
        ,@lens_specifications_dia--成品 DIA
        ,@lens_specifications_power--成品 POWER
        ,@lens_specifications_powerx--成品 POWERX
        ,@lens_specifications_powery--成品 POWERY
        ,@lens_semi_specifications_bc--半成品 BC
        ,@lens_semi_specifications_bcx--半成品 BCX
        ,@lens_semi_specifications_bcy--半成品 BCY
        ,@lens_semi_specifications_ct--半成品 CT
        ,@lens_semi_specifications_tc--半成品 TC
        ,GETDATE()
        ,GETDATE()
        ,@user_id
        )
	
    SELECT SCOPE_IDENTITY () AS RESULT
GO
/****** Object:  StoredProcedure [dbo].[sp_BTOX_erp_info_get]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: BTOX 顏色
	OODA_RESULT: RECORDSET
*/
/*
	修改歷程
	時間				修正人		Mark
	 20727			Kaid		Create
*/
ALTER PROCEDURE [dbo].[sp_BTOX_erp_info_get] 
    @rec_id       BIGINT
AS
	SELECT
		[rec_id]
      ,[runcard_id]
      ,[runcard_no]
      ,[customer]
      ,[material_no]
      ,[color]--下拉選單
      ,[plasma]
      ,[reich]
      ,[remark]
      ,[reich2]
      ,[lot_no]
      ,[lens_specifications_bc]--成品 BC
      ,[lens_specifications_bcx]--成品 BCX
      ,[lens_specifications_bcy]--成品 BCY
      ,[lens_specifications_ct]--成品 CT
      ,[lens_specificatons_tc]--成品 TC
      ,[lens_specifications_dia]--成品 DIA
      ,[lens_specifications_power]--成品 POWER
      ,[lens_specifications_powerx]--成品 POWERX
      ,[lens_specifications_powery]--成品 POWERY
      ,[lens_semi_specifications_bc]--半成品 BC
      ,[lens_semi_specifications_bcx]--半成品 BCX
      ,[lens_semi_specifications_bcy]--半成品 BCY
      ,[lens_semi_specifications_ct]--半成品 CT
      ,[lens_semi_specifications_tc]--半成品 TC
      ,[create_dt]
      ,[update_dt]
	FROM
		(
			SELECT
				rcid
			FROM
				dbo.rc
			WHERE
				rcsteid <> 6
		) AS rcTb INNER JOIN
        (
            SELECT
                [rec_id]
            ,[runcard_id]
            ,[runcard_no]
            ,[customer]
            ,[material_no]
            ,[color]--下拉選單
            ,[plasma]
            ,[reich]
            ,[remark]
            ,[reich2]
            ,[lot_no]
            ,[lens_specifications_bc]--成品 BC
            ,[lens_specifications_bcx]--成品 BCX
            ,[lens_specifications_bcy]--成品 BCY
            ,[lens_specifications_ct]--成品 CT
            ,[lens_specificatons_tc]--成品 TC
            ,[lens_specifications_dia]--成品 DIA
            ,[lens_specifications_power]--成品 POWER
            ,[lens_specifications_powerx]--成品 POWERX
            ,[lens_specifications_powery]--成品 POWERY
            ,[lens_semi_specifications_bc]--半成品 BC
            ,[lens_semi_specifications_bcx]--半成品 BCX
            ,[lens_semi_specifications_bcy]--半成品 BCY
            ,[lens_semi_specifications_ct]--半成品 CT
            ,[lens_semi_specifications_tc]--半成品 TC
            ,[create_dt]
            ,[update_dt]
            FROM
                btox.erp_info
            WHERE
                rec_id = @rec_id
        ) AS erpTb ON erpTb.runcard_id = rcTb.rcid
GO
/****** Object:  StoredProcedure [dbo].[sp_BTOX_erp_info_list]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: BTOX 顏色
	OODA_RESULT: RECORDSET
*/
/*
	修改歷程
	時間				修正人		Mark
	 20727			Kaid		Create
*/
ALTER PROCEDURE [dbo].[sp_BTOX_erp_info_list] 
    @erp_order_no       NVArCHAR(50) = NULL
AS
    IF (@erp_order_no IS NULL) SET @erp_order_no = ''

	SELECT
		[rec_id]
      ,[runcard_id]
      ,[runcard_no]
      ,[customer]
      ,[material_no]
      ,[color]--下拉選單
      ,[plasma]
      ,[reich]
      ,erpTb.[remark]
      ,[reich2]
      ,[lot_no]
      ,[lens_specifications_bc]--成品 BC
      ,[lens_specifications_bcx]--成品 BCX
      ,[lens_specifications_bcy]--成品 BCY
      ,[lens_specifications_ct]--成品 CT
      ,[lens_specificatons_tc]--成品 TC
      ,[lens_specifications_dia]--成品 DIA
      ,[lens_specifications_power]--成品 POWER
      ,[lens_specifications_powerx]--成品 POWERX
      ,[lens_specifications_powery]--成品 POWERY
      ,[lens_semi_specifications_bc]--半成品 BC
      ,[lens_semi_specifications_bcx]--半成品 BCX
      ,[lens_semi_specifications_bcy]--半成品 BCY
      ,[lens_semi_specifications_ct]--半成品 CT
      ,[lens_semi_specifications_tc]--半成品 TC
      ,[create_dt]
      ,erpTb.[update_dt]
      ,ptid
      ,dbo.p.pid
	FROM
		(
			SELECT
				rcid
			FROM
				dbo.rc
			WHERE
				rcsteid <> 6
		) AS rcTb INNER JOIN
        (
            SELECT
                [rec_id]
            ,[runcard_id]
            ,[runcard_no]
            ,[customer]
            ,[material_no]
            ,[color]--下拉選單
            ,[plasma]
            ,[reich]
            ,[remark]
            ,[reich2]
            ,[lot_no]
            ,[lens_specifications_bc]--成品 BC
            ,[lens_specifications_bcx]--成品 BCX
            ,[lens_specifications_bcy]--成品 BCY
            ,[lens_specifications_ct]--成品 CT
            ,[lens_specificatons_tc]--成品 TC
            ,[lens_specifications_dia]--成品 DIA
            ,[lens_specifications_power]--成品 POWER
            ,[lens_specifications_powerx]--成品 POWERX
            ,[lens_specifications_powery]--成品 POWERY
            ,[lens_semi_specifications_bc]--半成品 BC
            ,[lens_semi_specifications_bcx]--半成品 BCX
            ,[lens_semi_specifications_bcy]--半成品 BCY
            ,[lens_semi_specifications_ct]--半成品 CT
            ,[lens_semi_specifications_tc]--半成品 TC
            ,[create_dt]
            ,[update_dt]
            FROM
                btox.erp_info
            WHERE
                runcard_no LIKE '%' + @erp_order_no + '%'
        ) AS erpTb ON erpTb.runcard_id = rcTb.rcid INNER JOIN
        (
            SELECT
                rcid, pid
            FROM
                dbo.mpc
            WHERE
                is_processing = 1 AND is_deleted = 0
        ) AS mpcTb ON mpcTb.rcid = rcTb.rcid INNER JOIN
        dbo.p ON dbo.p.pid = mpcTb.pid
GO
/****** Object:  StoredProcedure [dbo].[sp_BTOX_erp_info_update]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: BTOX 顏色
	OODA_RESULT: VOID
*/
/*
	修改歷程
	時間				修正人		Mark
	 20727			Kaid		Create
*/
ALTER PROCEDURE [dbo].[sp_BTOX_erp_info_update]
    @rec_id                         BIGINT,
    @runcard_no                     NVARCHAR(50) = null
   ,@customer                       NVARCHAR(50) = null
   ,@material_no                    NVARCHAR(50) = null
   ,@color                          NVARCHAR(50) = null
   ,@plasma                         NVARCHAR(50) = null
   ,@reich                          NVARCHAR(50) = null
   ,@remark                         NVARCHAR(50) = null
   ,@reich2                         NVARCHAR(50) = null
   ,@lot_no                         NVARCHAR(50) = null
   ,@lens_specifications_bc         FLOAT = NULL--成品 BC
   ,@lens_specifications_bcx        FLOAT = NULL--成品 BCX
   ,@lens_specifications_bcy        FLOAT = NULL--成品 BCY
   ,@lens_specifications_ct         FLOAT = NULL--成品 CT
   ,@lens_specificatons_tc          FLOAT = NULL--成品 TC
   ,@lens_specifications_dia        FLOAT = NULL--成品 DIA
   ,@lens_specifications_power      FLOAT = NULL--成品 POWER
   ,@lens_specifications_powerx     FLOAT = NULL--成品 POWERX
   ,@lens_specifications_powery     FLOAT = NULL--成品 POWERY
   ,@lens_semi_specifications_bc    FLOAT = NULL--半成品 BC
   ,@lens_semi_specifications_bcx   FLOAT = NULL--半成品 BCX
   ,@lens_semi_specifications_bcy   FLOAT = NULL--半成品 BCY
   ,@lens_semi_specifications_ct    FLOAT = NULL--半成品 CT
   ,@lens_semi_specifications_tc    FLOAT = NULL--半成品 TC
   ,@user_id                        INT = NULL
AS
    UPDATE btox.erp_info
    SET
         runcard_no = @runcard_no
        ,customer = @customer
        ,material_no = @material_no
        ,color = @color--下拉選單
        ,plasma = @plasma
        ,reich = @reich
        ,remark = @remark
        ,reich2 = @reich2
        ,lot_no = @lot_no
        ,lens_specifications_bc = @lens_specifications_bc--成品 BC
        ,lens_specifications_bcx = @lens_specifications_bcx --成品 BCX
        ,lens_specifications_bcy = @lens_specifications_bcy--成品 BCY
        ,lens_specifications_ct = @lens_specifications_ct--成品 CT
        ,lens_specificatons_tc = @lens_specificatons_tc--成品 TC
        ,lens_specifications_dia = @lens_specifications_dia--成品 DIA
        ,lens_specifications_power = @lens_specifications_power--成品 POWER
        ,lens_specifications_powerx = @lens_specifications_powerx--成品 POWERX
        ,lens_specifications_powery = @lens_specifications_powery--成品 POWERY
        ,lens_semi_specifications_bc = @lens_semi_specifications_bc--半成品 BC
        ,lens_semi_specifications_bcx = @lens_semi_specifications_bcx--半成品 BCX
        ,lens_semi_specifications_bcy = @lens_semi_specifications_bcy--半成品 BCY
        ,lens_semi_specifications_ct = @lens_semi_specifications_ct--半成品 CT
        ,lens_semi_specifications_tc = @lens_semi_specifications_tc--半成品 TC
        ,update_dt = GETDATE()
        ,[user_id] = @user_id
    WHERE   
        rec_id = @rec_id
	
    SELECT SCOPE_IDENTITY () AS RESULT
GO
/****** Object:  StoredProcedure [dbo].[sp_BTOX_material_info_list]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: BTOX 顏色
	OODA_RESULT: RECORDSET
*/
/*
	修改歷程
	時間				修正人		Mark
	 20727			Kaid		Create
*/
ALTER PROCEDURE [dbo].[sp_BTOX_material_info_list] 
AS
	SELECT
		color
	FROM
		btox.material_info
GO
/****** Object:  StoredProcedure [dbo].[sp_mes_runtask_checkout]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: Runtask check-out
	OODA_RESULT: SCALAR
*/
/*
	修改歷程
	時間			修正人		Mark
	260526		Kaid		CREATE
*/
ALTER PROCEDURE [dbo].[sp_mes_runtask_checkout]
	@rcid				BIGINT,
	@mpoid				BIGINT,
	@checkout_qty		DECIMAL(19,4),
	@scrap_qty			DECIMAL(19,4),
	@update_dt			DATETIME,
	@update_uid			SMALLINT
AS
	DECLARE @state	BIGINT = -1
	DECLARE @mpcid	BIGINT = -1
	DECLARE @oseq	INT = -1
	DECLARE @eqpid	INT = NULL
	DECLARE @opid	BIGINT

	SELECT @mpcid = mpcid, @oseq = oseq, @opid = oactid FROM dbo.mpo WHERE mpoid = @mpoid AND rtsteid = 51
	SELECT @state = rtid FROM dbo.rt WHERE mpoid = @mpoid AND rtsteid = 51
	SELECT @eqpid = eqpid FROM dbo.rteqp WHERE rtid = @state

	IF(@state > 0)
	BEGIN
		UPDATE dbo.mpo
		SET
			rtsteid = 57, 
			update_uid = @update_uid,
			update_dt = @update_dt
		WHERE
			mpoid = @mpoid

		UPDATE dbo.rt
		SET
			rtsteid = 57, 
			checkout_qty = @checkout_qty,
			scrap_qty = @scrap_qty,
			oenduid = @update_uid,
			oenddt = @update_dt,
			update_uid = @update_uid,
			update_dt = @update_dt
		WHERE
			rtid = @state
		
		-- Kaid : 最後一站直接工單結案
		IF NOT EXISTS(SELECT COUNT(*) FROM dbo.mpo WHERE mpcid = @mpcid AND oseq > @oseq)
		BEGIN
			UPDATE dbo.rc
			SET
				rcsteid = 6,
				prod_end_dt = @update_dt,
				update_uid = @update_uid,
				update_dt = @update_dt
			WHERE
				rcid = @rcid
		END

		IF EXISTS (SELECT COUNT(*) FROM dbo.rteqp WHERE rtid = @state)
		BEGIN
			INSERT INTO dbo.eqpstlg
				SELECT TOP(1)
					eqpid, 5, @rcid, @state, 'sysAction', @update_uid, @update_dt
				FROM
					dbo.rteqp 
				WHERE 
					rtid = @state

			UPDATE dbo.eqp
				SET
					eqpsid = 5,
					update_dt = @update_dt,
					update_uid = @update_uid
				WHERE
					eqpid = @eqpid
		END

		DECLARE @is_on			BIT
		DECLARE @mold_log_id	BIGINT

		DECLARE @cur_moldid	INT 

		DECLARE mdo CURSOR FOR
			SELECT
				moldid
			FROM
				dbo.map_op_mold INNER JOIN
				dbo.map_op_eqp ON dbo.map_op_eqp.eqpid = dbo.map_op_mold.eqpid AND dbo.map_op_eqp.opid = dbo.map_op_mold.opid
			WHERE
				dbo.map_op_eqp.eqpid = @eqpid AND dbo.map_op_mold.opid = @opid
		OPEN mdo
		FETCH NEXT FROM mdo INTO @cur_moldid
		WHILE(@@FETCH_STATUS = 0)
		BEGIN
			SELECT TOP(1) @mold_log_id = recid, @is_on = is_on FROM dbo.mold_bind_log WHERE eqpid = @eqpid AND moldid = @cur_moldid ORDER BY create_dt DESC

			IF(@is_on = 'True')
			BEGIN
				UPDATE dbo.mold_bind_log
				SET
					usage_count = usage_count + 1
				WHERE	
					recid = @mold_log_id

		END

			FETCH NEXT FROM mdo INTO @cur_moldid
		END
		CLOSE mdo
		DEALLOCATE mdo

		
	END

	SELECT @state AS result
GO
/****** Object:  StoredProcedure [dbo].[sp_web_department_create]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: create control_chart (return id)
	OODA_RESULT: SCALAR
*/
/*
	修改歷程
	時間			修正人		Mark
	20250106		Jade		Create
*/
ALTER PROCEDURE [dbo].[sp_web_department_create] 
	@department_no		nvarchar(50),
	@department_name	nvarchar(50),
	@update_by			smallint

AS
	INSERT INTO dbo.dept
		(deptno,deptname, pltid, is_delete, update_dt, update_uid)
	VALUES
		(@department_no,@department_name, NULL, 0, GETDATE(), @update_by)

	SELECT @@IDENTITY  AS RESULT
GO
/****** Object:  StoredProcedure [dbo].[sp_web_department_delete]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: create control_chart (return id)
	OODA_RESULT: SCALAR
*/
ALTER PROCEDURE [dbo].[sp_web_department_delete] 
	@deptid		smallint,
	@update_by	smallint
AS
	UPDATE dbo.dept
	SET 
		is_delete = 1,
		update_dt = GETDATE(),
		update_uid = @update_by
	WHERE
		deptid = @deptid

GO
/****** Object:  StoredProcedure [dbo].[sp_web_department_info_by_id]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
 OODA_REMARK: 
 OODA_RESULT: RECORDSET
*/

/*
	修改歷程
	時間			修正人		Mark
	2025/01/06		Jade		CREATE
*/

ALTER PROCEDURE [dbo].[sp_web_department_info_by_id]

    @deptid		SMALLINT

AS
	SELECT 
		deptid, deptno, deptname
	FROM 
		dbo.dept
	WHERE 
		dbo.dept.deptid = @deptid
GO
/****** Object:  StoredProcedure [dbo].[sp_web_department_list]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: GET department list
	OODA_RESULT: RECORDSET
*/
/*
	修改歷程
	時間			修正人		Mark
	20250106		Jade		Create
*/
ALTER PROCEDURE [dbo].[sp_web_department_list]

AS

		SELECT
			dbo.dept.deptid,
			deptno,
			deptname,
			dbo.dept.update_dt,
			username
		FROM 
			dbo.dept INNER JOIN
			dbo.[user] ON dbo.[user].[uid] = dbo.dept.update_uid
		WHERE
			dbo.dept.is_delete = 0 
GO
/****** Object:  StoredProcedure [dbo].[sp_web_department_option_list]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: sp_web_department_all_list
	OODA_RESULT: RECORDSET
*/
/*
	修改歷程
	時間			修正人		Mark
	 060428		Kaid		Create
*/
ALTER PROCEDURE [dbo].[sp_web_department_option_list] 
AS
	SELECT          
		deptid, deptno, deptname
	FROM              
		dbo.dept
	WHERE
		is_delete = 0
GO
/****** Object:  StoredProcedure [dbo].[sp_web_department_update]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: create control_chart (return id)
	OODA_RESULT: SCALAR
*/
/*
	修改歷程
	時間			修正人		Mark
	20250106		Jade		Create
*/
ALTER PROCEDURE [dbo].[sp_web_department_update] 
	@deptid				smallint,
	@department_name	nvarchar(50),
	@update_by			smallint
AS
	UPDATE dbo.dept
	SET 
		deptname = @department_name,
		update_dt = GETDATE(),
		update_uid = @update_by
	WHERE
		deptid = @deptid

GO
/****** Object:  StoredProcedure [dbo].[sp_web_equip_option_list_by_state]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: WEB設備主檔List (option)
	OODA_RESULT: RECORDSET
*/
/*
	修改歷程
	時間				修正人		Mark
	 260507			Kaid		Create
*/
ALTER PROCEDURE [dbo].[sp_web_equip_option_list_by_state] 
	@eqpsid		SMALLINT = NULL
AS
	SELECT 
		eqpid, eqpno, eqpname
	FROM 
		dbo.eqp 
	WHERE 
		is_deleted = 0 AND (dbo.eqp.eqpsid = @eqpsid OR @eqpsid IS NULL)
	
GO
/****** Object:  StoredProcedure [dbo].[sp_web_equip_state_log_list]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: 設備變更紀錄 (前5筆)
	OODA_RESULT: RECORDSET
*/
/*
	修改歷程
	時間				修正人		Mark
	 260508			Kaid		Create
*/
ALTER PROCEDURE [dbo].[sp_web_equip_state_log_list] 
	@eqpid		INT,
	@data_count	INT = 5
AS
	SELECT
		dbo.ceqps.eqpsno, dbo.ceqps.eqpsname, logTb.remark, username, logTb.create_dt, IIF(btox.erp_info.runcard_no IS NULL, '', btox.erp_info.runcard_no) AS erp_no
	FROM
		(
			SELECT TOP(@data_count)
				eqpid, eqpsid, rcid, rtid, remark, create_by, create_dt
			FROM
				dbo.eqpstlg
			WHERE
				eqpid = @eqpid
			ORDER BY create_dt DESC
		) AS logTb INNER JOIN 
		dbo.ceqps  ON dbo.ceqps.eqpsid = logTb.eqpsid INNER JOIN
		dbo.[user] ON dbo.[user].[uid] = logTb.create_by LEFT JOIN
		dbo.rc ON dbo.rc.rcid = logTb.rcid  LEFT JOIN
		btox.erp_info ON btox.erp_info.runcard_id = dbo.rc.rcid
GO
/****** Object:  StoredProcedure [dbo].[sp_web_maintain_alarm_create]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: 新增維修區域 (return id)
	OODA_RESULT: SCALAR
*/
ALTER PROCEDURE [dbo].[sp_web_maintain_alarm_create] 
	@alarmno			NVARCHAR(50),
	@alarmname		NVARCHAR(60)
AS
	DECLARE @state BIGINT = -1 

	SELECT @state = alarmid FROM dbo.calarm WHERE alarmno = @alarmno

	IF @state < 0
	BEGIN
		INSERT INTO dbo.calarm
			(alarmno, alarmname)
		VALUES
			(@alarmno, @alarmname)

		SELECT @state = SCOPE_IDENTITY()
	END
	ELSE
	BEGIN
		SET @state = -1
	END
	
	SELECT @state AS RESULT
GO
/****** Object:  StoredProcedure [dbo].[sp_web_maintain_alarm_delete]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: 刪除維修區域 (return id)
	OODA_RESULT: SCALAR
*/
ALTER PROCEDURE [dbo].[sp_web_maintain_alarm_delete] 
	@alarmid				INT,
	@update_uid			smallint
AS
	DECLARE @state BIGINT = -1 

	SELECT @state = alarmid FROM dbo.calarm WHERE alarmid = @alarmid

	IF @state > 0
	BEGIN
		UPDATE dbo.calarm
		SET
			is_deleted = 1
		WHERE
			alarmid = @alarmid
	END

	SELECT @state AS result
GO
/****** Object:  StoredProcedure [dbo].[sp_web_maintain_alarm_get]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: web part list 產品主檔列表
	OODA_RESULT: RECORDSET
*/

/*

	修改歷程
	時間				修正人		Mark
	2026/04/29			Kaid		CREATE
*/

ALTER PROCEDURE [dbo].[sp_web_maintain_alarm_get] 
	@alarmid				INT
AS		
	SELECT          
		alarmid, alarmno, alarmname
	FROM              
		dbo.calarm
	WHERE          
		alarmid = @alarmid
GO
/****** Object:  StoredProcedure [dbo].[sp_web_maintain_alarm_list]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: web part list 產品主檔列表
	OODA_RESULT: RECORDSET
*/

/*

	修改歷程
	時間				修正人		Mark
	2026/04/29			Kaid		CREATE
*/

ALTER PROCEDURE [dbo].[sp_web_maintain_alarm_list] 

AS		
	SELECT          
		alarmid, alarmno, alarmname, is_system
	FROM              
		dbo.calarm
	WHERE          
		is_deleted = 0 
GO
/****** Object:  StoredProcedure [dbo].[sp_web_maintain_alarm_update]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: 更新維修區域 (return id)
	OODA_RESULT: SCALAR
*/
ALTER PROCEDURE [dbo].[sp_web_maintain_alarm_update] 
	@alarmid			INT,
	@alarmname		nvarchar(50)
AS
	DECLARE @state BIGINT = -1
	
	SELECT @state = alarmid FROM dbo.calarm WHERE alarmid = @alarmid
	
	IF @state > 0
	BEGIN
		UPDATE dbo.calarm
		SET
			alarmname = @alarmname
		WHERE
			alarmid = @alarmid	
	END

	SELECT @state AS RESULT
GO
/****** Object:  StoredProcedure [dbo].[sp_web_maintain_area_create]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: 新增維修區域 (return id)
	OODA_RESULT: SCALAR
*/
ALTER PROCEDURE [dbo].[sp_web_maintain_area_create] 
	@mtano			NVARCHAR(50),
	@mtaname		NVARCHAR(60),
	@remark			NVARCHAR(MAX) = NULL,
	@update_uid		SMALLINT
AS
	DECLARE @state BIGINT = -1 

	SELECT @state = mtaid FROM dbo.cmta WHERE mtano = @mtano

	IF @state < 0
	BEGIN
		INSERT INTO dbo.cmta
			(mtano, mtaname, remark)
		VALUES
			(@mtano, @mtaname, @remark)

		SELECT @state = SCOPE_IDENTITY()
	END
	ELSE
	BEGIN
		SET @state = -1
	END
	
	SELECT @state AS RESULT
GO
/****** Object:  StoredProcedure [dbo].[sp_web_maintain_area_delete]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: 刪除維修區域 (return id)
	OODA_RESULT: SCALAR
*/
ALTER PROCEDURE [dbo].[sp_web_maintain_area_delete] 
	@mtaid				INT,
	@update_uid			smallint
AS
	DECLARE @state BIGINT = -1 

	SELECT @state = mtaid FROM dbo.cmta WHERE mtaid = @mtaid

	IF @state > 0
	BEGIN
		UPDATE dbo.cmta
		SET
			is_deleted = 1
		WHERE
			mtaid = @mtaid
	END

	SELECT @state AS result
GO
/****** Object:  StoredProcedure [dbo].[sp_web_maintain_area_get]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: web part list 產品主檔列表
	OODA_RESULT: RECORDSET
*/

/*

	修改歷程
	時間				修正人		Mark
	2026/04/29			Kaid		CREATE
*/

ALTER PROCEDURE [dbo].[sp_web_maintain_area_get] 
	@mtaid				INT
AS		
	SELECT          
		mtaid, mtano, mtaname
	FROM              
		dbo.cmta
	WHERE          
		mtaid = @mtaid
GO
/****** Object:  StoredProcedure [dbo].[sp_web_maintain_area_list]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: web part list 產品主檔列表
	OODA_RESULT: RECORDSET
*/

/*

	修改歷程
	時間				修正人		Mark
	2026/04/29			Kaid		CREATE
*/

ALTER PROCEDURE [dbo].[sp_web_maintain_area_list] 

AS		
	SELECT          
		mtaid, mtano, mtaname
	FROM              
		dbo.cmta
	WHERE          
		is_deleted = 0 
GO
/****** Object:  StoredProcedure [dbo].[sp_web_maintain_area_update]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: 更新維修區域 (return id)
	OODA_RESULT: SCALAR
*/
ALTER PROCEDURE [dbo].[sp_web_maintain_area_update] 
	@mtaid			INT,
	@mtaname		nvarchar(50),
	@remark			NVARCHAR(MAX) = NULL
AS
	DECLARE @state BIGINT = -1
	
	SELECT @state = mtaid FROM dbo.cmta WHERE mtaid = @mtaid
	
	IF @state > 0
	BEGIN
		UPDATE dbo.cmta
		SET
			mtaname = @mtaname,
			remark = @remark
		WHERE
			mtaid = @mtaid	
	END

	SELECT @state AS RESULT
GO
/****** Object:  StoredProcedure [dbo].[sp_web_maintain_reason_create]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: 新增維修原因 (return id)
	OODA_RESULT: SCALAR
*/
ALTER PROCEDURE [dbo].[sp_web_maintain_reason_create] 
	@mtresno		NVARCHAR(50),
	@mtresname		NVARCHAR(60)
AS
	DECLARE @state BIGINT = -1 

	SELECT @state = mtresid FROM dbo.cmtres WHERE mtresno = @mtresno

	IF @state < 0
	BEGIN
		INSERT INTO dbo.cmtres
			(mtresno, mtresname)
		VALUES
			(@mtresno, @mtresname)

		SELECT @state = SCOPE_IDENTITY()
	END
	ELSE
	BEGIN
		SET @state = -1
	END
	
	SELECT @state AS RESULT
GO
/****** Object:  StoredProcedure [dbo].[sp_web_maintain_reason_delete]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: 刪除維修區域 (return id)
	OODA_RESULT: SCALAR
*/
ALTER PROCEDURE [dbo].[sp_web_maintain_reason_delete] 
	@mtresid			INT,
	@update_uid			smallint
AS
	DECLARE @state BIGINT = -1 

	SELECT @state = mtresid FROM dbo.cmtres WHERE mtresid = @mtresid

	IF @state > 0
	BEGIN
		UPDATE dbo.cmtres
		SET
			is_deleted = 1
		WHERE
			mtresid = @mtresid
	END

	SELECT @state AS result
GO
/****** Object:  StoredProcedure [dbo].[sp_web_maintain_reason_get]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: web part list 產品主檔列表
	OODA_RESULT: RECORDSET
*/

/*

	修改歷程
	時間				修正人		Mark
	2026/04/29			Kaid		CREATE
*/

ALTER PROCEDURE [dbo].[sp_web_maintain_reason_get] 
	@mtresid				INT
AS		
	SELECT          
		mtresid, mtresno, mtresname
	FROM              
		dbo.cmtres
	WHERE          
		mtresid = @mtresid
GO
/****** Object:  StoredProcedure [dbo].[sp_web_maintain_reason_list]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: web part list 產品主檔列表
	OODA_RESULT: RECORDSET
*/

/*

	修改歷程
	時間				修正人		Mark
	2026/04/29			Kaid		CREATE
*/

ALTER PROCEDURE [dbo].[sp_web_maintain_reason_list] 

AS		
	SELECT          
		mtresid, mtresno, mtresname
	FROM              
		dbo.cmtres
	WHERE          
		is_deleted = 0 
GO
/****** Object:  StoredProcedure [dbo].[sp_web_maintain_reason_update]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: 更新維修區域 (return id)
	OODA_RESULT: SCALAR
*/
ALTER PROCEDURE [dbo].[sp_web_maintain_reason_update] 
	@mtresid		INT,
	@mtresname		nvarchar(50),
	@remark			NVARCHAR(MAX) = NULL
AS
	DECLARE @state BIGINT = -1
	
	SELECT @state = mtresid FROM dbo.cmtres WHERE mtresid = @mtresid
	
	IF @state > 0
	BEGIN
		UPDATE dbo.cmtres
		SET
			mtresname = @mtresname
		WHERE
			mtresid = @mtresid
	END

	SELECT @state AS RESULT
GO
/****** Object:  StoredProcedure [dbo].[sp_web_mold_list]    Script Date: 2026/7/29 下午 03:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: 模具新增
	OODA_RESULT: RECORDSET
*/
/*
	修改歷程
	時間				修正人		Mark
	 260507			Kaid		Create
*/
ALTER PROCEDURE [dbo].[sp_web_mold_list] 
	@fctid		TINYINT = NULL,
	@moldtid	TINYINT = NULL
AS
	SELECT DISTINCT
		dbo.mold.moldid, dbo.cmoldt.moldtid, dbo.cfct.fctid, dbo.cmolds.moldsid, dbo.cmoldbinds.moldbindsid,
		dbo.cfct.fctname, dbo.cmoldt.moldtname, dbo.mold.moldno, dbo.mold.moldname,
		dbo.cmolds.mdsname, dbo.cmoldbinds.moldbindsname, dbo.mold.remark, dbo.mold.update_dt, dbo.[user].username,
		dbo.eqp.eqpid, eqpno, eqpname
	FROM
		dbo.mold INNER JOIN
		dbo.cmoldbinds ON dbo.mold.moldbindsid = dbo.cmoldbinds.moldbindsid INNER JOIN
		dbo.cmolds ON dbo.cmolds.moldsid = dbo.mold.moldsid INNER JOIN
		dbo.cfct ON dbo.cfct.fctid = dbo.mold.fctid INNER JOIN
		dbo.cmoldt ON dbo.cmoldt.moldtid = dbo.mold.moldtid INNER JOIN
		dbo.[user] ON dbo.[user].[uid] = dbo.mold.update_uid LEFT JOIN
		dbo.map_op_mold ON dbo.map_op_mold.moldid = dbo.mold.moldid LEFT JOIN
		dbo.map_op_eqp ON dbo.map_op_eqp.eqpid = dbo.map_op_mold.eqpid LEFT JOIN
		dbo.eqp ON dbo.eqp.eqpid = dbo.map_op_eqp.eqpid
	WHERE
		(dbo.mold.fctid = @fctid OR @fctid IS NULL) AND (dbo.mold.moldtid = @moldtid OR @moldtid IS NULL)
		AND mold.is_deleted = 0
GO
/****** Object:  StoredProcedure [dbo].[sp_web_map_op_eqp_list]    Script Date: 2026/7/31 下午 04:08:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	OODA_REMARK: 站點機台綁定關係
	OODA_RESULT: RECORDSET
*/
/*
	修改歷程
	時間			修正人		Mark
	260701		Kaid		Create
*/
ALTER PROCEDURE [dbo].[sp_web_map_op_eqp_list]
	
AS
	DECLARE @resultTb TABLE
	(
		eqpid		INT,
		eqpno		NVARCHAR(50),
		eqpname		NVARCHAR(50),
		actmsgid	INT,
		opname		NVARCHAR(50),
		fctid		INT,
		fctno		NVARCHAR(50),
		fctname		NVARCHAR(50),
		moldid		INT,
		moldno		NVARCHAR(50),
		moldname	NVARCHAR(50),
		moldtname	NVARCHAR(50)
	)

		DECLARE @opid		INT,
			@eqpid		INT

	DECLARE mdo CURSOR FOR
		SELECT
			opid, eqpid
		FROM
			dbo.map_op_eqp			

	OPEN mdo
	FETCH NEXT FROM mdo INTO @opid, @eqpid
	WHILE(@@FETCH_STATUS = 0)
	BEGIN
		INSERT INTO @resultTb
			SELECT
				dbo.eqp.eqpid, dbo.eqp.eqpno, dbo.eqp.eqpname,
				@opid, opnameTb.displaymsg, 
				dbo.cfct.fctid, dbo.cfct.fctno, dbo.cfct.fctname,
				IIF(dbo.mold.moldid IS NOT NULL, dbo.mold.moldid, NULL), 
				IIF(dbo.mold.moldno IS NOT NULL, dbo.mold.moldno, ''),
				IIF(dbo.mold.moldname IS NOT NULL, dbo.mold.moldname, ''),
				IIF(dbo.cmoldt.moldtname IS NOT NULL, dbo.cmoldt.moldtname, '')
			FROM
				(
					SELECT
						opid, eqpid
					FROM
						dbo.map_op_eqp
					WHERE
						opid = @opid AND eqpid = @eqpid 
				) AS mapTb INNER JOIN
				(
					SELECT
						actmsgid, displaymsg
					FROM
						dbo.actmsg 
					WHERE
						actmsgid = @opid AND lcid = 1
				) AS opnameTb ON opnameTb.actmsgid = mapTb.opid INNER JOIN
				dbo.eqp ON dbo.eqp.eqpid = mapTb.eqpid INNER JOIN
				dbo.cfct ON dbo.cfct.fctid = dbo.eqp.fctid LEFT JOIN
				dbo.map_op_mold ON dbo.map_op_mold.eqpid = mapTb.eqpid AND dbo.map_op_mold.opid = mapTb.opid LEFT JOIN
				dbo.mold ON dbo.mold.moldid = dbo.map_op_mold.moldid LEFT JOIN
				dbo.cmoldbinds ON dbo.cmoldbinds.moldbindsid = dbo.mold.moldbindsid LEFT JOIN
				dbo.cmoldt ON dbo.cmoldt.moldtid = dbo.mold.moldtid
				 
		FETCH NEXT FROM mdo INTO @opid, @eqpid
	END
	CLOSE mdo
	DEALLOCATE mdo


	SELECT * FROM @resultTb