<?php

if (!defined('BASEPATH'))
    exit('No direct script access allowed');

class Menu extends CI_Controller {

    function __construct() {

        parent::__construct();
        $this->load->library('Formatslist');
    }
	
    public function categories()
    {
        
         // Query to fetch categories, subcategories, and products
        // $query = $this->db->query("SELECT c1.ID AS category_id, c1.NAME AS category_name, c1.PARENTID AS parent_id,
        //        c2.NAME AS parent_name
        //     FROM CATEGORIES c1
        //     LEFT JOIN CATEGORIES c2 ON c1.PARENTID = c2.ID ORDER BY parent_id, category_name");

								 
        $query = $this->db->query("SELECT c1.ID AS category_id, c1.NAME AS category_name, c1.PARENTID AS parent_id,
        c2.NAME AS parent_name
         FROM CATEGORIES c1
         LEFT JOIN CATEGORIES c2 ON c1.PARENTID = c2.ID
         ORDER BY CONCAT(c1.PARENTID, '_', c1.NAME), c1.NAME ASC");
 

        $categories = array();

        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $category_id = $row->category_id;
                $category_name = $row->category_name;
                $parent_id = $row->parent_id;
                $parent_name = $row->parent_name;

                if (!isset($categories[$category_id])) {
                    $categories[$category_id] = array(
                        "id" => $category_id,
                        "categoryname" => $category_name,
                        "parent_id" => $parent_id,
                        "parent_name" => $parent_name,
                        "subcategories" => array()
                    );
                }
                
                // Check if the parent category exists in $categories
                if ($parent_id !== null && !isset($categories[$parent_id])) {
                    // Create the parent category if it doesn't exist
                    $categories[$parent_id] = array(
                        "id" => $parent_id,
                        "categoryname" => $parent_name,
                        "parent_id" =>null,
                        "parent_name" =>null,
                        "subcategories" => array()
                    );
                }

                if ($parent_id !== null) {
                    $categories[$parent_id]["subcategories"][] = &$categories[$category_id];
                }

                //  Add the current category as a subcategory of its parent
                // if ($parent_id !== null && !in_array($category_id, array_column($categories[$parent_id]["subcategories"], "id"))) {
                //     $categories[$parent_id]["subcategories"][] = &$categories[$category_id];
                // }
                
            }
            
        // Find the root categories (categories with no parent)
        $rootCategories = array();

        foreach ($categories as $categoryId => $category) {
            if (isset($category["id"])) {
                $isDirectChild = false;

                // Check if the category is a direct child of another category
                foreach ($categories as $otherCategoryId => $otherCategory) {
                    if (isset($otherCategory["subcategories"]) && in_array($category["id"], array_column($otherCategory["subcategories"], "id"))) {
                        $isDirectChild = true;
                        break;
                    }
                }

                // Only add the category if it's not a direct child of another category
                if (!$isDirectChild) {
                    if ($category["parent_id"] === null) {
                        $rootCategories[] = $category;
                    }
                }
            }
        }

        // Convert the root categories to JSON
        $jsonData = json_encode(array("data" => $rootCategories), JSON_PRETTY_PRINT);

        // Output the JSON data
        $this->output
            ->set_content_type('application/json')
            ->set_output($jsonData);
        }
        else
        {
                   // Convert the root categories to JSON
        $jsonData = json_encode(array("data" => $categories), JSON_PRETTY_PRINT);

        // Output the JSON data
        $this->output
            ->set_content_type('application/json')
            ->set_output($jsonData);

        }
        
       
    }
    
    public function list()
    {
        $jsonStr = file_get_contents('php://input');
    $jsonObj = json_decode($jsonStr);
    if($jsonObj!=null && $jsonObj->category !="")
    {
        return $this->getmenu("SELECT * FROM PRODUCTS WHERE CATEGORY LIKE '". $jsonObj->category."' ORDER BY NAME");
    }
    else
    {
        return $this->getmenu("SELECT * FROM PRODUCTS WHERE CATEGORY LIKE '". $jsonObj->category."' ORDER BY NAME");
    }
    }

    public function getmenu($productsql)
    {

        // // Query to fetch categories, subcategories, and products
        // $query = $this->db->query("SELECT c1.ID AS category_id, c1.NAME AS category_name, c1.PARENTID AS parent_id,
        //        c2.NAME AS parent_name
        //     FROM categories c1
        //     LEFT JOIN categories c2 ON c1.PARENTID = c2.ID");

        // $categories = array();

        // if ($query->num_rows() > 0) {
        //     foreach ($query->result() as $row) {
        //         $category_id = $row->category_id;
        //         $category_name = $row->category_name;
        //         $parent_id = $row->parent_id;
        //         $parent_name = $row->parent_name;

        //         if (!isset($categories[$category_id])) {
        //             $categories[$category_id] = array(
        //                 "id" => $category_id,
        //                 "categoryname" => $category_name,
        //                 "parent_id" => $parent_id,
        //                 "parent_name" => $parent_name,
        //                 "subcategories" => array(),
        //                 "items" => array(),
        //             );
        //         }

        //         if ($parent_id !== null) {
        //             $categories[$parent_id]["subcategories"][] = &$categories[$category_id];
        //         }
        //     }
        // }

        // Query to fetch products
        $query = $this->db->query($productsql);
        $products = array();
        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {

                $product_id = $row->ID;
                $product_name = $row->NAME;
                $category_id = $row->CATEGORY;


                $sendstatus="No";
                $kotnum="KOT1";
                if($row->ATTRIBUTES!=null && !empty($row->ATTRIBUTES))
                {
                    libxml_use_internal_errors(true);
                $xml = simplexml_load_string( $row->ATTRIBUTES);
                if($xml!==false){
				
                foreach($xml->entry as $a => $b) {
                    
                     foreach($b->attributes() as $c => $d) {
                          
                    if($d=="kotnum")
                    {
                       $kotnum= (string)$b;
                    }
                    if($d=="sendstatus")
                    {
                       $sendstatus= (string)$b;
                    }
                         
                     }
                    
              
 
                }
            }
                }
                // $tariffprice = null;
                // if($tariffarea!="")
                // {

                    $tariffquery = $this->db->query("SELECT TARIFFID,FORMAT(PRICESELL, 2) AS PRICESELL  FROM TARIFFAREAS_PROD WHERE PRODUCTID LIKE '".$row->ID ."'");
                    $tariffprices = $tariffquery->result();

                    // if ($tariffrow) {
                    //     $tariffprice=$tariffrow->PRICESELL;
                    // }
                    // else{
                    //     $tariffprice=null;
                    // }
                //}
                $products[]["items"][] = array(
                            'id' => $row->CODE,
                            "name" => $product_name,
                            'menu_number' => $row->CODE,
                            'unit_price' => str_replace(',', '', number_format($this->calculatetax($row->ID), 2)),
                            'tariff_price' => $tariffprices,
                            'currency_code' => 'AED',
                            'sendStatus' => $sendstatus,
                            'kotNum' => $kotnum,
                            'attributes' => $row->ATTRIBUTES,
                            'variations' => $this->getvariations($row->ID),
                            'options' => $this->getoptions($row->ID)

                );

                
            }
        }

