<?php

use phpDocumentor\Reflection\DocBlock\Tags\Var_;

include_once "Controller.php";
include_once __DIR__ . "/../models/DealsModel.php";
include_once __DIR__ . "/../models/DealsDocsModel.php";
include_once __DIR__ . "/../helpers/hubspot/Hubspot.php";

class DealsController extends Controller {

    public function __construct($ui, $_L, $config, $_pd) {
        parent::__construct($ui, $_L, $config, $_pd);

        $this->ui->assign('_title', $this->_L['module imobles deals list tasks'] . ' - ' . $this->config['CompanyName']);
        $this->ui->assign('_st', $_L['module imobles']);
    }

    public function add_task() {
        $id = $this->routes[3];
        $deal = @$this->routes[4];
        $profile_view = @$this->routes[5];
        if($deal){
            $deals = ORM::for_table('module_imobles_deals')
                    ->where_null('deleted_at')
                    ->where("id",$deal)
                    ->find_many();
            $this->ui->assign('deal', $deals); 
        }
        if ($id > 0) {
            $model = new DealtasksModel();
            $task = $model->getOne($id);
            $this->ui->assign('task', $task);

            $ac = ORM::for_table('sys_users')
                    ->where('id',$task->responsible_id)
                    ->find_many();
            $this->ui->assign('responsible', $ac);
            
        } else {
            $user = User::_info();
            $ac = ORM::for_table('sys_users')
                    ->where('id',$user->id)
                    ->find_many();
            $this->ui->assign('responsible', $ac);

            $this->ui->assign('task', (object) array( "deal_id"=>'',"title" => "", 'description' => '', 'due_date' => '', 'closed' => 0, 'client_responsibility' => 0));
        }
        $this->ui->assign('id', $id);
        $this->ui->assign('profile_view', $profile_view);
        $this->ui->display($this->_pd . '/views/deals/form_tasks.tpl'); 
    }
 
    public function post_task() {
        try {
            $data = $_POST;
            $model = new DealtasksModel();
            $msg = '';
            $is_new = true;
            if (empty(_post('title'))) {
                $msg .= $this->_L['Title'] . $this->_L['is required'];
            }
            if (empty(_post('due_date'))) {
                $msg .= $this->_L['module imobles deals tasks term'] . $this->_L['is required'];
            }

            if (empty($msg)) {

                if (isset($data['id']) && $data['id'] > 0) {
                    $is_new = false;
                    $task = $model->getOne($data['id']);
                    $task = $model->updateDealtasks($data, $task, true);
                } else {
                    $task = $model->createDealtasks($data);
                }

                if(isset($task->id)){
                    _notify($this->_L['module imobles deals task'] . " " . $this->_L['Add Successfull'], 's');

                    $deal_aux = ORM::for_table('module_imobles_deals')->find_one($data['deal_id']);
                    if(isset($deal_aux->id)){
                        $data['title_google'] = 'Tarefa Rocket: '.$deal_aux->title.' | '.$data['title'];
                    } else {
                        $data['title_google'] = 'Tarefa Rocket: '.$data['title'];
                    }
                    $retorno['status'] = 'OK';
                    $data['due_date_google'] = date('Ymd', strtotime(_formatDateDB($data['due_date']))).'T103000';
                    $retorno['fields'] = $data;
                    $retorno['is_new'] = $is_new; 

                    // Criar task no hubspot
                    $deal_id = $data['deal_id'];
                    $deal_details = ORM::for_table('module_imobles_deals')->find_one($deal_id);
                    if(!empty($deal_details->hs_deal_id)){
                        $hsHelper = new hubspotHelper();
                        $due_date = isset($data['due_date']) ? _formatDateDB($data['due_date']) : date('Y-m-d');
                        $hs_timestamp = date('Y-m-d', strtotime($due_date)).'T12:00:00Z';
                        $hs_task_subject = $data['title'];
                        $hs_task_body = $data['description'];
                        $hs_task_status = @$data['closed']==1?'COMPLETED':'NOT_STARTED';
                        $hs_task_type = 'TODO';
                        $hubspot_owners = $hsHelper->hubspotGetOwners();
                        $hubspot_owner_id = null;
                        if(!empty($hubspot_owners)){
                            $user = User::_info();
                            foreach($hubspot_owners as $owner){
                                if($owner->email == $user->username){
                                    $hubspot_owner_id = $owner->id;
                                }
                            }
                        } 

                        // $hs_task_id = null;
                        // if(isset($task->hs_task_id) && intval($task->hs_task_id) > 0){
                        //     $hs_task_id = $task->hs_task_id;
                        //     // TODO: tratar o UPDATE das tasks
                            
                        // } else {
                        //     $hs_task = $hsHelper->hubspotCreateTask($deal_details->hs_deal_id, $hs_timestamp, $hs_task_body, $hs_task_subject, $hs_task_status, $hs_task_type, $hubspot_owner_id, $hs_task_id);
                        //     if(isset($hs_task->id)){
                        //         $task->hs_task_id = $hs_task->id;
                        //         $task->save();
                        //     }
                        // }
                    }
                } else {
                    $retorno['status'] = 'ERRO';
                    $retorno['msg'] = $this->_L['module_imobles_insert_error'];
                }
            } else {
                $retorno['status'] = 'ERRO';
                $retorno['msg'] = $msg;
            }
        } catch (Exception $ex) {
            $retorno['status'] = 'ERRO';
            $retorno['msg'] = $ex->getMessage();
        }
        echo json_encode($retorno);
    }

