viewandproc.sql

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

	b.GoodsNo
   From Import a,ImportDetail b
Where a.ImportNo=b.ImportNo  and a.auditFlag='1'
Group by  a.InputDept,b.GoodsNo,Convert(char(8),a.AuditDate,112)
go


if exists ( Select * from SysObjects Where Name='VDestgoodsAlloc' and Type='V' )
   Drop View VDestgoodsAlloc
go

Create View VDestgoodsAlloc as 
	select Convert(char(8),a.ReceiveDate,112) AuditDate,
	a.DestDept,
	Sum(Round(b.RetailPrice * b.Qty,2)) RetailAmt,
	Sum(round(b.CostPrice * b.Qty,2)) CostAmt,
	Sum(b.Amt) Amt,
	Sum(b.NtaxAmt) NtaxAmt,
	Sum(b.Qty) Qty,
	b.GoodsNo
From Alloc a,AllocDetail b
Where a.AllocNo=b.AllocNO  and a.AuditFlag='1'
Group by a.DestDept,b.GoodsNo,Convert(char(8),a.ReceiveDate,112)
go

if exists ( Select * from SysObjects Where Name='VSrcgoodsAlloc' and Type='V' )
   Drop View VSrcgoodsAlloc
go

Create View VSrcgoodsAlloc as 
	select Convert(char(8),a.ReceiveDate,112) AuditDate,
	a.SrcDept,
	Sum(Round(b.RetailPrice * b.Qty,2)) RetailAmt,
	Sum(round(b.CostPrice * b.Qty,2)) CostAmt,
	Sum(b.Amt) Amt,
	Sum(b.NtaxAmt) NtaxAmt,
	Sum(b.Qty) Qty,
	b.GoodsNo
From Alloc a,AllocDetail b
Where a.AllocNo=b.AllocNO and a.AuditFlag='1'
Group by a.SrcDept,b.GoodsNo,Convert(char(8),a.ReceiveDate,112)
go


if exists ( Select * from SysObjects Where Name='vGoodsLoss' and Type='V' )
   Drop View vGoodsLoss
go
Create View vGoodsLoss as
  select Convert(char(8),a.AuditDate,112) AuditDate,
        a.DeptNO,
	Sum(Round(b.RetailPrice * b.Qty,2)) RetailAmt,
	Sum(Round(b.CostPrice * b.Qty,2)) Amt,
	Sum(b.Qty) Qty,
	b.GoodsNo
	From Loss a,LossDetail b
Where a.LossNo=b.LossNo  and a.AuditFlag='1'
Group by a.DeptNO,b.goodsno,Convert(char(8),a.AuditDate,112)
go


if exists ( Select * from SysObjects Where Name='vGoodsRetail' and Type='V' )
   Drop View vGoodsRetail
go
Create View vGoodsRetail as 
  Select Convert(char(8),a.InputDate,112) Inputdate,
	a.deptNo,
	Sum(a.Amt) Amt,
	Sum(a.DisAmt) DisAmt,
	Sum(a.Qty) Qty,
	Sum(Round(a.CostPrice * a.Qty,2)) CostAmt,
	b.GoodsNo
From Retail a,Goods b
Where a.GoodsNo=b.GoodsNo
Group by a.DeptNO,b.GoodsNo,Convert(char(8),a.InputDate,112)
go




if exists ( select * from sysobjects where name='VCasherReport' and type='v')
	drop view VCasherReport
go

Create View VCasherReport as
	Select Casher,max(RetailDate) inputdate, 
	sum(case paymode when 1 then amt else 0 end) as casher1,
	sum(case paymode when 2 then amt else 0 end) as casher2,
	sum(case paymode when 3 then amt else 0 end) as casher3,
	sum(case paymode when 4 then amt else 0 end) as casher4,
	sum(case paymode when 5 then amt else 0 end) as casher5,
	sum(case paymode when 6 then amt else 0 end) as casher6,
	sum(case paymode when 7 then amt else 0 end) as casher7,
	sum(case paymode when 8 then amt else 0 end) as casher8,
	sum(case paymode when 9 then amt else 0 end) as casher9,
	sum(case paymode when 10 then amt else 0 end) as casher10,
	sum(amt) as cashersum 
From casherreport
group by casher,convert(char(8),RetailDate,112)
go

/* end Create View */




/*===============================================*/
/*   			Year Procedure 		 */
/*    			Created 2000.07.08       */
/*===============================================*/

/*-----------------------------------------------------------------------------*/
if exists ( Select * from sysobjects where name='up_InsertRetail' and type='p')
	drop procedure up_InsertRetail
go

