<?php

defined('BASEPATH') OR exit('No direct script access allowed');

require_once APPPATH . '/libraries/Custom_Model.php';
class school_requirement_model extends Custom_Model {

		public function __construct() 
		{
        	parent::__construct();
        }

        public function getFilterDistricts()
        {
            $this->db->SELECT('STUDENTS_SCHOOL_CHILD_COUNT.district_id,STUDENTS_SCHOOL_CHILD_COUNT.district_name');
            $this->db->FROM(CSR_SCHOOL_REQUIREMENTS.' as CSR_SCHOOL_REQUIREMENTS');
            $this->db->JOIN(STUDENTS_SCHOOL_CHILD_COUNT.' as STUDENTS_SCHOOL_CHILD_COUNT','CSR_SCHOOL_REQUIREMENTS.school_id = STUDENTS_SCHOOL_CHILD_COUNT.school_id','LEFT');
            $this->db->WHERE('CSR_SCHOOL_REQUIREMENTS.isactive',1);
            $this->db->ORDER_BY('STUDENTS_SCHOOL_CHILD_COUNT.district_name','ASC');
            $this->db->GROUP_BY('STUDENTS_SCHOOL_CHILD_COUNT.district_id');
             $data = $this->db->GET()->result_array();
          //print_r($this->db->last_query());die();
          // print_r(count($data));die();
            return $data;
        }


        public function getFilterSchools($district_id)
        {
            $this->db->SELECT('STUDENTS_SCHOOL_CHILD_COUNT.school_id,STUDENTS_SCHOOL_CHILD_COUNT.school_name');
            $this->db->FROM(CSR_SCHOOL_REQUIREMENTS.' as CSR_SCHOOL_REQUIREMENTS');
            $this->db->JOIN(STUDENTS_SCHOOL_CHILD_COUNT.' as STUDENTS_SCHOOL_CHILD_COUNT','CSR_SCHOOL_REQUIREMENTS.school_id = STUDENTS_SCHOOL_CHILD_COUNT.school_id','LEFT');
            $this->db->WHERE('CSR_SCHOOL_REQUIREMENTS.isactive',1);
            $this->db->WHERE('STUDENTS_SCHOOL_CHILD_COUNT.district_id',$district_id);
            $this->db->ORDER_BY('STUDENTS_SCHOOL_CHILD_COUNT.school_name','ASC');
            $this->db->GROUP_BY('STUDENTS_SCHOOL_CHILD_COUNT.school_id');
             $data = $this->db->GET()->result_array();
          //print_r($this->db->last_query());die();
          // print_r(count($data));die();
            return $data;
        }


         public function getFilterMaterials()
        {
            $this->db->SELECT('CSR_SCHOOL_REQUIREMENTS.cat_id,CSR_CATEGORY_MASTER.cat_name,');
            $this->db->FROM(CSR_SCHOOL_REQUIREMENTS.' as CSR_SCHOOL_REQUIREMENTS');
             $this->db->JOIN(CSR_CATEGORY_MASTER.' as CSR_CATEGORY_MASTER','CSR_SCHOOL_REQUIREMENTS.cat_id = CSR_CATEGORY_MASTER.id and CSR_CATEGORY_MASTER.isactive = 1','LEFT');
            $this->db->WHERE('CSR_SCHOOL_REQUIREMENTS.isactive',1);
            $this->db->ORDER_BY('CSR_CATEGORY_MASTER.cat_name','ASC');
            $this->db->GROUP_BY('CSR_SCHOOL_REQUIREMENTS.cat_id');
             $data = $this->db->GET()->result_array();
            
             foreach($data as $key => $value)
             {
                $this->db->SELECT('CSR_SCHOOL_REQUIREMENTS.mat_id,CSR_MATERIAL_MASTER.material_name,');
                $this->db->FROM(CSR_SCHOOL_REQUIREMENTS.' as CSR_SCHOOL_REQUIREMENTS');
                $this->db->JOIN(CSR_MATERIAL_MASTER.' as CSR_MATERIAL_MASTER','CSR_SCHOOL_REQUIREMENTS.mat_id = CSR_MATERIAL_MASTER.id and CSR_MATERIAL_MASTER.isactive = 1','LEFT');
                $this->db->WHERE('CSR_SCHOOL_REQUIREMENTS.isactive',1);
                $this->db->WHERE('CSR_MATERIAL_MASTER.cat_id',$value['cat_id']);
                $this->db->ORDER_BY('CSR_MATERIAL_MASTER.material_name','ASC');
                $this->db->GROUP_BY('CSR_SCHOOL_REQUIREMENTS.mat_id');
                $materials = $this->db->GET()->result_array();
                $data[ $key ]['materials'] = $materials;
             }    
            return $data;
        }


