thmodel/coinshare.bak.sql

create procedure CoinShare(IN tablename char(16), IN tablepk int)
  leave_proc:
BEGIN
  /*调用方法 call CoinShare('a', 1)
   返回值说明： 0-错误， 0-成功， 1成功，但之前已处理，本次忽略
    A: 会员（付款者：购买服务端普通会员 或 购买服务的人才）
    B: 人才/模特 （收款者：充值会员或者 提供服务的人才）
    C：经纪人
    D：模特的推荐人
    E：会员的推荐人
    F：平台
*/
/*金额*/
  declare AmountFee Decimal(10,2) DEFAULT 0;
  declare Fee_A Decimal(10,2) DEFAULT 0;
  declare Fee_B Decimal(10,2) DEFAULT 0;
  declare Fee_C Decimal(10,2) DEFAULT 0;
  declare Fee_D Decimal(10,2) DEFAULT 0;
  declare Fee_E Decimal(10,2) DEFAULT 0;
  declare Fee_F Decimal(10,2) DEFAULT 0;
/*人员PK*/
  declare membPK_A int DEFAULT 0;
  declare membPK_B int DEFAULT 0;
  declare membPK_C int DEFAULT 0;
  declare membPK_D int DEFAULT 0;
  declare membPK_E int DEFAULT 0;
  declare membPK_F int DEFAULT 0;
/*比例*/
  declare Rate_A Decimal(10,2) DEFAULT 0;
  declare Rate_B Decimal(10,2) DEFAULT 0;
  declare Rate_C Decimal(10,2) DEFAULT 0;
  declare Rate_D Decimal(10,2) DEFAULT 0;
  declare Rate_E Decimal(10,2) DEFAULT 0;
  declare Rate_F Decimal(10,2) DEFAULT 0;
#中文含义
  declare AccountRecord_stype varchar(20) DEFAULT '';
  declare AccountRecord_Atype_A varchar(20) DEFAULT '';
  declare AccountRecord_Atype_B varchar(20) DEFAULT '';
  declare AccountRecord_Atype_Other varchar(20) DEFAULT '';
  declare AccountRecord_abstract varchar(40) DEFAULT '';
  declare AccountRecord_ADate datetime;

#其它参数
  declare pro_AccFlag int;       #结算状态位，1表示可以计算，9表示已经计算
  declare pro_iCount int;
  declare pro_isR int default 0; #是否红包
  declare pro_isV int default 0; #是否视频
  declare pro_isS int default 0; #是否商业订单
  declare pro_isM int default 0; #是否模卡
  declare pro_isP int default 0; #是否活动
  declare pro_isInCash int default 0; #是否充值
  declare pro_isOutCash int default 0; #是否取现
  declare curAccLeft Decimal(10,2); #会员当前余额
  declare recordcount int;

#数据库事务变量
  DECLARE sql_error INTEGER DEFAULT 0;
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET sql_error=1;