Create procedure up_InsertRetail
  @Goodsno	Tgoods,
  @DeptNo	Tdept,
  @SaleMan	TStaff,
  @Casher	TStaff,
  @PosNo	Varchar(4),
  @InvoiceNo	Varchar(12),
  @ReceiptNo    Varchar(12),
  @BusinessFlag Char(1),
  @PayMode	Tinyint,
  @CustNo	TVendor,
  @Qty		TQty,
  @Amt		TAmt,
  @DisCount	TDisCount,
  @RetailPrice	TPrice,
  @Disamt	TAmt,
  @BatchNo	TBatch,
  @Ud1		Varchar(10),
  @Ud2	        Varchar(10),
  @ud3		Varchar(10),
  @Ud4		Varchar(10)
 As
  
Declare @CostPrice TPrice  /*成本价*/
Declare @Cost	char(1)		/*成本计价方法*/

Select @Cost=value from sysparams Where name='cost'

Select @CostPrice = case  @Cost When  '2'  then CurCostPrice 
		                else LastCostPrice
                    end
From Stock Where DeptNO=@DeptNo and GoodsNO=@GoodsNo

      Insert Retail
	 ( GoodsNo,DeptNo,SaleMan,Casher,PosNo,InvoiceNo,ReceiptNo,BusinessFlag,PayMode,
	  CustNo,Qty,Amt,DisCount,RetailPrice,CostPrice,DisAmt,BatchNo,ud1,ud2,ud3,ud4) 
	Values (@GoodsNo,@DeptNo,@Saleman,@Casher,@PosNo,@InVoiceNo,@ReceiptNo,@BusinessFlag,@PayMode,
	  @Custno,@Qty,@Amt,@DisCount,@RetailPrice,@CostPrice,@DisAmt,@BatchNo,@ud1,@ud2,@ud3,@ud4 )
go

Print "Procedure up_InsertRetail is Created"
go

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

Create Proc up_Processdayretail (@InputDate varchar(8))
as
Declare @GoodsNo 	TGoods
Declare @DeptNo 	Tdept
Declare @Amt		TAmt
Declare @DisAmt	TAmt
Declare @Qty		TQty
Declare @CostAmt	TAmt
Declare @CostFlag	char(1)
Declare @CostRatio	numeric(10,3)

Update a set a.CostPrice=(a.amt*b.CostRatio)/nullif(a.Qty ,0)
from Retail a,Goods b
Where a.goodsno=b.goodsno and b.CostFlag='1' and a.Qty<>0 and Convert(char(8),a.InputDate,112)=@Inputdate

Declare cursor_retail Cursor for 
   select deptno,goodsno,sum(amt),sum(round(costprice*qty,2)),sum(qty),sum(DisAmt)
   from retail
   Where Convert(char(8),inputdate,112)=@InputDate
   group by deptno,goodsno

Open cursor_retail
Fetch cursor_retail into @deptNo,@GoodsNo,@Amt,@CostAmt,@Qty,@DisAmt
while @@Fetch_Status=0
  begin
   Select @CostFlag=isnull(CostFlag,'0'),@CostRatio=isnull(CostRatio,0) from goods Where goodsno=@GoodsNo
   update stock
	set retailqty = isnull(retailqty,0) + @qty,
	    retailamt = isnull(retailamt,0) + @amt,
	    retaildisamt = isnull(retaildisamt,0) + @disamt,
	    RetailCost = Isnull(RetailCost,0) + @CostAmt,
	    curqty = Case @CostFlag When '0' then isnull(curqty,0) - @qty
		     end,
	    curcostamt = Case @CostFlag When '0' then isnull(curcostamt,0) - @CostAmt
			 end,
	    ModifyDate=GetDate()
   where goodsno = @goodsno and deptno=@DeptNO
Fetch cursor_retail into @deptNo,@GoodsNo,@Amt,@CostAmt,@Qty,@DisAmt
end
close cursor_retail
dealLocate cursor_retail

go


print 'proc up_processdayretail is created!'

go   


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

CREATE PROC up_modify_price
	@today char(8),
	@goodsno Tgoods,
	@deptno Tdept,
	@oretailprice Tprice,
	@nretailprice Tprice,
	@omemberprice Tprice,
	@nmemberprice Tprice,
	@alldept char(1),
	@startdate char(8),
	@enddate char(8),
	@ospecialflag char(1),
	@nspecialflag char(1)
AS
Declare @LocalFlag  char(1)
Declare @EveryPoints  numeric(10,3)

Select @LocalFlag=LocalFlag from Dept where DeptNo=@deptNo
Select @EveryPoints=Cast(value as numeric(10,3)) from sysparams where name='everypoints'
if @EveryPoints=0  Select @EveryPoints=10

if @enddate is null
    Select @EndDate=convert(char(8),dateadd(dd,5,getdate()),112)
if @enddate<@today  Return

