<?php

use Akeneo\Component\SpreadsheetParser\SpreadsheetParser;

/**
 * This is the model class for table "{{vendor_fileupload_status}}".
 *
 * The followings are the available columns in table '{{vendor_fileupload_status}}':
 * @property integer $id
 * @property integer $fileupload_id
 * @property integer $vendor_id
 * @property integer $status
 * @property integer $total_product
 * @property integer $sync_status
 * @property string $date_published
 *
 * The followings are the available model relations:
 * @property Fileupload $fileupload
 * @property Vendor $vendor
 */
class VendorFile extends CActiveRecord
{
    const STATUS_PENDING = 1;
    const STATUS_PARSING = 2;
    const STATUS_FOR_REVIEW = 3;
    const STATUS_PUSHING = 4;
    const STATUS_PUSHED = 5;
    const STATUS_DELETED = 6;
    const STATUS_ERROR = 7;
    const STATUS_ARCHIVED = 8;

    const SYNC_STATUS_SYNCING = 0;
    const SYNC_STATUS_SYNCED = 1;
    /**
     * @return string the associated database table name
     */
    public function tableName()
    {
        return '{{vendor_file}}';
    }

    /**
     * @return array validation rules for model attributes.
     */
    public function rules()
    {
        // NOTE: you should only define rules for those attributes that
        // will receive user inputs.
        return array(
            array('fileupload_id, vendor_id', 'required'),
            array('fileupload_id, vendor_id, status, total_product, sync_status', 'numerical', 'integerOnly'=>true),
            // The following rule is used by search().
            // @todo Please remove those attributes that should not be searched.
            array('id, fileupload_id, vendor_id, status, total_product, sync_status, date_published', 'safe', 'on'=>'search'),
        );
    }

    /**
     * @return array relational rules.
     */
    public function relations()
    {
        // NOTE: you may need to adjust the relation name and the related
        // class name for the relations automatically generated below.
        return array(
            'fileupload' => array(self::BELONGS_TO, 'Fileupload', 'fileupload_id'),
            'vendor' => array(self::BELONGS_TO, 'Vendor', 'vendor_id')
        );
    }

    /**
     * @return array customized attribute labels (name=>label)
     */
    public function attributeLabels()
    {
        return array(
            'id' => 'ID',
            'fileupload_id' => 'Fileupload',
            'vendor_id' => 'Vendor',
            'status' => 'Status',
            'total_product' => 'Total Record',
            'sync_status' => 'Sync Status'
        );
    }

    /**
     * Retrieves a list of models based on the current search/filter conditions.
     *
     * Typical usecase:
     * - Initialize the model fields with values from filter form.
     * - Execute this method to get CActiveDataProvider instance which will filter
     * models according to data in model fields.
     * - Pass data provider to CGridView, CListView or any similar widget.
     *
     * @return CActiveDataProvider the data provider that can return the models
     * based on the search/filter conditions.
     */
    public function search()
    {
        // @todo Please modify the following code to remove attributes that should not be searched.

        $criteria=new CDbCriteria;

        $criteria->compare('id', $this->id);
        $criteria->compare('fileupload_id', $this->fileupload_id);
        $criteria->compare('vendor_id', $this->vendor_id);
        $criteria->compare('status', $this->status);
        $criteria->compare('total_product', $this->total_product);
        $criteria->compare('sync_status', $this->sync_status);

        return new CActiveDataProvider($this, array(
            'criteria'=>$criteria,
        ));
    }

    /**
     * Returns the static model of the specified AR class.
     * Please note that you should have this exact method in all your CActiveRecord descendants!
     * @param string $className active record class name.
     * @return VendorFileStatus the static model class
     */
    public static function model($className=__CLASS__)
    {
        return parent::model($className);
    }

    public function getData()
    {
        $data = self::model()->findAll(array(
            'condition' => 'status NOT IN ('. self::STATUS_DELETED .', '. self::STATUS_ARCHIVED .')',
            'order' => 'id DESC'
        ));

        return $data;
    }

    public function getDataArchive()
    {
        $data = self::model()->findAll(array(
            'condition' => 'status IN ('. self::STATUS_ARCHIVED .')',
            'order' => 'id DESC'
        ));

        return $data;
    }

    public static function getStatusList($withColor = false)
    {
        if ($withColor == true) {
            return [
                self::STATUS_PENDING => '<span class="label label-default">Pending</span>',
                self::STATUS_PARSING => '<span class="label label-primary">Parsing</span>',
                self::STATUS_FOR_REVIEW => '<span class="label label-info">For Review</span>',
                self::STATUS_PUSHING => '<span class="label label-warning">Publishing</span>',
                self::STATUS_PUSHED => '<span class="label label-success">Published</span>',
                self::STATUS_ERROR => '<span class="label label-danger">Errors</span>',
                self::STATUS_ARCHIVED => '<span class="label label-danger">Archived</span>'
            ];
        } else {
            return [
                self::STATUS_PENDING => 'Pending',
                self::STATUS_PARSING => 'Parsing',
                self::STATUS_FOR_REVIEW => 'For Review',
                self::STATUS_PUSHING => 'Publishing',
                self::STATUS_PUSHED => 'Published',
                self::STATUS_DELETED => 'Deleted',
                self::STATUS_ERROR => 'Errors',
                self::STATUS_ARCHIVED => 'Archived',
            ];
        }
    }

    public static function getStatusLabel($index)
    {
        return self::getStatusList()[$index];
    }

    public static function getStatusLabelWithColor($index)
    {
        return self::getStatusList(true)[$index];
    }

    public static function getPendingFiles()
    {
        $results = self::model()->findAll(array(
            'condition' => 'status = :status',
            'params' => array(':status' => self::STATUS_PENDING),
            'order' => 'id DESC'
        ));

        return $results;
    }

    public static function getPendingFile()
    {
        $file = self::model()->find(array(
            'condition' => 'status = :status',
            'params' => array(':status' => self::STATUS_PENDING),
            'order' => 'id ASC',
        ));

        return $file;
    }

    public static function getParsingFile()
    {
        $file = self::model()->find(array(
            'condition' => 'status = :status',
            'params' => array(':status' => self::STATUS_PARSING),
            'order' => 'id DESC',
        ));

        return $file;
    }

    public function parsedSpreedSheetData($options = [])
    {
        $parsedData = array();

        $spreedSheetFile = $this->fileupload->getAbsPath();

        if (!$spreedSheetFile) {
            throw new Exception('Parser initial: Spreadsheet file is not exists.');
        }

        $spreedSheetParser = SpreadsheetParser::open($spreedSheetFile);

        // validate column mapping setting
        $settingColumnMapping = SettingColumnMapping::model()->find();

        if (!$settingColumnMapping) {
            throw new Exception('Parser initial: Column mapping setting is not set.');
        }
        
        switch ($this->vendor_id) {
            case Vendor::INDEX_MOTOBIX:
                $parsedData = $this->_parsedSpreedSheetMotobix($spreedSheetParser, $options);
            break;

            case Vendor::INDEX_FLIR:
                $parsedData = $this->_parsedSpreedSheetFlir($spreedSheetParser, $options);
            break;

            case Vendor::INDEX_ACTI:
                $parsedData = $this->_parsedSpreedSheetActi($spreedSheetParser, $options);
            break;

            case Vendor::INDEX_GEO_VISION:
                $parsedData = $this->_parsedSpreedSheetGeoVision($spreedSheetParser, $options);
            break;

            case Vendor::INDEX_VIVOTEK:
                $parsedData = $this->_parsedSpreedSheetVivotek($spreedSheetParser, $options);
            break;

            case Vendor::INDEX_AXIS:
                $parsedData = $this->_parsedSpreedSheetAxis($spreedSheetParser, $options);
            break;

            case Vendor::INDEX_BOSCH:
                $parsedData = $this->_parsedSpreedSheetBosch($spreedSheetParser, $options);
            break;

            case Vendor::INDEX_ISONAS:
                $parsedData = $this->_parsedSpreedSheetIsonas($spreedSheetParser, $options);
            break;

            case Vendor::INDEX_QNAP:
                $parsedData = $this->_parsedSpreedSheetQnap($spreedSheetParser, $options);
            break;

            case Vendor::INDEX_RAYTEC:
                $parsedData = $this->_parsedSpreedSheetRaytec($spreedSheetParser, $options);
            break;

            case Vendor::INDEX_ENTRUST_DATACARD:
                $parsedData = $this->_parsedSpreedSheetEntrustDatacard($spreedSheetParser, $options);
            break;

            case Vendor::INDEX_AV_COSTAR:
                $parsedData = $this->_parsedSpreedSheetAVCostar($spreedSheetParser, $options);
            break;

            case Vendor::INDEX_LOCKNETICS:
                $parsedData = $this->_parsedSpreedSheetLocknetics($spreedSheetParser, $options);
            break;

            case Vendor::INDEX_DIGITAL_WATCHDOG:
                $parsedData = $this->_parsedSpreedSheetDigitalWatchDog($spreedSheetParser, $options);
            break;

            case Vendor::INDEX_SCHLAGE:
                $parsedData = $this->_parsedSpreedSheetSchlage($spreedSheetParser, $options);
            break;

            case Vendor::INDEX_USA_VISION:
                $parsedData = $this->_parsedSpreedSheetUsaVision($spreedSheetParser, $options);
            break;

            case Vendor::INDEX_AXTON:
                $parsedData = $this->_parsedSpreedSheetAxton($spreedSheetParser, $options);
            break;

            case Vendor::INDEX_ABB:
                $parsedData = $this->_parsedSpreedSheetABB($spreedSheetParser, $options);
            break;

            default:
                throw new Exception('Parser initial: Vendor not yet supported');
        }

        return $parsedData;
    }

    /* Processing data from spreadsheet to database */
    public function normalizedSpreedSheetData($parsedData)
    {
        $processDataCount = 0;
        //Dev::pvx($normalizedSpreedSheetData);
        $settingDefaultValue = SettingDefaultValue::model()->find(array(
            'condition' => 'status = 1'
        ));
        $processData = [];
        foreach ($parsedData as $dataIndex => $dataValue) {

            $vendorNormalizedExists = VendorNormalized::model()->findAll(array(
                'alias' => 'vn',
                'join' => 'join {{vendor_normalized_info}} as vni on vn.id = vni.vendor_normalized_id',
                'condition' => '(vni.mpn = :mpn AND vn.sheet_row_index_code = :sheet_row_index_code) AND vn.vendor_fileupload_id = :vendorfile_id',
                'params' => [
                    ':mpn' => $dataValue['mpn'],
                    ':sheet_row_index_code' => $dataValue['sheet_row_index_code'],
                    ':vendorfile_id' => $this->id,
                ]
            ));

            if(empty($vendorNormalizedExists)){
                $data[] = $dataValue;
                $transaction = Yii::app()->db->beginTransaction();
                $vendorNormalized = new VendorNormalized;

                $vendorNormalized->sheet_row_index_code = (string) $dataValue['sheet_row_index_code'];
                $vendorNormalized->vendor_fileupload_id = $this->id;
                
                $vendorName = ucwords(strtolower(Vendor::getVendorNameLabel($this->vendor_id)));

                if (isset($dataValue['categories'])) {
                    $vendorNormalized->categories = $dataValue['categories'];
                }

                if ($vendorNormalized->validate() && $vendorNormalized->save()) {
                    $vendorNormalizedInfo = new VendorNormalizedInfo;
                
                    $vendorNormalizedInfo->vendor_normalized_id = $vendorNormalized->id;

                    $vendorNormalizedInfo->brand_name = Vendor::getVendorNameLabel($vendorNormalized->vendorFile->vendor->id);

                    if (empty($dataValue['mpn'])) {
                        $vendorNormalized->status = VendorNormalized::STATUS_ERROR_VALIDATION;
                        $vendorNormalized->report = 'Empty MPN';
                    } else {
                        $parsedDataExists = $this->isParsedDataExists($dataValue['mpn']);

                        if ($parsedDataExists) {
                            $vendorNormalized->status = VendorNormalized::STATUS_ERROR_VALIDATION;
                            $vendorNormalized->report = 'Duplicate MPN on Spreadsheet';
                        }

                        $normalizedLockPricing = VendorNormalizedPricingLock::model()->find([
                            'condition' => 'mpn = :mpn AND status = :status',
                            'params' => [
                                ':mpn' => trim($dataValue['mpn']),
                                ':status' => VendorNormalizedPricingLock::STATUS_ACTIVE,
                            ]
                        ]);
        
                        if ($normalizedLockPricing) {
                            $vendorNormalizedInfo->price_sbb = $normalizedLockPricing->price;
                        }

                    }

                    // validate
                    if (!isset($dataValue['mpn'])) {
                        throw new Exception('Parser Final: MPN is missing for some products.');
                    }
                    $mpn = trim(preg_replace('/\s\s+/', ' ', $dataValue['mpn']));
                    $mpn = ZCommon::cleanSpecialCharacter($mpn);
                    $vendorNormalizedInfo->mpn = $mpn;
                    $vendorNormalizedInfo->product_name = substr($vendorName.' '.$mpn.' '.strip_tags($dataValue['description']),0,70);
                    $vendorNormalizedInfo->msrp = isset($dataValue['msrp']) ? ZCommon::cleanAmmount($dataValue['msrp']) : 0.00;
                    $vendorNormalizedInfo->cost = isset($dataValue['cost']) ? ZCommon::cleanAmmount($dataValue['cost']) : 0.00;
                    $vendorNormalizedInfo->description = isset($dataValue['description']) ? trim($dataValue['description']) : '';
                    $vendorNormalizedInfo->qb_description = isset($dataValue['description']) ? trim($dataValue['description']) : '';
                    $vendorNormalizedInfo->ean = isset($dataValue['ean']) ? trim($dataValue['ean']) : '';

                    $vendorNormalizedInfo->sku = $vendorNormalizedInfo->brand_name . ':'. $vendorNormalizedInfo->mpn;

                    if (!empty($settingDefaultValue['width'])) {
                        $width = $settingDefaultValue['width'];
                    } else {
                        $width = isset($dataValue['width']) ? trim($dataValue['width']) : 0;
                    }

                    if (!empty($settingDefaultValue['length'])) {
                        $length = $settingDefaultValue['length'];
                    } else {
                        $length = isset($dataValue['length']) ? trim($dataValue['length']) : 0;
                    }

                    if (!empty($settingDefaultValue['height'])) {
                        $height = $settingDefaultValue['height'];
                    } else {
                        $height = isset($dataValue['height']) ? trim($dataValue['height']) : 0;
                    }

                    if (!empty($settingDefaultValue['weight'])) {
                        $weight = $settingDefaultValue['weight'];
                    } else {
                        $weight = isset($dataValue['weight']) ? trim($dataValue['weight']) : 0;
                    }

                    if (!empty($settingDefaultValue['quantity'])) {
                        $quantity = $settingDefaultValue['quantity'];
                    } else {
                        $quantity = isset($dataValue['quantity']) ? trim($dataValue['quantity']) : 0;
                    }

                    $vendorNormalizedInfo->width = isset($dataValue['width']) ? $dataValue['width'] : $width;
                    $vendorNormalizedInfo->length = isset($dataValue['length']) ? $dataValue['length'] : $length;
                    $vendorNormalizedInfo->height = isset($dataValue['height']) ? $dataValue['height'] : $height;
                    $vendorNormalizedInfo->weight = isset($dataValue['weight']) ? $dataValue['weight'] : $weight;

                    if (!$vendorNormalizedInfo->validate()) {
                        throw new Exception('Parser Final: ' . CJSON::encode($vendorNormalizedInfo->getErrors()));
                    }

                    if ($vendorNormalizedInfo->save()) {
                        $vendorNormalized->product_status = VendorNormalized::productCheckStatus($vendorNormalizedInfo->attributes);

                        if (!$vendorNormalized->save(false)) {
                            throw new Exception('Parser Final: something went wrong in normalize product status.');
                        }
                        $transaction->commit(); 
                    } else {
                        $transaction->rollback();
                        throw new Exception('Parser Final: something went wrong in normalized info.');
                    }

                    
                    $processData[] = $vendorNormalizedInfo->attributes;
                } else {
                    $transaction->rollback();
                    throw new Exception('Parser Final: ' . CJSON::encode($vendorNormalized->getErrors()));
                }

                $processDataCount++;
            }
        }
        //Dev::pvx($data);

        if ($this->_isNormalizeDataDone()) {
            $this->_setStatusForReview();
        }
        
        return $processData;
    }   

    /* Processing data from wordpress to database */
    public function normalizedSyncingData($product, $platformIds, $platform){
        try{
            $vendorNormalizeds = VendorNormalized::model()->findAll(array(
                'alias' => 'vn',
                'join' => 'join {{vendor_normalized_info}} as vni on vn.id = vni.vendor_normalized_id',
                'condition' => 'vni.sku = :sku AND vn.vendor_fileupload_id = :vendorfile_id',
                'params' => [
                    ':sku' => $product->sku,
                    ':vendorfile_id' => $this->id,
                ]
            ));

            //check the product on sbb/gss
            if(!isset($platformIds['platform_1_id']) || $platformIds['platform_1_id'] == ''){
                $sbbSearch = WooCommerce::getProductBySKU($product->sku);
                if(!empty($sbbSearch)){
                    $platformIds['platform_1_id'] = $sbbSearch[0]->id;
                    //$platformIds['platform_1_description'] = $sbbSearch[0]->description;
                }
            }
            if(!isset($platformIds['platform_2_id']) || $platformIds['platform_2_id'] == ''){
                $gssSearch = WooCommerceGSS::getProductBySKU($product->sku);
                if(!empty($gssSearch)){
                    $platformIds['platform_2_id'] = $gssSearch[0]->id;
                    //$platformIds['platform_2_description'] = $gssSearch[0]->description;
                }
            }

            //create EOL vendor normalized if not exist.
            if(empty($vendorNormalizeds)){
                $settingDefaultValue = SettingDefaultValue::model()->find(array(
                    'condition' => 'status = 1'
                ));
                if (!empty($settingDefaultValue['width'])) {
                    $width = $settingDefaultValue['width'];
                } else {
                    $width = isset($dataValue['width']) ? trim($dataValue['width']) : 0;
                }

                if (!empty($settingDefaultValue['length'])) {
                    $length = $settingDefaultValue['length'];
                } else {
                    $length = isset($dataValue['length']) ? trim($dataValue['length']) : 0;
                }

                if (!empty($settingDefaultValue['height'])) {
                    $height = $settingDefaultValue['height'];
                } else {
                    $height = isset($dataValue['height']) ? trim($dataValue['height']) : 0;
                }

                if (!empty($settingDefaultValue['weight'])) {
                    $weight = $settingDefaultValue['weight'];
                } else {
                    $weight = isset($dataValue['weight']) ? trim($dataValue['weight']) : 0;
                }

                if (!empty($settingDefaultValue['quantity'])) {
                    $quantity = $settingDefaultValue['quantity'];
                } else {
                    $quantity = isset($dataValue['quantity']) ? trim($dataValue['quantity']) : 0;
                }

                $brandName = Vendor::getVendorNameLabel($this->vendor_id);
                $vendorName = ucwords(strtolower($brandName));
                $mpn = str_replace($brandName.':', '', $product->sku);
                
                $transaction = Yii::app()->db->beginTransaction();

                $vendorNormalized = new VendorNormalized;
                $vendorNormalized->vendor_fileupload_id = $this->id;
                $vendorNormalized->product_status = VendorNormalized::PRODUCT_STATUS_EOL;
                $vendorNormalized->product_from = VendorNormalized::PRODUCT_FROM_WEBSITE;
                if($this->isParsedDataExists($product->name)){
                    $vendorNormalized->status = VendorNormalized::STATUS_ERROR_VALIDATION;
                    $vendorNormalized->report = 'Duplicate MPN';
                }else{
                    $vendorNormalized->status = VendorNormalized::STATUS_FOR_REVIEW;
                }
                if(isset($platformIds['platform_1_id']) && !empty($platformIds['platform_1_id']) && $platformIds['platform_1_id'] != ''){
                    $vendorNormalized->platform_1_rest_id = $platformIds['platform_1_id'];
                    $vendorNormalized->platform_1_rest_status = VendorNormalized::REST_STATUS_EXISTING;
                }else{
                    $vendorNormalized->platform_1_rest_status = VendorNormalized::REST_STATUS_NEW;
                }
                if(isset($platformIds['platform_2_id']) && !empty($platformIds['platform_2_id']) && $platformIds['platform_2_id'] != ''){
                    $vendorNormalized->platform_2_rest_id = $platformIds['platform_2_id'];
                    $vendorNormalized->platform_2_rest_status = VendorNormalized::REST_STATUS_EXISTING;
                }else{
                    $vendorNormalized->platform_2_rest_status = VendorNormalized::REST_STATUS_NEW;
                }
                if($vendorNormalized->save(false)){
                    $vendorNormalizedInfo = new VendorNormalizedInfo;
                    $vendorNormalizedInfo->vendor_normalized_id = $vendorNormalized->id;
                    $vendorNormalizedInfo->brand_name = $brandName;
                    $vendorNormalizedInfo->mpn = $mpn;
                    $vendorNormalizedInfo->product_name = substr($vendorName.' '.$mpn.' '.strip_tags($product->short_description) ?: strip_tags($product->description),0,70);
                    $vendorNormalizedInfo->sku = $product->sku;
                    $vendorNormalizedInfo->msrp = $product->regular_price;
                    if($platform == 'sbb'){
                        $vendorNormalizedInfo->price_sbb = $product->sale_price;
                    }
                    $vendorNormalizedInfo->description = $product->description;
                    $vendorNormalizedInfo->qb_description = $product->short_description ?: '';
                    $vendorNormalizedInfo->ean = $product->ean;
                    $vendorNormalizedInfo->width = $product->dimensions->width ?: $width;
                    $vendorNormalizedInfo->length = $product->dimensions->length ?: $length;
                    $vendorNormalizedInfo->height = $product->dimensions->height ?: $height;
                    $vendorNormalizedInfo->weight = $product->weight ?: $weight;
                    if($vendorNormalizedInfo->save(false)){
                        $this->total_product = VendorNormalized::countTotalParsed($this->id);
                        if($this->save(false)){
                            $transaction->commit();
                            $message = 'Done create ID: '.$vendorNormalized->id;
                        }else{
                            $error = 'Error updating vendor file ID: '.$vendorFile->id;
                            $transaction->rollback();
                        }
                    }else{
                        $error = 'Error creating vendor normalized info ID: '.$vendorNormalizedInfo->id;
                        $transaction->rollback();
                    }
                }else{
                    $error = 'Error creating vendor normalized ID: '.$vendorNormalized->id;
                }  
            }else{
                foreach ($vendorNormalizeds as $vendorNormalized) {
                    $transaction = Yii::app()->db->beginTransaction();
                    $vendorNormalizedInfo = $vendorNormalized->vendorNormalizedInfo;

                    $brandName = Vendor::getVendorNameLabel($this->vendor_id);
                    $vendorName = ucwords(strtolower($brandName));
                    $mpn = str_replace($brandName.' ', '', $product->name);
                    $mpn = str_replace($brandNameUcWords.' ', '', $mpn);
                    
                    /* Remove description length compare 20220115 
                    $description = (strlen($product->description) >= strlen($vendorNormalizedInfo->description)) ? $product->description :  $vendorNormalizedInfo->description;*/
                    if(empty($vendorNormalized->platform_1_rest_id) && $vendorNormalized->platform_1_rest_status == VendorNormalized::REST_STATUS_PENDING){
                        if(!empty($platformIds['platform_1_id'])){
                            $vendorNormalized->platform_1_rest_id = $platformIds['platform_1_id'];
                            $vendorNormalized->platform_1_rest_status = VendorNormalized::REST_STATUS_EXISTING;
                        }else{
                            $vendorNormalized->platform_1_rest_status = VendorNormalized::REST_STATUS_NEW;
                        }
                    }

                    if(empty($vendorNormalized->platform_2_rest_id) && $vendorNormalized->platform_2_rest_status == VendorNormalized::REST_STATUS_PENDING){
                        if(!empty($platformIds['platform_2_id'])){
                            $vendorNormalized->platform_2_rest_id = $platformIds['platform_2_id'];
                            $vendorNormalized->platform_2_rest_status = VendorNormalized::REST_STATUS_EXISTING;
                        }else{
                            $vendorNormalized->platform_2_rest_status = VendorNormalized::REST_STATUS_NEW;
                        }
                    }

                    if($vendorNormalized->status != VendorNormalized::STATUS_ERROR_VALIDATION)
                        $vendorNormalized->status = VendorNormalized::STATUS_FOR_REVIEW;
                        
                    if($vendorNormalized->save(false)){
                        $vendorNormalizedInfo->product_name = substr((strlen($product->name) >= strlen($vendorNormalizedInfo->product_name) ? $product->name : $vendorNormalizedInfo->product_name),0,70);
                        //remove saving description 20220115
                        // $vendorNormalizedInfo->description = $description;
                        if($vendorNormalized->product_from == VendorNormalized::PRODUCT_FROM_WEBSITE && $platform == 'sbb'){
                            $vendorNormalizedInfo->price_sbb = $product->sale_price;
                        }
                        if((float)$vendorNormalizedInfo->msrp == (float) 0 ){
                            $vendorNormalizedInfo->msrp = $product->regular_price;
                        }
                        if($vendorNormalizedInfo->save(false)){
                            $transaction->commit();
                            $message = 'Done update ID: '.$vendorNormalized->id;
                        }else{
                            $error = 'Error updating vendor normalized info ID: ' .$vendorNormalizedInfo->id;
                            $transaction->rollback();
                        }
                    }else{
                        $error = 'Error updating vendor normalized ID: ' .$product->id;
                        $transaction->rollback();
                    } 
                }
            }

            $result['message'] = $message;
            $result['error'] = $error;
            return $result;
        }catch(Exception $e){
            Dev::pvx($e);
        }
    }

    protected function isParsedDataExists($identifier)
    {
        $data = VendorNormalized::model()->find(array(
            'alias' => 'vn',
            'join' => 'INNER JOIN {{vendor_normalized_info}} AS vni ON vni.vendor_normalized_id = vn.id',
            'condition' => 'vn.vendor_fileupload_id = :vendor_fileupload_id 
				AND (vn.status = :status OR vn.status = :status1)
				AND vni.mpn = :mpn',
            'params' => array(
                ':mpn' => $identifier,
                ':vendor_fileupload_id' => $this->id,
                ':status' => VendorNormalized::STATUS_FOR_REVIEW,
                ':status1' => VendorNormalized::STATUS_PENDING)
        ));

        return $data ? true : false;
    }

