Query ที่ใช้ ส่งออก 43 แฟ้ม JHCIS เวอร์ชั่น 2.5.1-x.11

01->Village: SELECT v.pcucode hospcode,v.villcode vid,ifnull(ntraditional,'') ntraditional,ifnull(nmonk,'') nmonk,ifnull(nreligionleader,'') nreligionleader,ifnull(nbroadcast,'') nbroadcast,ifnull(nradio,'') nradio,ifnull(npchc,'') npchc,ifnull(nclinic,'') nclinic,ifnull(ndrugstore,'') ndrugstore,ifnull(nchildcenter,'') nchildcenter,ifnull(npschool,'') npschool,ifnull(nsschool,'') nsschool,ifnull(ntemple,'') ntemple,ifnull(nreligiousplace,'') nreligiousplace,ifnull(nmarket,'') nmarket,ifnull(nshop,'') nshop,ifnull(nfoodshop,'') nfoodshop,ifnull(nstall,'') nstall,ifnull(nraintank,'') nraintank,ifnull(nchickenfarm,'') nchickenfarm,ifnull(npigfarm,'') npigfarm,ifnull(wastewater,'') wastewater,ifnull(garbage,'') garbage,ifnull(nfactory,'') nfactory,ifnull(FORMAT(latitude,6),'') latitude,ifnull(FORMAT(longitude,6),'') longitude,ifnull(outdate,'') outdate,ifnull(numactually,'') numactually,'' risktype ,ifnull(numstateless,'') numstateless,ifnull(nexerciseclub,'') nexerciseclub,ifnull(nolderlyclub,'') nolderlyclub,ifnull(ndisableclub,'') ndisableclub,ifnull(nnumberoneclub,'') nnumberoneclub,REPLACE(REPLACE(REPLACE(IFNULL(v.dateupdate,CONCAT(CURDATE(),CURTIME())),'-',''),' ',''),':','') d_update  FROM village v  LEFT JOIN (   SELECT pcucode,villcode         ,SUM(Case WHEN businesstype IN('07','08') THEN 1 ELSE 0 END) as nclinic      ,SUM(Case WHEN businesstype ='05' THEN 1 ELSE 0 END) as ndrugstore      ,SUM(Case WHEN businesstype IN('04','40') THEN 1 ELSE 0 END) as nmarket         ,SUM(Case WHEN businesstype ='D6' THEN 1 ELSE 0 END) as nchickenfarm         ,SUM(Case WHEN businesstype ='D7' THEN 1 ELSE 0 END) as npigfarm        ,SUM(Case WHEN businesstype IN('10','11','12') THEN 1 ELSE 0 END) as nfactory        from villagebusiness GROUP BY villcode  ) AS v1 ON v.pcucode=v1.pcucode AND v.villcode=v1.villcode  LEFT JOIN (  SELECT pcucode ,villcode       ,SUM(Case WHEN circletype ='01' THEN 1 ELSE 0 END) as nexerciseclub          ,SUM(Case WHEN circletype ='05' THEN 1 ELSE 0 END) as nolderlyclub      ,SUM(Case WHEN circletype ='98' THEN 1 ELSE 0 END) as ndisableclub   ,SUM(Case WHEN circletype IN('10','11','12') THEN 1 ELSE 0 END) as nnumberoneclub   from villagecircle GROUP BY villcode  ) AS v2 ON v.pcucode=v2.pcucode AND v.villcode=v2.villcode  LEFT JOIN (    SELECT pcucode ,villcode        ,SUM(Case WHEN religion ='01' THEN 1 ELSE 0 END) as ntemple     ,SUM(Case WHEN religion <>'01' THEN 1 ELSE 0 END) as nreligiousplace         from villagetemple GROUP BY villcode  ) AS v3 ON v.pcucode=v3.pcucode AND v.villcode=v3.villcode  LEFT JOIN (   SELECT pcucode ,villcode        ,SUM(Case WHEN maxclass ='0' THEN 1 ELSE 0 END) as nchildcenter      ,SUM(Case WHEN maxclass IN('1','3') THEN 1 ELSE 0 END) as npschool      ,SUM(Case WHEN maxclass ='2' THEN 1 ELSE 0 END) as nsschool     from villageschool GROUP BY villcode  ) AS v4 ON v.pcucode=v4.pcucode AND v.villcode=v4.villcode  WHERE RIGHT(v.villcode,2)<>'00'  AND v.dateupdate between '2018-04-01' AND '2018-04-30'

02->home: SELECT pcucode hospcode,house.hcode hid,ifnull(hid,'') house_id ,case when housechar='1' then '1' when housechar='2' then '2' when housechar='3' then '4'      when housechar='4' then '3' when housechar='5' then '5' when housechar='x' then '9'      when housechar IS NULL then '8' else '8' end AS housetype ,'' roomno,'' condo ,ifnull(hno,'') house,'' soisub,'' soimain,ifnull(road,'') road,'' villaname ,SUBSTR(villcode,7,2) village,SUBSTR(villcode,5,2) tambon,SUBSTR(villcode,3,2) ampur,SUBSTR(villcode,1,2) changwat ,IFNULL(telephonehouse,'') telephone ,REPLACE(IFNULL(FORMAT(xgis,6),''),',','') latitude,REPLACE(IFNULL(FORMAT(ygis,6),''),',','') longitude,ifnull(nfam,'') nfamily ,if(area IS NULL,'1',if(area NOT IN('1','2'),'1',area)) locatype ,if(pidvola is null or trim(pidvola)='','',pidvola) vhvid ,if(house.pid is null or trim(house.pid),'',house.pid) headid ,if(toilet IS NULL OR toilet='2','9',if(toilet NOT IN('0','1','2'),'9',toilet)) toilet ,if(waterdrinkeno IS NULL OR waterdrinkeno='2','9',if(waterdrinkeno NOT IN('0','1','2'),'9',    waterdrinkeno)) water ,CASE WHEN waterdrink='11' THEN '1' WHEN waterdrink IN('21','22','23') THEN '2'       WHEN waterdrink IN('52','52') THEN '3' WHEN waterdrink IN('61','62') THEN '4'       WHEN waterdrink IN('31','32') THEN '6' WHEN waterdrink = '91' THEN '5'       ELSE '9' END watertype ,if(garbageerase IS NULL OR garbageerase NOT IN('1','2','3','4','9'),'9',garbageerase) garbage ,case WHEN housesanitation = '0' then '0' WHEN housesanitation = '1' then '1'       WHEN housesanitation = '2' then '9' else '1' END as housing ,if(houseendur IS NULL OR houseendur NOT IN('1','2','3'),'9',houseendur) durability ,if(houseclean IS NULL OR houseclean='2','9',if(houseclean NOT IN('0','1','2'),'9',houseclean)) cleanliness ,if(houseairflow IS NULL OR houseairflow='2','9',if(houseairflow NOT IN('0','1','2'),'9',houseairflow)) ventilation ,if(houselight IS NULL OR houselight='2','9',if(houselight NOT IN('0','1','2'),'9',houselight)) light ,if(waterassuage IS NULL OR waterassuage='2','9',if(waterassuage NOT IN('0','1','2'),'9',waterassuage)) watertm ,if(foodcook IS NULL OR foodcook='2','9',if(foodcook NOT IN('0','1','2'),'9',foodcook)) mfood ,if(controlinsetdisease IS NULL OR controlinsetdisease='2','9',if(controlinsetdisease NOT IN('0','1','2'),'9',controlinsetdisease)) bcontrol ,if(controlrat IS NULL OR controlrat='2','9',if(controlrat NOT IN('0','1','2'),'9',controlrat)) acontrol ,'' chemical ,'' outdate ,house.villcode villcode ,REPLACE(REPLACE(REPLACE(IFNULL(house.dateupdate,CONCAT(CURDATE(),CURTIME())),'-',''),' ',''),':','') d_update  FROM house  LEFT JOIN    (
  SELECT pcucodeperson,hcode ,COUNT(DISTINCT(ifnull(familyno,'1'))) nfam from person GROUP BY pcucodeperson,hcode       ) AS fam ON house.pcucode=fam.pcucodeperson AND house.hcode=fam.hcode  where house.dateupdate between '2018-04-01' AND '2018-04-30' AND (DATE(house.dateupdate) BETWEEN '2018-04-01' AND '2018-04-30')ORDER BY house.pcucode ASC, house.hid ASC

03->card: SELECT DISTINCT  person.pcucodeperson AS pcucode, person.pid, person.idcard AS cid, person.rightno AS insid,  IF(TRIM(person.datestart)='' OR person.datestart LIKE '0000-00-00%','',DATE_FORMAT(person.datestart,'%Y%m%d')) AS start,  IF(TRIM(person.dateexpire)='' OR person.dateexpire LIKE '0000-00-00%','',DATE_FORMAT(person.dateexpire,'%Y%m%d')) AS expire,  person.hosmain AS main, person.hossub AS sub,  IF(cright.mapright IS NOT NULL AND TRIM(cright.mapright)<>'',cright.mapright,IF(trim(person.rightcode)='01','4200','9100')) AS instype,  IF( TRIM(person.dateupdate)='' OR person.dateupdate LIKE '0000-00-00%','',DATE_FORMAT(person.dateupdate,'%Y%m%d%H%i%s') ) AS d_update ,'' instype2_old ,if(person.idcard is null or length(trim(person.idcard))<13,'',person.idcard) id FROM person  LEFT JOIN cright ON (person.rightcode=cright.rightcode) WHERE (person.rightcode IS NOT NULL AND TRIM(person.rightcode)<>'') AND person.pcucodeperson='04232' AND person.pid NOT IN (SELECT persondeath.pid FROM persondeath WHERE persondeath.pcucodeperson='04232')  AND (person.dateupdate >= '2010-01-01') AND (person.dateupdate >= '2010-01-01') AND ( (person.dateupdate BETWEEN '2018-04-01' AND '2018-04-30') ) ORDER BY person.pcucodeperson ASC, person.pid ASC

04->Women: SELECT   women.pcucodeperson AS pcucode, women.pid,IF(fptype NOT IN('1','2','3','4','5','6','7','8') OR fptype IS NULL,'9',fptype) fptype ,IF(IF(fptype NOT IN('1','2','3','4','5','6','7','8') OR fptype IS NULL,'9',fptype)='9',IF(reasonnofp IS NULL OR reasonnofp NOT IN('1','2','3'),'3',reasonnofp),'') reasonnofp,women.childalive,  IF(women.dateupdate IS NULL OR TRIM(women.dateupdate) = '' OR women.dateupdate LIKE '0000-00-00%'   ,IF(women.datesurvey IS NULL OR YEAR(women.datesurvey)='0000',DATE_FORMAT(NOW(),'%Y%m%d%H%i%s'),DATE_FORMAT(women.datesurvey,'%Y%m%d%H%i%s')),DATE_FORMAT(women.dateupdate,'%Y%m%d%H%i%s')) AS d_update  ,person.idcard as cid  ,if(totalson is null or trim(totalson)='',IF(women.childalive IS NULL OR TRIM(women.childalive)='','0',women.childalive),totalson) totalson  ,if(abortion is null or trim(abortion)='','0',abortion) abortion  ,if(stillbirth is null or trim(stillbirth)='','0',stillbirth) stillbirth  ,if(person.idcard is null or trim(person.idcard)='' or length(trim(person.idcard))<13,'',person.idcard) cid  FROM women  JOIN person ON (women.pcucodeperson = person.pcucodeperson AND women.pid = person.pid) WHERE person.marystatus IN ('2','f') AND person.sex = '2'  AND TRIM(women.pcucodeperson)<>'' AND women.pcucodeperson='04232'  AND (women.dateupdate >= '2010-01-01' OR women.datesurvey >= '2010-01-01') AND ( (date(women.dateupdate) BETWEEN '2018-04-01' AND '2018-04-30') OR (women.datesurvey BETWEEN '2018-04-01' AND '2018-04-30') ) ORDER BY women.pcucodeperson ASC, women.pid ASC

05->chronic:- SELECT DISTINCT  person.pcucodeperson AS pcucode, person.pid,IF(personchronic.datefirstdiag IS NULL OR TRIM(personchronic.datefirstdiag)='' OR personchronic.datefirstdiag LIKE '0000-00-00%','',DATE_FORMAT(personchronic.datefirstdiag,'%Y%m%d')) AS datedx ,UCase(REPLACE(cdisease.diseasecode,'.','')) AS chronic ,IF(personchronic.datedischart IS NULL OR TRIM(personchronic.datedischart)='' OR personchronic.datedischart LIKE '0000-00-00%','',DATE_FORMAT(personchronic.datedischart,'%Y%m%d')) AS datedis ,personchronic.typedischart AS typedis ,IF( personchronic.dateupdate IS NULL OR TRIM(personchronic.dateupdate)='' OR personchronic.dateupdate LIKE '0000-00-00%','',DATE_FORMAT(personchronic.dateupdate,'%Y%m%d%H%i%s') ) AS d_update  ,if(hospfirstdiag is null or trim(hospfirstdiag)='','',hospfirstdiag) hospfirstdiag  ,if(cup is null or trim(cup)='','',cup) cup ,if(person.idcard is null or length(trim(person.idcard))<13,'',person.idcard) cid  FROM personchronic  LEFT JOIN cdisease ON (personchronic.chroniccode=cdisease.diseasecode)  JOIN person ON (personchronic.pcucodeperson=person.pcucodeperson AND personchronic.pid=person.pid) WHERE typelive IN('0','1','3') AND personchronic.pcucodeperson='04232'  AND ((ucase(left(chroniccode,3)) between 'I60' AND 'I69') OR (ucase(left(chroniccode,3)) between 'I10' AND 'I15') OR (ucase(left(chroniccode,3)) = 'I64') OR (ucase(left(chroniccode,5)) = 'J44.9') OR (ucase(left(chroniccode,3)) between 'I20' AND 'I25') OR (ucase(left(chroniccode,3)) between 'E10' AND 'E14') OR (ucase(left(chroniccode,3)) between 'J45' AND 'J46') OR (ucase(left(chroniccode,3)) = 'J43') OR (ucase(left(chroniccode,3)) = 'E66') OR (ucase(left(chroniccode,3)) between 'C00' AND 'C97') OR (ucase(left(chroniccode,3)) between 'A15' AND 'A19') OR (ucase(left(chroniccode,3)) between 'B20' AND 'B24') OR (ucase(left(chroniccode,3)) = 'K70.3') OR (ucase(left(chroniccode,5)) = 'K71.7') OR (ucase(left(chroniccode,3)) = 'K74') OR (ucase(left(chroniccode,3)) = 'K73') OR (ucase(left(chroniccode,3)) = 'N18') OR (ucase(left(chroniccode,3)) between 'M15' AND 'M19') OR (ucase(left(chroniccode,3)) = 'M47') OR (ucase(left(chroniccode,3)) between 'M05' AND 'M06') ) AND (DATE(personchronic.dateupdate) >= '2010-01-01') AND ((personchronic.datefirstdiag BETWEEN '2018-04-01' AND '2018-04-30') OR (DATE(personchronic.dateupdate) BETWEEN '2018-04-01' AND '2018-04-30') ) ORDER BY person.pcucodeperson ASC, person.pid ASC

06->death: SELECT DISTINCT  person.pcucodeperson AS pcucode, person.pid, person.idcard AS cid  ,UCase(REPLACE(cdisease1.diseasecode,'.','')) AS cdeatha  ,UCase(REPLACE(cdisease2.diseasecode,'.','')) AS cdeathb  ,UCase(REPLACE(cdisease3.diseasecode,'.','')) AS cdeathc  ,UCase(REPLACE(cdisease4.diseasecode,'.','')) AS cdeathd  ,UCase(REPLACE(cdisease5.diseasecode,'.','')) AS odisease  ,UCase(REPLACE(cdisease6.diseasecode,'.','')) AS cdeath  ,IF(persondeath.deaddate IS NULL OR TRIM(persondeath.deaddate)='' OR persondeath.deaddate LIKE '0000-00-00%','',DATE_FORMAT(persondeath.deaddate,'%Y%m%d')) AS ddeath  ,IF(persondeath.deadplace IS NULL OR persondeath.deadplace <> '1','2','1') AS pdeath  ,IF(sex='1','4',IF((deliveryconcern = '1' OR deliveryconcern = '2'),deliveryconcern,'9')) as pregnancy , IF( persondeath.dateupdate IS NULL OR TRIM(persondeath.dateupdate)='' OR persondeath.dateupdate LIKE '0000-00-00%','',DATE_FORMAT(persondeath.dateupdate,'%Y%m%d%H%i%s') ) AS d_update ,IF(v.visitno IS NOT NULL,v.pcucode, IF(trim(deadplace) IS NULL OR trim(deadplace)<>'1','',IF(hospdeath IS NULL OR trim(deadplace)='','99999',hospdeath))) hospdeath ,v.visitno seq ,IFNULL(u.idcard,'') provider ,if(person.idcard is null or length(trim(person.idcard))<13,'',person.idcard) id  FROM persondeath  JOIN person ON (persondeath.pcucodeperson=person.pcucodeperson AND persondeath.pid=person.pid)  LEFT JOIN cdisease cdisease1 ON (persondeath.cdeatha=cdisease1.diseasecode)  LEFT JOIN cdisease cdisease2 ON (persondeath.cdeathb=cdisease2.diseasecode)  LEFT JOIN cdisease cdisease3 ON (persondeath.cdeathc=cdisease3.diseasecode)  LEFT JOIN cdisease cdisease4 ON (persondeath.cdeathd=cdisease4.diseasecode)  LEFT JOIN cdisease cdisease5 ON (persondeath.odisease=cdisease5.diseasecode)  LEFT JOIN cdisease cdisease6 ON (persondeath.deadcause=cdisease6.diseasecode)  left join visit v on persondeath.pcucodeperson=v.pcucodeperson and persondeath.pid=v.pid and persondeath.deaddate=v.visitdate  LEFT JOIN `user` u ON v.username = u.username  WHERE persondeath.pcucodeperson='04232'  AND (persondeath.dateupdate >= '2010-01-01') AND ( (persondeath.deaddate BETWEEN '2018-04-01' AND '2018-04-30') OR (DATE(persondeath.dateupdate) BETWEEN '2018-04-01' AND '2018-04-30') ) ORDER BY person.pcucodeperson ASC, person.pid ASC

