CREATE DEFINER = `statistics_sql`@`%` PROCEDURE `2004_StatVulHostAsset`(IN `runMode` int(11), IN `startdate` date, IN `enddate` date)
    MODIFIES SQL DATA
BEGIN
  
  DECLARE starttime01 BIGINT DEFAULT  UNIX_TIMESTAMP();
  DECLARE starttime02 BIGINT DEFAULT  UNIX_TIMESTAMP();
  DECLARE starttime03 BIGINT DEFAULT  UNIX_TIMESTAMP();
  DECLARE endtime BIGINT DEFAULT  UNIX_TIMESTAMP();
  DECLARE querydate DATE DEFAULT NULL;
  DECLARE Var01 DATETIME DEFAULT NOW();
  DECLARE Var02 DATETIME DEFAULT NOW();
  DECLARE Var03 INT DEFAULT 0;
  DECLARE Var04 INT DEFAULT 0;
  DECLARE Var05 INT DEFAULT 0;
  DECLARE Var06 INT DEFAULT 0;
  DECLARE Var07 DATETIME DEFAULT NOW();
  DECLARE Var08 DATETIME DEFAULT NOW();
  DECLARE LogStatType INT DEFAULT 2004;
  DECLARE WeakName TEXT CHARACTER SET utf8mb4 DEFAULT '应用弱口令';
  
  DECLARE FlagVal INT DEFAULT 0; 
        DECLARE tmp_exec_statement TEXT CHARACTER SET utf8mb4 DEFAULT NULL;
  
  DECLARE tmp_fieldlst VARChAR(256) DEFAULT "#,risk_usable_flag#,unrepair_flag#,risk_usable_flag,unrepair_flag#,high_risk_flag#,risk_usable_flag,high_risk_flag#,unrepair_flag,high_risk_flag#,risk_usable_flag,unrepair_flag,high_risk_flag";
  
  
  SET starttime01=UNIX_TIMESTAMP();
  INSERT INTO hhit_stat_process_log (stat_date01, stat_date02,stat_type, start_time) 
    VALUES(DATE_FORMAT(NOW(),'%Y-%m-%d'),DATE_FORMAT(NOW(),'%Y-%m-%d'),LogStatType, starttime01); 
  
  IF runMode=0 THEN 
    DROP TABLE IF EXISTS `statistics_hhit_vul_host`;
  END IF;

  CREATE TABLE IF NOT EXISTS `statistics_hhit_vul_host` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `ts` int(11) NOT NULL DEFAULT 1 COMMENT '租户TS',
    `stat_type` int(1) NOT NULL DEFAULT '1' COMMENT '统计的类型1表示天，2表示按周，3表示按月，4表示按年',
    `stat_date01` date NOT NULL DEFAULT '0000-00-00' COMMENT '统计开始日期',
    `stat_date02` date NOT NULL DEFAULT '0000-00-00' COMMENT '统计结束日期',
    `stat_year` char(4) NOT NULL COMMENT '统计日期年份',
    `stat_month` char(2) NOT NULL COMMENT '统计日期月份',
    `stat_week` char(2) NOT NULL COMMENT '统计日期周',
    `is_group_by` int(11) NOT NULL DEFAULT 0 COMMENT '是否分组统计 0不分组统计, 1分组统计',
    `org_id` int(11) NOT NULL DEFAULT 0 COMMENT '组织ID',
    `org_ts` int(10) NOT NULL DEFAULT '1' COMMENT '组织TS',
    `org_name` varchar(256) NOT NULL COMMENT '组织名称',
    `org_code` varchar(64) NOT NULL COMMENT '组织代码',
    `bs_id` int(11) NOT NULL DEFAULT 0 COMMENT '业务系统ID',
    `bs_name` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '业务系统名称',
    `high_risk_flag` int(11)  NOT NULL DEFAULT 0 COMMENT '漏洞高风险标识 0 非高风险 1高风险',
    `unrepair_flag` int(11)  NOT NULL DEFAULT 0 COMMENT '漏洞未修复标识 0 已修复, 1 未修复',
    `risk_usable_flag` int(11) NOT NULL DEFAULT 0 COMMENT '是否验证可利用漏洞标志 0 否 1是',
    `name` varchar(255) NOT NULL DEFAULT '' COMMENT '名称',
    `cve_code` varchar(32) NOT NULL DEFAULT '' COMMENT 'CVE编号',
    `cnnvd_code` varchar(32) NOT NULL DEFAULT '' COMMENT 'cnnvd编号',
    `severity` tinyint(3) NOT NULL DEFAULT '0' COMMENT '风险等级[0:信息,1:低危,2:中危,3:高危,4:超危]',
    `total_asset_nums` int(11) NOT NULL DEFAULT 0 COMMENT '漏洞影响主机数量',
    
    `create_id` int(11) NOT NULL DEFAULT '0' COMMENT '创建者ID',
    `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `modify_id` int(11) NOT NULL DEFAULT '0' COMMENT '修改者ID',
    `modify_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
    PRIMARY KEY (`id`),
    UNIQUE KEY `process_stat_index` (`stat_date01`,`stat_type`,`stat_year`,`stat_month`,`stat_week`,`is_group_by`,`org_id`,`org_ts`,`bs_id`,`high_risk_flag`,`unrepair_flag`,`risk_usable_flag`,`cve_code`) USING BTREE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='漏洞影响面排名';

   
  SET querydate=startdate;
  WHILE DATEDIFF(enddate,querydate)>=0 DO
    SET Var01 = DATE_FORMAT(querydate,'%Y-%m-%d');
    SET Var02 = DATE_FORMAT(querydate,'%Y-%m-%d');
    SET starttime02=UNIX_TIMESTAMP();    
    SET Var03=1;
    SET Var04=YEAR(querydate);
    SET Var05=MONTH(querydate);
    SET Var06=WEEKOFYEAR(querydate);
    SET Var07 = DATE_FORMAT(Var01,'%Y-%m-%d 00:00:00');
    SET Var08 = DATE_FORMAT(Var02,'%Y-%m-%d 23:59:59');

    SET FlagVal = 0;
    WHILE FlagVal < 8 DO 
      
      SET tmp_exec_statement=CONCAT(" REPLACE INTO `statistics_hhit_vul_host` ( ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"    `stat_type`,`stat_date01`,`stat_date02`,`stat_year`,`stat_month`,`stat_week`,`is_group_by`, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"    `org_id`,`org_ts`,`org_name`,`org_code`, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"    `bs_id`,`bs_name`, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"    `high_risk_flag`,`unrepair_flag`,`risk_usable_flag`, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"    `name`,`cve_code`,`cnnvd_code`,`severity`,`total_asset_nums` ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement," ) ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement," SELECT ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"   T100.`stat_type`,T100.`stat_date01`,T100.`stat_date02`,T100.`stat_year`,T100.`stat_month`,T100.`stat_week`, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"  ",FlagVal,"  AS `is_group_by`, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"   IFNULL(org.id,0) AS `org_id`, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"   IFNULL(org.org_ts,1) AS `org_ts`, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"   IFNULL(org.org_name,'') AS `org_name`, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"   IFNULL(org.org_code,'') AS `org_code`, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"   0 AS bs_id, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"   '' AS bs_name, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"   T100.high_risk_flag AS high_risk_flag, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"   T100.unrepair_flag as unrepair_flag, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"   T100.risk_usable_flag AS risk_usable_flag, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"   T100.name AS name, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"   T100.cve_code AS cve_code, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"   T100.cnnvd_code AS cnnvd_code, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"   T100.severity AS severity, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"   T100.total_asset_nums AS total_asset_nums ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement," FROM ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement," ( ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"     SELECT ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,     Var03 , "     AS stat_type , ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"'", Var01 , "' AS stat_date01, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"'", Var02 , "' AS stat_date02, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,     Var04 , "     AS stat_year, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,     Var05 , "     AS stat_month, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,     Var06 , "     AS stat_week, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"     IFNULL(haor.org_id,0) AS org_id, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"     IF(haor.severity >= 3,1,0) AS high_risk_flag, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"     IF(haor.repair_status = 0,1,0) AS unrepair_flag,");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"     IF(haor.source_type = 1 OR haor.source_type = 2,1,0) AS risk_usable_flag, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"     haor.cve_code AS cve_code, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"     haor.cnnvd_code AS cnnvd_code, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"     (CASE haor.cve_code  ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"       WHEN '' THEN ","'WeakName'" ," ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"       ELSE haor.name ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"     END) AS name, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"     haor.severity AS severity, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"     COUNT(DISTINCT IF(DATEDIFF('",Var01,"', haor.vul_create_time)>=0 , haor.asset_uid,NULL)) AS total_asset_nums ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"     FROM  ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"         hhit_vul_risk_asset_org_relevance haor ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"     GROUP BY org_id,cve_code ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,     SUBSTRING_INDEX(SUBSTRING_INDEX(tmp_fieldlst,"#",FlagVal+1),"#",-1) ); 
      SET tmp_exec_statement=CONCAT(tmp_exec_statement," ) AS T100 ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement," LEFT JOIN v_hhit_sbsys_org_base org ON org.id = T100.org_id; ");

      SET @tmp_exec_statement=tmp_exec_statement;
      PREPARE stmt FROM @tmp_exec_statement;
                        EXECUTE stmt;

      
      SET tmp_exec_statement=CONCAT("REPLACE INTO `statistics_hhit_vul_host` (");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"`stat_type`,`stat_date01`,`stat_date02`,`stat_year`,`stat_month`,`stat_week`,`is_group_by`,");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"`org_id`,`org_ts`,`org_name`,`org_code`,");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"   `bs_id`,`bs_name`,");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"   `high_risk_flag`,`unrepair_flag`,`risk_usable_flag`,");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"   `name`,`cve_code`,`cnnvd_code`,`severity`,`total_asset_nums`)");
      
      SET tmp_exec_statement=CONCAT(tmp_exec_statement," SELECT ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"       T100.`stat_type`,T100.`stat_date01`,T100.`stat_date02`,T100.`stat_year`,T100.`stat_month`,T100.`stat_week`, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"  ", FlagVal+8 ,"  AS `is_group_by`, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"       IFNULL(org.id,0) AS `org_id`, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"       IFNULL(org.org_ts,1) AS `org_ts`, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"       IFNULL(org.org_name,'') AS `org_name`, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"       IFNULL(org.org_code,'') AS `org_code`, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"       IFNULL(hb.id,0) AS bs_id, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"       IFNULL(hb.name,'') AS bs_name, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"       T100.high_risk_flag AS high_risk_flag, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"       T100.unrepair_flag as unrepair_flag, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"       T100.risk_usable_flag AS risk_usable_flag, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"       T100.name AS name, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"       T100.cve_code AS cve_code, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"       T100.cnnvd_code AS cnnvd_code, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"       T100.severity AS severity, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"       T100.total_asset_nums AS total_asset_nums ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"     FROM ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"     ( ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"         SELECT ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,     Var03 , "     AS stat_type , ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"'", Var01 , "' AS stat_date01, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"'", Var02 , "' AS stat_date02, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,     Var04 , "     AS stat_year, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,     Var05 , "     AS stat_month, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,     Var06 , "     AS stat_week, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"         IFNULL(haor.org_id,0) AS org_id, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"         IFNULL(habr.business_id,0) AS business_id, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"         IFNULL(habr.business_ts,1) AS business_ts, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"         IF(haor.severity >= 3,1,0) AS high_risk_flag, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"         IF(haor.repair_status = 0,1,0) AS unrepair_flag,");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"         IF(haor.source_type = 1 OR haor.source_type = 2,1,0) AS risk_usable_flag, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"         haor.cve_code AS cve_code, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"         haor.cnnvd_code AS cnnvd_code, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"         (CASE habr.cve_code  ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"       WHEN '' THEN ","'WeakName'" ," ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"           ELSE habr.name ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"         END) AS name, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"         haor.severity AS severity, ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"     COUNT(DISTINCT IF(DATEDIFF('",Var01,"', haor.vul_create_time)>=0 , haor.asset_uid,NULL)) AS total_asset_nums ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"         FROM  ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"             hhit_vul_risk_asset_org_relevance haor ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"         JOIN hhit_vul_risk_asset_business_relevance habr ON haor.vul_id = habr.vul_id ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"         GROUP BY org_id,habr.business_id,habr.business_ts,cve_code ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,     SUBSTRING_INDEX(SUBSTRING_INDEX(tmp_fieldlst,"#",FlagVal+1),"#",-1) ); 
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"     ) AS T100 ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"     LEFT JOIN v_hhit_business hb ON hb.id = T100.business_id AND hb.ts = T100.business_ts ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"     LEFT JOIN v_hhit_sbsys_org_base org ON org.id = T100.org_id; ");
      SET tmp_exec_statement=CONCAT(tmp_exec_statement,"  ");

      SET @tmp_exec_statement=tmp_exec_statement;
      PREPARE stmt FROM @tmp_exec_statement;
                        EXECUTE stmt;

      SET FlagVal=FlagVal+1;

      UPDATE statistics_hhit_vul_host SET name = "应用弱口令" WHERE name = 'WeakName' AND stat_date01 = Var01;
    END WHILE;

    SET endtime=UNIX_TIMESTAMP();
    UPDATE hhit_stat_process_log SET end_time=endtime,spend_time=endtime-starttime02 WHERE start_time=starttime02 AND stat_type=LogStatType;
    SET querydate = DATE_ADD(Var01,INTERVAL 1 day);
    END WHILE;
    
    REPLACE INTO `hhit_stat_last_time_record`(`stat_type`,`end_time`) VALUES(LogStatType,UNIX_TIMESTAMP());
END