<?php namespace App\Models\teamstore;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class TeamStoreModel extends Model {
	
	function selectAllTeamStore() // display all data from database
	{
		$i = DB::table('teamstores')
						->where("IsActive", "true")
						->orderBy('Id', 'DESC')
						->paginate(16);
        return $i;
	}

	function selectTeamStoreProducts($field, $teamstoreId) // display all data from database
	{
		$i = DB::table('teamstore_products')
						->where($field, $teamstoreId)
						->orderBy('Ordering', 'ASC')
						->orderBy('Ordering', 'ASC')->get();
        return $i;
	}
	
	function selectTeamStoreProductByIdHash($id)
	{
		$i = DB::table('teamstore_products')
						->where(DB::raw('md5(Id)') , $id)
						->get();
        return $i;
	}

	function selectTeamStore($field,  $teamstoreURL) 
	{
		$i = DB::table('teamstores')
						->where($field, $teamstoreURL)
						->get();
        return $i;
	}

	function checkStorePassword($storeid, $password) 
	{
		$i = DB::table('teamstores')
						->where('Id', $storeid)
						->where('Password', $password)
						->get();
        return $i;
	}

	function selectTeamStoreGroupByCartKey($cartKey)
	{
		$i = DB::table('cart_tmp')
						->where('CartKey', $cartKey)
						->groupby('CartKey')
						->get();
        return $i;
	}	
	
	function getProductThumbnails($productId){

		$i = DB::table('teamstore_product_thumbnails')
							->where('ProductId', $productId)
							->orderby('Ordering', 'ASC')
							->get();
		return $i;
	}

	function insertTeamStoreProduct($item_details){

		$i = DB::table('teamstore_products')->insert($item_details);
		$id = DB::getPdo()->lastInsertId();
		
		return array(
				'i' => $i,
				'lastId' => $id
		);
	}

	function insertTeamStoreProductThumbnails($item_details){

		$i = DB::table('teamstore_product_thumbnails')->insert($item_details);
		return $i;
	}

	function getThumbnails($productId){

		$i = DB::table('teamstore_product_thumbnails')
											->where('ProductId', $productId)
											->orderby('Ordering', 'ASC')
											->get();
		return $i;
	}

	function getTeams($productId){

		$i = DB::table('teams')->where('ProductId', $productId)->get();
		return $i;
	}

	function getSizes(){

		$i = DB::table('sizes')
							->where('IsActive', 'TRUE')
							->orderby('Ordering', 'ASC')
							->get();
		return $i;
	}

	function getSizesByBracket($bracket){

		$i = DB::table('sizes')->select('Size', 'SizeDisplay')
							->where('Bracket', $bracket)
							->where('IsActive', 'TRUE')
							->orderby('Ordering', 'ASC')
							->get();
		return $i;
	}


	function insertToCart($data){

		$i = DB::table('cart_tmp')->insert($data);
		$id = DB::getPdo()->lastInsertId();
		
		return array(
				'i' => $i,
				'lastId' => $id
		);
		// return $i;
	}

	function selectDisplayCartThumb(){

		$i = DB::table('teamstore_product_thumbnails')
										->where('ImageClass', 'active')
										->get();
		return $i;
	}


	function myCart($cartKey){
		// echo $cartKey;
		if($cartKey != ""){
			$i = DB::table('cart_tmp')
								->leftjoin('teamstore_voucher', 'cart_tmp.VoucherId', '=', 'teamstore_voucher.Id')
								->select('cart_tmp.*', 'teamstore_voucher.VoucherCode',  'teamstore_voucher.VoucherType',  'teamstore_voucher.VoucherValue', 'teamstore_voucher.VoucherExpiryDate', 'teamstore_voucher.Status')
								->where('cart_tmp.CartKey', $cartKey)
								->get();

			// var_dump($i);
   			return $i;
		}
	}

	function myCartGroup($cartKey){
		if($cartKey != ""){
			$pdo 	= 	DB::connection()->getPdo();
			$query	=	$pdo->prepare("SELECT *,  COUNT(Id) AS qty, Price * SUM(Quantity) AS total_price FROM cart_tmp WHERE CartKey = :ck GROUP BY ProductId");
			$query->execute([':ck'=>$cartKey]);
			$row = $query->fetchAll(\PDO::FETCH_OBJ);
   			return $row;
		}
	}


	function getSubtotal($cartKey){

		$i = DB::table('cart_tmp')->select(DB::raw('SUM(Quantity * Price) AS Subtotal'))
								->where('CartKey','=',$cartKey)
								->get();
		return $i;
		
	}

	function updateStoreItem($data, $url){
		$i = DB::table('teamstore_products')->where('ProductURL', $url)
										->update($data);			
        return $i;
	}

	function getSoldQty($productId){
		$i = DB::table('orders')->select(DB::raw('SUM(Quantity) AS SoldQty'))
				->where('ProductId', $productId)
				->get();
		return $i;
	}
	
	function selectVoucher($data){
		$i = DB::table('teamstore_voucher')
								->where(DB::raw('BINARY `VoucherCode`'), $data['voucher'])
								->where('TeamStoreId', $data['store_id'])
								->get();
		return $i;
	}

	function selectVoucherWhereIn($data){
		$i = DB::table('teamstore_voucher')
								->whereIn('Id', $data)
								->get();
		return $i;
	}

	function updateVoucherValueInCart($data, $id){
		$i = DB::table('cart_tmp')
					->where('Id', $id)
					->update($data);			
        return $i;
	}

	function selectTeamstoreSearch($field, $value, $keyword){

		$i = DB::table('teamstores')
								->where("StoreName", "LIKE","%$keyword%")
								->where("IsActive", "true")
								->orderby($field, $value)
								->paginate(16);
		return $i;
	}

	function selectTeamstoreFilter($field, $value){

		$i = DB::table('teamstores')
								->where("IsActive", "true")
								->orderby($field, $value)
								->paginate(16);
		return $i;
	}
	
}