<?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.
 */

/**
 * Description of Registration_house_women_child_model
 *
 * @author zmq163
 */
class Registration_house_women_child_model extends CI_Model {

    //put your code here
    var $mdb;

    public function __construct() {
        parent::__construct();
        $this->mdb = $this->load->database('mdb', TRUE);
    }

    /**
     * 
     * 
     * 
     * 
     */
    public function updateWeekAttempted($pregnantId = NULL, $numberWeek = NULL, $sign = NULL) {
        if ($pregnantId != NULL and $numberWeek != NULL and $numberWeek > 0 and $sign != NULL) {
            $this->mdb->set('week_id', 'week_id ' . $sign . (int) $numberWeek, FALSE);
            $this->mdb->where('pregnant_id', $pregnantId);
            $this->mdb->update('master_asha_to_anm_alert');
            return ($this->mdb->affected_rows() > 0) ? TRUE : FALSE;
        } else {
            return FALSE;
        }
    }

    /**
     * 
     * 
     * 
     * 
     */
    public function updatePregnanByPregnantIdModel($_pregnant_id = NULL, $_update_data = NULL) {
        if ($_pregnant_id != NULL and $_update_data != NULL) {
            $this->mdb->where('pregnant_id', $_pregnant_id);
            $this->mdb->update('mira_pregnant_women', $_update_data);
            if (isset($_update_data['delivery_status']) and $_update_data['delivery_status'] == '-999') {
                $this->mdb->select('women_id');
                $this->mdb->from('mira_pregnant_women');
                $this->mdb->where('pregnant_id', $_pregnant_id);
                $temp_data = $this->getData($this->mdb->get());
                $this->mdb->where('women_id', $temp_data[0]['women_id']);
                $this->mdb->update('mira_women', array('pregnant_status' => 1));
            }
            if ($this->mdb->affected_rows() > 0) {
                return TRUE;
            } else {
                return FALSE;
            }
        } else {
            return FALSE;
        }
    }

    /**
     * 
     * 
     * 
     * 
     */
    public function childRegistrationModel($_child_data = NULL) {
        if ($_child_data != NULL) {
            $this->mdb->insert('mira_child', $_child_data);
            return ($this->mdb->affected_rows() != 1) ? FALSE : TRUE;
        } else {
            return FALSE;
        }
    }

    /**
     * 
     * 
     * 
     * 
     * 
     * 
     */
    public function womenRegistrationModel($_women_data = NULL, $_lpm_date = NULL) {
        if ($_women_data != NULL) {
            if ($_women_data['pregnant_status']) {
                $this->mdb->trans_begin();
                $this->mdb->insert('mira_women', $_women_data);

                $pregnant_data = array(
                    'pregnant_date' => $_lpm_date,
                    'women_id' => $this->mdb->insert_id()
                );
                $this->pregnantRegistrationModel($pregnant_data);
                if ($this->mdb->trans_status() === FALSE) {
                    $this->mdb->trans_rollback();
                    return FALSE;
                } else {
                    $this->mdb->trans_commit();
                    return TRUE;
                }
            } else {
                $this->mdb->insert('mira_women', $_women_data);
                return ($this->mdb->affected_rows() != 1) ? FALSE : TRUE;
            }
        } else {
            return FALSE;
        }
    }

    /**
     * 
     * 
     * 
     * 
     */
    public function getWomenModel($_women_condition = NULL) {
        if ($_women_condition != NULL) {
            $this->mdb->select('*');
            $this->mdb->from('mira_women');
            $this->mdb->where($_women_condition);
            return $this->getData($this->mdb->get());
        } else {
            return FALSE;
        }
    }

    /**
     * 
     * 
     * 
     * 
     * 
     */
    public function updateWomenModel($_condition_array = NULL, $_women_update_data = NULL) {
        if ($_condition_array != NULL and $_women_update_data != NULL) {
            $this->mdb->where($_condition_array);
            $this->mdb->update('mira_women', $_women_update_data);
            return ($this->mdb->affected_rows() != 1) ? FALSE : TRUE;
        } else {
            return FALSE;
        }
    }

    /**
     * 
     * 
     * 
     * 
     * 
     */
    public function pregnantRegistrationModel($_pregnant_data = NULL) {
        if ($_pregnant_data != NULL) {
            $this->mdb->trans_begin();
            $this->mdb->insert('mira_pregnant_women', $_pregnant_data);
            $this->updateWomenModel(array('women_id' => $_pregnant_data['women_id']), array('pregnant_status' => 1));
            if ($this->mdb->trans_status() === FALSE) {
                $this->mdb->trans_rollback();
                return FALSE;
            } else {
                $this->mdb->trans_commit();
                return TRUE;
            }
        } else {
            return FALSE;
        }
    }

    /**
     * 
     * 
     * 
     * 
     */
    public function houseRegistrationModel($_house_data = NULL) {
        if ($_house_data != NULL) {
            $this->mdb->insert('mira_house', $_house_data);
            return ($this->mdb->affected_rows() != 1) ? FALSE : TRUE;
        } else {
            return FALSE;
        }
    }
    /**
     * 
     * 
     * 
     * 
     */
    public function houseUpdateModel($autoIdArray = NULL) {
        
        if ($autoIdArray != NULL) {
            $womenIdsArray=  $this->getWomenListByHouseId($autoIdArray);
            if($womenIdsArray){
                $this->womenUpdateModel($womenIdsArray);
            }
            $this->mdb->set('delete_status', 1);
            $this->mdb->where_in('auto_id',$autoIdArray);
            $this->mdb->update('mira_house');
            return ($this->mdb->affected_rows() >0) ? TRUE : FALSE;
        } else {
            return FALSE;
        }
    }
    private function getWomenListByHouseId($houseIdArray){
        $this->mdb->select('*');
        $this->mdb->from('mira_women');
        $this->mdb->where_in('house_id',$houseIdArray);
        $tempWomenData=  $this->getData($this->mdb->get());
        if($tempWomenData){
            $tempWomenId=array();
                for($i=0;$i<count($tempWomenData);$i++){
                    $tempWomenId[]=$tempWomenData[$i]['women_id'];
                }
                return $tempWomenId;
        }  else {
            return FALSE;
        }
    }

    /**
     * 
     * 
     * 
     * 
     */
    public function womenUpdateModel($womenIdArray = NULL) {
        if ($womenIdArray != NULL) {
            $this->childUpdateModelBYwoemnIds($womenIdArray);
            $this->pregnantUpdateModel($womenIdArray);
            $this->mdb->set('delete_status', 1);
            $this->mdb->where_in('women_id',$womenIdArray);
            $this->mdb->update('mira_women');
            return ($this->mdb->affected_rows() >0) ? TRUE : FALSE;
        } else {
            return FALSE;
        }
    }
    /**
     * 
     * 
     * 
     * 
     */
    public function childUpdateModelBYwoemnIds($womenIdArray = NULL) {
        if ($womenIdArray != NULL) {
            $this->mdb->set('delete_status', 1);
             $this->mdb->where_in('women_id',$womenIdArray);
            $this->mdb->update('mira_child');
            return ($this->mdb->affected_rows() >0) ? TRUE : FALSE;
        } else {
            return FALSE;
        }
    }
    /**
     * 
     * 
     * 
     * 
     */
    public function childUpdateModel($childIdArray = NULL) {
        if ($childIdArray != NULL) {
            $this->mdb->set('delete_status', 1);
             $this->mdb->where_in('child_id',$childIdArray);
            $this->mdb->update('mira_child');
            return ($this->mdb->affected_rows() >0) ? TRUE : FALSE;
        } else {
            return FALSE;
        }
    }
    /**
     * 
     * 
     * 
     * 
     */
    public function pregnantUpdateModel($womenIdArray = NULL) {
        if ($womenIdArray != NULL) {
            $this->mdb->set('delete_status', 1);
            $this->mdb->where_in('women_id',$womenIdArray);
            $this->mdb->update('mira_pregnant_women');
            return ($this->mdb->affected_rows() >0) ? TRUE : FALSE;
        } else {
            return FALSE;
        }
    }
    

    /**
     * 
     * 
     * 
     * 
     * 
     */

    public function getHouseListTotalRecordsByRegionCodeModel($regionCode = NULL){
        if ($regionCode != NULL) {
            $this->mdb->select('*');
            $this->mdb->from('mira_house');
            $this->mdb->where(array('region_code'=> $regionCode, 'delete_status' => 0));
            return $this->mdb->count_all_results();
        } else {
            return FALSE;
        }
    }

    public function getHouseListByRegionCodeModel($_region_code = NULL, $_active_page = NULL, $_per_page_row = NULL) {
        if ($_region_code != NULL) {
            $this->mdb->select('*');
            $this->mdb->from('mira_house');
            $this->mdb->where(array('region_code'=> $_region_code,'delete_status'=>0));

            if ($_active_page != NULL and $_per_page_row != NULL) {
                $this->mdb->order_by('mira_house.auto_id', 'asc');
                $this->mdb->limit($_per_page_row, ($_active_page - 1) * $_per_page_row);
            }
            return $this->getData($this->mdb->get());
        } else {
            return FALSE;
        }
    }

    public function getHouseListUsingPagination($regionCode = NULL, $pageNumber = NULL){
        if ($regionCode != NULL) {
            $this->mdb->select('*');
            $this->mdb->from('mira_house');
            $this->mdb->where(array('region_code'=> $regionCode,'delete_status'=>0));
            $this->mdb->order_by('mira_house.auto_id', 'asc');
            $this->mdb->limit(8, ($pageNumber - 1) * 8);
            return $this->getData($this->mdb->get());
        } else {
            return FALSE;
        }
    }

    public function getHouseListByRegionCodeModelV2($_region_code = NULL, $_active_page = NULL, $_per_page_row = NULL,$_stdate= NULL,$_endate= NULL,$_search_by= NULL) {
        if ($_region_code != NULL) {
            if($_search_by==1){
                $this->mdb->select('*');
                $this->mdb->from('mira_house');
                $this->mdb->where(array('region_code'=> $_region_code,'delete_status'=>0));
                $this->mdb->where('mobile_date >=', $_stdate);
                $this->mdb->where('mobile_date <=', $_endate);
                if ($_active_page != NULL and $_per_page_row != NULL) {
                    $this->mdb->order_by('mira_house.auto_id', 'asc');
                    $this->mdb->limit($_per_page_row, ($_active_page - 1) * $_per_page_row);
                }
            }
            else if($_search_by==2){
                $this->mdb->select('*');
                $this->mdb->from('mira_house');
                $this->mdb->where(array('region_code'=> $_region_code,'delete_status'=>0));
                $this->mdb->where('actual_register_date >=', $_stdate);
                $this->mdb->where('actual_register_date <=', $_endate);
                if ($_active_page != NULL and $_per_page_row != NULL) {
                    $this->mdb->order_by('mira_house.auto_id', 'asc');
                    $this->mdb->limit($_per_page_row, ($_active_page - 1) * $_per_page_row);
                }
            }
            else if($_search_by==3){
                $this->mdb->select('*');
                $this->mdb->from('mira_house');
                $this->mdb->where(array('region_code'=> (int)$_region_code,'delete_status'=>0));
                $this->mdb->where('create_date >=', $_stdate);
                $this->mdb->where('create_date <=', $_endate);
                if ($_active_page != NULL and $_per_page_row != NULL) {
                    $this->mdb->order_by('mira_house.auto_id', 'asc');
                    $this->mdb->limit($_per_page_row, ($_active_page - 1) * $_per_page_row);
                }
            }
            return $this->getData($this->mdb->get());
        } else {
            return FALSE;
        }
    }

