ic_store_catalog.php

<?php
function get_catalog_new($column=1, $storeID=0, $parentID=0, $isFront=0) {
        if ($isFront != 0) {
            // information is being pulled from the new front end.
            $newJoin = "LEFT JOIN ic_product_stores as st ON $storeID = st.storeID AND prod.productID = st.productID";
            $extsql = " AND st.oos = 0";
        } else {
            // information is being pulled from the administration panel.
            $newJoin = '';
            $extsql = '';
        }
        if ($storeID != 0) {
            $extrapro = " AND productID IN ( SELECT productID FROM ic_product_stores WHERE storeID = $storeID)";
            $extrasql = " WHERE storeID = $storeID";
        } else {
            $extrapro = '';
            $extrasql = '';
        }
        $query = "SELECT DISTINCT cat.categoryID, cat.catalogID, cat.storeID, cat.categoryID, cat.parentID, group_concat(cat.productID SEPARATOR ',') AS productID,
                  cat.title, cat.column, cat.the_order, cat.cat_order, prod.title AS product, cat1.title AS category,
                  (SELECT DISTINCT GROUP_CONCAT(t.productID SEPARATOR ',') FROM ic_products t WHERE t.productID
                    NOT IN (SELECT productID FROM ic_store_catalog WHERE storeID=$storeID AND categoryID=cat.categoryID)
                    AND productID IN (SELECT productID FROM ic_product_category_rel WHERE categoryID=cat.categoryID) $extrapro
                  )  AS missing_products,
                  (SELECT DISTINCT GROUP_CONCAT(categoryID SEPARATOR ',') FROM ic_product_categories WHERE categoryID NOT IN(
		    SELECT DISTINCT categoryID FROM ic_store_catalog WHERE storeID=$storeID) AND categoryID IN (
		        SELECT categoryID FROM ic_product_category_rel WHERE productID IN(
		            SELECT productID FROM ic_product_stores $extrasql)) AND parentID=cat.categoryID) AS missing_categories
                  FROM ic_store_catalog AS cat
                  LEFT JOIN ic_products as prod ON cat.productID = prod.productID
                  LEFT JOIN ic_product_categories AS cat1 ON cat.categoryID = cat1.categoryID
                  WHERE cat.`storeID` = $storeID AND cat.`column` = $column AND cat.`parentID` = 0 GROUP BY cat.categoryID ORDER BY cat.`cat_order` ASC";

        $result = $this->db->Query($query);
        $result = $result->result_array();
        $i = 0;
        foreach ($result as $row) {
            
            $query = "SELECT DISTINCT cat.categoryID, cat.catalogID, cat.storeID, cat.categoryID, cat.parentID, group_concat(cat.productID SEPARATOR ',') AS productID,
                  cat.title, cat.column, cat.the_order, cat.cat_order, prod.title AS product, cat1.title AS category,
                  (SELECT DISTINCT GROUP_CONCAT(t.productID SEPARATOR ',') FROM ic_products t WHERE t.productID
                    NOT IN (SELECT productID FROM ic_store_catalog WHERE storeID=$storeID AND categoryID=cat.categoryID)
                    AND productID IN (SELECT productID FROM ic_product_category_rel WHERE categoryID=cat.categoryID) $extrapro
                  )  AS missing_products
                  FROM ic_store_catalog AS cat
                  LEFT JOIN ic_products as prod ON cat.productID = prod.productID
                  LEFT JOIN ic_product_categories AS cat1 ON cat.categoryID = cat1.categoryID
                  WHERE cat.`storeID` = $storeID AND cat.`parentID` = '".$row['categoryID']."' GROUP BY cat.categoryID ORDER BY cat.`cat_order` ASC";

            $query = $this->db->Query($query);
            $result[$i]['children'] = $query->result_array();
            if ($row['missing_products'] != '') {
                $query = "SELECT prod.* FROM ic_products AS prod WHERE prod.productID IN ('$row[missing_products]')";
                $query = $this->db->Query($query);
                $result[$i]['products_missing'] = $query->result_array();
            }

            if ($row['missing_categories'] != '') {
                $query = "SELECT prod.* FROM ic_product_categories AS prod WHERE prod.categoryID IN ('$row[missing_categories]')";
                $query = $this->db->Query($query);
                $result[$i]['categories_missing'] = $query->result_array();
            }

            $query = "SELECT DISTINCT prod.*, cat.the_order, cat.catalogID,
                      (SELECT COUNT(po.productID) FROM ic_product_options AS po WHERE productID = prod.productID) AS options
                      FROM ic_products AS prod
                      LEFT JOIN ic_store_catalog AS cat ON prod.productID = cat.productID
                      $newJoin
                      WHERE prod.productID IN (". $row['productID'].") AND cat.storeID = '$storeID' $extsql GROUP BY prod.productID ORDER BY cat.`the_order` ASC";
            $query = $this->db->Query($query);
            $result[$i]['products'] = $query->result_array();

            $a = 0;
                foreach ($result[$i]['children'] as $row2) {
                    $query = "SELECT DISTINCT prod.*, cat.the_order, cat.catalogID,
                              (SELECT COUNT(po.productID) FROM ic_product_options AS po WHERE productID = prod.productID) AS options
                              FROM ic_products AS prod
                              LEFT JOIN ic_store_catalog AS cat ON prod.productID = cat.productID
                              $newJoin
                              WHERE prod.productID IN (". $row2['productID'].") and cat.storeID = '$storeID' $extsql GROUP BY prod.productID ORDER BY cat.`the_order` ASC";
                    $query = $this->db->Query($query);
                    $result[$i]['children'][$a]['products'] = $query->result_array();

                    if ($row2['missing_products'] != '') {
                        $query = "SELECT prod.* FROM ic_products AS prod WHERE prod.productID IN ('$row2[missing_products]')";
                        $query = $this->db->Query($query);
                        $result[$i]['children'][$a]['products_missing'] = $query->result_array();
                    }

                    $a++;
                }
            $i++;
        }

        return $result;
    }