account.sql

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

/*============================================*/
/*	Object for account		      */
/*============================================*/

/* Import View For Account Voucher */
if exists ( select * from sysobjects where name='vimport_acct' and type='v')
  Drop view VImport_Acct
go

Create View VImport_Acct as 
	select Convert(char(8),a.InputDate,112) InputDate,
	        a.InputDept,a.VendorNo,
		Sum(b.RetailPrice * b.Qty) RetailAmt,
		Sum(b.Amt) Amt,
		Sum(b.NtaxAmt) NTaxAmt,
		Sum(b.Qty) Qty,
		isnull(c.RetailMode,'0') RetailMode
   From Import a,ImportDetail b,Goods c
Where a.ImportNo=b.ImportNo and b.GoodsNo=c.GoodsNo and a.Auditflag='1'
Group by  a.InputDept,a.VendorNo,Convert(char(8),a.InputDate,112),c.RetailMode
go

/* Procedure GetMaxPzh for account */
if exists ( select * from sysobjects where name='getmaxpzh' and type='p')
	drop procedure GetmaxPzh 
go

Create Procedure GetMaxPzh @pzh char(4) output
as
Select @pzh=max(pzh) from pz
select @pzh=Isnull(@pzh,'0000')
Select @pzh=Convert(char(4),Convert(integer,@pzh) + 1)

go

/* Procedure Get Account dqh,dwdm by Vendorno */
if exists ( select * from sysobjects where name='getaccountvendorno' and type='p')
	Drop Procedure GetAccountVendorNo
go

Create Procedure GetAccountVendorNo @VendorNo char(8),@dqh char(4) output,@dwdm varchar(8) output
As
Select @dqh=AreaCode,@dwdm=Seqno from Vendor Where VendorNo=@VendorNo
go

/* Account Voucher For Import */
if exists ( select * from sysobjects where name='CreateImportAccount' and type='p')
drop procedure CreateImportAccount
go

CREATE Procedure CreateImportAccount @AccountDate char(8) 
as

Declare @VendorNo TVendor
Declare @InputDept TDept
Declare @GroupNo TGroup
Declare @RetailAmt TAmt
Declare @Amt		TAmt
Declare @NTaxAmt	TAmt
Declare @AcctAmt	TAmt
Declare @SubjectNo	Varchar(12)
Declare @Abstract		Varchar(30)
Declare @Credit		Char(1)
Declare @VoucherType	Char(1)
Declare @Property	Char(1)
Declare @DataSource	Char(1)
Declare @pzh		char(4)
Declare @dqh		Char(4)
Declare @dwdm		Varchar(8)
Declare @pzrq		Tdate
Declare @RetailMode     char(1)

Select @pzrq=Convert(datetime,@AccountDate,112)
Declare c1 Cursor for select VendorNo,InputDept,RetailAmt,Amt,NTaxAmt,RetailMode from VImport_Acct Where Inputdate=@AccountDate
Open c1
Fetch c1 Into @VendorNo,@InputDept,@RetailAmt,@Amt,@NTaxAmt,@RetailMode
While @@Fetch_Status=0
  Begin
        /* Get Pzh */
	Execute GetMaxPzh @pzh output
    if @RetailMode='0'
       Declare c2 Cursor for select SubjectNo,Property,Abstract,Credit,VoucherType,DataSource From Voucherdefine Where VoucherName='import'
    else
       Declare c2 Cursor for select SubjectNo,Property,Abstract,Credit,VoucherType,DataSource From Voucherdefine Where VoucherName='importagen'

    Open c2
    Fetch c2 Into @SubjectNo,@Property,@Abstract,@Credit,@VoucherType,@DataSource
    While @@Fetch_Status=0
     begin
	    Select @AcctAmt=
   	   Case @DataSource 
   	   	 When '1' then @Amt 
		 When '2' then @NTaxAmt
                 When '3' then @Amt - @NTaxAmt
		 When '4' then @RetailAmt 
   		 When '5' then @RetailAmt - @Amt
		end
       if @Credit='1' 
 	 begin
		/* Get Account Complete SubjectNo */
      	  Insert Pz (pzrq,pzh,pzlx,zy,kmdm,j,bmdm)
	  Values (@pzrq,@pzh,@VoucherType,@Abstract,@SubjectNo,@AcctAmt,@InputDept)
	end
       else
	  if @Property='2'
	     begin
          		 /* Get Account dqh,dwdm */
	     Execute GetAccountVendorNo @VendorNo,@dqh output,@dwdm output
	     Insert Pz (pzrq,pzh,pzlx,zy,kmdm,d,bmdm,dqh,dwdm)
   	     Values (@pzrq,@pzh,@VoucherType,@Abstract,@SubjectNo,@AcctAmt,@InputDept,@dqh,@dwdm)
	     end
	  else
	     begin
   			/*Get Account Complete SubjectNo */
      	     Insert Pz (pzrq,pzh,pzlx,zy,kmdm,d,bmdm)
	     Values (@pzrq,@pzh,@VoucherType,@Abstract,@SubjectNo,@AcctAmt,@InputDept)
	     end		  
    Fetch c2 Into @SubjectNo,@Property,@Abstract,@Credit,@VoucherType,@DataSource
    end
   close c2  
   DealLocate c2
 Fetch c1 Into @VendorNo,@InputDept,@RetailAmt,@Amt,@NTaxAmt,@RetailMode
 end    
 Close c1
 DealLocate c1