    public function getHouseListByRegionCodeModelV2Pagination($_page_number = NULL, $_region_code = NULL, $_stdate= NULL, $_endate= NULL, $_search_by= NULL){
        if ($_region_code != NULL) {
            if($_search_by==1){
                $this->mdb->select('*');
                $this->mdb->from('mira_house');
                $this->mdb->where(array('region_code'=> $_region_code,'delete_status'=>0));
                $this->mdb->where('mobile_date >=', $_stdate);
                $this->mdb->where('mobile_date <=', $_endate);
                $this->mdb->order_by('mira_house.auto_id', 'asc');
                $this->mdb->limit(8, ($_page_number - 1) * 8);
            }
            else if($_search_by==2){
                $this->mdb->select('*');
                $this->mdb->from('mira_house');
                $this->mdb->where(array('region_code'=> $_region_code,'delete_status'=>0));
                $this->mdb->where('actual_register_date >=', $_stdate);
                $this->mdb->where('actual_register_date <=', $_endate);
                $this->mdb->order_by('mira_house.auto_id', 'asc');
                $this->mdb->limit(8, ($_page_number - 1) * 8);
            }
            else if($_search_by==3){
                $this->mdb->select('*');
                $this->mdb->from('mira_house');
                $this->mdb->where(array('region_code'=> (int)$_region_code,'delete_status'=>0));
                $this->mdb->where('create_date >=', $_stdate);
                $this->mdb->where('create_date <=', $_endate);
                $this->mdb->order_by('mira_house.auto_id', 'asc');
                $this->mdb->limit(8, ($_page_number - 1) * 8);
            }
            return $this->getData($this->mdb->get());
        } else {
            return FALSE;
        }
    }

    public function getWomenListByRegionCodeModelV2Pagination($_page_number = NULL, $_region_code = NULL, $_stdate= NULL, $_endate= NULL, $_search_by= NULL){
        if ($_region_code != NULL) {
            if($_search_by==1){
                $this->mdb->select('mira_women.*,mira_house.house_number,mira_house.house_head_name');
                $this->mdb->from('mira_women');
                $this->mdb->join('mira_house', 'mira_house.auto_id=mira_women.house_id', 'INNER');
                $this->mdb->where(array('mira_house.region_code'=> $_region_code,'mira_women.delete_status'=>0));
                $this->mdb->where('mira_women.mobile_date >=', $_stdate);
                $this->mdb->where('mira_women.mobile_date <=', $_endate);
                $this->mdb->order_by('mira_women.women_id', 'asc');
                $this->mdb->limit(8, ($_page_number - 1) * 8);
            }
            else if($_search_by==2) {
                $this->mdb->select('mira_women.*,mira_house.house_number,mira_house.house_head_name');
                $this->mdb->from('mira_women');
                $this->mdb->join('mira_house', 'mira_house.auto_id=mira_women.house_id', 'INNER');
                $this->mdb->where(array('mira_house.region_code'=> $_region_code,'mira_women.delete_status'=>0));
                $this->mdb->where('mira_women.actual_register_date >=', $_stdate);
                $this->mdb->where('mira_women.actual_register_date <=', $_endate);
                $this->mdb->order_by('mira_women.women_id', 'asc');
                $this->mdb->limit(8, ($_page_number - 1) * 8);
            }
            else if($_search_by==3){
                $this->mdb->select('mira_women.*,mira_house.house_number,mira_house.house_head_name');
                $this->mdb->from('mira_women');
                $this->mdb->join('mira_house', 'mira_house.auto_id=mira_women.house_id', 'INNER');
                $this->mdb->where(array('mira_house.region_code'=> $_region_code,'mira_women.delete_status'=>0));
                $this->mdb->where('mira_women.create_date >=', $_stdate);
                $this->mdb->where('mira_women.create_date <=', $_endate);
                $this->mdb->order_by('mira_women.women_id', 'asc');
                $this->mdb->limit(8, ($_page_number - 1) * 8);
            }
            return $this->getData($this->mdb->get());
        }
        else {
            return FALSE;
        }
    }

    public function getChildListByRegionCodeModelV2Pagination($_page_number = NULL, $_region_code = NULL, $_stdate= NULL, $_endate= NULL, $_search_by= NULL){
        if ($_region_code != NULL) {
            if($_search_by==1){
                $this->mdb->select('mira_child.*,mira_women.name,mira_house.house_number,mira_house.house_head_name');
                $this->mdb->from('mira_child');
                $this->mdb->join('mira_women', 'mira_women.women_id=mira_child.women_id', 'INNER');
                $this->mdb->join('mira_house', 'mira_house.auto_id=mira_women.house_id', 'INNER');
                $this->mdb->where(array('mira_house.region_code'=>$_region_code,'mira_child.delete_status'=>0));
                $this->mdb->where('mira_child.mobile_date >=', $_stdate);
                $this->mdb->where('mira_child.mobile_date <=', $_endate);
                $this->mdb->order_by('mira_child.child_id', 'asc');
                $this->mdb->limit(8, ($_page_number - 1) * 8);
            }
            else if($_search_by==2){
                $this->mdb->select('mira_child.*,mira_women.name,mira_house.house_number,mira_house.house_head_name');
                $this->mdb->from('mira_child');
                $this->mdb->join('mira_women', 'mira_women.women_id=mira_child.women_id', 'INNER');
                $this->mdb->join('mira_house', 'mira_house.auto_id=mira_women.house_id', 'INNER');
                $this->mdb->where(array('mira_house.region_code'=>$_region_code,'mira_child.delete_status'=>0));
                $this->mdb->where('mira_child.actual_register_date >=', $_stdate);
                $this->mdb->where('mira_child.actual_register_date <=', $_endate);
                $this->mdb->order_by('mira_child.child_id', 'asc');
                $this->mdb->limit(8, ($_page_number - 1) * 8);
            }
            else if($_search_by==3){
                $this->mdb->select('mira_child.*,mira_women.name,mira_house.house_number,mira_house.house_head_name');
                $this->mdb->from('mira_child');
                $this->mdb->join('mira_women', 'mira_women.women_id=mira_child.women_id', 'INNER');
                $this->mdb->join('mira_house', 'mira_house.auto_id=mira_women.house_id', 'INNER');
                $this->mdb->where(array('mira_house.region_code'=>$_region_code,'mira_child.delete_status'=>0));
                $this->mdb->where('mira_child.create_date >=', $_stdate);
                $this->mdb->where('mira_child.create_date <=', $_endate);
                $this->mdb->order_by('mira_child.child_id', 'asc');
                $this->mdb->limit(8, ($_page_number - 1) * 8);
            }
            return $this->getData($this->mdb->get());
        }
        else {
           
            return FALSE;
        }
    }

    public function getTotalHouseByRegionCodeModelV2($_region_code = NULL, $_active_page = NULL, $_per_page_row = NULL,$_stdate= NULL,$_endate= NULL,$_search_by= NULL) {
        if ($_region_code != NULL) {
            if($_search_by==1){
                $this->mdb->select('*');
                $this->mdb->from('mira_house');
                $this->mdb->where(array('region_code'=> $_region_code,'delete_status'=>0));
                $this->mdb->where('mobile_date >=', $_stdate);
                $this->mdb->where('mobile_date <=', $_endate);
            }
            else if($_search_by==2){
                $this->mdb->select('*');
                $this->mdb->from('mira_house');
                $this->mdb->where(array('region_code'=> $_region_code,'delete_status'=>0));
                $this->mdb->where('actual_register_date >=', $_stdate);
                $this->mdb->where('actual_register_date <=', $_endate);
            }
            else if($_search_by==3){
                $this->mdb->select('*');
                $this->mdb->from('mira_house');
                $this->mdb->where(array('region_code'=> (int)$_region_code,'delete_status'=>0));
                $this->mdb->where('create_date >=', $_stdate);
                $this->mdb->where('create_date <=', $_endate);
            }
            return $this->mdb->count_all_results();
        } else {
            return FALSE;
        }
    }
    
    /**
     * 
     * 
     * 
     * 
     * 
     */

     public function getWomenListTotalRecordsByRegionCodeModel($regionCode = NULL){
        if ($regionCode != NULL) {
            $this->mdb->select('*');
            $this->mdb->from('mira_women');
            $this->mdb->where(array('region_code'=> $regionCode, 'delete_status' => 0));
            return $this->mdb->count_all_results();
        } else {
            return FALSE;
        }
    }

    public function getWomenListByRegionCodeModel($_region_code = NULL, $_current_page = NULL, $_per_page_row = NULL) {
        if ($_region_code != NULL) {
            $this->mdb->select('mira_women.*,mira_house.house_number,mira_house.house_head_name');
            $this->mdb->from('mira_women');
            $this->mdb->join('mira_house', 'mira_house.auto_id=mira_women.house_id', 'INNER');
            $this->mdb->where(array('mira_house.region_code'=> $_region_code,'mira_women.delete_status'=>0));
            if ($_current_page != NULL and $_per_page_row != NULL) {
                $this->mdb->order_by('mira_women.women_id', 'asc');
                $this->mdb->limit($_per_page_row, ($_current_page - 1) * $_per_page_row);
            }
            return $this->getData($this->mdb->get());
        } else {
            return FALSE;
        }
    }

    public function getWomenListUsingPagination($regionCode = NULL, $pageNumber = NULL){
        if ($regionCode != NULL) {
            $this->mdb->select('mira_women.*,mira_house.house_number,mira_house.house_head_name');
            $this->mdb->from('mira_women');
            $this->mdb->join('mira_house', 'mira_house.auto_id=mira_women.house_id', 'INNER');
            $this->mdb->where(array('mira_house.region_code'=> $regionCode,'mira_women.delete_status'=>0));
            $this->mdb->order_by('mira_women.women_id', 'asc');
            $this->mdb->limit(8, ($pageNumber - 1) * 8);
            return $this->getData($this->mdb->get());
        } else {
            return FALSE;
        }
    }

    
    public function getWomenListByRegionCodeModelV2($_region_code = NULL, $_current_page = NULL, $_per_page_row = NULL ,$_stdate= NULL,$_endate= NULL,$_search_by= NULL) {
        if ($_region_code != NULL) {
            if($_search_by==1)
            {
            $this->mdb->select('mira_women.*,mira_house.house_number,mira_house.house_head_name');
            $this->mdb->from('mira_women');
            $this->mdb->join('mira_house', 'mira_house.auto_id=mira_women.house_id', 'INNER');
            $this->mdb->where(array('mira_house.region_code'=> $_region_code,'mira_women.delete_status'=>0));
            $this->mdb->where('mira_women.mobile_date >=', $_stdate);
            $this->mdb->where('mira_women.mobile_date <=', $_endate);
            if ($_current_page != NULL and $_per_page_row != NULL) {
                $this->mdb->order_by('mira_women.women_id', 'asc');
                $this->mdb->limit($_per_page_row, ($_current_page - 1) * $_per_page_row);
            }   
            }
            else if($_search_by==2)
            {
            $this->mdb->select('mira_women.*,mira_house.house_number,mira_house.house_head_name');
            $this->mdb->from('mira_women');
            $this->mdb->join('mira_house', 'mira_house.auto_id=mira_women.house_id', 'INNER');
            $this->mdb->where(array('mira_house.region_code'=> $_region_code,'mira_women.delete_status'=>0));
            $this->mdb->where('mira_women.actual_register_date >=', $_stdate);
            $this->mdb->where('mira_women.actual_register_date <=', $_endate);
            if ($_current_page != NULL and $_per_page_row != NULL) {
                $this->mdb->order_by('mira_women.women_id', 'asc');
                $this->mdb->limit($_per_page_row, ($_current_page - 1) * $_per_page_row);
            }  
            }
             else if($_search_by==3)
             {
             $this->mdb->select('mira_women.*,mira_house.house_number,mira_house.house_head_name');
            $this->mdb->from('mira_women');
            $this->mdb->join('mira_house', 'mira_house.auto_id=mira_women.house_id', 'INNER');
            $this->mdb->where(array('mira_house.region_code'=> $_region_code,'mira_women.delete_status'=>0));
             $this->mdb->where('mira_women.create_date >=', $_stdate);
            $this->mdb->where('mira_women.create_date <=', $_endate);
            if ($_current_page != NULL and $_per_page_row != NULL) {
                $this->mdb->order_by('mira_women.women_id', 'asc');
                $this->mdb->limit($_per_page_row, ($_current_page - 1) * $_per_page_row);
            }  
             }
            return $this->getData($this->mdb->get());
        } else {
            return FALSE;
        }
    }