        public function getSchoolReqList($from_date,$end_date,$schoold_id,$district_id,$mat_id,$cat_id,$is_featured)
        {
            $this->db->SELECT('CSR_SCHOOL_REQUIREMENTS.id as school_req_id,CSR_SCHOOL_REQUIREMENTS.is_featured, CSR_MATERIAL_MASTER.DEscription as desc,CSR_SCHOOL_REQUIREMENTS.school_id,STUDENTS_SCHOOL_CHILD_COUNT.district_id,STUDENTS_SCHOOL_CHILD_COUNT.block_id,STUDENTS_SCHOOL_CHILD_COUNT.district_name,STUDENTS_SCHOOL_CHILD_COUNT.district_name,STUDENTS_SCHOOL_CHILD_COUNT.block_name,STUDENTS_SCHOOL_CHILD_COUNT.udise_code,STUDENTS_SCHOOL_CHILD_COUNT.school_type,STUDENTS_SCHOOL_CHILD_COUNT.school_name,STUDENTS_SCHOOL_CHILD_COUNT.management,STUDENTS_SCHOOL_CHILD_COUNT.category,CSR_SCHOOL_REQUIREMENTS.mat_id,CSR_MATERIAL_MASTER.material_name,CSR_CATEGORY_MASTER.id as cad_id,CSR_CATEGORY_MASTER.cat_name,CSR_SUBCATEGORY_MASTER.id as sud_cat_id,CSR_SUBCATEGORY_MASTER.sub_cat_name, CSR_SCHOOL_REQUIREMENTS.qty,CSR_MATERIAL_MASTER.cost_per_unit,CSR_SCHOOL_REQUIREMENTS.created_by, CSR_SCHOOL_REQUIREMENTS.created_on, CSR_SCHOOL_REQUIREMENTS.updated_by, CSR_SCHOOL_REQUIREMENTS.updated_on,IFNULL(CSR_CONTRIBUTION_DETAILS.received_qty,0) as received_qty,replace(SCHOOLNEW_BASICINFO.address,"\\\n","<br>") as school_address,SCHOOLNEW_BASICINFO.pincode,UDISE_STAFFREG.teacher_name,UDISE_STAFFREG.mbl_nmbr');

            $this->db->FROM(CSR_SCHOOL_REQUIREMENTS.' as CSR_SCHOOL_REQUIREMENTS');
            $this->db->JOIN(CSR_MATERIAL_MASTER.' as CSR_MATERIAL_MASTER','CSR_SCHOOL_REQUIREMENTS.mat_id = CSR_MATERIAL_MASTER.id and CSR_MATERIAL_MASTER.isactive = 1','LEFT');
           // $this->db->JOIN(CSR_CONTRIBUTION_DETAILS.' as CSR_CONTRIBUTION_DETAILS','CSR_SCHOOL_REQUIREMENTS.id = CSR_CONTRIBUTION_DETAILS.req_id','LEFT');
            $this->db->JOIN('(SELECT req_id,sum(qty) as received_qty FROM csr_contribution_details where status = "FUNDS_RECEIVED" OR status = "MAT_RECEIVED" group by req_id) as CSR_CONTRIBUTION_DETAILS','CSR_SCHOOL_REQUIREMENTS.id = CSR_CONTRIBUTION_DETAILS.req_id','LEFT');
            $this->db->JOIN(CSR_CATEGORY_MASTER.' as CSR_CATEGORY_MASTER','CSR_MATERIAL_MASTER.cat_id = CSR_CATEGORY_MASTER.id and CSR_MATERIAL_MASTER.id = CSR_SCHOOL_REQUIREMENTS.mat_id and CSR_CATEGORY_MASTER.isactive = 1','LEFT');
            $this->db->JOIN(CSR_SUBCATEGORY_MASTER.' as CSR_SUBCATEGORY_MASTER','CSR_MATERIAL_MASTER.sub_cat_id = CSR_SUBCATEGORY_MASTER.id and CSR_SUBCATEGORY_MASTER.isactive = 1','LEFT');
            $this->db->JOIN(STUDENTS_SCHOOL_CHILD_COUNT.' as STUDENTS_SCHOOL_CHILD_COUNT','CSR_SCHOOL_REQUIREMENTS.school_id = STUDENTS_SCHOOL_CHILD_COUNT.school_id','LEFT');
             $this->db->JOIN(EMISUSER_TEACHER.' as EMISUSER_TEACHER_CREATED_BY','CSR_SCHOOL_REQUIREMENTS.created_by = EMISUSER_TEACHER_CREATED_BY.emis_username','LEFT');
             $this->db->JOIN(EMISUSER_TEACHER.' as EMISUSER_TEACHER_UPDATED_BY','CSR_SCHOOL_REQUIREMENTS.updated_by = EMISUSER_TEACHER_UPDATED_BY.emis_username','LEFT');
             $this->db->JOIN(SCHOOLNEW_BASICINFO.' as SCHOOLNEW_BASICINFO','CSR_SCHOOL_REQUIREMENTS.school_id = SCHOOLNEW_BASICINFO.school_id','LEFT');

             $this->db->JOIN(UDISE_STAFFREG.' as UDISE_STAFFREG','CSR_SCHOOL_REQUIREMENTS.school_id = UDISE_STAFFREG.school_key_id AND UDISE_STAFFREG.archive = 1 AND UDISE_STAFFREG.teacher_type in (27,28,29)','LEFT');
            $this->db->WHERE('CSR_SCHOOL_REQUIREMENTS.qty > ifnull(CSR_CONTRIBUTION_DETAILS.received_qty,0)');
            $this->db->WHERE('CSR_SCHOOL_REQUIREMENTS.isactive',1);
            //$this->db->WHERE('CSR_SCHOOL_REQUIREMENTS.qty > ifnull(CSR_CONTRIBUTION_DETAILS.received_qty,0)');
           //$this->db->ORDER_BY (RAND(1,1000));
            $this->db->ORDER_BY('CSR_SCHOOL_REQUIREMENTS.id','DESC');
           //  $this->db->WHERE('CSR_MATERIAL_MASTER.material_name !=',null);
            $this->db->WHERE('CSR_MATERIAL_MASTER.cost_per_unit != ',null);
            $this->db->LIMIT(1000);

            if($schoold_id != null)
            {
                 $this->db->WHERE('CSR_SCHOOL_REQUIREMENTS.school_id',$schoold_id);
            }
            if($district_id != null)
            {
                 $this->db->WHERE('STUDENTS_SCHOOL_CHILD_COUNT.district_id',$district_id);
            }
            if($mat_id != null)
            {
                 $this->db->WHERE('CSR_SCHOOL_REQUIREMENTS.mat_id',$mat_id);
            }
            if($cat_id != null)
            {
                 $this->db->WHERE('CSR_MATERIAL_MASTER.cat_id',$cat_id);
            }
            if($is_featured != null)
            {
                 $this->db->WHERE('CSR_SCHOOL_REQUIREMENTS.is_featured',$is_featured);
            }
            
            $data = $this->db->GET()->result_array();
         // print_r($this->db->last_query());die();
            return $data;

        }