    protected function _isNormalizeDataDone()
    {
        $data = VendorNormalized::model()->findAll(array(
            'alias' => 'vn',
            'join' => 'INNER JOIN {{vendor_normalized_info}} AS vni ON vni.vendor_normalized_id = vn.id',
            'condition' => 'vn.vendor_fileupload_id = :vendor_fileupload_id 
				AND vn.status IN ('. VendorNormalized::STATUS_PENDING .', '. VendorNormalized::STATUS_ERROR_VALIDATION .')',
            'params' => array(':vendor_fileupload_id' => $this->id)
        ));

        if (count($data) >= (int)$this->total_product) {
            return true;
        } else {
            return false;
        }
    }

    public function dataCount(){
        $data = VendorNormalized::model()->findAll(array(
            'alias' => 'vn',
            'join' => 'INNER JOIN {{vendor_normalized_info}} AS vni ON vni.vendor_normalized_id = vn.id',
            'condition' => 'vn.vendor_fileupload_id = :vendor_fileupload_id',
            'params' => array(':vendor_fileupload_id' => $this->id)
        ));

        return count($data);
    }

    public function _setStatusParsing()
    {
        $this->status = self::STATUS_PARSING;
        $this->save(false);
    }

    protected function _setStatusForReview()
    {
        $this->status = self::STATUS_FOR_REVIEW;
        $this->save(false);
    }

    public function setStatusPushed()
    {
        $this->status = self::STATUS_PUSHED;
        $this->date_published = date('Y-m-d H:i:s');
        $this->save(false);
    }

    public function setStatusError($report)
    {
        $this->status = self::STATUS_ERROR;
        $this->report = (string) $report;
        $this->save(false);
    }

    public function setSyncStatusSynced()
    {
        $this->sync_status = self::SYNC_STATUS_SYNCED;
        $this->save(false);
    }

    public function resetNormalizeData()
    {
        $datas = VendorNormalized::model()->findAll(array(
            'condition' => 'vendor_fileupload_id = :vendor_fileupload_id
				AND status IN (
					'. VendorNormalized::STATUS_FOR_REVIEW .',
					'. VendorNormalized::STATUS_PUSHING .',
					'. VendorNormalized::STATUS_PUSHED .',
					'. VendorNormalized::STATUS_ERROR_PUSHING .',
					'. VendorNormalized::STATUS_ERROR_VALIDATION .')',
            'params' => array(':vendor_fileupload_id' => $this->id)
        ));

        $error = 0;

        foreach ($datas as $data) {
            $data->status = VendorNormalized::STATUS_DELETED;

            if (!$data->save(false)) {
                $error++;
            }
        }

        if ($error <= 0) {
            return true;
        } else {
            return false;
        }
    }

    public function getParsedData($spreadsheet = false)
    {
        if($spreadsheet){
            $data = VendorNormalized::model()->findAll(array(
                'condition' => 'vendor_fileupload_id = :vendor_fileupload_id
    			 	AND status in (
    					'. VendorNormalized::STATUS_FOR_REVIEW .',
    					'. VendorNormalized::STATUS_PUSHING .',
    					'. VendorNormalized::STATUS_PUSHED .',
    					'. VendorNormalized::STATUS_ERROR .',
    					'. VendorNormalized::STATUS_ERROR_PUSHING .',
    					'. VendorNormalized::STATUS_DELETED .',
    					'. VendorNormalized::STATUS_ERROR_VALIDATION .') AND product_from = :product_from',
                'params' => array(
                    ':vendor_fileupload_id' => $this->id,
                    ':product_from' => VendorNormalized::PRODUCT_FROM_SPREADSHEET,
                )
            ));
        }else{
            $data = VendorNormalized::model()->findAll(array(
                'condition' => 'vendor_fileupload_id = :vendor_fileupload_id
                    AND status in (
                        '. VendorNormalized::STATUS_FOR_REVIEW .',
                        '. VendorNormalized::STATUS_PUSHING .',
                        '. VendorNormalized::STATUS_PUSHED .',
                        '. VendorNormalized::STATUS_ERROR .',
                        '. VendorNormalized::STATUS_ERROR_PUSHING .',
                        '. VendorNormalized::STATUS_DELETED .',
                        '. VendorNormalized::STATUS_ERROR_VALIDATION .')',
                'params' => array(
                    ':vendor_fileupload_id' => $this->id
                )
            ));
        }

        return $data;
    }

    public function getProductCheckDataStatus()
    {
        $productStatus = [
            VendorNormalized::PRODUCT_STATUS_NOT_CHECKED => 0,
            VendorNormalized::PRODUCT_STATUS_NEW  => 0,
            VendorNormalized::PRODUCT_STATUS_UPDATED  => 0,
            VendorNormalized::PRODUCT_STATUS_REMAIN  => 0,
            VendorNormalized::PRODUCT_STATUS_EOL  => 0 		];

        $parsedDatas = VendorNormalized::model()->findAll([
            'condition' => 'vendor_fileupload_id = :vendor_fileupload_id',
            'params' => [':vendor_fileupload_id' => $this->id]
        ]);

        foreach ($parsedDatas as $parsedData) {
            if ($parsedData->product_status == VendorNormalized::PRODUCT_STATUS_NOT_CHECKED) {
                $productStatus[VendorNormalized::PRODUCT_STATUS_NOT_CHECKED]++;
            }

            if ($parsedData->product_status == VendorNormalized::PRODUCT_STATUS_NEW) {
                $productStatus[VendorNormalized::PRODUCT_STATUS_NEW]++;
            }

            if ($parsedData->product_status == VendorNormalized::PRODUCT_STATUS_UPDATED) {
                $productStatus[VendorNormalized::PRODUCT_STATUS_UPDATED]++;
            }

            if ($parsedData->product_status == VendorNormalized::PRODUCT_STATUS_REMAIN) {
                $productStatus[VendorNormalized::PRODUCT_STATUS_REMAIN]++;
            }

            if ($parsedData->product_status == VendorNormalized::PRODUCT_STATUS_EOL) {
                $productStatus[VendorNormalized::PRODUCT_STATUS_EOL]++;
            }
        }

        return $productStatus;
    }

    public function resetOldProducts()
    {
        $resetProductFeeds = [];

        $oldProducts = Product::model()->findAll([
            'condition' => 'vendor_id = :vendor_id AND batch_id <> :batch_id AND status IN ('. Product::STATUS_ACTIVE .','. Product::STATUS_ERROR .')',
            'params' => ['vendor_id' => $this->vendor_id, ':batch_id' => VendorFile::generateBatchId($this->id)]
        ]);

        foreach ($oldProducts as $oldProduct) {
            $oldProduct->status = Product::STATUS_DELETED;
            $oldProduct->save(false);

            $resetProductFeeds[] = $oldProduct->attributes;
        }

        return $resetProductFeeds;
    }

    public static function generateBatchId($id)
    {
        return 'BTC'. $id;
    }

    public static function countStatuses()
    {
        $query = '
			SELECT status, count(*) as count FROM {{vendor_file}}
			WHERE status NOT IN ('. self::STATUS_DELETED .')
			GROUP BY status
		';

        $command = Yii::app()->db->createCommand($query);
        $results = $command->queryAll();
        
        $formatedData = [
            self::STATUS_PENDING => 0,
            self::STATUS_PARSING => 0,
            self::STATUS_FOR_REVIEW => 0,
            self::STATUS_PUSHING => 0,
            self::STATUS_PUSHED => 0,
            self::STATUS_ERROR => 0,
        ];

        foreach ($results as $result) {
            $formatedData[(int) $result['status']] = $result['count'];
        }

        return $formatedData;
    }

    public static function countVendors()
    {
        $query = '
			SELECT vendor_id, count(*) as count FROM {{vendor_file}}
			WHERE status NOT IN ('. self::STATUS_DELETED .')
			GROUP BY vendor_id
		';

        $command = Yii::app()->db->createCommand($query);
        $results = $command->queryAll();
        
        $formatedData = [
            Vendor::INDEX_GEO_VISION => 0,
            Vendor::INDEX_MOTOBIX => 0,
            Vendor::INDEX_FLIR => 0,
            Vendor::INDEX_ACTI => 0,
            Vendor::INDEX_VIVOTEK => 0,
            Vendor::INDEX_AXIS => 0,
            Vendor::INDEX_BOSCH => 0,
            Vendor::INDEX_ISONAS => 0,
            Vendor::INDEX_QNAP => 0,
            Vendor::INDEX_RAYTEC => 0,
            Vendor::INDEX_ENTRUST_DATACARD => 0,
            Vendor::INDEX_AV_COSTAR => 0,
            Vendor::INDEX_LOCKNETICS => 0,
            Vendor::INDEX_DIGITAL_WATCHDOG => 0,
            Vendor::INDEX_SCHLAGE => 0,
            Vendor::INDEX_USA_VISION => 0,
        ];

        foreach ($results as $result) {
            $formatedData[(int) $result['vendor_id']] = $result['count'];
        }

        return $formatedData;
    }

    public static function countSpreadSheet()
    {
        $query = '
			SELECT id FROM {{vendor_file}}
			WHERE status NOT IN ('. self::STATUS_DELETED .', '. self::STATUS_ARCHIVED .')
		';

        $command = Yii::app()->db->createCommand($query);
        $results = $command->queryAll();
        
        return count($results);
    }

    public function countSpreadSheetData()
    {
        $parsedDataCount = 0;

        $spreedSheetFile = $this->fileupload->getAbsPath();
        
        if (!$spreedSheetFile) {
            throw new Exception('Spreadsheet file is not existing in server.');
        }

        $spreedSheetParser = SpreadsheetParser::open($spreedSheetFile);

        if (!$spreedSheetParser) {
            throw new Exception('Spreadsheet file is not readable in server.');
        }

        $settingColumnMapping = SettingColumnMapping::model()->find();

        if ($settingColumnMapping->mpn == '') {
            throw new Exception('MPN column mapping value is not set or empty in <strong>column mapping setting</strong>.');
        }

        switch ($this->vendor_id) {
            case Vendor::INDEX_MOTOBIX:
                $parsedDataCount = count($this->_parsedSpreedSheetMotobix($spreedSheetParser));
            break;
            
            case Vendor::INDEX_FLIR:
                $parsedDataCount = count($this->_parsedSpreedSheetFlir($spreedSheetParser));
            break;

            case Vendor::INDEX_ACTI:
                $parsedDataCount = count($this->_parsedSpreedSheetActi($spreedSheetParser));
            break;

            case Vendor::INDEX_GEO_VISION:
                $parsedDataCount = count($this->_parsedSpreedSheetGeoVision($spreedSheetParser));
            break;

            case Vendor::INDEX_VIVOTEK:
                $parsedDataCount = count($this->_parsedSpreedSheetVivotek($spreedSheetParser));
            break;

            case Vendor::INDEX_AXIS:
                $parsedDataCount = count($this->_parsedSpreedSheetAxis($spreedSheetParser));
            break;

            case Vendor::INDEX_BOSCH:
                $parsedDataCount = count($this->_parsedSpreedSheetBosch($spreedSheetParser));
            break;

            case Vendor::INDEX_ISONAS:
                $parsedDataCount = count($this->_parsedSpreedSheetIsonas($spreedSheetParser));
            break;

            case Vendor::INDEX_QNAP:
                $parsedDataCount = count($this->_parsedSpreedSheetQnap($spreedSheetParser));
            break;

            case Vendor::INDEX_RAYTEC:
                $parsedDataCount = count($this->_parsedSpreedSheetRaytec($spreedSheetParser));
            break;

            case Vendor::INDEX_ENTRUST_DATACARD:
                $parsedDataCount = count($this->_parsedSpreedSheetEntrustDatacard($spreedSheetParser));
            break;

            case Vendor::INDEX_AV_COSTAR:
                $parsedDataCount = count($this->_parsedSpreedSheetAVCostar($spreedSheetParser));
            break;

            case Vendor::INDEX_LOCKNETICS:
                $parsedDataCount = count($this->_parsedSpreedSheetLocknetics($spreedSheetParser));
            break;

            case Vendor::INDEX_DIGITAL_WATCHDOG:
                $parsedDataCount = count($this->_parsedSpreedSheetDigitalWatchDog($spreedSheetParser));
            break;

            case Vendor::INDEX_SCHLAGE:
                $parsedDataCount = count($this->_parsedSpreedSheetSchlage($spreedSheetParser));
            break;

            case Vendor::INDEX_USA_VISION:
                $parsedDataCount = count($this->_parsedSpreedSheetUsaVision($spreedSheetParser));
            break;

            case Vendor::INDEX_AXTON:
                $parsedDataCount = count($this->_parsedSpreedSheetAxton($spreedSheetParser));
            break;

            case Vendor::INDEX_ABB:
                $parsedDataCount = count($this->_parsedSpreedSheetABB($spreedSheetParser));
            break;
        }
                
        return $parsedDataCount;
    }

    protected function _parsedSpreedSheetMotobix($spreedSheetParser, $options = [])
    {
        $parsedData = [];
        $rawData = [];

        $columnIndex = 0;

        // get sheets
        $worksheets = $spreedSheetParser->getWorksheets();

        // validate sheets
        if (count($worksheets) > 1) {
            throw new Exception('Spreadsheet for MOBOTIX should only contain 1 sheet.');
        }

        // select sheet data
        $spreedSheetIndex = $spreedSheetParser->getWorksheetIndex($worksheets[0]);
        
        // get sheet data
        $rows = $spreedSheetParser->createRowIterator($spreedSheetIndex);

        // validate sheet data
        if (!$rows) {
            $errorMessage = 'Spreadsheet has empty content.';
            throw new Exception($errorMessage);
            /* Dev::pv($errorMessage);
            break; */
        }

        $columnRow = [];

        // get and set sheet column
        foreach ($rows as $rowIndex =>$rowValue) {
            $rowValue1 = isset($rowValue[0]) ? ZCommon::cleanString($rowValue[0]) : '';
            $rowValue2 = isset($rowValue[1]) ? ZCommon::cleanString($rowValue[1]) : '';
            $rowValue3 = isset($rowValue[2]) ? ZCommon::cleanString($rowValue[2]) : '';
            $rowValue4 = isset($rowValue[3]) ? ZCommon::cleanString($rowValue[3]) : '';
            $rowValue5 = isset($rowValue[4]) ? ZCommon::cleanString($rowValue[4]) : '';
            $rowValue6 = isset($rowValue[5]) ? ZCommon::cleanString($rowValue[5]) : '';
            $rowValue7 = isset($rowValue[6]) ? ZCommon::cleanString($rowValue[6]) : '';
            $rowValue8 = isset($rowValue[7]) ? ZCommon::cleanString($rowValue[7]) : '';
            $rowValue9 = isset($rowValue[8]) ? ZCommon::cleanString($rowValue[8]) : '';
            $rowValue10 = isset($rowValue[9]) ? ZCommon::cleanString($rowValue[9]) : '';
            $rowValue11 = isset($rowValue[10]) ? ZCommon::cleanString($rowValue[10]) : '';
            $rowValue12 = isset($rowValue[11]) ? ZCommon::cleanString($rowValue[11]) : '';
            $rowValue13 = isset($rowValue[12]) ? ZCommon::cleanString($rowValue[12]) : '';
            $rowValue14 = isset($rowValue[13]) ? ZCommon::cleanString($rowValue[13]) : '';
            $rowValue15 = isset($rowValue[14]) ? ZCommon::cleanString($rowValue[14]) : '';
            $rowValue16 = isset($rowValue[15]) ? ZCommon::cleanString($rowValue[15]) : '';
            $rowValue17 = isset($rowValue[16]) ? ZCommon::cleanString($rowValue[16]) : '';
            $rowValue18 = isset($rowValue[17]) ? ZCommon::cleanString($rowValue[17]) : '';
            $rowValue19 = isset($rowValue[18]) ? ZCommon::cleanString($rowValue[18]) : '';
            $rowValue20 = isset($rowValue[19]) ? ZCommon::cleanString($rowValue[19]) : '';
            $rowTrim = ZCommon::cleanArray($rowValue);


            if ((in_array('mpn', $rowTrim) || in_array('MxProductCode', $rowTrim))
                && in_array('EANCode', $rowTrim)
                && (in_array('description', $rowTrim) || in_array('DescriptionLong(English)', $rowTrim))
                && (in_array('msrp', $rowTrim) || in_array('MSRPnetinUSD', $rowTrim))
                && (in_array('cost', $rowTrim) || in_array('PartnerPricing', $rowTrim))) {
                $columnRow = $rowValue;
                $columnIndex = $rowIndex;
                break;
            }
        }
        
        // validate sheet column
        if ($columnIndex <= 0) {
            $errorMessage = 'Selected vendor don\'t support spreadsheet columns. <small><ul><li>Make sure spreadsheet belong to selected vendor.</li><li>Make sure spreadsheet mpn column is set in column mapping setting.</li></ul></small>';
            throw new Exception($errorMessage);
            /* Dev::pv($errorMessage);
            break; */
            // throw new Exception('Products column is not supported sheet.');
        }

        $columnMapped = array();

        // get and set sheet mapped column
        foreach ($rows as $rowIndex => $rowValue) {
            if ($rowIndex >= $columnIndex) {
                foreach ($rowValue as $columnKey => $columnValue) {
                    $hasMatch = false;
                    
                    // retrive column mapping setting data
                    $settingColumnMapping = SettingColumnMapping::model()->find()->attributes;

                    // remove column unecessary mapping data
                    unset($settingColumnMapping['id']);
                    unset($settingColumnMapping['status']);
                    unset($settingColumnMapping['date_created']);
                    unset($settingColumnMapping['date_updated']);

                    foreach ($settingColumnMapping as $settingColumnMappingkey => $settingColumnMappingData) {
                        if (trim($settingColumnMappingData) == '') {
                            continue;
                        }
                        $settingColumnMappingData = strtolower($settingColumnMappingData);
                        $settingColumnMappingDataArr = array_map('ZCommon::cleanString', explode(',', $settingColumnMappingData));

                        if (in_array(ZCommon::cleanString(strtolower($columnValue)), $settingColumnMappingDataArr)) {
                            $columnMapped[$columnKey] = $settingColumnMappingkey;
                            $hasMatch = true;
                            break;
                        }
                    }

                    if (!$hasMatch) {
                        $columnMapped[$columnIndex] = '';
                    }
                }
                break;
            }
        }

        // validate sheet column mapped mpn
        if (!in_array('mpn', $columnMapped)) {
            $errorMessage = 'Cannot select spreadsheet mpn, make sure mpn is set in column mapping setting.';
            throw new Exception($errorMessage);
            /* Dev::pv($errorMessage);
            break; */
        }

        $productCategories = array(Vendor::getVendorNameLabel(Vendor::INDEX_MOTOBIX));

        // set sheet column mapped values
        foreach ($rows as $rowIndex => $rowValue) {
            if ($rowIndex > $columnIndex) {
                $tmpData = [];
                $rawDataTmp = [];

                $sheetRowIndexCode = ZCommon::cleanString($worksheets[0] .'-'. $rowIndex);

                foreach ($rowValue as $columnKey => $columnValue) {
                    if (isset($columnMapped[$columnKey]) && $columnMapped[$columnKey] != '') {
                        $tmpData[$columnMapped[$columnKey]] = $columnValue;
                    }

                    if (isset($columnRow[$columnKey])) {
                        $rawDataTmp[ZCommon::cleanString($columnRow[$columnKey])] = $columnValue;
                    }
                }

                $tmpData['categories'] = json_encode($productCategories);
                $tmpData['sheet_row_index'] = $rowIndex;
                $tmpData['sheet_row_index_code'] = $sheetRowIndexCode ;

                $rawData[$sheetRowIndexCode] = $rawDataTmp;

                $parsedData[] = $tmpData;
            }
        }

        // return raw data
        if (isset($options['rawData']) && $options['rawData'] === true) {
            return $rawData;
        }

        return $parsedData;
    }

    protected function _parsedSpreedSheetFlir($spreedSheetParser, $options = [])
    {
        $parsedData = [];
        $rawData = [];

        $columnIndex = 0;

        // get sheets
        $worksheets = $spreedSheetParser->getWorksheets();

        // validate sheets
        if (count($worksheets) > 1) {
            $errorMessage = 'Spreadsheet for FLIR should only contain 1 sheet.';
            throw new Exception($errorMessage);
            /* Dev::pv($errorMessage);
            break; */
        }

        // select sheet
        $spreedSheetIndex = $spreedSheetParser->getWorksheetIndex($worksheets[0]);
        $rows = $spreedSheetParser->createRowIterator($spreedSheetIndex);

        // validate sheet data
        if (!$rows) {
            $errorMessage = 'Spreadsheet has empty content.';
            throw new Exception($errorMessage);
            /* Dev::pv($errorMessage);
            break; */
        }

        $columnRow = [];

        // get and set sheet column
        foreach ($rows as $rowIndex =>$rowValue) {
            $rowValue1 = isset($rowValue[0]) ? ZCommon::cleanString($rowValue[0]) : '';
            $rowValue2 = isset($rowValue[1]) ? ZCommon::cleanString($rowValue[1]) : '';
            $rowValue3 = isset($rowValue[2]) ? ZCommon::cleanString($rowValue[2]) : '';
            $rowValue4 = isset($rowValue[3]) ? ZCommon::cleanString($rowValue[3]) : '';
            $rowValue5 = isset($rowValue[4]) ? ZCommon::cleanString($rowValue[4]) : '';
            $rowValue6 = isset($rowValue[5]) ? ZCommon::cleanString($rowValue[5]) : '';
            $rowValue7 = isset($rowValue[6]) ? ZCommon::cleanString($rowValue[6]) : '';
            $rowValue8 = isset($rowValue[7]) ? ZCommon::cleanString($rowValue[7]) : '';
            $rowValue9 = isset($rowValue[8]) ? ZCommon::cleanString($rowValue[8]) : '';
            $rowValue10 = isset($rowValue[9]) ? ZCommon::cleanString($rowValue[9]) : '';
            $rowValue11 = isset($rowValue[10]) ? ZCommon::cleanString($rowValue[10]) : '';
            $rowValue12 = isset($rowValue[11]) ? ZCommon::cleanString($rowValue[11]) : '';
            $rowValue13 = isset($rowValue[12]) ? ZCommon::cleanString($rowValue[12]) : '';
            $rowValue14 = isset($rowValue[13]) ? ZCommon::cleanString($rowValue[13]) : '';
            $rowValue15 = isset($rowValue[14]) ? ZCommon::cleanString($rowValue[14]) : '';
            $rowValue16 = isset($rowValue[15]) ? ZCommon::cleanString($rowValue[15]) : '';
            $rowValue17 = isset($rowValue[16]) ? ZCommon::cleanString($rowValue[16]) : '';
            $rowValue18 = isset($rowValue[17]) ? ZCommon::cleanString($rowValue[17]) : '';
            $rowValue19 = isset($rowValue[18]) ? ZCommon::cleanString($rowValue[18]) : '';
            $rowValue20 = isset($rowValue[19]) ? ZCommon::cleanString($rowValue[19]) : '';
            $rowTrim = ZCommon::cleanArray($rowValue);


            if (in_array('ItemCode', $rowTrim)
                /*&& in_array('PartNumber', $rowTrim)*/
                && in_array('Description', $rowTrim)
                && in_array('UnitListPrice($)', $rowTrim)
                && (in_array('GSSCost50%OFF', $rowTrim) || in_array('UnitDiscountPrice($)', $rowTrim))) {
                $columnRow = $rowValue;
                $columnIndex = $rowIndex;
                break;
            }

            /* if($rowValue1 != ''
            && $rowValue2 == ''
            && $rowValue3 == ''){
                continue;
            } */
        }

        // validate sheets columns
        if ($columnIndex <= 0) {
            $errorMessage = 'Spreadsheet has empty content.';
            throw new Exception($errorMessage);
            /* Dev::pv($errorMessage);
            break; */
        }

        $columnMapped = array();

        // get and set sheet mapped data
        foreach ($rows as $rowIndex => $rowValue) {
            if ($rowIndex >= $columnIndex) {
                foreach ($rowValue as $columnKey => $columnValue) {
                    $hasMatch = false;
                    
                    // retrive column mapping setting data
                    $settingColumnMapping = SettingColumnMapping::model()->find()->attributes;
    
                    // remove column unecessary mapping data
                    unset($settingColumnMapping['id']);
                    unset($settingColumnMapping['status']);
                    unset($settingColumnMapping['date_created']);
                    unset($settingColumnMapping['date_updated']);

                    foreach ($settingColumnMapping as $settingColumnMappingkey => $settingColumnMappingData) {
                        if (trim($settingColumnMappingData) == '') {
                            continue;
                        }
                        
                        $settingColumnMappingData = strtolower($settingColumnMappingData);
                        $settingColumnMappingDataArr = array_map('ZCommon::cleanString', explode(',', $settingColumnMappingData));
    
                        if (in_array(ZCommon::cleanString(strtolower($columnValue)), $settingColumnMappingDataArr)) {
                            $columnMapped[$columnKey] = $settingColumnMappingkey;
                            $hasMatch = true;
                            break;
                        }
                    }

                    if (!$hasMatch) {
                        $columnMapped[$columnIndex] = '';
                    }
                }
                break;
            }
        }
        
        // validate sheet column mapped mpn
        if (!in_array('mpn', $columnMapped)) {
            $errorMessage = 'Cannot select spreadsheet mpn, make sure mpn is set in column mapping setting.';
            throw new Exception($errorMessage);
            /* Dev::pv($errorMessage);
            break; */
        }

        $sheetCategories = [];
        $productCategories = [];
 
        // set sheet column mapped values
        foreach ($rows as $rowIndex => $rowValue) {
            if ($rowIndex > $columnIndex) {
                if (count($rowValue) < 4) {
                    $sheetCategories[] = Vendor::getVendorNameLabel(Vendor::INDEX_FLIR);
                    $sheetCategories[] = trim($rowValue[0]);
                    continue;
                }

                //check if value is category
                if ($rowValue[0] == $rowValue[1] && $rowValue[0] == $rowValue[2] && $rowValue[3] == $rowValue[0]) {
                    continue;
                }
                
                $tmpData = array();
                $rawDataTmp = [];

                $sheetRowIndexCode = ZCommon::cleanString($worksheets[0] .'-'. $rowIndex);
                
                foreach ($rowValue as $columnKey => $columnValue) {
                    if (isset($columnMapped[$columnKey]) && $columnMapped[$columnKey] != '') {
                        $tmpData[$columnMapped[$columnKey]] = $columnValue;
                    }

                    if (isset($columnRow[$columnKey])) {
                        $rawDataTmp[ZCommon::cleanString($columnRow[$columnKey])] = $columnValue;
                    }

                    if (count($sheetCategories) > 0) {
                        $productCategories = $sheetCategories;
                    }
                    
                    $tmpData['categories'] = json_encode($productCategories);
                    $tmpData['sheet_row_index'] = $rowIndex;
                }

                $tmpData['sheet_row_index_code'] = $sheetRowIndexCode ;

                $sheetCategories = [];

                $rawData[$sheetRowIndexCode] = $rawDataTmp;

                $parsedData[] = $tmpData;
            }
        }

        // return raw data
        if (isset($options['rawData']) && $options['rawData'] === true) {
            return $rawData;
        }
        
        return $parsedData;
    }