//         // Find the root categories (categories with no parent)
//         $rootCategories = array();

//         foreach ($categories as $categoryId => $category) {
//             if(isset($category["id"]))
//             {
//             if ($category["parent_id"] === null) {
//                 $rootCategories[] = $category;
//             }
//     }
// }
        

        // Convert the root categories to JSON
        $jsonData = json_encode(array("data" => $products), JSON_PRETTY_PRINT);

        // Output the JSON data
        $this->output
            ->set_content_type('application/json')
            ->set_output($jsonData);


    }
    
    public function gettariff() {
		
		
							 
						  
						   
							
            
            
				 
				
				 
        $sql = "SELECT * FROM TARIFFAREAS ORDER BY TARIFFORDER, NAME";
$arr = $this->db->query($sql)->result();

$data=array();
$i=0;
if ($arr != '') {
            foreach ($arr as $key => $aRow) {
                $i++;
								 
							   
					  
				 
								 
				
						 
					
							  
						  
									
					 
					  
					 
										
					 
						
					 
						 
					  
					
			  
 
				 
				

                array_push($data,
                array(
                    'id' => $aRow->ID, 
                 'name' => $aRow->NAME
							 
																												   
						   
							
						
							   
																			
								   
                ));
            }
}
						  
							   
					  
					  
						   
			 
		 
				 
			  
			
		 


        

//add the header here
header('Content-Type: application/json');
echo json_encode(array("data"=>$data));
        
        
    }




    public function search() {
        $jsonStr = file_get_contents('php://input');
        $jsonObj = json_decode($jsonStr);
        $query = $jsonObj->query;
    
										   
		 
			
		 
						  
		 
        $sql = "SELECT * FROM PRODUCTS WHERE NAME LIKE '%" . $query . "%' ORDER BY NAME;";

									  
			   
        $arr = $this->db->query($sql)->result();

        $data = array();
    
        if ($arr != '') {
            foreach ($arr as $aRow) {
                $sendstatus = "No";
                $kotnum = "KOT1";

                
            
							   
                if ($aRow->ATTRIBUTES != null && !empty($aRow->ATTRIBUTES)) {
                    libxml_use_internal_errors(true);
                    $xml = simplexml_load_string($aRow->ATTRIBUTES);
                    if ($xml !== false) {
                        foreach ($xml->entry as $b) {
					
                            foreach ($b->attributes() as $c => $d) {
						  
                                if ($d == "kotnum") {
                                    $kotnum = (string)$b;
                                }
                                if ($d == "sendstatus") {
                                    $sendstatus = (string)$b;
                                }
                            }
                        }
                    }
										
					 
											   
					 
						 
					  
					
			  
 
                }
    
									  
                $tariffquery = $this->db->query("SELECT TARIFFID, FORMAT(PRICESELL, 2) AS PRICESELL FROM TARIFFAREAS_PROD WHERE PRODUCTID LIKE '" . $aRow->ID . "'");
                $tariffprices = $tariffquery->result();
    
                $data[] = array(
															 
							
											
					 
				 

								 
                    'id' => $aRow->CODE,
                    'name' => $aRow->NAME,
                    'menu_number' => $aRow->CODE,
                    'unit_price' => str_replace(',', '', number_format($this->calculatetax($aRow->ID), 2)),
                    'tariff_price' => $tariffprices,
														   
                    'currency_code' => 'AED',
                    'sendStatus' => $sendstatus,
                    'kotNum' => $kotnum,
                    'attributes' => $aRow->ATTRIBUTES,
                    'variations' => $this->getvariations($aRow->ID),
                    'options' => $this->getoptions($aRow->ID)
                );
            }
        }
    
							 
        header('Content-Type: application/json');
        echo json_encode(array("data" => $data));
    }
    


    
    
    public function searchcode() {


        $jsonStr = file_get_contents('php://input');
        $jsonObj = json_decode($jsonStr);
        $query = $jsonObj->code;
    
										   
		 
			
		 
						  
		 
        $sql = "SELECT * FROM PRODUCTS WHERE CODE LIKE '" . $query . "' ORDER BY NAME;";

		
									  
			   

        $arr = $this->db->query($sql)->result();

        $data = array();
    
        if ($arr != '') {
            foreach ($arr as $aRow) {
				
                $sendstatus = "No";
                $kotnum = "KOT1";
    
                if ($aRow->ATTRIBUTES != null) {
				 
                    $xml = simplexml_load_string($aRow->ATTRIBUTES);
    
                    foreach ($xml->entry as $b) {
					
                        foreach ($b->attributes() as $c => $d) {
						  
                            if ($d == "kotnum") {
					 
                                $kotnum = (string)$b;
                            }
                            if ($d == "sendstatus") {
					 
                                $sendstatus = (string)$b;
					 
						 
					  
					
			  
 
                            }
									
								 
				 

																																							   
													 

									 
														   
                        }
						 
										  
                    }
                }
    
                $tariffquery = $this->db->query("SELECT TARIFFID, FORMAT(PRICESELL, 2) AS PRICESELL FROM TARIFFAREAS_PROD WHERE PRODUCTID LIKE '" . $aRow->ID . "'");
                $tariffprices = $tariffquery->result();
    
                $data[] = array(
                    'id' => $aRow->CODE,
                    'name' => $aRow->NAME,
                    'menu_number' => $aRow->CODE,
                    'unit_price' => str_replace(',', '', number_format($this->calculatetax($aRow->ID), 2)),
																																	 
                    'tariff_price' => $tariffprices,
                    'currency_code' => 'AED',
                    'sendStatus' => $sendstatus,
                    'kotNum' => $kotnum,
                    'attributes' => $aRow->ATTRIBUTES,
                    'variations' => $this->getvariations($aRow->ID),
                    'options' => $this->getoptions($aRow->ID)
                );
            }
        }
    
							 
        header('Content-Type: application/json');
        echo json_encode(array("data" => $data));
    }
    
    


    public function category() {
        $sql = "SELECT * FROM CATEGORIES ORDER BY NAME;";
        $arr = $this->db->query($sql)->result();
        $data = array();
    
        if ($arr != '') {
            foreach ($arr as $key => $aRow) {

                array_push($data,
                        array('id' => $aRow->ID,
                            'name' => $aRow->NAME
                ));
            }
        }

        //add the header here
        header('Content-Type: application/json');
        echo json_encode(array("data" => $data));
    }
    
    public function calculatetax($pid){
        
        $sql = "SELECT PRICESELL, TAXCAT FROM PRODUCTS WHERE ID LIKE '" . $pid . "';";
        $arr = $this->db->query($sql)->result();
        
        $sql = "SELECT RATE, RATECASCADE, RATEORDER FROM TAXES WHERE CATEGORY LIKE '" . $arr[0]->TAXCAT . "' AND PARENTID IS NULL;";
         $arrtax = $this->db->query($sql)->result();
         $tax1 = 0;
        $tax2 = 0;
        $tax3=0;
        if($arr[0]->PRICESELL >0)
            {
        foreach($arrtax as $key => $aRow)
        {
            $rate = $aRow->RATE;
            if($rate != 0) {
                if($aRow->RATECASCADE == 0)
                {
                    $tax1 = $tax1 + ($arr[0]->PRICESELL * $rate);
                }
                else if($aRow->RATECASCADE != 0)
                {
                    $tax2 = ($tax1+$arr[0]->PRICESELL) * $rate;
                }
                    $tax3 = $tax1 + $tax2;
                
            }
            else {
                $tax3=0;
            }
        }
        return $this->formatslist->formatCurrencywithHash($arr[0]->PRICESELL+$tax3);
    }
 else {
        return $this->formatslist->formatCurrencywithHash(0);
    }
}

