<?php

class crmClass {

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

    public function __construct() {
        $this->db = DBConnectorFactory::loadDriver();
        $this->logged_id = Session::getUserID();
        $this->current_date = date('Y-m-d H:i:s');
    }

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

    public function excelDatetoPHPDate($excel_date_time, $format = 'Y-m-d H:i:s') {
        $tmp_timezone = date_default_timezone_get();
        date_default_timezone_set('GMT');   //or it will add zone hours
        //25569 date diff between January 1, 1900 [excel] and 1970 [unix] (including 19 leap years)
        //86400 seconds per day
        //        $format_date_time = date($format, (($excel_date_time - 25569) * 86400));
        $format_date_time = date($format, $this->excelToPhpConvert($excel_date_time));
        date_default_timezone_set($tmp_timezone);
        return $format_date_time;
    }

    public function excelToPhpConvert($dateValue, $format = 'Y-m-d H:i:s') {
        //PHPExcel_Shared_Date::ExcelToPHP
        //        if (self::$_excelBaseDate == self::CALENDAR_WINDOWS_1900) {
        $my_excelBaseDate = 25569;
        //	Adjust for the spurious 29-Feb-1900 (Day 60)
        if ($dateValue < 60) {
            --$my_excelBaseDate;
        }
        //        } else {
        //            $my_excelBaseDate = 24107;
        //        }
        // Perform conversion
        if ($dateValue >= 1) {
            $utcDays = $dateValue - $my_excelBaseDate;
            $returnValue = round($utcDays * 86400);
            if (($returnValue <= PHP_INT_MAX) && ($returnValue >= -PHP_INT_MAX)) {
                $returnValue = (integer) $returnValue;
            }
        } else {
            $hours = round($dateValue * 24);
            $mins = round($dateValue * 1440) - round($hours * 60);
            $secs = round($dateValue * 86400) - round($hours * 3600) - round($mins * 60);
            $returnValue = (integer) gmmktime($hours, $mins, $secs);
        }
        return $returnValue;
    }

