<?php
/**
 * @link      http://www.u-bo.com
 * @copyright 南京友博网络科技有限公司 
 * @license   http://www.u-bo.com/license/
 */

namespace home\modules\reimbursement\models;

use Yii;
use yii\db\ActiveRecord;
use yii\db\Query;
use yii\helpers\Url;
use yii\helpers\ArrayHelper;
use qcbase\models\Auth;
use qcbase\models\UserDepartment;
use home\modules\task\models\Task;
use home\modules\reimbursement\models\Category;
use home\modules\reimbursement\models\Application;
use home\modules\reimbursement\models\Verify;
use yii\base\ErrorException;
use common\models\PermissionScope;
use home\models\Announcement;
use PHPExcel;
use PHPExcel_IOFactory;
use PHPExcel_Style_Alignment;
use PHPExcel_Style_Border;
use common\components\Tool;

/**
 * 报销表 ({{%reimbursement}})
 *
 * @property integer $reim_id  (ID)
 * @property string $name  (标题)
 * @property string $link_type  (关联类型)
 * @property string $link_id  (关联ID)
 * @property integer $appli_id  (报销申请单ID)
 * @property string $verify_ids  (审核人 关联表qc_user)
 * @property string $reim_people  (报销人)
 * @property integer $reim_time  (产生日期)
 * @property decimal $reim_money  (金额)
 * @property smallint $bill_amount  (票据张数)
 * @property smallint $category_id  (报销类别ID (关联qc_reimbursement_category表)
 * @property string $verify_record  (审批记录)
 * @property string $remark  (备注)
 * @property integer $verify_time  (报销时间)
 * @property integer $create_time  (创建时间)
 * @property integer $update_time  (最后更新时间)
 * @property smallint $isverify  (审核状态   0待审核   1通过   2否决)
 * @property smallint $status  (状态  99逻辑删除)
 * @property integer $user_id  (创建人ID)
 * @property string $ip  (创建人IP)
 *
 * @author Strief http://blog.u-bo.com
 * @since 0.1
 */
class Reimbursement extends ActiveRecord
{
    const ISVERIFY_WAIT = 0; //审核状态:待审核
    const ISVERIFY_ENABLE = 1; //审核状态:通过
    const ISVERIFY_DISABLE = 2; ///审核状态:否决

    const ISCLEARING_WAIT = 0; //结算状态:未结算
    const ISCLEARING_ENABLE = 1; //结算状态:已结算

    public $link_name;
    public $category_name;
    public $verify_names;
    public $appli_name;
    public $appli_isverify;
    public $verify_status;
    public $verify_remark;
    public $verify_id;
    public $isSave = true;
    public $showLink = true; //是否显示报销关联
    public $showAppli = false; //是否显示关联申请单
    public $isShow = true; //审核页面里是否显示审核项
    public $isSelect = true; //关联项是否可选
    public $maxMoney = 300;

    /**
     * @inheritdoc
     */
    public static function tableName()
    {
        return '{{%reimbursement}}';
    }

    /**
     * @inheritdoc
     */
    public function rules()
    {
        return [
            [['name', 'link_type', 'link_id', 'verify_names', 'reim_people', 'reim_money', 'bill_amount', 'reim_time'], 'required'],
            [['bill_amount', 'category_id', 'create_time', 'update_time', 'isverify', 'status', 'user_id', 'appli_id'], 'integer'],
            [['reim_money', 'appli_id'], 'number'],
            [['reim_money'], 'number', 'max' => 99999999.99],
            [['reim_people', 'reim_time'], 'string', 'max' => 50],
            [['remark', 'verify_ids', 'verify_record'], 'string', 'max' => 255],
            [['ip'], 'string', 'max' => 15],
        ];
    }

