PHP File Manager

Editing File: Reports.php

<?php

/* 
 * Copyright (C) Wayne Purton-Smith - All Rights Reserved
 * Unauthorized copying of this file or removing this paragraph, via any medium is strictly prohibited
 * Proprietary and confidential
 * Written by Wayne Purton-Smith <waynepurtonsmith@hotmail.co.uk> February 2014
 */

class Reports extends CI_Model {
    
    public function get($report_id = 0, $filters = array(), $skip = 0, $limit = 30)
    {
        $filters = array_merge(array
        (
            'date_from'     => NULL,
            'date_to'       => NULL
        ), $filters);
        
        $this_month_start = strtotime('first day of this month 00:00:00');
        $this_month_end = strtotime('last day of this month 23:59:59');
        
        $sql = "SELECT `r`.*,
                       `rt`.`name` AS `template_name`
                FROM 
                (
                    `reports` `r`
                    
                    LEFT OUTER JOIN `report_templates` `rt` ON `rt`.`template_id` = `r`.`template_id`
                )
                WHERE `r`.`is_deleted` = 0";
        
        if(($report_id = (int) $report_id) > 0)
        {
            $sql .= " AND `r`.`report_id` = $report_id ";
            $limit = 1;
        }
        
        $date_from = (($date_from = (int) $filters['date_from']) > 0) ? $date_from : NULL;
        $date_to = (($date_to = (int) $filters['date_to']) > 0) ? $date_to : NULL;
        
        if(!$date_from && $date_to)
        {
            $sql .= " AND `r`.`generated_date` <= $date_to ";
        }
        elseif($date_from && !$date_to)
        {
            $sql .= " AND `r`.`generated_date` >= $date_from ";
        }
        elseif($date_from && $date_to)
        {
            $sql .= " AND ( `r`.`generated_date` >= $date_from AND `r`.`generated_date` <= $date_to ) ";
        }
                
        $sql .= " GROUP BY `r`.`report_id`
                  ORDER BY `r`.`generated_date` DESC ";
        
        $sql .= $this->common->returnOffset($skip, $limit);
        
        $result = $this->db->query($sql);
        
        //preprint($this->db->last_query());
        
        if($result->num_rows())
        {
            $rows = $result->result();
            $result->free_result();
            
            return ($report_id > 0 && $limit === 1) ? reset($rows) : $rows;
        }
        
        return array();
    }
    
    public function generate($report_id = 0, $name = NULL, $date_from = 0, $date_to = 0)
    {
        $report_id = (($report_id = (int) $report_id) > 0) ? $report_id : 0;
        $report_data = ($report_id) ? $this->get($report_id) : NULL;
        
        if($report_id && !$report_data)
        {
            return false;
        }
        
        $date_from = (is_string($date_from)) ? strtotime($date_from) : NULL;
        $date_to = (is_string($date_to)) ? strtotime($date_to) : NULL;
        
        $date_from = (($date_from = (int) $date_from) > 0) ? strtotime('00:00:00', $date_from) : NULL;
        $date_to = (($date_to = (int) $date_to) > 0) ? strtotime('23:59:59', $date_to) : NULL;
        
        $orders = $this->orders->get(NULL, NULL, array
        (
            'order_date_from'    => $date_from,
            'order_date_to'      => $date_to
        ), -1);
        
        $data[] = array('Order ID', 'First name', 'Last name', 'Delivery address', 'Ordered date', 'Shipping date', 'VAT', 'Discount', 'Total', 'Total incl. VAT');
        
        foreach($orders as $order_info)
        {
            $invoice_full_name = format_name($order_info->invoice_first_name, $order_info->invoice_last_name);
            $invoice_address = format_address($order_info->invoice_address_1, $order_info->invoice_address_2, $order_info->invoice_address_3, $order_info->invoice_town, $order_info->invoice_county, $order_info->invoice_postcode, $order_info->invoice_country);
            
            $data[] = array
            (
                $order_info->order_number,
                $invoice_full_name,
                $invoice_address,
                date('d/m/Y', $order_info->ordered_date),
                ($order_info->delivery_date) ? date('d/m/Y', $order_info->delivery_date) : 'N/A',
                $order_info->vat,
                $order_info->discounted_amount,
                $order_info->total,
                $order_info->total_inc_vat
            );
        }
        
        $name = (strlen(($name = trim($name)))) ? $name : 'Generated report';
        $file_name = url_title($name . ' ' . date('d-m-Y \a\t H-i-s') . '-' . rand(10000, 999999), '-', true) . '.csv';
        $file_location = 'assets/reports/' . $file_name;
        
        if(($file_size = $this->convertToCsv($data, $file_location)))
        {
            $this->db->query($this->db->insert_string('reports', array
            (
                'report_name'       => $name,
                'date_from'         => $date_from,
                'date_to'           => $date_to,
                'generated_date'    => time(),
                'record_count'      => (count($data) - 1),
                'file_name'         => $file_name,
                'file_size'         => $file_size
            )));
            
            return ($this->db->affected_rows() === 1);
        }
        
        return false;
    }
    
