SELECT count(DISTINCT b.t_patient_appointment_id) as t_p FROM t_visit v join t_patient_appointment b on b.t_visit_id=v.t_visit_id WHERE LEFT ( v.visit_begin_visit_time, 10 )>='' AND LEFT ( v.visit_begin_visit_time, 10 )<='' AND v.f_visit_status_id <>'4'

SELECT count(DISTINCT b.t_visit_diag_map_id) as t_p FROM t_visit v join t_visit_diag_map b on b.t_visit_id=v.t_visit_id left outer join b_template_dx c on c.b_template_dx_id=b.b_template_dx_id WHERE LEFT ( v.visit_begin_visit_time, 10 )>='' AND LEFT ( v.visit_begin_visit_time, 10 )<='' AND v.f_visit_status_id <>'4' AND b.visit_diag_map_active='1'

SELECT v.t_visit_id, concat(LEFT(v.visit_vn,3),RIGHT(v.visit_vn,6)) as an, REPLACE ( pdx.diag_icd10_number, '.', '' ) AS pdx, v.visit_hn AS hn, p.f_sex_id AS sex, v.visit_patient_age AS age_y, pyn.b_contract_plans_id AS pttype, SUM(r.billing_total) AS income, CAST (NULLIF(LEFT(v.visit_begin_admit_date_time,4),'') AS INTEGER)-543 || substr(v.visit_begin_admit_date_time, 5, 6) AS regdate, CASE WHEN length(v.visit_staff_doctor_discharge_date_time)=19 THEN CAST (NULLIF(LEFT(v.visit_staff_doctor_discharge_date_time,4),'') AS INTEGER)-543 || substr(v.visit_staff_doctor_discharge_date_time, 5, 6) ELSE NULL END AS dchdate, NULL AS admdate, COALESCE ( SUM( r.billing_patient_share ), 2 ) AS paid_money, COALESCE ( SUM( r.billing_remain ), 2 ) AS remain_money, COALESCE ( SUM( r.billing_payer_share ), 2 ) AS uc_money, COALESCE ( SUM( r.billing_total ), 2 ) AS item_money, CASE WHEN pyn.contract_plans_pttype='OFC' THEN 'A2' ELSE pyn.contract_plans_pttype END as pcode, v.visit_vn AS vn, py.visit_payment_card_number AS pttypeno, COALESCE ( SUM( r.billing_paid ), 0 ) AS rcpt_money, COALESCE ( SUM( r.billing_deduct ), 0 ) AS discount_money , v.f_visit_status_id, s.visit_status_description FROM t_visit v LEFT OUTER JOIN ( SELECT diag_icd10_vn, diag_icd10_number FROM t_diag_icd10 WHERE f_diag_icd10_type_id = '1' AND diag_icd10_active = '1' ) pdx ON pdx.diag_icd10_vn = v.t_visit_id LEFT OUTER JOIN t_patient p ON p.patient_hn = v.visit_hn LEFT OUTER JOIN f_patient_prefix pr ON pr.f_patient_prefix_id = p.f_patient_prefix_id LEFT OUTER JOIN t_billing_invoice rv ON rv.t_visit_id = v.t_visit_id LEFT OUTER JOIN ( SELECT * FROM t_visit_payment WHERE visit_payment_active = '1' AND visit_payment_priority='0' ) py ON py.t_visit_id = rv.t_visit_id AND py.t_visit_payment_id = rv.t_payment_id LEFT OUTER JOIN b_contract_plans pyn ON pyn.b_contract_plans_id = py.b_contract_plans_id LEFT OUTER JOIN t_billing r ON r.t_visit_id = v.t_visit_id AND rv.t_billing_id = r.t_billing_id LEFT OUTER JOIN f_visit_status s ON s.f_visit_status_id = v.f_visit_status_id WHERE v.f_visit_type_id = '1' /*0=OPD 1=IPD*/ AND v.f_visit_status_id <>'4' AND pyn.contract_plans_active = '1' AND py.visit_payment_active = '1' AND r.billing_active = '1' AND rv.billing_invoice_active = '1' AND rv.billing_invoice_complete = '1' AND LEFT ( v.visit_begin_admit_date_time, 10 )>='' AND LEFT ( v.visit_begin_admit_date_time, 10 )<='' GROUP BY v.visit_an, v.t_visit_id, pdx.diag_icd10_number, p.f_sex_id, pyn.b_contract_plans_id, py.visit_payment_card_number, s.visit_status_description

