<?php

class Shipping {

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

    public function __construct() {
        $this->db = DBConnectorFactory::loadDriver();
        $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 submitShippingCompany() {
        $shipping_company_id = $this->info['shipping_company_id'];
        $this->info['shipping_company_fees'] = !empty($this->info['shipping_company_fees']) ? $this->info['shipping_company_fees'] : NULL;
        $this->info['user_name'] = !empty($this->info['user_name']) ? $this->info['user_name'] : NULL;
        $this->info['password'] = !empty($this->info['password']) ? $this->info['password'] : NULL;
        $this->info['token'] = !empty($this->info['token']) ? $this->info['token'] : NULL;
        $this->info['api_folder'] = !empty($this->info['api_folder']) ? $this->info['api_folder'] : NULL;
        $this->info['shipping_account_id'] = !empty($this->info['shipping_account_id']) ? $this->info['shipping_account_id'] : NULL;
        $this->info['sales_account_id'] = !empty($this->info['sales_account_id']) ? $this->info['sales_account_id'] : NULL;
        $this->info['main_account_id'] = !empty($this->info['main_account_id']) ? $this->info['main_account_id'] : NULL;
        if ($shipping_company_id == 0) {
            $this->db->prepare_Stmt('INSERT INTO tbl_shipping_company 
            (shipping_company_name,shipping_company_fees,user_name,password,token,shipping_account_id,
            sales_account_id,main_account_id,api_folder,api_version,note,is_invoice_total_on_shipping,trans_on_change_status) 
            VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)', [&$this->info['shipping_company_name'], &$this->info['shipping_company_fees'],
                &$this->info['user_name'], &$this->info['password'], &$this->info['token'],
                &$this->info['shipping_account_id'], &$this->info['sales_account_id'],
                &$this->info['main_account_id'],
                &$this->info['api_folder'], &$this->info['api_version'],
                &$this->info['note'], &$this->info['is_invoice_total_on_shipping'], $this->info['trans_on_change_status']]);
            $shipping_company_id = $this->db->id();
        } else {
            $this->db->prepare_Stmt('Update  tbl_shipping_company 
            set shipping_company_name = ?,shipping_company_fees= ?,user_name= ? ,password = ?,token = ?,
            shipping_account_id=?,sales_account_id= ?,main_account_id= ?,api_folder = ?,api_version = ?,note = ?,
            is_invoice_total_on_shipping= ?,trans_on_change_status=? where shipping_company_id= ?', [&$this->info['shipping_company_name'],
                &$this->info['shipping_company_fees'], &$this->info['user_name'],
                &$this->info['password'], &$this->info['token'],
                &$this->info['shipping_account_id'], &$this->info['sales_account_id'],
                &$this->info['main_account_id'], &$this->info['api_folder'],
                &$this->info['api_version'], &$this->info['note'],
                &$this->info['is_invoice_total_on_shipping'], $this->info['trans_on_change_status'],
                &$shipping_company_id]);
        }
        $error = $this->db->error();
        return $error;
    }

    public function deleteShippingCompany() {
        $query = $this->db->query('select * from tbl_invoices where  shipping_company_id = "' . $this->info['shipping_company_id'] . '"');
        $row_count = $this->db->rowsno($query);
        if ($row_count > 0) {
            return [CommonLanguage::getMessage('unable_delete_shipping_company')];
        }
        $this->db->prepare_Stmt('Delete from tbl_shipping_company WHERE shipping_company_id = ?', [&$this->info['shipping_company_id']]);
        $error = $this->db->error();
        return $error;
    }

    public function searchShippingCompany($term = null, $page, $limit) {
        $data = array();
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        $data_filter = array();
        if (!is_null($term)) {
            $cond .= ' and shipping_company_name LIKE ? ';
            $data_filter[] = "%" . $term . "%";
        }
        $query = $this->db->prepare_Stmt('select 
                                    shipping_company_id as id,
                                    shipping_company_name as text,
                                    api_folder
                                from 
                                    tbl_shipping_company
                                    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 submit_schedule() {
        $this->db->query('START TRANSACTION');
        $current_date = date('Y-m-d');
        foreach ($this->info['invoices'] as $invoice) {
            $this->db->prepare_Stmt('update tbl_invoices 
            set pick_up_date = ?,drop_off_date = ?,shipping_status_id = 2 
            where Invoice_ID_Auto = ?', [&$this->info['pick_up_date'], &$this->info['drop_off_date'], &$invoice]);
            $this->db->prepare_Stmt('INSERT INTO `tbl_invoice_shipping`
            (`Invoice_ID_Auto`, `pick_up_date`, `drop_off_date`, `shipping_status_id`,date_last_status) 
            VALUES (?,?,?,2,?)', [&$invoice, &$this->info['pick_up_date'], &$this->info['drop_off_date'], &$current_date]);
        }
        $error = $this->db->error();
        if (empty($error))
            $this->db->query('COMMIT');
        else
            $this->db->query('ROLLBACK');
        return $error;
    }

    public function cancel_private_shipping() {
        $current_date = date('Y-m-d');
        $invoices_ids = implode(',', $this->info['invoices']);
        $this->db->query('START TRANSACTION');
        $this->db->prepare_Stmt('update tbl_invoices set shipping_status_id = 4
        where Invoice_ID_Auto in (' . $invoices_ids . ') ');
        $this->db->prepare_Stmt('update tbl_invoice_shipping set date_last_status = ?,shipping_status_id = 4
        where Invoice_ID_Auto in (' . $invoices_ids . ') ', [&$current_date]);
        $error = $this->db->error();
        if (empty($error))
            $this->db->query('COMMIT');
        else
            $this->db->query('ROLLBACK');
        return $error;
    }

    public function submit_delivery_private() {
        $current_date = date('Y-m-d');
        $invoices_ids = implode(',', $this->info['invoices']);
        $this->db->query('START TRANSACTION');
        $invoice = new Invoice;
        $invoice->stop_transaction_start_end = true;
        foreach ($this->info['invoices'] as $inv_id) {
            $invoice->generate_ship_trans($inv_id, 6);
        }
        $this->db->prepare_Stmt('update tbl_invoices set shipping_status_id = 6
        where Invoice_ID_Auto in (' . $invoices_ids . ') ');
        $this->db->prepare_Stmt('UPDATE `tbl_invoice_shipping` SET `shipping_status_id`= 6 WHERE  Invoice_ID_Auto  IN (' . $invoices_ids . ')', [&$current_date]);

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

    public function get_total_shippings() {
        $cond = ' and current_year_id =  "' . $this->year_id . '"';
        if (!empty($this->info['shipping_company_id']))
            $cond .= 'And shipping_company_id = "' . $this->info['shipping_company_id'] . '"';
        else
            $cond .= ' and shipping_company_id is not null';
        if (is_numeric($this->info['customer_id'])) {
            $customer_id = $this->info['customer_id'];
            $cond .= ' and Customer_ID = "' . $customer_id . '"';
        }
        if (is_numeric($this->info['shipping_status_id'])) {
            $shipping_status = $this->info['shipping_status_id'];
            $cond .= ' and shipping_status_id = "' . $shipping_status . '"';
        }
        if (!empty($this->info['from_date']) && !empty($this->info['to_date'])) {
            $cond .= ' and Invoice_Date between "' . $this->info['from_date'] . '" AND  "' . $this->info['to_date'] . '"';
        }
        if (!empty($this->info['pick_up_from_date']) && !empty($this->info['pick_up_to_date'])) {
            $cond .= ' and pick_up_date between "' . $this->info['pick_up_from_date'] . '" AND  "' . $this->info['pick_up_to_date'] . '"';
        }
        if (!empty($this->info['drop_off_from_date']) && !empty($this->info['drop_off_to_date'])) {
            $cond .= ' and drop_off_date between "' . $this->info['drop_off_from_date'] . '" AND  "' . $this->info['drop_off_to_date'] . '"';
        }
        if (!empty($this->info['paid_type_id'])) {
            if ($this->info['paid_type_id'] == 1)
                $cond .= ' and Payment_Paid_Amount = 0';
            else
            if ($this->info['paid_type_id'] == 2)
                $cond .= ' and Payment_Paid_Amount = Invoice_Net_Total';
        }


        $default_curr = CurrencyFactory::getDefaultCurrency();
        $local_symbol = CurrencyFactory::getLocalCurrencySymbol();


        $query = $this->db->query('select Invoice_Date as date,current_rate,sum(Collection*current_rate) as local_total,
        sum(Collection*second_currency_rate) as second_total,Invoice_Currency as currency_id from shipping_list where current_year_id =  "' . $this->year_id . '"  ' . $cond);

        $row = $this->db->assoc($query);

        $total_sec = smc_session()['default_setting']['activate_second_currency'] == 1 ? '<br/>' . CommonFunctions::formatNumber($row['second_total']) . ' ' . smc_session()['second_currency']['symbol'] : '';

        $total = CommonFunctions::formatNumber($row['local_total']) . ' ' . $local_symbol.' '.$total_sec;
        return $total;
    }

    public function update_old_invoices_shipping_data() {
        //        die('ok');
        $this->db->query('START TRANSACTION');
        $invoices_query = $this->db->query('
        SELECT inv.*,
               ship_comp.shipping_account_id,
               ship_comp.main_account_id
        FROM `tbl_invoices` inv
        inner join tbl_shipping_company ship_comp
        on ship_comp.shipping_company_id = inv.shipping_company_id

        WHERE inv.`shipping_company_id` IS NOT NULL
        AND inv.`is_invoice_total_on_shipping` = 0 ');
        while ($row = $this->db->assoc($invoices_query)) {
            $is_actual = $row['is_actual'];
            $this->db->prepare_Stmt('UPDATE `tbl_invoices`
            SET `ship_comp_acc_id`= ?,is_invoice_total_on_shipping = 1
            WHERE  Invoice_ID_Auto  = ? ', [&$row['main_account_id'], &$row['Invoice_ID_Auto']]);
            $customerAccount = Customer::getCustomerAccount($row['Customer_ID'])['Account_ID'];
            $prefix = $row['invoice_prefix'];
            $main_nb = $row['Invoice_ID'];
            $ref_num = 'Inv.' . $prefix . $main_nb;
            $transaction_query = $this->db->query('select * from tbl_accounting_transactions 
            where main_id = "' . $row['Invoice_ID_Auto'] . '" and main_type= "inv" limit 1');
            $transaction_row = $this->db->assoc($transaction_query);
            $dat_1 = ["JV_ID" => $transaction_row['JV_ID'],
                "Transaction_Reference" => $ref_num,
                "Transaction_Account" => $customerAccount,
                "Transaction_Type" => "Credit",
                "Transaction_Amount" => ($row['Invoice_Net_Total'] + $row['Invoice_Shipping']),
                "Transaction_Date" => $row['Invoice_Date'],
                "Currency_ID" => $row['Invoice_Currency'],
                "Logged_ID" => $row['Logged_ID'],
                "created_logged_id" => $row['created_logged_id'],
                "Transaction_Description" => $transaction_row['Transaction_Description'],
                "date_created" => $row['date_created'],
                "date_time" => $row['date_time'],
                "Current_Ex_Rate" => $row['current_rate'],
                "second_currency_rate" => $row['second_currency_rate'],
                "main_id" => $row['Invoice_ID_Auto'],
                "main_type" => "inv",
                "branch_id" => $row['branch_id'],
                "due_date" => $row['Invoice_Due_Date'],
                "main_nb" => $main_nb,
                "prefix" => $prefix,
                "job_id" => $row['job_id'],
                "project_id" => $row['project_id']
            ];
            JV::addTransaction($dat_1, $is_actual);
            $dat_2 = ["JV_ID" => $transaction_row['JV_ID'],
                "Transaction_Reference" => $ref_num,
                "Transaction_Account" => $row['main_account_id'],
                "Transaction_Type" => "Debit",
                "Transaction_Amount" => $row['Invoice_Net_Total'],
                "Transaction_Date" => $row['Invoice_Date'],
                "Currency_ID" => $row['Invoice_Currency'],
                "Logged_ID" => $row['Logged_ID'],
                "created_logged_id" => $row['created_logged_id'],
                "Transaction_Description" => $transaction_row['Transaction_Description'],
                "date_created" => $row['date_created'],
                "date_time" => $row['date_time'],
                "Current_Ex_Rate" => $row['current_rate'],
                "second_currency_rate" => $row['second_currency_rate'],
                "main_id" => $row['Invoice_ID_Auto'],
                "main_type" => "inv",
                "branch_id" => $row['branch_id'],
                "due_date" => $row['Invoice_Due_Date'],
                "main_nb" => $main_nb,
                "prefix" => $prefix,
                "job_id" => $row['job_id'],
                "project_id" => $row['project_id']
            ];
            JV::addTransaction($dat_2, $is_actual);
            $dat_3 = ["JV_ID" => $transaction_row['JV_ID'],
                "Transaction_Reference" => $ref_num,
                "Transaction_Account" => $row['shipping_account_id'],
                "Transaction_Type" => "Debit",
                "Transaction_Amount" => $row['Invoice_Shipping'],
                "Transaction_Date" => $row['Invoice_Date'],
                "Currency_ID" => $row['Invoice_Currency'],
                "Logged_ID" => $row['Logged_ID'],
                "created_logged_id" => $row['created_logged_id'],
                "Transaction_Description" => $transaction_row['Transaction_Description'],
                "date_created" => $row['date_created'],
                "date_time" => $row['date_time'],
                "Current_Ex_Rate" => $row['current_rate'],
                "second_currency_rate" => $row['second_currency_rate'],
                "main_id" => $row['Invoice_ID_Auto'],
                "main_type" => "inv",
                "branch_id" => $row['branch_id'],
                "due_date" => $row['Invoice_Due_Date'],
                "main_nb" => $main_nb,
                "prefix" => $prefix,
                "job_id" => $row['job_id'],
                "project_id" => $row['project_id']
            ];
            JV::addTransaction($dat_3, $is_actual);
        }
        $error = $this->db->error();
        if (empty($error))
            $this->db->query('COMMIT');
        else
            $this->db->query('ROLLBACK');
        return $error;
    }

}