07->service: SELECT DISTINCT  '00000' AS clinic,  trim(visit.pcucode) as pcucode, visit.pid,visit.pid as hn, visit.visitno AS seq,  IF(visit.visitdate IS NULL OR TRIM(visit.visitdate)='' OR visit.visitdate LIKE '0000-00-00%','',DATE_FORMAT(visit.visitdate,'%Y%m%d')) AS date_serv ,ifnull(visit.timestart,'') as time_serv, visit.incup AS locate,  visit.timeservice AS intime,  IF(LCase(visit.rightcode) LIKE 'wsdl%','',IF(cright.mapright IS NOT NULL AND TRIM(cright.mapright)<>'',cright.mapright,'9100')) AS instype,  visit.rightno AS insid,IF(lcase(visit.hosmain) LIKE 'wsdl%' OR visit.hosmain IS NULL,'',visit.hosmain) AS main,IF(lcase(visit.hossub) LIKE 'wsdl%' OR visit.hossub IS NULL,'',visit.hossub) hossub,if(receivepatient IS NOT NULL AND receivepatient <>'00','3','1') typein,  if(visit.receivepatient='00' or trim(visit.receivepatient)='','',visit.receivefromhos) AS refinhos, case when receivepatient in('01','05','99') then '1' when receivepatient ='04' then '2'       when receivepatient ='02' then '4' when receivepatient ='03' then '5'       else '' end AS referin ,if(symptoms is null or trim(symptoms)='','',REPLACE(REPLACE(symptoms,'\n',' '),'\r',' ')) symptoms,if(servicetype is null or servicetype not in('1','2'),'1',servicetype) service_type ,ifnull(temperature,'') temperature,IF(pressure2 IS NULL OR TRIM(pressure2)='',if(pressure is null,'',trim(left(pressure,locate('/',pressure)-1))),trim(left(pressure2,locate('/',pressure2)-1))) sbp,IF(pressure2 IS NULL OR TRIM(pressure2)='',if(pressure is null,'',trim(mid(pressure,locate('/',pressure)+1,length(pressure)-locate('/',pressure)))),trim(mid(pressure2,locate('/',pressure2)+1,length(pressure2)-locate('/',pressure2)))) dbp,ifnull(pulse,'') pr,ifnull(respri,'') rr, case when alivestatus='2' then '5' when alivestatus='3' then '4' when alivestatus='6' then '6'       when alivestatus='7' then '7' when alivestatus='8' then '8' ELSE '1' END AS typeout, if(visit.refer='00' or trim(visit.refer)='','',visit.refertohos) AS refouhos,  case when refer in('01','05','99') then '1' when refer ='04' then '2'       when refer in('02','06') then '4' when refer ='03' then '5'       else '' end AS referout,  IF(visit.money3 IS NOT NULL AND TRIM(visit.money3)<>'', REPLACE(FORMAT(visit.money3,2),',',''), FORMAT(0,2)) AS cost,  IF(visit.money1 IS NOT NULL AND TRIM(visit.money1)<>'', REPLACE(FORMAT(visit.money1,2),',',''), FORMAT(0,2)) AS price,  IF(visit.money2 IS NOT NULL AND TRIM(visit.money2)<>'', REPLACE(FORMAT(visit.money2,2),',',''), FORMAT(0,2)) AS pay ,IF(visit.moneynoclaim IS NOT NULL AND TRIM(visit.moneynoclaim)<>'', REPLACE(FORMAT(visit.moneynoclaim,2),',',''), FORMAT(0,2)) AS actualpay, IF( visit.dateupdate IS NULL OR TRIM(visit.dateupdate)='' OR visit.dateupdate LIKE '0000-00-00%',DATE_FORMAT(visit.visitdate,'%Y%m%d%H%i%s'),DATE_FORMAT(visit.dateupdate,'%Y%m%d%H%i%s') ) AS d_update ,p.idcard cid  FROM (select v.pcucodeperson,v.pcucode,v.pid,v.pid hn,v.visitno,v.visitdate,timestart,incup,timeservice             ,v.rightcode,v.rightno,IF(lcase(hosmain) LIKE 'wsdl%' OR hosmain IS NULL,'',hosmain) hosmain,IF(lcase(hossub) LIKE 'wsdl%' OR hossub IS NULL,'',hossub) hossub,receivepatient,receivefromhos,symptoms,servicetype,temperature             ,pressure,pressure2,pulse,respri,alivestatus,refertohos,refer,money3,money1,money2,moneynoclaim             ,v.dateupdate,flagservice        from visit v        LEFT JOIN (select pcucode,visitno from visitdrug d                        WHERE DATE(d.dateupdate) BETWEEN '2018-04-01' AND '2018-04-30' OR d.dateupdate IS NULL                        GROUP BY pcucode,visitno ) AS d        ON v.pcucode=d.pcucode and v.visitno=d.visitno        WHERE (v.visitdate BETWEEN '2018-04-01' AND '2018-04-30')              OR (DATE(v.dateupdate) BETWEEN '2018-04-01' AND '2018-04-30')  UNION        select v.pcucodeperson,v.pcucode,v.pid,v.pid hn,v.visitno,v.visitdate,timestart,incup,timeservice             ,v.rightcode,v.rightno,IF(lcase(hosmain) LIKE 'wsdl%' OR hosmain IS NULL,'',hosmain) hosmain,IF(lcase(hossub) LIKE 'wsdl%' OR hossub IS NULL,'',hossub) hossub,receivepatient,receivefromhos,symptoms,servicetype,temperature             ,pressure,pressure2,pulse,respri,alivestatus,refertohos,refer,money3,money1,money2,moneynoclaim             ,v.dateupdate,flagservice        from visit v        LEFT JOIN (select pcucode,visitno from visitdiag d                        WHERE DATE(d.dateupdate) BETWEEN '2018-04-01' AND '2018-04-30' OR d.dateupdate IS NULL                        GROUP BY pcucode,visitno ) AS d        ON v.pcucode=d.pcucode and v.visitno=d.visitno        WHERE (v.visitdate BETWEEN '2018-04-01' AND '2018-04-30')           OR (DATE(v.dateupdate) BETWEEN '2018-04-01' AND '2018-04-30')        ) AS visit  join person p on visit.pcucodeperson = p.pcucodeperson and visit.pid = p.pid  LEFT JOIN cright ON (visit.rightcode=cright.rightcode)  LEFT JOIN _tmpExportStd18ServicePttype ON (visit.pcucodeperson=_tmpExportStd18ServicePttype.pcucodeperson AND visit.pid=_tmpExportStd18ServicePttype.pid)  LEFT JOIN (SELECT pcucodeperson ,pid ,IF(deaddate IS NULL OR YEAR(deaddate)='0000','1915-01-01',deaddate) dd FROM persondeath pd ) pde ON visit.pcucodeperson=pde.pcucodeperson AND visit.pid=pde.pid  WHERE (visit.flagservice <>'99')  AND visit.pcucode='04232'  AND (pde.dd>=visitdate or pde.pid IS NULL)

08->newborncare:= SELECT vb.pcucodeperson hospcode,vb.pid,ifnull(vb.visitno,'') seq ,IF(vdeli.datedeliver IS NULL OR vdeli.datedeliver='0000-00-00','',REPLACE(vdeli.datedeliver,'-','')) bdate ,IF(vb.datecare IS NULL OR vb.datecare='0000-00-00','',REPLACE(vb.datecare,'-','')) bcare ,vb.pcucodeperson bcplace ,CASE WHEN babyhealth ='0' THEN '2' WHEN babyhealth ='1' THEN '1' ELSE '9' END bcareresult ,CASE WHEN food ='1' THEN '1' WHEN food ='2' THEN '2' WHEN food ='3' THEN '3'       WHEN food ='4' THEN '4' ELSE '1' END food ,IF(u.idcard IS NULL,'',u.idcard) provider ,REPLACE(REPLACE(REPLACE(IF(vb.dateupdate is null OR left(vb.dateupdate,2)='00',CONCAT(CURDATE(),CURTIME()),vb.dateupdate),'-',''),' ',''),':','') d_update  ,if(p.idcard is null or trim(p.idcard)='' or length(trim(p.idcard))<13,'',p.idcard) cid  from visitbabycare vb  LEFT JOIN visit v ON vb.pcucode=v.pcucode AND vb.visitNo=v.visitno  LEFT JOIN visitancdeliverchild vdc ON vb.pcucodeperson=vdc.pcucodechild AND vb.pid=vdc.pidchild  LEFT JOIN visitancdeliver vdeli ON vdc.pcucodeperson=vdeli.pcucodeperson AND vdc.pid=vdeli.pid AND vdc.pregno=vdeli.pregno  LEFT JOIN `user` u ON v.pcucode = u.pcucode AND v.username = u.username join person p on v.pcucodeperson=p.pcucodeperson and v.pid=p.pid  WHERE ((vb.datecare BETWEEN '2018-04-01' AND '2018-04-30')  OR (DATE(vb.dateupdate) BETWEEN '2018-04-01' AND '2018-04-30'))

09->prenatal:= SELECT vpc.pcucodeperson hospcode,vpc.pid pid,vpc.pregno gravida,IF(vpc.lmp IS NULL OR TRIM(vpc.lmp)='' OR vpc.lmp LIKE '0000-00-00%','',DATE_FORMAT(vpc.lmp,'%Y%m%d')) AS lmp,IF(vpc.edc IS NULL OR TRIM(vpc.edc)='' OR vpc.edc LIKE '0000-00-00%','',DATE_FORMAT(vpc.edc,'%Y%m%d')) AS edc,CASE WHEN vb.vdrl = '0' THEN '1' WHEN vb.vdrl IN('1','2','3') THEN '2' WHEN vb.vdrl = '9' THEN '3'       WHEN LCASE(vb.vdrl) = 'x' THEN '4' ELSE '9' END AS vdrl_result,CASE WHEN vb.hbag = '0' THEN '1' WHEN vb.hbag ='1' THEN '2' WHEN vb.hbag = '9' THEN '3'       WHEN LCASE(vb.hbag) = 'x' THEN '4' ELSE '9' END AS hb_result ,CASE WHEN vb.hiv = '0' THEN '1' WHEN vb.hiv ='1' THEN '2' WHEN vb.hiv = '9' THEN '3'       WHEN LCASE(vb.hiv) = 'x' THEN '4' ELSE '9' END AS hiv_result ,IF(lbf.datecheck IS NULL OR lbf.datecheck='0000-00-00','',REPLACE(lbf.datecheck,'-','')) date_hct ,IF(vb.hct IS NULL OR TRIM(vb.hct)='','',vb.hct) hct_result ,CASE WHEN vb.talas1 = '0' THEN '1' WHEN vb.talas1 ='1' THEN '2' WHEN vb.talas1 = '9' THEN '3'       WHEN LCASE(vb.talas1) = 'x' THEN '4' ELSE '9' END AS thalassemia ,REPLACE(REPLACE(REPLACE(IF(vpc.dateupdate is null OR left(vpc.dateupdate,2)='00',CONCAT(CURDATE(),CURTIME()),vpc.dateupdate),'-',''),' ',''),':','') d_update ,'' provider ,if(p.idcard is null or trim(p.idcard)='' or length(trim(p.idcard))<13,'',p.idcard) cid  FROM visitancpregnancy vpc   LEFT JOIN      (SELECT g.pcucodeperson ,g.pid ,pregno,MAX(vb.datecheck) datecheck        FROM visitancpregnancy g LEFT JOIN visitlabblood vb ON g.pcucodeperson=vb.pcucodeperson AND g.pid=vb.pid       WHERE (vb.datecheck  BETWEEN '2018-04-01' AND '2018-04-30' OR DATE(vb.dateupdate) BETWEEN '2018-04-01' AND '2018-04-30')         AND (DATEDIFF(vb.datecheck,lmp) < 280) AND vb.hct IS NOT  NULL AND TRIM(vb.hct)<>''       GROUP BY g.pcucodeperson ,g.pid ,pregno ORDER BY vb.datecheck DESC      ) as lbf  ON vpc.pcucodeperson=lbf.pcucodeperson AND vpc.pid=lbf.pid AND vpc.pregno=lbf.pregno  LEFT JOIN visitlabblood vb ON lbf.pcucodeperson=vb.pcucodeperson AND lbf.pid=vb.pid AND lbf.datecheck=vb.datecheck  join person p on vpc.pcucodeperson=p.pcucodeperson and vpc.pid=p.pid  WHERE (vpc.lmp BETWEEN '2018-04-01' AND '2018-04-30')     OR (DATE(vpc.dateupdate) BETWEEN '2018-04-01' AND '2018-04-30')     OR (lbf.datecheck BETWEEN '2018-04-01' AND '2018-04-30')

10->Labor:= SELECT vd.pcucodeperson hospcode,vd.pid,vd.pregno gravida ,IF(vp.lmp IS NULL OR TRIM(vp.lmp)='' OR vp.lmp LIKE '0000-00-00%','',DATE_FORMAT(vp.lmp,'%Y%m%d')) AS lmp,IF(vp.edc IS NULL OR TRIM(vp.edc)='' OR vp.edc LIKE '0000-00-00%','',DATE_FORMAT(vp.edc,'%Y%m%d')) AS edc,IF(vd.datedeliver IS NULL OR TRIM(vd.datedeliver)='' OR vd.datedeliver LIKE '0000-00-00%','',DATE_FORMAT(vd.datedeliver,'%Y%m%d')) AS bdate,IF(deliverend IS NULL OR TRIM(deliverend) ='','O800',REPLACE(deliverend,'.','')) bresult ,IF(vd.deliverplace IS NULL OR vd.deliverplace NOT IN('1','2','3','4'),'5',vd.deliverplace) bplace ,IF(hosservice IS NULL OR TRIM(hosservice)='','00000',hosservice) bhosp ,IF(vd.delivertype IS NULL OR delivertype NOT IN('1','2','3','4','5','6'),if(deliverresult='ท','6','1'),vd.delivertype) btype ,IF(vd.operater IS NULL OR operater NOT IN('1','2','3','4','5'),'1',vd.operater) bdoctor ,IFNULL(aliv.al,'0') lborn ,IF(vd.numdeadinpreg IS NULL OR TRIM(vd.numdeadinpreg)='','0',vd.numdeadinpreg) sborn ,REPLACE(REPLACE(REPLACE(IF(vd.dateupdate IS NULL OR LEFT(vd.dateupdate,2)='00',CONCAT(CURDATE(),CURTIME()),vd.dateupdate),'-',''),' ',''),':','') d_update  ,if(p.idcard is null or trim(p.idcard)='' or length(trim(p.idcard))<13,'',p.idcard) cid  FROM visitancdeliver vd  JOIN visitancpregnancy vp ON vd.pcucodeperson=vp.pcucodeperson AND vd.pid=vp.pid AND vd.pregno=vp.pregno  LEFT JOIN  (SELECT DISTINCT pcucodeperson,pid,pregno,COUNT(*) al   FROM visitancdeliverchild  GROUP BY pcucodeperson,pid,pregno) AS aliv   ON vd.pcucodeperson=aliv.pcucodeperson AND vd.pid=aliv.pid AND vd.pregno=aliv.pregno join person p on vd.pcucodeperson=p.pcucodeperson and vd.pid=p.pid  WHERE ((vd.datedeliver BETWEEN '2018-04-01' AND '2018-04-30') OR (DATE(vd.dateupdate) BETWEEN '2018-04-01' AND '2018-04-30'))

11->postnatal:= SELECT vc.pcucodeperson hospcode,vc.pid ,vc.visitno seq,vc.pregno gravida ,vd.datedeliver bdate ,vc.datecare ppcare ,IF(vc.hosservice IS NULL OR TRIM(vc.hosservice)='',vc.pcucodeperson,vc.hosservice) ppplace ,CASE WHEN resultcheck ='0' THEN '2' WHEN resultcheck ='1' THEN '1' ELSE '9' END ppresult ,IFNULL(u.idcard,'') provider ,REPLACE(REPLACE(REPLACE(IF(vc.dateupdate IS NULL OR LEFT(vc.dateupdate,2)='00',IF(v.dateupdate IS NULL OR TRIM(v.dateupdate)='' OR v.dateupdate LIKE '0000-00-00%',DATE_FORMAT(v.visitdate,'%Y%m%d%H%i%s'),DATE_FORMAT(v.dateupdate,'%Y%m%d%H%i%s')) ,vc.dateupdate),'-',''),' ',''),':','') d_update  ,if(p.idcard is null or trim(p.idcard)='' or length(trim(p.idcard))<13,'',p.idcard) cid  from visitancmothercare vc  LEFT JOIN visitancdeliver vd ON vc.pcucodeperson=vd.pcucodeperson AND vc.pid=vd.pid AND vc.pregno=vd.pregno  LEFT JOIN visit v ON vc.pcucode=v.pcucode AND vc.visitno=v.visitno  LEFT JOIN `user` u ON v.username=u.username join person p on vc.pcucodeperson=p.pcucodeperson and vc.pid=p.pid  WHERE DATEDIFF(datecare,datedeliver) > 0 AND DATEDIFF(datecare,datedeliver) < 43 AND ((vc.datecare BETWEEN '2018-04-01' AND '2018-04-30')  OR (DATE(vc.dateupdate) BETWEEN '2018-04-01' AND '2018-04-30'))

12->SpecialPP:= SELECT spp.pcucodeperson hospcode,spp.pid ,IF(spp.visitno IS NULL OR TRIM(spp.visitno)='','',spp.visitno) seq ,REPLACE(dateserv,'-','') date_serv ,IF(servplace IS NULL OR servplace NOT IN('1','2'),'2',servplace) servplace ,ppspecial ,IF(ppsplace IS NULL OR TRIM(ppsplace)='',IF(spp.visitno IS NOT NULL,spp.pcucode,''),ppsplace) ppsplace ,IFNULL(u1.idcard,IFNULL(u2.idcard,'')) provider ,REPLACE(REPLACE(REPLACE(IF(spp.dateupdate IS NULL OR TRIM(spp.dateupdate)='' OR LEFT(spp.dateupdate,2)='00' ,CONCAT(CURDATE(),CURTIME()),spp.dateupdate),'-',''),' ',''),':','') d_update ,ifnull(ppresult,'') ppresult ,if(p.idcard is null or length(trim(p.idcard))<>13,'',p.idcard) cid  FROM f43specialpp spp  LEFT JOIN `visit` v ON spp.pcucode=v.pcucode AND spp.visitno=v.visitno  LEFT JOIN `user` u1 ON spp.provider=u1.username  LEFT JOIN `user` u2 ON v.username=u2.username  join person p on spp.pcucodeperson=p.pcucodeperson and spp.pid=p.pid  AND (UCase(LEFT(ppspecial,2)) ='1B')  AND ((spp.dateserv BETWEEN '2018-04-01' AND '2018-04-30')   OR  (DATE(spp.dateupdate) BETWEEN '2018-04-01' AND '2018-04-30'))

13->Accident = SELECT v.pcucode hospcode,v.pid,v.visitno seq ,concat(REPLACE(v.visitdate,'-',''), if(vda.dateupdate IS NULL,'',REPLACE(TIME(vda.dateupdate),':',''))) datetime_serv ,concat(REPLACE(vda.dateaccident,'-',''),IF(vda.timeaccident IS NULL OR vda.timeaccident='00:00:00','000000',REPLACE(vda.timeaccident,':',''))) datetime_ae ,CASE WHEN UCASE(LEFT(vd.diagcode,3)) BETWEEN 'V01' AND 'V99' THEN '01'       WHEN UCASE(LEFT(vd.diagcode,3)) BETWEEN 'W01' AND 'W19' THEN '02'       WHEN UCASE(LEFT(vd.diagcode,3)) BETWEEN 'W20' AND 'W49' THEN '03'       WHEN UCASE(LEFT(vd.diagcode,3)) BETWEEN 'W50' AND 'W64' THEN '04'       WHEN UCASE(LEFT(vd.diagcode,3)) BETWEEN 'W65' AND 'W74' THEN '05'       WHEN UCASE(LEFT(vd.diagcode,3)) BETWEEN 'W75' AND 'W84' THEN '06'       WHEN UCASE(LEFT(vd.diagcode,3)) BETWEEN 'W85' AND 'W99' THEN '07'       WHEN UCASE(LEFT(vd.diagcode,3)) BETWEEN 'X00' AND 'X09' THEN '08'       WHEN UCASE(LEFT(vd.diagcode,3)) BETWEEN 'X10' AND 'X19' THEN '09'       WHEN UCASE(LEFT(vd.diagcode,3)) BETWEEN 'X20' AND 'X29' THEN '10'       WHEN UCASE(LEFT(vd.diagcode,3)) BETWEEN 'X30' AND 'X39' THEN '11'       WHEN UCASE(LEFT(vd.diagcode,3)) BETWEEN 'X40' AND 'X49' THEN '12'       WHEN UCASE(LEFT(vd.diagcode,3)) BETWEEN 'X50' AND 'X57' THEN '13'       WHEN UCASE(LEFT(vd.diagcode,3)) BETWEEN 'X58' AND 'X59' THEN '14'       WHEN UCASE(LEFT(vd.diagcode,3)) BETWEEN 'X60' AND 'X84' THEN '15'       WHEN UCASE(LEFT(vd.diagcode,3)) BETWEEN 'X85' AND 'Y09' THEN '16'                    WHEN UCASE(LEFT(vd.diagcode,3)) BETWEEN 'Y10' AND 'Y33' THEN '17'       WHEN UCASE(LEFT(vd.diagcode,3)) BETWEEN 'X35' AND 'Y36' THEN '18'                    WHEN UCASE(LEFT(vd.diagcode,3)) = 'Y34' THEN '19'  ELSE '' END aetype ,IF(aeplace IS NULL OR aeplace NOT IN('01','02','03','04','05','06','07','08','09','10','11','98','99'),'98',aeplace) aeplace ,IF(typeinae IS NULL OR typeinae NOT IN('1','2','3','4','5','6','7','9'),'7',typeinae) typein_ae ,IF(typeinaccident IS NULL OR typeinaccident NOT IN('1','2','3','8','9'),'8',typeinaccident) traffic ,IF(vehicletype IS NULL OR vehicletype NOT IN('01','02','03','04','05','06','07','08','09','10','11','12','98','99'),'98',vehicletype) vehicle ,IF(alcohol IS NULL OR alcohol NOT IN('1','2','9'),'9',alcohol) alcohol ,IF(nacroticdrug IS NULL OR nacroticdrug NOT IN('1','2','9'),'9',nacroticdrug) nacrotic_drug ,IF(belt IS NULL OR belt NOT IN('1','2','9'),'9',belt) belt ,IF(helmet IS NULL OR helmet NOT IN('1','2','9'),'9',helmet) helmet ,IF(airway IS NULL OR airway NOT IN('1','2','3'),'3',airway) airway ,IF(stopbleed IS NULL OR stopbleed NOT IN('1','2','3'),'3',stopbleed) stopbleed ,IF(splint IS NULL OR splint NOT IN('1','2','3'),'3',splint) splint ,IF(fluid IS NULL OR fluid NOT IN('1','2','3'),'3',fluid) fluid ,IF(urgency IS NULL OR urgency NOT IN('1','2','3','4','5','6'),'6',urgency) urgency ,IF(comaeye IS NULL OR comaeye NOT IN('1','2','3','4'),'4',comaeye) coma_eye ,IF(comaspeak IS NULL OR comaspeak NOT IN('1','2','3','4','5'),'5',comaspeak) coma_speak ,IF(comamovement IS NULL OR comamovement NOT IN('1','2','3','4','5','6'),'6',comamovement) coma_movement ,REPLACE(REPLACE(REPLACE(IF(vda.dateupdate IS NULL OR TRIM(vda.dateupdate)='' OR LEFT(vda.dateupdate,2)='00'  ,CONCAT(CURDATE(),CURTIME()),vda.dateupdate),'-',''),' ',''),':','') d_update  ,if(p.idcard is null or trim(p.idcard)='' or length(trim(p.idcard))<13,'',p.idcard) cid  FROM visit v  JOIN visitdiag vd ON v.pcucode=vd.pcucode AND v.visitno=vd.visitno  JOIN visitdiagaccidentaddress vda ON vd.pcucode=vda.pcucode AND vd.visitno=vda.visitno AND vd.diagcode=vda.diagcode  join person p on v.pcucodeperson=p.pcucodeperson and v.pid=p.pid  WHERE (UCASE(LEFT(vd.diagcode,3)) BETWEEN 'V01' AND 'V99'   OR UCASE(LEFT(vd.diagcode,3)) BETWEEN 'W00' AND 'W99'   OR UCASE(LEFT(vd.diagcode,3)) BETWEEN 'X00' AND 'X99'   OR UCASE(LEFT(vd.diagcode,3)) BETWEEN 'Y00' AND 'Y99')  AND (v.visitdate >= '2010-01-01')  AND ((v.visitdate  BETWEEN '2018-04-01' AND '2018-04-30'))

14->Community_Service:= SELECT hh.pcucode hospcode,v.pid,hh.visitno seq ,REPLACE(v.visitdate,'-','') date_serv ,IFNULL(ch.homehealthmap,ch.homehealthcode) comservice ,IFNULL(u1.idcard,IFNULL(u2.idcard,'')) provider ,REPLACE(REPLACE(REPLACE(IF(hh.dateupdate IS NULL OR TRIM(hh.dateupdate)='' OR LEFT(hh.dateupdate,2)='00'    ,CONCAT(CURDATE(),CURTIME()),hh.dateupdate),'-',''),' ',''),':','') d_update  ,if(p.idcard is null or trim(p.idcard)='' or length(trim(p.idcard))<13,'',p.idcard) cid  FROM visithomehealthindividual hh  JOIN `visit` v ON hh.pcucode=v.pcucode AND hh.visitno=v.visitno  JOIN `chomehealthtype` ch ON hh.homehealthtype=ch.homehealthcode  LEFT JOIN `user` u1 ON hh.pcucode = u1.pcucode AND hh.`user`=u1.username  LEFT JOIN `user` u2 ON v.pcucode = u2.pcucode AND v.username=u2.username  join person p on v.pcucodeperson=p.pcucodeperson and v.pid=p.pid  WHERE      (v.visitdate BETWEEN '2018-04-01' AND '2018-04-30')

15->Community_Act:= SELECT cac.pcucode hospcode,cac.villcode vid,if(cac.datestart is null OR TRIM(cac.datestart)='' OR LEFT(cac.datestart,2)='00','',REPLACE(cac.datestart,'-','')) date_start ,if(cac.datefinish is null OR TRIM(cac.datefinish)='' OR LEFT(cac.datefinish,2)='00','',REPLACE(cac.datefinish,'-','')) date_finish,IFNULL(cca.actmap,cca.ccommucode) comactivity ,IFNULL(u1.idcard,'') provider ,REPLACE(REPLACE(REPLACE(IF(cac.dateupdate IS NULL OR TRIM(cac.dateupdate)='' OR LEFT(cac.dateupdate,2)='00'    ,CONCAT(CURDATE(),CURTIME()),cac.dateupdate),'-',''),' ',''),':','') d_update  FROM f43communityactivity cac  JOIN ccommunityactivity cca ON cac.comactivity = cca.ccommucode  LEFT JOIN `user` u1 ON cac.pcucode = u1.pcucode AND cac.provider=u1.username WHERE (cac.datestart >= '2010-01-01') AND (cac.datestart BETWEEN '2018-04-01' AND '2018-04-30')

16->Dental:==>> SELECT v.pcucode hospcode,v.pid,v.visitno seq,REPLACE(v.visitdate,'-','') date_serv ,IF(pn.pid IS NOT NULL,'1'     ,CASE WHEN getAgeYearNum(birth,visitdate)< 4 THEN '2'           WHEN getAgeYearNum(birth,visitdate)> 3 AND getAgeYearNum(birth,visitdate)< 19 THEN '3'           WHEN getAgeYearNum(birth,visitdate)> 59 THEN '4' ELSE '5' END) denttype ,IF(v.servicetype IS NULL OR v.servicetype NOT IN('1','2'),'1',v.servicetype) servplace ,IF(toothpermanent IS NULL OR TRIM(toothpermanent)='' OR toothpermanent<=0,0,toothpermanent) pteeth ,IF(toothpermanentcorrupt IS NULL OR TRIM(toothpermanentcorrupt)='' OR toothpermanentcorrupt<=0,0,toothpermanentcorrupt) pcaries ,IF(pfilling IS NULL OR TRIM(pfilling)='' OR pfilling<=0,0,pfilling) pfilling ,IF(pextract IS NULL OR TRIM(pextract)='' OR pextract<=0,0,pextract) pextract ,IF(toothmilk IS NULL OR TRIM(toothmilk)='' OR toothmilk<=0,0,toothmilk) dteeth ,IF(toothmilkcorrupt IS NULL OR TRIM(toothmilkcorrupt)='' OR toothmilkcorrupt<=0,0,toothmilkcorrupt) dcaries ,IF(dfilling IS NULL OR TRIM(dfilling)='' OR dfilling<=0,0,dfilling) dfilling ,IF(dextract IS NULL OR TRIM(dextract)='' OR dextract<=0,0,dextract) dextract ,IF(needfluoride IS NULL OR needfluoride NOT IN('1','2'),'2',needfluoride) need_fluoride ,IF(needscaling IS NULL OR needscaling NOT IN('1','2'),'2',needscaling) need_scaling ,IF(needsealant IS NULL OR TRIM(needsealant)='' OR needsealant<=0,0,needsealant) need_sealant ,IF(pfillingneed IS NULL OR TRIM(pfillingneed)='' OR pfillingneed<=0,0,pfillingneed) need_pfilling ,IF(dfillingneed IS NULL OR TRIM(dfillingneed)='' OR dfillingneed<=0,0,dfillingneed) need_dfilling ,IF(needpextract IS NULL OR TRIM(needpextract)='' OR needpextract<=0,0,needpextract) need_pextract ,IF(needdextract IS NULL OR TRIM(needdextract)='' OR needdextract<=0,0,needdextract) need_dextract ,IF(nprosthesis IS NULL OR nprosthesis NOT IN('1','2','3','4'),'4',nprosthesis) nprosthesis ,IF(permanentperma IS NULL OR TRIM(permanentperma)='' OR permanentperma<=0,0,permanentperma) permanent_permanent ,IF(permanentprost IS NULL OR TRIM(permanentprost)='' OR permanentprost<=0,0,permanentprost) permanent_prosthesis ,IF(prosthesisprost IS NULL OR TRIM(prosthesisprost)='' OR prosthesisprost<=0,0,prosthesisprost) prosthesis_prosthesis ,CONCAT(IF(gum IS NULL OR gum NOT IN('0','1','2','3','4','5','9'),'9',gum),        IF(gum2 IS NULL OR gum2 NOT IN('0','1','2','3','4','5','9'),'9',gum2),        IF(gum3 IS NULL OR gum3 NOT IN('0','1','2','3','4','5','9'),'9',gum3),        IF(gum4 IS NULL OR gum4 NOT IN('0','1','2','3','4','5','9'),'9',gum4),        IF(gum5 IS NULL OR gum5 NOT IN('0','1','2','3','4','5','9'),'9',gum5),        IF(gum6 IS NULL OR gum6 NOT IN('0','1','2','3','4','5','9'),'9',gum6)       ) gum,if(depend IS NULL OR depend NOT IN('1','2','3','4','5','6','7','8','9'),'',depend) schooltype,'' klass ,IFNULL(u2.idcard,IFNULL(u1.idcard,'')) provider ,REPLACE(REPLACE(REPLACE(IF(d.dateupdate IS NULL OR TRIM(d.dateupdate)='' OR LEFT(d.dateupdate,2)='00'    ,IF(v.dateupdate IS NULL OR TRIM(v.dateupdate)='' OR LEFT(v.dateupdate,2)='00'    ,CONCAT(CURDATE(),CURTIME()),v.dateupdate),d.dateupdate),'-',''),' ',''),':','') d_update ,dv.idcard cid  FROM visitdentalcheck d  JOIN visit v ON d.pcucode=v.pcucode AND d.visitno=v.visitno  JOIN  (SELECT p.pcucodeperson,p.pid,if(p.idcard is null or trim(p.idcard)='','',p.idcard) idcard ,ifnull(birth,'') birth   FROM person p   JOIN visitdentalcheck k ON p.pcucodeperson = k.pcucodeperson and p.pid = k.pid  WHERE k.datedental BETWEEN '2018-04-01' and '2018-04-30' GROUP BY p.pcucodeperson,p.pid ) AS dv    ON d.pcucodeperson = dv.pcucodeperson and d.pid = dv.pid  LEFT JOIN personstudent std on dv.pcucodeperson=std.pcucodeperson and dv.pid=std.pid  LEFT JOIN villageschool vsch on std.pcucode=vsch.pcucode and std.villcode=vsch.villcode and std.schoolno=vsch.schoolno  LEFT JOIN `user` u1 ON v.pcucode = u1.pcucode AND v.username=u1.username  LEFT JOIN `user` u2 ON d.pcucode = u2.pcucode AND d.provider=u2.username   LEFT JOIN (SELECT na.pcucodeperson,na.pid,na.lmp            FROM visitancpregnancy na             JOIN visitdentalcheck d ON na.pcucodeperson=d.pcucodeperson AND na.pid=d.pid             WHERE na.lmp IS NOT NULL AND LEFT(na.lmp,2)<>'00' AND TRIM(na.lmp)<>''               AND DATEDIFF(d.datedental,na.lmp) > 0 AND DATEDIFF(d.datedental,na.lmp) < 300             GROUP BY na.pcucodeperson,na.pid,na.lmp             ) AS pn  ON d.pcucodeperson = pn.pcucodeperson AND d.pid = pn.pid  WHERE v.flagservice <> '99' AND  ( (v.visitdate BETWEEN '2018-04-01' AND '2018-04-30')  OR  (Date(d.dateupdate) BETWEEN '2018-04-01' AND '2018-04-30')  )

17->Disability := SELECT un1.hospcode ,IF(un1.disabid IS NULL OR TRIM(un1.disabid)='','',un1.disabid) disabid ,un1.pid ,un1.disabtype ,IF(pt1.disabcause IS NULL OR TRIM(pt1.disabcause) ='','',pt1.disabcause) disabcause ,IF(pt1.diagcode IS NULL OR TRIM(pt1.diagcode) ='','',UCase(REPLACE(pt1.diagcode,'.',''))) diagcode ,IF(pt1.datefound IS NULL OR TRIM(pt1.datefound)='','',REPLACE(pt1.datefound,'-','')) date_detect ,IF(pt1.datestartunable IS NULL OR TRIM(pt1.datestartunable)='','',REPLACE(pt1.datestartunable,'-','')) date_disab ,IF(pt1.dateupdate IS NULL OR LEFT(pt1.dateupdate,2) ='00'     ,''     ,REPLACE(REPLACE(REPLACE(pt1.dateupdate,'-',''),' ',''),':','')) d_update ,p.idcard cid  FROM personunable1type pt1 JOIN (  SELECT pu.pcucodeperson hospcode,pu.registerno disabid,pu.pid        ,incompletetype disabtype,typecode      FROM personunable pu    JOIN personunable1type p1 ON pu.pcucodeperson=p1.pcucodeperson AND pu.pid=p1.pid
JOIN cpersonincomplete cu ON p1.typecode=cu.incompletecode      WHERE cu.incompletetype IS NOT NULL AND TRIM(cu.incompletetype) <>''    GROUP BY pu.pcucodeperson ,pu.registerno ,pu.pid ,incompletetype   ORDER BY pid ,incompletetype ) AS un1  ON pt1.pcucodeperson=un1.hospcode AND pt1.pid=un1.pid AND pt1.typecode=un1.typecode  join person p on pt1.pcucodeperson=p.pcucodeperson and pt1.pid=p.pid  Where (pt1.datefound BETWEEN '2018-04-01' AND '2018-04-30')  OR (DATE(pt1.dateupdate) BETWEEN '2018-04-01' AND '2018-04-30')

18->Drugallergy:= SELECT pa.pcucodeperson hospcode,pa.pid ,IF(pa.daterecord IS NULL OR LEFT(pa.daterecord,2) ='00',REPLACE(CURDATE(),'-',''),REPLACE(pa.daterecord,'-','')) daterecord ,c.drugcode24 drugallergy ,IF(c.drugname IS NULL OR TRIM(c.drugname)='','',c.drugname) dname ,IF(pa.typedx IS NULL OR pa.typedx NOT IN('1','2','3','4','5'),'',pa.typedx) typedx ,IF(pa.levelalergic IS NULL OR pa.levelalergic NOT IN('1','2','3','4','5','6','7','8'),'',pa.levelalergic) alevel ,if(symptom IS NULL OR TRIM(symptom)='','',symptom) symptom ,IF(informant IS NULL OR informant NOT IN('1','2','3','4'),'1',informant) informant ,IF(informhosp IS NULL OR TRIM(informhosp)='','',informhosp) informhosp ,IF(pa.dateupdate IS NULL OR LEFT(pa.dateupdate,2) ='00'    ,'' ,REPLACE(REPLACE(REPLACE(pa.dateupdate,'-',''),' ',''),':','')) d_update ,'' provider ,if(p.idcard is null or trim(p.idcard)='' or length(trim(p.idcard))<13,'',p.idcard) cid  FROM personalergic pa  JOIN person p on pa.pcucodeperson=p.pcucodeperson AND pa.pid=p.pid  JOIN cdrug c ON pa.drugcode=c.drugcode  WHERE c.drugcode24 IS NOT NULL AND LENGTH(TRIM(c.drugcode24))=24 AND (pa.daterecord >= '2010-01-01') AND (pa.dateupdate BETWEEN '2018-04-01' AND '2018-04-30')

19->Rehabilitation:= SELECT hp.pcucodeperson hospcode,hp.pid,hp.visitno seq ,'' an ,'' date_admit ,REPLACE(hp.dateserv,'-','') date_serv ,'' date_start ,'' date_finish ,rehabcode ,IF(atdevice IS NULL OR TRIM(atdevice) ='','',atdevice) at_device ,IF(atno IS NULL OR TRIM(atno)='' OR atno <=0,'',atno) at_no ,IFNULL(chospital.hoscode,'00000') rehabplace ,IFNULL(u1.idcard,IFNULL(u2.idcard,'')) provider ,REPLACE(REPLACE(REPLACE(IF(hp.dateupdate IS NULL OR TRIM(hp.dateupdate)='' OR LEFT(hp.dateupdate,2)='00'    ,CONCAT(CURDATE(),CURTIME()),hp.dateupdate),'-',''),' ',''),':','') d_update  ,if(person.idcard is null or trim(person.idcard)='' or length(trim(person.idcard))<13,'',person.idcard) cid  FROM visithealthperformance hp  LEFT JOIN `visit` v ON hp.pcucode=v.pcucode AND hp.visitno=v.visitno  LEFT JOIN `user` u1 ON hp.`provider`=u1.username  LEFT JOIN `user` u2 ON v.username=u2.username  LEFT JOIN chospital ON hp.rehabplace=chospital.hoscode  JOIN person on person.pcucodeperson=hp.pcucodeperson and person.pid=hp.pid  WHERE  (hp.dateserv BETWEEN '2018-04-01' AND '2018-04-30')

20->Care_Refer==>> SELECT  v7c.pcucode hospcode ,numberrefer referid ,if(referidprovince IS NULL OR TRIM(referidprovince)='','',referidprovince) referid_province ,IF(caretype NOT IN('1','2','3','4','5','6'),'6',caretype) caretype,IF( v7c.dateupdate IS NULL OR TRIM(v7c.dateupdate)='' OR v7c.dateupdate LIKE '0000-00-00%',DATE_FORMAT(visit.visitdate,'%Y%m%d%H%i%s'),DATE_FORMAT(v7c.dateupdate,'%Y%m%d%H%i%s') ) AS d_update ,x.idcard as cid  FROM visit7carerefer v7c  JOIN visit ON (v7c.pcucode=visit.pcucode AND v7c.visitno=visit.visitno)  JOIN person x ON visit.pcucodeperson=x.pcucodeperson AND visit.pid=x.pid  WHERE (visit.flagservice<>'99')  AND visit.refer IS NOT NULL AND visit.refer<>'00'  AND v7c.pcucode='04232'  AND IFNULL(v7c.dateupdate,visit.dateupdate) BETWEEN '2018-04-01' AND '2018-04-30'

21->Clinical_Refer:==>> SELECT  v7cn.pcucode hospcode ,numberrefer referid ,if(referidprovince IS NULL OR TRIM(referidprovince)='','',referidprovince) referid_province ,Concat(Replace(visitdate,'-',''),Replace(clinicaltime,':','')) datetime_assess,clinicalcode ,'' clinicalname ,clinicalvalue ,IF(comment IS NULL OR TRIM(comment)='','',comment) clinicalresult,IF( v7cn.dateupdate IS NULL OR TRIM(v7cn.dateupdate)='' OR v7cn.dateupdate LIKE '0000-00-00%',DATE_FORMAT(visit.visitdate,'%Y%m%d%H%i%s'),DATE_FORMAT(v7cn.dateupdate,'%Y%m%d%H%i%s') ) AS d_update  FROM visit7clinicalrefer v7cn  JOIN visit ON (v7cn.pcucode=visit.pcucode AND v7cn.visitno=visit.visitno)  WHERE (visit.flagservice<>'99')  AND visit.refer IS NOT NULL AND visit.refer<>'00'  AND v7cn.pcucode='04232'  AND IFNULL(v7cn.dateupdate,visit.dateupdate) BETWEEN '2018-04-01' AND '2018-04-30'

22->Drugrefer:==>> SELECT DISTINCT  '00000' AS clinic,  visit.pcucode, visit.pid, visit.visitno AS seq,  IF(visit.visitdate IS NULL OR TRIM(visit.visitdate)='' OR visit.visitdate LIKE '0000-00-00%','',DATE_FORMAT(visit.visitdate,'%Y%m%d')) AS date_serv,  visitdrug.unit AS amount,  IF(visitdrug.costprice IS NOT NULL AND TRIM(visitdrug.costprice)<>'', REPLACE(FORMAT(visitdrug.costprice,2),',',''), FORMAT(0,2)) AS drugcost,  IF(visitdrug.realprice IS NOT NULL AND TRIM(visitdrug.realprice)<>'', REPLACE(FORMAT(visitdrug.realprice,2),',',''), FORMAT(0,2)) AS drugpric,  IF(cdrug.drugcode IS NULL OR cdrug.drugcode='','',cdrug.drugcode) AS did,  IF(cdrug.drugname IS NULL OR cdrug.drugname='','',cdrug.drugname) AS dname,  IF(cdrug.drugcode24 IS NOT NULL AND cdrug.drugcode24<>'', cdrug.drugcode24, IF(LENGTH(cdrug.drugcode)=19,CONCAT(cdrug.drugcode,'00000'),cdrug.drugcode)) AS didstd,  ifnull(unitsellcode,unitsell) AS unit, unitpacking as unit_packing, IF( visitdrug.dateupdate IS NULL OR TRIM(visitdrug.dateupdate)='' OR visitdrug.dateupdate LIKE '0000-00-00%',DATE_FORMAT(visit.visitdate,'%Y%m%d%H%i%s'),DATE_FORMAT(visitdrug.dateupdate,'%Y%m%d%H%i%s') ) AS d_update ,numberrefer referid ,if(referidprovince IS NULL OR TRIM(referidprovince)='','',referidprovince) referid_province ,concat(replace(visit.visitdate,'-',''),replace(ifnull(timestart,curtime()),':','')) datetime_dstart ,'' datetime_dfinish ,if(visitdrug.dose IS NULL OR TRIM(visitdrug.dose)='','-',visitdrug.dose) ddescription  FROM visitdrug  JOIN (SELECT pcucodeperson,pid,visit.pcucode,visit.visitno,visitdate,flagservice,username             ,numberrefer,referidprovince,timestart,visit.refer        from visit WHERE visit.visitdate BETWEEN '2018-04-01' AND '2018-04-30'        UNION        select pcucodeperson,pid,visit.pcucode,visit.visitno,visitdate,flagservice,username             ,numberrefer,referidprovince,timestart,visit.refer        from visit        JOIN (select visitdrug.pcucode, visitdrug.visitno from visitdrug              WHERE DATE(visitdrug.dateupdate) BETWEEN '2018-04-01' AND '2018-04-30' GROUP BY visitdrug.visitno             ) AS diu        ON visit.pcucode=diu.pcucode AND visit.visitno=diu.visitno       ) AS visit ON (visitdrug.pcucode=visit.pcucode AND visitdrug.visitno=visit.visitno)  join person x ON visit.pcucodeperson=x.pcucodeperson and visit.pid=x.pid  LEFT JOIN cdrug ON (visitdrug.drugcode=cdrug.drugcode)  left join cdrugunitsell on cdrug.unitsell = cdrugunitsell.unitsellcode  LEFT JOIN `user` u ON visitdrug.pcucode = u.pcucode AND visitdrug.doctor1 = u.username  WHERE (visit.flagservice<>'99')  AND visit.refer IS NOT NULL AND visit.refer<>'00'  AND (cdrug.drugtype='01' OR cdrug.drugtype='10')  AND TRIM(visitdrug.pcucode)<>'' AND visitdrug.pcucode='04232'  AND IFNULL(DATE(visitdrug.dateupdate),visit.visitdate) BETWEEN '2018-04-01' AND '2018-04-30'  ORDER BY visit.pcucode ASC, visit.visitdate DESC, visit.visitno DESC

