<?php

defined('BASEPATH') or exit('No direct script access allowed');

class Cron_model extends CI_Model
{
    public function __construct()
    {
        parent::__construct();
        $this->lang->admin_load('cron');
    }

    public function getSettings()
    {
        $q = $this->db->get_where('settings', ['setting_id' => 1], 1);
        if ($q->num_rows() > 0) {
            return $q->row();
        }
        return false;
    }

    public function run_cron()
    {
        $m = [];
        if ($this->resetOrderRef()) {
            $m[] = lang('order_ref_updated');
        }
        if ($pendingInvoices = $this->getAllPendingInvoices()) {
            $p = 0;
            foreach ($pendingInvoices as $invoice) {
                $this->updateInvoiceStatus($invoice->id);
                $p++;
            }
            $m[] = sprintf(lang('x_pending_to_due'), $p);
        }
        if ($partialInvoices = $this->getAllPPInvoices()) {
            $pp = 0;
            foreach ($partialInvoices as $invoice) {
                $this->updateInvoiceStatus($invoice->id);
                $pp++;
            }
            $m[] = sprintf(lang('x_partial_to_due'), $pp);
        }
        if ($unpaidpurchases = $this->getUnpaidPuchases()) {
            $up = 0;
            foreach ($unpaidpurchases as $purchase) {
                $this->db->update('purchases', ['payment_status' => 'due'], ['id' => $purchase->id]);
                $up++;
            }
            $m[] = sprintf(lang('x_purchases_changed'), $up);
        }
        if ($pis = $this->get_expired_products()) {
            $e  = 0;
            $ep = 0;
            foreach ($pis as $pi) {
                $this->db->update('purchase_items', ['quantity_balance' => 0], ['id' => $pi->id]);
                $e++;
                $ep += $pi->quantity_balance;
            }
            $this->site->syncQuantity(null, null, $pis);
            $m[] = sprintf(lang('x_products_expired'), $e, $ep);
        }
        if ($promos = $this->getPromoProducts()) {
            $pro = 0;
            foreach ($promos as $pr) {
                $this->db->update('products', ['promotion' => 0], ['id' => $pr->id]);
                $pro++;
            }
            $m[] = sprintf(lang('x_promotions_expired'), $pro);
        }
        $date = date('Y-m-d H:i:s', strtotime('-1 month'));
        if ($this->deleteUserLgoins($date)) {
            $m[] = sprintf(lang('user_login_deleted'), $date);
        }
        if ($this->db_backup()) {
            $m[] = lang('backup_done');
        }
        if ($this->checkUpdate()) {
            $m[] = lang('update_available');
        }
        $r = !empty($m) ? $m : false;
        $this->send_email($r);
        // $this->db->truncate('sessions');
        $this->db->delete('sessions', ['timestamp <' => time() - 60 * 60 * 48]);
        return $r;
    }

