diff options
Diffstat (limited to 'html/app/models/market')
| -rw-r--r-- | html/app/models/market/Market_repository.php | 235 | ||||
| -rw-r--r-- | html/app/models/market/ProductCategories_model.php | 199 | ||||
| -rw-r--r-- | html/app/models/market/Product_model.php | 49 |
3 files changed, 483 insertions, 0 deletions
diff --git a/html/app/models/market/Market_repository.php b/html/app/models/market/Market_repository.php new file mode 100644 index 0000000..1338426 --- /dev/null +++ b/html/app/models/market/Market_repository.php @@ -0,0 +1,235 @@ +<?php + +namespace app\models\market; + +use \Repository; + +/** Market_repository + * (thematic-entity repository) + * --- + * SQL entity templates for Market + * + * Every '_base' is a repository of common 'prepare' SQL-queries; + * They usually include holders for attaching/binding data safely; + * These queries are expensive in syntax but fast on run time and + * are used to collect multi-joined amounts ot data. + * + * Organize yous repositories in logical/thematic groups (partitioning). + * This way your repository is managable and uses very few sources. + * + * This class acts like a namespaced repository for geting your code readable + * Avoid pushing very simple queries into repository classes. A record like... + * 'CARS_by:Age' => "SELECT * FROM cars WHERE age = :Age" + * ... won't make your code sexier nor easier to read; it will just increase + * your label's entropy and make trickier to pick your next sensable label; + * + * repository classes include two methods (inherited by the abstract, so + * you do not need to implement something more than just construct + * the $repository array) + * ** pull: for pulling a query by a friendly name + * ** echo: optional method used by some devolopment tools + * + * TODO: development tool using echo is still in progress + */ +class Market_repository extends Repository +{ + //// use Repository; // repository trait includes + //// // a method to pull an entity of the gathering + //// // and method to (TODO:) ... + + /** repository is an array of Prepare SWL queries; + * each query is attached to the array via a friendly label; + * + * NOTE: (PROPOSAL) + * --- + * about the label: + * 1. keep names short descriptive but not too long + * 2. main entity/entities shall be UPPERCASED + * 3. determinands/adjectives shall be Cappitalized + * 4. words are connected with underscore (_) + * 5. if SQL inludes bind-holderd, label shall be suffixed with '_by:' + * followed by holder names connected with undercores (case-sensitively) + * + * about the data-bind holders (if present): + * 1. all holders are prefixed with ':' + * 2. holder name shall begin with a letter and contain one word. + * + * example: + * 'Active_EMPLOYEES_by:Age_CityID' => "SELECT Em.*, c.* + * FROM employee Em LEFT JOIN city c ON c.id = Em.city_id + * WHERE c.id = :CityID AND Em.age = :Age" + * + * It is recommended to leave a descriptive comment befor each declaration + * and you may also include sql-comments inside the query. + */ + public static $repository = [ + + // PRODUCTS by: storeID, ProductUrl + // picks all product properties + // + default image + price for certain store + 'Active_PRODUCT_by:storeID_ProductUrl' => + "SELECT p.*, + pc.ID AS ProductCategory, + pc.Hierarchy, + pc.FullFriendlyUrl AS `Path`, + prices.ProductPrice_OriginalPrice AS Price, + prices.ProductPrice_DiscountedPrice AS DiscountPrice, + prices.UnitMeasurePrice_OriginalPrice AS PerUnitPrice, + prices.UnitMeasurePrice_DiscountedPrice AS PerUnitDiscountPrice, + b.Title BrandName + FROM products p + LEFT JOIN products_to_product_categories p2pc ON p.ID = p2pc.SimpleProductID + LEFT JOIN product_categories pc ON p2pc.ProductCategoryID = pc.ID + LEFT JOIN prices ON p.SKU = prices.SKU + LEFT JOIN products_to_images p2i ON p2i.SimpleProductID = p.ID + LEFT JOIN assets ON assets.ID = p2i.ImageID + LEFT JOIN brands b ON b.ID = p.BrandID + WHERE pc.IsActive = 1 AND pc.IsCurrentlyActive = 1 + AND p2i.Order = 1 + AND p.IsActive = 1 + AND p.Published = 1 + AND prices.PriceListID = :storeID + AND p.FriendlyUrl = :ProductUrl" + , + + // PRODUCTS by: storeID, ProductID + // picks all product properties + // + default image + price for certain store + 'Active_PRODUCT_by:storeID_ProductID' => + "SELECT p.*, + pc.ID AS ProductCategory, + pc.Hierarchy, + pc.FullFriendlyUrl AS `Path`, + prices.ProductPrice_OriginalPrice AS Price, + prices.ProductPrice_DiscountedPrice AS DiscountPrice, + prices.UnitMeasurePrice_OriginalPrice AS PerUnitPrice, + prices.UnitMeasurePrice_DiscountedPrice AS PerUnitDiscountPrice, + b.Title BrandName + FROM products p + LEFT JOIN products_to_product_categories p2pc ON p.ID = p2pc.SimpleProductID + LEFT JOIN product_categories pc ON p2pc.ProductCategoryID = pc.ID + LEFT JOIN prices ON p.SKU = prices.SKU + LEFT JOIN products_to_images p2i ON p2i.SimpleProductID = p.ID + LEFT JOIN assets ON assets.ID = p2i.ImageID + LEFT JOIN brands b ON b.ID = p.BrandID + WHERE pc.IsActive = 1 AND pc.IsCurrentlyActive = 1 + AND p2i.Order = 1 + AND p.IsActive = 1 + AND p.Published = 1 + AND prices.PriceListID = :storeID + AND p.ID = :ProductID" + , + + // CATEGORY-PRODUCTS by: storeID, likeHierarchy + // traverses all products from category and subcategories + // picke price and default image + 'CATEGORY-PRODUCTS_by:storeID_likeHierarchy' => + "SELECT p.*, + assets.Url AS ImageUrl, + pc.FullFriendlyUrl AS `Path`, + prices.ProductPrice_OriginalPrice AS Price, + prices.ProductPrice_DiscountedPrice AS DiscountPrice, + prices.UnitMeasurePrice_OriginalPrice AS PerUnitPrice, + prices.UnitMeasurePrice_DiscountedPrice AS PerUnitDiscountPrice + FROM products p + LEFT JOIN prices ON p.SKU = prices.SKU + LEFT JOIN products_to_product_categories p2pc ON p2pc.SimpleProductID = p.ID + LEFT JOIN product_categories pc ON pc.ID = p2pc.ProductCategoryID + LEFT JOIN products_to_images p2i ON p2i.SimpleProductID = p.ID + LEFT JOIN assets ON assets.ID = p2i.ImageID + WHERE p2pc.ProductCategoryID IN ( + SELECT `ID` FROM product_categories + WHERE Hierarchy LIKE :likeHierarchy + ) + AND p2i.Order = 1 + AND p.IsActive = 1 + AND p.Published = 1 + AND prices.PriceListID = :storeID" + , + + // Count Products + // of all last-lever Product categories + // by StoreID + // --- + // used on creating product_categories tree + // ProductCategories_model::tree() + 'Count_Last_Level_PRODUCTS_by:storeID' => + "SELECT pc.ID, COUNT(p.ID) as `Counter` + FROM product_categories pc + LEFT JOIN products_to_product_categories p2pc ON pc.ID = p2pc.ProductCategoryID + LEFT JOIN products p ON p2pc.SimpleProductID = p.ID + LEFT JOIN prices ON p.SKU = prices.SKU + LEFT JOIN products_to_images p2i ON p2i.SimpleProductID = p.ID + LEFT JOIN assets ON assets.ID = p2i.ImageID + WHERE pc.IsActive = 1 AND pc.IsCurrentlyActive = 1 + AND p2i.Order = 1 + AND pc.Level = 2 + AND p.IsActive = 1 + AND p.Published = 1 + AND prices.PriceListID = :storeID + GROUP BY pc.ID" + , + + // PRODUCT by: ProductID + // Join images array (json string) + // Join prices for all stores (json string) + 'Active_PRODUCT_complete_by:ProductID' => + "SELECT p.*, pc.ID AS ProductCategory, + pc.Hierarchy, pc.FullFriendlyUrl AS `Path`, + ( -- get all store prices as Json + SELECT JSON_ARRAYAGG(JSON_OBJECT( + 'Store', prices.PriceListID, + 'Price', prices.ProductPrice_OriginalPrice, + 'DiscountPrice', prices.ProductPrice_DiscountedPrice, + 'PerUnitPrice', prices.UnitMeasurePrice_OriginalPrice, + 'PerUnitDiscountPrice', prices.UnitMeasurePrice_DiscountedPrice + )) + FROM prices + WHERE prices.SKU = p.SKU + ) as pricesJson, + ( -- get all images as Json + SELECT JSON_ARRAYAGG(JSON_OBJECT( + 'Order', p2i.Order, + 'Url', assets.Url, + 'Title', assets.Title, + 'Description', assets.Description + )) + FROM products_to_images p2i + LEFT JOIN assets ON assets.ID = p2i.ImageID + WHERE p2i.SimpleProductID = p.ID + ) AS imagesJson, + b.Title BrandName + FROM products p + LEFT JOIN products_to_product_categories p2pc ON p.ID = p2pc.SimpleProductID + LEFT JOIN product_categories pc ON p2pc.ProductCategoryID = pc.ID + LEFT JOIN products_to_images p2i ON p2i.SimpleProductID = p.ID + LEFT JOIN assets ON assets.ID = p2i.ImageID + LEFT JOIN brands b ON b.ID = p.BrandID + WHERE pc.IsActive = 1 AND pc.IsCurrentlyActive = 1 + AND p2i.Order = 1 + AND p.IsActive = 1 + AND p.Published = 1 + AND p.ID = :ProductID" + , + + // Product images by: ProductID + // (sorted by 'order' field) + 'PRODUCT_IMAGES_by:productID' => + "SELECT a.Url, a.Title, a.Description + FROM products p + LEFT JOIN products_to_images p2i ON p2i.SimpleProductID = p.ID + LEFT JOIN assets a ON a.ID = p2i.ImageID + WHERE p.IsActive = 1 + AND p.Published = 1 + AND p.ID = :productID + ORDER BY p2i.Order" + + ]; + + /* no need to impement anything else; + * you can overide if needed the default methods: + * + public static function pull($entity){ } + * + public static function echo($content =false){ } + */ + +} diff --git a/html/app/models/market/ProductCategories_model.php b/html/app/models/market/ProductCategories_model.php new file mode 100644 index 0000000..d8c274e --- /dev/null +++ b/html/app/models/market/ProductCategories_model.php @@ -0,0 +1,199 @@ +<?php + +namespace app\models\market; + +use \Registry; +use app\models\market\Market_repository; + + +/** ProductCategories_model + * --- + * undertakes to collect any product_categories data + * from the database and prepare needed data structures. + * + */ +class ProductCategories_model +{ + + /** Get (product_) category from (full-friendly_) URL + * --- (self explanatory) + * @param $url (string): Full-Friendly-URL + * @return $category (array); also includes all category products + */ + public static function categoryFromUrl($url) + { + $db = Registry::use('database'); + $category = $db->query( "SELECT * FROM product_categories + WHERE FullFriendlyUrl = :url AND IsActive = 1", + [ ':url' => $url ] + )->getFirst(); + + if ($category === false) { return false; } // no category? -> false + + // GET PRODUCTS of category + $category['products'] = self::categoryProductsByHierarchy($category['Hierarchy']); + return $category; + } + + + /** Get (product_) category from ID + * --- (self explanatory) + * @param $id (int): Catgory ID (usualy from products) + * @return $category (array); also includes all category products + */ + public static function categoryFromID($id) + { + return Registry::use('database')->query( "SELECT * FROM product_categories + WHERE `ID` = :url AND IsActive = 1", + [':id' => $id] + )->getFirst(); + + } + + + /** categoryProductsByHierarchy + * --- + * This method returns the products belonging to a certain + * category (and all it's the children/sub-categories); + * It uses category.Hierarchy in a WHERE LIKE condition to + * make it fast. + * + * The method is category.Level agnostic + * + * @param $category (array) + * @return $products (array) + */ + public static function categoryProductsByHierarchy($hierarchy, $store = 904) + { + // GET PRODUCTS of category + // NOTE: + // category.Hierarchy is used + + return Registry::use('database')->runQuery( + Market_repository::pull('CATEGORY-PRODUCTS_by:storeID_likeHierarchy'), + [ + ':likeHierarchy' => $hierarchy.'%', + ':storeID' => 904 + ] + ); + } + + + # depricated: + # now Proxy has the responsibility to cache whatever + # --- + # /** GET tree of product_categories + # * --- + # * get from cache or cache it after creation + # */ + # public static function tree($store = 904) + # { + # $cacheKey = get_called_class() . $store .'/tree'; + # + # // IF category CACHED + # $cache = Registry::use('cache'); + # $tree = $cache->get($cacheKey); + # if ($tree !== false) { + # return $tree; + # } + # + # // (not cached) Create and Cache it + # $tree = self::createCategoriesTree(); + # $cache->set($cacheKey, $tree, CACHE_ROOT_TTL); + # + # return $tree; + # } + + + /** CREATE product_categories tree + * NOTE: includes active categories only; + * + * Algorith uses 'Hierarchy' for category path-positioning, + * implementing a linear conctruction of the requested tree; + * (faster and less source-consuming than a recursive algo) + */ + public static function tree($store = 904) + { + $tree = []; // variable to hold results + + $db = Registry::use('database'); + + // get raw categories data --------------------------------------------- + + $raw = $db->runQuery( "SELECT `ID`, Title, `Level`, Hierarchy, + ParentID, `Order`, FullFriendlyUrl + FROM product_categories pc + WHERE IsActive = 1 AND IsCurrentlyActive = 1 + ORDER BY pc.Level asc, pc.Order asc, pc.Hierarchy asc", + [] + ); + + // count products per 3rd level category ------------------------------- + + $countProducts = $db->runQuery( + Market_repository::pull('Count_Last_Level_PRODUCTS_by:storeID'), + [ ':storeID' => $store ] + ); + // map categoryID -> Counter + $mapCounter = []; + foreach($countProducts as $rec) $mapCounter[$rec['ID']] = $rec['Counter']; + + // parse raw data to hierarchical tree (2 pass) ------------------------ + + // 1st pass: parse raw data, construct and fill the $tree array ........ + foreach($raw as $rec) { + + // create category path from Hierarchy (format: .10.10100. ) + // so remove 1st and last dots (.) from Hierarchy string + // then explode via dot-character (.) + $categoryPath = explode('.', substr($rec['Hierarchy'], 1, -1)); + + switch ($rec['Level']) { + case 0: + $tree[$categoryPath[0]] = [ + 'info' => $rec, + 'childs' => [] + ]; break; + + case 1: + $tree[$categoryPath[0]]['childs'][$categoryPath[1]] = [ + 'info' => $rec, + 'childs' => [] + ]; break; + + case 2: + $tree[$categoryPath[0]]['childs'][$categoryPath[1]]['childs'][$categoryPath[2]] = [ + 'info' => $rec, + 'count' => isset($mapCounter[$categoryPath[2]]) ? $mapCounter[$categoryPath[2]] : 0 + ]; break; + + default: + // nothing... + } + + } + + // 2nd pass: remove categories with no products ........................ + foreach($tree as $id0 => $rec0) { + $count0 = 0; + + foreach($rec0['childs'] as $id1 => $rec1) { + $count1 = 0; + + foreach($rec1['childs'] as $id2 => $rec2) { + if ($rec2['count']>0) $count1++; + else unset($tree[$id0]['childs'][$id1]['childs'][$id2]); + } + + if ($count1 > 0) $count0++; + else unset($tree[$id0]['childs'][$id1]); + } + + if (!$count0) unset($tree[$id0]); + } + + return $tree; + } + + +}
\ No newline at end of file diff --git a/html/app/models/market/Product_model.php b/html/app/models/market/Product_model.php new file mode 100644 index 0000000..aa554a1 --- /dev/null +++ b/html/app/models/market/Product_model.php @@ -0,0 +1,49 @@ +<?php + +namespace app\models\market; + +use \Registry; +use app\models\market\Market_repository as Market; + +class Product_model +{ + public static function fromUrl($url, $store = 904) + { + $product = Registry::use('database')->query( + Market::pull('Active_PRODUCT_by:storeID_ProductUrl'), + [ + ':storeID' => $store, + ':ProductUrl' => $url + ] + )->getFirst(); + + // if no product, return false + if ($product === false) return false; + + // inject product's images + $product['Images'] = self::getProductImages($product['ID']); + + return $product; + } + + + public static function getProductImages($id) + { + ## return Registry::use('database')->runQuery( + ## "SELECT a.Url, a.Title, a.Description + ## FROM products p + ## LEFT JOIN products_to_images p2i ON p2i.SimpleProductID = p.ID + ## LEFT JOIN assets a ON a.ID = p2i.ImageID + ## WHERE p.IsActive = 1 AND p.Published = 1 + ## AND p.ID = :id + ## ORDER BY p2i.Order", + ## [ ':id' => $id ] + ## ); + + return Registry::use('database')->runQuery( + Market::pull('PRODUCT_IMAGES_by:productID'), + ['productID' => $id ] + ); + } + +}
\ No newline at end of file |