comyearp.sql

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

if (select count(*) from sysobjects where name='up_rcvsaledetail')>0
   drop proc up_rcvsaledetail
go
CREATE PROC up_rcvsaledetail
AS
  set nocount on
  declare @SaleNo TReceipt
  declare @GoodsNo TGoods
  declare @NTaxPrice TPrice
  declare @Price TPrice
  declare @RetailPrice TPrice
  declare @Qty TQty
  declare @AuditQty TQty
  declare @NTaxAmt TAmt
  declare @Amt TAmt
  declare @CostPrice TPrice
  declare @OrgSaleNo TPrice
  declare @BatchNo TBatch
  declare @ID integer

  declare @deletes integer
  declare @updates integer
  declare @inserts integer
  declare @err integer

  declare c1 cursor for select SaleNo,GoodsNo,NTaxPrice,Price,RetailPrice,Qty,AuditQty,
       NTaxAmt,Amt,CostPrice,OrgSaleNo,BatchNo,ID from com_saledetail
  open c1
  select @err=@@error
  if @err<>0
  begin
     rollback    
     exec up_rcverror @err,'saledetail','open'
     return @err
  end
  select @inserts=0
  begin tran
  while 1 = 1
  begin
    fetch c1 into @SaleNo,@GoodsNo,@NTaxPrice,@Price,@RetailPrice,@Qty,@AuditQty,
       @NTaxAmt,@Amt,@CostPrice,@OrgSaleNo,@BatchNo,@ID
    if @@fetch_status <> 0
       break
      if (select count(*) from saledetail where saleno=@saleno and goodsno=@goodsno)=0
      begin
        insert saledetail(SaleNo,GoodsNo,NTaxPrice,Price,RetailPrice,Qty,AuditQty,
             NTaxAmt,Amt,CostPrice,OrgSaleNo,BatchNo)
        values(@SaleNo,@GoodsNo,@NTaxPrice,@Price,@RetailPrice,@Qty,@AuditQty,
             @NTaxAmt,@Amt,@CostPrice,@OrgSaleNo,@BatchNo)
        select @err=@@error
        if @err <>0
        begin
          rollback tran
          exec up_rcverror @err,'saledetail','insert'
          close c1
          deallocate c1
          return @err
        end
        select @inserts=@inserts+1
      end
  end
  commit
  close c1
  deallocate c1
  exec up_rcvlog 'saledetail',0,0,@inserts
  truncate table com_saledetail
  return @@error
go
print 'up_rcvsaledetail created!'
go

/* up_rcvloss */

if (select count(*) from sysobjects where name='up_rcvloss')>0
   drop proc up_rcvloss
go
CREATE PROC up_rcvloss
AS
  declare @deletes integer
  declare @updates integer
  declare @inserts integer
  declare @err integer
  set nocount on  
  begin tran
  insert loss(LossNo,InputDate,LossDate,AuditDate,DeptNO,ReceiptNo,Operator,Employee,Amt,
              AuditFlag,AuditOperator,BusinessFlag,Remark) 
       select LossNo,InputDate,LossDate,AuditDate,DeptNO,ReceiptNo,Operator,Employee,Amt,
              AuditFlag,AuditOperator,BusinessFlag,Remark
       from com_loss where lossno not in (select lossno from loss)
  select @err=@@error,@inserts=@@rowcount
  if @err<>0
  begin
    rollback
    exec up_rcverror @err,'loss','insert'
    return @err
  end
  commit
  exec up_rcvlog 'loss',0,0,@inserts
  truncate table com_loss
  return @@error
go
print 'up_rcvloss created!'
go

/* up_rcvlossdetail */
if (select count(*) from sysobjects where name='up_rcvlossdetail')>0
   drop proc up_rcvlossdetail
go
CREATE PROC up_rcvlossdetail
AS
  set nocount on
  declare @LossNo TReceipt
  declare @GoodsNo TGoods
  declare @LossType Char
  declare @Qty TQty
  declare @AuditQty TQty
  declare @CostPrice TPrice
  declare @RetailPrice TPrice
  declare @BatchNo TBatch
  declare @OrgLossNo TReceipt
  declare @ID integer

  declare @deletes integer
  declare @updates integer
  declare @inserts integer
  declare @err integer

  declare c1 cursor for select LossNo,GoodsNo,LossType,Qty,AuditQty,RetailPrice,
         BatchNo,OrgLossNo,ID from com_lossdetail
 
  open c1
  select @err=@@error
  if @err<>0
  begin
     exec up_rcverror @err,'lossdetail','open'
     return @err
  end
  select @inserts=0
  begin tran
  while 1 = 1
  begin
    fetch c1 into @LossNo,@GoodsNo,@LossType,@Qty,@AuditQty,@RetailPrice,
         @BatchNo,@OrgLossNo,@ID
    if @@fetch_status <> 0
       break
      if (select count(*) from lossdetail where lossno=@lossno and goodsno=@goodsno)=0
      begin
        insert lossdetail(LossNo,GoodsNo,LossType,Qty,AuditQty,RetailPrice,
             BatchNo,OrgLossNo)
        values(@LossNo,@GoodsNo,@LossType,@Qty,@AuditQty,@RetailPrice,
             @BatchNo,@OrgLossNo)
        select @err=@@error
        if @err <>0
        begin
          rollback tran
          deallocate c1
          exec up_rcvlog @err,'lossdetail','insert'
          return @err
        end
        select @inserts=@inserts + 1
      end
  end
  commit  
  exec up_rcvlog 'lossdetail',0,0,@inserts
  close c1
  deallocate c1
  truncate table com_lossdetail
  return @@error