SELECT b_service_point_id as spclty, service_point_description as name FROM b_service_point

SELECT e.b_employee_id as code, concat(p.person_firstname,' ',p.person_lastname ) as name, CASE WHEN e.employee_number IS NULL THEN p.person_pid WHEN trim(e.employee_number) ='' THEN p.person_pid WHEN pe.description like 'แพท%' AND left(trim(e.employee_number),1) BETWEEN '1' and '9' THEN concat('ว',trim(e.employee_number)) WHEN pe.description like '%ทันตแพท%' AND left(trim(e.employee_number),1) BETWEEN '1' and '9' THEN concat('ท',trim(e.employee_number)) ELSE e.employee_number END as licenseno, e.f_provider_type_id as position_id FROM t_person p left outer join b_employee e on e.t_person_id=p.t_person_id LEFT outer JOIN f_provider_type pe on pe.f_provider_type_id=e.f_provider_type_id WHERE e.t_person_id IS NOT NULL;

SELECT b_item_billing_subgroup_id AS income, item_billing_subgroup_description AS name FROM b_item_billing_subgroup

SELECT b_item.b_item_id as icode, b_item.item_common_name as name, c.item_price as price, CASE WHEN dd.b_item_id IS NOT NULL AND TRIM(dd.drug_unit_packing)='' and b_item.item_common_name LIKE '%mg%/%ml%' THEN 'ขวด' WHEN dd.b_item_id IS NOT NULL AND TRIM(dd.drug_unit_packing)='' and b_item.item_common_name LIKE '%กรัม%' THEN 'ซอง' WHEN dd.b_item_id IS NOT NULL AND TRIM(dd.drug_unit_packing)='' and b_item.item_common_name LIKE '%mg%' THEN 'tab' WHEN dd.b_item_id IS NOT NULL AND TRIM(dd.drug_unit_packing)='' and b_item.item_common_name LIKE '%ml%' THEN 'ขวด' WHEN dd.b_item_id IS NOT NULL AND TRIM(dd.drug_unit_packing)='' and b_item.item_common_name LIKE '%vial%' THEN 'Vial' WHEN dd.b_item_id IS NOT NULL AND TRIM(dd.drug_unit_packing)='' and b_item.item_common_name LIKE '%amp%' THEN 'Amp' WHEN dd.b_item_id IS NOT NULL AND (RIGHT(TRIM(dd.drug_unit_packing),1) between '1' and '9' ) and b_item.item_common_name LIKE '%mg%/%ml%' THEN 'ขวด' WHEN dd.b_item_id IS NOT NULL AND (RIGHT(TRIM(dd.drug_unit_packing),1) between '1' and '9' ) and b_item.item_common_name LIKE '%กรัม%' THEN 'ซอง' WHEN dd.b_item_id IS NOT NULL AND (RIGHT(TRIM(dd.drug_unit_packing),1) between '1' and '9' ) and b_item.item_common_name LIKE '%mg%' THEN 'tab' WHEN dd.b_item_id IS NOT NULL AND (RIGHT(TRIM(dd.drug_unit_packing),1) between '1' and '9' ) and b_item.item_common_name LIKE '%ml%' THEN 'ขวด' WHEN dd.b_item_id IS NOT NULL AND (RIGHT(TRIM(dd.drug_unit_packing),1) between '1' and '9' ) and b_item.item_common_name LIKE '%vial%' THEN 'Vial' WHEN dd.b_item_id IS NOT NULL AND (RIGHT(TRIM(dd.drug_unit_packing),1) between '1' and '9' ) and b_item.item_common_name LIKE '%amp%' THEN 'Amp' WHEN dd.b_item_id IS NOT NULL AND TRIM(dd.drug_unit_packing)<>'' THEN TRIM(dd.drug_unit_packing) WHEN dd.b_item_id IS NULL THEN NULL ELSE 'non' END as unit, b_item.b_item_billing_subgroup_id as income, CASE WHEN b_item.item_active = '1' THEN 'Y' WHEN b_item.item_active = '0' THEN 'N' END as istatus FROM b_item LEFT OUTER JOIN ( select max(b_item_price_id) as max_b_item_price_id,b_item_id from b_item_price group by b_item_id ) b on b.b_item_id=b_item.b_item_id LEFT OUTER JOIN b_item_price c on c.b_item_price_id=b.max_b_item_price_id and c.b_item_id=b.b_item_id LEFT OUTER JOIN b_item_drug dd on dd.b_item_id=b_item.b_item_id