if @startdate=@today and (@alldept='1' or @LocalFlag='1')
   begin
     update goods set 
	retailprice=@nretailprice,memberprice=@nmemberprice,spriceflag=@nspecialflag,
        Points=Round(@NRetailPrice/@EveryPoints,3)
	where goodsno=@goodsno
     update stock set retailprice=@NRetailPrice where goodsno=@goodsno
     update deptPs set
         retailprice=@nretailprice,memberprice=@nmemberprice,spriceflag=@nspecialflag 
	where goodsno=@goodsno
   end

if @enddate=@today  and (@alldept='1' or @LocalFlag='1')
    begin
     update goods set 
	retailprice=@oretailprice,memberprice=@omemberprice,spriceflag=@ospecialflag, 
        Points=Round(@ORetailPrice/@EveryPoints,3)
	where goodsno=@goodsno
     /* added by zhiping bao */
     update stock set retailprice=@ORetailPrice where goodsno=@goodsno
     update deptPs set
         retailprice=@oretailprice,memberprice=@Omemberprice,spriceflag=@Ospecialflag,
         ModifyDate=Getdate()	where goodsno=@goodsno
     end

go

print 'up_modify_price is created already.'
go

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

CREATE PROC up_adjust_account
AS
declare @today char(8)
declare @goodsno Tgoods
declare @deptno Tdept
declare @oretailprice Tprice
declare @nretailprice Tprice
declare @omemberprice Tprice
declare @nmemberprice Tprice
declare @alldept char(1)
declare @startdate char(8)
declare @enddate char(8)
declare @ospecialflag char(1)
declare @nspecialflag char(1)
declare @auditflag char(1)

if datepart(hh,getdate())<12
	select @today=convert(char(8),getdate(),112)
else
	select @Today=Convert(char(8),dateadd(dd,2,getdate()),112)

declare adjust_cursor Cursor for 
select  goodsno,deptno,convert(char(8),startdate,112),convert(char(8),enddate,112),
	oretailprice,nretailprice,omemberprice,nmemberprice,ospecialflag,nspecialflag,
	alldepart
from adjustdetail,adjust 
where (adjustdetail.adjustno=adjust.adjustno) and (auditflag='1')   
and (convert(char(8),startdate,112)=@today or convert(char(8),enddate,112)=@today) 

open adjust_cursor
fetch next from adjust_cursor 
into  	@goodsno,@deptno,@startdate,@enddate,@oretailprice,@nretailprice,
	@omemberprice,@nmemberprice,@ospecialflag,@nspecialflag,@alldept

while @@fetch_status=0
begin
	exec up_modify_price
	@today,@goodsno,@deptno,@oretailprice,@nretailprice,@omemberprice,
	@nmemberprice,@alldept,@startdate,@enddate,@ospecialflag,@nspecialflag 

	fetch next from adjust_cursor 
	into  	@goodsno,@deptno,@startdate,@enddate,@oretailprice,@nretailprice,
		@omemberprice,@nmemberprice,@ospecialflag,@nspecialflag,@alldept
end 
close adjust_cursor
deallocate adjust_cursor
go

print 'up_adjust_account is created already.'
go

if (select count(*) from sysobjects where type='p' and name='up_modify_import_price')>0
	drop proc up_modify_import_price
go

CREATE PROC up_modify_import_price
	@today char(8),
	@goodsno Tgoods,
	@vendorno Tvendor,
	@oprice Tprice,
	@nprice Tprice,
	@startdate char(8),
	@enddate char(8),
	@stockflag char(1)
AS
declare @taxratio float
declare @Price TPrice

if @enddate is null
    Select @EndDate=convert(char(8),dateadd(dd,2,getdate()),112)
if @enddate<@today  Return
select @taxratio=taxratio from goods where goodsno=@goodsno
if @startdate=@today 
 	  Select @Price=@NPrice
else if @EndDate=@today
	   Select @Price=@OPrice
else Return
   
     update goods set 
		evaluateprice=@Price,nevaluateprice=@price/(1+@taxratio)
		where goodsno=@goodsno
    
if exists  ( select 1 from vendorprice where goodsno=@goodsno and vendorno=@vendorno)
    Update Vendorprice set TaxPrice=@Price,NTaxPrice=@Price/(1+@TaxRatio) Where Goodsno=@GoodsNo and VendorNo=@VendorNO
else
    Insert VendorPrice (vendorno,goodsno,taxprice,ntaxprice) values (@vendorNo,@goodsno,@price,@price/(1 + @TaxRatio))

   GO

print 'up_Modify_import_price is Created ok!'
go

if (select count(*) from sysobjects where type='p' and name='up_adjust_import_account')>0
	drop proc up_adjust_import_account 
go

/*进价调整*/
CREATE PROC up_adjust_import_account
AS
declare @today char(8)
declare @goodsno Tgoods
declare @vendorno Tvendor
declare @oprice Tprice
declare @nprice Tprice
declare @startdate char(8)
declare @enddate char(8)
declare @stockflag char(1)
declare @auditflag char(1)