23->Investig_Refer:==>> SELECT  v7cn.pcucode hospcode ,numberrefer referid ,if(referidprovince IS NULL OR TRIM(referidprovince)='','',referidprovince) referid_province ,Concat(Replace(visitdate,'-',''),Replace(investtime,':','')) datetime_invest,investcode ,'' investname ,IF(IF(investdatereport IS NULL OR investdatereport='0000-00-00','',REPLACE(investdatereport,'-',''))<>''     AND     IF(investtimereport IS NULL,'',REPLACE(investtimereport,':',''))<>'' ,Concat(REPLACE(investdatereport,'-',''),REPLACE(investtimereport,':','')),'') datetime_report ,investvalue ,IF(comment IS NULL OR TRIM(comment)='','',comment) investresult,IF( v7cn.dateupdate IS NULL OR TRIM(v7cn.dateupdate)='' OR v7cn.dateupdate LIKE '0000-00-00%',DATE_FORMAT(visit.visitdate,'%Y%m%d%H%i%s'),DATE_FORMAT(v7cn.dateupdate,'%Y%m%d%H%i%s') ) AS d_update FROM visit7investigaterefer v7cn  INNER JOIN visit ON (v7cn.pcucode=visit.pcucode AND v7cn.visitno=visit.visitno)  WHERE (visit.flagservice<>'99')  AND visit.refer IS NOT NULL AND visit.refer<>'00'  AND v7cn.pcucode='04232'  AND (visit.visitdate >= '2010-01-01')  AND ((visit.visitdate BETWEEN '2018-04-01' AND '2018-04-30')       OR (left(v7cn.dateupdate,10) between '2018-04-01' AND '2018-04-30'))

24->Procedurerefer:==>> SELECT DISTINCT  '00000' AS clinic,  visit.pcucode, visit.pid, visit.visitno AS seq,  IF(visit.visitdate IS NULL OR TRIM(visit.visitdate)='' OR visit.visitdate LIKE '0000-00-00%','',DATE_FORMAT(visit.visitdate,'%Y%m%d')) AS date_serv,  visitdrug.unit AS amount,  IF(visitdrug.costprice IS NOT NULL AND TRIM(visitdrug.costprice)<>'', REPLACE(FORMAT(visitdrug.costprice,2),',',''), FORMAT(0,2)) AS drugcost,  IF(visitdrug.realprice IS NOT NULL AND TRIM(visitdrug.realprice)<>'', REPLACE(FORMAT(visitdrug.realprice,2),',',''), FORMAT(0,2)) AS drugpric,  IF(cdrug.drugcode IS NULL OR cdrug.drugcode='','',cdrug.drugcode) AS did,  IF(cdrug.drugname IS NULL OR cdrug.drugname='','',cdrug.drugname) AS procedurename,  cdrug.drugcode AS procedcode,  ifnull(unitsellcode,unitsell) AS unit, unitpacking as unit_packing, IF( visitdrug.dateupdate IS NULL OR TRIM(visitdrug.dateupdate)='' OR visitdrug.dateupdate LIKE '0000-00-00%',DATE_FORMAT(visit.visitdate,'%Y%m%d%H%i%s'),DATE_FORMAT(visitdrug.dateupdate,'%Y%m%d%H%i%s') ) AS d_update ,numberrefer referid ,if(referidprovince IS NULL OR TRIM(referidprovince)='','',referidprovince) referid_province ,concat(replace(visit.visitdate,'-',''),replace(ifnull(timestart,curtime()),':','')) timestart ,'' timefinish ,if(cdrug.drugname IS NULL OR TRIM(cdrug.drugname)='','',cdrug.drugname) dname ,cdrug.drugcode procedcode ,if(visitdrug.dose IS NULL OR TRIM(visitdrug.dose)='','-',visitdrug.dose) pdescription ,'' procedresult ,LEFT(ifnull(`user`.idcard,IFNULL(`user`.noofoccupation,ifnull(`user`.licenseno,''))),13) as doctor1 FROM visitdrug  JOIN (SELECT pcucodeperson,pid,visit.pcucode,visit.visitno,visitdate,flagservice,username              ,numberrefer,referidprovince,timestart,visit.refer        from visit WHERE visit.visitdate BETWEEN '2018-04-01' AND '2018-04-30'        UNION        select pcucodeperson,pid,visit.pcucode,visit.visitno,visitdate,flagservice,username              ,numberrefer,referidprovince,timestart,visit.refer        from visit        JOIN (select visitdrug.pcucode, visitdrug.visitno from visitdrug              WHERE DATE(visitdrug.dateupdate) BETWEEN '2018-04-01' AND '2018-04-30' GROUP BY visitdrug.visitno             ) AS diu        ON visit.pcucode=diu.pcucode AND visit.visitno=diu.visitno       ) AS visit ON (visitdrug.pcucode=visit.pcucode AND visitdrug.visitno=visit.visitno)  JOIN  ( SELECT p.pcucodeperson,p.pid,if(p.idcard is null or trim(p.idcard)='','',p.idcard) idcard  FROM person p  JOIN (select pcucodeperson,pid from visit v  WHERE (v.visitdate BETWEEN '2018-04-01' and '2018-04-30')  OR (DATE(v.dateupdate) BETWEEN '2018-04-01' and '2018-04-30') ) as v  ON p.pcucodeperson=v.pcucodeperson AND p.pid=v.pid  GROUP BY p.pcucodeperson,p.pid ) AS x  ON visit.pcucodeperson=x.pcucodeperson and visit.pid=x.pid  LEFT JOIN cdrug ON (visitdrug.drugcode=cdrug.drugcode)  LEFT JOIN _tmpdbregister cdrugstd ON (cdrug.tcode=cdrugstd.tcode)  left join cdrugunitsell on cdrug.unitsell = cdrugunitsell.unitsellcode  LEFT JOIN `user` ON visitdrug.doctor1 = `user`.username WHERE (visit.flagservice='01' OR visit.flagservice='02' OR visit.flagservice='03' OR visit.flagservice IS NULL OR TRIM(visit.flagservice)='')  AND visit.refer IS NOT NULL AND visit.refer<>'00'  AND (cdrug.drugtype='02')  AND TRIM(visitdrug.pcucode)<>'' AND visitdrug.pcucode='04232'  AND (visit.visitdate >= '2010-01-01')  AND ((visit.visitdate BETWEEN '2018-04-01' AND '2018-04-30') ) ORDER BY visit.pcucode ASC, visit.visitdate DESC, visit.visitno DESC

25->Refer_History:==>> SELECT v.pcucode hospcode ,numberrefer referid,if(referidprovince IS NULL OR TRIM(referidprovince)='','',referidprovince) referid_province ,v.pid ,v.visitno seq ,'' an ,'' referid_origin ,'' hospcode_origin ,concat(replace(v.visitdate,'-',''),replace(ifnull(v.timestart,curtime()),':','')) datetime_serv ,'' datetime_admit ,concat(replace(v.visitdate,'-',''),replace(ifnull(v.timeend,curtime()),':','')) datetime_refer ,'00000' clinic_refer ,v.refertohos hospdestination ,IF(symptoms IS NULL OR TRIM(symptoms)='','',TRIM(LEFT(symptoms,255))) chiefcomp ,IF(vitalcheck IS NULL OR TRIM(vitalcheck)='','',TRIM(LEFT(vitalcheck,255))) physicalexam ,'' diagfirst ,IF(d1.pcucode IS NULL,'',d1.diseasename) diaglast ,IF(pstatus IS NULL OR TRIM(pstatus)='',' .ไม่ระบุฯ',pstatus) pstatus ,IF(ptype IS NULL OR ptype NOT IN('1','2','3'),'1',ptype) ptype ,IF(emergency IS NULL OR emergency NOT IN('1','2','3','4','5'),'1',emergency) emergency ,IF(ptypedis IS NULL OR ptypedis NOT IN('01','02','03','04','05','06','07','99'),'99',ptypedis) ptypedis ,case when refer in('01','05','99') then '1' when refer ='04' then '2' when refer in('02','06') then '4'       when refer ='03' then '5' else '1' end AS causeout ,IF(request IS NULL OR TRIM(request)='','',request) request ,LEFT(ifnull(`user`.idcard,IFNULL(`user`.noofoccupation,ifnull(`user`.licenseno,''))),13) as provider ,IF(vhr.dateupdatehistory IS NULL OR TRIM(vhr.dateupdatehistory)='' OR vhr.dateupdatehistory LIKE '0000-00-00%',DATE_FORMAT(v.visitdate,'%Y%m%d%H%i%s'),DATE_FORMAT(vhr.dateupdatehistory,'%Y%m%d%H%i%s') ) AS d_update FROM visit7referhisreferres vhr INNER JOIN visit v ON vhr.pcucode=v.pcucode AND vhr.visitno=v.visitno LEFT JOIN `user` on v.pcucode = `user`.pcucode AND v.username = `user`.username LEFT JOIN(SELECT v.pcucode,v.visitno,cd.diseasename FROM visit v           LEFT JOIN visitdiag vd ON v.pcucode=vd.pcucode AND v.visitno=vd.visitno            JOIN cdisease cd ON vd.diagcode=cd.diseasecode           WHERE v.refer IS NOT NULL AND TRIM(v.refer)<>'' AND v.refer<>'00' GROUP BY pcucode,visitno          ) AS d1      ON v.pcucode=d1.pcucode AND v.visitno=d1.visitno  WHERE (v.flagservice<>'99')    AND  v.refer IS NOT NULL AND v.refer<>'00' AND v.refertohos IS NOT NULL AND TRIM(v.refertohos)<>''   AND v.pcucode='04232'  AND (v.visitdate >= '2010-01-01')  AND ((v.visitdate BETWEEN '2018-04-01' AND '2018-04-30') )

26->Refer_Result:==>> SELECT v.pcucode hospcode ,referidsource referid_source,if(referidprovince IS NULL OR TRIM(referidprovince)='','',referidprovince) referid_province ,if(v.receivefromhos IS NULL or trim(v.receivefromhos)='','00000',v.receivefromhos) hosp_source,IF(referresult IS NULL OR referresult NOT IN('1','2','3','4','5'),'5',referresult) refer_result ,IFNULL(CONCAT(replace(v.visitdate,'-',''),replace(IFNULL(vrr.referreceitime,curtime()),':','')),'') datetime_in,v.pid pid_in ,'' an_in ,if(referresult='1','',IF(reason IS NULL OR TRIM(reason)='','',reason)) reason ,IF(vrr.dateupdateresult IS NULL OR TRIM(vrr.dateupdateresult)='' OR vrr.dateupdateresult LIKE '0000-00-00%',DATE_FORMAT(v.visitdate,'%Y%m%d%H%i%s'),DATE_FORMAT(vrr.dateupdateresult,'%Y%m%d%H%i%s') ) AS d_update  FROM visit v JOIN visit7referhisreferres vrr ON v.pcucode=vrr.pcucode AND v.visitno=vrr.visitno  WHERE  v.receivepatient IN('01','02','03','04','05','99')    AND (v.flagservice <>'99')    AND  referidsource IS NOT NULL AND TRIM(referidsource)<>''    AND  v.pcucode='04232'  AND ((date(vrr.dateupdateresult) BETWEEN '2018-04-01' AND '2018-04-30') )

27->Diag:==>> SELECT  '00000' AS clinic, visit.pcucode, visit.pid, visit.visitno seq ,IF(visit.visitdate IS NULL OR TRIM(visit.visitdate)='' OR visit.visitdate LIKE '0000-00-00','',DATE_FORMAT(visit.visitdate,'%Y%m%d')) AS date_serv , visitdiag.dxtype, UCase(REPLACE(visitdiag.diagcode,'.','')) AS diagcode,  IF(visitdiag.dateupdate IS NULL OR TRIM(visitdiag.dateupdate)='' OR visitdiag.dateupdate LIKE '0000-00-00%',DATE_FORMAT(visit.visitdate,'%Y%m%d%H%i%s'),DATE_FORMAT(visitdiag.dateupdate,'%Y%m%d%H%i%s')) AS d_update ,x.idcard cid ,IFNULL(u.idcard,IFNULL(doctordiag,visit.username)) provider  FROM visitdiag  LEFT JOIN `user` u ON visitdiag.pcucode = u.pcucode AND visitdiag.doctordiag = u.username  join visit ON visitdiag.pcucode=visit.pcucode AND visitdiag.visitno=visit.visitno  LEFT JOIN (SELECT pcucodeperson ,pid ,IF(deaddate IS NULL OR YEAR(deaddate)='0000','1890-01-01',deaddate) dd FROM persondeath pd ) pde ON visit.pcucodeperson=pde.pcucodeperson AND visit.pid=pde.pid  JOIN  ( SELECT p.pcucodeperson,p.pid,if(p.idcard is null or trim(p.idcard)='','',p.idcard) idcard  FROM (select pcucodeperson,pid from visit v  JOIN visitdiag d ON v.pcucode=d.pcucode and v.visitno=d.visitno  WHERE IFNULL(DATE(d.dateupdate),v.visitdate)  BETWEEN '2018-04-01' and '2018-04-30' ) as v  join person p  ON v.pcucodeperson=p.pcucodeperson AND v.pid=p.pid  GROUP BY p.pid ) AS x  ON visit.pcucodeperson=x.pcucodeperson and visit.pid=x.pid  WHERE  (pde.dd>=visitdate or pde.pid IS NULL)  AND ((visit.visitdate BETWEEN '2018-04-01' and '2018-04-30') OR (DATE(visitdiag.dateupdate) BETWEEN '2018-04-01' and '2018-04-30'))

28->drug:==>> SELECT  '00000' AS clinic,  visit.pcucode, visit.pid, visit.visitno AS seq,  IF(visit.visitdate IS NULL OR TRIM(visit.visitdate)='' OR visit.visitdate LIKE '0000-00-00','',DATE_FORMAT(visit.visitdate,'%Y%m%d')) AS date_serv,  visitdrug.unit AS amount,  IF(visitdrug.costprice IS NOT NULL AND TRIM(visitdrug.costprice)<>'', REPLACE(FORMAT(visitdrug.costprice,2),',',''), FORMAT(0,2)) AS drugcost,  IF(visitdrug.realprice IS NOT NULL AND TRIM(visitdrug.realprice)<>'', REPLACE(FORMAT(visitdrug.realprice,2),',',''), FORMAT(0,2)) AS drugpric,  IF(cdrug.drugcode IS NULL OR cdrug.drugcode='','',cdrug.drugcode) AS did,  IF(cdrug.drugname IS NULL OR cdrug.drugname='','',cdrug.drugname) AS dname,  IF(cdrug.drugcode24 IS NOT NULL AND cdrug.drugcode24<>'', cdrug.drugcode24, IF(LENGTH(cdrug.drugcode)=19,CONCAT(cdrug.drugcode,'00000'),cdrug.drugcode)) AS didstd,  ifnull(unitsellcode,unitsell) AS unit, unitpacking as unit_packing, IF(visitdrug.dateupdate IS NULL OR TRIM(visitdrug.dateupdate)='' OR visitdrug.dateupdate LIKE '0000-00-00%',DATE_FORMAT(visit.visitdate,'%Y%m%d%H%i%s'),DATE_FORMAT(visitdrug.dateupdate,'%Y%m%d%H%i%s') ) AS d_update ,x.idcard as cid ,IFNULL(u.idcard,IFNULL(visitdrug.doctor1,visit.username)) provider ,if(dose is null or trim(dose)='','',dose) dose  FROM visitdrug  LEFT JOIN `user` u ON visitdrug.pcucode = u.pcucode AND visitdrug.doctor1 = u.username  join visit ON visitdrug.pcucode=visit.pcucode AND visitdrug.visitno=visit.visitno  LEFT JOIN (SELECT pcucodeperson ,pid ,IF(deaddate IS NULL OR YEAR(deaddate)='0000','1970-01-01',deaddate) dd FROM persondeath pd ) pde ON visit.pcucodeperson=pde.pcucodeperson AND visit.pid=pde.pid  JOIN  ( SELECT p.pcucodeperson,p.pid,if(p.idcard is null or trim(p.idcard)='','',p.idcard) idcard  FROM (select DISTINCT pcucodeperson,pid from visit v  JOIN visitdrug d ON v.pcucode=d.pcucode and v.visitno=d.visitno  WHERE IFNULL(DATE(d.dateupdate),v.visitdate)  BETWEEN '2018-04-01' and '2018-04-30' ) as v  JOIN person p  ON v.pcucodeperson=p.pcucodeperson AND v.pid=p.pid ) AS x  ON visit.pcucodeperson=x.pcucodeperson and visit.pid=x.pid  JOIN cdrug ON visitdrug.drugcode=cdrug.drugcode  left join cdrugunitsell on cdrug.unitsell = cdrugunitsell.unitsellcode WHERE  cdrug.drugtype IN('01','10')  AND (pde.dd>=visitdate or pde.pid IS NULL)  AND ((visit.visitdate BETWEEN '2018-04-01' and '2018-04-30') OR (DATE(visitdrug.dateupdate) BETWEEN '2018-04-01' and '2018-04-30'))

29->procedure_opd:==>> SELECT  '00000' AS clinic,  visit.pcucode, visit.pid, visit.visitno AS seq,  IF(visit.visitdate IS NULL OR TRIM(visit.visitdate)='' OR visit.visitdate LIKE '0000-00-00','',DATE_FORMAT(visit.visitdate,'%Y%m%d')) AS date_serv,  REPLACE(cdrug.drugcode,'-','') AS proced,  IF(visitdrug.realprice IS NOT NULL AND TRIM(visitdrug.realprice)<>'', REPLACE(FORMAT(visitdrug.realprice,2),',',''), FORMAT(0,2)) AS servpric,  IF(visitdrug.dateupdate IS NULL OR TRIM(visitdrug.dateupdate)='' OR visitdrug.dateupdate LIKE '0000-00-00%',DATE_FORMAT(visit.visitdate,'%Y%m%d%H%i%s'),DATE_FORMAT(visitdrug.dateupdate,'%Y%m%d%H%i%s') ) AS d_update ,LEFT(ifnull(`user`.idcard,IFNULL(`user`.noofoccupation,ifnull(`user`.licenseno,''))),13) as doctor1 ,x.idcard as cid  FROM visitdrug  left join `user` on visitdrug.pcucode = `user`.pcucode AND doctor1 = `user`.username  join visit ON visitdrug.pcucode=visit.pcucode AND visitdrug.visitno=visit.visitno  LEFT JOIN (SELECT pcucodeperson ,pid ,IF(deaddate IS NULL OR YEAR(deaddate)='0000','1890-01-01',deaddate) dd FROM persondeath pd ) pde ON visit.pcucodeperson=pde.pcucodeperson AND visit.pid=pde.pid JOIN  ( SELECT p.pcucodeperson,p.pid,if(p.idcard is null or trim(p.idcard)='','',p.idcard) idcard  FROM (select DISTINCT pcucodeperson,pid from visit v  JOIN visitdrug d ON v.pcucode=d.pcucode and v.visitno=d.visitno  WHERE IFNULL(DATE(d.dateupdate),v.visitdate)  BETWEEN '2018-04-01' and '2018-04-30' ) as v  JOIN person p ON p.pcucodeperson=v.pcucodeperson AND p.pid=v.pid ) AS x  ON visit.pcucodeperson=x.pcucodeperson and visit.pid=x.pid  JOIN cdrug ON (visitdrug.drugcode=cdrug.drugcode)  WHERE cdrug.drugtype='02'  AND ((visit.visitdate BETWEEN '2018-04-01' and '2018-04-30') OR (DATE(visitdrug.dateupdate) BETWEEN '2018-04-01' and '2018-04-30'))   AND (pde.dd>=visitdate or pde.pid IS NULL)

