How to use the between command in codeigniter to generate report

1

I need to generate the following report:

The user selects the time interval (start date and end date) and the revenue report is generated showing the value of the added revenues.

Here is the photo of the view that calls the function in the Model class:

ViewCode(photoabove):

<divclass="col-md-12 col-xs-11"> 
    <div class="page-header">
        <h4 align="center"><strong>Relatório de Receitas</strong></h4>
    </div>                       
    <form action="<?= base_url() ?>receita/pagas" method="post">
        <div class="row">
            <div class="col-md-4 col-md-offset-5" >                                           
                <label>Data do recebimento entre:</label>
            </div>
        </div>
        <div class="row">
            <div class="col-md-2 col-md-offset-4" >                                           
                <div id="datepicker1" class="input-group date" data-date-format="dd-mm-yyyy">
                    <input id="dtrecinicio" class="form-control" type="text" name="dtrecinicio" required>
                    <span class="input-group-addon"><i class="glyphicon glyphicon-calendar"></i></span>
                </div>
                <script type="text/javascript">
                    $(function () {
                        $("#datepicker1").datepicker({
                            autoclose: true,
                            todayHighlight: true,
                            language: "pt-BR"
                        }).dtrecinicio('update', new Date());
                    });
                </script>                
            </div>
            <div class="col-md-1"> 
                <label>E</label>
            </div>
            <div class="col-md-2">                   
                <div id="datepicker2" class="input-group date" data-date-format="dd-mm-yyyy">
                    <input id="dtrecfim" class="form-control" type="text" name="dtrecfim" required>
                    <span class="input-group-addon"><i class="glyphicon glyphicon-calendar"></i></span>
                </div>
                <script type="text/javascript">
                    $(function () {
                        $("#datepicker2").datepicker({
                            autoclose: true,
                            todayHighlight: true,
                            language: "pt-BR"
                        }).dtrecfim('update', new Date());
                    });
                </script>
            </div>
        </div>                
        <br>
        <br>
        <div class="row">            
            <div class="col-md-10 col-xs-12">
                <div class="col-md-3 col-md-offset-6">
                    <div class="form-group">
                        <button class="btn btn-md btn-primary btn-block" type="submit">GERAR</button>                
                    </div>
                </div>                               
            </div>
        </div>
        <div class="row">                 
            <div class="col-md-10 col-xs-12">
                <div class="col-md-3 col-md-offset-6">
                    <div class="form-group">
                        <a class="btn btn-md btn-default btn-block hidden-print" href="<?= base_url(); ?>">CANCELAR</a>
                    </div>
                </div>                                        
            </div>                                        
        </div>
    </form>
</div> 

Model Class Function:

    function get_receitas_like() {
        $inicio = $this->input->post('dtrecinicio');
        $fim = $this->input->post('dtrecfim');
//        $this->db->select('*');
        $this->db->select_sum("valor_receita");

        $this->db->where('dtrecebimento' >= $inicio);
        $this->db->where('dtrecebimento' <= $fim);
//        $this->db->where('dtrecebimento' BETWEEN  AND $dtrecebimento2');
//        $this->db->where('dtrecebimento', $termo);
        return $this->db->get('receitas')->result();
    }

View code that displays aggregated data:

<div class="row">
        <div class="col-md-12">
            <p align="center"><font  size="3" face="helvetica"><u><strong>RELATÓRIO - RECEITAS</strong></u></font></p>                                                           
        </div>
    </div>          
    <div class="row">
        <div class="col-md-5 col-md-offset-4 col-xs-12">
            <div class="table-responsive"> 
                <table class="table table-striped">
                    <tr class="row">                         
                        <td><strong>Valor total recebido no período selecionado:</strong> R$ <?php echo number_format($receitas[0]->valor_receita,2,",","."); ?></td>                                                                                             
                    </tr>                     
                    <tr class="row">                                   
                        <td></td> 
                        <td></td>                                                        
                    </tr>                     
                </table>
            </div>
        </div> 
    </div>

Photo of the Recipes table:

    
asked by anonymous 17.07.2018 / 23:47

1 answer

2

Some modifications:

If your date is in the correct format the date that comes from the screen is not, it needs to be converted to Date and then in SQL it passes the corresponding format, eg:

$inicio = DateTime::createFromFormat('d-m-Y', $this->input->post('dtrecinicio'));
$fim = DateTime::createFromFormat('d-m-Y', $this->input->post('dtrecfim'));

and change:

$this->db->where('dtrecebimento' >= $inicio));
$this->db->where('dtrecebimento' <= $fim);

for

$this->db->where('dtrecebimento >=', $inicio->format('Y-m-d'));
$this->db->where('dtrecebimento <=', $fim->format('Y-m-d'));

The signals must be within the first parameter.

Reference: Custom Where

    
18.07.2018 / 01:04