    public function send_email($details)
    {
        if ($details) {
            $table_html = '';
            $tables     = $this->cron_model->yesterday_report();
            foreach ($tables as $table) {
                $table_html .= $table . '<div style="clear:both"></div>';
            }
            foreach ($details as $detail) {
                $table_html = $table_html . $detail;
            }
            $msg_with_yesterday_report = $table_html;
            $owners                    = $this->db->get_where('users', ['group_id' => 1])->result();
            $this->load->library('email');
            $config['useragent'] = 'Stock Manager Advance';
            $config['protocol']  = $this->Settings->protocol;
            $config['mailtype']  = 'html';
            $config['crlf']      = "\r\n";
            $config['newline']   = "\r\n";
            if ($this->Settings->protocol == 'sendmail') {
                $config['mailpath'] = $this->Settings->mailpath;
            } elseif ($this->Settings->protocol == 'smtp') {
                $config['smtp_host'] = $this->Settings->smtp_host;
                $config['smtp_user'] = $this->Settings->smtp_user;
                $config['smtp_pass'] = $this->Settings->smtp_pass;
                $config['smtp_port'] = $this->Settings->smtp_port;
                if (!empty($this->Settings->smtp_crypto)) {
                    $config['smtp_crypto'] = $this->Settings->smtp_crypto;
                }
            }
            $this->email->initialize($config);

            foreach ($owners as $owner) {
                list($user, $domain) = explode('@', $owner->email);
                if ($domain != 'tecdiary.com') {
                    $this->load->library('parser');
                    $parse_data = [
                        'name'      => $owner->first_name . ' ' . $owner->last_name,
                        'email'     => $owner->email,
                        'msg'       => $msg_with_yesterday_report,
                        'site_link' => base_url(),
                        'site_name' => $this->Settings->site_name,
                        'logo'      => '<img src="' . base_url('assets/uploads/logos/' . $this->Settings->logo) . '" alt="' . $this->Settings->site_name . '"/>',
                    ];
                    $msg     = file_get_contents('./themes/' . $this->Settings->theme . '/admin/views/email_templates/cron.html');
                    $message = $this->parser->parse_string($msg, $parse_data);
                    $subject = lang('cron_job') . ' - ' . $this->Settings->site_name;

                    $this->email->from($this->Settings->default_email, $this->Settings->site_name);
                    $this->email->to($owner->email);
                    $this->email->subject($subject);
                    $this->email->message($message);
                    $this->email->send();
                }
            }
        }
    }

    private function checkUpdate()
    {
        $fields = ['version' => $this->Settings->version, 'code' => $this->Settings->purchase_code, 'username' => $this->Settings->envato_username, 'site' => base_url()];
        $this->load->helper('update');
        $protocol = is_https() ? 'https://' : 'http://';
        $updates  = get_remote_contents($protocol . 'tecdiary.com/api/v1/update/', $fields);
        $response = json_decode($updates);
        if (!empty($response->data->updates)) {
            $this->db->update('settings', ['update' => 1], ['setting_id' => 1]);
            return true;
        }
        return false;
    }

    private function db_backup()
    {
        $this->load->dbutil();
        $prefs = [
            'format'   => 'txt',
            'filename' => 'sma_db_backup.sql',
        ];
        $back    = $this->dbutil->backup($prefs);
        $backup  = &$back;
        $db_name = 'db-backup-on-' . date('Y-m-d-H-i-s') . '.txt';
        $save    = './files/backups/' . $db_name;
        $this->load->helper('file');
        write_file($save, $backup);

        $files = glob('./files/backups/*.txt', GLOB_BRACE);
        $now   = time();
        foreach ($files as $file) {
            if (is_file($file)) {
                if ($now - filemtime($file) >= 60 * 60 * 24 * 30) {
                    unlink($file);
                }
            }
        }

        return true;
    }

    private function deleteUserLgoins($date)
    {
        $this->db->where('time <', $date);
        if ($this->db->delete('user_logins')) {
            return true;
        }
        return false;
    }

