viewandproc.sql

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

create procedure up_importcheck_update_by_checkviaamt
@VendorNo char(8),@Amt TAmt
as
declare @ImportNo TReceipt
declare @GoodsNo TGoods
declare @ImportQty TQty
declare @ExportQty TQty
declare @TaxPrice numeric(12,4)
declare @CheckQty TQty
declare @CheckAmt numeric(12,2)
declare @NoCheckQty numeric(12,3)
declare @NoCheckAmt numeric(12,2)

if @Amt > 0 
begin
	declare my_cursor cursor for
		select ImportNo,GoodsNo,isnull(ImportQty,0),isnull(ExportQty,0),isnull(TaxPrice,0),isnull(CheckQty,0),isnull(CheckAmt,0),isnull(NoCheckQty,0),isnull(NoCheckAmt,0) from Import_To_Check
		where VendorNo = @VendorNo and round((isnull(ImportQty,0) + isnull(ExportQty,0))*TaxPrice,2) > isnull(CheckAmt,0)
		order by ImportNo,GoodsNo
	OPEN my_cursor
	if @@error <> 0 return
	FETCH NEXT from my_cursor into @ImportNo,@GoodsNo,@ImportQty,@ExportQty,@TaxPrice,@CheckQty,@CheckAmt,@NoCheckQty,@NoCheckAmt
	while @@fetch_status = 0
   begin 
		if @Amt > (round((isnull(@ImportQty,0) + isnull(@ExportQty,0))*@TaxPrice,2) - @CheckAmt)  
		   begin
				update Import_To_Check
					set CheckQty = isnull(ImportQty,0) + isnull(ExportQty,0),
						 CheckAmt = round((isnull(ImportQty,0) + isnull(ExportQty,0))*TaxPrice,2),
						 NoCheckQty = 0,
						 NoCheckAmt = 0
   	   	WHERE current of my_cursor
				select @Amt = @Amt - @NoCheckAmt
   		end
		else
			begin
				update Import_To_Check
					SET CheckQty = isnull(CheckQty,0) + @Amt / TaxPrice,
						 CheckAmt = isnull(CheckAmt,0) + @Amt,
						 NoCheckQty = isnull(NoCheckQty,0) - @Amt / TaxPrice,
						 NoCheckAmt = isnull(NoCheckAmt,0) - @Amt
		      WHERE current of my_cursor
				select @Amt = 0
			   break
			end
		FETCH NEXT from my_cursor into @ImportNo,@GoodsNo,@ImportQty,@ExportQty,@TaxPrice,@CheckQty,@CheckAmt,@NoCheckQty,@NoCheckAmt
	end
end
close my_cursor
deallocate my_cursor
go

print 'up_importcheck_update_by_checkviaamt is created'
go



/*供货商应付帐流水初始值录入处理*/
if (select count(*) from sysobjects where name='up_payable_init') > 0
	drop procedure up_Payable_init
go

create procedure up_Payable_init 
@VendorNo TVendor,@businessdate datetime,@initamt TAmt 
as 
declare @tmp_value TAmt
declare @addamt TAmt
declare @subamt TAmt  

declare mycursor CURSOR FOR  
	select isnull(addamt,0),isnull(subamt,0)  from Payable
	where VendorNo = @VendorNo and businessdate > @businessdate order by businessdate
OPEN mycursor  
FETCH NEXT from mycursor into @addamt,@subamt
select @tmp_value = @initamt  
while @@fetch_status = 0 
   begin  
	 	select @tmp_value = @tmp_value + @addamt - @subamt
		update Payable set BalanceAmt = @tmp_value WHERE current of mycursor  
	 	FETCH NEXT from mycursor into @addamt,@subamt
   end 
close mycursor 
deallocate mycursor 
go

print 'up_Payable_Init is Created!'
go


/*进货结算登帐*/
if(select count(*) from sysobjects where type='p' and name='up_import_account')>0
	drop proc up_import_account
go

create proc up_import_account
as
declare @importno Treceipt
declare @vendorno Tvendor
declare @importdate Tdate
declare @goodsno Tgoods
declare @ntaxprice Tprice
declare @taxprice Tprice
declare @retailprice Tprice
declare @importqty Tqty
declare @taxamt TAmt
declare @balanceamt TAmt
Declare @account_date char(8)
Declare @day int

if datepart(hh,getdate())<19 
   Select @Day=-1
else
   Select @Day=0

Select @Account_date=Convert(char(8),DateAdd(dd,@day,GetDate()),112)

declare my_cursor cursor for
select auditdate,ImportNo,VendorNo,Amt from import
		where convert(char(8),auditdate,112) = @account_date
			and auditflag='1'
		order by auditdate 
