webadmin.php

/var/www/hkosl.com/ifapc/html_20210817/IFAPC2021/application/controllers/webadmin/Bk_score.php

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

class Bk_score extends MY_Controller
{
    public function __construct()
    {
        // set $this->table and $this->model
        parent::__construct(substr(__CLASS__, 3));

        $this->block_methods = array('sort');

        $this->updated_single_language_fields = array(
        );
        $tmp = array(
            'reference_no' => __('Reference no'),
            'category_id' => __('Category'),
            'application_id' => __('Application'),
            'point' => __('Point'),
            'total' => __('Total'),
            'count' => __('Count'),


            'judge_id' => __('Judge'),

            'nominee_title' => __('Title'),
            'nominee_tel' => __('Contact Tel./ Moile No.'),
            'nominee_wechat' => __('Wechat Account'),

            'nominee_assistant_title' => __('Assistant’s Title'),
            'nominee_assistant_email' => __('Assistant’s Email'),
            'nominee_assistant_tel' => __('Assistant’s Contact No.'),

            'company_name' => __('Name of Present Company Name'),
            'company_position' => __('Present Position'),
            'company_industry' => __('Industry'),
            'company_stock_code' => __('Stock code'),

            'nominator_title' => __('Title'),
            'nominator_job_title' => __('Present Position'),
            'nominator_company' => __('Name of Present Company'),
            'nominator_tel' => __('Office Tel./ Mobile No.'),
            'nominator_address' => __('Correspondence Address'),

            'nominee_email' => __('Email'),
            'nominator_email' => __('Email'),

            'remark' => __('Remark'),
        );
        $this->single_language_fields = array_merge($this->updated_single_language_fields, $tmp);

        $this->updated_multiple_language_fields = array(
        );
        $tmp = array(
            'nominee_name_' => __('Name'),
            'nominee_assistant_name_' => __('Assistant’s Name'),

            'nominator_name_' => __('Name'),
        );
        $this->multiple_language_fields = array_merge($this->updated_multiple_language_fields, $tmp);

        $this->single_language_encpytion_fields = array(
            'nominee_birth_place', 'nominee_tel', 'nominee_wechat', 'nominee_assistant_tel', 'company_name', 'company_address', 'company_position', 'company_industry', 'company_website', 'narrative_enterprise', 'narrative_industry', 'narrative_contribution', 'narrative_corporate', 'narrative_personal', 'nominator_job_title', 'nominator_company', 'nominator_tel', 'nominator_address', 'nominator_reason', 'nominee_name_tc', 'nominee_name_en', 'nominee_assistant_name_tc', 'nominee_assistant_name_en', 'nominator_name_tc', 'nominator_name_en'
        );
    }

    public function search_application($application_id)
    {
        if (count(Application_model::get_certain($application_id))) {
            $_SESSION['searches_score']['application_id'] = $application_id;
        }
        redirect(admin_url('bk_score'));
    }

    public function index($page = 1)
    {
        if (!empty($_SESSION['searches_score'])) {
            $_POST['searches'] = $_SESSION['searches_score'];
            unset($_SESSION['searches_score']);
        }
        $this->search = $_POST['searches'];

        $this->load->vars('applications', Application_model::get_data());
        $this->load->vars('judges', Judge_model::get_data());
        $this->load->vars('categories', Category_model::get_data());
        parent::index($page);
    }

    public function detial($application_id, $judge_id)
    {
        if (!count(Score_status_model::get_data_by_field_with_value(['application_id' => $application_id, 'judge_id' => $judge_id, 'judge_submitted' => 1]))) {
            redirect(admin_url('bk_score'));
        }
        $data['model'] = $this->model->get_data_by_field_with_value(['application_id' => $application_id, 'judge_id' => $judge_id]);
        $data['remark'] = Score_remark_model::get_data_by_field_with_value(['application_id' => $application_id, 'judge_id' => $judge_id])[0]->remark;

        $data["id"] = $id;
        $data["languages"] = $this->languages;
        $data["table"] = $this->table;
        $data["permission"] = $this->permission;

        $data["block_methods"] = $this->block_methods;
        $data["redirect_link"] = $this->redirect_link;

        $data['validate_fields'] = $this->get_concat_single_and_multiple_language_fields('validate');
        $data['name_fields'] = $this->get_concat_single_and_multiple_language_fields('name');

        $this->load->view('webadmin/' . $this->table . '_detial', $data);
    }

