<?php

/**
 * This is the model class for table "{{vendor_normalized}}".
 *
 * The followings are the available columns in table '{{vendor_normalized}}':
 * @property integer $id
 * @property integer $vendor_fileupload_id
 * @property integer $status
 * @property string $date_created
 * @property string $date_updated
 *
 * The followings are the available model relations:
 * @property VendorFileupload $vendorFileupload
 * @property VendorNormalizedInfo[] $vendorNormalizedInfos
 */
class VendorNormalized extends CActiveRecord
{
	const STATUS_PENDING = 0;
	const STATUS_FOR_REVIEW = 1;
	const STATUS_PUSHING = 2;
	const STATUS_PUSHED = 3;
	const STATUS_DELETED = 4;
	const STATUS_ERROR_PUSHING = 5;
	const STATUS_ERROR_VALIDATION = 6;
	const STATUS_ERROR = 7;

	const PRODUCT_STATUS_NOT_CHECKED = 0;
	const PRODUCT_STATUS_NEW = 1;
	const PRODUCT_STATUS_UPDATED = 2;
	const PRODUCT_STATUS_REMAIN = 3;
	const PRODUCT_STATUS_EOL = 4;

	const REST_STATUS_PENDING = 0;
	const REST_STATUS_NEW = 1;
	const REST_STATUS_EXISTING = 2;
	const REST_STATUS_EOL = 3;
	const REST_STATUS_NON_EOL = 4;

	const IMAGE_STATUS_MISSING = 0;
	const IMAGE_STATUS_EXIST = 1;

	const PRODUCT_FROM_SPREADSHEET = 0;
	const PRODUCT_FROM_WEBSITE = 1;

	/* 	new, updated, deleted, remain*/
	/**
	 * @return string the associated database table name
	 */
	public function tableName()
	{
		return '{{vendor_normalized}}';
	}

	/**
	 * @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('vendor_fileupload_id', 'required'),
			array('vendor_fileupload_id, status', 'numerical', 'integerOnly'=>true),
			array('date_created, date_updated', 'safe'),
			// The following rule is used by search().
			// @todo Please remove those attributes that should not be searched.
			array('id, vendor_fileupload_id, status, date_created, date_updated', '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(
			'vendorFile' => array(self::BELONGS_TO, 'vendorFile', 'vendor_fileupload_id'),
			'vendorNormalizedInfos' => array(self::HAS_MANY, 'VendorNormalizedInfo', 'vendor_normalized_id'),
			'vendorNormalizedInfo' => array(self::HAS_ONE, 'VendorNormalizedInfo', 'vendor_normalized_id'),
		);
	}

	/**
	 * @return array customized attribute labels (name=>label)
	 */
	public function attributeLabels()
	{
		return array(
			'id' => 'ID',
			'vendor_fileupload_id' => 'Vendor Fileupload',
			'status' => 'Status',
			'date_created' => 'Date Created',
			'date_updated' => 'Date Updated',
		);
	}

	/**
	 * 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('vendor_fileupload_id',$this->vendor_fileupload_id);
		$criteria->compare('status',$this->status);
		$criteria->compare('date_created',$this->date_created,true);
		$criteria->compare('date_updated',$this->date_updated,true);

		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 VendorNormalized the static model class
	 */
	public static function model($className=__CLASS__)
	{
		return parent::model($className);
	}

	public function beforeSave() {
		if (parent::beforeSave()) {
			if ($this->isNewRecord) {
				$this->date_created = date('Y-m-d H:i:s');
				$this->date_updated = date('Y-m-d H:i:s');
			} else {
				$this->date_updated = date('Y-m-d H:i:s');
			}
		}

		return true;
	}

	public static function getStatusList($withColor = false) {
		if ($withColor) {
			return array(
				self::STATUS_PENDING => '<span class="label label-default">Pending</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_PUSHING => '<span class="label label-danger">Error Publishing</span>',
				self::STATUS_DELETED => '<span class="label label-danger">Deleted</span>',
				self::STATUS_ERROR_VALIDATION => '<span class="label label-danger">Error Validation</span>',
				self::STATUS_ERROR => '<span class="label label-danger">Errors</span>',
			);
		} else {
			return array(
				self::STATUS_PENDING => 'Pending',
				self::STATUS_FOR_REVIEW => 'For Review',
				self::STATUS_PUSHING => 'Publishing',
				self::STATUS_PUSHED => 'Published',
				self::STATUS_ERROR_PUSHING => 'Error',
				self::STATUS_DELETED => 'Deleted',
				self::STATUS_ERROR_VALIDATION => 'Error Validation',
				self::STATUS_ERROR => 'Errors'
			);
		}
	}


