viewandproc.sql

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

go

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
@saccount_date char(8),
@eaccount_date char(8)
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
set nocount on

declare my_cursor cursor for
select auditdate,ImportNo,VendorNo,Amt from import
		where convert(char(8),auditdate,112) >= @saccount_date
			and convert(char(8),auditdate,112) <= @eaccount_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)>=@saccount_date
          and convert(char(8),i.auditdate,112)<=@eaccount_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
@saccount_date char(8),
@eaccount_date char(8)
as
declare @GoodsNo Tgoods
declare @RetailQty Tqty
set nocount on
begin transaction
declare retail_account cursor for
	select goodsno,sum(isnull(qty,0)) from deptretail 
        where convert(char(8),retaildate,112)>=@saccount_date
              and convert(char(8),retaildate,112)<=@eaccount_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,Vimport 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

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 importstat(deptno,groupno,classno,importdate,amt,ntaxamt,qty)
select a.inputdept,c.groupno,c.classno,convert(char(8),a.auditdate,112),sum(b.amt),sum(b.ntaxamt),sum(b.qty)
from goods c,import a,importdetail b
where a.importno=b.importno and b.goodsno=c.goodsno and convert(char(8),a.auditdate,112)=@inputdate and a.AuditFlag='1'
group by a.inputdept,c.groupno,c.classno,convert(char(8),a.auditdate,112)


/*品类进货统计*/
insert into categoryimportstat(categoryno,importdate,amt,ntaxamt,qty)
select isnull(c.categoryno,'99999999'),convert(char(8),a.auditdate,112),sum(b.amt),sum(b.ntaxamt),sum(b.qty)
from goods c,import a,importdetail b
where a.importno=b.importno and b.goodsno=c.goodsno and convert(char(8),a.auditdate,112)=@inputdate and a.AuditFlag='1'
group by isnull(c.categoryno,'99999999'),convert(char(8),a.auditdate,112)


/*厂商进货统计*/
insert into vendorimportstat(vendorno,importdate,amt,ntaxamt,qty)
select isnull(c.vendorno,'00000000'),convert(char(8),a.auditdate,112),sum(b.amt),sum(b.ntaxamt),sum(b.qty)
from goods c,import a,importdetail b
where a.importno=b.importno and b.goodsno=c.goodsno and convert(char(8),a.auditdate,112)=@inputdate and a.AuditFlag='1'
group by isnull(c.vendorno,'00000000'),convert(char(8),a.auditdate,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'),c.taxratio,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),c.retailmode,c.taxratio


/*厂商销售统计*/
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_InserthRetailFromRetail' and type='p')
  drop Proc up_InserthRetailFromRetail
go

CREATE PROCEDURE up_InserthRetailFromRetail 
AS
 Declare @day Int
 if datepart(hh,getdate())<19 
    Select @day=-1
 else
    Select @day=0

 if exists ( select 1 from hRetail Where Convert(char(8),InputDate,112)=Convert(char(8),DAteAdd(dd,@Day,Getdate()),112)) Return

 Insert hRetail
	 ( GoodsNo,DeptNo,SaleMan,Casher,PosNo,InvoiceNo,ReceiptNo,BusinessFlag,PayMode,
	  CustNo,Qty,Amt,DisCount,RetailPrice,CostPrice,DisAmt,BatchNo,ud1,ud2,ud3,ud4,inputdate) 
Select GoodsNo,DeptNo,SaleMan,Casher,PosNo,InvoiceNo,ReceiptNo,BusinessFlag,PayMode,
	  CustNo,Qty,Amt,DisCount,RetailPrice,CostPrice,DisAmt,BatchNo,ud1,ud2,ud3,ud4,inputdate
From Retail
Where Convert(char(8),inputdate,112)=Convert(char(8),dateadd(dd,@day,getdate()),112)

go

print 'proc up_InserthRetailFromRetail 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 PosRetail
 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_ProcessDayEnd' and type='p')
  drop Proc up_ProcessDayEnd
go

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

Declare c1 cursor for select ProcessSql from ProcessDayEnd where ProcessFlag='1'
Open c1
Fetch c1 into @Mysql

While @@Fetch_Status=0
  begin
    Execute @Mysql
    Fetch c1 into @mysql
  end
close c1
deallocate c1
go

print 'up_ProcessDayEnd is created!'
go

Insert ProcessDayEnd (processsql,processflag,Remark)
Values ('ProcessPosAndSuspendRetail','1',null)
go

Insert ProcessDayEnd (processsql,processflag,Remark)
values ('Up_insertCasherReport','1',null)
go

Insert ProcessDayEnd (ProcessSql,processFlag,Remark)
Values ('Up_InserthRetailFromRetail','1',null)
go

Insert ProcessDayEnd (ProcessSql,processFlag,Remark)
Values ('Up_ProcessStat','1',null)
go



/* End Create Procedure */