    private function gen_html($costing, $discount, $expenses, $returns, $purchases, $sales, $warehouse = null)
    {
        $html = '<div style="border:1px solid #DDD; padding:10px; margin:10px 0;"><h3>' . ($warehouse ? $warehouse->name . ' (' . $warehouse->code . ')' : lang('all_warehouses')) . '</h3>
        <table width="100%" class="stable">
        <tr>
            <td style="border-bottom: 1px solid #EEE;">' . lang('products_sale') . '</td>
            <td style="text-align:right; border-bottom: 1px solid #EEE;">' . $this->sma->formatMoney($costing->sales) . '</td>
        </tr>';
        if ($discount && $discount->order_discount > 0) {
            $html .= '
            <tr>
                <td style="border-bottom: 1px solid #DDD;">' . lang('order_discount') . '</td>
                <td style="text-align:right;border-bottom: 1px solid #DDD;">' . $this->sma->formatMoney($discount->order_discount) . '</td>
            </tr>';
        }
        $html .= '
        <tr>
            <td style="border-bottom: 1px solid #EEE;">' . lang('products_cost') . '</td>
            <td style="text-align:right; border-bottom: 1px solid #EEE;">' . $this->sma->formatMoney($costing->cost) . '</td>
        </tr>';
        if ($expenses && $expenses->total > 0) {
            $html .= '
            <tr>
                <td style="border-bottom: 1px solid #DDD;">' . lang('expenses') . '</td>
                <td style="text-align:right;border-bottom: 1px solid #DDD;">' . $this->sma->formatMoney($expenses->total) . '</td>
            </tr>';
        }
        $html .= '
        <tr>
            <td width="300px;" style="border-bottom: 1px solid #DDD;"><strong>' . lang('profit') . '</strong></td>
            <td style="text-align:right;border-bottom: 1px solid #DDD;">
                <strong>' . $this->sma->formatMoney($costing->sales - $costing->cost - ($discount ? $discount->order_discount : 0) - ($expenses ? $expenses->total : 0)) . '</strong>
            </td>
        </tr>';
        if (isset($returns->total)) {
            $html .= '
            <tr>
                <td width="300px;" style="border-bottom: 2px solid #DDD;"><strong>' . lang('return_sales') . '</strong></td>
                <td style="text-align:right;border-bottom: 2px solid #DDD;"><strong>' . $this->sma->formatMoney($returns->total) . '</strong></td>
            </tr>';
        }
        $html .= '</table><h4 style="margin-top:15px;">' . lang('general_ledger') . '</h4>
        <table width="100%" class="stable">';
        if ($sales) {
            $html .= '
            <tr>
                <td width="33%" style="border-bottom: 1px solid #DDD;">' . lang('total_sales') . ': <strong>' . $this->sma->formatMoney($sales->total_amount) . '(' . $sales->total . ')</strong></td>
                <td width="33%" style="border-bottom: 1px solid #DDD;">' . lang('received') . ': <strong>' . $this->sma->formatMoney($sales->paid) . '</strong></td>
                <td width="33%" style="border-bottom: 1px solid #DDD;">' . lang('taxes') . ': <strong>' . $this->sma->formatMoney($sales->tax) . '</strong></td>
            </tr>';
        }
        if ($purchases) {
            $html .= '
            <tr>
                <td width="33%">' . lang('total_purchases') . ': <strong>' . $this->sma->formatMoney($purchases->total_amount) . '(' . $purchases->total . ')</strong></td>
                <td width="33%">' . lang('paid') . ': <strong>' . $this->sma->formatMoney($purchases->paid) . '</strong></td>
                <td width="33%">' . lang('taxes') . ': <strong>' . $this->sma->formatMoney($purchases->tax) . '</strong></td>
            </tr>';
        }
        $html .= '</table></div>';
        return $html;
    }

    private function get_expired_products()
    {
        if ($this->Settings->remove_expired) {
            $date = date('Y-m-d');
            $this->db->where('expiry <=', $date)->where('expiry !=', null)->where('expiry !=', '0000-00-00')->where('quantity_balance >', 0);
            $q = $this->db->get('purchase_items');
            if ($q->num_rows() > 0) {
                foreach (($q->result()) as $row) {
                    $data[] = $row;
                }
                return $data;
            }
        }
        return false;
    }

    private function getAllPendingInvoices()
    {
        $today    = date('Y-m-d');
        $paid     = $this->lang->line('paid');
        $canceled = $this->lang->line('cancelled');
        $q        = $this->db->get_where('sales', ['due_date <=' => $today, 'due_date !=' => '1970-01-01', 'due_date !=' => null, 'payment_status' => 'pending']);
        if ($q->num_rows() > 0) {
            foreach (($q->result()) as $row) {
                $data[] = $row;
            }
            return $data;
        }
        return false;
    }

