附件销售存储过程式.sql

手机进销存系统Delphi源码,管理手机的进货还有销售方面的功能

declare @act int--操作类型1增加2修改3删除
declare @pjzl char(5)--配件资料
declare @gt   char(4)--柜台号资料
declare @num int--出售的数量
declare @mon money--出售价格
declare @date datetime--日期
declare @person char(4)-- 售货员
declare @fxlx  char(1)--附件销售类型
declare @memo char(200)--备注
declare @fjxcid  char(12)--附件销售ID
declare @OutNumber char(12) --output 




declare @NowNO int
declare @ErrorMsg nvarchar(200)
declare @IDType char(2)
declare @numb int

declare @curfid char(12)
declare @curfnum int
declare @curfuse int
declare  @curi int





if @act=1 
	begin
		set @IDType='FX'
		set @numb=4
		set @curi=@num
				DECLARE cur_fjrc SCROLL CURSOR FOR
				select  fid,fnum,fuse from tfjrc where fpjzl=@pjzl and fnum>fuse order by fdate
				OPEN cur_fjrc
				FETCH first FROM cur_fjrc
				into @curfid,@curfnum,@curfuse
				WHILE (@@FETCH_STATUS = 0)and (@curi>0)
					BEGIN

	
						if (@curfnum-@curfuse)>=@curi     --此记录恰好可以满足申请进价的记录
							begin
								set @num=@curi
								set @curi=0
								
							end	
						if (@curfnum-@curfuse)<@curi
							begin
							set @num=@curfnum-@curfuse
							set @curi=@curi-@num--还有这么多需要进行申请进价
							
							end		--更新此条进货记录的FUSE的数目
						 update tfjrc set fuse=fuse+@num where fid=@curfid
						
						exec pGetID @IDType,@numb,@OutNumber Output
		  					if @@Error<>0
   							Goto Failed  
						insert into tfjxc(fid,fpjzl,fnum,fdate,fperson,ffxlx,fmemo,fmon,ffjrc)values(@OutNumber,@pjzl ,@num,@date ,@person,@fxlx,@memo,@mon,@curfid  )        
   						if @@Error<>0
   							Goto Failed       

          		
			


		   		FETCH NEXT FROM cur_fjrc
				   into  @curfid,@curfnum,@curfuse
				END
				CLOSE cur_fjrc
				DEALLOCATE cur_fjrc
	end
if @act=2 
	begin
	      

			update tfjxc set fgt=@gt,fperson=@person,ffxlx=@fxlx,fmemo=@memo,fmon=@mon where fid=@fjxcid 
   			if @@Error<>0
   				Goto Failed 
	end
if @act=3 
	begin

 		set @curfid=(select fid from tfjxc where fid=@fjxcid) 
		set @curfnum=(select fnum from tfjxc where fid=@fjxcid)
		update tfjrc set fuse=fuse-@curfnum where fid=@curfid
		delete from tfjxc where fid=@fjxcid 
	end



	
Failed: