Reports in Codeigniter

Reports in Codeigniter

Found a nice solution myself. If you want to generate reports in csv format it is very easy with codeigniter.
Your model function

function index(){
return $query = $this->db->get('my_table');
    /*
    Here you should note i am returning 
    the query object instead of 
    $query->result() or $query->result_array()
    */
}    

Now in controller

function get_report(){
    $this->load->model('my_model');
    $this->load->dbutil();
    $this->load->helper('file');
    /* get the object   */
    $report = $this->my_model->index();
    /*  pass it to db utility function  */
    $new_report = $this->dbutil->csv_from_result($report);
    /*  Now use it to write file. write_file helper function will do it */
    write_file('csv_file.csv',$new_report);
    /*  Done    */
}

No externals are required everything is available in codeigntier. Cheers!
If you want to write xml file it is easy too.

Just use xml_from_result() method of dbutil and use write_file('xml_file.xml,$new_report)
Visit these links they will help.

Database Utility Class

And

File Helper

Monthly Report in Codeigniter

What you need to do is combining database queries with a simple for loop in PHP.

First, you have to understand how to get all the values inside the month. cout_created is a timestamp/datetime field.
In MySQL, you can get the data using a query like this one:

SELECT * FROM yourtable WHERE MONTH(cout_created) = XX

Using this query, in PHP you will go for this:

// Initialize the array
$monthlyreport = array();
// For each month we are gonna do the same
for ($month = 1; $month <= 12; $month++) {
    // We get the results with database library, changing the sql according to our needs.
    $sql = "SELECT cout_result FROM yourtable WHERE MONTH(cout_created) = " . $month; 
    $query = $this->db->query($sql);
    // The accum variable to sum all the profits.
    $sum = 0;      
    // And foreach record, we sum the value to the actual one.
    foreach($query->result() as $row) {
       $sum = $sum + $row->cout_result;
    }
    // When finish, save the result on the array and start again.
    $montlyreport[$month] = $sum;
}

That would be the easiest way to understand how to do it, but we can do it even better. MySQL allow us to do the same by using its built-in SUM() function directly on MySQL, so we don't have to make additional processing on PHP. We can do this:

// Initialize the array
$monthlyreport = array();
// For each month we are gonna do the same
for ($month = 1; $month <= 12; $month++) {
    // But now we will get sum of the values instead of each value
    $sql = "SELECT SUM(cout_result) as profit FROM yourtable WHERE MONTH(cout_created) = " . $month; 
    $query = $this->db->query($sql);
    // And just save the profit
    $montlyreport[$month] = $query->row()->profit;
}

I have not tested it as I don't have a PHP environment here for testing, but let me know how it works and I'll update the answer accordingly.

EDIT: I have come with another solution that with solve it with only one query to the database, but it will depend of the performance with your database size and number of records:

SELECT SUM(cout_result) as profit, MONTH(cout_created) as mymonth
FROM yourtable
GROUP BY MONTH(cout_created)

With this, you just need to iterate with the foreach saving every profit directly by the mymonth

$sql = "SELECT SUM(cout_result) as profit, MONTH(cout_created) as mymonth FROM yourtable GROUP BY MONTH(cout_created)"
$query = $this->db->query($sql);
$monthlyreport = array();
foreach ($query->result() as $row) {
    $monthlyreport[$row->mymonth] = $row->profit;
}

Related Topics