<?php

namespace backend\controllers;

use Yii;
use common\models\Card;
use backend\models\CardSearch;
use yii\web\NotFoundHttpException;
use yii\db\Query;
use common\models\Customer;
use yii\helpers\Json;
use backend\models\CardImportRfidForm;
use yii\web\UploadedFile;
use common\components\Helper;
use backend\models\CardInsertForm;
use common\models\Ticket;
use frontend\components\HtmlHelper;

/**
 * CardController implements the CRUD actions for Card model.
 */
class CardController extends \backend\controllers\BackendController {
	public function behaviors() {
		return [ 
				'access' => [ 
						'class' => \yii\filters\AccessControl::className (),
						'rules' => [
								// allow authenticated users
								[
								'actions' => [
										'clear',
										 
								],
								'allow' => true,
								'roles' => [
										'admin'
								]
								],
								[ 
										'actions' => [ 
												'create',
												'index',
												'view',
												'update',
												'list' ,
												'import-rfid',
												'insert',
												'recalculate',
										],
										'allow' => true,
										'roles' => [ 
												'@' 
										] 
								] ,
								
						]
						// everything else is denied
						 
				] 
		];
	}
	
	
	public function actionClear($id){
		
		$model = $this->findModel ( $id );
		
		Ticket::updateAll(['id_card' => null],['id_card' => $model->id_card]);
		
		
		$customer = $model->customer;
		$customer->id_customer_card = null;
		$customer->save(false);
		
		Card::updateCardFlagTicket($model->id_card);
		
		
		Helper::flash('success', "Vendég törölve a kártyáról");
		
		$this->redirect(['card/update','id' =>$id]);
	}
	
	/**
	 * Lists all Card models.
	 * 
	 * @return mixed
	 */
	public function actionIndex() {
		$searchModel = new CardSearch ();
		$dataProvider = $searchModel->search ( Yii::$app->request->queryParams );
		
		return $this->render ( 'index', [ 
				'searchModel' => $searchModel,
				'dataProvider' => $dataProvider 
		] );
	}
	
	/**
	 * Displays a single Card model.
	 * 
	 * @param integer $id        	
	 * @return mixed
	 */
	public function actionView($id) {
		return $this->render ( 'view', [ 
				'model' => $this->findModel ( $id ) 
		] );
	}
	
	/**
	 * Creates a new Card model.
	 * If creation is successful, the browser will be redirected to the 'view' page.
	 * 
	 * @return mixed
	 */
	public function actionCreate() {
		$model = new Card ();
		$model->status = Card::STATUS_ACTIVE;
		$model->type = Card::TYPE_RFID;
		if ($model->load ( Yii::$app->request->post () ) && $model->save ()) {
			\Yii::$app->session->setFlash ( 'success', 'Card created!' );
			if (isset ( $_POST ['create_next'] )) {
				return $this->redirect ( [ 
						'create' 
				] );
			} else {
				return $this->redirect ( [ 
						'view',
						'id' => $model->id_card 
				] );
			}
		} else {
			return $this->render ( 'create', [ 
					'model' => $model 
			] );
		}
	}
	
	/**
	 * Updates an existing Card model.
	 * If update is successful, the browser will be redirected to the 'view' page.
	 * 
	 * @param integer $id        	
	 * @return mixed
	 */
	public function actionUpdate($id) {
		$model = $this->findModel ( $id );
		if ($model->load ( Yii::$app->request->post () ) && $model->save ()) {
			return $this->redirect ( [ 
					'index' 
			] );
			// return $this->redirect(Yii::$app->request->referrer);
		} else {
			return $this->render ( 'update', [ 
					'model' => $model 
			] );
		}
	}
	
	/**
	 * Deletes an existing Card model.
	 * If deletion is successful, the browser will be redirected to the 'index' page.
	 * 
	 * @param integer $id        	
	 * @return mixed
	 */
	public function actionDelete($id) {
		$this->findModel ( $id )->delete ();
		
		return $this->redirect ( [ 
				'index' 
		] );
	}
	
	/**
	 * Finds the Card model based on its primary key value.
	 * If the model is not found, a 404 HTTP exception will be thrown.
	 * 
	 * @param integer $id        	
	 * @return Card the loaded model
	 * @throws NotFoundHttpException if the model cannot be found
	 */
	protected function findModel($id) {
		if (($model = Card::findOne ( $id )) !== null) {
			return $model;
		} else {
			throw new NotFoundHttpException ( 'The requested page does not exist.' );
		}
	}
	