30->surveillance: SELECT visitdiag.pcucode, visitdiag.visitno AS seq,  UCase(REPLACE(cdisease.diseasecode,'.','')) AS diagcode,  IF(visit.visitdate IS NULL OR TRIM(visit.visitdate)='' OR visit.visitdate LIKE '0000-00-00%','',DATE_FORMAT(visit.visitdate,'%Y%m%d')) AS date_serv,  person.pid, if(person.idcard is null or trim(person.idcard)='' or length(trim(person.idcard))<13,'',person.idcard) cid,  cdisease.code506,  visitdiag506address.hno, visitdiag506address.mu, visitdiag506address.road, visitdiag506address.subdistcode,if(visitdiag506address.distcode='99','01',visitdiag506address.distcode) distcode , visitdiag506address.provcode,  visitdiag506address.area,  IF(visitdiag506address.sickdatestart IS NULL OR TRIM(visitdiag506address.sickdatestart)='' OR visitdiag506address.sickdatestart LIKE '0000-00-00%',DATE_FORMAT(visit.visitdate,'%Y%m%d'),DATE_FORMAT(visitdiag506address.sickdatestart,'%Y%m%d')) AS sickdatestart,  IF(visitdiag506address.sickdatefind IS NULL OR TRIM(visitdiag506address.sickdatefind)='' OR visitdiag506address.sickdatefind LIKE '0000-00-00%',DATE_FORMAT(visit.visitdate,'%Y%m%d'),DATE_FORMAT(visitdiag506address.sickdatefind,'%Y%m%d')) AS sickdatefind,  visitdiag506address.status AS ptstat,  IF(visitdiag506address.deaddate IS NULL OR TRIM(visitdiag506address.deaddate)='' OR visitdiag506address.deaddate LIKE '0000-00-00%','',DATE_FORMAT(visitdiag506address.deaddate,'%Y%m%d')) AS date_death,  visitdiag506address.flag18fileexpo,  IF(visitdiag.dateupdate IS NULL OR TRIM(visitdiag.dateupdate)='' OR visitdiag.dateupdate LIKE '0000-00-00%',DATE_FORMAT(visit.visitdate,'%Y%m%d%H%i%s'),DATE_FORMAT(visitdiag.dateupdate,'%Y%m%d%H%i%s') ) AS d_update ,'' an ,'' date_admit ,ifnull(syndrome,'') syndrome,ifnull(complicacation,'') complicacation,ifnull(organism,'') organism,ifnull(latitude,'') latitude ,ifnull(longitude,'') longitude ,IFNULL(u.idcard,IFNULL(visitdiag.doctordiag,visit.username)) provider  FROM visitdiag  LEFT JOIN visit ON (visitdiag.pcucode=visit.pcucode AND visitdiag.visitno=visit.visitno)  LEFT JOIN person ON (visit.pcucodeperson=person.pcucodeperson AND visit.pid=person.pid)  LEFT JOIN cdisease ON (visitdiag.diagcode=cdisease.diseasecode)  LEFT JOIN visitdiag506address ON (visitdiag.pcucode=visitdiag506address.pcucode AND visitdiag.visitno=visitdiag506address.visitno AND visitdiag.diagcode=visitdiag506address.diagcode)  LEFT JOIN `user` u ON visitdiag.doctordiag = u.username WHERE (visit.flagservice='01' OR visit.flagservice='02' OR visit.flagservice='03' OR visit.flagservice IS NULL OR TRIM(visit.flagservice)='')  AND TRIM(visitdiag.pcucode)<>'' AND visitdiag.pcucode='04232'  AND visitdiag.conti='0' AND cdisease.code506<>''  AND (visit.visitdate >= '2010-01-01') AND (visit.visitdate BETWEEN '2018-04-01' AND '2018-04-30') ORDER BY visit.pcucode ASC, visit.visitdate DESC, visit.visitno DESC

31->anc = SELECT  visitanc.pcucodeperson AS pcucode, visitanc.pid, visitanc.visitno AS seq ,IF(visitanc.datecheck IS NULL OR TRIM(visitanc.datecheck)='' OR visitanc.datecheck LIKE '0000-00-00%','',DATE_FORMAT(visitanc.datecheck,'%Y%m%d')) AS date_serv,visitanc.pregno, visitanc.sugar, visitanc.albumin, visitanc.breastcheck, visitanc.headache, visitanc.sickening,visitanc.tyroid, visitanc.dancebaby, visitanc.utmugo, visitanc.edima, visitanc.utblood, visitanc.cramp,visitanc.urinary, visitanc.heartattach, visitanc.fundus, visitanc.positionbaby, visitanc.pilot, visitanc.heartbaby,(CASE WHEN IF(datecheck IS NOT NULL AND lmp IS NOT NULL,DATEDIFF(datecheck,lmp)/7,0) <= 12        AND IF(datecheck IS NOT NULL AND lmp IS NOT NULL,DATEDIFF(datecheck,lmp)/7,0) <> 0 THEN '1'                  WHEN IF(datecheck IS NOT NULL AND lmp IS NOT NULL,DATEDIFF(datecheck,lmp)/7,0) >= 16        AND IF(datecheck IS NOT NULL AND lmp IS NOT NULL,DATEDIFF(datecheck,lmp)/7,0) <= 20 THEN '2'                     WHEN IF(datecheck IS NOT NULL AND lmp IS NOT NULL,DATEDIFF(datecheck,lmp)/7,0) >= 24        AND IF(datecheck IS NOT NULL AND lmp IS NOT NULL,DATEDIFF(datecheck,lmp)/7,0) <= 28 THEN '3'                     WHEN IF(datecheck IS NOT NULL AND lmp IS NOT NULL,DATEDIFF(datecheck,lmp)/7,0) >= 30        AND IF(datecheck IS NOT NULL AND lmp IS NOT NULL,DATEDIFF(datecheck,lmp)/7,0) <= 34 THEN '4'                     WHEN IF(datecheck IS NOT NULL AND lmp IS NOT NULL,DATEDIFF(datecheck,lmp)/7,0) >= 36        AND IF(datecheck IS NOT NULL AND lmp IS NOT NULL,DATEDIFF(datecheck,lmp)/7,0) <= 40 THEN '5'                     ELSE ''  END) as ancno,IF((visitanc.hosservice IS NULL OR visitanc.hosservice=''),ifnull(visitanc.pcucode,'00000'),visitanc.hosservice) AS aplace ,CASE WHEN (DATEDIFF(datecheck,lmp)/7)<=12 THEN FLOOR(DATEDIFF(datecheck,lmp)/7)       WHEN (DATEDIFF(datecheck,lmp)/7)>12 AND (DATEDIFF(datecheck,lmp)/7)<16       THEN (CASE WHEN (DATEDIFF(datecheck,lmp)/7)<13 THEN 13                  WHEN (DATEDIFF(datecheck,lmp)/7)>=15 THEN 15                  ELSE 14 END)      WHEN (DATEDIFF(datecheck,lmp)/7)>=16 AND (DATEDIFF(datecheck,lmp)/7)<=20       THEN FLOOR(DATEDIFF(datecheck,lmp)/7)      WHEN (DATEDIFF(datecheck,lmp)/7)>20 AND (DATEDIFF(datecheck,lmp)/7)<24       THEN (CASE WHEN (DATEDIFF(datecheck,lmp)/7)<21 THEN 21                  WHEN (DATEDIFF(datecheck,lmp)/7)>=23 THEN 23                  ELSE 22 END)       WHEN (DATEDIFF(datecheck,lmp)/7)>=24 AND (DATEDIFF(datecheck,lmp)/7)<=28       THEN FLOOR(DATEDIFF(datecheck,lmp)/7)       WHEN (DATEDIFF(datecheck,lmp)/7)>28 AND (DATEDIFF(datecheck,lmp)/7)<30 THEN 29       WHEN (DATEDIFF(datecheck,lmp)/7)>=30 AND (DATEDIFF(datecheck,lmp)/7)<=34       THEN FLOOR(DATEDIFF(datecheck,lmp)/7)       WHEN (DATEDIFF(datecheck,lmp)/7)>34 AND (DATEDIFF(datecheck,lmp)/7)<36 THEN 35       WHEN (DATEDIFF(datecheck,lmp)/7)>=36 THEN FLOOR(DATEDIFF(datecheck,lmp)/7)  END AS pregageweek ,IF( visitanc.dateupdate IS NULL OR TRIM(visitanc.dateupdate)='' OR visitanc.dateupdate LIKE '0000-00-00%',DATE_FORMAT(visitanc.datecheck,'%Y%m%d%H%i%s'),DATE_FORMAT(visitanc.dateupdate,'%Y%m%d%H%i%s') ) AS d_update ,if(person.idcard is null or trim(person.idcard)='' or length(trim(person.idcard))<13,'',person.idcard) cid ,if(u.idcard is null or trim(u.idcard) ='','',u.idcard) provider ,IF(ancres IS NULL OR ancres NOT IN('1'),'2','1') ancres  FROM visitanc  join person on visitanc.pcucodeperson = person.pcucodeperson and visitanc.pid = person.pid  join visitancpregnancy vp on visitanc.pcucodeperson = vp.pcucodeperson and visitanc.pid = vp.pid and visitanc.pregno = vp.pregno  left join visit v on visitanc.pcucode=v.pcucode and visitanc.visitno=v.visitno  left join `user` u on v.pcucode = u.pcucode AND v.username = u.username  WHERE visitanc.pcucodeperson IS NOT NULL AND TRIM(visitanc.pcucodeperson)<>'' AND ((CASE WHEN IF(datecheck IS NOT NULL AND lmp IS NOT NULL,DATEDIFF(datecheck,lmp)/7,0) <= 12        AND IF(datecheck IS NOT NULL AND lmp IS NOT NULL,DATEDIFF(datecheck,lmp)/7,0) <> 0 THEN '1'                       WHEN IF(datecheck IS NOT NULL AND lmp IS NOT NULL,DATEDIFF(datecheck,lmp)/7,0) >= 16        AND IF(datecheck IS NOT NULL AND lmp IS NOT NULL,DATEDIFF(datecheck,lmp)/7,0) <= 20 THEN '2'             WHEN IF(datecheck IS NOT NULL AND lmp IS NOT NULL,DATEDIFF(datecheck,lmp)/7,0) >= 24        AND IF(datecheck IS NOT NULL AND lmp IS NOT NULL,DATEDIFF(datecheck,lmp)/7,0) <= 28 THEN '3'                     WHEN IF(datecheck IS NOT NULL AND lmp IS NOT NULL,DATEDIFF(datecheck,lmp)/7,0) >= 30        AND IF(datecheck IS NOT NULL AND lmp IS NOT NULL,DATEDIFF(datecheck,lmp)/7,0) <= 34 THEN '4'                     WHEN IF(datecheck IS NOT NULL AND lmp IS NOT NULL,DATEDIFF(datecheck,lmp)/7,0) >= 36        AND IF(datecheck IS NOT NULL AND lmp IS NOT NULL,DATEDIFF(datecheck,lmp)/7,0) <= 40 THEN '5'                     ELSE '0'  END)  IN('1','2','3','4','5','0','') ) AND ((visitanc.datecheck BETWEEN '2018-04-01' AND '2018-04-30'))ORDER BY visitanc.pcucodeperson ASC, visitanc.datecheck DESC, visitanc.visitno DESC

32->epi:= SELECT DISTINCT  trim(visitepi.pcucodeperson) AS pcucode, visitepi.pid, visitepi.visitno AS seq ,IF(LCase(LEFT(vaccinecode,2))='tt',IF(dateepi>'2016-09-30','101',cdrug.files18epi)    ,IF(LCase(vaccinecode) IN('dt1','dt2','dt3','dt4','dt5')      ,IF(dateepi>'2016-09-30','106',cdrug.files18epi),cdrug.files18epi)) AS vcctype ,IF(visitepi.dateepi IS NULL OR TRIM(visitepi.dateepi)='' OR visitepi.dateepi LIKE '0000-00-00%','',DATE_FORMAT(visitepi.dateepi,'%Y%m%d')) AS date_serv  ,IF(visitepi.hosservice IS NULL OR visitepi.hosservice='',ifnull(visitepi.pcucode,'00000'),trim(visitepi.hosservice)) AS vccplace  ,IF(visitepi.dateupdate IS NULL OR TRIM(visitepi.dateupdate)='' OR      visitepi.dateupdate LIKE '0000-00-00%',DATE_FORMAT(visitepi.dateepi,'%Y%m%d%H%i%s') ,DATE_FORMAT(visitepi.dateupdate,'%Y%m%d%H%i%s') ) AS d_update  ,if(person.idcard is null or trim(person.idcard)='' or length(trim(person.idcard))<13,'',person.idcard) cid  ,if(u.idcard is null or trim(u.idcard) ='','',u.idcard) provider  FROM visitepi  join person on visitepi.pcucodeperson = person.pcucodeperson and visitepi.pid = person.pid  LEFT JOIN cdrug ON (visitepi.vaccinecode=cdrug.drugcode AND cdrug.drugtype='05')  left join visit v on visitepi.pcucode=v.pcucode and visitepi.visitno=v.visitno  left join `user` u on v.pcucode = u.pcucode AND v.username = u.username  WHERE visitepi.dateepi IS NOT NULL AND TRIM(visitepi.dateepi)<>''  AND TRIM(visitepi.pcucodeperson)<>'' AND visitepi.pcucodeperson='04232'  AND ((visitepi.dateepi BETWEEN '2018-04-01' AND '2018-04-30') OR (DATE(visitepi.dateupdate) BETWEEN '2018-04-01' AND '2018-04-30')) ORDER BY visitepi.pcucodeperson ASC, visitepi.dateepi DESC, visitepi.visitno DESC

33->FP SELECT DISTINCT  trim(visitfp.pcucodeperson) AS pcucode, visitfp.pid, visitfp.visitno,  visitfp.fpcode AS did,  IF(cdrug.drugcode24 IS NOT NULL AND cdrug.drugcode24<>'', cdrug.drugcode24, IF(LENGTH(cdrug.drugcode)=19,CONCAT(cdrug.drugcode,'00000'),cdrug.drugcode)) AS didstd,  visitfp.unit,  IF(visitfp.datefp IS NULL OR TRIM(visitfp.datefp)='' OR visitfp.datefp LIKE '0000-00-00%','',DATE_FORMAT(visitfp.datefp,'%Y%m%d')) AS date_serv,  IF(visitfp.hosservice IS NULL OR visitfp.hosservice='' OR visitfp.hosservice IS NULL,trim(visitfp.pcucode),trim(visitfp.hosservice)) AS fpplace,  cdrug.drugtypesub,  IF( visitfp.dateupdate IS NULL OR TRIM(visitfp.dateupdate)='' OR visitfp.dateupdate LIKE '0000-00-00%',DATE_FORMAT(visitfp.datefp,'%Y%m%d%H%i%s'),DATE_FORMAT(visitfp.dateupdate,'%Y%m%d%H%i%s') ) AS d_update  ,if(person.idcard is null or trim(person.idcard)='' or length(trim(person.idcard))<13,'',person.idcard) cid  ,if(u.idcard is null,ifnull(v.username,''),u.idcard) provider FROM visitfp  join person on visitfp.pcucodeperson = person.pcucodeperson and visitfp.pid = person.pid  LEFT JOIN cdrug ON (visitfp.fpcode=cdrug.drugcode)  left join visit v on visitfp.pcucode=v.pcucode and visitfp.visitno=v.visitno  left join `user` u on v.pcucode = u.pcucode AND v.username = u.username  WHERE visitfp.datefp IS NOT NULL AND TRIM(visitfp.datefp)<>''      AND fpcode <> 'fp0000'     AND TRIM(visitfp.pcucodeperson)<>'' AND visitfp.pcucodeperson='04232'      AND  getAgeYearNum(birth,datefp) > 8      AND (getAgeYearNum(birth,datefp) < 60  OR (getAgeYearNum(birth,datefp)=60 AND getAgeMonthNum(birth,datefp)=0 AND getAgeDayNum(birth,datefp)=0)) AND (visitfp.datefp BETWEEN '2018-04-01' AND '2018-04-30') ORDER BY visitfp.pcucodeperson ASC, visitfp.datefp DESC, visitfp.visitno DESC

34->Nutri: SELECT vnu.pcucode,nu.pid,nu.vn as seq ,IF(nu.nLastDate IS NULL OR TRIM(nu.nLastDate)='' OR nu.nLastDate LIKE '0000-00-00%','',DATE_FORMAT(nu.nLastDate,'%Y%m%d')) AS date_serv ,(getAgeYearNum(p.birth,nLastDate)*12)+getAgeMonthNum(p.birth,nLastDate) AS agemonth ,nu.weight AS weightkg,nu.tall AS heightcm,nlevel,IF(vnu.dateupdate IS NULL OR TRIM(vnu.dateupdate)='' OR vnu.dateupdate LIKE '0000-00-00%',DATE_FORMAT(vs.visitdate,'%Y%m%d%H%i%s'),DATE_FORMAT(vnu.dateupdate,'%Y%m%d%H%i%s') ) AS d_update ,p.idcard AS cid,p.sex,IF(nu.headcycle IS NULL OR nu.headcycle='','',FORMAT(nu.headcycle,0)) headcycle,IF(nu.growdevelop IS NULL OR nu.growdevelop='','',IF(nu.growdevelop IN('1','2'),nu.growdevelop,'3')) AS growdevelop ,IF(nu.food IS NULL OR nu.food='','0',nu.food) AS food ,IF(nu.bottle IS NULL OR nu.bottle NOT IN('1','2'),'2',nu.bottle) AS bottle ,if(u.idcard is null or trim(u.idcard) ='','',u.idcard) provider  FROM person p  JOIN  (SELECT nutri.pcucodeperson,nutri.pid,nutri.pcucode,nutri.visitno as vn ,max(mvd) AS nLastDate ,nutri.weight,tall ,ifnull(nutri.headcycle,'') headcycle,IF(growdevelop IS NULL OR TRIM(growdevelop)='','',IF(growdevelop IN('1','2'),growdevelop,'3')) growdevelop ,IF(food IS NULL OR food NOT IN('0','1','2','3','4'),'',food) food ,IF(bottle IS NULL OR bottle NOT IN('1','2'),'',bottle) bottle  FROM (SELECT pcucodeperson,pid,v.visitdate mvd,v.pcucode,v.visitno,vnu.weight ,tall ,vnu.headcycle,ifnull(growdevelop,'1') growdevelop ,ifnull(food,'0') food ,ifnull(bottle,'') bottle                 from visit v            join visitnutrition vnu on v.pcucode = vnu.pcucode and v.visitno = vnu.visitno                  WHERE vnu.weight is not NULL AND vnu.tall IS NOT NULL  AND ((visitdate BETWEEN '2018-04-01' AND '2018-04-30')      OR (DATE(vnu.dateupdate) BETWEEN '2018-04-01' AND '2018-04-30'))                  order by pid,v.visitdate DESC,v.visitno DESC              ) as nutri  GROUP BY pcucodeperson,pid ORDER BY pid     )as nu ON p.pcucodeperson=nu.pcucodeperson and p.pid=nu.pid  JOIN visitnutrition vnu ON nu.pcucode=vnu.pcucode AND nu.vn=vnu.visitno  JOIN visit vs ON vnu.pcucode=vs.pcucode AND vnu.visitno=vs.visitno  left join `user` u on vs.pcucode = u.pcucode AND vs.username = u.username  WHERE p.nation IN('94','95','99','094','095','099') AND       (vs.flagservice='01' OR vs.flagservice='02' OR vs.flagservice='03' OR vs.flagservice IS NULL OR TRIM(vs.flagservice)='')    AND TRIM(vnu.pcucode)<>'' AND vnu.pcucode='04232'    AND ((vnu.weight IS NOT NULL AND vnu.weight>0) AND (vnu.tall IS NOT NULL AND vnu.tall>0))

35->NCDScreen: SELECT trim(ns.pcucode) as pcucode,ns.pid,ns.visitno as visitno,IF(ns.screen_date IS NULL OR TRIM(ns.screen_date)='' OR ns.screen_date LIKE '0000-00-00%','',DATE_FORMAT(ns.screen_date,'%Y%m%d')) AS screen_date,ns.servplace,ns.smoke,ns.alcohol ,if(screen_q1<>'1','2','1') as screen_q1,ns.htfamily as htfamily ,FORMAT(ns.weight,1) weight ,FORMAT(ns.height,0) height,ROUND(ns.waist) as waist,ns.hbp_s1 as hbp_s1,ns.hbp_d1 as hbp_d1,ifnull(ns.hbp_s2,ns.hbp_s1) as hbp_s2,ifnull(ns.hbp_d2,ns.hbp_d1) as hbp_d2,IF(ns.bsl IS NULL OR ns.bsl<1,'',ns.bsl) bsl ,IF(ns.bstest IS NULL OR ns.bstest NOT IN('1','2','3','4') OR ns.bsl<1,'',ns.bstest) as bstest ,ns.pcucode pc ,IF(ns.dateupdate IS NULL OR TRIM(ns.dateupdate)='' OR ns.dateupdate LIKE '0000-00-00%',           DATE_FORMAT(ns.screen_date,'%Y%m%d%H%i%s'),           DATE_FORMAT(ns.dateupdate,'%Y%m%d%H%i%s') ) AS dateupdate ,person.idcard AS cid ,IFNULL(u.idcard,'') provider  FROM ncd_person_ncd_screen ns  LEFT JOIN (SELECT pcucodeperson ,pid ,IF(deaddate IS NULL OR YEAR(deaddate)='0000','1890-01-01',deaddate) dd  FROM persondeath pd ) pde ON ns.pcucode=pde.pcucodeperson AND ns.pid=pde.pid  LEFT JOIN `user` u ON ns.pcucode = u.pcucode AND ns.user_update=u.username join person on ns.pcucode = person.pcucodeperson AND ns.pid = person.pid  AND  (ns.screen_date IS not NULL AND TRIM(ns.screen_date)<>'' AND ns.screen_date not LIKE '0000-00-00%') AND getAgeYearNum(person.birth,ns.screen_date) >  14  AND (pde.dd>=screen_date or pde.pid IS NULL)  AND (ns.screen_date >= '2010-01-01')  AND ((ns.screen_date BETWEEN '2018-04-01' AND '2018-04-30') )ORDER BY ns.pcucode ASC, ns.screen_date DESC, ns.visitno DESC

