ERP产品购进系统商品管理（三十三）

购进系统总体架构图：

总体业务：

流程图：

 

 自定义函数：

-- Description:	根据渠道编号查询渠道名称
-- =============================================
CREATE FUNCTION [dbo].[getChannelNameByChannelID]
(
	@ChannelID INT
)
RETURNS NVARCHAR(200)
AS
BEGIN
	DECLARE @ChannelName NVARCHAR(200)
    SELECT @ChannelName=ChannelName FROM dbo.BioErpCrmManageChannel
    WHERE ChannelID=@ChannelID
    RETURN @ChannelName    
END

-- Description:	根据渠道编号查询渠道名称
-- =============================================
CREATE FUNCTION [dbo].[getTypeNameByTypeID]
(
	@TypeID INT
)
RETURNS NVARCHAR(200)
AS
BEGIN
	DECLARE @TypeName NVARCHAR(200)
    SELECT @TypeName=TypeName FROM dbo.BioProType
    WHERE TypeID=@TypeID    
    RETURN @TypeName
END

 创建视图：

CREATE VIEW [dbo].[View_ProInfo]
AS
SELECT
	ProID,
	ProName,
	TypeID,
	TypeName=dbo.getTypeNameByTypeID(TypeID),
	CustomerID,
	CustomerName= dbo.getCustomerByID(CustomerID),
	ProLogo,
	Spec,
	ProFullName,
	ConfirmNo,
	Unit,
	ProSize,
	Grossweight,
	MadeEnterprise,
	Retailprice,
	BidPrice,
	TradePrice,
	AdvicePrice,
	CountyPrice,
	CityPrice,
	ProvincePrice,
	NetPrice,
	NetVipPrice,
	NetMemberprice,
	OurCompany,
	ChannelID,
	ChannelName=dbo.getChannelNameByChannelID(ChannelID),
	Usefullife,
	SubmitTime,
	StockMost,
	StockLess,
	Remark,
	Userid,
	DeleteSate
FROM
	BiotbProduct

 相对应存储过程的封装

1.BioProType_Add 产品类型添加

CREATE PROC [dbo].[BioProType_Add]
(
	@ParentTypeID INT,
	@TypeName NVARCHAR(100)
)
AS
BEGIN
	INSERT BioProType
	(
		ParentTypeID,
		TypeName
	)
	VALUES
	(
		
		@ParentTypeID,
		@TypeName
	)
END

 2.BioProType_Update 产品类型修改

CREATE PROCEDURE [dbo].[BioProType_Update]
@TypeID INT,
@ParentTypeID INT,
@TypeName NVARCHAR(100)
 AS 
	UPDATE [BioProType] SET 
	[ParentTypeID] = @ParentTypeID,[TypeName] = @TypeName
	WHERE TypeID=@TypeID 
GO

 3.BiotbProduct_Add 产品信息添加

CREATE PROCEDURE [dbo].[BiotbProduct_ADD]
@ProID int output,
@ProName nvarchar(100),
@TypeID int,
@CustomerID int,
@ProLogo nvarchar(200),
@Spec nvarchar(100),
@ProFullName nvarchar(100),
@ConfirmNo nvarchar(100),
@Unit nvarchar(50),
@ProSize nvarchar(50),
@Grossweight nvarchar(50),
@MadeEnterprise nvarchar(100),
@Retailprice money,
@BidPrice money,
@TradePrice money,
@AdvicePrice money,
@CountyPrice money,
@CityPrice money,
@ProvincePrice money,
@NetPrice money,
@NetVipPrice money,
@NetMemberprice money,
@OurCompany nvarchar(100),
@ChannelID int,
@Usefullife nvarchar(50),
@SubmitTime datetime,
@StockMost nvarchar(100),
@StockLess nvarchar(100),
@Remark nvarchar(1000),
@Userid int

 AS 
	INSERT INTO [BiotbProduct](
	[ProName],[TypeID],[CustomerID],[ProLogo],[Spec],[ProFullName],[ConfirmNo],[Unit],[ProSize],[Grossweight],[MadeEnterprise],[Retailprice],[BidPrice],
[TradePrice],[AdvicePrice],[CountyPrice],[CityPrice],[ProvincePrice],[NetPrice],[NetVipPrice],[NetMemberprice],[OurCompany],[ChannelID],[Usefullife],
[SubmitTime],[StockMost],[StockLess],[Remark],[Userid]
	)VALUES(
	@ProName,@TypeID,@CustomerID,@ProLogo,@Spec,@ProFullName,@ConfirmNo,@Unit,@ProSize,@Grossweight,@MadeEnterprise,@Retailprice,@BidPrice,
@TradePrice,@AdvicePrice,@CountyPrice,@CityPrice,@ProvincePrice,@NetPrice,@NetVipPrice,@NetMemberprice,@OurCompany,@ChannelID,
@Usefullife,@SubmitTime,@StockMost,@StockLess,@Remark,@Userid
	)
	SET @ProID = @@IDENTITY

 4.BiotbProduct_Update 产品修改

