<?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
 */
abstract class Started_work_analysis_create extends Report_data
{
    const MAX_EXTERNALLIST_LENGTH = 1000;
    protected $projects_list;
    protected $cost_centres_list;
    protected $fee_categories_list;
    
    /**
     * Create the data (RUNS IN MAIN DB)
     * 
     * @param   object      $request_vars           Request vars
     * @param   integer     $fee_multi_mode_set     Fee multiplier set item_id
     * @return  array       array(Projects list, Cost_centres list)
     */
    public function create_data($request_vars, $fee_multi_mode_set=null)
    {
        self::$CI->load->helper('us_date_helper');
        self::$CI->load->model('Fee_categories_model', 'fee_categories');
        self::$CI->load->model('Cost_centres_model', 'cost_centres');
        self::$CI->load->model('Work_packages_model', 'work_packages');
        self::$CI->load->model('Projects_model', 'projects', true);
        self::$CI->load->model('Total_costs_updater_model', 'total_costs_updater');
        
        $request_vars_prev_date = clone $request_vars;
        $request_vars_prev_date->keydate = $request_vars_prev_date->datespan;
        
        $old_and_future = true; $old_and_future_bool = true;
        if (isset($request_vars->only_active_projects) && $request_vars->only_active_projects) {
            $old_and_future = array(clone $request_vars_prev_date->keydate, clone $request_vars->keydate);
            $old_and_future_bool = false;
        }
        
        self::$CI->smarty->assign('date_prev', $request_vars_prev_date->keydate->us_date());
        self::$CI->smarty->assign('date_main', $request_vars->keydate->us_date());
        self::$CI->smarty->assign('old_and_future', $old_and_future_bool);
        
        $this->projects_list = self::$CI->projects->get_simple_list($old_and_future, true, false, true, false, $request_vars); //old_and_future is either true or date
        if (empty($this->projects_list)) {
            return array(array(), array(), array());
        }
        
        $projects_ids = array();
        foreach ($this->projects_list as $project_item) {
            array_push($projects_ids, $project_item->project_id);
        }
        
        $this->cost_centres_list = array();
        if ($request_vars->showcostcentres) {
            $cost_centres_results = self::$CI->work_packages->get_cost_centres_for_projects($projects_ids);
            
            $cost_centre_ids = array();
            foreach ($cost_centres_results AS $cost_centres_result) {
                array_push($cost_centre_ids, $cost_centres_result->cost_centre_id);
            }
            
            if (!empty($cost_centre_ids)) {
                $this->cost_centres_list = self::$CI->cost_centres->get_list(false, false, $cost_centre_ids);
            }
        }
        
        $this->fee_categories_list = array();
        if ($request_vars->showcategories) {
            $this->fee_categories_list = self::$CI->fee_categories->get_list();
        }
        
        $suffix = '_prev';
        self::init_temp_table($request_vars);
        
        foreach ($this->projects_list as $project_item) {
            $data = array(
                'id'            =>  $project_item->project_id,
                'project'       =>  $project_item->project,
                'is_calc_from_expenses'     =>  $project_item->is_calc_from_expenses,
            );
            self::$CI->db->simple_query(self::$tempdb->insert_string(self::$temptable, $data));
        }
        
        $this->_create_data_sub($request_vars, $fee_multi_mode_set, '');
        $this->_create_data_sub($request_vars_prev_date, $fee_multi_mode_set, $suffix);
        
        return array($this->projects_list, $this->cost_centres_list, $this->fee_categories_list);
    }
    