SELECT md5(v.visit_vn||v.visit_hn||string_agg(s.visit_primary_symptom_main_symptom, ',')||string_agg(s.visit_primary_symptom_current_illness, ',')) as hos_guid, v.visit_vn as vn, v.visit_hn AS hn, LEFT ( v.visit_begin_visit_time, 10 ) AS vstdate, RIGHT ( v.visit_begin_visit_time, 8 ) AS vsttime , string_agg( CASE WHEN coalesce(s.visit_primary_symptom_main_symptom, '') = '' THEN '-' WHEN coalesce(s.visit_primary_symptom_main_symptom, '') <> '' THEN s.visit_primary_symptom_main_symptom END, ',') as cc, string_agg( CASE WHEN coalesce(s.visit_primary_symptom_current_illness, '') = '' THEN '-' WHEN coalesce(s.visit_primary_symptom_current_illness, '') <> '' THEN s.visit_primary_symptom_current_illness END, ',') as hpi FROM t_visit v LEFT OUTER JOIN t_visit_primary_symptom s on s.t_visit_id = v.t_visit_id WHERE s.visit_primary_symptom_active='1' AND v.f_visit_status_id <>'4' AND LEFT ( v.visit_begin_visit_time, 10 )>='' AND LEFT ( v.visit_begin_visit_time, 10 )<='' GROUP BY v.t_visit_id ORDER BY v.visit_vn ASC

SELECT md5(o.t_order_id) AS hos_guid, CASE WHEN v.visit_an IS NULL OR v.visit_an = '' THEN v.visit_vn ELSE NULL END AS vn, v.visit_hn AS hn, CASE WHEN v.f_visit_type_id = '1' THEN concat(LEFT(v.visit_vn,3),RIGHT(v.visit_vn,6)) ELSE NULL END AS an, o.b_item_id AS icode, o.order_qty AS qty, du.b_item_drug_frequency_id AS drugusage, NULL AS idr, NULL AS iperday, NULL AS iperdose, NULL AS recetime, o.order_price AS unitprice, CAST (NULLIF(LEFT(v.visit_begin_visit_time,4),'') AS INTEGER)-543 || substr(v.visit_begin_visit_time, 5, 6) AS vstdate, RIGHT ( v.visit_begin_visit_time, 8 ) AS vsttime, o.order_staff_order AS doctor, CAST (NULLIF(LEFT(o.order_date_time,4),'') AS INTEGER)-543 || substr(o.order_date_time, 5, 6) AS rxdate, RIGHT ( o.order_date_time, 8 ) AS rxtime, NULL AS sp_use, NULL AS hcode, NULL AS print, NULL AS dep_code, oi.t_billing_invoice_item_id AS finance_number, NULL AS discount, NULL AS use_right, NULL AS node_id, NULL AS order_no, NULL AS sub_type, op.b_contract_plans_id AS pttype, b_item_billing_subgroup_id AS income, NULL AS remain, NULL AS item_type, Null AS staff, NULL AS doctor_lock, CASE WHEN op.b_contract_id = '0000000000000' THEN '01' ELSE '02' END AS paidst, NULL AS item_no, NULL AS last_modified, ( o.order_price * o.order_qty ) AS sum_price, ( o.order_cost :: FLOAT8 * o.order_qty ) AS cost, NULL AS stock_department_id, NULL AS command_doctor, NULL AS opi_doctor_finance_type_id FROM t_order o LEFT OUTER JOIN t_visit v ON v.t_visit_id = o.t_visit_id LEFT OUTER JOIN t_order_drug du ON du.t_order_id= o.t_order_id and du.b_item_id=o.b_item_id LEFT OUTER JOIN t_billing_receipt_item oi ON oi.t_visit_id = o.t_visit_id AND oi.b_item_id = o.b_item_id LEFT OUTER JOIN t_visit_payment op ON op.t_visit_id = o.t_visit_id AND op.visit_payment_active = '1' WHERE LEFT ( v.visit_begin_visit_time, 10 )>='' AND LEFT ( v.visit_begin_visit_time, 10 )<='' AND v.f_visit_status_id <>'4' GROUP BY v.f_visit_type_id, v.visit_vn, v.visit_hn, v.visit_an, o.order_qty, du.b_item_drug_frequency_id, o.order_price, o.order_staff_order, o.order_date_time, o.b_item_billing_subgroup_id, o.order_cost, v.visit_begin_visit_time, oi.t_billing_invoice_item_id, op.b_contract_plans_id, o.t_order_id, o.b_item_id, op.b_contract_id