#获得金额，会员和才人的memberPK
  if lower(tablename)= lower('RedPacket')  THEN #红包
     Set pro_isR = 1;
     Set AccountRecord_stype = '红包';
		 Set AccountRecord_Atype_A = '红包支付';
		 Set AccountRecord_Atype_B = '红包收入';
		 Set AccountRecord_Atype_Other = '红包分红收入';
     select Count(*),  Max(a.coin),
            Max(a.MemberPK),  max(b.MemberPK), max(B.BMemberPK),
            max(a.AccFlag)
          into pro_iCount, AmountFee,
            membPK_A, membPK_B, membPK_C,
            pro_AccFlag
     from RedPacket a inner join ModelInfo b on a.ModelPK = b.PK
     where a.PK = tablepk;
  elseif lower(tablename)= lower('VideoRecord')  THEN #视频
     Set pro_isV = 1;
     Set AccountRecord_stype = '聊天';
		 Set AccountRecord_Atype_A = '聊天支付';
		 Set AccountRecord_Atype_B = '聊天收入';
		 Set AccountRecord_Atype_Other = '聊天分成收入';
     select Count(*), Max(a.coin),
            Max(a.MemberPK),  max(b.MemberPK), max(B.BMemberPK),
            max(a.AccFlag)
          into pro_iCount, AmountFee,
            membPK_A, membPK_B, membPK_C,
            pro_AccFlag
     from VideoRecord a inner join ModelInfo b on a.ModelPK = b.PK
     where a.PK = tablepk;
  elseif lower(tablename)= lower('OrderRecord')  THEN #商业订单
     Set pro_isS = 1;
		 Set AccountRecord_stype = '订单';
		 Set AccountRecord_Atype_A = '订单支付';
		 Set AccountRecord_Atype_B = '服务收入';
		 Set AccountRecord_Atype_Other = '服务分成收入';
		 Select Count(*),  Max(a.Coin),
            Max(a.MemberPK), max(b.MemberPK), max(C.MemberPK),
            max(a.AccFlag)
					into pro_iCount, AmountFee,
            membPK_A, membPK_B, membPK_C,
            pro_AccFlag
		 from OrderRecord a Inner Join  ModelInfo b on a.ModelPK = b.PK
     		 Left Join BrokerInfor c on a.BrokerPK = c.PK
     where a.PK = tablepk;
  elseif lower(tablename)= lower('ModelInfo.PK')  THEN #是否模卡 （虚拟数据表）
		 Set AccountRecord_stype = '模卡';
		 Set AccountRecord_Atype_A = '模卡支付';
		 Set AccountRecord_Atype_B = '模卡收入';
		 Set AccountRecord_Atype_Other = '模卡分成收入';
		 Set pro_isM = 1;
     Set membPK_B = -1;
     Set pro_AccFlag = 1;
		 Select Count(*),  Max(b.Price), Max(a.MemberPK)
				into pro_iCount, AmountFee,  membPK_A
		 From ModelInfo a inner Join ModelCard b on a.CardPK = b.PK
     Where a.PK = tablepk;
  elseif lower(tablename)= lower('ParttySign')  THEN #活动(会员参加付款，平台收款, 模特不收款）
		 Set AccountRecord_stype = '活动';
		 Set AccountRecord_Atype_A = '活动支出';
		 Set AccountRecord_Atype_B = '活动收入';
		 Set AccountRecord_Atype_Other = '活动分成收入';
     Set pro_isP = 1;
		 Set membPK_B = -1;
     select Count(*),  Max(a.Fee),
					  Max(a.MemberPK), max(a.AccFlag)
				into pro_iCount, AmountFee,  membPK_A, pro_AccFlag
     from ParttySign a
     Where a.PK = tablepk;
  elseif lower(tablename)= lower('CashRecord')  THEN #充值，提现
	   Select Count(*),  Max(a.Fee),
            Max(case when LOCATE('提现',a.PState)>0 then a.memberPK else -1 end),
            Max(case when LOCATE('支付',a.PState)>0 then a.memberPK else -1 end),
						Max(case when LOCATE('提现',a.PState)>0 then 1 else 0 end),
            Max(case when LOCATE('支付',a.PState)>0 then 1 else 0 end),
						Max(a.AccFlag)
				into pro_iCount, AmountFee,  membPK_A, membPK_B,
            pro_isOutCash, pro_isInCash, pro_AccFlag
		 from CashRecord a
     where  a.PK = tablepk;

     if IFNULL(pro_isOutCash, 0) = 1 then
       Set AccountRecord_stype = '充值提现';
     elseif IFNULL(pro_isInCash, 0) = 1 then
       Set AccountRecord_stype = '充值提现';
     else
       Select -1 as Result, concat('无法识别当前业务是充值还是提现。') as mess;
       LEAVE leave_proc;
     end if;
	   Set AccountRecord_Atype_A = '提现支出';
	   Set AccountRecord_Atype_B = '充值币额收入';
	   Set AccountRecord_Atype_Other = '充值提现分成收入';
  else
    Select -1 as Result, concat('参数tablename=',tablename,',系统暂不支持处理') as mess;
    LEAVE leave_proc;
  end if;