    public function delete_tasks() {
        $model = new DealtasksModel();
        $tsks = $model->getOne(_post('id'));
        $model->deleteDealtasks($tsks);
        r2(U . 'module_imobles/deals/tasks', 's', $this->_L['delete_successful']);
    }

    public function delete_task() {
        $model = new DealtasksModel();
        $tsks = $model->getOne(_post('id'));
        $model->deleteDealtasks($tsks);
        $retorno['status'] = 'OK';
        echo json_encode($retorno);
    }

    public function setDeals() {
        $deal = isset($this->routes['3']) ? $this->routes['3'] : 0;
        if ($deal > 0) {
            $c = ORM::for_table('module_imobles_deals')
                    ->where_raw("deleted_at is null")
                    ->where_raw("id IN (" . $deal . ")")
                    ->find_one();
            if ($c) {
                $_SESSION['filter']['module_imobles/deals/tasks']['crm_account_id'] = $c->crm_account_id;
                $_SESSION['filter']['module_imobles/deals/tasks']['deals_id'] = $deal;
            }
        }
        r2(U . "module_imobles/deals/tasks");
    }

    public function close_task() {
        $model = new DealtasksModel();
        $tsks = $model->getOne(_post('id'));
        $tsks->closed = _post('close_task');
        $tsks->save();
        $retorno['status'] = 'OK';
        echo json_encode($retorno);
    }

    public function notes() {
        $deal_id = @$this->routes[4];
        $cid = @$this->routes[3];
        $d = ORM::for_table('crm_accounts')->find_one($cid);
        if ($d) {
            $ac = ORM::for_table('module_imobles_deals_notes')
            ->where('crm_account_id', $cid)
            ->where('deal_id', $deal_id)
            ->where_null('deleted_at')
            ->order_by_desc('created_at')
            ->find_array();

            foreach($ac as $k=>$a){
                $sys_user = ORM::for_table('sys_users')->find_one($a['created_by']);
                if(isset($sys_user->id)){
                    $ac[$k]['sys_user_name'] = $sys_user->fullname;
                }
            }

            $this->ui->assign('ac', $ac); 
            $this->ui->display($this->_pd . '/views/deals/notes.tpl');
        }
    }

    public function add_notes_post() {
        $cid = _post('cid');
        $deal_id = $_POST['deal_id'];
        $msg = $_POST['msg'];
        $user = User::_info();

        $d = ORM::for_table('module_imobles_deals_notes')->create();
        $d->crm_account_id = $cid;
        $d->msg = $msg;
        $d->deal_id = $deal_id;
        $d->created_at = date('Y-m-d H:i:s');
        $d->created_by = $user->id;
        $d->save();
        echo $cid;
    }

    public function edit_notes() {
        $id = $this->routes[3];
        $d = ORM::for_table('module_imobles_deals_notes')->find_one($id);
        if ($d) {
            $this->ui->assign('d', $d);
            $this->ui->display($this->_pd . '/views/deals/modal_edit_note.tpl');
        }
    }

    public function edit_notes_post() {
        $edit_activity_id = _post('edit_activity_id');
        $user = User::_info();
        $d = ORM::for_table('module_imobles_deals_notes')->find_one($edit_activity_id);
        if ($d) {
            $message_text = $_POST['message_text'];
            $d->msg = $message_text;
            $d->updated_at = date('Y-m-d H:i:s');
            $d->updated_by = $user->id;
            $d->save();
            echo $d->id();
        }
    }

    public function deals_notes_delete() {
        $id = _post('id');
        $user = User::_info();
        $d = ORM::for_table('module_imobles_deals_notes')->find_one($id);
        if ($d) {
            $d->deleted_at = date('Y-m-d H:i:s');
            $d->deleted_by = $user->id;
            $d->save();
            echo $d->id();
        }
    }