    protected function _parsedSpreedSheetActi($spreedSheetParser, $options = [])
    {
        $parsedData = [];
        $parsedMergeData = [];
        $rawData = [];

        // get additional sheet
        /*$spreedSheetIndex = $spreedSheetParser->getWorksheetIndex('EAN UPC Bar Code');

        // validate additional sheet
        if (!$spreedSheetIndex) {
            throw new Exception('Spreadsheet has missing sheet "EAN UPC Bar Code"');
        }

        // get additional sheet data
        $rows = $spreedSheetParser->createRowIterator($spreedSheetIndex);

        // set additional sheet data
        foreach ($rows as $rowIndex => $rowValue) {
            if (isset($rowValue[1])) {
                $parsedMergeData[$rowValue[1]] = $rowValue;
            }
        }*/

        // get main sheets
        $worksheets = $spreedSheetParser->getWorksheets();

        // set main sheets data
        foreach ($worksheets as $worksheetKey => $worksheet) {
            if ($worksheet == 'EAN UPC Bar Code') {
                continue;
            }

            if ($worksheet == 'Extended Warranty') {
                continue;
            }
        
            if ($worksheet == 'Price Release Note') {
                continue;
            }

            $columnIndex = 0;

            // get main sheet
            $spreedSheetIndex = $spreedSheetParser->getWorksheetIndex($worksheet);

            $rows = $spreedSheetParser->createRowIterator($spreedSheetIndex);

            // validate sheet data
            if (!$rows) {
                $errorMessage = 'Spreadsheet has empty content.';
                throw new Exception($errorMessage);
                /* Dev::pv($errorMessage);
                break; */
            }
    
            $columnRow = [];

            // get and set sheet column
            foreach ($rows as $rowIndex => $rowValue) {
                $rowTrim = ZCommon::cleanArray($rowValue);


                if ((in_array('OrderingNumber', $rowTrim) || in_array('PartNumber', $rowTrim))
                    && in_array('Description', $rowTrim)
                    && in_array('MSRP', $rowTrim)
                    && (in_array('PartnerPrice', $rowTrim)) || in_array('COST', $rowTrim)) {
                    $columnRow = $rowValue;
                    $columnIndex = $rowIndex;
                    break;
                }
            }

            if ($columnIndex <= 0 && $worksheetKey+1 != count($worksheets)) {
                continue;
            }

            // validate sheet column
            if ($columnIndex <= 0 && $worksheetKey+1 == count($worksheets)) {
                $errorMessage = 'Products mpn is missing in <em><strong>"'. $worksheet .'"</strong></em> sheet. <small><ul><li>Make sure mpn identifier column was defined in Settings > Column Name Mapping.</li><li>Make sure sheet contain real products.</li><li>Delete sheet if it doesn\'t contain any products.</li></ul></small>';
                throw new Exception($errorMessage);
                /* Dev::pv($errorMessage);
                break; */
            }
    
            $columnMapped = array();
    
            // get and set sheet column mapped
            foreach ($rows as $rowIndex => $rowValue) {
                /*$rowValue[] = 'EAN Barcode';
                $rowValue[] = 'UPC Barcode';*/

                if ($rowIndex >= $columnIndex) {
                    foreach ($rowValue as $columnKey => $columnValue) {
                        $hasMatch = false;
                        
                        // get mapping setting data
                        $settingColumnMapping = SettingColumnMapping::model()->find()->attributes;
    
                        // get remove setting data
                        unset($settingColumnMapping['id']);
                        unset($settingColumnMapping['status']);
                        unset($settingColumnMapping['date_created']);
                        unset($settingColumnMapping['date_updated']);
                        
                        // set sheet column mapped
                        foreach ($settingColumnMapping as $settingColumnMappingkey => $settingColumnMappingData) {
                            if (trim($settingColumnMappingData) == '') {
                                continue;
                            }
                            
                            $settingColumnMappingData = strtolower($settingColumnMappingData);
                            $settingColumnMappingDataArr = array_map('ZCommon::cleanString', explode(',', $settingColumnMappingData));
                        
                            if (in_array(ZCommon::cleanString(strtolower($columnValue)), $settingColumnMappingDataArr)) {
                                $columnMapped[$columnKey] = $settingColumnMappingkey;
                                $hasMatch = true;
                                break;
                            }
                        }
                        
                        if (!$hasMatch) {
                            $columnMapped[$columnIndex] = '';
                        }
                    }

                    break;
                }
            }
        
            // validate sheet column mapped mpn
            if (!in_array('mpn', $columnMapped)) {
                $errorMessage = 'Cannot select spreadsheet mpn, make sure mpn is set in column mapping setting. Sheet Name <strong>"'. $worksheet .'"</strong>';
                throw new Exception($errorMessage);
                /* Dev::pv($errorMessage);
                break; */
            }

            $sheetCategories = [];
            $productCategories = [];
            
            // set sheet column mapped values
            foreach ($rows as $rowIndex => $rowValue) {
                if ($rowIndex > $columnIndex) {
                    if (count($rowValue) < 4) {
                        $sheetCategories[] = Vendor::getVendorNameLabel(Vendor::INDEX_ACTI);
                        $sheetCategories[] = trim($rowValue[0]);
                        continue;
                    }
        
                    if (isset($parsedMergeData[$rowValue[1]]) && $parsedMergeData[$rowValue[1]]) {
                        $rowValue[10] = $parsedMergeData[$rowValue[1]][3];
                        $rowValue[11] = $parsedMergeData[$rowValue[1]][4];
                    } else {
                        $rowValue[10] = '';
                        $rowValue[11] = '';
                    }

                    $tmpData = array();
                    $rawDataTmp = [];
                    
                    $sheetRowIndexCode = ZCommon::cleanString($worksheetKey. '-' .$worksheet .'-'. $rowIndex);

                    foreach ($rowValue as $columnKey => $columnValue) {
                        if (isset($columnMapped[$columnKey]) && $columnMapped[$columnKey] != '') {
                            $tmpData[$columnMapped[$columnKey]] = $columnValue;
                        }

                        if (isset($columnRow[$columnKey])) {
                            $rawDataTmp[ZCommon::cleanString($columnRow[$columnKey])] = $columnValue;
                        }

                        if (count($sheetCategories) > 0) {
                            $productCategories = $sheetCategories;
                        }
    
                        $tmpData['categories'] = json_encode($productCategories);
                        $tmpData['sheet_row_index'] = $rowIndex;
                    }

                    $tmpData['sheet_row_index_code'] = $sheetRowIndexCode ;
    
                    $sheetCategories = [];

                    $rawData[$sheetRowIndexCode] = $rawDataTmp;
                    $parsedData[] = $tmpData;
                }
            }
        }

        // return raw data
        if (isset($options['rawData']) && $options['rawData'] === true) {
            return $rawData;
        }
        
        return $parsedData;
    }

    protected function _parsedSpreedSheetGeoVision($spreedSheetParser, $options = [])
    {
        $parsedData = [];
        $sheetsData = [];
        $rawData = [];

        // get shees
        $worksheets = $spreedSheetParser->getWorksheets();
        $lastWorksheet = key(array_slice($worksheets, -1, 1, true));
        $countLoop = 0;
        //set sheets data
        foreach ($worksheets as $worksheetKey => $worksheet) {
            // get sheet sheets rows data
            $myWorksheetIndex = $spreedSheetParser->getWorksheetIndex($worksheet);
            $rows = $spreedSheetParser->createRowIterator($myWorksheetIndex);
            
            // validate sheet data
            if (!$rows) {
                $errorMessage = 'Spreadsheet has empty content.';
                throw new Exception($errorMessage);
                /* Dev::pv($errorMessage);
                break; */
            }

            $sheetTable = '';
            $sheetTableRow = '';

            // get and set sheet column
            foreach ($rows as $rowIndex => $rowValue) {
                $sheetCategories = [];
                
                // collect spreedsheet rows
                $rowValue1 = isset($rowValue[0]) ? ZCommon::cleanString($rowValue[0]) : '';
                $rowValue2 = isset($rowValue[1]) ? ZCommon::cleanString($rowValue[1]) : '';
                $rowValue3 = isset($rowValue[2]) ? ZCommon::cleanString($rowValue[2]) : '';
                $rowValue4 = isset($rowValue[3]) ? ZCommon::cleanString($rowValue[3]) : '';
                $rowValue5 = isset($rowValue[4]) ? ZCommon::cleanString($rowValue[4]) : '';
                $rowValue6 = isset($rowValue[5]) ? ZCommon::cleanString($rowValue[5]) : '';
                $rowValue7 = isset($rowValue[6]) ? ZCommon::cleanString($rowValue[6]) : '';
                $rowValue8 = isset($rowValue[7]) ? ZCommon::cleanString($rowValue[7]) : '';
                $rowValue9 = isset($rowValue[8]) ? ZCommon::cleanString($rowValue[8]) : '';
                $rowValue10 = isset($rowValue[9]) ? ZCommon::cleanString($rowValue[9]) : '';
                $rowValue11 = isset($rowValue[10]) ? ZCommon::cleanString($rowValue[10]) : '';
                $rowValue12 = isset($rowValue[11]) ? ZCommon::cleanString($rowValue[11]) : '';
                $rowValue13 = isset($rowValue[12]) ? ZCommon::cleanString($rowValue[12]) : '';
                $rowValue14 = isset($rowValue[13]) ? ZCommon::cleanString($rowValue[13]) : '';
                $rowValue15 = isset($rowValue[14]) ? ZCommon::cleanString($rowValue[14]) : '';
                $rowValue16 = isset($rowValue[15]) ? ZCommon::cleanString($rowValue[15]) : '';
                $rowTrim = ZCommon::cleanArray($rowValue);

                // skip unimportant spreedsheet rows
                /*if (trim($rowValue1) == ''
                && trim($rowValue2) == ''
                && trim($rowValue3) == ''
                && trim($rowValue4) == ''
                && trim($rowValue5) != '') {
                    continue;
                } elseif (trim($rowValue1) == ''
                && trim($rowValue2) != ''
                && trim($rowValue3) == ''
                && trim($rowValue4) == ''
                && trim($rowValue5) == '') {
                    continue;
                }*/

                if ($rowValue1 != ''
                && $rowValue2 == ''
                && $rowValue3 == ''
                && $rowValue4 == ''
                && $rowValue5 == '') {
                    continue;
                }

                if ($rowValue1 == ''
                && $rowValue2 != ''
                && $rowValue3 == ''
                && $rowValue4 == ''
                && $rowValue5 == '') {
                    continue;
                }

                if ($rowValue1 != ''
                && $rowValue2 == ''
                && $rowValue3 != ''
                && $rowValue4 == ''
                && $rowValue5 == '') {
                    continue;
                }

                if ($rowValue1 == ''
                && $rowValue2 == ''
                && $rowValue3 == ''
                && $rowValue4 == ''
                && $rowValue5 == '') {
                    continue;
                }

                // Camera 1 sheets
                if (in_array('ModelNo.', $rowTrim)
                /*&& in_array('Lens', $rowTrim)*/
                && in_array('MSRP', $rowTrim)
                && in_array('Reseller', $rowTrim)
                && in_array('Description', $rowTrim)
                /*&& in_array('VIP-10%', $rowTrim)*/) {
                    $sheetTable = 'camera1';
                    $sheetTableRow = $rowValue;
                    continue;
                }

                // Clearance 1 sheets
                if (in_array('ModelNo.', $rowTrim)
                && in_array('Description', $rowTrim)
                && in_array('Clearance', $rowTrim)) {
                    $sheetTable = 'clearance1';
                    $sheetTableRow = $rowValue;
                    continue;
                }

                // Mount 1
                if (in_array('ModelNo.', $rowTrim)
                && in_array('CameraModel', $rowTrim)
                && in_array('MSRP', $rowTrim)
                && in_array('Reseller', $rowTrim)
                && in_array('Description', $rowTrim)
                && in_array('VIP-10%', $rowTrim)) {
                    $sheetTable = 'mount1';
                    $sheetTableRow = $rowValue;
                    continue;
                }

                // Mount 2
                if (in_array('ModelNo.', $rowTrim)
                && in_array('CommittedImporter', $rowTrim)
                && in_array('MSRP', $rowTrim)
                && in_array('Reseller', $rowTrim)
                && in_array('Description', $rowTrim)
                && in_array('VIP-10%', $rowTrim)) {
                    $sheetTable = 'mount2';
                    $sheetTableRow = $rowValue;
                    continue;
                }

                /*
                Multiple
                PoE Switch 1
                Standaline, Video Encoder & Dec
                Access Control & LPR
                Capture Card
                */
                if (in_array('ModelNo.', $rowTrim)
                && in_array('CommittedImporter', $rowTrim)
                && in_array('MSRP', $rowTrim)
                && in_array('Reseller', $rowTrim)
                && in_array('Description', $rowTrim)
                && in_array('VIP-10%', $rowTrim)) {
                    $sheetTable = 'poeswitch1';
                    $sheetTableRow = $rowValue;
                    continue;
                }

                // Capture Card 2
                if (in_array('OrderingPartNo.', $rowTrim)
                && in_array('SKUNo.', $rowTrim)
                && in_array('Price', $rowTrim)
                && in_array('Description', $rowTrim)) {
                    $sheetTable = 'capturecard2';
                    $sheetTableRow = $rowValue;
                    continue;
                }

                // UVS VMS Server
                if (in_array('SalesPartNo.', $rowTrim)
                && in_array('Retail', $rowTrim)
                && in_array('SI', $rowTrim)
                && in_array('VIP', $rowTrim)) {
                    $sheetTable = 'uvsvmsserver';
                    $sheetTableRow = $rowValue;
                    continue;
                }
                
                // UVS VMS Server 5
                if (in_array('Salespart#', $rowTrim)
                && in_array('Price', $rowTrim)) {
                    $sheetTable = 'uvsvmsserver2/optional-accessories-1';
                    $sheetTableRow = $rowValue;
                    continue;
                }

                // Recording, CMS & SAN Server 1
                if (in_array('SalesPartNo.', $rowTrim)
                && in_array('CPU/MEM', $rowTrim)
                && in_array('Retail', $rowTrim)
                && in_array('SI', $rowTrim)
                && in_array('Wholesale', $rowTrim)
                && in_array('VIP', $rowTrim)) {
                    $sheetTable = 'recording-cms-&-san-server1';
                    $sheetTableRow = $rowValue;
                    continue;
                }

                // Recording, CMS & SAN Server 2
                if (in_array('Model', $rowTrim)
                && in_array('ItemNumber', $rowTrim)
                && in_array('UPC', $rowTrim)
                && in_array('Description', $rowTrim)
                && in_array('Retail', $rowTrim)
                && in_array('SI', $rowTrim)
                && in_array('Wholesale', $rowTrim)
                && in_array('VIP', $rowTrim)) {
                    $sheetTable = 'recording-cms-&-san-server2';
                    $sheetTableRow = $rowValue;
                    continue;
                }

                // Optional Accessories 2
                if ((in_array('SalesPartNo.', $rowTrim) || in_array('Salespart#', $rowTrim))
                && (in_array('VIP', $rowTrim) || in_array('VIP Price', $rowTrim))) {
                    $sheetTable = 'optional-accessories-2';
                    $sheetTableRow = $rowValue;
                    continue;
                }
                

                if ($sheetTable == 'capturecard2') {
                    continue;
                }

                if ($sheetTable == '' && $worksheetKey != $lastWorksheet) {
                    continue;
                }
                // validate mapped colulmn
                if ($sheetTable == '' && $worksheetKey == $lastWorksheet) {
                    if($countLoop == 0){
                        continue;
                    }
                    $errorMessage = '<small>Selected vendor don\'t support spreadsheet columns. Sheet Name <strong>"'. $worksheet .'"</strong>.</small><small><ul><li>Make sure spreadsheet belong to selected vendor.</li><li>Make sure spreadsheet mpn column is set in column mapping setting.</li></ul></small>';
                    // $errorMessage = 'Products column is not supported in <em><strong>"'. $worksheet .'"</strong></em> sheet.';
                    throw new Exception($errorMessage);
                    /* Dev::pv($errorMessage);
                    break; */
                }

                $sheetRowIndexCode = ZCommon::cleanString($worksheetKey .'-'. $worksheet) .'-'. $rowIndex;

                if (isset($options['rawData']) && $options['rawData'] === true) {
                    $rawDataTmp = [];

                    foreach ($sheetTableRow as $sheetTableRowKey => $sheetTableRowVal) {
                        if (isset($rowValue[$sheetTableRowKey])) {
                            $rawDataTmp[ZCommon::cleanString($sheetTableRowVal)] = $rowValue[$sheetTableRowKey];
                        }
                    }

                    $rawData[$sheetRowIndexCode] = $rawDataTmp;

                    continue;
                }

                // set categories
                $sheetCategories[] = Vendor::getVendorNameLabel(Vendor::INDEX_GEO_VISION);
                $sheetCategories[] = $worksheet;
                
                // set sheets columns and sheets data
                $sheetsData[$worksheet][$sheetTable][] = [
                    'sheet_col' => $sheetTableRow,
                    'sheet_data' => $rowValue,
                    'sheet_row_index' => $rowIndex,
                    'sheet_row_index_code' => $sheetRowIndexCode,
                    'sheet_row_categories' => json_encode($sheetCategories)
                ];
            }
        }
        
        // return raw data
        if (isset($options['rawData']) && $options['rawData'] === true) {
            return $rawData;
        }

        // retrive column mapping setting data
        $settingColumnMapping = SettingColumnMapping::model()->find()->attributes;

        // remove column unecessary mapping data
        unset($settingColumnMapping['id']);
        unset($settingColumnMapping['status']);
        unset($settingColumnMapping['date_created']);
        unset($settingColumnMapping['date_updated']);

        $settingColumnMappingValues = [];

        // prepare mapping column setting, retrieve
        foreach ($settingColumnMapping as $settingColumnMappingkey => $settingColumnMappingData) {
            $settingColumnMappingData = strtolower($settingColumnMappingData);
            $columnMapData =  array_map('ZCommon::cleanString', explode(',', $settingColumnMappingData));
            $settingColumnMappingValues[$settingColumnMappingkey] = $columnMapData;
        }
        
        $sheetParsedData = [];

        // set columns mapping and values
        foreach ($sheetsData as $sheetsDataKey => $sheetsDataVal) {
            foreach ($sheetsDataVal as $sheetsDataTableKey => $sheetsDataTableVal) {
                foreach ($sheetsDataTableVal as $sheetsDataTableItemKey => $sheetsDataTableItemVal) {
                    $sheetData = $sheetsDataTableItemVal['sheet_data'];
                    $sheetCol = $sheetsDataTableItemVal['sheet_col'];
                    $sheetRowIndex = $sheetsDataTableItemVal['sheet_row_index'];
                    $sheetRowIndexCode = $sheetsDataTableItemVal['sheet_row_index_code'];
                    $sheetCategories = $sheetsDataTableItemVal['sheet_row_categories'];
                    $sheetColumnWithData = [];

                    foreach ($sheetCol as $sheetColumnKey => $sheetColumnVal) {
                        $sheetColumnVal = ZCommon::cleanString($sheetColumnVal);

                        $columnHasMappingData = false;

                        foreach ($settingColumnMappingValues as $settingColumnMappingKey => $settingColumnMappingVal) {
                            if (in_array(strtolower($sheetColumnVal), $settingColumnMappingVal)) {
                                $sheetColumnVal = $settingColumnMappingKey;
                                $columnHasMappingData = true;
                                continue;
                            }
                        }
                        $sheetColumnValRaw ='';
                        if (!$columnHasMappingData) {
                            $sheetColumnValRaw = $sheetColumnVal;
                            $sheetColumnVal = '__' . $sheetColumnValRaw;
                        }

                        $sheetColumnWithData[$sheetColumnVal] = isset($sheetData[$sheetColumnKey]) ? $sheetData[$sheetColumnKey]: 'not set';
                    }
                    
                    $sheetParsedData[] = $sheetColumnWithData + [
                        'sheets_name' => $sheetsDataKey,
                        'sheets_row_index' => $sheetRowIndex,
                        'sheet_row_index_code' => $sheetRowIndexCode,
                        'sheets_col' => $sheetColumnValRaw,
                        'categories' => $sheetCategories];
                }
            }
        }

        // format sheet data
        foreach ($sheetParsedData as $sheetParsedDataKey => $sheetParsedDataVal) {
            $tmpData = [];
            
            foreach ($sheetParsedDataVal as $sheetRowDatakey => $sheetRowDataVal) {
                $tmpData[$sheetRowDatakey] = $sheetRowDataVal;
            }
            $parsedData[] = $tmpData;
        }

        // validate data mpn
        foreach ($parsedData as $parsedDataKey => $parsedDataValue) {
            $errorSheetName = '';
            $errorSheetRowIndex = '';

            if (!in_array('mpn', array_keys($parsedDataValue))) {
                $errorSheetName = $parsedDataValue['sheets_name'];

                $errorMessage = 'Cannot select spreadsheet mpn, make sure mpn is set in column mapping setting. Sheet Name <strong>"'. $errorSheetName .'"</strong>';
                
                throw new Exception($errorMessage);
                /* Dev::pv($errorMessage);
                break; */
            }
        }

        return $parsedData;
    }

