comyearp.sql

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

    return @err
  end
  insert cardconsume(CardNo,DeptNo,ModifyDate,Amt,Times,Points) 
         select CardNo,DeptNo,ModifyDate,Amt,Times,Points from com_cardconsume
  select @err=@@error,@inserts=@@rowcount
  if @err<>0
  begin
    rollback
    exec up_rcverror @err,'cardconsume','com->insert'
    return @err
  end
  commit
  select @inserts=@inserts - @updates
  exec up_rcvlog 'cardconsume',0,@updates,@inserts
  truncate table com_cardconsume
  return @@error
go
print 'up_rcvcardconsume created!'
go

/* up_rcvordersrights */

if (select count(*) from sysobjects where name='up_rcvordersrights')>0
   drop proc up_rcvordersrights
go
CREATE PROC up_rcvordersrights
AS
  declare @deletes integer
  declare @updates integer
  declare @inserts integer
  declare @err integer
  set nocount on    
  begin tran
  delete ordersrights from ordersrights a,com_deldata b
      where b.tablename='ordersrights' and a.deptno=b.key1 and a.goodsno=b.key2
  select @err=@@error,@deletes=@@rowcount
  if @err<>0
  begin
    rollback
    exec up_rcverror @err,'ordersrights','delete'
    return @err
  end
  delete ordersrights from ordersrights a,com_ordersrights b
      where a.deptno=b.deptno and a.goodsno=b.goodsno
  select @err=@@error,@updates=@@rowcount
  if @err<>0
  begin
    rollback
    exec up_rcverror @err,'ordersrights','update(com->delete)'
    return @err
  end
  insert ordersrights select * from com_ordersrights 
  select @err=@@error,@inserts=@@rowcount
  if @err<>0
  begin
    rollback
    exec up_rcverror @err,'ordersrights','insert'
    return @err
  end
  delete com_deldata where tablename='ordersrights'
  commit
  select @inserts=@inserts-@updates
  exec up_rcvlog 'ordersrights',@deletes,@updates,@inserts
  truncate table com_ordersrights
  return @@error
go
print 'up_rcvordersrights created!'
go

/* up_rcvreachrights */

if (select count(*) from sysobjects where name='up_rcvreachrights')>0
   drop proc up_rcvreachrights
go
CREATE PROC up_rcvreachrights
AS
  declare @deletes integer
  declare @updates integer
  declare @inserts integer
  declare @err integer
  set nocount on    
  begin tran
  delete reachrights from reachrights a,com_deldata b
      where b.tablename='reachrights' and a.deptno=b.key1 and a.goodsno=b.key2
  select @err=@@error,@deletes=@@rowcount
  if @err<>0
  begin
    rollback
    exec up_rcverror @err,'reachrights','delete'
    return @err
  end
  delete reachrights from reachrights a,com_reachrights b
      where a.deptno=b.deptno and a.goodsno=b.goodsno
  select @err=@@error,@updates=@@rowcount
  if @err<>0
  begin
    rollback
    exec up_rcverror @err,'reachrights','update(com->delete)'
    return @err
  end
  insert reachrights select * from com_reachrights
  select @err=@@error,@inserts=@@rowcount
  if @err<>0
  begin
    rollback
    exec up_rcverror @err,'reachrights','inserts'
    return @err
  end
  delete com_deldata where tablename='reachrights'
  commit
  select @inserts=@inserts-@updates
  exec up_rcvlog 'reachrights',@deletes,@updates,@inserts
  truncate table com_reachrights
  return @@error
go
print 'up_rcvreachrights created!'
go

/* up_rcvadjustrights */

if (select count(*) from sysobjects where name='up_rcvadjustrights')>0
   drop proc up_rcvadjustrights
go
CREATE PROC up_rcvadjustrights
AS
  declare @deletes integer
  declare @updates integer
  declare @inserts integer
  declare @err integer
  set nocount on    
  begin tran
  delete adjustrights from adjustrights a,com_deldata b
      where b.tablename='adjustrights' and a.deptno=b.key1 and a.goodsno=b.key2
  select @err=@@error,@deletes=@@rowcount
  if @err<>0
  begin
    rollback
    exec up_rcverror @err,'adjustrights','delete'
    return @err
  end
  delete adjustrights from adjustrights a,com_adjustrights b
        where a.deptno=b.deptno and a.goodsno=b.goodsno
  select @err=@@error,@updates=@@rowcount
  if @err<>0
  begin
     rollback
     exec up_rcverror @err,'adjustrights','update(com->delete)'
     return @err
  end
  insert adjustrights select * from com_adjustrights
  select @err=@@error,@inserts=@@rowcount
  if @err<>0
  begin
    rollback
    exec up_rcverror @err,'adjustrights','inserts'
    return @err
  end
  delete com_deldata where tablename='adjustrights'
  commit
  select @inserts=@inserts-@updates
  exec up_rcvlog 'adjustrights',@deletes,@updates,@inserts
  truncate table com_adjustrights
  return @@error