    public function tasks() {
        $deal_id = null;
        if ($i = array_search('deal_id', $this->routes)){
            $deal_id = filter_var($this->routes[$i + 1], FILTER_SANITIZE_STRING);
        }
        if(!empty($deal_id)){
            $_SESSION['filter']['module_imobles/deals/tasks_'.$deal_id]['deals_id'] = $deal_id;
        }
        $where = '';
        $join = "";
        $filter = array();
        $filter['items_per_page'] = '200';
        if ($_SERVER['REQUEST_METHOD'] == 'POST') {
            unset($_SESSION['filter']['module_imobles/deals/tasks_'.$deal_id]);
            if (filter_var(_post('clear_filter'), FILTER_SANITIZE_STRING) == 1) {
                // unset($_SESSION['filter']['module_imobles/deals/tasks_'.$deal_id]);
            } else {

        //                $_SESSION['filter']['module_imobles/deals/tasks_'.$deal_id]['search'] = filter_var(_post('search'), FILTER_SANITIZE_STRING);
                $_SESSION['filter']['module_imobles/deals/tasks_'.$deal_id]['crm_account_id'] = filter_var(_post('crm_account_id'), FILTER_SANITIZE_STRING);
                $_SESSION['filter']['module_imobles/deals/tasks_'.$deal_id]['deals_id'] = filter_var(_post('deals_id'), FILTER_SANITIZE_STRING);
                $filter['items_per_page'] = filter_var(_post('items_per_page'), FILTER_SANITIZE_STRING);
                $_SESSION['filter']['module_imobles/deals/tasks_'.$deal_id]['task_status'] = filter_var(_post('task_status'), FILTER_SANITIZE_STRING);
                $_SESSION['filter']['module_imobles/deals/tasks_'.$deal_id]['responsible_id'] = filter_var(_post('responsible_id'), FILTER_SANITIZE_STRING);

                $_SESSION['filter']['module_imobles/deals/tasks_'.$deal_id]['period'] = filter_var(_post('period'), FILTER_SANITIZE_STRING);
                $_SESSION['filter']['module_imobles/deals/tasks_'.$deal_id]['period_ini'] = filter_var(_post('period_ini'), FILTER_SANITIZE_STRING);
                $_SESSION['filter']['module_imobles/deals/tasks_'.$deal_id]['period_end'] = filter_var(_post('period_end'), FILTER_SANITIZE_STRING);
                
            }
        } else {
            if ($i = array_search('items_per_page', $this->routes))
                $filter['items_per_page'] = filter_var($this->routes[$i + 1], FILTER_SANITIZE_STRING);
        }

        if (isset($_SESSION['filter']['module_imobles/deals/tasks_'.$deal_id])) {
            $itenPp = $filter['items_per_page'];
            $filter = $_SESSION['filter']['module_imobles/deals/tasks_'.$deal_id];
            $filter['items_per_page'] = $itenPp;
        } else {
        //            $filter['search'] = "";
            $filter['crm_account_id'] = "";
            $filter['deals_id'] = "";
        }

        $this->ui->assign('dealFilter', true);

        if (!empty($filter['crm_account_id'])) {
            $where .= " AND d.crm_account_id IN (" . htmlentities($filter['crm_account_id']) . ")";
            $c = ORM::for_table('crm_accounts')
                    ->where_raw("id IN (" . htmlentities($filter['crm_account_id']) . ")")
                    ->find_many();
            $this->ui->assign('clientSelecteds', $c);
        }

        if (!empty($filter['deals_id'])) {

            $where .= " AND e.deal_id IN (" . htmlentities($filter['deals_id']) . ")";
            $c = ORM::for_table('module_imobles_deals')
                    ->where_raw("id IN (" . htmlentities($filter['deals_id']) . ")")
                    ->find_many();
            $this->ui->assign('deal', $c);
            $ac = ORM::for_table('crm_accounts')
                    ->where_raw("id IN (" . htmlentities($c[0]->crm_account_id) . ")")
                    ->find_many();
            $this->ui->assign('clientSelecteds', $ac);
        } 

        if(empty($filter['crm_account_id']) && empty($filter['deals_id']))
        {
            // $user = User::_info();
            // $where .= " AND (d.sdr_id IN (" . $user->id . ") or d.seller_id IN (" . htmlentities($user->id) . ") )";
        }

        if (!empty($filter['responsible_id'])) {
            $where .= " AND IF(e.responsible_id IS NULL, IF (st.type = 'sale', d.seller_id IN (" . htmlentities($filter['responsible_id']) . "), d.sdr_id IN (" . htmlentities($filter['responsible_id']) . ") ), e.responsible_id = ". htmlentities($filter['responsible_id']).") ";
            $ac = ORM::for_table('sys_users')
                    ->where_raw("id IN (" . htmlentities($filter['responsible_id']) . ")")
                    ->find_many();
            $this->ui->assign('responsible', $ac);
        }

        if (isset($filter['task_status'])) {
            if($filter['task_status'] === '1'){
                $where .= " AND e.closed = 1 ";
            } else if($filter['task_status'] === '0'){
                $where .= " AND (e.closed IS NULL OR e.closed = 0) ";
            }
        }


        // Filtro por períodos
        if (isset($filter['period']) && $filter['period'] != '') {
            if ($filter['period'] == 'all')
                $where .= " AND e.due_date IS NOT NULL";
            if ($filter['period'] == 'day')
                $where .= " AND e.due_date = '" . date('Y-m-d') . "'";
            if ($filter['period'] == 'week')
                $where .= " AND WEEK(e.due_date) = WEEK('" . date('Y-m-d') . "')";
            if ($filter['period'] == 'month')
                $where .= " AND e.due_date >= '" . date('Y-m-1') . "' AND e.due_date <= '" . date('Y-m-31') . "'";
            if ($filter['period'] == 'interval' && $filter['period_ini'] != '' && $filter['period_end'] != '')
                $where .= " AND e.due_date BETWEEN '" . implode('-', array_reverse(explode('/', $filter['period_ini']))) .
                        "' AND '" . implode('-', array_reverse(explode('/', $filter['period_end']))) . "'";
        }
        $sql = ' FROM module_imobles_deal_tasks e'
                . ' JOIN module_imobles_deals d ON (e.deal_id=d.id)'
                . ' LEFT JOIN sys_users seller ON (d.seller_id=seller.id)'
                . ' LEFT JOIN sys_users sdr ON (d.sdr_id=sdr.id)'
                . ' LEFT JOIN sys_users resp ON (e.responsible_id=resp.id)'
                . ' JOIN module_imobles_pipe_stages st ON (d.sale_stage_id=st.id)'
                . ' JOIN crm_accounts c ON (c.id=d.crm_account_id)'
                . $join
                . '  WHERE e.deleted_at IS NULL ' . $where;
        $order = ' ORDER BY e.due_date asc';
        $select = "Select 
                    IF (e.responsible_id IS NULL,
                    IF (st.type = 'sale', seller.fullname, sdr.fullname), resp.fullname) as responsible,
                    e.*,d.title as title_deal, st.type as pipe_type, sdr.fullname as sdr_name, c.id as crm_account_id  ";
        if ($filter['items_per_page'] != 'all'){
            $filter['items_per_page'] = ($filter['items_per_page'] > 0) ? $filter['items_per_page'] : 10;
        }
        
        if ($i = array_search('items_per_page', $this->routes)){
            $filter['items_per_page'] = filter_var($this->routes[$i + 1], FILTER_SANITIZE_STRING);
        }

        $page = 1;
        if ($i = array_search('page', $this->routes)){
            $page = filter_var($this->routes[$i + 1], FILTER_SANITIZE_STRING);
        }

        $d = ORM::for_table('module_imobles_deals_tasks')->raw_query("select count(e.id)  as total" . $sql . "")->find_many();
        $bottomBar = array(
            array('type' => 'int', 'info' => $this->_L['module imobles deals_Qty'], 'value' => $d[0]->total),
        );

        $paginator = new ModulePaginator('module_imobles_deals_tasks', $select . $sql . $order, $filter);
        $records = $paginator->setItemsPerPage($filter['items_per_page'])
                ->setTotalItems($d[0]->total)
                ->setCurrentPageNumber($page)
                ->run();

        $this->ui->assign('tasks', $records);
        $this->ui->assign('paginator', $paginator);
        $this->ui->assign('bottomBar', $bottomBar);

        ORM::configure("logging", false);

        $this->ui->assign('jsvar', '
        _L[\'are_you_sure\'] = \'' . $this->_L['are_you_sure'] . '\';
        _L[\'Save\'] = \'' . $this->_L['Save'] . '\';
        _L[\'OK\'] = \'' . $this->_L['OK'] . '\';
        _L[\'Remove\'] = \'' . $this->_L['Remove'] . '\';
        _L[\'Cancel\'] = \'' . $this->_L['Cancel'] . '\';
        _L[\'Select\'] = \'' . $this->_L['Select'] . '\'; 
        _L[\'module_imobles Delete deals task?\'] = \'' . $this->_L['module_imobles Delete deals task?'] . '\'; '
        );


        $css_arr = array('dp/dist/datepicker.min','redactor/redactor', 'dropzone/dropzone', 'modal', '/../../../application/plugins/module_imobles/assets/css/default', 's2/css/select2.min');

        $js_arr = array('dp/dist/datepicker.min', 'modal', 'redactor/redactor.min', 'redactor/fontcolor', 'dropzone/dropzone',
            '/../../../application/plugins/module_imobles/assets/js/dealstask-list', 's2/js/select2.min'
        );

        $assets_css = Asset::css($css_arr, date("Y-m-d"));
        $assets_js = Asset::js($js_arr, date("Y-m-d"));

        $this->ui->assign('xheader', $assets_css);
        $this->ui->assign('xfooter', $assets_js);


        $this->ui->assign('filter', $filter);
        if(empty($deal_id)){
            $this->ui->assign('profile_view', false);
            $this->ui->assign('_st', $this->_L['module imobles deals tasks']);
            $this->ui->assign('_include', 'deals/list');
            $this->ui->display('wrapper.tpl');
        } else {
            $this->ui->assign('profile_view', 1);
            $this->ui->display($this->_pd . '/views/deals/list.tpl');
        }
    }

    public function kanban() {
        $where = '';
        $filter = array();
        $filter['items_per_page'] = 100000;
        if ($_SERVER['REQUEST_METHOD'] == 'POST') {
            unset($_SESSION['filter']['module_imobles/deals/kanban']);
            if (filter_var(_post('clear_filter'), FILTER_SANITIZE_STRING) == 1) {
                $filter['priority'] = "";
                $filter['status_id'] = "";
                $filter['temperature'] = "";
                $filter['crm_account_id'] = "";
                $filter['sdr_id'] = "";
                $filter['seller_id'] = "";
                $filter['period'] = "";
                $filter['period_ini'] = "";
                $filter['period_end'] = "";
                // $filter['type'] = "";
                $_SESSION['filter']['module_imobles/deals/kanban']['type'] = filter_var(_post('type'), FILTER_SANITIZE_STRING);
            } else {
                $_SESSION['filter']['module_imobles/deals/kanban']['type'] = filter_var(_post('type'), FILTER_SANITIZE_STRING);
                $_SESSION['filter']['module_imobles/deals/kanban']['sdr_id'] = filter_var(_post('sdr_id'), FILTER_SANITIZE_STRING);
                $_SESSION['filter']['module_imobles/deals/kanban']['seller_id'] = filter_var(_post('seller_id'), FILTER_SANITIZE_STRING);
                $_SESSION['filter']['module_imobles/deals/kanban']['status_id'] = filter_var(_post('status_id'), FILTER_SANITIZE_STRING);
                $_SESSION['filter']['module_imobles/deals/kanban']['priority'] = filter_var(_post('priority'), FILTER_SANITIZE_STRING);
                $_SESSION['filter']['module_imobles/deals/kanban']['temperature'] = filter_var(_post('temperature'), FILTER_SANITIZE_STRING);
                $_SESSION['filter']['module_imobles/deals/kanban']['crm_account_id'] = filter_var(_post('crm_account_id'), FILTER_SANITIZE_STRING);

                $_SESSION['filter']['module_imobles/deals/kanban']['period'] = filter_var(_post('period'), FILTER_SANITIZE_STRING);
                $_SESSION['filter']['module_imobles/deals/kanban']['period_ini'] = filter_var(_post('period_ini'), FILTER_SANITIZE_STRING);
                $_SESSION['filter']['module_imobles/deals/kanban']['period_end'] = filter_var(_post('period_end'), FILTER_SANITIZE_STRING);

                $filter['items_per_page'] = $filter['items_per_page'];
            }
        } else {
          
            $filter['priority'] = "";
            $filter['status_id'] = "1";
            $filter['temperature'] = "";
            $filter['crm_account_id'] = "";
            $filter['period'] = "";
            $filter['period_ini'] = "";
            $filter['period_end'] = "";
            $user = User::_info();

            if(preg_match('/(SDR)/', $user->user_type)){
                $filter['type'] = "presale";
                $filter['sdr_id'] = $user->id;
            } else if(preg_match('/(SELLER)/', $user->user_type)){
                $filter['type'] = "sale";
                $filter['seller_id'] = $user->id;
            } 
        }

        if (isset($_SESSION['filter']['module_imobles/deals/kanban'])) {
            $itenPp = $filter['items_per_page'];
            $filter = $_SESSION['filter']['module_imobles/deals/kanban'];
            $filter['items_per_page'] = $itenPp;
        }

        if (!empty($filter['sdr_id'])) {

            $where .= " AND (e.sdr_id IN (" . htmlentities($filter['sdr_id']) . "))";

            $ac = ORM::for_table('sys_users')
                    ->where_raw("id IN (" . htmlentities($filter['sdr_id']) . ")")
                    ->find_many();
            $this->ui->assign('responsible_sdr', $ac);
        }

        if (!empty($filter['seller_id'])) {

            $where .= " AND (e.seller_id IN (" . htmlentities($filter['seller_id']) . ") )";

            $ac = ORM::for_table('sys_users')
                    ->where_raw("id IN (" . htmlentities($filter['seller_id']) . ")")
                    ->find_many();
            $this->ui->assign('responsible_seller', $ac);
        }


        // TODO: mostrando apenas VENDA no Pipe
        $filter['type'] = 'sale';
        if (!empty($filter['type'])) {
            $where .= " AND st.type = ('" . htmlentities($filter['type']) . "')";
        }

        if (!empty($filter['status_id'])) {
            $where .= " AND e.status_id = ('" . htmlentities($filter['status_id']) . "')";
        }


        if (!empty($filter['crm_account_id'])) {
            $where .= " AND e.crm_account_id IN (" . htmlentities($filter['crm_account_id']) . ")";
            $c = ORM::for_table('crm_accounts')
                    ->where_raw("id IN (" . htmlentities($filter['crm_account_id']) . ")")
                    ->find_many();
            $this->ui->assign('clientSelecteds', $c);
        }

        // Filtro por períodos
        // var_dump($filter['period']);exit;
        if (isset($filter['period']) && $filter['period'] != '') {
            if ($filter['period'] == 'all')
                $where .= " AND e.closed_at IS NOT NULL";
            if ($filter['period'] == 'day')
                $where .= " AND e.closed_at = '" . date('Y-m-d') . "'";
            if ($filter['period'] == 'week')
                $where .= " AND WEEK(e.closed_at) = WEEK('" . date('Y-m-d') . "')";
            if ($filter['period'] == 'month')
                $where .= " AND e.closed_at >= '" . date('Y-m-1') . "' AND e.closed_at <= '" . date('Y-m-31') . "'";
            if ($filter['period'] == 'interval' && $filter['period_ini'] != '' && $filter['period_end'] != '')
                $where .= " AND e.closed_at BETWEEN '" . implode('-', array_reverse(explode('/', $filter['period_ini']))) .
                        "' AND '" . implode('-', array_reverse(explode('/', $filter['period_end']))) . "'";
        }


        // TODO: patch para ocultar os atendimentos de Pré-Venda
        $where .= " AND e.sale_stage_id <= 7 ";


        $sql = ' FROM module_imobles_deals e'
                . ' LEFT JOIN module_imobles_pipe_vgv v on v.id=e.vgv_id'
                . ' LEFT JOIN module_imobles_pipe_timing t on t.id=e.timing_id'
                . ' LEFT JOIN module_imobles_pipe_engagement eng on eng.id=e.engagement_id'
                . ' LEFT JOIN module_imobles_pipe_stages st on st.id=e.sale_stage_id'
                . '  WHERE e.deleted_at IS NULL ' . $where;


        $order = ' ORDER BY e.id desc';


        $select = "Select
                e.*,v.weight as v_weight, v.name as estimated_vgv,t.weight as t_weight,eng.weight as eng_weight ,
            (select concat(min(sub.due_date),' | ', sub.title) from module_imobles_deal_tasks sub where sub.deal_id=e.id and (sub.closed is null or sub.closed=0) AND sub.deleted_at IS NULL)  as min_due_date_tasks
            ";

        if ($filter['items_per_page'] != 'all')
            $filter['items_per_page'] = ($filter['items_per_page'] > 0) ? $filter['items_per_page'] : 1000;

        $page = isset($this->routes['3']) ? $this->routes['3'] : 1;
        $d = ORM::for_table('module_imobles_deals_tasks')->raw_query("select count(e.id)  as total" . $sql . "")->find_many();
        $paginator = new ModulePaginator('module_imobles_deals_tasks', $select . $sql . $order, $filter);
        $records = $paginator->setItemsPerPage($filter['items_per_page'])
                ->setTotalItems($d[0]->total)
                ->setCurrentPageNumber($page)
                ->run();

        ORM::configure("logging", false);

        $this->ui->assign('jsvar', '
        _L[\'are_you_sure\'] = \'' . $this->_L['are_you_sure'] . '\';
        _L[\'Save\'] = \'' . $this->_L['Save'] . '\';
        _L[\'OK\'] = \'' . $this->_L['OK'] . '\';
        _L[\'Remove\'] = \'' . $this->_L['Remove'] . '\';
        _L[\'Cancel\'] = \'' . $this->_L['Cancel'] . '\';
        _L[\'Select\'] = \'' . $this->_L['Select'] . '\'; 
        _L[\'module imobles deals kanban success\'] = \'' . $this->_L['module imobles deals kanban success'] . '\'; '
        );

        $css_arr = array('dp/dist/datepicker.min', 'modal', '/../../../application/plugins/module_imobles/assets/css/default', 's2/css/select2.min');

        $js_arr = array('dp/dist/datepicker.min', 'modal',
            '/../../../application/plugins/module_imobles/assets/js/dealskanban', 's2/js/select2.min'
        );

        $assets_css = Asset::css($css_arr, '1');
        $assets_js = Asset::js($js_arr, '1');

        $this->ui->assign('xheader', $assets_css);
        $this->ui->assign('xfooter', $assets_js);
        if (empty($filter['type'])) {
            $module_imobles_pipe_stagess = ORM::for_table('module_imobles_pipe_stages')
                            ->where_null('deleted_at')
                            ->order_by_asc('type')
                            ->order_by_asc('sorder')->find_many();
        } else {
            $module_imobles_pipe_stagess = ORM::for_table('module_imobles_pipe_stages')
                            ->where_null('deleted_at')
                            ->where("type", $filter['type'])
                            ->order_by_asc('type')
                            ->order_by_asc('sorder')->find_many();
        }
        $recordStage = array();
        $total = 0;
        foreach ($records as $r) {

            if(empty($r['sale_stage_id'])){
                continue;
            }
            
            if (empty($r['stage_update_at'])) {
                $r['stage_update_at'] = $r['created_at'];
            }

            if(empty($r['v_weight']) || empty($r['t_weight']) || empty($r['eng_weight'])){
                $r['priority'] = 'hight';
                $r['desc_priority'] = $this->_L['module imobles deals priority noinfo'];
                $r['order_priority'] = 1;
            } else {
                $priority = $r['v_weight'] + $r['t_weight'] + $r['eng_weight'];
                if ($priority >= 6) {
                    $r['priority'] = 'hight';
                    $r['desc_priority'] = $this->_L['module imobles deals priority hight'];
                    $r['order_priority'] = 2;
                } else if ($priority >= 5) {
                    $r['priority'] = 'medium';
                    $r['desc_priority'] = $this->_L['module imobles deals priority medium'];
                    $r['order_priority'] = 3;
                } else {
                    $r['priority'] = 'low';
                    $r['desc_priority'] = $this->_L['module imobles deals priority low'];
                    $r['order_priority'] = 4;
                }
            }
            
            if (empty($r['min_due_date_tasks'])) {
                $r['temperature'] = 'no tasks opened';
                $r['temperature_cor'] = 'red';
                $r['temperature_desc'] = $this->_L['module imobles deals temperature no tasks opened'];
                $r['order_temperature'] = 1;
            } else {
                $task_aux = explode('|',$r['min_due_date_tasks']);
                $r['min_due_date_tasks'] = trim($task_aux[0]);
                $r['min_due_date_tasks_title'] = trim(@$task_aux[1]);
                if (strtotime($r['min_due_date_tasks']) > strtotime(date("Y-m-d"))) {
                    $r['temperature'] = 'in day';
                    $r['temperature_cor'] = 'green';
                    $r['temperature_desc'] = $this->_L['module imobles deals temperature in day'];
                    $r['order_temperature'] = 3;
                } else if (strtotime($r['min_due_date_tasks']) > strtotime("-3 days", strtotime(date("Y-m-d")))) {
                    $r['temperature'] = 'low overdue 72h';
                    $r['temperature_cor'] = 'yellow';
                    $r['temperature_desc'] = $this->_L['module imobles deals temperature low overdue 72h'];
                    $r['order_temperature'] = 2;
                } else {
                    $r['temperature'] = 'hight overdue 72h';
                    $r['temperature_cor'] = 'red';
                    $r['temperature_desc'] = $this->_L['module imobles deals temperature hight overdue 72h'];
                    $r['order_temperature'] = 1;
                }
            }

            if($r['sale_stage_id'] == 7){
                // $r['priority'] = 'low';
                // $r['desc_priority'] = $this->_L['module imobles deals priority low'];
                // $r['order_priority'] = 4;
                $r['temperature'] = 'in day';
                $r['temperature_cor'] = 'green';
                $r['temperature_desc'] = $this->_L['module imobles deals temperature in day'];
                $r['order_temperature'] = 3;

            }

            if(empty($r['deal_amount'])) {
                // if(!empty($r['estimated_vgv'])){
                //     $r['deal_amount'] = $r['estimated_vgv'];
                // } else {
                //     $r['deal_amount'] = 'N/A';
                // }
            }

            if (!empty($filter['priority'])) {
                if ($filter['priority'] != $r['priority']) {
                    continue;
                }
            }
            if (!empty($filter['temperature'])) {
                if ($filter['temperature'] != $r['temperature']) {
                    continue;
                }
            }

            $recordStage[$r['sale_stage_id']][] = $r;
            $total++;
        }
        
        foreach ($recordStage as $stage_key => $stage_val) {
            $a = array();
            $b = array();
            foreach ($stage_val as $k => $t) {
                $a[$k] = $t['order_priority'];
                $b[$k] = $t['order_temperature'];
            }
            array_multisort($a, SORT_ASC, $b, SORT_ASC, $stage_val);
            $recordStage[$stage_key] = $stage_val;
        }

        $bottomBar = array(
            array('type' => 'int', 'info' => $this->_L['module imobles deals_Qty'], 'value' => $total),
        );
        
        $this->ui->assign('status', ORM::for_table('module_imobles_pipe_status')
                        ->where_null('deleted_at')
                        ->order_by_asc('sorder')->find_many());
        $this->ui->assign('stages', $module_imobles_pipe_stagess);
        $this->ui->assign('countStages', count($module_imobles_pipe_stagess));
        $this->ui->assign('dealsStage', $recordStage);
        // $this->ui->assign('paginator', $paginator);
        $this->ui->assign('filter', $filter);
        $this->ui->assign('bottomBar', $bottomBar);
        $this->ui->assign('_st', $this->_L['module imobles deals tasks']);
        $this->ui->assign('_include', 'deals/kanban');
        $this->ui->display('wrapper.tpl');
    }

    public function kanbanClosingJourney() {
        $stage_id = $this->routes[3];
        $id = @$this->routes[4];
        $this->ui->assign('stage_id', $stage_id);
        $this->ui->assign('id', $id);
        $this->ui->display($this->_pd . '/views/deals/closing_journey.tpl');
    }

    public function saveKanbanStage() {
        $deals = ORM::for_table('module_imobles_deals')->find_one(_post('id'));
        if (_post('stage_id') <=7 && _post('stage_id') > $deals->sale_stage_id && empty($deals->closing_journey) &&  empty(_post('closing_journey'))) {
            $retorno['status'] = 'ERRO';
            $retorno['stage_id'] = _post('stage_id');
            $retorno['msg'] = '<b>'.$this->_L['module_imobles deals closing_journey'] .'</b> '. $this->_L['is required']. ' para essa etapa do funil';
        } else {
            $deals->sale_stage_id = _post('stage_id');
            $user = User::_info();
            $deals->stage_update_at = date('Y-m-d H:i:s');
            $deals->updated_at = date('Y-m-d H:i:s');
            $deals->updated_by = $user->id;

            if (!empty(_post('closing_journey'))) {
                $deals->closing_journey = _post('closing_journey');
            }
            $deals->save();
            
            // Atualização no Hubpost
            if(!empty($deals->hs_deal_id)){
                $hsHelper = new hubspotHelper();

                $data = array();
                if($deals->sale_stage_id == 1){
                    $data['dealstage'] = '10780349';
                } elseif($deals->sale_stage_id == 2){
                    $data['dealstage'] = '46791452';
                } elseif($deals->sale_stage_id == 3){
                    $data['dealstage'] = '6568482';
                } elseif($deals->sale_stage_id == 4){
                    $data['dealstage'] = '50005805';
                } elseif($deals->sale_stage_id == 5){
                    $data['dealstage'] = '6950881';
                } elseif($deals->sale_stage_id == 6){
                    $data['dealstage'] = '6950882';
                } elseif($deals->sale_stage_id == 7){
                    $data['dealstage'] = 'closedwon';
                } elseif($deals->sale_stage_id == 8){
                    $data['dealstage'] = '14019903';
                } elseif($deals->sale_stage_id == 9){
                    $data['dealstage'] = '57109334';
                } elseif($deals->sale_stage_id == 10){
                    $data['dealstage'] = '9881352';
                } elseif($deals->sale_stage_id == 11){
                    $data['dealstage'] = '9881353';
                } elseif($deals->sale_stage_id == 12){
                    $data['dealstage'] = '65328237';
                } elseif($deals->sale_stage_id == 13){
                    $data['dealstage'] = '65328238';
                } elseif($deals->sale_stage_id == 14){
                    $data['dealstage'] = '57109335';
                } elseif($deals->sale_stage_id == 15){
                    $data['dealstage'] = '9881351';
                } elseif($deals->sale_stage_id == 16){
                    $data['dealstage'] = '9881354';
                } elseif($deals->sale_stage_id == 17){
                    $data['dealstage'] = '37156417';
                } elseif($deals->sale_stage_id == 18){
                    $data['dealstage'] = '9624616';
                }
                $aux= $hsHelper->hubspotUpdateDealProperties($deals->hs_deal_id, $data);
            }
            $retorno['status'] = 'OK';
            $retorno['msg'] = $this->_L['module imobles deals kanban success'];
        }
        echo json_encode($retorno);
    }


    public function docs_list() {
        $dealID = $this->routes[3];

        $docs = ORM::for_table("module_imobles_deals_docs")
            ->select_expr('d.*, c.name as category_name')
            ->table_alias('d')
            ->left_outer_join('module_imobles_docs_category', 'c.id = d.category_id', 'c')
            ->where('d.deal_id', $dealID)
            ->where_null('d.deleted_at')
            ->find_many();


        $css_arr = array(
            'modal',
            'dropzone/dropzone',
            's2/css/select2.min',
            'dp/dist/datepicker.min',
            '/../../../application/plugins/module_imobles/assets/css/default'
        );
        
        $js_arr = array(
            'modal',
            'dropzone/dropzone',
            's2/js/select2.min',
            'dp/dist/datepicker.min',
            '/../../../application/plugins/module_imobles/assets/js/dealsdocs-list'            
        );

        $assets_css = Asset::css($css_arr, date("Y-m-d"));
        $assets_js = Asset::js($js_arr, date("Y-m-d h:i:s") . "001");

        $this->ui->assign('xheader', $assets_css);
        $this->ui->assign('xfooter', $assets_js);

        $this->ui->assign('docs', $docs);
        $this->ui->display($this->_pd . '/views/deals/docs_list.tpl');
    }

    public function docs_add() {
        $dealID = $this->routes[3];

        $this->ui->assign('doc', (object) array(
            "id" => "",
            "deal_id" => $dealID,
            "category_id" => "",
            "category_name" => "",
            'issued_date' => '',
            'expiration_date' => '',
            'obs' => "")
        );
        
        $css_arr = array(
            'modal',
            'dropzone/dropzone',
            's2/css/select2.min',
            'dp/dist/datepicker.min',
            '/../../../application/plugins/module_imobles/assets/css/default'
        );
        
        $js_arr = array(
            'modal',
            'dropzone/dropzone',
            's2/js/select2.min',
            'dp/dist/datepicker.min',
            '/../../../application/plugins/module_imobles/assets/js/dealsdocs-list'            
        );

        $assets_css = Asset::css($css_arr, date("Y-m-d"));
        $assets_js = Asset::js($js_arr, date("Y-m-d h:i:s") . "001");

        $this->ui->assign('xheader', $assets_css);
        $this->ui->assign('xfooter', $assets_js);

        $this->ui->display($this->_pd . '/views/deals/docs_form.tpl');
    }

    public function docs_edit() {
        $docID = $this->routes[3];
        
        $doc = ORM::for_table('module_imobles_deals_docs')
            ->table_alias('d')
            ->select_expr('d.*, c.name as category_name')
            ->left_outer_join('module_imobles_docs_category', 'd.category_id = c.id', 'c')
            ->find_one($docID);
        $this->ui->assign('doc', $doc);
       
        $css_arr = array(
            'modal',
            'dropzone/dropzone',
            's2/css/select2.min',
            'dp/dist/datepicker.min',
            '/../../../application/plugins/module_imobles/assets/css/default'
        );
        
        $js_arr = array(
            'modal',
            'dropzone/dropzone',
            's2/js/select2.min',
            'dp/dist/datepicker.min',
            '/../../../application/plugins/module_imobles/assets/js/dealsdocs-list'            
        );

        $assets_css = Asset::css($css_arr, date("Y-m-d"));
        $assets_js = Asset::js($js_arr, date("Y-m-d h:i:s") . "001");

        $this->ui->assign('xheader', $assets_css);
        $this->ui->assign('xfooter', $assets_js);
        
        $this->ui->display($this->_pd . '/views/deals/docs_form.tpl');
    }


    public function docs_post(){
        try {
            $model = new DealsDocsModel();
            $data = $_POST;
            $msg = '';
            if (empty($data['id']) && empty(@$_FILES['file']['name'])) {
                $msg .= $this->_L['module imobles doc'] . $this->_L['is required'];
            }
            if (empty($msg)) {

                if (isset($data['id']) && $data['id'] > 0) {
                    $doc = $model->getOne($data['id']);
                    $doc = $model->updateDocs($data, $doc);
                    if (empty($doc->errorFile)) {
                        _notify($this->_L['module imobles doc'] . " " . $this->_L['Edited Successfull'], 's');
                    } else {
                        echo isset($this->_L[$doc->errorFile]) ? $this->_L[$doc->errorFile] : $doc->errorFile;
                        exit();
                    }
                } else {
                    $docs = $model->createDocs($data);
                    if (empty($docs->errorFile)) {
                        _notify($this->_L['module imobles doc'] . " " . $this->_L['Add Successfull'], 's');
                    } else {
                        echo isset($this->_L[$docs->errorFile]) ? $this->_L[$docs->errorFile] : $docs->errorFile;
                        exit();
                    }
                }
                $msg = "OK";
            }
        } catch (Exception $ex) {
            echo ($ex->getMessage());
        }
        echo $msg;
    }

    public function docs_delete(){
        $model = new DealsDocsModel();
        $doc = $model->getOne(_post('id'));
        $model->deleteDoc($doc);
        $retorno['status'] = 'OK';
        echo json_encode($retorno);
    }

}