    protected function _parsedSpreedSheetVivotek($spreedSheetParser, $options = [])
    {
        $parsedData = [];
        $sheetsData = [];
        $rawData = [];

        // get shees
        $worksheets = $spreedSheetParser->getWorksheets();
        $countLoop = 0;
        
        //set sheets data
        foreach ($worksheets as $worksheetKey => $worksheet) {
            
        
            // get sheet sheets rows data
            $myWorksheetIndex = $spreedSheetParser->getWorksheetIndex($worksheet);
            $rows = $spreedSheetParser->createRowIterator($myWorksheetIndex);
            // validate sheet data
            if (!$rows) {
                $errorMessage = 'Spreadsheet has empty content.';
                throw new Exception($errorMessage);
                /* Dev::pv($errorMessage);
                break; */
            }

            $sheetTable = '';
            $sheetTableRow = '';

            // get and set sheet column
            foreach ($rows as $rowIndex => $rowValue) {
                $sheetCategories = [];
                
                // collect spreedsheet rows
                $rowValue1 = isset($rowValue[0]) ? ZCommon::cleanString($rowValue[0]) : '';
                $rowValue2 = isset($rowValue[1]) ? ZCommon::cleanString($rowValue[1]) : '';
                $rowValue3 = isset($rowValue[2]) ? ZCommon::cleanString($rowValue[2]) : '';
                $rowValue4 = isset($rowValue[3]) ? ZCommon::cleanString($rowValue[3]) : '';
                $rowValue5 = isset($rowValue[4]) ? ZCommon::cleanString($rowValue[4]) : '';
                $rowValue6 = isset($rowValue[5]) ? ZCommon::cleanString($rowValue[5]) : '';
                $rowValue7 = isset($rowValue[6]) ? ZCommon::cleanString($rowValue[6]) : '';
                $rowValue8 = isset($rowValue[7]) ? ZCommon::cleanString($rowValue[7]) : '';
                $rowValue9 = isset($rowValue[8]) ? ZCommon::cleanString($rowValue[8]) : '';
                $rowValue10 = isset($rowValue[9]) ? ZCommon::cleanString($rowValue[9]) : '';
                $rowValue11 = isset($rowValue[10]) ? ZCommon::cleanString($rowValue[10]) : '';
                $rowValue12 = isset($rowValue[11]) ? ZCommon::cleanString($rowValue[11]) : '';
                $rowValue13 = isset($rowValue[12]) ? ZCommon::cleanString($rowValue[12]) : '';
                $rowValue14 = isset($rowValue[13]) ? ZCommon::cleanString($rowValue[13]) : '';
                $rowValue15 = isset($rowValue[14]) ? ZCommon::cleanString($rowValue[14]) : '';
                $rowValue16 = isset($rowValue[15]) ? ZCommon::cleanString($rowValue[15]) : '';
                $rowValue17 = isset($rowValue[16]) ? ZCommon::cleanString($rowValue[16]) : '';
                $rowValue18 = isset($rowValue[17]) ? ZCommon::cleanString($rowValue[17]) : '';
                $rowValue19 = isset($rowValue[18]) ? ZCommon::cleanString($rowValue[18]) : '';
                $rowValue20 = isset($rowValue[19]) ? ZCommon::cleanString($rowValue[19]) : '';
                $rowValue21 = isset($rowValue[20]) ? ZCommon::cleanString($rowValue[20]) : '';
                $rowValue22 = isset($rowValue[21]) ? ZCommon::cleanString($rowValue[21]) : '';
                $rowValue23 = isset($rowValue[22]) ? ZCommon::cleanString($rowValue[22]) : '';
                $rowValue24 = isset($rowValue[23]) ? ZCommon::cleanString($rowValue[23]) : '';
                $rowValue25 = isset($rowValue[24]) ? ZCommon::cleanString($rowValue[24]) : '';
                $rowValue26 = isset($rowValue[25]) ? ZCommon::cleanString($rowValue[25]) : '';
                $rowValue27 = isset($rowValue[26]) ? ZCommon::cleanString($rowValue[26]) : '';
                $rowValue28 = isset($rowValue[27]) ? ZCommon::cleanString($rowValue[27]) : '';
                $rowValue29 = isset($rowValue[28]) ? ZCommon::cleanString($rowValue[28]) : '';
                $rowValue30 = isset($rowValue[29]) ? ZCommon::cleanString($rowValue[29]) : '';
                $rowTrim = ZCommon::cleanArray($rowValue);
                $rowstrtolower = array_map('strtolower',$rowTrim);

                //skip unimportant row
                if ($rowValue1 == ''
                && $rowValue2 == ''
                && $rowValue3 == ''
                && $rowValue4 == ''
                && $rowValue5 == '') {
                    continue;
                }

                /* Commented to support single sheet
                    if ($rowValue1 != ''
                    && $rowValue2 != ''
                    && $rowValue3 != ''
                    && $rowValue4 != ''
                    && $rowValue5 == '') {
                        continue;
                    }
                */

                if ($rowValue1 == ''
                && $rowValue2 == ''
                && $rowValue3 == ''
                && $rowValue4 == ''
                && $rowValue5 != '') {
                    continue;
                }
                if ($rowValue1 != ''
                && $rowValue2 == ''
                && $rowValue3 == ''
                && $rowValue4 == ''
                && $rowValue5 == '') {
                    continue;
                }
                if ($rowValue1 != ''
                && $rowValue2 != ''
                && $rowValue3 == ''
                && $rowValue4 == ''
                && $rowValue5 == '') {
                    continue;
                }
                if ($rowValue1 == 'Software'
                && $rowValue2 != ''
                && $rowValue3 != ''
                && $rowValue4 == ''
                && $rowValue5 == '') {
                    continue;
                }
                /*if ($rowValue1 == ''
                && $rowValue2 != ''
                && $rowValue3 != ''
                && $rowValue4 == ''
                && $rowValue5 == '') {
                    continue;
                }*/

                // Product sheets
                if (in_array('model#', $rowstrtolower)
                    && in_array('ean', $rowstrtolower)
                    && in_array('distributorprice', $rowstrtolower)
                    && in_array('msrp', $rowstrtolower)) {
                    $sheetTable = 'product';
                    $sheetTableRow = $rowValue;
                    $sheetTableRow[] = 'description';
                    continue;
                }

                // Acessory Price sheets
                if (in_array('modelname', $rowstrtolower)
                    && in_array('unitprice', $rowstrtolower)
                    && in_array('msrp', $rowstrtolower)
                    && in_array('description', $rowstrtolower)) {
                    $sheetTable = 'accessory';
                    $sheetTableRow = $rowValue;
                    continue;
                }

                // Cabinet  sheets
                if (in_array('model', $rowstrtolower)
                    && in_array('description', $rowstrtolower)
                    && in_array('distributorprice(usd)', $rowstrtolower)
                    && in_array('msrp(usd)', $rowstrtolower)) {
                    $sheetTable = 'cabinet';
                    $sheetTableRow = $rowValue;
                    continue;
                }

                // Cabinet  sheets
                if (in_array('modelname', $rowstrtolower)
                    && in_array('unitprice', $rowstrtolower)
                    && in_array('msrp', $rowstrtolower)
                    && in_array('port/description', $rowstrtolower)) {
                    $sheetTable = 'poeaccessory';
                    $sheetTableRow = $rowValue;
                    continue;
                }

                // product
                if (in_array('model', $rowstrtolower)
                    && in_array('ean', $rowstrtolower)
                    && in_array('description', $rowstrtolower)
                    && in_array('msrp', $rowstrtolower)
                    && in_array('distributionprice', $rowstrtolower)) {
                    $sheetTable = 'prod';
                    $sheetTableRow = $rowValue;
                    continue;
                }

                // Support single sheet format
                    if (in_array('modelname', $rowstrtolower)
                    && in_array('msrp', $rowstrtolower)
                    && in_array('cost', $rowstrtolower)
                    && in_array('description', $rowstrtolower)) {
                    $sheetTable = 'sheet1';
                    $sheetTableRow = $rowValue;
                    continue;
                }

                // Support single sheet format
                    if (in_array('modelname', $rowstrtolower)
                    && in_array('msrp', $rowstrtolower)
                    && in_array('cost', $rowstrtolower)
                    && in_array('description-unlimited', $rowstrtolower)) {
                    $sheetTable = 'sheet1';
                    $sheetTableRow = $rowValue;
                    continue;
                }

                // Support single sheet format
                if (in_array('mpn', $rowstrtolower)
                && in_array('msrp', $rowstrtolower)
                && in_array('cost', $rowstrtolower)
                && in_array('description', $rowstrtolower)) {
                    $sheetTable = 'sheet1';
                    $sheetTableRow = $rowValue;
                    continue;
                }

                //skip worksheet
                if ($sheetTable == '' && $worksheetKey+1 != count($worksheets)) {
                    continue;
                }
                
                // validate mapped colulmn
                if ($sheetTable == '' && $worksheetKey+1 == count($worksheets)) {
                    $errorMessage = '<small>Selected vendor doesn\'t support spreadsheet columns. Sheet Name <strong>"'. $worksheet .'"</strong>.</small><small><ul><li>Make sure spreadsheet belong to selected vendor.</li><li>Make sure spreadsheet mpn column is set in column mapping setting.</li></ul></small>';
                    // $errorMessage = 'Products column is not supported in <em><strong>"'. $worksheet .'"</strong></em> sheet.';
                    throw new Exception($errorMessage);
                    /* Dev::pv($errorMessage);
                    break; */
                }

                $sheetRowIndexCode = ZCommon::cleanString($worksheetKey .'-'. $worksheet) .'-'. $rowIndex;

                if (isset($options['rawData']) && $options['rawData'] === true) {
                    $rawDataTmp = [];

                    foreach ($sheetTableRow as $sheetTableRowKey => $sheetTableRowVal) {
                        if (isset($rowValue[$sheetTableRowKey])) {
                            $rawDataTmp[ZCommon::cleanString($sheetTableRowVal)] = $rowValue[$sheetTableRowKey];
                        }
                    }

                    $rawData[$sheetRowIndexCode] = $rawDataTmp;

                    continue;
                }

                //setting up description for product price worksheet
                if ($sheetTable == 'product') {
                    foreach ($sheetTableRow as $key => $column) {
                        if (!isset($rowValue[$key])) {
                            $rowValue[$key] = '';
                        }

                        if ($column == 'description') {
                            $rowValue[$key] = ($rowValue[8] != '' ? $rowValue[8].', ' : '').($rowValue[12] != '' ? $rowValue[12].', ' : '').($rowValue[13] != '' ? $rowValue[13].', ' : '').($rowValue[16] != '' ? $rowValue[16] : '').($rowValue[17] != '' ? $rowValue[17].', ' : '');
                        }
                    }
                }

                // set categories
                $sheetCategories[] = Vendor::getVendorNameLabel(Vendor::INDEX_VIVOTEK);
                $sheetCategories[] = $worksheet;
                
                // set sheets columns and sheets data
                $sheetsData[$worksheet][$sheetTable][] = [
                    'sheet_col' => $sheetTableRow,
                    'sheet_data' => $rowValue,
                    'sheet_row_index' => $rowIndex,
                    'sheet_row_index_code' => $sheetRowIndexCode,
                    'sheet_row_categories' => json_encode($sheetCategories)
                ];
            }
        }

        // return raw data
        if (isset($options['rawData']) && $options['rawData'] === true) {
            return $rawData;
        }

        // retrive column mapping setting data
        $settingColumnMapping = SettingColumnMapping::model()->find()->attributes;

        // remove column unecessary mapping data
        unset($settingColumnMapping['id']);
        unset($settingColumnMapping['status']);
        unset($settingColumnMapping['date_created']);
        unset($settingColumnMapping['date_updated']);

        $settingColumnMappingValues = [];

        // prepare mapping column setting, retrieve
        foreach ($settingColumnMapping as $settingColumnMappingkey => $settingColumnMappingData) {
            $settingColumnMappingData = strtolower($settingColumnMappingData);
            $columnMapData =  array_map('ZCommon::cleanString', explode(',', $settingColumnMappingData));
            $settingColumnMappingValues[$settingColumnMappingkey] = $columnMapData;
        }
        
        $sheetParsedData = [];

        // set columns mapping and values
        foreach ($sheetsData as $sheetsDataKey => $sheetsDataVal) {
            foreach ($sheetsDataVal as $sheetsDataTableKey => $sheetsDataTableVal) {
                foreach ($sheetsDataTableVal as $sheetsDataTableItemKey => $sheetsDataTableItemVal) {
                    $sheetData = $sheetsDataTableItemVal['sheet_data'];
                    $sheetCol = $sheetsDataTableItemVal['sheet_col'];
                    $sheetRowIndex = $sheetsDataTableItemVal['sheet_row_index'];
                    $sheetRowIndexCode = $sheetsDataTableItemVal['sheet_row_index_code'];
                    $sheetCategories = $sheetsDataTableItemVal['sheet_row_categories'];

                    $sheetColumnWithData = [];

                    foreach ($sheetCol as $sheetColumnKey => $sheetColumnVal) {
                        $sheetColumnVal = ZCommon::cleanString($sheetColumnVal);

                        $columnHasMappingData = false;

                        foreach ($settingColumnMappingValues as $settingColumnMappingKey => $settingColumnMappingVal) {
                            if (in_array(strtolower($sheetColumnVal), $settingColumnMappingVal)) {
                                $sheetColumnVal = $settingColumnMappingKey;
                                $columnHasMappingData = true;
                                continue;
                            }
                        }
                        $sheetColumnValRaw = '';
                        if (!$columnHasMappingData) {
                            $sheetColumnValRaw = $sheetColumnVal;
                            $sheetColumnVal = '__' . $sheetColumnValRaw;
                        }

                        $sheetColumnWithData[$sheetColumnVal] = isset($sheetData[$sheetColumnKey]) ? preg_replace("/[\n\r]/", " ", $sheetData[$sheetColumnKey]) : 'not set';
                    }
                    
                    $sheetParsedData[] = $sheetColumnWithData + [
                        'sheets_name' => $sheetsDataKey,
                        'sheets_row_index' => $sheetRowIndex,
                        'sheet_row_index_code' => $sheetRowIndexCode,
                        'sheets_col' => $sheetColumnValRaw,
                        'categories' => $sheetCategories];
                }
            }
        }

        // format sheet data
        foreach ($sheetParsedData as $sheetParsedDataKey => $sheetParsedDataVal) {
            $tmpData = [];
            
            foreach ($sheetParsedDataVal as $sheetRowDatakey => $sheetRowDataVal) {
                $tmpData[$sheetRowDatakey] = $sheetRowDataVal;
            }
            $parsedData[] = $tmpData;
        }

        // validate data mpn
        foreach ($parsedData as $parsedDataKey => $parsedDataValue) {
            $errorSheetName = '';
            $errorSheetRowIndex = '';

            if (!in_array('mpn', array_keys($parsedDataValue))) {
                $errorSheetName = $parsedDataValue['sheets_name'];

                $errorMessage = 'Cannot select spreadsheet mpn, make sure mpn is set in column mapping setting. Sheet Name <strong>"'. $errorSheetName .'"</strong>';

                throw new Exception($errorMessage);
                /* Dev::pv($errorMessage);
                break; */
            }
        }

        return $parsedData;
    }

    protected function _parsedSpreedSheetAxis($spreedSheetParser, $options = [])
    {
        $parsedData = [];
        $rawData = [];

        $columnIndex = 0;

        // get sheets
        $worksheets = $spreedSheetParser->getWorksheets();

        // validate sheets
        if (count($worksheets) > 1) {
            throw new Exception('Spreadsheet for Axis should only contain 1 sheet.');
        }

        // select sheet
        $spreedSheetIndex = $spreedSheetParser->getWorksheetIndex($worksheets[0]);
        $rows = $spreedSheetParser->createRowIterator($spreedSheetIndex);

        // validate sheet data
        if (!$rows) {
            $errorMessage = 'Spreadsheet has empty content.';
            throw new Exception($errorMessage);
            /* Dev::pv($errorMessage);
            break; */
        }

        $columnRow = [];

        // get and set sheet column
        foreach ($rows as $rowIndex =>$rowValue) {
            $rowValue1 = isset($rowValue[0]) ? ZCommon::cleanString($rowValue[0]) : '';
            $rowValue2 = isset($rowValue[1]) ? ZCommon::cleanString($rowValue[1]) : '';
            $rowValue3 = isset($rowValue[2]) ? ZCommon::cleanString($rowValue[2]) : '';
            $rowValue4 = isset($rowValue[3]) ? ZCommon::cleanString($rowValue[3]) : '';
            $rowValue5 = isset($rowValue[4]) ? ZCommon::cleanString($rowValue[4]) : '';
            $rowValue6 = isset($rowValue[5]) ? ZCommon::cleanString($rowValue[5]) : '';
            $rowTrim = ZCommon::cleanArray($rowValue);

            /* if($rowValue1 != ''
            && $rowValue2 == ''
            && $rowValue3 == ''){
                continue;
            } */
            
            //skip unimportant row
            if ($rowValue1 == ''
            && $rowValue2 == ''
            && $rowValue3 == ''
            && $rowValue4 == ''
            && $rowValue5 == '') {
                continue;
            }
            if ($rowValue1 != ''
            && $rowValue2 == ''
            && $rowValue3 == ''
            && $rowValue4 == ''
            && $rowValue5 == '') {
                continue;
            }
            if ($rowValue1 != ''
            && $rowValue2 == ''
            && $rowValue3 == ''
            && $rowValue4 == ''
            && $rowValue5 != '') {
                continue;
            }

            if (in_array('AxisModel', $rowTrim)
                && in_array('Description', $rowTrim)
                && in_array('MSRP', $rowTrim)
                && in_array('GSSPrice', $rowTrim)) {
                $columnRow = $rowValue;
                $columnIndex = $rowIndex;
                break;
            }
        }

        // validate sheets columns
        if ($columnIndex <= 0) {
            throw new Exception('Spreadsheet has empty content.');
        }

        $columnMapped = array();

        // get and set sheet mapped data
        foreach ($rows as $rowIndex => $rowValue) {
            if ($rowIndex >= $columnIndex) {
                foreach ($rowValue as $columnKey => $columnValue) {
                    $hasMatch = false;
                    
                    // retrive column mapping setting data
                    $settingColumnMapping = SettingColumnMapping::model()->find()->attributes;
    
                    // remove column unecessary mapping data
                    unset($settingColumnMapping['id']);
                    unset($settingColumnMapping['status']);
                    unset($settingColumnMapping['date_created']);
                    unset($settingColumnMapping['date_updated']);

                    foreach ($settingColumnMapping as $settingColumnMappingkey => $settingColumnMappingData) {
                        if (trim($settingColumnMappingData) == '') {
                            continue;
                        }
                        
                        $settingColumnMappingData = strtolower($settingColumnMappingData);
                        $settingColumnMappingDataArr = array_map('ZCommon::cleanString', explode(',', $settingColumnMappingData));
    
                        if (in_array(ZCommon::cleanString(strtolower($columnValue)), $settingColumnMappingDataArr)) {
                            $columnMapped[$columnKey] = $settingColumnMappingkey;
                            $hasMatch = true;
                            break;
                        }
                    }

                    if (!$hasMatch) {
                        $columnMapped[$columnIndex] = '';
                    }
                }
                break;
            }
        }
        
        // validate sheet column mapped mpn
        if (!in_array('mpn', $columnMapped)) {
            $errorMessage = 'Cannot select spreadsheet mpn, make sure mpn is set in column mapping setting.';
            throw new Exception($errorMessage);
            /* Dev::pv($errorMessage);
            break; */
        }

        $sheetCategories = [];
        $productCategories = [];
 
        // set sheet column mapped values
        foreach ($rows as $rowIndex => $rowValue) {
            if ($rowIndex > $columnIndex) {
                if (count($rowValue) < 4) {
                    $sheetCategories[] = Vendor::getVendorNameLabel(Vendor::INDEX_FLIR);
                    $sheetCategories[] = trim($rowValue[0]);
                    continue;
                }

                //check if value is category
                if ($rowValue[0] == $rowValue[1] && $rowValue[0] == $rowValue[2] && $rowValue[3] == $rowValue[0]) {
                    continue;
                }
                
                $tmpData = array();
                $rawDataTmp = [];

                $sheetRowIndexCode = ZCommon::cleanString($worksheets[0] .'-'. $rowIndex);
                
                foreach ($rowValue as $columnKey => $columnValue) {
                    if (isset($columnMapped[$columnKey]) && $columnMapped[$columnKey] != '') {
                        $tmpData[$columnMapped[$columnKey]] = $columnValue;
                    }

                    if (isset($columnRow[$columnKey])) {
                        $rawDataTmp[ZCommon::cleanString($columnRow[$columnKey])] = $columnValue;
                    }

                    if (count($sheetCategories) > 0) {
                        $productCategories = $sheetCategories;
                    }
                    
                    $tmpData['categories'] = json_encode($productCategories);
                    $tmpData['sheet_row_index'] = $rowIndex;
                }

                $tmpData['sheet_row_index_code'] = $sheetRowIndexCode ;

                $sheetCategories = [];

                $rawData[$sheetRowIndexCode] = $rawDataTmp;

                $parsedData[] = $tmpData;
            }
        }

        // return raw data
        if (isset($options['rawData']) && $options['rawData'] === true) {
            return $rawData;
        }
        
        return $parsedData;
    }

    protected function _parsedSpreedSheetBosch($spreedSheetParser, $options = [])
    {
        $parsedData = [];
        $sheetsData = [];
        $rawData = [];

        // get shees
        $worksheets = $spreedSheetParser->getWorksheets();

        //set sheets data
        foreach ($worksheets as $worksheetKey => $worksheet) {
            
            //worksheet target

            // get sheet sheets rows data
            $myWorksheetIndex = $spreedSheetParser->getWorksheetIndex($worksheet);
            $rows = $spreedSheetParser->createRowIterator($myWorksheetIndex);
            // validate sheet data
            if (!$rows) {
                $errorMessage = 'Spreadsheet has empty content.';
                throw new Exception($errorMessage);
                /* Dev::pv($errorMessage);
                break; */
            }
            $sheetTable = '';
            $sheetTableRow = '';

            // get and set sheet column
            foreach ($rows as $rowIndex => $rowValue) {
                $sheetCategories = [];
                
                // collect spreedsheet rows
                $rowValue1 = isset($rowValue[0]) ? ZCommon::cleanString($rowValue[0]) : '';
                $rowValue2 = isset($rowValue[1]) ? ZCommon::cleanString($rowValue[1]) : '';
                $rowValue3 = isset($rowValue[2]) ? ZCommon::cleanString($rowValue[2]) : '';
                $rowValue4 = isset($rowValue[3]) ? ZCommon::cleanString($rowValue[3]) : '';
                $rowValue5 = isset($rowValue[4]) ? ZCommon::cleanString($rowValue[4]) : '';
                $rowValue6 = isset($rowValue[5]) ? ZCommon::cleanString($rowValue[5]) : '';
                $rowValue7 = isset($rowValue[6]) ? ZCommon::cleanString($rowValue[6]) : '';
                $rowValue8 = isset($rowValue[7]) ? ZCommon::cleanString($rowValue[7]) : '';
                $rowValue9 = isset($rowValue[8]) ? ZCommon::cleanString($rowValue[8]) : '';
                $rowValue10 = isset($rowValue[9]) ? ZCommon::cleanString($rowValue[9]) : '';
                $rowValue11 = isset($rowValue[10]) ? ZCommon::cleanString($rowValue[10]) : '';
                $rowValue12 = isset($rowValue[11]) ? ZCommon::cleanString($rowValue[11]) : '';
                $rowValue13 = isset($rowValue[12]) ? ZCommon::cleanString($rowValue[12]) : '';
                $rowValue14 = isset($rowValue[13]) ? ZCommon::cleanString($rowValue[13]) : '';
                $rowValue15 = isset($rowValue[14]) ? ZCommon::cleanString($rowValue[14]) : '';
                $rowTrim = ZCommon::cleanArray($rowValue);

                //skip unimportant row
                if ($rowValue1 != ''
                && $rowValue2 == ''
                && $rowValue3 == ''
                && $rowValue4 == ''
                && $rowValue5 == '') {
                    continue;
                }
                if ($rowValue1 != ''
                && $rowValue2 != ''
                && $rowValue3 == ''
                && $rowValue4 == ''
                && $rowValue5 == '') {
                    continue;
                }
                if ($rowValue1 != ''
                && $rowValue2 == ''
                && $rowValue3 == ''
                && $rowValue4 != ''
                && $rowValue5 == '') {
                    continue;
                }
                if ($rowValue1 != ''
                && $rowValue2 == ''
                && $rowValue3 == ''
                && $rowValue4 == ''
                && $rowValue5 == ''
                && $rowValue7 != ''
                && $rowValue8 != '') {
                    continue;
                }
                if ($rowValue1 == ''
                && $rowValue2 == ''
                && $rowValue3 == ''
                && $rowValue4 == ''
                && $rowValue5 == ''
                && $rowValue7 != ''
                && $rowValue8 != '') {
                    continue;
                }
                if ($rowValue1 != ''
                && $rowValue2 != ''
                && $rowValue3 == ''
                && $rowValue4 == ''
                && $rowValue5 == '') {
                    continue;
                }
                if ($rowValue1 == ''
                && $rowValue2 != ''
                && $rowValue3 != ''
                && $rowValue4 == ''
                && $rowValue5 == '') {
                    continue;
                }
                if (in_array('PRODUCT', $rowTrim)
                    && in_array('DESCRIPTION', $rowTrim)
                    && in_array('MSRP(USD)', $rowTrim)
                    && (in_array('APPROVEDRESELLER', $rowTrim) || in_array('APPROVEDRESELLERwithVS/CBS5%BVMS16%PerfDiscounts', $rowTrim))) {
                    $sheetTable = 'product';
                    $sheetTableRow = $rowValue;
                    continue;
                }
                // validate mapped colulmn
                if ($sheetTable == '' && $worksheetKey+1 == count($worksheets)) {
                    $errorMessage = '<small>Selected vendor doesn\'t support spreadsheet columns. Sheet Name <strong>"'. $worksheet .'"</strong>.</small><small><ul><li>Make sure spreadsheet belong to selected vendor.</li><li>Make sure spreadsheet mpn column is set in column mapping setting.</li></ul></small>';
                    // $errorMessage = 'Products column is not supported in <em><strong>"'. $worksheet .'"</strong></em> sheet.';
                    throw new Exception($errorMessage);
                    /* Dev::pv($errorMessage);
                    break; */
                }
                // change worksheet
                if ($sheetTable == '') {
                    continue;
                }
                $sheetRowIndexCode = ZCommon::cleanString($worksheetKey .'-'. $worksheet) .'-'. $rowIndex;

                if (isset($options['rawData']) && $options['rawData'] === true) {
                    $rawDataTmp = [];

                    foreach ($sheetTableRow as $sheetTableRowKey => $sheetTableRowVal) {
                        if (isset($rowValue[$sheetTableRowKey])) {
                            $rawDataTmp[ZCommon::cleanString($sheetTableRowVal)] = $rowValue[$sheetTableRowKey];
                        }
                    }

                    $rawData[$sheetRowIndexCode] = $rawDataTmp;

                    continue;
                }

                // set categories
                $sheetCategories[] = Vendor::getVendorNameLabel(Vendor::INDEX_BOSCH);
                $sheetCategories[] = $worksheet;
                
                // set sheets columns and sheets data
                $sheetsData[$worksheet][$sheetTable][] = [
                    'sheet_col' => $sheetTableRow,
                    'sheet_data' => $rowValue,
                    'sheet_row_index' => $rowIndex,
                    'sheet_row_index_code' => $sheetRowIndexCode,
                    'sheet_row_categories' => json_encode($sheetCategories)
                ];
            }
        }
        // return raw data
        if (isset($options['rawData']) && $options['rawData'] === true) {
            return $rawData;
        }

        // retrive column mapping setting data
        $settingColumnMapping = SettingColumnMapping::model()->find()->attributes;

        // remove column unecessary mapping data
        unset($settingColumnMapping['id']);
        unset($settingColumnMapping['status']);
        unset($settingColumnMapping['date_created']);
        unset($settingColumnMapping['date_updated']);

        $settingColumnMappingValues = [];

        // prepare mapping column setting, retrieve
        foreach ($settingColumnMapping as $settingColumnMappingkey => $settingColumnMappingData) {
            $settingColumnMappingData = strtolower($settingColumnMappingData);
            $columnMapData =  array_map('ZCommon::cleanString', explode(',', $settingColumnMappingData));
            $settingColumnMappingValues[$settingColumnMappingkey] = $columnMapData;
        }
        
        $sheetParsedData = [];
        // set columns mapping and values
        foreach ($sheetsData as $sheetsDataKey => $sheetsDataVal) {
            foreach ($sheetsDataVal as $sheetsDataTableKey => $sheetsDataTableVal) {
                foreach ($sheetsDataTableVal as $sheetsDataTableItemKey => $sheetsDataTableItemVal) {
                    $sheetData = $sheetsDataTableItemVal['sheet_data'];
                    $sheetCol = $sheetsDataTableItemVal['sheet_col'];
                    $sheetRowIndex = $sheetsDataTableItemVal['sheet_row_index'];
                    $sheetRowIndexCode = $sheetsDataTableItemVal['sheet_row_index_code'];
                    $sheetCategories = $sheetsDataTableItemVal['sheet_row_categories'];

                    $sheetColumnWithData = [];

                    foreach ($sheetCol as $sheetColumnKey => $sheetColumnVal) {
                        $sheetColumnVal = ZCommon::cleanString($sheetColumnVal);

                        $columnHasMappingData = false;

                        foreach ($settingColumnMappingValues as $settingColumnMappingKey => $settingColumnMappingVal) {
                            if (in_array(strtolower($sheetColumnVal), $settingColumnMappingVal)) {
                                $sheetColumnVal = $settingColumnMappingKey;
                                $columnHasMappingData = true;
                                continue;
                            }
                        }
                        $sheetColumnValRaw = '';
                        if (!$columnHasMappingData) {
                            $sheetColumnValRaw = $sheetColumnVal;
                            $sheetColumnVal = '__' . $sheetColumnValRaw;
                        }

                        $sheetColumnWithData[$sheetColumnVal] = isset($sheetData[$sheetColumnKey]) ? preg_replace("/[\n\r]/", " ", $sheetData[$sheetColumnKey]) : 'not set';
                    }
                    
                    $sheetParsedData[] = $sheetColumnWithData + [
                        'sheets_name' => $sheetsDataKey,
                        'sheets_row_index' => $sheetRowIndex,
                        'sheet_row_index_code' => $sheetRowIndexCode,
                        'sheets_col' => $sheetColumnValRaw,
                        'categories' => $sheetCategories];
                }
            }
        }
        // format sheet data
        foreach ($sheetParsedData as $sheetParsedDataKey => $sheetParsedDataVal) {
            $tmpData = [];
            
            foreach ($sheetParsedDataVal as $sheetRowDatakey => $sheetRowDataVal) {
                $tmpData[$sheetRowDatakey] = $sheetRowDataVal;
            }
            $parsedData[] = $tmpData;
        }

        // validate data mpn
        foreach ($parsedData as $parsedDataKey => $parsedDataValue) {
            $errorSheetName = '';
            $errorSheetRowIndex = '';

            if (!in_array('mpn', array_keys($parsedDataValue))) {
                $errorSheetName = $parsedDataValue['sheets_name'];

                $errorMessage = 'Cannot select spreadsheet mpn, make sure mpn is set in column mapping setting. Sheet Name <strong>"'. $errorSheetName .'"</strong>';

                throw new Exception($errorMessage);
                /* Dev::pv($errorMessage);
                break; */
            }
        }

        return $parsedData;
    }