#检查是否满足分摊计算条件
  Set AmountFee = IFNULL(AmountFee,0);
  if (pro_iCount <> 1) then
		Select -2 as Result, CONCAT('参数：',tablename,'或PK=',Cast(tablepk as CHAR),'传入错误') as mess;
    LEAVE leave_proc;
  elseif IFNULL(pro_AccFlag,0) = 9 THEN
		Select 1 as Result, '当前费用已处理' as mess;
    LEAVE leave_proc;
  elseif IFNULL(pro_AccFlag,0) <> 1 THEN
		Select -3 as Result, '当前业务还未完结' as mess;
    LEAVE leave_proc;
  elseif IFNULL(membPK_A,0) = 0 or IFNULL(membPK_B,0) = 0 THEN
		Select -5 as Result, '当前业务中，数据不完整：会员PK或才人PK为空' as mess;
    LEAVE leave_proc;
  elseif AmountFee <=0 then
		Select -6 as Result, '当前业务中，金额为零，无需处理。' as mess;
    LEAVE leave_proc;
  end if;


#获得经纪人，模特和会员各自的介绍人
#仅(红包+视频聊天+商业订单演出）才考虑分成部分的人员PK
#当人才的推荐人(D)为经纪人(c)时，作为推荐人的身份部分(D)，不参与分成
#推荐人(D)是模特时，不参与分成
  if (pro_isR>0 or pro_isV>0 or pro_isS>0) THEN
    Select IntroducerPK into membPK_D From memberinfo where PK = membPK_B;
    Select IntroducerPK into membPK_E From memberinfo where PK = membPK_A;
  end if;

  Set membPK_C = ifnull(membPK_C,0);
  Set membPK_D = ifnull(membPK_D,0);
  Set membPK_E = ifnull(membPK_E,0);
  if membPK_D = membPK_C THEN
    Set membPK_D=0;
  end if;

  if (membPK_D>0) and Exists (Select 1 From memberinfo Where PK = membPK_D AND MembType = 7) then
    Set membPK_D=0;
  END IF;

