comyearp.sql

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

use sypos2000
go
/* up_rcvdownalldata */
if (select count(*) from sysobjects where name='up_rcvdownalldata')>0
   drop proc up_rcvdownalldata
go
CREATE PROC up_rcvdownalldata
--  @ret integer output,
--  @disptable varchar(30) output
AS
  declare @ret integer
  declare @disptable varchar(30)
  declare @procname varchar(100)

  set nocount on
  delete rcvoneerror
  declare proccur cursor for select procname,dispname from rcvdowntables where rcvflag='1' order by ord
  select @ret=@@error
  if @ret<>0
  begin
     select @disptable='定义游标时'
     delete rcvallerror
     insert rcvallerror(ret,errtable) values(@ret,@disptable)
     return @ret
  end
 
  open proccur
  select @ret=@@error
  if @ret<>0
  begin
     select @disptable='打开游标时(up_rcvdownalldata)'
     delete rcvallerror
     insert rcvallerror(ret,errtable) values(@ret,@disptable)
     return @ret
  end
  while 1 = 1
  begin
    fetch proccur into @procname,@disptable
    if @@fetch_status <> 0
       break
    exec @ret=@procname
    --print @procname
    if @ret<>0
       break
  end
  close proccur
  deallocate proccur
  if @ret<>0
  begin
     delete rcvoneerror
     insert rcvoneerror(ret,errtable) values(@ret,@disptable)
     return @ret
  end
  set nocount off
  return @ret
go
print 'up_rcvdownalldata created!'
go

/* up_rcvupalldata */
if (select count(*) from sysobjects where name='up_rcvupalldata')>0
   drop proc up_rcvupalldata
go
CREATE PROC up_rcvupalldata
--  @ret integer output,
--  @disptable varchar(30) output
AS
  declare @ret integer
  declare @disptable varchar(30)
  declare @procname varchar(100)
  
  set nocount on
  delete rcvoneerror
  declare proccur cursor for select procname,dispname from rcvuptables where rcvflag='1' order by ord
  select @ret=@@error
  if @ret<>0
  begin
     select @disptable='定义游标时'
     delete rcvallerror
     insert rcvallerror(ret,errtable) values(@ret,@disptable)
     return @ret
  end
 
  open proccur
  select @ret=@@error
  if @ret<>0
  begin
     select @disptable='打开游标时(up_rcvupalldata)'
     delete rcvallerror
     insert rcvallerror(ret,errtable) values(@ret,@disptable)
     return @ret
  end
  while 1 = 1
  begin
    fetch proccur into @procname,@disptable
    if @@fetch_status <> 0
       break
   exec @ret=@procname
   --print @procname
   if @ret<>0
      break
  end
  close proccur
  deallocate proccur
  if @ret<>0
  begin
     delete rcvoneerror
     insert rcvoneerror(ret,errtable) values(@ret,@disptable)
     return @ret
  end
  set nocount off
  return @ret
go
print 'up_rcvupalldata created!'
go

/* up_rcverror */
if (select count(*) from sysobjects where name='up_rcverror')>0
   drop proc up_rcverror
go
CREATE PROC up_rcverror
   @code integer,
   @table varchar(30),
   @oper varchar(10)
AS  
   insert rcverrcode(code,oper,tablename) values (@code,@oper,@table)
GO
print 'up_rcverror created!'
go

/* up_rcvlog */
if (select count(*) from sysobjects where name='up_rcvlog')>0
   drop proc up_rcvlog
go
CREATE PROC up_rcvlog
   @table varchar(30),
   @deletes integer,
   @updates integer,
   @inserts integer
AS
  if @deletes>0 or @updates>0 or @inserts>0
     insert rcvlog(tablename,deletes,updates,inserts) values(@table,@deletes,@updates,@inserts)
GO
print 'up_rcvlog created!'
go

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

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

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

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

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

/* up_rcvstaff */
if (select count(*) from sysobjects where name='up_rcvstaff')>0
   drop proc up_rcvstaff
go
CREATE PROC up_rcvstaff
AS
  declare @deletes integer
  declare @updates integer
  declare @inserts integer
  declare @err integer
  set nocount on
  update com_staff set modifydate=getdate()
  begin tran
  delete staff from staff a,com_deldata b where a.operator=b.key1 and b.tablename='staff'
  select @err=@@error,@deletes=@@rowcount
  if @err<>0
  begin
     rollback
     exec up_rcverror @err,'staff','delete'
     return @err
  end
  update staff set 
         name=b.name,
         DEPTNO=b.deptno,
         GROUPNO=b.groupno,
         SEX=b.sex,
         NATION=b.nation,
         GRADE=b.grade,
         ADDR=b.addr,
         ZIP=b.zip,
         TEL=b.tel,
         FAX=b.fax,
         WORKTYPE=b.worktype,
         BIRTHDAY=b.birthday,
         WORKDATE=b.workdate,
         ENTRYDATE=b.entrydate,
         EDUCATION=b.education,
         PERSONALID=b.personalid,
         PROPERTY=b.property,
         MODIFYDATE=b.modifydate,
         REMARK=b.remark 
      from staff a,com_staff b 
      where a.operator=b.operator
  select @err=@@error,@updates=@@rowcount
  if @err<>0
  begin
     rollback
     exec up_rcverror @err,'staff','update'
     return @err
   end
  insert staff select * from com_staff where operator not in 
  (select operator from staff)
  select @err=@@error,@inserts=@@rowcount
  if @err<>0 
  begin
     rollback
     exec up_rcverror @err,'staff','insert'
     return @err
  end
  delete com_deldata where tablename='staff'
  commit
  exec up_rcvlog 'staff',@deletes,@updates,@inserts
  truncate table com_staff
  return @@error
go
print 'up_rcvstaff created!'
go

/* up_rcvgoods */
if (select count(*) from sysobjects where name='up_rcvgoods')>0
   drop proc up_rcvgoods
go
CREATE PROC up_rcvgoods
AS    
  set nocount on
  declare @upsql varchar(2000)
  declare @column_name varchar(50)
  declare @cn integer
  declare @delimitor char
  declare @deletes integer
  declare @updates integer
  declare @inserts integer