    protected function _parsedSpreedSheetIsonas($spreedSheetParser, $options = [])
    {
        $parsedData = [];
        $rawData = [];

        $columnIndex = 0;

        // get sheets
        $worksheets = $spreedSheetParser->getWorksheets();

        // validate sheets
        if (count($worksheets) > 1) {
            $errorMessage = 'Spreadsheet for Axis should only contain 1 sheet.';
            throw new Exception($errorMessage);
            /* Dev::pv($errorMessage);
            break; */
        }

        // select sheet
        $spreedSheetIndex = $spreedSheetParser->getWorksheetIndex($worksheets[0]);
        $rows = $spreedSheetParser->createRowIterator($spreedSheetIndex);
        //Dev::pvx($spreedSheetIndex);
        // validate sheet data
        if (!$rows) {
            $errorMessage = 'Spreadsheet has empty content.';
            throw new Exception($errorMessage);
            /* Dev::pv($errorMessage);
            break; */
        }

        $columnRow = [];

        // get and set sheet column
        foreach ($rows as $rowIndex =>$rowValue) {
            $rowValue1 = isset($rowValue[0]) ? ZCommon::cleanString($rowValue[0]) : '';
            $rowValue2 = isset($rowValue[1]) ? ZCommon::cleanString($rowValue[1]) : '';
            $rowValue3 = isset($rowValue[2]) ? ZCommon::cleanString($rowValue[2]) : '';
            $rowValue4 = isset($rowValue[3]) ? ZCommon::cleanString($rowValue[3]) : '';
            $rowValue5 = isset($rowValue[4]) ? ZCommon::cleanString($rowValue[4]) : '';
            $rowValue6 = isset($rowValue[5]) ? ZCommon::cleanString($rowValue[5]) : '';
            $rowTrim = ZCommon::cleanArray($rowValue);

            /* if($rowValue1 != ''
            && $rowValue2 == ''
            && $rowValue3 == ''){
                continue;
            } */
            
            //skip unimportant row
            if ($rowValue1 == ''
            && $rowValue2 == ''
            && $rowValue3 == ''
            && $rowValue4 == ''
            && $rowValue5 == '') {
                continue;
            }
            if ($rowValue1 != ''
            && $rowValue2 == ''
            && $rowValue3 == ''
            && $rowValue4 == ''
            && $rowValue5 == '') {
                continue;
            }
            if ($rowValue1 != ''
            && $rowValue2 == ''
            && $rowValue3 == ''
            && $rowValue4 == ''
            && $rowValue5 != '') {
                continue;
            }
            if ($rowValue1 == ''
            && $rowValue2 == ''
            && $rowValue3 != ''
            && $rowValue4 == ''
            && $rowValue5 == '') {
                continue;
            }

            if(count($rowTrim) < 3){
                continue;
            }

            if (in_array('PartNumber', $rowTrim)
                && in_array('Description', $rowTrim)
                && in_array('MSRP', $rowTrim)) {
                $columnRow = $rowValue;
                $columnIndex = $rowIndex;
                break;
            }
            if (in_array('MPN', $rowTrim)
                && in_array('Description', $rowTrim)
                && in_array('MSRP', $rowTrim)) {
                $columnRow = $rowValue;
                $columnIndex = $rowIndex;
                break;
            }
        }

        // validate sheets columns
        if ($columnIndex <= 0) {
            $errorMessage = 'Spreadsheet has empty content.';
            throw new Exception($errorMessage);
            // Dev::pv($errorMessage);
        }

        // get and set sheet mapped data
        foreach ($rows as $rowIndex => $rowValue) {
            if ($rowIndex >= $columnIndex) {
                foreach ($rowValue as $columnKey => $columnValue) {
                    $hasMatch = false;
                    // retrive column mapping setting data
                    $settingColumnMapping = SettingColumnMapping::model()->find()->attributes;
    
                    // remove column unecessary mapping data
                    unset($settingColumnMapping['id']);
                    unset($settingColumnMapping['status']);
                    unset($settingColumnMapping['date_created']);
                    unset($settingColumnMapping['date_updated']);

                    foreach ($settingColumnMapping as $settingColumnMappingkey => $settingColumnMappingData) {
                        if (trim($settingColumnMappingData) == '') {
                            continue;
                        }
                        
                        $settingColumnMappingData = strtolower($settingColumnMappingData);
                        $settingColumnMappingDataArr = array_map('ZCommon::cleanString', explode(',', $settingColumnMappingData));
    
                        if (in_array(ZCommon::cleanString(strtolower($columnValue)), $settingColumnMappingDataArr)) {
                            $columnMapped[$columnKey] = $settingColumnMappingkey;
                            $hasMatch = true;
                            break;
                        }
                    }

                    if (!$hasMatch) {
                        $columnMapped[$columnIndex] = '';
                    }
                }
                break;
            }
        }
        
        // validate sheet column mapped mpn
        if (!in_array('mpn', $columnMapped)) {
            $errorMessage = 'Cannot select spreadsheet mpn, make sure mpn is set in column mapping setting.';
            throw new Exception($errorMessage);
            // Dev::pv($errorMessage);
        }

        $sheetCategories = [];
        $productCategories = [];
 
        // set sheet column mapped values
        foreach ($rows as $rowIndex => $rowValue) {
            if ($rowIndex > $columnIndex) {
                if (count($rowValue) < 3) {
                    $sheetCategories[] = Vendor::getVendorNameLabel(Vendor::INDEX_ISONAS);
                    $sheetCategories[] = trim($rowValue[0]);
                    continue;
                }

                //check if value is category
                if ($rowValue[0] == $rowValue[1] && $rowValue[0] == $rowValue[2] && $rowValue[3] == $rowValue[0]) {
                    continue;
                }
                
                $tmpData = array();
                $rawDataTmp = [];

                $sheetRowIndexCode = ZCommon::cleanString($worksheets[0] .'-'. $rowIndex);
                foreach ($rowValue as $columnKey => $columnValue) {
                    if (isset($columnMapped[$columnKey]) && $columnMapped[$columnKey] != '') {
                        $tmpData[$columnMapped[$columnKey]] = $columnValue;
                    }

                    if (isset($columnRow[$columnKey])) {
                        $rawDataTmp[ZCommon::cleanString($columnRow[$columnKey])] = $columnValue;
                    }

                    if (count($sheetCategories) > 0) {
                        $productCategories = $sheetCategories;
                    }
                    
                    $tmpData['categories'] = json_encode($productCategories);
                    $tmpData['sheet_row_index'] = $rowIndex;
                }

                $tmpData['sheet_row_index_code'] = $sheetRowIndexCode ;
                
                $sheetCategories = [];

                $rawData[$sheetRowIndexCode] = $rawDataTmp;

                $parsedData[] = $tmpData;
            }
        }

        // return raw data
        if (isset($options['rawData']) && $options['rawData'] === true) {
            return $rawData;
        }

        return $parsedData;
    }

    protected function _parsedSpreedSheetQnap($spreedSheetParser, $options = [])
    {
        $parsedData = [];
        $rawData = [];

        $columnIndex = 0;

        // get sheets
        $worksheets = $spreedSheetParser->getWorksheets();

        // validate sheets
        if (count($worksheets) > 1) {
            $errorMessage = 'Spreadsheet for QNAP should only contain 1 sheet.';
            throw new Exception($errorMessage);
            /* Dev::pv($errorMessage);
            break; */
        }

        // select sheet
        $spreedSheetIndex = $spreedSheetParser->getWorksheetIndex($worksheets[0]);
        $rows = $spreedSheetParser->createRowIterator($spreedSheetIndex);
        // validate sheet data
        if (!$rows) {
            Dev::pv($rows);
            $errorMessage = 'Spreadsheet has empty content.';
            throw new Exception($errorMessage);
            /* Dev::pv($errorMessage);
            break; */
        }

        $columnRow = [];

        // get and set sheet column
        foreach ($rows as $rowIndex =>$rowValue) {
            $rowValue1 = isset($rowValue[0]) ? ZCommon::cleanString($rowValue[0]) : '';
            $rowValue2 = isset($rowValue[1]) ? ZCommon::cleanString($rowValue[1]) : '';
            $rowValue3 = isset($rowValue[2]) ? ZCommon::cleanString($rowValue[2]) : '';
            $rowValue4 = isset($rowValue[3]) ? ZCommon::cleanString($rowValue[3]) : '';
            $rowValue5 = isset($rowValue[4]) ? ZCommon::cleanString($rowValue[4]) : '';
            $rowValue6 = isset($rowValue[5]) ? ZCommon::cleanString($rowValue[5]) : '';
            $rowValue7 = isset($rowValue[6]) ? ZCommon::cleanString($rowValue[6]) : '';
            $rowValue8 = isset($rowValue[7]) ? ZCommon::cleanString($rowValue[7]) : '';
            $rowTrim = ZCommon::cleanArray($rowValue);

            /* if($rowValue1 != ''
            && $rowValue2 == ''
            && $rowValue3 == ''){
                continue;
            } */
            
            //skip unimportant row
            if ($rowValue1 == ''
            && $rowValue2 == ''
            && $rowValue3 == ''
            && $rowValue4 == ''
            && $rowValue5 == '') {
                continue;
            }
            if ($rowValue1 != ''
            && $rowValue2 != ''
            && $rowValue3 == ''
            && $rowValue4 == ''
            && $rowValue5 == '') {
                continue;
            }
            if ($rowValue1 != ''
            && $rowValue2 == ''
            && $rowValue3 == ''
            && $rowValue4 == ''
            && $rowValue5 != '') {
                continue;
            }
            
            if (in_array('VendPart#', $rowTrim)
                && (in_array('Desc.Line2(Max.35characters)', $rowTrim) || in_array('Description', $rowTrim))
                && (in_array('DistributorCost(FOBOrigin)', $rowTrim) || in_array('DistributorCost', $rowTrim))
                && in_array('MSRP', $rowTrim)) {
                $columnRow = $rowValue;
                $columnIndex = $rowIndex;
                break;
            }

            if (in_array('MPN', $rowTrim)
                && in_array('MSRP', $rowTrim)
                && in_array('Cost', $rowTrim)
                && in_array('Description', $rowTrim)) {
                $columnRow = $rowValue;
                $columnIndex = $rowIndex;
                break;
            }

            if (in_array('PartNumber', $rowTrim)
                && in_array('MSRP', $rowTrim)
                && in_array('Cost', $rowTrim)
                && in_array('Description', $rowTrim)) {
                $columnRow = $rowValue;
                $columnIndex = $rowIndex;
                break;
            }
        }

        // validate sheets columns
        if ($columnIndex <= 0) {
            $errorMessage = 'Spreadsheet has empty content.';
            throw new Exception($errorMessage);
            /* Dev::pv($errorMessage);
            break; */
        }

        // get and set sheet mapped data
        foreach ($rows as $rowIndex => $rowValue) {
            if ($rowIndex >= $columnIndex) {
                foreach ($rowValue as $columnKey => $columnValue) {
                    $hasMatch = false;
                    // retrive column mapping setting data
                    $settingColumnMapping = SettingColumnMapping::model()->find()->attributes;
    
                    // remove column unecessary mapping data
                    unset($settingColumnMapping['id']);
                    unset($settingColumnMapping['status']);
                    unset($settingColumnMapping['date_created']);
                    unset($settingColumnMapping['date_updated']);

                    foreach ($settingColumnMapping as $settingColumnMappingkey => $settingColumnMappingData) {
                        if (trim($settingColumnMappingData) == '') {
                            continue;
                        }
                        
                        $settingColumnMappingData = strtolower($settingColumnMappingData);
                        $settingColumnMappingDataArr = array_map('ZCommon::cleanString', explode(',', $settingColumnMappingData));
    
                        if (in_array(ZCommon::cleanString(strtolower($columnValue)), $settingColumnMappingDataArr)) {
                            $columnMapped[$columnKey] = $settingColumnMappingkey;
                            $hasMatch = true;
                            break;
                        }
                    }

                    if (!$hasMatch) {
                        $columnMapped[$columnIndex] = '';
                    }
                }
                break;
            }
        }
        
        // validate sheet column mapped mpn
        if (!in_array('mpn', $columnMapped)) {
            $errorMessage = 'Cannot select spreadsheet mpn, make sure mpn is set in column mapping setting.';
            throw new Exception($errorMessage);
            /* Dev::pv($errorMessage);
            break; */
        }

        $sheetCategories = [];
        $productCategories = [];
 
        // set sheet column mapped values
        foreach ($rows as $rowIndex => $rowValue) {
            if ($rowIndex > $columnIndex) {
                if (count($rowValue) < 4) {
                    $sheetCategories[] = Vendor::getVendorNameLabel(Vendor::INDEX_QNAP);
                    $sheetCategories[] = trim($rowValue[0]);
                    continue;
                }

                //check if value is category
                if ($rowValue[0] == $rowValue[1] && $rowValue[0] == $rowValue[2] && $rowValue[3] == $rowValue[0]) {
                    continue;
                }
                
                $tmpData = array();
                $rawDataTmp = [];

                $sheetRowIndexCode = ZCommon::cleanString($worksheets[0] .'-'. $rowIndex);
                
                foreach ($rowValue as $columnKey => $columnValue) {
                    if (isset($columnMapped[$columnKey]) && $columnMapped[$columnKey] != '') {
                        $tmpData[$columnMapped[$columnKey]] = $columnValue;
                    }

                    if (isset($columnRow[$columnKey])) {
                        $rawDataTmp[ZCommon::cleanString($columnRow[$columnKey])] = $columnValue;
                    }

                    if (count($sheetCategories) > 0) {
                        $productCategories = $sheetCategories;
                    }
                    
                    $tmpData['categories'] = json_encode($productCategories);
                    $tmpData['sheet_row_index'] = $rowIndex;
                }

                $tmpData['sheet_row_index_code'] = $sheetRowIndexCode ;

                $sheetCategories = [];

                $rawData[$sheetRowIndexCode] = $rawDataTmp;

                $parsedData[] = $tmpData;
            }
        }

        // return raw data
        if (isset($options['rawData']) && $options['rawData'] === true) {
            return $rawData;
        }
        
        return $parsedData;
    }

    protected function _parsedSpreedSheetRaytec($spreedSheetParser, $options = [])
    {
        $parsedData = [];
        $rawData = [];

        $columnIndex = 0;

        // get sheets
        $worksheets = $spreedSheetParser->getWorksheets();

        // validate sheets
        /*if (count($worksheets) > 1) {
            throw new Exception('Spreadsheet for Raytec should only contain 1 sheet.');
        }*/

        // select sheet
        $spreedSheetIndex = $spreedSheetParser->getWorksheetIndex($worksheets[0]);
        $rows = $spreedSheetParser->createRowIterator($spreedSheetIndex);

        // validate sheet data
        if (!$rows) {
            $errorMessage = 'Spreadsheet has empty content.';
            throw new Exception($errorMessage);
            /* Dev::pv($errorMessage);
            break; */
        }

        $columnRow = [];

        // get and set sheet column
        foreach ($rows as $rowIndex =>$rowValue) {
            $rowValue1 = isset($rowValue[0]) ? ZCommon::cleanString($rowValue[0]) : '';
            $rowValue2 = isset($rowValue[1]) ? ZCommon::cleanString($rowValue[1]) : '';
            $rowValue3 = isset($rowValue[2]) ? ZCommon::cleanString($rowValue[2]) : '';
            $rowValue4 = isset($rowValue[3]) ? ZCommon::cleanString($rowValue[3]) : '';
            $rowValue5 = isset($rowValue[4]) ? ZCommon::cleanString($rowValue[4]) : '';
            $rowValue6 = isset($rowValue[5]) ? ZCommon::cleanString($rowValue[5]) : '';
            $rowValue7 = isset($rowValue[6]) ? ZCommon::cleanString($rowValue[6]) : '';
            $rowValue8 = isset($rowValue[7]) ? ZCommon::cleanString($rowValue[7]) : '';
            $rowTrim = ZCommon::cleanArray($rowValue);

            /* if($rowValue1 != ''
            && $rowValue2 == ''
            && $rowValue3 == ''){
                continue;
            } */
            
            //skip unimportant row
            if(count($rowTrim) < 3){
                continue;
            }
            if(empty($rowTrim)){
                continue;
            }
            if ($rowValue1 == ''
            && $rowValue2 == ''
            && $rowValue3 == ''
            && $rowValue4 == ''
            && $rowValue5 == '') {
                continue;
            }
            if ($rowValue1 == ''
            && $rowValue2 == ''
            && $rowValue3 == ''
            && $rowValue4 != ''
            && $rowValue5 != '') {
                continue;
            }
            if ($rowValue1 != ''
            && $rowValue2 != ''
            && $rowValue3 == ''
            && $rowValue4 == ''
            && $rowValue5 == '') {
                continue;
            }
            if ($rowValue1 != ''
            && $rowValue2 == ''
            && $rowValue3 == ''
            && $rowValue4 == ''
            && $rowValue5 != '') {
                continue;
            }

            if (in_array('PartNumber', $rowTrim)
                && in_array('Description', $rowTrim)
                && in_array('GSS', $rowTrim)
                && in_array('MSRP', $rowTrim)) {
                $columnRow = $rowValue;
                $columnIndex = $rowIndex;
                break;
            }
        }

        // validate sheets columns
        if ($columnIndex <= 0) {
            $errorMessage = 'Spreadsheet has empty content.';
            throw new Exception($errorMessage);
            /* Dev::pv($errorMessage);
            break; */
        }

        // get and set sheet mapped data
        foreach ($rows as $rowIndex => $rowValue) {
            if ($rowIndex >= $columnIndex) {
                foreach ($rowValue as $columnKey => $columnValue) {
                    $hasMatch = false;
                    // retrive column mapping setting data
                    $settingColumnMapping = SettingColumnMapping::model()->find()->attributes;
    
                    // remove column unecessary mapping data
                    unset($settingColumnMapping['id']);
                    unset($settingColumnMapping['status']);
                    unset($settingColumnMapping['date_created']);
                    unset($settingColumnMapping['date_updated']);

                    foreach ($settingColumnMapping as $settingColumnMappingkey => $settingColumnMappingData) {
                        if (trim($settingColumnMappingData) == '') {
                            continue;
                        }
                        
                        $settingColumnMappingData = strtolower($settingColumnMappingData);
                        $settingColumnMappingDataArr = array_map('ZCommon::cleanString', explode(',', $settingColumnMappingData));
    
                        if (in_array(ZCommon::cleanString(strtolower($columnValue)), $settingColumnMappingDataArr)) {
                            $columnMapped[$columnKey] = $settingColumnMappingkey;
                            $hasMatch = true;
                            break;
                        }
                    }

                    if (!$hasMatch) {
                        $columnMapped[$columnIndex] = '';
                    }
                }
                break;
            }
        }
        
        // validate sheet column mapped mpn
        if (!in_array('mpn', $columnMapped)) {
            $errorMessage = 'Cannot select spreadsheet mpn, make sure mpn is set in column mapping setting.';
            throw new Exception($errorMessage);
            /* Dev::pv($errorMessage);
            break; */
        }

        $sheetCategories = [];
        $productCategories = [];
 
        // set sheet column mapped values
        foreach ($rows as $rowIndex => $rowValue) {
            if ($rowIndex > $columnIndex) {
                if (count($rowValue) < 3) {
                    $sheetCategories[] = Vendor::getVendorNameLabel(Vendor::INDEX_RAYTEC);
                    $sheetCategories[] = trim($rowValue[0]);
                    continue;
                }

                //check if value is category
                if ($rowValue[0] == $rowValue[1] && $rowValue[0] == $rowValue[2] && $rowValue[3] == $rowValue[0]) {
                    continue;
                }
                
                $tmpData = array();
                $rawDataTmp = [];

                $sheetRowIndexCode = ZCommon::cleanString($worksheets[0] .'-'. $rowIndex);
                
                foreach ($rowValue as $columnKey => $columnValue) {
                    if (isset($columnMapped[$columnKey]) && $columnMapped[$columnKey] != '') {
                        if($columnValue != 'MSRP' || $columnValue != 'GSS'){
                            $tmpData[$columnMapped[$columnKey]] = $columnValue;
                        } 
                    }

                    if (isset($columnRow[$columnKey])) {
                        if($columnValue != 'MSRP' || $columnValue != 'GSS'){
                            $rawDataTmp[ZCommon::cleanString($columnRow[$columnKey])] = $columnValue;
                        } 
                    }

                    if (count($sheetCategories) > 0) {
                        $productCategories = $sheetCategories;
                    }
                    
                    $tmpData['categories'] = json_encode($productCategories);
                    $tmpData['sheet_row_index'] = $rowIndex;
                }

                $tmpData['sheet_row_index_code'] = $sheetRowIndexCode ;

                $sheetCategories = [];

                $rawData[$sheetRowIndexCode] = $rawDataTmp;

                $parsedData[] = $tmpData;
            }
        }
        // return raw data
        if (isset($options['rawData']) && $options['rawData'] === true) {
            return $rawData;
        }
        
        return $parsedData;
    }

    protected function _parsedSpreedSheetEntrustDatacard($spreedSheetParser, $options = [])
    {
        $parsedData = [];
        $sheetsData = [];
        $rawData = [];

        // get sheets
        $worksheets = $spreedSheetParser->getWorksheets();

        //set sheets data
        foreach ($worksheets as $worksheetKey => $worksheet) {
    
            // get sheet sheets rows data
            $myWorksheetIndex = $spreedSheetParser->getWorksheetIndex($worksheet);
            $rows = $spreedSheetParser->createRowIterator($myWorksheetIndex);
            // validate sheet data
            if (!$rows) {
                $errorMessage = 'Spreadsheet has empty content.';
                throw new Exception($errorMessage);
                /* Dev::pv($errorMessage);
                break; */
            }
            
            $sheetTable = '';
            $sheetTableRow = '';

            // get and set sheet column
            foreach ($rows as $rowIndex => $rowValue) {
                $sheetCategories = [];
                
                // collect spreedsheet rows
                $rowValue1 = isset($rowValue[0]) ? ZCommon::cleanString($rowValue[0]) : '';
                $rowValue2 = isset($rowValue[1]) ? ZCommon::cleanString($rowValue[1]) : '';
                $rowValue3 = isset($rowValue[2]) ? ZCommon::cleanString($rowValue[2]) : '';
                $rowValue4 = isset($rowValue[3]) ? ZCommon::cleanString($rowValue[3]) : '';
                $rowValue5 = isset($rowValue[4]) ? ZCommon::cleanString($rowValue[4]) : '';
                $rowValue6 = isset($rowValue[5]) ? ZCommon::cleanString($rowValue[5]) : '';
                $rowValue7 = isset($rowValue[6]) ? ZCommon::cleanString($rowValue[6]) : '';
                $rowValue8 = isset($rowValue[7]) ? ZCommon::cleanString($rowValue[7]) : '';
                $rowValue9 = isset($rowValue[8]) ? ZCommon::cleanString($rowValue[8]) : '';
                $rowValue10 = isset($rowValue[9]) ? ZCommon::cleanString($rowValue[9]) : '';
                $rowValue11 = isset($rowValue[10]) ? ZCommon::cleanString($rowValue[10]) : '';
                $rowValue12 = isset($rowValue[11]) ? ZCommon::cleanString($rowValue[11]) : '';
                $rowValue13 = isset($rowValue[12]) ? ZCommon::cleanString($rowValue[12]) : '';
                $rowValue14 = isset($rowValue[13]) ? ZCommon::cleanString($rowValue[13]) : '';
                $rowValue15 = isset($rowValue[14]) ? ZCommon::cleanString($rowValue[14]) : '';
                $rowValue16 = isset($rowValue[15]) ? ZCommon::cleanString($rowValue[15]) : '';
                $rowValue17 = isset($rowValue[16]) ? ZCommon::cleanString($rowValue[16]) : '';
                $rowTrim = ZCommon::cleanArray($rowValue);
                //skip unimportant row
                if ($rowValue1 != ''
                && $rowValue2 == ''
                && $rowValue3 == ''
                && $rowValue4 == ''
                && $rowValue5 == '') {
                    continue;
                }
                if ($rowValue1 == ''
                && $rowValue2 == ''
                && $rowValue3 == ''
                && $rowValue4 == ''
                && $rowValue5 == '') {
                    continue;
                }
                /* if ($rowValue1 != ''
                 && $rowValue2 != ''
                 && $rowValue3 != ''
                 && $rowValue4 != ''
                 && $rowValue5 == '') {
                     continue;
                 }
                 if ($rowValue1 == ''
                 && $rowValue2 == ''
                 && $rowValue3 == ''
                 && $rowValue4 == ''
                 && $rowValue5 == '') {
                     continue;
                 }
                 if ($rowValue1 != ''
                 && $rowValue2 == ''
                 && $rowValue3 == ''
                 && $rowValue4 == ''
                 && $rowValue5 == '') {
                     continue;
                 }
                 if ($rowValue1 != ''
                 && $rowValue2 != ''
                 && $rowValue3 == ''
                 && $rowValue4 == ''
                 && $rowValue5 == '') {
                     continue;
                 }
                 if ($rowValue1 == ''
                 && $rowValue2 != ''
                 && $rowValue3 != ''
                 && $rowValue4 == ''
                 && $rowValue5 == '') {
                     continue;
                 }*/

                // Export sheets
                if (in_array('Item', $rowTrim)
                    && in_array('Description', $rowTrim)
                    && in_array('ListPrice', $rowTrim)
                    && in_array('CustomerPrice', $rowTrim)) {
                    $sheetTable = 'items';
                    $sheetTableRow = $rowValue;
                    continue;
                }
                
                //skip worksheet
                if ($sheetTable == '' && $worksheetKey+1 != count($worksheets)) {
                    continue;
                }

                // validate mapped colulmn
                if ($sheetTable == '' && $worksheetKey+1 == count($worksheets)) {
                    $errorMessage = '<small>Selected vendor doesn\'t support spreadsheet columns. Sheet Name <strong>"'. $worksheet .'"</strong>.</small><small><ul><li>Make sure spreadsheet belong to selected vendor.</li><li>Make sure spreadsheet mpn column is set in column mapping setting.</li></ul></small>';
                    // $errorMessage = 'Products column is not supported in <em><strong>"'. $worksheet .'"</strong></em> sheet.';
                    throw new Exception($errorMessage);
                    /* Dev::pv($errorMessage);
                    break; */
                }

                $sheetRowIndexCode = ZCommon::cleanString($worksheetKey .'-'. $worksheet) .'-'. $rowIndex;

                if (isset($options['rawData']) && $options['rawData'] === true) {
                    $rawDataTmp = [];

                    foreach ($sheetTableRow as $sheetTableRowKey => $sheetTableRowVal) {
                        if (isset($rowValue[$sheetTableRowKey])) {
                            $rawDataTmp[ZCommon::cleanString($sheetTableRowVal)] = $rowValue[$sheetTableRowKey];
                        }
                    }

                    $rawData[$sheetRowIndexCode] = $rawDataTmp;

                    continue;
                }

                // set categories
                $sheetCategories[] = Vendor::getVendorNameLabel(Vendor::INDEX_ENTRUST_DATACARD);
                $sheetCategories[] = $worksheet;
                
                // set sheets columns and sheets data
                $sheetsData[$worksheet][$sheetTable][] = [
                    'sheet_col' => $sheetTableRow,
                    'sheet_data' => $rowValue,
                    'sheet_row_index' => $rowIndex,
                    'sheet_row_index_code' => $sheetRowIndexCode,
                    'sheet_row_categories' => json_encode($sheetCategories)
                ];
            }
        }
        // return raw data
        if (isset($options['rawData']) && $options['rawData'] === true) {
            return $rawData;
        }

        // retrive column mapping setting data
        $settingColumnMapping = SettingColumnMapping::model()->find()->attributes;

        // remove column unecessary mapping data
        unset($settingColumnMapping['id']);
        unset($settingColumnMapping['status']);
        unset($settingColumnMapping['date_created']);
        unset($settingColumnMapping['date_updated']);

        $settingColumnMappingValues = [];

        // prepare mapping column setting, retrieve
        foreach ($settingColumnMapping as $settingColumnMappingkey => $settingColumnMappingData) {
            $settingColumnMappingData = strtolower($settingColumnMappingData);
            $columnMapData =  array_map('ZCommon::cleanString', explode(',', $settingColumnMappingData));
            $settingColumnMappingValues[$settingColumnMappingkey] = $columnMapData;
        }
        
        $sheetParsedData = [];

        // set columns mapping and values
        foreach ($sheetsData as $sheetsDataKey => $sheetsDataVal) {
            foreach ($sheetsDataVal as $sheetsDataTableKey => $sheetsDataTableVal) {
                foreach ($sheetsDataTableVal as $sheetsDataTableItemKey => $sheetsDataTableItemVal) {
                    $sheetData = $sheetsDataTableItemVal['sheet_data'];
                    $sheetCol = $sheetsDataTableItemVal['sheet_col'];
                    $sheetRowIndex = $sheetsDataTableItemVal['sheet_row_index'];
                    $sheetRowIndexCode = $sheetsDataTableItemVal['sheet_row_index_code'];
                    $sheetCategories = $sheetsDataTableItemVal['sheet_row_categories'];

                    $sheetColumnWithData = [];

                    foreach ($sheetCol as $sheetColumnKey => $sheetColumnVal) {
                        $sheetColumnVal = ZCommon::cleanString($sheetColumnVal);

                        $columnHasMappingData = false;

                        foreach ($settingColumnMappingValues as $settingColumnMappingKey => $settingColumnMappingVal) {
                            if (in_array(strtolower($sheetColumnVal), $settingColumnMappingVal)) {
                                $sheetColumnVal = $settingColumnMappingKey;
                                $columnHasMappingData = true;
                                continue;
                            }
                        }
                        $sheetColumnValRaw = '';
                        if (!$columnHasMappingData) {
                            $sheetColumnValRaw = $sheetColumnVal;
                            $sheetColumnVal = '__' . $sheetColumnValRaw;
                        }

                        $sheetColumnWithData[$sheetColumnVal] = isset($sheetData[$sheetColumnKey]) ? preg_replace("/[\n\r]/", " ", $sheetData[$sheetColumnKey]) : 'not set';
                    }
                    
                    $sheetParsedData[] = $sheetColumnWithData + [
                        'sheets_name' => $sheetsDataKey,
                        'sheets_row_index' => $sheetRowIndex,
                        'sheet_row_index_code' => $sheetRowIndexCode,
                        'sheets_col' => $sheetColumnValRaw,
                        'categories' => $sheetCategories];
                }
            }
        }

        // format sheet data
        foreach ($sheetParsedData as $sheetParsedDataKey => $sheetParsedDataVal) {
            $tmpData = [];
            
            foreach ($sheetParsedDataVal as $sheetRowDatakey => $sheetRowDataVal) {
                $tmpData[$sheetRowDatakey] = $sheetRowDataVal;
            }
            $parsedData[] = $tmpData;
        }

        // validate data mpn
        foreach ($parsedData as $parsedDataKey => $parsedDataValue) {
            $errorSheetName = '';
            $errorSheetRowIndex = '';

            if (!in_array('mpn', array_keys($parsedDataValue))) {
                $errorSheetName = $parsedDataValue['sheets_name'];

                $errorMessage = 'Cannot select spreadsheet mpn, make sure mpn is set in column mapping setting. Sheet Name <strong>"'. $errorSheetName .'"</strong>';
                throw new Exception($errorMessage);
                /* Dev::pv($errorMessage);
                break; */
            }
        }

        return $parsedData;
    }