    /**
     * @inheritdoc
     */
    public function attributeLabels()
    {
        return [
            'reim_id' => 'ID',
            'name' => '用途',
            'link_type' => '关联类型',
            'link_id' => '关联',
            'appli_id' => '报销申请单ID',
            'verify_ids' => '审核人',
            'reim_people' => '报销人',
            'reim_time' => '产生日期',
            'reim_money' => '金额(元)',
            'bill_amount' => '票据张数',
            'category_id' => '类别',
            'verify_record' => '审批记录',
            'remark' => '备注',
            'verify_time' => '报销日期',
            'create_time' => '创建时间',
            'update_time' => '最后更新时间',
            'isverify' => '审核状态',
            'status' => '状态',
            'user_id' => '创建人ID',
            'ip' => '创建人Ip',
            'category_name' => '类别',
            'link_name' => '关联',
            'verify_names' => '审核人',
            'appli_name' => '申请单名称',
            'appli_isverify' => '申请单状态',
        ];
    }

    public function init()
    {
        parent::init();
        $this->status = 1;
        $this->appli_id = 0;
        $this->bill_amount = 1;
        $this->reim_people = Yii::$app->user->identity->realname;
        $this->reim_time = date('Y-m-d', time());
    }

    /**
     * 公共搜索所需信息
     * 
     * 这里返回公共搜索的信息，我们希望所有的搜索走统一的配置
     * 返回值里的键名为keywords的数组为搜索里关键字搜索所支持的字段
     * 返回值里的键名为status的数组为搜索里状态搜索所支持的字段
     */
    public function search()
    {
        return [
            'keywords' => [
                'name' => '用途',
            ],
            'status' => self::getStatusList(),
            'isclearing' => [
                self::ISCLEARING_WAIT => '未结算',
                self::ISCLEARING_ENABLE => '已结算',
            ],
        ];
    }

    /**
     * 不同角色进入时的列表排序(搭配SORT_ASC使用)
     * 
     * 使用方法
     * ~~
     * $query->orderBy([
     *      Reimbursement::isverifyOrderBy('reim') => SORT_ASC,
     *      'reim_id' => SORT_DESC,
     *      'create_time' => SORT_DESC,
     *      ]);
     * ~~
     *
     * @param string $type 角色类型('reim'报销人 'verify'审核人)
     * @param string $pk 排序字段
     * 
     * @return string 排序格式
     */
    public static function isverifyOrderBy($type = 'reim', $pk = 'isverify')
    {
        $reimConfig = Yii::$app->cache->get('reimbursement_config');
        if ($type == 'reim') {
            $reimOrder = (isset($reimConfig['reimbursement_reim_order'])) ? $reimConfig['reimbursement_reim_order'] : [self::ISVERIFY_DISABLE, self::ISVERIFY_WAIT, self::ISVERIFY_ENABLE];
            //对于报销数据提交人，按被否决、待审核、审核通过的优先级排序
            //return "INSTR('" . self::ISVERIFY_DISABLE . "," . self::ISVERIFY_WAIT . "," . self::ISVERIFY_ENABLE . "', " . $pk . ")";
            return "INSTR('" . implode(',', $reimOrder) . "', " . $pk . ")";
        } else {
            $reimOrder = (isset($reimConfig['reimbursement_verify_order'])) ? $reimConfig['reimbursement_verify_order'] : [self::ISVERIFY_WAIT, self::ISVERIFY_DISABLE, self::ISVERIFY_ENABLE];
            //对于审核人操作，按待审核、被否决、审核通过的优先级排序
            //return "INSTR('" . self::ISVERIFY_WAIT . "," . self::ISVERIFY_DISABLE . "," . self::ISVERIFY_ENABLE . "', " . $pk . ")";
            return "INSTR('" . implode(',', $reimOrder) . "', " . $pk . ")";
        }
    }

    /**
     * 返回状态列表 
     */
    public static function getStatusList()
    {
        return [
            self::ISVERIFY_ENABLE => '通过',
            self::ISVERIFY_DISABLE => '否决',
            //self::ISVERIFY_WAIT => '取消',
        ];
    }

    /**
     * 关联获取任务信息
     */
    public function getTask()
    {
        return $this->hasOne(Task::className(), ['task_id' => 'link_id']);
    }

    /**
     * 关联获取报销类别表信息
     */
    public function getCategory()
    {
        return $this->hasOne(Category::className(), ['cat_id' => 'category_id']);
    }

    /**
     * 关联获取报销申请单表信息
     */
    public function getApplication()
    {
        return $this->hasOne(Application::className(), ['appli_id' => 'appli_id']);
    }