    public function export()
    {
        $from = $this->input->post('from');
        $to = $this->input->post('to');

        if (empty($from) || empty($to) || strtotime($from) > strtotime($to)) {
            exit();
        }

        $name_fields = $this->get_concat_single_and_multiple_language_fields('name');

        $spreadsheet = new PhpOffice\PhpSpreadsheet\Spreadsheet();

        $spreadsheet->getProperties()->setTitle('Office 2007 XLSX Test Document')
            ->setSubject('Office 2007 XLSX Test Document')
            ->setDescription('Test document for Office 2007 XLSX, generated using PHP classes.')
            ->setKeywords('office 2007 openxml php')
            ->setCategory('Test result file');

        $spreadsheet->getDefaultStyle()->getFont()->setSize(8);

        $activeSheet = $spreadsheet->setActiveSheetIndex(0);
        $activeSheet->getDefaultColumnDimension()->setWidth(30);
        $activeSheet->setTitle('report');

        $fields = array('reference_no', 'category_id', 'total', 'count');

        $excel_styles = [
            'borders' => [
                'bottom' => ['borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN],
                'right' => ['borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN],
                'left' => ['borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN],
            ],
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            ],
        ];

        $fields_count = 0;
        foreach ($fields as $k => $field) {
            $activeSheet->getStyle(num2alpha($k) . '1')->applyFromArray($excel_styles);
            $activeSheet->setCellValue(num2alpha($k) . '1', $name_fields[$field]);
            $fields_count++;
        }
        $judges = Judge_model::get_data();
        foreach ($judges as $k => $judge) {
            $activeSheet->getStyle(num2alpha($k + $fields_count) . '1')->applyFromArray($excel_styles);
            $activeSheet->setCellValue(num2alpha($k + $fields_count) . '1', $judge['name']);
        }

        $excel_styles = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT,
            ],
        ];

        $model = $this->model->get_data_by_searching(['from' => $from, 'to' => $to]);
        $row_num = 2;
        $application_id_arr = [];
        foreach ($model as $key => $row) {
            if (!in_array($row['application_id'], $application_id_arr)) {
                $application_id_arr[] = $row['application_id'];
            }
            $fields_count = 0;
            foreach ($fields as $k => $field) {
                $cell = num2alpha($k) . ($row_num);

                $value = empty($row[$field]) ? '' : in_array($field, $this->single_language_encpytion_fields) ? $this->encryption->decrypt($row[$field]) : $row[$field];
                if ($field == 'date_of_birth' && $value == '0000-00-00') {
                    $value = '';
                } else if ($field == 'category_id') {
                    $value = Category_model::get_certain($value)->{'name_' . get_wlang()};
                }
                $activeSheet->getStyle($cell)->applyFromArray($excel_styles);
                $activeSheet->setCellValue($cell, $value);
                $fields_count++;
            }

            foreach ($judges as $k => $judge) {
                $cell = num2alpha($k + $fields_count) . ($row_num);

                $value = empty($row[$field]) ? '' : in_array($field, $this->single_language_encpytion_fields) ? $this->encryption->decrypt($row[$field]) : $row[$field];
                if ($field == 'date_of_birth' && $value == '0000-00-00') {
                    $value = '';
                }
                $activeSheet->getStyle($cell)->applyFromArray($excel_styles);
                $activeSheet->setCellValue($cell, $row['judge'][$judge['id']]);
            }
            $row_num++;
        }

        $spreadsheet->createSheet();
        $activeSheet = $spreadsheet->setActiveSheetIndex(1);
        $activeSheet->getDefaultColumnDimension()->setWidth(20);
        $activeSheet->setTitle('detail');

        $excel_styles = [
            'borders' => [
                'top' => ['borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN],
                'bottom' => ['borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN],
                'right' => ['borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN],
                'left' => ['borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN],
            ],
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            ],
        ];

        $activeSheet->mergeCells('C1:H1');
        $activeSheet->setCellValue('C1', __('Nominee’s Personal Info'));
        $activeSheet->getStyle('C1:H1')->applyFromArray($excel_styles);

        $activeSheet->mergeCells('I1:M1');
        $activeSheet->setCellValue('I1', __('Nominee’s Assistant Info'));
        $activeSheet->getStyle('I1:M1')->applyFromArray($excel_styles);

        $activeSheet->mergeCells('N1:Q1');
        $activeSheet->setCellValue('N1', __('Nominee’s Company Info'));
        $activeSheet->getStyle('N1:Q1')->applyFromArray($excel_styles);

        $activeSheet->mergeCells('R1:X1');
        $activeSheet->setCellValue('R1', __('Nominator’s Personal Info'));
        $activeSheet->getStyle('R1:X1')->applyFromArray($excel_styles);

        // 'Nominee’s Personal Info', 'Nominee’s Assistant Info', 'Nominee’s Company Info'
        $fields = array('reference_no', 'category_id', 'nominee_title', 'nominee_name_tc', 'nominee_name_en', 'nominee_email', 'nominee_tel', 'nominee_wechat', 'nominee_assistant_title', 'nominee_assistant_name_tc', 'nominee_assistant_name_en', 'nominee_assistant_email', 'nominee_assistant_tel', 'company_name', 'company_position', 'company_industry', 'company_stock_code', 'nominator_title', 'nominator_name_tc', 'nominator_name_en', 'nominator_job_title', 'nominator_company', 'nominator_email', 'nominator_tel', 'judge', 'total', 'point_1', 'point_2', 'point_3', 'point_4', 'point_5', 'remark');

        $show_fields = array('judge', 'total', 'point_1', 'point_2', 'point_3', 'point_4', 'point_5', 'remark');

        $fields_count = 0;
        foreach ($fields as $k => $field) {
            $activeSheet->getStyle(num2alpha($k) . '2')->applyFromArray($excel_styles);
            if (empty($name_fields[$field])) {
                if (strpos($field, 'point_') !== false) {
                    $scoring_item_id = substr($field, strlen('point_'), strlen($field));
                    $title = Scoring_item_model::get_certain($scoring_item_id)->{'name_' . get_wlang()};
                } else {
                    $title = _($field);
                }
            } else {
                $title = $name_fields[$field];
            }
            $activeSheet->setCellValue(num2alpha($k) . '2', $title);
            $fields_count++;
        }

        $excel_styles = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT,
            ],
        ];

        $row_num = 3;
        foreach ($application_id_arr as $application_id) {
            $model = $this->model->get_data_by_application_id($application_id, ['from' => $from, 'to' => $to]);

            foreach ($model as $key => $row) {
                $fields_count = 0;
                foreach ($fields as $k => $field) {
                    if ($key == 0 || in_array($field, $show_fields)) {
                        $cell = num2alpha($k) . ($row_num);

                        $value = empty($row[$field]) ? '' : in_array($field, $this->single_language_encpytion_fields) ? $this->encryption->decrypt($row[$field]) : $row[$field];

                        if ($field == 'category_id') {
                            $value = Category_model::get_certain($value)->{'name_' . get_wlang()};
                        } else if ($field == 'nominee_email') {
                            $value = Member_model::get_certain($row['nominee_member_id'])->email;
                        } else if ($field == 'nominator_email') {
                            $value = Member_model::get_certain($row['nominator_member_id'])->email;
                        } else if ($field == 'nominee_title') {
                            $titles = ['ms' => __('Ms'), 'mrs' => __('Mrs'), 'miss' => __('Miss'), 'dr' => __('Dr')];
                            $value = $titles[$value];
                        } else if ($field == 'nominee_assistant_title') {
                            $titles = ['mr' => __('Mr'), 'ms' => __('Ms'), 'miss' => __('Miss')];
                            $value = $titles[$value];
                        } else if ($field == 'nominator_title') {
                            $titles = ['mr' => __('Mr'), 'mrs' => __('Mrs'), 'ms' => __('Ms'), 'miss' => __('Miss'), 'dr' => __('Dr')];
                            $value = $titles[$value];
                        }

                        $activeSheet->getStyle($cell)->applyFromArray($excel_styles);
                        $activeSheet->setCellValue($cell, $value);
                    }
                    $fields_count++;
                }
                $row_num++;
            }
        }

        $content_type = 'application/vnd.ms-excel';
        $writer_type = 'Xls';
        $file_extension = '.xls';

        $filename = $this->table . '_' . $from . '_' . $to;

        header('Content-Type: ' . $content_type);
        header('Content-Disposition: attachment;filename="' . $filename . $file_extension . '"');
        header('Cache-Control: max-age=0');
        // If you're serving to IE 9, then the following may be needed
        header('Cache-Control: max-age=1');
        // If you're serving to IE over SSL, then the following may be needed
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0

        $writer = PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, $writer_type);
        ob_end_clean();

        $writer->save('php://output');
        exit;
    }

}