#获取各类分摊比例
  if pro_isR = 1 THEN #红包
     Set Rate_A = 100;
     select ratio_R, ratio_RB into Rate_B, Rate_C
     from  ModelInfo b
     where b.MemberPK = membPK_B; #模特比例，经纪人比例，这2个信息从模特中获取

     select IfNull(c.ratio_RR, b.ratio_RR) into Rate_D
     from memberinfo a Left Join ModelInfo b on a.PK = b.MemberPK
          Left Join BrokerInfor c on a.PK = C.MemberPK
     where a.pk = membPK_D; #模特的推荐人比例，从模特的推荐人的数据中获取

     select IfNull(c.ratio_RRM, b.ratio_RRM) into Rate_E
     from memberinfo a Left Join ModelInfo b on a.PK = b.MemberPK
          Left Join BrokerInfor c on a.PK = C.MemberPK
     where a.pk = membPK_E; #会员的推荐人比例，从会员的推荐人的数据中获取
   elseif pro_isV = 1  THEN #是否视频
     Set Rate_A = 100;
     select ratio_V, ratio_VB into Rate_B, Rate_C
     from ModelInfo b
     where b.MemberPK = membPK_B;

     select IfNull(c.ratio_VR, b.ratio_VR) into Rate_D
     from memberinfo a Left Join ModelInfo b on a.PK = b.MemberPK
          Left Join BrokerInfor c on a.PK = C.MemberPK
     where a.pk = membPK_D; #模特的推荐人比例，从模特的推荐人的数据中获取

     select IfNull(c.ratio_VRM, b.ratio_VRM) into Rate_E
     from memberinfo a Left Join ModelInfo b on a.PK = b.MemberPK
          Left Join BrokerInfor c on a.PK = C.MemberPK
     where a.pk = membPK_E; #会员的推荐人比例，从会员的推荐人的数据中获取

  elseif pro_isS = 1 THEN #是否商业订单
		 Set Rate_A = 100;
     select ratio_S, ratio_SB into Rate_B, Rate_C
     from ModelInfo b
     where b.MemberPK = membPK_B;

     select IfNull(c.ratio_SR, b.ratio_SR) into Rate_D
     from memberinfo a Left Join ModelInfo b on a.PK = b.MemberPK
          Left Join BrokerInfor c on a.PK = C.MemberPK
     where a.pk = membPK_D; #模特的推荐人比例，从模特的推荐人的数据中获取

     select IfNull(c.ratio_SRM, b.ratio_SRM) into Rate_E
     from memberinfo a Left Join ModelInfo b on a.PK = b.MemberPK
          Left Join BrokerInfor c on a.PK = C.MemberPK
     where a.pk = membPK_E; #会员的推荐人比例，从会员的推荐人的数据中获取

  elseif pro_isM =1 then #是否模卡
		 Set Rate_A = 100;
  elseif pro_isP=1 then #是否活动
		 Set Rate_A = 100;
  elseif pro_isInCash=1 then #是否充值
		 Set Rate_B = 100;
  elseif pro_isOutCash=1 then #是否取现
		 Set Rate_A = 100;
  else
    Select -7 as Result, concat('参数tablename=',tablename,',系统暂不支持处理') as mess;

    LEAVE leave_proc;
  end if;

  Set Rate_A = Rate_A/100.0;
  Set Rate_B = ifnull(Rate_B,0)/100.0;
  Set Rate_C = ifnull(Rate_C,0)/100.0;
  Set Rate_D = ifnull(Rate_D,0)/100.0;
  Set Rate_E = ifnull(Rate_E,0)/100.0;

  if (Rate_B>1 or Rate_C>1 or Rate_D>1 or Rate_E>1) THEN
    Select -8 as Result, '当前人才的费用分摊比例设置有误,比例系数不可大于100%.' as mess;
    LEAVE leave_proc;
  elseIF (Rate_B+Rate_C+Rate_D+Rate_E > 1.2) THEN
    Select -9 as Result, '当前人才的费用分摊比例设置有误,各分摊系数相加大于120%' as mess;
    LEAVE leave_proc;
  end if;

#计算各自分摊金额，平台分摊金额
  Select  AmountFee * (Case when membPK_A>0 then Rate_A else 0 end),
				  AmountFee * (Case when membPK_B>0 then Rate_B else 0 end),
				  AmountFee * (Case when membPK_C>0 then Rate_C else 0 end),
				  AmountFee * (Case when membPK_D>0 then Rate_D else 0 end),
				  AmountFee * (Case when membPK_E>0 then Rate_E else 0 end)
        into Fee_A, Fee_B, Fee_C, Fee_D, Fee_E;
  Set Fee_F = AmountFee - Fee_B - Fee_C - Fee_D - Fee_E;

#检查会员帐户余额是否足够扣除(模卡容许扣款至负数）。
  IF (pro_isR=1 OR pro_isV=1 OR pro_isS=1 OR pro_isP=1 OR pro_isOutCash=1 ) then
    Select IFNULL(coin_A,0) into curAccLeft From memberinfo where PK = membPK_A;
	  Set curAccLeft = IFNull(curAccLeft,0);
    if Fee_A > curAccLeft THEN
      Select -10 as Result, concat('当前会员帐户余额【',CAST(FORMAT(curAccLeft,0) as char),'】，不足以完成业务。') as mess;
      LEAVE leave_proc;
    end if;
  end if;

