<?php
class Observ extends MY_Controller
{
   var $db_onedev;
   public function index()
   {
      echo "Observ API";
   }

   public function __construct()
   {
      parent::__construct();
      $this->db_onedev = $this->load->database("onedev", true);
      $this->load->library('Satusehat');
   }
	

	function getdata(){
        $prm = $this->sys_input;
        $orderHeaderID = $prm['orderheaderID'];
        $results = array();
        $sql = "SELECT IFNULL(OHPatientMapIHSNumber, 'Belum terdaftar') as patient_ihsnumber,
                EncounterUUID as encounter_uuid,
                IFNULL(OHDoctorMapIHSNumber,'Belum diinput') as doctor_ihsnumber,
                fn_global_age_count_day(M_PatientDOB, date(T_OrderHeaderDate)) age_in_day,
                M_PatientM_SexID as sex_id
                FROM t_orderheader
                JOIN m_patient ON T_OrderHeaderM_PatientID = M_PatientID
                JOIN one_health.encounter ON EncounterT_orderHeaderID = T_orderHeaderID AND EncounterIsActive = 'Y'
                LEFT JOIN one_health.oh_doctor_map ON OHDoctorMapM_DoctorID = T_OrderHeaderPjM_DoctorID AND OHDoctorMapIsActive = 'Y'
                LEFT JOIN one_health.oh_patient_map ON OHPatientMapM_PatientID = T_OrderHeaderM_PatientID AND OHPatientMapIsActive = 'Y'
                WHERE
                T_OrderHeaderID = ?";
        $query = $this->db_onedev->query($sql,array($orderHeaderID));
		if(!$query){
            //echo $this->db_onedev->last_query();
			$this->sys_error("error get t_orderheader");
			exit;
		}

		$r_order = $query->row_array();

		$sql = "SELECT t_orderdetail.*, Group_ResultID, Group_ResultName, 
                IFNULL(Nat_TestLoincCode,'') as code_loinc,
                IFNULL(Nat_TestLoincDisplay,'') as display_loinc,
                Nat_TestIsQuantitative as isQuantitatif,
                Nat_TestID,
                T_OrderDetailAddOnNormalFlag as code_interpretation,
                IF(T_OrderDetailAddOnNormalFlag = 'N','Normal',IF(T_OrderDetailAddOnNormalFlag = 'L','Low','High')) as display_interpretation
                FROM t_orderdetail
                JOIN t_test ON T_OrderDetailT_TestID = T_TestID AND T_TestID in (1069,1070,1071)
                JOIN t_orderdetailaddon ON T_OrderDetailAddOnT_OrderDetailID = T_OrderDetailID
                JOIN nat_test ON T_TestNat_TestID = Nat_TestID AND Nat_TestIsActive = 'Y'
                JOIN group_resultdetail ON Group_ResultDetailT_TestID = T_TestID AND Group_ResultDetailIsActive = 'Y'
                JOIN group_result ON Group_ResultDetailGroup_ResultID = Group_ResultID AND Group_ResultFlagNonLab = 'N'
                LEFT JOIN nat_test_loinc ON Nat_TestLoincNat_TestID = T_TestNat_TestID 
                WHERE
                T_OrderDetailT_OrderHeaderID = ? AND 
                T_OrderDetailIsActive = 'Y' AND 
                T_OrderDetailT_TestIsResult = 'Y'
                ORDER BY T_OrderDetailT_TestSasCode ASC";
		$query = $this->db_onedev->query($sql,array($orderHeaderID));
		if(!$query){
            // echo $this->db_onedev->last_query();
			$this->sys_error("error get t_orderdetail");
			exit;
		}

		$r_details = $query->result_array();
        if($r_details){
            $sql = "SELECT *
                    FROM one_health.organization 
                    JOIN m_branch ON organizationM_BranchID = M_BranchID AND M_BranchIsDefault = 'Y' AND M_BranchIsActive = 'Y'
                    WHERE
                    organizationIsActive = 'Y'";
            $query = $this->db_onedev->query($sql);
            if(!$query){
                //echo $this->db_onedev->last_query();
                $this->sys_error("error get m_branch");
                exit;
            }

            $r_organization = $query->row_array();
            $organizationID = $r_organization['organizationID'];

            $data_results = [];
            foreach ($r_details as $key => $value) {
                $observation_uuid = $this->satusehat->gen_uuid();
                $data_result = [];
                $result["fullUrl"] = "urn:uuid:".$observation_uuid;
                $data_result['resourceType'] = "Observation";
                $data_result['identifier'] = array(
                    array(
                        "system" => "http://sys-ids.kemkes.go.id/observation/".$organizationID,
                        "value" => $value['T_OrderDetailID']
                    )
                );
                $data_result['status'] = "final";
                $data_result['category'] = array(
                    array(
                        "coding" => array(
                            array(
                                "system" => "http://terminology.hl7.org/CodeSystem/observation-category",
                                "code" => "laboratory",
                                "display" => "Laboratory"
                            )
                        )
                    )
                );

                $data_result['code'] = array(
                    "coding" => array(
                        array(
                            "system" => "http://loinc.org",
                            "code" => $value['code_loinc'],
                            "display" => $value['display_loinc']
                        )
                    )
                );
                $data_result['subject'] = array(
                    "reference" => "Patient/".$r_order['patient_ihsnumber']
                );
                $data_result['encounter'] = array(
                    'reference' => "Encounter/".$r_order['encounter_uuid']
                );

                $data_result['effectiveDateTime'] = date('c', strtotime($value['T_OrderDetailVerDate']));
                $data_result['issued'] = date('c', strtotime($value['T_OrderDetailValDate']));
                $data_result['performer'] = array(
                    array(
                        'reference' => "Practitioner/".$r_order['doctor_ihsnumber']
                    ),
                    array(
                        'reference' => "Organization/".$organizationID
                    )
                );

                $sql = "SELECT SpecimenUUID
                        FROM t_ordersample
                        JOIN t_test ON T_OrderSampleT_SampleTypeID = T_TestT_SampleTypeID AND 
                        T_TestID = ?
                        JOIN one_health.specimen ON SpecimentT_OrderSampleID = T_OrderSampleID AND
                        SpecimenIsActive = 'Y'
                        WHERE
                            T_OrderSampleT_OrderHeaderID = ? AND 
                            T_OrderSampleIsActive = 'Y'
                        GROUP BY T_TestT_SampleTypeID 
                        LIMIT 1";
                $query = $this->db_onedev->query($sql,array($value['T_OrderDetailT_TestID'],$value['T_OrderDetailT_OrderHeaderID']));
                if(!$query){
                    //echo $this->db_onedev->last_query();
                    $this->sys_error("error get spescimen");
                    exit;
                }
                
                $r_specimen = $query->row_array();

                $data_result['specimen'] = array(
                    'reference' => "Specimen/".$r_specimen['SpecimenUUID']
                );

                $serviceRequestUUID = "";
                if($value['T_OrderDetailT_TestIsPrice'] == "Y"){
                    $sql = "SELECT ServiceRequestUUID
                            FROM one_health.service_request
                            WHERE
                            ServiceRequestT_OrderDetailID = ? AND ServiceRequestIsActive = 'Y'
                            LIMIT 1";
                    $query_service_req = $this->db_onedev->query($sql,array($value['T_OrderDetailID']));
                    if(!$query_service_req){
                         //echo $this->db_onedev->last_query();
                        $this->sys_error("error get service_request 1");
                        exit;
                    }    

                    $serviceRequestUUID = $query_service_req->row()->ServiceRequestUUID;
                   
                }else{
                    $sql = "SELECT ServiceRequestUUID
                            FROM t_orderdetail
                            JOIN one_health.service_request ON ServiceRequestT_OrderDetailID = T_OrderDetailID AND
                            ServiceRequestIsActive = 'Y'
                            WHERE
                            T_OrderDetailT_OrderHeaderID = ? AND
                            T_OrderDetailT_TestIsPrice = 'Y' AND
                            T_OrderDetailT_TestSasCode LIKE CONCAT(SUBSTR(?, 0, 8),'%') AND
                            T_OrderDetailIsActive = 'Y'";
                    $query_service_req = $this->db_onedev->query($sql,array(
                        $value['T_OrderDetailT_OrderHeaderID'],
                        $value['T_OrderDetailID']
                    ));
                    if(!$query_service_req){
                         //echo $this->db_onedev->last_query();
                        $this->sys_error("error get service_request 2");
                        exit;
                    }    

                    $serviceRequestUUID = $query_service_req->row()->ServiceRequestUUID;
                }

                $data_result['basedOn'] = array(
                    array(
                        "reference" => "ServiceRequest/".$serviceRequestUUID
                    )
                );

                $sql = "SELECT Nat_MethodeID, Nat_MethodeName,Nat_NormalValueMinValue,Nat_NormalValueMaxValue,
                        Nat_NormalValueMinValueInclusive, Nat_NormalValueMaxValueInclusive
                        FROM nat_normalvalue
                        JOIN nat_methode ON Nat_NormalValueNat_MethodeID = Nat_MethodeID 
                        WHERE
                        Nat_NormalValueID = ?
                        ";
                $query = $this->db_onedev->query($sql,array(
                    $value['T_OrderDetailNat_NormalValueID']
                ));
                if(!$query){
                    $this->sys_error("error get Nat_MethodeName ");
                    exit;
                }  
//echo $this->db_onedev->last_query();
                $r_normal_value = $query->row_array();
                //print_r($r_normal_value);
                //$data_result['methode_name'] = $r_normal_value['Nat_MethodeName'];
                
                if($value['isQuantitatif'] == "N"){
                    $data_result['valueString'] = $value['T_OrderDetailResult'];
                    $data_result['valueCodeableConcept'] = array(
                        'coding' => array(
                            array(
                                'system' =>  "http://snomed.info/sct",
                                'code' => "",
                                "display" => ""
                            )
                        )
                    );

                    $data_result['referenceRange'] = array(
                        array(
                            'text' => $value['T_OrderDetailNormalValueNote']
                        )
                    );

                }else{
                    $data_result['valueQuantity'] = array(
                        'value' => floatval($value['T_OrderDetailResult']),
                        'unit' => "mmol/L",//$value['T_OrderDetailNat_UnitName'],
                        'system' => "http://unitsofmeasure.org",
                        'code' => "mmol/L",//$value['T_OrderDetailNat_UnitName']
                    );
                    $data_result['interpretation'] = array(
                        array(
                            'coding' => array(
                                array(
                                    'system' => "http://terminology.hl7.org/CodeSystem/v3-ObservationInterpretation",
                                    'code' => $value['code_interpretation'],
                                    'display' => $value['display_interpretation']
                                )
                            )
                        )
                    );

                    $data_normal_values = [];

                    $sql = "SELECT *
                            FROM nat_normalvaluetype
                            WHERE
                            Nat_NormalValueTypeIsActive = 'Y'";
                    $query = $this->db_onedev->query($sql);

                    if(!$query){
                         //echo $this->db_onedev->last_query();
                        $this->sys_error("error get normalvalues types");
                        exit;
                    }  

                    $normal_value_types = $query->result_array();

                    foreach ($normal_value_types as $k_nvt => $v_nvt) {
                        $sql = "SELECT *
                                FROM nat_normalvalue
                                JOIN nat_flag ON Nat_NormalValueNat_FlagID = Nat_FlagID
                                JOIN nat_test ON Nat_NormalValueNat_TestID = Nat_TestID
                                JOIN nat_unit ON Nat_TestNat_UnitID = Nat_UnitID
                                WHERE
                                ( Nat_NormalValueValidDate IS NULL OR Nat_NormalValueValidDate <  NOW() ) AND
                                Nat_NormalValueIsActive = 'Y' AND
                                Nat_NormalValueNat_NormalValueTypeID = {$v_nvt['Nat_NormalValueTypeID']} AND  
                                Nat_NormalValueIsAbnormal = 'N' AND 
                                ( Nat_NormalValueNat_MethodeID = {$r_normal_value["Nat_MethodeID"]}  ) AND
                                Nat_NormalValueNat_TestID = {$value["Nat_TestID"]} AND 
                                (
                                    ( {$v_nvt['Nat_NormalValueTypeID']} in (1,3) AND Nat_NormalValueNat_SexID = {$r_order['sex_id']} )  OR 
                                    {$v_nvt['Nat_NormalValueTypeID']} in (2,4)
                                ) AND
                                ( 
                                    ( Nat_NormalValueMinAgeInclusive = 'Y' AND  fn_normal_get_age(Nat_NormalValueMinAge,Nat_NormalValueAgeUnit) <= {$r_order["age_in_day"]} ) OR
                                    ( Nat_NormalValueMinAgeInclusive = 'N' AND  fn_normal_get_age(Nat_NormalValueMinAge,Nat_NormalValueAgeUnit) < {$r_order["age_in_day"]} )
                                )                                                                                  
                                AND 
                                ( 
                                    ( Nat_NormalValueMaxAgeInclusive = 'Y' AND  fn_normal_get_age(Nat_NormalValueMaxAge,Nat_NormalValueAgeUnit) >= {$r_order["age_in_day"]} ) OR
                                    ( Nat_NormalValueMaxAgeInclusive = 'N' AND  fn_normal_get_age(Nat_NormalValueMaxAge,Nat_NormalValueAgeUnit) > {$r_order["age_in_day"]} )
                                ) 
                                ";

                                $query = $this->db_onedev->query($sql);

                                if(!$query){
                                     echo $this->db_onedev->last_query();
                                    $this->sys_error("error get normalvalues type".$v_nvt['Nat_NormalValueTypeID']);
                                    exit;
                                }  

                                $normalvalues = $query->result_array();
                                if($normalvalues){
                                    foreach ($normalvalues as $k_normalvalue => $v_normalvalue) {
                                        $data_normal_values[] = array(
                                            "text" => $v_normalvalue["Nat_FlagName"],
                                            "high" => array(
                                                "value" => floatval($v_normalvalue["Nat_NormalValueMaxValue"]),
                                                "unit" => "mmol/L",//$v_normalvalue["Nat_UnitName"],
                                                "system" => "http://unitsofmeasure.org",
                                                "code" => "mmol/L"//$v_normalvalue["Nat_UnitName"]
                                            ),
                                            "low" => array(
                                                "value" => floatval($v_normalvalue["Nat_NormalValueMinValue"]),
                                                "unit" => "mmol/L",//$v_normalvalue["Nat_UnitName"],
                                                "system" => "http://unitsofmeasure.org",
                                                "code" => "mmol/L"//$v_normalvalue["Nat_UnitName"]
                                            )
                                        );
                                    }
                                }
                    }

                    $data_result['referenceRange'] = $data_normal_values;
                    $result["resource"] = $data_result;
                    $result["request"] = array("method" => "POST", "url" => "Observation");
                    $data_results[] = $result;

                    $sql = "INSERT INTO one_health.observation (
                        observationUUID,
                        observationT_OrderHeaderID,
                        observationT_TestID,
                        observationType,
                        observationJSON,
                        observationCreated
                    ) VALUES(?,?,?,?,?,NOW())";

                    $query = $this->db_onedev->query($sql,array(
                        $observation_uuid,
                        $value['T_OrderDetailT_OrderHeaderID'],
                        $value['T_OrderDetailT_TestID'],
                        $value['isQuantitatif'] == 'Y'?'Kuantitatif':'Kualitatif',
                        json_encode($result)
                    ));

                    if(!$query){
                        echo $this->db_onedev->last_query();
                        $this->sys_error("error insert observation");
                        exit;
                    }  
                    
                }
                

            }
        }

		 echo json_encode($data_results);
	}


}