1、
USE [DB_MES2]
GO/****** Object: UserDefinedFunction [dbo].[GetProductCopyFuncIsHasAllocated] Script Date: 2017/7/12 9:36:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[GetProductCopyFuncIsHasAllocated] (@Product_ID uniqueidentifier)
RETURNS int
AS
BEGIN
DECLARE @Result int
DECLARE @Result2 int
SELECT @Result =COUNT(*) from MES_PRODUCT_PART p
join MES_PRODUCT_STEP s on p.PRODUCT_STEP_ID=s.PRODUCT_STEP_ID
join MES_PRODUCT_PROCESS ps on s.PRODUCT_PROCESS_ID=ps.PRODUCT_PROCESS_ID
join MES_PRODUCT_FLOW pf on ps.PRODUCT_FLOW_ID= pf.PRODUCT_FLOW_ID
where PRODUCT_ID = @Product_ID
if(@Result>0)
set @Result2=1
else
set @Result2=0
RETURN @Result2
END
--测试
DECLARE @Result int
EXEC @Result=[GetProductCopyFuncIsHasAllocated] '4ed7000c-e123-41ba-a31b-8f1216b91922'
print @Result
2、
USE [DB_MES2]
GO
/****** Object: UserDefinedFunction [dbo].[GetProductCopyFunc] Script Date: 2017/7/12 9:16:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[GetProductCopyFuncIsHasCountersign] (@Product_ID uniqueidentifier)
RETURNS int
AS
BEGIN
DECLARE @Result int
DECLARE @Result2 int
SELECT @Result = COUNT(*) from SYS_COUNTERSIGN_REQ where PRODUCT_ID = @Product_ID
if(@Result>0)
set @Result2=1
else
set @Result2=0
RETURN @Result2
END
3、
USE [DB_MES2]
GO
/****** Object: UserDefinedFunction [dbo].[GetProductCopyFuncIsHasCad] Script Date: 2017/7/12 9:34:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[GetProductCopyFuncIsHasCad] (@Product_ID uniqueidentifier)
RETURNS int
AS
BEGIN
DECLARE @Result int
DECLARE @Result2 int
SELECT @Result = COUNT(*) from SYS_PRODUCT_DOC where PRODUCT_ID = @Product_ID
if(@Result>0)
set @Result2=1
else
set @Result2=0
RETURN @Result2
END
--测试
--DECLARE @Result int
--EXEC @Result=[GetProductCopyFuncIsHasCad] '4ed7000c-e123-41ba-a31b-8f1216b91922'
--print @Result
4、
USE [DB_MES2]
GO
/****** Object: UserDefinedFunction [dbo].[GetProductCopyFuncIsHasAllocated] Script Date: 2017/7/12 9:51:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[GetProductCopyFuncIsHasProcess] (@Product_ID uniqueidentifier)
RETURNS int
AS
BEGIN
DECLARE @Result int
DECLARE @Result2 int
SELECT @Result = COUNT(*) from MES_PRODUCT_FLOW where PRODUCT_ID = @Product_ID
if(@Result>0)
set @Result2=1
else
set @Result2=0
RETURN @Result2
END
--测试
--DECLARE @Result int
--EXEC @Result = [GetProductCopyFuncIsHasProcess] '4ed7000c-e123-41ba-a31b-8f1216b91922'
--PRINT @Result
5、
USE [DB_MES2]
GO
/****** Object: UserDefinedFunction [dbo].[GetProductCopyFuncIsHasBom] Script Date: 2017/7/12 9:32:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[GetProductCopyFuncIsHasBom] (@Product_ID uniqueidentifier)
RETURNS int
AS
BEGIN
DECLARE @Result int
DECLARE @Result2 int
SELECT @Result = COUNT(*) from SYS_PRODUCT_BOM where PRODUCT_ID = @Product_ID
if(@Result>0)
set @Result2=1
else
set @Result2=0
RETURN @Result2
END
--测试
--DECLARE @Result int
--EXEC @Result=[GetProductCopyFuncIsHasBom] '4ed7000c-e123-41ba-a31b-8f1216b91922'
--print @Result
6、总的调用
USE [DB_MES2]
GO
/****** Object: StoredProcedure [dbo].[GetProductCopy] Script Date: 2017/7/10 8:59:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,王林杰>
-- Create date: <Create Date,2017/07/10>
-- Description: <Description,根据产品id获取产品复制的所有相关信息>
-- =============================================
ALTER PROCEDURE [dbo].[GetProductCopy]
@Product_ID uniqueidentifier
AS
BEGIN
DECLARE @ResultCountersign int
DECLARE @ResultBom int
DECLARE @ResultAllocated int
DECLARE @ResultCad int
DECLARE @ResultProcess int
--判断是否会签
EXEC @ResultCountersign=GetProductCopyFuncIsHasCountersign @Product_ID
--print @ResultCountersign
--判断是否分配Bom
EXEC @ResultBom=GetProductCopyFuncIsHasBom @Product_ID
--print @ResultBom
--判断是否分配Cad
EXEC @ResultCad=GetProductCopyFuncIsHasCad @Product_ID
--print @ResultCad
--判断是否设置流程
EXEC @ResultProcess = [GetProductCopyFuncIsHasProcess] @Product_ID
---print @ResultProcess
--判断是否分配元件
EXEC @ResultAllocated=GetProductCopyFuncIsHasAllocated @Product_ID
--print @ResultAllocated
SELECT a.PRODUCT_ID,a.PRODUCT_NO,a.PRODUCT_NAME,a.VER,a.SPEC,a.CUSTOMER,a.CATEGORY,a.STATUS,a.DESCRIPTION,a.OTHER1,a.OTHER2,a.OTHER3,a.OTHER4,
a.MK_USER_ID,a.COUNTERSIGN_CODE,PUBDATE,a.STOPDATE,a.REPLACE_MODE,
@ResultBom as IsHasBom,
@ResultAllocated as IsHasAllocated,
@ResultCad as IsHasCad,
@ResultCountersign as IsHasCountersign ,
@ResultProcess as IsHasProcess
FROM SYS_PRODUCT a WHERE PRODUCT_ID = @Product_ID
--判断是否分配Bom
if(@ResultBom>0)
SELECT * FROM SYS_PRODUCT_BOM WHERE PRODUCT_ID = @Product_ID
--判断是否分配Cad
if(@ResultCad>0)
SELECT * FROM SYS_PRODUCT_DOC WHERE PRODUCT_ID = @Product_ID
--判断是否会签
if(@ResultCountersign>0)
SELECT * FROM SYS_COUNTERSIGN_REQ WHERE PRODUCT_ID = @Product_ID
--判断是否设置流程
if(@ResultProcess>0)
SELECT * FROM MES_PRODUCT_FLOW WHERE PRODUCT_ID = @Product_ID
--判断是否分配元件
if(@ResultAllocated>0)
SELECT * from MES_PRODUCT_PART p
join MES_PRODUCT_STEP s on p.PRODUCT_STEP_ID=s.PRODUCT_STEP_ID
join MES_PRODUCT_PROCESS ps on s.PRODUCT_PROCESS_ID=ps.PRODUCT_PROCESS_ID
join MES_PRODUCT_FLOW pf on ps.PRODUCT_FLOW_ID= pf.PRODUCT_FLOW_ID
where PRODUCT_ID = @Product_ID
--产品替代料
SELECT * from MES_BOM_REPLACE where PRODUCT_ID = @Product_ID
END
--EXEC [GetProductCopy] '4ed7000c-e123-41ba-a31b-8f1216b91922'
7、程序调用方式
SqlParameter parameter = new SqlParameter("@Product_ID", System.Data.SqlDbType.UniqueIdentifier);
parameter.SqlValue = ProductId;
var ss = Repository.ExecuteQuerySqlToDataSet("EXEC [GetProductCopy] @Product_ID", parameter);(本作者使用的是ef实体)