use Ks2007
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SynManageWaterEx]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SynManageWaterEx]
go
/*
表 dealwater 真实的流水表
 serialno  流水序号，自动生成
 Pos_No   控水器编号，参数获得
 Deal_time  流水入账时间
 Accountno  水控帐号
 Gdc6_Accountno 一卡通帐号
 Deal_money  实际的消费金额，支持金额为0的流水，精确到分
 CostPrice  单价，精确到0.1分
 Deal_Value  水量
 CostKind_Unit 计费单位
 now_money  流水入账后的帐户余额
 deal_mac  大一卡通的流水戳
 subsity   补助帐户金额
 is_subsity  为1表示消费的为补助帐户中的钱,大一卡通不支持使用补助帐户
 is_free   为1表示为免费消费
 CancelFlag  为1表示已经回绕,大一卡通不支持回绕
 mac    流水校验
*/

--该存储过程处理水控系统大一卡通流水
--暂时不考虑每种控水模式的补助,也不考虑双帐户的问题。
CREATE   PROCEDURE SynManageWaterEx(@Result int output,@PosNo smallint,@CardNo int,
@WaterNo smallint,@DealMoney int,@Amount smallint,@ConType smallint,@CardBalance int,
@UseCardTimes smallint ,@SynAccountNo int)
  AS
begin
 declare @Costkind_No int
 declare @RealMoney Money
 declare @curwaterno  numeric(18,0)
 declare @Accountno int
 declare @gdc6_accountno int
 declare @Pid_no int
 declare @CostPrice money
 declare @Costkind_Unit varchar(8)
  declare @posflag tinyint
 declare @ls_Costkind_unit smallint
 declare @Costkind_price money
 declare @Nowmoney money
