สมัครสมาชิก

รายงานประกันสังคม

Administrator
โพสต์: 10
ลงทะเบียนเมื่อ: พุธ 09 ก.ค. 2014 10:41 am

รายงานประกันสังคม

โพสต์โดย admin » อังคาร 22 ก.ค. 2014 9:15 am

อาจารย์ลีโอ, chumsin, iamleo
พฤหัสฯ. 13 ก.พ. 2014 2:15 pm
โพสต์: 1630
โค้ด: เลือกทั้งหมด
SELECT query1.dateVisit AS dateVisit,query1.visit_hn AS HN,query1.visit_vn AS VN, query1.patient_prefix_description || '   ' || query1.patient_firstname || '   ' || query1.patient_lastname  AS  NAME, query1.patient_pid AS PID, query1.patient_birthday AS  BIRTHDAY
, query1.employee_number AS DOCTOR
, query1.diag_icd10_number AS ICD10
,SUM(CASE WHEN TRIM(query1.f_item_billing_group_id) = '05'
   THEN query1.totalPrice
   ELSE 0.00
END) AS DRUG_SUPPLY
,SUM(CASE WHEN TRIM(query1.f_item_billing_group_id) = '00'
   THEN query1.totalPrice
   ELSE 0.00
END) AS LAB
,SUM(CASE WHEN TRIM(query1.f_item_billing_group_id) = '01'
   THEN query1.totalPrice
   ELSE 0.00
END) AS XRAY
,SUM(CASE WHEN TRIM(query1.f_item_billing_group_id) = '02'
   THEN query1.totalPrice
   ELSE 0.00
END) AS OTHER
,SUM(CASE WHEN TRIM(query1.f_item_billing_group_id) = '03'
   THEN query1.totalPrice
END) AS OPERATING
,SUM(CASE WHEN TRIM(query1.f_item_billing_group_id) = '04'
   THEN query1.totalPrice
   ELSE 0.00
END) AS OTHER_TREAT
,SUM(CASE WHEN TRIM(query1.f_item_billing_group_id) = '06'
   THEN query1.totalPrice
   ELSE 0.00
END) AS ICU
,SUM(CASE WHEN TRIM(query1.f_item_billing_group_id) = '07'
   THEN query1.totalPrice
   ELSE 0.00
END) AS ROOM
,SUM(CASE WHEN TRIM(query1.f_item_billing_group_id) = '08'
   THEN query1.totalPrice
   ELSE 0.00
END) AS FOOD
,SUM(CASE WHEN TRIM(query1.f_item_billing_group_id) = '09'
   THEN query1.totalPrice
  ELSE 0.00
END) AS OTHER_PAID
,SUM(query1.totalPrice) AS TOTAL_PRICE
,query1.mainhos
FROM
(SELECT t_visit.f_visit_status_id
  , t_visit_payment.visit_payment_priority
  , t_order.f_order_status_id
  , t_visit.t_visit_id
  , t_visit.visit_hn
  , t_visit.visit_vn
  , f_patient_prefix.patient_prefix_description
  , t_patient.patient_firstname
  , t_patient.patient_lastname
  , t_patient.patient_pid
  , t_patient.patient_birthday
  , b_employee.employee_number
  , t_diag_icd10.diag_icd10_number
  , t_order.t_order_id
  , t_order.order_price
  , t_order.order_qty
  ,(t_order.order_price * t_order.order_qty) AS totalPrice
  , f_item_billing_group.f_item_billing_group_id AS f_item_billing_group_id
  , b_contract_plans.contract_plans_description AS contract_plans_description
  , SUBSTRING(t_visit.visit_financial_discharge_time,0,11) AS dateVisit
,t_visit_payment.visit_payment_main_hospital as mainhos
FROM
  t_visit INNER JOIN t_patient
   ON (t_visit.t_patient_id = t_patient.t_patient_id AND t_visit.f_visit_status_id <> '4')
  INNER JOIN f_patient_prefix
   ON t_patient.f_patient_prefix_id = f_patient_prefix.f_patient_prefix_id
  INNER JOIN t_visit_payment
   ON (t_visit.t_visit_id = t_visit_payment.t_visit_id AND t_visit_payment.visit_payment_priority = '0')
  INNER JOIN b_contract_plans
   ON t_visit_payment.b_contract_plans_id = b_contract_plans.b_contract_plans_id
  INNER JOIN t_diag_icd10
   ON (t_visit.t_visit_id = t_diag_icd10.diag_icd10_vn AND t_diag_icd10.f_diag_icd10_type_id = '1')
  INNER JOIN b_employee
   ON b_employee.b_employee_id = t_diag_icd10.diag_icd10_staff_doctor
  INNER JOIN t_order
   ON (t_visit.t_visit_id = t_order.t_visit_id
    AND t_order.f_order_status_id <> '0'
    AND t_order.f_order_status_id <> '3')   
  INNER JOIN b_item_billing_subgroup
   ON t_order.b_item_billing_subgroup_id = b_item_billing_subgroup.b_item_billing_subgroup_id
  INNER JOIN f_item_billing_group
   ON b_item_billing_subgroup.f_item_billing_group_id = f_item_billing_group.f_item_billing_group_id
WHERE
  t_visit.f_visit_status_id <>'4'
and t_visit.f_visit_type_id='0'
  AND b_contract_plans.b_contract_plans_id in  ('2120000000003','2120000000006','212114043855340804','2120000000004')
  AND ((t_order.f_order_status_id <> '0')  AND (t_order.f_order_status_id <> '3') )
    AND substring(t_visit.visit_financial_discharge_time,1,10)   Between substr('2556-09-24',1,10) And substr('2556-09-25',1,10)
) AS query1
GROUP BY
query1.dateVisit,query1.visit_hn ,query1.visit_vn
,query1.patient_prefix_description , query1.patient_firstname, query1.patient_lastname
, query1.patient_pid , query1.patient_birthday, query1.employee_number
, query1.diag_icd10_number
,query1.mainhos
ORDER BY
query1.dateVisit,query1.visit_hn;