    public function getTotalWomenByRegionCodeModelV2($_region_code = NULL, $_current_page = NULL, $_per_page_row = NULL ,$_stdate= NULL,$_endate= NULL,$_search_by= NULL){
        if ($_region_code != NULL) {
            if($_search_by==1){
                $this->mdb->select('mira_women.*,mira_house.house_number,mira_house.house_head_name');
                $this->mdb->from('mira_women');
                $this->mdb->join('mira_house', 'mira_house.auto_id=mira_women.house_id', 'INNER');
                $this->mdb->where(array('mira_house.region_code'=> $_region_code,'mira_women.delete_status'=>0));
                $this->mdb->where('mira_women.mobile_date >=', $_stdate);
                $this->mdb->where('mira_women.mobile_date <=', $_endate); 
            }
            else if($_search_by==2){
                $this->mdb->select('mira_women.*,mira_house.house_number,mira_house.house_head_name');
                $this->mdb->from('mira_women');
                $this->mdb->join('mira_house', 'mira_house.auto_id=mira_women.house_id', 'INNER');
                $this->mdb->where(array('mira_house.region_code'=> $_region_code,'mira_women.delete_status'=>0));
                $this->mdb->where('mira_women.actual_register_date >=', $_stdate);
                $this->mdb->where('mira_women.actual_register_date <=', $_endate); 
            }
            else if($_search_by==3){
                $this->mdb->select('mira_women.*,mira_house.house_number,mira_house.house_head_name');
                $this->mdb->from('mira_women');
                $this->mdb->join('mira_house', 'mira_house.auto_id=mira_women.house_id', 'INNER');
                $this->mdb->where(array('mira_house.region_code'=> $_region_code,'mira_women.delete_status'=>0));
                $this->mdb->where('mira_women.create_date >=', $_stdate);
                $this->mdb->where('mira_women.create_date <=', $_endate);
            }
            return $this->mdb->count_all_results();
        }
        else {
            return FALSE;
        }
    }
    
    
    /**
     * 
     * 
     * 
     * 
     * 
     */

    public function getChildListTotalRecordsByRegionCodeModel($regionCode = NULL){
        if ($regionCode != NULL) {
            $this->mdb->select('*');
            $this->mdb->from('mira_child');
            $this->mdb->join('mira_women', 'mira_women.women_id=mira_child.women_id', 'INNER');
            $this->mdb->join('mira_house', 'mira_house.auto_id=mira_women.house_id', 'INNER');
            $this->mdb->where(array('mira_house.region_code'=>$regionCode,'mira_child.delete_status'=>0));
            return $this->mdb->count_all_results();
        } else {
            return FALSE;
        }
    }

    public function getChildListByRegionCodeModel($_region_code = NULL, $_current_page = NULL, $_per_page_row = NULL) {
        if ($_region_code != NULL) {
            $this->mdb->select('mira_child.*,mira_women.name,mira_house.house_number,mira_house.house_head_name');
            $this->mdb->from('mira_child');
            $this->mdb->join('mira_women', 'mira_women.women_id=mira_child.women_id', 'INNER');
            $this->mdb->join('mira_house', 'mira_house.auto_id=mira_women.house_id', 'INNER');
            $this->mdb->where(array('mira_house.region_code'=>$_region_code,'mira_child.delete_status'=>0));
            if ($_current_page != NULL and $_per_page_row != NULL) {
                $this->mdb->order_by('mira_child.child_id', 'asc');
                $this->mdb->limit($_per_page_row, ($_current_page - 1) * $_per_page_row);
            }
            return $this->getData($this->mdb->get());
        } else {
           
            return FALSE;
            
        }
    }

    public function getChildListUsingPagination($regionCode, $pageNumber){
        if ($regionCode != NULL) {
            $this->mdb->select('mira_child.*,mira_women.name,mira_house.house_number,mira_house.house_head_name');
            $this->mdb->from('mira_child');
            $this->mdb->join('mira_women', 'mira_women.women_id=mira_child.women_id', 'INNER');
            $this->mdb->join('mira_house', 'mira_house.auto_id=mira_women.house_id', 'INNER');
            $this->mdb->where(array('mira_house.region_code'=>$regionCode,'mira_child.delete_status'=>0));
            $this->mdb->order_by('mira_women.women_id', 'asc');
            $this->mdb->limit(8, ($pageNumber - 1) * 8);
            return $this->getData($this->mdb->get());
        } else {
            return FALSE;
        }
    }
    
    
    public function getChildListByRegionCodeModelV2($_region_code = NULL, $_current_page = NULL, $_per_page_row = NULL,$_stdate= NULL,$_endate= NULL,$_search_by= NULL) {
        if ($_region_code != NULL) {
            
            if($_search_by==1)
            {
            $this->mdb->select('mira_child.*,mira_women.name,mira_house.house_number,mira_house.house_head_name');
            $this->mdb->from('mira_child');
            $this->mdb->join('mira_women', 'mira_women.women_id=mira_child.women_id', 'INNER');
            $this->mdb->join('mira_house', 'mira_house.auto_id=mira_women.house_id', 'INNER');
            $this->mdb->where(array('mira_house.region_code'=>$_region_code,'mira_child.delete_status'=>0));
            $this->mdb->where('mira_child.mobile_date >=', $_stdate);
            $this->mdb->where('mira_child.mobile_date <=', $_endate);
            if ($_current_page != NULL and $_per_page_row != NULL) {
                $this->mdb->order_by('mira_child.child_id', 'asc');
                $this->mdb->limit($_per_page_row, ($_current_page - 1) * $_per_page_row);
            }
            }
            else if($_search_by==2)
            {
            $this->mdb->select('mira_child.*,mira_women.name,mira_house.house_number,mira_house.house_head_name');
            $this->mdb->from('mira_child');
            $this->mdb->join('mira_women', 'mira_women.women_id=mira_child.women_id', 'INNER');
            $this->mdb->join('mira_house', 'mira_house.auto_id=mira_women.house_id', 'INNER');
            $this->mdb->where(array('mira_house.region_code'=>$_region_code,'mira_child.delete_status'=>0));
            $this->mdb->where('mira_child.actual_register_date >=', $_stdate);
            $this->mdb->where('mira_child.actual_register_date <=', $_endate);
            if ($_current_page != NULL and $_per_page_row != NULL) {
                $this->mdb->order_by('mira_child.child_id', 'asc');
                $this->mdb->limit($_per_page_row, ($_current_page - 1) * $_per_page_row);
            }
            }
            else if($_search_by==3)
            {
            $this->mdb->select('mira_child.*,mira_women.name,mira_house.house_number,mira_house.house_head_name');
            $this->mdb->from('mira_child');
            $this->mdb->join('mira_women', 'mira_women.women_id=mira_child.women_id', 'INNER');
            $this->mdb->join('mira_house', 'mira_house.auto_id=mira_women.house_id', 'INNER');
            $this->mdb->where(array('mira_house.region_code'=>$_region_code,'mira_child.delete_status'=>0));
            $this->mdb->where('mira_child.create_date >=', $_stdate);
            $this->mdb->where('mira_child.create_date <=', $_endate);
            if ($_current_page != NULL and $_per_page_row != NULL) {
                $this->mdb->order_by('mira_child.child_id', 'asc');
                $this->mdb->limit($_per_page_row, ($_current_page - 1) * $_per_page_row);
            }
            }

             return $this->getData($this->mdb->get());
        } else {
           
            return FALSE;
            
        }
    }

    public function getTotalChildByRegionCodeModelV2($_region_code = NULL, $_current_page = NULL, $_per_page_row = NULL,$_stdate= NULL,$_endate= NULL,$_search_by= NULL){
        if ($_region_code != NULL) {
            if($_search_by==1){
                $this->mdb->select('mira_child.*,mira_women.name,mira_house.house_number,mira_house.house_head_name');
                $this->mdb->from('mira_child');
                $this->mdb->join('mira_women', 'mira_women.women_id=mira_child.women_id', 'INNER');
                $this->mdb->join('mira_house', 'mira_house.auto_id=mira_women.house_id', 'INNER');
                $this->mdb->where(array('mira_house.region_code'=>$_region_code,'mira_child.delete_status'=>0));
                $this->mdb->where('mira_child.mobile_date >=', $_stdate);
                $this->mdb->where('mira_child.mobile_date <=', $_endate);
            }
            else if($_search_by==2){
                $this->mdb->select('mira_child.*,mira_women.name,mira_house.house_number,mira_house.house_head_name');
                $this->mdb->from('mira_child');
                $this->mdb->join('mira_women', 'mira_women.women_id=mira_child.women_id', 'INNER');
                $this->mdb->join('mira_house', 'mira_house.auto_id=mira_women.house_id', 'INNER');
                $this->mdb->where(array('mira_house.region_code'=>$_region_code,'mira_child.delete_status'=>0));
                $this->mdb->where('mira_child.actual_register_date >=', $_stdate);
                $this->mdb->where('mira_child.actual_register_date <=', $_endate);
            }
            else if($_search_by==3){
                $this->mdb->select('mira_child.*,mira_women.name,mira_house.house_number,mira_house.house_head_name');
                $this->mdb->from('mira_child');
                $this->mdb->join('mira_women', 'mira_women.women_id=mira_child.women_id', 'INNER');
                $this->mdb->join('mira_house', 'mira_house.auto_id=mira_women.house_id', 'INNER');
                $this->mdb->where(array('mira_house.region_code'=>$_region_code,'mira_child.delete_status'=>0));
                $this->mdb->where('mira_child.create_date >=', $_stdate);
                $this->mdb->where('mira_child.create_date <=', $_endate);
            }
            return $this->mdb->count_all_results();
        }
        else {
           
            return FALSE;
        }
    }

