<?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 Fee_comparison_analysis extends Report_data
{
    protected static $temptable_fc;
    
    /**
     * Create the data (RUNS IN MAIN DB)
     * 
     * @param   object      $request_vars           Request vars
     * @param   integer     $fee_multi_mode_set     Optional Fee multiplier set item_id
     * @return  array       Projects list
     */
    public function create_data($request_vars, $fee_multi_mode_set=null)
    {
        self::$CI->load->helper('us_date_helper');;
        self::$CI->load->model('Work_packages_tags_model', 'work_packages_tags', true);
        self::$CI->load->model('Work_packages_model', 'work_packages');
        self::$CI->load->model('Total_costs_updater_model', 'total_costs_updater');
        self::$CI->load->model('Projects_model', 'projects');
        
        self::init_temp_table();

        $request_vars->projects = array();
        $request_vars->has_null = false;
        $wp_tags = array();

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

        } else {
            if (isset($request_vars->project_group_group) && !empty($request_vars->project_group_group)) {
                $request_vars->project_group = $request_vars->project_group_group;
            }
            
            $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_id_list(false, Work_packages_model::WHICH_FEE_AREA, true, $request_vars->projects);
        }

        if (isset($wps_list) && !empty($wps_list)) {
            foreach($wps_list AS $wp_item) {
                if (isset($wp_item->tag_id) && !empty($wp_item->tag_id)) {
                   array_push($wp_tags, $wp_item->tag_id);
                } else {
                    $request_vars->has_null = true;  
                }
            }
            if (!empty($wp_tags)) {
                $tags_list = self::$CI->work_packages_tags->get_list($wp_tags); 
            } else {
                $tags_list = array();
            }

            if (!empty($tags_list)) {
                foreach ($tags_list as $tag_item) {
                    $data = array(
                        'id'            =>  $tag_item->id,
                        'tag_name'      =>  $tag_item->name,
                        'tag_id'        =>  $tag_item->id,
                    );
                    self::$CI->db->simple_query(self::$tempdb->insert_string(self::$temptable, $data));

                }
            }

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

            return $tags_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());   

        $date_now = new UsDate('now');         
        $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 ';
        }

        $sql_main = '
                FROM work_packages AS wptab 
                WHERE 
                    '.$filter_project.'
                    ((wptab.tag_id=temptab.tag_id)
                    OR 
                    (wptab.tag_id IS NULL AND temptab.tag_id IS NULL)
                    ) AND wptab.is_fee_area=1';
        
        $sql = '
            UPDATE '.self::$temptable.' AS temptab
            SET %s
            planned_fees'.$suffix.' = (
                SELECT sum(wptab.fee_costs) %s
            ),
            planned_fee_hours'.$suffix.' = (
                SELECT sum(wptab.fee_hours) %s
            )
        ';

        if (isset($request_vars->has_null) && !empty($request_vars->has_null)) {
            $insert_null_tag = '
                INSERT INTO '.self::$temptable.'(id, tag_name, tag_id)
                VALUES(-999999, "'.self::$CI->lang->line('work_packages_no_tag').'", (SELECT tag_id FROM work_packages WHERE tag_id IS NULL AND is_fee_area = 1 LIMIT 1))';

            if (!self::$CI->db->query($insert_null_tag)) {
                die('ERROR WHEN CREATING DATA 5.1');
            }
        }
        if (!self::$CI->db->query(sprintf($sql, 'date'.$suffix.' = \''.$date_now->mysqlday().'\', ', $sql_main, $sql_main, $sql_main, $sql_main))) {
            die('ERROR WHEN CREATING DATA 5.1');
        }
   
        self::$CI->db->query('UPDATE '.self::$temptable.' SET planned_fees'.$suffix.'=0 WHERE planned_fees'.$suffix.' IS NULL');
        self::$CI->db->query('UPDATE '.self::$temptable.' SET budget_planned_costs'.$suffix.'=0 WHERE budget_planned_costs'.$suffix.' IS NULL');
        self::$CI->db->query('UPDATE '.self::$temptable.' SET budget_planned_hours'.$suffix.'=0 WHERE budget_planned_hours'.$suffix.' IS NULL');
        self::$CI->db->query('UPDATE '.self::$temptable.' SET completion_level'.$suffix.'=0 WHERE completion_level'.$suffix.' IS NULL');

        self::$CI->total_costs_updater->update_times_hours($request_vars, self::$temptable, 'tag_id', 'total_times_hours'.$suffix);
        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, 'total');

        self::$CI->total_costs_updater->update_externalexpenses($request_vars, self::$temptable, false, true, 'tag_id', 'total_externalexpenses_charged_cost'.$suffix);
        self::$CI->db->query('UPDATE '.self::$temptable.' SET total_externalexpenses_charged_cost'.$suffix.'=0 WHERE total_externalexpenses_charged_cost'.$suffix.' IS NULL');
   
        self::$CI->total_costs_updater->update_externalexpenses($request_vars, self::$temptable, true, false, 'tag_id', 'hon_decr_costs'.$suffix, true);
        self::$CI->db->query('UPDATE '.self::$temptable.' SET hon_decr_costs'.$suffix.'=0 WHERE hon_decr_costs'.$suffix.' IS NULL');
                                               
        self::$CI->total_costs_updater->update_completion_level($request_vars, self::$temptable, 'tag_id', 'completion_level'.$suffix);
        self::$CI->db->query('UPDATE '.self::$temptable.' SET completion_level'.$suffix.'=0 WHERE completion_level'.$suffix.' IS NULL');

        self::$CI->total_costs_updater->update_weighted_completion_level($request_vars, self::$temptable, 'tag_id', 'weighted_completion_level'.$suffix);
        self::$CI->db->query('UPDATE '.self::$temptable.' SET weighted_completion_level'.$suffix.'=0 WHERE weighted_completion_level'.$suffix.' IS NULL');
        
        self::$CI->total_costs_updater->update_fee_internal_completed($request_vars, self::$temptable, 'tag_id', 'fee_internal_completed'.$suffix);
        self::$CI->db->query('UPDATE '.self::$temptable.' SET fee_internal_completed'.$suffix.'=0 WHERE fee_internal_completed'.$suffix.' IS NULL');
        
        self::$CI->total_costs_updater->update_fee($request_vars, self::$temptable);
        self::$CI->db->query('UPDATE '.self::$temptable.' SET fee_value'.$suffix.'=0 WHERE fee_value'.$suffix.' IS NULL');
        
        self::$CI->total_costs_updater->update_fee($request_vars, self::$temptable, 'tag_id', 'planned_fee_value'.$suffix, true);
        self::$CI->db->query('UPDATE '.self::$temptable.' SET planned_fee_value'.$suffix.'=0 WHERE planned_fee_value'.$suffix.' IS NULL');
        
        self::$CI->total_costs_updater->update_fee($request_vars, self::$temptable, 'tag_id', 'fee_value_remitted'.$suffix, false, true);
        self::$CI->db->query('UPDATE '.self::$temptable.' SET fee_value_remitted'.$suffix.'=0 WHERE fee_value_remitted'.$suffix.' IS NULL');
    }

    /**
     * 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->total_costs_updater->update_times($request_vars, $fee_multi_mode_set, self::$temptable, 'tag_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->total_costs_updater->update_travels($request_vars, self::$temptable, 'tag_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->total_costs_updater->update_extras($request_vars, self::$temptable, 'tag_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->total_costs_updater->update_externalexpenses($request_vars, self::$temptable, false, false, 'tag_id', $prefix_as.'_externalexpenses_cost'.$suffix, false);
        self::$CI->db->query('UPDATE '.self::$temptable.' SET '.$prefix_as.'_externalexpenses_cost'.$suffix.'=0 WHERE '.$prefix_as.'_externalexpenses_cost'.$suffix.' IS NULL');
        
        self::$CI->total_costs_updater->update_externalexpenses($request_vars_no_date, self::$temptable, true, false, 'tag_id', $prefix_as.'_externalexpenses_planned_cost'.$suffix, false);
        self::$CI->db->query('UPDATE '.self::$temptable.' SET '.$prefix_as.'_externalexpenses_planned_cost'.$suffix.'=0 WHERE '.$prefix_as.'_externalexpenses_planned_cost'.$suffix.' IS NULL');
    }

    /**
     * Assign values of the report to smarty
     * 
     * @return  void
     */
    public function assign()
    {
        die('NOT IMPLEMENTED. USE assign_row instead');
    }
    
    /**
     * Assign values of the report row to smarty
     * 
     * @param   integer     $tag_id                 Tag ID
     * @param   boolean     $for_chart              Optional: Flag if rows should not be assigend to smarty
     * @return  void
     */
    public function assign_row($tag_id, $for_chart=false)
    {
        $tempdb = self::$tempdb;
        $temptable = self::$temptable;
                
        $tempdb->from($temptable);
        
        $tempdb->select('*');
        
        $sql_totalcost = 'ROUND( total_times_cost, 2 ) + ROUND( total_travels_cost, 2 ) + ROUND( total_extras_cost, 2 ) + ROUND( total_externalexpenses_cost, 2 )';
        $tempdb->select($sql_totalcost.' AS total_cost', false);

        $sql_fee_internal = 'ROUND( planned_fees, 2 ) - IFNULL(ROUND( hon_decr_costs, 2 ), 0 )';
        $tempdb->select($sql_fee_internal.' AS fee_internal', false);
        
        $sql_cost_diff = 'ROUND( '.$sql_fee_internal.', 2 ) - IFNULL(ROUND( '.$sql_totalcost.', 2 ), 0 )';
        $tempdb->select($sql_cost_diff.' AS cost_diff', false);
        
        $sql_hours_diff = 'ROUND( planned_fee_hours, 2 ) - IFNULL(ROUND( total_times_hours, 2 ), 0 )';
        $tempdb->select($sql_hours_diff.' AS hours_diff', false);
        
        $sql_hours_used = 'ROUND( ROUND( total_times_hours, 2 ) / IFNULL(ROUND( planned_fee_hours, 2 ), 0 )*100, 2 )';
        $tempdb->select($sql_hours_used.' AS hours_used', false);
        
        $tempdb->select('ROUND( ( IFNULL('.$sql_totalcost.', 0 ) + IFNULL(hon_decr_costs, 0 ) ) / planned_fees, 2 )  AS cost_used', false);
        $tempdb->select('ROUND( IFNULL('.$sql_totalcost.', 0 ) / ( '.$sql_fee_internal.' ), 2 )  AS cost_internal_used', false);
        
        $tempdb->select('ROUND( SUM( IFNULL('.$sql_totalcost.', 0 ) + IFNULL(hon_decr_costs, 0 ) ) / SUM( IFNULL( total_times_hours , 0 ) ), 2 ) AS costs_per_hour', false);
        $tempdb->select('ROUND( SUM( planned_fees ) / SUM( IFNULL( total_times_hours, 0 ) ), 2 ) AS fees_per_hour', false);
        $tempdb->select('ROUND( SUM( planned_fees ) / SUM( IFNULL( planned_fee_hours, 0 ) ), 2 ) AS fees_per_fee_hour', false);
                
        $tempdb->where($temptable.'.id', $tag_id);
        
        $query = $tempdb->get();
        
        if (!$for_chart) {
            self::$CI->smarty->assign('data_item', $query->row());
        } else {
            return $query->row();
        }
    }
    
    /**
     * Assign values of the report row to smarty
     *
     * @param   integer     $request_vars    
     * @return  void
     */
    public function assign_sum_row($request_vars=null) {
        self::$CI->load->model('Fees_model', 'fees');
        if ($request_vars !== NULL) {
            if (isset($request_vars->project_group) && !empty($request_vars->project_group) && (!isset($request_vars->project_group_group) && empty($request_vars->project_group_group))) {
                $request_vars->project = $request_vars->project_group;
            } 
            if (isset($request_vars->project_group_group) && !empty($request_vars->project_group_group)) {
                $request_vars->project_group = $request_vars->project_group_group;
            } 

            $fees_calc = self::$CI->fees->get_sum_values_information($request_vars, null, Fees_model::TYPE_INVOICED);
            $fees_planned = self::$CI->fees->get_sum_values_information($request_vars, null, Fees_model::TYPE_NOT_INVOICED);
            self::$CI->smarty->assign('fees_calc', $fees_calc);
            self::$CI->smarty->assign('fees_planned', $fees_planned);
        }
        
        $tempdb = self::$tempdb;
        $temptable = self::$temptable;

        $sql_totalcost = 'ROUND( total_times_cost, 2 ) + ROUND( total_travels_cost, 2 ) + ROUND( total_extras_cost, 2 ) + ROUND( total_externalexpenses_cost, 2 )';
        $tempdb->select_sum('ROUND( '.$sql_totalcost.', 2 ) ', 'total_cost_sum');
        $tempdb->select_sum('ROUND( planned_fees, 2 ) ', 'planned_fees_sum');
        $tempdb->select_sum('ROUND( fee_internal_completed, 2 ) ', 'fee_internal_completed_sum');
        $tempdb->select_sum('ROUND( planned_fee_hours, 2 ) ', 'planned_fee_hours_sum');
        $sql_fee_internal = 'ROUND( planned_fees, 2 ) - IFNULL(ROUND( hon_decr_costs, 2 ), 0 )';
        $tempdb->select_sum('ROUND( '.$sql_fee_internal.', 2 ) ', 'fee_internal_sum');
        $sql_cost_diff = 'ROUND( '.$sql_fee_internal.', 2 ) - IFNULL(ROUND( '.$sql_totalcost.', 2 ), 0 )';
        $tempdb->select_sum('ROUND( '.$sql_cost_diff.', 2 ) ', 'cost_diff_sum');
        $sql_cost_used = 'ROUND( ( IFNULL('.$sql_totalcost.', 0 ) + IFNULL(hon_decr_costs, 0 ) ) / planned_fees, 2 )';
        $tempdb->select_sum('ROUND( '.$sql_cost_used.', 2 ) ', 'cost_used_sum');
        $sql_cost_internal_used = 'ROUND( IFNULL('.$sql_totalcost.', 0 ) / ( '.$sql_fee_internal.' ), 2 )';
        $tempdb->select_sum('ROUND( '.$sql_cost_internal_used.', 2 ) ', 'cost_internal_used_sum');
        $sql_hours_diff = 'ROUND( planned_fee_hours, 2 ) - IFNULL(ROUND( total_times_hours, 2 ), 0 )';
        $tempdb->select_sum('ROUND( '.$sql_hours_diff.', 2 ) ', 'hours_diff_sum');
        $sql_hours_used = 'ROUND( ROUND( total_times_hours, 2 ) / IFNULL(ROUND( planned_fee_hours, 2 ), 0 ), 2 )';
        $tempdb->select_sum('ROUND( '.$sql_hours_used.', 2 ) ', 'hours_used_sum');
        $tempdb->select_sum('ROUND( completion_level, 2 ) ', 'completion_level_sum');
        $tempdb->select_sum('ROUND( total_times_cost, 2 ) ', 'total_times_cost_sum');
        $tempdb->select_sum('ROUND( total_times_hours, 2 ) ', 'total_times_hours_sum');
        $tempdb->select_sum('ROUND( total_travels_cost, 2 ) ', 'total_travels_cost_sum');
        $tempdb->select_sum('ROUND( total_extras_cost, 2 ) ', 'total_extras_cost_sum');
        $tempdb->select_sum('ROUND( total_externalexpenses_cost, 2 ) ', 'total_externalexpenses_cost_sum');
        $tempdb->select_sum('ROUND( hon_decr_costs, 2 ) ', 'hon_decr_costs_sum');
        $tempdb->select_sum('ROUND( fee_value, 2 ) ', 'fee_value_sum');
        $tempdb->select_sum('ROUND( planned_fee_value, 2 ) ', 'planned_fee_value_sum');
        $tempdb->select_sum('ROUND( fee_value_remitted, 2 ) ', 'fee_value_remitted_sum');
        
        $sql_costs_per_hour = 'SUM( IFNULL('.$sql_totalcost.', 0 ) + IFNULL(hon_decr_costs, 0 ) ) / SUM( IFNULL(total_times_hours, 0 ) )';
        $tempdb->select('ROUND( '.$sql_costs_per_hour.', 2 ) AS costs_per_hour_sum', false);
        
        $sql_fees_per_hour = 'SUM(planned_fees) / SUM( IFNULL(total_times_hours, 0 ) )';
        $tempdb->select('ROUND( '.$sql_fees_per_hour.', 2 ) AS fees_per_hour_sum', false);
        
        $sql_fees_per_fee_hour_sum = 'SUM(planned_fees) / SUM( IFNULL(planned_fee_hours, 0 ) )';
        $tempdb->select('ROUND( '.$sql_fees_per_fee_hour_sum.', 2 ) AS fees_per_fee_hour_sum', false);
                
        $query = $tempdb->get($temptable);
        self::$CI->smarty->assign('data_item', $query->row());
    }

    /**
     * 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('fee_comp', true);
        self::$CI->tempdb->add_field('id', 'integer');
        self::$CI->tempdb->add_field('tag_name', 'varchar(200)');
        
        $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($suffix)
    {
        self::$CI->tempdb->add_field('date'.$suffix, 'datetime');
        self::$CI->tempdb->add_field('tag_id'.$suffix, 'integer');
        self::$CI->tempdb->add_field('total_cost'.$suffix, 'integer');
        self::$CI->tempdb->add_field('planned_fees'.$suffix, 'double default 0');
        self::$CI->tempdb->add_field('planned_fee_hours'.$suffix, 'double default 0');
        self::$CI->tempdb->add_field('budget_planned_costs'.$suffix, 'double default 0');
        self::$CI->tempdb->add_field('budget_planned_hours'.$suffix, 'double default 0');
        self::$CI->tempdb->add_field('fee_internal'.$suffix, 'double default 0');
        self::$CI->tempdb->add_field('cost_diff'.$suffix, 'double default 0');
        self::$CI->tempdb->add_field('cost_used'.$suffix, 'double default 0');
        self::$CI->tempdb->add_field('cost_internal_used'.$suffix, 'double default 0');
        self::$CI->tempdb->add_field('hours_diff'.$suffix, 'double default 0');
        self::$CI->tempdb->add_field('hours_used'.$suffix, 'double default 0');
        self::$CI->tempdb->add_field('completion_level'.$suffix, 'double default 0');
        self::$CI->tempdb->add_field('weighted_completion_level'.$suffix, 'double default 0');
        self::$CI->tempdb->add_field('fee_internal_completed'.$suffix, 'double default 0');
        self::$CI->tempdb->add_field('total_times_cost'.$suffix, 'double default 0');
        self::$CI->tempdb->add_field('total_times_hours'.$suffix, 'double default 0');
        self::$CI->tempdb->add_field('total_travels_cost'.$suffix, 'double default 0');
        self::$CI->tempdb->add_field('total_extras_cost'.$suffix, 'double default 0');
        self::$CI->tempdb->add_field('total_externalexpenses_cost'.$suffix, 'double default 0');
        self::$CI->tempdb->add_field('total_externalexpenses_charged_cost'.$suffix, 'double default 0');
        self::$CI->tempdb->add_field('total_externalexpenses_planned_cost'.$suffix, 'double default 0');
        self::$CI->tempdb->add_field('hon_decr_costs'.$suffix, 'double default 0');
        self::$CI->tempdb->add_field('costs_per_hour'.$suffix, 'double default 0');
        self::$CI->tempdb->add_field('fees_per_hour'.$suffix, 'double default 0');
        self::$CI->tempdb->add_field('fees_per_fee_hour'.$suffix, 'double default 0');
        self::$CI->tempdb->add_field('fee_value'.$suffix, 'double default 0');
        self::$CI->tempdb->add_field('planned_fee_value'.$suffix, 'double default 0');
        self::$CI->tempdb->add_field('fee_value_remitted'.$suffix, 'double default 0');
    }
    
} // END class Started_work_analysis