go
print 'up_rcvlossdetail created!'
go

/* up_rcvreq */
if (select count(*) from sysobjects where name='up_rcvreq')>0
   drop proc up_rcvreq
go
CREATE PROC up_rcvreq
AS
  set nocount on
  declare @ReceiptNo TReceipt
  declare @OrderNo TReceipt
  declare @GoodsNo TGoods
  declare @MergeFlag Char
  declare @OrderQty TQty
  declare @AuditQty TQty
  declare @Qty TQty
  declare @DestDept TDept
  declare @OrderDept TDept
  declare @VendorNo TVendor
  declare @InputDept TDept
  declare @ReqDept TDept
  declare @Operator TStaff
  declare @Employee TStaff
  declare @ReachDate TDate
  declare @InputDate TDate

  declare @NewOrderDept TDept
  declare @NewDestDept TDept
  declare @NewVendorNo TVendor
  
  declare @LocalDept TDept
  declare @Order char
  declare @Reach char

  declare @deletes integer
  declare @updates integer
  declare @inserts integer
  declare @err integer

  select @order=(select isnull(value,'1') from sysparams where name='order')
  select @reach=(select isnull(value,'1') from sysparams where name='reach')
  select @LocalDept=(select deptno from dept where masterdept=deptno and localflag='1')

  if @LocalDept is null 
  begin
    exec up_rcverror 9999,'req','no main dept'
    return 9999
  end
  declare c1 cursor for select ReceiptNo,OrderNo,GoodsNo,MergeFlag,OrderQty,AuditQty,
         Qty,DestDept,OrderDept,VendorNo,InputDept,Operator,Employee,ReachDate,
         InputDate,ReqDept from com_req
  open c1
  select @err=@@error
  if @err<>0
  begin
     exec up_rcverror @err,'req','open'
     close c1
     deallocate c1
     return @err
  end
  select @deletes=0,@updates=0,@inserts=0
  begin tran
  while 1 = 1
  begin
    fetch c1 into @ReceiptNo,@OrderNo,@GoodsNo,@MergeFlag,@OrderQty,@AuditQty,
         @Qty,@DestDept,@OrderDept,@VendorNo,@InputDept,@Operator,@Employee,@ReachDate,
         @InputDate,@ReqDept
    if @@fetch_status <> 0
       break
      if (select count(*) from req where ReceiptNo=@ReceiptNo and goodsno=@GoodsNo)=0
      begin
        select @NewDestDept=@DestDept
        if @NewDestDept is null
        begin
           if @Reach='1' 
              select @NewDestDept=@LocalDept
           else
              select @NewDestDept=(select deptno from reachrights 
                    where deptno=@LocalDept and goodsno=@GoodsNo)
        end
        select @NewOrderDept=@OrderDept
        select @NewVendorNo=@VendorNo
        if @NewOrderDept is null
        begin 
           if @order='1' 
              select @NewOrderDept=@LocalDept
           else
              select @NewOrderDept=(select deptno from ordersrights
                    where deptno=@LocalDept and goodsno=@GoodsNo) 
           select @NewVendorNo=(select vendorno from goods where goodsno=@GoodsNo)              
        end
        if (@DestDept is null) and (@NewDestDept is not null) 
           select @DestDept=@NewDestDept
        if (@OrderDept is null) and (@NewOrderDept is not null) 
           select @OrderDept=@NewOrderDept
        select @VendorNo=@NewVendorNo 
        insert req(ReceiptNo,OrderNo,GoodsNo,MergeFlag,OrderQty,AuditQty,
                 Qty,DestDept,OrderDept,VendorNo,InputDept,Operator,Employee,ReachDate,
                 InputDate,ReqDept)
        values(@ReceiptNo,@OrderNo,@GoodsNo,@MergeFlag,@OrderQty,@AuditQty,
               @Qty,@DestDept,@OrderDept,@VendorNo,@InputDept,@Operator,@Employee,@ReachDate,
               @InputDate,@ReqDept)
        select @err=@@error
        if @err <>0
        begin
          rollback tran
          exec up_rcverror @err,'req','insert'
          close c1
          deallocate c1
          return @err
        end
        select @inserts=@inserts+1
      end
      else
      begin
        update req set
             OrderNo=@OrderNo,MergeFlag=@MergeFlag,OrderQty=@OrderQty,AuditQty=@AuditQty,
             Qty=@Qty,DestDept=@DestDept,OrderDept=@OrderDept,VendorNo=@VendorNo,
             ReachDate=@ReachDate,ReqDept=@ReqDept
        where ReceiptNo=@ReceiptNo and GoodsNo=@GoodsNo
        select @err=@@error
        if @err <>0
        begin
          rollback tran
          exec up_rcverror @err,'req','update'
          close c1
          deallocate c1
          return @err
        end
        select @updates=@updates+1
      end
  end
  commit
  exec up_rcvlog 'req',@deletes,@updates,@inserts
  close c1
  deallocate c1
  truncate table com_req
  return @@error
