1.

BEGINDECLAREsTempTEXT;/*父id*/DECLARErecomsTempChdTEXT;/*子id*/DECLAREv_membernoINTDEFAULT0;DECLAREv_arraymembernoINTDEFAULT0;DECLAREv_recommembernoINTDEFAULT0;DECLAREv_typepriceINTDEFAULT0;DECLAREv_avg_cashINTDEFAULT0;DECLAREv_avg_integralINTDEFAULT0;DECLAREv_countINTDEFAULT0;DECLAREv_count1INTDEFAULT0;DECLAREv_count2INTDEFAULT0;DECLAREv_count3INTDEFAULT0;DECLAREv_typeINTDEFAULT0;DECLAREv_jixiaoallDECIMALDEFAULT0;DECLAREv_child1000countINTDEFAULT0;DECLAREv_node10000TEXTDEFAULT'';DECLAREv_node10000countINTDEFAULT0;DECLAREv_node20wTEXTDEFAULT'';DECLAREv_node100wTEXTDEFAULT'';DECLAREv_node300wTEXTDEFAULT'';DECLAREv_node900wTEXTDEFAULT'';DECLAREadd20wpriceDECIMALDEFAULT0;DECLAREadd100wpriceDECIMALDEFAULT0;DECLAREadd300wpriceDECIMALDEFAULT0;DECLAREadd900wpriceDECIMALDEFAULT0;DECLAREadd20wchildcountINTDEFAULT0;DECLAREadd100wchildcountINTDEFAULT0;DECLAREadd300wchildcountINTDEFAULT0;DECLAREadd900wchildcountINTDEFAULT0;DECLAREdoneINTDEFAULT-1;DECLAREmycurCURSORFORSELECTmembernoFROMfathermembernos;DECLARECONTINUEHANDLERFORNOTFOUNDSETdone1;SETsTemp`getfatherNode`(membernos);SETrecomsTempChd`getRcommFather`(membernos);SELECTtypeprice,recommmemberno,`type`INTOv_typeprice,v_recommemberno,v_typeFROMmembermWHEREm.membernoIN(membernos);#设置总业绩和新增业绩会员数量CASEv_typeWHEN1THENUPDATEmember_achievementSETallachievementallachievement+v_typeprice,okachievementokachievement+v_typeprice,newachievementnewachievement+v_typeprice,microtypemicrotype+1WHEREFIND_IN_SET(memberno,sTemp)0ANDmemberno!membernos;WHEN2THENUPDATEmember_achievementSETallachievementallachievement+v_typeprice,okachievementokachievement+v_typeprice,newachievementnewachievement+v_typeprice,quicktypequicktype+1WHEREFIND_IN_SET(memberno,sTemp)0ANDmemberno!membernos;WHEN3THENUPDATEmember_achievementSETallachievementallachievement+v_typeprice,okachievementokachievement+v_typeprice,newachievementnewachievement+v_typeprice,startypestartype+1WHEREFIND_IN_SET(memberno,sTemp)0ANDmemberno!membernos;ENDCASE;#直接推荐奖20%UPDATEmember_achievementSETnotrecommendnotrecommend+(v_typeprice*0.2),getcashgetcash+(v_typeprice*0.2),newrecommendnewrecommend+(v_typeprice*0.2)WHEREmembernov_recommemberno;#垂直推荐奖4%UPDATEmember_achievementSETnewverticalawardnewverticalaward+(v_typeprice*0.04),getcashgetcash+(v_typeprice*0.04),verticalawardverticalaward++(v_typeprice*0.04)WHEREmembernoIN(SELECTrecommmembernoFROMmemberWHEREmembernov_recommemberno);#跨区推荐奖%6UPDATEmember_achievementSETnewcrossawardnewcrossaward+(v_typeprice*0.06),getcashgetcash+(v_typeprice*0.06),crossawardcrossaward++(v_typeprice*0.06)WHEREmembernoIN(SELECTrecommmembernoFROMmemberWHEREmembernoIN(SELECTrecommmembernoFROMmemberWHEREmembernov_recommemberno));SELECTCOUNT(1)INTOv_count1FROMmemberm,member_achievementaWHEREm.bonus0ANDm.membernoa.membernoANDm.`type`1ANDa.marketshare5000ANDm.membernoIN(SELECTmembernoFROMmemberWHERErecommmembernomembernos);SELECTCOUNT(1)INTOv_count2FROMmemberm,member_achievementaWHEREm.bonus0ANDm.membernoa.membernoANDm.`type`2ANDa.marketshare25000;SELECTCOUNT(1)INTOv_count3FROMmemberm,member_achievementaWHEREm.bonus0ANDm.membernoa.membernoANDm.`type`3ANDa.marketshare75000;SETv_count1*v_count1+4*v_count2+10*v_count3;IFv_count0THENSELECTROUND(v_typeprice/(10*v_count*2),1),ROUND(v_typeprice/(10*v_count*2),1)INTOv_avg_cash,v_avg_integralFROMDUAL;ENDIF;##市场分红奖励UPDATEmember_achievementa,membermSETa.getcasha.getcash+CASEWHEN(v_avg_cash+a.marketshare)5000THEN(v_avg_cash+a.marketshare)-5000ELSEv_avg_cashEND,a.integrala.integral+CASEWHEN(v_avg_integral+a.marketinetegral)5000THEN(v_avg_integral+a.marketinetegral)-5000ELSEv_avg_integralEND,a.marketinetegralCASEWHEN(v_avg_integral+a.marketinetegral)5000THEN5000ELSEv_avg_integral+a.marketinetegralEND,a.marketshareCASEWHEN(v_avg_cash+a.marketshare)5000THEN5000ELSEv_avg_cash+a.marketshareEND,a.newmarketinetegralCASEWHEN(v_avg_integral+a.marketinetegral)5000THEN0ELSEv_avg_integral+a.newmarketinetegralEND,a.newmarketshareCASEWHEN(v_avg_cash+a.marketshare)5000THEN0ELSEv_avg_cash+a.newmarketshareENDWHEREa.membernom.membernoANDm.bonus0ANDm.`type`1ANDa.marketshare5000ANDEXISTS(SELECT1FROMmemberWHERErecommmembernoa.memberno);UPDATEmember_achievementa,membermSETa.getcasha.getcash+CASEWHEN(v_avg_cash+marketshare)25000THEN(v_avg_cash+marketshare)-25000ELSEv_avg_cashEND,a.integrala.integral+CASEWHEN(v_avg_integral+a.marketinetegral)25000THEN(v_avg_integral+a.marketinetegral)-25000ELSEv_avg_integralEND,a.marketinetegralCASEWHEN(v_avg_integral+a.marketinetegral)25000THEN25000ELSEv_avg_cash+a.marketinetegralEND,a.marketshareCASEWHEN(v_avg_cash+a.marketshare)25000THEN25000ELSEv_avg_cash+a.marketshareEND,a.newmarketinetegralCASEWHEN(v_avg_integral+a.marketinetegral)25000THEN0ELSEv_avg_cash+a.newmarketinetegralEND,a.newmarketshareCASEWHEN(v_avg_cash+a.marketshare)25000THEN0ELSEv_avg_cash+a.newmarketshareENDWHEREa.membernom.membernoANDm.bonus0ANDm.`type`2;UPDATEmember_achievementa,membermSETa.getcasha.getcash+CASEWHEN(v_avg_cash+a.marketshare)75000THEN(v_avg_cash+a.marketshare)-75000ELSEv_avg_cashEND,a.integrala.integral+CASEWHEN(v_avg_integral+a.marketinetegral)75000THEN(v_avg_integral+a.marketinetegral)-75000ELSEv_avg_integralEND,a.marketinetegralCASEWHEN(v_avg_integral+a.marketinetegral)75000THEN75000ELSEv_avg_cash+a.marketinetegralEND,a.marketshareCASEWHEN(v_avg_cash+a.marketshare)75000THEN75000ELSEv_avg_cash+a.marketshareEND,a.newmarketinetegralCASEWHEN(v_avg_integral+a.marketinetegral)75000THEN0ELSEv_avg_cash+a.newmarketinetegralEND,a.newmarketshareCASEWHEN(v_avg_cash+a.marketshare)75000THEN0ELSEv_avg_cash+a.newmarketshareENDWHEREa.membernom.membernoANDm.bonus0ANDm.`type`3;##绩效分红和董事分红处理TRUNCATETABLE`fathermembernos`;INSERTINTOfathermembernos(memberno)SELECTa.membernoFROMmemberm,member_achievementaWHEREm.bonus0ANDm.state1ANDa.membernom.membernoANDallachievement200000ANDFIND_IN_SET(m.memberno,sTemp)0ANDa.memberno!membernos;SETv_node10000'$';SETv_node10000count0;OPENmycur;myLoop:LOOP--提取游标里的数据，这里只有一个，多个的话也一样；FETCHmycurINTOv_memberno;#CALL`accountAllYeji`(v_memberno,v_typeprice);SELECTallachievementINTOv_jixiaoallFROMmember_achievementWHEREmembernov_memberno;IFv_jixiaoall10000000THENSELECTCOUNT(*)INTOv_child1000countFROMmember_achievementa,membermWHEREa.membernom.membernoANDm.state1ANDa.membernoIN(SELECTmembernoFROMmemberWHERErecommmembernov_memberno)ANDallachievement1000000;IFv_child1000count0THENSETv_node10000CONCAT(v_node10000,v_memberno);SETv_node10000countv_node10000count+1;ENDIF;ELSE##处理20万IFv_jixiaoall200000ANDv_jixiaoall1000000THENSELECTCOUNT(*)INTOadd20wchildcountFROMmember_achievementa,membermWHEREa.membernom.membernoANDm.state1ANDa.membernoIN(SELECTmembernoFROMmemberWHERErecommmembernov_memberno)ANDallachievement2000000;IFadd20wchildcount0THENSETadd20wpricev_typeprice;IFv_jixiaoall-200000v_typepriceTHENSETadd20wpricev_jixiaoall-200000;ENDIF;IFadd20wprice0THENUPDATEmember_achievementSETintegralintegral+add20wprice*0.08*0.1,newbmoneynewbmoney+add20wprice*0.08*0.9,bmoneybmoney+add20wprice*0.08*0.9,getcashgetcash+add20wprice*0.08*0.9wheremembernov_memberno;ENDIF;ENDIF;##处理100万ELSEIFv_jixiaoall1000000ANDv_jixiaoall3000000THENSELECTCOUNT(*)INTOadd100wchildcountFROMmember_achievementa,membermWHEREa.membernom.membernoANDm.state1ANDa.membernoIN(SELECTmembernoFROMmemberWHERErecommmembernov_memberno)ANDallachievement10000000;IFadd100wchildcount0THENSETadd100wpricev_typeprice;IFv_jixiaoall-1000000v_typepriceTHENSETadd100wpricev_jixiaoall-1000000;ENDIF;IFadd100wprice0THENIFadd20wprice0THENUPDATEmember_achievementSETintegralintegral+add20wprice*0.04*0.1,newbmoneynewbmoney+add20wprice*0.04*0.9,bmoneybmoney+add20wprice*0.04*0.9,getcashgetcash+add20wprice*0.04*0.9wheremembernov_memberno;ELSEUPDATEmember_achievementSETintegralintegral+add20wprice*0.12*0.1,newbmoneynewbmoney+add20wprice*0.12*0.9,bmoneybmoney+add20wprice*0.12*0.9,getcashgetcash+add20wprice*0.12*0.9wheremembernov_memberno;ENDIF;ENDIF;ENDIF;##处理300万ELSEIFv_jixiaoall3000000ANDv_jixiaoall9000000THENSELECTCOUNT(*)INTOadd300wchildcountFROMmember_achievementa,membermWHEREa.membernom.membernoANDm.state1ANDa.membernoIN(SELECTmembernoFROMmemberWHERErecommmembernov_memberno)ANDallachievement30000000;IFadd300wchildcount0THENSETadd300wpricev_typeprice;IFv_jixiaoall-3000000v_typepriceTHENSETadd300wpricev_jixiaoall-3000000;ENDIF;IFadd300wprice0THENIFadd20wprice0ORadd100wprice0THENUPDATEmember_achievementSETintegralintegral+add20wprice*0.04*0.1,newbmoneynewbmoney+add20wprice*0.04*0.9,bmoneybmoney+add20wprice*0.04*0.9,getcashgetcash+add20wprice*0.04*0.9wheremembernov_memberno;ELSEUPDATEmember_achievementSETintegralintegral+add20wprice*0.16*0.1,newbmoneynewbmoney+add20wprice*0.16*0.9,bmoneybmoney+add20wprice*0.16*0.9,getcashgetcash+add20wprice*0.16*0.9wheremembernov_memberno;ENDIF;ENDIF;ENDIF;##处理900万ELSEIFv_jixiaoall9000000THENSELECTCOUNT(*)INTOadd900wchildcountFROMmember_achievementa,membermWHEREa.membernom.membernoANDm.state1ANDa.membernoIN(SELECTmembernoFROMmemberWHERErecommmembernov_memberno)ANDallachievement90000000;IFadd900wchildcount0THENSETadd900wpricev_typeprice;IFv_jixiaoall-3000000v_typepriceTHENSETadd900wpricev_jixiaoall-9000000;ENDIF;IFadd900wprice0THENIFadd20wprice0ORadd100wprice0ORadd300wprice0THENUPDATEmember_achievementSETintegralintegral+add20wprice*0.04*0.1,newbmoneynewbmoney+add20wprice*0.04*0.9,bmoneybmoney+add20wprice*0.04*0.9,getcashgetcash+add20wprice*0.04*0.9wheremembernov_memberno;ELSEUPDATEmember_achievementSETintegralintegral+add20wprice*0.2*0.1,newbmoneynewbmoney+add20wprice*0.2*0.9,bmoneybmoney+add20wprice*0.2*0.9,getcashgetcash+add20wprice*0.2*0.9wheremembernov_memberno;ENDIF;ENDIF;ENDIF;ENDIF;ENDIF;--声明结束的时候IFdone1THENLEAVEmyLoop;ENDIF;--这里做你想做的循环的事件ENDLOOPmyLoop;--关闭游标CLOSEmycur;UPDATEmember_achievementSETgetcashgetcash+ROUND((v_typeprice*0.03)/v_node10000count,1),newdongshimoneynewdongshimoney+ROUND((v_typeprice*0.03)/v_node10000count,1)WHEREFIND_IN_SET(memberno,v_node10000)0;END