	public static function getStatusListEdit() {
		return array(
			self::STATUS_FOR_REVIEW => 'Active',
			self::STATUS_DELETED => 'Deleted'
		);
	}

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

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

	public static function getProductFromList(){
		return array(
			self::PRODUCT_FROM_SPREADSHEET => 'Spreadsheet',
			self::PRODUCT_FROM_WEBSITE => 'Website',
		);
	}

	public static function getProductFromLabel($index){
		return self::getProductFromList()[$index];
	}

	public static function countTotalParsing($vendorFileId) {
		$totalParsedQuery = '
			SELECT id as count FROM {{vendor_normalized}} vn 
			WHERE vendor_fileupload_id = '. (int) $vendorFileId .'
		';

		$totalParsedCommand = Yii::app()->db->createCommand($totalParsedQuery);
		$totalParsedData = $totalParsedCommand->queryAll();

		return count($totalParsedData);
	}

	public static function countTotalParsed($vendorFileId) {
		$totalParsedQuery = '
			SELECT id as count FROM {{vendor_normalized}} vn 
			WHERE vendor_fileupload_id = '. (int) $vendorFileId .' AND status != '.VendorNormalized::STATUS_DELETED.'
		';

		$totalParsedCommand = Yii::app()->db->createCommand($totalParsedQuery);
		$totalParsedData = $totalParsedCommand->queryAll();

		return count($totalParsedData);
	}

	public static function countStatusForReview($vendorFileId) {
		$forReviewQuery = '
			SELECT id as count FROM {{vendor_normalized}} vn 
			WHERE vendor_fileupload_id = '. $vendorFileId .' 
			AND status = '. (int) self::STATUS_FOR_REVIEW .'
		';

		$forReviewCommand = Yii::app()->db->createCommand($forReviewQuery);
		$forReviewData = $forReviewCommand->queryAll();

		return count($forReviewData);
	}

	public static function countStatusSynced($vendorFileId) {
		$forReviewQuery = '
			SELECT id as count FROM {{vendor_normalized}} vn 
			WHERE vendor_fileupload_id = '. $vendorFileId .' 
			AND status = '. (int) self::STATUS_FOR_REVIEW .' and (platform_1_rest_status != '.VendorNormalized::REST_STATUS_PENDING.' or platform_2_rest_status != '.VendorNormalized::REST_STATUS_PENDING.')
		';

		$forReviewCommand = Yii::app()->db->createCommand($forReviewQuery);
		$forReviewData = $forReviewCommand->queryAll();

		return count($forReviewData);
	}

	public static function countStatusPending($vendorFileId) {
		$forReviewQuery = '
			SELECT id as count FROM {{vendor_normalized}} vn 
			WHERE vendor_fileupload_id = '. $vendorFileId .' 
			AND status = '. (int) self::STATUS_PENDING .'
		';

		$forReviewCommand = Yii::app()->db->createCommand($forReviewQuery);
		$forReviewData = $forReviewCommand->queryAll();

		return count($forReviewData);
	}

	public static function countStatusPushing($vendorFileId) {
		$pushingQuery = '
			SELECT id as count FROM {{vendor_normalized}} vn 
			WHERE vendor_fileupload_id = '. $vendorFileId .' 
			AND status = '. (int) self::STATUS_PUSHING .'
		';

		$pushingCommand = Yii::app()->db->createCommand($pushingQuery);
		$pushingData = $pushingCommand->queryAll();

		return count($pushingData);
	}

	public static function countStatusPushed($vendorFileId) {
		$pushedQuery = '
			SELECT id as count FROM {{vendor_normalized}} vn 
			WHERE vendor_fileupload_id = '. $vendorFileId .' 
			AND status = '. (int) self::STATUS_PUSHED .'
		';

		$pushedCommand = Yii::app()->db->createCommand($pushedQuery);
		$pushedData = $pushedCommand->queryAll();

		return count($pushedData);
	}

