<?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 Abbreviated_analysis 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('Fees_model', 'fees');
        self::$CI->load->model('Projects_model', 'projects');
        self::$CI->load->model('Work_packages_model', 'work_packages');
        self::$CI->load->model('Cost_centres_model', 'cost_centres');
        self::$CI->load->model('Wp_total_costs_updater_model', 'wp_total_costs_updater');
        self::$CI->load->model('System_settings_model', 'system_settings', true);
        
        set_us_memory_limit();
        restart_timout_counter(true);  
        
        $cost_centres = self::$CI->cost_centres->get_list(false, true, false, true); //set last flag true when db flag is availeable
        $request_vars->projects = array();
        $request_vars->has_null = false;
        $request_vars->cost_centres = $cost_centres;
        $insert_dummy = '';

        self::init_temp_table($request_vars);

        if (isset($request_vars->project) && !empty($request_vars->project)) {
            array_push($request_vars->projects, $request_vars->project);
            
            $project = self::$CI->projects->get_item($request_vars->projects[0], false, true);
            if ($project===null) {
                return array();
            }

        } else {            
            $projects = self::$CI->projects->get_simple_list(
                !$request_vars->only_active_projects,
                true,
                false,
                true,
                false,
                $request_vars
            );
            foreach($projects AS $project) {
                array_push($request_vars->projects, $project->id);
            }
        }

        if (sizeof($request_vars->projects) > 0) {
            $wps_list = self::$CI->work_packages->get_list(false, Work_packages_model::WHICH_ALL, null, true, true, false, true, false, $request_vars->projects);
            if (!$request_vars->load_sub) {
                $wp_view = self::$CI->work_packages->get_list(false, Work_packages_model::WHICH_ALL, null, false, true, false, true, true, $request_vars->projects);
            } else {
                $wp_view = $wps_list;
            }
        }

        if (isset($request_vars->date__from) && isset($request_vars->date__to)) {
            $request_vars->fee_date__from = $request_vars->date__from;
            $request_vars->fee_date__to = $request_vars->date__to;
        }

        if (isset($wps_list) && !empty($wps_list)) {
            $i_count = count($wps_list);
            for ($i=0;$i<$i_count;$i++) {
                $wp_item = $wps_list[$i];
                if (intval($wp_item->lft) === 1) {
                    $project_name = $wp_item->project;
                }
                $request_vars->project = $wp_item->project_id;
                
                $in_fee_or_sum = ($wp_item->is_fee_area || intval($wp_item->lft) === 1) ? true : false;
                
                $settled_fees_val = null;
                if ($in_fee_or_sum) {
                    if (self::$CI->system_settings->getboolUse_work_package_table() == 1 && $wp_item->lft > 1) {
                        $feeadmin = self::$CI->fees->get_fee_wp($wp_item->id, TRUE, TRUE, $request_vars);
                        $settled_fees_val = $feeadmin->value;
                    } else {
                        $feeadmin = self::$CI->fees->get_sum_values_information($request_vars, array(), Fees_model::TYPE_INVOICED);
                        $settled_fees_val = $feeadmin->sum_value;
                    }
                }

                $data = array(
                    'id'                =>  $wp_item->id,
                    'work_package_id'   =>  $wp_item->id,
                    'project_id'    =>  $wp_item->project_id,
                    'project_name'  =>  $project_name,
                    'wp_name'       =>  $wp_item->full_name,
                    'cs_name'       =>  $wp_item->cost_centre_name,
                    'fee_costs'     =>  $wp_item->fee_costs,
                    'fee_hours'     =>  $wp_item->fee_hours,
                    'budget_costs'  =>  $wp_item->budget_planned_costs,
                    'budget_hours'  =>  $wp_item->budget_planned_hours,
                    'completion_level'  =>  $wp_item->completion_level,
                    'work_package_left' =>  $wp_item->lft,
                    'work_package_right' =>  $wp_item->rgt,
                    'settled_fees'      =>  $settled_fees_val,
                    'work_package_is_fee'            => $wp_item->is_fee_area,
                );
                self::$CI->db->query(self::$tempdb->insert_string(self::$temptable, $data));
                
                if ($wp_item->is_fee_area) {
                    self::$CI->db->query('UPDATE '.self::$temptable.' SET work_package_is_fee_or_sum=true WHERE project_id='.$wp_item->project_id.' AND work_package_left<='.$wp_item->lft.' AND work_package_right>='.$wp_item->rgt);
                }
                
                if ($insert_dummy !== $wp_item->project_name) {
                    $data = array('id' => -999999-$wp_item->project_id, 'project_id'=>$wp_item->project_id, 'project_name'=>$project_name, 'work_package_left'=>-999999, 'work_package_is_fee'=>true, 'work_package_is_fee_or_sum'=>true);
                    self::$CI->db->query(self::$tempdb->insert_string(self::$temptable, $data));
                    $insert_dummy = $wp_item->project_name;
                }
            }

            unset($request_vars->project);
            $this->_create_data_sub($request_vars, $fee_multi_mode_set, '');

            return $wp_view;
        } 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', true);
        self::$CI->db->query('UPDATE '.self::$temptable.' SET total_times_hours=0 WHERE total_times_hours IS NULL');

        $this->_create_data_sub__total_costs($request_vars, $fee_multi_mode_set, $suffix, 'total');
   
        self::$CI->wp_total_costs_updater->update_externalexpenses($request_vars, self::$temptable, true, false, 'work_package_id', 'hon_decr_costs', true);
        self::$CI->db->query('UPDATE '.self::$temptable.' SET hon_decr_costs=0 WHERE hon_decr_costs IS NULL');
                
        self::$CI->db->query('UPDATE '.self::$temptable.' SET fee_internal=IFNULL(fee_costs,0) - IFNULL(hon_decr_costs,0)');
        self::$CI->db->query('UPDATE '.self::$temptable.' SET fee_internal=0 WHERE fee_internal IS NULL');
                                                       
        self::$CI->db->query('UPDATE '.self::$temptable.' SET fee_internal_completed=completion_level*fee_internal');
        self::$CI->db->query('UPDATE '.self::$temptable.' SET fee_internal_completed=0 WHERE fee_internal_completed IS NULL');
        
        self::$CI->db->query('UPDATE '.self::$temptable.' SET fee_internal_completed_all=completion_level*fee_costs');
        self::$CI->db->query('UPDATE '.self::$temptable.' SET fee_internal_completed_all=0 WHERE fee_internal_completed_all IS NULL');
        
        self::$CI->db->query('UPDATE '.self::$temptable.' SET hours_completed=completion_level*fee_hours');
        self::$CI->db->query('UPDATE '.self::$temptable.' SET hours_completed=0 WHERE hours_completed IS NULL');
        
        self::$CI->db->query('UPDATE '.self::$temptable.' SET hours_completed_diff=hours_completed-total_times_hours');
        self::$CI->db->query('UPDATE '.self::$temptable.' SET hours_completed_diff=0 WHERE hours_completed_diff IS NULL');
        
        if (self::$CI->system_settings->getboolUse_work_package_table() == 1) {
            self::$CI->db->query('UPDATE '.self::$temptable.' SET settled_fees_diff=IFNULL(fee_internal_completed, 0)-IFNULL(settled_fees,0)');

            self::$CI->db->query('UPDATE '.self::$temptable.' SET settled_fees_diff_all=IFNULL(fee_internal_completed_all,0)-IFNULL(settled_fees,0)');
        }
        
        $sql = '
            UPDATE '.self::$temptable.' AS temptab SET
            hours_deducted = (
                SELECT COALESCE(SUM(ROUND( IF(tim.number_external IS NOT NULL, tim.number_external, tim.number) ,2)),0)
                FROM times AS tim WHERE tim.project_id=temptab.project_id AND tim.work_package_id=temptab.work_package_id 
                AND tim.calcexp_id IS NOT NULL
                AND tim.deducted_day IS NOT NULL AND tim.is_ignore = 0
            )';
        if (!self::$CI->db->query($sql)) {
            die('ERROR WHEN CREATING DATA hours_deducted');
        }
        
        $sql = '
            UPDATE '.self::$temptable.' AS temptab SET
            proj_hourcosts_deducted = (
                SELECT COALESCE(SUM(ROUND(tim.factor * tim.price_per_unit, 2) * IF(tim.number_external IS NOT NULL, tim.number_external, tim.number)),0)
                FROM times AS tim WHERE tim.project_id=temptab.project_id AND tim.work_package_id=temptab.work_package_id
                AND tim.calcexp_id IS NOT NULL
                AND tim.deducted_day IS NOT NULL AND tim.is_ignore = 0
            )
        ';
        if (!self::$CI->db->query($sql)) {
            die('ERROR WHEN CREATING DATA proj_hourcosts_deducted');
        }
        
        self::_count_child_values($request_vars);
        
        self::_get_project_sum($request_vars);
        
        self::_calc_rows($request_vars);
    }
    
    /**
     * 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);

        if (count($request_vars->cost_centres) > 0) {
            foreach($request_vars->cost_centres as $idx=>$cost_centre) {
                $suffix = '_'.$cost_centre->id;
                self::_calc_internal_costs($request_vars, $fee_multi_mode_set, $suffix, $prefix_as, true);
            }
        }
        $suffix = '';
        self::_calc_internal_costs($request_vars, $fee_multi_mode_set, '', $prefix_as);
                
        self::$CI->wp_total_costs_updater->update_externalexpenses($request_vars, self::$temptable, false, false, 'work_package_id', $prefix_as.'_externalexpenses_cost'.$suffix, false, false, true);
        self::$CI->db->query('UPDATE '.self::$temptable.' SET '.$prefix_as.'_externalexpenses_cost'.$suffix.'=0 WHERE '.$prefix_as.'_externalexpenses_cost'.$suffix.' IS NULL');
        
        self::$CI->wp_total_costs_updater->update_externalexpenses($request_vars_no_date, self::$temptable, true, false, 'work_package_id', $prefix_as.'_externalexpenses_planned_cost'.$suffix, false, false, true);
        self::$CI->db->query('UPDATE '.self::$temptable.' SET '.$prefix_as.'_externalexpenses_planned_cost'.$suffix.'=0 WHERE '.$prefix_as.'_externalexpenses_planned_cost'.$suffix.' IS NULL');
    }

    /**
     * Calculates times/travels/extras together
     * 
     * @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')
     * @param   boolean     $for_cost_centre        Optional: if it is for a cost centre
     * @return  void
     */
    private function _calc_internal_costs($request_vars, $fee_multi_mode_set, $suffix, $prefix_as, $for_cost_centre=false) {
            self::$CI->wp_total_costs_updater->update_times($request_vars, $fee_multi_mode_set, self::$temptable, 'work_package_id', $prefix_as.'_times_cost'.$suffix, $for_cost_centre);
            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_travels($request_vars, self::$temptable, 'work_package_id', $prefix_as.'_travels_cost'.$suffix, $for_cost_centre);
            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, $for_cost_centre);
            self::$CI->db->query('UPDATE '.self::$temptable.' SET '.$prefix_as.'_extras_cost'.$suffix.'=0 WHERE '.$prefix_as.'_extras_cost'.$suffix.' IS NULL');
    }
    
    /**
     * Calculates values for the report
     * 
     * @param   integer     $wp_id                  WP ID
     * @param   boolean     $load_all               Show only top row or whole tree
     * @return  void
     */
    private function _calc_rows($request_vars)
    {
        $tempdb = self::$tempdb;
        $temptable = self::$temptable;
        
        self::$CI->db->query('UPDATE '.self::$temptable.' SET fee_internal=IFNULL(fee_costs,0) - IFNULL(hon_decr_costs,0)');
        self::$CI->db->query('UPDATE '.self::$temptable.' SET fee_internal=0 WHERE fee_internal IS NULL');
                                                       
        self::$CI->db->query('UPDATE '.self::$temptable.' SET hours_completed=completion_level*fee_hours');
        self::$CI->db->query('UPDATE '.self::$temptable.' SET hours_completed=0 WHERE hours_completed IS NULL');
        
        self::$CI->db->query('UPDATE '.self::$temptable.' SET hours_completed_diff=hours_completed-total_times_hours');
        self::$CI->db->query('UPDATE '.self::$temptable.' SET hours_completed_diff=0 WHERE hours_completed_diff IS NULL');
        
        $sql_totalcost = 'UPDATE '.self::$temptable.' SET total_cost = total_times_cost + total_travels_cost + total_extras_cost + total_externalexpenses_cost';
        $tempdb->query($sql_totalcost);

        if (count($request_vars->cost_centres) > 0) {
            foreach($request_vars->cost_centres as $idx=>$cost_centre) {
                $suffix = '_'.$cost_centre->id;
                $sql_totalcost_cost_centre = 'UPDATE '.self::$temptable.' SET cost_centre'.$suffix.' = total_times_cost'.$suffix.' + total_travels_cost'.$suffix.' + total_extras_cost'.$suffix;
                $tempdb->query($sql_totalcost_cost_centre);
            }
        }
    
        $sql_cost_diff = 'UPDATE '.self::$temptable.' SET cost_diff = ROUND(fee_internal,2) - IFNULL(ROUND(total_cost,2),0) WHERE work_package_is_fee = 1  OR work_package_left = 1';
        $tempdb->query($sql_cost_diff);
        
        $sql_fee_internal_completed_diff = 'UPDATE '.self::$temptable.' SET fee_internal_completed_diff = ROUND(fee_internal_completed,2) - IFNULL(ROUND(total_cost,2),0) WHERE work_package_is_fee = 1  OR work_package_left = 1';
        $tempdb->query($sql_fee_internal_completed_diff);
        
        $sql_fee_internal_completed_all_diff = 'UPDATE '.self::$temptable.' SET fee_internal_completed_all_diff = ROUND(fee_internal_completed_all,2) - IFNULL(ROUND(total_cost,2),0) WHERE work_package_is_fee = 1 OR work_package_left = 1';
        $tempdb->query($sql_fee_internal_completed_all_diff);
        
        $sql_hours_diff = 'UPDATE '.self::$temptable.' SET hours_diff = ROUND(fee_hours,2) - IFNULL(ROUND(total_times_hours,2),0)';
        $tempdb->query($sql_hours_diff);
        
        $sql_fees_per_hour = 'UPDATE '.self::$temptable.' SET fees_per_hour = ROUND(fee_costs,2) / IFNULL(ROUND(total_times_hours,2),0) WHERE total_times_hours<>0';
        $tempdb->query($sql_fees_per_hour);
        
        $sql_fees_per_fee_hour = 'UPDATE '.self::$temptable.' SET fees_per_fee_hour = ROUND(fee_costs,2) / IFNULL(ROUND(fee_hours,2),0) WHERE fee_hours<>0';
        $tempdb->query($sql_fees_per_fee_hour);
        
        $temptable2 = self::init_temp_table_copy();
        
        self::_count_child_values_helper('cost_diff', $temptable2, true); 
        
        self::_count_child_values_helper('fee_internal_completed_diff', $temptable2, true); 
        
        self::_count_child_values_helper('fee_internal_completed_all_diff', $temptable2, true); 
        
    }
         
    /**
     * Sum values from the lowest WP for parents
     * 
     * @param   object  $request_vars       Request vars
     * @return  void
     */
    private function _count_child_values($request_vars) {
        $temptable2 = self::init_temp_table_copy();

        self::_count_child_values_helper('hon_decr_costs', $temptable2);
        
        self::_count_child_values_helper('fee_internal', $temptable2, true); 
        
        self::_count_child_values_helper('fee_internal_completed', $temptable2, true);
        
        self::_count_child_values_helper('fee_internal_completed_all', $temptable2, true); 
        
        if (self::$CI->system_settings->getboolUse_work_package_table() == 1) {
            self::_count_child_values_helper('settled_fees', $temptable2, true, true); 
            
            self::_count_child_values_helper('settled_fees_diff', $temptable2, true, true); 
            
            self::_count_child_values_helper('settled_fees_diff_all', $temptable2, true, true); 
        }

        if (count($request_vars->cost_centres) > 0) {
            foreach($request_vars->cost_centres as $idx=>$cost_centre) {
                self::_count_child_values_helper('total_times_cost_'.$cost_centre->id, $temptable2);
                
                self::_count_child_values_helper('total_travels_cost_'.$cost_centre->id, $temptable2);
                
                self::_count_child_values_helper('total_extras_cost_'.$cost_centre->id, $temptable2);
            }
        }

        self::_count_child_values_helper('hours_completed', $temptable2);
        
        self::_count_child_values_helper('total_times_cost', $temptable2);
        
        self::_count_child_values_helper('total_travels_cost', $temptable2);
        
        self::_count_child_values_helper('total_extras_cost', $temptable2);
        
        self::_count_child_values_helper('total_externalexpenses_cost', $temptable2);
        
        self::_count_child_values_helper('total_times_hours', $temptable2);
        
        self::_count_child_values_helper('hours_completed_diff', $temptable2);
        
        self::_count_child_values_helper('fees_per_hour', $temptable2, true);
        
        self::_count_child_values_helper('fees_per_fee_hour', $temptable2, true);

        self::_count_child_values_helper('hours_deducted', $temptable2, true);
        
        self::_count_child_values_helper('proj_hourcosts_deducted', $temptable2, true);

    }
    
    /*
     * Sum values from the WP together for the parent node
     * 
     *  
     * @param   String  $table_field       The field name in the table
     * @param   String  $temptable2        Copy of the original tmp table
     * @param   boolean $use_fee           Should only fee areas be calculated
     * @return  void
     */
    private function _count_child_values_helper($table_field, $temptable2, $use_fee=false, $ignore_top=false) {
        if($use_fee) {
            $my_where = ' chld.work_package_is_fee=1 ';
        } else {
            $my_where = ' chld.work_package_right - chld.work_package_left = 1 ';
        }
        $my_where2 = '';
        if($ignore_top) {
            $my_where2 = ' AND top.work_package_left > 1';
        }
        
        $sql = 'UPDATE '.self::$temptable.' AS top 
                    SET 
                '.$table_field.' = (SELECT 
                    IFNULL(SUM(chld.'.$table_field.'), top.'.$table_field.')
                FROM
                    (SELECT 
                        '.$table_field.', project_id, work_package_left, work_package_right, work_package_is_fee
                    FROM
                        '.$temptable2.' ) AS chld
                    WHERE
                        top.project_id = chld.project_id
                        AND (chld.work_package_left > top.work_package_left AND chld.work_package_right < top.work_package_right)
                        AND '.$my_where.')
                WHERE top.work_package_right - top.work_package_left > 1'.$my_where2;

        if (!self::$CI->db->query($sql)) {
            self::$CI->db->trans_rollback();
            return self::$CI->CI->lang->line('basic_db_w_fail'); //ERROR
        }
    }
    
    /**
     * Sum values from the WP together for the top node
     * 
     * @param   object  $request_vars       Request vars
     * @return  void
     */
    private function _get_project_sum($request_vars) {
        $temptable2 = self::init_temp_table_copy();

        self::_get_project_sum_helper('hon_decr_costs', $temptable2);
        
        self::_get_project_sum_helper('fee_internal', $temptable2); 
        
        self::_get_project_sum_helper('fee_internal_completed', $temptable2);
                        
        self::_get_project_sum_helper('fee_internal_completed_all', $temptable2); 
        
        self::_get_project_sum_helper('hours_completed', $temptable2);

        if (count($request_vars->cost_centres) > 0) {
            foreach($request_vars->cost_centres as $idx=>$cost_centre) {
                self::_get_project_sum_helper('total_times_cost_'.$cost_centre->id, $temptable2);
                
                self::_get_project_sum_helper('total_travels_cost_'.$cost_centre->id, $temptable2);
                
                self::_get_project_sum_helper('total_extras_cost_'.$cost_centre->id, $temptable2);
            }
        }
        
        self::_get_project_sum_helper('total_times_cost', $temptable2);
        
        self::_get_project_sum_helper('total_travels_cost', $temptable2);
        
        self::_get_project_sum_helper('total_extras_cost', $temptable2);
        
        self::_get_project_sum_helper('total_externalexpenses_cost', $temptable2);
        
        self::_get_project_sum_helper('total_times_hours', $temptable2);
        
        self::_get_project_sum_helper('hours_completed_diff', $temptable2);
        
        if (self::$CI->system_settings->getboolUse_work_package_table() == 0) {
            self::_settled_fees_diff('settled_fees_diff', $temptable2, 'IFNULL(fee_internal_completed, 0)-IFNULL(settled_fees,0)');
            
            self::_settled_fees_diff('settled_fees_diff_all', $temptable2, 'IFNULL(fee_internal_completed_all,0)-IFNULL(settled_fees,0)');
        } 
    }
    
    /**
     * Sum values from the WP together for the top node
     * 
     * @param   String  $table_field       The field name in the table
     * @param   String  $temptable2        Copy of the original tmp table
     * @return  void
     */
    private function _get_project_sum_helper($table_field, $temptable2) {
        $sql = 'UPDATE '.self::$temptable.' AS top 
                    SET 
                '.$table_field.' = (SELECT 
                    SUM(chld.'.$table_field.')
                FROM
                    (SELECT 
                        '.$table_field.', project_id, work_package_is_fee
                    FROM
                         '.$temptable2.' ) AS chld
                    WHERE
                        top.project_id = chld.project_id
                        AND chld.work_package_is_fee = 1)
                WHERE
                    top.work_package_left = 1;';

        if (!self::$CI->db->query($sql)) {
            self::$CI->db->trans_rollback();
            return self::$CI->CI->lang->line('basic_db_w_fail'); //ERROR
        }
    }
    
    /**
     * Calculates settled_fees_diff and settled_fees_diff_all
     * 
     * @param   String  $table_field       The field name in the table
     * @param   String  $temptable2        Copy of the original tmp table
     * @param   String  $calculation       The calculation string for the table field
     * @return  void
     */
    private function _settled_fees_diff($table_field, $temptable2, $calculation) {
        $sql = 'UPDATE '.self::$temptable.' AS top 
                    SET 
                '.$table_field.' = (SELECT 
                    ('.$calculation.')
                FROM
                    (SELECT 
                        '.$table_field.', project_id, work_package_left
                    FROM
                         '.$temptable2.' ) AS chld
                    WHERE
                        top.project_id = chld.project_id AND chld.work_package_left=1)
                WHERE
                    top.work_package_left = 1;';

        if (!self::$CI->db->query($sql)) {
            self::$CI->db->trans_rollback();
            return self::$CI->CI->lang->line('basic_db_w_fail'); //ERROR
        }
    }
    
    /**
     * 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
     * @param   int         $left                   The left value (for fee branch load)
     * @param   int         $right                  The right value (for fee branch load)
     * @return  void
     */
    public function get_values($request_vars, $project_id=false, $load_sub=false, $left=false, $right=false)
    {
        $tempdb = self::$tempdb;
        $temptable = self::$temptable;
        $temptable2 = self::init_temp_table_copy();
        $tempdb->from($temptable);
        
        $tempdb->select('id');
        $tempdb->select('project_id');
        $tempdb->select('project_name');
        $tempdb->select('settled_fees');
        $tempdb->select('settled_fees_diff');
        $tempdb->select('settled_fees_diff_all');
        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('IFNULL(cs_name, \'-\') AS cs_name', false);
        $tempdb->select('total_cost');
        $tempdb->select('total_times_hours');
        $tempdb->select('completion_level * 100 AS completion_level');
        $tempdb->select('fee_costs');
        $tempdb->select('hon_decr_costs');
        $tempdb->select('fee_internal');
        $tempdb->select('cost_diff');
        $tempdb->select('((total_cost+hon_decr_costs)/fee_costs)*100 AS cost_used');
        $tempdb->select('(total_cost/fee_internal)*100 AS cost_internal_used');
        $tempdb->select('fee_internal_completed');
        $tempdb->select('fee_internal_completed_diff');
        $tempdb->select('fee_internal_completed_all');
        $tempdb->select('fee_internal_completed_all_diff');
        $tempdb->select('fee_hours');
        $tempdb->select('hours_diff');
        $tempdb->select('hours_completed');
        $tempdb->select('hours_completed_diff');
        $tempdb->select('(total_times_hours/fee_hours)*100 AS hours_used');
        $tempdb->select('((total_cost+hon_decr_costs)/total_times_hours) AS costs_per_hour');
        $tempdb->select('budget_costs');
        $tempdb->select('budget_hours');
        $tempdb->select('work_package_left');
        $tempdb->select('work_package_right');
        $tempdb->select('fees_per_hour');
        $tempdb->select('fees_per_fee_hour');
        $tempdb->select('hours_deducted');
        $tempdb->select('proj_hourcosts_deducted');
        $tempdb->select('work_package_is_fee');
        $tempdb->select('work_package_is_fee_or_sum');
        $tempdb->select('total_externalexpenses_cost AS externalexpenses_cost');
        
        if (count($request_vars->cost_centres) > 0) {
            foreach($request_vars->cost_centres as $idx=>$cost_centre) {
                $tempdb->select('cost_centre_'.$cost_centre->id);
            }
        }
        
        if ($project_id) {
            $tempdb->where($temptable.'.project_id', $project_id);
        }
        if (!$load_sub) {
            $tempdb->where($temptable.'.work_package_left', 1); 
        }
        if (isset($request_vars->indirect_costs) && !empty($request_vars->indirect_costs)) {
            self::$CI->projects->filter_not_add_to_indirect_costs($tempdb, $temptable);   
        }
        if ($left && $right) {
            $tempdb->where($temptable.'.work_package_left >= '.$left); 
            $tempdb->where($temptable.'.work_package_right <= '.$right); 
        }
        if (isset($request_vars->widget_data['use_fee_view']) && strtolower($request_vars->widget_data['use_fee_view']) === 'true' && $load_sub) {
            $tempdb->where(self::_get_for_fee_view($temptable, $temptable2, $request_vars->projects)); 
        }

        $tempdb->order_by('project_name');
        $tempdb->order_by('work_package_left');

        $query = $tempdb->get();

        return $query->result_object();
    }

    /**
     * Filter to get only fee areas (incl. parents)
     *
     * @param   String      $temptable            
     * @param   String     $project_id             Project ID
     * 
     * @return  String
     */
    public function _get_for_fee_view($temptable, $temptable2, $project_id) {
        return $temptable.'.project_id IN ('.$project_id.') AND 
            ('.$temptable.'.work_package_is_fee = 1 OR 
            (SELECT count(*) FROM '.$temptable2.' tmp2 
            WHERE tmp2.project_id = '.$temptable.'.project_id AND tmp2.work_package_is_fee = 1 
            AND '.$temptable.'.work_package_left < tmp2.work_package_left AND '.$temptable.'.work_package_right > tmp2.work_package_right) > 0)';
    }
    
     /**
     * Assign values of the report row
     *
     * @param   object  $request_vars       Request vars
     * @return  void
     */
    public function get_sum_row($request_vars) {
        $tempdb = self::$tempdb;
        $temptable = self::$temptable;
                
        $tempdb->from($temptable);
        
        $tempdb->select_sum('settled_fees', 'sum_settled_fees');
        $tempdb->select_sum('settled_fees_diff', 'sum_settled_fees_diff');
        $tempdb->select_sum('settled_fees_diff_all', 'sum_settled_fees_diff_all');
        $tempdb->select_sum('total_cost', 'sum_total_cost');
        $tempdb->select_sum('total_times_hours', 'sum_total_times_hours');
        $tempdb->select_sum('completion_level * 100', 'sum_completion_level');
        $tempdb->select_sum('fee_costs', 'sum_fee_costs');
        $tempdb->select_sum('hon_decr_costs', 'sum_hon_decr_costs');
        $tempdb->select_sum('fee_internal', 'sum_fee_internal');
        $tempdb->select_sum('cost_diff', 'sum_cost_diff');
        $tempdb->select('(SUM(total_cost+hon_decr_costs)/SUM(fee_costs))*100 AS sum_cost_used');
        $tempdb->select('(SUM(total_cost)/SUM(fee_internal))*101 AS sum_cost_internal_used');
        $tempdb->select_sum('fee_internal_completed', 'sum_fee_internal_completed');
        $tempdb->select_sum('fee_internal_completed_diff', 'sum_fee_internal_completed_diff');
        $tempdb->select_sum('fee_internal_completed_all', 'sum_fee_internal_completed_all');
        $tempdb->select_sum('fee_internal_completed_all_diff', 'sum_fee_internal_completed_all_diff');
        $tempdb->select_sum('fee_hours', 'sum_fee_hours');
        $tempdb->select_sum('hours_diff', 'sum_hours_diff');
        $tempdb->select_sum('hours_completed', 'sum_hours_completed');
        $tempdb->select_sum('hours_completed_diff', 'sum_hours_completed_diff');
        $tempdb->select('(SUM(total_times_hours)/SUM(fee_hours))*100 AS sum_hours_used');;
        $tempdb->select('(SUM(total_cost+hon_decr_costs)/SUM(total_times_hours)) AS sum_costs_per_hour');;
        $tempdb->select_sum('budget_costs', 'sum_budget_costs');
        $tempdb->select_sum('budget_hours', 'sum_budget_hours');
        $tempdb->select_sum('total_externalexpenses_cost', 'sum_externalexpenses_cost');
        $tempdb->select_sum('hours_deducted', 'sum_hours_deducted');
        $tempdb->select_sum('proj_hourcosts_deducted', 'sum_proj_hourcosts_deducted');
        
        if (count($request_vars->cost_centres) > 0) {
            foreach($request_vars->cost_centres as $idx=>$cost_centre) {
                $tempdb->select_sum('cost_centre_'.$cost_centre->id, 'sum_cost_centre_'.$cost_centre->id);
            }
        }

        $tempdb->where($temptable.'.work_package_left', 1);
        
        if (isset($request_vars->project) && !empty($request_vars->project)) {
            $tempdb->where($temptable.'.project_id', $request_vars->project);
        }
        if (isset($request_vars->projects) && !empty($request_vars->projects)) {
            $projects = (is_array($request_vars->projects)) ? implode(',', $request_vars->projects) : $request_vars->projects;
            $tempdb->where($temptable.'.project_id IN ('.$projects.')');
        }
        if (isset($request_vars->indirect_costs) && !empty($request_vars->indirect_costs)) {
            self::$CI->projects->filter_not_add_to_indirect_costs($tempdb, $temptable);   
        }
        
        $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('abbreviated', true);
        self::$CI->tempdb->add_field('id', 'integer');
        
        $this->_init_temp_table_sub($request_vars);

        $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   object  $request_vars           Request vars
     * @return  void
     */
    private function _init_temp_table_sub($request_vars=null)
    {
        self::$CI->tempdb->add_field('is_missing', 'boolean DEFAULT 0');
        
        self::$CI->tempdb->add_field('work_package_id', 'double'); #WORK PACKAGE
        self::$CI->tempdb->add_field('wp_name', 'varchar(300)'); #WORK PACKAGE
        self::$CI->tempdb->add_field('cs_name', 'varchar(200)'); #WORK PACKAGE
        self::$CI->tempdb->add_field('total_times_cost', 'double'); #WORK PACKAGE
        self::$CI->tempdb->add_field('total_travels_cost', 'double'); #WORK PACKAGE
        self::$CI->tempdb->add_field('total_extras_cost', 'double'); #WORK PACKAGE
        self::$CI->tempdb->add_field('total_externalexpenses_cost', 'double'); #WORK PACKAGE
        self::$CI->tempdb->add_field('total_externalexpenses_planned_cost', 'double'); #WORK PACKAGE
        self::$CI->tempdb->add_field('settled_fees', 'double'); #WORK PACKAGE
        self::$CI->tempdb->add_field('settled_fees_diff', 'double'); #WORK PACKAGE
        self::$CI->tempdb->add_field('settled_fees_diff_all', 'double'); #WORK PACKAGE
        self::$CI->tempdb->add_field('hon_decr_costs', 'double'); #WORK PACKAGE
        
        self::$CI->tempdb->add_field('sum_total_hours_wp', 'double'); #WORK PACKAGE SUM UP
        self::$CI->tempdb->add_field('sum_total_wp', 'double'); #WORK PACKAGE SUM UP
        
        self::$CI->tempdb->add_field('project_id', 'integer');
        self::$CI->tempdb->add_field('project', 'varchar(200)');
        self::$CI->tempdb->add_field('project_name', 'varchar(200)');
        
        self::$CI->tempdb->add_field('work_package_left', 'integer');
        self::$CI->tempdb->add_field('work_package_right', 'integer');
        self::$CI->tempdb->add_field('work_package_is_fee', 'boolean');
        self::$CI->tempdb->add_field('work_package_is_fee_or_sum', 'boolean');
        
        self::$CI->tempdb->add_field('total_times_hours', 'double'); #PROJECT
        self::$CI->tempdb->add_field('total_cost', 'double'); #PROJECT
        self::$CI->tempdb->add_field('settled_fees_diff_all', 'double'); #PROJECT
        
        self::$CI->tempdb->add_field('fee_costs', 'double'); #FEE AREA
        self::$CI->tempdb->add_field('fee_hours', 'double'); #FEE AREA
        self::$CI->tempdb->add_field('total_is_hon_decrhon', 'double'); #FEE AREA
        self::$CI->tempdb->add_field('hours_diffhon', 'double'); #FEE AREA
        self::$CI->tempdb->add_field('cost_diff', 'double');
        
        self::$CI->tempdb->add_field('completion_level', 'double'); #WORK AREA
        self::$CI->tempdb->add_field('fee_internal', 'double');
        self::$CI->tempdb->add_field('fee_internal_completed', 'double');
        self::$CI->tempdb->add_field('fee_internal_completed_all', 'double');
        self::$CI->tempdb->add_field('fee_internal_completed_diff', 'double');
        self::$CI->tempdb->add_field('fee_internal_completed_all_diff', 'double');
        self::$CI->tempdb->add_field('hours_completed', 'double');
        self::$CI->tempdb->add_field('hours_completed_diff', 'double');
        self::$CI->tempdb->add_field('hours_diff', 'double');
        
        self::$CI->tempdb->add_field('date_filter', 'integer(1)');
        
        self::$CI->tempdb->add_field('budget_costs', 'double');#Budgetplan
        self::$CI->tempdb->add_field('budget_hours', 'double');#Budgetplan

        self::$CI->tempdb->add_field('fees_per_hour', 'double');
        self::$CI->tempdb->add_field('fees_per_fee_hour', 'double');
        
        self::$CI->tempdb->add_field('hours_deducted', 'double');
        self::$CI->tempdb->add_field('proj_hourcosts_deducted', 'double');

        if (count($request_vars->cost_centres) > 0) {
            foreach($request_vars->cost_centres as $idx=>$cost_centre) {
                self::$CI->tempdb->add_field('total_times_cost_'.$cost_centre->id, 'double'); #WORK PACKAGE
                self::$CI->tempdb->add_field('total_travels_cost_'.$cost_centre->id, 'double'); #WORK PACKAGE
                self::$CI->tempdb->add_field('total_extras_cost_'.$cost_centre->id, 'double'); #WORK PACKAGE
                self::$CI->tempdb->add_field('cost_centre_'.$cost_centre->id, 'double');
            }
        }
    }

    /**
     * Create a copf of the current temptable
     * 
     * @return  string
     */
    protected function init_temp_table_copy()
    {
        $temptable2 = us_uniqid('abbreviated', true);
        if (!self::$tempdb->query('CREATE TEMPORARY TABLE '.$temptable2.' AS SELECT * FROM '.self::$temptable)) {
            basic_error('Cannot create temp table. Error ' . self::$tempdb->_error_message(), null, __CLASS__ . '::' . __METHOD__);
        }
        
        return $temptable2;
    }
    
    public function assign() {
        
    }

} // END class Abbreviated_analysis
