comyearp.sql

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

  declare @err integer
  declare @LocalDept TDept

  select @LocalDept=(select deptno from dept where masterdept=deptno and localflag='1')

  if @LocalDept is null 
  begin
    exec up_rcverror 9999,'goods','no main dept'
    return 9999
  end
  update goods set barcode=a.goodsno from goods a,com_goods b
  where a.barcode=b.barcode and a.goodsno<>b.goodsno
  update com_goods set modifydate=getdate()
  begin transaction 
  delete goods where goodsno in (select key1 from com_deldata where tablename='goods')
  select @err=@@error,@deletes=@@rowcount
  if @err<>0
  begin
     rollback
     exec up_rcverror @err,'goods','delete'
     return @err
  end
  select @cn=0
  select @upsql='update goods set'
  declare c1 cursor for select column_name from information_schema.columns where table_name='goods'
  open c1
  fetch c1 into @column_name  
  while @@fetch_status=0
  begin
     if @cn>0
        select @delimitor=','
     else
        select @delimitor=' '
     if (select count(*) from depttablerights where deptno=@LocalDept and tablename='goods' and fieldname=@column_name)=0
     begin 
        select @upsql=@upsql+@delimitor+@column_name+'=b.'+@column_name
        select @cn=@cn + 1
     end
     fetch c1 into @column_name
  end
  close c1
  deallocate c1
  if @cn>0
  begin
     select @upsql=@upsql+' from goods a,com_goods b where a.goodsno=b.goodsno'
     exec(@upsql)
     select @err=@@error,@updates=@@rowcount
     if @err<>0
     begin
       rollback
       exec up_rcverror @err,'goods','update'
       return @err
     end
  end
  insert goods select * from com_goods where goodsno not in 
    (select goodsno from goods)
  select @err=@@error,@inserts=@@rowcount
  if @err<>0
  begin
    rollback
    exec up_rcverror @err,'goods','insert'
    return @err
  end
  delete com_deldata where tablename='goods'
  commit
  exec up_rcvlog 'goods',@deletes,@updates,@inserts
  truncate table com_goods
  return @@error
go
print 'up_rcvgoods created!'
go

/* up_rcvpaymode */
if (select count(*) from sysobjects where name='up_rcvpaymode')>0
   drop proc up_rcvpaymode
go
CREATE PROC up_rcvpaymode
AS
  declare @deletes integer
  declare @updates integer
  declare @inserts integer
  declare @err integer
  set nocount on
  update com_paymode set modifydate=getdate()
  begin tran
  delete paymode where paymode in (select key1 from com_deldata where tablename='paymode')
  select @err=@@error,@deletes=@@rowcount
  if @err<>0
  begin
     rollback  
     exec up_rcverror @err,'paymode','delete'
     return @err
  end
  update paymode set 
         name=b.name,
         modifydate=b.modifydate 
     from paymode a,com_paymode b 
      where a.paymode=b.paymode
  select @err=@@error,@updates=@@rowcount
  if @err<>0
  begin
    rollback
    exec up_rcverror @err,'paymode','update'
    return @err
  end
  insert paymode select * from com_paymode where paymode not in 
  (select paymode from paymode)
  select @err=@@error,@inserts=@@rowcount
  if @err<>0
  begin
    rollback
    exec up_rcverror @err,'paymode','insert'
    return @err
  end
  delete com_deldata where tablename='paymode'
  commit
  exec up_rcvlog 'paymode',@deletes,@updates,@inserts
  truncate table com_paymode
  return @@error
go
print 'up_rcvpaymode created!'
go

/* up_rcvuserdeffield */
if (select count(*) from sysobjects where name='up_rcvuserdeffield')>0
   drop proc up_rcvuserdeffield
go
CREATE PROC up_rcvuserdeffield
AS
  declare @deletes integer
  declare @updates integer
  declare @inserts integer
  declare @err integer
  set nocount on
  update com_userdeffield set modifydate=getdate()
  begin tran
  delete userdeffield from userdeffield a,com_deldata b 
         where a.tablename=b.key1 and a.fieldname=b.key2 and b.tablename='userdeffield'
  select @err=@@error,@deletes=@@rowcount
  if @err<>0
  begin
     rollback
     exec up_rcverror @err,'userdeffield','delete'
     return @err
  end
  update userdeffield set 
         tdispname=b.tdispname,
         fdispname=b.fdispname,
         isusing=b.isusing,
         modifydate=b.modifydate 
      from userdeffield a,com_userdeffield b 
      where a.tablename=b.tablename and a.fieldname=b.fieldname
  select @err=@@error,@updates=@@rowcount
  if @err<>0
  begin
    rollback
    exec up_rcverror @err,'userdeffield','update'
    return @err
  end
  insert userdeffield select * from com_userdeffield 
      where tablename+fieldname not in 
      (select tablename+fieldname from userdeffield)  
  select @err=@@error,@inserts=@@rowcount
  if @err<>0
  begin 
    rollback
    exec up_rcverror @err,'userdeffield','insert'
    return @err
  end
  delete com_deldata where tablename='userdeffield'
  commit
  exec up_rcvlog 'userdeffield',@deletes,@updates,@inserts
  truncate table com_userdeffield
  return @@error
