ทะเบียนหลังคลอด และการดูแลหลังคลอด

ทะเบียนหลังคลอด และการดูแล มารดา-ทารก หลังคลอด

Copy เอาไป รันใน SQL Querey เอานะครับ
เปลี่ยนวันกำหนดคลอดได้ที่บรรทัด
WHERE v.village_moo <> 0 AND p.death = "N" and a.edc > 20140930
โดยใช้รูปแบบ ปปปปดดวว

SELECT p.person_id,p.cid,CONCAT(p.pname,p.fname," ",p.lname) name_mother,
h.address,v.village_moo as moo ,Year(Now())-Year(p.birthdate) age,
CONCAT (substr(a.lmp,9,2),"-",substr(a.lmp,6,2),"-",substr(a.lmp,1,4)+543) lmp,
CONCAT (substr(a.edc,9,2),"-",substr(a.edc,6,2),"-",substr(a.edc,1,4)+543) edc,
CONCAT (substr(a.labor_date,9,2),"-",substr(a.labor_date,6,2),"-",substr(a.labor_date,1,4)+543) birth_baby,
DATEDIFF(( SELECT ac.care_date FROM person_anc_preg_care ac
LEFT JOIN person_anc aa on ac.person_anc_id = aa.person_anc_id
where aa.person_id = p.person_id and aa.person_anc_id = a.person_anc_id and
ac.preg_care_number = 1 order by ac.care_date desc LIMIT 0,1 ),labor_date) m_1,
( SELECT concat(substr(ac.care_date,9,2),"-",substr(ac.care_date,6,2),"-",substr(ac.care_date,1,4)+543)ac FROM person_anc_preg_care ac
LEFT JOIN person_anc aa on ac.person_anc_id = aa.person_anc_id
where aa.person_id = p.person_id and aa.person_anc_id = a.person_anc_id and
ac.preg_care_number = 1 order by ac.care_date desc LIMIT 0,1 ) mother_care_1,
DATEDIFF(( SELECT ac.care_date FROM person_anc_preg_care ac
LEFT JOIN person_anc aa on ac.person_anc_id = aa.person_anc_id
where aa.person_id = p.person_id and aa.person_anc_id = a.person_anc_id and
ac.preg_care_number = 2 order by ac.care_date desc LIMIT 0,1 ),labor_date) m_2,
(SELECT concat(substr(ac.care_date,9,2),"-",substr(ac.care_date,6,2),"-",substr(ac.care_date,1,4)+543) ac FROM person_anc_preg_care ac
LEFT JOIN person_anc aa on ac.person_anc_id = aa.person_anc_id
where aa.person_id = p.person_id and aa.person_anc_id = a.person_anc_id and
ac.preg_care_number = 2 order by ac.care_date desc LIMIT 0,1 ) mother_care_2,
DATEDIFF(( SELECT ac.care_date FROM person_anc_preg_care ac
LEFT JOIN person_anc aa on ac.person_anc_id = aa.person_anc_id
where aa.person_id = p.person_id and aa.person_anc_id = a.person_anc_id and
ac.preg_care_number = 3 order by ac.care_date desc LIMIT 0,1 ),labor_date) m_3,
(SELECT concat(substr(ac.care_date,9,2),"-",substr(ac.care_date,6,2),"-",substr(ac.care_date,1,4)+543) ac FROM person_anc_preg_care ac
LEFT JOIN person_anc aa on ac.person_anc_id = aa.person_anc_id
where aa.person_id = p.person_id and aa.person_anc_id = a.person_anc_id and
ac.preg_care_number = 3 order by ac.care_date desc LIMIT 0,1 ) mother_care_3,
concat(substr(a.force_complete_date,9,2),"-",substr(a.force_complete_date,6,2),"-",substr(a.force_complete_date,1,4)+543) mother_send_43f,
CONCAT(p2.pname,p2.fname," ",p2.lname) name_baby,
DATEDIFF(( SELECT pc.care_date FROM person_wbc_post_care pc
LEFT JOIN person_wbc pw on pc.person_wbc_id = pw.person_wbc_id
where pw.person_id = p2.person_id and pc.care_number= 1 order by pc.care_date desc LIMIT 0,1),labor_date) b_1,
( SELECT concat(substr( pc.care_date,9,2),"-",substr(pc.care_date,6,2),"-",substr(pc.care_date,1,4)+543)pc FROM person_wbc_post_care pc
LEFT JOIN person_wbc pw on pc.person_wbc_id = pw.person_wbc_id
where pw.person_id = p2.person_id and pc.care_number= 1 order by pc.care_date desc LIMIT 0,1 ) baby_care_1,
DATEDIFF(( SELECT pc.care_date FROM person_wbc_post_care pc
LEFT JOIN person_wbc pw on pc.person_wbc_id = pw.person_wbc_id
where pw.person_id = p2.person_id and pc.care_number= 2 order by pc.care_date desc LIMIT 0,1),labor_date) b_2,
( SELECT concat(substr( pc.care_date,9,2),"-",substr(pc.care_date,6,2),"-",substr(pc.care_date,1,4)+543)pc FROM person_wbc_post_care pc
LEFT JOIN person_wbc pw on pc.person_wbc_id = pw.person_wbc_id
where pw.person_id = p2.person_id and pc.care_number= 2 order by pc.care_date desc LIMIT 0,1 ) baby_care_2,
DATEDIFF(( SELECT pc.care_date FROM person_wbc_post_care pc
LEFT JOIN person_wbc pw on pc.person_wbc_id = pw.person_wbc_id
where pw.person_id = p2.person_id and pc.care_number= 3 order by pc.care_date desc LIMIT 0,1),labor_date) b_3,
( SELECT concat(substr( pc.care_date,9,2),"-",substr(pc.care_date,6,2),"-",substr(pc.care_date,1,4)+543)pc FROM person_wbc_post_care pc
LEFT JOIN person_wbc pw on pc.person_wbc_id = pw.person_wbc_id
where pw.person_id = p2.person_id and pc.care_number= 3 order by pc.care_date desc LIMIT 0,1 ) baby_care_3,
concat(substr(pw.force_complete_date,9,2),"-",substr(pw.force_complete_date,6,2),"-",substr(pw.force_complete_date,1,4)+543) baby_send_43f
FROM person p
left JOIN house h ON p.house_id = h.house_id
left JOIN village v ON v.village_id = h.village_id
LEFT JOIN person_anc a ON a.person_id = p.person_id
left JOIN person_anc_preg_care ac ON ac.person_anc_id = a.person_anc_id
LEFT JOIN person_anc_service ps ON ps.person_anc_id = a.person_anc_id
left OUTER JOIN person p2 ON p2.birthdate = a.labor_date and p2.mother_person_id = p.person_id
LEFT OUTER JOIN person_wbc pw on pw.person_id = p2.person_id
LEFT OUTER JOIN person_wbc_post_care pc ON pc.person_wbc_id = pw.person_wbc_id
WHERE v.village_moo <> 0 AND p.death = "N" and a.edc > 20140930
GROUP BY a.person_anc_id
ORDER BY a.lmp asc