    public function submitCustomer() {
        $startDate = date('Y-m-d');
        $organization_id = $this->info['organization_id'];
        $this->db->query('START TRANSACTION');
        $auto_create_acct_cust_from_crm = smc_session()['default_setting']['auto_create_acct_cust_from_crm'];
        $this->info['organization_longitude'] = empty($this->info['organization_longitude']) ? NULL : $this->info['organization_longitude'];
        $this->info['organization_latitude'] = empty($this->info['organization_latitude']) ? NULL : $this->info['organization_latitude'];
        $this->info['region_id'] = empty($this->info['region_id']) ? NULL : $this->info['region_id'];
        $this->info['region_id'] = empty($this->info['region_id']) ? NULL : $this->info['region_id'];
        $this->info['organization_group_id'] = !empty($this->info['organization_group_id']) ? $this->info['organization_group_id'] : NULL;
        $this->info['job_description'] = !empty($this->info['job_description']) ? $this->info['job_description'] : NULL;
        if ($organization_id == 0) {
            $this->db->prepare_Stmt('INSERT INTO tbl_customer_organization
            (organization_email,organization_customer_name,orgaization_work_phone,
            orgaization_mobile_phone,city_id,organization_address,start_date,
            organization_longitude,organization_latitude,region_id,
            created_by,date_created,last_modified,organization_group_id,job_description)
                                     VALUES
                                    (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
                                         ', [&$this->info['organization_email'], &$this->info['organization_customer_name'],
                &$this->info['orgaization_work_phone'], &$this->info['orgaization_mobile_phone'],
                &$this->info['city_id'], &$this->info['organization_address'],
                &$startDate, &$this->info['organization_longitude'],
                &$this->info['organization_latitude'], &$this->info['region_id'],
                &$this->logged_id, &$this->current_date,
                &$this->current_date, &$this->info['organization_group_id'], &$this->info['job_description']]);
            $organization_id = $this->db->id();
            $this->db->prepare_Stmt('INSERT INTO tbl_contacts
                                    (contact_name,contact_work_phone,contact_mobile_phone,contact_email,
                                    organization_id,is_primary,created_by,date_created,last_modified)
                                     VALUES
                                    (?,?,?,?,?,1,?,?,?)
                                         ', [&$this->info['contact_name'], &$this->info['contact_work_phone'],
                &$this->info['contact_mobile_phone'],
                &$this->info['contact_email'], &$organization_id,
                &$this->logged_id, &$this->current_date, &$this->current_date]);
            if ($auto_create_acct_cust_from_crm == 1) {
                $local_currency = smc_session()['local_currency'];
                $default_class_id = smc_session()['default_setting']['pricing_class_id'];
                $this->db->prepare_Stmt('INSERT 
                                    INTO 
                                        tbl_customers 
                                    (
                                        Customer_Name,
                                        Customer_Email,
                                        Customer_Work_Phone,
                                        Customer_Mobile,
                                        Customer_Country_ID,
                                        Customer_City_ID,
                                        Customer_Address,
                                        Customer_Lng,
                                        Customer_Lat,
                                        region_id,
                                        Organization_id,
                                        Currency_ID,
                                        Class_ID,
                                        customer_group_id,
                                    )
                                        VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', [ &$this->info['organization_customer_name'],
                    &$this->info['organization_email'],
                    &$this->info['orgaization_work_phone'],
                    &$this->info['orgaization_mobile_phone'],
                    &$this->info['country_id'],
                    &$this->info['city_id'],
                    &$this->info['organization_address'],
                    &$this->info['organization_longitude'],
                    &$this->info['organization_latitude'],
                    &$this->info['region_id'], &$organization_id,
                    &$local_currency, &$default_class_id,
                    &$this->info['organization_group_id'],

                        ]
                );
            }
        } else {
            $this->db->prepare_Stmt('UPDATE tbl_customer_organization
                                                 SET
                                                        organization_customer_name =?,
                                                        organization_email =?,
                                                        orgaization_work_phone =?,
                                                        orgaization_mobile_phone =?,
                                                        city_id =?,
                                                        organization_address =?,
                                                        organization_longitude =?,
                                                        organization_latitude =?,
                                                        region_id =?,
                                                        modified_by = ?,
                                                        last_modified = ?,
                                                        organization_group_id = ?,
                                                        job_description = ?
                                                 WHERE 
                                                        organization_id =?

                                         ', [&$this->info['organization_customer_name'],
                &$this->info['organization_email'],
                &$this->info['orgaization_work_phone'],
                &$this->info['orgaization_mobile_phone'],
                &$this->info['city_id'],
                &$this->info['organization_address'],
                &$this->info['organization_longitude'],
                &$this->info['organization_latitude'], &$this->info['region_id'],
                &$this->logged_id, &$this->current_date,
                &$this->info['organization_group_id'],
                &$this->info['job_description'],
                &$organization_id]);
            $this->db->prepare_Stmt('
                                    UPDATE 
                                        tbl_customers
                                    SET 
                                        Customer_Name            = ?,
                                        Customer_Email           = ?,
                                        Customer_Work_Phone      = ?,
                                        Customer_Mobile          = ?,
                                        Customer_Country_ID      = ?,
                                        Customer_City_ID         = ?,
                                        Customer_Address         = ?,
                                        Customer_Lng             = ?,
                                        Customer_Lat             = ?,
                                        region_id                = ?,
                                        customer_group_id        = ?
                                    WHERE 
                                        Organization_id         = ?', [&$this->info['organization_customer_name'],
                &$this->info['organization_email'],
                &$this->info['orgaization_work_phone'],
                &$this->info['orgaization_mobile_phone'],
                &$this->info['country_id'],
                &$this->info['city_id'],
                &$this->info['organization_address'],
                &$this->info['organization_longitude'],
                &$this->info['organization_latitude'],
                &$this->info['region_id'],
                &$this->info['organization_group_id'],
                &$organization_id
            ]);
            $query = $this->db->query('select contact_id from tbl_contacts where is_primary = 1 and organization_id ="' . $organization_id . '"');
            if ($this->db->rowsno($query) != 0) {
                $this->db->prepare_Stmt('UPDATE tbl_contacts
                                                 SET
                                                        contact_name =?,
                                                        contact_work_phone =?,
                                                        contact_mobile_phone =?,
                                                        contact_email =?,
                                                        modified_by = ?,
                                                        last_modified = ?

                                                 WHERE 
                                                        organization_id =?
                                                and
                                                        is_primary = 1

                                         ', [&$this->info['contact_name'], &$this->info['contact_work_phone'],
                    &$this->info['contact_mobile_phone'], &$this->info['contact_email'],
                    &$this->logged_id, &$this->current_date, &$organization_id]);
            } else {
                $this->db->prepare_Stmt('INSERT INTO tbl_contacts
                                     (contact_name,contact_work_phone,contact_mobile_phone,contact_email,organization_id,is_primary,created_by,date_created,last_modified
                                     )
                                     VALUES
                                    (?,?,?,?,?,1,?,?,?)
                                         ', [&$this->info['contact_name'], &$this->info['contact_work_phone'],
                    &$this->info['contact_mobile_phone'], &$this->info['contact_email'], &$organization_id,
                    &$this->logged_id, &$this->current_date, &$this->current_date]);
            }
        }
        $error = $this->db->error();
        if (empty($error)) {
            $customer_directory = dirBase . "uploads/crm_customers/{$organization_id}";
            if (!empty($_FILES['customer_attachments']['name'][0])) {
                if (!file_exists($customer_directory))
                    mkdir($customer_directory, 0777, true);

                $all_main_files = $_FILES['customer_attachments'];
                foreach ($all_main_files['name'] as $i => $original_name) {
                    $file_name = microtime(true) . '.' . pathinfo($original_name, PATHINFO_EXTENSION);
                    $destination_file = $customer_directory . "/" . $file_name;
                    move_uploaded_file($all_main_files['tmp_name'][$i], $destination_file);
                }
            }
            if (!empty($this->info['deleted_attachments'])) {
                $deleted_attachments_array = json_decode($this->info['deleted_attachments'], "true");
                foreach ($deleted_attachments_array as $deleted_attachment) {
                    $del_attach_path = dirBase . "uploads/crm_customers/{$organization_id}/{$deleted_attachment}";
                    if (file_exists($del_attach_path))
                        unlink(dirBase . "uploads/crm_customers/{$organization_id}/{$deleted_attachment}");
                }
            }
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    public function submitContact() {
        $contact_id = $this->info['contact_id'];
        if ($contact_id == 0) {
            $this->db->prepare_Stmt('INSERT INTO tbl_contacts
                                     (contact_name,contact_work_phone,contact_mobile_phone,contact_email,organization_id,created_by,date_created,last_modified
                                     )
                                     VALUES
                                    (?,?,?,?,?,?,?,?)
                                         ', [&$this->info['contact_name'], &$this->info['contact_work_phone'],
                &$this->info['contact_mobile_phone'],
                &$this->info['contact_email'], &$this->info['organization_id'],
                &$this->logged_id, &$this->current_date, &$this->current_date]);
        } else {
            $this->db->prepare_Stmt('UPDATE tbl_contacts
                                                 SET
                                                        contact_name =?,
                                                        contact_work_phone =?,
                                                        contact_mobile_phone =?,
                                                        contact_email =?,
                                                        organization_id =?,
                                                        modified_by = ?,
                                                        last_modified = ?
                                                 WHERE 
                                                        contact_id =?

                                         ', [&$this->info['contact_name'], &$this->info['contact_work_phone'], &$this->info['contact_mobile_phone'], &$this->info['contact_email'],
                &$this->info['organization_id'], &$this->logged_id,
                &$this->current_date, &$contact_id]);
        }
        $error = $this->db->error();
        return $error;
    }

    public function organizationSearch($term = null, $page, $limit) {
        $data = array();
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        $data_filter = array();

        if (!is_null($term)) {
            $cond .=' where (organization_customer_name LIKE ? )';
            $data_filter[] = "%" . $term . "%";
        }
        $query = $this->db->prepare_Stmt('select 
                                    organization_id as id,
                                    organization_customer_name as text
                                from 
                                    tbl_customer_organization
                                ' . $cond . '
                                limit ' . ($page * $limit) . ',' . ($limit + 1), $data_filter);
        while ($row = $this->db->assoc($query)) {
            if (!empty($this->info['contact'])) {
                $contact_data = self::get_contact_data($row['id']);
                $row['contact_name'] = $contact_data['contact_name'];
                $row['contact_id'] = $contact_data['contact_id'];
                $row['count'] = $contact_data['count'];
            }
            $data['data'][] = $row;
        }
        if (count($data['data']) > $limit) {
            array_pop($data['data']);
            $data['is_more'] = true;
        }
        return $data;
    }

    public function contactsSearch($term = null, $page, $limit, $organization_id = null) {
        $data = array();
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        $data_filter = array();
        if (!is_null($term)) {
            $cond .=' and (cts.contact_name LIKE ? or org.organization_name LIKE ? )' . (!is_null($organization_id) ? ' and org.organization_id = "' . $organization_id . '"' : '');
            $data_filter[] = "%" . $term . "%";
            $data_filter[] = "%" . $term . "%";
        }
        $query = $this->db->prepare_Stmt('select 
                                    cts.contact_id as id,
                                    cts.contact_name as text
                                from 
                                    tbl_contacts cts
                                inner join 
                                    tbl_customer_organization org
                                using(organization_id) 
                                where 1=1
                                ' . $cond . ' limit ' . ($page * $limit) . ',' . ($limit + 1), $data_filter);
        while ($row = $this->db->assoc($query)) {
            $data['data'][] = $row;
        }
        if (count($data['data']) > $limit) {
            array_pop($data['data']);
            $data['is_more'] = true;
        }
        return $data;
    }

    public function get_contact_data($organization_id) {
        $data = array('count' => 0, 'contact_name' => '', 'contact_id' => '');
        $contact_query = $this->db->query('
        select * from  tbl_contacts where organization_id ="' . $organization_id . '"');
        $contact_nb = $this->db->rowsno($contact_query);
        if ($contact_nb == 1) {
            $contact_data = $this->db->assoc($contact_query);
            $data['count'] = 1;
            $data['contact_name'] = $contact_data['contact_name'];
            $data['contact_id'] = $contact_data['contact_id'];
        }
        return $data;
    }

    public function pipelineData() {
        $data = array();
        $query_stages = $this->db->query('select * from tbl_deals_stage order by stage_id asc');
        while ($row = $this->db->assoc($query_stages)) {
            $data[$row['stage_id']] = $row;
        }
        $query = $this->db->query('select  *  from  pipeline_data ');
        while ($row = $this->db->assoc($query)) {
            if (!isset($data[$row['stage_id']][$row['deal_id']])) {
                $data[$row['stage_id']]['deals'][$row['deal_id']] = $row;
                $data[$row['stage_id']]['deals'][$row['deal_id']]['activities'] = array();
            }
            if (!is_null($row['activity_id'])) {
                $data[$row['stage_id']]['deals'][$row['deal_id']]['activities'][] = ['activity_id' => $row['activity_id'],
                    'activity_title' => $row['activity_title'],
                    'activity_notes' => $row['activity_notes'],
                    'end_date' => $row['end_date']
                ];
            }
        }
        return $data;
    }

    public function submitDeal() {
        $currentDateTime = date('Y-m-d H:i:s');
        $deal_id = $this->info['deal_id'];
        $this->db->query('START TRANSACTION');
        $query = $this->db->query('select max(order_id) as max_order  from tbl_deals where stage_id ="' . $this->info['current_stage_id'] . '"');
        $row = $this->db->assoc($query)['max_order'];
        $this->info['currency_id'] = $this->info['deal_value'] == "" ? '' : $this->info['currency_id'];
        $order_id = $row + 1;
        if ($deal_id == 0) {
            $this->db->prepare_Stmt('INSERT INTO tbl_deals
                                     (deal_title,deal_date,deal_state,stage_id,contact_id,deal_value,currency_id,deal_open,
                                     order_id,created_by,date_created,last_modified,date_last_contacted,Cat_ID,deal_probability
                                     )
                                     VALUES
                                    (?,?,"Incomplete",?,?,?,?,"Activated",?,?,?,?,?,?,?)
                                         ', [&$this->info['deal_title'], &$currentDateTime,
                &$this->info['current_stage_id'], &$this->info['contact_id'],
                &$this->info['deal_value'], &$this->info['currency_id'], &$order_id,
                &$this->logged_id, &$this->current_date, &$this->current_date,
                &$currentDateTime, &$this->info['Cat_ID'], &$this->info['deal_probability']]);
            $deal_id = $this->db->id();
            $this->db->prepare_Stmt('INSERT INTO tbl_deals_log
                                     (deal_id,log_date)
                                     VALUES
                                    (?,?)
                                         ', [&$deal_id, &$currentDateTime]);
            $stage_query = $this->db->query('select * from tbl_deals_stage');
            while ($stage_row = $this->db->assoc($stage_query)) {
                $newCurrentDateTime = $this->info['current_stage_id'] == $stage_row['stage_id'] ? $currentDateTime : '';
                $this->db->prepare_Stmt('INSERT INTO tbl_deals_progress
                                     (stage_id,deal_id,progress_date)
                                     VALUES
                                    (?,?,?)
                                         ', [&$stage_row['stage_id'], &$deal_id, &$newCurrentDateTime]);
            }
            $this->db->prepare_Stmt('update tbl_customer_organization set is_contacted = 1 where organization_id = ?', [&$this->info['organization_id']]);
        } else {
            $old_stage_query = $this->db->query('select stage_id from tbl_deals where deal_id ="' . $deal_id . '"');
            $old_stage_id = $this->db->assoc($old_stage_query)['stage_id'];
            $this->db->prepare_Stmt('UPDATE tbl_deals
                                                 SET
                                                        deal_title =?,
                                                        stage_id =?,
                                                        currency_id =?,
                                                        contact_id =?,
                                                        deal_value =?,
                                                        modified_by = ?,
                                                        last_modified = ?,
                                                        date_last_contacted = ?,
                                                        Cat_ID = ?,
                                                        deal_probability = ?
                                                 WHERE 
                                                        deal_id =?

                                         ', [&$this->info['deal_title'], &$this->info['current_stage_id'],
                &$this->info['currency_id'], &$this->info['contact_id'],
                &$this->info['deal_value'],
                &$this->logged_id, &$this->current_date, &$currentDateTime,
                &$this->info['Cat_ID'], &$this->info['deal_probability'], &$deal_id]);
            $this->db->prepare_Stmt('update tbl_deals_progress set progress_date= ? where deal_id = ? and stage_id = ? ', [&$this->current_date, &$deal_id, &$this->info['current_stage_id']]);
            if ($old_stage_id != $this->info['current_stage_id']) {
                $this->db->prepare_Stmt('INSERT INTO tbl_deals_log
                                     (deal_id,old_stage_id,new_stage_id,log_date)
                                     VALUES
                                    (?,?,?,?)
                                         ', [&$deal_id, &$old_stage_id,
                    &$this->info['current_stage_id'], &$currentDateTime]);
            }
        }
        $error = $this->db->error();
        if (empty($error)) {
            $deal_directory = dirBase . "uploads/deals/{$deal_id}";
            if (!empty($_FILES['deal_attachments']['name'][0])) {
                if (!file_exists($deal_directory))
                    mkdir($deal_directory, 0777, true);

                $all_main_files = $_FILES['deal_attachments'];
                foreach ($all_main_files['name'] as $i => $original_name) {
                    $file_name = microtime(true) . '.' . pathinfo($original_name, PATHINFO_EXTENSION);
                    $destination_file = $deal_directory . "/" . $file_name;
                    move_uploaded_file($all_main_files['tmp_name'][$i], $destination_file);
                }
            }
            if (!empty($this->info['deleted_attachments'])) {
                $deleted_attachments_array = json_decode($this->info['deleted_attachments'], "true");
                foreach ($deleted_attachments_array as $deleted_attachment) {
                    $del_attach_path = dirBase . "uploads/deals/{$deal_id}/{$deleted_attachment}";
                    if (file_exists($del_attach_path))
                        unlink(dirBase . "uploads/deals/{$deal_id}/{$deleted_attachment}");
                }
            }
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    public function deleteDeal() {
        $this->db->query('START TRANSACTION');
        $this->db->prepare_Stmt('Delete from tbl_deals_state_log WHERE deal_id =?', [&$this->info['deal_id']]);
        $this->db->prepare_Stmt('Delete from tbl_deals_progress WHERE deal_id =?', [&$this->info['deal_id']]);
        $this->db->prepare_Stmt('Delete from tbl_deals_notes WHERE deal_id =?', [&$this->info['deal_id']]);
        $this->db->prepare_Stmt('Delete from tbl_deals_log WHERE deal_id =?', [&$this->info['deal_id']]);
        $this->db->prepare_Stmt('Delete from tbl_deals_activity WHERE deal_id =?', [&$this->info['deal_id']]);
        $this->db->prepare_Stmt('Delete from tbl_deals WHERE deal_id =?', [&$this->info['deal_id']]);
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    public function submitActivity() {
        $this->db->query('START TRANSACTION');
        //        echo '<pre>';
        //        print_r($this->info);
        $activity_id = $this->info['activity_id'];
        $currentDateTime = date('Y-m-d H:i:s');
        $activityChecked = $this->info['activity_checked'];
        $activityCheckedAt = $activityChecked == 1 ? $currentDateTime : NULL;
        $duration_array = explode(':', empty($this->info['activity_duration']) ? "00:15" : $this->info['activity_duration']);
        $start_date = date('Y-m-d H:i:s', strtotime($this->info['end_date'] . ' ' . $this->info['current_time']));
        $end_date = date('Y-m-d H:i:s', strtotime($start_date . '+ ' . intval($duration_array[0]) . ' hours + ' . intval($duration_array[1]) . ' minutes'));
        $this->info['contact_id'] = empty($this->info['contact_id']) ? NULL : $this->info['contact_id'];
        $this->info['deal_id'] = empty($this->info['deal_id']) ? NULL : $this->info['deal_id'];
        if ($activity_id == 0) {
            $this->db->prepare_Stmt('INSERT INTO tbl_deals_activity
                                     (
                                        activity_title,
                                        activity_notes,
                                        activity_type_id,
                                        organization_id,
                                        contact_id,
                                        deal_id,
                                        start_date,
                                        activity_duration,
                                        activity_created_at,
                                        end_date,
                                        activity_checked,
                                        activity_checked_at,
                                        created_by,
                                        date_created,
                                        last_modified)
                                     VALUES
                                    (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
                                ', [&$this->info['activity_title'], &$this->info['activity_notes'],
                &$this->info['current_activity_type_id'], &$this->info['organization_id'],
                &$this->info['contact_id'], &$this->info['deal_id'],
                &$start_date, &$this->info['activity_duration'],
                &$currentDateTime, &$end_date,
                &$activityChecked, &$activityCheckedAt,
                &$this->logged_id, &$this->current_date, &$this->current_date]);
            $this->db->prepare_Stmt('update tbl_customer_organization set is_contacted = 1 where organization_id = ?', [&$this->info['organization_id']]);
        } else {
            $this->db->prepare_Stmt('UPDATE tbl_deals_activity
                                                 SET
                                                        activity_title =?,
                                                        activity_notes =?,
                                                        activity_type_id =?,
                                                        organization_id =?,
                                                        contact_id =?,
                                                        deal_id =?,
                                                        start_date =?,
                                                        activity_checked =?,
                                                        activity_duration =?,
                                                        end_date =?,
                                                        activity_checked_at =?,
                                                        modified_by =?,
                                                        last_modified = ?
                                                 WHERE 
                                                        activity_id =?

                            ', [&$this->info['activity_title'], &$this->info['activity_notes'],
                &$this->info['current_activity_type_id'], &$this->info['organization_id'],
                &$this->info['contact_id'], &$this->info['deal_id'],
                &$start_date, &$activityChecked,
                &$this->info['activity_duration'], &$end_date,
                &$this->info['activity_checked_at'], &$this->logged_id,
                &$this->current_date, &$activity_id]);
        }
        if (!empty($this->info['deal_id'])) {
            $this->db->prepare_Stmt('UPDATE tbl_deals set date_last_contacted = ?  where deal_id = ? ', [&$currentDateTime, &$this->info['deal_id']]);
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    public function submitCurrentCall() {
        $this->db->query('START TRANSACTION');
        $currentDateTime = date("Y-m-d H:i:s");
        $duration_array = explode(':', empty($this->info['current_activity_duration']) ? "00:15" : $this->info['current_activity_duration']);
        $start_date = date('Y-m-d H:i:s', strtotime($this->info['current_date'] . ' ' . $this->info['current_call_time']));
        $end_date = date('Y-m-d H:i:s', strtotime($start_date . '+ ' . intval($duration_array[0]) . ' hours + ' . intval($duration_array[1]) . ' minutes'));
        $this->info['contact_id'] = empty($this->info['contact_id']) ? NULL : $this->info['contact_id'];
        $this->info['deal_id'] = empty($this->info['deal_id']) ? NULL : $this->info['deal_id'];
        $this->db->prepare_Stmt('INSERT INTO tbl_deals_activity
                                     (
                                        activity_title,
                                        activity_notes,
                                        activity_type_id,
                                        action_type_id,
                                        organization_id,
                                        contact_id,
                                        deal_id,
                                        start_date,
                                        activity_duration,
                                        activity_checked,
                                        activity_checked_at,
                                        activity_created_at,
                                        end_date,
                                        created_by,
                                        date_created,
                                        last_modified)
                                     VALUES
                                    (?,?,1,?,?,?,?,?,?,1,?,?,?,?,?,?)
                                ', [&$this->info['call_title'],
            &$this->info['call_note'],
            &$this->info['action_type_id'],
            &$this->info['organization_id'],
            &$this->info['contact_id'],
            &$this->info['deal_id'],
            &$start_date,
            &$this->info['current_activity_duration'],
            &$currentDateTime,
            &$currentDateTime, &$end_date,
            &$this->logged_id, &$this->current_date, &$this->current_date]);
        if ($this->info['action_type_id'] == 3) {
            $activity_id = $this->db->id();
            $next_start_date = date('Y-m-d H:i:s', strtotime($this->info['current_next_date'] . ' ' . $this->info['current_call_time']));
            $next_end_date = date('Y-m-d H:i:s', strtotime($next_start_date . '+ ' . intval($duration_array[0]) . ' hours + ' . intval($duration_array[1]) . ' minutes'));
            $this->db->prepare_Stmt('INSERT INTO tbl_deals_activity
                                     (
                                        activity_title,
                                        activity_notes,
                                        activity_type_id,
                                        organization_id,
                                        start_date,
                                        activity_duration,
                                        activity_created_at,
                                        end_date,
                                        created_by,
                                        date_created,
                                        last_modified,
                                        parent_id)
                                     VALUES
                                    (?,?,1,?,?,?,?,?,?,?,?,?)
                                ', [&$this->info['call_title'],
                &$this->info['call_note'],
                &$this->info['organization_id'],
                &$next_start_date,
                &$this->info['current_activity_duration'],
                &$currentDateTime, &$next_end_date,
                &$this->logged_id, &$this->current_date, &$this->current_date,
                &$activity_id]);
        }
        $this->db->prepare_Stmt('update tbl_customer_organization set is_contacted = 1 where organization_id = ?', [&$this->info['organization_id']]);
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    public function submitNote() {
        $this->db->query('START TRANSACTION');
        $currentDateTime = date('Y-m-d H:i:s');
        $this->db->prepare_Stmt('INSERT INTO tbl_deals_notes
                                     (note_content,deal_id,organization_id,contact_id,note_date)
                                     VALUES
                                    (?,?,?,?,?)
                            ', [&$this->info['note_content'],
            &$this->info['deal_id'],
            &$this->info['organization_id'],
            &$this->info['contact_id'],
            &$currentDateTime]);
        $this->db->prepare_Stmt('UPDATE tbl_deals set date_last_contacted = ?  where deal_id = ? ', [&$currentDateTime, &$this->info['deal_id']]);
        $this->db->prepare_Stmt('update tbl_customer_organization set is_contacted = 1 where organization_id = ?', [&$this->info['organization_id']]);
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    public function getDealProductCat($category) {
        $query = $this->db->query('select 
                                    Cat_ID as id,
                                    Cat_Name as text
                                from 
                                    tbl_product_cat 
                                where Cat_ID IN (' . $category . ')');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function getDealProductCategory($category) {
        $data = '';
        $query = $this->db->query('select 
                                    Cat_ID as id,
                                    Cat_Name as text
                                from 
                                    tbl_product_cat 
                                where Cat_ID IN (' . $category . ')');
        while ($row = $this->db->assoc($query)) {
            $data .= $row['text'] . ',';
        }
        return substr($data, 0, -1);
    }

    public function getDealsContact($term = null, $page, $limit, $organization_id = null, $contact_id = null) {
        $data = array();
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        $data_filter = array();
        if (!is_null($term)) {
            $cond .=' where ( deals.deal_title LIKE ?)' . (!is_null($organization_id) ? ' and org.organization_id = "' . $organization_id . '"' : '') . (!is_null($contact_id) ? ' and cts.contact_id = "' . $contact_id . '"' : '');
            $data_filter[] = "%" . $term . "%";
        }
        $query = $this->db->prepare_Stmt('select 
                                    deals.deal_id as id,
                                    deals.deal_title as text
                                from 
                                    tbl_deals deals
                                inner join 
                                    tbl_contacts cts
                                using(contact_id)
                                inner join 
                                    tbl_customer_organization org 
                                using(organization_id)
                                ' . $cond . '
                                limit ' . ($page * $limit) . ',' . ($limit + 1), $data_filter);
        while ($row = $this->db->assoc($query)) {
            $data['data'][] = $row;
        }
        if (count($data['data']) > $limit) {
            array_pop($data['data']);
            $data['is_more'] = true;
        }
        return $data;
    }

    public function getAllActivities() {
        $data = array();
        $condition = '';
        $type = $this->info['type'];
        $status_id = $this->info['status_id'];
        $condition .= $type == 0 ? '' : ' and activity_type_id = "' . $type . '"';
        $condition .=!is_numeric($status_id) ? '' : ' and activity_checked = "' . $status_id . '"';
        if (!empty($this->info['current_date']) && ($this->info['current_date'] == 'planned' || $this->info['current_date'] == 'overdue')) {
            if ($this->info['current_date'] == 'planned')
                $condition .= ' and start > "' . date('Y-m-d h:i:s') . '" ';
            else
                $condition .= ' and start < "' . date('Y-m-d h:i:s') . '" ';
        }
        $query = $this->db->query('select  *  from  all_activities where  
                 (   Date(start) >= "' . $this->info['start'] . '"
                and
                    Date(start) <= "' . $this->info['end'] . '"
                  ' . $condition . '  )
                    or
                    ( Date(end) >= "' . $this->info['start'] . '"
                and
                    Date(end) <=  "' . $this->info['end'] . '" ' . $condition . ') ');
        while ($row = $this->db->assoc($query)) {
            $row['color'] = date('Y-m-d h:i:s', strtotime($row['end'])) > date('Y-m-d h:i:s') ? 'green' : 'red';
            $data[] = $row;
        }
        return $data;
    }

    public function updateActivityStatus() {
        $this->db->query('START TRANSACTION');
        $currentDateTime = date('Y-m-d H:i:s');
        $this->db->prepare_Stmt('UPDATE tbl_deals_activity
                                                 SET
                                                        activity_checked =?,
                                                        activity_checked_at =?
                                                 WHERE 
                                                        activity_id =?

                                         ', [&$this->info['activity_checked'], &$currentDateTime, &$this->info['activity_id']]);
        $deal_id = CommonFunctions::SpecificAttribute('tbl_deals_activity', 'activity_id', $this->info['activity_id'], 'deal_id');
        $this->db->prepare_Stmt('UPDATE tbl_deals set date_last_contacted = ?  where deal_id = ? ', [&$currentDateTime, &$deal_id]);

        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    public function getProfileLog() {
        $condition = '';
        $is_inv_ret = true;
        if (!empty($this->info['organization_id']))
            $condition .= ' and organization_id = "' . $this->info['organization_id'] . '"';
        if (!empty($this->info['contact_id'])) {
            $condition .= ' and contact_id = "' . $this->info['contact_id'] . '"';
            $is_inv_ret = false;
        }
        if (!empty($this->info['deal_id'])) {
            $condition .= ' and deal_id = "' . $this->info['deal_id'] . '"';
            $is_inv_ret = false;
        }
        $data = array('activity' => array(), 'log' => array(), 'state_log' => array(), 'notes' => array(), 'inv_ret' => array());
        $activity_query = $this->db->query('select * from all_activities where activity_checked = 1' . $condition . ' Order by activity_created_at desc');
        while ($activity_row = $this->db->assoc($activity_query)) {
            $activity_row['action_type_name'] = !empty($activity_row['action_type_name']) ? CommonLanguage::getMessage($activity_row['action_type_name']) : '';
            $data['activity'][] = $activity_row;
        }
        $log_query = $this->db->query('select * from all_logs where 1 = 1' . $condition . ' Order by log_date desc');
        while ($log_row = $this->db->assoc($log_query)) {
            $data['log'][] = $log_row;
        }
        $state_log_query = $this->db->query('select * from all_state_logs where 1=1 ' . $condition . ' Order by state_log_date desc ');
        while ($state_log_row = $this->db->assoc($state_log_query)) {
            $data['state_log'][] = $state_log_row;
        }
        $notes_query = $this->db->query('select * from all_notes where 1=1 ' . $condition . ' Order by note_date desc ');
        while ($notes_row = $this->db->assoc($notes_query)) {
            $data['notes'][] = $notes_row;
        }
        if ($is_inv_ret) {
            $customer_query = $this->db->query('select Customer_ID from tbl_customers
            where Organization_id = "' . $this->info['organization_id'] . '"');
            $customer_row = $this->db->assoc($customer_query);
            if (!empty($customer_row['Customer_ID'])) {
                $inv_query = $this->db->query('
        select ID as inv_ret_nb,date_created,current_net_total as inv_ret_total,Currency_Symbol 
        from sales_invoice_list where Person_ID ="' . $customer_row['Customer_ID'] . '" Order by date_created desc ');
                while ($inv_row = $this->db->assoc($inv_query)) {
                    $inv_row['current_type'] = CommonLanguage::getMessage('invoices');
                    $inv_row['current_net_total'] = CommonFunctions::formatNumber($inv_row['inv_ret_total']);
                    $data['inv_ret'][] = $inv_row;
                }
                $return_query = $this->db->query('
        select return_nb as inv_ret_nb,date_created,current_net_total,Currency_Symbol
        from customer_return_list where Person_ID ="' . $customer_row['Customer_ID'] . '" Order by date_created desc ');
                while ($return_row = $this->db->assoc($return_query)) {
                    $return_row['current_type'] = CommonLanguage::getMessage('return');
                    $return_row['current_net_total'] = CommonFunctions::formatNumber($return_row['current_net_total']);
                    $data['inv_ret'][] = $return_row;
                }
            }
        }
        return $data;
    }

    public function submitDealState() {
        $logged_id = Session::getUserID();
        $this->db->query('START TRANSACTION');
        $currentDateTime = date('Y-m-d H:i:s');
        $query = $this->db->query('select o.*,co.Country_ID from tbl_customer_organization o
                                        join tbl_all_cities c on c.ID = o.city_id 
                                        join tbl_all_countries co on co.Code = c.CountryCode
                where organization_id = "' . $this->info['org_id'] . '"');
        $org = $this->db->assoc($query);
        $this->db->prepare_Stmt('UPDATE tbl_deals
                                                 SET
                                                        deal_state =?,
                                                        reason_for_loss =?,
                                                        date_last_contacted = ?
                                                 WHERE 
                                                        deal_id =?
                            ', [&$this->info['deal_state'], &$this->info['reason_for_loss'],
            &$currentDateTime, &$this->info['deal_id']]);
        $this->db->prepare_Stmt('INSERT INTO  tbl_deals_state_log 
                                        (deal_state,deal_id,state_log_date) VALUES 
                                        (?,?,?)
                                         ', [&$this->info['deal_state'], &$this->info['deal_id'], &$currentDateTime]);
        if ($org['is_moved'] == 0 && $this->info['deal_state'] === 'Won') {
            $local_currency = smc_session()['local_currency'];
            $default_class_id = smc_session()['default_setting']['pricing_class_id'];
            $this->db->prepare_Stmt('INSERT 
                                    INTO 
                                        tbl_customers 
                                    (
                                        Customer_Name,
                                        Customer_Email,
                                        Customer_Work_Phone,
                                        Customer_Mobile,
                                        Customer_Country_ID,
                                        Customer_City_ID,
                                        Customer_Address,
                                        Customer_Lng,
                                        Customer_Lat,
                                        region_id,
                                        Organization_id,
                                        Currency_ID,
                                        Class_ID,
                                        customer_group_id,
                                        Logged_ID
                                    )
                                        VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', [&$org['organization_customer_name'],
                &$org['organization_email'],
                &$org['orgaization_work_phone'],
                &$org['orgaization_mobile_phone'],
                &$org['Country_ID'],
                &$org['city_id'],
                &$org['organization_address'],
                &$org['organization_longitude'],
                &$org['organization_latitude'],
                &$org['region_id'], &$this->info['org_id'],
                &$local_currency, &$default_class_id,
                &$org['organization_group_id'],
                &$logged_id]
            );
            $this->db->prepare_Stmt('update tbl_customer_organization set is_moved =1 where organization_id = ?', [&$this->info['org_id']]);
        }


        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    public function updateDealStage() {
        $this->db->query('START TRANSACTION');
        $currentDateTime = date('Y-m-d H:i:s');
        $old_stage = CommonFunctions::CommonView('tbl_deals', ['deal_id'], [$this->info['deal_id']])[0]['stage_id'];
        $this->db->prepare_Stmt('UPDATE tbl_deals_progress
                                                set
                                                    progress_date = ?
                                                 WHERE 
                                                        deal_id =?
                                                AND
                                                    stage_id = ?

                                         ', [&$currentDateTime, &$this->info['deal_id'], &$this->info['stage_id']]);
        $this->db->prepare_Stmt('INSERT INTO tbl_deals_log
                                     (deal_id,old_stage_id,new_stage_id,log_date)
                                     VALUES
                                    (?,?,?,?)
                                         ', [&$this->info['deal_id'],
            &$old_stage,
            &$this->info['stage_id'],
            &$currentDateTime]);
        $this->db->prepare_Stmt('UPDATE tbl_deals
                                                 SET
                                                        stage_id = ?,
                                                        order_id = ?,
                                                        date_last_contacted = ?
                                                 WHERE 
                                                        deal_id =?

                                         ', [&$this->info['stage_id'], &$this->info['order_id'],
            &$currentDateTime, &$this->info['deal_id']]);

        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    public function getPlannedOverdueActivities($divided = null) {
        $data = array();
        $condition = '';
        $condition .= array_key_exists('deal_id', $this->info) && $this->info['deal_id'] != 0 ? ' and deal_id="' . $this->info['deal_id'] . '" ' : '';
        $condition .= array_key_exists('organization_id', $this->info) && $this->info['organization_id'] != 0 ? ' and organization_id="' . $this->info['organization_id'] . '" ' : '';
        $condition .= array_key_exists('contact_id', $this->info) && $this->info['contact_id'] != 0 ? ' and contact_id="' . $this->info['contact_id'] . '" ' : '';
        $query = $this->db->query('select  *  from  all_activities where activity_checked = 0 ' . $condition . ' Order by  activity_created_at desc');
        if (is_null($divided)) {
            $data['planned'] = array();
            $data['overdue'] = array();
        }

        $currentDateTime = date('Y-m-d H:i:s');
        while ($row = $this->db->assoc($query)) {
            $row['action_type_name'] = !empty($row['action_type_name']) ? CommonLanguage::getMessage($row['action_type_name']) : '';
            if (is_null($divided)) {
                if ($row['end'] < $currentDateTime) {
                    $data['overdue'][] = $row;
                } else {
                    $data['planned'][] = $row;
                }
            } else {
                $data[] = $row;
            }
        }
        return $data;
    }

    public function uploadExcelSheet() {
        require dirname(__FILE__) . '/../PHPExcel/PHPExcel/IOFactory.php';
        $Fileextension = $_FILES['Document_File_Name']['tmp_name'];
        $excel = PHPExcel_IOFactory::load($Fileextension);
        $writer = PHPExcel_IOFactory::createWriter($excel, 'CSV');

        $writer->setDelimiter(";");
        $writer->setEnclosure("");
        self::convertxlsxtoCSV($Fileextension, 'output.csv');
        //
        $today_date = date('Y-m-d');
        $current_date_time = date('Y-m-d H:i:s');
        $time_now = date('H:i:s');
        $this->db->query('START TRANSACTION');
        $activity_type_arr = array();
        $query = $this->db->query('SELECT * FROM tbl_activity_type');
        while ($row = $this->db->assoc($query)) {
            $activity_type_arr[strtolower($row['activity_type_title'])] = $row['activity_type_id'];
        }
        $product_category_arr = array();
        $productCategoryQuery = $this->db->query('SELECT * FROM tbl_product_cat');
        while ($row2 = $this->db->assoc($productCategoryQuery)) {
            $product_category_arr[strtolower($row2['Cat_Name'])] = $row2['Cat_ID'];
        }
        if (($getfile = fopen('output.csv', "r"))) {
            fgetcsv($getfile, 1000, ",");   //escape first row/header
            $row_no = 1;
            while (($data = fgetcsv($getfile, 1000, ","))) {
                //trim the data
                array_walk_recursive($data, function(&$value, $index) {
                    $value = trim($value);
                });
                $col1 = $data[0]; // organization_name
                $col2 = $data[1]; // contact_first_name
                $col3 = $data[2]; // contact_last_name
                $col4 = $data[3]; // contact mobile
                $col5 = $data[4]; // contact landline
                $col6 = $data[5]; // organization address
                $col7 = $data[6]; // last contacted
                $last_contacted = '';

                if ($col7 != '') {
                    $last_contacted = $this->excelDatetoPHPDate($col7, 'Y-m-d');
                }
                $col8 = $data[7]; // note on company
                $col9 = $data[8]; // time for next_activity
                $next_activity = '';
                if ($col9 !== '') {
                    $next_activity = $this->excelDatetoPHPDate($col9, 'Y-m-d');
                }
                $col10 = $data[9]; // activity_type
                $col11 = $data[10]; // activity description
                $col12 = $data[11]; // create flage
                $col13 = $data[12]; // product category
                $col14 = empty($data[13]) ? NULL : $data[13]; // product description
                $is_contacted = empty($last_contacted) ? 0 : 1;
                //                inset the row to the temp table
                if ($col1 != '') {
                    $this->db->prepare_Stmt('INSERT INTO tbl_customer_organization
                                     (organization_name,organization_customer_name,
                                     organization_address,city_id,start_date,created_by,date_created,last_modified,
                                     job_description,is_contacted,orgaization_mobile_phone)
                                     VALUES
                                    (?,?,?,4080,?,?,?,?,?,?,?)
                                         ', [&$col1, &$col1, &$col6, &$today_date, &$this->logged_id,
                        &$this->current_date, &$this->current_date, &$col14, &$is_contacted,
                        &$col4]);
                    $organization_id = $this->db->id();
                    $deal_id = 0;
                    $contact_id = 0;
                    if ($col2 != '') {
                        $contact_name = $col2 . ' ' . $col3;
                        $this->db->prepare_Stmt('INSERT INTO  tbl_contacts
                                     (contact_name,contact_mobile_phone,organization_id,is_primary,
                                     created_by,date_created,last_modified)
                                     VALUES
                                    (?,?,?,1,?,?,?)
                                         ', [&$contact_name, &$col5, &$organization_id,
                            &$this->logged_id, &$this->current_date, &$this->current_date]);
                        $contact_id = $this->db->id();
                        if ($last_contacted != '' && $col12 == "yes") {
                            $deal_title = $col1 . ' Deal';
                            $deal_date = $last_contacted . ' ' . $time_now;
                            $this->db->prepare_Stmt('INSERT INTO  tbl_deals
                                     (deal_title,deal_date,deal_state,stage_id,contact_id,date_last_contacted,
                                     created_by,date_created,last_modified,Cat_ID)
                                     VALUES
                                    (?,?,"Incomplete",1,?,?,?,?,?,?)
                                         ', [&$deal_title, &$deal_date, &$contact_id, &$last_contacted,
                                &$this->logged_id, &$this->current_date,
                                &$this->current_date, &$product_category_arr[$col13]]);
                            $deal_id = $this->db->id();
                            $stage_query = $this->db->query('select * from tbl_deals_stage');
                            while ($stage_row = $this->db->assoc($stage_query)) {
                                $progress_date = $stage_row['stage_id'] == 1 ? $current_date_time : "0000-00-00 00:00:00";
                                $this->db->prepare_Stmt('INSERT INTO  tbl_deals_progress
                                     (stage_id,deal_id,progress_date)
                                     VALUES
                                    (?,?,?)
                                         ', [&$stage_row['stage_id'], &$deal_id, &$progress_date]);
                            }
                        }
                        if ($col10 != '') {
                            $activity_date_time = $next_activity . ' ' . $time_now;
                            $this->db->prepare_Stmt('INSERT INTO   tbl_deals_activity
                                     (activity_title,activity_notes,activity_type_id,deal_id,
                                     start_date,activity_duration,end_date,activity_checked,
                                     activity_created_at,organization_id,contact_id,
                                     created_by,date_created,last_modified)
                                     VALUES
                                    (?,?,?,?,?,"0:15",?,0,?,?,?,?,?,?)
                                         ', [&$col10, &$col11, &$activity_type_arr[$col10], &$deal_id,
                                &$activity_date_time, &$activity_date_time,
                                &$current_date_time, &$organization_id, &$contact_id,
                                &$this->logged_id, &$this->current_date, &$this->current_date]);
                        }
                    }
                    if ($col8 != '') {
                        $note_date_time = $last_contacted . ' ' . $time_now;
                        $this->db->prepare_Stmt('INSERT INTO tbl_deals_notes
                                     (note_content,deal_id,organization_id,contact_id,note_date)
                                     VALUES
                                    (?,?,?,?,?)
                                         ', [&$col8, &$deal_id, &$organization_id, &$contact_id, &$note_date_time]);
                    }
                }


                $row_no++;
            }
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    public function convertxlsxtoCSV($infile, $outfile) {
        $fileType = PHPExcel_IOFactory::identify($infile);
        $objReader = PHPExcel_IOFactory::createReader($fileType);

        $objReader->setReadDataOnly(true);
        $objPHPExcel = $objReader->load($infile);

        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'CSV');
        $objWriter->save($outfile);
    }

    public function add_edit_contra() {
        $this->db->query('START TRANSACTION');
        $date_time = date('Y-m-d H:i:s');
        $logged_id = smc_session()['user_logged_id'];
        $archive_error = array();
        $archive_id = 0;
        $old_archive = '';
        $contra_id = $this->info['contra_id'];
        if ($this->info['contra_id'] == 0) {
            $contra_nb = $this->generate_contra_nb();
            $data = [&$this->info['customer_id'], &$contra_nb, &$this->info['contra_date'],
                &$this->info['contra_amount'], &$this->info['currency_id'], &$date_time, &$date_time,
                &$logged_id, &$logged_id];
            $this->db->prepare_Stmt('INSERT INTO `tbl_contra`( `customer_id`, `contra_nb`, `contra_date`,
                `contra_amount`, `currency_id`, `date_created`, `date_time`, `Logged_ID`, `created_logged_id`)
                values(?,?,?,?,?,?,?,?,?)', $data);
            $contra_id = $this->db->id();
        } else {
            $data = [&$this->info['customer_id'], &$this->info['contra_date'],
                &$this->info['contra_amount'], &$this->info['currency_id'], &$date_time,
                &$logged_id, &$this->info['contra_id']];
            $this->db->prepare_Stmt('update `tbl_contra` set `customer_id`=?, `contra_date`=?,
                `contra_amount`=?, `currency_id`=?, `date_time`=?, `Logged_ID`=? where contra_id = ?
                ', $data);
            $query = $this->db->query('select archive_id,archive_name from tbl_archive 
                where main_id = "' . $this->info['contra_id'] . '" and main_type="con"');
            $row = $this->db->assoc($query);
            $old_archive = !empty($row) ? $row['archive_name'] : '';
            $archive_id = !empty($row) ? $row['archive_id'] : 0;
        }

        if (!empty($_FILES['archive_name']['name'])) {
            $archive_data = array('main_id' => $contra_id, 'main_type' => 'con',
                'archive_name' => $_FILES['archive_name']['name'], 'archive_id' => $archive_id,
                'old_archive_name' => $old_archive);
            $archive_error = GeneralClass::submit_archive($archive_data, true);
        }
        $error = $this->db->error();
        if (empty($error) && empty($archive_error))
            $this->db->query('COMMIT');
        else
            $this->db->query('ROLLBACK');
        return $error;
    }

    public function generate_contra_nb() {
        $contra_nb = 1;
        $query = $this->db->query('select contra_nb from tbl_contra order by contra_nb desc limit 1');
        $row = $this->db->assoc($query);
        if (!empty($row['contra_nb']))
            $contra_nb = $row['contra_nb'] + 1;
        return $contra_nb;
    }

    public function contraSearch($term = null, $page, $limit) {
        $data = array();
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        $data_filter = array();
        if (!is_null($term)) {
            $cond .= ' and (contra_date LIKE ? or contra_nb LIKE ? or Customer_Name LIKE ? )';
            $data_filter[] = "%" . $term . "%";
            $data_filter[] = "%" . $term . "%";
            $data_filter[] = "%" . $term . "%";
        }
        $query = $this->db->prepare_Stmt('select 
                                    concat_ws(" - ",Customer_Name,contra_date,contra_nb) as text,
                                    contra_id as id 
                                from  crm_contra_list
                                where 1=1
                                ' . $cond . '
                                limit ' . ($page * $limit) . ',' . ($limit + 1), $data_filter);
        while ($row = $this->db->assoc($query)) {
            $data['data'][] = $row;
        }
        if (count($data['data']) > $limit) {
            array_pop($data['data']);
            $data['is_more'] = true;
        }
        return $data;
    }

    public function UpdateActivityTime() {
        $this->db->query('START TRANSACTION');
        $currentDateTime = date('Y-m-d H:i:s');
        $start_date = new DateTime($this->info['start_date']);
        $interval = $start_date->diff(new DateTime($this->info['end_date']));
        $duration = $interval->format('%h') . ':' . $interval->format('%i');
        $this->db->prepare_Stmt('UPDATE tbl_deals_activity
                                                     SET
                                                            start_date =?,
                                                            end_date =?,
                                                            activity_duration = ?
                                                     WHERE 
                                                            activity_id =?

                                             ', [&$this->info['start_date'], &$this->info['end_date'],
            &$duration, &$this->info['activity_id']]);
        $deal_id = CommonFunctions::SpecificAttribute('tbl_deals_activity', 'activity_id', $this->info['activity_id'], 'deal_id');
        if (!empty($deal_id)) {
            $this->db->prepare_Stmt('UPDATE tbl_deals set date_last_contacted = ?  where deal_id = ? ', [&$currentDateTime, &$deal_id]);
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    public function updateCustomerPotiential() {
        $this->db->prepare_Stmt('UPDATE tbl_customer_organization set is_potiential = ?  where organization_id = ? ', [&$this->info['is_potiential'], &$this->info['organization_id']]);
        $error = $this->db->error();
        return $error;
    }

    public function submit_general_contact() {
        $contact_id = $this->info['contact_id'];
        if ($contact_id == 0) {
            $this->db->prepare_Stmt('INSERT INTO `tbl_general_contact`( `contact_name`, `contact_job`,
                `contact_phone`, `contact_mobile`, `contact_note`) values
                                    (?,?,?,?,?)
                                         ', [&$this->info['contact_name'], &$this->info['contact_job'],
                &$this->info['contact_phone'],
                &$this->info['contact_mobile'], &$this->info['contact_note']]);
        } else {
            $this->db->prepare_Stmt('UPDATE tbl_general_contact
                                                 SET
                                                        contact_name =?,
                                                        contact_job =?,
                                                        contact_phone =?,
                                                        contact_mobile =?,
                                                        contact_note =?
                                                 WHERE 
                                                        contact_id =?

                                         ', [&$this->info['contact_name'], &$this->info['contact_job'],
                &$this->info['contact_phone'],
                &$this->info['contact_mobile'], &$this->info['contact_note'],
                &$contact_id]);
        }
        $error = $this->db->error();
        return $error;
    }

    public function delete_general_contact($id) {
        $this->db->prepare_Stmt('delete from tbl_general_contact where contact_id = ?', [$id]);
        $error = $this->db->error();
        return $error;
    }

}