CREATE PROCEDURE [dbo].[BiotbProduct_Update]
@ProID INT,
@ProName NVARCHAR(100),
@TypeID INT,
@CustomerID INT,
@ProLogo NVARCHAR(200),
@Spec NVARCHAR(100),
@ProFullName NVARCHAR(100),
@ConfirmNo NVARCHAR(100),
@Unit NVARCHAR(50),
@ProSize NVARCHAR(50),
@Grossweight NVARCHAR(50),
@MadeEnterprise NVARCHAR(100),
@Retailprice MONEY=0,
@BidPrice MONEY=0,
@TradePrice MONEY=0,
@AdvicePrice MONEY=0,
@CountyPrice MONEY=0,
@CityPrice MONEY=0,
@ProvincePrice MONEY=0,
@NetPrice MONEY=0,
@NetVipPrice MONEY=0,
@NetMemberprice MONEY=0,
@OurCompany NVARCHAR(100),
@ChannelID INT,
@Usefullife NVARCHAR(50),
@SubmitTime DATETIME,
@StockMost NVARCHAR(100),
@StockLess NVARCHAR(100),
@Remark NVARCHAR(1000),
@Userid INT,
@DeleteSate BIT
 AS 
	UPDATE [BiotbProduct] SET 
	[ProName] = @ProName,[TypeID] = @TypeID,[CustomerID] = @CustomerID,[ProLogo] = @ProLogo,[Spec] = @Spec,[ProFullName] = @ProFullName,[ConfirmNo]
 = @ConfirmNo,[Unit] = @Unit,[ProSize] = @ProSize,[Grossweight] = @Grossweight,[MadeEnterprise] = @MadeEnterprise,[Retailprice] = @Retailprice,[BidPrice] 
= @BidPrice,[TradePrice] = @TradePrice,[AdvicePrice] = @AdvicePrice,[CountyPrice] = @CountyPrice,[CityPrice] = @CityPrice,[ProvincePrice] = @ProvincePrice,
[NetPrice] = @NetPrice,[NetVipPrice] = @NetVipPrice,[NetMemberprice] = @NetMemberprice,[OurCompany] = @OurCompany,[ChannelID] = @ChannelID,[Usefullife] =
 @Usefullife,[SubmitTime] = @SubmitTime,[StockMost] = @StockMost,[StockLess] = @StockLess,[Remark] = @Remark,[Userid]=@Userid,DeleteSate=@DeleteSate
	WHERE ProID=@ProID 

 5.产品图片添加的存储过程;BiotbProImgs_ADD

CREATE PROCEDURE [dbo].[BiotbProImgs_ADD]
@ProID INT,
@ProImg NVARCHAR(200)

 AS 
	INSERT INTO [BiotbProImgs](
	[ProID],[ProImg]
	)VALUES(
	@ProID,@ProImg
	)

 6.根据产品编号获取产品对象：View_ProInfo_GetModel

-- Description:	根据产品编号获取产品对象
-- =============================================
CREATE PROCEDURE [dbo].[View_ProInfo_GetModel]
	@ProID int
	AS
BEGIN
	
	SET NOCOUNT ON;
  SELECT
  	ProID,
  	ProName,
  	TypeID,
  	TypeName,
  	CustomerID,
  	CustomerName,
  	ProLogo,
  	Spec,
  	ProFullName,
  	ConfirmNo,
  	Unit,
  	ProSize,
  	Grossweight,
  	MadeEnterprise,
  	Retailprice,
  	BidPrice,
  	TradePrice,
  	AdvicePrice,
  	CountyPrice,
  	CityPrice,
  	ProvincePrice,
  	NetPrice,
  	NetVipPrice,
  	NetMemberprice,
  	OurCompany,
  	ChannelID,
  	ChannelName,
  	Usefullife,
  	SubmitTime,
  	StockMost,
  	StockLess,
  	Remark,
  	Userid,
  	DeleteSate	
  FROM
  	View_ProInfo 
  	WHERE ProID=@ProID
END