    protected function _parsedSpreedSheetAVCostar($spreedSheetParser, $options = [])
    {
        $parsedData = [];
        $sheetsData = [];
        $rawData = [];

        // get sheets
        $worksheets = $spreedSheetParser->getWorksheets();

        //set sheets data
        foreach ($worksheets as $worksheetKey => $worksheet) {
            
            //worksheet target

            // get sheet sheets rows data
            $myWorksheetIndex = $spreedSheetParser->getWorksheetIndex($worksheet);
            $rows = $spreedSheetParser->createRowIterator($myWorksheetIndex);
            // validate sheet data
            if (!$rows) {
                $errorMessage = 'Spreadsheet has empty content.';
                throw new Exception($errorMessage);
                /* Dev::pv($errorMessage);
                break; */
            }
            $sheetTable = '';
            $sheetTableRow = '';

            // get and set sheet column
            foreach ($rows as $rowIndex => $rowValue) {
                $sheetCategories = [];
                
                // collect spreedsheet rows
                $rowValue1 = isset($rowValue[0]) ? ZCommon::cleanString($rowValue[0]) : '';
                $rowValue2 = isset($rowValue[1]) ? ZCommon::cleanString($rowValue[1]) : '';
                $rowValue3 = isset($rowValue[2]) ? ZCommon::cleanString($rowValue[2]) : '';
                $rowValue4 = isset($rowValue[3]) ? ZCommon::cleanString($rowValue[3]) : '';
                $rowValue5 = isset($rowValue[4]) ? ZCommon::cleanString($rowValue[4]) : '';
                $rowValue6 = isset($rowValue[5]) ? ZCommon::cleanString($rowValue[5]) : '';
                $rowValue7 = isset($rowValue[6]) ? ZCommon::cleanString($rowValue[6]) : '';
                $rowValue8 = isset($rowValue[7]) ? ZCommon::cleanString($rowValue[7]) : '';
                $rowValue9 = isset($rowValue[8]) ? ZCommon::cleanString($rowValue[8]) : '';
                $rowValue10 = isset($rowValue[9]) ? ZCommon::cleanString($rowValue[9]) : '';
                $rowValue11 = isset($rowValue[10]) ? ZCommon::cleanString($rowValue[10]) : '';
                $rowValue12 = isset($rowValue[11]) ? ZCommon::cleanString($rowValue[11]) : '';
                $rowValue13 = isset($rowValue[12]) ? ZCommon::cleanString($rowValue[12]) : '';
                $rowValue14 = isset($rowValue[13]) ? ZCommon::cleanString($rowValue[13]) : '';
                $rowValue15 = isset($rowValue[14]) ? ZCommon::cleanString($rowValue[14]) : '';
                $rowTrim = ZCommon::cleanArray($rowValue);
                $rowstrtolower = array_map('strtolower',$rowTrim);
                
                
                //skip unimportant row
                if ($rowValue1 == ''
                && $rowValue2 == ''
                && $rowValue3 == ''
                && $rowValue4 == ''
                && $rowValue5 == '') {
                    continue;
                }
                if ($rowValue1 != ''
                && $rowValue2 == ''
                && $rowValue3 == ''
                && $rowValue4 == ''
                && $rowValue5 == '') {
                    continue;
                }
                if ($rowValue1 != ''
                && $rowValue2 == ''
                && $rowValue3 == ''
                && $rowValue4 == ''
                && $rowValue5 != '') {
                    continue;
                }

                if (in_array('PartNumber', $rowTrim)
                    && in_array('Description', $rowTrim)
                    /*&& in_array('DistributionPrice', $rowTrim)*/
                    && in_array('MSRP', $rowTrim)) {
                        $sheetTable = 'AV Costar 1';
                        $sheetTableRow = $rowValue;
                        continue;
                }

                // validate mapped colulmn
                if ($sheetTable == '' && $worksheetKey+1 == count($worksheets)) {
                    $errorMessage = '<small>Selected vendor doesn\'t support spreadsheet columns. Sheet Name <strong>"'. $worksheet .'"</strong>.</small><small><ul><li>Make sure spreadsheet belong to selected vendor.</li><li>Make sure spreadsheet mpn column is set in column mapping setting.</li></ul></small>';
                    // $errorMessage = 'Products column is not supported in <em><strong>"'. $worksheet .'"</strong></em> sheet.';
                    throw new Exception($errorMessage);
                    /* Dev::pv($errorMessage);
                    break; */
                }
                // change worksheet
                if ($sheetTable == '') {
                    continue;
                }
                $sheetRowIndexCode = ZCommon::cleanString($worksheetKey .'-'. $worksheet) .'-'. $rowIndex;

                if (isset($options['rawData']) && $options['rawData'] === true) {
                    $rawDataTmp = [];

                    foreach ($sheetTableRow as $sheetTableRowKey => $sheetTableRowVal) {
                        if (isset($rowValue[$sheetTableRowKey])) {
                            $rawDataTmp[ZCommon::cleanString($sheetTableRowVal)] = $rowValue[$sheetTableRowKey];
                        }
                    }

                    $rawData[$sheetRowIndexCode] = $rawDataTmp;

                    continue;
                }

                // set categories
                $sheetCategories[] = Vendor::getVendorNameLabel(Vendor::INDEX_AV_COSTAR);
                $sheetCategories[] = $worksheet;
                
                // set sheets columns and sheets data
                $sheetsData[$worksheet][$sheetTable][] = [
                    'sheet_col' => $sheetTableRow,
                    'sheet_data' => $rowValue,
                    'sheet_row_index' => $rowIndex,
                    'sheet_row_index_code' => $sheetRowIndexCode,
                    'sheet_row_categories' => json_encode($sheetCategories)
                ];
            }
        }
       // Dev::pvx($sheetsData);
        // return raw data
        if (isset($options['rawData']) && $options['rawData'] === true) {
            return $rawData;
        }

        // retrive column mapping setting data
        $settingColumnMapping = SettingColumnMapping::model()->find()->attributes;

        // remove column unecessary mapping data
        unset($settingColumnMapping['id']);
        unset($settingColumnMapping['status']);
        unset($settingColumnMapping['date_created']);
        unset($settingColumnMapping['date_updated']);

        $settingColumnMappingValues = [];

        // prepare mapping column setting, retrieve
        foreach ($settingColumnMapping as $settingColumnMappingkey => $settingColumnMappingData) {
            $settingColumnMappingData = strtolower($settingColumnMappingData);
            $columnMapData =  array_map('ZCommon::cleanString', explode(',', $settingColumnMappingData));
            $settingColumnMappingValues[$settingColumnMappingkey] = $columnMapData;
        }
        
        $sheetParsedData = [];
        // set columns mapping and values
        foreach ($sheetsData as $sheetsDataKey => $sheetsDataVal) {
            foreach ($sheetsDataVal as $sheetsDataTableKey => $sheetsDataTableVal) {
                foreach ($sheetsDataTableVal as $sheetsDataTableItemKey => $sheetsDataTableItemVal) {
                    $sheetData = $sheetsDataTableItemVal['sheet_data'];
                    $sheetCol = $sheetsDataTableItemVal['sheet_col'];
                    $sheetRowIndex = $sheetsDataTableItemVal['sheet_row_index'];
                    $sheetRowIndexCode = $sheetsDataTableItemVal['sheet_row_index_code'];
                    $sheetCategories = $sheetsDataTableItemVal['sheet_row_categories'];

                    $sheetColumnWithData = [];

                    foreach ($sheetCol as $sheetColumnKey => $sheetColumnVal) {
                        $sheetColumnVal = ZCommon::cleanString($sheetColumnVal);

                        $columnHasMappingData = false;

                        foreach ($settingColumnMappingValues as $settingColumnMappingKey => $settingColumnMappingVal) {
                            if (in_array(strtolower($sheetColumnVal), $settingColumnMappingVal)) {
                                $sheetColumnVal = $settingColumnMappingKey;
                                $columnHasMappingData = true;
                                continue;
                            }
                        }
                        $sheetColumnValRaw = '';
                        if (!$columnHasMappingData) {
                            $sheetColumnValRaw = $sheetColumnVal;
                            $sheetColumnVal = '__' . $sheetColumnValRaw;
                        }

                        $sheetColumnWithData[$sheetColumnVal] = isset($sheetData[$sheetColumnKey]) ? preg_replace("/[\n\r]/", " ", $sheetData[$sheetColumnKey]) : 'not set';
                    }
                    
                    $sheetParsedData[] = $sheetColumnWithData + [
                        'sheets_name' => $sheetsDataKey,
                        'sheets_row_index' => $sheetRowIndex,
                        'sheet_row_index_code' => $sheetRowIndexCode,
                        'sheets_col' => $sheetColumnValRaw,
                        'categories' => $sheetCategories];
                }
            }
        }
        // format sheet data
        foreach ($sheetParsedData as $sheetParsedDataKey => $sheetParsedDataVal) {
            $tmpData = [];
            
            foreach ($sheetParsedDataVal as $sheetRowDatakey => $sheetRowDataVal) {
                $tmpData[$sheetRowDatakey] = $sheetRowDataVal;
            }
            $parsedData[] = $tmpData;
        }
       // Dev::pvx($parsedData);
        // validate data mpn
        foreach ($parsedData as $parsedDataKey => $parsedDataValue) {
            $errorSheetName = '';
            $errorSheetRowIndex = '';

            if (!in_array('mpn', array_keys($parsedDataValue))) {
                $errorSheetName = $parsedDataValue['sheets_name'];

                $errorMessage = 'Cannot select spreadsheet mpn, make sure mpn is set in column mapping setting. Sheet Name <strong>"'. $errorSheetName .'"</strong>';

                throw new Exception($errorMessage);
                /* Dev::pv($errorMessage);
                break; */
            }
        }

        return $parsedData;
    }

    protected function _parsedSpreedSheetLocknetics($spreedSheetParser, $options = [])
    {
        $parsedData = [];
        $sheetsData = [];
        $rawData = [];

        // get sheets
        $worksheets = $spreedSheetParser->getWorksheets();

        //set sheets data
        foreach ($worksheets as $worksheetKey => $worksheet) {
            
            //worksheet target

            // get sheet sheets rows data
            $myWorksheetIndex = $spreedSheetParser->getWorksheetIndex($worksheet);
            $rows = $spreedSheetParser->createRowIterator($myWorksheetIndex);
            // validate sheet data
            if (!$rows) {
                $errorMessage = 'Spreadsheet has empty content.';
                throw new Exception($errorMessage);
                /* Dev::pv($errorMessage);
                break; */
            }
            $sheetTable = '';
            $sheetTableRow = '';

            // get and set sheet column
            foreach ($rows as $rowIndex => $rowValue) {
                $sheetCategories = [];
                
                // collect spreedsheet rows
                $rowValue1 = isset($rowValue[0]) ? ZCommon::cleanString($rowValue[0]) : '';
                $rowValue2 = isset($rowValue[1]) ? ZCommon::cleanString($rowValue[1]) : '';
                $rowValue3 = isset($rowValue[2]) ? ZCommon::cleanString($rowValue[2]) : '';
                $rowValue4 = isset($rowValue[3]) ? ZCommon::cleanString($rowValue[3]) : '';
                $rowValue5 = isset($rowValue[4]) ? ZCommon::cleanString($rowValue[4]) : '';
                $rowValue6 = isset($rowValue[5]) ? ZCommon::cleanString($rowValue[5]) : '';
                $rowValue7 = isset($rowValue[6]) ? ZCommon::cleanString($rowValue[6]) : '';
                $rowValue8 = isset($rowValue[7]) ? ZCommon::cleanString($rowValue[7]) : '';
                $rowValue9 = isset($rowValue[8]) ? ZCommon::cleanString($rowValue[8]) : '';
                $rowValue10 = isset($rowValue[9]) ? ZCommon::cleanString($rowValue[9]) : '';
                $rowValue11 = isset($rowValue[10]) ? ZCommon::cleanString($rowValue[10]) : '';
                $rowValue12 = isset($rowValue[11]) ? ZCommon::cleanString($rowValue[11]) : '';
                $rowValue13 = isset($rowValue[12]) ? ZCommon::cleanString($rowValue[12]) : '';
                $rowValue14 = isset($rowValue[13]) ? ZCommon::cleanString($rowValue[13]) : '';
                $rowValue15 = isset($rowValue[14]) ? ZCommon::cleanString($rowValue[14]) : '';
                $rowTrim = ZCommon::cleanArray($rowValue);
                $rowstrtolower = array_map('strtolower',$rowTrim);
                //skip unimportant row
                if ($rowValue1 != ''
                && $rowValue2 == ''
                && $rowValue3 == ''
                && $rowValue4 == ''
                && $rowValue5 == '') {
                    continue;
                }
                if ($rowValue1 != ''
                && $rowValue2 != ''
                && $rowValue3 == ''
                && $rowValue4 == ''
                && $rowValue5 == '') {
                    continue;
                }
                if ($rowValue1 != ''
                && $rowValue2 == ''
                && $rowValue3 == ''
                && $rowValue4 != ''
                && $rowValue5 == '') {
                    continue;
                }
                if ($rowValue1 != ''
                && $rowValue2 == ''
                && $rowValue3 == ''
                && $rowValue4 == ''
                && $rowValue5 == ''
                && $rowValue7 != ''
                && $rowValue8 != '') {
                    continue;
                }
                if ($rowValue1 == ''
                && $rowValue2 == ''
                && $rowValue3 == ''
                && $rowValue4 == ''
                && $rowValue5 == ''
                && $rowValue7 != ''
                && $rowValue8 != '') {
                    continue;
                }
                if ($rowValue1 != ''
                && $rowValue2 != ''
                && $rowValue3 == ''
                && $rowValue4 == ''
                && $rowValue5 == '') {
                    continue;
                }
                if ($rowValue1 == ''
                && $rowValue2 != ''
                && $rowValue3 != ''
                && $rowValue4 == ''
                && $rowValue5 == '') {
                    continue;
                }
                if ((in_array(strtolower('LockneticsElectricStrikesandElectromagneticLocks'), $rowstrtolower) 
                    || in_array(strtolower('LockneticsElectricStrikesandElectromagneticLocksandaccessories'), $rowstrtolower)
                    && (in_array('ListPrice', $rowstrtolower) 
                    || in_array('ListPriceJuly2018', $rowstrtolower))
                    && (in_array('AllegionPartner', $rowstrtolower) 
                    || in_array('AllegionPartnerExampleRecommendedNetPrice:', $rowstrtolower)))) {
                    $sheetTable = 'Locknetics 1';
                    $sheetTableRow = $rowValue;
                    continue;
                }

                if (in_array('productname', $rowstrtolower)
                    && in_array('productdescription', $rowstrtolower)
                    && in_array('upc', $rowstrtolower)
                    && (in_array('listprice', $rowstrtolower))) {
                    $sheetTable = 'Locknetics 2';
                    $sheetTableRow = $rowValue;
                    continue;
                }
                // validate mapped colulmn
                if ($sheetTable == '' && $worksheetKey+1 == count($worksheets)) {
                    $errorMessage = '<small>Selected vendor doesn\'t support spreadsheet columns. Sheet Name <strong>"'. $worksheet .'"</strong>.</small><small><ul><li>Make sure spreadsheet belong to selected vendor.</li><li>Make sure spreadsheet mpn column is set in column mapping setting.</li></ul></small>';
                    // $errorMessage = 'Products column is not supported in <em><strong>"'. $worksheet .'"</strong></em> sheet.';
                    throw new Exception($errorMessage);
                    /* Dev::pv($errorMessage);
                    break; */
                }
                // change worksheet
                if ($sheetTable == '') {
                    continue;
                }
                $sheetRowIndexCode = ZCommon::cleanString($worksheetKey .'-'. $worksheet) .'-'. $rowIndex;

                if (isset($options['rawData']) && $options['rawData'] === true) {
                    $rawDataTmp = [];

                    foreach ($sheetTableRow as $sheetTableRowKey => $sheetTableRowVal) {
                        if (isset($rowValue[$sheetTableRowKey])) {
                            $rawDataTmp[ZCommon::cleanString($sheetTableRowVal)] = $rowValue[$sheetTableRowKey];
                        }
                    }

                    $rawData[$sheetRowIndexCode] = $rawDataTmp;

                    continue;
                }

                // set categories
                $sheetCategories[] = Vendor::getVendorNameLabel(Vendor::INDEX_LOCKNETICS);
                $sheetCategories[] = $worksheet;
                
                // set sheets columns and sheets data
                $sheetsData[$worksheet][$sheetTable][] = [
                    'sheet_col' => $sheetTableRow,
                    'sheet_data' => $rowValue,
                    'sheet_row_index' => $rowIndex,
                    'sheet_row_index_code' => $sheetRowIndexCode,
                    'sheet_row_categories' => json_encode($sheetCategories)
                ];
            }
        }
       // Dev::pvx($sheetsData);
        // return raw data
        if (isset($options['rawData']) && $options['rawData'] === true) {
            return $rawData;
        }

        // retrive column mapping setting data
        $settingColumnMapping = SettingColumnMapping::model()->find()->attributes;

        // remove column unecessary mapping data
        unset($settingColumnMapping['id']);
        unset($settingColumnMapping['status']);
        unset($settingColumnMapping['date_created']);
        unset($settingColumnMapping['date_updated']);

        $settingColumnMappingValues = [];

        // prepare mapping column setting, retrieve
        foreach ($settingColumnMapping as $settingColumnMappingkey => $settingColumnMappingData) {
            $settingColumnMappingData = strtolower($settingColumnMappingData);
            $columnMapData =  array_map('ZCommon::cleanString', explode(',', $settingColumnMappingData));
            $settingColumnMappingValues[$settingColumnMappingkey] = $columnMapData;
        }
        
        $sheetParsedData = [];
        // set columns mapping and values
        foreach ($sheetsData as $sheetsDataKey => $sheetsDataVal) {
            foreach ($sheetsDataVal as $sheetsDataTableKey => $sheetsDataTableVal) {
                foreach ($sheetsDataTableVal as $sheetsDataTableItemKey => $sheetsDataTableItemVal) {
                    $sheetData = $sheetsDataTableItemVal['sheet_data'];
                    $sheetCol = $sheetsDataTableItemVal['sheet_col'];
                    $sheetRowIndex = $sheetsDataTableItemVal['sheet_row_index'];
                    $sheetRowIndexCode = $sheetsDataTableItemVal['sheet_row_index_code'];
                    $sheetCategories = $sheetsDataTableItemVal['sheet_row_categories'];

                    $sheetColumnWithData = [];

                    foreach ($sheetCol as $sheetColumnKey => $sheetColumnVal) {
                        $sheetColumnVal = ZCommon::cleanString($sheetColumnVal);

                        $columnHasMappingData = false;

                        foreach ($settingColumnMappingValues as $settingColumnMappingKey => $settingColumnMappingVal) {
                            if (in_array(strtolower($sheetColumnVal), $settingColumnMappingVal)) {
                                $sheetColumnVal = $settingColumnMappingKey;
                                $columnHasMappingData = true;
                                continue;
                            }
                        }
                        $sheetColumnValRaw = '';
                        if (!$columnHasMappingData) {
                            $sheetColumnValRaw = $sheetColumnVal;
                            $sheetColumnVal = '__' . $sheetColumnValRaw;
                        }

                        $sheetColumnWithData[$sheetColumnVal] = isset($sheetData[$sheetColumnKey]) ? preg_replace("/[\n\r]/", " ", $sheetData[$sheetColumnKey]) : 'not set';
                    }
                    
                    $sheetParsedData[] = $sheetColumnWithData + [
                        'sheets_name' => $sheetsDataKey,
                        'sheets_row_index' => $sheetRowIndex,
                        'sheet_row_index_code' => $sheetRowIndexCode,
                        'sheets_col' => $sheetColumnValRaw,
                        'categories' => $sheetCategories];
                }
            }
        }
        // format sheet data
        foreach ($sheetParsedData as $sheetParsedDataKey => $sheetParsedDataVal) {
            $tmpData = [];
            
            foreach ($sheetParsedDataVal as $sheetRowDatakey => $sheetRowDataVal) {
                $tmpData[$sheetRowDatakey] = $sheetRowDataVal;
            }
            $parsedData[] = $tmpData;
        }
       // Dev::pvx($parsedData);
        // validate data mpn
        foreach ($parsedData as $parsedDataKey => $parsedDataValue) {
            $errorSheetName = '';
            $errorSheetRowIndex = '';

            if (!in_array('mpn', array_keys($parsedDataValue))) {
                $errorSheetName = $parsedDataValue['sheets_name'];

                $errorMessage = 'Cannot select spreadsheet mpn, make sure mpn is set in column mapping setting. Sheet Name <strong>"'. $errorSheetName .'"</strong>';

                throw new Exception($errorMessage);
                /* Dev::pv($errorMessage);
                break; */
            }
        }

        return $parsedData;
    }