    /**
     * 
     * 
     * 
     * 
     * 
     */
    public function getPregnantListByRegionCodeRunningModel($_region_code = NULL, $_current_page = NULL, $_per_page_row = NULL) {
        if ($_region_code != NULL) {
            $this->mdb->select('mira_pregnant_women.pregnant_id,mira_pregnant_women.women_id,mira_pregnant_women.pregnant_date,mira_pregnant_women.create_date as registration_date,mira_pregnant_women.mobile_date,mira_pregnant_women.actual_register_date,mira_pregnant_women.pregnant_status,mira_pregnant_women.delivery_status,mira_pregnant_women.closing_date,mira_pregnant_women.delivery_location,mira_pregnant_women.delivered_baby_status,mira_pregnant_women.delivered_women_status,mira_pregnant_women.tran_pid,mira_pregnant_women.region_code,mira_pregnant_women.delete_status,mira_women.name,mira_house.house_number');
            $this->mdb->from('mira_pregnant_women');
            $this->mdb->join('mira_women', 'mira_women.women_id=mira_pregnant_women.women_id', 'INNER');
            $this->mdb->join('mira_house', 'mira_house.auto_id=mira_women.house_id', 'INNER');
            $this->mdb->where(array('mira_house.region_id' => $_region_code, 'mira_pregnant_women.pregnant_status' => 1,'mira_pregnant_women.delete_status'=>0));
            if ($_current_page != NULL and $_per_page_row != NULL) {
                $this->mdb->order_by('mira_pregnant_women.pregnant_id', 'asc');
                $this->mdb->limit($_per_page_row, ($_current_page - 1) * $_per_page_row);
            }
            return $this->getData($this->mdb->get());
        } else {
            return FALSE;
        }
    }

    /**
     * 
     * 
     * 
     * 
     * 
     */
    public function getPregnantListByRegionCodeModel($_region_code = NULL, $_current_page = NULL, $_per_page_row = NULL) {
        if ($_region_code != NULL) {
            $this->mdb->select('mira_pregnant_women.pregnant_id,mira_pregnant_women.women_id,mira_pregnant_women.pregnant_date,mira_pregnant_women.create_date as registration_date,mira_pregnant_women.mobile_date,mira_pregnant_women.actual_register_date,mira_pregnant_women.pregnant_status,mira_pregnant_women.delivery_status,mira_pregnant_women.closing_date,mira_pregnant_women.delivery_location,mira_pregnant_women.delivered_baby_status,mira_pregnant_women.delivered_women_status,mira_pregnant_women.tran_pid,mira_pregnant_women.region_code,mira_pregnant_women.delete_status,mira_women.name,mira_house.house_number,women_tracking.anc1,women_tracking.anc2,women_tracking.anc3,women_tracking.anc4');
            $this->mdb->from('mira_pregnant_women');
            $this->mdb->join('mira_women', 'mira_women.women_id=mira_pregnant_women.women_id', 'INNER');
            $this->mdb->join('mira_house', 'mira_house.auto_id=mira_women.house_id', 'INNER');
            $this->mdb->join('women_tracking', 'women_tracking.pregnant_id=mira_pregnant_women.pregnant_id', 'LEFT');
            $this->mdb->where(array('mira_house.region_id' => $_region_code, 'mira_pregnant_women.pregnant_status' => 0,'mira_pregnant_women.delete_status' => 0));
           
            return $this->getData($this->mdb->get());
        } else {
            return FALSE;
        }
    }
    
    public function getChildVaccinationByRegioncode($_region_code) {
        if ($_region_code != NULL) {
             $query="SELECT 
             m.c_id,
    m.child_id,
    m.child_name AS child_name,m.dob,CASE WHEN m.gender = 1 THEN 'Male' WHEN m.gender = 2 THEN 'Female' WHEN m.gender = 3 THEN 'Other' ELSE 'Unknown' END AS gender_name,
    CASE WHEN m.child_category = 1 THEN 'Zero dose' WHEN m.child_category = 2 THEN 'Partial vaccination' WHEN m.child_category = 3 THEN 'Fully vaccinated' ELSE 'Unknown' END AS child_category_name,
    CASE WHEN m.mcp_card = 1 THEN 'Yes' WHEN m.mcp_card = 2 THEN 'No' ELSE 'Unknown' END AS mcpcard_status,
    m.why_not_vaccinated as main_reason, m.other_reason,  w.name AS mother_name,
    h.house_number AS house_number,h.land_mark as address,
     -- ✅ NEW FIELDS
    hc.name AS health_center_name,
    mo.name AS mohalla_name,
   CASE

-- ✅ PZD
WHEN TIMESTAMPDIFF(WEEK, m.dob, CURDATE()) BETWEEN 14 AND 52
     AND MAX(CASE WHEN mci.vaccine_code = 'Pentavalent-1' THEN 1 ELSE 0 END) = 0
THEN 'PZD'

-- ✅ ZD
WHEN TIMESTAMPDIFF(WEEK, m.dob, CURDATE()) > 52
     AND MAX(CASE WHEN mci.vaccine_code = 'Pentavalent-1' THEN 1 ELSE 0 END) = 0
THEN 'ZD'

-- ✅ FIC (Fully Immunized)
WHEN TIMESTAMPDIFF(WEEK, m.dob, CURDATE()) > 52
     AND MAX(CASE WHEN mci.vaccine_code = 'BCG' THEN 1 ELSE 0 END) = 1
     AND MAX(CASE WHEN mci.vaccine_code = 'OPV-0' THEN 1 ELSE 0 END) = 1
     AND MAX(CASE WHEN mci.vaccine_code = 'OPV-1' THEN 1 ELSE 0 END) = 1
     AND MAX(CASE WHEN mci.vaccine_code = 'OPV-2' THEN 1 ELSE 0 END) = 1
     AND MAX(CASE WHEN mci.vaccine_code = 'OPV-3' THEN 1 ELSE 0 END) = 1
     AND MAX(CASE WHEN mci.vaccine_code = 'Pentavalent-1' THEN 1 ELSE 0 END) = 1
     AND MAX(CASE WHEN mci.vaccine_code = 'Pentavalent-2' THEN 1 ELSE 0 END) = 1
     AND MAX(CASE WHEN mci.vaccine_code = 'Pentavalent-3' THEN 1 ELSE 0 END) = 1
     AND MAX(CASE WHEN mci.vaccine_code = 'Measles' THEN 1 ELSE 0 END) = 1
THEN 'FIC'

-- ✅ PV (Partially Vaccinated)
WHEN TIMESTAMPDIFF(WEEK, m.dob, CURDATE()) > 52
     AND MAX(CASE WHEN mci.vaccine_code = 'Pentavalent-1' THEN 1 ELSE 0 END) = 1
     AND (
          MAX(CASE WHEN mci.vaccine_code = 'BCG' THEN 1 ELSE 0 END) = 0
       OR MAX(CASE WHEN mci.vaccine_code = 'OPV-0' THEN 1 ELSE 0 END) = 0
       OR MAX(CASE WHEN mci.vaccine_code = 'OPV-1' THEN 1 ELSE 0 END) = 0
       OR MAX(CASE WHEN mci.vaccine_code = 'OPV-2' THEN 1 ELSE 0 END) = 0
       OR MAX(CASE WHEN mci.vaccine_code = 'OPV-3' THEN 1 ELSE 0 END) = 0
       OR MAX(CASE WHEN mci.vaccine_code = 'Pentavalent-2' THEN 1 ELSE 0 END) = 0
       OR MAX(CASE WHEN mci.vaccine_code = 'Pentavalent-3' THEN 1 ELSE 0 END) = 0
       OR MAX(CASE WHEN mci.vaccine_code = 'Measles' THEN 1 ELSE 0 END) = 0
     )
THEN 'PV'

ELSE 'NA'

END AS immunization_status,
    COALESCE(MAX(CASE WHEN mci.vaccine_code = 'Hepatitis B-0' THEN mci.vaccine_date END), '-------') AS HepatitisB,
    COALESCE(MAX(CASE WHEN mci.vaccine_code = 'OPV-0' THEN mci.vaccine_date END), '-------') AS OPV0,
    COALESCE(MAX(CASE WHEN mci.vaccine_code = 'BCG' THEN mci.vaccine_date END), '-------') AS BCG,
    COALESCE(MAX(CASE WHEN mci.vaccine_code = 'OPV-1' THEN mci.vaccine_date END), '-------') AS OPV1,
    COALESCE(MAX(CASE WHEN mci.vaccine_code = 'Pentavalent-1' THEN mci.vaccine_date END), '-------') AS Pentavalent1,
    COALESCE(MAX(CASE WHEN mci.vaccine_code = 'Rotavirus-1' THEN mci.vaccine_date END), '-------') AS Rotavirus1,
    COALESCE(MAX(CASE WHEN mci.vaccine_code = 'IPV-1' THEN mci.vaccine_date END), '-------') AS IPV1,
    COALESCE(MAX(CASE WHEN mci.vaccine_code = 'PCV-1' THEN mci.vaccine_date END), '-------') AS PCV1,
    COALESCE(MAX(CASE WHEN mci.vaccine_code = 'OPV-2' THEN mci.vaccine_date END), '-------') AS OPV2,
    COALESCE(MAX(CASE WHEN mci.vaccine_code = 'Pentavalent-2' THEN mci.vaccine_date END), '-------') AS Pentavalent2,
    COALESCE(MAX(CASE WHEN mci.vaccine_code = 'Rotavirus-2' THEN mci.vaccine_date END), '-------') AS Rotavirus2,
    COALESCE(MAX(CASE WHEN mci.vaccine_code = 'OPV-3' THEN mci.vaccine_date END), '-------') AS OPV3,
    COALESCE(MAX(CASE WHEN mci.vaccine_code = 'Pentavalent-3' THEN mci.vaccine_date END), '-------') AS Pentavalent3,
    COALESCE(MAX(CASE WHEN mci.vaccine_code = 'Rotavirus-3' THEN mci.vaccine_date END), '-------') AS Rotavirus3,
    COALESCE(MAX(CASE WHEN mci.vaccine_code = 'IPV-2' THEN mci.vaccine_date END), '-------') AS IPV2,
    COALESCE(MAX(CASE WHEN mci.vaccine_code = 'PCV-2' THEN mci.vaccine_date END), '-------') AS PCV2,
    COALESCE(MAX(CASE WHEN mci.vaccine_code = 'Measles' THEN mci.vaccine_date END), '-------') AS Measles,
    COALESCE(MAX(CASE WHEN mci.vaccine_code = 'Vitamin-A' THEN mci.vaccine_date END), '-------') AS VitaminA,
    COALESCE(MAX(CASE WHEN mci.vaccine_code = 'PCV Booster' THEN mci.vaccine_date END), '-------') AS PCVBooster,
    COALESCE(MAX(CASE WHEN mci.vaccine_code = 'DPT Booster' THEN mci.vaccine_date END), '-------') AS DPTBooster,
    COALESCE(MAX(CASE WHEN mci.vaccine_code = 'OPV Booster' THEN mci.vaccine_date END), '-------') AS OPVBooster,
    COALESCE(MAX(CASE WHEN mci.vaccine_code = 'Measles Booster' THEN mci.vaccine_date END), '-------') AS MeaslesBooster,
    m.mobile_date,TIMESTAMPDIFF(WEEK, m.dob, CURDATE()) AS age_in_weeks

FROM 
    mira_child m
    LEFT JOIN master_child_immunization mci ON mci.child_id = m.child_id
    LEFT JOIN mira_women w 
    ON w.women_id = m.women_id

LEFT JOIN mira_house h 
    ON h.auto_id = w.house_id
    
    -- ✅ NEW JOINS
LEFT JOIN health_centre hc 
    ON hc.id = h.health_center

LEFT JOIN mohalla mo 
    ON mo.moh_id = h.mohalla
WHERE 
    m.region_code = ?
GROUP BY 
   m.c_id, m.child_id, m.child_name,w.name,
    h.house_number,h.land_mark,  hc.name, mo.name 
ORDER BY 
    m.c_id;";
             // Use query binding to prevent SQL injection
        $q1 = $this->mdb->query($query, array($_region_code));
        return $this->getData($q1);

        } else {
            return FALSE;
        }
    }
    
    
        public function getPregnantRunningListByRegionCodeModel($_region_code = NULL, $_current_page = NULL, $_per_page_row = NULL) {
        if ($_region_code != NULL) {
            $this->mdb->select('mira_pregnant_women.pregnant_id,mira_pregnant_women.women_id,mira_pregnant_women.pregnant_date,mira_pregnant_women.create_date as registration_date,mira_pregnant_women.mobile_date,mira_pregnant_women.actual_register_date,mira_pregnant_women.pregnant_status,mira_pregnant_women.delivery_status,mira_pregnant_women.closing_date,mira_pregnant_women.delivery_location,mira_pregnant_women.delivered_baby_status,mira_pregnant_women.delivered_women_status,mira_pregnant_women.tran_pid,mira_pregnant_women.region_code,mira_pregnant_women.delete_status,mira_women.name,mira_house.house_number,women_tracking.anc1,women_tracking.anc2,women_tracking.anc3,women_tracking.anc4');
            $this->mdb->from('mira_pregnant_women');
            $this->mdb->join('mira_women', 'mira_women.women_id=mira_pregnant_women.women_id', 'INNER');
            $this->mdb->join('mira_house', 'mira_house.auto_id=mira_women.house_id', 'INNER');
            $this->mdb->join('women_tracking', 'women_tracking.pregnant_id=mira_pregnant_women.pregnant_id', 'LEFT');
            $this->mdb->where(array('mira_house.region_id' => $_region_code, 'mira_pregnant_women.pregnant_status' => 1,'mira_pregnant_women.delete_status' => 0));
           
            return $this->getData($this->mdb->get());
        } else {
            return FALSE;
        }
    }

