<?php

class Project {

    protected $info = array();
    private $db;

    public function __construct() {
        $this->db = DBConnectorFactory::loadDriver();
        $this->logged_id = smc_session()['user_logged_id'];
        $this->date_time = date('Y-m-d H:i:s');
    }

    public function setdata(array $data) {
        $this->info = $data;
        return $this;
    }

    public function get_activity_tree($parent_id = 0) {
        $data = array();
        $query = $this->db->query('select * from activity_tree_list where parent_id = "' . $parent_id . '"');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function submit_activity_tree() {
        $this->info['parent_id'] = empty($this->info['parent_id']) ? 0 : $this->info['parent_id'];
        $this->info['account_id'] = empty($this->info['account_id']) ? NULL : $this->info['account_id'];
        if ($this->info['activity_id'] == 0) {
            $this->db->prepare_Stmt('INSERT INTO `tbl_activity_tree`(`activity_code`, `activity_title`,activity_title_alias, `parent_id`, `account_id`, `is_final`, `date_created`, `created_logged_id`, `date_modified`, `modified_by`)
            VALUES (?,?,?,?,?,?,?,?,?,?)', [&$this->info['activity_code'], &$this->info['activity_title'], $this->info['activity_title_alias'],
                &$this->info['parent_id'], &$this->info['account_id'],
                &$this->info['is_final'], &$this->date_time, &$this->logged_id,
                &$this->date_time, &$this->logged_id]);
        } else {
            $this->db->prepare_Stmt('update `tbl_activity_tree` 
            set `activity_code` = ?, `activity_title` = ?,activity_title_alias=?, `account_id` = ?, `is_final` = ? ,
            `date_modified` = ?, `modified_by` = ? WHERE activity_id =? ', [&$this->info['activity_code'], &$this->info['activity_title'],
                $this->info['activity_title_alias'], &$this->info['account_id'],
                &$this->info['is_final'], &$this->date_time, &$this->logged_id,
                &$this->info['activity_id']]);
        }
        $error = $this->db->error();
        return array_unique($error);
    }

    public function delete_activity_tree() {
        $activity_childs = CommonFunctions::get_activity($this->info['entry_value']);
        $query = $this->db->prepare_stmt('select * from  tbl_activity_budget where activity_id in (' . $activity_childs . ')');
        $row_count_1 = $this->db->rowsno($query);
        $query = $this->db->prepare_stmt('select * from tbl_project_period where  FIND_IN_SET(' . $activity_childs . ', activity_ids) ');
        $row_count_2 = $this->db->rowsno($query);
        if ($row_count_2 != 0 || $row_count_1 != 0) {
            $error = CommonLanguage::getMessage('activity_used');
        } else {
            $this->db->prepare_Stmt('Delete from tbl_activity_tree where activity_id = ?
                                         ', [&$this->info['entry_value']]);
            $error = $this->db->error();
        }
        return $error;
    }

    public function searchMainActivity($term = null, $page, $limit) {
        $data = array();
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        $data_filter = array();
        if (!is_null($term)) {
            $cond .= ' and (activity_title LIKE ? or activity_code like ? )';
            $data_filter[] = "%" . $term . "%";
            $data_filter[] = "%" . $term . "%";
        }
        $is_final = !empty($this->info['is_final']) ? 1 : 0;
        $query = $this->db->prepare_Stmt('select concat_ws(" - ",activity_code,activity_title) as text,
                                    activity_id as id 
                                from  tbl_activity_tree
                                where is_final = "' . $is_final . '"
                                ' . $cond . '
                                limit ' . ($page * $limit) . ',' . ($limit + 1), $data_filter);
        while ($row = $this->db->assoc($query)) {
            $data['data'][] = $row;
        }
        if (count($data['data']) > $limit) {
            array_pop($data['data']);
            $data['is_more'] = true;
        }
        return $data;
    }

    public function submit_project_period() {
        $this->db->query('START TRANSACTION');
        $activity_ids = explode(",", $this->info['activity_ids']);
        if (!empty($activity_ids)) {
            foreach ($activity_ids as $activity_id) {
                $all_activities = CommonFunctions::get_activity($activity_id, true);
                if (!empty($all_activities)) {
                    $all_activities = explode(",", $all_activities);
                    if (!empty(array_intersect($all_activities, $activity_ids)))
                        return[CommonLanguage::getMessage('activity_link_parent_sel')];
                }
            }
        }
        if ($this->info['period_id'] == 0) {
            $this->info['period_nb'] = self::get_max_project_period();
            $this->db->prepare_stmt('INSERT INTO `tbl_project_period`
            (`period_title`, `activity_ids`, `period_nb`, 
            `project_id`, `start_date`, `end_date`, 
            `nb_period`, `period_unit`, `currency_id`,`account_id`,
            `date_created`, `created_logged_id`, `date_modified`, `modified_by`)
            VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)', [&$this->info['period_title'], &$this->info['activity_ids'],
                &$this->info['period_nb'], &$this->info['project_id'],
                &$this->info['start_date'], &$this->info['end_date'],
                &$this->info['nb_period'], &$this->info['period_unit'],
                &$this->info['currency_id'], &$this->info['account_id'],
                &$this->date_time, &$this->logged_id, &$this->date_time, &$this->logged_id]);
            $this->info['period_id'] = $this->db->id();
        } else {
            $this->db->prepare_stmt('update `tbl_project_period`
            set `period_title` = ?, `activity_ids` = ?,
            `project_id` =? , `start_date` = ?, `end_date` = ?, 
            `nb_period` = ?, `period_unit` =?, `currency_id` = ?,`account_id` = ?,
            `date_modified` =?, `modified_by` = ? where period_id = ?', [&$this->info['period_title'], &$this->info['activity_ids'],
                &$this->info['project_id'],
                &$this->info['start_date'], &$this->info['end_date'],
                &$this->info['nb_period'], &$this->info['period_unit'],
                &$this->info['currency_id'], &$this->info['account_id'],
                &$this->date_time, &$this->logged_id,
                &$this->info['period_id']]);
            $this->db->prepare_stmt('Delete from tbl_project_period_details where period_id = ? ', [&$this->info['period_id']]);
        }
        $details = json_decode($this->info['details'], true);
        foreach ($details as $row) {
            $this->db->prepare_stmt('INSERT INTO `tbl_project_period_details`
            ( ' . (!empty($row['detail_id']) ? 'detail_id,' : '') . '`period_id`, `detail_period_title`, `start_date`, `end_date`) 
                VALUES (' . (!empty($row['detail_id']) ? $row['detail_id'] . ',' : '') . '?,?,?,?)', [&$this->info['period_id'], &$row['detail_period_title'], &$row['start_date'], &$row['end_date']]);
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

    public function get_max_project_period() {
        $query = $this->db->query('SELECT MAX(period_nb) AS Entry_ID 
            FROM tbl_project_period ');
        $row = $this->db->assoc($query);
        if (!empty($row))
            $max_nb = $row['Entry_ID'] + 1;
        else
            $max_nb = 1;
        return $max_nb;
    }

    public function check_project_period_used($period_id) {
        $flag = false;
        $query = $this->db->prepare_stmt('select * from  tbl_budget where period_id = ?', [$period_id]);
        $row_count = $this->db->rowsno($query);
        if ($row_count != 0)
            $flag = true;
        //
        $query = $this->db->prepare_stmt('select * from  tbl_project_funding f 
            join tbl_project_period_details d on d.detail_id = f.detail_id
            where period_id = ?', [$period_id]);
        $row_count = $this->db->rowsno($query);
        if ($row_count != 0)
            $flag = true;
        //
        $query = $this->db->prepare_stmt('select * from tbl_project_expense where period_id = ?', [$period_id]);
        $row_count = $this->db->rowsno($query);
        if ($row_count != 0)
            $flag = true;
        return $flag;
    }

    public function delete_project_period() {
        $is_used = $this->check_project_period_used($this->info['entry_value']);
        if ($is_used)
            return CommonLanguage::getMessage('project_period_used');
        $this->db->query('START TRANSACTION');
        $this->db->prepare_Stmt('Delete from tbl_project_period where period_id = ?
                                         ', [&$this->info['entry_value']]);
        $this->db->prepare_Stmt('Delete from tbl_project_period_details where period_id = ?
                                         ', [&$this->info['entry_value']]);
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

    public function get_period_activities($activity_ids) {
        $data = array();
        $query = $this->db->prepare_Stmt('select activity_title as text,
                                    activity_id as id 
                                from  tbl_activity_tree
                                where activity_id in (' . $activity_ids . ')');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function searchProjectPeriodDetails($term = null, $page, $limit) {
        $data = array();
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        $data_filter = array();
        if (!is_null($term)) {
            $cond .= ' and (detail_period_title LIKE ? or start_date LIKE ? or end_date LIKE ?)';
            $data_filter[] = "%" . $term . "%";
            $data_filter[] = "%" . $term . "%";
            $data_filter[] = "%" . $term . "%";
        }
        $query = $this->db->prepare_Stmt('select concat(detail_period_title," (",start_date," ",end_date,")") as text,
                                    detail_id as id,
                                    start_date,
                                    end_date
                                from  tbl_project_period_details
                               where period_id = "' . $this->info['period_id'] . '"
                               ' . (!empty($this->info['is_not_linked']) ? '' : ' and detail_id not in (select detail_id from tbl_project_funding)') . '
                                ' . $cond . '
                                limit ' . ($page * $limit) . ',' . ($limit + 1), $data_filter);
        while ($row = $this->db->assoc($query)) {
            $data['data'][] = $row;
        }
        if (count($data['data']) > $limit) {
            array_pop($data['data']);
            $data['is_more'] = true;
        }
        return $data;
    }

    public function searchProjectPeriodActivity($term = null, $page, $limit) {
        $data = array();
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        $data_filter = array();
        $activity_ids = '';
        $project_period = $this->info['period_id'];
        $query_period = $this->db->query('select activity_ids from tbl_project_period where period_id = "' . $project_period . '" ');
        $period_row = $this->db->assoc($query_period);
        if (!empty($period_row['activity_ids'])) {
            $period_row['activity_ids'] = explode(",", $period_row['activity_ids']);
            foreach ($period_row['activity_ids'] as $val) {
                $activity_ids .= ($activity_ids == '' ? '' : ',') . CommonFunctions::get_activity($val);
            }
        }
        if (!is_null($term)) {
            $cond .= ' and (activity_title LIKE ? or activity_code like ?)';
            $data_filter[] = "%" . $term . "%";
            $data_filter[] = "%" . $term . "%";
        }
        $query = $this->db->prepare_Stmt('select ' . (!empty($this->info['is_alias']) && smc_session()['default_setting']['activate_activity_alias'] == 1 ? 'concat_ws(" - ",activity_code,if(activity_title_alias="",activity_title,activity_title_alias))' : 'concat_ws(" - ",activity_code,activity_title)') . ' as text,
                                    activity_id as id,
                                    account_id
                                from  tbl_activity_tree
                               where is_final = 1 ' . ($activity_ids == '' ? 'and 1=2' : ' and  activity_id in (' . $activity_ids . ') ') . '
                                ' . $cond . '
                                limit ' . ($page * $limit) . ',' . ($limit + 1), $data_filter);
        while ($row = $this->db->assoc($query)) {
            $data['data'][] = $row;
        }
        if (count($data['data']) > $limit) {
            array_pop($data['data']);
            $data['is_more'] = true;
        }
        return $data;
    }

    public function searchProjectPeriod($term = null, $page, $limit) {
        $data = array();
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        $data_filter = array();
        if (!is_null($term)) {
            $cond .= ' and (period_title LIKE ? or start_date LIKE ? or end_date LIKE ?)';
            $data_filter[] = "%" . $term . "%";
            $data_filter[] = "%" . $term . "%";
            $data_filter[] = "%" . $term . "%";
        }
        $query = $this->db->prepare_Stmt('select concat(project_period.period_title," - ",projects.project_name," (",project_period.start_date," ",project_period.end_date,")") as text,
                                    project_period.period_id as id,
                                    project_period.project_id,
                                    project_period.account_id,
                                    project_period.currency_id,
                                    currency.Currency_Name
                                from  tbl_project_period project_period
                                inner join tbl_projects projects on projects.project_id = project_period.project_id
                                inner join tbl_common_currency currency on currency.Currency_ID = project_period.currency_id
                                where 1=1
                                ' . $cond . '
                                limit ' . ($page * $limit) . ',' . ($limit + 1), $data_filter);
        while ($row = $this->db->assoc($query)) {
            $data['data'][] = $row;
        }
        if (count($data['data']) > $limit) {
            array_pop($data['data']);
            $data['is_more'] = true;
        }
        return $data;
    }

    public function activity_budget() {
        $data = array();
        $period_arr = array();
        $header_arr = array();
        $header = array('activity' => '',
            'total_budget' => '',
            'is_partner' => '');
        $header_arr = [['name' => CommonLanguage::getMessage('activity')],
            ['name' => CommonLanguage::getMessage('total')]];
        $query_period = $this->db->query('select * from tbl_project_period where period_id ="' . $this->info['period_id'] . '"');
        $row_period = $this->db->assoc($query_period);
        $query = $this->db->query('select * from tbl_project_period_details where period_id = "' . $this->info['period_id'] . '"
                                   order by detail_id');
        while ($row = $this->db->assoc($query)) {
            $row['header_index'] = count($header);
            $period_arr[$row['detail_id']] = $row;
            $header[$row['detail_id'] . '_total'] = 0;
            $header_arr[] = ['name' => $row['detail_period_title']];
        }
//        echo '<pre>';
        $parent_activity_arr = array();
        $header_arr[] = ['name' => CommonLanguage::getMessage('is_partner')];
        if (!empty($row_period['activity_ids'])) {
            $parent_activity_query = $this->db->query('select * from tbl_activity_tree where activity_id in (' . $row_period['activity_ids'] . ')');
            while ($row = $this->db->assoc($parent_activity_query)) {
                $parent_activity_arr[$row['activity_id']] = $row;
            }
            $activity_ids_arr = explode(",", $row_period['activity_ids']);
            //            print_r($activity_ids_arr);
            //            die('ok');
            foreach ($activity_ids_arr as $activity_id) {
                $parents_arr = array();
                $activity_arr = array();
                $data[$activity_id] = [$parent_activity_arr[$activity_id]['activity_code'] . ' - ' . $parent_activity_arr[$activity_id]['activity_title'], ''];
                foreach ($period_arr as $detail_id => $detail_row)
                    $data[$activity_id][$detail_row['header_index']] = '';
                array_push($data[$activity_id], '');
                $data[$activity_id]['category_total'] = 0;
                CommonFunctions::get_activity_tree($activity_id, $parents_arr, $activity_arr);
                if (!empty($parents_arr[$activity_id])) {
                    foreach ($parents_arr[$activity_id] as $parent_id) {
                        $this->build_activity_tree_arr($data, $parent_id, $parents_arr, $activity_arr, $period_arr);
                    }
                }
            }
        }
        $period_arr_keys = array_keys($period_arr);
//        print_r($period_arr_keys);
        $budget_id = 0;
        $query = $this->db->query('select budget.*
                                   from tbl_activity_budget budget
                                   inner join tbl_budget budget_main on budget_main.budget_id = budget.budget_id
                                   left join tbl_project_period_details period_details 
                                   on period_details.detail_id = budget.detail_id 
                                   inner join tbl_activity_tree activity 
                                   on activity.activity_id = budget.activity_id
                                   where budget_main.period_id = "' . $this->info['period_id'] . '"');
        while ($row = $this->db->assoc($query)) {
//            print_r($row);
            $budget_id = $row['budget_id'];
            if (!empty($row['detail_id']) && in_array($row['detail_id'], $period_arr_keys)) {
                $detail_indx = $period_arr[$row['detail_id']]['header_index'];
                $data[$row['activity_id']][$detail_indx] = '<input type="number"  min="0" step="any" class="form-control detail" data-activity-id=' . $row['activity_id'] . ' data-detail-id=' . $row['detail_id'] . ' name="detail" value=' . $row['total'] . ' />';
                $data[$row['activity_id']]['category_total'] += $row['total'];
            } else {
                $data[$row['activity_id']][1] = '<input type="number" min="0" step="any" name="total_budget" class="form-control total_budget" value=' . $row['total'] . ' />';
                $data[$row['activity_id']]['is_partner'] = '<input type="checkbox" name="is_partner" class="is_partner" data-activity-id=' . $row['activity_id'] . ' checked/>';
                foreach ($period_arr as $detail_id => $detail_row)
                    $data[$row['activity_id']][$detail_row['header_index']] = '<input type="number"  min="0" step="any"  class="form-control detail" data-activity-id=' . $row['activity_id'] . ' data-detail-id=' . $detail_id . ' name="detail"  readonly/>';
            }
        }
            //    print_r($data); die('ok');

        //        echo"<pre>";
        //        die(print_r($data));
        $data = array_map(function($val) {
            if (!empty($val['category_total']) && !empty($val['is_partner']))
                $val[1] = '<input type="number"  min="0" step="any"  name="total_budget" class="form-control total_budget" value="' . $val['category_total'] . '" />';
            unset($val['category_total']);
            return array_values($val);
        }, $data);
        return ['body' => array_values($data), 'header' => $header_arr, 'budget_id' => $budget_id];
    }

    public function build_activity_tree_arr(&$data, $activity_id, $parents_arr, $activity_arr, $period_arr) {
        $activity_data = $this->get_activity_data($activity_id, $parents_arr, $activity_arr, $period_arr);
        if (!empty($activity_data)) {
            $data[$activity_id] = $activity_data[$activity_id];
        }
        if (!empty($parents_arr[$activity_id])) {
            foreach ($parents_arr[$activity_id] as $child_id) {
                $this->build_activity_tree_arr($data, $child_id, $parents_arr, $activity_arr, $period_arr);
            }
        }
    }

    public function get_activity_data($activity_id, $parents_arr, $activity_arr, $period_arr) {
        $total = $paid = $remaining = 0;
        $blocks = array($activity_id);
        $this->get_activity_childs($activity_id, $blocks, $parents_arr);
        $current_array = array();
        $current_array[$activity_id][0] = $activity_arr[$activity_id]['activity_code'] . ' - ' . $activity_arr[$activity_id]['activity_title'];
        $current_array[$activity_id][1] = $activity_arr[$activity_id]['is_final'] == 1 ? '<input type="number" min="0" step="any" name="total_budget" class="form-control total_budget"  />' : '';
        foreach ($period_arr as $detail_id => $detail_row)
            $current_array[$activity_id][$detail_row['header_index']] = $activity_arr[$activity_id]['is_final'] == 1 ? '<input type="number" min="0" step="any" class="form-control detail" data-activity-id=' . $activity_id . ' data-detail-id=' . $detail_id . ' name="detail" />' : '';
        $current_array[$activity_id]['is_partner'] = $activity_arr[$activity_id]['is_final'] == 1 ? '<input type="checkbox" name="is_partner" class="is_partner" data-activity-id=' . $activity_id . ' />' : '';
        $current_array[$activity_id]['category_total'] = 0;
        return $current_array;
    }

    public function get_activity_childs($activity_id, &$data, $parents_arr, $is_direct = false) {
        if (!empty($parents_arr[$activity_id])) {
            foreach ($parents_arr[$activity_id] as $child_id) {
                $data[] = $child_id;
                if (!$is_direct) {
                    $this->get_activity_childs($child_id, $data, $parents_arr);
                }
            }
        }
    }

    public function submit_activity_budget() {
        $this->db->query('START TRANSACTION');
        if (!empty($this->info['activty_budget'])) {
            $activity_budget = $this->info['activty_budget'];
            if ($this->info['budget_id'] == 0) {
                $rate = CurrencyFactory::getLastExRate($this->info['currency_id']);
                $second_rate = CurrencyFactory::getSecondCurrencyLastExRate($this->info['currency_id']);
                $data = [$this->info['currency_id'], $rate, $second_rate, $this->info['period_id'],
                    &$this->info['project_id'], $this->date_time, $this->date_time, $this->logged_id,
                    $this->logged_id];
                $this->db->prepare_Stmt('INSERT INTO `tbl_budget`( `currency_id`, `current_rate`,
                `second_currency_rate`, `period_id`, `project_id`, `date_created`, `date_time`,
                `created_logged_id`, `logged_id`) values(?,?,?,?,?,?,?,?,?) ', $data);
                $budget_id = $this->db->id();
            } else {
                $this->db->prepare_Stmt('update `tbl_budget` set `date_time`=?,
                 `logged_id`=? where budget_id=?', [$this->date_time, $this->logged_id,
                    $this->info['budget_id']]);
                $budget_id = $this->info['budget_id'];
                $this->db->prepare_Stmt('delete from tbl_activity_budget where budget_id = ? ', [&$budget_id]);
            }
            foreach ($activity_budget as $row) {
                $detail_id = !empty($row['detail_id']) ? $row['detail_id'] : NULL;
                $is_partner = !empty($detail_id) ? 0 : 1;
                $this->db->prepare_Stmt('INSERT INTO `tbl_activity_budget`
            (budget_id,`detail_id`, `activity_id`, `total`, `is_partner`) VALUES (?,?,?,?,?)
            on duplicate key update
                     `total` = ?,`is_partner` = ?', [$budget_id, &$detail_id, &$row['activity_id'],
                    &$row['total'], &$is_partner, &$row['total'], &$is_partner]);
            }
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

    public function submit_project_funding() {
        $this->db->query('START TRANSACTION');
        $project_funding_id = $this->info['project_funding_id'];
        $is_actual = 0;
        $current_date = !empty($this->info['project_funding_date']) ? $this->info['project_funding_date'] : date('Y-m-d');
        if ($project_funding_id == 0) {
            $project_funding_nb = self::get_max_project_funding();
            $this->db->prepare_Stmt('INSERT INTO `tbl_project_funding`
            ( `project_funding_nb`, `detail_id`, `date_created`, `created_by`, `date_modified`, `modified_by`,payment_acc_id,project_funding_date)
            VALUES (?,?,?,?,?,?,?,?)', [&$project_funding_nb, &$this->info['detail_id'], &$this->date_time, &$this->logged_id,
                &$this->date_time, &$this->logged_id, &$this->info['payment_acc_id'], &$current_date]);
            $project_funding_id = $this->db->id();
        } else {
            $project_funding_nb = $this->info['project_funding_nb'];
            $this->db->prepare_Stmt('update `tbl_project_funding`
            set `date_modified` = ?, `modified_by` = ?,payment_acc_id=?,project_funding_date = ? where `project_funding_id`= ? ', [&$this->date_time, &$this->logged_id, &$this->info['payment_acc_id'], &$current_date, &$project_funding_id]);
            $this->db->prepare_Stmt('delete from tbl_accounting_transactions
                                     where main_id = ? and main_type= "pfd"', [&$project_funding_id]);
        }
        $query = $this->db->query('select main.*,
                                          details.detail_id
                                   from tbl_project_period_details details
                                   inner join tbl_project_period main using (period_id)
                                   where details.detail_id = "' . $this->info['detail_id'] . '"');
        $row_main = $this->db->assoc($query);
        $project_total = 0;
        $jv = JV::returnJV($is_actual);
        $currency_id = $row_main['currency_id'];
        $project_period_account = $row_main['account_id'];
        $project_id = $row_main['project_id'];
        $rate = CurrencyFactory::getLastExRate($currency_id);
        $second_rate = CurrencyFactory::getSecondCurrencyLastExRate($currency_id);
        $ref_num = 'Pfd.' . $project_funding_nb;

        foreach ($this->info['project_funding'] as $row) {
            $data = ["JV_ID" => $jv,
                "Transaction_Reference" => $ref_num,
                "Transaction_Account" => $row['account_id'],
                "Transaction_Type" => "Credit",
                "Transaction_Amount" => $row['total'],
                "Transaction_Date" => $current_date,
                "Currency_ID" => $currency_id,
                "Logged_ID" => $this->logged_id,
                "Transaction_Description" => 'Project funding',
                "Current_Ex_Rate" => $rate,
                "second_currency_rate" => $second_rate,
                "main_id" => $project_funding_id,
                "main_type" => "pfd",
                "main_nb" => $project_funding_nb,
                "project_id" => $project_id,
                "activity_id" => $row['activity_id']
            ];
            $project_total += $row['total'];
            JV::addTransaction($data, $is_actual);
        }
        $data_1 = ["JV_ID" => $jv,
            "Transaction_Reference" => $ref_num,
            "Transaction_Account" => $project_period_account,
            "Transaction_Type" => "Debit",
            "Transaction_Amount" => $project_total,
            "Transaction_Date" => $current_date,
            "Currency_ID" => $currency_id,
            "Logged_ID" => $this->logged_id,
            "Transaction_Description" => 'Project funding',
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "main_id" => $project_funding_id,
            "main_type" => "pfd",
            "main_nb" => $project_funding_nb,
            "project_id" => $project_id,
        ];
        JV::addTransaction($data_1, $is_actual);
        $data_1 = ["JV_ID" => $jv,
            "Transaction_Reference" => $ref_num,
            "Transaction_Account" => $project_period_account,
            "Transaction_Type" => "Credit",
            "Transaction_Amount" => $project_total,
            "Transaction_Date" => $current_date,
            "Currency_ID" => $currency_id,
            "Logged_ID" => $this->logged_id,
            "Transaction_Description" => 'Project funding',
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "main_id" => $project_funding_id,
            "main_type" => "pfd",
            "main_nb" => $project_funding_nb,
            "project_id" => $project_id,
        ];
        JV::addTransaction($data_1, $is_actual);
        $data_1 = ["JV_ID" => $jv,
            "Transaction_Reference" => $ref_num,
            "Transaction_Account" => $this->info['payment_acc_id'],
            "Transaction_Type" => "Debit",
            "Transaction_Amount" => $project_total,
            "Transaction_Date" => $current_date,
            "Currency_ID" => $currency_id,
            "Logged_ID" => $this->logged_id,
            "Transaction_Description" => 'Project funding',
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "main_id" => $project_funding_id,
            "main_type" => "pfd",
            "main_nb" => $project_funding_nb,
            "project_id" => $project_id,
        ];
        JV::addTransaction($data_1, $is_actual);
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

    public function get_max_project_funding() {
        $query = $this->db->query('SELECT MAX(project_funding_nb) AS Entry_ID 
            FROM tbl_project_funding ');
        $row = $this->db->assoc($query);
        if (!empty($row))
            $max_nb = $row['Entry_ID'] + 1;
        else
            $max_nb = 1;
        return $max_nb;
    }

    public function delete_project_funding() {
        $this->db->query('START TRANSACTION');
        $this->db->prepare_Stmt('delete from tbl_accounting_transactions where main_id = ?  and main_type= "pfd" ', [&$this->info['project_funding_id']]);
        $this->db->prepare_Stmt('delete from tbl_project_funding where project_funding_id = ? ', [&$this->info['project_funding_id']]);
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

    public function get_project_funding($project_funding_id) {
        $data = array('details' => array(), 'main' => array());
        $query = $this->db->query('select funding.*,
                                          concat(details.detail_period_title," (",details.start_date," ",details.end_date,")") as detail_title,
                                          project_period.period_id,
                                          project_period.project_id,
                                          project_period.account_id,
                                          project_period.currency_id,
                                          concat(project_period.period_title," - ",projects.project_name," (",project_period.start_date," ",project_period.end_date,")") as project_period_title,
                                          concat_ws(" - ",chart.Account_Name,chart.Account_Code) as payment_acc_name
                                   from tbl_project_funding funding
                                   inner join tbl_project_period_details details on details.detail_id = funding.detail_id
                                   inner join tbl_project_period project_period on project_period.period_id = details.period_id
                                   inner join tbl_projects projects on projects.project_id = project_period.project_id
                                   left join tbl_chart_of_accounts chart on chart.Account_ID = funding.payment_acc_id
                                   where funding.project_funding_id = "' . $project_funding_id . '"');
        $data['main'] = $this->db->assoc($query);
        $period_details_query = $this->db->query('select details.*,
                                                         activity.activity_title,
                                                         activity.activity_code,
                                                         activity.account_id
                                                  from  tbl_activity_budget details 
                                                  inner join tbl_budget budget_main on budget_main.budget_id = details.budget_id
                                                  inner join tbl_activity_tree activity on details.activity_id = activity.activity_id
                                                  where budget_main.period_id = "' . $data['main']['period_id'] . '"
                                                  and details.detail_id is null');
        while ($row = $this->db->assoc($period_details_query)) {
            $data['details'][] = [$row['activity_code'], $row['activity_title'], $row['total']];
        }
        $data['main']['currency_id'] = '';
        $data['main']['currency_name'] = '';
        $transaction_query = $this->db->query('select trans.activity_id,
                                                      trans.Transaction_Amount as total,
                                                      activity.activity_title,
                                                      activity.activity_code,
                                                      activity.account_id,
                                                      trans.Currency_ID
                                               from tbl_accounting_transactions trans
                                               inner join tbl_activity_tree activity using(activity_id)
                                               where trans.main_id = "' . $project_funding_id . '" and main_type = "pfd" ');
        while ($row = $this->db->assoc($transaction_query)) {
            $data['main']['currency_id'] = $row['Currency_ID'];
            $data['details'][] = [$row['activity_code'], $row['activity_title'], '<input type="number" class="form-control funding_total" name="funding_total" data-activity-id="' . $row['activity_id'] . '" data-account-id="' . $row['account_id'] . '" value="' . $row['total'] . '"/>'];
        }
        $data['main']['currency_name'] = CommonFunctions::SpecificAttribute('tbl_common_currency', ['Currency_ID'], [$data['main']['currency_id']], 'Currency_Name');
        return $data;
    }

    public function get_project_budget() {
        $data = array();
        $period_details_query = $this->db->query('select details.*,
                                                         activity.activity_code,
                                                         activity.activity_title,
                                                         activity.account_id
                                                  from  tbl_activity_budget details 
                                                  inner join tbl_budget budget_main on budget_main.budget_id = details.budget_id
                                                  inner join tbl_activity_tree activity on details.activity_id = activity.activity_id
                                                  where budget_main.period_id = "' . $this->info['period_id'] . '"
                                                  and (details.detail_id = "' . $this->info['detail_id'] . '" or details.detail_id is null)');
        while ($row = $this->db->assoc($period_details_query)) {
            $data[] = [$row['activity_code'], $row['activity_title'], empty($row['detail_id']) ? $row['total'] : '<input type="number" class="form-control funding_total" name="funding_total" data-activity-id="' . $row['activity_id'] . '" data-account-id="' . $row['account_id'] . '" value="' . $row['total'] . '"/>'];
        }
        return $data;
    }

    public function submit_project_expense() {
        $check = CommonFunctions::check_lock_data(['type' => 'project_expense', 'start_date' => $this->info['project_expense_date'], 'is_actual' => 0]);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        $this->info['branch_id'] = !empty($this->info['branch_id']) ? $this->info['branch_id'] : ( empty(smc_session()['user_accounts']['branch_id']) ? smc_session()['default_setting']['default_branch_id'] : smc_session()['user_accounts']['branch_id']);

        $flag = 0;
        $this->db->query('START TRANSACTION');
        $rate = !empty($this->info['Currency_Rate']) ? $this->info['Currency_Rate'] : CurrencyFactory::getLastExRate($this->info['Currency_ID']);
        $second_rate = !empty($this->info['second_currency_rate']) ? $this->info['second_currency_rate'] : CurrencyFactory::getSecondCurrencyLastExRate($this->info['Currency_ID']);
        $jv = JV::returnJV(0);

        $this->info['project_expense_total'] = isset($this->info['project_expense_total']) ? $this->info['project_expense_total'] : 0;
        $this->info['job_id'] = !empty($this->info['job_id']) ? $this->info['job_id'] : NULL;
        $project_expense_id = $this->info['project_expense_id'];
        if ($project_expense_id == 0) {
            $project_expense_nb = self::get_max_project_expense();
            $data = [&$project_expense_nb, &$this->info['project_expense_date'], &$this->info['period_id'],
                &$this->info['project_expense_total'], &$rate, &$second_rate,
                &$this->info['Currency_ID'], &$this->info['job_id'], &$this->info['branch_id'],
                &$this->date_time, &$this->logged_id, &$this->date_time, &$this->logged_id,
                $this->info['supplier_id'], $this->info['paid_amount'], $this->info['payment_acc_id'],
                $this->info['check_nb']];

            $this->db->prepare_Stmt('INSERT INTO `tbl_project_expense`
        (`project_expense_nb`,`project_expense_date`, `period_id`, `project_expense_total`, `current_rate`,
        `second_rate`, `currency_id`,`job_id`,`branch_id`,`date_created`, `created_by`, `date_modified`,
        `modified_by`,`supplier_id`, `paid_amount`, `payment_acc_id`, `check_nb`) 
        VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', $data);
            $project_expense_id = $this->db->id();
        } else {
            $project_expense_nb = $this->info['project_expense_nb'];
            $this->db->prepare_Stmt('update `tbl_project_expense`
        set `project_expense_date` = ?, `period_id` = ?, `project_expense_total` = ?, `current_rate` = ?,
        `second_rate` = ?, `currency_id` = ?,`job_id` = ?,`branch_id` = ?, `date_modified` = ?, `modified_by` = ?,
        `supplier_id`=?, `paid_amount`=?, `payment_acc_id`=?, `check_nb`=?
        where project_expense_id = ?', [&$this->info['project_expense_date'], &$this->info['period_id'],
                &$this->info['project_expense_total'], &$rate, &$second_rate,
                &$this->info['Currency_ID'], &$this->info['job_id'], &$this->info['branch_id'],
                &$this->date_time, &$this->logged_id, $this->info['supplier_id'], $this->info['paid_amount'], $this->info['payment_acc_id'],
                $this->info['check_nb'], &$project_expense_id]);
            $this->db->prepare_Stmt('delete from tbl_project_expense_detail where project_expense_id = ? ', [&$project_expense_id]);
            $this->db->prepare_Stmt('delete from tbl_accounting_transactions where main_id = ? and main_type="pex" ', [&$project_expense_id]);
        }
        $period_query = $this->db->query('
        select * 
        from tbl_project_period where period_id = "' . $this->info['period_id'] . '"');
        $period_row = $this->db->assoc($period_query);

        $activity_accounts = array();
        $activity_query = $this->db->query('
        select * 
        from tbl_activity_tree where activity_id in (' . implode(",", $this->info['activity_ids']) . ')');
        while ($row = $this->db->assoc($activity_query)) {
            $activity_accounts[$row['activity_id']] = $row['account_id'];
        }
        $ref = 'Pexp.' . $project_expense_nb;
        $supp_acc_id = Supplier::get_all_aupp_accs($this->info['supplier_id'])['exp'];
        $dat_2 = ["JV_ID" => $jv,
            "Transaction_Reference" => $ref,
            "Transaction_Account" => $supp_acc_id,
            "Transaction_Type" => "Credit",
            "Transaction_Amount" => $this->info['expense_total'],
            "Transaction_Date" => $this->info['project_expense_date'],
            "Currency_ID" => $this->info['Currency_ID'],
            "Logged_ID" => $this->logged_id,
            "created_logged_id" => $this->logged_id,
            "date_created" => $this->date_time,
            "date_time" => $this->date_time,
            "Transaction_Description" => 'project expense ',
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "main_id" => $project_expense_id,
            "main_type" => 'pex',
            "branch_id" => $this->info['branch_id'],
            "main_nb" => $project_expense_nb,
            "job_id" => $this->info['job_id'],
            "project_id" => $period_row['project_id'],
        ];

        JV::addTransaction($dat_2, $flag);
        foreach ($this->info['project_expenses'] as $item) {
            $item['project_expense_note'] = !empty($item['project_expense_note']) ? $item['project_expense_note'] : NULL;
            $item['currency_id'] = !empty($item['currency_id']) ? $item['currency_id'] : $this->info['Currency_ID'];
            $det_second_rate = !empty($item['second_currency_rate']) ? $item['second_currency_rate'] : ($item['currency_id'] == $this->info['Currency_ID'] && !empty($this->info['second_currency_rate']) ? $this->info['second_currency_rate'] : CurrencyFactory::getSecondCurrencyLastExRate($item['currency_id']));
            $item['ex_rate'] = !empty($item['ex_rate']) ? $item['ex_rate'] : CurrencyFactory::getLastExRate($item['currency_id']);

            $this->db->prepare_Stmt('INSERT INTO `tbl_project_expense_detail`
            ( `project_expense_id`, `activity_id`, `currency_id`, `current_rate`,
            `second_currency_rate`, `activity_total`, `project_expense_note`) 
            VALUES (?,?,?,?,?,?,?)', [&$project_expense_id, &$item['activity_id'], &$item['currency_id'],
                &$item['ex_rate'], &$det_second_rate, &$item['activity_total'],
                &$item['project_expense_note']]);

            $sub_id = $this->db->id();
            $desc = empty($item['project_expense_note']) ? 'project expense ' : $item['project_expense_note'];

            $dat_1 = ["JV_ID" => $jv,
                "Transaction_Reference" => $ref,
                "Transaction_Account" => $activity_accounts[$item['activity_id']],
                "Transaction_Type" => "Debit",
                "Transaction_Amount" => $item['activity_total'],
                "Transaction_Date" => $this->info['project_expense_date'],
                "Currency_ID" => $item['currency_id'],
                "Logged_ID" => $this->logged_id,
                "created_logged_id" => $this->logged_id,
                "date_created" => $this->date_time,
                "date_time" => $this->date_time,
                "Transaction_Description" => $desc,
                "Current_Ex_Rate" => $item['ex_rate'],
                "second_currency_rate" => $det_second_rate,
                "main_id" => $project_expense_id,
                "sub_id" => $sub_id,
                "main_type" => 'pex',
                "branch_id" => $this->info['branch_id'],
                "main_nb" => $project_expense_nb,
                "job_id" => $this->info['job_id'],
                "project_id" => $period_row['project_id'],
                "activity_id" => $item['activity_id'],
            ];
            JV::addTransaction($dat_1, $flag);
        }
        $dat_2 = ["JV_ID" => $jv,
            "Transaction_Reference" => $ref,
            "Transaction_Account" => $supp_acc_id,
            "Transaction_Type" => "Debit",
            "Transaction_Amount" => $this->info['paid_amount'],
            "Transaction_Date" => $this->info['project_expense_date'],
            "Currency_ID" => $this->info['Currency_ID'],
            "Logged_ID" => $this->logged_id,
            "created_logged_id" => $this->logged_id,
            "date_created" => $this->date_time,
            "date_time" => $this->date_time,
            "Transaction_Description" => 'project expense ',
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "main_id" => $project_expense_id,
            "main_type" => 'pex',
            "branch_id" => $this->info['branch_id'],
            "main_nb" => $project_expense_nb,
            "job_id" => $this->info['job_id'],
            "project_id" => $period_row['project_id'],
        ];
        JV::addTransaction($dat_2, $flag);
        $dat_2 = ["JV_ID" => $jv,
            "Transaction_Reference" => $ref,
            "Transaction_Account" => $this->info['payment_acc_id'],
            "Transaction_Type" => "Credit",
            "Transaction_Amount" => $this->info['paid_amount'],
            "Transaction_Date" => $this->info['project_expense_date'],
            "Currency_ID" => $this->info['Currency_ID'],
            "Logged_ID" => $this->logged_id,
            "created_logged_id" => $this->logged_id,
            "date_created" => $this->date_time,
            "date_time" => $this->date_time,
            "Transaction_Description" => 'project expense ',
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "main_id" => $project_expense_id,
            "main_type" => 'pex',
            "branch_id" => $this->info['branch_id'],
            "main_nb" => $project_expense_nb,
            "job_id" => $this->info['job_id'],
            "project_id" => $period_row['project_id'],
        ];
        JV::addTransaction($dat_2, $flag);
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
            return [$project_expense_id];
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    public function get_max_project_expense() {
        $query = $this->db->query('SELECT MAX(project_expense_nb) AS Entry_ID 
            FROM tbl_project_expense ');
        $row = $this->db->assoc($query);
        if (!empty($row))
            $max_nb = $row['Entry_ID'] + 1;
        else
            $max_nb = 1;
        return $max_nb;
    }

    public function delete_project_expense() {
        $query = $this->db->query('SELECT `project_expense_date` FROM `tbl_project_expense`
            WHERE project_expense_id = "' . $this->info['project_expense_id'] . '"');
        $row = $this->db->assoc($query);
        $check = CommonFunctions::check_lock_data(['type' => 'project_expense', 'start_date' => $row['project_expense_date'], 'is_actual' => 0]);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        $this->db->query('START TRANSACTION');
        $this->db->prepare_Stmt('delete from tbl_accounting_transactions where main_id = ?  and main_type= "pex" ', [&$this->info['project_expense_id']]);
        $this->db->prepare_Stmt('delete from tbl_project_expense where project_expense_id = ? ', [&$this->info['project_expense_id']]);
        $this->db->prepare_Stmt('delete from tbl_project_expense_detail where project_expense_id = ? ', [&$this->info['project_expense_id']]);
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

    public function get_project_expense($project_expense_id) {
        $data = array('main' => array(), 'details' => array());
        $query_main = $this->db->query('select *
                                        from project_expense_list
                                        where project_expense_id = "' . $project_expense_id . '"');
        $data['main'] = $this->db->assoc($query_main);
        $data['main']['template_project_expense_total'] = CommonFunctions::formatNumber($data['main']['project_expense_total']);
        $query_details = $this->db->query('select details.*,
                                                  concat_ws(" - ",activity_code,activity.activity_title) as activity_title,
                                                  concat_ws(" - ",activity_code,activity.activity_title_alias) as activity_title_alias,
                                                  currency.Currency_Symbol
                                           from tbl_project_expense_detail details
                                           inner join tbl_activity_tree activity using(activity_id)
                                           inner join tbl_common_currency currency on currency.Currency_ID = details.currency_id
                                           where details.project_expense_id = "' . $project_expense_id . '" ');
        while ($row = $this->db->assoc($query_details)) {
            $row['template_activity_total'] = CommonFunctions::formatNumber($row['activity_total']);
            $data['details'][] = $row;
        }
        return $data;
    }

    public function get_project_period_report() {
        $data = array();
        $parent_activity_arr = array();
        $query_period = $this->db->query('select * from tbl_project_period where period_id ="' . $this->info['period_id'] . '"');
        $row_period = $this->db->assoc($query_period);
        //        echo '<pre>';
        $parent_activity_query = $this->db->query('select activity_id,activity_code,activity_title,
            if(activity_title_alias="",activity_title,activity_title_alias) as activity_alias
            from tbl_activity_tree where activity_id in (' . $row_period['activity_ids'] . ')');
        while ($row = $this->db->assoc($parent_activity_query)) {
            $parent_activity_arr[$row['activity_id']] = $row;
        }
        //            print_r($parent_activity_arr);
        $activity_ids_arr = explode(",", $row_period['activity_ids']);

        foreach ($activity_ids_arr as $activity_id) {
            $parents_arr = array();
            $activity_arr = array();
            CommonFunctions::get_activity_tree($activity_id, $parents_arr, $activity_arr);
            $data[$activity_id] = ['activity_code' => $parent_activity_arr[$activity_id]['activity_code'],
                'activity_title' => $parent_activity_arr[$activity_id][!empty($this->info['show_alias']) ? 'activity_alias' : 'activity_title'],
                'activity_id' => $activity_id,
                'expense_now' => 0, 'expense_earlier' => 0, 'expense_reported' => 0,
                'total_funds' => 0, 'outstanding_amount' => 0, 'budget_plan' => 0,
                'balance' => 0, 'is_parent' => 1,
                'next_ids' => !empty($parents_arr[$activity_id]) ? implode(",", $parents_arr[$activity_id]) : ''
            ];
            //                print_r($parents_arr);
            //                die('ok');
            if (!empty($parents_arr[$activity_id])) {
                foreach ($parents_arr[$activity_id] as $parent_id) {
                    $current_amount_arr = self::get_totals_data($this->info, array_keys($activity_arr));
                    $this->build_project_period_arr($data, $parent_id, $parents_arr, $activity_arr, $current_amount_arr);
                }
            }
        }
        //                echo '<pre>';
        //                print_r($data);
        //                die('ok');
        $final_data = array();
        foreach ($data as $row) {
            if (in_array($row['activity_id'], $activity_ids_arr)) {
                //                echo 'hello';
                $next_ids = explode(",", $row['next_ids']);
                //                                print_r($next_ids);
                //                                die('ok');
                foreach ($next_ids as $ids) {
                    //                    echo $data[$ids]['total_funds'].'=====<br>';
                    $row['expense_now'] +=!empty($data[$ids]['expense_now']) ? $data[$ids]['expense_now'] : 0;
                    $row['expense_earlier'] +=!empty($data[$ids]['expense_earlier']) ? $data[$ids]['expense_earlier'] : 0;
                    $row['total_funds'] +=!empty($data[$ids]['total_funds']) ? $data[$ids]['total_funds'] : 0;
                    $row['budget_plan'] +=!empty($data[$ids]['budget_plan']) ? $data[$ids]['budget_plan'] : 0;
                }
            }
            $row['expense_reported'] = CommonFunctions::formatNumber($row['expense_now'] + $row['expense_earlier']);
            $row['outstanding_amount'] = CommonFunctions::formatNumber($row['total_funds'] - $row['expense_reported']);
            $row['balance'] = CommonFunctions::formatNumber($row['budget_plan'] - $row['total_funds']);
            $row['expense_now'] = CommonFunctions::formatNumber($row['expense_now']);
            $row['expense_earlier'] = CommonFunctions::formatNumber($row['expense_earlier']);
            $row['total_funds'] = CommonFunctions::formatNumber($row['total_funds']);
            $row['budget_plan'] = CommonFunctions::formatNumber($row['budget_plan']);
            unset($row['activity_id']);
            unset($row['is_parent']);
            unset($row['next_ids']);
            $final_data[] = array_values($row);
        }
        //                        echo '<pre>';
        //                print_r($final_data);
        //                die('ok');
        return $final_data;
    }

    public function build_project_period_arr(&$data, $activity_id, $parents_arr, $activity_arr, $current_amount_arr) {
        $project_period_total = $this->get_project_period_totals($activity_id, $parents_arr, $activity_arr, $current_amount_arr);
        if (!empty($project_period_total)) {
            $data[$activity_id] = $project_period_total[$activity_id];
        }
        if (!empty($parents_arr[$activity_id])) {
            foreach ($parents_arr[$activity_id] as $child_id) {
                $this->build_project_period_arr($data, $child_id, $parents_arr, $activity_arr, $current_amount_arr);
            }
        }
    }

    public function get_project_period_totals($activity_id, $parents_arr, $activity_arr, $current_arr) {
        $expense_now = $expense_earlier = $budget_plan = $total_funds = 0;
        $activities = array($activity_id);
        $this->get_activity_childs($activity_id, $activities, $parents_arr);
        $is_parent = false;
        if (!empty($parents_arr[$activity_id]))
            $is_parent = true;
        foreach ($activities as $current_id) {
            if (!empty($current_arr[$current_id])) {
                $expense_now += empty($current_arr[$current_id]['expense_now']) ? 0 : $current_arr[$current_id]['expense_now'];
                $expense_earlier += empty($current_arr[$current_id]['expense_earlier']) ? 0 : $current_arr[$current_id]['expense_earlier'];
                $total_funds += empty($current_arr[$current_id]['total_funds']) ? 0 : $current_arr[$current_id]['total_funds'];
                $budget_plan += empty($current_arr[$current_id]['budget_plan']) ? 0 : $current_arr[$current_id]['budget_plan'];
            }
        }
        $current_array = array();
        $current_array[$activity_id] = [ "activity_code" => $activity_arr[$activity_id]['activity_code'],
            "activity_title" => $activity_arr[$activity_id]['activity_title'],
            "activity_id" => $activity_id,
            "expense_now" => $expense_now,
            "expense_earlier" => $expense_earlier,
            "expense_reported" => 0,
            "total_funds" => $total_funds,
            "outstanding_amount" => 0,
            "budget_plan" => $budget_plan,
            "balance" => 0,
            "is_parent" => $is_parent,
            "next_ids" => ""];
        return $current_array;
    }

    public function get_totals_data($filter_arr, $activity_ids) {
        $current_array = array();
        $query_expenses_now = $this->db->query('select trans.local_amount,trans.Transaction_Amount,
                                            (trans.Transaction_Amount*trans.second_currency_rate) as second_amount,
                                            activity_id,trans.Currency_ID,period.currency_id as project_currency_id
                                            from tbl_accounting_transactions trans
                                            inner join tbl_project_expense expense
                                            on expense.project_expense_id = trans.main_id and trans.main_type = "pex"
                                            inner join tbl_project_period period on period.period_id = expense.period_id
                                            where trans.Transaction_Date between "' . $filter_arr['start_date'] . '" and  "' . $filter_arr['end_date'] . '"
                                            and expense.period_id  = "' . $filter_arr['period_id'] . '"
                                            and trans.activity_id in (' . implode(",", $activity_ids) . ')');
        while ($row = $this->db->assoc($query_expenses_now)) {
            $activity_id = $row['activity_id'];
            if (!isset($current_array[$activity_id])) {
                $current_array[$activity_id] = array(
                    "expense_now" => 0,
                    "expense_earlier" => 0,
                    "total_funds" => 0,
                    "budget_plan" => 0
                );
            }
            $amount = $this->get_project_exp_amount($row['project_currency_id'], $row['local_amount'], $row['second_amount'], $row['Transaction_Amount'], $row['Currency_ID']);
            $current_array[$activity_id]["expense_now"] += $amount;
        }
        $query_expenses_earlier = $this->db->query('select trans.local_amount,trans.Transaction_Amount,
                                            (trans.Transaction_Amount*trans.second_currency_rate) as second_amount,
                                            activity_id,trans.Currency_ID,period.currency_id as project_currency_id
                                            from tbl_accounting_transactions trans
                                            inner join tbl_project_expense expense
                                            on expense.project_expense_id = trans.main_id and trans.main_type = "pex"
                                            inner join tbl_project_period period on period.period_id = expense.period_id
                                            where trans.Transaction_Date < "' . $filter_arr['start_date'] . '" 
                                            and expense.period_id  = "' . $filter_arr['period_id'] . '"
                                            and trans.activity_id in (' . implode(",", $activity_ids) . ')');
        while ($row = $this->db->assoc($query_expenses_earlier)) {
            $activity_id = $row['activity_id'];
            if (!isset($current_array[$activity_id])) {
                $current_array[$activity_id] = array(
                    "expense_now" => 0,
                    "expense_earlier" => 0,
                    "total_funds" => 0,
                    "budget_plan" => 0
                );
            }
            $amount = $this->get_project_exp_amount($row['project_currency_id'], $row['local_amount'], $row['second_amount'], $row['Transaction_Amount'], $row['Currency_ID']);
            $current_array[$activity_id]["expense_earlier"] += $amount;
        }
        $query_total_funds = $this->db->query('select trans.local_amount,trans.Transaction_Amount,
                                            (trans.Transaction_Amount*trans.second_currency_rate) as second_amount,
                                            activity_id,trans.Currency_ID,period.currency_id as project_currency_id
                                            from tbl_accounting_transactions trans
                                            inner join tbl_project_funding funding
                                            on funding.project_funding_id = trans.main_id and trans.main_type = "pfd"
                                            inner join tbl_project_period_details det_period on det_period.detail_id = funding.detail_id
                                            inner join tbl_project_period period on period.period_id = det_period.period_id
                                            where trans.Transaction_Date between "' . $filter_arr['start_date'] . '" and  "' . $filter_arr['end_date'] . '"
                                            and funding.detail_id  = "' . $filter_arr['detail_id'] . '"
                                            and trans.activity_id in (' . implode(",", $activity_ids) . ')');
        while ($row = $this->db->assoc($query_total_funds)) {
            $activity_id = $row['activity_id'];
            if (!isset($current_array[$activity_id])) {
                $current_array[$activity_id] = array(
                    "expense_now" => 0,
                    "expense_earlier" => 0,
                    "total_funds" => 0,
                    "budget_plan" => 0
                );
            }
            $amount = $this->get_project_exp_amount($row['project_currency_id'], $row['local_amount'], $row['second_amount'], $row['Transaction_Amount'], $row['Currency_ID']);
            $current_array[$activity_id]["total_funds"] += $amount;
        }
        $query_budget_plan = $this->db->query('select total,(budget.total*budget_main.current_rate) as local_amount,
                                            (budget.total*budget_main.second_currency_rate) as second_amount,
                                            activity_id,budget_main.currency_id
                                            from tbl_activity_budget budget
                                            inner join tbl_budget budget_main on budget_main.budget_id = budget.budget_id
                                            where budget_main.period_id = "' . $filter_arr['period_id'] . '"
                                            and activity_id in (' . implode(",", $activity_ids) . ')');
        while ($row = $this->db->assoc($query_budget_plan)) {
            $activity_id = $row['activity_id'];
            if (!isset($current_array[$activity_id])) {
                $current_array[$activity_id] = array(
                    "expense_now" => 0,
                    "expense_earlier" => 0,
                    "total_funds" => 0,
                    "budget_plan" => 0
                );
            }
            $amount = $this->get_project_exp_amount($row['currency_id'], $row['local_amount'], $row['second_amount'], $row['total'], $row['currency_id']);
            $current_array[$activity_id]["budget_plan"] += $amount;
        }
        return $current_array;
    }

    public function get_project_exp_amount($project_currency, $local_amount, $second_amount, $trans_amount, $current_currency) {
        $amount = 0;
        if ($this->info['currency_id'] == 1 || (empty($this->info['currency_id']) && $project_currency == smc_session()['default_setting']['local_currency']))//local currency
            $amount = $local_amount;
        else if ($this->info['currency_id'] == 2 || (empty($this->info['currency_id']) && $project_currency == smc_session()['default_setting']['second_currency']))
            $amount = $second_amount;
        else {
            if ($current_currency == $project_currency)
                $amount = $trans_amount;
            else
                $amount = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies(smc_session()['default_setting']['local_currency'], $project_currency, $local_amount);
        }
        return $amount;
    }

    public function activity_balances_report() {
        //from_date to_date period_id currency_id
        $data = array();
        $parent_arr = array();
        $activity_arr = array();
        $fund_ids_arr = array();
        $query_act = $this->db->query('select currency_id,activity_ids from tbl_project_period 
            where period_id = "' . $this->info['period_id'] . '"');
        $row_act = $this->db->assoc($query_act);
        $activity_ids = $row_act['activity_ids'];
        $currency_id = $row_act['currency_id'];
        CommonFunctions::get_activity_tree($activity_ids, $parent_arr, $activity_arr, 1);
        //
        $fund_query = $this->db->query('select project_funding_id from tbl_project_funding f 
            join tbl_project_period_details d on d.detail_id = f.detail_id
            where d.period_id = "' . $this->info['period_id'] . '"');
        while ($row = $this->db->assoc($fund_query)) {
            $fund_ids_arr[] = $row['project_funding_id'];
        }
        //
        if (!empty($fund_ids_arr) && !empty($activity_arr)) {
            $query = $this->db->query('select t.activity_id,
            sum(Transaction_Amount*Current_Ex_Rate*if(Transaction_Type="Debit",-1,1)) as local_amount,
            sum(Transaction_Amount*second_currency_rate*if(Transaction_Type="Debit",-1,1)) as second_amount
            from tbl_accounting_transactions t 
            join tbl_activity_tree a on a.activity_id = t.activity_id
            where t.activity_id in (' . implode(',', array_keys($activity_arr)) . ')
                and t.main_id in (' . implode(',', $fund_ids_arr) . ')
                and Transaction_Date <= "' . $this->info['from_date'] . '" and t.main_type="pfd"
                group by activity_id');
            while ($row = $this->db->assoc($query)) {
                $amount = $this->get_project_exp_amount($currency_id, $row['local_amount'], $row['second_amount'], $row['local_amount'], smc_session()['default_setting']['local_currency']);
                $activity_arr[$row['activity_id']]['funding'] = $amount;
                $activity_arr[$row['activity_id']]['balance'] = $amount;
            }
        }

        $query_exp = $this->db->query('select (d.activity_total*d.current_rate) as local_amount,
            (d.activity_total*d.second_currency_rate) as second_amount,
            d.activity_total,d.currency_id,
            d.activity_id,e.project_expense_date,s.Supplier_Name,d.project_expense_note,e.check_nb
            from tbl_project_expense_detail d 
            join tbl_project_expense e on e.project_expense_id = d.project_expense_id
            join tbl_stock_suppliers s on e.supplier_id = s.Supplier_ID
            where d.activity_id in (' . implode(',', array_keys($activity_arr)) . ')
                and e.period_id = "' . $this->info['period_id'] . '"
                    and e.project_expense_date between "' . $this->info['from_date'] . '"
                        and "' . $this->info['to_date'] . '"
                ');
        while ($row = $this->db->assoc($query_exp)) {
            //            $row['activity_code'] =$activity_arr[$row['activity_id']]['activity_code'];
            //            $row['activity_title'] =$activity_arr[$row['activity_id']]['activity_title'];
            //            $row['activity_alias'] =$activity_arr[$row['activity_id']]['activity_alias'];
            //            $row['balance'] =!empty($activity_arr[$row['activity_id']]['balance']) ? $activity_arr[$row['activity_id']]['balance'] : 0;
            $amount = $this->get_project_exp_amount($currency_id, $row['local_amount'], $row['second_amount'], $row['activity_total'], $row['currency_id']);
            $row['activity_total'] = $amount;
            $activity_arr[$row['activity_id']]['expenses'][] = $row;
            if (empty($activity_arr[$row['activity_id']]['balance']))
                $activity_arr[$row['activity_id']]['balance'] = 0;
            $activity_arr[$row['activity_id']]['balance'] = $activity_arr[$row['activity_id']]['balance'] - $row['activity_total'];
        }
        $total_exp = $total_fund = $total_balance = 0;
        foreach ($activity_arr as $row) {
            $funding = CommonFunctions::formatNumber(!empty($row['funding']) ? $row['funding'] : 0);
            $balance = CommonFunctions::formatNumber(!empty($row['balance']) ? $row['balance'] : 0);
            $title = !empty($this->info['show_alias']) ? $row['activity_alias'] : $row['activity_title'];
            $total_fund+=(!empty($row['funding']) ? $row['funding'] : 0);

            if (!empty($row['expenses'])) {
                foreach ($row['expenses'] as $exp) {
                    $data[] = array($row['activity_code'], $title,
                        $funding, CommonFunctions::formatNumber($exp['activity_total']), $exp['project_expense_date'], $exp['Supplier_Name'],
                        $exp['project_expense_note'],
                        $exp['check_nb'],
                        $balance
                    );
                    $total_exp+=$exp['activity_total'];
                }
            } else {
                if (!empty($this->info['ignore_zero']) && empty($row['funding']))
                    continue;
                $data[] = array($row['activity_code'], $title,
                    $funding, 0, '', '', '', '', $balance
                );
            }
        }
        $total_balance = $total_fund - $total_exp;
        return ['total_exp' => CommonFunctions::formatNumber($total_exp),
            'total_fund' => CommonFunctions::formatNumber($total_fund),
            'total_balance' => CommonFunctions::formatNumber($total_balance),
            'data' => $data];
    }

    public function update_activity_parent() {
        $this->db->prepare_Stmt('update tbl_activity_tree set parent_id = ? 
            where activity_id = ?', [$this->info['parent_id'], $this->info['activity_id']]);
        $error = $this->db->error();
        return $error;
    }

}
