delimiter |

CREATE

    EVENT statistics_event

    ON SCHEDULE

        EVERY 1 DAY

        STARTS CONCAT(CURRENT_DATE(), ' 00:00:00')

        #STARTS '2016-01-22 17:42:00'

    ON COMPLETION PRESERVE

    ENABLE

    COMMENT '统计数据'

    DO

        BEGIN

        DECLARE l_is_initialize INT(11);

        DECLARE l_yesterday VARCHAR(10);

        DECLARE l_yesterday_off_net_num INT(11);

        DECLARE l_yesterday_registered_num INT(11);

        DECLARE l_yesterday_con INT(11);

        DECLARE l_yesterday_active_user INT(11);

        DECLARE l_yesterday_per_con INT(11);

        DECLARE l_total_off_net_num INT(11);

        DECLARE l_total_registered_num INT(11);

        DECLARE l_total_con INT(11);

        DECLARE l_total_active_user INT(11);

        DECLARE l_total_per_con INT(11);

        DECLARE l_last_channel_id INT(11);

        DECLARE l_last_status_id INT(11);

        select AUTO_INCREMENT into l_last_channel_id from INFORMATION_SCHEMA.TABLES where TABLE_NAME='stat_channel';

        select AUTO_INCREMENT into l_last_status_id from INFORMATION_SCHEMA.TABLES where TABLE_NAME='stat_exe_status';

        INSERT INTO stat_exe_status(id,event_scheduler,start_time,status,create_time,update_time)

         VALUES (l_last_status_id,'统计event，每天零点执行',CURRENT_TIME(),1,CURRENT_TIME(),CURRENT_TIME());

        # 首次时 昨日统计数 means 截止昨日统计数

        SELECT count(1) INTO l_is_initialize from stat_channel;

        # 获取昨天日期字符串 2016-01-01

        SELECT CURRENT_DATE () - INTERVAL 1 DAY INTO l_yesterday ;

        IF l_is_initialize > 0 THEN

            # 昨日离网人数

            SELECT count(1) INTO l_yesterday_off_net_num from patient where `status`= 2 and SUBSTR(unsubscribeTime, 1, 10) = l_yesterday;

            # 昨天新注册用户数

            SELECT COUNT(1) INTO l_yesterday_registered_num from patient where SUBSTR(createtime, 1, 10) = l_yesterday;

            # 昨天咨询数

            SELECT count(1) INTO l_yesterday_con from crm_order where `status` =2 and SUBSTR(updatetime, 1, 10) = l_yesterday;

            # 昨日活跃数

            SELECT count(1) INTO l_yesterday_active_user from (SELECT count(1) from user_scan where SUBSTR(create_time, 1, 10) = l_yesterday  GROUP BY PATIENT_ID  ) t;

            # 昨日转换人数

            SELECT COUNT(1) INTO l_yesterday_per_con from patient t1 where SUBSTR(t1.createtime, 1, 10) = l_yesterday and EXISTS (SELECT * from crm_order t2 where t1.patientID = t2.patientid and SUBSTR(t2.updatetime, 1, 10) = l_yesterday and t2.status = 2);

        ELSE

        # 首次：截止昨日

            # 截止昨日离网人数

            SELECT count(1) INTO l_yesterday_off_net_num from patient where `status`= 2 and SUBSTR(unsubscribeTime, 1, 10) <= l_yesterday;

            # 截止昨天新注册用户数

            SELECT COUNT(1) INTO l_yesterday_registered_num from patient where SUBSTR(createtime, 1, 10) <= l_yesterday;

            # 截止昨天咨询数

            SELECT count(1) INTO l_yesterday_con from crm_order where `status` =2 and SUBSTR(updatetime, 1, 10) <= l_yesterday;

            # 截止昨日活跃数

            SELECT count(1) INTO l_yesterday_active_user from (SELECT count(1) from user_scan where SUBSTR(create_time, 1, 10) <= l_yesterday  GROUP BY PATIENT_ID  ) t;

            # 截止昨日转换人数

            SELECT COUNT(1) INTO l_yesterday_per_con from patient t1 where SUBSTR(t1.createtime, 1, 10) <= l_yesterday and EXISTS (SELECT * from crm_order t2 where t1.patientID = t2.patientid and SUBSTR(t2.updatetime, 1, 10) <= l_yesterday and t2.status = 2);

        END IF;

        INSERT INTO stat_channel (

            id,

            yesterday_off_net_num,

            yesterday_registered_num,

            yesterday_con,

            yesterday_active_user,

            yesterday_per_con,

            day

        ) VALUES(

            l_last_channel_id,

            l_yesterday_off_net_num,

            l_yesterday_registered_num,

            l_yesterday_con,

            l_yesterday_active_user,

            l_yesterday_per_con,

            CURRENT_TIME()

        );

        SELECT

            SUM(yesterday_off_net_num),

            SUM(yesterday_registered_num),

            SUM(yesterday_con),

            SUM(yesterday_active_user),

            SUM(yesterday_per_con)

        INTO

            l_total_off_net_num,

            l_total_registered_num,

            l_total_con,

            l_total_active_user,

            l_total_per_con

        FROM stat_channel;

        UPDATE stat_channel SET

            total_off_net_num = l_total_off_net_num,

            total_registered_num = l_total_registered_num,

            total_con = l_total_con,

            total_active_user = l_total_active_user,

            total_per_con = l_total_per_con

        where id = l_last_channel_id;

        UPDATE stat_exe_status SET

            end_time = CURRENT_TIME(),

            `status` = 2,

            update_time = CURRENT_TIME()

        WHERE id = l_last_status_id;

        END|

delimiter ;