<?php

class JobOrder {

    protected $info = array();
    private $db;
    //    private $perm;
    private $year_id;
    private $date_time;

    public function __construct() {
        $this->db = DBConnectorFactory::loadDriver();
        //        $this->perm = new Permissions(Session::getUserID());
        $this->year_id = Session::getCurrentYearID();
        $this->logged_id = smc_session()['user_logged_id'];
        $this->date_time = date('Y-m-d H:i:s');
    }

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

    public function submitJobOrderOption() {
        $this->db->query('START TRANSACTION');
        $option_id = $this->info['option_id'];
        $option_details = json_decode($this->info['option_details'], true);
        $option_details_ids = json_decode($this->info['option_details_ids'], true);
        $option_detail_cond = !empty($option_details_ids) ? ' and option_detail_id not in(' . implode(',', $option_details_ids) . ') ' : '';
        if ($option_id == 0) {
            $this->db->prepare_Stmt('INSERT INTO tbl_job_order_option (option_title,sort_order,is_published,is_title_hidden,is_content_hidden) VALUES(?,?,?,?,?)', [&$this->info['option_title'],&$this->info['sort_order'], &$this->info['is_published'], &$this->info['is_title_hidden'], &$this->info['is_content_hidden']]);
            $option_id = $this->db->id();
        } else {
            $this->db->prepare_Stmt('Update  tbl_job_order_option set option_title = ?,sort_order= ?, is_published=?,is_title_hidden = ?,is_content_hidden = ? where option_id= ?', [&$this->info['option_title'],&$this->info['sort_order'], &$this->info['is_published'],&$this->info['is_title_hidden'],&$this->info['is_content_hidden'], &$option_id]);
            $this->db->prepare_Stmt('delete from tbl_job_order_option_details where option_id = ?' . $option_detail_cond, [&$option_id]);
        }
        foreach ($option_details as $details) {
            $details['is_required'] = $details['type_id'] == 3 ? 0 : $details['is_required'];
            if ($details['option_detail_id'] == 0) {
                $this->db->prepare_Stmt('INSERT INTO tbl_job_order_option_details 
                                    (option_detail_name,option_detail_sort_order,type_id,option_id,is_required)
                                     VALUES(?,?,?,?,?)', [&$details['option_detail_name'],&$details['option_detail_sort_order'], &$details['type_id'],
                                                          &$option_id, &$details['is_required']]);
                $details['option_detail_id'] = $this->db->id();
            } else {
                $this->db->prepare_Stmt('update  tbl_job_order_option_details 
                                    set option_detail_name = ?,option_detail_sort_order = ?,type_id = ?,is_required = ? where option_detail_id = ? ', [&$details['option_detail_name'],&$details['option_detail_sort_order'], &$details['type_id'], &$details['is_required'], &$details['option_detail_id']]);
                if (empty($details['option_detail_value_editable']))
                    $this->db->prepare_Stmt('delete from tbl_job_order_option_details_value where option_detail_id = ? ', [&$details['option_detail_id']]);
            }
            if (($details['type_id'] == 2 || $details['type_id'] == 3 || $details['type_id'] == 7) && !empty($details['option_detail_value'] && empty($details['option_detail_value_editable']))) {
                $option_values_array = explode(",", $details['option_detail_value']);
                foreach ($option_values_array as $current_value) {
                    $this->db->prepare_Stmt('INSERT INTO tbl_job_order_option_details_value 
                                    (detail_value_title,option_detail_id)
                                     VALUES(?,?)', [&$current_value, &$details['option_detail_id']]);
                }
            }
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }
    public function deleteJobOrderOption() {
        $option_detail_ids = array();
        $job_order_options_query = $this->db->query('select option_detail_id from tbl_job_order_option_details where option_id= "' . $this->info['option_id'] . '" ');
        while ($row = $this->db->assoc($job_order_options_query)) {
            $option_detail_ids[] = $row['option_detail_id'];
        }
        $job_order_details_options_query = $this->db->query('select * from tbl_job_order_details_options where  option_detail_id in (' . implode(",", $option_detail_ids) . ')');
        $job_order_details_options_count = $this->db->rowsno($job_order_details_options_query);
        if ($job_order_details_options_count > 0) {
            return [CommonLanguage::getMessage('unable_delete_job_order_options')];
        }
        $this->db->prepare_Stmt('Delete from tbl_job_order_option WHERE option_id = ?', [&$this->info['option_id']]);
        $error = $this->db->error();
        return $error;
    }
    public function getSpecificJobOrderOptionData() {
        $id = $this->info['option_id'];
        $data = array('main' => array(),
                      'details' => array());
        $job_order_option_main = $this->db->query('select *
                                                    from tbl_job_order_option options 
                                                   where option_id = "' . $id . '"');
        $data['main'] = $this->db->assoc($job_order_option_main);
        $job_order_option_details = $this->db->query('select details.*,
                                                          types.type_name,
                                                          options.detail_option_id
                                                    from tbl_job_order_option_details details 
                                                   inner join tbl_job_order_option_type types using(type_id)
                                                   left join tbl_job_order_details_options options
                                                   on options.option_detail_id = details.option_detail_id
                                                   where details.option_id = "' . $id . '" ');
        while ($row = $this->db->assoc($job_order_option_details)) {
            $data['details'][$row['option_detail_id']] = $row;
        }
        $job_order_option_details_value = $this->db->query('select details_value.detail_value_id,
                                                                   details_value.detail_value_title,
                                                                   details_value.option_detail_id
                                                    from tbl_job_order_option_details_value details_value
                                                    where details_value.option_detail_id in (' . implode(",", array_keys($data['details'])) . ')');
        while ($row = $this->db->assoc($job_order_option_details_value)) {
            $data['details'][$row['option_detail_id']]['detail_value_title'][] = $row['detail_value_title'];
        }
        $data['details'] = array_values($data['details']);
        return $data;
    }

    public function submitJobOrderStage() {
        $stage_id = $this->info['stage_id'];
        if ($stage_id == 0) {
            $this->db->prepare_Stmt('INSERT INTO tbl_job_order_stage
                                     (stage_title,sort_nb)
                                     VALUES
                                    (?,?)
                                         ', [&$this->info['stage_title'], &$this->info['sort_nb']]);
        } else {
            $this->db->prepare_Stmt('UPDATE tbl_job_order_stage
                                                 SET
                                                        stage_title = ?,
                                                        sort_nb = ?
                                                 WHERE 
                                                        stage_id =?

                                         ', [&$this->info['stage_title'], &$this->info['sort_nb'], &$stage_id]);
        }
        $error = $this->db->error();
        return $error;
    }
    public function deleteJobOrderStage() {
        $job_order_stage_query = $this->db->query('select * from tbl_job_order 
                                                   where stage_id = "'.$this->info['stage_id'].'" ');
        $job_order_stage_count = $this->db->rowsno($job_order_stage_query);
        if ($job_order_stage_count > 0) {
            return [CommonLanguage::getMessage('unable_delete_job_order_stage')];
        }
        $this->db->prepare_Stmt('Delete from tbl_job_order_stage WHERE stage_id =? ', [&$this->info['stage_id']]);
        $error = $this->db->error();
        return $error;
    } 

    public function submitJobOrderBlock() {
        $block_id = $this->info['block_id'];
        if ($block_id == 0) {
            $this->db->prepare_Stmt('INSERT INTO  tbl_job_order_block
                                     (block_name,sort_nb,option_ids)
                                     VALUES
                                    (?,?,?)
                                         ', [&$this->info['block_name'], 
                                             &$this->info['sort_nb'], &$this->info['option_ids']]);
        } else {
            $this->db->prepare_Stmt('UPDATE  tbl_job_order_block
                                                 SET
                                                        block_name = ?,
                                                        sort_nb = ?,
                                                        option_ids = ?
                                                 WHERE 
                                                        block_id =?

                                         ', [&$this->info['block_name'], &$this->info['sort_nb'],
                                             &$this->info['option_ids'], &$block_id]);
        }
        $error = $this->db->error();
        return $error;
    }
    public function deleteJobOrderBlock() {
        $this->db->prepare_Stmt('Delete from  tbl_job_order_block WHERE block_id =? ', [&$this->info['block_id']]);
        $error = $this->db->error();
        return $error;
    }

    public function companyJobOrderSearch() {
        $data = array();
        $cond = '';
        $data_filter = array();
        if (!is_null($this->info['term'])) {
            $cond .= ' and (company LIKE ? )';
            $data_filter[] = "%" . $this->info['term'] . "%";
        }
        $query = $this->db->prepare_Stmt('SELECT distinct(company) as text
                                    FROM tbl_job_order  where 1=1
                                    ' . $cond . ' limit 30',$data_filter);
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }
    public function autocompleteJoborderOptionSearch() {
        $data = array();
        $cond = '';
        $data_filter = array();
        if (!is_null($this->info['term'])) {
            $cond .= ' and (details_option.option_detail_value LIKE ? )';
            $data_filter[] = "%" . $this->info['term'] . "%";
        }
        $query = $this->db->prepare_Stmt('SELECT details_option.option_detail_value as text
                                    FROM tbl_job_order_details_options details_option
                                    inner join tbl_job_order_option_details option_details
                                    on option_details.option_detail_id =details_option.option_detail_id
                                    and option_details.type_id="5"
                                    where 1=1
                                    ' . $cond . ' limit 30',$data_filter);
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function getAllJobOrderOptions(){
        $job_order_options = array();
        $job_order_option_main = $this->db->query('select *
                                                    from tbl_job_order_option options order by sort_order asc');
        while ($row = $this->db->assoc($job_order_option_main)) {
            $job_order_options[$row['option_id']] = $row;
        }
        $job_order_option_details = $this->db->query('select details.*,
                                                          types.type_name
                                                    from tbl_job_order_option_details details 
                                                   inner join tbl_job_order_option_type types using(type_id)
                                                   order by details.option_detail_sort_order asc');
        while ($row = $this->db->assoc($job_order_option_details)) {
            $job_order_options[$row['option_id']]['options'][$row['option_detail_id']] = $row;
        }
        $job_order_option_details_value = $this->db->query('select details_value.detail_value_id,
                                                                   details_value.detail_value_title,
                                                                   details_value.option_detail_id,
                                                                   details.option_id
                                                    from tbl_job_order_option_details_value details_value
                                                    inner join tbl_job_order_option_details details
                                                    on details.option_detail_id = details_value.option_detail_id ');
        while ($row = $this->db->assoc($job_order_option_details_value)) {
            $job_order_options[$row['option_id']]['options'][$row['option_detail_id']]['detail_value_title'][] = $row['detail_value_title'];
        }
        return $job_order_options;
    }
    public function getJobOrderBlockOptionData() {
        $data = array();
        $job_order_options = $this->getAllJobOrderOptions();
        //Yousif
        $n=count($job_order_options);
        //echo "<script>alert('$n');</script>";
        //Yousif
        $condition = '';
        if(!empty($this->info['block_id']))
            $condition .= ' and block_id = "'.$this->info['block_id'].'"';
        $job_order_blocks_query = $this->db->query('select * from tbl_job_order_block where 1=1 '.$condition.' order by sort_nb asc');
        if($n!=0){
            while($row = $this->db->assoc($job_order_blocks_query)){
                $row['options'] = array();
                $option_ids_array = explode(",",$row['option_ids']);
                foreach($option_ids_array as $option){
                    $row['options'][] = $job_order_options[$option];
                }
                $data [] = $row; 
            }
        }
        return $data;
    }
    public function submitJobOrder() {
        $is_actual = 0;
        $check = CommonFunctions::check_lock_data(['type' => 'job_order', 'start_date' => $this->info['job_order_date'], 'is_actual' => $is_actual]);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        $this->db->query('START TRANSACTION');
        $job_order_id = $this->info['job_order_id'];
        if ($job_order_id == 0) {
            $job_order_number = self::getMaxJobOrderNumber();
            $this->db->prepare_Stmt('INSERT INTO tbl_job_order
                                (job_order_nb,customer_id,company,print_ref,job_order_date,person_in_charge,
                                note,conform_date,delivery_date,price,first_payment,date_created,date_time,
                                created_logged_id,logged_id,current_year_id) 
                                VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
                                ', [&$job_order_number, &$this->info['customer_id'], &$this->info['company'],
                                    &$this->info['print_ref'], &$this->info['job_order_date'],
                                    &$this->info['person_in_charge'], &$this->info['note'],
                                    &$this->info['conform_date'], &$this->info['delivery_date'],
                                    &$this->info['price'],&$this->info['first_payment'], &$this->date_time,
                                    &$this->date_time, &$this->logged_id,
                                    &$this->logged_id, &$this->year_id]);
            $job_order_id = $this->db->id();
        } else {
            $this->db->prepare_Stmt('update  tbl_job_order
                                set customer_id = ?,company = ?,print_ref = ?,job_order_date = ?,
                                person_in_charge = ?,note = ?,conform_date = ?,delivery_date = ?,price = ?,
                                first_payment = ? ,is_published = ? ,date_time = ?,logged_id = ? where job_order_id = ?
                                ', [&$this->info['customer_id'], &$this->info['company'],
                                    &$this->info['print_ref'], &$this->info['job_order_date'],
                                    &$this->info['person_in_charge'], &$this->info['note'],
                                    &$this->info['conform_date'], &$this->info['delivery_date'], &$this->info['price'],
                                    &$this->info['first_payment'], &$this->info['is_published'],
                                    &$this->date_time, &$this->logged_id, &$job_order_id]);
            $this->db->prepare_Stmt('delete from tbl_job_order_details where job_order_id = ? ', [&$job_order_id]);
            if (!empty($this->info['deleted_main_images'])) {
                $deleted_images_array = json_decode($this->info['deleted_main_images'], "true");
                foreach ($deleted_images_array as $deleted_image) {
                    $del_img_path = dirBase . "uploads/job_order/{$job_order_id}/main/{$deleted_image}";
                    if (file_exists($del_img_path))
                        unlink(dirBase . "uploads/job_order/{$job_order_id}/main/{$deleted_image}");
                }
            }
        }
        $option_details = json_decode($this->info['option_details'], true);
        $job_order_directory = dirBase . "uploads/job_order/{$job_order_id}";
        $job_order_main_directory = dirBase . "uploads/job_order/{$job_order_id}/main";
        $job_order_sub_directory = dirBase . "uploads/job_order/{$job_order_id}/sub";
        if (!empty($_FILES)) {
            if (!file_exists($job_order_directory))
                mkdir($job_order_directory, 0777, true);
            if (!file_exists($job_order_main_directory))
                mkdir($job_order_main_directory, 0777, true);
            if (!file_exists($job_order_sub_directory))
                mkdir($job_order_sub_directory, 0777, true);
        }
        //
        foreach ($option_details as $details) {
            $main_index = $details['index'];
            $block_id = $details['block_id'];
            $this->db->prepare_Stmt('INSERT INTO tbl_job_order_details
                                (block_id,detail_index,job_order_id) 
                                VALUES(?,?,?)
                                ', [&$block_id,&$main_index,&$job_order_id]);
            $order_detail_id = $this->db->id();
            foreach ($details['option_values'] as $detail_value) {
                if ($detail_value['type_id'] == 4) {
                    $current_main_indexes = 'option_detail_name_file_'.$block_id.'_' . $main_index . '_' . $detail_value['option_detail_id'];
                    $option_detail_value_name = '';
                    if (!empty($detail_value['old_image_data'])) {
                        $option_detail_image_value = array_diff(explode("##", $detail_value['old_image_data']), explode("##", $detail_value['deleted_image_data']));
                        $option_detail_value_name = implode("##", $option_detail_image_value);
                    }
                    if (!empty($_FILES[$current_main_indexes]['name'][0])) {
                        $all_main_files = $_FILES[$current_main_indexes];
                        foreach ($all_main_files['name'] as $i => $original_name) {
                            $file_name = $detail_value['option_detail_id'] . '_' . microtime(true) . '.' . pathinfo($original_name, PATHINFO_EXTENSION);
                            $destination_file = $job_order_sub_directory . "/" . $file_name;
                            $option_detail_value_name .= ($option_detail_value_name == '' ? '' : '##') . $file_name;
                            move_uploaded_file($all_main_files['tmp_name'][$i], $destination_file);
                        }
                    }
                    $this->db->prepare_Stmt('INSERT INTO tbl_job_order_details_options (order_detail_id,option_detail_id,option_detail_value) 
                                VALUES(?,?,?)', [&$order_detail_id, &$detail_value['option_detail_id'], &$option_detail_value_name]);
                    if (!empty($detail_value['deleted_image_data'])) {
                        $image_to_be_deleted_arr = explode("##", $detail_value['deleted_image_data']);
                        foreach ($image_to_be_deleted_arr as $current_image)
                            if (file_exists($job_order_sub_directory . "/{$current_image}"))
                                unlink($job_order_sub_directory . "/{$current_image}");
                    }
                } elseif (!empty($detail_value['option_detail_value']))
                    $this->db->prepare_Stmt('INSERT INTO tbl_job_order_details_options (order_detail_id,option_detail_id,option_detail_value) 
                                VALUES(?,?,?)', [&$order_detail_id, &$detail_value['option_detail_id'], &$detail_value['option_detail_value']]);
            }
        }
        $error = $this->db->error();
        if (empty($error)) {
            if (!empty($_FILES['job_order_main_images']['name'][0])) {
                $all_main_files = $_FILES['job_order_main_images'];
                foreach ($all_main_files['name'] as $i => $original_name) {
                    $file_name = microtime(true);
                    $destination_file = $job_order_main_directory . "/" . $file_name . '.' . pathinfo($original_name, PATHINFO_EXTENSION);
                    move_uploaded_file($all_main_files['tmp_name'][$i], $destination_file);
                }
            }
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

    public function deleteJobOrder() {
        if (smc_session()['year_closed'] == 1) {
            $error = array();
            $error[0] = CommonLanguage::getMessage('delete_year_alert');
            return $error;
        }
        $query = $this->db->query('select job_order_date from tbl_job_order WHERE job_order_id = "'.$this->info['job_order_id'].'"');
        $row = $this->db->assoc($query);
        $check = CommonFunctions::check_lock_data(['type' => 'job_order', 'start_date' => $row['job_order_date'], 'is_actual' => 0]);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        $this->db->prepare_Stmt('Delete from tbl_job_order WHERE job_order_id = ?', [&$this->info['job_order_id']]);
        $error = $this->db->error();
        return $error;
    }

    public function getMaxJobOrderNumber() {
        $query = $this->db->query('SELECT MAX(job_order_nb) AS Entry_ID 
            FROM tbl_job_order where current_year_id = "' . $this->year_id . '" ');
        $row = $this->db->assoc($query);
        $job_order_nb = !empty($row) ? $row['Entry_ID'] + 1 : 1;
        return $job_order_nb;
    }

    public function getJobOrderData($id) {
        $data = array('main' => array(),
                      'details' => array());
        $job_order_main = $this->db->query('select job_order.*,customer.organization_customer_name as customer_name
                                                    from tbl_job_order job_order 
                                                    inner join tbl_customer_organization  customer on job_order.customer_id = customer.organization_id
                                                   where job_order.job_order_id = "' . $id . '"');
        $data['main'] = $this->db->assoc($job_order_main);
        $job_order_options = $this->getAllJobOrderOptions();
        $job_order_blocks_query = $this->db->query('select blocks.*,details.detail_index
                                                    from tbl_job_order_details details 
                                                    inner join tbl_job_order_block blocks using(block_id)
                                                    where details.job_order_id = "'.$id.'"
                                                    order by blocks.sort_nb asc');
        while($row = $this->db->assoc($job_order_blocks_query)){
            $row['options'] = array();
            $option_ids_array = explode(",",$row['option_ids']);
            foreach($option_ids_array as $option){
                $row['options'][$option] = $job_order_options[$option];
            }
            $data ['details'][$row['block_id'].','.$row['detail_index']] = $row; 
        }
        $job_order_details_value = $this->db->query('select details_options.*,
                                                            details.order_detail_id,
                                                            details.block_id,
                                                            details.detail_index,
                                                            option_details.option_id
                                                    from tbl_job_order_details_options details_options
                                                    inner join tbl_job_order_option_details option_details
                                                    on option_details.option_detail_id = details_options.option_detail_id
                                                    inner join tbl_job_order_details details
                                                    on details.order_detail_id = details_options.order_detail_id
                                                    where details.job_order_id = "'.$id.'"
                                                    ');
        while ($row = $this->db->assoc($job_order_details_value)) {
            $data['details'][$row['block_id'].','.$row['detail_index']]['options'][$row['option_id']]['options'][$row['option_detail_id']]['current_value'] = $row['option_detail_value']; 
        }
        return $data;
    }

    public function updateJobOrderStage() {
        $this->db->prepare_Stmt('update tbl_job_order set stage_id = ?,is_published = 1 WHERE job_order_id = ?', [&$this->info['stage_id'], &$this->info['job_order_id']]);
        $error = $this->db->error();
        return $error;
    }

}
