<?php

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

class mira_graph_view_model extends CI_Model {

    public function __construct() {
        parent::__construct();
        $this->mdb = $this->load->database('mdb', TRUE);
        
    }
    public function getAshaDetailWithRegionCode($_app_id = NULL, $_region_code = NULL) {
        if ($_app_id != NULL && $_region_code != NULL) {
            $this->mdb->select('master_area_create_by_super.region_code, master_area_create_by_super.region_name, master_user_details.user_name');
            $this->mdb->from('master_area_create_by_super');
            $this->mdb->join('master_login_user', 'master_area_create_by_super.region_code = master_login_user.create_region_id', 'INNER');
            $this->mdb->join('master_user_details', 'master_user_details.user_login_id = master_login_user.user_login_id', 'INNER');
            $this->mdb->where([
                'master_area_create_by_super.region_code_super' => $_region_code,
                'master_area_create_by_super.app_id' => $_app_id
            ]);
            $query = $this->mdb->get();
            $result = $query->result_array();
            $final_data = [];
            if (!empty($result)) {
                foreach ($result as $row) {
                    $region_code = $row['region_code'];
                    $region_name = $row['region_name'];

                    $this->mdb->select('COUNT(*) as house_count');
                    $this->mdb->from('mira_house');
                    $this->mdb->where('region_code', $region_code);
                    $house = $this->mdb->get()->row_array();

                    $this->mdb->select('COUNT(*) as woman_count');
                    $this->mdb->from('mira_women');
                    $this->mdb->where('region_code', $region_code);
                    $woman = $this->mdb->get()->row_array();

                    $this->mdb->select('COUNT(*) as child_count');
                    $this->mdb->from('mira_child');
                    $this->mdb->where('region_code', $region_code);
                    $child = $this->mdb->get()->row_array();

                    $this->mdb->select('COUNT(*) as pregnant_woman_count');
                    $this->mdb->from('mira_pregnant_women');
                    $this->mdb->where('region_code', $region_code);
                    $pregnant_woman = $this->mdb->get()->row_array();

                    $data = [
                        'region_code' => $region_code,
                        'region_name' => $region_name,
                        'user_name' => $row['user_name']
                    ];
                    if ($house['house_count'] !== null) {
                        $data['house_count'] = $house['house_count'];
                    }
                    if ($woman['woman_count'] !== null) {
                        $data['woman_count'] = $woman['woman_count'];
                    }
                    if ($child['child_count'] !== null) {
                        $data['child_count'] = $child['child_count'];
                    }
                    if ($pregnant_woman['pregnant_woman_count'] !== null) {
                        $data['pregnant_woman_count'] = $pregnant_woman['pregnant_woman_count'];
                    }
                    $final_data[] = $data;
                }
                return $final_data;
            } else {
                return [];
            }
        } else {
            return FALSE;
        }
    }



    public function getVaccinationDataOfChildren($anm_region_code, $asha_region_code) {
    // First Query: Get vaccine-wise count
    $this->mdb->select('v.vaccine_id, v.vaccine_code, COUNT(m.child_id) as count');
    $this->mdb->from('master_child_immunization as v');
    $this->mdb->join('mira_child as m', 'm.child_id = v.child_id AND m.region_code = ' . $this->mdb->escape($asha_region_code), 'left');
    $this->mdb->group_by('v.vaccine_code, v.vaccine_id');
    $this->mdb->order_by('v.vaccine_id', 'ASC');
    $query1 = $this->mdb->get();
    
    if ($query1->num_rows() > 0) {
        $vaccineData = $query1->result_array();
    } else {
        $vaccineData = [];  // Empty array if no data found
    }

    // Second Query: Get total number of unique children
    $this->mdb->select('COUNT(DISTINCT child_id) as total_children');
    $this->mdb->from('mira_child');
    $this->mdb->where('region_code', $asha_region_code);
    $query2 = $this->mdb->get();
    
    if ($query2->num_rows() > 0) {
        $totalChildren = $query2->row_array()['total_children'];
    } else {
        $totalChildren = 0;  // Default value if no data found
    }

    // Combine both results
    return [
        'total_children' => $totalChildren,
        'vaccination_data' => $vaccineData
    ];
}

    private function childVaccineListDone($childRegistrationDate, $child_id) {
        if ($childRegistrationDate != NULL && $child_id != NULL) {
            $this->mdb->select('*');
            $this->mdb->from('master_child_immunization');
            $this->mdb->where([
                'DATE_FORMAT(master_child_immunization.submit_date,"%Y-%m-%d") >=' => date('Y-md', strtotime($childRegistrationDate)),
                'master_child_immunization.child_id' => $child_id
            ]);
// $tempData = $this->getData($this->mdb->get());
            $tempData = $this->mdb->get()->result_array();
            return $tempData ? count($tempData) : 0;
        } else {
            return 0;
        }
    }