	/**
	 * Your controller action to fetch the list
	 */
	public function actionList($search = null) {
		$query = new Query ();
		
		$query->select ( [ 
				'card.number as number',
				'customer.name as name',
				"concat( card.number , case when  customer.name is null then  '' else customer.name end  ) as txt " 
		] )->from ( Card::tableName () )->join ( "left join", Customer::tableName (), 'card.id_card = customer.id_customer_card' )->where ( ' lower(number) LIKE "%' . strtolower ( $search ) . '%"' )->orderBy ( 'number' );
		
		if (isset ( $_GET ['onlyFree'] ) && $_GET ['onlyFree'] == '1') {
			$query->andWhere ( 'customer.id_customer is null' );
		}
		
		$command = $query->createCommand ();
		$data = $command->queryAll ();
		$out = [ ];
		foreach ( $data as $d ) {
			$out [] = [ 
					'number' => $d ['number'],
					'name' => $d ['name'],
					'txt' => $d ['txt'] 
			];
		}
		echo Json::encode ( $out );
	}
	public function actionImportRfid() {
		$model = new CardImportRfidForm ();
			$arr = [];
		
		if (Yii::$app->request->isPost) {
			$model->file = UploadedFile::getInstance($model, 'file');

// 			print_r($model->file);
// 			$model->message  = "ok";			
			$file = $model->file->tempName;
			
			$file = fopen ( $file , "r" );
			
			$trans = null;
			$i = 0;
			$j = 0;
			while ( ($data = fgetcsv ( $file, 0, "," )) != null ) {
// 				if ($i == 0) {
// 					$i ++;
// 					continue;
// 				}
				$j++;
				$number = $key = false;
				if ( isset($data[0]) ){
					$number = $data[0];
				}
				
				if ( isset($data[1]) ){
					$key = $data[1];
				}
				
				if ( isset($number) && isset($key)){
				$item = [];
				$item['number'] = $number;
				$item['key'] = Helper::fixAsciiChars( $key);
				$arr[] = $item;
				}
				
				
			}
			
			$failed = [];
			$sqls = [];
			$inserts = [];
			foreach ($arr as $item ){
				$card = Card::find()->andWhere(['number' => $item['number']])->one();
				if ( $card != null ){
					$card->rfid_key = $item['key'];
					$sql = "update card set rfid_key = '" . strtolower( $item['key'] )."' where id_card = " .$card->id_card .";"; 
					$sqls[] = $sql;
					$i++;
				}else{
// 					$failed [] = $item;
					$sql = "insert into card (number,status,type,created_at,updated_at, rfid_key) values(";
					$sql .= " '" .$item['number'] . "'" ;
					$sql .= " ," . Card::STATUS_ACTIVE ;
					$sql .= " ," . Card::TYPE_RFID;
					$sql .= " ,'" .  date("Y-m-d H:i:s") ."'" ;
					$sql .= " ,'" .  date("Y-m-d H:i:s") ."'" ;
					$sql .=" ,'" .$item['key'] ."'";
					$sql .= " );";
					$inserts[] = $sql;
// 					rfid_key = '" . strtolower( $item['key'] )."' where id_card = " .$card->id_card .";";
				}
			}
			
			$model->message = "rows read: " .$j ." / ". "updated cards: " .$i;
			$model->message .= "<br> array size: " . count($arr);
			$model->message .= "<br> failed: " . print_r($failed,true);
			$model->message .= "<br>sql:";
			$model->message .= "<br>". implode("<br>", $sqls);
			$model->message .= "<br><br><br>Inserts<br><br><br>";
			$model->message .= "<br>". implode("<br>", $inserts);
			
		}
		
		return $this->render ( 'importRfid.php', [ 
				'model' => $model 
		] );
	}
	
	
	
	public function actionInsert() {
		$model = new CardInsertForm();
		$arr = [];
	
		if (Yii::$app->request->isPost) {
			$model->file = UploadedFile::getInstance($model, 'file');
	
			// 			print_r($model->file);
			// 			$model->message  = "ok";
			$file = $model->file->tempName;
				
			$file = fopen ( $file , "r" );
				
			$trans = null;
			$i = 0;
			$j = 0;
			while ( ($data = fgetcsv ( $file, 0, "," )) != null ) {
				// 				if ($i == 0) {
				// 					$i ++;
				// 					continue;
				// 				}
				$j++;
				$number = $key = false;
				if ( isset($data[0]) ){
					$number = $data[0];
				}
	
				if ( isset($data[1]) ){
					$key = $data[1];
				}
	
				if ( isset($number) && isset($key)  && !strpos($key, "E+") && strlen($key) > 7 ){
					$item = [];
					$item['number'] = $number;
					$item['key'] = Helper::fixAsciiChars( $key);
					$arr[] = $item;
				}
	
	
			}
				
			$failed = [];
			$sqls = [];
			$inserts = [];
			foreach ($arr as $item ){
					// 					$failed [] = $item;
					$sql = "insert into card (number,status,type,created_at,updated_at, rfid_key) values(";
					$sql .= " '" .$item['number'] . "'" ;
					$sql .= " ," . Card::STATUS_ACTIVE ;
					$sql .= " ," . Card::TYPE_RFID;
					$sql .= " ,'" .  date("Y-m-d H:i:s") ."'" ;
					$sql .= " ,'" .  date("Y-m-d H:i:s") ."'" ;
					$sql .=" ,'" .$item['key'] ."'";
					$sql .= " );";
					$inserts[] = $sql;
			}
				
			$model->message = "rows read: " .$j ." / ". "updated cards: " .$i;
			$model->message .= "<br> array size: " . count($arr);
			$model->message .= "<br> failed: " . print_r($failed,true);
			$model->message .= "<br>sql:";
			$model->message .= "<br>". implode("<br>", $sqls);
			$model->message .= "<br><br><br>Inserts<br><br><br>";
			$model->message .= "<br>". implode("<br>", $inserts);
			
			
			$inserts = implode("\n", $inserts);
			header("Content-type:text/plain"); //for pdf file
			//header('Content-Type:text/plain; charset=ISO-8859-15');
			//if you want to read text file using text/plain header
			header('Content-Disposition: attachment; filename="insert.sql"');
			header('Content-Length: ' . strlen($inserts));
			echo $inserts;
			exit();
				
		}
	
		return $this->render ( 'insert.php', [
				'model' => $model
		] );
	}
	
	
	public function actionRecalculate(){
		
		if (Yii::$app->request->isPost) {
			$connection = \Yii::$app->db;
			$command = $connection->createCommand( Ticket::$SQL_UPDATE );
			$result = $command->execute();
			\Yii::info("Tickets updated: " . $result );
			
			
			\Yii::$app->session->setFlash('success', 'Módosított bérletek száma: ' . $result);
			
		}
		
		return $this->render("recalculate");
	}
	
	
}
