Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- <?php
- /**
- * Handling build of JSON data for database table row
- * Updating cache column for table row
- */
- namespace App\libs;
- use App\Model\Exceptions\DatabaseCacheUpdateException;
- use App\Web\Paths;
- use Symfony\Component\Config\Definition\Exception\Exception;
- class DatabaseCacheManager
- {
- const DB_USER = "gloffer_test";
- const DB_PASSWORD = "GlofferTest2015";
- const DB_DATABASE = "gloffer";
- const DB_CACHE_USER = "gloffer_test";
- const DB_CACHE_PASSWORD = "GlofferTest2015";
- const DB_CACHE_DATABASE = "gloffer_cache";
- const STORE_TYPE = "single";
- /**
- * @var \mysqli
- */
- private $dbConnection;
- /**
- * @var \mysqli
- */
- private $dbConnectionCache;
- /**
- * @var string
- */
- private $type;
- /**
- * @var int
- */
- private $id;
- /**
- * @var bool
- */
- private $live_record;
- /**
- * @var array
- */
- private $arrayData;
- /**
- * @var bool
- */
- private $needRebuild;
- private $serverAddress;
- private $serverAddressCache;
- public function __construct()
- {
- $this->live_record = FALSE;
- $this->needRebuild = TRUE;
- $this->arrayData = array();
- if(isset($_SERVER["HTTP_HOST"]))
- {
- $this->serverAddress = $_SERVER["HTTP_HOST"] == "158.196.145.34" ? "localhost" : "158.196.145.34";
- }
- else
- {
- $this->serverAddress = "158.196.145.34";
- }
- $this->serverAddressCache = "158.196.145.34";
- $this->dbConnection = mysqli_connect($this->serverAddress, self::DB_USER, self::DB_PASSWORD, self::DB_DATABASE);
- if (mysqli_connect_errno()) {
- //\Tracy\Debugger::log("Couldnt connect to database in DatabaseCacheManager.");
- throw new \Exception("Couldn't connect to database");
- }
- mysqli_query($this->dbConnection, "SET character_set_client=utf8");
- mysqli_query($this->dbConnection, "SET character_set_connection=utf8");
- mysqli_query($this->dbConnection, "SET character_set_results=utf8");
- $this->dbConnectionCache = mysqli_connect($this->serverAddressCache, self::DB_CACHE_USER, self::DB_CACHE_PASSWORD, self::DB_CACHE_DATABASE);
- if (mysqli_connect_errno()) {
- //\Tracy\Debugger::log("Couldnt connect to database in DatabaseCacheManager.");
- throw new \Exception("Couldn't connect to cache database");
- }
- mysqli_query($this->dbConnectionCache, "SET character_set_client=utf8");
- mysqli_query($this->dbConnectionCache, "SET character_set_connection=utf8");
- mysqli_query($this->dbConnectionCache, "SET character_set_results=utf8");
- }
- public function __destruct()
- {
- @$this->dbConnection->close();
- @$this->dbConnectionCache->close();
- }
- public function setHTTPHost($host)
- {
- }
- public function storeCache()
- {
- if($this->needRebuild) {
- $this->buildJsonData();
- }
- return $this->updateCache();
- }
- public function setPublished()
- {
- $query = "UPDATE cac_cache SET cache_status='published' WHERE cac_cache_type='$this->type' AND cac_cache_id='$this->id'";
- $result = mysqli_query($this->dbConnectionCache, $query);
- if(mysqli_error($this->dbConnectionCache)) {
- throw new \Exception("SetPublished [" . $this->id . "] query failed: " . mysqli_error($this->dbConnectionCache));
- }
- return TRUE;
- }
- public function setElastic()
- {
- $query = "UPDATE cac_cache SET cache_status='elastic' WHERE cac_cache_type='$this->type' AND cac_cache_id='$this->id'";
- $result = mysqli_query($this->dbConnectionCache, $query);
- if(mysqli_error($this->dbConnectionCache)) {
- throw new \Exception("setElastic [" . $this->id . "] query failed: " . mysqli_error($this->dbConnectionCache));
- }
- return TRUE;
- }
- public function setWaiting()
- {
- $query = "UPDATE cac_cache SET cache_status='waiting' WHERE cac_cache_type='$this->type' AND cac_cache_id='$this->id'";
- $result = mysqli_query($this->dbConnectionCache, $query);
- if(mysqli_error($this->dbConnectionCache)) {
- throw new \Exception("SetWaiting[" . $this->id . "] query failed: " . mysqli_error($this->dbConnectionCache));
- }
- return TRUE;
- }
- public function setError($cacheReport = NULL)
- {
- if($cacheReport == NULL)
- {
- $query = "UPDATE cac_cache SET cache_status = 'error', cache_report = $cacheReport WHERE cac_cache_type = '$this->type' AND cac_cache_id = $this->id";
- }
- else
- {
- $query = sprintf("UPDATE cac_cache SET cache_status = 'error', cache_report = '%s' WHERE cac_cache_type = '%s' AND cac_cache_id = '%s'", mysqli_real_escape_string($this->dbConnection,$cacheReport), $this->type, $this->id);
- }
- $result = mysqli_query($this->dbConnectionCache, $query);
- if(mysqli_error($this->dbConnectionCache)) {
- throw new \Exception("SetError [" . $this->id . "] query failed: " . mysqli_error($this->dbConnectionCache));
- }
- return TRUE;
- }
- public function setReport($cacheReport)
- {
- $query = sprintf("UPDATE cac_cache SET cache_report = '%s' WHERE cac_cache_type = '%s' AND cac_cache_id = '%s'", mysqli_real_escape_string($this->dbConnection,$cacheReport), $this->type, $this->id);
- $result = mysqli_query($this->dbConnectionCache, $query);
- if(mysqli_error($this->dbConnectionCache)) {
- throw new \Exception("setReport [" . $this->id . "] query failed: " . mysqli_error($this->dbConnectionCache));
- }
- return TRUE;
- }
- public function setRebuild()
- {
- $query = "UPDATE cac_cache SET cache_status='rebuild' WHERE cac_cache_type='$this->type' AND cac_cache_id='$this->id'";
- $result = mysqli_query($this->dbConnectionCache, $query);
- if(mysqli_error($this->dbConnectionCache)) {
- throw new \Exception("SetRebuild [" . $this->id . "] query failed: " . mysqli_error($this->dbConnectionCache));
- }
- return TRUE;
- }
- public function setEmpty()
- {
- $query = "UPDATE cac_cache SET cache_status='empty', cache = NULL, cache_report = NULL WHERE cac_cache_type='$this->type' AND cac_cache_id='$this->id'";
- $result = mysqli_query($this->dbConnectionCache, $query);
- if(mysqli_error($this->dbConnectionCache)) {
- throw new \Exception("SetEmpty [" . $this->id . "] query failed: " . mysqli_error($this->dbConnectionCache));
- }
- return TRUE;
- }
- protected function buildJsonData()
- {
- try
- {
- $this->needRebuild = FALSE;
- if ($this->type == NULL || $this->id == NULL) {
- return;
- }
- // Nastaveni parametru pro vyhledavani, single nebo multi,jen aktivni zaznamy, ukladani do cache a poslani hodnot do rabbitmq
- $pole = array();
- $type = self::STORE_TYPE;
- $origin_id = NULL;
- if ($type == "multi") {
- $id = " id IN ($this->id) ";
- } else {
- $id = " id='$this->id' ";
- $origin_id = $this->id;
- }
- if ($this->live_record) {
- $live = "
- AND active IN ('enabled','new')
- AND visibility NOT IN ('blocked','invisible')";
- $live_tra_object = "
- AND tra_object_visibility NOT IN ('blocked','invisible')";
- } else {
- $live = "";
- $live_tra_object = "";
- }
- /////////////////////////////////////////////////////////////////////////////////////////////////////
- // Zakladní informace o objektu
- $objectBlackList = ['label', 'title', 'description', 'cache', 'content', 'note', 'reaction'];
- $categoryBlackList = ['cat_category_show_parameters',
- 'cat_category_show_bus_auction', 'cat_category_show_description',
- 'cat_category_show_bus_quote', 'cat_category_show_location_map',
- 'cat_category_show_bus_rent', 'cat_category_show_tag', 'cat_category_redirect',
- 'language', 'country', 'destination', 'duration_begin', 'duration_end'];
- $query = "SELECT * FROM $this->type WHERE $id $live ORDER BY id";
- $result = mysqli_query($this->dbConnection, $query);
- if($result === FALSE) {
- throw new \Exception("DatabaseCacheManager false query on type: $this->type [" . $origin_id . "] " . mysqli_error($this->dbConnection));
- }
- if(mysqli_num_rows($result) == FALSE) {
- throw new \Exception("Empty result for type " . $this->type . " and ID: " . $this->id);
- }
- while ($row = mysqli_fetch_assoc($result)) {
- //echo $row['id']."<hr>";
- if ($this->type == "cat_category" || $this->type == "cat_trademark" ||
- $this->type == "cms_article" || $this->type == "cms_structure") {
- $this->remove_elements_from_black_list($row, $categoryBlackList);
- }
- $this->remove_prefix($row, $this->type . "_");
- if ($this->type == "cms_article") {
- $this->remove_prefix($row, "cms_");
- }
- $this->remove_elements_from_black_list($row, $objectBlackList);
- // Připojení názvu tabulky do pole hodnot (pro_product, cat_category, atd...)
- $row = array_merge(['source' => $this->type], $row);
- // $this->changeStringToNumber($row);
- $pole[$row['id']] = $row;
- $pole[$row['id']]['translate'] = array();
- if (isset($row["cat_category_id"]) == TRUE) {
- $pole[$row['id']]['category'] = array();
- }
- //$pole[$row['id']]['property'] = array();
- //$pole[$row['id']]['contact'] = array();
- //$pole[$row['id']]['image'] = array();
- //$pole[$row['id']]['link_in_country'] = array();
- }
- //////////////////////////////////////////////////////////////////////////////////////////////////////
- // Ceny
- $tables_pricing = array("pro_catalog", "pro_product", "pro_watchdog", "pro_request",
- "pro_response", "bus_auction", "bus_quote", "bus_rent", "bus_trade");
- if (in_array($this->type, $tables_pricing)) {
- foreach ($pole[$origin_id] as $key => $value) {
- if (substr($key, 0, 5) == "price" OR $key == "currency") {
- $pole[$origin_id]['pricing'][$key] = $value;
- $this->remove_element($pole[$origin_id], $key);
- }
- if ($key == "changed") {
- $pole[$origin_id]['pricing'][$key] = $value;
- }
- }
- $this->changeStringToNumber($pole[$origin_id]['pricing']);
- }
- //////////////////////////////////////////////////////////////////////////////////////////////////////
- // Rating
- // Rating parameters are between basic parameters in default
- /* $tables_rating = array("pro_catalog", "pro_product", "fir_firm", "fir_branch",
- "shp_shop", "cat_category", "cat_trademark");
- if (in_array($this->type, $tables_rating)) {
- foreach ($pole[$origin_id] as $key => $value) {
- if (substr($key, 0, 6) == "rating") {
- $pole[$origin_id]['rating'][$key] = $value;
- $this->remove_element($pole[$origin_id], $key);
- }
- }
- $this->changeStringToNumber($pole[$origin_id]['rating']);
- }*/
- /////////////////////////////////////////////////////////////////////////////////////////////////////
- $categoryCounter = 0;
- //Doplnenie kategorii spolu s parent kategoriami
- if (isset($pole[$origin_id]['category']) == TRUE || $this->type == "cat_category") {
- // docasne vytvoreni category casti u cat_category kvuli category path
- if ($this->type == "cat_category" ) {
- $pole[$origin_id]["cat_category_id"] = $origin_id;
- $pole[$origin_id]['category'] = [];
- }
- $category_id = $pole[$origin_id]["cat_category_id"];
- while ($category_id != NULL) {
- $query = "SELECT id, cat_category_parent_id, cat_category_code, priority_level, priority_internal, priority_commercial FROM cat_category WHERE id=" . $category_id;
- $result = mysqli_query($this->dbConnection, $query);
- $row = mysqli_fetch_assoc($result);
- if($result === FALSE) {
- throw new \Exception("DatabaseCacheManager false query on cat_category [" . $origin_id . "] " . mysqli_error($this->dbConnection));
- }
- if ($row != NULL) {
- $category = array();
- $category["id"] = $row["id"];
- $category["code"] = $row["cat_category_code"];
- $category['cat_category_priority_level'] = $row["priority_level"];
- $category['cat_category_priority_internal'] = $row["priority_internal"];
- $category['cat_category_priority_commercial'] = $row["priority_commercial"];
- if($row["id"] == $pole[$origin_id]["cat_category_id"]) {
- $pole[$origin_id]['cat_category_priority_level'] = $row["priority_level"];
- $pole[$origin_id]['cat_category_priority_internal'] = $row["priority_internal"];
- $pole[$origin_id]['cat_category_priority_commercial'] = $row["priority_commercial"];
- }
- $this->remove_prefix($category, "cat_");
- $this->changeStringToNumber($category);
- array_push($pole[$origin_id]['category'], $category);
- if ($row["cat_category_parent_id"] != NULL) {
- $category_id = $row["cat_category_parent_id"];
- } else {
- $category_id = NULL;
- }
- }
- $categoryCounter++;
- }
- for($i = 0; $i < count($pole[$origin_id]['category']); $i++){
- $pole[$origin_id]['category'][$i]['category_order'] = $categoryCounter - $i - 1;
- }
- $pole[$origin_id]['category'] = array_reverse($pole[$origin_id]['category'], true);
- $this->remove_prefix($pole[$origin_id], "cat_");
- }
- //doplnenie sub-kategorii
- // if ($this->type == "cat_category") {
- // $pole[$origin_id]["children_categories"] = array();
- // $query = "SELECT id, cat_category_code FROM cat_category WHERE cat_category_parent_id = $origin_id";
- // $result = mysqli_query($this->dbConnection, $query);
- //
- // if($result === FALSE) {
- // throw new \Exception("DatabaseCacheManager false query on cat_category [" . $origin_id . "] " . mysqli_error($this->dbConnection));
- // }
- //
- // while ($row = mysqli_fetch_assoc($result))
- // {
- // $subcat = array("id" => $row["id"], "code" => $row["cat_category_code"]);
- // $this->changeStringToNumber($subcat);
- //
- // array_push($pole[$origin_id]["children_categories"], $subcat);
- // }
- // }
- // //doplnenie sub structures pre cms_structure
- // if ($this->type == "cms_structure") {
- // $pole[$origin_id]["children_structures"] = array();
- // $query = "SELECT id, cms_structure_code FROM cms_structure WHERE cms_structure_parent_id = $origin_id";
- // $result = mysqli_query($this->dbConnection, $query);
- //
- // if($result === FALSE) {
- // throw new \Exception("DatabaseCacheManager false query on cms_structure [" . $origin_id . "] " . mysqli_error($this->dbConnection));
- // }
- //
- // while ($row = mysqli_fetch_assoc($result)) {
- // $subcat = array("id" => $row["id"], "code" => $row["cms_structure_code"]);
- // $this->changeStringToNumber($subcat);
- // array_push($pole[$origin_id]["children_structures"], $subcat);
- // }
- // }
- // Doplneni jazyku = preklady
- $query = "SELECT tra_object_id AS id, language, tra_object_label AS label,tra_object_title AS title,tra_object_description AS description,tra_object_content AS content, tra_object_slug AS slug FROM tra_object WHERE " . str_replace('id', 'tra_object_id', $id) . " AND tra_object_type='$this->type' $live $live_tra_object ORDER BY language";
- $result = mysqli_query($this->dbConnection, $query);
- while ($row = mysqli_fetch_assoc($result)) {
- $allowedLanguages = ['cs', 'sk', 'en'];
- if (in_array($row['language'],$allowedLanguages)) {
- $pole[$row['id']]['translate'][$row['language']]['label'] = str_replace('"','\\"',$row['label']);
- $pole[$row['id']]['translate'][$row['language']]['title'] = str_replace('"','\\"',$row['title']);
- $pole[$row['id']]['translate'][$row['language']]['description'] = str_replace('"','\\"',strip_tags($row['description']));
- $pole[$row['id']]['translate'][$row['language']]['content'] = str_replace('"','\\"',strip_tags($row['content']));
- $pole[$row['id']]['translate'][$row['language']]['description_source'] = htmlspecialchars($row['description'], ENT_QUOTES, 'UTF-8');
- $pole[$row['id']]['translate'][$row['language']]['content_source'] = htmlspecialchars($row['content'], ENT_QUOTES, 'UTF-8');
- $pole[$row['id']]['translate'][$row['language']]['slug'] = $row['slug'];
- if (isset($pole[$origin_id]['category']) == TRUE || $this->type == "cat_category") {
- $translateCategoriesIds = array();
- //kategoria produktu
- $category_path = "";
- $category_destination = "";
- foreach($pole[$origin_id]['category'] as $cat) {
- array_push($translateCategoriesIds, $cat["id"]);
- }
- if(count($translateCategoriesIds)) {
- $trCatQuery = "SELECT tra_object_id AS id, tra_object_title AS title FROM tra_object WHERE tra_object_type = 'cat_category' AND tra_object_id IN(" . implode(",", $translateCategoriesIds) . ") AND language = '" . $row["language"] . "'" . $live . $live_tra_object . " ORDER BY tra_object_id DESC";
- $trCatResult = mysqli_query($this->dbConnection, $trCatQuery);
- $translatedCategories = [];
- while ($tRow = mysqli_fetch_assoc($trCatResult)) {
- $translatedCategories[] = ["id" => $tRow['id'], "title" => $tRow['title']];
- }
- //serazeni od nejvyssi kategorie po nejnizsi
- usort($translatedCategories, function ($a, $b) use ($translateCategoriesIds) {
- $pos_a = array_search($a['id'], $translateCategoriesIds);
- $pos_b = array_search($b['id'], $translateCategoriesIds);
- return $pos_a - $pos_b;
- });
- foreach ($translatedCategories as $item) {
- $category_path .= $item['title'] . " | ";
- if (isset($pole[$origin_id]['category_id']) && $pole[$origin_id]['category_id'] == $item['id']) {
- $category_destination = $item['title'];
- }
- // $category_path = rtrim($category_path, ' | ');
- }
- $category_path = rtrim($category_path, ' | ');
- }
- $pole[$origin_id]['translate'][$row['language']]['category_path'] = $category_path;
- $pole[$origin_id]['translate'][$row['language']]['category_destination'] = $category_destination;
- //array_push()
- }
- }
- //test
- /* $pole[$row['id']]['translate'][$row['language']]['label'] = $pole[$row['id']]['translate'][$row['language']]['label'];
- $pole[$row['id']]['translate'][$row['language']]['title'] = $pole[$row['id']]['translate'][$row['language']]['title'];
- $pole[$row['id']]['translate'][$row['language']]['description'] = $pole[$row['id']]['translate'][$row['language']]['description'];
- $pole[$row['id']]['translate'][$row['language']]['content'] = $pole[$row['id']]['translate'][$row['language']]['content'];
- $pole[$row['id']]['translate'][$row['language']]['content_strip_tags'] = $pole[$row['id']]['translate'][$row['language']]['content_strip_tags'];
- */
- }
- // ostraneni docasne category casti u cat_category
- if ($this->type == "cat_category" ) {
- unset($pole[$origin_id]['category']);
- unset($pole[$origin_id]['category_id']);
- unset($pole[$origin_id]['category_priority_level']);
- unset($pole[$origin_id]['category_priority_internal']);
- unset($pole[$origin_id]['category_priority_commercial']);
- }
- /////////////////////////////////////////////////////////////////////////////////////////////////////
- // Parametry produktu
- $propertyBlackList = ['duration_begin', 'duration_end', 'active', 'created', 'changed', 'cache'];
- // Blacklist pro vnitřní cyklus(value_multi)
- $innerBlackList = ['priority_level', 'priority_internal', 'priority_commercial', 'visibility', 'active', 'note', 'value_note', 'country', 'value_slug', 'destination'];
- $tables_property = array('pro_catalog', 'pro_product', 'pro_request', 'pro_response', 'pro_watchdog', 'bus_auction', 'bus_rent', 'bus_quote');
- $addonBlackList = ['value_note', 'value_cache', 'tolerance_order',
- 'tolerance_value', 'tolerance_percent', 'tolerance_operation', 'tolerance_range_max', 'tolerance_range_min', 'value_checkbox'];
- if (in_array($this->type, $tables_property)) {
- $query = "SELECT pv.*,
- prp_property_dependency_id AS dependency_id,
- prp_unit_default_id AS unit_default_id,
- prp_property_code AS code,
- prp_property_icon AS icon,
- prp_property_type AS type,
- prp_property_show AS 'show',
- prp_property_slug AS slug,
- prp_unit_code AS unit_code,
- prp_property_value_cache AS value_cache
- FROM prp_property p JOIN pro_value pv ON (p.id=pv.prp_property_id) LEFT JOIN prp_unit pu ON (pv.prp_unit_id=pu.id) WHERE " . str_replace('id', $this->type . '_id', $id) . " " . str_replace('active', 'pv.active', str_replace('visibility', 'pv.visibility', $live));
- $result = mysqli_query($this->dbConnection, $query);
- $value_multi = array();
- if(mysqli_num_rows($result)) {
- $pole[$origin_id]['property'] = array();
- }
- $i = 0;
- while ($row = mysqli_fetch_assoc($result)) {
- array_push($value_multi, $row['id']);
- $pom_id = $row[$this->type . '_id'];
- for ($ckl = 0; $ckl < sizeof($tables_property); $ckl++) $this->remove_element($row, $tables_property[$ckl] . '_id');
- $this->remove_elements_from_black_list($row, $propertyBlackList);
- $this->remove_elements_from_black_list($row, $innerBlackList);
- $this->remove_prefix($row, "pro_");
- $this->remove_prefix($row, "cat_");
- $this->remove_prefix($row, "prp_");
- // Odstranění prvků začínajících prefixem "value" a majícíh hodnotu null
- foreach ($row as $key => $value) {
- if ($value == null AND substr($key, 0, 5) == "value" AND $key != "value_single_id") {
- $this->remove_element($row, $key);
- }
- }
- //get exact value code for single_id from prp_property_value or store value in one exact field
- if (isset($row["value_single_id"])) {
- if ($row["value_cache"] == "no") {
- $query_property_value = "SELECT prp_property_value_title FROM prp_property_value WHERE id=" . $row["value_single_id"];
- $result_property_value = mysqli_query($this->dbConnection, $query_property_value);
- $property_value = mysqli_fetch_assoc($result_property_value);
- $row["value"] = $property_value["prp_property_value_title"];
- }
- else {
- $row["value"] = null;
- }
- } elseif (isset($row["value_text"])) {
- $row["value"] = $row["value_text"];
- } elseif (isset($row["value_textarea"])) {
- $row["value"] = $row["value_textarea"];
- } elseif (isset($row["value_texteditor"])) {
- $row["value"] = $row["value_texteditor"];
- } elseif (isset($row["value_date"])) {
- $row["value"] = $row["value_date"];
- } elseif (isset($row["value_time"])) {
- $row["value"] = $row["value_time"];
- } elseif (isset($row["value_datetime"])) {
- $row["value"] = $row["value_datetime"];
- } elseif (isset($row["value_checkbox"])) {
- $row["value"] = $row["value_checkbox"];
- }
- else
- $row["value"] = null;
- $row['multi'] = array();
- //$pole[$pom_id]['property'][$row['id']] = $row;
- $query_m = "SELECT ppv.* FROM pro_value_multi pm JOIN prp_property_value ppv ON (pm.prp_property_value_id=ppv.id)
- WHERE pro_value_id=" . $row['id'] . " " . str_replace('active', 'pm.active', str_replace('visibility', 'pm.visibility', $live));
- $result_m = mysqli_query($this->dbConnection, $query_m);
- while ($row_m = mysqli_fetch_assoc($result_m)) {
- $this->remove_prefix($row_m, "prp_");
- $this->remove_prefix($row_m, "property_", ['property_id', 'property_value_parent_id']);
- $this->remove_elements_from_black_list($row_m, $propertyBlackList);
- $this->remove_elements_from_black_list($row_m, $innerBlackList);
- // $this->remove_elements_from_black_list($row_m, $valueMultiBlackList);
- $row_m["value"] = $row_m["value_code"];
- $row_m["value"] = $row_m["value_code"];
- if ($row["value_cache"] == "no") {
- $row_m['value'] = $row_m["value_title"];
- }
- $this->changeStringToNumber($row_m);
- array_push($row['multi'], $row_m);// = $row_m;
- }
- if (is_array($pole[$origin_id]['property']) == FALSE) {
- $pole[$origin_id]['property'] = array();
- }
- $this->remove_elements_from_black_list($row, $addonBlackList);
- $this->changeStringToNumber($row);
- array_push($pole[$origin_id]['property'], $row);
- }
- }
- /////////////////////////////////////////////////////////////////////////////////////////////////////
- // Kontakt z lnk_contact
- $contactBlackList = ['duration_begin', 'duration_end', 'active', 'created', 'changed', 'cache', 'priority_level', 'priority_commercial', 'priority_internal', 'visibility', 'note'];
- // Určuje, které prvky se mají odstranit na základě typu kontaktu
- $contactTypeRelatedColumns = ['phone' => ['lnk_contact_phone'], 'mobile' => ['lnk_contact_mobile'], 'fax' => ['lnk_contact_fax'], 'email' => ['lnk_contact_email'], 'web' => ['lnk_contact_web'], 'facebook' => ['lnk_contact_social_facebook'], 'linkedin' => ['lnk_contact_social_linkedin'], 'google+' => ['lnk_contact_social_google'], 'pinterest' => ['lnk_contact_social_pinterest'], 'instagram' => ['lnk_contact_social_instagram'], 'twitter' => ['lnk_contact_social_twitter'], 'youtube' => ['lnk_contact_video_youtube'], 'vimeo' => ['lnk_contact_video_vimeo'], 'bank_account' => ['lnk_contact_bank_account', 'lnk_contact_bank_iban', 'lnk_contact_bank_bic_swift', 'lnk_contact_bank_name'], 'address' => ['lnk_contact_address_street', 'lnk_contact_address_street_number', 'lnk_contact_address_locality', 'lnk_contact_address_neighborhood', 'lnk_contact_address_postal_code', 'lnk_contact_address_region', 'lnk_contact_address_country', 'lnk_contact_place_id'], 'gps' => ['lnk_contact_gps_latitude', 'lnk_contact_gps_longitude', 'lnk_contact_gps_altitude', 'lnk_contact_gps_latitude_number', 'lnk_contact_gps_longitude_number', 'lnk_contact_gps_altitude_number']];
- $tables_contact = array('cor_user', 'fir_firm', 'fir_branch', 'shp_shop', 'fir_employee', 'cat_category', 'cat_trademark', 'pro_catalog', 'pro_product', 'shp_feed_product', 'pro_request', 'pro_response', 'pro_watchdog', 'bus_auction', 'bus_rent', 'bus_quote');
- if (in_array($this->type, $tables_contact)) {
- //$query="SELECT * FROM lnk_contact WHERE ".str_replace('id',$this->type.'_id',$id)." $live ORDER BY lnk_contact_order";
- $query = "SELECT * FROM lnk_contact WHERE lnk_contact_object_type = '$this->type' AND
- lnk_contact_object_id = '$origin_id' ORDER BY lnk_contact_order";
- $result = mysqli_query($this->dbConnection, $query);
- if($result === FALSE) {
- throw new \Exception("DatabaseCacheManager false query on lnk_contact [" . $origin_id . "] " . mysqli_error($this->dbConnection));
- }
- if(mysqli_num_rows($result)) {
- $pole[$origin_id]['contact'] = array();
- }
- $i = 0;
- while ($row = mysqli_fetch_assoc($result)) {
- $allowedTypes = explode(',', $row['lnk_contact_type']);
- foreach ($contactTypeRelatedColumns as $key => $value) {
- if (!in_array($key, $allowedTypes)) {
- foreach ($value as $item) {
- $this->remove_element($row, $item);
- }
- }
- }
- // $row['lnk_contact_content_strip_tags'] = strip_tags($row['lnk_contact_content']);
- $this->remove_prefix($row, "lnk_contact_");
- $this->remove_elements_from_black_list($row, $contactBlackList);
- $this->changeStringToNumber($row);
- array_push($pole[$origin_id]['contact'], $row);
- //$pole[$pom_id]['contact'][$i]=$row;
- $i++;
- }
- }
- /////////////////////////////////////////////////////////////////////////////////////////////////////
- // Pocty
- if ($this->type != "cat_category") {
- $pole[$origin_id]["stats"] =
- [ "request_count" => 0,
- "sales_count" => 0,
- "favorite_like" => 0,
- "favorite_dislike" => 0,
- "inspection_month" => 0,
- "inspection_year" => 0,
- "inspection_full" => 0
- ];
- }
- /////////////////////////////////////////////////////////////////////////////////////////////////////
- // Obrazky
- $tables_image = array('cor_user', 'fir_firm', 'fir_branch', 'shp_shop', 'fir_employee', 'cat_category', 'cat_trademark', 'pro_catalog', 'pro_product', 'shp_feed_product', 'pro_request', 'pro_response', 'pro_watchdog', 'bus_auction', 'bus_rent', 'bus_quote', 'prp_property', 'prp_unit', 'gal_gallery_item', 'cms_article', 'cms_structure', 'cms_blog', 'cms_questionnaire', 'cms_index', 'cms_test', 'cms_query', 'web_slider');
- $imagesBlackList = ["extension", "object_id", "cor_user_id", "object_type", "imageExtension", "sanatized_name"];
- if (in_array($this->type, $tables_image)) {
- $query = "SELECT img_image_link.*, img_image.img_image_extension AS imageExtension
- FROM img_image_link
- LEFT JOIN img_image ON img_image.id = img_image_link.img_image_id
- WHERE img_image_link_object_type = '$this->type' AND
- img_image_link_object_id = '$origin_id' ORDER BY img_image_link_order";
- $result = mysqli_query($this->dbConnection, $query);
- $i = 0;
- $type = "";
- if($result === FALSE) {
- throw new \Exception("DatabaseCacheManager false query on img_image_link [" . $origin_id . "] " . mysqli_error($this->dbConnection));
- }
- if(mysqli_num_rows($result)) {
- $pole[$origin_id]['image'] = array();
- }
- while ($row = mysqli_fetch_assoc($result)) {
- $this->remove_prefix($row, "img_image_link_");
- $this->remove_prefix($row, "img_");
- $this->remove_prefix($row, "image_", ["image_id"]);
- $this->remove_elements_from_black_list($row, $innerBlackList);
- $this->remove_elements_from_black_list($row, $propertyBlackList);
- $this->remove_element($row, "link_note");
- if ($type != $row['type']) {
- $i = 0;
- $type = $row['type'];
- }
- //get base relative path for image
- $path = Paths::getRemoteImageBasePath($row["image_id"], $row["imageExtension"]);
- $row["path"] = "/images" . $path;
- $this->remove_elements_from_black_list($row, $imagesBlackList);
- $this->changeStringToNumber($row);
- if ($row['type'] == "main") {
- array_unshift($pole[$origin_id]['image'], $row);
- }
- else{
- array_push($pole[$origin_id]['image'], $row);
- }
- //$pole[$pom_id]['image'] = $row;
- $i++;
- }
- }
- /////////////////////////////////////////////////////////////////////////////////////////////////////
- // Vazba na jine zeme dostupnosti produktu link_in_country
- $query = "SELECT id,lnk_in_country_object_id,lnk_in_country_price_convert,lnk_in_country_price_new,lnk_in_country_exchange_rate,currency,country,duration_begin,duration_end FROM lnk_in_country WHERE " . str_replace('id', 'lnk_in_country_object_id', $id) . " AND lnk_in_country_object_type='$this->type' $live";
- $result = mysqli_query($this->dbConnection, $query);
- if($result === FALSE) {
- throw new \Exception("DatabaseCacheManager false query on link_in_country [" . $origin_id . "] " . mysqli_error($this->dbConnection));
- }
- if(mysqli_num_rows($result)) {
- $pole[$origin_id]['link_in_country'] = array();
- }
- while ($row = mysqli_fetch_assoc($result)) {
- $pom_id = $row['lnk_in_country_object_id'];
- $pom_id_lnk = $row['id'];
- $this->remove_prefix($row, "lnk_in_country_");
- $this->remove_element($row, 'id');
- $this->remove_element($row, 'lnk_in_country_object_id');
- $this->changeStringToNumber($row);
- array_push($pole[$pom_id]['link_in_country'], $row);
- //$pole[$pom_id]['link_in_country'][$pom_id_lnk]=$row;
- }
- /////////////////////////////////////////////////////////////////////////////////////////////////////
- // Branch u firmy
- if ($this->type == "fir_firm") {
- $query = "SELECT * FROM fir_branch WHERE " . str_replace('id', $this->type . '_id', $id) . " $live";
- $result = mysqli_query($this->dbConnection, $query);
- $i = 0;
- if($result === FALSE) {
- throw new \Exception("DatabaseCacheManager false query on fir_firm [" . $origin_id . "] " . mysqli_error($this->dbConnection));
- }
- while ($row = mysqli_fetch_assoc($result)) {
- $pom_id = $row[$this->type . '_id'];
- for ($ckl = 0; $ckl < sizeof($tables_image); $ckl++) $this->remove_element($row, $tables_image[$ckl] . '_id');
- $this->remove_prefix($row, "fir_branch_");
- $this->remove_elements_from_black_list($row, $contactBlackList);
- $this->changeStringToNumber($row);
- // array_push($pole[$pom_id]['branch'], $row);
- $pole[$pom_id]['branch'][$i] = $row;
- $i++;
- }
- }
- // Shop u firm a branch
- $tables_shop = array('fir_firm', 'fir_branch');
- if (in_array($this->type, $tables_shop)) {
- $query = "SELECT * FROM shp_shop WHERE " . str_replace('id', $this->type . '_id', $id) . " $live";
- $result = mysqli_query($this->dbConnection, $query);
- $i = 0;
- $type = "";
- if($result === FALSE) {
- throw new \Exception("DatabaseCacheManager false query on firm and branch [" . $origin_id . "] " . mysqli_error($this->dbConnection));
- }
- while ($row = mysqli_fetch_assoc($result)) {
- $pom_id = $row[$this->type . '_id'];
- for ($ckl = 0; $ckl < sizeof($tables_image); $ckl++) $this->remove_element($row, $tables_image[$ckl] . '_id');
- $this->remove_prefix($row, "shp_shop_");
- $this->remove_elements_from_black_list($row, $contactBlackList);
- $this->changeStringToNumber($row);
- // array_push($pole[$pom_id]['shop'], $row);
- $pole[$pom_id]['shop'][$i] = $row;
- $i++;
- }
- }
- /////////////////////////////////////////////////////////////////////////////////////////////////////
- // Property values
- // if ($this->type == "prp_property") {
- //
- // $query = "SELECT * FROM prp_property_value WHERE prp_property_id = " . $this->id;
- // $result = mysqli_query($this->dbConnection, $query);
- // $i = 0;
- // while ($row = mysqli_fetch_assoc($result)) {
- // $this->remove_prefix($row, "prp_property_value_");
- //
- // $this->remove_elements_from_black_list($row, $propertyBlackList);
- // $this->remove_elements_from_black_list($row, $objectBlackList);
- //
- //
- // $queryTranslateValues = "SELECT tra_object_id AS id, language, tra_object_label AS label,tra_object_title AS title,tra_object_description AS description,tra_object_content AS content, tra_object_slug AS slug FROM tra_object WHERE tra_object_id = " . $row['id'] . " AND tra_object_type='prp_property_value' $live $live_tra_object ORDER BY language";
- // $resultTranslateValues = mysqli_query($this->dbConnection, $queryTranslateValues);
- //
- // while ($rowTranslateValues = mysqli_fetch_assoc($resultTranslateValues)) {
- //
- // $allowedLanguages = ['cs', 'sk', 'en'];
- // if (in_array($rowTranslateValues['language'], $allowedLanguages)) {
- // $row['translate'][$rowTranslateValues['language']]['label'] = str_replace('"', '\\"', $rowTranslateValues['label']);
- // $row['translate'][$rowTranslateValues['language']]['title'] = str_replace('"', '\\"', $rowTranslateValues['title']);
- //
- // $row['translate'][$rowTranslateValues['language']]['description'] = str_replace('"', '\\"', strip_tags($rowTranslateValues['description']));
- // $row['translate'][$rowTranslateValues['language']]['content'] = str_replace('"', '\\"', strip_tags($rowTranslateValues['content']));
- //
- // $row['translate'][$rowTranslateValues['language']]['description_source'] = htmlspecialchars($rowTranslateValues['description'], ENT_QUOTES, 'UTF-8');
- // $row['translate'][$rowTranslateValues['language']]['content_source'] = htmlspecialchars($rowTranslateValues['content'], ENT_QUOTES, 'UTF-8');
- // $row['translate'][$rowTranslateValues['language']]['slug'] = $rowTranslateValues['slug'];
- // }
- //
- // }
- //
- // $this->changeStringToNumber($row);
- // $pole[$this->id ]['value'][$i] = $row;
- // $i++;
- // }
- // }
- if ($this->type == "prp_property") {
- $prpPropertyBlackList = ["decimal_scale", "text_length_min", "decimal_precision",
- "text_trim", "range_min", "range_max"];
- $this->remove_elements_from_black_list($pole[$this->id], $prpPropertyBlackList);
- $query = "SELECT u.* FROM prp_unit_assigned ua JOIN prp_unit u ON (u.id=ua.prp_unit_id) WHERE prp_property_id = " . $this->id;
- $result = mysqli_query($this->dbConnection, $query);
- $i = 0;
- while ($row = mysqli_fetch_assoc($result)) {
- $this->remove_prefix($row, "prp_unit_");
- $this->remove_elements_from_black_list($row, $propertyBlackList);
- // $this->remove_elements_from_black_list($row, $objectBlackList);
- $this->remove_elements_from_black_list($row, $innerBlackList);
- $this->changeStringToNumber($row);
- $pole[$this->id ]['units'][$i] = $row;
- $i++;
- }
- }
- $this->changeStringToNumber($pole[$origin_id]);
- $this->arrayData = $pole;
- }
- catch(\Exception $e)
- {
- throw new DatabaseCacheUpdateException("DatabaseCacheManager, buildJson exception: " . $e->getMessage());
- }
- }
- public function cleanArrayData(&$array)
- {
- foreach($array as $key => &$value) {
- if(is_array($value)) {
- $value = $this->cleanArrayData($value);
- }
- else {
- if($value != null)
- {
- $value = htmlspecialchars($value, ENT_QUOTES);
- $value = str_replace('\\', '', $value);
- $value = str_replace('\\/\\', '', $value);
- $value = str_replace("\t", "\\t", $value);
- $value = str_replace("\r", "\\r", $value);
- $value = str_replace("\n", "\\n", $value);
- $value = str_replace("\f", "\\f", $value);
- }
- }
- }
- return $array;
- }
- private function updateCache()
- {
- try {
- $this->arrayData = $this->cleanArrayData($this->arrayData); //clean for json encode
- // Ulozeni hodnoty do cache
- if (self::STORE_TYPE == 'multi') {
- throw new \Exception("Unsupported store type MULTI.");
- $key = array_keys($this->arrayData);
- for ($ckl = 0; $ckl < sizeof($key); $ckl++) {
- $pom = array();
- $pom = $this->arrayData[$key[$ckl]];
- $pom_json = json_encode($pom, JSON_UNESCAPED_UNICODE);
- $query = "INSERT INTO cac_cache (cac_cache_type, cac_cache_id, cache,cache_status) VALUES ('$this->type', " . $key[$ckl] . ", '$pom_json', 'generated')
- ON DUPLICATE KEY UPDATE cache='$pom_json', cache_status='generated'";
- $result = mysqli_query($this->dbConnectionCache, $query);
- if(!$result) {
- throw new \Exception("Update cache query failed: " . mysqli_error($this->dbConnectionCache));
- }
- }
- } else {
- if(isset($this->arrayData[$this->id]) == FALSE) {
- throw new \Exception("DatabaseCacheManager error - empty array for id to store to cache: " . $this->id);
- }
- $this->arrayData[$this->id]["changed"] = date("Y-m-d H:i:s");
- $json = $this->getJsonData();
- $verifyArray = json_decode($json);
- if (json_last_error() !== JSON_ERROR_NONE) {
- $json = ""; //{} - "" wont be saved
- }
- $query = "INSERT INTO cac_cache (cac_cache_type, cac_cache_id, cache,cache_status) VALUES ('$this->type', " . $this->id . ", '$json', 'generated')
- ON DUPLICATE KEY UPDATE cache='$json', cache_status='generated'";
- $result = mysqli_query($this->dbConnectionCache, $query);
- if(!$result) {
- throw new \Exception("Update cache [" . $this->id . "] query failed: " . mysqli_error($this->dbConnectionCache));
- }
- }
- }
- catch(\Exception $e) {
- //\Tracy\Debugger::log("DatabaseCacheManager, updateCache exception: " . $e->getMessage());
- return FALSE;
- }
- return TRUE;
- }
- public function deleteCache()
- {
- $this->needRebuild = TRUE;
- $query = "UPDATE $this->type SET cache='' WHERE id='$this->id'";
- $result = mysqli_query($this->dbConnection, $query);
- return $result == FALSE ? FALSE : TRUE;
- }
- public function getJsonData()
- {
- if($this->needRebuild) {
- $this->buildJsonData();
- }
- if(isset($this->arrayData[$this->id]) == FALSE) {
- throw new \Exception("Unset ID in arrayData.");
- }
- return json_encode($this->arrayData[$this->id], JSON_UNESCAPED_UNICODE);
- }
- public function getArrayData()
- {
- if($this->needRebuild) {
- $this->buildJsonData();
- }
- return $this->arrayData;
- }
- public function setId($id)
- {
- $this->needRebuild = TRUE;
- $this->id = $id;
- }
- public function getId()
- {
- return $this->id;
- }
- public function setType($type)
- {
- $this->needRebuild = TRUE;
- $this->type = $type;
- }
- public function getType($type)
- {
- return $this->type;
- }
- public function setLiveRecord($bool)
- {
- $this->live_record = $bool;
- }
- public function getLiveRecord()
- {
- return $this->live_record;
- }
- protected function remove_element(&$array, $key) // pass array by reference
- {
- unset($array[$key]);
- }
- /**
- * Odstrani všechny prvky, které se nachází na blacklistu
- */
- protected function remove_elements_from_black_list(&$array, $blackList)
- {
- foreach ($array as $key => $value) {
- if (in_array($key, $blackList)) {
- $this->remove_element($array, $key);
- }
- }
- }
- /**
- * @param $array
- * @param $old_key
- * @param $new_key
- * Vymění starý klíč za nový
- */
- protected function change_key(&$array, $old_key, $new_key) {
- if ( array_key_exists( $old_key, $array ) )
- {
- $keys = array_keys( $array );
- $keys[ array_search( $old_key, $keys ) ] = $new_key;
- $array = array_combine( $keys, $array );
- }
- }
- /**
- * @param $array
- * @param $prefix
- * @param array $no_process
- * Odstraní zadaný prefix ze všech prvků pole, pokud existuje
- * Třetí parametr obsahuje prvky, u kterých prefix zůstává
- */
- protected function remove_prefix(&$array, $prefix, $no_process = [])
- {
- foreach ($array as $key => $value) {
- if (!in_array($key, $no_process)) {
- $pom = str_replace($prefix,"",$key);
- if ($key != $pom) {
- $this->change_key($array, $key, $pom );
- }
- }
- }
- }
- protected function utf8_encode_recursive ($array)
- {
- $result = array();
- foreach ($array as $key => $value)
- {
- if (is_array($value))
- {
- $result[$key] = $this->utf8_encode_recursive($value);
- }
- else if (is_string($value))
- {
- $result[$key] = utf8_encode($value);
- }
- else
- {
- $result[$key] = $value;
- }
- }
- return $result;
- }
- public function getObjectsToRebuild($limit, $cache = NULL)
- {
- $objects = [];
- $list = "'rebuild', 'empty', 'waiting'";
- $cacheTypeList = "'cms_structure', 'cms_article', 'cat_category', 'cat_trademark', 'prp_property_value', 'prp_property', 'pro_catalog'";
- if($cache)
- $query = "SELECT id, cac_cache_id, cac_cache_type, cache FROM cac_cache WHERE cache_status IN ($list) AND active = 'enabled' AND cac_cache_type IN ($cacheTypeList) LIMIT $limit";
- else
- $query = "SELECT id, cac_cache_id, cac_cache_type FROM cac_cache WHERE cache_status IN ($list) AND active = 'enabled' AND cac_cache_type IN ($cacheTypeList) LIMIT $limit";
- //$query = "SELECT id, cac_cache_id, cac_cache_type FROM cac_cache WHERE cache_status IN ($list) LIMIT $limit";
- $result = mysqli_query($this->dbConnectionCache, $query);
- echo "\r\n";
- echo 'Total rows selected: ' . $result->num_rows;
- echo "\r\n";
- // query ok
- if($result != false)
- {
- while($row = $result->fetch_assoc()){
- $objects[] = $row;
- }
- }
- else
- {
- throw new \Exception('getObjectsToRebuild exception');
- }
- return $objects;
- }
- public function getObjectsWithCacheToUpdateByIds($ids)
- {
- $objects = [];
- $query = "SELECT id, cac_cache_id, cac_cache_type, cache FROM cac_cache WHERE id IN (".implode(',', $ids).")";
- $result = mysqli_query($this->dbConnectionCache, $query);
- // query ok
- if($result != false)
- {
- while($row = $result->fetch_assoc()){
- $objects[] = $row;
- }
- }
- else
- {
- throw new \Exception('getObjectsWithCacheToUpdate exception');
- }
- return $objects;
- }
- public function getTestObjectsToRebuild($limit, $cache = NULL)
- {
- $objects = [];
- $query = "select id, cac_cache_id, cac_cache_type from cac_cache where cac_cache_type = 'cat_category' LIMIT $limit";
- $result = mysqli_query($this->dbConnectionCache, $query);
- echo "\r\n";
- echo 'Total rows selected:' . $result->num_rows;
- // query ok
- if($result != false)
- {
- while($row = $result->fetch_assoc()){
- $objects[] = $row;
- }
- }
- else
- {
- throw new \Exception('getTestObjectsToRebuild exception');
- }
- return $objects;
- }
- public function updateCacheStatusAndReport($status, $ids, $report = NULL)
- {
- $status = "'$status'";
- $this->dbConnectionCache->autocommit(FALSE);
- try
- {
- $this->dbConnectionCache->begin_transaction();
- echo "Update starts ...";
- echo "\r\n";
- if(!$report)
- {
- $query = "UPDATE cac_cache SET cache_status = $status WHERE id IN (".implode(',', $ids).")";
- }
- else
- {
- $query = "UPDATE cac_cache SET cache_status = $status, cache_report = $report WHERE id IN (".implode(',', $ids).")";
- }
- mysqli_query($this->dbConnectionCache, $query);
- if(mysqli_error($this->dbConnectionCache)) {
- echo mysqli_error($this->dbConnectionCache);
- throw new \Exception("Update transaction was not executed correctly, command rollbacked" . mysqli_error($this->dbConnectionCache));
- }
- $affectedRows = $this->dbConnectionCache->affected_rows;
- $this->dbConnectionCache->commit();
- }
- catch(\Exception $e)
- {
- $this->dbConnectionCache->rollback();
- $affectedRows = -1;
- throw new \Exception("Update transaction was not executed correctly, command rollbacked");
- }
- return $affectedRows;
- }
- public function changeStringToNumber(&$array){
- $blackList = ["title", "label", "description", "content", "value_title", "value_label", "value_description", "value_content"];
- foreach($array as $key => $value)
- {
- if(is_numeric($value) && !in_array($key, $blackList))
- {
- $array[$key] = (float)$value;
- }
- }
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement