<?php if (!defined('BASEPATH')) exit('No direct script access allowed');

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Shared\Date as PSSDDate;
ini_set('max_execution_time', 0);
ini_set('memory_limit', '-1');
ini_set('sqlsrv.ClientBufferMaxKBSize','524288');
ini_set('pdo_sqlsrv.client_buffer_max_kb_size','524288');

class Home extends CI_Controller
{

    function __construct() {
        parent::__construct();
        $this->layout = 'none';
        $this->load->library('asec_giz_api_library');
        $this->load->model('ProjectReviewWithdraw');
        $this->load->model('ProjectReviewIntervention');
        $this->load->model('ProjectReviewHold');
        $this->load->model('ProjectTr');
        $this->load->model("ProjectTracking");
        $this->load->model('Direct2db_model');
    }

    function index() {
        $post = $this->input->post();
        if ($post) {
            // 1 = new project; 2 = reffered_proposal; 7 = terminated_proposal
            $data = projectStatusDashboard($post['type']);
            $ret  = ($data) ? $data : '0';
            echo json_encode($ret);exit;
        }
        $data['hidden_grid']         = (isGroup("DO")) ? 'hidden' : '';
        $data['hidden_grid_secdiv']  = (isGroup("SECDIV")) ? 'hidden' : '';
        $data['new_project']         = '-';
        $data['reffered_proposal']   = '-';
        $data['terminated_proposal'] = '-';
        $data['withdrawed_proposal'] = '-';
        render('home/home', $data, 'main-layout');
    }

    function statusProject($id)
    {
        $data = getSingleDataApi('project_tr', array(
            'project_tr_id' => $id
        ));

        if ($data['id_auth_user_refer'] || $data['id_auth_user_assign']) {
            $type = $data['id_auth_user_assign'] ? 'assign' : 'refer';
            $dataUser = getSingleDataApi('auth_user', array(
            'id_auth_user' => $data["id_auth_user_$type"]
            ));
        }
        if ($dataUser) {
            $data['status'] = "<b>Referred to:</b> $dataUser[username] ($dataUser[email])";

            if ($data['id_ref_status_approval'] && $data['is_complete'] != 1)
            {
                $status = getSingleDataApi('ref_status_approval', array(
                'id' => $data['id_ref_status_approval']
                ));
                if ($data['id_auth_user_recommend'])
                {
                    $userRecommend = getSingleDataApi('auth_user', array(
                        'id_auth_user' => $data['id_auth_user_recommend']
                    ));
                    $recommend = "and recommend this project to user $userRecommend[username] ($userRecommend[email])";
                }
                else
                {
                    // $recommend = $data['note_refer'] ? "<br>Note : $data[approval_note]" : '';
                }
                $data['status'] = "User $dataUser[username] ($dataUser[email]) $status[name] $recommend";
            }
            else if ($data['id_auth_user_assign'])
            {
                $data['status'] = "<b>Assign to user:</b> $dataUser[username] ($dataUser[email])";
            }

            if ($data['is_complete'] == 1)
            {
                $data['status'] = 'Completed';
            }
        }
        return $data['status'];
    }

    public function process($value = '')
    {
        $post = $this->input->post();
        if ($post)
        {
            $data['value'] = $post['status'];
            $where['name'] = 'using_login_form';
            $this->db->update('config', $data, $where);
            redirect('home');
        }
    }

    function imagemanager()
    {
        $post = purify($this->input->post());
        $file = $_FILES;
        if ($file)
        {
            $file        = $_FILES['img'];
            $fname       = $file['name'];
            $maxFileSize = MAX_UPLOAD_SIZE * 1024 * 1024;
            if (!is_writable(UPLOAD_DIR))
            { //kalo ga bisa upload
                $ret['error']   = 1;
                $ret['message'] = "Directory is readonly";
            }
            else if ($file['size'] >= $maxFileSize)
            {
                $ret['error']   = 1;
                $ret['message'] = "Max File size is " . MAX_UPLOAD_SIZE . "MB";
            }
            else if ($fname)
            {
                if (!file_exists(isset($folder)))
                { //kalo blm ada foldernya, bikin dulu
                    @mkdir($folder);
                }
                $upload = upload_file('img', 'temp');
                $ret['filename'] = URL_UPLOAD_CONTENT . "images/article/temp/" . $upload['file_name'];
                $ret['file']     = $upload['file_name'];
                $ret['size']     = $upload['file_size'];
                $ret['width']    = $upload['image_width'];
                $ret['height']   = $upload['image_height'];
                $ret['message']  = 'success';
            }
            echo json_encode($ret);exit;
        }

        $searchPicture = isset($post['searchPicture']) ? $post['searchPicture'] : '';
        $gets['id_name']         = 'id';
        $gets['is_single_table'] = 1;
        $gets['table_name']      = 'file_manager';
        $gets['where']           = "(user_id_create = " . id_user() . " or is_public = 1) and name LIKE '%" .
        $searchPicture . "%'";
        $gets['is_num_rows']     = 1;
        $total_records = $this->asec_giz_api_library->call('master_data/get_data', $gets);
        if (is_array($total_records))
        {
            $total_records = 0;
        }
        $per_page      = 12;
        $data['pages'] = round($total_records / $per_page);
        $data['load']  = base_url() . 'home/imagemanager';
        // $data['search'] = base_url().'home/search';
        //sanitize post value
        if (isset($post['page']))
        {
            $page_number = filter_var($post["page"], FILTER_SANITIZE_NUMBER_INT, FILTER_FLAG_STRIP_HIGH);
            if (!is_numeric($page_number))
            {
                die('Invalid page number!');
            } //incase of invalid page number

        }
        else
        {
            $page_number = 1;
        }
        //get current starting point of records
        $offset = (($page_number - 1) * $per_page);

        $gets2['id_name'] = 'id';
        $gets2['is_single_table'] = 1;
        $gets2['table_name'] = 'file_manager';
        $gets2['where'] = "(user_id_create = " . id_user() . " or is_public = 1) and name LIKE '%" . $searchPicture . "%'";
        $gets2['limit'] = $per_page;
        $gets2['offset'] = $offset;
        $data['list_data'] = $this->asec_giz_api_library->call('master_data/get_data', $gets2);
        if ($data['list_data']['message'])
        {
            $data['list_data'] = [];
        }
        render('apps/filemanager', $data, 'blank');
    }

    function imagemanager_save()
    {
        $post    = $this->input->post();
        $tmp     = URL_UPLOAD_CONTENT . 'external/' . $post['tmp'];
        $thumbs  = UPLOAD_DIR . 'small/' . $post['name'];
        $ori_tmp = UPLOAD_DIR . 'temp/' . $post['name'];
        $ori     = UPLOAD_DIR . 'large/' . $post['name'];
        copy($tmp, $thumbs);
        copy($ori_tmp, $ori);
        unset($post['tmp']);
        $post['user_id_create'] = id_user();
        insertApi('file_manager', $post);
    }

    function del_file()
    {
        $id = $this->input->post('id');
        $x = deleteData($id, array(
            'table_name' => 'file_manager',
            'id_name' => 'id'
        ));
        echo "success";exit;
    }

