CREATE DEFINER = ‘root‘@‘localhost‘
PROCEDURE myhouse.calculate_wtht_server_fee(IN store_Id VARCHAR(255), IN startDate VARCHAR(30), IN endDate VARCHAR(30))
BEGIN
    DECLARE i int DEFAULT 1;
    DECLARE v_heTongBian varchar(50);
    DECLARE v_wordType varchar(1);
    DECLARE v_albsz varchar(1);
    DECLARE v_bcChoinceTwo varchar(30);
    DECLARE v_serviceMiddleMoney int DEFAULT 0;
    DECLARE v_zhuanRangPriceT int DEFAULT 0;
    DECLARE v_bcChoinceNote11 varchar(10);
    DECLARE v_jdr varchar(10);

    DECLARE temp_count int;
    declare tmp_sum int default 0;

    DECLARE lp_flag boolean DEFAULT TRUE;

    DECLARE wtht_cursor CURSOR FOR SELECT heTongBian,wordType,albsz,bcChoinceTwo,serviceMiddleMoney,zhuanRangPriceT,bcChoinceNote11,jdr FROM wtht WHERE jdr in (select xm from sys_yh where find_in_set(StoreID, store_Id) and (isDel <> -1 or isDel is null)) AND creatTime >= startDate AND creatTime < endDate and wordType<>6 and wordType<>5 and (flag=3 or flag=-3) order by heTongBian,wordType desc;
    
      -- handler 句柄
  	DECLARE continue handler for NOT FOUND set lp_flag = false;
  
    drop table if exists temp_wtht_fee;
      -- 创建临时表收集数据
  	CREATE temporary TABLE `temp_wtht_fee` (
  		`agentName` varchar(20) COMMENT ‘经纪人姓名‘,
  		`secondContractTotal` int COMMENT ‘二手买卖签单业绩‘,
  		`secondSignNum` int COMMENT ‘签单数量‘
  	) ENGINE=InnoDB;	

    OPEN wtht_cursor;

    WHILE lp_flag DO
        fetch wtht_cursor into v_heTongBian,v_wordType,v_albsz,v_bcChoinceTwo,v_serviceMiddleMoney,v_zhuanRangPriceT,v_bcChoinceNote11,v_jdr;
        SELECT COUNT(*) INTO temp_count FROM temp_wtht_fee WHERE agentName=v_jdr;
        IF temp_count=0 THEN
            INSERT INTO temp_wtht_fee VALUES (v_jdr,0,0);
        end if;
        IF i%3<>0 THEN
            SET i = i + 1;
            if v_wordType = 4 then
              if v_bcChoinceTwo like ‘%⑨%‘ then
                set tmp_sum = tmp_sum + 3000;
              end if;
              if v_albsz = 3 then
                if v_bcChoinceTwo like ‘%⑧%‘ then
                  set tmp_sum = tmp_sum + v_zhuanRangPriceT*0.005;
                  fetch wtht_cursor into v_heTongBian,v_wordType,v_albsz,v_bcChoinceTwo,v_serviceMiddleMoney,v_zhuanRangPriceT,v_bcChoinceNote11,v_jdr;
                  set i = i + 1;
                  set tmp_sum = tmp_sum + v_serviceMiddleMoney;
                elseif v_bcChoinceTwo like ‘%?%‘ then
                  set tmp_sum = tmp_sum + v_bcChoinceNote11;
                  fetch wtht_cursor into v_heTongBian,v_wordType,v_albsz,v_bcChoinceTwo,v_serviceMiddleMoney,v_zhuanRangPriceT,v_bcChoinceNote11,v_jdr;
                  set i = i + 1;
                  set tmp_sum = tmp_sum + v_serviceMiddleMoney;
                else
                  fetch wtht_cursor into v_heTongBian,v_wordType,v_albsz,v_bcChoinceTwo,v_serviceMiddleMoney,v_zhuanRangPriceT,v_bcChoinceNote11,v_jdr;
                  set i = i + 1;
                  set tmp_sum = tmp_sum + v_serviceMiddleMoney;
                end if;
              else
                  fetch wtht_cursor into v_heTongBian,v_wordType,v_albsz,v_bcChoinceTwo,v_serviceMiddleMoney,v_zhuanRangPriceT,v_bcChoinceNote11,v_jdr;
                  set i = i + 1;
                  set tmp_sum = tmp_sum + v_serviceMiddleMoney;
              end if;
            elseif v_wordType = 1 then
              set tmp_sum = tmp_sum + v_serviceMiddleMoney;
            end if;
        ELSE
            SET i = 1;
            set tmp_sum = tmp_sum + v_serviceMiddleMoney;
            UPDATE temp_wtht_fee set secondSignNum=secondSignNum+1,secondContractTotal=secondContractTotal+tmp_sum WHERE agentName=v_jdr;
            set tmp_sum = 0;
        END IF;
     END WHILE;

    CLOSE wtht_cursor;

END