    private function childVaccineListMissed($childRegistrationDate, $child_id) {
        if ($childRegistrationDate != NULL && $child_id != NULL) {
            $this->mdb->select('*');
            $this->mdb->from('master_child_immunization');
            $this->mdb->where([
                'DATE_FORMAT(master_child_immunization.submit_date,"%Y-%m-%d") >=' => date('Y-md', strtotime($childRegistrationDate)),
                'master_child_immunization.child_id' => $child_id,
                'master_child_immunization.vaccine_date' => '1900-01-01'
            ]);
            $tempData = $this->mdb->get()->result_array();
            return $tempData ? count($tempData) : 0;
        } else {
            return 0;
        }
    }

    public function getDoseCompletionData($anm_region_code, $asha_region_code) {
        return $this->getTotalChildUnder5Year($asha_region_code);
    }

    private function getTotalChildUnder5Year($regionId) {
        if ($regionId !== NULL) {
// Optimized query to get total, done, and missed vaccine counts in one go
            $this->mdb->select("COUNT(DISTINCT mira_child.child_id) AS total,
SUM(CASE
WHEN master_child_immunization.vaccine_date = '1900-01-01' THEN 1
ELSE 0
END) AS missed,
SUM(CASE
WHEN master_child_immunization.vaccine_date != '1900-01-01' THEN 1
ELSE 0
END) AS done
");
            $this->mdb->from('mira_child');
            $this->mdb->join('master_child_immunization', 'mira_child.child_id =
master_child_immunization.child_id', 'LEFT');
            $this->mdb->where('mira_child.region_code', $regionId);
            $this->mdb->where('DATE(master_child_immunization.submit_date) >=', date('Y-m-d', strtotime('mira_child.create_date')));
            $result = $this->mdb->get()->row_array();
            return [
                'total' => isset($result['total']) ? $result['total'] : 0,
                'missed' => isset($result['missed']) ? $result['missed'] : 0,
                'done' => isset($result['done']) ? $result['done'] : 0
            ];
        } else {
            return ['total' => 0, 'missed' => 0, 'done' => 0];
        }
    }
    
   


    public function getPregnancyStatistics($anm_region_code = NULL, $asha_region_code = NULL) {
//        $this->db->select('
//    SUM(CASE WHEN delivery_status = 1 AND delivery_location = 6 THEN 1 ELSE 0 END) AS hospital_deliveries,
//    SUM(CASE WHEN delivery_status = 1 AND delivery_location = 7 THEN 1 ELSE 0 END) AS home_deliveries,
//    SUM(CASE WHEN delivery_status NOT IN (0, 1) THEN 1 ELSE 0 END) AS other_deliveries,
//    SUM(CASE WHEN pregnant_status = 1 THEN 1 ELSE 0 END) AS running_pregnancies
//');
//$this->db->from('mira_pregnant_women');
//$this->db->where('region_code', $asha_region_code);
//
//$query = $this->db->get();
////$result = $query->row(); // Fetch the result
//
//
//
//// Fetch the result
//$pregnancy_result = $query->row();
//
//
//
    $sql = "SELECT SUM(CASE WHEN delivery_status = 1 AND delivery_location =
6 THEN 1 ELSE 0 END) AS hospital_deliveries, SUM(CASE WHEN delivery_status = 1 AND delivery_location = 7 THEN 1 ELSE 0 END) AS home_deliveries, SUM(CASE WHEN delivery_status NOT IN (0, 1) THEN 1 ELSE 0 END) AS other_deliveries, SUM(CASE WHEN pregnant_status = 1 THEN 1 ELSE 0 END) AS running_pregnancies, COUNT(*) AS total_pregnant FROM mira_pregnant_women WHERE region_code = ?";
         $pregnancy_result = $this->mdb->query($sql, array($asha_region_code))->row_array();

        //    //$pregnancy_result = $this->mdb->get()->row_array();
// Fetch total number of pregnant women
        $this->mdb->select("COUNT(*) AS total_pregnant");
        $this->mdb->from("mira_pregnant_women");
        if (!empty($anm_region_code)) {
            $this->mdb->where("region_code", $asha_region_code);
        }
        $pregnant_result = $this->mdb->get()->row_array(); // Renamed variable for clarity
        return [
            'hospital_delivery' => (int) $pregnancy_result['hospital_deliveries'],
            'home_delivery' => (int) $pregnancy_result['home_deliveries'],
            'other' => (int) $pregnancy_result['other_deliveries'],
            'running_pregnancy' => (int) $pregnancy_result['running_pregnancies'],
            'total_pregnant' => (int) $pregnant_result['total_pregnant'] // Corrected variable name
        ];
    }

}