public function getvariations($pid)
{
    $sqlcom = "SELECT PRODUCTS.ID, PRODUCTS.CODE, PRODUCTS.NAME, PRODUCTS.PRICESELL from PRODUCTS JOIN PRODUCTS_COM ON PRODUCTS.ID=PRODUCTS_COM.PRODUCT2
 WHERE PRODUCTS_COM.PRODUCT='" . $pid . "';";
                $arrcom = $this->db->query($sqlcom)->result();
                $variations = array();
                if ($arrcom != '') {
                    foreach ($arrcom as $keycom => $aRowcom) {

                        array_push($variations, 
                                array('id' => $aRowcom->CODE, 
                                    'name' => $aRowcom->NAME, 
                                    'unit_price' => str_replace(',', '', $this->calculatetax($aRowcom->ID)), 
                                    'currency_code' => 'aed'));
                    }
                }
                return $variations;
}


public function getoptions($pid)
{
    $sqlcom = "SELECT PRODUCTS.ID, PRODUCTS.NAME, PRODUCTS.PRICESELL from PRODUCTS JOIN PRODUCTS_COM ON PRODUCTS.ID=PRODUCTS_COM.PRODUCT2
 WHERE PRODUCTS_COM.PRODUCT='" . $pid . "';";
                $arrcom = $this->db->query($sqlcom)->result();
                $options = array();
                
//        array_push($options, array('id' => 2, 'name' => 'Without Sugar', 'unit_price' => "0", 'currency_code' => 'aed'));
//        array_push($options, array('id' => 4, 'name' => 'Extra Cheese', 'unit_price' => "15", 'currency_code' => 'aed'));

//                if ($arrcom != '') {
//                    foreach ($arrcom as $keycom => $aRowcom) {
//
//                        array_push($options, array('id' => $aRowcom->ID, 'name' => $aRowcom->NAME, 'unit_price' => $this->calculatetax($aRowcom->ID), 'currency_code' => 'aed'));
//                    }
//                }
                return $options;
}
}
