I have Codeigniter project and I need to get sum of profit of budgets according to the year that mentioned in dropdown.

I have written the codes as mentioned below. But, when I am selecting 2021 option, it is not working.
Could you please show me the error of coding in below?

View

    <form>
              <div class="box-body">
               
                <div class="col-md-6">
                  <div class="form-group">
                    <label for="example">Select Year</label>
                    <select class="form-control" name="year" id="SelectOption">
                      <option value="">Select</option>
                      <option value="2015">2015</option>
                      <option value="2016">2016</option>
                      <option value="2017">2017</option>
                      <option value="2018">2018</option>
                      <option value="2019">2019</option>
                      <option value="2020">2020</option>
                      <option value="2021">2021</option>
                      
                        
                    </select>
                  </div>
                </div>
              </div>
              

 <div class="col-md-6">
 <div class="form-group">
 <label for="example">Profit</label>
 <input type="text" id="profitdiv" name="txtname" class="form-control" readonly>
 </div>
 </div>
              
 </form>


<script>

document.getElementById('SelectOption').addEventListener('change', function() 
{var val = $( "#SelectOption" ).val();
    
  console.log(val)
  if(val === '2021') {
    $.ajax({
                type: "POST",
                url : "<?php echo base_url(); ?>Reports/get_profit",
                data:{val:val},
                success: function(data){
                    $('#profitdiv').html(data);
                }
            });
    }
}
</script>

Controller

public function get_profit()
    {
        $this->load->model('Reports_model');
        $data=$this->Reports_model->select_profit();
        if($data!=='')
        {
            $this->session->set_flashdata('success', "Profit Show Succesfully"); 
        }else{
         $this->session->set_flashdata('error', "Sorry, Profit Showing Failed.");
        }
        redirect($_SERVER['HTTP_REFERER']);
        

    }

Model

function select_profit()
    {
      $this->db->select_sum("budget_tbl.surplus ");
       $this->db->from("budget_tbl");
       $this->db->like(‘surplus’,'2021-','after');
       $qry=$this->db->get();

    }

CodePudding user response：

How you record the date in your database? (format )

pass the year through your model

$year = $this -> input -> post("val")    
$data=$this->Reports_model->select_profit($year);

.

function select_profit($year)
    {
      $this->db->select_sum("budget_tbl.surplus ");
       $this->db->from("budget_tbl");
       $this->db->where(‘surplus >=’,$year.'-01-01');
       $qry=$this->db->get();
       return $qry -> result();    
    }

CodePudding user response：

full code

HTML

<form >
    <div class="box-body" >

        <div class="col-md-6" >
            <div class="form-group" >
                <label for="example" >Select Year</label >
                <select class="form-control" name="year" id="Year" >
                    <option value="" >Select</option >
                    <option value="2015" >2015</option >
                    <option value="2016" >2016</option >
                    <option value="2017" >2017</option >
                    <option value="2018" >2018</option >
                    <option value="2019" >2019</option >
                    <option value="2020" >2020</option >
                    <option value="2021" >2021</option >
                 </select >
            </div >
        </div >
    </div >


    <div class="col-md-6" >
        <div class="form-group" >
            <label for="example" >Profit</label >
            <input type="text" id="profitdiv" name="txtname" class="form-control" readonly >
        </div >
    </div >

</form >


<script >

    $("#Year").change(function () {
        var Year = $(this).val();

        $.ajax({
            type: "POST",
            url: "<?php echo base_url (); ?>index/get_profit",
            data: {Year: Year},
            success: function (data) {
                $('#profitdiv').html(data);
            }
        });

    })


</script >

Controller

public function get_profit ()
        {
            $this -> load -> model ( 'Reports_model' );
            $year = $this -> input -> post ( "val" );
            $data = $this -> Reports_model -> select_profit ( $year );

            if (!empty( $data )) {
                echo "Profit Show Succesfully";
            } else {
                echo "Sorry, Profit Showing Failed";
            }

        }

Model

function select_profit($year)
    {
      $this->db->select_sum("budget_tbl.surplus ");
       $this->db->from("budget_tbl");
       $this->db->where(‘surplus >=’,$year.'-01-01 00:00:00');
       $qry=$this->db->get();
       return $qry -> result();    
    }

CodePudding user response：

In your model, you will have to match the year to your date column.

In your existing code wherein you want sum of surplus for the year 2021, change the following line of your model:

$this->db->like(‘surplus’,'2021-','after');  

to

$this->db->like(‘name_of_generated_date_column’,'2021-','after');  

Add this line:

return $query->result();

To make it work for any selected year:

As mentioned in the post above, extract the year in your controller:

$year = $this -> input -> post("val")  

Send and retrieve data to/from your model

$data=$this->Reports_model->select_profit($year);  

In the model:

function select_profit($year)  
    {  
...  
       $this->db->like(‘name_of_generated_date_column’,$year,'after');  
...  
return $query->result();
    }