	public static function countStatusError($vendorFileId) {
		$errorQuery = '
			SELECT id as count FROM {{vendor_normalized}} vn 
			WHERE vendor_fileupload_id = '. $vendorFileId .' 
			AND status IN ('. (int) self::STATUS_ERROR_PUSHING .', '. (int) self::STATUS_ERROR_VALIDATION .')
		';

		$errorCommand = Yii::app()->db->createCommand($errorQuery);
		$errorData = $errorCommand->queryAll();

		return count($errorData);
	}

	public static function countStatusDeleted($vendorFileId) {
		$deletedQuery = '
			SELECT id as count FROM {{vendor_normalized}} vn 
			WHERE vendor_fileupload_id = '. $vendorFileId .' 
			AND status = '. (int) self::STATUS_DELETED .'
		';

		$deletedCommand = Yii::app()->db->createCommand($deletedQuery);
		$deletedData = $deletedCommand->queryAll();

		return count($deletedData);
	}

	public static function getNormalizedFiles($vendorFileId) {
		$vendorFilesQuery = '
			SELECT vf.*, f.date_created, v.name as vendor_name, f.original_filename FROM {{vendor_normalized}} vn 
			INNER JOIN {{vendor_file}} AS vf ON vf.id = vn.vendor_fileupload_id
			INNER JOIN {{fileupload}} AS f ON f.id = vf.fileupload_id
			INNER JOIN {{vendor}} AS v ON v.id = vf.vendor_id
			WHERE vendor_fileupload_id = '. $vendorFileId .' 
			AND vn.status IN (
				'. (int) self::STATUS_FOR_REVIEW .', 
				'. (int) self::STATUS_PUSHING .', 
				'. (int) self::STATUS_PUSHED .')
			GROUP BY vf.id
		';

		$vendorFilesCommand = Yii::app()->db->createCommand($vendorFilesQuery);
		$vendorFilesData = $vendorFilesCommand->queryAll();

		return $vendorFilesData;
	}

	public function getVendorName() {
		return $this->vendorFile->vendor->name;
	}

	public static function productCheckStatus($data) {
		$isNew = ProductInfo::model()->findAll([
			'alias' => 'pi',
			'join' => 'INNER JOIN {{product}} AS p ON p.id = pi.product_id',
			'condition' => 'pi.mpn = :mpn AND p.status <> :status',
			'params' => [':mpn' => trim($data['mpn']), ':status' => Product::STATUS_DELETED]
		]);

		if (!$isNew) {
			return VendorNormalized::PRODUCT_STATUS_NEW;
		}

		$isRemain = ProductInfo::model()->findAll([
			'alias' => 'pi',
			'join' => 'INNER JOIN {{product}} AS p ON p.id = pi.product_id',
			'condition' => '
				pi.sku = :sku AND
				pi.mpn = :mpn AND
				pi.msrp = :msrp AND
				pi.cost = :cost AND
				pi.description = :description AND
				pi.ean = :ean AND
				pi.width = :width AND
				pi.length = :length AND
				pi.height = :height AND
				pi.weight = :weight AND
				p.status <> :status
			',
			'params' => [
				':sku' => trim($data['sku']),
				':mpn' => trim($data['mpn']),
				':msrp' => (float) $data['msrp'],
				':cost' => (float) $data['cost'],
				':description' => trim($data['description']),
				':ean' => trim($data['ean']),
				':width' => trim($data['width']),
				':length' => trim($data['length']),
				':height' => trim($data['height']),
				':weight' => trim($data['weight']),
				':status' => Product::STATUS_DELETED
			]  
		]);

		if ($isRemain) {
			return VendorNormalized::PRODUCT_STATUS_REMAIN;
		} else {
			return VendorNormalized::PRODUCT_STATUS_UPDATED;
		}
	}

	public static function getProductStatusList($withColor = false) {
		if ($withColor) {
			return array(
				self::PRODUCT_STATUS_NOT_CHECKED => '<span class="label label-danger">Re-Check</span>',
				self::PRODUCT_STATUS_NEW => '<span class="label label-success">New</span>',
				self::PRODUCT_STATUS_UPDATED => '<span class="label label-info">Updated</span>',
				self::PRODUCT_STATUS_REMAIN => '<span class="label label-default">Exist</span>',
				self::PRODUCT_STATUS_EOL => '<span class="label label-warning">EOL</span>'
			);
		} else {
			return array(
				self::PRODUCT_STATUS_NOT_CHECKED => 'Rechecked',
				self::PRODUCT_STATUS_NEW => 'New',
				self::PRODUCT_STATUS_UPDATED => 'Updated',
				self::PRODUCT_STATUS_REMAIN => 'Exist',
				self::PRODUCT_STATUS_EOL => 'EOL',
			);
		}
	}