go

print 'proc createimportaccout is created!'
go

if exists ( select 1 from sysobjects where name='CreateRetailAccount' and type='p')
   drop proc CreateRetailAccount
go

Create Proc CreateRetailAccount (@AccountDate char(8))
as

Declare @InputDept TDept
Declare @Amt		TAmt
Declare @NTaxAmt	TAmt
Declare @CostAmt 	TAmt
Declare @NCostAmt	TAmt
Declare @AcctAmt	TAmt
Declare @SubjectNo	Varchar(12)
Declare @Abstract		Varchar(30)
Declare @Credit		Char(1)
Declare @VoucherType	Char(1)
Declare @Property	Char(1)
Declare @DataSource	Char(1)
Declare @pzh		char(4)
Declare @pzrq		Tdate

Select @pzrq=Convert(datetime,@AccountDate,112)

Select @Amt=sum(a.amt),  @NTaxAmt=sum(round(a.amt/(1+b.TaxRatio),2)),
       @CostAmt=sum(round(a.CostPrice * a.qty,2)),
       @NCostAmt=sum(round(a.CostPrice * a.qty/(1+b.TaxRatio),2))
from Retail a,Goods b where  retail.goodsno=goods.goodsno and  Convert(char(8),a.Inputdate,112)=@AccountDate

		/* Get Pzh */
    Execute GetMaxPzh @pzh output
    Declare c2 Cursor for select SubjectNo,Property,Abstract,Credit,VoucherType,DataSource From Voucherdefine Where VoucherName='retail'
    Open c2
    Fetch c2 Into @SubjectNo,@Property,@Abstract,@Credit,@VoucherType,@DataSource
    While @@Fetch_Status=0
     begin
	    Select @AcctAmt=
   	   Case @DataSource 
   	   	 When '1' then @Amt 
		 When '2' then @NTaxAmt
                 When '3' then @CostAmt
		 When '4' then @NCostAmt
   		 When '5' then @Amt - @NTaxAmt
		end
       if @Credit='1' 
 	 begin
		/* Get Account Complete SubjectNo */
      	  Insert Pz (pzrq,pzh,pzlx,zy,kmdm,j)
	  Values (@pzrq,@pzh,@VoucherType,@Abstract,@SubjectNo,@AcctAmt)
	end
       else
         begin
   		/*Get Account Complete SubjectNo */
      	  Insert Pz (pzrq,pzh,pzlx,zy,kmdm,d)
	  Values (@pzrq,@pzh,@VoucherType,@Abstract,@SubjectNo,@AcctAmt)
         end		  
    Fetch c2 Into @SubjectNo,@Property,@Abstract,@Credit,@VoucherType,@DataSource
    end
   close c2  
   DealLocate c2

go

print 'proc CreateRetailForAccount is Created'
go


/* process data to account system */
if exists ( select 1 from sysobjects where name='CreatepzForAccount' and type='p')
   drop proc CreatepzForAccount
go

Create proc CreatepzForAccount
as
begin
   declare @date char(8)
   if datepart(hh,getdate())>19
       Select @Date=Convert(char(8),getdate(),112)
   else
      Select @date=Convert(char(8),dateadd(dd,-1,GetDate()),112)
   
   /* for Retail */
   Execute   CreateRetailAccount @Date
   
   /* for Import */
   Execute   CreateImportAccount @date
end
go

print 'proc CreatepzForAccount is Created'
go

/*   End Create Objects for Account   */