    /**
     * 插入更新前置操作
     */
    public function beforeSave($insert)
    {
        if (parent::beforeSave($insert)) {
            //判断是否是插入操作，若为修改操作则判断是否为否决项重新提交
            if ($this->isNewRecord) {
                $this->user_id = Yii::$app->user->id;
                $this->ip = Yii::$app->getRequest()->getUserIP();
//                $this->reim_time = time();
                $this->create_time = time();
                if ($this->appli_id != 0) {
                    $command = Yii::$app->db->createCommand();
                    $result = $command->update('{{%reimbursement_application}}', ['isused' => Application::ISUSED_DISABLE], 'appli_id = :appli_id', [':appli_id' => $this->appli_id])->execute();
                }
            } elseif ($this->isverify == self::ISVERIFY_DISABLE) {
                //将当前项重新设置为审核中
                $this->isverify = 0;

                //若操作为编辑，则向审核人发送系统通知
                $url = '/reimbursement/reimbursement/detail';
                $title =   $this->name . '-' . Yii::$app->user->identity->realname;
                $content = Yii::$app->user->identity->realname . '于' . date('Y-m-d H:m:s', time()) . '提交“' . $this->name . '”报销申请！';
                $content .= ' <a target="_blank" href="' .  Url::to([$url, 'id' => $this->reim_id]) . '">点击查看</a>';
                $content = str_replace('wap.php', 'index.php', $content);

                Announcement::Send($title, $content, explode(',', $this->verify_ids), 3);

                //删除原先已有的审核记录
                //$verify = Verify::deleteVerify('reim', $this->reim_id);
            }
            $this->reim_time = strtotime($this->reim_time);
            $this->update_time = time();
            return true;
        } else {
            return false;
        }
    }

    /**
     * 插入更新后置操作
     */
    public function afterSave($insert, $changedAttributes)
    {
        parent::afterSave($insert, $changedAttributes);
        if ($insert) {
            //若操作为新增，则向审核人发送系统通知
            $url = '/reimbursement/reimbursement/detail';
            $title =  $this->name . '-' . Yii::$app->user->identity->realname;
            $content = Yii::$app->user->identity->realname . '于' . date('Y-m-d H:m:s', time()) . '提交“' . $this->name . '”报销申请！';
            $content .= ' <a target="_blank" href="' . Url::to([$url, 'id' => $this->reim_id]) . '">点击查看</a>';
            $content = str_replace('wap.php', 'index.php', $content);
            Announcement::Send($title, $content, explode(',', $this->verify_ids), 3);
        }

        \qcbase\models\ActionLog::insertLog('home', '修改', json_encode($changedAttributes));
    }

    /**
     * 工作台调取报销信息
     * @param  integer $userId 当前用户ID
     * @param  integer $limit  显示数量
     * @param  array   $order  排序
     */
    public static function getReimbursement($userId, $limit = 5, $order = ['create_time' => SORT_DESC])
    {
        $reims = static::find()
            ->orderBy($order)
            ->limit($limit)
            ->asArray()
            ->indexBy('reim_id');
        $reims->where(['user_id' => $userId]);
        $reims = $reims->all();
        return $reims;
    }

    /**
     * 权限审核
     * @param  string  $url     操作URL
     * @param  integer $user_id 用户ID
     */
    public static function readAccess($url = null, $user_id = 0)
    {
        if (empty($url)) {
            return false;
        }
        empty($user_id) ? $user_id = \Yii::$app->user->id : $user_id;
        //非超级管理员，需要检测权限
        if (Yii::$app->session->get('isSuperAdmin')) {
            return true;
        }
        //检测是否具有某一特定权限
        if (Auth::checkAccess($url, $user_id)) {
            return true;
        }
        return false;
    }