	public static function getProductStatusLabel($index) {
		return self::getProductStatusList()[$index];
	}

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

	public static function countProductCheckStatuses() {
		$query = '
			SELECT vn.product_status, count(*) as count FROM {{vendor_normalized}} AS vn
			INNER JOIN {{vendor_file}} AS vf ON vf.id = vn.vendor_fileupload_id
			WHERE vf.status IN ('. VendorFile::STATUS_FOR_REVIEW .')
			AND vn.status in (
				'. VendorNormalized::STATUS_PENDING .',
				'. VendorNormalized::STATUS_FOR_REVIEW .',
				'. VendorNormalized::STATUS_PUSHING .',
				'. VendorNormalized::STATUS_PUSHED .',
				'. VendorNormalized::STATUS_ERROR .',
				'. VendorNormalized::STATUS_ERROR_PUSHING .',
				'. VendorNormalized::STATUS_ERROR_VALIDATION .')
			GROUP BY vn.product_status
		';

		$command = Yii::app()->db->createCommand($query);
		$results = $command->queryAll();
		
		$formatedData = [
			self::PRODUCT_STATUS_NEW => 0,
			self::PRODUCT_STATUS_UPDATED => 0,
			self::PRODUCT_STATUS_REMAIN => 0,
			self::PRODUCT_STATUS_EOL => 0
		];

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

		return $formatedData;
	}

	public static function countStatuses() {
		$query = '
			SELECT vn.status, count(*) as count FROM {{vendor_normalized}} AS vn
			INNER JOIN {{vendor_file}} AS vf ON vf.id = vn.vendor_fileupload_id
			WHERE vf.status IN ('. VendorFile::STATUS_FOR_REVIEW .')
			AND vn.status in (
				'. VendorNormalized::STATUS_PENDING .',
				'. VendorNormalized::STATUS_FOR_REVIEW .',
				'. VendorNormalized::STATUS_PUSHING .',
				'. VendorNormalized::STATUS_PUSHED .',
				'. VendorNormalized::STATUS_ERROR .',
				'. VendorNormalized::STATUS_ERROR_PUSHING .',
				'. VendorNormalized::STATUS_DELETED .',
				'. VendorNormalized::STATUS_ERROR_VALIDATION .')
			GROUP BY vn.status
		';

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

		foreach ($results as $result) {
			if ($result['status'] == self::STATUS_ERROR_PUSHING) 
				$formatedData[self::STATUS_ERROR] += $result['count'];
			else if ($result['status'] == self::STATUS_ERROR_VALIDATION) 
				$formatedData[self::STATUS_ERROR] += $result['count'];
			else
				$formatedData[(int) $result['status']] = $result['count'];

		}

		return $formatedData;
	}