-- declare @subsity money
 declare @mac float
 declare @Message varchar(128)
 select @Costkind_no = CostKind_No from Pos_mes where Pos_no = @PosNo and Class_no > 0
 if @@rowcount = 0 -- 控水器没有被分配，写入错误流水表
 begin
  insert into error_water(error_no,card_no,pos_no,deal_money,deal_val,pid_no,accountno,
   gdc6_accountno,error_mes,error_time,cardbalance,useCardTimes,SynCardNo) values(
   4,@Cardno,@PosNo,@DealMoney/100.0,@Amount,0,0,0,'分组未找到',getdate(),@CardBalance,
   @UseCardTimes,@SynAccountNo)
  set @Result = 4
  return
 end
  --防重发并处理流水号
  select top 1 posNo from PosWaterNo where PosNo=@PosNo and waterNo=@WaterNo
  if @@rowcount>0 begin
  set @Result = 5
  return
 end
  set @RealMoney=@DealMoney/100.0
 select @Nowmoney = @Cardbalance/100.0   
    declare @rowno smallint --将流水号表中多余的流水号删除,还不错多保留8笔
    set @rowno=1
    declare water_cursor cursor for
  select idno from poswaterno where PosNo=@posno order by idno desc
    open water_cursor
    fetch next from water_cursor into @curwaterno
    while @@FETCH_STATUS=0
    begin
 if @rowno>=8
 begin
     delete from Poswaterno where idno=@curwaterno
            break
 end
 fetch next from water_cursor into @curwaterno
 set @rowno=@rowno+1
    end
    close water_cursor
    deallocate water_cursor
    insert into poswaterno(posno,waterno) values(@posno,@waterNo)
 --处理流水号结束
 --验证帐户是否正确

 select * from dealwater where deal_mac=@UseCardTimes and gdc6_Accountno =@SynAccountno
 if @@rowcount > 0 begin
  set @Result = 5
  return   
 end
    select @Accountno = Accountno, @Pid_no = Pid_no from emmployee
  where Card_no = @CardNo and gdc6_Accountno =@SynAccountno and Work_mes in(1,2)
 if @@rowcount = 0 begin
     select @Accountno = Accountno, @Pid_no = Pid_no from emmployee
   where old_Card_no = @CardNo and gdc6_Accountno =@SynAccountno and Work_mes in(1,2)
  if @@rowcount = 0 begin
   insert into error_water(error_no,card_no,pos_no,deal_money,deal_val,pid_no,accountno,
    gdc6_accountno,error_mes,error_time,cardbalance,useCardTimes,SynCardNo) values(
    3,@CardNo,@PosNo,@DealMoney/100.0,@Amount,0,0,0,'帐户未找到',getdate(),@CardBalance,
    @UseCardTimes,@SynAccountNo)
   set @Result = 3
   return
  end
 end
 --验证帐户是否正确结束
 --获得其他的基本信息，包括单价、计费单位
 select @Posflag = Costkind_flag,@ls_Costkind_unit = Costkind_unit,@Costkind_price = Costkind_price
  from Costkind_mes where Costkind_no= @Costkind_no
 if @@rowcount > 0 begin
     if @Posflag =0
      set @Costkind_unit=convert(varchar(8),@ls_Costkind_unit*0.1)+'秒'
     else
      set @Costkind_unit=convert(varchar(8),@ls_Costkind_unit*0.1)+'升'
 end else begin
   insert into error_water(error_no,card_no,pos_no,deal_money,deal_val,pid_no,accountno,
    gdc6_accountno,error_mes,error_time) values(2,@CardNo,@PosNo,@DealMoney/100.0,@Amount,
    0,0,0,'控水模式未找到',getdate())
   set @Result = 2
   return
 end
 select top 1 @costprice =  Cost_price1 from price_mes where Pid_no = @Pid_no and Costkind_no = @Costkind_no
 if @@rowcount = 0 begin
  set @costprice = @costkind_price
 end
 --获得其他的基本信息结束
 --如果金额为0为免费流水，流水不回传，单价为0不能认为是免费流水
 if (@Realmoney = 0) begin
  begin Transaction freeTrans
  select @mac = dbo.get_mac(@Realmoney,@costprice,@Amount,@Nowmoney-@RealMoney)
  insert into dealwater(Pos_No,Deal_time,Accountno,Gdc6_Accountno,Deal_money,CostPrice,Deal_Value,
   CostKind_Unit,now_money,deal_mac,subsity,is_subsity,is_free,CancelFlag,mac) values (@PosNo,
   getdate(),@AccountNo,@SynAccountno,0,@costprice,@Amount,@Costkind_unit,@nowmoney,0,0,
   0,1,0,@mac)
  if @@error > 0 begin
   rollback Tran
   set @Result = 3
   return
  end
  update daily_rec set total_costnum=total_costnum+1
  set @Result = 1
  commit Transaction freeTrans
  return
 end
 --正常判断，按原来规则
 declare @ls_money money
 set @ls_money = @costprice*@Amount*1.0 
 --这里涉及到一个细节问题的处理，就是我们通常认为的不正常流水应该如何处理，原来的做法是写入unnormal_Water表
 --但是这样会造成卡库不平，对平台来说相当于卡库不平，本版本处理的时候不论是否符合 价格*水量=金额 规则，全部
 --按正常流水处理，仅仅剔除大额的流水
 if (@RealMoney<0) or (@RealMoney >20) begin --不正常的流水
  begin Transaction unnormalTrans
  insert into unnormal_dealwater (Card_no,Pos_no,Deal_time,Accountno,gdc6_Accountno,Deal_money,
   costprice,Deal_Value,CardBlance,useCardTimes,SynCardNo) values (@CardNo,@PosNo,getdate(),
   @Accountno,@SynAccountno,@Realmoney,@costprice,@Amount,@Cardbalance,@useCardTimes,@SynAccountno)
  set @result = 1
  commit Transaction unnormalTrans
  return
 end else begin
  begin Transaction dealTrans
   set @Message='单价:'+convert(char(4),@costPrice)+'/交易水量:'+convert(char(4),@amount)+'/计费单位:'+@Costkind_unit
   select @mac = dbo.get_mac(@Realmoney,@costprice,@Amount,@Nowmoney-@RealMoney)
   insert into dealwater(Pos_No,Deal_time,Accountno,Gdc6_Accountno,Deal_money,CostPrice,Deal_Value,
    CostKind_Unit,now_money,deal_mac,subsity,is_subsity,is_free,CancelFlag,mac) values (@PosNo,
    getdate(),@AccountNo,@SynAccountno,@Realmoney,@costprice,@Amount,@Costkind_unit,@nowmoney-@Realmoney,
    @UseCardTimes,0,0,0,0,@mac)
   if @@error > 0 begin
    rollback Tran
    set @Result = 3
    return
   end
   insert into Syntong20WaterEx(Operator,TranTime,CardNo,Balance,TranAmt,UseCardNum,Abstrac,Accountno,
    PosNo) values ('QYJ',getdate(),@CardNo,@Cardbalance,@DealMoney,@usecardtimes,@Message,@SynAccountno,@PosNo)
   update emmployee set Now_money = @Nowmoney - @realmoney where accountno = @accountno   
   update daily_rec set total_costmoney=total_costmoney+@RealMoney,total_costnum=total_costnum+1,pos_change=1
         update pos_mes set new_costmoney=@Realmoney,total_costnum=total_costnum+1,total_costmoney=total_costmoney+@RealMoney where pos_no=@PosNo  --更改当日累计值
           update pos_mes set max_costmoney=@RealMoney where pos_no=@posno and max_costmoney<@Realmoney  --更改当日消费还不错大额
--   update daily_rec set total_costmoney=total_costmoney+@RealMoney,total_costnum=total_costnum+1
      set @Result = 1
   if @@error > 0 begin
    rollback Tran
    set @Result = 3
    return
   end
  commit Transaction dealTrans
     return
 end 
end