open my_cursor
fetch next from my_cursor into @importdate,@importno,@vendorno,@Taxamt
while @@fetch_status = 0 
begin
	select @balanceamt = isnull(balanceamt,0) from Payable where id = (select max(id) from Payable where vendorno = @vendorno and businesstype<>'期初值')
	if @@rowcount = 0 select @balanceamt = 0
   insert into Payable(vendorno,businessdate,businesstype,invoiceno,addamt,balanceamt)
		values(@vendorno,@importdate,'进货',@importno,@taxamt,@taxamt + @balanceamt)
	fetch next from my_cursor into @importdate,@importno,@vendorno,@Taxamt
end
close my_cursor
deallocate my_cursor

declare  import_account cursor for 
    select i.importno,i.vendorno,i.auditdate,d.goodsno,d.ntaxprice,d.taxprice,
	   d.retailprice,d.auditqty from import i,importdetail d
    where i.importno=d.importno and i.auditflag='1' 
          and convert(char(8),i.auditdate,112)=@account_date
    order by i.auditdate asc
open import_account
fetch next from import_account 
into @importno,@vendorno,@importdate,@goodsno,@ntaxprice,@taxprice,@retailprice,@importqty
while @@fetch_status=0
begin
	if @importqty>0
		insert import_to_check(VendorNo,ImportNo,ImportDate,GoodsNo,NTaxPrice,
				       TaxPrice,RetailPrice,ImportQty,NoCheckQty,NoCheckAmt)
		values(@vendorno,@importno,@importdate,@goodsno,@ntaxprice,
		       @taxprice,@retailprice,@importqty,@importqty,round(@importqty*@Taxprice,2))
	else
		insert import_to_check(VendorNo,ImportNo,ImportDate,GoodsNo,NTaxPrice,
				       TaxPrice,RetailPrice,exportQty,NoCheckQty,NoCheckAmt)
		values(@vendorno,@importno,@importdate,@goodsno,@ntaxprice,
		       @taxprice,@retailprice,@importqty,@importqty,round(@importqty*@taxprice,2))

	exec up_importcheck_update_by_import @goodsno,@importno,@importqty,@vendorno

	fetch next from import_account 
	into @importno,@vendorno,@importdate,@goodsno,@ntaxprice,@taxprice,@retailprice,@importqty
end
close import_account
deallocate import_account
go

print 'up_Import_Account is Created!'
go

/*销售结算登帐*/
if(select count(*) from sysobjects where type='p' and name='up_retail_account')>0
	drop proc up_retail_account
go

create proc up_retail_account
as
declare @GoodsNo Tgoods
declare @RetailQty Tqty
Declare @account_date char(8)
Declare @day int

Set nocount on
if datepart(hh,getdate())<19 
   Select @Day=-1
else
   Select @Day=0

Select @Account_date=Convert(char(8),DateAdd(dd,@day,GetDate()),112)

begin transaction
declare retail_account cursor for
	select goodsno,sum(isnull(qty,0)) from deptretail 
        where convert(char(8),retaildate,112)=@account_date
        group by goodsno

open retail_account
fetch next from retail_account into @goodsno,@retailqty
while @@fetch_status=0
begin
	exec up_importcheck_update_by_retail @goodsno,@retailqty
   if @@error <> 0
   begin
		rollback transaction
		close retail_account
		deallocate retail_account
      return
	end
	fetch next from retail_account into @goodsno,@retailqty
end
close retail_account
commit transaction
deallocate retail_account

go

print 'proc up_retail_account is Created! '
go

/* End for 结算   Procedure   */

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


/*订货数量*/
CREATE proc up_UpdateOrderQtywithImportQty
as
 Declare @Day int
 Declare @Inputdate char(8)

   if datepart(hh,getdate())<19 
   	Select @Day=-1
   else
   	Select @Day=0

   Select @InputDate=Convert(char(8),Dateadd(dd,@day,getdate()),112)
   
   Update a set a.OrderQty=b.Qty - a.OrderQty 
   from TopandBottom a,VGoodsimport b
   Where a.goodsno=b.goodsno and a.deptNo=b.InputDept and b.auditDate=@Inputdate
   
   Update TopandBottom set OrderQty=0 where OrderQty <=0   
go

print ' up_UpdateOrderQtywithImportQty is created '
go

/*修正订货数量2*/
CREATE proc up_UpdateOrderQtywithAllocQty
as
 Declare @Day int
 Declare @Inputdate char(8)

   if datepart(hh,getdate())<19 
   	Select @Day=-1
   else
   	Select @Day=0

   Select @InputDate=Convert(char(8),Dateadd(dd,@day,getdate()),112)
   
   Update a set a.OrderQty=b.Qty - a.OrderQty 
   from TopandBottom a,VDestGoodsAlloc b
   Where a.goodsno=b.goodsno and a.deptNo=b.DestDept and b.auditDate=@Inputdate
   
   Update TopandBottom set OrderQty=0 where OrderQty <=0   