    function notif($return = '')
    {
        $isDo         = isGroup("DO");
        $isPCPMD      = isGroup("PCPMD");
        $isSecDiv     = isGroup("SECDIV");
        $id_user      = id_user();
        $id_struc_org = id_user('id_struc_org');
        $steps_review = ['pam', 'pac', 'sb', 'cpr', 'dp'];
        $my_task      = 0;
        $notif        = [];
        if ($isSecDiv)
        {
            # TASK. PCPMD refer proposal to SECDIV
            $where = 'is_complete = 0 AND (id_ref_status_approval IS NULL OR id_ref_status_approval = 0) AND (id_auth_user_assign is NULL OR id_auth_user_assign = 0) AND id_auth_user_refer = ' . $id_user;
            $data = $this->ProjectTr->fetchAll($where);
            foreach ($data as $key => $value)
            {
                $notif[strtotime($value['modify_date']) ]['task'] = ['title' => $value['project_tr_title'], 'desc' => 'PCPMD Refer Proposal to you', 'url' => "pre_proposals/view/$value[project_tr_id]", 'time' => $value['modify_date'], 'unread' => 'unread'];
                $my_task++;
            }
        }

        if ($isPCPMD)
        {
            # TASK. SECDIV respond Refer
            $where = 'is_complete = 0 AND (id_ref_status_approval IS NOT NULL) AND (id_auth_user_assign is NULL OR id_auth_user_assign = 0)';
            $data = $this->ProjectTr->fetchAll($where);
            if ($data)
            {
                foreach ($data as $key => $value)
                {
                    $notif[strtotime($value['modify_date']) ]['task'] = ['title' => '<b>Proposal</b>: ' . $value[project_tr_title] . '<br>
									<b>Status</b>: ' . $value['status_approval'], 'desc' => 'SECDIV respond proposal', 'url' => "pre_proposals/view/$value[project_tr_id]", 'time' => $value['modify_date'], 'unread' => ''];
                    $my_task++;
                }
            }

            # UPDATE. New proposal from PCPMD
            $data = $this->ProjectTr->fetchAll("id_auth_user_assign IS NULL");
            if ($data)
            {
                foreach ($data as $key => $value)
                {
                    $username = $value['user_id_create'] == $id_user ? 'You' : $value['created_by'];
                    $notif[strtotime($value['create_date']) ]['update'] = ['title' => $value['project_tr_title'], 'desc' => "New Proposal from $username", 'url' => "pre_proposals/view/$value[project_tr_id]", 'time' => $value['create_date'], 'unread' => ''];
                }
            }

            # UPDATE. PCPMD refer proposal to SECDIV
            $data = get_proposal_logs(['user_id_create' => $id_user, 'active_role' => 'PCPMD'], 'refer');
            if ($data)
            {
                foreach ($data as $key => $value)
                {
                    $project_tr_ids[] = $value['project_tr_id'];
                    $username = $value['user_id_create'] == $id_user ? 'You' : getUser($value['user_id_create']);
                    $secdiv_username = getUser($value['id_auth_user_refer']);
                    $notif[strtotime($value['create_date']) ]['update'] = ['title' => $value['project_tr_title'], 'desc' => "$username refer proposal to $secdiv_username", 'url' => "pre_proposals/view/$value[project_tr_id]", 'time' => $value['create_date'], 'unread' => ''];
                }
                $ids = implode($project_tr_ids, ',');
                # UPDATE. SECDIV respond refer proposal (reject)
                $data = get_proposal_logs("project_tr_id IN ($ids) AND active_role = 'SECDIV'", 'reject');
                if ($data)
                {
                    foreach ($data as $key => $value)
                    {
                        $secdiv_username = getUser($value['id_auth_user_refer']);
                        $notif[strtotime($value['create_date']) + 1]['update'] = ['title' => $value['project_tr_title'], 'desc' => "$secdiv_username reject proposal that you are referred", 'url' => "pre_proposals/view/$value[project_tr_id]", 'time' => $value['create_date'], 'unread' => ''];
                    }
                }
                # UPDATE. SECDIV respond refer proposal (approve)
                $data = get_proposal_logs("project_tr_id IN ($ids) AND active_role = 'SECDIV'", 'approve');
                if ($data)
                {
                    foreach ($data as $key => $value)
                    {
                        $secdiv_username = getUser($value['id_auth_user_refer']);
                        $notif[strtotime($value['create_date']) + 1]['update'] = ['title' => $value['project_tr_title'], 'desc' => "$secdiv_username approve proposal that you are referred", 'url' => "pre_proposals/view/$value[project_tr_id]", 'time' => $value['create_date'], 'unread' => ''];
                    }
                }
            }

            # UPDATE. PCPMD update proposal
            $data = get_proposal_logs(['user_id_create' => $id_user, 'active_role' => 'PCPMD'], 'edit');
            if ($data)
            {
                foreach ($data as $key => $value)
                {
                    $username = $value['user_id_create'] == $id_user ? 'You' : getUser($value['user_id_create']);
                    $notif[strtotime($value['create_date']) ]['update'] = ['title' => $value['project_tr_title'], 'desc' => "$username update proposal", 'url' => "pre_proposals/view/$value[project_tr_id]", 'time' => $value['create_date'], 'unread' => ''];
                }
            }

            # UPDATE. PCPMD delete proposal
            $data = get_proposal_logs(['user_id_create' => $id_user, 'active_role' => 'PCPMD'], 'delete');
            if ($data)
            {
                foreach ($data as $key => $value)
                {
                    $username = $value['user_id_create'] == $id_user ? 'You' : getUser($value['user_id_create']);
                    $notif[strtotime($value['create_date']) + 1]['update'] = ['title' => $value['project_tr_title'], 'desc' => "$username delete proposal", 'url' => "#", 'time' => $value['create_date'], 'unread' => ''];
                }
            }

            # UPDATE. PCPMD assign/confirm proposal to DO
            $data = get_proposal_logs(['user_id_create' => $id_user, 'active_role' => 'PCPMD'], 'assign');
            if ($data)
            {
                foreach ($data as $key => $value)
                {
                    $username = $value['user_id_create'] == $id_user ? 'You' : getUser($value['user_id_create']);
                    $do_username = getUser($value['id_auth_user_assign']);
                    $notif[strtotime($value['create_date']) + 1]['update'] = ['title' => $value['project_tr_title'], 'desc' => "$username assign proposal to $do_username", 'url' => "project_proposal/view/$value[project_tr_id]", 'time' => $value['create_date'], 'unread' => ''];
                }
            }

            /** HOLD */
            # UPDATE. PCPMD request to hold
            $data = get_proposal_logs("(a.user_id_create='$id_user' OR b.user_id_create='$id_user') AND active_role = 'PCPMD'", 'request hold', 1);
            if ($data)
            {
                foreach ($data as $key => $value)
                {
                    $notif[strtotime($value['create_date']) + 1]['update'] = ['title' => $value['project_tr_title'],
                    // 'desc' => "$do_username Request to Hold",
                    'desc' => "You Request to Hold", 'url' => "project_proposal/view/$value[project_tr_id]", 'time' => $value['create_date'], 'unread' => ''];
                }
            }
            # UPDATE. DO request to hold
            $data = get_proposal_logs("(a.user_id_create='$id_user' OR b.user_id_create='$id_user') AND active_role = 'DO'", 'request hold', 1);
            if ($data)
            {
                foreach ($data as $key => $value)
                {
                    $notif[strtotime($value['create_date']) + 1]['update'] = ['title' => $value['project_tr_title'],
                    // 'desc' => "$do_username Request to Hold",
                    'desc' => "DO Request to Hold", 'url' => "project_proposal/view/$value[project_tr_id]", 'time' => $value['create_date'], 'unread' => ''];
                }
            }
            # UPDATE. PCPMD approve hold
            $data = get_proposal_logs("(a.user_id_create='$id_user' OR b.user_id_create='$id_user') AND active_role = 'PCPMD'", 'approve hold', 1);
            if ($data)
            {
                foreach ($data as $key => $value)
                {
                    $notif[strtotime($value['create_date']) + 1]['update'] = ['title' => $value['project_tr_title'], 'desc' => "You Approve Hold", 'url' => "project_proposal/view/$value[project_tr_id]", 'time' => $value['create_date'], 'unread' => ''];
                }
            }
            # UPDATE. PCPMD reject hold
            $data = get_proposal_logs("(a.user_id_create='$id_user' OR b.user_id_create='$id_user') AND active_role = 'PCPMD'", 'reject hold', 1);
            if ($data)
            {
                foreach ($data as $key => $value)
                {
                    $notif[strtotime($value['create_date']) + 1]['update'] = ['title' => $value['project_tr_title'], 'desc' => "You Reject Hold", 'url' => "project_proposal/view/$value[project_tr_id]", 'time' => $value['create_date'], 'unread' => ''];
                }
            }
            # TASK. DO request to hold
            $data = $this->ProjectReviewHold->fetchAll("id_auth_user_approve is NULL AND a.project_tr_id is NOT NULL");
            foreach ($data as $key => $value)
            {
                $time = $value['modify_date'] ? $value['modify_date'] : $value['create_date'];
                $notif[strtotime($time) + 1]['task'] = ['title' => $value['project_tr_title'], 'desc' => "DO Request to Hold", 'url' => "project_proposal/view/$value[project_tr_id]", 'time' => $time, 'unread' => ''];
                $my_task++;
            }
            /** HOLD */

            /** RESUME */
            # UPDATE. PCPMD request to resume
            $data = get_proposal_logs("(a.user_id_create='$id_user' OR b.user_id_create='$id_user') AND active_role = 'PCPMD'", 'request resume', 1);
            if ($data)
            {
                foreach ($data as $key => $value)
                {
                    // $do_username = $value['id_auth_user_assign']==$id_user?'You':getUser($value['id_auth_user_assign']);
                    $notif[strtotime($value['create_date']) + 1]['update'] = ['title' => $value['project_tr_title'],
                    // 'desc' => "$do_username Request to Hold",
                    'desc' => "You Request to Resume", 'url' => "project_proposal/view/$value[project_tr_id]", 'time' => $value['create_date'], 'unread' => ''];
                }
            }
            # UPDATE. PCPMD approve resume
            $data = get_proposal_logs("(a.user_id_create='$id_user' OR b.user_id_create='$id_user') AND active_role = 'PCPMD'", 'approve resume', 1);
            if ($data)
            {
                foreach ($data as $key => $value)
                {
                    $notif[strtotime($value['create_date']) + 1]['update'] = ['title' => $value['project_tr_title'], 'desc' => "You Approve Resume", 'url' => "project_proposal/view/$value[project_tr_id]", 'time' => $value['create_date'], 'unread' => ''];
                }
            }
            # UPDATE. PCPMD reject resume
            $data = get_proposal_logs("(a.user_id_create='$id_user' OR b.user_id_create='$id_user') AND active_role = 'PCPMD'", 'reject resume', 1);
            if ($data)
            {
                foreach ($data as $key => $value)
                {
                    $notif[strtotime($value['create_date']) + 1]['update'] = ['title' => $value['project_tr_title'], 'desc' => "You Reject Resume", 'url' => "project_proposal/view/$value[project_tr_id]", 'time' => $value['create_date'], 'unread' => ''];
                }
            }
            # TASK. DO Request Resume to PCPMD
            $data = $this->ProjectReviewHold->fetchAll("status = 5");
            foreach ($data as $key => $value)
            {
                $time = $value['modify_date'] ? $value['modify_date'] : $value['create_date'];
                $notif[strtotime($time) ]['task'] = ['title' => $value['project_tr_title'], 'desc' => 'DO Request to Resume', 'url' => "project_proposal/view/$value[project_tr_id]", 'time' => $time, 'unread' => ''];
                $my_task++;
            }
            /** RESUME */

            /** WITHDRAW */
            # UPDATE. PCPMD request to withdraw
            $data = get_proposal_logs("(a.user_id_create='$id_user' OR b.user_id_create='$id_user') AND active_role = 'PCPMD'", 'request withdraw', 1);
            if ($data)
            {
                foreach ($data as $key => $value)
                {
                    $notif[strtotime($value['create_date']) + 1]['update'] = ['title' => $value['project_tr_title'], 'desc' => "You Request to Withdraw", 'url' => "project_proposal/view/$value[project_tr_id]", 'time' => $value['create_date'], 'unread' => ''];
                }
            }
            # UPDATE. PCPMD approve withdraw
            $data = get_proposal_logs("(a.user_id_create='$id_user' OR b.user_id_create='$id_user') AND active_role = 'PCPMD'", 'approve withdraw', 1);
            if ($data)
            {
                foreach ($data as $key => $value)
                {
                    $notif[strtotime($value['create_date']) + 1]['update'] = ['title' => $value['project_tr_title'], 'desc' => "You Approve Withdraw", 'url' => "project_proposal/view/$value[project_tr_id]", 'time' => $value['create_date'], 'unread' => ''];
                }
            }
            # UPDATE. PCPMD reject withdraw
            $data = get_proposal_logs("(a.user_id_create='$id_user' OR b.user_id_create='$id_user') AND active_role = 'PCPMD'", 'reject withdraw', 1);
            if ($data)
            {
                foreach ($data as $key => $value)
                {
                    $notif[strtotime($value['create_date']) + 1]['update'] = ['title' => $value['project_tr_title'], 'desc' => "You Reject Withdraw", 'url' => "project_proposal/view/$value[project_tr_id]", 'time' => $value['create_date'], 'unread' => ''];
                }
            }
            # TASK. DO Request to Withdraw
            $where = "id_auth_user_approve is NULL";
            $data = $this
                ->ProjectReviewWithdraw
                ->fetchAll($where);
            foreach ($data as $key => $value)
            {
                $time = $value['modify_date'] ? $value['modify_date'] : $value['create_date'];
                $notif[strtotime($time) ]['task'] = ['title' => $value['project_tr_title'], 'desc' => 'DO Request to Withdraw', 'url' => "project_proposal/view/$value[project_tr_id]", 'time' => $time, 'unread' => ''];
                $my_task++;
            }
            /** WITHDRAW */

            /** REVISION */
            # UPDATE. PCPMD request revision to PCPMD
            foreach ($steps_review as $k1 => $v1)
            {
                $data = get_proposal_logs("(a.user_id_create='$id_user' OR b.user_id_create='$id_user') AND active_role = 'PCPMD'", 'request revision ' . $v1, 1);
                if ($data)
                {
                    foreach ($data as $key => $value)
                    {
                        $do_username = $value['id_auth_user_assign'] == $id_user ? 'You' : getUser($value['id_auth_user_assign']);
                        $notif[strtotime($value['create_date']) + 1]['update'] = ['title' => $value['project_tr_title'], 'desc' => "$do_username request to revision in " . strtoupper($v1) . " Step", 'url' => "project_proposal/view/$value[project_tr_id]", 'time' => $value['create_date'], 'unread' => ''];
                    }
                }
            }
            # UPDATE. PCPMD approve revision
            foreach ($steps_review as $k1 => $v1)
            {
                $data = get_proposal_logs("(a.user_id_create='$id_user' OR b.user_id_create='$id_user') AND active_role = 'PCPMD'", 'approve revision ' . $v1, 1);
                if ($data)
                {
                    foreach ($data as $key => $value)
                    {
                        $notif[strtotime($value['create_date']) + 1]['update'] = ['title' => $value['project_tr_title'], 'desc' => "You Approve Revision in " . strtoupper($v1) . " Step", 'url' => "project_proposal/view/$value[project_tr_id]", 'time' => $value['create_date'], 'unread' => ''];
                    }
                }
            }
            # UPDATE. PCPMD approve complete revision
            foreach ($steps_review as $k1 => $v1)
            {
                $data = get_proposal_logs("(a.user_id_create='$id_user' OR b.user_id_create='$id_user') AND active_role = 'PCPMD'", 'complete to revise ' . $v1, 1);
                if ($data)
                {
                    foreach ($data as $key => $value)
                    {
                        $notif[strtotime($value['create_date']) + 1]['update'] = ['title' => $value['project_tr_title'], 'desc' => "You Approve Complete Revision in " . strtoupper($v1) . " Step", 'url' => "project_proposal/view/$value[project_tr_id]", 'time' => $value['create_date'], 'unread' => ''];
                    }
                }
            }
            # UPDATE. PCPMD reject complete revision
            foreach ($steps_review as $k1 => $v1)
            {
                $data = get_proposal_logs("(a.user_id_create='$id_user' OR b.user_id_create='$id_user') AND active_role = 'PCPMD'", 'reject revision ' . $v1, 1);
                if ($data)
                {
                    foreach ($data as $key => $value)
                    {
                        $notif[strtotime($value['create_date']) + 1]['update'] = ['title' => $value['project_tr_title'], 'desc' => "You Reject Revision in " . strtoupper($v1) . " Step", 'url' => "project_proposal/view/$value[project_tr_id]", 'time' => $value['create_date'], 'unread' => ''];
                    }
                }
            }
            # TASK. DO request to revision
            $where = "(a.user_id_create='$id_user' OR f.struc_org_id='$id_struc_org') AND ref_project_status_code = 'RREV'";
            $data = $this->ProjectTr->fetchAll($where);
            if ($data)
            {
                foreach ($data as $key => $value)
                {
                    $notif[strtotime($value['modify_date']) + 1]['task'] = ['title' => $value['project_tr_title'], 'desc' => "DO Request to Revise", 'url' => "project_proposal/view/$value[project_tr_id]", 'time' => $value['modify_date'], 'unread' => ''];
                    $my_task++;
                }
            }
            # TASK. DO request to finish revision
            $where = "(a.user_id_create='$id_user' OR f.struc_org_id='$id_struc_org') AND ref_project_status_code = 'RCR'";
            $data = $this->ProjectTr->fetchAll($where);
            if ($data)
            {
                foreach ($data as $key => $value)
                {
                    $notif[strtotime($value['modify_date']) + 1]['task'] = ['title' => $value['project_tr_title'], 'desc' => "DO Request to Complete Revision", 'url' => "project_proposal/view/$value[project_tr_id]", 'time' => $value['modify_date'], 'unread' => ''];
                    $my_task++;
                }
            }
            /** REVISION */

            /** INTERVENTION */
            # UPDATE. PCPMD request to intervention
            $data = get_proposal_logs("(a.user_id_create='$id_user' OR b.user_id_create='$id_user') AND active_role = 'PCPMD'", 'request intervention', 1);
            if ($data)
            {
                foreach ($data as $key => $value)
                {
                    $notif[strtotime($value['create_date']) + 1]['update'] = ['title' => $value['project_tr_title'],
                    // 'desc' => "$do_username Request to Hold",
                    'desc' => "You Request to Intervention", 'url' => "project_proposal/view/$value[project_tr_id]", 'time' => $value['create_date'], 'unread' => ''];
                }
            }
            # UPDATE. PCPMD approve intervention
            $data = get_proposal_logs("(a.user_id_create='$id_user' OR b.user_id_create='$id_user') AND active_role = 'PCPMD'", 'approve intervention', 1);
            if ($data)
            {
                foreach ($data as $key => $value)
                {
                    $notif[strtotime($value['create_date']) + 1]['update'] = ['title' => $value['project_tr_title'], 'desc' => "You Approve Intervention", 'url' => "project_proposal/view/$value[project_tr_id]", 'time' => $value['create_date'], 'unread' => ''];
                }
            }
            # UPDATE. PCPMD reject intervention
            $data = get_proposal_logs("(a.user_id_create='$id_user' OR b.user_id_create='$id_user') AND active_role = 'PCPMD'", 'reject intervention', 1);
            if ($data)
            {
                foreach ($data as $key => $value)
                {
                    $notif[strtotime($value['create_date']) + 1]['update'] = ['title' => $value['project_tr_title'], 'desc' => "You Reject Intervention", 'url' => "project_proposal/view/$value[project_tr_id]", 'time' => $value['create_date'], 'unread' => ''];
                }
            }
            /** INTERVENTION */

            /** FINISH */
            # UPDATE. DO || PCPMD Finish Step
            foreach ($steps_review as $k1 => $v1)
            {
                $data = get_proposal_logs("(a.user_id_create='$id_user' OR b.user_id_create='$id_user') AND (active_role = 'DO' OR active_role = 'PCPMD')", 'finish ' . $v1, 1);
                if ($data)
                {
                    foreach ($data as $key => $value)
                    {
                        $do_username = $value['id_auth_user_assign'] == $id_user ? 'You' : getUser($value['id_auth_user_assign']);
                        $do_username = $value['user_id_create'] == $id_user ? 'You' : getUser($value['user_id_create']);
                        $notif[strtotime($value['create_date']) + 1]['update'] = ['title' => $value['project_tr_title'], 'desc' => "$do_username Finish Step " . strtoupper($v1) , 'url' => "project_proposal/view/$value[project_tr_id]", 'time' => $value['create_date'], 'unread' => ''];
                    }
                }
            }
            /** FINISH */

            # UPDATE. Reject Proposal
            $data = $this->ProjectTr->fetchAll('is_complete = 2');
            foreach ($data as $key => $value)
            {
                $username = getUser($value['id_auth_user_reject']);
                $notif[strtotime($value['modify_date']) ]['update'] = ['title' => $value['project_tr_title'], 'desc' => "User $username Reject Proposal", 'url' => "pre_proposals/view/$value[project_tr_id]", 'time' => $value['modify_date'], 'unread' => ''];
            }
        }

        if ($isDo)
        {
            # UPDATE. PCPMD assign proposal to DO
            $data = get_proposal_logs("(id_auth_user_assign='$id_user' OR struc_org_id_assign='$id_struc_org') AND active_role = 'PCPMD'", 'assign');
            if ($data)
            {
                foreach ($data as $key => $value)
                {
                    $username = getUser($value['user_id_create']);
                    $do_username = $value['id_auth_user_assign'] == $id_user ? 'You' : getUser($value['id_auth_user_assign']);
                    $notif[strtotime($value['create_date']) + 1]['update'] = ['title' => $value['project_tr_title'], 'desc' => "$username assign proposal to $do_username", 'url' => "project_proposal/view/$value[project_tr_id]", 'time' => $value['create_date'], 'unread' => ''];
                }
            }

            /** HOLD */
            # UPDATE. DO request hold to PCPMD
            $data = get_proposal_logs("(id_auth_user_assign='$id_user' OR struc_org_id_assign='$id_struc_org') AND active_role = 'DO'", 'request hold');
            if ($data)
            {
                foreach ($data as $key => $value)
                {
                    $do_username = $value['id_auth_user_assign'] == $id_user ? 'You' : getUser($value['id_auth_user_assign']);
                    $notif[strtotime($value['create_date']) + 1]['update'] = ['title' => $value['project_tr_title'], 'desc' => "$do_username request to hold", 'url' => "project_proposal/view/$value[project_tr_id]", 'time' => $value['create_date'], 'unread' => ''];
                }
            }
            # UPDATE. PCPMD approve hold
            $data = get_proposal_logs("(id_auth_user_assign='$id_user' OR struc_org_id_assign='$id_struc_org') AND active_role = 'PCPMD'", 'approve hold');
            if ($data)
            {
                foreach ($data as $key => $value)
                {
                    $notif[strtotime($value['create_date']) + 1]['update'] = ['title' => $value['project_tr_title'], 'desc' => "PCPMD approve your request to hold", 'url' => "project_proposal/view/$value[project_tr_id]", 'time' => $value['create_date'], 'unread' => ''];
                }
            }
            # UPDATE. PCPMD reject hold
            $data = get_proposal_logs("(id_auth_user_assign='$id_user' OR struc_org_id_assign='$id_struc_org') AND active_role = 'PCPMD'", 'reject hold');
            if ($data)
            {
                foreach ($data as $key => $value)
                {
                    $notif[strtotime($value['create_date']) + 1]['update'] = ['title' => $value['project_tr_title'], 'desc' => "PCPMD reject your request to hold", 'url' => "project_proposal/view/$value[project_tr_id]", 'time' => $value['create_date'], 'unread' => ''];
                }
            }
            /** HOLD */

            /** RESUME */
            # UPDATE. DO request to resume
            $data = get_proposal_logs("(id_auth_user_assign='$id_user' OR struc_org_id_assign='$id_struc_org') AND active_role = 'DO'", 'request resume');
            if ($data)
            {
                foreach ($data as $key => $value)
                {
                    $do_username = $value['id_auth_user_assign'] == $id_user ? 'You' : getUser($value['id_auth_user_assign']);
                    $notif[strtotime($value['create_date']) + 1]['update'] = ['title' => $value['project_tr_title'], 'desc' => "$do_username Request to resume", 'url' => "project_proposal/view/$value[project_tr_id]", 'time' => $value['create_date'], 'unread' => ''];
                }
            }
            # UPDATE. PCPMD approve resume
            $data = get_proposal_logs("(id_auth_user_assign='$id_user' OR struc_org_id_assign='$id_struc_org') AND active_role = 'PCPMD'", 'approve resume');
            if ($data)
            {
                foreach ($data as $key => $value)
                {
                    $notif[strtotime($value['create_date']) + 1]['update'] = ['title' => $value['project_tr_title'], 'desc' => "PCPMD approve your request to resume", 'url' => "project_proposal/view/$value[project_tr_id]", 'time' => $value['create_date'], 'unread' => ''];
                }
            }
            # UPDATE. PCPMD reject resume
            $data = get_proposal_logs("(id_auth_user_assign='$id_user' OR struc_org_id_assign='$id_struc_org') AND active_role = 'PCPMD'", 'reject resume');
            if ($data)
            {
                foreach ($data as $key => $value)
                {
                    $notif[strtotime($value['create_date']) + 1]['update'] = ['title' => $value['project_tr_title'], 'desc' => "PCPMD reject your request to resume", 'url' => "project_proposal/view/$value[project_tr_id]", 'time' => $value['create_date'], 'unread' => ''];
                }
            }
            /** RESUME */

            /** WITHDRAW */
            # UPDATE. DO request to withdraw
            $data = get_proposal_logs("(id_auth_user_assign='$id_user' OR struc_org_id_assign='$id_struc_org') AND active_role = 'DO'", 'request withdraw');
            if ($data)
            {
                foreach ($data as $key => $value)
                {
                    $do_username = $value['id_auth_user_assign'] == $id_user ? 'You' : getUser($value['id_auth_user_assign']);
                    $notif[strtotime($value['create_date']) + 1]['update'] = ['title' => $value['project_tr_title'], 'desc' => "$do_username Request to Withdraw", 'url' => "project_proposal/view/$value[project_tr_id]", 'time' => $value['create_date'], 'unread' => ''];
                }
            }
            # UPDATE. PCPMD approve withdraw
            $data = get_proposal_logs("(id_auth_user_assign='$id_user' OR struc_org_id_assign='$id_struc_org') AND active_role = 'PCPMD'", 'approve withdraw');
            if ($data)
            {
                foreach ($data as $key => $value)
                {
                    $notif[strtotime($value['create_date']) + 1]['update'] = ['title' => $value['project_tr_title'], 'desc' => "PCPMD approve your request to withdraw", 'url' => "project_proposal/view/$value[project_tr_id]", 'time' => $value['create_date'], 'unread' => ''];
                }
            }
            # UPDATE. PCPMD reject withdraw
            $data = get_proposal_logs("(id_auth_user_assign='$id_user' OR struc_org_id_assign='$id_struc_org') AND active_role = 'PCPMD'", 'reject withdraw');
            if ($data)
            {
                foreach ($data as $key => $value)
                {
                    $notif[strtotime($value['create_date']) + 1]['update'] = ['title' => $value['project_tr_title'], 'desc' => "PCPMD reject your request to withdraw", 'url' => "project_proposal/view/$value[project_tr_id]", 'time' => $value['create_date'], 'unread' => ''];
                }
            }
            /** WITHDRAW */

            /** REVISION */
            # UPDATE. DO request to revision
            foreach ($steps_review as $k1 => $v1)
            {
                $data = get_proposal_logs("(id_auth_user_assign='$id_user' OR struc_org_id_assign='$id_struc_org') AND active_role = 'DO'", 'request revision ' . $v1);
                if ($data)
                {
                    foreach ($data as $key => $value)
                    {
                        $do_username = $value['id_auth_user_assign'] == $id_user ? 'You' : getUser($value['id_auth_user_assign']);
                        $notif[strtotime($value['create_date']) + 1]['update'] = ['title' => $value['project_tr_title'], 'desc' => "$do_username Request to Revise in " . strtoupper($v1) . " Step", 'url' => "project_proposal/view/$value[project_tr_id]", 'time' => $value['create_date'], 'unread' => ''];
                    }
                }
            }
            # UPDATE. DO request to complete revision
            foreach ($steps_review as $k1 => $v1)
            {
                $data = get_proposal_logs("(id_auth_user_assign='$id_user' OR struc_org_id_assign='$id_struc_org') AND active_role = 'DO'", 'request complete revision ' . $v1);
                if ($data)
                {
                    foreach ($data as $key => $value)
                    {
                        $do_username = $value['id_auth_user_assign'] == $id_user ? 'You' : getUser($value['id_auth_user_assign']);
                        $notif[strtotime($value['create_date']) + 1]['update'] = ['title' => $value['project_tr_title'], 'desc' => "$do_username Request to Complete Revision in " . strtoupper($v1) . " Step", 'url' => "project_proposal/view/$value[project_tr_id]", 'time' => $value['create_date'], 'unread' => ''];
                    }
                }
            }
            # UPDATE. PCPMD approve to complete revision
            foreach ($steps_review as $k1 => $v1)
            {
                $data = get_proposal_logs("(a.id_auth_user_assign='$id_user' OR a.struc_org_id_assign='$id_struc_org') AND active_role = 'PCPMD'", 'approve revision ' . $v1);
                if ($data)
                {
                    foreach ($data as $key => $value)
                    {
                        $do_username = $value['id_auth_user_assign'] == $id_user ? 'You' : getUser($value['id_auth_user_assign']);
                        $notif[strtotime($value['create_date']) + 1]['update'] = ['title' => $value['project_tr_title'], 'desc' => "PCPMD approve your request to revise in " . strtoupper($v1) . " step", 'url' => "project_proposal/view/$value[project_tr_id]", 'time' => $value['create_date'], 'unread' => ''];
                    }
                }
            }
            # UPDATE. PCPMD approve complete revision
            foreach ($steps_review as $k1 => $v1)
            {
                $data = get_proposal_logs("(a.id_auth_user_assign='$id_user' OR a.struc_org_id_assign='$id_struc_org') AND active_role = 'PCPMD'", 'complete to revise ' . $v1, 1);
                if ($data)
                {
                    foreach ($data as $key => $value)
                    {
                        $notif[strtotime($value['create_date']) + 1]['update'] = ['title' => $value['project_tr_title'], 'desc' => "PCPMD Approve Complete Revision in " . strtoupper($v1) . " Step", 'url' => "project_proposal/view/$value[project_tr_id]", 'time' => $value['create_date'], 'unread' => ''];
                    }
                }
            }

            // # UPDATE. PCPMD approve withdraw
            // $data = get_proposal_logs("(id_auth_user_assign='$id_user' OR struc_org_id_assign='$id_struc_org') AND active_role = 'PCPMD'",'approve withdraw');
            // if($data){
            // 	foreach ($data as $key => $value) {
            // 		$notif[strtotime($value['create_date'])+1]['update'] = [
            // 			'title' => $value['project_tr_title'],
            // 			'desc' => "PCPMD approve your withdraw request",
            // 			'url' => "project_proposal/view/$value[project_tr_id]",
            // 			'time' => $value['create_date'],
            // 			'unread' => ''
            // 		];
            // 	}
            // }
            // # UPDATE. PCPMD reject withdraw
            // $data = get_proposal_logs("(id_auth_user_assign='$id_user' OR struc_org_id_assign='$id_struc_org') AND active_role = 'PCPMD'",'reject withdraw');
            // if($data){
            // 	foreach ($data as $key => $value) {
            // 		$notif[strtotime($value['create_date'])+1]['update'] = [
            // 			'title' => $value['project_tr_title'],
            // 			'desc' => "PCPMD reject your withdraw request",
            // 			'url' => "project_proposal/view/$value[project_tr_id]",
            // 			'time' => $value['create_date'],
            // 			'unread' => ''
            // 		];
            // 	}
            // }

            /** REVISION */

            /** INTERVENTION */
            # UPDATE. DO request intervention to PCPMD
            $data = get_proposal_logs("(id_auth_user_assign='$id_user' OR struc_org_id_assign='$id_struc_org') AND active_role = 'DO'", 'request intervention');
            if ($data)
            {
                foreach ($data as $key => $value)
                {
                    $do_username = $value['id_auth_user_assign'] == $id_user ? 'You' : getUser($value['id_auth_user_assign']);
                    $notif[strtotime($value['create_date']) + 1]['update'] = ['title' => $value['project_tr_title'], 'desc' => "$do_username request to intervention", 'url' => "project_proposal/view/$value[project_tr_id]", 'time' => $value['create_date'], 'unread' => ''];
                }
            }
            # UPDATE. PCPMD approve intervention
            $data = get_proposal_logs("(id_auth_user_assign='$id_user' OR struc_org_id_assign='$id_struc_org') AND active_role = 'PCPMD'", 'approve intervention');
            if ($data)
            {
                foreach ($data as $key => $value)
                {
                    $notif[strtotime($value['create_date']) + 1]['update'] = ['title' => $value['project_tr_title'], 'desc' => "PCPMD approve your request to intervention", 'url' => "project_proposal/view/$value[project_tr_id]", 'time' => $value['create_date'], 'unread' => ''];
                }
            }
            # UPDATE. PCPMD reject intervention
            $data = get_proposal_logs("(id_auth_user_assign='$id_user' OR struc_org_id_assign='$id_struc_org') AND active_role = 'PCPMD'", 'reject intervention');
            if ($data)
            {
                foreach ($data as $key => $value)
                {
                    $notif[strtotime($value['create_date']) + 1]['update'] = ['title' => $value['project_tr_title'], 'desc' => "PCPMD reject your request to intervention", 'url' => "project_proposal/view/$value[project_tr_id]", 'time' => $value['create_date'], 'unread' => ''];
                }
            }
            /** INTERVENTION */

            /** FINISH */
            # UPDATE. DO || PCPMD Finish Step
            foreach ($steps_review as $k1 => $v1)
            {
                $data = get_proposal_logs("(id_auth_user_assign='$id_user' OR struc_org_id_assign='$id_struc_org') AND (active_role = 'DO' OR active_role = 'PCPMD')", 'finish ' . $v1);
                if ($data)
                {
                    foreach ($data as $key => $value)
                    {
                        $do_username = $value['id_auth_user_assign'] == $id_user ? 'You' : getUser($value['id_auth_user_assign']);
                        $do_username = $value['user_id_create'] == $id_user ? 'You' : getUser($value['user_id_create']);
                        $notif[strtotime($value['create_date']) + 1]['update'] = ['title' => $value['project_tr_title'], 'desc' => "$do_username Finish Step " . strtoupper($v1) , 'url' => "project_proposal/view/$value[project_tr_id]", 'time' => $value['create_date'], 'unread' => ''];
                    }
                }
            }
            /** FINISH */

            #3. Withdraw UPDATE(DO)
            # UPDATE. PCPMD update status to Withdraw
            $where = "ref_status_approval_id IN(1,2) and a.is_read_do is NULL";
            $data = $this
                ->ProjectReviewWithdraw
                ->fetchAll($where);
            foreach ($data as $key => $value)
            {
                $time = $value['modify_date'] ? $value['modify_date'] : $value['create_date'];
                $notif[strtotime($time) ]['update'] = ['title' => $value['project_tr_title'], 'desc' => "PCPMD $value[status] Withdraw", 'url' => "project_proposal/view/$value[project_tr_id]", 'time' => $time, 'unread' => ''];
            }

            #6. PCPMD approve / reject : hold : notif update to DO
            $where = "ref_status_approval_id IN(1,2)  and a.is_read_do is NULL AND id_auth_user_assign = $id_user";
            $data = $this->ProjectReviewHold->fetchAll($where);
            foreach ($data as $key => $value)
            {
                $time = $value['modify_date'] ? $value['modify_date'] : $value['create_date'];
                $notif[strtotime($time) ]['update'] = ['title' => $value['project_tr_title'], 'desc' => "PCPMD $value[status] Hold", 'url' => "project_proposal/view/$value[project_tr_id]", 'time' => $time, 'unread' => ''];
            }

            // #12. PCPMD Reject Resume : notif update to DO
            // $data = $this->ProjectReviewHold->fetchAll("status = 2 and a.is_read_do = -1");
            // foreach ($data as $key => $value){
            // 	$time = $value['modify_date'] ? $value['modify_date'] : $value['create_date'];
            // 	$notif[strtotime($time)]['update'] = [
            // 		'title' => $value['project_tr_title'],
            // 		'desc' => "PCPMD Reject Request Resume",
            // 		'url' => "project_proposal/view/$value[project_tr_id]",
            // 		'time' => $time,
            // 		'unread' => ''
            // 	];
            // }
            #13. PCPMD Approve Resume : notif update to DO
            $data = $this->ProjectReviewHold->fetchAll("status = 6 and a.is_read_do = 0");
            foreach ($data as $key => $value)
            {
                $time = $value['modify_date'] ? $value['modify_date'] : $value['create_date'];
                $time = $value['modify_date'] ? $value['modify_date'] : $value['create_date'];
                $notif[strtotime($time) ]['update'] = ['title' => $value['project_tr_title'], 'desc' => "PCPMD Approve Request Resume", 'url' => "project_proposal/view/$value[project_tr_id]", 'time' => $time, 'unread' => ''];
            }
        }

        if ($isPCPMD || $isDo)
        {
            #4. Intervention UPDATE(DO) & TASK(PCPMD)
            $where = $isPCPMD ? "id_auth_user_approve is NULL" : "ref_status_approval_id IN(1,2)  and a.is_read_do is NULL";
            $data = $this->ProjectReviewIntervention->fetchAll($where);
            foreach ($data as $key => $value)
            {
                $title = $value['project_tr_title'];
                $desc  = $isPCPMD ? "DO Request to Intervention" : "PCPMD $value[status] Intervention";
                $url   = "project_proposal/view/$value[project_tr_id]";
                $type  = $isDo ? 'update' : 'task'; //update utk DO (jika request di setujui / di tolak) / task utk PCPMD utk follow up request
                if ($type == 'task')
                {
                    $my_task++;
                }
                $time = $value['modify_date'] ? $value['modify_date'] : $value['create_date'];
                if ($isDo || $isPCPMD)
                {
                    $notif[strtotime($time) ][$type] = array(
                        'title' => $title,
                        'desc' => $desc,
                        'url' => $url,
                        'time' => $time,
                        'unread' => ''
                    );
                }
            }

            #10. Review Project
            $this->load->model('ProjectReviewTrans');
            $where = '';
            if ($isDo)
            {
                $where = "AND id_auth_user_assign = $id_user";
            }
            $data = $this->ProjectReviewTrans->fetchAll("project_review_trans_status_id = 1 $where");
            foreach ($data as $key => $value)
            {
                $code = $value['review_position'];
                if ((($code == 'PAM' || $code == 'SB') && $isPCPMD) || (($code == 'PAC' || $code == 'CPR' || $code == 'DP') && $isDo))
                {
                    $type = 'update';
                }
                else if (($code == 'PAM' || $code == 'SB') && $isDo || (($code == 'PAC' || $code == 'CPR' || $code == 'DP') && $isPCPMD))
                {
                    $type = 'task';
                }
                if ($type == 'task')
                {
                    $my_task++;
                }
                $title = $value['project_tr_title'];
                $desc = "Proposal Review : $code";
                $url = 'project_proposal/view/'.$value['project_tr_id'];
                $time = $value['modify_date'] ? $value['modify_date'] : $value['create_date'];
                $notif[strtotime($time) ][$type] = array(
                    'title' => $title,
                    'desc' => $desc,
                    'url' => $url,
                    'time' => $time,
                    'unread' => ''
                );
            }
        }

        //create html
        $htmla = '<div class="all-notif-content notif-cnt">';
        $htmlu = '<div class="update-notif-content notif-cnt hide">';
        $htmlt = '<div class="task-notif-content notif-cnt hide">';

        $totalUpdate = $totalTask = $totalAll = $totalNotif = 0;
        krsort($notif); //sort by newest
        if ($return)
        {
            return $notif;
        }

        foreach ($notif as $key => $value)
        {
            if ($totalUpdate < 20 || $totalTask < 20)
            {
                $val = isset($value['update']) ? $value['update'] : $value['task'];
                $bg  = isset($value['update']) ? 'blue' : 'orange';
                ++$totalAll;
                $htmla .= $this->notif_html($val['title'], $val['desc'], $val['url'], $totalAll, $bg, $val['time'], $val['unread']);
                if ($bg == 'blue')
                { // update
                    ++$totalUpdate; //total yg dihitung hanya task aja
                    $htmlu .= $this->notif_html($val['title'], $val['desc'], $val['url'], $totalUpdate, $bg, $val['time'], $val['unread']);
                }
                if ($bg == 'orange')
                { //task
                    ++$totalTask;
                    $htmlt .= $this->notif_html($val['title'], $val['desc'], $val['url'], $totalTask, $bg, $val['time'], $val['unread']);
                }
            }
        }

        $htmla .= '</div>';
        $htmlu .= '</div>';
        $htmlt .= '</div>';
        //end pcpmd hold
        //do finish review
        //pcpmd finish review
        $seeAll = '<div id="see-more-notif"><a href="' . $this->data['base_url'] . 'home/notifications">See All Notifications</a></div>';

        $html = $htmla . $htmlu . $htmlt . $seeAll;
        //total notif seharusnya nanti dibuang kalo format lama udah di ubah ke format baru
        $ret['totalAll']    = $totalAll + $totalNotif;
        //total notif seharusnya nanti dibuang kalo format lama udah di ubah ke format baru
        $ret['totalTask']   = $my_task == 0 ? '' : $my_task;
        $ret['totalUpdate'] = $totalUpdate;
        $ret['html']        = $html;
        echo json_encode($ret);
        exit;
    }

    function notif_html($title = '', $desc = '', $url = '', $n = '', $bg = 'orange', $time = '', $unread = '')
    {
        return "<div class='media $unread'>" . "<a href='" . $this->data['base_url'] . $url . "'>" . "<div class='media-left'><i class='fa media-object bg-$bg'>" . $n . "</i></div>" . "<div class='media-body'>" . "<h6 class='media-heading'>$desc</h6>" . "<div class='shorcut-text' title='$title'>$title</div>" . "<small>" . time_elapsed_string($time) . "</small>" . "</div>" . "</a>" . "</div>";
    }

    function notifications()
    {
        $post  = $this->input->post();
        $notif = $this->notif(1);
        $htmla = $htmlu = $htmlt = '';
        if ($notif)
        {
            foreach ($notif as $key => $value)
            {
                $val = $value['update'] ? $value['update'] : $value['task'];
                $bg  = $value['update'] ? 'blue' : 'orange';
                ++$totalAll;
                $htmla .= $this->notif_html($val['title'], $val['desc'], $val['url'], $totalAll, $bg, $val['time'], $val['unread']);
                if ($bg == 'blue')
                { // update
                    ++$totalUpdate; //total yg dihitung hanya task aja
                    $htmlu .= $this->notif_html($val['title'], $val['desc'], $val['url'], $totalUpdate, $bg, $val['time'], $val['unread']);
                }
                if ($bg == 'orange')
                { //task
                    ++$totalTask;
                    $htmlt .= $this->notif_html($val['title'], $val['desc'], $val['url'], $totalTask, $bg, $val['time'], $val['unread']);
                }
            }
        }
        $data['notification'] = $htmla . $htmlu . $htmlt;
        $data['page_title']   = "Your Notifications";
        render('home/notifications', $data, 'main-layout');
    }

    function detail_project($project_tr_id, $tab)
    {
        if ($tab == 'log')
        {
            $this->load->model('ProjectTrLog');
            $data['is_pcpmd']      = isGroup('PCPMD') ? '' : 'hide';
            $data['log_project']   = $this->ProjectTrLog->fetchAll(['project_tr_id' => $project_tr_id]);
            $data['project_tr_id'] = $project_tr_id;
        }
        else if ($tab == 'classifications')
        {
            $data = getSingleDataApi('project_tr', array(
                'project_tr_id' => $project_tr_id
            ));

            ###### START SINGLE/CROSS SECTOR ######
            $data['singleChecked'] = ($data['is_cross_sector'] == 1) ? '' : 'Single Sector';
            $data['crossChecked']  = ($data['is_cross_sector'] == 1) ? 'Cross Sector' : '';
            ###### END SINGLE/CROSS SECTOR ######


            ###### START RECURRING PROJECT ######
            $data['recurring'] = $data['is_recurring_project'] == 1 ? 'This proposal is recurring to project with <b>Project ID: ' . $data["project_id"] . '</b>' : 'This proposal is new project.';
            ###### END RECURRING PROJECT ######


            ###### START SECTORAL BODY ######
            $get_data = getSingleDataApi('sectoral_body_tr', array(
                'project_trx_id' => $project_tr_id,
                'is_main_sectoral_body' => 1
            ));
            $sectoral_body = getSingleDataApi('sectoral_body_mst', array(
                'sectoral_body_id' => $get_data['sectoral_body_id']
            ));
            $data['sectoral_body_data'] = (empty($sectoral_body)) ? 'Data empty' : $sectoral_body['sectoral_body_title'];
            ###### END SECTORAL BODY ######


            ###### START SECTORAL BODY WORKING GROUP ######
            $get_data = getSingleDataApi('sectoral_body_wg_mst', array(
                'sectoral_body_wg_id' => $data['project_supporting_sectoral_body_id']
            ));
            $data['sectoral_body_working_group'] = (empty($get_data)) ? 'Data empty' : $get_data['sectoral_body_wg_title'];
            ###### END SECTORAL BODY WORKING GROUP ######


            ###### START SUPPORT SECTORAL ######
            $get_data = getDataApi('sectoral_body_tr', array(
                'project_trx_id' => $project_tr_id,
                'is_main_sectoral_body' => 0
            ));
            $data['sectoral_supporting_list'] = [];
            if (!empty($get_data))
            { // data does
                foreach ($get_data as $key => $value)
                {
                    $list_sectoral_body = getSingleDataApi('sectoral_body_mst', array(
                        'sectoral_body_id' => $value['sectoral_body_id']
                    ));
                    $sectoral_body_title = isset($list_sectoral_body['sectoral_body_title'])?$list_sectoral_body['sectoral_body_title']:'';
                    $data['sectoral_supporting_list'][$key]['list_sectoral_template'] = '<tr><td>' . $sectoral_body_title . '</td></tr></div>';
                }
            }


            ###### START PILLAR ######
            $get_pillar = array(
                'sort_field' => 'is_main_pillar',
                'sort_type' => 'desc',
                'is_single_table_pillar_relation' => 1,
                'table_name' => 'pillar_tr'
            );
            $get_pillar['where'] = ['project_trx_id' => $project_tr_id];
            $get_data = $this->asec_giz_api_library->call('master_data/get_data', $get_pillar);
            $main_pillar = $other_pillar = [];
            if (!isset($get_data['message'])) { // pillar does
                foreach ($get_data as $key => $value) {
                    if ($value['is_main_pillar'] == 1) {
                        array_push($main_pillar,$value);
                    } else {
                        array_push($other_pillar,$value);
                    }
                }
            }
            $data['main_pillar']  = $main_pillar;
            $data['other_pillar'] = $other_pillar;
            ###### END PILLAR ######


            ###### START INTERVENTION ######
            $get_data = getSingleDataApi('type_intervention_mst', array(
                'type_intervention_id' => $data['type_intervention_id']
            ));
            $data['list_interventions'] = isset($get_data['type_intervention_title'])?$get_data['type_intervention_title']:'';
            ###### END INTERVENTION ######


            ###### START NATURE CORPORATION ######
            $get_data = getSingleDataApi('type_nature_corporation_mst', array(
                'type_nature_corporation_id' => $data['type_nature_corporation_id']
            ));
            $data['list_nature_corporate'] = isset($get_data['type_nature_corporation_title'])?$get_data['type_nature_corporation_title']:'';
            ###### END NATURE CORPORATION ######
        }
        else if ($tab == 'durations')
        {
            $data = getSingleDataApi('project_tr', array(
                'project_tr_id' => $project_tr_id
            ));

            $data['project_completion_date'] = date("Y-m-d", strtotime($data['project_completion_date']));
            $data['project_commancement_date'] = date("Y-m-d", strtotime($data['project_commancement_date']));

            $get_data = getSingleDataApi('type_project_durations_mst', [
                'type_project_durations_id' => $data['type_project_durations_id']
            ]);
            $data['project_duration'] = $get_data['type_project_durations_title'];
        }
        else if ($tab == 'participating')
        {
            $data = getSingleDataApi('project_tr', ['project_tr_id' => $project_tr_id]);

            $data['reason_if_none_project_participating'] = '';
            $data['country'] = [];

            ###### START PROJECT PARTICIPATING ######
            if ($data['project_participating'] == 11)
            { // OTHERS
                $data['country_name'] = 'Others';
                $data['reason_if_none_project_participating'] = $data['reason_if_none_project_participating'];
            }
            if ($data['project_participating'] == 10)
            { // CLMV
                $data['country_name'] = 'CLMV';
            }
            if ($data['project_participating'] == 9)
            { // ASEAN PLUS THREE
                $data['country_name'] = 'ASEAN Plus Three';
            }
            if ($data['project_participating'] == 7)
            { // SINGLE
                $data['country_name'] = 'Single Country';
            }
            if ($data['project_participating'] == 6)
            { // ASEAN
                $data['country_name'] = 'ASEAN';
            }

            $params['where']           = ['project_trx_id' => $project_tr_id];
            $params['table_name']      = 'affiliation_tr';
            $params['is_single_table'] = 1;
            $countries = $this->asec_giz_api_library->call('project_transaction/get_data_affiliation_trx', $params);
            if (!isset($countries['status'])) {
                $name = '';
                foreach ($countries as $key => $value) {
                    // $name .= ;
                    $country[] = $value['country_name'];
                }
                $data['additional_country'] = $country;
            }

            $extraAff['select']     = "a.*, b.country_name as country_name";
            $extraAff['table_join'] = ['country_mst b'];
            $extraAff['type_join']  = ['left'];
            $extraAff['where_join'] = ['a.country_id = b.country_id AND b.is_delete = 0'];
            $countries = getDataApi('affiliation_tr', ['project_trx_id' => $project_tr_id], $extraAff);

            $country = '';
            if ($countries) {
                $countries = getDataApi('affiliation_tr', ['project_trx_id' => $project_tr_id]);
                foreach ($countries as $key => $value)
                {
                    if (isset($value['country_name'])) {
                        $country .= ',' . $value['country_name'];
                    }
                }
            }

            // $params = array(
            //     'table' => 'affiliation_mst',
            //     'id' => 'affiliation_id',
            //     'name' => 'affiliation_name',
            //     "title" => "Select Affiliation",
            //     'selected' => $data['project_participating'],
            //     'where' => 'is_delete=0',
            //     'order' => 'create_date'
            // );
            // $data['list_affiliation'] = selectlistApi($params);
        }
        else if ($tab == 'proponent-org')
        {
            ###### PROPONENT AND CONTACT ORGANIZATION ######
            $get_data = getDataApi('proponent_org_tr', array(
                'project_trx_id' => $project_tr_id,
                'is_agency' => 0,
                'is_proponent' => 1
            ));
            $dataProp[] = default_variabel($tab);

            if (!empty($get_data))
            {
                $n = 1;
                foreach ($get_data as $key => $value)
                {
                    $params = array(
                        'table_name' => 'proponent_org_mst',
                        'proponent_org_id' => $value['proponent_org_id'],
                        'is_single_table' => 1
                    );
                    // $params['debug_query'] = 1;
                    // debug_api();
                    $proponent_org = $this->asec_giz_api_library->call('project_transaction/get_data_proponent_org', $params);
                    if (!isset($proponent_org['message'])) {
                        $country_name = getSingleDataApi('country_mst', ['country_id' => $proponent_org['country_id']]);
                        $dataProp[$key] = [
                            'no' => $n,
                            'proponent_org_title' => $proponent_org['proponent_org_title'],
                            'proponent_org_email' => $proponent_org['proponent_org_email'],
                            'proponent_org_email_alt' => $proponent_org['proponent_org_email_alt'],
                            'proponent_org_address' => $proponent_org['proponent_org_address'],
                            'proponent_name' => $proponent_org['proponent_name'],
                            'proponent_email' => $proponent_org['proponent_email'],
                            'proponent_address' => $proponent_org['proponent_address'],
                            'proponent_person_note' => $proponent_org['proponent_person_note'],
                            'country_name' => $country_name['country_name']
                        ];
                        $n++;
                    }
                }
            }
            $data['proponent_data'] = $dataProp;
        }
        else if ($tab == 'agency')
        {
            ###### PROPONENT AGENCY AND CONTACT ORGANIZATION ######
            $get_data = getDataApi('proponent_org_tr', array(
                'project_trx_id' => $project_tr_id,
                'is_agency' => 1,
                'is_proponent' => 0
            ));
            $dataAgency[] = default_variabel($tab);
            if (!empty($get_data))
            {
                $n = 1;
                foreach ($get_data as $key => $value)
                {
                    $params = array(
                        'table_name' => 'proponent_org_mst',
                        'proponent_org_id' => $value['proponent_org_id'],
                        'is_single_table' => 1
                    );
                    $proponent_agency = $this->asec_giz_api_library->call('project_transaction/get_data_proponent_org', $params);

                    if (!isset($proponent_agency['message'])) {
                        $country_name = getSingleDataApi('country_mst', ['country_id' => $proponent_agency['country_id']]);
                        $dataAgency[$key] = [
                            'no' => $n,
                            'proponent_agency_title' => $proponent_agency['proponent_org_title'],
                            'proponent_agency_email' => $proponent_agency['proponent_org_email'],
                            'proponent_agency_email_alt' => $proponent_agency['proponent_org_email_alt'],
                            'proponent_agency_address' => $proponent_agency['proponent_org_address'],
                            'proponent_agency_contact_name' => $proponent_agency['proponent_name'],
                            'proponent_agency_contact_email' => $proponent_agency['proponent_email'],
                            'proponent_agency_contact_address' => $proponent_agency['proponent_address'],
                            'proponent_agency_contact_person_note' => $proponent_agency['proponent_person_note'],
                            'proponent_agency_contact_country_name' => $country_name['country_name'],
                        ];
                        $n++;
                    }
                }
            }
            $data['agency_data'] = $dataAgency;
        }
        else if ($tab == 'attachments')
        {
            $data = [];
            ###### FILE UPLOAD ######
            $params['is_single_table'] = 1;
            $params['where']           = ['project_trx_id' => $project_tr_id];
            $params['table_name']      = 'proposal_file_upload_tr';
            $get_data = $this->asec_giz_api_library->call('project_transaction/get_data_files_tr', $params);
            if (!isset($get_data['message']))
            {
                $data = getSingleDataApi('project_tr', ['project_tr_id' => $project_tr_id]);
                foreach ($get_data as $key => $value)
                {
                    $data['upload'][$key]['file_name']     = $value['file_name'];
                    $data['upload'][$key]['category_name'] = $value['file_category_name'];
                    $data['upload'][$key]['url_download']  = 'proposal-id/' . $data["proposal_temp_id"] . "/" . $value['file_name'];
                }
            }

            ## new feature, lampiran2
            # Added 27 August 2021

            // revised
            $extra["table_join"] = ["project_review_trans_finish b","ref_project_review c"];
            $extra["where_join"] = ["b.project_review_trans_id = a.id","a.ref_project_review_id = c.id"];
            $extra["select"]     = "a.*, b.files as files, c.name as step_review";
            $data_file_revised = getDataApi("project_review_trans", "project_tr_id = '$project_tr_id' AND project_review_trans_status_id = 4", $extra);
            $files = [];
            if (isset($data_file_revised) && !empty($data_file_revised)) {
                foreach ($data_file_revised as $key => $value) {
                    $dataLampiran = [];
                    if (!empty($value['files'])) {
                        $lampiran = json_decode($value['files'],1);
                        foreach ($lampiran as $keyLampiran => $valueLampiran) {
                            $category = getSingleDataApi("files_category_mst", "files_category_mst_id = $valueLampiran[category]");
                            $dataLampiran[] = [
                                'file_name' => $valueLampiran['name'],
                                'category_name' => isset($category['files_category_name'])?$category['files_category_name']:'',
                                'url_download' => 'review/'.$project_tr_id.'/'.$value['step_review'].'/'.$valueLampiran['name']
                            ];
                        }
                    }
                    $files[$value['step_review']] = $dataLampiran;
                }
                $data["revised_file_pcpmd"] = $files;
            }

            // hold
            $data_file_hold = getSingleDataApi("project_tr_review_hold", "project_tr_id = $project_tr_id");
            $files = [];
            if (isset($data_file_hold["files"]) && !empty($data_file_hold['files'])) {
                $files = json_decode($data_file_hold["files"], true);
                foreach ($files as $key => $value) {
                    $category = getSingleDataApi("files_category_mst", "files_category_mst_id = $value[category]");
                    $files[$key]["file_name"]     = $value["name"];
                    $files[$key]["category_name"] = $category["files_category_name"];
                    $files[$key]["url_download"]  = 'review/'.$project_tr_id."/HOLD/PCPMD/".$value["name"];
                    // $files[$key]["url_download"]  = 'https://project.asean.org/proposal-doc/migrasi/review/'.$project_tr_id."/HOLD/PCPMD/".$value["name"];
                }
                $data["hold_file_pcpmd"] = $files;
            }
            $files = [];
            if (isset($data_file_hold["files_do"]) && !empty($data_file_hold['files_do'])) {
                $files = json_decode($data_file_hold["files_do"], true);
                foreach ($files as $key => $value) {
                    $category = getSingleDataApi("files_category_mst", "files_category_mst_id = $value[category]");
                    $files[$key]["file_name"]     = $value["name"];
                    $files[$key]["category_name"] = $category["files_category_name"];
                    $files[$key]["url_download"]  = 'review/'.$project_tr_id."/HOLD/DO/".$value["name"];
                    // $files[$key]["url_download"]  = 'https://project.asean.org/proposal-doc/migrasi/review/'.$project_tr_id."/HOLD/DO/".$value["name"];
                }
                $data["hold_file_do"] = $files;
            }

            // withdraw
            $data_withdraw_file = getSingleDataApi("project_review_withdraw", "project_tr_id = $project_tr_id");
            $files = [];
            if (isset($data_withdraw_file["files"]) && !empty($data_withdraw_file['files'])) {
                $files = json_decode($data_withdraw_file["files"], true);
                foreach ($files as $key => $value) {
                    $category = getSingleDataApi("files_category_mst", "files_category_mst_id = $value[category]");
                    $files[$key]["file_name"]     = $value["name"];
                    $files[$key]["category_name"] = $category["files_category_name"];
                    $files[$key]["url_download"]  = 'review/'.$project_tr_id."/WITHDRAW/PCPMD/".$value["name"];
                    // $files[$key]["url_download"]  = 'https://project.asean.org/proposal-doc/migrasi/review/'.$project_tr_id."/WITHDRAW/PCPMD/".$value["name"];
                }
                $data["withdraw_file_pcpmd"] = $files;
            }
            $files = [];
            if (isset($data_withdraw_file["files_do"]) && !empty($data_withdraw_file['files_do'])) {
                $files = json_decode($data_withdraw_file["files_do"], true);
                foreach ($files as $key => $value) {
                    $category = getSingleDataApi("files_category_mst", "files_category_mst_id = $value[category]");
                    $files[$key]["file_name"]     = $value["name"];
                    $files[$key]["category_name"] = $category["files_category_name"];
                    $files[$key]["url_download"]  = 'review/'.$project_tr_id."/WITHDRAW/DO/".$value["name"];
                    // $files[$key]["url_download"]  = 'https://project.asean.org/proposal-doc/migrasi/review/'.$project_tr_id."/WITHDRAW/DO/".$value["name"];
                }
                $data["withdraw_file_do"] = $files;
            }

            // intervention
            $data_intervention_file = getSingleDataApi("project_review_intervention", "project_tr_id = $project_tr_id");
            $files = [];
            if (isset($data_intervention_file["files"]) && !empty($data_intervention_file['files'])) {
                $files = json_decode($data_intervention_file["files"], true);
                foreach ($files as $key => $value) {
                    $category = getSingleDataApi("files_category_mst", "files_category_mst_id = $value[category]");
                    $files[$key]["file_name"]     = $value["name"];
                    $files[$key]["category_name"] = $category["files_category_name"];
                    $files[$key]["url_download"]  = 'review/'.$project_tr_id."/INTERVENTION/PCPMD/".$value["name"];
                    // $files[$key]["url_download"]  = 'https://project.asean.org/proposal-doc/migrasi/review/'.$project_tr_id."/INTERVENTION/PCPMD/".$value["name"];
                }
                $data["intervention_file_pcpmd"] = $files;
            }
            $files = [];
            if (isset($data_intervention_file["files_do"]) && !empty($data_intervention_file['files_do'])) {
                $files = json_decode($data_intervention_file["files_do"], true);
                foreach ($files as $key => $value) {
                    $category = getSingleDataApi("files_category_mst", "files_category_mst_id = $value[category]");
                    $files[$key]["file_name"]     = $value["name"];
                    $files[$key]["category_name"] = $category["files_category_name"];
                    $files[$key]["url_download"]  = 'review/'.$project_tr_id."/INTERVENTION/DO/".$value["name"];
                    // $files[$key]["url_download"]  = 'https://project.asean.org/proposal-doc/migrasi/review/'.$project_tr_id."/INTERVENTION/DO/".$value["name"];
                }
                $data["intervention_file_do"] = $files;
            }
        }
        else if ($tab == 'objectives-result')
        {
            ###### PROJECT OBJECTIVES ######
            $get_data  = getDataApi('project_objectives_trx', ['project_trx_id' => $project_tr_id]);
            $dataObj[] = default_variabel('objectives');
            if (!empty($get_data))
            { // data does
                $n = 1;
                foreach ($get_data as $key => $value)
                {
                    $dataObj[$key]['no']         = $n;
                    $dataObj[$key]['objectives'] = $value['project_objectives_trx_name'];
                    $n++;
                }
            }
            $data['data_objectives'] = $dataObj;

            ###### PROJECT RESULT ######
            $get_data  = getDataApi('project_result_trx', ['project_trx_id' => $project_tr_id]);
            $dataResult[] = default_variabel('result');
            if (!empty($get_data))
            { // data does
                $n = 1;
                foreach ($get_data as $key => $value)
                {
                    $dataResult[$key]['no'] = $n;
                    $dataResult[$key]['result'] = $value['project_result_trx_name'];
                    $n++;
                }
            }
            $data['data_result'] = $dataResult;
        }
        else if ($tab == 'funding-source')
        {
            ###### FUNDING SOURCE ######
            $data = getSingleDataApi('project_tr', ['project_tr_id' => $project_tr_id]);
            setlocale(LC_MONETARY, 'en_US');
            $propose_total_funding = $data['propose_total_funding'];
            $data['propose_total_funding'] = money_format_custom('%.2n', $data['propose_total_funding']);
            $get_fs['is_single_table'] = 1;
            $get_fs['where']           = ['project_trx_id' => $project_tr_id];
            $get_fs['where_join']      = ['a.funding_source_id = c.funding_source_id'];
            $get_fs['select']          = ('a.*, c.*');
            $get_fs['table_name']      = 'project_funding_source_tr';
            $get_fs['table_join']      = ['funding_source_mst c'];
            $get_data = $this->asec_giz_api_library->call('master_data/get_data', $get_fs);
            $dataFs[] = default_variabel($tab);
            if (!isset($get_data['message']))
            { // data does
                $n = 1;
                foreach ($get_data as $key => $value)
                {
                    $dataFs[$key]['no']     = $n;
                    $dataFs[$key]['name']   = $value['funding_source_name'];
                    $dataFs[$key]['budget'] = money_format_custom('%.2n', $value['proposal_budget_project']);
                    $n++;
                }
            }
            $data['funding'] = $dataFs;
            //////////////////////////////////////////////////////////////////////////////////////////
        }
        render2("apps/detail_project/$tab", $data, 'blank');
    }

    function historyrevisiondetail2($id1, $id2 = '', $dataOnly = '')
    {
        $this->load->model('ProjectFundingSourceTrLogModel', 'modFundingSorceLog');
        $this->load->model('SectoralBodyTrLogModel', 'modSectoralBodyLog');
        $this->load->model('PillarTrLogModel', 'modPillarTrLog');
        $this->load->model('OrganizationLogModel', 'modOrganizationTrLog');
        $this->load->model('AttachmentsLogModel', 'modAttachmentsTrLog');
        $this->load->model('ObjectivesTrModel', 'modObjectivesTrLog');
        $this->load->model('ResultTrModel', 'modResultTrLog');
        $compare1 = getProjectData($id1, 1);
        $compare1['project_commancement_date'] = date('Y-m-d', strtotime($compare1['project_commancement_date']));
        $compare1['project_completion_date']   = date('Y-m-d', strtotime($compare1['project_completion_date']));
        if (!$id2)
        {
            $extra['sort_field'] = 'id';
            $extra['sort_type'] = 'desc';
            // debug_api();
            $this->db->where('log_description', 'Revision');
            $this->db->where('project_tr_id', $compare1['project_tr_id']);
            $this->db->where('id <', $id1);
            $this->db->order_by('id','desc');
            $dt = $this->db->get('project_tr_log')->result_array();
            // $dt = getSingleDataApi('project_tr_log', "log_description = 'Revision' AND project_tr_id = $compare1[project_tr_id] AND id = '$id1'", $extra);
            $id2 = $dt ? $dt['id'] : 0;
        }

        $compare2 = $id2 ? getProjectData($id2, 1) : array();
        if (!empty($compare2)) {
            $compare2['project_commancement_date'] = !empty($compare2) ? date('Y-m-d', strtotime($compare2['project_commancement_date'])) : '';
            $compare2['project_completion_date'] = !empty($compare2) ? date('Y-m-d', strtotime($compare2['project_completion_date'])) : '';
        }

        $data['list_log'] = getDataApi('project_tr_log', "project_tr_id = $compare1[project_tr_id]");
        $data['id1']      = $id1;
        $data['id2']      = $id2;
        $data['compare1'] = $compare1;
        $data['compare2'] = $compare2;
        $data['dataOnly'] = $dataOnly;

        ###### SECTORAL BODY / MAIN COMMITTEE ######
        $sectoral_body_main = $this->modSectoralBodyLog->getSectoralBody("$id1,$id2");
        foreach ($sectoral_body_main as $key => $value)
        {
            $sectoral_body_main1 = $this->modSectoralBodyLog->fetchRow("a.sectoral_body_id = $value[sectoral_body_id] AND project_tr_log_id = $id1 AND is_main_sectoral_body = 1");
            $sectoral_body_main2 = $this->modSectoralBodyLog->fetchRow("a.sectoral_body_id = $value[sectoral_body_id] AND project_tr_log_id = $id2 AND is_main_sectoral_body = 1");
            if ((!$sectoral_body_main1 || !$sectoral_body_main2) && $id2 != 0)
            {
                $sectoral_body_main[$key]['diff'] = 'bg-red-lighter';
            } else {
                $sectoral_body_main[$key]['diff'] = '';
            }
            $sectoral_body_main[$key]['source1'] = $sectoral_body_main1;
            $sectoral_body_main[$key]['source2'] = $sectoral_body_main2;
        }
        $data['sectoral_body_main'] = $sectoral_body_main;

        ###### SECTORAL BODY / Supporting COMMITTEE ######
        $sectoral_body_support = $this
            ->modSectoralBodyLog
            ->getSectoralBody("$id1,$id2");
        foreach ($sectoral_body_support as $key => $value)
        {
            $sectoral_body_support1 = $this->modSectoralBodyLog->fetchRow("a.sectoral_body_id = $value[sectoral_body_id] AND project_tr_log_id = $id1 AND is_main_sectoral_body != 1");
            $sectoral_body_support2 = $this->modSectoralBodyLog->fetchRow("a.sectoral_body_id = $value[sectoral_body_id] AND project_tr_log_id = $id2 AND is_main_sectoral_body != 1");
            if ((!$sectoral_body_support1 || !$sectoral_body_support2) && $id2 != 0)
            {
                $sectoral_body_support[$key]['diff'] = 'bg-red-lighter';
            } else {
                $sectoral_body_support[$key]['diff'] = '';
            }
            $sectoral_body_support[$key]['source1'] = $sectoral_body_support1;
            $sectoral_body_support[$key]['source2'] = $sectoral_body_support2;
        }
        $data['sectoral_body_support'] = $sectoral_body_support;

        ###### SECTORAL BODY WG / SUB COMMITTEE ######
        $get_sectoral_body_wg_where = array(
            'sectoral_body_wg_id' => $compare1['project_supporting_sectoral_body_id']
        );
        $data['sub_committee1'] = getSingleDataApi('sectoral_body_wg_mst', $get_sectoral_body_wg_where) ['sectoral_body_wg_title'];
        $get_sectoral_body_wg_where = array(
            'sectoral_body_wg_id' => isset($compare2['project_supporting_sectoral_body_id']) ? $compare2['project_supporting_sectoral_body_id'] : 0
        );
        $sub_committee2 = getSingleDataApi('sectoral_body_wg_mst', $get_sectoral_body_wg_where);
        $data['sub_committee2'] = !empty($sub_committee2) ? $sub_committee2['sectoral_body_wg_title']:'';

        ###### Main PILLAR ######
        $type_of_main_pillars = $this->modPillarTrLog->getPillar("$id1,$id2");
        foreach ($type_of_main_pillars as $key => $value)
        {
            $type_of_main_pillars1 = $this->modPillarTrLog->fetchRow("a.type_of_pillars_id = $value[type_of_pillars_id] AND project_tr_log_id = $id1 AND is_main_pillar = 1");
            $type_of_main_pillars2 = $this->modPillarTrLog->fetchRow("a.type_of_pillars_id = $value[type_of_pillars_id] AND project_tr_log_id = $id2 AND is_main_pillar = 1");
            if ((!$type_of_main_pillars1 || !$type_of_main_pillars2) && $id2 != 0)
            {
                $type_of_main_pillars[$key]['diff'] = 'bg-red-lighter';
            } else {
                $type_of_main_pillars[$key]['diff'] = '';
            }

            $type_of_main_pillars[$key]['source1'] = $type_of_main_pillars1;
            $type_of_main_pillars[$key]['source2'] = $type_of_main_pillars2;
        }
        $data['type_of_main_pillars'] = $type_of_main_pillars;

        ###### Other PILLAR ######
        $type_of_other_pillars = $this->modPillarTrLog->getPillar("$id1,$id2");
        foreach ($type_of_other_pillars as $key => $value)
        {
            $type_of_other_pillars1 = $this->modPillarTrLog->fetchRow("a.type_of_pillars_id = $value[type_of_pillars_id] AND project_tr_log_id = $id1 AND is_main_pillar != 1");
            $type_of_other_pillars2 = $this->modPillarTrLog->fetchRow("a.type_of_pillars_id = $value[type_of_pillars_id] AND project_tr_log_id = $id2 AND is_main_pillar != 1");
            if ((!$type_of_other_pillars1 || !$type_of_other_pillars2) && $id2 != 0)
            {
                $type_of_other_pillars[$key]['diff'] = 'bg-red-lighter';
            } else {
                $type_of_other_pillars[$key]['diff'] = '';
            }

            $type_of_other_pillars[$key]['source1'] = $type_of_other_pillars1;
            $type_of_other_pillars[$key]['source2'] = $type_of_other_pillars2;
        }
        $data['type_of_other_pillars'] = $type_of_other_pillars;


        ###### Type Of Intervention ######
        $get_intervention_where = array(
            'type_intervention_id' => $compare1['type_intervention_id']
        );
        $data['intervention1'] = getSingleDataApi('type_intervention_mst', $get_intervention_where) ['type_intervention_title'];
        $get_intervention_where = array(
            'type_intervention_id' => isset($compare2['type_intervention_id']) ? $compare2['type_intervention_id'] : 0
        );
        $intervention2 = getSingleDataApi('type_intervention_mst', $get_intervention_where);
        $data['intervention2'] = !empty($intervention2) ? $intervention2['type_intervention_title'] : '';


        ###### Type of Cooperation ######
        $get_cooperation_where = array(
            'type_nature_corporation_id' => $compare1['type_nature_corporation_id']
        );
        $data['cooperation1'] = getSingleDataApi('type_nature_corporation_mst', $get_cooperation_where) ['type_nature_corporation_title'];
        $get_cooperation_where = array(
            'type_nature_corporation_id' => isset($compare2['type_nature_corporation_id']) ? $compare2['type_nature_corporation_id'] : 0
        );
        $cooperation2 = getSingleDataApi('type_nature_corporation_mst', $get_cooperation_where);
        $data['cooperation2'] = !empty($cooperation2) ? $cooperation2['type_nature_corporation_title']:'';

        ###### Recurring ######
        if ($compare1['is_recurring_project'] == 1)
        {
            $data['recurring1'] = 'This proposal is recurring to project with <b>Project ID: ' . $compare1["project_id"] . '</b>';
        }
        else
        {
            $data['recurring1'] = 'This proposal is new project.';
        }
        if (isset($compare2['is_recurring_project']) && $compare2['is_recurring_project'] == 1)
        {
            $data['recurring2'] = 'This proposal is recurring to project with <b>Project ID: ' . $compare2["project_id"] . '</b>';
        }
        else
        {
            $data['recurring2'] = 'This proposal is new project.';
        }

        ###### Duration ######
        $get_project_duration_where = array(
            'type_project_durations_id' => $compare1['type_project_durations_id']
        );
        $data['project_duration1'] = getSingleDataApi('type_project_durations_mst', $get_project_duration_where) ['type_project_durations_title'];
        $get_project_duration_where = array(
            'type_project_durations_id' => isset($compare2['type_project_durations_id']) ? $compare2['type_project_durations_id'] : 0
        );
        $project_duration2 = getSingleDataApi('type_project_durations_mst', $get_project_duration_where);
        $data['project_duration2'] = !empty($project_duration2) ? $project_duration2['type_project_durations_title']:'';


        ###### Proponent Organization ######
        $organization = $this->modOrganizationTrLog->getOrganization("$id1,$id2");
        foreach ($organization as $key => $value)
        {
            $organization1 = $this->modOrganizationTrLog->fetchRow("a.proponent_org_id = $value[proponent_org_id] AND project_tr_log_id = $id1 AND a.is_proponent = 1");
            $organization2 = $this->modOrganizationTrLog->fetchRow("a.proponent_org_id = $value[proponent_org_id] AND project_tr_log_id = $id2 AND a.is_proponent = 1");
            if ((!$organization1 || !$organization2) && $id2 != 0)
            {
                $organization[$key]['diff'] = 'bg-red-lighter';
            }

            $organization[$key]['source1'] = $organization1;
            $organization[$key]['source2'] = $organization2;
        }
        $data['organization'] = $organization;


        ###### Attachments ######
        $attachments = $this->modAttachmentsTrLog->getAttachments("$id1,$id2");
        foreach ($attachments as $key => $value)
        {
            $attachments1 = $this->modAttachmentsTrLog->fetchRow("a.proposal_file_upload_id = $value[proposal_file_upload_id] AND project_tr_log_id = $id1");
            $attachments2 = $this->modAttachmentsTrLog->fetchRow("a.proposal_file_upload_id = $value[proposal_file_upload_id] AND project_tr_log_id = $id2");
            if ((!$attachments1 || !$attachments2) && $id2 != 0)
            {
                $attachments[$key]['diff'] = 'bg-red-lighter';
            }
            else
            {
                $attachments[$key]['diff'] = compare(isset($attachments1['files_category_name'])?$attachments1['files_category_name']:'', isset($attachments2['files_category_name'])?$attachments1['files_category_name']:'');
            }

            $attachments[$key]['source1'] = $attachments1;
            $attachments[$key]['source2'] = $attachments2;
        }
        $data['attachments'] = $attachments;

        ###### Objectives & Results ######
        $objectives = $this->modObjectivesTrLog->getObjectives("$id1,$id2");
        foreach ($objectives as $key => $value)
        {
            $objectives1 = $this->modObjectivesTrLog->fetchRow("a.id = $value[id] AND project_tr_log_id = $id1");
            $objectives2 = $this->modObjectivesTrLog->fetchRow("a.id = $value[id] AND project_tr_log_id = $id2");
            if ((!$objectives1 || !$objectives2) && $id2 != 0)
            {
                $objectives[$key]['diff'] = 'bg-red-lighter';
            } else {
                $objectives[$key]['diff'] = '';
            }
            $objectives[$key]['source1'] = $objectives1;
            $objectives[$key]['source2'] = $objectives2;
        }
        $data['objectives'] = $objectives;

        $result = $this->modResultTrLog->getResult("$id1,$id2");
        foreach ($result as $key => $value)
        {
            $result1 = $this->modResultTrLog->fetchRow("a.id = $value[id] AND project_tr_log_id = $id1");
            $result2 = $this->modResultTrLog->fetchRow("a.id = $value[id] AND project_tr_log_id = $id2");
            if ((!$result1 || !$result2) && $id2 != 0)
            {
                $result[$key]['diff'] = 'bg-red-lighter';
            } else {
                $result[$key]['diff'] = '';
            }
            $result[$key]['source1'] = $result1;
            $result[$key]['source2'] = $result2;
        }
        $data['result'] = $result;

        ###### FUNDING ######
        $funding = $this
            ->modFundingSorceLog
            ->getFundingSource("$id1,$id2");
        foreach ($funding as $key => $value)
        {
            $funding1 = $this->modFundingSorceLog->fetchRow("a.funding_source_id = $value[funding_source_id] AND project_tr_log_id = $id1");
            $funding2 = $this->modFundingSorceLog->fetchRow("a.funding_source_id = $value[funding_source_id] AND project_tr_log_id = $id2");
            if ((!$funding1 || !$funding2) && $id2 != 0)
            {
                $funding[$key]['diff'] = 'bg-red-lighter';
            }
            else
            {
                $funding[$key]['diff'] = compare(!empty($funding1) ? $funding1['proposal_budget_project']:'', !empty($funding2)?$funding2['proposal_budget_project']:'');
            }
            $funding[$key]['source1'] = $funding1;
            $funding[$key]['source2'] = $funding2;
        }
        // debugvar($funding);
        $data['funding'] = $funding;
        render2('apps/project_proposal/detaillog', $data, 'blank');
    }

    function get_revision_file($id = '')
    {
        if ($id)
        {
            $this->load->model('ProjectReviewTransFinishModel', 'finishModel');
            $activeReview = getReviewPosition($id);
            $id_active_review = isset($activeReview['id'])?$activeReview['id']:0;
            $dataFinish   = $this->finishModel->fetchRow("project_review_trans_id = $id_active_review");
            $files = [];
            if(isset($dataFinish['files'])){
                $files = json_decode($dataFinish['files'], true);
                foreach ($files as $key => $value)
                {
                    $category = getSingleDataApi("files_category_mst", "files_category_mst_id = $value[category]");
                    $files[$key]['categoryName'] = $category['files_category_name'];
                    $files[$key]['fileUrl']      = base_url() . 'proposal-doc/review/' . $activeReview['project_tr_id'] . '/' . $activeReview['review'] . '/' . $value['name'];
                }
            }
            $data['files'] = $files;
            render2('apps/detail_project/list-files', $data, 'blank');
        }
    }


    // SCRIPT MIGRASI PROPOSAL
    function get_scdc()
    {
        $excel_path = APPPATH.'../migration.xlsx';
        $spreadsheet = IOFactory::load($excel_path);
        $worksheet = $spreadsheet->getSheet(3);
        $highestRow = $worksheet->getHighestRow();
        $rows = [];
        $data = [];
        for ($row = 4; $row <= $highestRow; ++$row) {
            $col = 1;
            $cell = $worksheet->getCellByColumnAndRow($col, $row);
            $maxCol = $col + 100;
            for ( ; $col <= $maxCol; ++$col) {
                $value = $worksheet->getCellByColumnAndRow($col, $row)->getValue();
                $coordinates = $worksheet->getCellByColumnAndRow($col, $row)->getCoordinate();
                $year = null;
                switch ($col) {
                    case 2:
                        $rows[$row]['is_folder_exists'] = $value;
                        break;
                    case 3:
                        $rows[$row]['proposal_id'] = trim($value);
                        break;
                    case 4:
                        $rows[$row]['title'] = $value;
                        break;
                    case 5:
                        $rows[$row]['status'] = $value;
                        break;
                    case 7:
                        $rows[$row]['description'] = $value;
                        break;
                    case 8:
                        if (is_numeric($value)) {
                            $rows[$row]['back_date'] = date('Y-m-d', PSSDDate::excelToTimestamp($value));
                        } else {
                            $rows[$row]['back_date'] = '-';
                        }
                        break;
                    case 9:
                        $rows[$row]['is_endorsed'] = $value;
                        break;
                    case 10:
                        $rows[$row]['is_recurring'] = $value;
                        break;
                    case 11:
                        $rows[$row]['previous_project_id'] = $value;
                        break;
                    case 12:
                        $rows[$row]['project_classification'] = $value;
                        break;
                    case 13:
                        if (is_numeric($value)) {
                            $rows[$row]['main_committee'] = $value;
                        } else {
                            $rows[$row]['main_committee'] = '-';
                        }
                        break;
                    case 14:
                        if (is_numeric($value)) {
                            $rows[$row]['sub_committee'] = $value;
                        } else {
                            $rows[$row]['sub_committee'] = '-';
                        }
                        break;
                    case 15:
                        $rows[$row]['supporting_committee'] = $value;
                        break;
                    case 16:
                        if (is_numeric($value)) {
                            $rows[$row]['pillar'] = $value;
                        } else {
                            $rows[$row]['pillar'] = '-';
                        }
                        break;
                    case 17:
                        $rows[$row]['intervention_type'] = $value;
                        break;
                    case 18:
                        $rows[$row]['cooperation_type'] = $value;
                        break;
                    case 19:
                        if (is_numeric($value)) {
                            $rows[$row]['proposed_commencement_date'] = date('Y-m-d', PSSDDate::excelToTimestamp($value));
                        } else {
                            $rows[$row]['proposed_commencement_date'] = '-';
                        }
                        break;
                    case 20:
                        if (is_numeric($value)) {
                            $rows[$row]['proposed_completion_date'] = date('Y-m-d', PSSDDate::excelToTimestamp($value));
                        } else {
                            $rows[$row]['proposed_completion_date'] = '-';
                        }
                        break;
                    case 21:
                        $rows[$row]['participants'] = $value;
                        break;
                    case 22:
                        $rows[$row]['more_countries_of_participants'] = $value;
                        break;
                    case 23:
                        $rows[$row]['reason_if_not_all_asean'] = $value;
                        break;
                    case 24:
                        // if (is_numeric($value)) {
                            $rows[$row]['proponent'] = $value;
                        // } else {
                        //     $rows[$row]['proponent'] = '-';
                        // }
                        break;
                    case 25:
                        if (is_numeric($value)) {
                            $rows[$row]['agency'] = $value;
                        } else {
                            $rows[$row]['agency'] = '-';
                        }
                        break;
                    case 26:
                        if (is_numeric($value)) {
                            $rows[$row]['project_objective'] = $value;
                        } else {
                            $rows[$row]['project_objective'] = '-';
                        }
                        break;
                    case 27:
                        if (is_numeric($value)) {
                            $rows[$row]['project_output'] = $value;
                        } else {
                            $rows[$row]['project_output'] = '-';
                        }
                        break;
                    case 28:
                        if (is_numeric($value)) {
                            $rows[$row]['funding_source'] = $value;
                        } else {
                            $rows[$row]['funding_source'] = '-';
                        }
                        break;
                    case 29:
                        $rows[$row]['funding_source_budget'] = $value;
                        break;
                    case 30:
                        $rows[$row]['file_attachment'] = $value;
                        break;
                    case 31:
                        if (is_numeric($value)) {
                            $rows[$row]['proposal_submission_date'] = date('Y-m-d', PSSDDate::excelToTimestamp($value));
                        } else {
                            $rows[$row]['proposal_submission_date'] = '-';
                        }
                        break;
                    case 32:
                        $rows[$row]['old_step_review'] = $value;
                        break;
                    case 33:
                        $rows[$row]['step_review_generated_by_system'] = $value;
                        break;
                    case 35:
                        $rows[$row]['creator'] = $value;
                        break;
                    case 36:
                        $rows[$row]['confirm_to'] = $value;
                        break;
                    case 37:
                        $rows[$row]['review_step']['pam']['score'] = $value;
                        break;
                    case 38:
                        $rows[$row]['review_step']['pam']['attachments'] = $value;
                        break;
                    case 65:
                        if (is_numeric($value)) {
                            $rows[$row]['review_step']['pam']['approval_date'] = date('Y-m-d', PSSDDate::excelToTimestamp($value));
                        } else {
                            $rows[$row]['review_step']['pam']['approval_date'] = '-';
                        }
                        break;
                    case 39:
                        $rows[$row]['review_step']['pac']['attachments'] = $value;
                        break;
                    case 40:
                        $rows[$row]['review_step']['pac']['remarks'] = $value;
                        break;
                    case 66:
                        if (is_numeric($value)) {
                            $rows[$row]['review_step']['pac']['approval_date'] = date('Y-m-d', PSSDDate::excelToTimestamp($value));
                        } else {
                            $rows[$row]['review_step']['pac']['approval_date'] = '-';
                        }
                        break;
                    case 41:
                        $rows[$row]['review_step']['sb']['is_get_endorse'] = $value;
                        break;
                    case 42:
                        $rows[$row]['review_step']['sb']['attachments'] = $value;
                        break;
                        case 43:
                        $rows[$row]['review_step']['sb']['remark'] = $value;
                        break;
                    case 67:
                        if (is_numeric($value)) {
                            $rows[$row]['review_step']['sb']['approval_date'] = date('Y-m-d', PSSDDate::excelToTimestamp($value));
                        } else {
                            $rows[$row]['review_step']['sb']['approval_date'] = '-';
                        }
                        break;
                    case 44:
                        $rows[$row]['review_step']['cpr']['is_get_endorse'] = $value;
                        break;
                    case 45:
                        $rows[$row]['review_step']['cpr']['attachments'] = $value;
                        break;
                    case 46:
                        $rows[$row]['review_step']['cpr']['remark'] = $value;
                        break;
                    case 68:
                        if (is_numeric($value)) {
                            $rows[$row]['review_step']['cpr']['approval_date'] = date('Y-m-d', PSSDDate::excelToTimestamp($value));
                        } else {
                            $rows[$row]['review_step']['cpr']['approval_date'] = '-';
                        }
                        break;
                    case 47:
                        $rows[$row]['review_step']['dp']['is_get_endorse'] = $value;
                        break;
                    case 48:
                        $rows[$row]['review_step']['dp']['attachments'] = $value;
                        break;
                    case 49:
                        $rows[$row]['review_step']['dp']['remark'] = $value;
                        break;
                    case 69:
                        if (is_numeric($value)) {
                            $rows[$row]['review_step']['dp']['approval_date'] = date('Y-m-d', PSSDDate::excelToTimestamp($value));
                        } else {
                            $rows[$row]['review_step']['dp']['approval_date'] = '-';
                        }
                        break;
                    case 50:
                        $rows[$row]['project_tracking']['funding_disbursement']['attachments'] = $value;
                        break;
                    case 51:
                        if (is_numeric($value)) {
                            $rows[$row]['project_tracking']['funding_disbursement']['update_date'] = date('Y-m-d', PSSDDate::excelToTimestamp($value));
                        } else {
                            $rows[$row]['project_tracking']['funding_disbursement']['update_date'] = '-';
                        }
                        break;
                    case 52:
                        $rows[$row]['project_tracking']['funding_disbursement']['remarks'] = $value;
                        break;
                    case 53:
                        $rows[$row]['project_tracking']['project_implementation']['attachments'] = $value;
                        break;
                    case 54:
                        if (is_numeric($value)) {
                            $rows[$row]['project_tracking']['project_implementation']['start_date'] = date('Y-m-d', PSSDDate::excelToTimestamp($value));
                        } else {
                            $rows[$row]['project_tracking']['project_implementation']['start_date'] = '-';
                        }
                        break;
                    case 55:
                        if (is_numeric($value)) {
                            $rows[$row]['project_tracking']['project_implementation']['end_date'] = date('Y-m-d', PSSDDate::excelToTimestamp($value));
                        } else {
                            $rows[$row]['project_tracking']['project_implementation']['end_date'] = '-';
                        }
                        break;
                    case 56:
                        if (is_numeric($value)) {
                            $rows[$row]['project_tracking']['project_implementation']['update_date'] = date('Y-m-d', PSSDDate::excelToTimestamp($value));
                        } else {
                            $rows[$row]['project_tracking']['project_implementation']['update_date'] = '-';
                        }
                        break;
                    case 57:
                        $rows[$row]['project_tracking']['project_implementation']['remarks'] = $value;
                        break;
                    case 58:
                        $rows[$row]['project_tracking']['completion_report_submission']['attachments'] = $value;
                        break;
                    case 59:
                        if (is_numeric($value)) {
                            $rows[$row]['project_tracking']['completion_report_submission']['update_date'] = date('Y-m-d', PSSDDate::excelToTimestamp($value));
                        } else {
                            $rows[$row]['project_tracking']['completion_report_submission']['update_date'] = '-';
                        }
                        break;
                    case 60:
                        $rows[$row]['project_tracking']['completion_report_submission']['remarks'] = $value;
                        break;
                    case 61:
                        $rows[$row]['project_tracking']['project_closing']['attachments'] = $value;
                        break;
                    case 62:
                        if (is_numeric($value)) {
                            $rows[$row]['project_tracking']['project_closing']['update_date'] = date('Y-m-d', PSSDDate::excelToTimestamp($value));
                        } else {
                            $rows[$row]['project_tracking']['project_closing']['update_date'] = '-';
                        }
                        break;
                    case 63:
                        $rows[$row]['project_tracking']['project_closing']['remarks'] = $value;
                        break;
                    case 75:
                        $rows[$row]['is_programme'] = ($value == 'Yes') ? 1 : 0;
                        break;
                    case 76:
                        $rows[$row]['position'] = 'BX'.$row;
                        $rows[$row]['status_proposal'] = $value;
                        break;
                }
            }

            foreach ($rows as $key => $value) {
                // if ($value['proposal_id'] == 'ASC/ASC/09/0011/IAI') {
                    $data[$key] = $value;
                // }
            }
        }

        return array_values($data);
    }

    function set_flag($position, $value = '') {
        // $excel_path = APPPATH.'../migration.xlsx';
        // $excel2 = IOFactory::load($excel_path); // Empty Sheet
        // $excel2->setActiveSheetIndex(2);
        // $excel2->getActiveSheet()->setCellValue($position, $value);
        // $objWriter = IOFactory::createWriter($excel2, 'Xlsx');
        // $objWriter->save('migration.xlsx');
    }

    function data_migration()
    {
        echo "<pre>";
        $time_start_total = microtime(true);
        $time_start = microtime(true);
        $scdc = $this->get_scdc();
        $time_end = microtime(true);
        echo "Read excel in: ".($time_end - $time_start)."s. <br>";

        $data = $this->db->select('project_tr_id, proposal_id, project_tr_title')->get_where('project_tr', array('is_delete' => 0))->result_array();
        $project_tr_ids = [];
        $project_tr = [];
        echo "=======================================<br>";
        echo "Total current proposal:".count($data)."<br>";
        echo "Total migration target:".count($scdc)."<br>";
        echo "=======================================<br>";
        $duplicated_proposal_ids = array(
            'N/A',
            'n/a',
            'IND/SME/10/001/REG',
            'ASC/ASC/09/0011/IAI',
            'FIN/STU/14/001/JAF',
            'TRC/SCR/11/004/REG',
        );
        foreach ($data as $key => $value) {
            $project_tr_ids[$value['proposal_id']] = $value['project_tr_id'];
            $project_tr[$value['project_tr_id']] = $value;
        }

        $result = [];
        $tracking_steps = array(
            1 => 'Funding Disbursement',
            2 => 'Project Implementation',
            3 => 'Completion Report Submission',
            4 => 'Project Closing',
        );

        $review_steps = array(
            1 => 'PAM',
            2 => 'PAC',
            3 => 'SB',
            4 => 'CPR',
            5 => 'DP',
            6 => 'Post CPR',
        );

        foreach ($scdc as $key => $value) {
            $time_start = microtime(true);
            $tr_id = null;
            $existing_proposal = null;
            if ($value['status_proposal'] == '') {
                // Create Project
                if (array_search($value['proposal_id'], $duplicated_proposal_ids) !== false) {
                    // Validasi tambahan untuk ID proposal duplikat
                    // cek juga judul proposalnya
                    $check_existing_proposal = $this->db->select('project_tr_id,project_tr_title,proposal_id')
                    ->order_by('project_tr_id', 'desc')
                    ->get_where('project_tr', array(
                        'proposal_id' => $value['proposal_id'],
                        'project_tr_title' => $value['title'],
                        'is_delete' => 0,
                    ))->row_array();
                    if ($check_existing_proposal) {
                        $existing_proposal = $check_existing_proposal['project_tr_id'];
                    }
                } else {
                    $existing_proposal = isset($project_tr_ids[$value['proposal_id']]) ? $project_tr_ids[$value['proposal_id']] : null;
                }

                if ($existing_proposal) {
                    $tr_id = $existing_proposal;
                    $this->update_proposal($tr_id, $value);
                } else {
                    $tr_id = $this->create_proposal($value);
                }
                if ($tr_id) {
                    $upload_others = $this->insert_attachments($tr_id);
                    if ($value['status_proposal'] == '') {
                        // Assign proposal
                        $do_user_id = explode('| ', $value['confirm_to'])[0];
                        $this->assign($tr_id, $do_user_id);
                    }

                    // Generate step review
                    $review_step = $this->get_step_review($tr_id);
                    if ($review_step) {
                        $current_proposal_status = $value['status'];
                        // Proposal review
                        switch($current_proposal_status) {
                            case 'Completed':
                                $result[$value['proposal_id']]['id']              = $tr_id;
                                $result[$value['proposal_id']]['proposal_id']     = $value['proposal_id'];
                                $result[$value['proposal_id']]['status']          = true;
                                $result[$value['proposal_id']]['proposal_status'] = $current_proposal_status;
                                $result[$value['proposal_id']]['other_file_uploaded'] = $upload_others;
                                foreach ($review_step as $k_step => $step) {
                                    $review_result = $this->finish($tr_id, $value['review_step'][strtolower($step)]['approval_date']);
                                    $result[$value['proposal_id']]['review_steps'][$k_step]['step']          = $step;
                                    $result[$value['proposal_id']]['review_steps'][$k_step]['file_uploaded'] = $review_result['total_files'];
                                }
                                foreach ($tracking_steps as $k_step => $step) {
                                    $step_code = str_replace(' ', '_', strtolower($step));
                                    $tracking_result = $this->project_tracking($tr_id, $k_step, $current_proposal_status, $value['project_tracking'][$step_code]['update_date'], $value['project_tracking'][$step_code]['remarks']);
                                    $result[$value['proposal_id']]['tracking_steps'][$k_step]['step']          = $step;
                                    $result[$value['proposal_id']]['tracking_steps'][$k_step]['file_uploaded'] = $tracking_result['total_files'];
                                }

                                $this->set_flag($value['position'], 'Berhasil');
                                break;
                            case 'Implemented':
                                $result[$value['proposal_id']]['id']              = $tr_id;
                                $result[$value['proposal_id']]['proposal_id']     = $value['proposal_id'];
                                $result[$value['proposal_id']]['status']          = true;
                                $result[$value['proposal_id']]['proposal_status'] = $current_proposal_status;
                                $result[$value['proposal_id']]['other_file_uploaded'] = $upload_others;
                                foreach ($review_step as $k_step => $step) {
                                    $review_result = $this->finish($tr_id, $value['review_step'][strtolower($step)]['approval_date']);
                                    $result[$value['proposal_id']]['review_steps'][$k_step]['step']          = $step;
                                    $result[$value['proposal_id']]['review_steps'][$k_step]['file_uploaded'] = $review_result['total_files'];
                                }

                                foreach ($tracking_steps as $k_step => $step) {
                                   $tracking_result = $this->project_tracking($tr_id, $k_step, $current_proposal_status);
                                    $result[$value['proposal_id']]['tracking_steps'][$k_step]['step']          = $step;
                                    $result[$value['proposal_id']]['tracking_steps'][$k_step]['file_uploaded'] = $tracking_result['total_files'];

                                    // Hanya jalankan sampai step ke 2
                                    if ($k_step == 2) {
                                        break;
                                    }
                                }

                                $this->set_flag($value['position'], 'Berhasil');
                                break;
                            case 'Ongoing':
                                $result[$value['proposal_id']]['id']              = $tr_id;
                                $result[$value['proposal_id']]['proposal_id']     = $value['proposal_id'];
                                $result[$value['proposal_id']]['status']          = true;
                                $result[$value['proposal_id']]['proposal_status'] = $current_proposal_status;
                                $result[$value['proposal_id']]['other_file_uploaded'] = $upload_others;
                                foreach ($review_step as $k_step => $step) {
                                    $review_result = $this->finish($tr_id, $value['review_step'][strtolower($step)]['approval_date']);
                                    $result[$value['proposal_id']]['review_steps'][$k_step]['step']          = $step;
                                    $result[$value['proposal_id']]['review_steps'][$k_step]['file_uploaded'] = $review_result['total_files'];
                                }

                                foreach ($tracking_steps as $k_step => $step) {
                                   $tracking_result = $this->project_tracking($tr_id, $k_step, $current_proposal_status);
                                    $result[$value['proposal_id']]['tracking_steps'][$k_step]['step']          = $step;
                                    $result[$value['proposal_id']]['tracking_steps'][$k_step]['file_uploaded'] = $tracking_result['total_files'];

                                    // Hanya jalankan step ke 1
                                    if ($k_step == 1) {
                                        break;
                                    }
                                }

                                $this->set_flag($value['position'], 'Berhasil');
                                break;
                            case 'Pending ASEC':
                                $result[$value['proposal_id']]['id']              = $tr_id;
                                $result[$value['proposal_id']]['proposal_id']     = $value['proposal_id'];
                                $result[$value['proposal_id']]['status']          = true;
                                $result[$value['proposal_id']]['proposal_status'] = $current_proposal_status;
                                $result[$value['proposal_id']]['other_file_uploaded'] = $upload_others;

                                $max_step = 5;
                                if (in_array('DP', $review_step)) {
                                    $max_step = 5;
                                } else if (in_array('CPR', $review_step)) {
                                    $max_step = 4;
                                } else if (in_array('PAC', $review_step)) {
                                    $max_step = 2;
                                }
                                foreach ($review_step as $k_step => $step) {
                                    if (array_search($step, $review_steps) < $max_step) {
                                        // Jika bukan step terakhir, finish
                                        $review_result = $this->finish($tr_id, $value['review_step'][strtolower($step)]['approval_date']);
                                        $result[$value['proposal_id']]['review_steps'][$k_step]['step']          = $step;
                                        $result[$value['proposal_id']]['review_steps'][$k_step]['process']       = 'finish';
                                        $result[$value['proposal_id']]['review_steps'][$k_step]['file_uploaded'] = $review_result['total_files'];
                                    } else {
                                        // Stop di step PAC
                                        $result[$value['proposal_id']]['review_steps'][$k_step]['step']          = $step;
                                        $result[$value['proposal_id']]['review_steps'][$k_step]['process']       = '-';
                                        $result[$value['proposal_id']]['review_steps'][$k_step]['file_uploaded'] = $review_result['total_files'];
                                        break;
                                    }
                                }

                                $this->set_flag($value['position'], 'Berhasil');
                                break;
                            case 'Pending CPR':
                                $result[$value['proposal_id']]['id']              = $tr_id;
                                $result[$value['proposal_id']]['proposal_id']     = $value['proposal_id'];
                                $result[$value['proposal_id']]['status']          = true;
                                $result[$value['proposal_id']]['proposal_status'] = $current_proposal_status;
                                $result[$value['proposal_id']]['other_file_uploaded'] = $upload_others;
                                foreach ($review_step as $k_step => $step) {
                                    if (array_search($step, $review_steps) < 4) {
                                        // Jika bukan step terakhir, finish
                                        $review_result = $this->finish($tr_id, $value['review_step'][strtolower($step)]['approval_date']);
                                        $result[$value['proposal_id']]['review_steps'][$k_step]['step']          = $step;
                                        $result[$value['proposal_id']]['review_steps'][$k_step]['process']       = 'finish';
                                        $result[$value['proposal_id']]['review_steps'][$k_step]['file_uploaded'] = $review_result['total_files'];
                                    } else {
                                        $result[$value['proposal_id']]['review_steps'][$k_step]['step']          = $step;
                                        $result[$value['proposal_id']]['review_steps'][$k_step]['process']       = '-';
                                        $result[$value['proposal_id']]['review_steps'][$k_step]['file_uploaded'] = 0;
                                        break;
                                    }
                                }
                                $this->set_flag($value['position'], 'Berhasil');
                                break;
                            case 'Pending DP':
                                $result[$value['proposal_id']]['id']              = $tr_id;
                                $result[$value['proposal_id']]['proposal_id']     = $value['proposal_id'];
                                $result[$value['proposal_id']]['status']          = true;
                                $result[$value['proposal_id']]['proposal_status'] = $current_proposal_status;
                                $result[$value['proposal_id']]['other_file_uploaded'] = $upload_others;
                                // print_r('<br>');
                                foreach ($review_step as $k_step => $step) {
                                    if (array_search($step, $review_steps) < 5) {
                                        // Jika bukan step terakhir, finish
                                        $review_result = $this->finish($tr_id, $value['review_step'][strtolower($step)]['approval_date']);
                                        $result[$value['proposal_id']]['review_steps'][$k_step]['step']          = $step;
                                        $result[$value['proposal_id']]['review_steps'][$k_step]['process']       = 'finish';
                                        $result[$value['proposal_id']]['review_steps'][$k_step]['file_uploaded'] = $review_result['total_files'];
                                    } else {
                                        // Stop di step DP
                                        $result[$value['proposal_id']]['review_steps'][$k_step]['step']          = $step;
                                        $result[$value['proposal_id']]['review_steps'][$k_step]['process']       = '-';
                                        $result[$value['proposal_id']]['review_steps'][$k_step]['file_uploaded'] = $review_result['total_files'];
                                        break;
                                    }
                                }
                                $this->set_flag($value['position'], 'Berhasil');
                                break;
                            case 'Pending Sponsoring Body':
                                $result[$value['proposal_id']]['id']              = $tr_id;
                                $result[$value['proposal_id']]['proposal_id']     = $value['proposal_id'];
                                $result[$value['proposal_id']]['status']          = true;
                                $result[$value['proposal_id']]['proposal_status'] = $current_proposal_status;
                                $result[$value['proposal_id']]['other_file_uploaded'] = $upload_others;
                                foreach ($review_step as $k_step => $step) {
                                    if (array_search($step, $review_steps) < 3) {
                                        // Jika bukan step terakhir, finish
                                        $review_result = $this->finish($tr_id, $value['review_step'][strtolower($step)]['approval_date']);
                                        $result[$value['proposal_id']]['review_steps'][$k_step]['step']          = $step;
                                        $result[$value['proposal_id']]['review_steps'][$k_step]['process']       = 'finish';
                                        $result[$value['proposal_id']]['review_steps'][$k_step]['file_uploaded'] = $review_result['total_files'];
                                    } else {
                                        // Hold di step SB
                                        $result[$value['proposal_id']]['review_steps'][$k_step]['step']          = $step;
                                        $result[$value['proposal_id']]['review_steps'][$k_step]['process']       = '-';
                                        $result[$value['proposal_id']]['review_steps'][$k_step]['file_uploaded'] = $review_result['total_files'];
                                        break;
                                    }
                                }

                                $this->set_flag($value['position'], 'Berhasil');
                                break;
                        }
                    }
                }

            } else {
                $result[$value['proposal_id']]['proposal_id'] = $value['proposal_id'];
                $result[$value['proposal_id']]['status']      = false;
                $result[$value['proposal_id']]['description'] = 'Already migrated';

                // if ($value['is_folder_exists'] == 'No') {
                //     $this->set_flag($value['position'], 'Folder does not exist');
                // }
            }
            $time_end = microtime(true);
            echo "$key - Time process for $value[proposal_id] ($tr_id) : ".($time_end - $time_start)."s. <br>";
        }

        echo "=======================================<br>";
        $time_end_total = microtime(true);
        echo "Migrated successfully in: ".($time_end_total - $time_start_total)."s. <br>";
        echo "=======================================<br>";
        echo "Details: <br>";

        echo "<pre>";
        print_r($result);exit;
    }

    function get_scdc_update()
    {
        $excel_path = APPPATH.'../migration.xlsx';
        $spreadsheet = IOFactory::load($excel_path);
        $worksheet = $spreadsheet->getSheet(0);
        $highestRow = $worksheet->getHighestRow();
        $rows = [];
        $data = [];
        for ($row = 6; $row <= $highestRow; ++$row) {
            $col = 1;
            $cell = $worksheet->getCellByColumnAndRow($col, $row);
            $maxCol = $col + 100;
            for ( ; $col <= $maxCol; ++$col) {
                $value = $worksheet->getCellByColumnAndRow($col, $row)->getValue();
                $coordinates = $worksheet->getCellByColumnAndRow($col, $row)->getCoordinate();
                $year = null;
                switch ($col) {
                    case 3:
                        $rows[$row]['proposal_id'] = trim($value);
                        break;
                    case 4:
                        $rows[$row]['title'] = $value;
                        break;
                    case 7:
                        $rows[$row]['description'] = $value;
                        break;
                    case 31:
                        if (is_numeric($value)) {
                            $rows[$row]['proposal_submission_date'] = date('Y-m-d', PSSDDate::excelToTimestamp($value));
                        } else {
                            $rows[$row]['proposal_submission_date'] = NULL;
                        }
                        break;
                    case 40:
                        $rows[$row]['remarks_pac'] = $value;
                        break;
                    case 43:
                        $rows[$row]['remarks_sb'] = $value;
                        break;
                    case 46:
                        $rows[$row]['remarks_cpr'] = $value;
                        break;
                    case 49:
                        $rows[$row]['remarks_dp'] = $value;
                        break;
                    case 52:
                        $rows[$row]['remarks_fd'] = $value;
                        break;
                    case 65:
                        if (is_numeric($value)) {
                            $rows[$row]['approve_date_pam'] = date('Y-m-d', PSSDDate::excelToTimestamp($value));
                        } else {
                            $rows[$row]['approve_date_pam'] = NULL;
                        }
                        break;
                    case 66:
                        if (is_numeric($value)) {
                            $rows[$row]['approve_date_pac'] = date('Y-m-d', PSSDDate::excelToTimestamp($value));
                        } else {
                            $rows[$row]['approve_date_pac'] = NULL;
                        }
                        break;
                    case 67:
                        if (is_numeric($value)) {
                            $rows[$row]['approve_date_sb'] = date('Y-m-d', PSSDDate::excelToTimestamp($value));
                        } else {
                            $rows[$row]['approve_date_sb'] = NULL;
                        }
                        break;
                    case 68:
                        if (is_numeric($value)) {
                            $rows[$row]['approve_date_cpr'] = date('Y-m-d', PSSDDate::excelToTimestamp($value));
                        } else {
                            $rows[$row]['approve_date_cpr'] = NULL;
                        }
                        break;
                    case 69:
                        if (is_numeric($value)) {
                            $rows[$row]['approve_date_dp'] = date('Y-m-d', PSSDDate::excelToTimestamp($value));
                        } else {
                            $rows[$row]['approve_date_dp'] = NULL;
                        }
                        break;
                    case 70:
                        if (is_numeric($value)) {
                            $rows[$row]['update_date_fd'] = date('Y-m-d', PSSDDate::excelToTimestamp($value));
                        } else {
                            $rows[$row]['update_date_fd'] = NULL;
                        }
                        break;
                    case 71:
                        if (is_numeric($value)) {
                            $rows[$row]['start_date_pi'] = date('Y-m-d', PSSDDate::excelToTimestamp($value));
                        } else {
                            $rows[$row]['start_date_pi'] = NULL;
                        }
                        break;
                    case 72:
                        if (is_numeric($value)) {
                            $rows[$row]['end_date_pi'] = date('Y-m-d', PSSDDate::excelToTimestamp($value));
                        } else {
                            $rows[$row]['end_date_pi'] = NULL;
                        }
                        break;
                    case 73:
                        if (is_numeric($value)) {
                            $rows[$row]['update_date_crs'] = date('Y-m-d', PSSDDate::excelToTimestamp($value));
                        } else {
                            $rows[$row]['update_date_crs'] = NULL;
                        }
                        break;
                    case 74:
                        if (is_numeric($value)) {
                            $rows[$row]['update_date_pc'] = date('Y-m-d', PSSDDate::excelToTimestamp($value));
                        } else {
                            $rows[$row]['update_date_pc'] = NULL;
                        }
                        break;
                    case 75:
                        $rows[$row]['position'] = 'BW'.$row;
                        $rows[$row]['status_proposal'] = $value;
                        break;
                }
            }

            foreach ($rows as $key => $value) {
                $date = date('Y-m-d', strtotime($value['proposal_submission_date']));
                $filter_date = date('Y', strtotime($value['proposal_submission_date']));
                if ($filter_date == '2020') { // yang status proposalnya new
                    // if ($value['proposal_id'] == 'SCD/HEA/18/010/REG') { // yang status proposalnya new
                    if ($value['status_proposal'] == '') {
                        $data[$key] = $value;
                    }
                    // }
                }
            }
        }

        return array_values($data);
    }

    function data_update($type = 'old')
    {
        $scdc = $this->get_scdc_update($type);
        echo "<pre>";
        // $data = $this->db->get('project_tr')->result_array();
        // $project_tr_ids = [];
        // $project_tr = [];
        // foreach ($data as $key => $value) {
        //     $project_tr_ids[$value['project_tr_id']] = $value['proposal_id'];
        //     $project_tr[$value['project_tr_id']] = $value;
        // }

        $result = [];
        $tracking_steps = array(
            1 => 'Funding Disbursement',
            2 => 'Project Implementation',
            3 => 'Completion Report Submission',
            4 => 'Project Closing',
        );

        $review_steps = array(
            1 => 'PAM',
            2 => 'PAC',
            3 => 'SB',
            4 => 'CPR',
            5 => 'DP',
        );
        foreach ($scdc as $key => $value) {
                // Create Project
                $extra['sort']       = "DESC";
                $extra['field_name'] = "project_tr_id";
                $is_exists = getSingleDataApi('project_tr', array(
                    'proposal_id' => $value['proposal_id']
                ), $extra);
                // if ($is_exists AND $type=="new") {
                if ($is_exists) {
                    $tr_id = $is_exists['project_tr_id'];
                }
                if ($tr_id) {
                    $result[$value['proposal_id']]['id']              = $tr_id;
                    $result[$value['proposal_id']]['proposal_id']     = $value['proposal_id'];
                    $result[$value['proposal_id']]['status']          = true;
                    // $review_step = $this->get_step_review($tr_id);
                    // foreach ($review_steps as $k_step => $step) {
                        // if ($step=='PAM') {
                            if ($value['approve_date_pam']!=NULL) {
                                $data_update = array(
                                            'ref_project_review_id' => 1,
                                            'approve_date'          => $value['approve_date_pam']
                                        );
                                $review_result = $this->update_project_tr($tr_id, $data_update);
                                $result[$value['proposal_id']]['review_steps'][$key]['step'] = 'PAM';
                                $this->set_flag($value['position'], 'Berhasil PAM');
                            }
                        // } else if ($step=='PAC') {
                            if ($value['approve_date_pac']!=NULL) {
                                $data_update = array(
                                            'ref_project_review_id' => 2,
                                            'remarks'               => $value['remarks_pac'],
                                            'approve_date'          => $value['approve_date_pac']
                                        );
                                $review_result = $this->update_project_tr($tr_id, $data_update);
                                $result[$value['proposal_id']]['review_steps'][$key]['step'] = 'PAC';
                                $this->set_flag($value['position'], 'Berhasil PAC');
                            }
                        // } else if ($step=='SB') {
                            if ($value['approve_date_sb']!=NULL) {
                                $data_update = array(
                                            'ref_project_review_id' => 3,
                                            'remarks'               => $value['remarks_sb'],
                                            'approve_date'          => $value['approve_date_sb']
                                        );
                                $review_result = $this->update_project_tr($tr_id, $data_update);
                                $result[$value['proposal_id']]['review_steps'][$key]['step'] = 'SB';
                                $this->set_flag($value['position'], 'Berhasil SB');
                            }
                        // } else if ($step=='CPR') {
                            if ($value['approve_date_cpr']!=NULL) {
                                $data_update = array(
                                            'ref_project_review_id' => 4,
                                            'remarks'               => $value['remarks_cpr'],
                                            'approve_date'          => $value['approve_date_cpr']
                                        );
                                $review_result = $this->update_project_tr($tr_id, $data_update);
                                $result[$value['proposal_id']]['review_steps'][$key]['step'] = 'CPR';
                                $this->set_flag($value['position'], 'Berhasil CPR');
                            }
                        // } else if ($step=='DP') {
                            if ($value['approve_date_dp']!=NULL) {
                                $data_update = array(
                                            'ref_project_review_id' => 5,
                                            'remarks'               => $value['remarks_dp'],
                                            'approve_date'          => $value['approve_date_dp']
                                        );
                                $review_result = $this->update_project_tr($tr_id, $data_update);
                                $result[$value['proposal_id']]['review_steps'][$key]['step'] = 'DP';
                                $this->set_flag($value['position'], 'Berhasil DP');
                            }
                        // }
                    // }
                    // foreach ($tracking_steps as $k_step => $step) {
                        // if ($k_step==1) {
                            if ($value['update_date_fd']!=NULL) {
                                $data_update = array(
                                            'ref_tracking_type_id' => 1,
                                            'update_date'          => $value['update_date_fd']
                                        );
                                $tracking_result = $this->update_tracking($tr_id, $data_update);
                                $result[$value['proposal_id']]['tracking_steps'][$key]['step'] = 'Fund Disbursement';
                                $this->set_flag($value['position'], 'Berhasil 1');
                            }
                        // } else if ($k_step==2) {
                            if ($value['start_date_pi']!=NULL || $value['end_date_pi']!=NULL) {
                                $data_update = array(
                                            'ref_tracking_type_id'  => 2,
                                            'start_date'            => $value['start_date_pi'],
                                            'end_date'              => $value['end_date_pi']
                                        );
                                $tracking_result = $this->update_tracking($tr_id, $data_update);
                                $result[$value['proposal_id']]['tracking_steps'][$key]['step'] = 'Project Implementation';
                                $this->set_flag($value['position'], 'Berhasil 2');
                            }
                        // } else if ($k_step==3) {
                            if ($value['update_date_crs']!=NULL) {
                                $data_update = array(
                                            'ref_tracking_type_id'  => 3,
                                            'update_date'            => $value['update_date_crs']
                                        );
                                $tracking_result = $this->update_tracking($tr_id, $data_update);
                                $result[$value['proposal_id']]['tracking_steps'][$key]['step'] = 'Completion Report Submission';
                                $this->set_flag($value['position'], 'Berhasil 3');
                            }
                        // } else if ($k_step==4) {
                            if ($value['update_date_pc']!=NULL) {
                                $data_update = array(
                                            'ref_tracking_type_id'  => 4,
                                            'update_date'            => $value['update_date_pc']
                                        );
                                $tracking_result = $this->update_tracking($tr_id, $data_update);
                                $result[$value['proposal_id']]['tracking_steps'][$key]['step'] = 'Project Closing';
                                $this->set_flag($value['position'], 'Berhasil 4');
                            }
                        // }
                    // }
                }
        }

        echo "<pre>";
        print_r($result);exit;
    }

    function update_project_tr($id,$data=array())
    {
        $project_review_trans = getSingleDataApi('project_review_trans', array(
            'project_tr_id'         => $id,
            'ref_project_review_id' => $data["ref_project_review_id"]
        ));

        $save["approve_date"] = $data["approve_date"];
        if (!empty($project_review_trans)) {
            $save["user_id_modify"] = 11;
            updateApi('project_review_trans', isset($project_review_trans['id'])?$project_review_trans['id']:'', $save);
        }
    }

    function update_tracking($projectTrId,$data=array())
    {
        $refTrackingTypeId = $data['ref_tracking_type_id'];
        $tracking = $this->ProjectTracking->fetchRow(
            "a.project_tr_id = $projectTrId AND ref_tracking_type_id = $refTrackingTypeId"
        );
        if (!empty($tracking)) {
            if ($refTrackingTypeId==1 || $refTrackingTypeId==3 || $refTrackingTypeId==4) {
                $save_tracking["update_date"] = $data["update_date"];
            } else if ($refTrackingTypeId==2) {
                $save_tracking["start_date_implementation"] = $data["start_date"];
                $save_tracking["end_date_implementation"]   = $data["end_date"];
            }
            $save_tracking["user_id_modify"] = 11;
            $id = $this->ProjectTracking->update($save_tracking, isset($tracking["id"])?$tracking["id"]:'');
            // log_tracking($id);
            // $project_id_final = !empty($tracking) ? $tracking['project_id_final'] : '';
            // log_project($projectTrId, "Update Tracking: $project_id_final");
        }
    }

    function counterDailyProposal()
    {
        $start_date = date('Y-m-d').' 00:00:01';
        $end_date = date('Y-m-d').' 23:59:59';
        $data = $this->db->get_where('project_tr', "create_date BETWEEN '$start_date' AND '$end_date'")->num_rows();
        $counter = str_pad($data + 1, 3, 0, STR_PAD_LEFT);
        return $counter;
    }

    function create_proposal($post) {
        $time = date('H:i:s');
        $y = date('Y');
        $m = date('m');
        $d = date('d');
        $daily_counter = $this->counterDailyProposal();
        $temp_id = $y . $m . $d . $daily_counter;
        if ($post['main_committee'] != '-') {
            $main_committee_id = $post['main_committee'];
        } else {
            $main_committee_id = 0;
        }

        if ($post['sub_committee'] != '-') {
            $sub_committee_id = $post['sub_committee'];
        } else {
            $sub_committee_id = null;
        }

        if ($post['proposed_commencement_date'] != '-' && $post['proposed_completion_date'] != '-') {
            $date1        = date_create($post['proposed_commencement_date']);
            $date2        = date_create($post['proposed_completion_date']);
            $diff         = date_diff($date1, $date2);
            $six_month    = 30 * 6;
            $twelve_month = 30 * 12;
            $one_year     = 366;
            if ($diff->d >= $one_year) {
                // duration is > 12 months
                $type_project_durations_id = 1004;
            } else if ($diff->d >= $six_month && $diff->d < $one_year) {
                // duration is 6-12 months
                $type_project_durations_id = 1003;
            } else {
                // duration is < 6 months
                $type_project_durations_id = 1002;
            }
        } else {
            // $type_project_durations_id = 1002;
            $type_project_durations_id = null;
        }

        // Funding Project
        $funding_source        = explode('|', $post['funding_source']);
        $funding_source_budget = explode('|', $post['funding_source_budget']);
        $total_funding         = 0;
        foreach ($funding_source_budget as $key => $value) {
            $budget = str_replace(',', '', $value);
            $total_funding = $total_funding + $budget;
        }

        // -------------------- Save data transaksi proposal --------------------
        $is_cross_sector = str_replace(' ', '', explode('|', $post['project_classification']))[0];
        $data_transaksi  = array(
            'project_tr_title'                    => $post['title'] != '-' ? $post['title'] : '',
            'project_tr_desc'                     => $post['description'] != '-' ? $post['description'] : '',
            'proposal_id'                         => $post['proposal_id'] != '-' ? $post['proposal_id'] : '',
            'project_id'                          => $post['previous_project_id'] != '-' ? $post['previous_project_id'] : '',
            'type_intervention_id'                => str_replace(' ', '', explode('|', $post['intervention_type']))[0],
            'type_nature_corporation_id'          => str_replace(' ', '', explode('|', $post['cooperation_type']))[0],
            'project_commancement_date'           => $post['proposed_commencement_date'] != '-' ? $post['proposed_commencement_date'].' '.$time : null,
            'project_completion_date'             => $post['proposed_completion_date'] != '-' ? $post['proposed_completion_date'].' '.$time : null,
            'is_recurring_project'                => $post['is_recurring'] == 'Yes' ? 1 : 0,
            'is_cross_sector'                     => $is_cross_sector,
            'proposal_temp_id'                    => $temp_id,
            'project_supporting_sectoral_body_id' => $sub_committee_id,
            'type_project_durations_id'           => $type_project_durations_id,
            'project_participating'               => str_replace(' ', '', explode('|', $post['participants']))[0],
            'proposal_submission_date'            => $post['proposal_submission_date'] != '-' ? $post['proposal_submission_date'] : null,
            'propose_total_funding'               => $total_funding,
            'is_save_as_draft'                    => 0,
            'is_complete'                         => 0,
            'is_edit_proposal_temp_id'            => '',
            'data_migration'                      => 1,
        );

        $tr_id = $this->Direct2db_model->insert($data_transaksi, 'project_tr');
        $act = "Insert Data Proposal Temporary";
        insert_log($act);
        // -------------------- End save data transaksi proposal --------------------

        // -------------------- Save participants --------------------
        $affiliation = isset($post['participants']) ? $post['participants'] : '';
        if ($affiliation)
        {
            $country_id = $post['more_countries_of_participants'];
            if ($country_id != '-') {
                $country_id = explode('|', $country_id);
                foreach ($country_id as $key => $value)
                {
                    $country_aff[$key] = array(
                        'project_trx_id' => $tr_id,
                        'country_id'     => $value,
                    );
                }

                $this->Direct2db_model->insert_batch($country_aff, 'affiliation_tr');
                $act = "Insert Data Country ID Transaction";
                insert_log($act);
            }
        }
        // -------------------- End save participants --------------------

        // -------------------- Save pillars --------------------
        if ($post['pillar'] != '-')
        {
            $pillar_id = explode('|', $post['pillar']);
            foreach ($pillar_id as $key => $value)
            {
                if (count($pillar_id) == 1 || substr_count($value, 'main_') > 0)
                {
                    // if pillar just one so make it as main automatically
                    $insert_pillar[$key] = array(
                        'project_trx_id'     => $tr_id,
                        'type_of_pillars_id' => substr_count($value, 'main_') > 0 ? str_replace('main_', '', $value) : $value,
                        'is_main_pillar'     => 1,
                    );
                }
                else
                {
                    $insert_pillar[$key] = array(
                        'project_trx_id'     => $tr_id,
                        'type_of_pillars_id' => $value,
                        'is_main_pillar'     => 0,
                    );
                }

            }

            $this->Direct2db_model->insert_batch($insert_pillar, 'pillar_tr');
            $act = "Insert Data Pillar Transaction";
            insert_log($act);
        }
        // -------------------- End save pillars --------------------

        // -------------------- Save proponent organizations --------------------
        if ($post['proponent'] != '-')
        {
            $org = explode('|', $post['proponent']);
            foreach ($org as $key => $value)
            {
                if (is_numeric($value)) {
                    $proponent_org[$key] = array(
                        'project_trx_id'   => $tr_id,
                        'proponent_org_id' => $value,
                        'is_proponent'     => 1,
                        'is_agency'        => 0,
                    );
                } else {
                    $is_proponent_exists = $this->db->get_where('proponent_org_mst', "proponent_org_title LIKE '%".$value."%' AND is_delete = 0")->row_array();
                    if ($is_proponent_exists) {
                        $proponent_org[$key] = array(
                            'project_trx_id'   => $tr_id,
                            'proponent_org_id' => $is_proponent_exists['proponent_org_id'],
                            'is_proponent'     => 1,
                            'is_agency'        => 0,
                        );
                    } else {
                        $this->db->insert('proponent_org_mst', array(
                            'proponent_org_title'   => $value,
                            'proponent_org_address' => 'n/a',
                            'user_id_create'        => id_user(),
                            'create_date'           => date('Y-m-d H:i:s'),
                            'is_proponent'          => 1,
                            'is_agency'             => 0,
                        ));
                        $proponent_org_id = $this->db->insert_id();

                        $proponent_org[$key] = array(
                            'project_trx_id'   => $tr_id,
                            'proponent_org_id' => $proponent_org_id,
                            'is_proponent'     => 1,
                            'is_agency'        => 0,
                        );
                    }
                }
            }

            $this->Direct2db_model->insert_batch($proponent_org, 'proponent_org_tr');
            $act = "Insert Data Proponent Organization Transaction";
            insert_log($act);
        }
        // -------------------- End save proponent organizations --------------------

        // -------------------- Save proponent agency --------------------
        if ($post['agency'] != '-')
        {
            $org = explode('|', $post['agency']);
            foreach ($org as $key => $value)
            {
                $proponent_agency[$key] = array(
                    'project_trx_id'   => $tr_id,
                    'proponent_org_id' => $value,
                    'is_proponent'     => 0,
                    'is_agency'        => 1,
                );
            }

            $this->Direct2db_model->insert_batch($proponent_agency, 'proponent_org_tr');
            $act = "Insert Data Proponent Agency Transaction";
            insert_log($act);
        }
        // -------------------- End save proponent agency --------------------

        // -------------------- Save single or cross project --------------------
        $main_data_sectoral = array(
            'project_trx_id'        => $tr_id,
            'sectoral_body_id'      => $main_committee_id,
            'is_main_sectoral_body' => 1,
        );

        $this->Direct2db_model->insert($main_data_sectoral, 'sectoral_body_tr');

        // add supporting sectoral body if cross sector
        if ($is_cross_sector != 0)
        {
            if ($post['supporting_committee'] != '-')
            {
                $sectoral_body = explode('|',$post['supporting_committee']);
                foreach ($sectoral_body as $key => $value)
                {
                    $support_sector[$key] = array(
                        'project_trx_id'        => $tr_id,
                        'sectoral_body_id'      => $value,
                        'is_main_sectoral_body' => 0,
                    );
                }

                $this->Direct2db_model->insert_batch($support_sector, 'sectoral_body_tr');
                $act = "Insert Data Sectoral Body Transaction";
                insert_log($act);
            }
        }
        // -------------------- End single or cross project --------------------

        // -------------------- Save project objective --------------------
        if ($post['project_objective'] != '-')
        {
            $objectives = explode("|", $post['project_objective']);
            foreach ($objectives as $key => $value)
            {
                $objective_tr_data[$key] = array(
                    'project_objectives_trx_name' => $value,
                    'project_trx_id'              => $tr_id,
                );
            }

            $this->Direct2db_model->insert_batch($objective_tr_data, 'project_objectives_trx');
            $act = "Insert project objectives";
            insert_log($act);
        }
        // -------------------- End save project objective --------------------

        // -------------------- Save project output --------------------
        if ($post['project_output'] != '-')
        {
            $output = explode('|', $post['project_output']);
            foreach ($output as $key => $value)
            {
                $output_tr_data[$key] = array(
                    'project_result_trx_name' => $value,
                    'project_trx_id'          => $tr_id,
                );
            }
            $this->Direct2db_model->insert_batch($output_tr_data, 'project_result_trx');
            $act = "Insert project result";
            insert_log($act);
        }
        // -------------------- End save project output --------------------

        // -------------------- Save funding source --------------------
        if ($post['funding_source'] != '-')
        {
            foreach ($funding_source_budget as $key => $value) {
                $budget_project[$key] = array(
                    'project_trx_id'          => $tr_id,
                    'proposal_budget_project' => str_replace(',', '', $value),
                    'funding_source_id'       => $funding_source[$key],
                );
            }

            $this->Direct2db_model->insert_batch($budget_project, 'project_funding_source_tr');
            $act = "Insert Data Project Funding Source Transaction";
            insert_log($act);
        }
        // -------------------- End save funding source --------------------

        return $tr_id;
    }

    function update_proposal($id, $post) {
        $data = $this->db->select('*')->get_where('project_tr', array('project_tr_id' => $id))->row_array();
        if ($data) {
            $funding_source        = explode('|', $post['funding_source']);
            $funding_source_budget = explode('|', $post['funding_source_budget']);
            $total_funding         = 0;
            foreach ($funding_source_budget as $key => $value) {
                $budget = str_replace(',', '', $value);
                $total_funding = $total_funding + $budget;
            }

            // -------------------- Save data transaksi proposal --------------------
            $is_cross_sector = str_replace(' ', '', explode('|', $post['project_classification']))[0];
            $data_transaksi  = array(
                'project_tr_title'                    => $post['title'] != '-' ? $post['title'] : '',
                'proposal_id'                         => $post['proposal_id'] != '-' ? $post['proposal_id'] : '',
                'proposal_submission_date'            => $post['proposal_submission_date'] != '-' ? $post['proposal_submission_date'] : null,
                'propose_total_funding'               => $total_funding,
                'data_migration'                      => 1,
            );

            $this->Direct2db_model->update($data_transaksi, $id, 'project_tr', 'project_tr_id');
            $act = "Update Data Proposal Temporary";
            insert_log($act);
            // -------------------- End save file proposal --------------------

            // -------------------- Save pillars --------------------
            if ($post['pillar'] != '-' && $post['pillar'] != '')
            {
                $update_pillar['is_delete'] = 1;
                $this->db->update('pillar_tr', $update_pillar, "project_trx_id = $id");
                $pillar_id = explode('|', $post['pillar']);
                foreach ($pillar_id as $key => $value)
                {
                    if (count($pillar_id) == 1 || substr_count($value, 'main_') > 0)
                    {
                        // if pillar just one so make it as main automatically
                        $insert_pillar[$key] = array(
                            'project_trx_id'     => $id,
                            'type_of_pillars_id' => substr_count($value, 'main_') > 0 ? str_replace('main_', '', $value) : $value,
                            'is_main_pillar'     => 1,
                            'is_delete'          => 0,
                        );
                    }
                    else
                    {
                        $insert_pillar[$key] = array(
                            'project_trx_id'     => $id,
                            'type_of_pillars_id' => $value,
                            'is_main_pillar'     => 0,
                            'is_delete'          => 0,
                        );
                    }

                }

                $this->Direct2db_model->insert_batch(array_values($insert_pillar), 'pillar_tr');
                $act = "Update Data Pillar Transaction";
                insert_log($act);
            }
            // -------------------- End save pillars --------------------

            // -------------------- Save funding source --------------------
            if ($post['funding_source'] != '-' && $post['funding_source'] != '')
            {
                $update_pillar['is_delete'] = 1;
                $this->db->update('project_funding_source_tr', $update_pillar, "project_trx_id = $id");
                foreach ($funding_source_budget as $key => $value) {
                    $budget_project[$key] = array(
                        'project_trx_id'          => $id,
                        'proposal_budget_project' => str_replace(',', '', $value),
                        'funding_source_id'       => $funding_source[$key],
                        'is_delete'               => 0,
                    );
                }

                $this->Direct2db_model->insert_batch(array_values($budget_project), 'project_funding_source_tr');
                $act = "Update Data Project Funding Source Transaction";
                insert_log($act);
            }
            // -------------------- End save funding source --------------------
        }
        return $id;
    }

    function insert_attachments($id = '') {
        $data = $this->db->select('*')->get_where('project_tr', array('project_tr_id' => $id))->row_array();
        $total_file_upload = 0;
        if ($data) {
            $proposal_id   = $data['proposal_id'];
            $proposal_year = ($data['proposal_submission_date'] != null && $data['proposal_submission_date'] != '-') ? explode('-', $data['proposal_submission_date'])[0] : null;
            if ($proposal_year) {
                $source_path   = DOCUMENT_PATH."../source/";
                $path = $source_path.$proposal_year.'/'.str_replace('/', '-', $proposal_id);
                // Baca file-file yg ada di folder Others
                if (file_exists($path.'/Others')) {
                    if ($handle = opendir($path.'/Others')) {
                        $i = 0;
                        while (false !== ($entry = readdir($handle))) {
                            if ($entry != "." && $entry != ".." && !is_dir($path.'/Others/'.$entry)) {
                                $upload = $this->uploadFile($path.'/Others/'.$entry, UPLOAD_PROPOSAL_ID . $data['proposal_temp_id'] . '/');
                                // $migrate_res['attachments']['other'][$i] = $upload;
                                $i++;

                                if ($upload['status'] == true) {
                                    $file_project[$i] = array(
                                        'project_trx_id'          => $data['project_tr_id'],
                                        'proposal_file_upload_id' => $upload['file_id'],
                                        'files_category_mst_id'   => 4, // Other annex
                                    );

                                    $total_file_upload++;
                                }
                            }
                        }

                        if (isset($file_project)) {
                            $this->Direct2db_model->insert_batch($file_project, 'proposal_file_upload_tr');
                            $act = "Insert Data File Proposal Temporary Transaction";
                            insert_log($act);
                        }
                    }
                }
            }
        }

        return $total_file_upload;
    }

    function uploadFile($src_path, $dst_path)
    {
        $list_file_type = file_extension();
        if ($src_path)
        {
            $src_path  = $src_path;
            $mimetype  = mime_content_type($src_path);
            $size      = filesize($src_path);
            $seg       = explode('/', $src_path);
            $file_name = end($seg);
            $file_seg  = explode('.', $file_name);
            $extension = strtolower(end($file_seg));
            if ($size > MAX_FILE_SIZE_UPLOAD)
            {
                $ret['status']    = false;
                $ret['file_name'] = $file_name;
                $ret['message']   = 'File too big';
            }
            else
            {
                if (in_array($extension, $list_file_type))
                {
                    $save_file = array(
                        'name'     => $file_name,
                        'type'     => $mimetype,
                        'tmp_name' => '',
                        'size'     => $size,
                    );

                    $idfile_saved   = $this->Direct2db_model->insert($save_file, 'proposal_file_upload');
                    // $jam            = str_replace(':', '', date('H:i:s'));
                    // $folder_name    = generate_rand(4) . '-' . $jam;

                    if (!file_exists($dst_path))
                    {
                        mkdir($dst_path, 0755, true);
                    }

                    $sukses = copy($src_path, $dst_path . $file_name);
                    $ret['status']    = true;
                    $ret['file_name'] = $file_name;
                    $ret['file_id']   = $idfile_saved;
                }
                else
                {
                    // $zip = new ZipArchive;
                    // $res   = $zip->open($src_path);
                    // if ($res === TRUE) {
                    //   $zip->extractTo($dst_path);
                    //   $zip->close();
                    //   $ret['status']    = true;
                    //   $ret['file_id']   = 0;
                    //   echo 'Zip!';
                    // } else {
                        $ret['status']    = false;
                        $ret['file_name'] = $file_name;
                        $ret['message']   = 'Invalid file type';
                    // }
                }
            }
        }
        else
        {
            $ret['status']   = false;
            $ret['message']  = 'File path is empty';
        }

        return $ret;
    }

    function assign($id, $do_user_id) {
        // --------------- Get data for assign ---------------
        $data = $this->db->select('project_tr_id, project_tr_title, project_tr_desc,proposal_id, propose_total_funding')
        ->get_where('project_tr', array(
            'project_tr_id' => $id
        ))->result_array();

        $data['project_tr_id'] = isset($data['project_tr_id']) ? $data['project_tr_id']: '';
        $data['project_tr_title'] = isset($data['project_tr_title']) ? $data['project_tr_title']: '';
        $data['project_tr_desc'] = isset($data['project_tr_desc']) ? $data['project_tr_desc']: '';
        $data['proposal_id'] = isset($data['proposal_id']) ? $data['proposal_id']: '';

        $countries = $this->db->select('country_name')
        ->join('proponent_org_mst b', 'b.proponent_org_id = a.proponent_org_id', 'LEFT')
        ->join('country_mst c', 'c.country_id = b.country_id', 'LEFT')
        ->get_where('proponent_org_tr a', array(
            'a.project_trx_id' => $id,
            'a.is_proponent' => 1,
        ))->result_array();
        if (count($countries) > 0) {
            $countries = array_column($countries, 'country_name');
        }

        $merge_country = implode(', ', $countries);
        $replace['url']                     = base_url() . "project_proposal/view/$data[project_tr_id]";
        $replace['projectTitle']            = $data['project_tr_title'];
        $replace['proponent_country']       = $merge_country;
        $replace['projectDescription']      = $data['project_tr_desc'];
        $replace['proposal_funding_source'] = (isset($data['propose_total_funding'])) ? money_format_custom('%.2n', $data['propose_total_funding']) : money_format_custom('%.2n', 0);

        $this->load->helper('mail');
        $user_sess     = $this->session->userdata('ADM_SESS');
        $tpt_assign    = mail_tpt_new(['c.code' => 'ASSIGN'], $replace, $user_sess, 'assign');
        $email_content = !empty($tpt_assign) ? $tpt_assign['content'] : '';
        $assign_detail = !empty($tpt_assign) ? $tpt_assign['details'] : '';
        $do_user = $this->db->select('username')
        ->get_where('view_users_roles a', array(
            'id_auth_user' => $do_user_id,
        ))->result_array();

        if ($do_user) {
            $email_content = str_replace('{userName}', $do_user['username'], $email_content);
        }
        $email_content = str_replace('{projectTitle}', $data['project_tr_title'], $email_content);
        $email_content = str_replace('{projectDescription}', $data['project_tr_desc'], $email_content);
        // --------------- End Get data for assign ---------------
        $post = array(
            'id_auth_user'  => id_user(),
            'type'          => 'assign',
            'message'       => $email_content,
            'message_cc'    => $email_content,
            'cc'            => isset($assign_detail['email_cc']) ? $assign_detail['email_cc'] : '',
            'id_cc'         => isset($assign_detail['id_cc']) ? $assign_detail['id_cc'] : '',
            'struc_org_id'  => 1067,
        );
        // --------------- Assign Process ---------------
        $ret['error'] = 1;
        $type         = 'assign';

        $save["id_auth_user_$type"]     = $post['id_auth_user'];
        $save["id_ref_status_approval"] = 0;
        if (!empty($post['id_cc'])) {
            $save["id_auth_user_assign_cc"] = $post['id_cc'];
        }

        $save[$type . "_message"]        = $post["message"];
        $save[$type . "_message_cc"]     = $post["message_cc"];
        $save["struc_org_id_$type"]      = $post['struc_org_id'];
        $save['is_complete']             = 1;
        $save["ref_project_status_code"] = '';

        // Create review step
        $review_step = $this->project_review_step($id, 'save');
        $process = $this->Direct2db_model->update(
            $save,
            $id,
            'project_tr',
            'project_tr_id',
        );
        if ($process)
        {
            log_project($id, ucfirst($type) , NULL, $review_step);
        }
        // --------------- End assign Process ---------------
    }

    function generate_proposal_id($id_project, $debug = '')
    {
        $data = getSingleDataApi('project_tr', array(
            'project_tr_id' => $id_project
        ));
        if ($data)
        {
            $this->load->model('SectoralBodyTr');
            # format :
            # sector/sub_sector/2 digit tahun berjalan/auto-increment mengacu pada sector, reset per tahun/scope
            //sector
            // echo $data['sectoral_body_id'];
            $secBodyTr = getSingleDataApi('sectoral_body_tr', array(
                'project_trx_id'        => $id_project,
                'is_main_sectoral_body' => 1
            ));
            $secBody = getSingleDataApi('sectoral_body_mst', array(
                'sectoral_body_id' => $secBodyTr['sectoral_body_id']
            ));
            //subsector
            $secBodyWG = getSingleDataApi('sectoral_body_wg_mst', array(
                'sectoral_body_wg_id' => $data['project_supporting_sectoral_body_id']
            ));
            //2 digit tahun berjalan
            $year = $data['is_edit_proposal_temp_id'] == 0 ? date('y') : substr($data['proposal_temp_id'], 2, 2);
            //auto-increment mengacu pada sector, reset per tahun
            // $projectTr = getDataApi('project_tr',"proposal_id like '%/$year/%' AND sectoral_body_id = $data[sectoral_body_id] AND project_tr_id != $id_project");
            // $projectTr = $this->SectoralBodyTr->fetchAll("b.proposal_id like '%/$year/%' AND a.sectoral_body_id = $secBodyTr[sectoral_body_id] AND a.project_trx_id != $id_project AND is_main_sectoral_body = 1");
            $projectTr = getDataApi('project_tr', "proposal_id like '$secBody[sector]%/$year/%'");
            // print_r($projectTr);exit;
            $proposal_id = [];
            foreach ($projectTr as $key => $value)
            {
                $proposal = explode('/', $value['proposal_id']);
                $proposal_id[] = (int)$proposal[3];
            }

            if (!empty($proposal_id)) {
                // $ai = count($projectTr)+1;
                $ai = max($proposal_id) + 1;
            } else {
                $ai = 1;
            }
            $ai_hundred = get_number($ai);
            # scope
            $scope = getSingleDataApi('affiliation_mst', array(
                'affiliation_id' => $data['project_participating']
            ));
            $affiliation_tr = getSingleDataApi('affiliation_tr', array(
                'project_trx_id' => $id_project
            ));
            $asean_scope = getSingleDataApi('affiliation_mst', array(
                'affiliation_id' => 6
            ));
            if ($data['project_participating'] == '6')
            { // ASEAN
                if ($affiliation_tr)
                { // ASEAN + Single Country
                    $scope_selected = $asean_scope['affiliation_code'];
                }
                else
                {
                    $scope_selected = $scope['affiliation_code'];
                }
            }
            else if ($data['project_participating'] == '9')
            { // ASEAN Plus 3
                if ($affiliation_tr)
                { // ASEAN PLus 3 + Single Country
                    $scope_selected = $asean_scope['affiliation_code'];
                }
                else
                {
                    $scope_selected = $scope['affiliation_code'];
                }
            }
            else if ($data['project_participating'] == '10')
            { // CLMV
                if ($affiliation_tr)
                { // CLMV + Single Country
                    $scope_selected = $asean_scope['affiliation_code'];
                }
                else
                {
                    $scope_selected = $scope['affiliation_code'];
                }
            }
            else if ($data['project_participating'] == '7')
            { // Single country
                $scope_selected = $scope['affiliation_code'];
            }
            else
            { // others
                $scope_selected = $scope['affiliation_code'];
            }

            if (empty($secBody['sector']))
            {
                $sectoral_code = '0';
            }
            else
            {
                $sectoral_code = $secBody['sector'];
            }

            if (empty($secBodyWG['sub_sector']))
            {
                $sectoral_bw_code = '0';
            }
            else
            {
                $sectoral_bw_code = $secBodyWG['sub_sector'];
            }

            $ret = $sectoral_code . '/' . $sectoral_bw_code . '/' . $year . '/' . $ai_hundred . '/' . $scope_selected;
        }
        return $ret;
    }

    function project_review_step($project_tr_id, $process = '')
    {
        if ($project_tr_id)
        {
            $project = $this->db->select('a.propose_total_funding, a.is_recurring_project, b.funding_source_id')
                ->join('project_funding_source_tr b', 'b.project_trx_id = a.project_tr_id', 'LEFT')
                ->get_where('project_tr a', array(
                    'a.project_tr_id' => $project_tr_id,
            ))->result_array();
            $propose_total_funding = $project[0]['propose_total_funding'];
            $is_recurring_project = $project[0]['is_recurring_project'];
            // print_r($project);exit;
            if (!empty($project[0]['funding_source_id']))
            {
                foreach ($project as $key => $value)
                {
                    $funding_source_ids[] = $value['funding_source_id'];
                }
                $funding = " IN (" . implode(',', $funding_source_ids) . ")";
            }
            else
            {
                $funding = 'IS NULL';
            }

            if (isset($propose_total_funding)) {
                $is_budget_over_100k = $propose_total_funding >= 100000 ? 1 : 0;
            } else {
                $is_budget_over_100k = 0;
            }

            if (isset($is_recurring_project)) {
                $is_first_project = $is_recurring_project ? 0 : 1;
            } else {
                $is_first_project = 0;
            }

            $data = $this->db->select('c.id, c.code,c.review_sort, c.total_day_single_sector, c.total_day_cross_sector, c.is_working_day')
                ->distinct()
                ->join('project_review_step_detail b', 'b.project_review_step_id = a.id AND b.is_delete = 0', 'RIGHT')
                ->join('ref_project_review c', 'c.id = b.ref_project_review_id AND  c.is_delete = 0', 'RIGHT')
                ->where("funding_source_id $funding")
                ->get_where('project_review_step a', array(
                    'is_first_project' => $is_first_project,
                    'is_budget_over_100k' => $is_budget_over_100k,
            ))->result_array();

            $date = date('Y-m-d');
            $time = ' ' . date('H:i:s');
            if ($process == 'save')
            {
                $step = [];
                foreach ($data as $key => $value)
                {

                    if (!$value['id']) continue;

                    $save["project_tr_id"]         = $project_tr_id;
                    $save["ref_project_review_id"] = $value['id'];
                    $save["start_date"]            = $key == 0 ? ($date . $time) : NULL;
                    if (isset($project['is_cross_sector'])) {
                        $save["duration_day"]          = $project['is_cross_sector'] == 1 ? $value['total_day_cross_sector'] : $value['total_day_single_sector'];
                    } else {
                        $save["duration_day"]          = $value['total_day_single_sector'];
                    }

                    $save["is_working_day"]        = $value['is_working_day'];
                    $save["project_review_trans_status_id"] = $key == 0 ? 1 : 0;
                    if ($value['is_working_day'] == 1)
                    {
                        $end_date = get_working_day_date($date, $save['duration_day']);
                    }
                    else
                    {
                        $end_date = date('Y-m-d', strtotime("+$save[duration_day] days", strtotime($date)));
                    }
                    $save["end_date"] = $key == 0 ? ($end_date . $time) : NULL;
                    $cek = $this->db->select('id')
                        ->get_where('project_review_trans', array(
                            'project_tr_id' => $project_tr_id,
                            'ref_project_review_id' => $save['ref_project_review_id'],
                    ))->row_array();

                    if (isset($cek['id'])) {
                        $this->Direct2db_model->update($save, $cek['id'], 'project_review_trans', 'id');
                    } else {
                        $this->Direct2db_model->insert($save, 'project_review_trans');
                    }
                    $step[] = $value['code'];
                    $ids[]  = $value['id'];
                }

                if (isset($ids))
                {
                    $del = $this->db->select('id')
                        ->where_not_in('ref_project_review_id', $ids)
                        ->get_where('project_review_trans', array(
                            'project_tr_id' => $project_tr_id,
                    ))->result_array();
                    if (count($del) > 0) {
                        foreach ($del as $key => $value) {
                            $this->Direct2db_model->update(array('is_delete' => 1), $value['id'], 'project_review_trans', 'id');
                        }
                    }
                }
                return true;
            }

            // debugvar($data);
            return $data;
        }
    }

    function get_step_review($project_tr_id)
    {
        if ($project_tr_id)
        {
            $extra['table_join'] = array('ref_project_review b');
            $extra['where_join'] = array('a.ref_project_review_id = b.id');
            $extra['select']     = "a.*, b.code, b.name";
            $extra['sort']       = "ASC";
            $extra['field_name'] = "review_sort";
            $review_step         = getDataApi('project_review_trans', "project_tr_id = $project_tr_id", $extra);
            return array_column($review_step, 'name');
        }
    }

    function finish($id, $approve_date)
    {
        $data = $this->db->select('proposal_submission_date, proposal_id')
                ->get_where('project_tr', array(
                    'project_tr_id' => $id,
            ))->row_array();
        $ret['status']      = false;
        $ret['total_files'] = 0;
        if ($data) {
            $post = array(
                'total_weighted_score' => '61.21',
                'approve_date'         => ($approve_date == '-' || empty($approve_date))? date('Y-m-d') : $approve_date,
            );

            $activeReview = $this->db->select('a.id, a.project_tr_id, a.ref_project_review_id, a.start_date, a.end_date, a.project_review_trans_status_id, a.is_working_day, a.duration_day, a.finish_date, b.code as review,c.isRevision,c.isCompleteRevision,c.isReject,c.isRejectRevision,c.revision_complete_reason,c.remarks_complete,c.revision_reason,c.remarks, c.revision_request_user_id, c.revision_approve_user_id,c.id as finish_id')
                ->join('ref_project_review b', 'b.id = a.ref_project_review_id', 'left')
                ->join('project_review_trans_finish c', 'c.project_review_trans_id = a.id', 'left')
                ->get_where('project_review_trans a', array(
                    'project_tr_id' => $id,
                    'project_review_trans_status_id' => 1,
            ))->row_array();
            if ($activeReview) {
                if ($post) {
                    $this->Direct2db_model->update(
                        array('ref_project_status_code' => ''),
                        $id,
                        'project_tr',
                        'project_tr_id',
                    );
                    $save["project_review_trans_id"] = isset($activeReview["id"]) ? $activeReview["id"] : '';
                    if (isset($post["total_weighted_score"])) {
                        $save["total_weighted_score"] = $post["total_weighted_score"];
                    }

                    if (isset($post["remarks"])) {
                        $save["remarks"] = $post["remarks"];
                    }

                    $review = isset($activeReview["review"])?$activeReview["review"]:'';
                    $files  = [];

                    $source_path   = DOCUMENT_PATH."../source/";
                    $proposal_year = $data['proposal_submission_date'] != '-' ? explode('-', $data['proposal_submission_date'])[0] : null;
                    if ($proposal_year) {
                        $path = $source_path.$proposal_year.'/'.str_replace('/', '-', $data['proposal_id'])."/Review/$review";

                        // Baca file-file yg ada di folder Review
                        if (file_exists($path)) {
                            if ($handle = opendir($path)) {
                                $i = 0;
                                while (false !== ($entry = readdir($handle))) {
                                    if ($entry != "." && $entry != ".." && !is_dir($path.'/'.$entry)) {
                                        $upload = $this->uploadFile($path.'/'.$entry, DOCUMENT_REVIEW_PATH."/".$id.'/'.$review.'/');
                                        $migrate_res['attachments']['other'][$i] = $upload;
                                        $i++;

                                        if ($upload['status'] == true) {
                                            $ret['total_files']++;

                                            $files[$i]["name"]     = $entry;
                                            $files[$i]["category"] = 4;
                                        }
                                    }
                                }
                            }
                        }
                    }

                    if (!empty($files)) {
                        $save["files"] = json_encode($files);
                    }
                    if (isset($activeReview['finish_id'])) {
                        $finish_id = $this->Direct2db_model->update(
                            $save,
                            $activeReview['finish_id'],
                            'project_review_trans_finish',
                            'id',
                        );
                    } else {
                        $finish_id = $this->Direct2db_model->insert(
                            $save,
                            'project_review_trans_finish',
                        );
                    }
                    if (!isset($post["request_reject"])
                        || isset($post["request_reject"]) && $post["request_reject"] == ''
                    ) {
                        $update["project_review_trans_status_id"] = 4; //complete
                        $update["finish_date"] = date("Y-m-d H:i:s");
                        $update['approve_date'] = isset($post['approve_date'])?$post['approve_date']:'';
                        if (isset($activeReview['id'])) {
                            $finish_id = $this->Direct2db_model->update(
                                $update,
                                $activeReview['id'],
                                'project_review_trans',
                                'id',
                            );
                        } else {
                            $finish_id = $this->Direct2db_model->insert(
                                $update,
                                'project_review_trans',
                            );
                        }

                        $nextReview = $this->db->select('a.*, b.code as review')
                            ->join('ref_project_review b', 'b.id = a.ref_project_review_id', 'left')
                            ->order_by('b.review_sort', 'asc')
                            ->get_where('project_review_trans a', array(
                                'project_tr_id' => $id,
                                'project_review_trans_status_id' => 0,
                        ))->row_array();
                        if ($nextReview) {
                            $next["project_review_trans_status_id"] = 1; // active
                            $next["start_date"] = $update["finish_date"];
                            $next["end_date"]   = get_working_day_date($update["finish_date"], $nextReview["duration_day"], $nextReview["is_working_day"]) . " " .date("H:i:s");
                            $this->Direct2db_model->update(
                                $next,
                                $nextReview['id'],
                                'project_review_trans',
                                'id',
                            );
                        } else {
                            $projectId = generate_project_id($id);
                            $this->Direct2db_model->update(
                                array(
                                    "project_id_final" => $projectId,
                                    'ref_project_status_code' => 'FR'
                                ),
                                $id,
                                'project_tr',
                                'project_tr_id',
                            );
                            // $this->ProjectTr->updateStatus($id, "FR");
                        }
                    }

                    $review           = isset($activeReview['review']) ? $activeReview['review'] : '';
                    $id_active_review = isset($activeReview["id"]) ? $activeReview["id"] : '';
                    $project_log_id   = log_project($id, "Finish $review", null, null, ['approve_date'=>$post['approve_date']]);
                    log_finish($project_log_id, $id_active_review, $finish_id);

                    $ret['status'] = true;
                }
            }
        }

        return $ret;
    }

    function project_tracking($projectTrId='', $refTrackingTypeId='', $status='', $update_date = null, $remarks = null)
    {
        $ret['total_files'] = 0;
        // $data = $this->ProjectTr->fetchRow("a.project_tr_id = $projectTrId");
        $data = $this->db->select('proposal_submission_date, project_id_final, proposal_id')
                ->get_where('project_tr', array(
                    'project_tr_id' => $projectTrId,
            ))->row_array();
        $tracking = $this->db->select('id')
                ->get_where('project_tracking', array(
                    'project_tr_id' => $projectTrId,
                    'ref_tracking_type_id' => $refTrackingTypeId,
            ))->row_array();

        if ($tracking) {
            $data = array_merge($data, $tracking);
        }

        $post  = array(
            // 'folder_temp_name' => 'xaHi-102812',
            // 'next_no'          => '2',
            // 'files_tr_id[]'    => '0',
            // 'files_id[]'       => '13749',
            // 'files_name[]'     => 'soal-logika-kandidat-v3.pdf',
            // 'category_files[]' => '4',
            // 'update_date'      => date('Y-m-d'),
            'update_date'      => ($update_date == '-' || empty($update_date))? date('Y-m-d') : $update_date,
            'remarks'          => ($remarks == '-' || empty($remarks))? '' : $remarks,
        );
        $is_do = isGroup("DO");
        if ($post) {
            $save["project_tr_id"]        = $projectTrId;
            $save["ref_tracking_type_id"] = $refTrackingTypeId;
            $save["update_date"]          = $post["update_date"];
            $save["remarks"]              = $post["remarks"];


            if (isset($post["start_date_implementation"]) && !empty($post["start_date_implementation"])) {
                $save["start_date_implementation"] = $post["start_date_implementation"];
            } else {
                // $save["start_date_implementation"] = '';
            }
            if (isset($post["end_date_implementation"]) && !empty($post["end_date_implementation"])) {
                $save["end_date_implementation"] = $post["end_date_implementation"];
            } else {
                // $save["end_date_implementation"] = '';
            }

            $files = [];
            $source_path   = DOCUMENT_PATH."../source/";
            $proposal_year = $data['proposal_submission_date'] != '-' ? explode('-', $data['proposal_submission_date'])[0] : null;
            if ($refTrackingTypeId=='3') {
                if ($proposal_year) {
                    $path = $source_path.$proposal_year.'/'.str_replace('/', '-', $data['proposal_id'])."/Tracking";

                    // Baca file-file yg ada di folder Review
                    if (file_exists($path)) {
                        if ($handle = opendir($path)) {
                            $i = 0;
                            while (false !== ($entry = readdir($handle))) {
                                if ($entry != "." && $entry != ".." && !is_dir($path.'/'.$entry)) {
                                    $upload = $this->uploadFile($path.'/'.$entry, DOCUMENT_TRACKING_PATH . "/" . $refTrackingTypeId . "/" . $projectTrId . "/");
                                    $migrate_res['attachments']['other'][$i] = $upload;
                                    $i++;

                                    if ($upload['status'] == true) {
                                        $ret['total_files']++;

                                        $files[$i]["name"]     = $entry;
                                        $files[$i]["category"] = 4;
                                    }
                                }
                            }
                        }
                    }
                }
            }

            if (isset($post["files_name_edit"])) {
                $key = isset($key) ? $key++ : 1;
                foreach ($post["files_name_edit"] as $k => $fname) {
                    $files[$key + $k]["name"]     = $fname;
                    $files[$key + $k]["category"] = (int) $post["category_edit"][$k];
                }
            }

            if (!empty($files)) {
                $save["files"] = json_encode($files);
            }

            if ($refTrackingTypeId == 4) {
                if ($is_do) {
                    $save["id_auth_user_request"]   = id_user();
                    $save["id_ref_status_approval"] = 1; //request
                    $save["remarks_do"]             = $post["remarks"];
                } else {
                    $save["remarks"]                = $post["remarks"];
                    $save["id_auth_user_request"]   = id_user();
                    $save["id_ref_status_approval"] = 2; //2 approve : 3 reject
                }
            }

            $project_id_final = !empty($data['project_id_final']) ? $data['project_id_final'] : '';
            if (isset($tracking["id"])) {
                $id = $this->Direct2db_model->update(
                    $save,
                    $tracking["id"],
                    'project_tracking',
                    'id',
                );
            } else {
                $id = $this->Direct2db_model->insert(
                    $save,
                    'project_tracking',
                );
            }
            log_tracking($id);
            log_project($projectTrId, "Update Tracking: $project_id_final");
            if ($refTrackingTypeId == 4) {
                $code = $is_do ? "RCT" : "CT";
            } else {
                $code = "UT";
            }

            $this->Direct2db_model->update(
                array(
                    'ref_project_status_code' => $code
                ),
                $projectTrId,
                'project_tr',
                'project_tr_id',
            );
            $ret["error"] = 0;

            if ($status=='Implemented' && $refTrackingTypeId!=2) {
                $this->project_tracking($projectTrId, $refTrackingTypeId+1);
            } else if ($status=='Completed' && $refTrackingTypeId!=4) {
                $this->project_tracking($projectTrId, $refTrackingTypeId+1, "Completed");
            }
            return $ret;
        }
    }

    function update_assign() {
        $data = array(
            array('project_tr_id' => 222, 'division_id' => 2054),
            array('project_tr_id' => 233, 'division_id' => 2054),
            array('project_tr_id' => 292, 'division_id' => 2054),
            array('project_tr_id' => 386, 'division_id' => 1022),
            array('project_tr_id' => 698, 'division_id' => 1008),
            array('project_tr_id' => 699, 'division_id' => 1008),
            array('project_tr_id' => 1064, 'division_id' => 1008),
            array('project_tr_id' => 1065, 'division_id' => 1008),
        );
        foreach ($data as $key => $value) {
            $id = $value['project_tr_id'];
            $id_division = $value['division_id'];

            // --------------- Get data for assign ---------------
            $data = $this->db->select('project_tr_id, project_tr_title, project_tr_desc,proposal_id, propose_total_funding,is_recurring_project')
            ->get_where('project_tr', array(
                'project_tr_id' => $id
            ))->row_array();
            $data['project_tr_id'] = isset($data['project_tr_id']) ? $data['project_tr_id']: '';
            $data['project_tr_title'] = isset($data['project_tr_title']) ? $data['project_tr_title']: '';
            $data['project_tr_desc'] = isset($data['project_tr_desc']) ? $data['project_tr_desc']: '';
            $data['proposal_id'] = isset($data['proposal_id']) ? $data['proposal_id']: '';

            if ($id_division) {
                $this->db->select('id_auth_user');
                $user_ids = $this->db->get_where('auth_user', array(
                    'struc_org_id' => $id_division,
                    'is_delete' => 0,
                ))->result_array();
            }

            if (isset($user_ids[0])) {
                $id_user = array_column($user_ids, 'id_auth_user');
                $do_user_id = $id_user[0];

                $countries = $this->db->select('country_name')
                ->join('proponent_org_mst b', 'b.proponent_org_id = a.proponent_org_id', 'LEFT')
                ->join('country_mst c', 'c.country_id = b.country_id', 'LEFT')
                ->get_where('proponent_org_tr a', array(
                    'a.project_trx_id' => $id,
                    'a.is_proponent' => 1,
                ))->result_array();
                if (count($countries) > 0) {
                    $countries = array_column($countries, 'country_name');
                }

                $merge_country = implode(', ', $countries);
                $replace['url']                     = base_url() . "project_proposal/view/$data[project_tr_id]";
                $replace['projectTitle']            = $data['project_tr_title'];
                $replace['proponent_country']       = $merge_country;
                $replace['projectDescription']      = $data['project_tr_desc'];
                $replace['proposal_funding_source'] = (isset($data['propose_total_funding'])) ? money_format_custom('%.2n', $data['propose_total_funding']) : money_format_custom('%.2n', 0);

                $this->load->helper('mail');
                $user_sess     = $this->session->userdata('ADM_SESS');
                $tpt_assign    = mail_tpt_new(['c.code' => 'ASSIGN'], $replace, $user_sess, 'assign');
                $email_content = !empty($tpt_assign) ? $tpt_assign['content'] : '';
                $do_user = $this->db->select('username')
                ->get_where('view_users_roles a', array(
                    'id_auth_user' => $do_user_id,
                ))->row_array();

                if ($do_user) {
                    $email_content = str_replace('{userName}', $do_user['username'], $email_content);
                }
                $email_content = str_replace('{project_tr_title}', $data['project_tr_title'], $email_content);
                $email_content = str_replace('{proposal_id}', $data['proposal_id'], $email_content);

                if ($data['is_recurring_project'] == 1) {
                    $email_content = str_replace('{is_recurring}', 'Yes', $email_content);
                } else {
                    $email_content = str_replace('{is_recurring}', 'No', $email_content);
                }

                // --------------- End Get data for assign ---------------
                echo "UPDATE project_tr SET id_ref_status_approval = 0, id_auth_user_assign = '$do_user_id', struc_org_id_assign = '$id_division' WHERE project_tr_id = $id; <br>";
                // echo "UPDATE project_tr SET id_ref_status_approval = 0, id_auth_user_assign = '$do_user_id', struc_org_id_assign = '$id_division', assign_message = '$email_content', assign_message_cc = '$email_content' WHERE project_tr_id = $id; <br>";
            } else {
                echo "UPDATE project_tr SET id_ref_status_approval = 0, struc_org_id_assign = '$id_division' WHERE project_tr_id = $id; <br>";
            }
        }
        // print_r('');exit;
    }

    function fix_missing_step_review()
    {
        echo "<pre>";

        $result = [];
        $broken_datas = array(
            array('project_tr_id' => 2489, 'proposal_id' => 'SEM/SEM/21/001/REG', 'origin_funding_source_id' => 1051,'copy_funding_step_review_from_id' => null, 'new_funding_source_id' => 1051, 'status_scdc' => 'Ongoing'),
            array('project_tr_id' => 3687, 'proposal_id' => 'ASC/ASC/15/002/REG', 'origin_funding_source_id' => 2081,'copy_funding_step_review_from_id' => null, 'new_funding_source_id' => 1024, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4478, 'proposal_id' => 'TRC/MON/16/008/REG', 'origin_funding_source_id' => 2072,'copy_funding_step_review_from_id' => 1051, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4483, 'proposal_id' => 'TRC/SCR/16/007/REG', 'origin_funding_source_id' => 1050,'copy_funding_step_review_from_id' => null, 'new_funding_source_id' => 1051, 'status_scdc' => 'Ongoing'),
            array('project_tr_id' => 4466, 'proposal_id' => 'TRC/CYB/14/002/REG', 'origin_funding_source_id' => 2073,'copy_funding_step_review_from_id' => 1051, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4107, 'proposal_id' => 'GCP/GEN/19/005/REG', 'origin_funding_source_id' => 2090,'copy_funding_step_review_from_id' => null, 'new_funding_source_id' => 1059, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4171, 'proposal_id' => 'n/a', 'origin_funding_source_id' => 2075,'copy_funding_step_review_from_id' => 1019, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4172, 'proposal_id' => 'n/a', 'origin_funding_source_id' => 2075,'copy_funding_step_review_from_id' => 1019, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4173, 'proposal_id' => 'n/a', 'origin_funding_source_id' => 2075,'copy_funding_step_review_from_id' => 1019, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4417, 'proposal_id' => 'SCT/FST/12/008/REG', 'origin_funding_source_id' => 2075,'copy_funding_step_review_from_id' => 1019, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4424, 'proposal_id' => 'SCT/INF/13/0013/REG', 'origin_funding_source_id' => 2079,'copy_funding_step_review_from_id' => 1024, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4427, 'proposal_id' => 'SCT/MST/10/002/REG', 'origin_funding_source_id' => 2085,'copy_funding_step_review_from_id' => null, 'new_funding_source_id' => 1023, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 3625, 'proposal_id' => 'AGF/LIV/13/0010/REG', 'origin_funding_source_id' => 2071,'copy_funding_step_review_from_id' => 1056, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4148, 'proposal_id' => 'INF/ENR/13/001/REG', 'origin_funding_source_id' => 2078,'copy_funding_step_review_from_id' => null, 'new_funding_source_id' => 1025, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4136, 'proposal_id' => 'IND/SME/14/002/REG', 'origin_funding_source_id' => 2080,'copy_funding_step_review_from_id' => null, 'new_funding_source_id' => 1037, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4139, 'proposal_id' => 'IND/SME/16/003/REG', 'origin_funding_source_id' => 2080,'copy_funding_step_review_from_id' => null, 'new_funding_source_id' => 1037, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4143, 'proposal_id' => 'IND/SME/16/007/REG', 'origin_funding_source_id' => 2078,'copy_funding_step_review_from_id' => null, 'new_funding_source_id' => 1025, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4144, 'proposal_id' => 'IND/SME/17/001/REG', 'origin_funding_source_id' => 2074,'copy_funding_step_review_from_id' => null, 'new_funding_source_id' => 2061, 'status_scdc' => 'Ongoing'),
            array('project_tr_id' => 4059, 'proposal_id' => 'FIN/BON/09/002/JAF', 'origin_funding_source_id' => 2077,'copy_funding_step_review_from_id' => 1024, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4060, 'proposal_id' => 'FIN/BON/09/003/JAF', 'origin_funding_source_id' => 2077,'copy_funding_step_review_from_id' => 1024, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4048, 'proposal_id' => 'ETF/ICT/13/004/REG', 'origin_funding_source_id' => 2078,'copy_funding_step_review_from_id' => null, 'new_funding_source_id' => 1025, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4049, 'proposal_id' => 'ETF/ICT/14/002/REG', 'origin_funding_source_id' => 2078,'copy_funding_step_review_from_id' => null, 'new_funding_source_id' => 1025, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4050, 'proposal_id' => 'ETF/ICT/14/003/REG', 'origin_funding_source_id' => 2080,'copy_funding_step_review_from_id' => null, 'new_funding_source_id' => 1037, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4062, 'proposal_id' => 'FIN/BON/09/005/JAF', 'origin_funding_source_id' => 2077,'copy_funding_step_review_from_id' => 1024, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4061, 'proposal_id' => 'FIN/BON/09/004/JAF', 'origin_funding_source_id' => 2077,'copy_funding_step_review_from_id' => 1024, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4115, 'proposal_id' => 'GEN/MLT/13/002/REG', 'origin_funding_source_id' => 2079,'copy_funding_step_review_from_id' => 1024, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4093, 'proposal_id' => 'FIN/STU/14/001/JAF', 'origin_funding_source_id' => 2077,'copy_funding_step_review_from_id' => 1024, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4094, 'proposal_id' => 'FIN/STU/14/001/JAF', 'origin_funding_source_id' => 2077,'copy_funding_step_review_from_id' => 1024, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 3613, 'proposal_id' => 'AGF/FIS/17/011/REG', 'origin_funding_source_id' => 2080,'copy_funding_step_review_from_id' => null, 'new_funding_source_id' => 1037, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 3615, 'proposal_id' => 'AGF/FOR/17/001/REG', 'origin_funding_source_id' => 2078,'copy_funding_step_review_from_id' => null, 'new_funding_source_id' => 1025, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 3619, 'proposal_id' => 'AGF/FOS/13/0013/REG', 'origin_funding_source_id' => 2071,'copy_funding_step_review_from_id' => 1056, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 3622, 'proposal_id' => 'AGF/FR/13/001/REG', 'origin_funding_source_id' => 2078,'copy_funding_step_review_from_id' => null, 'new_funding_source_id' => 1025, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 3591, 'proposal_id' => 'AGF/AGR/15/011/REG', 'origin_funding_source_id' => 2078,'copy_funding_step_review_from_id' => null, 'new_funding_source_id' => 1025, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 3594, 'proposal_id' => 'AGF/AGR/17/009/REG', 'origin_funding_source_id' => 2078,'copy_funding_step_review_from_id' => null, 'new_funding_source_id' => 1025, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4428, 'proposal_id' => 'SCT/MST/13/004/REG', 'origin_funding_source_id' => 2085,'copy_funding_step_review_from_id' => null, 'new_funding_source_id' => 1023, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4525, 'proposal_id' => 'TRN/FAC/12/006/REG', 'origin_funding_source_id' => 2078,'copy_funding_step_review_from_id' => null, 'new_funding_source_id' => 1025, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4083, 'proposal_id' => 'FIN/STU/09/009/JAF', 'origin_funding_source_id' => 2077,'copy_funding_step_review_from_id' => 1024, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4132, 'proposal_id' => 'IND/SME/12/008/REG', 'origin_funding_source_id' => 2078,'copy_funding_step_review_from_id' => null, 'new_funding_source_id' => 1025, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4087, 'proposal_id' => 'FIN/STU/12/004/JAF', 'origin_funding_source_id' => 2077,'copy_funding_step_review_from_id' => 1024, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4460, 'proposal_id' => 'TRA/STA/19/003/REG', 'origin_funding_source_id' => 2080,'copy_funding_step_review_from_id' => null, 'new_funding_source_id' => 1037, 'status_scdc' => 'Ongoing'),
            array('project_tr_id' => 4463, 'proposal_id' => 'TRA/TRA/19/004/REG', 'origin_funding_source_id' => 2080,'copy_funding_step_review_from_id' => null, 'new_funding_source_id' => 1037, 'status_scdc' => 'Ongoing'),
            array('project_tr_id' => 4079, 'proposal_id' => 'FIN/STU/09/0012/JAF', 'origin_funding_source_id' => 2077,'copy_funding_step_review_from_id' => 1024, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4072, 'proposal_id' => 'FIN/FNC/10/001/JAF', 'origin_funding_source_id' => 2077,'copy_funding_step_review_from_id' => 1024, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4073, 'proposal_id' => 'FIN/FNC/10/002/JAF', 'origin_funding_source_id' => 2077,'copy_funding_step_review_from_id' => 1024, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4074, 'proposal_id' => 'FIN/FNC/11/001/JAF', 'origin_funding_source_id' => 2077,'copy_funding_step_review_from_id' => 1024, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4075, 'proposal_id' => 'FIN/FNC/12/001/JAF', 'origin_funding_source_id' => 2077,'copy_funding_step_review_from_id' => 1024, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4076, 'proposal_id' => 'FIN/FNC/13/001/JAF', 'origin_funding_source_id' => 2077,'copy_funding_step_review_from_id' => 1024, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4011, 'proposal_id' => 'ENV/EVN/14/003/REG', 'origin_funding_source_id' => 2078,'copy_funding_step_review_from_id' => null, 'new_funding_source_id' => 1025, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4069, 'proposal_id' => 'FIN/BON/13/005/JAF', 'origin_funding_source_id' => 2077,'copy_funding_step_review_from_id' => 1024, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4070, 'proposal_id' => 'FIN/BON/14/001/JAF', 'origin_funding_source_id' => 2077,'copy_funding_step_review_from_id' => 1024, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4071, 'proposal_id' => 'FIN/BON/14/002/JAF', 'origin_funding_source_id' => 2077,'copy_funding_step_review_from_id' => 1024, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4063, 'proposal_id' => 'FIN/BON/09/006/JAF', 'origin_funding_source_id' => 2077,'copy_funding_step_review_from_id' => 1024, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 3601, 'proposal_id' => 'AGF/BIO/11/003/REG', 'origin_funding_source_id' => 2087,'copy_funding_step_review_from_id' => 1056, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 3606, 'proposal_id' => 'AGF/CRO/11/007/REG', 'origin_funding_source_id' => 2080,'copy_funding_step_review_from_id' => null, 'new_funding_source_id' => 1037, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 3578, 'proposal_id' => 'AGF/AGR/12/0017/REG', 'origin_funding_source_id' => 2078,'copy_funding_step_review_from_id' => null, 'new_funding_source_id' => 1025, 'status_scdc' => 'Implemented'),
            array('project_tr_id' => 3582, 'proposal_id' => 'AGF/AGR/12/008/REG', 'origin_funding_source_id' => 2080,'copy_funding_step_review_from_id' => null, 'new_funding_source_id' => 1037, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4066, 'proposal_id' => 'FIN/BON/13/002/JAF', 'origin_funding_source_id' => 2077,'copy_funding_step_review_from_id' => 1024, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4067, 'proposal_id' => 'FIN/BON/13/003/JAF', 'origin_funding_source_id' => 2077,'copy_funding_step_review_from_id' => 1024, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4068, 'proposal_id' => 'FIN/BON/13/004/JAF', 'origin_funding_source_id' => 2077,'copy_funding_step_review_from_id' => 1024, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 3599, 'proposal_id' => 'AGF/ARD/17/010/REG', 'origin_funding_source_id' => 2080,'copy_funding_step_review_from_id' => null, 'new_funding_source_id' => 1037, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4064, 'proposal_id' => 'FIN/BON/09/008/JAF', 'origin_funding_source_id' => 2077,'copy_funding_step_review_from_id' => 1024, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4065, 'proposal_id' => 'FIN/BON/13/001/JAF', 'origin_funding_source_id' => 2077,'copy_funding_step_review_from_id' => 1024, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 3984, 'proposal_id' => 'ENV/DIS/17/014/REG', 'origin_funding_source_id' => 2089,'copy_funding_step_review_from_id' => 1056, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4362, 'proposal_id' => 'SCD/YOU/13/0028/REG', 'origin_funding_source_id' => 2079,'copy_funding_step_review_from_id' => 1024, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4364, 'proposal_id' => 'SCD/YOU/13/0030/REG', 'origin_funding_source_id' => 2079,'copy_funding_step_review_from_id' => 1024, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4365, 'proposal_id' => 'SCD/YOU/13/0032/REG', 'origin_funding_source_id' => 2079,'copy_funding_step_review_from_id' => 1024, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4286, 'proposal_id' => 'SCD/SPO/13/0031/REG', 'origin_funding_source_id' => 2079,'copy_funding_step_review_from_id' => 1024, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4280, 'proposal_id' => 'SCD/POV/19/036/REG', 'origin_funding_source_id' => 2090,'copy_funding_step_review_from_id' => null, 'new_funding_source_id' => 1059, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 3769, 'proposal_id' => 'CIN/CUL/13/0036/REG', 'origin_funding_source_id' => 2080,'copy_funding_step_review_from_id' => null, 'new_funding_source_id' => 1037, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 3771, 'proposal_id' => 'CIN/CUL/13/0038/REG', 'origin_funding_source_id' => 2079,'copy_funding_step_review_from_id' => 1024, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4259, 'proposal_id' => 'SCD/LAB/17/023/REG', 'origin_funding_source_id' => 2074,'copy_funding_step_review_from_id' => null, 'new_funding_source_id' => 2061, 'status_scdc' => 'Ongoing'),
            array('project_tr_id' => 3887, 'proposal_id' => 'CIN/MED/14/0010/REG', 'origin_funding_source_id' => 2079,'copy_funding_step_review_from_id' => 1024, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4198, 'proposal_id' => 'SCD/EDU/10/003/REG', 'origin_funding_source_id' => 2070,'copy_funding_step_review_from_id' => 1051, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 3728, 'proposal_id' => 'CIN/CUL/10/0021/REG', 'origin_funding_source_id' => 2085,'copy_funding_step_review_from_id' => null, 'new_funding_source_id' => 1023, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 3966, 'proposal_id' => 'ENV/DIS/12/0019/REG', 'origin_funding_source_id' => 2088,'copy_funding_step_review_from_id' => 1056, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4226, 'proposal_id' => 'SCD/EDU/17/012/REG', 'origin_funding_source_id' => 2080,'copy_funding_step_review_from_id' => null, 'new_funding_source_id' => 1037, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4249, 'proposal_id' => 'SCD/LAB/11/0026/REG', 'origin_funding_source_id' => 2082,'copy_funding_step_review_from_id' => 1024, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4242, 'proposal_id' => 'SCD/HEA/15/022/REG', 'origin_funding_source_id' => 2083,'copy_funding_step_review_from_id' => null, 'new_funding_source_id' => 1057, 'status_scdc' => 'Ongoing'),
            array('project_tr_id' => 4247, 'proposal_id' => 'SCD/LAB/10/004/REG', 'origin_funding_source_id' => 2084,'copy_funding_step_review_from_id' => 1051, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4041, 'proposal_id' => 'ENV/HAZ/17/011/REG', 'origin_funding_source_id' => 1055,'copy_funding_step_review_from_id' => 1056, 'new_funding_source_id' => null, 'status_scdc' => 'Ongoing'),
            array('project_tr_id' => 4300, 'proposal_id' => 'SCD/SWD/11/0032/REG', 'origin_funding_source_id' => 2080,'copy_funding_step_review_from_id' => null, 'new_funding_source_id' => 1037, 'status_scdc' => 'Ongoing'),
            array('project_tr_id' => 4306, 'proposal_id' => 'SCD/SWD/12/0031/REG', 'origin_funding_source_id' => 2080,'copy_funding_step_review_from_id' => null, 'new_funding_source_id' => 1037, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4095, 'proposal_id' => 'FIN/STU/14/002/JAF', 'origin_funding_source_id' => 2077,'copy_funding_step_review_from_id' => 1024, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4096, 'proposal_id' => 'FIN/STU/14/003/JAF', 'origin_funding_source_id' => 2077,'copy_funding_step_review_from_id' => 1024, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4092, 'proposal_id' => 'FIN/STU/13/004/JAF', 'origin_funding_source_id' => 2077,'copy_funding_step_review_from_id' => 1024, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4085, 'proposal_id' => 'FIN/STU/12/002/JAF', 'origin_funding_source_id' => 2077,'copy_funding_step_review_from_id' => 1024, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4086, 'proposal_id' => 'FIN/STU/12/003/JAF', 'origin_funding_source_id' => 2077,'copy_funding_step_review_from_id' => 1024, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4091, 'proposal_id' => 'FIN/STU/13/003/JAF', 'origin_funding_source_id' => 2077,'copy_funding_step_review_from_id' => 1024, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4081, 'proposal_id' => 'FIN/STU/09/0014/JAF', 'origin_funding_source_id' => 2077,'copy_funding_step_review_from_id' => 1024, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4082, 'proposal_id' => 'FIN/STU/09/0015/JAF', 'origin_funding_source_id' => 2077,'copy_funding_step_review_from_id' => 1024, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4084, 'proposal_id' => 'FIN/STU/12/001/JAF', 'origin_funding_source_id' => 2077,'copy_funding_step_review_from_id' => 1024, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4080, 'proposal_id' => 'FIN/STU/09/0013/JAF', 'origin_funding_source_id' => 2077,'copy_funding_step_review_from_id' => 1024, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4088, 'proposal_id' => 'FIN/STU/12/005/JAF', 'origin_funding_source_id' => 2077,'copy_funding_step_review_from_id' => 1024, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4089, 'proposal_id' => 'FIN/STU/13/001/JAF', 'origin_funding_source_id' => 2077,'copy_funding_step_review_from_id' => 1024, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4090, 'proposal_id' => 'FIN/STU/13/002/JAF', 'origin_funding_source_id' => 2077,'copy_funding_step_review_from_id' => 1024, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4077, 'proposal_id' => 'FIN/STU/09/0010/JAF', 'origin_funding_source_id' => 2077,'copy_funding_step_review_from_id' => 1024, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
            array('project_tr_id' => 4078, 'proposal_id' => 'FIN/STU/09/0011/JAF', 'origin_funding_source_id' => 2077,'copy_funding_step_review_from_id' => 1024, 'new_funding_source_id' => null, 'status_scdc' => 'Completed'),
        );
        $tracking_steps = array(
            1 => 'Funding Disbursement',
            2 => 'Project Implementation',
            3 => 'Completion Report Submission',
            4 => 'Project Closing',
        );

        $review_steps = array(
            1 => 'PAM',
            2 => 'PAC',
            3 => 'SB',
            4 => 'CPR',
            5 => 'DP',
            6 => 'Post CPR',
        );

        // Reformulate step review for new funding sources
        foreach ($broken_datas as $key => $value) {
            if (!empty($value['copy_funding_step_review_from_id'])) {
                $reformulate_step_review[$value['copy_funding_step_review_from_id']]['copy_funding_step_review_from_id'] = $value['copy_funding_step_review_from_id'];
                $reformulate_step_review[$value['copy_funding_step_review_from_id']]['origin_funding_source_id'][$value['origin_funding_source_id']] = $value['origin_funding_source_id'];
            }
        }

        foreach ($reformulate_step_review as $key => $value) {
            $review_step_mapping_clone = $this->db->get_where('project_review_step', array(
                'funding_source_id' => $value['copy_funding_step_review_from_id'],
                'is_delete' => 0,
            ))->result_array();

            foreach($value['origin_funding_source_id'] as $key2 => $origin_funding_source_id) {
                foreach ($review_step_mapping_clone as $key3 => $value3) {
                    $project_review_step_mapping_id = $this->Direct2db_model->insert(array(
                        'is_first_project' => $value3['is_first_project'],
                        'is_budget_over_100k' => $value3['is_budget_over_100k'],
                        'funding_source_id' => $origin_funding_source_id,
                    ), 'project_review_step');

                    if ($project_review_step_mapping_id) {
                        $project_review_step_detail = $this->db->get_where('project_review_step_detail', array(
                            'project_review_step_id' => $value3['id'],
                            'is_delete' => 0,
                        ))->result_array();
                        foreach ($project_review_step_detail as $key4 => $value4) {
                            $insert_detail_step_review[$key4] = array(
                                'project_review_step_id' => $project_review_step_mapping_id,
                                'ref_project_review_id' => $value4['ref_project_review_id'],
                                'is_delete' => 0,
                            );
                        }
                        $this->Direct2db_model->insert_batch($insert_detail_step_review, 'project_review_step_detail');
                    }
                }
            }
        }
        // End reformulate step review for new funding sources

        foreach ($broken_datas as $key => $value) {
            $tr_id = $value['project_tr_id'];
            $this->Direct2db_model->update(array('is_complete' => 1), $tr_id, 'project_tr', 'project_tr_id');

            // Update funding source
            if (!empty($value['new_funding_source_id'])) {
                $update_funding_source = array(
                    'funding_source_id' => $value['new_funding_source_id'],
                );
                $this->Direct2db_model->update($update_funding_source, $tr_id, 'project_funding_source_tr', 'project_trx_id');
            }

            // Generate step review
            $this->project_review_step($tr_id, 'save');

            // Inject and update position review
            $time_start = microtime(true);
            $review_step = $this->get_step_review($tr_id);
            if ($review_step) {
                $current_proposal_status = $value['status_scdc'];
                $result[$value['proposal_id'].'_'.$value['project_tr_id']]['id']              = $tr_id;
                $result[$value['proposal_id'].'_'.$value['project_tr_id']]['proposal_id']     = $value['proposal_id'];
                $result[$value['proposal_id'].'_'.$value['project_tr_id']]['step_exists']     = 'true';
                // Proposal review
                switch($current_proposal_status) {
                    case 'Completed':
                        $result[$value['proposal_id'].'_'.$value['project_tr_id']]['id']              = $tr_id;
                        $result[$value['proposal_id'].'_'.$value['project_tr_id']]['proposal_id']     = $value['proposal_id'];
                        foreach ($review_step as $k_step => $step) {
                            $review_result = $this->finish($tr_id, null);
                            $result[$value['proposal_id'].'_'.$value['project_tr_id']]['review_steps'][$k_step]['step']          = $step;
                            $result[$value['proposal_id'].'_'.$value['project_tr_id']]['review_steps'][$k_step]['file_uploaded'] = $review_result['total_files'];
                        }
                        foreach ($tracking_steps as $k_step => $step) {
                            $step_code = str_replace(' ', '_', strtolower($step));
                            $tracking_result = $this->project_tracking($tr_id, $k_step, $current_proposal_status);
                            $result[$value['proposal_id'].'_'.$value['project_tr_id']]['tracking_steps'][$k_step]['step']          = $step;
                            $result[$value['proposal_id'].'_'.$value['project_tr_id']]['tracking_steps'][$k_step]['file_uploaded'] = $tracking_result['total_files'];
                        }

                        break;
                    case 'Implemented':
                        $result[$value['proposal_id'].'_'.$value['project_tr_id']]['id']              = $tr_id;
                        $result[$value['proposal_id'].'_'.$value['project_tr_id']]['proposal_id']     = $value['proposal_id'];
                        foreach ($review_step as $k_step => $step) {
                            $review_result = $this->finish($tr_id, null);
                            $result[$value['proposal_id'].'_'.$value['project_tr_id']]['review_steps'][$k_step]['step']          = $step;
                            $result[$value['proposal_id'].'_'.$value['project_tr_id']]['review_steps'][$k_step]['file_uploaded'] = $review_result['total_files'];
                        }

                        foreach ($tracking_steps as $k_step => $step) {
                            $tracking_result = $this->project_tracking($tr_id, $k_step, $current_proposal_status);
                            $result[$value['proposal_id'].'_'.$value['project_tr_id']]['tracking_steps'][$k_step]['step']          = $step;
                            $result[$value['proposal_id'].'_'.$value['project_tr_id']]['tracking_steps'][$k_step]['file_uploaded'] = $tracking_result['total_files'];

                            // Hanya jalankan sampai step ke 2
                            if ($k_step == 2) {
                                break;
                            }
                        }

                        break;
                    case 'Ongoing':
                        $result[$value['proposal_id'].'_'.$value['project_tr_id']]['id']              = $tr_id;
                        $result[$value['proposal_id'].'_'.$value['project_tr_id']]['proposal_id']     = $value['proposal_id'];
                        foreach ($review_step as $k_step => $step) {
                            $review_result = $this->finish($tr_id, null);
                            $result[$value['proposal_id'].'_'.$value['project_tr_id']]['review_steps'][$k_step]['step']          = $step;
                            $result[$value['proposal_id'].'_'.$value['project_tr_id']]['review_steps'][$k_step]['file_uploaded'] = $review_result['total_files'];
                        }

                        foreach ($tracking_steps as $k_step => $step) {
                            $tracking_result = $this->project_tracking($tr_id, $k_step, $current_proposal_status);
                            $result[$value['proposal_id'].'_'.$value['project_tr_id']]['tracking_steps'][$k_step]['step']          = $step;
                            $result[$value['proposal_id'].'_'.$value['project_tr_id']]['tracking_steps'][$k_step]['file_uploaded'] = $tracking_result['total_files'];

                            // Hanya jalankan step ke 1
                            if ($k_step == 1) {
                                break;
                            }
                        }

                        break;
                    case 'Pending ASEC':
                        $result[$value['proposal_id'].'_'.$value['project_tr_id']]['id']              = $tr_id;
                        $result[$value['proposal_id'].'_'.$value['project_tr_id']]['proposal_id']     = $value['proposal_id'];
                        $max_step = 5;
                        if (in_array('DP', $review_step)) {
                            $max_step = 5;
                        } else if (in_array('CPR', $review_step)) {
                            $max_step = 4;
                        } else if (in_array('PAC', $review_step)) {
                            $max_step = 2;
                        }
                        foreach ($review_step as $k_step => $step) {
                            if (array_search($step, $review_steps) < $max_step) {
                                // Jika bukan step terakhir, finish
                                $review_result = $this->finish($tr_id, null);
                                $result[$value['proposal_id'].'_'.$value['project_tr_id']]['review_steps'][$k_step]['step']          = $step;
                                $result[$value['proposal_id'].'_'.$value['project_tr_id']]['review_steps'][$k_step]['process']       = 'finish';
                                $result[$value['proposal_id'].'_'.$value['project_tr_id']]['review_steps'][$k_step]['file_uploaded'] = $review_result['total_files'];
                            } else {
                                // Stop di step PAC
                                $result[$value['proposal_id'].'_'.$value['project_tr_id']]['review_steps'][$k_step]['step']          = $step;
                                $result[$value['proposal_id'].'_'.$value['project_tr_id']]['review_steps'][$k_step]['process']       = '-';
                                $result[$value['proposal_id'].'_'.$value['project_tr_id']]['review_steps'][$k_step]['file_uploaded'] = 0;
                                break;
                            }
                        }

                        $this->set_flag($value['position'], 'Berhasil');
                        break;
                    case 'Pending CPR':
                        $result[$value['proposal_id'].'_'.$value['project_tr_id']]['id']              = $tr_id;
                        $result[$value['proposal_id'].'_'.$value['project_tr_id']]['proposal_id']     = $value['proposal_id'];
                        foreach ($review_step as $k_step => $step) {
                            if (array_search($step, $review_steps) < 4) {
                                // Jika bukan step terakhir, finish
                                $review_result = $this->finish($tr_id, null);
                                $result[$value['proposal_id'].'_'.$value['project_tr_id']]['review_steps'][$k_step]['step']          = $step;
                                $result[$value['proposal_id'].'_'.$value['project_tr_id']]['review_steps'][$k_step]['process']       = 'finish';
                                $result[$value['proposal_id'].'_'.$value['project_tr_id']]['review_steps'][$k_step]['file_uploaded'] = $review_result['total_files'];
                            } else {
                                $result[$value['proposal_id'].'_'.$value['project_tr_id']]['review_steps'][$k_step]['step']          = $step;
                                $result[$value['proposal_id'].'_'.$value['project_tr_id']]['review_steps'][$k_step]['process']       = '-';
                                $result[$value['proposal_id'].'_'.$value['project_tr_id']]['review_steps'][$k_step]['file_uploaded'] = 0;
                                break;
                            }
                        }
                        break;
                    case 'Pending DP':
                        $result[$value['proposal_id'].'_'.$value['project_tr_id']]['id']              = $tr_id;
                        $result[$value['proposal_id'].'_'.$value['project_tr_id']]['proposal_id']     = $value['proposal_id'];
                        foreach ($review_step as $k_step => $step) {
                            if (array_search($step, $review_steps) < 5) {
                                // Jika bukan step terakhir, finish
                                $review_result = $this->finish($tr_id, null);
                                $result[$value['proposal_id'].'_'.$value['project_tr_id']]['review_steps'][$k_step]['step']          = $step;
                                $result[$value['proposal_id'].'_'.$value['project_tr_id']]['review_steps'][$k_step]['process']       = 'finish';
                                $result[$value['proposal_id'].'_'.$value['project_tr_id']]['review_steps'][$k_step]['file_uploaded'] = $review_result['total_files'];
                            } else {
                                // Stop di step DP
                                $result[$value['proposal_id'].'_'.$value['project_tr_id']]['review_steps'][$k_step]['step']          = $step;
                                $result[$value['proposal_id'].'_'.$value['project_tr_id']]['review_steps'][$k_step]['process']       = '-';
                                $result[$value['proposal_id'].'_'.$value['project_tr_id']]['review_steps'][$k_step]['file_uploaded'] = '0';
                                break;
                            }
                        }
                        break;
                    case 'Pending Sponsoring Body':
                        $result[$value['proposal_id'].'_'.$value['project_tr_id']]['id']              = $tr_id;
                        $result[$value['proposal_id'].'_'.$value['project_tr_id']]['proposal_id']     = $value['proposal_id'];
                        foreach ($review_step as $k_step => $step) {
                            if (array_search($step, $review_steps) < 3) {
                                // Jika bukan step terakhir, finish
                                $review_result = $this->finish($tr_id, null);
                                $result[$value['proposal_id'].'_'.$value['project_tr_id']]['review_steps'][$k_step]['step']          = $step;
                                $result[$value['proposal_id'].'_'.$value['project_tr_id']]['review_steps'][$k_step]['process']       = 'finish';
                                $result[$value['proposal_id'].'_'.$value['project_tr_id']]['review_steps'][$k_step]['file_uploaded'] = $review_result['total_files'];
                            } else {
                                // Hold di step SB
                                $result[$value['proposal_id'].'_'.$value['project_tr_id']]['review_steps'][$k_step]['step']          = $step;
                                $result[$value['proposal_id'].'_'.$value['project_tr_id']]['review_steps'][$k_step]['process']       = '-';
                                $result[$value['proposal_id'].'_'.$value['project_tr_id']]['review_steps'][$k_step]['file_uploaded'] = 0;
                                break;
                            }
                        }

                        break;
                }
            } else {
                $result[$value['proposal_id'].'_'.$value['project_tr_id']]['id']              = $tr_id;
                $result[$value['proposal_id'].'_'.$value['project_tr_id']]['proposal_id']     = $value['proposal_id'];
                $result[$value['proposal_id'].'_'.$value['project_tr_id']]['step_exists']     = 'false';
            }
        }

        echo "<pre>";
        print_r($result);exit;
    }

    function update_files(){
        echo '<pre>';
        $broken_datas = array(
            array('project_tr_id' => 3687, 'ref_project_review_id' => 2, 'files' => '{"1":{"name":"Clarification Note - AIPR Forum on Women Participation in Peace Process PAM PAC.docx","category":4},"2":{"name":"Memo PAC C (004).pdf","category":4}}'),
            array('project_tr_id' => 4466, 'ref_project_review_id' => 4, 'files' => '{"1":{"name":"Letter to CPR (028). 7 April 2014.pdf","category":4}}'),
            array('project_tr_id' => 4417, 'ref_project_review_id' => 4, 'files' => '{"1":{"name":"ASEAN Project for ASEAN Food Conference-India.for CPR.PDF","category":4},"2":{"name":"CPR Endorsement 149.2nov12.pdf","category":4},"3":{"name":"Letter CPR 140.11 oct12.pdf","category":4},"4":{"name":"matrix compilation.S_T projects.1nov12.docx","category":4}}'),
            array('project_tr_id' => 4424, 'ref_project_review_id' => 2, 'files' => '{"1":{"name":"JENESYS 2.0 Science _ Technology.for PAC.pdf","category":4},"2":{"name":"Memo to PAC D (113).pdf","category":4},"3":{"name":"Microsoft Outlook - Memo Style.pdf","category":4}}'),
            array('project_tr_id' => 4424, 'ref_project_review_id' => 4, 'files' => '{"1":{"name":"CPR Endoresement (005). ER2.J. 17 January 2014.pdf","category":4},"3":{"name":"Letter to CPR (195). 31 Dec. 2013.pdf","category":4},"4":{"name":"matrix compilation. JENESYS 2.0\u201dScience and Technology (005).pdf","category":4}}'),
            array('project_tr_id' => 4148, 'ref_project_review_id' => 4, 'files' => '{"1":{"name":"CPR Endorsement (096). ER2.30 July 2013.pdf","category":4},"2":{"name":"Letter to CPR (078). 12 July 2013.pdf","category":4},"3":{"name":"matrix compilation.Rural Renewable Energy (Biogas) Development for ASEAN Countries( 096).pdf","category":4},"4":{"name":"Prop RAC_ASEAN_Biogas Project.endorsed.pdf","category":4}}'),
            array('project_tr_id' => 4136, 'ref_project_review_id' => 2, 'files' => '{"1":{"name":"6. APEN_JAIF_PROJECTPROPOSAL_ATTACHMENT1_WORKPLAN_27JUN2013.xls","category":4},"2":{"name":"7. APEN_JAIF_PROJECTPROPOSAL_ATTACHMENT2_TORFORPERSONNEL_27JUN2013.doc","category":4},"3":{"name":"8. APEN_JAIF_PROJECTPROPOSAL_ATTACHMENT3_TORFORCOMMITTEE_WG_27JUN2013.doc","category":4},"4":{"name":"9. APEN_JAIF_PROJECTPROPOSAL_ATTACHMENT4_BUDGET_27JUN2013.xls","category":4},"6":{"name":"CLARIFICATION NOTES CI-HRD-PAM PAC.docx","category":4},"7":{"name":"Comprehensive Industrial Human Resource Development.for PAC.pdf","category":4},"8":{"name":"Matrix of PAC D ACTIONS comments for CI-HRD.docx","category":4},"9":{"name":"Memo PAC D (030).pdf","category":4},"10":{"name":"Memo PAC D Actions Comments (031).pdf","category":4},"11":{"name":"Microsoft Outlook - Memo Style.pdf","category":4}}'),
            array('project_tr_id' => 4136, 'ref_project_review_id' => 4, 'files' => '{"1":{"name":"Comprehensive Industrial Human Resource Development. endorsed..pdf","category":4},"3":{"name":"CPR Endorsement (008). ER2.J. 5 March 2015.pdf","category":4},"4":{"name":"Letter to CPR (007). 18 February 2015.pdf","category":4},"5":{"name":"matrix compilation. Comprehensive Industrial Human Resource Development (Ci-HRD) (008).pdf","category":4}}'),
            array('project_tr_id' => 4144, 'ref_project_review_id' => 4, 'files' => '{"1":{"name":"ASEAN Cooperation Project Proposal - Canada OECD Project on ASEAN SMEs (COPAS)_Endorsed14June2017.pdf","category":4},"2":{"name":"Matrix of CPR Comments and Clarification_14June2017.pdf","category":4},"3":{"name":"Memo re CPR Ad Ref Approval (052) COPAS_14June2017.pdf","category":4}}'),
            array('project_tr_id' => 4048, 'ref_project_review_id' => 4, 'files' => '{"1":{"name":"AJ-SCEC 6.pdf","category":4},"2":{"name":"AJCEP JC 9.pdf","category":4},"3":{"name":"CPR Endorsement (080). ER2.17 July 2013.pdf","category":4},"5":{"name":"Letter to CPR (068). 27 June 2013.pdf","category":4},"6":{"name":"matrix compilation.Study on the Development of ASEAN ICT Skill Standards (080).pdf","category":4},"8":{"name":"Study on the Development of ASEAN ICT Skill Standards.proposal.pdf","category":4}}'),
            array('project_tr_id' => 4049, 'ref_project_review_id' => 4, 'files' => '{"1":{"name":"CPR Endorsement (045). ER2.J. 5 June 2014.pdf","category":4},"2":{"name":"Letter to CPR (036). 9 May 2014.docx.pdf","category":4}}'),
            array('project_tr_id' => 4050, 'ref_project_review_id' => 2, 'files' => '{"2":{"name":"Feasibility Study for Practical Use ASEAN Smart Network.for PAC.pdf","category":4},"3":{"name":"Memo PAC B (032).pdf","category":4}}'),
            array('project_tr_id' => 4050, 'ref_project_review_id' => 4, 'files' => '{"1":{"name":"CPR Endorsement (065). ER2.J. 25 August 2014.pdf","category":4},"2":{"name":"Feasibility Study for Practical Use ASEAN Smart Network.endorsed.pdf","category":4},"4":{"name":"Letter to CPR (055). 7 August 2014.pdf","category":4},"5":{"name":"matrix compilation. Practical Use and Sophistication of ICT(ASEAN Smart Network).pdf","category":4}}'),
            array('project_tr_id' => 4115, 'ref_project_review_id' => 4, 'files' => '{"1":{"name":"GEN-MLT-13-002-REG-AEC Youth Network 2nd 3rd Batch.For CPR.pdf","category":4}}'),
            array('project_tr_id' => 3591, 'ref_project_review_id' => 4, 'files' => '{"1":{"name":"Annex 1. Budget.xls","category":4},"2":{"name":"matrix compilation. The Applications of Chemical Fertilizers and Pesticides in ASEAN (012).pdf","category":4}}'),
            array('project_tr_id' => 4428, 'ref_project_review_id' => 4, 'files' => '{"1":{"name":"ASEAN Pakistan Conference on Material Science.endorsed.pdf","category":4},"2":{"name":"ASEAN-Pakistan Conference on Materials Science.for CPR.pdf","category":4},"3":{"name":"CPR Endorsement (059).ER1.11 June 2013.pdf","category":4},"4":{"name":"Letter to CPR (053). 22 May 2013.pdf","category":4},"5":{"name":"matrix compilation.ASEAN-Pakistan Conference on Materials Science (059).pdf","category":4}}'),
            array('project_tr_id' => 4011, 'ref_project_review_id' => 4, 'files' => '{"1":{"name":"CPR Endorsement (057). ER2.J. 21 July 2014.pdf","category":4},"2":{"name":"Letter to CPR (042).11 June 2014.pdf","category":4},"3":{"name":"matrix compilation. Training Course on Carbon Accounting System and Carbon Trading (057).pdf","category":4},"4":{"name":"Training Course on Carbon Accounting System and Carbon Trading.endorsed.pdf","category":4},"5":{"name":"Training Course on Carbon Accounting System and Carbon Trading.for CPR.PDF","category":4}}'),
            array('project_tr_id' => 4011, 'ref_project_review_id' => 5, 'files' => '{"1":{"name":"22 16 Apr Approval - Training Course on Carbon Accounting System and Carbon Trading.pdf","category":4},"2":{"name":"M. to EERD_Training on Carbon Accountin System (27 Apr 2015, Vol.1, 038.pdf","category":4},"3":{"name":"RACProject_27March2015_C_accounting.pdf","category":4},"4":{"name":"Revised budget_27March2015.xlsx","category":4}}'),
            array('project_tr_id' => 3601, 'ref_project_review_id' => 4, 'files' => '{"2":{"name":"FA for CPR Approval 010.pdf","category":4},"5":{"name":"STDF Contract WTO-ASEAN Draft ver 25-01-2012 (clean).doc","category":4}}'),
            array('project_tr_id' => 3606, 'ref_project_review_id' => 4, 'files' => '{"1":{"name":"CPR 027.pdf","category":4},"2":{"name":"img-320200226.pdf","category":4},"3":{"name":"Taxonomy_ For CPR 2.docx","category":4},"4":{"name":"Taxonomy_ For CPR 2.pdf","category":4}}'),
            array('project_tr_id' => 3578, 'ref_project_review_id' => 4, 'files' => '{"3":{"name":"CPR Endorsement.043.ER2. 23 april 2013.pdf","category":4},"4":{"name":"Letter to CPR (036). 4 April 2013.pdf","category":4},"5":{"name":"matrix compilation.Rice Production in Southeast Asia under Organic Agriculture Farming System.pdf","category":4}}'),
            array('project_tr_id' => 3582, 'ref_project_review_id' => 2, 'files' => '{"1":{"name":"Memo PAC A (083).20 Nov12.pdf","category":4},"2":{"name":"Memo PAC A Comments (088).27nov12.pdf","category":4},"3":{"name":"Microsoft Outlook - Memo Style.pdf","category":4},"4":{"name":"PAC A ACTIONS.comments.088.matrix.docx","category":4}}'),
            array('project_tr_id' => 3582, 'ref_project_review_id' => 4, 'files' => '{"1":{"name":"CPR Endorsement.018.ER2.25feb13.pdf","category":4},"2":{"name":"letter CPR 010.4febr13.pdf","category":4},"3":{"name":"matrix compilation.Reduction of Post-harvest Losses for Agricultural Produces.pdf","category":4}}'),
            array('project_tr_id' => 4362, 'ref_project_review_id' => 2, 'files' => '{"1":{"name":"Memo PAC A (108).pdf","category":4},"2":{"name":"Microsoft Outlook - Memo Style.pdf","category":4}}'),
            array('project_tr_id' => 4362, 'ref_project_review_id' => 5, 'files' => '{"1":{"name":"46 February 19 bank account (JENESYS2.0 Japanese Language).pdf","category":4}}'),
            array('project_tr_id' => 4364, 'ref_project_review_id' => 2, 'files' => '{"1":{"name":"Memo PAC A (108).pdf","category":4},"2":{"name":"Microsoft Outlook - Memo Style.pdf","category":4},"3":{"name":"Social Community.for PAC.pdf","category":4}}'),
            array('project_tr_id' => 4364, 'ref_project_review_id' => 4, 'files' => '{"1":{"name":"CPR Endoresement (024). ER2.J. 7 March 2014.pdf","category":4},"4":{"name":"Letter to CPR (020). 26 February 2014.pdf","category":4},"5":{"name":"matrix compilation. JENESYS 2.0.Social Community (024).pdf","category":4}}'),
            array('project_tr_id' => 4365, 'ref_project_review_id' => 4, 'files' => '{"1":{"name":"CPR Endoresement (026). ER2.J. 17 March 2014.pdf","category":4},"2":{"name":"JENESYS 2.0. Dispatching Programme.endorsed.pdf","category":4},"4":{"name":"Letter to CPR (020). 26 February 2014.pdf","category":4},"5":{"name":"matrix compilation. JENESYS 2.0. Dispatching Programme (026).pdf","category":4}}'),
            array('project_tr_id' => 4286, 'ref_project_review_id' => 4, 'files' => '{"1":{"name":"ANNEX 2.INDICATIVE WORKPLAN.JENESYS 2.0 SPORTS.XLSX.XLSX","category":4}}'),
            array('project_tr_id' => 3771, 'ref_project_review_id' => 2, 'files' => '{"1":{"name":"Memo PAC D (109).pdf","category":4}}'),
            array('project_tr_id' => 4259, 'ref_project_review_id' => 2, 'files' => '{"1":{"name":"Annex 1_Budget Proposal.xlsx","category":4},"2":{"name":"Annex 3_Presentation of ILO on ASEAN TRIANGLE Phase II in 10th ACMW.pdf","category":4},"3":{"name":"Annex 4 - Monitoring and Evaluation Plan - draft.docx","category":4},"4":{"name":"Clarification Notes_ASEAN TRIANGLE_PAC_19Sept2017.docx","category":4},"5":{"name":"For Consideration_ASEAN TRIANGLE Phase II.docx","category":4},"6":{"name":"Memo PAC (069) TRIANGLE_14 September 2017.pdf","category":4},"7":{"name":"Memo re PAC Ad-Ref Comments (018) TRIANGLE_19 Sept2017.pdf","category":4},"8":{"name":"PAC COMMENTS on TRIANGLE_19Sept2017.docx","category":4},"9":{"name":"SCD-LAB-17-023-REG.pdf","category":4},"10":{"name":"[PAC Ad-Ref Endorsement] SCDLAB17023REG.pdf","category":4}}'),
            array('project_tr_id' => 4259, 'ref_project_review_id' => 4, 'files' => '{"1":{"name":"Letter to CPR (100) 17 Nov 2017.pdf","category":4}}'),
            array('project_tr_id' => 3887, 'ref_project_review_id' => 2, 'files' => '{"1":{"name":"JENESYS 2.0 Mass Media.for PAC.pdf","category":4},"2":{"name":"Memo PAC A (017).pdf","category":4},"3":{"name":"Microsoft Outlook - Memo Style.pdf","category":4}}'),
            array('project_tr_id' => 3887, 'ref_project_review_id' => 4, 'files' => '{"1":{"name":"CPR Endoresement (034). ER2.J. 25 April 2014.pdf","category":4},"4":{"name":"Letter to CPR (027). 7 April 2014.pdf","category":4},"5":{"name":"matrix compilation. JENESYS 2.0.Mass Media(034).pdf","category":4}}'),
            array('project_tr_id' => 4249, 'ref_project_review_id' => 4, 'files' => '{"1":{"name":"ASEAN Japan SSN_for CPR(1).docx","category":4},"2":{"name":"ASEAN Japan SSN_for CPR.docx","category":4},"3":{"name":"ASEAN Japan SSN_for CPR.pdf","category":4},"4":{"name":"CPR 001.pdf","category":4},"5":{"name":"CPR 134.pdf","category":4},"6":{"name":"Matrix Compilations as of 3 Jan 2012.doc","category":4}}'),
            array('project_tr_id' => 4242, 'ref_project_review_id' => 2, 'files' => '{"1":{"name":"ASEAN ARCH Project - PAC Endorsement_150909.pdf","category":4},"2":{"name":"Memo PAC (057) ASEAN ARCH Project_150904.pdf","category":4},"3":{"name":"SCD-HEA-15-022-REG ASEAN ARCH Project_PAC.pdf","category":4}}'),
            array('project_tr_id' => 4041, 'ref_project_review_id' => 4, 'files' => '{"1":{"name":"1_Letter to ASEAN re MAHFSA draft agreement (as of 23 May 2018).PDF","category":4}}'),
            array('project_tr_id' => 4300, 'ref_project_review_id' => 4, 'files' => '{"2":{"name":"CPR Endorsement (139). ER2. 7 October 2013.pdf","category":4},"4":{"name":"Letter to CPR (132). 20 September 2013.pdf","category":4},"5":{"name":"matrix compilation.Establishment of ASEAN Wide Research Networking on Ageing(139).pdf","category":4}}'),
            array('project_tr_id' => 4306, 'ref_project_review_id' => 2, 'files' => '{"1":{"name":"Delivering Social Protection Programs.for PAC.pdf","category":4},"2":{"name":"Memo PAC C comments (084).22nov12.pdf","category":4},"3":{"name":"Microsoft Outlook - Memo Style.pdf","category":4},"4":{"name":"PAC C Actions-Comments.ASEAN Reg Prog for Cap Dev.084.matrix.docx","category":4}}'),
            array('project_tr_id' => 4306, 'ref_project_review_id' => 4, 'files' => '{"1":{"name":"ASEAN Regional Program for Capacity Development.endorsed..pdf","category":4},"2":{"name":"CPR Endorsement (074). ER2. 9 July 2013.pdf","category":4},"4":{"name":"Letter to CPR (064). 18 June 2013.pdf","category":4},"5":{"name":"matrix compilation. memo 074.pdf","category":4}}'),
        );

        foreach ($broken_datas as $key => $value) {
            $data = $this->db->query("
                SELECT a.project_tr_id, a.ref_project_review_id, a.is_delete, b.id as project_review_trans_finish_id, b.files
                FROM project_review_trans a
                LEFT JOIN project_review_trans_finish b ON b.project_review_trans_id =  a.id
                WHERE a.project_tr_id = $value[project_tr_id] AND a.ref_project_review_id = $value[ref_project_review_id]
                AND a.is_delete = 0
                AND b.is_delete = 0
            ")->row_array();

            if (isset($data['project_review_trans_finish_id'])) {
                $this->Direct2db_model->update(array(
                    'files' => $value['files'],
                ), $data['project_review_trans_finish_id'], 'project_review_trans_finish', 'id');
            }
        }
    }
}