go
print 'up_rcvuserdeffield created!'
go

/* up_rcvmembercard */
if (select count(*) from sysobjects where name='up_rcvmembercard')>0
   drop proc up_rcvmembercard
go
CREATE PROC up_rcvmembercard
AS
  declare @deletes integer
  declare @updates integer
  declare @inserts integer
  declare @err integer
  set nocount on
  update com_membercard set modifydate=getdate()
  begin tran
  delete membercard from membercard a,com_deldata b where a.cardno=b.key1 and b.tablename='membercard'
  select @err=@@error,@deletes=@@rowcount
  if @err<>0
  begin
    rollback
    exec up_rcverror @err,'membercard','delete'
    return @err
  end 
  update membercard set 
         NAME=b.name,
         CARDTYPE=b.cardtype,
         PERSONALID=b.personalid,
         TEL=b.tel,
         ZIP=b.zip,
         INPUTDATE=b.inputdate,
         STARTDATE=b.startdate,
         ENDDATE=b.enddate,
         DEPTNO=b.deptno,
         OPERATOR=b.operator,
         PASSWD=b.passwd,
         DISCOUNT=b.discount,
         CURAMT=b.curamt,
         ADDR=b.addr,
         USERTYPE=b.usertype,
         DISTYPE=b.distype,
         modifydate=b.modifydate 
      from membercard a,com_membercard b 
      where a.cardno=b.cardno and
         (a.NAME<>b.name or 
         a.CARDTYPE<>b.cardtype or 
         a.PERSONALID<>b.personalid or 
         a.TEL<>b.tel or 
         a.ZIP<>b.zip or 
         a.STARTDATE<>b.startdate or 
         a.ENDDATE<>b.enddate or 
         a.DEPTNO<>b.deptno or 
         a.OPERATOR<>b.operator or 
         a.PASSWD<>b.passwd or 
         a.DISCOUNT<>b.discount or 
         a.CURAMT<>b.curamt or 
         a.ADDR<>b.addr or 
         a.USERTYPE<>b.usertype or 
         a.DISTYPE<>b.distype) 
  select @err=@@error,@updates=@@rowcount
  if @err<>0
  begin
    rollback
    exec up_rcverror @err,'membercard','update'
    return @err
  end
  insert membercard select * from com_membercard where cardno not in 
  (select cardno from membercard)
  select @err=@@error,@inserts=@@rowcount
  if @err<>0 
  begin
    rollback
    exec up_rcverror @err,'membercard','insert'
    return @err
  end
  delete com_deldata where tablename='membercard'
  commit
  exec up_rcvlog 'membercard',@deletes,@updates,@inserts
  truncate table com_membercard
  return @@error
go
print 'up_rcvmembercard created!'
go

/* up_rcvcust */
if (select count(*) from sysobjects where name='up_rcvcust')>0
   drop proc up_rcvcust
go
CREATE PROC up_rcvcust
AS
  declare @deletes integer
  declare @updates integer
  declare @inserts integer
  declare @err integer
  set nocount on
  update com_cust set modifydate=getdate()
  begin tran
  delete cust from cust a,com_deldata b where a.custno=b.key1 and b.tablename='cust'
  select @err=@@error,@deletes=@@rowcount
  if @err<>0
  begin
    rollback
    exec up_rcverror @err,'cust','delete'
    return @err
  end
  update cust set 
         NAME=b.name,
         SHORTNAME=b.shortname,
         TEL=b.tel,
         FAX=b.fax,
         ZIP=b.zip,
         ADDR=b.addr,
         LEGMAN=b.legman,
         MANAGER=b.manager,
         LINKMAN=b.linkman,
         BANK1=b.bank1,
         ACCT1=b.acct1,
         BANK2=b.bank2,
         ACCT2=b.acct2,
         TAXNO=b.taxno,
         PAYMODE=b.paymode,
         DISCOUNT=b.discount,
         INPUTDATE=b.inputdate,
         MODIFYDATE=b.modifydate,
         UD1=b.ud1,
         UD2=b.ud2,
         UD4=b.ud4,
         UD3=b.ud3,
         UD5=b.ud5,
         REMARK=b.remark 
     from cust a,com_cust b 
     where a.custno=b.custno
  select @err=@@error,@updates=@@rowcount
  if @err<>0
  begin
    rollback
    exec up_rcverror @err,'cust','update'
    return @err
  end
  insert cust select * from com_cust where custno not in 
     (select custno from cust)
  select @err=@@error,@inserts=@@rowcount
  if @err<>0
  begin
    rollback
    exec up_rcverror @err,'cust','delete'
    return @err
  end
  delete com_deldata where tablename='cust'
  commit
  exec up_rcvlog 'cust',@deletes,@updates,@inserts
  truncate table com_cust
  return @@error
