skeleton commit; based on an anom project

1 files changed, 235 insertions, 0 deletions

diff --git a/public/app/models/market/Market_repository.php b/public/app/models/market/Market_repository.php
new file mode 100644
index 0000000..1338426
--- /dev/null
+++ b/public/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){ }
+     */
+
+}