         public function getIndividualSchoolReqData($schoold_id)
        {
            $this->db->SELECT('CSR_SCHOOL_REQUIREMENTS.id as school_req_id,max(CSR_SCHOOL_REQUIREMENTS.is_featured) as is_featured, CSR_SCHOOL_REQUIREMENTS.school_id,STUDENTS_SCHOOL_CHILD_COUNT.district_id,STUDENTS_SCHOOL_CHILD_COUNT.block_id,STUDENTS_SCHOOL_CHILD_COUNT.district_name,STUDENTS_SCHOOL_CHILD_COUNT.district_name,STUDENTS_SCHOOL_CHILD_COUNT.block_name,STUDENTS_SCHOOL_CHILD_COUNT.udise_code,STUDENTS_SCHOOL_CHILD_COUNT.school_type,STUDENTS_SCHOOL_CHILD_COUNT.school_name,STUDENTS_SCHOOL_CHILD_COUNT.management,STUDENTS_SCHOOL_CHILD_COUNT.category,sum(CSR_MATERIAL_MASTER.cost_per_unit * CSR_SCHOOL_REQUIREMENTS.qty) as total_cost,CSR_SCHOOL_REQUIREMENTS.created_by, CSR_SCHOOL_REQUIREMENTS.created_on, CSR_SCHOOL_REQUIREMENTS.updated_by, CSR_SCHOOL_REQUIREMENTS.updated_on,SCHOOLNEW_BASIC_DETAIL.latitude,SCHOOLNEW_BASIC_DETAIL.longitude,STUDENTS_SCHOOL_CHILD_COUNT.section_nos,STUDENTS_SCHOOL_CHILD_COUNT.low_class,STUDENTS_SCHOOL_CHILD_COUNT.high_class,STUDENTS_SCHOOL_CHILD_COUNT.total_b,STUDENTS_SCHOOL_CHILD_COUNT.total_g,STUDENTS_SCHOOL_CHILD_COUNT.total,STUDENTS_SCHOOL_CHILD_COUNT.teach_tot,STUDENTS_SCHOOL_CHILD_COUNT.nonteach_tot,STUDENTS_SCHOOL_CHILD_COUNT.totstaff,sum(CSR_CONTRIBUTION_DETAILS.amount) as total_recevied,SCHOOLNEW_INFRA_DETAIL.photo1,SCHOOLNEW_INFRA_DETAIL.photo2,SCHOOLNEW_INFRA_DETAIL.photo3,SCHOOLNEW_INFRA_DETAIL.photo4,SCHOOLNEW_INFRA_DETAIL.photo5');

            $this->db->FROM(CSR_SCHOOL_REQUIREMENTS.' as CSR_SCHOOL_REQUIREMENTS');
            $this->db->JOIN(CSR_MATERIAL_MASTER.' as CSR_MATERIAL_MASTER','CSR_SCHOOL_REQUIREMENTS.mat_id = CSR_MATERIAL_MASTER.id and CSR_MATERIAL_MASTER.isactive = 1','LEFT');
            $this->db->JOIN(STUDENTS_SCHOOL_CHILD_COUNT.' as STUDENTS_SCHOOL_CHILD_COUNT','CSR_SCHOOL_REQUIREMENTS.school_id = STUDENTS_SCHOOL_CHILD_COUNT.school_id','LEFT');
            $this->db->JOIN(EMISUSER_TEACHER.' as EMISUSER_TEACHER_CREATED_BY','CSR_SCHOOL_REQUIREMENTS.created_by = EMISUSER_TEACHER_CREATED_BY.emis_username','LEFT');
            $this->db->JOIN(EMISUSER_TEACHER.' as EMISUSER_TEACHER_UPDATED_BY','CSR_SCHOOL_REQUIREMENTS.updated_by = EMISUSER_TEACHER_UPDATED_BY.emis_username','LEFT');
            $this->db->JOIN(SCHOOLNEW_BASIC_DETAIL.' as SCHOOLNEW_BASIC_DETAIL','CSR_SCHOOL_REQUIREMENTS.school_id = SCHOOLNEW_BASIC_DETAIL.school_id','LEFT');
            $this->db->JOIN(CSR_CONTRIBUTION_DETAILS.' as CSR_CONTRIBUTION_DETAILS','CSR_SCHOOL_REQUIREMENTS.school_id = CSR_CONTRIBUTION_DETAILS.school_id and CSR_SCHOOL_REQUIREMENTS.id = CSR_CONTRIBUTION_DETAILS.req_id','LEFT');
            $this->db->JOIN(SCHOOLNEW_INFRA_DETAIL.' as SCHOOLNEW_INFRA_DETAIL','CSR_SCHOOL_REQUIREMENTS.school_id = SCHOOLNEW_INFRA_DETAIL.school_key_id','LEFT');
            $this->db->WHERE('CSR_SCHOOL_REQUIREMENTS.isactive',1);
            
            
            if(is_numeric($schoold_id))
            {
                $this->db->WHERE('CSR_SCHOOL_REQUIREMENTS.school_id',$schoold_id);
            }
            else
            {
                $this->db->GROUP_BY('CSR_SCHOOL_REQUIREMENTS.school_id','ASC');
                $this->db->WHERE('CSR_SCHOOL_REQUIREMENTS.is_featured',1);
            }
          
           
            
            $data = $this->db->GET()->result_array();
           //print_r($this->db->last_query());
            return $data;

        }

