PHP File Manager

Editing File: Accounting.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 Accounting extends CI_Model {
    
    public function get($report_id = 0, $tab_id = 0, $filters = array(), $skip = 0, $limit = 30)
    {
        $filters = array_merge(array
        (
            'date'          => NULL,
            'group_date'    => false
        ), (array) $filters);
        
        $sql = "SELECT *,
                       ((`r`.`total_takings` + `r`.`card_sales`) - `r`.`total_float` + `r`.`cash_out` - `r`.`stall_costs`) AS `grand_total`,
                       (((`r`.`total_takings` + `r`.`card_sales`) - `r`.`total_float` + `r`.`cash_out` - `r`.`stall_costs`) - `r`.`point_of_sale`) AS `sale_difference`,
                       IF(`total_takings` <> 0, 1, 0) AS `has_end_result`
                FROM
                (
                    SELECT `r`.*,
                           SUM(
                               (`r`.`cash_twenties` * 20) + 
                               (`r`.`cash_tenners` * 10) + 
                               (`r`.`cash_fivers` * 5) + 
                               (`r`.`cash_two_pounds` * 2) + 
                               (`r`.`cash_one_pounds` * 1) + 
                               (`r`.`cash_fifty_pences` * 0.50) + 
                               (`r`.`cash_twenty_pences` * 0.20) + 
                               (`r`.`cash_ten_pences` * 0.10) + 
                               (`r`.`cash_five_pences` * 0.05) + 
                               (`r`.`cash_two_pences` * 0.02) + 
                               (`r`.`cash_one_pences` * 0.01)
                            ) AS `total_float`,
                            
                            SUM(
                               (`r`.`till_twenties` * 20) + 
                               (`r`.`till_tenners` * 10) + 
                               (`r`.`till_fivers` * 5) + 
                               (`r`.`till_two_pounds` * 2) + 
                               (`r`.`till_one_pounds` * 1) + 
                               (`r`.`till_fifty_pences` * 0.50) + 
                               (`r`.`till_twenty_pences` * 0.20) + 
                               (`r`.`till_ten_pences` * 0.10) + 
                               (`r`.`till_five_pences` * 0.05) + 
                               (`r`.`till_two_pences` * 0.02) + 
                               (`r`.`till_one_pences` * 0.01)
                            ) AS `total_takings`,
                            
                            `t`.`stalled`,
                            `t`.`reports_inc_vat`
                    FROM
                    (
                        `accounting_reports` `r`

INNER JOIN `accounting_tabs` `t` ON `t`.`tab_id` = `r`.`tab_id`
                    )
                    WHERE `r`.`is_deleted` = 0";
        
        $return_flat_row = false;
        
        if(($report_id = (int) $report_id) > 0)
        {
            $sql .= " AND `r`.`report_id` = $report_id ";
            $limit = 1;
        }
        
        if(($tab_id = (int) $tab_id) > 0)
        {
            $sql .= " AND `r`.`tab_id` = $tab_id ";
        }
        
        if(($report_date = (int) $filters['date']) > 1)
        {
            $sql .= " AND `r`.`date` = $report_date ";
            
            if($tab_id)
            {
                $return_flat_row = true;
                $limit = 1;
            }
        }
        
        if($filters['group_date'])
        {
            $sql .= " GROUP BY DATE_FORMAT(FROM_UNIXTIME(`r`.`date`), '%e-%m-%Y')";
        }
        else
        {
            $sql .= " GROUP BY `r`.`report_id`";
        }
        
        $sql .= " ORDER BY `r`.`date` DESC";
        
        $sql .= " ) AS `r`";
        
        $sql .= $this->common->returnOffset($skip, $limit);
        
        $result = $this->db->query($sql);

if(isset($_SERVER['HTTP_USER_AGENT']) && strpos($_SERVER['HTTP_USER_AGENT'], 'rdit-dev') !== false)
        {
            //preprint($this->db->last_query());
        }
        
        if($result->num_rows())
        {
            $rows = $result->result();
            $result->free_result();

return (($report_id > 0 || $return_flat_row) && $limit === 1) ? reset($rows) : $rows;
        }
        
        return array();
    }
    
    public function getTabs($tab_id = 0, $skip = 0, $limit = 30)
    {
        $sql = "SELECT `t`.*,
                       IF(`total_reports` > 0, `total_reports`, 0) AS `total_reports`
                FROM
                (
                    `accounting_tabs` `t`
                    
                    LEFT OUTER JOIN
                    (
                        SELECT COUNT(`report_id`) AS `total_reports`,
                               SUM(
                                   (`cash_twenties` * 20) + 
                                   (`cash_tenners` * 10) + 
                                   (`cash_fivers` * 5) + 
                                   (`cash_two_pounds` * 2) + 
                                   (`cash_one_pounds` * 1) + 
                                   (`cash_fifty_pences` * 0.50) + 
                                   (`cash_twenty_pences` * 0.20) + 
                                   (`cash_ten_pences` * 0.10) + 
                                   (`cash_five_pences` * 0.05) + 
                                   (`cash_two_pences` * 0.02) + 
                                   (`cash_one_pences` * 0.01)
                                ) AS `total_float`,
                               `tab_id`
                        FROM `accounting_reports`
                        WHERE `is_deleted` = 0
                        GROUP BY `tab_id`
                    ) AS `r` ON `r`.`tab_id` = `t`.`tab_id`
                )
                WHERE `t`.`is_deleted` = 0";
        
        if(($tab_id = (int) $tab_id) > 0)
        {
            $sql .= " AND `t`.`tab_id` = $tab_id ";
            $limit = 1;
        }
        
        $return_flat_row = false;
        
        $sql .= $this->common->returnOffset($skip, $limit);
        
        $result = $this->db->query($sql);

if($result->num_rows())
        {
            $rows = $result->result();
            $result->free_result();

return (($tab_id > 0 || $return_flat_row) && $limit === 1) ? reset($rows) : $rows;
        }
        
        return array();
    }
    
    public function save($tab_id = 0, $data = array(), $report_id = 0)
    {
        $report_id = (($report_id = (int) $report_id) > 0) ? $report_id : 0;
        $is_update = ($report_id > 0);
        
        $tab_id = (($tab_id = (int) $tab_id) > 0) ? $tab_id : 0;
        
        if(!$is_update && !($tab_id > 0 && ($tab_info = $this->getTabs($tab_id))))
        {
            return $this->common->setResponseMessage('Select the account for this report to be saved to');
        }
        
        $tab_info = (!$is_update) ? $tab_info : $this->get($report_id);
        
        $tab_is_stall = ($tab_info->stalled);
        
        $report_date = (isset($data['report_date']) && strlen(($report_date = trim($data['report_date']))) && ($report_date = strtotime($data['report_date'] . ' 00:00:00')) > 1) ? $report_date : NULL;
        
        if(!$is_update && !$report_date)
        {
            return $this->common->setResponseMessage('The report date is required');
        }
        
        if(date('N', $report_date) == 7)
        {
            //return $this->common->setResponseMessage('A report on a Sunday? Really?');
        }
        
        if($report_date >= strtotime('tomorrow 00:00:00'))
        {
            //return $this->common->setResponseMessage('Reporting for the future? Really?');
        }
        
        if(!$is_update)
        {
            if(($this->get(NULL, $tab_id, array('date' => $report_date))))
            {
                return $this->common->setResponseMessage('A report already exists for this date, please edit the existing report instead');
            }
        }
        
        $cash_out = (!$tab_is_stall && isset($data['cash_out'])) ? abs((float) $data['cash_out']) : 0;
        $card_sales = (!$tab_is_stall && isset($data['card_sales'])) ? abs((float) $data['card_sales']) : 0;
        $stall_costs = ($tab_is_stall && isset($data['stall_costs'])) ? abs((float) $data['stall_costs']) : 0;
        
        $actual_report = (!$tab_is_stall && isset($data['actual_report']) && ($actual_report = (float) $data['actual_report']) !== 0) ? $actual_report : 0;
        $point_of_sale = (!$tab_is_stall && isset($data['point_of_sale']) && ($point_of_sale = (float) $data['point_of_sale']) !== 0) ? $point_of_sale : 0;
        
        foreach(array('start', 'end') as $float_type)
        {
            foreach($this->getCashTypes() as $money_type => $money_amount)
            {
                $input_name = $float_type . '_' . $money_type;
                $$input_name = (isset($data[$input_name])) ? (int) $data[$input_name] : 0;
            }
        }
        
        $sql_data = array
        (
            'tab_id'                => $tab_id,
            'date'                  => $report_date,
            
            'cash_out'              => $cash_out,
            'card_sales'            => $card_sales,
            'actual_report'         => $actual_report,
            'point_of_sale'         => $point_of_sale,
            'stall_costs'           => $stall_costs,
            
            'cash_twenties'         => $start_twenties,
            'cash_tenners'          => $start_tenners,
            'cash_fivers'           => $start_fivers,
            'cash_two_pounds'       => $start_two_pounds,
            'cash_one_pounds'       => $start_one_pounds,
            'cash_fifty_pences'     => $start_fifty_pences,
            'cash_twenty_pences'    => $start_twenty_pences,
            'cash_ten_pences'       => $start_ten_pences,
            'cash_five_pences'      => $start_five_pences,
            'cash_two_pences'       => $start_two_pences,
            'cash_one_pences'       => $start_one_pences,
            
            'till_twenties'         => $end_twenties,
            'till_tenners'          => $end_tenners,
            'till_fivers'           => $end_fivers,
            'till_two_pounds'       => $end_two_pounds,
            'till_one_pounds'       => $end_one_pounds,
            'till_fifty_pences'     => $end_fifty_pences,
            'till_twenty_pences'    => $end_twenty_pences,
            'till_ten_pences'       => $end_ten_pences,
            'till_five_pences'      => $end_five_pences,
            'till_two_pences'       => $end_two_pences,
            'till_one_pences'       => $end_one_pences
        );
        
        if($is_update)
        {
            unset($sql_data['date']);
            
            $sql = $this->db->update_string('accounting_reports', $sql_data, "`report_id` = $report_id");
        }
        else
        {
            $sql = $this->db->insert_string('accounting_reports', $sql_data);
        }
        
        $result = $this->db->query($sql);
        
        if((!$is_update && $this->db->affected_rows() === 1) || $is_update)
        {
            $this->common->setResponseMessage('The report was successfully saved', true);
            
            return ($is_update) ? $report_id : $this->db->insert_id();
        }
        
        return $this->common->setResponseMessage('The report could not be saved at this time - try again');
    }
    
    public function saveTab($data = array(), $tab_id = 0)
    {
        $tab_id = (($tab_id = (int) $tab_id) > 0) ? $tab_id : 0;
        $is_update = ($tab_id > 0);
        
        $account_name = (isset($data['account_name']) && ($account_name = trim($data['account_name']))) ? $account_name : NULL;
        
        if(!$account_name)
        {
            return $this->common->setResponseMessage('A name is required for this account');
        }
        
        $is_stalled = (isset($data['stalled']) && (int) $data['stalled'] === 1);
        $reports_include_vat = (isset($data['include_vat']) && (int) $data['include_vat'] === 1);
        
        $sql_data = array
        (
            'name'              => $account_name,
            'stalled'           => $is_stalled,
            'reports_inc_vat'   => $reports_include_vat
        );
        
        if($is_update)
        {
            $sql = $this->db->update_string('accounting_tabs', $sql_data, "`tab_id` = $tab_id");
        }
        else
        {
            $sql = $this->db->insert_string('accounting_tabs', $sql_data);
        }
        
        $result = $this->db->query($sql);
        
        if((!$is_update && $this->db->affected_rows() === 1) || $is_update)
        {
            $this->common->setResponseMessage('The account was successfully saved', true);
            
            return ($is_update) ? $tab_id : $this->db->insert_id();
        }
        
        return $this->common->setResponseMessage('The account could not be saved at this time - try again');
    }
    
    public function getCashTypes()
    {
        return array
        (
            'twenties'          => 20, 
            'tenners'           => 10, 
            'fivers'            => 5, 
            'two_pounds'        => 2, 
            'one_pounds'        => 1, 
            'fifty_pences'      => 0.50, 
            'twenty_pences'     => 0.20, 
            'ten_pences'        => 0.10, 
            'five_pences'       => 0.05, 
            'two_pences'        => 0.02, 
            'one_pences'        => 0.01
        );
    }
    
    public function getDefaultCash($tab_id = 1)
    {
        $sql = "SELECT *
                FROM `accounting_tab_defaults`";
        
        if(($tab_id = (int) $tab_id) > 0)
        {
            $sql .= " WHERE `tab_id` = $tab_id";
        }
        
        $result = $this->db->query($sql);
        
        if($result->num_rows() > 0)
        {
            if($tab_id > 0)
            {
                $row = $result->row_array();
         
                $keys = array_keys($row);
                
                foreach($keys as &$key)
                {
                    $key = str_replace('default_', '', $key);
                }
                
                return array_combine($keys, array_values($row));
            }
            else
            {
                $rows = $result->result();
                $result->free_result();
            
                return $rows;
            }
        }
        
        return array();
    }
    
    public function deleteReport($report_id = 0)
    {
        if(($report_id = (int) $report_id) > 0)
        {
            $this->db->query($this->db->update_string('accounting_reports', array('is_deleted' => 1), "`report_id` = $report_id"));
            return ($this->db->affected_rows() === 1);
        }
        
        return false;
    }
    
    public function deleteTab($tab_id = 0)
    {
        if(($tab_id = (int) $tab_id) > 0)
        {
            $this->db->query($this->db->update_string('accounting_tabs', array('is_deleted' => 1), "`tab_id` = $tab_id"));
            return ($this->db->affected_rows() === 1);
        }
        
        return false;
    }
}

Cancel