    private function getAllPPInvoices()
    {
        $today    = date('Y-m-d');
        $paid     = $this->lang->line('paid');
        $canceled = $this->lang->line('cancelled');
        $q        = $this->db->get_where('sales', ['due_date <=' => $today, 'due_date !=' => '1970-01-01', 'due_date !=' => null, 'payment_status' => 'partial']);
        if ($q->num_rows() > 0) {
            foreach (($q->result()) as $row) {
                $data[] = $row;
            }
            return $data;
        }
        return false;
    }

    private function getCosting($date, $warehouse_id = null)
    {
        $this->db->select('SUM( COALESCE( purchase_unit_cost, 0 ) * quantity ) AS cost, SUM( COALESCE( sale_unit_price, 0 ) * quantity ) AS sales, SUM( COALESCE( purchase_net_unit_cost, 0 ) * quantity ) AS net_cost, SUM( COALESCE( sale_net_unit_price, 0 ) * quantity ) AS net_sales', false);
        $this->db->where('costing.date', $date);
        if ($warehouse_id) {
            $this->db->join('sales', 'sales.id=costing.sale_id')
            ->where('sales.warehouse_id', $warehouse_id);
        }

        $q = $this->db->get('costing');
        if ($q->num_rows() > 0) {
            return $q->row();
        }
        return false;
    }

    private function getExpenses($sdate, $edate, $warehouse_id = null)
    {
        $this->db->select('SUM( COALESCE( amount, 0 ) ) AS total', false);
        $this->db->where('date >=', $sdate)->where('date <=', $edate);
        if ($warehouse_id) {
            $this->db->where('warehouse_id', $warehouse_id);
        }

        $q = $this->db->get('expenses');
        if ($q->num_rows() > 0) {
            return $q->row();
        }
        return false;
    }

    private function getOrderDiscount($sdate, $edate, $warehouse_id = null)
    {
        $this->db->select('SUM( COALESCE( order_discount, 0 ) ) AS order_discount', false);
        $this->db->where('date >=', $sdate)->where('date <=', $edate);
        if ($warehouse_id) {
            $this->db->where('warehouse_id', $warehouse_id);
        }

        $q = $this->db->get('sales');
        if ($q->num_rows() > 0) {
            return $q->row();
        }
        return false;
    }

    private function getOrderRef()
    {
        $q = $this->db->get_where('order_ref', ['ref_id' => 1], 1);
        if ($q->num_rows() > 0) {
            return $q->row();
        }
        return false;
    }

    private function getPromoProducts()
    {
        $today = date('Y-m-d');
        $q     = $this->db->get_where('products', ['promotion' => 1, 'end_date !=' => null, 'end_date <=' => $today]);
        if ($q->num_rows() > 0) {
            foreach (($q->result()) as $row) {
                $data[] = $row;
            }
            return $data;
        }
        return false;
    }

    private function getReturns($sdate, $edate, $warehouse_id = null)
    {
        $this->db->select('SUM( COALESCE( grand_total, 0 ) ) AS total', false)
        ->where('sale_status', 'returned');
        $this->db->where('date >=', $sdate)->where('date <=', $edate);
        if ($warehouse_id) {
            $this->db->where('warehouse_id', $warehouse_id);
        }

        $q = $this->db->get('sales');
        if ($q->num_rows() > 0) {
            return $q->row();
        }
        return false;
    }

    private function getTotalPurchases($sdate, $edate, $warehouse_id = null)
    {
        $this->db->select('count(id) as total, sum(COALESCE(grand_total, 0)) as total_amount, SUM(COALESCE(paid, 0)) as paid, SUM(COALESCE(total_tax, 0)) as tax', false)
            ->where('status !=', 'pending')
            ->where('date >=', $sdate)->where('date <=', $edate);
        if ($warehouse_id) {
            $this->db->where('warehouse_id', $warehouse_id);
        }
        $q = $this->db->get('purchases');
        if ($q->num_rows() > 0) {
            return $q->row();
        }
        return false;
    }