#设置状态位 + 保存分摊金额 + 记录账户变更表  （启用事务？）
  START TRANSACTION;
  #a)设置状态位
  if lower(tablename)= lower('RedPacket')  THEN
     Update RedPacket
     Set AccFlag = 9
     where PK = tablepk and AccFlag=1;
  elseif lower(tablename)= lower('VideoRecord')  THEN
     Update VideoRecord
     Set AccFlag = 9
     where PK = tablepk and AccFlag=1;
  elseif lower(tablename)= lower('OrderRecord')  THEN
     Update OrderRecord
     Set AccFlag = 9
     where PK = tablepk and AccFlag=1;
  elseif lower(tablename)= lower('ParttySign')  THEN
     Update ParttySign
     Set AccFlag = 9
     where PK = tablepk and AccFlag=1;
  elseif lower(tablename)= lower('CashRecord')  THEN
     Update CashRecord
     Set AccFlag = 9
     where PK = tablepk and AccFlag=1;
  elseif lower(tablename)= lower('ModelInfo.PK') then
     set recordcount=0; #即不做任何处理
  else
    ROLLBACK;
    Select -11 as Result, concat('参数tablename=',tablename,',系统暂不支持处理') as mess;
    LEAVE leave_proc;
  end if;

  Set recordcount = ROW_COUNT();
  if (pro_isM=0) and (recordcount <> 1 ) THEN #不考虑模卡的情况
    ROLLBACK;
    Select -12 as Result, '当前业务已被处理，不可重复处理。' as mess;
    LEAVE leave_proc;
  end if;

  #保存分摊金额
  IF (pro_isR =1 or pro_isV=1 or pro_isS = 1) THEN
    #>A 处理会员帐户
    if (membPK_A>0) then
      Update memberinfo
      Set coin_A = ifnull(coin_A,0) - Fee_A,
          coin_OR = IFNULL(coin_OR,0) + pro_isR*Fee_A,
          coin_OV = IFNULL(coin_OV,0) + pro_isV*Fee_A,
          coin_OS = IFNULL(coin_OS,0) + pro_isS*Fee_A
      where PK = membPK_A;
    end if;

		#红包和商业订单，需要扣减（预扣金额）
		if (pro_isR=1 or pro_isS = 1) then
			Update memberinfo
      Set Coin_OI = ifNull(Coin_OI,0) - Fee_A
      Where PK = membPK_A;
    end if;

    #> B模特
    if (membPK_B>0) then
      Update memberinfo
      Set coin_A = ifnull(coin_A,0) + Fee_B,
          coin_IR= IFNULL(coin_IR,0) + pro_isR*Fee_B,
          coin_IV= IFNULL(coin_IV,0) + pro_isV*Fee_B,
          coin_IS= IFNULL(coin_IS,0) + pro_isS*Fee_B
      where PK = membPK_B;
    end if;

    #> C经纪人
    if (membPK_C > 0) AND (Fee_C<>0) THEN
      Update memberinfo
      Set coin_A = ifnull(coin_A,0) + Fee_C,
          coin_IRR= IFNULL(coin_IRR,0) + pro_isR*Fee_C,
          coin_IVV= IFNULL(coin_IVV,0) + pro_isV*Fee_C,
          coin_ISS= IFNULL(coin_ISS,0) + pro_isS*Fee_C
      where PK = membPK_C;
    end if;

    #>D 模特（才人）的推荐人
    if (membPK_D > 0) AND (Fee_D<>0) THEN
      Update memberinfo
      Set coin_A = ifnull(coin_A,0) + Fee_D,
          coin_IRRR= IFNULL(coin_IRRR,0) + Fee_D
      where PK = membPK_D;
    end if;

    #>E 会员的推荐人
    if (membPK_E > 0) AND (Fee_E<>0) THEN
      Update memberinfo
      Set coin_A = ifnull(coin_A,0) + Fee_E,
          coin_IRRRM= IFNULL(coin_IRRR,0) + Fee_E
      where PK = membPK_E;
    end if;
  ELSEIF pro_isM >0 THEN #是否模卡
    Update memberinfo
    Set coin_A = ifnull(coin_A,0) - Fee_A,
        coin_OM = IFNULL(coin_OM,0) + Fee_A
    where PK = membPK_A;
  ELSEIF pro_isP >0 THEN #是否活动
    Update memberinfo
    Set coin_A = ifnull(coin_A,0) - Fee_A,
        coin_OP = IFNULL(coin_OP,0) + Fee_A
    where PK = membPK_A;
  ELSEIF pro_isInCash >0 THEN #是否充值
    Update memberinfo
    Set coin_A = ifnull(coin_A,0) + Fee_B,
        coin_I = IFNULL(coin_I,0) + Fee_B
    where PK = membPK_B;
  ELSEIF pro_isOutCash >0 THEN #是否取现
    Update memberinfo
    Set coin_A = ifnull(coin_A,0) - Fee_A,
        coin_O = IFNULL(coin_O,0) + Fee_A
    where PK = membPK_A;
  ELSE
    ROLLBACK;
    Select -13 as Result, '软件内部错误。' as mess;
    LEAVE leave_proc;
  END IF;

  #记录账户变动记录,为了充分利用索引,临时记录ADate
  Set AccountRecord_ADate = SYSDATE();

  if (membPK_A>0) AND (Fee_A<>0) then
    Insert Into AccountRecord( MemberPK, ADate, SType, LinkPK, AType, Coin )
    Values ( membPK_A, AccountRecord_ADate, AccountRecord_stype, tablepk, AccountRecord_Atype_A, Fee_A);
  end if;

  if (membPK_B>0) AND (Fee_B<>0) then
    if (Rate_B < 1) then
      Set AccountRecord_abstract = Concat('收入系数：',CAST(FORMAT(Rate_B*100,0) as char), '%');
    end if;
    Insert Into AccountRecord( MemberPK, ADate, SType, LinkPK, AType, Coin, abstract )
    Values ( membPK_B, AccountRecord_ADate, AccountRecord_stype, tablepk, AccountRecord_Atype_B, Fee_B, AccountRecord_abstract);
  end if;

  if (membPK_C>0) AND (Fee_C<>0) then
    Insert Into AccountRecord( MemberPK, ADate, SType, LinkPK, AType, Coin, abstract  )
    Values ( membPK_C, AccountRecord_ADate, AccountRecord_stype, tablepk, AccountRecord_Atype_Other, Fee_C, Concat('经纪人分成系数：',CAST(FORMAT(Rate_C*100,0) as char), '%'));
  end if;

  if (membPK_D>0) AND (Fee_D<>0) then
    Insert Into AccountRecord( MemberPK, ADate, SType, LinkPK, AType, Coin, abstract  )
    Values ( membPK_D, AccountRecord_ADate, AccountRecord_stype, tablepk, AccountRecord_Atype_Other, Fee_D, Concat('模特推荐人分红系数：',CAST(FORMAT(Rate_D*100,0) as char), '%'));
  end if;

  if (membPK_E>0) AND (Fee_E<>0) then
    Insert Into AccountRecord( MemberPK, ADate, SType, LinkPK, AType, Coin, abstract  )
    Values ( membPK_E, AccountRecord_ADate, AccountRecord_stype, tablepk, AccountRecord_Atype_Other, Fee_E, Concat('会员推荐人分红系数：',CAST(FORMAT(Rate_E*100,0) as char), '%'));
  end if;

  #记录当前余额
  Update AccountRecord,
         (select PK, coin_A from memberinfo Where PK IN (membPK_A, membPK_B, membPK_C, membPK_D, membPK_E) ) B
  Set AccountRecord.Balance = B.coin_A
  Where AccountRecord.ADate = AccountRecord_ADate
       and AccountRecord.SType= AccountRecord_stype
       and AccountRecord.LinkPK= tablepk
       and AccountRecord.MemberPK = B.PK;

  IF sql_error = 1 THEN
    ROLLBACK;
    Select -14 as Result, '数据保存发生未知异常' as mess;
    LEAVE leave_proc;
  ELSE
    COMMIT;
    #rollback;
  END IF;

#返回成功值
  Select 0 as Result, 'success' as mess;
END;