36->ChronicFU: SELECT DISTINCT visit.pcucodeperson,visit.pid,visit.seq visitno           ,date_serv AS visitdate,visit.weight,visit.height,ROUND(visit.waist) as waist ,pup AS sbp, plo AS dbp ,IF(visit.examfoot is NULL or (TRIM(visit.examfoot)=''),'2',IF(visit.examfoot in('0','1'),'1','3')) AS examfoot , CASE WHEN visit.eyeopthalmoscope ='1' THEN 1 WHEN visit.eyeopthalmoscope ='2' THEN 2  WHEN visit.eyeopthalmoscope ='3' THEN 3 WHEN visit.eyeopthalmoscope ='4' THEN 4  WHEN visit.eyeopthalmoscope ='9' THEN 8 ELSE 9 END  AS eyeopthalmoscope ,IF(visit.dateupdate IS NULL OR TRIM(visit.dateupdate)='' OR visit.dateupdate LIKE '0000-00-00%',          concat(REPLACE(datefollow,'-',''),REPLACE(TIME(NOW()),':','')), DATE_FORMAT(visit.dateupdate,'%Y%m%d%H%i%s') ) AS dateupdate ,IFNULL(u.idcard,'') provider, hosserv, visit.cid  FROM       (select p.pcucodeperson, p.pid,diu.pcucode hosserv, seq, visit.username,datefollow                ,IFNULL(visit.weight,IFNULL(diu.weight,'')) weight               ,IFNULL(visit.height,IFNULL(diu.height,'')) height               ,IFNULL(visit.waist,IFNULL(diu.waistline,'')) waist ,if(pressure IS NULL OR TRIM(pressure)='' OR locate('/',pressure)<2,bps,left(pressure,locate('/',pressure)-1)) as pup ,if(pressure IS NULL OR TRIM(pressure)='' OR locate('/',pressure)<2,bpd,mid(pressure,locate('/',pressure)+1,length(pressure)-locate('/',pressure))) as plo                ,IF(datefollow IS NULL OR TRIM(datefollow)='' OR datefollow LIKE '0000-00-00%','',                    DATE_FORMAT(datefollow,'%Y%m%d')) AS date_serv                ,examfoot, eyeopthalmoscope                ,diu.dateupdate                ,if(p.idcard is null or trim(p.idcard)='' or length(trim(p.idcard))<13,'',p.idcard) cid        FROM (select pcucode, seq, examfoot, eyeopthalmoscope, dateupdate, pcucodeperson, pid, datefollow                          ,weight, height, waistline, bps, bpd                  FROM ncdservice  WHERE (DATE(ncdservice.dateupdate) BETWEEN '2018-04-01' AND '2018-04-30'           OR ncdservice.datefollow BETWEEN '2018-04-01' AND '2018-04-30')                 ) AS diu        LEFT JOIN visit        ON diu.pcucodeperson=visit.pcucode AND diu.seq=visit.visitno        JOIN person p ON diu.pcucodeperson=p.pcucodeperson AND diu.pid=p.pid        ) as visit  LEFT join            (SELECT DISTINCT pcucodeperson,pid FROM personchronic             WHERE ((lcase(left(chroniccode,3)) between 'e10' and 'e14')                OR (lcase(left(chroniccode,3)) between 'i10' and 'i15'))           ) AS pcn on visit.pcucodeperson = pcn.pcucodeperson and visit.pid = pcn.pid  LEFT JOIN `user` u ON visit.username=u.username LEFT JOIN (SELECT pcucodeperson ,pid ,IF(deaddate IS NULL OR YEAR(deaddate)='0000','1890-01-01',deaddate) dd FROM persondeath pd ) pde ON visit.pcucodeperson=pde.pcucodeperson AND visit.pid=pde.pid  where        visit.eyeopthalmoscope in('1','2','3','4','8','9')  AND (pde.dd>=datefollow or pde.pid IS NULL)

37->LABFU ==>> SELECT v.pcucode,v.pid,v.seq visitno,v.date_serv ,case WHEN (v.typetesting is null OR v.typetesting='1') AND v.foodsuspend='0' then '0531101'       WHEN (v.typetesting is null OR v.typetesting='1') AND v.foodsuspend='1' then '0531102'       WHEN (v.typetesting='2') AND v.foodsuspend='0' then '0531002'       WHEN (v.typetesting='2') AND v.foodsuspend='1' then '0531004'       else '0531101' END as labcode ,v.sugarnumdigit ,IF(v.dateupdate IS NULL OR TRIM(v.dateupdate)='' OR v.dateupdate LIKE '0000-00-00%'    ,concat(replace(v.date_serv,'-',''),left(REPLACE(TIME(NOW()),':',''),2),'0',left(v.pid,1),right(REPLACE(TIME(NOW()),':',''),2))   ,DATE_FORMAT(v.dateupdate,'%Y%m%d%H%i%s') ) AS dateupdate     ,v.pcucode hosservice    ,if(p.idcard is null or trim(p.idcard)='' or length(trim(p.idcard))<13,'',p.idcard) cid  FROM (       select pcucodeperson,visit.pcucode, visit.pid,visitdate, visit.visitno AS seq, visit.username,flagservice              ,IF(visit.visitdate IS NULL OR TRIM(visit.visitdate)='' OR visit.visitdate LIKE '0000-00-00%','',DATE_FORMAT(visit.visitdate,'%Y%m%d')) AS date_serv              ,sugarnumdigit,typetesting,foodsuspend,diu.dateupdate        from (select pcucode, visitno,sugarnumdigit,typetesting,foodsuspend,dateupdate from visitlabsugarblood              WHERE DATE(dateupdate) BETWEEN '2018-04-01' AND '2018-04-30' GROUP BY visitno             ) AS diu        JOiN visit        ON diu.pcucode=visit.pcucode AND diu.visitno=visit.visitno       ) as v  JOIN person p on v.pcucodeperson=p.pcucodeperson and v.pid=p.pid  LEFT JOIN (SELECT pcucodeperson ,pid ,IF(deaddate IS NULL OR YEAR(deaddate)='0000','1890-01-01',deaddate) dd FROM persondeath pd ) pde ON v.pcucodeperson=pde.pcucodeperson AND v.pid=pde.pid  WHERE        v.sugarnumdigit IS NOT NULL  AND (pde.dd>=visitdate or pde.pid IS NULL)  AND ((v.visitdate BETWEEN '2018-04-01' AND '2018-04-30')  OR (date(v.dateupdate) BETWEEN '2018-04-01' AND '2018-04-30'))  UNION SELECT v.pcucodeperson as pcucode,v.pid,if(v.visitno is null OR v.visitno = '','',v.visitno) as visitno,        IF(v.datecheck IS NULL OR TRIM(v.datecheck)='' OR v.datecheck LIKE '0000-00-00%','',DATE_FORMAT(v.datecheck,'%Y%m%d')) AS date_serv ,IFNULL(cL.labmappingexpo43,v.labcode) AS labcode ,v.labresultdigit as sugarnumdigit,IF(v.dateupdate IS NULL OR TRIM(v.dateupdate)='' OR v.dateupdate LIKE '0000-00-00%'    ,concat(replace(v.datecheck,'-',''),left(REPLACE(TIME(NOW()),':',''),2),'0',left(v.pid,1),right(REPLACE(TIME(NOW()),':',''),2))   ,DATE_FORMAT(v.dateupdate,'%Y%m%d%H%i%s')) AS dateupdate    ,hosservice    ,if(p.idcard is null or trim(p.idcard)='' or length(trim(p.idcard))<13,'',p.idcard) cid  FROM        (select pcucodeperson,pid, pcucode,visitno,labresultdigit,labcode,datecheck,dateupdate, hosservice        from visitlabchcyhembmsse        WHERE (DATE(dateupdate) BETWEEN '2018-04-01' AND '2018-04-30'                 OR datecheck BETWEEN '2018-04-01' AND '2018-04-30')       ) as v  LEFT join clabchcyhembmsse cL ON v.labcode=cL.labcode  join person p on v.pcucodeperson=p.pcucodeperson and v.pid=p.pid  LEFT JOIN (SELECT pcucodeperson ,pid ,IF(deaddate IS NULL OR YEAR(deaddate)='0000','1890-01-01',deaddate) dd FROM persondeath pd ) pde ON v.pcucodeperson=pde.pcucodeperson AND v.pid=pde.pid  WHERE v.labresultdigit is NOT null   AND (pde.dd>=datecheck or pde.pid IS NULL)  AND ((v.datecheck BETWEEN '2018-04-01' AND '2018-04-30')  OR (date(v.dateupdate) BETWEEN '2018-04-01' AND '2018-04-30'))  GROUP BY v.pcucodeperson,v.pid,v.visitno,v.datecheck,v.labcode

38->ChargeOPD:==>> SELECT v.pcucode hospcode,v.pid,v.visitno seq ,REPLACE(visitdate,'-','') date_serv ,'00000' clinic ,chargeitem,chargelist ,SUM(unit) quantity ,IF(mapright IS NULL OR LENGTH(mapright)<>4,'9100',mapright) instype ,replace(FORMAT(SUM(IF(vd.unit IS NULL OR TRIM(vd.unit)='',0,vd.unit)*IF(vd.costprice IS NULL OR TRIM(vd.costprice)='',0,vd.costprice)),2),',','') cost,replace(FORMAT(SUM(IF(vd.unit IS NULL OR TRIM(vd.unit)='',0,vd.unit)*IF(vd.realprice IS NULL OR TRIM(vd.realprice)='',0,vd.realprice)),2),',','') price,replace(FORMAT(SUM(IF(vd.unit IS NULL OR TRIM(vd.unit)='',0,vd.unit)*IF(vd.noclaimprice IS NULL OR TRIM(vd.noclaimprice)='',0,vd.noclaimprice)),2),',','') payprice,IF(v.dateupdate IS NULL OR v.dateupdate LIKE '0000-00-00%',DATE_FORMAT(v.visitdate,'%Y%m%d%H%i%s'),DATE_FORMAT(v.dateupdate,'%Y%m%d%H%i%s') ) AS d_update ,if(v.idcard is null or trim(v.idcard)='' or length(trim(v.idcard))<13,'',v.idcard) cid  FROM visitdrug vd  JOIN(      SELECT vs.pcucode ,vs.visitno ,vs.pcucodeperson,vs.pid ,vs.visitdate ,vs.dateupdate ,vs.rightcode, p.idcard       FROM visitdrug d       JOIN visit vs ON d.pcucode=vs.pcucode and d.visitno=vs.visitno       JOIN person p ON vs.pcucodeperson=p.pcucodeperson and vs.pid=p.pid  WHERE vs.visitdate BETWEEN '2018-04-01' AND '2018-04-30' Group By vs.pcucode,vs.visitno      ) AS v  ON vd.pcucode=v.pcucode AND vd.visitno=v.visitno  LEFT JOIN cright cr ON v.rightcode = cr.rightcode  JOIN cdrug cd ON  vd.drugcode=cd.drugcode  WHERE chargeitem IS NOT NULL AND chargelist IS NOT NULL  GROUP BY v.pcucode,v.visitno,chargeitem,chargelist

39->Provider:  SELECT pcucode hospcode,ifnull(idcard,'') provider ,ifnull(noofoccupation,'') registerno ,if(council IS NULL OR council NOT IN('01','02','03','04','05','06','07'),'',council) council ,ifnull(idcard,'') cid ,ifnull(prename,'') pn,ifnull(fname,'') name,ifnull(lname,'') lname ,ifnull(usersex,'2') sex ,IF(zero2null(userbirth) IS NULL,'',REPLACE(userbirth,'-','')) birth ,CASE WHEN officertype ='1' THEN '01' WHEN officertype ='2' THEN '02' WHEN officertype ='3' THEN '03'       WHEN officertype ='5' THEN '04' WHEN officertype ='4' THEN '05' WHEN officertype ='6' THEN '06'       WHEN officertype ='8' THEN '08' WHEN LCase(officertype) ='w' THEN '07'       WHEN LCase(officertype) ='a' THEN '10'       WHEN officertype IN('081','082','083','084','085') THEN officertype  ELSE '09' END providertype ,IF(zero2null(dateworkhere) IS NULL,'',REPLACE(dateworkhere,'-','')) startdate ,IF(zero2null(datemovehere) IS NULL,'',REPLACE(datemovehere,'-','')) outdate ,ifnull(pcucodemovefrom,'') moveform ,ifnull(pcucodemoveto,'') moveto ,REPLACE(REPLACE(REPLACE(IFNULL(`user`.dateupdate,CONCAT(CURDATE(),CURTIME())),'-',''),' ',''),':','') d_update  FROM `user`  LEFT JOIN (SELECT pcucode p, username u FROM visit               WHERE (visit.visitdate BETWEEN '2018-04-01' AND '2018-04-30')                OR (DATE(visit.dateupdate) BETWEEN '2018-04-30' AND '2018-04-30')             GROUP BY pcucode,username            ) AS xu ON `user`.pcucode=xu.p AND `user`.username=xu.u WHERE (lcase(username) NOT IN('adm','newuser','drug_store_admin','move_all_person_out','usr_create_func','usr_db','student_update') AND LCASE(LEFT(username,10)) NOT IN('code_manag','usr_repair')   AND TRIM(username)<>''   AND pcucode ='04232'  AND ((DATE(`user`.dateupdate) between '2018-04-01' AND '2018-04-30')  AND (lcase(username) NOT IN('adm','newuser','drug_store_admin','move_all_person_out','usr_create_func','usr_db','student_update') AND LCASE(LEFT(username,10)) NOT IN('code_manag','usr_repair')))) OR (xu.p IS NOT NULL)

40->Address = SELECT person.pcucodeperson hospcode,person.pid,'1' addresstype ,IF(LENGTH(trim(hidmoi11))<11 OR hidmoi11 IS NULL,'',trim(hidmoi11)) house_id ,IF(housetype IS NULL OR housetype NOT IN('1','2','3','4','5','8','9'),'9',housetype) housetype ,IFNULL(roomno,'') roomno ,IFNULL(condo,'') condo  ,IFNULL(hnomoi,'') houseno ,IFNULL(soisub,'') soisub ,IFNULL(soimain,'') soimain ,IFNULL(roadmoi,'') road ,'' villaname  ,IF(mumoi IS NULL OR trim(mumoi)='0' OR trim(mumoi)='','99',if(LENGTH(trim(mumoi))=1 ,concat('0',trim(mumoi)),mumoi)) village  ,IF(subdistcodemoi IS NULL OR trim(subdistcodemoi)='','99',subdistcodemoi) tambon  ,IF(distcodemoi IS NULL OR trim(distcodemoi)='','99',IF(distcodemoi='99','01',distcodemoi)) ampur  ,IF(provcodemoi IS NULL OR trim(provcodemoi)='','99',provcodemoi) changwat  ,REPLACE(REPLACE(REPLACE(IFNULL(person.dateupdateaddressout,CONCAT(CURDATE(),CURTIME())),'-',''),' ',''),':','') d_update  ,person.idcard cid  FROM person   JOIN house on person.pcucodeperson=house.pcucode AND person.hcode=house.hcode  WHERE (RIGHT(house.villcode,2)='00' OR person.typelive = '3')  AND (person.dateupdateaddressout BETWEEN '2018-04-01' AND '2018-04-30')  UNION SELECT person.pcucodeperson hospcode,person.pid,'2' addresstype ,IF(LENGTH(trim(hidmoi11))<11 OR hidmoi11 IS NULL,'',trim(hidmoi11)) house_id ,IF(typelive IN('0','1','3'),IF(housechar IS NULL OR housechar NOT IN('1','2','3','4','5','8','9'),'9'    ,CASE WHEN housechar='1' THEN '1' WHEN housechar='2' THEN '2' WHEN housechar='3' THEN '4'          WHEN housechar='4' THEN '3' WHEN housechar='5' THEN '5' WHEN housechar='9' THEN '8'     ELSE '9' END),'9') housetype ,IFNULL(roomno,'') roomno ,IFNULL(condo,'') condo  ,IFNULL(q.hno,'') houseno ,'' soisub ,'' soimain ,'' road ,'' villaname  ,IF(mu IS NULL OR trim(mu)='0' OR trim(mu)='','99',if(LENGTH(trim(mu))=1 ,concat('0',trim(mu)),mu)) village  ,IF(subdistcode IS NULL OR trim(subdistcode)='','99',subdistcode) tambon  ,IF(distcode IS NULL OR trim(distcode)='','99',IF(distcode='99','01',distcode)) ampur  ,IF(provcode IS NULL OR trim(provcode)='','99',provcode) changwat  ,REPLACE(REPLACE(REPLACE(IFNULL(person.dateupdateaddressout,CONCAT(CURDATE(),CURTIME())),'-',''),' ',''),':','') d_update ,person.idcard cid  FROM personaddresscontact q  JOIN person ON q.pcucodeperson=person.pcucodeperson AND q.pid=person.pid  JOIN house on person.pcucodeperson=house.pcucode AND person.hcode=house.hcode  WHERE (RIGHT(house.villcode,2)='00' OR person.typelive = '3')  AND (person.dateupdateaddressout BETWEEN '2018-04-01' AND '2018-04-30')

41->Newborn:  SELECT vc.pcucodechild hospcode,vc.pidchild pid,vc.pid mpid,vc.pregno gravida ,FLOOR(DATEDIFF(datedeliver,lmp)/7) ga,IF(datedeliver IS NULL OR YEAR(datedeliver)='0000','',REPLACE(datedeliver,'-','')) bdate ,IF(vd.delivertime IS NULL OR TRIM(vd.delivertime)='','',REPLACE(vd.delivertime,':','')) btime ,IF(vd.deliverplace IS NULL OR vd.deliverplace NOT IN('1','2','3','4'),'5',vd.deliverplace) bplace ,IF(vd.hosservice IS NULL OR TRIM(vd.hosservice) ='','00000',vd.hosservice) bhosp ,IF(birthno IS NULL OR TRIM(birthno)='','1',birthno) birthno ,IF(vc.delivertype IS NULL OR TRIM(vc.delivertype)='','1',vc.delivertype) btype ,IF(vd.operater IS NULL OR vd.operater NOT IN('1','2','3','4','5'),'1',vd.operater) bdoctor ,IF(vc.weight IS NULL OR TRIM(vc.weight)='','',vc.weight) bweight ,IF(a1 IS NULL AND p1 IS NULL AND g1 IS NULL AND x1 IS NULL AND r1 IS NULL   ,IF(apgar1minutesum IS NULL OR ABS(apgar1minutesum)>10,'99',ABS(apgar1minutesum))    ,IF(IFNULL(ABS(a1),0)+IFNULL(ABS(p1),0)+IFNULL(ABS(g1),0)+IFNULL(ABS(x1),0)+IFNULL(ABS(r1),0)>10,'99',    IFNULL(ABS(a1),0)+IFNULL(ABS(p1),0)+IFNULL(ABS(g1),0)+IFNULL(ABS(x1),0)+IFNULL(ABS(r1),0)))  asphyxia  ,CASE WHEN vc.vitamink='0' THEN '2' WHEN vc.vitamink IN('1','2') THEN '1' ELSE '9' END vitk ,IF(vc.tsh IS NULL OR vc.tsh NOT IN('1','2','9'),'9',vc.tsh) tsh ,IF(vc.tsh IS NULL OR vc.tsh<>'1','',IF(tshresult IS NULL OR TRIM(tshresult)='','',FORMAT(tshresult,1))) tshresult ,REPLACE(REPLACE(REPLACE(IFNULL(vc.dateupdate,CONCAT(CURDATE(),CURTIME())),'-',''),' ',''),':','') d_update  ,if(p.idcard is null or trim(p.idcard)='' or length(trim(p.idcard))<13,'',p.idcard) cid  FROM visitancdeliverchild vc  JOIN visitancdeliver vd on vc.pcucodeperson=vd.pcucodeperson and vc.pid=vd.pid and vc.pregno=vd.pregno  JOIN visitancpregnancy vp ON vc.pcucodeperson=vp.pcucodeperson AND vc.pid=vp.pid AND vc.pregno=vp.pregno  join person p on vc.pcucodechild=p.pcucodeperson and vc.pidchild=p.pid  WHERE  (vd.datedeliver between '2018-04-01' AND '2018-04-30') OR (DATE(vc.dateupdate) between '2018-04-01' AND '2018-04-30')