go
print 'up_rcvadjustrights created!'
go

/* up_rcvcustprice */

if (select count(*) from sysobjects where name='up_rcvcustprice')>0
   drop proc up_rcvcustprice
go
CREATE PROC up_rcvcustprice
AS
  declare @deletes integer
  declare @updates integer
  declare @inserts integer
  declare @err integer
  set nocount on    
  begin tran
  delete custprice from custprice a,com_deldata b
      where b.tablename='custprice' and a.custno=b.key1 and a.goodsno=b.key2
  select @err=@@error,@deletes=@@rowcount
  if @err<>0
  begin
     rollback
     exec up_rcverror @err,'custprice','delete'
     return @err
  end
  delete custprice from custprice a,com_custprice b
     where a.custno=b.custno and a.goodsno=b.goodsno
  select @err=@@error,@updates=@@rowcount
  if @err<>0
  begin
     rollback
     exec up_rcverror @err,'custprice','update(com->delete)'
     return @err
  end
  insert custprice select * from com_custprice 
  select @err=@@error,@inserts=@@rowcount
  if @err<>0
  begin
     rollback
     exec up_rcverror @err,'custprice','inserts'
     return @err
  end
  delete com_deldata where tablename='custprice'
  commit
  select @inserts=@inserts-@updates
  exec up_rcvlog 'custprice',@deletes,@updates,@inserts
  truncate table com_custprice
  return @@error
go
print 'up_rcvcustprice created!'
go

/* up_rcvvendorprice */

if (select count(*) from sysobjects where name='up_rcvvendorprice')>0
   drop proc up_rcvvendorprice
go
CREATE PROC up_rcvvendorprice
AS
  declare @deletes integer
  declare @updates integer
  declare @inserts integer
  declare @err integer
  set nocount on    
  update com_vendorprice set modifydate=getdate()
  begin tran
  delete vendorprice from vendorprice a,com_deldata b
      where b.tablename='vendorprice' and a.vendorno=b.key1 and a.goodsno=b.key2
  select @err=@@error,@deletes=@@rowcount
  if @err<>0
  begin
    rollback
    exec up_rcverror @err,'vendorprice','delete'
    return @err
  end
  delete vendorprice from vendorprice a,com_vendorprice b
     where a.vendorno=b.vendorno and a.goodsno=b.goodsno
  select @err=@@error,@updates=@@rowcount
  if @err<>0
  begin
    rollback
    exec up_rcverror @err,'vendorprice','update(com->delete)'
    return @err
  end
  insert vendorprice select * from com_vendorprice
  select @err=@@error,@inserts=@@rowcount
  if @err<>0
  begin
    rollback
    exec up_rcverror @err,'vendorprice','insert'
    return @err
  end
  delete com_deldata where tablename='vendorprice'
  commit
  select @inserts=@inserts-@updates
  exec up_rcvlog 'vendorprice',@deletes,@updates,@inserts
  truncate table com_vendorprice
  return @@error
go
print 'up_rcvvendorprice created!'
go

/* up_rcvcategory */
if (select count(*) from sysobjects where name='up_rcvcategory')>0
   drop proc up_rcvcategory
go
CREATE PROC up_rcvcategory
AS
  declare @deletes integer
  declare @updates integer
  declare @inserts integer
  declare @err integer
  set nocount on
  update com_category set modifydate=getdate()
  begin tran
  delete category from category a,com_deldata b
      where a.categoryno=b.key1 and b.tablename='category'
  select @err=@@error,@deletes=@@rowcount
  if @err<>0
  begin
    rollback
    exec up_rcverror @err,'category','delete'
    return @err
  end
  delete category from category a,com_category b
    where a.categoryno=b.categoryno
  select @err=@@error,@updates=@@rowcount
  if @err<>0
  begin
    rollback
    exec up_rcverror @err,'category','udpate(com->delete)'
    return @err
  end
  insert category select * from com_category
  select @err=@@error,@inserts=@@rowcount
  if @err<>0
  begin
    rollback
    exec up_rcverror @err,'category','insert'
    return @err
  end
  delete com_deldata where tablename='category'
  commit
  select @inserts=@inserts-@updates
  exec up_rcvlog 'category',@deletes,@updates,@inserts
  delete com_category