    /**
     * 某项报销的某项操作对当前用户是否有操作权限
     * 
     * @param string $action 当前操作
     * @param integer $reim_id 需要验证的报销id
     * @return boolean 有权限返回true，没有则返回false
     */
    public static function checkAccess($action = null, $reim_id = 0)
    {
        if (empty($action) || empty($reim_id)) {
            return false;
        }

        $data = self::findOne(['reim_id' => $reim_id]);

        switch ($action) {
            case 'detail':
                $detailUser = PermissionScope::getScopeUser('reimbursement/reimbursement/detail');
                if (!empty($detailUser) && !in_array($data->user_id, $detailUser) && !in_array(Yii::$app->user->id, explode(',', $data->verify_ids))) {
                    return false;
                }
                break;

            case 'view':
                $viewUser = PermissionScope::getScopeUser('reimbursement/reimbursement/view');
                if ($data->isverify == Reimbursement::ISVERIFY_DISABLE && !(!empty($viewUser) && !in_array($data->user_id, $viewUser))) {
                    break;
                } elseif ($data->isverify == Reimbursement::ISVERIFY_WAIT && (in_array(Yii::$app->user->id, explode(',', $data->verify_ids)) || empty($viewUser))) {
                    break;
                }else {
                    return false;
                }

            case 'update':
                $viewUser = PermissionScope::getScopeUser('reimbursement/reimbursement/update');
                if ($data->isverify == Reimbursement::ISVERIFY_DISABLE && !(!empty($viewUser) && !in_array($data->user_id, $viewUser))) {
                    break;
                } elseif ($data->isverify == Reimbursement::ISVERIFY_WAIT && (in_array(Yii::$app->user->id, explode(',', $data->verify_ids)) || empty($viewUser))) {
                    break;
                }else {
                    return false;
                }
                
            case 'verify':
                if ($data->isverify == Reimbursement::ISVERIFY_WAIT && in_array(Yii::$app->user->id, explode(',', $data->verify_ids)) && Reimbursement::readAccess('reimbursement/reimbursement/verify')) {
                    break;
                } else {
                    return false;
                }

            case 'delete':
                $deleteUser = PermissionScope::getScopeUser('reimbursement/reimbursement/delete');
                if ((!empty($deleteUser) && !in_array($data->user_id, $deleteUser)) || $data->isverify == Reimbursement::ISVERIFY_ENABLE) {
                    return false;
                }
                break;

            case 'set-status':
                if ($data->isverify == Reimbursement::ISVERIFY_WAIT && in_array(Yii::$app->user->id, explode(',', $data->verify_ids)) && Reimbursement::readAccess('reimbursement/reimbursement/verify')) {
                    break;
                } else {
                    return false;
                }

            case 'set-isclearing':
                if ($data->isverify == Reimbursement::ISVERIFY_ENABLE && Reimbursement::readAccess('reimbursement/reimbursement/verify')) {
                    break;
                } else {
                    return false;
                }

            default:
                return false;
            }

        return true;
    }

    /**
     * 获取报销人直属上级领导
     */
    public static function getDeptLeader()
    {
        $user_list = Yii::$app->cache->get('user_list');
        if (!isset($user_list)) {
            throw new ErrorException('请更新缓存');
        }
        $user_id = Yii::$app->user->id;
        /* if (Yii::$app->session->get('isSuperAdmin')) {
          $leader_id = 1;
          } else { */
        $pk = (Yii::$app->cache->get('reimbursement_config')['reimbursement_user_select'] == 'verify') ? 'verify_user_id' : 'lead_user_id';
        $leader_id = UserDepartment::getDeptLeaderByUser($user_id, 'reimbursement/reimbursement/verify', $pk);
        //}
        return [
            'leader_id' => $leader_id,
            'leader_name' => $user_list[$leader_id]['realname']
        ];
    }