SELECT v.visit_vn as vn, v.visit_hn AS hn, CASE WHEN v.f_visit_type_id = '1' THEN concat(LEFT(v.visit_vn,3),RIGHT(v.visit_vn,6)) ELSE NULL END AS an, CAST (NULLIF(LEFT(v.visit_begin_visit_time,4),'') AS INTEGER)-543 || substr(v.visit_begin_visit_time, 5, 6) AS vstdate, RIGHT ( v.visit_begin_visit_time, 8 ) AS vsttime , py.visit_payment_main_hospital AS hospmain, py.visit_payment_sub_hospital AS hospsub, v.visit_queue as oqueue, pyn.b_contract_plans_id AS pttype, py.visit_payment_card_number AS pttypeno, v.visit_dx as diag_text, v.visit_modify_staff as staff, v.f_visit_status_id, s.visit_status_description, sv.b_service_point_id , v.f_visit_opd_discharge_status_id as ovstost FROM t_visit v LEFT OUTER JOIN ( SELECT diag_icd10_vn, diag_icd10_number FROM t_diag_icd10 WHERE f_diag_icd10_type_id = '1' AND diag_icd10_active = '1' ) pdx ON pdx.diag_icd10_vn = v.t_visit_id LEFT OUTER JOIN t_patient p ON p.patient_hn = v.visit_hn LEFT OUTER JOIN f_patient_prefix pr ON pr.f_patient_prefix_id = p.f_patient_prefix_id LEFT OUTER JOIN ( SELECT * FROM t_visit_payment WHERE visit_payment_active = '1' AND visit_payment_priority='0' ) py ON py.t_visit_id = v.t_visit_id LEFT OUTER JOIN b_contract_plans pyn ON pyn.b_contract_plans_id = py.b_contract_plans_id LEFT OUTER JOIN t_billing r ON r.t_visit_id = v.t_visit_id LEFT OUTER JOIN t_visit_service sv ON sv.t_visit_id = v.t_visit_id LEFT OUTER JOIN f_visit_status s ON s.f_visit_status_id = v.f_visit_status_id WHERE LEFT ( v.visit_begin_visit_time, 10 )>='' AND LEFT ( v.visit_begin_visit_time, 10 )<='' AND v.f_visit_status_id <>'4' GROUP BY v.visit_vn, v.visit_hn, v.f_visit_type_id, v.visit_begin_visit_time, py.visit_payment_main_hospital, py.visit_payment_sub_hospital, v.visit_queue, pyn.b_contract_plans_id, py.visit_payment_card_number, v.visit_dx, v.visit_modify_staff, v.f_visit_status_id, s.visit_status_description, sv.b_service_point_id, v.f_visit_opd_discharge_status_id