go
print 'up_rcvreq created!'
go

/* up_rcvorders */

if (select count(*) from sysobjects where name='up_rcvorders')>0
   drop proc up_rcvorders
go
CREATE PROC up_rcvorders
AS
  declare @deletes integer
  declare @updates integer
  declare @inserts integer
  declare @err integer
  set nocount on  
  select @deletes=0,@updates=0,@inserts=0
  begin tran
  insert orders(OrderNo,InputDate,ReachDate,Operator,Employee,InputDept,OrderDept,DestDept,
                VendorNo,BusinessFlag,ReceiptFlag,Remark) 
         select OrderNo,InputDate,ReachDate,Operator,Employee,InputDept,OrderDept,DestDept,
                VendorNo,BusinessFlag,ReceiptFlag,Remark
         from com_orders where orderno not in (select orderno from orders)
  select @err=@@error,@inserts=@@rowcount
  if @err<>0
  begin
    rollback
    exec up_rcverror @err,'orders','insert'
    return @err
  end
  commit
  exec up_rcvlog 'orders',0,0,@inserts
  truncate table com_orders
  return @@error
go
print 'up_rcvorders created!'
go

/* up_rcvorderdetail */
if (select count(*) from sysobjects where name='up_rcvorderdetail')>0
   drop proc up_rcvorderdetail
go
CREATE PROC up_rcvorderdetail
AS
  set nocount on
  declare @OrderNo TReceipt
  declare @GoodsNo TGoods
  declare @OrderQty TQty
  declare @AuditQty TQty
  declare @Qty TQty
  declare @NTaxPrice TPrice
  declare @TaxPrice TPrice
  declare @ID  integer

  declare @deletes integer
  declare @updates integer
  declare @inserts integer
  declare @err integer

  declare c1 cursor for select OrderNo,GoodsNo,OrderQty,AuditQty,Qty,NTaxPrice,
               TaxPrice,ID from com_orderdetail
 
  open c1
  select @err=@@error 
  if @err<>0
  begin
     exec up_rcverror @err,'orderdetail','open'
     close c1
     deallocate c1
     return @err
  end
  select @deletes=0,@updates=0,@inserts=0
  begin tran
  while 1 = 1
  begin
    fetch c1 into @OrderNo,@GoodsNo,@OrderQty,@AuditQty,@Qty,@NTaxPrice,@TaxPrice,@ID
    if @@fetch_status <> 0
       break
      if (select count(*) from orderdetail where orderno=@orderno and goodsno=@goodsno)=0
      begin
        insert orderdetail(OrderNo,GoodsNo,OrderQty,AuditQty,Qty,NTaxPrice,TaxPrice)
        values(@OrderNo,@GoodsNo,@OrderQty,@AuditQty,@Qty,@NTaxPrice,@TaxPrice)
        select @err=@@error
        if @err <>0
        begin    
          rollback tran
          exec up_rcverror @err,'orderdetail','insert'
          close c1
          deallocate c1
          return @err
        end
        select @inserts=@inserts+1
      end
  end
  commit tran
  exec up_rcvlog 'orderdetail',0,0,@inserts
  close c1
  deallocate c1
  truncate table com_orderdetail
  return @@error
go
print 'up_rcvorderdetail created!'
go

/* up_rcvdeptretail */

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

/* up_rcvcardconsume */

if (select count(*) from sysobjects where name='up_rcvcardconsume')>0
   drop proc up_rcvcardconsume
go
CREATE PROC up_rcvcardconsume
AS
  declare @deletes integer
  declare @updates integer
  declare @inserts integer
  declare @err integer
  set nocount on    
  begin tran
  delete cardconsume from cardconsume a,com_cardconsume b
    where a.cardno=b.cardno and a.deptno=b.deptno
  select @err=@@error,@updates=@@rowcount
  if @err<>0
  begin
    rollback
    exec up_rcverror @err,'cardconsume','com->delete'