go
print 'up_rcvcategory created!'
go

/* up_rcvtopandbottom */

if (select count(*) from sysobjects where name='up_rcvtopandbottom')>0
   drop proc up_rcvtopandbottom
go
CREATE PROC up_rcvtopandbottom
AS
  declare @deletes integer
  declare @updates integer
  declare @inserts integer
  declare @err integer
  set nocount on
  begin tran
  update topandbottom set 
         maxqty=b.MAXQTY,
         minqty=b.MINQTY,
         modifydate=getdate(),
         orderqty=b.OrderQty
      from topandbottom a,com_topandbottom b 
      where a.deptno=b.deptno and a.goodsno=b.goodsno
  select @err=@@error,@updates=@@rowcount
  if @err<>0
  begin
     rollback
     exec up_rcverror @err,'topandbottom','delete'
     return @err
  end
  commit
  exec up_rcvlog 'topandbottom',0,@updates,0
  truncate table com_topandbottom
  return @@error
go
print 'up_rcvtopandbottom created!'
go

/* up_rcvdeptstock */

if (select count(*) from sysobjects where name='up_rcvdeptstock')>0
   drop proc up_rcvdeptstock
go
CREATE PROC up_rcvdeptstock
AS
  declare @deletes integer
  declare @updates integer
  declare @inserts integer
  declare @err integer
  set nocount on
  update com_deptstock set modifydate=getdate()
  begin tran
  delete deptstock from deptstock a,com_deptstock b
       where a.year=b.year and a.month=b.month and a.deptno=b.deptno and a.goodsno=b.goodsno
  select @err=@@error,@updates=@@rowcount
  if @err<>0
  begin
     rollback
     exec up_rcverror @err,'deptstock','update(com->delete)'
     return @err
  end
  insert deptstock select * from com_deptstock
  select @err=@@error,@inserts=@@rowcount
  if @err<>0
  begin
     rollback
     exec up_rcverror @err,'deptstock','insert(com->insert)'
     return @err
  end
  commit
  select @inserts=@inserts-@updates
  exec up_rcvlog 'deptstock',0,@updates,@inserts
  truncate table com_deptstock
  return @@error
go
print 'up_rcvdeptstock created!'
go

/* up_rcvdayreport */

if (select count(*) from sysobjects where name='up_rcvdayreport')>0
   drop proc up_rcvdayreport
go
CREATE PROC up_rcvdayreport
AS
  declare @deletes integer
  declare @updates integer
  declare @inserts integer
  declare @err integer
  set nocount on
  begin tran
  delete dayreport from dayreport a,com_dayreport b
       where a.deptno=b.deptno and a.groupno=b.groupno and a.businessdate=b.businessdate
  select @err=@@error,@updates=@@rowcount
  if @err<>0
  begin
     rollback
     exec up_rcverror @err,'dayreport','update(com->delete)'
     return @err
  end
  insert dayreport select * from com_dayreport
  select @err=@@error,@inserts=@@rowcount
  if @err<>0
  begin
     rollback
     exec up_rcverror @err,'dayreport','insert(com->insert)'
     return @err
  end
  commit
  select @inserts=@inserts-@updates
  exec up_rcvlog 'dayreport',0,@updates,@inserts
  truncate table com_dayreport
  return @@error
go
print 'up_rcvcardflow created!'
go

/* up_rcvcardflow */

if (select count(*) from sysobjects where name='up_rcvcardflow')>0
   drop proc up_rcvcardflow
go
CREATE PROC up_rcvcardflow
AS
/*  
declare @deletes integer
  declare @updates integer
  declare @inserts integer
  declare @err integer
  set nocount on
  begin tran
  delete cardflow from cardflow a,com_cardflow b
       where a.cardno=b.cardno and a.inputdate=b.inputdate
  select @err=@@error,@updates=@@rowcount
  if @err<>0
  begin
     rollback
     exec up_rcverror @err,'cardflow','update(com->delete)'
     return @err
  end
  insert cardflow(
	CardNO,
	Casher,
	PosNo,
	InputDate,
	ReceiptNO,
	InvoiceNo,
	DisCount,
	Points,
	Amt,
	DisAmt)
  select
	CardNO,
	Casher,
	PosNo,
	InputDate,
	ReceiptNO,
	InvoiceNo,
	D