viewandproc.sql

这是用PB6开发的一个POS管理系统

CREATE PROCEDURE up_UpdateReceiptNO
@TableName Varchar(30),
@DeptNo    Tdept,
@ReceiptNo TReceipt
AS

Declare @No TReceipt
Declare @len int
Declare @Count int

select @no=no from receiptno where name=@TableName and deptno=@deptno
Select @Count=@@RowCount
Select @len=len(@deptNo)
if @DeptNo <> left(@ReceiptNo,@len) Return

     if @count<1 
     begin
         select @no=ltrim(rtrim(@deptno)) + '000001'
         Insert Into receiptno (name,deptno,no) values (@TableName,@deptno,@no)
     end
     else
        if Right(@No,6)='999998'
           Delete from Receiptno where name=@TableName
        else
           update receiptno set no=@ReceiptNO where name=@TableName and deptno=@deptno
go

Print 'Create up_UpdateReceiptNo ok!'
go
/*------------------------------------------------------------------------------------*/
/****** Object:  procedure dbo.up_getmaxreceiptno    Script Date: 00-4-3 14:47:31 ******/
if (select count(*) from sysobjects where type='p' and name='up_getreceiptno')>0
	drop proc up_getreceiptno
go

CREATE PROC up_getreceiptno
	@tablename varchar(30),
	@deptno Tdept,
	@receiptno Treceipt output
AS
declare @no integer
declare @temp Treceipt

if (select count(*) from receiptno where deptno=@deptno and name=@tablename)>0   
begin
   select @temp=no from receiptno where deptno=@deptno and name=@tablename
   select @receiptno=left(@temp,4)
   select @no=convert(int,right(@temp,6))+1
   select @temp=ltrim(rtrim(str(@no)))
   select @temp=left('000000',6-len(@temp))+@temp
   select @receiptno=@deptno+@temp
end
else
   select @receiptno=@deptno+'000001'		
go
print 'up_getreceiptno is created already.'
go


if exists(select * from sysobjects where name='up_UpdateVendorPrice' and type='p')
	drop procedure up_UpdateVendorPrice
go

Create Procedure up_UpdatevendorPrice (
@VendorNo tVendor,
@GoodsNo TGoods,
@TaxPrice TPrice)
as 
Begin
  Declare @HighPrice TPrice
  Declare @LowPrice   TPrice
  declare @HighDate	TDate
  Declare @LowDate	Tdate
 if (select count(*) from vendorprice where goodsno=@goodsno and vendorno=@vendorno)>0
    begin
        Select @HighPrice=HighPrice,@HighDate=HighDate,@LowDate=LowDate,@LowPrice=LowPrice from VendorPrice Where VendorNo=@VendorNo and GoodsNo=@GoodsNo
        if @TaxPrice > @HighPrice 
             Select @HighPrice=@TaxPrice,@HighDate=GetDate()
	if @TaxPrice < @LowPrice
	     Select @LowPrice=@Taxprice,@LowDate=GetDate()
	update vendorprice
	set lastprice=@taxprice,
	    subprice=lastprice,
	    HighPrice=@HighPrice,
	    HighDate=@HighDate,
	    LowPrice=@LowPrice,
	    LowDate=@LowDate
	where vendorno=@vendorno and goodsno=@goodsno
    end
else
	insert vendorprice(goodsno,vendorno,lastprice,highprice,
			   lowprice,subprice,highdate,lowdate)
	values(@goodsno,@vendorno,@taxprice,@taxprice,
               @taxprice,@taxprice,Getdate(),GetDate())
end
go

print ' Procedure up_UpdateVendorPrice is created'
go

if exists(select * from sysobjects where name='up_UpdateOrders' and type='p')
	drop procedure up_UpdateOrders
go
Create Procedure up_UpdateOrders (
@OrderNo tReceipt,
@Goodsno TGoods,
@Qty   TQty)
as
Begin
	update orders
	set receivedate=GetDate()
	where orderno=@orderno

	update orderdetail
	set	qty=isnull(qty,0)+@qty
	where orderno=@orderno and goodsno=@goodsno
end
go

print "Procedure up_UpdateOrders is created"
go

if exists(select * from sysobjects where name='up_InsertAlloc' and type='p')
	drop procedure up_InsertAlloc
go