SELECT p.patient_hn as hn, pr.patient_prefix_description as pname, REPLACE(p.patient_firstname,'$string','') AS fname, REPLACE(p.patient_lastname,'$string','') AS lname, p.f_patient_occupation_id as occupation, p.f_patient_race_id as citizenship, p.patient_birthday as birthday, REPLACE(p.patient_house,'$string','') as addrpart, p.patient_moo as moopart, RIGHT(p.patient_tambon,2) AS tmbpart, substr(p.patient_amphur,3,2) AS amppart, LEFT(p.patient_changwat,2) AS chwpart, p.f_patient_blood_group_id as bloodgrp, /* CASE WHEN p.f_patient_discharge_status_id='1' THEN CAST (NULLIF(LEFT(patient_discharge_date_time,4),'') AS INTEGER)-543 || substr(patient_discharge_date_time, 5, 6) ELSE NULL END as deathday, */ LEFT(patient_discharge_date_time,10) as deathday, p.f_patient_marriage_status_id as marrystatus, p.patient_hn as hn_int, p.f_patient_nation_id as nationality, p.f_patient_religion_id as religion, p.f_sex_id as sex, p.patient_pid as cid, p.f_patient_education_type_id as educate, CASE WHEN p.f_patient_discharge_status_id='1' THEN 'Y' ELSE 'N' END AS death FROM t_patient p LEFT OUTER JOIN f_patient_prefix pr ON pr.f_patient_prefix_id = p.f_patient_prefix_id

SELECT b_contract_plans_id as pttype, contract_plans_description as name, CASE WHEN contract_plans_active='1' THEN 'Y' ELSE 'N' END as isuse, CASE WHEN contract_plans_pttype='OFC' THEN 'A2' ELSE contract_plans_pttype END as pcode FROM b_contract_plans

SELECT b.t_billing_id as arrear_id, CASE WHEN v.f_visit_type_id = '1' THEN concat(LEFT(v.visit_vn,3),RIGHT(v.visit_vn,6)) ELSE v.visit_vn END as vn, left(b.billing_billing_date_time,10) as arrear_date, right(b.billing_billing_date_time,8) as arrear_time, b.billing_remain as amount, b.billing_staff_record as staff, NULL as rcpno, NULL as finance_number, NULL as paid, NULL as pt_type, v.visit_hn as hn, NULL as receive_money_date, NULL as receive_money_time, NULL as receive_money_staff, NULL as hos_guid, concat(LEFT(v.visit_vn,3),RIGHT(v.visit_vn,6)) as an FROM t_billing b left join t_billing_receipt r on r.t_billing_id=b.t_billing_id left join t_visit v on v.t_visit_id=b.t_visit_id WHERE b.billing_remain>0 and (v.visit_vn<>'' or v.visit_vn IS NOT NULL) AND v.f_visit_status_id <>'4' GROUP BY b.t_billing_id, v.f_visit_type_id, v.visit_vn, v.visit_hn

SELECT r.t_billing_receipt_id as finance_number, r.billing_receipt_number as rcpno, r.billing_receipt_paid as bill_amount, REPLACE(r.billing_receipt_date_time,',',' ') as bill_date_time, v.visit_hn as hn, CASE WHEN v.f_visit_type_id = '1' THEN concat(LEFT(v.visit_vn,3),RIGHT(v.visit_vn,6)) ELSE v.visit_vn END AS vn, CASE WHEN v.f_visit_type_id = '1' THEN 'IPD' ELSE 'OPD' END AS department, op.b_contract_plans_id AS pttype, b.billing_remain as remain_money, b.billing_deduct as discount, b.billing_remain+r.billing_receipt_paid+b.billing_deduct as total_amount FROM t_billing b left join t_billing_receipt r on r.t_billing_id=b.t_billing_id left join t_visit v on v.t_visit_id=b.t_visit_id LEFT OUTER JOIN t_billing_invoice iv on iv.t_visit_id=b.t_visit_id LEFT OUTER JOIN t_visit_payment op ON op.t_visit_id = b.t_visit_id and op.t_visit_payment_id=iv.t_payment_id WHERE b.billing_paid>0 and b.billing_active='1' and (v.visit_vn<>'' or v.visit_vn IS NOT NULL) AND LEFT ( r.billing_receipt_date_time, 10 )>='' AND LEFT ( r.billing_receipt_date_time, 10 )<='' AND op.visit_payment_active='1' AND v.f_visit_status_id <>'4' AND r.billing_receipt_active='1' AND r.t_billing_invoice_ids::varchar = concat('{'::varchar, iv.t_billing_invoice_id::varchar, '}'::varchar) GROUP BY r.t_billing_receipt_id, b.billing_remain, b.billing_deduct, r.billing_receipt_number, r.billing_receipt_paid, r.billing_receipt_date_time, v.visit_hn, v.f_visit_type_id, v.visit_vn, op.b_contract_plans_id

