SET @provis= (SELECT provincecode FROM hdc.sys_config LIMIT 1);
SET @d1='2019-04-01';
SET @d2='2020-03-31';
#set @qof_year=2561;

DROP TABLE IF EXISTS tt_qof_ht_63;
CREATE TABLE tt_qof_ht_63 (
  chwcode varchar(255) DEFAULT NULL,
  HOSPCODE varchar(255) DEFAULT NULL,
  B varchar(255) DEFAULT NULL,
  A varchar(255) DEFAULT NULL,
  C varchar(255) DEFAULT NULL,
  D_UPDATE datetime DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT IGNORE INTO tt_qof_ht_63 
(select 
@provis as chwcode,
#@qof_year as 'year',
summary.HOSPCODE,
#chospital.hosname,
summary.B,
summary.A,
round(summary.A*100/summary.B,2) as C,
DATE_FORMAT(NOW(),'%Y-%m-%d %T') as D_UPDATE
from(
	SELECT
		t_person_cid.HOSPCODE,
		count(distinct t_person_cid.CID) as B,
		count(distinct if(sc.cid is not null,sc.cid,null)) as A
	FROM
	t_person_cid

	LEFT JOIN (
		select t_dmht.cid
		from t_dmht
		where t_dmht.min_date_dx_ht<@d1  and t_dmht.type_dx in('01','03')
	) reg on t_person_cid.CID=reg.cid
	LEFT JOIN (
		select person.cid
		from person
		inner join ncdscreen on person.pid=ncdscreen.pid and person.hospcode=ncdscreen.hospcode
		where ncdscreen.date_serv between @d1 and @d2 AND (ncdscreen.SBP_1>30 OR ncdscreen.DBP_1>20)
		group by person.cid
	) sc on t_person_cid.CID=sc.cid
	WHERE t_person_cid.NATION='099'
	AND left(t_person_cid.CID,1) not in('0','9','6','7')
	AND t_person_cid.DISCHARGE=9
	AND t_person_cid.TYPEAREA in(1,3)
	AND t_person_cid.BIRTH BETWEEN subdate(@d1,INTERVAL 74 year) AND subdate(@d2,INTERVAL 35 YEAR)

	AND reg.cid is null
	GROUP BY t_person_cid.HOSPCODE
) summary
INNER JOIN chospital on summary.HOSPCODE=chospital.hoscode 
WHERE chospital.hdc_regist=1)