viewandproc.sql

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

/*=============================================*/
/*  System Base Table  View  in  Year DataBase */
/*=============================================*/
use sypos2000
go
if exists ( select * from sysobjects where name='up_CreateViewForSyposTable' and type='p')
	drop procedure up_CreateViewForsypostable
go

Create Procedure up_CreateViewForsyposTable as
Declare @TableName Varchar(50)
Declare @Mysql varchar(300)
Declare c1 Cursor for Select name from sypos..sysobjects where type='u'
open c1
Fetch c1 into @TableName
While @@Fetch_Status=0
   begin
      if exists ( select * from sysobjects where name = @TableName and type='v')
       begin
        select @mysql="drop view " + @TableName
	execute (@Mysql)
       end
      if not exists ( select * from sysobjects where name=@TableName)
        begin
         select @mysql="Create view " + @TableName + " as select * from sypos.." + @TableName
         Execute (@Mysql)
        end
    Fetch c1 into @TableName
   end
close c1
DealLocate c1
go

execute up_CreateViewforsyposTable
go

if exists ( select 1 from sysobjects where name='vBarCodes' and type='v')
   drop view VBarCodes
go

Create view VBarCodes as 
select PackGoodsNo goodsno,name,shortname,barcode,userdefno,groupno,Classno,Productid,statno,spec,
	RetailPrice,MemberPrice,SPriceFlag,Points,UseFlag 
From PackGoods
union
select goodsno,name,shortname,barcode,userdefno,groupno,Classno,Productid,statno,spec,
	RetailPrice,MemberPrice,SPriceFlag,Points,UseFlag
From Goods
union
select b.goodsno,a.name,a.shortname,b.barcode,a.userdefno,a.groupno,a.Classno,a.Productid,a.statno,a.spec,
	a.RetailPrice,a.MemberPrice,a.SPriceFlag,a.Points,a.UseFlag
From Goods a,MultiGoods b
where a.goodsno=b.goodsno

go

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

Create View vGoodsTemp as Select a.goodsno,b.ClassNo,b.GroupNo,b.VendorNO,sum(a.qty) qty from GoodsTemp a ,Goods b
group by a.goodsno,b.ClassNo,b.GroupNo,b.VendorNo

go


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

Create View StaffAuth  as 
 select operator,name func from operatorrights

go

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

Create view VCustPrice as select a.goodsno,a.barcode,a.userdefno,
a.groupno,a.classno,a.spec,a.name,a.shortname,a.model,a.productid,
a.RetailPrice,a.PackRatio,a.TaxRatio,a.EvaluatePrice,a.nEvaluatePrice,
a.useflag,b.lastCostPrice,b.initcostprice,b.curcostprice,b.CurQty,
c.price,b.DeptNo,c.custno 
from Goods a,stock b,custprice c 
where a.goodsno=b.goodsno and a.goodsno=c.goodsno
go

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

Create view VChecks as 
SELECT  checks.GOODSNO , goods.barcode, checks.ACCTQTY ,checks.REALQTY ,checks.ACCTAMT ,
        checks.REALAMT , Goods.NAME ,   Goods.GROUPNO , Goods.CLASSNO , checks.RETAILPRICE, 
	Checks.DeptNO, Checks.AcctQty * Checks.RetailPrice RetailAcctAmt,
	Checks.RealQty * Checks.RetailPrice RetailRealAmt,
	isnull(Checks.RealQty,0) - isnull(AcctQty,0) balanceqty,
	(isnull(Checks.RealQty,0) - isnull(AcctQty,0)) * checks.RetailPrice RetailBalanceAmt
        FROM Goods ,           checks     
        WHERE ( Goods.GOODSNO = checks.GOODSNO )
go


if exists ( Select * from SysObjects Where Name='VCostPrice' and Type='V' )
   Drop View VCostPrice
go
Create view VCostPrice as select a.goodsno,a.barcode,a.userdefno,
a.groupno,a.classno,a.spec,a.name,a.shortname,a.model,a.productid,
a.RetailPrice,a.SalePrice,a.PackRatio,a.TaxRatio,a.EvaluatePrice,a.nEvaluatePrice,
b.lastCostPrice,b.initcostprice,b.curcostprice,b.DeptNo,b.CurQty,a.useflag
from Goods a,stock b where a.goodsno=b.goodsno 
go

if exists ( Select * from SysObjects Where Name='VVendorPrice' and Type='V' )
   Drop View VVendorPrice
go
Create view VvendorPrice as select a.goodsno,a.barcode,a.userdefno,
a.groupno,a.classno,a.spec,a.name,a.shortname,a.model,a.productid,
a.RetailPrice,a.SalePrice,a.PackRatio,a.TaxRatio,a.EvaluatePrice,
a.nEvaluatePrice,a.useflag,b.lastCostPrice,b.initcostprice,b.DeptNo,
b.curcostprice,b.CurQty,c.taxprice,c.vendorno
from Goods a,stock b,VendorPrice c 
where a.goodsno=b.goodsno and a.goodsno=c.goodsno
go

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