    protected function _parsedSpreedSheetDigitalWatchDog($spreedSheetParser, $options = [])
    {
        $parsedData = [];
        $sheetsData = [];
        $rawData = [];

        // get shees
        $worksheets = $spreedSheetParser->getWorksheets();
        $lastWorksheet = end($worksheets);
        $hadData = false;
        //set sheets data
        foreach ($worksheets as $worksheetKey => $worksheet) {
            
            //worksheet target

            // get sheet sheets rows data
            $myWorksheetIndex = $spreedSheetParser->getWorksheetIndex($worksheet);
            $rows = $spreedSheetParser->createRowIterator($myWorksheetIndex);
            // validate sheet data
            if (!$rows) {
                throw new Exception('Spreadsheet has empty content.');
                // break;
            }
            $sheetTable = '';
            $sheetTableRow = '';
            // Dev::pvx($rows);
            // get and set sheet column
            foreach ($rows as $rowIndex => $rowValue) {
                $sheetCategories = [];
                
                // collect spreedsheet rows
                $rowValue1 = isset($rowValue[0]) ? ZCommon::cleanString($rowValue[0]) : '';
                $rowValue2 = isset($rowValue[1]) ? ZCommon::cleanString($rowValue[1]) : '';
                $rowValue3 = isset($rowValue[2]) ? ZCommon::cleanString($rowValue[2]) : '';
                $rowValue4 = isset($rowValue[3]) ? ZCommon::cleanString($rowValue[3]) : '';
                $rowValue5 = isset($rowValue[4]) ? ZCommon::cleanString($rowValue[4]) : '';
                $rowValue6 = isset($rowValue[5]) ? ZCommon::cleanString($rowValue[5]) : '';
                $rowValue7 = isset($rowValue[6]) ? ZCommon::cleanString($rowValue[6]) : '';
                $rowValue8 = isset($rowValue[7]) ? ZCommon::cleanString($rowValue[7]) : '';
                $rowValue9 = isset($rowValue[8]) ? ZCommon::cleanString($rowValue[8]) : '';
                $rowValue10 = isset($rowValue[9]) ? ZCommon::cleanString($rowValue[9]) : '';
                $rowValue11 = isset($rowValue[10]) ? ZCommon::cleanString($rowValue[10]) : '';
                $rowValue12 = isset($rowValue[11]) ? ZCommon::cleanString($rowValue[11]) : '';
                $rowValue13 = isset($rowValue[12]) ? ZCommon::cleanString($rowValue[12]) : '';
                $rowValue14 = isset($rowValue[13]) ? ZCommon::cleanString($rowValue[13]) : '';
                $rowValue15 = isset($rowValue[14]) ? ZCommon::cleanString($rowValue[14]) : '';
                $rowTrim = ZCommon::cleanArray($rowValue);
                $rowstrtolower = array_map('strtolower',$rowTrim);
                //skip unimportant row
                if ($rowValue1 != ''
                && $rowValue2 == ''
                && $rowValue3 == ''
                && $rowValue4 == ''
                && $rowValue5 == ''
                && $rowValue6 == '') {
                    continue;
                }
                if ($rowValue1 == ''
                && $rowValue2 == ''
                && $rowValue3 == ''
                && $rowValue4 == ''
                && $rowValue5 == ''
                && $rowValue6 != '') {
                    continue;
                }

                if (in_array(strtolower('Model'), $rowstrtolower) 
                    && in_array(strtolower('Description'), $rowstrtolower) 
                    && in_array(strtolower('Msrp'), $rowstrtolower) 
                    && (in_array(strtolower('0'), $rowstrtolower) || in_array(strtolower('0%'), $rowstrtolower))) {
                    $sheetTable = 'New Product';
                    $sheetTableRow = $rowValue;
                    $hadData = true;
                    continue;
                }
                
                if (in_array(strtolower('Model'), $rowstrtolower) 
                    && in_array(strtolower('Description'), $rowstrtolower) 
                    && in_array(strtolower('Msrp'), $rowstrtolower) 
                    && (in_array(strtolower('0.5'), $rowstrtolower)  || in_array(strtolower('50%'), $rowstrtolower))) {
                    $sheetTable = 'Complete Price List';
                    $sheetTableRow = $rowValue;
                    $hadData = true;
                    continue;
                }
                
                if (in_array(strtolower('Model'), $rowstrtolower) 
                    && in_array(strtolower('Description'), $rowstrtolower) 
                    && in_array(strtolower('Msrp'), $rowstrtolower) 
                    && (in_array(strtolower('cost'), $rowstrtolower))) {
                    $sheetTable = 'New Product (2)';
                    $sheetTableRow = $rowValue;
                    $hadData = true;
                    // Dev::pvx($rowValue);
                    continue;
                }

                // validate mapped colulmn
                if ($sheetTable == '' && $worksheet == $lastWorksheet && !$hadData) {
                    $errorMessage = '<small>Selected vendor doesn\'t support spreadsheet columns.</small><small><ul><li>Make sure spreadsheet belong to selected vendor.</li><li>Make sure spreadsheet column is set in column mapping setting.</li></ul></small>';
                    // $errorMessage = 'Products column is not supported in <em><strong>"'. $worksheet .'"</strong></em> sheet.';
                    throw new Exception($errorMessage);
                    // Dev::pv($errorMessage);
                }
                // change worksheet
                if ($sheetTable == '') {
                    continue;
                }
                $sheetRowIndexCode = ZCommon::cleanString($worksheetKey .'-'. $worksheet) .'-'. $rowIndex;

                if (isset($options['rawData']) && $options['rawData'] === true) {
                    $rawDataTmp = [];

                    foreach ($sheetTableRow as $sheetTableRowKey => $sheetTableRowVal) {
                        if (isset($rowValue[$sheetTableRowKey])) {
                            $rawDataTmp[ZCommon::cleanString($sheetTableRowVal)] = $rowValue[$sheetTableRowKey];
                        }
                    }

                    $rawData[$sheetRowIndexCode] = $rawDataTmp;

                    continue;
                }

                // set categories
                $sheetCategories[] = Vendor::getVendorNameLabel(Vendor::INDEX_DIGITAL_WATCHDOG);
                $sheetCategories[] = $worksheet;
                
                // set sheets columns and sheets data
                $sheetsData[$worksheet][$sheetTable][] = [
                    'sheet_col' => $sheetTableRow,
                    'sheet_data' => $rowValue,
                    'sheet_row_index' => $rowIndex,
                    'sheet_row_index_code' => $sheetRowIndexCode,
                    'sheet_row_categories' => json_encode($sheetCategories)
                ];
            }
        }
       // Dev::pvx($sheetsData);
        // return raw data
        if (isset($options['rawData']) && $options['rawData'] === true) {
            return $rawData;
        }

        // retrive column mapping setting data
        $settingColumnMapping = SettingColumnMapping::model()->find()->attributes;

        // remove column unecessary mapping data
        unset($settingColumnMapping['id']);
        unset($settingColumnMapping['status']);
        unset($settingColumnMapping['date_created']);
        unset($settingColumnMapping['date_updated']);

        $settingColumnMappingValues = [];

        // prepare mapping column setting, retrieve
        foreach ($settingColumnMapping as $settingColumnMappingkey => $settingColumnMappingData) {
            $settingColumnMappingData = strtolower($settingColumnMappingData);
            $columnMapData =  array_map('ZCommon::cleanString', explode(',', $settingColumnMappingData));
            $settingColumnMappingValues[$settingColumnMappingkey] = $columnMapData;
        }
        
        $sheetParsedData = [];
        // set columns mapping and values
        foreach ($sheetsData as $sheetsDataKey => $sheetsDataVal) {
            foreach ($sheetsDataVal as $sheetsDataTableKey => $sheetsDataTableVal) {
                foreach ($sheetsDataTableVal as $sheetsDataTableItemKey => $sheetsDataTableItemVal) {
                    $sheetData = $sheetsDataTableItemVal['sheet_data'];
                    $sheetCol = $sheetsDataTableItemVal['sheet_col'];
                    $sheetRowIndex = $sheetsDataTableItemVal['sheet_row_index'];
                    $sheetRowIndexCode = $sheetsDataTableItemVal['sheet_row_index_code'];
                    $sheetCategories = $sheetsDataTableItemVal['sheet_row_categories'];

                    $sheetColumnWithData = [];

                    foreach ($sheetCol as $sheetColumnKey => $sheetColumnVal) {
                        $sheetColumnVal = ZCommon::cleanString($sheetColumnVal);

                        $columnHasMappingData = false;

                        foreach ($settingColumnMappingValues as $settingColumnMappingKey => $settingColumnMappingVal) {
                            if (in_array(strtolower($sheetColumnVal), $settingColumnMappingVal)) {
                                $sheetColumnVal = $settingColumnMappingKey;
                                $columnHasMappingData = true;
                                continue;
                            }
                        }
                        $sheetColumnValRaw = '';
                        if (!$columnHasMappingData) {
                            $sheetColumnValRaw = $sheetColumnVal;
                            $sheetColumnVal = '__' . $sheetColumnValRaw;
                        }

                        $sheetColumnWithData[$sheetColumnVal] = isset($sheetData[$sheetColumnKey]) ? preg_replace("/[\n\r]/", " ", $sheetData[$sheetColumnKey]) : 'not set';
                    }
                    
                    $sheetParsedData[] = $sheetColumnWithData + [
                        'sheets_name' => $sheetsDataKey,
                        'sheets_row_index' => $sheetRowIndex,
                        'sheet_row_index_code' => $sheetRowIndexCode,
                        'sheets_col' => $sheetColumnValRaw,
                        'categories' => $sheetCategories];
                }
            }
        }
        // format sheet data
        foreach ($sheetParsedData as $sheetParsedDataKey => $sheetParsedDataVal) {
            $tmpData = [];
            
            foreach ($sheetParsedDataVal as $sheetRowDatakey => $sheetRowDataVal) {
                $tmpData[$sheetRowDatakey] = $sheetRowDataVal;
            }
            $parsedData[] = $tmpData;
        }
       // Dev::pvx($parsedData);
        // validate data mpn
        foreach ($parsedData as $parsedDataKey => $parsedDataValue) {
            $errorSheetName = '';
            $errorSheetRowIndex = '';

            if (!in_array('mpn', array_keys($parsedDataValue))) {
                $errorSheetName = $parsedDataValue['sheets_name'];

                $errorMessage = 'Cannot select spreadsheet mpn, make sure mpn is set in column mapping setting. Sheet Name <strong>"'. $errorSheetName .'"</strong>';

                throw new Exception($errorMessage);
                /* Dev::pv($errorMessage);
                break; */
            }
        }

        if(empty($parsedData)){
        	$errorMessage = 'No Existing Data!';

            throw new Exception($errorMessage);
            /* Dev::pv($errorMessage);
            break; */
        }
        // Dev::pvx($parsedData);
        return $parsedData;
    }

    protected function _parsedSpreedSheetSchlage($spreedSheetParser, $options = [])
    {
        $parsedData = [];
        $sheetsData = [];
        $rawData = [];

        // get shees
        $worksheets = $spreedSheetParser->getWorksheets();
        $lastWorksheet = end($worksheets);
        $hadData = false;
        //set sheets data
        foreach ($worksheets as $worksheetKey => $worksheet) {
            
            //worksheet target

            // get sheet sheets rows data
            $myWorksheetIndex = $spreedSheetParser->getWorksheetIndex($worksheet);
            $rows = $spreedSheetParser->createRowIterator($myWorksheetIndex);
            // validate sheet data
            if (!$rows) {
                // break;
                throw new Exception('Spreadsheet has empty content.');
            }
            $sheetTable = '';
            $sheetTableRow = '';
            // Dev::pvx($rows);
            // get and set sheet column
            foreach ($rows as $rowIndex => $rowValue) {
                $sheetCategories = [];
                
                // collect spreedsheet rows
                $rowValue1 = isset($rowValue[0]) ? ZCommon::cleanString($rowValue[0]) : '';
                $rowValue2 = isset($rowValue[1]) ? ZCommon::cleanString($rowValue[1]) : '';
                $rowValue3 = isset($rowValue[2]) ? ZCommon::cleanString($rowValue[2]) : '';
                $rowValue4 = isset($rowValue[3]) ? ZCommon::cleanString($rowValue[3]) : '';
                $rowValue5 = isset($rowValue[4]) ? ZCommon::cleanString($rowValue[4]) : '';
                $rowValue6 = isset($rowValue[5]) ? ZCommon::cleanString($rowValue[5]) : '';
                $rowValue7 = isset($rowValue[6]) ? ZCommon::cleanString($rowValue[6]) : '';
                $rowValue8 = isset($rowValue[7]) ? ZCommon::cleanString($rowValue[7]) : '';
                $rowValue9 = isset($rowValue[8]) ? ZCommon::cleanString($rowValue[8]) : '';
                $rowValue10 = isset($rowValue[9]) ? ZCommon::cleanString($rowValue[9]) : '';
                $rowValue11 = isset($rowValue[10]) ? ZCommon::cleanString($rowValue[10]) : '';
                $rowValue12 = isset($rowValue[11]) ? ZCommon::cleanString($rowValue[11]) : '';
                $rowValue13 = isset($rowValue[12]) ? ZCommon::cleanString($rowValue[12]) : '';
                $rowValue14 = isset($rowValue[13]) ? ZCommon::cleanString($rowValue[13]) : '';
                $rowValue15 = isset($rowValue[14]) ? ZCommon::cleanString($rowValue[14]) : '';
                $rowTrim = ZCommon::cleanArray($rowValue);
                $rowstrtolower = array_map('strtolower',$rowTrim);
                //skip unimportant row
                if ($rowValue1 != ''
                && $rowValue2 == ''
                && $rowValue3 == ''
                && $rowValue4 == ''
                && $rowValue5 == ''
                && $rowValue6 == '') {
                    continue;
                }
                if ($rowValue1 == ''
                && $rowValue2 == ''
                && $rowValue3 == ''
                && $rowValue4 == ''
                && $rowValue5 == ''
                && $rowValue6 != '') {
                    continue;
                }

                if (in_array(strtolower('Mpn'), $rowstrtolower) 
                    && in_array(strtolower('Description'), $rowstrtolower) 
                    && in_array(strtolower('Msrp'), $rowstrtolower) 
                    && (in_array(strtolower('Cost'), $rowstrtolower) || in_array(strtolower('0%'), $rowstrtolower))) {
                    $sheetTable = 'Dist';
                    $sheetTableRow = $rowValue;
                    $hadData = true;
                    // Dev::pvx($rowValue);
                    continue;
                }

                // validate mapped colulmn
                if ($sheetTable == '' && $worksheet == $lastWorksheet && !$hadData) {
                    $errorMessage = '<small>Selected vendor doesn\'t support spreadsheet columns.</small><small><ul><li>Make sure spreadsheet belong to selected vendor.</li><li>Make sure spreadsheet column is set in column mapping setting.</li></ul></small>';
                    // $errorMessage = 'Products column is not supported in <em><strong>"'. $worksheet .'"</strong></em> sheet.';
                    throw new Exception($errorMessage);
                    /* Dev::pv($errorMessage);
                    break; */
                }
                // change worksheet
                if ($sheetTable == '') {
                    continue;
                }
                $sheetRowIndexCode = ZCommon::cleanString($worksheetKey .'-'. $worksheet) .'-'. $rowIndex;

                if (isset($options['rawData']) && $options['rawData'] === true) {
                    $rawDataTmp = [];

                    foreach ($sheetTableRow as $sheetTableRowKey => $sheetTableRowVal) {
                        if (isset($rowValue[$sheetTableRowKey])) {
                            $rawDataTmp[ZCommon::cleanString($sheetTableRowVal)] = $rowValue[$sheetTableRowKey];
                        }
                    }

                    $rawData[$sheetRowIndexCode] = $rawDataTmp;

                    continue;
                }

                // set categories
                $sheetCategories[] = Vendor::getVendorNameLabel(Vendor::INDEX_SCHLAGE);
                $sheetCategories[] = $worksheet;
                
                // set sheets columns and sheets data
                $sheetsData[$worksheet][$sheetTable][] = [
                    'sheet_col' => $sheetTableRow,
                    'sheet_data' => $rowValue,
                    'sheet_row_index' => $rowIndex,
                    'sheet_row_index_code' => $sheetRowIndexCode,
                    'sheet_row_categories' => json_encode($sheetCategories)
                ];
            }
        }
       // Dev::pvx($sheetsData);
        // return raw data
        if (isset($options['rawData']) && $options['rawData'] === true) {
            return $rawData;
        }

        // retrive column mapping setting data
        $settingColumnMapping = SettingColumnMapping::model()->find()->attributes;

        // remove column unecessary mapping data
        unset($settingColumnMapping['id']);
        unset($settingColumnMapping['status']);
        unset($settingColumnMapping['date_created']);
        unset($settingColumnMapping['date_updated']);

        $settingColumnMappingValues = [];

        // prepare mapping column setting, retrieve
        foreach ($settingColumnMapping as $settingColumnMappingkey => $settingColumnMappingData) {
            $settingColumnMappingData = strtolower($settingColumnMappingData);
            $columnMapData =  array_map('ZCommon::cleanString', explode(',', $settingColumnMappingData));
            $settingColumnMappingValues[$settingColumnMappingkey] = $columnMapData;
        }
        
        $sheetParsedData = [];
        // set columns mapping and values
        foreach ($sheetsData as $sheetsDataKey => $sheetsDataVal) {
            foreach ($sheetsDataVal as $sheetsDataTableKey => $sheetsDataTableVal) {
                foreach ($sheetsDataTableVal as $sheetsDataTableItemKey => $sheetsDataTableItemVal) {
                    $sheetData = $sheetsDataTableItemVal['sheet_data'];
                    $sheetCol = $sheetsDataTableItemVal['sheet_col'];
                    $sheetRowIndex = $sheetsDataTableItemVal['sheet_row_index'];
                    $sheetRowIndexCode = $sheetsDataTableItemVal['sheet_row_index_code'];
                    $sheetCategories = $sheetsDataTableItemVal['sheet_row_categories'];

                    $sheetColumnWithData = [];

                    foreach ($sheetCol as $sheetColumnKey => $sheetColumnVal) {
                        $sheetColumnVal = ZCommon::cleanString($sheetColumnVal);

                        $columnHasMappingData = false;

                        foreach ($settingColumnMappingValues as $settingColumnMappingKey => $settingColumnMappingVal) {
                            if (in_array(strtolower($sheetColumnVal), $settingColumnMappingVal)) {
                                $sheetColumnVal = $settingColumnMappingKey;
                                $columnHasMappingData = true;
                                continue;
                            }
                        }
                        $sheetColumnValRaw = '';
                        if (!$columnHasMappingData) {
                            $sheetColumnValRaw = $sheetColumnVal;
                            $sheetColumnVal = '__' . $sheetColumnValRaw;
                        }

                        $sheetColumnWithData[$sheetColumnVal] = isset($sheetData[$sheetColumnKey]) ? preg_replace("/[\n\r]/", " ", $sheetData[$sheetColumnKey]) : 'not set';
                    }
                    
                    $sheetParsedData[] = $sheetColumnWithData + [
                        'sheets_name' => $sheetsDataKey,
                        'sheets_row_index' => $sheetRowIndex,
                        'sheet_row_index_code' => $sheetRowIndexCode,
                        'sheets_col' => $sheetColumnValRaw,
                        'categories' => $sheetCategories];
                }
            }
        }
        // format sheet data
        foreach ($sheetParsedData as $sheetParsedDataKey => $sheetParsedDataVal) {
            $tmpData = [];
            
            foreach ($sheetParsedDataVal as $sheetRowDatakey => $sheetRowDataVal) {
                $tmpData[$sheetRowDatakey] = $sheetRowDataVal;
            }
            $parsedData[] = $tmpData;
        }
       // Dev::pvx($parsedData);
        // validate data mpn
        foreach ($parsedData as $parsedDataKey => $parsedDataValue) {
            $errorSheetName = '';
            $errorSheetRowIndex = '';

            if (!in_array('mpn', array_keys($parsedDataValue))) {
                $errorSheetName = $parsedDataValue['sheets_name'];

                $errorMessage = 'Cannot select spreadsheet mpn, make sure mpn is set in column mapping setting. Sheet Name <strong>"'. $errorSheetName .'"</strong>';

                throw new Exception($errorMessage);
                // break;
            }
        }

        if(empty($parsedData)){
        	$errorMessage = 'No Existing Data!';

            throw new Exception($errorMessage);
           /*  Dev::pv($errorMessage);
            break; */
        }
        // Dev::pvx($parsedData);
        return $parsedData;
    }

    protected function _parsedSpreedSheetUsaVision($spreedSheetParser, $options = [])
    {
        $parsedData = [];
        $sheetsData = [];
        $rawData = [];

        // get shees
        $worksheets = $spreedSheetParser->getWorksheets();
        $lastWorksheet = end($worksheets);
        $hadData = false;
        //set sheets data
        foreach ($worksheets as $worksheetKey => $worksheet) {
            
            //worksheet target

            // get sheet sheets rows data
            $myWorksheetIndex = $spreedSheetParser->getWorksheetIndex($worksheet);
            $rows = $spreedSheetParser->createRowIterator($myWorksheetIndex);
            // validate sheet data
            if (!$rows) {
                // break;
                throw new Exception('Spreadsheet has empty content.');
            }
            $sheetTable = '';
            $sheetTableRow = '';
            // Dev::pvx($rows);
            // get and set sheet column
            foreach ($rows as $rowIndex => $rowValue) {
                $sheetCategories = [];
                
                // collect spreedsheet rows
                $rowValue1 = isset($rowValue[0]) ? ZCommon::cleanString($rowValue[0]) : '';
                $rowValue2 = isset($rowValue[1]) ? ZCommon::cleanString($rowValue[1]) : '';
                $rowValue3 = isset($rowValue[2]) ? ZCommon::cleanString($rowValue[2]) : '';
                $rowValue4 = isset($rowValue[3]) ? ZCommon::cleanString($rowValue[3]) : '';
                $rowValue5 = isset($rowValue[4]) ? ZCommon::cleanString($rowValue[4]) : '';
                $rowValue6 = isset($rowValue[5]) ? ZCommon::cleanString($rowValue[5]) : '';
                $rowValue7 = isset($rowValue[6]) ? ZCommon::cleanString($rowValue[6]) : '';
                $rowValue8 = isset($rowValue[7]) ? ZCommon::cleanString($rowValue[7]) : '';
                $rowValue9 = isset($rowValue[8]) ? ZCommon::cleanString($rowValue[8]) : '';
                $rowValue10 = isset($rowValue[9]) ? ZCommon::cleanString($rowValue[9]) : '';
                $rowValue11 = isset($rowValue[10]) ? ZCommon::cleanString($rowValue[10]) : '';
                $rowValue12 = isset($rowValue[11]) ? ZCommon::cleanString($rowValue[11]) : '';
                $rowValue13 = isset($rowValue[12]) ? ZCommon::cleanString($rowValue[12]) : '';
                $rowValue14 = isset($rowValue[13]) ? ZCommon::cleanString($rowValue[13]) : '';
                $rowValue15 = isset($rowValue[14]) ? ZCommon::cleanString($rowValue[14]) : '';
                $rowTrim = ZCommon::cleanArray($rowValue);
                $rowstrtolower = array_map('strtolower',$rowTrim);
                //skip unimportant row
                if ($rowValue1 != ''
                && $rowValue2 == ''
                && $rowValue3 == ''
                && $rowValue4 == ''
                && $rowValue5 == ''
                && $rowValue6 == '') {
                    continue;
                }
                if ($rowValue1 == ''
                && $rowValue2 == ''
                && $rowValue3 == ''
                && $rowValue4 == ''
                && $rowValue5 == ''
                && $rowValue6 != '') {
                    continue;
                }

                if (in_array(strtolower('ModelNo.'), $rowstrtolower) 
                    && in_array(strtolower('MSRP'), $rowstrtolower) 
                    && in_array(strtolower('VIP-5%'), $rowstrtolower) 
                    && (in_array(strtolower('VIP-10%'), $rowstrtolower) )) {
                    $sheetTable = 'Product';
                    $sheetTableRow = $rowValue;
                    $hadData = true;
                    // Dev::pvx($rowValue);
                    continue;
                }

                // validate mapped colulmn
                if ($sheetTable == '' && $worksheet == $lastWorksheet && !$hadData) {
                    $errorMessage = '<small>Selected vendor doesn\'t support spreadsheet columns.</small><small><ul><li>Make sure spreadsheet belong to selected vendor.</li><li>Make sure spreadsheet column is set in column mapping setting.</li></ul></small>';
                    // $errorMessage = 'Products column is not supported in <em><strong>"'. $worksheet .'"</strong></em> sheet.';
                    throw new Exception($errorMessage);
                    /* Dev::pv($errorMessage);
                    break; */
                }
                // change worksheet
                if ($sheetTable == '') {
                    continue;
                }
                $sheetRowIndexCode = ZCommon::cleanString($worksheetKey .'-'. $worksheet) .'-'. $rowIndex;

                if (isset($options['rawData']) && $options['rawData'] === true) {
                    $rawDataTmp = [];

                    foreach ($sheetTableRow as $sheetTableRowKey => $sheetTableRowVal) {
                        if (isset($rowValue[$sheetTableRowKey])) {
                            $rawDataTmp[ZCommon::cleanString($sheetTableRowVal)] = $rowValue[$sheetTableRowKey];
                        }
                    }

                    $rawData[$sheetRowIndexCode] = $rawDataTmp;

                    continue;
                }

                // set categories
                $sheetCategories[] = Vendor::getVendorNameLabel(Vendor::INDEX_USA_VISION);
                $sheetCategories[] = $worksheet;
                
                // set sheets columns and sheets data
                $sheetsData[$worksheet][$sheetTable][] = [
                    'sheet_col' => $sheetTableRow,
                    'sheet_data' => $rowValue,
                    'sheet_row_index' => $rowIndex,
                    'sheet_row_index_code' => $sheetRowIndexCode,
                    'sheet_row_categories' => json_encode($sheetCategories)
                ];
            }
        }
       // Dev::pvx($sheetsData);
        // return raw data
        if (isset($options['rawData']) && $options['rawData'] === true) {
            return $rawData;
        }

        // retrive column mapping setting data
        $settingColumnMapping = SettingColumnMapping::model()->find()->attributes;

        // remove column unecessary mapping data
        unset($settingColumnMapping['id']);
        unset($settingColumnMapping['status']);
        unset($settingColumnMapping['date_created']);
        unset($settingColumnMapping['date_updated']);

        $settingColumnMappingValues = [];

        // prepare mapping column setting, retrieve
        foreach ($settingColumnMapping as $settingColumnMappingkey => $settingColumnMappingData) {
            $settingColumnMappingData = strtolower($settingColumnMappingData);
            $columnMapData =  array_map('ZCommon::cleanString', explode(',', $settingColumnMappingData));
            $settingColumnMappingValues[$settingColumnMappingkey] = $columnMapData;
        }
        
        $sheetParsedData = [];
        // set columns mapping and values
        foreach ($sheetsData as $sheetsDataKey => $sheetsDataVal) {
            foreach ($sheetsDataVal as $sheetsDataTableKey => $sheetsDataTableVal) {
                foreach ($sheetsDataTableVal as $sheetsDataTableItemKey => $sheetsDataTableItemVal) {
                    $sheetData = $sheetsDataTableItemVal['sheet_data'];
                    $sheetCol = $sheetsDataTableItemVal['sheet_col'];
                    $sheetRowIndex = $sheetsDataTableItemVal['sheet_row_index'];
                    $sheetRowIndexCode = $sheetsDataTableItemVal['sheet_row_index_code'];
                    $sheetCategories = $sheetsDataTableItemVal['sheet_row_categories'];

                    $sheetColumnWithData = [];

                    foreach ($sheetCol as $sheetColumnKey => $sheetColumnVal) {
                        $sheetColumnVal = ZCommon::cleanString($sheetColumnVal);

                        $columnHasMappingData = false;

                        foreach ($settingColumnMappingValues as $settingColumnMappingKey => $settingColumnMappingVal) {
                            if (in_array(strtolower($sheetColumnVal), $settingColumnMappingVal)) {
                                $sheetColumnVal = $settingColumnMappingKey;
                                $columnHasMappingData = true;
                                continue;
                            }
                        }
                        $sheetColumnValRaw = '';
                        if (!$columnHasMappingData) {
                            $sheetColumnValRaw = $sheetColumnVal;
                            $sheetColumnVal = '__' . $sheetColumnValRaw;
                        }

                        $sheetColumnWithData[$sheetColumnVal] = isset($sheetData[$sheetColumnKey]) ? preg_replace("/[\n\r]/", " ", $sheetData[$sheetColumnKey]) : 'not set';
                    }
                    
                    $sheetParsedData[] = $sheetColumnWithData + [
                        'sheets_name' => $sheetsDataKey,
                        'sheets_row_index' => $sheetRowIndex,
                        'sheet_row_index_code' => $sheetRowIndexCode,
                        'sheets_col' => $sheetColumnValRaw,
                        'categories' => $sheetCategories];
                }
            }
        }
        // format sheet data
        foreach ($sheetParsedData as $sheetParsedDataKey => $sheetParsedDataVal) {
            $tmpData = [];
            
            foreach ($sheetParsedDataVal as $sheetRowDatakey => $sheetRowDataVal) {
                $tmpData[$sheetRowDatakey] = $sheetRowDataVal;
            }
            $parsedData[] = $tmpData;
        }
       // Dev::pvx($parsedData);
        // validate data mpn
        foreach ($parsedData as $parsedDataKey => $parsedDataValue) {
            $errorSheetName = '';
            $errorSheetRowIndex = '';

            if (!in_array('mpn', array_keys($parsedDataValue))) {
                $errorSheetName = $parsedDataValue['sheets_name'];

                $errorMessage = 'Cannot select spreadsheet mpn, make sure mpn is set in column mapping setting. Sheet Name <strong>"'. $errorSheetName .'"</strong>';

                throw new Exception($errorMessage);
                // break;
            }
        }

        if(empty($parsedData)){
        	$errorMessage = 'No Existing Data!';

            throw new Exception($errorMessage);
           /*  Dev::pv($errorMessage);
            break; */
        }
        // Dev::pvx($parsedData);
        return $parsedData;
    }

