select t1.base_Oid t1base_oid ,sum(t1.TwoGeneration_tgroup_commission_amount) TwoGeneration_tgroup_commission_amount from

(select base.oid base_Oid ,baseaccount.oid baseaccount_Oid ,baseaccount2.oid baseaccount2_Oid

 

 

 

,@productYearRate := CASE when product2.durationPeriodDays < 88 then @productYearRate := 0.5

when 88<= product2.durationPeriodDays and product2.durationPeriodDays <= 130 then @productYearRate := 1.3

when 135<= product2.durationPeriodDays and product2.durationPeriodDays < 190 then @productYearRate := 1.1

when 360<= product2.durationPeriodDays and product2.durationPeriodDays < 365 then @productYearRate := 0.8

END as productYearRate

 

 

,sum(product2.durationPeriodDays * torder2.orderAmount / 365 * @productYearRate * 0.0075) TwoGeneration_tgroup_commission_amount

,GROUP_CONCAT(baseaccount2.phoneNum) CPhoneDetail

,GROUP_CONCAT(torder2.orderAmount) COrderDetail

,GROUP_CONCAT(product2.durationPeriodDays) CProductDetail

,GROUP_CONCAT(@productYearRate) CRate

 

from T_MONEY_INVESTOR_BASEACCOUNT base

left join T_MONEY_INVESTOR_BASEACCOUNT_REFEREE referee

ON base.oid = referee.investorOid

left join T_MONEY_INVESTOR_BASEACCOUNT_REFER_DETAILS ref_detail

on referee.oid = ref_detail.refereeOid

left JOIN T_MONEY_INVESTOR_BASEACCOUNT baseaccount

on ref_detail.investorOid = baseaccount.oid

left join T_MONEY_INVESTOR_BASEACCOUNT_REFEREE referee2

ON baseaccount.oid = referee2.investorOid

left join T_MONEY_INVESTOR_BASEACCOUNT_REFER_DETAILS ref_detail2

on referee2.oid = ref_detail2.refereeOid

left JOIN T_MONEY_INVESTOR_BASEACCOUNT baseaccount2

on ref_detail2.investorOid = baseaccount2.oid

left join T_MONEY_INVESTOR_TRADEORDER torder2

on baseaccount2.oid = torder2.investorOid

left join T_GAM_PRODUCT product2

on product2.oid = torder2.productOid

 

left join t_money_investor_employee employee

ON base.idNum = employee.personalId

left join T_MONEY_INVESTOR_BANK bank

on bank.investorOid = base.oid

left join T_MONEY_INVESTOR_TRADEORDER torder

on baseaccount.oid = torder.investorOid

left join T_GAM_PRODUCT product

on product.oid = torder.productOid

where (employee.companyName like '%物业%' or employee.companyName like '%齐家科技%') and employee.companyName <> '航运物业公司' and torder.orderType = 'invest' and torder2.orderType='invest'

and torder.investorOid is not null and torder2.investorOid is not null

and torder2.orderAmount is not null and torder2.orderStatus in ('accepted' ,'confirmed')

and torder.orderStatus in ('accepted' ,'confirmed')

group by baseaccount.oid