Create Procedure up_InsertAlloc (
@SrcDept	TDept,
@DestDept 	TDept,
@ImportNo 	TReceipt,
@GoodsNo 	TGoods,
@Qty		TQty,
@TaxPrice	TPrice,
@NTaxPrice	TPrice,
@Amt		TAmt,
@NTaxAmt	TAmt,
@ValidDate	TDate,
@VendorNo	TVendor,
@BatchNo	TBatch,
@RetailPrice	TPrice,
@Operator	TStaff,
@Employee	TStaff,
@BusinessFlag	char(1))
as
begin
  Declare @AllocNo TReceipt
  Declare @Profitratio numeric(5,3)
  Declare @Price	TPrice

	if (select count(*) from alloc where receiptno=@importno and receiptflag='2')>0
	   select @allocno=allocno from alloc where receiptno=@importno and receiptflag='2'	
	else
	begin
		exec up_getreceiptno 'alloc',@destdept,@allocno output
		insert alloc(allocno,receiptno,operator,employee,allocdate,destdept,srcdept,
			inputdept,cost,ntaxamt,amt,receiptflag,businessflag,receivedate,auditflag)
		values(@allocno,@importno,@operator,@employee,getdate(),@destdept,@Srcdept,
		       @SrcDept,0,0,0,'2',@businessflag,Getdate(),'1')
	end
	Select @ProfitRatio=Isnull(ProfitRatio,0) from Dept where DeptNo=@Destdept
	Select @Price = @TaxPrice * (1 + @ProfitRatio)
	Select @NTaxPrice = @NTaxPrice * ( 1 + @Profitratio)
	Select @Amt = Round(@Amt * (1 + @Profitratio),2)
 	Select @NTaxAmt = Round(@NTaxAmt * ( 1 + @ProfitRatio),2)
        insert allocdetail(allocno,goodsno,qty,auditQty,costprice,price,ntaxprice,amt,
		ntaxamt,validdate,vendorno,batchno,RetailPrice)
	values(@allocno,@goodsno,@qty,@Qty,@TaxPrice,@Price,@ntaxprice,@amt,
	       @ntaxamt,@validdate,@vendorno,@batchno,@RetailPrice)

	update alloc
	set cost=cost+@amt,amt=amt+@amt,ntaxamt=ntaxamt+@ntaxamt
        where allocno=@allocno
end
go
Print "Procedure up_insertAlloc is Created "
go


/*=====================================*/
/* Process PosRetail and SuspendRetail */
/*=====================================*/

if exists (select * from sysobjects where id = object_id('dbo.ProcessPosAndSuspendRetail') and sysstat & 0xf = 4)
	drop procedure dbo.ProcessPosAndSuspendRetail
go

CREATE PROCEDURE ProcessPosAndSuspendRetail AS 

Delete from PosRetail Where InputDate < DateAdd(dd,-5,GetDate())
Delete From SusPendRetail Where InputDate < DateAdd(dd,-5,GetDate())
Delete From Retail Where InputDate < DateAdd(dd,-32,GetDate())
go

Print " Procedure Processposandsuspendretail is Created!"
go

if exists ( select * from sysobjects where name='autocreateDayReport' and type='p')
  drop proc AutoCreateDayReport
go

Create Proc AutoCreateDayReport (@day as int) as
Declare @deptNo as tDept
Declare @Date tDate
Declare @SDate char(8)
Declare @Count int
Declare @year int
Declare @Month Int
Declare @Flag char(1)

Select @date=DateAdd(dd,@day,GetDate())
Select @SDate=Convert(char(8),@date,112)
Select @year=year,@Month=Month from Months where Convert(char(8),enddate,112)=@SDate
Select @Count=@@RowCount

declare c1 cursor for select deptno from dept where localflag='1'
open c1
Fetch c1 into @deptNo
While @@Fetch_Status=0
 Begin
   Select @Flag=isnull(Flag,'0') from tally where Convert(char(8),businessdate,112)=@SDate and deptNo=@deptNo
   if @Flag<>'1'  /* 没有登帐*/
    begin
	   Execute CreateDayReport @deptNo,@Sdate
	   if @Count>0 
		Execute MonthInit @deptNo,@Year,@Month
	   Insert tally (deptno,tallydate,businessdate,staff,flag)
	   values (@deptNo,getdate(),@Date,'root','1')
    end
   Fetch c1 into @DeptNO
 end
