notice/doc/sql/apisql.sql

-- create table notice.apisql(
--     id int(11) not null auto_increment,
--     sqlname varchar(255) not null,
--     sqltext text not null,
--     primary key(id)
-- ) ENGINE = InnoDB DEFAULT CHARSET = utf8 comment 'sql语句';

update apisql set 
sqltext="select m.* from message m join message_type mt
	 on m.sys_name=mt.sys_name and m.pasture_id=mt.pasture_id and m.service_id=mt.service_id
	 where m.status=0 and (
		mt.remind_type=1 
		or
		(
			mt.remind_type=2 and 
			timestampdiff(minute,m.created_at,now())>mt.delay_time	and
			date_format(now(),'%H:%i')>mt.push_time	and 
			(
				cycle_type=1 or cycle_type=3 or
				( 
					cycle_type=2 and timestampdiff(minute,m.updated_at,now()) >mt.interval_time 
					and m.push_count<mt.push_limit
				)
			)
		)
		or 
		(
			mt.remind_type=3 and 
			 (
				mt.date_type=1 
				or (mt.date_type=2 and  weekday(now() ) +1 =mt.push_date)
				or (mt.date_type=3 and  (day(now()) =mt.push_date or DAYOFMONTH(now())<mt.push_date))
			 ) 
			 and date_format(now(),'%H:%i')>mt.push_time
		)
	 ) "