db/functions.sql

CREATE PROCEDURE `pro_resume_work_daily`(IN `lstr_day` varchar(32))
BEGIN

	-- 先删除旧的
	DELETE t FROM ts_resume_work_form t WHERE t.`name` = lstr_day;
	
	-- 再新增新的
	-- 1, 统计总的, 按机构分组
	INSERT INTO ts_resume_work_form (statis_id, org_id, st_type, `name`, `value`)
	SELECT UUID_SHORT(), t.org_id, 'total', lstr_day, count( 1 ) 
	FROM
		ta_resume_work_form t 
	WHERE DATE_FORMAT( t.create_date, '%Y-%m-%d' ) = lstr_day  -- lstr_day 格式 YYYY-MM-DD
		AND t.`status` > - 1
		GROUP BY t.org_id;
		
	-- 不分组
	INSERT INTO ts_resume_work_form (statis_id, org_id, st_type, `name`, `value`)
	SELECT UUID_SHORT(), null, 'total', lstr_day, count( 1 ) 
	FROM
		ta_resume_work_form t 
	WHERE DATE_FORMAT( t.create_date, '%Y-%m-%d' ) = lstr_day  -- lstr_day 格式 YYYY-MM-DD
		AND t.`status` > - 1;
		
	-- 2, 统计正常的，按机构分组
	INSERT INTO ts_resume_work_form (statis_id, org_id, st_type, `name`, `value`)
	SELECT UUID_SHORT(), t.org_id, 'normal', lstr_day, count( 1 ) 
	FROM
		ta_resume_work_form t 
	WHERE
		( t.nucleic_is_normal is NULL OR t.nucleic_is_normal = 1 )
		AND ( t.antigen_is_normal is NULL OR t.antigen_is_normal = 1 )
		AND DATE_FORMAT( t.create_date, '%Y-%m-%d' ) = lstr_day  -- lstr_day 格式 YYYY-MM-DD
		AND t.`status` > - 1
		GROUP BY t.org_id;

-- 不分组
	INSERT INTO ts_resume_work_form (statis_id, org_id, st_type, `name`, `value`)
	SELECT UUID_SHORT(), null, 'normal', lstr_day, count( 1 ) 
	FROM
		ta_resume_work_form t 
	WHERE
		( t.nucleic_is_normal is NULL OR t.nucleic_is_normal = 1 )
		AND ( t.antigen_is_normal is NULL OR t.antigen_is_normal = 1 )
		AND DATE_FORMAT( t.create_date, '%Y-%m-%d' ) = lstr_day  -- lstr_day 格式 YYYY-MM-DD
		AND t.`status` > - 1;
		
	-- 3, 统计异常, 按机构分组
	INSERT INTO ts_resume_work_form (statis_id, org_id, st_type, `name`, `value`)
	SELECT UUID_SHORT(), t.org_id, 'abnormal', lstr_day, count( 1 ) 
	FROM
		ta_resume_work_form t 
	WHERE
		( t.nucleic_is_normal = 0 OR t.antigen_is_normal = 0 )
		AND DATE_FORMAT( t.create_date, '%Y-%m-%d' ) = lstr_day  -- lstr_day 格式 YYYY-MM-DD
		AND t.`status` > - 1
		GROUP BY t.org_id;
		
	-- 不分组
	INSERT INTO ts_resume_work_form (statis_id, org_id, st_type, `name`, `value`)
	SELECT UUID_SHORT(), null, 'abnormal', lstr_day, count( 1 ) 
	FROM
		ta_resume_work_form t 
	WHERE
		( t.nucleic_is_normal = 0 OR t.antigen_is_normal = 0 )
		AND DATE_FORMAT( t.create_date, '%Y-%m-%d' ) = lstr_day  -- lstr_day 格式 YYYY-MM-DD
		AND t.`status` > - 1;

END;;


CREATE EVENT `job_resume_work_daily`
ON SCHEDULE
EVERY '1' DAY STARTS '2022-05-16 01:00:00'
DO call pro_resume_work_daily(DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 1 DAY), '%Y-%m-%d'));;