    /**
     * 报销统计
     */
    public static function getReimByCategory($cat_id, $map, $type, $beginTime, $endTime)
    {
        $where = "SELECT count(reim_id), sum(reim_money), sum(bill_amount), count(if(isverify = :ISVERIFY_WAIT";
        $where .= ",true, null)) AS wait, count(if(isverify = :ISVERIFY_ENABLE";
        $where .= ",true, null)) AS enable, count(if(isverify = :ISVERIFY_DISABLE";
        $where .= ",true, null)) AS disable FROM qc_reimbursement WHERE category_id =:cat_id";

        if ($map == 'month') {
            $begin_time = mktime(0, 0, 0, date('m'), 1, date('Y'));
            $end_time = mktime(23, 59, 59, date('m'), date('t'), date('Y'));
            $where .= " AND (reim_time BETWEEN :begin_time AND :end_time)";
        } elseif ($map == 'week') {
            $begin_time = mktime(0, 0, 0, date('m'), date('d') - date('w') + 1, date('Y'));
            $end_time = mktime(23, 59, 59, date('m'), date('d') - date('w') + 7, date('Y'));
            $where .= " AND (reim_time BETWEEN :begin_time AND :end_time)";
        }

        if ($type == 'reim') {
            $where .= " AND user_id = :user_id";
        } elseif ($type == 'verify') {
            $where .= " AND FIND_IN_SET(:user_id, verify_ids)";
        }

        if (!empty($beginTime) && !empty($endTime)) {
            $beginTime = (strtotime($beginTime) == false) ? $beginTime : strtotime($beginTime);
            $endTime = (strtotime($endTime) == false) ? $endTime : strtotime($endTime) + 86399;
            $where .= " AND (reim_time BETWEEN :beginTime AND :endTime)";
        }

        $command = Yii::$app->db->createCommand($where);
        $command->bindValue(':ISVERIFY_WAIT', self::ISVERIFY_WAIT);
        $command->bindValue(':ISVERIFY_ENABLE', self::ISVERIFY_ENABLE);
        $command->bindValue(':ISVERIFY_DISABLE', self::ISVERIFY_DISABLE);
        $command->bindValue(':cat_id', $cat_id);

        if ($map == 'month' || $map == 'week') {
            $command->bindValue(':begin_time', $begin_time);
            $command->bindValue(':end_time', $end_time);
        }
        
        if ($type == 'reim' || $type == 'verify') {
            $command->bindValue(':user_id', Yii::$app->user->id);
        }
        
        if (!empty($beginTime) && !empty($endTime)) {
            $command->bindValue(':beginTime', $beginTime);
            $command->bindValue(':endTime', $endTime);    
        }
        
        $reims = $command->queryAll();
        return $reims;
    }

    /**
     * 报销报表导出
     * 
     * @param  string $title 导出的表的名称
     * @param  [type] $data  导出的数据
     * @param  string $beginTime 开始时间
     * @param  string $endTime 结束时间
     */
    public static function pointExport($title = '', $data, $beginTime, $endTime)
    {
        $countTitle = '';
        //若当前是按某一人进行导出的，则标题前显示该人的名称
        if (isset($data->params[':ids'])) {
            $ids = $data->params[':ids'];
            $idsArray = explode(',', $ids);
            if (count($idsArray) == 1) {
                $user = Yii::$app->cache->get('user_list');
                $countTitle = "【" . $user[$ids]['realname'] . "】-- ";
            }
        }

        $data = $data->with('category')->asArray()->orderBy(['user_id' => SORT_ASC, 'reim_time' => SORT_ASC])->all();
        if (empty($data)) {
            throw new ErrorException('当前查询下无数据，无法导出！');
        }
        $countMoney = 0;
        $beginTime = (strtotime($beginTime) == false) ? $beginTime : strtotime($beginTime);
        $endTime = (strtotime($endTime) == false) ? $endTime : strtotime($endTime);
        $beginTimeYear = date('Y', $beginTime);
        $beginTimeMonth = date('m', $beginTime);
        $endTimeYear = date('Y', $endTime);
        $endTimeMonth = date('m', $endTime);
        if ($beginTimeYear == $endTimeYear && $beginTimeMonth == $endTimeMonth) {
            $countTitle .= $beginTimeYear . '年' . $beginTimeMonth . '月 ';
        } else {
            $countTitle .= $beginTimeYear . '年' . $beginTimeMonth . '月--' . $endTimeYear . '年' . $endTimeMonth . '月 ';
        }

        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . iconv("UTF-8", "GB2312", $countTitle . '报销') . '.xls"');
        header('Cache-Control: max-age=0');

        $objPHPExcel = new PHPExcel();

        $objPHPExcel->setActiveSheetIndex(0);
        $objActSheet = $objPHPExcel->getActiveSheet();

        // Set document properties
        $objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
            ->setLastModifiedBy("Maarten Balliauw")
            ->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");

