<?php
/**
 * CodeIgniter library
 *
 * @category CodeIgniter
 * @license /_licenses/untermStrich/agb_normal.pdf
 * @author Christian Koller
 * @copyright 2009 untermStrich
 * @package us_libraries
 * @version $Id$
*/

if (!defined('BASEPATH')) { exit('No direct script access allowed'); }

require_once APPPATH.'us_libraries/Report_data.php';

/**
 * Used to create, save, load and assign data for the report
 *
 * @property    My_Controller           $CI                     The main CI object
 */
class Calcexp_analysis extends Report_data
{
    
    /**
     * Create the data
     * 
     * @param   object      $request_vars           Request vars
     * @param   integer     $fee_multi_mode_set     Optional Fee multiplier set item_id
     * @return  void
     */
    public function create_data($request_vars, $fee_multi_mode_set=null)
    {
        self::$CI->load->model('Projects_model', 'projects', true);
        self::$CI->load->model('Category_rates_model', 'category_rates');
        self::$CI->load->model('Times_model', 'times');
        self::$CI->load->model('Extras_model', 'extras');
        self::$CI->load->model('Travels_model', 'travels');
        self::$CI->load->model('Externalexpenses_model', 'externalexpenses');
        self::$CI->load->library('Category_rates_tools', null, 'category_rates_tools');
        self::$CI->load->helper('db_where_grouped');
        self::$CI->load->helper('assoc_array');
        
        self::init_temp_table();
        
        $projects = self::$CI->projects->get_simple_list(
            !$request_vars->only_active_projects,
            true,
            false,
            false,
            Projects_model::CALCEXP_FULLINFO,
            $request_vars
        );
        foreach ($projects as $project) {
            $data = array(
                'id'            => $project->id,
                'project'       => $project->project,
                'threshold'     => isset($project->calcexp_threshold) ? $project->calcexp_threshold : 0,
            );
            
            $request_vars_last_date = new stdClass();
            $request_vars_last_date->project = $project->id;
            $request_vars_last_date->is_project = 1;
            $last_obj = self::$CI->category_rates_tools->get_last_calcexp_deducted_date(
                $request_vars_last_date
            );
            unset($request_vars_last_date);
            
            $data['last_date_str'] = '';
            if (!empty($last_obj)) {
                if(!empty($last_obj->date)) {
                    $data['last_date_str'] = $last_obj->date->us_date();
                }
            }
            
            self::$tempdb->simple_query(self::$tempdb->insert_string(self::$temptable, $data)); 
        }
        unset($projects);
        
        $request_vars->calcexp_id = 'all';
        if ($request_vars->calcexp_type==false) {
            $type_times = Category_rates_model::T_WORK_PACKAGE;
        } else {
            $type_times = Category_rates_model::T_TIMES;
        }
        
        $this->_add_values($type_times, $request_vars, false);
        
        $request_vars->only_active = true; //Only entries without ignore flag
        $this->_add_values($type_times, $request_vars, true);
    }
    
    /**
     * Assign values of the report to smarty
     * 
     * @param   object  $request_vars   Optional Request vars
     * 
     * @return  void
     */
    public function assign($request_vars=null)
    {
        $tempdb = self::$tempdb;
        $temptable = self::$temptable;
        
        if ($request_vars===null) {
            die('Missing request vars');
        }
        
        $warn_limit = intval($request_vars->warn_limit);
        if ($warn_limit<50 || $warn_limit>100) {
            $warn_limit = 90;
        }
        
        $tempdb->select('*');
        $tempdb->select('open_times_number + deducted_times_number AS total_number');
        $tempdb->select('open_total + deducted_total AS total');
        $tempdb->select('CASE WHEN threshold>0 THEN (open_total + deducted_total) / threshold ELSE 0 END   AS threshold_reached', false);
        
        $tempdb->where('open_total + deducted_total >', 0);
        
        if ($request_vars->only_threshold_reached) {
            $tempdb->where('open_total + deducted_total > threshold');
        }
        
        switch ($request_vars->order_by) {
            case 'open_total':
                $tempdb->order_by('open_total', 'desc');
                break;
            case 'threshold_reached':
                $tempdb->order_by('threshold_reached', 'desc');
                break;
            case 'threshold':
                $tempdb->order_by('threshold', 'desc');
                break;
            case 'total_number':
                $tempdb->order_by('total_number', 'desc');
                break;
            case 'total':
                $tempdb->order_by('total', 'desc');
                break;
            case 'deducted_total':
                $tempdb->order_by('deducted_total', 'desc');
                break;
            case 'project':
            default:
                $tempdb->order_by('project', 'asc');
        }
        
        $query = $tempdb->get($temptable);
        self::$CI->smarty->assign('warn_limit', $warn_limit/100);
        self::$CI->smarty->assign('calcexp_analysis', $query->result());
    }
    