Create View VDeptPrice as  
Select  a.GoodsNo,a.DeptNo,a.RetailPrice,a.MemberPrice,
	a.SpriceFlag,b.Name,b.GroupNo,b.ClassNo,b.Model,
	b.Spec,b.Productid,b.Packratio,b.SalePrice,b.TaxRatio,
	b.EvaluatePrice,b.NEvaluatePrice,b.Grade,b.RetailUnit,
	b.PackUnit,b.RetailMode,b.UseFlag,b.userdefno
from DeptPS a,Goods b where  a.goodsno=b.goodsno  

go


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

Create view VDeptStock as 
	select * from deptstock
  union
        select a.year,a.month,b.* from months a,stock b where getdate()<=a.enddate and getdate()>=a.startdate
go

/* ============================================================ */
/*   View: VStock                                               */
/* ============================================================ */
if exists ( Select * from SysObjects Where Name='VStock' and Type='V' )
   Drop View VStock
go
Create view VStock as
select a.*, b.Name, b.GroupNo,b.ClassNo, b.CategoryNo,b.UseFlag, b.SPriceFlag, 
       b.TaxRatio,b.EvaluatePrice,b.NevaluatePrice
from Stock a, Goods b
where a.GoodsNo = b.GoodsNo
go

/* View For Realtime Query */
/*  pos view */
if exists ( Select * from SysObjects Where Name='VPos' and Type='V' )
   Drop View VPos
go

Create View VPos as
  Select PosNo,Sum(Amt) Amt,Sum(Qty) Qty,Sum(Qty * CostPrice) CostAmt,Sum(DisAmt) DisAmt,
	Count(distinct ReceiptNO) ReceiptCount,Convert(char(8),inputdate,112) inputdate
  From Retail
  Group by PosNo,Convert(char(8),inputdate,112)
go

/* Casher View */

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

Create View VCasher as
Select Casher,Sum(Amt) Amt,Sum(Qty) Qty,Sum(Qty * CostPrice) CostAmt,Sum(DisAmt) DisAmt,
          Count(Distinct ReceiptNo) ReceiptCount,Convert(char(8),inputdate,112) inputdate
From Retail
Group by Casher,Convert(char(8),inputdate,112)
go

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

Create View VHours as
Select DatePart(hh,InputDate) Hours,Sum(Amt) Amt,Sum(Qty) Qty,Sum(Qty * CostPrice) CostAmt,
	Sum(DisAmt) DisAmt,Count(Distinct ReceiptNo) ReceiptCount,Convert(char(8),inputdate,112) inputdate
From Retail
Group by DatePart(hh,InputDate),Convert(char(8),inputdate,112) 
go

/* end */

/* View for DayReport (by deptno,groupno and auditdate) */
/*begin */
if exists ( Select * from SysObjects Where Name='VGroupSale' and Type='V' )
   Drop View VGroupSale
go
Create View VGroupSale as 
	select Convert(char(8),a.AuditDate,112) AuditDate,
	a.Deptno,
	sum(b.Amt) Amt,
	sum(b.Ntaxamt) NTaxAmt,
	Sum(b.Qty) Qty,
	sum(Round(b.RetailPrice*b.Qty,2)) RetailAmt,
	Sum(Round((b.RetailPrice - b.Price) * b.Qty,2)) SaleDisAmt,
	Sum(Round(b.CostPrice * b.Qty,2)) CostAmt, 
	Sum(round(b.CostPrice/(1 + c.TaxRatio) * b.Qty,2)) NCostAmt, 
	c.Groupno
   from Sale a,SaleDetail b,Goods c
   Where a.SaleNO=b.SaleNo and b.GoodsNo=c.Goodsno and a.AuditFlag='1'
   Group by a.DeptNO,c.GroupNo,Convert(char(8),a.AuditDate,112)
go

if exists ( Select * from SysObjects Where Name='VGroupImport' and Type='V' )
   Drop View VGroupImport
go
Create View VGroupImport as 
	select Convert(char(8),a.AuditDate,112) AuditDate,
        a.InputDept,
	Sum(Round(b.RetailPrice * b.Qty,2)) RetailAmt,
	Sum(b.Amt) Amt,
	Sum(b.NtaxAmt) NTaxAmt,
	Sum(b.Qty) Qty,
	c.GroupNo
   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,c.GroupNo,Convert(char(8),a.AuditDate,112)
go


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

Create View VDestGroupAlloc 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(round(b.CostPrice/(1 + c.TaxRatio) * b.Qty,2)) NCostAmt,
	Sum(b.Amt) Amt,
	Sum(b.NtaxAmt) NtaxAmt,
	Sum(b.Qty) Qty,
	c.GroupNo
From Alloc a,AllocDetail b,Goods c
Where a.AllocNo=b.AllocNO and b.GoodsNo=c.GoodsNo and a.AuditFlag='1'
Group by a.DestDept,c.GroupNo,Convert(char(8),a.ReceiveDate,112)
go

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