    protected function _parsedSpreedSheetAxton($spreedSheetParser, $options = [])
    {
        $parsedData = [];
        $sheetsData = [];
        $rawData = [];

        // get shees
        $worksheets = $spreedSheetParser->getWorksheets();
        $lastWorksheet = end($worksheets);
        $hadData = false;
        //set sheets data
        foreach ($worksheets as $worksheetKey => $worksheet) {
            
            //worksheet target

            // get sheet sheets rows data
            $myWorksheetIndex = $spreedSheetParser->getWorksheetIndex($worksheet);
            $rows = $spreedSheetParser->createRowIterator($myWorksheetIndex);
            // validate sheet data
            if (!$rows) {
                // break;
                throw new Exception('Spreadsheet has empty content.');
            }
            $sheetTable = '';
            $sheetTableRow = '';
            // Dev::pvx($rows);
            // get and set sheet column
            foreach ($rows as $rowIndex => $rowValue) {
                $sheetCategories = [];
                
                // collect spreedsheet rows
                $rowValue1 = isset($rowValue[0]) ? ZCommon::cleanString($rowValue[0]) : '';
                $rowValue2 = isset($rowValue[1]) ? ZCommon::cleanString($rowValue[1]) : '';
                $rowValue3 = isset($rowValue[2]) ? ZCommon::cleanString($rowValue[2]) : '';
                $rowValue4 = isset($rowValue[3]) ? ZCommon::cleanString($rowValue[3]) : '';
                $rowValue5 = isset($rowValue[4]) ? ZCommon::cleanString($rowValue[4]) : '';
                $rowValue6 = isset($rowValue[5]) ? ZCommon::cleanString($rowValue[5]) : '';
                $rowTrim = ZCommon::cleanArray($rowValue);
                $rowstrtolower = array_map('strtolower',$rowTrim);
                //skip unimportant row
                if ($rowValue1 != ''
                && $rowValue2 == ''
                && $rowValue3 == ''
                && $rowValue4 == ''
                && $rowValue5 == ''
                && $rowValue6 == '') {
                    continue;
                }
                if ($rowValue1 == ''
                && $rowValue2 == ''
                && $rowValue3 == ''
                && $rowValue4 == ''
                && $rowValue5 == ''
                && $rowValue6 != '') {
                    continue;
                }
                
                if (in_array(strtolower('PartNumber'), $rowstrtolower) 
                    && in_array(strtolower('Description'), $rowstrtolower) 
                    && in_array(strtolower('MSRP'), $rowstrtolower) 
                    && (in_array(strtolower('45%'), $rowstrtolower)  || in_array(strtolower('0.45'), $rowstrtolower) )) {
                    $sheetTable = 'Product';
                    $sheetTableRow = $rowValue;
                    $hadData = true;
                    // Dev::pvx($rowValue);
                    continue;
                }

                // validate mapped colulmn
                if ($sheetTable == '' && $worksheet == $lastWorksheet && !$hadData) {
                    $errorMessage = '<small>Selected vendor doesn\'t support spreadsheet columns.</small><small><ul><li>Make sure spreadsheet belong to selected vendor.</li><li>Make sure spreadsheet column is set in column mapping setting.</li></ul></small>';
                    // $errorMessage = 'Products column is not supported in <em><strong>"'. $worksheet .'"</strong></em> sheet.';
                    throw new Exception($errorMessage);
                    /* Dev::pv($errorMessage);
                    break; */
                }
                // change worksheet
                if ($sheetTable == '') {
                    continue;
                }
                $sheetRowIndexCode = ZCommon::cleanString($worksheetKey .'-'. $worksheet) .'-'. $rowIndex;

                if (isset($options['rawData']) && $options['rawData'] === true) {
                    $rawDataTmp = [];

                    foreach ($sheetTableRow as $sheetTableRowKey => $sheetTableRowVal) {
                        if (isset($rowValue[$sheetTableRowKey])) {
                            $rawDataTmp[ZCommon::cleanString($sheetTableRowVal)] = $rowValue[$sheetTableRowKey];
                        }
                    }

                    $rawData[$sheetRowIndexCode] = $rawDataTmp;

                    continue;
                }

                // set categories
                $sheetCategories[] = Vendor::getVendorNameLabel(Vendor::INDEX_AXTON);
                $sheetCategories[] = $worksheet;
                
                // set sheets columns and sheets data
                $sheetsData[$worksheet][$sheetTable][] = [
                    'sheet_col' => $sheetTableRow,
                    'sheet_data' => $rowValue,
                    'sheet_row_index' => $rowIndex,
                    'sheet_row_index_code' => $sheetRowIndexCode,
                    'sheet_row_categories' => json_encode($sheetCategories)
                ];
            }
        }
       // Dev::pvx($sheetsData);
        // return raw data
        if (isset($options['rawData']) && $options['rawData'] === true) {
            return $rawData;
        }

        // retrive column mapping setting data
        $settingColumnMapping = SettingColumnMapping::model()->find()->attributes;

        // remove column unecessary mapping data
        unset($settingColumnMapping['id']);
        unset($settingColumnMapping['status']);
        unset($settingColumnMapping['date_created']);
        unset($settingColumnMapping['date_updated']);

        $settingColumnMappingValues = [];

        // prepare mapping column setting, retrieve
        foreach ($settingColumnMapping as $settingColumnMappingkey => $settingColumnMappingData) {
            $settingColumnMappingData = strtolower($settingColumnMappingData);
            $columnMapData =  array_map('ZCommon::cleanString', explode(',', $settingColumnMappingData));
            $settingColumnMappingValues[$settingColumnMappingkey] = $columnMapData;
        }
        
        $sheetParsedData = [];
        // set columns mapping and values
        foreach ($sheetsData as $sheetsDataKey => $sheetsDataVal) {
            foreach ($sheetsDataVal as $sheetsDataTableKey => $sheetsDataTableVal) {
                foreach ($sheetsDataTableVal as $sheetsDataTableItemKey => $sheetsDataTableItemVal) {
                    $sheetData = $sheetsDataTableItemVal['sheet_data'];
                    $sheetCol = $sheetsDataTableItemVal['sheet_col'];
                    $sheetRowIndex = $sheetsDataTableItemVal['sheet_row_index'];
                    $sheetRowIndexCode = $sheetsDataTableItemVal['sheet_row_index_code'];
                    $sheetCategories = $sheetsDataTableItemVal['sheet_row_categories'];

                    $sheetColumnWithData = [];

                    foreach ($sheetCol as $sheetColumnKey => $sheetColumnVal) {
                        $sheetColumnVal = ZCommon::cleanString($sheetColumnVal);

                        $columnHasMappingData = false;

                        foreach ($settingColumnMappingValues as $settingColumnMappingKey => $settingColumnMappingVal) {
                            if (in_array(strtolower($sheetColumnVal), $settingColumnMappingVal)) {
                                $sheetColumnVal = $settingColumnMappingKey;
                                $columnHasMappingData = true;
                                continue;
                            }
                        }
                        $sheetColumnValRaw = '';
                        if (!$columnHasMappingData) {
                            $sheetColumnValRaw = $sheetColumnVal;
                            $sheetColumnVal = '__' . $sheetColumnValRaw;
                        }

                        $sheetColumnWithData[$sheetColumnVal] = isset($sheetData[$sheetColumnKey]) ? preg_replace("/[\n\r]/", " ", $sheetData[$sheetColumnKey]) : 'not set';
                    }
                    
                    $sheetParsedData[] = $sheetColumnWithData + [
                        'sheets_name' => $sheetsDataKey,
                        'sheets_row_index' => $sheetRowIndex,
                        'sheet_row_index_code' => $sheetRowIndexCode,
                        'sheets_col' => $sheetColumnValRaw,
                        'categories' => $sheetCategories];
                }
            }
        }
        // format sheet data
        foreach ($sheetParsedData as $sheetParsedDataKey => $sheetParsedDataVal) {
            $tmpData = [];
            
            foreach ($sheetParsedDataVal as $sheetRowDatakey => $sheetRowDataVal) {
                $tmpData[$sheetRowDatakey] = $sheetRowDataVal;
            }
            $parsedData[] = $tmpData;
        }
       // Dev::pvx($parsedData);
        // validate data mpn
        foreach ($parsedData as $parsedDataKey => $parsedDataValue) {
            $errorSheetName = '';
            $errorSheetRowIndex = '';

            if (!in_array('mpn', array_keys($parsedDataValue))) {
                $errorSheetName = $parsedDataValue['sheets_name'];

                $errorMessage = 'Cannot select spreadsheet mpn, make sure mpn is set in column mapping setting. Sheet Name <strong>"'. $errorSheetName .'"</strong>';

                throw new Exception($errorMessage);
                // break;
            }
        }

        if(empty($parsedData)){
        	$errorMessage = 'No Existing Data!';

            throw new Exception($errorMessage);
           /*  Dev::pv($errorMessage);
            break; */
        }
        // Dev::pvx($parsedData);
        return $parsedData;
    }

    protected function _parsedSpreedSheetABB($spreedSheetParser, $options = [])
    {
        $parsedData = [];
        $sheetsData = [];
        $rawData = [];

        // get shees
        $worksheets = $spreedSheetParser->getWorksheets();
        $lastWorksheet = end($worksheets);
        $hadData = false;
        //set sheets data
        foreach ($worksheets as $worksheetKey => $worksheet) {
            
            //worksheet target

            // get sheet sheets rows data
            $myWorksheetIndex = $spreedSheetParser->getWorksheetIndex($worksheet);
            $rows = $spreedSheetParser->createRowIterator($myWorksheetIndex);
            // validate sheet data
            if (!$rows) {
                // break;
                throw new Exception('Spreadsheet has empty content.');
            }
            $sheetTable = '';
            $sheetTableRow = '';
            // Dev::pvx($rows);
            // get and set sheet column
            foreach ($rows as $rowIndex => $rowValue) {
                $sheetCategories = [];
                
                // collect spreedsheet rows
                $rowValue1 = isset($rowValue[0]) ? ZCommon::cleanString($rowValue[0]) : '';
                $rowValue2 = isset($rowValue[1]) ? ZCommon::cleanString($rowValue[1]) : '';
                $rowValue3 = isset($rowValue[2]) ? ZCommon::cleanString($rowValue[2]) : '';
                $rowValue4 = isset($rowValue[3]) ? ZCommon::cleanString($rowValue[3]) : '';
                $rowValue5 = isset($rowValue[4]) ? ZCommon::cleanString($rowValue[4]) : '';
                $rowValue6 = isset($rowValue[5]) ? ZCommon::cleanString($rowValue[5]) : '';
                $rowTrim = ZCommon::cleanArray($rowValue);
                $rowstrtolower = array_map('strtolower',$rowTrim);
                //skip unimportant row
                if ($rowValue1 != ''
                && $rowValue2 == ''
                && $rowValue3 == ''
                && $rowValue4 == ''
                && $rowValue5 == ''
                && $rowValue6 == '') {
                    continue;
                }
                if ($rowValue1 == ''
                && $rowValue2 == ''
                && $rowValue3 == ''
                && $rowValue4 == ''
                && $rowValue5 == ''
                && $rowValue6 != '') {
                    continue;
                }
                
                if (in_array(strtolower('PartNumber'), $rowstrtolower) 
                    && in_array(strtolower('Description'), $rowstrtolower) 
                    && in_array(strtolower('MSRP'), $rowstrtolower) 
                    && in_array(strtolower('Cost'), $rowstrtolower)) {
                    $sheetTable = 'Door Entry System - 2 Wire';
                    $sheetTableRow = $rowValue;
                    $hadData = true;
                    continue;
                }
                
                if (in_array(strtolower('ArticleNo.'), $rowstrtolower) 
                    && in_array(strtolower('Productdescription'), $rowstrtolower) 
                    && in_array(strtolower('2022AuthDistPrice'), $rowstrtolower) 
                    && in_array(strtolower('2022StockingDistPrice'), $rowstrtolower)) {
                    $sheetTable = 'Door Entry System - IP';
                    $sheetTableRow = $rowValue;
                    $hadData = true;
                    continue;
                }

                // validate mapped colulmn
                if ($sheetTable == '' && $worksheet == $lastWorksheet && !$hadData) {
                    $errorMessage = '<small>Selected vendor doesn\'t support spreadsheet columns.</small><small><ul><li>Make sure spreadsheet belong to selected vendor.</li><li>Make sure spreadsheet column is set in column mapping setting.</li></ul></small>';
                    // $errorMessage = 'Products column is not supported in <em><strong>"'. $worksheet .'"</strong></em> sheet.';
                    throw new Exception($errorMessage);
                    /* Dev::pv($errorMessage);
                    break; */
                }
                // change worksheet
                if ($sheetTable == '') {
                    continue;
                }
                $sheetRowIndexCode = ZCommon::cleanString($worksheetKey .'-'. $worksheet) .'-'. $rowIndex;

                if (isset($options['rawData']) && $options['rawData'] === true) {
                    $rawDataTmp = [];

                    foreach ($sheetTableRow as $sheetTableRowKey => $sheetTableRowVal) {
                        if (isset($rowValue[$sheetTableRowKey])) {
                            $rawDataTmp[ZCommon::cleanString($sheetTableRowVal)] = $rowValue[$sheetTableRowKey];
                        }
                    }

                    $rawData[$sheetRowIndexCode] = $rawDataTmp;

                    continue;
                }

                // set categories
                $sheetCategories[] = Vendor::getVendorNameLabel(Vendor::INDEX_ABB);
                $sheetCategories[] = $worksheet;
                
                // set sheets columns and sheets data
                $sheetsData[$worksheet][$sheetTable][] = [
                    'sheet_col' => $sheetTableRow,
                    'sheet_data' => $rowValue,
                    'sheet_row_index' => $rowIndex,
                    'sheet_row_index_code' => $sheetRowIndexCode,
                    'sheet_row_categories' => json_encode($sheetCategories)
                ];
            }
        }
       // Dev::pvx($sheetsData);
        // return raw data
        if (isset($options['rawData']) && $options['rawData'] === true) {
            return $rawData;
        }

        // retrive column mapping setting data
        $settingColumnMapping = SettingColumnMapping::model()->find()->attributes;

        // remove column unecessary mapping data
        unset($settingColumnMapping['id']);
        unset($settingColumnMapping['status']);
        unset($settingColumnMapping['date_created']);
        unset($settingColumnMapping['date_updated']);

        $settingColumnMappingValues = [];

        // prepare mapping column setting, retrieve
        foreach ($settingColumnMapping as $settingColumnMappingkey => $settingColumnMappingData) {
            $settingColumnMappingData = strtolower($settingColumnMappingData);
            $columnMapData =  array_map('ZCommon::cleanString', explode(',', $settingColumnMappingData));
            $settingColumnMappingValues[$settingColumnMappingkey] = $columnMapData;
        }
        
        $sheetParsedData = [];
        // set columns mapping and values
        foreach ($sheetsData as $sheetsDataKey => $sheetsDataVal) {
            foreach ($sheetsDataVal as $sheetsDataTableKey => $sheetsDataTableVal) {
                foreach ($sheetsDataTableVal as $sheetsDataTableItemKey => $sheetsDataTableItemVal) {
                    $sheetData = $sheetsDataTableItemVal['sheet_data'];
                    $sheetCol = $sheetsDataTableItemVal['sheet_col'];
                    $sheetRowIndex = $sheetsDataTableItemVal['sheet_row_index'];
                    $sheetRowIndexCode = $sheetsDataTableItemVal['sheet_row_index_code'];
                    $sheetCategories = $sheetsDataTableItemVal['sheet_row_categories'];

                    $sheetColumnWithData = [];

                    foreach ($sheetCol as $sheetColumnKey => $sheetColumnVal) {
                        $sheetColumnVal = ZCommon::cleanString($sheetColumnVal);

                        $columnHasMappingData = false;

                        foreach ($settingColumnMappingValues as $settingColumnMappingKey => $settingColumnMappingVal) {
                            if (in_array(strtolower($sheetColumnVal), $settingColumnMappingVal)) {
                                $sheetColumnVal = $settingColumnMappingKey;
                                $columnHasMappingData = true;
                                continue;
                            }
                        }
                        $sheetColumnValRaw = '';
                        if (!$columnHasMappingData) {
                            $sheetColumnValRaw = $sheetColumnVal;
                            $sheetColumnVal = '__' . $sheetColumnValRaw;
                        }

                        $sheetColumnWithData[$sheetColumnVal] = isset($sheetData[$sheetColumnKey]) ? preg_replace("/[\n\r]/", " ", $sheetData[$sheetColumnKey]) : 'not set';
                    }
                    
                    $sheetParsedData[] = $sheetColumnWithData + [
                        'sheets_name' => $sheetsDataKey,
                        'sheets_row_index' => $sheetRowIndex,
                        'sheet_row_index_code' => $sheetRowIndexCode,
                        'sheets_col' => $sheetColumnValRaw,
                        'categories' => $sheetCategories];
                }
            }
        }
        // format sheet data
        foreach ($sheetParsedData as $sheetParsedDataKey => $sheetParsedDataVal) {
            $tmpData = [];
            
            foreach ($sheetParsedDataVal as $sheetRowDatakey => $sheetRowDataVal) {
                $tmpData[$sheetRowDatakey] = $sheetRowDataVal;
            }
            $parsedData[] = $tmpData;
        }
       // Dev::pvx($parsedData);
        // validate data mpn
        foreach ($parsedData as $parsedDataKey => $parsedDataValue) {
            $errorSheetName = '';
            $errorSheetRowIndex = '';

            if (!in_array('mpn', array_keys($parsedDataValue))) {
                $errorSheetName = $parsedDataValue['sheets_name'];

                $errorMessage = 'Cannot select spreadsheet mpn, make sure mpn is set in column mapping setting. Sheet Name <strong>"'. $errorSheetName .'"</strong>';

                throw new Exception($errorMessage);
                // break;
            }
        }

        if(empty($parsedData)){
        	$errorMessage = 'No Existing Data!';

            throw new Exception($errorMessage);
        }
        return $parsedData;
    }
    // back up 05042021
    // protected function _parsedSpreedSheetLocknetics($spreedSheetParser, $options = [])
    // {
    //     $parsedData = [];
    //     $rawData = [];

    //     $columnIndex = 0;

    //     // get sheets
    //     $worksheets = $spreedSheetParser->getWorksheets();

    //     // validate sheets
    //     if (count($worksheets) > 1) {
    //         throw new Exception('Spreadsheet for Locknetics should only contain 1 sheet.');
    //     }

    //     // select sheet
    //     $spreedSheetIndex = $spreedSheetParser->getWorksheetIndex($worksheets[0]);
    //     $rows = $spreedSheetParser->createRowIterator($spreedSheetIndex);

    //     // validate sheet data
    //     if (!$rows) {
    //         throw new Exception('Spreadsheet has empty content.');
    //     }

    //     $columnRow = [];

    //     // get and set sheet column
    //     foreach ($rows as $rowIndex =>$rowValue) {
    //         $rowValue1 = isset($rowValue[0]) ? ZCommon::cleanString($rowValue[0]) : '';
    //         $rowValue2 = isset($rowValue[1]) ? ZCommon::cleanString($rowValue[1]) : '';
    //         $rowValue3 = isset($rowValue[2]) ? ZCommon::cleanString($rowValue[2]) : '';
    //         $rowValue4 = isset($rowValue[3]) ? ZCommon::cleanString($rowValue[3]) : '';
    //         $rowValue5 = isset($rowValue[4]) ? ZCommon::cleanString($rowValue[4]) : '';
    //         $rowValue6 = isset($rowValue[5]) ? ZCommon::cleanString($rowValue[5]) : '';
    //         $rowTrim = ZCommon::cleanArray($rowValue);

    //         /* if($rowValue1 != ''
    //         && $rowValue2 == ''
    //         && $rowValue3 == ''){
    //             continue;
    //         } */
            
    //         //skip unimportant row
    //         if ($rowValue1 == ''
    //         && $rowValue2 == ''
    //         && $rowValue3 == ''
    //         && $rowValue4 == ''
    //         && $rowValue5 == '') {
    //             continue;
    //         }
    //         /*if ($rowValue1 != ''
    //         && $rowValue2 == ''
    //         && $rowValue3 == ''
    //         && $rowValue4 == ''
    //         && $rowValue5 == '') {
    //             continue;
    //         }*/
    //         /*if ($rowValue1 != ''
    //         && $rowValue2 == ''
    //         && $rowValue3 == ''
    //         && $rowValue4 == ''
    //         && $rowValue5 != '') {
    //             continue;
    //         }*/
    //         if ((in_array('LockneticsElectricStrikesandElectromagneticLocks', $rowTrim) || in_array('LockneticsElectricStrikesandElectromagneticLocksandaccessories', $rowTrim))
    //             && (in_array('ListPrice', $rowTrim) || in_array('ListPriceJuly2018', $rowTrim))
    //             && (in_array('AllegionPartner', $rowTrim) || in_array('AllegionPartnerExampleRecommendedNetPrice:', $rowTrim))) {
    //             $columnRow = $rowValue;
    //             $columnIndex = $rowIndex;
    //             break;
    //         }
    //     }

    //     // validate sheets columns
    //     if ($columnIndex <= 0) {
    //         throw new Exception('Spreadsheet has empty content.');
    //     }

    //     $columnMapped = array();

    //     // get and set sheet mapped data
    //     foreach ($rows as $rowIndex => $rowValue) {
    //         if ($rowIndex >= $columnIndex) {
    //             foreach ($rowValue as $columnKey => $columnValue) {
    //                 $hasMatch = false;
    //                 // retrive column mapping setting data
    //                 $settingColumnMapping = SettingColumnMapping::model()->find()->attributes;
    
    //                 // remove column unecessary mapping data
    //                 unset($settingColumnMapping['id']);
    //                 unset($settingColumnMapping['status']);
    //                 unset($settingColumnMapping['date_created']);
    //                 unset($settingColumnMapping['date_updated']);

    //                 foreach ($settingColumnMapping as $settingColumnMappingkey => $settingColumnMappingData) {
    //                     if (trim($settingColumnMappingData) == '') {
    //                         continue;
    //                     }
                        
    //                     $settingColumnMappingData = strtolower($settingColumnMappingData);
    //                     $settingColumnMappingDataArr = array_map('ZCommon::cleanString', explode(',', $settingColumnMappingData));
    
    //                     if (in_array(ZCommon::cleanString(strtolower($columnValue)), $settingColumnMappingDataArr)) {
    //                         $columnMapped[$columnKey] = $settingColumnMappingkey;
    //                         $hasMatch = true;
    //                         break;
    //                     }
    //                 }

    //                 if (!$hasMatch) {
    //                     $columnMapped[$columnIndex] = '';
    //                 }
    //             }
    //             break;
    //         }
    //     }
    //     // validate sheet column mapped mpn
    //     if (!in_array('mpn', $columnMapped)) {
    //         throw new Exception('Cannot select spreadsheet mpn, make sure mpn is set in column mapping setting.');
    //     }

    //     $sheetCategories = [];
    //     $productCategories = [];
 
    //     // set sheet column mapped values
    //     foreach ($rows as $rowIndex => $rowValue) {
    //         if ($rowIndex >= $columnIndex) {
    //             if (count($rowValue) < 2) {
    //                 $sheetCategories[] = Vendor::getVendorNameLabel(Vendor::INDEX_LOCKNETICS);
    //                 $sheetCategories[] = trim($rowValue[0]);
    //                 continue;
    //             }

    //             //check if value is category
    //             // if ($rowValue[0] == $rowValue[1] && $rowValue[0] == $rowValue[2]) {
    //             //     continue;
    //             // }

    //             $tmpData = array();
    //             $rawDataTmp = [];

    //             $sheetRowIndexCode = ZCommon::cleanString($worksheets[0] .'-'. $rowIndex);
                
    //             foreach ($rowValue as $columnKey => $columnValue) {
    //                 if (isset($columnMapped[$columnKey]) && $columnMapped[$columnKey] != '') {
    //                     $tmpData[$columnMapped[$columnKey]] = $columnValue;
    //                 }

    //                 if (isset($columnRow[$columnKey])) {
    //                     $rawDataTmp[ZCommon::cleanString($columnRow[$columnKey])] = $columnValue;
    //                 }

    //                 if (count($sheetCategories) > 0) {
    //                     $productCategories = $sheetCategories;
    //                 }
                    
    //                 $tmpData['categories'] = json_encode($productCategories);
    //                 $tmpData['sheet_row_index'] = $rowIndex;
    //             }

    //             $tmpData['sheet_row_index_code'] = $sheetRowIndexCode ;

    //             $sheetCategories = [];

    //             $rawData[$sheetRowIndexCode] = $rawDataTmp;

    //             $parsedData[] = $tmpData;
    //         }
    //     }
    //     // return raw data
    //     if (isset($options['rawData']) && $options['rawData'] === true) {
    //         return $rawData;
    //     }
        
    //     return $parsedData;
    // }
}
