declare @tmessageid bigint
set @tmessageid=(select isnull(MAX(tmessageid),0) from tmessage)
insert into tmessage
(tmessageid, siteid, createdate, rmkenable, fmessage, ftype, fisread, freaddate, tenterprise_userid)
select @tmessageid+ROW_NUMBER()over(order by t1.fphonenumber), t1.siteid, getDate(), 0, '提醒：区域有'+convert(varchar(20),count(t2.tagentsid))+'个代理商今天未录入数据！', '系统', 0, null, t1.tenterprise_userid
from tenterprise_users t1
         inner join tagents t2 on t1.siteid=t2.siteid and t1.tenterprise_userid=t2.fsaleruserid
         left join tcustomer_reception t3 on t1.siteid=t3.siteid and t2.tagentsid=t3.tagentsid and convert(varchar(10),t3.createdate,120)= convert(varchar(10),GETDATE(),120)
         left join tsaleevent t4 on t1.siteid=t4.siteid and t2.tagentsid=t4.tagentsid and convert(varchar(10),t4.createdate,120)= convert(varchar(10),GETDATE(),120)
         left join tagents_cost t5 on t1.siteid=t5.siteid and t2.tagentsid=t5.tagentsid and convert(varchar(10),t5.createdate,120)= convert(varchar(10),GETDATE(),120)
         inner join tappmodelauth t6 on t1.siteid=t6.siteid and t6.fisdefault=1
where t1.fphonenumber is not null and t1.fusertype='企业' and (t2.fappmodel='专业版' or (t2.fappmodel is null and t6.fappmodel='专业版')) and t3.tcustomer_receptid is null and t4.tsaleeventid is null and t5.tagents_costid is null
group by t1.tenterprise_userid,t1.fphonenumber,t1.siteid

