BEGIN

SET @b_year:=(SELECT yearprocess FROM hdc.pk_byear LIMIT 1);

set @bdate=concat(@b_year-1,'-10-01'); 
set @edate=concat(@b_year,'-09-30');

### QOF ปีปัจจุบัน  ####
SET @start_d:='20190401';
SET @end_d:='20200331';

### QOF ปีที่แล้ว  ####
SET @start_d2:='20180401';
SET @end_d2:='20190331';

### QOF 2 ปี  ####
SET @start_d3:='20180401';
SET @end_d3:='20200331';


######################################################################

DROP TABLE IF EXISTS t_depress_qof;

CREATE TABLE IF NOT EXISTS `t_depress_qof` (
  `b_year` varchar(4) NOT NULL DEFAULT '',
  `HOSPCODE` varchar(5) NOT NULL DEFAULT '',
  `CID` varchar(13) NOT NULL DEFAULT '',
  `PID` varchar(15) NOT NULL DEFAULT '',
  `HID` varchar(14) DEFAULT NULL,
  `PRENAME` varchar(3) NOT NULL DEFAULT '',
  `NAME` varchar(50) NOT NULL DEFAULT '',
  `LNAME` varchar(50) NOT NULL DEFAULT '',
  `HN` varchar(15) DEFAULT NULL,
  `SEX` varchar(1) NOT NULL DEFAULT '',
  `BIRTH` date NOT NULL DEFAULT '0000-00-00',
  `MSTATUS` char(1) DEFAULT NULL,
  `OCCUPATION_OLD` varchar(3) DEFAULT NULL,
  `OCCUPATION_NEW` varchar(4) DEFAULT NULL,
  `RACE` varchar(3) DEFAULT NULL,
  `NATION` varchar(3) NOT NULL DEFAULT '',
  `RELIGION` varchar(2) DEFAULT NULL,
  `EDUCATION` varchar(2) DEFAULT NULL,
  `FSTATUS` varchar(1) DEFAULT NULL,
  `FATHER` varchar(13) DEFAULT NULL,
  `MOTHER` varchar(13) DEFAULT NULL,
  `COUPLE` varchar(13) DEFAULT NULL,
  `VSTATUS` varchar(1) DEFAULT NULL,
  `MOVEIN` date DEFAULT NULL,
  `DISCHARGE` varchar(1) DEFAULT NULL,
  `DDISCHARGE` date DEFAULT NULL,
  `ABOGROUP` varchar(1) DEFAULT NULL,
  `RHGROUP` varchar(1) DEFAULT NULL,
  `LABOR` varchar(2) DEFAULT NULL,
  `PASSPORT` varchar(8) DEFAULT NULL,
  `TYPEAREA` varchar(1) NOT NULL DEFAULT '',
  `D_UPDATE` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `check_hosp` varchar(5) NOT NULL DEFAULT '',
  `check_typearea` varchar(1) NOT NULL DEFAULT '',
  `vhid` varchar(8) DEFAULT NULL,
  `check_vhid` varchar(8) DEFAULT NULL,
  `maininscl` varchar(5) DEFAULT NULL,
  `inscl` varchar(5) DEFAULT NULL,
  `age_y` int(3) DEFAULT NULL,	
  `addr` varchar(200) DEFAULT NULL,
  `home` DECIMAL(1,0) NOT NULL DEFAULT 0,
  `TELEPHONE` varchar(15) DEFAULT NULL,
  `MOBILE` varchar(15) DEFAULT NULL,

	`date_dx` date DEFAULT NULL,
	`code_dx` varchar(7) DEFAULT NULL,
	`hosp_dx` varchar(5) DEFAULT NULL,

	`date_hhc` date DEFAULT NULL,
	`code_hhc` varchar(7) DEFAULT NULL,
	`hosp_hhc` varchar(5) DEFAULT NULL,

	`date_9q` date DEFAULT NULL,
	`code_9q` varchar(7) DEFAULT NULL,
	`hosp_9q` varchar(5) DEFAULT NULL,

	`date_8q` date DEFAULT NULL,
	`code_8q` varchar(7) DEFAULT NULL,
	`hosp_8q` varchar(5) DEFAULT NULL,


  PRIMARY KEY (`CID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


DELETE FROM `t_depress_qof`;



INSERT INTO t_depress_qof
SELECT
		2019,
		p.HOSPCODE,
		p.CID,
		p.PID,
		p.HID,
		p.PRENAME,
		p.NAME,
		p.LNAME,
		p.HN,
		p.SEX,
		p.BIRTH,
		p.MSTATUS,
		p.OCCUPATION_OLD,
		p.OCCUPATION_NEW,
		p.RACE,
		p.NATION,
		p.RELIGION,
		p.EDUCATION,
		p.FSTATUS,
		p.FATHER,
		p.MOTHER,
		p.COUPLE,
		p.VSTATUS,
		p.MOVEIN,
		p.DISCHARGE,
		p.DDISCHARGE,
		p.ABOGROUP,
		p.RHGROUP,
		p.LABOR,
		p.PASSPORT,
		p.TYPEAREA,
		p.D_UPDATE,
		p.check_hosp,
		p.check_typearea,
		p.vhid,
		p.check_vhid,
		p.maininscl,
		p.inscl,
		p.age_y,
		p.addr,
		p.home,
		p.TELEPHONE,
		p.MOBILE,
		null, 
		null, 
		null, 
		null, 
		null,
		null, 
		null, 
		null, 
		null, 
		null, 
		null, 
		null


FROM hdc.t_person_cid p
inner join hdc.tmp_f3x td on td.cid=p.CID 
WHERE p.check_typearea in(1,3) AND p.NATION in(99) AND p.DISCHARGE in(9) AND LENGTH(TRIM(p.CID)) = 13 and left(p.CID,1) not in('0','9','6','7')
AND p.BIRTH < '20040401'

GROUP BY p.CID;


UPDATE  t_depress_qof c INNER JOIN (
 select pe.cid,c.HOSPCODE,c.PID,c.DATE_SERV,c.COMSERVICE from community_service c 
 left join t_person_db pe on c.hospcode=pe.hospcode and c.pid=pe.pid 
 where date_serv BETWEEN @start_d AND @end_d and COMSERVICE like '1A02%' 
) d ON c.cid=d.cid 
SET c.date_hhc = d.date_serv,c.code_hhc = d.COMSERVICE,c.hosp_hhc = d.HOSPCODE;

UPDATE  t_depress_qof c INNER JOIN (
 select pe.cid,c.HOSPCODE,c.PID,c.DATE_SERV,c.DIAGCODE from diagnosis_opd c 
 left join t_person_db pe on c.hospcode=pe.hospcode and c.pid=pe.pid 
 where date_serv BETWEEN @start_d AND @end_d and 
 SUBSTR(DIAGCODE,1,3) IN('F32','F33','F38','F39') OR SUBSTR(DIAGCODE,1,4) IN('F341')
) d ON c.cid=d.cid 
SET c.date_dx = d.date_serv,c.code_dx = d.DIAGCODE,c.hosp_dx = d.HOSPCODE;

UPDATE   t_depress_qof c INNER JOIN (
 select pe.cid,c.HOSPCODE,c.PID,c.DATE_SERV,c.PPSPECIAL from specialpp c 
 left join t_person_db pe on c.hospcode=pe.hospcode and c.pid=pe.pid 
 where date_serv BETWEEN @start_d AND @end_d and PPSPECIAL in ('1B0282','1B0283','1B0284','1B0285','1B0260','1B0261','1B0262','1B0263') 
) d ON c.cid=d.cid 
SET c.date_9q = d.date_serv,c.code_9q = d.PPSPECIAL,c.hosp_9q = d.HOSPCODE;

UPDATE   t_depress_qof c INNER JOIN (
 select pe.cid,c.HOSPCODE,c.PID,c.DATE_SERV,c.PPSPECIAL from specialpp c 
 left join t_person_db pe on c.hospcode=pe.hospcode and c.pid=pe.pid 
 where date_serv BETWEEN @start_d AND @end_d and PPSPECIAL in ('1B0270','1B0271','1B0272','1B0273') 
) d ON c.cid=d.cid 
SET c.date_8q = d.date_serv,c.code_8q = d.PPSPECIAL,c.hosp_8q = d.HOSPCODE;




#####

DROP TABLE IF EXISTS tt_qof_depress_63;
CREATE TABLE IF NOT EXISTS tt_qof_depress_63 (
  hospcode varchar(5) NOT NULL
  ,distcode varchar(15) NOT NULL 
  ,chwcode varchar(2) NOT NULL 
  ,A varchar(10) DEFAULT NULL 
  ,B VARCHAR(10) NOT NULL
  ,`D_UPDATE` datetime DEFAULT NULL
  ,PRIMARY KEY (hospcode)
  ,KEY  (distcode)
  ,KEY  (chwcode)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT IGNORE INTO tt_qof_depress_63
SELECT 
 c.hospcode,h.distcode,h.provcode as chwcode,
  sum(if(date_dx is not null or date_hhc is not null or date_9q is not null or date_8q is not null,1,0)) as A,
  count(CID) as B,
  DATE_FORMAT(NOW(),'%Y-%m-%d %T') as D_UPDATE
  FROM t_depress_qof c 
  LEFT OUTER JOIN chospital h on c.hospcode =h.hoscode  
  GROUP BY c.hospcode;



  END