525 lines
16 KiB
PHP
525 lines
16 KiB
PHP
<?php
|
|
|
|
namespace BRSystems\Core\Sys\sysTranslationsPub\inc;
|
|
|
|
use BRSystems\Core\Library\cDb\cDb;
|
|
|
|
/**
|
|
* sysTranslationsPub Module
|
|
*
|
|
* @category sysTranslationsPub Module
|
|
* @author Mareks Keišs <mareks.keiss@brsystems.lv>
|
|
* @since Version 2.0
|
|
*/
|
|
class sysTranslationsPubModule
|
|
{
|
|
public $db;
|
|
|
|
/**
|
|
* Class constructor
|
|
*/
|
|
public function __construct() {
|
|
|
|
$this->db = cDb::getInstance();
|
|
|
|
}
|
|
|
|
/**
|
|
* Function to get module records
|
|
*
|
|
* @author Mareks Keišs <mareks.keiss@brsystems.lv>
|
|
* @category sysTranslationsPub Module
|
|
* @param string $where - sql query
|
|
* @param string $sort - sql query
|
|
* @param int $offset - db table offset
|
|
* @param int $limitPerPage - limit per page
|
|
* @return array of data
|
|
* @version 1.1
|
|
*/
|
|
public function getModuleRecords($where, $sort, $offset = 0, $limitPerPage = 20){
|
|
|
|
$sSql = "
|
|
SELECT SQL_CALC_FOUND_ROWS
|
|
DISTINCT msg.id,
|
|
msg.msg_message,
|
|
map.links, map.rec_count
|
|
FROM
|
|
site_pub_msg msg
|
|
LEFT JOIN site_pub_msg_translations msgt ON
|
|
msgt.msg_id = msg.id
|
|
LEFT JOIN (
|
|
SELECT
|
|
ll.msg_id, GROUP_CONCAT(sm.p_title SEPARATOR '|') as links, COUNT(ll.msg_id) as rec_count
|
|
FROM
|
|
temp_pub_msg_links ll
|
|
LEFT JOIN site_map sm ON
|
|
sm.id = ll.map_id
|
|
LEFT JOIN site_pub_msg mm ON
|
|
mm.id = ll.msg_id
|
|
WHERE
|
|
mm.id is not null
|
|
GROUP BY
|
|
ll.msg_id
|
|
) map ON
|
|
map.msg_id = msg.id
|
|
WHERE
|
|
1 {$where}
|
|
ORDER BY
|
|
{$sort}
|
|
LIMIT
|
|
{$offset}, {$limitPerPage}
|
|
";
|
|
|
|
$this->db->query($sSql);
|
|
return $this->db->getArray();
|
|
}
|
|
|
|
/**
|
|
* Function to get module records
|
|
*
|
|
* @author Aivars Žukovskis <mareks.keiss@brsystems.lv>
|
|
* @category sysTranslationsPub Module
|
|
* @return array of data
|
|
* @version 1.1
|
|
*/
|
|
public function getRecordData(){
|
|
|
|
$sSql = "
|
|
SELECT SQL_CALC_FOUND_ROWS
|
|
DISTINCT msg.id,
|
|
msg.msg_message,
|
|
ll.lang_code,
|
|
msgt.msg_translation
|
|
FROM
|
|
site_pub_msg msg
|
|
LEFT JOIN site_pub_msg_translations msgt ON
|
|
msgt.msg_id = msg.id
|
|
LEFT JOIN site_languages ll ON
|
|
msgt.msg_lang_id = ll.id
|
|
";
|
|
|
|
$this->db->query($sSql);
|
|
return $this->db->getFieldBasedArrayFields('id','lang_code',array('msg_message', 'msg_translation'));
|
|
}
|
|
|
|
/**
|
|
* Function to get data by query and limit
|
|
*
|
|
* @author Mareks Keišs <mareks.keiss@brsystems.lv>
|
|
* @category APP pubLogistics
|
|
* @param string $sQuery - route id
|
|
* @param int $offset - route data
|
|
* @param int $chunkSize - route data
|
|
* @return array - record data
|
|
* @version 1.1
|
|
*/
|
|
public function getDataByQueryAndOffset($sQuery, $offset = 0, $chunkSize = 0){
|
|
|
|
if(!!$sQuery){
|
|
|
|
$this->db->query("
|
|
{$sQuery}
|
|
|
|
LIMIT
|
|
{$offset}, {$chunkSize}
|
|
");
|
|
return $this->db->getArray();
|
|
}
|
|
return false;
|
|
}
|
|
|
|
/**
|
|
* Function to get data by query and limit
|
|
*
|
|
* @author Mareks Keišs <mareks.keiss@brsystems.lv>
|
|
* @category APP pubLogistics
|
|
* @return array - record data
|
|
* @version 1.1
|
|
*/
|
|
public function getBaseData(){
|
|
|
|
|
|
$sSql = "
|
|
SELECT SQL_CALC_FOUND_ROWS
|
|
DISTINCT msg.id,
|
|
msg.msg_message as message
|
|
FROM
|
|
site_pub_msg msg
|
|
";
|
|
$this->db->query($sSql);
|
|
return $this->db->getFieldBasedArray('id');
|
|
|
|
}
|
|
|
|
/**
|
|
* Function to get data by query and limit
|
|
*
|
|
* @author Mareks Keišs <mareks.keiss@brsystems.lv>
|
|
* @category APP pubLogistics
|
|
* @return array - record data
|
|
* @version 1.1
|
|
*/
|
|
public function getRecordTranslations(){
|
|
|
|
$sSql = "
|
|
SELECT
|
|
msgt.msg_id,
|
|
msgt.msg_lang_id,
|
|
msgt.msg_translation
|
|
FROM
|
|
site_pub_msg_translations msgt
|
|
";
|
|
|
|
$this->db->query($sSql);
|
|
return $this->db->getArray();
|
|
|
|
}
|
|
|
|
/**
|
|
* Function to get logistic object data and add to array cols
|
|
*
|
|
* @author Mareks Keišs <mareks.keiss@brsystems.lv>
|
|
* @category APP pubLogistics
|
|
* @param string $sQuery - query to exec
|
|
* @param int $offset - row offset
|
|
* @param int $chunkSize - chunk size
|
|
* @param array $data - data array
|
|
* @param array $r2i - logistic id to col id
|
|
* @param string|int $zero - zero value
|
|
* @return array - record data
|
|
* @version 1.1
|
|
*/
|
|
public function addLogisticsObjectsToDataArray($sQuery, $offset = 0, $chunkSize = 0, &$data, $r2i, $zero = 0){
|
|
if(!!$sQuery && !!$data){
|
|
|
|
$this->db->query("
|
|
{$sQuery}
|
|
|
|
LIMIT
|
|
{$offset}, {$chunkSize}
|
|
");
|
|
|
|
$pData = $this->db->getFieldBasedArrayFields('id', 'r', 'p');
|
|
if(!!$pData && is_array($pData)){
|
|
foreach($data as &$v){
|
|
foreach($r2i as $cId=>$rId) { // col id & Row id
|
|
$v["p{$cId}"] = (isset($pData[$v['id']][$rId]) && $pData[$v['id']][$rId]? $pData[$v['id']][$rId] : $zero);
|
|
}
|
|
|
|
}
|
|
unset($v);
|
|
}
|
|
|
|
return $this->db->getArray();
|
|
}
|
|
return false;
|
|
}
|
|
|
|
/**
|
|
* Function to add or save record data
|
|
*
|
|
* @author Mareks Keišs <mareks.keiss@brsystems.lv>
|
|
* @category sysTranslationsPub Module
|
|
* @param int $id - record id for update
|
|
* @param array $data - record data
|
|
* @return boot record new/old id
|
|
* @version 1.1
|
|
*/
|
|
public function saveRecordDataById($id, $data){
|
|
|
|
if($id){
|
|
if(!$this->db->_queryUpdate('site_pub_msg', $data, array('id' => $id))){
|
|
return false;
|
|
}
|
|
}else{
|
|
if(!($id = $this->db->_queryInsert('site_pub_msg', $data))){
|
|
return false;
|
|
}
|
|
}
|
|
return $id;
|
|
|
|
}
|
|
|
|
/**
|
|
* Function to get record data by id
|
|
*
|
|
* @author Mareks Keišs <mareks.keiss@brsystems.lv>
|
|
* @category sysTranslationsPub Module
|
|
* @param int $id - record id
|
|
* @return array - record data
|
|
* @version 1.1
|
|
*/
|
|
public function getRecordDataById($id){
|
|
|
|
return $this->db->_querySelectRow('site_pub_msg', false, array('id' => $id));
|
|
|
|
}
|
|
|
|
/**
|
|
* Function to get system languages array
|
|
*
|
|
* @author Mareks Keišs <mareks.keiss@brsystems.lv>
|
|
* @category sysTranslationsPub Module
|
|
* @return array - record data
|
|
* @version 1.1
|
|
*/
|
|
public function getSysLanguages(){
|
|
|
|
return $this->db->_querySelect('site_languages', false, array('type' => 0), array('site_languages.order' => 'ASC'));
|
|
|
|
}
|
|
|
|
/**
|
|
* Function to get record translation data
|
|
*
|
|
* @author Mareks Keišs <mareks.keiss@brsystems.lv>
|
|
* @category sysTranslationsPub Module
|
|
* @param int $msgId - message record id
|
|
* @return array - record data
|
|
* @version 1.1
|
|
*/
|
|
public function getRecordTranslationsById($msgId){
|
|
|
|
$sSql = "
|
|
SELECT
|
|
msgt.msg_lang_id,
|
|
msgt.msg_translation
|
|
FROM
|
|
site_pub_msg_translations msgt
|
|
WHERE
|
|
msgt.msg_id = " . $this->db->prepData($msgId) . "
|
|
";
|
|
|
|
$this->db->query($sSql);
|
|
return $this->db->getFieldBasedArray('msg_lang_id');
|
|
|
|
}
|
|
|
|
/**
|
|
* Function to delete all table records form db
|
|
*
|
|
* @author Mareks Keišs <mareks.keiss@brsystems.lv>
|
|
* @category sysTranslationsPub Module
|
|
* @return bool true / false
|
|
* @version 1.1
|
|
*/
|
|
public function deleteAllRecords(){
|
|
|
|
$this->db->execute("DELETE FROM `temp_pub_msg_links`");
|
|
$this->db->execute("DELETE FROM `site_pub_msg_translations`");
|
|
return $this->db->execute("DELETE FROM `site_pub_msg`");
|
|
|
|
}
|
|
|
|
/**
|
|
* Function to delete all table records form db that are unused or untranslated
|
|
*
|
|
* @author Mareks Keišs <mareks.keiss@brsystems.lv>
|
|
* @category sysTranslationsPub Module
|
|
* @return bool true / false
|
|
* @version 1.1
|
|
*/
|
|
public function cleanupAllRecords(){
|
|
|
|
$this->db->executeMulti("
|
|
SELECT
|
|
@lang_count:=COUNT(id)
|
|
FROM
|
|
`site_languages` ll
|
|
WHERE
|
|
ll.type = '0';
|
|
|
|
CREATE TEMPORARY TABLE tmp_id_to_delete_from_msg AS
|
|
SELECT
|
|
msg.id, count_check.cc
|
|
FROM
|
|
`site_pub_msg` msg
|
|
LEFT JOIN `site_pub_msg_translations` msgt
|
|
ON msg.id = msgt.msg_id
|
|
LEFT JOIN (
|
|
SELECT
|
|
msg.id,COUNT(msg.id) cc
|
|
FROM
|
|
`site_pub_msg` msg
|
|
LEFT JOIN `site_pub_msg_translations` msgt
|
|
ON msg.id = msgt.msg_id
|
|
GROUP BY msg.id
|
|
) count_check ON
|
|
count_check.id = msg.id
|
|
WHERE
|
|
msg.msg_message = msgt.msg_translation
|
|
or msgt.msg_translation is null
|
|
or TRIM(msgt.msg_translation) = ''
|
|
GROUP BY msg.id
|
|
HAVING COUNT(msg.id) = count_check.cc;
|
|
", true);
|
|
|
|
$this->db->query("SELECT COUNT(id) as total FROM tmp_id_to_delete_from_msg;");
|
|
$rows = $this->db->getOne();
|
|
|
|
$this->db->executeMulti("
|
|
DELETE b_msg
|
|
FROM `site_pub_msg` b_msg
|
|
WHERE b_msg.id IN (SELECT id FROM tmp_id_to_delete_from_msg);
|
|
|
|
DELETE t_msg
|
|
FROM `site_pub_msg_translations` t_msg
|
|
WHERE t_msg.msg_id IN (SELECT id FROM tmp_id_to_delete_from_msg);
|
|
|
|
|
|
CREATE TEMPORARY TABLE tmp_links_to_delete_from_msg AS
|
|
SELECT
|
|
*, COUNT(map_id) as cc
|
|
FROM
|
|
temp_pub_msg_links ll
|
|
GROUP BY
|
|
ll.map_id, msg_id
|
|
HAVING
|
|
cc > 1;
|
|
|
|
DELETE pl
|
|
FROM temp_pub_msg_links pl
|
|
WHERE pl.msg_id IN (SELECT msg_id FROM tmp_links_to_delete_from_msg);
|
|
|
|
INSERT INTO temp_pub_msg_links
|
|
SELECT msg_id, map_id FROM tmp_links_to_delete_from_msg;
|
|
|
|
");
|
|
|
|
return (is_numeric($rows) ? $rows : false);
|
|
|
|
}
|
|
|
|
/**
|
|
* Function to delete table records form db
|
|
*
|
|
* @author Mareks Keišs <mareks.keiss@brsystems.lv>
|
|
* @category sysTranslationsPub Module
|
|
* @param array $ids - record ids to delete
|
|
* @return bool true / false
|
|
* @version 1.1
|
|
*/
|
|
public function deleteRecords($ids){
|
|
|
|
$ids = implode(',', $ids);
|
|
$this->db->execute("DELETE FROM `temp_pub_msg_links` WHERE `msg_id` IN ({$ids})");
|
|
$this->db->execute("DELETE FROM `site_pub_msg_translations` WHERE `msg_id` IN ({$ids})");
|
|
return $this->db->execute("DELETE FROM `site_pub_msg` WHERE `id` IN ({$ids})");
|
|
|
|
}
|
|
|
|
/**
|
|
* Function to update message translations
|
|
*
|
|
* @author Mareks Keišs <mareks.keiss@brsystems.lv>
|
|
* @category sysTranslationsPub Module
|
|
* @param int $msgId - record id
|
|
* @param array $translations - record translations
|
|
* @return bool true / false
|
|
* @version 1.1
|
|
*/
|
|
public function updateTranslations($msgId, $translations){
|
|
|
|
$this->db->_queryDelete('site_pub_msg_translations', array('msg_id' => $msgId));
|
|
return $this->db->_queryInsertArray('site_pub_msg_translations', $translations, false, true);
|
|
|
|
}
|
|
|
|
|
|
/**
|
|
* Function to change record status in db table
|
|
*
|
|
* @author Mareks Keišs <mareks.keiss@brsystems.lv>
|
|
* @category sysTranslationsPub Module
|
|
* @param array $ids - record ids to affect
|
|
* @param string $field - field to update for status ENUM
|
|
* @return bool true / false
|
|
* @version 1.1
|
|
*/
|
|
public function changeRecordsStatus($ids, $field){
|
|
|
|
$time = time();
|
|
$ids = implode(',', $ids);
|
|
$status = $this->db->query("UPDATE site_menus msg SET {$field} = '1', menu_modified = '{$time}' WHERE {$field} = '0' AND id IN ({$ids})");
|
|
if(!$status)
|
|
return $status;
|
|
$status = $this->db->query("UPDATE site_menus msg SET {$field} = '0', menu_modified = '{$time}' WHERE {$field} = '1' AND id IN ({$ids}) AND menu_modified <> '{$time}'");
|
|
return $status;
|
|
|
|
}
|
|
|
|
|
|
/**
|
|
* Function to reserve next x classificator table ids
|
|
*
|
|
* @author Mareks Keišs <mareks.keiss@brsystems.lv>
|
|
* @category CMS cmsClassificators
|
|
* @param array $resRowsCount - tables rows to reserve
|
|
* @return bool record new/old id
|
|
* @version 1.1
|
|
*/
|
|
public function rsvTableRows($resRowsCount){
|
|
|
|
if($resRowsCount && $resRowsCount > 0){
|
|
$this->db->query("SELECT COALESCE(MAX(id),0)+{$resRowsCount} FROM site_pub_msg");
|
|
if($maxId = $this->db->getOne()){
|
|
$this->db->query("INSERT INTO site_pub_msg (id, msg_message) VALUES ({$maxId}, -1)");
|
|
return $maxId;
|
|
}
|
|
}
|
|
return false;
|
|
}
|
|
|
|
public function getMaxId(){
|
|
$this->db->query("SELECT MAX(id) FROM site_pub_msg");
|
|
return $this->db->getOne();
|
|
}
|
|
/**
|
|
* Function to add or save classificator row record data
|
|
*
|
|
* @author Mareks Keišs <mareks.keiss@brsystems.lv>
|
|
* @category CMS cmsClassificators
|
|
* @param array/string $msg
|
|
* @return array
|
|
* @version 1.1
|
|
*/
|
|
public function getMsgIdsByMsg($msg){
|
|
|
|
$sSql = "
|
|
SELECT
|
|
id,
|
|
msg_message
|
|
FROM
|
|
site_pub_msg
|
|
WHERE
|
|
msg_message IN ('" . implode("','", $this->db->prepData($msg, true)) . "')
|
|
";
|
|
|
|
$this->db->query($sSql);
|
|
return $this->db->getArray();
|
|
}
|
|
|
|
public function truncateTable($table){
|
|
$this->db->execute("TRUNCATE TABLE {$table}");
|
|
}
|
|
|
|
/**
|
|
* Function to add or save classificator row record data
|
|
*
|
|
* @author Mareks Keišs <mareks.keiss@brsystems.lv>
|
|
* @category CMS cmsClassificators
|
|
* @param string $table - table name
|
|
* @param array $data - import data
|
|
* @param array|bool $where - condition for removal
|
|
* @return bool record new/old id
|
|
* @version 1.1
|
|
*/
|
|
public function saveTableImportData($table, $data, $where = false){
|
|
|
|
if(!!$where && is_array($where))
|
|
$this->db->_queryDelete($table, $where);
|
|
$this->db->_queryInsertArray($table, $data, false, true);
|
|
|
|
}
|
|
}
|