    private function getTotalSales($sdate, $edate, $warehouse_id = null)
    {
        $this->db->select('count(id) as total, sum(COALESCE(grand_total, 0)) as total_amount, SUM(COALESCE(paid, 0)) as paid, SUM(COALESCE(total_tax, 0)) as tax', false)
            ->where('sale_status !=', 'pending')
            ->where('date >=', $sdate)->where('date <=', $edate);
        if ($warehouse_id) {
            $this->db->where('warehouse_id', $warehouse_id);
        }
        $q = $this->db->get('sales');
        if ($q->num_rows() > 0) {
            return $q->row();
        }
        return false;
    }

    private function getUnpaidPuchases()
    {
        $today = date('Y-m-d');
        $q     = $this->db->get_where('purchases', ['payment_status !=' => 'paid', 'payment_status !=' => 'due', 'payment_term >' => 0, 'due_date !=' => null, 'due_date <=' => $today]);
        if ($q->num_rows() > 0) {
            foreach (($q->result()) as $row) {
                $data[] = $row;
            }
            return $data;
        }
        return false;
    }

    private function resetOrderRef()
    {
        if ($this->Settings->reference_format == 1 || $this->Settings->reference_format == 2) {
            $month = date('Y-m') . '-01';
            $year  = date('Y') . '-01-01';
            if ($ref = $this->getOrderRef()) {
                $reset_ref = ['so' => 1, 'qu' => 1, 'po' => 1, 'to' => 1, 'pos' => 1, 'do' => 1, 'pay' => 1, 'ppay' => 1, 're' => 1, 'rep' => 1, 'ex' => 1, 'qa' => 1];
                if ($this->Settings->reference_format == 1 && strtotime($ref->date) < strtotime($year)) {
                    $reset_ref['date'] = $year;
                    $this->db->update('order_ref', $reset_ref, ['ref_id' => 1]);
                    return true;
                } elseif ($this->Settings->reference_format == 2 && strtotime($ref->date) < strtotime($month)) {
                    $reset_ref['date'] = $month;
                    $this->db->update('order_ref', $reset_ref, ['ref_id' => 1]);
                    return true;
                }
            }
        }
        return false;
    }

    private function updateInvoiceStatus($id)
    {
        if ($this->db->update('sales', ['payment_status' => 'due'], ['id' => $id])) {
            return true;
        }
        return false;
    }

    private function yesterday_report()
    {
        $date       = date('Y-m-d', strtotime('-1 day'));
        $sdate      = $date . ' 00:00:00';
        $edate      = $date . ' 23:59:59';
        $warehouses = $this->db->get('warehouses')->result();
        foreach ($warehouses as $warehouse) {
            $costing         = $this->getCosting($date, $warehouse->id);
            $discount        = $this->getOrderDiscount($sdate, $edate, $warehouse->id);
            $expenses        = $this->getExpenses($sdate, $edate, $warehouse->id);
            $returns         = $this->getReturns($sdate, $edate, $warehouse->id);
            $total_purchases = $this->getTotalPurchases($sdate, $edate, $warehouse->id);
            $total_sales     = $this->getTotalSales($sdate, $edate, $warehouse->id);
            $html[]          = $this->gen_html($costing, $discount, $expenses, $returns, $total_purchases, $total_sales, $warehouse);
        }

        $costing         = $this->getCosting($date);
        $discount        = $this->getOrderDiscount($sdate, $edate);
        $expenses        = $this->getExpenses($sdate, $edate);
        $returns         = $this->getReturns($sdate, $edate);
        $total_purchases = $this->getTotalPurchases($sdate, $edate);
        $total_sales     = $this->getTotalSales($sdate, $edate);
        $html[]          = $this->gen_html($costing, $discount, $expenses, $returns, $total_purchases, $total_sales);

        return $html;
    }
}
