<?php
/**
 * CodeIgniter library
 *
 * @category CodeIgniter
 * @license /_licenses/untermStrich/agb_normal.pdf
 * @author Christian Wengg
 * @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 Detailed_evaluation extends Report_data
{   
    /**
     * Creates 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('Total_costs_model', 'total_costs');
        self::$CI->load->model('Fee_areas_model', 'fee_areas');
        self::$CI->load->model('Projects_model', 'projects');
        self::$CI->load->model('Work_packages_model', 'work_packages');
        self::$CI->load->model('Wp_total_costs_updater_model', 'wp_total_costs_updater');
        
        set_us_memory_limit();
        restart_timout_counter(true);  
        
        self::init_temp_table();
        
        $request_vars->has_null = false;
        $request_vars->ignore_gross_hourly_wage = true;
        $request_vars->view_is_private_project = true;

        $project_id = false;
        $times_list = self::$CI->total_costs->get_times_for_detailed_evaluation_report($request_vars, $fee_multi_mode_set);
        $insert_dummy = '';
        
        $projects = self::$CI->projects->get_simple_list(
            !$request_vars->only_active_projects,
            true, false, true, false,
            $request_vars
        );
        
        if (isset($times_list) && !empty($times_list)) {
            $i_count = count($times_list);
            for ($i = 0; $i < $i_count; $i++) {
                $times_item = $times_list[$i];
                $data = array(
                    'project_id'        =>  $times_item->project_id,
                    'staff_name'        =>  $times_item->staff_name,
                    'staff_costs'       =>  $times_item->Ausdr1,  
                    'staff_hours'       =>  $times_item->number,
                    'project_name'      =>  $times_item->project,
                    'staff_id'          =>  $times_item->staff_id,
                    'work_package_id'   =>  $times_item->work_package_id,
                    'wp_name'           =>  $times_item->name,
                    'wp_number'         =>  $times_item->wp_number
                );
                self::$CI->db->simple_query(self::$tempdb->insert_string(self::$temptable, $data));
            }
        }          

        $temptable = self::init_temp_table_copy();
        $i_count = count($projects);
        for ($i = 0; $i < $i_count; $i++) {
            $project = $projects[$i];
            if (!$project_id || intval($project_id) !== intval($project->id)) {
                $wp_list = self::$CI->work_packages->get_list($project->id);
                foreach ($wp_list AS $wp_item) {
                    $data = array(
                        'wp_number'         =>  $wp_item->wp_number,
                        'wp_name'           =>  $wp_item->name,
                        'work_package_left' =>  $wp_item->lft,
                        'work_package_id'   =>  $wp_item->id,
                        'project_name'      =>  $project->project,
                        'project_id'        =>  $wp_item->project_id
                    );
                    $project_id = $wp_item->project_id;
                    self::$tempdb->insert_or_update(self::$temptable, array('work_package_id' => $wp_item->id), $data, true, $temptable);
                }
            }
            if ($insert_dummy !== $project->project) {
                $data = array('id' => -9999999-$project->id, 'project_id'=>$project->id, 'project_name'=>$project->project, 'work_package_left'=>-999999);
                self::$CI->db->simple_query(self::$tempdb->insert_string(self::$temptable, $data));
                $insert_dummy = $project->project;
            }
        }
            
        if ((isset($request_vars->dates__from) && !empty($request_vars->dates__from)) && (isset($request_vars->dates__to) && !empty($request_vars->dates__to))) {
            $request_vars->date__from = $request_vars->dates__from; 
            $request_vars->date__to = $request_vars->dates__to;  
        } 

        $this->_create_data_sub($request_vars, $fee_multi_mode_set, '');
        
        if (isset($times_list) && !empty($times_list)) {
            return $times_list;
        } else {
            return array();
        }
    }
    
       /**
     * Create the data for date type
     * 
     * @param   object      $request_vars           Request vars
     * @param   integer     $fee_multi_mode_set     Fee multiplier set item_id
     * @param   string      $suffix                 Suffix
     * @return  void
     */
    private function _create_data_sub($request_vars, $fee_multi_mode_set, $suffix)
    {
        self::$CI->lang->load('work_packages', self::$CI->web_settings->getLanguage());   

        $filter_project = '';
        if(!empty($request_vars->projects)) {
            $project_id_list = '';
            foreach ($request_vars->projects AS $project_id) {
                $project_id_list.=$project_id.', ';
            }
            $request_vars->projects = substr($project_id_list, 0, -2);
            $filter_project = 'project_id IN ('.$request_vars->projects.') AND ';
        }

        self::$CI->wp_total_costs_updater->update_times_hours($request_vars, self::$temptable, 'work_package_id', 'total_times_hours'.$suffix, true);
        self::$CI->db->query('UPDATE '.self::$temptable.' SET total_times_hours'.$suffix.'=0 WHERE total_times_hours'.$suffix.' IS NULL');

        $this->_create_data_sub__total_costs($request_vars, $fee_multi_mode_set, $suffix, 'wp_total');        
    }
    
    /**
     * Create the data for date type - For time, travels, extras and externalexpenses costs
     * 
     * @param   object      $request_vars           Request vars
     * @param   integer     $fee_multi_mode_set     Fee multiplier set item_id
     * @param   string      $suffix                 Suffix
     * @param   string      $prefix_as              Prefix AS ('total' or 'external')
     * @return  void
     */
    private function _create_data_sub__total_costs($request_vars, $fee_multi_mode_set, $suffix, $prefix_as)
    {
        $request_vars_no_date = clone $request_vars;
        unset($request_vars_no_date->keydate);
        
        self::$CI->wp_total_costs_updater->update_times($request_vars, $fee_multi_mode_set, self::$temptable, 'work_package_id', $prefix_as.'_times_cost'.$suffix);
        self::$CI->db->query('UPDATE '.self::$temptable.' SET '.$prefix_as.'_times_cost'.$suffix.'=0 WHERE '.$prefix_as.'_times_cost'.$suffix.' IS NULL');
        
        self::$CI->wp_total_costs_updater->update_times_hours($request_vars, self::$temptable, 'work_package_id', $prefix_as.'_times_hours'.$suffix);
        self::$CI->db->query('UPDATE '.self::$temptable.' SET '.$prefix_as.'_times_hours'.$suffix.'=0 WHERE '.$prefix_as.'_times_hours'.$suffix.' IS NULL');
        
        self::$CI->wp_total_costs_updater->update_travels($request_vars, self::$temptable, 'work_package_id', $prefix_as.'_travels_cost'.$suffix);
        self::$CI->db->query('UPDATE '.self::$temptable.' SET '.$prefix_as.'_travels_cost'.$suffix.'=0 WHERE '.$prefix_as.'_travels_cost'.$suffix.' IS NULL');
        
        self::$CI->wp_total_costs_updater->update_extras($request_vars, self::$temptable, 'work_package_id', $prefix_as.'_extras_cost'.$suffix);
        self::$CI->db->query('UPDATE '.self::$temptable.' SET '.$prefix_as.'_extras_cost'.$suffix.'=0 WHERE '.$prefix_as.'_extras_cost'.$suffix.' IS NULL');
        
        self::$CI->wp_total_costs_updater->update_externalexpenses($request_vars, self::$temptable, false, false, 'work_package_id', $prefix_as.'_externalexpenses_cost'.$suffix);
        self::$CI->db->query('UPDATE '.self::$temptable.' SET '.$prefix_as.'_externalexpenses_cost'.$suffix.'=0 WHERE '.$prefix_as.'_externalexpenses_cost'.$suffix.' IS NULL');
        
        self::_get_staff_sum();
        self::_get_project_sum();
        self::_get_sum();
    }
             
    /**
     * Sum values from the time entries together for one staff in one project
     * 
     * @param   object  $request_vars       Request vars
     * @return  void
     */
    private function _get_staff_sum() {
        $temptable2 = self::init_temp_table_copy();
        
        self::_get_staff_sum_helper('staff_total_times_cost', 'staff_costs', $temptable2);
        self::_get_staff_sum_helper('staff_total_times_hours', 'staff_hours', $temptable2);
    }
    
    /**
     * Sum values from the time entries together for one staff in one project
     * 
     * @param   object  $request_vars       Request vars
     * @return  void
     */
    private function _get_staff_sum_helper($table_field, $table_field2, $temptable2) {        
        $sql = 'UPDATE '.self::$temptable.' as top
                    SET 
                '.$table_field.' = (SELECT 
                    SUM(chld.'.$table_field2.')
                FROM
                    (SELECT 
                        '.$table_field2.', project_id, staff_id
                    FROM
                         '.$temptable2.') AS chld
                    WHERE
                        top.project_id = chld.project_id AND top.staff_id = chld.staff_id);';
        
        self::$CI->db->query($sql);
    }
    
    /**
     * Sum values from the WP together for the top node
     * 
     * @param   object  $request_vars       Request vars
     * @return  void
     */
    private function _get_project_sum() {
        $temptable2 = self::init_temp_table_copy();
        
        self::_get_project_sum_helper('project_total_times_cost', 'wp_total_times_cost', $temptable2);
        
        self::_get_project_sum_helper('project_total_times_hours', 'wp_total_times_hours', $temptable2);
        
        self::_get_project_sum_helper('project_total_travels_cost', 'wp_total_travels_cost', $temptable2);
        
        self::_get_project_sum_helper('project_total_extras_cost', 'wp_total_extras_cost', $temptable2);
        
        self::_get_project_sum_helper('project_total_externalexpenses_cost', 'wp_total_externalexpenses_cost', $temptable2);
        
        self::_get_project_sum_helper('project_total_times_hours', 'wp_total_times_hours', $temptable2);
    }
    
    /**
     * Sum values from the projects together 
     * 
     * @param   object  $request_vars       Request vars
     * @return  void
     */
    private function _get_sum() {
        $temptable2 = self::init_temp_table_copy();
        
        self::_get_sum_helper('total_times_cost', 'project_total_times_cost', $temptable2);
        
        self::_get_sum_helper('total_times_hours', 'project_total_times_hours', $temptable2);
        
        self::_get_sum_helper('total_travels_cost', 'project_total_travels_cost', $temptable2);
        
        self::_get_sum_helper('total_extras_cost', 'project_total_extras_cost', $temptable2);
        
        self::_get_sum_helper('total_externalexpenses_cost', 'project_total_externalexpenses_cost', $temptable2);
    }
    
    /**
     * Sum values from the WP together for the top node
     * 
     * @param   String  $table_field       The field name in the table
     * @param   String  $table_field2      The field name in the table which should be summed up
     * @param   String  $temptable2        Copy of the original tmp table
     * @return  void
     */
    private function _get_project_sum_helper($table_field, $table_field2, $temptable2) {
        $sql = 'UPDATE '.self::$temptable.' AS top 
                    SET 
                '.$table_field.' = (SELECT 
                    SUM(chld.'.$table_field2.')
                FROM
                    (SELECT 
                        '.$table_field2.', project_id, work_package_id
                    FROM
                         '.$temptable2.' GROUP BY project_id, work_package_id) AS chld
                    WHERE
                        top.project_id = chld.project_id);';

        self::$CI->db->query($sql);
    }
        
    /**
     * Sum values from the WP together for the top node
     * 
     * @param   String  $table_field       The field name in the table
     * @param   String  $table_field2      The field name in the table which should be summed up
     * @param   String  $temptable2        Copy of the original tmp table
     * @return  void
     */
    private function _get_sum_helper($table_field, $table_field2, $temptable2) {
        $sql = 'UPDATE '.self::$temptable.' 
                    SET 
                '.$table_field.' = (SELECT 
                    SUM(tmp2.'.$table_field2.')
                FROM
                    (SELECT 
                        SUM(DISTINCT '.$table_field2.') as '.$table_field2.'
                    FROM
                        '.$temptable2.'
                    GROUP BY project_id) AS tmp2);';
        
        self::$CI->db->query($sql);
    }
     
    /**
     * Get values for the report
     * 
     * @param   object      $request_vars           Request vars
     * @param   integer     $project_id             Project ID
     * @param   boolean     $load_sub               Show only top row or whole tree
     * @return  void
     */
    public function get_values($request_vars, $project_id=false, $staff_id=false, $load_sub=false)
    {
        $tempdb = self::$tempdb;
        $temptable = self::$temptable;
                
        $tempdb->from($temptable);
        
        $tempdb->select('work_package_id');
        $tempdb->select('project_name');
        if ($project_id!==false) {
            $tempdb->select('IFNULL(wp_name, \''.self::$CI->lang->line('reporting_wrong_time_entry').'\') AS wp_name', false);
        } else {
            $tempdb->select('IFNULL(wp_name, \'-\') AS wp_name', false);
        }
        $tempdb->select('wp_number');
        $tempdb->select('staff_name');
        $tempdb->select('project_total_travels_cost');
        $tempdb->select('project_total_extras_cost');
        $tempdb->select('project_total_externalexpenses_cost');
        $tempdb->select('project_total_times_cost');
        $tempdb->select('project_total_times_hours');
        $tempdb->select('total_travels_cost');
        $tempdb->select('total_extras_cost');
        $tempdb->select('total_externalexpenses_cost');
        $tempdb->select('total_times_cost');
        $tempdb->select('total_times_hours');
        $tempdb->select('staff_total_times_cost AS costs');
        $tempdb->select('staff_total_times_hours as hours');
        $tempdb->select('staff_costs AS detail_costs');
        $tempdb->select('staff_hours as detail_hours');
        $tempdb->select('staff_id');
        $tempdb->select('project_id');
        $tempdb->select('id');
        
        if ($project_id) {
            $tempdb->where($temptable.'.project_id', $project_id);
            $tempdb->where($temptable.'.staff_id', $staff_id);
        }
        
        if (isset($request_vars->no_indirect_costs) && !empty($request_vars->no_indirect_costs)) {
            self::$CI->projects->filter_not_add_to_indirect_costs($tempdb, $temptable);   
        }
        
        $tempdb->order_by('project_name');
        $tempdb->order_by('staff_name');
        
        if (!$load_sub) {
            $tempdb->group_by('project_name');
        } else {
            $tempdb->order_by('work_package_left');
            $tempdb->group_by('work_package_id');
        }
        $tempdb->group_by('staff_id');
        
        $query = $tempdb->get();

        return $query->result_object();
    }
    
    /**
     * Get values for the report
     * 
     * @param   object      $request_vars           Request vars
     * @param   integer     $project_id             Project ID
     * @param   String      $type                   The type for detail evaluation (travels, extras, expenses)
     * @param   boolean     $load_sub               Show only top row or whole tree
     * @return  void
     */
    public function get_values_not_staff($request_vars, $project_id=false, $type='times')
    {
        $tempdb = self::$tempdb;
        $temptable = self::$temptable;
                
        $tempdb->from($temptable);
        
        $tempdb->select('work_package_id');
        $tempdb->select('project_name');
        $tempdb->select('wp_number');
        $tempdb->select('IFNULL(wp_name, \'-\') AS wp_name', false);
        $tempdb->select('staff_name');
        $tempdb->select('project_total_'.$type.'_cost AS costs');
        ($type === 'times') ? $tempdb->select('project_total_times_hours AS hours') : $tempdb->select('0 AS hours');
        $tempdb->select('wp_total_'.$type.'_cost AS detail_costs');
        $tempdb->select('wp_total_times_cost');
        $tempdb->select('wp_total_travels_cost');
        $tempdb->select('wp_total_extras_cost');
        $tempdb->select('wp_total_externalexpenses_cost');
        ($type === 'times') ? $tempdb->select('wp_total_times_hours as detail_hours') : $tempdb->select('0 as detail_hours');
        $tempdb->select('staff_id');
        $tempdb->select('project_id');
        $tempdb->select('id');
        
        $tempdb->where($temptable.'.project_id', $project_id);
        $tempdb->where($temptable.'.wp_total_'.$type.'_cost > 0');
        
        $tempdb->order_by('project_name');
        $tempdb->order_by('work_package_left');

        $tempdb->group_by('work_package_id');
        
        $query = $tempdb->get();

        return $query->result_object();
    }
         
    /**
     * Create the temp table (BUT IN MAIN DB)
     * 
     * @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('detailed_eval', true);
        self::$CI->tempdb->add_field('id', 'integer AUTO_INCREMENT PRIMARY KEY');
        
        $this->_init_temp_table_sub();

        $no_tmp = false;     
        $tempdb = self::$CI->tempdb->create_table($temptable, US_TempDB::ENGINE_MEMORY, $no_tmp, true);
                
        self::$tempdb = $tempdb;
        self::$temptable = $temptable;
    }
    
    /**
     * Init for date type
     * 
     * @param   string  $suffix     Suffix
     * @return  void
     */
    private function _init_temp_table_sub()
    {        
        self::$CI->tempdb->add_field('work_package_id', 'double');
        self::$CI->tempdb->add_field('project_id', 'double');
        self::$CI->tempdb->add_field('staff_id', 'double');
        self::$CI->tempdb->add_field('wp_number', 'varchar(20)');
        self::$CI->tempdb->add_field('wp_name', 'varchar(300)');
        self::$CI->tempdb->add_field('project_name', 'varchar(200)');
        self::$CI->tempdb->add_field('staff_name', 'varchar(200)');
        self::$CI->tempdb->add_field('staff_total_times_cost', 'double');
        self::$CI->tempdb->add_field('staff_total_times_hours', 'double');
        self::$CI->tempdb->add_field('wp_total_times_cost', 'double');
        self::$CI->tempdb->add_field('wp_total_times_hours', 'double');
        self::$CI->tempdb->add_field('wp_total_travels_cost', 'double');
        self::$CI->tempdb->add_field('wp_total_extras_cost', 'double'); 
        self::$CI->tempdb->add_field('wp_total_externalexpenses_cost', 'double');
        self::$CI->tempdb->add_field('staff_costs', 'double');
        self::$CI->tempdb->add_field('staff_hours', 'double');
        self::$CI->tempdb->add_field('work_package_left', 'integer');
        self::$CI->tempdb->add_field('project_total_times_cost', 'double');
        self::$CI->tempdb->add_field('project_total_times_hours', 'double');
        self::$CI->tempdb->add_field('project_total_travels_cost', 'double');
        self::$CI->tempdb->add_field('project_total_extras_cost', 'double'); 
        self::$CI->tempdb->add_field('project_total_externalexpenses_cost', 'double');
        self::$CI->tempdb->add_field('total_times_cost', 'double');
        self::$CI->tempdb->add_field('total_times_hours', 'double');
        self::$CI->tempdb->add_field('total_travels_cost', 'double');
        self::$CI->tempdb->add_field('total_extras_cost', 'double'); 
        self::$CI->tempdb->add_field('total_externalexpenses_cost', 'double');
    }

    /**
     * Create a copy of the current temptable
     * 
     * @return  string
     */
    protected function init_temp_table_copy()
    {
        $temptable2 = us_uniqid('detailed_eval', true);
        self::$tempdb->query('CREATE TEMPORARY TABLE '.$temptable2.' AS SELECT * FROM '.self::$temptable);
        return $temptable2;
    }
    
    public function assign() {
        
    }

} // END class Detailed_evaluation