    /**
     * 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)
    {
        $date_now = new UsDate('now');
        
        $sql_main = '
                FROM work_packages AS wptab 
                WHERE 
                    wptab.project_id=temptab.id
                    AND
                    wptab.lft=1
                ORDER BY date DESC
                LIMIT 1';
        
        $sql_history = '
                FROM work_packages_history AS wptab 
                    INNER JOIN 
                    work_packages AS wp 
                    ON 
                    wptab.wp_id=wp.id
                WHERE 
                    wptab.project_id=temptab.id
                    AND
                    wp.lft=1
                    AND 
                    wptab.date <= \''.$request_vars->keydate->mysqlday().'\'
                ORDER BY date DESC
                LIMIT 1';
        
        if (self::$CI->system_settings->getboolProject_intern_extern()) {
            $sql_main_external = '
                    FROM work_packages AS wptab 
                    WHERE 
                        wptab.project_id=temptab.id
                        AND
                        wptab.is_fee_area=1
                        AND
                        wptab.is_extern=1';

            $sql_history_external = '
                    FROM work_packages_history AS wptab 
                        INNER JOIN 
                        work_packages AS wp 
                        ON 
                        wptab.wp_id=wp.id
                    WHERE 
                        wptab.project_id=temptab.id
                        AND
                        wp.is_fee_area=1
                        AND
                        wp.is_extern=1
                        AND 
                        wptab.date <= \''.$request_vars->keydate->mysqlday().'\'';   
        } else {
            $sql_main_external = '
                    FROM work_packages AS wptab 
                    WHERE 
                        0=1';

            $sql_history_external = '
                    FROM work_packages_history AS wptab 
                        INNER JOIN 
                        work_packages AS wp 
                        ON 
                        wptab.wp_id=wp.id
                    WHERE 
                        0=1';            
        }
        
        switch (self::$CI->web_settings->getNumberLocale()) {
            case 'en':
                $locale = 'en_GB';
                break;
            case 'hu':
                $locale = 'hu_HU';
                break;
            case 'ch':
                $locale = 'de_CH';
                break;
            case 'de':
            default:
                $locale = 'de_DE';
                break;
        }

        $sql = '
            UPDATE '.self::$temptable.' AS temptab
            SET %s
            planned_fees'.$suffix.' = (
                SELECT wptab.fee_costs %s
            ),
            planned_fees_external'.$suffix.' = (
                SELECT SUM(wptab.fee_costs) %s
            ),
            planned_fees_externallist'.$suffix.' = SUBSTRING((
                SELECT GROUP_CONCAT(CONCAT_WS(\': \', SUBSTRING(%s.name, 1, 20), FORMAT(wptab.fee_costs, 2, \''.$locale.'\')) SEPARATOR \' | \') %s
            ), 1, '.self::MAX_EXTERNALLIST_LENGTH.'),
            budget_plan_costs'.$suffix.' = (
		SELECT wptab.budget_planned_costs %s
            ),
            budget_plan_costs_external'.$suffix.' = (
		SELECT SUM(wptab.budget_planned_costs) %s
            ),
            fees_plan_hours'.$suffix.' = (
		SELECT wptab.fee_hours %s
            ),
            budget_plan_hours'.$suffix.' = (
		SELECT wptab.budget_planned_hours %s
            ),
            completion_level'.$suffix.' = (
		SELECT wptab.completion_level %s
            )
        ';
        $sql_date = '
            date'.$suffix.' = (
                SELECT wptab.date %s
            ),
        ';
        
        if (!self::$CI->db->query(sprintf($sql, 'date'.$suffix.' = \''.$date_now->mysqlday().'\', ', $sql_main, $sql_main_external, 'wptab', $sql_main_external, $sql_main, $sql_main_external, $sql_main, $sql_main, $sql_main))) {
            self::$CI->load->helper('markdown');
            self::$CI->lang->load('pages');
            echo markdown(self::$CI->lang->linef('pages_started_work_analysis_low_group_concat_max_len', '5.1'));
            die();
        }
        
        if (datetime_diff_days($request_vars->keydate, $date_now) >= 0) {
            if (!self::$CI->db->query(sprintf($sql, sprintf($sql_date, $sql_history), $sql_history, $sql_history_external, 'wp', $sql_history_external, $sql_history, $sql_history_external, $sql_history, $sql_history, $sql_history))) {
                self::$CI->load->helper('markdown');
                self::$CI->lang->load('pages');
                echo markdown(self::$CI->lang->linef('pages_started_work_analysis_low_group_concat_max_len', '5.2'));
                die();
            }
        }
        
        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 planned_fees_external'.$suffix.'=0 WHERE planned_fees_external'.$suffix.' IS NULL');
        self::$CI->db->query('UPDATE '.self::$temptable.' SET budget_plan_costs'.$suffix.'=0 WHERE budget_plan_costs'.$suffix.' IS NULL');
        self::$CI->db->query('UPDATE '.self::$temptable.' SET budget_plan_costs_external'.$suffix.'=0 WHERE budget_plan_costs_external'.$suffix.' IS NULL');
        self::$CI->db->query('UPDATE '.self::$temptable.' SET fees_plan_hours'.$suffix.'=0 WHERE fees_plan_hours'.$suffix.' IS NULL');
        self::$CI->db->query('UPDATE '.self::$temptable.' SET budget_plan_hours'.$suffix.'=0 WHERE budget_plan_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, '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, '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, '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');
        
        if ($request_vars->showoffers) {
            $sql = '
                UPDATE '.self::$temptable.' AS temptab SET
                offers_offered'.$suffix.' = (
                    SELECT SUM(lo.costs)
                    FROM lut_fee_areas_offer AS lo
                    INNER JOIN project_offers AS po
                        ON lo.offer_id = po.id
                    INNER JOIN work_packages AS wp
                        ON lo.work_package_id = wp.id
                    WHERE po.project_id=temptab.id 
                        AND po.status=10 
                        AND IFNULL(po.order_date, po.date) <= \''.$request_vars->keydate->mysqlday().'\'
                        AND wp.is_fee_area=1
                ),
                offers_ordered'.$suffix.' = (
                    SELECT SUM(lo.costs)
                    FROM lut_fee_areas_offer AS lo
                    INNER JOIN project_offers AS po
                        ON lo.offer_id = po.id
                    INNER JOIN work_packages AS wp
                        ON lo.work_package_id = wp.id
                    WHERE po.project_id=temptab.id 
                        AND po.status=20
                        AND IFNULL(po.order_date, po.date) <= \''.$request_vars->keydate->mysqlday().'\'
                        AND wp.is_fee_area=1
                )
            ';
            if (!self::$CI->db->query($sql)) {
                die('ERROR WHEN CREATING DATA 16.1.1');
            }
            
            self::$CI->db->query('UPDATE '.self::$temptable.' SET offers_offered'.$suffix.'=0 WHERE offers_offered'.$suffix.' IS NULL');
            self::$CI->db->query('UPDATE '.self::$temptable.' SET offers_ordered'.$suffix.'=0 WHERE offers_ordered'.$suffix.' IS NULL');
        }
        
        $this->_create_data_sub__fees($request_vars, $suffix);
        
        $this->_create_data_sub__nonlinear($request_vars, $suffix, $date_now);
        
        $this->_create_data_sub__calcexp($request_vars, $suffix);

        $this->_create_data_sub__cost_centres($request_vars, $fee_multi_mode_set, $suffix, $date_now);
        
        $this->_create_data_sub__dates($request_vars, $suffix);
    }
    
    
    
    /**
     * Create the data for actual fees
     * 
     * @param   object  $request_vars       Request vars
     * @param   string  $suffix             Suffix
     * @return  void
     */
    private function _create_data_sub__dates($request_vars, $suffix)
    {
        if (!$request_vars->showdates || $suffix!=='') {
            return;
        }
        
        $sql = '
            UPDATE '.self::$temptable.' AS temptab
            SET last_times = (
                SELECT 
                    COALESCE(MAX(date), \''.UsBasicDateTime::DATEISNULL.'\')
                FROM
                    times
                WHERE
                    times.project_id=temptab.id
            ),
            last_completion_level = (
                SELECT 
                    COALESCE(MAX(loc_estimation_date), \''.UsBasicDateTime::DATEISNULL.'\')
                FROM
                    work_packages
                WHERE
                    work_packages.project_id=temptab.id
            )
        ';
        if (!self::$CI->db->query($sql)) {
            die('ERROR WHEN CREATING DATA 17.1');
        }
    }
    
    /**
     * Create the data for actual fees
     * 
     * @param   object  $request_vars       Request vars
     * @param   string  $suffix             Suffix
     * @return  void
     */
    private function _create_data_sub__fees($request_vars, $suffix)
    {
        if (!$request_vars->showfees) {
            return;
        }
        
        $sql = '
            UPDATE '.self::$temptable.' AS temptab
            SET %s'.$suffix.' = (
                SELECT 
                    SUM(
                        ROUND(
                            IF(
                                ROUND(fee.deduction, 2) != 0,
                                fee.value,
                                CASE fee.remited_after_tax
                                    WHEN 0 THEN fee.value
                                    ELSE fee.remited_after_tax
                                END
                            ) + 0.0000001,
                            2
                        )
                    )
                FROM
                    %s AS fee
                WHERE
                    fee_date %s \''.$request_vars->keydate->mysqlday().'\' 
                    AND
                    fee.project_id=temptab.id
                    %s
            )
        ';
        if (!self::$CI->db->query(sprintf($sql, 'fees_invoiced', 'fees_calc', '<=', ''))) {
            die('ERROR WHEN CREATING DATA 9.1.1');
        }
        if (!self::$CI->db->query(sprintf($sql, 'fees_invoiced_after', 'fees_calc', '>=', ''))) {
            die('ERROR WHEN CREATING DATA 9.1.2');
        }
        #finally_invoiced
        if (!self::$CI->db->query(sprintf($sql, 'fees_invoiced_finally_invoiced', 'fees_calc', '<=', ' AND fee.finally_invoiced=1'))) {
            die('ERROR WHEN CREATING DATA 9.2.1');
        }
        if (!self::$CI->db->query(sprintf($sql, 'fees_invoiced_finally_invoiced_not', 'fees_calc', '<=', ' AND fee.finally_invoiced=0'))) {
            die('ERROR WHEN CREATING DATA 9.2.2');
        }
        if (!self::$CI->db->query(sprintf($sql, 'fees_planned', 'fees_planned_calc', '>=', ''))) {
            die('ERROR WHEN CREATING DATA 10.1');
        }
        if (!self::$CI->db->query(sprintf($sql, 'fees_planned_before', 'fees_planned_calc', '<=', ''))) {
            die('ERROR WHEN CREATING DATA 10.2');
        }
        self::$CI->db->query('UPDATE '.self::$temptable.' SET fees_invoiced'.$suffix.'=0 WHERE fees_invoiced'.$suffix.' IS NULL');
        self::$CI->db->query('UPDATE '.self::$temptable.' SET fees_invoiced_after'.$suffix.'=0 WHERE fees_invoiced_after'.$suffix.' IS NULL');
        self::$CI->db->query('UPDATE '.self::$temptable.' SET fees_invoiced_finally_invoiced'.$suffix.'=0 WHERE fees_invoiced_finally_invoiced'.$suffix.' IS NULL');
        self::$CI->db->query('UPDATE '.self::$temptable.' SET fees_invoiced_finally_invoiced_not'.$suffix.'=0 WHERE fees_invoiced_finally_invoiced_not'.$suffix.' IS NULL');
        self::$CI->db->query('UPDATE '.self::$temptable.' SET fees_planned'.$suffix.'=0 WHERE fees_planned'.$suffix.' IS NULL');
        self::$CI->db->query('UPDATE '.self::$temptable.' SET fees_planned_before'.$suffix.'=0 WHERE fees_planned_before'.$suffix.' IS NULL');

        if ($request_vars->showcategories) {
            foreach ($this->fee_categories_list as $fee_category) {
                if (!self::$CI->db->query(sprintf($sql, 'fees_invoiced_'.$fee_category->id, 'fees_calc', '<=', ' AND fee.fee_category_id='.$fee_category->id))) {
                    die('ERROR WHEN CREATING DATA 9.10.'.$fee_category->id);
                }
                self::$CI->db->query('UPDATE '.self::$temptable.' SET fees_invoiced_'.$fee_category->id.$suffix.'=0 WHERE fees_invoiced_'.$fee_category->id.$suffix.' IS NULL');
            }
        }

        $sql = '
            UPDATE '.self::$temptable.' AS temptab
            SET %s'.$suffix.' = (
                SELECT 
                    SUM(
                        ROUND(fee.remited_after_tax + 0.0000001, 2)
                    )
                FROM
                    %s AS fee
                WHERE
                    fee_date %s \''.$request_vars->keydate->mysqlday().'\' 
                    AND
                    fee.project_id=temptab.id
            )
        ';
        if (!self::$CI->db->query(sprintf($sql, 'fees_remited', 'fees_calc', '<='))) {
            die('ERROR WHEN CREATING DATA 11.1');
        }
        self::$CI->db->query('UPDATE '.self::$temptable.' SET fees_remited'.$suffix.'=0 WHERE fees_remited'.$suffix.' IS NULL');

        $sql = '
            UPDATE '.self::$temptable.' AS temptab
            SET %s'.$suffix.' = (
                SELECT 
                    SUM(
                        ROUND(fee.remited_after_tax + 0.0000001, 2)
                    )
                FROM
                    %s AS fee
                WHERE
                    fee_date %s \''.$request_vars->keydate->mysqlday().'\' 
                    AND
                    fee.project_id=temptab.id
            )
        ';
        if (!self::$CI->db->query(sprintf($sql, 'fees_remited_after', 'fees_calc', '>='))) {
            die('ERROR WHEN CREATING DATA 11.2');
        }
        self::$CI->db->query('UPDATE '.self::$temptable.' SET fees_remited_after'.$suffix.'=0 WHERE fees_remited_after'.$suffix.' IS NULL');
        
        $sql = '
            UPDATE '.self::$temptable.' AS temptab
            SET %s'.$suffix.' = (
                SELECT 
                    SUM(
                        ROUND(
                            IF(
                                ROUND(fee.deduction, 2)!= 0,
                                fee.value-fee.remited_after_tax,
                                0
                            ) + 0.0000001,
                            2
                        )
                    )
                FROM
                    %s AS fee
                WHERE
                    fee_date %s \''.$request_vars->keydate->mysqlday().'\' 
                    AND
                    fee.project_id=temptab.id
            )
        ';
        
        if (!self::$CI->db->query(sprintf($sql, 'fees_deduction', 'fees_calc', '<='))) {
            die('ERROR WHEN CREATING DATA 11.3');
        }
        self::$CI->db->query('UPDATE '.self::$temptable.' SET fees_deduction'.$suffix.'=0 WHERE fees_deduction'.$suffix.' IS NULL');
    }
    
    /**
     * Create the data for non linear delimitation
     * 
     * @param   object  $request_vars       Request vars
     * @param   string  $suffix             Suffix
     * @param   object  $date_now           Now date
     * @return  void
     */
    private function _create_data_sub__nonlinear($request_vars, $suffix, UsDate $date_now)
    {
        if ($request_vars->usetype!=='nonlinear_default') {
            return;
        }
        
        $sql_deduct_hon_decr_costs_main = '';
        $sql_deduct_hon_decr_costs_date = '';
        if ($request_vars->deduct_hon_decr_costs) {
            $sql_deduct_hon_decr_costs_temp = '
                - (
                    SELECT 
                        IFNULL(SUM(reptr.value), 0)
                    FROM
                        externalexpenses_planned AS reptr
                        INNER JOIN
                        work_packages AS wp3
                        ON 
                        reptr.work_package_id = wp3.id
                    WHERE
                        reptr.project_id = wp2.project_id
                        AND 
                        reptr.is_hon_decr = TRUE
                        AND
                        wp3.lft>=%s2.lft
                        AND
                        wp3.rgt<=%s2.rgt
                )
            ';
            $sql_deduct_hon_decr_costs_main = sprintf($sql_deduct_hon_decr_costs_temp, 'wp', 'wp');
            $sql_deduct_hon_decr_costs_date = sprintf($sql_deduct_hon_decr_costs_temp, 'wpm', 'wpm');
        }
        #$sql_deduct_hon_decr_costs = '';

        $sql_main = '
            UPDATE '.self::$temptable.' AS temptab SET
            completed'.$suffix.' = (
                SELECT
                    SUM(
                        wp.completion_level * (SELECT 
                                wp2.fee_costs
                                '.$sql_deduct_hon_decr_costs_main.'
                            FROM
                                work_packages AS wp2
                            WHERE
                                wp2.project_id = wp.project_id
                                AND
                                wp2.is_fee_area = 1
                                AND
                                wp2.lft<=wp.lft
                                AND
                                wp2.rgt>=wp.rgt
                        ) * wp.budget_proportion / (SELECT 
                            wp2.budget_proportion
                        FROM
                            work_packages AS wp2
                        WHERE
                            wp2.project_id = wp.project_id
                            AND
                            wp2.is_fee_area = 1
                            AND
                            wp2.lft<=wp.lft
                            AND
                            wp2.rgt>=wp.rgt
                        )
                    )
                FROM
                    work_packages AS wp 
                WHERE 
                    wp.project_id=temptab.id
                    AND 
                    wp.lft!=1
                    AND 
                    wp.rgt-wp.lft=1
            )
        ';
        if (!self::$CI->db->query($sql_main)) {
            die('ERROR WHEN CREATING DATA 12.1. (Hint: Validate that the is_fee_area is correctly spread across the tree)');
        }

        if (datetime_diff_days($request_vars->keydate, $date_now) >= 0) {
            $sql_date = '
                UPDATE '.self::$temptable.' AS temptab SET
                completed'.$suffix.' = (
                    SELECT
                        SUM(
                            wp.completion_level * (SELECT 
                                    wp2.fee_costs
                                    '.$sql_deduct_hon_decr_costs_date.'
                                FROM
                                    work_packages_history AS wp2
                                    INNER JOIN 
                                    work_packages AS wpm2
                                    ON 
                                    wp2.wp_id=wpm2.id
                                WHERE
                                    wp2.date = wp.date
                                    AND 
                                    wp2.project_id = wp.project_id
                                    AND
                                    wpm2.is_fee_area = 1
                                    AND
                                    wpm2.lft<=wpm.lft
                                    AND
                                    wpm2.rgt>=wpm.rgt
                                    LIMIT 1
                            ) * wp.budget_proportion / (SELECT 
                                wp2.budget_proportion
                            FROM
                                work_packages_history AS wp2
                                INNER JOIN 
                                work_packages AS wpm2
                                ON 
                                wp2.wp_id=wpm2.id
                            WHERE
                                wp2.date = wp.date
                                AND 
                                wp2.project_id = wp.project_id
                                AND
                                wpm2.is_fee_area = 1
                                AND
                                wpm2.lft<=wpm.lft
                                AND
                                wpm2.rgt>=wpm.rgt
                                LIMIT 1
                            )
                        )
                    FROM
                        work_packages_history AS wp 
                        INNER JOIN 
                        work_packages AS wpm
                        ON 
                        wp.wp_id=wpm.id
                    WHERE 
                        wp.project_id=temptab.id
                        AND 
                        wp.date=temptab.date'.$suffix.'
                        AND
                        wpm.lft!=1
                        AND 
                        wpm.rgt-wpm.lft=1
                )
            ';
            if (!self::$CI->db->query($sql_date)) {
                die('ERROR WHEN CREATING DATA 12.2');
            }
        }


        $sql = '
            UPDATE '.self::$temptable.' AS temptab SET
            completed_budget'.$suffix.' = (
                SELECT
                    SUM(
                        wp.budget_planned_costs*wp.completion_level
                    )
                FROM
                    work_packages AS wp
                WHERE 
                    wp.project_id=temptab.id
                    AND
                    wp.lft!=1
                    AND 
                    wp.rgt-wp.lft=1
            )
        ';
        if (!self::$CI->db->query($sql)) {
            die('ERROR WHEN CREATING DATA 13.1');
        }


        if (datetime_diff_days($request_vars->keydate, $date_now) >= 0) {
            $sql = '
                UPDATE '.self::$temptable.' AS temptab SET
                completed_budget'.$suffix.' = (
                    SELECT
                        SUM(
                            wp.budget_planned_costs*wp.completion_level
                        )
                    FROM
                        work_packages_history AS wp 
                        INNER JOIN 
                        work_packages AS wpm
                        ON 
                        wp.wp_id=wpm.id
                    WHERE 
                        wp.project_id=temptab.id
                        AND 
                        wp.date=temptab.date'.$suffix.'
                        AND
                        wpm.lft!=1
                        AND 
                        wpm.rgt-wpm.lft=1
                )
            ';
            if (!self::$CI->db->query($sql)) {
                die('ERROR WHEN CREATING DATA 13.2');
            }
        }
    }
    
    
    /**
     * Create the data for calcexp billing
     * 
     * @param   object  $request_vars       Request vars
     * @param   string  $suffix             Suffix
     * @return  void
     */
    private function _create_data_sub__calcexp($request_vars, $suffix)
    {
        if (!$request_vars->showcalcexp) {
            return;
        }
        
        $sql = '
            UPDATE '.self::$temptable.' AS temptab SET
            calcexp_lastdate'.$suffix.' = (
                SELECT GREATEST(
                    (SELECT 
                        COALESCE(MAX(ex1.deducted_day), \''.UsBasicDateTime::DATEISNULL.'\') 
                        FROM externalexpenses AS ex1 
                        WHERE ex1.project_id=temptab.id AND ex1.deducted_day <= \''.$request_vars->keydate->mysqlday().'\'
                        ),
                    (SELECT 
                        COALESCE(MAX(ex2.deducted_day), \''.UsBasicDateTime::DATEISNULL.'\') 
                        FROM extras AS ex2 
                        WHERE ex2.project_id=temptab.id AND ex2.deducted_day <= \''.$request_vars->keydate->mysqlday().'\'
                        ),
                    (SELECT 
                        COALESCE(MAX(ti1.deducted_day), \''.UsBasicDateTime::DATEISNULL.'\') 
                        FROM times AS ti1 
                        WHERE ti1.project_id=temptab.id AND ti1.deducted_day <= \''.$request_vars->keydate->mysqlday().'\'
                        ),
                    (SELECT 
                        COALESCE(MAX(ti2.deducted_day_woa), \''.UsBasicDateTime::DATEISNULL.'\') 
                        FROM times AS ti2 
                        WHERE ti2.project_id=temptab.id AND ti2.deducted_day_woa <= \''.$request_vars->keydate->mysqlday().'\'
                        ),
                    (SELECT 
                        COALESCE(MAX(tr.deducted_day), \''.UsBasicDateTime::DATEISNULL.'\') 
                        FROM travels AS tr 
                        WHERE tr.project_id=temptab.id AND tr.deducted_day <= \''.$request_vars->keydate->mysqlday().'\'
                        )
                )
            )
        ';
        if (!self::$CI->db->query($sql)) {
            die('ERROR WHEN CREATING DATA 14.1.1');
        }

        $sql = '
            UPDATE '.self::$temptable.' AS temptab SET
            calcexp_loc_lastdate'.$suffix.' = (
                SELECT COALESCE(MAX(fee_date), \''.UsBasicDateTime::DATEISNULL.'\')
                FROM payments AS p
                INNER JOIN project_offers AS po
                    ON p.offer_id = po.id
                WHERE po.project_id=temptab.id AND p.status = 20 AND p.fee_date <= \''.$request_vars->keydate->mysqlday().'\'
            )
        ';
        if (!self::$CI->db->query($sql)) {
            die('ERROR WHEN CREATING DATA 14.1.2');
        }

        $sql = '
            UPDATE '.self::$temptable.' AS temptab SET
            project_no_estim_offers'.$suffix.' = (
                SELECT SUM(costs) 
                FROM lut_fee_areas_offer AS lo
                INNER JOIN project_offers AS po
                    ON lo.offer_id = po.id
                INNER JOIN work_packages AS wp
                    ON lo.work_package_id = wp.id
                WHERE po.project_id=temptab.id 
                    AND po.status=20 
                    AND po.is_estimation<>1 
                    AND po.date <= \''.$request_vars->keydate->mysqlday().'\'
                    AND wp.is_fee_area=1
            )
        ';
        if (!self::$CI->db->query($sql)) {
            die('ERROR WHEN CREATING DATA 14.1.3');
        }

        $sql = '
            UPDATE '.self::$temptable.' AS temptab SET
            fees_invoiced_lastdate'.$suffix.' = (
                SELECT COALESCE(MAX(fee_date), \''.UsBasicDateTime::DATEISNULL.'\')
                FROM fees
                WHERE project_id=temptab.id AND fee_date <= \''.$request_vars->keydate->mysqlday().'\'
            )
        ';
        if (!self::$CI->db->query($sql)) {
            die('ERROR WHEN CREATING DATA 14.1.3');
        }

        
        $sql = '
            UPDATE '.self::$temptable.' AS temptab SET
            %s = (
                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.id AND tim.calcexp_id IS NOT NULL
                AND tim.date <= \''.$request_vars->keydate->mysqlday().'\' AND tim.deducted_day IS NOT NULL AND tim.is_ignore = 0
            )
            WHERE temptab.is_calc_from_expenses = 1';
        if (!self::$CI->db->query(sprintf($sql, 'calcexp_times_hours'.$suffix))) {
            die('ERROR WHEN CREATING DATA 14.2.00');
        }
        
        $sql = '
            UPDATE '.self::$temptable.' AS temptab SET
            %s = %s (
                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.id AND tim.calcexp_id %s %s
            )
            WHERE temptab.is_calc_from_expenses = 1
        ';
        $sql1 = 'AND tim.deducted_day IS NOT NULL AND tim.is_ignore = 0 AND tim.deducted_day <= \''.$request_vars->keydate->mysqlday().'\'';
        $sql2 = 'AND tim.date <= \''.$request_vars->keydate->mysqlday().'\'';
        $sql3 = 'AND tim.date <= \''.$request_vars->keydate->mysqlday().'\' AND tim.deducted_day IS NOT NULL AND tim.is_ignore = 0 AND tim.deducted_day > \''.$request_vars->keydate->mysqlday().'\'';
        if ($suffix==='' && !self::$CI->db->query(sprintf($sql, 'calcexp_open_proj_curr', '', 'IS NULL', ''))) {
            die('ERROR WHEN CREATING DATA 14.2.01');
        }
        if (!self::$CI->db->query(sprintf($sql, 'calcexp_open_proj'.$suffix, '', 'IS NULL', $sql2))) {
            die('ERROR WHEN CREATING DATA 14.2.02');
        }
        if (!self::$CI->db->query(sprintf($sql, 'calcexp_open_proj'.$suffix, 'calcexp_open_proj'.$suffix.' + ', 'IS NOT NULL', $sql3))) {
            die('ERROR WHEN CREATING DATA 14.2.03');
        }
        if (!self::$CI->db->query(sprintf($sql, 'calcexp_done_proj'.$suffix, '', 'IS NOT NULL', $sql1))) {
            die('ERROR WHEN CREATING DATA 14.2.04');
        }

        $sql = '
            UPDATE '.self::$temptable.' AS temptab SET
            %s = %s (
                SELECT COALESCE(SUM(ROUND(tim.factor * tim.price_area, 2) * IF(tim.number_external IS NOT NULL, tim.number_external, tim.number)),0)
                FROM times AS tim WHERE tim.project_id=temptab.id AND tim.calcexp_id_woa %s %s
            )
            WHERE temptab.is_calc_from_expenses = 1
        ';
        $sql1 = 'AND tim.deducted_day_woa IS NOT NULL AND tim.is_ignore_woa = 0 AND tim.deducted_day_woa <= \''.$request_vars->keydate->mysqlday().'\'';
        $sql2 = 'AND tim.date <= \''.$request_vars->keydate->mysqlday().'\'';
        $sql3 = 'AND tim.date <= \''.$request_vars->keydate->mysqlday().'\' AND tim.deducted_day_woa IS NOT NULL AND tim.is_ignore_woa = 0 AND tim.deducted_day_woa > \''.$request_vars->keydate->mysqlday().'\'';
        if ($suffix==='' && !self::$CI->db->query(sprintf($sql, 'calcexp_open_woa_curr', '', 'IS NULL', ''))) {
            die('ERROR WHEN CREATING DATA 14.3.01');
        }
        if (!self::$CI->db->query(sprintf($sql, 'calcexp_open_woa'.$suffix, '', 'IS NULL', $sql2))) {
            die('ERROR WHEN CREATING DATA 14.3.02');
        }
        if (!self::$CI->db->query(sprintf($sql, 'calcexp_open_woa'.$suffix, 'calcexp_open_woa'.$suffix.' + ', 'IS NOT NULL', $sql3))) {
            die('ERROR WHEN CREATING DATA 14.3.03');
        }
        if (!self::$CI->db->query(sprintf($sql, 'calcexp_done_woa'.$suffix, '', 'IS NOT NULL', $sql1))) {
            die('ERROR WHEN CREATING DATA 14.3.04');
        }

        $sql = '
            UPDATE '.self::$temptable.' AS temptab SET
            %s = %s + (
                SELECT COALESCE(SUM(ROUND(COALESCE(extexp.charged, extexp.value), 2)),0)
                FROM externalexpenses AS extexp WHERE extexp.project_id=temptab.id AND extexp.is_calc_from_expenses = 1 AND extexp.calcexp_id %s %s
            )
            WHERE temptab.is_calc_from_expenses = 1
        ';
        $sql1 = 'AND extexp.deducted_day IS NOT NULL AND extexp.is_ignore = 0 AND extexp.deducted_day <= \''.$request_vars->keydate->mysqlday().'\'';
        $sql2 = 'AND extexp.date <= \''.$request_vars->keydate->mysqlday().'\'';
        $sql3 = 'AND extexp.date <= \''.$request_vars->keydate->mysqlday().'\' AND extexp.deducted_day IS NOT NULL AND extexp.is_ignore = 0 AND extexp.deducted_day > \''.$request_vars->keydate->mysqlday().'\'';
        if ($suffix==='' && !self::$CI->db->query(sprintf($sql, 'calcexp_open_proj_curr', 'calcexp_open_proj_curr', 'IS NULL', ''))) {
            die('ERROR WHEN CREATING DATA 14.5.01');
        }
        if (!self::$CI->db->query(sprintf($sql, 'calcexp_open_proj'.$suffix, 'calcexp_open_proj'.$suffix, 'IS NULL', $sql2))) {
            die('ERROR WHEN CREATING DATA 14.5.02');
        }
        if (!self::$CI->db->query(sprintf($sql, 'calcexp_open_proj'.$suffix, 'calcexp_open_proj'.$suffix, 'IS NOT NULL', $sql3))) {
            die('ERROR WHEN CREATING DATA 14.5.03');
        }
        if (!self::$CI->db->query(sprintf($sql, 'calcexp_done_proj'.$suffix, 'calcexp_done_proj'.$suffix, 'IS NOT NULL', $sql1))) {
            die('ERROR WHEN CREATING DATA 14.5.04');
        }
        if ($suffix==='' && !self::$CI->db->query(sprintf($sql, 'calcexp_open_woa_curr', 'calcexp_open_woa_curr', 'IS NULL', ''))) {
            die('ERROR WHEN CREATING DATA 14.5.11');
        }
        if (!self::$CI->db->query(sprintf($sql, 'calcexp_open_woa'.$suffix, 'calcexp_open_woa'.$suffix, 'IS NULL', $sql2))) {
            die('ERROR WHEN CREATING DATA 14.5.12');
        }
        if (!self::$CI->db->query(sprintf($sql, 'calcexp_open_woa'.$suffix, 'calcexp_open_woa'.$suffix, 'IS NOT NULL', $sql3))) {
            die('ERROR WHEN CREATING DATA 14.5.13');
        }
        if (!self::$CI->db->query(sprintf($sql, 'calcexp_done_woa'.$suffix, 'calcexp_done_woa'.$suffix, 'IS NOT NULL', $sql1))) {
            die('ERROR WHEN CREATING DATA 14.5.14');
        }

        $sql = '
            UPDATE '.self::$temptable.' AS temptab SET
            %s = %s + (
                SELECT COALESCE(SUM(ROUND(e.price_per_unit, 2)*IF(e.number_external IS NOT NULL, e.number_external, e.number)),0)
                FROM extras AS e WHERE e.project_id=temptab.id AND e.calcexp_id %s %s
            )
            WHERE temptab.is_calc_from_expenses = 1
        ';
        $sql1 = 'AND e.deducted_day IS NOT NULL AND e.is_ignore = 0 AND e.deducted_day <= \''.$request_vars->keydate->mysqlday().'\'';
        $sql2 = 'AND e.date <= \''.$request_vars->keydate->mysqlday().'\'';
        $sql3 = 'AND e.date <= \''.$request_vars->keydate->mysqlday().'\' AND e.deducted_day IS NOT NULL AND e.is_ignore = 0 AND e.deducted_day > \''.$request_vars->keydate->mysqlday().'\'';
        if ($suffix==='' && !self::$CI->db->query(sprintf($sql, 'calcexp_open_proj_curr', 'calcexp_open_proj_curr', 'IS NULL', ''))) {
            die('ERROR WHEN CREATING DATA 14.6.01');
        }
        if (!self::$CI->db->query(sprintf($sql, 'calcexp_open_proj'.$suffix, 'calcexp_open_proj'.$suffix, 'IS NULL', $sql2))) {
            die('ERROR WHEN CREATING DATA 14.6.02');
        }
        if (!self::$CI->db->query(sprintf($sql, 'calcexp_open_proj'.$suffix, 'calcexp_open_proj'.$suffix, 'IS NOT NULL', $sql3))) {
            die('ERROR WHEN CREATING DATA 14.6.03');
        }
        if (!self::$CI->db->query(sprintf($sql, 'calcexp_done_proj'.$suffix, 'calcexp_done_proj'.$suffix, 'IS NOT NULL', $sql1))) {
            die('ERROR WHEN CREATING DATA 14.6.04');
        }
        if ($suffix==='' && !self::$CI->db->query(sprintf($sql, 'calcexp_open_woa_curr', 'calcexp_open_woa_curr', 'IS NULL', ''))) {
            die('ERROR WHEN CREATING DATA 14.6.11');
        }
        if (!self::$CI->db->query(sprintf($sql, 'calcexp_open_woa'.$suffix, 'calcexp_open_woa'.$suffix, 'IS NULL', $sql2))) {
            die('ERROR WHEN CREATING DATA 14.6.12');
        }
        if (!self::$CI->db->query(sprintf($sql, 'calcexp_open_woa'.$suffix, 'calcexp_open_woa'.$suffix, 'IS NOT NULL', $sql3))) {
            die('ERROR WHEN CREATING DATA 14.6.13');
        }
        if (!self::$CI->db->query(sprintf($sql, 'calcexp_done_woa'.$suffix, 'calcexp_done_woa'.$suffix, 'IS NOT NULL', $sql1))) {
            die('ERROR WHEN CREATING DATA 14.6.14');
        }

        $sql = '
            UPDATE '.self::$temptable.' AS temptab SET
            %s = %s + (
                SELECT COALESCE(SUM(ROUND(trav.price_per_unit, 2)*IF(trav.number_external IS NOT NULL, trav.number_external, trav.number)),0)
                FROM travels AS trav WHERE trav.project_id=temptab.id AND trav.calcexp_id %s %s
            )
            WHERE temptab.is_calc_from_expenses = 1
        ';
        $sql1 = 'AND trav.deducted_day IS NOT NULL AND trav.is_ignore = 0 AND trav.deducted_day <= \''.$request_vars->keydate->mysqlday().'\'';
        $sql2 = 'AND trav.date <= \''.$request_vars->keydate->mysqlday().'\'';
        $sql3 = 'AND trav.date <= \''.$request_vars->keydate->mysqlday().'\' AND trav.deducted_day IS NOT NULL AND trav.is_ignore = 0 AND trav.deducted_day > \''.$request_vars->keydate->mysqlday().'\'';
        if ($suffix==='' && !self::$CI->db->query(sprintf($sql, 'calcexp_open_proj_curr', 'calcexp_open_proj_curr', 'IS NULL', ''))) {
            die('ERROR WHEN CREATING DATA 14.7.01');
        }
        if (!self::$CI->db->query(sprintf($sql, 'calcexp_open_proj'.$suffix, 'calcexp_open_proj'.$suffix, 'IS NULL', $sql2))) {
            die('ERROR WHEN CREATING DATA 14.7.02');
        }
        if (!self::$CI->db->query(sprintf($sql, 'calcexp_open_proj'.$suffix, 'calcexp_open_proj'.$suffix, 'IS NOT NULL', $sql3))) {
            die('ERROR WHEN CREATING DATA 14.7.03');
        }
        if (!self::$CI->db->query(sprintf($sql, 'calcexp_done_proj'.$suffix, 'calcexp_done_proj'.$suffix, 'IS NOT NULL', $sql1))) {
            die('ERROR WHEN CREATING DATA 14.7.04');
        }
        if ($suffix==='' && !self::$CI->db->query(sprintf($sql, 'calcexp_open_woa_curr', 'calcexp_open_woa_curr', 'IS NULL', ''))) {
            die('ERROR WHEN CREATING DATA 14.7.11');
        }
        if (!self::$CI->db->query(sprintf($sql, 'calcexp_open_woa'.$suffix, 'calcexp_open_woa'.$suffix, 'IS NULL', $sql2))) {
            die('ERROR WHEN CREATING DATA 14.7.12');
        }
        if (!self::$CI->db->query(sprintf($sql, 'calcexp_open_woa'.$suffix, 'calcexp_open_woa'.$suffix, 'IS NOT NULL', $sql3))) {
            die('ERROR WHEN CREATING DATA 14.7.13');
        }
        if (!self::$CI->db->query(sprintf($sql, 'calcexp_done_woa'.$suffix, 'calcexp_done_woa'.$suffix, 'IS NOT NULL', $sql1))) {
            die('ERROR WHEN CREATING DATA 14.7.14');
        }

        $sql = '
            UPDATE '.self::$temptable.' AS temptab SET
            calcexp_loc'.$suffix.' = (
                SELECT SUM(COALESCE(pf.fixed_value, pf.value))
                FROM payments_fees AS pf
                INNER JOIN payments AS p
                    ON pf.payments_id = p.id
                INNER JOIN project_offers AS po
                    ON p.offer_id = po.id
                WHERE
                    p.status = 20
                AND
                    p.fee_date <= \''.$request_vars->keydate->mysqlday().'\'
                AND
                    po.project_id = temptab.id
            )
        ';
        if (!self::$CI->db->query($sql)) {
            die('ERROR WHEN CREATING DATA 14.8');
        }
    }
    
    /**
     * Create the data for cost centres
     * 
     * @param   object      $request_vars           Request vars
     * @param   integer     $fee_multi_mode_set     Fee multiplier set item_id
     * @param   string      $suffix                 Suffix
     * @param   object      $date_now               Now date
     * @return  void
     */
    private function _create_data_sub__cost_centres($request_vars, $fee_multi_mode_set, $suffix, UsDate $date_now)
    {
        if (!$request_vars->showcostcentres) {
            return;
        }
        
        $sql_main = '
                FROM work_packages AS wptab 
                WHERE 
                    wptab.project_id=temptab.id
                    AND
                    wptab.is_fee_area=1
                    AND
                    wptab.cost_centre_id=%s
                ';

        $sql_history = '
                FROM work_packages_history AS wptab 
                    INNER JOIN 
                    work_packages AS wp 
                    ON 
                    wptab.wp_id=wp.id
                WHERE 
                    wptab.project_id=temptab.id
                    AND
                    wp.is_fee_area=1
                    AND
                    wp.cost_centre_id=%s
                    AND 
                    wptab.date = temptab.date%s
                ';

        $sql = '
            UPDATE '.self::$temptable.' AS temptab
            SET cs_planned_fees_%s'.$suffix.' = (
                SELECT IFNULL(SUM(wptab.fee_costs), 0) %s
            )
        ';

        foreach ($this->cost_centres_list as $cost_centre) {
            if (!self::$CI->db->query(sprintf($sql, $cost_centre->id, sprintf($sql_main, $cost_centre->id)))) {
                die('ERROR WHEN CREATING DATA 15.1');
            }

            if (datetime_diff_days($request_vars->keydate, $date_now) >= 0) {
                if (!self::$CI->db->query(sprintf($sql, $cost_centre->id, sprintf($sql_history, $cost_centre->id, $suffix)))) {
                    die('ERROR WHEN CREATING DATA 15.2');
                }
            }
            
            $request_vars_cs = clone $request_vars;
            $request_vars_cs->item_cost_centre = $cost_centre->id;
            
            self::$CI->db->query('UPDATE '.self::$temptable.' SET cs_total_cost_'.$cost_centre->id.$suffix.'=0');
            
            self::$CI->db->query('UPDATE '.self::$temptable.' SET temp=NULL WHERE temp IS NOT NULL');
            self::$CI->total_costs_updater->update_times($request_vars_cs, $fee_multi_mode_set, self::$temptable, 'id', 'temp');
            self::$CI->db->query('UPDATE '.self::$temptable.' SET cs_total_cost_'.$cost_centre->id.$suffix.'=cs_total_cost_'.$cost_centre->id.$suffix.'+temp WHERE temp IS NOT NULL');
            
            self::$CI->db->query('UPDATE '.self::$temptable.' SET temp=NULL WHERE temp IS NOT NULL');
            self::$CI->total_costs_updater->update_travels($request_vars_cs, self::$temptable, 'id', 'temp');
            self::$CI->db->query('UPDATE '.self::$temptable.' SET cs_total_cost_'.$cost_centre->id.$suffix.'=cs_total_cost_'.$cost_centre->id.$suffix.'+temp WHERE temp IS NOT NULL');

            self::$CI->db->query('UPDATE '.self::$temptable.' SET temp=NULL WHERE temp IS NOT NULL');
            self::$CI->total_costs_updater->update_extras($request_vars_cs, self::$temptable, 'id', 'temp');
            self::$CI->db->query('UPDATE '.self::$temptable.' SET cs_total_cost_'.$cost_centre->id.$suffix.'=cs_total_cost_'.$cost_centre->id.$suffix.'+temp WHERE temp IS NOT NULL');

            self::$CI->db->query('UPDATE '.self::$temptable.' SET temp=NULL WHERE temp IS NOT NULL');
            self::$CI->total_costs_updater->update_externalexpenses($request_vars_cs, self::$temptable, false, false, 'id', 'temp');
            self::$CI->db->query('UPDATE '.self::$temptable.' SET cs_total_cost_'.$cost_centre->id.$suffix.'=cs_total_cost_'.$cost_centre->id.$suffix.'+temp WHERE temp IS NOT 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, '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, '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, '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, '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, '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');
        
        if ($request_vars->showextexpdetails && $request_vars->showcategories) {
            foreach ($this->fee_categories_list as $fee_category) {
                self::$CI->total_costs_updater->update_externalexpenses($request_vars, self::$temptable, false, false, 'id', $prefix_as.'_externalexpenses_cost_'.$fee_category->id.$suffix, false, $fee_category->id);
                self::$CI->db->query('UPDATE '.self::$temptable.' SET '.$prefix_as.'_externalexpenses_cost_'.$fee_category->id.$suffix.'=0 WHERE '.$prefix_as.'_externalexpenses_cost_'.$fee_category->id.$suffix.' IS NULL');
            }
        }
    }

    /**
     * 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('started_work', true);
        self::$CI->tempdb->add_field('id', 'integer');
        self::$CI->tempdb->add_field('project', 'varchar(200)');
        self::$CI->tempdb->add_field('is_calc_from_expenses', 'bool');
        self::$CI->tempdb->add_field('temp', 'double default 0');
        
        $this->_init_temp_table_sub($request_vars, '');
        $this->_init_temp_table_sub($request_vars, '_prev');

        $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
     * @param   string  $suffix                 Suffix
     * @return  void
     */
    private function _init_temp_table_sub($request_vars, $suffix)
    {
        self::$CI->tempdb->add_field('date'.$suffix, 'datetime');
        self::$CI->tempdb->add_field('planned_fees'.$suffix, 'double default 0');
        self::$CI->tempdb->add_field('planned_fees_external'.$suffix, 'double default 0');
        self::$CI->tempdb->add_field('planned_fees_externallist'.$suffix, 'varchar('.self::MAX_EXTERNALLIST_LENGTH.')');
        self::$CI->tempdb->add_field('budget_plan_costs'.$suffix, 'double default 0');
        self::$CI->tempdb->add_field('budget_plan_costs_external'.$suffix, 'double default 0');
        self::$CI->tempdb->add_field('fees_plan_hours'.$suffix, 'double default 0');
        self::$CI->tempdb->add_field('budget_plan_hours'.$suffix, 'double default 0');
        self::$CI->tempdb->add_field('completion_level'.$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('completed'.$suffix, 'double default 0');
        self::$CI->tempdb->add_field('completed_budget'.$suffix, 'double default 0');
        self::$CI->tempdb->add_field('hon_decr_costs'.$suffix, 'double default 0');
        self::$CI->tempdb->add_field('fees_invoiced'.$suffix, 'double default 0');
        self::$CI->tempdb->add_field('fees_invoiced_finally_invoiced'.$suffix, 'double default 0');
        self::$CI->tempdb->add_field('fees_invoiced_finally_invoiced_not'.$suffix, 'double default 0');
        self::$CI->tempdb->add_field('fees_planned'.$suffix, 'double default 0');
        self::$CI->tempdb->add_field('fees_remited'.$suffix, 'double default 0');
        self::$CI->tempdb->add_field('fees_deduction'.$suffix, 'double default 0');
        
        self::$CI->tempdb->add_field('fees_planned_before'.$suffix, 'double default 0');
        self::$CI->tempdb->add_field('fees_invoiced_after'.$suffix, 'double default 0');
        self::$CI->tempdb->add_field('fees_remited_after'.$suffix, 'double default 0');
        
        if ($request_vars->showoffers) {
            self::$CI->tempdb->add_field('offers_offered'.$suffix, 'double default 0');
            self::$CI->tempdb->add_field('offers_ordered'.$suffix, 'double default 0');
        }

        if ($request_vars->showcategories) {
            foreach ($this->fee_categories_list as $fee_category) {
                self::$CI->tempdb->add_field('total_externalexpenses_cost_'.$fee_category->id.$suffix, 'double default 0');
                self::$CI->tempdb->add_field('fees_invoiced_'.$fee_category->id.$suffix, 'double default 0');
            }
        }
        
        if ($request_vars->showcalcexp) {
            self::$CI->tempdb->add_field('calcexp_lastdate'.$suffix, 'datetime default \''.UsBasicDateTime::DATEISNULL.'\'');
            if ($suffix==='') {
                self::$CI->tempdb->add_field('calcexp_open_proj_curr', 'double default NULL');
                self::$CI->tempdb->add_field('calcexp_open_woa_curr', 'double default NULL');
            }
            self::$CI->tempdb->add_field('calcexp_open_proj'.$suffix, 'double default NULL');
            self::$CI->tempdb->add_field('calcexp_open_woa'.$suffix, 'double default NULL');
            self::$CI->tempdb->add_field('calcexp_done_proj'.$suffix, 'double default NULL');
            self::$CI->tempdb->add_field('calcexp_done_woa'.$suffix, 'double default NULL');
            
            self::$CI->tempdb->add_field('calcexp_loc_lastdate'.$suffix, 'datetime default \''.UsBasicDateTime::DATEISNULL.'\'');
            self::$CI->tempdb->add_field('project_no_estim_offers'.$suffix, 'double default NULL');
            self::$CI->tempdb->add_field('calcexp_loc'.$suffix, 'double default 0');
            
            self::$CI->tempdb->add_field('fees_invoiced_lastdate'.$suffix, 'datetime default \''.UsBasicDateTime::DATEISNULL.'\'');
            
            self::$CI->tempdb->add_field('calcexp_times_hours'.$suffix, 'double default 0');
        }
        
        foreach ($this->cost_centres_list as $cost_centre) {
            self::$CI->tempdb->add_field('cs_planned_fees_'.$cost_centre->id.$suffix, 'double default 0');
            self::$CI->tempdb->add_field('cs_total_cost_'.$cost_centre->id.$suffix, 'double default 0');
        }
        
        if ($request_vars->showdates && $suffix==='') {
            self::$CI->tempdb->add_field('last_times'.$suffix, 'datetime default \''.UsBasicDateTime::DATEISNULL.'\'');
            self::$CI->tempdb->add_field('last_completion_level'.$suffix, 'datetime default \''.UsBasicDateTime::DATEISNULL.'\'');
        }
        
    }
    
} // END class Started_work_analysis