Administrator
โพสต์: 10
ลงทะเบียนเมื่อ: พุธ 09 ก.ค. 2014 10:41 am

Re: รายงานประกันสังคม cr : อาจารย์ลีโอ

โพสต์โดย admin » อังคาร 22 ก.ค. 2014 9:19 am

อีกแบบนึงของอาจารย์ "SP"
ศุกร์ 21 ก.ย. 2007 10:23 am
โพสต์: 443
โค้ด: เลือกทั้งหมด
SELECT substring(visit_begin_visit_time,1,10) AS วันที่รับบริการ, t_visit.visit_hn AS HN, t_visit.visit_vn AS Vn, t_visit.f_visit_type_id AS Type_id, '' ||patient_prefix_description || '' ||patient_firstname || ' ' || patient_lastname AS ชื่อ_สกุล, t_patient.patient_pid AS ID, t_visit_payment.visit_payment_card_number AS Card_ID, b_contract_plans.contract_plans_description AS Plans,t_visit.visit_dx AS DX, t_diag_icd10.diag_icd10_number AS ICD10,b_item_billing_subgroup.item_billing_subgroup_description AS Billing_group, t_billing_invoice_billing_subgroup.billing_invoice_billing_subgroup_total AS ราคา
FROM (f_patient_prefix INNER JOIN (t_patient INNER JOIN ((b_contract_plans INNER JOIN (t_visit INNER JOIN t_visit_payment ON t_visit.t_visit_id = t_visit_payment.t_visit_id) ON b_contract_plans.b_contract_plans_id = t_visit_payment.b_contract_plans_id) INNER JOIN (t_billing_invoice_billing_subgroup INNER JOIN b_item_billing_subgroup ON t_billing_invoice_billing_subgroup.b_item_billing_subgroup_id = b_item_billing_subgroup.b_item_billing_subgroup_id) ON t_visit_payment.t_visit_id = t_billing_invoice_billing_subgroup.t_visit_id) ON t_patient.t_patient_id = t_visit.t_patient_id) ON f_patient_prefix.f_patient_prefix_id = t_patient.f_patient_prefix_id) INNER JOIN t_diag_icd10 ON t_visit.t_visit_id = t_diag_icd10.diag_icd10_vn
WHERE (t_visit.visit_begin_visit_time) >= ?
AND (t_visit.visit_begin_visit_time) <= ?
GROUP BY t_visit.visit_begin_visit_time, t_visit.visit_hn,t_visit.visit_dx, t_visit.visit_vn, t_visit.f_visit_type_id, f_patient_prefix.patient_prefix_description,t_patient.patient_firstname,t_patient.patient_lastname, t_patient.patient_pid, t_visit_payment.visit_payment_card_number, b_contract_plans.contract_plans_description, b_item_billing_subgroup.item_billing_subgroup_description, t_billing_invoice_billing_subgroup.billing_invoice_billing_subgroup_total, t_diag_icd10.diag_icd10_number
HAVING (((b_contract_plans.contract_plans_description) Like '%ประกัน%'))
ORDER BY t_visit.visit_begin_visit_time;

ผู้เริ่มต้น
โพสต์: 4
ลงทะเบียนเมื่อ: พุธ 09 ก.ค. 2014 3:50 pm

Re: รายงานประกันสังคม

โพสต์โดย Nankuung » ศุกร์ 25 ก.ค. 2014 11:06 am

ขอบคุณครับ

ย้อนกลับไปยัง รายงาน Hospital OS

ผู้ใช้งานขณะนี้

กำลังดูบอร์ดนี้: ไม่มีสมาชิกใหม่ และ บุคคลทั่วไป 1 ท่าน

Fatal: Not able to open ./cache/data_global.php