go
print 'up_rcvcust created!'
go

/* up_rcvvendor */
if (select count(*) from sysobjects where name='up_rcvvendor')>0
   drop proc up_rcvvendor
go
CREATE PROC up_rcvvendor
AS
  declare @deletes integer
  declare @updates integer
  declare @inserts integer
  declare @err integer
  set nocount on
  update com_vendor set modifydate=getdate()
  begin tran
  delete vendor from vendor a,com_deldata b where a.vendorno=b.key1 and b.tablename='vendor'
  select @err=@@error,@deletes=@@rowcount
  if @err<>0
  begin
     rollback
     exec up_rcverror @err,'vendor','delete'
     return @err
  end
  update vendor set 
         NAME=b.name,
         SHORTNAME=b.shortname,
         TEL=b.tel,
         FAX=b.fax,
         ZIP=b.zip,
         ADDR=b.addr,
         LEGMAN=b.legman,
         MANAGER=b.manager,
         LINKMAN=b.linkman,
         BANK1=b.bank1,
         ACCT1=b.acct1,
         BANK2=b.bank2,
         ACCT2=b.acct2,
         TAXNO=b.taxno,
         PID=b.pid,
         HID=b.hid,
         PAYMODE=b.paymode,
         INPUTDATE=b.inputdate,
         MODIFYDATE=b.modifydate,
         UD1=b.ud1,
         UD2=b.ud2,
         UD4=b.ud4,
         UD3=b.ud3,
         UD5=b.ud5,
         REMARK=b.remark 
     from vendor a,com_vendor b 
     where a.vendorno=b.vendorno
  select @err=@@error,@updates=@@rowcount
  if @err<>0
  begin
    rollback
    exec up_rcverror @err,'vendor','update'
    return @err
  end
  insert vendor select * from com_vendor where vendorno not in 
     (select vendorno from vendor)
  select @err=@@error,@inserts=@@rowcount
  if @err<>0
  begin
     rollback
     exec up_rcverror @err,'vendor','insert'
     return @err
  end
  delete com_deldata where tablename='vendor'
  commit
  exec up_rcvlog 'vendor',@deletes,@updates,@inserts
  truncate table com_vendor
  return @@error
go
print 'up_rcvvendor created!'
go

/* up_rcvdeptps */
if (select count(*) from sysobjects where name='up_rcvdeptps')>0
   drop proc up_rcvdeptps
go
CREATE PROC up_rcvdeptps
AS
  declare @deletes integer
  declare @updates integer
  declare @inserts integer
  declare @err integer
  set nocount on
  begin tran
  update deptps set 
         curqty=b.curqty,
         retailprice=b.retailprice,
         memberprice=b.memberprice,
         saleprice=b.saleprice,
         spriceflag=b.spriceflag,
--         curcostprice=b.curcostprice,
--         lastcostprice=b.lastcostprice,
         modifydate=getdate()
     from deptps a,com_deptps 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,'deptps','update'
    return @err
  end 
  delete com_deptps from deptps a,com_deptps b
  where a.deptno=b.deptno and a.goodsno=b.goodsno
  select @err=@@error,@deletes=@@rowcount
  if @err<>0
  begin
    rollback
    exec up_rcverror @err,'deptps','delete'
    return @err
  end 
  insert deptps(deptno,goodsno,curqty,retailprice,memberprice,saleprice,spriceflag,
                modifydate)
          select deptno,goodsno,curqty,retailprice,memberprice,saleprice,spriceflag,
                 getdate()
                 from com_deptps
  select @err=@@error,@inserts=@@rowcount
  if @err<>0
  begin
    rollback
    exec up_rcverror @err,'deptps','insert'
    return @err
  end 
  commit
  exec up_rcvlog 'deptps',0,@updates,@inserts
  truncate table com_deptps
  return @@error
go
print 'up_rcvdeptps created!'
go

/* up_rcvalloc */

if (select count(*) from sysobjects where name='up_rcvalloc')>0
   drop proc up_rcvalloc
go
CREATE PROC up_rcvalloc