	public static function countParsedData() {
		$query = '
			SELECT * FROM {{vendor_normalized}} AS vn
			INNER JOIN {{vendor_file}} AS vf ON vf.id = vn.vendor_fileupload_id
			WHERE vf.status IN ('. VendorFile::STATUS_FOR_REVIEW .') 

			AND vn.status in (
				'. VendorNormalized::STATUS_FOR_REVIEW .',
				'. VendorNormalized::STATUS_PUSHING .',
				'. VendorNormalized::STATUS_PUSHED .',
				'. VendorNormalized::STATUS_ERROR .',
				'. VendorNormalized::STATUS_ERROR_PUSHING .',
				'. VendorNormalized::STATUS_ERROR_VALIDATION .')
		';

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

	public static function getPendingData($vendorFileId){
		$query = '
			SELECT * FROM {{vendor_normalized}}
			where vendor_fileupload_id = '.$vendorFileId.'
			and status = '.self::STATUS_PENDING.'
		';

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

		return $results;
	}

	public function getForReviewData($vendorFileId){
		$query = '
			SELECT * FROM {{vendor_normalized}}
			where vendor_fileupload_id = '.$vendorFileId.'
			and status = '.self::STATUS_FOR_REVIEW.'
		';

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

		return $results;
	}

	public function getVendorData($vendorFileId){
		$query = '
			SELECT * FROM {{vendor_normalized}}
			where vendor_fileupload_id = '.$vendorFileId;

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

		return $results;
	}

    public static function getPlatformStatusForChecking($vendorfile_id){
		$vendorNormalized = $this::model()->findAll(array(
			'condition' => 'platform_1_rest_status = :platform_1_rest_status AND platform_2_rest_status = :platform_2_rest_status AND vendor_fileupload_id = :vendorfile_id',
			'params' => array(
				':platform_1_rest_status' => VendorNormalized::REST_STATUS_PENDING,
				':platform_2_rest_status' => VendorNormalized::REST_STATUS_PENDING,
				'vendorfile_id' => $vendorfile_id,
			),
		));

		return $vendorNormalized;
	}

	public static function getPlatformStatusChecked($vendorfile_id){
		$vendorNormalized = $this::model()->findAll(array(
			'condition' => 'platform_1_rest_status != :platform_1_rest_status AND platform_2_rest_status != :platform_2_rest_status AND vendor_fileupload_id = :vendorfile_id',
			'params' => array(
				':platform_1_rest_status' => VendorNormalized::REST_STATUS_PENDING,
				':platform_2_rest_status' => VendorNormalized::REST_STATUS_PENDING,
				'vendorfile_id' => $vendorfile_id,
			),
		));

		return $vendorNormalized;
	}

	public static function getVendorFileProducts($vendorfile_id){
		$vendorNormalized = $this::model()->findAll(array(
			'condition' => 'vendor_fileupload_id = :vendorfile_id',
			'params' => array(
				'vendorfile_id' => $vendorfile_id,
			),
		));

		return $vendorNormalized;
	}



	public static function getPlatformStatusList($withColor = false, $platform) {
		if ($withColor) {
			return array(
				self::REST_STATUS_PENDING => '<span class="label label-default">'.$platform.' Pending</span>',
				self::REST_STATUS_NEW => '<span class="label label-success">'.$platform.' New</span>',
				self::REST_STATUS_EXISTING => '<span class="label label-info">'.$platform.' Existing</span>',
				self::REST_STATUS_EOL => '<span class="label label-warning">'.$platform.' EOL</span>',
			);
		} else {
			return array(
				self::REST_STATUS_PENDING => $platform.' Pending',
				self::REST_STATUS_NEW => $platform.' New',
				self::REST_STATUS_EXISTING => $platform.' Existing',
				self::REST_STATUS_EOL => $platform.' EOL',
			);
		}
	}

	public static function getPlatformStatusLabel($index, $platform) {
		return self::getPlatformStatusList(false, $platform)[$index];
	}

	public static function getPlatformStatusLabelWithColor($index, $platform) {
		return self::getPlatformStatusList(true, $platform)[$index];
	}

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

    public static function getPlatformStatus($restStatus, $vendorFileId, $platform){
    	if($platform == 'sbb'){
    		$result = self::model()->findAll(array(
    			'condition' => 'platform_1_rest_status = :restStatus AND vendor_fileupload_id = :vendorFileId AND product_status != :productStatus',
    			'params' => array(
    				':restStatus' => $restStatus,
    				':vendorFileId' => $vendorFileId,
    				':productStatus' => VendorNormalized::PRODUCT_STATUS_EOL,
    			),
    		));
    	}else{
    		$result = self::model()->findAll(array(
    			'condition' => 'platform_2_rest_status = :restStatus AND vendor_fileupload_id = :vendorFileId AND product_status != :productStatus',
    			'params' => array(
    				':restStatus' => $restStatus,
    				':vendorFileId' => $vendorFileId,
    				':productStatus' => VendorNormalized::PRODUCT_STATUS_EOL,
    			),
    		));
    	}

    	return $result;
    }

    public static function checkImageStatus($imageUrl){
    	$handle = curl_init($imageUrl);
		curl_setopt($handle,  CURLOPT_RETURNTRANSFER, TRUE);

		/* Get the HTML or whatever is linked in $url. */
		$response = curl_exec($handle);

		/* Check for 404 (file not found). */
		$httpCode = curl_getinfo($handle, CURLINFO_HTTP_CODE);
		//Dev::pvx($httpCode);
		if($httpCode == 200) {
			curl_close($handle);
		    return true;
		}else{
			curl_close($handle);
			return false;
		}
    }

    public static function get_content($URL){
      $ch = curl_init();
      curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
      curl_setopt($ch, CURLOPT_URL, $URL);
      $data = curl_exec($ch);
      curl_close($ch);
      return $data;
}
}
