<?php

class TicketingClass {

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

    public function __construct() {
        $this->db = DBConnectorFactory::loadDriver();
        $this->invoice = new Invoice();
        $this->depot = new WarehouseDepot();
        $this->permissions = Session::getPermissionsObject();
        $this->logged_id = smc_session()['user_logged_id'];
        $this->date_time = date('Y-m-d H:i:s');
    }

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

    public function submit_ticketing_type() {
        $ticketing_type_id = $this->info['ticketing_type_id'];
        if ($ticketing_type_id == 0) {
            $this->db->prepare_Stmt('INSERT INTO `tbl_ticketing_type`(`ticketing_type_name`) VALUES (?)
                                         ', [&$this->info['ticketing_type_name']]);
        } else {
            $this->db->prepare_Stmt('UPDATE tbl_ticketing_type
                                     SET    ticketing_type_name = ?
                                     WHERE  ticketing_type_id =?', [&$this->info['ticketing_type_name'], &$ticketing_type_id]);
        }
        $error = $this->db->error();
        return $error;
    }
    public function submit_maintenance_type() {
        $ticketing_type_id = $this->info['ticketing_type_id'];
        if ($ticketing_type_id == 0) {
            $this->db->prepare_Stmt('INSERT INTO `tbl_maintenance_type`(`maintenance_type_name`) VALUES (?)
                                         ', [&$this->info['ticketing_type_name']]);
        } else {
            $this->db->prepare_Stmt('UPDATE tbl_maintenance_type
                                     SET    maintenance_type_name = ?
                                     WHERE  maintenance_type_id =?', [&$this->info['ticketing_type_name'], &$ticketing_type_id]);
        }
        $error = $this->db->error();
        return $error;
    }
    public function delete_ticketing_type() {
        $query = $this->db->query('select * from tbl_ticketing where  ticketing_type_id = "' . $this->info['ticketing_type_id'] . '"');
        $row_count = $this->db->rowsno($query);
        if ($row_count > 0) {
            return [CommonLanguage::getMessage('ticketing_type_used')];
        }
        $this->db->prepare_Stmt('delete from tbl_ticketing_type where ticketing_type_id = ?', [&$this->info['ticketing_type_id']]);
        $error = $this->db->error();
        return $error;
    }
    public function delete_maintenance_type() {
        $query = $this->db->query('select * from tbl_maintenance where  maintenance_type_id = "' . $this->info['ticketing_type_id'] . '"');
        $row_count = $this->db->rowsno($query);
        if ($row_count > 0) {
            return [CommonLanguage::getMessage('maintenance_type_used')];
        }
        $this->db->prepare_Stmt('delete from tbl_maintenance_type where maintenance_type_id = ?', [&$this->info['ticketing_type_id']]);
        $error = $this->db->error();
        return $error;
    }

    public function submit_ticketing_station() {
        $ticketing_station_id = $this->info['ticketing_station_id'];
        if ($ticketing_station_id == 0) {
            $this->db->prepare_Stmt('INSERT INTO `tbl_ticketing_station`
            (`ticketing_station_name`, `ticketing_station_location`, `depot_id`) VALUES (?,?,?) ', [&$this->info['ticketing_station_name'], &$this->info['ticketing_station_location'], &$this->info['depot_id']]);
        } else {
            $this->db->prepare_Stmt('UPDATE tbl_ticketing_station
                                     SET    ticketing_station_name = ?,
                                            ticketing_station_location = ?,
                                            depot_id = ?
                                     WHERE  ticketing_station_id =?', [&$this->info['ticketing_station_name'], &$this->info['ticketing_station_location'],
                                                                       &$this->info['depot_id'], &$ticketing_station_id]);
        }
        $error = $this->db->error();
        return $error;
    }
    public function submit_maintenance_supporter() {
        $ticketing_station_id = $this->info['ticketing_station_id'];
        if ($ticketing_station_id == 0) {
            $this->db->prepare_Stmt('INSERT INTO `tbl_maintenance_supporter`
            (`maintenance_supporter_name`) VALUES (?) ', [&$this->info['ticketing_station_name']]);
        } else {
            $this->db->prepare_Stmt('UPDATE tbl_maintenance_supporter
                                     SET    maintenance_supporter_name = ?
                                     WHERE  maintenance_supporter_id =?', [&$this->info['ticketing_station_name'], &$ticketing_station_id]);
        }
        $error = $this->db->error();
        return $error;
    }
    public function delete_ticketing_station() {
        $query = $this->db->query('select * from tbl_ticketing where  ticketing_station_id = "' . $this->info['ticketing_station_id'] . '"');
        $row_count = $this->db->rowsno($query);
        if ($row_count > 0) {
            return [CommonLanguage::getMessage('ticketing_station_used')];
        }
        $this->db->prepare_Stmt('delete from tbl_ticketing_station where ticketing_station_id = ?', [&$this->info['ticketing_station_id']]);
        $error = $this->db->error();
        return $error;
    }
    public function delete_maintenance_supporter() {
        $query = $this->db->query('select * from tbl_ticketing where  ticketing_station_id = "' . $this->info['ticketing_station_id'] . '"');
        $row_count = $this->db->rowsno($query);
        if ($row_count > 0) {
            return [CommonLanguage::getMessage('maintenance_supporter_used')];
        }
        $this->db->prepare_Stmt('delete from tbl_maintenance_supporter where maintenance_supporter_id = ?', [&$this->info['ticketing_station_id']]);
        $error = $this->db->error();
        return $error;
    }

    public function submit_ticketing() {
        $error = token_unique::is_valid_token();
        if (!empty($error))
            return $error;
//        if (!empty($this->info['unique_id']) && !empty(smc_session()['default_setting']['is_production'])) {
//            if (!isset(smc_session()['duplicate_check'][$this->info['unique_id']])) {
//                $error = array();
//                $error[0] = CommonLanguage::getMessage('duplicate_record');
//                return $error;
//            } else {
//                unset(smc_session()['duplicate_check'][$this->info['unique_id']]);
//            }
//        }
        $consignment = json_decode($this->info['consignment'], true);
        $invoices = json_decode($this->info['invoices'], true);
        if (count($consignment) > 0 && empty(smc_session()['default_setting']['default_consignment_depot'])) {
            return [CommonLanguage::getMessage('default_consignment_depot')];
        }
        $this->db->query('START TRANSACTION');
        $ticketing_id = $this->info['ticketing_id'];
        $this->info['customer_additional_phone'] = empty($this->info['customer_additional_phone']) ? NULL : $this->info['customer_additional_phone'];
        $this->info['ticketing_station_id'] = empty($this->info['ticketing_station_id']) ? NULL : $this->info['ticketing_station_id'];
        if ($ticketing_id == 0) {
            if (!$this->permissions->hasPermission('show_ticketing_product_details')) {
                $this->info['paid_amount'] = NULL;
                $this->info['payment_account_id'] = NULL;
            }
            $date_finished = NULL;
            $finished_by = NULL;
            if ($this->info['ticketing_status_id'] == 3) {
                $date_finished = $this->date_time;
                $finished_by = $this->logged_id;
            }
            $ticketing_nb = self::get_max_ticketing_nb();
            $this->db->prepare_Stmt('INSERT INTO `tbl_ticketing`
            (`ticketing_nb`, `customer_id`, `customer_additional_phone`, `ticketing_type_id`, 
            `ticketing_station_id`,`paid_amount`, `payment_account_id`,
            `ticketing_date`, `ticketing_status_id`,`date_created`, `created_by`,
            `date_modified`, `modified_by`,`date_finished`,`finished_by`,`depot_id`,`currency_id`,`ticketing_schedule_date`,`assigned_user_id`)
            VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', [&$ticketing_nb, &$this->info['customer_id'], &$this->info['customer_additional_phone'],
                                                              &$this->info['ticketing_type_id'], &$this->info['ticketing_station_id'],
                                                              &$this->info['paid_amount'], &$this->info['payment_account_id'],
                                                              &$this->info['ticketing_date'], &$this->info['ticketing_status_id'],
                                                              &$this->date_time, &$this->logged_id, &$this->date_time, &$this->logged_id,
                                                              &$date_finished, &$finished_by, &$this->info['depot_id'], &$this->info['currency_id'], 
                                                              &$this->info['ticketing_schedule_date'], &$this->info['assigned_user_id']]);
            $ticketing_id = $this->db->id();
            $this->db->prepare_Stmt('INSERT INTO `tbl_ticketing_replies`
            (`ticketing_reply_description`, `created_by`, `date_created`, `modified_by`, `date_modified`, `ticketing_id`) 
            VALUES (?,?,?,?,?,?)', [&$this->info['ticketing_reply_description'], &$this->logged_id, &$this->date_time,
                                    &$this->logged_id, &$this->date_time, &$ticketing_id]);
            $ticketing_reply_id = $this->db->id();
            $this->db->prepare_Stmt('update tbl_ticketing set ticketing_reply_id = ? where ticketing_id = ?', [&$ticketing_reply_id, &$ticketing_id]);
        } else {
            $update_data = [&$this->info['customer_id'], &$this->info['customer_additional_phone'],
                            &$this->info['ticketing_type_id'], &$this->info['ticketing_station_id'],
                            &$this->info['paid_amount'], &$this->info['payment_account_id'],
                            &$this->info['ticketing_date'], &$this->info['ticketing_status_id'],
                            &$this->date_time, &$this->logged_id, &$this->info['depot_id'],
                            &$this->info['currency_id'],&$this->info['ticketing_schedule_date'],&$this->info['assigned_user_id']];
            $query_concat = '';
            if ($this->info['ticketing_status_id'] == 3) {
                if ($this->info['old_ticketing_status_id'] != 3) {
                    $date_finished = $this->date_time;
                    $finished_by = $this->logged_id;
                    $query_concat .= ' ,`date_finished` = ?,`finished_by` = ?  ';
                    $update_data[] = $date_finished;
                    $update_data[] = $finished_by;
                }
            }
            $update_data[] = &$ticketing_id;
            $this->db->prepare_Stmt('update `tbl_ticketing` set
             `customer_id` = ?, `customer_additional_phone` = ?, `ticketing_type_id` =?, 
            `ticketing_station_id` = ?,`paid_amount` = ?, `payment_account_id` =?,
            `ticketing_date` = ?, `ticketing_status_id` =?,
            `date_modified` = ?, `modified_by` = ?,depot_id =?,currency_id = ?,`ticketing_schedule_date` = ?,`assigned_user_id` =? ' . $query_concat . ' 
            where ticketing_id = ?', $update_data);
            $ticketing_reply_id = $this->info['ticketing_reply_id'];
        }
        if ($this->permissions->hasPermission('show_ticketing_product_details')) {
            $transfer_query = $this->db->query('select Request_ID from tbl_stock_request where ticketing_id = "' . $ticketing_id . '"');
            $transfer_row = $this->db->assoc($transfer_query);
            $this->depot->stop_transaction_start_end = true;
            $checking_return = ['qty_error' => CommonLanguage::getMessage('check_qty_exist'),
                                'serial_error' => CommonLanguage::getMessage('check_serial_exist'),
                                'lot_error' => CommonLanguage::getMessage('check_lot_exist'),
                                'expiry_error' => CommonLanguage::getMessage('check_expiry_exist'),
                               ];
            if (count($consignment) > 0) {
                $products = array();
                foreach ($consignment as $row) {
                    $products[] = ['Product_ID' => $row['product_id'],
                                   'UOM_ID' => $row['uom_id'],
                                   'Request_Qty' => $row['quantity']
                                  ];
                }
                $transfer_arr = ['Request_Date' => $this->info['ticketing_date'],
                                 'Request_To' => $this->info['depot_id'],
                                 'Request_From' => smc_session()['default_setting']['default_consignment_depot'],
                                 'Delivery_Date' => $this->info['ticketing_date'],
                                 'is_confirmed' => 1,
                                 'Status_ID' => 3,
                                 'note' => 'Ticketing Transfer',
                                 'customer_id' => $this->info['customer_id'],
                                 'ticketing_id' => $ticketing_id,
                                 'Products' => $products,
                                ];
                if (!empty($transfer_row['Request_ID'])) {
                    $transfer_arr['Request_ID'] = $transfer_row['Request_ID'];
                    $returned_data = $this->depot->setData($transfer_arr)->editRequest();
                } else {
                    $returned_data = $this->depot->setData($transfer_arr)->createRequest();
                }
                if (!is_numeric($returned_data[0])) {
                    if (in_array($returned_data[0], array_keys($checking_return)))
                        return ['Transfer :: ' . CommonLanguage::getMessage($checking_return[$returned_data[0]])];
                    else
                        return ['Transfer :: ' . $returned_data[0]];
                }
            } else {
                if (!empty($transfer_row['Request_ID'])) {
                    $returned_data = $this->depot->delete_request([$transfer_row['Request_ID']]);
                    if (!empty($returned_data[0])) {
                        $this->db->query('ROLLBACK');
                        return ['Transfer :: ' . $returned_data[0]];
                    }
                }
            }
            $this->invoice->stop_transaction_start_end = true;
            $invoice_query = $this->db->query('select Invoice_ID_Auto from tbl_invoices  where ticketing_id = "' . $ticketing_id . '"');
            $invoice_row = $this->db->assoc($invoice_query);
            if (count($invoices) > 0) {
                $products_invoice_arr = json_decode($this->info['products_invoice'], true);
                $product_info = array();
                $query_products = $this->db->query('select * from tbl_stock_products where Product_ID in (' . implode(",", array_unique($products_invoice_arr)) . ')');
                while ($row = $this->db->assoc($query_products)) {
                    $product_info[$row['Product_ID']] = $row;
                }
                $products = array();
                $sub_total = 0;
                $invoice_vat = 0;
                foreach ($invoices as $row) {
                    $itm_disc_per = empty($row['discount']) ? 0 : floatval($row['discount']);
                    $itm_disc_per = min($itm_disc_per, 100);
                    $vat_percent = $product_info[$row['product_id']]['vat_percent'];
                    $unit_price_bfr_vat = ((floatval($row['price']) * 100) / (100 + floatval($vat_percent)));
                    $unit_price_aftr_row_disc = (($unit_price_bfr_vat * (100 - $itm_disc_per)) / 100);
                    $vat_amount = (($unit_price_aftr_row_disc * $row['quantity'] * $vat_percent) / 100);   //vat after
                    $sub_total += (($row['quantity'] * $unit_price_aftr_row_disc)); //after row discount only
                    $invoice_vat += $vat_amount;
                    $products[] = ['Product_ID' => $row['product_id'],
                                   'UOM_ID' => $row['uom_id'],
                                   'Item_Quantity' => $row['quantity'],
                                   'Item_Price' => $unit_price_bfr_vat, //unit price before vat
                                   'vat_percent' => $vat_percent,
                                   'vat_val' => $vat_amount,
                                   'Item_Discount' => $itm_disc_per,
                                   'Discount_Amount' => ($row['quantity'] * $unit_price_bfr_vat * ( $itm_disc_per / 100)),
                                   'Product_Total_Price' => $row['quantity'] * $unit_price_aftr_row_disc,
                                  ];
                }
                $customer_account_arr = Customer::getCustomerAccount($this->info['customer_id']);
                $invoice_arr = ['Invoice_Date' => $this->info['ticketing_date'],
                                'Invoice_Due_Date'=>$this->info['ticketing_date'],
                                'Customer_ID' => $this->info['customer_id'],
                                'Depot_ID' => $this->info['depot_id'],
                                'Invoice_Currency' => $this->info['currency_id'],
                                'Invoice_Discount' => 0,
                                'discount_amount' => 0,
                                'Customer_Name' => $this->info['customer_name'],
                                'Customer_Account' => $customer_account_arr['Account_ID'],
                                'Account_Code' => 0,
                                'Card_Amount' => 0,
                                'Cash_Amount' => $this->info['paid_amount'],
                                'Cash_Account' => $this->info['payment_account_id'],
                                'CurrencySymbole' => $this->info['currency_symbol'],
                                'shipping_account' => NULL,
                                'shipping' => 0,
                                'tax_amount' => 0,
                                'project_id' => NULL,
                                'job_id' => NULL,
                                'Logged_ID' => $this->logged_id,
                                'ticketing_id' => $ticketing_id,
                                'Class_ID' => (!empty(smc_session()['default_setting']['default_ticketing_class']) ? smc_session()['default_setting']['default_ticketing_class'] : (!empty(smc_session()['default_setting']['pricing_class_id']) ? smc_session()['default_setting']['pricing_class_id'] : '')),
                                'Invoice_VAT' => $invoice_vat,
                                'Invoice_Sub_Total' => $sub_total,
                                'Invoice_Net_Total' => ($sub_total + $invoice_vat),
                                'Products' => $products
                               ];

                if (!empty($invoice_row['Invoice_ID_Auto'])) {
                    $invoice_arr['Invoice_ID_Auto'] = $invoice_row['Invoice_ID_Auto'];
                    $returned_data = $this->invoice->setData($invoice_arr)->editInvoice();
                } else {
                    $returned_data = $this->invoice->setData($invoice_arr)->insertSalesInvoice();
                }
                if (!is_numeric($returned_data[0])) {
                    $this->db->query('ROLLBACK');
                    if (in_array($returned_data[0], array_keys($checking_return)))
                        return ['Invoice :: ' . CommonLanguage::getMessage($checking_return[$returned_data[0]])];
                    else
                        return ['Invoice :: ' . $returned_data[0]];
                }
            }else {
                if (!empty($invoice_row['Invoice_ID_Auto'])) {
                    $returned_data = $this->invoice->deleteSalesInvoice([$invoice_row['Invoice_ID_Auto']]);
                    if (!empty($returned_data[0])) {
                        $this->db->query('ROLLBACK');
                        return ['Invoice :: ' . $returned_data[0]];
                    }
                }
            }
            $this->db->prepare_Stmt('delete from tbl_ticketing_details where ticketing_id = ?', [&$ticketing_id]);
            $product_details = json_decode($this->info['product_details'], true);
            foreach ($product_details as $details) {
                $this->db->prepare_Stmt('INSERT INTO `tbl_ticketing_details`
                (`ticketing_id`, `product_id`, `uom_id`, `quantity`, 
                `price`, `is_consignment`, `is_free`,`discount`,`net_total`) 
                VALUES (?,?,?,?,?,?,?,?,?)', 
                                        [&$ticketing_id, &$details['product_id'], &$details['uom_id'], &$details['quantity'],
                                         &$details['price'], &$details['is_consignment'], &$details['is_free'], 
                                         &$details['discount'],&$details['total']]);
            }
        }
        //
        $error = $this->db->error();
        if (empty($error)) {
            $ticketing_directory = dirBase . "uploads/ticketing/{$ticketing_id}";
            $ticketing_reply_directory = dirBase . "uploads/ticketing/{$ticketing_id}/{$ticketing_reply_id}";
            if (!empty($_FILES['attachment_url']['name'][0])) {
                if (!file_exists($ticketing_directory))
                    mkdir($ticketing_directory, 0777, true);
                if (!file_exists($ticketing_reply_directory))
                    mkdir($ticketing_reply_directory, 0777, true);

                $all_main_files = $_FILES['attachment_url'];
                foreach ($all_main_files['name'] as $i => $original_name) {
                    $file_name = $ticketing_id . '_' . microtime(true) . '.' . pathinfo($original_name, PATHINFO_EXTENSION);
                    $destination_file = $ticketing_reply_directory . "/" . $file_name;
                    move_uploaded_file($all_main_files['tmp_name'][$i], $destination_file);
                }
            }
            if (!empty($this->info['delete_attachments'])) {
                $deleted_attachments_array = json_decode($this->info['delete_attachments'], "true");
                foreach ($deleted_attachments_array as $deleted_attachment) {
                    $del_attach_path = $ticketing_reply_directory . "/{$deleted_attachment}";
                    if (file_exists($del_attach_path))
                        unlink($ticketing_reply_directory . "/{$deleted_attachment}");
                }
            }
            $this->db->query('COMMIT');
            token_unique::remove_token();
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }
    public function submit_maintenance() {
        $error = token_unique::is_valid_token();
        if (!empty($error))
            return $error;
//        if (!empty($this->info['unique_id']) && !empty(smc_session()['default_setting']['is_production'])) {
//            if (!isset(smc_session()['duplicate_check'][$this->info['unique_id']])) {
//                $error = array();
//                $error[0] = CommonLanguage::getMessage('duplicate_record');
//                return $error;
//            } else {
//                unset(smc_session()['duplicate_check'][$this->info['unique_id']]);
//            }
//        }
        $consignment = json_decode($this->info['consignment'], true);
        $invoices = json_decode($this->info['invoices'], true);
        if (count($consignment) > 0 && empty(smc_session()['default_setting']['default_consignment_depot'])) {
            return [CommonLanguage::getMessage('default_consignment_depot')];
        }
        $this->db->query('START TRANSACTION');
        $ticketing_id = $this->info['ticketing_id'];
        $this->info['customer_additional_phone'] = empty($this->info['customer_additional_phone']) ? NULL : $this->info['customer_additional_phone'];
        if ($ticketing_id == 0) {
            if (!$this->permissions->hasPermission('show_ticketing_product_details')) {
                $this->info['paid_amount'] = NULL;
                $this->info['payment_account_id'] = NULL;
            }
            $date_finished = NULL;
            $finished_by = NULL;
            if ($this->info['ticketing_status_id'] == 3) {
                $date_finished = $this->date_time;
                $finished_by = $this->logged_id;
            }
            $ticketing_nb = self::get_max_maintenanc_nb();
            $this->info['nonce'] = upload_url . "ticketing/". $this->info['nonce'].".wav";
            $this->db->prepare_Stmt('INSERT INTO `tbl_maintenance`
            (`maintenance_nb`, `customer_id`, `serial_number`, `maintenance_type_id`, 
            `paid_amount`, `payment_account_id`,
            `maintenance_date`, `maintenance_status_id`,`date_created`, `created_by`,
            `date_modified`, `modified_by`,`date_finished`,`finished_by`,`depot_id`,`currency_id`,`maintenance_schedule_date`,`assigned_user_id`,`ime`,`phone_brand`,`phone_name`,`invoice_number`,`nonce`,`color`,`password`,`Employee_ID`,`maintenance_note`,`exchange_rate`,`range_cost`,`color_id`,`type_id`,`other_note`)
            VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', [&$ticketing_nb, &$this->info['customer_id'], &$this->info['customer_additional_phone'],
                                                              &$this->info['ticketing_type_id'], 
                                                              &$this->info['paid_amount'], &$this->info['payment_account_id'],
                                                              &$this->info['ticketing_date'], &$this->info['ticketing_status_id'],
                                                              &$this->date_time, &$this->logged_id, &$this->date_time, &$this->logged_id,
                                                              &$date_finished, &$finished_by, &$this->info['depot_id'], &$this->info['currency_id'], 
                                                              &$this->info['ticketing_schedule_date'], &$this->logged_id, &$this->info['ime'], &$this->info['phone_brand'], &$this->info['phone_name'], &$this->info['invoice_number'], &$this->info['nonce'], &$this->info['phone_color'], &$this->info['phone_password'], &$this->info['Employee_ID'], &$this->info['ticketing_reply_description'], &$this->info['exchange_rate'], &$this->info['range_cost'], &$this->info['color_id'], &$this->info['type_id'], &$this->info['other_note']]);
            $ticketing_id = $this->db->id();
            $this->db->prepare_Stmt('INSERT INTO `tbl_maintenance_replies`
            (`maintenance_reply_description`, `created_by`, `date_created`, `modified_by`, `date_modified`, `maintenance_id`) 
            VALUES (?,?,?,?,?,?)', [&$this->info['ticketing_reply_description'], &$this->logged_id, &$this->date_time,
                                    &$this->logged_id, &$this->date_time, &$ticketing_id]);
            $ticketing_reply_id = $this->db->id();
            $this->db->prepare_Stmt('update tbl_maintenance set maintenance_reply_id = ? where maintenance_id = ?', [&$ticketing_reply_id, &$ticketing_id]);
        } else {
            if($this->info['is_record'] == 'true')
                $this->info['nonce'] = upload_url . "ticketing/". $this->info['nonce'].".wav";

            $update_data = [&$this->info['customer_id'], &$this->info['customer_additional_phone'],
                            &$this->info['ticketing_type_id'], 
                            &$this->info['paid_amount'], &$this->info['payment_account_id'],
                            &$this->info['ticketing_date'], &$this->info['ticketing_status_id'],
                            &$this->date_time, &$this->logged_id, &$this->info['depot_id'],
                            &$this->info['currency_id'],&$this->info['ticketing_schedule_date'],&$this->logged_id,&$this->info['ime'],&$this->info['phone_brand'],&$this->info['phone_name'],&$this->info['invoice_number'], &$this->info['phone_color'], &$this->info['phone_password'], &$this->info['nonce'], &$this->info['Employee_ID'], &$this->info['ticketing_reply_description'], &$this->info['exchange_rate'], &$this->info['range_cost'], &$this->info['color_id'], &$this->info['type_id'], &$this->info['other_note']];
            $query_concat = '';
            if ($this->info['ticketing_status_id'] == 3) {
                if ($this->info['old_ticketing_status_id'] != 3) {
                    $date_finished = $this->date_time;
                    $finished_by = $this->logged_id;
                    $query_concat .= ' ,`date_finished` = ?,`finished_by` = ?  ';
                    $update_data[] = $date_finished;
                    $update_data[] = $finished_by;
                }
            }
            $update_data[] = &$ticketing_id;
            $this->db->prepare_Stmt('update `tbl_maintenance` set
             `customer_id` = ?, `serial_number` = ?, `maintenance_type_id` =?, 
            `paid_amount` = ?, `payment_account_id` =?,
            `maintenance_date` = ?, `maintenance_status_id` =?,
            `date_modified` = ?, `modified_by` = ?,depot_id =?,currency_id = ?,`maintenance_schedule_date` = ?,`assigned_user_id` =? ,`ime` =? ,`phone_brand` =? ,`phone_name` =?,`invoice_number` =?,`color`=?,`password`=? ,`nonce`=? , `Employee_ID`=?, `maintenance_note`=?, `exchange_rate`=?, `range_cost`=?, `color_id`=?, `type_id`=?, `other_note`=?' . $query_concat . ' 
            where maintenance_id = ?', $update_data);
            $ticketing_reply_id = $this->info['ticketing_reply_id'];
        }
        if ($this->permissions->hasPermission('show_ticketing_product_details')) {
            $transfer_query = $this->db->query('select Request_ID from tbl_stock_request where ticketing_id = "' . $ticketing_id . '"');
            $transfer_row = $this->db->assoc($transfer_query);
            $this->depot->stop_transaction_start_end = true;
            $checking_return = ['qty_error' => CommonLanguage::getMessage('check_qty_exist'),
                                'serial_error' => CommonLanguage::getMessage('check_serial_exist'),
                                'lot_error' => CommonLanguage::getMessage('check_lot_exist'),
                                'expiry_error' => CommonLanguage::getMessage('check_expiry_exist'),
                               ];
            if (count($consignment) > 0) {
                $products = array();
                foreach ($consignment as $row) {
                    $products[] = ['Product_ID' => $row['product_id'],
                                   'UOM_ID' => $row['uom_id'],
                                   'Request_Qty' => $row['quantity']
                                  ];
                }
                $transfer_arr = ['Request_Date' => $this->info['ticketing_date'],
                                 'Request_To' => $this->info['depot_id'],
                                 'Request_From' => smc_session()['default_setting']['default_consignment_depot'],
                                 'Delivery_Date' => $this->info['ticketing_date'],
                                 'is_confirmed' => 1,
                                 'Status_ID' => 3,
                                 'note' => 'Ticketing Transfer',
                                 'customer_id' => $this->info['customer_id'],
                                 'ticketing_id' => $ticketing_id,
                                 'Products' => $products,
                                ];
                if (!empty($transfer_row['Request_ID'])) {
                    $transfer_arr['Request_ID'] = $transfer_row['Request_ID'];
                    $returned_data = $this->depot->setData($transfer_arr)->editRequest();
                } else {
                    $returned_data = $this->depot->setData($transfer_arr)->createRequest();
                }
                if (!is_numeric($returned_data[0])) {
                    if (in_array($returned_data[0], array_keys($checking_return)))
                        return ['Transfer :: ' . CommonLanguage::getMessage($checking_return[$returned_data[0]])];
                    else
                        return ['Transfer :: ' . $returned_data[0]];
                }
            } else {
                if (!empty($transfer_row['Request_ID'])) {
                    $returned_data = $this->depot->delete_request([$transfer_row['Request_ID']]);
                    if (!empty($returned_data[0])) {
                        $this->db->query('ROLLBACK');
                        return ['Transfer :: ' . $returned_data[0]];
                    }
                }
            }
            $this->invoice->stop_transaction_start_end = true;
            $invoice_query = $this->db->query('select Invoice_ID_Auto from tbl_invoices  where ticketing_id = "' . $ticketing_id . '"');
            $invoice_row = $this->db->assoc($invoice_query);
            if (count($invoices) > 0) {
                $products_invoice_arr = json_decode($this->info['products_invoice'], true);
                $product_info = array();
                $query_products = $this->db->query('select * from tbl_stock_products where Product_ID in (' . implode(",", array_unique($products_invoice_arr)) . ')');
                while ($row = $this->db->assoc($query_products)) {
                    $product_info[$row['Product_ID']] = $row;
                }
                $products = array();
                $sub_total = 0;
                $invoice_vat = 0;
                foreach ($invoices as $row) {
                    $itm_disc_per = empty($row['discount']) ? 0 : floatval($row['discount']);
                    $itm_disc_per = min($itm_disc_per, 100);
                    $vat_percent = $product_info[$row['product_id']]['vat_percent'];
                    $unit_price_bfr_vat = ((floatval($row['price']) * 100) / (100 + floatval($vat_percent)));
                    $unit_price_aftr_row_disc = (($unit_price_bfr_vat * (100 - $itm_disc_per)) / 100);
                    $vat_amount = (($unit_price_aftr_row_disc * $row['quantity'] * $vat_percent) / 100);   //vat after
                    $sub_total += (($row['quantity'] * $unit_price_aftr_row_disc)); //after row discount only
                    $invoice_vat += $vat_amount;
                    $products[] = ['Product_ID' => $row['product_id'],
                                   'UOM_ID' => $row['uom_id'],
                                   'Item_Quantity' => $row['quantity'],
                                   'Item_Price' => $unit_price_bfr_vat, //unit price before vat
                                   'vat_percent' => $vat_percent,
                                   'vat_val' => $vat_amount,
                                   'Item_Discount' => $itm_disc_per,
                                   'Discount_Amount' => ($row['quantity'] * $unit_price_bfr_vat * ( $itm_disc_per / 100)),
                                   'Product_Total_Price' => $row['quantity'] * $unit_price_aftr_row_disc,
                                  ];
                }
                $customer_account_arr = Customer::getCustomerAccount($this->info['customer_id']);
                $invoice_arr = ['Invoice_Date' => $this->info['ticketing_date'],
                                'Invoice_Due_Date'=>$this->info['ticketing_date'],
                                'Customer_ID' => $this->info['customer_id'],
                                'Depot_ID' => $this->info['depot_id'],
                                'Invoice_Currency' => $this->info['currency_id'],
                                'Invoice_Discount' => 0,
                                'discount_amount' => 0,
                                'Customer_Name' => $this->info['customer_name'],
                                'Customer_Account' => $customer_account_arr['Account_ID'],
                                'Account_Code' => 0,
                                'Card_Amount' => 0,
                                'Cash_Amount' => $this->info['paid_amount'],
                                'Cash_Account' => $this->info['payment_account_id'],
                                'CurrencySymbole' => $this->info['currency_symbol'],
                                'shipping_account' => NULL,
                                'shipping' => 0,
                                'tax_amount' => 0,
                                'project_id' => NULL,
                                'job_id' => NULL,
                                'Logged_ID' => $this->logged_id,
                                'ticketing_id' => $ticketing_id,
                                'Class_ID' => (!empty(smc_session()['default_setting']['default_ticketing_class']) ? smc_session()['default_setting']['default_ticketing_class'] : (!empty(smc_session()['default_setting']['pricing_class_id']) ? smc_session()['default_setting']['pricing_class_id'] : '')),
                                'Invoice_VAT' => $invoice_vat,
                                'Invoice_Sub_Total' => $sub_total,
                                'Invoice_Net_Total' => ($sub_total + $invoice_vat),
                                'Products' => $products
                               ];

                if (!empty($invoice_row['Invoice_ID_Auto'])) {
                    $invoice_arr['Invoice_ID_Auto'] = $invoice_row['Invoice_ID_Auto'];
                    $returned_data = $this->invoice->setData($invoice_arr)->editInvoice();
                } else {
                    $returned_data = $this->invoice->setData($invoice_arr)->insertSalesInvoice();
                }
                if (!is_numeric($returned_data[0])) {
                    $this->db->query('ROLLBACK');
                    if (in_array($returned_data[0], array_keys($checking_return)))
                        return ['Invoice :: ' . CommonLanguage::getMessage($checking_return[$returned_data[0]])];
                    else
                        return ['Invoice :: ' . $returned_data[0]];
                }
            }else {
                if (!empty($invoice_row['Invoice_ID_Auto'])) {
                    $returned_data = $this->invoice->deleteSalesInvoice([$invoice_row['Invoice_ID_Auto']]);
                    if (!empty($returned_data[0])) {
                        $this->db->query('ROLLBACK');
                        return ['Invoice :: ' . $returned_data[0]];
                    }
                }
            }
            $this->db->prepare_Stmt('delete from tbl_ticketing_details where ticketing_id = ?', [&$ticketing_id]);
            $product_details = json_decode($this->info['product_details'], true);
            foreach ($product_details as $details) {
                $this->db->prepare_Stmt('INSERT INTO `tbl_ticketing_details`
                (`ticketing_id`, `product_id`, `uom_id`, `quantity`, 
                `price`, `is_consignment`, `is_free`,`discount`,`net_total`) 
                VALUES (?,?,?,?,?,?,?,?,?)', 
                                        [&$ticketing_id, &$details['product_id'], &$details['uom_id'], &$details['quantity'],
                                         &$details['price'], &$details['is_consignment'], &$details['is_free'], 
                                         &$details['discount'],&$details['total']]);
            }
        }
        //
        $error = $this->db->error();
        if (empty($error)) {
            $ticketing_directory = dirBase . "uploads/ticketing/{$ticketing_id}";
            $ticketing_reply_directory = dirBase . "uploads/ticketing/{$ticketing_id}/{$ticketing_reply_id}";
            if (!empty($_FILES['attachment_url']['name'][0])) {
                if (!file_exists($ticketing_directory))
                    mkdir($ticketing_directory, 0777, true);
                if (!file_exists($ticketing_reply_directory))
                    mkdir($ticketing_reply_directory, 0777, true);

                $all_main_files = $_FILES['attachment_url'];
                foreach ($all_main_files['name'] as $i => $original_name) {
                    $file_name = $ticketing_id . '_' . microtime(true) . '.' . pathinfo($original_name, PATHINFO_EXTENSION);
                    $destination_file = $ticketing_reply_directory . "/" . $file_name;
                    move_uploaded_file($all_main_files['tmp_name'][$i], $destination_file);
                }
            }
            if (!empty($this->info['delete_attachments'])) {
                $deleted_attachments_array = json_decode($this->info['delete_attachments'], "true");
                foreach ($deleted_attachments_array as $deleted_attachment) {
                    $del_attach_path = $ticketing_reply_directory . "/{$deleted_attachment}";
                    if (file_exists($del_attach_path))
                        unlink($ticketing_reply_directory . "/{$deleted_attachment}");
                }
            }
            $this->db->query('COMMIT');
            token_unique::remove_token();
             return $ticketing_id;
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }
    public function submit_event() {
        $error = token_unique::is_valid_token();
        if (!empty($error))
            return $error;
          $this->db->query('START TRANSACTION');
        $ticketing_id = $this->info['ticketing_id'];
         if ($ticketing_id == 0) {
             
            
             $this->db->prepare_Stmt('INSERT INTO `tbl_event`
            (`event_id`,`event_book_type_id`,`book_time`,`from_date`,`to_date`,`from_time`,`to_time`,`event_break_type_id`,`event_break_time`,`off_day`,`event_date`,`event_name`)
            VALUES (?,?,?,?,?,?,?,?,?,?,?,?)',[&$this->info['event_id'],&$this->info['event_book_type_id'],&$this->info['book_time'],&$this->info['from_date'],&$this->info['to_date'],&$this->info['from_time'],&$this->info['to_time'],&$this->info['event_break_type_id'],&$this->info['event_break_time'],&$this->info['off_day'],&$this->date_time,&$this->info['event_name']]);
          } else {
            $update_data = [&$this->info['event_id'],&$this->info['event_book_type_id'],&$this->info['book_time'],&$this->info['from_date'],&$this->info['to_date'],&$this->info['from_time'],&$this->info['to_time'],&$this->info['event_break_type_id'],&$this->info['event_break_time'],&$this->info['off_day'],&$this->info['event_name']];
            $query_concat = '';
             
            $update_data[] = &$ticketing_id;
            $this->db->prepare_Stmt('update `tbl_event` set
             `event_id` = ?, `event_book_type_id` = ?, `book_time` = ? , `from_date` = ? , `to_date` = ?, `from_time` = ? , `to_time` = ?, `event_break_type_id` = ?, `event_break_time` = ? , `off_day` = ? , `event_name` = ?     ' . $query_concat . ' 
            where id = ?', $update_data);
         }
         //
        $error = $this->db->error();
        if (empty($error)) {
            
            $this->db->query('COMMIT');
            token_unique::remove_token();
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }
    public function submit_appointment() {
        $error = token_unique::is_valid_token();
        if (!empty($error))
            return $error;
          $this->db->query('START TRANSACTION');
          $appointment_id = $this->info['appointment_id'];
          if ($appointment_id == 0) {
             
            
             $this->db->prepare_Stmt('INSERT INTO `tbl_appointment`
            (`event_id`,`customer_id`,`field_name`,`app_date`,`app_time`)
            VALUES (?,?,?,?,?)',[&$this->info['event_id'],&$this->info['customer_id'],&$this->info['field_name'],&$this->info['app_date'],&$this->info['app_time']]);
          } else {
            $update_data = [&$this->info['event_id'],&$this->info['customer_id'],&$this->info['field_name'],&$this->info['app_date'],&$this->info['app_time']];
            $query_concat = '';
             
            $update_data[] = &$appointment_id;
            $this->db->prepare_Stmt('update `tbl_appointment` set
             `event_id` = ?, `customer_id` = ?, `field_name` = ? , `app_date` = ? , `app_time` = ?     ' . $query_concat . ' 
            where id = ?', $update_data);
         }
         //
        $error = $this->db->error();
        if (empty($error)) {
            
            $this->db->query('COMMIT');
            token_unique::remove_token();
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }
    public function get_max_ticketing_nb() {
        $query = $this->db->query('SELECT MAX(ticketing_nb) AS Entry_ID  FROM tbl_ticketing ');
        $row = $this->db->assoc($query);
        if (!empty($row))
            $ticketing = $row['Entry_ID'] + 1;
        else
            $ticketing = 1;
        return $ticketing;
    }
    public function get_max_maintenanc_nb() {
        $query = $this->db->query('SELECT MAX(maintenance_nb) AS Entry_ID  FROM tbl_maintenance ');
        $row = $this->db->assoc($query);
        if (!empty($row))
            $ticketing = $row['Entry_ID'] + 1;
        else
            $ticketing = 1;
        return $ticketing;
    }
    public function get_ticketing_data($ticketing_id) {
        $data = array();
        $query_main = $this->db->query('
        select ticketing.*,
               cust.Customer_Name,
               ticketing_type.ticketing_type_name,
               station.ticketing_station_name,
               accounts.Account_Name,
               statuses.ticketing_status_name,
               concat_ws(" - ",depot.Depot_Code,depot.Depot_Name) as depot_name,
               concat_ws(" - ",currency.Currency_Name,currency.Currency_Symbol) as currency_name,
               users.User_Name
        from tbl_ticketing ticketing 
        inner join tbl_customers cust on cust.Customer_ID = ticketing.customer_id
        inner join tbl_ticketing_type ticketing_type on ticketing_type.ticketing_type_id = ticketing.ticketing_type_id
        inner join tbl_ticketing_status statuses on statuses.ticketing_status_id = ticketing.ticketing_status_id
        left join tbl_ticketing_station station on station.ticketing_station_id = ticketing.ticketing_station_id
        left join tbl_stock_depot_warehouse depot on depot.Depot_ID = ticketing.depot_id
        left join tbl_common_currency currency on currency.Currency_ID = ticketing.currency_id
        left join tbl_chart_of_accounts accounts on accounts.Account_ID = ticketing.payment_account_id
        left join tbl_common_users users on users.User_ID = ticketing.assigned_user_id
        where ticketing_id = "' . $ticketing_id . '"
        ');
        $data['main'] = $this->db->assoc($query_main);
        $query_reply = $this->db->query('select * from tbl_ticketing_replies
                            where ticketing_reply_id = "' . $data['main']['ticketing_reply_id'] . '"');
        $data['reply'] = $this->db->assoc($query_reply);
        $query_items = $this->db->query('
        select details.*,products.Product_Name,uom.UOM_Name 
        from tbl_ticketing_details details 
        inner join tbl_stock_products products on products.Product_ID = details.product_id
        inner join tbl_stock_uom uom on uom.UOM_ID = details.uom_id
        where details.ticketing_id = "' . $ticketing_id . '"');
        while ($row = $this->db->assoc($query_items)) {
            $data['products'][] = $row;
        }
        return $data;
    } 
    public function finish_maintenance($maintenance_id) {
        $date_finished = $this->date_time;
        $finished_by = $this->logged_id;

        $this->db->prepare_Stmt('update tbl_maintenance set maintenance_status_id = 3,date_finished = ? ,finished_by = ?  where maintenance_id = ?', [ $date_finished,$finished_by, &$maintenance_id]);
        return true;
    }
    public function get_maintenance_data($ticketing_id) {
        $data = array();
        
        $query_main = $this->db->query('
        SELECT
    ticketing.*,
    color.Color_Name as color_name,
    brand.brand_name as type_name,
    replies.maintenance_reply_description as reply_description,
    cust.Customer_Name,
    cust.Customer_Phone,
    ticketing_type.maintenance_type_name,
    station.maintenance_supporter_name,
    accounts.Account_Name,
    statuses.ticketing_status_name,
    CONCAT_WS(
        " - ",
        depot.Depot_Code,
        depot.Depot_Name
    ) AS depot_name,
    CONCAT_WS(
        " - ",
        currency.Currency_Name,
        currency.Currency_Symbol
    ) AS currency_name,
    users.User_Name,
    user.User_Full_Name as Employee_full_name 
FROM
    tbl_maintenance ticketing
INNER JOIN tbl_customers cust ON
    cust.Customer_ID = ticketing.customer_id
INNER JOIN tbl_maintenance_type ticketing_type ON
    ticketing_type.maintenance_type_id = ticketing.maintenance_type_id
INNER JOIN tbl_ticketing_status statuses ON
    statuses.ticketing_status_id = ticketing.maintenance_status_id
LEFT JOIN tbl_maintenance_supporter station ON
    station.maintenance_supporter_id = ticketing.maintenance_supporter_id
LEFT JOIN tbl_maintenance_replies replies ON
    replies.maintenance_id = ticketing.maintenance_id
LEFT JOIN tbl_stock_depot_warehouse depot ON
    depot.Depot_ID = ticketing.depot_id
LEFT JOIN tbl_common_currency currency ON
    currency.Currency_ID = ticketing.currency_id
LEFT JOIN tbl_chart_of_accounts accounts ON
    accounts.Account_ID = ticketing.payment_account_id
LEFT JOIN tbl_common_users users ON
    users.User_ID = ticketing.assigned_user_id
LEFT JOIN tbl_common_users user ON
    user.User_ID = ticketing.Employee_ID
LEFT JOIN tbl_color color ON
    color.Color_ID = ticketing.color_id

LEFT JOIN tbl_brand_products brand ON
    brand.brand_id = ticketing.type_id
WHERE
ticketing.maintenance_id = "' . $ticketing_id . '"
        ');
        $data['main'] = $this->db->assoc($query_main);
        

        $query_reply = $this->db->query('select * from tbl_maintenance_replies
                            where maintenance_reply_id = "' . $data['main']['maintenance_reply_id'] . '"');
        $data['reply'] = $this->db->assoc($query_reply);
        $query_items = $this->db->query('
        select details.*,products.Product_Name,uom.UOM_Name 
        from tbl_ticketing_details details 
        inner join tbl_stock_products products on products.Product_ID = details.product_id
        inner join tbl_stock_uom uom on uom.UOM_ID = details.uom_id
        where details.ticketing_id = "' . $ticketing_id . '"');
        while ($row = $this->db->assoc($query_items)) {
            $data['products'][] = $row;
        }
        return $data;
    } 
    public function get_event_data($ticketing_id) {
        $data = array();
        
        $query_main = $this->db->query('
        SELECT * FROM tbl_event WHERE id = "' . $ticketing_id . '" ');
        $data['main'] = $this->db->assoc($query_main);
        

         
        return $data;
    } 
    public function get_appointment_data($appointment_id) {
        $data = array();
        
        $query_main = $this->db->query('
        SELECT
        *
    FROM
        `tbl_event`,
        `tbl_appointment`,
        `tbl_customers`
    WHERE
        `tbl_event`.`id` = `tbl_appointment`.`event_id` AND `tbl_customers`.`Customer_ID` = `tbl_appointment`.`customer_id` and `tbl_appointment`.id = "' . $appointment_id . '" ');
        $data['main'] = $this->db->assoc($query_main);
         
        return $data;
    } 

    public function get_maintenance_by_serial_number($serial){
        
        $data = array();
        
        $query_main = $this->db->query('
        SELECT
    ticketing.*,
    replies.maintenance_reply_description as reply_description,
    cust.Customer_Name,
    ticketing_type.maintenance_type_name,
    station.maintenance_supporter_name,
    accounts.Account_Name,
    statuses.ticketing_status_name,
    CONCAT_WS(
        " - ",
        depot.Depot_Code,
        depot.Depot_Name
    ) AS depot_name,
    CONCAT_WS(
        " - ",
        currency.Currency_Name,
        currency.Currency_Symbol
    ) AS currency_name,
    users.User_Name
FROM
    tbl_maintenance ticketing
INNER JOIN tbl_customers cust ON
    cust.Customer_ID = ticketing.customer_id
INNER JOIN tbl_maintenance_type ticketing_type ON
    ticketing_type.maintenance_type_id = ticketing.maintenance_type_id
INNER JOIN tbl_ticketing_status statuses ON
    statuses.ticketing_status_id = ticketing.maintenance_status_id
LEFT JOIN tbl_maintenance_supporter station ON
    station.maintenance_supporter_id = ticketing.maintenance_supporter_id
LEFT JOIN tbl_maintenance_replies replies ON
    replies.maintenance_id = ticketing.maintenance_id
LEFT JOIN tbl_stock_depot_warehouse depot ON
    depot.Depot_ID = ticketing.depot_id
LEFT JOIN tbl_common_currency currency ON
    currency.Currency_ID = ticketing.currency_id
LEFT JOIN tbl_chart_of_accounts accounts ON
    accounts.Account_ID = ticketing.payment_account_id
LEFT JOIN tbl_common_users users ON
    users.User_ID = ticketing.assigned_user_id
WHERE
ticketing.serial_number = "' . $serial . '"
        ');
        $data[] = $this->db->assoc($query_main);
        while ($row = $this->db->assoc($query_main))
                $data[]  = $row;
        return $data;

    }
    public function delete_ticketing() {
        $this->db->query('START TRANSACTION');
        $invoice_ids_arr = [];
        $query_invoice = $this->db->query('select Invoice_ID_Auto from tbl_invoices where ticketing_id = "' . $this->info['ticketing_id'] . '"');
        while ($row = $this->db->assoc($query_invoice)) {
            $invoice_ids_arr[] = $row['Invoice_ID_Auto'];
        }
        if (!empty($invoice_ids_arr)) {
            $this->invoice->stop_transaction_start_end = true;
            $this->invoice->deleteSalesInvoice($invoice_ids_arr);
        }
        $this->db->prepare_Stmt('delete from tbl_ticketing where ticketing_id = ?', [&$this->info['ticketing_id']]);
        $this->db->prepare_Stmt('delete from tbl_ticketing_details where ticketing_id = ?', [&$this->info['ticketing_id']]);
        $this->db->prepare_Stmt('delete from tbl_ticketing_replies where ticketing_id = ?', [&$this->info['ticketing_id']]);

        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }
    public function delete_maintenance() {
        $this->db->query('START TRANSACTION');
        $invoice_ids_arr = [];
        $query_invoice = $this->db->query('select Invoice_ID_Auto from tbl_invoices where ticketing_id = "' . $this->info['ticketing_id'] . '"');
        while ($row = $this->db->assoc($query_invoice)) {
            $invoice_ids_arr[] = $row['Invoice_ID_Auto'];
        }
        if (!empty($invoice_ids_arr)) {
            $this->invoice->stop_transaction_start_end = true;
            $this->invoice->deleteSalesInvoice($invoice_ids_arr);
        }
        $this->db->prepare_Stmt('delete from tbl_maintenance where maintenance_id = ?', [&$this->info['ticketing_id']]);
        //$this->db->prepare_Stmt('delete from tbl_ticketing_details where ticketing_id = ?', [&$this->info['ticketing_id']]);
        $this->db->prepare_Stmt('delete from tbl_maintenance_replies where maintenance_id = ?', [&$this->info['ticketing_id']]);

        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }
    public function delete_event() {
        $this->db->query('START TRANSACTION');
        
        $this->db->prepare_Stmt('delete from tbl_appointment where event_id = ?', [&$this->info['ticketing_id']]);
        //$this->db->prepare_Stmt('delete from tbl_ticketing_details where ticketing_id = ?', [&$this->info['ticketing_id']]);
        $this->db->prepare_Stmt('delete from tbl_event where id = ?', [&$this->info['ticketing_id']]);

        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }
    public function delete_appointment() {
        $this->db->query('START TRANSACTION');
        
        $this->db->prepare_Stmt('delete from tbl_appointment where id = ?', [&$this->info['ticketing_id']]);

        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }
    public function get_ticketing_calendar(){
        $data= array();
        $condition ='';
        if(is_numeric($this->info['customer_id'])){
            $condition .= ' and customer_id = "'.$this->info['customer_id'].'"';
        }
        if(!empty($this->info['assigned_user_id'])){
            $condition .= ' and assigned_user_id = "'.$this->info['assigned_user_id'].'"';
        }
        $start = date('Y-m-d',strtotime($this->info['start']));
        $end = date('Y-m-d',strtotime($this->info['end']));
        $query =  $this->db->query('
        select 
        Customer_Name,assigned_user_id,ticketing_schedule_date,
        ticketing_status_id,customer_id,assigned_user_id,User_Name
        from ticketing_list where Date(ticketing_schedule_date)  between "'.$start.'" and "'.$end.'"
        '.$condition);
        while($row = $this->db->assoc($query)){
            $data[] = ['start'=>$row['ticketing_schedule_date'],
                       'end'=>$row['ticketing_schedule_date'],
                       'color'=>$row['ticketing_status_id'] == 1 ? '#555555' : ($row['ticketing_status_id'] == 2 ?'#ed9421' : '#8F00DC'),
                       'title'=>$row['Customer_Name'].' - '.$row['User_Name'],
                      ];
        }
        return $data;
    }

    public function get_ticketing_replies($ticketing_id) {
        $data = array();
        $query_reply = $this->db->query('
         select replies.*,users.User_Full_Name from tbl_ticketing_replies replies
         inner join tbl_common_users users on users.User_ID = replies.created_by
         where replies.ticketing_id = "' . $ticketing_id . '" 
         order by replies.date_created desc');
        while ($row = $this->db->assoc($query_reply)) {
            $data[] = $row;
        }
        return $data;
    }
    public function get_ticketing_reply($ticketing_reply_id, $delete_attachment = true) {
        $data = array('ticketing_reply_description' => '', 'attachments' => '');
        $query = $this->db->query('select * from tbl_ticketing_replies where ticketing_reply_id = "' . $ticketing_reply_id . '"');
        $row = $this->db->assoc($query);
        $data['ticketing_reply_description'] = $row['ticketing_reply_description'];
        $html = '';
        if (is_dir(dirBase . "uploads/ticketing/" . $row['ticketing_id'] . "/" . $row['ticketing_reply_id'])) {
            $dir = scandir(dirBase . "uploads/ticketing/" . $row['ticketing_id'] . "/" . $row['ticketing_reply_id']);
            foreach ($dir as $file_name) {
                if (substr($file_name, 0, 1) != '..' && substr($file_name, 0, 1) != '.' && $file_name) {
                    $path_info = dirBase . "uploads/ticketing/" . $row['ticketing_id'] . "/" . $row['ticketing_reply_id'] . "/" . $file_name;
                    $extension = pathinfo($path_info)['extension'];
                    $html .= '<div class=" main_attachment_row display-flex m-r-10"  data-attachment-name="' . $file_name . '">';
                    if (in_array($extension, ['png', 'gif', 'jpeg', 'png', 'jpg'])) {
                        $html .= '<img class="image_popup" popup-src="' . SITE_LINK . 'uploads/ticketing/' . $row['ticketing_id'] . '/' . $row['ticketing_reply_id'] . '/' . $file_name . '" src="' . SITE_LINK . 'uploads/ticketing/' . $row['ticketing_id'] . '/' . $row['ticketing_reply_id'] . '/' . $file_name . '" style="max-width:50px;max-height:50px;cursor:pointer;">';
                    } else {
                        $html .= '<a target="_blank" href="' . SITE_LINK . 'uploads/ticketing/' . $row['ticketing_id'] . '/' . $row['ticketing_reply_id'] . '/' . $file_name . '">' . $extension . '</a>';
                    }
                    if ($delete_attachment)
                        $html .= ' <i class="fa fa-trash delete_reply_attachment red-star" style="cursor:pointer;"></i>';
                    $html .= '</div>';
                }
            }
        }
        $data['attachments'] = $html;
        return $data;
    }
    public function get_maintenance_reply($ticketing_reply_id, $delete_attachment = true) {
        $data = array('ticketing_reply_description' => '', 'attachments' => '');
        $query = $this->db->query('select * from tbl_maintenance_replies where maintenance_reply_id = "' . $ticketing_reply_id . '"');
        $row = $this->db->assoc($query);
        $data['ticketing_reply_description'] = $row['ticketing_reply_description'];
        $html = '';
        if (is_dir(dirBase . "uploads/ticketing/" . $row['ticketing_id'] . "/" . $row['ticketing_reply_id'])) {
            $dir = scandir(dirBase . "uploads/ticketing/" . $row['ticketing_id'] . "/" . $row['ticketing_reply_id']);
            foreach ($dir as $file_name) {
                if (substr($file_name, 0, 1) != '..' && substr($file_name, 0, 1) != '.' && $file_name) {
                    $path_info = dirBase . "uploads/ticketing/" . $row['ticketing_id'] . "/" . $row['ticketing_reply_id'] . "/" . $file_name;
                    $extension = pathinfo($path_info)['extension'];
                    $html .= '<div class=" main_attachment_row display-flex m-r-10"  data-attachment-name="' . $file_name . '">';
                    if (in_array($extension, ['png', 'gif', 'jpeg', 'png', 'jpg'])) {
                        $html .= '<img class="image_popup" popup-src="' . SITE_LINK . 'uploads/ticketing/' . $row['ticketing_id'] . '/' . $row['ticketing_reply_id'] . '/' . $file_name . '" src="' . SITE_LINK . 'uploads/ticketing/' . $row['ticketing_id'] . '/' . $row['ticketing_reply_id'] . '/' . $file_name . '" style="max-width:50px;max-height:50px;cursor:pointer;">';
                    } else {
                        $html .= '<a target="_blank" href="' . SITE_LINK . 'uploads/ticketing/' . $row['ticketing_id'] . '/' . $row['ticketing_reply_id'] . '/' . $file_name . '">' . $extension . '</a>';
                    }
                    if ($delete_attachment)
                        $html .= ' <i class="fa fa-trash delete_reply_attachment red-star" style="cursor:pointer;"></i>';
                    $html .= '</div>';
                }
            }
        }
        $data['attachments'] = $html;
        return $data;
    }
    public function submit_ticketing_reply() {
        $this->db->query('START TRANSACTION');
        $current_type = $this->info['current_type'];
        $ticketing_id = $this->info['ticketing_id'];
        if ($current_type == 'add') {
            $this->db->prepare_Stmt('INSERT INTO `tbl_ticketing_replies`
            (`ticketing_reply_description`, `created_by`, `date_created`, `modified_by`, `date_modified`, `ticketing_id`) 
            VALUES (?,?,?,?,?,?)', [&$this->info['ticketing_reply_description'], &$this->logged_id, &$this->date_time,
                                    &$this->logged_id, &$this->date_time, &$ticketing_id]);
            $ticketing_reply_id = $this->db->id();
            $this->db->prepare_Stmt('
        update `tbl_ticketing` set `date_modified` = ?, `modified_by` =?,ticketing_reply_id =? 
        where `ticketing_id` = ?', [&$this->date_time, &$this->logged_id, &$ticketing_reply_id,
                                    &$ticketing_id]);
        } else {
            $ticketing_reply_id = $this->info['ticketing_reply_id'];
            $this->db->prepare_Stmt('update `tbl_ticketing_replies` set
            `ticketing_reply_description` = ?, `modified_by` = ?, `date_modified` = ? where `ticketing_id` = ?', [&$this->info['ticketing_reply_description'], &$this->logged_id,
                                                                                                                  &$this->date_time, &$ticketing_id]);
        }
        $error = $this->db->error();
        if (empty($error)) {
            $ticketing_directory = dirBase . "uploads/ticketing/{$ticketing_id}";
            $ticketing_reply_directory = dirBase . "uploads/ticketing/{$ticketing_id}/{$ticketing_reply_id}";
            if (!empty($_FILES['reply_attachment_url']['name'][0])) {
                if (!file_exists($ticketing_directory))
                    mkdir($ticketing_directory, 0777, true);
                if (!file_exists($ticketing_reply_directory))
                    mkdir($ticketing_reply_directory, 0777, true);

                $all_main_files = $_FILES['reply_attachment_url'];
                foreach ($all_main_files['name'] as $i => $original_name) {
                    $file_name = $ticketing_id . '_' . microtime(true) . '.' . pathinfo($original_name, PATHINFO_EXTENSION);
                    $destination_file = $ticketing_reply_directory . "/" . $file_name;
                    move_uploaded_file($all_main_files['tmp_name'][$i], $destination_file);
                }
            }
            if (!empty($this->info['delete_reply_attachments'])) {
                $deleted_attachments_array = json_decode($this->info['delete_reply_attachments'], "true");
                foreach ($deleted_attachments_array as $deleted_attachment) {
                    $del_attach_path = dirBase . "uploads/ticketing/{$ticketing_id}/{$ticketing_reply_id}/{$deleted_attachment}";
                    if (file_exists($del_attach_path))
                        unlink(dirBase . "uploads/ticketing/{$ticketing_id}/{$ticketing_reply_id}/{$deleted_attachment}");
                }
            }
            $this->db->query('COMMIT');
            return [$ticketing_reply_id];
        } else {
            $this->db->query('ROLLBACK');
            return array_unique($error);
        }
    }
    public function submit_maintenance_reply() {
        $this->db->query('START TRANSACTION');
        $current_type = $this->info['current_type'];
        $ticketing_id = $this->info['ticketing_id'];
        if ($current_type == 'add') {
            $this->db->prepare_Stmt('INSERT INTO `tbl_maintenance_replies`
            (`maintenance_reply_description`, `created_by`, `date_created`, `modified_by`, `date_modified`, `maintenance_id`) 
            VALUES (?,?,?,?,?,?)', [&$this->info['ticketing_reply_description'], &$this->logged_id, &$this->date_time,
                                    &$this->logged_id, &$this->date_time, &$ticketing_id]);
            $ticketing_reply_id = $this->db->id();
            $this->db->prepare_Stmt('
        update `tbl_maintenance` set `date_modified` = ?, `modified_by` =?,maintenance_reply_id =? 
        where `maintenance_id` = ?', [&$this->date_time, &$this->logged_id, &$ticketing_reply_id,
                                    &$ticketing_id]);
        } else {
            $ticketing_reply_id = $this->info['ticketing_reply_id'];
            $this->db->prepare_Stmt('update `tbl_maintenance_replies` set
            `maintenance_reply_description` = ?, `modified_by` = ?, `date_modified` = ? where `maintenance_id` = ?', [&$this->info['ticketing_reply_description'], &$this->logged_id,
                                                                                                                  &$this->date_time, &$ticketing_id]);
        }
        $error = $this->db->error();
        if (empty($error)) {
            $ticketing_directory = dirBase . "uploads/ticketing/{$ticketing_id}";
            $ticketing_reply_directory = dirBase . "uploads/ticketing/{$ticketing_id}/{$ticketing_reply_id}";
            if (!empty($_FILES['reply_attachment_url']['name'][0])) {
                if (!file_exists($ticketing_directory))
                    mkdir($ticketing_directory, 0777, true);
                if (!file_exists($ticketing_reply_directory))
                    mkdir($ticketing_reply_directory, 0777, true);

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

    public static function get_pending_ticketing($type){
        $data= array('count'=>0,'data'=>array());
        $db = DBConnectorFactory::loadDriver();
        $cond = '';
        if($type == 'assigned'){
            $cond .= ' where ticketing_status_id = 1  and assigned_user_id = "'.smc_session()['user_logged_id'].'"';
        }else{
            $default_hours = smc_session()['default_setting']['default_hours_pending_late_ticketing'];
            $cond .= ' where ticketing_status_id in (1,2) and TIMESTAMPDIFF(HOUR, `date_created`,now())  > "'.$default_hours.'" ';
        }
        $query = $db->query('
        select ticketing_id,Customer_Name,User_Name,
        assigned_user_id,TIMESTAMPDIFF(HOUR, `date_created`,now()) as hours,ticketing_type_name,ticketing_schedule_date
       from ticketing_list '.$cond);
        while($row = $db->assoc($query)){
            $data['data'][] = $row;
            $data['count']++;
        }
        return $data;
    }
    public function maintenanceSearch($term = null, $page, $limit){
        $data = array();
        
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        $data_filter = array();
        if (!is_null($term)) {
            $cond .= ' and (serial_number LIKE ?   )';
            $data_filter[] = "%" . $term . "%";
         }
         
          
        //        echo"<pre>";
        //        die(print_r($cust_emp_arr));
        // Yousif Dahabra
       
                $query = $this->db->prepare_Stmt('select 
                serial_number as id,

                concat_ws("  ",serial_number) as text
                
                
            from 
                tbl_maintenance 
                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 maintenanceInvoiceSearch($term = null, $page, $limit){
        $data = array();
        
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        $data_filter = array();
        if (!is_null($term)) {
            $cond .= ' and (CONCAT(
                tbl_invoices.invoice_prefix,
                Invoice_ID
            ) LIKE ?   )';
            $data_filter[] = "%" . $term . "%";
         }
       
                $query = $this->db->prepare_Stmt('
                SELECT
                
                Invoice_ID_Auto as id,
                CONCAT_WS(
                    "  ",
                    CONCAT(
                        tbl_invoices.invoice_prefix,
                        Invoice_ID
                    )
                ) AS text
            FROM
                `tbl_maintenance`
            INNER JOIN tbl_invoices ON tbl_invoices.Invoice_ID_Auto = tbl_maintenance.`invoice_number` 
            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 maintenanceSupporterSearch($term = null, $page, $limit){
        $data = array();
        
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        $data_filter = array();
        if (!is_null($term)) {
            $cond .= ' and (maintenance_supporter_name LIKE ?   )';
            $data_filter[] = "%" . $term . "%";
         }
         
          
        //        echo"<pre>";
        //        die(print_r($cust_emp_arr));
        // Yousif Dahabra
       
                $query = $this->db->prepare_Stmt('select 
                maintenance_supporter_name as id,

                concat_ws("  ",maintenance_supporter_name) as text
                
                
            from 
            tbl_maintenance_supporter 
                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_event(){
        $data = array();
        $data['data'] = array();
        $data['max_time'] = array();
        $query = $this->db->query('select  * from tbl_event   where 1=1  and id="' . $this->info['event_id'] . '" ');
        while ($row = $this->db->assoc($query)) {
                $data['data'][] = $row;
        }
        $from_time=$data['data'][0]['from_time'];
        $to_time=$data['data'][0]['to_time'];
        $event_break_time=$data['data'][0]['event_break_time'];
        $event_break_type ='';
        if($data['data'][0]['event_break_type_id'] == 1){
            $event_break_type = 'hour';
        }else{
            $event_break_type = 'minutes';
        }
        

        /* in submit appointment
        $book_time=$data['data'][0]['book_time'];
        if($data['data'][0]['book_time'] == 1){

        }else if($data['data'][0]['book_time'] == 2){

        }
        else if($data['data'][0]['book_time'] == 3){

        }
        else if($data['data'][0]['book_time'] == 4){

        }
        */



        $query_app = $this->db->query('select  max(app_time) as max_app_time from tbl_appointment   where id="' . $this->info['event_id'] . '" ');
        while ($row_app = $this->db->assoc($query_app)) {
            $data['max_time'][] = $row_app;
        }
        $max_app_time =  $data['max_time'][0]['max_app_time'];
        if($max_app_time != null){
            
            
             

        }else{
             //display the converted time
            $app_time= date('H:i:s',strtotime("+$event_break_time $event_break_type",strtotime($from_time)));
            $data['value']=$app_time;

        }
        return $data;
    }
    public function save_audio(){
        if ( !empty($_FILES['payload'])) {
            
            $rand = $_POST['nonce'] ;
            $info = pathinfo($_FILES['payload']['name']);
            $fname = $_FILES['payload']['tmp_name'];
            // new file must be less than 10mb
            if (filesize($fname) < 10 * pow(1024, 2)){
                move_uploaded_file($fname, dirBase . "uploads/ticketing/$rand.wav");
                return ['succ'];
            }else{
                return ['move error'];
            }
        }
    
        return ['error'];

    }


}