close c1
deallocate c1

go

print 'Proc AutoCreatedayreport is created!'
go

/****************************************************************************/
/*                      结算用过程				          */
/****************************************************************************/

/*原结算过程*/
if exists ( select * from sysobjects where name='up_InsertAccountForImport' and type='p')
  drop Proc up_InsertAccountForImport
go

CREATE Proc up_InsertAccountForImport 
as
Declare @day int
if datepart(hh,getdate())<19 
   Select @Day=-1
else
   Select @Day=0
 Insert Account (Subjectno,DeptNo,ReceiptNo,Companyno,Employee,Operator,Credit,balance,occurdate)
   Select '204',a.InputDept,a.ImportNo,a.VendorNo,a.Employee,a.Operator,sum(b.Amt),sum(-b.amt),a.AuditDate
   From Import a,Importdetail b
   where a.ImportNo=b.ImportNo and a.AuditFlag='1' and
    Convert(char(8),a.AuditDate,112)=Convert(char(8),dateAdd(dd,@day,GetDate()),112)
   group by a.Inputdept,a.ImportNo,a.vendorNo,a.employee,a.Operator,a.Auditdate

go

print 'proc up_InsertAccountForImport is created!'
go


/*新结算过程*/

/* up_importcheck_update_by_retail 销售划进货流水 */
if (select count(*) from sysobjects where name = 'up_importcheck_update_by_retail') > 0
   drop procedure up_importcheck_update_by_retail
go

create procedure up_importcheck_update_by_retail
@GoodsNo TGoods,@RetailQty TQty
as   
declare @ImportQty TQty   
declare @exportQty TQty //退货数量 
declare @RetailedQty TQty   //已销售数量
declare @BalanceQty TQty   //剩余数量
select @BalanceQty = @RetailQty
if @RetailQty > 0  	//正常销售，销售数量为正数	  
	begin  //先划供货商为88888888的进货
		select @RetailedQty = isnull(RetailQty,0) from Import_To_Check where VendorNo='88888888' and GoodsNo = @GoodsNo
		if @RetailedQty  < 0 
			begin
				if ( @BalanceQty + @RetailQty ) > 0
					update Import_To_Check set RetailQty = 0 where VendorNo ='88888888' and GoodsNo = @GoodsNo
				else
					begin
						update Import_To_Check set RetailQty = isnull(RetailQty,0) + @BalanceQty where VendorNo='88888888' and GoodsNo = @GoodsNo
						return
					end
				select @BalanceQty =@BalanceQty + @RetailEdQty
			end 
	   declare my_cursor CURSOR FOR    
		   SELECT isnull(ImportQty,0),isnull(ExportQty,0),isnull(RetailQty,0) from Import_To_Check
				where GoodsNo = @GoodsNo and (isnull(ImportQty,0) + isnull(ExportQty,0) > isnull(RetailQty,0) )
				order by id   
	end 
else  //退货或更正时，销售数量为负数
	begin
		select @RetailEdQty = isnull(RetailQty,0) from Import_To_Check where VendorNo='88888888' and GoodsNo = @GoodsNo
		if @RetailedQty  > 0 
			begin
				if (@RetailQty + @RetailedQty) > 0
					begin
						update Import_To_Check set RetailQty = isnull(RetailQty,0) + @BalanceQty where VendorNo='88888888' and GoodsNo = @GoodsNo
						return
					end
				else
					update Import_To_Check set RetailQty = 0 where VendorNo='88888888' and GoodsNo = @GoodsNo
				select @BalanceQty = @BalanceQty + @RetailedQty
			end
	   declare my_cursor CURSOR FOR    
		   SELECT isnull(ImportQty,0),isnull(ExportQty,0),isnull(RetailQty,0) from Import_To_Check
				where GoodsNo = @GoodsNo and isnull(RetailQty,0) > 0 
				order by id desc   
	end