    public function templates($template_id = 0, $skip = 0, $limit = 30)
    {
        $sql = "SELECT *
                FROM `templates`
                WHERE `is_deleted` = 0";
        
        $sql .= (($template_id = (int) $template_id) > 0)? " AND `template_id` = $template_id" : '';
        
        $sql .= $this->common->returnOffset($skip, $limit);
    }
    
    public function convertToCsv($data = array(), $file_name = NULL)
    {
        $buffer = fopen('data://text/plain,' . "", 'w+');
        
        foreach($data as $val) 
        {
            fputcsv($buffer, $val);
        }
        
        rewind($buffer);
        $csv_contents = stream_get_contents($buffer);
        
        if($file_name)
        {
            if(!file_put_contents($file_name, $csv_contents))
            {
                return false;
            }
        }
        
        fclose($buffer);
        
        return ($file_name) ? filesize($file_name) : $csv_contents;
    }
    
    public function download($report_id = 0)
    {
        if(($report_id = (int) $report_id) > 0 && ($report_info = $this->get($report_id)))
        {
            $report_file = 'assets/reports/' . $report_info->file_name;
            
            if(!file_exists($report_file))
            {
                exit('Report does not exist on the server');
            }
            
            header("Content-Type: text/csv", true);
            header("Content-Length: " . filesize($report_file), true);
            header("Content-Disposition: attachment; filename=\"" . str_replace('"', '\"', $report_info->report_name) . ".csv\"");
            
            readfile($report_file);
            exit;
        }
        
        exit('Report does not exist in the database');
    }
    
    public function delete($report_id = 0)
    {
        if(($report_id = (int) $report_id) > 0)
        {
            $this->db->query($this->db->update_string('reports', array('is_deleted' => 1), "`report_id` = $report_id"));
            return ($this->db->affected_rows() === 1);
        }
        
        return false;
    }
    
    public function templateVariables()
    {
        return array
        (
            'Order ID'                  => 'order_id',
            'Order number'              => 'order_number',
            'Ref order ID'              => 'ref_order_id',
            'Invoice first name'        => 'invoice_first_name',
            'Invoice last name'         => 'invoice_last_name',
            'Invoice business'          => 'invoice_business',
            'Invoice address 1'         => 'invoice_address_1',
            'Invoice address 2'         => 'invoice_address_2',
            'Invoice address 3'         => 'invoice_address_3',
            'Invoice town'              => 'invoice_town',
            'Invoice county'            => 'invoice_county',
            'Invoice postcode'          => 'invoice_postcode',
            'Invoice country'           => 'invoice_country',
            'Invoice phone'             => 'invoice_phone',
            'Invoice mobile'            => 'invoice_mobile',
            'Invoice email'             => 'invoice_email',
            'Delivery first name'       => 'delivery_first_name',
            'Delivery last name'        => 'delivery_last_name',
            'Delivery business'         => 'delivery_business',
            'Delivery address 1'        => 'delivery_address_1',
            'Delivery address 2'        => 'delivery_address_2',
            'Delivery address 3'        => 'delivery_address_3',
            'Delivery town'             => 'delivery_town',
            'Delivery county'           => 'delivery_county',
            'Delivery postcode'         => 'delivery_postcode',
            'Delivery country'          => 'delivery_country',
            'Delivery phone'            => 'delivery_phone',
            'Delivery mobile'           => 'delivery_mobile',
            'Delivery email'            => 'delivery_email',
            'Ordered date'              => 'ordered_date',
            'Shipping date'             => 'delivery_date',
            'Total incl. VAT'           => 'total_inc_vat',
            'Total discounted'          => 'discounted_amount'
        );
    }
}

Cancel