go

print ' up_UpdateOrderQtywithAllocQty is created '
go


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

/*日终统计过程*/
CREATE PROCEDURE up_ProcessStat
AS

Declare @day int
Declare @Inputdate char(8)

   if datepart(hh,getdate())<19 
   	Select @Day=-1
   else
   	Select @Day=0

Select @InputDate=Convert(char(8),Dateadd(dd,@day,getdate()),112)

/*配送统计*/
insert into allocstat(deptno,groupno,classno,alloctdate,amt,ntaxamt,qty)
select a.destdept,c.groupno,c.classno,convert(char(8),a.auditdate,112),sum(b.amt),sum(b.ntaxamt),sum(b.qty)
from alloc a,allocdetail b,goods c
where a.allocno=b.allocno and b.goodsno=c.goodsno and convert(char(8),a.auditdate,112)=@inputdate and a.AuditFlag='1'
group by a.destdept,c.groupno,c.classno,convert(char(8),a.auditdate,112)


/*品类销售统计*/
insert into categoryretailstat(categoryno,retaildate,retailmode,taxratio,amt,costamt,disamt)
select isnull(c.categoryno,'99999999'),convert(char(8),b.retaildate,112),isnull(c.retailmode,'0'),isnull(c.taxratio,0),sum(b.amt),sum(b.costamt),sum(b.disamt)
from goods c,deptretail b
where b.goodsno=c.goodsno and convert(char(8),b.retaildate,112)=@inputdate 
group by isnull(c.categoryno,'99999999'),convert(char(8),b.retaildate,112),isnull(c.retailmode,'0'),isnull(c.taxratio,0)


/*厂商销售统计*/
insert into vendorretailstat(vendorno,retaildate,retailmode,taxratio,amt,costamt,disamt)
select isnull(c.vendorno,'9999999999'),convert(char(8),b.retaildate,112),isnull(c.retailmode,'0'),isnull(c.taxratio,0),sum(b.amt),sum(b.costamt),sum(b.disamt)
from goods c,deptretail b
where b.goodsno=c.goodsno and convert(char(8),b.retaildate,112)=@inputdate
group by c.vendorno,convert(char(8),b.retaildate,112),isnull(c.retailmode,'0'),isnull(c.taxratio,0)


/*销售统计*/

insert into retailstat(deptno,GroupNo,ClassNO,taxRatio,RetailMode,RetailDate,Amt,CostAmt,DisAmt,Qty) 
select b.deptno,c.groupno,c.classno,isnull(c.taxratio,0),isnull(c.retailmode,'0'),convert(char(8),b.retaildate,112),sum(b.amt),sum(b.costamt),sum(b.disamt),sum(b.qty)
from goods c,deptretail b
where c.goodsno=b.goodsno and convert(char(8),b.retaildate,112)=@inputdate 
group by b.deptno,c.groupno,c.classno,isnull(c.taxratio,0),isnull(c.retailmode,'0'),convert(char(8),b.retaildate,112)


go

print 'up_processStat is Created!'
go

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

CREATE Proc up_InsertCasherreport 
 as
 Declare @day Int
 if datepart(hh,getdate())<19 
    Select @day=-1
 else
    Select @day=0

 Insert CasherReport (Casher,RetailDate,PayMode,Amt,DisAmt)
 select casher,Max(InputDate),paymode,sum(amt),sum(DisAmt)
 From Retail
 Where Convert(char(8),InputDate,112)=Convert(char(8),DAteAdd(dd,@Day,Getdate()),112)
 Group by Casher,PayMode

go

print ' proc up_insertCasherReport is created!'
go

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

Create proc up_UpdateGoodsNewFlag as
Declare @day int
Select @Day=Ascii(lower(value)) - Ascii('a') from sysparams where name='newflag'

Update Goods Set NewFlag='0' where newflag='1' and 
    Convert(char(8),InputDate,112)<Convert(char(8),DateAdd(dd,-@day,GetDate()),112)

go


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

/*日终处理过程*/
CREATE PROCEDURE up_ProcessDayEnd AS
Declare @mysql varchar(300)
Declare @Day int

if datepart(hh,getdate())<19 
   Select @Day=-1
else
   Select @Day=0

if exists (select 1 from CasherReport where Convert(char(8),Retaildate,112)=
           Convert(char(8),DateAdd(dd,@day,GetDate()),112)) Return   --已经处理过

Declare cursor_dayend cursor for select ProcessSql from ProcessDayEnd where ProcessFlag='1' order by SeqNo
Open cursor_dayend
Fetch cursor_dayend into @Mysql

While @@Fetch_Status=0
  begin
    Execute @Mysql
    Fetch cur