SELECT v.visit_vn AS vn, v.visit_hn AS hn, REPLACE ( pdx.diag_icd10_number, '.', '' ) AS pdx, pdx.diag_icd10_staff_doctor as dx_doctor, p.f_sex_id AS sex, v.visit_patient_age AS age_y, pyn.b_contract_plans_id AS pttype, sum(coalesce(r.billing_total,0)) as income, sum(coalesce(r.billing_patient_share,0)) as paid_money, sum(coalesce(r.billing_remain,0)) as remain_money, sum(coalesce(r.billing_payer_share,0))as uc_money, sum(coalesce(r.billing_total,0)) as item_money, LEFT ( v.visit_begin_visit_time, 10 ) AS vstdate, CASE WHEN pyn.contract_plans_pttype='OFC' THEN 'A2' ELSE pyn.contract_plans_pttype END as pcode, py.visit_payment_main_hospital AS hospmain, py.visit_payment_sub_hospital AS hospsub, py.visit_payment_card_number AS pttypeno, py.visit_payment_card_expire_date AS pttype_expire, P.patient_pid AS cid, REPLACE ( pdx.diag_icd10_number, '.', '' ) AS main_pdx, LEFT ( v.visit_begin_visit_time, 7 ) AS ym, sum(coalesce(r.billing_paid,0)) as rcpt_money, sum(coalesce(r.billing_deduct,0)) as discount_money, v.f_visit_status_id, s.visit_status_description FROM t_visit v LEFT OUTER JOIN ( SELECT diag_icd10_vn, diag_icd10_number,diag_icd10_staff_doctor FROM t_diag_icd10 WHERE f_diag_icd10_type_id = '1' AND diag_icd10_active = '1' ) pdx ON pdx.diag_icd10_vn = v.t_visit_id LEFT OUTER JOIN t_patient P ON P.patient_hn = v.visit_hn LEFT OUTER JOIN f_patient_prefix pr ON pr.f_patient_prefix_id = P.f_patient_prefix_id LEFT OUTER JOIN ( SELECT * FROM t_visit_payment WHERE visit_payment_active = '1' AND visit_payment_priority='0' ) py ON py.t_visit_id = v.t_visit_id LEFT OUTER JOIN b_contract_plans pyn ON pyn.b_contract_plans_id = py.b_contract_plans_id LEFT OUTER JOIN t_billing r ON r.t_visit_id = v.t_visit_id LEFT OUTER JOIN f_visit_status s ON s.f_visit_status_id = v.f_visit_status_id WHERE v.f_visit_type_id = '0' /*0=OPD 1=IPD*/ AND v.f_visit_status_id <>'4' AND LEFT ( v.visit_begin_visit_time, 10 )>='' AND LEFT ( v.visit_begin_visit_time, 10 )<='' GROUP BY v.visit_vn, v.visit_hn, pdx, dx_doctor, sex, age_y, pttype, vstdate, pcode, hospmain, hospsub, pttypeno, pttype_expire, cid, main_pdx, ym, v.f_visit_status_id, s.visit_status_description, v.t_visit_id ORDER BY v.t_visit_id DESC