<?php namespace App\Models;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class MainModel extends Model {

	//
	function selectAllSports() // display all data from database
	{
        $i = DB::table('sports')->get();
        return $i;
	}

	function selectSportsByURL($url) // display all data from database
	{
		$i = DB::table('sports')
								->where('URL', $url)
								->get();
        return $i;
	}

	function selectSportsTemplates($id) //
	{
		$i = DB::table('templates')->where('SportsId', '=', $id)->get();
		return $i;
	}

	function selectSportsId($url) //
	{
		$i = DB::table('sports')->where('URL', '=', $url)->get();
		$id = $i[0]->Id;
		
		$k = DB::table('templates')->where('SportsId', '=', $id)
								   ->where('IsActive','=', "TRUE")
								   ->get();
		return $k;
	}

	function selectStates() // display all data from database
	{
		$i = DB::table('states')->get();
		return $i;
	}

	function selectAvailableNumbers($id) //
	{
		$i = DB::table('orders')->where('TeamId', '=', $id)->get();
		return $i;
	}

	function selectSizes() //
	{
		$i = DB::table('sizes')->select('Size')->orderby('Ordering', 'ASC')->get();
		return $i;
	}

	function selectProducts() //
	{
		$i = DB::table('products')->orderby('Ordering', 'ASC')->get();
		return $i;
	}

	function insertToCart($data){

		$i = DB::table('cart_tmp')->insert($data);
		return $i;
	}

	function cartCount($cartkey){ // 

		$i = DB::table('cart_tmp')
								->whereNull('VoucherId')
								->where('CartKey', '=', $cartkey)
								->sum('Quantity');

		return $i;

	}

	function getProduct($ProductURL)
	{

		$i = DB::table('products')->where('ProductURL', $ProductURL)->first();
   		return $i;
	}

	function myCart($cartKey){

		if($cartKey != ""){

			$i = DB::table('cart_tmp')->select('cart_tmp.Id', 'cart_tmp.CartKey', 'teams.GradeLevel', 'teams.Team', 'cart_tmp.Name', 'cart_tmp.Number', 'cart_tmp.Size', 'cart_tmp.Quantity', 'products.ProductImage', 'products.ProductName', 'products.ProductPrice', 'products.ProductURL')
							          ->leftjoin('products', 'cart_tmp.ProductURL','=','products.ProductURL')
							          ->leftjoin('teams', 'cart_tmp.TeamId','=','teams.Id')
							          ->where('cart_tmp.CartKey','=',$cartKey)
							          ->get();
   			return $i;
		}
	}

	function removeItem($id){

		$i = DB::table('cart_tmp')->where('Id', $id)->delete();
		return $i;
	}

	function insertRegistrantInfo($info){

		$i = DB::table('registrant_info')->insert($info);
		return $i;
	}

	function selectPurchaseProduct($cartKey){


		$i = DB::table('cart_tmp')->select('cart_tmp.CartKey', 'products.ProductCategory', 'products.ProductImage', 'products.ProductName', 'products.ProductPrice',  DB::raw('CONCAT(teams.GradeLevel," - ", teams.GradeLevel) AS Team'), 'cart_tmp.TeamId','cart_tmp.Name', 'cart_tmp.Number', 'sizes.Ordering',  'cart_tmp.Size')
							          ->leftjoin('products', 'cart_tmp.ProductURL','=','products.ProductURL')
							          ->leftjoin('teams', 'cart_tmp.TeamId','=','teams.Id')
							          ->leftjoin('sizes', 'cart_tmp.Size','=','sizes.Size')
							          ->orderby('sizes.Ordering', 'ASC')
							          ->orderby('cart_tmp.Number', 'ASC')
							          ->orderby('cart_tmp.Name', 'ASC')
							          ->where('cart_tmp.CartKey','=',$cartKey)
							          ->get();
		return $i;
	}

	function insertPurchaseProduct($orders){

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


	function flushCart($cartKey){

		$i = DB::table('cart_tmp')->where('CartKey', $cartKey)->delete();
		return $i;
	}

	function selectCategory($id){
		$i = DB::table('template_categories')
											->whereIn('Id', $id)
											->get();
		return $i;
	}

	function selectTemplatesByCategory($url, $cat) //
	{
		$i = DB::table('sports')->where('URL', '=', $url)->get();
		$id = $i[0]->Id;
		
		$k = DB::table('templates')->where('SportsId', '=', $id)
								   ->where('Category','=', $cat)
								   ->where('IsActive','=', "TRUE")
								   ->get();
		return $k;
	}


	function selectCategoryName($tempCode){
		$i = DB::table('templates')->select('templates.TemplateCode', 'template_categories.Category','category_ports.Port')
									  ->leftjoin('template_categories', 'templates.Category','=','template_categories.Id')
									  ->leftjoin('category_ports', 'template_categories.Id','=','category_ports.CategoryId')
							          ->where('templates.TemplateCode','=',$tempCode)
									  ->get();
		return $i;
	}
	
}