OPEN my_cursor   
FETCH NEXT from my_cursor into @ImportQty,@ExportQty,@RetailedQty   
if @@fetch_status = 0 //表示有未划完进货的记录
	begin 
		while @@fetch_status = 0   
  		begin   
      	if @RetailQty > 0   
	  			BEGIN    
	    			if (@ImportQty + @ExportQty) >= (@BalanceQty + @RetailedQty)   //剩余数量+已销售数量小于进货数量，表示够划
	    				begin  
	       				UPDATE Import_To_Check SET RetailQty = isnull(RetailQty,0) + @BalanceQty   
		  						WHERE current of my_cursor   
							select @BalanceQty = 0 
			  	  			BREAK   //退出循环
			  			end   
			  		ELSE   
		  				begin   
		  					UPDATE Import_To_Check SET RetailQty = (@ImportQty + @ExportQty) WHERE current of my_cursor   
		  	 				select @BalanceQty = @BalanceQty - (@ImportQty + @ExportQty - @RetailedQty)   
			  	 		end   
			  	END    
			Else    
				BEGIN    
		  			if (@RetailedQty + @BalanceQty) > 0   
		  				begin  
		     				UPDATE Import_To_Check SET RetailQty = isnull(RetailQty,0) + @BalanceQty   
							WHERE current of my_cursor   
							select @BalanceQty = 0
		  	  				BREAK   //退出循环
		  				end   
			  		ELSE   
			  			begin   
			  				UPDATE Import_To_Check SET RetailQty = 0 WHERE current of my_cursor   
		  		 			select @BalanceQty = @BalanceQty + @RetailedQty   
		  	 			end   
			  	END    		  
			FETCH NEXT from my_cursor into @ImportQty,@ExportQty,@RetailedQty   
  		end   
		if @BalanceQty <> 0 //销售数量没有划完
			if (select count(*) from Import_To_Check where VendorNo='88888888' and GoodsNo = @GoodsNo ) >0 
				update Import_To_Check set
					RetailQty = isnull(RetailQty,0) + @BalanceQty
				where VendorNo = '88888888' and GoodsNo = @GoodsNo
			else
				insert into Import_To_Check(VendorNo,GoodsNo,RetailQty,ImportDate,ImportNo)
					values( '88888888',@GoodsNo,@BalanceQty,getdate(),'##########')
	end 
else  //没有可划进货的记录，可能是先销后进的情况
	if (select count(*) from Import_To_Check where VendorNo='88888888' and GoodsNo = @GoodsNo ) >0 
		update Import_To_Check set
			RetailQty = isnull(RetailQty,0) + @BalanceQty
		where VendorNo = '88888888' and GoodsNo = @GoodsNo
	else
		insert into Import_To_Check(VendorNo,GoodsNo,RetailQty,ImportDate,ImportNo)
			values( '88888888',@GoodsNo,@BalanceQty,getdate(),'##########')
close my_cursor   
deallocate my_cursor 
go

print 'up_importcheck_update_by_retail is created!'
go


/* up_importcheck_update_by_import 进货调整import_to_check表 */
if (select count(*) from sysobjects where name = 'up_importcheck_update_by_import') > 0
   drop procedure up_importcheck_update_by_import
go

create procedure up_importcheck_update_by_import
@GoodsNo TGoods, @ImportNo TReceipt, @ImportQty TQty, @VendorNo TVendor
as
declare @RetailedQty TQty //已销售数量

select @RetailedQty = isnull(RetailQty,0) from Import_To_Check
where VendorNo ='88888888' and GoodsNo = @GoodsNo

if @@rowcount = 0 return
if @RetailedQty > 0  //表示已先销后进，则要把已销的数量反映到进货来
	if @ImportQty < @RetailedQty
		begin
			update Import_To_Check set RetailQty = isnull(RetailQty,0) - @ImportQty
				where VendorNo = '88888888' and GoodsNo = @GoodsNo
			update Import_To_Check set RetailQty =  @ImportQty
				where VendorNo = @VendorNo and ImportNo = @ImportNo and GoodsNo = @GoodsNo
		end
	else
		begin
			update Import_To_Check set RetailQty = 0
				where VendorNo = '88888888' and GoodsNo = @GoodsNo
			update Import_To_Check set RetailQty =  @RetailedQty
				where VendorNo = @VendorNo and ImportNo = @ImportNo and GoodsNo = @GoodsNo
		end
go

print 'up_importcheck_update_by_import is Created!'
go


/* up_importcheck_update_by_checkviaamt 按金额结算时调整import_to_check表的结算数量及结算金额 */
if (select count(*) from sysobjects where name = 'up_importcheck_update_by_checkviaamt') > 0
   drop procedure up_importcheck_update_by_checkviaamt