Create View VSrcGroupAlloc 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(round(b.CostPrice/(1 + c.TaxRatio) * b.Qty,2)) NCostAmt,
	Sum(b.Amt) Amt,
	Sum(b.NtaxAmt) NtaxAmt,
	Sum(b.Qty) Qty,
	c.GroupNo
From Alloc a,AllocDetail b,Goods c
Where a.AllocNo=b.AllocNO and b.GoodsNo=c.GoodsNo and a.AuditFlag='1'
Group by a.SrcDept,c.GroupNo,Convert(char(8),a.ReceiveDate,112)
go


if exists ( Select * from SysObjects Where Name='VGroupLoss' and Type='V' )
   Drop View VGroupLoss
go
Create View VGroupLoss 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(round(b.CostPrice/(1 + c.TaxRatio) * b.Qty,2)) NTaxAmt,
	Sum(b.Qty) Qty,
	c.GroupNo
	From Loss a,LossDetail b,Goods c
Where a.LossNo=b.LossNo and b.GoodsNo=c.GoodsNo and a.AuditFlag='1'
Group by a.DeptNO,c.GroupNo,Convert(char(8),a.AuditDate,112)
go


if exists ( Select * from SysObjects Where Name='VGroupRetail' and Type='V' )
   Drop View VGroupRetail
go
Create View VGroupRetail 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,
	Sum(round(a.CostPrice/(1 + b.TaxRatio) * a.Qty,2)) NCostAmt,
	b.GroupNo
From Retail a,Goods b
Where a.GoodsNo=b.GoodsNo
Group by a.DeptNO,b.GroupNo,Convert(char(8),a.InputDate,112)
go


if exists ( Select * from SysObjects Where Name='VGroupStock' and Type='V' )
   Drop View VGroupStock
go
Create View VGroupStock as
 Select b.Groupno,a.deptNO,
	Sum(a.CurCostAmt) CurCostAmt,
	Sum(Round(a.CurQty * a.RetailPrice,2)) CurRetailAmt,
	Sum(round(a.CurCostAmt / (1 + b.TaxRatio),2)) CurNCostAmt
 From Stock a,Goods b
 Where a.goodsno=b.goodsNo 
 Group by b.GroupNo,a.DeptNo
go

if exists ( Select * from SysObjects Where Name='VGroupDayStock' and Type='V' )
   Drop View VGroupDayStock
go
Create View VGroupDayStock as
 Select b.Groupno,a.deptNO,a.date,
	Sum(a.CurAmt) CurCostAmt,
	Sum(a.CurQty * a.RetailPrice) CurRetailAmt,
	Sum(a.CurAmt / (1 + b.TaxRatio)) CurNCostAmt
 From DayStock a,Goods b
 Where a.goodsno=b.goodsNo 
 Group by b.GroupNo,a.DeptNo,a.date
go

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


Create View VGroupChecks as 
 select b.deptno,a.GroupNo,Convert(char(8),b.Enddate,112) EndDate,
	Sum(Round(b.retailPrice * (isnull(b.RealQty,0) - Isnull(b.AcctQty,0)),2)) CheckRetailAmt,
        Sum(Round(b.CostPrice * isnull(b.RealQty,0),2) - isnull(b.AcctAmt,0)) CheckAmt,
        Sum(Round(b.CostPrice * isnull(b.RealQty,0),2)/(1 + a.Taxratio) - ISnull(b.AcctAmt,0)/(1 + a.TaxRatio)) NCheckAmt
From ChecksBackup b,Goods a
Where b.GoodsNo=a.GoodsNo
Group by b.DeptNo,a.GroupNo,Convert(char(8),b.EndDate,112)
go

/* end */



if exists ( Select * from SysObjects Where Name='vGoodsSale' and Type='V' )
   Drop View vGoodsSale
go
Create View vGoodsSale as 
	select Convert(char(8),a.AuditDate,112) AuditDate,
	a.Deptno,
	sum(b.Amt) Amt,
	sum(b.Ntaxamt) NTaxAmt,
	Sum(b.Qty) Qty,
	sum(Round(b.RetailPrice*b.Qty,2)) RetailAmt,
	Sum(Round((b.RetailPrice - b.Price) * b.Qty,2)) SaleDisAmt,
	Sum(Round(b.CostPrice * b.Qty,2)) CostAmt, 
	b.GoodsNo
   from Sale a,SaleDetail b
   Where a.SaleNO=b.SaleNo  and a.AuditFlag='1'
   Group by a.DeptNO,b.GoodsNo,Convert(char(8),a.AuditDate,112)
go

if exists ( Select * from SysObjects Where Name='vGoodsImport' and Type='V' )
   Drop View vGoodsImport
go
Create View vGoodsImport as 
	select Convert(char(8),a.AuditDate,112) AuditDate,
        a.InputDept,
	Sum(Round(b.RetailPrice * b.Qty,2)) RetailAmt,
	Sum(b.Amt) Amt,
	Sum(b.NtaxAmt) NTaxAmt,
	Sum(b.Qty) Qty,