    /**
     * 
     * 
     * 
     * 
     * 
     */
    public function getHouseRegionIdModel($_region_id = NULL) {
        if ($_region_id != NULL) {
            $this->mdb->select('mira_house.auto_id,mira_house.house_number');
            $this->mdb->from('mira_house');
            $this->mdb->where(array('region_id'=> $_region_id,'delete_status'=>0));
            return $this->getData($this->mdb->get());
        } else {
            return FALSE;
        }
    }

    /**
     * 
     * 
     * 
     * 
     */
    public function getAshaListWithRegionCode($_app_id = NULL, $_region_code = NULL) {
        if ($_app_id != NULL and $_region_code != NULL) {
            $this->mdb->select('master_area_create_by_super.region_code,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(array('master_area_create_by_super.region_code_super' => $_region_code, 'master_area_create_by_super.app_id' => $_app_id,'master_login_user.user_activation_status'=>1));
            return $this->getData($this->mdb->get());
        } else {
            return FALSE;
        }
    }

    
    public function getAshaHouseHoldDataWithRegionCode($_app_id = NULL, $_region_code = NULL ,$_stdate=NULL,$_endate=NULL,$_searchby=NULL) {
        if ($_app_id != NULL and $_region_code != NULL) {
           if((int)$_region_code==99999)
            {
                $arr = [];
                log_message('error', 'Region By 2: ' . $_region_code);
             $datasource=  $this->get_all_combined_data($_stdate,$_endate,$_searchby); 
             // Log values
        /*   log_message('error', 'Start Date: ' . $_stdate);
           log_message('error', 'End Date: ' . $_endate);
           log_message('error', 'Search By: ' . $_searchby);
           */
                $arr[0]['region_code'] = 99999; 
                $arr[0]['user_name'] = 'Total';
                $arr[0]['houses']=$datasource['house'];
                $arr[0]['children']=$datasource['child'];
                $arr[0]['zerodose']=$datasource['zerodose'];
                $arr[0]['partialyvaccinated']=$datasource['partialyvaccinated'];
                $arr[0]['fullyvaccinated']=$datasource['fullyvaccinated'];
                $arr[0]['activechild']=$datasource['activechild'];
                $arr[0]['closedchild']=$datasource['closedchild'];
                $arr[0]['immunized_child']=$datasource['immunized_child'];
                $arr[0]['not_immunized_child']=$datasource['not_immunized_child'];
                $arr[0]['total_vaccination']=$datasource['total_vaccination'];
                $arr[0]['total_followups']=$datasource['total_followups'];
                $arr[0]['top_3_chalanges']=$datasource['top_3_chalanges'];
                $arr[0]['mcp_card']=$datasource['mcp_card'];
                
                
                
                return $arr;  
            } 
            
            else
            {
            $this->mdb->select('master_area_create_by_super.region_code,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(array('master_area_create_by_super.region_code_super' => $_region_code, 'master_area_create_by_super.app_id' => $_app_id,'master_login_user.user_activation_status'=>1));
            $result = $this->getData($this->mdb->get());
            $arr = [];
            for($i = 0; $i < count($result); $i++){
            
                $datasource=$this->get_combined_data($result[$i]['region_code'],$_stdate,$_endate,$_searchby);
                
                $arr[$i]['region_code'] = $result[$i]['region_code']; 
                $arr[$i]['user_name'] = $result[$i]['user_name'];
                $arr[$i]['houses']=$datasource['house'];
                $arr[$i]['children']=$datasource['child'];
                $arr[$i]['zerodose']=$datasource['zerodose'];
                $arr[$i]['partialyvaccinated']=$datasource['partialyvaccinated'];
                $arr[$i]['fullyvaccinated']=$datasource['fullyvaccinated'];
                $arr[$i]['activechild']=$datasource['activechild'];
                $arr[$i]['closedchild']=$datasource['closedchild'];
                $arr[$i]['immunized_child']=$datasource['immunized_child'];
                $arr[$i]['not_immunized_child']=$datasource['not_immunized_child'];
                $arr[$i]['total_vaccination']=$datasource['total_vaccination'];
                $arr[$i]['total_followups']=$datasource['total_followups'];
                $arr[$i]['top_3_chalanges']=$datasource['top_3_chalanges'];
                $arr[$i]['mcp_card']=$datasource['mcp_card'];
                
                
                
           }
            return $arr;  
            }
            
            
        } else {
            return FALSE;
        }
    }
    
    private function applyCommonFilters($builder, $dateColumn, $_stdate, $_endate) {
    $builder->where("DATE($dateColumn) >=", $_stdate);
    $builder->where("DATE($dateColumn) <=", $_endate);
    $builder->where_not_in('region_code', [29,30,31]);
    }
    
   /* public function get_all_combined_data($_stdate,$_endate,$_searchby) {
        
        $dateColumn = '';
        switch ($_searchby) {
         case 1:
        $dateColumn = 'mobile_date';
        break;
        case 2:
        $dateColumn = 'actual_register_date';
        break;
        case 3:
        $dateColumn = 'create_date';
        break;
         default:
        // Handle cases where $_searchby is not 1, 2, or 3
        $dateColumn = 'create_date'; // or set $data1 to a default value
        $_stdate='2020-01-01';
        $_endate=  date('Y-m-d');
        break;
         }

      
       // Count total houses

        $this->mdb->where("DATE($dateColumn) BETWEEN " . $this->mdb->escape($_stdate) . " AND " . $this->mdb->escape($_endate), NULL, FALSE);
         $this->mdb->where('region_code not in (13,15,16,17,62,63,64,45)');
        $this->mdb->from('mira_house'); // Specify the table name
        $data1= $this->mdb->count_all_results();
        
        // Count total womens

        $this->mdb->where("DATE($dateColumn) BETWEEN " . $this->mdb->escape($_stdate) . " AND " . $this->mdb->escape($_endate), NULL, FALSE);
        $this->mdb->where('region_code not in (13,15,16,17,62,63,64,45)');
        $this->mdb->from('mira_women'); // Specify the table name
        $data2= $this->mdb->count_all_results();
        
        // Count total children

        $this->mdb->where("DATE($dateColumn) BETWEEN " . $this->mdb->escape($_stdate) . " AND " . $this->mdb->escape($_endate), NULL, FALSE);
        $this->mdb->where('region_code not in (13,15,16,17,62,63,64,45)');
        $this->mdb->where('women_id >', 0);
        $this->mdb->from('mira_child'); // Specify the table name
        $data3= $this->mdb->count_all_results();
        
        
         // Count total zero dose children
        $this->mdb->where("DATE($dateColumn) BETWEEN " . $this->mdb->escape($_stdate) . " AND " . $this->mdb->escape($_endate), NULL, FALSE);
        $this->mdb->where('region_code not in (13,15,16,17,62,63,64,45)');
        $this->mdb->where('women_id >', 0);
        $this->mdb->where('child_category', 1);
        $this->mdb->from('mira_child'); // Specify the table name
        $datazero= $this->mdb->count_all_results();
        
        // Count total missed dose children
        $this->mdb->where("DATE($dateColumn) BETWEEN " . $this->mdb->escape($_stdate) . " AND " . $this->mdb->escape($_endate), NULL, FALSE);
        $this->mdb->where('region_code not in (13,15,16,17,62,63,64,45)');
        $this->mdb->where('women_id >', 0);
        $this->mdb->where('child_category', 2);
        $this->mdb->from('mira_child'); // Specify the table name
        $datamissed= $this->mdb->count_all_results();
        
        // Count total zmq refusal dose children
        $this->mdb->where("DATE($dateColumn) BETWEEN " . $this->mdb->escape($_stdate) . " AND " . $this->mdb->escape($_endate), NULL, FALSE);
        $this->mdb->where('region_code not in (13,15,16,17,62,63,64,45)');
        $this->mdb->where('women_id >', 0);
        $this->mdb->where('child_category', 3);
        $this->mdb->from('mira_child'); // Specify the table name
        $datazmqr= $this->mdb->count_all_results();
        
        // Count total imi refusal dose children
        $this->mdb->where("DATE($dateColumn) BETWEEN " . $this->mdb->escape($_stdate) . " AND " . $this->mdb->escape($_endate), NULL, FALSE);
        $this->mdb->where('region_code not in (13,15,16,17,62,63,64,45)');
        $this->mdb->where('women_id >', 0);
        $this->mdb->where('child_category', 4);
        $this->mdb->from('mira_child'); // Specify the table name
        $dataimir= $this->mdb->count_all_results();
        
        
        // Count total running pregnant women
        $this->mdb->where(array('pregnant_status'=>1));
        $this->mdb->where("DATE($dateColumn) BETWEEN " . $this->mdb->escape($_stdate) . " AND " . $this->mdb->escape($_endate), NULL, FALSE);
        $this->mdb->where('region_code not in (13,15,16,17,62,63,64,45)');
        $this->mdb->from('mira_pregnant_women'); // Specify the table name
        $data4= $this->mdb->count_all_results();
        
        // Count total running closed pregnant women
        $this->mdb->where(array('pregnant_status'=>0));
         $this->mdb->where("DATE(closing_date) BETWEEN " . $this->mdb->escape($_stdate) . " AND " . $this->mdb->escape($_endate), NULL, FALSE);
         $this->mdb->where('region_code not in (13,15,16,17,62,63,64,45)');
        $this->mdb->from('mira_pregnant_women'); // Specify the table name
        $data5= $this->mdb->count_all_results();
        
         // Count total immunization
       $this->mdb->select('COUNT(*) as total'); // Select the count of records
       $this->mdb->from('master_child_immunization mci'); // Alias for clarity
       $this->mdb->join('mira_child mc', 'mci.child_id = mc.child_id '); // Join with mira_child on child_id// Condition for region_code
       $this->mdb->where('mc.women_id >', 0); // Ensure to reference region_code from mira_child
       $this->mdb->where('mc.region_code not in (13,15,16,17,62,63,64,45)');
       $this->mdb->where("DATE(mci.submit_date) BETWEEN " . $this->mdb->escape($_stdate) . " AND " . $this->mdb->escape($_endate), NULL, FALSE);
       $data6 = $this->mdb->get()->row()->total; // Execute and retrieve the total count

        
       
       // Total  Immunized children only for active children     //2540
       $this->mdb->select('COUNT( Distinct mc.child_id ) as total'); // Select the count of records
       $this->mdb->from('master_child_immunization mci'); // Alias for clarity
       $this->mdb->join('mira_child mc', 'mci.child_id = mc.child_id '); // Join with mira_child on child_id
       $this->mdb->where('mc.women_id >', 0);// Ensure to reference region_code from mira_child
       $this->mdb->where('mc.running_status =', 0);
       $this->mdb->where('mc.total_vaccine >', 0);
       $this->mdb->where('mc.region_code not in (13,15,16,17,62,63,64,45)');
       $this->mdb->where("DATE(mci.submit_date) BETWEEN " . $this->mdb->escape($_stdate) . " AND " . $this->mdb->escape($_endate), NULL, FALSE);
       $data10 = $this->mdb->get()->row()->total; // Execute and retrieve the total count
 
        // Total Not Immunized children for active children  //     312
        $this->mdb->where(array('total_vaccine'=>0));
        $this->mdb->where('women_id >', 0);
        $this->mdb->where('region_code not in (13,15,16,17,62,63,64,45)');
        $this->mdb->where('running_status =', 0);
        $this->mdb->from('mira_child'); // Specify the table name
        $data7= $this->mdb->count_all_results();
        // Total closed cases also included with immunized children  //    189
        $this->mdb->where('women_id >', 0);
        $this->mdb->where('running_status >', 0);
        $this->mdb->where('region_code not in (13,15,16,17,62,63,64,45)');
        $this->mdb->from('mira_child'); // Specify the table name
        $dataclosechildren= $this->mdb->count_all_results();
        
        // Total closed cases only for immunized children   //   112
        
       $this->mdb->select('COUNT(DISTINCT mci.child_id) as total'); // Select the count of records
       $this->mdb->from('master_child_immunization mci'); // Alias for clarity
       $this->mdb->join('mira_child mc', 'mci.child_id = mc.child_id '); // Join with mira_child on child_id
       $this->mdb->where('mc.women_id >', 0); // Ensure to reference region_code from mira_child
       $this->mdb->where('mc.running_status >', 0); 
       $this->mdb->where('mc.region_code not in (13,15,16,17,62,63,64,45)');
       $datacloseonlyimmunizedchildren = $this->mdb->get()->row()->total; // Execute and retrieve the total count

       
       // Total closed cases only for not single immunized children    //  189-112
       $datacloseonlynotimmunizedchildren=$dataclosechildren-$datacloseonlyimmunizedchildren;
       
        // Total hrp
      
         $this->mdb->select('COUNT(*) as total'); // Select the count of records
         $this->mdb->from('master_asha_to_anm_alert ma'); // Alias for clarity
         $this->mdb->join('mira_pregnant_women mpw', 'ma.pregnant_id = mpw.pregnant_id');
         $this->mdb->where('mpw.region_code not in (13,15,16,17,62,63,64,45)');// Join on pregnant_id
         $this->mdb->where("DATE(ma.create_date) BETWEEN " . $this->mdb->escape($_stdate) . " AND " . $this->mdb->escape($_endate), NULL, FALSE);
         $result = $this->mdb->get()->row(); // Execute and retrieve the row
         $data8 = ($result && isset($result->total)) ? $result->total : 0; // Return 0 if no results found

 
        
        // Total anc
$this->mdb->select('COUNT(*) as total'); // Select the count of records
$this->mdb->from('anm_anc_critical aac'); // Alias for clarity
$this->mdb->join('mira_pregnant_women mpw', 'aac.pregnant_id = mpw.pregnant_id'); // Join on pregnant_id
 $this->mdb->where('mpw.region_code not in (13,15,16,17,62,63,64,45)');
$this->mdb->where("DATE(aac.created_date) BETWEEN " . $this->mdb->escape($_stdate) . " AND " . $this->mdb->escape($_endate), NULL, FALSE);

$result = $this->mdb->get()->row(); // Execute and retrieve the row

$data9 = ($result && isset($result->total)) ? $result->total : 0; // Return 0 if no records found
  
        
      
        
        //  Total Hepatitis B-0
       $this->mdb->select('COUNT(*) as total'); // Select the count of records
       $this->mdb->from('master_child_immunization mci'); // Alias for clarity
       $this->mdb->join('mira_child mc', 'mci.child_id = mc.child_id '); // Join with mira_child on child_id
       $this->mdb->where(array('mci.vaccine_id'=>8)); // Ensure to reference region_code from mira_child
        $this->mdb->where('mc.region_code not in (13,15,16,17,62,63,64,45)');
       $this->mdb->where("DATE(mci.submit_date) BETWEEN " . $this->mdb->escape($_stdate) . " AND " . $this->mdb->escape($_endate), NULL, FALSE);
       $data11 = $this->mdb->get()->row()->total; // Execute and retrieve the total count

        
          //  Total OPV-0
       $this->mdb->select('COUNT(*) as total'); // Select the count of records
       $this->mdb->from('master_child_immunization mci'); // Alias for clarity
       $this->mdb->join('mira_child mc', 'mci.child_id = mc.child_id '); // Join with mira_child on child_id
       $this->mdb->where(array('mci.vaccine_id'=>9)); // Ensure to reference region_code from mira_child
       $this->mdb->where('mc.region_code not in (13,15,16,17,62,63,64,45)');
       $this->mdb->where("DATE(mci.submit_date) BETWEEN " . $this->mdb->escape($_stdate) . " AND " . $this->mdb->escape($_endate), NULL, FALSE);
       $data12 = $this->mdb->get()->row()->total; // Execute and retrieve the total count

        
       //  Total BCG
       $this->mdb->select('COUNT(*) as total'); // Select the count of records
       $this->mdb->from('master_child_immunization mci'); // Alias for clarity
       $this->mdb->join('mira_child mc', 'mci.child_id = mc.child_id '); // Join with mira_child on child_id
       $this->mdb->where(array('mci.vaccine_id'=>10)); // Ensure to reference region_code from mira_child
       $this->mdb->where('mc.region_code not in (13,15,16,17,62,63,64,45)');
       $this->mdb->where("DATE(mci.submit_date) BETWEEN " . $this->mdb->escape($_stdate) . " AND " . $this->mdb->escape($_endate), NULL, FALSE);
       $data13 = $this->mdb->get()->row()->total; // Execute and retrieve the total count

        //  Total Except Hepatitis B-0,OPV-0, BCG
       $this->mdb->select('COUNT(*) as total'); // Select the count of records
       $this->mdb->from('master_child_immunization mci'); // Alias for clarity
       $this->mdb->join('mira_child mc', 'mci.child_id = mc.child_id '); // Join with mira_child on child_id
       $this->mdb->where("mci.vaccine_id NOT IN (8, 9, 10)", NULL, FALSE); // Exclude vaccine IDs 8, 9, and 10
       $this->mdb->where('mc.region_code not in (13,15,16,17,62,63,64,45)');
       $this->mdb->where("DATE(mci.submit_date) BETWEEN " . $this->mdb->escape($_stdate) . " AND " . $this->mdb->escape($_endate), NULL, FALSE);
       $data14 = $this->mdb->get()->row()->total; // Execute and retrieve the total count
   
   
   // Total anc 0
       $this->mdb->select('COUNT(*) as total'); // Select the count of records
       $this->mdb->from('mira_anc_status0 aac'); // Alias for clarity
       $this->mdb->join('mira_pregnant_women mpw', 'aac.pregnant_id = mpw.pregnant_id'); // Join on pregnant_id
       $this->mdb->where(array('aac.anc_code'=>0)); // Reference region_code from mira_pregnant_women
       $this->mdb->where('mpw.region_code not in (13,15,16,17,62,63,64,45)');
       $this->mdb->where("DATE(aac.create_date) BETWEEN " . $this->mdb->escape($_stdate) . " AND " . $this->mdb->escape($_endate), NULL, FALSE);
       $result = $this->mdb->get()->row(); // Execute and retrieve the row
       $dataanc0 = ($result && isset($result->total)) ? $result->total : 0; // Return 0 if no records found

  
       // Total anc 1
       $this->mdb->select('COUNT(*) as total'); // Select the count of records
       $this->mdb->from('anm_anc_critical aac'); // Alias for clarity
       $this->mdb->join('mira_pregnant_women mpw', 'aac.pregnant_id = mpw.pregnant_id'); // Join on pregnant_id
       $this->mdb->where(array('aac.anc_code'=>1)); // Reference region_code from mira_pregnant_women
       $this->mdb->where('mpw.region_code not in (13,15,16,17,62,63,64,45)'); // Reference region_code from mira_pregnant_women
       $this->mdb->where("DATE(aac.created_date) BETWEEN " . $this->mdb->escape($_stdate) . " AND " . $this->mdb->escape($_endate), NULL, FALSE);
       $result11 = $this->mdb->get()->row(); // Execute and retrieve the row
       $dataanc1 = ($result11 && isset($result11->total)) ? $result11->total : 0; // Return 0 if no records found
  
       // Total anc 2
       $this->mdb->select('COUNT(*) as total'); // Select the count of records
       $this->mdb->from('anm_anc_critical aac'); // Alias for clarity
       $this->mdb->join('mira_pregnant_women mpw', 'aac.pregnant_id = mpw.pregnant_id'); // Join on pregnant_id
       $this->mdb->where(array('aac.anc_code'=>2)); // Reference region_code from mira_pregnant_women
       $this->mdb->where('mpw.region_code not in (13,15,16,17,62,63,64,45)'); // Reference region_code from mira_pregnant_women
       $this->mdb->where("DATE(aac.created_date) BETWEEN " . $this->mdb->escape($_stdate) . " AND " . $this->mdb->escape($_endate), NULL, FALSE);
       $result22 = $this->mdb->get()->row(); // Execute and retrieve the row
       $dataanc2 = ($result22 && isset($result22->total)) ? $result22->total : 0; // Return 0 if no records found

        // Total anc 3
       $this->mdb->select('COUNT(*) as total'); // Select the count of records
       $this->mdb->from('anm_anc_critical aac'); // Alias for clarity
       $this->mdb->join('mira_pregnant_women mpw', 'aac.pregnant_id = mpw.pregnant_id'); // Join on pregnant_id
       $this->mdb->where(array('aac.anc_code'=>3)); // Reference region_code from mira_pregnant_women
       $this->mdb->where('mpw.region_code not in (13,15,16,17,62,63,64,45)'); // Reference region_code from mira_pregnant_women
       $this->mdb->where("DATE(aac.created_date) BETWEEN " . $this->mdb->escape($_stdate) . " AND " . $this->mdb->escape($_endate), NULL, FALSE);
       $result33 = $this->mdb->get()->row(); // Execute and retrieve the row
       $dataanc3 = ($result33 && isset($result33->total)) ? $result33->total : 0; // Return 0 if no records found

       
      // Total anc 4
       $this->mdb->select('COUNT(*) as total'); // Select the count of records
       $this->mdb->from('anm_anc_critical aac'); // Alias for clarity
       $this->mdb->join('mira_pregnant_women mpw', 'aac.pregnant_id = mpw.pregnant_id'); // Join on pregnant_id
       $this->mdb->where(array('aac.anc_code'=>4)); // Reference region_code from mira_pregnant_women
       $this->mdb->where('mpw.region_code not in (13,15,16,17,62,63,64,45)'); // Reference region_code from mira_pregnant_women
       $this->mdb->where("DATE(aac.created_date) BETWEEN " . $this->mdb->escape($_stdate) . " AND " . $this->mdb->escape($_endate), NULL, FALSE);
       $result44 = $this->mdb->get()->row(); // Execute and retrieve the row
       $dataanc4 = ($result44 && isset($result44->total)) ? $result44->total : 0; // Return 0 if no records found
       
       // Total immunized Male childredn 
       $this->mdb->select('COUNT( Distinct mc.child_id ) as total'); // Select the count of records
       $this->mdb->from('master_child_immunization mci'); // Alias for clarity
       $this->mdb->join('mira_child mc', 'mci.child_id = mc.child_id '); // Join with mira_child on child_id
       $this->mdb->where('mc.women_id >', 0);// Ensure to reference region_code from mira_child
       $this->mdb->where('mc.running_status =', 0);
       $this->mdb->where('mc.total_vaccine >', 0);
       $this->mdb->where('mc.gender =', 1);
       $this->mdb->where('mc.region_code not in (13,15,16,17,62,63,64,45)');
       $this->mdb->where("DATE(mci.submit_date) BETWEEN " . $this->mdb->escape($_stdate) . " AND " . $this->mdb->escape($_endate), NULL, FALSE);
       $datamale = $this->mdb->get()->row()->total; // Execute and retrieve the total count
       
       
       
       
       
       // Total immunized FeMale childredn 
       $this->mdb->select('COUNT( Distinct mc.child_id ) as total'); // Select the count of records
       $this->mdb->from('master_child_immunization mci'); // Alias for clarity
       $this->mdb->join('mira_child mc', 'mci.child_id = mc.child_id '); // Join with mira_child on child_id
       $this->mdb->where('mc.women_id >', 0);// Ensure to reference region_code from mira_child
       $this->mdb->where('mc.running_status =', 0);
       $this->mdb->where('mc.total_vaccine >', 0);
       $this->mdb->where('mc.gender =', 2);
       $this->mdb->where('mc.region_code not in (13,15,16,17,62,63,64,45)');
       $this->mdb->where("DATE(mci.submit_date) BETWEEN " . $this->mdb->escape($_stdate) . " AND " . $this->mdb->escape($_endate), NULL, FALSE);
       $datafemale = $this->mdb->get()->row()->total; // Execute and retrieve the total count
       
       // Total home deliveris
       // $this->mdb->where('region_code', $region_code);
        $this->mdb->where(array('delivery_status'=>1,'delivery_location'=>7,'pregnant_status'=>0)); 
        $this->mdb->where('region_code not in (13,15,16,17,62,63,64,45)');
        $this->mdb->where("DATE(closing_date) BETWEEN " . $this->mdb->escape($_stdate) . " AND " . $this->mdb->escape($_endate), NULL, FALSE);
        $this->mdb->from('mira_pregnant_women'); // Specify the table name
        $data_homedelivery= $this->mdb->count_all_results();
       
       // Total hospital deliveris

        $this->mdb->where(array('delivery_status'=>1,'delivery_location'=>6,'pregnant_status'=>0)); 
        $this->mdb->where('region_code not in (13,15,16,17,62,63,64,45)');
        $this->mdb->where("DATE(closing_date) BETWEEN " . $this->mdb->escape($_stdate) . " AND " . $this->mdb->escape($_endate), NULL, FALSE);
        $this->mdb->from('mira_pregnant_women'); // Specify the table name
        $data_hospitaldelivery= $this->mdb->count_all_results();
       
       // Total aborted
        $this->mdb->where(array('delivery_status'=>2,'delivery_location'=>0,'pregnant_status'=>0)); 
        $this->mdb->where('region_code not in (13,15,16,17,62,63,64,45)');
        $this->mdb->where("DATE(closing_date) BETWEEN " . $this->mdb->escape($_stdate) . " AND " . $this->mdb->escape($_endate), NULL, FALSE);
        $this->mdb->from('mira_pregnant_women'); // Specify the table name
        $data_aborted= $this->mdb->count_all_results();

        // Total death
        $this->mdb->where(array('delivery_status'=>3,'delivery_location'=>6,'delivery_location'=>7,'pregnant_status'=>0)); 
        $this->mdb->where('region_code not in (13,15,16,17,62,63,64,45)');
        $this->mdb->where("DATE(closing_date) BETWEEN " . $this->mdb->escape($_stdate) . " AND " . $this->mdb->escape($_endate), NULL, FALSE);
        $this->mdb->from('mira_pregnant_women'); // Specify the table name
        $data_death= $this->mdb->count_all_results();
        
        // Total Migrated
        $this->mdb->where(array('delivery_status'=>4,'delivery_location'=>0,'pregnant_status'=>0)); 
        $this->mdb->where('region_code not in (13,15,16,17,62,63,64,45)');
        $this->mdb->where("DATE(closing_date) BETWEEN " . $this->mdb->escape($_stdate) . " AND " . $this->mdb->escape($_endate), NULL, FALSE);
        $this->mdb->from('mira_pregnant_women'); // Specify the table name
        $data_migrated= $this->mdb->count_all_results();
        
        // Total Others
        $this->mdb->where(array('delivery_status'=>5,'delivery_location'=>0,'pregnant_status'=>0)); 
        $this->mdb->where('region_code not in (13,15,16,17,62,63,64,45)');
        $this->mdb->where("DATE(closing_date) BETWEEN " . $this->mdb->escape($_stdate) . " AND " . $this->mdb->escape($_endate), NULL, FALSE);
        $this->mdb->from('mira_pregnant_women'); // Specify the table name
        $data_others= $this->mdb->count_all_results();     
     
       
// Combine the data into an associative array
        $result = array(
            'house' => $data1,
            'women' => $data2,
            'child' => $data3,
            'pregwomen' => $data4,
            'closepregwomen' => $data5,
            'immunization'=>$data6,
            'notimmunizedchild'=>$data7,
            'immunizedchild'=>$data10,
            'total_hrp'=>$data8,
            'total_anc'=>$data9,
            'total_heptb'=>$data11,
            'total_opv'=>$data12,
            'total_bcg'=>$data13,
            'total_other_vaccine'=>$data14,
            
            'total_pentavalent_1'=>0,
            'total_opv_1'=>0,
            'total_rotavirus_1'=>0,
            'total_ipv_1'=>0,
            'total_pcv_1'=>0,
            'total_opv_2'=>0,
            'total_pentavalent_2'=>0,
            'total_rotavirus_2'=>0,
            'total_opv_3'=>0,
            'total_pentavalent_3'=>0,
            'total_rotavirus_3'=>0,
            'total_ipv_2'=>0,
            'total_pcv_2'=>0,
            'total_measles'=>0,
            'total_vitaminA'=>0,
            'total_pcv_booster'=>0,
            'total_dpt_booster'=>0,
            'total_opv_booster'=>0,
            'total_measles_booster'=>0,
            'total_closed_children'=>$dataclosechildren,
            'total_anc0'=>$dataanc0,
            'total_anc1'=>$dataanc1,
            'total_anc2'=>$dataanc2,
            'total_anc3'=>$dataanc3,
            'total_anc4'=>$dataanc4,
            'total_male'=>$datamale,
            'total_female'=>$datafemale,
            'total_homedelivery'=>$data_homedelivery,
            'total_hospitaldelivery'=>$data_hospitaldelivery,
            'total_aborted'=>$data_aborted,
            'total_death'=>$data_death,
            'total_migrated'=>$data_migrated,
            'total_others'=>$data_others,
            'zerodose'=>$datazero,
            'missedose'=>$datamissed,
            'zmqr'=>$datazmqr,
            'imir'=>$dataimir,
            
        );
        return $result;
     }
    */
    public function get_all_combined_data($_stdate, $_endate, $_searchby)
    {
         $dateColumn = '';
    switch ($_searchby) {
        case 1: $dateColumn = 'mobile_date'; break;
        case 2: $dateColumn = 'actual_register_date'; break;
        case 3:$dateColumn = 'create_date'; break;
        default:
        // Handle cases where $_searchby is not 1, 2, or 3
        $dateColumn = 'create_date'; // or set $data1 to a default value
        $_stdate='2020-01-01';
        $_endate=  date('Y-m-d');
       /*
        log_message('error', 'Start Date 1: ' . $_stdate);
        log_message('error', 'End Date: ' .$_endate);
        log_message('error', 'Search By: ' . $dateColumn);
       */
        break;
    
    }

    // ===== HOUSES =====
    $this->applyCommonFilters($this->mdb, $dateColumn, $_stdate, $_endate);
    $house = $this->mdb
    ->from('mira_house')
    ->count_all_results();
   /*
    log_message('error', 'Query: ' . $this->mdb->last_query());
    log_message('error', 'Houses: ' . $house);
   */
    // ===== CHILD COUNTS =====
      $this->mdb->select("
      COUNT(*) as total_child,
      SUM(CASE WHEN child_category=1 THEN 1 ELSE 0 END) as zerodose,
      SUM(CASE WHEN mcp_card=1 THEN 1 ELSE 0 END) as mcpcard,
      SUM(CASE WHEN child_category=2 THEN 1 ELSE 0 END) as partialyvaccinated,
      SUM(CASE WHEN child_category=3 THEN 1 ELSE 0 END) as fullyvaccinated,
      SUM(CASE WHEN running_status=0 THEN 1 ELSE 0 END) as activechild,
      SUM(CASE WHEN total_vaccine=0 THEN 1 ELSE 0 END) as not_immunized_child
     ");
    $this->mdb->from('mira_child');
    $this->applyCommonFilters($this->mdb, $dateColumn, $_stdate, $_endate);
    $childData = $this->mdb->get()->row();
    //log_message('error', 'childData: ' .$childData->total_child ?? 0);

    // Safe values
    $total_child = $childData->total_child ?? 0;
    $mcp_child = $childData->mcpcard ?? 0;
    $zerodose = $childData->zerodose ?? 0;
    $partialyvaccinated = $childData->partialyvaccinated ?? 0;
    $fullyvaccinated = $childData->fullyvaccinated ?? 0;
    $activechild = $childData->activechild ?? 0;
    $not_immunized_child = $childData->not_immunized_child ?? 0;
    
    
     // ===== Total Closed Children =====
    $this->mdb->select("
    SUM(CASE WHEN running_status>0 THEN 1 ELSE 0 END) as closedchild
    ");
   $this->mdb->from('mira_child');
   $this->mdb->where_not_in('region_code', [29,30,31]);
   $this->mdb->where("closing_date >=", $_stdate);
   $this->mdb->where("closing_date <=", $_endate);
   $closedchildData = $this->mdb->get()->row();
   $closedchild = $closedchildData->closedchild ?? 0; 

     // ===== Total Immunized Children =====
    $this->mdb->select("COUNT(DISTINCT child_id) as immunized_child");
    $this->mdb->from('master_child_immunization');
    $this->mdb->where_not_in('region_code', [29,30,31]);
    $this->mdb->where("DATE(submit_date) >=", $_stdate);
    $this->mdb->where("DATE(submit_date) <=", $_endate);
    $immunizedchildData = $this->mdb->get()->row();
    $immunized_child = $immunizedchildData->immunized_child ?? 0;
    // ===== Total Vaccination =====
   $this->mdb->select("COUNT(*) as total_vaccination");
   $this->mdb->from('master_child_immunization');
   $this->mdb->where_not_in('region_code', [29,30,31]);
   $this->mdb->where("DATE(submit_date) >=", $_stdate);
   $this->mdb->where("DATE(submit_date) <=", $_endate);
   $totalvaccineData = $this->mdb->get()->row();
   $total_vaccination = $totalvaccineData->total_vaccination ?? 0;

    // ===== FOLLOWUPS =====
    $this->mdb->select("COUNT(*) as total_followups");
    $this->mdb->from('mira_followup');
    $this->applyCommonFilters($this->mdb, $dateColumn, $_stdate, $_endate);

    $followupData = $this->mdb->get()->row();
    $total_followups = $followupData->total_followups ?? 0;
    log_message('error', 'followupData: ' .$total_followups);
    
     // ===== TOP 3 Chalanges =====
    $this->mdb->select('reason');
    $this->mdb->from('child_register_reasons');
    $this->mdb->where_not_in('region_code', [29,30,31]);
    $this->mdb->where("DATE(create_date) >=", $_stdate);
    $this->mdb->where("DATE(create_date) <=", $_endate);
    $this->mdb->where("DATE(create_date) <=", $_endate);
    $this->mdb->where("reason NOT LIKE", "अन्य:%");
    $this->mdb->group_by('reason');
    $this->mdb->order_by('COUNT(reason)', 'DESC');
    $this->mdb->limit(5);
    $query = $this->mdb->get();
    $reasons = array_column($query->result_array(), 'reason');
    log_message('error', 'reasons: ' . implode(', ', $reasons));
    
    // ===== Percentage with MCP cards =====
    if ($total_child > 0) {
    $mcppercentage = round(($mcp_child / $total_child) * 100, 2);
    } else {
    $mcppercentage = 0;
    }
       
    

    return [
        'house' => $house,
        'child' => $total_child,
        'zerodose' => $zerodose,
        'partialyvaccinated' => $partialyvaccinated,
        'fullyvaccinated' => $fullyvaccinated,
        'activechild' => $activechild,
        'closedchild' => $closedchild,
        'immunized_child' => $immunized_child,
        'not_immunized_child' => $not_immunized_child,
        'total_vaccination' => $total_vaccination,
        'total_followups' => $total_followups,
        'top_3_chalanges' => implode(', ', $reasons),
        'mcp_card' => $mcppercentage
    ];
}
    
    public function get_combined_data($region_code,$_stdate,$_endate,$_searchby) {
        
        $dateColumn = '';
        switch ($_searchby) {
         case 1:
        $dateColumn = 'mobile_date';
        break;
        case 2:
        $dateColumn = 'actual_register_date';
        break;
        case 3:
        $dateColumn = 'create_date';
        break;
         default:
        // Handle cases where $_searchby is not 1, 2, or 3
        $dateColumn = 'create_date'; // or set $data1 to a default value
        $_stdate='2020-01-01';
        $_endate=  date('Y-m-d');
        break;
         }

        
        // ===== HOUSES =====
        $this->mdb->where("DATE(create_date) >=", $_stdate);
        $this->mdb->where("DATE(create_date) <=", $_endate);
       $this->mdb->where('region_code',$region_code );
       $house = $this->mdb
    ->from('mira_house')
    ->count_all_results();


    // ===== CHILD COUNTS =====
    $this->mdb->select("
    COUNT(*) as total_child,
    SUM(CASE WHEN mcp_card=1 THEN 1 ELSE 0 END) as mcpcard,
    SUM(CASE WHEN child_category=1 THEN 1 ELSE 0 END) as zerodose,
    SUM(CASE WHEN child_category=2 THEN 1 ELSE 0 END) as partialyvaccinated,
    SUM(CASE WHEN child_category=3 THEN 1 ELSE 0 END) as fullyvaccinated,
    SUM(CASE WHEN running_status=0 THEN 1 ELSE 0 END) as activechild,
    SUM(CASE WHEN total_vaccine=0 THEN 1 ELSE 0 END) as not_immunized_child
    ");
    $this->mdb->from('mira_child');
    $this->mdb->where('region_code',$region_code );
    $this->mdb->where("DATE(create_date) >=", $_stdate);
    $this->mdb->where("DATE(create_date) <=", $_endate);
    $childData = $this->mdb->get()->row();


    // Safe values
    $total_child = $childData->total_child ?? 0;
    $mcp_child = $childData->mcpcard ?? 0;
    $zerodose = $childData->zerodose ?? 0;
    $partialyvaccinated = $childData->partialyvaccinated ?? 0;
    $fullyvaccinated = $childData->fullyvaccinated ?? 0;
    $activechild = $childData->activechild ?? 0;
    $not_immunized_child = $childData->not_immunized_child ?? 0;
    
     // ===== Total Closed Children =====
    $this->mdb->select("
    SUM(CASE WHEN running_status>0 THEN 1 ELSE 0 END) as closedchild
    ");
   $this->mdb->from('mira_child');
   $this->mdb->where('region_code',$region_code );
   $this->mdb->where("closing_date >=", $_stdate);
   $this->mdb->where("closing_date <=", $_endate);
   $closedchildData = $this->mdb->get()->row();
   $closedchild = $closedchildData->closedchild ?? 0; 
   
        // ===== Total Immunized Children =====
    $this->mdb->select("COUNT(DISTINCT child_id) as immunized_child");
    $this->mdb->from('master_child_immunization');
    $this->mdb->where('region_code',$region_code );
    $this->mdb->where("DATE(submit_date) >=", $_stdate);
    $this->mdb->where("DATE(submit_date) <=", $_endate);
    $immunizedchildData = $this->mdb->get()->row();
    $immunized_child = $immunizedchildData->immunized_child ?? 0;
    
      // ===== Total Vaccination =====
   $this->mdb->select("COUNT(*) as total_vaccination");
   $this->mdb->from('master_child_immunization');
   $this->mdb->where('region_code',$region_code );
   $this->mdb->where("DATE(submit_date) >=", $_stdate);
   $this->mdb->where("DATE(submit_date) <=", $_endate);
   $totalvaccineData = $this->mdb->get()->row();
   $total_vaccination = $totalvaccineData->total_vaccination ?? 0;

    
    // ===== FOLLOWUPS =====
    $this->mdb->select("COUNT(*) as total_followups");
    $this->mdb->from('mira_followup');
    $this->mdb->where('region_code',$region_code );
    $this->mdb->where("DATE(create_date) >=", $_stdate);
    $this->mdb->where("DATE(create_date) <=", $_endate);
    $followupData = $this->mdb->get()->row();
    $total_followups = $followupData->total_followups ?? 0;
    log_message('error', 'followupData: ' .$total_followups);
    
     // ===== TOP 3 Chalanges =====
    $this->mdb->select('reason');
    $this->mdb->from('child_register_reasons');
    $this->mdb->where('region_code',$region_code );
    $this->mdb->where("DATE(create_date) >=", $_stdate);
    $this->mdb->where("DATE(create_date) <=", $_endate);
    $this->mdb->where("reason NOT LIKE", "अन्य:%");
    $this->mdb->group_by('reason');
    $this->mdb->order_by('COUNT(reason)', 'DESC');
    $this->mdb->limit(5);
    $query = $this->mdb->get();
    $reasons = array_column($query->result_array(), 'reason');
    log_message('error', 'reasons: ' . implode(', ', $reasons));
    
    // ===== Percentage with MCP cards =====
    if ($total_child > 0) {
    $mcppercentage = round(($mcp_child / $total_child) * 100, 2);
    } else {
    $mcppercentage = 0;
    }
   
    return [
        'house' => $house,
        'child' => $total_child,
        'zerodose' => $zerodose,
        'partialyvaccinated' => $partialyvaccinated,
        'fullyvaccinated' => $fullyvaccinated,
        'activechild' => $activechild,
        'closedchild' => $closedchild,
        'immunized_child' => $immunized_child,
        'not_immunized_child' => $not_immunized_child,
        'total_vaccination' => $total_vaccination,
        'total_followups' => $total_followups,
        'top_3_chalanges' => implode(', ', $reasons),
        'mcp_card' => $mcppercentage
    ];
    
    
     }
    /**
     * 
     * 
     * 
     * 
     */
    private function getData($_query = NULL) {
        if ($_query != NULL and $_query->num_rows() > 0) {
            return $_query->result_array();
        } else {
           // print_r($_query);
           // return $_query->result_array();
            FALSE;
        }
    }
    
     ///Rahim Code
    
     public function getWomenListByRegionCode($house_id) {
        if ($house_id != NULL) {
            $this->mdb->select('mira_women.*, mira_house.house_number, mira_house.house_head_name, mira_house.auto_id');
            $this->mdb->from('mira_women');
            $this->mdb->join('mira_house', 'mira_house.auto_id = mira_women.house_id', 'INNER');
            $this->mdb->where(array('mira_women.house_id' => $house_id, 'mira_women.delete_status' => 0));
            return $this->getData($this->mdb->get());
        } else {
            return FALSE;
        }
    }


    public function getChildrenListByWomenId($women_id){
        if ($women_id != NULL) {
            $this->mdb->select('mira_child.*,mira_women.name,mira_house.house_number,mira_house.house_head_name');
            $this->mdb->from('mira_child');
            $this->mdb->join('mira_women', 'mira_women.women_id=mira_child.women_id', 'INNER');
            $this->mdb->join('mira_house', 'mira_house.auto_id=mira_women.house_id', 'INNER');
            $this->mdb->where(array('mira_child.women_id'=>$women_id,'mira_child.delete_status'=>0));
            return $this->getData($this->mdb->get());
        } else {
            return FALSE;
        }
    }
    
    ///Rahim Code
    
     public function getANMListWithAppId($_app_id = NULL, $_region_code = NULL) {
        if ($_app_id != NULL and $_region_code != NULL) {
            $this->mdb->select('master_area_create_by_super.region_code,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(array('master_area_create_by_super.region_level' => 4, 'master_area_create_by_super.app_id' => $_app_id));
            return $this->getData($this->mdb->get());
        } else {
            return FALSE;
        }
    
}

     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, master_user_details.profile_pic');
             $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
             ]);

             // Fetch the result as an array
             $query = $this->mdb->get();
             $result = $query->result_array();
             // Store data in an array
             $final_data = [];

             if (!empty($result)) {
                 foreach ($result as $row) {
                     $region_code = $row['region_code'];

                     // Fetch house count
                     $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();



                     // Fetch woman count
                     $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();

                     // Fetch child count
                     $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();

                     // Fetch pregnant woman count
                     $this->mdb->select('COUNT(*) as pregnant_woman_count');
                     $this->mdb->from('mira_pregnant_women');
                     $this->mdb->where([
                         'region_code'=> $region_code,
                         'pregnant_status'=>1
                     ]);
                     $pregnant_woman = $this->mdb->get()->row_array();


                     $data = [
                         'region_code' => $region_code,
                         'user_name' => $row['user_name'],
                         'region_name' => $row['region_name'],
                         'profile_pic' => $row['profile_pic'] // ✅ Add this line to include profile_pic
                     ];


                     // Conditionally add only non-null values
                     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'];
                     }

                     // Store data
                     $final_data[] = $data;
                 }

                 return $final_data; // Return final data array
             } else {
                 return []; // Return an empty array if no data is found
             }


             // Store the result in an array
             if (!empty($result)) {
                 return $result;  // Return array for further processing
             } else {
                 return [];  // Return an empty array if no data found
             }
         } else {
             return FALSE;
         }

     }
    
}