    /**
     * Add values for either deducted or open
     * 
     * @param   integer     $type_times         One of the Category_rates_model::T_ constants defining the type of rate to load
     * @param   object      $request_vars       Request vars
     * @param   boolean     $deducted           Get deducted or non deducted
     * @return  void
     */
    private function _add_values($type_times, $request_vars, $deducted)
    {
        $tempdb = self::$tempdb;
        $temptable = self::$temptable;
        
        if ($deducted) {
            $suffix = 'deducted';
        } else {
            $suffix = 'open';
        }
        
        $total_open_times = self::$CI->times->get_calcexp_sum($deducted, $type_times, $request_vars, false, true);
        foreach ($total_open_times as $total) {
            $update_sql = 'UPDATE '.$temptable.' SET ';
            $update_sql .= $suffix.'_times_number = '.round($total->number ?? 0, 2).', ';
            $update_sql .= $suffix.'_times = '.round($total->total ?? 0, 2).', ';
            $update_sql .= $suffix.'_total = '.round($total->total ?? 0, 2).' + '.$suffix.'_total ';
            $update_sql .= ' WHERE id='.$total->project_id;
            $tempdb->simple_query($update_sql);
        }
        unset($total_open_times);
        
        $total_open_extras = self::$CI->extras->get_calcexp_sum($deducted, Category_rates_model::T_EXTRAS, $request_vars, false, true);
        foreach ($total_open_extras as $total) {
            $update_sql = 'UPDATE '.$temptable.' SET ';
            $update_sql .= $suffix.'_extras = '.round($total->total ?? 0, 2).', ';
            $update_sql .= $suffix.'_total = '.round($total->total ?? 0, 2).' + '.$suffix.'_total ';
            $update_sql .= ' WHERE id='.$total->project_id;
            $tempdb->simple_query($update_sql);
        }
        unset($total_open_extras);
        
        $total_open_travels = self::$CI->travels->get_calcexp_sum($deducted, Category_rates_model::T_TRAVELS, $request_vars, false, true);
        foreach ($total_open_travels as $total) {
            $update_sql = 'UPDATE '.$temptable.' SET ';
            $update_sql .= $suffix.'_travels = '.round($total->total ?? 0, 2).', ';
            $update_sql .= $suffix.'_total = '.round($total->total ?? 0, 2).' + '.$suffix.'_total ';
            $update_sql .= ' WHERE id='.$total->project_id;
            $tempdb->simple_query($update_sql);
        }
        unset($total_open_travels);
        
        $total_open_externalexpenses = self::$CI->externalexpenses->get_calcexp_sum($deducted, Category_rates_model::T_EXTEXP, $request_vars, false, true);
        foreach ($total_open_externalexpenses as $total) {
            $update_sql = 'UPDATE '.$temptable.' SET ';
            $update_sql .= $suffix.'_externalexpenses = '.round($total->total ?? 0, 2).', ';
            $update_sql .= $suffix.'_total = '.round($total->total ?? 0, 2).' + '.$suffix.'_total ';
            $update_sql .= ' WHERE id='.$total->project_id;
            $tempdb->simple_query($update_sql);
        }
        unset($total_open_externalexpenses);
    }

    /**
     * Create the temp table
     * 
     * @param   object  $request_vars           Request vars
     * @return  void
     */
    public function init_temp_table($request_vars=null)
    {
        self::$CI->load->library('US_TempDB', '', 'tempdb');
        
        $temptable = us_uniqid('calcexp', true);
        self::$CI->tempdb->add_field('id', 'integer');
        self::$CI->tempdb->add_field('project', 'varchar(200)');
        self::$CI->tempdb->add_field('last_date_str', 'varchar(10)');
        self::$CI->tempdb->add_field('threshold', 'double default 0');
        
        self::$CI->tempdb->add_field('open_times_number', 'double default 0');
        self::$CI->tempdb->add_field('open_times', 'double default 0');
        self::$CI->tempdb->add_field('open_extras', 'double default 0');
        self::$CI->tempdb->add_field('open_travels', 'double default 0');
        self::$CI->tempdb->add_field('open_externalexpenses', 'double default 0');
        
        self::$CI->tempdb->add_field('deducted_times_number', 'double default 0');
        self::$CI->tempdb->add_field('deducted_times', 'double default 0');
        self::$CI->tempdb->add_field('deducted_extras', 'double default 0');
        self::$CI->tempdb->add_field('deducted_travels', 'double default 0');
        self::$CI->tempdb->add_field('deducted_externalexpenses', 'double default 0');
        
        self::$CI->tempdb->add_field('open_total', 'double default 0');
        self::$CI->tempdb->add_field('deducted_total', 'double default 0');
        
        $tempdb = self::$CI->tempdb->create_table($temptable, US_TempDB::ENGINE_MEMORY);
        
        self::$tempdb = $tempdb;
        self::$temptable = $temptable;
    }
    
} // END class Calcexp_analysis