        $cellArray = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J');
        $widthArray = array(15, 30, 15, 15, 15, 15, 15, 30, 30, 15);
        foreach ($cellArray as $key => $value) {
            $objPHPExcel->getActiveSheet()->getColumnDimension($value)->setWidth($widthArray[$key]);
        }
        $objActSheet->getStyle('A:J')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objActSheet->getStyle('B')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        $objActSheet->getStyle('E')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        $objActSheet->getStyle('H')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        $objActSheet->getStyle('I')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        $objActSheet->mergeCells('A1:J1')->setCellValue('A1', $countTitle . '报销');
        $objActSheet->setCellValue('A2', '日期');
        $objActSheet->setCellValue('B2', '项目/销售机会/客户名');
        $objActSheet->setCellValue('C2', '产生日期');
        $objActSheet->setCellValue('D2', '提交日期');
        $objActSheet->setCellValue('E2', '报销人');
        $objActSheet->setCellValue('F2', '张数');
        $objActSheet->setCellValue('G2', '报销项目');
        $objActSheet->setCellValue('H2', '用途');
        $objActSheet->setCellValue('I2', '备注');
        $objActSheet->setCellValue('J2', '报销金额/元');

        $row = 3;
        foreach ($data as $key => $value) {
            //关联类型前加类型名
            $name = '';
            if ($value['link_type'] == 'task') {
                //若关联的是任务，则查询该任务关联的项目，若有则显示该项目名，若查不到项目，则直接显示该任务
                $link = \common\models\Common::getLinkData($value['link_type'], $value['link_id']);
                $task = Task::findOne(ArrayHelper::getValue($link, 'task_id'));
                $link_id = ArrayHelper::getValue($link, 'link_id');
                if ($task['link_type'] == 'project' && $link_id != null) {
                    $name = ArrayHelper::getValue(\common\models\Common::getLinkData('project', $link_id), 'name');
                }

                if ($name != '') {
                    $name = '【项目】' . $name;
                } else {
                    $name = '【任务】' . ArrayHelper::getValue($link, 'name');
                }
            }

            if ($value['link_type'] == 'sale_opportunity') {
                $name = '【销售机会】' . ArrayHelper::getValue(\common\models\Common::getLinkData($value['link_type'], $value['link_id']), 'name');
            }

            if ($value['link_type'] == 'customer') {
                $name = '【客户】' . ArrayHelper::getValue(\common\models\Common::getLinkData($value['link_type'], $value['link_id']), 'name');
            }

            $objActSheet->setCellValue('A' . $row, date('Y-m', $value['reim_time']));
            $objActSheet->setCellValue('B' . $row, $name);
            $objActSheet->setCellValue('C' . $row, date('Y-m-d', $value['reim_time']));
            $objActSheet->setCellValue('D' . $row, date('Y-m-d', $value['create_time']));
            $objActSheet->setCellValue('E' . $row, $value['reim_people']);
            $objActSheet->setCellValue('F' . $row, $value['bill_amount']);
            $objActSheet->setCellValue('G' . $row, $value['category']['name']);
            $objActSheet->setCellValue('H' . $row, $value['name']);
            $objActSheet->setCellValue('I' . $row, $value['remark']);
            $objActSheet->setCellValue('J' . $row, $value['reim_money']);
            $countMoney += $value['reim_money'];
            $row = $row + 1;
        }
        $objActSheet->setCellValue('A' . $row, '合计');
        $objActSheet->setCellValue('B' . $row, '');
        $objActSheet->mergeCells('C' . $row . ':I' . $row)->setCellValue('C' . $row, '');
        $objActSheet->setCellValue('J' . $row, $countMoney);

        $styleArray = [
            'borders' => [
                'allborders' => [
                    'style' => PHPExcel_Style_Border::BORDER_THIN,
                    'color' => ['argb' => 'FF000000'],
                ]
            ],
        ];
        $styleArray1 = [
            'font' => [
                'bold' => true,
                'color' => ['argb' => '00000000'],
            ],
            'alignment' => [
                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            ],
        ];
        $objPHPExcel->getActiveSheet()->getStyle('A1:J' . ($row - 1))->applyFromArray($styleArray);
        $objPHPExcel->getActiveSheet()->getStyle('A2:J2')->applyFromArray($styleArray1);

        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
    }
}