        function csrDashboardContent()
        {
         //  $no_of_schools_posted = $this->db->get_compiled_select('SELECT count(distinct school_id) FROM '.CSR_SCHOOL_REQUIREMENTS);
         
         //  $total_amount_required = $this->db->get_compiled_select('SELECT sum((qty * CSR_MATERIAL_MASTER.cost_per_unit)) as required_amount FROM '.CSR_SCHOOL_REQUIREMENTS.' as CSR_SCHOOL_REQUIREMENTS
         //    LEFT JOIN '.CSR_MATERIAL_MASTER.'as CSR_MATERIAL_MASTER on CSR_SCHOOL_REQUIREMENTS.mat_id = CSR_MATERIAL_MASTER.id');
         //  $no_of_schools_posted = $this->db->get_compiled_select('SELECT count(distinct school_id) FROM '.CSR_SCHOOL_REQUIREMENTS);

         //  $this->db->SELECT("count(distinct user_id) as supporters,sum(amount) as contributed_amount,($no_of_schools_posted) as no_of_schools_posted,($total_amount_required) as total_amount_required");
         //  $this->db->FROM(CSR_CONTRIBUTIONS);
         // // return $this->db->GET()->result_array();
         //  echo $no_of_schools_posted;
         //  echo $total_amount_required;

          $query = "SELECT count(distinct user_id) as supporters,ifnull(sum(amount),0) as contributed_amount,(SELECT count(distinct school_id) FROM ".CSR_SCHOOL_REQUIREMENTS.") as no_of_schools_posted,(SELECT sum(total) FROM ".STUDENTS_SCHOOL_CHILD_COUNT." where school_id in (SELECT distinct school_id FROM ".CSR_SCHOOL_REQUIREMENTS.")) as students_tot_count,(SELECT sum((qty * CSR_MATERIAL_MASTER.cost_per_unit)) as required_amount FROM ".CSR_SCHOOL_REQUIREMENTS." as CSR_SCHOOL_REQUIREMENTS LEFT JOIN ".CSR_MATERIAL_MASTER." as CSR_MATERIAL_MASTER on CSR_SCHOOL_REQUIREMENTS.mat_id = CSR_MATERIAL_MASTER.id WHERE CSR_SCHOOL_REQUIREMENTS.isactive = 1) as total_amount_required FROM ".CSR_CONTRIBUTIONS." as CSR_CONTRIBUTIONS WHERE CSR_CONTRIBUTIONS.status= 'FUNDS_RECEIVED'OR CSR_CONTRIBUTIONS.status= 'MAT_RECEIVED'";
            return $this->db->query($query)->result_array()[0];
            print_r($this->db->last_query());


        }

        function getStatusUpdates($req_id)
        {
            return $updates = $this->selectCustomRecords(array('*'),array('fk_req_id' => $req_id,'isactive' => 1),CSR_SCHOOL_REQ_UPDATES);
        }


}