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

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

购进系统总体架构图：

总体业务：

流程图：

自定义函数：

-- Description: 根据渠道编号查询渠道名称-- =============================================CREATE FUNCTION [dbo].[getChannelNameByChannelID]( @ChannelID INT)RETURNS NVARCHAR(200)ASBEGIN 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)ASBEGIN DECLARE @TypeName NVARCHAR(200) SELECT @TypeName=TypeName FROM dbo.BioProType WHERE TypeID=@TypeID RETURN @TypeNameEND

创建视图：

CREATE VIEW [dbo].[View_ProInfo]ASSELECT 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, DeleteSateFROM BiotbProduct

相对应存储过程的封装

1.BioProType_Add 产品类型添加

CREATE PROC [dbo].[BioProType_Add]( @ParentTypeID INT, @TypeName NVARCHAR(100))ASBEGIN 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 ASBEGIN 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=@ProIDEND

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。