42->Person=>> SELECT DISTINCT  trim(person.pcucodeperson) AS pcucode, person.idcard, person.pid, person.hcode,  person.prename, person.fname, person.lname, person.sex,  IF(person.birth IS NULL OR TRIM(person.birth)='' OR person.birth LIKE '0000-00-00%','',DATE_FORMAT(person.birth,'%Y%m%d')) AS birth,  person.roadmoi, person.hnomoi,  if(length(trim(person.mumoi))=1,concat('0',trim(person.mumoi)),if(trim(person.mumoi)='' or mumoi is NULL,'00',mumoi)) as mumoi,  person.subdistcodemoi, person.distcodemoi, person.provcodemoi,  person.marystatus,  IF(person.origin IS NULL OR TRIM(person.origin)='','',person.origin) AS origin,  IF(person.nation IS NULL OR TRIM(person.nation)='','',person.nation) AS nation,person.religion,IF(person.educate IS NULL OR person.educate NOT IN('00','01','02','03','04','05','06','09'),'09',person.educate) educate , person.familyposition, person.fatherid, person.motherid, person.mateid,  IF(person.datein IS NULL OR TRIM(person.datein)='' OR person.datein LIKE '0000-00-00%','',DATE_FORMAT(person.datein,'%Y%m%d')) AS movein,  person.dischargetype AS dischar,  IF(person.dischargedate IS NULL OR TRIM(person.dischargedate)='' OR person.dischargedate LIKE '0000-00-00%','',DATE_FORMAT(person.dischargedate,'%Y%m%d')) AS ddisch,  concat(ifnull(bloodgroup,''),ifnull(bloodrh,'')) as bloodgroup , if(nation='94' or nation='95' or nation='99','',if(intercode IN('01','02','03','11','12','13','14','15','16','17','18','21','22','23'),intercode,'23') ) as intercode, if(typelive='0','1',if(typelive='5','0',typelive)) as typelive,  IF(house.villcode IS NOT NULL,house.villcode,'') AS villcode,  IF(length(occupa)<>3,'',occupa) AS mapoccupation,  IF(cnation.mapnation IS NOT NULL AND TRIM(cnation.mapnation)<>'',cnation.mapnation,'') AS mapnation,  IF(cnationrace.mapnation IS NOT NULL AND TRIM(cnationrace.mapnation)<>'',cnationrace.mapnation,'') AS maporigin,  IF( person.dateupdate IS NULL OR TRIM(person.dateupdate)='' OR person.dateupdate LIKE '0000-00-00%','',DATE_FORMAT(person.dateupdate,'%Y%m%d%H%i%s') ) AS d_update ,if(length(trim(occupa))=4,occupa,if(o2.mapoccupa IS NULL OR length(trim(o2.mapoccupa))=0,'',o2.mapoccupa)) occupa_new ,pst.tp vstatus ,if(Ucase(bloodrh)='P','1',if(Ucase(bloodrh)='N','2','')) bloodrh ,ifnull(passpotnumber,'') passport ,IF(person.telephoneperson IS NULL,'',IF(LENGTH(trim(person.telephoneperson))>15,SUBSTRING(person.telephoneperson,15,1),person.telephoneperson)) telephone,IF(person.mobile IS NULL,'',IF(LENGTH(trim(person.mobile))>15,SUBSTRING(person.mobile,15,1),person.mobile)) mobile FROM person  LEFT JOIN house ON (person.pcucodeperson=house.pcucode AND person.hcode=house.hcode)  LEFT JOIN coccupa ON (person.occupa=coccupa.occupacode)  LEFT JOIN coccupa o2 ON (person.occupa=o2.occupacode)  JOIN _rit_person_tmp rtmp ON person.pcucodeperson=rtmp.pcucode AND person.pid=rtmp.pid  LEFT JOIN cnation ON (person.nation=cnation.nationcode)  LEFT JOIN cnation cnationrace ON (person.origin=cnationrace.nationcode)  LEFT JOIN persondeath pd ON person.pcucodeperson=pd.pcucodeperson AND person.pid=pd.pid  LEFT JOIN (SELECT t.pcucodeperson ,t.pid,CASE WHEN t.typecode IN('01','02') THEN '1' WHEN t.typecode ='09' THEN '2'       WHEN t.typecode IN('11','12') THEN '4' WHEN t.typecode ='03' THEN '3'       WHEN t.typecode IS NULL THEN '' ELSE '5' END AS tp  FROM persontype t WHERE t.typecode<>'10' GROUP BY t.pid) AS pst  ON person.pcucodeperson = pst.pcucodeperson AND person.pid = pst.pid  UNION DISTINCT SELECT DISTINCT  trim(person.pcucodeperson) AS pcucode, person.idcard, person.pid, person.hcode,  person.prename, person.fname, person.lname, person.sex,  IF(person.birth IS NULL OR TRIM(person.birth)='' OR person.birth LIKE '0000-00-00%','',DATE_FORMAT(person.birth,'%Y%m%d')) AS birth,  person.roadmoi, person.hnomoi,  if(length(trim(person.mumoi))=1,concat('0',trim(person.mumoi)),if(trim(person.mumoi)='' or mumoi is NULL,'00',mumoi)) as mumoi,  person.subdistcodemoi, person.distcodemoi, person.provcodemoi,  person.marystatus,  IF(person.origin IS NULL OR TRIM(person.origin)='','',person.origin) AS origin,  IF(person.nation IS NULL OR TRIM(person.nation)='','',person.nation) AS nation,person.religion,IF(person.educate IS NULL OR person.educate NOT IN('00','01','02','03','04','05','06','09'),'09',person.educate) educate , person.familyposition, person.fatherid, person.motherid, person.mateid,  IF(person.datein IS NULL OR TRIM(person.datein)='' OR person.datein LIKE '0000-00-00%','',DATE_FORMAT(person.datein,'%Y%m%d')) AS movein,  person.dischargetype AS dischar,  IF(person.dischargedate IS NULL OR TRIM(person.dischargedate)='' OR person.dischargedate LIKE '0000-00-00%','',DATE_FORMAT(person.dischargedate,'%Y%m%d')) AS ddisch,  concat(ifnull(bloodgroup,''),ifnull(bloodrh,'')) as bloodgroup , if(nation='94' or nation='95' or nation='99','',if(intercode IN('01','02','03','11','12','13','14','15','16','17','18','21','22','23'),intercode,'23') ) as intercode, if(typelive='0','1',if(typelive='5','0',typelive)) as typelive,  IF(house.villcode IS NOT NULL,house.villcode,'') AS villcode,  IF(length(occupa)<>3,'',occupa) AS mapoccupation,  IF(cnation.mapnation IS NOT NULL AND TRIM(cnation.mapnation)<>'',cnation.mapnation,'') AS mapnation,  IF(cnationrace.mapnation IS NOT NULL AND TRIM(cnationrace.mapnation)<>'',cnationrace.mapnation,'') AS maporigin,  IF( person.dateupdate IS NULL OR TRIM(person.dateupdate)='' OR person.dateupdate LIKE '0000-00-00%','',DATE_FORMAT(person.dateupdate,'%Y%m%d%H%i%s') ) AS d_update ,if(length(trim(occupa))=4,occupa,if(o2.mapoccupa IS NULL OR length(trim(o2.mapoccupa))=0,'',o2.mapoccupa)) occupa_new ,pst.tp vstatus ,if(Ucase(bloodrh)='P','1',if(Ucase(bloodrh)='N','2','')) bloodrh ,ifnull(passpotnumber,'') passport ,IF(person.telephoneperson IS NULL,'',IF(LENGTH(trim(person.telephoneperson))>15,SUBSTRING(person.telephoneperson,15,1),person.telephoneperson)) telephone,IF(person.mobile IS NULL,'',IF(LENGTH(trim(person.mobile))>15,SUBSTRING(person.mobile,15,1),person.mobile)) mobile FROM person  LEFT JOIN house ON (person.pcucodeperson=house.pcucode AND person.hcode=house.hcode)  LEFT JOIN coccupa ON (person.occupa=coccupa.occupacode)  LEFT JOIN coccupa o2 ON (person.occupa=o2.occupacode)  LEFT JOIN cnation ON (person.nation=cnation.nationcode)  LEFT JOIN cnation cnationrace ON (person.origin=cnationrace.nationcode)  INNER JOIN visit ON (person.pcucodeperson=visit.pcucodeperson AND person.pid=visit.pid)  LEFT JOIN (SELECT t.pcucodeperson ,t.pid,CASE WHEN t.typecode IN('01','02') THEN '1' WHEN t.typecode ='09' THEN '2'       WHEN t.typecode IN('11','12') THEN '4' WHEN t.typecode ='03' THEN '3'       WHEN t.typecode IS NULL THEN '' ELSE '5' END AS tp  FROM persontype t WHERE t.typecode<>'10' GROUP BY t.pid) AS pst  ON person.pcucodeperson = pst.pcucodeperson AND person.pid = pst.pid WHERE TRIM(person.pcucodeperson)<>'' AND person.pcucodeperson='04232' AND flagservice not in('04','05','06','07')AND trim(person.fname) <> 'เบิกวัสดุ' and visit.flagservice <> '99'  AND (visit.visitdate >= '2010-01-01') AND (visit.visitdate BETWEEN '2018-04-01' AND '2018-04-30')  UNION DISTINCT SELECT DISTINCT  trim(person.pcucodeperson) AS pcucode, person.idcard, person.pid, person.hcode,  person.prename, person.fname, person.lname, person.sex,  IF(person.birth IS NULL OR TRIM(person.birth)='' OR person.birth LIKE '0000-00-00%','',DATE_FORMAT(person.birth,'%Y%m%d')) AS birth,  person.roadmoi, person.hnomoi,  if(length(trim(person.mumoi))=1,concat('0',trim(person.mumoi)),if(trim(person.mumoi)='' or mumoi is NULL,'00',mumoi)) as mumoi,  person.subdistcodemoi, person.distcodemoi, person.provcodemoi,  person.marystatus,  IF(person.origin IS NULL OR TRIM(person.origin)='','',person.origin) AS origin,  IF(person.nation IS NULL OR TRIM(person.nation)='','',person.nation) AS nation,person.religion,IF(person.educate IS NULL OR person.educate NOT IN('00','01','02','03','04','05','06','09'),'09',person.educate) educate , person.familyposition, person.fatherid, person.motherid, person.mateid,  IF(person.datein IS NULL OR TRIM(person.datein)='' OR person.datein LIKE '0000-00-00%','',DATE_FORMAT(person.datein,'%Y%m%d')) AS movein,  person.dischargetype AS dischar,  IF(person.dischargedate IS NULL OR TRIM(person.dischargedate)='' OR person.dischargedate LIKE '0000-00-00%','',DATE_FORMAT(person.dischargedate,'%Y%m%d')) AS ddisch,  concat(ifnull(bloodgroup,''),ifnull(bloodrh,'')) as bloodgroup , if(nation='94' or nation='95' or nation='99','',if(intercode IN('01','02','03','11','12','13','14','15','16','17','18','21','22','23'),intercode,'23') ) as intercode, if(typelive='0','1',if(typelive='5','0',typelive)) as typelive,  IF(house.villcode IS NOT NULL,house.villcode,'') AS villcode,  IF(length(occupa)<>3,'',occupa) AS mapoccupation,  IF(cnation.mapnation IS NOT NULL AND TRIM(cnation.mapnation)<>'',cnation.mapnation,'') AS mapnation,  IF(cnationrace.mapnation IS NOT NULL AND TRIM(cnationrace.mapnation)<>'',cnationrace.mapnation,'') AS maporigin,  IF( person.dateupdate IS NULL OR TRIM(person.dateupdate)='' OR person.dateupdate LIKE '0000-00-00%','',DATE_FORMAT(person.dateupdate,'%Y%m%d%H%i%s') ) AS d_update ,if(length(trim(occupa))=4,occupa,if(o2.mapoccupa IS NULL OR length(trim(o2.mapoccupa))=0,'',o2.mapoccupa)) occupa_new ,pst.tp vstatus ,if(Ucase(bloodrh)='P','1',if(Ucase(bloodrh)='N','2','')) bloodrh ,ifnull(passpotnumber,'') passport ,IF(person.telephoneperson IS NULL,'',IF(LENGTH(trim(person.telephoneperson))>15,SUBSTRING(person.telephoneperson,15,1),person.telephoneperson)) telephone,IF(person.mobile IS NULL,'',IF(LENGTH(trim(person.mobile))>15,SUBSTRING(person.mobile,15,1),person.mobile)) mobile FROM person  LEFT JOIN house ON (person.pcucodeperson=house.pcucode AND person.hcode=house.hcode)  LEFT JOIN coccupa ON (person.occupa=coccupa.occupacode)  LEFT JOIN coccupa o2 ON (person.occupa=o2.occupacode)  LEFT JOIN cnation ON (person.nation=cnation.nationcode)  LEFT JOIN cnation cnationrace ON (person.origin=cnationrace.nationcode)  INNER JOIN personchronic ON (person.pcucodeperson=personchronic.pcucodeperson AND person.pid=personchronic.pid)  LEFT JOIN (SELECT t.pcucodeperson ,t.pid,CASE WHEN t.typecode IN('01','02') THEN '1' WHEN t.typecode ='09' THEN '2'       WHEN t.typecode IN('11','12') THEN '4' WHEN t.typecode ='03' THEN '3'       WHEN t.typecode IS NULL THEN '' ELSE '5' END AS tp  FROM persontype t WHERE t.typecode<>'10' GROUP BY t.pid) AS pst  ON person.pcucodeperson = pst.pcucodeperson AND person.pid = pst.pid WHERE TRIM(person.pcucodeperson)<>'' AND person.pcucodeperson='04232' AND trim(person.fname) <> 'เบิกวัสดุ'  AND (personchronic.dateupdate >= '2010-01-01') AND ( (personchronic.datedxfirst BETWEEN '2018-04-01' AND '2018-04-30') OR (personchronic.dateupdate BETWEEN '2018-04-01' AND '2018-04-30') )  UNION DISTINCT SELECT DISTINCT  trim(person.pcucodeperson) AS pcucode, person.idcard, person.pid, person.hcode,  person.prename, person.fname, person.lname, person.sex,  IF(person.birth IS NULL OR TRIM(person.birth)='' OR person.birth LIKE '0000-00-00%','',DATE_FORMAT(person.birth,'%Y%m%d')) AS birth,  person.roadmoi, person.hnomoi,  if(length(trim(person.mumoi))=1,concat('0',trim(person.mumoi)),if(trim(person.mumoi)='' or mumoi is NULL,'00',mumoi)) as mumoi,  person.subdistcodemoi, person.distcodemoi, person.provcodemoi,  person.marystatus,  IF(person.origin IS NULL OR TRIM(person.origin)='','',person.origin) AS origin,  IF(person.nation IS NULL OR TRIM(person.nation)='','',person.nation) AS nation,person.religion, IF(person.educate IS NULL OR person.educate NOT IN('00','01','02','03','04','05','06','09'),'09',person.educate) educate , person.familyposition, person.fatherid, person.motherid, person.mateid,  IF(person.datein IS NULL OR TRIM(person.datein)='' OR person.datein LIKE '0000-00-00%','',DATE_FORMAT(person.datein,'%Y%m%d')) AS movein,  person.dischargetype AS dischar,  IF(person.dischargedate IS NULL OR TRIM(person.dischargedate)='' OR person.dischargedate LIKE '0000-00-00%','',DATE_FORMAT(person.dischargedate,'%Y%m%d')) AS ddisch,  concat(ifnull(bloodgroup,''),ifnull(bloodrh,'')) as bloodgroup , if(nation='94' or nation='95' or nation='99','',if(intercode IN('01','02','03','11','12','13','14','15','16','17','18','21','22','23'),intercode,'23') ) as intercode, if(typelive='0','1',if(typelive='5','0',typelive)) as typelive,  IF(house.villcode IS NOT NULL,house.villcode,'') AS villcode,  IF(length(occupa)<>3,'',occupa) AS mapoccupation,  IF(cnation.mapnation IS NOT NULL AND TRIM(cnation.mapnation)<>'',cnation.mapnation,'') AS mapnation,  IF(cnationrace.mapnation IS NOT NULL AND TRIM(cnationrace.mapnation)<>'',cnationrace.mapnation,'') AS maporigin,  IF( person.dateupdate IS NULL OR TRIM(person.dateupdate)='' OR person.dateupdate LIKE '0000-00-00%','',DATE_FORMAT(person.dateupdate,'%Y%m%d%H%i%s') ) AS d_update ,if(length(trim(occupa))=4,occupa,if(o2.mapoccupa IS NULL OR length(trim(o2.mapoccupa))=0,'',o2.mapoccupa)) occupa_new ,pst.tp vstatus ,if(Ucase(bloodrh)='P','1',if(Ucase(bloodrh)='N','2','')) bloodrh ,ifnull(passpotnumber,'') passport ,IF(person.telephoneperson IS NULL,'',IF(LENGTH(trim(person.telephoneperson))>15,SUBSTRING(person.telephoneperson,15,1),person.telephoneperson)) telephone,IF(person.mobile IS NULL,'',IF(LENGTH(trim(person.mobile))>15,SUBSTRING(person.mobile,15,1),person.mobile)) mobile FROM person  LEFT JOIN house ON (person.pcucodeperson=house.pcucode AND person.hcode=house.hcode)  LEFT JOIN coccupa ON (person.occupa=coccupa.occupacode)  LEFT JOIN coccupa o2 ON (person.occupa=o2.occupacode)  LEFT JOIN cnation ON (person.nation=cnation.nationcode)  LEFT JOIN cnation cnationrace ON (person.origin=cnationrace.nationcode)  INNER JOIN persondeath ON (person.pcucodeperson=persondeath.pcucodeperson AND person.pid=persondeath.pid)  LEFT JOIN (SELECT t.pcucodeperson ,t.pid,CASE WHEN t.typecode IN('01','02') THEN '1' WHEN t.typecode ='09' THEN '2'       WHEN t.typecode IN('11','12') THEN '4' WHEN t.typecode ='03' THEN '3'       WHEN t.typecode IS NULL THEN '' ELSE '5' END AS tp  FROM persontype t WHERE t.typecode<>'10' GROUP BY t.pid) AS pst  ON person.pcucodeperson = pst.pcucodeperson AND person.pid = pst.pid  WHERE TRIM(person.pcucodeperson)<>'' AND person.pcucodeperson='04232' AND trim(person.fname)<>'เบิกวัสดุ'  AND (persondeath.dateupdate >= '2010-01-01') AND ( (persondeath.deaddate BETWEEN '2018-04-01' AND '2018-04-30') OR (persondeath.dateupdate BETWEEN '2018-04-01' AND '2018-04-30') )  UNION DISTINCT SELECT DISTINCT  trim(person.pcucodeperson) AS pcucode, person.idcard, person.pid, person.hcode,  person.prename, person.fname, person.lname, person.sex,  IF(person.birth IS NULL OR TRIM(person.birth)='' OR person.birth LIKE '0000-00-00%','',DATE_FORMAT(person.birth,'%Y%m%d')) AS birth,  person.roadmoi, person.hnomoi,  if(length(trim(person.mumoi))=1,concat('0',trim(person.mumoi)),if(trim(person.mumoi)='' or mumoi is NULL,'00',mumoi)) as mumoi,  person.subdistcodemoi, person.distcodemoi, person.provcodemoi,  person.marystatus,  IF(person.origin IS NULL OR TRIM(person.origin)='','',person.origin) AS origin,  IF(person.nation IS NULL OR TRIM(person.nation)='','',person.nation) AS nation,person.religion, IF(person.educate IS NULL OR person.educate NOT IN('00','01','02','03','04','05','06','09'),'09',person.educate) educate , person.familyposition, person.fatherid, person.motherid, person.mateid,  IF(person.datein IS NULL OR TRIM(person.datein)='' OR person.datein LIKE '0000-00-00%','',DATE_FORMAT(person.datein,'%Y%m%d')) AS movein,  person.dischargetype AS dischar,  IF(person.dischargedate IS NULL OR TRIM(person.dischargedate)='' OR person.dischargedate LIKE '0000-00-00%','',DATE_FORMAT(person.dischargedate,'%Y%m%d')) AS ddisch,  concat(ifnull(bloodgroup,''),ifnull(bloodrh,'')) as bloodgroup , if(nation='94' or nation='95' or nation='99','',if(intercode IN('01','02','03','11','12','13','14','15','16','17','18','21','22','23'),intercode,'23') ) as intercode, if(typelive='0','1',if(typelive='5','0',typelive)) as typelive,  IF(house.villcode IS NOT NULL,house.villcode,'') AS villcode,  IF(length(occupa)<>3,'',occupa) AS mapoccupation,  IF(cnation.mapnation IS NOT NULL AND TRIM(cnation.mapnation)<>'',cnation.mapnation,'') AS mapnation,  IF(cnationrace.mapnation IS NOT NULL AND TRIM(cnationrace.mapnation)<>'',cnationrace.mapnation,'') AS maporigin,  IF( person.dateupdate IS NULL OR TRIM(person.dateupdate)='' OR person.dateupdate LIKE '0000-00-00%','',DATE_FORMAT(person.dateupdate,'%Y%m%d%H%i%s') ) AS d_update ,if(length(trim(occupa))=4,occupa,if(o2.mapoccupa IS NULL OR length(trim(o2.mapoccupa))=0,'',o2.mapoccupa)) occupa_new ,pst.tp vstatus ,if(Ucase(bloodrh)='P','1',if(Ucase(bloodrh)='N','2','')) bloodrh ,ifnull(passpotnumber,'') passport ,IF(person.telephoneperson IS NULL,'',IF(LENGTH(trim(person.telephoneperson))>15,SUBSTRING(person.telephoneperson,15,1),person.telephoneperson)) telephone,IF(person.mobile IS NULL,'',IF(LENGTH(trim(person.mobile))>15,SUBSTRING(person.mobile,15,1),person.mobile)) mobile FROM person  LEFT JOIN house ON (person.pcucodeperson=house.pcucode AND person.hcode=house.hcode)  LEFT JOIN coccupa ON (person.occupa=coccupa.occupacode)  LEFT JOIN coccupa o2 ON (person.occupa=o2.occupacode)  LEFT JOIN cnation ON (person.nation=cnation.nationcode)  LEFT JOIN cnation cnationrace ON (person.origin=cnationrace.nationcode)  INNER JOIN women ON (person.pcucodeperson=women.pcucodeperson AND person.pid=women.pid)  LEFT JOIN (SELECT t.pcucodeperson ,t.pid,CASE WHEN t.typecode IN('01','02') THEN '1' WHEN t.typecode ='09' THEN '2'       WHEN t.typecode IN('11','12') THEN '4' WHEN t.typecode ='03' THEN '3'       WHEN t.typecode IS NULL THEN '' ELSE '5' END AS tp  FROM persontype t WHERE t.typecode<>'10' GROUP BY t.pid) AS pst  ON person.pcucodeperson = pst.pcucodeperson AND person.pid = pst.pid  WHERE TRIM(person.pcucodeperson)<>'' AND person.pcucodeperson='04232' AND trim(person.fname)<>'เบิกวัสดุ'  AND (women.dateupdate >= '2010-01-01' OR women.datesurvey >= '2010-01-01') AND ( (women.dateupdate BETWEEN '2018-04-01' AND '2018-04-30') OR (women.datesurvey BETWEEN '2018-04-01' AND '2018-04-30') )  UNION DISTINCT SELECT DISTINCT  trim(person.pcucodeperson) AS pcucode, person.idcard, person.pid, person.hcode,  person.prename, person.fname, person.lname, person.sex,  IF(person.birth IS NULL OR TRIM(person.birth)='' OR person.birth LIKE '0000-00-00%','',DATE_FORMAT(person.birth,'%Y%m%d')) AS birth,  person.roadmoi, person.hnomoi,  if(length(trim(person.mumoi))=1,concat('0',trim(person.mumoi)),if(trim(person.mumoi)='' or mumoi is NULL,'00',mumoi)) as mumoi,  person.subdistcodemoi, person.distcodemoi, person.provcodemoi,  person.marystatus,  IF(person.origin IS NULL OR TRIM(person.origin)='','',person.origin) AS origin,  IF(person.nation IS NULL OR TRIM(person.nation)='','',person.nation) AS nation,person.religion, IF(person.educate IS NULL OR person.educate NOT IN('00','01','02','03','04','05','06','09'),'09',person.educate) educate , person.familyposition, person.fatherid, person.motherid, person.mateid,  IF(person.datein IS NULL OR TRIM(person.datein)='' OR person.datein LIKE '0000-00-00%','',DATE_FORMAT(person.datein,'%Y%m%d')) AS movein,  person.dischargetype AS dischar,  IF(person.dischargedate IS NULL OR TRIM(person.dischargedate)='' OR person.dischargedate LIKE '0000-00-00%','',DATE_FORMAT(person.dischargedate,'%Y%m%d')) AS ddisch,  concat(ifnull(bloodgroup,''),ifnull(bloodrh,'')) as bloodgroup , if(nation='94' or nation='95' or nation='99','',if(intercode IN('01','02','03','11','12','13','14','15','16','17','18','21','22','23'),intercode,'23') ) as intercode, if(typelive='0','1',if(typelive='5','0',typelive)) as typelive,  IF(house.villcode IS NOT NULL,house.villcode,'') AS villcode,  IF(length(occupa)<>3,'',occupa) AS mapoccupation,  IF(cnation.mapnation IS NOT NULL AND TRIM(cnation.mapnation)<>'',cnation.mapnation,'') AS mapnation,  IF(cnationrace.mapnation IS NOT NULL AND TRIM(cnationrace.mapnation)<>'',cnationrace.mapnation,'') AS maporigin,  IF( person.dateupdate IS NULL OR TRIM(person.dateupdate)='' OR person.dateupdate LIKE '0000-00-00%','',DATE_FORMAT(person.dateupdate,'%Y%m%d%H%i%s') ) AS d_update ,if(length(trim(occupa))=4,occupa,if(o2.mapoccupa IS NULL OR length(trim(o2.mapoccupa))=0,'',o2.mapoccupa)) occupa_new ,pst.tp vstatus ,if(Ucase(bloodrh)='P','1',if(Ucase(bloodrh)='N','2','')) bloodrh ,ifnull(passpotnumber,'') passport ,IF(person.telephoneperson IS NULL,'',IF(LENGTH(trim(person.telephoneperson))>15,SUBSTRING(person.telephoneperson,15,1),person.telephoneperson)) telephone,IF(person.mobile IS NULL,'',IF(LENGTH(trim(person.mobile))>15,SUBSTRING(person.mobile,15,1),person.mobile)) mobile FROM person  LEFT JOIN house ON (person.pcucodeperson=house.pcucode AND person.hcode=house.hcode)  LEFT JOIN coccupa ON (person.occupa=coccupa.occupacode)  LEFT JOIN coccupa o2 ON (person.occupa=o2.occupacode)  LEFT JOIN cnation ON (person.nation=cnation.nationcode)  LEFT JOIN cnation cnationrace ON (person.origin=cnationrace.nationcode)  INNER JOIN visitepi ON (person.pcucodeperson=visitepi.pcucodeperson AND person.pid=visitepi.pid)  LEFT JOIN (SELECT t.pcucodeperson ,t.pid,CASE WHEN t.typecode IN('01','02') THEN '1' WHEN t.typecode ='09' THEN '2'       WHEN t.typecode IN('11','12') THEN '4' WHEN t.typecode ='03' THEN '3'       WHEN t.typecode IS NULL THEN '' ELSE '5' END AS tp  FROM persontype t WHERE t.typecode<>'10' GROUP BY t.pid) AS pst  ON person.pcucodeperson = pst.pcucodeperson AND person.pid = pst.pid  WHERE TRIM(person.pcucodeperson)<>'' AND person.pcucodeperson='04232' AND trim(person.fname)<>'เบิกวัสดุ'  AND (visitepi.dateepi >= '2010-01-01') AND (visitepi.dateepi BETWEEN '2018-04-01' AND '2018-04-30')  UNION DISTINCT SELECT DISTINCT  trim(person.pcucodeperson) AS pcucode, person.idcard, person.pid, person.hcode,  person.prename, person.fname, person.lname, person.sex,  IF(person.birth IS NULL OR TRIM(person.birth)='' OR person.birth LIKE '0000-00-00%','',DATE_FORMAT(person.birth,'%Y%m%d')) AS birth,  person.roadmoi, person.hnomoi,  if(length(trim(person.mumoi))=1,concat('0',trim(person.mumoi)),if(trim(person.mumoi)='' or mumoi is NULL,'00',mumoi)) as mumoi,  person.subdistcodemoi, person.distcodemoi, person.provcodemoi,  person.marystatus,  IF(person.origin IS NULL OR TRIM(person.origin)='','',person.origin) AS origin,  IF(person.nation IS NULL OR TRIM(person.nation)='','',person.nation) AS nation,person.religion, IF(person.educate IS NULL OR person.educate NOT IN('00','01','02','03','04','05','06','09'),'09',person.educate) educate , person.familyposition, person.fatherid, person.motherid, person.mateid,  IF(person.datein IS NULL OR TRIM(person.datein)='' OR person.datein LIKE '0000-00-00%','',DATE_FORMAT(person.datein,'%Y%m%d')) AS movein,  person.dischargetype AS dischar,  IF(person.dischargedate IS NULL OR TRIM(person.dischargedate)='' OR person.dischargedate LIKE '0000-00-00%','',DATE_FORMAT(person.dischargedate,'%Y%m%d')) AS ddisch,  concat(ifnull(bloodgroup,''),ifnull(bloodrh,'')) as bloodgroup , if(nation='94' or nation='95' or nation='99','',if(intercode IN('01','02','03','11','12','13','14','15','16','17','18','21','22','23'),intercode,'23') ) as intercode, if(typelive='0','1',if(typelive='5','0',typelive)) as typelive,  IF(house.villcode IS NOT NULL,house.villcode,'') AS villcode,  IF(length(occupa)<>3,'',occupa) AS mapoccupation,  IF(cnation.mapnation IS NOT NULL AND TRIM(cnation.mapnation)<>'',cnation.mapnation,'') AS mapnation,  IF(cnationrace.mapnation IS NOT NULL AND TRIM(cnationrace.mapnation)<>'',cnationrace.mapnation,'') AS maporigin,  IF( person.dateupdate IS NULL OR TRIM(person.dateupdate)='' OR person.dateupdate LIKE '0000-00-00%','',DATE_FORMAT(person.dateupdate,'%Y%m%d%H%i%s') ) AS d_update ,if(length(trim(occupa))=4,occupa,if(o2.mapoccupa IS NULL OR length(trim(o2.mapoccupa))=0,'',o2.mapoccupa)) occupa_new ,pst.tp vstatus ,if(Ucase(bloodrh)='P','1',if(Ucase(bloodrh)='N','2','')) bloodrh ,ifnull(passpotnumber,'') passport ,IF(person.telephoneperson IS NULL,'',IF(LENGTH(trim(person.telephoneperson))>15,SUBSTRING(person.telephoneperson,15,1),person.telephoneperson)) telephone,IF(person.mobile IS NULL,'',IF(LENGTH(trim(person.mobile))>15,SUBSTRING(person.mobile,15,1),person.mobile)) mobile FROM person  LEFT JOIN house ON (person.pcucodeperson=house.pcucode AND person.hcode=house.hcode)  LEFT JOIN coccupa ON (person.occupa=coccupa.occupacode)  LEFT JOIN coccupa o2 ON (person.occupa=o2.occupacode)  LEFT JOIN cnation ON (person.nation=cnation.nationcode)  LEFT JOIN cnation cnationrace ON (person.origin=cnationrace.nationcode)  INNER JOIN visitfp ON (person.pcucodeperson=visitfp.pcucodeperson AND person.pid=visitfp.pid)  LEFT JOIN (SELECT t.pcucodeperson ,t.pid,CASE WHEN t.typecode IN('01','02') THEN '1' WHEN t.typecode ='09' THEN '2'       WHEN t.typecode IN('11','12') THEN '4' WHEN t.typecode ='03' THEN '3'       WHEN t.typecode IS NULL THEN '' ELSE '5' END AS tp  FROM persontype t WHERE t.typecode<>'10' GROUP BY t.pid) AS pst  ON person.pcucodeperson = pst.pcucodeperson AND person.pid = pst.pid  WHERE TRIM(person.pcucodeperson)<>'' AND person.pcucodeperson='04232' AND trim(person.fname)<>'เบิกวัสดุ'  AND (visitfp.datefp >= '2010-01-01') AND (visitfp.datefp BETWEEN '2018-04-01' AND '2018-04-30')  UNION DISTINCT SELECT DISTINCT  trim(person.pcucodeperson) AS pcucode, person.idcard, person.pid, person.hcode,  person.prename, person.fname, person.lname, person.sex,  IF(person.birth IS NULL OR TRIM(person.birth)='' OR person.birth LIKE '0000-00-00%','',DATE_FORMAT(person.birth,'%Y%m%d')) AS birth,  person.roadmoi, person.hnomoi,  if(length(trim(person.mumoi))=1,concat('0',trim(person.mumoi)),if(trim(person.mumoi)='' or mumoi is NULL,'00',mumoi)) as mumoi,  person.subdistcodemoi, person.distcodemoi, person.provcodemoi,  person.marystatus,  IF(person.origin IS NULL OR TRIM(person.origin)='','',person.origin) AS origin,  IF(person.nation IS NULL OR TRIM(person.nation)='','',person.nation) AS nation,person.religion ,IF(person.educate IS NULL OR person.educate NOT IN('00','01','02','03','04','05','06','09'),'09',person.educate) educate , person.familyposition, person.fatherid, person.motherid, person.mateid,  IF(person.datein IS NULL OR TRIM(person.datein)='' OR person.datein LIKE '0000-00-00%','',DATE_FORMAT(person.datein,'%Y%m%d')) AS movein,  person.dischargetype AS dischar,  IF(person.dischargedate IS NULL OR TRIM(person.dischargedate)='' OR person.dischargedate LIKE '0000-00-00%','',DATE_FORMAT(person.dischargedate,'%Y%m%d')) AS ddisch,  concat(ifnull(bloodgroup,''),ifnull(bloodrh,'')) as bloodgroup , if(nation='94' or nation='95' or nation='99','',if(intercode IN('01','02','03','11','12','13','14','15','16','17','18','21','22','23'),intercode,'23') ) as intercode, if(typelive='0','1',if(typelive='5','0',typelive)) as typelive,  IF(house.villcode IS NOT NULL,house.villcode,'') AS villcode,  IF(length(occupa)<>3,'',occupa) AS mapoccupation,  IF(cnation.mapnation IS NOT NULL AND TRIM(cnation.mapnation)<>'',cnation.mapnation,'') AS mapnation,  IF(cnationrace.mapnation IS NOT NULL AND TRIM(cnationrace.mapnation)<>'',cnationrace.mapnation,'') AS maporigin,  IF( person.dateupdate IS NULL OR TRIM(person.dateupdate)='' OR person.dateupdate LIKE '0000-00-00%','',DATE_FORMAT(person.dateupdate,'%Y%m%d%H%i%s') ) AS d_update ,if(length(trim(occupa))=4,occupa,if(o2.mapoccupa IS NULL OR length(trim(o2.mapoccupa))=0,'',o2.mapoccupa)) occupa_new ,pst.tp vstatus ,if(Ucase(bloodrh)='P','1',if(Ucase(bloodrh)='N','2','')) bloodrh ,ifnull(passpotnumber,'') passport ,IF(person.telephoneperson IS NULL,'',IF(LENGTH(trim(person.telephoneperson))>15,SUBSTRING(person.telephoneperson,15,1),person.telephoneperson)) telephone,IF(person.mobile IS NULL,'',IF(LENGTH(trim(person.mobile))>15,SUBSTRING(person.mobile,15,1),person.mobile)) mobile FROM person  LEFT JOIN house ON (person.pcucodeperson=house.pcucode AND person.hcode=house.hcode)  LEFT JOIN coccupa ON (person.occupa=coccupa.occupacode)  LEFT JOIN coccupa o2 ON (person.occupa=o2.occupacode)  LEFT JOIN cnation ON (person.nation=cnation.nationcode)  LEFT JOIN cnation cnationrace ON (person.origin=cnationrace.nationcode)  INNER JOIN visitanc ON (person.pcucodeperson=visitanc.pcucodeperson AND person.pid=visitanc.pid)  LEFT JOIN (SELECT t.pcucodeperson ,t.pid,CASE WHEN t.typecode IN('01','02') THEN '1' WHEN t.typecode ='09' THEN '2'       WHEN t.typecode IN('11','12') THEN '4' WHEN t.typecode ='03' THEN '3'       WHEN t.typecode IS NULL THEN '' ELSE '5' END AS tp  FROM persontype t WHERE t.typecode<>'10' GROUP BY t.pid) AS pst  ON person.pcucodeperson = pst.pcucodeperson AND person.pid = pst.pid  WHERE  TRIM(person.pcucodeperson)<>'' AND person.pcucodeperson='04232' AND trim(person.fname)<>'เบิกวัสดุ'  AND (visitanc.datecheck >= '2010-01-01') AND (visitanc.datecheck BETWEEN '2018-04-01' AND '2018-04-30')  UNION DISTINCT SELECT DISTINCT  trim(person.pcucodeperson) AS pcucode, person.idcard, person.pid, person.hcode,  person.prename, person.fname, person.lname, person.sex,  IF(person.birth IS NULL OR TRIM(person.birth)='' OR person.birth LIKE '0000-00-00%','',DATE_FORMAT(person.birth,'%Y%m%d')) AS birth,  person.roadmoi, person.hnomoi,  if(length(trim(person.mumoi))=1,concat('0',trim(person.mumoi)),if(trim(person.mumoi)='' or mumoi is NULL,'00',mumoi)) as mumoi,  person.subdistcodemoi, person.distcodemoi, person.provcodemoi,  person.marystatus,  IF(person.origin IS NULL OR TRIM(person.origin)='','',person.origin) AS origin,  IF(person.nation IS NULL OR TRIM(person.nation)='','',person.nation) AS nation,person.religion,IF(person.educate IS NULL OR person.educate NOT IN('00','01','02','03','04','05','06','09'),'09',person.educate) educate , person.familyposition, person.fatherid, person.motherid, person.mateid,  IF(person.datein IS NULL OR TRIM(person.datein)='' OR person.datein LIKE '0000-00-00%','',DATE_FORMAT(person.datein,'%Y%m%d')) AS movein,  person.dischargetype AS dischar,  IF(person.dischargedate IS NULL OR TRIM(person.dischargedate)='' OR person.dischargedate LIKE '0000-00-00%','',DATE_FORMAT(person.dischargedate,'%Y%m%d')) AS ddisch,  concat(ifnull(bloodgroup,''),ifnull(bloodrh,'')) as bloodgroup , if(nation='94' or nation='95' or nation='99','',if(intercode IN('01','02','03','11','12','13','14','15','16','17','18','21','22','23'),intercode,'23') ) as intercode, if(typelive='0','1',if(typelive='5','0',typelive)) as typelive,  IF(house.villcode IS NOT NULL,house.villcode,'') AS villcode,  IF(length(occupa)<>3,'',occupa) AS mapoccupation,  IF(cnation.mapnation IS NOT NULL AND TRIM(cnation.mapnation)<>'',cnation.mapnation,'') AS mapnation,  IF(cnationrace.mapnation IS NOT NULL AND TRIM(cnationrace.mapnation)<>'',cnationrace.mapnation,'') AS maporigin,  IF( person.dateupdate IS NULL OR TRIM(person.dateupdate)='' OR person.dateupdate LIKE '0000-00-00%','',DATE_FORMAT(person.dateupdate,'%Y%m%d%H%i%s') ) AS d_update ,if(length(trim(occupa))=4,occupa,if(o2.mapoccupa IS NULL OR length(trim(o2.mapoccupa))=0,'',o2.mapoccupa)) occupa_new ,pst.tp vstatus ,if(Ucase(bloodrh)='P','1',if(Ucase(bloodrh)='N','2','')) bloodrh ,ifnull(passpotnumber,'') passport ,IF(person.telephoneperson IS NULL,'',IF(LENGTH(trim(person.telephoneperson))>15,SUBSTRING(person.telephoneperson,15,1),person.telephoneperson)) telephone,IF(person.mobile IS NULL,'',IF(LENGTH(trim(person.mobile))>15,SUBSTRING(person.mobile,15,1),person.mobile)) mobile FROM person  LEFT JOIN house ON (person.pcucodeperson=house.pcucode AND person.hcode=house.hcode)  LEFT JOIN coccupa ON (person.occupa=coccupa.occupacode)  LEFT JOIN coccupa o2 ON (person.occupa=o2.occupacode)  LEFT JOIN cnation ON (person.nation=cnation.nationcode)  LEFT JOIN cnation cnationrace ON (person.origin=cnationrace.nationcode)  LEFT JOIN persondeath pd ON person.pcucodeperson=pd.pcucodeperson AND person.pid=pd.pid  LEFT JOIN (SELECT t.pcucodeperson ,t.pid,CASE WHEN t.typecode IN('01','02') THEN '1' WHEN t.typecode ='09' THEN '2'       WHEN t.typecode IN('11','12') THEN '4' WHEN t.typecode ='03' THEN '3'       WHEN t.typecode IS NULL THEN '' ELSE '5' END AS tp  FROM persontype t WHERE t.typecode<>'10' GROUP BY t.pid) AS pst  ON person.pcucodeperson = pst.pcucodeperson AND person.pid = pst.pid  WHERE TRIM(person.pcucodeperson)<>'' AND person.pcucodeperson='04232'  AND trim(person.fname)<>'เบิกวัสดุ'  AND DATE(person.dateupdate) BETWEEN '2018-04-01' AND '2018-04-30'  AND pd.pid IS NULL